Yleinen kaava
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Yhteenveto
Yhteenvetona ylin n arvo alueen vastaavuuskriteereistä, voit käyttää kaavaa, joka perustuu LARGE-funktioon, joka on kääritty SUMPRODUCT-funktion sisään. Kaavan (yllä) yleisessä muodossa alue edustaa joukkoa soluja, joita verrataan ehtoihin , arvot edustavat numeerisia arvoja, joista ylin arvot haetaan, ja N edustaa ajatusta N: stä arvosta.
Esimerkissä aktiivinen solu sisältää tämän kaavan:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Jossa väri on nimetty alue B5: B12 ja arvo on nimetty alue C5: C12.
Selitys
Yksinkertaisimmassa muodossaan LARGE palauttaa alueen "N: nneksi suurimman" arvon tällä rakenteella:
=LARGE (range,N)
Joten esimerkiksi:
=LARGE (C5:C12,2)
palauttaa alueen 2. suurimman arvon C5: C12, joka on 12 näytetyssä esimerkissä.
Jos kuitenkin syötät "matriisivakion" (esim. Vakion muodossa muodossa (1,2,3)) LARGE: ksi toisena argumenttina, LARGE palauttaa joukon tuloksia yhden tuloksen sijaan. Joten, kaava:
=LARGE (C5:C12, (1,2,3))
palauttaa ensimmäisen, toisen ja kolmannen suurimman arvon C5: C12 seuraavassa taulukossa: (12,12,10)
Joten temppu on suodattaa arvot värien perusteella ennen LARGE-suoritusta. Teemme tämän lausekkeella:
(color=E5)
Mikä johtaa TOSI / EPÄTOSI-arvoihin. Kertolaskuoperaation aikana nämä arvot pakotetaan ykkösiksi ja nolliksi:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Joten lopputulos on, että vain arvot, jotka liittyvät väriin "punainen", selviävät toiminnasta:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
ja muut arvot pakotetaan nollaan.
Huomaa: tämä kaava ei käsittele tekstiä arvon alueella. Katso alempaa.
Tekstin käsittely arvoina
Jos sinulla on tekstiä missä tahansa arvoalueissa, LARGE-funktio heittää #ARVO-virheen ja estää kaavan toimimasta.
Voit käsitellä tekstiä arvoalueella lisäämällä IFERROR-funktion seuraavasti:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Täällä pyydystämme LARGE-virheitä, jotka johtuvat tekstiarvoista, ja korvaamme nollalla. IF: n käyttäminen LARGE: n sisällä edellyttää, että kaava syötetään näppäimillä + Shift + Enter, joten vaihdamme SUMPRODUCT: n sijasta SUM: iin.
Huomaa: törmäsin tähän kaavaan, jonka hämmästyttävä Barry Houdini on lähettänyt pinoverflow'lle.
