Фильтровать уникальные значения в первом столбце и подводить итог соответствующим значениям из второго столбца.

Вопрос или проблема

Вот что мне нужно сделать:

Например, у меня есть исходные данные с двумя столбцами. В первом столбце у меня есть некоторые значения, которые могут повторяться. Во втором столбце – значения, соответствующие каждому значению из первого столбца.

Задача состоит в том, чтобы:
– Отфильтровать первый столбец по уникальным значениям.
– Суммировать соответствующие значения из второго столбца.

На изображении я сделал вручную пример.

Теперь я использую фильтры Excel для сортировки по алфавиту. Затем вручную копирую только уникальные значения из первого столбца и вручную суммирую из второго. Пожалуйста, помогите, как это можно сделать проще?

Вы можете использовать формулу массива для автоматизации этого процесса. В этом примере входные данные находятся в ячейках A1:B7.

Теперь в столбце D мы получим уникальные значения из A, а в E мы получим сумму. Однако для правильной работы формулы вы должны оставить одну пустую ячейку в строке 1, которая обычно является заголовком.

Получение уникальных значений.

В ячейку D2 введите следующую формулу и нажмите CTRL + SHIFT + ENTER в строке формул, чтобы создать формулу массива. Формула теперь будет заключена в фигурные скобки, что указывает на то, что это формула массива, и перетащите её вниз, пока не получите пустые ячейки. Я предполагаю, что у вас Excel 2007 и выше, который поддерживает функцию IFERROR. В противном случае просто удалите внешнюю IFERROR.

=IFERROR(INDEX($A$1:$A$7,MATCH(0,COUNTIF($D$1:D1,$A$1:$A$7),0)),””)

Смотрите скриншот ниже.

Теперь в ячейку E2 введите следующую формулу массива и перетащите её вниз.

=SUM(IF($A$1:$A$7=D2,$B$1:$B$7,0))

Смотрите скриншот ниже.

Отрегулируйте ссылки на ячейки, чтобы учесть больший размер таблицы, чтобы вам не пришлось вручную обновлять формулу в дальнейшем.

Создайте сводную таблицу для обоих столбцов, перетащите “Задачу” в метки строк и аналогично перетащите ваши данные в значения и выберите сумму.

Для всех, кто проходит мимо: в современных версиях Excel это можно просто решить, используя функции UNIQUE и SUMIF:
Получите уникальные значения из диапазона A1:A5 с помощью =UNIQUE(A1:A5) в отдельной ячейке (например, A7), это покажет все уникальные значения в диапазоне. В соседней ячейке суммируйте значения по уникальному значению задачи: =SUMIF(A1:A5; A7#; B1:B5). Обратите внимание на оператор #: если в диапазон будут добавлены новые уникальные значения, сумма автоматически расширится.

Ответ или решение

Чтобы упростить процесс фильтрации уникальных значений из первого столбца и суммирования соответствующих значений из второго столбца в Excel, можно использовать несколько методов, включая массивные формулы, сводные таблицы и функции UNIQUE и SUMIF.

1. Использование массивных формул

Этот метод хорошо подходит для версий Excel до 365, где функции UNIQUE ещё не поддерживаются. Предположим, что ваши исходные данные находятся в ячейках A1:B7:

Шаг 1: Получение уникальных значений

В ячейку D2 введите следующую формулу массива и нажмите CTRL + SHIFT + ENTER:

=IFERROR(INDEX($A$1:$A$7,MATCH(0,COUNTIF($D$1:D1,$A$1:$A$7),0)),"")

После этого протяните формулу вниз, пока не начнут появляться пустые значения. Эта формула извлекает уникальные значения из диапазона A1:A7 и выводит их в столбце D.

Шаг 2: Суммирование соответствующих значений

В ячейку E2 введите следующую массивную формулу и также протяните вниз:

=SUM(IF($A$1:$A$7=D2,$B$1:$B$7,0))

Эта формула суммирует значения из столбца B, соответствующие каждому уникальному значению из столбца D.

2. Использование сводной таблицы

Сводные таблицы — это отличный способ для анализа данных без написания сложных формул.

Шаг 1: Создание сводной таблицы

1. Выделите диапазон данных (A1:B7).
2. Перейдите на вкладку Вставка и выберите Сводная таблица.
3. В появившемся диалоговом окне выберите, где вы хотите разместить сводную таблицу (например, на новом листе).
4. В области полей сводной таблицы перетащите столбец A в область Строки и столбец B в область Значения. Убедитесь, что для значений установлено обобщение через Сумма.

Таким образом, вы получите сводную таблицу, которая автоматически суммирует значения из второго столбца для каждого уникального значения первого столбца.

3. Современные функции Excel

Если вы используете Excel 365 или другую современную версию Excel, вы можете использовать функции UNIQUE и SUMIF для более простого решения.

Шаг 1: Получение уникальных значений

В ячейку D1 введите:

=UNIQUE(A1:A7)

Шаг 2: Суммирование

В ячейку E1 введите:

=SUMIF(A1:A7, D1#, B1:B7)

Использование оператора # позволяет автоматически обновлять диапазон суммирования, если в колонне D добавляются новые уникальные значения.

Заключение

Каждый из предложенных методов позволяет эффективно решать задачу фильтрации уникальных значений и их суммирования в Excel. Выбор метода зависит от версии Excel и ваших предпочтений по работе с формулами. Использование сводных таблиц или современных функций Excel значительно упрощает процесс, делает его более интуитивным и позволяет избежать ручного копирования и вставки.