Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Как из текста в ячейке найти информацию по заданной маске?
 
#1
20.06.2017 17:25:19
Друзья, прошу помочь!
Имеется столбец, в каждой ячейке содержится информация примерно такая:
"МЕШКИ "ГОЛЬДБАНД ШИРИНА У ОСНОВАНИЯ 13 СМ - 6608 ШТ. МЕШКИ БУМАЖНЫЕ КЛЕЕНЫЕ КЛАПАННЫЕ МНОГОСЛОЙНОГО ТИПА I-5 (ГОСТ 2226-2013)"

Вопрос, как вытащить из ячейки только цифру, которая стоит перед " ШТ." и перенести это значение в соседний столбец?
То есть, если смотреть приложенный файл, как автоматизировать проставление информации в столбец B, который я начал заполнять вручную? Таблица имеет огромное количество строк...
Изменено: 4danila - 20.06.2017 17:58:31 (Добавил файл)
 
 
 
#2
20.06.2017 18:01:19
Прикрепил файл с примером. Информацию в профиле изменил.
 
 
 
#3
20.06.2017 18:08:37
=ЕСЛИОШИБКА(--ПСТР(ПСТР(A2;ПОИСК(" шт. ";A2)-6;6);ПОИСК(" ";ПСТР(A2;ПОИСК(" шт. ";A2)-6;6));6);)
Надежнее:
=ЕСЛИОШИБКА(--ПРАВБ(ПОДСТАВИТЬ(ЛЕВБ(A2;ПОИСК(" шт. ";A2)-1);" ";ПОВТОР(" ";50));50);"")
 
 
 
#4
20.06.2017 18:08:47
Здрасьте.
Под конец рабочего дня - родился монстр:
Код
=ЗНАЧЕН(СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(ЛЕВСИМВ(A2;ПОИСК("ШТ.";A2)-1);" ";"\";ДЛСТР(ЛЕВСИМВ(A2;ПОИСК("ШТ.";A2)-1))-ДЛСТР(ПОДСТАВИТЬ(ЛЕВСИМВ(A2;ПОИСК("ШТ.";A2)-1);" ";""))-1);ДЛСТР(ПОДСТАВИТЬ(ЛЕВСИМВ(A2;ПОИСК("ШТ.";A2)-1);" ";"\";ДЛСТР(ЛЕВСИМВ(A2;ПОИСК("ШТ.";A2)-1))-ДЛСТР(ПОДСТАВИТЬ(ЛЕВСИМВ(A2;ПОИСК("ШТ.";A2)-1);" ";""))-1))-ПОИСК("\";ПОДСТАВИТЬ(ЛЕВСИМВ(A2;ПОИСК("ШТ.";A2)-1);" ";"\";ДЛСТР(ЛЕВСИМВ(A2;ПОИСК("ШТ.";A2)-1))-ДЛСТР(ПОДСТАВИТЬ(ЛЕВСИМВ(A2;ПОИСК("ШТ.";A2)-1);" ";""))-1)))))

Вставить в В2 и тянуть вниз. Там, где вылезет #Знач - в этой строке в исходном значении нет "ШТ."
Кому решение нужно - тот пример и рисует.
 
 
 
#5
20.06.2017 18:31:17
Большое спасибо! Нереально помогли!
 
 
 
#6
20.06.2017 18:42:31
UDF
Код
Function iKol_vo(cell As String)
 With CreateObject("VBScript.RegExp")
     .Global = True
     .IgnoreCase = True
     .Pattern = "\d+ (?=ШТ)"
     If .test(cell) Then
       iKol_vo = .Execute(cell)(0)
     Else
       iKol_vo = "Нет количества в ШТ"
     End If
 End With
End Function
 
 
 
#7
20.06.2017 18:57:30
Kuzmich спасибо Вам тоже!
 
 
 
#8
20.06.2017 19:06:42
Кнопка цитирования не для ответа [МОДЕРАТОР]

Пытливый, можно поправить Вашу гениальную формулу, чтобы в случае отрицательного чисел, умножало ячейку на -1? То есть, чтобы в столбце были всегда положительные числа (см. ячейка В5 и С5 в Пример2). Так же обнаружилась проблема - если перед искомым числом стоит пробел и другое число, то формула сцепляет эти два числа в одно (ячейки В4 и С4 в Пример2).
 
 
 
#9
20.06.2017 19:07:43
=ЕСЛИОШИБКА(--ПРАВБ(ПОДСТАВИТЬ(ЛЕВБ(A2;ПОИСК("?шт";A2));"- ";"000000");7);"")
Алексей М.
 
 
 
#10
20.06.2017 19:13:59
Огромное спасибо, АlехМ !
Формула красивая и работает супер! Только опять те же проблемы, что я описывал в Пример2. Поправить бы их как-нибудь...
 
 
 
#11
20.06.2017 19:16:10
=ЕСЛИОШИБКА(ABS(-ПРАВБ(ПОДСТАВИТЬ(ЛЕВБ(A2;ПОИСК("?шт";A2));"- ";"000000");7));"")
Алексей М.
 
 
 
#12
20.06.2017 19:21:05
Цитата
Поправить бы их как-нибудь...
Код
Function iKol_vo(cell As String) As Double
 With CreateObject("VBScript.RegExp")
     .Global = True
     .IgnoreCase = True
     .Pattern = "\d+ ?(?=ШТ)"
     If .test(cell) Then
       iKol_vo = .Execute(cell)(0)
     Else
       iKol_vo = "Нет количества в ШТ"
     End If
 End With
End Function
 
 
 
#13
20.06.2017 19:25:54
АlехМ, почти идеально!
Еще бы, чтобы ячейку В4 корректно считала (Пример2), было бы совсем супер.
 
 
 
#14
20.06.2017 19:28:01
Цитата
Еще бы, чтобы ячейку В4 корректно считала
А вы попробовали последнюю UDF?
 
 
 
#15
20.06.2017 19:33:29
Цитата
Kuzmich написал:
А вы попробовали последнюю UDF?
Спасибо! Читаю как раз об этом в учебнике для чайников.. Ни разу сам не использовал.
 
 
 
#16
20.06.2017 19:35:13
Цитата
danil77 написал:
Еще бы, чтобы ячейку В4 корректно считала
А что там не получилось?
В В4 = 133250 без минуса.
PS. понял. там 250 должно быть.
Изменено: АlехМ - 20.06.2017 19:36:59
Алексей М.
 
 
 
#17
20.06.2017 19:39:34
danil77, не цитируйте без необходимости.

Вы вторую формулу из сообщения №3 пробовали применять? Добавим к ней перевод в положительные:
=ЕСЛИОШИБКА(ABS(--ПРАВБ(ПОДСТАВИТЬ(ЛЕВБ(A2;ПОИСК(" шт. ";A2)-1);" ";ПОВТОР(" ";50));50));"")
 
 
 
#18
20.06.2017 19:41:49
=ЕСЛИОШИБКА(ABS(-ПРАВБ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ЛЕВБ(A2;ПОИСК("?шт";A2)));" ";"000000");7));"")
Алексей М.
 
 
 
#19
20.06.2017 19:46:57
АlехМ, теперь вроде идеально работает! Спасибо Вам большое!
vikttur, спасибо Вам тоже! Ваша формула почему то у меня в Примере2 вообще пустые ячейки выдала в В4 и В5.
 
 
 
#20
20.06.2017 19:54:55
Во втором примере другие данные, без  пробела перед "шт."
Формулу нужно доработать, но уже неинтересно.  Нужно сразу показывать разные исходные данные, а не догонять дополнениями.
 
 
 
#21
20.06.2017 21:42:58
vikttur, я конечно был неправ, что не выложил весь массив.
Вылезли новые проблемы с исходной базой. Проанализировал всю базу, обнаружил последний обидный но часто повторяющийся косяк с исходными данными. В одной и той же ячейке может быть записано несколько раз "шт". То есть в данном случае нужно либо подсчитывать сумму по каждому повторению "шт" (см строку 65 Пример_мес05_2017) и записать это значение в столбец "Количество шт". Либо видимо что гораздо проще для этих случаев создать новый столбец, в котором подсчитывалось бы количество повторений "шт" в одной ячейке. Как бы это сделать теперь, хотя бы формулу для количества повторений "шт" в одной ячейке...
 
 
 
#22
20.06.2017 22:13:17
Цитата
подсчитывать сумму по каждому повторению "шт"
Код
Function iKol_vo(cell As String) As Double
Dim mo As Object
Dim n As Integer
 With CreateObject("VBScript.RegExp")
     .Global = True
     .IgnoreCase = True
     .Pattern = "\d+ ?(?=ШТ)"
     If .test(cell) Then
       Set mo = .Execute(cell)
       For n = 0 To mo.Count - 1
         iKol_vo = iKol_vo + mo.Item(n)
       Next
     Else
       iKol_vo = 0
     End If
 End With
End Function
 
 
 
#23
20.06.2017 22:36:39
Цитата
для количества повторений "шт" в одной ячейке...
Код
Function iKol_vo_repeat(cell As String) As Double
Dim mo As Object
 With CreateObject("VBScript.RegExp")
     .Global = True
     .IgnoreCase = True
     .Pattern = "\d+ ?(?=ШТ)"
     If .test(cell) Then
       Set mo = .Execute(cell)
         iKol_vo_repeat = mo.Count
     Else
       iKol_vo_repeat = 0
     End If
 End With
End Function
 
 
 
#24
21.06.2017 10:37:04
Kuzmich, спасибо Вам огромное! Золотой Вы человек!!!
 
 
 
#25
26.06.2017 19:57:16
Прошу помощи.
UDF "iKol_vo", которую сделал уважаемый Kuzmich работает почти идеально.
Осталось 2 случая, когда UDF считает не так как надо. (см Пример4.xlsx)
1. Иногда в исходной базе под "ШТ" подразумевается тысячи штук изделия. В таком случае стоит какая-то цифра (иногда целое, иногда дробь) потом всегда пробел и всегда "1000".
2. Вторая проблема связана с тем, что в исходной базе иногда требуемое число разделяет пробел, который нужно удалить, чтобы суммы всех чисел считались правильно.

Понимаю, что исходную UDF допилить нереально, чтобы она учитывала еще и эти два случая. Но я бы мог выделить в базе только строки с проблемой 1 и строки с проблемой 2. А потом их как-то отдельно обработать.
Можно сделать еще 2 UDF для случая 1 и случая 2 ?
 
 
 
#26
26.06.2017 21:30:08
Цитата
Иногда в исходной базе под "ШТ" подразумевается тысячи штук изделия
Попробуйте такую UDF
Код
Function iKol_vo_1(cell As String) As Double
Dim mo As Object
Dim n As Integer
 With CreateObject("VBScript.RegExp")
     .Global = True
     .IgnoreCase = True
     .Pattern = "\d+,?\d?\d?(?= 1000 ШТ)"
     If .test(cell) Then
       Set mo = .Execute(cell)
       For n = 0 To mo.Count - 1
         iKol_vo_1 = iKol_vo_1 + mo.Item(n) * 1000
       Next
     Else
       iKol_vo_1 = 0
     End If
 End With
End Function
Цитата
Вторая проблема связана с тем, что в исходной базе иногда требуемое число разделяет пробел
Код
Function iKol_vo(cell As String) As Double
Dim mo As Object
Dim n As Integer
 With CreateObject("VBScript.RegExp")
     .Global = True
     .IgnoreCase = True
     .Pattern = "\d+ ?\d+ ?(?=ШТ)"
     If .test(cell) Then
       Set mo = .Execute(cell)
       For n = 0 To mo.Count - 1
         iKol_vo = iKol_vo + mo.Item(n)
       Next
     Else
       iKol_vo = 0
     End If
 End With
End Function
 
 
 
#27
26.06.2017 21:41:01
Цитата
Понимаю, что исходную UDF допилить нереально, чтобы она учитывала еще и эти два случая.
Попробуйте так
Код
Function iSumma(cell As String) As Double
Dim mo As Object
Dim n As Integer
 With CreateObject("VBScript.RegExp")
     .Global = True
     .IgnoreCase = True
  If InStr(1, cell, " 1000 ШТ") Then
     .Pattern = "\d+,?\d?\d?(?= 1000 ШТ)"
     If .test(cell) Then
       Set mo = .Execute(cell)
       For n = 0 To mo.Count - 1
         iSumma = iSumma + mo.Item(n) * 1000
       Next
     Else
       iSumma = 0
     End If
  Else
       .Pattern = "\d+ ?\d+ ?(?=ШТ)"
     If .test(cell) Then
       Set mo = .Execute(cell)
       For n = 0 To mo.Count - 1
         iSumma = iSumma + mo.Item(n)
       Next
     Else
       iSumma = 0
     End If
  End If
 End With
End Function
Только у меня по третьей строке получилась сумма 207450 в отличии от вашей 206550
 
 
 
#28
26.06.2017 22:22:32
Это невероятно, Kuzmich!!! Спасибо Вам огромное!!!
 
 
 
#29
27.06.2017 15:16:47
Иногда UDF iSumma выдаёт неверное значение. Выделил жёлтым цветом (первые три строки Примера).
Подскажите, пожалуйста, в чём причина?
 
 
 
#30
27.06.2017 17:29:40
Цитата
в чём причина?
В ваших прошлых примерах было два знака после запятой, а в этом три
Измените строчку
Код
     .Pattern = "\d+,?\d?\d?\d?(?= 1000 ШТ)"
 
 
 
Страницы: 1 2 След.
Наверх