Как можно изменить тип ссылки ответ обоснуйте

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

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

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

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

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

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

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

Вот что нам нужно сделать:

1. Переходим в самую верхнюю ячейку результирующего столбца (не считая шапки таблицы), ставим знак “равно” (“=”) и пишем в ней формулу: = B2*C2 .
2. Когда выражение готово, нажимаем клавишу Enter на клавиатуре, после чего получаем результат в ячейке с формулой.

Возможные ошибки при работе с относительными ссылками

Безусловно, благодаря относительным ссылкам существенно упрощаются многие расчеты в Эксель. Однако, они не всегда помогают решить поставленную задачу.

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

1. Встаем в первую ячейку столбца для расчетов, где пишем формулу: =D2/D13 .
2. Нажимаем Enter, чтобы получить результат. После того, как мы скопируем формулу на оставшиеся ячейки столбца, вместо результатов увидим следующую ошибку: #ДЕЛ/0! .

Дело в том, что из-за того, что все ссылки на ячейки в формуле, которую мы скопировали, относительные, координаты в последующих ячейках сдвинулись. Т.е. для ячейки E3 формула выглядит следующим образом: =D3/D14 . Но, как мы видим, ячейка D14 – пустая, из-за чего программа и выдает ошибку, информирующую о том, что делить на цифру 0 нельзя.

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

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

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

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

1. Для начала пишем формулу в привычном виде в требуемой ячейке. В нашем случае она выглядит так: = D2/D13 .
2. Когда формула готова, не спешим нажимать клавишу Enter. Теперь нам нужно зафиксировать координаты ячейки D13. Для этого перед названием столбца и порядковым номером строки печатаем символ “$”. Или же можно просто после ввода адреса сразу нажать клавишу F4 на клавиатуре (курсор может находиться до, после или внутри координат). В итоге формула должна выглядеть следующим образом: D2/$D$13 .
3. Теперь можно нажать Enter, чтобы вывести результат в ячейку.
4. Остается только скопировать формулу с помощью маркера заполнения на нижние строки. На этот раз, благодаря тому, что мы зафиксировали ячейку с итоговой суммой, результат появится и в других ячейках.

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

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

• Если мы напишем ссылку как “$G5”, это означает, что будет меняться строка, а столбец будет зафиксирован.
• Если мы укажем “G$5”, в этом случае, фиксироваться будет номер строки, в то время, как столбец будет меняться.

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

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

Заключение

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

Как можно изменить тип ссылки

Если имеется ввиду Excel, то там возможно 3 вида ссылок: относительные (записываются в виде A1, A5:C2), абсолютные (в виде $A$5, $A$4:$D$7) и смешанные ($A6, D$5:$C2).

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

Какие бывают ссылки в Excel

Здравствуйте, дорогие читатели. Я решил выделить эту тему в отдельный пост, потому что многие не знают какие бывают ссылки в Эксель и не представляют, чем они отличаются друг от друга. Ежедневно я получаю вопросы от читателей, которые неправильно применяют ссылки на ячейки. Из-за этого, использование формул может давать ошибочные результаты. Уделите этому тексту 5 минут, чтобы потом не было неожиданных результатов вычислений.

Ссылки в Эксель

Давайте разберемся какие типы ссылок бывают в программе. Сразу уточню, для записи в одной ячейке, тип используемой ссылки не имеет значения. Но если вы будете копировать формулу в другое место, использование разных ссылок даёт разные результаты. Так вот, ссылки на ячейки бывают трёх видов:

1. Абсолютные ссылки. Они однозначно указывают адрес ячейки, на которую ссылаются. При копировании, этот адрес не изменяется. Так обычно выглядит абсолютная ссылка в Excel: =$A$1 . В этой ссылке перед именами столбцов и строк стоит знак «$», который указывает программе не изменять эти координаты при копировании.
2. Относительные ссылки. Такие ссылки запоминают свое положение относительно источника и при копировании так же, изменяют свои координаты. Например, в ячейке А1 записано =В2 . Значит, мы ссылаемся на ячейку, которая на одну строку ниже нашей и на один столбец правее. Теперь, если скопировать эту формулу в клетку С3 , ссылка в ней будет такая: =D4 , т.е. сохранит своё относительное положение к источнику.

Относительные ссылки в Excel удобно использовать, когда нужно применить одну и ту же формулу к списку данных. Тогда при копировании формула берет исходные данные из нужного пункта списка автоматически.

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

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

Как изменить тип ссылки в Эксель

Изменение типа ссылки – очень простая задача, которую можно выполнить двумя путями. Как вы уже поняли, чтобы координаты не меняли своего значения при копировании, нужно устанавливать перед ними знак «$». И делается это так:

1. Вручную – дважды кликните на ячейке со ссылкой для редактирования содержимого. Проставьте «$» перед теми координатами, которые нужно «заморозить» и нажмите Enter .
2. Автоматическим перебором — установите курсор на ссылке и нажимайте F4 , пока не получите нужный вид ссылки. Каждое нажатие клавиши устанавливает в данной ссылке новый тип ссылки. Нажатие клавиши циклически изменяет варианты ссылок по кругу: Относительная — Абсолютная — Изменяются столбцы — Изменяются строки — Относительная… Я пользуюсь этим способом, и он ни разу не подводил.

Внешние ссылки в Эксель

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

1. Ссылка на ячейку на том же листе по умолчанию ставится относительной и выглядит, как набор координат. Например: =А1
2. Ссылка на ячейку на другом листе активной книги, по умолчанию тоже относительная, но содержит имя листа, на котором расположена искомая ячейка. Имя листа и адрес ячейки разделяются восклицательным знаком. Например, =Лист1!А1 .
3. Ссылка на ячейку в другом файле по умолчанию абсолютная и записывается, как комбинация: [Имя_рабочей_книги]Имя_листа!Адрес_ячейки . Например: =[Книга1.xlsx]Лист1!$А$1 . И здесь нужно сделать несколько уточнений:
   1. Если целевая рабочая книга закрыта, ссылка изменяет вид: Адрес_рабочей_книги[Имя_рабочей_книги]Имя_листа!Адрес_ячейки .
   2. Если имя листа или книги содержит пробелы, ссылка заключается в одинарные кавычки по такой схеме: ’[Книга 1.xlsx]Лист 1’!$А$1 .

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

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

   Спасибо за прочтение, до встречи на страницах блога OfficeЛЕГКО!

   Добавить комментарий Отменить ответ

   2 комментариев

   Пожалуй, статья не является полной (по крайней мере, на свой вопрос я в ней ответ не нашёл).
   При пользовании EXCEL встретился термин «ПРОСТАЯ ССЫЛКА»! Не пойму, что он означает.
   В частности, указание на необходимость /возможность использования «простой ссылки» даётся при неправильном (по меркам EXCEL, естественно) написании ОГРАНИЧЕНИЙ, используемых при применении опции «ПОИСК РЕШЕНИЯ» (буквально появляется следующее сообщение: «Ограничение должно быть числом, ПРОСТОЙ ССЫЛКОЙ или формулой с числовыми значениями»).
   Исходя из специфики решаемой задачи в данном случае интерес может представлять только ПРОСТАЯ ССЫЛКА.
   Буду признателен автору, если он пояснит значение этого термина (разумеется, если знает это).

   Заранее спасибо. С уважением, Виктор (Московская область)

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