В тази статия ще научим как да намерим n -то най -малко с критерии & n -то най -голямо с критерии от дадената таблица в Excel.
Сценарий:
С прости думи, докато работите с числа в числа, понякога се дава условие, т.е. когато трябва да търсим 5 -тата най -висока дадена стойност. Можете лесно да разрешите този проблем, като използвате функциите на Excel, както е обяснено по -долу.
N -ти по големина с критерии
Как да решим проблема?
За тази статия ще трябва да използваме функцията LARGE. Сега ще направим формула от тези функции. Тук ни е дадена таблица и трябва да намерим n -тата най -голяма стойност в диапазона с дадени критерии.
Обща формула:
{ = LARGE (IF (c_range = "стойност", диапазон), н ) }
c_range : диапазон от критерии
стойност : критериите съвпадат със стойността
диапазон : резултат масив
н : n -ти по големина
Забележка : Не поставяйте къдрави скоби ръчно. Това е формула на масив, трябва да се използва Ctrl + Shift + Enter вместо просто Enter, където връща #NUM! грешка.
Пример:
Всичко това може да е объркващо за разбиране. Така че, нека тестваме тази формула, като я изпълним в примера, показан по -долу.
Тук имаме подробности за поръчката с дата на поръчка, регион и цена на поръчката.
Трябва да разберем петата по големина цена на поръчка от източния регион. Тук диапазонът е даден като използване на инструмента за имена на диапазон excel.
регион (C3: C16)
цена (D3: D16)
Първо, трябва да намерим Петите най -големи стойности с помощта на функцията LARGE и след това да извършим операцията по сумата над тези 5 стойности. Сега ще използваме следната формула, за да получим сумата
Използвайте формулата:
{ = ГОЛЯМ (IF (регион = G5, цена), F5)}
Обяснение:
- Функцията IF проверява критериите, съответстващи на всички стойности в региона с дадена източна стойност в клетка G5 и връща съответните TRUE стойности от ценовия диапазон.
= ГОЛЯМ ({58.41; 303.63; FALSE; 153.34; 82.84; FALSE; 520.01; FALSE; 177; FALSE; FALSE; 57.97; 97.72; FALSE}), F5)
- Функцията LARGE взема масива, който има целия ценови диапазон, както е показано по -горе, и връща 5 -тата най -голяма стойност от масива, както е показано на снимката по -долу.
Можете да видите формулата в полето за формула, както е показано по -горе. Използвайте Ctrl + Shift + Enter, за да получите резултата.
Както можете да видите, формулата на масива връща петата по големина цена $ 97.72 с източния регион.
Можете също така да подавате масив като масив, критерии в кавички и n стойност директно към функцията, вместо да използвате препратка към клетка или именен диапазон.
Използвайте формулата:
{ = ГОЛЯМ (АКО (C3: C16 = "Запад", D3: D16), 3)}
Използвайте Ctrl + Shift + Enter
Можете да видите, че формулата работи добре, където намерете n -тата най -голяма стойност с критерии в таблицата.
N -то най -ниско с критерии
Как да решим проблема?
За тази статия ще трябва да използваме функцията SMALL. Сега ще направим формула от тези функции. Тук ни е дадена таблица и трябва да намерим n -тата най -малка стойност в диапазона с дадени критерии.
Обща формула:
{ = МАЛКО (АКО (c_range = "стойност", диапазон), н ) }
c_range : диапазон от критерии
стойност : критериите съвпадат със стойността
диапазон : резултат масив
н : n -ти по големина
Забележка : Не поставяйте къдрави скоби ръчно. Това е формула на масив, трябва да се използва Ctrl + Shift + Enter вместо просто Enter, където връща #NUM! грешка.
Пример:
Всичко това може да е объркващо за разбиране. Така че, нека тестваме тази формула, като я изпълним в примера, показан по -долу.
Тук имаме подробности за поръчката с дата на поръчка, регион и цена на поръчката.
Трябва да разберем най -малката цена на поръчка от източния регион. Тук диапазонът е даден като използване на инструмента за име на диапазон excel.
регион (C3: C16)
цена (D3: D16)
Първо, трябва да намерим Петата най -малка или най -ниска стойност, използвайки функцията SMALL и след това да потърсим операцията, която да се извърши над стойностите. Сега ще използваме следната формула, за да получим сумата
Използвайте формулата:
{ = МАЛКО (АКО (регион = G5, цена), F5)}
Обяснение:
- Функцията IF проверява критериите, съответстващи на всички стойности в региона с дадена източна стойност в клетка G5 и връща съответните TRUE стойности от ценовия диапазон.
= ГОЛЯМ ({58.41; 303.63; FALSE; 153.34; 82.84; FALSE; 520.01; FALSE; 177; FALSE; FALSE; 57.97; 97.72; FALSE}), F5)
- Функцията SMALL взема масива, който има целия ценови диапазон, както е показано по -горе, и връща 5 -тата най -голяма стойност от масива, както е показано на снимката по -долу.
Можете да видите формулата в полето за формула, както е показано по -горе. Използвайте Ctrl + Shift + Enter за да получите резултата.
Както можете да видите формулата на масива връща петата най -малка цена $ 153.34 с източния регион.
Можете също така да подавате масив като масив, критерии в кавички и n стойност директно към функцията, вместо да използвате препратка към клетка или именен диапазон.
Използвайте формулата:
{ = МАЛКО (АКО (C3: C16 = "запад", D3: D16), 3)}
Използвайте Ctrl + Shift + Enter
Можете да видите, че формулата работи добре, когато намерите n -тата най -голяма стойност с критерии в таблицата.
Ето някои наблюдателни бележки, показани по -долу.
Бележки:
- Формулата работи само с числа.
- Не поставяйте къдрави скоби ръчно. Това е формула на масив, трябва да се използва Ctrl + Shift + Enter вместо просто Enter, където връща #NUM! грешка.
- Стойността на не може да бъде по -голяма от броя на стойността на критерия или връща #NUM! Грешка.
- Ако критериите не отговарят на формулата, връща грешка.
- Масивът на аргументите трябва да е със същата дължина, освен функцията.
Надявам се тази статия за това как да намерите n -то най -голямо с критерии & n -то най -малко с критерии в excel е обяснителна. Разгледайте още статии за търсене на формуляри в Excel тук. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта
Как да използвате функцията SUMPRODUCT в Excel: Връща SUM след умножаване на стойности в множество масиви в Excel.
SUM, ако датата е между : Връща сумата на стойностите между датите или периода в Excel.
Сума, ако датата е по -голяма от датата: Връща сумата на стойностите след дадената дата или период в Excel.
2 начина за сумиране по месеци в Excel: Връща сумата на стойностите в рамките на даден конкретен месец в Excel.
Как да сумирате няколко колони с условие: Връща сумата на стойностите в множество колони, имащи условие в excel
Как да използвате заместващи символи в Excel : Пребройте клетки, съвпадащи с фрази, използвайки заместващи знаци в Excel
Популярни статии
50 Преки пътища в Excel за повишаване на вашата производителност : По -бързо изпълнете задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Как да използвате tФункцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
