Построение графика зависимости в Microsoft Excel

Содержание

Построение графиков функций и плоскостей в Microsoft Excel

Построение графика зависимости в Microsoft Excel

Microsoft Excel – ведущая программа обработки электронных таблиц.

Электронные таблицы позволяют производить обработку чисел и текста, задавать формулы и функции для автоматического выполнения, прогнозировать бюджет на основе сценария, представлять данные в виде диаграмм, публиковать рабочие листы в Интернете и т.д.

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

В своей работе я использовал программу Microsoft Excel для создания графиков функции и показал применение их к решению задач. По теме «Построение графиков функции в Microsoft Excel» существует недостаточно научной литературы.

Так данную тему рассматривают М. Додж и К. Стинсон в книге «Эффективная работа с Microsoft Excel». Не менее интересна рассказывается в книге Л.В. Рудиковой «Microsoft Excel для студента». При работе над рефератом я использовал также книгу Р.Б.

Райхмиста «Графики функций».

Диаграммы Excel дают возможность графического представления различных числовых данных. Для построения диаграмм следует предварительно подготовить диапазон необходимых данных, а затем воспользоваться командой Вставка/диаграмма или соответствующей кнопкой «Мастера диаграмм» на панели инструментов Стандартная.

В Excel можно строить два типа диаграмм: внедренные и диаграммы на отдельных листах. Внедренные диаграммы создаются на рабочем листе рядом с таблицами, данными и текстом и, используются при создании отчетов. Диаграммы на отдельном листе удобны для подготовки слайдов или для вывода на печать.

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

Форматирование достигается использованием соответствующих команд панели инструментов Диаграммы или с помощью контекстного меню соответствующего объекта диаграммы («достаточно щелкнуть правой кнопкой мыши на нужном объекте и из контекстного меню выбрать команду Формат»).

Два типа графиков функций в excel

Будем считать, что функции, графики которых нам надо строить всегда расположенных в столбцах. Причем в столбце а пусть находятся значения переменной х, а в столбцах В, С…значения функций f1.f2,…соответственно.

В Excel’е есть специальный тип диаграммы “график”. При помощи него можно построить график зависимости данных в столбце (например, В) от номера строчки. Независимо от того, какие подписи оси категорий мы зададим.

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

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

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

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

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

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

Операции, которые можно проводить с диаграммами

С диаграммами можно проводить следующие операции:

  • Добавлять и удалять ряды данных с помощью мастера диаграмм либо из контекстного меню диаграммы командой Исходные данные. Возможно также использование клавиши , перетаскивание мышью данных на построенную диаграмму.
  • Изменять «редактировать» данные в диаграмме и на рабочем листе – с помощью средства Подбор параметра (если данные, на которых построена диаграмма, выражены через формулу).
  • Переставлять ряды данных на диаграмме (это касается, в основном диаграмм гистограммного типа).
  • Вставлять текст в любом месте диаграммы – выделить диаграмму, а затем в строке формул ввести необходимый текст, который можно буксировать по всей диаграмме и форматировать как надпись.
  • Редактировать, форматировать и добавлять различные элементы диаграмм – с помощью контекстного меню для необходимого объекта диаграммы.
  • Изменять пространственную ориентацию трехмерных диаграмм – выделить диаграмму и воспользоваться командой меню Диаграмма/Объёмный вид, можно также щелкнуть мышью на конце любой оси координат – появится «черные крестики», а затем удерживая мышь на одном из них, изменять расположение трехмерной диаграммы в пространстве.
  • Добавлять различные графические объекты с помощью кнопок панели инструментов Рисование, либо посредством команд меню Вставка/Рисунок.
  • Настраивать оси и выбирать шкалу можно с помощью контекстного меню для данной оси.
  • Строить составные диаграммы (различные типы графиков в одной системе координат) используя нестандартные типы диаграмм.
  • Изменять типы диаграмм, выбрав команду Тип диаграммы из контекстного меню.
  • Создавать рисованные диаграммы (вместо цветовой заливки – рисунки). В этом случае необходимо выбрать некоторый ряд данных и использовать для него команду контекстного меню Формат рядов данных.
  • Связывать текст на диаграмме с ячейками рабочего листа.
  • Создавать диаграммы на основе структурированных данных.
  • Применять диаграммы для анализа данных, т.е. строить личные линии тренда и делать прогнозы.
  • Построение графиков функций и поверхностей.

    Этапы построения графиков функций

    Построение графиков является частным случаем построения диаграмм. Графики выбирают в тех случаях, когда хотят отобразить изменение данных с течением времени. Графики позволяют «анализировать закономерность изменения величин».Работа по построению графиков функций включает в себя 4 этапа:

    • Подготовка диапазона области определения функции с помощью маркера автозаполнения.
    • Расчёт значения функции или функций на данном диапазоне, используя формулы и функции рабочего листа Excel и маркер автозаполнения.
    • Выделение диапазона области определения и области значения функции или функций и использование мастера построения диаграмм. Для построения графиков лучше использовать типы диаграмм График и Точечная.
    • Форматирование полученного графика или графиков.

Источник: //www.microarticles.ru/article/postroenie-grafikov-fynktsij-i-ploskostej-v-Microsoft-Excel.html

Как построить график зависимости в Эксель

Построение графика зависимости в Microsoft Excel

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

Как постороить одноосевой график

Для каждого случая построения графиков существуют индивидуальные условия, но практически всегда должна быть независимая переменная функция. На практике такую обозначают символами «х» или «у».

  1. Предположим, у нас есть таблица с двумя известными составляющими. Построим график зависимости на ее основе. Выделяем весь диапазон ячеек, которые охватывает наша таблица, затем переходим во вкладку «Вставка». Находим блок с диаграммами и нажимаем на инструмент «Вставить график». Для начала подойдёт самый простой.
  1. Автоматически система произвела построение графика с ломаными линиями, однако нам понадобится лишь одна, поэтому ненужную ось (с количеством часов) можно удалить. Для этого сделаем выделяем её мышью, а затем нажмем правой кнопкой вызовем меню редактирования. Нажимаем «Удалить».
  1. На данном этапе можно считать первый способ построения графика завершенным. Мы удалили ось временного промежутка и получили значение, которое нас интересует: «Зависимость выполненных задач за заданное время».

Для более сложных систем зависимости можно использовать графическую диаграмму с маркерами. Она поможет более точно определить сегмент данных и выстроить аналитическую картину таблицы.

Как постороить осложненный график зависимости

При наличии двух или трех неизменных данных в таблице, график зависимости выполняет сложную функцию. Рассмотрим способ построения графика зависимости для нескольких значений:

  1. Выделим всю область нашей таблицы и снова запустим первый график. Перед вами появился объект, в котором одна линия, отвечающая за год, отображается некорректно. Удаляем ее, как в предыдущем способе.
  1. График отобразился иначе, однако одна из линий все равно неправильно отвечает за свои функции. Для исправления этой ошибки выбираем область построения (активируем ее так, чтобы видны были элементы перемещения). Затем сделаем клик правой кнопкой мыши и нажмем на пункт «Выбрать данные».
  1. Открывается дополнительное окно настроек. В нем находим кнопку «Изменить», после ее нажатия автоматически открывается окно поменьше.
  1. Теперь выделяем диапазон ячеек, где отображаются года. Только не вносите в адрес заголовок этого столбца. Затем нажмите кнопку «ОК.
  1. Снова нажмите «ОК», чтобы сохранить изменения, внесенные в график зависимости. Обратите внимание, что адреса обрабатываемых ячеек автоматически перенеслись в поле диапазона.

После произведенных манипуляций все данные на листе получили четкую позицию, соответственно, отображаются корректно. Можно заметить, никаких сложностей с оформлением графиков зависимости нет.

Если на графике отображается несоответствие величин, необходимо построить вспомогательную ось. Для этого нажмите на область изменения оси, а затем сделайте клик правой кнопкой мыши.

Выберите «Формат ряда данных». В окне мастера справа отметьте флажок «По вспомогательной оси». Изменения будут активированы.

Алгебраическая функция и график зависимости

Этот способ построения графика зависимости используют студенты и те, кто работают в области статистики. Прежде чем выстроить график зависимости, необходимо посчитать значение «х» и прописать его в таблице с определенным шагом.

  1. Для начала построим таблицу и впишем в нее единственное значение. Вставляем в первую ячейку «х» число -15. Затем заполним оставшиеся данные вниз через три шага. Для этого воспользуемся автоматическим инструментом. Выделяем весь столбец «х», затем во вкладке «» нажимаем на пункт «Заполнить». Высветится список, в котором выбираем «Прогрессия».
  1. В следующем открывшемся окне редактируем все так, как указано на фото. Обязательно проставляем величину шага «3», затем отмечаем предельное значение, в нашем случае будет число «12». Нажимаем кнопку «ОК».
  1. Выполняем преобразование обычной формулы в формулу Excel. Для этого вставим нашу формулу в первую ячейку столбца «у», где вместо значения «х» пропишем адрес ячейки, в котором оно прописано. Затем в конце формулы нажимаем кнопку «Enter».
  1. Как вы видите, все значения автоматически посчитались для каждой отдельно взятой ячейки. Если в вашей версии Excel значения не заполнились, необходимо использовать маркер автозаполнения. Наведите на нижний угол до появления крестика. Нажмите и протяните вниз до последней ячейки.
  1. Как в предыдущих способах, выстраиваем графики. В данном случае, необходимо выбрать маркированный объект. Обязательно удаляем лишнюю ось.

Если вам малопонятны функции Microsoft Excel, вы можете вручную прописать каждое значение выражения, однако автоматизированный процесс поможет сберечь ваше время.

Подробнее о функциях Excel и их применении читайте в инструкции Справочника.

Источник: //studwork.org/spravochnik/informatika/excel/alternativnye-sposoby-postroeniya-grafika-zavisimosti-v-excel

Построение диаграмм с несколькими рядами данных в MS EXCEL

Построение графика зависимости в Microsoft Excel

Рассмотрим построение в MS EXCEL 2010 диаграмм с несколькими рядами данных, а также использование вспомогательных осей и совмещение на одной диаграмме диаграмм различных типов.

Построение диаграмм с одним рядом данных рассмотрено в статье Основы построения диаграмм в MS EXCEL. Начинающим пользователям также целесообразно перед прочтением ознакомиться со статьей Основные типы диаграмм.

ГИСТОГРАММА

Построим Гистограмму с группировкой на основе таблицы с двумя числовыми столбцами, близких по значениям.

Выделите любую ячейку таблицы (см. файл примера), на вкладке Вставка, в группе Диаграммы нажмите кнопку Гистограмма, в выпавшем меню выберите Гистограмма с группировкой.

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

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

Для этого достаточно выбрать для одного из рядов построение на вспомогательной оси (дважды кликнув на один из столбцов), а затем настроить ширину столбцов (боковой зазор), чтобы отображались оба ряда.

//www.youtube.com/watch?v=1VDfnG4Q3e0

Если не настроить ширину столбцов, то эту диаграмму можно спутать с диаграммой Гистограмма с пополнением (столбцы, относящие к одной категории «ставятся» друг на друга).

Другой возможностью отображения на диаграмме двух рядов со значительно различающимися значениями, является масштабирование самих значений в таблице-источнике.

Горизонтальную вспомогательную ось можно расположить даже вверху. При этом столбцы разных рядов будут оригинально пересекаться.

Теперь изменим подписи по горизонтальной оси (категории).

В окне Выбор источника данных видно, что для обоих рядов подписи горизонтальной оси одинаковы, т.к. категории одинаковы для обоих рядов (столбец Месяц). 

Кликните сначала по ряду Продажи, затем по Прибыль – подписи по горизонтальной оси будут одинаковы.

При желании это также можно изменить. В окне Выбор источника данных выделите ряд Прибыль, нажмите кнопку Изменить расположенную справа, удалите ссылку на ячейки. Теперь у ряда Прибыль вместо названия категорий будут просто порядковые числа 1, 2, 3,… Однако, они не будут отображаться на диаграмме, т.к. отображается пока только Основная горизонтальная ось.

Теперь в меню Оси (вкладка Макет, группа Оси) выберите Вспомогательная горизонтальная ось и установите ее Слева направо. В окне формата Вспомогательной вертикальной оси измените точку пересечения оси (установите Автовыбор). Получим вот такую диаграмму.

Хотя техническая возможность отображения 2-х различных категорий существует, конечно, таких диаграмм нужно избегать, т.к. их сложно воспринимать. Категории должны быть одинаковыми для всех рядов на диаграмме. Естественно, такой трюк сработает только для двух групп рядов, т.к. имеется всего 2 типа оси: основная и вспомогательная.

ГРАФИК

Диаграмма График во многом аналогична Гистограмме с группировкой: к ней применимы те же идеи по отображению двух рядов со значительно отличающимися значениями, что и к Гистограмме.

После размещения рядов на разных осях получим вот такую диаграмму (линии вертикальных осей выделены цветами, соответствующими цветам рядов).

Примечание. Если провести горизонтальные линии сетки к вспомогательной оси, то они, естественно, могут не совпасть с линиями к основной оси, т.к. масштабы осей (величины основных делений вертикальных осей) могут не совпадать. Это может «перегрузить» диаграмму.

Вообще, к использованию вспомогательных вертикальных осей, а тем более вспомогательных горизонтальных осей для Гистограммы и Графика нужно подходить обдуманно: ведь диаграмма должна «читаться» – быть понятной без дополнительных комментариев.

ТОЧЕЧНАЯ

Визуально Точечная диаграмма похожа на диаграмму типа График (если конечно у Точечной диаграммы точки соединены линиями).

Различие состоит в том, что для построения Графика в качестве координаты Х всегда используется последовательность 1, 2, 3, … (с подписями – категориями), а для Точечной значения по горизонтальной оси м.б. любыми числами (см. статью График vs Точечная).

Примечание. Если для построения Точечной диаграммы не указана ссылка на значения Х (или ссылка указывает на текстовые значения), то в качестве координат по Х будет использована та же последовательность 1, 2, 3, …, что и для Графика.

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

Теперь о совмещении разных типов диаграмм с Точечной. Если для Точечной диаграммы не используется координата Х, то на диаграмме она выглядит как График.

Подписи по горизонтальной оси берутся от Графика. В окне Выбор источника данных видно, что для ряда отображаемого Точечной диаграммой подписи горизонтальной оси одинаковы изменить/ удалить нельзя.

Кроме того, График может быть только на основной оси и поменять это нельзя.

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

Это связано с тем, что при совмещении с Точечной диаграммой (на одной оси и когда у Точечной указаны значения по Х), диаграмма типа График становится как бы главной:

  • на горизонтальной оси отображаются подписи только для Графика;
  • вертикальная сетка не отображается для отрицательных значений Х (т.к. График строится только для Х=1, 2, 3, …);
  • у Графика невозможно изменить Ось с Основной на Вспомогательную (у Точечной можно).

Если Точечную построить на вспомогательной оси, то диаграмма изменится.

Подписи для Точечной (значения по Х) теперь отображаются сверху.

Совет. Диаграмма типа Точечная используется для построения окружностей, эллипсов и других замкнутых фигур на плоскости.

Теперь рассмотрим построение 2-х рядов данных, которые используют диаграмму Точечная.

Сначала построим 2 эллипса с различными координатами центра и размерами полуосей без использования вспомогательных осей.

Примечание. Фактически на диаграмме 4 ряда данных: точка центра представляет отдельный ряд.

Дважды кликнем по бордовому эллипсу и выберем построение ряда на вспомогательной оси (сделаем то же и центра эллипса).

Теперь координаты Y для бордового эллипса откладываются по правой вертикальной оси (можно ее для наглядности выделить также бордовым цветом).

Добавим Вспомогательную горизонтальную ось (в меню Оси (вкладка Макет, группа Оси) выберите Вспомогательная горизонтальная ось и установите ее По умолчанию).

Наверное, для диаграммы типа Точечная использование вспомогательных осей наиболее оправдано – это позволяет отображать на одной диаграмме различные по масштабу кривые.

КРУГОВАЯ

В статье Основные типы диаграмм показано, что разместить несколько рядов в круговой диаграмме технически можно, но, как правило, не целесообразно.

Совмещение разных типов диаграмм

Про совмещение Графика и Точечной диаграммы рассказано выше (см. раздел Точечная).

Совмещением Линейчатой и Гистограммы не стоит заниматься, т.к. выглядит это достаточно странно, почти также странно, как и совмещение Гистограммы с Круговой (см. также статью Основные типы диаграмм, раздел Круговая).

Совет. Также можно посмотреть статью Совмещаем разные типы диаграмм.

Оригинальностью совмещения могут также похвастаться Гистограмма и Нормированная линейчатая с накоплением.

Наверное, единственными типами диаграмм, которые стоит совмещать, являются График и Гистограмма (категории должны быть одинаковыми для обоих рядов).

Для такой диаграммы постройте сначала Гистограмму с группировкой с двумя рядами (см. раздел Гистограмма в начале этой статьи). Затем выделите нужный ряд и нажмите кнопку Изменить тип диаграммы (вкладка Конструктор). Выбрав График или График с Маркерами нажмите ОК.

Если масштабы значений у рядов существенно отличаются, то можно настроить вспомогательную вертикальную ось (см. раздел Гистограмма).

Источник: //excel2.ru/articles/postroenie-diagramm-s-neskolkimi-ryadami-dannyh-v-ms-excel

Как сделать зависимость в excel?

Построение графика зависимости в Microsoft Excel

Одной из типичных математических задач является построение графика зависимости. В нем отображается зависимость функции от изменения аргумента.

На бумаге выполнить данную процедуру не всегда просто. Но инструменты Excel, если в должной мере овладеть ими, позволяют выполнить данную задачу точно и относительно быстро.

Давайте выясним, как это можно сделать, используя различные исходные данные.

Процедура создания графика

Зависимость функции от аргумента является типичной алгебраической зависимостью. Чаще всего аргумент и значение функции принято отображать символами: соответственно «x» и «y».

Нередко нужно произвести графическое отображение зависимости аргумента и функции, которые записаны в таблицу, или представлены в составе формулы.

Давайте разберем конкретные примеры построения подобного графика (диаграммы) при различных заданных условиях.

Способ 1: создание графика зависимости на основе данных таблицы

Прежде всего, разберем, как создать график зависимости на основе данных, предварительно внесенных в табличный массив. Используем таблицу зависимости пройденного пути (y) от времени (x).

  1. Выделяем таблицу и переходим во вкладку «Вставка». Кликаем по кнопке «График», которая имеет локализацию в группе «Диаграммы» на ленте. Открывается выбор различных типов графиков. Для наших целей выбираем самый простой. Он располагается первым в перечне. Клацаем по нему.
  2. Программа производит построение диаграммы. Но, как видим, на области построения отображается две линии, в то время, как нам нужна только одна: отображающая зависимость пути от времени. Поэтому выделяем кликом левой кнопки мыши синюю линию («Время»), так как она не соответствует поставленной задаче, и щелкаем по клавише Delete.
  3. Выделенная линия будет удалена.

Собственно на этом построение простейшего графика зависимости можно считать завершенным. При желании также можно отредактировать наименования диаграммы, её осей, удалить легенду и произвести некоторые другие изменения. Об этом подробнее рассказывается в отдельном уроке.

Урок: Как сделать график в Экселе

Способ 2: создание графика зависимости с несколькими линиями

Более сложный вариант построения графика зависимости представляет собой случай, когда одному аргументу соответствуют сразу две функции. В этом случае потребуется построить две линии. Для примера возьмем таблицу, в которой по годам расписана общая выручка предприятия и его чистая прибыль.

  1. Выделяем всю таблицу вместе с шапкой.
  2. Как и в предыдущем случае, жмем на кнопку «График» в разделе диаграмм. Опять выбираем самый первый вариант, представленный в открывшемся списке.
  3. Программа производит графическое построение согласно полученным данным. Но, как видим, в данном случае у нас имеется не только лишняя третья линия, но ещё и обозначения на горизонтальной оси координат не соответствуют тем, которые требуются, а именно порядку годов.

    Сразу удалим лишнюю линию. Ею является единственная прямая на данной диаграмме — «Год». Как и в предыдущем способе, выделяем линию кликом по ней мышкой и жмем на кнопку Delete.

  4. Линия удалена и вместе с ней, как вы можете заметить, преобразовались значения на вертикальной панели координат. Они стали более точными. Но проблема с неправильным отображением горизонтальной оси координат все-таки остается. Для решения данной проблемы кликаем по области построения правой кнопкой мыши. В меню следует остановить выбор на позиции «Выбрать данные…».
  5. Открывается окошко выбора источника. В блоке «Подписи горизонтальной оси» кликаем по кнопке «Изменить».
  6. Открывается окошко ещё меньше предыдущего. В нём нужно указать координаты в таблице тех значений, которые должны отображаться на оси. С этой целью устанавливаем курсор в единственное поле данного окна. Затем зажимаем левую кнопку мыши и выделяем всё содержимое столбца «Год», кроме его наименования. Адрес тотчас отразится в поле, жмем «OK».
  7. Вернувшись в окно выбора источника данных, тоже щелкаем «OK».
  8. После этого оба графика, размещенные на листе, отображаются корректно.

Способ 3: построение графика при использовании различных единиц измерения

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

Имеем таблицу, в которой представлены данные по объему продаж определенного товара в тоннах и по выручке от его реализации в тысячах рублей.

  1. Как и в предыдущих случаях выделяем все данные табличного массива вместе с шапкой.
  2. Клацаем по кнопке «График». Снова выбираем первый вариант построения из перечня.
  3. Набор графических элементов сформирован на области построения. Тем же способом, который был описан в предыдущих вариантах, убираем лишнюю линию «Год».
  4. Как и в предыдущем способе, нам следует на горизонтальной панели координат отобразить года. Кликаем по области построения и в списке действий выбираем вариант «Выбрать данные…».
  5. В новом окне совершаем щелчок по кнопке «Изменить» в блоке «Подписи» горизонтальной оси.
  6. В следующем окне, производя те же действия, которые были подробно описаны в предыдущем способе, вносим координаты столбца «Год» в область «Диапазон подписей оси». Щелкаем по «OK».
  7. При возврате в предыдущее окно также выполняем щелчок по кнопке «OK».
  8. Теперь нам следует решить проблему, с которой ещё не встречались в предыдущих случаях построения, а именно, проблему несоответствия единиц величин. Ведь, согласитесь, не могут располагаться на одной панели координат деления, которые одновременно обозначают и денежную сумму (тыс. рублей) и массу (тонны). Для решения данной проблемы нам потребуется произвести построение дополнительной вертикальной оси координат.

    В нашем случае для обозначения выручки оставим ту вертикальную ось, которая уже имеется, а для линии «Объём продаж» создадим вспомогательную. Клацаем по данной линии правой кнопкой мышки и выбираем из перечня вариант «Формат ряда данных…».

  9. Запускается окно формата ряда данных. Нам нужно переместиться в раздел «Параметры ряда», если оно было открыто в другом разделе. В правой части окна расположен блок «Построить ряд». Требуется установить переключатель в позицию «По вспомогательной оси». Клацаем по наименованию «Закрыть».
  10. После этого вспомогательная вертикальная ось будет построена, а линия «Объём продаж» переориентируется на её координаты. Таким образом, работа над поставленной задачей успешно окончена.

Способ 4: создание графика зависимости на основе алгебраической функции

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

У нас имеется следующая функция: y=3×2+2x-15. На её основе следует построить график зависимости значений y от x.

  1. Прежде, чем приступить к построению диаграммы, нам нужно будет составить таблицу на основе указанной функции. Значения аргумента (x) в нашей таблице будут указаны в диапазоне от -15 до +30 с шагом 3. Чтобы ускорить процедуру введения данных, прибегнем к использованию инструмента автозаполнения «Прогрессия».

    Указываем в первой ячейке столбца «X» значение «-15» и выделяем её. Во вкладке «» клацаем по кнопке «Заполнить», размещенной в блоке «Редактирование». В списке выбираем вариант «Прогрессия…».

  2. Выполняется активация окна «Прогрессия». В блоке «Расположение» отмечаем наименование «По столбцам», так как нам необходимо заполнить именно столбец. В группе «Тип» оставляем значение «Арифметическая», которое установлено по умолчанию. В области «Шаг» следует установить значение «3». В области «Предельное значение» ставим цифру «30». Выполняем щелчок по «OK».
  3. После выполнения данного алгоритма действий весь столбец «X» будет заполнен значениями в соответствии с заданной схемой.
  4. Теперь нам нужно задать значения Y, которые бы соответствовали определенным значениям X. Итак, напомним, что мы имеем формулу y=3×2+2x-15. Нужно её преобразовать в формулу Excel, в которой значения X будут заменены ссылками на ячейки таблицы, содержащие соответствующие аргументы.

    Выделяем первую ячейку в столбце «Y». Учитывая, что в нашем случае адрес первого аргумента X представлен координатами A2, то вместо представленной выше формулы получаем такое выражение:

    =3*(A22)+2*A2-15

    Записываем это выражение в первую ячейку столбца «Y». Для получения результата расчета щелкаем по клавише Enter.

  5. Результат функции для первого аргумента формулы рассчитан. Но нам нужно рассчитать её значения и для других аргументов таблицы. Вводить формулу для каждого значения Y очень долгое и утомительное занятие. Намного быстрее и проще её скопировать. Эту задачу можно решить с помощью маркера заполнения и благодаря такому свойству ссылок в Excel, как их относительность. При копировании формулы на другие диапазоны Y значения X в формуле будут автоматически изменяться относительно своих первичных координат.

    Наводим курсор на нижний правый край элемента, в который ранее была записана формула. При этом с курсором должно произойти преображение. Он станет черным крестиком, который носит наименование маркера заполнения. Зажимаем левую кнопку мыши и тащим этот маркер до нижних границ таблицы в столбце «Y».

  6. Вышеуказанное действие привело к тому, что столбец «Y» был полностью заполнен результатами расчета формулы y=3×2+2x-15.
  7. Теперь настало время для построения непосредственно самой диаграммы. Выделяем все табличные данные. Снова во вкладке «Вставка» жмем на кнопку «График» группы «Диаграммы». В этом случае давайте из перечня вариантов выберем «График с маркерами».
  8. Диаграмма с маркерами отобразится на области построения. Но, как и в предшествующих случаях, нам потребуется произвести некоторые изменения для того, чтобы она приобрела корректный вид.
  9. Прежде всего, удалим линию «X», которая разместилась горизонтально на отметке 0 координат. Выделяем данный объект и жмем на кнопку Delete.
  10. Легенда нам тоже не нужна, так как мы имеем только одну линию («Y»). Поэтому выделяем легенду и снова жмем по клавише Delete.
  11. Теперь нам нужно значения в горизонтальной панели координат заменить на те, которые соответствуют столбцу «X» в таблице.

    Кликом правой кнопки мыши выделяем линию диаграммы. В меню перемещаемся по значению «Выбрать данные…».

  12. В активировавшемся окне выбора источника клацаем по уже хорошо знакомой нам кнопке «Изменить», располагающейся в блоке «Подписи горизонтальной оси».
  13. Запускается окошко «Подписи оси». В области «Диапазон подписей оси» указываем координаты массива с данными столбца «X». Ставим курсор в полость поля, а затем, произведя необходимый зажим левой кнопки мыши, выделяем все значения соответствующего столбца таблицы, исключая лишь его наименование. Как только координаты отобразятся в поле, клацаем по наименованию «OK».
  14. Вернувшись к окну выбора источника данных, клацаем по кнопке «OK» в нём, как до этого сделали в предыдущем окне.
  15. После этого программа произведет редактирование ранее построенной диаграммы согласно тем изменениям, которые были произведены в настройках. График зависимости на основе алгебраической функции можно считать окончательно готовым.

Урок: Как сделать автозаполнение в Майкрософт Эксель

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

Конкретный вариант построения зависит от того, на основе чего строится диаграмма: табличные значения или функция. Во втором случае перед построением диаграммы придется ещё создавать таблицу с аргументами и значениями функций.

Кроме того, график может быть построен, как на основе одной функции, так и нескольких.

Мы рады, что смогли помочь Вам в решении проблемы.

Источник: //word-office.ru/kak-sdelat-zavisimost-v-excel.html

Построение графика в excel и использование функции линейн

Построение графика зависимости в Microsoft Excel

Рассмотрим результаты эксперимента, приведенные в исследованном выше примере.

Исследуем характер зависимости в три этапа:

— Построим график зависимости.

— Построим линию тренда (в данном случае это прямая ).

— Получим числовые характеристики коэффициентов этого уравнения.

Решение

Построение графика зависимости.

1. Выделим интервал А1:В25.

2. Вызовем Мастер диаграмм, нажав соответствующую кнопку на панели инструментов.

3. Используя мышь, выделим область для встроенной диаграммы.

4. На 1 шаге в диалоговом окне Мастера диаграмм интервал А1:В25 должен быть указан, если это не так укажите. Нажмите Шаг.

5. На 2 шаге выберите тип диаграммы XY-точечная.Нажмите Шаг.

6. На 3 шаге выберите первый тип автоформата. Нажмите Шаг

7. На 4 шаге укажите следующие параметры:

8. Отвести 1 столбец для данных по оси Х; отвести 1 строку для текста легенды. Нажмите Шаг.

9. На 5 шаге в окне «Название диаграммы: » введите заголовок «Линейная аппроксимация»; в окне «Категорий [X]:» введите «x»; в окне «Значений [Y]:» введите «y». Нажмите Закончить.

Построение линии тренда

Для построения линии тренда выполним следующую последовательность действий:

Дважды щелкнем по диаграмме. Диаграмма активизируется.

Щелкните по графику непосредственно в одну из изображенных точек. Сам график активизируется, его окраска изменится.

Вставляем линию тренда, воспользуемся меню Вставка – Линия тренда.

Появиться диалоговое окно «Линия тренда» выберем на вкладке «Тип» (Рис.2) линейный тип и перейдем к вкладке «Параметры».

На вкладке «Параметры» (Рис.3) потребуем показывать уравнение тренда на диаграмме и показывать значение , поставив их в соответствующие клетки. Нажмем кнопку ОК.

Рис.2

Рис.3

На диаграмме появится линия тренда с соответствующим уравнением. Также изменится легенда. При желании текстовое поле с уравнением и значением , а также название координат x и y, можно оттащить в более удобное место, как это сделано на Рис 4.

Рис.4

Для построения квадратичной аппроксимации на четвертом шаге в диалоговом окне «Линия тренда» выберем на вкладке «Тип» (Рис.2) полиномиальный тип степень 2. Результат представлен на рис.5.

Рис.5

Для построения экспоненциальной аппроксимации на четвертом шаге в диалоговом окне «Линия тренда» выберем на вкладке «Тип» (Рис.2) экспоненциальный тип. Результат представлен на рис.6.

Рис.6

Сравнивая результаты, полученные при помощи функции ЛИНЕЙН видим что они полностью совпадают с вычислениями, проведенными выше. Это указывает на то, что вычисления верны.

Примечание: Полученное при построении линии тренда значение коэффициента детерминированности для экспоненциальной зависимости не совпадает с истинным значением (это значение было сосчитано вручную выше) поскольку при вычислении коэффициента детерминированности с помощью функции ЛИНЕЙН используются не истинные значения , а преобразованные значения с дальнейшей линеаризацией.

Получение числовых характеристик зависимости

Для построения числовых характеристик необходимо создать табличную формулу, которая будет занимать 5 строк и 2 столбца. Этот интервал может располагаться в произвольном месте на рабочем листе. В этот интервал требуется ввести функцию ЛИНЕЙН. Для этого выполняем следующую последовательность действий:

— Выделите область A65:B69.

— Вызовите Мастер функций.

— Выберите функцию Линейн.

— Определим аргументы функции.

— В качестве изв_знач_уукажите В1:В25.

— В качестве изв_знач_хукажите А1:А25.

— Третье поле Константаоставьте пустым.

— В четвертом поле статнаберите истина.

— Нажмите кнопку Закончить.

— Установите курсор в строку формул.

— Нажмите комбинацию клавиш Ctrl+Shift+Enter, это обеспечит ввод табличной формулы!

В результате должны заполниться все ячейки интервала A65:B69(см. табл.9).

Таблица 9.

Поясним назначение некоторых величин, расположенных в табл.9.

Величины, расположенные в ячейках A67 и B67 характеризуют соответственно наклон и сдвиг.

A69 — коэффициент детерминированности.

A70 — F-наблюдаемое значение.

B68 — число степеней свободы.

A69 — регрессионная сумма квадратов.

B69 — остаточная сумма квадратов.

Рассмотрим назначение функции ЛИНЕЙН.

Эта функция использует метод наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные. Функция возвращает массив, который описывает полученную прямую. Уравнение для прямой линии имеет следующий вид:

или ,

где зависимое значение y является функцией независимого значения x. Значения m — это коэффициенты, соответствующие каждой независимой переменной x, а b — это постоянная. Заметим, что y, x и m могут быть векторами.

Функция ЛИНЕЙН возвращает массив . ЛИНЕЙН может также возвращать дополнительную регрессионную статистику.

Синтаксис

ЛИНЕЙН(известные_значения_y;известные_значения_x;конст; статистика)

Известные_значения_y — это множество значений y, которые уже известны для соотношения .

— Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная.

Известные_значения_x — это множество значений x, которые уже известны для соотношения .

— Массив известные_значения_x может содержать одно или несколько множеств переменных.

— Если используется только одна переменная, то известные_значения_y и известные_значения_x могут быть массивами любой формы при условии, что они имеют одинаковую размерность.

— Если используется более одной переменной, то известные_значения_y должны быть вектором (то есть интервалом высотой в одну строку или шириной в один столбец).

Конст- это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.

— Если констимеет значение ИСТИНА или опущена, то b вычисляется обычным образом.

— Если констимеет значение ЛОЖЬ, то b полагается равным 0 и значения m подбираются так, чтобы выполнялось соотношение y = mx .

Статистика- это логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии.

— Если статистикаимеет значение ИСТИНА, то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику, так что возвращаемый массив будет иметь вид:

. (17)

— Если статистика имеет значение ЛОЖЬ или опущена, то функция ЛИНЕЙН возвращает только коэффициенты m и постоянную b.

Дополнительная регрессионная статистика приведена в табл. 10

Таблица 10.

Величина Описание
Стандартные значения ошибок для коэффициентов .
= #Н/Д, если конст имеет значение ЛОЖЬ.
Коэффициент детерминированности.
Стандартная ошибка для оценки y.
F-статистика, или F-наблюдаемое значение. F-статистика используется для определения того, является ли наблюдаемая взаимосвязь между зависимой и независимой переменными случайной или нет.
Степени свободы. Степени свободы полезны для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели нужно сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН.
Регрессионная сумма квадратов.
Остаточная сумма квадратов

В табл.11 показано, в каком порядке возвращается дополнительная регрессионная статистика.

Таблица 11.

Точность аппроксимации с помощью прямой, вычисленной функцией ЛИНЕЙН, зависит от степени разброса данных. Чем ближе данные к прямой, тем более точной является модель, используемая функцией ЛИНЕЙН.

Функция ЛИНЕЙН использует метод наименьших квадратов для определения наилучшей аппроксимации данных.

Когда имеется только одна независимая переменная, тогда x, m и b вычисляются по следующим формулам:

, (18)

Эти формулы могут быть использованы для нахождения решения системы (4), вместо m и b следует подставить и .

:

Источник: //csaa.ru/postroenie-grafika-v-excel-i-ispolzovanie-funkcii/

Поделиться:
Нет комментариев

    Добавить комментарий

    Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.