Как сделать константу в Excel

Обновлено: 04.07.2024

При работе с электронными таблицами необходимо знать об относительных и абсолютных ссылках на ячейки.

Вот проблема: когда вы КОПИРУЕТЕ ФОРМУЛУ, содержащую ссылки на ячейки, что происходит со ссылками на ячейки?

Обычно ССЫЛКИ НА КЛЕТКИ ИЗМЕНЯЮТСЯ! Если скопировать формулу на 2 строки вправо, то ссылки на ячейки в формуле сместятся на 2 ячейки вправо. Если скопировать формулу на 3 строки вниз и на 1 строку влево, то ссылки на ячейки в формуле сместятся на 3 строки вниз и на 1 строку влево. Они называются относительными ссылками на ячейки, поскольку они изменяются относительно того места, куда вы копируете формулу.

Если вы не хотите, чтобы ссылки на ячейки менялись при копировании формулы, сделайте эти ссылки на ячейки абсолютными ссылками на ячейки. Поместите «$» перед буквой столбца, если вы хотите, чтобы она всегда оставалась неизменной. Поместите «$» перед номером строки, если вы хотите, чтобы он всегда оставался неизменным. Например, «$C$3» относится к ячейке C3, а «$C$3» будет работать точно так же, как «C3», если вы скопируете формулу. Примечание: при вводе формул вы можете использовать клавишу F4 сразу после ввода ссылки на ячейку для переключения между различными относительными/абсолютными версиями этого адреса ячейки.

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

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

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

Карин Стилл

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

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

Это наиболее широко используемый тип ссылки на ячейку в формулах. Относительные ссылки на ячейки — это базовые ссылки на ячейки, которые корректируются и изменяются при копировании или использовании автозаполнения.

=СУММ(B5:B8), как показано ниже, изменяется на =СУММ(C5:C8) при копировании в следующую ячейку.

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

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

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

Более сложный пример:

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

Проверьте формулу в ячейке E4. Сделав ссылку на первую ячейку $C4, вы предотвратите изменение столбца при копировании, но позволите строке измениться при копировании вниз, чтобы приспособиться к снижению цен на различные товары. Сделав ссылку на последнюю ячейку A$12, вы предотвратите изменение номера строки при копировании вниз, но позволите столбцу измениться и отразить скидку B при копировании. Смущенный? Посмотрите на рисунок ниже и результаты ячеек.

Теперь вы можете подумать, а почему бы просто не использовать 10% и 15% в реальных формулах? Разве это не было бы проще? Да, если вы уверены, что процент скидки никогда не изменится, что крайне маловероятно. Скорее всего, в конечном итоге эти проценты необходимо будет скорректировать. Ссылаясь на ячейки, содержащие 10% и 15%, а не на фактические числа, когда процент изменяется, все, что вам нужно сделать, это изменить процент один раз в ячейке A12 и/или B12 вместо того, чтобы перестраивать все ваши формулы. Excel автоматически обновит цены со скидками, чтобы отразить изменение процента скидки.

Краткий обзор использования абсолютных ссылок на ячейки:

$A1	Разрешает изменение ссылки на строку, но не ссылка на столбец.
A$1	Разрешает изменять ссылку на столбец, но не на строку.
$A$1	Не позволяет изменять ни столбец, ни ссылку на строку.

Существует ярлык для размещения абсолютных ссылок на ячейки в ваших формулах!

При вводе формулы после ввода ссылки на ячейку нажмите клавишу F4. Excel автоматически делает ссылку на ячейку абсолютной! Продолжая нажимать F4, Excel будет циклически перебирать все возможности абсолютной ссылки. Например, в первой формуле абсолютной ссылки на ячейку в этом руководстве, =B4*$B$10, я мог бы ввести =B4*B10, а затем нажать клавишу F4, чтобы изменить B10 на $B$10.Продолжая нажимать F4, вы получите 10 B$, затем B10 и, наконец, B10. Нажатие F4 изменяет только ссылку на ячейку непосредственно слева от точки вставки.

Я надеюсь, что этот учебник сделал эти типы ссылок на ячейки «абсолютно» понятными!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

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

Вы можете использовать константы в формулах массива или сами по себе.

В формуле массива введите открывающую фигурную скобку, нужные значения и закрывающую скобку. Вот пример: =СУММ(A1:E1*)

Константа находится внутри фигурных скобок (<)>, и да, вы действительно вводите эти фигурные скобки вручную.

Введите оставшуюся часть формулы и нажмите Ctrl+Shift+Enter.

Формула будет выглядеть примерно так: <=SUM(A1:E1*<1,2,3,4,5>)>, а результаты будут выглядеть так:

Формула умножала A1 на 1, B1 на 2 и т. д., избавляя вас от необходимости вводить числа 1, 2, 3, 4, 5 в ячейки рабочего листа.

Используйте константу для ввода значений в столбце

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

Выберите нужные ячейки.

Введите знак равенства и константу. Разделяйте значения в константе точкой с запятой, а не запятыми, а если вы вводите текст, заключайте его в двойные кавычки. Например: =

Нажмите Ctrl+Shift+Enter. Константа выглядит так:

Говоря компьютерными науками, это одномерная вертикаль константа.

Используйте константу для ввода значений в строку

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

Выберите нужные ячейки.

Введите знак равенства и свою константу, но на этот раз вы разделяете значения запятыми, а не точками с запятой. Например: =

Нажмите Ctrl+Shift+Enter, и константа будет выглядеть так:

Говоря компьютерными науками, это одномерная горизонтальная константа.

Используйте константу для ввода значений в несколько столбцов и строк

Выберите нужные ячейки.

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

Введите знак равенства и константу. В этом случае разделяйте значения в каждой строке запятыми и используйте точку с запятой в конце каждой строки. Например:

Нажмите Ctrl+Shift+Enter и:

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

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

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

В любую пустую ячейку введите (или скопируйте и вставьте) эту формулу, а затем нажмите Ctrl+Shift+Enter:

В ячейке A3 отображается значение 85.

Что случилось? Вы умножили значение в ячейке A1 на 1, значение в ячейке B2 на 2 и т. д., а затем функция СУММ добавила эти результаты. Вы также можете ввести формулу как =СУММ(A1*1,B1*2,C1*3,D1*4,E1*5)

При желании вы можете ввести оба набора значений в качестве констант массива:

Чтобы попробовать это, скопируйте формулу, выберите пустую ячейку и вставьте формулу в строку формул, а затем нажмите Ctrl+Shift+Enter. Вы видите тот же результат.

Примечания. Ищите следующие проблемы, если ваши константы не работают:

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

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

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

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

Пример сохранения постоянного значения в Excel

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

Далее у нас есть ячейка B1, которая содержит фактическую процентную ставку, мы представим наш процент в виде десятичной дроби, чтобы 10% стали 0,1.

Теперь ниже у нас есть три столбца:

Основная сумма
Простые проценты
Итого (основная сумма + простые проценты)

в A3 мы помещаем 1000 долларов США, в A4 мы помещаем 2000 долларов США, затем выделяем обе ячейки, захватываем нижний правый угол и перетаскиваем вниз еще 8 строк, так что теперь у вас есть значения от 1000 до 10 000 долларов США.

Далее в ячейке B3 мы вводим формулу для расчета процентов путем умножения нашего первого значения (1000 долларов США в ячейке A3) на процентную ставку (0,1 или 10 % в ячейке B1).

В ячейке должно быть указано 100 долларов США.

Теперь, как и при создании 10 строк с шагом в 1000 долларов, мы можем захватить правый нижний угол и перетащить его вниз. Проблема в том, что обе ссылки будут изменять ячейки, но мы хотим, чтобы только первая (основная сумма) вычислялась со следующей ячейки вниз (A4, A5 и т. д.). мы хотим, чтобы ячейка процентной ставки всегда рассчитывалась из B1 (а не из B2, B3 и т. д., как естественно предполагается в электронной таблице).

Итак, чтобы исправить это, мы добавим «$» перед буквой столбца и номером строки для нашей ячейки процентной ставки, изменив формулу в B3, чтобы она теперь выглядела следующим образом:

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

Наконец, чтобы закруглить ситуацию, давайте проведем еще один форум в C3, чтобы добавить основную часть к проценту, используя простую формулу =(A3+B3) в C3.

Теперь возьмите и перетащите C3 вниз, и вы увидите, как увеличиваются приращения, и теперь мы знаем, сколько простых процентов мы получим за год на 1000–10 000 долларов США.

Хотите, чтобы все изменилось? Измените 0,1 в ячейке B1 на 0,05 (5%), и все изменится по всем направлениям.

Какие ваши любимые советы и рекомендации по Excel? Нужна помощь, чтобы понять что-то в электронной таблице? Свяжитесь со мной в Твиттере @vsellis или в Google+ по адресу gplus.to/scottellis.

Узнавайте что-то новое каждый день!

Как сделать константу в Excel

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

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

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

Используйте константу для ввода значений в столбце

Используйте константу для ввода значений в строку

Используйте константу для ввода значений в несколько столбцов и строк

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

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

Пример сохранения постоянного значения в Excel

О Скотте Эллисе

Комментарии