< návrat zpět

MS Excel


Téma: Dynamicky se menici pocet radku s vzorci rss

Zaslal/a 29.7.2017 22:24

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ěď >

#037005
avatar
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
#037006
elninoslov
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: zip37006_flt.zip (32kB, staženo 7x)
citovat
#037007
avatar
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
icon #037008
admin
Dobrý den.
Info k přílohám http://wall.cz/index.php?m=topic&id=8351citovat
#037010
elninoslov
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: xlsx37010_example2.xlsx (109kB, staženo 6x)
citovat
#037012
avatar
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: xlsx37012_clock-change-leap-year-check.xlsx (16kB, staženo 10x)
citovat

Uživatelské menu

Nejste přihlášen(a)
avatar\n

Menu

On-line nástroje

Formulář Faktura

Formulář Faktura III

Oblíbený formulář Faktura byl vylepšen a rozšířen. Formulář faktura III
Více se dočtete zde.

Aktivní diskuse

Pridanie prvku ActiveX do nového riadku VBA

elninoslov • 20.1. 11:18

Vypisování obsahu buněk z tabulky

evelin • 20.1. 10:00

Vyhľadávanie a import z txt cez VBA

Bill • 20.1. 9:24

Vypisování obsahu buněk z tabulky

elninoslov • 19.1. 23:23

vyhledat hodnotu ve sloupci a přiřadit z jiného

elninoslov • 19.1. 22:57

Vypisování obsahu buněk z tabulky

evelin • 19.1. 22:22

Součet více hodnot v inputboxu.

xlnc • 19.1. 20:02