Завантажте зразок робочої книги
У цьому посібнику буде показано, як підсумовувати дані, що відповідають конкретним номерам тижнів у Excel та Google Таблицях.
Сума Якщо за номером тижня
Щоб "підсумувати якщо" за номером тижня, ми будемо використовувати функцію SUMIFS. Але спочатку нам потрібно додати допоміжний стовпець, що містить функцію WEEKNUM.
The Номер тижня допоміжний стовпець обчислюється за допомогою функції WEEKNUM:
| 1 | = WEEKNUM (B3,1) |
Далі ми будемо використовувати функцію SUMIFS для підсумовування всіх Продаж які мають місце у певному Номер тижня.
| 1 | = SUMIFS (D3: D9, C3: C9, F3) |
Сума, якщо за тиждень - без стовпця помічника
Метод допоміжного стовпця простий у використанні, але ви також можете повторити обчислення в одній формулі, використовуючи функцію SUMPRODUCT у поєднанні з функцією WEEKNUM, щоб підсумувати Загальна кількість продажів автор: Номер тижня.
| 1 | = ПІДПРИЄМСТВО (-(ТИЖНЯ (B3: B9+0,1) = E3), C3: C9) |
У цьому прикладі ми можемо використовувати функцію SUMPRODUCT для виконання складних обчислень "сума якщо". Пройдемося по наведеному вище прикладу.
Ось наша остаточна формула:
| 1 | = ПІДПРИЄМСТВО (-(ТИЖНЯ (B3: B9+0,1) = E3), C3: C9) |
По -перше, функція SUMPRODUCT перелічує масив значень із діапазонів комірок:
| 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, змінюються на значення TRUE.
| 1 | = SUMPRODUCT (-({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}), {4; 9; 1; 7; 6; 2; 5}) |
Далі подвійні тире (-) перетворюють значення TRUE і FALSE в 1s і 0s:
| 1 | = ПІДПРИЄМСТВО ({1; 0; 0; 0; 0; 0; 0}, {4; 9; 1; 7; 6; 2; 5}) |
Потім функція SUMPRODUCT перемножує кожну пару записів у масивах для створення масиву Кількість продажів що мають а Номер тижня з 1:
| 1 | = ПІДПРИЄМСТВО ({4; 0; 0; 0; 0; 0; 0}) |
Нарешті, числа в масиві підсумовуються разом:
| 1 | =4 |
Потім цю формулу повторюють для інших можливих значень Номер тижня.
Детальніше про використання булевих операторів та команди “-” у функції SUMPRODUCT можна знайти тут.
Блокування посилань на клітинки
Щоб полегшити читання наших формул, ми показали формули без заблокованих посилань на клітинки:
| 1 | = ПІДПРИЄМСТВО (-(ТИЖНЯ (B3: B9+0,1) = E3), C3: C9) |
Але ці формули не працюватимуть належним чином при копіюванні та вставці в інше місце у файлі. Замість цього, ви повинні використовувати заблоковані посилання на клітинки, як це:
| 1 | = SUMPRODUCT (-(WEEKNUM ($ B $ 3: $ B $ 9+0,1) = E3), $ C $ 3: $ C $ 9) |
Прочитайте нашу статтю про блокування посилань на клітинки, щоб дізнатися більше.
Сума Якщо за тиждень у Таблицях Google
Ці формули працюють точно так само в Google Таблицях, як і в Excel.
Однак функція WEEKNUM є більш гнучкою у Таблицях Google, ніж у Excel, і приймає вхідні та вихідні дані масиву. Тому операція {Array} +0 у формулі WEEKNUM (B3: B9+0,1) не потрібна.
Повну формулу SUMPRODUCT можна записати в Google Таблицях так:
| 1 | =ВИХОДИ(--(WEEKNUM($ B $ 3: $ B $ 9+0,1)=E3),$ C $ 3: $ C $ 9) |
