Тор интересуется, есть ли способ выполнить поиск без необходимости указывать столбец или строку, а результатом будет адрес ячейки, в которой найдено значение. Например, он хочет найти значение (например, 345 или «мой текст») и заставить функцию выполнять поиск по всем ячейкам на всех листах книги и возвращать полный адрес ячейки, в которой было найдено значение.

Используемый вами подход будет зависеть от диапазона, в котором вы хотите выполнить поиск.

Если вы хотите выполнить поиск на том же листе, на котором вы хотите отобразить ответ, вы можете использовать формулу, например следующую:

=ADDRESS(MAX(ROW(1:5)(A1:E5="my text")), MAX(COLUMN(A1:E1)(A1:E5="my text")),4)

Его следует ввести как формулу массива (нажмите Ctrl + Shift + Enter), и поиск выполняется только в диапазоне A1: E5. При желании вы можете изменить диапазон, изменив формулу соответствующим образом.

Большая область поиска — это просмотр всего рабочего листа. Это все еще можно сделать с помощью формулы массива, например следующей:

=ADDRESS(MAX(ROW(Sheet1!1:65000)(IF(Sheet1!1:65000=$A$1,1,0))), MAX(COLUMN(Sheet1!$1:$65000)IF(Sheet1!1:65000=$A$1,1,0)))

Формула предполагает, что то, что вы ищете, хранится в ячейке A1.

Вы должны изменить обозначение Sheet1 на имя любого листа, который вы хотите найти.

Если вы хотите выполнить поиск в более широком диапазоне, например на всех листах в книге, лучшим решением будет использование макроса, который вызывает функцию «Найти» в Excel.

Function FindAddr(vValue As Variant)

Dim wks As Worksheet     Dim rCell As Range     Dim bFound As Boolean

bFound = False     For Each wks In ActiveWorkbook.Worksheets         With wks             Set rCell = .Cells.Find _               (What:=vValue, After:=.Cells(1), _               LookIn:=xlValues, LookAt:=xlWhole, _               SearchOrder:=xlByRows, _               SearchDirection:=xlNext, _               MatchCase:=False)

If Not rCell Is Nothing Then                 bFound = True                 Exit For             End If         End With     Next     If bFound Then         FindAddr = wks.Name & "!" & _           rCell.Address(False, False)

Else         FindAddr = "Not Found"

End If     Set wks = Nothing     Set rCell = Nothing End Function

Эта функция предназначена для вызова из другого макроса, который передает ей все, что нужно искать в параметре vValue. Функция возвращает либо полный адрес (включая имя рабочего листа) первого совпадения, либо возвращает «Не найдено», если совпадений не было.

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (11524) применим к Microsoft Excel 2007, 2010, 2013 и 2016.

Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Searching_for_a_Value_Using_a_Function [Поиск значения с помощью функции].