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

Создание абсолютной ссылки

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

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

После того, как формула введена, просто ставим в ячейке, или в строке формул, перед координатами столбца и строки ячейки, на которую нужно сделать абсолютную ссылку, знак доллара. Можно также, сразу после ввода адреса нажать функциональную клавишу F7, и знаки доллара перед координатами строки и столбца отобразятся автоматически. Формула в самой верхней ячейке примет такой вид: «=D2/$D$7».

Копируем формулу вниз по столбцу. Как видим, на этот раз все получилось. В ячейках находятся корректные значения. Например, во второй строке таблицы формула выглядит, как «=D3/$D$7», то есть делитель поменялся, а делимое осталось неизменным.

Что такое относительные и абсолютные адреса в формулах Excel 2010?

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

Адреса ячеек и диапазонов в Excel могут быть относительными и абсолютными. Посмотрим вот на такую таблицу (рис. 3.2).

Рис. 3.2. Таблица с суммой

В ячейке Е2 — сумма диапазона ячеек В2:D2. Чтобы ввести эту формулу, я воспользовалась той самой кнопкой

Рис. 3.3. Вкладка Формулы

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

Как видно на рис. 3.2, в строке формул указана формула, а в самой ячейке — результат сложения ячеек во второй строке. Чтобы просуммировать остальные строки нашей таблицы, не обязательно каждый раз вводить формулу. Можно просто выполнить автозаполнение для столбца Е. Да, не удивляйтесь. Формулы тоже можно копировать (рис. 3.4).

Рис. 3.4. Автозаполнение столбца формулой

Посмотрите, при автозаполнении программа автоматически поменяла адреса ячеек, которые нужно суммировать. В столбце Е в каждой строке будет сумма ячеек из столбцов В, С и D.

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

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

Абсолютный адрес не будет изменяться при копировании или автозаполнении ячеек. Чтобы показать программе Excel, что адрес абсолютный, к нему добавляют значки $, например: $А$1. Это абсолютный адрес ячейки А1. Чтобы быстро сделать относительный адрес в формуле абсолютным, дважды щелкните кнопкой мышки на формуле, поставьте курсор на ссылку нужной ячейки и нажмите клавишу F4. Но можно знак $ добавлять в формулы вручную с клавиатуры. На рис. 3.5 показан пример таблицы с абсолютным адресом.

Рис. 3.5. Автозаполнение столбца формулой с абсолютным адресом

В этой таблице столбец С получается путем умножения значения ячеек из столбца В на значение ячейки В1

Еще раз обращу ваше внимание на то, что, если поменялся курс доллара, вы меняете одно число в таблице. Все остальное Excel пересчитает автоматически

Относительные и абсолютные адреса ячеек

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

Большинство ссылок в формулах записываются в относительной форме — например, С3 (столбец)(строка)

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

При копировании формулы с относительной ссылкой (столбец)(строка) на n строк ниже и на m столбцов правее ссылка изменяется на (столец+m)(строка+n)

В большинстве случаев это очень удобно, но иногда этого не требуется. Поясним это на следующем примере.

Необходимо вычислить стоимость каждой модели принтеров на складе. Т.к. курс $ периодически изменяется, ячейка B11 будет использоваться для хранения текущего значения. При изменении кура достаточно внести новое значение в ячейку С11 и стоимость будет автоматически пересчитана.

1. Вставим необходимые для расчета формулы
   В ячейку F14      =В14*E14
   В ячейку G14     =F14*B11
2. Выделим ячейки F14  и G14
3. При помощи автозаполнения скопируем в нижележащие строки

Обратите внимание на возникшие ошибки в столбце G

Перейдем в режим отображения формул при помощи меню Сервис — Параметры

При копировании формулы из 14 в 15 строку Excel изменил адрес ячейки с B11 на B12, что в нашем случае делать не следовало.

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

1. Дважды щелкните мышью по ячейки G14 (перейдите в режим редактирования)
2. Нажмите клавишу <F4>. Теперь в формуле участвует абсолютный адрес $B$11
3. Введите формулу, нажав <Enter>
4. Скопируйте в нижележащие ячейки
5. Отмените режим отображения формул (Сервис — Параметры)

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

Некоторые ссылки в формулах записываются в абсолютной форме — например, $С$3

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

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

Изменение типа ссылки

Для того, чтобы превратить относительную ссылку в абсолютную, достаточно в режиме редактирования формулы установить курсор непосредственно за ссылкой и нажать клавишу <F4>

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

При помощи символа абсолютной адресации Вы можете гибко варьировать способ адресации ячеек. Например $B11 обозначает , что при копировании формул будет изменяться только адресация строки ячейки, а при обозначении B$11 — только столбца. Такая адресация называется смешанной.

При вводе формулы в строке формул, можно быстро перебрать по кругу относительный , смешанный и абсолютный адреса. Просто укажите на какой — нибудь адрес и нажимайте <F4>, чтобы по кругу перебрать все четыре варианта.

Использование имен для абсолютной адресации

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

Например назначив ячейки B11 имени курс можно ввести следующую формулу

=F14*курс

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

Для того, чтобы назначить имя ячейки необходимо

1. Выделить ячейку
2. Выполнить команду меню Вставка — Имя — Присвоить
3. Введите имя в стоке имя ячейки, например курс
4. Нажмите кнопку OK
5. Введите формулу

Имя файла

Имя файла состоит из непосредственно имени файла и расширения. Для операционной системы MS-DOS и оболочки Win 3.11 имя не превышает 8 символов, расширение- 3 символов. Имя не может содержать точек, т.е. файл с именем 4.1.htm в MS-DOS не допустим. Win 95/98/NT допускает неограниченное (до 255) по длине имя файла и расширение, точки в именах файлов.

Соответственно, требуется оценка, где будет использоваться гипертекстовый справочник/пособие/учебник. Если потребуется перенос под Win 3.11, то лучше сразу привести имена в соответствие с форматом 8.3.

Регистр игнорируется во всех версиях Windows, но принимает важное значение при обращениях к системам Unix, где два имени в разных регистрах соответствуют двум разным файлам. Первый файл любого проекта имеет имя Index.Htm, это является международным стандартом

Первый файл любого проекта имеет имя Index.Htm, это является международным стандартом.

Как поставить смешанную ссылку в Excel

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

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

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

1. Для получения точных расчетов вводим =D1/$D$3 и нажимаем «Enter». Программа выдает точный ответ.
2. Чтобы переместить формулу в последующие ячейки вниз по столбцу и получить точные результаты, протяните маркер до нижней ячейки.
3. В результате программа выдаст правильные расчеты.

Прописываем формулу для получения смешанной ссылки с учетом всех правил

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

Excel умеет ссылаться не только в пределах одного листа, но и на другие листы и даже другие файлы Excel

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

Это, конечно, помогает не забывать, что в файле есть внешние ссылки, но лучше, чтобы их вообще не было. Во-вторых, при изменении или удалении внешнего источника мгновенно меняются и результаты вычислений, что нужно далеко не всегда. В третьих, если возникнут ошибки в расчетах, то на обнаружение их источника может потребоваться дополнительное время, т.к. данные находятся в другом файле. В общем, сразу после вычислений с использованием данных из других книг внешние ссылки рекомендуется удалить: Данные → Подключения → Изменить связи → Разорвать связь.

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

Недостатки видов адресации

Недостаток относительной адресации заключается в том, что схранённая на компьютере страничка не всегда имеет ссылку на сам сервер. Возможно, удивительным кажется тот факт, что пользователь, когда заходит на Интернет-сервер, видит на страничке адрес этого же сервера. С учётом возможного сохранения странички на локальном компьютере, удивление пропадает.

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

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

Какие бывают ссылки, виды ссылок.

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

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

Ссылки бывают открытыми для поисковых роботов и закрытые, тематические и обычные, анкорные и безанкорные. Закрытые ссылки (обычно с «nofollow») робот для засчитывания ссылки для ТИЦ и PR не берет (?). Визуально определить открытые и закрытые ссылки поможет с помощью их подсветки SEO инструмент проверки сайтов  — расширение для браузеров RDS bar. Нас интересуют открытые для роботов тематические анкорные ссылки. Тематические ссылки — ссылки с сайтов подобной тематики. Т.е. для страниц сайта, посвященных отдыху на море, тематическими будут сайты турагенств, форумы про отдых, блоги (частные сайты со статьями) посвященные отдыху и другая родственная тематика. Анкорные ссылки-ссылки, поверх адреса страницы которых накладывается нужный Вам текст. Например: ссылка на страницу сайта http://alfabetseo.net/. На неё накладываем текст «продвижение». В коде с подсветкой это выглядит так <ahref=»http://alfabetseo.net/»>продвижение</a>.

В ВВ коде выглядит так [url=http://alfabetseo.net]продвижение. Визуально на страничке сайта анкорная ссылка выглядит так продвижение. К ссылкам, в зависимости от их задач, обычно добавляются атрибуты. Некоторые самые распостраненные из них:

«noindex», — запрет индексации текста со сылкой в Яндексе, для Гугл не используется. Выглядеть в коде это будет примерно так: <!—noindex—> текст  текст <a  href=»http://alfabetseo.net/» title=»название ссылки»>Текст анкора ссылки</a> текст  текст <!—/noindex—>

«nofollow» — ссылка закрыта от передачи веса ТИЦ и PR; 

«blank» — по такой ссылке страница открывается в новой вкладке. Выглядит это примерно так:  <a href=»http://alfabetseo.net/» target=»_blank» rel=»nofollow»>Текст анкора ссылки</a>.

Как применять эти атрибуты при перелинковке рассказывается в статье оптимизация сайта.

Создание ссылок. Как создать ссылку.

Создать ссылку можно разными способами: в тектовом редакторе (блокнот или notepad) используя символы кода и загрузив их на сайт, вредакторе сайтакодом, скопироватьадрес страницы сайта в адресной строке браузера и втавить в визуальный редактор на сайте (не забывать про атрибуты ссылки).

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

Выделение ключевых слов в ссылке в Гугл.

В Яндексе

Ссылки можно писать вручную в коде, соблюдая соответствующие элементы кода (<a href=»ссылка»/a>). Основная часть ссылки — название домена сайта. Для внутренних ссылок в редакторе админ панели сайта доменное имя своего сайта обычно не записывается.

При создании материала или категории (страницы сайта) в CMS (системах управления контентом) сайта ссылки генерируются автоматически, можно создать параметры генерации. Для SEO в соответствующих плагинах ссылки можно задавать вручную с транслитерацией согласно ключевым словам страницы для их чтения роботами поисковиков. Например название страницы ССЫЛКИ  после генерации с транслитерацией будет выглядеть http://site/ssylki. Для подбора оптимального написания русского слова латинскими буквами для поисковиков можно в поисковом окне браузера набирать нужное слово латинскими буквами и браузер сразу покажет транслит для будущей ссылкит.

Создать ссылку можно в CMS сайта в графе Алиас или в специальных SEO плагинах SEF.

От количества входящих анкорных тематических ссылок и веса страницы – сайта донора  зависит показатель ТИЦ и PR и, соответственно, позиция сайта в поисковой выдаче.

Абсолютная ссылка в Excel и особенности ее оформления

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

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

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

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

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

Использование абсолютных и относительных ссылок в Excel

Заполните табличку, так как показано на рисунке:

Описание исходной таблицы. В ячейке A2 находиться актуальный курс евро по отношению к доллару на сегодня. В диапазоне ячеек B2:B4 находятся суммы в долларах. В диапазоне C2:C4 будут находится суммы в евро после конвертации валют. Завтра курс измениться и задача таблички автоматически пересчитать диапазон C2:C4 в зависимости от изменения значения в ячейке A2 (то есть курса евро).

Для решения данной задачи нам нужно ввести формулу в C2: =B2/A2 и скопировать ее во все ячейки диапазона C2:C4. Но здесь возникает проблема. Из предыдущего примера мы знаем, что при копировании относительные ссылки автоматически меняют адреса относительно своего положения. Поэтому возникнет ошибка:

Относительно первого аргумента нас это вполне устраивает. Ведь формула автоматически ссылается на новое значение в столбце ячеек таблицы (суммы в долларах). А вот второй показатель нам нужно зафиксировать на адресе A2. Соответственно нужно менять в формуле относительную ссылку на абсолютную.

Как сделать абсолютную ссылку в Excel? Очень просто нужно поставить символ $ (доллар) перед номером строки или колонки. Или перед тем и тем. Ниже рассмотрим все 3 варианта и определим их отличия.

Наша новая формула должна содержать сразу 2 типа ссылок: абсолютные и относительные.

1. В C2 введите уже другую формулу: =B2/A$2. Чтобы изменить ссылки в Excel сделайте двойной щелчок левой кнопкой мышки по ячейке или нажмите клавишу F2 на клавиатуре.
2. Скопируйте ее в остальные ячейки диапазона C3:C4.

Описание новой формулы. Символ доллара ($) в адресе ссылок фиксирует адрес в новых скопированных формулах.

Абсолютные, относительные и смешанные ссылки в Excel:

1. $A$2 – адрес абсолютной ссылки с фиксацией по колонкам и строкам, как по вертикали, так и по горизонтали.
2. $A2 – смешанная ссылка. При копировании фиксируется колонка, а строка изменяется.
3. A$2 – смешанная ссылка. При копировании фиксируется строка, а колонка изменяется.

Для сравнения: A2 – это адрес относительный, без фиксации. Во время копирования формул строка (2) и столбец (A) автоматически изменяются на новые адреса относительно расположения скопированной формулы, как по вертикали, так и по горизонтали.

Примечание. В данном примере формула может содержать не только смешанную ссылку, но и абсолютную: =B2/$A$2 результат будет одинаковый. Но в практике часто возникают случаи, когда без смешанных ссылок не обойтись.

Полезный совет. Чтобы не вводить символ доллара ($) вручную, после указания адреса периодически нажимайте клавишу F4 для выбора нужного типа: абсолютный или смешанный. Это быстро и удобно.

Виды ссылок в Excel и как их сделать в документе

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

Ссылки для электронной книги

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

Обратите внимание!

Без знака «=» ссылка, оформленная в Excel, считается недействительной и при редактировании она уже не будет неизменным значением. Поэтому не забывайте о знаке «=».

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

Обратите внимание на то, как должна выглядеть такая ссылка

3. Смешанные ссылки создаются, когда мы меняем одну из координат. Добавляем «$» перед координатой. При копировании координата с отсутствующим дополнительным значком будет изменяться. На примере это выглядит следующим образом.

Функция 20: ADDRESS (АДРЕС)

Функция ADDRESS (АДРЕС) возвращает ссылку на ячейку в виде текста, основываясь на номере строки и столбца. Она может возвращать абсолютный или относительный адрес в стиле ссылок A1 или R1C1. К тому же в результат может быть включено имя листа.

Как можно использовать функцию ADDRESS (АДРЕС)?

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

• Получить адрес ячейки, зная номер строки и столбца.
• Найти значение ячейки, зная номер строки и столбца.
• Возвратить адрес ячейки с самым большим значением.

Синтаксис ADDRESS (АДРЕС)

Функция ADDRESS (АДРЕС) имеет вот такой синтаксис:

• abs_num (тип_ссылки) – если равно 1 или вообще не указано, то функция возвратит абсолютный адрес ($A$1). Чтобы получить относительный адрес (A1), используйте значение 4. Остальные варианты: 2=A$1, 3=$A1.
• a1 – если TRUE (ИСТИНА) или вообще не указано, функция возвращает ссылку в стиле A1, если FALSE (ЛОЖЬ), то в стиле R1C1.
• sheet_text (имя_листа) – имя листа может быть указано, если Вы желаете видеть его в возвращаемом функцией результате.

Ловушки ADDRESS (АДРЕС)

Функция ADDRESS (АДРЕС) возвращает лишь адрес ячейки в виде текстовой строки. Если Вам нужно значение ячейки, используйте её в качестве аргумента функции INDIRECT (ДВССЫЛ) или примените одну из альтернативных формул, показанных в .

Ошибка в относительной ссылке

Но, далеко не во всех случаях нам нужны именно относительные ссылки. Например, нам нужно в той же таблице рассчитать удельный вес стоимости каждого наименования товара от общей суммы. Это делается путем деления стоимости на общую сумму. Например, чтобы рассчитать удельный вес картофеля, мы его стоимость (D2) делим на общую сумму (D7). Получаем следующую формулу: «=D2/D7».

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

D8 – это совершенно пустая ячейка, поэтому формула и выдаёт ошибку. Соответственно, формула в строке ниже будет ссылаться на ячейку D9, и т.д. Нам же нужно, чтобы при копировании постоянно сохранялась ссылка на ячейку D7, где расположен итог общей суммы, а такое свойство имеют как раз абсолютные ссылки.

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

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

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

Так же стоит отметить закономерность изменения ссылок в формулах. Данные в B3 ссылаются на A3, B4 на A4 и т.д. Все зависит од того куда будет ссылаться первая введенная формула, а ее копии будут изменять ссылки относительно своего положения в диапазоне ячеек на листе.

Excel: Ссылки относительные и абсолютные

• Часто при использовании формул в Excel после ввода формулы в одну ячейку необходимо скопировать или распространить ее на блок ячеек.
• При копировании формул возникает необходимость управлять изменением адресов ячеек или ссылок.
• Ссылка в Excel — адрес ячейки или связного диапазона ячеек.
• Адрес ячейки определяется пересечением столбца и строки, например: A1, C16.
• Адрес диапазона ячеек задается адресом верхней левой ячейки и нижней правой, например: A1:C5.
• Ссылки в Excel бывают 3-х типов:

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

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

«Относительность» ссылки означает, что из данной ячейки ссылаются на ячейку, отстоящую на столько-то строк и столбцов относительно данной.

Пример.

В ячейке А6 формула ссылается на две ячейки (С3 и С4), отстоящие от данной на два столбца вправо и на три (С3) и две (С4) ячейки выше.

При копировании или «протаскивании» c помощью Маркера заполнения формулы, например, в ячейку А7  формула  изменяется  (Excel пересчитывает адреса всех относительных ссылок в ней в соответствии с новым положением ячейки).

Теперь формула в ячейке А7  ссылается на ячейки С4 и С5. Названия ссылок изменились, но осталось неизменным их положение относительно ячейки, в которой находится формула (два столбца вправо и на три (С4) и две (С5) ячейки выше).

Относительные ссылки целесообразно использовать в формулах в двух случаях:

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

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

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

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

1. Необходимости применения в формулах констант.
2. Необходимости фиксации диапазона для проведения расчетов.

Пример.

В диапазоне  А1:А5 указаны зарплаты сотрудников отдела, а в С1 – процент премии, установленный для всего отдела. Подсчитаем  премию каждого сотрудника и поместим в диапазоне В1:В5.

Для расчета премии первого сотрудника  введем в ячейку В1 формулу =А1*С1.

Если мы с помощью Маркера заполнения протянем формулу вниз, то получим  в ячейке В2 формулу =А2*С2, в ячейке В3 —  =А3*С3 и т.д.  Так как в ячейках диапазона С2:С5 нет значений, то в  диапазоне В2 : В5 получаем нули. Для исправления ошибки, необходимо зафиксировать в формуле ссылку на ячейку С1, т.е. заменить относительную ссылку С1 на абсолютную $C$1.

Для этого:

• выделите ячейку  В1
• в  Строке формул поставьте  знак «$» перед буквой столбца и адресом строки  $С$1. Более быстрый способ — в  Строке формул поставьте курсор на ссылку  С1 (можно перед С, перед или после 1) и  нажмите один раз клавишу «F4». Ссылка С1 выделится и превратится в $C$1.
• нажмите ENTER

Формула приняла вид « =А1*$С$1». Маркером  заполнения протяните полученную формулу вниз.

Теперь  диапазон  В2: В5 заполнен значениями премий сотрудников.

Быстрый способ сделать относительную ссылку абсолютной — выделить относительную ссылку и нажать один раз клавишу «F4», при этом Excel сам проставит знаки «$».

Семинары. Вебинары. Конференции

Актуальные темы. Лучшие лекторы Москвы и РФ. Сертификаты ИПБР. Более 30 тематик в месяц.

ПОМОГИТЕ ПОЖАЛУЙСТА С ТЕСТАМИ ПО ИНФОРМАТИКЕ НА ЗНАНИЕ ЭКСЕЛЬ. ОЧЕНЬ СРОЧНО ДАЮ 35 БАЛЛОВ. УМОЛЯЮ ПОМОГИТЕ

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

2)Какой символ необходимо использовать, чтобы закрепить индекс адреса ячейки? а)% б)& в)$ г) ‘ д)@

3)С какого символа должна начинаться любая формула в Excel? а)-> б)= в): г):=

4)Формулы для расчетов вводятся а)Только «вручную» — с клавиатуры б)Только через меню Вставка->Функция в)Вручную (с клавиатуры) или через меню Вставка->Функция

5)Что необходимо сделать, чтобы сообщить приложению о необходимости обработать функцию по правилам массива? а)Передать ее в качестве аргумента специальной функции б)Поместить ее в фигурные скобки в)Поместить все содержимое ячейки в фигурные скобки г)Одновременно нажать клавиши Ctrl + Shift + Enter д)Одновременно нажать клавиши Alt + Shift + Enter

6)Имена каких строк и столбцов при копировании формулы =$В23+C$24 не будут меняться: а)В б)С в)23 г)24

7)В ячейке C4 формула =B4/B2. Как она будет выглядеть, если переместить ее в ячейку C5? а)B4/B2 б)С4/С2 в)B5/B3 г)C4/B2

8)Каково число диапазонов, суммируемых в формуле: =СУММ(F2;F6:F15;$A$6:C13;H1:H5;J1;L1;N1) а)10 б)7 в)6 г)20

9)Содержимое ячейки: #########. В чем причина? а)Содержимое ячейки не соответствует ее формату б)Данные не помещаются в ячейку в)Расчет формулы в ячейке дал деление на ноль г)Данные в ячейке зашифрованы д)Формула содержит ошибку

10)В формулу нужно ввести ссылку на ячейку. Что для этого нужно сделать? а)Щелкнуть по нужной ячейке левой кнопкой мыши б)Нажать клавишу F4 в)В строке формул вызвать контекстное меню г)Написать адрес ячейки вручную

11)В ячейку введено число 0,70 и применен процентный формат. Каков будет результат, отображенный в ячейке? а)0,7% б)70% в)700% г)7000% д)7%

12)Какой вид примет курсор мыши при автозаполнении? а)черный плюсик б)курсор ввода в)белый плюсик с черной обводкой г)звездочка

13)В ячейку Е5 ввели формулу =$A$5*B5. Затем эту формулу скопировали вниз. Какая формула содержится в ячейке Е8? а)=$A$5*B8 б)=$A$8*B8 в)=$A$5*B5 г)=$A$8*B5

14)Какой символ можно использовать в формулах в качестве знака возведения в степень? а)

15)Что означает знак $ в формуле =A2*$B$6? а)Расчеты выполняются в долларах б)На ячейку В6 сделана абсолютная ссылка в)На ячейку В6 сделана относительная ссылка г)Числу в ячейке присвоен денежный стиль

16)Для выделения мышкой нескольких областей следует держать нажатой клавишу а)Alt б)Ctrl в)Shift г)Esc

17)Укажите, что определяет функция СРЗНАЧ в программе Excel? а)сpеднее аpифметическое заданного диапазона ячеек б)среднее значение от деления ячеек в)упорядочивание по убыванию чисел г)среднее геометрическое заданного диапазона ячеек

18)Если в Microsoft Excel к ячейке, в которой указана формула, применить комбинацию клавиш Ctrl+C и вставить в другую ячейку с помощью комбинации клавиш Ctrl+V, то в итоговую ячейку вставится. а)Значение для данной формулы б)0 в)В ячейке будет указана ошибка г)Данная формула

Относительная адресация

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

Основные символы, используемые в относительной адресации:

.	текущий каталог
..	каталог на уровень выше (родительский каталог)
.	каталог на 3 уровня выше, и т.д. В том случае, если система не поддерживает более двух точек, следует использовать строку вида ../../../ и т.д.
корневой каталог устройства, некорректно обрабатывается Internet Explorer 5.0 в локальном режиме, происходит автоматическое переключение протокола на http.

Относительная адресация (относительные ссылки)

Теперь примеры.

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

Альтернативное решение

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

Относительная адресация при создании формул для Условного форматирования.

Как быстро установить символ доллара $

Если ставить значок доллара с клавиатуры классическим методом, то потребуется вначале перейти в английскую раскладку, а затем нажать Shift+4. Скажем прямо, это долго и неудобно. Гораздо быстрее войти в режим редактирования формул с помощью клавиши F2 (если вы набираете формулу вручную, то уже находитесь в этом режиме), установить курсор на нужной ссылке и нажать F4. Ссылка начнет менять свой режим «абсолютности». После нажатия F4 относительная ссылка станет абсолютной (и по строкам, и по столбцам). Если нажать F4еще раз, то абсолютной станет только строка, следующее нажатие сделает абсолютным только столбец. Очередное нажатие F4сделает ссылку снова относительной. И так по кругу. Снова абсолютная, только строка, только столбец, относительная и т.д. Последовательным нажатием F4останавливаетесь на нужном варианте.

Очень удобно, не нужно переключать раскладку и искать $. Эта же кнопка работает и в диалоговом окне «Вставки функции». Например, при написании формулы ВПР или СУММЕСЛИ нужно внимательно смотреть на ссылающиеся диапазоны, поэтому значки $ используются очень часто.Как я уже говорил выше, если диапазон, на который нужно сослаться находится в той же книге (файле Excel), пусть даже и на другом листе, то Excel по умолчанию устанавливает относительные ссылки (без значка «$»). А вот ссылки на другие книги Excel по умолчанию делает абсолютными (вставляет «$» где только можно). Но про другие листы и книги поговорим в следующем параграфе.

Пример относительной ссылки

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

Делается это простым умножением количества (столбец B) на цену (столбец C). Например, для первого наименования товара формула будет выглядеть так «=B2*C2». Вписываем её в соответствующую ячейку таблицы.

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

Но, как видим, формула в нижней ячейке уже выглядит не «=B2*C2», а «=B3*C3». Соответственно, изменились и те формулы, которые расположены ниже. Вот таким свойством изменения при копировании и обладают относительные ссылки.