Üldine valem
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Kokkuvõte
Vahemikus sobitamise kriteeriumide n kõige suurema väärtuse summeerimiseks võite kasutada funktsiooni SUURPROGRAMM sisse keeratud funktsiooni LARGE põhjal valemit. Üldises vormis valemiga (ülal), raadius kujutab Lahtrivahemiku mis on võrreldes kriteeriumid , väärtusi esindab arvväärtused, millest ülemine väärtuste laaditakse ja N tähistab ideed Nth väärtus.
Näites sisaldab aktiivne lahter järgmist valemit:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Kus värv on nimega vahemik B5: B12 ja väärtus nimega vahemik C5: C12.
Selgitus
Lihtsamas vormis tagastab LARGE selle konstruktsiooniga vahemikus "N-nda" väärtuse:
=LARGE (range,N)
Nii näiteks:
=LARGE (C5:C12,2)
tagastab vahemikus C5: C12 suuruselt 2. väärtuse, mis on näidatud näites 12.
Kui aga sisestate teise argumendina LARGE "massiivikonstandi" (nt konstand kujul (1,2,3)), tagastab LARGE tulemuste massiivi ühe tulemuse asemel. Niisiis, valem:
=LARGE (C5:C12, (1,2,3))
tagastab massiivi 1., 2. ja 3. väärtuse C5: C12 sellises massiivis: (12,12,10)
Niisiis, siin on trikk väärtuste filtreerimine enne LARGE käivitamist värvide põhjal. Teeme seda väljendiga:
(color=E5)
Selle tulemusel saadakse massiivi TRUE / FALSE väärtusi. Korrutamistoimingu ajal sunnitakse need väärtused ühedeks ja nullideks:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Nii et lõpptulemus on see, et operatsioonist jäävad ellu ainult punase värviga seotud väärtused:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
ja muud väärtused sunnitakse nulli.
Märkus. See valem ei käsitle väärtuste vahemikus olevat teksti. Vt allpool.
Teksti käsitlemine väärtustes
Kui teil on väärtuste vahemikus kuskil tekst, viskab LARGE funktsioon tõrke #VALUE ja peatab valemi töötamise.
Väärtuste vahemikus oleva teksti käsitsemiseks võite lisada funktsiooni IFERROR järgmiselt:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Siinkohal püüame LARGE-st tekstiväärtustest põhjustatud vead kinni ja asendame nulliga. IF-i kasutamine LARGE-s nõuab, et valem sisestataks juhtklahviga + shift + enter, nii et lülitume SUMPRODUCT-i asemel SUM-ile.
Märkus. Ma sattusin selle valemi juurde, mille postitas hämmastav Barry Houdini stackoverflow'is.
