Как определить функцию по графику в excel
Информатика. Базовый курс 2-е издание/Под ред. С.В. Симоновича. - СПб.: Питер, 2004.-640с.:ил.
- Персональные компьютеры;
- Приложение Windows – электронные таблицы Excel.
- Проектор
- Карточки с индивидуальными заданиями на построение графиков функций.
- Организационный момент – 3 мин.
- Проверка домашнего задания –10 мин.
- Объяснение нового материала –20 мин.
- Применение полученных знаний –20 мин.
- Самостоятельная работа. – 20 мин
- Подведение итогов урока. Домашнее задание – 7 мин.
Ход урока
Организационный момент
Проверка готовности учащихся к уроку, отметка отсутствующих, объявление темы и цели урока
Проверка домашнего задания. (фронтальный опрос)
- Что представляет собой рабочая область программы Excel?
- Как определяется адрес ячейки?
- Как изменить ширину столбца, высоту строки?
- Как ввести формулу в Excel?
- Что такое маркер заполнения и для чего он нужен?
- Что такое относительная адресация ячеек?
- Что такое абсолютная адресация ячеек? Как она задается?
- Что такое колонтитулы? Как они задаются?
- Как задать поля печатного документа? Как изменить ориентацию бумаги?
- Что такое функциональная зависимость у = f(х)? Какая переменная является зависимой, а какая независимой?
- Как ввести функцию в Excel?
- Что такое график функции у = f(х)?
- Как построить диаграмму в Excel?
Объяснение нового материала.
При объяснении нового материала может быть использован файл Excel с шаблонами задач (Приложение 1), который выводится на экран с помощью проектора
Сегодня мы рассмотрим применение табличного процессора Excel для графиков функций. На предыдущих практических вы уже строили диаграммы к различным задачам, используя Мастер диаграмм. Графики функций, так же как и диаграммы строятся с помощью Мастера диаграмм программы Excel.
Рассмотрим построение графиков функций на примере функции у = sin x.
Вид данного графика хорошо известен вам по урокам математики, попробуем построить его средствами Excel.
Программа будет строить график по точкам: точки с известными значениями будут плавно соединяться линией. Эти точки нужно указать программе, поэтому, сначала создается таблица значений функции у = f(х).
- отрезок оси ОХ, на котором будет строиться график.
- шаг переменной х, т.е. через какой промежуток будут вычисляться значения функции.
Задача 1.Построить график функции у = sin x на отрезке [– 2; 2] с шагом h = 0,5.
1. Заполним таблицу значений функции. В ячейку С4 введем первое значение отрезка: – 2
2. В ячейку D4 введем формулу, которая будет добавлять к лево-стоящей ячейки шаг: = В4 + $A$4
3. Маркером заполнения ячейки D4 заполним влево ячейки строки 4, до тех пор, пока получим значение другого конца отрезка: 2.
4. Выделим ячейку С5, вызовем Мастер функций, в категории математические выберем функцию SIN, в качестве аргумента функции выберем ячейку С4.
5. Маркером заполнения распространим эту формулу в ячейках строки 5 до конца таблицы.
Таким образом, мы получили таблицу аргументов (х) и значений (у) функции у = sin x на отрезке [-2;2] с шагом h = 0,5 :
x | -2 | -1,75 | -1,5 | -1,25 | -1 | -0,75 | -0,5 | -0,25 | 0 | 0,25 | 0,5 | 0,75 | 1 | 1,25 | 1,5 | 1,75 | 2 |
y | -0,9092 | -0,9839 | -0,9974 | -0,9489 | -0,8414 | -0,6816 | -0,4794 | -0,2474 | 0 | 0,2474 | 0,4794 | 0,6816 | 0,8414 | 0,9489 | 0,9974 | 0,9839 | 0,9092 |
6. Следующий шаг. Выделим таблицу и вызовем Мастер диаграмм. На первом шаге выберем во вкладке Нестандартные Гладкие графики.
7. На втором шаге во вкладке Ряд выполним:
В поле Ряд необходимо выделить ряд х и нажать на кнопку “Удалить” (график изменений х нам не нужен. График функции – это график изменения значений у)
В поле Подписи оси Х нажать на кнопку. Выделить в таблице ячейки со значениями х и нажмите на кнопку . Подписи по горизонтальной оси станут такими, как у нас в таблице.
8. На третьем шаге заполним вкладку Заголовки.
На самом деле пока это мало похоже на график функции в нашем привычном понимании.
- Вызовем контекстное меню оси ОУ. Затем, выберем пункт Формат оси…. Во вкладке Шкала установим: цена основного деления: 1. Во вкладке Шрифт установим размер шрифта 8пт.
- Вызовем контекстное меню оси ОХ. Выберем пункт Формат оси….
Во вкладке Шкала установим: пересечение с осью ОУ установите номер категории 5 (чтобы ось ОУ пересекала ось ОХ в категории с подписью 0, а это пятая по счету категория).
Во вкладке шрифт установите размер шрифта 8пт. Нажмите на кнопку ОК.
Остальные изменения выполняются аналогично.
Для закрепления рассмотрим еще одну задачу на построение графика функций. Эту задачу попробуйте решить самостоятельно, сверяясь с экраном проектора.
Применение полученных знаний.
Пригласить к проектору студента и сформулировать следующую задачу.
Задача 2. Построить график функции у = х 3 на отрезке [– 3; 3] с шагом h = 0,5.
1. Создать следующую таблицу: Создать таблица значений функции у = f(х).
2. В ячейку С4 ввести первое значение отрезка: –3
3. В ячейку D4 ввести формулу, которая будет добавлять к лево-стоящей ячейки шаг: = В4 + $A$4
4. Маркером заполнения ячейки D3 заполнить влево ячейки строки 3, до тех пор, пока не будет получено значение другого конца отрезка: 3.
5. В ячейку С5 ввести формулу вычисления значения функции: = С4^3
6. Маркером заполнения скопировать формулу в ячейки строки 5 до конца таблицы.
Таким образом, должна получиться таблица аргументов (х) и значений (у) функции у = х 3 на отрезке [–3;3] с шагом h = 0,5:
х | -3 | -2,5 | -2 | -1,5 | -1 | -0,5 | 0 | 0,5 | 1 | 1,5 | 2 | 2,5 | 3 |
y | -27 | -15,625 | -8 | -3,375 | -1 | -0,125 | 0 | 0,125 | 1 | 3,375 | 8 | 15,625 | 27 |
7. Выделить таблицу и вызвать мастер диаграмм. На первом шаге выбрать во второй вкладке Гладкие графики.
8. На втором шаге во вкладке Ряд выполнить:
- В поле Ряд выделить ряд х и нажать на кнопку “Удалить” (график изменений х нам не нужен. График функции – это график изменения значений у)
- В поле Подписи оси Х нажать на кнопку . Выделить в таблице ячейки со значениями х и нажать на кнопку . Подписи по горизонтальной оси станут такими, как у нас в таблице.
9. На третьем шаге заполнить вкладку Заголовки.
10. Пример полученного графика:
11. Оформить график.
12. Установить параметры страницы и размеры диаграмм таким образом, что бы все поместилось на одном листе альбомной ориентации.
13. Создать колонтитулы для данного листа (Вид Колонтитулы…):
14. Верхний колонтитул слева: график функции у = x 3
Сохранить документ своей папке под именем График.
Самостоятельная работа.
Работа по карточкам с индивидуальными заданиями. (Приложение 2)
Пример карточки, с задачей в общем виде, выводится на экран с помощью проектора.
1. Построить график функции y=f(x) на отрезке [a;b] с шагом h=c
2. Установить параметры страницы и размеры графика таким образом, что бы все поместилось на одном листе альбомной ориентации.
3. Создать колонтитулы для данного листа (Вид Колонтитулы…):
- Верхний колонтитул слева: график функции y=f(x)
- Нижний колонтитул в центре: ваши Ф.И.О. и дата
4. Сохранить в своей папке под именем “Зачетный график”
5. Вывести документ на печать.
После выполнения задания правильность каждого варианта проверяется с помощью проектора.
Информация воспринимается легче, если представлена наглядно. Один из способов презентации отчетов, планов, показателей и другого вида делового материала – графики и диаграммы. В аналитике это незаменимые инструменты.
Построить график в Excel по данным таблицы можно несколькими способами. Каждый из них обладает своими преимуществами и недостатками для конкретной ситуации. Рассмотрим все по порядку.
Простейший график изменений
График нужен тогда, когда необходимо показать изменения данных. Начнем с простейшей диаграммы для демонстрации событий в разные промежутки времени.
Допустим, у нас есть данные по чистой прибыли предприятия за 5 лет:
Год | Чистая прибыль* |
2010 | 13742 |
2011 | 11786 |
2012 | 6045 |
2013 | 7234 |
2014 | 15605 |
* Цифры условные, для учебных целей.
Заходим во вкладку «Вставка». Предлагается несколько типов диаграмм:
Выбираем «График». Во всплывающем окне – его вид. Когда наводишь курсор на тот или иной тип диаграммы, показывается подсказка: где лучше использовать этот график, для каких данных.
Выбрали – скопировали таблицу с данными – вставили в область диаграммы. Получается вот такой вариант:
Прямая горизонтальная (синяя) не нужна. Просто выделяем ее и удаляем. Так как у нас одна кривая – легенду (справа от графика) тоже убираем. Чтобы уточнить информацию, подписываем маркеры. На вкладке «Подписи данных» определяем местоположение цифр. В примере – справа.
Улучшим изображение – подпишем оси. «Макет» – «Название осей» – «Название основной горизонтальной (вертикальной) оси»:
Заголовок можно убрать, переместить в область графика, над ним. Изменить стиль, сделать заливку и т.д. Все манипуляции – на вкладке «Название диаграммы».
Вместо порядкового номера отчетного года нам нужен именно год. Выделяем значения горизонтальной оси. Правой кнопкой мыши – «Выбрать данные» — «Изменить подписи горизонтальной оси». В открывшейся вкладке выбрать диапазон. В таблице с данными – первый столбец. Как показано ниже на рисунке:
Можем оставить график в таком виде. А можем сделать заливку, поменять шрифт, переместить диаграмму на другой лист («Конструктор» — «Переместить диаграмму»).
График с двумя и более кривыми
Допустим, нам нужно показать не только чистую прибыль, но и стоимость активов. Данных стало больше:
Но принцип построения остался прежним. Только теперь есть смысл оставить легенду. Так как у нас 2 кривые.
Добавление второй оси
Как добавить вторую (дополнительную) ось? Когда единицы измерения одинаковы, пользуемся предложенной выше инструкцией. Если же нужно показать данные разных типов, понадобится вспомогательная ось.
Сначала строим график так, будто у нас одинаковые единицы измерения.
Выделяем ось, для которой хотим добавить вспомогательную. Правая кнопка мыши – «Формат ряда данных» – «Параметры ряда» — «По вспомогательной оси».
Нажимаем «Закрыть» — на графике появилась вторая ось, которая «подстроилась» под данные кривой.
Это один из способов. Есть и другой – изменение типа диаграммы.
Щелкаем правой кнопкой мыши по линии, для которой нужна дополнительная ось. Выбираем «Изменить тип диаграммы для ряда».
Определяемся с видом для второго ряда данных. В примере – линейчатая диаграмма.
Всего несколько нажатий – дополнительная ось для другого типа измерений готова.
Строим график функций в Excel
Вся работа состоит из двух этапов:
- Создание таблицы с данными.
- Построение графика.
Пример: y=x(√x – 2). Шаг – 0,3.
Составляем таблицу. Первый столбец – значения Х. Используем формулы. Значение первой ячейки – 1. Второй: = (имя первой ячейки) + 0,3. Выделяем правый нижний угол ячейки с формулой – тянем вниз столько, сколько нужно.
В столбце У прописываем формулу для расчета функции. В нашем примере: =A2*(КОРЕНЬ(A2)-2). Нажимаем «Ввод». Excel посчитал значение. «Размножаем» формулу по всему столбцу (потянув за правый нижний угол ячейки). Таблица с данными готова.
Переходим на новый лист (можно остаться и на этом – поставить курсор в свободную ячейку). «Вставка» — «Диаграмма» — «Точечная». Выбираем понравившийся тип. Щелкаем по области диаграммы правой кнопкой мыши – «Выбрать данные».
Выделяем значения Х (первый столбец). И нажимаем «Добавить». Открывается окно «Изменение ряда». Задаем имя ряда – функция. Значения Х – первый столбец таблицы с данными. Значения У – второй.
Жмем ОК и любуемся результатом.
Наложение и комбинирование графиков
Построить два графика в Excel не представляет никакой сложности. Совместим на одном поле два графика функций в Excel. Добавим к предыдущей Z=X(√x – 3). Таблица с данными:
Выделяем данные и вставляем в поле диаграммы. Если что-то не так (не те названия рядов, неправильно отразились цифры на оси), редактируем через вкладку «Выбрать данные».
А вот наши 2 графика функций в одном поле.
Графики зависимости
Данные одного столбца (строки) зависят от данных другого столбца (строки).
Построить график зависимости одного столбца от другого в Excel можно так:
Условия: А = f (E); В = f (E); С = f (E); D = f (E).
Выбираем тип диаграммы. Точечная. С гладкими кривыми и маркерами.
Выбор данных – «Добавить». Имя ряда – А. Значения Х – значения А. Значения У – значения Е. Снова «Добавить». Имя ряда – В. Значения Х – данные в столбце В. Значения У – данные в столбце Е. И по такому принципу всю таблицу.
Готовые примеры графиков и диаграмм в Excel скачать:
Точно так же можно строить кольцевые и линейчатые диаграммы, гистограммы, пузырьковые, биржевые и т.д. Возможности Excel разнообразны. Вполне достаточно, чтобы наглядно изобразить разные типы данных.
Учитывая, что функция – зависимость одного параметра от другого, зададим значения для оси абсцисс с шагом 0,5. Строить график будем на отрезке [-3;3]. Называем столбец «х» , пишем первое значение «-3» , второе – «-2,5» . Выделяем их и тянем вниз за черный крестик в правом нижнем углу ячейки.
Будем строить график функции вида y=х^3+2х^2+2. В ячейке В1 пишем «у» , для удобства можно вписать всю формулу. Выделяем ячейку В2 , ставим «=» и в «Строке формул» пишем формулу: вместо «х» ставим ссылку на нужную ячейку, чтобы возвести число в степень, нажмите «Shift+6» . Когда закончите, нажмите «Enter» и растяните формулу вниз.
У нас получилась таблица, в одном столбце которой записаны значения аргумента – «х» , в другом – рассчитаны значения для заданной функции.
Перейдем к построению графика функции в Excel. Выделяем значения для «х» и для «у» , переходим на вкладку «Вставка» и в группе «Диаграммы» нажимаем на кнопочку «Точечная» . Выберите одну из предложенных видов.
График функции выглядит следующим образом.
Теперь покажем, что по оси «х» установлен шаг 0,5. Выделите ее и кликните по ней правой кнопкой мши. Из контекстного меню выберите пункт «Формат оси» .
Откроется соответствующее диалоговое окно. На вкладке «Параметры оси» в поле «цена основных делений» , поставьте маркер в пункте «фиксированное» и впишите значение «0,5» .
Чтобы добавить название диаграммы и название для осей, отключить легенду, добавить сетку, залить ее или выбрать контур, поклацайте по вкладкам «Конструктор» , «Макет» , «Формат» .
Построить график функции в Эксель можно и с помощью «Графика» . О том, как построить график в Эксель, Вы можете прочесть, перейдя по ссылке.
Давайте добавим еще один график на данную диаграмму. На этот раз функция будет иметь вид: у1=2*х+5. Называем столбец и рассчитываем формулу для различных значений «х» .
Выделяем диаграмму, кликаем по ней правой кнопкой мыши и выбираем из контекстного меню «Выбрать данные» .
В поле «Элементы легенды» кликаем на кнопочку «Добавить» .
Появится окно «Изменение ряда» . Поставьте курсор в поле «Имя ряда» и выделите ячейку С1 . Для полей «Значения Х» и «Значения У» выделяем данные из соответствующих столбцов. Нажмите «ОК» .
Чтобы для первого графика в Легенде не было написано «Ряд 1» , выделите его и нажмите на кнопку «Изменить» .
Ставим курсор в поле «Имя ряда» и выделяем мышкой нужную ячейку. Нажмите «ОК» .
Ввести данные можно и с клавиатуры, но в этом случае, если Вы измените данные в ячейке В1 , подпись на диаграмме не поменяется.
В результате получилась следующая диаграмма, на которой построены два графика: для «у» и «у1» .
Думаю теперь, Вы сможете построить график функции в Excel, и при необходимости добавлять на диаграмму нужные графики.
Для рассмотренных выше задач удавалось построить уравнение или систему уравнений. Но во многих случаях при решении практических задач имеются лишь экспериментальные (результаты измерений, статистические, справочные, опытные) данные. По ним с определенной мерой близости пытаются восстановить эмпирическую формулу (уравнение), которая может быть использована для поиска решения, моделирования, оценки решений, прогнозов.
Процесс подбора эмпирической формулы P(x) для опытной зависимости F(x) называется аппроксимацией (сглаживанием). Для зависимостей с одним неизвестным в Excel используются графики, а для зависимостей со многими неизвестными – пары функций из группы Статистические ЛИНЕЙН и ТЕНДЕНЦИЯ, ЛГРФПРИБЛ и РОСТ .
В настоящем разделе рассматривается аппроксимация экспериментальных данных с помощью графиков Excel: на основе данных стоится график, к нему подбирается линия тренда, т.е. аппроксимирующая функция, которая с максимальной степенью близости приближается к опытной зависимости.
Степень близости подбираемой функции оценивается коэффициентом детерминации R 2 . Если нет других теоретических соображений, то выбирают функцию с коэффициентом R 2 , стремящимся к 1. Отметим, что подбор формул с использованием линии тренда позволяет установить как вид эмпирической формулы, так и определить численные значения неизвестных параметров.
Excel предоставляет 5 видов аппроксимирующих функций:
1. Линейная – y=cx+b. Это простейшая функция, отражающая рост и убывание данных с постоянной скоростью.
2. Полиномиальная – y=c +c1x+c2x 2 +…+c6x 6 . Функция описывает попеременно возрастающие и убывающие данные. Полином 2-ой степени может иметь один экстремум (min или max), 3-ей степени – до 2-х экстремумов, 4-ой степени – до 3-х и т.д.
3. Логарифмическая – y=clnx+b. Эта функция описывает быстро возрастающие (убывающие) данные, которые затем стабилизируются.
4. Степенная – y=cx b , (х>0и y>0). Функция отражает данные с постоянно увеличивающейся (убывающей) скоростью роста.
5. Экспоненциальная – y=ce bx , (e – основание натурального логарифма). Функция описывает быстро растущие (убывающие) данные, которые затем стабилизируются.
Для всех 5-ти видов функций используется аппроксимация данных по методу наименьших квадратов (см. справку по F1 "линия тренда").
В качестве примера рассмотрим зависимость продаж от рекламы, заданную следующими статистическими данными по некоторой фирме:
Реклама (тыс. руб.) | 1,5 | 2,5 | 3,5 | 4,5 | 5,5 |
Продажи (тыс. руб.) |
Необходимо построить функцию, наилучшим образом отражающую эту зависимость. Кроме того, необходимо оценить продажи для рекламных вложений в 6 тыс. руб.
Приступим к решению. В первую очередь введите эти данные в Excel и постройте график, как на рис. 38. Как видно, график построен на основании диапазона B2:J2. Далее, щелкнув правой кнопкой мыши по графику, добавьте линию тренда, как показано на рис. 38.
Чтобы подписать ось Х соответствующими значениями рекламы (как на рис. 38), следует в ниспадающем меню (рис. 38) выбрать пункт Исходные данные. В открывшемся одноименном окне, в закладке Ряд, в поле Подписи оси Х, укажите диапазон ячеек, где записаны значения Х (здесь $B$1:$K$1):
В открывшемся окне настройки (рис. 39), на закладке Тип выберите для аппроксимации логарифмическую линию тренда (по виду графика). На закладке Параметры установите флажки, отображающие на графике уравнение и коэффициент детерминации.
После нажатия ОК Вы получите результат, как на рис. 40. Коэффициент детерминации R 2 =0.9846, что является неплохой степенью близости. Для подтверждения правильности выбранной функции (поскольку других теоретических соображений нет) спрогнозируйте развитие продаж на 10 периодов вперед. Для этого щелкните правой кнопкой по линии тренда – измените формат – после этого в поле Прогноз: вперед на: установите 10 (рис. 41).
После установки прогноза Вы увидите изменение кривой графика на 10 периодов наблюдения вперед, как на рис. 42. Он с большой долей вероятности отражает дальнейшее увеличение продаж с увеличением рекламных вложений.
Вычисление по полученной формуле =237,96*LN(6)+5,9606 в Excel дает значение 432 тыс. руб.
В Excel имеется функция ПРЕДСКАЗ(), которая вычисляет будущее значение Y по существующим парам значений X и Y значениям с использованием линейной регрессии. Функция Y по возможности должна быть линейной, т.е. описываться уравнением типа c+bx. Функция предсказания для нашего примера запишется так: =ПРЕДСКАЗ(K1;B2:J2;B1:J1). Запишите – должно получится значение 643,6 тыс. руб.
Часть11. Контрольные задания
Интересует вопрос как в Ексель узнать значение функции в промежуточных точках.
Я построил график в Ексель по экспериментальным данным, например - в момент времени 1 величина принимает значение y1, а в момент времени 2 величина принимает значение y2 (y1 и y2 получены экспериментально). А мне нужно узнать чему равна величина в момент времени 1.5 - хочу определить это по графику, но не на глаз, а точно. В Екселе это можно сделать?
При наведении мышью на график Ексель выдает значение абсциссы. А мне надо значение функции - ординату. На глаз грубо и так видно, но мне надо точно до 0.001
А мне надо значение функции - ординату. На глаз грубо и так видно, но мне надо точно до 0.001
Добавьте к графику линию тренда. Выберите по величине R^2(?) тип, наиболее точно соответствующий графику (линейный, степенной, экспоненциальный) и в уравнение этого тренда подставляйте значение абсциссы.
нельзя по двум точкам построить функцию:)
это может быть линия или парабола, причем вторая как вниз, так и вверх основанием.. очень смешные результаты получатся.
нельзя по двум точкам построить функцию
это может быть линия или парабола, причем вторая как вниз, так и вверх основанием.
Я построил график в Ексель по экспериментальным данным
Хороши экспериментальные данные из двух измерений. :)
нельзя по двум точкам построить функцию:)
Хороши экспериментальные данные из двух измерений. :)
С чего выдумал, что у меня два измерения? У меня их сотни, не меньше тысячи. Если вы про это Я построил график в Ексель по экспериментальным данным, например - в момент времени 1 величина принимает значение y1, а в момент времени 2 величина принимает значение y2 (y1 и y2 получены экспериментально). так это было приведено в качестве примера, не буду же я писать y1, y2, y3, . y99, . y967 . Улыбайся.
С чего выдумал, что у меня два измерения?
Читайте внимательнее.
Не я написал, что
нельзя по двум точкам построить функцию
И прокомментировал я, как несложно догадаться, ответ Andriy,
Хороши экспериментальные данные из двух измерений
Это было про меня
у меня всего два измерения
Well, OK:
Lexander иди учи Ексель
читайте внимательно
До свидания
Lexander, все-таки, настаиваю на том, что Вам нужно осилить, хотя бы, интерполяцию, а лучше аппроксимацию. Так как вопрос у Вас из серии: "как, имея три рубля, получить шесть?"
Вкраце: узнать значения функции в промежуточных точках по таблично заданной функции НЕЛЬЗЯ. Можно узнать значения ДРУГИХ функций, которые будут проходить через заданные точки (интерполяция) или проходить очень близко к заданным точкам (аппроксимация - на практике предпочтительней).
То есть Вы заменяете неизвестную Вам функцию (о которой известно лишь конкретные ее точки - результаты эксперимента) на другую функцию, и уже берете значения этой другой функции в промежуточных точках. Через заданные точки (сколько бы их ни было) можно провести сколько угодно функций. Именно поэтому Ваш вопрос изначально некорректен. Ексель воспользуется одним методом интерполяции, матлаб другим - значение функции в промежуточных точках будет разным.
Более того, лучше воспользоваться не интерполяцией (когда нужно построить функцию, строго проходяющую через заданные точки), а аппроксимацией. Так как если исходные точки получены экспериментально, то в них обязательно будет погрешность, поэтому проходить строго через них не нужно - это лишь увеличит общую разницу между полученной функцией и неизвестной реальной.
Lexander, все-таки, настаиваю на том, что Вам нужно осилить, хотя бы, интерполяцию, а лучше аппроксимацию. Так как вопрос у Вас из серии: "как, имея три рубля, получить шесть?"
Вкраце: узнать значения функции в промежуточных точках по таблично заданной функции НЕЛЬЗЯ. Можно узнать значения ДРУГИХ функций, которые будут проходить через заданные точки (интерполяция) или проходить очень близко к заданным точкам (аппроксимация - на практике предпочтительней).
То есть Вы заменяете неизвестную Вам функцию (о которой известно лишь конкретные ее точки - результаты эксперимента) на другую функцию, и уже берете значения этой другой функции в промежуточных точках. Через заданные точки (сколько бы их ни было) можно провести сколько угодно функций. Именно поэтому Ваш вопрос изначально некорректен. Ексель воспользуется одним методом интерполяции, матлаб другим - значение функции в промежуточных точках будет разным.
Более того, лучше воспользоваться не интерполяцией (когда нужно построить функцию, строго проходяющую через заданные точки), а аппроксимацией. Так как если исходные точки получены экспериментально, то в них обязательно будет погрешность, поэтому проходить строго через них не нужно - это лишь увеличит общую разницу между полученной функцией и неизвестной реальной.
Ему всего-навсего нужно знать закон, по которому ексель (ред. авт. сохр.) строит график. И все.
Вляпалась. отнюдь. Если автор что то строит по экспериментальным данным, то считать то, что ему выдал "эксель", за искому зависимость, мягко говоря, наивно.
Это во-первых.
Во-вторых, эксель строит график путем соединения соседних точек. Никаких функций при этом не строится (интерполяции и аппроксимации не происходит).
Чтобы найти значения в промежуточных точках надо как раз провести интерполяцию или аппроксимацию.
gav, (добродушно) ну, не совсем так. Посмотрите выше я приводил ссылку на библиотеку на php. Если excel соединяет точки, причем, ЧСХ, гладко, значит в нем какая-то математика для этого зашита. А вот как он сие делает (методика), мне не ведомо, ибо гугль не признается
Во-вторых, эксель строит график путем соединения соседних точек. Никаких функций при этом не строится (интерполяции и аппроксимации не происходит).
Чтобы найти значения в промежуточных точках надо как раз провести интерполяцию или аппроксимацию.
1. Автор хочет знать Y в промежуточной точке ГРАФИКА - читаем исходный пост.
2. Последняя фраза, особенно "или" - впечатлила. Мда.
1. Автор хочет знать Y в промежуточной точке ГРАФИКА - читаем исходный пост.
При построении графика Эксель тупо соединяет каждые две соседние точки прямой. Соответственно, берем точку. Смотрим какому отрезку принадлежит, эти две соседние точки соединены прямой. Строим по ним прямую. На ней будет лежать точка, которую ищет автор. Но поступать так - аморально для студентов, а уж тем более для аспирантов. Надо делать по-человечки, т.е. через интерполяцию или аппроксимацию по нашим данным.
При построении графика Эксель тупо соединяет каждые две соседние точки прямой.
Ой. Значит, мне показалось, что при выборе нестандартных типов диаграмм есть "гладкие графики". А там как тупо? Сплайнами? Кубическими?
Вляпалась. да какими сплайнами? Просто прямую строит по двум точкам, да и то, чтобы только соединить эти точки (подозреваю, что только в процессе вывода на экран "графика"). Нигде она (прямая) в "аналитическом" виде в системе "не оседает". Так что узнать там значения не представляется возможным, если искусственно эту прямую не организовать. Но смысла в этом нет никакого - лучше нормальную интерполяцию или аппроксимацию.
Добавлено через 6 минут
Hogfather, Посмотрите выше я приводил ссылку на библиотеку на php. Если excel соединяет точки, причем, ЧСХ, гладко, значит в нем какая-то математика для этого зашита
Где в ссылке про php говорится, что Excel плавно точки соединяет?
gav, Ви таки пойдите на привоз, купите селедку и морочьте ей голову. Ну не знаете Excel, так и скажите
Где в ссылке про php говорится, что Excel плавно точки соединяет?
Нигде. Там рассказывается на пальцах, как соединяют точки.
А плавно Excel соединяет точки по определению. Вот пример, график одного и того же (два способа построения графика).
Ребята, у графических тулбоксов цель - строить графики. Красивые, симпатишные, с прибамбасами и т.п. Но мне кажется несколько самонадеянным считать, что графические тулбоксы снабжены еще сложными математическими методами, которые прежде, чем построить график решают еще поставленные математические задачи (причем, мы конкретную математическую задачу им и не ставили, вообще говоря). Поэтому графические тулбоксы отдельно, математические методы обработки данных отдельно. Это мое мнение.
Если будете интерполировать, то да, лучше кубическим сплайнами. Там в Екселе должна быть функция (наверное), которая автоматически всё сделает и заодно выдаст информацию в интересующих вас промежуточных точках.
Добавлено через 2 минуты
А плавно Excel соединяет точки по определению.
То есть я так понимаю, что Ексель делает интерполяцию перед построением графика? Возможно я чего-то не понимаю.
Hogfather, так там поди второй график как сплайн выбран?
Добавлено через 1 минуту
Uzanka, да, есть там в типе графиков "плавная кривая". Но опять же что за метод ее построения - загадка.
В любом случае, это просчитывается на этапе вывода графика на экран. И нигде в OLE не фигурирует..
gav, вот и я о том. Конкретная математическая задача нами не была еще поставлена, а Ексель уже за нас всё решил ))
Вляпалась.
Hogfather,
Ребята, я посмотрела сейчас, построила графики в Ексель. Должна признать, что не права. При выборе опции "Гладкая" там однозначно работает какой-то алгоритм. Вопрос только какой метод стоит там по умолчанию.
Uzanka, понятно, что какой то алгоритм в конечном счете используется для вывода графика :)
Вопрос в том, что он не предназначен для трассировки точек графика и для нахождения прмежуточных значений. Его роль - исключительно визуализация.
Если кому то надо получить значения в промежуточных точках, то он должен нормальную интерполяцию и аппроксимацию делать соответствующими функциями, кои есть в наличии. Ссылки Hogfather это явно подтверждают.
gav, (добродушно) Это мы еще не затронули обоснование степени полинома при аппроксимации. В Excel эта задача решается, но нужно знать, в какой бубен бить.
Чё делается .
Да, пионэрам надо бы хоть чуть-чуть знать матчасть. "Апроксимация" посредством экселя - дикое убожество, конечно. Особенно, если не понимать как этот магический продукт работает. И не знать основ математики.
(добродушно) Это мы еще не затронули обоснование степени полинома при аппроксимации
Не будем даже упоминать о том, что вообще говоря, сходимость, например, метода наименьших квадратов строго доказана только для одного класса функций - полиномов Чебышева .
И нигде в OLE не фигурирует..
Причем тут OLE-то ?
График 1 - исходная функция (уравнение которой написано под графиком)
График 2 - сглаженная функция.
График 3 - аппроксимация средствами Excel, (судя по величине R^2 - убогая :) ).
Тип урока: Обобщение, закрепление пройденного материала и объяснение нового.
Цели и задачи урока:
- повторение изученных графиков функций;
- повторение и закрепление графического способа решения уравнений;
- закрепление навыков записи и копирования формул, построения графиков функций в электронных таблицах Excel 2007;
- формирование и первичное закрепление знаний о решении уравнений с использованием возможностей электронных таблиц Excel 2007;
- формирование мышления, направленного на выбор оптимального решения;
- формирование информационной культуры школьников.
Оборудование: персональные компьютеры, мультимедиапроектор, проекционный экран.
Материалы к уроку: презентация Power Point на компьютере учителя (Приложение 1).
Организационный момент.
Слайд 1 из Приложения1 ( далее ссылки на слайды идут без указания Приложения1).
Объявление темы урока.
1. Устная работа (актуализация знаний).
Слайд 2 - Соотнесите перечисленные ниже функции с графиками на чертеже (Рис. 1):
у = 6 - х; у = 2х + 3; у = (х + 3) 2 ; у = -(х - 4) 2 ; .
Слайд 3 Графический способ решения уравнений вида f(x)=0.
Корнями уравнения f(x)=0 являются значения х1, х2, … точек пересечения графика функции y=f(x) с осью абсцисс (Рис. 2).
Найдите корни уравнения х 2 -2х-3=0, используя графический способ решения уравнений (Рис.3).
Слайд 5 Графический способ решения уравнений вида f (x)=g (x).
Корнями уравнения f(x)=g(x) являются значения х1, х2, … точек пересечения графиков функций y=f(x) и у=g(x). (Рис. 4):
Слайд 6 Найдите корни уравнения , используя графический способ решения уравнений (Рис. 5).
2. Объяснение нового материала. Практическая работа.
Решение уравнений графическим способом требует больших временных затрат на построение графиков функций и в большинстве случаев дает грубо приближенные решения. При использовании электронных таблиц, в данном случае – Microsoft Excel 2007, существенно экономится время на построение графиков функций, и появляются дополнительные возможности нахождения корней уравнения с заданной точностью (метод Подбор параметра).
I. Графический способ решения уравнений вида f(x)=0 в Excel.
Дальнейшая работа выполняется учителем в Excel одновременно с учениками с подробными (при необходимости) инструкциями и выводом результатов на проекционный экран. Слайды Приложения 1 используются для формулировки задач и подведения промежуточных итогов.
Пример1: Используя средства построения диаграмм в Excel, решить графическим способом уравнение -х 2 +5х-4=0.
Для этого: построить график функции у=-х 2 +5х-4 на промежутке [ 0; 5 ] с шагом 0,25; \найти значения х точек пересечения графика функции с осью абсцисс.
Выполнение задания можно разбить на этапы:
1 этап: Представление функции в табличной форме (рис. 6):
- в ячейку А1 ввести текст Х, в ячейку A2 — Y;
- в ячейку В1 ввести число 0, в ячейку С1 – число 0,25;
- выделить ячейки В1:С1, подвести указатель мыши к маркеру выделения, и в тот момент, когда указатель мыши примет форму черного крестика, протянуть маркер выделения вправо до ячейки V1 (Рис. 7).
- в ячейку B2 ввести формулу =-(B1^2)+5*B1-4;
При вводе формулы можно вводить адрес ячейки с клавиатуры (не забыть переключиться на латиницу), а можно просто щелкнуть мышью на ячейке с нужным адресом.
После ввода формулы в ячейке окажется результат вычисления по формуле, а в поле ввода строки формул - сама формула (Рис. 8):
- скопировать содержимое ячейки B2 в ячейки C2:V2 за маркер выделения. Весь ряд выделенных ячеек заполнится содержимым первой ячейки. При этом ссылки на ячейки в формулах изменятся относительно смещения самой формулы.
2 этап: Построение диаграммы типа График.
- выделить диапазон ячеек B2:V2;
- на вкладке Вставка|Диаграммы|График выбрать вид График;
- на вкладке Конструктор|Выбрать данные (Рис. 9) в открывшемся окне «Выбор источника данных» щелкнуть по кнопке Изменить в поле Подписи горизонтальной оси - откроется окно «Подписи оси». Выделить в таблице диапазон ячеек B1:V1 (значения переменной х). В обоих окнах щелкнуть по кнопкам ОК;
- на вкладке Макет|Оси|Основная горизонтальная ось|Дополнительные параметры основной горизонтальной оси выбрать:
Интервал между делениями: 4;
Интервал между подписями: Единица измерения интервала: 4;
Положение оси: по делениям;
Выбрать ширину и цвет линии (Вкладки Тип линии и Цвет линии);
- самостоятельно изменить ширину и цвет линии для вертикальной оси;
- на вкладке Макет|Сетка|Вертикальные линии сетки по основной оси выбрать Основные линии сетки.
Примерный результат работы приведен на рис. 10:
3 этап: Определение корней уравнения.
График функции у=-х 2 +5х-4 пересекает ось абсцисс в двух точках и, следовательно, уравнение -х 2 +5х-4=0 имеет два корня: х1=1; х2=4.
II. Графический способ решения уравнений вида f(x)=g(x) в Excel.
Пример 2: Решить графическим способом уравнение .
Для этого: в одной системе координат построить графики функций у1= и у2=1-х на промежутке [ -1; 4 ] с шагом 0,25; найти значение х точки пересечения графиков функций.
1 этап: Представление функций в табличной форме (рис. 1):
2 этап: Построение диаграммы типа График.
Примерный результат работы приведен на Рис. 12:
3 этап: Определение корней уравнения.
Графики функций у1= и у2=1-х пересекаются в одной точке (0;1) и, следовательно, уравнение имеет один корень – абсцисса этой точки: х=0.
III. Метод Подбор параметра.
Графический способ решения уравнений красив, но далеко не всегда точки пересечения могут быть такими «хорошими», как в специально подобранных примерах 1 и 2.
Возможности электронных таблиц позволяют находить приближенные значения коней уравнения с заданной точностью. Для этого используется метод Подбор параметра.
Пример 3: Разберем метод Подбор параметра на примере решения уравнения -х 2 +5х-3=0.
1 этап: Построение диаграммы типа График для приближенного определения корней уравнения.
Построить график функции у=-х 2 +5х-3, отредактировав полученные в Примере 1 формулы.
- выполнить двойной щелчок по ячейке B2, внести необходимые изменения;
- с помощью маркера выделения скопировать формулу во все ячейки диапазона C2:V2.
Все изменения сразу отобразятся на графике.
Примерный результат работы приведен на Рис. 13:
2 этап: Определение приближенных значений корней уравнения.
График функции у=-х 2 +5х-3 пересекает ось абсцисс в двух точках и, следовательно, уравнение -х 2 +5х-4=0 имеет два корня.
По графику приближенно можно определить, что х1≈0,7; х2≈4,3.
3 этап: Поиск приближенного решения уравнения с заданной точностью методом Подбор параметра.
1) Начать с поиска более точного значения меньшего корня.
По графику видно, что ближайший аргумент к точке пересечения графика с осью абсцисс равен 0,75. В таблице значений функции этот аргумент размещается в ячейке E1.
- Выделить ячейку Е2;
- перейти на вкладку Данные|Анализ «что-если»|Подбор параметра…;
В открывшемся диалоговом окне Подбор параметра (Рис. 14) в поле Значение ввести требуемое значение функции: 0.
В поле Изменяя значение ячейки: ввести $E$1 (щелкнув по ячейке E1).
Щелкнуть по кнопке ОК.
- В окне Результат подбора (Рис. 15) выводится информация о величине подбираемого и подобранного значения функции:
- В ячейке E1 выводится подобранное значение аргумента 0,6972 с требуемой точностью (0,0001).
Установить точность можно путем установки в ячейках таблицы точности представления чисел – числа знаков после запятой (Формат ячеек|Число|Числовой).
Итак, первый корень уравнения определен с заданной точностью: х1≈0,6972.
2) Самостоятельно найти значение большего корня с той же точностью. (х2≈4,3029).
IV. Метод Подбор параметра для решения уравнений вида f(x)=g(x).
При использовании метода Подбор параметров для решения уравнений вида f(x)=g(x) вводят вспомогательную функцию y(x)=f(x)-g(x) и находят с требуемой точностью значения х точек пересечения графика функции y(x) с осью абсцисс.
3. Закрепление изученного материала. Самостоятельная работа.
Задание: Используя метода Подбор параметров, найти корни уравнения с точностью до 0,001.
- ввести функцию у= и построить ее график на промежутке [ -1; 4 ] с шагом 0,25 (Рис. 16):
- найти приближенное значение х точки пересечения графика функции с осью абсцисс (х≈1,4);
- найти приближенное решение уравнения с точностью до 0,001 методом Подбор параметра (х≈1,438).
Слайд 12 Проверка результатов самостоятельной работы.
Слайд 13 Повторение графического способа решения уравнения вида f(x)=0.
Слайд 14 Повторение графического способа решения уравнения вида f(x)=g(x).
5. Домашнее задание.
Используя средства построения диаграмм в Excel и метод Подбор параметра, определите корни уравнения х 2 -5х+2=0 с точностью до 0,01.
Электронные таблицы EXCEL можно использовать при изучении многих разделов физики, математики и других предметов. Одной из данных тем является математическая тема: “Анализ элементарных функций”. Данная работа посвящена использованию электронных таблиц EXCEL в анализе элементарных функций.
На рис.1 представлена некоторая функция, заданная с помощью таблицы, построение которой приведено в Приложении 1.
Особенность анализа функций с помощью электронных таблиц заключается в том, что анализ проводится на дискретной информации. Действительно, количество значений исследуемой функции конечно и дано (как правило) с некоторым постоянным шагом по аргументу.
- определение максимума (минимума) данной функции на заданном интервале,
- определение возрастания и/или убывания функции на заданном интервале,
- определение четности заданной функции,
- определение периодичности заданной функции,
- определение значения первой производной данной функции и наличия экстремумов.
Для решения этих задач потребуется построить таблицы ВводаИсходнойИнформации, таблицы ВыводаРезультатов, Расчетной таблицы для обработки информации и дополнительно построим Вспомогательную таблицу. Таблицу ВводаИсходнойИнформации и основу Расчетной таблицу построены при введении значений исследуемой функции в Приложении 1 и показанной на рис.1. Построим решения задач анализа.
Задача 1.
Для заданной функции на заданном интервале аргумента определить ее минимальное и максимальное значения.
Решение.
Для решения этой задачи построим таблицу ВыводаРезультатов в виде
В мастере функций Excel имеются функции МАКС(…) и МИН(…), позволяющие решить нашу задачу. Для этого необходимо в ячейки Е2 и Е3 ввести соответственно формулы:
Примечание. Если “протяжка” велась до другой строки, то в интервале ссылок указать соответствующий номер строки.
Задача 2.
Определение возрастания и/или убывания функции на заданном интервале.
Решение.
Для решения данной задачи в расчетной таблице необходимо построить столбец D, в котором знак будет указывать на возрастание или убывание функции в данной точке. Для этого в ячейку D11 введем формулу
И там, где знак положительный, функция возрастает, а там, где знак отрицательный, функция – убывает.
Далее построим вспомогательную таблицу, в которой определим количество признаков возрастания и убывания функции
F G 1 КОММЕНТАРИИ ЗНАЧЕНИЯ 2 Признак возрастания 3 Признак убывания
Для этого в ячейки g2 и g3 соответственно вводим формулы:
Таблицу ВыводаРезультатов дополним строкой
D E 1 комментарии значения 2 Макс f(X) =МАКС(с10:с50) 3 Мин f(X) =МИН(с10:с50) 4 Возрастание/Убывание
И для определения характера изменения заданной функции остается ввести в ячейку Е4 формулу:
Задача 3.
Определение четности данной.
Решение.
Для решения данной задачи необходимо знание аналитического представления исходной функции, так как признаком четности функции является равенство f(X) = f(-X).
Для решения данной задачи в расчетной таблице построим дополнительный столбец. В ячейку Е10 введем формулу
E10 => [=f(b10) – f(-b10)]
и “протянуть” ее до строки №50.
Таблицу ВыводаРезультатов дополним строкой
D E 1 комментарии значения 2 Макс f(X) =МАКС(с10:с50) 3 Мин f(X) =МИН(с10:с50) 4 Возрастание/Убывание [=ЕСЛИ(g2>0; ЕСЛИ(g3 5 четность
В ячейку Е4 таблицы ВыводаРезультатов вводим формулу.
Задача 4.
Определить является ли введенный период периодом заданной функции.
Решение.
Для решения данной задачи необходимо знание аналитического представления исходной функции, так как признаком периодичности функции является равенство f(Х) = f(X+).
Для данной задачи добавим в таблице ВводаИсходнойИнформации строку №6, в которой введем численное значение предполагаемого периода.
А В 1 КОММЕНТАРИИ ЗНАЧЕНИЯ 2 Xn 3 Xk 4 Shag 5 N 6 период
В ячейку В6 вводится число – предполагаемый период заданной функции.
Далее для решения данной задачи построим дополнительный столбец в расчетной таблице, для этого в ячейку F11 введем формулу
и “потягиваем” эту формулу до строки №50.
В таблице ВыводаРезультатов добавим строчку.
D E 1 комментарии значения 2 Макс f(X) =МАКС(с10:с50) 3 Мин f(X) =МИН(с10:с50) 4 четность =ЕСЛИ(g2>0; ЕСЛИ(g3 5 период
В ячейке Е5 необходимо ввести формулу
Если данное значение предполагаемого периода не подходит, то его можно изменить.
- значения первой производной данной функции,
- наличия и количество экстремумов.
Решение.
Для решения первой части задачи, то есть определения первой производной воспользуемся ее разностным определение.
Построим столбец. Для этого в ячейку G11 вводим формулу:
и “протягиваем ее до строки с №50.
В результате получаем разностные приближенные значения первой производной заданной функции в заданных точках.
Для решения второй части задачи, то есть определения наличия и количества экстремумов на заданной функции в таблицу ВыводаРезультатов дополнить строкой.
D E 1 комментарии значения 2 Макс f(X) =МАКС(с10:с50) 3 Мин f(X) =МИН(с10:с50) 4 четность =ЕСЛИ(g2>0; ЕСЛИ(g3 5 период [=ЕСЛИ(СУММ(f10:f50) ”не период”)] 6 Количество Экстремумов.
В ячейке Е6 необходимо ввести формулу
На рис.2 приведена построенная таблица решения задач.
Предложенные в данной работе задачи можно использовать на интегрированных уроках информатика-математика, на уроках информатики при изучении электронных таблиц, в частности, в теме: “Мастер функций”.
Читайте также: