Pivot Tablo: Excel'de Adım Adım Özet Tablo Oluşturma

Pivot tablo bir tablonun satırlarını Region gibi bir kategoriye göre gruplar ve her grup için Sales gibi bir sayıyı formülsüz toplar. Bir tane oluşturmak için verideki bir hücreye tıklayın, Ekle > PivotTable'a gidin, Tamam'a basın ve Region'ı Satırlar'a, Sales'i Değerler'e sürükleyin. Aşağıdaki tablo bir pivot tablo değildir: aynı özeti formüllerle kurar, böylece toplamların değişmesini izleyebilirsiniz.

FormüllerSıfırla

F2fx

=SUMIF($A$2:$A$9,E2,$C$2:$C$9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Sales	% of total
2	North	Apple	120		North	455	49%
3	South	Pear	85		South	305	33%
4	North	Pear	240		East	170	18%
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Formülünü görmek için bir hücreye tıkla. Bir sayıyı ya da formülü değiştir, tablo yeniden hesaplanır.Türkçe Excel’de: =ETOPLA($A$2:$A$9;E2;$C$2:$C$9)

BENZERSİZ (İngilizce UNIQUE) her bölgeyi bir kez listeler, ETOPLA (İngilizce SUMIF) da onu toplar: North 455, South 305 ve East 170; bunlar toplam 930'un %49'u, %33'ü ve %18'idir. C3'ü 185 yapın, South toplamı ve üç yüzde de hemen izler. Bir pivot tablo aynı sayıları gösterirdi, ama ancak onu yeniledikten sonra. Tablodaki formülleri de Türkçe adlarla ve noktalı virgülle yazabilirsiniz: =ETOPLA($A$2:$A$9;E2;$C$2:$C$9).

Pivot tablo nasıl oluşturulur

Başlamadan önce kaynak veriyi denetleyin: her sütunda bir ad bulunan tek bir başlık satırı, satır başına bir kayıt, ortada boş satır veya sütun olmaması ve ara toplam satırı olmaması.

1. Verideki herhangi bir hücreye tıklayın.
2. Ekle > PivotTable'a gidin (bazı sürümlerde Ekle > PivotTable > Tablodan/Aralıktan).
3. Excel aralığı doldurur. Yeni Çalışma Sayfası'nı seçin ve Tamam'a basın.
4. Sağda sütun başlıklarınızı listeleyen PivotTable Alanları bölmesiyle boş bir pivot tablo görünür.
5. Region'ı Satırlar kutusuna, Sales'i Değerler kutusuna sürükleyin. Pivot tablo her bölgeyi bir kez, yanında Sales toplamıyla ve bir Genel Toplam satırıyla gösterir.
6. Gösterileni değiştirmek için alanları kutular arasında ya da bölmenin dışına sürükleyin.

Nereden başlayacağınızdan emin değilseniz Ekle > Önerilen PivotTable'lar veriniz için birkaç hazır düzen gösterir. Mac'te menü aynıdır: Ekle > PivotTable.

Satırlar, Sütunlar, Değerler ve Filtreler

PivotTable Alanları bölmesinde dört kutu vardır ve her pivot tablo hangi sütunun hangi kutuya gideceğine dair bir seçimdir:

• Satırlar: sol tarafta aşağı doğru kategoriler, her farklı değer için bir satır (Region).
• Sütunlar: üstte yatay kategoriler, her farklı değer için bir sütun (Product).
• Değerler: her birleşim için hesaplanacak sayılar. Sayısal bir sütun için varsayılan Topla'dır; Say, Ortalama, En Büyük, En Küçük ve diğerleri Değer Alanı Ayarları'ndadır.
• Filtreler: bütün pivot tablonun ona göre filtrelendiği, üstünde açılır liste olarak gösterilen bir alan.

Satırlar'da Region, Sütunlar'da Product, Değerler'de Sales varken yukarıdaki veri için pivot tablo şöyle görünür:

Sum of Sales   Column Labels
Row Labels     Apple   Pear   Grand Total
East              60    110           170
North            215    240           455
South            150    155           305
Grand Total      425    505           930

Bu düzenin formül sürümü bölgeleri BENZERSİZ ile aşağı, ürünleri DEVRİK_DÖNÜŞÜM(BENZERSİZ()) ile yatay listeler ve ızgaranın her hücresini, iki listeyi ölçüt olarak alan tek bir ÇOKETOPLA ile hesaplar:

FormüllerSıfırla

F2fx

=SUMIFS(C2:C9,A2:A9,E2:E4,B2:B9,F1:G1)

	A	B	C	D	E	F	G
1	Region	Product	Sales			Apple	Pear
2	North	Apple	120		North	215	240
3	South	Pear	85		South	150	155
4	North	Pear	240		East	60	110
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Formülünü görmek için bir hücreye tıkla. Bir sayıyı ya da formülü değiştir, tablo yeniden hesaplanır.Türkçe Excel’de: =ÇOKETOPLA(C2:C9;A2:A9;E2:E4;B2:B9;F1:G1)

E2 North, South ve East'i aşağı, F1 Apple ve Pear'ı yatay taşırır, F2'deki ÇOKETOPLA da aralarındaki 3'e 2'lik ızgarayı doldurur: her bölge ve ürün çifti için bir toplam. B5'i Apple'dan Pear'a çevirin, iki East hücresi de değişir. Buradaki sıra değerlerin ilk göründüğü sıradır; bir pivot tablo etiketlerini alfabetik sıralar.

Toplam yerine sayı, ortalama veya yüzde

Pivot tabloda Değerler kutusundaki alana tıklayın ve Değer Alanı Ayarları'nı seçin. Değerleri Özetleme Ölçütü sekmesi Topla, Say, Ortalama, En Büyük ve En Küçük arasında geçiş yapar; Değerleri Farklı Göster sekmesi sayıları Genel Toplamın %'si, Sütun Toplamının %'si, yürüyen toplam ve diğerlerine çevirir. Her birinin doğrudan bir formül karşılığı vardır:

FormüllerSıfırla

E2fx

=UNIQUE(A2:A9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Orders	Average
2	North	Apple	120		North	3	151.7
3	South	Pear	85		South	3	101.7
4	North	Pear	240		East	2	85.0
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Formülünü görmek için bir hücreye tıkla. Bir sayıyı ya da formülü değiştir, tablo yeniden hesaplanır.Türkçe Excel’de: =BENZERSİZ(A2:A9)

North'un ortalaması 151.7 olan 3 siparişi, South'un ortalaması 101.7 olan 3 siparişi, East'in ortalaması 85.0 olan 2 siparişi vardır. Genel Toplamın %'si sütunu bu sayfadaki ilk tablodadır.

Özeti tek bir ürüne göre filtreleme

Filtreler kutusu pivot tablonun üstüne bir açılır liste koyar. Formül sürümü, açılır listesi olan bir hücre ve ETOPLA'ya bir koşul daha ekleyen ÇOKETOPLA'dır (İngilizce SUMIFS). F1'de bir ürün seçin:

FormüllerSıfırla

F1fx

	A	B	C	D	E	F
1	Region	Product	Sales		Product:	Apple▾ApplePear
2	North	Apple	120
3	South	Pear	85		Region	Sales
4	North	Pear	240		North	215
5	East	Apple	60		South	150
6	South	Apple	150		East	60
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Formülünü görmek için bir hücreye tıkla. Bir sayıyı ya da formülü değiştir, tablo yeniden hesaplanır.

Apple seçiliyken North 215, South 150 ve East 60 gösterir. Pear'ı seçin, 240, 155 ve 110'a dönerler. Daha fazla koşul için ÇOKETOPLA, listenin Excel'de nasıl ekleneceği için açılır liste sayfasına bakın.

Pivot tabloyu yenileme

Bir pivot tablo kaynak verinin bir kopyasını (pivot önbelleği) tutar ve kaynaktaki bir hücre değiştiğinde yeniden hesaplamaz. Veriyi düzenledikten sonra:

• Pivot tablonun herhangi bir yerine sağ tıklayıp Yenile'yi seçin ya da Windows'ta Alt+F5'e basın.
• Veri > Tümünü Yenile (Ctrl+Alt+F5) çalışma kitabındaki her pivot tabloyu yeniler.
• Dosya her açıldığında yenilemek için pivot tabloya sağ tıklayın, PivotTable Seçenekleri'ni seçin ve Veri sekmesinde Dosyayı açarken verileri yenile'yi işaretleyin.

Kaynak aralığın altına eklenen yeni satırlar yenilemeden sonra bile dahil edilmez. Aralığı PivotTable Analizi > Veri Kaynağını Değiştir altında değiştirin ya da daha iyisi, pivot tabloyu oluşturmadan önce kaynağı bir tabloya çevirin: veriyi seçip Ctrl+T'ye basın ya da Ekle > Tablo'yu kullanın. Bir tablo satır ekledikçe büyür ve pivot tablo onları bir sonraki yenilemede alır.

Her bölgeyi tek bir formülle toplama

FormüllerSıfırla

F2fx

	A	B	C	D	E	F
1	Region	Product	Sales		Region	Sales
2	North	Apple	120		North
3	South	Pear	85		South
4	North	Pear	240		East
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Formülünü görmek için bir hücreye tıkla. Bir sayıyı ya da formülü değiştir, tablo yeniden hesaplanır.

Sıra sende: F2 hücresinde E2:E4'te listelenen her bölgenin satışlarını tek bir formülle toplayın.

Kontrol etCevabı göster

Cevap 455, 305 ve 170'i taşırır. ETOPLA'ya ölçüt olarak bütün E2:E4 listesini vermek bölge başına bir toplam döndürür, bu yüzden aşağı doldurulacak bir şey yoktur. Excel 2019 ve öncesinde ne BENZERSİZ ne de taşma vardır: bölgeleri E2:E4'e yazın ve =ETOPLA($A$2:$A$9;E2;$C$2:$C$9) formülünü aşağı doldurun. $ işaretleri olmadan aralıklar her satırla birlikte aşağı kayar ve toplamlar yanlış çıkar.

GRUPLAÖLÇÜT ve ÖZETÖLÇÜT: tek formülde pivot tablo

Microsoft 365 için Excel'de, bütün bir özeti tek formülden kuran ve yenileme gerektirmeden her formül gibi yeniden hesaplanan iki fonksiyon vardır. Güncel bir Microsoft 365 aboneliği gerektirirler. Yukarıdaki veri için:

=GROUPBY(A2:A9,C2:C9,SUM)

East      170
North     455
South     305
Total     930

=PIVOTBY(A2:A9,B2:B9,C2:C9,SUM)

          Apple   Pear   Total
East         60    110     170
North       215    240     455
South       150    155     305
Total       425    505     930

Türkçe Excel'de bunlar =GRUPLAÖLÇÜT(A2:A9;C2:C9;TOPLA) ve =ÖZETÖLÇÜT(A2:A9;B2:B9;C2:C9;TOPLA) olarak yazılır.

GRUPLAÖLÇÜT (İngilizce GROUPBY) satır alanını, değerleri ve fonksiyonu (TOPLA, BAĞ_DEĞ_DOLU_SAY, ORTALAMA, MAK, PERCENTOF) alır. ÖZETÖLÇÜT (İngilizce PIVOTBY) aralarına bir sütun alanı ekler. İkisi de bir pivot tablo gibi etiketleri sıralar ve toplam satırları ekler.

Pivot tablo mu, formüller mi

Veriyi keşfetmek ve bir soruyu bir kez cevaplamak için pivot tablo; bir raporda duran, başka formüllere veri veren ve her zaman güncel olması gereken bir özet için formüller kullanın. Bir pivot tablonun sayılarını denetlemek için onun bir hücresini ÇOKETOPLA ile yeniden kurun: ikisi uyuşmuyorsa pivot tablonun genellikle yenilenmesi gerekir ya da kaynak aralığı fazla kısadır.