Šajā rakstā mēs iemācīsimies atrast n -to mazāko ar kritērijiem un n -to lielāko ar kritērijiem no dotās tabulas programmā Excel.
Scenārijs:
Vienkāršiem vārdiem sakot, strādājot ar skaitļiem datu skaitļos, dažreiz tiek piešķirts nosacījums, ti, kad mums jāmeklē piektā augstākā dotā vērtība. Jūs varat viegli atrisināt šo problēmu, izmantojot Excel funkcijas, kā paskaidrots tālāk.
N lielākais ar kritērijiem
Kā atrisināt problēmu?
Šajā rakstā mums būs jāizmanto funkcija LARGE. Tagad mēs izveidosim formulu no šīm funkcijām. Šeit mums ir dota tabula, un mums ir jāatrod n -tā lielākā vērtība diapazonā ar dotajiem kritērijiem.
Vispārējā formula:
{ = LIELS (IF (c_range = "value", range), n ) }
c_diapazons : kritēriju diapazons
vērtību : kritēriji atbilst vērtībai
diapazons : rezultātu masīvs
n : n lielākais
Piezīme : Nelieciet cirtainus kronšteinus manuāli. Šī ir masīva formula, kas jāizmanto Ctrl + Shift + Enter Ievadīšanas vietā ievadiet #NUM! kļūda.
Piemērs:
To visu var būt mulsinoši saprast. Tātad, pārbaudīsim šo formulu, palaižot to zemāk redzamajā piemērā.
Šeit mums ir informācija par pasūtījumu ar pasūtījuma datumu, reģionu un pasūtījuma cenu.
Mums jānoskaidro piektā lielākā pasūtījuma cena no Austrumu reģiona. Šeit diapazons ir norādīts kā izmantojot nosaukto diapazona Excel rīku.
reģions (C3: C16)
cena (D3: D16)
Pirmkārt, mums ir jāatrod piektās lielākās vērtības, izmantojot funkciju LARGE, un pēc tam šīm 5 vērtībām jāveic summa. Tagad, lai iegūtu summu, mēs izmantosim šādu formulu
Izmantojiet formulu:
{ = LIELS (IF (reģions = G5, cena), F5)}
Paskaidrojums:
- Funkcija IF pārbauda kritērijus, kas atbilst visām reģiona vērtībām ar norādīto austrumu vērtību G5 šūnā, un atgriež atbilstošās patiesās vērtības no cenu diapazona.
= LIELS ({58.41; 303.63; FALSE; 153.34; 82.84; FALSE; 520.01; FALSE; 177; FALSE; FALSE; 57.97; 97.72; FALSE}), F5)
- Funkcija LARGE paņem masīvu, kuram ir viss cenu diapazons, kā parādīts iepriekš, un atgriež 5. lielāko vērtību no masīva, kā parādīts zemāk esošajā momentuzņēmumā.
Jūs varat apskatīt formulu formulas lodziņā, kā parādīts iepriekšējā momentuzņēmumā. Lai iegūtu rezultātu, izmantojiet Ctrl + Shift + Enter.
Kā redzat, masīva formula atgriež piekto lielāko cenu 97,72 ASV dolāru apmērā ar Austrumu reģionu.
Jūs varat arī barot masīvu kā masīvu, kritērijus pēdiņās un n vērtību tieši funkcijai, nevis izmantot šūnu atsauci vai nosaukto diapazonu.
Izmantojiet formulu:
{ = LIELS (JA (C3: C16 = "Rietumi", D3: D16), 3)}
Izmantot Ctrl + Shift + Enter
Jūs varat redzēt, ka formula darbojas labi, ja tabulā atrodat n -to lielāko vērtību ar kritērijiem.
N. zemākais ar kritērijiem
Kā atrisināt problēmu?
Šajā rakstā mums būs jāizmanto funkcija SMALL. Tagad mēs izveidosim formulu no šīm funkcijām. Šeit mums ir dota tabula, un mums ir jāatrod n -tā mazākā vērtība diapazonā ar dotajiem kritērijiem.
Vispārējā formula:
{ = SMALL (IF (c_range = "value", range), n ) }
c_diapazons : kritēriju diapazons
vērtību : kritēriji atbilst vērtībai
diapazons : rezultātu masīvs
n : n lielākais
Piezīme : Nelieciet cirtainus kronšteinus manuāli. Šī ir masīva formula, kas jāizmanto Ctrl + Shift + Enter Ievadīšanas vietā ievadiet #NUM! kļūda.
Piemērs:
To visu var būt mulsinoši saprast. Tātad, pārbaudīsim šo formulu, palaižot to zemāk redzamajā piemērā.
Šeit mums ir informācija par pasūtījumu ar pasūtījuma datumu, reģionu un pasūtījuma cenu.
Mums jānoskaidro n -tā mazākā pasūtījuma cena no Austrumu reģiona. Šeit diapazons ir norādīts kā izmantojot nosaukto diapazona Excel rīku.
reģions (C3: C16)
cena (D3: D16)
Pirmkārt, mums ir jāatrod piektā mazākā vai zemākā vērtība, izmantojot funkciju SMALL, un pēc tam jāmeklē darbība, kas jāveic virs vērtībām. Tagad, lai iegūtu summu, mēs izmantosim šādu formulu
Izmantojiet formulu:
{ = MAZS (JA (reģions = G5, cena), F5)}
Paskaidrojums:
- Funkcija IF pārbauda kritērijus, kas atbilst visām reģiona vērtībām ar norādīto austrumu vērtību G5 šūnā, un atgriež atbilstošās patiesās vērtības no cenu diapazona.
= LIELS ({58.41; 303.63; FALSE; 153.34; 82.84; FALSE; 520.01; FALSE; 177; FALSE; FALSE; 57.97; 97.72; FALSE}), F5)
- Funkcija SMALL ņem masīvu, kuram ir viss cenu diapazons, kā parādīts iepriekš, un atgriež 5. lielāko vērtību no masīva, kā parādīts zemāk esošajā momentuzņēmumā.
Jūs varat apskatīt formulu formulas lodziņā, kā parādīts iepriekšējā momentuzņēmumā. Izmantot Ctrl + Shift + Enter lai iegūtu rezultātu.
Kā redzat, masīva formula atgriež piekto mazāko cenu 153,34 ASV dolāru apmērā ar Austrumu reģionu.
Jūs varat arī barot masīvu kā masīvu, kritērijus pēdiņās un n vērtību tieši funkcijai, nevis izmantot šūnu atsauci vai nosaukto diapazonu.
Izmantojiet formulu:
{ = MAZS (JA (C3: C16 = "rietumi", D3: D16), 3)}
Izmantot Ctrl + Shift + Enter
Jūs varat redzēt, ka formula darbojas labi, ja tabulā atrodat n -to lielāko vērtību ar kritērijiem.
Tālāk ir norādītas dažas novērošanas piezīmes.
Piezīmes:
- Formula darbojas tikai ar skaitļiem.
- Nelieciet cirtainus kronšteinus manuāli. Šī ir masīva formula, kas jāizmanto Ctrl + Shift + Enter Ievadīšanas vietā ievadiet #NUM! kļūda.
- Vērtība nevar būt lielāka par kritēriju skaita vērtību, vai arī tā atgriež #NUM! Kļūda.
- IF kritēriji neatbilst formulai atgriež kļūdu.
- Argumentu masīvam jābūt vienāda garuma, ja nav funkcijas.
Ceru, ka šis raksts par to, kā programmā Excel atrast n -to lielāko ar kritērijiem un n -to mazāko ar kritērijiem, ir paskaidrojošs. Izpētiet citus rakstus par uzmeklēšanas formulām programmā Excel šeit. Ja jums patika mūsu emuāri, kopīgojiet to ar saviem draugiem Facebook. Un arī jūs varat sekot mums Twitter un Facebook. Mēs labprāt uzklausītu jūsu viedokli, dariet mums zināmu, kā mēs varam uzlabot, papildināt vai ieviest jauninājumus mūsu darbā un uzlabot to jūsu labā. Rakstiet mums e -pasta vietnē
Funkcijas SUMPRODUCT izmantošana programmā Excel: Atgriež SUM pēc vērtību reizināšanas vairākos Excel masīvos.
SUM, ja datums ir starp : Atgriež vērtību SUM starp Excel datumiem vai periodu.
Summa, ja datums ir lielāks par norādīto: Atgriež vērtību SUM pēc Excel noteiktā datuma vai perioda.
2 veidi, kā apkopot summu pa mēnešiem programmā Excel: Atgriež Excel summu noteiktā noteiktā mēnesī.
Kā apkopot vairākas kolonnas ar nosacījumu: Atgriež vērtību SUM vairākās kolonnās ar nosacījumu programmā Excel
Kā programmā Excel izmantot aizstājējzīmes : Saskaitiet šūnas, kas atbilst frāzēm, izmantojot Excel aizstājējzīmes
Populāri raksti
50 Excel saīsne produktivitātes paaugstināšanai : Ātrāk izpildiet savu uzdevumu. Šie 50 īsceļi ļaus jums strādāt vēl ātrāk programmā Excel.
Kā lietot tVLOOKUP funkcija programmā Excel : Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
Kā programmā Excel izmantot funkciju COUNTIF : Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai saskaitītu noteiktas vērtības. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.
Kā lietot SUMIF funkciju programmā Excel : Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.