OpenOffice.cz - web věnovaný kancelářským balíkům OpenOffice.org

martan1484

Člen

Registrace: 30. 12. 2010

Příspěvků: 14

Podmíněné MAX

Ahoj,
existuje nějaká možnost, jak vyhledat nejvyšší hodnotu v buňkách podobně, jako funguje funkce SUMIF resp. SUMPRODUCT?
Jde mi o toto: Mám tabulku, v níž si eviduji výkony na kole (datum, čas, vzdálenost, rychlosti atd.). Tu následně „rozsekám" podle roků a měsíců a jedna ze sledovaných je nejvyšší rychlost za dané období.
V současné době používám kombinaci funkcí MAX a INDIRECT, která odkazuje přímo na oblast buněk, kde by se hledané hodnoty měly vyskytovat, nicméně toto řešení je nevyhovující z hlediska nepružnosti funkce INDIRECT - vyžaduje úpravu vzorce v případě vložení řádku.
Je možné tuto nepružnost nějak eliminovat?

Díky předem.

Martin

Ondra.kl

Člen

Registrace: 30. 9. 2008

Příspěvků: 933

Re: Podmíněné MAX

a nebylo by jednodušší místo vzorců použít kontingenční tabulku? Ta umožňuje nejen sčítat ale i hledat maximum a další.

hanus

Člen

Místo Olomouc

Registrace: 29. 11. 2006

Příspěvků: 573

Web

Re: Podmíněné MAX

Nebo pomocí maticových vzorců, např.
=MAX(IF(YEAR(A1:A5)=2011;B1:B5;0))
a vložení do buňky provést pomocí CTRL+SHIFT+ENTER
kde v A1:A5 jsou datumy a v B1:B5 čísla (větší než 0), pak to najde maximum z čísel za rok 2011

Zajímavé je, že pokud do podmínky přídám ještě měsíc
=MAX(IF(AND(YEAR(A1:A5)=2011;MONTH(A1:A5)=1);B1:B5;0))
tak to přestane fungovat - no počkáme si, co na to lp. :-)
Ono to vypadá, jako by AND nezvládalo maticové vzorce, resp. asi vrací TRUE jen tehdy, pokud jsou všechny prvky matice TRUE a nikoli jen ty dané dvojice po řádcích, takže se musí použít jiná konstrukce.

Editoval hanus (14. 1. 2011 19:40:15)

hanus

Člen

Místo Olomouc

Registrace: 29. 11. 2006

Příspěvků: 573

Web

Re: Podmíněné MAX

Tak konečně:
=MAX((MONTH(A1:A10)=1)*(YEAR(A1:A10)=2011)*B1:B10)
a vložit jako maticový vzorec pomocí CTRL+SHIFT+ENTER
ve sl.A jsou datumy, ve sl.B čísla
Místo konkrétních čísel pro měsíc a rok lze vložit odkazy na jiné buňky a pokud si zafixujete odkazy na data ve sl.A a B, pak můžete jednoduše vytvořit v pomocné tabulce přehled za všechny měsíce jednotlivých let rozkopírováním vzorce (v jednom pomocném sl.budou roky a ve druhém měsíce).
POZOR!!! Tato konstrukce funguje pouze pro čísla > 0, nelze ho použít pro záporná čísla.

Editoval hanus (14. 1. 2011 20:12:05)

lp.

Člen

Registrace: 24. 9. 2009

Příspěvků: 844

Re: Podmíněné MAX

Více problémů najednou...

Funkce AND a OR fungují s maticovými výrazy podobně jako MAX, MIN, SUM, ... tj. zpracují celé výsledné pole v argumentu. Tyto funkce v maticových vzorcích obvykle nelze vnořovat tj. konstrukce typu {=SUM(IF(AND(... ); ...;...}  nedají očekávaný výsledek.

Pokud potřebujeme vnořit více podmínek, můžeme někdy výraz převést na jednu podmínku nebo použít klasicky místo AND a OR operace násobení a sčítání.

Převedení na jednu podmínku:

{=MAX(IF(YEAR(A1:A10)&MONTH(A1:A10)="20111";B1:B10;""))}

nebo (třeba)

{=MAX(IF(TEXT(A1:A10;"yyyym")="20111";B1:B10;""))}

Tyto vzorce mají jednu chybu: Pokud není podmínka splněna, vrací nulu, nelze tedy podle výsledku rozeznat, jestli je maximum nula nebo jestli chybí data.

Zohlednit to můžeme např. takto:

{=IF(OR(TEXT(A1:A10;"yyyym")="20111");MAX(IF(TEXT(A1:A10;"yyyym")="20111";B1:B10));"Nejsou data")}

(Hned tu máme OR v maticovém výrazu.)

Varianta s pomocnou buňku:

{=IF(OR(TEXT(A1:A10;"yyyym")=TEXT(C1;"yyyym"));MAX(IF(TEXT(A1:A10;"yyyym")=TEXT(C1;"yyyym");B1:B10));"Nejsou data")}

v C1 je datum z měsíce, pro který hledáme maximum.

Pokud přidáme ještě jednu pomocnou buňku:

{=IF(OR(TEXT(A1:A10;D1)=TEXT(C1;D1));MAX(IF(TEXT(A1:A10;D1)=TEXT(C1;D1);B1:B10));"Nejsou data")}

Můžeme pružně měnit období, za které se výraz vyhodnocuje:

D1: "yyyy"    - hodnotíme celý rok

D1: "yyyyqq"    - hodnotíme kvartály

D1: "yyyymm"    - hodnotíme měsíce

D1: "yyyyww"    - hodnotíme týdny

D1: ="[<"&VALUE("1.1.2011")&"]yyyy;yyyymm"      - pro letošní rok hodnotíme po měsících, minulé roky jen jako celek....

Toto samozřejmě platí, pokud jsou ve sloupci A data a ve sloupci B čísla, kde hledáte maxima.

V původním dotazu byla zmínka o použití funkce INDIRECT. Nepochopil jsem její účel. Pokud jsou hodnoty rozděleny do bloků, pak by měl fungovat normální odkaz.

KOntingenční tabulka - dobrý nástroj, neaktualizuje se automaticky, záleží na to, co chceme s výsledky dělat dál.

Editoval lp. (14. 1. 2011 23:42:51)