Sayfaları Birleştirme ve Tekrar Edenleri Bulma

Sayfaları Birleştirme ve Tekrar Edenleri Bulma, sayfalardaki verileri bir sayfada toplayıp, kriter bazlı bulmayı öğretmektedir.

Tüm detaylarını tek tek ele alacağımız içeriğimiz, soru kapsamında hazırlanmış özel bir formülün detaylarını anlatmaktadır. Hazırladığımız formüle ilişkin açıklamalarımızı anlatalım.

Dosyada, 3 ayrı sayfa bulunmaktadır.
Bu sayfalar, A, B, C şeklinde harflerle isimlendirilmiştir.
Her sayfada çalışanın adı ve sicil numarası (özet bırakılan) yer almaktadır.

Yapılmak istenen ise, 3 sayfada

Her sayfadaki çalışan ve sicil bilgilerini ayrı bir sayfada toplamak,
Aynı ad ve aynı sicile sahip olan kim varsa sadece o kişilerin ad ve sicil bilgisini listelemektir.

Normal şartlarda, Excel’in klasik fonksiyonları ile bu tür bir ihtiyacın üstesinden gelmek kolay olmamaktadır. Ancak, Dinamik Dizi Fonksiyonları ile oldukça kolay ve sadece bir hücreye yazılan formül ile işin üstesinden gelinmektedir. Öyleyse, tüm detaylarını anlatacağımız içeriğimizin adımlarına geçelim.

A, B, C isimli sayfalardaki veriler A:B sütunlarında konumlandırılmıştır. A sütununda AD, B sütununda ise SİCİL bilgisi yer almaktadır. İlk olarak, her 3 sayfada bulunan verilerin tek sayfada birleştirilmesine ihtiyaç vardır. Bunun için yeni nesil Excel fonksiyonlarından olan SÜTUNA ile başlayacağız.

=SÜTUNA(‘A:C’!A2:B495;1)

Böylece, üstteki gibi yazacağımız formül, 3 ayrı sayfadaki AD ve SİCİL bilgilerini alt alta gelecek şekilde bir sütunda listeleyecektir. Ancak, sayfalardaki dizilimde AD ayrı bir sütunda, SİCİL ayrı bir sütunda bulunmaktadır. Görüldüğü üzere, SÜTUNA ile yazdığımız formül tek başına yeterli değildir. O zaman, yine yeni nesil Excel fonksiyonlarından olan SATIRSAR ile formülü genişletmemiz gerekmektedir. Sonraki adımdaki formülümüz şöyle oluşacaktır.

=SATIRSAR (SÜTUNA(‘A:C’!A2:B495;1);2)

Eğer formülü uyguladıysanız, artık 3 sayfadaki verilerin tek sayfada alt alta ve iki sütunda listelendiğini göreceksiniz. SÜTUNA ile hazırladığımız formülde, fonksiyonun “dizi” öğesi için kullandığımız yönteme dikkat etmiş olabilirsiniz. Bunun nasıl yapıldığına dair detayları, Birden Fazla Sayfadaki Verileri Kolay Toplama linkinden izleyebilirsiniz. Böylece, ilk aşamadaki adımlarımızı tamamlamış olduk.

Aynı Ada ve Sicile Sahip Tekrar Edenleri Bulma

Dosyadaki ihtiyacın ikinci aşamasında, boş sayfada oluşturulan verilerde, sadece tekrar eden değerlerin listelenmesine ihtiyaç duyulmaktadır. Ancak, hem AD hem de SİCİL tekrar eden nitelikte olmalıdır. Dolayısıyla, burada yine kullanacağımız gelişmiş Excel fonksiyonları olacaktır. İhtiyacımız olan, BENZERSİZ fonksiyonudur. Ancak, bu fonksiyon en temel kullanım ile yeterli olmayacaktır. İlk olarak, üstte kullandığımız formülün dışına BENZERSİZ fonksiyonunu eklememiz gerekecektir.

=BENZERSİZ(SATIRSAR(SÜTUNA(‘A:C’!A2:B495;1);2);;1)

Buradaki en kritik husus, BENZERSİZ fonksiyonunun “exactly_once” öğesinin seçeğini, “DOĞRU – Yalnızca bir kez görünen öğeleri döndür” olarak seçmektir. Böylece, 3 ayrı sayfadan elde ettiğimiz verilerde, hem AD hem de SİCİL tekrar ediyorsa, bu listenin dışında kalacaktır. Lakin, bu formül bizim için istenilen sonuca ulaşmak noktasında aslında sadece bir tanımdan ibarettir. Formülü, kriterin bir parçası olmak için kullanacağız.

Sayfaları Birleştirme ve Tekrar Edenleri Bulma işlemi için sonraki adımımız şöyle devam edecektir. Üstte verdiğimiz formüllerde, sayfaları birleştirdik ve sadece bir kez yazılı olanları listeledik. Böylece, elimizde hem “veri tablosu” hem de “kriter tablosu” oluşmuş bulunmaktadır. Bu iki formülü, sonuca ulaştığımız ana formülümüzde birer tanım olarak kullanmaktayız. Yani, LET ile yapılan ad tanımlarının ad değerleri niteliğindedirler. Görünüm aşaması şu şekildedir.

=LET(rng;SATIRSAR(SÜTUNA(‘A:C’!A2:B495;1);2);a;BENZERSİZ(rng;;1)…

Böylece, asıl formülümüzün tanımlamaları bitirilmiş oldu. Artık son aşamada, işlevi yerine getirecek kısmının yazılması kaldı. Hedefe ulaşmamızı sağlayacak olan, BENZERSİZ ve FİLTRE fonksiyonlarıdır. BENZERSİZ fonksiyonun burada sadece “dizi” öğesi kullanılacaktır. Dolayısıyla, sonuca oluşmamızı sağlayan temel yapı, FİLTRE fonksiyonu üzerine kurulacaktır. FİLTRE fonksiyonun “dizi” öğesine, tanımladığımız rng değişkenini yazıyoruz. “ekle” öğesi için ise, yine işleri biraz zorlu hale getirecek formül kullanacağız.

Kriterlerini Tanımlama

Formülün temel yapısı, KAÇINCI üzerine kurulacaktır.Ancak, KAÇINCI tek başına çok fazla işlevsel olmadığından, muhteşem ekürisi olan İNDİS ile güçlendirilecektir. KAÇINCI fonksiyonunun “aranan_değer” öğesi için kullanımımız aşağıdaki gibidir.

İNDİS(rng;;1)&”|”&İNDİS(rng;;2)

Dikkat ederseniz, oluşturduğumuz veri tablosunun her iki sütunu, “aranan_değer” kısmında birleştirilmektedir. Örneğin, AHMET|123 şeklinde bir “aranan_değer” oluşturulmuştur. Hemen akabinde, KAÇINCI fonksiyonunun “aranan_dizi” öğesinde ise, sadece bir kez yazılanları bulduğumuz tablonun her iki sütunu kullanılmaktadır. Kullanımı şöyle olmaktadır.

İNDİS(a;;1)&”|”&İNDİS(a;;2)

Aslında her iki tanımda birbirinin aynısıdır. Ancak, tek farkları ikinci formülde oluşan veri tablosunda, birden fazla yazılmış olan AD ve SİCİL bilgisinin bulunmamasıdır. Dolayısıyla, yapılmak istenen her iki tablonunu değerlerinin karşılaştırılması ve sıralamadaki numaralarının bulunması şeklinde özetlenebilir. KAÇINCI fonksiyonunun opsiyonel olan “[eşleştir_tür]” öğesi içinse, “0 – Tam eşleşme” seçeneği tercih edilmelidir. Formülümüz, veri tablomuzdaki toplam sayı kadar oluşturmamızı sağlayacaktır. Ancak, tekrar eden AD ve SİCİL bilgilerinin olduğu numaralara geldiğinde, #YOK Hatası verecektir. İşte tam olarak istediğimiz, bu hatanın olduğu numaralardaki AD ve SİCİL bilgileridir. Yani #YOK Hatası veren satırlarda bulunan değerler, bizim bulmak istediğimiz değerlerdir. Toplam 4 tane #YOK Hatası alınmaktadır. Böylece, bizim en az 2 tane AD ve SİCİL bilgimizin “tekrar eden” nitelikte olduğunu tespit etmiş bulunmaktayız. Hataları devre dışı bırakmak için, EYOKSA fonksiyonu işimizi görecektir. EYOKSA fonksiyonu sıra numalarını, DOĞRU ya da YANLIŞ şeklinde boolean ifadelere döndürmektedir.