Практическая работа
Табличный процессор MICROSOFT EXCEL 2007
Выполнение расчетных операций
Цель работы: изучить способы выполнение расчетных операций, работа с формулами и Мастером функций.
О
сновным достоинством редактора электронных таблиц Ехсеl является наличие мощного аппарата формул и функций, с помощью которых можно выполнять математические финансовые и статистические операции.
Формулой в Ехсеl называется последовательность символов, которая начинается со знака равенства (=) и содержит вычисляемые элементы (операнды) и операторы.
Операнды
Ввод формулы начинается со знака =. По этому значку Excel определяет, что вы хотите вставить формулу в ячейку.
Примеры формул.
= А7 + В8 * 5 = доход – расход
Операторы Имена
где:
А7 и В8 – это адреса ячеек, участвующих в формуле,
5 – числовая константа
+ * - это операторы.
Синтаксис формулы определяет порядок, в котором вычисляется значения.
! Приоритет в вычислениях тот же что и в математике!
Операции, которые выполняются над элементами формул, осуществляется с помощью операторов. Разновидности арифметических операторов и операторы сравнения представлены в таблице 1.
Таблица 1. Разновидности операторов.
Арифметические операторы | Операторы сравнения |
Оператор | Значение | Оператор | Значение |
+ | Сложение | = | Равно |
- | Вычитание | | Больше |
* | Умножение | | Меньше |
/ | Деление | = | Больше или равно |
% | Процент | | Меньше или равно |
^ | Возведение в степень | | Не равно |
Оператор текста &– объединяет два и более текстовых значения в единое.
Например: А1 – хорошая; В2 – Погода; А4 = А1&В2 (хорошая Погода)
Указанные в формуле адреса называются ссылками. По умолчанию Excel воспринимает ссылки в формулах как относительные, которые записываются в следующем виде А1, В12, Н3. Удобство относительных ссылок становится очевидным при копировании формул. Для копирования формул необходимо выделить ячейку, содержащую формулу, а затем протянуть маркер заполнения по нужному диапазону ячеек.
Копировать формулу можно по горизонтали или по вертикали. При этом:
-
при копировании вправо (влево) по горизонтали смещение на одну ячейку увеличивает (уменьшает) каждый номер столбца в формуле на единицу;
-
при копировании вниз (вверх) по вертикали смещение на одну ячейку увеличивает (уменьшает) каждый номер строки в формуле на единицу.
Происходит эффект автоиндексации адресов.
Относительные ссылки применяются в таблице чаще всего, однако бывают случаи, когда при копировании формулы надо запретить, автоматически менять ссылку на ячейку. Для этого используют абсолютную ссылку, при копировании формулы абсолютные ссылки не изменяются. Чтобы изменить относительную ссылку на абсолютную надо перед номером столбца и номером строки записать символ «замораживания» адреса – «$». С помощью этого символа можно заморозить как весь адрес, например $В$1, так и отдельные его части, например $В2, В$2 – эти ссылки называют еще смешанные.
Изменить относительную ссылку на абсолютную необходимо:
-
Перейти в режим редактирования формул.
-
Установить текстовый курсор перед адресом ячейки.
-
Нажать клавишу F4 для изменения типа ссылки, чтобы не вводить знак «$» вручную.
Последовательность изменения типов ссылок, например, для ячейки А1 при использовании клавиши F4 такая:
-
$А$1 - абсолютная ссылка (фиксированная ячейка);
-
А$ 1 - изменяемый столбец и неизменяемая строка;
-
$А1 - неизменяемый столбец и изменяемая строка;
-
А1 - относительная ссылка.
В формулах можно использовать ссылки на ячейки, как текущего листа, так и других листов рабочей книги. Например, ссылка на ячейку А1, расположенную на листе с именем Лист1, будет выглядеть так: Лист1!А1.
Задание 1. Относительные ссылки.
-
Запустите Microsoft Excel.
-
Лист 1 назовите Показатели.
-
На этом листе создайте таблицу Показатели работы больницы №8 и вычислите процент выполнения плана по годам. Исходные данные представлены на рис. 1.
Рис.1. Исходные данные
-
Для вычисления процента выполнения плана по годам требуется фактическое значение разделить на плановое и умножить на 100.
Для этого:
-
Выделите ячейку В6, в которую хотите вставить формулу.
-
Введите знак =
-
Щелкните по ячейке В5 (адрес ячейки отобразится в формуле), затем введите
знак / (деления).
-
Щелкните по ячейке В4, затем введите знак * (умножить) и число 100.
-
Формула будет иметь следующий вид
Для подтверждения набранной формулы нажмите клавишу Enter , или нажмите
в строке формул. Ввод формулы будет закончен и в ячейке отобразится результат.
-
Чтобы произвести расчеты в следующих ячейках необходимо формулу из ячейки В6 скопировать в следующие ячейки по горизонтали, используя маркер автозаполнения, как показано на рис. 2.
Рис 2. Использование маркера автозаполнения.
Отметим, что программа сама внесла соответствующие изменения в формулы: =С5/С4*100; =D5/D4*100; =E5/E4*100; =F5/F4*100.
-
Сравните таблицу с образцом, представленном на рис. 3. Выполните форматирование.
Рис.3 Образец таблицы
-
Лист 2 назовите Грипп, используя исходные данные, представленные на рис.4, создайте таблицу и произведите расчеты
Рис. 4. Исходные данные.
-
В столбце «Коэффициент» вписать формулу, которая позволяет вычислять так называемый интенсивный коэффициент, когда число заболевших делиться на число учащихся в соответствующих классах. Используя метод автозаполнения, скопируйте формулу для определения коэффициента по всем классам.
-
Убедитесь, что программа изменила ссылки на ячейки в соответствии с новым положением формулы.
-
В графе «Всего» получит общее учащихся, число заболевших и значение интенсивного коэффициента по всем младшим классам. Для расчета используйте функцию автосуммирования. Для этого:
-
Выделите ячейку В8, которую будет помещен результат
-
Щелкните по кнопке
(функция =СУММ()) на ленте Главная
-
Проверить, правильно ли выделен диапазон для суммирования (В4:В7)
Нажать
или клавишу Enter.
-
Округлите значение коэффициента до 2х знаков после запятой, для этого выделите ячейки (D4:D8) и нажмите кнопку
(Уменьшить разрядность) на ленте Главная. Сравните таблицу с образцом и выполните форматирование, представленное на рис. 5.
Рис. 5. Образец.
Задание 2. Абсолютные ссылки.
-
Лист 3 назовите Дизентерия, используя исходные данные, представленные на рисунке 6, создайте таблицу и произведите расчеты.
Рис. 6. Исходные данные
-
Необходимо получить общее число больных в графе «Всего».
-
В столбце «коэффициент» вписать формулу, позволяющую вычислять так называемый, экстенсивный коэффициент, когда число больных соответствующей формы болезни делится на общее число больных (=B4/B8). Скопируйте эту формулу в следующие ячейки по вертикали, используя маркер автозаполнения.
В
нимание! Если мы используем относительные ссылки то, в результате вычисления получим ошибку (рис. 7).
Рис.7 Сообщение об ошибке.
-
Таким образом, чтобы ссылка на ячейку В8 оставалась неизменной необходимо, для ссылки на ячейку задать абсолютный адрес, и формула будет иметь следующий вид: =В4/$B$8.
-
С помощью автозаполнения скопируйте данную формулу в остальные ячейки.
-
Просмотрите формулы и убедитесь, что относительные ссылки изменились, но ссылка на ячейку В8 осталась прежней рис. 8.
Рис.8. Пример вычисления.
-
Округлите значение коэффициента до 2х знаков после запятой, для этого выделите ячейки (С4:С7) и нажмите кнопку
(Уменьшить разрядность)
-
Полученный результат представлен на рисунке 9.
Рис.9. Образец таблицы.
-
Создайте новый лист-Лист 4, назовите его Объемы продаж, используя исходные данные, представленные на рис. 10, создайте таблицу, произведите расчеты и отформатируйте.
Рис.10. Исходные данные.
-
Необходимо рассчитать сумму выручки за каждый месяц и выручку по каждой аптеке за 6 месяцев.
-
Столбец «Объем продаж» рассчитать по формуле: =Н5/$Н$12*100.
Задание 3. Смешанные ссылки.
-
Создайте новый лист-Лист 5, назовите его Наценка, используя исходные данные, представленные на рис. 11, создайте таблицу. Требуется рассчитать отпускную стоимость товара при различных наценках, с учетом, что закупочная цена фиксирована.
Рис. 11. Расчет значений в таблице с использованием смешанных ссылок.
-
Нам необходимо записать в ячейку D4 такую формулу, которая бы при копировании в ячейки диапазона D4:F6 рассчитывала стоимость с учетом разных значений наценки.
-
При «протаскивании» формулы по столбцам нам необходимо, чтобы столбец С был зафиксирован. Аналогично, при «протаскивании» формулы по строкам, нам необходимо зафиксировать строку 3. В ячейке D4 таким образом получилась формула =$C4*(1+D$3); абсолютные ссылки выделены. При протаскивании по диапазону (D4:F6) такая формула дает правильные значения в каждой ячейке диапазона.
-
Сохранить книгу под именем Расчеты_Фамилия.
5