Atsisiųskite darbaknygės pavyzdį
Ši pamoka parodys, kaip rasti didžiausią langelio vertę, atitinkančią konkrečias sąlygas „Excel“ ir „Google“ skaičiuoklėse.
„Max If Array“ funkcija
Funkcija MAX nustato didžiausią skaičių eilutės reikšmę.
Norėdami nustatyti didžiausią vertę, atitinkančią nurodytą sąlygą, galime naudoti funkciją MAX kartu su IF funkcija.
„Google“ skaičiuoklių ir „Excel 2022“ ar naujesnių versijų vartotojams rekomenduojama naudoti paprastesnę funkciją „MAXIFS“. Tai paaiškinta vėlesniame skyriuje.
Šiame pavyzdyje bus naudojamos MAX ir IF funkcijos masyvo formulėje, kad būtų galima nustatyti didžiausią Užsakymo dydis kiekvienam Parduotuvės pavadinimas
| 1 | {= MAX (IF (B3: B8 = "A", D3: D8))} |
„Office 365“ ir „Excel“ versijose po 2022 m. Galite tiesiog įvesti aukščiau pateiktą formulę, kaip įprastai (paspausdami ENTER).
Tačiau naudojant „Excel 2022“ ir ankstesnę formulę turite įvesti paspausdami CTRL + SHIFT + ENTER. Tai atlikę, pastebėsite, kad aplink formulę atsiranda garbanotų masyvų skliausteliai.
Norėdami parodyti, kaip ši formulė veikia, suskaidykime ją į veiksmus.
Tai yra mūsų galutinė formulė (rodoma be automatiškai pridėtų masyvo formulės skliaustų):
| 1 | = MAX (IF (B3: B8 = "A", D3: D8)) |
Pirma, ląstelių diapazono vertės įtraukiamos į formulę kaip masyvai:
| 1 | = MAX (IF ({(A ";" B ";" A ";" B ";" A ";" B "} =" A ", {500; 400; 300; 700; 600; 200})) |
Toliau Parduotuvės pavadinimas = „A“ sąlyga sukuria TRUE/FALSE reikšmių masyvą:
| 1 | = MAX (IF ({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {500; 400; 300; 700; 600; 200})) |
Tada IF funkcija pakeičia visas TRUE reikšmes į atitinkamas Užsakymo dydis:
| 1 | = MAX ({500; FALSE; 300; FALSE; 600; FALSE}) |
Funkcija MAX nustato didžiausią masyvo skaičių, nepaisydama jokių FALSE reikšmių, kad būtų rodomas didžiausias Užsakymo dydis už Parduotuvės pavadinimas = „A“:
| 1 | =600 |
Maksimalus jei - keli kriterijai
Mes taip pat galime nustatyti didžiausią vertę, pagrįstą keliais kriterijais, naudodami Boolean logiką.
Šis pavyzdys parodys didžiausią Užsakymo dydis kiekvienam Parduotuvės pavadinimas, bet už Užsakymo datos iki 2021-04-30 naudojant funkcijas MAX, IF ir DATE:
| 1 | {= MAX (IF ((B3: B8 = "A")*(C3: C8 |
Atkreipkite dėmesį, kad mes dauginame du TIKRŲ/NETIKRŲ kriterijų rinkinius:
| 1 | (B3: B8 = "A") * (C3: C8 |
Jei abu kriterijai yra TIKRI, visa sąlyga bus apskaičiuota kaip TIESA, tačiau jei vienas (ar daugiau) kriterijų yra NETIESA, ji bus apskaičiuota kaip KLAIDA.
Naudojant šią metodiką, prie šios formulės galima pridėti daug skirtingų kriterijų.
„Max If“ - keli kriterijai su ląstelių nuorodomis
Paprastai nėra gera praktika reikšmes koduoti į formules. Vietoj to, lanksčiau naudoti atskirus langelius kriterijams apibrėžti.
Kad atitiktų Parduotuvės pavadinimas iki F stulpelyje nurodytos vertės, galime atnaujinti formulę taip:
| 1 | {= MAX (IF ((B3: B8 = F3)*(C3: C8 |
Ląstelių nuorodų užrakinimas
Kad mūsų formules būtų lengviau skaityti, parodėme formules be užrakintų langelių nuorodų:
| 1 | {= MAX (IF ((B3: B8 = F3)*(C3: C8 |
Tačiau šios formulės neveiks tinkamai, kai bus nukopijuotos ir įklijuotos kitur jūsų faile. Vietoj to turėtumėte naudoti užrakintas langelių nuorodas:
| 1 | {= MAX (IF (($ B $ 3: $ B $ 8 = F3)*($ C $ 3: $ C $ 8) |
Norėdami sužinoti daugiau, perskaitykite mūsų straipsnį apie ląstelių nuorodų užrakinimą.
Funkcija MAXIFS
„Google“ skaičiuoklių ir „Excel 2022“ ar naujesnių versijų vartotojai gali naudoti vieną funkciją „MAXIFS“, norėdami pakartoti ankstesnių pavyzdžių funkcijų „MAX“ ir „IF“ veikimą.
Šiame kitame pavyzdyje didžiausios rodomos funkcijos MAXIFS ir DATE Užsakymo dydis kiekvienam Parduotuvės pavadinimas dėl Užsakymo datos iki 2021-04-30:
| 1 | = MAXIFS (D3: D8, B3: B8, "A", C3: C8, "<" ir DATA (2021,4,30)) |
Funkcija MAXIFS nereikalauja, kad vartotojas įvesdamas formulę turėtų paspausti CTRL + SHIFT + ENTER.
„Max If“ (maksimali vertė su sąlyga) „Google“ skaičiuoklėse
Aukščiau pateikti pavyzdžiai „Google“ skaičiuoklėse veikia lygiai taip pat, kaip ir „Excel“, tačiau, kadangi yra funkcija MAXIFS, rekomenduojama naudoti šią vieną funkciją, o ne derinti funkcijas MAX ir IF.
Jei reikia naudoti pavyzdžius, naudojančius MAX ir IF funkcijas, „Google“ skaičiuoklės reikalauja, kad juos įvestumėte kaip masyvo formules. Užuot rodę formulę su „Excel“ garbanotųjų masyvų skliaustais {}, paspausdami CTRL + SHIFT + ENTER automatiškai prideda ARRAYFORMULA funkciją aplink formulę:
| 1 | = ARRAYFORMULA (MAX (IF (((B3: B8 = "A"))*(C3: C8 |
