Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как найти значение и вернуть адрес ячейки в Excel?

Как вернуть адрес ячейки вместо значения в Excel (простая формула)

Как вернуть адрес ячейки вместо значения в Excel (простая формула)

При использовании формул поиска Excel (например, VLOOKUP, XLOOKUP или INDEX / MATCH) целью является поиск совпадающего значения и получение этого значения (или соответствующего значения в той же строке / столбце) в качестве результата.

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

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

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

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

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

Функция ADDRESS в Excel предназначена для этого.

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

Ниже приводится синтаксис функции ADDRESS:

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

Давайте на примере разберем, как это работает.

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

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

Читайте так же:
Как изменить значения в диапазоне ячеек в Excel?

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

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

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

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

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

Формула будет работать для фиксированного набора данных и простой формуле.

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

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

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

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

Синтаксис функции CHECK выглядит следующим образом

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

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

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

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

Использовать приведенную выше формулу очень просто.

Я использовал формулу INDEX в качестве второго аргумента, чтобы получить отдел сотрудника с идентификатором KR256.

Читайте так же:
Как извлечь текст между второй и третьей запятой из ячеек в Excel?

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

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

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

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

В нашем примере это именно так.

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

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

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

Относительная, абсолютная и смешанная адресация в Excel

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

Существует три типа ссылок на ячейки: относительная, абсолютная и смешанная.

Относительное обращение[.

Он используется по умолчанию. Это означает, что когда формула, содержащая адреса, копируется из одной ячейки в другую, параметры меняются. Адреса сохраняются путем прямого указания имени столбца и номера строки — например, «A1» или «R1C1» при выборе.

Читайте так же:
Как изменить сразу несколько путей гиперссылок в Excel?

При копировании по вертикали вверх или вниз параметр, отвечающий за номер строки, соответственно уменьшается или увеличивается на единицу.

Изменение формулы по горизонтали вправо или влево изменит параметр с номером столбца на один больше или меньше.

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

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

Относительная, абсолютная и смешанная адресация в Excel

Относительная, абсолютная и смешанная адресация в Excel

Абсолютный адрес

Абсолютная адресация используется, когда формула не должна меняться при обновлении. Ссылка на ячейку записывается с помощью символа «$» рядом с именем столбца или строки, в зависимости от задачи.

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

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

Относительная, абсолютная и смешанная адресация в Excel

В качестве примера, ячейки «$C$10» и «$C$11» имеют абсолютную ссылку.

Относительная, абсолютная и смешанная адресация в Excel

В абсолютных ссылках на столбец символ «$» записывается только перед описанием столбца, например, «$C10». Абсолютная ссылка на строку будет — только перед номером строки — «C$10».

Можно преобразовать адрес из относительного в абсолютный. Нажмите горячую клавишу F4 при вводе ссылки (например, когда курсор выделит нужное поле). Система автоматически добавит символы «$».

Смешанные адресации

Такая адресация не всегда рассматривается отдельно от других типов адресации. Если к абсолютной ссылке на столбец или строку прикреплен либо столбец, либо строка, то ситуация относится к смешанному типу.

Читайте так же:
Как найти значение с двумя или несколькими критериями в Excel?

Суперабсолютная адресная доставка

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

Массивы B2 — B5 и C2 — C5 содержат значения в двух столбцах. Необходимо ввести сумму чисел в первом и втором столбцах (а также во всех остальных строках диапазона) в столбце D.

Относительная, абсолютная и смешанная адресация в Excel

Если вы добавляете новый столбец между столбцами A и B, формула изменяется на $C2 + D2.

Относительная, абсолютная и смешанная адресация в Excel

Для задач, в которых можно добавлять или удалять строки и столбцы, но в формуле необходимо указать строго фиксированное поле, попробуйте использовать функцию DVSSYL(). DVSSYL(«B2») — пример параметра с именем ячейки.

Относительная, абсолютная и смешанная адресация в Excel

Если в обновляемом поле нет числового значения, то в формулу по умолчанию передается ноль.

Относительная, абсолютная и смешанная адресация в Excel

Чтобы избежать ошибок при выводе решения (например, деления на ноль), в IF() можно добавить проверочную функцию EPUSTO().

Существует также возможность использования функции MESSAGE(), которая задает расстояние между всеми строками и столбцами в диапазоне интервалов, а также основной ячейкой.

Как в Excel отобразить связанные ячейки

В Excel имеется очень полезная возможность отображения связанных ячеек. Так, можно для ячейки с формулой отобразить связи — ячейки, значения из которых используются в формуле. Кроме того, установив табличный курсор в какую-либо ячейку, можно просмотреть, в каких формулах она участвует. При этом вес связи отображаются в виде стрелок.

Чтобы отобразить ссылки на ячейки, участвующие в данной формуле, установите курсор на ячейку, в которой находится формула, и нажмите кнопку Влияющие ячейки на вкладке Формулы панели инструментов. В результате стрелки, выходящие из формулы и направляющиеся к ячейке, будут идти вместе (рис. 1.12).

Читайте так же:
Как запретить пользователям печатать лист?

Рис. 1.12. Влияющие ячейки

Рисунок 1.12: Приточные ячейки

Чтобы увидеть, на какие другие ячейки влияет значение в ячейке, установите табличный курсор в ячейку и нажмите кнопку Зависимые ячейки на вкладке Формулы панели инструментов. В результате стрелки, указывающие на зависимые ячейки, выйдут из ячейки, содержащей формулу (рисунок 1.13). Обратите внимание, что связанными отображаются только те ячейки, на которые непосредственно влияет выбранная ячейка. Эта связь не проявляется в случае косвенного влияния, когда первая клетка влияет на вторую, а вторая — на третью. В этом случае первая ячейка косвенно влияет на значение третьей ячейки, но взаимосвязь в данном случае не показана.

Рис. 1.13. Зависимые ячейки

Рисунок 1.13. Зависимые клетки

Такие отношения можно отображать сразу для нескольких ячеек, создавая таким образом своеобразную карту отношений ячеек таблицы. В этом случае можно одновременно отображать отношения разных типов (как влияющие, так и зависимые ячейки).

Чтобы удалить отображаемые связи, просто нажмите кнопку Удалить стрелки на вкладке Формулы панели инструментов. Таким образом, все ранее отображенные связи будут скрыты. Чтобы скрыть только определенные типы связей (иллюстрирующие влияющие или зависимые связи), нажмите стрелку рядом с кнопкой Удалить стрелки и выберите, какие стрелки вы хотите удалить (рис. 1.14).

Рис. 1.14. Сокрытие ненужных стрелок

Рисунок 1.14: Скрыть ненужные стрелки

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector