Как найти ссылку на ячейку в эксель

Всем привет.

При обработке некоторой таблички в MS Excel возникла простенькая задача — надо было найти все формулы в которых участовала определенная ячейка. Ммм, попробуйте далее не читать и решить ее самостоятельно.

Ну а кому не втерпеж, слушаем далее. Итак для примера мне надо найти кто использует ячейку B4.

Первое решение — используем поиск. Указываем ячейку в поиске и нажимаем «Найти все». В результате имеем список всех ячеек где наша B4 участвует в расчетах. Для чего не подходит такой способ? Это не подходит в случае, если наша ячейка участвует  в диапазоне типа «=СУММ(B3:B7)» или ссылка абсолютная «=B$4».

Как найти ячейки, связанные с внешними источниками в Excel

В этой статье вы узнаете, как найти внешние ссылки в Excel.

Найти внешние ссылки

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

Как вы можете видеть выше, значение в B2 связано с рабочим листом с именем Внешний файл .xlsx (Лист1, ячейка B2). Ячейки B5, B7 и B8 также содержат похожие ссылки. Теперь посмотрите на этот файл и значение в ячейке B2.

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

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

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

Поиск внешних ссылок с помощью функции поиска и замены

1. В Лента, перейти к Главная> Найти и выбрать> Заменить.

2. Во всплывающем окне (1) введите «* .xl *» для Найти то, что, (2) нажмите Найти все, и (3) нажмите CTRL + A на клавиатуре, чтобы выделить все найденные ячейки.

Связанные файлы должны быть в формате Excel (.xlsx, .xlsm, .xls), поэтому вы хотите найти ячейки, содержащие «.xl» в формуле (ссылка). Звездочки (*) перед и после «.xl» обозначают любой символ, поэтому поиск найдет любое из расширений файлов Excel.

3. В результате выбираются все ячейки, содержащие внешнюю ссылку (B2, B5, B7 и B8). Чтобы заменить их определенным значением, введите это значение в поле Заменить коробка и удар Заменить все. Если оставить поле пустым, удаляется содержимое всех связанных ячеек. Например, если ввести 55 в поле Заменить на, получится Ценности столбец на картинке ниже. В любом случае замененные ячейки больше не связаны с другой книгой.

Найдите внешние ссылки с помощью ссылок редактирования

Другой вариант — использовать функцию редактирования ссылок в Excel.

1. В Лента, перейти к Данные> Изменить ссылки.

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

Вы поможете развитию сайта, поделившись страницей с друзьями

Функция АДРЕС возвращает адрес определенной ячейки (текстовое значение), на которую указывают номера столбца и строки. К примеру, в результате выполнения функции =АДРЕС(5;7) будет выведено значение $G$5.

Примечание: наличие символов «$» в адресе ячейки $G$5 свидетельствует о том, что ссылка на данную ячейку является абсолютной, то есть не меняется при копировании данных.

Функция АДРЕС в Excel: описание особенностей синтаксиса

Функция АДРЕС имеет следующую синтаксическую запись:

=АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[a1];[имя_листа])

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

Описание аргументов:

• Номер_строки – числовое значение, соответствующее номеру строки, в которой находится требуемая ячейка;
• Номер_столбца – числовое значение, которое соответствует номеру столбца, в котором расположена искомая ячейка;
• [тип_ссылки] – число из диапазона от 1 до 4, соответствующее одному из типов возвращаемой ссылки на ячейку:

1. абсолютная на всю ячейку, например — $A$4
2. абсолютная только на строку, например — A$4;
3. абсолютная только на столбец, например — $A4;
4. относительная на всю ячейку, например A4.

• [a1] – логическое значение, определяющее один из двух типов ссылок: A1 либо R1C1;
• [имя_листа] – текстовое значение, которое определяет имя листа в документе Excel. Используется для создания внешних ссылок.

Примечания:

1. Ссылки типа R1C1 используются для цифрового обозначения столбцов и строк. Для возвращения ссылок такого типа в качестве параметра a1 должно быть явно указано логическое значение ЛОЖЬ или соответствующее числовое значение 0.
2. Стиль ссылок в Excel может быть изменен путем установки/снятия флажка пункта меню «Стиль ссылок R1C1», который находится в «Файл – Параметры – Формулы – Работа с Формулами».
3. Если требуется ссылка на ячейку, которая находится в другом листе данного документа Excel, полезно использовать параметр [имя_листа], который принимает текстовое значение, соответствующее названию требуемого листа, например «Лист7».



Примеры использования функции АДРЕС в Excel

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

На листе «Курсы» создана таблица с актуальными курсами валют:

На отдельном листе «Цены» создана таблица с товарами, отображающая стоимость в долларах США (USD):

В ячейку D3 поместим ссылку на ячейку таблицы, находящейся на листе «Курсы», в которой содержится информация о курсе валюты USD. Для этого введем следующую формулу: =АДРЕС(3;2;1;1;»Курсы»).

Значение параметров:

• 3 – номер строки, в которой содержится искомая ячейка;
• 2 – номер столбца с искомой ячейкой;
• 1 – тип ссылки – абсолютная;
• 1 – выбор стиля ссылок с буквенно-цифровой записью;
• «Курсы» — название листа, на котором находится таблица с искомой ячейкой.

Для расчета стоимости в рублях используем формулу: =B3*ДВССЫЛ(D3).

Функция ДВССЫЛ необходима для получения числового значения, хранимого в ячейке, на которую указывает ссылка. В результате вычислений для остальных товаров получим следующую таблицу:

Как получить адрес ссылки на ячейку Excel?

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

Исходная таблица имеет следующий вид:

Для получения ссылки на ячейку с минимальной стоимостью товара используем формулу:

Функция АДРЕС принимает следующие параметры:

• число, соответствующее номеру строки с минимальным значением цены (функция МИН выполняет поиск минимального значения и возвращает его, функция ПОИСКПОЗ находит позицию ячейки, содержащей минимальное значение цены. К полученному значению добавлено 2, поскольку ПОИСКПОЗ осуществляет поиск относительно диапазона выбранных ячеек.
• 2 – номер столбца, в котором находится искомая ячейка.

Аналогичным способом получаем ссылку на ячейку с максимальной ценой товара. В результате получим:

Адрес по номерам строк и столбцов листа Excel в стиле R1C1

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

Исходная таблица имеет следующий вид:

Для получения ссылки на ячейку B6 используем следующую формулу: =АДРЕС(6;2;1;0).

Аргументы функции:

• 6 – номер строки искомой ячейки;
• 2 – номер столбца, в котором содержится ячейка;
• 1 – тип ссылки (абсолютная);
• 0 – указание на стиль R1C1.

В результате получим ссылку:

Практическое применение функции АДРЕС: Поиск значения в диапазоне таблицы Excel по столбцам и строкам.

Примечание: при использовании стиля R1C1 запись абсолютной ссылки не содержит знака «$». Чтобы отличать абсолютные и относительные ссылки используются квадратные скобки «[]». Например, если в данном примере в качестве параметра тип_ссылки указать число 4, ссылка на ячейку примет следующий вид:

Скачать примеры функции АДРЕС для получения ссылки на ячейку в Excel

Так выглядит абсолютный тип ссылок по строкам и столбцам при использовании стиля R1C1.