Вычисления в таблицах производятся с помощью формул. Формула начинается со знака равенства (=) и может состоять из математических операторов, значений, ссылок на ячейки и имен функций, например,
=F1*5+D1/3
=СУММ(G5:G15)
=ПРОИЗВЕД(F7;J9)
При этом результатом выполнения формулы является некоторое новое значение. Результат помещается в ячейку, в которой находится формула. В формулах, используемых в электронных таблицах, соблюдается тот же порядок выполнения арифметических операций, что и в математике.
Ссылки на ячейки в формулах можно вводить, не набирая на клавиатуре, а выделяя их с помощью мыши на рабочем листе. После окончания ввода формулы надо нажать [Enter]. Удаляются и редактируются формулы как и обычный текст.
Ссылки на ячейки могут быть относительные, абсолютныеисмешанные. Относительная ссылкана ячейку используется в формулах для указания адреса ячейки относительно позиции ячейки, содержащей формулу. Относительная ссылка записывается в виде последовательности заголовков столбца и строки, например В14. Абсолютная ссылка – это фиксированная ссылка на ячейку. Абсолютная ссылка устанавливается путем указания символа доллара ($) в адресе ячейки, например $В$14. В смешанных ссылках используются разные способы адресации, например А$1 или $А1.
При копировании или переносе формулы на некоторое число позиций каждая содержащаяся в ней относительная ссылка заменяется ссылкой на другую ячейку, смещенную относительно исходной на такое же число позиций и в том же направлении, что и формула. При использовании в данном случае абсолютной ссылки адрес ячейки, на которую ссылается формула, остается неизменным. В случае использования смешанных ссылок изменяется только относительная часть ссылки.
В формулах можно использовать встроенные функции. Функции – это специальные, заранее созданные формулы, позволяющие быстро выполнять сложные вычисления. Например, рассмотрим логическую функцию ЕСЛИ,которая используется при проверке условий для значений и формул. Ее синтаксис имеет вид:
Примером может быть формула: =ЕСЛИ (A7>0;5;4), в которой проверяется значение ячейки A7. В случае, если оно положительно, в текущую ячейку заносится 5, в противном случае – 4.
Для вставки формул, содержащих функции, можно воспользоваться мастером функций, который вызывается по команде Вставка 4 Функцияили кнопкой на панели инструментов .
Рассмотрим порядок задания формул в таблице Учет движения товаров на складах, где рассчитаем остаток материалов на конец месяца и общие суммы остатка на начало месяца, остатка на конец месяца, прихода и расхода всех материалов. В таблице Оптовые и розничные цены товаров рассчитаем розничную цену материалов (оптовая цена + 30%).
1. Активизируйте лист Товары.
2. Перейдите в режим отображения формул, выполнив командуСервис► Параметры►вкладкаВид, Установитефлажок Формулыи нажмите [ОК].
Примечание. Ширина столбцов в режиме формул несколько увеличится, но при возврате в режим отображения значений формул (по команде Сервис► Параметры►снять флажок Формулы на вкладке Вид), таблица снова примет обычный вид.
3. Введите формулу для расчета остатка на конец месяца в ячейку F4:
· установите курсор в ячейку F4; введите знак «=»;
· выполните щелчок на ячейке С4;
· введите знак «+» и выполните щелчок на ячейке D4;
· введите знак « -» и выполните щелчок на ячейке Е4; нажмите [Enter].
В результате в ячейке F4 (рис. 5.7) отобразится формула вида: =C4+D4-E4.
4. Скопируйте формулу из ячейки F4 в диапазон ячеек F5:F8. Для этого выделите ячейку F4, скопируйте формулу командой Правка►Копировать, затем выделите диапазон F5:F8 и выполните команду Правка► Вставить.
Примечание. Обратите внимание, что в каждой формуле будут использованы ссылки на ячейки соответствующей строки.
5. Вычислите сумму остатка на начало месяца, создав формулу с помощью мастера функций:
· установите курсор в ячейку С9, выполните команду Вставка► Функция. На экран будет выведено окно первого шага Мастера функций;
· в списке Категориявыберите группу функций Математические;
· в списке Функция выберите СУММ и нажмите кнопку [ОК];
· на втором шаге Мастера функций укажите аргументы функции. Для этого установите курсор в поле Число1 и введите с клавиатуры адрес диапазона ячеек для суммирования C4:C8 (английскими символами) или выделите этот диапазон на листе Товары при помощи мыши (что будет правильнее, так как исключит возможную ошибку ввода с клавиатуры!). Нажмите кнопку [ОК]. Excel поместит в ячейку C9 формулу вида: =СУММ(С4:С8) (см. рис. 5.7);
· скопируйте ее в ячейки D9, Е9, F9 аналогично изложенному выше способу копирования (рис. 5.7).
Примечание. Формулу можно копировать и с помощью маркера автозаполнения. Для этого надо курсор мыши установить в правый нижний угол ячейки, из которой выполняется копирование, чтобы он приобрел вид знака «+» и протянуть курсор при нажатой левой клавише мыши на нужное количество ячеек в нужном направлении.
6. Перейдите в режим отображения на экране значений формул командой Сервис►Параметры►вкладка Вид, снимите флажок Формулыинажмите[ОК].Посколькутаблица не содержит исходных данных, то в ячейках с формулами будет отображаться нулевое значение.
7. Активизируйте лист Цены.
8. В ячейку D4 и введите формулу : =С4*130% (к оптовой стоимости добавляется 30%, т.е. исходное значение умножается на 130%).
9. Скопируйте формулу из D4 в ячейки D5:D8,используя буфер обмена или метод автозаполнения.
10. Сохраните рабочую книгу, выполнив команду Файл►Сохранить. В окне сохранения укажите путь сохранения и имя файла – УЧЕТ и нажмите [Сохранить].
.
скопируйте ее в ячейки D9, Е9, F9 аналогично изложенному выше способу копирования (рис. 5.7).