Bendroji formulė
=SUMPRODUCT(LARGE(rng,(1,2,N)))
Santrauka
Apibendrinant didžiausias diapazono vertes, galite naudoti formulę, pagrįstą funkcija LARGE, įterptą į SUMPRODUCT funkciją. Bendroje formulės formoje (aukščiau) rng reiškia langelių diapazoną, kuriame yra skaitinės vertės, o N reiškia N- osios vertės idėją.
Pavyzdyje aktyvioje langelyje yra ši formulė:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Paaiškinimas
Paprasčiausia forma LARGE grąžins „N didžiausią“ diapazono vertę. Pvz., Formulė:
=LARGE(B4:B13, 2)
grąžins 2-ą pagal dydį B4: B13 diapazono vertę, kuri, aukščiau pateiktame pavyzdyje, yra skaičius 9.
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(B4:B13,(1,2,3))
grąžins 1, 2 ir 3 pagal dydį reikšmę diapazone B4: B13. Ankstesniame pavyzdyje, kur B4: B13 yra skaičiai 1-10, rezultatas iš LARGE bus masyvas (8,9,10). Tada SUMPRODUCT susumuoja šio masyvo skaičius ir pateikia bendrą sumą, kuri yra 27.
SUM vietoj SUMPRODUCT
SUMPRODUCT yra lanksti funkcija, leidžianti naudoti k langelių nuorodas funkcijoje LARGE.
Tačiau jei naudojate paprastą koduotą masyvo konstantą, pvz., (1,2,3), galite tiesiog naudoti funkciją SUM:
=SUM(LARGE(B4:B13,(1,2,3)))
Atminkite, kad šią formulę turite įvesti kaip masyvo formulę, jei naudojate langelių nuorodas, o ne masyvo konstantą k viduje LARGE.
Kai N taps didelis
Kai N tampa didelis, pasidaro nuobodu sukurti masyvo konstantą rankomis. Jei norite susumuoti į 20 ar 30 didžiausių reikšmių dideliame sąraše, masyvo konstantos su 20 ar 30 elementų įvedimas užtruks daug laiko. Tokiu atveju galite naudoti nuorodą masyvo konstantai, naudojančiai EILĖ ir NETIESIOGINIS funkcijas, sukurti.
Pvz., Jei norite susumuoti 20 geriausių reikšmių diapazone, vadinamame „rng“, galite parašyti tokią formulę:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
Kintamasis N
Jei nepakanka duomenų, fiksuotas N gali sukelti klaidų. Tokiu atveju galite išbandyti tokią formulę:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Čia mes naudojame MIN su COUNT, kad susumuotume 3 didžiausias reikšmes arba reikšmių skaičių, jei mažiau nei 3.
