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

Question

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

Product   | Attribute
----------+----------
Product A | Cyan
Product B | Cyan
Product C | Cyan
Product A | Magenta
Product C | Magenta
Product B | Yellow
Product C | Yellow
Product A | Black
Product B | Black

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

Product   | Attribute
----------+--------------------------
Product A | Cyan,Magenta,Black
Product B | Cyan,Yellow,Black
Product C | Cyan,Magenta,Yellow,Black

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

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

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

Answer 1

   |     A     |     B
---+-----------+-----------
 1 |  PRODUCT  | ATTRIBUTE
 2 | Product A | Cyan
 3 | Product B | Cyan
 4 | Product C | Cyan
 5 | Product A | Magenta
 6 | Product C | Magenta
 7 | Product B | Yellow
 8 | Product C | Yellow
 9 | Product A | Black
10 | Product B | Black

Предполагая, что строка 1:1 является строкой заголовка.

1. Сортировать по столбцу А для группировки по продукту

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

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

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

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

   Product A    Cyan       Cyan                   FALSE
   Product A    Magenta    Cyan,Magenta           FALSE
   Product A    Black      Cyan,Magenta,Black     TRUE
   Product B    Cyan       Cyan                   FALSE
   Product B    Yellow     Cyan,Yellow            FALSE
   Product B    Black      Cyan,Yellow,Black      TRUE
   Product C    Cyan       Cyan                   FALSE
   Product C    Magenta    Cyan,Magenta           FALSE
   Product C    Yellow     Cyan,Magenta,Yellow    TRUE
   

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

6. Готово Колонка A & C - это то, что вам нужно.

Answer 2

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

1. Выберите стол
2. На вкладке POWER QUERY (или DATA в 2016 году) выберите "Из таблицы"
3. Нажмите на столбец "Продукт"
4. на вкладке "Преобразование" выберите "Группировать по"
5. Убедитесь, что на вкладке "Вид" установлен флажок "Панель формул".

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

   ОТ:

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

   TO:

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

   

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

Answer 3

Вот пара подходов, оба "не макро" ...

1. С небольшим набором данных, предварительно отсортировав его по продуктам (аналогично продукту GROUP BY), вы можете сначала скопировать столбец "Продукт", вставить его в другое место, а затем удалить дубликаты. Затем скопируйте "Атрибуты" для каждого продукта и «вставьте специальный, ТРАНСПОЗИРОВАТЬ» рядом с каждым продуктом. Затем объедините запятую с каждым из ваших транспонированных атрибутов в столбце окончательных результатов. По общему признанию, все это «копировать / вставлять специальные / транспонировать» быстро устареет, если у вас длинный список продуктов.

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