Как получить дедуплицированный список меток строк и их суммированных значений в Excel 2010

Question

У меня есть лист Excel с несколькими строками в этом формате:

Column A|Column B
label   | value
--------|--------

header (merged cells)
labelA  |12
labelB  |5
labelC  |9

header (merged cells)
labelA  |13
labelD  |8

Это в основном список меток и значений, разделенных на несколько разных разделов. На самом деле существует 70-80 этикеток, и все этикетки используются 5-10 раз. Мне нужно создать вывод на отдельном листе в следующем формате:

Column A|Column B
label   | Total sum
--------|----------
labelA  |25
labelB  |5
labelC  |9
labelD  |8

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

Я уверен, что могу создать макрос для этого, но я не хочу добавлять макросы, если мне это не нужно. Данные в данный момент отсутствуют в сводной таблице. Есть ли способ сделать это без этих решений?

Answer 1

Получение суммы для разных меток легко достигается с помощью формулы SUMIF . Предполагая, что ваши метки находятся в столбце A вашего выходного листа, а первая метка находится в строке 2, формула для B2 будет иметь следующий вид:

=SUMIF(SourceSheet!$A:$A,$A1,SourceSheet!$B:$B)

- расширять по мере необходимости. Получить все метки автоматически немного сложнее, так как вам нужно их дублировать. Следующая формула массива сделает это для меток в строках с 1 по 100 вашего исходного листа (при необходимости измените область, заменив SourceSheet на правильное имя листа):

=IFERROR(
   INDEX(SourceSheet!$A1:$A100,
     SMALL(
       IFERROR(
         IF(
           MATCH(SourceSheet!$A1:$A100,SourceSheet!$A1:$A100,0)<ROW(),
           "",
           MATCH(SourceSheet!$A1:$A100,SourceSheet!$A1:$A100,0)
         ),
       ""),
     ROW())
   ),
 "")

Обратите внимание, что это относится к одной строке в Excel (здесь я выделил формулу, чтобы сделать ее более читабельной), и чтобы она работала, вам нужно сохранить ее как формулу массива (выберите весь диапазон, который вы хотите получить в результате введите формулу в строке формул - не в ячейке! - и сохраните ее, нажав Ctrl+Shift+Enter).

Если формула была введена правильно, начиная с таких значений:

вернет эти результаты:

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

Answer 2

Лучшее, что вы можете сделать, - это повернуть его и, как только вы добавите новые данные, просто обновите таблицу. Если вам действительно не нравятся опорные точки , вы можете использовать sumif или sumproduct. Прежде чем создавать формулу, установите диапазоны имен для ваших столбцов. Будет проще, если вы создадите формулу на другом листе.

=sumif(ColumnA, "labelA", ColumnB)

ColumnA & ColumnB будут названиями ваших диапазонов. Это просто образец

=sumproduct((ColumnA="labelA")*(ColumnB))

Что касается новых меток, вы можете вручную добавить их на второй лист. Я не знаю, как добавить их автоматически, если вы не сделаете макрос. Надеюсь, это поможет. :)

Answer 3

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

=sumif(Sheet1!A1:A9999, "labelA", Sheet1!B2:B9999)

Вам нужно будет добавить функцию вручную для каждой новой метки. Я не уверен, как автоматически добавить итоги для нового ярлыка.