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