Preuzmite primjer radne knjige
Ovaj će vodič pokazati kako pronaći najveću vrijednost ćelije koja zadovoljava određene uvjete u Excelu i Google tablicama.
Max If Array Funkcija
MAX funkcija identificira najveću vrijednost u nizu brojeva.
Možemo koristiti funkciju MAX u kombinaciji s funkcijom IF za identifikaciju najveće vrijednosti koja zadovoljava određeni uvjet.
Korisnicima Google tablica i programa Excel 2022 ili novijih preporučuje se upotreba jednostavnije funkcije MAXIFS. To je objašnjeno u kasnijem odjeljku.
Ovaj primjer će koristiti MAX i IF funkcije u formuli niza za identifikaciju najvećih Veličina narudžbe za svakoga Naziv trgovine
| 1 | {= MAX (IF (B3: B8 = "A", D3: D8))} |
U sustavu Office 365 i verzijama programa Excel nakon 2022. godine možete jednostavno unijeti gornju formulu na uobičajen način (pritiskom na ENTER).
Međutim, za Excel 2022 i starije morate formulu unijeti pritiskom na CTRL + SHIFT + ENTER. Nakon toga ćete primijetiti da se oko formule pojavljuju zagrada zagrada.
Da bismo pokazali kako ova formula radi, podijelimo je na korake.
Ovo je naša konačna formula (prikazana bez automatski dodanih zagrada formule niza):
| 1 | = MAX (IF (B3: B8 = "A", D3: D8)) |
Prvo se vrijednosti raspona ćelija dodaju formuli kao nizovi:
| 1 | = MAX (IF ({"A"; "B"; "A"; "B"; "A"; "B"} = "A", {500; 400; 300; 700; 600; 200})) |
Dalje Naziv trgovine = ”A” uvjet proizvodi niz TRUE/FALSE vrijednosti:
| 1 | = MAX (IF ({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {500; 400; 300; 700; 600; 200})) |
Tada IF funkcija mijenja sve TRUE vrijednosti u relevantne Veličina narudžbe:
| 1 | = MAX ({500; FALSE; 300; FALSE; 600; FALSE}) |
MAX funkcija identificira najveći broj u nizu, zanemarujući bilo koje FALSE vrijednosti, kako bi pokazala najveću Veličina narudžbe za Naziv trgovine = "A":
| 1 | =600 |
Maksimalno ako - više kriterija
Također možemo identificirati najveću vrijednost na temelju više kriterija pomoću Booleove logike.
Ovaj primjer će pokazati najveće Veličina narudžbe za svakoga Naziv trgovine, ali za Datumi narudžbe prije 30.4.2021 pomoću funkcija MAX, IF i DATE:
| 1 | {= MAX (IF ((B3: B8 = "A")*(C3: C8 |
Primijetite da ovdje množimo dva skupa TRUE/FALSE kriterija zajedno:
| 1 | (B3: B8 = "A") * (C3: C8 |
Ako su oba kriterija ISTINA, tada će se ukupni uvjet izračunati kao ISTINA, ali ako je jedan (ili više) kriterija FALSE, izračunat će se kao FALSE.
Pomoću ove metodologije moguće je ovoj formuli dodati mnogo različitih kriterija.
Maksimalno ako - više kriterija s referencama na ćelije
Obično nije dobra praksa tvrdo kodirati vrijednosti u formule. Umjesto toga, fleksibilnije je koristiti zasebne ćelije za definiranje kriterija.
Da bi odgovarao Naziv trgovine do vrijednosti prikazane u stupcu F, možemo ažurirati formulu tako da bude:
| 1 | {= MAX (IF ((B3: B8 = F3)*(C3: C8 |
Zaključavanje referenci ćelija
Radi lakšeg čitanja formula prikazali smo formule bez zaključanih referenci ćelija:
| 1 | {= MAX (IF ((B3: B8 = F3)*(C3: C8 |
Ali ove formule neće raditi ispravno ako ih kopirate i zalijepite na drugo mjesto u datoteci. Umjesto toga, trebali biste koristiti zaključane ćelijske reference poput ove:
| 1 | {= MAX (IF (($ B $ 3: $ B $ 8 = F3)*($ C $ 3: $ C $ 8 |
Pročitajte naš članak o zaključavanju referenci stanica da biste saznali više.
MAXIFS funkcija
Korisnici Google tablica i Excela 2022 ili noviji mogu koristiti jednu funkciju MAXIFS za repliciranje ponašanja funkcija MAX i IF prikazanih u ranijim primjerima.
Ovaj sljedeći primjer koristi funkcije MAXIFS i DATE za prikaz najvećih Veličina narudžbe za svakoga Naziv trgovine za Datumi narudžbe prije 30.4.2021 .:
| 1 | = MAXIFS (D3: D8, B3: B8, "A", C3: C8, "<" & DATE (2021,4,30)) |
Funkcija MAXIFS ne zahtijeva od korisnika da pritisne CTRL + SHIFT + ENTER prilikom unosa formule.
Maksimalno ako (najveća vrijednost s uvjetom) u Google tablicama
Gore prikazani primjeri rade potpuno isto u Google tablicama kao i u Excelu, ali kako je dostupna funkcija MAXIFS, preporučuje se korištenje ove jedne funkcije umjesto kombiniranja funkcija MAX i IF.
Ako je potrebno koristiti primjere koji koriste funkcije MAX i IF, Google tablice zahtijevaju da ih unesete kao formule niza. Umjesto prikazivanja formule s Excelovim zagradama u nizu {}, pritiskom na CTRL + SHIFT + ENTER automatski se dodaje funkcija ARRAYFORMULA oko formule:
| 1 | = ARRAYFORMULA (MAX (IF ((B3: B8 = "A")*(C3: C8 |
