Light-electric.com

IT Журнал
15 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция больше в excel

Анализ топовых значений функциями НАИБОЛЬШИЙ и НАИМЕНЬШИЙ

Скорее всего вам известны простые функции МИН (MIN) и МАКС (MAX) , позволяющие быстро найти минимальное или максимальное значение в таблице. Но что если нам требуется найти, например, не самое большое, а 2-е или 5-е значение в ТОПе? Здесь помогут функции НАИБОЛЬШИЙ (LARGE) и НАИМЕНЬШИЙ (SMALL) .

Синтаксис этих функций похож:

=НАИБОЛЬШИЙ( Диапазон ; Позиция )

=НАИМЕНЬШИЙ( Диапазон ; Позиция )

  • Диапазон – диапазон ячеек с числами, которые мы проверяем.
  • Позиция – целое число, представляющее собой позицию (ранг, номер в рейтинге) извлекаемого элемента.

Например, если у нас есть таблица с прибылями по товарам, то с помощью функции НАИБОЛЬШИЙ можно легко определить максимальное (первое в рейтинге) значение из диапазона прибыли (B2:B22), которое фактически будет тождественно формуле МАКС(B2:B22):

Аналогичным образом, функция

… выдаст следующее за ним максимальное (2-е в рейтинге) и т.д.

Массив констант и сумма ТОПов

Что интересно, аргумент Позиция может быть не просто числом, а набором чисел — массивом констант в фигурных скобках. Так, например, для получения суммы первых трех максимальных значений в диапазоне можно использовать формулу с прописанным внутри массивом констант для первых трех позиций (сочетание Ctrl+Shift+Enter в конце можно не нажимать, хотя по факту это и формула массива):

Сортировка формулой

Функцию НАИМЕНЬШИЙ (SMALL) очень удобно использовать для сортировки формулой набора числовых значений. Для этого достаточно сделать вспомогательную нумерацию (1, 2, 3. ) и ссылаться на нее во втором аргументе:

Если вместо НАИМЕНЬШИЙ использовать функцию НАИБОЛЬШИЙ, то сортировка, естественно, будет уже по убыванию.

Только числа

Еще одной полезной особенностью этих функций является то, что они игнорируют все, кроме чисел, т.е. «не видят» текст и логические значения (ИСТИНА, ЛОЖЬ). Это бывает полезно использовать, например, для извлечения всех сумм по заданному наименованию, когда товар встречается больше одного раза и ВПР (VLOOKUP) уже не поможет:

В этом примере функция ЕСЛИ (IF) в столбце С проверяет наименование на соответствие заданному (Огурцы) и выводит сумму или логическую ЛОЖЬ. А для извлечения потом всех полученных сумм из столбца С используется наша функция НАИМЕНЬШИЙ, которая игнорирует ЛОЖЬ — и мы получаем список стоимостей всех сделок по нужному товару.

Функция НАИБОЛЬШИЙ() в EXCEL

Синтаксис

НАИБОЛЬШИЙ ( массив ; k )

Массив — ссылка на диапазон ячеек, содержащие данные, для которых определяется k-ое наибольшее значение. Также возможен ввод массива констант , например, = НАИБОЛЬШИЙ(<10:20:30:40:50>;1)

k — позиция (начиная с наибольшей) в массиве или диапазоне ячеек. Если k ? 0 или k больше, чем количество значений в массиве , то функция НАИБОЛЬШИЙ() возвращает значение ошибки #ЧИСЛО!

Если n — количество значений в массиве , то формула =НАИБОЛЬШИЙ(массив;1) вернет наибольшее (максимальное) значение, а =НАИБОЛЬШИЙ(массив;n) — наименьшее (минимальное). Т.е. формула =НАИБОЛЬШИЙ(массив;1) эквивалентна =МАКС(массив) , а =НАИБОЛЬШИЙ(массив;n) эквивалентна =МИН(массив)

Пустые ячейки, логические значения (ЛОЖЬ и ИСТИНА) и текст функцией игнорируются. Это видно из таблицы в файле примера .

Значение ошибки в ячейке приводит к ошибке в формуле. Прежде чем применять функцию НАИБОЛЬШИЙ () — обработайте ошибку, например с помощью функции ЕСЛИОШИБКА() .

Если в массиве нет ни одного числового значения, то функция вернет значение ошибки #ЧИСЛО!, что выгодно ее отличает от функции МАКС() , возвращающую в этом случае 0!

Значение числа в текстовом формате игнорируется функцией НАИБОЛЬШИЙ () (см. столбец Е на рисунке выше). Перед нахождением наибольшего значения можно попытаться преобразовать все значения в числовой формат. Это можно сделать формулой массива = НАИБОЛЬШИЙ(ЕСЛИ(ЕЧИСЛО(E5:E9+0);E5:E9+0;»»);1)

Необходимо помнить особенность функции НАИБОЛЬШИЙ() при работе со списками чисел, среди которых имеются повторы. Например, если имеется исходный массив <1;2;3; 6 ;6;7>, то третьим наибольшим (по версии функции НАИБОЛЬШИЙ() ) будет считаться 6, а не 3. Все правильно и логично, но иногда об этом забывают. С человеческой точки зрения третьим наибольшим будет все-таки, наверное, 3 (т.е. повторы не учитываются).

Наибольший с учетом условия

В отличие от функции СУММ() и СЧЁТ() у НАИБОЛЬШИЙ () нет аналога СУММЕСЛИ() и СЧЁТЕСЛИ() , позволяющих выполнять вычисления с учетом условия. Но, с помощью формул массива можно получить формулу для нахождения наибольшего с учетом условия (см. здесь ).

Сумма 3-х наибольших

С помощью нестандартной записи второго аргумента можно расширить возможности функции НАИБОЛЬШИЙ() . Например, найдем сумму 3-х наибольших значений из диапазона A5:A9 = СУММ(НАИБОЛЬШИЙ(A5:A9;<1;2;3>))

Второй аргумент введен как константа массива , что позволило найти 3 наибольших значения.

Аналогично можно найти, например, среднее 2-х наибольших: =СРЗНАЧ(НАИБОЛЬШИЙ(A5:A9;<1;2>))

Удивительно, но 2 последние формулы даже не обязательно вводить как формулы массива .

Другие применения функции

Функция НАИБОЛЬШИЙ() является достаточно часто используемой, т.к. она позволяет упорядочивать числовые массивы. Ее можно, например, использовать для сортировки списков и таблиц .

Функция ЕСЛИ в Excel – примеры использования

Функция ЕСЛИ в Excel – это одна из самых простых функций. Именно поэтому она является одной из основных и при этом она очень полезна.

Сейчас мы на примерах рассмотрим, как можно использовать функцию ЕСЛИ в Excel, а также какие задачи мы можем решить с ее помощью.

Что делает функция ЕСЛИ?

Она позволяет создать дерево решений, в котором при выполнении какого-то условия происходит определенное действие. А если это условие не выполняется, то совершается другое действие.

При этом аргумент функции должен быть вопросом, на который возможно 2 варианта ответа: «да» и «нет», «истина» или «ложь».

Читать еще:  Как разгруппировать строки в excel

Вот как может выглядеть это дерево решений.

Итак, функция ЕСЛИ позволяет задать вопрос и указать на 2 варианта вычислений в зависимости от полученного на него ответа. Они и являются тремя аргументами функции.

Синтаксис функции ЕСЛИ

Вот как выглядит синтаксис этой функции и её аргументы:

=ЕСЛИ(логическое выражение, значение если «да», значение если «нет»)

Логическое выражение — (обязательное) условие, которое возвращает значение «истина» или «ложь» («да» или «нет»);

Значение если «да» — (обязательное) действие, которое выполняется в случае положительного ответа;

Значение если «нет» — (обязательное) действие, которое выполняется в случае отрицательного ответа;

Давайте вместе подробнее рассмотрим эти аргументы.

Первый аргумент – это логический вопрос. И ответ этот может быть только «да» или «нет», «истина» или «ложь».

Как правильно задать вопрос? Для этого можно составить логическое выражение, используя знаки “=”, “>”, “ =”, “ ”. Давайте попробуем задать такой вопрос вместе.

Простейший пример применения.

Предположим, вы работаете в компании, которая занимается продажей шоколада в нескольких регионах и работает с множеством покупателей.

Нам необходимо выделить продажи, которые произошли в нашем регионе, и те, которые были сделаны за рубежом. Для этого нужно добавить в таблицу ещё один признак для каждой продажи – страну, в которой она произошла. Мы хотим, чтобы этот признак создавался автоматически для каждой записи (то есть, строки).

В этом нам поможет функция ЕСЛИ. Добавим в таблицу данных столбец “Страна”. Регион “Запад” – это местные продажи («Местные»), а остальные регионы – это продажи за рубеж («Экспорт»).

Как правильно записать?

Устанавливаем курсор в ячейку G2 и вводим знак “=”. Для Excel это означает, что сейчас будет введена формула. Поэтому как только далее будет нажата буква “е”, мы получим предложение выбрать функцию, начинающуюся этой буквы. Выбираем “ЕСЛИ”.

Далее все наши действия также будут сопровождаться подсказками.

В качестве первого аргумента записываем: С2=”Запад”. Как и в других функциях Excel, адрес ячейки можно не вводить вручную, а просто кликнуть на ней мышкой. Затем ставим “,” и указываем второй аргумент.

Второй аргумент – это значение, которое примет ячейка G2, если записанное нами условие будет выполнено. Это будет слово “Местные”.

После этого снова через запятую указываем значение третьего аргумента. Это значение примет ячейка G2, если условие не будет выполнено: “Экспорт”. Не забываем закончить ввод формулы, закрыв скобку и затем нажав “Enter”.

Наша функция выглядит следующим образом:

Наша ячейка G2 приняла значение «Местные».

Теперь нашу функцию можно скопировать во все остальные ячейки столбца G.

А если один из параметров не заполнен?

Если вас не интересует, что будет, к примеру, если интересующее вас условие не выполняется, тогда можно не вводить второй аргумент. К примеру, мы предоставляем скидку 10% в случае, если заказано более 100 единиц товара. Не указываем никакого аргумента для случая, когда условие не выполняется.

Что будет в результате?

Насколько это красиво и удобно – судить вам. Думаю, лучше все же использовать оба аргумента.

И в случае, если второе условие не выполняется, но делать при этом ничего не нужно, вставьте в ячейку пустое значение.

Однако, такая конструкция может быть использована в том случае, если значение «Истина» или «Ложь» будут использованы другими функциями Excel в качестве логических значений.

Обратите также внимание, что полученные логические значения в ячейке всегда выравниваются по центру. Это видно и на скриншоте выше.

Более того, если вам действительно нужно только проверить какое-то условие и получить «Истина» или «Ложь» («Да» или «Нет»), то вы можете использовать следующую конструкцию –

Обратите внимание, что кавычки здесь использовать не нужно. Если вы заключите аргументы в кавычки, то в результате выполнения функции ЕСЛИ вы получите текстовые значения, а не логические.

Рассмотрим, как ещё можно использовать функцию ЕСЛИ.

Использование функции ЕСЛИ с числами.

Точно так же, как мы это делали с текстом, в аргументах функции можно использовать и числа.

Однако для нас важно то, что функция ЕСЛИ позволяет не только заполнять ячейки определёнными числовыми значениями в зависимости от выполнения условия, но также и производить некоторые вычисления.

К примеру, мы предоставляем нашему покупателю скидку в зависимости от суммы покупки. Если сумма больше 100, то он получает скидку 10%.

Назовём столбец Н “Скидка” и в ячейку H2 введём функцию ЕСЛИ, вторым аргументом которой будет формула расчёта скидки.

Итак, мы с вами убедились, что простая на первый взгляд функция ЕСЛИ дает нам на самом деле много возможностей для операций с данными.

Логические функции Excel

Логические выражения используются для записи условий, в которых сравниваются числа, функции, формулы, текстовые или логические значения. Любое логическое выражение должно содержать по крайней мере один оператор сравнения, который определяет отношение между элементами логического выражения. Ниже представлен список операторов сравнения Excel

= Больше или равно

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

Функция ЕСЛИ

Функция ЕСЛИ (IF) имеет следующий синтаксис:

=ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)

Следующая формула возвращает значение 10, если значение в ячейке А1 больше 3, а в противном случае — 20:

В качестве аргументов функции ЕСЛИ можно использовать другие функции. В функции ЕСЛИ можно использовать текстовые аргументы. Например:

Читать еще:  Как посчитать проценты в excel формула

=ЕСЛИ(А1>=4;»Зачет сдал»;»Зачет не сдал»)

Можно использовать текстовые аргументы в функции ЕСЛИ, чтобы при невыполнении условия она возвращала пустую строку вместо 0.

Аргумент логическое_выражение функции ЕСЛИ может содержать текстовое значение. Например:

Эта формула возвращает значение 10, если ячейка А1 содержит строку «Динамо», и 290, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без учета регистра.

Функции И, ИЛИ, НЕ

Функции И (AND), ИЛИ (OR), НЕ (NOT) — позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:

=И(логическое_значение1;логическое_значение2. )
=ИЛИ(логическое_значение1;логическое_значение2. )

Функция НЕ имеет только один аргумент и следующий синтаксис:

=НЕ(логическое_значение)

Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

Приведем пример. Пусть Excel возвращает текст «Прошел», если ученик имеет средний балл более 4 (ячейка А2), и пропуск занятий меньше 3 (ячейка А3). Формула примет вид:

Если значение в ячейке А1 является целым числом, формула читается следующим образом: «Если значение в ячейке А1 равно 100, возвратить строку «Всегда». В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить «Обычно». В противном случае, если значение в ячейке А1 находится между 60 и 80, возвратить строку «Иногда». И, если ни одно из этих условий не выполняется, возвратить строку «Никогда». Всего допускается до 7 уровней вложения функций ЕСЛИ.

Функции ИСТИНА и ЛОЖЬ

Функции ИСТИНА (TRUE) и ЛОЖЬ (FALSE) предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят следующим образом:

=ИСТИНА()
=ЛОЖЬ()

Например, ячейка А1 содержит логическое выражение. Тогда следующая функция возвратить значение «Проходите», если выражение в ячейке А1 имеет значение ИСТИНА:

В противном случае формула возвратит «Стоп».

Функция ЕПУСТО

Если нужно определить, является ли ячейка пустой, можно использовать функцию ЕПУСТО (ISBLANK), которая имеет следующий синтаксис:

=ЕПУСТО(значение)

Аргумент значение может быть ссылкой на ячейку или диапазон. Если значение ссылается на пустую ячейку или диапазон, функция возвращает логическое значение ИСТИНА, в противном случае ЛОЖЬ.

В начало страницы

В начало страницы

#2. Решаем задачи в Excel. Поиск наибольшего значения

В прошлом выпуске мы рассмотрели задачу, в которой нужно было определить сотрудника с максимальным возрастом и вывести его имя в отдельную ячейку.

Предложенное решение этой задачи имеет один существенный недостаток. Дело в том, что функция МАКС возвращает первое максимальное значение из диапазона и на этом весь расчет прекращается. Это означает, что если в списке будет два или больше сотрудников с одинаковым возрастом, то в результатах мы увидим лишь первого по списку.

Давайте решим задачу таким образом, чтобы получился список всех сотрудников, имеющих максимальный возраст.

Итак, первым делом мы по-прежнему должны определить максимальный возраст. Для этого вполне подойдет функция МАКС , которую мы уже использовали ранее.

Ну а далее нам нужно сравнивать возраст каждого сотрудника с максимальным и в случае совпадения выводить имя сотрудника в столбце F ниже.

На первый взгляд ничего сложного, но не так все просто, как кажется и сейчас вы в этом убедитесь.

Дело в том, что все стандартные функции Экселя, которые сразу приходят на ум, выдадут лишь первое значение из диапазона, удовлетворяющее условию. Поэтому нам придется воспользоваться формулой массива .

Формула массива является частью стандартного функционала Экселя и многие даже не догадываются об ее существовании.

По сути, формула массива позволяет выполнять несколько вычислений с одним или несколькими элементами диапазона ячеек, то есть элементами массива. В итоге формула массива возвращает либо один результат вычислений, либо несколько результатов, то есть массив значений, что нам и нужно.

Не буду сейчас вдаваться в подробности, так как эта заметка и так будет довольно длинной. Если вы хотите узнать больше о формуле массива, то напишите об этом в комментариях и я раскрою тему подробнее в одной из следующих заметок.

Итак, давайте решим задачу.

Как и в прошлый раз , воспользуемся функцией ИНДЕКС , которая возвращает значение ячейки, заданного номером строки и номером столбца.

Первый ее аргумент — массив значений. Выбираем все ячейки с именами сотрудников.

Далее мы должны указать второй аргумент — номер строки, из которой будет возвращено значение. Нам нужно выводить значение из столбца A той строки, в которой значение в столбце B равно вычисленному значению максимального возраста (находится в ячейке F1), и тут идеально подойдет функция ЕСЛИ — если вычисленное значение из ячейки F1 равно одному из значений диапазона B2:B6, то нам нужно вывести номер этой строки. Определить номер строки довольно просто и в этом нам поможет функция СТРОКА , которая выводит фактический номер строки, определяемый адресом ячейки.

Так, например, пустая функция СТРОКА() выведет номер строки Экселя, в которой находится сама эта формула. Мы же в формуле должны вывести номер строки из диапазона значений и у нас в первой строке Экселя находится заголовок таблицы, учитывать который не нужно. Поэтому из полученного номера строки вычтем единицу, чтобы исключить строку заголовка.

Ну а в случае когда условие функции ЕСЛИ не выполняется выведем пустую строку, для этого укажем две кавычки. Итоговая формула будет выглядеть так:

Читать еще:  Как изменить высоту ячейки в excel

Вроде бы формула готова, но она работать не будет. Как я уже сказал, мы должны использовать формулу массива и поэтому необходимо подготовить формулу соответствующим образом.

Думаю, что проще будет объяснять уже на готовой формуле. Отличаться она будет лишь дополнительной функцией НАИМЕНЬШИЙ , которой мы «обернем» наше условие.

Эта функция возвращает k -ое наименьшее значение во множестве данных. Множество данных определяется результатом функции ЕСЛИ , а вот аргумент k , нужно будет вычислять, так как он должен изменяться.

Для его вычисления снова воспользуемся функцией СТРОКА , но перед этим давайте разберемся с функцией НАИМЕНЬШИЙ , так как она не самая простая для понимания.

Рассмотрим ее на таком примере — введем диапазон значений 1, 2, 3, 4, 5 и укажем формулу:

То есть нам нужно вывести третье наименьшее значение в диапазоне.

Логично, что в диапазоне чисел третье наименьшее равно 3. Если же мы изменим в диапазоне 3 на 1, то и результат также изменится — на 2.

То есть функция проходит весь диапазон значений и, выстраивает их по возрастанию, а затем отсчитывает k -ое (в нашем примере третье) значение по списку.

Если изменить в диапазоне 2 на 1, то функция вернет единицу, так как третье наименьшее равно ей.

Надеюсь, работа этой функции прояснилась.

Теперь возвращаемся к нашей формуле. Выглядеть она будет так:

Здесь формула СТРОКА()-1 вычисляет аргумент k для функции НАИМЕНЬШИЙ .

Как я уже упоминал, пустая функция СТРОКА возвращает номер строки с формулой. Так как у нас формула находится во второй строке Экселя, то мы можем использовать это значение для вычисления соответствующего аргумента k . Чуть дальше мы рассмотрим работу формулы и вам станет понятен принцип вычисления.

Чтобы сделать формулу формулой массива необходимо нажать не Enter , а сочетание клавиш Ctrl+Shift+Enter .

На то, что формула стала формулой массива указывают фигурные скобки, в которые она была заключена.

Это не текстовые скобки, то есть нельзя их просто набрать с клавиатуры. Эти скобки появляются лишь при нажатии сочетания клавиш Ctrl+Shift+Enter и они указывают на то, что программа воспринимает данную формулу, как формулу массива.

Вы видите, что в ячейке F2 появился правильный результат.

Давайте проанализируем формулу с помощью соответствующего инструмента со вкладки Формулы .

Здесь мы можем по шагам проходить все вычисления в формуле и таким образом разобраться в порядке вычислений и в получаемых значениях.

Так как мы имеем дело с формулой массива, то на выходе будем иметь не одно значение, а массив данных, что мы сейчас и увидим.

В первую очередь проверяется условие в функции ЕСЛИ (текущее вычисление подчеркивается).

Мы видим, что условие выдало пять ответов, то есть массив значений, равный по размеру диапазону ячеек с именами сотрудников. Из пяти ответов только последний ИСТИНА , что мы можем также подтвердить, взглянув на первоначальный диапазон значений, в котором пока только у одного сотрудника возраст равен максимальному.

Далее на базе полученных значений функция СТРОКА выдает массив с номерами строк.

Поскольку условие у нас выполняется только в последней строке, то мы получим четыре пустых значения (кавычки) и цифру 5, соответствующую пятой строке массива данных:

Переходим к функции НАИМЕНЬШИЙ , которая должна вернуть нам первое наименьшее значение. И как раз для того, чтобы автоматически вычислять это значение и была вставлена формула СТРОКА()-1 . Мы изначально вставили формулу во вторую строку Экселя, поэтому она выдаст единицу, а значит функция НАИМЕНЬШИЙ выдаст первое наименьшее значение, которое будет равно 5.

Соответственно, функция ИНДЕКС выдаст значение, находящееся в пятой строке столбца А, а это и есть имя сотрудника с максимальным возрастом:

Чтобы получить диапазон значений нужно размножить эту формулу автозаполнением, но мы столкнемся с проблемой.

Во-первых, в изначальной формуле использовались относительные ссылки и при автозаполнении это проявилось, то есть диапазоны «съехали».

Во-вторых, функция выводит сообщение #ЧИСЛО! из-за особенностей вычислений. Давайте разберемся в этой ситуации.

Во-первых, изменим ссылки на абсолютные с помощью клавиши F4 . Для этого при редактировании формулы устанавливаем текстовый курсор в ссылку и нажимаем F4 .

Не забудем в конце нажать сочетание Ctrl+Shift+Enter , чтобы сделать формулу формулой массива.

Теперь снова автозаполнением протянем формулу на весь диапазон и проанализируем вычисление во второй строке. На определенном этапе в функции НАИМЕНЬШИЙ мы будем искать второе наименьшее значение в диапазоне:

Но в диапазоне есть только одна цифра и именно поэтому появляется ошибка #ЧИСЛО! , поскольку формула возвращает недопустимое значение.

Чтобы избавиться от этой ошибки воспользуемся функцией ЕСЛИОШИБКА . Она работает следующим образом — если вычисление по формуле проходит без ошибок, то возвращается вычисленное значение, а если возникает ошибка, то функция вернет значение, которое мы сами укажем. То есть мы эту функцию пишем в самом начале формулы и затем добавляем еще одно условие — двойные кавычки. Это означает, что если вычисления приведут к ошибке, то в ячейке ничего не отобразится.

Делаем формулу формулой массива с помощью сочетания клавиш и вновь протягиваем ее по диапазону. Ошибки исчезли.

Ну а теперь проверим работу формулы, изменив возраст у сотрудников.

С моей точки зрения, наиболее сложный для понимания элемент формулы — это функция НАИМЕНЬШИЙ и вычисление аргумента k . Поэтому предлагаю посмотреть видео на эту тему, в котором более детально изложен материал:

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector