Загальна формула
=IF(A2=A1,"",SUMIF(A:A,A2,B:B))
Резюме
Для проміжних підсумків даних за групою або міткою, безпосередньо в таблиці, ви можете використовувати формулу на основі функції SUMIF.
У наведеному прикладі формула в D5 має вигляд:
=IF(B5=B4,"",SUMIF(B:B,B5,C:C))
Примітка: дані повинні бути відсортовані за стовпчиком групування, щоб отримати точні результати.
Пояснення
Структура цієї формули базується на IF, який перевіряє кожне значення у стовпці B, щоб перевірити, чи воно однакове зі значенням у "клітинці вище". Коли значення збігаються, формула нічого не повертає (""). Коли значення різні, функція IF викликає SUMIF:
SUMIF(B:B,B5,C:C)
У кожному рядку, де SUMIF ініціюється IF, SUMIF обчислює суму всіх відповідних рядків у стовпці C (C: C). Критеріями, що використовуються SUMIF, є поточне значення рядка стовпця B (B5), перевірене на всі стовпці B (B: B).
Повні посилання на такі стовпці - це круто та елегантно, оскільки вам не потрібно турбуватися про те, де дані починаються та закінчуються, але ви повинні бути впевнені, що над та під таблицею немає додаткових даних, які SUMIF може потрапити.
Продуктивність
Може здатися, що використання повного посилання на стовпець - погана ідея, оскільки поточні версії Excel містять більше 1 млн рядків. Однак тестування показало, що Excel обчислює дані лише у "використаному діапазоні" (А1 до адреси перетину останнього використовуваного стовпця та останнього використовуваного рядка) із формулою цього типу.
Чарльз Вільямс у Fast Excel має хорошу статтю на цю тему з повним набором результатів хронометражу.
Чому щодо зведених таблиць?
Цей приклад призначений для того, щоб показати, як працюють посилання на повні стовпці та як їх можна використовувати безпосередньо в таблиці даних. Зведені таблиці залишаються відмінним способом групування та узагальнення даних.
