- Dinamik dizi fonksiyonları nedir?
- FILTER: koşula uyan satırları ayırmak
- UNIQUE: benzersiz değerleri çıkarmak
- SORT ve SORTBY: sıralamak
- Spill davranışı, hata türleri ve sınırlar
- Örnek uygulama: dinamik bölge raporu
- Sık yapılan hatalar ve ne zaman başka araç gerekir?
- Bu beceriyi Mindset programlarında nerede çalışırsınız?
- Sık sorulan sorular
Excel'de Dinamik Dizi Fonksiyonlarıyla Canlı Listeler ve Raporlar
Excel'de dinamik dizi fonksiyonlarıyla FILTER, UNIQUE ve SORT kullanımını, spill davranışını ve canlı rapor örneğini adım adım öğrenin.
Dinamik dizi fonksiyonları nedir?
Klasik Excel formülü bir hücreye tek bir sonuç döndürür. Dinamik dizi fonksiyonları ise tek bir formülden birden çok değer üretir ve sonuçları yan yana ya da alt alta komşu hücrelere kendiliğinden yayar. Bu davranışa İngilizce kaynaklarda spill denir. Formülü yalnızca ilk hücreye yazarsınız; Excel sonuç kadar hücreyi doldurur, veri değiştikçe sonuç alanı büyür ya da küçülür.
Bu yaklaşım, daha önce karmaşık dizi formülleri, yardımcı sütunlar ya da makrolar gerektiren işleri sadeleştirir. Verideki benzersiz değerleri çıkarmak, belirli bir koşulu sağlayan satırları ayrı bir listeye almak ya da bir listeyi sıralamak artık tek bir formülle yapılabilir. Fonksiyonlar yalnızca dinamik dizi desteği bulunan güncel Excel sürümlerinde çalışır; eski sürümlerle paylaşılan dosyalarda uyumluluk ayrıca denetlenmelidir.
FILTER: koşula uyan satırları ayırmak
FILTER fonksiyonu, bir veri aralığından belirli bir koşulu sağlayan satırları döndürür. Ana parçaları şunlardır: filtrelenecek aralık, koşul ve koşul sağlanmazsa gösterilecek değer. Örneğin bir sipariş tablosundan yalnızca bölgesi "Ege" olan kayıtları ayrı bir alana çıkarmak için formülde tablonun tamamını, bölge sütununun "Ege" olduğu koşulu ve eşleşme yoksa yazılacak bir "Kayıt yok" metnini verirsiniz.
Koşullar birleştirilebilir. Koşulların birlikte sağlanması (ve) çarpım ile, en az birinin sağlanması (veya) toplama ile ifade edilir. Örneğin bölgesi Ege olan ve tutarı eşiğin üstünde olan siparişleri tek formülle listeleyebilirsiniz. Koşulu bir hücreye bağlarsanız (örneğin açılır listeden seçilen bölge), kullanıcı seçimini değiştirdikçe liste canlı güncellenen bir rapora dönüşür.
UNIQUE: benzersiz değerleri çıkarmak
UNIQUE, bir listedeki yinelenen değerleri ayıklayıp her değerin tek örneğini döndürür. Müşteri adı sütunundan benzersiz müşteri listesi çıkarmak, bir satış tablosundan ürün kategorilerini listelemek ya da veri tablosundaki farklı kod değerlerini görmek tipik kullanımlardır. İsteğe bağlı bir ayarla yalnızca bir kez geçen değerleri döndürmek de mümkündür; bu, tek seferlik müşterileri ayırmak gibi analizlerde işe yarar.
UNIQUE çıktısı, başka formüllerin girdisi olarak kullanılabilir. Örneğin benzersiz müşteri listesini bir hücreden başlatıp yanına her müşterinin toplam satışını getiren bir formül yazarsanız, ek kopyalama yapmadan canlı bir özet tablo elde edersiniz. Metin değerlerinde fazladan boşluklar veya büyük küçük harf farkı olabileceği için, önce veri temizliği yapmak gerekir; yoksa görünürde aynı olan değerler ayrı sayılabilir.
SORT ve SORTBY: sıralamak
SORT fonksiyonu bir aralığı belirttiğiniz sütuna göre artan ya da azalan sıralar. Asıl gücü, orijinal veriyi değiştirmemesi ve sonucun canlı kalmasıdır: kaynak tabloya yeni kayıt eklendiğinde sıralı liste kendiliğinden güncellenir. SORTBY ise sıralama ölçütü olarak çıktı aralığında olmayan bir sütunu kullanmanıza izin verir.
Fonksiyonlar iç içe kullanıldığında asıl verim ortaya çıkar. Örneğin benzersiz müşterileri tek formülle çıkarıp sıralamak, ya da koşula uyan kayıtları filtreleyip tutara göre büyükten küçüğe dizmek mümkündür. Bu iç içe formüller, "en çok satış yapılan müşteriler" gibi dinamik listeler üretir. Formüller karmaşıklaştıkça okunabilirlik düşer; LET gibi adlandırma yardımcıları ve açıklayıcı notlar bakım kolaylığı sağlar.
Spill davranışı, hata türleri ve sınırlar
Sonuç alanı, formülün bulunduğu hücreden başlayan taşma bölgesidir. Bu bölgede başka veri bulunuyorsa Excel spill hatası verir; çözüm, sonuç alanındaki engeli temizlemek ya da formülü boş bir alana taşımaktır. Sonuç alanındaki hücrelere tek tek yazıp değiştirmek mümkün değildir: değişiklik formülün kendisi üzerinde yapılır.
Spill sonucuna başka formüllerden başvurmak için taşma aralığı işlecini kullanırsınız; bu, sonuç alanı büyüse de küçülse de başvurunun doğru kalmasını sağlar. Grafik, koşullu biçimlendirme ve veri doğrulama gibi araçlar da dinamik sonuç alanına başvurarak canlı çalışabilir. Sınır olarak dikkat edilecek nokta, Excel tablolarının (listelerin) içinde dinamik dizi formülü yazılamamasıdır; formül tablonun dışında bir alanda kurulmalıdır.
Örnek uygulama: dinamik bölge raporu
Örneğin bir satış tablosunda tarih, bölge, müşteri, ürün ve tutar sütunları bulunur. Raporu sunacak kişi bir ayarlar hücresinde açılır listeden bölgeyi seçer. Yanındaki alanda FILTER formülü, bu bölgenin kayıtlarını tutara göre SORT ile sıralanmış şekilde listeler. Başka bir alanda UNIQUE, seçili bölgedeki benzersiz müşteri listesini üretir; yanına toplam satışı getiren bir toplama formülü eklenir.
Bölge değiştirildiğinde tüm rapor kendiliğinden yenilenir; yardımcı sütun, kopyalama ya da makro gerekmez. Aynı yapı, veri doğrulama ile oluşturulan seçim hücresi ve grafikle birleştirildiğinde etkileşimli bir özet panele dönüşür. Kaynak tabloya yeni satır eklendiğinde rapor da güncellenir; bu yüzden kaynağı Excel tablosu olarak tutmak önerilir.
Sık yapılan hatalar ve ne zaman başka araç gerekir?
Başlangıçta sık karşılaşılan sorunlar: sonuç alanında engel olması (spill hatası), eski sürümde dosyanın açılması ve formüllerin bozulması, boşluk ya da büyük küçük harf farkı nedeniyle aynı görünen değerlerin ayrı sayılması ve koşulda mantık hatası yapmak (ve ile veya karışıklığı). Ayrıca çok büyük veri kümelerinde ağır iç içe formüller dosyayı yavaşlatabilir.
Dinamik diziler Excel'in veri işleme gücünü artırır, ancak binlerce kaynaktan veri birleştirme, tekrar eden veri hazırlama adımları ya da çok büyük veri kümeleri için Power Query gibi veri dönüştürme araçları, ilişkisel veri modelleri veya veri tabanı sorguları daha uygun olabilir. Seçim, işin tekrarlanma sıklığına ve veri hacmine bağlıdır.
Bu beceriyi Mindset programlarında nerede çalışırsınız?
Microsoft Excel ile Veri Analizi Eğitimi - İleri Excel (Uygulamalı) programında Veri Analizi ve İleri Formüller ile İleri Seviye İşlevler ve İleri Tablo Yönetimi modülleri formül tarafını kapsar; ayrıca veri tabloları ve PivotTabloları ile ileri veri analizi araçları modülleri bulunur.
Excel Orta Düzey programında İleri Arama ve Basit Referans Formülleri, Koşullu Fonksiyonlar ve Çok Katmanlı Hesaplamalar, Veri Temizleme ve Düzenli Veri Yapısı ile Pivot Tablo Ustalığı modülleri dinamik dizi fonksiyonlarına zemin hazırlar.
İlgili Mindset programı
Microsoft Excel ile Veri Analizi Eğitimi - İleri Excel (Uygulamalı)
Sık sorulan sorular
Hangi Excel sürümlerinde dinamik dizi fonksiyonları vardır?
Dinamik dizi fonksiyonları Microsoft 365 aboneliğindeki güncel Excel sürümlerinde ve yeni masaüstü sürümlerinin bir kısmında bulunur; eski sürümlerde yoktur. Dosyayı eski sürüm kullanan biriyle paylaşacaksanız formüllerin bozulabileceğini, mümkünse sonuçların değer olarak da sunulması gerektiğini göz önünde bulundurun.
FILTER fonksiyonunda birden fazla koşul nasıl kullanılır?
Ve koşulu için koşul ifadeleri çarpılır, veya koşulu için toplanır. Her koşul ayrı parantez içinde yazılır. Örneğin hem bölgesi Ege olan hem de tutarı belirli bir eşiğin üstünde olan satırlar çarpım yoluyla süzülür. Karmaşık koşullarda önce yardımcı bir hücrede her koşulu ayrı sınayıp sonra birleştirmek hata ayıklamayı kolaylaştırır.
Spill hatası nedir, nasıl çözülür?
Formülün sonuç alanında başka veri varsa Excel sonucu yayamaz ve spill hatası verir. Engel olan hücreleri temizlemek ya da formülü boş bir alana taşımak çözümdür. Excel tablosu içinde dinamik dizi formülü yazmaya çalışmak da benzer bir hata üretir; bu durumda formül tablonun dışında kurulmalıdır.
UNIQUE ile pivot tablo arasında ne fark var?
UNIQUE yalnızca benzersiz değerleri listeler; toplama, ortalama gibi özetleri formülle yanına eklemeniz gerekir. Pivot tablo ise gruplama ve toplamı hazır sunar, sürükle-bırak ile görünüm değiştirir. Formül tabanlı dinamik raporlama esneklik verir, pivot tablo hız verir; çoğu zaman bunlar birlikte kullanılır.
Eski dizi formüllerinden dinamik dizilere geçmek gerekli mi?
Zorunlu değildir; eski formüller çalışmaya devam eder. Ancak yeni dosyalarda dinamik diziler daha okunabilir ve bakımı kolaydır. Dosya eski sürüm kullanan kişilerle paylaşılıyorsa mevcut yapıyı korumak, yeni araçları ayrı bir sürümde denemek akıllıca olur.
İlgili rehberler
İlgili eğitimler
Kategori: Veri Analitiği ve İş Zekası