Проведение вычислений

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

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

Чтобы написать формулу Excel, начните вводить данные в ячейку со знака равенства, тогда они будут интерпретироваться как выражение для вычисления, а не текст. После знака равенства вы вводите формулу. Например, вы можете найти сумму значений в ячейках С2 и С3 с помощью формулы =С2+С3. После того как вы ввели формулу в ячейку, вы можете проверить ее, щелкнув на ячейке и отредактировав ее содержимое в строке формул. Например, вы можете заменить эту формулу на =C3-C2, для вычисления разности между содержимым ячеек С2 и С3.

Ввод ссылок на 15 или 20 ячеек может показаться утомительным, однако в Excel легко работать со сложными вычислениями. Для задания нового вычисления выберите пункт Функция (Function) в меню Вставка (Insert). Откроется диалоговое окно Мастер функций (Insert Function) со списком функций, или предопределенных формул, из которого вы можете выбрать нужную вам функцию.

В следующей таблице описаны наиболее часто используемые функции.

Вы также можете использовать две другие функции, ТДАТА() [NOW()] и ПЛТ() [PMT()]. Функция ТДАТА() возвращает время, когда рабочая книга была открыта, поэтому значение функции будет изменяться каждый раз, когда будет открываться рабочая книга. Правильная запись функции выглядит так: =ТДАТА(); чтобы обновить текущее время и дату, просто сохраните работу, закройте и снова откройте документ. Функция ПЛТ() немного сложнее. Она вычисляет сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки. Чтобы произвести с ее помощью вычисления, функции требуется задать ставку, количество месяцев платежей и стартовый баланс. Элементы, вводимые в функцию, называются аргументами и должны быть введены в определенном порядке. Этот порядок выглядит так: ПЛТ(ставка;кпер;пс;бс;тип). В следующей таблице приведены описания каждого аргумента функции ПЛТ.

Если вы взяли взаймы $20.000 под 8-процентную ставку и возвращаете кредит в течение 24-х месяцев, вы можете использовать функцию ПЛТ() для определения размера ежемесячных выплат. В этом случае, функцию следует задавать таким образом: =ПЛТ(8%/12,24,20000); функция возвратит величину ежемесячных выплат в размере $904.55.

Вы также можете использовать в формулах имена любых диапазонов ячеек. Например, если имя диапазона "Заказ1" ссылается на ячейки с С2 по С6, вы можете вычислить среднее значение ячеек с С2 по С6 по формуле =СРЗНАЧ(Заказ1) [AVERAGE(Заказ1)]. Если вы хотите включить в формулу область смежных ячеек, но еще не определили эти ячейки как диапазон, можете щелкнуть на первой ячейке диапазона и перетащить указатель на последнюю ячейку. Если ячейки не смежные, нажмите и удерживайте (Ctrl) и щелкните на нужных ячейках. В обоих случаях, когда вы отпустите кнопку мыши, ссылки на выбранные вами ячейки появятся в формуле.

Формулы также могут быть использованы для вывода сообщений при определенных условиях. Например, Кэтрин Тернер, владелец компании "Все для сада", предоставляет бесплатный экземпляр журнала о садоводстве покупателям, сделавшим покупки на сумму более $150. Такой тип формул называется условной формулой и использует функцию ЕСЛИ (IF). Чтобы написать условную формулу, щелкните на ячейке, которая будет содержать формулу и откройте диалоговое окно вставки функции. В диалоговом окне выберите функцию ЕСЛИ из списка доступных функций и нажмите ОК. Откроется диалоговое окно Аргументы функции (Function Arguments).

При работе с функцией ЕСЛИ, диалоговое окно Аргументы функции (Function Arguments) содержит три поля: Лог_выражение (Logical test), Значение_если_истина (Value_if_true) и Значение_если_ложь (Value_if_false). В строку Лог_выражение (Logical test) вводится условие, которое вы хотите проверять. Для проверки, превышает ли сумма заказа $150, выражение будет выглядеть так: СУММ(Заказ1)>150.

Теперь вам нужно сделать так, чтобы Excel отображал сообщения, указывающие на то, должен ли покупатель получать бесплатный экземпляр журнала. Чтобы выводить сообщения с помощью функции ЕСЛИ, введите эти сообщения в строках Значение_если_истина (Value_if_true) или Значение_если_ложь (Value_if_false). В данном случае вы можете ввести в строке Значение_если_истина (Value_if_true) "Вы получаете бесплатный журнал" и "Спасибо за покупку" в строке Значение_если_ложь (Value_if_false).

Когда вы создали формулу, вы можете скопировать ее в буфер и вставить в другую ячейку. После этого Excel попытается изменить формулу так, чтобы она работала в новых ячейках. В качестве примера на этом рисунке ячейка D8 содержит формулу =СУММ(С2:С6):

Щелкните на ячейке D8, скопируйте содержимое в буфер и вставьте результат в ячейку D16; в ячейке D16 появится выражение =СУММ(С10:С14). Excel изменит формулу так, что она будет применима к ячейкам в этой области листа! Excel использует в формулах относительную ссылку, или ссылку, которая изменяется при копировании формулы в другую ячейку. В относительных ссылках указывается только строка и столбец ячейки.

Если вы хотите, чтобы ссылка на ячейку оставалась неизменной при копировании в другую ячейку, вы можете использовать абсолютную ссылку. Чтобы сделать ссылку на ячейку абсолютной, нужно ввести значок $ перед номером строки и перед номером столбца. Например, для того чтобы формула в ячейке D16 выводила сумму значений в ячейках с С10 по С14, независимо от того, в какой ячейке она находится, следует ввести формулу =СУММ($C$10:$C$14).