Как найти адрес ячейки в excel vba

Обновлено: 21.11.2024

При использовании формул поиска в Excel (таких как ВПР, ВПР или ИНДЕКС/ПОИСКПОЗ) цель состоит в том, чтобы найти совпадающее значение и получить это значение (или соответствующее значение в той же строке/столбце) в качестве результата.

Но в некоторых случаях вместо получения значения может потребоваться, чтобы формула возвращала адрес ячейки значения.

Это может быть особенно полезно, если у вас большой набор данных и вы хотите узнать точную позицию результата формулы поиска.

В Excel есть несколько функций, предназначенных именно для этого.

В этом руководстве я покажу вам, как с помощью простых формул найти и вернуть адрес ячейки вместо значения в Excel.

Это руководство охватывает:

Поиск и возврат адреса ячейки с помощью функции ADDRESS

Функция АДРЕС в Excel предназначена именно для этого.

Он берет строку и номер столбца и дает вам адрес этой конкретной ячейки.

Ниже приведен синтаксис функции АДРЕС:

  • номер_строки: номер строки ячейки, для которой требуется адрес ячейки.
  • column_num: номер столбца ячейки, для которой вы хотите получить адрес
  • [abs_num]: необязательный аргумент, в котором можно указать, будет ли ссылка на ячейку абсолютной, относительной или смешанной.
  • [a1]: необязательный аргумент, в котором можно указать, хотите ли вы использовать ссылку в стиле R1C1 или стиле A1.
  • [sheet_text]: необязательный аргумент, в котором вы можете указать, хотите ли вы добавить имя листа вместе с адресом ячейки или нет.

Теперь давайте рассмотрим пример и посмотрим, как это работает.

Предположим, что есть набор данных, как показано ниже, где у меня есть идентификатор сотрудника, его имя и его отдел, и я хочу быстро узнать адрес ячейки, которая содержит отдел для идентификатора сотрудника KR256.

Ниже приведена формула, которая это сделает:

В приведенной выше формуле я использовал функцию ПОИСКПОЗ, чтобы узнать номер строки, содержащей заданный идентификатор сотрудника.

А поскольку отдел находится в столбце C, я использовал 3 в качестве второго аргумента.

Эта формула прекрасно работает, но у нее есть один недостаток: она не будет работать, если вы добавите строку над набором данных или столбец слева от набора данных.

Это связано с тем, что когда я указываю второму аргументу (номер столбца) значение 3, оно жестко закодировано и не изменится.

Если я добавлю любой столбец слева от набора данных, формула будет считать 3 столбца с начала рабочего листа, а не с начала набора данных.

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

Но если вам нужно, чтобы это было более надежным, используйте вариант, описанный в следующем разделе.

Поиск и возврат адреса ячейки с помощью функции CELL

Хотя функция АДРЕС была создана специально для предоставления ссылки на ячейку с указанным номером строки и столбца, существует и другая функция, которая делает то же самое.

Она называется функцией ЯЧЕЙКА (и может дать гораздо больше информации о ячейке, чем функция АДРЕС).

Ниже приведен синтаксис функции ЯЧЕЙКА:

  • info_type: информация о нужной ячейке. Это может быть адрес, номер столбца, имя файла и т. д.
  • [ссылка]: необязательный аргумент, в котором вы можете указать ссылку на ячейку, для которой вам нужна информация о ячейке.

Теперь давайте рассмотрим пример, в котором вы можете использовать эту функцию для поиска и получения ссылки на ячейку.

Предположим, у вас есть набор данных, как показано ниже, и вы хотите быстро узнать адрес ячейки, в которой находится отдел для идентификатора сотрудника KR256.

Ниже приведена формула, которая это сделает:

Приведенная выше формула довольно проста.

Я использовал формулу ИНДЕКС в качестве второго аргумента, чтобы получить отдел для идентификатора сотрудника KR256.

А затем просто завернул его в функцию CELL и попросил вернуть адрес ячейки этого значения, которое я получаю из формулы INDEX.

Теперь вот секрет того, почему это работает: формула ИНДЕКС возвращает искомое значение, когда вы даете ей все необходимые аргументы. Но в то же время он также вернет ссылку на результирующую ячейку.

В нашем примере формула ИНДЕКС возвращает "Продажи" в качестве результирующего значения, но в то же время вы также можете использовать ее для получения ссылки на ячейку этого значения вместо самого значения.

Обычно, когда вы вводите формулу ИНДЕКС в ячейку, она возвращает значение, потому что это то, что от нее ожидается. Но в случаях, когда требуется ссылка на ячейку, формула ИНДЕКС даст вам ссылку на ячейку.

В этом примере это именно то, что он делает.

Лучшее в использовании этой формулы то, что она не привязана к первой ячейке листа. Это означает, что вы можете выбрать любой набор данных (который может находиться в любом месте рабочего листа), использовать формулу ИНДЕКС для обычного поиска, и он все равно даст вам правильный адрес.

И если вы вставите дополнительную строку или столбец, формула изменится соответствующим образом, чтобы дать вам правильный адрес ячейки.

Итак, это две простые формулы, которые можно использовать для поиска и возврата адреса ячейки вместо значения в Excel.

Активная ячейка — это выбранная в данный момент ячейка на листе. Активная ячейка в VBA может использоваться как ссылка для перехода к другой ячейке или изменения свойств той же активной ячейки или ссылки на ячейки, полученной из активной ячейки. , доступ к активной ячейке в VBA можно получить с помощью метода application.property с ключевым словом активная ячейка.

В Excel есть миллионы ячеек, и вы наверняка сомневаетесь, какая из них является активной ячейкой. Например, посмотрите на изображение ниже.

На самой картинке выше у нас много ячеек. Найти, какая ячейка является активной, очень просто, независимо от того, какая ячейка выбрана прямо сейчас. В VBA это называется «Активная ячейка».

Если ваша активная ячейка не видна в окне, посмотрите на поле имени. Поле имени. В Excel поле имени расположено в левой части окна и используется для присвоения имени таблице или ячейке. Имя обычно представляет собой символ строки, за которым следует номер столбца, например ячейка A1. читать дальше, он покажет вам адрес активной ячейки. На изображении выше адрес активной ячейки — B3.

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

Например, если мы хотим выбрать ячейку A1 и вставить значение «Привет», мы можем написать это двумя способами. Ниже показан способ выбора ячейки и вставки значения с помощью объекта VBA «RANGE»

Код:

Сначала будет выбрана ячейка A1 «Диапазон («A1»). Выбрать”

Затем в ячейку A1 Range("A1") будет вставлено значение "Привет". Значение = "Привет"

Теперь я удалю строку Range("A1"). Значение = «Привет» и используйте свойство Active Cell для вставки значения.

Код:

Аналогично, сначала будет выбрана ячейка A1 «Диапазон («A1»). Выбрать”

Но здесь я использовал ActiveCell.Value = "Hello" вместо Range("A1").Value = "Hello"

Причина, по которой я использовал свойство Active Cell, заключается в том, что в тот момент, когда я выбираю ячейку A1, она становится активной ячейкой. Таким образом, мы можем использовать свойство активной ячейки Excel VBA для вставки значения.

Чтобы лучше понять это, давайте покажем адрес активной ячейки в окне сообщения. Теперь взгляните на изображение ниже.

На приведенном выше изображении активной ячейкой является «B3», а значение равно 55. Давайте напишем код на VBA, чтобы получить адрес активной ячейки.

Код:

Запустите этот код с помощью клавиши F5 или вручную. Затем он покажет адрес активной ячейки в окне сообщения.

Вывод:

Аналогично приведенный ниже код покажет значение активной ячейки.

Код:

Вывод:

Приведенный ниже код покажет номер строки активной ячейки.

Код:

Вывод:

Приведенный ниже код покажет номер столбца активной ячейки.

Код:

Вывод:

Свойство активной ячейки также имеет параметры. После ввода свойства ActiveCell открывает скобки, чтобы увидеть параметры.

Используя этот параметр, мы также можем ссылаться на другую ячейку.

Например, ActiveCell (1,1) означает активную ячейку. Если вы хотите перейти на одну строку вниз, вы можете использовать ActiveCell (2,1). Здесь 2 не означает перемещение вниз на две строки, а только на одну строку вниз. Аналогично, если вы хотите переместиться на одну колонку вправо, то это код ActiveCell (2,2)

Например, посмотрите на изображение ниже.

На изображении выше активной ячейкой является A2. Чтобы вставить значение в активную ячейку, введите этот код.

Код:

Запустите этот код вручную или с помощью клавиши F5. В ячейку будет вставлено значение «Hiiii».

Теперь, если вы хотите вставить такое же значение в ячейку ниже, вы можете использовать этот код.

Код:

Это вставит значение в ячейку ниже активной ячейки.

Если вы хотите сразу же вставить значение в один столбец, вы можете использовать этот код.

Код:

Это вставит "Hiiii" в следующую ячейку столбца активной ячейки.

Таким образом, мы можем ссылаться на ячейки в VBA, используя Active Cell Property.

Надеюсь, вам понравилось. Спасибо, что уделили нам время.

Рекомендуемые статьи

Это руководство по VBA Active Cell. Здесь мы изучаем концепцию активной ячейки, чтобы найти адрес ячейки, а также изучаем параметры активной ячейки в Excel VBA вместе с практическими примерами и загружаемым шаблоном. Ниже вы можете найти несколько полезных статей по Excel VBA –

Здравствуйте. Есть ли какая-нибудь формула для поиска текста и возврата его адреса ячейки? Это похоже на то, когда мы используем функцию Ctrl + F, чтобы найти текст, а затем Excel выделяет ячейку, содержащую упомянутый текст.В то же время в строке имени отображается адрес ячейки. Итак, есть ли какая-нибудь формула, которая может выполнить такую ​​​​задачу? Заранее спасибо!

Факты об Excel

DeusXv

Известный участник
Формулы ячеек
ДиапазонФормула
C1=АДРЕС(ПОИСКПОЗ("Привет*",$A$1:$A$99,0),1)

адхинагара

Новый участник

Спасибо, DeusXv. Но что, если я еще не знаю, столбец ячейки содержит текст. Ваша формула использует столбец A (строка 1–99) в качестве массива поиска. В моем случае столбец может быть столбцом A или B, или может быть P, AA, CG и т. д.

DeusXv

Известный участник

Просто измените диапазоны в формуле/коде, чтобы они выглядели следующим образом:

И код будет искать этот фрагмент текста во всех ячейках в этом диапазоне.

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

Надеюсь, это поможет

DeusXv

Известный участник

Для ввода диапазона с помощью поля ввода используйте этот код

адхинагара

Новый участник

DeusXv

Известный участник

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

Надеюсь, это поможет

АбхинавК

Новый участник

У меня похожая проблема, однако один и тот же текст "ab" появляется в столбце несколько раз.. например:

a1 ab
a2 cd
a3 ef
a4 gh
a5 ij
a6 kl
a7 ab
a8 ab
a9 мин

когда я использую ADDRESS( MATCH( "ab",$A$1:$A$9,0 ),1 ), я продолжаю получать первый встреченный адрес, который равен a1, тогда как я хотел бы видеть a1, a7, a8 ..помогите

зоракс

Новый участник

@AbhinavC
Почему бы вам не использовать следующий пустой столбец для формулы, которая проверяет, было ли в предыдущем поле в той же строке введено «ab», и если вы получаете адрес..

АбхинавК

Новый участник

Похожие темы

Excel содержит более 450 функций, и с каждым годом их становится все больше. Это огромная цифра, так с чего же начать? Прямо здесь, с этим набором.

Вы можете использовать формулу поиска, чтобы вернуть адрес ячейки вместо значения в электронной таблице Excel. Обычно это делается для передачи адреса ячейки другой функции.

В этом примере мы будем искать клиента по его идентификатору и возвращать адрес ячейки, содержащей страну клиента.

Использование функции АДРЕС

Функция АДРЕС Excel будет использоваться для возврата адреса ячейки, когда значение будет найдено. Функция записывается следующим образом;

Row_num: номер строки ячейки.

Column_num: номер столбца ячейки.

Abs_num: Тип ссылки, которую вы хотите вернуть. По умолчанию функция ADDRESS возвращает абсолютную ссылку, такую ​​как $F$12. Другие варианты — вернуть абсолютную строку/относительный столбец, относительную строку/абсолютный столбец или относительную ссылку. Вы делаете свой выбор, вводя число от 1 до 4.

a1: это эталонный стиль. Введите 0 для стиля R1C1, такого как R12C6 (это ячейка F6), или введите 1 для стиля A1, который является классической F6. Стиль A1 используется по умолчанию.

Sheet_text: имя рабочего листа для использования в справке.

Формула для поиска адреса ячейки

Функция АДРЕС Excel объединяется с функцией ПОИСКПОЗ, чтобы найти адрес ячейки значения.

Функция ПОИСКПОЗ используется для поиска номера строки клиента. Номер столбца вводится как 6. Для определения номера столбца также можно было бы использовать другую функцию ПОИСКПОЗ.

Другие аргументы не использовались, что означает, что ответ будет возвращен с использованием значений по умолчанию для абсолютной ссылки на ячейку, в стиле A1 и без текста листа.

В приведенном ниже примере показано, как к функции АДРЕС добавляется число 4, чтобы вернуть адрес ячейки в качестве относительной ссылки на ячейку.

Посмотреть видео

Читайте также: