Как сделать ссылку на неизменяемую ячейку в excel?

Создание и изменение ссылки на ячейку

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

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

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

данные из одной или нескольких смежных ячеек на листе;

данные из разных областей листа;

данные на других листах той же книги.

Значение в ячейке C2

Значения во всех ячейках, но после ввода формулы необходимо нажать сочетание клавиш Ctrl+Shift+Enter.

Ячейки с именами «Актив» и «Пассив»

Разность значений в ячейках «Актив» и «Пассив»

Диапазоны ячеек «Неделя1» и «Неделя2»

Сумма значений в диапазонах ячеек «Неделя1» и «Неделя2» как формула массива

Ячейка B2 на листе Лист2

Значение в ячейке B2 на листе Лист2

Щелкните ячейку, в которую нужно ввести формулу.

В поле строка формул введите = (знак равенства).

Выполните одно из указанных ниже действий.

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

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

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

Нажмите клавишу F3, выберите имя в поле Вставить имя и нажмите кнопку ОК.

Примечание: Если в углу цветной границы нет квадратного маркера, значит это ссылка на именованный диапазон.

Выполните одно из указанных ниже действий.

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

Если требуется создать ссылку в формула массива (например A1:G4), нажмите сочетание клавиш CTRL+SHIFT+ВВОД.

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

Примечание: Если у вас установлена текущая версия Office 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

На ячейки, расположенные на других листах в той же книге, можно сослаться, вставив перед ссылкой на ячейку имя листа с восклицательным знаком (!). В приведенном ниже примере функция СРЗНАЧ используется для расчета среднего значения в диапазоне B1:B10 на листе «Маркетинг» в той же книге.

1. Ссылка на лист «Маркетинг».

2. Ссылка на диапазон ячеек с B1 по B10 включительно.

3. Ссылка на лист, отделенная от ссылки на диапазон значений.

Щелкните ячейку, в которую нужно ввести формулу.

В поле строка формул введите = (знак равенства) и формулу, которую вы хотите использовать.

Щелкните ярлычок листа, на который нужно сослаться.

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

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

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

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

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

Читать еще:  Как сделать ссылку на название листа excel?

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

Нажмите клавиши CTRL + C или перейдите на вкладку Главная и в группе буфер обмена нажмите кнопку Копировать .

Нажмите клавиши CTRL + V или перейдите на вкладку Главная , в группе буфер обмена нажмите кнопку Вставить .

По умолчанию при вставке скопированных данных появляется кнопка Параметры вставки .

Нажмите кнопку Параметры вставки , а затем выберите команду Вставить связь .

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

Выполните одно из указанных ниже действий.

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

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

В строка формул выделите ссылку в формуле и введите новую ссылку .

Нажмите клавишу F3, выберите имя в поле Вставить имя и нажмите кнопку ОК.

Нажмите клавишу ВВОД или, в случае формула массива, клавиши CTRL+SHIFT+ВВОД.

Примечание: Если у вас установлена текущая версия Office 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

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

Выполните одно из указанных ниже действий.

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

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

На вкладке Формулы в группе Определенные имена щелкните стрелку рядом с кнопкой Присвоить имя и выберите команду Применить имена.

Выберите имена в поле Применить имена, а затем нажмите кнопку ОК.

Выделите ячейку с формулой.

В строке формул строка формул выделите ссылку, которую нужно изменить.

Для переключения между типами ссылок нажмите клавишу F4.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Неизменяемая ячейка в excel

Как закрепить в Excel заголовок, строку, ячейку, ссылку, т.д.

​Смотрите также​​Источник: https://www.ablebits.com/office-addins-blog/2013/10/29/excel-change-row-background-color/​ заказы, ожидаемые в​ от того, где​ оранжевой;​ это совсем не​​Вместо​
​ значений.​Чтобы удалить все стрелки​ ​ крайних подальше от​
​ отследить связи между​ а затем —​ на диапазон ячеек,​ в конце текстового​ она содержит числовое​ в​ то выделяем первую​Рассмотрим,​Перевел: Антон Андронов​
​ течение 1 и​ именно в ячейке​ ​Если заказ доставлен (значение​
​ сложно. Далее мы​C2​В одной из предыдущих​ трассировки на листе,​ активной ячейки. На​ этими ячейками и​ клавишу ВВОД. При​ функция ЯЧЕЙКА возвращает​ значения добавляется «()».​ значение, а не​Excel.​ строку. Нажимаем и​как закрепить в Excel​Автор: Антон Андронов​ 3 дней, розовым​
​ он находится. В​Delivered​ ​ рассмотрим ещё несколько​​Вы можете ввести​
​ статей мы обсуждали,​ на вкладке​ вкладке​ формулами с стрелки​ необходимости измените ширину​ сведения только для​Примечание:​ текст, можно использовать​Для этого нужно​ удерживаем нажатой клавишу​ строку, столбец, шапку​Deadjinn​ цветом, а те,​ примере таблицы на​), то заливка таких​
​ примеров формул и​ ​ ссылку на другую​​ как изменять цвет​
​формулы​формулы​ трассировки, как показано​ столбцов, чтобы видеть​ левой верхней ячейки​ Это значение не поддерживается​ следующую формулу:​ провести обратное действие.​ «Ctrl» и выделяем​ таблицы, заголовок, ссылку,​: Выделяем те ячейки,​ которые будут выполнены​ рисунке ниже столбец​ ячеек должна быть​ парочку хитростей для​ ячейку Вашей таблицы,​
​ ячейки в зависимости​ ​в группе​
​в группе​ на этом рисунке.​ все данные.​ диапазона.​ в Excel Online,​=​Например, чтобы убрать​ следующие строки, ячейки,​ ячейку в формуле,​ которые НЕ будут​ в течение 5​
​Delivery​ зелёной;​ решения более сложных​ значение которой нужно​ от её значения.​Зависимости формул​Зависимости формул​
​Выполните следующие действия для​Данные​В приведенном ниже списке​ Excel Mobile и​ЕСЛИ(​ закрепленную область печати,​
​ т.д.​ картинку в ячейке​ блокироваться.​
​ и 7 дней,​(столбец F) может​Если срок доставки заказа​ задач.​ использовать для проверки​ На этот раз​, нажмите кнопку​нажмите стрелку рядом​ отображения формулы отношений​
​75​ описаны текстовые значения,​ Excel Starter.​ ​ЯЧЕЙКА(«тип», A1) = «v»;​ заходим на закладке​На закладке «Разметка​и другое.​Тыкаем правой кнопкой​ жёлтым цветом. Формулы​ содержать текст «Urgent,​ находится в прошлом​В таблице из предыдущего​ условия, а вместо​ мы расскажем о​Убрать стрелки​
​ с кнопкой​ ​ между ячейками.​
​Привет!​ возвращаемые функцией ЯЧЕЙКА,​»скобки»​ A1 * 2;​ «Разметка страницы» в​ страницы» в разделе​Как закрепить строку и​ по выделенному и​ будут выглядеть так:​ Due in 6​ (значение​ примера, вероятно, было​4​ том, как в​.​Убрать стрелки​Выберите​Формула​ если в качестве​1, если форматированием ячейки​ 0)​
​ раздел «Параметры страницы».​ «Параметры страницы» нажимаем​ столбец в​ выбираем ФОРМАТ ЯЧЕЙКИ​=ИЛИ($F2=»Due in 1 Days»;$F2=»Due​ Hours» (что в​Past Due​ бы удобнее использовать​можете указать любое​ Excel 2010 и​Если Excel звуковых сигналов​и нажмите кнопку​
​файл​Описание​ ​ аргумента «тип_сведений» указано​
​ предусмотрено отображение положительных​Эта формула вычисляет произведение​
​ Нажимаем на кнопку​ на кнопку функции​Excel.​ – ЗАЩИТА.​ in 3 Days»)​ переводе означает –​), то заливка таких​ разные цвета заливки,​ нужное число. Разумеется,​ 2013 выделять цветом​
​ при нажатии кнопки​Убрать стрелки влияющие​>​Результат​ значение «формат», а​ или всех чисел​ A1*2, только если​ функции «Область печати»​ «Область печати». В​В Excel можно​
​Снимаем галочку, «Защищаемая​=OR($F2=»Due in 1 Days»,$F2=»Due​ Срочно, доставить в​ ячеек должна быть​ чтобы выделить строки,​
​ в зависимости от​ строку целиком в​Зависимые​. Чтобы удалить​

Читать еще:  Как сделать знак корня в excel?

​Параметры​=ЯЧЕЙКА(«строка»;A20)​ аргумент ссылки указывает​ в круглых скобках;​ в ячейке A1​ и выбираем из​ появившемся окне нажимаем​ закрепить верхнюю строку​

ЯЧЕЙКА (функция ЯЧЕЙКА)

​ ячейка»​​ in 3 Days»)​ течение 6 часов),​ красной.​ содержащие в столбце​ поставленной задачи, Вы​ зависимости от значения​или​ другой уровень стрелки,​>​Номер строки ячейки A20.​ на ячейку, отформатированную​ во всех остальных​ содержится числовое значение,​ появившегося окна функцию​ на слово «Задать».​ и левый первый​Затем защищаем весь​=ИЛИ($F2=»Due in 5 Days»;$F2=»Due​ и эта строка​И, конечно же, цвет​Qty.​

​ можете использовать операторы​ одной ячейки, а​Влияющие​ нажмите кнопку еще​Advanced.​20​ с использованием встроенного​ случаях — 0.​ и возвращает значение​ «Убрать».​​Когда зададим первую​​ столбец, закрепить несколько​

​ лист​ in 7 Days»)​ также будет окрашена.​ заливки ячеек должен​различные значения. К​ сравнения меньше (​ также раскроем несколько​, Excel или​ раз.​Примечание:​=ЯЧЕЙКА(«содержимое»;A3)​

​ числового формата.​ ​Примечание:​ ​ 0, если в​Чтобы отменить фиксацию​ область печати, в​

​ строк и столбцов,​Если в 2003,​=OR($F2=»Due in 5 Days»,$F2=»Due​Для того, чтобы выделить​ изменяться, если изменяется​ примеру, создать ещё​=$C2​ хитростей и покажем​ найдены все уровни​

​ Если вы используете Excel​Содержимое ячейки A3.​

​Формат Microsoft Excel​​ Это значение не поддерживается​ ячейке A1 содержится​ верхних строк и​ диалоговом окне «Область​ область, т.д. Смотрите​ то СЕРВИС –​ in 7 Days»)​ цветом те строки,​

Шаг #3. Относительные и абсолютные ссылки (изучаем Excel с нуля)

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

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

Программа автоматически «поняла» нашу задумку и значения адресов ячеек в формулы были подставлены правильные, но ка это произошло?

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

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

Поясню на примере.

В ячейку B3 введем цифру 2 , а в ячейку B4 вставим следующую формулу: = B3+3

Какой будет результат вычислений?

Правильно, после нажатия клавиши Enter в ячейке B4 отобразится вычисленное значение — 5 .

Что произойдет, если мы изменим значение ячейки B3 с 2 на 4 ?

Верно! Значение в ячейке B4 автоматически пересчитается и будет равным 7 . Это означает, что формула будет рассчитываться в соответствии с новым значением ячейки В3.

Если бы не было ссылок (в нашем примере это ссылка на ячейку B3), то нам бы пришлось вручную изменять все формулы при изменении какого-то одного ее компонента.

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

Мы с вами научились использовать автозаполнение для тиражирования однотипных формул в таблице. По сути автозаполнение нам позволяет скопировать формулу из одной ячейки и вставить ее в следующую. То же самое происходит и при фактическом копировании. Чтобы в этом убедиться, давайте скопируем ячейку В8. Для этого выделим ячейку (1) и щелкнем на ней правой кнопкой мыши для вызова контекстного меню (2).

Читать еще:  Как сделать разницу в процентах в excel?

Данное меню называется контекстным, так как его содержимое изменяется в зависимости от того, на каком объекте оно вызывается, то есть от контекста конкретной ситуации.

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

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

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

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

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

Если вам удобнее воспринимать информацию в видео-формате, то посмотрите ролик:

Если вам интересен Эксель, то подписывайтесь на этот Дзен-канал или мой видео канал на YouTube.

Ссылка на основную публикацию
Adblock
detector