İleri Ders 8: Pivot Tabloda Hesaplanan Alan ve Dilimleyici

Hesaplanan alan ne işe yarar?

Hesaplanan alan, pivot tabloda kaynak verideki alanlardan formülle türetilen yeni bir alandır. Satış listenizde Ciro ve Kâr sütunları varsa ama Kâr marjı yoksa, kaynak tabloya sütun eklemeden marjı pivotun içinde hesaplayabilirsiniz. Alan, alan listesinde diğerleri gibi görünür ve Değerler bölümüne sürüklenebilir.

Bu dersteki örnek, üç kategoride sekiz ürünün ciro ve kârını içeren küçük bir tablo. Rakamlar varsayımsaldır.

Adım adım: Kâr marjı alanını eklemek

Pivot tablonun içinde bir hücre seçtiğinizde şeritte PivotTable Çözümle sekmesi görünür (bazı sürümlerde adı yalnızca Çözümle'dir). Hesaplamalar grubunda Alanlar, Öğeler ve Kümeler menüsünü açıp Hesaplanan Alan komutunu seçin.

Açılan kutuda Ad alanına "Kâr marjı" yazın. Formül kutusundaki 0'ı silip = Kâr / Ciro yazın; alan adlarını listeden seçip Alan Ekle düğmesiyle de ekleyebilirsiniz. Ekle ve Tamam düğmelerine bastığınızda yeni alan değerler bölümüne gelir. Sayı biçimini yüzde yaptığınızda Elektronik satırında %25 görürsünüz.

Bu formülde hücre başvurusu ve tanımlı ad kullanamazsınız. Formül yalnızca pivotun kaynak alanlarıyla ve sabit sayılarla çalışır.

Toplamların oranı, oranların ortalaması

Hesaplanan alan, formülü tek tek satırlara değil, alanların toplamına uygular. Elektronik için önce kâr toplamı (9.000 TL) ve ciro toplamı (36.000 TL) bulunur, sonra bölünür: 9.000 / 36.000 = %25.

Kaynak tabloya her satır için marj sütunu ekleyip pivotta bunun ortalamasını alsaydınız farklı bir sayı görürdünüz. Elektronik ürünlerin satır marjları %25, %35 ve %20'dir ve ortalamaları yaklaşık %26,67'dir. Fark, 6.000 TL'lik şarj aletinin yüksek marjının 18.000 TL'lik kulaklıkla eşit ağırlık almasından gelir. Kategori kârlılığını soruyorsanız doğru cevap toplamların oranıdır, yani hesaplanan alanın verdiği sonuçtur.

Bu farkı bilmek, hesaplanan alanın her formülde doğru sonuç vermediğini de anlamanızı sağlar. Adet x birim fiyat gibi satır düzeyinde çarpım gereken hesaplarda hesaplanan alan toplam adet ile toplam fiyatı çarpar ve anlamsız bir sayı üretir. Böyle hesapları kaynak tabloya sütun olarak ekleyin.

Dilimleyici ile filtreleme

Dilimleyici, pivot tabloyu tıklanabilir düğmelerle filtreleyen bir paneldir. Pivotta bir hücre seçip PivotTable Çözümle sekmesinden Dilimleyici Ekle'ye basın, açılan kutuda Kategori alanını işaretleyip Tamam deyin.

Dilimleyicideki Ev düğmesine tıkladığınızda pivot yalnızca Ev kategorisini gösterir. Birden fazla öğe seçmek için Ctrl tuşuna basılı tutarak tıklayabilirsiniz. Filtreyi kaldırmak için dilimleyicinin sağ üst köşesindeki filtre temizleme simgesini kullanın. Dilimleyicinin avantajı, hangi filtrenin açık olduğunu raporu okuyan herkesin bir bakışta görmesidir.

Dilimleyiciyi rapora yerleştirmek

Dilimleyiciye tıkladığınızda şeritte Dilimleyici sekmesi görünür. Bu sekmeden dilimleyicinin stilini, düğmelerin kaç sütunda dizileceğini ve boyutunu ayarlarsınız. Üç kategorili bir raporda düğmeleri tek satıra dizmek, dilimleyiciyi tablonun hemen üstüne ince bir şerit olarak yerleştirmenizi sağlar.

Aylık bir yönetim raporunda Kategori dilimleyicisi ve ay alanına bağlı ikinci bir dilimleyici birlikte kullanılabilir. Okur önce Ev'i, sonra mart ayını seçer ve pivot yalnızca bu kesişimi gösterir. Dilimleyicilerde seçili düğmeler renkli kalır, bu yüzden ekran görüntüsü alınan raporda bile hangi filtrenin açık olduğu anlaşılır.

Genel toplam satırında ne görünür?

Hesaplanan alan genel toplam satırında da aynı kuralla çalışır: tüm kârların toplamını tüm ciroların toplamına böler. Sekiz ürünün kâr toplamı 15.070 TL, ciro toplamı 53.000 TL'dir ve genel toplam satırında yaklaşık %28,43 görünür.

Bu sayı üç kategorinin marjlarının (%25, %33,23 ve %43,75) ortalaması değildir. Basit ortalama %34'e yakın çıkardı, ama Elektronik cironun üçte ikisinden fazlasını oluşturduğu için genel marj onun %25'ine yaklaşır. Raporu sunarken bu farkı bilmek, "kategorilerin ortalaması neden tutmuyor" sorusuna hazırlıklı olmanızı sağlar.

Sonucu formülle doğrulamak

Pivot tablo yenilenmeden kaynak verideki değişikliği göstermez. Raporu paylaşmadan önce bir kategoriyi formülle hesaplamak, hem yenileme unutkanlığını hem yanlış alan seçimini yakalar. Elektronik marjı için =YUVARLA(ÇOKETOPLA(D2:D9;A2:A9;"Elektronik")/ÇOKETOPLA(C2:C9;A2:A9;"Elektronik");4) hesaplanan alanla aynı %25'i verir.

Sıradaki adım

Alıştırmada üç kategorinin kâr marjını, hesaplanan alanın yaptığı gibi toplamların oranı olarak formülle hesaplayacaksınız. Ardından aynı veriyle bir pivot kurup Kâr marjı hesaplanan alanını ekleyin ve sonuçları karşılaştırın. Son adımda Kategori dilimleyicisi ekleyip filtreyi değiştirin.

=ÇOKETOPLA(C2:C9;A2:A9;"Elektronik")

=SUMIFS(C2:C9,A2:A9,"Elektronik")

=YUVARLA(ÇOKETOPLA(D2:D9;A2:A9;"Elektronik")/ÇOKETOPLA(C2:C9;A2:A9;"Elektronik");4)

=ROUND(SUMIFS(D2:D9,A2:A9,"Elektronik")/SUMIFS(C2:C9,A2:A9,"Elektronik"),4)

=YUVARLA(TOPLA.ÇARPIM((A2:A9="Elektronik")*(D2:D9/C2:C9))/EĞERSAY(A2:A9;"Elektronik");4)

=ROUND(SUMPRODUCT((A2:A9="Elektronik")*(D2:D9/C2:C9))/COUNTIF(A2:A9,"Elektronik"),4)