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

=F1*5+D1/3;

=СУММ(G5:G15);

=ПРОИЗВЕД(F7;J9).

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

Ссылки на ячейки в формулах можно вводить, не набирая на клавиатуре, а выделяя их с помощью мыши на рабочем листе. После окончания ввода формулы надо нажать [Enter]. Удаляются и редактируются формулы как и обычный текст.

Ссылки на ячейки могут быть относительные, абсолютные и смешанные. Относительная ссылка на ячейку используется в формулах для указания адреса ячейки относительно позиции ячейки, содержащей формулу. Относительная ссылка записывается в виде последовательности заголовков столбца и строки, например В14. Абсолютная ссылка – это фиксированная ссылка на ячейку. Абсолютная ссылка устанавливается путем указания символа доллара ($) в адресе ячейки, например $В$14. В смешанных ссылках используются разные способы адресации, например А$1 или $А1.

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

В формулах можно использовать встроенные функции. Функции – это специальные, заранее созданные формулы, позволяющие быстро выполнять сложные вычисления. Например, рассмотрим логическую функцию [ЕСЛИ], которая используется при проверке условий для значений и формул. Ее синтаксис имеет вид:

=ЕСЛИ(логическое_выражение; значение_если_истина;значение_если_ложь).

Примером может быть формула: =ЕСЛИ(A7>0;5;4), в которой проверяется значение ячейки A7. В случае, если оно положительно, в текущую ячейку заносится 5, в противном случае 4.

Для вставки формул, содержащих функции, можно воспользоваться мастером функций, который вызывается с помощью ленты, на вкладке [Функция], группе [Библиотека функций] по команде [Вставить функцию].

Рассмотрим порядок задания формул в таблице «Учет движения товаров на складах», где рассчитаем остаток материалов на конец месяца и общие суммы остатка на начало месяца, остатка на конец месяца, прихода и расхода всех материалов. В таблице «Оптовые и розничные цены товаров» рассчитаем розничную цену материалов (оптовая цена + 30%).

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

Перейдите в режим отображения формул, выполнив команду [Показать формулы], которая находится на ленте во вкладке [Формулы], в группе [Зависимости формул].

Примечание. Ширина столбцов в режиме формул несколько увеличится, но при возврате в режим отображения значений формул (отменить выполнение команды [Показать формулы], которая находится на ленте во вкладке [Формулы], в группе [Зависимости формул]), таблица снова примет обычный вид.

Введите формулу для расчета остатка на конец месяца в ячейку F4:

установите курсор в ячейку F4; введите знак «=»;

выполните щелчок на ячейке С4;

введите знак «+» и выполните щелчок на ячейке D4;

введите знак « - » и выполните щелчок на ячейке Е4; нажмите [Enter].

В результате в ячейке F4 (рис. 9) отобразится формула вида: =C4+D4-E4.

Скопируйте формулу из ячейки F4 в диапазон ячеек F5:F8. Для этого выделите ячейку F4, скопируйте формулу командой [Копировать] которая находится на ленте во вкладке [Главная] в группе [Буфер обмена], затем выделите диапазон F5:F8 и выполните команду [Вставить], которая находится на ленте во вкладке [Главная] в группе [Буфер обмена].

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

Вычислите сумму остатка на начало месяца, создав формулу с помощью мастера функций:

· установите курсор в ячейку С9, выполните команду [Вставить функцию], на ленте во вкладке [Формулы] в группе [Библиотека функций]. На экран будет выведено окно первого шага [Мастера функций];

· в списке [Категория] выберите группу функций [Математические];

· в списке [Функция] выберите [СУММ] и нажмите кнопку [ОК];

· на втором шаге [Мастера функций] укажите аргументы функции. Для этого установите курсор в поле [Число1] и введите с клавиатуры адрес диапазона ячеек для суммирования C4:C8 (английскими символами) или выделите этот диапазон на листе «Товары» при помощи мыши (что будет правильнее, так как исключит возможную ошибку ввода с клавиатуры!). Нажмите кнопку [ОК]. Excel поместит в ячейку C9 формулу вида: =СУММ(С4:С8) (см. рис. 9);

· скопируйте ее в ячейки D9, Е9, F9 аналогично изложенному выше способу копирования (рисунке 9).

Примечание. Формулу можно копировать и с помощью маркера автозаполнения. Для этого надо курсор мыши установить в правый нижний угол ячейки, из которой выполняется копирование, чтобы он приобрел вид знака «+» и протянуть курсор при нажатой левой клавише мыши на нужное количество ячеек в нужном направлении.

Рисунок 9 – Таблица 1 в режиме формул

Перейдите в режим отображения на экране значений формул отжав кнопку [Показать формулы], на ленте во вкладке [Формулы], в группе [Зависимости формул]), таблица снова примет обычный вид. Поскольку таблица не содержит исходных данных, то в ячейках с формулами будет отображаться нулевое значение.

Активизируйте лист «Цены».

В ячейку D4 и введите формулу: =С4*130 % (к оптовой стоимости добавляется 30 %, т.е. исходное значение умножается на 130%).

Скопируйте формулу из D4 в ячейки D5:D8, используя буфер обмена или метод автозаполнения.

Сохраните рабочую книгу, выполнив команду [Сохранить] на панели быстрого доступа, расположенную над лентой. В окне сохранения укажите путь сохранения и имя файла – «УЧЕТ» и нажмите [Сохранить].

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

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

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

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

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

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

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

Активизируйте ячейку В4 и на ленте выберите вкладку [Главная], группу [Редактирование], выполните команду [Заполнить] и из раскрывающегося списка выберите [Прогрессия].

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

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

Рисунок 10 – Таблица 1 с исходными данными и результатами вычислений