Excel'de Boş Olmayan Huücreler Nasıl Sayılır: 5 Temel Yöntem

Ayrıca her kişinin yanıtının ne kadar tamamlandığını görmek için satır bazında da sayabilirim:

=COUNTA(B2:F2)Altyapı:

Sara Ibrahim için bu, tüm alanları doldurduğu için 5 döner. Nour Mahmoud için =COUNTA(B6:F6) yalnızca 1 döner; çünkü sadece e-postasını verdi ve geri kalanını bıraktı.

COUNTA() ne zaman yeterli olur

Basit bir sayıma ihtiyacınız olduğunda ve veriniz temizken COUNTA() kullanın. Aralığınızdaki her boş huücre gerçekten boşsa (hiç dokunulmamış, formül yoksa), COUNTA() mükemmel çalışır.

Karışıklık, formüllerin "" (boş dizeler) döndürmesiyle başlar. Bu sınır durumu, aşağıdaki "Excel Boş ve Boş Görünen Huücreleri Nasıl Yorumlar" bölümünde ayrıntılı açıklanacak.

COUNTIF() ile Boş Olmayan Huücreleri Sayın

COUNTIF() boş olmayan huücreleri saymanın başka bir yolunu sunar. Var olan her şeyi saymak yerine, bir koşulu sağlayan huücreleri sayar. Ve bu koşul "boş değil" olabilir.

"<>" ölçütü

Formül böyle görünür:

=COUNTIF(B2:B13,"<>")Altyapı:

Buradaki "<>" kısmı "hiçbir şeye eşit değil" anlamına gelir ki bu da "boş değil" demenin başka bir yoludur. Az önceki aynı e-posta sütunu için bu bana 10 verir, COUNTA() ile aynı.

Neden COUNTIF(range,"*") kullanmayalım?

Bunu eski derslerde görüyorum ve bu bir hatadır. Joker karakter "*" yalnızca metni sayar. Sayıları, tarihleri ve DOĞRU/YANLIŞ değerlerini tamamen yoksayar. Sütununuz farklı veri türleri içeriyorsa, yanlış sayım elde edersiniz.

Bunun yerine "<>" kullanın. Elinizde hangi tür veri olursa olsun çalışır.

COUNTIF() ne zaman COUNTA()'dan iyidir?

Asıl avantajı, COUNTIF() kullanınca doğal olarak COUNTIFS()'a geçebilmenizdir. Boş olmayan huìcreleri, başka koşulları (belirli bir departman veya tarih aralığı gibi) da sağlayanlarla birlikte saymanız gerektiğinde COUNTIFS() hazırdır. İleride koşullar ekleyebileceğinizi düşünüyorsanız, geçiş kolay olsun diye COUNTIF() ile başlayın.

Çoğu veri için COUNTA() ile COUNTIF(range,"<>") aynı sonuçları verir. İkisi de aynı şeyleri sayar; hatalar ve formül kaynaklı boşlar dahil. Temel fark esnekliktir. COUNTIF(), COUNTIFS() ile çok ölçütlü sayıma köprünüzdür.

Koşulları Sağlayan Boş Olmayan Huücreleri Sayın

İşte COUNTIF() gerçekten parlamaya başlar. COUNTIFS() boş olmayan huücreleri, başka koşulları da sağladıklarında saymanıza olanak tanır.

Boş olmayan mantıkla COUNTIFS()

Diyelim ki çalışan eğitimini takip ediyorum ve Satış departmanında kaç kişinin puan gönderdiğini bilmek istiyorum. Formül şöyle:

=COUNTIFS(B2:B11,"Sales",E2:E11,"<>")Altyapı:

Bu şunu ifade eder: "Departman 'Sales' OLAN ve Puan boş OLMAYAN satırları say." İzleyicimde bu bana 3 veriyor; çünkü Mona Sayed Satış'ta ama henüz puan teslim etmedi.

Birden fazla koşul

Gerekirse 127 kriter çiftine kadar istifleyebilirsiniz. İşte hem "Complete" işaretli hem de puanı olan çalışanları sayan bir formül:

=COUNTIFS(C2:C11,"Complete",E2:E11,"<>")Altyapı:

Bu bana 6 sonucunu verir; çünkü Mona Sayed eğitimini tamamladı ama hiç puan göndermedi.

Başka bir örnek. Tamamlanma tarihi olan Mühendislik ekip üyelerini sayma:

=COUNTIFS(B2:B11,"Engineering",D2:D11,"<>")Altyapı:

Bu 3 döndürür; çünkü Omar Mostafa'nın eğitimi hâlâ beklemede ve henüz tarihi yok.

Kalıp basit: Boş olmayan değerleri kontrol ettiğiniz sütun için "<>" kullanın ve belirli filtrelerinizi diğer sütunlara koyun.

Excel'in "Boş" ve "Empty" Huücreleri Nasıl Yorumladığı

Sayma hatalarının çoğu burada olur. Excel, ekranda aynı görünen farklı "boş" türlerini görür.

Gerçekten boş huücreler

Gerçekten boş bir huücreye hiç dokunulmamıştır ya da tamamen temizlenmiştir. ISBLANK() TRUE döndürür, COUNTA() atlar ve COUNTBLANK() sayar. Tüm işlevlerin boş olduğu konusunda hemfikir olduğu tek huücre türü budur.

Formül kaynaklı boş dizeler

Bu herkesi sık sık yakalar. =IF(A1>60,"Pass","") gibi bir formül "" döndürdüğünde, huücre boş görünür ama aslında bir formül sonucu içerir. Farklı işlevlerin bunu nasıl gördüğü şöyledir:

• COUNTA() → Sayar (boş değil)
• COUNTIF(range,"<>") → Sayar (boş değil)
• COUNTBLANK() → Sayar (boş)
• ISBLANK() → FALSE döndürür

Sorunu gördünüz mü? COUNTBLANK() boş diyor, ancak ISBLANK() boş değil diyor. Bu, Excel'i yıllardır kullananları bile yanıltır.

SUMPRODUCT() + LEN() geçici çözümü

Yalnızca gerçekten görünen içeriği olan huücreleri saymak için (formül boşları yok, boşluk karakterleri yok) bunu kullanın:

=SUMPRODUCT(--(LEN(B5:B10)>0))Altyapı:

Bu, her huücrede kaç karakter olduğunu kontrol eder. LEN() 0 dönerse, huücre her ne olursa olsun boştur. -- bölümü DOĞRU/YANLIŞı 1/0'a dönüştürür ve SUMPRODUCT()` bunları toplar.

Boşluk karakterleri olan huücreleri de yoksayan daha katı bir sayım için:

=SUMPRODUCT(--(LEN(TRIM(B5:B10))>0))Altyapı:

Gizli karakterli huücreler

Bazen bir huücre boş görünür ama içinde bir boşluk (CHAR(32)) ya da web verisi veya PDF'lerden gelen kırılmasız boşluk (CHAR(160)) bulunur. COUNTA() bunu sayar, LEN() 1 veya daha fazla döner ve ISBLANK() FALSE der.

Bunları sayınıza dahil etmek için, önce gösterdiğim daha katı formülü kullanın: =SUMPRODUCT(--(LEN(TRIM(range))>0)). TRIM() kısmı, saymadan önce normal boşlukları kaldırır. Kırılmasız boşluklar için, verinizi önce =SUBSTITUTE(A1,CHAR(160),"") ile temizleyin ya da CLEAN() işlevini kullanın.

Filtreli Verilerde Boş Olmayan Huìcreleri Sayın

İnsanların çoğunu şaşırtan bir durum: verinizi filtrelersiniz, bir COUNTA() formülü yazarsınız ve sonuç gizli satırları da içerir. Bunun nedeni, COUNTA()'nın filtreleri umursamamasıdır. Görüp görmediğinize bakmadan aralıktaki her şeyi sayar.

SUBTOTAL(): Filtre farkında olan alternatif

SUBTOTAL() bunu düzeltir. Filtrelere saygı göstererek sayar.

=SUBTOTAL(3,D2:D11)Altyapı:

3 değeri SUBTOTAL() işlevine COUNTA() tarzı bir sayım yapmasını söyler. Farkı: filtreyle gizlenen satırları atlar.

İşlev numaraları: 3 vs. 103

SUBTOTAL() iki moda sahiptir:

• 3 numaralı işlev: AutoFilter ile gizlenen satırları atlar, ancak elle gizlenen satırları yine de sayar.
• 103 numaralı işlev: GÖRÜNMEYEN tÜM satırları (filtre ve elle gizlenen) atlar.

=SUBTOTAL(3,D2:D11)    -- Skips AutoFilter rows
=SUBTOTAL(103,D2:D11)  -- Skips ALL hidden rowsAltyapı:

Çoğu zaman ihtiyacınız olan 3 tür. Sadece satırları elle gizlediyseniz (sağ tıkla > Gizle) ve bunları da hariç tutmak istiyorsanız 103 kullanın.

AGGREGATE(): Dağınık veriler için alternatif

Filtreli verilerinizde hata değerleri varsa, SUBTOTAL() hata verebilir. AGGREGATE() bunu daha iyi yönetir:

=AGGREGATE(3,7,D2:D11)Altyapı:

3 işlev numarasıdır (COUNTA()) ve 7 hem gizli satırları hem de hataları yoksaymasını söyler. Bu, dağınık veriler için en güvenli seçenektir; ancak çoğu kişiye gerekmez.

Excel Tablolarında Boş Olmayan Huìcreleri Sayma

Veriniz bir Excel Tablosu içindeyse (Ekle > Tablo seçin ya da Ctrl+T tuşlarına basın), huücre adresleri yerine yapılandırılmış başvurular kullanabilirsiniz. Bu, formüllerinizi daha açık hale getirir ve tablo büyüdüğünde otomatik güncellenirler.

COUNTA() ile yapılandırılmış başvurular

=COUNTA(C2:C9) yazmak yerine, şöyle yazarsınız:

=COUNTA(TaskTracker[Assigned To])Altyapı:

Bu, TaskTracker tablomdaki Assigned To sütunundaki boş olmayan huücreleri sayar. Başvuru, başlık satırını otomatik olarak atlar ve yeni satırlar eklediğimde genileir.

COUNTIFS() ile yapılandırılmış başvurları birleştirmek

COUNTIFS() tablolarla harika çalışır:

=COUNTIFS(TaskTracker[Status],"Done",TaskTracker[Completion Date],"<>")Altyapı:

Bu, "Done" olarak işaretlenmiş ve tamamlanma tarihi olan görevleri sayar. İzleyicimde bu, tüm tamamlanmış görevlerin tarihi olduğu için 4 sonucunu verir.

Neden tablolar saymada daha iyidir

Tabloları kullanışlı kılan iki şey var. İlk olarak, yapılandırılmış başvurular başlık satırını otomatik olarak atlar; bu nedenle 2. satırdan başlama derdiniz olmaz. İkincisi, tabloya yeni bir satır eklediğinizde, yapılandırılmış başvuru kullanan tüm formüller yeni veriyi otomatik olarak kapsar. Aralıkları manuelle güncellemenize gerek kalmaz.

Bu, gün geçtikçe büyüyen veriler için idealdir; günlükler, izleyiciler veya düzenli olarak içe aktardığınız veriler gibi.

Boş Olmayan Huìcreleri Sayarken Yaygın Sorunlar

Saysı beklenenden yüksek

Yaşanan: COUNTA() 6 diyor ama siz yalnızca 4 veri içeren huücre görüyorsunuz.

Neden: İki huücrede "" döndüren formüller var. Az önce belirttiğim gibi, bunlar boş görünür ama teknik olarak boş değildir.

Çözüm: =SUMPRODUCT(--(LEN(range)>0)) kullanın; COUNTA() yerine. Bu, yalnızca görünür içeriği olan huücreleri sayar ve formül kaynaklı boşları yoksayar.

Gizli boşluklar sayımı bozuyor

Yaşanan: Bir huücre boş görünür, ama COUNTA() onu sayar ve ISBLANK() FALSE der.

Neden: Huücrede boşluk karakterleri var; muhtemelen bir web sitesinden veya başka bir dosyadan kopyalarken geldiler.

Çözüm: =LEN(A1) ile kontrol edin. Huücre boş görünürken 0'dan büyük bir değer dönerse gizli karakterleriniz var demektir. Verinizi temizleyin: önce =TRIM(A1) kullanın; ya da daha katı formülü uygulayın: =SUMPRODUCT(--(LEN(TRIM(range))>0)).

Hata değerleri, istemediğinizde sayılıyor

Yaşanan: Boş olmayan huìcreleri saymak istiyorsunuz ama #DIV/0! veya #N/A gibi hatalar da dahil oluyor.

Neden: Hem COUNTA() hem de COUNTIF(range,"<>") hataları boş olmayan sayar.

Çözüm: Verinizde filtreler de varsa, filtreli veriler bölümünde bahsettiğim gibi =AGGREGATE(3,7,range) kullanın. 7 hem gizli satırları hem hataları atlar. Filtrelenmemiş veriler için =SUMPRODUCT(--(LEN(IFERROR(range,""))>0)) kullanın.

Sayım filtrelemeden sonra güncellenmiyor

Yaşanan: Verinizi filtreliyorsunuz ve COUNTA() hala filtrelenmemiş sayıyı gösteriyor.

Neden: COUNTA() filtreleri tamamen yoksayar.

Çözüm: Filtreli veriler bölümünde anlattığım gibi =SUBTOTAL(3,range) kullanın. Görünene göre otomatik ayarlanır.

Boş Olmayan Huìcreleri Sayma İçin En İyi Uygulamalar

Bu sorunlarla sayamayacağım kadar çok kez uğraştıktan sonra artık şunları yapıyorum:

• Temiz, basit veri kümeleleri için COUNTA() ile başlayın. Hızlı ve düzdür.

• Daha sonra koşullar ekleyebilecekseniz COUNTIF(range,"<>") kullanın. Çoklu kriter için doğal olarak COUNTIFS()'e bağlanır.

• Sayıma güvenmeden önce formül boşlarını test edin. Birkaç "boş" huücreyi =LEN(cell) ile yerinde kontrol edin. 0 dışında bir şey görürseniz, SUMPRODUCT()+LEN() yaklaşımına geçin.

• Filtreli veriler için SUBTOTAL(3,range) kullanın. Filtrelere saygı gösteren tek sayma işlevi budur.

• İçe aktırılan veriyi önce temizleyin. Web siteleri veya diğer kaynaklardan gelen verilerde TRIM() çalıştırın ve CHAR(160) olup olmadığını kontrol edin.

• Büyüyen veri kümeleleri için Excel Tabloları kullanın. Yapılandırılmış başvurular, satır eklediğinizde otomatik güncellenir.

• Karmaşık formülleri belgeleyin. SUMPRODUCT(--(LEN(TRIM(range))>0)) kullanıyorsanız, nedenini açıklayan bir not ekleyin. Gelecekteki siz minnettar olacaksınız.

Sonuç

Excel, boş olmayan huìcreleri saymak için birkaç yol sunar ve doğru olan, duruma bağlıdır. COUNTA() çoğu durumda işe yarar. COUNTIF() ve COUNTIFS() koşullar eklemenizi sağlar. SUBTOTAL() filtrelere saygı gösterir. SUMPRODUCT() ve LEN() ise formüllerin görünmez boşlar yarattığı durumlarda yedek çözücünüzdür.

Püf nokta, formülleri ezberlemek değil. Excel'in "boş"u ne sandığını anlamaktır. "" döndüren bir formül boş görünür ama değildir. Boşluk karakteri görünmezdir ama sayılır. Bu farklılıkları kavradığınızda, artık yanlış sayılarla karşılaşmazsınız.

Excel becerilerinizi geliştirmeye devam etmek isterseniz, Data Analysis in Excel kursumuza göz atın. Burada, bu yazıdaki her şeyle iyi uyum sağlayan formüller, PivotTable'lar ve veri temizleme konularını içerir.