В чем разница между типами ссылок используемых в excel

Изменение типа ссылки: относительная, абсолютная, смешанная

По умолчанию ссылка на ячейку является относительной ссылкой, что означает, что ссылка относится к расположению ячейки. Если, например, вы ссылаетесь на ячейку A2 из ячейки C2, вы фактически ссылаетесь на ячейку, которая является двумя столбцами слева (C минус A) в одной строке (2). При копировании формулы, содержащей относительную ссылку на ячейку, эта ссылка в формуле изменится.

Например, если скопировать формулу =B4*C4 из ячейки D4 в D5, формула в D5 адаптируется вправо на один столбец и станет =B5*C5. Если вы хотите сохранить исходную ссылку на ячейку в этом примере при ее копировании, необходимо сделать ссылку на ячейку абсолютной перед столбцами (B и C) и строкой (2) со знаком доллара ($). Затем при копировании формулы =$B$4*$C$4 из D4 в D5 формула остается точно такой же.

Реже может потребоваться смешивать абсолютные и относительные ссылки на ячейки, перед столбцом или значением строки со знаком доллара, который исправляет столбец или строку (например, $B 4 или C$4).

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

1. Выделите ячейку с формулой.
2. В строке формул строка формул

Копируемая формула

Первоначальная ссылка

Новая ссылка

$A$1 (абсолютный столбец и абсолютная строка)

$A$1 (абсолютная ссылка)

A$1 (относительный столбец и абсолютная строка)

C$1 (смешанная ссылка)

$A1 (абсолютный столбец и относительная строка)

$A3 (смешанная ссылка)

A1 (относительный столбец и относительная строка)

C3 (относительная ссылка)

В чем разница между типами ссылок используемых в excel

������� ���������� ��������� MS Office 2007: Microsoft Excel

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

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

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

• Относительные ссылки. При копировании формул эти ссылки автоматически изменяются в соответствии с новым положением формулы.
• Абсолютные ссылки. Эти ссылки не изменяются при копировании формул.
• Смешанные ссылки. В этих ссылках номер строки (или столбца) является абсолютным, а столбца (строки) — относительным.

Отличительной особенностью абсолютных ссылок являются два знака доллара ($): один перед буквой столбца и второй перед номером строки (например, $А$5 ). Чтобы поставить два знака доллара ($) в адресе ячейки, следует поставить курсор в любом месте адреса ячейки в строке формул и нажать клавишу F4 на клавиатуре один раз.

В Excel также допускаются смешанные ссылки, в которых только одна часть адреса является абсолютной (например, $А4 или А$4 ). В этом случае клавишу F4 необходимо нажать два или три раза (соответственно А$4 или $А4 ). Четвертое нажатие F4 возвращает к относительной ссылке. Например, если необходимо поставить какую-либо ссылку на А1 , то первое нажатие клавиши F4 преобразует ссылку на ячейку в $А$ 1, второе — в А$1 , третье — в $А1 , а четвертое вернет ей первоначальный вид — А1 . Нажимайте клавишу F4 до тех пор, пока не появится нужный тип ссылки.

Различие между разными типами ссылок проявляется при копировании формул.

На рис.30 показана таблица, в ячейке D2 которой находится формула умножения количества наименований товара на его цену. Формула выглядит следующим образом: =В2*С2 . Если ее скопировать маркером заполнения на ячейки D3 и D4 , то получим изображенную на рисунке таблицу. Поскольку в этой формуле используются относительные ссылки, то при копировании формулы в ячейки D3 и D4 они соответствующим образом изменятся, то есть в ячейке D3 получим формулу: =ВЗ*СЗ , а в ячейке D4 соответственно =В4*С4 .

Если в ячейке D2 заменить относительные ссылки абсолютными, то получим =$В$2*$С$2 .

Если теперь скопировать эту формулу в ячейку D3 , то получим неправильный результат. Формулы в ячейках D3 и D2 будут одинаковыми.

Теперь изменим этот пример и подсчитаем комиссионные. Значение процентной ставки комиссионных хранится в ячейке в 7 (рис.31). Перенесем заголовок Всего на одну ячейку вправо, а в D1 впишем =А7 .

В результате в ячейке D1 получим Комиссионные . В ячейку D2 введем формулу =В2*С2*$В$7 . Количество умножается на цену, а затем результат умножается на процентную ставку комиссионных, значение которой хранится в ячейке В7 . Обратите внимание на то, что ссылка на ячейку В7 является абсолютной . Скопировав ячейку D2 в D3 , получим =В3*С3*$В$7. Ссылки на ячейки В2 и С2 изменились, а ссылка на ячейку В7 — нет, т.е. мы получили правильный результат.

На рис.32 показана таблица, в которой используются смешанные ссылки. В левом столбце хранится значение длины прямоугольника, а в верхней строке находится ширина. В остальных ячейках вычисляется площадь прямоугольника соответственно данной длине и ширине. Например, в ячейке D5 вычисляется площадь прямоугольника, длина которого — 2, а ширина — 1,5. Для вычисления площади в ячейку С3 вводится формула = $В3*С$2.

Обратите внимание на то, что в формуле используются смешанные ссылки. В ссылке на ячейку В3 абсолютной является ссылка на столбец ( $В ), а в ссылке на ячейку С2 используется абсолютная ссылка на строку ( $2 ). Скопировав эту формулу во все ячейки диапазона, мы получим правильный результат вычислений. Например, в ячейке F7 будет содержаться такая формула =$B7*F$2 .

При использовании в ячейке С3 абсолютных или относительных ссылок результат окажется неверным.

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

Ссылка в 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 всегда возвращает идентификатор листа, содержащегося во внешнем эталонном типе данных.

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

Microsoft Excel/Ссылки

Ссылка в Microsoft Excel — адрес ячейки или связного диапазона ячеек. В каждом листе Excel может быть 256 (2 в 8 степени) столбцов и 65536 (2 в 16 степени) строк (В Excel 2007 16384 (2 в 14 степени) столбцов и 1048576 (2 в 20 степени) строк). Адрес ячейки определяется пересечением столбца и строки, как в шахматах или морском бое, например: A1, C16. Адрес диапазона ячеек задается адресом верхней левой ячейки и нижней правой, например: A1:C5.

Виды представления ссылок [ править ]

Есть два вида представления ссылок в Microsoft Excel:

• Классический;
• Стиль ссылок R1C1 (здесь R — row (строка), C — column (столбец)).

Включить стиль ссылок R1C1 можно в настройках «Сервис» —> «Параметры» —> закладка «Общие» —> галочка «Стиль ссылок R1C1»

Если включен классический вид и в ячейке с адресом A1 находится формула «=B3» (B3 — относительная ссылка), то после переключения в вид R1C1 она примет вид «=R[2]C[1]». В режиме R1C1 в квадратных скобках указывается относительное смещение. В скобках после R указано смещение в строках (row) — две строки вниз (вниз, потому что число положительное, если бы стояло «R[-2]» — было бы вверх). В скобках после C указано смещение в столбцах — 1 столбец вправо (если бы было C[-1] — влево). В общем случае направления вниз и вправо считаются положительными, вверх и влево — отрицательными.

Если включен классический вид и в ячейке с адресом A1 находится формула «=$B$3» ($B$3 — абсолютная ссылка), то после переключения в вид R1C1 она примет вид «=R3C2». Квадратных скобок нет, то есть указано не относительно смещение по отношению к положению формула, а абсолютное смещение по отношению к всему листу (вспомните абсолютную и относительную системы координат).

Типы ссылок (типы адресации) [ править ]

Ссылки в Excel бывают 3-х типов:

• Относительные ссылки (пример: A1);
• Абсолютные ссылки (пример: $A$1);
• Смешанные ссылки (пример: $A1 или A$1, они наполовину относительные, наполовину абсолютные).

Знак $ здесь никакого отношения к денежным единицам не имеет, это лишь способ указать Excel тип ссылки. Различия между разными типами ссылок можно увидеть, если потянуть за маркер автозаполнения активной ячейки или диапазона ячеек, содержащих формулу со ссылками.

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

Если вы ставите в какой-то ячейке знак «=», затем щелкаете левой кнопкой мыши на какой-то ячейке, Excel подставляет после » запоминает», на каком расстоянии (в строках и столбцах) Вы щелкнули ОТНОСИТЕЛЬНО положения ячейки, где поставили » /w/index.php?title=%D0%9C%D0%B0%D1%80%D0%BA%D0%B5%D1%80_%D0%B0%D0%B2%D1%82%D0%BE%D0%B7%D0%B0%D0%BF%D0%BE%D0%BB%D0%BD%D0%B5%D0%BD%D0%B8%D1%8F&action=edit&redlink=1″ title=»Маркер автозаполнения (страница не существует)»>маркер автозаполнения, эта формула скопируется во все ячейки, через которые мы протянули. И в каждой ячейке эта ссылка будет указывать на ячейку, расположенную на 3 столбца влево и 2 строки вверх ОТНОСИТЕЛЬНО положения ссылки. Это можно проверить, дважды щелкнув на одной из скопированных формул, или выделив ее, и нажав F2. Для лучшего понимания вспомните, как ходит шахматный конь. Он ходит буквой «Г» и из центра доски бьёт 8 клеток. «Упростим» немного правило хода коня: представим, что он может ходить только одной буквой «Г» — 2 клетки вперед и одну вправо. На какую бы клетку доски мы не поставим коня, каждый раз он ОТНОСИТЕЛЬНО своего положения отсчитывает смещение в строках и столбцах — 2 строки вверх и один столбец влево. Точно таким же образом работают относительные ссылки, только правило их «хода» задает пользователь. Каждый раз, когда мы тянем за маркер автозаполнения формула, содержащую относительные ссылки, Excel пересчитывает адреса всех относительных ссылок в ней в соответствии с их «правилом хода» (у каждой относительной ссылки в формуле может быть свое «правило»).

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

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

Чтобы сделать относительную ссылку абсолютной, достаточно поставить знак «$» перед буквой столбца и адресом строки, например $A$1. Более быстрый способ — выделить относительную ссылку и нажать один раз клавишу «F4», при этом Excel сам проставит знак «$». Если второй раз нажать «F4», ссылка станет смешанной такого типа A$1, если третий раз — такого $A1, если в четвертый раз — ссылка опять станет относительной. И так по кругу.

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

Смешанные ссылки являются наполовину абсолютными и наполовину относительными. Знак доллара в них стоит или перед буквой столбца или перед номером строки. Это самый сложный для понимания тип ссылки. Например, в ячейке записана формула «=A$1». Ссылка A$1 относительная по столбцу A и абсолютная по строке 1. Если мы потянем за маркер автозаполнения эту формулу вниз или вверх, то ссылки во всех скопированных формулах будут указывать на ячейку A1, то есть будет вести себя как абсолютные. Однако, если потянем вправо или влево — ссылки будет вести себя как относительные, то есть Excel будет пересчитывать ее адрес. Таким образом, формулы, созданные автозаполнением, будут использовать один и тот же номер строки ($1), но изменится номер столбца (A, B, C. ).

Именованные ячейки [ править ]

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

Примечание! Именованные ячейки по умолчанию являются абсолютными ссылками.

Для создания именованной ячейки нужно выделить нужную ячейку или диапазон, затем щелкнуть в текстовое поле (см. рис. ниже) ввести свое имя и нажать Enter. Можно использовать русские имена. Имя не может содержать пробелов и начинаться с цифры. Именованная ячейка может ссылаться на несвязный диапазон ячеек (выделенный с «Ctrl»).

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

или нажать клавишу «F3», откроется следующее окно:

Пример использования: «=СУММ(tablica_1);»

Для того что бы убрать имя именованной ячейки (например: чтобы присвоить другой ячейке это имя) — Вставка/имя/присвоить/удалить. В Excel 2007 — Формулы/Диспетчер имен/Удалить.