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

Normalde, COUNTIF fonksiyonu ile belirli bir değeri içermeyen hücreleri saymak kolaydır. Bu yazıda, Excel'de belirli bir aralıktaki birçok değeri içermeyen hücrelerin sayısını nasıl hesaplayacağınız anlatılacaktır.

Birçok değeri içermeyen hücreler nasıl sayılır?

Aşağıdaki ekran görüntüsünde gösterildiği gibi, D3:D4 aralığında listelenen değerleri içermeyen B3:B11 hücrelerini 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))}

Argümanlar

Not: Bu formül, bir dizi formülü olarak girilmelidir. Formülü uyguladıktan sonra, formülün etrafında kıvrık parantezler varsa, bir dizi formülü başarıyla oluşturulmuştur.

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

1. Sonucu çıkarmak için boş bir hücre seçin.

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

=TOPLA(1-(MMULT(--(SAYIYSA(ARA(TRANSPOZ(D3:D4),B3:B11))),SATIR(D3:D4)^0)>0))

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

=TOPLA(1-(MMULT(--(SAYIYSA(ARA(TRANSPOZ(D3:D4),B3:B11))),SATIR(D3:D4)^0)>0))

1) --(SAYIYSA(ARA(TRANSPOZ(D3:D4),B3:B11))):

• TRANSPOZ(D3:D4): TRANSPOZ işlevi, D3:D4'ün yönünü değiştirir ve {“say”, “boş”} değerini döndürür;
• ARA({“say”, “boş”},B3:B11): Burada ARA işlevi, alt dizeler olan “say” ve “boş”un konumlarını B3:B11 aralığından bulur ve şu şekilde bir dizi 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 durumda, B3:B11'deki her hücre iki kez aranır çünkü hücreleri sayarken hariç tutmak istediğiniz iki değer vardır, bu nedenle dizide 18 değer elde edersiniz. Dizideki her sayı, B3:B11'deki her hücredeki “say” veya “boş”un ilk karakterinin konumunu gösterir.
• SAYIYSA{#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}: SAYIYSA işlevi, dizide sayılarla karşılaşıldığında DOĞRU, hatalarla karşılaşıldığında YANLIŞ döndürür. İşte şu şekilde bir sonuç döndürü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}.
• --({YANLIŞ,YANLIŞ;YANLIŞ,YANLIŞ;DOĞRU,YANLIŞ;YANLIŞ,DOĞRU;DOĞRU,YANLIŞ;YANLIŞ,YANLIŞ;YANLIŞ,YANLIŞ;DOĞRU,
  YANLIŞ;DOĞRU,DOĞRU}): Bu iki eksi işareti, “DOĞRU”yu 1'e, “YANLIŞ”ı 0'a dönüştürür. İşte burada yeni bir dizi elde edersiniz: {0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}.

2) SATIR(D3:D4)^0: SATIR işlevi, hücre başvurusunun satır numaralarını döndürür: {3;4}, ardından üs alma operatörü (^), 3 ve 4 sayılarının 0. kuvvetini alır ve son olarak şu sonucu döndürür: {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ı döndürür: {0;0;1;1;1;0;0;1;2} orijinal verileri karşılamak için. Dizideki sıfırdan farklı herhangi bir sayı, hariç tutulan dizelerden en az birinin bulunduğunu gösterir ve sıfır, hariç tutulan hiçbir dizenin bulunmadığını gösterir.

4) TOPLA(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. Eğer bir sayı 0'dan büyükse DOĞRU, değilse YANLIŞ döndürür. Ardından şu şekilde yeni bir dizi elde edersiniz: {YANLIŞ;YANLIŞ;DOĞRU;DOĞRU;DOĞRU;YANLIŞ,YANLIŞ,DOĞRU;DOĞRU}.
• 1-{YANLIŞ;YANLIŞ;DOĞRU;DOĞRU;DOĞRU;YANLIŞ,YANLIŞ,DOĞRU;DOĞRU}: Sadece belirtilen değerleri içermeyen hücreleri saymamız gerektiğinden, dizideki bu değerleri tersine çevirmemiz gerekir. Matematik operatörü, DOĞRU ve YANLIŞ değerlerini otomatik olarak 1'lere ve 0'lara dönüştürür ve sonunda şu sonucu döndürür: {1;1;0;0;0;1;1;0;0}.
• TOPLA{1;1;0;0;0;1;1;0;0}: TOPLA işlevi, dizideki tüm sayıları toplar ve sonucu 4 olarak döndürür.

İlgili fonksiyonlar

İlgili formüller