Bendroji formulė
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Santrauka
Apibendrinant n didžiausias diapazono atitikimo kriterijų reikšmes, galite naudoti formulę, pagrįstą funkcija DIDELIS, įterptą į funkciją SUMPRODUCT. Bendroje formulės formoje (aukščiau) diapazonas reiškia langelių diapazoną, kuris yra lyginamas su kriterijais , reikšmės reiškia skaitines vertes, iš kurių gaunamos didžiausios vertės, o N reiškia N-osios vertės idėją.
Pavyzdyje aktyvioje langelyje yra ši formulė:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Kur spalva yra pavadintas diapazonas B5: B12, o vertė - pavadintas diapazonas C5: C12.
Paaiškinimas
Paprasčiausia forma LARGE pateikia šią struktūrą „N-tą pagal dydį“ diapazono vertę:
=LARGE (range,N)
Taigi, pavyzdžiui:
=LARGE (C5:C12,2)
grąžins 2-ą pagal dydį reikšmę diapazone C5: C12, kuri parodytame pavyzdyje yra 12.
Tačiau jei „LARGE“ kaip antrąjį argumentą pateiksite „masyvo konstantą“ (pvz., Konstantą formoje (1,2,3)), LARGE pateiks rezultatų masyvą, o ne vieną rezultatą. Taigi, formulė:
=LARGE (C5:C12, (1,2,3))
grąžins 1, 2 ir 3 pagal dydį C5: C12 reikšmę tokiu masyvu: (12,12,10)
Taigi, triukas čia yra filtruoti reikšmes pagal spalvą prieš paleidžiant LARGE. Tai darome su fraze:
(color=E5)
Dėl to gaunama TRUE / FALSE reikšmių masyvas. Vykdant daugybos operaciją, šios vertės yra verčiamos į vienetus ir nulius:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Taigi galutinis rezultatas yra tas, kad operacijos metu išgyvena tik vertės, susijusios su „raudona“ spalva:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
o kitos vertės yra priverstos nuliui.
Pastaba: ši formulė netvarkys vertės diapazono teksto. Žiūrėkite žemiau.
Teksto tvarkymas vertėmis
Jei bet kurioje vertybių diapazone yra teksto, funkcija DIDELĖ išmes #VALUE klaidą ir sustabdys formulės veikimą.
Norėdami tvarkyti tekstą vertės diapazone, galite pridėti IFERROR funkciją taip:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Čia mes sulaikome LARGE klaidas, kurias sukelia teksto reikšmės, ir pakeičiame nuliu. Naudojant IF viduje DIDELIS, formulę reikia įvesti su klavišais + Shift + Enter, todėl mes pereiname prie SUM, o ne į SUMPRODUCT.
Pastaba: Aš patekau į šią formulę, kurią „Stoveroverflow“ paskelbė nuostabus Barry Houdini.
