Formula Excel untuk mengira sel dengan teks: sel -sel, spesifik atau ditapis

Bagaimana cara mengira sel yang mengandungi teks dalam Excel?

Excel pada asalnya direka untuk memproses nombor, tetapi hari ini kami juga sering menggunakannya untuk menyimpan dan memproses teks. Ingin tahu berapa banyak sel dalam lembaran kerja yang mengandungi teks? Microsoft Excel menyediakan beberapa fungsi untuk mencapai matlamat ini. Fungsi mana yang harus digunakan? Ia bergantung kepada keadaan. Dalam tutorial ini, anda akan mendapati pelbagai formula dan masa terbaik untuk menggunakan setiap formula.

• Sel statistik yang mengandungi teks
• Abaikan rentetan dan ruang kosong dan mengira sel yang mengandungi teks
• Statistik sel yang mengandungi teks tertentu
• Statistik yang ditapis sel yang mengandungi teks

Cara Mengira Bilangan Sel yang Mengandung Teks Dalam Excel

Terdapat dua formula asas untuk mencari bilangan sel yang mengandungi sebarang rentetan teks atau watak dalam julat tertentu.

Gunakan formula Countif untuk mengira semua sel yang mengandungi teks

Apabila anda ingin mencari bilangan sel dalam excel yang mengandungi teks, menggunakan asterisk dalam parameter bersyarat , penyelesaian terbaik dan paling mudah ialah:

COUNTIF(*范围*, "\*") Oleh kerana asterisk (*) adalah kad liar yang sepadan dengan urutan watak, formula akan mengira semua sel yang mengandungi sebarang teks.

Sel statistik yang mengandungi teks menggunakan formula sumroduct

Satu lagi cara untuk mendapatkan bilangan sel yang mengandungi teks adalah menggunakan fungsi sumroduct dan istext dalam kombinasi:

SUMPRODUCT(--ISTEXT(*范围*)) atau

SUMPRODUCT(ISTEXT(*范围*)*1) Fungsi istext memeriksa sama ada setiap sel dalam julat yang ditentukan mengandungi sebarang aksara teks dan mengembalikan pelbagai nilai (sel yang mengandungi sel) dan nilai -nilai palsu (sel lain). Operator dua kali ganda (-) atau operasi pendaraban yang benar dan palsu kepada 1 dan 0 masing-masing, dengan itu menghasilkan pelbagai 1 dan 0. Fungsi sumproduct merangkum semua elemen array dan mengembalikan bilangan 1, iaitu bilangan sel yang mengandungi teks.

Untuk lebih memahami bagaimana formula ini berfungsi, lihat nilai mana yang dikira dan yang tidak dikira:

Kira kandungan	Kandungan yang tidak terkira
- Sel yang mengandungi teks - watak khas - Nombor yang diformatkan sebagai teks - Sel yang boleh dianggap kosong tetapi mengandungi rentetan kosong (""), apostrophes ('), ruang, atau watak yang tidak kelihatan	- nombor - tarikh - Nilai logik yang benar dan palsu - kesilapan - Sel kosong

Sebagai contoh, untuk mengira sel yang mengandungi teks dalam julat A2: A10 (tidak termasuk nombor, tarikh, nilai logik, kesilapan, dan sel kosong), anda boleh menggunakan salah satu formula berikut:

=COUNTIF(A2:A10, "*")

=SUMPRODUCT(--ISTEXT(A2:A10))

=SUMPRODUCT(ISTEXT(A2:A10)*1)

Angka berikut menunjukkan hasilnya:

Abaikan ruang dan rentetan kosong dan mengira sel yang mengandungi teks

Formula yang dibincangkan di atas mengira semua sel yang mengandungi sebarang aksara teks. Walau bagaimanapun, dalam beberapa kes, ini boleh mengelirukan kerana sesetengah sel mungkin kelihatan kosong tetapi sebenarnya mengandungi watak -watak yang tidak dapat dilihat oleh mata manusia, seperti rentetan kosong, apostrophes, ruang, rehat garis, dan lain -lain sebagai hasilnya, sel -sel yang boleh dianggap sebagai kosong akan dikira oleh formula, menyebabkan pengguna bimbang tentang cuba memikirkan sebabnya.

Untuk mengecualikan sel -sel kosong "positif positif" dari kiraan, gunakan fungsi countifs dan gunakan watak "tidak termasuk" dalam keadaan kedua.

Sebagai contoh, untuk mengira sel yang mengandungi teks dalam julat A2: A7 (abaikan sel yang mengandungi aksara ruang ), gunakan formula berikut:

=COUNTIFS(A2:A7,"*", A2:A7, " ")

Jika julat sasaran mengandungi sebarang nombor yang didorong oleh formula, sesetengah formula boleh menghasilkan rentetan kosong (""). Untuk mengabaikan sel -sel yang mengandungi rentetan kosong , ganti "*" dengan "*?*" Dalam parameter kriteria1 :

=COUNTIFS(A2:A9,"*?*", A2:A9, " ")

Tanda tanya yang dilampirkan dengan asterisk menunjukkan bahawa terdapat sekurang -kurangnya satu watak teks dalam sel. Oleh kerana rentetan kosong tidak mempunyai aksara, ia tidak memenuhi kriteria dan tidak akan dikira. Sel kosong bermula dengan apostrophe (') juga tidak dikira.

Dalam angka di bawah, terdapat ruang dalam A7, apostrophe dalam A8, dan rentetan kosong dalam A9 (= ""). Formula kami mengabaikan semua sel ini dan mengembalikan kiraan sel teks 3:

Cara mengira sel yang mengandungi teks tertentu dalam excel

Untuk mendapatkan bilangan sel yang mengandungi teks atau watak tertentu, hanya berikan teks dalam parameter bersyarat fungsi COUNTIF. Contoh berikut menerangkan kehalusan.

Untuk memadankan teks sampel dengan tepat, masukkan teks penuh dalam petikan:

COUNTIF(*范围*, "*文本*") Mengira sel -sel yang sepadan dengan sebahagian , letakkan teks antara dua asterisk, yang mewakili bilangan aksara sebelum dan selepas teks:

COUNTIF(*范围*, "\**文本*\*") sebagai contoh, untuk mengetahui berapa banyak sel dalam julat A2: A7 mengandungi perkataan "pisang", gunakan formula berikut:

=COUNTIF(A2:A7, "bananas")

Untuk mengira semua sel yang mengandungi "pisang" sebagai sebahagian daripada kandungan mereka (tanpa mengira lokasi), gunakan formula berikut:

=COUNTIF(A2:A7, "*bananas*")

Untuk menjadikan formula lebih mudah digunakan, anda boleh meletakkan keadaan dalam sel yang telah ditetapkan, seperti D2, dan letakkan rujukan sel dalam parameter kedua:

=COUNTIF(A2:A7, D2)

Berdasarkan input dalam D2, formula dapat sepenuhnya atau sebahagiannya sepadan dengan teks contoh:

• Untuk perlawanan yang tepat, taipkan perkataan atau frasa yang sama sebagai perkataan penuh atau frasa yang dipaparkan dalam jadual sumber, seperti pisang .
• Untuk perlawanan separa, taipkan teks sampel yang dikelilingi oleh aksara wildcard, seperti * pisang * .

Oleh kerana formula itu tidak sensitif , anda tidak perlu bimbang tentang kes surat, yang bermaksud * pisang * juga ok.

Sebagai alternatif, untuk mengira sel yang sepadan dengan sebahagian , menyertai rujukan sel dan watak wildcard, sebagai contoh:

=COUNTIF(A2:A7, "*"&D2&"*")

Untuk maklumat lanjut, lihat cara mengira sel yang mengandungi teks tertentu dalam Excel.

Cara mengira sel yang ditapis yang mengandungi teks dalam excel

Apabila menggunakan penapis Excel untuk memaparkan hanya data yang relevan untuk masa yang diberikan, kadang -kadang anda mungkin perlu mengira sel -sel yang dapat dilihat yang mengandungi teks . Malangnya, tidak ada penyelesaian satu klik untuk tugas ini, tetapi contoh berikut akan membimbing anda melalui langkah-langkah.

Katakan anda mempunyai jadual seperti yang ditunjukkan dalam angka berikut. Sesetengah penyertaan diekstrak dari pangkalan data yang lebih besar menggunakan formula dan beberapa kesilapan berlaku dalam proses. Anda ingin mencari jumlah item dalam lajur A. Formula COUNTIF yang kami gunakan untuk mengira sel yang mengandungi teks sangat berkesan apabila semua baris dapat dilihat:

=COUNTIF(A2:A10, "*")

Sekarang anda menyempitkan senarai berdasarkan kriteria tertentu, seperti menapis item dengan beberapa lebih daripada 10. Persoalannya - berapa banyak projek yang tersisa?

Untuk mengira sel yang ditapis yang mengandungi teks , anda perlu melakukan perkara berikut:

1. Dalam jadual sumber, buat semua baris kelihatan. Untuk melakukan ini, jelaskan semua penapis dan tidak tersembunyi baris tersembunyi.

2. Tambah lajur pembantu yang mengandungi formula subtotal yang menunjukkan sama ada baris telah ditapis. Untuk memproses sel yang ditapis , gunakan 3 untuk parameter fungsi_num : =SUBTOTAL(3, A2)

   Untuk mengenal pasti semua sel tersembunyi (ditapis dan tersembunyi secara manual), masukkan 103 dalam fungsi_num :

   =SUBTOTAL(103, A2)

   Dalam contoh ini, kami ingin mengira sel -sel yang kelihatan yang mengandungi teks , tanpa mengira bagaimana sel -sel lain tersembunyi, jadi kami memasuki formula kedua dalam A2 dan menyalinnya ke A10.

   Untuk sel -sel yang kelihatan, formula mengembalikan 1. Sebaik sahaja anda menyaring atau menyembunyikan baris tertentu secara manual, formula mengembalikan 0 untuk mereka. (Anda tidak akan melihat nol ini kerana ia dikembalikan untuk baris tersembunyi. Untuk memastikan ia berfungsi dengan cara ini, hanya menyalin kandungan sel tersembunyi yang mengandungi formula subtotal ke mana -mana sel yang kelihatan, mis.

   

3. Gunakan fungsi countifs dan dua pasangan kriteria / kriteria yang berbeza untuk mengira sel -sel yang boleh dilihat yang mengandungi teks:

   • Keadaan 1 - Cari sel yang mengandungi sebarang teks ("*") dalam julat A2: A10.
   • Keadaan 2 - Cari 1 dalam julat D2: D10 untuk mengesan sel -sel yang kelihatan.

   =COUNTIFS(A2:A10, "*", D2:D10, 1)

Sekarang anda boleh menapis data yang anda mahukan, dan formula akan memberitahu anda bilangan sel yang ditapis yang mengandungi teks dalam lajur A (3 dalam kes ini):

Jika anda tidak mahu memasukkan lajur tambahan ke dalam lembaran kerja anda, anda memerlukan formula yang lebih panjang untuk menyelesaikan tugas. Pilih sahaja yang anda suka:

=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))), --(ISTEXT(A2:A10)))

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10) - MIN(ROW(A2:A10)),,1)), -- (ISTEXT(A2:A10)))

Pengendali pendaraban juga boleh:

=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))) * (ISTEXT(A2:A10)))

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10)-MIN(ROW(A2:A10)),,1)) * (ISTEXT(A2:A10)))

Formula mana yang hendak digunakan bergantung pada keutamaan peribadi anda - hasilnya sama:

Bagaimana formula ini berfungsi

Formula pertama menggunakan fungsi tidak langsung untuk "memberikan" rujukan tunggal kepada subtotal untuk semua sel dalam julat yang ditentukan. Formula kedua menggunakan gabungan fungsi mengimbangi, baris, dan min untuk mencapai tujuan yang sama.

Fungsi subtotal mengembalikan pelbagai 1 dan 0, di mana 1 mewakili sel yang kelihatan dan 0 mewakili sel -sel tersembunyi (seperti lajur tambahan di atas).

Fungsi istext memeriksa setiap sel dalam A2: A10, dan mengembalikan benar jika sel mengandungi teks, jika tidak, pulangan palsu. Double unary operator (-) memancarkan nilai yang benar dan palsu kepada 1 dan 0. Pada ketika ini, formula adalah seperti berikut:

=SUMPRODUCT({0;1;1;1;0;1;1;0;0}, {1;1;1;0;1;1;0;1;1})

Fungsi sumproduct pertama mengadili unsur -unsur pada kedudukan yang sama dalam dua tatasusunan, dan kemudian merumuskan array yang dihasilkan.

Oleh kerana hasil mendarab dengan sifar adalah sifar, hanya sel yang diwakili oleh 1 dalam kedua -dua tatasusunan mempunyai 1 dalam array akhir.

=SUMPRODUCT({0;1;1;0;0;1;0;0;0})

Bilangan 1 dalam array di atas adalah bilangan sel yang dapat dilihat yang mengandungi teks.

Ini adalah cara mengira sel yang mengandungi teks dalam Excel. Terima kasih kerana membaca, dan berharap dapat melihat anda di blog kami minggu depan!

Fail yang boleh dimuat turun

Formula untuk mengira sel yang mengandungi teks dalam Excel

Atas ialah kandungan terperinci Formula Excel untuk mengira sel dengan teks: sel -sel, spesifik atau ditapis. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!