Как посчитать количество цифр в ячейках, даже если помимо цифр в них есть другие символы? Как посчитать ячейки, содержащие только цифры? А ячейки, содержащие цифры и другие символы?
На эти и другие вопросы вы найдете ответы в этой статье.
Посчитать цифры внутри ячеек
Если число целое, число символов в ячейке будет равно числу цифр. В таком случае формула будет:
Но что, если число дробное (с запятой), или содержит буквенные символы/пунктуацию?
Тут поможет формула массива на основе функций ПОДСТАВИТЬ и ДЛСТР:
А вот сам текст формулы, который можно скопировать:
=СУММ(ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;{1;2;3;4;5;6;7;8;9;0};"")))
Механика формулы довольно проста:
- Функция ПОДСТАВИТЬ в исходной строке поочередно заменяет на пустоту каждое из значений числового массива
- Функция ДЛСТР в каждом из случаев возвращает длину результирующей строки
- Из исходной длины вычитается эта длина (если цифры в строке не было, результат будет нулевым)
- Производится суммирование всех результатов
Посчитать числовые ячейки (содержащие только числа)
Чрезвычайно ненадежное базовое решение, работающее только в случаях, когда в данных действительно содержатся только числа – это использовать функцию СЧЁТ. Пример:
Обратите внимание, функция не учтет ячейки даже если они просто форматированы как текст. Вот одно из более надежных решений, тоже формула массива:
=СУММ(1-ЕОШИБКА(ЗНАК(A2:A6)))
Формулу нужно вводить сочетанием Ctrl + Shift + Enter, иначе она не сработает.
Механика этой формулы:
- Для каждой из ячеек диапазона функция ЗНАК возвращает числовое значение или ошибку. Особенность функции в том, что в отличие от функции СЧЁТ, она игнорирует формат ячейки
- Если в ячейке действительно не число, а текст или текст + цифры, функция выдаст ошибку, и ее подхватит функция ЕОШИБКА, выдавая ИСТИНА в случае ошибки и ЛОЖЬ в случае ее отсутствия
- Как обнулить ошибки и сделать единицами их отсутствие? Очень просто — вычесть их из единицы. ИСТИНА эквивалентна единице и вернет в таком случае 0, а ЛОЖЬ равна нулю и вернет 1
- Просуммируем итоговые результаты
Посчитать в диапазоне ячейки, содержащие цифры
Если ячейки могут содержать не только цифры, но и текстовые символы, задача сводится к тому, чтобы сначала определить наличие цифр в ячейках. Ниже формула из соответствующей статьи:
=СЧЁТ(ПОИСК({1:2:3:4:5:6:7:8:9:0};A1))>0
А далее — уже дело техники! Задача лишь в том, чтобы посчитать количество ячеек, содержащих ИСТИНА.
Например, можно функцией СЧЁТЕСЛИ:
Можно использовать формулу массива, с помощью двойного отрицания диапазона сделать ИСТИНЫ и ЛОЖЬ единицами и нулями соответственно, а потом просуммировать получившиеся значения массива. Не забудьте про Ctrl + Shift + Enter при вводе формулы.
чтобы в ячейке появились характерные для формул массива фигурные скобки:
Заключение
Итак, надеюсь, вам помогла эта статья и вы успешно решите любую задачу по подсчету цифр в ячейках и подсчету самих этих ячеек. Хотите поблагодарить автора? Приобретите мою надстройку для Excel — она ускоряет выполнение множества рутинных задач.
Как в excel суммировать ячейки с цифрой и текстом?
т.е.
в ячейке G2 должна получиться сумма всех ячеек строки 2 где есть буква «л»,
а в ячейке H2 сумма всех ячеек строки 2 где есть буква «п»
пример на скрине
-
Вопрос заданболее трёх лет назад
-
9431 просмотр
Для буквы «л» формула такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК(«л»;B4:G4);—ПОДСТАВИТЬ(B4:G4;»л»;»»));0))
Для «п» соответственно такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК(«п»;B4:G4);—ПОДСТАВИТЬ(B4:G4;»п»;»»));0))
Обе — формулы массива. Вводятся одновременным нажатием клавиш Ctrl+Shift+Enter
Пригласить эксперта
-
Показать ещё
Загружается…
15 апр. 2023, в 23:37
1000 руб./в час
15 апр. 2023, в 23:24
25000 руб./за проект
15 апр. 2023, в 21:40
2000 руб./за проект
Минуточку внимания
17 авг. 2022 г.
читать 2 мин
Вы можете использовать следующие методы для суммирования ячеек в Excel, содержащих определенный текст:
Метод 1: суммирование ячеек, содержащих один конкретный текст
=SUMIF( A2:A11 , "*avs*", B2:B11 )
Эта формула суммирует ячейки в диапазоне B2:B11 , где соответствующие ячейки в A2:A11 содержат «avs» в ячейке.
Метод 2: суммирование ячеек, содержащих один из нескольких текстов
=SUMIF( A2:A11 , "*avs*", B2:B11 )+SUMIF( A2:A11 , "*eat*", B2:B11 )
Эта формула суммирует ячейки в диапазоне B2:B11 , где соответствующие ячейки в A2:A11 содержат «avs» или «eat» в ячейке.
В следующих примерах показано, как использовать каждый метод на практике со следующим набором данных в Excel:
Пример 1: сумма ячеек, содержащих один конкретный текст
Мы можем использовать следующую формулу для суммирования значений в столбце « Очки », где название команды содержит «avs» в названии:
=SUMIF( A2:A11 , "*avs*", B2:B11 )
На следующем снимке экрана показано, как использовать эту формулу на практике:
Мы видим, что ячейки, в которых столбец « Команда » содержит «avs», набрали в общей сложности 52 балла.
Мы можем проверить это вручную, просуммировав значения, где столбец Team содержит «avs»:
Сумма Очков Команд, в названии которых есть «avs»: 12 + 5 + 8 + 10 + 17 = 52 .
Пример 2. Суммарные ячейки, содержащие один из нескольких текстов
Мы можем использовать следующую формулу для суммирования значений в столбце « Очки », где название команды содержит «avs» или «eat» в названии:
=SUMIF( A2:A11 , "*avs*", B2:B11 )+SUMIF( A2:A11 , "*eat*", B2:B11 )
На следующем снимке экрана показано, как использовать эту формулу на практике:
Мы видим, что ячейки, в которых столбец Team содержит «avs» или «eat», набрали в общей сложности 65 баллов.
Мы можем проверить это вручную, просуммировав значения, где столбец Team содержит «avs» или «eat»:
Сумма Очков для Команд, в названии которых есть «avs» или «eat»: 12 + 5 + 8 + 10 + 13 + 17 = 65 .
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Excel: как удалить строки с определенным текстом
Excel: как проверить, содержит ли ячейка частичный текст
Excel: как проверить, содержит ли ячейка текст из списка
|
В разных ячейках написаны цифры и буквы вместе. Например: 23д, 15н, 18д и т.д. |
|
|
dude Пользователь Сообщений: 1066 |
если цифры всегда слева |
|
vikttur Пользователь Сообщений: 47199 |
Формула массива: |
|
bobo Пользователь Сообщений: 4 |
выделяем нужные ячейки, правый клик, формат ячеек, (основной «д»)или (основной «км») или (основной «кг») и т.д. можно писать что вы пожелаете. |
|
bobo Пользователь Сообщений: 4 |
{quote}{login=bobo}{date=07.04.2011 08:53}{thema=}{post}выделяем нужные ячейки, правый клик, формат ячеек, все форматы,(основной «д»)или (основной «км») или (основной «кг») и т.д. можно писать что вы пожелаете.{/post}{/quote} |
|
слэн  Пользователь Сообщений: 5192 |
как в формате применить эти «или» ? |
|
А что должно получиться, если 23д+15н? |
|
|
bobo Пользователь Сообщений: 4 |
слово «основной» — это значение ячейки который вбивает пользователь с клавиатуры, добавочное слово (после цифры) — экзель приписывает то слово которое вы написали формат ячеек-все форматы-основной «слово» |
|
bobo Пользователь Сообщений: 4 |
лучше посмотрите в этом файле формат ячеек |
|
{quote}{login=The_Prist}{date=06.04.2011 10:32}{thema=}{post}Странно, что при «простой автосумме выдает ошибку». Если применить формулу =СУММ(A1:A10), то она просто игнорирует ячейки с текстом. {/quote} |
|
|
{quote}{login=vikttur}{date=07.04.2011 08:14}{thema=}{post}Формула массива: |
|
|
{quote}{login=bobo}{date=07.04.2011 08:53}{thema=}{post}выделяем нужные ячейки, правый клик, формат ячеек, (основной «д»)или (основной «км») или (основной «кг») и т.д. можно писать что вы пожелаете.{/post}{/quote} |
|
|
MCH Пользователь Сообщений: 3875 |
#13 11.04.2011 17:28:55 еще вариант: |
|
Как посчитать сумму ячеек где буквы и цифры |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
