Что означает ссылка в excel

Ссылки на лист

Ссылка в Microsoft Excel — это тип данных, который ссылается на прямоугольный блок ячеек (который может быть только одной ячейкой) или, в некоторых случаях, на несколько несвязанных блоков ячеек. В Excel для ячеек на текущем листе используется один ссылочный тип, который называется внутренней ссылкой. Любая ячейка, которая не находится на текущем листе, описывается с помощью другого типа ссылки, известного как внешняя ссылка. Определение активного и текущего см. в следующем разделе.

Активный и текущий

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

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

Важные моменты, которые следует помнить:

Активная книга, лист или ячейка обычно не является текущей, хотя это может быть.
Функция надстройки, будь то в модуле Visual Basic для приложений (VBA), библиотеке DLL или XLL, всегда вызывается из текущей ячейки на текущем листе или из одной из них в случае многопоточного пересчета (MTR).

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

Ссылки на внутренний и внешний лист

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

Многие функции API C возвращают ссылки или принимают ссылочные аргументы. Любая функция API C, которая принимает ссылочные аргументы, принимает внутренние или внешние ссылки, за исключением функции xlSheetNm , для которой требуется внешняя ссылка. Некоторые функции возвращают только внутренние или внешние ссылки. Например, функция C API xlfCaller возвращает ссылку на вызывающие ячейки по определению на текущем листе. Возвращаемая ссылка всегда является внутренней ссылкой, хотя функция может возвращать типы, не относящиеся к ссылке, в которых функция не вызывается из ячейки листа. Функция API C xlSheetId всегда возвращает идентификатор листа, содержащегося во внешнем эталонном типе данных.

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

Функции ссылки и поиска (справка)

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

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

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

Возвращает ссылку на отдельную ячейку листа в виде текста.

Возвращает количество областей в ссылке.

Выбирает значение из списка значений.

Кнопка Office 365

Функция CHOOSECOLS

Возвращает указанные столбцы из массива

Кнопка Office 365

Функция CHOOSEROWS

Возвращает указанные строки из массива.

Возвращает номер столбца, на который указывает ссылка.

Возвращает количество столбцов в ссылке.

Кнопка Office 365

Функция DROP

Исключает указанное количество строк или столбцов из начала или конца массива

Кнопка Office 365

Функция EXPAND

Развертывание или заполнение массива до указанных измерений строк и столбцов

Фильтрует диапазон данных на основе условий, которые вы определяете

Excel 2013

Ф.ТЕКСТ

Возвращает формулу в заданной ссылке в виде текста.

Excel 2010

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Возвращает данные, хранящиеся в отчете сводной таблицы.

Выполняет поиск в первой строке массива и возвращает значение указанной ячейки.

Кнопка Office 365

Функция HSTACK

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

Создает ссылку, открывающую документ, который находится на сервере сети, в интрасети или в Интернете.

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

Возвращает ссылку, заданную текстовым значением.

Ищет значения в векторе или массиве.

Ищет значения в ссылке или массиве.

Возвращает смещение ссылки относительно заданной ссылки.

Возвращает номер строки, определяемой ссылкой.

Возвращает количество строк в ссылке.

Получает данные реального времени из программы, поддерживающей автоматизацию COM.

Сортирует содержимое диапазона или массива

Сортирует содержимое диапазона или массива на основе значений в соответствующем диапазоне или массиве

Кнопка Office 365

Функция TAKE

Возвращает указанное число смежных строк или столбцов из начала или конца массива.

Кнопка Office 365

Функция TOCOL

Возвращает массив в одном столбце

Кнопка Office 365

Функция TOROW

Возвращает массив в одной строке

Возвращает транспонированный массив.

Возвращает список уникальных значений в списке или диапазоне

Кнопка Office 365

Функция VSTACK

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

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

Кнопка Office 365

Функция WRAPCOLS

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

Кнопка Office 365

Функция WRAPROWS

Заключает предоставленную строку или столбец значений по строкам после указанного числа элементов

Выполняет поиск по диапазону или массиву и возвращает элемент, соответствующий первому обнаружению совпадения. Если совпадение отсутствует, функция ПРОСМОТРX может вернуть ближайшее (приблизительное) совпадение.

Возвращает относительную позицию элемента в массиве или диапазоне ячеек.

Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.

Типы ссылок на ячейки в формулах Excel

Если вы работаете в Excel не второй день, то, наверняка уже встречали или использовали в формулах и функциях Excel ссылки со знаком доллара, например $D$2 или F$3 и т.п. Давайте уже, наконец, разберемся что именно они означают, как работают и где могут пригодиться в ваших файлах.

Относительные ссылки

Это обычные ссылки в виде буква столбца-номер строки ( А1, С5, т.е. «морской бой»), встречающиеся в большинстве файлов Excel. Их особенность в том, что они смещаются при копировании формул. Т.е. C5, например, превращается в С6, С7 и т.д. при копировании вниз или в D5, E5 и т.д. при копировании вправо и т.д. В большинстве случаев это нормально и не создает проблем:

Смешанные ссылки

Иногда тот факт, что ссылка в формуле при копировании «сползает» относительно исходной ячейки — бывает нежелательным. Тогда для закрепления ссылки используется знак доллара ($), позволяющий зафиксировать то, перед чем он стоит. Таким образом, например, ссылка $C5 не будет изменяться по столбцам (т.е. С никогда не превратится в D, E или F), но может смещаться по строкам (т.е. может сдвинуться на $C6, $C7 и т.д.). Аналогично, C$5 — не будет смещаться по строкам, но может «гулять» по столбцам. Такие ссылки называют смешанными:

Абсолютные ссылки

Ну, а если к ссылке дописать оба доллара сразу ($C$5) — она превратится в абсолютную и не будет меняться никак при любом копировании, т.е. долларами фиксируются намертво и строка и столбец:

Самый простой и быстрый способ превратить относительную ссылку в абсолютную или смешанную — это выделить ее в формуле и несколько раз нажать на клавишу F4. Эта клавиша гоняет по кругу все четыре возможных варианта закрепления ссылки на ячейку: C5 → $C$5 → $C5 → C$5 и все сначала.

Все просто и понятно. Но есть одно «но». Предположим, мы хотим сделать абсолютную ссылку на ячейку С5. Такую, чтобы она ВСЕГДА ссылалась на С5 вне зависимости от любых дальнейших действий пользователя. Выясняется забавная вещь — даже если сделать ссылку абсолютной (т.е. $C$5), то она все равно меняется в некоторых ситуациях. Например: Если удалить третью и четвертую строки, то она изменится на $C$3. Если вставить столбец левее С, то она изменится на D. Если вырезать ячейку С5 и вставить в F7, то она изменится на F7 и так далее. А если мне нужна действительно жесткая ссылка, которая всегда будет ссылаться на С5 и ни на что другое ни при каких обстоятельствах или действиях пользователя?

Действительно абсолютные ссылки

Решение заключается в использовании функции ДВССЫЛ (INDIRECT) , которая формирует ссылку на ячейку из текстовой строки. Если ввести в ячейку формулу: =ДВССЫЛ(«C5») =INDIRECT(«C5») то она всегда будет указывать на ячейку с адресом C5 вне зависимости от любых дальнейших действий пользователя, вставки или удаления строк и т.д. Единственная небольшая сложность состоит в том, что если целевая ячейка пустая, то ДВССЫЛ выводит 0, что не всегда удобно. Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО: =ЕСЛИ(ЕПУСТО(ДВССЫЛ(«C5″));»»;ДВССЫЛ(«C5»)) =IF(ISBLANK(INDIRECT(«C5″));»»;INDIRECT(«C5»))

Ссылки по теме

Трехмерные ссылки на группу листов при консолидации данных из нескольких таблиц
Зачем нужен стиль ссылок R1C1 и как его отключить
Точное копирование формул макросом с помощью надстройки PLEX