Общие сведения о формулах в Excel

Создание формулы, ссылающейся на значения в других ячейках

1. Выделите ячейку.

2. Введите знак равенства (=).

   Примечание

   Формулы в Excel начинаются со знака равенства.

3. Выберите ячейку или введите ее адрес в выделенной.

   

4. Введите оператор. Например, используйте - для вычитания.

5. Выберите следующую ячейку или введите ее адрес в выделенной.

   

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

Просмотр формулы

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

• Чтобы увидеть формулу в строке формул, выберите ячейку.

  

Ввод формулы, содержащей встроенную функцию

1. Выделите пустую ячейку.

2. Введите знак = равенства, а затем введите функцию. Например, введите =SUM , чтобы получить общий объем продаж.

3. Введите открываемую скобку (.

4. Выберите диапазон ячеек, а затем введите закрывающая скобка ).

   

5. Нажмите клавишу ВВОД, чтобы получить результат.

Скачивание книги "Учебник по формулам"

Скачайте книгу Начало работы с формулами. Если вы не знакомы с Excel или даже имеете некоторый опыт работы с ним, вы можете ознакомиться с наиболее распространенными формулами Excel в этом туре. С помощью реальных примеров и полезных визуальных элементов вы сможете использовать SUM, COUNT, AVERAGE и VLOOKUP, как профессионал.

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

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

Части формулы Excel

Формула также может содержать любой или все из следующих элементов: функции, ссылки, операторы и константы.

1. Функции. Функция PI() возвращает значение pi: 3,142...
2. Ссылки: A2 возвращает значение в ячейке A2.
3. Константы: числа или текстовые значения, которые вы вводите непосредственно в формулу, например 2.
4. Операторы: оператор ^ (курсор) повышает число до значения, а оператор * (звездочка) умножает числа.

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

Константой является значение, которое не вычисляется и всегда остается неизменным. Например, дата 09.10.2008, число 210 и текст «Прибыль за квартал» являются константами. Выражение или значение, полученное из выражения, не является константой. Если формула в ячейке содержит константы, а не ссылки на другие ячейки (например, имеет вид =30+70+110), значение в такой ячейке изменяется только после редактирования формулы. Как правило, константы лучше размещать в отдельных ячейках, где при необходимости их можно легко изменить, а затем ссылаться на эти ячейки в формулах.

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

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

Стиль ссылок A1

По умолчанию Excel использует стиль ссылок A1, в котором столбцы обозначаются буквами (от A до XFD, не более 16 384 столбцов), а строки — номерами (от 1 до 1 048 576). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, и затем — номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2.

Ячейка или диапазон	Использование
Ячейка на пересечении столбца A и строки 10	A10
Диапазон ячеек: столбец А, строки 10-20.	A10:A20
Диапазон ячеек: строка 15, столбцы B-E	B15:E15
Все ячейки в строке 5	5:5
Все ячейки в строках с 5 по 10	5:10
Все ячейки в столбце H	H:H
Все ячейки в столбцах с H по J	H:J
Диапазон ячеек: столбцы А-E, строки 10-20	A10:E20

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

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

1. Ссылается на лист с именем Marketing
2. Относится к диапазону ячеек от B1 до B10.
3. Восклицательный знак (!) отделяет ссылку на лист от ссылки на диапазон ячеек

Разница между абсолютными, относительными и смешанными ссылками

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

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

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

Абсолютная ссылка на ячейку в формуле, например $A$1, всегда ссылается на ячейку в определенном расположении. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. Если вы копируете или заполняете формулу в строках или столбцах вниз, абсолютная ссылка не настраивается. По умолчанию в новых формулах используются относительные ссылки, поэтому может потребоваться переключить их на абсолютные ссылки. Например, при копировании или заполнении абсолютной ссылки из ячейки B2 в ячейку B3 она остается прежней в обеих ячейках: =$A$1.

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

Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка на столбец имеет вид $A1, $B1 и т. д. Абсолютная ссылка на строку имеет вид A$1, B$1 и т. д. Если положение ячейки, содержащей формулу, изменяется, относительная ссылка изменяется, а абсолютная ссылка не изменяется. Если вы копируете или заполняете формулу в строках или столбцах вниз, относительная ссылка автоматически корректируется, а абсолютная ссылка не настраивается. Например, при копировании или заполнении смешанной ссылки из ячейки A2 в ячейку B3 она автоматически изменяется с =A$1 на =B$1.

Стиль трехмерных ссылок

Удобный способ для ссылки на несколько листов

Трехмерные ссылки используются для анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка содержит ссылку на ячейку или диапазон, перед которой указываются имена листов. В Microsoft Excel используются все листы, указанные между начальным и конечным именами в ссылке. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист13 включительно.

• При помощи трехмерных ссылок можно создавать ссылки на ячейки на других листах, определять имена и создавать формулы с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА, СТАНДОТКЛОНПА, ДИСПР, ДИСП.В, ДИСПА и ДИСППА.
• В формулах массива нельзя использовать трехмерные ссылки.
• Нельзя использовать трехмерные ссылки с оператором пересечения (одно пространство) или в формулах, использующих неявное пересечение.

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

Нижеследующие примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для суммирования значений в ячейках с A2 по A5 на листах со второго по шестой.

• Вставка или копирование. Если вставить листы между листами 2 и 6, Microsoft Excel прибавит к сумме содержимое ячеек с A2 по A5 на новых листах.
• Удалить При удалении листов между Листами 2 и Лист6 Excel удаляет их значения из вычисления.
• Перемещение. Если листы, находящиеся между листом 2 и листом 6, переместить таким образом, чтобы они оказались перед листом 2 или после листа 6, Microsoft Excel вычтет из суммы содержимое ячеек с перемещенных листов.
• Перемещение конечного листа. Если переместить лист 2 или 6 в другое место книги, Microsoft Excel скорректирует сумму с учетом изменения диапазона листов.
• Удаление конечного листа. Если удалить лист 2 или 6, Microsoft Excel скорректирует сумму с учетом изменения диапазона листов.

Стиль ссылок R1C1

Можно использовать такой стиль ссылок, при котором нумеруются и строки, и столбцы. Стиль ссылок R1C1 удобен для вычисления положения столбцов и строк в макросах. При использовании стиля R1C1 в Microsoft Excel положение ячейки обозначается буквой R, за которой следует номер строки, и буквой C, за которой следует номер столбца.

Ссылка	Значение
R[-2]C	Относительная ссылка на ячейку две строки вверх и в одном столбце
R[2]C[2]	Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее
R2C2	Абсолютная ссылка на ячейку, расположенную во второй строке второго столбца
R[-1]	Относительная ссылка на строку, расположенную выше текущей ячейки
R	Абсолютная ссылка на текущую строку

При записи макроса в Microsoft Excel для некоторых команд используется стиль ссылок R1C1. Например, если вы записываете команду, например нажатие кнопки Автосумма , чтобы вставить формулу, которая добавляет диапазон ячеек, Excel записывает формулу с помощью ссылок на стиль R1C1, а не A1.
Вы можете включить или отключить стиль ссылки R1C1, задав или очистив поле стиль ссылки R1C1 проверка в разделе Работа с формулами в категории Формулы диалогового окна Параметры. Чтобы открыть это диалоговое окно, перейдите на вкладку Файл .

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

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

См. также

• Переключение между относительными, абсолютными и смешанными ссылками для функций
• Использование операторов в формулах Excel
• Порядок выполнения действий в формулах Excel
• Использование функций и вложенных функций в формулах Excel
• Определение и использование имен в формулах
• Использование формул массива: рекомендации и примеры
• Удаление формул
• Рекомендации, позволяющие избежать появления неработающих формул
• Поиск ошибок в формулах
• Сочетания клавиш и горячие клавиши в Excel
• Функции Excel (по категориям)