Урок 33-34
«Составление формул. Мастер функций.
Использование электронных таблиц для обработки экономических задач.»
Ι Организационный
этап.
ΙΙ Постановка
цели и задач урока. Мотивация учебной деятельности учащихся.
Цель: Получить теоретические и практические знания и навыки по
вводу и применению формул в таблице Excel; научиться обрабатывать экономические задачи с
помощью электронных таблиц.
1.
Образовательные:
-
формирование умений применять имеющиеся математические знания и знания из курса
информатики к решению практических задач;
- ознакомление с задачами оптимизации и способами их решения с помощью ms
Excel;
2.
Развивающие:
–
развитие внимания, познавательной активности, творческих способностей,
логического мышления.
3.
Воспитательные:
–
воспитание интереса к предмету;
– самостоятельности в принятии решения;
– формирование культуры общения.
- С какой целью используют Excel? (Электронные таблицы
позволяют сформировать данные в виде таблицы, рассчитать содержимое ячейки с
помощью формулы, представить числовую информацию в графическом виде).
Часто при решении практических задач возникают ситуации,
когда необходимо достичь какой-то конкретной цели. Например, необходимо чтобы
себестоимость продукции составляла 20 рублей. Специфика таких задач состоит в
том, что в Вашем распоряжении есть математическая модель исследуемого процесса,
например, закон ценообразования, но Вы не знаете, при каком значении входящего
в нее параметров можно достичь поставленную цель. Решение таких задач можно
искать методом перебора. Однако в лучшем случае на это уходит много времени.
Можно предложить другие способы решения. В Excel они реализованы как поиск
значения параметра формулы, удовлетворяющего ее конкретному значению.
ΙΙΙ Повторение
изученного материала.
Учащиеся отвечают на
поставленные вопросы и получают оценки.
1. Что такое Электронные таблицы?
2. Что является основной задачей ЭТ? (Основная
задача – автоматические вычисления с данными в таблицах.)
3. Что такое ссылка?
4. Какие виды ссылок вы знаете?
5. Какая между ними разница?
6. Определите виды ссылок: C3, $C$3, C$3, $C3
ΙV Первичное
усвоение новых знаний.
Показ презентации:
Назначение формулы
•
Вычисление по формулам
является основной целью создания документа в среде табличного процессора.
•
Формула является
основным инструментом обработки данных.
•
Формула связывает
данные, содержащиеся в различных ячейках, и позволяет получить новое расчетное
значение по этим данным.
Правила записи формул
•
Формула – математическое
выражение, записанное по правилам, установленным в среде табличного процессора.
•
Формула может включать в себя:
–
константы (значения, не
меняющиеся при расчете),
–
переменные,
–
знаки арифметических операций («+»,
«-», «*», «/»),
–
скобки,
–
функции.
Пример
формулы с константой
Использование функций
•
Функции позволяют производить сложные вычисления в электронных таблицах.
•
В Excel имеется несколько видов встроенных функций:
–
Математические;
–
Статистические;
–
Дата и время;
–
Логические и другие.
СТАТИСТИЧЕСКИЕ
функции
|
Вид
записи
|
Назначение
|
|
МИН(…)
|
Определение минимального из указанных чисел
|
|
МАКС(…)
|
Определение максимального из указанных чисел
|
|
СРЕДНЕЕ(…)
|
Определение среднего значения указанных
чисел
|
|
СУММ(…)
|
Определение суммы указанных чисел
|
Функции ДАТА И
ВРЕМЯ
|
Вид
записи
|
Назначение
|
|
СЕГОДНЯ()
|
Значение сегодняшней даты в виде даты в
числовом формате
|
|
МЕСЯЦ(дата)
|
Вычисление порядкового номера месяца в году
по указанной дате
|
|
ДЕНЬ(дата)
|
Вычисление порядкового номера дня в месяце
по указанной дате
|
|
ГОД(дата)
|
Вычисление года по указанной дате
|
Логические функции
•
И(условие1;условие2;…) – вычисляет значения
(ИСТИНА, ЛОЖЬ) логической операции «И»
•
ИЛИ(условие1;условие2;…) - вычисляет значения
(ИСТИНА, ЛОЖЬ) логической операции «И»
•
ЕСЛИ(условие; знач_Истина; знач_Ложь) – вычисляет
значения в зависимости от выполнения условия
Теперь, зная названия формул и функций (их
значений) можем перейти к решению прикладной задачи.
Использование
надстройки “Поиск решения” при решении прикладных задач.
Пусть известно, что в
штате вашего предприятия должно состоять 6 подсобных рабочих, 8 продавцов, 10
рабочих-специалистов, 3 менеджера, зав. производством, заведующая складом,
бухгалтер и директор. Общий месячный фонд зарплаты составляет 10 000 у.е.
Необходимо определить, какими должны быть оклады сотрудников предприятия.
Продавец получает в 1,5 раза больше подсобного рабочего
(А2=1,5; В2=0);
Рабочий-специалист – в 3 раза больше подсобного рабочего
(А3=0;В3=0);
Менеджер - на 30 у.е. больше, чем рабочий-специалист(А4=3;
B4=30);
Заведующий производством - в 2 раза больше грузчика(А5=2;
В5=0);
Зав. складом - на 40 у.е. больше продавца (А6=1,5;
В6=40);
Бухгалтер - в 4 раза больше подсобного рабочего (А7=4;
В7=0);
Директор - на 20у.е. больше бухгалтера (А8=4;
В8=20);
Построим
модель решения этой задачи
-
Как вы думаете, что мы возьмем за основу для расчета зарплаты работников
предприятия? (оклад подсобного рабочего) Почему? (Все другие оклады
рассчитываются исходя из оклада подсобного рабочего). За основу возьмем оклад
подсобного рабочего, а остальные оклады будем вычислять, исходя из него: во
столько-то раз или на столько-то больше.
Введем
обозначения коэффициентов: А – показывает, во сколько раз оклад по должности
больше оклада подсобного рабочего; А1—для подсобного рабочего, А2-
для продавца и т.д.
В – коэффициент, который
показывает, на сколько больше. В1, В2 и т.д.
-
Что еще известно в задаче? (количество работников каждой должности) Обозначим
количество работников через N: N1,N2,…N8
- Чтобы рассчитать зарплату для каждой должности, мы должны знать оклад
подсобного рабочего. Обозначим его С.
Мы
знаем количество человек на каждой должности, коэффициенты и то, что фонд
заработной платы =10000 у.е. Каким образом мы можем записать математическую
модель решения этой задачи? Нашу модель можно записать как уравнение
N1*A1*C+N2*(A2*C+B2)+...+N8*(A8*C+B8)
= 10000, проверим по таблице
В
этом уравнении нам известны A1...A8, B1...B8 и N1... N8, а С
неизвестно.
Анализ
уравнения показывает, что задача составления расписания свелась к решению
линейного уравнения относительно С. Решим его.
Технология работы:
Предположим, что оклад
подсобного рабочего равен 150 у.е.
- Сколько столбцов нам
необходимо построить?
Введем исходные данные в
рабочий лист электронной таблицы, как показано:
В
столбце D вычислим заработную плату для каждой должности.
Вспомните
правила набора формулы в строке формул.
Записываем
формула, начиная со знака “=”. Какую формулу мы должны ввести, чтобы рассчитать
зарплату подсобного рабочего? (Для ячейки D4 формула расчета имеет вид
=B4*$H$8+C4). Почему нам необходима абсолютная ссылка на ячейку H8? ( параметры
этой ячейки не должны изменяться)
Нужно
ли вводить формулы в каждую ячейку столбца D? (Можно воспользоваться
автозаполнением).
В
столбце F вычислите заработную плату всех рабочих данной должности. Какую
формулу введем в ячейку F4 (формула расчета имеет вид =D4*E4).
В
ячейке F12 вычислите суммарный фонд заработной платы предприятия. Какой
функцией воспользуемся? (СУММ или пиктограммой “Автосумма”). Рабочий лист
электронной таблицы будет выглядеть, как показано ниже.
Что
же получилось? Взяв оклад подсобного рабочего за 150 у.е., мы превысили месячный
фонд зарплаты. Определим оклад подсобного рабочего так, чтобы расчетный фонд
был равен заданному. Как решить поставленную задачу? (уменьшить оклад
подсобного рабочего, скажем, до 120 у.е.). Для решения этой задачи
воспользуемся процедурой “Подбор параметра”.
•
активизируем команду Подбор параметра из меню Сервис;
•
в поле "Установить в
ячейке" появившегося окна введем ссылку на ячейку F12, содержащую формулу;
•
в поле "Значение"
наберем искомый результат 10000;
•
в поле "изменяя значение
ячейки" введем ссылку на изменяемую ячейку H8 и щелкните на кнопке ОК.
Как
видите, программа нашла оптимальное решение.
Анализ
задачи показывает, что с помощью Excel можно решать линейные уравнения.
Конечно, такое уравнение может решить любой школьник. Однако, благодаря этому
простому примеру стало, очевидным, что поиск значения параметра формулы,
удовлетворяющего ее конкретному значению, - это не что иное, как численное
решение уравнений. Другими словами, используя Excel, можно решать любые
уравнения с одной переменной.
- Для чего используется
процедура “Подбор параметров”?
V Первичное
закрепление.
Выполнение лабораторной работы №16.
VΙ Информация
о домашнем задании, инструктаж по его выполнению.
Выучить материалы
лекции; оформить отчет по выполненной лабораторной работе;
VΙΙ Рефлексия
(подведение итогов занятия).
1. Какие задания на уроке понравились больше
всего?
•
работать за компьютером с таблицей
•
Выполнять тестовое задание
•
Работать с интерактивной доской
•
другое
2. Какие задания вызвали трудности, как вы
справились?
3. Над чем ещё надо поработать?
4. Своей работой на уроке я:
•
довольна;
•
не совсем довольна;
•
я не довольна, потому что ...
