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

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

1. В первую очередь вычисляются выражения внутри круглых скобок.

2. Умножение и деление выполняются раньше сложения и вычитания.

3. Операторы с одинаковым приоритетом выполняются слева направо.

4. Для изменения порядка выполнения операторов используют круглые скобки.

5. Если в формуле количество закрывающих и открывающих круглых скобок не совпадает, выводится сообщение «Несоответствие скобок» и выделяется ошибочная часть формулы.

6. Формула, содержащая ссылки на адреса ячеек, связана с ячейками рабочей книги, а значение формулы зависит от содержимого ячеек, на которые указывают ссылки, и оно изменяется при изменении содержимого этих ячеек.

Поскольку значение показателя «Наценка» зависит от цены товара, целесообразно применить логическую функцию ЕСЛИ для расчета соответствующей графы таблицы.

Функция ЕСЛИ определяет условие (условия), при котором возможны два варианта действий, первое из которых свершится при выполнении заданного условия, второе – при его невыполнении.

Функция ЕСЛИ используется при проверке условий для значений и формул.

Синтаксис функции ЕСЛИ:

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

В данном примере величина наценки будет рассчитываться по следующему алгоритму: если цена товара по прайс-листу меньше 500 у.е., то наценка составит 50% от цены, в противном случае 30%.

Упражнение. Для расчета данных в графе «Наценка» необходимо выполнить следующие действия:

• Активизировать ячейку С3.

• Щелкнуть левой кнопкой мыши по кнопке Вставить функцию (f_x) в строке формул.

• В появившемся окне Мастер функций выбрать категорию функций логические.

• В списке функций выбрать ЕСЛИ.

• Нажать ОК.

• Подвинуть появившееся на экране окно за пределы таблицы, чтобы не мешать указателю мыши работать с ячейками таблицы.

• Установить курсор в окно Логическое выражение.

• Щелкнуть левой кнопкой мыши в ячейке В3, в результате чего адрес данной ячейки отражен в окне Логическое выражение. С клавиатуры набрать <500.

• Перевести курсор в окно Значение_если_истина.

• Щелкнуть левой кнопкой мыши в ячейки В3 и далее ввести формулу В3*50%.

• Перевести курсор в окно Значение_если ложь.

• Щелкнуть левой кнопкой мыши в ячейки В3 и далее ввести формулу В3*30%.

• Нажать ОК.

Обратить внимание на формулу, которая отразилась в строке формул. Данную формулу опытный пользователь мог сразу ввести с клавиатуры. Словами это логическое выражение было сформулировано в описании алгоритма расчета наценки, который был приведен выше. Выражение примет следующий вид: =ЕСЛИ(В3<500;В3*50%;В3*30%).

Упражнение. Необходимо скопировать полученную функцию в ячейки С4:С10, для этого:

• Установить курсор в ячейку С3 и щелкнуть по правой кнопке мыши для вызова контекстного меню.

• Выбрать команду Копировать (при этом вокруг ячейки С3 появится «бегущая» рамка).

• Выделить блок ячеек С4:С10 и нажать правую кнопку мыши.

• В контекстном меню выбрать команду Вставить.

• Щелкнуть левой кнопкой мыши в любой свободной ячейке листа, чтобы снять выделение с блока С4:С10.

• Нажать Esc, чтобы убрать «бегущую» рамку вокруг ячейки С3.

Упражнение. Расчет следующей графы «Цена с учетом наценки» производиться с использованием простой арифметической формулы: =Цена+Наценка. Для этого необходимо:

• Активизировать ячейку D3.

• С клавиатуры ввести знак «=».

• Щелкнуть левой кнопкой мыши в ячейке В3.

• С клавиатуры ввести знак «+».

• Щелкнуть левой кнопкой мыши в ячейке С3 и нажать Enter.

• Скопировать полученный результат в блок ячеек D4:D10.

• Щелкнуть левой кнопкой мыши в любой свободной ячейке листа, чтобы снять выделение с блока D4:D10.

• Нажать Esc, чтобы убрать «бегущую рамку вокруг ячейки D3.

• Графу «Количество» заполнить по образцу (рис.6).

Рис.6

• Для расчета графы «Сумма» в ячейку F3 ввести формулу =D3*E3 (Цена с учетом наценки * Количество) и скопировать ее в ячейки F4:F10.

• Расчет графы «Налог на добавленную стоимость (НДС)» производится по формуле Сумма*18%. НДС - это налог, перечисляемый в бюджет государства в размере 18% от суммы продаж. В ячейку G3 ввести формулу =F3*18% и скопировать ее в ячейки данной графы.

• Для определения выручки магазина по каждому виду товара в ячейку Н2 ввести заголовок «Выручка», а в ячейку Н3 - формулу =F3+G3 («Сумма» + «НДС»).