Tabulka s formulářem + absolutní odkaz na buňku z jiného listu

[Dali]

Dobrý den,

mohu poprosit o radu, jak v Google tabulce udělat absolutní odkaz na jiný list, do něhož vkládám data z webového formuláře? Na prvním listu sbírám data a na druhém listu mám vzorce, které pracují s hodnotami z prvního listu. Vše funguje OK, ale když dorazí nová odpověď z formuláře (přibude řádek na prvním listu), neprojeví se hodnoty na druhém listu, protože se všechny vzorce posunou a třeba místo na 7. řádek odkazují na 8. prázdný řádek...

Zkrátka místo odkazu "=List_Hodnoty!F7" se vzorec sám upraví na "=List_Hodnoty!F8"

Zkoušel jsem absolutní odkaz (místo "F7" dát "$F$7"), ale funguje to stále stejně. Místo toho, aby se druhý list postupně doplňoval tak jak přichází řádky do první listu, musím vždy ručně protáhnout vzorce z existujícího vyplněného řádku dolů.

Předem díky za jakýkoli tip.

Dali

[Martin Bugner]

Jestli tomu dobře rozumím, tak navrhuji ve druhém listu, který sbírá data z prvního, jednoduše předvyplnit vzorce do celého sloupce. Tak budete  mít zajištěno, že hodnota z příslušné buňky listu A se promítne do dané buňky listu B. Samozřejmě definovat absolutně.

Snad jsem to vysvětlil srozumitelně, ale v podstatě jde o to, že v listu B nemohou sami od sebe přibývat buňky se vzorci - ty tam musí být již předdefinované, aby došlo k požadovanému efektu.

Martin

Dne čtvrtek, 2. srpna 2012 21:18:11 UTC+2 Dali napsal(a):

[Dali]

Martine, díky za reakci. Vzorce mám předkopírované asi na 50 řádků dopředu, ale jak přichází odpovědi z webu do prvního listu, tak se nezapisují do již existujících řádků. Místo toho se vloží nový řádek a ty původní (prázdné řádky) se posunou o jeden níže. Vzorce na druhém listu (rádoby chytře) následují tento posun, odkazují na stále stejné řádky a ignorují ten nově vložený. Uf, snad jsem to moc nezamotal a je to pochopitelné ;-)).

Jde o to, jak vzorec zafixovat tak, aby ukazoval stále třeba na 7.řádek i v okamžiku, že třeba mezi 2. a 3. řádek vložíte nějaký nový (nyní se vzorec automaticky upraví a odkazuje na 8.řádek).

Díky za pomoc

Dali

Dne čtvrtek, 2. srpna 2012 21:18:11 UTC+2 Dali napsal(a):

Dobrý den,

[Martin Bugner]

Aha, takže buňku nedefinovat absolutně, ale relativně, resp. mixovat, tedy absolutně zafixovat pouze sloupec.
Příklad: Vstupní data list_A:F7 => výstup list_B:$F7

Absolutní definicí totiž způsobím, že natvrdo vyžaduji, aby se mi v buňce zobrazovaly data vždy tam kam jsem je absolutně umístil. Je možné, že na rozdíl od Excelu toto Google Spreadsheet tak úplně nerespektuje, proto to "chytré" chování.
Jinak vyzkoušejte Nápovědu > Seznam funkcí > filtr 'absolute' - https://support.google.com/docs/bin/static.py?hl=en&topichttps://support.google.com/docs/bin/static.py?hl=en&topic=25273&page=table.cs=25273&page=table.cs, a nebo naopak se to chová plně v souladu s tím jak to máte nadefinované.

Těžko poradit, když přímo nevidět, a zkoušet se mi to nechce :)

Martin

Dne pátek, 3. srpna 2012 8:18:31 UTC+2 Dali napsal(a):

[Dali]

Tak problém s adresováním buňky jsem vyřešil pomocí funkce INDIRECT. Teď už se vzorec nepřepíše ani v případě, že se nad adresovanou buňkou vloží nový řádek.

Konkrétní zápis je =INDIRECT("List!A7") . To zaručí, že při vložení nového řádku třeba mezi řádek 3 a 4 nedojde k automatické úpravě odkazu ve vzorci z A7 na A8, ale zůstane odkaz na A7. Možná je to kanón na vrabce, ale na nic jednoduššího jsem nepřišel...

Delší vzorce se díky tomu stávají celkem nepřehledné: =IF(INDIRECT("Hodnoty!B7")="";"";INDIRECT("Hodnoty!B7")-INDIRECT("Hodnoty!B6"))

Bohužel se objevil jiný zádrhel. Pokud chci vzorec roztáhnout na více řádků, chová se obsah INDIRECT jako absolutní odkaz = neupravuje automaticky číslo řádku ve vzorci. Přepisovat to ručně třeba na 100 řádků a v každé buňce 3 hodnoty je nereálné. To už raději použiji běžné adresování a až mi webový formulář nasbírá na prvním listu více dat, roztáhnu na druhém listu vzorce dolů...

Existuje něco mezi tím? Tak, aby odkaz na buňku nebyl ovlivňován děním okolo zdroje dat na který vzorec ukazuje, ale při kopírovaní samotného vzorce k automatické úpravě docházelo? Šlo by třeba použít funkci ROW(), která vrací číslo aktuálního řádku? Zkoušel jsem něco ve smyslu "Hodnoty!B(ROW())", ale bez úspěchu.

Díky za jakýkoli tip ;-)

Dali

Dne čtvrtek, 2. srpna 2012 21:18:11 UTC+2 Dali napsal(a):

Dobrý den,

[Dali]

Tak problém s rozkopírováním INDIRECTu jsem vyřešil funkcí OFFSET. Celé mi to přijde jako drbání pravou rukou za levým uchem, ale funguje to :-).

Mám-li to shrnout, funkce INDIRECT zabezpečuje to, že vložení nového řádku z webového formuláře do listu "Hodnoty" neposune vzorce na dalším listu (nedojde k vynechání výpočtu s nově vloženým řádkem), ale zároveň díky funkci OFFSET lze vzorce na druhém listu rozkopírovat dolů, aniž by bylo nutné každý řádek vzorců ručně upravovat. Fuj.

Pokud někdo přijde na jednodušší způsob, budu rád. Přitom by stačilo, aby Google z webového formuláře neukládal data jako nový řádek, ale jen naplnil daty již existující prázdný řádek.

=IF(OFFSET(INDIRECT("Hodnoty!B1");ROW()-1;0)="";"";(OFFSET(INDIRECT("Hodnoty!B1");ROW()-1;0)-(OFFSET(INDIRECT("Hodnoty!B1");ROW()-2;0)))) 

Dne sobota, 4. srpna 2012 13:37:47 UTC+2 Dali napsal(a):

[Kragork Krag]

Právě řeším stejný problém. Mohl bys poslat výsledek tvého zkoumání?

Snažím se vycházet z toho, o čem jsi zde psal. Jako závěr uvádíš =OFFSET(INDIRECT("Hodnoty!B1");ROW()-1;0), což bohužel nefunguje. Vypadá to na chybu v ROW.

Byl bych rád, kdybys mi poradil. Díky

Dne pondělí, 13. srpna 2012 13:45:37 UTC+2 Dali napsal(a):

[Kragork Krag]

Tak sice nechápu, jak ti to funguje, ale už jsem to také vyřešil. místo problematické části vzorce jsem prostě použil odkaz na čísla ve vedlejším sloupci, kde jsou pěkně od jedné do nekonečna.

Výsledek vypadá takto: 

=OFFSET(INDIRECT("formulář!B2");C7-1;0) C7=1

=OFFSET(INDIRECT("formulář!B2");C8-1;0) C8=2

atd

Ale pomohl jsi mi, kdybys mi to neukázal, ty dvě funkce bych asi nenašel. Výsledek své práce jsi se přece vůbec nemusel psát. Díky.

Dne pondělí, 13. srpna 2012 13:45:37 UTC+2 Dali napsal(a):

[Tomáš Zelený]

Ahoj,

Je škoda, že nejde nastavit, aby se odpovědi zapisovaly do stávajících řádků. Naštěstí jsem našel video, jak tento problém vyřešit (bez funkcí INDIRECT a OFFSET).

http://www.youtube.com/watch?feature=player_embedded&v=ul4PguzpCOY

Stačí si vytvořit nový list a do vybrané buňku napsat funkci:   =IMPORTRANGE("(číslo tabulky, dokumentu, naleznete v URL)"; "(Rozsah)") Tato funkce importuje vybraný rozsah z jiného dokumentu do naší tabulku, pro nás to bude paradoxně tatáž tabulka. Jestli používáte "staré" Tabulky Google, mělo by vše fungovat, já jsem si ovšem před pár dny aktivoval Nové Tabulky Google, kde tato funkce není ještě funkční. Navíc URL adresy nových tabulek mají očividně jiný formát, takže ani nevím, co z ní zkopírovat. https://support.google.com/drive/answer/3541068?hl=en

[Stanislav Dolejší, Progresia]

Dobré poledne,

jen update, nové G tabulky již jsou i v ImportRange o.k.

jen se mění tvar, že místo keye se zadává celý odkaz na dokument

např: 

https://docs.google.com/spreadsheets/d/154FczTxZjFldB-O1jWegDE58N2L6_ipJM45ndBepAys/edit#gid=0 -odkaz již v novém formátu (Nové GTabs), ale funguje i na původní verzi dokumentů https://docs.google.com/spreadsheet/ccc?key=*klíč*&usp=drive_web#gid=2

v Import range: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/154FczTxZjFldB-O1jWegDE58N2L6_ipJM45ndBepAys/edit#gid=0" ;"test!A1:C3") - tento sheet je verejny na cteni takže je možné si to ImportRangenout z něj na test

$.

Dne středa, 5. února 2014 22:18:26 UTC+1 Tomáš Zelený napsal(a):

[Tomas Brejsek]

Zdravim, dotaz je sice jiz hodne stary, ale nedavno jsem hledal reseni stejneho problemu.

Pokud se jeste nekdo takovy najde kdo shani radu, tak hledejte funkci "arrayformula". Vzorce pak funguji i po vlozeni dat z formulare.

Tomas

Dne čtvrtek 2. srpna 2012 21:18:11 UTC+2 Dali napsal(a):