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

Как сохранить постоянную ячейку в Excel за 3 шага (с советами) • BUOM

Автор: редакционная команда Indeed

8 апреля 2022 г.

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

Зачем держать ячейку постоянной в Excel?

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

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

Как сохранить ячейку постоянной в Excel?

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

1.

Решите, какую ячейку оставить неизменной

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

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

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

3. Обозначьте свои константы в формуле

Excel использует знаки доллара («$») для обозначения абсолютных ссылок в формулах. Вы можете добавить абсолютную ссылку в формулу одним из двух способов:

Ручной ввод

Первый способ сохранить постоянную ячейку в формуле — добавить ссылочные символы вручную. Выбрав ячейку с формулой, перейдите в строку формул и используйте курсор, чтобы добавить «$» перед именем столбца и строки вашей постоянной ячейки. Если ваша формула ссылается на ячейки D4 и E4, а вы хотите, чтобы ячейка E4 оставалась неизменной, введите в формуле ссылку на ячейку как «$E$4».

Сочетание клавиш

Второй метод использует сочетание клавиш для создания абсолютной ссылки. При использовании компьютера с Windows клавиша «F4» добавляет ссылку. Выбрав ячейку, содержащую формулу, поместите курсор в ссылку между именами столбца и строки и нажмите клавишу «F4». Некоторые компьютеры требуют, чтобы вы нажимали и удерживали клавишу «Fn», прежде чем нажимать «F4». Этот ярлык добавляет «$» одновременно перед именем столбца и строки.

Вы также можете использовать сочетание клавиш при использовании операционной системы Mac. Ярлык «Command + T» добавляет ссылку на формулу. Как и в Windows, выберите ячейку с формулой и поместите курсор в середину ссылочного термина, затем используйте «Command + T». Ярлык добавляет «$» к обеим половинам имени ячейки. Добавляя символы абсолютной ссылки к константе вашей ячейки, вы гарантируете, что ваша эталонная ячейка останется неизменной при переносе или копировании этой формулы в другие ячейки.

Советы по поддержанию постоянной ячейки

Вот несколько советов по использованию и созданию постоянных ячеек:

Создание смешанных ссылок

Excel использует относительные ссылки, абсолютные ссылки и смешанные ссылки. Смешанная ссылка объединяет два других типа и сохраняет постоянным либо столбец, либо строку. Вы можете создать смешанную ссылку, добавив «$» либо к имени строки, либо к имени столбца, например «A$4» или «$A4». Это позволяет применять формулу к диапазону ячеек без редактирования формулы вручную.

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

Сделать диапазоны постоянными

Формулы Excel могут ссылаться как на отдельные ячейки, так и на диапазоны ячеек. Вы можете сделать диапазон постоянным так же, как вы делаете константой ячейку. В строке формул для нужной формулы добавьте «$» к термину диапазона. Для абсолютной ссылки на диапазон, например «A2:A5», используйте ручной или сокращенный метод, чтобы добавить «$» ко всем частям вашего диапазона, например «$A$2:$A$5». Вы также можете создавать смешанные ссылки, сохраняя константу столбца с помощью «$A2:$A5» или сохраняя константу строки с «A$2:A$5».

Используйте функцию переключения

Сочетания клавиш «F4» и «Command + T» имеют вторую функцию при создании постоянных ячеек в формулах. После использования ярлыка для добавления абсолютной ссылки вы можете использовать ярлык для переключения формата ссылок. Нажатие клавиши еще два или три раза приводит к ротации термина формулы через смешанные ссылки или относительные ссылки. Например, если ваша формула ссылается на D5, и вы использовали сочетание клавиш «F4» для создания «$D$5», то повторное нажатие клавиши даст вам «$D5», «D$5» или «D5» в строке формул.

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

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

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

Формулы. Microsoft Office

Формулы

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

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

=A1*2

После нажатия кнопки ввода во второй клеточке столбца появится число 2. Теперь выделите мышкой клетку, которой мы присвоили формулу, подцепите мышкой правый нижний угол этой клетки и, держа нажатой левую кнопку мыши, потяните ее вниз по столбцу, до тех пор, пока курсор не остановится на клетке с нужным вам номером, например, с номером А64. Теперь, отпустив кнопку мыши, вы присвоите всем выделенным вами ячейкам формулу первоначальной клетки с одной маленькой поправкой: в формуле для каждой новой клетки в столбце будет фигурировать именно ее клетка-предшественница, а не первоначальная клетка А1. Это преобразование Excel выполнит автоматически.

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

Итак, нам нужно узнать сумму значений ячеек в диапазоне с A1 по A64. Формула на этот случай будет такая:

=СУММ(А1:А64)

Знак «:» используется в Excel для сокращенного обозначения интервала ячеек, то есть, «A1:A64» означает «от ячейки A1 до ячейки A64».

Для того чтобы формула преобразовалась в числовое значение, т. е. в сумму чисел во всех ячейках этого столбца, нажмите клавишу Enter. Теперь в ячейке стоит результат вычислений по заданной вами формуле. Если выделить эту ячейку курсором, то формула отобразится в строке формул. Если в дальнейшем вам потребуется изменить данные в ячейках указанного диапазона, то программа автоматически пересчитает числа по заданной формуле и самостоятельно изменит итоговую сумму. Теперь самое интересное. Для того чтобы вычислить сумму в остальных столбцах, совершенно необязательно проводить те же операции, которые вы выполняли, суммируя данные. Все можно сделать гораздо проще. Выделите курсором ячейку с суммой, «зацепите» маркер заполнения и протяните его через соседние ячейки. После этой операции программа сама скопирует формулу в отмеченные ячейки, соотнесет ее с числами того или иного столбца, автоматически изменяя ссылки, проведет вычисление и мгновенно выдаст вам готовый результат.

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

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

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

? Значения или ссылки на ячейки, с которыми выполняются расчеты.

? Операторы (простые математические операции, такие как сложение или умножение).

Операторы Excel

Например, простейшая формула имеет вид:

=А1+А2

Эта формула складывает значения ячеек А1 и А2. При создании формул Excel важно помнить о приоритете операторов.

При обработке формулы Excel выполняет операции слева направо в следующем порядке:

? Все операции в круглых скобках.

? Показательные (экспоненциальные) выражения или операторы.

? Остальные операции согласно правилам арифметики.

Ввод формул

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

Чтобы ввести формулу, выполните следующее:

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

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

Введите оператор, например, знак плюс (+).

Щелкните по следующей ячейке, включаемой в формулу. Опять введите оператор, выделите следующую ячейку и т. д. Продолжайте выделять ячейки и вводить операторы, пока не завершите формулу.

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

Редактирование формул

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

Копирование формул

Благодаря относительной адресации ячеек, Excel позволяет скопировать готовую формулу в смежные ячейки; при этом адреса ячеек будут изменены автоматически. Выделите ячейку с формулой. Установите указатель мыши на черный квадратик в правом нижнем углу курсорной рамки (указатель примет форму крестика). Нажмите левую кнопку и смещайте указатель вниз, так чтобы смежные ячейки были выделены пунктирной рамкой. Отпустите кнопку мыши. Excel скопирует формулу в нужные ячейки, причем номера строк (столбцов) будут автоматически изменены.

Адреса ячеек в разных листах

При работе с Excel мы можем оперировать не только отдельными ячейками или даже таблицами-листами, но и целыми книгами. И вот представьте себе, что перед нами возникает необходимость «связать» несколько различных таблиц в книгу и снабдить их перекрестными ссылками. То есть результат в ячейке одного листа-таблицы будет напрямую зависеть от ячейки другого. Использовать адреса ячеек в формулах мы уже умеем – но только в пределах одного листа. А как же быть в этом случае? Если вам необходимо использовать ссылку на ячейку в другом листе, включите в формулу его имя, поставив между ним и адресом ячейки восклицательный знак.

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

Для упрощения копирования и перемещения формул в Excel применяется концепция, известная как относительная адресация. Excel интерпретирует адреса ячеек, введенные в формулу, не как ссылку на их постоянное расположение, а как ссылку на их относительное расположение. При некоторых операциях копирования, удаления, вставки Excel автоматически изменяет этот адрес в формулах. Например, если первые 3 ячейки столбца А заполнены следующими данными:

А1: 50

А2: 100

А3: =А1+А2

то при работе с формулой в ячейке А3 Excel понимает ее не как «взять число из ячейки А1 и прибавить его к числу из ячейки А2». Вместо этого система понимает данную формулу как «взять число из ячейки, расположенной в том же столбце, что и текущая, на две строки выше, и прибавить его к числу из ячейки, расположенной. ..». При копировании данной формулы в другую ячейку, например, в В3, принцип останется тот же: «взять число из ячейки, расположенной в том же столбце, что и текущая, на две строки выше, и прибавить его к…». То есть, при копировании этой формулы из ячейки А3 в ячейку В3 она автоматически преобразится в формулу =В1+В2. Такой тип ссылок избавляет от необходимости снова и снова вводить одинаковые формулы – их можно просто копировать. Однако, в некоторых формулах необходимо сохранять ссылку на точно указанную ячейку, иными словами, ссылка на ячейку не должна зависеть от расположения формулы на листе. Например, в рабочем листе имеется список товаров с их долларовыми ценами, и есть одна ячейка, например А1, в которой записан текущий курс рубль/$. При создании формулы необходимо всегда ссылаться на ячейку А1, и эта ссылка не должна изменяться при копировании формулы. В таком случае следует применять ссылку иного типа – абсолютную ссылку. Абсолютная ссылка остается неизменной при изменении адреса ячейки с формулой. При помощи абсолютных ссылок можно заставить Excel при копировании формул изменять ссылки на столбцы, но сохранять ссылку на строку постоянной, либо изменять ссылки на строки, но сохранять ссылку на столбец постоянной, либо сохранять постоянными ссылки – как на столбец, так и на строку. Чтобы заменить относительную ссылку на абсолютную, введите знак доллара ($) перед той частью ссылки, которая должна стать абсолютной.

Примеры:

$А1 Всегда ссылается на столбец А, ссылка на строку может изменяться.

А$1 Всегда ссылается на строку 1, ссылка на столбец может изменяться.

$А$1 Всегда ссылается на ячейку А1.

Ссылки вида А1 называются относительными, вида $А$1 – абсолютными, вида $А1 или А$1 – смешанными.

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

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

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

Нажмите клавишу F4. Excel немедленно установит знаки доллара перед буквой, обозначающей столбец, и номером строки в ссылке на ячейку. Знак $ можно ввести вручную.

Продолжайте строить формулу вплоть до завершения, нажимая клавишу F4 для вставки Повторно, нажимая клавишу F4, можно циклически переходить от одного типа ссылок к другим. Введите, например, относительную ссылку С6. Теперь нажимайте клавишу F4 – ссылка будет иметь вид: $C$6, C$6, $C6 и С6.

Данный текст является ознакомительным фрагментом.

Математические формулы

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

Формулы

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

1.7. Формулы

1.7. Формулы
Формулой считают любую последовательность не менее чем двух символов, которая не является словом (названием, аббревиатурой) в русском или каком-либо другом языке. Например, «MATLAB» является словом, «/(л:(0))» – нет.Формулы также нумеруются внутри одного раздела.

4.1. Математические формулы

4.1. Математические формулы
В текстовом редакторе Word существует специальный инструмент для работы с формулами – редактор формул. С его помощью можно создавать сложные объекты, выбирая символы с панели инструментов и задавая переменные и числа. При этом размер шрифтов,

Глава 6 Формулы и функции

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

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

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

«Формулы в формулах»

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

Формулы

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

16.3. Формулы

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

2.4. Формулы

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

Таинственные формулы прогресса

Таинственные формулы прогресса
Автор: Виктор ШепелевЛетом 1975 года в коридорах исследовательского центра Xerox PARC, где шла работа над компьютером Alto, стало неожиданно многолюдно. В учреждение внезапно зачастили родственники, друзья, знакомые и соседи работавших в центре

14.8. Формулы

14.8. Формулы
Ячейка может содержать не только статическое значение, но и формулу, т. е. значение ячейки будет вычисляться по заданной вами формуле. Давайте сразу перейдем к примерам формул — как говорится, лучше один раз увидеть, чем сто раз

Математические формулы для женщин

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

5.2.6. Формулы

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

Формулы

Формулы
Кроме тех типов данных, которые указаны в поле Числовые форматы диалогового окна Формат ячеек (см. рис. 4.23), существует еще один формат данных, который называется формулой и может храниться в ячейке. Этот формат указывает программе Excel, что прежде, чем поместить

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

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

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

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

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

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

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

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

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

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

1. Выберите ячейки, которые вы хотите использовать.

2. Введите знак равенства и свою константу. Разделяйте значения в константе точкой с запятой, а не запятыми, а если вы вводите текст, заключайте его в двойные кавычки. Например: ={«Квартал 1″;»Квартал 2″;»Квартал 3»}

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

   С точки зрения гиков это одномерная вертикаль константа.

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

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

1. Выберите ячейки, которые вы хотите использовать.

2. Введите знак равенства и вашу константу, но на этот раз вы разделяете значения запятыми, а не точками с запятой. Например: = {1,2,3,4,5}

3. org/ListItem»>

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

   В компьютерных терминах это одномерная горизонтальная константа .

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

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

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

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

   = {1,2,3,4;5,6,7,8;9,10,11,12}

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

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

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

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

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

  =СУММ(A1:E1*{1,2,3,4,5})

  Значение 85 появится в ячейке A3.

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

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

=СУММ({3,4,5,6,7}*{1,2,3,4,5})

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

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

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

• Возможно, вы выбрали диапазон ячеек, который не соответствует количеству элементов в вашей константе. Например, если вы выберете столбец из шести ячеек для использования с константой из пяти ячеек, в пустой ячейке появится ошибка #Н/Д. Если вы не выберете достаточно ячеек, Excel пропустит значения, которым нет соответствующей ячейки.

• Подробнее о формулах массива:

  • Введите формулу массива

  • org/ListItem»>

    Развернуть формулу массива

  • Удалить формулу массива

  • Правила изменения формул массива

Верх страницы

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

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

/en/excelformulas/complex-formulas/content/

Введение

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

Дополнительно: загрузите наш пример файла для этого урока.

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

1. Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку D2 .
2. Введите формулу для расчета требуемого значения. В нашем примере мы введем =B2*C2 .
3. Нажмите Введите на клавиатуре. Формула будет рассчитана , и результат будет отображаться в ячейке.
4. Найдите маркер заполнения в правом нижнем углу нужной ячейки. В нашем примере мы найдем дескриптор заполнения для ячейки 9.0187 Д2 .
5. Нажмите, удерживайте и перетащите маркер заполнения по ячейкам, которые вы хотите заполнить. В нашем примере мы выберем ячейки D3:D12 .
6. Отпустите кнопку мыши. Формула будет скопирована в выбранные ячейки с относительными ссылками , и значения будут рассчитаны в каждой ячейке.

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

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

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

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

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

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

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

В нашем примере мы будем использовать 7,5% ставку налога с продаж в ячейке E1 для расчета налога с продаж для всех товаров в столбце D . Нам нужно будет использовать абсолютную ссылку на ячейку $E$1 в нашей формуле. Поскольку в каждой формуле используется одна и та же налоговая ставка, мы хотим, чтобы эта ссылка оставалась постоянной, когда формула копируется и заполняется другими ячейками в столбце D.

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

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

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

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

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

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

Для ссылки на ячейки между рабочими листами:

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

1. Найдите ячейку, на которую вы хотите сослаться, и обратите внимание на ее рабочий лист. В нашем примере мы хотим сослаться на ячейку 9.0187 E14 на рабочем листе заказа меню .
2. Перейдите к нужному рабочему листу . В нашем примере мы выберем рабочий лист Счет-фактура общественного питания .
3. Появится выбранный рабочий лист .
4. Найдите и выберите ячейку , в которой должно отображаться значение. В нашем примере мы выберем ячейку B2 .
5. Введите знак равенства (=) , лист имя , за которым следует цифра 9.0187 восклицательный знак ( ! ) и адрес ячейки . В нашем примере мы введем =’Порядок меню’!E14 .
6. Нажмите Введите на клавиатуре. Появится значение указанной ячейки. Если значение ячейки E14 изменится на рабочем листе заказа меню, оно будет автоматически обновлено на рабочем листе счетов за питание.

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

Вызов!

1. Откройте существующую книгу Excel. Если вы хотите, вы можете использовать файл примера для этого урока.
2. Создайте формулу, использующую относительную ссылку . Если вы используете пример, используйте маркер заполнения , чтобы заполнить формулу в ячейках с E4 по E14 . Дважды щелкните ячейку, чтобы увидеть скопированную формулу и относительные ссылки на ячейки.
3. Создайте формулу, использующую абсолютный ссылка . Если вы используете пример, исправьте формулу в ячейке D4 так, чтобы она ссылалась только на налоговую ставку в ячейке E2 как на абсолютную ссылку , а затем используйте дескриптор заполнения, чтобы заполнить формулу из ячеек D4 в .