Использование SUMPRODUCT граф с многократным или критериями
Как я уже упоминал во многих своих блогах, СУММПРОИЗВ — это очень универсальная функция, которую можно использовать для различных целей. В этой статье мы увидим, как мы можем использовать эту функцию для подсчета значений с несколькими критериями ИЛИ.
Общая ссылка: / summing-excel-sumproduct-function [SUMPRODUCT]
Формула для подсчета с несколькими критериями или
|
= |
Criteria1: это любые критерии, которые возвращают массив значений TRUE и FALSE.
Criteria2: это следующий критерий, который вы хотите проверить.
Точно так же вы можете иметь столько критериев, сколько захотите.
Приведенная выше общая формула часто модифицируется для соответствия требованиям подсчета с использованием нескольких критериев ИЛИ. Но основная формула такова. Сначала мы посмотрим, как это работает, на примере, а затем обсудим другие сценарии, в которых вам нужно будет немного изменить эту формулу.
Пример: подсчет пользователей, если код дилера или год совпадает с использованием SUMPRODUCT
Итак, у нас есть набор данных о продавцах. Данные содержат много столбцов. Что нам нужно сделать, так это подсчитать количество пользователей, у которых есть код «INKA» или год «2016». Убедитесь, что если у кого-то есть и то, и другое (код «инка» и 2016 год), он будет засчитан как 1.
Итак, здесь у нас есть два критерия. Мы используем указанную выше формулу СУММПРОИЗВ:
|
= |
Здесь код и год — это именованные диапазоны.
Это возвращает 7. В данных у нас есть 5 записей кода INKA и 4 записи за 2016 год.
Но 2 записи имеют как «ИНКА», так и 2016 как код и год соответственно.
Эти записи считаются как 1. И вот как мы получаем 7.
Как это работает?
Итак, давайте посмотрим, как формула решается шаг за шагом, а затем я расскажу, как она работает.
|
= |
|
1⇒`SUMPRODUCT*`(—\{TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;…}+\{FALSE;FALSE;FALSE;TRUE;TRUE;…})>0 |
|
2⇒`SUMPRODUCT*`(—\{1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0 |
|
3⇒`SUMPRODUCT*`(—(\{TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;…}) |
|
4⇒`SUMPRODUCT*`(\{1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
|
5⇒7 |
На первом этапе значение I3 («INKA») сравнивается с каждой ячейкой в кодовом диапазоне. Это возвращает массив ИСТИНА и ЛОЖЬ. ИСТИНА для каждого матча. Для экономии места я не показал все ИСТИНА-ЛОЖЬ. Точно так же значение K3 (2016) сопоставляется с каждой ячейкой в диапазоне лет.
На следующем шаге мы добавляем эти два массива, в результате чего получается новый массив числовых значений. Как вы, возможно, знаете, TRUE обрабатывается как 1, а FALSE как 0 в Excel. Итак, когда добавляются ИСТИНА и ИСТИНА, мы получаем 2, а остальное вы можете понять.
На следующем этапе мы проверяем, какое значение в массиве больше 0.
Это снова преобразует массив в истинный ложный массив. Для каждого получаемого значения 0 False и rest преобразуются как true. Теперь наш ответ — количество ИСТИННЫХ значений в массиве. Но как их считать? Вот как.
link: / tips-the-double-negatives-in-excel [Двойные отрицательные (-) знаки] используются для преобразования логических значений в единицы и нули.
Таким образом, каждое ИСТИННОЕ значение в массиве преобразуется в 1, а ЛОЖЬ в 0. На последнем этапе СУММПРОИЗВ суммирует этот массив, и мы получаем наш ответ как 7.
Добавление дополнительных критериев или критериев для подсчета с помощью SUMPRODUCT Итак, если вам нужно добавить дополнительные критерии для подсчета, вы можете просто добавить критерии, используя знак + к функции. Например, если вы хотите добавить еще один критерий к приведенной выше формуле, чтобы она добавляла количество сотрудников, которые продали более 5 продуктов.
Формула СУММПРОИЗВ будет выглядеть просто так:
|
= |
Просто! не так ли?
Но предположим, что вы хотите иметь два критерия из диапазона Code *. Допустим, вы хотите посчитать «INKB». Так как же это сделать? Один из методов использует описанный выше метод, но он будет повторяться. Допустим, я хочу добавить еще 10 критериев из того же диапазона. В таких случаях этот метод не подходит для подсчета с помощью SUMPRODUCT.
Допустим, у нас есть данные, расположенные вот так.
Коды критериев находятся в одной строке I2: J2. Здесь важна организация данных. Формула СУММПРОИЗВ для трех параметров подсчета ИЛИ будет:
|
= |
_Это формула СУММПРОИЗВ для подсчета по нескольким критериям, когда несколько критериев из одного диапазона записаны в строке. _ Это возвращает правильный ответ, который равен 10.
Если вы введете любой год в J3, формула также добавит это количество.
Это используется, когда критерии находятся в одной строке. Будет ли это работать, если критерии находятся в одном столбце для одного и того же диапазона? Нет, не будет.
В этом примере у нас есть несколько кодов для подсчета, но эти коды типов записаны в один столбец. Когда мы используем приведенную выше формулу СУММПРОИЗВ, мы получаем ошибку ans # N / A. Мы не будем подробно рассказывать, как возникла эта ошибка, так как это сделает статью слишком длинной. Посмотрим, как мы можем заставить это работать.
Чтобы эта формула работала, вам нужно заключить критерии кода в link: / Mathematical-functions-excel-transpose-function [TRANSPOSE function]. Это заставит формулу работать.
|
= |
Это формула для подсчета с несколькими условиями в одном диапазоне, когда критерии указаны в столбце.
Так что, приятель, я надеюсь, что я был достаточно ясен, и это имело смысл. Надеюсь, это послужит вашей цели пребывания здесь. Если эта формула не решила вашу проблему, сообщите мне о ваших требованиях в разделе комментариев ниже. Я буду более чем счастлив помочь вам любым способом. Вы можете упомянуть любые сомнения, связанные с Excel / VBA. А пока продолжайте учиться, продолжайте преуспевать.
Похожие статьи
link: / summing-excel-sumproduct-function [Как использовать функцию СУММПРОИЗВ в Excel]: возвращает СУММ после умножения значений в нескольких массивах в Excel. Эту функцию можно использовать для выполнения нескольких задач. Это одна из самых универсальных функций.
link: / counting-countifs-with-dynamic-conditions-range [COUNTIFS with Dynamic Criteria Range]: Для подсчета с динамическим диапазоном критериев мы просто используем функцию INDIRECT. Эта функция может связать: / counting-countifs-with-or-for-множественные критерии [COUNTIFS с OR для нескольких критериев]: Подсчет ячеек, которые имеют несколько критериев, совпадающих с использованием функции OR. Чтобы добавить логику ИЛИ в функцию СЧЁТЕСЛИМН, вам не нужно использовать функцию ИЛИ.
link: / tips-using-the-if-and-or-functions-together-in-microsoft-excel-2010-2013 [Использование IF с функциями И / ИЛИ в Microsoft Excel]: Эти логические функции используются для выполнять вычисления по нескольким критериям. С IF функции OR и AND используются для включения или исключения совпадений.
link: / logic-excel-or-function [Как использовать функцию ИЛИ в Microsoft Excel]: функция используется для включения всех ИСТИННЫХ значений в несколько критериев. link: / logic-formulas-count-cells-that-contain-this-or-that [Как подсчитать ячейки, которые содержат то или другое в Excel в Excel]: Для ячеек, содержащих то или иное, мы можем Функция СУММПРОИЗВ. Вот как вы делаете эти расчеты.
Популярные статьи:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-вашу-продуктивность [50 комбинаций клавиш Excel для повышения вашей продуктивности] | Выполняйте свою задачу быстрее. Эти 50 ярлыков заставят вас работать в Excel еще быстрее.
link: / формулы-и-функции-введение-функции-vlookup [Как использовать функцию Excel VLOOKUP] | Это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листов. link: / tips-countif-in-microsoft-excel [Как использовать]
link: / введение-формулы-и-функции-функции-vlookup [Excel]
link: / tips-countif-in-microsoft-excel [Функция СЧЁТЕСЛИ] | Подсчитайте значения с условиями, используя эту удивительную функцию.
Вам не нужно фильтровать данные для подсчета определенных значений. Функция Countif важна для подготовки вашей приборной панели.
link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel] | Это еще одна важная функция приборной панели. Это поможет вам суммировать значения для конкретных условий.