среда, 3 июня 2015 г.

Функция SEARCH для поиска текста внутри предложений

Для некоторых программ проводящих фильтрацию данных документа возникает необходимость убрать/отфильтровать строки содержащие определенное слово или словосочетание. Обычные функции типа LEFT не подходят - т.к. это слово может встретиться в любой части строки - в начале, середине, конце.
Для решения такой задачи можно использовать построение дополнительных столбцов с функцией SEARCH, которая ищет заданное слово во всей строке. Для каждого условия нужен отдельный столбец для построения. Функция возвращает число больше нуля, если строка найдена (число будет обозначать порядковый номер первого символа найденного слова). Если у итоге нужно отфильтровать все условия эти числа просто суммируются с отдельном столбце и затем проводится фильтрация по условию не равно нулю.
Рассмотрим код из макроса "Продажи без рецептов":

Sub Formul_poisk
  oCell = ThisComponent.Sheets(0).GetCellbyPosition( 0, 0 )
ThisComponent.CurrentController.Select(oCell)
odoc=thiscomponent
oSheet = ThisComponent.Sheets(0)
oCellCursor = oSheet.createCursor()
oCellCursor.GotoStartOfUsedArea(True)
oCellCursor.GotoEndOfUsedArea(True)
Start_Row = oCellCursor.getRangeAddress.StartRow
End_Row = oCellCursor.getRangeAddress.EndRow
currcell = osheet.getcellbyposition(11, End_Row)
currcell2 = osheet.getcellbyposition(3, End_Row)
currcell3 = osheet.getcellbyposition(12, End_Row)
currcell5 = osheet.getcellbyposition(13, End_Row)
currcell4 = osheet.getcellbyposition(14, End_Row)
currcell7 = osheet.getcellbyposition(15, End_Row)
currcell16 = osheet.getcellbyposition(16, End_Row)
currcell8 = osheet.getcellbyposition(8, End_Row)
oConv8 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv8.Address = currcell8.getCellAddress
oConv = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv.Address = currcell.getCellAddress
oConv2 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv2.Address = currcell2.getCellAddress
oConv3 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv3.Address = currcell3.getCellAddress
oConv5 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv5.Address = currcell5.getCellAddress
oConv4 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv4.Address = currcell4.getCellAddress
oConv7 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv7.Address = currcell16.getCellAddress
oConv16 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv16.Address = currcell16.getCellAddress
'Данными формулами мы ищем во всех строках нужные нам сочетания
' Причем каждую строку мы проверяем на все сочетания
 Podstanovka  = "=SEARCH("&Chr(34) &"фас"& Chr(34) &";" & oConv2.UserInterfaceRepresentation & ")"
 currcell.setFormula (Podstanovka)
  Podstanovka2  = "=SEARCH("&Chr(34) &"уценен"& Chr(34) &";" & oConv2.UserInterfaceRepresentation & ")"
 currcell3.setFormula (Podstanovka2)
   Podstanovka3  = "=SEARCH("&Chr(34) &"СП ГМ"& Chr(34) &";" & oConv2.UserInterfaceRepresentation & ")"
currcell5.setFormula (Podstanovka3)
odoc.getCurrentController().Select(currcell)
   Podstanovka4  = "=SEARCH("&Chr(34) &"СПГМ"& Chr(34) &";" & oConv2.UserInterfaceRepresentation & ")"
currcell4.setFormula (Podstanovka4)
odoc.getCurrentController().Select(currcell)
   Podstanovka5  = "=SEARCH("&Chr(34) &"СП  ГМ"& Chr(34) &";" & oConv2.UserInterfaceRepresentation & ")"
currcell7.setFormula (Podstanovka5)
   Podstanovka6  = "=SEARCH("&Chr(34) &"разлив"& Chr(34) &";" & oConv2.UserInterfaceRepresentation & ")"
currcell16.setFormula (Podstanovka6)
odoc.getCurrentController().Select(currcell)
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args19(1) as new com.sun.star.beans.PropertyValue
args19(0).Name = "By"
args19(0).Value = 1
args19(1).Name = "Sel"
args19(1).Value = true
'Выделяем весь нижний ряд формул
dispatcher.executeDispatch(document, ".uno:GoRightToEndOfData", "", 0, args19())
dim args29(1) as new com.sun.star.beans.PropertyValue
args29(0).Name = "By"
args29(0).Value = 1
args29(1).Name = "Sel"
args29(1).Value = true
' Протягиваем все полученные формулы вверх до конца столбцов
dispatcher.executeDispatch(document, ".uno:GoUpToStartOfData", "", 0, args29())
dispatcher.executeDispatch(document, ".uno:FillUp", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:FillUp", "", 0, Array())
'вставляем осмысленные заголовки
ThisComponent.Sheets(0).getCellByPosition (11, 0).String = "Поиск сочетания "&Chr(34) &"фас"& Chr(34) & ""
ThisComponent.Sheets(0).getCellByPosition (12, 0).String = "Поиск сочетания "&Chr(34) &"уценен"& Chr(34) &""
ThisComponent.Sheets(0).getCellByPosition (13, 0).String = "Поиск сочетания "&Chr(34) &"СП ГМ"& Chr(34) &""
ThisComponent.Sheets(0).getCellByPosition (14, 0).String = "Поиск сочетания "&Chr(34) &"СПГМ"& Chr(34) &""
ThisComponent.Sheets(0).getCellByPosition (15, 0).String = "Поиск сочетания "&Chr(34) &"СП  ГМ"& Chr(34) &""
ThisComponent.Sheets(0).getCellByPosition (16, 0).String = "Поиск сочетания "&Chr(34) &"разливное"& Chr(34) &""
'Убираем формулы
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dim args2(5) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Flags"
args2(0).Value = "SV"
args2(1).Name = "FormulaCommand"
args2(1).Value = 0
args2(2).Name = "SkipEmptyCells"
args2(2).Value = false
args2(3).Name = "Transpose"
args2(3).Value = false
args2(4).Name = "AsLink"
args2(4).Value = false
args2(5).Name = "MoveMode"
args2(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args2())
'Меняем ошибку "#ЗНАЧЕН!" на ноль (это можно сделать и помощью более короткой
' функции поиска и замены описанной ранее)
dim args1(17) as new com.sun.star.beans.PropertyValue
args1(0).Name = "SearchItem.StyleFamily"
args1(0).Value = 2
args1(1).Name = "SearchItem.CellType"
args1(1).Value = 0
args1(2).Name = "SearchItem.RowDirection"
args1(2).Value = true
args1(3).Name = "SearchItem.AllTables"
args1(3).Value = false
args1(4).Name = "SearchItem.Backward"
args1(4).Value = false
args1(5).Name = "SearchItem.Pattern"
args1(5).Value = false
args1(6).Name = "SearchItem.Content"
args1(6).Value = false
args1(7).Name = "SearchItem.AsianOptions"
args1(7).Value = false
args1(8).Name = "SearchItem.AlgorithmType"
args1(8).Value = 1
args1(9).Name = "SearchItem.SearchFlags"
args1(9).Value = 65536
args1(10).Name = "SearchItem.SearchString"
args1(10).Value = "#ЗНАЧЕН!"
args1(11).Name = "SearchItem.ReplaceString"
args1(11).Value = "0"
args1(12).Name = "SearchItem.Locale"
args1(12).Value = 255
args1(13).Name = "SearchItem.ChangedChars"
args1(13).Value = 2
args1(14).Name = "SearchItem.DeletedChars"
args1(14).Value = 2
args1(15).Name = "SearchItem.InsertedChars"
args1(15).Value = 2
args1(16).Name = "SearchItem.TransliterateFlags"
args1(16).Value = 1280
args1(17).Name = "SearchItem.Command"
args1(17).Value = 3
dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args1())
'Вводим еще одни столбец для суммирования
 currcell6 = osheet.getcellbyposition(17, End_Row)
 oConv6 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv6.Address = currcell6.getCellAddress
'Складываем полученные цифры во всех столбцах в один, чтобы в последствие по нему 'провести фильтрацию по признаку не равно нулю
Modul3 =  "=IF(" & oConv.UserInterfaceRepresentation & "+" & oConv3.UserInterfaceRepresentation & "+" & oConv5.UserInterfaceRepresentation & "+" & oConv4.UserInterfaceRepresentation & "+" & oConv7.UserInterfaceRepresentation &   "+" & oConv16.UserInterfaceRepresentation & " >0;0;1)"
currcell6.setFormula (Modul3)
odoc.getCurrentController().Select(currcell6)
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args195(1) as new com.sun.star.beans.PropertyValue
args195(0).Name = "By"
args195(0).Value = 1
args195(1).Name = "Sel"
args195(1).Value = true
dispatcher.executeDispatch(document, ".uno:GoUpToStartOfData", "", 0, args195())
dispatcher.executeDispatch(document, ".uno:FillUp", "", 0, Array())
ThisComponent.Sheets(0).getCellByPosition (17, 0).String = "Сравнение"
End Sub

P.S. Чтобы не заниматься заменой ошибок ЗНАЧЕН и не удалять формулы можно воспользоваться как и с функцией VLOOKUP более сложным вариантом формулы с использованием IF и ISERROR;

 Podstanovka  = "=IF(ISERROR(SEARCH("&Chr(34) &"сырье"& Chr(34) &";" & oConv2.UserInterfaceRepresentation & "));0;1)"

Задача: В файле реестр товарных позиций, запущенному по подтипам списаний по МХ осуществить поиск по условиям "сырье" и "п\ф" в двух крайних столбцах с помощью усложненной формулы. Ввести суммирующих столбец и по нему отфильтровать данные на новый лист по признаку равно нулю.

Комментариев нет:

Отправить комментарий