Ввод формул. Использование мастера функций

Вычисления в таблицах производятся с помощью формул. Формула начинается со знака равенства (=) и может состоять из математических операторов, значений, ссылок на ячейки и имен функций, например,

=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. Сохраните рабочую книгу, выполнив команду Файл►Сохранить. В окне сохранения укажите путь сохранения и имя файла – УЧЕТ и нажмите [Сохранить].

Автозаполнение

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

· Для копирования содержимого ячейки в ячейки диапазона, расположенного, например, справа необходимо выполнить команду меню Правка►Заполнить ► Вправо. Также можно выполнять копирование с использованием автозаполнения влево, вниз, вверх.

· Команда Правка►Заполнить►Прогрессия используется для заполнения выделенного диапазона ячеек последовательностями чисел или дат. Данные в первых ячейках каждой строки или столбца диапазона будут использованы в качестве начальных значений последовательностей.

Рассмотрим порядок использования функции автозаполнения на примере ввода данных в столбец Код материала таблицы Учет движения материалов на складах.

1. Активизируйте лист Товары.

2. Введите в ячейку В4 число 101 и нажмите [Enter].

3. Активизируйте ячейку В4 и выполните команду Правка► Заполнить►Прогрессия.

4. В поле Расположение установите флажок – по столбцам, в поле Тип – арифметическая. В поле Шаг введите – 1, в поле Предельное значение – 105. Нажмите [ОК].

5. Заполните таблицу остальными исходными данными (табл. 5.1). Таблица примет вид, представленный на рис. 5.8.