EXCEL'DE DİNAMİK DİZİLERLE KPI HESAPLAMALARI YAPMAK

Yıllarca Excel'de bir KPI tablosu kurmanın yolu sabit oldu: bir özet sayfası, üzerine ÇOKETOPLA ile doldurulmuş onlarca satır, yanına pivot, altına bağlı grafik. Veri büyüdüğünde işin yarısı formülün aralığını yeniden çekmeye giderdi. Sonra modern Excel, hücreye yazılan tek bir formülün aşağı doğru "yayılmasına" izin verdi. Dinamik dizi motoru, KPI hesaplamayı statik bir özet işlemden canlı bir veri akışına çevirdi.

Yapı şu mantıkla yeniden çalışıyor: kaynak tabloya yeni satır eklendiğinde panel kendiliğinden genişliyor; filtre kriteri değiştiğinde sonuç tek hücrede güncelleniyor; sıralama, benzersizleştirme ve toplama tek formül zincirine sığıyor. Bu yazıda dinamik dizinin ne olduğunu, FİLTRE-SIRALA-BENZERSİZ-SIRALI-SIRALAÖLÇÜT fonksiyonlarının KPI bağlamında nasıl kullanıldığını ve Türkiye'deki üretim, perakende, finans senaryolarına nasıl indiğini somut örneklerle göreceksin.

Dinamik Dizi Mantığı ve Yayılma Davranışı Aslında Neyi Değiştirdi?

Klasik Excel'de bir formül tek hücreye girilirdi; çoklu sonuç istiyorsan ya formülü aşağı kopyalardın ya da CTRL+SHIFT+ENTER ile dizi formülü yazardın. Modern dinamik dizi motoru bu mantığı tersine çevirdi: bir formül birden fazla değer döndürürse Excel bunları kendiliğinden alttaki ve yandaki boş hücrelere yayar. Bu davranışa "spill" (yayılma) deniyor.

Yayılma alanının ilk hücresi formülü taşır; geri kalan hücreler ona bağlı sonuç hücreleridir. Yayılan aralığa tek başvuruyla erişmek için hash operatörü kullanılır: A2 yerine A2# yazdığında, A2'den başlayıp aşağı doğru ne kadar yayılmışsa hepsini temsil eder. Aralık büyüyüp küçüldükçe başvuru kendiliğinden ayarlanır, formülü düzenlemek gerekmez.

Üç davranış pratikte sürekli karşımıza çıkar. #YAYIL! hatası, formülün yayılacağı hücrelerden biri doluyken görünür; engel kaldırıldığında formül kendiliğinden açılır. Tablo nesnesinin içinde yazılan dinamik dizi formülleri yayılmaz — Tablo zaten satır bazlı çalışır. Birleştirilmiş hücreler yayılmayı bozar; KPI sayfasında merge cell mümkün olduğunca azaltılır. Microsoft'un Excel ürün dokümantasyonu her dinamik dizi fonksiyonu için bu sınır durumları örnekli açıklar; iş yoğunluğunda formülü ezberden yazmak yerine bu sayfayı sık ziyaret etmek hata avına vakit harcamayı kısaltır.

FİLTRE, SIRALA, BENZERSİZ ve SIRALI Fonksiyonlarının Anatomisi

KPI hesaplamasının omurgası dört fonksiyon: filtreleme, sıralama, benzersizleştirme ve sıralı sayı üretme. İngilizce arayüzde FILTER, SORT, UNIQUE, SEQUENCE; Türkçe arayüzde FİLTRE, SIRALA, BENZERSİZ, SIRALI olarak görünür. Yanlarına SIRALAÖLÇÜT (SORTBY) ve LET eklenince paneli kurmak için yeterli alet kutusu oluşur.

• FİLTRE: =FİLTRE(dizi; dahil; [boşsa]) — Belirtilen koşulu sağlayan satırları döndürür. Örnek: =FİLTRE(Satis[Tutar]; Satis[Bolge]="Marmara")
• BENZERSİZ: =BENZERSİZ(dizi; [sütun_bazlı]; [tam_eşsiz]) — Bir aralıktaki tekrarsız değerleri çıkarır. Aktif ürün listesi, şube listesi, müşteri kategorisi üretmek için ideal.
• SIRALA: =SIRALA(dizi; [sıra_no]; [yön]) — Veriyi belirtilen sütuna göre artan/azalan sıralar. =SIRALA(KPI#; 2; -1) ikinci sütuna göre azalan.
• SIRALAÖLÇÜT: =SIRALAÖLÇÜT(dizi; ölçüt1; yön1; ...) — Dizi dışı bir kritere göre sıralama. Örnek: ürünleri ada göre değil aylık satışa göre dizmek.
• SIRALI: =SIRALI(satır; [sütun]; [başlangıç]; [adım]) — 1'den N'e kadar sayı dizisi üretir. SIRALI(12) on iki ayı, SIRALI(30) bir ay içindeki günleri verir.
• LET: =LET(ad1; değer1; ...; sonuç) — Formül içinde değişken tanımlar; aynı alt ifadeyi tekrar tekrar yazmaz, performansı düşürmez, formülü okunaklı yapar.

Bu fonksiyonların KPI bağlamındaki gücü iç içe yerleştirildiklerinde ortaya çıkıyor. =SIRALA(BENZERSİZ(FİLTRE(Satis[Urun]; Satis[Tarih]>=B1)); 1; 1) formülü tek hücreye yazılır; B1 tarihinden sonra satılan benzersiz ürünleri alfabetik sıralı olarak yayar. Yeni satış eklendiğinde liste otomatik genişler.

Türk Şirketlerinde KPI Senaryoları: Üretim, Satış, Perakende

Soyut formülün anlamı somut iş bağlamına oturduğunda netleşiyor. Türkiye'de orta ölçekli üretim, satış ve perakende ekiplerinin haftalık-aylık takip ettiği KPI başlıkları büyük ölçüde örtüşür; dinamik dizi yapısı bu başlıkların hemen hepsine uyar.

Bursa Demirtaş OSB'deki bir otomotiv yan sanayisinde üretim KPI'sı şöyle kuruluyor: vardiya başında giriş sayacı, fire oranı, hat duruş süresi, OEE (Genel Ekipman Etkinliği). Veri vardiya sonu Excel'e düşer; KPI sayfası dinamik dizilerle özet üretir.

1. Aktif iş emirleri: =BENZERSİZ(FİLTRE(Uretim[IsEmri]; Uretim[Durum]="Acik"))
2. Vardiya bazlı fire oranı: =ÇOKETOPLA(Uretim[Fire]; Uretim[Vardiya]; D2#) / ÇOKETOPLA(Uretim[Uretim_Miktari]; Uretim[Vardiya]; D2#)
3. OEE eşik altı vardiyalar: =FİLTRE(OEE#; OEE_Oran#<0,75; "Tüm vardiyalar hedef üzerinde")
4. En yüksek 5 fire kalemi: =İNDİS(SIRALAÖLÇÜT(Fire#; Tutar#; -1); SIRALI(5))

Konya'da bir tarım makineleri üreticisi, bölge bayilerinden gelen satış verilerini benzer mantıkla izliyor. Marmara, Ege, İç Anadolu, Akdeniz bölgeleri için aylık ciro, kalem adet, ortalama sepet tutarı hesaplanıyor. =BENZERSİZ(Satis[Bolge]) bölge listesini, yanına yerleştirilen =ÇOKETOPLA(Satis[Tutar]; Satis[Bolge]; H2#) bölge bazlı toplamı, üçüncü sütundaki =H2#/TOPLA(Satis[Tutar]) payı verir. Yeni bayi açıldığında veya bir bölge listeden düştüğünde formüller dokunulmadan güncellenir.

Perakende tarafında, İstanbul'da bir hızlı tüketim zinciri haftalık SKU performansı için aynı yapıyı kullanıyor. Hedefe karşı gerçekleşme, kategori bazlı katkı, mağaza karşılaştırması — hepsi tek bir KPI sayfasında. Düşük performans gösteren mağazaları otomatik yakalamak için: =FİLTRE(Magaza[Ad]; (Magaza[Gerceklesme]/Magaza[Hedef])<0,8). Sonuç sıfır mağaza olabilir, üç mağaza olabilir; formül aynı kalır, panel kendini ayarlar. Sistematik KPI kurma akışını saha senaryolarıyla öğrenmek isteyenler için Excel eğitimi dinamik dizi-pivot-Power Query üçlüsünü birlikte aktarır.

Tek Hücrede KPI Bandı Nasıl Kurulur?

KPI panelinin üst bandı genellikle dört-altı kart içerir: bu ay toplam satış, geçen aya göre değişim, hedef gerçekleşme oranı, en iyi ürün, en iyi bölge, kritik uyarı. Klasik yöntemde her kart için ayrı ÇOKETOPLA, ayrı EĞER zinciri yazılırdı. Dinamik dizilerle bant tek bir LET formülüyle kurulabilir.

Örneğin "bu ay en çok satan beş ürünü tutar bilgisiyle göster" kartı:

=LET( bu_ay; FİLTRE(Satis; AY(Satis[Tarih])=AY(BUGÜN())); urunler; BENZERSİZ(İNDİS(bu_ay;;2)); tutarlar; ÇOKETOPLA(Satis[Tutar]; Satis[Urun]; urunler); top5; SIRALAÖLÇÜT(urunler; tutarlar; -1); İNDİS(top5; SIRALI(5)) )

LET burada üç şeyi birden çözüyor. Alt ifadeleri (bu_ay, urunler, tutarlar) tek seferde hesaplıyor — aynı FİLTRE'yi formül içinde dört kez tekrar etmiyorsun. Formül okunaklı kalıyor; üç ay sonra geri döndüğünde "burada ne yapmıştım" sorusuna cevap veriyor. Excel'in hesaplama motoru ifadeyi bir kez çalıştırıyor, dosya performansı korunuyor.

Geçen aya göre değişim kartı şöyle: =LET( bu; ÇOKETOPLA(Satis[Tutar]; Satis[Ay]; AY(BUGÜN())); onceki; ÇOKETOPLA(Satis[Tutar]; Satis[Ay]; AY(BUGÜN())-1); (bu-onceki)/onceki ). Sonucu yüzde formatına çevir, koşullu biçimlendirme ile +%5'in üzerini yeşil, -%5'in altını kırmızı boya. Kartın sayısal ve görsel katmanı tek formül + bir biçimlendirme kuralıyla tamam.

Spill Range ve # Operatörünün Pratik Faydası Nerede?

Yayılma aralığını tek başvuruyla temsil etmek dinamik dizi mimarisinin asıl güzelliği. Bir BENZERSİZ formülü H2'de açılıp H2:H47'ye kadar yayıldıysa, H2# yazdığında tam o aralığı alırsın. Yeni veri eklendiğinde H2:H52'ye genişlerse H2# başvurusu kendiliğinden büyür.

Bu davranışın pratik faydası üç noktada öne çıkar. Grafiklerde: Pivot grafiğin alternatifi olarak normal grafiğin veri aralığına H2# yazdığında, yeni kategori eklendiğinde grafik kendi ekseninde genişler. Veri doğrulama listesinde: Açılır liste kaynağına =Sayfa1!H2# yazdığında dropdown otomatik genişler — yeni şube açıldığında listeye elle eklemen gerekmez. İç içe formüllerde: KPI sayfasında BENZERSİZ üretip yanına ÇOKETOPLA yapıştırdığında, ölçüt aralığını H2# verirsin; bölge listesi büyüdükçe toplam satırları da büyür.

Klasik yöntemle karşılaştırma şu tabloda yoğunlaşıyor:

Yapılandırılmış başvuru (örn. Satis[Tutar]) dinamik dizilerin kardeş yapısı. Tablo nesnesi (CTRL+T) içinde tutulan veri, dinamik dizi formülünün gördüğü her hücreyi otomatik tarar. Birlikte kullanıldıklarında "KPI panelimi 6 ayda bir yeniden kurmak zorunda kalıyorum" şikayetinin çözümü kendiliğinden gelir.

Finans ve İK Tarafında Dinamik Dizi: Nakit, Bütçe, Bordro KPI'ları

Üretim ve satış dışında dinamik dizilerin değdiği iki alan finans ve insan kaynakları. Türkiye'deki bağımsız mali müşavir bürolarının ve KOBİ finans departmanlarının aylık raporlama yoğunluğu, dinamik dizilerin sağladığı zaman tasarrufuna en açık alanlardan biri.

Nakit akış KPI sayfası tipik olarak şu kartları taşır: ay sonu tahmini bakiye, son 30 gündeki giriş-çıkış, müşteri bazlı alacak yaşlandırması, vade geçmiş borçlar. =FİLTRE(Cari[Bakiye]; (Cari[Vade]<BUGÜN())*(Cari[Tip]="Alacak")) formülü vade geçmiş alacakları tek hücrede yayar; toplamı =TOPLA(O2#) ile alırsın. Yaşlandırma analizi için: =SIRALAÖLÇÜT(FİLTRE(Cari; Cari[Tip]="Alacak"); FİLTRE(Cari[Vade]; Cari[Tip]="Alacak"); 1) — vadeye göre artan sıralı liste çıkar.

Bütçe gerçekleşme KPI'sı için ay-kategori matris tablosu kuruluyor. =BENZERSİZ(Butce[Kategori]) bir sütunda, üst satırda =SIRALI(1; 12) ile 12 ay yayılır; ortadaki matris hücresine =ÇOKETOPLA(Gerc[Tutar]; Gerc[Kategori]; T2#; Gerc[Ay]; U1#) yazdığında çapraz matris kendiliğinden dolar. Yeni kategori veya yeni gerçekleşme satırı eklendiğinde matris büyür.

İK tarafında bordro icmali ve performans takibi dinamik dizilerle kuruluyor. Departman bazlı ortalama prim, kıdem yılına göre devamsızlık oranı, performans skoru üst yüzde 10'a giren çalışanların listesi — hepsi tek sayfada. =FİLTRE(Personel[Ad]; Personel[Performans]>PERSENTİL(Personel[Performans]; 0,9)) formülü performans skoru üst %10'a giren çalışan adlarını yayar. Listenin uzunluğu değişebilir; formülün kendisi sabit kalır. SGK ve gelir vergisi tarifelerine bağlı bordro KPI'larında ise =EĞERÇOK(...) dilim tablosu ile birleşince hesap motoru tam çalışır hale gelir.

Performans, Bakım ve Yaygın Hatalar Nelere Dikkat Etmeli?

Dinamik dizi yapısı bedava değil; yanlış kurgulanırsa dosya yavaşlar veya formül beklenmedik yerlerde patlar. Tecrübeyle öğrenilen birkaç pratik kural performans ve sürdürülebilirliği güvende tutar.

• Tam sütun başvurusu yasak: FİLTRE(A:A; ...) milyon satır tarar, dosya kilitlenebilir. Tablo nesnesi veya tanımlı isim kullan; aralık sadece gerçek veriyi içersin.
• Oynak fonksiyonlardan kaçın: BUGÜN, ŞİMDİ, DOLAYLI, KAYDIR her hesaplamada yeniden tetiklenir. KPI bandında BUGÜN sıklıkla geçer; sayısı arttıkça dosya ağırlaşır. Mümkün yerde sabit tarih hücresi referansla.
• Birden fazla LET bağlı formülü ayır: Aynı LET'i 10 hücrede tekrar etmektense bir hücrede sonucu üret, diğerlerinden # ile başvur. Hesaplama bir kez çalışır.
• Yayılma engellerini izle: Formülün altındaki hücrede manuel girilmiş bir not varsa #YAYIL! verir. KPI sayfasında "boş bırakılması gereken alanı" görsel olarak işaretle (açık gri arka plan).
• Eski sürüm açacaklara dikkat: Bir paydaş dosyayı dinamik dizi desteklemeyen bir sürümde açtığında formüller _xlfn. önekiyle hata verir. Paylaşım öncesi hedef sürümü sor; gerekirse sonuçları değer olarak yapıştır.
• Power Query ile birleşim: Çok büyük ham veri (yüz binlerce satır) için dinamik dizi yerine Power Query + Veri Modeli daha hızlı. KPI ekranı dinamik dizi, arkadaki veri hazırlığı Power Query iyi bir bölünme.

Yaygın hatalar genelde tek bir yere konsantre. Dolar işareti yerine yapılandırılmış başvuru kullanmamak, yayılan aralığın altını "boş bırakacak alan" olarak işaretlememek, LET'in içine LET nestlemek (okunabilirlik bozulur), aynı kaynak veriyi farklı sayfalarda iki ayrı Tablo'ya bölmek. Bu dördünden kaçınmak çoğu KPI dosyasını sağlıklı bir bakım hattında tutar.

Modern Excel'deki dinamik dizi motoru tek bir özellik değil, raporlama disiplinin yeniden düşünüldüğü bir geçiş. Tek formülün hücreden hücreye yayıldığını ve panelin canlı kaldığını ilk gördüğünde, "bu sayfayı her ay baştan kuruyordum" alışkanlığı hızla geride kalır.