Dinamik ARRAY'lerle Özet Tablolar

Bir özet tablo, uzun bir ham satır listesini küçük, okunabilir bir bölüme indirger: her kategori için bir satır ve yanında toplamlar. Yüzlerce satırlık bir satış günlüğünün, her bölgeyi ve o bölgenin toplam gelirini gösteren düzenli bir tabloya dönüşmesini düşünün.

Eskiden bunun için elle yenilemeniz gereken bir çapraz tablo kullanılırdı. Modern yöntem ise verileriniz değiştiği anda kendini güncelleyen dinamik dizi formülleri kullanır. Düğme yok, yenileme yok.

Bu derste üç güçlü aracı birleştireceksiniz: kategorileri listelemek için UNIQUE, her birinin toplamını hesaplamak için SUMIFS ve eşleşen satırları almak için FILTER. Bunlar birlikte canlı bir özet oluşturur.

Satışlar adlı, üç sütunlu bir çalışma sayfası düşünün: A sütununda Bölge, B sütununda Ürün ve C sütununda Tutar bulunuyor; veriler 2. satırdan 200. satıra kadar uzanıyor.

Amacımız, her benzersiz bölgeyi ve o bölgenin toplam satışını gösteren bir özet oluşturmaktır. İlk zorluk, bölgeleri elle yazmadan temiz bir bölge listesi elde etmektir; çünkü ileride yeni bölgeler ortaya çıkabilir.

• A2:A200 aralığında East, West, East, North gibi birçok yinelenen bölge adı bulunur.
• İstediğimiz liste şudur: East, West, North; her biri yalnızca bir kez listelenmelidir.

Bu benzersiz liste, özetin tamamının temelidir.

UNIQUE işlevi bir aralık alır ve her değeri yalnızca bir kez döndürür. Sonuç taşar; yani tek bir formül, farklı değerlerin sayısı kadar hücreyi doldurur.

Bunu E2 hücresine yazın; bölge listesi aşağıda otomatik olarak görünür:

Daha sonra verilere yeni bir bölge eklenirse taşan liste kendiliğinden büyür. Formülü hiçbir zaman düzenlemeniz gerekmez.

Şimdi E sütunundaki her bölge için toplam Tutarı hesaplamamız gerekiyor. SUMIFS, bir aralıktaki değerleri yalnızca başka bir aralık belirli bir koşulla eşleştiğinde toplar.

Yapısı şöyledir: SUMIFS(sum_range, criteria_range, criteria). Bunu ilk bölgenin yanındaki F2 hücresine yerleştirin:

E2# başvurusu püf noktasıdır. # işareti, E2'den başlayan taşan aralığın tamamını belirtir. Böylece bu tek formül, UNIQUE tarafından üretilen her bölgenin toplamını hesaplar.

Taşma başvurusu E2#, bir formülün ürettiği bloğun boyutu ne kadar büyürse büyüsün tamamına işaret eder. Özeti dinamik yapan şey budur.

UNIQUE 3 bölge bulduğunda E2# 3 hücre yüksekliğinde olur ve SUMIFS 3 toplam döndürür. Veriler 5 bölgeye çıktığında her iki aralık da hiçbir düzenleme gerektirmeden birlikte genişler.

• E2 = yalnızca en üstteki tek hücre.
• E2# = E2'den başlayan taşan dizinin tamamı.

# işaretini kullanmaya alışın; bu işaret gösterge paneli formüllerinin kalbidir.

Toplamlar sıralandığında özet daha kolay okunur. Kategorilerin alfabetik görünmesi için bölge listesini SORT ile sıralayın veya tüm tabloyu toplama göre sıralayın.

Bölgeleri E2 hücresinde alfabetik olarak listelemek için:

F sütunundaki toplamlar hâlâ E2# başvurusunu kullandığından, bölgeleri sıralamak toplamların sırasını da otomatik olarak düzeltir. İki sütun aynı sırayı korur.

Bazen yalnızca bir toplamı değil, tek bir kategoriye ait temel satırları da görmek istersiniz. FILTER, bir koşulu karşılayan her satırı döndürür ve bunları taşır.

Bölgenin H1 hücresindeki değere eşit olduğu tüm satış satırlarını göstermek için:

H1 hücresinde East varsa East bölgesine ait her satırı görürsünüz. H1'i West olarak değiştirin; blok anında kendini yeniden yazar. Bu, bir gösterge panelindeki ayrıntıya inme görünümünün temelidir.

Eşleşme olmadığında FILTER bir #CALC! hatası verir. Düzeni korumak için isteğe bağlı üçüncü bağımsız değişkeni alternatif bir mesaj olarak sağlayın.

Üçüncü bağımsız değişken, hiç eşleşme olmadığında görüntülenir:

Artık satış olmayan bir bölge, hata yerine anlaşılır bir not gösterir. Yanlışlıkla yapılan bir seçimin düzeni bozmaması için gösterge panellerine bu alternatif mesajı her zaman ekleyin.

Bir özet genellikle her bölgenin kaç siparişi olduğunu da gösterir; yalnızca para tutarını değil. COUNTIFS, SUMIFS gibi bir koşulu karşılayan satırları sayar, ancak toplama aralığı kullanmaz.

Bunu toplamların yanındaki G sütununa yerleştirin:

Artık üç sütunlu özetinizde Bölge, Toplam Satış ve Sipariş Sayısı bulunur; bunların tümü E2# içindeki tek bir taşan bölge listesi tarafından yönlendirilir. Her şey birlikte yenilenir.

Yan yana duran tam tarif şöyledir:

• E2: =SORT(UNIQUE(Sales!A2:A200)) bölgeleri listeler.
• F2: =SUMIFS(Sales!C2:C200, Sales!A2:A200, E2#) her birinin toplamını hesaplar.
• G2: =COUNTIFS(Sales!A2:A200, E2#) her birini sayar.

Satırlar boyunca yalnızca E2 formülü yazılır; F ve G sütunları # başvurusundan taşarak dolar. Satışlar sayfasına herhangi bir yere yeni bir satış ekleyin; üç sütunun tamamı hiçbir tıklama gerektirmeden güncellenir.

Formüllerle oluşturulan bir özetin, değerleri elle yazmaya veya bir çapraz tabloyu yenilemeye göre önemli avantajları vardır:

• Canlı: veriler değiştiği anda yeniden hesaplanır.
• Kendini boyutlandıran: yeni kategoriler UNIQUE ve # başvurusu aracılığıyla otomatik olarak görünür.
• Anlaşılır: hücredeki mantığı herkes okuyabilir.

Bunun karşılığında, taşma aralıklarının büyüyebileceği boş alana ihtiyacı vardır; engellenen taşmaları sonraki bir derste ele alacağız. Şimdilik formüllerinizin altında yer bırakın.

Kendini güncelleyen bir özet tablo oluşturma konusundaki öğrendiklerinizi sınayın.

Kendi kendini güncel tutan bir özet tablo oluşturdunuz:

• UNIQUE her kategoriyi bir kez listeler ve sonucu taşır.
• SORT bu listeyi okunabilirlik için sıralar.
• SUMIFS ve COUNTIFS, E2# taşma başvurusunu kullanarak her kategorinin toplamını ve sayısını hesaplar.
• FILTER, ayrıntıya inmek için eşleşen satırları getirir; eşleşme olmadığında alternatif bir mesaj gösterir.

Her formül taşan listeyi temel aldığından yeni veri eklemek, hiçbir manuel işlem gerektirmeden özetin tamamını günceller. Bir sonraki derste tam kapsamlı çapraz tablo raporlarını yalnızca formüllerle yeniden oluşturacaksınız.