EXCEL’DE DİNAMİK DİZİLERLE KPI HESAPLAMALARI YAPMAK
Bir KPI raporu “çalışıyor” olabilir ama her yeni satır eklendiğinde formüller koptuğunda, sütunlar taşınca ya da filtre mantığı elle güncellendiğinde rapor aslında sürdürülebilir değildir. Modern Excel’in dinamik diziler yaklaşımı, KPI hesaplamalarını tek bir mantık akışıyla kurup çıktı aralıklarını kendiliğinden büyüten yapılar üretmeyi mümkün kılar. Bu sayede bir metrik tanımlarsınız; veri büyüdükçe raporun “kendini toparlaması” için ek yama yapmanız gerekmez.
Kurumsal ekiplerde KPI’lar sadece sayısal çıktı değildir; karar alma döngüsünün parçasıdır. Ürün yöneticisi sürüm başarısını, satış lideri hedef sapmasını, operasyon ekibi SLA uyumunu, finans ekibi marj trendini aynı dosyada izlemek ister. Dinamik diziler, “pivot mu yapalım, Power Query mi kuralım, yoksa yeni bir tablo mu açalım?” tartışmasını her defasında başlatmadan, ölçüm mantığını tek yerde toplayıp güvenilir bir raporlama katmanı üretmek için güçlü bir araçtır.
Bu yazıda dinamik dizilerle KPI hesaplamaları kurmayı, ölçüm mantığını LET ve LAMBDA ile okunabilir hale getirmeyi, filtreleme-sıralama-özetleme adımlarını tek formülde birleştirmeyi ve dashboard tüketimi için “spill” çıktıları güvenli biçimde kullanmayı adım adım ele alacağız. İsterseniz kapsamlı bir uygulama için Excel eğitimi içeriğine de göz atabilirsiniz.
Dinamik dizilerle KPI mantığını kurgulamak ve ölçmek
Dinamik dizi yaklaşımında amaç, KPI’nın “hangi satırlar dahil, hangi kriterler geçerli, hangi dönemde ölçülüyor, hangi boyutta kırılıyor” gibi kurallarını tek akışta tanımlamaktır. Böylece formül kopyalamak yerine “kural motoru” kurarsınız. En sık ihtiyaç duyulan yapı taşları FILTER, SORT, UNIQUE, CHOOSECOLS, TAKE, DROP, HSTACK ve VSTACK fonksiyonlarıdır.
KPI tasarlarken önce veri modelini netleştirin: Kaydın tarihi, birim/ekip, ürün/özellik, durum/sonuç, tutar/süre gibi alanlar hangi sütunlarda duruyor? Ardından ölçüm hedefini tanımlayın: “Dönem bazlı toplam”, “adet”, “oran”, “medyan süre” veya “hedefe göre sapma” gibi. Bu tanım oturduğunda dinamik dizi, veri büyüdükçe çıktı aralığını otomatik genişleterek raporu güncel tutar.
Tablo yapısını standardize etmek ve isimli aralıkları yönetmek
KPI hesaplamalarında en pratik başlangıç, veriyi Excel Tablosu (Structured Table) yapmak ve sütunları anlamlı isimlerle etiketlemektir. Örnek olarak “Veri” isimli tabloyu düşünelim: Tarih, Ekip, Ürün, Durum, Tutar, SüreDakika. Bu yapı, dinamik dizi formüllerinin hem okunabilirliğini hem de bakımı kolay hale getirir.
Ölçüm kapsamını netleştirmek ve dönem kırılımı belirlemek
Bir KPI “hangi zaman penceresinde” raporlanacak? Aylık, haftalık, sprint bazlı veya çeyreklik… Dinamik dizilerle dönem üretmek için tarih alanından ay başlangıcı türetmek (örneğin EOMONTH veya DATE ile) ve UNIQUE ile dönem listesini otomatik oluşturmak sık kullanılan bir desendir. Bu sayede “yeni ay eklendi, tabloya sütun açalım” ihtiyacı azalır.
LET fonksiyonuyla KPI formüllerini okunur hale getirmek
Dinamik diziler güçlüdür ama tek satırda uzun formüller yazıldığında bakım zorlaşabilir. LET, formül içinde değişken tanımlayarak hem performansı artırır hem de ekip içinde okunabilirliği yükseltir. KPI’larda tipik olarak “filtrelenmiş veri”, “dönem”, “kriter”, “ara toplam” gibi parçalar LET ile isimlendirilir.
Filtreleme adımlarını ayrıştırmak ve değişkenleştirmek
Örneğin sadece “Tamamlandı” durumundaki kayıtları, belirli bir ekip için ve seçili tarih aralığında çekmek istiyorsunuz. Bu filtre sonucu, hem toplam tutar hem de adet KPI’sında tekrar tekrar kullanılabilir. LET ile aynı filtreyi tek yerde tanımlayıp farklı metriklerde reuse etmek, hatayı azaltır.
=LET(
baslangic, DATE(2026,1,1),
bitis, DATE(2026,12,31),
ekip, "Platform",
veri, Veri,
f, FILTER(veri, (veri[Durum]="Tamamlandı")*(veri[Ekip]=ekip)*(veri[Tarih]>=baslangic)*(veri[Tarih]<=bitis)),
toplamTutar, SUM(CHOOSECOLS(f, XMATCH("Tutar", Veri[#Headers]))),
adet, ROWS(f),
HSTACK(toplamTutar, adet)
)Yukarıdaki desen, tek bir filtre tanımıyla iki KPI’yı aynı anda döndürür. Üstelik filtre aralığı büyüdükçe çıktı kendiliğinden güncellenir. Bu tür formüllerde dikkat edilmesi gereken nokta, başlık eşleştirmeleri ve sütun seçiminin tutarlı olmasıdır.
İş mantığını tek yerde tutmak ve hata ayıklamayı hızlandırmak
LET kullanırken her değişkeni iş mantığına göre adlandırın: f yerine filtreliKayitlar, adet yerine tamamlananAdet gibi. Büyük ekiplerde KPI raporları ortak kullanıldığı için “bu formül ne yapıyor?” sorusuna cevap vermek, karar döngüsünü hızlandırır ve sürpriz sapmaları azaltır.
FILTER, UNIQUE ve SORT ile boyut kırılımları üretmek
Bir KPI çoğu zaman tek bir toplam sayı değildir; ekip, ürün, müşteri segmenti veya sprint gibi boyutlara göre kırılımları gerekir. Dinamik dizilerde boyut listelerini otomatik üretmek için UNIQUE ve sıralamak için SORT kullanılır. Ardından bu boyutların her biri için KPI’yı hesaplamak üzere MAP/BYROW gibi fonksiyonlar devreye girer.
Ekip listesini otomatik çıkarmak ve rapor aralığını genişletmek
Veri tablonuza yeni bir ekip eklendiğinde raporun “kendiliğinden” o ekibi göstermesi, dinamik dizilerin en büyük avantajıdır. Örneğin tüm ekiplerin alfabetik listesini aşağıdaki gibi üretebilirsiniz:
=SORT(UNIQUE(Veri[Ekip]))Bu çıktı “spill” olarak aşağı doğru taşar. Dashboard tasarımında bu alana referans vererek ekip bazlı KPI tablolarını otomatik oluşturabilirsiniz.
Ürün ve dönem kırılımını birleştirmek ve matris raporlamak
Bir başka yaygın senaryo, ürün x ay matrisinde KPI göstermektir. Ay listesini tarih alanından türetip UNIQUE ile almak, ardından bu dönemi label’a çevirmek mümkündür. Burada hedef; elle sütun eklemek yerine, dönemin değişmesiyle matrisin otomatik büyümesidir.
MAP ve BYROW ile KPI hesaplarını ölçeklemek
Dinamik dizilerde en kritik sıçrama, “her satır için ayrı formül” yaklaşımından “tek formülle tüm satırların hesaplanması” yaklaşımına geçiştir. BYROW satır bazlı, MAP ise bir veya daha fazla dizi üzerinde paralel hesap yapmak için idealdir. Bu sayede ekip listesi, ürün listesi veya dönem listesi üzerinde KPI hesaplarını toplu üretebilirsiniz.
Tekil boyut üzerinden KPI üretmek ve tablo döndürmek
Örneğin her ekip için “Tamamlandı” adet KPI’sını üretmek istiyorsunuz. Ekip listesini UNIQUE ile alıp MAP ile her ekipte filtrelenmiş satır sayısını hesaplayabilirsiniz:
=LET(
ekipler, SORT(UNIQUE(Veri[Ekip])),
kpiAdet, MAP(ekipler, LAMBDA(e,
ROWS(FILTER(Veri, (Veri[Durum]="Tamamlandı")*(Veri[Ekip]=e)))
)),
HSTACK(ekipler, kpiAdet)
)Bu çıktı iki sütunlu bir tablo döndürür: Ekip ve KPI değeriniz. Veri büyüdükçe ekip listesi uzar, KPI tablosu otomatik genişler. Ayrıca bu tabloyu grafiklere bağlamak için tek bir kaynak aralık yeterlidir.
Çoklu kriterleri parametrelemek ve LAMBDA ile standartlaştırmak
Kurumsal raporlarda KPI tanımı sık değişir: “Sadece canlı ortam”, “Sadece belirli ürün ailesi”, “Sadece kritik öncelik” gibi. Burada LAMBDA, KPI’yı bir fonksiyon gibi tanımlayıp parametreleri dışarıdan almanızı sağlar. Böylece KPI’nın mantığı tek bir yerde yaşar; kriter değiştiğinde formül zinciri kırılmaz.
KPI oranlarıyla hedef sapmalarını hesaplamak ve izlemek
KPI’ların önemli bir kısmı “oran” veya “hedefe göre sapma”dır: dönüşüm oranı, hatalı kayıt oranı, SLA uyum yüzdesi, hedefe ulaşma oranı gibi. Dinamik dizilerde oran hesaplarında dikkat edilmesi gereken iki konu vardır: paydanın sıfır olması ve filtre sonucunun boş dönmesidir. Bu yüzden IFERROR veya IF ile korumalı hesap yapmak kritik olur.
Hedef tablosuyla birleştirmek ve sapmayı otomatik hesaplamak
Hedefleri ayrı bir tabloda (örneğin “Hedef” tablosunda Ekip, Ay, HedefAdet) tutup, gerçek KPI ile eşleştirmek iyi bir pratiktir. Dinamik dizilerle hedefi arayıp sapmayı hesaplamak mümkündür. Mantık: “gerçek - hedef” ya da “gerçek / hedef - 1” gibi. Bu yaklaşım, karar vericilerin sapmayı hızlı yorumlamasını sağlar.
SLA gibi süre KPI’larını hesaplamak ve outlier yönetmek
Süre KPI’larında ortalama yanıltıcı olabilir; medyan veya yüzde 90 persentil daha anlamlıdır. Excel’de PERCENTILE.INC gibi fonksiyonlar kullanılabilir; dinamik dizilerle filtrelenmiş süre dizisini beslersiniz. Ayrıca çok uç değerleri (outlier) belirli bir eşiğe göre kırpmak, raporun güvenilirliğini artırır.
- Payda sıfırsa oranı 0 veya boş döndürmek
- Süre KPI’larında medyan veya persentil tercih etmek
- Outlier için eşik tanımlayıp raporlamayı standardize etmek
- Hedef sapmasını hem mutlak hem yüzdesel göstermek
Dashboard tüketimi için spill aralıklarını güvenli kullanmak
Dinamik dizi çıktıları “spill” aralıkları üretir; bu aralıklar veri büyüdükçe genişler. Dashboard’da grafik, koşullu biçimlendirme ve doğrulama listeleri için bu dinamik alanları doğru yönetmek gerekir. En sık karşılaşılan sorun, spill alanının üzerine başka içerik gelmesi veya grafik kaynak aralığının sabit kalmasıdır.
Grafik kaynaklarını dinamikleştirmek ve rapor kırılmasını önlemek
HSTACK ile ürettiğiniz KPI tablosunu tek bir hücreden başlayacak şekilde konumlandırıp, grafiğin veri kaynağını bu spill aralığına bağlayın. Ayrıca rapor sayfasında spill alanının çevresinde boş tampon alan bırakmak, sonradan yapılan eklemelerin “#SPILL!” hatası üretmesini engeller.
Veri doğrulama listelerini dinamik dizilerle beslemek
Kullanıcıların ekip veya ürün seçtiği filtre hücrelerinde, doğrulama listelerini UNIQUE ile beslemek büyük kolaylıktır. Böylece yeni bir ekip/ürün eklendiğinde açılır listeler güncellenir. Karar vericiler için bu, “liste güncel değil” kaynaklı yanlış raporlama riskini azaltır.
Performans, bakım ve yönetişimle sürdürülebilirlik sağlamak
Dinamik diziler pratik bir raporlama katmanı sunar; ancak kurumsal kullanımda performans ve yönetişim kritik hale gelir. Büyük veri setlerinde FILTER’ı çok kez çağırmak yerine LET ile ara sonuçları cache etmek, aynı raporda tekrar eden hesapları azaltır. Ayrıca KPI tanımlarını standartlaştırmak, farklı ekiplerin aynı metriği farklı hesaplamasını önler.
Sürümleme disiplinini kurmak ve KPI tanımını dokümante etmek
KPI formüllerini “KPI Sözlüğü” sayfasında toplayın: metrik adı, tanımı, filtre kriterleri, dönem kuralı ve örnek çıktısı. Böylece ürün, satış ve operasyon ekipleri aynı KPI’ya bakarken kavram birliği oluşur. Değişiklikleri tarihleyip sürümlemek, geçmiş raporların neden farklı göründüğünü açıklamayı kolaylaştırır.
Hata senaryolarını ele almak ve güvenilir raporlama üretmek
Boş filtre sonucu, eksik tarih, beklenmeyen metin türü gibi hatalar KPI’ları sessizce bozabilir. IFERROR ile “0” döndürmek her zaman doğru değildir; bazı KPI’larda boş dönmek daha anlamlıdır. Bu nedenle her metrik için “hata davranışı” belirlemek, raporların karar desteği niteliğini artırır. Özellikle yönetici panolarında hatayı görünür kılmak, yanlış karar riskini azaltır.
