Как сделать абсолютную ссылку в excel на mac
Обновлено: 02.07.2024
Преимущества абсолютных ссылок сложно недооценить. Их часто приходится использовать в процессе работы с программой. Относительные ссылки на ячейки в 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 для выбора нужного типа: абсолютный или смешанный. Это быстро и удобно.
Вы работаете с Excel и вам нужно создать абсолютную ссылку? Мы поможем вам в этом!
Абсолютная ссылка — это часть формулы, которую нельзя изменять. В нашем примере мы привели небольшую таблицу с числовыми данными. В столбцах приведены данные, которые нужно суммировать. А сумма в каждом случае должна быть умножена на число, находящееся в ячейке А1.
То есть нам нужно создать абсолютную ссылку. Формула для расчетов представлена в строке ввода. Создайте абсолютную ссылку, установив курсор перед А1. Теперь нажмите клавишу F4. Перед А и 1 теперь должен появиться значок доллара $, что означает, что данные этой ячейки останутся неизменными. Эту формулу можно будет копировать в другие ячейки.
Абсолютная ссылка в формуле Excel — это, как уже показывает название — неизменная часть формулы. Вы можете с ее помощью «зафиксировать» формулы в ячейке, чтобы не допустить их изменения. Как создать абсолютную ссылку в электронной таблице Excel, объясняется в этой статье.
Создайте формулу Excel с абсолютными ссылками
Абсолютные ссылки всегда полезны, если в формуле Excel есть одно или несколько значений, которые должны применяться ко всем формулам в определенной области. Например, вы добавляете числа из нескольких строк и столбцов и хотите, чтобы каждая сумма умножалась на значение данной ячейки. Для наглядности рассмотрим это на примере таблицы.
Сначала мы выбираем ячейку Excel, которая должна содержать нашу абсолютную ссылку. В нашем случае ячейка A1, в которую мы вводим номер 2. В ячейки от A3-A5 до C3-C5 введите любые цифры. Затем нажмите на ячейку D3, перейдите в панель формул и введите знак равенства.
Затем щелкните ячейку таблицы A3, поместите знак плюса после A3 в формулу, щелкните ячейку D3, снова добавьте знак плюса и щелкните ячейку C3. За C3 введите знак умножения, а затем нажмите ячейку с нашей абсолютной ссылкой — ячейку A1.
Так ячейка MS Excel становится абсолютной ссылкой
Поскольку мы просто хотим использовать только что созданную формулу для других строк позже, мы должны что-то предпринять, чтобы ячейка таблицы A1 оставалась в формуле.
Сначала переместите курсор перед A1 в строке редактирования. Затем нажмите клавишу [F4]. А и 1 теперь представляют собой набор знаков внутри долларов. Это означает, что значение этой ячейки Excel всегда принимается без изменений.
Знак доллара позволяет легко увидеть на первый взгляд, что вы имеете дело с абсолютной ссылкой. Наконец, если вы перетащите формулу в ячейки таблицы D4 и D5, вы заметите, что соответствующие ссылки на ячейки изменяются — кроме ячейки A1, которая остается постоянной.
Если у вас более сложные вычисления и вы хотите установить несколько функций в ячейке Excel одновременно, вы также можете это сделать.
Ссылка на ячейку в программах для работы с электронными таблицами, таких как Excel и Google Sheets, определяет местоположение ячейки на листе.
Ячейка — это одна из коробчатых структур, заполняющих лист, и вы можете найти ее по ее ссылкам, например, A1, F26 или W345. Ссылка на ячейку состоит из буквы столбца и номера строки, которые пересекаются в местоположении ячейки. При перечислении ссылки на ячейку буква столбца всегда появляется первой.
Ссылки на ячейки отображаются в формулах , функциях, диаграммах и других командах Excel.
Информация в этой статье относится к версиям Excel 2019, 2016, 2013, Excel для Mac, Excel Online и Google Sheets.
Использование ссылки на ячейку включает автоматическое обновление
Одно из преимуществ использования ссылок на ячейки в формулах электронной таблицы состоит в том, что, как правило, если данные, расположенные в ссылочных ячейках, изменяются, формула или диаграмма автоматически обновляются, чтобы отразить это изменение.
Если рабочая книга настроена на автоматическое обновление при внесении изменений в рабочую таблицу, вы можете выполнить обновление вручную, нажав клавишу F9 на клавиатуре.
Ссылка на ячейки из разных листов
Ссылки на ячейки не ограничиваются тем же рабочим листом, где расположены данные. Другие листы в том же файле могут ссылаться друг на друга, включая нотацию, которая сообщает программе, из какого листа вытащить ячейку.
Вам не нужна нотация листа, если вы ссылаетесь на ячейку в том же листе.
Аналогично, когда вы ссылаетесь на данные в другой рабочей книге , имя рабочей книги и рабочий лист включаются в ссылку вместе с расположением ячейки.
Чтобы сослаться на ячейку на другом листе, перед ссылкой на ячейку укажите «Sheet [число]» с восклицательным знаком после нее, а затем имя ячейки. Поэтому, если вы хотите получить информацию из ячейки A1 на листе 3, вы наберете « Sheet3! A1 ».
Запись, ссылающаяся на другую книгу в Excel, также содержит название книги в скобках. Чтобы использовать информацию, содержащуюся в ячейке B2 в Листе 2 Рабочей книги 2, введите « [Book2] Sheet2! B2 ».
Сотовый диапазон
Хотя ссылки часто относятся к отдельным ячейкам, таким как A1, они также могут относиться к группе или диапазону ячеек. Вы определяете диапазоны ячеек по начальной и конечной ячейкам. В случае диапазонов, которые занимают несколько строк и столбцов, вы будете использовать ссылки на ячейки ячеек в верхнем левом и нижнем правом углах диапазона.
Разделите пределы диапазона ячеек двоеточием (:), которое указывает Excel или Google Sheets включить все ячейки между этими начальными и конечными точками. Таким образом, чтобы перехватить все между ячейками A1 и D10, вы должны ввести « A1: D10 ».
Чтобы захватить всю строку или столбец, вы по-прежнему используете обозначение диапазона ячеек, но вы используете только номера столбцов или буквы строк. Чтобы включить все в столбец A, диапазон будет « A: A ». Чтобы использовать строку 8, вы наберете « 8: 8 ». Для всего, что в столбцах с B по D, вы наберете « B: D ».
Относительные, абсолютные и смешанные ссылки на ячейки
Три типа ссылок, которые можно использовать в Excel и Google Sheets, легко определяются по наличию или отсутствию знаков доллара ($) в ссылке на ячейку. Знак доллара говорит программе использовать это значение при каждом запуске формулы.
- Относительные ссылки на ячейки не содержат знаков доллара (например, A1).
- Ссылки на смешанные ячейки имеют знаки доллара, прикрепленные либо к букве, либо к номеру в ссылке, но не к обоим (т. Е. $ A1 или A $ 1).
- Абсолютные ссылки на ячейки имеют знаки доллара, прикрепленные к каждой букве или номеру в ссылке (т.е. $ A $ 1).
Обычно вы используете абсолютную или смешанную ссылку на ячейку, если настраиваете формулу. Например, если у вас есть число в ячейке A1, больше чисел в столбце B, а столбец C содержит суммы A1 и каждое из значений в B, вы будете использовать «$ A $ 1» в формуле SUM, чтобы при Вы заполняете автоматически, программа знает, что всегда использовать число в A1 вместо пустых ячеек под ним.
Копирование формул и ссылок на разные ячейки
Другое преимущество использования ссылок на ячейки в формулах состоит в том, что они облегчают копирование формул из одного местоположения в другое на рабочем листе или в рабочей книге.
Относительные ссылки на ячейки изменяются при копировании, чтобы отразить новое расположение формулы. Относительное имя происходит из-за того, что при копировании они меняются относительно своего местоположения. Обычно это хорошая вещь, и именно поэтому относительные ссылки на ячейки являются типом ссылки по умолчанию, используемым в формулах.
Иногда ссылки на ячейки должны оставаться неизменными при копировании формул. Копирование формул является еще одним основным использованием абсолютной ссылки, такой как = $ A $ 2 + $ A $ 4. Значения в этих ссылках не изменяются при их копировании.
В других случаях может потребоваться изменить часть ссылки на ячейку, например букву столбца, при этом номер строки остается неизменным или наоборот при копировании формулы. В этом случае вы будете использовать ссылку на смешанную ячейку, например = $ A2 + A $ 4. К какой бы части ссылки не был прикреплен знак доллара, она остается неизменной, а другая часть изменяется при копировании.
Таким образом, для $ A2 при копировании буква столбца всегда A, но номера строк изменяются на $ A3, $ A4, $ A5 и т. Д.
Решение использовать различные ссылки на ячейки при создании формулы основывается на расположении данных, которые будут использоваться в скопированных формулах.
Переключение между типами ссылок на ячейки
Самый простой способ изменить ссылки на ячейки с относительных на абсолютные или смешанные — это нажать клавишу F4 на клавиатуре. Чтобы изменить существующие ссылки на ячейки, Excel должен быть в режиме редактирования , который вы вводите, дважды щелкнув по ячейке указателем мыши или нажав клавишу F2 на клавиатуре.
Чтобы преобразовать относительные ссылки на ячейки в абсолютные или смешанные ссылки на ячейки:
Применение абсолютной адресации
Абсолютная адресация нам может понадобиться, например, в том случае, когда мы копируем формулу, одна часть которой состоит из переменной, отображаемой в ряду чисел, а вторая имеет постоянное значение. То есть, данное число играет роль неизменного коэффициента, с которым нужно провести определенную операцию (умножение, деление и т.д.) всему ряду переменных чисел.
В Excel существует два способа задать фиксированную адресацию: путем формирования абсолютной ссылки и с помощью функции ДВССЫЛ. Давайте рассмотрим каждый из указанных способов подробно.
Способ 1: абсолютная ссылка
Безусловно, самым известным и часто применяемым способом создать абсолютную адресацию является применение абсолютных ссылок. Абсолютные ссылки имеют отличие не только функциональное, но и синтаксическое. Относительный адрес имеет такой синтаксис:
У фиксированного адреса перед значением координат устанавливается знак доллара:
Существует и более быстрый способ. Нужно установить курсор в ячейку, в которой находится адрес, и щелкнуть по функциональной клавише F4. После этого знак доллара моментально появится одновременно перед координатами по горизонтали и вертикали данного адреса.
Теперь давайте рассмотрим, как применяется на практике абсолютная адресация путем использования абсолютных ссылок.
Возьмем таблицу, в которой рассчитывается заработная плата работников. Расчет производится путем умножения величины их личного оклада на фиксированный коэффициент, который одинаков для всех сотрудников. Сам коэффициент расположен в отдельной ячейке листа. Перед нами стоит задача рассчитать заработную плату всех работников максимально быстрым способом.
-
Итак, в первую ячейку столбца «Заработная плата» вводим формулу умножения ставки соответствующего работника на коэффициент. В нашем случае эта формула имеет такой вид:
В Экселе также применяется, так называемая смешанная адресация. В этом случае в адресе элемента фиксируется либо столбец, либо строка. Достигается это таким образом, что знак доллара ставится только перед одним из координат адреса. Вот пример типичной смешанной ссылки:
Этот адрес тоже считается смешанным:
То есть, абсолютная адресация в смешанной ссылке используется только для одного из значений координат из двух.
Посмотрим, как такую смешанную ссылку можно применить на практике на примере все той же таблицы заработной платы сотрудников предприятия.
-
Как видим, ранее мы сделали так, что все координаты второго множителя имеют абсолютную адресацию. Но давайте разберемся, обязательно ли в этом случае оба значения должны быть фиксированными? Как видим, при копировании происходит смещение по вертикали, а по горизонтали координаты остаются неизменными. Поэтому вполне можно применить абсолютную адресацию только к координатам строки, а координаты столбца оставить такими, каковыми они являются по умолчанию – относительными.
Выделяем первый элемент столбца «Заработная плата» и в строке формул выполняем вышеуказанную манипуляцию. Получаем формулу следующего вида:
Способ 2: функция ДВССЫЛ
Вторым способом организовать абсолютную адресацию в таблице Excel является применение оператора ДВССЫЛ. Указанная функция относится к группе встроенных операторов «Ссылки и массивы». Её задачей является формирование ссылки на указанную ячейку с выводом результата в тот элемент листа, в котором находится сам оператор. При этом ссылка прикрепляется к координатам ещё крепче, чем при использовании знака доллара. Поэтому иногда принято называть ссылки с использованием ДВССЫЛ «суперабсолютными». Этот оператор имеет следующий синтаксис:
Функция имеет в наличии два аргумента, первый из которых имеет обязательный статус, а второй – нет.
Аргумент «Ссылка на ячейку» является ссылкой на элемент листа Excel в текстовом виде. То есть, это обычная ссылка, но заключенная в кавычки. Именно это и позволяет обеспечить свойства абсолютной адресации.
Взглянем, как будет работать абсолютная адресация, организованная при помощи функции ДВССЫЛ, на примере нашей таблицы заработной платы.
-
Производим выделение первого элемента столбца «Заработная плата». Ставим знак «=». Как помним, первый множитель в указанной формуле вычисления зарплаты должен быть представлен относительным адресом. Поэтому просто кликаем на ячейку, содержащую соответствующее значение оклада (C4). Вслед за тем, как её адрес отобразился в элементе для вывода результата, жмем на кнопку «умножить» (*) на клавиатуре. Затем нам нужно перейти к использованию оператора ДВССЫЛ. Выполняем щелчок по иконке «Вставить функцию».
Ставим курсор в поле «Ссылка на ячейку». Просто кликаем по тому элементу листа, в котором находится коэффициент для расчета зарплаты (G3). Адрес тут же появится в поле окна аргументов. Если бы мы имели дело с обычной функцией, то на этом введение адреса можно было бы считать завершенным, но мы используем функцию ДВССЫЛ. Как мы помним, адреса в ней должны иметь вид текста. Поэтому оборачиваем координаты, которые расположись в поле окна, кавычками.
Абсолютную адресацию в таблицах Excel можно обеспечить двумя способами: использование функции ДВССЫЛ и применение абсолютных ссылок. При этом функция обеспечивает более жесткую привязку к адресу. Частично абсолютную адресацию можно также применять при использовании смешанных ссылок.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Читайте также: