Адреса и ссылки, абсолютные и относительные
Относительная адресация
Ячейка в столбце А, строке1 адресуется как А1. Можно адресовать область смежных ячеек с помощью введения координат верхней левой ячейки области, а затем после двоеточия — координат нижней правой ячейки. Например, квадрат, образованный первыми четырьмя ячейками в левом верхнем углу, адресуется как A1:B2.
Таким способом адресации создаётся относительная ссылка на область A1:B2. Здесь термин «относительная» означает, что ссылка на эту область будет автоматически корректироваться при копировании формул.
Абсолютная адресация
Absolute referencing is the opposite of relative addressing. A dollar sign is placed before each letter and number in an absolute reference, for example, $A$1:$B$2.
LibreOffice can convert the current reference, in which the cursor is positioned in the input line, from relative to absolute and vice versa by pressing F4.
If you start with a relative address such as A1, the first time you press this key combination, both row and column are set to absolute references ($A$1). The second time, only the row (A$1), and the third time, only the column ($A1). If you press the key combination once more, both column and row references are switched back to relative (A1)
LibreOffice Calc shows the references to a formula. If, for example, you click the formula =SUM(A1:C5;D15:D24) in a cell, the two referenced areas in the sheet will be highlighted in color. For example, the formula component «A1:C5» may be in blue and the cell range in question bordered in the same shade of blue. The next formula component «D15:D24» can be marked in red in the same way.
Когда использовать абсолютные и относительные ссылки
Что характерно для относительной ссылки? Предположим, нужно подсчитать в ячейке Е1 сумму всех ячеек области A1:B2. Формула для ввода в ячейку Е1 имеет вид: =SUM(A1:B2).
Если в дальнейшем потребуется вставить новый столбец перед столбцом А, относительные адреса элементов, которые нужно суммировать, станут B1:C2, а формула будет находиться в ячейке F1, а не Е1. После вставки нового столбца придется проверить и подкорректировать все формулы в листе и, возможно, в остальных листах.
Удобно, что LibreOffice выполняет эту работу автоматически. После вставки нового столбца А формула =SUM(A1:B2) автоматически изменится на =SUM(B1:C2). Номера строк также автоматически откорректируются, если будет вставлена новая строка 1. Абсолютные и относительные ссылки всегда корректируются в LibreOffice Calc при любом перемещении области, на которую есть ссылка. Однако будьте внимательны, когда копируете формулу, поскольку в этом случае будут корректироваться только относительные ссылки, но не абсолютные.
Абсолютные ссылки используются, когда при вычислении ссылаются на одну конкретную ячейку на листе. Если формула, ссылающаяся как раз на эту ячейку, копируется на ячейку ниже относительно первоначальной ячейки, то ссылка соответственно будет передвинута вниз, если координаты ячейки не были определены как абсолютные.
Кроме случая вставки новых строк и столбцов, ссылки могут также измениться, когда существующая формула, которая ссылается на определенные ячейки, копируется в другую область листа. Предположим, в строку 10 введена формула =SUM(A1:A9). Если нужно подсчитать сумму соседнего столбца справа, просто скопируйте эту формулу в ячейку, расположенную справа. Копия формулы в столбце В будет автоматически откорректирована и примет вид =SUM(B1:B9).
1. Относительные и абсолютные ссылки
Microsoft Office Excel
Лабораторная работа
«Построение диаграмм в MS Excel»
Цель работы:
использование относительных и абсолютных адресов ячеек;
построение диаграмм;
связывание ячеек в таблицах текущего рабочего листа и различных листов
Ссылкой
однозначно определяется ячейка или
группа ячеек листа, а также упрощается
поиск значений или данных, используемых
в формуле.
С помощью ссылок можно
использовать в формуле данные, находящиеся
в различных местах листа, а также
использовать значение одной и той же
ячейки в нескольких формулах. Можно
ссылаться на ячейки, находящиеся на
других листах книги или в другой книге,
или на данные другого приложения.
Ссылки на ячейки других книг называются внешними ссылками.
Ссылки на данные других приложений называются удаленными ссылками.
По умолчанию в Excel используются ссылки типа A1, в которых столбцы обозначаются буквами от A до IV (256 столбцов максимально), а строки числами — от 1 до 65 536). Например, D50 является ссылкой на ячейку, расположенную в пересечении столбца D с 50-й строкой. Чтобы сослаться на диапазон ячеек, введите ссылку на верхний левый угол диапазона, поставьте двоеточие (:), а затем – ссылку на правый нижний угол диапазона. В следующей таблице представлены варианты возможных ссылок.
Чтобы сослаться на: | Нужно ввести: |
Ячейку столбца A и 10-й строки | A10 |
Диапазон ячеек столбца A с 10-й строки по 20-ю | A10:A20 |
Диапазон ячеек в 15-й строке со столбца B по столбец E | B15:E15 |
Все ячейки в 5-й строке | 5:5 |
Все ячейки между 5-й и 10-й строками включительно | 5:10 |
Все ячейки в столбце H | Н:Н |
Все ячейки между столбцами H и J включительно | H:J |
В зависимости от поставленной задачи можно использовать:
относительные ссылки, которые при копировании или перемещении ячейки с формулой ссылаются на другие ячейки относительно позиции ячейки с формулой;
абсолютные
ссылки,
которые при копировании или перемещении
ячейки с формулой всегда ссылаются на
одну и ту же ячейку (ячейки).
Различия между относительными и абсолютными ссылками
При создании формулы ссылки обычно изменяются относительно расположения ячейки, содержащей формулу. Например, ячейка B6 содержит формулу =A5; искомое значение находится на одну ячейку выше и левее ячейки B6.
При копировании формулы, содержащей относительные ссылки, и вставке ее в другое место, ссылки будут указывать на другие ячейки. Например, если формула из ячейки B6 копируется в ячейку B7, то в ячейке B7 будет содержаться формула =A6, которая ссылается на одну ячейку выше и левее ячейки B7.
Другой пример, если имеется формула, умножающая содержимое ячейки A5 на ячейку C1 (=A5*C1), то при копировании формулы в любую другую ячейку изменятся обе ссылки и названия столбцов, и номера строк, т.к. изначально формула содержит относительные ссылки на ячейки А5 и С1. Такой тип ссылок называется относительным.
Если
необходимо, чтобы
ссылки не изменялись при копировании формулы в другую ячейку, нужно
использовать абсолютные ссылки.
Абсолютная ссылка бывает двух типов: частичная и полная.
Частичная абсолютная ссылка при копировании или перемещении ячейки не изменяет тот параметр адреса ячейки, перед которым стоит знак $ — это либо столбец, либо строка.
Полная абсолютная ссылка при копировании или перемещении ячейки не изменяет весь адрес ячейки, т.к. знак $ стоит и перед именем столбца, и перед номером строки.
Чтобы создать полную абсолютную ссылку на ячейку C1, впечатайте знак доллара перед именем столбца и перед номером строки в адресе ячейки, например, =A5*$C$1.
Задание. Создайте таблицу «Продажа мороженого в различных округах», используя относительные и абсолютные ссылки ячеек.
Создание таблицы
Создайте таблицу по образцу (рис. 1). Ячейки в столбце F имеют формат Процентный, а количество десятичных знаков = 0.
В
ячейке F5 формула должна содержать абсолютную ссылку на
ячейку Е9: =Е5/$E$9.
Для оформления таблицы используйте команду Формат-Автоформат. Выберите стиль оформления Простой.
Рис. 1. Таблица данных с использованием относительных и абсолютных ссылок
Как использовать абсолютную ссылку на ячейку в Excel (примеры и руководство)
Когда мы хотим зафиксировать положение выбранной ячейки в формуле, чтобы ее значение не менялось всякий раз, когда мы перемещаем ячейку или копируем формулу в другие ячейки или листы, мы используем абсолютные ссылки в Excel. Для этого знак доллара ($) следует использовать как до, так и после имени столбца выбранной ячейки. В качестве альтернативы мы можем набрать функциональную клавишу F4, которая сразу закроет алфавит столбца с долларом. Например, ячейка A1 будет отображаться как =$A$1, если ее необходимо исправить.
В Excel адрес ячейки со знаком доллара ($) называется абсолютной ссылкой на ячейку. Ссылка на столбец, ссылка на строку или обе могут стоять перед ним. В Excel мы можем поддерживать постоянную строку, столбец или и то, и другое с абсолютной ссылкой на ячейку. При переносе в дополнительные ячейки он остается неизменным.
Как использовать абсолютную ссылку на ячейку в Excel Online, 2016 и 2019
Имена некоторых сотрудников и соответствующее количество часов, которые они отработали, перечислены в данных ниже. Все получают одинаковую фиксированную почасовую оплату.
Используя пример 1
1. Теперь брутто-зарплата должна быть определена на основе количества отработанных часов.
2. Почасовая ставка заработной платы = 10 долларов. У нас фиксированная почасовая ставка 10 долларов. Теперь подсчитаем валовую заработную плату для каждого работника.
3. Как показано на снимке экрана выше, чтобы получить валовую заработную плату сотрудника Джона, мы умножим количество отработанных часов (B20) на почасовую ставку фиксированной заработной платы (B16) в ячейке C20.
Результат:
4. Как показано на скриншоте ниже, если мы перетащим эту формулу для остальных сотрудников, результаты будут неточными.
5. На скриншоте выше видно, что установленная почасовая ставка заработной платы колеблется для каждого сотрудника. Однако все работники получают одинаковую фиксированную ставку заработной платы. Ячейка B16 будет служить абсолютной ссылкой на ячейку в дальнейшем. Пожалуйста, посмотрите на изображение ниже:
6.Имени столбца и номеру строки будет предшествовать знак доллара $, как в $B$16.Результат:
все работники. Теперь, когда мы перетащим эту формулу для остальных сотрудников, она даст правильные результаты. Путем деления общего количества часов, отработанных всеми сотрудниками, на почасовую ставку для установленной заработной платы, рассчитывается валовая компенсация. Пожалуйста, посмотрите на изображение ниже:
Абсолютная ссылка в excel на примере 2
1.Ритика идет в магазин и покупает товары и косметику. Она выбирает следующие 15 предметов:
2.
К каждому предмету применяется ставка налога на товары и услуги (GST), которая составляет 10%. Мы должны вычислить налоговую ставку для каждого предмета, чтобы определить чистую сумму. Путем умножения количества каждого предмета на соответствующую цену продукта мы сначала определим общую стоимость каждого предмета.
Результат:
3. Для остальных товаров перетащите эту формулу.
4. Теперь мы должны умножить общую цену на ставку налога на товары и услуги, чтобы определить налоговую ставку для каждого продукта. Налог на товары и услуги взимается по ставке 10%. Поскольку GST фиксирован для каждого товара, в этом случае мы будем использовать абсолютную ссылку на ячейку. Следовательно, мы будем использовать клавишу F4, чтобы создать ячейку E33 в качестве абсолютной ссылки на ячейку.
Результат:
5. Перетащите эту формулу на оставшиеся объекты.
6. Общий счет можно получить, добавив общую стоимость и налог на товары и услуги.
7.Итак, судя по скриншоту ниже, результат рупий.
300.60:
Примечание. Эта статья, написанная выше, является попыткой показать вам, как использовать абсолютную ссылку на ячейку в Excel Online, 2016 и 2019, как в Windows, так и в Mac. Вам просто нужно немного понять, как и что происходит. работать, и вы хорошо идти. Обладая этими базовыми знаниями или информацией о том, как их использовать, вы также можете получить доступ и использовать различные другие параметры в Excel или электронной таблице. Кроме того, он очень похож на Word или Document. Так что, в некотором смысле, если вы изучите что-то одно, например Excel, вы сможете автоматически научиться использовать и Word, потому что оба они очень похожи во многих отношениях. Если вы хотите узнать больше о WPS Office, вы можете бесплатно скачать WPS Office для доступа к Word, Excel, PowerPoint.
Упражнения Excel — Абсолютная ссылка
Вас интересуют знаки доллара в формуле Excel? Продолжайте читать, чтобы узнать об абсолютных ссылках в Excel!
Отличительной особенностью Excel является то, что вы можете написать формулу в одной ячейке, которая ссылается на содержимое другой ячейки.
Например, предположим, что ячейка B2 (ячейка, расположенная в столбце B, строка 2) содержит текст "С днем рождения!" Вы можете написать формулу в любом месте рабочего листа, которая ссылается на ячейку B2, чтобы использовать информацию, содержащуюся в этой ячейке. Допустим, вы хотите узнать длину текста в ячейке B2, поэтому вы используете функцию ДЛСТР:
Это здорово, потому что вам нужно только написать «С Днем Рождения!» один раз на вашем листе, и вы можете ссылаться на него в других формулах столько раз, сколько хотите, не записывая текст в нескольких местах. Вместо того, чтобы печатать «С Днем Рождения!» в каждую формулу можно просто написать B2.
Сравнение абсолютных ссылок и относительных ссылок в Excel
В приведенном выше примере буква B2 — это то, что мы называем относительной ссылкой (в отличие от абсолютной ссылки), поскольку она не содержит знака доллара ($). Относительная ссылка изменится, когда формула будет скопирована или перетащена в новую ячейку.
Например, у вас есть таблица названий городов, и вам нужны первые три буквы каждого названия города.
| А | Б | |
|---|---|---|
| 1 | ИМЯ | ПЕРВЫЕ 3 БУКВЫ |
| 2 | Миннеаполис | =ВЛЕВО(A2, 3) |
| 3 | Чикаго | |
| 4 | Мэдисон | |
| 5 | Нью-Йорк |
Мы можем использовать функцию ВЛЕВО, чтобы получить первые три буквы названия каждого города.
Мы можем написать = ЛЕВЫЙ (A2, 3) в ячейке B2. В этом примере A2 является относительной ссылкой, поэтому Excel знает, что нужно ссылаться на ячейку слева от того места, где написана формула. Это удобно, потому что теперь мы можем скопировать эту формулу до конца столбца B и получить правильный ответ для каждого города.
Точно так же, как ячейка B2 ссылается на город в ячейке A2, когда мы копируем формулу в ячейку B3, формула изменится, чтобы ссылаться на город в ячейке A3, и так далее. Если бы мы скопировали эту формулу в ячейку B100, формула изменилась бы на ссылочную ячейку A100. Это полезно, когда мы хотим использовать формулу одного типа для нескольких элементов списка.
Абсолютная ссылка, с другой стороны, изменяет , а не , когда формула копируется или перетаскивается в новое место.
Абсолютная ссылка в Excel — это ссылка на ячейку со знаком доллара ($) перед буквой и/или числом в ссылке на ячейку. Например, вместо относительной ссылки A2 вы можете увидеть абсолютную ссылку $A$2, а также $A2 или A$2.
Знак доллара в абсолютной ссылке «блокирует» или «привязывает» часть ссылки (строку, столбец или и то, и другое), перед которой он стоит.
Абсолютная ссылка $A2 будет всегда ссылкой на столбец A, но строка будет меняться в зависимости от того, куда копируется ссылка.
Абсолютная ссылка A$2 будет всегда ссылкой на строку 2, но столбец будет обновляться в зависимости от того, куда скопирована ссылка.
Абсолютная ссылка $A$2 всегда будет ссылкой на ячейку A2, независимо от того, где на листе копируется формула.
Когда использовать абсолютную ссылку в Excel
Абсолютные ссылки используются, когда ячейка, на которую вы ссылаетесь, не должна обновляться в зависимости от того, где написана формула. Например, скажем, у нас есть рабочий лист, где одна конкретная ячейка содержит скорость или процент, который мы хотели бы использовать в нашей формуле. В таблице ниже мы показываем общий счет для нескольких посетителей ресторана.
Чтобы рассчитать правильную сумму чаевых для каждого счета, мы умножим сумму счета на процент чаевых, указанный в ячейке E1. Мы храним сумму чаевых в отдельной ячейке, а не пишем 0,18 в каждой формуле, поэтому, если мы решим изменить процент чаевых, нам нужно будет обновить его только в одном месте (ячейка E1), а не обновлять каждую формулу в списке.
| А | Б | С | Д | Э | |
|---|---|---|---|---|---|
| 1 | СЧЕТ | СУММА ЧАЕВЫХ | Процент чаевых: | 0,18 | |
| 2 | 24. 17 | ? | |||
| 3 | 37,98 | ||||
| 4 | 14.01 | ||||
| 5 | 88,25 |
Допустим, мы написали формулу со всеми относительными ссылками.
Мы могли бы написать =A2*E1 в ячейке B2. Это отлично сработает для первого счета, и мы умножим 24,17 на 0,18, чтобы получить правильные чаевые. Но когда мы скопируем эту формулу вниз по списку в ячейку B3, формула изменится и умножит ячейку A3 на E2, которая, конечно же, пуста.
Почему Excel делает это? Поскольку мы копируем формулу на одну строку вниз, Excel также обновляет каждую относительную ссылку на одну строку вниз, что правильно в случае A3, но не в случае E2. Мы хотим сослаться на ячейку E1 для процента чаевых независимо от того, где написана формула .
Таким образом, мы запишем =A2*$E$1 . Таким образом, мы можем скопировать формулу вниз по столбцу B, и она всегда будет ссылаться на правильный счет (поскольку это относительная ссылка) и всегда ссылаться на правильный процент чаевых (поскольку это абсолютная ссылка), из которых есть только один, расположенный в ячейке. Е1.
Блокировка различных частей абсолютной ссылки на ячейку
Мы можем написать абсолютную ссылку, такую как $A$1, которая всегда будет ссылаться на ячейку A1, независимо от того, где находится формула.
Мы говорим, что и строка, и столбец в этой абсолютной ссылке привязаны или заблокированы, потому что перед каждым из них стоит знак доллара. Мы также можем заблокировать одну часть за один раз.
Написав $A1, мы блокируем столбец ; строка изменится в зависимости от того, куда копируется формула, но она всегда будет ссылаться на столбец A.
Мы также можем написать A$1. Это блокирует строку . Эта абсолютная ссылка всегда будет ссылаться на строку 1, но столбец будет обновляться в зависимости от того, куда вставлена формула.
Например, у нас есть компания, в которой продавцы ежемесячно получают разные комиссионные. Мы хотим умножить продажи каждого продавца за данный месяц на процент комиссионных за этот месяц, чтобы найти сумму их комиссионных за месяц.
| А | Б | С | Д | Э | Ф | Г | Х | я | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | МЕСЯЦ | КОМИССИЯ % | МАЙК ПРОДАЖА | ЮЛИЯ ПРОДАЖА | АЛЕКС ПРОДАЖ | МАЙК КОМИССИЯ | ЮЛИЯ КОМИССИЯ | АЛЕКС КОМИССИЯ | |
| 2 | янв. | 0,22 | 1020 | 1850 | 1400 | ? | |||
| 3 | фев | 0,25 | 1520 | 1440 | 1500 | ||||
| 4 | марта | 0,26 | 1580 | 1630 | 1650 | ||||
| 5 | апр. | 0,28 | 1650 | 1000 | 1740 |
Мы хотим написать одну формулу, чтобы найти комиссию Майка за январь, а затем скопировать эту формулу, чтобы найти комиссию каждого за все месяцы с помощью той же формулы.
В ячейке G2 мы можем написать =$B2*C2 , чтобы рассчитать комиссию Майка. Обратите внимание, что C2 — это относительная ссылка, а $B2 — абсолютная ссылка, где заблокирован только столбец — строка обновляется в зависимости от того, где написана формула.
Если мы напишем =$B2*C2 в G2, мы умножим Продажи Майка на ставку комиссии за январь, чтобы правильно рассчитать комиссию Майка за январь.
Мы можем скопировать формулу в ячейку h3 (на один столбец справа от G2), чтобы рассчитать комиссию Юлии. Относительная ссылка изменится на D2 (на один столбец справа от C2), чтобы указать на январские продажи Джулии, но $B2 по-прежнему будет ссылаться на комиссионную ставку за январь, поскольку столбец заблокирован. Это правильно рассчитывает комиссию Юлии за январь.
Если мы скопируем исходную формулу вниз от G2 до G3 (на одну строку вниз), мы сможем рассчитать комиссию Майка за февраль. C2 меняется на C3 (на одну строку ниже), чтобы указать на февральские продажи Майка. $B2 изменится на $B3, чтобы указать комиссионную ставку за февраль, поскольку столбец заблокирован, а строка — нет.
Мы можем скопировать эту формулу в любую ячейку от G2 до I5 (и далее) и всегда рассчитывать правильную комиссию продавца за данный месяц.