Группировка меток и объединение их текстовых значений (например, сводная таблица)

У меня есть таблица с такими данными:

Товар     | Атрибут
----------+----------
Товар A  | Cyan
Товар B  | Cyan
Товар C  | Cyan
Товар A  | Magenta
Товар C  | Magenta
Товар B  | Yellow
Товар C  | Yellow
Товар A  | Black
Товар B  | Black

Я хотел бы сгруппировать все по столбцу A, а столбец B сделать списком значений, разделенным запятыми, которые имеют общий столбец A, например:

Товар     | Атрибут
----------+--------------------------
Товар A  | Cyan,Magenta,Black
Товар B  | Cyan,Yellow,Black
Товар C  | Cyan,Magenta,Yellow,Black

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

Мне удалось это сделать, импортировав данные в базу данных MySQL и используя функцию MySQL GROUP_CONCAT(Attribute) в запросе с клаузой GROUP BY Product, после многократных попыток найти решение в Excel.

Для справки в будущем, возможно ли это в Excel без использования макросов? Независимо от того, возможно это или нет, как можно этого добиться?

   |     A     |     B
---+-----------+-----------
 1 |  ТОВАР    | АТРИБУТ
 2 | Товар A  | Cyan
 3 | Товар B  | Cyan
 4 | Товар C  | Cyan
 5 | Товар A  | Magenta
 6 | Товар C  | Magenta
 7 | Товар B  | Yellow
 8 | Товар C  | Yellow
 9 | Товар A  | Black
10 | Товар B  | Black

Предположим, что строка 1:1 это строка заголовка.

1. Сортировка по столбцу A, чтобы сгруппировать по товарам

2. Подготовьте данные в формате, разделенном запятыми в столбце C, введя в C2 следующую формулу и скопировав до C3:C10.

   =IF(A2<>A1, B2, C1 & "," & B2)
   

3. Определите нужные строки, введя в D2 =A2<>A3 и скопировав до D3:D10.

4. Скопируйте столбцы C:D, затем вставьте их как значение (AltE–S–V–Enter). Вы получите:

   Товар A    Cyan       Cyan                   FALSE
   Товар A    Magenta    Cyan,Magenta           FALSE
   Товар A    Black      Cyan,Magenta,Black     TRUE
   Товар B    Cyan       Cyan                   FALSE
   Товар B    Yellow     Cyan,Yellow            FALSE
   Товар B    Black      Cyan,Yellow,Black      TRUE
   Товар C    Cyan       Cyan                   FALSE
   Товар C    Magenta    Cyan,Magenta           FALSE
   Товар C    Yellow     Cyan,Magenta,Yellow    TRUE
   

5. Удалите ненужные строки, отфильтровав FALSE в столбце D с помощью автоматической фильтрации и удалив эти строки.

6. Завершите. Столбцы A и C это то, что вам нужно.

Я знаю, что это старый пост, но у меня была такая задача сегодня. Я использовал дополнение PowerQuery от Microsoft (ПРИМЕЧАНИЕ: в Excel 2016 оно встроено по умолчанию).

1. Выберите вашу таблицу
2. На вкладке POWER QUERY (или ДАННЫЕ в 2016), выберите “Из таблицы”
3. Нажмите на столбец “Товар”
4. На вкладке Преобразование выберите “Группировать по”
5. На вкладке Вид убедитесь, что “Строка формул” отмечена

6. Измените формулу

   С:

   = Table.Group(#"Changed Type", {"Product"}, {{"Count", each Table.RowCount(_), type number}})
   

   НА:

   = Table.Group(#"Changed Type", {"Product"}, {{"Attributes", each Text.Combine([Attribute], ", "), type text}})
   

   

Шаг 6 использует формулы Power Query (M) для выполнения манипуляций с данными, недоступных через базовые операции в интерфейсе. Microsoft предоставляет полную справку онлайн по всем доступным расширенным функциям в Power Query.

Вот несколько подходов, оба “без макросов”…

1. С небольшим объемом данных, после предварительной сортировки по товарам (похоже на группировку по товарам), вы могли бы сначала скопировать столбец “Товар”, вставить его в другое место, затем удалить дубликаты. Далее, скопируйте “Атрибуты” для каждого товара и “вставьте со специальными возможностями, ТРАНСПОЗИЦИЯ” рядом с каждым товаром. Затем объедините запятую с каждым из ваших транспонированных атрибутов в финальном результирующем столбце. Очевидно, что все это “копировать/вставить со специальными возможностями/транспонировать” быстро надоест, если у вас длинный список товаров.

2. Если у вас много данных, используя несколько формул, вы можете добиться конечного результата, как показано ниже. Формулы в F2, G2, H2, I2 и N2 указаны синими стрелками. Скопируйте их на строки ниже по мере необходимости. Обратите внимание, что J2:L2 используют ту же формулу, что и I2. Также формула F2 ссылается на именованный диапазон “Products”, который охватывает диапазон A:A.

Это старый вопрос, но я столкнулся с ним, пытаясь сделать то же самое, поэтому, если это кому-то поможет, теперь это можно сделать в ячейке, используя функции.

Предположим, ваши данные находятся в A1, это сгруппирует его по столбцу A, затем удалит дубликаты и отсортирует значения из столбца B. Если вы хотите сохранить дубликаты и не выполнять сортировку, то вам нужен только GROUPBY.

=LET(data, GROUPBY(A2:A10, B2:B10, ARRAYTOTEXT, 0, 0), HSTACK(INDEX(data, 0, 1), BYROW(data, LAMBDA(row, LET(val, INDEX(row, 1, 2), vals, TEXTSPLIT(val, ", ", , TRUE), TEXTJOIN(",", TRUE, SORT(UNIQUE(vals, TRUE))))))))