Pivot tabloya hesaplanan alan eklemek: formül, örnek ve tuzaklar

Hesaplanan alan nedir, neden gerekir?

Pivot tablo, kaynak veride bulunan alanları toplar, sayar ya da ortalamasını alır. Ancak çoğu zaman rapor için gereken ölçü kaynakta yoktur: kâr marjı, birim fiyat, komisyon tutarı, iade oranı gibi. Hesaplanan alan (calculated field), pivot tablonun içinde mevcut alanlardan yeni bir ölçü türetmeye yarar.

Bu özelliği kullanmadan alternatif, kaynak veriye yeni bir sütun ekleyip formülü orada yazmaktır. Çoğu durumda bu da çalışır, ancak her kaynak güncellemesinde formülü satırlara taşımak gerekir. Hesaplanan alan ise pivotun içinde yaşar, kaynak yenilendiğinde otomatik yeniden hesaplanır ve raporu bir kez kuran herkesin işini kolaylaştırır.

Bir ürün satışı tablosunda ciro ve maliyet alanları varsa, hesaplanan alan ile kâr tutarı ve kâr marjı doğrudan pivotta gösterilebilir. Böylece analizi başkasına devrederken formülün hangi sütunda gizli kaldığını araştırmak gerekmez.

Adım adım hesaplanan alan ekleme

Genel akış şöyledir:

• Pivot tablo içinde bir hücre seçilir ve şeritteki pivot araçlarından alan, öğe ve küme seçeneklerine gidilir.
• Burada hesaplanan alan eklenir ve alana bir ad verilir. Ad, okuyucunun ne ölçtüğünü anlayacağı kadar açık olmalıdır.
• Formül kutusuna formül yazılır. Formül, kaynak alan adlarını kullanır; alanlar listesinden çift tıklanarak formüle eklenebilir. Örneğin kâr = ciro eksi maliyet ya da marj = kâr bölü ciro.
• Tamam denildiğinde yeni alan alan listesinde belirir ve diğer alanlar gibi değerler bölgesine sürüklenebilir.
• Sayı biçimi ayrıca ayarlanır; oran niteliğindeki alanı yüzde biçiminde göstermek okunurluğu artırır.

Formülde hücre adresi ya da başka sayfadaki başvuru kullanılamaz; yalnızca pivot kaynağındaki alan adları kullanılır. Alan adlarında boşluk varsa Excel adı tırnak içine alır. Hesaplanan alan silmek ya da düzenlemek için aynı menüye dönülür.

Önemli kural: hesaplama toplamlar üzerinden yapılır

Hesaplanan alanın en kritik özelliği, formülün satır satır değil, alanların toplamları üzerinden çalışmasıdır. Yani kâr marjı formülü kâr bölü ciro olduğunda, pivot her hücre için önce ilgili kategorinin toplam kârını ve toplam cirosunu alır, sonra böler. Bu, ağırlıklı ortalama sonucu verir ve çoğu zaman doğru olandır.

Ama bu davranış bazı ölçüler için yanıltıcıdır. Örneğin birim fiyat alanını ciro bölü adet olarak tanımlarsanız, sonuç toplam ciro bölü toplam adettir; satır satır fiyatların ortalaması değildir. Bu sonuçlar genellikle birbirinden farklıdır ve hangisine ihtiyaç duyduğunuz, soruya göre belirlenir.

Ayrıca formülde toplam yerine ortalama ya da sayım gerektiren bir mantık varsa hesaplanan alan uygun değildir; bu durumda kaynak veriye yardımcı sütun eklemek ya da veri modelinde ölçü tanımlamak gerekir. Bu sınırı bilmek, pivot sonuçlarının neden beklenenden farklı çıktığı sorusunun ana yanıtıdır.

Hesaplanan öğe ile farkı

Pivot tablo farklı türde formüller sunar. Hesaplanan alan, alanlar arasında işlem yapar: ciro ile maliyet arasından kâr üretmek gibi. Hesaplanan öğe (calculated item) ise aynı alanın içindeki öğeler arasında işlem yapar: bir ürün grubu alanında A ve B öğelerinin toplamından yeni bir birleşik öğe oluşturmak gibi.

Örneğin bölge alanında Marmara ve Ege öğelerini toplayıp Batı adlı yeni bir öğe tanımlamak hesaplanan öğedir. Cirodan maliyeti çıkarıp kâr üretmek ise hesaplanan alandır.

Hesaplanan öğe daha az tercih edilir: alandaki tüm öğelerin ayrı ayrı hesaplanması gerektiğinde performansı düşürebilir, toplamlarla karışabilir ve veri modelli pivotlarda genellikle desteklenmez. Çoğu durumda öğeleri gruplamak ya da kaynakta yeni bir sınıflandırma sütunu oluşturmak daha temiz bir çözümdür. Pratik kural: önce gruplamayı deneyin, olmuyorsa hesaplanan öğeye bakın.

Sık hatalar ve tuzaklar

Hesaplanan alanlarda şu durumlar sık sorun çıkarır:

• Toplam satırı yanlış görünür: Formül toplamlar üzerinden çalıştığı için genel toplam beklenenden farklı olabilir. Önce ürün bazında doğru olan sonucun genel toplamda da mantıklı olup olmadığı kontrol edilmelidir.
• Kaynak alan adı değişir: Kaynak veride sütun adı değiştirilirse formül bozulur. Hesaplanan alanı yeniden tanımlamak gerekir.
• Bölme hatası: Paydası sıfır olan kategorilerde hata gösterilebilir. Bunu önlemek için kaynakta sıfır paydalı satırlar gözden geçirilir ya da pivot seçeneklerinde hata değerleri için gösterim ayarlanır.
• Ortalama beklentisi: Hesaplanan alan toplam üzerinden çalıştığı için satır bazlı ortalama beklenen bir ölçüyü yanlış verir.
• Hesaplanan alan yerine kaynak sütun gerektiği halde alan eklenmesi: Satır bazında koşullu mantık (EĞER ile) gerekiyorsa pivotta değil kaynakta çözülmelidir.

Rapor teslim edilmeden önce örnek birkaç satırı elle hesaplayıp pivot sonucuyla karşılaştırmak en güvenli kontroldür.

Başlangıçtan ileri düzeye öğrenme yolu

Başlangıç aşamasında düz bir pivot tablo kurulur ve basit bir hesaplanan alan (örneğin kâr) eklenir. Toplam satırının nasıl davrandığını gözlemlemek bu aşamanın asıl kazanımıdır.

Orta aşamada oran ve marj alanları, hesaplanan öğe ile gruplama karşılaştırması ve sayı biçimlendirme çalışılır. Pivot tabloların dilimleyiciyle etkileşimli hale getirilmesi de bu aşamada eklenir.

İleri aşamada Power Pivot ve veri modeli devreye girer. Veri modelinde ölçüler DAX adı verilen formül diliyle yazılır; bu ölçüler hesaplanan alanın sınırlarını aşar ve çoklu tablo, zaman karşılaştırması ve ağırlıklı ölçülere izin verir. Hesaplanan alan, bu geçişin kavramsal basamağıdır.

Bu beceriyi Mindset programlarında nerede çalışırsınız?

Mindset kataloğundaki Microsoft Excel ile Veri Analizi Eğitimi - İleri Excel (Uygulamalı) programı, Veri Tabloları ve PivotTabloları modülüyle birlikte ileri veri analizi araçları ve ileri formüller başlıklarını içerir. Hesaplanan alanın dayandığı pivot tablo kurulumu bu kapsamda işlenir. Finans, satış analizi ve raporlama ekipleri için uygundur. Kurumsal ekiplerin ortak rapor şablonlarını standartlaştırması açısından da ilgili olabilir.