Vispārēja formula
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Kopsavilkums
Apkopojot augšējās n vērtības diapazona atbilstības kritērijos, varat izmantot formulu, kuras pamatā ir funkcija LARGE, kas iesaiņota funkcijas SUMPRODUCT iekšpusē. Formulas (iepriekš) vispārējā formā diapazons apzīmē šūnu diapazonu, kas tiek salīdzināts ar kritērijiem , vērtības apzīmē skaitliskas vērtības, no kurām iegūst augstākās vērtības, un N ir ideja par N vērtību.
Piemērā aktīvajā šūnā ir šāda formula:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Kur krāsa ir nosauktais diapazons B5: B12 un vērtība ir nosauktais diapazons C5: C12.
Paskaidrojums
Vienkāršākajā formā LARGE ar šādu konstrukciju atgriež diapazona "N lielāko" vērtību:
=LARGE (range,N)
Piemēram, piemēram:
=LARGE (C5:C12,2)
atgriezīs 2. lielāko vērtību diapazonā C5: C12, kas parādītajā piemērā ir 12.
Tomēr, ja jūs kā otro argumentu LARGE piegādājat "masīva konstanti" (piem., Konstanti formā (1,2,3)), LARGE viena rezultāta vietā atgriezīs rezultātu masīvu. Tātad, formula:
=LARGE (C5:C12, (1,2,3))
atgriezīs 1., 2. un 3. lielāko vērtību C5: C12 tādā masīvā: (12,12,10)
Tātad, triks šeit ir filtrēt vērtības, pamatojoties uz krāsu, pirms LARGE darbojas. Mēs to darām ar izteicienu:
(color=E5)
Rezultātā tiek iegūts TRUE / FALSE vērtību masīvs. Reizināšanas darbības laikā šīs vērtības tiek piespiestas vienās un nullēs:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Tātad galarezultāts ir tāds, ka operācijā izdzīvo tikai vērtības, kas saistītas ar sarkano krāsu:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
un pārējās vērtības tiek piespiestas uz nulli.
Piezīme. Šī formula neapstrādās vērtību diapazona tekstu. Skatīt zemāk.
Teksta apstrāde vērtībās
Ja jums ir teksts jebkurā vērtību diapazonā, funkcija LARGE izmetīs #VALUE kļūdu un apturēs formulas darbību.
Lai apstrādātu tekstu vērtību diapazonā, varat pievienot IFERROR funkciju šādi:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Šeit mēs noķeram kļūdas no LARGE, ko izraisa teksta vērtības, un aizstājam ar nulli. Izmantojot IF iekšpusē LARGE, formula jāievada ar vadības taustiņu + shift + enter, tāpēc mēs pārslēdzamies uz SUM, nevis SUMPRODUCT.
Piezīme: Es uzskrēju šai formulai, kuru stackoverflow ievietoja pārsteidzošais Berijs Houdini.
