Excel Formülü: Bir hücrenin belirli değerleri içerip içermediğini kontrol et, ancak diğer değerleri hariç tut

Diyelim ki iki değer listesi var ve B3 hücresinin E3:E5 aralığındaki değerlerden birini içerip içermediğini kontrol etmek istiyorsunuz, ancak aynı zamanda F3:F4 aralığındaki hiçbir değeri içermemesini istiyorsunuz (aşağıdaki ekran görüntüsüne bakın). Bu öğretici, bu görevi Excel'de hızlı bir şekilde gerçekleştirmek için bir formül sağlayacak ve formülün argümanlarını açıklayacaktır.

Genel formül:

Argümanlar

Dönüş değeri:

Formül 1 veya 0 döndürür. Hücre, dahil edilmesi gereken değerlerden birini içerdiğinde ve hariç tutulması gereken hiçbir değeri içermiyorsa, 1 döndürür; aksi takdirde 0 döndürür. Bu formülde, 1 ve 0 mantıksal değerler olan DOĞRU ve YANLIŞ gibi ele alınır.

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

Diyelim ki B3 hücresinin E3:E5 aralığındaki değerlerden birini içerip içermediğini kontrol etmek istiyorsunuz, ancak aynı anda F3:F4 aralığındaki değerleri hariç tutmak istiyorsunuz, lütfen aşağıdaki formülü kullanın.

Basın Enter tuşuna basarak kontrol sonucunu alın.

Açıklama

1. Kısım: (TOPLA.ÇARPIM(--EĞERSAY(ARA($E$3:$E$5,B3)))>0) hücrenin E3:E5'teki değerleri içerip içermediğini kontrol eder.

ARA fonksiyonu: ARA fonksiyonu, bir metin dizesinin başka bir metin içindeki ilk karakterinin konumunu döndürür. Eğer ARA fonksiyonu eşleşen metni bulursa, göreli pozisyonu döndürür; bulamazsa #DEĞER! hatası döndürür. Örneğin, burada ARA($E$3:$E$5,B3) formülü, E3:E5 aralığındaki her değeri B3 hücresinde arar ve B3 hücresindeki her metin dizesinin konumunu döndürür. Sonuç olarak şu dizi sonucunu döndürür: {1;7;12}.

EĞERSAY fonksiyonu: EĞERSAY fonksiyonu, bir hücre sayı olduğunda DOĞRU döndürür. Dolayısıyla EĞERSAY(ARA($E$3:$E$5,B3)) ARA fonksiyonunun 3 sayı bulduğu için {doğru,doğru,doğru} dizisi sonucunu döndürür.

--EĞERSAY(ARA($E$3:$E$5,B3)) DOĞRU değerini 1'e, YANLIŞ değerini 0'a dönüştürür, dolayısıyla bu formül diziyi {1;1;1} olarak değiştirir.

TOPLA.ÇARPIM fonksiyonu: Aralıkları çarpmak veya dizileri toplamak için kullanılır ve ürün toplamını döndürür. TOPLA.ÇARPIM(--EĞERSAY(ARA($E$3:$E$5,B3))) 1+1+1=3 döndürür.

Son olarak, sol formülü karşılaştırın. TOPLA.ÇARPIM(--EĞERSAY(ARA($E$3:$E$5,B3))) ve 0, sol formülün sonucu 0'dan büyük olduğu sürece sonuç DOĞRU olacaktır, aksi takdirde YANLIŞ döndürür. Burada DOĞRU döndürür.

2. Kısım: (TOPLA.ÇARPIM(--EĞERSAY(ARA($F$3:$F$4,B3)))=0) hücrenin F3:F4'teki değerleri içermediğini kontrol eder.

ARA($F$3:$F$4,B3) formülü, E3:E5 aralığındaki her değeri B3 hücresinde arar ve B3 hücresindeki her metin dizesinin konumunu döndürür. Sonuç olarak şu dizi sonucunu döndürür: {#DEĞER!;#DEĞER!}.

EĞERSAY(ARA($F$3:$F$4,B3)) ARA fonksiyonunun 0 sayı bulduğu için {yanlış;yanlış} dizisi sonucunu döndürür.

--EĞERSAY(ARA($F$3:$F$4,B3)) DOĞRU değerini 1'e, YANLIŞ değerini 0'a dönüştürür, dolayısıyla bu formül diziyi {0;0} olarak değiştirir.

TOPLA.ÇARPIM fonksiyonu: Aralıkları çarpmak veya dizileri toplamak için kullanılır ve ürün toplamını döndürür. TOPLA.ÇARPIM(--EĞERSAY(ARA($F$3:$F$4,B3))) 0+0=0 döndürür.

Son olarak, sol formülü karşılaştırın. TOPLA.ÇARPIM(--EĞERSAY(ARA($F$3:$F$4,B3))) ve 0, sol formülün sonucu 0'a eşit olduğu sürece sonuç DOĞRU dönecektir, aksi takdirde YANLIŞ dönecektir. Burada DOĞRU döner.

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

=(TOPLA.ÇARPIM(--EĞERSAY(ARA($E$3:$E$5,B3)))>0)*(TOPLA.ÇARPIM(--EĞERSAY(ARA($F$3:$F$4,B3)))=0)

=DOĞRU*DOĞRU

=1

Bu formülde, 1 ve 0 mantıksal değerler olan DOĞRU ve YANLIŞ gibi ele alınır.

Örnek Dosya

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

İlgili Formüller

• Bir hücrenin belirli bir metni içerip içermediğini kontrol et
  Bir hücrenin A aralığındaki bazı metinleri içerip içermediğini, ancak B aralığındaki metinleri içermemesini kontrol etmek için Excel'de COUNT, SEARCH ve AND fonksiyonlarını birleştiren bir dizi formülü kullanabilirsiniz.
• Bir hücrenin birçok şeyden birini içerip içermediğini kontrol et
  Bu öğretici, 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.
• Bir hücrenin şeylerden birini içerip içermediğini kontrol et
  Diyelim ki Excel'de, sütun E'de bir değer listesi var ve sütun B'deki hücrelerin sütun E'deki tüm değerleri içerip içermediğini kontrol etmek ve DOĞRU veya YANLIŞ döndürmek istiyorsunuz.
• Bir hücrenin sayı içerip içermediğini kontrol et
  Bazen bir hücrenin sayısal karakterler içerip içermediğini kontrol etmek isteyebilirsiniz. Bu öğretici, hücre sayı içeriyorsa DOĞRU, hücre sayı içermiyorsa YANLIŞ döndüren bir formül sağlar.