Farklı Sayfalardan Formülle Veri Alma

Excel Verilerini Üç Ölçütle Ayrı Bir Rapor Sayfasında Listeleyin​

Farklı Sayfalardan Formülle Veri Alma çalışma kitabı, bir sayfada tutulan kayıtların başka bir sayfada koşullara göre listelenmesini örnekler. Kaynak kayıtlar veri sayfasında, seçim alanları ve sonuçlar rapor sayfasındadır. Raporu belirleyen üç alan şehir, gerçekleşme durumu ve durum kodudur. Aynı kaydın bu üç koşulu birlikte karşılaması gerekir. Sonuç olarak kaynak listenin tüm sütunları kopyalanmaz; rapor için belirlenen altı bilgi alanı seçilir. Listeleme hücre formülleriyle yapılır; çalışma kitabında bu işlemi yürüten bir VBA makrosu bulunmaz. Dinamik dizi formüllerinin desteklendiği Excel sürümünde sonuç alanı eşleşme sayısına göre genişler.

Kaynak Listenin Düzeni​

veri sayfasında dokuz sütun vardır: S.NO, ŞEHİR, İŞ NO, PERSONEL, CİNSİYET, ÇAĞRI TARİHİ, İLÇE, DURUM ve D.KODU. Başlıklar birinci satırda bulunur. Sonraki otuz satır, farklı şehirler, personel adları, tarihler ve durum bilgileri içeren örnek kayıtları taşır. Sıra numaraları ise sonraki boş veri alanları için de hazırlanmıştır. Sayfa üzerinde toplam 386 sıra numarası bulunması, 386 eksiksiz örnek kayıt bulunduğu anlamına gelmez. Mevcut örneklerin bilgi alanları ikinci satırdan otuz birinci satıra kadar doldurulmuştur; devamındaki satırlarda yalnız sıra numaraları vardır.
Şehir alanı, raporun bölge olarak etiketlenen seçim alanına karşılık gelir. Durum alanında GERÇEKLEŞTİ ve GERÇEKLEŞMEDİ değerleri kullanılır. D.KODU, A veya B gibi kodları taşır ve üçüncü eşleşme ölçütüdür. Personelin cinsiyeti ve ilçesi kaynak listede yer almasına rağmen sonuç raporuna alınan sütunlar arasında değildir. Bu alanların dosyada bulunması, raporun bunlara göre de filtreleme yaptığı anlamına gelmez. Mevcut formülün karar verdiği alanlar şehir, durum ve koddur; diğer bilgiler seçilen kaydın içerik alanlarıdır.

Rapor Sayfasındaki Seçim ve Sonuç Alanları​

rapor sayfasında D2 durum seçimini, D3 şehir seçimini, D4 kod seçimini taşır. Bu hücrelerin yanında DURUM SEÇ, BÖLGE ve D.KODU etiketleri yer alır. Başlangıç değerleri sırasıyla GERÇEKLEŞTİ, ANKARA ve A olarak hazırlanmıştır. Bu birleşim üç örnek kaydı getirir. Sonuç tablosunda S.NO, ŞEHİR, İŞ NO, PERSONEL, ÇAĞRI TARİHİ, DURUM ve D.KODU başlıkları vardır. Kaynak sıra numarası yerine, filtrelenen sonuçların kendi sıra numaraları gösterilir. Bu nedenle kaynak listedeki dokuzuncu kayıt sonuçta birinci sırada bulunabilir.
Başlangıç raporunda Ankara şehrine ait 2022/09, 2022/24 ve 2022/28 iş numaraları görülür. Üçünde de durum GERÇEKLEŞTİ ve kod A olduğundan aynı sonuç listesine girerler. Kaynak listede aynı şehre ait başka durum veya kod taşıyan kayıtlar bulunması, bunların otomatik olarak rapora dahil edilmesini sağlamaz. Raporun bir şehir özeti olarak kullanılabilmesi için seçilen diğer iki ölçütün kapsamı da dikkate alınır. Dosyada tüm durumları kapsayan özel bir Tümü seçeneği veya boş ölçütü yok sayan bir koşul tanımlanmamıştır.

FİLTRE ve ELEMAN Formüllerinin Görevi​

Asıl listeleme rapor sayfasının C7 hücresindeki dinamik dizi formülünden başlar. FİLTRE işlevi, kaynak satırların koşulları sağlayıp sağlamadığını değerlendirir. Veri sayfasının şehir sütunu D3 ile, durum sütunu D2 ile, kod sütunu D4 ile karşılaştırılır. Bu üç karşılaştırmanın çarpımı, bütün koşulların birlikte sağlanmasını ifade eder. Yalnız bir koşulun eşleşmesi yeterli değildir. Bu mantık, şehirden bağımsız bir durum listesi veya durumdan bağımsız bir şehir listesi hazırlamaktan farklıdır; sonuç seçilen üçlüye tam olarak bağlıdır.
ELEMAN işlevi, kaynak verinin rapora alınacak sütunlarını bir araya getirir. Seçilen alanlar şehir, iş numarası, personel, çağrı tarihi, durum ve koddur. Kaynak sütunlarının sırası korunarak altı sütunlu bir sonuç üretilir. Cinsiyet ve ilçe sütunlarının atlanması bu sütun seçiminden kaynaklanır. Formülde veri aralıkları ikinci satırdan Excel sayfasının son satırına kadar uzanır. Dolayısıyla mevcut otuz örneğin altına uygun alanları doldurulmuş yeni kayıtlar eklendiğinde, filtre aralığı yalnız mevcut örneklerle sınırlı kalmaz. Ancak iş numarası gibi alanların yeni kayıt için ayrıca hazırlanması gerekir.

Sıralı Sonuç Numaralarının Oluşması​

Sonuçların solundaki sıra numaraları B7 hücresindeki SIRALI formülüyle oluşturulur. Formül, C sütunundaki dolu sonuç sayısını BAĞ_DEĞ_DOLU_SAY işleviyle değerlendirir ve birden başlayan ardışık sayılar üretir. Bu numaralar kaynak S.NO değerlerinin aynısı değildir; raporda kaçıncı sonucun gösterildiğini belirtir. Ölçütler değiştirildiğinde sonuç sayısı da değişirse sıra numarası dizisinin uzunluğu buna göre değişir. Böylece üç kayıt getiren bir seçim ile tek kayıt getiren bir seçim aynı sayıda numaralı satır taşımak zorunda değildir.
Eşleşme bulunmadığında FİLTRE formülü Veri Bulunamadı metnini verir. Bu metin C7 hücresinde bir dolu değer olduğundan, mevcut sıra numarası formülü yanında 1 gösterebilir. Bu durum bir kayıt bulunduğunu belirtmez; sonuç hücresindeki açıklama metninin sayılmasıdır. Rapor değerlendirilirken yalnız sıra numarasına bakmak yerine şehir alanındaki metin de okunur. Boş bir şehir veya geçersiz bir ölçüt birleşimi tüm kayıtları listeleyen serbest seçim olarak çalışmaz. Kaynak veride karşılığı olmayan birleşimler için bu boş sonuç açıklaması kullanılır.

İş Numaralarının Hesaplanması​

veri sayfasındaki C2:C31 hücreleri, çağrı tarihinin yılı ile sıra numarasını birleştirir. YIL işlevi F sütunundaki tarihten yılı alır. Sayısal sıra numarasının uzunluğu UZUNLUK ile kontrol edilir. Tek basamaklı numara için başa sıfır eklenir; daha uzun numaralar kendi değeriyle kullanılır. EĞER ve BİRLEŞTİR işlevlerinin bu kullanımı, örneğin dokuzuncu kayıt için 2022/09 biçimini oluşturur. Tarih ve sıra numarası değiştirildiğinde hesaplanan iş numarası da bunlara bağlı olarak değişir.
Bu formül genel bir benzersiz kayıt denetimi değildir. Aynı yıl ve aynı sıra numarası birden fazla satırda kullanılırsa kendiliğinden başka bir kimlik oluşturmaz. Örnekte iki haneli görünüm hedeflenmekle birlikte üç basamaklı sıra numarası kesilmez. Yeni kayıt satırlarına bilgi eklenirken C sütunundaki formülün de ilgili satıra uyarlanması gerekir; çalışma kitabında bu alan bir Excel tablosunun otomatik hesaplanan sütunu olarak tanımlanmamıştır. İş numarası alanının boş bırakılması, filtrelenen kaydın diğer alanları mevcut olsa bile rapordaki bu bilginin eksik görünmesine neden olabilir.

Gizli Ölçüt Listesi ve Dosyanın Kullanımı​

Sayfa1 adlı üçüncü çalışma sayfası gizlidir ve seçim listelerinin yardımcı değerlerini içerir. Bu sayfada iki durum, sekiz şehir ve dört kod değeri vardır. Kaynak veride E kodu ve sekiz şehirlik örnek seçim listesinin dışında kalan şehirler de vardır; sabit yardımcı listeler bütün kaynak değerlerinden otomatik oluşmaz ve bu değerlerin açılır listede sunulması için gizli yardımcı liste ile doğrulama aralığının elle düzenlenmesi gerekir. Bu listeleme alanı yeni kayıtların kendisi değildir; raporda seçilebilen değerlerin kaynağıdır. Şehir veya kod çeşitliliği değiştirilirken hem kaynak kayıtların hem seçim listelerinin birbiriyle uyumu değerlendirilir. Açılır listede bir değer bulunması, veri sayfasında o değerle eşleşen bir kayıt bulunduğunu garanti etmez. Sonuç yine üç koşulun birlikte değerlendirilmesiyle belirlenir.
Kullanım için önce veri sayfasındaki kayıtlar incelenir, ardından rapor sayfasındaki üç ölçüt belirlenir. Sonuçlar altı bilgi sütunu ve ayrı sıra numarası alanında okunur. Dinamik sonuç alanına elle ek bilgi yazılması, formülün genişleyeceği hücreleri kapatabilir; bu alan rapor çıktısı olarak kullanılır. Kaynak listede bir kişinin adı veya çağrı tarihi değiştirilirse rapora giren kayıt güncel bilgiyi taşır. Dosya içinde Power Query sorgusu, dış çalışma kitabına bağlantı veya makro çalıştırma adımı bulunmaz. Hesaplama, aynı dosyanın sayfaları arasındaki hücre başvurularıyla yürütülür.

Dosyanın Uygun Olduğu Çalışmalar​

Bu örnek, kayıtları şehir, gerçekleşme durumu ve kod gibi birlikte kullanılacak alanlarla ayırmak isteyen çalışmalar için uygundur. Kaynak listenin bilgi sütunlarını rapor düzeninden ayırması, birden fazla sonuç getiren formülün mantığını görmeyi sağlar. Sabit bir arama sonucundan farklı olarak aynı ölçüt birleşimine uyan bütün kayıtlar listelenir. Üç alanın birlikte kullanılması, aynı şehrin farklı durum ve kodlarını birbirine karıştırmadan incelemeye yardımcı olur. Dosyanın amacı grafik, toplam ödeme veya ilçe dağılımı üretmek değil, seçilmiş kayıt alanlarını ayrı sayfaya formülle aktarmaktır.

Kod Seçim Listesindeki Sürüm Düzeltmesi​

Güncellenmiş sürümde rapor sayfasının D4 hücresindeki açılır liste düzeltilmiştir. Önceki dosyada şehir listesinin doğrulama alanı D3 ile D4 hücrelerini birlikte kapsadığı için, kod alanında şehir seçenekleri görülebilmiştir. D4 alanı artık gizli Sayfa1 sayfasındaki C2:C5 kod listesini kullanır. D2 durum listesi ve D3 şehir listesi korunmuştur. Bu değişiklik filtre formülüne yeni koşul eklemez ve sonuçların sütun sırasını değiştirmez; mevcut üç ölçütün doğru değerlerle seçilebilmesini sağlar. Dinamik dizi formülleri, iş numarası hesaplamaları, gizli yardımcı sayfa ve tam sütun veri aralıkları korunur.