Excel Formülü: Bir hücrenin birkaç değerden birini içerip içermediğini, ancak diğer değerleri dışlayıp dışlamadığını kontrol edin

İki değer listesi olduğunu varsayalım: B3 hücresinin E3:E5 aralığındaki değerlerden birini içerip içermediğini, aynı zamanda aşağıda ekran görüntüsünde gösterildiği gibi F3:F4 aralığındaki hiçbir değeri içermeyip içermediğini kontrol etmek istiyorsunuz. Bu öğretici, bu görevi Excel’de hızlıca gerçekleştirmeniz için uygun bir formül sunacak ve formülün bağımsız değişkenlerini açıklayacaktır.

Genel formül:

Bağımsız değişkenler

Dönüş değeri:

Formül, 1 veya 0 döndürür. Hücre, dahil edilmesi gereken değerlerden en az birini içeriyor ve dışlanması gereken değerlerden hiçbirini içermiyorsa 1; aksi takdirde 0 sonucunu verir. Bu formülde 1 ve 0, mantıksal TRUE ve FALSE değerleri gibi değerlendirilir.

Bu formül nasıl çalışır

B3 hücresinin E3:E5 aralığındaki değerlerden birini içerip içermediğini, ancak aynı zamanda F3:F4 aralığındaki değerleri dışlayıp dışlamadığını kontrol etmek istiyorsanız lütfen aşağıdaki formülü kullanın

Sonucu almak için Enter tuşuna basın.

Açıklama

Kısım 1: (SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0), E3:E5 aralığındaki değerleri hücrede içerip içermediğini kontrol eder

SEARCH işlevi: SEARCH işlevi, bir metin dizesinin başka bir metin içindeki ilk karakterinin konumunu döndürür. Eşleşen metni bulursa ilgili konumu, bulamazsa #DEĞER! hatasını verir. Örneğin, burada SEARCH($E$3:$E$5,B3) formülü, E3:E5 aralığındaki her değeri B3 hücresinde arar ve bu değerlerin B3’teki ilk oluşumlarının konumlarını döndürür. Sonuç şöyle bir dizi olur: {1;7;12}.

ISNUMBER işlevi: ISNUMBER işlevi, bir hücre sayı içeriyorsa TRUE döndürür. Bu nedenle ISNUMBER(SEARCH($E$3:$E$5,B3)) formülü, SEARCH işlevi üç eşleşme bulduğundan {TRUE;TRUE;TRUE} şeklinde bir dizi sonucu verir.

--ISNUMBER(SEARCH($E$3:$E$5,B3)) formülü, TRUE değerini 1’e, FALSE değerini ise 0’a dönüştürerek dizi sonucunu şu hale getirir: {1;1;1}.

SUMPRODUCT işlevi, aralıkları çarpmak veya dizilerin çarpımlarını toplayarak sonuç döndürmek için kullanılır. SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) formülü, 1+1+1=3 sonucunu verir.

Son olarak, sol taraftaki formül SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) ile 0 karşılaştırılır; sol formülün sonucu 0’dan büyük olduğu sürece sonuç TRUE olur, aksi takdirde FALSE döner. Burada TRUE döner.

Kısım 2: (SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0), F3:F4 aralığındaki değerleri hücrede içermeyip içermediğini kontrol eder

SEARCH($F$3:$F$4,B3) formülü SEARCH($F$3:$F$4,B3), E3:E5 aralığındaki her değeri B3 hücresinde arar ve B3 hücresindeki her metin dizesinin konumunu döndürür. Şuna benzer bir dizi sonucu verir: {#VALUE!;#VALUE!}.

ISNUMBER(SEARCH($F$3:$F$4,B3)), SEARCH işlevi eşleşme bulamadığı için {false;false} şeklinde bir dizi sonucu döndürür.

--ISNUMBER(SEARCH($F$3:$F$4,B3)), TRUE değerini 1'e, FALSE değerini ise 0'a dönüştürür; böylece bu formül dizi sonucunu şuna çevirir: {0;0}.

SUMPRODUCT işlevi: aralıkları çarpmak veya dizilerin çarpımlarını toplayarak toplam sonucu döndürmek için kullanılır. SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) formülü, 0+0=0 sonucunu verir.

Son olarak, sol taraftaki formül SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) ile 0 karşılaştırılır: sol formülün sonucu 0’a eşit olduğu sürece sonuç TRUE döner; aksi takdirde FALSE döner. Bu durumda TRUE döner.

Kısım 3: İki formülü çarpın

=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>,0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)

=TRUE*TRUE

=1

Bu formülde 1 ve 0, mantıksal TRUE ve FALSE değerleri gibi değerlendirilir.

Örnek Dosya

Örnek dosyayı indirmek için tıklayın

İlgili Formüller

• Bir hücrenin belirli bir metin içerip içermediğini kontrol edin
  Excel’de bir hücrenin A aralığındaki belirli metinleri içerip içermediğini ve aynı zamanda B aralığındaki metinleri içermeyip içermediğini kontrol etmek için COUNT, SEARCH ve AND işlevlerini birleştiren bir dizi formülü kullanabilirsiniz.
• Hücre, çok sayıda öğeden birini içeriyor mu diye kontrol edin
  Bu öğretici, Excel'de bir hücrenin belirli değerlerden herhangi birini içerip içermediğini kontrol etmek için kullanabileceğiniz bir formül sunar ve formüldeki bağımsız değişkenleriyle nasıl çalıştığını ayrıntılı şekilde açıklar.
• Hücre, öğelerden birini içeriyor mu diye kontrol edin
  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 edip TRUE veya FALSE döndürmek istiyorsunuz.
• Hücrenin sayı içerip içermediğini kontrol edin
  Bazen bir hücrenin sayısal değer içerip içermediğini kontrol etmeniz gerekebilir. Bu öğretici, hücre sayı içeriyorsa TRUE, içermiyorsa FALSE döndüren pratik bir formül sunar.