Lejupielādējiet darbgrāmatas piemēru
Šī apmācība parādīs, kā aprēķināt “liels, ja” vai “mazs, ja”, iegūstot n-to lielāko (vai mazāko) vērtību, pamatojoties uz kritērijiem.
LIELAS un MAZAS funkcijas
Funkcija LARGE tiek izmantota, lai aprēķinātu n -to lielāko vērtību (k) masīvā, bet funkcija SMALL atgriež mazāko n -to vērtību.
Lai izveidotu “lielu If”, mēs masīva formulā izmantosim funkciju LARGE kopā ar funkciju IF.
LIELI JA
Masīva formulā apvienojot LARGE (vai SMALL) un IF, mēs būtībā varam izveidot funkciju “LARGE IF”, kas darbojas līdzīgi kā darbojas iebūvētā SUMIF formula. Apskatīsim piemēru.
Mums ir saraksts ar atzīmēm, kuras skolēni ir ieguvuši divos dažādos mācību priekšmetos:
Pieņemot, ka mums tiek lūgts atrast trīs labākās atzīmes, kas iegūtas katram priekšmetam, piemēram:
Lai to paveiktu, mēs varam ligzdot IF funkciju ar priekšmets kā mūsu kritēriji LARGE iekšienē darbojas šādi:
= LIELS (JA (=,),)
= LIELS (JA ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
Ja izmantojat Excel 2022 un vecāku versiju, formula jāievada, nospiežot CTRL + SHIFT + ENTER lai iegūtu formulas cirtainās iekavas.
Kā darbojas formula?
Formula darbojas, novērtējot katru mūsu kritēriju diapazona šūnu kā PATIESA vai FALSE.
Augstākās pakāpes vērtības (k = 1) atrašana matemātikā:
= LIELS (JA ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
= LIELS (JA ({TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {0.81; 0.8; 0.93; 0.42; 0.87; 0.63; 0.71; 0.58; 0.73}), 1)
Pēc tam IF funkcija aizstāj katru vērtību ar FALSE, ja tās nosacījums nav izpildīts.
= LIELS ({0.81; FALSE; FALSE; 0.42; FALSE; 0.63; FALSE; 0.58; FALSE}, 1)
Funkcija LARGE izlaiž FALSE vērtības un aprēķina lielāko (k = 1) no atlikušajām vērtībām (0,81 ir lielākās vērtības starp 0,42 un 0,81).
MAZI, JA
To pašu paņēmienu var izmantot arī ar funkciju SMALL.
= MAZS (JA ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
LIELI JA ar vairākiem kritērijiem
Lai izmantotu LARGE IF ar vairākiem kritērijiem (līdzīgi kā darbojas iebūvētā SUMIFS formula), vienkārši ievietojiet vairāk IF funkciju LARGE funkcijā šādi:
= LIELS (IF (=, IF (=,)),)
= LIELS (JA ($ D $ 2: $ D $ 18 = $ H3, IF ($ B $ 2: $ B $ 18 = $ G3, $ E $ 2: $ E $ 18)), es $ 2)
Vēl viens veids, kā iekļaut vairākus kritērijus, ir pavairot kritērijus kopā, kā parādīts šajā rakstā
Padomi un triki:
- Ja iespējams, vienmēr norādiet pozīciju (k) no palīgšūnas un bloķēšanas atsauci (F4), jo tas atvieglos automātiskās aizpildīšanas formulas.
- Ja izmantojat Excel 2022 vai jaunāku versiju, varat ievadīt formulu, neizmantojot Ctrl + Shift + Enter.
- Lai iegūtu to studentu vārdus, kuri ieguva augstākās atzīmes, apvienojiet to ar INDEX MATCH
