Sumuokite aukščiausias n reikšmes

Norėdami apibendrinti aukščiausias diapazono vertes, galite naudoti formulę, pagrįstą LARGE funkcija, esančia funkcijos SUMPRODUCT viduje. Bendra formulės forma (aukščiau), rng reiškia langelių diapazoną, kuriame yra skaitinės vertės ir N reiškia N -osios vertės idėją.

Pavyzdyje aktyvioje ląstelėje yra ši formulė:

pašalinti eilučių pertraukas

= SUMPRODUCT ( LARGE (rng,{1,2, N }))

Paprasčiausiu pavidalu LARGE grąžins „N“ pagal dydį diapazono vertę. Pavyzdžiui, formulė:

 
= SUMPRODUCT ( LARGE (B4:B13,{1,2,3}))

grąžins antrą pagal dydį B4: B13 diapazono vertę, kuri, kaip aprašyta aukščiau, yra skaičius 9.

Tačiau jei LARGE kaip antrąjį argumentą pateiksite „masyvo konstantą“ (pvz., Konstantą formos {1,2,3}), LARGE grąžins rezultatų masyvą, o ne vieną rezultatą. Taigi, formulė:

 
= LARGE (B4:B13, 2)

grąžins 1, 2 ir 3 didžiausią diapazono B4: B13 reikšmę. Anksčiau pateiktame pavyzdyje, kur B4: B13 yra skaičiai 1–10, rezultatas LARGE bus masyvas {8,9,10}. Tada „SUMPRODUCT“ susumuoja šio masyvo skaičius ir grąžina bendrą sumą, kuri yra 27.

SUM vietoj SUMPRODUCT

„SUMPRODUCT“ yra lanksti funkcija, leidžianti naudoti funkcijos „LARGE“ k langelių nuorodas.

Tačiau, jei naudojate paprastą užkoduotą kodą masyvo konstanta kaip {1,2,3}, galite tiesiog naudoti SUM funkciją:

 
= LARGE (B4:B13,{1,2,3})

Atkreipkite dėmesį, kad turite įveskite šią formulę kaip masyvo formulę jei naudojate langelių nuorodas, o ne masyvo konstantą k viduje LARGE.

Kai N tampa didelis

Kai N tampa didelis, pasidaro nuobodu rankiniu būdu sukurti masyvo konstantą - jei norite sudėti į 20 ar 30 reikšmių dideliame sąraše, masyvo konstantos su 20 arba 30 elementų įvedimas užtruks ilgai. Tokiu atveju galite naudoti nuorodą, kad sukurtumėte masyvo konstantą, kuri naudoja ROW ir INDIRECT funkcijas.

Pavyzdžiui, jei norite SUMMUOTI 20 geriausių reikšmių diapazone, vadinamame „rng“, galite parašyti tokią formulę:

 
= SUM ( LARGE (B4:B13,{1,2,3}))

Kintamasis N

Jei duomenų nepakanka, fiksuotas N gali sukelti klaidų. Tokiu atveju galite išbandyti tokią formulę:

kaip padaryti linijinę regresiją Excel

 
= SUMPRODUCT ( LARGE (rng, ROW ( INDIRECT ('1:20'))))

Čia mes naudojame MIN su COUNT, kad susumuotume 3 svarbiausias vertes arba reikšmių skaičių, jei jis yra mažesnis nei 3.