Как посчитать цифры с буквами в excel - Word и Excel - помощь в работе с программами

В разных ячейках написаны цифры и буквы вместе. Например: 23д, 15н, 18д и т.д.
Как посчитать сумму этих чисел?
При простой автосумме выдает ошибку, из-за текста.
Отделить их тоже никак не возможно.
Как поступить в этом случае?
Заранее благодарю!

dude Пользователь Сообщений: 1066 Регистрация: 22.12.2012	если цифры всегда слева Function vvv(d As Range) As Double Dim s For Each s In d vvv = vvv + Val(d.Text) Next End Function

vikttur Пользователь Сообщений: 47199 Регистрация: 15.09.2012	Формула массива: =СУММ(—ЛЕВСИМВ(A1:A3;2))

bobo Пользователь Сообщений: 4 Регистрация: 01.01.1970	выделяем нужные ячейки, правый клик, формат ячеек, (основной «д»)или (основной «км») или (основной «кг») и т.д. можно писать что вы пожелаете.

bobo Пользователь Сообщений: 4 Регистрация: 01.01.1970	{quote}{login=bobo}{date=07.04.2011 08:53}{thema=}{post}выделяем нужные ячейки, правый клик, формат ячеек, все форматы,(основной «д»)или (основной «км») или (основной «кг») и т.д. можно писать что вы пожелаете.{/post}{/quote}

слэн Пользователь Сообщений: 5192 Регистрация: 16.01.2013	как в формате применить эти «или» ?

	А что должно получиться, если 23д+15н?

bobo

Пользователь

Сообщений: 4
Регистрация: 01.01.1970

слово «основной» — это значение ячейки который вбивает пользователь с клавиатуры, добавочное слово (после цифры) — экзель приписывает то слово которое вы написали формат ячеек-все форматы-основной «слово»
«слово» — это то что вы хотите видеть после цифры.

bobo Пользователь Сообщений: 4 Регистрация: 01.01.1970	лучше посмотрите в этом файле формат ячеек

	{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}Формула массива: =СУММ(—ЛЕВСИМВ(A1:A3;2)){/post}{/quote} тоже пробовал, не работает.

{quote}{login=bobo}{date=07.04.2011 08:53}{thema=}{post}выделяем нужные ячейки, правый клик, формат ячеек, (основной «д»)или (основной «км») или (основной «кг») и т.д. можно писать что вы пожелаете.{/post}{/quote}
к сожалению результата нет.
выручайте господа!

MCH Пользователь Сообщений: 3875 Регистрация: 22.12.2012	#13 11.04.2011 17:28:55 еще вариант: =СУММПРОИЗВ(—ПСТР(«00″&D6:AB6;1;ДЛСТР(D6:AB6)+1))

Источник

Как в 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 руб./за проект

Минуточку внимания

Источник

Как посчитать сумму ячеек где буквы и цифры

exc012

Дата: Вторник, 29.10.2019, 13:27 |
Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 29

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

Здравствуйте.
Есть ячейки в них будет такой текст, например,
Я 8.2
Я 4,4
Необходимо в результирующей ячейке посчитать сумму по этим всем ячейкам, чтобы формула игнорировала буквы
Т.е. тут итог должен быть 12.6

Какую формулу надо использовать (функцию для суммирования)?

Сообщение отредактировал exc012 — Среда, 30.10.2019, 06:59

Ответить

bmv98rus

Дата: Вторник, 29.10.2019, 14:16 |
Сообщение № 2

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4009

Репутация:
760
±

Замечаний:
0% ±

Excel 2013/2016

exc012, Все ячейки с буквами. Название темы лучше приблизить к сути проблемы.

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Ответить

китин

Дата: Вторник, 29.10.2019, 14:30 |
Сообщение № 3

Группа: Модераторы

Ранг: Экселист

Сообщений: 6973

Репутация:
1063
±

Замечаний:
0% ±

Excel 2007;2010;2016

exc012, — Прочитайте Правила форума
— Исправьте название темы согласно п.2 Правил форума, конкретнее обозначьте проблему
— Приложите файл с исходными данными и желаемым результатом (можно вручную) в формате Excel размером до 100кб согласно п.3 Правил форума

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Ответить

exc012

Дата: Среда, 30.10.2019, 07:00 |
Сообщение № 4

Группа: Пользователи

Ранг: Новичок

Сообщений: 29

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

Прикладываю файл

К сообщению приложен файл:

0575860.xls
(25.0 Kb)

Ответить

bmv98rus

Дата: Среда, 30.10.2019, 07:40 |
Сообщение № 5

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4009

Репутация:
760
±

Замечаний:
0% ±

Excel 2013/2016

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

Код

=SUMPRODUCT(—SUBSTITUTE(A3:C3;»Я»;))

но то что было в топике требует минимум еще одну подстановку, точки или запятой на разделитель десятичный

Код

=SUMPRODUCT(—SUBSTITUTE(SUBSTITUTE(A3:C3;»Я»;);».»;»,»)

А для общего случая не зависисмого от региональных установок

Код

=SUMPRODUCT(—SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3:C3;»Я»;);».»;»,»);»,»;MID(1/2;2;1)))

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Ответить

amfor

Дата: Среда, 30.10.2019, 07:49 |
Сообщение № 6

Группа: Проверенные

Ранг: Обитатель

Сообщений: 250

Репутация:
75
±

Замечаний:
0% ±

Excel 2016

Вариант

Код

=СУММПРОИЗВ(—ПСТР(«00″&A3:C3;1;ДЛСТР(A3:C3)+1))

Семь бед — один RESET.

Ответить

exc012

Дата: Четверг, 31.10.2019, 07:06 |
Сообщение № 7

Группа: Пользователи

Ранг: Новичок

Сообщений: 29

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

Вариант

=СУММПРОИЗВ(—ПСТР(«00″&A3:C3;1;ДЛСТР(A3:C3)+1))

Спасибо!!! Так работает.
Еще добавил в конце +СУММ(A3:C3) чтобы подсуммировались ячейки, где буква не указана.

Отличный форум! Оригинальный дизаин

Ответить

exc012

Дата: Понедельник, 11.11.2019, 08:03 |
Сообщение № 8

Группа: Пользователи

Ранг: Новичок

Сообщений: 29

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

СОС!!
Помогите, оказывается надо учесть еще лучай когда в ячейк не 1 буква, например, 7.6Я, а еще когда так в ячейке: 7.6АП

Прикладываю файл, помогите!

П.с. сейчас еще сам, конечно попробую, но не факт что смогу.

К сообщению приложен файл:

6893054.xls
(25.0 Kb)

Ответить

Светлый

Дата: Понедельник, 11.11.2019, 09:17 |
Сообщение № 9

Группа: Друзья

Ранг: Старожил

Сообщений: 1742

Репутация:
479
±

Замечаний:
0% ±

Excel 2013, 2016

Ещё одна функция ПОДСТАВИТЬ и всё в порядке.

Программировать проще, чем писать стихи.

Ответить

exc012

Дата: Понедельник, 11.11.2019, 10:53 |
Сообщение № 10

Группа: Пользователи

Ранг: Новичок

Сообщений: 29

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

Излишнее цитирование удалено администрацией — это нарушение п.5j Правил форума
У меня вообще не работал.
Пожалуйста?

Сообщение отредактировал китин — Понедельник, 11.11.2019, 12:52

Ответить

exc012

Дата: Понедельник, 11.11.2019, 11:35 |
Сообщение № 11

Группа: Пользователи

Ранг: Новичок

Сообщений: 29

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

Очень бы хотелось получить ответ. Просто срочно надо!!!
Ковыряюсь по всякому, не работает

Вот в такой формуле, если поставить 3 нуля, тогда работает, если Я с пробелом стоит, но это тоже не вариант пробелы ставить

Код

=СУММПРОИЗВ(—ПСТР(«000″&A3:C3;1;ДЛСТР(A3:C3)+1))

Ответить

bmv98rus

Дата: Понедельник, 11.11.2019, 12:27 |
Сообщение № 12

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4009

Репутация:
760
±

Замечаний:
0% ±

Excel 2013/2016

Ковыряюсь по всякому, не работает

Ну вы ж сразу от правильного варианта переключились на понятный Вам.

Ещё одна функция ПОДСТАВИТЬ и всё в порядке.

Код

=SUMPRODUCT(—SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3:C3;»Я»;);»АП»;);».»;»,»))

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Ответить

exc012

Дата: Понедельник, 11.11.2019, 12:45 |
Сообщение № 13

Группа: Пользователи

Ранг: Новичок

Сообщений: 29

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

Спасибо, видимо я плохой пример привел.
Там внутри длинная строка и могут быть пустые ячейки

Этот вариант работает только при непрерывной строке.

А строка может быть:

7.8Я 7.8АП В В 7.8РП

То есть и букв может быть много и в ячейке может быть только буква без часов.

Ответить

bmv98rus

Дата: Понедельник, 11.11.2019, 12:52 |
Сообщение № 14

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4009

Репутация:
760
±

Замечаний:
0% ±

Excel 2013/2016

видимо я плохой пример привел.

Естественно надо прикладывать реалистичные данные, или уметь адаптировать к ним решения.

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Ответить

exc012

Дата: Понедельник, 11.11.2019, 12:53 |
Сообщение № 15

Группа: Пользователи

Ранг: Новичок

Сообщений: 29

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

Вот, прикладываю файл уже приближенный.

К сообщению приложен файл:

6538239.xlsx
(11.1 Kb)

Ответить

exc012

Дата: Понедельник, 11.11.2019, 12:57 |
Сообщение № 16

Группа: Пользователи

Ранг: Новичок

Сообщений: 29

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

Естественно надо прикладывать реалистичные данные, или уметь адаптировать к ним решения.
К сообщению приложен файл: Copy_of_1267_1.xlsx(17.3 Kb)

Да, вижу тут есть обработка ошибки, но, если как в недавнем выложеном файле. Разные буквы, т.е. чтобы учесть все буквы надо формулу очень большукю тут рисовать

Поэтому я схватился за вариант

Код

=СУММПРОИЗВ(—ПСТР(«00″&A3:C3;1;ДЛСТР(A3:C3)+1))

Где все это работало и только не учитывало буквы с длиной 2 (т.е. всякие РП, АП, ДО, и т.д.)
И попытался ее смодифицировать

Ответить

exc012

Дата: Понедельник, 11.11.2019, 13:13 |
Сообщение № 17

Группа: Пользователи

Ранг: Новичок

Сообщений: 29

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

Что то хотел упростить (убрать замену точек на зпт) и добавить обработку еще 1 символа «РП» что-то заплюхался
…Еще пробую так, чтото не работает

Код

=СУММПРОИЗВ(ЕСЛИОШИБКА(—ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A3:E3;»Я»;);»АП»;);»РП»;);))

Сообщение отредактировал exc012 — Понедельник, 11.11.2019, 13:14

Ответить

bmv98rus

Дата: Понедельник, 11.11.2019, 13:22 |
Сообщение № 18

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4009

Репутация:
760
±

Замечаний:
0% ±

Excel 2013/2016

Ага,а если учесть что пример от
то…~~Короче, если тексто суффиксом, то массивная~~

Код

=SUM(IFERROR(—SUBSTITUTE(LEFT(A3:E3;MAX(IF(ISNUMBER(—MID(A3:E3;{1;2;3;4;5};1));{1;2;3;4;5})));».»;»,»);))

~~Думаю 5 хватит, даже если будет 23.59~~
Нормальные примеры в следующий раз готовьте.

Что-то сложное вышло из под пера

Код

=SUM(IFERROR(—SUBSTITUTE(LEFT(A3:E3;TRANSPOSE(MMULT(—(ISNUMBER(—MID(TRANSPOSE(SUBSTITUTE(A3:E3;».»;»,»));1;{12345})));{1;1;1;1;1})));».»;»,»);))

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Сообщение отредактировал bmv98rus — Понедельник, 11.11.2019, 14:28

Ответить

exc012

Дата: Понедельник, 11.11.2019, 13:24 |
Сообщение № 19

Группа: Пользователи

Ранг: Новичок

Сообщений: 29

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

Добавил обработку еще символа, кое-как с бубном заработало.
Сейчас попытаюсь все буквы учесть

Ответить

exc012

Дата: Понедельник, 11.11.2019, 13:38 |
Сообщение № 20

Группа: Пользователи

Ранг: Новичок

Сообщений: 29

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

7.8Я 7.8АП В В 7.8РП
то…

Короче, если тексто суффиксом, то массивная

=СУММ(ЕСЛИОШИБКА(—ПОДСТАВИТЬ(ЛЕВСИМВ(A3:E3;МАКС(ЕСЛИ(ЕЧИСЛО(—ПСТР(A3:E3;{1;2;3;4;5};1));{1;2;3;4;5})));».»;»,»);))
Думаю 5 хватит, даже если будет 23.59
Нормальные примеры в следующий раз готовьте.

Формула в вашем примере работает, а себе в лист вставляю и меняю диапазон на свой и показывает 0.
Вроде аккуратно копирую формулу.

Ответить

Источник

You can use the following basic formula to sum cells in Excel that contain both numbers and text:

=SUM(SUBSTITUTE(B2:B8, "some_text", "")+0)

This particular formula removes the text string “some_text” from each cell in the range B2:B8 and then calculates the sum of the values in the range B2:B8.

The following examples show how to use this formula in practice.

Example 1: Calculate Sum of Cells with Text and Numbers

Suppose we have the following dataset that shows the total number of sales at seven different stores:

To calculate the sum of sales, we can type the following formula into cell B10:

=SUM(SUBSTITUTE(B2:B8, " items", "")+0)

Once we press Enter, the sum of the items will be shown:

The sum of the items sold is 97.

This formula simply substituted a blank where “items” used to be in each cell and then calculated the sum of the values remaining in the cells.

Example 2: Calculate Sum of Cells with Different Text and Numbers

Suppose we have the following dataset that shows the total number of sales at seven different stores:

To calculate the sum of sales, we can type the following formula into cell B10:

=SUM(SUBSTITUTE(SUBSTITUTE(B2:B8, " items", ""), "things", "")+0)

Once we press Enter, the sum of the values in column B will be shown:

The sum of the items sold is 97.

This formula simply substituted a blank where “items” and “things” used to be in each cell and then calculated the sum of the values remaining in the cells.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Replace Blank Cells with Zero in Excel
How to Replace #N/A Values in Excel
How to Sum If Cells Contain Text in Excel

Источник

17 авг. 2022 г.
читать 2 мин

Вы можете использовать следующую базовую формулу для суммирования ячеек в Excel, содержащих как числа, так и текст:

=SUM(SUBSTITUTE( B2:B8 , "some_text", "")+0)

Эта конкретная формула удаляет текстовую строку «some_text» из каждой ячейки в диапазоне B2:B8 , а затем вычисляет сумму значений в диапазоне B2:B8 .

Следующие примеры показывают, как использовать эту формулу на практике.

Пример 1: вычислить сумму ячеек с текстом и числами

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

Чтобы рассчитать сумму продаж, мы можем ввести следующую формулу в ячейку B10 :

=SUM(SUBSTITUTE( B2:B8 , " items", "")+0)

Как только мы нажмем Enter , будет показана сумма элементов:

Сумма проданных товаров равна 97 .

Эта формула просто заменяла пробел вместо «элементов» в каждой ячейке, а затем вычисляла сумму значений, оставшихся в ячейках.

Пример 2: вычислить сумму ячеек с разным текстом и числами

Чтобы рассчитать сумму продаж, мы можем ввести следующую формулу в ячейку B10 :

=SUM(SUBSTITUTE(SUBSTITUTE( B2:B8 , " items", ""), "things", "")+0)

Как только мы нажмем Enter , будет показана сумма значений в столбце B:

Сумма проданных товаров равна 97 .

Эта формула просто подставляла пробел вместо «предметов» и «вещей» в каждой ячейке, а затем вычисляла сумму значений, оставшихся в ячейках.

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

Как заменить пустые ячейки нулем в Excel
Как заменить значения #N/A в Excel
Как суммировать, если ячейки содержат текст в Excel

Источник

Минуточку внимания

Example 1: Calculate Sum of Cells with Text and Numbers

Example 2: Calculate Sum of Cells with Different Text and Numbers

Additional Resources

Пример 1: вычислить сумму ячеек с текстом и числами

Пример 2: вычислить сумму ячеек с разным текстом и числами

Дополнительные ресурсы

А вот еще интересные статьи: