Найти энный по величине с критериями и энный Наименьший с критериями первенствует
В этой статье мы узнаем, как найти n-е наименьшее по критериям и n-е наибольшее с критериями из данной таблицы в Excel.
Сценарий:
Проще говоря, при работе с числами в числах данных иногда задается условие, то есть когда нам нужно найти 5-е по величине данное значение. Вы можете легко решить эту проблему, используя функции Excel, как описано ниже.
N-й по величине с критериями
Как решить проблему?
Для этой статьи нам потребуется использовать link: / logic-formulas-excel-large-function [LARGE function]
. Теперь составим формулу из этих функций. Здесь нам дана таблица, и нам нужно найти n-е наибольшее значение в диапазоне с заданными критериями.
Общая формула:
{ = LARGE ( IF ( c_range = "value" , range ) , n )}
c_range: значение диапазона критериев: диапазон значений соответствия критериев: массив результатов n: n-й по величине Примечание. Не вставляйте фигурные скобки вручную. Это формула массива, вместо клавиши Enter необходимо использовать Ctrl + Shift + Enter, где она возвращает #NUM! ошибка.
Пример:
Все это может сбивать с толку. Итак, давайте протестируем эту формулу, запустив ее на примере, показанном ниже.
Здесь у нас есть детали заказа с датой заказа, регионом и ценой заказа.
Нам нужно узнать 5-й по величине заказ из Восточного региона. Здесь диапазон указан при использовании инструмента Excel с именованным диапазоном.
область (C3: C16)
цена (D3: D16)
Во-первых, нам нужно найти пятое по величине значение с помощью функции НАИБОЛЬШИЙ, а затем выполнить операцию суммирования для этих 5 значений. Теперь воспользуемся следующей формулой, чтобы получить сумму
Используйте формулу:
{ = LARGE ( IF ( region = G5 , price ) , F5 )}
Пояснение:
-
Функция
link: / tips-if-condition-in-excel [IF]
проверяет критерии, соответствующие всем значениям в регионе с заданным восточным значением в ячейке G5, и возвращает соответствующие ИСТИННЫЕ значения из диапазона цен.
НАИБОЛЬШИЙ (\ {58,41; 303,63; ЛОЖЬ; 153,34; 82,84; ЛОЖЬ; 520,01; ЛОЖЬ; 177; ЛОЖЬ; ЛОЖЬ; 57,97; 97,72; ЛОЖЬ}), F5)
-
link: / logic-formulas-excel-large-function [LARGE]
функция принимает массив, который имеет весь диапазон цен, как показано выше, и возвращает пятое по величине значение из массива, как показано на снимке ниже.
Вы можете просмотреть формулу в поле формулы, как показано на снимке выше. Используйте Ctrl + Shift + Enter, чтобы получить результат.
Как видите, формула массива возвращает 5-ю по величине цену 97,72 доллара США с учетом восточного региона.
Вы также можете передать массив как массив, критерии в кавычках и значение n непосредственно в функцию вместо использования ссылки на ячейку или именованного диапазона.
Используйте формулу:
{ = LARGE ( IF ( C3:C16 = "West" , D3:D16 ) , 3 )}
Используйте Ctrl + Shift + Enter
Вы можете видеть, что формула отлично работает, если найти n-е наибольшее значение, имеющее критерии в таблице.
N-й самый низкий по критериям
Как решить проблему?
Для этой статьи нам потребуется использовать ссылку: / statistics-formulas-excel-small-function [МАЛЕНЬКАЯ функция] `. Теперь составим формулу из этих функций. Здесь нам дана таблица, и нам нужно найти n-е наименьшее значение в диапазоне с заданными критериями.
Общая формула:
{ = SMALL ( IF ( c_range = "value" , range ) , n )}
c_range: значение диапазона критериев: диапазон значений соответствия критериев: массив результатов n: n-й по величине Примечание. Не вставляйте фигурные скобки вручную. Это формула массива, вместо клавиши Enter необходимо использовать Ctrl + Shift + Enter, где она возвращает #NUM! ошибка.
Пример:
Все это может сбивать с толку. Итак, давайте протестируем эту формулу, запустив ее на примере, показанном ниже.
Здесь у нас есть детали заказа с датой заказа, регионом и ценой заказа.
Нам нужно узнать цену n-го наименьшего заказа из Восточного региона. Здесь диапазон указан при использовании инструмента Excel с именованным диапазоном.
область (C3: C16)
цена (D3: D16)
Во-первых, нам нужно найти Пятое наименьшее или наименьшее значение с помощью функции МАЛЕНЬКИЙ, а затем выполнить операцию поиска по значениям.
Теперь воспользуемся следующей формулой, чтобы получить сумму
Используйте формулу:
{ = SMALL ( IF ( region = G5 , price ) , F5 )}
Пояснение:
-
Функция
link: / tips-if-condition-in-excel [IF]
проверяет критерии, соответствующие всем значениям в регионе с заданным восточным значением в ячейке G5, и возвращает соответствующие ИСТИННЫЕ значения из диапазона цен.
НАИБОЛЬШИЙ (\ {58,41; 303,63; ЛОЖЬ; 153,34; 82,84; ЛОЖЬ; 520,01; ЛОЖЬ; 177; ЛОЖЬ; ЛОЖЬ; 57,97; 97,72; ЛОЖЬ}), F5)
-
ссылка: / статистические-формулы-excel-small-function [МАЛЕНЬКИЙ]
функция принимает массив, который имеет весь диапазон цен, как показано выше, и возвращает пятое по величине значение из массива, как показано на снимке ниже.
Вы можете просмотреть формулу в поле формулы, как показано на снимке выше. Используйте Ctrl + Shift + Enter, чтобы получить результат.
Как видите, формула массива возвращает 5-ю наименьшую цену 153,34 доллара США с учетом восточного региона.
Вы также можете передать массив как массив, критерии в кавычках и значение n непосредственно в функцию вместо использования ссылки на ячейку или именованного диапазона.
Используйте формулу:
{ = SMALL ( IF ( C3:C16 = "west" , D3:D16 ) , 3 )}
Используйте Ctrl + Shift + Enter
Вы можете видеть, что формула отлично работает там, где в таблице находится n-е наибольшее значение, имеющее критерии.
Вот некоторые наблюдения, показанные ниже.
Примечания:
-
Формула работает только с числами.
-
Не ставьте фигурные скобки вручную. Это формула массива, вместо клавиши Enter необходимо использовать Ctrl + Shift + Enter, где она возвращает #NUM!
ошибка. . Значение не может быть больше, чем количество значений критерия, в противном случае возвращается # ЧИСЛО! Ошибка.
-
Если критерий не соответствует формуле, возвращается ошибка.
-
Массив аргументов должен быть той же длины, что и функция.
Надеюсь, эта статья о том, как найти n-й по величине с критериями и n-й по величине с критериями в Excel, является пояснительной. Дополнительные статьи о формулах поиска в Excel можно найти здесь. Если вам понравились наши блоги, поделитесь ими с друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected]
Похожие статьи
link: / summing-excel-sumproduct-function [Как использовать функцию СУММПРОИЗВ в Excel]
: возвращает СУММ после умножения значений в нескольких массивах в Excel.
link: / summing-sum-if-date-is-between [SUM if date is between]
: возвращает СУММУ значений между заданными датами или периодом в Excel.
link: / summing-sum-if-date-more-than-given-date [Сумма, если дата больше заданной даты]
: * Возвращает СУММУ значений после заданной даты или периода в Excel.
link: / summing-2-way-to-sum-by-month-in-excel [2-способ суммирования по месяцам в Excel]
: * Возвращает СУММУ значений за данный конкретный месяц в excel.
link: / summing-how-to-sum-multiple-columns-with-condition [Как суммировать несколько столбцов с условием]
: возвращает СУММУ значений по нескольким столбцам, имеющим условие в excel `link: / tips-excel- wildcards [Как использовать подстановочные знаки в excel *] `: Подсчет ячеек, соответствующих фразам, с использованием подстановочных знаков в excel
Популярные статьи
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-Повышение-продуктивность [50 ярлыков Excel для повышения производительности]
: быстрее справляйтесь с задачей. Эти 50 ярлыков заставят вас работать в Excel еще быстрее.
link: / formulas-and-functions-Introduction-of-vlookup-function [Как использовать функцию ВПР в Excel]
: Это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листы. link: / tips-countif-in-microsoft-excel [Как использовать функцию СЧЁТЕСЛИ в Excel]
: Подсчет значений с условиями с помощью этой замечательной функции. Вам не нужно фильтровать данные для подсчета определенных значений. Функция Countif важна для подготовки вашей приборной панели.
link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]
: Это еще одна важная функция панели инструментов. Это поможет вам суммировать значения для конкретных условий.