Графики функций в программе Excel

Министерство  образования РФ

МАОУ СОШ  № 122 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Реферат на тему:

«Графики  функций в программе Excel» 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

                                                               

                                                                                       
 

                                                                                        Выполнила:

                                                                                        хххх

                                                                                        Проверила:

                                                                                        ххх 
 
 

Пермь 2010г. 

Введение

      Программа MS Excel, являясь лидером на рынке  программ обработки электронных таблиц, определяет тенденции развития в этой области. Вплоть до версии 4.0 программа Excel представляла собой фактический стандарт с точки зрения функциональных возможностей и удобства работы. Теперь на рынке появилась версия 5.0, которая содержит много улучшений и приятных неожиданностей.

      К значительным достижениям в новой  версии программы Excel можно отнести  появление трехмерных документов (блокнотов). Установление связей между файлами и таблицами значительно упростилось по сравнению с прежними версиями. Контекстные меню значительно расширены, а дополнительные программные инструменты облегчают решение сложных прикладных задач.

      Следует также упомянуть о различных помощниках (Ассистентах), которые помогают пользователю задавать функции и выдают рекомендации, если существует более простой метод решения текущей задачи. В программу Excel встроена удобная подсистема помощи, которая в любой момент готова выдать необходимую справку.

      Описанные до сих пор новшества касаются в основном комфорта в работе и  быстрого освоения программы. Одним из важнейших функциональных расширений программы, предназначенным для профессионалов, является встроенная в Excel Среда программирования Visual Basic (VBA) для решения прикладных задач. Благодаря VBA фирме Microsoft удалось не только расширить возможности языка макрокоманд Excel 4.0, но и ввести новый уровень прикладного программирования, поскольку VBA позволяет создавать полноценные прикладные пакеты, которые по своим функциям выходят далеко за рамки обработки электронных таблиц. Кроме этого, следует назвать следующие важные новшества программы Excel 5.0: менеджер файлов, который выдает подробную информацию о всех файлах; диалоговые окна-регистры; отдельная пиктограмма для форматирования; - появление механизма Drag & Plot, предназначенного для быстрой активизации диаграмм.  
 
 
 
 
 
 
 
 
 
 
 
 
 
 

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Глава 1

 

Как построить график?

 

     В Excel’е есть специальный тип диаграммы "график". При помощи него можно  построить график зависимости данных в столбце (например, В) от номера строчки. Независимо от того, какие подписи оси категорий мы зададим. (Даже если по оси Х будут подписаны значения из столбца А, график не будет выражать зависимость f1(x) в общем случае). Строится как самая обычная диаграмма. (См. примеры 1 и 2). Можно строить несколько графиков функций на одной диаграмме.

     Если  столбец А содержит арифметическую прогрессию, то можно считать, что  график построенной функции выражает зависимость f1(x).

     Такой метод построения графика функции  удобен, если дана протабулированная функция.

     На  шаге 2 в качестве диапазона надо указать соответствующие ячейки столбца В, а на вкладке “Ряд”  в строчке “Подписи по оси Х” указать соответствующие ячейки столбца А.

     Второй  метод построения графика зависимости  более универсален. Надо строить диаграмму типа “Точечная”. (Если выбрать третий вид, то она совсем будет похожа на график.)

     На  втором шаге в качестве диапазона  указываем столбец В. А на вкладке  ряд в качестве “Значения Х” указываем столбец А.

     Если  мы хотим построить несколько графиков (например, графики функций f1 и f2) на одной диаграмме, надо нажать кнопку “Добавить”, и для добавленного ряда указать в графе “Значения Y” - столбец С, в графе “Значения Х” - столбец А.

     Аналогично  с помощью диаграммы типа “Точечная” можно нарисовать график какой-нибудь нетривиальной зависимости. Например, f1(f2). См. пример №3. 

     Линия тренда. 

     Есть  несколько базовых функций (линейная, логарифм, экспонента, полиномы,…). Если у нас есть неизвестно какая зависимость, Excel может нам сказать, на какую базовую функцию она похожа больше всего.

     Для этого поступим так. Построим график этой зависимости (можно График, можно  Точечная), воспользуемся пунктом  Меню=>Диаграмма=>Добавить линию  тренда. Выберем тип тренда. Excel построит на диаграмме еще один график, показывающий, к какой зависимости ближе всего исследуемые данные.

     Например, если мы выберем тип тренда “Линейная”, то Excel построит линейную функцию, которая лучше всего описывает исследуемую зависимость. На диаграмме это будет выглядеть так: это будет такая прямая линия, что график построенной функции отклоняется от нее наименьшим образом. См. пример 4.

     Примечание: Excel использует метод наименьших квадратов. Т.е. Excel строит тренд с условием, что сумма квадратов расстояний от реальных точек до линии тренда минимальна для всех линий данного типа.

     Как понять, что за тренд построен? Очень  просто. Надо вывести его уравнение (“Формат линии тренда”, вкладка “Параметры”, соответствующая галка). И вы узнаете приблизительное уравнение зависимости между вашими данными.

     Как понять, какой тип тренда надо использовать? На той же вкладке “параметры”  окна “формат линии тренда” включаем галку “поместить на диаграмму величину достоверности аппроксимации (R^2)”. После этого в подписи к тренду появится эта самая величина достоверности. Чем она больше, тем лучше построенный тренд. См. пример 5. 
 

Пример  №1. 

Построение графика  функции f1.

Меню=>Вставка=>Диаграмма…

Шаг 1. Тип - График, Вид - первый. Далее.

Шаг 2. Диапазон: =Пример!$B$2:$B$22, в столбцах.

На вкладке  “Ряд”: Имя: “=Пример!$B$1”,

Подписи по оси  Х“=Пример!$A$2:$A$22”.

Шаг 3. Легенду  убрать. Линии сетки основные в  двух направлениях, Заголовки убрать. Готово. 

Растягиваем до необходимого размера. Теперь будем  подправлять то, что нам не понравилось.

Формат оси  категорий. Вкладка “Выравнивание” 90° .

Формат оси  значений. Вкладка “Шкала”. Минимальное  значение 1.

Формат области  построения. Заливка белым.

Формат рядов  данных. Вкладка “Вид”. Толщина  линии - третья, цвет - красный.  

Получилось: 

 
 
 

Пример  №2. 

Построим на одной диаграмме два графика  функций сразу. И f1, и f2.

Меню=>Вставка=>Диаграмма…

Шаг 1. Тип - График, Вид - первый. Далее.

Шаг 2. Диапазон: =Пример!$B$2:$C$22, в столбцах.

На вкладке  “Ряд”: Имя: “=Пример!$B$1” (для Ряда 1), “=Пример!$C$1” (для Ряда 2). Подписи  по оси Х“=Пример!$A$2:$A$22”.

Шаг 3. Легенда - внизу. Линии сетки основные в  двух направлениях, Заголовки убрать. Готово. 

Растягиваем до необходимого размера. Теперь будем подправлять то, что нам не понравилось.

Формат оси  категорий. Вкладка “Выравнивание” 90° .

Формат области  построения. Заливка белым.

Формат рядов  данных. Вкладка “Вид”. Толщина  линии - третья. (Для двух графиков). 
 

Получилось:

 
 
 

Пример  №3. 

Поиск зависимости  между f1 и f2. 

Чтобы построить  график зависимости f2(f1), надо использовать тип диаграммы “Точечная”.

Меню=>Вставка=>Диаграмма…

Шаг 1. Тип - Точечная, Вид - третий (со сглаженными линиями, без маркеров). Далее.

Шаг 2. Диапазон: =Пример!$B$2:$C$22, в столбцах.

На вкладке  “Ряд”: Имя: “f2(f1)”.

Шаг 3. Легенду  убрать. Линии сетки убрать. Заголовки  убрать. Готово. 

Поправляем:

Формат области  построения. Заливка белым. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Получилось:

 
 
 

Пример №4. 

Поиск зависимости  с помощью линии тренда. (см.лист “Тренд”) 

Найдем зависимость  между x и f1. Для этого можно использовать как тип диаграммы График, так  и Точечная, т.к. значения в столбце A образуют арифметическую прогрессию.

Меню=>Вставка=>Диаграмма…

Шаг 1. Тип - Точечная, Вид - первый. Далее.

Шаг 2. Диапазон: =Тренд!$A$2:$B$22, в столбцах.

На вкладке  “Ряд”: Имя: “f1(x)”.

Шаг 3. Легенда  внизу. Линии сетки основные в  двух направлениях. Заголовки убрать. Готово. 

Поправляем.

Формат области  построения. Заливка белым.

Формат оси  категорий. Минимальное значение 1, максимальное 2.

Формат оси  значений. Минимальное значение 10. 

Добавляем линию  тренда. Похоже, что необходим линейный тренд.

Меню=>Диаграмма=>Добавить линию тренда.

Тип линейный.

На вкладке  “Параметры”. Показывать уравнение на диаграмме. 

Поправляем:

Выносим уравнение  за область диаграммы. 
 
 
 
 
 
 
 

Получилось: 

 

Точки лежат  достаточно близко к линии тренда, значит тип тренда мы выбрали хорошо. 

Пример  №5. 

Поиск зависимости f1(f2) с помощью линии тренда.

Меню=>Вставка=>Диаграмма…

Шаг 1. Тип - Точечная, Вид - первый. Далее.

Шаг 2. Диапазон: =Тренд!$B$2:$B$22, в столбцах.

На вкладке  “Ряд”: Имя: “f1(f2)”. Значения X “=Тренд!$C$2:$C$22”.

Шаг 3. Легенда  внизу. Линии сетки основные в  двух направлениях. Заголовки убрать. Готово. 

Поправляем:

Формат области  построения. Заливка белым.

Формат оси  категорий. Минимальное значение 2.

Формат оси  значений. Минимальное значение 10. 

Добавляем линию  тренда. По точкам не сразу понятно, какой тренд добавлять. Добавим линейный, затем логарифмический и посмотрим, при каком величина R^2 ближе к 1. Такой и лучше.

Меню=>Диаграмма=>Добавить линию тренда.

Тип линейный.

На вкладке  “Параметры”. Показывать уравнение  на диаграмме. Поместить на диаграмму R^2. 

Получили R2=0,9705. Неплохо.

Формат линии  тренда. Тип логарифмическая. 

Поличили R2=0,9867. Лучше. Такой тренд и оставим. 

Поправляем:

Выносим уравнение  за область диаграммы.

Формат линии  тренда. На вкладке параметры убираем  значок “поместить на диаграмму R^2”. 

Получилось: