Как сделать формулу чтобы считала только цифры в excel

Обновлено: 04.07.2024

Недавно, в комментариях к заметке Excel. Суммирование по ячейкам, выделенным цветом меня спросили, как подсчитать число ячеек, содержащих определенный символ / букву. У меня получилось решить задачу без использования кода VBA на основе формулы массива. Если вы не использовали ранее такие формулы, рекомендую начать с заметки Excel. Введение в формулы массива.

Шаг 1. Определим, содержится ли искомый символ в ячейке (рис. 1).

Рис. 1. Содержится ли искомый символ в выбранной ячейке

Шаг 2. Избавляемся от ошибочных значений (рис. 2).

Рис. 2. Замена ошибочных значений нулями

ЕСЛИОШИБКА(НАЙТИ($C$1;A1);0) – возвращает 0, если значение функции НАЙТИ($C$1;A1) выдает ошибку, в противном случае возвращает само значение функции НАЙТИ($C$1;A1)

Шаг. 3. Заменяем номер позиции на единицу

Рис. 3. Приведение всех положительных значений к единице

=ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A1)/1000;1);0) – преобразование, позволяющее для любого значения позиции (не ошибки) получить 1; номер позиции, возвращаемый функцией НАЙТИ($C$1;A1), делится на 1000 и округляется до ближайшего целого (если быть точным, то ячейка Excel максимально может содержать 32 767 символов, так что «для надежности» можно заменить 1000 на 32 767 :); в этом случае, вы избежите ошибки при любом содержимом ячеек).

В качестве альтернативы можно использовать функцию ЕСЛИ:

Шаг. 4. Суммируем все единички. Напомню, что единица соответствует ячейке, содержащей искомый символ, а ноль – ячейке, не содержащей искомый символ (рис. 4).

Рис. 4. Суммирование ячеек, содержащих искомый символ

В ячейке С2 я использовал формулу массива:

Функция последовательно вычисляет значения =ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A1)/1000;1);0), =ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A2)/1000;1);0) … и так вплоть до =ЕСЛИОШИБКА(ОКРВВЕРХ(НАЙТИ($C$1;A14)/1000;1);0)

Результат вычисления (0 или 1) заносится в память, формируя виртуальный массив . Функция СУММ просто суммирует все единицы.

Альтернативная функция: =СУММ(ЕСЛИОШИБКА(ЕСЛИ(НАЙТИ($C$1;A1:A14)>0;1);0))

P.S. Не вводите фигурные скобки в строку формул, а наберите всю формулу без фигурных скобок и нажмите одновременно Ctrl+Shift+Enter

87 комментариев для “Excel. Подсчет числа ячеек, содержащих символ (букву)”

День Добрый подскажите а как нужно прописать чтобы формула считала по цвету и тому что в ней написано пример: ячейки разноцветные и месяца в них разные нужно нужно посчитать кол-во всех зелённых ячеек с надписью СЕНТЯБРЬ ? заранее СПАСИБО

Максим, воспользуйтесь кодом:
Function КолЦвет(диапазон As Range, критерий As Range, номер_месяца As String) As Double
Application.Volatile True
Dim i As Range
For Each i In диапазон
If i.Interior.Color = критерий.Interior.Color And i = номер_месяца Then
КолЦвет = КолЦвет + 1
End If
Next
End Function
См. также Excel-файл (зазипованный)

Здравствуйте подскажите пожалуйста как сравнить вот эти две таблицы, надо найти по одинаковым строчкам отличие в количестве.

Огромное человеческое спасибо . спасибо

Добрый день какой можно воспользоваться формулой чтобы в нужной ячейки проставлялось определённое число при определённом Условии(текст) например : погрузка стекла стоит 2000, а плитки 3000 и гравия 3500 погруженный материал цена за погрузку прайс по погрузке одного тс
стекло стекло 2000
плитка плитка 3000
гравий гравий 3500
стекло
гравий
плитка

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

да, иначе не считала бы. Отправил Вам ваш файл, лист " рис.4 " все видно

Подскажите пожалуйста: в ячейке есть значение с цифрами и буквой(С), как просуммировать ячейки с буквой (С) или другими буквами?

Андрей, супер! Всё верно! Простое и изящное решение. Отмечу Ваш коммент среди лучших))

Спасибо большое на свой вопрос нашла у Вас ответ.

Большое спасибо за формулу!

Огромное Вам и создателю сайта человеческое спасибо.

Спасибо огромное. очень помог!!простое и понятное решение .

Спасибо вам и создателю темы! Очень помогли:)

Подскажите пожалуйста, мне надо в столбце с числами сделать изменения, перед началом цифры выставить везде *

Виктория, если я правильно понял ваш вопрос, воспользуйтесь формулой = " * " &A1

Спасибо большое, сейчас попробую)

Получилось, спасибо. Столько время теперь с экономлю

68.319396
60.237774
192.109067, а надо

*68.319396*
*60.237774*
*192.109067*

Формула немного усложняется))
= " * " &A1& " * "

Да, чуть усложнилась, но до этого я додумалась))))

а для вашего случая будет так =СЦЕПИТЬ( " * " ;M6; " * " ).

zdrastvuyte izvinite umneya neskolka vobrosov est. 1 kak mojno v excel summirovat bukvi na primer a.b.c.d. i nasummu nado viti 4. isho odin primer a.a.a.a.a. i na summu 5 nado viti/ pojalosta pomogite.

Анвар, если я правильно понял вопрос, вам нужно определить число символов в текстовой строке. Для этого в Excel применяется функция ДЛСТР().

zdrastvuyte gospodin Baguzin. u menya odin vopros kak mojno v odin yacheyke 16 stoit i na drugom yacheyke 8 stoit. U nas rabote yest tabel tam nochniye chasov shitat nada. 16=2 i 8=6 i tak dale. odin mesyes 30 den est ili 31 den ili 29 den. yacheyki nado i summirovat. vot na primer tabel
1. 2. 3. 4. 5. 6. 7. 8. 9.-dni
o 16 8 o 16 8 o 16 8 -rabochiye chasov
na nemu nado summirovat yesli mi budem nochniye schitat ruchnoy o eta oddix, 16 ravno na 2, 8 ravno 6. i tak nam nam na 9 dnyey poluchitsya 2*3=6 i 6*3=18 i tak 6+18=24 chasov vxodit vot etova mnya nado formula stobi ekcel sam nam summiroval eti nochniye chasov avtomaticheskiy. Yesli vopros ponyatno pojalosta dayte nam otvet.

vopros tokoy na primer
yacheyka
A B
1 акмал ишчи
2 акмал укувчи
3 жасур ишчи
4 илхом алочи
5 акмал домла
6 илхом ишчи
7 акмал балочи
8 жасур укувчи
9 жасур балочи
vot takiye slovi dano nam. Eti 2 stolbik. Pervoy A stolbik i vtoroye B stolbik. Kajdiy slova iz odin yacheyki sostoitsya.
Vopros takoye. Menya nado na primer
s pervogo tablitsi акмал i vtorogo tablitsi sve kotoriye prenadlejit акмалю nado sobirat na odin yacheyki.
ишчи, укувчи, домла, балочи. vot tak nado menya sobirat v odin yacheyki.
Vot ishyo primer
s pervogo tablitse жасур. syo kakiye slovi prenadlejit nado menya sobirat v odin yacheyku s stolbika B.
ишчи, укувчи, балочи.
isho mojno primer. илхом
sobirayem na odin yacheyku vse prenadlejayushi к илхому с stolbika B.
eto nam tak poluchayetsya.
алочи, ишчи.
vopros po moy zglaydu vam ponyatno dumayu.
Yesli zdes (формула впр) rom ispolzuvali bi to nam togta tolko pervoye naydet. na primer
акмал ишчи a ostalniye ne naydet ili ne sobirayet.
1 акмал ишчи
2 акмал укувчи
5 акмал домла
7 акмал балочи
yesli mesta slovi sifri stoil bi tokda nam s formuley суммесли sobirali bi.
Primer
1 акмал 6
2 акмал 9
5 акмал 12
7 акмал 22
a nam nado slovi sobirat na odin yacheyke.
ODIN FORMULA NADO SHTOBI. IZ PERVOGO TABLITS TAKOVO (АКМАЛ) I S VTOROGOVA TABLITSA SVE SLOVI PODLEJAYESHI K АКМАЛЕ SOBIRAT NADO.
tOLKO NAM S FORMULEY NAYTI I SOBIRAT NADO.
potamushta perechen ochen bolshaya.
yasli vopros vam ne ponayatno pojalusta skajite menya vashu pochta ya tam s excelskiy faylom otpravlyu.

yacheyka
A
акмал
жасур
илхом
акмал
илхом
акмал
жасур
жасур
B
укувчи
ишчи
алочи
домла
ишчи
балочи
укувчи
балочи
yesli vam ne ponyao dumal. za etogo takogo variantom toje otpravlyayu.

EXCEL предназначена для работы с электронными таблицами, является универсальной программой находящейся в составе пакета Microsoft Office. С помощью Эксель можно организовывать информацию, хранить и анализировать данные. Используется Microsoft Office Excel не только профессиональными специалистами. Она доступна любому желающему для выполнения различных задач. Научившись, как в Эксель посчитать сумму, пользователь сможет использовать программу для ведения домашнего бюджета, производить расчеты (простые, сложные), строить диаграммы, графики.

Принцип работы Эксель

Работа Майкрософт Эксель заключается в использовании формул, которые при помощи специального процессора разрешают:

• создавать таблицы разного размера и степени сложности;

• выполнять подсчет значений, расположенных в ячейках.

Большинство пользователей не знают, как в EXCEL посчитать сумму, поэтому мы поэтапно рассмотрим, как это сделать

Подсчет суммы в Excel, этапы

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

Чтобы произвести подсчет, левой кнопкой мышки выделяем ячейки, в которых указаны значения, и следующую ячейку под ними, где нет цифр (здесь будет расположено значение суммы), нажимаем значок Σ (Автосумма), расположенный в правом верхнем углу таблицы.

Также сумму можно подсчитать другим способом. Выделяем ячейку, где будет находиться значение суммы, нажимаем кнопку «Автосумма».

Область, в которой значения будут суммироваться, выделяется, а в ячейке суммы будет виден диапазон ячеек, которые просуммируются. Если это нужный нам диапазон, нажимаем клавишу «Enter», если нет, то мы можем изменять этот диапазон (например В4:В9 можно изменить на В4:В6).

Также рассмотрим, как посчитать сумму в Excel более сложным способом. К примеру, нам нужно подсчитать значения ячеек, не стоящих одно под другим (подряд) в столбце. На изображении желтым цветом отмечены ячейки, значения которых необходимо суммировать.

Курсор размещаем на ту ячейку, где должна находиться сумма выделенных ячеек. Нажимаем «=» (равно), кликаем на первую, выделенную желтым, ячейку, после чего в ячейке суммы будет сформирована формула. После этого кликаем «+» (плюс), за ним нажимаем на следующую выделенную желтым ячейку, опять «+», после чего жмем на последнюю, выделенную желтым, ячейку. После этого на экране появится:

Теперь жмем «Enter» и перед нами появляется подсчитанная сумма.

пример на скрине

Вопрос задан более двух лет назад
6894 просмотра

Простой 8 комментариев

IMHO формула не умеет считать цифры с буквами. Вам нужно хранить буквы и цифры в разных ячейках для формулы.

Saboteur, а если подсчитать сумму только тех ячеек где есть любая буква. Если просто использовать сумм, то суммируются ячейки без текста, а как сделать наоборот, чтобы суммировались только ячейки которые содержат любую букву? Число и строка - разные типы переменных. Формула не сможет суммировать.

Что мешает сделать так:
Л П None
8 2
3 9
6
2
И потом уже суммировать цифры в столбцах

Для буквы "л" формула такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК("л";B4:G4);--ПОДСТАВИТЬ(B4:G4;"л";""));0))
Для "п" соответственно такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК("п";B4:G4);--ПОДСТАВИТЬ(B4:G4;"п";""));0))

Обе - формулы массива. Вводятся одновременным нажатием клавиш Ctrl+Shift+Enter

Игорь, может еще поможете с одним вопросом, правда не знаю возможно ли вообще такое.

Суть в следующем. Для каждой ячейки хочу сделать выпадающий список с этими же значениями, например 8л;2п;3л и т.д

Как сделать чтобы в ячейке при выборе значения была видна только цифра? например я выбрал 8л из выпадающего списка, но в ячейке было видно только цифру 8, но при этом работала ваша формула

Для буквы "л" формула такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК("л";B4:G4);--ПОДСТАВИТЬ(B4:G4;"л";""));0))
Для "п" соответственно такая:
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК("п";B4:G4);--ПОДСТАВИТЬ(B4:G4;"п";""));0))

Надеюсь понятно написал, что я хочу?

maratmd, думаю, тут не обойтись без макроса, который будет красить букву в цвет фона ячейки при выборе какого-то значения из списка. Таким образом, в списке будут цифры с буквами, а в ячейке видны только цифры (буквы будут окрашены в фоновый цвет). Других путей не вижу. Макрос надо поместить в модуль листа, диапазон замените на свой, в котором расположены ячейки со списками:

Работая с табличной информацией, у пользователей зачастую возникает необходимость в подсчете суммы того или иного показателя. Часто в качестве этих показателей выступают наименования строчек, по которым необходимо произвести суммирование всей информации в ячейках. Из статьи вы узнаете все методы, позволяющие реализовать эту процедуру.

Суммирование значений в строке

Воспроизвести процесс суммирования значений в строке можно следующими методами:

арифметическая формула;
автосумма;
различные функции.

Каждый из этих способов подразделяется на дополнительные способы. Разберемся с ними более подробно.

Способ 1: арифметическая формула

Первоначально выясним, как, используя арифметическую формулу, возможно произвести суммирование в строчке. Разберем все на определенном примере. Допустим, у нас есть табличка, в которой отображена выручка 5 магазинов по определенным датам. Названия торговых точек – наименования строчек. Даты – наименования колонок.

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

Производим выделение той ячейки, где в дальнейшем будет отражаться результат. Вводим в ячейку символ «=». Жмем ЛКМ по самой первой ячейке в этой строчке, содержащей числовые показатели. Замечаем, что после нажатия координаты ячейки отобразились в ячейке для подсчета результата. Вводим символ «+» и кликаем на следующую ячейку в строке. Продолжаем чередовать символ «+» с координатами ячеек строки первой торговой точки. В результате мы получаем формулу: =B3+C3+D3+E3+F3+G3+H3.

После проведения всех манипуляций нажимаем на «Enter».
Готово! Результат вывелся в ячейке, в которую мы вводили формулу для подсчёта суммы.

Обратите внимание! Как видите, этот метод является понятным и легким, но в нем существует один неприятный недостаток. Реализация этого метода занимает большое количество времени. Рассмотрим более быстрые варианты суммирования.

Способ 2: автосумма

Использование автоматической суммы – метод, который выполняется намного быстрее, чем вышерассмотренный. Пошаговое руководство выглядит так:

Используя зажатую ЛКМ, производим выделение всех ячеек первой строки, которые имеют числовые данные. Передвигаемся в раздел «Главная», располагающийся в верхней части интерфейса табличного процессора. Находим блок команд «Редактирование» и кликаем на элемент под названием «Редактирование». 4

Рекомендация! Альтернативный вариант – переходим в раздел «Формулы» и нажимаем на кнопку «Автосумма», находящуюся в блоке «Библиотека функций». Третий вариант – использование комбинации клавиш «Alt» + «=» после выделения ячейки.

Как вы заметили, этот способ производит суммирование в строчке намного быстрее, чем вышеописанный. Главный недостаток – результат выводится только правее от выбранного диапазона. Чтобы результат отображался в любом выбранном месте, необходимо воспользоваться другими методами.

Способ 3: функция СУММ

Использование интегрированной функции табличного процессора под названием СУММ не имеет недостатков, как у рассмотренных ранее способов. СУММ – математическая функция. Задача оператора – суммирование числовых значений. Общий вид оператора: =СУММ(число1;число2;…).

Важно! Аргументами этой функции могут являться как числовые значения, так и координаты ячеек. Максимальное число аргументов – 255.

Пошаговое руководство выглядит следующим образом:

Производим выделение какой-либо пустой ячейки на рабочем листе. В ней мы будем выводить результат суммирования. Стоит отметить, что она может располагаться даже на отдельном рабочем листе документа. После осуществления выделения жмем на кнопку «Вставить функцию», находящуюся рядом со строчкой для ввода формул.

На экране отобразилось небольшое окошко под названием «Мастер функций». Раскрываем список около надписи «Категория:» и выбираем элемент «Математические». Чуть ниже в перечне «Выберите функцию:» находим оператор СУММ и щелкаем на него. После проведения всех манипуляций щелкаем на кнопочку «ОК», располагающуюся в нижней части окна.

На дисплее появилось окошко под названием «Аргументы функции». В пустое поле «Число1» вводим адрес строки, значения в которой необходимо сложить. Для осуществления этой процедуры ставим указатель в эту строчку, а потом, используя зажатие ЛКМ, производим выделение всего диапазона с числовыми значениями. После проведения всех манипуляций щелкаем на «ОК».

Готово! В выбранной изначально ячейке отобразился результат суммирования.

Функция СУММ(SUM) не работает

Иногда случается так, что оператор СУММ не работает. Основные причины неисправности:

неправильный формат чисел (текстовый) при импорте данных;
наличие скрытых символов и пробелов в ячейках с числовыми значениями.

Стоит отметить! Числовые значения всегда выравниваются по правой стороне, а текстовая информация – по левой.

Как найти сумму наибольших (наименьших) значений

Разберемся с тем, как вычислить сумму наименьших или же наибольших значений. К примеру, нам необходимо просуммировать три минимальных или же три максимальных значения.

Оператор НАИБОЛЬШИЙ позволяет возвратить максимальный показатель из выбранных данных. 2-й аргумент указывает, какой именно показатель необходимо вернуть. В нашем конкретном примере формула выглядит так: =СУММ(НАИБОЛЬШИЙ(B2:D13;)).

Таким же образом работает и поиск наименьшего значения, только вместо оператора НАИБОЛЬШИЙ используется функция НАИМЕНЬШИЙ. Формула выглядит следующим образом: =СУММ(НАИМЕНЬШИЙ(B2:D13;)).

Растягивание формулы/функции суммирования на другие строки

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

Как вычислить сумму каждой N-ой строки.

На конкретном примере разберем, как вычислить сумму каждой N-ой строки. К примеру, у нас есть табличка, в которой отражена каждодневная прибыль торговой точки за определенный промежуток времени.

Задача: произвести расчёт еженедельной прибыли за каждую неделю. Оператор СУММ позволяет суммировать данные не только в диапазоне, но и в массиве. Здесь необходимо использование вспомогательного оператора СМЕЩ. В операторе СМЕЩ указывается некоторое количество аргументов:

Первая точка. Ячейка С2 вводится в виде абсолютной ссылки.
Количество шагов вниз.
Количество шагов вправо.
Число шагов вниз.
Количество столбиков в массиве. Попадание в последнюю точку массива показателей.

В конечном итоге мы получаем следующую формулу для первой недели: =СУММ(СМЕЩ($C$2;(СТРОКА()-2)*5;0;5;1)). В результате оператор сумм произведет суммирование всех пяти числовых значений.

3-D сумма, или работаем с несколькими листами рабочей книги Excel

Для подсчета чисел из одинаковой формы диапазона на некотором количестве рабочих листов необходимо использовать особый синтаксис, который называется «3D-ссылкой». Допустим, на всех рабочих листах книги располагается табличка с информацией за неделю. Нам необходимо соединить это все в одно целое и вывести в месячный показатель. Для начала вам необходимо посмотреть следующий видеоролик:

У нас есть четыре идентичных таблички. Обычный способ подсчета прибыли выглядит так: =СУММ(неделя1!B2:B8;неделя2!B2:B8;неделя3!B2:B8;неделя4!B2:B8). Здесь в роли аргументов выступают диапазоны ячеек.

Формула 3D-суммы выглядит следующим образом: =СУММ(неделя1:неделя4!B2:B8). Здесь говорится о том, что производится суммирование в диапазонах В2:В8, которые располагаются на рабочих листах: неделя (от 1 до 4). Происходит пошаговое увеличение номера рабочего листа на единицу.

Сумма с несколькими условиями

Бываю случаи, когда пользователю необходимо осуществить решение задачи, в которой указано от двух и более условий и нужно посчитать сумму числовых значений по разным критериям. Для реализации этой процедуры применяется функция «=СУММЕСЛИМН».

Пошаговое руководство выглядит следующим образом:

Для начала формируется табличка.
Производится выбор ячейки, в которой будет выведен результат суммирования.
Перемещаемся в строчку для ввода формул.
Вводим оператор: =СУММАЕСЛИМН.
Пошагово вводим диапазон сложения, диапазон условия1, условие1 и так далее.
После проведения всех манипуляций жмем «Enter». Готово! Подсчет произведен.

Стоит отметить! Между аргументами оператора в обязательном порядке должен стоять разделитель в виде точки с запятой «;». Если не использовать этот разделитель, то табличный процессор выведет ошибку, говорящую о том, что функция была введена неправильно.

Как посчитать процент от суммы

Теперь поговорим о том, как правильно посчитать процент от суммы. Самый легкий метод, который будет понятен всем пользователям, – это применение пропорции или правила «квадрата». Суть можно понять по нижеприведенной картинке:

Общая сумма выводится в ячейке F8 и имеет значение 3060. Иными словами, это стопроцентный доход, а нам необходимо найти, какую часть прибыли сделал Саша. Для подсчета мы применяем специальную формулу пропорции, которая выглядит следующим образом: =F10*G8/F8.

Важно! Первым делом умножается 2 известных числовых значения по диагонали, а потом производится деление на оставшееся 3-е значение.

Используя это простое правило, можно легко и просто высчитать процент от суммы.

Заключение

В статье было рассмотрено несколько способов, позволяющих получить сумму данных строки в табличном процессоре Эксель. Каждый способ имеет собственные преимущества и недостатки. Применение арифметической формулы – самый простой в использовании способ, но к нему целесообразнее прибегать при работе с небольшими объемами информации. Для работы с большими объёмами данных хорошо подходит автоматическое суммирование, а также функция СУММ.

Читайте также: