GoogleSpreadsheets/Excel - vyhledání maxima v dynamických sl

Programy pro práci s textem, tabulkami, prezentacemi

Moderátor: Moderátoři Živě.cz

Odeslat příspěvekod Ertefol 16. 6. 2017 14:54

Ahoj,
řeším zdánlivě trivální problém:

V tabulce mám v řádku vždy datum+číslo skladu, a ve sloupcích hodnoty pro daný výrobek na ten konkrétní den a sklad.

Ukázkový spreadsheet
https://docs.google.com/spreadsheets/d/ ... sp=sharing

Potřebuji najít maximální hodnotu za celý měsíc pro daný konkrétní sklad a výrobek. Výrobek budu vybírat dynamicky z menu a podle toho určím sloupec, v daném sloupci potřebuji vyhledat maximum na řádcích, které splní podmínku (tedy půjde o správný sklad). Vrátit chci jen hodnotu daného maxima.

Napadá vás někoho, jakou kombinací funkcí na to jít? MAXIF bych použil pro kontrolování označení skladu, ale jak dynamicky určit sloupec, ze kterého se má brát?

Díky za radu
PC, Wii U, 3DS
"Slabé pohlaví je silnějším pohlavím z důvodu slabosti silnějšího pohlaví k slabšímu pohlaví." - Přísloví
Jakub Kovář, externí redaktor Games, LEVELu, Pevnosti
Ertefol
Junior
Uživatelský avatar

Odeslat příspěvekod Ertefol 19. 6. 2017 13:20

Kdyby tedy někdo řešil něco podobného, tak jsem to vyřešil:
Kód: Vybrat vše
=ARRAYFORMULA(MAX(KDYŽ(sloupec_se_sklady_v_datech=vybraný_sklad;KDYŽ(řádek_s_výrobky_v_datech=id_vybraného_výrobku;oblast_dat)))
PC, Wii U, 3DS
"Slabé pohlaví je silnějším pohlavím z důvodu slabosti silnějšího pohlaví k slabšímu pohlaví." - Přísloví
Jakub Kovář, externí redaktor Games, LEVELu, Pevnosti
Ertefol
Junior
Uživatelský avatar

Odeslat příspěvekod tomas.kacha 20. 6. 2017 13:45

Bohužel jsme se nedostal k reakci, takže jste si už mezi tím vyřešil. Ale podobné řešení pro uživatele Excelu přikládám.

V Excelu není funkce ARRAYFORMULA, ale dá se postupovat podobně přes maticové vzorce. Mám anglický Excel, takže používám anglické vzorce.

{=MAX(IF((MONTH(pole_sloupce_mesice)=mesic)*(pole_sloupce_skladu=sklad)*(pole_radky_produktu=produkt);pole_rozsahu_hodnot))}

Například:
{=MAX(IF((MONTH(A2:A37)=$C$42)*(B2:B37=$B$43)*(C1:I1=$B$44);C2:I37))}
kde
- MONTH(A2:A37)=$C$42 je podmínka řešící, za jaký měsíc mě to zajímá. Rozsah A2:A37 značí sloupec s datumy a v buňce $C$42 si volím měsíc
- B2:B37=$B$43 je druhá podmínky pro sklad, kde ve sloupci a buňkách B2:B37 jsou jednotlivé sklady a v buňce $B$43 si volím jaký sklad mě zajímá
- C1:I1=$B$44 je poslední podmínka, řešící produkt, kde v řádce C1:I1 jsou produkty a v buňce $B$44 si volím jaký produkt mě zajímá
- C2:I37 je rozsah hodnot, ze kterých hledám

Podotýkám, že pokud jste v maticových vzorcích v Excelu nováček, tak složené závorky se nepíšou, ale maticový vzorec se po zadání potvrzuje pomocí CTRL+SHIFT+ENTER.

P.S. Nevím z jakého systému, nebo kdo vám data dává. Ale lepší by bylo mít výstup jako unikátní řádky pro každý den, sklad a produkt. Pak nemusíte řešit složité vzorce, ale vystačíte si s kontingenčními tabulkami.

-- 20. 6. 2017 14:55 --

Plus ještě obrázek k mému příkladu a mé tabulce
matrix_sklad.jpg
tomas.kacha
Kolemjdoucí


Kdo je online

Uživatelé procházející toto fórum: Žádní registrovaní uživatelé a 0 návštevníků