Функция LOOKUP() находит первое повторение и возвращает з

Автор ForumOOo (бот), 24 января 2012, 17:13

0 Пользователи и 1 гость просматривают эту тему.

ForumOOo (бот)

Компонент: Calc
Версия продукта: 3.2
Сборка: openoffice pro
ОС: Ubuntu 11.10

Функция LOOKUP() находит первое повторение и возвращает значение а Как
найти последнее повторение??? подскажите плиззз


--
Подпись: donald
Эл. почта: petrov_dm@mail.ru

JohnSUN

Добро пожаловать на форум, donald!
Обычно это делается двумя функциями OFFSET() и MATCH(): MATCH ищет индекс (какой именно - описывается в четвертом параметре), а OFFSET возвращает найденный результат.

Возможны еще варианты для разных данных (числа или строки, порядок сортировки и т.п)
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

Hasim

Извините, уважаемые, но я ничего не понял. Скажите, пожалуйста, подробно как найти первое и последнее вхождение слова Ярославль в ячейки
А1 Олег (Кострома), 1960 г.р.
А2 Иван (Ярославль), 1958 г.р.
А3 Сергей (Ярославль), 1973 г.р.
А4 Виктор (Кострома), 1961 г.р.
А5 Владимир (Ярославль), 1968 г.р.
А6 Тимур (Ярославль), 1972 г.р.
А7 Михаил (Кострома), 1963 г.р.

JohnSUN

Это сложная задача. Решаемая, но сложная. Главная трудность в том, что искомый текст "спрятан" между другими буквами, внутри строки. Сортировка данных в таком случае не даст никаких результатов.
Как всякая непростая задача, эта имеет несколько вариантов решений.
1. Стандартный фильтр (Меню Данные-Фильтр). Если бы я решал эту задачу, то воспользовался бы именно этим способом. См. первую картинку.
2. Преобразовать данные так, чтобы искомое значение было в отдельном столбце - Данные-Текст по столбцам. См. вторую картинку.
3. В свободном столбце - рядом с данными или на отдельном вспомогательном листе - ввести формулу, которая сделает нужные вычисления (собственно поиск - найдено значение или нет), а формула результата уже будет использовать значения из этого вспомогательного столбца.
4. Наворотить заумную формулу массива, которая вернет нужное значение. Как по мне, так это самое плохое решение: формула получится очень сложной, плохо читаемой и не понятной для тех, кто будет ею пользоваться потом. И, значит, её трудно будет изменить в случае необходимости и не наделать при этом ошибок.

[вложение удалено Администратором]
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

rogi

Цитата: Hasim от 25 января 2012, 12:45
Извините, уважаемые, но я ничего не понял. Скажите, пожалуйста, подробно как найти первое и последнее вхождение слова Ярославль в ячейки
А1 Олег (Кострома), 1960 г.р.
А2 Иван (Ярославль), 1958 г.р.
А3 Сергей (Ярославль), 1973 г.р.
А4 Виктор (Кострома), 1961 г.р.
А5 Владимир (Ярославль), 1968 г.р.
А6 Тимур (Ярославль), 1972 г.р.
А7 Михаил (Кострома), 1963 г.р.
Попробовал для этого списка функцию LOOKUP("Ярославль";A1:A7)
и получил  ??? Михаил (Кострома), 1963 г.р. (см. приложенный файлик)
Это как это получилось? Не понимаю действие функции...

P.S. Win7 OOo Pro 3.3.0


[вложение удалено Администратором]

JohnSUN

Да там фишка в чем - LOOKUP просто не нашел ни одной ячейки со словом "Ярославль".
Как сказано в справке: "Если с помощью функции LOOKUP не удается установить критерий поиска, то он соответствует самому большому значению в векторе просмотра, который меньше, чем критерий поиска, или равен ему."
Самая "большая" строка, ближайшая к букве "Я" - "А7 Михаил (Кострома), 1963 г.р."
Включение автоматической оценки регулярных выражений и изменение формулы до вида =LOOKUP(".*Ярославль.*";$A$2:$A$8) тоже положение не спасают: находит не первую ячейку, содержащую "Ярославль", а "последнюю перед не содержащей".
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

DixiX57

Если я правильно понял задачу, то во Вам банальная программка на basic для решения.

function MyStrFind(str1 as string, str2 as string) as integer
if instr(str2, str1)<> 0 then
MyStrFind=1
else
MyStrFind=0
end if
end function

Во вложенном файле пример. Ну а дальше - развивайте.

[вложение удалено Администратором]

JohnSUN

Ну, если с помощью макроса, то тогда уже лучше так:
REM Функция возвращает значение первой или последней ячейки в массиве
REM (диапазоне ячеек) arrData, содержащей значение whatFind.
REM Если findFirst равен 0 или Ложь, то возвращается последнее найденное значение
REM Если findFirst не равен 0, или равен Истина, или не указан, то первое найденное
REM Просматривается только первый столбец указанного диапазона
Function getValueByString(arrData, whatFind$, Optional findFirst)
Dim i&
If IsMissing(findFirst) Then findFirst = True
If findFirst Then ' Ищем последний
For i = UBound(arrData) To 1 Step -1
If InStr(arrData(i, 1), whatFind) Then
getValueByString = arrData(i, 1)
Exit Function
EndIf
Next i
Else ' Ищем первый
For i = 1 To UBound(arrData)
If InStr(arrData(i, 1), whatFind) Then
getValueByString = arrData(i, 1)
Exit Function
EndIf
Next i
EndIf
End Function

И вызывать как
=GETVALUEBYSTRING(A1:A7;"Ярославль";0) - первый
=GETVALUEBYSTRING(A3:A9;"Ярославль") - последний
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

Hasim

#8
А можно с помощью макроса найти любое повторение? Например, 2 и 4.
=GETVALUEBYSTRING(A1:A7;"Ярославль";2)
=GETVALUEBYSTRING(A3:A9;"Ярославль";4)

JohnSUN

Конечно, можно...
Function getStringByRank(arrData, whatFind$, Rank&)
Dim i&
Dim wasFound&
wasFound = 0
getStringByRank = Null ' На случай, если не найдем - ячека останется пустой
For i = LBound(arrData) To UBound(arrData)
If InStr(arrData(i, 1), whatFind) Then
wasFound = wasFound+1
If wasFound = Rank Then
getStringByRank = arrData(i, 1)
Exit Function
EndIf
EndIf
Next i
End Function

Но, если честно, Calc предназначался не для этого. Его основная задача - работа с числами. То что он умеет работать еще и со строками, не означает, что он обязан это делать.
Помню, видел в одной организации - бухгалтер печатала приказ на премирование в Экселе и страшно ругалась, потому что выравнивать пробелами отступы строк в ячейках очень утомительно...
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

DixiX57

Цитата: Hasim от 26 января 2012, 15:31А можно с помощью макроса найти любое повторение? Например, 2 и 4.
Тут есть "грабли". Номер повторения будет зависеть от того как отсортированы исходные данные (и отсортированы ли вообще!). Нужно быть очень внимательным, особенно при использовании таких функций через некоторое время, когда нюансы забываются... Поэтому, может быть автор озвучит задачу полностью? Может быть, вообще есть более простое глобальное решение. Так часто бывает...

Hasim

А можно с помощью этого же макроса найти количество повторений?
=GETVALUEBYSTRING(A1:A7;"Ярославль") - сколько раз встречается слово Ярославль
Это будет и номер последнего повторения? Правильно?
Тогда последнее повторение находим так
=GETVALUEBYSTRING(A1:A7;"Ярославль";GETVALUEBYSTRING(A1:A7;"Ярославль"))

JohnSUN

Цитата: Hasim от 26 января 2012, 17:37
А можно с помощью этого же макроса найти количество повторений?
=GETVALUEBYSTRING(A1:A7;"Ярославль") - сколько раз встречается слово Ярославль
Это будет и номер последнего повторения? Правильно?
Нет, к сожалению, не правильно. Это не номер, это само значение ячейки вся строка целиком, в которой нашлось слово "Ярославль".
И поскольку третий параметр не задан, это последнее значение в списке.
А настоящие количество ячеек со словом "Ярославль" ищется по формуле
=COUNTIF(A1:A7;".*Ярославль.*")
Цитата: Hasim от 26 января 2012, 17:37
Тогда последнее повторение находим так
=GETVALUEBYSTRING(A1:A7;"Ярославль";GETVALUEBYSTRING(A1:A7;"Ярославль"))
Видимо имелось в виду =GETSTRINGBYRANK($A$1:$A$7;"Ярославль";COUNTIF(A1:A7;".*Ярославль.*")) ? В столь сложных наворотах, мне кажется, нет необходимости
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

Hasim

=COUNTIF(A1:A7;".*Ярославль.*")
не считает, получается 0 ???

JohnSUN

Строка условия подсчета содержит регулярные выражения ".*" - "любое количество любых символов".
Чтобы Calc воспринимал эти два символа именно как регулярное выражение, нужно войти в меню Сервис-Параметры, в дереве слева выбрать Calc-Вычисления и установить флажок "Разрешить регулярные выражения в формулах"
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне