MS Excel

Dobry den,

v podstate mam dva dotazy.

Prvni se tyka maticoveho vzorce pro vyhledavani vice zaznamu v seznamu odpovidajicim hledanemu kriteriu.

V sloupci A jsou datumy a v sloupci B napr. ANO/NE pro konkretni den (at uz to ma znamenat cokoliv)

Sloupec A Sloupec B
01/07/2017 NE
02/07/2017 ANO
03/07/2017 NE
04/07/2017 NE
05/07/2017 ANO
06/07/2017 NE

Rekneme, ze mesic cervenec ma 31 dnu, tedy dany seznam ma 31 radku pro kazdy den v mesici.

Do pole D1 vlozim nasledujici maticovy vzorec:

=INDEX($A$1:$A$31, SMALL(IF($B$1:$B$31="ANO", ROW($A$1:$A$31)-ROW($A$1)+1), ROWS($A$1:A1)))

ktery mi vyhleda a v jednotlivych radich pod sebou vypise vsechny datumy, pro ktere plati kriterium ANO.

Vicemene vzorec chapu, ale neni mi jasne "ROWS($A$1:A1)" nakonci coby argument funkce SMALL. Co presne tato cast dela a jak funguje? Dekuji za objasneni a jednoduche vysvetleni.

Druhy dotaz se tyka v podstate tohoto a jemu podobnych problemu. V tomto pripade vim, ze je radku 31, ale muzu mit data, kde bude radku treba 1000 a nevim kolik z nich bude splnovat hledane kriterium.

Je nejak mozne dynamicky urcit, kolik danych vyhledanych zaznamu bude a na zaklada tohoto poctu automaticky v sloupci D vzorec pro jejich vypsani (viz. vyse) rozsirit? Takto musim sam manualne roztahnout danou maticovou funkci roztahnout na nejaky max rozsah, abych mel jistotu, ze budou zobrazeny skutecne vsechny vystupni zaznamy. Ale pro pripad, kdy bude zaznamu skutecne hodne, tak jak to udelat dynamicky, aby to poznalo, ze jich je treba 342 a do 342 radku pod sebou tuto funkci zkopirovat. Aby se to dela automaticky a vzdy se to uzpusobilo danemu vyhledavanemu kriteriu a vystupu na zaklade vstupnich dat. Jde to nejak udelat?

Predem moc dekuji za odpoved.

S pozdravem
Ondra

Zaslat odpověď >

OndraEx(29.7.2017 22:46)#037005

Jen jeste pro upresneni meho predchoziho dotazu. V podstate jde o verzi VLOOKUP pro "n" nalezenych zaznamu.

U dynamicky generovane poctu vypisu se mi jedna o neco ve smyslu "FOR (i=0; i<=COUNTIF(B:B=ANO); i++ { DO Vypis }"

Nejlepe bez VBA, ciste pomoci funkci Excelu, pokud by to slo.

Diky predem za odpoved

S pozdravem
Ondracitovat

elninoslov(29.7.2017 23:28)#037006

Niekoľko príkladov. Ten koniec mat. vzorca hovorí, ktorú položky v poradí od najmenšej (SMALL) ma vypísať. V riadku D1 hodí ROWS($A$1:A1) = 1, v riadku D2 hodí ROWS($A$1:A2) = 2, v riadku D3 hodí ROWS($A$1:A3) = 3... stačilo by ale ROW(A1), a postupným kopírovaním do ďalších riadkov sa výsledok oboch verzii mení na poradové číslo 1,2,3,... a to je účel.
Ak nebudete vedieť, čo som ktorou verziou myslel, alebo ako som ju dosiahol, píšte.

Příloha: zip

37006_flt.zip (32kB, staženo 58x)

citovat

OndraEx(30.7.2017 3:21)#037007

Dekuji Vam za vysvetleni, konecne mi to docvaklo a chapu proc a jak celkove vzorec funguje.

Jste neuveritelny, co se tyce rychlosti a efektivity odpovedi spolecne s prehrselem praktickych prikladu v priloze! Dekuji.

K tomu dynamickemu generovani v Excelu bych mel jeste jeden doplnujici dotaz, i kdyz se uz ne uplne tyka predchoziho prikladu s vyhledavanim na zaklade kriteria.

V priloze je soubor, coby ukazka co mam vlastne na mysli. Parametrem pro dynamicke vygenerovani podkladove tabulky, ktera bude nasledne naplnena vypocty, by mely byt pouze dva datumy v bunkach C3 a C4. Jsem schopen si zjistit, zda-li se jedna o normalni den, nebo zmenu casu na letni ci zimni cas a tedy zda-li ma den 24, 23 nebo 25 hodin. (tyto zjisteni pro oba datumy nejsou soucasti prilozene ukazky, v pripade potreby muzu doplnit) A na zaklade tohoto zjisteni (oba dny se budou kazdy den postupne menit vzdy na DNES a ZITRA) bych potreboval nejak dynamicky vygenerovat tu tabulku s odpovidajicim poctem radku - tedy 48 (2*24) pro dva normalni dny, nebo s 1 radkem mene (47) pokud je jednim z dnu zmena casu v breznu, pripadne s 1 radkem vice (49) pokud je jednim z dnum zmena casu v rijnu.

Idealni stav by byl, pokud by se vyplnily ty dva datumy a Excel by prichystal potrebnou tabulku s daty a hodinami odpovidajicimi tem zadanym dnum a vse by tak bylo automaticky pripraveno pro dalsi vypocty.

Jde tohle nejak dynamicky generovat bez VBA?

Snad jsem to vysvetlit aspon trochu srozumitelne, pripadne to doufam bude mozne pochopit z prilozeneho vzoroveho souboru.

Predem dekuji za odpoved.

S pozdravem
Ondra

PS: Tak zjistuji, ze nikde nevidim, jak ke zprave pripojit prilohu. Tak jsem to nahral alespon sem: https://uloz.to/!0xEcFoDjR10K/example-xlsxcitovat

admin(30.7.2017 9:01)#037008

Dobrý den.
Info k přílohám http://wall.cz/index.php?m=topic&id=8351 citovat

elninoslov(30.7.2017 15:05)#037010

No tak takéto niečo pôjde asi iba nachystaným vzorcom na dostatočný počet riadkov, alebo makrom.

EDIT: Teraz si uvedomujem, že vlastne neviem, ako sa správne počíta na ktorý deň padne posun časov. Myslel som, že je to posledný celý víkend v marci a októbri, ale zdá sa, že to tak nieje. Možno to bude 4 víkend v mesiaci (čo nieje to isté). Keď bude chuť, si to ešte možno pohľadám, zatiaľ si to teda v CHOOSE/ZVOLIT v F3:F1002 zmente na ten svoj výpočet.
1 param - normálne pripočítanie hodiny
2 param - marec
3 param - október
4 param - nič

Příloha: xlsx

37010_example2.xlsx (109kB, staženo 50x)

citovat

OndraEx(30.7.2017 16:27)#037012

Dobry den,

dekuji za Vasi odpoved, na soubor se v co nejkratsi mozne dobe podivam a v pripade neporozumeni reseni ozvu.

Abych Vam to zjednodusil, v priloze je excel se vzorci, jak resim kontrolu prestupneho roku a zmeny casu na letni/zimni. Snad se Vam to bude hodit.

Jeste jednou diky a dam vedet, jak chapu Vase reseni.

S pozdravem
Ondra

Příloha: xlsx

37012_clock-change-leap-year-check.xlsx (16kB, staženo 50x)

citovat

Téma: Dynamicky se menici pocet radku s vzorci

Uživatelské menu

Menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje