Решение прикладных задач в excel 8 класс
Регрессионный анализ
В MS Excel экспериментальные данные аппроксимируются линейным уравнением до 16 порядка:
где Y — зависимая переменная, X . Х — независимые переменные, а0, а . а16 — искомые коэффициенты регрессии.
Для получения коэффициентов регрессии используется процедура Регрессия из пакета анализа. Кроме того, могут быть использованы функция ЛИНЕЙН для получения параметров регрессионного уравнения и функция ТЕНДЕНЦИЯ.
Для реализации процедуры Регрессия необходимо:
•выполнить команду Сервис ► Анализ данных;
•в появившемся диалоговом окне Анализ данных в списке Инструменты анализа выбрать строку Регрессия, указав курсором мыши и щелкнув левой кнопкой мыши. Затем нажать кнопку ОК;
•если необходимо визуально проверить отличие экспериментальных точек от предсказанных по регрессионной модели, следует установить флажок в поле График подбора;
•нажать кнопку ОК.
Результаты анализа. Выходной диапазон будет включать в себя результаты дисперсионного анализа, коэффициенты регрессии, стандартную погрешность вычисления Y , среднеквадратичные отклонения, число наблюдений, стандартные погрешности для коэффициентов.
Интерпретация результатов. Значения коэффициентов регрессии находятся в столбце Коэффициенты и соответствуют:
• Y -пересечение — а ;
•переменная X — а ,
•переменная Х — а и т. д.
В столбце Р-Значение приводится достоверность отличия соответствующих коэффициентов от нуля. В случаях, когда Р > 0,05, коэффициент может считаться нулевым, что означает, что соответствующая независимая переменная практически не влияет на зависимую переменную.
Приводимое значение R -квадрат (коэффициент детерминации) определяет, с какой степенью точности полученное регрессионное уравнение аппроксимирует исходные данные. Если R -квадрат > 0,95, говорят о высокой точности аппроксимации (модель хорошо описывает явление). Если R -квадрат лежит в диапазоне от 0,7 до 0,95, говорят об удовлетворительной аппроксимации (модель в целом адекватна описываемому явлению). Если R -квадрат < 0,6, принято считать, что точность аппроксимации недостаточна и модель требует улучшения (введения новых независимых переменных, учета нелинейностей и т. д.).
Стоит задача предсказания успеваемости абитуриентов по данным вступительных тестов. В качестве исходных данных исследователь имеет для каждого из 10 учащихся предыдущего набора средний балл отметок и 3 показателя тестирования.
1. Создайте таблицу, которой в десяти ячейках столбца А и десяти ячейках столбца В записаны произвольные числа. В остальных столбцах записаны формулы производящие математические действия над этими числами:
- в столбце С – квадратный корень А;
- в столбце D – корень четвертой степени В;
- в столбце E - модуль суммы А и В;
- в столбце F - нахождение целого числа при делении А на В;
- в столбце G - нахождение остатка при делении А на В;
- в столбце H - нахождение наибольшего общего делителя А и В;
- в столбце I - нахождение наименьшего общего кратного А и В;
- в столбце J - сумму sinA и cosB;
- в столбце K - округляет до двух знаков после запятой результат нахождения корня третей степени В.
2. На предприятии сотрудникам выдается ежемесячно заработная плата. Кассиру необходимо указать какими именно купюрами ему хотелось бы получить ее. Необходимо все исходные данные представить в виде таблицы, где напротив каждой фамилии сотрудника, как только вводится начисленная ему зарплата, тут же появляются сумма «к выдачи», с учетом налогов, и соответствующий набор купюр. Одновременно подводится общий итог по всему коллективу.
Заработная плата с учетом налогов
Практическая работа № 1 «Математические функции»
1. Создайте таблицу, которой в десяти ячейках столбца А и десяти ячейках столбца В записаны произвольные числа. В остальных столбцах записаны формулы производящие математические действия над этими числами:
в столбце С – квадратный корень А;
в столбце D – корень четвертой степени В;
в столбце E - модуль суммы А и В;
в столбце F - нахождение целого числа при делении А на В;
в столбце G - нахождение остатка при делении А на В;
в столбце H - нахождение наибольшего общего делителя А и В;
в столбце I - нахождение наименьшего общего кратного А и В;
в столбце J - сумму sinA и cosB;
в столбце K - округляет до двух знаков после запятой результат нахождения корня третей степени В.
2. На предприятии сотрудникам выдается ежемесячно заработная плата. Кассиру необходимо указать какими именно купюрами ему хотелось бы получить ее. Необходимо все исходные данные представить в виде таблицы, где напротив каждой фамилии сотрудника, как только вводится начисленная ему зарплата, тут же появляются сумма «к выдачи», с учетом налогов, и соответствующий набор купюр. Одновременно подводится общий итог по всему коллективу.
Заработная плата с учетом налогов
Практическая работа № 2 «Абсолютные ссылки»
1. Заданы стоимость 1кВт·ч и показания счетчика за предыдущий и текущей месяцы. Необходимо вычислить расход электроэнергии за прошедший период и стоимость израсходованной электроэнергии.
Расчет стоимости электроэнергии
Номера квартир
Показания счетчика предыдущего месяца
Показания счетчика текущего месяца
Расход электроэнергии
Стоимость электроэнергии
2. Составьте таблицу умножения, в которой по горизонтали и по вертикали записаны числа
от одного до десяти, а в ячейки на их перенесении результат умножения.
Таблица умножения
Расчет стоимости подписки
Наименование издания
Количество выпусков в месяц
Цена номера
Количество месяцев
4. Заданы первый член арифметической прогрессии и разность. Построить таблицу, в которой как только вписываются порядковые номера (10 любых) членов арифметической прогрессии, сразу вычисляется значение каждого члена и сумма всех членов.
Арифметическая прогрессия
Первый член
Разность
Порядковый номер
Значение
Сумма первых членов
Практическая работа № 3 «Функции «Дата и время»»
1. Вычислите сколько учебных дней составляет третий триместр.
2. Необходимо вычислить возраст учащихся и день недели, в который они родились, если заданы список учащихся и даты их рождения.
Возраст учащихся
Дата рождения
Практическая работа № 4 «Статистические функции»
1. В задаче «Возраст учащихся» определите самого старшего и самого младшего учащихся.
2. Составьте таблицу кубов, в которой по горизонтали записаны десятки, а по вертикали записаны единицы, а в ячейках на их перенесении результат возведения числа в куб.
Таблица кубов
десятки
Практическая работа № 5
«Форматирование табличного документа. Создание и редактирование диаграмм»
Создайте книгу, содержащую все ваши задачи. Каждый лист назовите именем соответствующей задачи и задайте листам цвет ярлычка. Для оформления задачи «Возраст учащегося» воспользуетесь командой «Автоформат» и постойте график отражающий возраст учащихся. В задаче «Расписание поездов», создайте столбчатую диаграмму распределения времени стоянок по станциям
и круговую объемную диаграмму распределения времени в пути. В задаче «Стоимость электроэнергии» постойте диаграммы расхода электроэнергии и распределения стоимости электроэнергии по квартирам. Лист с задачей «Зарплата» сделайте невидимым, а на лист «Доход семьи» поставьте защиту. В задаче «Стоимость подписки» закрепите область с именованием издания. Данную книгу сохраните под именем «Задачи по Excel».
В Тверском лицее изучение Информационных технологий осуществляется в течение нескольких лет. По программе на эту тему отводится в 8 и 9 классе всего 12 часов, в 10 физико-математическом классе – еще 12 часов. На средней ступени изучаются основные возможности электронных таблиц, а именно, основные типы и форматы данных, относительные, абсолютные и смешанные ссылки, использование формул, встроенные функции, построение диаграмм различных типов. Все эти вопросы изучаются на простых доступных примерах. На старшей ступени в физико-математическом классе наряду с повторением пройденного материала изучаются и другие возможности программы Excel, например, использование сложных (вложенных) функций, в том числе логических.
Программа Excel предназначена и широко используется для вычислений, предполагающих представление данных в табличном виде. Для творческого использования возможностей Excel на уровне лицеистов необходимо не только познакомить детей с различными вычислительными возможностями программы, но и показать примеры практического применения процессора, например, на уроках математики. Наша задача – обеспечить грамотный подход и развить творческое отношение детей к решению разнообразных задач. Такими задачами могут быть: построение графиков функций, решение квадратного уравнения, решение системы уравнений, приближенное определение площадей фигур, ограниченных графиком функции, задачи оптимизации, прогнозирования и др. Некоторые из этих задач можно решать с помощью инструмента «Поиск решения».
Решение таких задач на уроках информатики позволяет осуществлять основные принципы педагогической деятельности:
Научность является обязательным дидактическим принципом, т. к. в основе изучения темы лежат современные компьютерные технологии.
Знакомство и изучение научной литературы по соответствующей тематике.
Изучение накопленного педагогического опыта.
Ведущая роль теоретических знаний в содержании обучения.
Изучение требований ВУЗов по предмету.
Проблемный подход, направленный на формирование творческого отношения к решению задач.
Связь обучения с практикой.
Использование межпредметных связей при изучении темы.
Систематичность и последовательность преподавания и усвоения знаний.
Индивидуализация и дифференциация обучения.
Сознательность и активность учащихся в обучении достигается благодаря развитию и постоянному подкреплению интереса к предмету, возможностей использования полученных знаний в различных сферах деятельности.
Прочность усвоения знаний.
Развитие навыков самостоятельной работы.
Формирование творческого отношения к учебе и работе.
Использование программы «Поиск решения»
Программа «Поиск решения» позволяет получить результат на основе изменения значения нескольких ячеек. Кроме того, при выполнении поиска решения можно задать условия – ввести ограничения. Эти возможности позволяют использовать программу Excel для решения системы уравнений и уравнений, при решении которых необходимо учитывать область допустимых значений, для нахождения точек, в которых достигается максимум или минимум значения целевой функции нескольких переменных, определенных на множестве с линейными и нелинейными ограничениями. Другими словами – находить оптимальное решение задачи с ограничениями.
Модели всех задач на оптимизацию состоят из следующих элементов:
Переменные - неизвестные величины, которые нужно найти при решении задачи.
Целевая функция - величина, которая зависит от переменных и является целью, ключевым показателем эффективности или оптимальности модели.
Ограничения - условия, которым должны удовлетворять переменные.
Поиск решения такой модели рассмотрим на примерах.
Пример 1. Решить уравнение 5x - 8lnx = 8
Для решения данного уравнения с помощью инструмента «Поиск решения» нужно:
В ячейку C7 занести значение x=0,0000001(исходное значение параметра). Присвоить этой ячейке имя X.
В ячейку B7 записать формулу
=5*X-8*LN(X) (Рис. 1). Рис.1
В меню Сервис выбрать пункт Поиск решения… Если этого пункта в меню нет, то его следует загрузить, выполнив команду меню Сервис - Надстройки. В открывшемся диалоговом окне следует поставить флажок Поиск решения. В результате выполнения этой команды появится окно Поиск решения.
В поле Установить целевую ячейку указать ссылку на ячейку с целевой функцией, значение которой необходимо оптимизировать, т. е. $B$7 (Рис.2). При поиске решения целевая ячейка должна содержать формулу и быть прямо или косвенно связанной с ячейками с изменяемыми значениями. Рис.2
В поле Изменяя ячейки: указать ячейки, отведенные под переменные целевой функции – это ячейка X, ее адрес $C$7.
Кнопка Удалить удаляет ограничение из списка, а кнопка Редактировать дает возможность вносить изменения.
Кнопка Параметры вызывает окно диалога "Параметры поиска решения", в котором вы можете изменять параметры целевой функции.
В списке Ограничения: указать дополнительные условия, которые необходимо учитывать при поиске решения. Ограничения вводятся с помощью кнопки Добавить (Рис.3).
В ячейке параметр (X) получим решение данного уравнения (Рис.4).
Таким образом, с помощью Excel можно решать любые уравнения с одной переменной. Мы рассмотрели задачу поиска значения параметра, позволяющего достичь конкретной цели. Но решаемые задачи могут быть более сложными. Например, поиск нескольких параметров, обеспечивающих некоторый, наперед заданный результат.
Пример 2. Решить систему уравнений x1 + 5x2 + x3 = -7
Для поиска корней системы уравнений также следует воспользоваться инструментом Поиск решения (Рис.5).
Результаты решения системы уравнений появятся в соответствующих ячейках (Рис.6).
Кроме того, зачастую нас интересует не конкретный результат, а минимально или максимально возможный. В таких задачах при поиске решения также накладываются дополнительные условия. Такие задачи в Excel решают с помощью инструмента «Поиск решения».
Дети, обучающиеся в лицее, заинтересованы в получении разносторонних и глубоких знаний. Решение разнообразных задач позволит расширить представление старшеклассников о возможностях электронных таблиц, продемонстрировать практическое применение программы Excel на уроках математики. Кроме того, возможность получать новые знания способствуют развитию творческого отношения детей к решению разнообразных задач. Полученные знания будут полезны нашим выпускникам для успешного и комфортного изучения информатики в ВУЗах.
СПИСОК ЛИТЕРТУРЫ
Безручко В.Т. Практикум по курсу «Информатика». Работа в Windows, Word, Excel: Учеб. пособие. - М.: Финансы и статистика, 2002.
Информатика. 9 класс. Простейшие статистические характеристики. Начальные сведения из теории вероятностей. Решение прикладных (экономических) задач в Excel: сборник элективных курсов / авт.-сост. А.А.Чернов. - Волгоград: Учитель, 2006.
Презентация на тему: " Решение прикладных задач в Excel. Учитель: Латышева Е.В." — Транскрипт:
1 Решение прикладных задач в Excel. Учитель: Латышева Е.В.
2 Цели урока: 1. Формирование у учащихся новых понятий и способов действий в среде Excel; 2. Знакомство с основными технологическими приемами при решении задач оптимизации; 3. Приобретение навыков работы с надстройками Excel, а именно использование инструментов Excel Поиск решения и Подбор параметра.
3 Задача. Предположим, что мы решили производить 2 вида полок А и В. На изготовление модели А требуется 3 м 3 досок, на изготовление модели В - 4 м 3 досок. За неделю можно получить не более 1800 м 3 досок. На изготовление модели А требуется – 15 минут, модели В – 30 минут. Рабочая неделя для 4 сотрудников составляет 160 часов. Сколько полок А и В надо изготовить, чтобы получить максимальную прибыль, если полка А стоит 3500 рублей, полка В – 4800 рублей.
4 АВ 1 Наименование 2 А 3 В 4 5 Прибыль=3500*x+4800*y 6 7 Затраты на материалы=3*x+4*y 8 Затраты по времени=0,25*x+0,5*y 9
5 Выделим ячейку В5 и выберем меню Данные, после чего активизируем команду Поиск решения. Заполним ячейки этого окна следующим образом:
6 На экране увидим:
7 Пусть мы хотим получать максимальную прибыль в размере рублей. Используем функцию Подбор параметра для определения новых значений. Выберем эту команду и заполним ячейки окна следующим образом:
8 На экране увидим:
9 Выберем в качестве Изменения значения ячейки количество полок вида А.
10 На экране увидим:
11 Самостоятельная работа: Предположим, что мы решили производить несколько видов конфет. Назовем их условно «А», «В», «С». Известно, что реализация 10 килограммов конфет «А» дает прибыль 9 у.е., «В» - 10 у.е., «С» - 16 у.е. Конфеты можно производить в любых количествах (сбыт обеспечен), но запасы сырья ограничены. Необходимо определить, каких конфет и сколько десятков килограммов необходимо произвести, чтобы общая прибыль от реализации была максимальной. Нормы расхода сырья на производство 10 кг конфет каждого вида приведены ниже:
12 СырьеНормы расхода сырьяЗапас сырья АВС Какао Сахар Накопит ель Прибыль91016
13 Литература: 1. Макарова, Н. В. Информатика. Задачник по моделированию. - 9 класс. – СПб.: Питер, Чернов, А. А., Чернов, А. Ф. Информатика. Сборник элективных курсов. – 9 класс. – Волгоград: Учитель, 2007.
Читайте также: