Формула Excel: Підрахуйте загальну кількість слів у комірці -

Зміст

Загальна формула

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

Резюме

Щоб підрахувати загальну кількість слів у комірці, ви можете використовувати формулу на основі функцій LEN та SUBSTITUTE за допомогою функції TRIM. У наведеному прикладі формула у клітинці C5, скопійована вниз, має вигляд:

=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+1

Формула повертає кількість слів у клітинці B5.

Пояснення

Excel не має спеціальної функції для підрахунку слів у комірці. Однак, з невеликою винахідливістю, ви можете створити таку формулу, використовуючи функції ЗАМІНИТЬ і ЛІН, за допомогою TRIM, як показано в прикладі. На високому рівні ця формула використовує функцію LEN для підрахунку кількості символів у комірці з пробілами та без, а потім використовує різницю, щоб з’ясувати кількість слів. Це працює, оскільки кількість слів дорівнює кількості пробілів + 1, якщо між кожним словом є один пробіл.

Перша частина формули підраховує символи в комірці B5 після видалення зайвого місця:

=LEN(TRIM(B5)) // normalize space, count characters

Усередині LEN функція TRIM спочатку видаляє зайві пробіли між словами або на початку або в кінці тексту. Це важливо, оскільки будь-які зайві пробіли скидають кількість слів. У цьому випадку немає зайвих пробілів, тому TRIM повертає оригінальний текст безпосередньо до функції LEN, яка повертає 30:

LEN("All Quiet on the Western Front") // returns 30

На даний момент ми маємо:

=30-LEN(SUBSTITUTE(B5," ",""))+1

Далі ми використовуємо функцію ЗАМІНИТЬ, щоб видалити з тексту всі пробіли:

SUBSTITUTE(B5," ","") // strip all space

Примітка SUBSTITUTE налаштовано на пошук пробілу ("") та заміну на порожній рядок (""). За замовчуванням SUBSTITUTE замінить усі пробіли. Результат доставляється безпосередньо до функції LEN, яка повертає рахунок:

LEN("AllQuietontheWesternFront") // returns 25

LEN повертає 25, кількість символів, що залишається після видалення всього місця. Тепер ми можемо спростити формулу, щоб:

=30-25+1 // returns 6

який повертає 6 як кінцевий результат, кількість слів у комірці B5.

Робота з порожніми клітинками

Формула у прикладі поверне 1, навіть якщо клітинка порожня або містить лише пробіл. Це відбувається тому, що ми додаємо 1 безумовно, після підрахунку пробілів між словами. Для захисту від цієї проблеми ви можете адаптувати формулу, як показано нижче:

=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(LEN(TRIM(B5))>0)

Зверніть увагу, що ми замінили 1 на такий вираз:

LEN(TRIM(B5))>0

Цей код спочатку обрізає B5, потім перевіряє довжину. Якщо B5 містить текст, LEN повертає додатне число, а вираз повертає TRUE. Якщо B5 порожній або містить лише пробіл, TRIM повертає порожній рядок ("") до LEN. У цьому випадку LEN повертає нуль (0), а вираз повертає FALSE. Фокус у тому, що TRUE та FALSE оцінюються як 1 та нуль відповідно, коли беруть участь у будь-якій математичній операції. Як результат, вираз додає 1 лише тоді, коли в B5 є текст. В іншому випадку він додає нуль (0). Цю логіку також можна записати за допомогою оператора функції IF наступним чином: