Як отримати кількість слів у Excel (за допомогою простих формул)

Хочете отримати кількість слів у Excel? Вірте чи ні, але Excel не має вбудованого лічильника слів.

Але не хвилюйтесь.

Прохолодна купа функцій Excel (або трохи VBA, якщо вам захочеться) може легко зробити це за вас.

У цьому уроці я покажу кілька способів підрахунку слів у Excel за допомогою простих формул. Наприкінці також буде розглянуто техніку створення власної формули за допомогою VBA, яка швидко дасть вам кількість слів будь -якого тексту в будь -якій клітинці.

Формула для отримання кількості слів у Excel

Перш ніж я дам точну формулу, давайте швидко розглянемо логіку, щоб отримати кількість слів.

Припустимо, у мене є речення, як показано нижче, для якого я хочу отримати кількість слів.

Хоча Excel не може підрахувати кількість слів, він може підрахувати кількість пробілів у реченні.

Отже, щоб отримати кількість слів, ми можемо підрахувати ці пробіли замість слів і додати 1 до загальної кількості (оскільки кількість пробілів буде на одну меншою за кількість слів).

Тепер може бути дві можливості:

1. Між кожним словом є один пробіл
2. Між словами багато пробілів.

Тож давайте подивимось, як порахувати загальну кількість слів у кожному випадку.

Приклад 1 - Коли між словами є один пробіл

Скажімо, у мене в комірці А1 є такий текст: Випусти кішку з мішка

Щоб підрахувати кількість слів, я б використав формулу:

= LEN (A1) -LEN (ЗАМІНА (A1, "", ""))+1

В результаті це поверне "7".

Ось як працює ця формула:

• LEN (A1) - Ця частина формули повертає 26, що є загальною кількістю символів у тексті у комірці A1. Він включає символи тексту, а також пробіли.
• ЗАМІНА (A1, ”“, ””) - Ця частина формули видаляє всі пробіли з тексту. Отже, результат у цьому випадку буде таким Відпустіть кату на мішок.
• LEN (ЗАМІНА (A1, ”“,“”) - Ця частина формули підраховує загальну кількість символів у тексті без пробілів. Отже, результат цього буде 20.
• LEN (A1) -LEN (ЗАМІНА (A1, ”“,“”)) - Це відніме довжину тексту без пробілів від довжини тексту з пробілами. У наведеному вище прикладі це буде 26-20, що дорівнює 6.
• = LEN (A1) -LEN (ЗАМІНА (A1, ”“,“”))+1 - Ми додаємо 1 до загального результату, оскільки загальна кількість пробілів на один менше загальної кількості слів. Наприклад, є один пробіл у двох словах і два пробіли в трьох словах.

Тепер це добре працює, якщо між словами є лише один пробіл. Але це не спрацює, якщо у вас буде більше одного пробілу між словами.

У цьому випадку використовуйте формулу в наступному прикладі.

Приклад 2: Коли між словами кілька пробілів

Припустимо, у вас є такий текст: Випустіть кота з мішка

У цьому випадку між словами є кілька пробілів.

Щоб отримати кількість слів, нам спочатку потрібно видалити всі зайві пробіли (наприклад, щоб між двома словами було лише один пробіл), а потім підрахувати загальну кількість пробілів.

Ось формула, яка дасть нам потрібну кількість слів:

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

Це подібна формула, використана у наведеному вище прикладі, з невеликими змінами - ми також використовували функцію TRIM.

Функція Excel TRIM видаляє будь -які провідні, кінцеві та зайві пробіли (крім одиничних пробілів між словами).

Решта формули працюють так само (як пояснено у прикладі 1).

Примітка: Якщо між словами немає пробілів, це вважається одним словом.

Використання спеціальної функції VBA для підрахунку слів у Excel

Хоча наведені вище формули чудово працюють, якщо вам потрібно часто обчислювати кількість слів, ви можете використовувати VBA для створення власної функції (її також називають функцією, визначеною користувачем).

Перевага використання користувацької функції полягає в тому, що ви можете створити її один раз, а потім використовувати як будь -яку іншу звичайну функцію Excel. Отже, замість того, щоб створювати довгу складну формулу, як ми зробили у двох прикладах вище, у вас є проста формула, яка бере посилання на клітинку та миттєво дає вам кількість слів.

Ось код, який створить цю спеціальну функцію, щоб отримати кількість слів у Excel.

Функція WordCount (CellRef As Range) Dim TextStrng As String Dim Result () As String Result = Split (WorksheetFunction.Trim (CellRef.Text), "") WordCount = UBound (Result ()) + 1 End Function

Після створення функцію WordCount можна використовувати так само, як і будь -яку іншу звичайну функцію Excel.

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

Як працює ця формула:

Наведений вище код VBA спочатку використовує функцію TRIM, щоб видалити всі початкові, кінцеві та подвійні пробіли з текстового рядка у осередку з посиланням.

Після того, як він має очищений рядок, він використовує функцію SPLIT у VBA, щоб розділити текстовий рядок на основі роздільника, який ми вказали як символ пробілу. Тому кожне слово відокремлюється як збережене як окремий елемент у змінній Result.

Потім ми використовуємо функцію UBOUND для підрахунку загальної кількості елементів, які були збережені у змінних Result. Оскільки VBA має базу 0, нам потрібно додати 1, щоб отримати загальну кількість слів.

Це означає, що результат (0) зберігає перше слово, результат (1) зберігає друге слово тощо. Оскільки цей підрахунок починається з 0, нам потрібно додати 1, щоб отримати реальну кількість слів.

Де розмістити цей код?

При створенні користувацької функції вам потрібно розмістити код у редакторі VB книги (це задній кінець книги, де ви можете написати код для автоматизації завдань та створення власних функцій).

Нижче наведено кроки для розміщення коду для функції "GetNumeric" у книзі.

1. Перейдіть на вкладку Розробник.
2. Натисніть на опцію Visual Basic. Це відкриє редактор VB у бекенді.
3. На панелі «Провідник проектів» у редакторі VB клацніть правою кнопкою миші будь-який об’єкт книги, до якого потрібно вставити код. Якщо ви не бачите Провідник проектів, перейдіть на вкладку Вигляд і натисніть Провідник проектів.
4. Перейдіть до пункту Вставити та натисніть Модуль. Це додасть об’єкт модуля до вашої книги.
5. Скопіюйте та вставте код у вікно модуля.

Після того, як ви скопіювали код у вікні коду, ви можете повернутися до свого робочого аркуша та використовувати цю функцію так само, як і будь -яку іншу звичайну функцію Excel.

Просто наберіть = Слово і він покаже вам формулу у списку.

Він бере один аргумент, який є посиланням на клітинку, і миттєво дає вам кількість слів у ньому.