Ten przewodnik wyjaśnia, jak i dlaczego używać interfejsu API Arkuszy Google do tworzenia tabel przestawnych w arkuszach kalkulacyjnych.
Co to jest tabela przestawna?
Tabele przestawne umożliwiają automatyczne podsumowywanie danych w arkuszu kalkulacyjnym agregacji, sortowania, liczenia lub uśredniania danych przy wyświetlaniu podsumowuje wyniki w nowej tabeli. Tabela przestawna działa jak zapytanie ze źródłowym zbiorem danych. Te dane źródłowe istnieją w innej lokalizacji w w arkuszu kalkulacyjnym, a tabela przestawna prezentuje przetwarzany widok danych.
Zobacz np. taki zbiór danych o sprzedaży:
A | B | C | D | E | F | G | |
1 | Kategoria produktu | Numer modelu | Koszt | Ilość | Region | Pracownik działu sprzedaży | Data wysyłki |
2 | Diabelski młyn | W-24 | 20,50 PLN | 4 | zachód | Beata | 01.03.2016 r. |
3 | Drzwi | D-01X | 15 zł | 2 | południe | Amir | 15.03.2016 r. |
4 | Silnik | ENG-0134 | 300 PLN | 1 | północ | Karolina | 20.03.2016 r. |
5 | Rama | FR-0B1 | 34 USD | 8 | wschód | Hanna | 12.03.2016 r. |
6 | Panel | P-034 | 18 PLN | 4 | północ | Devyn | 02.04.2016 r. |
7 | Panel | P-052 | 11,50 PLN | 7 | wschód | Eryk | 16.05.2016 r. |
8 | Diabelski młyn | W-24 | 20,50 PLN | 11 | południe | Sheldon | 30.04.2016 r. |
9 | Silnik | ENG-0161 | 330 USD | 2 | północ | Joanna | 02.07.2016 r. |
10 | Drzwi | D-01Y | 29 USD | 6 | zachód | Armando | 13.03.2016 r. |
11 | Rama | FR-0B1 | 34 USD | 9 | południe | Juliana | 27.02.2016 r. |
12 | Panel | P-102 | 3 USD | 15 | zachód | Karolina | 18.04.2016 r. |
13 | Panel | P-105 | 8,25 USD | 13 | zachód | Joanna | 20.06.2016 r. |
14 | Silnik | ENG-0211 | 283 USD | 1 | północ | Amir | 21.06.2016 r. |
15 | Drzwi | D-01X | 15 zł | 2 | zachód | Armando | 03.07.2016 r. |
16 | Rama | FR-0B1 | 34 USD | 6 | południe | Karolina | 15 lipca 2016 r. |
17 | Diabelski młyn | W-25 | 20,00 USD | 8 | południe | Hanna | 02.05.2016 r. |
18 | Diabelski młyn | W-11 | 29 USD | 13 | wschód | Eryk | 19.05.2016 r. |
19 | Drzwi | D-05 | 17,70 PLN | 7 | zachód | Beata | 28.06.2016 r. |
20 | Rama | FR-0B1 | 34 USD | 8 | północ | Sheldon | 30.03.2016 r. |
Możesz użyć tabeli przestawnej do utworzenia raportu pokazującego, ile sztuk każdego numeru modelu zostało sprzedane w poszczególnych regionach:
Kod źródłowy użyty do wygenerowania tej tabeli przestawnej znajdziesz w przykładzie sekcji poniżej.
Po umieszczeniu tabeli przestawnej w arkuszu kalkulacyjnym użytkownicy mogą interaktywnie zmieniać strukturę i szczegóły podsumowania w interfejsie Arkuszy.
Praca z tabelami przestawnymi
Definicja tabeli przestawnej jest powiązana z jedną komórką w arkuszu. Chociaż jest generowany automatycznie i znajduje się w jednej współrzędnej jednej komórce. Ta komórka zmieni się na lewy górny róg róg wyrenderowanej tabeli przestawnej z jej zakresem w pionie i poziomie zależy od jego definicji.
Dodawanie tabeli przestawnej
Aby dodać tabelę przestawną, użyj batchUpdate, dostarczając updateCells użytkownika. Za pomocą tej prośby przekazujesz Definicja tabeli PivotTable: zawartość komórki, jak pokazano poniżej:
"updateCells": {
"rows": {
"values": [{
"pivotTable": MyPivotTable
},
"start": {
"sheetId": sheetId,
"rowIndex": 0,
"columnIndex": 0
},
"fields": "pivotTable"
}
Spowoduje to umieszczenie tabeli przestawnej opisaną przez MyPivotTable
w określonym arkuszu,
z lewym górnym rogiem komórki A1
. (Wysokość i szerokość osi czasu)
są dynamiczne; określasz tylko punkt początkowy).
Typ PivotTable pozwala określony przez Ciebie:
- Źródłowy zakres danych
- Co najmniej jedno pole, którego dane będą stanowić wiersze tabeli przestawnej
- Co najmniej jedno pole, którego dane będą stanowić kolumny tabeli przestawnej
- Kryteria filtrowania i agregacji
- Układ tabeli przestawnej
Modyfikowanie i usuwanie tabel przestawnych
Nie ma jawnych żądań zmodyfikowania lub usunięcia tabeli przestawnej. Zamiast tego użyj updateCells żądania z różną zawartością komórki:
- Aby zmodyfikować tabelę przestawną, utwórz zmodyfikowaną definicję PivotTable i zaktualizuj używając tej komórki, podobnie jak w przypadku dodawania nowej tabeli przestawnej.
- Aby usunąć tabelę przestawną, podaj w komórce puste wartości. Dla patrz przykład Usuwanie tabeli przestawnej przykład.
Przypadki użycia
Tabele przestawne mają wiele różnych zastosowań w wielu różnych obszarach. w tym analizę statystyczną, aplikacje ERP, raporty finansowe, reszta. Przypadki użycia klasycznej tabeli przestawnej obejmują między innymi:
- Łączna sprzedaż według regionu i kwartału
- Średnie wynagrodzenie według tytułu i lokalizacji
- Liczba incydentów według usługi i pory dnia
Tabele przestawne mają ogromną liczbę potencjalnych zastosowań które można automatycznie generować. Możesz wygenerować tabelę przestawną które obsługują eksplorację interaktywną, ale są dostosowane do okoliczności, na przykład:
- Przeglądaj dane incydentów z ostatnich 24 godzin
- Wyświetl/przeanalizuj dane zbiorcze powiązane z obecnie wybranym kontem
- Analizowanie danych o sprzedaży w regionach należących do bieżącego użytkownika
Przykład
W tym przykładzie tworzymy tabelę przestawną ze zbioru danych w celu wygenerowania „numeru modelu”, region” widoczne na wprowadzeniu tej strony. Dodatkowe przykłady zobacz przykładową tabelę przestawną.