Çok sayıda değeri içermeyen hücrelerin sayısını sayma

Genellikle COUNTIF işleviyle tek bir belirli değeri içermeyen hücreleri saymak oldukça kolaydır. Bu gönderide, Excel’de belirli bir aralıkta birden fazla değeri içermeyen hücrelerin sayısını adım adım nasıl hesaplayacağınız anlatılmaktadır.

İçerisinde çok sayıda değer bulunmayan hücreleri nasıl sayabilirim?

Aşağıdaki ekran görüntüsünde gösterildiği gibi, B3:B11 aralığında D3:D4 listesinde yer almayan değerleri içeren hücreleri saymak için aşağıdaki adımları izleyebilirsiniz.

Genel Formül

{=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(criteria_range),range))),ROW(criteria_range)^0)>,0))}

Bağımsız Değişkenler

Not: Bu formül, bir dizi formülü olarak girilmelidir. Formülü uyguladıktan sonra etrafında süslü parantezler beliriyorsa, dizi formülü başarıyla oluşturulmuş demektir.

Bu formülü nasıl kullanırım?

1. Sonucu görüntülemek için boş bir hücre seçin.

2. Aşağıdaki formülü hücreye girin ve sonucu almak için Enter tuşuna basın.Ctrl+Shift+Enter tuşlarına aynı anda basın.

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>,0))

Bu formüller nasıl çalışıyor?

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>,0))

1) --(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))):

• TRANSPOSE(D3:D4): TRANSPOSE işlevi D3:D4 aralığının yönünü döndürür ve şunu döndürür:{«count»,«blank»};
• SEARCH({“count”,”blank”},B3:B11): Bu SEARCH işlevi, B3:B11 aralığında «count» ve «blank» alt dizgelerinin konumlarını arar ve şu diziyi döndürür:{#DEĞER!,#DEĞER!;#DEĞER!,#DEĞER!;1,#DEĞER!;#DEĞER!,8;1,#DEĞER!;#DEĞER!,#DEĞER!;#DEĞER!,#DEĞER!;1,
  #DEĞER!;1,7}.
• Bu örnekte, hücreleri sayarken dışlamak istediğiniz iki değer olduğundan, B3:B11 aralığındaki her hücre iki kez taranır; bu da dizide toplam 18 değer elde edilmesine neden olur. Dizideki her sayı, B3:B11 aralığındaki ilgili hücrede «count» veya «blank» ifadelerinin ilk karakterinin konumunu gösterir.
• ISNUMBER{#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,
  #VALUE!;1,#VALUE!;1,7}: ISNUMBER işlevi, dizide sayılarla karşılaşıldığında DOĞRU, hata değerleriyle karşılaşıldığında YANLIŞ döndürür. Burada sonuç şu şekilde döndürülür:{YANLIŞ,YANLIŞ;YANLIŞ,YANLIŞ;DOĞRU,YANLIŞ;YANLIŞ,DOĞRU;DOĞRU,YANLIŞ;YANLIŞ,YANLIŞ;YANLIŞ,YANLIŞ;DOĞRU,YANLIŞ;
  DOĞRU,DOĞRU}.
• --({FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,
  FALSE;TRUE,TRUE}): Bu iki eksi işareti, «DOĞRU»yu 1’a ve «YANLIŞ»ı 0’ye dönüştürür. Böylece şu yeni diziyi elde edersiniz:{0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}.

2)ROW(D3:D4)^0: ROW işlevi, hücre başvurusunun satır numaralarını döndürür: {3;4}. Ardından üs alma operatörü, bu sayıları 0. kuvvete yükselterek sonucu şu şekilde verir: {1;1}.

3) MMULT({0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1},{1;1}): MMULT işlevi, bu iki dizinin matris çarpımını hesaplayarak {0;0;1;1;1;0;0;1;2} sonucunu döndürür; böylece orijinal verilerle eşleşir. Dizideki sıfırdan büyük her değer, dışlanan dizgelerden en az birini içeren bir satırı gösterirken; sıfır değeri, o satırda hiçbir dışlanan dizenin bulunmadığını ifade eder.

4) SUM(1-{0;0;1;1;1;0;0;1;2}>0):

• {0;0;1;1;1;0;0;1;2}>0: Burada dizideki her sayının 0’dan büyük olup olmadığını kontrol edin. Sayı 0’dan büyükse DOĞRU, değilse YANLIŞ döndürür. Böylece şu yeni diziyi elde edersiniz:{YANLIŞ;YANLIŞ;DOĞRU;DOĞRU;DOĞRU;YANLIŞ;YANLIŞ;DOĞRU;DOĞRU}.
• 1-{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}: Belirtilen değerleri içermeyen hücreleri saymamız gerektiğinden, dizideki bu değerleri 1’den çıkararak tersine çevirmemiz gerekir. Matematiksel işlemci burada DOĞRU ve YANLIŞ değerlerini otomatik olarak sırasıyla 1 ve 0’a dönüştürür ve sonucu şu şekilde verir: {1;1;0;0;0;1;1;0;0}.
• SUM{1;1;0;0;0;1;1;0;0}SUM işlevi, dizideki tüm sayıları toplayarak nihai sonucu 4 olarak döndürür.

İlgili işlevler

İlgili formüller