Fórum pro uživatele kancelářského balíku OpenOffice | LibreOffice
 

#1 18. 2. 2016 12:50:10

PetrCHS
Člen
Registrace: 14. 3. 2014
Příspěvků: 14

Vypsání dat podle paramteru jednoho sloupce (použít GETPIVOTDATA ?)

Dobrý den, / Ahoj,

řeším teď pro hodně z Vás zřejmě triviální problém, ale nedokážu ani po přečtení x for a článků nalézt řešení.
Jedná se mě o to že mám na listu 1 tabulku, kde jsou vkládány do řádku data ("Název  projektu" "Datum" "Castka" "Polozka").
Dále z toho mám kontingenční tabulku (tam vidím pro jednotlivé Projekty sloučené data). Do teď ok.

Problém:
Dále mám list pro každý Projekt zvlášť a zde bych potřeboval z listu 1 vypsat pouze položky které mají ve sloupci "Název projekt" vyplněn daný projekt.

Zkoušel jsem to přes tu kontingenční tabulku přes GETPIVOTDATA, ale nějak se mě nedaří z toho dostat vůbec nějaká data a mám pocit, že zvolená cesta je špatná. Poradil by někdo?

Díky moc!
Petr

Offline

#2 18. 2. 2016 12:58:16

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 520

Re: Vypsání dat podle paramteru jednoho sloupce (použít GETPIVOTDATA ?)

Dobrý den, dělám to standardně pomocí funkce vlookup. V případě kopírování vzorce či celého listu nezapomeňte na stringy(dolary ..$). Napište pokud tento nástřel nestačí.

Editoval ludviktrnka (18. 2. 2016 12:59:19)


LibreOffice 5.2.2.2

Offline

#3 18. 2. 2016 13:44:47

PetrCHS
Člen
Registrace: 14. 3. 2014
Příspěvků: 14

Re: Vypsání dat podle paramteru jednoho sloupce (použít GETPIVOTDATA ?)

To jsem zkoušel také, ale nedokážu přijít na to jak docílit aby každý řádek byl jen jednou. Vysvětlím.
V listu jedna je 100 řádků, které obsahují napřeskáčku x projektů, pokud použiji VLOOKUP (=VLOOKUP($A$1;Sheet1.$B$4:$F$29;4;0)) a roztahnu tento vzorec. Vždy se mě opakuje pouze první nalezený řádek. Jak docílit aby tato funkce vypsala všechny řádky, které patří danému projektu a to jen jednou?

Offline

#4 18. 2. 2016 13:54:19

neutr
Člen
Registrace: 8. 3. 2007
Příspěvků: 2,678

Re: Vypsání dat podle paramteru jednoho sloupce (použít GETPIVOTDATA ?)

No ten výpis reprezentuje více položek od každého projektu a pravděpodobně i když je kontingenční tabulka seřazena, nemusí být položky bezprostředně za sebou v řádcích.
     Problém je řešitelný nejlépe maticovým vzorcem - například VLOOKUPem jak uvádí pan Trnka. Zadejte klasicky dialogem a při zadávání zaškrtněte pole "matice". Vybraný by měl být celý úsek ("Název  projektu" "Datum" "Castka" "Polozka"). Jinak pokud zadáte bez dialogu je nutné matici aktivivat pomocí Ctrl+Alt+Enter.


     Jde to také klasicky bez "maticových vzorců". V listu jednotlivého projektu zadáte do prvního sloupce nejprve COUNTIF, a následně MATCH tolikrát - kolikrát najde COUNTIF výskyt řetězce. Trik je v tom, že Match vrátí první, respektive poslední řádek s tímto obsahem. Takže pak to vypadá například takto :
=COUNTIF("projekt_1) = výsledek například 4 + následuje dotaz typu MATCH. Nyní víme, že potřebujeme pouze 4 dotazy tohoto typu.
1. =Match(celé pole od začátku do konce) = výsledek první výskyt například řádek 12.
2. =Match(řádek 13 až do konce) = výsledek druhý výskyt například řádek 28.
3. =Match(řádek 30 až do konce) = výsledek třetí výskyt například řádek 36.
4. =Match(řádek 37 až do konce) = výsledek čtvrtý výskyt například řádek 52.
     Pokud dáte do sloupce "A" stratovní řádek, bude výsledný řádek ve sloupci "B" a v "C" bude konstanta - poslední řádek na listu1 například 1000.
A1 - dáme sem COUNTIF který = 4
A2 = 1, B2 = 12, C2 = 1000,
A3 = 13, B3 = 28, C3 = 1000,
A4 = 29, B4 = 36, C4 = 1000,
A5 = 37, B5 = 52, C5 = 1000,
     Například v buňce A3 je vzorec = B2+1. Tím zaručímě start vzorce MATCH hned za výskytem předchozí hodnoty. V buňce B3 je samosebou MATCH - úsek čtený z A3 s koncem na řádku 1000.


     Myslím že toto byste měl už zvládnout sám i bez matice. Ale v tomto případě je matice šikovnější protože klasicky musíte načítat data ze 4 buněk stejného řádku zdroje - to při správně postavé matici nemusíte.


PS:
     Ty vzorce MATCH vrací relativní pořadí, takže ve vzorci musíte ještě přičíst k výsledku předchozí buňku "B". Výsledek vytvoříme funkcemi pro návrat obsahu - například obyčejný VLOOKUP který mačte část adresy z MATCH ap.
     Vzorec MATCH lze upravit do jediné buňky (není potřeba názorného řešení - to uvádím jen pro pochopení toho co má udělat vzorec - tedy pro začátečníky). Takže ten si přečte nejprve hodnotu předchozího řádku a začne hledat na řádku dalším. Ve výsledku pak přidá předchozí hodnotu "B" aby výsledek byl v absolutním (správném) určení řádku zdroje.

Editoval neutr (18. 2. 2016 14:08:23)


Pokud je Váš problém vyřešen, označte prosím svůj příspěvek za "VYŘEŠENÝ"
Zlepšíte tak orientaci na fóru při vyhledávání řešení problémů
JAK OZNAČIT TÉMA ZA VYŘEŠENÉ

Offline

#5 18. 2. 2016 13:57:23

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 520

Re: Vypsání dat podle paramteru jednoho sloupce (použít GETPIVOTDATA ?)

aha, tak to je horší. Výpis lze provést pomocí filtru (na to by bylo asi nejlepší makro) nebo pomocí zmíněné kontingenční tabulky s příslušným filtrem. Já to někdy dělám tak že kvůli nevhodné grafice si kontingeční tabulku dám bokem a do tabulky na listu (který chci tisknout) si hodnoty prostě překopíruji. (tedy natvrdo =XY123)

List pro každý projekt zvlášť - nevím přesně proč to potřebujete, ale většinou to je chybný postup. Lepší je mít jeden list a "výpis" daného projektu řešit pomocí volby projektu. Tyto aplikace často řeším makry, ale lze i použít funkci vlookup s tím že do základní tabulky přidáte pomocný sloupec (třeba skrytý), kde ke každému řádku s příslušným právě vyplňovaným projektem přiřadíte pořadové číslo 1,2,3 ... =if(B2="můj projekt";max($A$1:A1)+1;0). Výpis pak uděláte tak že vyhledáváte čísla 1,2,3,... a vypisujete potřebné údaje. Vyhledání čísla lze definovat volbou row(), nabývá hodnoty na níž je vzorec umístěn, čili v buňce A2 budu hledat row()-1


Ještě ke stavbě kontingenční tabulky - pokud ji chcete použít na výpis a nikoli na "kontingenci" tedy sdružení tak je dobré si do základní tabulky přidat identifikátor (například číslo řádku =row()) a to dát potom na první místo výpisu v kontingenční tabulce. Tím se vše hezky vypíše, neboť ID bude jedinečné.

Editoval ludviktrnka (18. 2. 2016 14:19:41)


LibreOffice 5.2.2.2

Offline

#6 18. 2. 2016 14:10:06

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 520

Re: Vypsání dat podle paramteru jednoho sloupce (použít GETPIVOTDATA ?)

Docela jsem zvědav jestli lze udělat výpis pomocí matice, muselo by se to kombinovat se seřazením a nějakým zkrácením aby matice neměla rozměr 100 řádků.


LibreOffice 5.2.2.2

Offline

#7 18. 2. 2016 14:21:23

neutr
Člen
Registrace: 8. 3. 2007
Příspěvků: 2,678

Re: Vypsání dat podle paramteru jednoho sloupce (použít GETPIVOTDATA ?)

Abych řekl pravdu, tak pokud jde o statická data, stačí na každý list zkopírovat celou zdrojovou tabulku, odfitrovat a data irelevantní smazat. To udělat pro všechny projekty. Stav kontrolovat pomocí COUNTIF - to múže zjistit, že ve zdroji je více výsledků, nežli v tabulce projektu a například něco vypíše a zčervená to...Následně lze postup oapkovat vždy když něco ve zdroji přibude, nebo zmizí.
     Na to vemte jed že tohle matice umí. Jde jen o to, jestli to umí klasický VLOOKUP. Maticové vzorce se dají nastait například jako SUM - a dělají totéž co LOOKUP (VLOO, HLOO..). matice umí i řadit pomocí SMALL ap. Jenže vybrané položky asi řadit třeba nebude. měly by byýt zapsány chronologicky a navíc asi není potřeba přetřídění. Půjde spíš jen o sumace.
     Já bych to mohl dohledat, ale "lp." to ví zaručeně - já bych musel laborovat, respektive hledat. Někde mám příklady, ale nevím kde :-(


Pokud je Váš problém vyřešen, označte prosím svůj příspěvek za "VYŘEŠENÝ"
Zlepšíte tak orientaci na fóru při vyhledávání řešení problémů
JAK OZNAČIT TÉMA ZA VYŘEŠENÉ

Offline

#8 18. 2. 2016 14:51:25

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 520

Re: Vypsání dat podle paramteru jednoho sloupce (použít GETPIVOTDATA ?)

hm je fakt že něco takového vám udělá výpis řádků s daným "projektem" odkazoaným v buňce "F1" {=SMALL(IF(B2:B13=$F$1;ROW();"");ROW()-1)} ale rozměr matice je shodný se zdrojem což je trochu nepraktické, nicméně použitelné. Výpis se pak provede pomocí funkce INDEX


ještě trochu vylepšení {=IFERROR(SMALL(IF(B2:B13=$F$1;ROW();"");ROW()-1);0)}


a indexování pak musí začínat if(XY<>0;INDEX(...)


Nakonec mi toto připadá docela dobré: do prvního skrytého sloupce si dáte tu matici {=IFERROR(SMALL(IF($list1.B2:B13=$F$1;ROW();"");ROW()-1);0)} , upravíte hodnoty row() podle umístění Uložit trojhmatem viz výše. Úprava matice nejlépe tak, že ji celou vyberete a stisknete průvodce funkcí.
Výpis projektů pak bude =IF($A2<>0;INDEX($list1$A$1:$C$13;$A2;číslo sloupce);"")

Editoval ludviktrnka (18. 2. 2016 15:15:46)


LibreOffice 5.2.2.2

Offline

#9 18. 2. 2016 15:45:42

lp.
Člen
Registrace: 24. 9. 2009
Příspěvků: 790

Re: Vypsání dat podle paramteru jednoho sloupce (použít GETPIVOTDATA ?)

Obdivuji ty, kdo uhodli co chcete.

Pokud máte kontingenčí tabulku, předpokládám, že sčítáte castky podle něčeho. Zkusil jste dvojklik na některý ze součtů v kt?

Možná to vyřeší problém.

Offline

#10 19. 2. 2016 13:43:01

PetrCHS
Člen
Registrace: 14. 3. 2014
Příspěvků: 14

Re: Vypsání dat podle paramteru jednoho sloupce (použít GETPIVOTDATA ?)

Děkuju za odpovědi. Teď se jdu na to podívat, dám vědět jak to dopadlo ;-).

Offline

#11 19. 2. 2016 14:25:34

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 520

Re: Vypsání dat podle paramteru jednoho sloupce (použít GETPIVOTDATA ?)

tady máte moje návrhy na zhotovení výpisu: https://dl.dropboxusercontent.com/u/146 … BDpisy.ods


LibreOffice 5.2.2.2

Offline

Zápatí