Z tego przewodnika dowiesz się, jak i dlaczego używać interfejsu Google Sheets API do tworzenia tabel przestawnych w arkuszach kalkulacyjnych.
Czym jest tabela przestawna?
Tabele przestawne umożliwiają podsumowywanie danych w arkuszu kalkulacyjnym przez automatyczne agregowanie, sortowanie, zliczanie lub wyliczanie średniej danych oraz wyświetlanie podsumowanych wyników w nowej tabeli. Tabela przestawna działa jak rodzaj zapytania względem źródłowego zbioru danych. Te dane źródłowe znajdują się w innej lokalizacji w arkuszu kalkulacyjnym, a tabela przestawna przedstawia przetworzone dane.
Rozważmy na przykład ten 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 zł | 4 | zachód | Beth | 01.03.2016 |
3 | Drzwi | D-01X | 15 zł | 2 | południe | Amir | 15.03.2016 r. |
4 | Silnik | ENG-0134 | 300 PLN | 1 | północ | Carmen | 20.03.2016 |
5 | Rama | FR-0B1 | 34,00 USD | 8 | wschód | Hannah | 12.03.2016 |
6 | Panel | P-034 | 18 PLN | 4 | północ | Devyn | 2.04.2016 r. |
7 | Panel | P-052 | 11,50 USD | 7 | wschód | Erik | 16.05.2016 r. |
8 | Diabelski młyn | W-24 | 20,50 zł | 11 | południe | Sheldon | 30.04.2016 r. |
9 | Silnik | ENG-0161 | 330 PLN | 2 | północ | Jessie | 2.07.2016 |
10 | Drzwi | D-01Y | 29,00 USD | 6 | zachód | Armando | 13.03.2016 |
11 | Rama | FR-0B1 | 34,00 USD | 9 | południe | Yuliana | 27.02.2016 r. |
12 | Panel | P-102 | 3 USD | 15 | zachód | Carmen | 18.04.2016 r. |
13 | Panel | P-105 | 8,25 PLN | 13 | zachód | Jessie | 20.06.2016 r. |
14 | Silnik | ENG-0211 | 283,00 PLN | 1 | północ | Amir | 21.06.2016 r. |
15 | Drzwi | D-01X | 15 zł | 2 | zachód | Armando | 3.07.2016 r. |
16 | Rama | FR-0B1 | 34,00 USD | 6 | południe | Carmen | 15.07.2016 r. |
17 | Diabelski młyn | W-25 | 20,00 USD | 8 | południe | Hannah | 2.05.2016 r. |
18 | Diabelski młyn | W-11 | 29,00 USD | 13 | wschód | Erik | 19.05.2016 r. |
19 | Drzwi | D-05 | 17,70 USD | 7 | zachód | Beth | 28.06.2016 r. |
20 | Rama | FR-0B1 | 34,00 USD | 8 | północ | Sheldon | 30.03.2016 r. |
Za pomocą tabeli przestawnej możesz utworzyć raport pokazujący, ile sztuk każdego modelu zostało sprzedanych w danym regionie:
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ę i szczegóły podsumowania za pomocą interfejsu Arkuszy.
Praca z tabelami przestawną
Definicja tabeli przestawnej jest powiązana z pojedynczą komórką w arkuszu. Chociaż renderowany obraz zajmuje wiele komórek zarówno pod względem wysokości, jak i szerokości, programowo jest on umieszczony w pojedynczej komórce. Ta komórka staje się lewym górnym rogiem wyrenderowanej tabeli przestawnej, a jej poziomy i pionowy zasięg są określane przez jej definicję.
Dodawanie tabeli przestawnej
Aby dodać tabelę przestawną, użyj metody batchUpdate, podając prośbę 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"
}
W ten sposób umieszczasz tabelę przestawną opisaną przez MyPivotTable
na określonym arkuszu, z lewym górnym rogiem w komórce A1
. (wysokość i szerokość tabeli przestawnej są dynamiczne; musisz podać tylko punkt początkowy).
Typ PivotTable umożliwia określenie:
- Zakres danych źródłowych
- co najmniej 1 pole, którego dane będą tworzyć wiersze tabeli przestawnej;
- Co najmniej 1 pole, którego dane będą tworzyć kolumny tabeli przestawnej
- Kryteria filtrowania i zbiorczości
- Układ tabeli przestawnej
Modyfikowanie i usuwanie tabel przestawnych
Nie ma żadnych wyraźnych próśb o modyfikowanie ani usuwanie tabeli przestawnej. Zamiast tego użyj żądania updateCells z inną zawartością komórek:
- Aby zmodyfikować tabelę przestawną, utwórz zmodyfikowaną definicję PivotTable i zaktualizuj komórkę, używając jej, podobnie jak w przypadku dodawania nowej tabeli przestawnej.
- Aby usunąć tabelę przestawną, zaktualizuj komórkę, wpisując w niej puste wartości. Przykład znajdziesz w artykule Usuwanie tabeli przestawnej.
Przypadki użycia
Tabele przestawne można stosować na wiele sposobów w różnych obszarach, m.in. do analizy statystycznej, aplikacji ERP, raportowania finansowego itp. Klasyczne tabele przestawne można stosować m.in. do:
- Łączna sprzedaż według regionu i kwartału
- Średnia pensja w zależności od stanowiska i lokalizacji
- Liczba incydentów według usługi i pory dnia
Tabele przestawne mają wiele zastosowań, a możliwość ich generowania programowo jest bardzo przydatna. Możesz generować tabele przestawne, które umożliwiają interaktywne eksplorowanie danych, ale są dostosowane do konkretnych okoliczności, np.:
- Wyświetlanie danych o incydentach z ostatnich 24 godzin
- wyświetlać i analizować dane zbiorcze odpowiadające aktualnie wybranemu kontu;
- Sprawdzanie danych o sprzedaży w przypadku obszarów należących do bieżącego użytkownika
Przykład
W tym przykładzie tworzymy tabelę przestawną na podstawie zbioru danych, aby uzyskać raport „Liczba modeli według regionu” pokazany we wstępie na tej stronie. Aby zobaczyć więcej przykładów, otwórz stronę przykładowa tabela przestawna.