Excel має деякі дивовижні текстові функції, які можуть вам допомогти при роботі з текстовими даними.
У деяких випадках вам може знадобитися розрахувати загальну кількість символів у клітині/діапазоні або кількість разів, коли конкретний символ зустрічається в клітині.
Хоча є функція LEN, яка може підраховувати кількість символів у комірці, решту ви також можете зробити за допомогою комбінації формул (як ми побачимо далі у прикладах).
У цьому підручнику я розгляну різні приклади, де можна підрахувати загальну чи конкретну кількість символів у клітинці/діапазоні в Excel.
Порахувати всі символи в клітинці
Якщо ви просто хочете отримати загальну кількість всіх символів у комірці, ви можете скористатися функцією LEN.
Функція LEN бере один аргумент, який може бути текстом у подвійних лапках або посиланням на клітинку на клітинку з текстом.
Наприклад, припустимо, що у вас є набір даних, як показано нижче, і ви хочете підрахувати загальну кількість символів у кожній клітинці:
Нижче наведена формула, яка дозволить це зробити:
= LEN (A2)
Сама по собі функція LEN може виглядати не так багато, але при поєднанні її з іншими формулами вона може зробити деякі чудові речі (наприклад, отримати кількість слів у клітинці або розділити ім’я та прізвище).
Примітка: Функція LEN підраховує всі символи в комірці, будь то спеціальний символ, цифри, розділові знаки та пробіли (провідні, кінцеві та подвійні пробіли між словами).
Оскільки функція LEN враховує кожен символ у клітинці, іноді ви можете отримати неправильний результат, якщо у вас є додаткові пробіли.
Наприклад, у наведеному нижче випадку функція LEN повертає 25 для тексту в комірці A1, тоді як це повинно було бути 22. Але оскільки вона також рахує зайві символи пробілу, ви отримуєте неправильний результат.
Щоб уникнути підрахунку зайвих пробілів, спочатку можна скористатися функцією TRIM, щоб видалити усі провідні, кінцеві та подвійні пробіли, а потім скористатися функцією LEN, щоб отримати справжню кількість слів.
Нижченаведена формула зробить це:
= ДОГОЛОВКА (ТРИМ (A2))
Порахуйте всі символи в діапазоні клітинок
Ви також можете використовувати функцію LEN для підрахунку загальної кількості символів у всьому діапазоні.
Наприклад, припустимо, що у нас однаковий набір даних, і цього разу, замість того, щоб отримувати кількість символів у кожній комірці, я хочу знати, скільки їх у всьому діапазоні.
Ви можете зробити це за формулою нижче:
= ПОВНІННЯ (Об’єктив (A2: A7)))
Дозвольте мені пояснити, як працює ця формула.
У наведеній вище формулі частина функції LEN займає весь діапазон клітинок і підраховує символи в кожній клітинці.
Результатом функції LEN буде такий:
{22;21;23;23;23;31}
Кожне з цих чисел позначає кількість символів у клітинці.
І коли ви використовуєте з ним функцію SUMPRODUCT, вона просто додасть усі ці цифри.
Тепер, якщо вам цікаво, чому ви не можете використовувати SUM замість SUMPRODUCT, причина в тому, що це масив, і SUMPRODUCT може обробляти масив, але функція SUM не може.
Однак, якщо ви все ще хочете використовувати SUM, ви можете використовувати формулу нижче (але пам’ятайте, що вам потрібно використовувати Control + Shift + Enter, щоб отримати результат замість звичайного Enter)
= SUM (LEN (A2: A7))
Підрахувати певні символи в клітинці
Як я вже згадував, справжня корисність функції LEN - це її використання у поєднанні з іншими формулами.
І якщо ви хочете підрахувати певні символи в клітинці (це може бути буква, цифра, спеціальний символ або пробіл), ви можете зробити це за допомогою комбінації формул.
Наприклад, припустимо, що у вас є набір даних, як показано нижче, і ви хочете підрахувати загальну кількість слів у кожній клітинці.
Хоча немає вбудованої формули для визначення кількості слів, ви можете підрахувати пробіли, а потім використовувати її, щоб дізнатися загальну кількість слів у клітинці.
Нижче наведена формула, яка дасть вам загальну кількість символів пробілу в осередку:
= LEN (A2) -LEN (ЗАМІНА (A2, "", ""))+1
Наведена вище формула підраховує загальну кількість символів пробілу, а потім додає 1 до цього числа, щоб отримати кількість слів.
Ось як працює ця формула:
- Функція SUBSTITUTE використовується для заміни всіх символів пробілу порожнім. Потім функція LEN використовується для підрахунку загальної кількості символів, коли немає пробілів.
- Результат об'єктива (ЗАМІНА (A2, "", "")) потім віднімається від LEN (A2). Це дає нам загальну кількість символів пробілу, які є у клітинці.
- 1 додається у формулу, і загальна кількість слів буде на одну більше загальної кількості символів пробілу (оскільки два слова розділені одним символом).
Зауважте, що якщо є провідні, кінцеві або подвійні пробіли, ви отримаєте неправильний результат. У такому випадку найкраще використовувати функцію TRIM разом із функцією LEN.
Ви також можете використовувати ту ж логіку, щоб знайти певний символ або слово чи фразу в клітині.
Наприклад, припустимо, що у мене є набір даних, як показано нижче, де у мене є різні партії, де кожна партія має алфавіт та номер для її представлення (наприклад, A1, J2 тощо)
Нижче наведена формула, яка дасть вам загальну кількість разів, коли кожен місяць створювалася партія з алфавітом А:
= LEN (B2) -LEN (ЗАМІНА (B2, "A", ""))
У наведеній вище формулі знову використовується та сама логіка - знайдіть довжину тексту в клітинці з символом і без нього, який потрібно підрахувати, а потім візьміть різницю цих двох.
У наведеній вище формулі я жорстко закодував символ, який я хочу підрахувати, але ви також можете помістити його в клітинку, а потім використовувати посилання на клітинку. Це робить його більш зручним, оскільки формула оновлюватиметься під час наступного змінення тексту в комірці.
Порахувати конкретні символи за формулою, що не враховує регістр
Існує одна проблема з формулою, яка використовується для підрахунку певних символів у клітинці.
Функція SUBSTITUTE чутлива до регістру. Це означає, що "А" не дорівнює "а". Ось чому ви отримуєте неправильний результат у клітинці С5 (результат мав би бути 3).
Отже, як ви можете отримати кількість символів конкретного персонажа, якщо це могло бути в будь -якому випадку (нижній чи верхній).
Ви робите це, роблячи регістр формули нечутливим. Хоча ви можете перейти до складної формули, я просто додав кількість символів обох регістрів (малі та великі літери А).
= LEN (B2) -LEN (ЗАМІННИК (B2, "A", ""))+LEN (B2) -LEN (ЗАМІННИК (B2, "a", ""))
Підрахунок символів/цифр до і після десяткового числа
Я не знаю чому, але це поширений запит, який я отримую від своїх читачів і бачив на багатьох форумах, таких як цей
Припустимо, у вас є набір даних, як показано нижче, і ви хочете підрахувати символи перед десятковим числом і після десяткового.
Нижче наведені формули, які дозволять це зробити.
Підрахувати символи/числа перед десятковим числом:
= LEN (INT (A2))
Підрахувати символи/числа після десяткового дробу:
= LEN (A2) -FIND (".", A2)
Зверніть увагу, що ці формули призначені лише для значущих цифр у комірці. Якщо у вас є нулі на початку або в кінці або ви використовували спеціальне форматування чисел для відображення більших/менших чисел, вищенаведені формули все одно даватимуть вам значні цифри до і після коми.
Отже, це деякі сценарії, коли ви можете використовувати формули для підрахунку символів у клітинці або діапазоні клітинок у Excel.
Сподіваюся, що підручник був вам корисний!
