Descărcați exemplul de registru de lucru
Acest tutorial va demonstra cum să găsiți cea mai mare valoare a celulei care îndeplinește condiții specifice în Excel și Foi de calcul Google.
Funcția Max If Array
Funcția MAX identifică cea mai mare valoare dintr-o serie de numere.
Putem utiliza funcția MAX combinată cu o funcție IF pentru a identifica cea mai mare valoare care îndeplinește o condiție specificată.
Utilizatorilor Foi de calcul Google și Excel 2022 sau versiuni ulterioare li se recomandă să utilizeze funcția MAXIFS mai simplă. Acest lucru este explicat într-o secțiune ulterioară.
Acest exemplu va folosi funcțiile MAX și IF într-o formulă matrice pentru a identifica cele mai mari Dimensiunea comenzii pentru fiecare Numele magazinului
| 1 | {= MAX (IF (B3: B8 = "A", D3: D8))}} |
În Office 365 și versiunile de Excel după 2022, puteți introduce pur și simplu formula de mai sus așa cum ați face în mod normal (apăsând ENTER).
Cu toate acestea, pentru Excel 2022 și versiuni anterioare trebuie să introduceți formula apăsând CTRL + SHIFT + ENTER. După ce faceți acest lucru, veți observa că parantezele matrice cret apar în jurul formulei.
Pentru a arăta cum funcționează această formulă, să o împărțim în pași.
Aceasta este formula noastră finală (afișată fără parantezele formulei matricei adăugate automat):
| 1 | = MAX (IF (B3: B8 = "A", D3: D8)) |
În primul rând, valorile intervalului de celule sunt adăugate la formulă ca tablouri:
| 1 | = MAX (IF ({"A"; "B"; "A"; "B"; "A"; "B"} = "A", {500; 400; 300; 700; 600; 200})) |
Apoi Numele magazinului = „A” condiție produce o serie de valori TRUE / FALSE:
| 1 | = MAX (IF ({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {500; 400; 300; 700; 600; 200})) |
Apoi, funcția IF schimbă toate valorile TRUE în relevante Dimensiunea comenzii:
| 1 | = MAX ({500; FALSE; 300; FALSE; 600; FALSE}) |
Funcția MAX identifică cel mai mare număr din matrice, ignorând orice valori FALSE, pentru a afișa cel mai mare Dimensiunea comenzii pentru Numele magazinului = „A”:
| 1 | =600 |
Max If - Criterii multiple
De asemenea, putem identifica cea mai mare valoare pe baza mai multor criterii utilizând logica booleană.
Acest exemplu va arăta cel mai mare Dimensiunea comenzii pentru fiecare Numele magazinului, dar pentru Date de comandă înainte de 30.04.2021 utilizând funcțiile MAX, IF și DATE:
| 1 | {= MAX (IF ((B3: B8 = "A")) * (C3: C8 |
Observați aici că înmulțim împreună două seturi de criterii ADEVĂRATE / FALSE:
| 1 | (B3: B8 = "A") * (C3: C8 |
Dacă ambele criterii sunt ADEVĂRATE, atunci condiția totală se va calcula ca ADEVĂRAT, dar dacă unul (sau mai multe) criterii este FALS, se va calcula ca FALS.
Folosind această metodologie, este posibil să adăugați multe criterii diferite la această formulă.
Max If - Criterii multiple cu referințe celulare
De obicei, nu este o bună practică să codificați valorile în formule. În schimb, este mai flexibil să folosiți celule separate pentru a defini criteriile.
Pentru a se potrivi cu Numele magazinului la valoarea afișată în coloana F, putem actualiza formula astfel:
| 1 | {= MAX (IF ((B3: B8 = F3) * (C3: C8 |
Blocarea referințelor celulei
Pentru a simplifica citirea formulelor, am arătat formulele fără referințe de celule blocate:
| 1 | {= MAX (IF ((B3: B8 = F3) * (C3: C8 |
Dar aceste formule nu vor funcționa corect atunci când sunt copiate și lipite în altă parte a fișierului. În schimb, ar trebui să utilizați referințe de celule blocate ca aceasta:
| 1 | {= MAX (IF (($ B $ 3: $ B $ 8 = F3) * ($ C $ 3: $ C $ 8 |
Citiți articolul nostru despre blocarea referințelor celulei pentru a afla mai multe.
Funcția MAXIFS
Utilizatorii de Foi de calcul Google și Excel 2022 sau versiuni ulterioare pot utiliza singura funcție MAXIFS pentru a reproduce comportamentul funcțiilor MAX și IF prezentate în exemplele anterioare.
Următorul exemplu folosește funcțiile MAXIFS și DATE pentru a afișa cele mai mari Dimensiunea comenzii pentru fiecare Numele magazinului pentru Date de comandă înainte de 30.04.2021:
| 1 | = MAXIFS (D3: D8, B3: B8, "A", C3: C8, "<" & DATE (2021,4,30)) |
Funcția MAXIFS nu necesită ca utilizatorul să apese CTRL + SHIFT + ENTER când introduceți formula.
Max If (Valoare maximă cu condiție) în Foi de calcul Google
Exemplele prezentate mai sus funcționează exact la fel în Foi de calcul Google ca în Excel, dar, deoarece funcția MAXIFS este disponibilă, se recomandă utilizarea acestei funcții unice în loc să combinați funcțiile MAX și IF.
Dacă sunt necesare exemple care folosesc funcțiile MAX și IF, atunci Foi de calcul Google necesită introducerea acestora ca formule matrice. În loc să se afișeze formula cu paranteze Excel matrice {}, apăsând CTRL + SHIFT + ENTER se adaugă automat funcția ARRAYFORMULA în jurul formulei:
| 1 | = ARRAYFORMULA (MAX (IF ((B3: B8 = "A")) * (C3: C8 |
