Опубликован: 04.10.2006 | Доступ: свободный | Студентов: 9232 / 2779 | Оценка: 4.18 / 4.12 | Длительность: 08:10:00
Специальности: Менеджер
Лекция 5:

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

< Лекция 4 || Лекция 5: 1234 || Лекция 6 >

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

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

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

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

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

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

Функция Описание
СУММ(SUM) Вычисляет сумму чисел в заданных ячейках
СРЗНАЧ(AVERAGE) Находит среднее значение чисел в заданных ячейках
СЧЕТ(COUNT) Подсчитывает количество чисел в списке аргументов в заданных ячейках
МАКС(MAX) Находит наибольшее значение в заданных ячейках
МИН(MIN) Находит наименьшее значение в заданных ячейках

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

Аргумент Описание
ставка Процентная ставка по ссуде, делится на 12 для определения ежемесячных выплат по ссуде
кпер Общая число выплат по ссуде
пс Приведенная к текущему моменту стоимость, или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой
бс Требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение бс равно 0
тип Число 0 (нуль) или 1, обозначающее, когда должна производиться выплата

Если вы взяли взаймы $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).

Совет. Если вы копируете формулу из строки формул, используете в вашей формуле абсолютные ссылки или только диапазоны ячеек, Excel не изменит ссылки на ячейки, когда вы скопируете вашу формулу в другую ячейку.
< Лекция 4 || Лекция 5: 1234 || Лекция 6 >
Гулзира Урбисинова
Гулзира Урбисинова

Каков минимально возможный масштаб отображения листа?