Auditportal.ru

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

Как изменить относительную ссылку на абсолютную ссылку в Excel?

Разница между абсолютной и относительной ссылкой

Абсолютные и относительные ссылки могут использоваться в самых разных компьютерных инструментах. Чаще всего они используются при создании веб-страниц, а также в электронных таблицах. Как выглядят эти ссылки? В чем разница между абсолютными и относительными ссылками?

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

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

Обратите внимание, что для определения «абсолютности» ссылки необходимо поставить знак $ перед каждой координатой в формуле. Поэтому запись в ячейке будет иметь вид = $A$1+$A$2.

Относительная ссылка, с другой стороны, представляет собой набор координат ячеек в формуле, которые изменяются, когда ячейка, в которой хранится формула, копируется в другое место таблицы. Это означает, что если ячейка A1 содержит формулу = B1+B2, формула автоматически изменится на = B2+B3, если эта ячейка будет скопирована в ячейку справа, A2, с использованием относительной ссылки.

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

Ссылки в структуре веб-страницы

В веб-дизайне также используются абсолютные и относительные ссылки. Обе они могут быть помещены в структуру веб-страниц. Как?

Относительная ссылка, размещенная в структуре веб-страницы, ссылается на неполный адрес файла, который выглядит как /page1234.html. Предполагается, что браузер, обнаружив относительную ссылку, автоматически добавляет недостающую часть адреса файла, прежде чем начать его загрузку.

Важно, чтобы переход по относительной ссылке осуществлялся с веб-страницы, расположенной на том же сайте, где размещен файл. То есть если на портале http://www.page1.com размещается страница с относительной ссылкой вида /page1234.html, то браузер, после того как пользователь кликнет по данной ссылке, начнет загружать файл по адресу http://www.page1.com/page1234.html.

Если, в свою очередь, разместить ее, к примеру, на портале http://www.page2.com, то браузер начнет загружать страницу http://www.page2.com/page1234.html. Поэтому веб-мастеру нужно быть внимательным с размещением относительных ссылок — главное, чтобы им соответствовал реальный файл на сервере.

Под абсолютной ссылкой в веб-странице понимается, в свою очередь, полный адрес файла, выглядящий как http://www.page.com/page1.html. Браузер будет обеспечивать переход к файлу только по этому адресу и никакому другому. Абсолютную ссылку можно встроить в веб-страницу, расположенную на любом сайте, — переход по ней на указанный файл всегда будет корректным.

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

Сравнение

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

Настоящая ссылка определяется в другой области ИТ — веб-разработке — как полный URL-адрес файла, расположенного на сервере. Хотя относительная ссылка относится только к части адреса хоста, этого достаточно, чтобы браузер автоматически заполнил полный путь к файлу на основе текущего адреса страницы, на которой расположена относительная ссылка.

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

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 существуют три типа ссылок:

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

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

Читайте так же:
Как использовать проверку данных, чтобы разрешить ввод только номера SSN в 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. ).

Читайте так же:
Как извлечь первые или первые два слова из текстовых строк в листе Google?
Именованные ячейки [ править ]

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

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

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

Imenovannaya ssilka.jpg

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

Vstavka imenovannoy ssilki 1.png

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

Vstavka imenovannoy ssilki 2.png

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

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

5. Расчеты в Excel

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

Примечание. Чтобы увидеть формулы в ячейках таблицы, нужно в диалоговом окне СервисПараметры на вкладке Вид в области Параметры окнаустановить флажок Формулы. Для возвращения к обычному виду ячеек необходимо сбросить этот флажок.

Общее правило использования формул в Excel заключается в том, что если вычисляемое значение зависит от других ячеек таблицы, всегда используйте формулу со ссылками на эти ячейки. Ссылка задается путем указания адреса ячейки. На рисунке 5.1 показан пример вычисления в ячейке C2 по формуле: = A2*B2

Вы можете задать ссылку на ячейку двумя способами:

  1. ввести адрес ячейки с клавиатуры;
  2. по ходу ввода формулы щелкать на нужной ячейке.

Второй метод быстрее и удобнее.

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

  1. активизировать ячейку С2;
  2. ввести с клавиатуры знак "=";
  3. щелкнуть в ячейке А2;
  4. ввести с клавиатуры знак " *";
  5. щелкнуть в ячейке В2;
  6. нажать <Enter>.

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

Копирование формул

Копирование формулы в соседние ячейки осуществляется путем автозаполнения, т.е. перетаскивания маркера заполнения из ячейки с формулой в соседние ячейки (столбец или строку). Это самый удобный и быстрый способ копирования.

  1. выделить диапазон для заполнения (включая ячейку с введенной формулой) и выполнить команду меню Правка />Заполнить />Вниз (если копирование выполняется по столбцу).
  2. протянуть маркер заполнения ячейки с формулой правой кнопкой мыши, в появившемся контекстном меню выбрать нужную команду
    • Копировать ячейки;
    • Заполнять только значения.

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

Формула копирования

Формула копирования

Форма после снятия копии

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

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

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

На рисунке 5.3 показан пример расчета налога с использованием этой формулы:

Налог = Стоимость * НДС

Ссылка на ячейку A2 должна оставаться неизменной при копировании формулы, то есть должна быть абсолютной — $A$2.

5%

Щелкните по ячейке (в данном примере A2) и нажмите клавишу , чтобы установить ссылку как абсолютную. При вводе формулы перед номером столбца и строки будет автоматически добавлен символ «$».

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

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

A$2 является фиксированной строкой.

A2 — это фиксированная, необратимая колонка;

Тип адресации ( относительная, абсолютная, смешанные) меняется повторными нажатиями клавиши <F4>при вводе адреса ячейки в формулу или при редактировании формулы.

Имена ячеек для абсолютной адресации

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

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

Именование текущей ячейки (строки):

Первый метод

  1. Щелкните в поле адреса строки формул, введите имя;
  2. Нажмите клавишу .
  1. выполнить команду Вставка />Имя />Присвоить ;
  2. в диалоговом окне ввести имя.

Это же диалоговое окно можно использовать для удаления имени, но обратите внимание, что если имя уже использовалось в формулах, его удаление приведет к ошибке (сообщение — «Name # ? «).

Просмотр зависимостей

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

Команда меню СервисЗависимости формул позволяет увидеть на экране связь между ячейками.

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

Если нужно увидеть, в какой формуле имеется ссылка на текущую ячейку, то следует выполнить команду Сервис />Зависимости формул />Зависимые ячейки.

Все зависимости в таблице изображаются стрелками. Для удаления стрелок служит команда Сервис />Зависимости формул />Убрать все стрелки.

При необходимости просмотра многих зависимостей удобно отобразить панель инструментов Зависимости командой Сервис />Зависимости формул />Панель зависимостей.

Редактирование формул

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

Изменение ссылки в формуле:

  • Выберите адрес ячейки в строке формул двойным щелчком мыши;
  • Щелкните в таблице в ячейке, на которую нужно сослаться.

Изменение типа адресации:

  • Выберите адрес ячейки двойным щелчком мыши;
  • Нажмите клавишу .

Когда вы закончите вносить изменения, используйте клавишу или кнопку Enter; если вы хотите отменить изменения, используйте клавишу или кнопку Cancel (Отмена).

Ссылки на другие рабочие листы

Копирование ячеек с формулами

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

  1. вставить на другой лист только числовое значение ячейки;
  2. скопировать содержимое ячейки с учетом ссылок.

Копирование цифровых значений:

  1. Поместить содержимое ячейки (диапазона) в буфер обмена;
  2. Перейти на другой лист и выбрать команду Paste Special из меню Insert (или из контекстного меню ячейки вставки);
  3. Установить переключатель значений.

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

Копирование с помощью зависимости

  1. Перейдите на целевой лист, активируйте ячейку вставки и введите знак «=»;
  2. Нажмите на ярлык исходного листа и выделите ячейку для копирования: ее адрес с именем листа появится в строке формул (например, =¡List1!F8;
  3. Нажмите клавишу .

Скопированное значение на листе назначения изменяется при редактировании влияющих ячеек на исходном листе.

Формулы со ссылками на другие листы

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

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

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