Постоянная ячейка в excel: Как сохранить постоянную ссылку на ячейку формулы в Excel?

Умножение столбца чисел на одно и то же число

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel для Windows Phone 10 Еще…Меньше

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

В нашем примере ниже требуется умножить все числа в столбце A на число 3 в ячейке C2. Формула =A2*C2 даст правильный результат (4500) в ячейке B2. Однако копирование формулы в последующие ячейки столбца B не будет работать, поскольку ссылка на ячейку C2 изменяется на C3, C4 и т. д. Так как в этих ячейках нет данных, результат в ячейках от B3 до B6 будет равен нулю.

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

$C$2, как показано в следующем примере.

Символ$ $ Excel, что ссылка на ячейку C2 является «абсолютной», поэтому при копировании формулы в другую ячейку она всегда будет ссылаться на ячейку C2. Чтобы создать формулу:

1. В ячейке B2 введите знак равенства (=).

2. Щелкните ячейку A2, чтобы добавить ее в формулу.

3. Введите символ «звездочка» (*).

4. Щелкните ячейку C2, чтобы добавить ее в формулу.

5. org/ListItem»>

   Введите символ $ перед C и еще один перед 2: $C$2.

6. нажмите клавишу ВВОД.

Совет:  Вместо символа $ можно разместить точку вставки до или после ссылки на ячейку, которую вы хотите сделать «абсолютной», и нажать клавишу F4, которая добавляет символы $.

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

1. Выберите ячейку B2.

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

Формула автоматически копируется на последующие ячейки столбца до ячейки B6.

После копирования формулы в столбце B появляются правильные результаты.

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

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

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

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

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

• org/ListItem»>

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

Например:

Формула	Объект ссылки	Возвращаемое значение
=C2	Ячейка C2	Значение в ячейке C2
=A1:F4	Ячейки A1–F4	Значения во всех ячейках, но после ввода формулы необходимо нажать сочетание клавиш Ctrl+Shift+Enter. Примечание. Эта функция не работает в Excel в Интернете.
=Актив-Пассив	Ячейки с именами «Актив» и «Пассив»	Разность значений в ячейках «Актив» и «Пассив»
{=Неделя1+Неделя2}	Диапазоны ячеек «Неделя1» и «Неделя2»	Сумма значений в диапазонах ячеек «Неделя1» и «Неделя2» как формула массива
=Лист2!B2	Ячейка B2 на листе Лист2	Значение в ячейке B2 на листе Лист2

org/ItemList»>

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

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

3. Выполните одно из следующих действий.

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

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

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

     • Введите имя.

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

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

4. org/ListItem»>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

   • org/ListItem»>

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

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

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

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

3. org/ListItem»>

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

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

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

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

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

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

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

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

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

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

3. org/ListItem»>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

   • org/ListItem»>

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

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

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

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

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

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

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

9 ноября 2017 г. Автор: EduPristine

Поделись со своей сетью!

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

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

По умолчанию Excel использует относительную ссылку на ячейку. В следующем примере вы можете видеть, что в ячейке D2 ссылки B2 и C2 являются относительными.

 

Узнайте о ссылках на ячейки в Excel


 

Когда вы нажмете ввод и перетащите маркер заполнения, вы заметите, что данные были заполнены автоматически. Выберите любую из автоматически заполненных ячеек (в нашем случае D9) и проверьте формулу в строке формул. Ячейка D9имеет ссылки как B9 * C9. Точно так же, если вы проверите другие ячейки, вы заметите, что D3 ссылается на B3 * C3, D4 ссылается на B3 * B4 и так далее.

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

Абсолютная ссылка на ячейку (сохранение постоянного значения в формуле Excel)

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

Когда вы нажмете ввод и перетащите маркер заполнения, вы заметите, что данные были заполнены автоматически. Выберите любую из автоматически заполненных ячеек (в нашем случае C12) и проверьте формулу в строке формул. Вы заметите, что ячейка C12 имеет ссылки как B12*$B$1. Ссылка на ячейку количества изменилась, тогда как ссылка на ячейку цены осталась неизменной, поскольку ссылка на ячейку была заблокирована или абсолютна.

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

Смешанная ссылка на ячейку (сохранение постоянной строки/столбца в формуле Excel)

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

 

Узнайте о ссылках на ячейки в Excel


 

Когда вы нажмете клавишу ввода и перетащите маркер заполнения, вы заметите, что данные были заполнены автоматически. Выберите любую из автоматически заполненных ячеек (в нашем случае D9) и проверьте формулу в строке формул. Вы заметите, что ячейка D9 имеет ссылки как B$2*C9. Цена остается прежней, так как мы заблокировали 2-й ряд.

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

Примечание: Для изменения ссылок вы также можете использовать горячую клавишу F4.

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

Переключение между относительными и абсолютными ссылками

Excel для Microsoft 365 для Mac Excel 2021 для Mac Excel 2019 для Mac Excel 2016 для Mac Excel для Mac 2011 Больше…Меньше

По умолчанию ссылка на ячейку относительно . Например, когда вы ссылаетесь на ячейку A2 из ячейки C2, вы на самом деле ссылаетесь на ячейку, которая находится на два столбца левее (C минус A) и находится в той же строке (2). Формула, содержащая относительную ссылку на ячейку, изменяется при копировании ее из одной ячейки в другую. Например, если скопировать формулу =A2+B2 из ячейки C2 в C3, ссылки на формулы в ячейке C3 сместятся вниз на одну строку и примут вид =A3+B3 .

Если вы хотите сохранить исходную ссылку на ячейку при ее копировании, вы «заблокируете» ее, поставив знак доллара ( $ ) перед ссылками на ячейки и столбцы. Например, если скопировать формулу =$A$2+$B$2 из C2 в D2, формула останется точно такой же. Это абсолютная ссылка.

В менее частых случаях вы можете захотеть сделать ссылку на ячейку «смешанной», поставив перед значением столбца или строки знак доллара, чтобы «заблокировать» столбец или строку (например, $A2 или B$3). . Чтобы изменить тип ссылки на ячейку:

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

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

3. Нажмите + T, чтобы просмотреть комбинации.