Prenesite primer delovnega zvezka
Ta vadnica bo pokazala, kako najti največjo vrednost celice, ki izpolnjuje posebne pogoje v Excelu in Google Preglednicah.
Največja funkcija matrike
Funkcija MAX označuje največjo vrednost v nizu številk.
Za identifikacijo največje vrednosti, ki izpolnjuje določen pogoj, lahko uporabimo funkcijo MAX v kombinaciji s funkcijo IF.
Uporabnikom Google Preglednic in Excela 2022 ali novejšega priporočamo uporabo enostavnejše funkcije MAXIFS. To je razloženo v naslednjem razdelku.
Ta primer bo za določitev največjega uporabil funkcije MAX in IF v formuli matrike Velikost naročila za vsakogar Ime trgovine
| 1 | {= MAX (IF (B3: B8 = "A", D3: D8))} |
V Office 365 in različicah Excela po letu 2022 lahko preprosto vnesete zgornjo formulo, kot običajno (s pritiskom na ENTER).
Za Excel 2022 in starejše morate formulo vnesti s pritiskom na CTRL + SHIFT + ENTER. Po tem boste opazili, da se okrog formule pojavijo oklepaji oklepaja.
Za prikaz delovanja te formule jo razčlenimo na korake.
To je naša zadnja formula (prikazana brez samodejno dodanih oklepajev formule matrike):
| 1 | = MAX (ČE (B3: B8 = "A", D3: D8)) |
Najprej se vrednosti obsega celic dodajo formuli kot matrike:
| 1 | = MAX (IF ({"A"; "B"; "A"; "B"; "A"; "B"} = "A", {500; 400; 300; 700; 600; 200})) |
Naslednji Ime trgovine = ”A” pogoj ustvari niz vrednosti TRUE/FALSE:
| 1 | = MAX (IF ({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {500; 400; 300; 700; 600; 200})) |
Nato funkcija IF spremeni vse vrednosti TRUE v ustrezne Velikost naročila:
| 1 | = MAX ({500; FALSE; 300; FALSE; 600; FALSE}) |
Funkcija MAX identificira največje število v matriki, pri tem pa prezre kakršne koli vrednosti FALSE, da prikaže največje Velikost naročila za Ime trgovine = "A":
| 1 | =600 |
Največ, če - več meril
Največjo vrednost lahko identificiramo tudi na podlagi več meril z uporabo logične logike.
Ta primer bo pokazal največjega Velikost naročila za vsakogar Ime trgovine, ampak za Datumi naročil pred 30.4.2021 z uporabo funkcij MAX, IF in DATE:
| 1 | {= MAX (IF ((B3: B8 = "A")*(C3: C8 |
Upoštevajte, da skupaj pomnožimo dva niza meril TRUE/FALSE:
| 1 | (B3: B8 = "A") * (C3: C8 |
Če sta oba kriterija TRUE, bo skupni pogoj izračunan kot TRUE, če pa je eno (ali več) meril FALSE, se bo izračunalo kot FALSE.
S to metodologijo je možno tej formuli dodati veliko različnih meril.
Največ, če - več meril z referencami celic
Običajno ni primerno trdo kodirati vrednosti v formule. Namesto tega je bolj prilagodljivo uporabljati ločene celice za opredelitev meril.
Za ujemanje z Ime trgovine do vrednosti, prikazane v stolpcu F, lahko formulo posodobimo tako:
| 1 | {= MAX (IF ((B3: B8 = F3)*(C3: C8 |
Zaklepanje referenc celic
Za lažje branje formul smo prikazali formule brez zaklenjenih sklicev na celice:
| 1 | {= MAX (IF ((B3: B8 = F3)*(C3: C8 |
Toda te formule ne bodo delovale pravilno, če jih kopirate in prilepite drugam v datoteko. Namesto tega uporabite zaklenjene sklice na celice, kot je ta:
| 1 | {= MAX (IF (($ B $ 3: $ B $ 8 = F3)*($ C $ 3: $ C $ 8 |
Če želite izvedeti več, preberite naš članek o zaklepanju referenc celic.
Funkcija MAXIFS
Uporabniki Google Preglednic in Excela 2022 ali novejšega lahko uporabijo eno samo funkcijo MAXIFS, da ponovijo vedenje funkcij MAX in IF, prikazanih v prejšnjih primerih.
Ta naslednji primer uporablja funkcije MAXIFS in DATE za prikaz največjega Velikost naročila za vsakogar Ime trgovine za Datumi naročil pred 30.4.2021:
| 1 | = MAXIFS (D3: D8, B3: B8, "A", C3: C8, "<" & DATE (2021,4,30)) |
Funkcija MAXIFS ne zahteva, da uporabnik pritisne CTRL + SHIFT + ENTER pri vnosu formule.
Največja vrednost (največja vrednost s pogojem) v Google Preglednicah
Zgornji primeri delujejo popolnoma enako v Google Preglednicah kot v Excelu, a ker je na voljo funkcija MAXIFS, je priporočljivo, da namesto kombinacije funkcij MAX in IF uporabite to eno samo funkcijo.
Če je treba uporabiti primere s funkcijami MAX in IF, potem Google Preglednice zahtevajo, da jih vnesete kot formule matrike. Namesto prikaza formule z oklepaji okrogle matrike Excel {}, pritisk CTRL + SHIFT + ENTER samodejno doda funkcijo ARRAYFORMULA okrog formule:
| 1 | = ARRAYFORMULA (MAX (IF ((B3: B8 = "A"))*(C3: C8 |
