DÜŞEYARA ile Birden Fazla Sonuç Getirme

Aynı Kayda Ait Birden Fazla Değeri Excel Hücresinde Birleştirin​

Bir sicil numarası kaynak listede birden fazla satırda bulunuyorsa, aranan kayda ait bütün adların birlikte görülmesi gerekebilir. Bu çalışma kitabı, aynı arama değerine karşılık gelen sonuçları tek hücrede birleştiren kullanıcı tanımlı bir Excel işlevini örnekler. Dosyanın başlığında DÜŞEYARA ifadesi bulunmakla birlikte hesaplama, Excel'in standart DÜŞEYARA işlevinin kendisiyle yapılmaz. ÇokSonuçluDüşeyara adlı VBA işlevi kaynak hücreleri tarar, eşleşen konumlardaki sonuçları alır ve yinelenen sonuç metinlerini tekilleştirerek belirlenen ayraçla birleştirir. Bu nedenle dosya, hem çok sonuçlu aramanın hem de çalışma sayfasından çağrılan özel VBA işlevinin somut bir örneğidir.

Tek Sayfalık Örnek Veri Düzeni​

Çalışma kitabında Sayfa1 adlı tek görünür sayfa bulunmaktadır. B2 hücresinde Sicil, C2 hücresinde Çocuk Adı, D2 hücresinde Çocuk Soyadı başlıkları yer alır. Örnek veriler B3:D8 aralığındaki altı satırda bulunmaktadır. Sicil sütununda 100 ve 200 değerleri tekrar etmektedir. 100 siciline ait adlar Ali, Hasan, Hale ve Cemal; 200 siciline ait adlar Ahmet ve Selin olarak düzenlenmiştir. Soyadları ayrı sütunda tutulmaktadır. Bu düzen, bir arama anahtarının kaynak tabloda birden fazla defa bulunabildiğini göstermek için hazırlanmıştır. Veri, gerçek bir personel yönetim sisteminin tamamını değil, arama işlevinin anlaşılmasını sağlayan küçük bir örneği temsil eder.
Sayfanın sağında F2 hücresindeki Aranan başlığı ve G2 hücresindeki Sonuçlar başlığı bulunmaktadır. F3 hücresi arama girdisidir. G3 hücresindeki formül ise bu girdiyi kullanarak sonuç metnini üretir. Başlangıç örneğinde F3 değeri 100 olduğunda G3 hücresinde Ali;Hasan;Hale;Cemal metni görülür. Sicil değeri 200 olarak değiştirildiğinde Ahmet;Selin sonucuna ulaşılır. Kaynak listede bulunmayan 999 gibi bir değer arandığında Sonuç Yok metni döner. Sonuçların ayrı satırlara taşması veya yeni bir sonuç sayfasında listelenmesi söz konusu değildir; işlev çıktısı tek bir metin hücresidir.

Özel İşlevin Dört Parametresi​

ÇokSonuçluDüşeyara işlevi dört parametre alır: aranan değer, taranacak veri aralığı, sonuçların başlangıç aralığı ve ayraç. Örnekte aranan değer F3 hücresinden alınır. Veri aralığı B3:D8 olarak verilmiştir. Sonuç başlangıcı C3:C8 aralığıdır. Ayraç olarak noktalı virgül kullanılır. Bu dört parametre, aynı işlevin başka bir veri düzeninde kullanılabilmesini sağlar; ancak kaynak ve sonuç başvurularının konumsal ilişkisi doğru kurulmalıdır. Formülde yalnızca arama hücresinin değiştirilmesiyle kaynak tablonun otomatik genişleyeceği varsayılmamalıdır. Yeni satırlar eklendiğinde formül başvurularının da uygun biçimde güncellenmesi gerekir.
Örnekte tarama aralığı yalnızca sicil sütunu değildir; B3:D8 içindeki bütün hücreler değerlendirilir. Bu ayrıntı, metin aramalarında sonucu etkiler. İşlev bir eşleşme bulduğunda, sonuç aralığının başlangıcından aynı satır ve sütun uzaklığına gider. Sicil B sütununda eşleştiğinde C sütunundaki ad alınır. Örneğin Ali adı C sütununda arandığında, aynı sütun uzaklığı nedeniyle D sütunundaki Veli sonucu alınır. Bu davranış mevcut işlevin iki boyutlu tarama ve konumsal kaydırma mantığıdır. Yalnızca sicil üzerinde arama yapılması istenirse veri aralığı B3:B8 olarak daraltılabilir; böylece ad ve soyadı hücreleri arama alanına girmez.

Tekilleştirme ve Sonuçların Sırası​

İşlev kaynak aralığı üst satırdan alt satıra, her satır içinde soldan sağa tarar. Eşleşen sonuçlar bu karşılaşma sırasıyla biriktirilir. Aynı sonuç metni tekrar bulunduğunda, çıktı listesine ikinci defa eklenmez. Böylece kaynak tabloda aynı adın aynı arama anahtarı altında birden fazla kaydı olsa bile sonuç hücresinde tek bir örneği bulunur. Bu tekilleştirme, kaynak verileri silmez veya satırları birleştirmez; yalnızca üretilen metnin içindeki yinelenen değerleri sınırlar. Kaynak listenin sırası değiştirilirse ilk karşılaşma sırası da değişebilir ve birleşik metindeki değerlerin sırası buna göre farklılaşabilir.
Sonuç metinlerinin tekilleştirilmesinde ikili metin karşılaştırması kullanılır. Bu nedenle A ve a gibi farklı büyük ve küçük harf biçimleri ayrı sonuçlar olarak korunur. İşlev sonuçların başındaki veya sonundaki boşlukları kendiliğinden temizlemez. Bir adın sonunda fazladan boşluk bulunması, onun başka bir görünüşte benzer addan farklı metin olarak değerlendirilmesine yol açabilir. Örnek işlev, veri temizleme veya ad standardizasyonu aracı değildir. Aranan anahtarların veri türü de dikkate alınmalıdır. Test edilen örnekte sayısal 100 ile metin olarak gönderilen 100 aynı biçimde eşleşmemiştir. Sicil değerleri aktarılırken kaynak ve arama girdisinin türleri tutarlı tutulmalıdır.

Ayraç Kullanımı ve Güncellenen Davranış​

Ayraç parametresi, sonuçların tek hücre içinde nasıl ayrılacağını belirler. Noktalı virgül ile Ali;Hasan;Hale;Cemal biçiminde bir çıktı oluşturulur. Boşluk içeren dikey çizgi ayıracı kullanıldığında Ali | Hasan | Hale | Cemal metni elde edilir. İki dikey çizgi gibi birden fazla karakter içeren bir ayraç da kullanılabilir. Boş metin ayraç olarak verilirse sonuçlar aralarında ek bir karakter olmadan birleştirilir. Bu son kullanımda ad sınırları daha zor okunacağı için, ayraç seçimi verinin nasıl yorumlanacağını doğrudan etkiler. Dosya birleşik metni ayrı hücrelere çözümleyen ek bir işlem içermez.
Önceki sürümde birden fazla karakterden oluşan ayraçlar, sonucun başında ve sonunda fazladan ayırıcı metin bırakabiliyordu. Güncellenmiş sürümde sonuçlar bir liste içinde toplanır ve Join işlemiyle yalnızca aralarına ayraç konularak birleştirilir. Ayrıca eski kodun iç işaretçisi olarak kullanılan üç dikey çizginin gerçek sonuç değerinde bulunması halinde oluşan veri çakışması giderilmiştir. Örneğin A|||B metni artık olduğu gibi korunur. İşlevin adı, dört parametresi, kaynak tarama sırası ve konumsal sonuç alma mantığı değiştirilmemiştir. Sayfa düzeni, mevcut arama girdisi ve G3 hücresindeki dizi formülü korunmuştur.

Boş Sonuçlar ve Kullanım Sınırları​

Hiçbir eşleşme bulunmadığında işlev Sonuç Yok metnini döndürür. Eşleşen sonuç hücresinin boş olması ise farklı bir durumdur: kaynakta eşleşme vardır, fakat alınacak metin boştur. Boş sonuç başka bir dolu sonuçla birlikte birleştiğinde ayraç konumundan boş öğe anlaşılabilir. Örneğin boş değer ile A sonucunun noktalı virgülle birleşmesi ;A biçiminde olabilir. Yalnızca boş bir sonuç bulunması halinde çıktı yine Sonuç Yok olarak değerlendirilir. Bu nedenle bu metin, boş sonuçlar bulunan veri kümelerinde tek başına eşleşme adedini göstermez. Dosyada ayrıca sayısal bir eşleşme sayacı veya boş kayıtları raporlayan ayrı alan bulunmamaktadır.
Sonuç değerinin içinde seçilen ayraç zaten bulunuyorsa, işlev bu karakteri kaçırmaz veya başka bir işaretle değiştirmez. Örneğin A;B değeri noktalı virgül ayracıyla birleştirildiğinde, sonuç metnindeki noktalı virgüllerin hangisinin veriye ait olduğunu yalnızca metne bakarak ayırmak güçleşir. Bu sebeple çıktı yeniden parçalara ayrılacaksa kaynak değerlerde bulunmayan bir ayraç seçilmelidir. Çok büyük aralıklarda her hücrenin taranması ve mevcut sonuçlarla karşılaştırılması işlem süresini artırabilir. Bu küçük örnekten sınırsız kayıt kapasitesi veya büyük tablolar için performans garantisi çıkarılmamalıdır. Hatalı hücreler ve sıra dışı aralık yapıları için bütün olası durumların kapsandığı da vaat edilmez.

Makrolu Dosyanın Açılması ve Hesaplanması​

Dosya xlsm biçimindedir ve özel işlev VBA modülünde yer alır. Çalışma sayfası formülünün hesaplanabilmesi için Excel'in bu çalışma kitabındaki VBA işlevini çalıştırmasına izin verilmesi gerekir. Makrolar devre dışı kaldığında, hücrede daha önce kaydedilmiş bir sonuç görünmesi işlevin o anda yeniden hesaplandığını kanıtlamaz. Dosya Excel masaüstü uygulamasında, güvenilir dosyalar için kullanılan normal güvenlik yaklaşımıyla açılmalıdır. Bu çalışma kitabında otomatik e-posta gönderimi, dış veri bağlantısı veya dosya açılışında çalışan bir olay yordamı bulunmamaktadır. İncelenen kodun görevi, verilen hücreleri okuyup metin sonucu döndürmektir.
G3 hücresi mevcut dosyada bir dizi formülü olarak kaydedilmiştir. İşlev adı Türkçe karakterler içerir; elle yazılırken Ç, ş ve ü karakterleri doğru kullanılmalıdır. Başka bir çalışma kitabına yalnızca hücre formülünü kopyalamak yeterli değildir; VBA modülünün de ilgili kitaba aktarılması gerekir. Aksi halde Excel özel işlevi bulamaz. Bu dosya, VBA içeren bir örnek olduğu için makroların çalışmadığı çevrim içi ortamlarda aynı yeniden hesaplama davranışını sağlayacağı ileri sürülmez. Sonuç hücresinin formülü silinmeden, değişiklikler öncelikle F3 arama girdisinde ve kaynak verilerde yapılmalıdır.

Doğrulanan Örnekler ve Uygulama Adımları​

Güncellenmiş çalışma kitabı gerçek Excel uygulamasında açılarak yeniden hesaplanmıştır. 100 ve 200 sicil değerleri, bulunmayan anahtar, metin anahtar, boş arama, çok karakterli ayraç ve boş ayraç senaryoları kontrol edilmiştir. Yinelenen sonuçlar, büyük küçük harf farkı, üç dikey çizgi içeren gerçek veri ve boş sonuç örnekleri ayrı geçici veri alanlarında denenmiştir. Arama girdisi değiştirildikten sonra çalışma sayfasındaki gerçek sonuç doğrulanmış; bir test kopyası kaydedilip yeniden açılarak yeniden hesaplama kontrol edilmiştir. Özgün kaynak dosyası arşivde korunmuş, güncelleme mevcut işlevin belirlenen iki ayraç sorununu gidermeye odaklanmıştır.
Kullanımda önce kaynak tablonun başlıkları ve arama yapılacak sütunlar incelenmelidir. Ardından F3 hücresine uygun türde bir arama değeri yazılır. G3 hücresindeki sonuç, kaynak satırlarının sırası ve seçilen ayraç dikkate alınarak okunur. Başka bir tabloya uyarlamada arama aralığı ile sonuç başlangıcının satır ve sütun ilişkisi özellikle denetlenmelidir. Sonuçların yalnızca belirli bir anahtar sütunundan alınması gerekiyorsa tarama aralığı o sütunla sınırlandırılmalıdır. Dosya, aynı anahtara ait birden fazla benzersiz değeri tek hücrede göstermek isteyen kullanıcılar için açıklayıcı bir VBA örneği sunar; mevcut altı satırlık düzen üzerinden işlevin parametreleri ve davranışı doğrudan izlenebilir.