Загрузите образец книги
В этом руководстве будет показано, как использовать функцию СУММЕСЛИМН для суммирования данных, соответствующих определенным дням недели в Excel и Google Таблицах.
Сумма, если по дням недели - текстовые значения
Сначала мы продемонстрируем, как суммировать данные, соответствующие конкретным Названия дней недели.
Мы можем использовать функцию СУММЕСЛИМН вместе со вспомогательным столбцом, содержащим функцию ТЕКСТ, чтобы суммировать все Продажи которые происходят на конкретном День недели:
| 1 | = СУММЕСЛИМН (D3: D9; C3: C9; F3) |
В приведенном выше примере мы используем функцию ТЕКСТ для извлечения имени День недели от Дата продажи:
| 1 | = ТЕКСТ (B3; «дддд») |
Затем функция СУММЕСЛИМН использует этот вспомогательный столбец для создания сводной таблицы:
| 1 | = СУММЕСЛИМН (D3: D9; C3: C9; F3) |
Сумма по дням недели - с использованием WEEKDAY
В качестве альтернативы можно использовать номера дней недели. Для этого вспомогательный столбец в исходных данных должен использовать функцию WEEKDAY:
| 1 | = ДЕНЬ НЕДЕЛИ (B3) |
Использование числового значения WEEKDAY вместо названия дня недели может предоставить вам более гибкие возможности для последующих задач визуализации данных и функций сводки.
Сумма по дням недели - без вспомогательного столбца
В приведенном выше примере данные суммированы по дням недели с использованием вспомогательного столбца. Чтобы избежать необходимости во вспомогательном столбце, мы можем использовать функцию SUMPRODUCT:
| 1 | = СУММПРОИЗВ (- (ДЕНЬ НЕДЕЛИ (B3: B9) = E3), C3: C9) |
Здесь функция СУММПРОИЗВ выполняет сложное вычисление «суммы если». Давайте рассмотрим приведенный выше пример.
Это наша окончательная формула:
| 1 | = СУММПРОИЗВ (- (ДЕНЬ НЕДЕЛИ (B3: B9) = E3), C3: C9) |
Во-первых, функция СУММПРОИЗВ оценивает номер дня недели каждого из Даты продаж а также перечисляет массив значений из Количество продаж столбец:
| 1 | = СУММПРОИЗВ (- ({6; 2; 5; 1; 4; 7; 3} = 1), {4; 9; 1; 7; 6; 2; 5}) |
Используя логический тест (= 1), номера дней недели из 1 меняются на ИСТИНА, все остальные - ЛОЖЬ:
| 1 | = СУММПРОИЗВ (- ({ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}), {4; 9; 1; 7; 6; 2; 5}) |
Затем двойные дефисы (-) преобразуют значения ИСТИНА и ЛОЖЬ в единицы и нули:
| 1 | = СУММПРОИЗВ ({0; 0; 0; 1; 0; 0; 0}, {4; 9; 1; 7; 6; 2; 5}) |
Затем функция СУММПРОИЗВ умножает каждую пару записей в массивах, чтобы получить массив Количество продаж на Даты продаж тот матч День недели 1:
| 1 | = СУММПРОИЗВ ({0; 0; 0; 7; 0; 0; 0}) |
Наконец, суммируются числа в массиве:
| 1 | =7 |
Более подробную информацию об использовании логических операторов и команды «-» в функции SUMPRODUCT можно найти здесь.
Блокировка ссылок на ячейки
Чтобы наши формулы было легче читать, мы показали формулы без заблокированных ссылок на ячейки:
| 1 | = СУММПРОИЗВ (- (ДЕНЬ НЕДЕЛИ (B3: B9) = E3), C3: C9) |
Но эти формулы не будут работать должным образом при копировании и вставке в другое место в файле. Вместо этого вы должны использовать ссылки на заблокированные ячейки следующим образом:
| 1 | = СУММПРОИЗВ (- (ДЕНЬ НЕДЕЛИ ($ B $ 3: $ B $ 9) = E3), $ C $ 3: $ C $ 9) |
Прочтите нашу статью о блокировке ссылок на ячейки, чтобы узнать больше.
Сумма если по дням недели в Google Таблицах
Эти формулы работают в Google Таблицах точно так же, как и в Excel.
