Завантажте зразок робочої книги
Цей підручник покаже, як використовувати функції SUMPRODUCT та SUMIFS для підсумовування даних, що відповідають певним критеріям, на кількох аркушах у Excel та Google Таблицях.
Звичайна сума на кількох аркушах
Іноді ваші дані можуть охоплювати кілька робочих аркушів у файлі Excel. Це звичайно для даних, які збираються періодично. Кожен аркуш у книзі може містити дані за встановлений період часу. Ми хочемо формулу, яка підсумовує дані, що містяться у двох або більше аркушах.
Функція SUM дозволяє легко підсумовувати дані на кількох аркушах за допомогою файлу 3D посилання:
| 1 | = SUM (аркуш1: аркуш2! A1) |
Однак це неможливо з функцією SUMIFS. Замість цього ми повинні використовувати більш складну формулу.
Сума, якщо на кількох аркушах
Цей приклад підсумовує Кількість запланованих поставок для кожного Замовник на кількох робочих аркушах, кожен з яких містить дані, що стосуються різного місяця, за допомогою функцій SUMIFS, SUMPRODUCT та INDIRECT:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Пройдемося по цій формулі.
Крок 1: Створіть формулу SUMIFS лише для 1 аркуша введення:
Ми підсумовуємо функцію SUMIFS Кількість запланованих поставок автор: Замовник для єдиного вхідного аркуша даних:
| 1 | = SUMIFS (D3: D7, C3: C7, H3) |
Крок 2: Додайте посилання на аркуш до формули
Ми зберігаємо результат формули незмінним, але вказуємо, що вхідні дані знаходяться в аркуші, що викликається "Крок 2"
| 1 | = SUMIFS ("Крок 2"! D3: D7, "Крок 2"! C3: C7, H3) |
Крок 3: Вкладіть усередину функції SUMPRODUCT
Щоб підготувати формулу до обчислення SUMIFS на кількох аркушах, а потім підсумувати результати разом, ми додаємо функцію SUMPRODUCT навколо формули
| 1 | = ПІДПРИЄМСТВО (SUMIFS ('Крок 3'! D3: D7, 'Крок 3'! C3: C7, H3)) |
Використання функції SUMIFS на одному аркуші дає єдине значення. На кількох аркушах функція SUMIFS виводить масив значень (по одному для кожного аркуша). Ми використовуємо функцію SUMPRODUCT для підрахунку значень у цьому масиві.
Крок 4: Замініть посилання на аркуш списком назв аркушів
Ми хочемо замінити Назва аркуша частина формули зі списком даних, що містить значення: Січ, Лютий, Бер, і Квіт. Цей список зберігається у комірках F3: F6.
Функція INDIRECT to забезпечує відображення текстового списку Назви аркушів вважається частиною дійсного посилання на комірку у функції SUMIFS.
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
У цій формулі попередньо записане посилання на діапазон:
| 1 | "Крок 3"! D3: D7 |
Замінюється на:
| 1 | Непрямий ("'" & F3: F6 & "'!" & "D3: D7") |
Лапки ускладнюють читання формули, тому тут вона показана з додаванням пробілів:
| 1 | Непрямий ("'" & F3: F6 & "'!" & "D3: D7") |
Використання цього способу посилання на список клітинок також дозволяє нам узагальнити дані з декількох аркушів, які не відповідають стилю числового списку. Стандартне 3D -посилання вимагатиме, щоб назви аркушів були у стилі: Input1, Input2, Input3 тощо, але наведений вище приклад дозволяє використовувати список будь -яких Назви аркушів і посилання на них в окремій комірці.
Блокування посилань на клітинки
Щоб полегшити читання наших формул, ми показали формули без заблокованих посилань на клітинки:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Але ці формули не працюватимуть належним чином при копіюванні та вставці в інше місце у файлі. Замість цього, ви повинні використовувати заблоковані посилання на клітинки, як це:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & $ F $ 3: $ F $ 6 & "'!" & "D3: D7"), INDIRECT ("'" & $ F $ 3: $ F $ 6 & "'!" & "C3: C7"), H3)) |
Прочитайте нашу статтю про блокування посилань на клітинки, щоб дізнатися більше.
Сума, якщо на кількох аркушах у Таблицях Google
Використання функції INDIRECT для посилання на список аркушів у функції SUMPRODUCT та SUMIFS наразі неможливо в Google Таблицях.
Натомість для кожного аркуша введення можна зробити окремі розрахунки SUMIFS, а результати скласти разом:
| 1234 | = SUMIFS (січ. D3: D7, січ. C3: C7, H3)+SUMIFS (лютий! D3: D7, лютий! C3: C7, H3)+SUMIFS (березень! D3: D7, березень! C3: C7, H3)+SUMIFS (квітень! D3: D7, квітень! C3: C7, H3) |
