Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как изменить / преобразовать абсолютную ссылку в относительную ссылку в Excel?

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

Здравствуйте, дорогие читатели. Я решил сделать это отдельной статьей, потому что многие люди не знают, что такое ссылки на ячейки в 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. Если целевая рабочая книга закрыта, ссылка становится Workbook_address[Имя_рабочей_книги]Имя_листа!Адрес_ячейки .
    2. Если имя листа или рабочей книги содержит пробелы, ссылка заключается в одинарные кавычки следующим образом: ‘[Workbook 1.xlsx]Sheet 1’!$A$1 .

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

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

    Пожалуйста, следите за блогом OfficeLegco, чтобы узнать больше! Спасибо, что читаете!

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

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

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

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

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

    Как изменить / преобразовать абсолютную ссылку в относительную ссылку в Excel?

    Модуль 2
    «Формулы»

    Ссылки. Типы ссылок(относительные, абсолютные, смешанные). Виды представления ссылок. Именованные ссылки. Формулы в Microsoft Excel. Использование текста в формулах. Использование ссылок в формулах. Операторы. Арифметические операторы. Логические операторы. Оператор объединения 2-х строк текста в одну. Операторы ссылок. Выражения.

    Ссылки

    Ссылка в Microsoft Excel — адрес ячейки или связного диапазона ячеек. В каждом листе Excel может быть 256 (2 в 8 степени) столбцов и 65536 (2 в 16 степени) строк. Адрес ячейки определяется пересечением столбца и строки, например: A1, C16. Адрес диапазона ячеек задается адресом верхней левой ячейки и нижней правой, например: A1:C5.

    Типы ссылок (типы адресации):

    В Excel ссылки подразделяются на три типа:

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

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

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

    Excel вставит относительную ссылку на ячейку после знака «=» в ячейке, если щелкнуть левой кнопкой мыши по ячейке со знаком «=». Excel пересчитывает все адреса относительных ссылок в нем каждый раз, когда мы тянем за маркер автозаполнения или копируем Формулу, содержащую относительные ссылки.

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

    Чтобы сделать относительную ссылку абсолютной, достаточно поставить знак «$» перед буквой столбца и адресом строки, например $A$1. Более быстрый способ — выделить относительную ссылку и нажать один раз клавишу «F4», при этом Excel сам проставит знак «$». Если второй раз нажать «F4», ссылка станет смешанной такого типа A$1, если третий раз — такого $A1, если в четвертый раз — ссылка опять станет относительной. И так по кругу.

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

    Смешанные ссылки являются наполовину абсолютными и наполовину относительными. Знак доллара в них стоит или перед буквой столбца или перед номером строки.
    Например, в ячейке записана формула «=A$1». Ссылка A$1 относительная по столбцу A и абсолютная по строке 1. Если мы потянем за маркер автозаполнения эту формулу вниз или вверх, то ссылки во всех скопированных форумулах будут указывать на ячейку A1, то есть будет вести себя как абсолютные. Однако, если потянем вправо или влево — ссылки будут вести себя как относительные, то есть Excel будет пересчитывать их адрес. Таким образом, формулы, созданные автозаполнением, будут использовать один и тот же номер строки ($1), но изменится номер столбца (A, B, C. ).

    Виды представления ссылок

    В Microsoft Excel есть два вида ссылок:

    • Классический;
    • Стиль ссылок R1C1 (здесь R — row (строка), C — column (столбец)).

    Включите стиль ссылок R1C1 в «Инструменты» —> «Параметры» —> вкладка «Общие» —> флажок «Стиль ссылок R1C1»

    Если включен классический вид и в ячейке с адресом A1 находится формула «=B3» (B3 — относительная ссылка), то после переключения в вид R1C1 она примет вид «=R[2]C[1]». В режиме R1C1 в квадратных скобках указывается относительное смещение. В скобках после R указано смещение в строках (row) — две строки вниз (вниз, потому что число положительное, если бы стояло «R[-2]» — было бы вверх). В скобках после C указано смещение в столбцах — 1 столбец вправо (если бы было C[-1] — влево). В общем случае направления вниз и вправо считаются положительными, вверх и влево — отрицательными.

    Если включен классический вид и в ячейке с адресом A1 находится формула «=$B$3» ($B$3 — абсолютная ссылка), то после переключения в вид R1C1 она примет вид «=R3C2». Квадратных скобок нет, то есть указано не относительно смещение по отношению к положению формула, а абсолютное смещение по отношению к всему листу.

    Именованные ссылки

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

    Чтобы создать именованную ссылку, выберите нужную ячейку или диапазон, затем щелкните в текстовом поле (см. рисунок ниже), введите имя и нажмите Enter. Можно использовать русские имена. В имени не может быть пробелов, и оно не может начинаться с цифры. Когда выделено «Ctrl», это относится к диапазону несвязанных ячеек.

    Imenovannaya ssilka.jpg

    С помощью кнопки со стрелкой вниз вставьте ссылку на имя:

    Vstavka imenovannoy ssilki 1.png

    Или нажмите клавишу «F3», откроется следующее окно:

    Vstavka imenovannoy ssilki 2.png

    Пример использования: «=СУММ(табл_1);».

    Формулы в Microsoft Excel

    С помощью Excel можно создавать программируемые электронные таблицы. Excel использует формулы для расчетов. Excel считает формулой все, что начинается со знака «=». Если в ячейку вписать «1+1», Excel не будет вычислять это выражение. В результате, если ввести «=1+1» и нажать Enter, то после вычисления формулы в ячейке появится число 2. Вы можете снова увидеть формулу, дважды щелкнув по ячейке, выделив ее и нажав F2, или нажав Ctrl+Апостроф. Когда вы снова выделите ячейку, вы также увидите ее на панели инструментов Панель формул. Дважды щелкните, нажмите F2 или щелкните в строке формул, чтобы изменить формулу. Нажмите Enter, чтобы завершить работу.

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

    Использование текста в формулах

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

    Использование ссылок в формулах

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

    Если в формуле используется несколько ссылок, то каждой из них Excel дает свой цвет. Это очень удобно. Пример: напишите в какой либо ячейке формулу «=A1+D1», нажмите Enter, затем два раза щелкнете по ячейке. В ячейке вы увидите формулу с разноцветными ссылками, а вокруг ячеек A1 и D1 будут прямоугольники соответствующих цветов. Гораздо проще найти, куда указывет ссылка, по цвету прямоугольника, чем просматривать буквы столбцов и номера строк. Наведите курсор мыши на один из разноцветных прямоугольников и перетащите левой кнопкой за границу в другое место. Вы увидите, что при этом меняются и адреса ячеек в формуле — часто это самый быстрый способ подправить адреса в формуле, особенно после копирования маркером автозаполнения.

    Операторы

    Операторы в Excel бывают бинарными и унарными. Бинарные операторы работают с 2 значениями. Например, оператор «*» умножает число слева на число справа. Если число слева или справа опущено, Excel выдает ошибку.

    Унарные операторы работают с одним значением. Примеры унарных операторов: унарный «+» (ничего не делает), унарный «-» (меняет знак числа справа) или унарный «%» (делит число слева на 100).

    Арифметические операторы
    • «+» — сложение (Пример: «=1+1»);
    • «-» — вычитание (Пример: «=1-1»);
    • «*» — умножение (Пример: «=2*3»);
    • «/» — Деление (Пример: «=1/3»);
    • «^» — Возведение в степень (Пример: «=2^10»);
    • «%» — Процент (Пример: «=3 %» — преобразуется в 0,03; «=37*8 %» — нашли 8 % от 37). То есть если мы дописываем после числа знак «%», то число делится на 100.
    Логические операторы
    • «>» — больше;
    • «<» — меньше;
    • «>=» — больше, либо равно;
    • «< <>» — неравно (проверка на неравенство).
    Оператор объединения 2-х строк текста в одну

    Оператор «&» (амперсанд) служит для «склеивания» между собой двух текстовых строк. Например, в ячейке A1 текст «мама», в ячейке A2 текст «мыла раму». В A3 пишем формулу «=A1 & A2». В результате в ячейке A3 появится текст «мамамыла раму». Как видим, пробел между двумя строками автоматически не ставится. Чтобы вставить этот пробел, нужно изменить формулу вот так: «=A1 & » » & A2».

    Операторы ссылок
    •  : (двоеточие). Ставится между ссылками на первую и последнюю ячейку диапазона. Такое сочетание является ссылкой на диапазон (A1:A15);
    •  ; (точка с запятой). Объединяет несколько ссылок в одну ссылку (СУММ(A1:A15;B1:B15));
    • (пробел). Оператор пересечения множеств. Служит для ссылки на общие ячейки двух диапазонов (B7:D7 C6:C8).

    Выражения

    Выражения в Excel бывают арифметические и логические. Арифметическое выражение (например, «=2*(2+5)», результат — 14) в результате дает числовое значение (положительное, отрицательное, дробное число). Логическое выражение (например, «=3>5», результат — логическое значение «ЛОЖЬ»)в результате может дать лишь 2 значения: «ЛОЖЬ» или «ИСТИНА» (одно число либо больше другого, либо не больше, других вариантов нет).

    Excel works!

    menu

    Например, если вы скопируете формулу в правую ячейку, все формулы также будут перемещены вправо. Это неудобно. Как закрепить формулы в нужной ячейке? Использование абсолютных и относительных ссылок в Excel. Многие знакомы с так называемыми якорями или символом $ в формулах. Но знаете ли вы, как быстро разместить $ якоря в нужном месте? Это комбинация клавиш F4

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

    Введение. Абсолютные и относительные ссылки

    Главным преимуществом Excel на заре была новая система независимого пересчета формул. Поэтому формулы и работа с ними главное преимущество этих электронных таблиц. И конечно же создатели не могли ни сделать абсолютные и относительные ссылки в этом чудо-редакторе.

    Ссылки в MS Excel — это как ссылки в интернете. Ссылкой называется адрес ячейки (например: А10, А — название столбца, 10 — название строки). Они делятся на два основных типа- абсолютные и относительные . В Excel формулы состоят из адресов и при перетаскивании нужные адреса сбиваются.

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

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

    То есть, если вы перетаскиваете формулу влево, вы должны закрепить (поставить якорь или знак $) имя столбца (букву), если вы перетаскиваете вниз, перед числом. Это особенно полезно, если вы выбираете информацию по двум критериям из большого набора данных (read =SUMMESLIMN()), например, по месяцу и городу.

    Хитрости. R1C1 в привычный формат

    Если вы хотите выбрать в формуле достаточно широкий диапазон, например A2:A10000, я предлагаю выбрать весь столбец, т.е. в формате A:A. Поэтому для всего столбца абсолютная ссылка будет выглядеть как $A:$A. Не забывайте о клавишах быстрого доступа. А еще лучше — создавайте диапазоны. Какие диапазоны? Читайте здесь 😉

    Да и еще… Если у вас и в столбцах и в строках цифры? Значит у вас включен тип ссылок R1C1, он достаточно удобен в программировании. Но его легко изменить. Жмете на круглую кнопку в верхнем левом углу, внизу меню — Параметры Excel- Формулы — снимаете галочку Стиль ссылок R1C1.

    голоса
    Рейтинг статьи
    Читайте так же:
    Как импортировать и подключить сайт в Excel?
Ссылка на основную публикацию
Adblock
detector