Возможности !SEMTools

Как посчитать количество цифр в ячейках, даже если помимо цифр в них есть другие символы? Как посчитать ячейки, содержащие только цифры? А ячейки, содержащие цифры и другие символы?

На эти и другие вопросы вы найдете ответы в этой статье.

Посчитать цифры внутри ячеек

Если число целое, число символов в ячейке будет равно числу цифр. В таком случае формула будет:

=ДЛСТР(A1)

Но что, если число дробное (с запятой), или содержит буквенные символы/пунктуацию?

Тут поможет формула массива на основе функций ПОДСТАВИТЬ и ДЛСТР:

Формула массива для подсчета цифр внутри ячейки
Формула массива для подсчета цифр внутри ячейки

А вот сам текст формулы, который можно скопировать:

=СУММ(ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;{1;2;3;4;5;6;7;8;9;0};"")))

Механика формулы довольно проста:

  1. Функция ПОДСТАВИТЬ в исходной строке поочередно заменяет на пустоту каждое из значений числового массива
  2. Функция ДЛСТР в каждом из случаев возвращает длину результирующей строки
  3. Из исходной длины вычитается эта длина (если цифры в строке не было, результат будет нулевым)
  4. Производится суммирование всех результатов

Посчитать числовые ячейки (содержащие только числа)

Чрезвычайно ненадежное базовое решение, работающее только в случаях, когда в данных действительно содержатся только числа – это использовать функцию СЧЁТ. Пример:

Формула с функцией СЧЁТ для подсчета чисел в диапазоне
Функция СЧЁТ считает числа в диапазоне
=СЧЁТ(A2:A6)

Обратите внимание, функция не учтет ячейки даже если они просто форматированы как текст. Вот одно из более надежных решений, тоже формула массива:

формула для подсчета числовых ячеек с числами в текстовом формате
Надежная формула для подсчета числовых ячеек, не игнорирующая числа в текстовом формате
=СУММ(1-ЕОШИБКА(ЗНАК(A2:A6)))

Формулу нужно вводить сочетанием Ctrl + Shift + Enter, иначе она не сработает.

Механика этой формулы:

  1. Для каждой из ячеек диапазона функция ЗНАК возвращает числовое значение или ошибку. Особенность функции в том, что в отличие от функции СЧЁТ, она игнорирует формат ячейки
  2. Если в ячейке действительно не число, а текст или текст + цифры, функция выдаст ошибку, и ее подхватит функция ЕОШИБКА, выдавая ИСТИНА в случае ошибки и ЛОЖЬ в случае ее отсутствия
  3. Как обнулить ошибки и сделать единицами их отсутствие? Очень просто — вычесть их из единицы. ИСТИНА эквивалентна единице и вернет в таком случае 0, а ЛОЖЬ равна нулю и вернет 1
  4. Просуммируем итоговые результаты

Посчитать в диапазоне ячейки, содержащие цифры

Если ячейки могут содержать не только цифры, но и текстовые символы, задача сводится к тому, чтобы сначала определить наличие цифр в ячейках. Ниже формула из соответствующей статьи:

=СЧЁТ(ПОИСК({1:2:3:4:5:6:7:8:9:0};A1))>0

А далее — уже дело техники! Задача лишь в том, чтобы посчитать количество ячеек, содержащих ИСТИНА.

Например, можно функцией СЧЁТЕСЛИ:

Выявить ячейки с цифрами и посчитать их СЧЁТЕСЛИ
Выявляем ячейки, содержащие цифры, и считаем их
=СЧЁТЕСЛИ(B2:B6;ИСТИНА)

Можно использовать формулу массива, с помощью двойного отрицания диапазона сделать ИСТИНЫ и ЛОЖЬ единицами и нулями соответственно, а потом просуммировать получившиеся значения массива. Не забудьте про Ctrl + Shift + Enter при вводе формулы.

=СУММ(--(B2:B6))

чтобы в ячейке появились характерные для формул массива фигурные скобки:

Выявить ячейки с цифрами и посчитать их формулой массива
Двойное отрицание массива и формула СУММ

Заключение

Итак, надеюсь, вам помогла эта статья и вы успешно решите любую задачу по подсчету цифр в ячейках и подсчету самих этих ячеек. Хотите поблагодарить автора? Приобретите мою надстройку для Excel — она ускоряет выполнение множества рутинных задач.

Оставить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

*