Относительная ссылка в excel пример: Относительные, абсолютные и смешанные ссылки на ячейки в Excel

Содержание

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

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

​Смотрите также​ предыдущем примере мы​Сотрудник​ – месячная надбавка​=B3*100/40​ введем следующую формулу​Рассмотрим простой пример. Создадим​Автор: Антон Андронов​ Для решения этой​$H$3​ на ячейки​ формулы станет В2=$А2*В$1.​ «ссылку» и нажмите​ соответственно.​ при переносе и​Вы можете дважды щелкнуть​ столбцам.​Относительные ссылки в Excel​

​ вполне могли бы​Оклад​ к окладу, одинаковая​=B4*100/40​ (основываясь на сведениях,​ небольшой набор данных,​В любом, даже базовом​ проблемы поместите символ​.​B2​ Таким образом, при​

​ F4. Либо вручную​Это и называется относительной​ копировании формул нам​ по заполненным ячейкам,​

Относительная ссылка – что это?

​В следующем примере мы​ позволяют значительно упростить​ заменить формулу =(B2+$G$3)*100/40​Зарплата​ для всех сотрудников:​=B5*100/40​ что оклад составляет​ состоящий из двух​​ пакете «Майкрософт Офис»​​ доллара ($) перед​Теперь протяните эту формулу​и​​ перетаскивании формулы мы​

​ напечатайте символы «доллара». ​ адресацией. Пример кажется​ частенько необходимо сохранить​ чтобы проверить правильность​ создадим выражение, которое​ жизнь, даже обычному​

Создание и копирование формул с относительными ссылками

​ на =(B2+G$3)*100/40 и​Абрамов А. А.​F​=B6*100/40​ 40 % от​ столбцов: «Сотрудник» и​ содержится мощный табличный​ номером строки –​ в другие ячейки.​С2​ получим ряд значений​Это последний тип, встречающийся​ простым и возникает​ либо, наоборот, заменить​ своих формул. Относительные​ будет умножать стоимость​

  1. ​ рядовому пользователю. Используя​ получить тот же​4100​G​
  2. ​=B7*100/40​ зарплаты): =B2*100/40. Здесь​ «Оклад»:​ редактор Excel («Эксель»).​​B$6​​Во всех формулах ссылка​
  3. ​. Обе ссылки являются​​ в каждой из​​ в формулах. Как​ естественный вопрос, а​ данные.​ ссылки должны быть​
  4. ​ каждой позиции в​ относительные ссылки в​ самый результат, ведь​11175​2​Как видим, когда мы​
  5. ​ В2 – это​А​ Список доступных в​. ​ на ячейку​ относительными.​
  6. ​ оставшихся ячеек:​ понятно из названия,​ не проще ли​Данный вид встречается наиболее​ разными для каждой​ меню на количество.​

​ своих вычислениях, Вы​ мы выполняли копирование​=(B2+$G$3)*100/40​Месяц​ протянули формулу вниз​ оклад первого сотрудника​В​ нем функций поистине​

​В результате формула должна​Н3​Выделите ячейку​В3=$А3*В$1​ это абсолютная и​ заново написать формулу?​ часто. Если вы​ ячейки, в зависимости​

​ Вместо того чтобы​

office-guru.ru>

Абсолютная и относительная ссылка в Excel. Как использовать?

​ можете буквально за​ формулы по вертикали,​Демидова М. П.​Надбавка​ по вертикали, имя​ в таблице. Нажмем​1​ впечатляет: начиная от​ принять такой вид:​ остается фиксированной. В результате​D2​С2=$А2*С$1​ относительная ссылка одновременно.​ Нет, в больших​ не ходили на​ от строки.​ создавать новую формулу​ несколько секунд выполнить​ и номер столбца​3750​3​

Определение

​ столбца осталось без​ Enter и подведем​Сотрудник​ возможности сортировки и​=$B2*(1-B$6)​ длина и ширина​, зажмите её нижний​С3=$А3*С$1​ Несложно догадаться, что​ таблицах и объёмных​ специальные курсы, вас​В Excel Вы также​ для каждой строки,​ работу, на которую,​ в любом случае​10300​Январь​ изменения (В), а​

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

​ указатель мыши к​Оклад​ фильтрации данных и​Примечание:​ в дюймах рассчитывается​ правый угол и​Как видите, абсолютная и​ на практике она​ вычислениях, в которых​ никто не обучал,​

​ можете создавать ссылки​ мы можем создать​ в противном случае,​ остался бы неизменным. ​=(B3+$G$3)*100/40​370​ вот номер строчки​ правому нижнему краю​2​ заканчивая возможностью построения​Мы намеренно не​

  • ​ правильно.​ протяните его вниз​ относительная ссылка здесь​ обозначается, как $А1​ могут использоваться самые​ то вы вряд​ между листами внутри​ всего одну, а​ понадобились бы часы.​ И это не​Закирова Е. М.​4​ последовательно изменился. Аналогично,​ ячейки С2, дожидаясь,​Абрамов А. А.​ на их основе​ помещаем символ доллара​Иногда нужно сочетание относительной​ до ячейки​ бы не помогли.​ или А$1. Таким​ разные функции и​ ли встречались с​ документа. Более подробно​ затем скопировать ее​ В данном уроке​

​ говоря уж о​8410​Соответственно, надо изменить и​ копируя формулу по​ пока указатель не​4100​ сводных диаграмм. И​ ($) перед номером​ и абсолютной ссылки​D5​ Поэтому при сложных​ образом, это позволяет​ операторы намного надежнее​ абсолютными ссылками. Поэтому​ об этом читайте​

Абсолютная ссылка

​ в другие строки.​ Вы узнаете все​ ситуациях, когда действительно​21950​ ячейку с расчетом​ горизонтали, мы получим​ примет форму тонкого​3​ практически невозможно представить​

  • ​ строки в ссылке​ (смешанная ссылка). ​.​ расчетах необходимо использовать​ сохранять адресацию на​ просто скопировать и​ мы начнем с​ в уроке Ссылки​ Для этого воспользуемся​ об абсолютных ссылках,​ надо зафиксировать только​=(B4+$G$3)*100/40​ зарплаты С2, теперь​ неизменное значение строки​ черного крестика. Удерживая​Демидова М. П.​ ни один хранимый​ на ячейку​Посмотрите на формулу в​Обратите внимание! Ячейка​ исключительно смешанный тип.​ столбец или строку,​ перенести формулу, чем​

​ того, что проще.​ на другие листы​ относительными ссылками, чтобы​ а также научитесь​ номер строки или​Игумнова Т. Л.​

Смешанная

​ она будет содержать​ при изменяющемся номере​ нажатой левую клавишу​3750​ в редакторе набор​В2​ ячейке​D3​ Это позволит безболезненно​ но продолжать «скользить»​ заново составлять выражение.​Абсолютные и относительные ссылки​ в Excel.​ правильно вычислить сумму​ применять их при​

​ столбца, а остальное​ ​3750​ ​ следующую формулу:​
​ столбца. Поэтому и​ ​ мыши, протянем формулу​ ​4​
​ данных без вычисляемых​ ​. Таким образом ссылка​
​F2​ ​ссылается на ячейки​

​ масштабировать таблицу, добавлять​ по нему. Рассмотрим​Как уже говорилось, абсолютные​ в формулах служат​Автор: Антон Андронов​ по каждому элементу.​ решении задач в​ оставить доступным для​10300​Зарплата​ ссылка называется «относительная»​ вниз до конца​Закирова Е. М.​ посредством формул строк​ может изменяться с​

​.​B3​ в неё строки​ пример.​ и относительные ссылки​ для работы с​Даже если вы недавно​Выделите ячейку, которая будет​ Microsoft Excel.​ изменения.​=(B5+$G$3)*100/40​=(B2+G3)*100/40​ — копии первой​ таблицы, то есть​

  • ​8410​
  • ​ и столбцов.​
  • ​В2​

​Необходимо скопировать эту формулу​и​ и столбцы. Единственное,​А​ в Excel служат​ адресами ячеек, но​ начали работать с​ содержать формулу. В​В Excel существует два​Интересно будет узнать, что​Итан П. Н.​Когда мы нажмем Enter,​ введенной формулы будут​ до ячейки С7​5​Любая формула – это​на​ в другие ячейки.​C3​ при работе с​

​В​ различным целям. Второй​ в противоположных целях.​ программой Microsoft Excel,​ нашем примере мы​

​ типа ссылок: относительные​

fb.ru>

Ссылки на ячейки в Excel

  • ​ абсолютная ссылка в​
  • ​4100​
  • ​ то увидим, что​

​ изменять ссылку относительно​ (можно заменить данное​Игумнова Т. Л.​ команда, содержащая указание,​B3​ Скопируйте формулу из​. Ячейка​

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

​ такими типами адресации​С​ тип ссылок, рассматриваемый​ Рассматриваемый нами вид​​ то, наверное, знаете,​​ выбрали ячейку D2.​ и абсолютные. Относительные​​ Excel может быть​​11175​​ в данной ячейке​​ своего положения в​ действие двойным кликом​

  1. ​3750​​ какие действия произвести​​при копировании формулы​ ячейки​D4​ всегда надо быть​​1​​ нами сегодня, призван​

  2. ​ является базовым и​​ что одним из​​Введите выражение для вычисления​​ и абсолютные ссылки​​ задана не только​​=(B6+$G$3)*100/40​​ зарплата правильно пересчиталась.​ диапазоне ячеек листа.​​ мыши по правому​​6​​ с той или​​ вниз. По тем​
    ​F2​​ссылается на ячейки​​ очень внимательными, поскольку,​​3,5​​ сохранить адресацию при​​ не сохраняет адреса​​ её преимуществ является​ необходимого значения. В​​ ведут себя по-разному​​ самостоятельным указанием знака​Кремлев О. П.​

Абсолютная ссылка

​ А вот когда​Как видим, разобраться с​​ нижнему краю ячейки).​​Итан П. Н.​

  1. ​ иной ячейкой. И​ же самым соображениям​​в ячейку​​B4​ допустив ошибку один​4,5​ любых изменениях и​ ячеек в формуле​ удобная работа с​ нашем примере, мы​​ при копировании и​​ доллара перед номером​

  2. ​9200​ мы протянем формулу​

  3. ​ тем, что такое​ Столбец автоматически заполнится​​4100​​ когда пользователь сталкивается​ мы не помещаем​G2​и​

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

​ раз в таблице​2​ перемещениях формулы.

  1. ​ при её переносе.​ формулами и таблицами.​​ введем​​ заполнении других ячеек.​

  2. ​ строки и/или столбца.​23925​ на всех сотрудников,​ относительная ссылка в​​ данными:​​7​​ с необходимостью скопировать,​​ символ доллара ($)​и посмотрите что​

  3. ​C4​ с использованной смешанной​70​Допустим, вам надо решить​​ Рассмотрим пример.​​ Поэтому сегодня мы​=B2*C2​ Относительные ссылки изменяются​ «Эксель» позволяет путем​=(B7+$G$3)*100/40​ то у них​​ Excel, совсем не​​Сотрудник​
  4. ​Кремлев О. П.​ «протянуть» формулу на​​ перед буквой столбца​​ получилось:​​. Ячейка​​ ссылкой, потом найти​3​ какую-нибудь физическую задачку.​Пусть нам даны ячейки​​ с вами рассмотрим​​.​ при копировании, а​ нажатия клавиши F4​Рядом с понятием «абсолютная​ зарплата не пересчитается,​
    ​ трудно.​
    ​Оклад​
  5. ​9200​ всю строку или​

    ​ в ссылке на​

​Видите, что произошло? Ссылка​​D5​ недочет будет очень​80​ У вас имеется​ с данными (столбцы)​​ адресование в таких​​Нажмите​ абсолютные, напротив, остаются​​ после указания адреса​​ ссылка» в Excel​​ ведь используемая относительная​​Перейдем к рассмотрению следующих​Зарплата​В параметрах «Экселя» зададим​ столбец, то понимает,​ ячейку​ на цену (столбец​ссылается на ячейки​ непросто.​Итак, у нас имеется​​ некая константа, записанная​ — М1, М2,​​ таблицах и узнаем,​​Enter​​ неизменными.​​ ячейки выбрать вид​​ всегда идет понятие​​ ссылка попыталась взять​ видов ссылок.​

​Абрамов А. А.​ стиль ссылок А1​ что в некоторых​В6​​ Price) должна быть​​B5​​На этом всё. Надеемся,​​ таблица с некоторыми​

​ в ячейке А1.​ М3, Н1, Н2,​
​ что такое относительные,​
​на клавиатуре. Формула​

​Более подробно об абсолютных​

office-guru.ru>

Абсолютная ссылка в Excel — описание, примеры.

​ ссылок – при​ ссылки смешанной.​ значения из G4..G8,​Абсолютная ссылка в Excel​4100​ — такая адресация​ случаях часть формулы​. Таким образом ссылка​ абсолютной по столбцу​и​ теперь вам стало​ данными. В столбце​ Конечно, можно было​ Н3. Под ними​ абсолютные и смешанные​ будет вычислена, а​ ссылках в Excel​ первом нажатии ссылка​

Формулы в «Эксель»

​Рассмотрим этот тип выражений. ​ где абсолютно ничего​ – следующий распространенный​10250​ удобна и понятна​ или формула целиком​ может изменяться с​B​C5​ лучше понятно, что​ «А» у нас​ бы каждый раз​ в ячейке М4​ ссылки. Их работа​ результат отобразится в​ Вы можете прочитать​ с относительной изменится​ Смешанная ссылка –​ нет. Дабы избежать​ вид ссылок. В​

Ссылка относительная

​Демидова М. П.​ большинству пользователей. Здесь​ должна остаться неизменной.​В6​и относительной по​. Другими словами, каждая​ такое абсолютная и​ имеется некая величина,​ писать это число​ мы пишем формулу,​ достаточно проста, поэтому​ ячейке.​

​ в данном уроке.​ на абсолютную, при​ это ссылка, у​ подобной ситуации, необходимо,​ этом случае при​

​3750​

​ А, В, С​

​ Так пользователь узнает,​

​на​

​ строке. Для решения​

​ рассматриваемая ячейка столбца​

​ относительная ссылка.​

​ а в строке​

​ вручную, однако проще​

​ результатом которой будет​

​ проще всего будет​

​Найдите маркер автозаполнения в​

​По умолчанию, все ссылки​

​ втором – на​

​ которой при копировании​

​ чтобы использовалась абсолютная​

​ копировании формулы фиксируются​

​9375​

​ – имена столбцов,​

​ что есть в​

​С6​

​ этой проблемы поместите​

​D​

​Автор: Антон Кисленков​ «1», коэффициент, на​ записать её в​ сумма трёх чисел​ рассмотреть их на​ правом нижнем углу​ в Excel являются​ смешанную с фиксированным​ изменяется номер столбца​ ссылка в Excel. ​ строка и столбец,​Закирова Е. М.​ а строки пронумерованы.​ Excel абсолютные и​и​ символ доллара ($)​

​ссылается на две​Относительная ссылка​ который необходимо умножить.​ одно определённое место.​ (М4=М1+М2+М3). Вроде всё​ практике. Так что​ рассматриваемой ячейки. В​ относительными. При копировании​ номером строки, при​ при неизменном номере​ Чтобы зафиксировать столбец​ на которые идет​8410​ Таким образом, у​ относительные ссылки. Рассмотрим​D6​ перед буквой столбца​ соседние ячейки слева.​Абсолютная ссылка​ Ячейки В2, С3​ Также нам даны​ просто. Теперь нам​ такое абсолютная и​ данном примере мы​ формул, они изменяются​ третьем – на​ строки или наоборот.​ или строку, которые​ ссылка в формуле.​21025​ ячейки с данными​ эти понятия более​при копировании формулы​

​ –​

​Посмотрите на формулу в​

​Смешанная ссылка​

​ будут содержать результаты.

​ два столбца данных​

​ необходимо посчитать содержимое​

​ относительная ссылка?​

​ ищем маркер автозаполнения​

​ на основании относительного​

​ смешанную с фиксированным​

​ При этом знак​

​ изменяться при копировании​

​Конечно, сама по себе​

​Игумнова Т. Л.​

​ «Закирова Е. М.»​

​ детально.​

​ вправо.​

​$B2​

​ ячейке​

​Ссылки на ячейки в​

​ Как видите абсолютная​

​ (М1,М2,Р1,Р2). В ячейках​ ячейки Н4. Она​

​Ссылки в Microsoft Excel​

​ в ячейке D2.​

​ расположения строк и​

​ номером столбца, ну​

​ доллара стоит соответственно​

​ формулы не должны,​

​ абсолютная ссылка в​

​3750​ адрес — А4.​Запомнить, что такое относительная​Теперь, если мы скопируем​.​E3​ Excel очень важны.​ и относительная ссылка​ М3 и Р3​ также является суммой​ — это не​Нажмите и, удерживая левую​ столбцов. Например, если​ а при следующем​ перед номером строки​ необходимо поставить соответственно​ одиночку не используется,​9375​

​ Это небольшое отступление​ ссылка в Excel,​ эту формулу в​При копировании формулы из​.​

​ Поймите разницу между​ тут бессильны. В​

Ссылка абсолютная

​ необходимо посчитать следующую​ тех данных, которые​ то же самое,​ кнопку мыши, перетащите​ Вы скопируете формулу​ нажатии ссылка снова​ (А$1) либо перед​ возле имени столбца​

​ ведь копировать ее​Итан П. Н.​ понадобится, когда станем​ проще простого. Этот​ другие ячейки, ссылки​ ячейки​Для создания абсолютной ссылки​ относительной, абсолютной и​ таких случаях необходимо​ формулу: М3=(М1+М2)*$А$1. Теперь​ находятся над ней.​

​ что и ссылки​ маркер автозаполнения по​=A1+B1​ примет вид относительной.​ номером столбца ($А1)​

​ или номера строки​

​ особого смысла нет.​

​4100​

​ разбираться со ссылками​

​ вид ссылки выбирается​

​ на столбец​

​F2​

​ на ячейку​

​ смешанной ссылками, и​

​ использовать смешанный тип. ​ при перетаскивании и​ Перетягиванием или просто​ в интернете. Ссылкой​ необходимым ячейкам. В​

​из строки 1​

​ Менять вид ссылки​

​ – то есть​ знак доллара ($).​ Поэтому данный тип​10250​ в формулах.​ по умолчанию и​B​в​h4​ вы на пути​Запишем формулу в первую​ копировании в ячейку​ копированием ячейки М4,​ называется адрес ячейки​ нашем случае это​ в строку 2,​ в Excel таким​ перед тем элементом,​Изменим нашу формулу на​ распространен в комбинированных​Кремлев О. П.​Теперь представим, что мы​ меняет свое значение​и строку​F3​

​, поместите символ доллара​ к успеху.​

​ ячейку В2=А2*В1. Но​

​ Р3 получится следующий​

​ мы переносим формулу​ (например: А1, В10).​ диапазон D3:D12.​

​ формула превратится в​

​ образом очень удобно,​

​ который изменяться не​

​ следующую:​

​ формулах, где часть​

​9200​

​ хотим рассчитать на​

​ при протягивании (копировании)​

​6​

​уже ссылка на​

​ ($) перед буквой​

​По умолчанию Excel использует​

​ чтобы перетащить её​

​ результат Р3=(Р1+Р2)*$А$1. То​

​ в Н4. В​

​ Они делятся на​

​Отпустите кнопку мыши. Формула​

​=A2+B2​

​ ведь при этом​

​ будет.​

​Зарплата​

​ их – абсолютные​

​23000​

​ основе данных по​

​ формулы в соседние​

​останутся фиксированными.​

​ скидку (строка Reduction)​

​ столбца и перед​ относительные ссылки. Посмотрите​ вправо, нужно закрепить​ есть адрес используемой​

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

​ результате все ссылки​ два основных вида​ будет скопирована в​. Относительные ссылки особенно​ нет необходимости прибегать​Ссылка смешанная применяется намного​=(B2+$G$3)*100/40​ ссылки, остальные являются​Формулы данных ячеек будут​ окладу заработную плату​ ячейки независимо от​Урок подготовлен для Вас​ должна быть абсолютной​ номером строки. У​ на формулу в​ столбец «А», а​

​ ячейки не сдвинется.​ в ней (М1,​ — абсолютные и​ выбранные ячейки с​ удобны, когда необходимо​ к смене раскладки​ чаще, чем истинная​И когда мы ее​ относительными.​ следующими:​ каждого сотрудника. Поставим​ того, выполняется копирование​ командой сайта office-guru.ru​ по строке​ вас должна получится​ ячейке​ чтобы вниз, закрепить​Чтобы адрес ссылки сделать​ М2, М3) будут​ относительные ссылки. В​ относительными ссылками, и​ продублировать тот же​ на клавиатуре.​

Интересный факт

​ абсолютная ссылка. Например,​ скопируем, вся зарплата​Введем для примера еще​Зарплата​ в ячейку С2​ вдоль столбцов или​Источник: http://www.excel-easy.com/functions/cell-references.html​6​ вот такая ссылка​D2​ строку «1». Поэтому​ абсолютным, нажмите на​ заменены на Н1,​ Excel адресация происходит​ в каждой будут​ самый расчет по​Автор: Елена Измайлова​ даже в простом​ сотрудников пересчитается:​ один набор данных​=B2*100/40​ знак равенства и​ строк. ​Перевела: Ольга Гелих​ и относительной по столбцу.​ –​. Она ссылается (указывает)​ правильным вариантом написания​ формулу, выделите необходимую​ Н2 и Н3,​ по ячейкам, поэтому​

​ вычислены значения.​

fb.ru>

​ нескольким строкам или​

Относительные и абсолютные ссылки – как создать и изменить

В руководстве объясняется, что такое адрес ячейки, как правильно записывать абсолютные и относительные ссылки в Excel, как ссылаться на ячейку на другом листе и многое другое.

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

Что такое ссылка на ячейку?

Рабочий лист в Excel состоит из ячеек. На каждую из них можно ссылаться, указав значение строки и значение столбца. Зачем это нужно? Чтобы получить значение, записанное в ней, и затем использовать его в вычислениях.

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

Например, A1 относится к адресу на пересечении столбца A и строки 1; B2 относится ко второй ячейке в столбце B и так далее.

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

Например, если вы введете простейшее выражение =A1 в клетку C1, Эксель продублирует данные из A1 в C1:

Чтобы сложить числа в ячейках A1 и A2, используйте:  =A1 + A2

Что такое ссылка на диапазон?

В Microsoft Excel диапазон – это блок из двух или более ячеек. Ссылка на диапазонпредставлена адресами верхней левой и нижней правой его ячеек, разделенных двоеточием.

Например, диапазон A1:C2 включает 6 ячеек от A1 до C2.

Как создать ссылку?

Чтобы записать ссылку на ячейку на том же листе, вам нужно сделать следующее:

  1. Выберите, где вы хотите ввести формулу.
  2. Введите знак равенства (=).
  3. Выполните одно из следующих действий:
    • Запишите координаты прямо в ячейку или в строку формул, или же
    • Кликните ячейку, к которой хотите обратиться.
  4. Введите оставшуюся часть формулы и нажмите Enter для завершения.

Например, чтобы сложить значения в A1 и A2, введите знак равенства, щелкните A1, введите знак плюса, щелкните A2 и нажмите Enter:

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

Например, чтобы сложить значения в A1, A2 и A3, введите знак равенства, затем имя функции СУММ и открывающую скобку, выберите ячейки от A1 до A3, введите закрывающую скобку и нажмите Enter:

Чтобы обратиться ко всей строке или целому столбцу, щелкните номер строки или букву столбца соответственно.

Например, чтобы сложить все ячейки в строке 1, начните вводить функцию СУММ, а затем кликните заголовок первой строки, чтобы включить ссылку на строку в ваш расчёт:

Как изменить ссылку?

Чтобы изменить адрес ячейки в существующей формуле Excel, выполните следующие действия:

  1. Выберите клетку, содержащую формулу, и нажмите F2, чтобы войти в режим редактирования, или дважды щелкните саму ячейку. Это выделит каждую ячейку или диапазон, на который ссылается формула, другим цветом.
  2. Чтобы изменить адрес, выполните одно из следующих действий:
    • Выберите адрес в формуле и вручную введите новый вместо него.
    • Выбрав ссылку, при помощи мышки укажите вместо нее другой адрес или диапазон на листе.
  1. Чтобы включить больше или меньше ячеек в ссылку на диапазон, перетащите его правый нижний угол:
  1. Нажмите Enter.

Как сделать перекрестную ссылку?

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

Чтобы сослаться на данные, находящиеся на другом листе, введите имя этого целевого листа с восклицательным знаком (!) перед адресом ячейки или диапазона.

Например, вот как вы можете создать ссылку на адрес A1 на листе Лист2 в той же книге Excel:

=Лист2!A1

Если имя рабочего листа содержит пробелы или неалфавитные символы, вы должны заключить его в одинарные кавычки, например:

=’Новый лист’!A1

Чтобы предотвратить возможные опечатки и ошибки, вы можете заставить Excel автоматически создавать для вас внешнюю ссылку. Вот как:

  1. Начните ввод в ячейку. Запишите знак «=».
  2. Щелкните вкладку листа, на которую хотите сослаться, затем выберите ячейку или диапазон ячеек на этом листе.
  3. Завершите ввод и нажмите Enter.

Как сослаться на другую книгу?

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

Например:

=[Книга1.xlsx]Лист1!A1

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

='[Новый файл.xlsx]Лист1′!A1

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

Итак, мы научились создавать простейшие ссылки. Теперь рассмотрим, какими они бывают.

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

Относительная ссылка на ячейку.

Относительная ссылка является самой простой и включает координаты строки и столбца, например А1 или А1:D10. По умолчанию все адреса ячеек в Экселе являются относительными.

Пример:

=A2

Это простейшее выражение сообщает программе, что нужно показать значение, которое записано в первой колонке (A) и второй строке (2). Используя скриншот чуть ниже, если бы эта формула была помещена в ячейку D1, она отобразила бы число «8», поскольку это значение находится по адресу A2.

При перемещении или копировании относительные ссылки изменяются в зависимости от относительного положения строк и столбцов. Иначе говоря, насколько новое местоположение изменилось относительно первоначального.

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

Например, чтобы сложить числа в A2 и B2, вы вводите это в C2: =A2+B2. При копировании из строки 2 в строку 3 выражение изменится на = A3+B3.

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

К примеру, так очень удобно перемножать количество и цену различных товаров в таблице, чтобы найти их стоимость.

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

Вместо того, чтобы вводить формулу для всех ячеек одну за другой, вы можете просто скопировать ячейку D2 и вставить ее во все остальные ячейки (D3: D8). Когда вы это сделаете, вы заметите, что адрес автоматически настраивается, чтобы ссылаться на соответствующую строку. Например, формула в ячейке D3 становится B3*C3, а в D4 теперь записано:  B4*C4.

Абсолютная ссылка на ячейку.

Абсолютная ссылка в Excel имеет знак доллара ($) в координатах строк или столбцов, например $A$1 или $A$1:$B$20.

Символ доллара, добавленный перед любой из координат, делает адрес абсолютным (т. е. предотвращает изменение номера строки и столбца).

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

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

Например, чтобы умножить числа в столбце B на величину скидки из F2, вы вводите следующую формулу в строке 2, а затем копируете её вниз, перетаскивая маркер заполнения:

=B2*$F$2

Относительная ссылка (B2) будет изменяться в зависимости от относительного положения строки, в которую она копируется, в то время как абсолютная ($F$2) всегда будет зафиксирована на одном и том же адресе:

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

Итак, относительная ссылка на ячейку отличается от абсолютной тем, что копирование или перемещение формулы приводит к её изменению. 

Абсолютные ссылки всегда указывают на конкретный адрес, независимо от того, где они находятся.

Смешанная ссылка.

Смешанные ссылки немного сложнее, чем абсолютные и относительные.

Может быть два типа смешанных ссылок:

  • Строка блокируется, а столбец изменяется при копировании.
  • Столбец фиксируется, а строка изменяется.

Смешанная ссылкасодержит одну относительную и одну абсолютную координату, например $A1 или A$1.

Как вы помните, абсолютная ссылка содержит 2 знака доллара ($), которые фиксируют как столбец, так и строку.  В смешанной только одна координата является фиксированной (абсолютной), а другая (относительная) будет изменяться в зависимости от нового расположения:

  • Абсолютный столбец и относительная строка, например $A1. Когда выражение с этим типом ссылки копируется в другие ячейки, знак $ перед буквой столбца фиксирует обращение строго на указанный столбец, чтобы оно никогда не изменялось. Относительная ссылка на строку без знака доллара будет меняться в зависимости от строки, в которую копируется формула.
  • Относительный столбец и абсолютная строка, например A$1. Здесь адресация на строку будет зафиксирована, а на столбец — поменяется.

Может быть много ситуаций, когда нужно фиксировать только одну координату: либо столбец, либо строку.

Например, чтобы умножить колонку с ценами (столбец В) на 3 разных значения наценки (C2, D2 и E2), вы поместите следующую формулу в C3, а затем скопируете ее вправо и затем вниз:

=$B3*(1+C$2)

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

В первом множителе мы зафиксировали столбец. Поэтому при копировании вправо по строке адрес $B3 не изменится: ведь строка по-прежнему третья.

А вот во втором множителе знак доллара мы поставили перед номером строки. Поэтому при копировании формулы в D3 координаты столбца изменятся и вместо C$2 мы получим D$2. В результате в D3 получим:

=$B3*(1+D$2)

А когда будем копировать вниз, всё будет наоборот: $B3 изменится на $B4, $B5 и т.д. А вот D$2 не изменится, так как «заморожена» строка. В результате в С4 получим:

=$B4*(1+C$2)

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

И если ваши наценки вдруг изменятся, просто поменяйте числа в C2:E2, и проблема будет решена почти мгновенно.

Как изменить ссылку с относительной на абсолютную (или смешанную)?

Чтобы переключиться с относительной на абсолютную и наоборот, вы можете либо добавить, либо удалить знак $ вручную. А можно использовать функциональную клавишу F4:

  1. Дважды щелкните ячейку, содержащую формулу.
  2. Выберите ссылку, которую хотите изменить.
  3. Нажмите F4 для переключения между четырьмя ссылочными типами.

Неоднократно нажимая F4, вы будете переключать их в следующем порядке:

Если вы выбрали относительную ссылку без знака $, например A1, последовательно нажимая F4, вы будете переключаться между абсолютной ссылкой с двумя знаками доллара $A$1, абсолютной строкой A$1, абсолютным столбцом $A1, а затем вновь вернёмся к A1.

Имя как разновидность абсолютной ссылки.

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

В нашем примере установите курсор в F2, а затем присвойте этому адресу имя, как это показано на рисунке выше. При этом можно использовать только буквы, цифры и нижнее подчёркивание, которым можно заменить пробел. Знаки препинания и служебные символы не допускаются.

Его вы можете использовать в вычислениях вашей рабочей книги.

=B2*скидка

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

Формула же при этом становится более понятной и читаемой.

Ссылка на столбец.

Как и на отдельные ячейки, ссылка на весь столбец может быть абсолютной и относительной, например:

  • Абсолютная ссылка на столбец – $A:$A
  • Относительная – A:A

Когда вы используете знак доллара ($) в абсолютной ссылке на столбец,  его адрес не изменится при копировании в другое расположение.

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

А теперь давайте посмотрим это на примере.

Предположим, у вас есть некоторые числа в колонке B, и вы хотите узнать их общее и среднее значение. Проблема в том, что новые данные добавляются в таблицу каждую неделю, поэтому писать обычную формулу СУММ() или СРЗНАЧ() для фиксированного диапазона ячеек – не лучший вариант. Вместо этого вы можете ссылаться на весь столбец B:

=СУММ($D:$D)— используйте знак доллара ($), чтобы создать абсолютную ссылку на весь столбец, которая привязывает формулу к столбцу B.

=СУММ(D:D)— напишите формулу без $, чтобы сделать относительную ссылку на весь столбец, которая будет изменяться при копировании.

Совет. При написании формулы щелкните мышкой на букве заголовка (D, например), чтобы добавить ссылку сразу на весь столбец. Как и в случае ячейками, программа по умолчанию вставляет относительную ссылку (без знака $):

Ссылка на строку.

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

  • Абсолютная ссылка на строку – $1:$1
  • Относительная – 1:1

Пример 2. Ссылка на всю строку (абсолютная и относительная)

Если данные в вашем листе расположены горизонтально, а не по вертикали, вы можете ссылаться на всю строку. Например, вот как мы можем рассчитать среднюю цену в строке 2:

=СРЗНАЧ($3:$3) – абсолютная ссылка на всю строку зафиксирована с помощью знака доллара ($).

=СРЗНАЧ(3:3) – относительная ссылка на строку изменится при копировании вниз.

В этом примере нам нужна относительная ссылка. Ведь у нас есть 6 строчек с данными, и мы хотим вычислить среднее значение для каждого товара отдельно. Записываем в B12 расчет средней цены для яблок и копируем его вниз:

Для бананов (B13) расчет уже будет такой:   СРЗНАЧ(4:4). Как видите, номер строки автоматически изменился. 

Ссылка на столбец, исключая первые несколько строк.

Это очень актуальная проблема, потому что довольно часто первые несколько строк на листе содержат некоторые вводные предложения, шапку даблицы или пояснительную информацию, и вы не хотите включать их в свои вычисления. К сожалению, Excel не допускает ссылок типа D3:D, которые включали бы все данные в столбце D, только начиная со строки 3. Если вы попытаетесь добавить такую ​​конструкцию, ваша формула, скорее всего, вернет ошибку #ИМЯ?.

Вместо этого вы можете указать максимальную строку, чтобы ваша ссылка включала все возможные адреса в данном столбце. В Excel с 2019 по 2007 максимум составляет 1 048 576 строк и 16 384 столбца. Более ранние версии программы имеют максимум 65 536 строк и 256 столбцов.

Итак, чтобы найти сумму продаж в приведенной ниже таблице (колонка «Стоимость»), можно использовать выражение:

=СУММ(D3:D1048576)

Как вариант, можно вычесть из общей суммы те данные, которые хотите исключить:

=СУММ(D:D)-СУММ(D1:D2)

Но первый вариант предпочтительнее, так как СУММ(D:D) выполняется дольше и требует больше вычислительных ресурсов, чем СУММ(D3:D1048576).

Смешанная ссылка на весь столбец.

Как я упоминал ранее, вы также можете создать смешанную ссылку на весь столбец или целую строку:

  • Смешанная на столбец, например $A:A
  • Смешанная на строку, например $1:1

Теперь посмотрим, что произойдет, если вы скопируете формулу с такими адресами в другие ячейки. Предположим, вы вводите формулу =СУММ($B:B) в какую-то клетку, в этом примере F3. Когда вы копируете формулу вправо (в G3), она меняется на, =СУММ($B:C), потому что первая B имеет знак $ и остается неподвижной, а вторая B – обычная и поэтому меняется. 

В результате Эксель сложит все числа в столбцах B и C. Ну и, двигаясь далее вправо, далее можно найти сумму уже трёх колонок.

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

Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге!

5 способов быстро транспонировать таблицу — В этой статье показано, как столбец можно превратить в строку в Excel с помощью функции ТРАНСП, специальной вставки, кода VBA или же специального инструмента. Иначе говоря, мы научимся транспонировать таблицу.… Как быстро заполнить пустые ячейки в Excel? — В этой статье вы узнаете, как выбрать сразу все пустые ячейки в электронной таблице Excel и заполнить их значением, находящимся выше или ниже, нулями или же любым другим шаблоном. Заполнять… Как поменять столбцы местами в Excel? — В этой статье вы узнаете несколько методов перестановки столбцов в Excel. Вы увидите, как можно перетаскивать один или сразу несколько столбцов мышью либо с помощью «горячих» клавиш. Если вы постоянно используете… Как в Excel разделить текст из одной ячейки в несколько — В руководстве объясняется, как разделить ячейки в Excel с помощью формул и стандартных инструментов. Вы узнаете, как разделить текст запятой, пробелом или любым другим разделителем, а также как разбить строки на…

0 0 голоса

Рейтинг статьи

1.

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

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

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

  • использовать в нескольких формулах значение одной ячейки.

     Имеются два вида ссылок: 

  1. относительные- зависящие от положения формулы;
  2. абсолютные — не зависящие от положения формулы.
     Различие между относительными и абсолютными ссылками проявляется при копировании формулы из текущей ячейки в другие ячейки.

     Относительные ссылки. Относительная ссылка определяет расположение ячейки с данными относительно ячейки, в которой записана формула. 2 (рисунок 3).

Рисунок 3 — Скопированная формула с относительной ссылкой 

     Абсолютные ссылки. Абсолютная ссылка всегда ссылается на ячейку, расположенную в определенном (фиксированном) месте. Перед каждой буквой и цифрой ставится знак $, например $B$2. При изменении позиции ячейки, которая имеет формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и столбцов абсолютная ссылка не корректируется (рисунок 4).

Рисунок 4 — Скопированная формула с абсолютной ссылкой

     Смешанные ссылки. Смешанная ссылка содержит абсолютно адресуемый столбец и относительно адресуемую строку ($A1) или относительно адресуемый столбец и абсолютно адресуемую строку (A$1). При изменении позиции ячейки, относительная часть адреса изменяется, а абсолютная часть адреса не изменяется. При копировании формулы вдоль строк и столбцов относительная ссылка корректируется, а абсолютная ссылка нет (рисунок 5).

Рисунок 5 — Скопированная формула со смешанной ссылкой

     Чтобы преобразовать ссылку из относительной в абсолютную и наоборот, можно выделить ее в строке ввода и нажать клавишу F4 (Microsoft Office Excel) или комбинацию клавиш Shift+F4 (OpenOffice.org Calc). Если выделить относительную ссылку, такую как А1, то пр первом нажатии этой клавиши (комбинации клавиш) и для строки, и для столбца установятся абсолютные ссылки ($A$1). При втором нажатии абсолютную ссылку получит только строка (A$1). При третьем нажатии абсолютную ссылку получит только столбец ($A1). Если нажать клавишу F4 еще раз, то для столбца и строки снова установятся относительные ссылки (А1).

Относительные и абсолютные ссылки в Эксель

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

На прошлом шаге мы создали целый столбец значений просто воспользовавшись функцией автозаполнения 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, то есть сумма итогового вклада с процентами минус сумма начального вклада.

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

Именно это мы и сделаем на следующем шаге.

MS Excel абсолютная ссылка относительная ссылка

В Excel есть три типа ссылок: относительная, абсолютная и смешанная.

Как сделать: описание, пошаговая инструкция, примеры и иллюстрации.

Все ссылки в Excel по умолчанию относительные.

Абсолютные и смешанные ссылки можно сделать с помощью символа доллар ($), прописав ее в формуле.

A1 — относительный столбец и относительная строка;

A$1 — относительный столбец и абсолютная строка;

$A1 — абсолютный столбец и относительная строка;

$A$1 — абсолютный столбец и абсолютная строка.

Рассмотрим разницу на примере.

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

Пример с относительными ссылками.

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

В таблице напротив ФИО каждого сотрудника стоит его оклад, процент налога и сумма самого налога (См. Рис 1).

В данном примере, в каждой ячейке прописана формула, где оклад сотрудника умножается на ставку налога, и так по каждому сотруднику.

Формула =D3*E3.

Обе ссылки являются относительной.

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

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

Рис. 1.

Абсолютная ссылка

Пример с абсолютной ссылкой.

Та же ситуация.

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

В данном примере ставка налога будет указана в отдельной ячейке (См. Рис. 2.)

Что бы ссылку на ячейку с налогом сделать абсолютной в формуле прописываем знак доллар ($).

Формула =E6*$E$2.

В данном случае абсолютной ссылкой является ссылка на ячейку *$E$2, и все формулы, в которых она указана, будут использовать значение, указанное в этой ячейки.

В нашем примере, если протянуть формулу вниз, по столбцу «Сумма налога», будет меняться только ссылка на ячейки с окладами сотрудников, а ссылка на процент не будет меняться.

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

Сама таблица при использовании абсолютных ссылок становиться меньше и удобнее для восприятия.

Рис. 2

 

Смешанная ссылка.

Позволяют закрепить либо столбец, либо строку.

Пример формул, где используются смешанные ссылки:

=E6*$E2

Здесь закреплен столбец Е. Строка номер 2 остаётся относительной.

=E6*E$2

Ссылки на понятия, которые прозвучали в данной статье:

Как в MS Excel посчитать проценты. Инструкция и примеры.

 

Абсолютная ссылка

Ссылка, указывающая к файлу полный путь с учетом домена и протокола передачи данных, называется абсолютной ссылкой html. Все подробности о них читайте в нашей Вики-статье.

Абсолютная ссылка html — вид ссылки, которая указывает к файлу полный путь. Соответствует всем требованиям веб пространства URL-обозначения, включая указание протокола передачи данных, домен, полный путь до конкретного файла/документа.

 

Абсолютная ссылка

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

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

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

В этом примере у нас есть две папки: ASP и HTML. Если мы свяжем файл Active-Server-Pages.asp с файлом HTML-Introduction.asp, нам нужно будет указать имя папки, потому что файл HTML-Introduction.asp находится за пределами папки ASP.

 

Итак, для первого примера предположим, что у нас есть две папки: asp и html. Предположим далее, что у нас есть по одной веб-странице в каждой из этих двух папок: папка asp содержит файл с именем Active-Server-Pages.asp, а в папке html есть файл с именем HTML-Introduction.asp. См. Рисунок 2. Чтобы создать ссылку из файла Active-Server-Pages.asp на файл HTML-Introduction.asp, нам нужно будет указать имя папки и файл в нашем теге ссылки:

<a href=»html/HTML-introduction.asp»>Introduction to HTML</a>

Что означает указанный выше тег ссылки? Он начинается с обычного тега привязки (<a>), который требуется при ссылках на другие документы. Затем, чтобы указать компьютеру, на какой документ мы хотим создать ссылку, мы используем свойство HREF, которое содержит целевой документ. Целевой документ — это папка с именем html и файл с именем HTML-Introduction.asp. 

Во втором примере мы создадим ссылку на http://www.google.com. Чтобы создать любую ссылку в HTML, мы должны использовать тег привязки со свойством HREF. Чтобы создать ссылку на www.google.com, мы используем следующий код:

<a href=http://www.google.com>Google</a>

Обратите внимание, что в этом примере свойство HREF начинается с http: // www. потому что мы хотим дать ссылку на веб-страницу, которая находится за пределами нашего текущего веб-сайта. Доменное имя (в данном случае google.com) загружает веб-страницу по умолчанию с этого конкретного веб-сайта.

 

Абсолютная и относительная ссылка: отличия

Чем отличается абсолютная ссылка от относительной? Абсолютная ссылка подразумевает использование полного URL-адреса, к примеру, http://www. simple.com/webpage1.html. В относительной указывается лишь сама страница — webpage1.html.

 

Абсолютные ссылки при раскрутке веб-ресурсов

Для указания сторонних сайтов предпочтительно использование абсолютных ссылок. Применение относительных может снизить весомость ресурса в глазах поисковиков. Ведь поисковые роботы могут проиндексировать веб-ресурс без доменного имени.

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

 

Вес ссылки

Вес ссылки – показатель, по которому поисковые системы определяют значимость ссылки.

Обычно вес ссылки веб назначается поисковиками, чтобы увеличить показатели вебсайта. Исходя из этого бытует мнение, что, если у вебсайта высокий ИКС или DR, то объем внешних ссылок с других ресурсов на нем существеннее. При этом, высокое качество ссылок играет не менее важную роль, чем и их число. В случае размещения на вебсайте ссылки с непопулярного источника, вес данной ссылки будет незначителен. Если же расположенная ссылка с широко известного и раскрученного вебсайта, соответственно вес ее будет велик. Наличие такого рода ссылки на вебсайте обычно приводит к повышению в позициях выдачи поисковой системой. При оптимизации вебсайта понятие «вес ссылки» несет важную роль. 

 

Реферальная ссылка

Реферальная ссылка — ссылка для перенаправления на интернет-ресурс новых пользователей. Реферальную ссылку можно проставлять на других сайтах. Как это работает? Ну например, вы зарегистрировались на каком то проекте, на который можно привлекать рефералов. На любом проекте есть разделы, например, “пригласить рефералов”, там вы найдете такую ссылку.

Абсолютные и относительные адреса ячеек

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

Абсолютная ссылка — это не изменяющийся при копировании и перемещении формулы адрес ячейки, содержащий исходное данное (операнд).

Для указания абсолютной адресации вводится символ $. Различают два типа абсолютной ссылки: полная и частичная.

Полная абсолютная ссылка указывается, если при копировании или перемещении адрес клетки, содержащий исходное данное, не меняется. Для этого символ $ ставится перед наименованием столбца и номером строки.

Пример 14.9. $B$5; $D$12 — полные абсолютные ссылки.

Частичная абсолютная ссылка указывается, если при копировании и перемещении не меняется номер строки или наименование столбца. При этом символ $ в первом случае ставится перед номером строки, а во втором — перед наименованием столбца.

Пример В$5, D$12 — частичная абсолютная ссылка, не меняется номер строки; $B5, $D12 — частичная абсолютная ссылка, не меняется наименование столбца.

Относительная ссылка — это изменяющийся при копировании и перемещении формулы адрес ячейки, содержащий исходное данное (операнд). Изменение адреса происходит по правилу относительной ориентации клетки с исходной формулой и клеток с операндами.

Форма написания относительной ссылки совпадает с обычной записью.

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

Рассмотрим правило относительной ориентации ячейки на примере.

Пример 2.6 В ячейке В4 находилась формула = В2+В3. При копировании ее в ячейку С4 формула приобретает вид =С2+С3

При копировании в ячейку В7 формула приобретает вид =В5+В6.

Общее правило: если формула копируется на N строк вниз, то Excel добавляет ко всем используемым номерам строк число N. Если формула копируется на M столбцов правее, то все используемые в ней буквенные обозначения столбцов смещаются на М позиций вправо.

При копировании можно предотвратить изменение формулы, если записать абсолютную ссылку на ячейку ($). Если требуется, чтобы не менялся номер строки или столбца, то применяют частичную абсолютную ссылку.

Ссылка на именованную ячейку (диапазон) всегда является абсолютной!

Все сказанное выше относится и к адресам диапазонов. Однако следует помнить, что диапазон задается адресами угловых ячеек. При простановке знаков $ при копировании необходимо знак $ ставить при координатах обоих угловых ячеек.

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

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

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

Описание управления ссылками и хранения в Excel

Сводка

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

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

Дополнительная информация

Как обрабатываются пути ссылок при открытии файла

Когда Excel открывает файл, содержащий ссылки (связанную книгу), он объединяет части ссылок, хранящиеся в файле, с необходимыми частями текущего пути связанной книги.

Как хранятся пути ссылок

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

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

  • Если связанный файл и файл исходных данных не находятся на одном диске, буква диска сохраняется вместе с путем к файлу и именем файла.

  • Если связанный файл и файл исходных данных находятся в одной папке, сохраняется только имя файла.

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

    Например, если связанный файл C: \ Mydir \ Linked.xls зависит от C: \ Mydir \ Files \ Source.xls, сохраняется только часть пути \ Files \ Source.xls.

  • Если исходный файл данных находится на одну папку ниже связанного файла, свойство сохраняется, чтобы указать это.

    Например, связанный файл — C: \ Mydir \ Files \ Myfile \ Linked.xls, а файл исходных данных — C: \ Mydir \ Files \ Source.xls. Excel сохраняет только \ MyDir \ Files \ .. \ Source.xls.

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

    Например, связанный файл — C: \ Mydir \ Files \ Myfiles1 \ Linked.xls, а файл исходных данных — C: \ Mydir \ Files \ Source.xls, связанный файл Linked.xls, копируется из папки C: \ Mydir \ Files \ Myfiles1 в папку с именем C: \ Mydir \ Files \ Myfiles2, и сохраняется ссылка на C: \ Mydir \ Files \ Source. xls.

  • Если файл исходных данных расположен в папке XLStart , Альтернативное расположение файла запуска или в папке библиотеки , свойство записывается для указания одной из этих папок, и сохраняется только имя файла.

    Примечание. Excel распознает две папки XLStart по умолчанию, из которых файлы автоматически открываются при запуске.Две папки выглядят следующим образом:

    • Папка XLStart, которая находится в папке установки Office, например C: \ Program Files \ Microsoft Office \ Папка Office \ XLStart

    • Папка XLStart в профиле пользователя, например C: \ Documents and Settings \ имя пользователя \ Application Data \ Microsoft \ Excel \ XLStart

    Папка XLStart в профиле пользователя — это папка XLStart, которая будет сохранена как свойство для ссылки. Если вы используете папку XLStart, которая находится в папке установки Office, эта папка XLStart обрабатывается как любая другая папка на жестком диске.

    Имя папки Office меняется в зависимости от версии Office. Например, папка Office называется Office, Office10, Office11 или Office12, в зависимости от используемой версии Office. Это изменение имени папки приводит к тому, что ссылки не работают, если вы переходите на компьютер, на котором работает версия Excel, отличная от той, в которой была установлена ​​ссылка.

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

Относительные и абсолютные ссылки

Ссылки на внешние книги по возможности создаются относительным образом. Это означает, что записывается не полный путь к файлу исходных данных, а скорее часть пути, относящаяся к связанной книге.С помощью этого метода вы можете перемещать книги, не разрывая связи между ними. Однако ссылки остаются неизменными, только если книги остаются в одном месте относительно друг друга. Например, если связанный файл — C: \ Mydir \ Linked.xls, а файл исходных данных — C: \ Mydir \ Files \ Source.xls, вы можете переместить файлы на диск D, пока исходный файл остается. находится в подпапке под названием «файлы».

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

Сопоставленные диски по сравнению с UNC

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

Ссылка, отображаемая в Excel, может отображаться по-разному в зависимости от того, как была открыта книга. Ссылка может соответствовать либо корневому общему ресурсу UNC, либо букве корневого диска, которая использовалась для открытия файла.

Сценарии, при которых ссылки могут не работать должным образом

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

Сценарий 1:

  1. Вы подключаете диск к корню общего ресурса. Например, вы подключаете диск Z к \\ Server \ Share \ Folder1.

  2. Вы создаете ссылки на книгу, которая хранится в назначенном месте, после того, как вы открываете файл через этот подключенный диск.

  3. Вы открываете файл по пути UNC.

  4. Как следствие, ссылка будет неработающей.

Если закрыть файл без его сохранения, ссылки не изменятся.Однако, если вы сохраните файл перед его закрытием, вы сохраните ссылки с текущим неработающим путем. Папки между корнем общего ресурса и сопоставленной папкой не будут указаны в пути. В приведенном выше примере ссылка изменится на \\ Server \ Folder1. Другими словами, имя общего ресурса удаляется из пути.

Сценарий 2:

  1. Вы подключаете диск к корню общего ресурса. Например, вы подключаете диск Z к \\ Server \ Share \ Folder1.

  2. Вы открываете файл по пути UNC или подключенному диску, сопоставленному с другой папкой на общем ресурсе, например \\ Server \ Share \ Folder2.

  3. Как следствие, ссылка будет разорвана.

Если закрыть файл без его сохранения, ссылки не изменятся. Однако, если вы сохраните файл перед его закрытием, вы сохраните ссылки с текущим неработающим путем.Папки между корнем общего ресурса и сопоставленной папкой не будут указаны в пути. В приведенном выше примере ссылка изменится на \\ Server \ Folder1 .

Excel HYPERLINK Function

На главную »Встроенные функции Excel» Excel-Lookup-And-Reference-Функции »Excel-Hyperlink-Function

Описание функции

Функция Excel Hyperlink создает гиперссылку на указанное место.

Синтаксис функции:

ГИПЕРССЫЛКА (расположение_ссылки, [понятное_имя])

, где аргументы следующие:

расположение_ссылки Адрес для ссылки.
[friendly_name]

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

Если аргумент [friendly_name] опущен, в ячейке отображается текст link_location.

Адрес, предоставленный для аргумента link_location, может быть абсолютной или относительной ссылкой. Разница между этими двумя ссылочными типами показана в таблице ниже.

Абсолютная ссылка

Показывает полный путь к файлу, на который указывает ссылка.

Например,

C: \ Documents and Settings \ User1 \ ExcelFile1.xlsx

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

Ссылается на расположение файла ссылки относительно текущего каталога.

Например, если текущий файл Excel находится в C: \ Documents and Settings, то относительная ссылка:

User1 \ ExcelFile1.xlsx

эквивалентна абсолютной ссылке:

C: \ Documents and Settings \ User1 \ ExcelFile1.xlsx

Кроме того, в относительной ссылке .. \ используется для указания каталога над текущим.

Например, если текущий файл Excel находится в папке C: \ Documents and Settings, то относительная ссылка:

.. \ ExcelFile1.xlsx

означает «переместиться на один каталог вверх и оттуда получить доступ к файлу ExcelFile1.xlsx»

Это эквивалентно абсолютной ссылке:

C: \ ExcelFile1.xlsx

Примеры функций гиперссылок

Гиперссылки Excel — отличный способ организации данных.Это показано в таблице ниже, которая сопоставляет детали счетов-фактур и использует функцию гиперссылки Excel для создания ссылок на отдельные счета-фактуры:

Формулы:

A B
1 Дата Ссылка на счет-фактуру
2 05-янв-17 = ГИПЕРССЫЛКА («C: \ Invoices \ Invoice_B001.pdf», «B001»)
3 9025- 17
= ГИПЕРССЫЛКА («Счет-фактура_B002.pdf «,» B002 «)
4

Результаты:

A B
2 05-янв-17 B001
3 06-янв-17 B002
4 9017 Обратите внимание, что в примере выше 9 , гиперссылка в ячейке B2 использует абсолютную ссылку, тогда как гиперссылка в ячейке B3 использует относительную ссылку.

Поскольку текущая электронная таблица находится в каталоге C: \ Invoices, функции гиперссылки в ячейках B2 и B3 создают ссылки на файлы Invoice_B001.pdf и Invoice_B002.pdf, которые находятся в каталоге C: \ Invoices. .

Дополнительные сведения и примеры функции гиперссылки Excel см. На веб-сайте Microsoft Office.

Возврат к функциям поиска и справочника Excel Страница
Возврат к списку всех встроенных функций Excel

Вставка гиперссылок (Microsoft Excel)

Одной из встроенных функций Excel является поддержка Интернета.Одним из аспектов этого является возможность добавлять гиперссылки в ваши книги Excel. Когда вы вставляете гиперссылку, вы можете использовать два типа. Вы можете вставить абсолютную или относительную гиперссылку. Для вставки гиперссылки:

  1. Выберите ячейку, в которую вы хотите вставить гиперссылку.
  2. Щелкните инструмент «Вставить гиперссылку» на панели инструментов или выберите «Гиперссылка» в меню «Вставка». Excel отображает диалоговое окно «Вставить гиперссылку». (См. Рисунок 1.)
  3. Рисунок 1. Диалоговое окно «Вставить гиперссылку».

  4. Если вы используете Excel 2000 или более позднюю версию, содержимое ячейки, выбранной на шаге 1, отображается в поле «Текст для отображения». Вы можете изменить его, если хотите.
  5. В поле «Ссылка на файл или URL-адрес» (Excel 97), в поле «Введите имя файла или веб-страницы» (Excel 2000) или в поле «Адрес» (Excel 2002 или Excel 2003) укажите адрес страницы, которая будет загружаться при загрузке. пользователь нажимает на вашу гиперссылку. Обычно это URL-адрес, но он также может быть файлом или ресурсом в вашей системе.Вы также можете выбрать адрес из раскрывающегося списка или использовать кнопку «Обзор», чтобы найти ресурс.
  6. Если вы используете Excel 97 и хотите, чтобы Excel использовал относительную гиперссылку, убедитесь, что установлен флажок «Использовать относительный путь для гиперссылки».
  7. По завершении нажмите ОК.

Если на шаге 5 вы указали, что хотите использовать относительную гиперссылку, Excel предполагает, что информация в поле «Ссылка на файл или URL-адрес» (Excel 97) или в поле «Введите имя файла или веб-страницы» (Excel 2000) или Поле адреса (Excel 2002 или Excel 2003) следует добавить к так называемому базовому URL-адресу, чтобы создать полный URL-адрес.Базовый URL указывается следующим образом:

  1. Выберите «Свойства» в меню «Файл». Excel отображает диалоговое окно «Свойства».
  2. Убедитесь, что выбрана вкладка «Сводка». (См. Рисунок 2.)
  3. Рисунок 2. Вкладка «Сводка» диалогового окна «Свойства».

  4. В поле «База гиперссылки» укажите первую часть любого URL-адреса, указанного с использованием относительных ссылок.
  5. По завершении нажмите ОК.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.Этот совет (2802) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь: Вставка гиперссылок .

Автор Биография

Аллен Вятт

Аллен Вятт — всемирно признанный автор, автор более чем 50 научно-популярных книг и многочисленных журнальных статей.Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнать больше о Allen …

Указание точных страниц для печати

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

Открой для себя больше

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

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

Открой для себя больше

Совместное использование книги

Нужно разрешить другим вносить вклад в вашу книгу Excel? Это легко сделать, если вы просто поделитесь им. Этот совет обеспечивает …

Открой для себя больше

Избавление от множества гиперссылок

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

Открой для себя больше

Копирование гиперссылки на множество листов

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

Открой для себя больше

Удаление гиперссылки

Гиперссылки могут быть полезны в одних таблицах, но неудобны в других. Вот как избавиться от лишних гиперссылок …

Открой для себя больше

относительных и абсолютных ссылок на ячейки

Урок 15: Относительные и абсолютные ссылки на ячейки

/ ru / excel2016 / создание-более-сложных-формул / содержание /

Введение

Существует два типа ссылок на ячейки: относительные и абсолютные .Относительные и абсолютные ссылки ведут себя по-разному при копировании и заполнении в другие ячейки. Относительные ссылки изменяют значение при копировании формулы в другую ячейку. С другой стороны, абсолютные ссылки остаются константой независимо от того, куда они копируются.

Необязательно: загрузите нашу рабочую тетрадь.

Посмотрите видео ниже, чтобы узнать больше о ссылках на ячейки.

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

По умолчанию все ссылки на ячейки являются относительными ссылками .При копировании в несколько ячеек они меняются в зависимости от относительного положения строк и столбцов. Например, если вы скопируете формулу = A1 + B1 из строки 1 в строку 2, формула станет = A2 + B2 . Относительные ссылки особенно удобны, когда вам нужно повторить один и тот же расчет для нескольких строк или столбцов.

Чтобы создать и скопировать формулу с использованием относительных ссылок:

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

  1. Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку D4 .
  2. Введите формулу , чтобы вычислить желаемое значение. В нашем примере мы введем = B4 * C4 .
  3. Нажмите Введите на клавиатуре.Формула будет рассчитана, и результат отобразится в ячейке.
  4. Найдите маркер заполнения в правом нижнем углу нужной ячейки. В нашем примере мы найдем маркер заполнения для ячейки D4 .
  5. Щелкните и перетащите маркер заливки на ячейки, которые нужно заполнить. В нашем примере мы выберем ячейки D5: D13 .
  6. Отпустите мышь. Формула будет скопирована в выбранные ячейки с относительными ссылками , результат будет отображаться в каждой ячейке.

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

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

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

Абсолютная ссылка обозначается в формуле добавлением знака доллара ($) перед столбцом и строкой. Если он предшествует столбцу или строке (но не обоим сразу), он называется смешанной ссылкой .

В большинстве формул вы будете использовать относительный ( A2 ) и абсолютный ( $ A $ 2 ) форматы. Смешанные ссылки используются реже.

При написании формулы в Microsoft Excel вы можете нажать клавишу F4 на клавиатуре для переключения между относительными, абсолютными и смешанными ссылками на ячейки, как показано на видео ниже.Это простой способ быстро вставить абсолютную ссылку.

Чтобы создать и скопировать формулу с использованием абсолютных ссылок:

В приведенном ниже примере мы собираемся использовать ячейку E2 (которая содержит ставку налога 7,5%) для расчета налога с продаж для каждого элемента в столбце D . Чтобы ссылка на налоговую ставку оставалась постоянной — даже когда формула копируется и заполняется в другие ячейки, — нам нужно сделать ячейку $ E $ 2 абсолютной ссылкой.

  1. Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку D4 .
  2. Введите формулу , чтобы вычислить желаемое значение. В нашем примере мы введем = (B4 * C4) * $ E $ 2 , что сделает $ E $ 2 абсолютной ссылкой.
  3. Нажмите Введите на клавиатуре. Формула будет рассчитана, и результат отобразится в ячейке.
  4. Найдите маркер заполнения в правом нижнем углу нужной ячейки.В нашем примере мы найдем маркер заполнения для ячейки D4 .
  5. Щелкните и перетащите маркер заливки на ячейки, которые вы хотите заполнить (ячейки D5: D13 в нашем примере).
  6. Отпустите мышь. Формула будет скопирована в выбранные ячейки с абсолютной ссылкой , и значения будут вычислены в каждой ячейке.

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

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

Использование ссылок на ячейки с несколькими листами

Excel позволяет вам ссылаться на любую ячейку на любом рабочем листе , что может быть особенно полезно, если вы хотите ссылаться на конкретное значение с одного рабочего листа на другой.Для этого вам просто нужно начать ссылку на ячейку с листа , имя , за которым следует восклицательный знак точка (!) . Например, если вы хотите сослаться на ячейку A1 на Sheet1 , ее ссылка на ячейку будет Sheet1! A1 .

Обратите внимание, что если имя рабочего листа содержит пробела , вам нужно будет заключить имя в одинарные кавычки (») . Например, если вы хотите указать ячейку A1 на листе с именем Бюджет на июль , ссылка на ячейку будет «Бюджет на июль»! A1 .

Для ссылки на ячейки на листах:

В нашем примере ниже мы будем ссылаться на ячейку с вычисленным значением между двумя листами. Это позволит нам использовать точно такое же значение на двух разных листах без переписывания формулы или копирования данных.

  1. Найдите ячейку, на которую хотите сослаться, и запишите ее рабочий лист. В нашем примере мы хотим сослаться на ячейку E14 на листе Порядок меню .
  2. Перейдите к нужному рабочему листу .В нашем примере мы выберем рабочий лист Catering Invoice .
  3. Найдите и выберите ячейку , в которой должно отображаться значение. В нашем примере мы выберем ячейку C4 .
  4. Введите знак равенства (=) , лист имя , за которым следует восклицательный знак (!) и адрес ячейки . В нашем примере мы введем = ‘Menu Order’! E14 .
  5. Нажмите Введите на клавиатуре.Появится значение указанной ячейки. Теперь, если значение ячейки E14 изменится на листе заказа меню, оно будет автоматически обновлено на листе счета-фактуры кейтеринга.

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

Если вы ввели имя рабочего листа неправильно, #REF! В ячейке появится ошибка . В нашем примере ниже мы неправильно ввели имя рабочего листа.Чтобы отредактировал, проигнорировал или исследовал ошибку , нажмите кнопку Ошибка рядом с ячейкой и выберите параметр в меню .

Challenge!

  1. Откройте нашу рабочую тетрадь.
  2. Щелкните вкладку Бумажные товары в нижнем левом углу книги.
  3. В ячейке D4 введите формулу, которая умножает цену за единицу в B4 , количество в C4 и ставку налога в E2 .Обязательно используйте абсолютную ссылку на ячейку для ставки налога, потому что она будет одинаковой во всех ячейках.
  4. Используйте дескриптор заполнения , чтобы скопировать только что созданную формулу в ячейки D5: D12 .
  5. Измените ставку налога в ячейке E2 на 6,5%. Обратите внимание, что все ваши ячейки обновлены. Когда вы закончите, ваша рабочая тетрадь должна выглядеть так:
  6. Щелкните вкладку Catering Invoice .
  7. Удалите значение в ячейке C5 и замените его ссылкой на общую стоимость бумажных товаров. Подсказка: Стоимость бумажных товаров указана в ячейке E13 на листе Бумажные товары .
  8. Используйте те же шаги, что и выше, для расчета налога с продаж для каждого элемента на листе Заказ меню . Общая стоимость в ячейке E14 должна обновиться. Затем в ячейке C4 рабочего листа Catering Invoice создайте ссылку на ячейку для только что рассчитанной суммы. Примечание: Если вы использовали нашу рабочую тетрадь, чтобы следить за ходом урока, возможно, вы уже выполнили этот шаг.
  9. Когда вы закончите, рабочий лист Catering Invoice должен выглядеть примерно так:

/ ru / excel2016 / functions / content /

Работа с гиперссылками в Excel

Вы можете использовать функцию ГИПЕРССЫЛКА для создания гиперссылки, которая открывает документ, хранящийся на сетевом сервере, в интрасети или в Интернете. Когда вы щелкаете ячейку, содержащую функцию ГИПЕРССЫЛКА, Excel открывает файл, который хранится в месте ссылки.

Синтаксис

HYPERLINK ( link_location , friendly_name)

Link_location — это путь и имя файла документа, который нужно открыть в виде текста. Link_location может относиться к месту в документе, например к определенной ячейке или именованному диапазону на листе или книге Excel, или к закладке в документе Microsoft Word. Путь может указывать на файл, хранящийся на жестком диске, или путь может быть путем универсального соглашения об именах (UNC) на сервере (в Microsoft Excel для Windows) или путем универсального указателя ресурсов (URL) в Интернете или интранет.

  • Местоположение ссылки может быть текстовой строкой, заключенной в кавычки, или ячейкой, содержащей ссылку в виде текстовой строки.

  • Если переход, указанный в link_location, не существует или не может быть перемещен, при щелчке по ячейке появляется ошибка.

Friendly_name — текст перехода или числовое значение, которое отображается в ячейке.Friendly_name отображается синим цветом и подчеркивается. Если friendly_name опущено, в ячейке отображается link_location как текст перехода.

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

  • Если friendly_name возвращает значение ошибки (например, #VALUE!), В ячейке отображается ошибка вместо текста перехода.

Примеры

В следующем примере открывается рабочий лист с именем Отчет о бюджете.xls, который хранится в Интернете по адресу example.microsoft.com/report и отображает текст «Щелкните для отчета»:

= HYPERLINK («http://example.microsoft.com/report/budget report.xls «,» Щелкните для отчета «)

В следующем примере создается гиперссылка на ячейку F10 на листе с именем Годовой в книге Budget Report.xls, которая хранится в Интернете по адресу example.microsoft.com/report. В ячейке на листе, содержащей гиперссылку, отображается содержимое ячейки D1 ​​в виде текста перехода:

= ГИПЕРССЫЛКА («[http: // example.microsoft.com/report/budget report.xls] Annual! F10 «, D1)

В следующем примере создается гиперссылка на диапазон с именем DeptTotal на листе с именем First Quarter в книге Budget Report.xls, который хранится в Интернет в расположении с именем example.microsoft.com/report. В ячейке на листе, содержащей гиперссылку, отображается текст «Щелкните, чтобы просмотреть итоги по отделу за первый квартал»:

= ГИПЕРССЫЛКА («[http://example.microsoft.ru / report / budget report.xls] Первый квартал! DeptTotal «,» Щелкните, чтобы просмотреть итоги по отделу за первый квартал «)

Чтобы создать гиперссылку на определенное место в документе Microsoft Word, вы должны использовать закладку для определения местоположения вы хотите перейти в документе. В следующем примере создается гиперссылка на закладку с именем QrtlyProfits в документе с именем Annual Report.doc, расположенном по адресу example.microsoft.com:

= HYPERLINK («[http: // example.microsoft.com/Annual Report.doc] QrtlyProfits «,» Ежеквартальный отчет о прибыли «)

В Excel для Windows в следующем примере содержимое ячейки D5 отображается в виде текста перехода в ячейке и открывается файл с именем 1stqtr.xls, который хранится на сервере с именем FINANCE в общей папке Statements. В этом примере используется UNC-путь:

= HYPERLINK («\\ FINANCE \ Statements \ 1stqtr.xls», D5)

В следующем примере открывается файл 1stqtr.xls в Excel для Windows, который хранится в каталоге с именем Finance на диске D, и отображает числовое значение, хранящееся в ячейке h20:

= ГИПЕРССЫЛКА («D: \ FINANCE \ 1stqtr.xls», h20)

В Excel для Windows, в следующем примере создается гиперссылка на область с именем Итоги в другой (внешней) книге Mybook.xls:

= ГИПЕРССЫЛКА («[C: \ My Documents \ Mybook.xls] Итоги»)

В Microsoft Excel для на Macintosh в следующем примере в ячейке отображается «Щелкните здесь» и открывается файл с именем First Quarter, который хранится в папке с именем «Отчеты о бюджете» на жестком диске с именем Macintosh HD:

= HYPERLINK («Macintosh HD: отчеты о бюджете: Первый квартал »,« Щелкните здесь »)

Вы можете создавать гиперссылки на листе для перехода из одной ячейки в другую.Например, если активным листом является лист с именем Июнь в книге с именем Бюджет, следующая формула создает гиперссылку на ячейку E56. Сам текст ссылки — это значение в ячейке E56.

= ГИПЕРССЫЛКА («[Бюджет] июнь! E56», E56)

Чтобы перейти на другой лист в той же книге, измените имя листа в ссылке. В предыдущем примере, чтобы создать ссылку на ячейку E56 на сентябрьском листе, измените слово «июнь» на «сентябрь».»

Формула ГИПЕРССЫЛКИ Excel — Создание интерактивной ссылки

В этом руководстве показано, как использовать функцию Excel HYPERLINK в Excel для создания интерактивной ссылки.

Обзор функций HYPERLINK

Функция ГИПЕРССЫЛКА Создает интерактивную ссылку. Ссылка может относиться к веб-странице, внешнему документу или ячейке в активной книге.

Чтобы использовать функцию листа HYPERLINK Excel, выберите ячейку и введите:

(обратите внимание, как появляются входные данные формулы)

Синтаксис и входы функции HYPERLINK:

= ГИПЕРССЫЛКА (расположение_ссылки, понятное_имя)

link_location — Путь к файлу, веб-адрес или адрес ссылки.Пример: «www.excelbootcamp.com»

friendly_name — Имя ссылки для отображения. Пример: Excel Boot Camp

AutoMacro — Генератор кода VBA

Функция HYPERLINK

Функция ГИПЕРССЫЛКА создает интерактивный ярлык, который перенаправляет пользователей из одного места в другое. Местоположение может быть ячейкой / листом в книге, другой книге, адресом электронной почты, файлом в Интернете или на сетевом сервере.

После создания гиперссылки интерактивный ярлык или [friendly_name] — это текст, который отображается вместо всего URL-адреса.

Функция ГИПЕРССЫЛКА — Ссылка на ячейку

Функция ГИПЕРССЫЛКА позволяет перейти к ссылке на ячейку в той же книге.

= ГИПЕРССЫЛКА («# T6», «Перейти к ячейке T6»)

Функция HYPERLINK — Ссылка на лист

Аналогичным образом функция ГИПЕРССЫЛКА может также перейти к ссылке на лист в той же книге.

= ГИПЕРССЫЛКА («# ‘example3’! A1», «Перейти к листу example3»)

Функция ГИПЕРССЫЛКА — расположение файла

Абсолютный путь

Функция ГИПЕРССЫЛКА позволяет открывать другую книгу или любой файл, имеющийся в вашей системе.

= ГИПЕРССЫЛКА («X: \ Book2.xlsx», «Открытая книга2»)

Примечание: гиперссылка не будет работать, если вы измените местоположение файла

Относительный путь

Если ожидается, что расположение файла изменится, мы можем использовать относительный путь в функции ГИПЕРССЫЛКА.

= ГИПЕРССЫЛКА («файлы Excel \ Book3.xlsx», «Открыть книгу3»)

Функция ГИПЕРССЫЛКИ — адрес электронной почты

Функция ГИПЕРССЫЛКА позволяет отправлять сообщение определенному получателю.

= ГИПЕРССЫЛКА («mailto: [email protected]», «Напишите нам письмо»)

Функция ГИПЕРССЫЛКИ — Веб-страница

Функция HYPERLINK может перенаправлять пользователей на определенную веб-страницу.

= ГИПЕРССЫЛКА («https://www.automateexcel.com/tutorials-practice-tests/», «Автоматизировать Excel»)

Функция ГИПЕРССЫЛКИ, ИНДЕКСА и СООТВЕТСТВИЯ

Функцию ГИПЕРССЫЛКА можно использовать с функцией ИНДЕКС и ПОИСКПОЗ для создания гиперссылок, которые извлекают совпадающее значение и создают для него ярлык.

= ГИПЕРССЫЛКА («#» & ЯЧЕЙКА («адрес», ИНДЕКС (A2: A6, ПОИСКПОЗ (D2, B2: B6,0))), ИНДЕКС (A2: A6, ПОИСКПОЗ (D2, B2: B6,0)) )

Несколько гиперссылок

При использовании функции ГИПЕРССЫЛКА можно редактировать несколько гиперссылок одновременно. Откройте диалоговое окно «Найти и заменить», нажав Ctrl + H.

  1. В диалоговом окне введите старую ссылку в поле «Найти:» и новую ссылку в поле «Заменить на:».
  2. Щелкните раскрывающийся список Искать в: и выберите Формулы.
  3. Теперь нажмите кнопку «Заменить все»

ГИПЕРССЫЛКА в Google Таблицах

Функция ГИПЕРССЫЛКА в Google Таблицах работает точно так же, как и в Excel:

Дополнительные примечания

Используйте функцию ГИПЕРССЫЛКА, чтобы создать гиперссылку на ячейку в книге, внешний файл или веб-страницу. Щелчок по ссылке приведет к переходу к ссылке. В зависимости от типа гиперссылки это может открыть веб-браузер, другой файл или просто перейти к указанной ячейке в книге активации.

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

Если вы используете гиперссылки, открывающие веб-страницы в Excel, вы можете изменить текст, идентифицирующий ссылку (в веб-языке он называется «Якорный текст»).

Обычно вы можете просто ввести URL-адрес в ячейку, и он автоматически преобразуется в гиперссылку. Это отлично работает для ссылки на домены, однако, когда я ссылаюсь на отдельные страницы, ссылка выглядит длинной и некрасивой. К счастью, Excel позволяет выбирать отображаемый текст.Excel называет это friendly_name.

Чтобы управлять текстом, отображаемым для ячейки с гиперссылкой, введите ссылку следующим образом:

Синтаксис

= ГИПЕРССЫЛКА (расположение_ссылки, [понятное_имя])

Пример

= ГИПЕРССЫЛКА («https://www.automateexcel.com/excel», «Автоматическая домашняя страница Excel»)

Вернуться к списку всех функций в Excel

Excel Гиперссылки и функция гиперссылок

Создавайте гиперссылки путем перетаскивания или с помощью функции ГИПЕРССЫЛКА.Удалите гиперссылки вручную или с помощью макроса. Составьте оглавление с макросом.

Создание гиперссылок в Excel

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

Вот четыре способа создания гиперссылки — щелкните ссылку, чтобы перейти к ним. инструкция, ниже:

Введите URL-адрес веб-сайта

Если вы введете в ячейку действующий адрес веб-сайта, Excel автоматически создать гиперссылку при нажатии Enter

Например,

  1. В ячейке B3 введите адрес веб-сайта Contextures: https: // www.contexttures.com
  2. Нажмите Enter

Текст изменится на гиперссылку, и вы можете щелкнуть ссылку, чтобы перейти к веб-сайт Contextures.

Если вы хотите, чтобы гиперссылки не создавались автоматически, выполните инструкции здесь.

Вставить гиперссылку

Еще один способ добавить гиперссылку — использовать встроенную команду Excel — «Вставить гиперссылку».

Начать ссылку

Чтобы использовать команду «Вставить гиперссылку», выполните следующие действия, чтобы начать работу:

  • Выберите ячейку, в которой вы хотите разместить гиперссылку
  • На ленте Excel перейдите на вкладку «Вставка» и выберите команду «Гиперссылка».
    • ИЛИ щелкните ячейку правой кнопкой мыши и выберите Ссылка
    • ИЛИ воспользуйтесь сочетанием клавиш — Ctrl + K
Отображение текста

В верхней части диалогового окна «Вставить гиперссылку» находится поле «Текст для отображения».То, что вы видите в поле «Текст для отображения», зависит от того, что находится в ячейке, в которой вы создаете гиперссылку.

  • Текст в ячейке
  • Пустая ячейка
  • Число в ячейке
  • Число «Текст» в ячейке
Текст в ячейке

Если выбранная ячейка содержит текст, этот текст появится в поле «Текст для отображения».

  • Вы можете редактировать текст для отображения текста
  • Измененный текст появится в ячейке после создания гиперссылки.

Пустая ячейка

Если выбранная ячейка пуста, имя листа и ячейка A1 появятся в поле «Текст для отображения».

  • Вы можете редактировать текст для отображения текста
  • Измененный текст появится в ячейке после создания гиперссылки.

Число в ячейке

Если выбранная ячейка содержит действительное число, вы, , не сможете редактировать текст в поле для отображения.Он затемнен и показывает это сообщение: <<Выбор в документе>>

  • ПРИМЕЧАНИЕ : Вы увидите то же сообщение, если добавляете гиперссылку к фигуре на листе.

Если вы хотите отредактировать текст для отображения:

  • Создайте гиперссылку, а затем измените значение в ячейке

ИЛИ

  • Закройте диалоговое окно «Вставить гиперссылку»
  • Измените номер ячейки на текст (отформатируйте как текст или введите апостроф перед числом)
  • Снова откройте диалоговое окно «Вставить гиперссылку», и поле «Текст для отображения» станет доступным для редактирования.
Текст для отображения затемнен и говорит: <<Выбор в документе>>

«Текст» Число в ячейке

Если выбранная ячейка содержит «текстовое» число (отформатированное как текст или начинающееся с апострофа), этот текстовый номер появится в поле «Текст для отображения».

  • Вы можете редактировать текст для отображения текста
  • Измененный текст появится в ячейке после создания гиперссылки.

Добавить экранную подсказку (необязательно)

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

ПРИМЕЧАНИЕ : Если вы оставите экранную подсказку пустой, полный адрес гиперссылки появится в экранной подсказке.

Чтобы добавить собственный текст для экрана Совет:

  • В окне «Вставить гиперссылку» нажмите кнопку «Подсказка к экрану»
  • Введите текст подсказки на экране — Веб-сайт Google
  • Нажмите ОК

Выберите тип ссылки

На панели «Ссылки на» в окне «Вставить гиперссылку» есть четыре параметра:

  1. Существующий файл или веб-страница
  2. Место в этом документе
  3. Создать новый документ
  4. Адрес электронной почты

В левой части окна «Вставить гиперссылку» щелкните нужный тип ссылки.

Существующий файл или веб-страница

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

  • В списке «Ссылка на» щелкните «Существующий файл» или «Веб-страница».
  • Чтобы создать ссылку на существующий файл, используйте навигацию по папкам, чтобы найти и выбрать нужный файл.
    • ИЛИ, чтобы создать ссылку на веб-страницу, введите ее URL-адрес в поле «Адрес»

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

Если вы создаете гиперссылки в файле Excel, самый быстрый и самый простой способ — использовать перетаскивание.Например, вы могли создать таблицу содержания (TOC) в книге со ссылками на другой лист.

Чтобы увидеть краткий обзор того, как вы можете создавать гиперссылку путем перетаскивания, вы можете посмотреть это короткое видео. Письменный инструкции под видео.

Если у вас возникли проблемы с перетаскиванием на другой лист, перетащите его на ближайший пустой лист. ячейку, затем вырежьте и вставьте на другой лист. Этот анимированный GIF показывает эти шаги.

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

Выполните следующие действия для создания гиперссылок перетаскиванием.

  1. Сначала сохраните книгу, если она еще не была сохранена. (В новом рабочая тетрадь, которая не была сохранена, этот метод не сработает.)
  2. Затем щелкните ячейку, на которую нужно установить ссылку. (Вы собираетесь чтобы перетащить эту ячейку на другой лист, где вы хотите создать гиперссылку)
  3. Наведите указатель мыши на границу ячейки и нажмите правую кнопку мыши.
  4. Чтобы перетащить ячейку на другой лист, нажмите клавишу Alt и перетащите ячейку на вкладку другого листа.
  5. После активации другого листа отпустите клавишу Alt и перетащите в ячейку, где вы хотите гиперссылку
  6. Отпустите правую кнопку мыши и во всплывающем меню нажмите «Создать». Гиперссылка здесь

  7. В ячейке появится гиперссылка с текстом из оригинала. клетка.

Остановить автоматические гиперссылки в Excel

Если вы не хотите, чтобы гиперссылки автоматически создавались при вводе веб-сайта адрес, вы можете отключить функцию гиперссылки. Чтобы увидеть краткий обзор о том, как остановить автоматическое создание автоматических гиперссылок, вы можете посмотреть это короткое видео. Письменные инструкции находятся под видео.

Для отключения опции гиперссылки:

  1. На ленте Excel щелкните вкладку Файл, затем щелкните Параметры
  2. Щелкните категорию Правописание и щелкните Параметры автозамены. кнопка
  3. На вкладке Автоформат при вводе снимите флажок с Интернет и сетевые пути с гиперссылками
  4. Дважды щелкните OK, чтобы закрыть диалоговые окна.

Используйте ГИПЕРССЫЛКУ Функция

Чтобы просмотреть краткий обзор того, как создать гиперссылку с помощью ГИПЕРССЫЛКИ функции вы можете посмотреть это короткое видео.Написано инструкция под видео

Ниже приведены письменные инструкции по использованию функция ГИПЕРССЫЛКА на:

Создать гиперссылку на веб-сайт или электронное письмо

Если в Excel есть список адресов электронной почты или веб-сайтов, вы можете создать гиперссылку для каждой строки в отдельном столбце. В в этом примере в столбце A есть имя, а также URL-адрес веб-сайта или адрес электронной почты в столбце B.Гиперссылки будут созданы в столбце C.

  • Вы будете использовать функцию ГИПЕРССЫЛКА, которая принимает два аргумента:

  • Link_Location : это адрес электронной почты или URL. Для адрес электронной почты, начните с «mailto:», а для URL — начните с https: //
  • Понятное имя : этот аргумент является необязательным и позволяет укажите «понятное» имя для ссылки.Например, вместо длинного URL-адреса можно показать название компании или краткое описание.

Чтобы создать гиперссылку для каждого адреса:

  1. В ячейке C2 введите формулу: = ГИПЕРССЫЛКА (B2, A2)
  2. Нажмите Enter, чтобы увидеть гиперссылку в C3. Он использует ссылку в ячейке B2, а текст в ячейке A2 отображается в ячейке C2 как дружественный Имя.
  3. Скопируйте формулу в оставшиеся строки.

Формула ГИПЕРССЫЛКИ в файл Excel

С формулой ГИПЕРССЫЛКИ вы может ссылаться на место в текущей книге Excel или на другой файл Excel. Посмотрите это видео, чтобы увидеть шаги и письменный инструкции под видео.

Ниже приведены письменные инструкции со ссылками на файл Excel:

Ссылка в том же файле

Вы можете использовать формулу ГИПЕРССЫЛКА, чтобы создать ссылку, которая ведет к ячейке или диапазону где-нибудь в текущей книге.Хитрость заключается в том, чтобы добавить знак фунта (#) в начале адреса.

Вот примеры формул HYPERLINK в той же книге:

Введите адрес

Начните с # , затем введите имя листа и ссылку на ячейку.

Необязательно — введите понятное имя, например «Бюджет», которое будет отображаться в ячейке:

  • = ГИПЕРССЫЛКА («# Sheet2! B2», «Бюджет»)
Использовать ссылки на ячейки

Используйте оператор &, чтобы создать расположение ссылки.

В этом примере имя листа находится в ячейке B5, адрес ячейки находится в C5 и понятное имя находится в ячейке D5.

  • = ГИПЕРССЫЛКА («#» & «‘» & B5 & «‘!» & C5, D5)

Введите имя диапазона

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

  • = ГИПЕРССЫЛКА («# StartCell», «StartCell»)
Следующая пустая ячейка

Перейти к пустой ячейке в конце списка рабочего листа или именованной таблицы.

Рабочий лист

В ячейке B1 формула находит первую пустую ячейку после списка листов в столбце A:

  • = ГИПЕРССЫЛКА («# A» & COUNTA (A: A) + ROW (A3), «Добавить запись»)

Чтобы найти номер пустой строки, сложите количество элементов в столбце A плюс номер строки для начальной ячейки.

Именованная таблица

В ячейке E1 формула находит первую пустую ячейку после названной таблицы в столбце D:

  • = ГИПЕРССЫЛКА («# D» & СУММ (COUNTA (tblDays [Day]), ROW (tblDays [[# Headers], [Day]]]), 1), «Добавить запись»)

Чтобы найти номер пустой строки, формула вычисляет СУММ:

  • Начальная строка таблицы
    • = СТРОКА (tblDays [[# заголовков], [Day]])
  • количество строк данных в таблице
  • Плюс один , чтобы попасть в пустую ячейку под таблицей

Ссылка на другой файл Excel

Чтобы создать ссылку на другой файл Excel в той же папке, просто используйте имя файла в качестве аргумента link_location для ГИПЕРССЫЛКИ функция.

= ГИПЕРССЫЛКА («MyFileC.xlsx», «FileC»)

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

  • В этом примере файл 1 уровня вверх.
  • = ГИПЕРССЫЛКА («.. \ MyFileB.xlsx», «ФайлB»)

  • В этом примере файл находится на 2 уровня выше.
  • = ГИПЕРССЫЛКА («.. \ .. \ MyFileA.xlsx «,» FileA «)

Удалить гиперссылки

Ниже приведены инструкции для двух методов удаления гиперссылок

Удаление вручную гиперссылки,

и макрос для удаления гиперссылок

Удалить гиперссылки вручную

В Excel 2010 или более поздней версии:

В Excel 2010 вы можете быстро удалить гиперссылки из выбранных ячеек. (Спасибо Сэму за этот совет.)

  1. Выберите ячейки, содержащие гиперссылки
  2. Щелкните правой кнопкой мыши любую выделенную ячейку
  3. Нажмите Удалить гиперссылки

Чтобы увидеть шаги, посмотрите это видео с краткими советами по Excel.

В Excel 2007 и более ранних версиях:

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

Макрос

для удаления всех гиперссылок в выбранных ячейках

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

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

 Sub delHyperlinks ()
Автор: Рик Ротштейн
  Выбор.Гиперссылки.Удалить
Конечный переводник 

Укажите на гиперссылку

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

Покажи указывающую руку

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

  • Выберите ячейку (я)
  • На вкладке «Главная» ленты Excel щелкните команду «Перенести текст».

Выберите ячейку гиперссылки

Если вам нужно выделить ячейку гиперссылки, возможно, для редактирования текста, не переходя по ссылке:

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

Создать список листов со ссылками (макрос)

Следующий код создаст Оглавление в файле Excel, со списком всех видимых листов и гиперссылкой на ячейку A1 на каждом листе.Вы можете получить этот код в виде образца файла — найдите загрузку # 3 в списке ниже

Совет. На каждом листе поместите гиперссылку обратно на лист «Оглавление».

 Подложка CreateTOC ()
Dim wsA как рабочий лист
Dim ws как рабочий лист
Dim wsTOC как рабочий лист
Dim lRow As Long
Dim rngList As Range
Dim lCalc As Long
Dim strTOC как строка
Dim strCell как строка
lCalc = Application.Calculation

При ошибке GoTo errHandler
Application.EnableEvents = False
Application.ScreenUpdating = False
Заявление.Расчет = xlCalculationManual

strTOC = "оглавление"
strCell = "A1"
Установить wsA = ActiveSheet

При ошибке Возобновить Далее
Установите wsTOC = Sheets (strTOC)
При ошибке GoTo errHandler

Если wsTOC - ничто, тогда
  Установите wsTOC = Sheets.Add (Before: = Sheets (1))
  wsTOC.Name = strTOC
Еще
  wsTOC.Cells.Clear
Конец, если

С wsTOC
  .Range ("B1"). Value = "Имя листа"
  lRow = 2
  Для каждой страницы в ActiveWorkbook.Worksheets
    Если ws.Visible = xlSheetVisible _
          И ws.Name <> strTOC Тогда
      .Cells (lRow, 2) .Value = ws.Name
      .Гиперссылки. Добавить _
          Якорь: =. Cells (lRow, 2), _
          Адрес: = "", _
          SubAddress: = "'" & ws.Name _
            & "'!" & strCell, _
          Подсказка: = ws.Name, _
          TextToDisplay: = ws.Name
      lRow = lRow + 1
    Конец, если
  Следующий ws
  
  Установите rngList = .Cells (1, 2) .CurrentRegion
  rngList.EntireColumn.AutoFit
  .Rows (1) .Font.Bold = True
Конец с

Application.ScreenUpdating = True
wsTOC.Activate
wsTOC.Cells (1, 2). Активировать

exitHandler:
  Application.EnableEvents = True
  Заявление.ScreenUpdating = True
  Application.Calculation = lCalc
  Установить rngList = Nothing
  Установите wsTOC = Nothing
  Установить ws = ничего
  Установите wsA = Nothing
  Выйти из подводной лодки
errHandler:
  MsgBox «Не удалось создать список»
  Возобновить exitHandler
Конечный переводник 

Создание поддельных гиперссылок в сводной таблице

Гиперссылки не могут быть добавлены в сводную таблицу, даже если есть гиперссылки в исходных данных. В этом видео вы увидите, как создавать поддельные гиперссылки. в сводной таблице, используя несколько строк кода Excel VBA.

Код показан под видео, и он был обновлен для обработки адреса электронной почты тоже.

Создание поддельных гиперссылок в сводной таблице

Для создания поддельных гиперссылок в сводной таблице можно использовать несколько строк. кода в модуле рабочего листа. Скопируйте этот код и измените имя поля, чтобы соответствовать имени в вашей сводной таблице. Вы также можете получить этот код в виде образца файла — найдите загрузку # 2 в списке ниже

 Private Sub Worksheet_SelectionChange _
  (Целевое значение ByVal как диапазон)
Dim selPF как PivotField
Dim strField как строка
Dim strAdd As String
Dim myVal как строка
strField = "Сайт"

При ошибке Возобновить Далее
Установите selPF = Target.PivotField
Если не selPF - это ничто и _
    selPF.Name = strField Тогда
  myVal = Target.Value
  Если InStr (1, myVal, "@")> 0, то
    strAdd = "mailto:"
  Конец, если
  ThisWorkbook.FollowHyperlink _
    Адрес: = strAdd & myVal, _
    NewWindow: = True
Конец, если

Конец подписки
 

Извлечь адрес гиперссылки

Для извлечения адреса можно использовать макрос или пользовательскую функцию (UDF). по гиперссылке.

Макрос

для извлечения адреса в соседнюю ячейку

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

ПРИМЕЧАНИЕ. Гиперссылки, созданные с помощью функции ГИПЕРССЫЛКА, не распознаются как гиперссылки рабочего листа. Распознаются только гиперссылки, которые вы вводите или вставляете.

 Sub ExtractHL_AdjacentCell ()
Тусклый HL как гиперссылка
Для каждого HL в ActiveSheet.
    HL.Range.Offset (0, 1) .Value = HL.Address
Следующий
Конец подписки
 

Создать функцию для Извлечь адрес гиперссылки

Нет встроенной функции для извлечения URL или адреса электронной почты из гиперссылка.Вы можете создать свою собственную функцию Excel — определяемую пользователем Функция (UDF) — извлечь адрес.

Чтобы увидеть краткий обзор того, как вы можете создать свой собственный UDF для извлечения место гиперссылки, вы можете посмотреть это короткое видео. Письменный инструкции под видео.

ПРИМЕЧАНИЕ. Гиперссылки, которые вы создаете с помощью функции ГИПЕРССЫЛКА, не распознаются как гиперссылки рабочего листа.

Создание определяемой пользователем функции гиперссылки (UDF)

Скопируйте этот код в обычный модуль в своей книге, затем используйте Функция HLink в этой книге, как и любая другая функция.Для Например, чтобы найти URL-адрес гиперссылки в ячейке B3, используйте эту формулу:

= HLink (B3)

Примечание: Если выбрано несколько ячеек, формула будет отображаться гиперссылка (если есть) из первой ячейки в выбранном диапазоне.

 Функция HLink (rng As Range) As String
'извлечь URL-адрес из гиперссылки - опубликовал Рик Ротштейн
  Если rng (1) .Hyperlinks.Count Тогда
      HLink = rng.Hyperlinks (1) .Address
  Конец, если
Конечная функция
 

Скрыть листы после гиперссылки

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

Чтобы все выглядело аккуратно, вы можете использовать код, который скрывает каждый лист после того, как вы щелкнете гиперссылку на этом листе. Спасибо Джиму Уильямсу за предложение этой техники.

Следующий код хранится в модуле ThisWorkbook и запускается при щелчке гиперссылки на листе в этой книге. Вы также можете получить этот код в виде образца файла — найдите загрузку # 4 в списке ниже

 Private Sub Workbook_SheetFollowHyperlink _
  (ByVal Sh как объект, ByVal Target как гиперссылка)
'показывает скрытый целевой лист и
  'скрывает лист, на котором была нажата гиперссылка
Dim strWs As String
Dim strTgt как строка
Dim strRng As String
Dim strMsg как строка
Dim lCut As Long
При ошибке GoTo errHandler

strMsg = "Проблема с кодом перехода по гиперссылке"
Выберите Case Sh.Имя
  Кейс "Инструкции", "Мои ссылки"
    Перейти к exitHandler
  Case Else
    strWs = Target.Parent
    Если ActiveSheet.Name <> strWs, то
      'получить целевую ячейку / диапазон
      strTgt = Target.SubAddress
      lCut = InStr (1, strTgt, "!")
      strRng = Вправо (strTgt, Len (strTgt) - lCut)
      
      С листами (strWs)
        strMsg = "Не удалось выбрать цель"
        .Visible = True
        .Активируйте
        .Range (strRng) .Активировать
      Конец с
    Конец, если
    strMsg = "Не удалось скрыть лист"
    Ш.Visible = False
Конец Выбрать

exitHandler:
  Выйти из подводной лодки
errHandler:
  MsgBox strMsg
  Возобновить exitHandler

Конец подписки
 

Загрузить образцы файлов гиперссылок Excel

  1. Общие примеры : Чтобы увидеть примеры гиперссылок, примеры функций гиперссылок и макрос, вы можете скачать образец файла Excel Hyperlink. Файл в формате xlsm, заархивирован и содержит макросы. Давать возможность макросы, если вы хотите использовать образец кода.
  2. Сводная таблица : для кода гиперссылки сводной таблицы загрузите Поддельный файл гиперссылок сводной таблицы. Файл в формате xlsm, и на молнии. Включите макросы, чтобы можно было использовать образец кода.
  3. Содержание : Чтобы получить код списка листов, загрузите файл Оглавления Excel. Файл в формате xlsm, и на молнии. Включите макросы, чтобы можно было использовать образец кода.
  4. Hide Sheets : Чтобы код скрыл листы после щелчка по гиперссылке, загрузите файл Скрыть листы после щелчка по гиперссылке.Файл в формате xlsm, и на молнии. Включите макросы, чтобы можно было использовать образец кода.
  5. Макрос гиперссылки : В этой книге есть макрос, который вставляет формулу гиперссылки в активную ячейку. Макрос запрашивает целевую ячейку и текст гиперссылки для отображения.
    Оставить комментарий

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

    Ваш адрес email не будет опубликован. Обязательные поля помечены *

    © 2019 Штирлиц Сеть печатных салонов в Перми

    Цифровая печать, цветное и черно-белое копирование документов, сканирование документов, ризография в Перми.