Bendroji formulė
(=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values))))
Santrauka
Norėdami gauti didžiausią duomenų rinkinio vertę, pagrįstą daugiau nei vienu kriterijumi, galite naudoti masyvo formulę, pagrįstą MAX ir IF funkcijomis. Parodytame pavyzdyje formulė I6 yra:
(=MAX(IF(color=G6,IF(item=H6,price))))
Jei spalva yra „raudona“ ir „skrybėlė“, rezultatas yra 11,00 USD
Pastaba: Tai yra masyvo formulė, kurią reikia įvesti naudodami Ctrl + Shift + Enter
Paaiškinimas
Šiame pavyzdyje naudojami šie pavadinti diapazonai: „spalva“ = B6: B14, „prekė“ = C6: C14 ir „kaina“ = E6: E14. Tikslas yra rasti maksimalią kainą už nurodytą spalvą ir daiktą.
Ši formulė naudoja dvi įdėtas IF funkcijas, apvyniotas MAX viduje, kad būtų grąžinta maksimali kaina pagal du kriterijus. Pradedant pirmojo IF teiginio loginiu testu, spalva = G6, pavadinto diapazono „spalva“ (B6: B14) vertės tikrinamos pagal langelio G6 reikšmę „raudona“. Rezultatas yra toks masyvas:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Atliekant antrojo IF teiginio, elemento = H6, loginį testą, pavadinto diapazono elemento (C6: C14) vertės yra tikrinamos pagal langelio H6 reikšmę „hat“. Rezultatas yra toks masyvas:
(TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE)
Antrojo IF teiginio „vertė, jei teisinga“ yra įvardytas diapazonas „kainos“ (E6: E14), kuris yra toks masyvas:
(11;8;9;12;9;10;9;8;7)
Kiekvienos šio diapazono prekės kaina grąžinama tik tada, kai pirmųjų dviejų aukščiau pateiktų masyvų rezultatas yra TIKRA prekėms, esančioms atitinkamose pozicijose. Parodytame pavyzdyje galutinis „MAX“ masyvas viduje atrodo taip:
(11;8;9;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Atkreipkite dėmesį, kad kainos, kurios „išgyvena“, yra tokios, kad spalva būtų „raudona“, o prekė būtų „kepurė“.
Tada funkcija MAX grąžina didžiausią kainą, automatiškai ignoruodama FALSE reikšmes.
Alternatyvi sintaksė naudojant loginę logiką
Taip pat galite naudoti šią masyvo formulę, kurioje naudojama tik viena IF funkcija ir loginė logika:
(=MAX(IF((color=G6)*(item=H6),price)))
Šios sintaksės privalumas yra tas, kad lengviau pridėti papildomų kriterijų, nepridedant papildomų įdėtų IF funkcijų. Jei jums reikalinga ARBA logika, naudokite pridėjimą, o ne dauginimą tarp sąlygų.
Su MAXIFS
„MAXIFS“ funkcija, pristatyta „Excel 2016“, skirta apskaičiuoti maksimumus pagal vieną ar daugiau kriterijų, nereikalaujant masyvo formulės. Naudojant MAXIFS, I6 formulė yra:
=MAXIFS(price,color,G6,item,H6)
Pastaba: MAXIFS automatiškai nepaisys tuščių langelių, atitinkančių kriterijus. Kitaip tariant, MAXIFS tuščių langelių, atitinkančių kriterijus, nelaikys nuliu. Kita vertus, MAXIFS grąžins nulį (0), jei nė vienas langelis neatitiks kriterijų.
