Как посчитать, попадает ли значение в заданный диапазон в Excel?

Как использовать функции СУММЕСЛИ и СУММЕСЛИМН в Excel

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

Эти функции могут применяться для решения различных задач в Excel.

Разбор синтаксиса СУММЕСЛИ

Начать стоит с правил ввода составляющих функции СУММЕСЛИ. Из ее частей формируется СУММЕСЛИМН, поэтому детальное описание второй опустим, а вернемся к ней только в завершающем разделе статьи. Стандартная запись функции выглядит как =СУММЕСЛИ(A1:A10;">1").

Значит, что суммироваться будут только те ячейки, которые подпадают под указанный критерий, то есть больше 1. Это удобно, например, когда вам нужно посчитать, сколько у вас всего значений, меньше, больше или равняются конкретному. Если вызвать окно «Аргументы функции», вы увидите более понятный вариант записи и можете использовать его, указав диапазон и критерий.

Задать значение для «Диапазон_суммирования» нужно только в том случае, если вы ищете значения больше или меньше конкретного, но при этом добавлять в сумму необходимо ячейки из другого столбца (пока это звучит непонятно, но далее вы увидите такое представление функции на наглядном примере).

Запись СУММЕСЛИ при неравенстве

Давайте еще раз рассмотрим использование SUMMESLY. После объявления функции укажите проверяемый диапазон как A1:A20;. Обязательно поставьте точку с запятой, так как она необходима для корректной работы функции.

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

Функция берет указанный вами диапазон и ищет там значения по критерию. Если значения попадают, они включаются в сумму. Затем происходит обращение к следующим ячейкам и так до конца диапазона. В итоге вы видите сумму только из тех чисел, которые соответствуют введенному вами неравенству.

Использование СУММЕСЛИ с текстом

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

Допустим, у вас есть столбец, в котором несколько строк с названием «Морковь». Нужно сделать так, чтобы функция посчитала сумму всего количества морковок, но не брала во внимание другие овощи и фрукты, записанные в том же столбце. Для этого отлично подойдет рассматриваемая функция, а ее запись в таком случае будет выглядеть так:

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

Затем вместо неравенства введите текст для поиска.

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

Это то самое значение «Диапазон_суммирования», о котором я говорил при просмотре окна «Аргументы функции».

На следующем скриншоте вы видите, что морковок в списке всего две, каждая запись имеет значение 12, а это значит, что в сумму попадают только эти два числа, в результате получается 24.

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

Использование функции СУММЕСЛИМН

Последний пример – функция СУММЕСЛИМН, которая похожа на предыдущую, но позволяет работать со множеством аргументов. Возьмем таблицу, где указано наименование продукции с некоторыми одинаковыми названиями, есть разная цена и количество.

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

Объявите функцию СУММЕСЛИМН и сначала запишите тот диапазон, который будете считать. В моем случае это количество груш.

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

Второй критерий – цена, которая должна превышать 10 за единицу. Соответственно, впишите блок с неравенством A1:A10;">10", где A1:A10 – диапазон ячеек, а >10 – критерий.

Нажмите клавишу Enter и ознакомьтесь с результатом. На следующем скриншоте вы видите, что груш с ценой больше 10 довольно много, все их количество суммировалось и отображается в отдельной ячейке.

При первой записи у вас могут возникнуть трудности с правильным написанием функции, ведь она содержит много условий. Я оставлю вам ее отдельно, чтобы вы могли скопировать ее и вставить, подставив вместо текущих диапазонов ячеек свои: =СУММЕСЛИМН(B2:B25;A2:A25;"Груши";C2:C25;">10"). Не забывайте о том, что первый диапазон – то, что вы считаете, далее идет первый критерий – диапазон с названием столбца, потом второй – диапазон с неравенством.

Это лишь несколько примеров использования функций SUMMESLY и SUMMESLYN в Excel. Вы можете использовать полученные знания в своих целях, выполнив необходимые вычисления и упростив взаимодействие с электронной таблицей.

Выборка значений из таблицы Excel по условию

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

Как сделать выборку в Excel по условию

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

Мы реализуем автоматическую выборку с помощью формулы со следующей структурой:

Поле «диапазон_данных_для_выбора» позволяет указать диапазон значений A6:A18 для выбора из таблицы (например, текстовые значения), из которого функция ИНДЕКС выберет одно результирующее значение. «Диапазон» означает область ячеек, числовые значения которых необходимо сравнить, чтобы выбрать первое наименьшее число. Если для второй функции СТРОКА указать «заголовок_столбца», то необходимо указать ссылку на ячейку, содержащую заголовок столбца диапазона числовых значений.

Эта формула, естественно, будет выполняться массивом. Для подтверждения ввода необходимо нажать всю комбинацию клавиш CTRL+SHIFT+Enter, а не только клавишу Enter. Если все сделано правильно, в строке формул появятся фигурные скобки.

Обратите внимание на рисунок ниже, где эта формула была введена в ячейку B3 в массиве:

Выборка соответствующего значения с первым наименьшим номером:

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

Как работает выборка по условию

Функция ИНДЕКС играет важную роль. Его номинальная задача — выбрать из исходной таблицы (указанной в первом аргументе — A6:A18) значения, соответствующие определенным числам. INDEX работает с критериями, определенными во втором (номер строки в таблице) и третьем (номер столбца в таблице) аргументах. Поскольку наш исходный массив A6:A18 имеет только один столбец, мы не указываем третий аргумент в функции INDEX.

Чтобы вычислить номер строки таблицы напротив наименьшего числа в соседнем диапазоне B6:B18 и использовать его в качестве второго аргумента, применяется несколько вычислений.

Функция IF позволяет выбрать значение из списка на основе условия. Первый аргумент определяет, где каждая ячейка в диапазоне B6:B18 проверяется на минимальное числовое значение: IFB6:B18=MINB6:B18. Это создает массив логических значений TRUE и FALSE в памяти программы. В нашем случае 3 элемента массива будут содержать значение TRUE, поскольку минимальное значение 8 содержит 2 дополнительных дубликата в столбце B6:B18.

Следующий шаг — определить, в каких строках интервала находится каждое минимальное значение. Это необходимо сделать, потому что нам нужно определить первое наименьшее значение. Это делается функцией STRING, которая заполняет элементы массива в памяти программы номерами строк листа. Но сначала мы вычтем все эти числа из числа в первой строке таблицы — B5, которое является числом 5. Это происходит потому, что функция ИНДЕКС работает с числами в таблице, а не с числами в рабочем листе Excel. В то же время функция STRING может вернуть только номер строки электронной таблицы. Чтобы избежать несоответствия, скорректируйте порядок номеров строк рабочего листа и таблицы путем вычитания разницы. Например, если таблица находится в строке 5 рабочего листа, то каждая строка в рабочем листе будет на 5 меньше, чем соответствующая строка в рабочем листе.

После выбора всех минимальных значений и сбора всех номеров строк в таблице функция MIN выберет наименьший номер строки. В этой же строке будет указано первое наименьшее число, которое встречается в столбце B6:B18. На основании номера строки функция INDEX выберет соответствующее значение из таблицы A6:A18. В итоге формула возвращает это значение в ячейку B3 как результат вычисления.

Как выбрать значение с наибольшим числом в Excel

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

Если вы хотите изменить условия формулы так, чтобы можно было выбрать первый максимум, но меньше 70 в Excel :

=70;»»;B6:B18));СТРОКА(B6:B18)-СТРОКА(B5);»»)))’ >

Как выбрать первое ненулевое значение минимума в Excel :

Как видите, эти формулы отличаются друг от друга только функциями MIN и MAX и аргументами.

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