EXCEL: функция НАИБОЛЬШИЙ
В этой статье Вы научитесь работать с функцией НАИБОЛЬШИЙ, или, в английской версии LARGE, и узнаете, зачем придумали функцию НАИБОЛЬШИЙ при живой-то функции МАКС.
Нередко, в работе с анализом числовых данных, важно знать наибольшее значение среди рассматриваемых данных. И первое, что приходит в голову, - использовать функцию МАКС. Но, как же быть, если, например, Вы подсчитываете баллы конкурса и среди сотни участников Вам нужно выбрать только 3-х с наилучшим результатом. Здесь и поможет функция НАИБОЛЬШИЙ.
Шлычков Константин
Эксперт и преподаватель Excel
Синтаксис функции НАИБОЛЬШИЙ (англ. LARGE)
= НАИБОЛЬШИЙ (массив; k)
Из увиденного можно отметить, что функция НАИБОЛЬШИЙ содержит в себе 2 аргумента:
Массив (англ. array) определяется выбором диапазона рассматриваемых данных
k - номер позиции, где 1-ым номером считается максимальное значение
Основные правила:
1) функция НАИБОЛЬШИЙ живёт в царстве чисел, а потому не признает и всячески игнорирует пустые ячейки, текстовые и логические данные.
2) какой привет, такой ответ: если в ячейке есть ошибка, то в ответ функция НАИБОЛЬШИЙ без церемоний выдаст ошибку.
3) правило, выведенное логикой: запись НАИБОЛЬШИЙ (массив; 1) выдаст нам максимальное значение, тогда как НАИБОЛЬШИЙ (массив; n) – минимальное значение.
4) не вводите в заблуждение: если k установить в виде негативного значения, либо присвоить значение превышающее количество данных в массиве, то, готовьтесь ко встрече со «#ЗНАЧ!».
2) какой привет, такой ответ: если в ячейке есть ошибка, то в ответ функция НАИБОЛЬШИЙ без церемоний выдаст ошибку.
3) правило, выведенное логикой: запись НАИБОЛЬШИЙ (массив; 1) выдаст нам максимальное значение, тогда как НАИБОЛЬШИЙ (массив; n) – минимальное значение.
4) не вводите в заблуждение: если k установить в виде негативного значения, либо присвоить значение превышающее количество данных в массиве, то, готовьтесь ко встрече со «#ЗНАЧ!».
Обратите внимание, что у функции НАИБОЛЬШИЙ есть аналог, работающий по обратному принципу - функция НАИМЕНЬШИЙ. Функция НАИМЕНЬШИЙ (массив; k), в английской версии SMALL (array; k), возвращает значение k-го наименьшего элемента из выбранного массива данных.
Шлычков Константин
Эксперт и преподаватель Excel
Пример 1. Конкурс «Эксперт по Excel»
Ну, а тем временем, закончился конкурс «Эксперт по Excel» и комиссия приступила к выставлению баллов, посмотрим на результаты проверки:
В конкурсе участников пусть и не так много, но в любом случае, хотелось бы поскорее выявить 3-х победителей и вручить им награды.
Если применить функцию МАКС, то я получу только результат 1-го места – 84 балла. Но, что делать дальше? Как быстро и безошибочно выявить претендента на 2-е, 3-е место?
Конечно, если Вы счастливый обладатель версии Excel 365, то воспользоваться функцией СОРТ будет гораздо целесообразнее. Подробнее о функции СОРТ Вы можете прочитать в моей статье "Функции СОРТ с СОРТПО".
Шлычков Константин
Эксперт и преподаватель Excel
С другой стороны, может показаться, что проще было бы воспользоваться СОРТИРОВКОЙ по убыванию. Однако, если один из членов жюри перепутает баллы нескольких участников, то СОРТИРОВКУ придётся применять снова в зависимости от частоты изменений. А это, согласитесь, не самый эффективный способ решения задачи.
Поэтому, в качестве эффективного решения предлагаю Вам функцию НАИБОЛЬШИЙ:
Шаг 1: Выберите ячейку для формулы
Шаг 2: Через знак "=" введите название функции НАИБОЛЬШИЙ
Шаг 3: В качестве аргумента "массив" выберите рассматриваемую область - диапазон
Шаг 4: В качестве аргумента "k" укажите номер позиции
Шаг 2: Через знак "=" введите название функции НАИБОЛЬШИЙ
Шаг 3: В качестве аргумента "массив" выберите рассматриваемую область - диапазон
Шаг 4: В качестве аргумента "k" укажите номер позиции
Давайте, также заменим функцию МАКС на НАИБОЛЬШИЙ для победителя.
Однако, стремление быть максимально эффективным никак не дает покоя: можно ли решить данную задачу еще быстрее? Да. Например, можно немного иначе представить результирующие данные, убрав слово "место" и оставив только числовые значения. Так получится не записывать отдельно каждый k в функции, а просто выбрать ячейку с номером позиции и продлить формулу вот таким образом.
Стоп. Что-то пошло не так. Заметили? Сейчас объясню.
На данном этапе важно помнить о фиксации ячеек, чтобы с каждой последующей итерацией диапазон выбранных ячеек не сдвигался вниз на шаг, искажая результаты.
Зафиксировать ячейку, сделав ее абсолютной, можно с помощью знака $.Так, поставив $ перед заголовком строки и столбца получим ссылку на абсолютную ячейку в таком виде: $A$1.
Обратите внимание, что знак $ ставится не только вручную, но и с помощью клавиши F4, для этого необходимо выделить заголовок ячейки A1 и нажать на горячую клавишу F4
Шлычков Константин
Эксперт и преподаватель Excel
Отлично. С баллами разобрались, теперь осталось понять кому эти баллы принадлежат.
В данном случае нам помогут две функции ИНДЕКС и ПОИСКПОЗ. Подробнее о функциях Вы можете прочитать в моей статье "Поиск с функциями ИНДЕКС и ПОИСКПОЗ".
Что же, пришло время награждать победителей.
В данном случае нам помогут две функции ИНДЕКС и ПОИСКПОЗ. Подробнее о функциях Вы можете прочитать в моей статье "Поиск с функциями ИНДЕКС и ПОИСКПОЗ".
Что же, пришло время награждать победителей.
Конкурс прошёл, а вопросы остались. Ведь, чем больше углубляешься в новую тему, тем больше вопросов возникает. И функция НАИБОЛЬШИЙ не исключение. Например, может ли эта функция работать с датами и временем?
Обратите внимание, что дата на самом деле является количеством дней, прошедших с 1 января 1900 года (в Excel взято за точку отсчета). Даты и время можно записать в виде числа.
Шлычков Константин
Эксперт и преподаватель Excel
Да, функция НАИБОЛЬШИЙ работает с датами, как с числовыми значениями, даже в случае с длинным форматом даты - это всего лишь настройки представления значения. Давайте разберем на примере.
Пример 2. Отчёт HR-менеджера
Менеджера по персоналу Ольгу попросили сделать отчёт за полгода. В отчёте необходимо выписать даты от самой близкой к нынешней до самой ранней. Посмотрим, какие данные у Ольги есть:
В таблице представлено два вида записи дат: короткий и длинный.
Для начала применим функцию НАИБОЛЬШИЙ к короткому формату даты.
Для начала применим функцию НАИБОЛЬШИЙ к короткому формату даты.
Ну вот, получили какой-то непонятный набор цифр. Почему?
Дело в том, что формат ячеек, где прописана функция – "общий", но на данном этапе работа производится с датами, а значит, перед тем, как применять функцию, важно обратить внимание на формат. Однако, даже если получилось так как в указанном примере, всё ещё поправимо:
Шлычков Константин
Эксперт и преподаватель Excel
Теперь, перейдем к рассмотрению работы функции НАИБОЛЬШИЙ с длинным форматом даты.
Всё получилось, только стоит привести полученные результаты к соответствующему формату.
Итак, сегодня мы разобрали несложную, но нужную тему о функции НАИБОЛЬШИЙ. Надеюсь, Вам было интересно и познавательно.
