1. Amaç
Elimizde aynı değerlerin birden fazla yer aldığı bir liste olduğunu varsayalım. Bu listedeki benzersiz değerleri kullanarak bir tablo hazırladığımızı varsayalım. Normalde Excel’in “Veri” sekmesindeki “Gelişmiş” seçeneği ile biz bu listedeki benzersiz değerleri ayrı bir liste haline getirebiliriz. Ancak bu listenin sürekli güncellendiğini ve bu işlemi sürekli tekrarlamak gerektiğinde formül ile bu değerleri çekmek daha avantajlı duruma gelecektir.
Bu işlem için Excel 365 ve Excel 2021 sürümlerinde BENZERSİZ işlevini kullanarak hızlı bir çözüm üretebilirsiniz. Ancak eski sürümlerde bu işlevin yerine kullanabileceğiniz bazı yöntemler de mevcut.
Bu yazıya başlamadan önce Excel’in dizi formülü özelliğine hakim olmanız gerekiyor. Bu nedenle öncelikle buradaki yazımı okumanızı şiddetle tavsiye ederim.
Kullanacağım formüller sabit bir aralığa başvuruyor. Bu nedenle bu listeye yeni değerler eklendiğinde formüllerin otomatik olarak bu yeni değerleri de dikkate almasını istiyorsanız ya Excel Tablo özelliğini kullanmanız gerek ye da değişken aralık tanımlaması yapmanız gerekmekte.
Ve ancak eşsiz değerler için yazdığınız formülü belirli bir aralığa uygulayabildiğinizden, bu aralığı da kontrol ederek tüm değerleri içerdiğinden emin olacak şekilde güncellemeyi unutmamalısınız.
2. Uygulama
Uygulama için ben buradan ulaşabileceğiniz aşağıdaki gibi basit bir tablo oluşturdum. Sizin uygulama için kendi dosyalarınızı da kullanabilirsiniz.
Burada yapmak istediğim yöntem Eşsiz Ad altında yer alan isimlerin Ad Listesi altında yer alıp almadığını kontrol etmek. Eğer bu liste içinde yok ise bu değeri listeye eklemek.
a. İki Listeyi Karşılaştırma
İki listeyi birbiri ile karşılaştırmak için EĞERSAY işlevini kullanacağım. Bu işlev Eşsiz Ad listesindeki tüm elemanların Ad Listesi altında toplam kaç tane olduğunu döndürecek. Eğer dönen sayı sıfır ise bu değer Eşsiz Ad listesinde yer almıyor demek. Ben D3 hücresine aşağıdaki formülü giriyorum:
=EĞERSAY($D$2:$D2;$B$3:$B$13)
Burada dikkat edeceğiniz ilk konu Eşsiz Ad aralığının D2’den başlaması. Eğer bu aralığı D3’ten başlatırsam döngüsel başvuraya sebep olurum ve formülüm hata verir. İkinci olarak aralığın ilk hücresinin adresi mutlak olarak ($D$2) ve ikinci hücresinin adresinin satırının göreceli olması ($D2). Bunun sebebi formülü aşağıya doğru çoğalttığımda aralığın ilk hücresi sabit kalacak ancak ikinci hücresi kendini güncelleyerek aralıktaki diğer değerleri de alacaktır. Mutlak ve göreceli hücre adresleri için buradaki yazıyı okumanızı tavsiye ederim.
Bu formül bize 0 ve 1’lerden oluşan bir liste döndürecektir. Excel 365 için formülü yazdığınızda aşağıya doğru uzunan değerleri göreceksiniz. Daha eski Excel sürümleri için CTRL+SHIFT+ENTER tuşlarına basar ve formülü aşağıya doğru uzatırsanız bu değerler gözükür.
İlginizi çekebilecek Diğer Yazılar
b. Listede Yer Almayan Değerin Konumunu Bulma
Bahsettiğim gibi bu sonuç listesinde yer alan 0 değerleri, İsim Listesinde o sırada yer alan değerin daha önce Eşsiz Ad listesinde yer almadığını ifader eder. Ben bu değerler içinde 0 değerinin ilk bulunduğu sırayı bulmam gerekiyor. Bunun için KAÇINCI işlevini kullanabilirim:
=KAÇINCI(0;EĞERSAY($D$2:$D2;$B$3:$B$13);0)
Bu formülü CTRL+SHIF+ENTER tuşları ile çalıştırdığınızda bize 1 değerini döndürecek. Eğer dizi formülü kullanmak istemiyorsanız EĞERSAY ile elde ettiğimiz listeyi İNDİS formülü ile erişilebilir kılmamız gerekir:
=KAÇINCI(0;İNDİS(EĞERSAY($D$2:$D2;$B$3:$B$13);;);0)
Aşağıdaki resimlerde iki formülün de sonuçlarını gösterdim. Formül çubuklarındaki formüllere dikkat edin.
c. Listede Yer Almayan Değerin Konumunu Bulma
Bundan sonra yapacağım işlem ise Ad Listesinde yer alan ve bir önceki adımda bulduğum rakamın sırasında yer alan değeri çekmek. Bunun için İNDİS veya KAYDIR kullanabilirim. Ben İNDİS işlevini kullanacağım:
=İNDİS($B$3:$B$13;KAÇINCI(0;EĞERSAY($D$2:$D2;$B$3:$B$13);0);)
Bu formülü de CTRL+SHIFT+ENTER tuşları ile çalıştırdığımda Ad Listesindeki ilk değer olan “Ahmet” değeri Eşsiz Ad listesinin altına gelecek. Sonrasında ben bu formülü aşağıya doğru uzatacağım:
Gördüğünüz gibi Ad Listesindeki değerlerden sadece bir tane olacak şekilde tüm değerleri çekebildik. Ancak bazı değerlerden birden fazla olduğundan yazdığımız formülün tam olarak kaç tane değer döndüreceğinden emin olmadığımızdan, formül aralığını uzun tutmak durumunda kaldık ve hali ile #YOK hatası da listede gözüktü.
d. Hataları Gizleme
Hataları gizlemek için EĞERHATA işlevini kullanacağım:
=EĞERHATA(İNDİS($B$3:$B$13;KAÇINCI(0;EĞERSAY($D$2:$D2;$B$3:$B$13);0););””)
Bu durumda artık hata yerine boş bir ifade gösterilir.
Anlattığım adımların nasıl çalıştığını görmek için aşağıdaki gibi “Formüller” sekmesindeki “Formülü Değerlendir” işlevini kullanabilirsiniz.
Gördüğünüz gibi bu formül ile artık sadece benzersiz değerlerin olduğu bir liste elde ettim. Özellikle sürekli güncellenen bir liste üzerinde işlem yapmanız gerekiyor ise bu formül çok faydalı olacaktır.
Bu yazının da sonuna geldim. Umarım işinize yarayacak bir çözüm olur. Ben bu formülü çok sık kullanmasam da bazı durumlarda benim çok işime yaradı. Excel 365 sürümüne geçtikten sonra artık güncelliğini yitirse de eski sürümleri kullananlar için oldukça faydalı olacağını düşünüyorum.
Merhaba. Bu güzel çalışmanız için teşekkür ederim.
Faydalı oldu ise amacıma ulaşmışım demektir. Güzel yorum için ayrıca teşekkürler.
aga sen kimsin ya. bunca yıllık vba makro cuyum daha bu formulleri senden gordum ögrendim.
sen kimsin dayııı. nerden cıktın. IMPARATORLUK ILAN ET SEN. AGALIK, PASALIK KESMEZ BU SEVIYEYI.
Merhaba,
Övgüler için teşekkür ederim ancak kendimi tanımlamam gerekirse ne ağa, ne paşa diyebilirim. Kendimi daha çok öğrenci olarak görüyorum. İşin doğrusu ben de VBA ile çok uğraştım. Ancak VBA kendi sorunlarını da beraber getiriyor, özellikle kişiye bağlı olması durumu. VBA kullanmadan nasıl çözerim sorusu ile başladı her şey. Kademe, kademe araştırarak bu seviyelere geldi.
Hala VBA kullanma durumu var tabii. Ancak eskisi kadar yoğun kullanma ihtiyacı da hissetmiyorum. Özellikle Power Query ile birlikte çoğu işi Excel içinde halletmek hem daha hızlı sonuç veriyor hem de müdahale etmek çok daha kolay oluyor. Ek olarak bazı firmaların makro kullanma kısıtlamalarına da maruz kalmıyorsunuz.
Saygılarımla,
selamün aleyküm
excelde değişik ve profesyonel dosyalarınızı paylaşmak ister misiniz
daha doğrusu aklımda excelde yapamadığım bir soru var.
yapsamda çok büyük dosyaya ve çok çalışmaya bağlı olacak. pratik komut varmı diye merak ediyorum.
benimde kendime göre excelde yaptığım programlar var. fakat düşündüğüm bazı şeyleri halaa yapamıyorum.lütfen iletişime geçebilr misiniz.
eposta:mbacak32@gmail.com
Merhaba,
Önce neler düşündüğünüzü iletişim formunu kullanarak iletebilirseniz ondan sonra bilgi verebilirim.
Buraya yazdığınız her yorum önce onaya düşer. Günün 24 saati site ile ilgilenme imkanım olmadığından bu yorumların görülmesi ve onaylanması zaman almaktadır.
Saygılarımla,
Çok teşekkürler, emeğinize sağlık. Bende orjinal haline çevirdim;
IFERROR(INDEX($D$2:$D$18;MATCH(0;COUNTIF($Q$1:$Q11;$D$2:$D$18);0));””)
Merhaba paylaşımınız için öncelik ile teşekkür ederim. Uzun zamandır bakıyordum aradığımı burada buldum.
Bu duruma benzer bir yardıma ihtiyacım var
Excel çalışma kitabı içinde iki farklı sayfalarda otak olan ve olmayan başlıklar ve bu başlıkların altında da Poz numarası dediğimiz numaralar var. Bunları başka bir sayfada Başlıklarına göre ortak olan ve ortak olmayan pozları nasıl yazdırırım.
Sayfa1 de “B” sütununda MİM.01 “D” sütununda 15.100.1004 gibi alt alta giden farklı başlıklar sayfa2 de de var
Sayfa1 de “B” sütununda MİM.02 “D” sütununda 15.100.1004 gibi alt alta giden farklı başlıklar sayfa2 de de var
Sayfa1 de “B” sütununda MİM.01 “D” sütununda 15.100.1005 gibi alt alta giden farklı başlıklar sayfa2 de de var
Sayfa3 de “B” sutununa MİM.01 gibi farklı tanımlama başlığına göre sayfa1 ve sayfa2 de ki poz numaralarını “”D” sütununa benzersiz olarak getirmek istiyorum
yukarıda ki formül tek sayfa için yetiyor ama iki sayfa için yeterli olmuyor Sayfa1 e bakıp benzersiz hale getirip sonra sayfa2 bakıp benzersiz hale getiremedim. Bu konuda yardımcı olursanız sevinirim. Şimdiden teşekkür ederim.
Merhaba,
Aşağıdaki yazıda anlatılanlar işinize yarayabilir:
http://excelileharikalar.com/index.php/2020/02/09/cok-sayfada-duseyara/
Saygılarımla,
Merhaba sayfada listede boşluk olduğunda hata vermekte. Yardımcı olur musunuz?
Merhaba,
Elimde bunun için bir formül yok. En hızlı çözüm bir filtreleme ile sadece istediğiniz değerleri farklı bir tabloya aktarmak ve bu tablo üzerinden sonuca ulaşmak.
Saygılarımla,
Merhaba,
Elimde formülasyonu belli olmayan bir Excel tablosu var. Buradan 20 satırlık bir tablodaki formülasyonu tespit etmek istiyorum. Bunu Excel kullanarak yapmam mümkün mü?
Merhaba,
Formülleri göremediğinizi varsayarak Excel dosyasının korumaya alındığını sonucuna varıyorum. Gözden Geçir sekmesinde “Satfa korumasını kaldır” komutunu kullanarak basit şifreler ile bir deneme yapabilirsiniz ancak eğer şifreyi bilmiyorsanız Excel koruma şifrelerini bulmanın veya iptal etmenin basit bir yolu yok. Ben bu konuda yaptığım araştırmalarda sağlam bir çözüm bulamadım.
Saygılarımla,
Allah razı olsun senden. Valla mükemmel bir iş çıkarmışsın. Araya araya deliye dönmüştüm. Sonunda buldum tam da istediğim şeyi. Sonsuz TEŞEKKÜRLER!.. Yalnız ufak bir sorunum daha var. Benim listemde B sütununda arada boşluklar da var. Boş hücreler sorun çıkarıyor.
Merhaba,
Boşluklu liste için:
=EĞERHATA(ARA(2;1/((EĞERSAY($D$2:D2;$B$3:$B$19)=0)*($B$3:$B$19<>“”));$B$3:$B$19);””)
formülünü kullanabilirsiniz.
Saygılarımla,