Bendroji formulė
(=SUM(SMALL(IF(range1=criteria,range2),(1,2,3,N))))
Santrauka
Apibendrinant n apatines n reikšmes diapazono atitikimo kriterijuose, galite naudoti masyvo formulę, pagrįstą funkcija MAŽA, įtraukta į SUM funkciją. Bendroje formulės formoje (aukščiau) diapazonas1 reiškia ląstelių diapazoną, palyginti su kriterijais , diapazone2 yra skaitinės vertės, iš kurių gaunamos apatinės vertės, o N reiškia „n- ąją “.
Pavyzdyje aktyvioje langelyje yra ši formulė:
=SUM(SMALL(IF(color=E5,value),(1,2,3)))
Kur spalva yra pavadintas diapazonas B5: B12, o vertė - pavadintas diapazonas C5: C12.
Pastaba: tai yra masyvo formulė, kurią reikia įvesti su „Control“ + „Shift“ + „Enter“.
Paaiškinimas
Paprasčiausiu pavidalu SMALL pateikia šią struktūrą „N-ąją mažiausią“ diapazono vertę:
=SMALL (range,N)
Taigi, pavyzdžiui:
=SMALL (C5:C12,2)
grąžins 2-ąją mažiausią reikšmę diapazone C5: C12, kuri yra 5 pateiktame pavyzdyje.
Tačiau jei „masyvo konstanta“ (pvz., Konstantą formos (1,2,3)) pateikiate SMALL kaip antrą argumentą, SMALL pateiks rezultatų masyvą, o ne vieną rezultatą. Taigi, formulė:
=SMALL (C5:C12, (1,2,3))
tokiu masyvu grąžins 1, 2 ir 3 mažiausias reikšmes C5: C12: (4,5,7).
Taigi, gudrybė yra filtruoti reikšmes pagal spalvą prieš paleidžiant SMALL. Tai darome su išraiška, pagrįsta IF funkcija:
IF(color=E5,value)
Tai sukuria masyvą vertybių, įvedamų į SMALL. Iš esmės į masyvą patenka tik su raudona spalva susietos vertės. Jei spalva lygi „raudonai“, masyve yra skaičius, o jei spalva nėra raudona, masyve yra FALSE:
SMALL((12;FALSE;10;FALSE;8;4;FALSE;FALSE),(1,2,3)))
SMALL funkcija ignoruoja FALSE reikšmes ir pateikia 3 mažiausias masyvo reikšmes: (4,8,10). Funkcija SUM grąžina galutinį rezultatą 22.
