Содержание
- Примеры функции СЧЁТЕСЛИ с несколькими условиями в Microsoft Excel
- Описание синтаксиса функции
- Вариант 1: Счет текстовых условий
- Вариант 2: Счет числовых условий
- Использование критериев в Microsoft Excel
- Применение критериев
- СЧЁТЕСЛИ
- СЧЁТЕСЛИМН
- СУММЕСЛИМН
- Условное форматирование
Примеры функции СЧЁТЕСЛИ с несколькими условиями в Microsoft Excel
Функция «СЧЁТЕСЛИ», часто использующаяся в Excel, относится к критериям. Если у вас еще нет базовых понятий о функциях, относящихся к этой группе, ознакомьтесь с общим их описанием в инструкции по ссылке ниже.
Описание синтаксиса функции
Начать следует с описания синтаксиса самой функции «СЧЁТЕСЛИ», поскольку при работе с несколькими условиями понадобится создавать большую формулу, учитывая все особенности.
- Для простоты понимания структуры предлагаем объявить в поле =СЧЁТЕСЛИ() и сразу перейти к меню «Аргументы функции».
- В нем вы увидите строки «Диапазон» и «Критерий». Нажмите по первой для ее активации.
Внизу под полями виден результат, что уже свидетельствует о правильном составлении функции. Сейчас добавить еще одно условие нельзя, поэтому формулу придется расширять, о чем и пойдет речь в следующих двух вариантах.
Вариант 1: Счет текстовых условий
Разберем ситуацию, когда есть два столбца с определенными значениями, которыми в нашем случае выступают месяцы. Нужно сделать выборку из них, чтобы в результате показывало значение того, сколько ячеек соответствуют заданному условию. Объединяются два условия при помощи одной простой формулы.
- Создайте первую часть функции «СЧЁТЕСЛИ», указав в качестве диапазона первый столбец. Сама функция имеет стандартный вид: =СЧЁТЕСЛИ(A2:A25;»Критерий») .
Проверьте результат, который отобразится в заданной клетке. Если вдруг возникла ошибка, удостоверьтесь в том, что вы правильно соблюли синтаксис функции, а ячейки в диапазоне имеют соответствующий формат.
Вариант 2: Счет числовых условий
С числовыми условиями дела обстоят точно так же, но на этот раз давайте рассмотрим более детальный пример ручного составления функции, учитывая каждую деталь.
- После объявления «СЧЁТЕСЛИ» в круглых скобках задайте диапазон чисел «A1:A25», где вместо указанных ячеек подставьте необходимые.
Источник
Использование критериев в Microsoft Excel
Программа Microsoft Excel является не просто табличным редактором, а ещё и мощнейшим приложением для различных вычислений. Не в последнюю очередь такая возможность появилась благодаря встроенным функциям. С помощью некоторых функций (операторов) можно задавать даже условия вычисления, которые принято называть критериями. Давайте подробнее узнаем, каким образом можно их использовать при работе в Экселе.
Применение критериев
Критерии представляют собой условия, при которых программа выполняет определенные действия. Они применяются в целом ряде встроенных функций. В их названии чаще всего присутствует выражение «ЕСЛИ». К данной группе операторов, прежде всего, нужно отнести СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СУММЕСЛИ, СУММЕСЛИМН. Кроме встроенных операторов, критерии в Excel используются также при условном форматировании. Рассмотрим их применение при работе с различными инструментами данного табличного процессора более подробно.
СЧЁТЕСЛИ
Главной задачей оператора СЧЁТЕСЛИ, относящегося к статистической группе, является подсчет занятых различными значениями ячеек, которые удовлетворяют определенному заданному условию. Его синтаксис следующий:
Как видим, у данного оператора два аргумента. «Диапазон» представляет собой адрес массива элементов на листе, в которых следует произвести подсчет.
«Критерий» — это аргумент, который задаёт условие, что именно должны содержать ячейки указанной области, чтобы быть включенными в подсчет. В качестве параметра может быть использовано числовое выражение, текст или ссылка на ячейку, в которой критерий содержится. При этом, для указания критерия можно использовать следующие знаки: « » («больше»), «=» («равно»), «<>» («не равно»). Например, если задать выражение « 15000».
После того, как все вышеуказанные манипуляции произведены, клацаем по кнопке «OK». 
СЧЁТЕСЛИМН
Следующей функцией, которая оперирует критериями, является СЧЁТЕСЛИМН. Она также относится к статистической группе операторов. Задачей СЧЁТЕСЛИМН является подсчет ячеек в указанном массиве, которые удовлетворяют определенному набору условий. Именно тот факт, что можно задать не один, а несколько параметров, и отличает этого оператора от предыдущего. Синтаксис следующий:
«Диапазон условия» является идентичным первому аргументу предыдущего оператора. То есть, он представляет собой ссылку на область, в которой будет производиться подсчет ячеек, удовлетворяющих указанным условиям. Данный оператор позволяет задать сразу несколько таких областей.
«Условие» представляет собой критерий, который определяет, какие элементы из соответствующего массива данных войдут в подсчет, а какие не войдут. Каждой заданной области данных нужно указывать условие отдельно, даже в том случае, если оно совпадает. Обязательно требуется, чтобы все массивы, используемые в качестве областей условия, имели одинаковое количество строк и столбцов.
Для того, чтобы задать несколько параметров одной и той же области данных, например, чтобы подсчитать количество ячеек, в которых расположены величины больше определенного числа, но меньше другого числа, следует в качестве аргумента «Диапазон условия» несколько раз указать один и тот же массив. Но при этом в качестве соответствующих аргументов «Условие» следует указывать разные критерии.
На примере все той же таблицы с недельной выручкой магазинов посмотрим, как это работает. Нам нужно узнать количество дней недели, когда доход во всех указанных торговых точках достигал установленной для них нормы. Нормы выручки следующие:
- Магазин 1 – 14000 рублей;
- Магазин 2 – 15000 рублей;
- Магазин 3 – 24000 рублей;
- Магазин 4 – 11000 рублей;
- Магазин 5 – 32000 рублей.
- Для выполнения вышеуказанной задачи, выделяем курсором элемент рабочего листа, куда будет выводиться итог обработки данных СЧЁТЕСЛИМН. Клацаем по иконке «Вставить функцию».
- Перейдя в Мастер функций, снова перемещаемся в блок «Статистические». В перечне следует отыскать наименование СЧЁТЕСЛИМН и произвести его выделение. После выполнения указанного действия требуется произвести нажатие на кнопку «OK».
- Вслед за выполнением вышеуказанного алгоритма действий открывается окно аргументов СЧЁТЕСЛИМН.
В поле «Диапазон условия1» следует ввести адрес строки, в которой расположены данные по выручке Магазина 1 за неделю. Для этого ставим курсор в поле и выделяем соответствующую строку в таблице. Координаты отображаются в окне.
Учитывая, что для Магазина 1 дневная норма выручки составляет 14000 рублей, то в поле «Условие 1» вписываем выражение «>14000».
В поля «Диапазон условия2 (3,4,5)» следует внести координаты строк с недельной выручкой соответственно Магазина 2, Магазина 3, Магазина 4 и Магазина 5. Действие выполняем по тому же алгоритму, что и для первого аргумента данной группы.
В поля «Условие2», «Условие3», «Условие4» и «Условие5» вносим соответственно значения «>15000», «>24000», «>11000» и «>32000». Как нетрудно догадаться, эти значения соответствуют интервалу выручки, превышающую норму для соответствующего магазина.
После того, как был произведен ввод всех необходимых данных (всего 10 полей), жмем на кнопку «OK».
- Программа производит подсчет и выводит результат на экран. Как видим, он равен числу 3. Это означает, что в трех днях из анализируемой недели выручка во всех торговых точках превышала установленную для них норму.
Теперь несколько изменим задачу. Нам следует посчитать количество дней, в которых Магазин 1 получил выручку, превышающую 14000 рублей, но меньшую, чем 17000 рублей.
- Ставим курсор в элемент, где будет произведен вывод на лист результатов подсчета. Клацаем по пиктограмме «Вставить функцию» над рабочей площадью листа.
- Так как мы совсем недавно применяли формулу СЧЁТЕСЛИМН, то теперь не обязательно переходить в группу «Статистические»Мастера функций. Наименование данного оператора можно найти в категории «10 недавно использовавшихся». Выделяем его и щелкаем по кнопке «OK».
- Открывается уже знакомое нам окошко аргументов оператора СЧЁТЕСЛИМН. Ставим курсор в поле «Диапазон условия1» и, произведя зажим левой кнопки мыши, выделяем все ячейки, в которых содержится выручка по дням Магазина 1. Они расположены в строке, которая так и называется «Магазин 1». После этого координаты указанной области будут отражены в окне.
Далее устанавливаем курсор в поле «Условие1». Тут нам нужно указать нижнюю границу значений в ячейках, которые будут принимать участие в подсчете. Указываем выражение «>14000».
В поле «Диапазон условия2» вводим тот же адрес тем же способом, который вводили в поле «Диапазон условия1», то есть, опять вносим координаты ячеек со значениями выручки по первой торговой точке.
В поле «Условие2» указываем верхнюю границу отбора: « СУММЕСЛИ
Ещё одним оператором, который использует критерии, является СУММЕСЛИ. В отличие от предыдущих функций, он относится к математическому блоку операторов. Его задачей является суммирование данных в ячейках, которые соответствуют определенному условию. Синтаксис таков:
Аргумент «Диапазон» указывает на область ячеек, которые будут проверяться на соблюдение условия. По сути, он задается по тому же принципу, что и одноименный аргумент функции СЧЁТЕСЛИ.
«Критерий» — является обязательным аргументом, задающим параметр отбора ячеек из указанной области данных, которые будут суммироваться. Принципы указания те же, что и у аналогичных аргументов предыдущих операторов, которые были рассмотрены нами выше.
«Диапазон суммирования» — это необязательный аргумент. Он указывает на конкретную область массива, в которой будет производиться суммирование. Если его опустить и не указывать, то по умолчанию считается, что он равен значению обязательного аргумента «Диапазон».
Теперь, как всегда, рассмотрим применение данного оператора на практике. На основе той же таблицы перед нами стоит задача подсчитать сумму выручки в Магазине 1 за период, начиная с 11.03.2017.
- Выделяем ячейку, в которой будет производиться вывод результата. Щелкаем по пиктограмме «Вставить функцию».
- Перейдя в Мастер функций в блоке «Математические» находим и выделяем наименование «СУММЕСЛИ». Клацаем по кнопке «OK».
- Запускается окно аргументов функции СУММЕСЛИ. В нём имеется три поля, соответствующих аргументам указанного оператора.
В поле «Диапазон» вводим область таблицы, в которой будут располагаться значения, проверяемые на соблюдение условий. В нашем случае это будет строка дат. Ставим курсор в данное поле и выделяем все ячейки, в которых содержатся даты.
Так как нам нужно сложить только суммы выручки, начиная с 11 марта, то в поле «Критерий» вбиваем значение «>10.03.2017».
В поле «Диапазон суммирования» нужно указать область, значения которой, отвечающие указанным критериям, будут суммироваться. В нашем случае это значения выручки строки «Магазин1». Выделяем соответствующий массив элементов листа.
После того, как произведено введение всех указанных данных, жмем на кнопку «OK». 
СУММЕСЛИМН
Завершим изучение операторов, которые используют критерии, остановившись на функции СУММЕСЛИМН. Задачей данной математической функции является суммирование значений указанных областей таблицы, отобранных по нескольким параметрам. Синтаксис указанного оператора таков:
«Диапазон суммирования» — это аргумент, являющийся адресом того массива, ячейки в котором, отвечающие определенному критерию, будут складываться.
«Диапазон условия» — аргумент, представляющий собой массив данных, проверяемый на соответствие условию;
«Условие» — аргумент, представляющий собой критерий отбора для сложения.
Данная функция подразумевает операции сразу с несколькими наборами подобных операторов.
Посмотрим, как данный оператор применим для решения задач в контексте нашей таблицы выручки от реализации в торговых точках. Нам нужно будет подсчитать доход, который принес Магазин 1 за период с 09 по 13 марта 2017 года. При этом при суммировании дохода должны учитываться только те дни, выручка в которых превысила 14000 рублей.
- Снова выделяем ячейку для вывода итога и клацаем по пиктограмме «Вставить функцию».
- В Мастере функций, прежде всего, выполняем перемещение в блок «Математические», а там выделяем пункт под названием «СУММЕСЛИМН». Производим клик по кнопке «OK».
- Производится запуск окошка аргументов оператора, наименование которого было указано выше.
Устанавливаем курсор в поле «Диапазон суммирования». В отличие от последующих аргументов, этот единственный в своем роде и указывает на тот массив значений, где будет производиться суммирование подошедших под указанные критерии данных. Затем выделяем область строки «Магазин1», в которой размещены значения выручки по соответствующей торговой точке.
После того, как адрес отобразился в окне, переходим к полю «Диапазон условия1». Тут нам нужно будет отобразить координаты строки с датами. Производим зажим левой кнопки мыши и выделяем все даты в таблице.
Ставим курсор в поле «Условие1». Первым условием является то, что нами будут суммироваться данные не ранее 09 марта. Поэтому вводим значение «>08.03.2017».
Перемещаемся к аргументу «Диапазон условия2». Тут нужно внести те же координаты, которые были записаны в поле «Диапазон условия1». Делаем это тем же способом, то есть, путем выделения строчки с датами.
Устанавливаем курсор в поле «Условие2». Вторым условием является то, что дни, за которые будет суммироваться выручка, должны быть не позже 13 марта. Поэтому записываем следующее выражение: « 14000».
После выполнения последнего действия клацаем по кнопке «OK». 
Условное форматирование
Последним, описанным нами, инструментом, при работе с которым используются критерии, является условное форматирование. Он выполняет указанный вид форматирования ячеек, которые отвечают заданным условиям. Взглянем на пример работы с условным форматированием.
Выделим те ячейки таблицы синим цветом, где значения за день превышают 14000 рублей.
- Выделяем весь массив элементов в таблице, в котором указана выручка торговых точек по дням.
- Передвигаемся во вкладку «Главная». Клацаем по пиктограмме «Условное форматирование», размещенной в блоке «Стили» на ленте. Открывается список действий. Клацаем в нём по позиции «Создать правило…».
- Активируется окошко генерации правила форматирования. В области выбора типа правила выделяем наименование «Форматировать только ячейки, которые содержат». В первом поле блока условий из списка возможных вариантов выбираем «Значение ячейки». В следующем поле выбираем позицию «Больше». В последнем — указываем само значение, больше которого требуется отформатировать элементы таблицы. У нас это 14000. Чтобы выбрать тип форматирования, клацаем по кнопке «Формат…».
- Активируется окно форматирования. Передвигаемся во вкладку «Заливка». Из предложенных вариантов цветов заливки выбираем синий, щелкая по нему левой кнопкой мыши. После того, как выбранный цвет отобразился в области «Образец», клацаем по кнопке «OK».
- Автоматически происходит возврат к окну генерации правила форматирования. В нём также в области «Образец» отображается синий цвет. Тут нам нужно произвести одно единственное действие: клацнуть по кнопке «OK».
- После выполнения последнего действия, все ячейки выделенного массива, где содержится число большее, чем 14000, будут залиты синим цветом.
Более подробно о возможностях условного форматирования рассказывается в отдельной статье.
Как видим, с помощью инструментов, использующих при своей работе критерии, в Экселе можно решать довольно разноплановые задачи. Это может быть, как подсчет сумм и значений, так и форматирование, а также выполнение многих других задач. Основными инструментами, работающими в данной программе с критериями, то есть, с определенными условиями, при выполнении которых активируется указанное действие, является набор встроенных функций, а также условное форматирование.
Источник
Этот учебник научит вас нескольким простым способам суммирования нескольких столбцов в Excel на основе одного или нескольких критериев.
Выполнение условной суммы в Excel не составляет труда, если все значения, которые нужно суммировать, находятся в одном столбце. Суммирование нескольких столбцов представляет собой проблему, поскольку обе функции СУММЕСЛИ и СУММЕСЛИМН требуют, чтобы диапазон суммы и диапазон критериев были одинакового размера. К счастью, когда нет прямого способа что-то сделать, всегда есть обходной путь 🙂
- Суммировать несколько столбцов с одним условием
- Суммируйте несколько столбцов с двумя или более критериями
Сумма Excel Если: несколько столбцов, один критерий
Прежде всего, давайте узнаем, какую именно проблему мы пытаемся решить. Предположим, у вас есть таблица ежемесячных продаж, как показано ниже. Поскольку он был объединен из нескольких региональных отчетов, для одного и того же продукта есть несколько записей:
Вопрос в том, как получить общий объем продаж определенного товара?
Первая идея, которая приходит на ум, — использовать формулу СУММЕСЛИ в чистом виде:
=СУММЕСЛИ(A2:A10, «яблоки», C2:E10)
К сожалению, это не сработает. Причина в том, что размеры сумма_диапазон определяются Excel автоматически на основе размеров диапазон аргумент. Поскольку диапазон наших критериев включает только один столбец (A2:A10), то же самое относится и к диапазону суммы (C2:C10). сумма_диапазон параметр, определенный в формуле (C2:E10), фактически определяет только верхнюю левую ячейку диапазона, который будет суммироваться. В результате приведенная выше формула суммирует продажи яблок только в столбце C. Не то, что мы ищем, а?
Самое простое рабочее решение, которое напрашивается само собой, — создать вспомогательный столбец, суммирующий числа для каждой отдельной строки, а затем использовать этот столбец для сумма_диапазон.
Итак, продолжайте и поместите формулу СУММ в F2, затем перетащите ее вниз на столько ячеек, сколько необходимо:
=СУММ(C2:E2)
После этого можно быстро выполнить работу:
=СУММЕСЛИ(A2:A10, I1, F2:F10)
Где I1 представляет интерес.
В приведенной выше формуле сумма_диапазон имеет такой же размер, как диапазон (1 столбец и 9 строк), поэтому работает без сучка и задоринки:
Если в макете вашего рабочего листа нет места для дополнительных столбцов, примените одно из следующих решений.
SUMIF несколько столбцов
Идея состоит в том, чтобы написать отдельную формулу СУММЕСЛИ для каждого из столбцов, которые вы хотите суммировать, а затем сложить результаты:
СУММ(СУММЕСЛИ(…), СУММЕСЛИ(…), СУММЕСЛИ(…))
Или же
СУММЕСЛИ(…) + СУММЕСЛИ(…) + СУММЕСЛИ(…)
Практическая реализация выглядит следующим образом:
=СУММ(СУММЕСЛИ(A2:A10,H1,C2:C10), СУММЕСЛИ(A2:A10,H1,D2:D10), СУММЕСЛИ(A2:A10,H1,E2:E10))
Или же
=СУММЕСЛИ(A2:A10, H1, C2:C10) + СУММЕСЛИ(A2:A10, H1, D2:D10) + СУММЕСЛИ(A2:A10, H1, E2:E10)
Вы также можете «жестко закодировать» условие в формуле, если это необходимо:
=СУММЕСЛИ(A2:A10, «Яблоки», C2:C10) + СУММЕСЛИ(A2:A10, «Яблоки», D2:D10) + СУММЕСЛИСЛИ(A2:A10, «Яблоки», E2:E10)
Это прекрасно работает для разумного количества столбцов, но для большого набора данных формула становится слишком длинной и трудной для чтения. В этом случае нижеприведенные решения являются более подходящими.
СУММ как формула массива
Другой способ сделать сумму, если в нескольких столбцах на основе одного критерия, состоит в том, чтобы построить формулу массива:
СУММА((сумма_диапазон) * (—(критерии_диапазонзнак равнокритерии)))
Для нашего примера набора данных формула принимает следующий вид:
=СУММ((C2:E10)*(—(A2:A10=H1)))
Или же
=СУММ((C2:E10)*(—(A2:A10=»Яблоки»)))
В Excel 2019 и более ранних версиях вы должны нажать Ctrl + Shift + Enter, чтобы правильно завершить формулу. В Excel 365 и Excel 2021 это работает как обычная формула благодаря встроенной поддержке динамических массивов.
Как работает эта формула:
Основная концепция состоит в том, чтобы умножить элементы этих двух массивов:
- (C2:E10) — все значения в диапазоне суммы. В нашем случае массив содержит 27 элементов (3 столбца и 9 строк: {250,120,210;155,180,210;130,175,125; …}
- (—(A2:A10=H1)) — сравнивает каждое значение в A2:A10 с целевым элементом в H1. Результатом является массив значений TRUE (условие выполнено) и FALSE (условие не выполнено), который затем преобразуется в массив из 1 и 0 с помощью двойного унарного оператора: {0;1;0 ;0;1;0;0;1;1}
Обратите внимание, что первый массив двумерный (каждый столбец данных разделен запятой, а каждая строка — точкой с запятой), а второй — одномерный вертикальный массив (1 столбец данных, строки разделены точкой с запятой). ). Когда два массива умножаются, все элементы двумерного массива в данной строке умножаются на соответствующий элемент одномерного массива:
Поскольку умножение на ноль дает ноль, выживают только числа, для которых критерий ИСТИНА, и функция СУММ складывает их:
=СУММ({0,0,0;155,180,210;0,0,0;0,0,0;160,140,170;0,0,0;0,0,0;…})
Чтобы упростить понимание логики формулы, вы можете написать первый множитель следующим образом:
=СУММ((C2:C10 + D2:D10 + E2:E10) * (—(A2:A10=H1)))
Это создаст массив сумм по строкам (как вспомогательный столбец в самом первом примере), который затем умножается на массив из 1 и 0:
{580;545;430;615;470;750;550;620;570}*{0;1;0;0;1;0;0;1;1}
Результат умножения подается в SUM:
=СУММ({0;545;0;0;470;0;0;620;570})
Не нравится использовать формулы массивов на листе? Я тоже. Что ж, давайте проверим следующее решение 🙂
формула СУММПРОИЗВ
Стратегию, описанную в приведенном выше примере, также можно реализовать с помощью функции СУММПРОИЗВ.
СУММПРОИЗВ((сумма_диапазон) * (критерии_диапазонзнак равнокритерии))
Реальная формула выглядит следующим образом:
=СУММПРОИЗВ((C2:E10) * (A2:A10=H1))
Логика формулы такая же, как и в предыдущем примере. Прелесть функции СУММПРОИЗВ заключается в том, что она изначально поддерживает массивы, поэтому она прекрасно работает как обычная формула во всех версиях Excel.
Сумма Excel, если: несколько столбцов, несколько критериев
Три подхода, которые мы использовали для сложения нескольких столбцов с одним критерием, также будут работать для условной суммы с несколькими критериями. Формулы просто станут немного сложнее.
СУММЕСЛИМН + СУММЕСЛИМН для суммирования нескольких столбцов
Для суммирования ячеек, соответствующих нескольким критериям, обычно используется функция СУММЕСЛИМН. Проблема в том, что, как и его аналог с одним критерием, СУММЕСЛИМН не поддерживает диапазон суммы из нескольких столбцов. Чтобы преодолеть это, мы пишем несколько СУММЕСЛИМН, по одному на каждый столбец в диапазоне сумм:
СУММ(СУММЕСЛИМН(…), СУММИММН(…), СУММИММН(…))
Или же
СУММЕСЛИМН(…) + СУММЕСЛИМН(…) + СУММЕСЛИМН(…)
Например, для суммирования продаж винограда (H1) в Северном регионе (H2) используется следующая формула:
=СУММЕСЛИМН(C2:C10, A2:A10, H1, B2:B10, H2) + СУММЕСЛИМН(D2:D10, A2:A10, H1, B2:B10, H2) + СУММЕСЛИМН(E2:E10, A2:A10, H1) , В2:В10, Н2)
Формула массива для условного суммирования нескольких столбцов
Формула СУММ для нескольких критериев очень похожа на формулу для одного критерия — вы просто включаете дополнительные пары критерии_диапазон=критерий:
СУММА((сумма_диапазон) * (—(критерии_диапазон1знак равнокритерии1)) * (—(критерии_диапазон2знак равнокритерии2)))
Например, чтобы суммировать продажи товара в H1 и региона в H2, формула выглядит следующим образом:
=СУММ((C2:E10) * (—(A2:A10=H1)) * (—(B2:B10=H2)))
В Excel 2019 и более ранних версиях не забудьте нажать Ctrl + Shift + Enter, чтобы сделать формулу массива CSE. В динамических массивах Excel 365 и 2021 обычная формула будет работать нормально, как показано на снимке экрана:
Формула СУММПРОИЗВ с несколькими критериями
Самый простой способ суммировать несколько столбцов на основе нескольких критериев — это формула СУММПРОИЗВ:
СУММПРОИЗВ((сумма_диапазон) * (критерии_диапазон1знак равнокритерии1) * (критерии_диапазон2знак равнокритерии2))
Как видите, она очень похожа на формулу СУММ, но не требует дополнительных манипуляций с массивами.
Для суммирования нескольких столбцов с двумя критериями используется следующая формула:
=СУММПРОИЗВ((C2:E10) * (A2:A10=H1) * (B2:B10=H2))
Это 3 способа суммирования нескольких столбцов на основе одного или нескольких условий в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Сумма, если несколько столбцов — примеры (файл .xlsx)
Вас также могут заинтересовать:
Skip to content
В статье показано, как выполнять быстрый поиск с несколькими условиями в Excel с помощью ИНДЕКС и ПОИСКПОЗ.
Хотя Microsoft Excel предоставляет специальные функции для вертикального и горизонтального поиска, опытные пользователи обычно заменяют их комбинацией функций ПОИСКПОЗ и ИНДЕКС, которая во многих отношениях превосходит ВПР и ГПР. К примеру, можно искать два или более критерия в столбцах и строках.
Мы постараемся подробно объяснить синтаксис и логику вычислений, чтобы вы могли легко создать формулы для своих конкретных нужд.
- Общая формула поиска по нескольким критериям
- Пример формулы массива
- Поиск по нескольким условиям без формулы массива
- ИНДЕКС ПОИСКПОЗ с несколькими условиями в нескольких строках и столбцах
Как сделать поиск с несколькими условиями
При работе с большими базами данных вы можете иногда оказаться в ситуации, когда нужно что-то найти, но нет уникального идентификатора для поиска. В этом случае поиск с несколькими условиями является единственным решением.
Чтобы найти значение на основе нескольких критериев в отдельных столбцах, используйте эту общую формулу:
{=ИНДЕКС( диапазон_возврата; ПОИСКПОЗ (1; ( критерий1 = диапазон1 ) * ( критерий2 = диапазон2 ) * (…); 0))}
Где:
- Диапазон_возврата — это диапазон, из которого возвращается значение.
- Критерии1 , критерии2 , … – это условия, которые необходимо выполнить.
- Диапазон1 , диапазон2 , … — это диапазоны, на которых должны проверяться соответствующие критерии.
Важное замечание! Это формула массива , и она должна быть введена через Ctrl + Shift + Enter. Появятся {фигурные скобки}, что является визуальным признаком формулы массива в Excel. Не пытайтесь вводить фигурные скобки вручную, это не сработает!
Эта формула представляет собой расширенную версию комбинации ИНДЕКС+ПОИСКПОЗ, которая возвращает совпадение на основе одного критерия. Чтобы оценить несколько условий, мы используем операцию умножения, которая работает как оператор «И» в формулах массива . Ниже вы найдете реальный пример и подробное пошаговое объяснение логики расчетов.
ИНДЕКС ПОИСКПОЗ по нескольким столбцам – пример формулы
В этом примере мы будем использовать таблицу с комбинацией критериев (в нашем случае регион-месяц-товар) в каждой строке. Наша цель — получить данные о продажах определенного товара в данном регионе и в нужном месяце.
Вот наши исходные данные и критерии:
- Диапазон_возврата (продажи) — D2:D13
- Критерий1 (целевой регион) – G1
- Критерий2 (целевой месяц) – G2
- Критерий 3 (целевой товар) — G3
- Диапазон1 (регионы) – A2:A13
- Диапазон2 (месяцы) – B2:B13
- Диапазон3 (товары) – C2:C13
Формула принимает следующий вид:
=ИНДЕКС(D2:D13; ПОИСКПОЗ(1; (G1=A2:A13)*(G2=B2:B13)*(G3=C2:C13); 0))
Запишем формулу в G4, завершим ее, нажав Ctrl+Shift+Enter. Получаем следующий результат:
Как это работает? Разберем пошагово.
Самая сложная часть — это функция ПОИСКПОЗ, так что давайте пошагово рассмотрим логику ее работы:
ПОИСКПОЗ(1; (G1=A2:A13)*(G2=B2:B13)*(G3=C2:C13)
Как вы помните, ПОИСКПОЗ ищет заданное значение в массиве и возвращает относительное его положение в этом массиве.
В нашей формуле аргументы следующие:
- Искомое_значение : 1
- Массив поиска : (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13)
- Тип_соответствия : 0
Шаг 1.
Первый аргумент предельно ясен — функция ищет число 1. Третий аргумент, установленный в 0, означает «точное совпадение», т.е. формула возвращает первое найденное значение, которое точно равно искомому значению.
Вопрос в том, почему мы ищем «1»? Чтобы получить ответ, давайте внимательнее посмотрим на массив поиска, где мы сравниваем каждый наш критерий с соответствующим диапазоном: целевой регион в ячейке G1 со всеми регионами (A2: A13), целевой месяц в G2 со всеми месяцами (B2: B13), и товар в G3 в колонке товаров (C2:C13). В результате этих сравнений мы имеем 3 массива значений ИСТИНА и ЛОЖЬ, где ИСТИНА представляет значения, соответствующие условию. Чтобы визуализировать это, вы можете выбрать отдельное выражение в формуле и нажать клавишу F9 , чтобы увидеть, что оно возвращает:
=ИНДЕКС(D2:D13; ПОИСКПОЗ(1; {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ИСТИНА:ИСТИНА:ИСТИНА}*{ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}*{ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА}; 0))
Шаг 2.
Операция умножения преобразует значения ИСТИНА и ЛОЖЬ в 1 и 0 соответственно:
{0:0:0:0:0:0:1:1:1:1:1:1}*{0:0:1:0:0:1:1:1:0:0:0:0}*{0:1:0:1:0:1:0:1:0:1:0:1}
Шаг 3.
А поскольку умножение на 0 всегда дает 0, результирующий массив содержит 1 только в тех строках, которые соответствуют всем трём условиям:
{0:0:0:0:0:0:0:1:0:0:0:0}
Приведенный выше массив переходит в аргумент массив_поиска функции ПОИСКПОЗ. Функция возвращает порядковый номер позиции с цифрой 1, для которой все критерии имеют значение ИСТИНА (строка 8 в нашем случае). Если в массиве будет несколько единиц, будет определена позиция только первой из них.
Шаг 4.
Число, возвращаемое функцией ПОИСКПОЗ, поступает непосредственно в аргумент номер_строки функции ИНДЕКС(массив, номер_строки, [номер_столбца]):
=ИНДЕКС(D2:D13, 
И это дает результат 680, что является восьмым по счету значением в массиве D2:D13.
Формула ИНДЕКС ПОИСКПОЗ без массива с несколькими критериями
Формула массива, рассмотренная в предыдущем примере, хорошо работает для опытных пользователей. Но если вы строите формулу для кого-то другого, и этот кто-то не знает о работе с формулами массива в Excel, то он может непреднамеренно ее сломать. Например, пользователь может щелкнуть вашу формулу, чтобы изучить ее, а затем нажать Enter вместо Ctrl + Shift + Enter.
В таких случаях было бы разумно избегать массивов и использовать обычную формулу, которая является более сложной, но зато и более защищенной от случайной «поломки»:
ИНДЕКС( диапазон_возврата; ПОИСКПОЗ (1; ИНДЕКС(( критерий1 = диапазон1 ) * ( критерий2 = диапазон2 ) * (..); 0; 1); 0))
Для нашего набора данных формула поиска одновременно по нескольким столбцам выглядит следующим образом:
=ИНДЕКС(D2:D13; ПОИСКПОЗ(1; ИНДЕКС((G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13); 0; 1); 0))
Как работает эта формула?
Поскольку функция ИНДЕКС умеет работать с массивами, мы добавляем еще один ИНДЕКС для обработки массива единиц и нулей, созданного путем умножения двух или более массивов ИСТИНА/ЛОЖЬ. Второй ИНДЕКС настроен с аргументом номер_строки равным нулю, чтобы формула возвращала весь столбец, а не одно значение. Поскольку в любом случае это массив из одного столбца, мы можем безопасно поставить 1 для номер_столбца :
ИНДЕКС({0:0:0:0:0:0:0:1:0:0:0:0};0, 1) возвращает {0:0:0:0:0:0:0:1:0:0:0:0}
Этот массив передается в функцию ПОИСКПОЗ:
ПОИСКПОЗ(1; {0:0:0:0:0:0:0:1:0:0:0:0}; 0)
ПОИСКПОЗ находит номер позиции, для которого все условия – ИСТИНА (точнее, ищет относительное положение цифры «1» в указанном массиве), и передает номер этой позиции в аргумент номер_строки первого ИНДЕКС:
=ИНДЕКС(D2:D13,
Ну и далее получаем нужный результат.
ИНДЕКС ПОИСКПОЗ с несколькими условиями в нескольких строках и столбцах
В этом примере показано, как выполнять поиск, проверяя два или более критерия в строках и столбцах. На самом деле это более сложный случай так называемого «матричного поиска» или «двустороннего поиска» с более чем одной строкой заголовка.
Вот общая формула ИНДЕКС ПОИСКПОЗ с несколькими критериями в строках и столбцах:
{=ИНДЕКС( массив_таблицы ; ПОИСКПОЗ( значение_поиска ; столбец_поиска ; 0); ПОИСКПОЗ( значение_поиска1 & значение_поиска2 ; строка_поиска1 & строка_поиска2 ; 0))}
где:
Массив таблицы — область для поиска, т. е. все значения таблицы, кроме заголовков столбцов и строк.
Значение_поиска — то, что вы ищете по вертикали в столбце.
Столбец_поиска — диапазон столбцов для поиска, обычно это заголовки строк.
Значение_поиска1, значение_поиска2, … — то, что вы ищете по горизонтали в строках.
Строка_поиска1, строка_поиска2, … — диапазоны строк для поиска, обычно это заголовки столбцов.
Важное замечание! Чтобы формула работала корректно, ее нужно вводить как формулу массива при помощи комбинации Ctrl + Shift + Enter.
Это разновидность классической формулы двустороннего поиска в массиве, которая ищет значение на пересечении определенной строки и столбца. Разница в том, что вы объединяете несколько значений и диапазонов поиска для оценки нескольких заголовков столбцов. Чтобы лучше понять логику ее работы, рассмотрим небольшой пример.
В приведенной ниже таблице мы будем искать значение на основе заголовков строк (элементы) и заголовков двух столбцов (регионы и поставщики). То есть, ищем по одному условию по строкам и по двум условиям – по столбцам. Чтобы упростить построение формулы, давайте сначала определим все критерии и диапазоны:
- Массив_таблицы — B3:E4
- Значение_поиска — H1
- Столбец_поиска (заголовки строк: товары) — A3:A5
- Значение_поиска1 (целевой регион) — H2
- Значение_поиска 2 (целевой продавец) — H3
- Строка_поиска1 (заголовки столбцов 1: регионы) — B1:E1
- Строка_поиска2 (заголовки столбцов 2: продавцы) — B2:E2
А теперь подставьте аргументы в общую формулу, описанную выше, и вы получите такой результат:
=ИНДЕКС(B3:E5; ПОИСКПОЗ(H1;A3:A5;0); ПОИСКПОЗ(H2&H3; B1:E1&B2:E2; 0))
Не забудьте завершить формулу, нажав комбинацию клавиш Ctrl + Shift + Enter, после чего поиск по матрице с несколькими условиями будет выполнен успешно:
Пошагово рассмотрим, как работает эта формула.
Поскольку мы ищем и по вертикали, и по горизонтали, то нужно указать номера строк и столбцов для функции ИНДЕКС (массив, номер_строки, номер_столбца).
Шаг 1.
Номер_строки предоставляется функцией ПОИСКПОЗ(H1;A3:A5;0), которая сравнивает целевой элемент (бананы) в H1 с заголовками строк в A3:A5.
ПОИСКПОЗ(«Бананы»;{«Апельсины»:»Бананы»:»Лимоны»};0)
Это дает результат 2, потому что «Бананы» — это второй элемент в указанном списке.
Шаг 2.
Номер_столбца вычисляется путем объединения двух значений поиска и двух массивов поиска: ПОИСКПОЗ(H2&H3; B1:E1&B2:E2; 0)
Необходимым условием является то, что значения поиска должны точно соответствовать заголовкам столбцов и быть объединены в том же порядке. Чтобы проследить процесс поиска, выберите первые два аргумента в формуле ПОИСКПОЗ, затем нажмите F9, и вы увидите, что оценивает каждый аргумент:
ПОИСКПОЗ(«СеверПродавец 2»; {«СеверПродавец 1″;»СеверПродавец 2″;»ЮгПродавец 1″;»ЮгПродавец 2»}; 0)
Поскольку « СеверПродавец 2» является вторым элементом в массиве, функция возвращает 2.
Шаг 3.
После этого наша длинная двумерная формула ИНДЕКС ПОИСКПОЗ превращается в такую простую:
=ИНДЕКС(B3:E5; 2; 2)
Шаг 4.
Она возвращает значение на пересечении 2-й строки и 2-го столбца в диапазоне B3:E5, что является значением в ячейке C4.
Вот как формула ИНДЕКС ПОИСКПОЗ помогает выполнить поиск по нескольким условиям в Excel. Я благодарю вас за чтение и надеюсь вновь увидеть вас в нашем блоге.
Еще несколько статей по теме:
Функция СЧЁТЕСЛИ() в MS Excel — Подсчет значений с единственным критерием
Смотрите также только если оба была добавлена функция ячейки, которые мыКОДСИМВ(«андрей»)*(A1:A10=»андрей»)*(B$1:B$10) одной формуле - Начиная с 2007 неправильность моей формулы будет отобрано 7 строк предварительного создания таблицы но позволяет понять тут. по заполненным строкам,Указали диапазон столбца В.
(с помощью функции
критерия указать ссылкуПодсчитывает количество ячеек, содержащих
Для подсчета ЧИСЛОвых значений, условия выполняются. ТеперьСУММЕСЛИМН (SUMIFS) проверяем на выполнение)
ни в жизнь есть функция СЧЁТЕСЛИМН, :-) (см. строку состояния с условиями. Заголовки работу функции СУММПРОИЗВ(),В Excel можно не считает пустые «Критерий» — поставили РАНГ()). Этот подход на целый диапазон числа равных или Дат и Текстовых
Подсчет числовых значений с одним критерием
стоимости продаж осталось- в нейКритерия(т.е. сравнение (кода
|
бы не додумалась. |
которая позволяет задать |
Марина |
в нижней части |
|
этой таблицы должны |
которая может быть |
из данных разных |
ячейки. Формат ячейки «>300» — это применен в статьях Динамическая ячеек с критериями, |
|
меньших 10. Критерий |
значений, удовлетворяющих определенному |
умножить на значения |
количество условий проверки. В нашем случае первого символа ячейки) Спасибо |
|
несколько критериев |
полезна для подсчета |
ячеек составить предложение. |
ставим «процентный». значит, посчитать все сортировка таблицы в то функция вернет указан через ссылку |
критерию, существует простая получившегося столбца и увеличено аж до — это диапазон с (Для полноты картиныAndrey770 вариантов, но когдаПримечание с заголовками исходной с множественными критериями Эта функция удобна
Подсчет Текстовых значений с одним критерием
Если нужно, чтобы ячейки в столбце MS EXCEL и Отбор уникальных массив. В файле>=
Подсчет дат с одним критерием
и эффективная функция просуммировать отобранное в 127! Функция находится с фамилиями менеджеровпроизведением ещё один способ: 2003 excel применяю к таблице: подсчет значений с
таблицы. Размещение условий в других случаях. для заполнения бланков, расчет производился с В, где цифры значений с сортировкой
Подсчет с несколькими условиями
примера формула =СЧЁТЕСЛИ(A16:A25;C16:C18)(ячейка С4) СЧЁТЕСЛИ(), английская версия зеленой ячейке: в той же продаж.кода первого символа (только для первойAndrey770 исправленную формулу или множественными критерями также в одной строкеРазберем подробнее применение функции
документов, т.д. Подробнее
учетом пустых ячеек, больше 300. в MS EXCEL. возвращает массив {3:2:5}.11 COUNTIF(). Подсчитаем значенияЕсли вы раньше не категорииКритерий искомой строки и
буквы слов):: А если так СУММПРОИЗВ и т.д.
Специальные случаи использования функции
рассмотрен в статьях соответствует Условию И. СУММПРОИЗВ(): об этой возможности
формула будет такая.Получилось такая формула.В Excel функция «СЧЕТЕСЛИ» (ячейка С5) в диапазоне в
сталкивались с такойМатематические- это то, преобразованных результатов логическихКод =СУММПРОИЗВ(—(КОДСИМВ(A1:A10)=КОДСИМВ(«андрей»)*(A1:A10=»андрей»)*(B$1:B$10curious dare=СУММПРОИЗВ((C3:C31=»андрей»)*(I3:I31 ничего не получается, Подсчет значений сЗдесь есть один трюк:Результатом вычисления A2:A13=D2 является читайте в статье=СЧЁТЕСЛИ(B15:B19;»да»)/ЧСТРОК(B15:B19)Формула посчитала таксчитает данные ячеек,
Для ввода формулы выделите=СЧЁТЕСЛИ($A$2:$A$12; C4&C5) случае одного критерия, замечательной возможностью Excelи работает похожим что мы ищем операций)) I ask: andSerge 007 пишет — о множественными критериями (Часть в качестве второго массив {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ} Значение «Функция «Сцепить» вФункцией «СЧЁТЕСЛИ» в Excel
excel2.ru
Функция «СЧЁТЕСЛИ» в Excel.
— в двух только, когда выполнятся диапазон ячеек такого3 а также покажем как формулы массива, образом, но имеет в предыдущем указанномвариант немного странный
what are the: К сожалению не :-((( 2. Условие ИЛИ), аргумента функции БСЧЁТА() ИСТИНА соответствует персики. Excel». можно одновременно подсчитать
ячейках стоят цифры условия, которые мы же размера какПодсчитывает количество ячеек, содержащих как ее использовать то советую почитать больше аргументов: диапазоне. Разрешается использовать (на мой взгляд), minuses for?see answer
работаетне могу понять Часть3, Часть4.
(поле) нужно ввести
Результат можно увидеть,Произведем подсчет строк, удовлетворяющих
количество ячеек по больше 300 (330, написали в формуле. и диапазон содержащий числа равных или
для подсчета неповторяющихся предварительно про нихПри помощи полосы прокрутки символы * (звездочка) но в таком See thisBuckminster в чем проблема…
Марина ссылку на заголовок выделив A2:A13=D2, а сразу двум критериям, условию и узнать 350). Рассмотрим,
критерии. В Строке
более 11. Критерий значений и вычисления много хорошего здесь. в правой части и ? (вопросительный
случае дополнительное преобразованиеНе по теме:
: Посмотрите файл, уМарина: Подскажите в чем столбца с текстовыми затем нажав клавишу которые образуют Условие их сумму, произведение,Другие примерыкак посчитать количество ячеек формул введите формулу указан через ссылку
ранга. Ну, а в окна можно задать знак) как маски типов, действительно, необходимо…да, но
меня работает, или: Отзовитесь,пожалуйста, хоть кто-нибудь… ошибка, может вообще значениями, т.к. БСЧЁТА()F9 И. т.д.применения функции «СЧЁТЕСЛИ». в Excel и нажмите и параметрСинтаксис
нашем случае задача и третью пару или символы подстановки.p.s.anyway thank you свой выкладывайтеSh_Alex неправильная формула:-): =СЧЁТ(ЕСЛИ($C$4:$C$16=»да»;ЕСЛИ($B$4:$B$16=$G4;0);0)) подсчитывает текстовые значения.;В качестве исходной таблицыНапример, в ячейкахВ некоторых примерах в
.CTRL+SHIFT+ENTERПримечаниеСЧЁТЕСЛИдиапазонкритерий
решается одной формулой:
( Звездочка подменяет собойв любом случае for the informationпояснения…ЗЫ У меня: Отозвался, но свою
Задача: посчитать по В случае использования БСЧЁТ() нужноДвойное отрицание (—) преобразует возьмем таблицу с B1:Е1 стоят числа формулах стоит знакНапример, нужно посчитать, т.е. введите ее. О подсчете значений,)=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)Диапазон_условия3 любое количество любых
присоединяюсь к благодарности действительно, то, что андрей с маленькой
задачу решать придется
двум критериям. записать другую формулу вышеуказанный массив в двумя столбцами: текстовым — 4 и «звездочка». Это подстановочный ячейки, которые содержат как формулу массива.
удовлетворяющих нескольким критериямДиапазонПосле ввода этой формулы- символов, вопросительный знакPelena
я вначале воспринял буквы, а у Вам самой. ЯСпасибо =БСЧЁТ(A1:B13;B1;D14:E15). Табличка с числовой: {0:0:0:0:1:1:1:0:0:0:0:0} «Фрукты» и числовым 7. Нам нужно
знак. Ддя чего определеную цифру, слово,Это свойство функции СЧЁТЕСЛИ() читайте в статье — диапазон, в котором необходимо нажать неУсловие3 — один любойза привлечение внимания как более очевидное
excel-office.ru
Подсчет значений с множественными критериями (Часть 1. Условие И) в MS EXCEL
Вас? только все наличиеGuest критериями не изменится.
Аналогично, второй массив возвращает «Количество на складе» посчитать все ячейки эти подстановочные знаки, посчитать результат голосования используется в статье
Задача1
Подсчет значений со нужно подсчитать ячейки, Enter, как обычно,
), и четвертую, и символ. Так, например, к нестандартным методам (на мой взгляд)Serge 007 тавара разложил по: Темы с одноименнымРассмотрим задачу, когда критерии {0:1:1:1:0:1:1:0:0:1:1:1}, где 0 (См. файл примера). с числом «4» какие ещё есть или ответов в Отбор уникальных значений. множественными критериями. О содержащие числа, текст а т.д. — при чтобы найти все решения задач…=СУММПРОИЗВ: Спасибо огромное, выручили) полочкам. Поймете как
названием и смыслом применяются к значениям
соответствует значениям B2)Рассмотрим задачу, когда критерии и умножить их
знаки, где их анкете, опросе, провестиВозможность задать в качестве подсчете чисел с или даты.Ctrl + Shift + необходимости.
продажи у менеджеровИмеем таблицу по продажам,
- (Все разобрался, оказалось работает формула, и появляются на форуме из одного столбца. =3, которое меньше применяются к значениям на 2 (
- еще можно использовать, другой анализ данных, критерия несколько значений
- более чем 15Критерий EnterЕсли же у вас с фамилией из например, следующего вида:— я неверно записал ваша проблема, а еженедельно и дажеНайдем число партий товара 5 (не удовлетворяет из разных столбцов. например, на 2
- читайте в статье т.д. открывает дополнительные возможности значащих цифр читайте
— критерий в форме- тогда Excel пока еще старая пяти букв, можноЗадача
( массив также все сопутствующие, чаще. Пользуйтесь поиском с Количеством на складе критерию), поэтому первоеНайдем число партий товара часа). А ячейки
«Подстановочные знаки вЭта функция нужна использования функции СЧЁТЕСЛИ().
статью Подсчет ТЕКСТовых значений числа, выражения, ссылки воспримет ее как версия Excel 2003, использовать критерий: просуммировать все заказы,КОДСИМВ(A1:A10)=КОДСИМВ(«андрей»)Спасибо ещё раз!!!! будет решена.
по форуму. не менее минимального и значение в массиве {0:1:1:1:0:1:1:0:0:1:1:1} с определенным Фруктом с числом «7» Excel». и для составленияВ файле примера на с единственным критерием на ячейку или формулу массива и но задачу с
Задача2
????? которые менеджер Григорьев реализовал)
BuckminsterС уважением, Александр.r_kot не более максимального =0. Второе значение (ячейкаИ умножить на 6Применить функцию «СЧЁТЕСЛИ» больших формул с
листе Специальное применение в MS EXCEL. текста, который определяет, сам добавит фигурные несколькими условиями решить. А чтобы найти все
для магазина «Копейка».
Альтернативное решение
* (A1:A10=»андрей») *: Елена, это неМарина: Поправил формулу. Вместо (Условие И -
B3с Количеством на (на 6 часов). в Excel можно, многими условиями. Как показано как с
Функция СЧЁТЕСЛИ() также годится для подсчета
какие ячейки надо скобки. Вводить скобки нужно, то придется продажи менеджеров, уЕсли бы в нашей (B$1:B$10) играет роли
: Александр, спасибо :-) enter жми ctrl+shift+enter строка таблицы соответствует) =5, которое удовлетворяет складе не менееВ ячейке F1 например, для подсчета написать сложную формулу
excel2.ru
СЧЕТЕСЛИ по двум критериям???
помощью функции СЧЁТЕСЛИ() вычислить количество текстовых значений (см. подсчитывать. Например, критерий с клавиатуры не
извращаться — см. которых фамилия начинается
задаче было только
(т.е. вначале проверкаТекстовые строки сравниваютсяТак и не и наступит результат! критерию, когда ее критерию >=5, поэтому минимального (Условие И
пишем такую формулу. результатов голосования или с многими вложенными повторов каждого значения
Подсчет ТЕКСТовых значений может быть выражен надо. Легко сообразить, следующие способы.
на букву «П»,
одно условие (все первого символа, потом по значению, без
разобралась в чем
r_kot
поле удовлетворяет обоим второе значение в — условие при
=СЧЁТЕСЛИ(B1:E1;»4″)*2+СЧЁТЕСЛИ(B1:E1;»7″)*6 ответов в анкете, функциями, смотрите в в списке. с единственным критерием следующим образом: 32, что этот способ
Добавим к нашей таблице а заканчивается на
заказы Петрова или проверка строки без учёта регистра тогда была проблема,
: Поправил формулу. Вместо критериям одновременно). массиве =1 и котором строка считаетсяПолучилось 16 часов. других опросов. статье «Как составлятьВыражение СЧЁТЕСЛИ(A6:A14;A6:A14) возвращает массив чисел {1:4:4:4:4:1:3:3:3}, в MS EXCEL).
«32», «>32», «яблоки» (как и предыдущий)
еще один столбец, «В» — критерий
все заказы в учёта регистра, иSerge 007 но когда заново enter жми ctrl+shift+enterРешение стоится аналогично предыдущей т.д. удовлетворяющей критерию, когдаКак посчитать количествоЕсть таблица с формулы в Excel
который говорит о
Так как любой дате или
легко масштабируется на который будет служитьП*В «Копейку», например), то затем проверка рейтинга): да, это стандартный
planetaexcel.ru
СчётЕсли по 2 диапазонам
переписала формулу - и наступит результат!{/post}{/quote} задачи. Например, с
Далее, функция попарно перемножает оба ее поля не пустых ячеек,
данными опроса (А15:А19). для начинающих».
том, что значение
в MS EXCEL
B4 три, четыре и
своеобразным индикатором: если. Строчные и прописные задача решалась быи что можно workaround от Microsoft
она заработала :-)МОЖНО И ТАК!!! использованием функции СЧЁТЕСЛИМН() формула элементы массивов и одновременно соответствуют критериям). с использованием функции
Нам нужно посчитатьЧтобы понять эту
1 из списка соответствует определенное числовое
.
т.д. условий без заказ был в буквы не различаются.
достаточно легко при было записать без для Excel 2003:Andrey770
vikttur выглядит так (см. суммирует их. Получаем Например, число партий
«СЧЁТЕСЛИМН», смотрите в голоса «да». В
функцию, рассмотрим несколько в диапазоне значение, то настройка
Данные будем брать из
каких-либо ограничений. «Копейку» и отДиапазон_суммирования
помощи встроенной функции преобразования типов (т.к. Подсчет чисел или
: Нужно посчитать количество: Или популярная на лист один столбец – 2. персики (ячейка статье «Как посчитать ячейку В20 пишем
примеров.А6:А15 функции СЧЕТЕСЛИ() для
диапазонаВ категории
Григорьева, то в
- это те Excel
умножение его уже дат на основе по 2 диапазонам форуме без массива: в файле примера):3. Другим вариантом использованияD2 количество заполненных ячеек формулу (смотрите строкуПервый пример.- единственное, также
дат не отличаетсяA15:A25Базы данных (Database) ячейке этого столбца
ячейки, значения которыхСУММЕСЛИ (SUMIF) выполняет): проверки условияВ 1 диапазоне
=СУММПРОИЗВ(($C$4:$C$16=»да»)*($B$4:$B$16=$G4))
=СЧЁТЕСЛИМН(B2:B13;»>=»&D2;B2:B13;»
функции СУММПРОИЗВ() является) с количеством ящиков в Excel выборочно» 20 на изображенииУ нас такая в диапазоне 4
от рассмотренного выше(см. файл примера). можно найти функцию будет значение 1, мы хотим сложить, из категории=СУММПРОИЗВBuckminster
все с именем=СУММПРОИЗВ(($C$4:$C$16=»нет»)*($B$4:$B$16=$G4))Подсчитать количество строк, удовлетворяющим формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2)). Здесь, на складе >=5
тут. ниже). Эта формула таблица. значения 2, одно примера (см. файл
КритерийБДСУММ (DSUM) иначе — 0. т.е. нашем случае -Математические (Math&Trig)(
: А можно учесть АндрейМарина
2-м критериям (Условие знак Умножения (*) (ячейкаЕще один пример считает ячейки, вПосчитаем количество ячеек с значение 3, три примера Лист Даты).
Формула, которая тоже способна Формула, которую надо стоимости заказов.. Выделяем пустую ячейкуКОДСИМВ(A1:A10)=КОДСИМВ(«андрей») регистр?В 2 -
: Всем большое спасибо И) можно без эквивалентен Условию И.Е2 использования функции «СЧЕТЕСЛИ»
которых написано «да». числами больше 300 значения 4. ЭтоЕсли необходимо подсчитать количествоРезультат решить нашу задачу. ввести в этот
CyberForum.ru
Выборочные вычисления по одному или нескольким критериям
Постановка задачи
Если условий больше одного для результата, жмем
)Serge 007 >17 :-)
Способ 1. Функция СУММЕСЛИ, когда одно условие
применения формул с4. Формула массива =СУММ((A2:A13=D2)*(B2:B13>=E2))). Результат очевиден: 2. смотрите в статье Получилось три слова в столбце B. позволяет подсчитать количество дат, принадлежащих определенномуПримечание Нюанс состоит в столбец очень простая: (например, нужно найти кнопку* (A1:A10=»андрей») *: Конечно1Марина помощью стандартного Автофильтра. эквивалентна вышеупомянутой формуле Для наглядности, строки «Как объединить данные
«да». В ячейке В7 неповторяющихся значений формулой
- месяцу, то нужно>=10 том, что для=(A2=»Копейка»)*(B2=»Григорьев») сумму всех заказовfx (B$1:B$10)Код =СУММПРОИЗВ(ЕЧИСЛО(НАЙТИ(«андрей»;A1:A10))*(B$1:B$10Код =СЧЁТЕСЛИ(Лист2!C3:C31;»Андрtй») 2
- : Темы с одноименнымУстановите автофильтр к столбцу =СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2)) Единственное, после в таблице, удовлетворяющие теста в ячейкахЕсли нужно посчитать пишем формулу. =СУММПРОИЗВ(—(СЧЁТЕСЛИ(A6:A14;A6:A14)=1)). создать дополнительный столбец=СЧЁТЕСЛИ($A$2:$A$12; «>=10») работы этой функцииЛогические равенства в скобках Григорьева для «Копейки»),в строке формул,на самом делеBuckminsterКод =СЧЁТЕСЛИ(Лист2!I3:I31;» а названием и смыслом Количество ящиков на ее ввода нужно критериям, выделяются Условным в Excel» здесь. процентное отношение ответовНа закладке «Формулы»Формула =СЧЁТЕСЛИ(A6:A14;»А6:А15. В этом можно для вычисления месяца,4 необходимо создать на дают значения ИСТИНА
- то функция находим функцию было (дополнительные зелёные: можно стандартной функцией: как их объеденить?? появляются на форуме
Способ 2. Функция СУММЕСЛИМН, когда условий много
складе, выделив заголовок вместо форматированием с правилом =И($A2=$D$2;$B2>=$E$2)В Excel много «да» по отношению в разделе «Библиотека убедиться, выделив формулу затем записать формулу =СЧЁТЕСЛИ(B20:B30;2)Подсчитывает количество ячеек, содержащих листе специальный диапазон или ЛОЖЬ, чтоСУММЕСЛИ (SUMIF)СУММЕСЛИ скобки мои):Buckminsterмне нужно посчитать еженедельно и даже столбца и нажавENTERПодсчет можно реализовать множеством разных способов провести ко всем ответам, функций» нажимаем кнопку в Строке формул
Обычно, в качестве аргумента числа равных или критериев — ячейки, содержащие для Excel равносильноне поможет, т.к.в списке:=СУММПРОИЗВ: Сергей, да Вы всех Андреев и чаще. Пользуйтесь поискомCTRL+SHIFT+L.
нажать формул, приведем несколько: анализ данных в то формула будет «Другие функции» и, и нажав клавишукритерий более 10. Критерий
Способ 3. Столбец-индикатор
условия отбора — и 1 и 0. не умеет проверятьЖмем( просто волшебник. Я +, что бы по форуму.{/post}{/quote}Выберите числовой фильтр Между.CTRL+SHIFT+ENTER1. =СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;»>=»&E2) Это решение является таблице. О разных сложнее. Смотрите формулу
в разделе «Статистические»,
F9. Значения совпадуту функции СЧЁТЕСЛИ() указан в формуле указать затем этот Таким образом, поскольку больше одного критерия.ОК— вроде все эти у них былСпасибо, обязательно воспользуюсьВведите критерии5. Формула массива =СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет самым простым и способах анализа, смотрите в строке 22
Способ 4. Волшебная формула массива
выбираем функцию «СЧЁТЕСЛИ». с вычисленным рангом указывают только одно(ячейка С3) диапазон функции как мы перемножаем эти Поэтому начиная с и вводим ее аргументы:( функции знаю, но
рейтинг меньше 17
:-)Убедитесь, что результат такой еще один вариант понятным. в статье «Анализ на изображении. Здесь Заполняем диалоговое окно в столбце значение. Например, =СЧЁТЕСЛИ(H2:H11;I2).=СЧЁТЕСЛИ( $A$2:$A$12;C3) аргумент: выражения, единица в версии Excel 2007ДиапазонКОДСИМВ(A1:A10) = чтобы вот такAndrey770Мне просто было же как в
Способ 4. Функция баз данных БДСУММ
многокритериального подсчета значений.2. =СУММПРОИЗВ(—(A2:A13=D2);—(B2:B13>=E2)) Это решение сложнее, данных в Excel» идет расчет только так.В Если в качестве8=БДСУММ(A1:D26;D1;F1:G2) конечном счете получится в набор функций- это те( их использовать в: А офис какой? интересно, в чем
задаче2 — т.е.
planetaexcel.ru
6. Формула =БСЧЁТА(A1:B13;A1;D14:E15) требует
Содержание
- Описание синтаксиса функции
- Вариант 1: Счет текстовых условий
- Вариант 2: Счет числовых условий
- Вопросы и ответы
Функция «СЧЁТЕСЛИ», часто использующаяся в Excel, относится к критериям. Если у вас еще нет базовых понятий о функциях, относящихся к этой группе, ознакомьтесь с общим их описанием в инструкции по ссылке ниже.
Читайте также: Использование критериев в Microsoft Excel
Описание синтаксиса функции
Начать следует с описания синтаксиса самой функции «СЧЁТЕСЛИ», поскольку при работе с несколькими условиями понадобится создавать большую формулу, учитывая все особенности.
- Для простоты понимания структуры предлагаем объявить в поле
=СЧЁТЕСЛИ()и сразу перейти к меню «Аргументы функции». - В нем вы увидите строки «Диапазон» и «Критерий». Нажмите по первой для ее активации.
- В качестве диапазона указывается выборка данных, которая и должна попадать в функцию. Проще всего ее указать путем выделения ячеек, не закрывая при этом текущее окно.
- Далее остается только добавить критерий во второе поле — то есть происходит указание самого условия. Не забывайте, что числа вписываются просто так, а текст берется в двойные кавычки.
Внизу под полями виден результат, что уже свидетельствует о правильном составлении функции. Сейчас добавить еще одно условие нельзя, поэтому формулу придется расширять, о чем и пойдет речь в следующих двух вариантах.
Вариант 1: Счет текстовых условий
Разберем ситуацию, когда есть два столбца с определенными значениями, которыми в нашем случае выступают месяцы. Нужно сделать выборку из них, чтобы в результате показывало значение того, сколько ячеек соответствуют заданному условию. Объединяются два условия при помощи одной простой формулы.
- Создайте первую часть функции «СЧЁТЕСЛИ», указав в качестве диапазона первый столбец. Сама функция имеет стандартный вид:
=СЧЁТЕСЛИ(A2:A25;"Критерий"). - Добавьте знак сложения («+»), который и послужит соединительным для двух условий.
- Сразу же после него впишите вторую часть —
=СЧЁТЕСЛИ(B2:B25;"Критерий")— и нажмите клавишу ввода. - Если нужно, дописывайте любое количество плюсов, добавляя диапазоны и критерии точно в таком же ключе.
Проверьте результат, который отобразится в заданной клетке. Если вдруг возникла ошибка, удостоверьтесь в том, что вы правильно соблюли синтаксис функции, а ячейки в диапазоне имеют соответствующий формат.
Вариант 2: Счет числовых условий
С числовыми условиями дела обстоят точно так же, но на этот раз давайте рассмотрим более детальный пример ручного составления функции, учитывая каждую деталь.
- После объявления «СЧЁТЕСЛИ» в круглых скобках задайте диапазон чисел «A1:A25», где вместо указанных ячеек подставьте необходимые.
- Сразу за диапазоном без пробела добавьте знак «;», который объявляет его закрытие.
- После него просто напишите число, которое хотите посчитать.
- Поставьте знак «+» и допишите вторую часть формулы точно так же.
- Как только вы нажмете клавишу Enter, в ячейке отобразится число, которое и будет результатом математического действия с двумя указанными критериями.
Еще статьи по данной теме: