Grupowanie etykiet i łączenie ich wartości tekstowych (np. Tabela przestawna)


22

Mam arkusz kalkulacyjny z takimi danymi:

Produkt | Atrybut
---------- + ----------
Produkt A | Cyjan
Produkt B | Cyjan
Produkt C | Cyjan
Produkt A | Magenta
Produkt C | Magenta
Produkt B | Żółty
Produkt C | Żółty
Produkt A | czarny
Produkt B | czarny

Co chciałbym zrobić, pogrupuj wszystko według Kolumny A i niech Kolumna B będzie rozdzielaną przecinkami listą wartości, które mają wspólną Kolumnę A, tak jak poniżej:

Produkt | Atrybut
---------- + --------------------------
Produkt A | Cyjan, Magenta, Czarny
Produkt B | Cyjan, Żółty, Czarny
Produkt C | Cyjan, Magenta, Żółty, Czarny

Niestety, tabele przestawne wiedzą tylko, jak pracować z wartościami liczbowymi, a najdalej do tego dąży, licząc liczbę wystąpień kolumny A.

Udało mi się to ostatecznie zaimportować dane do bazy danych MySQL i użyć GROUP_CONCAT(Attribute)funkcji MySQL w zapytaniu z GROUP BY Productklauzulą, ale po kilkukrotnym uderzeniu głową w biurko, próbując znaleźć rozwiązanie Excel.

Czy w przyszłości będzie to możliwe w programie Excel bez makr? Czy tak jest, czy nie, jak można to zrobić?

Odpowiedzi:


31
   |     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

Zakładając, że wiersz 1: 1 jest wierszem nagłówka.

  1. Sortuj według kolumny A, aby pogrupować według produktu

  2. Przygotuj dane w formacie rozdzielanym przecinkami w kolumnie C , wprowadzając do C2 następującą formułę i skopiuj do C3: C10.

    =IF(A2<>A1, B2, C1 & "," & B2)
    
  3. Zidentyfikuj przydatne wiersze , wprowadzając do D2 =A2<>A3i skopiuj do D3: D10.

  4. Skopiuj kolumnę C: D, a następnie wklej specjalnie jako wartość ( AltE- S- V- Enter). Otrzymasz teraz:

    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. Usuń niepotrzebne wiersze , filtrując FALSEkolumnę D za pomocą AutoFiltera, a następnie usuń te wiersze.

  6. Wykończenie . Kolumna A i C jest tym, czego potrzebujesz.


1
To używa twojej wyobraźni! Gratulacje!
jorgebg

Chociaż to działa, czasy zmieniły się od 2012 roku. Spójrz poniżej, aby uzyskać nową odpowiedź, która korzysta z dodatku PowerQuery (który jest wbudowany w program Excel 2016). To nie może być łatwiejsze. Może rozmawia z komputerem i prosi go o „zgrupowanie moich danych!” Może za kolejne 6 lat.
ripvlan

11

Wiem, że to stary post, ale miałem dzisiaj to wyzwanie. Użyłem dodatku PowerQuery firmy Microsoft (UWAGA: domyślnie jest wbudowany w Excel 2016).

  1. Wybierz swój stół
  2. Na karcie POWER QUERY (lub DANE w 2016 r.) Wybierz „Z tabeli”
  3. Kliknij kolumnę „Produkt”
  4. na karcie Przekształć wybierz „Grupuj według”
  5. Na karcie Widok upewnij się, że „Pasek formuły” jest zaznaczony
  6. Zmień wzór

    OD:

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

    DO:

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

    Kliknij tutaj, aby zobaczyć zdjęcie przedstawiające kroki z góry

Krok 6 wykorzystuje formuły Power Query (M) do wykonywania operacji na danych, które nie zostały ujawnione za pomocą podstawowych operacji udostępnianych w interfejsie użytkownika. Microsoft ma pełne źródło informacji dostępne online dla wszystkich zaawansowanych funkcji dostępnych w Power Query.


Bardzo eleganckie rozwiązanie, prawdopodobnie najlepsze ze wszystkich.
asdmin

@matt polska, zastanawiam się, czy wiesz, jak usunąć zduplikowane wartości wzdłuż linii za pomocą tej niesamowitej sztuczki?
Lana B,

W KROKU 1 (po wybraniu tabeli) powinieneś być w stanie przejść do zakładki Dane na wstążce i kliknąć „Usuń duplikaty”.
Matt Poland,

Istnieje inna opcja w ramach Power Query, jeśli nie chcesz zmieniać swojego oryginalnego stołu. Po KROKU 2 możesz przytrzymać CTRL i kliknąć obie kolumny, aby obie zostały wybrane. Następnie kliknij prawym przyciskiem myszy jedną z kolumn i wybierz „Usuń duplikaty”. Następnie przejdź do KROKU 3.
Matt Poland,

Dziękuję Ci! To najlepsza odpowiedź. Dla tych, którzy używają Excel 2016, PowerQuery jest wbudowany. Wystarczy nacisnąć kartę Dane, a następnie nacisnąć „Z tabeli / zakresu” w sekcji „Pobieranie i przekształcanie danych”. Zostanie otwarty Edytor zapytań o moc, naciśnij kartę Widok i włącz pasek formuły. Reszta instrukcji działa bez zmian.
ripvlan,

3

Oto kilka podejść, zarówno „bez makr”…

  1. Przy małym zestawie danych po pierwszym posortowaniu według produktu (podobnie jak w przypadku GRUPY WEDŁUG produktu) można najpierw skopiować kolumnę „Produkt”, wkleić ją w innym miejscu, a następnie usunąć duplikaty. Następnie skopiuj „Atrybuty” dla każdego produktu i „Wklej specjalnie, TRANSPOSUJ” obok każdego produktu. Następnie połącz przecinek z każdym z transponowanych atrybutów w końcowej kolumnie wyników. Trzeba przyznać, że wszystkie te „kopiuj / wklej specjalnie / transponuj” szybko się zestarzeją, jeśli masz długą listę produktów.

  2. Jeśli masz dużo danych, za pomocą kilku formuł możesz przejść do ostatecznego wyniku, jak pokazano poniżej. Wzory w F2, G2, H2, I2 i N2 są oznaczone niebieskimi strzałkami. W razie potrzeby skopiuj je do poniższych wierszy. Zauważ, że J2: L2 używa tej samej formuły co I2. Ponadto formuła F2 odnosi się do nazwanego zakresu „Produkty”, który obejmuje zakres A: A.

wprowadź opis zdjęcia tutaj


Są to dobre rozwiązania, choć tylko tak, jak mówisz, jeśli zestaw danych jest mały. Dzięki!
p0lar_bear
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.