Относительная ссылка в excel как сделать
Преимущества абсолютных ссылок сложно недооценить. Их часто приходится использовать в процессе работы с программой. Относительные ссылки на ячейки в Excel более популярные чем, абсолютные, но так же имеют свои плюсы и минусы.
В Excel существует несколько типов ссылок: абсолютные, относительные и смешанные. Сюда так же относятся «имена» на целые диапазоны ячеек. Рассмотрим их возможности и отличия при практическом применении в формулах.
Абсолютные и относительные ссылки в Excel
Абсолютные ссылки позволяют нам зафиксировать строку или столбец (или строку и столбец одновременно), на которые должна ссылаться формула. Относительные ссылки в Excel изменяются автоматически при копировании формулы вдоль диапазона ячеек, как по вертикали, так и по горизонтали. Простой пример относительных адресов ячеек:
- Заполните диапазон ячеек A2:A5 разными показателями радиусов.
- В ячейку B2 введите формулу вычисления объема сферы, которая будет ссылаться на значение A2. Формула будет выглядеть следующим образом: =(4/3)*3,14*A2^3
- Скопируйте формулу из B2 вдоль колонки A2:A5.
Как видите, относительные адреса помогают автоматически изменять адрес в каждой формуле.
Так же стоит отметить закономерность изменения ссылок в формулах. Данные в B3 ссылаются на A3, B4 на A4 и т.д. Все зависит од того куда будет ссылаться первая введенная формула, а ее копии будут изменять ссылки относительно своего положения в диапазоне ячеек на листе.
Использование абсолютных и относительных ссылок в Excel
Заполните табличку, так как показано на рисунке:
Описание исходной таблицы. В ячейке A2 находиться актуальный курс евро по отношению к доллару на сегодня. В диапазоне ячеек B2:B4 находятся суммы в долларах. В диапазоне C2:C4 будут находится суммы в евро после конвертации валют. Завтра курс измениться и задача таблички автоматически пересчитать диапазон C2:C4 в зависимости от изменения значения в ячейке A2 (то есть курса евро).
Для решения данной задачи нам нужно ввести формулу в C2: =B2/A2 и скопировать ее во все ячейки диапазона C2:C4. Но здесь возникает проблема. Из предыдущего примера мы знаем, что при копировании относительные ссылки автоматически меняют адреса относительно своего положения. Поэтому возникнет ошибка:
Относительно первого аргумента нас это вполне устраивает. Ведь формула автоматически ссылается на новое значение в столбце ячеек таблицы (суммы в долларах). А вот второй показатель нам нужно зафиксировать на адресе A2. Соответственно нужно менять в формуле относительную ссылку на абсолютную.
Как сделать абсолютную ссылку в Excel? Очень просто нужно поставить символ $ (доллар) перед номером строки или колонки. Или перед тем и тем. Ниже рассмотрим все 3 варианта и определим их отличия.
Наша новая формула должна содержать сразу 2 типа ссылок: абсолютные и относительные.
- В C2 введите уже другую формулу: =B2/A$2. Чтобы изменить ссылки в Excel сделайте двойной щелчок левой кнопкой мышки по ячейке или нажмите клавишу F2 на клавиатуре.
- Скопируйте ее в остальные ячейки диапазона C3:C4.
Описание новой формулы. Символ доллара ($) в адресе ссылок фиксирует адрес в новых скопированных формулах.
Абсолютные, относительные и смешанные ссылки в Excel:
- $A$2 – адрес абсолютной ссылки с фиксацией по колонкам и строкам, как по вертикали, так и по горизонтали.
- $A2 – смешанная ссылка. При копировании фиксируется колонка, а строка изменяется.
- A$2 – смешанная ссылка. При копировании фиксируется строка, а колонка изменяется.
Для сравнения: A2 – это адрес относительный, без фиксации. Во время копирования формул строка (2) и столбец (A) автоматически изменяются на новые адреса относительно расположения скопированной формулы, как по вертикали, так и по горизонтали.
Примечание. В данном примере формула может содержать не только смешанную ссылку, но и абсолютную: =B2/$A$2 результат будет одинаковый. Но в практике часто возникают случаи, когда без смешанных ссылок не обойтись.
Полезный совет. Чтобы не вводить символ доллара ($) вручную, после указания адреса периодически нажимайте клавишу F4 для выбора нужного типа: абсолютный или смешанный. Это быстро и удобно.
При работе с формулами в программе Microsoft Excel пользователям приходится оперировать ссылками на другие ячейки, расположенные в документе. Но, не каждый пользователь знает, что эти ссылки бывают двух видов: абсолютные и относительные. Давайте выясним, чем они отличаются между собой, и как создать ссылку нужного вида.
Определение абсолютных и относительных ссылок
Что же представляют собой абсолютные и относительные ссылки в Экселе?
Абсолютные ссылки – это ссылки, при копировании которых координаты ячеек не изменяются, находятся в зафиксированном состоянии. В относительных ссылках координаты ячеек изменяются при копировании, относительно других ячеек листа.
Пример относительной ссылки
Покажем, как это работает на примере. Возьмем таблицу, которая содержит количество и цену различных наименований продуктов. Нам нужно посчитать стоимость.
Делается это простым умножением количества (столбец B) на цену (столбец C). Например, для первого наименования товара формула будет выглядеть так «=B2*C2». Вписываем её в соответствующую ячейку таблицы.
Теперь, чтобы вручную не вбивать формулы для ячеек, которые расположены ниже, просто копируем данную формулу на весь столбец. Становимся на нижний правый край ячейки с формулой, кликаем левой кнопкой мыши, и при зажатой кнопке тянем мышку вниз. Таким образом, формула скопируется и в другие ячейки таблицы.
Но, как видим, формула в нижней ячейке уже выглядит не «=B2*C2», а «=B3*C3». Соответственно, изменились и те формулы, которые расположены ниже. Вот таким свойством изменения при копировании и обладают относительные ссылки.
Ошибка в относительной ссылке
Но, далеко не во всех случаях нам нужны именно относительные ссылки. Например, нам нужно в той же таблице рассчитать удельный вес стоимости каждого наименования товара от общей суммы. Это делается путем деления стоимости на общую сумму. Например, чтобы рассчитать удельный вес картофеля, мы его стоимость (D2) делим на общую сумму (D7). Получаем следующую формулу: «=D2/D7».
В случае, если мы попытаемся скопировать формулу в другие строки тем же способом, что и предыдущий раз, то получим совершенно неудовлетворяющий нас результат. Как видим, уже во второй строке таблицы формула имеет вид «=D3/D8», то есть сдвинулась не только ссылка на ячейку с суммой по строке, но и ссылка на ячейку, отвечающую за общий итог.
D8 – это совершенно пустая ячейка, поэтому формула и выдаёт ошибку. Соответственно, формула в строке ниже будет ссылаться на ячейку D9, и т.д. Нам же нужно, чтобы при копировании постоянно сохранялась ссылка на ячейку D7, где расположен итог общей суммы, а такое свойство имеют как раз абсолютные ссылки.
Создание абсолютной ссылки
Таким образом, для нашего примера делитель должен быть относительной ссылкой, и изменяться в каждой строке таблицы, а делимое должно быть абсолютной ссылкой, которая постоянно ссылается на одну ячейку.
С созданием относительных ссылок у пользователей проблем не будет, так как все ссылки в Microsoft Excel по умолчанию являются относительными. А вот, если нужно сделать абсолютную ссылку, придется применить один приём.
После того, как формула введена, просто ставим в ячейке, или в строке формул, перед координатами столбца и строки ячейки, на которую нужно сделать абсолютную ссылку, знак доллара. Можно также, сразу после ввода адреса нажать функциональную клавишу F7, и знаки доллара перед координатами строки и столбца отобразятся автоматически. Формула в самой верхней ячейке примет такой вид: «=D2/$D$7».
Копируем формулу вниз по столбцу. Как видим, на этот раз все получилось. В ячейках находятся корректные значения. Например, во второй строке таблицы формула выглядит, как «=D3/$D$7», то есть делитель поменялся, а делимое осталось неизменным.
Смешанные ссылки
Как видим, при работе с формулами в программе Microsoft Excel для выполнения различных задач приходится работать как с относительными, так и с абсолютными ссылками. В некоторых случаях используются также смешанные ссылки. Поэтому, пользователь даже среднего уровня должен четко понимать разницу между ними, и уметь пользоваться этими инструментами.
Мы рады, что смогли помочь Вам в решении проблемы.
Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
Да Нет
В Excel существует два типа ссылок: абсолютные и относительные. Эти ссылки ведут себя по разному при копировании и заполнении ячеек. Относительные ссылки изменяются когда вы копируете формулу из одной ячейки в другую, а абсолютные ссылки, напротив, не меняются вне зависимости от того, куда бы вы их ни скопировали.
Относительные ссылки в Excel
По умолчанию, все ссылки в Excel относительные. Когда вы копируете ссылку из одной ячейки в другую, она автоматически изменяется относительно позиции столбца и строки новой ячейки к ячейке, из которой вы скопировали ссылку. Например, если вы скопируете формулу =A1+B1 из строки 1 в строку 2, то формула изменится на =A2+B2. Относительные ссылки полезны в том случае, когда нам нужно повторить один и тот ж расчет на несколько столбцов и строк.
Как создать и скопировать формулу с относительными ссылками
На примере ниже мы хотим создать формулу, которая поможет в расчетах итогового счета за заказ в ресторане. Мы хотим в колонке “Итог” рассчитать сумму к оплате за каждое блюдо, в зависимости от его стоимости за единицу и количества. Для этого, мы создадим одну формулу с относительной ссылкой и скопируем ее на все строки таблицы. Для этого проделаем следующие шаги:
- Выделим первую ячейку, в столбце “Итог” в которой будет создана наша формула:
- Вставим в ячейку D2 формулу, которая перемножает цену за блюдо и количество: =B2*C2.
- Нажмите клавишу “Enter” на клавиатуре. Формула произведет расчет и его результат вы увидите в ячейке D2.
- Зажав левую клавишу мыши, протяните ячейку D2 за правый нижний угол по всему диапазону ячеек D3:D12. Таким образом, вы скопируете формулу из ячейки D2 и перенесете ее на каждую ячейку диапазона.
- Для того, чтобы удостовериться, что формулы скопировались правильно, дважды кликните на любой ячейке диапазона, в котором была протянута формула и вы увидите формулу перемножения ячеек.
Абсолютные ссылки в Excel
Часто, при расчетах нам нужно, чтобы при копировании формул, ссылки на ячейки не изменялись. В отличии от относительных ссылок, абсолютные позволяют зафиксировать при расчетах ячейки в определенных строках и столбцах, что делает процесс вычисления в таблицах более простым и эффективным.
Для создания абсолютной ссылки используется знак доллара “$”. С его помощью вы можете зафиксировать от изменений столбец, строку или всех вместе:
$A$2 – столбец и строка не изменяются при копировании формулы;
A$2 – при копировании формулы не меняется только строка;
$A2 – столбец не изменяется при копировании формулы.
Используя абсолютные ссылки в Excel, вы можете быстро изменять настройки фиксации столбца и строки, с помощью клавиши F4. Для этого нужно дважды кликнуть на ячейку с формулой, затем, левой клавишей мыши поставить курсор на значение ячейки и с помощью клавиши F4 настроить фиксацию строки и столбца.
Как создать и скопировать формулу с абсолютными ссылками
В нашем примере мы будем использовать в ячейке E1 – 18% как значение НДС для расчета налога на товары в колонке D. Для правильного расчета нам потребуется использовать абсолютную ссылку $E$1 в нашей формуле, так как нам важно, чтобы стоимость каждого товара перемножалась на ставку НДС, указанную в ячейке E1. Ниже рассмотрим как мы будем это делать:
- Выделим ячейку, в которую мы хотим вставить формулу для расчета налога. В нашем примере это ячейка D3.
- Напишем формулу, рассчитывающую сумму налога для каждого товара, с учетом его стоимости и количества =(B3*C3)*$E$1.
- Протянем полученную формулу на все ячейки в диапазоне D4:D13.
- Дважды кликните на любой ячейке из диапазона D4:D13 и убедитесь, что формула сработала корректно. Важно убедиться, что вы правильно указали ссылку на ячейку $E$1 в абсолютном формате.
Как создать ссылки на другие листы в Excel
Зачастую, нам в расчетах требуется задействовать данные с разных листов файла Excel. Для этого, при создании ссылки на ячейку из другого листа нужно использовать название листа и восклицательного знака на конце (!). Например, если вы хотите создать ссылку на ячейку A1 на листе Sheet1, то ссылка на эту ячейку будет выглядеть так:
=Sheet1!A1
ВАЖНО! Если в название листа, на ячейку с которого вы ссылаетесь есть пробелы, то название этого листа в ссылке должно быть заключено в кавычки (‘ ‘). Например, если название вашего листа Бюджет Финал, то ссылка на ячейку A1 будет выглядеть так:
‘Бюджет Финал’!A1
На примере ниже, мы хотим добавить в таблицу ссылку на ячейку, в которой уже произведены вычисления между двумя листами Excel файла. Это позволит нам использовать одно и то же значение на двух разных листах без перезаписи формулы или копирования данных между рабочими листами. Для этого проделаем следующие шаги:
- Выберем ячейку, на которую мы хотим сослаться и обратим внимание на название листа. В нашем случае это ячейка E14 на вкладке “Меню”:
- Перейдем на лист и выберем ячейку, в которой мы хотим поставить ссылку. В нашем примере это ячейка B2.
- В ячейке B2 введем формулу, ссылающуюся на ячейку E14 с листа “Меню”: =Меню!E14
- Нажмем клавишу “Enter” на клавиатуре и увидим в ячейке B2 значение ячейки E14 с листа “Меню”.
Если, в дальнейшем, вы переименуете лист, на который вы ссылались, то система автоматически обновит формулу.
word-office.ru
Относительные ссылки в Excel — Microsoft Excel для начинающих
Относительные ссылки в Excel позволяют значительно упростить жизнь, даже обычному рядовому пользователю. Используя относительные ссылки в своих вычислениях, Вы можете буквально за несколько секунд выполнить работу, на которую, в противном случае, понадобились бы часы. В данном уроке Вы узнаете все об абсолютных ссылках, а также научитесь применять их при решении задач в Microsoft Excel.
В Excel существует два типа ссылок: относительные и абсолютные. Относительные и абсолютные ссылки ведут себя по-разному при копировании и заполнении других ячеек. Относительные ссылки изменяются при копировании, а абсолютные, напротив, остаются неизменными.
Более подробно об абсолютных ссылках в Excel Вы можете прочитать в данном уроке.
Относительная ссылка – что это?
По умолчанию, все ссылки в Excel являются относительными. При копировании формул, они изменяются на основании относительного расположения строк и столбцов. Например, если Вы скопируете формулу =A1+B1 из строки 1 в строку 2, формула превратится в =A2+B2. Относительные ссылки особенно удобны, когда необходимо продублировать тот же самый расчет по нескольким строкам или столбцам.
Создание и копирование формул с относительными ссылками
В следующем примере мы создадим выражение, которое будет умножать стоимость каждой позиции в меню на количество. Вместо того чтобы создавать новую формулу для каждой строки, мы можем создать всего одну, а затем скопировать ее в другие строки. Для этого воспользуемся относительными ссылками, чтобы правильно вычислить сумму по каждому элементу.
- Выделите ячейку, которая будет содержать формулу. В нашем примере мы выбрали ячейку D2.
- Введите выражение для вычисления необходимого значения. В нашем примере, мы введем =B2*C2.
- Нажмите Enter на клавиатуре. Формула будет вычислена, а результат отобразится в ячейке.
- Найдите маркер автозаполнения в правом нижнем углу рассматриваемой ячейки. В данном примере мы ищем маркер автозаполнения в ячейке D2.
- Нажмите и, удерживая левую кнопку мыши, перетащите маркер автозаполнения по необходимым ячейкам. В нашем случае это диапазон D3:D12.
- Отпустите кнопку мыши. Формула будет скопирована в выбранные ячейки с относительными ссылками, и в каждой будут вычислены значения.
Вы можете дважды щелкнуть по заполненным ячейкам, чтобы проверить правильность своих формул. Относительные ссылки должны быть разными для каждой ячейки, в зависимости от строки.
В Excel Вы также можете создавать ссылки между листами внутри документа. Более подробно об этом читайте в уроке Ссылки на другие листы в Excel.
Оцените качество статьи. Нам важно ваше мнение:
office-guru.ru
Относительные и абсолютные ссылки в Эксель
Для того, чтобы сделать таблицу еще более гибкой и удобной, нам потребуются некоторые дополнительные знания. То есть опять немного теории…
На прошлом шаге мы создали целый столбец значений просто воспользовавшись функцией автозаполнения Excel.
Программа автоматически «поняла» нашу задумку и значения адресов ячеек в формулы были подставлены правильные, но ка это произошло?
Практически во всех формулах используются адреса ячеек, а правильнее сказать — ссылки на ячейки, что позволяет нам использовать в формулах значения, которые находятся или рассчитываются в других ячейках.
Если в формуле сделана ссылка на ячейку В3 (в которой находится какое-то число) и мы поменяем значение в этой ячейке, то автоматически будет пересчитана и формула.
Поясню на примере.
В ячейку B3 введем цифру 2, а в ячейку B4 вставим следующую формулу: = B3+3
Какой будет результат вычислений?
Правильно, после нажатия клавиши Enter в ячейке B4 отобразится вычисленное значение — 5.
Что произойдет, если мы изменим значение ячейки B3 с 2 на 4?
Верно! Значение в ячейке B4 автоматически пересчитается и будет равным 7. Это означает, что формула будет рассчитываться в соответствии с новым значением ячейки В3.
Если бы не было ссылок (в нашем примере это ссылка на ячейку B3), то нам бы пришлось вручную изменять все формулы при изменении какого-то одного ее компонента.
Итак, в формулах у нас могут участвовать ссылки на адреса ячеек таблицы. Эти ссылки могут быть относительными или абсолютными. Как показывает время, тема эта не очень проста для понимания, поэтому давайте с ней тщательно разберемся.
Мы с вами научились использовать автозаполнение для тиражирования однотипных формул в таблице. По сути автозаполнение нам позволяет скопировать формулу из одной ячейки и вставить ее в следующую. То же самое происходит и при фактическом копировании. Чтобы в этом убедиться, давайте скопируем ячейку В8. Для этого выделим ячейку (1) и щелкнем на ней правой кнопкой мыши для вызова контекстного меню (2).
Данное меню называется контекстным, так как его содержимое изменяется в зависимости от того, на каком объекте оно вызывается, то есть от контекста конкретной ситуации.
Выберем из контекстного меню пункт Копировать. Ячейка выделилась динамической рамкой.
Это нам указывает на то, что содержимое данной ячейки только что было скопировано.
Теперь установим табличный курсор в пустую ячейку В19 и вставим скопированную информацию. Для этого мы также можем воспользоваться контекстным меню, но мы задействуем панель инструментов Буфер обмена на вкладке Главная — нажимаем на кнопку Вставить и получаем результат.
Если мы посмотрим на формулу в ячейке B19, то увидим в ней ссылку на ячейка В18.
Понимаете что произошло? Это очень важно понять!
Мы копировали ячейку В8, в которой в формуле использовалась ссылка на вышестоящую ячейку В7 (1, см. рис. выше), то есть использовалась ссылка на ячейку, которая была выше ячейки с формулой. Именно так она и была скопирована — в ячейке В19 (2, см. рис. выше) в формуле используется ссылка на вышестоящую ячейку В18. То есть адрес ячейки в формуле берется относительно местоположения ячейки с формулой.
Для проверки попробуйте ответить на вопрос — если сейчас скопировать формулу из ячейки В7 (см. рис. выше), которая у нас несколько отличается от формул в других ячейках, и вставить ее в ячейку В20, то ссылка на какую ячейку мы увидим в итоге?
Ответили?
Давайте проверим — в формулу войдет ячейка B16, которая находится выше на четыре ячейки, как и ячейка В3, относительно ячейки В7.
Вот такие ссылки называются относительными, так как в формулу подставляется значение из ячейки, расположенной на определенном расстоянии относительно ячейки, с которой мы работаем.
А что делать, если нужно, чтобы в формуле участвовало значение из какой-то конкретной ячейки? Все логично — нужно использовать абсолютные ссылки, то есть ссылки на конкретные ячейки таблицы.
Как мы знаем, адрес ячейки состоит из обозначения столбца и строки, например, ячейка B3 находится на пересечении третьей строки и столбца B.
Чтобы сделать ссылку на ячейку абсолютной, нужно поставить знак $ перед обозначением столбца и строки (знак доллара ставится с помощью сочетаний клавиш Shift + 4). То есть, если я хочу, чтобы в формуле у меня всегда участвовало значение из ячейки В3, то изменю В3 на $B$3.
Давайте так и сделаем — изменим формулу в ячейке В7:
Одна ссылка на ячейку B3 у нас абсолютная, а другая остается относительной. Если теперь скопировать значение ячейки B7 и вставить его в любую другую ячейку, например, В21, то мы увидим следующее:
Абсолютная ссылка по-прежнему ведет на ячейку B3, а относительная изменилась и теперь указывает на ячейку B17.
То есть знаком $ мы как бы запрещаем Экселю изменять столбец или строку в адресе ячейки и он всегда будет в неизменном виде.
При этом мы можем запрещать изменять в ссылке что-то одно — либо столбец, либо строку. Такая ссылка будет называться смешанной. Такие ссылки выглядят так — B$2 или $B2, что, соответственно, запретит Эксель менять адрес строки или столбца.
Ну а теперь давайте попрактикуемся и применим полученные знания.
В ячейке А4 напишем — Процент, а в ячейку В4 подставим значение процента по вкладу — 11.
Теперь изменим формулы — меняем в ячейке В7 значение процента на абсолютную ссылку — $B$4, затем тоже самое проделаем в ячейке В8:
Растиражируем с помощью автозаполнения формулу из ячейки B8 на остальные ячейки столбца B.
Что мы получили в итоге? А то, что теперь мы можем менять и сумму вклада, и процент, а остальные данные будут вычисляться автоматически!
Давайте снизу таблицы в ячейке А19 напишем — Доход, а в ячейку В19 подставим простую формулу, вычисляющую этот доход: =B18-B3, то есть сумма итогового вклада с процентами минус сумма начального вклада.
С точки зрения функциональности таблица готова, но вот значения в ней не очень наглядны. Поэтому стоит применить некоторое форматирование, чтобы сделать таблицу более читаемой.
Именно это мы и сделаем на следующем шаге.
pcsecrets.ru
Ссылки в Excel | statanaliz.info
Когда пользуешься различными формулами Excel, то приходится обращать внимание на ссылки. Ссылки в Excel, как известно, бывают разными: относительными, абсолютными, внешними, в виде имен диапазонов и др.
Первым делом уясним, что такое относительные и абсолютные ссылки и зачем конструкторы Excel их придумали. Вопрос не праздный т.к. при работе с большими массивами данных формула прописывается обычно один раз, а затем «протягивается» (копируется) на другие ячейки (например, в итоговой строке или итоговом столбце таблицы). Корректность такого «протягивания» сильно зависит от типа ссылки. Здесь есть два варианта. Первый: диапазон, на который ведет ссылка, «перемещается» вслед за копируемой ячейкой, то есть остается на одном месте относительно ячейки с формулой. Второй: ссылка ведет на один и тот же адрес независимо от того, куда копируется формула. Недооценка важности вида ссылки приводит к ошибкам в расчетах и необходимости коррекции формул. Все это как минимум тормозит работу. Помните, что каждое лишнее действие вносит свой вклад в бесполезно потраченное время.
Рассмотрим простой пример. Нужно сложить два числа. Сделать это легко, прописав в свободной ячейке (например, внизу) знак «=» и затем через знак «+» сослаться на складываемые ячейки. Если чисел много, то суммировать лучше через функцию СУММ, указав сразу весь диапазон суммирования.
Как несложно заметить, вместо чисел мы делаем ссылки на складываемые ячейки или сразу на целый диапазон. Ссылки вместо чисел – это основное отличие Excel от калькулятора. Но чтобы они работали правильно, неплохо бы различать абсолютные и относительные ссылки.
Относительные ссылки Excel
Возьмем чуть более сложный пример, когда нужно вычислить сумму для нескольких столбцов или, как иногда говорят, подбить итоги. Чаще всего формулу вводят в одну ячейку и затем «протягивают» в нужном направлении.
Как видим, при копировании формулы диапазон суммирования автоматически перемещается вслед за формулой, позволяя за минимум действий рассчитать итоговую строку. Подобный расчет возможен благодаря относительности ссылок, которые как бы привязаны к ячейке с формулой, а не к адресу, на который указывают. Если формула копируется вправо, то и вычисляемый диапазон смещается туда же. Если вниз или вверх, то же самое произойдет с диапазоном вычисления. По умолчанию ссылки в рамках одной книги Excel делаются относительными и имеют следующий вид:
=A1,
где буква обозначает столбец, а число – строку.
Однако бывают ситуации, когда ссылка должна оставаться на месте, независимо от того, куда копируется формула.
Абсолютные ссылки
Рассмотрим другой пример, когда нужно рассчитать долю каждого значения к итогу. Введем формулу для расчета доли первого числа.
Теперь попробуем «протянуть» формулу вниз для расчета остальных долей. Сумма долей должна получиться ровно 100%. Однако уже на втором значении видно: что-то пошло не так.
Числитель «поехал» вниз правильно, а вот знаменатель должен был остаться на месте. Чтобы все получилось правильно, нужно зафиксировать ссылку на итог, чтобы она не «ездила» вслед за формулой. Короче говоря, ссылку на итоговую сумму нужно сделать абсолютной, для чего перед названием строки и столбца ставится значок доллара $ (<Shift+4> в английской раскладке):
=$A$1
Таким же образом можно «закрепить» ссылку на валютный курс, пошлину, налог и прочее, что находится в одном месте для многих ссылающихся ячеек.
Смешанные ссылки
Помимо абсолютных и относительных ссылок встречаются еще и т.н. смешанные ссылки, когда фиксируется только строка или столбец:
=A$1
=$A1
Закрепленная строка никак не влияет на абсолютность столбца, а абсолютный столбец не зависит от относительности строки. Для выбора правильного варианта нужно знать, куда копируется ячейка с формулой и не будет никаких проблем. Строго говоря, для расчета доли в примере выше ссылку на итог можно было сделать смешанной, т.к. фиксация была необходима только по строке. Но если формула копируется и по вертикали, и по горизонтали, тогда уж точно не обойтись без полной абсолютной ссылки.
Как быстро установить символ доллара $
Если ставить значок доллара с клавиатуры классическим методом, то потребуется вначале перейти в английскую раскладку, а затем нажать Shift+4. Скажем прямо, это долго и неудобно. Гораздо быстрее войти в режим редактирования формул с помощью клавиши F2 (если вы набираете формулу вручную, то уже находитесь в этом режиме), установить курсор на нужной ссылке и нажать F4. Ссылка начнет менять свой режим «абсолютности». После нажатия F4 относительная ссылка станет абсолютной (и по строкам, и по столбцам). Если нажать F4 еще раз, то абсолютной станет только строка, следующее нажатие сделает абсолютным только столбец. Очередное нажатие F4 сделает ссылку снова относительной. И так по кругу. Снова абсолютная, только строка, только столбец, относительная и т.д. Последовательным нажатием F4 останавливаетесь на нужном варианте.
Очень удобно, не нужно переключать раскладку и искать $. Эта же кнопка работает и в диалоговом окне «Вставки функции». Например, при написании формулы ВПР или СУММЕСЛИ нужно внимательно смотреть на ссылающиеся диапазоны, поэтому значки $ используются очень часто.
Как я уже говорил выше, если диапазон, на который нужно сослаться находится в той же книге (файле Excel), пусть даже и на другом листе, то Excel по умолчанию устанавливает относительные ссылки (без значка «$»). А вот ссылки на другие книги Excel по умолчанию делает абсолютными (вставляет «$» где только можно). Но про другие листы и книги поговорим в следующем параграфе.
Ссылки на другие листы и книги
Excel умеет ссылаться не только в пределах одного листа, но и на другие листы и даже другие файлы Excel. Такой функционал значительно расширяет диапазон вычислений. Если в формулах есть ссылки на другие файлы, то следует обратить внимание на следующее. Во-первых, при открытии файла с внешними связями Excel назойливо напоминает об этом выскакивающим посреди экрана окошком. Это, конечно, помогает не забывать, что в файле есть внешние ссылки, но лучше, чтобы их вообще не было. Во-вторых, при изменении или удалении внешнего источника мгновенно меняются и результаты вычислений, что нужно далеко не всегда. В третьих, если возникнут ошибки в расчетах, то на обнаружение их источника может потребоваться дополнительное время, т.к. данные находятся в другом файле. В общем, сразу после вычислений с использованием данных из других книг внешние ссылки рекомендуется удалить: Данные → Подключения → Изменить связи → Разорвать связь.
Все внутренние формулы останутся неизменными, а формулы с внешними ссылками превратятся в значения. Убрать ссылки Excel можно и по-другому. Для этих же целей можно использовать более топорный метод. Нужно просто скопировать диапазон с формулами и тупо вставить его сверху в виде значений (через специальную вставку). Правда, в значения превратятся уже все формулы, а не только внешние.
Стиль ссылок
И еще кое-что про ссылки. Иногда они выглядят несколько непривычно. Это особый стиль ссылок, при котором адрес ячейки A1 имеет наименование R1C1 (первая строка (R1), первая колонка (C1)). Такой стиль ссылок иногда может быть полезен, но довольно редко. Поэтому неплохо уметь возвращать нормальный вид адреса ячейки. Для этого нужно выполнить следующие действия: Файл → Параметры → Формулы и далее нужно снять галочку с пункта Стиль ссылки R1C1.
Всего доброго и до новых встреч.
Поделиться в социальных сетях:
statanaliz.info
Основные типы ссылок на ячейки в Excel
При работе в Excel обходиться без использования ссылок практически невозможно.
Рассмотрим на практике использование основных типов ссылок на ячейки в Excel.
Относительная ссылка в Excel
Относительная ссылка — это ссылка вида A1 (т.е. буква столбца + номер строки).
Основная особенность таких ссылок — при протягивании или копировании формулы в другие ячейки ссылка смещается.
Другими словами, при копировании вниз ссылка A1 превратится в A2, A3 и т.д., при копировании вправо — в B1, C1 и т.д.:
В данном примере мы копируем ячейку D2 с формулой A2*B2.
При перемещении формулы вниз получаем A2 -> A3 -> A4 -> A5, B2 -> B3 -> B4 -> B5.
Смешанная ссылка в Excel
Смешанная ссылка — это ссылка вида $A1 или A$1.
Знак доллара ($) служит фиксированием столбца или строки.
Иными словами, если мы поставим $ перед буквой столбца (например, $B5), то ссылка не будет изменяться по столбцам, но будет изменяться по строкам (при протягивании формула сместится на $B5, $B6, $B7 и т.д.). Аналогично, если знак $ поставить перед номером строки (например, B$5), то ссылка не будет изменяться по строкам, но будет изменяться по столбцам (при перемещении формула сдвинется на C$5, D$5, E$5 и т.д.).
Разберем использование смешанных ссылок на построении стандартной таблицы умножения:
В данном примере любая формула таблицы является произведением значений из столбца A и строки 2.
Добавляя в формулу расчета знак $ (например, G$2*$A8) мы последовательно фиксируем столбец и строку.
Абсолютная ссылка в Excel
Абсолютная ссылка — это ссылка вида $A$1.
Её особенность в том, что она не изменяется при копировании или протягивании формулы в другие ячейки.
В данном случае знак $ ставится как перед буквой столбца, так и перед номером строки, т.е. формула полностью фиксируется.
Абсолютная ссылка часто применяется, когда необходимо умножить или разделить диапазон ячеек на одно и тоже число.
Например, перевести данные в рубли по определенному курсу, или перевести данные в тысячи/миллионы/миллиарды:
Как сделать ссылку относительной/абсолютной/смешанной?
Помимо ручного проставления знака $ в формулу ячейки, можно воспользоваться инструментами Excel.
Если выделить формулу и последовательно нажимать клавишу F4, то Excel автоматические добавляет знак $ в формулу в следующем порядке — B5 -> $B$5 -> $B5 -> B$5
В зависимости от типа ссылки которую мы хотим поставить — нажимаем несколько раз F4 и получаем требуемый результат.
Удачи вам и до скорой встречи на страницах блога Tutorexcel.ru!
Поделиться с друзьями:
Поиск по сайту:
tutorexcel.ru
Ссылки в Excel
Ссылки в Эксель обычно используются для расчета формул. Причем ссылаться можно как на другую ячейку, так и на ячейку из другого листа или вообще из другой книги. Используя гиперссылки можно открыть любую программу или другую необходимую книгу Excel. Именно об этом и пойдет речь в данной статье.
Абсолютная и относительная ссылка на ячейку в Excel
Как Вы уже заметили, при вставке формул в документ Excel, в строке формул указываются адреса на ячейки, в которых содержатся данные. По умолчанию вставляются относительные ссылки на ячейки.
Относительная ссылка позволяет изменять адрес ячеек по строкам и столбцам при копировании формулы в другое место документа. То есть, если скопировать формулу из ячейки А3 в ячейку С3, то для расчета суммы возьмутся новые адреса ячеек: С1 и С2.
Использование относительных ссылок Вам пригодится, если нужно вводить в документ много одинаковых формул, например, при заполнении таблицы.
Рассмотрим следующий пример. Есть таблица, в которую внесены: наименование товара, его цена и количество проданных единиц. Посчитаем итоговую сумму для каждой единицы. В ячейку D6 пишем формулу: =В6*С6. Как видите, ссылки на ячейки в формуле относительные.
Чтобы не вписывать формулу в каждую строчку, выделите ячейку D6, кликните мышкой по маркеру в правом нижнем углу ячейки и растяните ее на необходимые ячейки.
Формула скопируется, и значения будут посчитаны. Если хотите проверить правильность формулы, выделите любую ячейку с результатом, и в строке формул посмотрите, какие ячейки использовались для расчета. Можете также кликнуть два раза мышкой по ячейке.
Абсолютная ссылка позволит закрепить определенную ячейку в строке и столбце для расчета формул. Таким образом, при копировании формулы, ссылка на эту ячейку меняться не будет.
Чтобы сделать абсолютную ссылку на ячейку в Excel, нужно добавить знак «$» в адрес ячейки перед названием столбца и строки. Или же поставить курсор в строке формул после адреса нужной ячейки и нажать «F4». В примере, для расчета суммы в ячейке А3, используется теперь абсолютная ссылка на ячейку А1.
Давайте посчитаем сумму для ячеек D1 и D2. В ячейку D3 скопируем формулу из А3. Как видите, результат вместо 24 – 25. Все из-за того, что в формуле была использована абсолютная ссылка на ячейку $A$1. Поэтому в расчете использовались не ячейки D1 и D2, а ячейки $A$1 и D2.
Рассмотрим для примера такую таблицу: есть наименование товара и его себестоимость. Чтобы определить цену товара для продажи, нужно посчитать НДС. НДС – 20%, и значение написано в ячейке В9. Вписываем формулу для расчета в ячейку С6.
Если мы скопируем формулу в остальные ячейки, то не получим результат. Так как в расчете будут использоваться ячейки В10 и В11, которые не заполнены значениями.
В этом случае, нужно использовать абсолютную ссылку на ячейку $В$9, чтобы для расчета формулы всегда бралось значение из этой ячейки. Теперь расчеты правильные.
Если в строке формул поставить курсор после адреса ячейки и нажать «F4» второй и третий раз, то получится смешанная ссылка в Excel. В этом случае, при копировании может не изменяться или строка – А$1, или столбец – $А1.
Ссылка на другой лист в Excel
Ссылка на другой лист в Эксель пригодится, если Вам нужно использовать в расчетах для формулы значения, которые находятся на других листах документа Excel.
Ссылка на ячейку с другого листа в формуле будет выглядеть следующим образом: Лист1!А1 – название листа, знак восклицания, адрес ячейки. Если в названии листа используются пробелы, то его нужно взять в одинарные кавычки: ‘Итоговые суммы’ – ‘Итоговые суммы’!А1.
Например, рассчитаем значение НДС для товаров. Таблица, в которой будет рассчитываться формула, находится на Листе1, значение НДС находится на листе с названием Все константы.
На листе Все константы для расчета формулы нам необходимо будет значение, записанное в ячейке В1.
Возвращаемся на Лист1. В ячейку С6 пишем формулу для расчета НДС: ставим «=», затем выделяем ячейку В6 и делаем ссылку на ячейку В1 с другого листа.
Чтобы формула правильно посчитала значения в других ячейках, делаем ссылку на ячейку В1 абсолютной: $В$1, и растягиваем ее по столбцу.
Если изменить название листа Все константы на Все константы1111, то оно автоматически поменяется и в формуле. Точно также, если на листе Все константы изменить значение в ячейке В1 с 20% на 22%, то формула будет пересчитана.
Для того чтобы сделать ссылку на другую книгу Excel в формуле, возьмите ее название в квадратные скобки. Например, сделаем ссылку в ячейке А1 в книге с названием Книга1 на ячейку А3 из книги с названием Ссылки. Для этого ставим в ячейку А1 «=», в квадратных скобках пишем название книги с расширением, затем название листа из этой книги, ставим «!» и адрес ячейки.
Книга, на которую мы ссылаемся, должна быть открыта.
Ссылка на файл или гиперссылка в Excel
В документах Excel иногда появляется необходимость ссылаться на внешние файлы или на другие книги Excel. Реализовать нам это поможет использование гиперссылки.
Выделите ячейку, в которую необходимо вставить гиперссылку. Это может быть как число, так и текст или рассчитанная формула, или пустая ячейка. Перейдите на вкладку «Вставка» и кликните по кнопочке «Гиперссылка».
Сделаем ссылку на другую книгу Эксель. В поле «Связать с» выбираем «файлом, веб-страницей». Найдите нужную папку на компьютере и выделите файл. В поле «Текст» можно изменить надпись, которая будет отображаться в ячейке – это только в том случае, если ячейка изначально была пустая. Нажмите «ОК».
Теперь при нажатии на созданную гиперссылку будет открываться книга Excel с названием Список.
Как видите, ссылки в Excel могут быть нескольких видов: относительные, абсолютные и смешанные. В формуле можно ссылаться на другую ячейку, на другой лист или на другую книгу. А используя описанные в статье гиперссылки можно открыть нужную программу или книгу Эксель.
Оценить статью: Загрузка… Поделиться новостью в соцсетях
Об авторе: Олег Каминский
Вебмастер. Высшее образование по специальности «Защита информации». Создатель портала comp-profi.com. Автор большинства статей и уроков компьютерной грамотности
comp-profi.com
Абсолютные и относительные ссылки Excel: Как с ними работать[2019]
Пользуясь программой Excel, вы должны знать, что здесь можно пользоваться формулами. Абсолютные и относительные ссылки упростят работу, а как ими пользоваться – читайте в нашей статье!
С помощью абсолютных и относительных ссылок пользователь Excel получает возможность создавать формулы, ссылающиеся на другой диапазон или ячейку. Но проблема в том, что пользователям сложно ориентироваться в этих понятиях. А если прибавить к этому еще и формулы…
Однако вы не переживайте – мы объясним все сложное максимально просто.
Содержание:
Абсолютная ссылка – что это?
Структура формул, предусмотренных программой Excel, состоит из знака доллара, имеющего следующий вид: $. Его ставят перед ссылкой на строки и столбцы. Ниже вы можете ознакомиться с примерами формул с использованием знака доллара, а также объяснениями по каждой из них.
Формулы со знаком доллара и их значение
В большинстве случаев в процессе создания формул, структура которых включает в себя абсолютную ссылку, пользователь прибегает к применению следующего формата: $A$2. Все остальные форматы, приведенные в изображении выше, хоть и используются на практике, но довольно редко.
Разрабатывая формулу, пользователю достаточно нажать на клавиатуре кнопку «F4», чтобы переключиться между абсолютной и относительной ссылкой. Согласно отзывам офисных сотрудников, этот способ вставки абсолютной ссылки является наиболее быстрым и простым.
к содержанию ↑Создаем формулу, используя абсолютные ссылки
Разберем конкретный пример. Ячейка E1 будет содержать налоговую ставку 7.5 процента для определения налога с продаж для каждой позиции, введенной в столбец D. Так как любая формула сопровождается идентичной налоговой ставкой, нам надо оставлять ссылку идентичной, когда мы копируем формулу в столбец D. В этом поможет ссылка $E$1.
- Выделяем ячейку, в которую будет помещена формула. Здесь мы пользуемся ячейкой D3.
Выделение ячейки
- Вводим выражение, вычисляющее результат. Формула получит следующий вид: =(B3*C3)*$E$1.
Ввод выражения для вычисления результата
- После нажатия клавиши «Enter»программа вычислит формулу, после чего мы увидим результат.
- В правой нижней части найдите маркер автоматического заполнения, как показано на примере.
Использование маркера автозаполнения
- Нажмите и удерживайте ЛКМ, перетащив маркер автоматического заполнения по оставшимся ячейкам. В этом примере речь идет о диапазоне D4:D13.
Копирование формулы во все оставшиеся ячейки
- Отпустив ЛКМ, формула скопируется в ячейки с абсолютными ссылками, каждая из которых отобразит результаты вычислений.
Отображение результатов при использовании формулы
Для проверки правильности формул также можно сделать два клика по уже заполненным ячейкам. Каждая из них должна содержать одинаковую абсолютную ссылку, при этом относительные получат разные значения, исходя из строки.
Проверка правильности формулы
Создавая абсолютные ссылки, вам нужно обязательно проверить наличие знака доллара в адресах.
Пример ниже моделирует ситуацию, при которой этот символ случайно забыли скопировать. Результат – в процессе копирования Excel думает, что это относительная ссылка, вследствие чего был получен неправильный результат.
Пример ошибки в вычислениях, когда формула не содержит знака доллара
к содержанию ↑Относительная ссылка – что это?
Любая ссылка в Excel является относительной по умолчанию. В ходе копирования формулы они могут меняться, опираясь на относительное расположение столбцов и строк. Рассмотрим пример: копируя формулу =A1+B1 во вторую строку из первой, формула автоматически получит следующий вид: =A2+B2.
Использование относительных ссылок всегда удобно при необходимости продублировать идентичные расчеты по нескольким столбцам и/или строкам.
к содержанию ↑Создание и копирование формул с относительными ссылками
Следующий пример демонстрирует создание выражения, способного умножить стоимость, указанную в позиции в меню, на ее количество. Новички, не зная о существовании относительных ссылок, часто создают новую формулу для отдельной строки. В этом же случае остается создать одну формулу, которая, путем совершения простых действий, скопируется в другие строки.
- Выделяем ячейку, для которой требуется формула. На примере ниже задействована ячейка D2.
Выделение ячейки
- Чтобы вычислить необходимое значение вам нужно ввести выражение. В этой ситуации оно имеет следующий вид: =B2*C2.
Указание выражения для проведения вычислений
- Нажимаем кнопку «Enter» на клавиатуре. Программа автоматически вычислит формулу, после чего отобразится итоговый результат.
- Ищем маркер автоматического заполнения – он находится справа внизу нужной нам ячейки. Пример ниже задействует маркер автоматического заполнения для ячейки D2.
Использование маркера автозаполнения
- Нажимаем ЛКМ и удерживаем ее, параллельно перетаскивая маркер по всем оставшимся ячейкам. Наш конкретный пример демонстрирует вычисление в ячейках с D3 по D12.
Копирование маркера автозаполнения по всем оставшимся ячейкам
- Отпускаем ЛКМ. Формула автоматически скопируется в ячейки, содержащие относительные ссылки, и каждая из них получит вычисленные значения.
Получение итоговых результатов вычислений
Как и в случае с абсолютными, вы также можете проверить правильность создания формул для относительных ссылок. Для каждой ячейки они должны быть разными, исходя из конкретной строки.
Проверка правильности результатов с использованием формулы
Excel позволяет создать отдельные ссылки внутри документа для разных листов. Но об этом мы расскажем в других статьях.
к содержанию ↑Выводы
Использование относительных и абсолютных ссылок значительно упрощает вычисление в таблицах. Наличие этого инструмента здорово экономит время. Ведь так пользователю не нужно прописывать формулу по отдельности для каждой строки.
Если у вас возникают трудности с использованием рассмотренных инструментов, напишите свой комментарий. Мы же постараемся вам помочь!
geek-nose.com