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

#1 14. 8. 2017 22:34:42

dech
Člen
Registrace: 1. 10. 2008
Příspěvků: 150

array funkce - filtrování?

Ahojte,
pokouším se použít ARRAY "formu" (ctrl+shift+enter) funkcí k filtrování dat.

Vím že existují automatické filtry apod....no prostě řekněme, že by se mi hodilo toto řešení...ale nevím jak k němu dospět.

Myslím, že ukázka v souboru je všeříkající.
https://uloz.to/!DNJDn7xP5WrH/ukazka-ods

diky za nápady.

(soubor je vytvořen v LO 5.2.7.2)


LO 6.1.2.1. (x64)  WIn 8 64bit  16GB RAM

Offline

#2 15. 8. 2017 06:06:11

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

Re: array funkce - filtrování?

dech napsal(a)

pokouším se použít ARRAY "formu" (ctrl+shift+enter) funkcí k filtrování dat.

Vím že existují automatické filtry apod....no prostě řekněme, že by se mi hodilo toto řešení...ale nevím jak k němu dospět.
(soubor je vytvořen v LO 5.2.7.2)

     Celkem chápu problém, ale nechápu zda chcete pomoci s filtrem, nebo s maticovým vzorcem. Takže nejsnadnější vytvoření filtrů je pomocí AUTOMATICKÉHO FILTRU. Vyberete celou tabulku a navolíte DATA > AUTOMATICKÝ FILTR (dostanete možnost zahrnout záhlaví. Pak už stačí postupně aktivovat jednotlivá filtrování ve sloupcích. Nejprve vyberete sloupec kde je výraz auto (zůstanou jen položky s autem - ostatní se skryjí). Následně navolíte sloupec s typem paliva (benzín) a zůstanou jen auta s pohonem na benzín. Podobné je to s ostatními filtry. Snad je vhodné doplnit, že toto je standardní práce v kontingenční tabulce. Máte například možnost vybrané výsledky vyexportovat do jiného umístění (listu a podobně).


     S maticovými vzorci je to maličko složitější. Už jste zřejmě zjistil, že maticové vzorce buď vytvoří sloupec kam vrátí výsledky z řádku, nebo výsledky sloučí do jednoho výsledku (součet, součin atd.) Pak tam tedy vznikají například ty nechtěné nuly. Vy ale požadujete víc. Měly by se soustředit (seřadit) požadované výsledky pod sebe.
     Takto soustředit výsledky umí jenom funkce RANK (jsou celkem 3 druhy). Lze ji zadávat také jako matici. S tím mohou být problémy (netestoval jsem to) ale funguje to perfektně pokud zadáte funkci na určitý úsek. Seřadí data podle potřeby. Takže například k Vašemu řešení přidejte ještě sloupec s funcí RANK a bude to zřejmě ono.
     Snadnější je snad jenom řádkové řešení IF(AND(B1 = "auto";C1="benzín")=1;A1;"") které vykopírujete do pomocného sloupce. Následně dostanete hodnoty bez nul a ty soustředíte pomocí RANK.

Editoval neutr (15. 8. 2017 06:08:41)


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

Offline

#3 15. 8. 2017 08:36:53

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

Re: array funkce - filtrování?

Jde to

(V případě složitějších vzorců se vyplácí výpočet nejprve vytvořit v listu pomocí obyčejných vzorců a až to bude fungovat, tak to naskládat do jednoho vzorce. V pomocném sloupci místo nul bude "velké" číslo, správné hodnoty vyberete funkcí small a kontrolujete, jestli máte vše, nejlépe přes countif. Maticový vzorec postavíte stejně.),

ale ...  složitý vzorec, pomalé, pole výsledků stejně zabere dost velkou oblast, ...

Pokud uvažujete stovky tisíc řádků, je to spíše dotaz na base forum.
Jinak stejnou službu poskytnou normální filtry.
Také lze použít kontingenční tabulku (použít jem řádková pole a odfiltrovat nuly).

Offline

#4 27. 9. 2017 19:22:20

dech
Člen
Registrace: 1. 10. 2008
Příspěvků: 150

Re: array funkce - filtrování?

ahojte pánové
omlouvám se za neodpovědi. 
děkuji za nápovědy!
snažil jsem se vaše napovědy použít - ale pořád mě můj problém navádí směrem k mému řešení.
automatický filtr znám - ale ten se hodí pro filtrování samotné DB. já potřebuju vytvořit nějaké formuláře/user interface/něco pro NEajtáky, které nejsou tak na první pohled komplikované.
S kontingenčníma tabulkama jsem za jedno, řekl bych že je zvládám, ale mám jich už tolik že spuštění makra na jejich aktualizaci nachvíli zasekne LO. Proto se snažím nahradit je nějakými vzorci které budou mít v podstatě jejich schopnost - vyfiltrovat a SDRUŽIT - neboli pokud výsledkem byl výraz který by se opakoval, tak jej KT zobrazí jen 1x.
Mezitím jsem se někam posunul - a to šeredně blízko toho co hledám, ale ještě mi chybí vyřešit jeden zádrhel  -viz. odkaz
https://uloz.to/!6bm7TBKVhxBe/ukazka2-ods


LO 6.1.2.1. (x64)  WIn 8 64bit  16GB RAM

Offline

#5 28. 9. 2017 07:44:04

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

Re: array funkce - filtrování?

Popravdě já bych to sestrojil jinak. Ale důležitý je obsah dotazu. Ten v příloze je, ale není dost specifikovaný. Raději upřesním to co si myslím, že je předmětem řešení :


     Ve skloupci "J" je zadán vzorec pro sestupné třídění. Obsahem dotazu je omezení vzorce na určitý počet předem zadaných výsledků - nikoliv na všechny relevantní.
     Omezení má být realizováno zápisem do buňky L8. Zřejmě sem přijde počet údajů. Zbytek polí ve sloupci "J" bude zneviditelněn.
     Řešení podle vzoru obsahuje za těchto předpokladů syntaktickou chybu:
=INDIRECT("H"&LARGE(IF(H9:H29="-";0;ROW(H9:H29));ROW()-ROW($L$8)))
Konkrétně ROW($L$8) nevrací obsah buňky L8, ale číslo vlastního řádku - tedy číslo 8. Takže po úpravě maticového vzorce by to mělo být takto :

=INDIRECT("H"&LARGE(IF(H9:H29="-";0;ROW(H9:H29));ROW()-$L$8))

     Ovšem ani takto by to funkci omezení "sdola" neplnilo podle představ. Obsah buňky totiž krátí rozsah každého jednoho řádku od začátku tedy "shora".


     Takže je nutno upřesnit. Stačí Vám tato úprava, nebo pořebujete vytvořit omezení "sdola"?



PS :
     Celkem mi to moc smyslu nedává. Proč se řadí barvy podle názvu? Nemá tam být například odkaz na konkrétní řádek "databáze"?
     Myslím že ta data jsou co do obsahu vyplněna zástupnými charakteristikami. Spíš bych tam místo barvy viděl datum zapůjčení, nebo například datum splatnosti, technické kontroly a podobně.


     Bývám kritizován dost často za to, že se vrtám v detailech. To je ale nutné když mám pochopit skutečnou potřebu. Často se dopracuji k tomu, že příklad je skutečně jen simulací skutečné potřeby. To je navýsost správné, ale může to vést k podstatným vadám při řešení.
     Zde například tuším, že půjde podle počtu položek nejspíš o seznamy které vedou například úřady (dopravka), nebo prodejci (zboží), exekutoři (zabavený majetek k prodeji), nebo i soudy (znalce, přísedící) nebo také autoopraváři (díly podle typu) a sklady pro vše možné.......
     Takže celkem nevadí zadání, ale chyby plynoucí z nesprávné substituce. Velmi důležitý je i celkový objem dat. Sešit sice uveze hodně, ale když je dat v bajtech velké množství zpomaluje se zpracování a může to i občas kleknout. Nakonec už sešit další data nevezme, spadne a nepůjde otevřít vůbec. Musel by se vykuchat obsah z XML.


     V zadání uvádíte, že jsou problémy i s makry (snad vybavení kontingenčních tabulek). Ale možné jde i o jinak používaná makra. Celkem je jisté, že že makry lze ošetřit mnohem více objemů protože vlastní vzorce zatěžují sešit dost výrazně (ačkoliv když fungují - jsou velice efektivní).
     Takže když se data pohybují v řádech desetitisíců buněk (řádky x sloupce) přestává být reálné použití vzorců. Musí se jít do maker, nebo do databáze.

Editoval neutr (28. 9. 2017 08:36:10)


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

Offline

#6 28. 9. 2017 14:21:39

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

Re: array funkce - filtrování?

Smysl mi to nedává. Ukázka ukazuje jeden "problém". To "setříděné" ve skutečnosti znamená vypsané v opačém pořadí. Třídit vzorcem texty jen pomocí funkcí listu snadno nejde - je nutno převést text na číslo, tj. srovnat velikost, odstranit diakritiku, "opravit", převést na číslo (pomůže SERIESSUM), ...
V jednom vzorci to nedáte.

Zřejmě to má být větší aplikace, je nutné k tomu přistoupit jako k regulárnímu programování. Doporučuji databázi, pokud to z nějakého důvodu není možné, pak makra, v nejhorším případě funkci, která vrátí požadovaný výstup. Při tom využívejte vestavěné funkce (rozšířený filtr vybere unikátní barvy).

Ověřte také makra. Přímočaré postupy běžně uváděné na internetu jsou často pro větší objemy dat velmi neefektivní. V případě kontingenčních tabulek lze také vytvořit jen několik a konkrétní výstup dotvořit pomocí funkce GETPIVOTDATA.

Offline

#7 28. 9. 2017 16:19:45

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

Re: array funkce - filtrování?

Otestujte toto Úprava_verze.


     Dnes odjedu a do neděle se ke svému compu nedostabu. Potřebovalo by to upřesnění a tak jsem jen hádal co opravdu potřebujete.


     Ten systém s maticovými vzorci by šel zřejmě upravit, ale nikoliv nacpat do jednoho vzorce. Zadání omezovače pro matickovou funkci LARGE je nesmysl. stačí tam pouze
=INDIRECT("H"&LARGE(IF(H9:H29="-";0;ROW(H9:H29));ROW()-8)))
- ve vzorci LARGE se zadává číslo pořadí od největšího = 1.
Potom 1. pořadí řádek  9-8 = 1,
Potom 2. pořadí řádek 10-8 = 2,
Potom 3. pořadí řádek 11-8 = 3,
.................................
     Udělal jsem trošku jiný postup. Místo Boolean ve filtru vracím přímo barvu a tu načtu v prvním sloupci podle "konsolidace" která už obsahuje unikátní hodnoty barev. Udělal jsem dost možností pro více barev a podle nich načítáme počet (Countif). Vedle ve sloupci podle Countif stanovíme pořadí barev. Ovšem jestli potřebujete tohle nebo něco jiného už odhadnout nedokážu.
     Pokud je nějaký vyhodnocovaný parametr nulový (prázdný) tak vzorce zobrazí prázdnou buňku. Nasimuloval jsem tam pole od řádku 9 do řádku 20.000 abyste viděl že to celkem ještě jde, ale je to už na hranici. Věřte že tohle už by bylo na jiný systém nežli vzorce.


     Měl byste se spíš zabývat tím co popisuje "lp." který má bohaté zkušenosti zejména pokud Vám jde o funkčnost a ne jen o pochopení možností.


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

Offline

Zápatí