Bir hücreden ilk liste değerini almak
Belirli bir hücrede listelenen ilk anahtar kelimeyi, hücre birden fazla değer içerdiğinde almak için, oldukça karmaşık bir dizi formülü olan INDEX, MATCH, ISNUMBER ve SEARCH fonksiyonlarını kullanmanız gerekecek.
Bir hücreden ilk liste değerini nasıl alabilirim?
Yukarıda gösterildiği gibi Anahtar Kelimeler sütununa karşı hücrelerde bulunan ilk eşleşen anahtar kelimeyi almak için tam eşleşme yerine bir içerme eşleşmesi yapmamız gerekir. Bunu yapmak için ISNUMBER ve SEARCH fonksiyonlarını birleştirerek DOĞRU ve YANLIŞ dizisi elde edebilirsiniz. Ardından MATCH'i kullanarak ilk DOĞRU değerinin konumunu bulun ve bu pozisyon numarasını INDEX'e besleyerek o pozisyondaki değeri alın.
Genel sözdizimi
=INDEX(anahtar_kelime_rng,MATCH(TRUE,ISNUMBER(SEARCH(anahtar_kelime_rng,arama_hücresi)),0))
√ Not: Bu bir dizi formülüdür ve bunu Ctrl + Shift + Enter ile girmeniz gerekir.
- anahtar_kelime_rng: Anahtar kelimeler içeren hücre aralığı.
- arama_hücresi: Anahtar kelimeleri içerip içermediğini kontrol etmek istediğiniz hücre.
B5 hücresinden Anahtar Kelimeler listesindeki ilk eşleşmeyi almak için lütfen aşağıdaki formülü C5 hücresine kopyalayın veya girin ve sonucu almak için Ctrl + Shift + Enter tuşlarına basın:
=INDEX(($E$5:$E$7,MATCH(TRUE,ISNUMBER(SEARCH(($E$5:$E$7,B5)),0))
√ Not: Yukarıdaki dolar işaretleri ($) mutlak referansları belirtir, yani formülü diğer hücrelere taşıdığınızda veya kopyaladığınızda formüldeki anahtar_kelime_rng değişmez. Ancak, dinamik olmasını istediğiniz için arama_hücresi'ne dolar işareti eklenmemiştir. Formülü girdikten sonra, formülü aşağıya doğru uygulamak için doldurma tutamacını sürükleyin.
Formül açıklaması
=INDEX(($E$5:$E$7,MATCH(TRUE,ISNUMBER(SEARCH($E$5:$E$7,B5)),0))
- SEARCH($E$5:$E$7,B5): SEARCH fonksiyonu, $E$5:$E$7 aralığından anahtar kelimeler bulunduğunda sayısal olarak pozisyonlarını döndürür, bulunamazsa #DEĞER! hatasını verir. Sonuç şu şekilde bir dizi olacaktır: {15;11;#DEĞER!}.
- ISNUMBER(ISNUMBER(SEARCH($E$5:$E$7,B5))) = ISNUMBER(ISNUMBER({15;11;#DEĞER!})): ISNUMBER fonksiyonu, dizideki sayıları DOĞRU'lara ve #DEĞER! değerlerini YANLIŞ'lara dönüştürecektir: {DOĞRU;DOĞRU;YANLIŞ}.
- MATCH(TRUE,MATCH(TRUE,ISNUMBER(SEARCH($E$5:$E$7,B5)),0),0) = MATCH(TRUE,MATCH(TRUE,{DOĞRU;DOĞRU;YANLIŞ},0),0): match_type 0, MATCH fonksiyonunun tam eşleşme yapmasını sağlar ve {DOĞRU;DOĞRU;YANLIŞ} dizisindeki ilk DOĞRU'nun pozisyonunu döndürür. Böylece, fonksiyon 1 değerini döndürür.
- INDEX(($E$5:$E$7,MATCH(TRUE,ISNUMBER(SEARCH($E$5:$E$7,B5)),0)) = INDEX(($E$5:$E$7,1): INDEX fonksiyonu daha sonra $E$5:$E$7 aralığındaki 1. değeri döndürür, bu da AAA'dır.
Not
- Bir hücre iki veya daha fazla anahtar kelime içeriyorsa, formül anahtar kelime aralığında listelenen ilk anahtar kelimeyi döndürür.
- Formül büyük/küçük harfe duyarlı değildir. Büyük/küçük harfe duyarlı bir eşleşme yapmak için SEARCH fonksiyonunu FIND ile değiştirebilirsiniz.
- Sadece hücrelerin bu dizeyi içerip içermediğini kontrol etmek istiyorsanız, anahtar kelimeler listesini sabit kodlu bir dizeyle (örn. {"AAA"}) değiştirebilirsiniz.
İlgili fonksiyonlar
Excel INDEX fonksiyonu, bir aralık veya diziye göre verilen pozisyona dayalı olarak görüntülenen değeri döndürür.
Excel MATCH fonksiyonu, belirli bir değeri bir hücre aralığında arar ve değerin göreli konumunu döndürür.
Excel'de, SEARCH fonksiyonu size aşağıdaki ekran görüntüsünde gösterildiği gibi verilen metin dizesinden belirli bir karakterin veya alt dizenin pozisyonunu bulmanıza yardımcı olabilir. Bu eğitimde, Excel'de bu SEARCH fonksiyonunu nasıl kullanacağınızı anlatacağım.
İlgili Formüller
Bir listeden hücredeki ilk eşleşen değeri almak
Anahtar kelimelerin bir listesi olduğunu varsayalım, belirli bir hücrede görünen ilk anahtar kelimeyi almak için, hücre birkaç değer içerdiğinde INDEX ve MATCH formülünü AGGREGATE ve SEARCH fonksiyonlarının yardımıyla kullanmanız gerekir.
INDEX ve MATCH ile tam eşleşme
Excel'deki belirli bir ürün, film veya kişi vb. hakkında bilgi almanız gerekiyorsa, INDEX ve MATCH fonksiyonlarının kombinasyonunu iyi bir şekilde kullanmalısınız.
Bir hücrenin belirli bir metni içerip içermediğini kontrol etmek
Buradaki eğitim, bir hücrenin belirli bir metni içerip içermediğini kontrol etmek için bazı formüller sağlar ve aşağıdaki ekran görüntüsünde gösterildiği gibi DOĞRU ve YANLIŞ döndürür, ayrıca argümanları ve formüllerin nasıl çalıştığını açıklar.
Bir hücrenin birçok şeyin tamamını içerip içermediğini kontrol etmek
Excel'de E sütununda bir değer listesi olduğunu varsayalım, B sütunundaki hücrelerin E sütunundaki tüm değerleri içerip içermediğini kontrol etmek ve aşağıdaki ekran görüntüsünde gösterildiği gibi DOĞRU veya YANLIŞ döndürmek istiyorsunuz. Bu eğitimde, bu görevi çözmek için bir formül sunulmaktadır.
Bir hücrenin birçok şeyden birini içerip içermediğini kontrol etmek
Bu eğitim, bir hücrenin Excel'deki birkaç değerden birini içerip içermediğini kontrol etmek için bir formül sağlar ve formüldeki argümanları ve formülün nasıl çalıştığını açıklar.
En İyi Ofis Üretkenlik Araçları
Kutools for Excel - Kalabalıktan Sıyrılmaya Yardımcı Olur
Kutools for Excel, İhtiyacınız Olan Her Şeyin Tek Tıklama Uzağında Olduğundan Emin Olmak İçin 300'den Fazla Özelliğe Sahiptir...
Office Tab - Microsoft Office'de (Excel dahil) Sekmeli Okuma ve Düzenlemeyi Etkinleştir
- Bir saniyede, onlarca açık belge arasında geçiş yapın!
- Her gün yüzlerce fare tıklamasından kurtulun, fare eline veda edin.
- Birden fazla belgeyi görüntülediğinizde ve düzenlediğinizde üretkenliğinizi %50 artırır.
- Ofis'e (Excel dahil) Chrome, Edge ve Firefox gibi etkili sekmeler getirir.