W tym przewodniku wyjaśniamy, jak i dlaczego należy używać interfejsu API Arkuszy Google do tworzenia tabel przestawnych w arkuszach kalkulacyjnych.
Co to jest tabela przestawna?
Tabele przestawne umożliwiają podsumowanie danych w arkuszu kalkulacyjnym oraz automatyczne agregowanie, sortowanie, zliczanie i uśrednianie danych przy jednoczesnym wyświetlaniu podsumowania wyników w nowej tabeli. Tabela przestawna działa jak zapytanie w odniesieniu do źródłowego zbioru danych. Te dane źródłowe istnieją w innym miejscu w arkuszu kalkulacyjnym, a tabela przestawna przedstawia widok przetwarzanych danych.
Weźmy na przykład taki zbiór danych o sprzedaży:
O | B | C | D | E | F | G | |
1 | Kategoria produktu | Numer modelu | Koszt | Liczba | Region | Sprzedawca | Data wysyłki |
2 | Diabelski młyn | W-24 | 20,50 USD | 4 | zachód | Beata | 1.03.2016 r. |
3 | Drzwi | D-01X | 15 PLN | 2 | południe | Amir | 15.03.2016 r. |
4 | Silnik | ENG-0134 | 300 PLN | 1 | północ | Karolina | 20.03.2016 r. |
5 | Ramka | FR-0B1 | 34 PLN | 8 | wschód | Hania | 12.03.2016 r. |
6 | Panel | P-034 | 18 PLN | 4 | północ | Dewyn | 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 USD | 11 | południe | Sheldon | 30.04.2016 r. |
9 | Silnik | ENG-0161 | 330 USD | 2 | północ | Jessie | 02.07.2016 r. |
10 | Drzwi | D-01Y | 29 USD | 6 | zachód | Armanda | 13.03.2016 r. |
11 | Ramka | FR-0B1 | 34 PLN | 9 | południe | Yuliana | 27.02.2016 r. |
12 | Panel | P-102 | 12 PLN | 15 | zachód | Karolina | 18.04.2016 r. |
13 | Panel | P-105 | 8,25 USD | 13 | zachód | Jessie | 20.06.2016 r. |
14 | Silnik | ENG-0211 | 283 USD | 1 | północ | Amir | 21.06.2016 r. |
15 | Drzwi | D-01X | 15 PLN | 2 | zachód | Armanda | 03.07.2016 r. |
16 | Ramka | FR-0B1 | 34 PLN | 6 | południe | Karolina | 15.07.2016 r. |
17 | Diabelski młyn | W-25 | 20,00 USD | 8 | południe | Hania | 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 USD | 7 | zachód | Beata | 28.06.2016 r. |
20 | Ramka | FR-0B1 | 34 PLN | 8 | północ | Sheldon | 30.03.2016 r. |
Korzystając z tabeli przestawnej, możesz utworzyć raport pokazujący, ile sztuk danego numeru modelu zostało sprzedanych w poszczególnych regionach:
Kod źródłowy użyty do wygenerowania tej tabeli przestawnej znajdziesz w sekcji Przykład poniżej.
Po umieszczeniu tabeli przestawnej w arkuszu kalkulacyjnym użytkownicy mogą interaktywnie zmieniać strukturę podsumowania i szczegóły, korzystając z interfejsu Arkuszy.
Praca z tabelami przestawnymi
Definicja tabeli przestawnej jest powiązana z jedną komórką w arkuszu. Mimo że renderowany obraz wygląda, jakby miał wiele komórek o wysokości i szerokości, programowo znajduje się on we współrzędnych jednej komórki. Ta komórka staje się lewym górnym rogiem renderowanej tabeli przestawnej, a jej zakres poziomy i pionowy jest określony na podstawie jej definicji.
Dodawanie tabeli przestawnej
Aby dodać tabelę przestawną, użyj metody batchUpdate, dostarczając żądanie updateCells. Za pomocą tego żądania możesz podać definicję PivotTable jako 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 opisanej przez funkcję MyPivotTable
w określonym arkuszu z lewym górnym rogiem komórki A1
. Wysokość i szerokość tabeli przestawnej
są dynamiczne – określa się tylko jej punkt początkowy.
Typ PivotTable umożliwia określenie:
- Ź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 żadnych jawnych żądań zmodyfikowania lub usunięcia tabeli przestawnej. Zamiast tego użyj żądania updateCells z inną zawartością komórki:
- Aby zmodyfikować tabelę przestawną, utwórz zmodyfikowaną definicję PivotTable i zaktualizuj za jej pomocą komórkę, podobnie jak w przypadku dodawania nowej tabeli przestawnej.
- Aby usunąć tabelę przestawną, zaktualizuj komórkę, podając puste wartości. Przykład znajdziesz w artykule Usuwanie tabeli przestawnej.
Przykłady zastosowań
Tabele przestawne mają wiele zastosowań, m.in. w analizach statystycznych, aplikacjach ERP i raportach finansowych. Klasyczne przypadki użycia tabeli przestawnej obejmują następujące elementy:
- Łączna sprzedaż według regionu i kwartału
- Średnie wynagrodzenie według stanowiska i lokalizacji
- Liczba incydentów według usługi i pory dnia
Liczba potencjalnych zastosowań tabel przestawnych jest ogromna, a możliwość ich automatycznego generowania daje ogromne możliwości. Możesz generować tabele przestawne, które obsługują eksplorację interaktywną, ale są dostosowane do określonych okoliczności, na przykład:
- Przeglądaj dane incydentów z ostatnich 24 godzin
- Wyświetl/analizuj dane zbiorcze odpowiadające obecnie wybranemu kontu
- Analizowanie danych sprzedaży w regionach należących do bieżącego użytkownika
Przykład
W tym przykładzie tworzymy tabelę przestawną ze zbioru danych, aby wygenerować raport „Numer modelu według regionu” widoczny na wstępie tej strony. Dodatkowe przykłady znajdziesz na stronie przykładowej tabeli przestawnej.