Formüllerle Özet Tablo Tarzı Raporlar

Bir çapraz tablo verileri iki boyutlu olarak düzenler: bir kategori için satırlar, başka bir kategori için sütunlar ve bunların kesişimlerini dolduran toplamlar. Klasik bir örnekte yanda Bölge, üstte Çeyrek ve her hücrede Satış bulunur.

Çapraz tablolar kullanışlıdır; ancak manuel olarak yenilenmeleri gerekir ve sabit bir blokta yer alırlar. Formüllerle oluşturulan çapraz tablo, veriler her değiştiğinde kendini canlı olarak yeniden oluşturur.

Bu derste satır başlıklarını, sütun başlıklarını ve her kesişimi otomatik olarak hesaplayan bir SUMIFS formülleri gövdesini yerleştireceksiniz.

A sütununda Bölge, B sütununda Çeyrek ve C sütununda Tutar bulunan, 2. satırdan 500. satıra kadar uzanan Satışlar adlı bir çalışma sayfası kullanacağız.

İstediğimiz rapor şöyle görünür:

• Satır etiketleri: E sütununda aşağı doğru listelenen her benzersiz Bölge.
• Sütun etiketleri: 1. satırda F ile I sütunları arasında yatay olarak yer alan Q1, Q2, Q3, Q4.
• Gövde: her Bölge ve Çeyrek çifti için toplam Tutar.

Her gövde hücresi tek bir soruya yanıt verir: bu bölge bu çeyrekte ne kadar satış yaptı?

Satır başlıkları benzersiz bölgelerdir. Bunların E sütununda aşağı doğru taşması ve sıralı kalması için UNIQUE ile SORT işlevlerini kullanın.

Bunu E2 hücresine yazın:

Bölgeler artık E2 hücresini ve aşağısındaki hücreleri kendiliğinden doldurur. Özet tablolarda olduğu gibi bu liste, tüm tablonun dayandığı temeldir.

Sütun başlıkları bir satıra yayılan çeyreklerdir. Q1, Q2, Q3, Q4 değerlerini elle yazabilir veya UNIQUE işlevinin etrafına TRANSPOSE ekleyerek bunları yatay biçimde taşıtabilirsiniz.

F1 hücresine yazıldığında benzersiz çeyrekleri üst kısma yatay olarak yerleştirir:

TRANSPOSE, dikey bir listeyi yatay listeye dönüştürür; böylece çeyreklerden oluşan bir sütun, başlıklardan oluşan bir satıra dönüşür. Artık tablonun her iki ekseni de hazırdır.

Şimdi gövdeyi doldurun. Her hücrenin, satırındaki bölge ile sütunundaki çeyreğin toplamını hesaplaması gerekir. SUMIFS iki koşulu kolayca işler.

İlk gövde hücresi olan F2'ye şunu yazın:

Bu formül, Bölge'nin soldaki etikete ve Çeyrek'in yukarıdaki başlığa eşit olduğu Tutar değerlerini toplar. Bu, çapraz tablodaki tek bir kesişimdir.

Dolar işaretleri, tek bir formülü kopyalayarak tüm tabloyu doldurmanızı sağlar. Karma başvuruları inceleyin:

• $E2, sütunu E'ye sabitler ancak satırın değişmesine izin verir; böylece her satır kendi bölgesini okur.
• F$1, satırı 1'e sabitler ancak sütunun değişmesine izin verir; böylece her sütun kendi çeyreğini okur.
• $C$2:$C$500 tamamen sabitlenmiştir; çünkü veri aralığı hiçbir zaman yer değiştirmez.

F2 hücresini tüm çeyrekler boyunca sağa ve tüm bölgeler boyunca aşağı kopyalayın; her hücre kendini kusursuzca uyarlar.

F2 doğru şekilde yazıldıktan sonra hücreyi seçin ve doldurma tutamacını çeyrek sütunları boyunca sağa, ardından bölge satırları boyunca aşağı sürükleyin. Excel, göreli kısımları sizin için yeniden yazar.

• G2 hücresinde Bölge için $E2, Çeyrek için G$1 kullanılır.
• F3 hücresinde Bölge için $E3, Çeyrek için F$1 kullanılır.

Sonuç, her kesişimin toplamının hesaplandığı eksiksiz bir çapraz tablodur. Özet tablo sihirbazına gerek yoktur ve Satışlar verileri değiştiği anda yeniden hesaplanır.

Gerçek bir çapraz tablo genel toplamları gösterir. Her satır ve sütunda basit SUM kullanarak sağa bir Toplam sütunu, alta da bir Toplam satırı ekleyin.

İlk bölgenin satır toplamı için bunu son çeyrekten sonraki sütuna yerleştirin:

Sütun toplamı için o çeyrekteki gövde hücrelerini satırlar boyunca toplayın. Bu kenar toplamları raporu tamamlanmış hissettirir ve okuyucuların sayıları bir bakışta kontrol etmesini sağlar.

Kullandığınız araç destekliyorsa, taşma başvurularını doğrudan SUMIFS işlevine vererek kopyalama işleminden kaçınabilirsiniz. Taşan başlıkları ölçüt olarak kullanın.

Bu tek formül, her bölge ve çeyrek kesişiminin toplamını hesaplar:

Burada E2# dikey bölge listesidir, F1# ise yatay çeyrek listesidir. Excel, bunları tek seferde tam bir tablo hâlinde eşleştirir. Sürükleme yöntemi daha uyumludur; ancak bu, zarif ve modern sürümdür.

Raporlar yalnızca tutarları değil, payı da gösterdiğinde daha anlamlı olur. Her bölgenin toplamını genel toplamın yüzdesi olarak ifade eden bir sütun ekleyin.

Bölgenin satır toplamı J2 hücresinde ve genel toplam J10 hücresinde bulunuyorsa şunu yazın:

Genel toplamı $J$10 ile sabitlemek, formülü tüm bölgelere aşağı doğru doldurmanızı ve her zaman aynı paydaya bölmesini sağlar. Sütunu yüzde biçiminde biçimlendirin; böylece okuyucular hangi bölgelerin öne çıktığını hemen görür.

Çapraz tablonun formüllerle güvenilir biçimde çalışmasını birkaç alışkanlık sağlar:

• Yeni satırların dahil edilmesi için 2. satırdan 500. satıra kadar olanlar gibi geniş aralıklar kullanın.
• Veri aralıklarını tam $ sabitleyicileriyle sabitleyin; yalnızca başlık başvuruları değişmelidir.
• Taşan başlıklar ve toplamlar için yer kalması amacıyla aşağıda ve sağda boş alan bırakın.

İyi yapıldığında bu rapor hiç bakım gerektirmez. Yeni satışlar girin; tablo, toplamlar ve etiketlerin tümü kendiliğinden güncellenir.

Formüllerle oluşturulan çapraz tabloyu çalıştıran karma başvuruları ne kadar iyi anladığınızı kontrol edin.

Yalnızca formüller kullanarak bir çapraz tabloyu yeniden oluşturdunuz:

• UNIQUE ile SORT, taşan bir sütunda satır başlıklarını oluşturdu.
• TRANSPOSE, sütun başlıklarını bir satıra yaydı.
• Karma başvurular olan $E2 ve F$1 ile birlikte kullanılan SUMIFS, sürükleyerek veya E2# ve F1# gibi taşma başvurularıyla her kesişimi doldurdu.
• SUM, genel toplam kenarlarını ekledi.

Tüm tablo canlı olarak yeniden hesaplanır. Bir sonraki derste ölçümleri yöneten açılır listelerle gösterge panelini etkileşimli hâle getireceksiniz.