Tabela przestawna programu Excel 2010: Jak mogę rozwinąć / zwinąć podzbiór rekordów pod odrębnym zestawem rekordów?

1

Rozważmy następujący zestaw danych w tabeli o nazwie Pracownik:

Name   Sup   Q1Sales  Q2Sales  Q1Atte  Q2Atte
John   Joe   Good     Bad      Bad     Bad
Mary   Joe   Good     Bad      Bad     Bad
Bob    Sara  Bad      Good     Bad     Bad
Mary   Sara  Bad      Good     Bad     Bad
Ed     Joe   Good     Good     Bad     Good
Sally  Jim   Bad      Good     Good    Good

Głównym segmentem danych jest sprzedaż i frekwencja kwartalna1 i kwartału2 (Atte). W pierwszym rekordzie przedstawiciel handlowy John zgłaszający się do Joe miał dobrą sprzedaż w pierwszym kwartale i złą sprzedaż w drugim kwartale. Jego uwaga Q1 była zła i jest Q2 frekwencja była również zła.

Gdybym miał wykonać następujące odrębne zapytanie ...

select distinct Q1Sales, Q2Sales, Q1Atte, Q2Atte from Employee

... zwróci następujące rekordy:

Q1Sales  Q2Sales  Q1Atte  Q2Atte
Good     Bad      Bad     Bad
Bad      Good     Bad     Bad
Good     Good     Bad     Good
Bad      Good     Good    Good

Teraz moje pytanie o tabelę przestawną Excel 2010. Chcę uruchomić pełny wyciąg wszystkich danych w Employee (bez wyraźnego) i wkleić go do arkusza Excel. Następnie chcę zawinąć to wszystko w tabelę przestawną. Po całkowitym zwinięciu chcę, aby tabela przestawna wyglądała następująco:

Q1Sales  Q2Sales  Q1Atte  Q2Atte
Good     Bad      Bad     Bad
Bad      Good     Bad     Bad
Good     Good     Bad     Good
Bad      Good     Good    Good

Jednak chcę mieć możliwość rozszerzenia go o dwa poziomy głęboko, jak pokazano tutaj:

Q1Sales  Q2Sales  Q1Atte  Q2Atte
Good     Bad      Bad     Bad
     Sup
     Joe
        Name
        John
        Mary
Bad      Good     Bad     Bad
    Sup
    Sara
        Name
        Bob
        Mary              
Good     Good     Bad     Good
    Sup
    Joe
        Name
        Ed
Bad      Good     Good    Good
    Sup
    Jim
        Name
        Sally

Czy tabela przestawna programu Excel obsługuje ten rodzaj wyraźnych zapytań? Jeśli nie miałem jasności co do mojego pytania, daj mi znać, jakich dodatkowych informacji potrzebujesz.

— oscilatingcretin
źródło

1

Myślę, że twoim największym wyzwaniem jest oryginalny układ danych. Czy zmiana układu danych jest możliwa? Czy to zasadniczo zmiana, czy drobne modyfikacje (np. Dodanie kolumn pomocniczych)? Czy jest to rzeczywista tabela programu Excel lub czy dane są pobierane z innego źródła (np. SQL)?

— dav

Układ danych jest w porządku. To tylko jednorazowy wyciąg danych. Konwertujemy dane w jednej tabeli na inną i odwzorowujemy stare wartości na nowe wartości. posiadanie charakterystycznego zestawu rekordów pozwoli nam przyjrzeć się wszystkim możliwym kombinacjom istniejących wartości, a także wszystkim możliwym kombinacjom wartości, które zostały przekonwertowane ze starych. Dane są pobierane z bazy danych, ale miałem nadzieję, że tabele przestawne Excela pozwolą mi na bardziej dynamiczny składany format.

— oscilatingcretin

1

EDYTOWAĆ: Po rozważeniu początkowego pytania i przejrzeniu danych nie sądzę, aby Excel był w stanie wygenerować typ raportu, który wydaje się pożądany (bez zmiany hakowania lub zmiany danych, jak sugerowano w obu odpowiedziach do tej pory).

Jeśli format raportu jest głównym celem, a format danych jest zablokowany, to myślę, że potrzebujesz oprogramowania do pisania raportów, takiego jak Crystal Reports (lub podobny pakiet), który może odczytywać dane i udostępniać na ich podstawie specjalnie sformatowany raport . Wygląda na to, że chcesz mieć zarówno raport podsumowujący, jak i szczegółowy, który Crystal może zrobić całkiem łatwo. Jeśli format jest mniej ważny i utrzymanie w programie Excel jest krytyczne, musisz myśleć poza polem, jak dwie odpowiedzi (do tej pory).

Dzięki kolumnom pomocniczym i formatowaniu warunkowemu można utworzyć taki raport w programie Excel:

Pivot Detail

Niestety, nie jest to tabela przestawna i nie można przenieść poziomu szczegółowości w górę ani w dół. Dzięki VBA i formantom formularzy możesz stworzyć poziom powiększenia, który opisałeś, ale nie mogę wymyślić natywnego sposobu na osiągnięcie tego.

ORYGINALNA ODPOWIEDŹ: Nie wierzę w twoją aktualną aranżację danych, dzięki czemu możesz zrobić dokładnie tak, jak chcesz. Oto szybka próba bez manipulacji danymi:

PivotDistinct

Dwie tabele przestawne dostarczą wszystkich informacji, ale nie tak zwartych i dopracowanych, jak sugerowałeś.

W obu przypadkach wystarczy umieścić wszystkie pola, które chcesz reprezentować, w sekcji Wiersze wierszy tabeli przestawnej i wybrać Projekt tabelaryczny w układzie raportu.

— dav
źródło

Sprawdzę twoje zaktualizowane rozwiązanie w ciągu kilku dni i poinformuję Cię, czy to działa. Ogólnie rzecz biorąc, wygląda to na to, czego potrzebuję, ale z elementami wyższego poziomu widocznymi z boku, a nie zwiniętymi. Jednak odpowiednia kolumna w prawdziwej bazie danych będzie zawierała kilkaset elementów na ekwiwalenty Sup-ekwiwalentu, więc teraz, gdy o tym myślę, może stanowić straszną ilość przewijania.

— oscilatingcretin

Skończyło się na napisaniu własnego skomplikowanego makra VBA, aby zrobić to, czego potrzebuję. Zaznaczyłem twoje, jak odpowiedziałeś, ponieważ powiedziałeś, że Excel nie ma rodzimego wsparcia dla tego, co technicznie odpowiedziało na moje pytanie.

— oscilatingcretin

0

Jakikolwiek powód, dla którego nie tworzysz unikalnego (odrębnego) klucza w danych po wyodrębnieniu? Tabela przestawna automatycznie połączy dane.

Jeśli dodam kolumnę „klucz” o następującej formule:

=CONCATENATE(D2,"-",E2,"-",F2,"-",G2)

Data

Mogę dokładnie odtworzyć to, do czego próbujesz (myślę ..) pod względem poziomów

Pivot output

— James
źródło

Nie pomyślałem o tym. Dobry pomysł, ale to bardziej hack niż rozwiązanie. Nie mogę dać temu biznesowi czegoś, co wygląda tak (brak kluczowych nazw kolumn; po prostu „Bad-Good-Bad-Bad”). To oczywiście tylko próbka. Prawdziwe dane mają znacznie więcej kolumn, których potrzebuję, aby uzyskać różne poziomy.

— oscilatingcretin