Формула в Excel - это настолько мощный инструмент, что если вы с ним еще не работали, то вы наверняка удивитесь, как вы до сих пор жили без него.Формула должна обязательно начинаться с знака =, в формуле могут использоваться знаки арифметических операций +, -, *, /, числа и имена ячеек, а также встроенные функции редактора.
Имена ячеек в формулах можно набирать вручную. С целью экономии времени и во избежание ошибок при построении формул можно задавать ссылки на ячейки с помощью мыши. Для этого после ввода каждой операции (арифметического действия) вместо того, чтобы набирать вручную имя ячейки, нужно кликнуть мышкой на соответствующей ячейке - и ее имя появится в формуле. При использовании этого метода вам нужно поступить следующим образом: ввести в ячейку знак =, кликнуть мышкой на ячейке B1 (вы увидите, как она выделится), ввести знак *, кликнуть мышкой на С1 и нажать Enter.Например, вы хотите подсчитать бюджет будущей вечеринки. Для этого вам нужно в столбик Excel ввести табличку с продуктами, которые вы хотите купить и ценами. В третьей колонке указано количество каждого продукта, которое вы планируете закупить.
В четвертой колонке введите формулу =B1*C1 и нажмите Enter.
Если в ячейку ввести формулу и нажать клавишу Enter, то в этой ячейке будет записан числовой результат вычисления по формуле. Если активизировать ячейку с формулой, то выше имен столбиков в строке формул будет записан текст формулы, по которой была вычислена ячейка.
Изменить формулу можно несколькими способами:
- дважды нажать левую клавишу мышки на ячейке, внести изменения в формулу и нажать Enter
- внести изменения в формуле в строке формул и нажать Enter
- активизировать ячейку , нажать клавишу F2, внести необходимые изменения и нажать Enter.
Если изменить содержимое ячейки, которая использована в формуле, то изменится и результат, вычисленный по формуле, соответственно вычисленным числовым данным.
В другую ячейку вы можете ввести формулу, подсчитывающую общую сумму расходов, то есть D1+D2+D3+D4+D5.
А теперь представьте, что ваши планы изменились, и вы хотите купить не 5, а 6 килограмм мяса, но взять меньше чипсов. Внесите все изменения в третью колонку, и вы увидите, как изменятся и формулы, и формулы с суммой по каждой позиции, и общая сумма.
Чудесный инструмент, не правда ли?
Как видите, формула правильно скопировалась для других строчек, и значения подсчитались абсолютно верно. Обратите внимание: если автозаполнение происходит по столбцам, то изменяется номер столбца. Если автозаполнение происходит по строкам, то в формуле изменяется номер строки.
Таким образом, если в вашей таблице необходимо провести одинаковые вычисления, воспользуйтесь функцией автозаполнения, чтобы повторно не вводить одинаковые формулы.
Относительный адрес указывает на ячейку, исходя из ее положения относительно ячейки, в которой находится формула. При автозаполнении формулы с относительным адресом ячейки формула изменяется в процессе автозаполнения.
Именно это свойство относительной адресации используется при копировании формул в другие ячейки.
При записи формул может возникнуть ситуация, когда адрес ячейки, которая используется в формуле, при автозаполнении не должен изменяться. В таком случае нужно использовать абсолютную адресацию ячеек.
Абсолютный адрес указывает на ячейку на основе ее фиксированного положения на рабочем листе. Признаком абсолютного адреса является значок $, который записывается перед номерами столбика и строки (например, $А$10).
При автозаполнении формулы с абсолютным адресом формула не будет изменяться - независимо от того, как было осуществлено автозаполнение. Для более быстрого преобразования относительного адреса в абсолютный при построении формулы нужно после ввода имени ячейки нажать клавишу F4.
А теперь давайте переделаем наш пример. Представьте себе, что вы не знаете, сколько людей планируется на вашей вечеринке, и вы составили табличку из расчета на одного человека.
Сбоку в ячейке F1 укажите предполагаемое количество гостей. Теперь вы решили подсчитать затраты. Если вы будете действовать старым способом создания формул, то уже очень скоро убедитесь, что на чипсы вам нужно … 0 руб.
Дело в том, что при автозаполнении формулы адрес ячейки F1 стал меняться, в ячейке F2 ничего не записано, то есть ноль, поэтому и появились нули. При использовании ячейки F1 нужно применить абсолютную адресацию ячеек, то есть ваша правильная формула должна иметь вид B1*C1*$F$1.
Выполнив автозаполнение, вы убедитесь, что значение $F$1 осталось в таком же виде во всех формулах.
При работе в редакторе можно использовать и смешанные адреса, которые содержат как относительную, так и абсолютную форму записи адреса. Клавиша F4 позволяет превратить абсолютный адрес в смешанный. То есть нажатие клавиши F4 на ячейке изменяет адрес циклически - сначала на абсолютный, потом на смешанный. В смешанном адресе символ $ указывает ту часть адреса, которая не будет изменяться. В адресе А$1 при автозаполнении будет изменяться номер столбика, а номер строки будет оставаться неизменным.
При построении сложных таблиц иногда бывает необходимо использовать в формуле ячейку, расположенную на другом рабочем листе. Для того, чтобы ввести в некоторую ячейку рабочего листа ссылку на ячейку другого рабочего листа, нужно в ячейке, где должна находиться формула, ввести знак =, перейти на нужный рабочий лист, выбрать нужную ячейку, кликнуть на ней мышкой и нажать клавишу Enter. Будет активизирован предшествующий рабочий лист, и в ячейке появится формула
= имя листа! адрес ячейки
Часть формулы, которая содержит ссылку на другой рабочий лист отделена символом ! от ссылки на ячейку.
При необходимости эту формулу можно изменить. Для этого нужно активизировать ячейку, нажать клавишу F2 и внести необходимые изменения.
Ссылка на ячейку другой рабочей книги выполняется аналогично. В этом случае нужно открыть обе рабочие книги и упорядочить их определенным способом в окне Excel, воспользовавшись пунктами меню "Окно". Во время ввода формулы в первой рабочей книге нужно активизировать окно второй рабочей книги и выбрать ячейку, которую нужно использовать в формуле. При этом в формуле с использованием такой ссылки имя рабочей книги будет записано в квадратных скобках.
Подытожим. Основные правила работы с формулами в редакторе Excel состоят в следующем:
- Если нужно выполнить вычисления по одной формуле для некоторого диапазона значений, то нужно построить формулу, применив относительный адрес для первого набора числовых данных, а потом выполнить автозаполнение этой формулы на весь диапазон значений.
- Если нужно построить формулу, которая бы работала с конкретным значением некоторой ячейки, то нужно построить формулу, в которой использовать абсолютный адрес ячейки, и тоже выполнить автозаполнение формулы на весь диапазон значений.