Evolcom.ru

Бытовая техника
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

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

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

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

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

Синтаксис функции СУММЕСЛИ в Microsoft Excel

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

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

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

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

Ввод диапазона для вычисления при записи функции СУММЕСЛИ в Microsoft Excel

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

Читайте так же:
Как подсчитать значения, разделенные запятыми, в одной ячейке в Excel?

Ввод неравенства при записи функции СУММЕСЛИ в Microsoft Excel

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

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

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

Пример записи с текстовым диапазоном для функции СУММЕСЛИ в Microsoft Excel

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

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

Затем вместо неравенства введите текст для поиска.Ввод значения в текстовом формате для функции СУММЕСЛИ в Microsoft Excel

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

Это то самое значение «Диапазон_суммирования», о котором я говорил при просмотре окна «Аргументы функции».Отображение диапазона суммирования в окне аргументов для функции СУММЕСЛИ в Microsoft Excel

На следующем скриншоте вы видите, что морковок в списке всего две, каждая запись имеет значение 12, а это значит, что в сумму попадают только эти два числа, в результате получается 24.Просмотр результата функции СУММЕСЛИ в Microsoft Excel для текста

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

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

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

Читайте так же:
Как повторно печатать заголовок (верхнюю строку) на каждой странице в Excel?

Таблица для использования функции СУММЕСЛИМН в Microsoft Excel

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

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

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

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

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

При первой записи у вас могут возникнуть трудности с правильным написанием функции, ведь она содержит много условий. Я оставлю вам ее отдельно, чтобы вы могли скопировать ее и вставить, подставив вместо текущих диапазонов ячеек свои: =СУММЕСЛИМН(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 выбиралось первое максимальное значение:

Читайте так же:
Как полутенить ячейку в Excel?

Первое максимальное значение.

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

=70;»»;B6:B18));СТРОКА(B6:B18)-СТРОКА(B5);»»)))’ >Максимальное значение по условию.

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

Больше чем ноль.

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector