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

Вход

Регистрация

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

 

= Мир MS Excel/Извлечение чисел из текстовых строк - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Извлечение чисел из текстовых строк (Формулы/Formulas)
Извлечение чисел из текстовых строк
mv6677 Дата: Пятница, 29.12.2017, 11:11 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 71
Репутация: 3 ±
Замечаний: 0% ±

Excel 2013
Здорово, народ.
Вот тут - вопрос у меня - для профессионалов. Есть таблица с текстовыми данными.
В ячейку BA4 - вписан текст, который нужно найти.

Так вот - этот текст встречается в таблице в виде "tpv rttx1" или "tpv rttx7" или "tpv rttx05". То есть - с каким-то числом, после окончания текста.
Если формула находит этот текст - то она выписывает в ячейку диапазона BC7:BC12 - число, стоящее напротив найденного текста.

Однако - если в одной ячейке данное слово встречается дважды... Или слово встречается в двух ячейках одной строки таблицы - то извлечение числа идет только по первому найденному значению.
Как заставить формулу - суммировать все найденные числа - если одинаковые слова встречаются либо в разный ячейках одной строки таблицы, либо в одной ячейке таблицы ?
К сообщению приложен файл: 9455189.xls (71.0 Kb)
 
Ответить
СообщениеЗдорово, народ.
Вот тут - вопрос у меня - для профессионалов. Есть таблица с текстовыми данными.
В ячейку BA4 - вписан текст, который нужно найти.

Так вот - этот текст встречается в таблице в виде "tpv rttx1" или "tpv rttx7" или "tpv rttx05". То есть - с каким-то числом, после окончания текста.
Если формула находит этот текст - то она выписывает в ячейку диапазона BC7:BC12 - число, стоящее напротив найденного текста.

Однако - если в одной ячейке данное слово встречается дважды... Или слово встречается в двух ячейках одной строки таблицы - то извлечение числа идет только по первому найденному значению.
Как заставить формулу - суммировать все найденные числа - если одинаковые слова встречаются либо в разный ячейках одной строки таблицы, либо в одной ячейке таблицы ?
Автор - mv6677
Дата добавления - 29.12.2017 в 11:11
vikttur Дата: Пятница, 29.12.2017, 12:15 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Код
=СУММ(ЕСЛИОШИБКА(--ЛЕВБ(ПОДСТАВИТЬ(ПСТР(G7:G12;ПОИСК(BA4;G7:G12)+ДЛСТР(BA4);4);"-";"    ");4);))

Формула массива, ввод тремя клавишами.
К сообщению приложен файл: 3838359.xls (71.0 Kb)
 
Ответить
Сообщение
Код
=СУММ(ЕСЛИОШИБКА(--ЛЕВБ(ПОДСТАВИТЬ(ПСТР(G7:G12;ПОИСК(BA4;G7:G12)+ДЛСТР(BA4);4);"-";"    ");4);))

Формула массива, ввод тремя клавишами.
Автор - vikttur
Дата добавления - 29.12.2017 в 12:15
mv6677 Дата: Пятница, 29.12.2017, 12:34 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 71
Репутация: 3 ±
Замечаний: 0% ±

Excel 2013
vikttur, что-то не работает ваша формула.

В сумме должно получится 9+5,5=14,5
А у вас - получилось 7,0.

Даже если вы считали только по одному столбцу таблицы - должно получиться 2+5,5=7,5
Тоже не сходится с получившимся результатом 7,0.
 
Ответить
Сообщениеvikttur, что-то не работает ваша формула.

В сумме должно получится 9+5,5=14,5
А у вас - получилось 7,0.

Даже если вы считали только по одному столбцу таблицы - должно получиться 2+5,5=7,5
Тоже не сходится с получившимся результатом 7,0.
Автор - mv6677
Дата добавления - 29.12.2017 в 12:34
sboy Дата: Пятница, 29.12.2017, 12:36 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Добрый день.
Вариант UDF, если правильно логику понял...
[vba]
Код
Function SUM_D(Diapazon As Range, ptrn As String)
    For Each cl In Diapazon.Cells
        txt = txt & cl.Value
    Next cl
    With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = ptrn & "(\d*)"
    Set objMatches = .Execute(txt)
        For i = 0 To objMatches.Count - 1
            Set objSubmatches = objMatches.Item(i).Submatches
                d = CInt(objSubmatches.Item(0))
                    If Left(objSubmatches.Item(0), 1) = "0" Then d = Val("0." & d)
                SUM_D = SUM_D + d
        Next
    End With
End Function
[/vba]
К сообщению приложен файл: 6312135.xls (84.0 Kb)

Яндекс: 410016850021169
 
Ответить
СообщениеДобрый день.
Вариант UDF, если правильно логику понял...
[vba]
Код
Function SUM_D(Diapazon As Range, ptrn As String)
    For Each cl In Diapazon.Cells
        txt = txt & cl.Value
    Next cl
    With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = ptrn & "(\d*)"
    Set objMatches = .Execute(txt)
        For i = 0 To objMatches.Count - 1
            Set objSubmatches = objMatches.Item(i).Submatches
                d = CInt(objSubmatches.Item(0))
                    If Left(objSubmatches.Item(0), 1) = "0" Then d = Val("0." & d)
                SUM_D = SUM_D + d
        Next
    End With
End Function
[/vba]
Автор - sboy
Дата добавления - 29.12.2017 в 12:36
vikttur Дата: Пятница, 29.12.2017, 12:52 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Цитата mv6677, 29.12.2017 в 12:34, в сообщении № 3 ()
у вас - получилось 7,0.

Цитата
rttx2-43
rttx5-tyr

2+5=7

Значит, неправильно понял задачу
 
Ответить
Сообщение
Цитата mv6677, 29.12.2017 в 12:34, в сообщении № 3 ()
у вас - получилось 7,0.

Цитата
rttx2-43
rttx5-tyr

2+5=7

Значит, неправильно понял задачу
Автор - vikttur
Дата добавления - 29.12.2017 в 12:52
mv6677 Дата: Пятница, 29.12.2017, 12:59 | Сообщение № 6
Группа: Пользователи
Ранг: Участник
Сообщений: 71
Репутация: 3 ±
Замечаний: 0% ±

Excel 2013
vikttur, да но вы проигнорировали слово"tpv rttx05", которое должно было еще добавить - число "0,5".

2+5+0,5=7,5


Сообщение отредактировал mv6677 - Пятница, 29.12.2017, 13:20
 
Ответить
Сообщениеvikttur, да но вы проигнорировали слово"tpv rttx05", которое должно было еще добавить - число "0,5".

2+5+0,5=7,5
Автор - mv6677
Дата добавления - 29.12.2017 в 12:59
mv6677 Дата: Пятница, 29.12.2017, 13:01 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 71
Репутация: 3 ±
Замечаний: 0% ±

Excel 2013
sboy, логику вы правильно поняли.

Но я спрашивал про формулу, а не про макрос.
Как то же самое - сделать формулой ?
 
Ответить
Сообщениеsboy, логику вы правильно поняли.

Но я спрашивал про формулу, а не про макрос.
Как то же самое - сделать формулой ?
Автор - mv6677
Дата добавления - 29.12.2017 в 13:01
Светлый Дата: Пятница, 29.12.2017, 13:52 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Попробуйте такую формулу массива:
Код
=СУММ(ЕСЛИОШИБКА(--ЛЕВБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(" "&ПСТР(G7:AZ7;ЕСЛИ(ПСТР(G7:AZ7;СТРОКА($1:$99);ДЛСТР(BA$4))=BA$4;СТРОКА($1:$99))+ДЛСТР(BA$4);5);" 0";" 0,");"-";"    ");5);))

Программировать проще, чем писать стихи.
 
Ответить
СообщениеПопробуйте такую формулу массива:
Код
=СУММ(ЕСЛИОШИБКА(--ЛЕВБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(" "&ПСТР(G7:AZ7;ЕСЛИ(ПСТР(G7:AZ7;СТРОКА($1:$99);ДЛСТР(BA$4))=BA$4;СТРОКА($1:$99))+ДЛСТР(BA$4);5);" 0";" 0,");"-";"    ");5);))
Автор - Светлый
Дата добавления - 29.12.2017 в 13:52
mv6677 Дата: Пятница, 29.12.2017, 14:15 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 71
Репутация: 3 ±
Замечаний: 0% ±

Excel 2013
Светлый, формула не работает

1) Не видит текста "tpv rttx" в ячейке I8

2) Текст в ячейке I11 видит, но выдает число несовпадающее с тем, что стоит напротив текста.
К сообщению приложен файл: 1-2-.xlsb (17.5 Kb)


Сообщение отредактировал mv6677 - Пятница, 29.12.2017, 17:43
 
Ответить
СообщениеСветлый, формула не работает

1) Не видит текста "tpv rttx" в ячейке I8

2) Текст в ячейке I11 видит, но выдает число несовпадающее с тем, что стоит напротив текста.
Автор - mv6677
Дата добавления - 29.12.2017 в 14:15
Светлый Дата: Пятница, 29.12.2017, 21:44 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
В новых данных нет знака "-" после числа, как в первоначальном варианте.
Нашёл ошибку в первом варианте
Код
=СУММ(ЕСЛИОШИБКА(--ЛЕВБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(" "&ПСТР(G7:AZ7;ЕСЛИ(ПСТР(G7:AZ7;СТРОКА($1:$99);ДЛСТР(BA$4))=BA$4;СТРОКА($1:$99)+ДЛСТР(BA$4));5);" 0";ЛЕВБ(1/2;2));"-";"    ");5);))

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

Сообщение отредактировал Светлый - Пятница, 29.12.2017, 22:20
 
Ответить
СообщениеВ новых данных нет знака "-" после числа, как в первоначальном варианте.
Нашёл ошибку в первом варианте
Код
=СУММ(ЕСЛИОШИБКА(--ЛЕВБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(" "&ПСТР(G7:AZ7;ЕСЛИ(ПСТР(G7:AZ7;СТРОКА($1:$99);ДЛСТР(BA$4))=BA$4;СТРОКА($1:$99)+ДЛСТР(BA$4));5);" 0";ЛЕВБ(1/2;2));"-";"    ");5);))
Автор - Светлый
Дата добавления - 29.12.2017 в 21:44
mv6677 Дата: Суббота, 30.12.2017, 07:59 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 71
Репутация: 3 ±
Замечаний: 0% ±

Excel 2013
Светлый, формула по-прежнему не видит текста "tpv rttx" в ячейке I8
(Там после целевого слова - нет знака "-" )
К сообщению приложен файл: 1-3-.xlsb (17.7 Kb)
 
Ответить
СообщениеСветлый, формула по-прежнему не видит текста "tpv rttx" в ячейке I8
(Там после целевого слова - нет знака "-" )
Автор - mv6677
Дата добавления - 30.12.2017 в 07:59
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Извлечение чисел из текстовых строк (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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