Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Выборочная сумма чисел в ячейке с текстом. - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выборочная сумма чисел в ячейке с текстом. (Формулы/Formulas)
Выборочная сумма чисел в ячейке с текстом.
Франц Дата: Среда, 09.11.2016, 10:39 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Доброе время суток!
Просьба помочь с формулой.

В одной ячейке находится текст + числа.
Пример:

ТЭЦ-11: ТГ-2, аварийный ремонт уплотнений генератора: 1,2млн.руб.
ТЭЦ-10: ТГ-5, аварийный ремонт системы возбуждения: 1,05млн.руб.

На конце события всегда число + без пробела "млн.руб." Задача: формулой посчитать сумму этих чисел (денег). В данном варианте сумма=2,25


Сообщение отредактировал Франц - Среда, 09.11.2016, 10:40
 
Ответить
СообщениеДоброе время суток!
Просьба помочь с формулой.

В одной ячейке находится текст + числа.
Пример:

ТЭЦ-11: ТГ-2, аварийный ремонт уплотнений генератора: 1,2млн.руб.
ТЭЦ-10: ТГ-5, аварийный ремонт системы возбуждения: 1,05млн.руб.

На конце события всегда число + без пробела "млн.руб." Задача: формулой посчитать сумму этих чисел (денег). В данном варианте сумма=2,25

Автор - Франц
Дата добавления - 09.11.2016 в 10:39
_Boroda_ Дата: Среда, 09.11.2016, 10:45 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 9348
Репутация: 3922 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?
Код
=СУММПРОИЗВ(--ПРАВБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1:A2;"млн.руб.";);" ";ПОВТОР(" ";99));99))
К сообщению приложен файл: 4226486.xlsx(10Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?
Код
=СУММПРОИЗВ(--ПРАВБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1:A2;"млн.руб.";);" ";ПОВТОР(" ";99));99))

Автор - _Boroda_
Дата добавления - 09.11.2016 в 10:45
китин Дата: Среда, 09.11.2016, 10:48 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3413
Репутация: 543 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
без вашего примера
Код
=СУММ(--ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПСТР(A1:A2;ПОИСК("млн.";A1:A2)-4;50));"млн.руб.";""))
формула масива


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
Сообщениебез вашего примера
Код
=СУММ(--ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПСТР(A1:A2;ПОИСК("млн.";A1:A2)-4;50));"млн.руб.";""))
формула масива

Автор - китин
Дата добавления - 09.11.2016 в 10:48
Франц Дата: Среда, 09.11.2016, 11:40 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Друзья, спасибо за помощь, но:
я указал, что текст находится в одной ячейке.
Ваши варианты для текста в 2-ух ячейках.
Текст вводился с применением АЛЬ+ЭНТЕР.
 
Ответить
СообщениеДрузья, спасибо за помощь, но:
я указал, что текст находится в одной ячейке.
Ваши варианты для текста в 2-ух ячейках.
Текст вводился с применением АЛЬ+ЭНТЕР.

Автор - Франц
Дата добавления - 09.11.2016 в 11:40
китин Дата: Среда, 09.11.2016, 11:50 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3413
Репутация: 543 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
я указал
указывать надо в файле формата .xls размером не более 100 кб


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
Сообщение
я указал
указывать надо в файле формата .xls размером не более 100 кб

Автор - китин
Дата добавления - 09.11.2016 в 11:50
Светлый Дата: Среда, 09.11.2016, 19:27 | Сообщение № 6
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 226
Репутация: 56 ±
Замечаний: 0% ±

Excel 2007
Вот такая монструозная формула будет суммировать любое количество слагаемых в ячейке. Формула массива:
Код
=СУММ(--ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР(ПСТР(A1;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1&СИМВОЛ(10);СТРОКА(1:999);1)=СИМВОЛ(10);СТРОКА(1:999));СТРОКА(СМЕЩ(A1;;;ДЛСТР(A1)-ДЛСТР(ПЕЧСИМВ(A1))+1)))-15;15);ПОИСК(": ";ПСТР(A1;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1&СИМВОЛ(10);СТРОКА(1:999);1)=СИМВОЛ(10);СТРОКА(1:999));СТРОКА(СМЕЩ(A1;;;ДЛСТР(A1)-ДЛСТР(ПЕЧСИМВ(A1))+1)))-15;15))+2;15);"млн.руб.";"");",";"."))

Если в системе десятичный разделитель другой, можно "." и "," поменять местами.


Программировать проще, чем писать стихи.
 
Ответить
СообщениеВот такая монструозная формула будет суммировать любое количество слагаемых в ячейке. Формула массива:
Код
=СУММ(--ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР(ПСТР(A1;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1&СИМВОЛ(10);СТРОКА(1:999);1)=СИМВОЛ(10);СТРОКА(1:999));СТРОКА(СМЕЩ(A1;;;ДЛСТР(A1)-ДЛСТР(ПЕЧСИМВ(A1))+1)))-15;15);ПОИСК(": ";ПСТР(A1;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1&СИМВОЛ(10);СТРОКА(1:999);1)=СИМВОЛ(10);СТРОКА(1:999));СТРОКА(СМЕЩ(A1;;;ДЛСТР(A1)-ДЛСТР(ПЕЧСИМВ(A1))+1)))-15;15))+2;15);"млн.руб.";"");",";"."))

Если в системе десятичный разделитель другой, можно "." и "," поменять местами.

Автор - Светлый
Дата добавления - 09.11.2016 в 19:27
Nic70y Дата: Среда, 09.11.2016, 20:49 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3475
Репутация: 722 ±
Замечаний: 0% ±

Excel 2013
del


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)


Сообщение отредактировал Nic70y - Среда, 09.11.2016, 22:11
 
Ответить
Сообщениеdel

Автор - Nic70y
Дата добавления - 09.11.2016 в 20:49
Франц Дата: Четверг, 10.11.2016, 07:04 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Светлый, Ваша формула

Код
=СУММ(--ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР(ПСТР(A1;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1&СИМВОЛ(10);СТРОКА(1:999);1)=СИМВОЛ(10);СТРОКА(1:999));СТРОКА(СМЕЩ(A1;;;ДЛСТР(A1)-ДЛСТР(ПЕЧСИМВ(A1))+1)))-15;15);ПОИСК(": ";ПСТР(A1;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1&СИМВОЛ(10);СТРОКА(1:999);1)=СИМВОЛ(10);СТРОКА(1:999));СТРОКА(СМЕЩ(A1;;;ДЛСТР(A1)-ДЛСТР(ПЕЧСИМВ(A1))+1)))-15;15))+2;15);"млн.руб.";"");",";"."))


работает, как раз для одной ячейки. Все замечательно, спасибо.
Только не могу понять, как ее сделать не для первой строки. Не для ячейки А1, а для А2 например. Выдает ошибку #ЧИСЛО. См. вложение.
Проблема с командой СТРОКА?
 
Ответить
СообщениеСветлый, Ваша формула

Код
=СУММ(--ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР(ПСТР(A1;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1&СИМВОЛ(10);СТРОКА(1:999);1)=СИМВОЛ(10);СТРОКА(1:999));СТРОКА(СМЕЩ(A1;;;ДЛСТР(A1)-ДЛСТР(ПЕЧСИМВ(A1))+1)))-15;15);ПОИСК(": ";ПСТР(A1;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1&СИМВОЛ(10);СТРОКА(1:999);1)=СИМВОЛ(10);СТРОКА(1:999));СТРОКА(СМЕЩ(A1;;;ДЛСТР(A1)-ДЛСТР(ПЕЧСИМВ(A1))+1)))-15;15))+2;15);"млн.руб.";"");",";"."))


работает, как раз для одной ячейки. Все замечательно, спасибо.
Только не могу понять, как ее сделать не для первой строки. Не для ячейки А1, а для А2 например. Выдает ошибку #ЧИСЛО. См. вложение.
Проблема с командой СТРОКА?

Автор - Франц
Дата добавления - 10.11.2016 в 07:04
Франц Дата: Четверг, 10.11.2016, 07:08 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Файл
К сообщению приложен файл: 9989069.xlsx(15Kb)


Сообщение отредактировал Франц - Четверг, 10.11.2016, 07:11
 
Ответить
СообщениеФайл

Автор - Франц
Дата добавления - 10.11.2016 в 07:08
AVI Дата: Четверг, 10.11.2016, 08:57 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 174
Репутация: 6 ±
Замечаний: 0% ±

Excel 2013
Друзья, спасибо за помощь, но:

Позволю себе ответить.
Формула Бороды.
Код
=СУММПРОИЗВ(--ПРАВБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1:A2;"млн.руб.";);" ";ПОВТОР(" ";99));99))

Диапазон указан.
Код
A1:A2

Если у Вас 1000 строк, то сделайте диапазон
Код
A1:A1000


Кстати, спасибо большое!! Сам то же самое делал, но монстроформулами, а тут все компактненько!
[moder]Чтобы формула не коверкалась отключайте смайлы в сообщении. Поправила[/moder]


Сообщение отредактировал Pelena - Четверг, 10.11.2016, 09:04
 
Ответить
Сообщение
Друзья, спасибо за помощь, но:

Позволю себе ответить.
Формула Бороды.
Код
=СУММПРОИЗВ(--ПРАВБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1:A2;"млн.руб.";);" ";ПОВТОР(" ";99));99))

Диапазон указан.
Код
A1:A2

Если у Вас 1000 строк, то сделайте диапазон
Код
A1:A1000


Кстати, спасибо большое!! Сам то же самое делал, но монстроформулами, а тут все компактненько!
[moder]Чтобы формула не коверкалась отключайте смайлы в сообщении. Поправила[/moder]

Автор - AVI
Дата добавления - 10.11.2016 в 08:57
Франц Дата: Четверг, 10.11.2016, 10:12 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Как работает формула Бороды понятно. Она считает значения в тексте в разных ячейках, а обсуждение про одну ячейку.


Сообщение отредактировал Франц - Четверг, 10.11.2016, 10:14
 
Ответить
СообщениеКак работает формула Бороды понятно. Она считает значения в тексте в разных ячейках, а обсуждение про одну ячейку.

Автор - Франц
Дата добавления - 10.11.2016 в 10:12
Pelena Дата: Четверг, 10.11.2016, 11:14 | Сообщение № 12
Группа: Модераторы
Ранг: Экселист
Сообщений: 9842
Репутация: 2252 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Проблема с командой СТРОКА?

Закрепите строки в функциях СТРОКА() и СМЕЩ()
Код
=СУММ(--ПОДСТАВИТЬ(ПСТР(ПСТР(A1;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1&СИМВОЛ(10);СТРОКА($1:$999);1)=СИМВОЛ(10);СТРОКА($1:$999));СТРОКА(СМЕЩ(A$1;;;ДЛСТР(A1)-ДЛСТР(ПЕЧСИМВ(A1))+1)))-15;15);ПОИСК(": ";ПСТР(A1;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1&СИМВОЛ(10);СТРОКА($1:$999);1)=СИМВОЛ(10);СТРОКА($1:$999));СТРОКА(СМЕЩ(A$1;;;ДЛСТР(A1)-ДЛСТР(ПЕЧСИМВ(A1))+1)))-15;15))+2;15);"млн.руб.";""))


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщение
Проблема с командой СТРОКА?

Закрепите строки в функциях СТРОКА() и СМЕЩ()
Код
=СУММ(--ПОДСТАВИТЬ(ПСТР(ПСТР(A1;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1&СИМВОЛ(10);СТРОКА($1:$999);1)=СИМВОЛ(10);СТРОКА($1:$999));СТРОКА(СМЕЩ(A$1;;;ДЛСТР(A1)-ДЛСТР(ПЕЧСИМВ(A1))+1)))-15;15);ПОИСК(": ";ПСТР(A1;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1&СИМВОЛ(10);СТРОКА($1:$999);1)=СИМВОЛ(10);СТРОКА($1:$999));СТРОКА(СМЕЩ(A$1;;;ДЛСТР(A1)-ДЛСТР(ПЕЧСИМВ(A1))+1)))-15;15))+2;15);"млн.руб.";""))

Автор - Pelena
Дата добавления - 10.11.2016 в 11:14
sv2014 Дата: Четверг, 10.11.2016, 11:37 | Сообщение № 13
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 156
Репутация: 37 ±
Замечаний: 0% ±

Excel 2013
Франц, добрый день,вариант функции в B1, B3 и E1

[vba]
Код
Function yyyy(t$)
With CreateObject("VBScript.RegExp"): .Pattern = " ?\d+,?(?:\d+)?(?=млн\.руб\.)": .Global = True
If .Execute(t).Count >= 2 Then yyyy = CDbl(.Execute(t)(.Execute(t).Count - 1)) + CDbl(.Execute(t)(.Execute(t).Count - 2)) Else yyyy = ""
End With
End Function
[/vba]
К сообщению приложен файл: example_10_11_2.xlsm(19Kb)


Сообщение отредактировал sv2014 - Четверг, 10.11.2016, 11:38
 
Ответить
СообщениеФранц, добрый день,вариант функции в B1, B3 и E1

[vba]
Код
Function yyyy(t$)
With CreateObject("VBScript.RegExp"): .Pattern = " ?\d+,?(?:\d+)?(?=млн\.руб\.)": .Global = True
If .Execute(t).Count >= 2 Then yyyy = CDbl(.Execute(t)(.Execute(t).Count - 1)) + CDbl(.Execute(t)(.Execute(t).Count - 2)) Else yyyy = ""
End With
End Function
[/vba]

Автор - sv2014
Дата добавления - 10.11.2016 в 11:37
Nic70y Дата: Четверг, 10.11.2016, 12:00 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3475
Репутация: 722 ±
Замечаний: 0% ±

Excel 2013
Код
=СУММ(--ПРАВБ(ПОДСТАВИТЬ(ЕСЛИОШИБКА(ПСТР(A1;ЕСЛИ(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));5)="млн.р";СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)))-15);15););" ";ПОВТОР(" ";15));15))
К сообщению приложен файл: 2698581.xlsx(15Kb)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщение
Код
=СУММ(--ПРАВБ(ПОДСТАВИТЬ(ЕСЛИОШИБКА(ПСТР(A1;ЕСЛИ(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));5)="млн.р";СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)))-15);15););" ";ПОВТОР(" ";15));15))

Автор - Nic70y
Дата добавления - 10.11.2016 в 12:00
Франц Дата: Четверг, 10.11.2016, 12:14 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Друзья, Всем спасибо!
Формула Сетлого и подсказка Pelena мою задачу решила.
Для интереса проверю еще последние 2а варианта.
Еще раз, Всем спасибо!
 
Ответить
СообщениеДрузья, Всем спасибо!
Формула Сетлого и подсказка Pelena мою задачу решила.
Для интереса проверю еще последние 2а варианта.
Еще раз, Всем спасибо!

Автор - Франц
Дата добавления - 10.11.2016 в 12:14
Светлый Дата: Четверг, 10.11.2016, 13:27 | Сообщение № 16
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 226
Репутация: 56 ±
Замечаний: 0% ±

Excel 2007
Пропустил всю переписку. Не смог ответить сразу. Доработал формулу (массивная):
Код
=СУММ(--ПОДСТАВИТЬ(ПСТР(ПОДСТАВИТЬ(ПСТР(A1;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1&СИМВОЛ(10);СТРОКА($1:$999);1)=СИМВОЛ(10);СТРОКА($1:$999));СТРОКА(СМЕЩ(A$1;;;ДЛСТР(A1)-ДЛСТР(ПЕЧСИМВ(A1))+1)))-15;15);" ";ПОВТОР(" ";15));15;7);",";ПСТР(1/2;2;1)))

А если немного доделать формулу Nic70y, то получится:
Код
=СУММ(--ПРАВБ(ПОДСТАВИТЬ(ПСТР(A1;ЕСЛИ(ПСТР(A1;СТРОКА($1:$999);5)="млн.р";СТРОКА($1:$999)-15;999);15);" ";ПОВТОР(" ";15))&0;16))

Только миллионы обязательно д.б. с десятичной точкой.


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

Сообщение отредактировал Светлый - Четверг, 10.11.2016, 14:16
 
Ответить
СообщениеПропустил всю переписку. Не смог ответить сразу. Доработал формулу (массивная):
Код
=СУММ(--ПОДСТАВИТЬ(ПСТР(ПОДСТАВИТЬ(ПСТР(A1;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1&СИМВОЛ(10);СТРОКА($1:$999);1)=СИМВОЛ(10);СТРОКА($1:$999));СТРОКА(СМЕЩ(A$1;;;ДЛСТР(A1)-ДЛСТР(ПЕЧСИМВ(A1))+1)))-15;15);" ";ПОВТОР(" ";15));15;7);",";ПСТР(1/2;2;1)))

А если немного доделать формулу Nic70y, то получится:
Код
=СУММ(--ПРАВБ(ПОДСТАВИТЬ(ПСТР(A1;ЕСЛИ(ПСТР(A1;СТРОКА($1:$999);5)="млн.р";СТРОКА($1:$999)-15;999);15);" ";ПОВТОР(" ";15))&0;16))

Только миллионы обязательно д.б. с десятичной точкой.

Автор - Светлый
Дата добавления - 10.11.2016 в 13:27
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выборочная сумма чисел в ячейке с текстом. (Формулы/Formulas)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2016 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!