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