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

[ForumOOo (бот)]

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

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


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

[JohnSUN]

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

Возможны еще варианты для разных данных (числа или строки, порядок сортировки и т.п)

[Hasim]

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

[JohnSUN]

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

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

[rogi]

Извините, уважаемые, но я ничего не понял. Скажите, пожалуйста, подробно как найти первое и последнее вхождение слова Ярославль в ячейки
А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) тоже положение не спасают: находит не первую ячейку, содержащую "Ярославль", а "последнюю перед не содержащей".

[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;"Ярославль") - последний

[Hasim]

А можно с помощью макроса найти любое повторение? Например, 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 предназначался не для этого. Его основная задача - работа с числами. То что он умеет работать еще и со строками, не означает, что он обязан это делать.
Помню, видел в одной организации - бухгалтер печатала приказ на премирование в Экселе и страшно ругалась, потому что выравнивать пробелами отступы строк в ячейках очень утомительно...

[DixiX57]

А можно с помощью макроса найти любое повторение? Например, 2 и 4.

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

[Hasim]

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

[JohnSUN]

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

Нет, к сожалению, не правильно. Это не номер, это само значение ячейки вся строка целиком, в которой нашлось слово "Ярославль".
И поскольку третий параметр не задан, это последнее значение в списке.
А настоящие количество ячеек со словом "Ярославль" ищется по формуле

Код Выделить Развернуть

=COUNTIF(A1:A7;".*Ярославль.*")

Тогда последнее повторение находим так
=GETVALUEBYSTRING(A1:A7;"Ярославль";GETVALUEBYSTRING(A1:A7;"Ярославль"))

Видимо имелось в виду =GETSTRINGBYRANK($A$1:$A$7;"Ярославль";COUNTIF(A1:A7;".*Ярославль.*")) ? В столь сложных наворотах, мне кажется, нет необходимости

[Hasim]

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

[JohnSUN]

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