Как сделать кривую в excel?

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

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

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

Начнем из анализа и создания графика функций в Excel. Мы убедимся в том, что линейный график в Excel существенно отличается от графика линейной функции, который преподают в школе.

Линейная функция x=y имеет следующие значения: x1=0, x2=1, x3=7. Заполните таблицу этими значениями как показано на рисунке:

Выделите диапазон A1:B4 и выберите инструмент: «Вставка»-«Диаграммы»-«График»-«График с маркерами».

В результате у нас созданы 2 линии на графике, которые наложены одна сверх другой. Так же мы видим, что линии сломаны, а значит, они не соответствуют презентации школьному графику линейной функции. Излом линий, получается, по причине того, что на оси X у нас после значений: 0, 1 сразу идет значение 7 (упущены 2,3,4,5,6).

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

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

Чтобы создать правильный график функций в Excel выберите подходящий график.

Выделите диапазон A1:B4 и выберите инструмент: «Вставка»-«Диаграммы»-«Точечная»-«Точечная с прямыми отрезками и маркерами».

Как видно на рисунке данный график содержит одинаковое количество значений на осях X и Y. По умолчанию в шаблоне данного графика цена делений оси X равна 2. При необходимости ее можно изменить. Для этого:

Теперь у нас отображается одинаковое количество значений по всем осям.

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

Примечание. В принципе первый способ можно было-бы оптимизировать под отображение линейной функции, если таблицу заполнить всеми значениями 0-7.

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

Несмотря на то, что теоретически гвозди можно забивать и микроскопом.

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

На следующих примерах вы убедитесь, что выбор имеет большое значение.

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

Использование диаграмм Excel — хороший способ отображения графиков математических и тригонометрических функций. В этой статье описываются два метода построения графика функции: с одной переменной с помощью точечной диаграммы и с двумя переменными с помощью 3D-диаграммы.

Построение графиков математических функций с одной переменной

Точечная диаграмма (известная как диаграмма XY в предыдущих версиях Excel) отображает точку (маркер) для каждой пары значений. Например, на рис. 140.1 показан график функции SIN
.

На диаграмму наносятся рассчитанные значения у для значений х (в радианах) от -5 до 5 с инкрементом (приращением) 0,5.

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

• Функция выражается в таком виде: у = SIN(x) .
• Соответствующая формула в ячейке В2 (которая копируется в ячейки, расположенные ниже) будет следующей: =SIN(A2) .
• Чтобы создать эту диаграмму, выполните следующие действия.

1. Выделите диапазон А1:В22 .
2. Выберите Вставка Диаграммы Точечная Точечная с прямыми отрезками и маркерами
   .

Измените значения в столбце А для построения графика функции при различных значениях х
. И, конечно, вы можете использовать любую формулу с одной переменной в столбце В. Вот несколько примеров, которые приводят к построению интересных графиков: =SIN(ПИ()*A2)*(ПИ()*A2)
=SIN(A2)/A2
=SIN(A2^3)*COS(A2^2)

1. =НОРМ.РАСП(A2;0;1;ЛОЖЬ)
2. Чтобы получить более точную диаграмму, увеличьте количество значений для построения графика и сделайте приращение в столбце А меньше.
3. Вы можете использовать онлайн наш файл примера графиков математических функций с одной переменной, расположенной в Excel Web Apps при помощи Skydrive, и внести свои данные (изменения не будут сохраняться) или скачать себе на компьютер, для чего необходимо кликнуть по иконке Excel в правом нижнем углу. Это бесплатно ????

Построение графиков математических функций с двумя переменными

Вы также можете строить графики функций, которые используют две переменные. Например, следующая функция рассчитывает z
для различных значений двух переменных (х и у): =SIN($A2)*COS($B1)

На рис. 140.2 приведена поверхностная диаграмма, которая рассчитывает значение z
для 21 значения х
в диапазоне от -3 до 0 и для 21 значения у
в диапазоне от 2 до 5. Для х
и у
используется приращение 0,15.

• Значения х находятся в диапазоне А2:А22 , а значения у — в диапазоне B1:V1 .
• Формула в ячейке В2 копируется в другие ячейки таблицы и имеет следующий вид: =SIN($A2)*C0S(B$1) .
• Чтобы создать диаграмму, выполните приведенные ниже действия.

1. Выделите диапазон A1:V22 .
2. Выберите Вставка Диаграммы Другие Поверхность
   .
3. Выберите макет диаграммы, который вам нравится, а затем настройте его.

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

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

Вот другие формулы, которые вы можете попробовать: =SIN(КОРЕНЬ($A2^2+B$1^2))
=SIN($A2)*COS($A2*B$1)

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

Применение

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

Построение графика

Мы можем построить график функции в «Экселе» обычного типа или относящийся к сложнейшим математическим операциям. Для этого выполняем несколько шагов. Открываем чистый лист Excel либо запускаем новый проект. Создаем два столбца.

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

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

Расчет

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

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

Необходимо просто щелкнуть мышью по соответствующей области.

Подробности о растягивании

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

Инструкция

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

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

В результате проделанных действий возникнет новое диалоговое окно, в нем нажимаем кнопку «Ряд».

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

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

График построен. При желании он может быть любых размеров и различной сложности.

Другие возможности

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

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

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

Рассмотрим вариант, в котором исходные данные необходимо брать из других клеток таблицы. К примеру, чтобы в ячейке B5 было показано уменьшенное на показатель 55 число, находящееся в ячейке D1, нажимаем на B5. Вводим знак равенства.

После этого нажимаем ячейку D1. За знаком равенства последует ссылка на выбранную нами клетку. Также можно набрать необходимый адрес вручную, не используя мышь. После этого вводим знак вычитания. Указываем число 55. Нажимаем Enter.

Далее Excel автоматически рассчитает и покажет результат.

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

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

Именно она нам и понадобится.

Чтобы ввести простую формулу, указываем в ячейке требуемое значение, вписав перед ним знак равенства. После нажатия клавиши Enter мы увидим результат. Можно использовать несколько способов ввода данных. Нажимаем на пустую клетку А1.

Вставляем в нее определенное значение, предварив его символом «равно». Переходим к соседней ячейке В1. Указываем в нее другой элемент формулы. Продолжаем аналогичные действия, пока все предоставленные в задаче параметры не будут введены в таблицу.

Знак «равно» ставим в крайнюю пустую ячейку.

Переходим к следующему этапу. Выбираем ячейку А1. Вставляем в нее арифметический знак (деление, умножение, сложение). Выбираем другую ячейку, к примеру, В2. Нажимаем Enter. Создаем исходное выражение, дважды кликнув на клетку и нажав Ctrl и «Апостроф».

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

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

Описывать процесс создания мы будем на примере Microsoft Excel 2007 (уже устаревшая, но очень хорошая версия программы). Процесс построения графика в более свежем Microsoft Excel 2010 будет отличаться лишь в некоторых деталях.

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

Пошаговая инструкция построения графика функции в Excel 2007

1. Запускаем программу, которая создаст новый чистый лист книги Excel. Подписываем два столбца (B и С), в одном из которых будет записан аргумент x, а в другом — функция y.
2. Заносим в столбец B, значения аргументов x, начиная с ячейки B3.

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

   Предлагаем выбрать значения в промежутке от -3 до +3 с шагом 0,1. В итоге вы получите 60 значений, по которым график функции будет проложен весьма плавно.

3. Далее, в ячейку C3 забьём формулу функции синуса или ту, которую вам надо построить. Если помните тригонометрию, то функция синуса записывается в виде y = sin x.

4. Однако формулы в Excel отличаются от записей математических формул, и всегда начинаются со знака равно — «=». В нашем примере, вы должны записать в ячейке C3 формулу вида = SIN(B3).
5. Забивать формулу в каждой новой строке очень долго и неудобно (представляете, нужно вбить 60 раз!).

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

6. Для этого щёлкаем на ячейке с набранной формулой. В правом нижнем углу ячейки должен появиться небольшой квадратик.

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

7. Переходим к построению графика функции. Заходим в Меню «Вставка» -> «Диаграмма» и выбираем подходящую точечную диаграмму. Жмем волшебную кнопку [Далее].
8. В открывшемся окне щелкаем вкладку «Ряд».

   Добавляем ряд нажатием кнопки [Добавить].

9. В этом окне нужно задать, из какого диапазона будут выбраны числа для графика. Чтобы выбрать нужные ячейки, следует щёлкнуть поочередно по кнопкам.
10. После этого выделим те ячейки, откуда будут выбраны значения для x и y.
11. Последним шагом станет нажатие кнопки [Готово].

Видеоурок построения графика функции средствами Microsoft Excel 2010

Остается только правильно настроить график функции в Microsoft Excel, согласно требованиям вашего преподавателя по информатике. По сути, само построение графика у знающего студента занимает от силы 1-2 минуты. Желаем успехов в построение более сложных графиков.

Источник: https://bakep.ru/equation-of-the-graph-in-the-excel-functions-of-excel-how-to-build.html

Ступенчатый график в Excel

Хитрости » 15 Декабрь 2015       Дмитрий       23970 просмотров

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

• Скачать пример
•   Tips_Charts_StepChart.xls (56,0 KiB, 2 215 скачиваний)

Способ 1: Применяем планки погрешностей
Для начала потребуется добавить столбец с формулой для погрешностей. Запишем в ячейку с первым значением(на скрине это C2, напротив 1 апр 2015) значение 0, а в следующую ячейку формулу: =B3-B2.

Теперь копируем ячейку с формулой, выделяем данные в столбце С, начиная с С3 и до конца данных в таблице и вставляем скопированную формулу. Или можно просто протянуть эту формулу до конца таблицы.

Теперь выделяем первые два столбца таблицы вместе с заголовками(Дата и Выручка) и вставляем новую диаграмму:

• Excel 2003:
  Вставка(Insert) —Диаграмма(Chart) —Точечная(Scatter) —С прямыми отрезками(Scatter with straight lines)
• Excel 2007 и выше:
  вкладка Вставка(Insert) -группа Диаграммы(Charts) —Точечная(Scatter) —С прямыми отрезками(Scatter with straight lines):
  

Далее необходимо добавить планки погрешностей:

• Excel 2007-2010:
  вкладка Макет(Layout) —Предел/Планки погрешностей(Error Bars) —Дополнительные параметры планок погрешностей(More Error Bars Options…)
• Excel 2013
  жмем справа от диаграммы кнопку со знаком «плюс» и ставим флажок Предел погрешностей(Error Bars)

Осталось дело за малым: на вкладке Макет(Layout) -группа кнопок Текущий фрагмент(Current Selection) выбираем Планки погрешностей по оси X(X Error Bars) -и сразу жмем там же кнопку Формат выделенного(Format Selection)(расположена сразу под вып.списком).
Указываем следующие параметры:

• Направление(Display) —Плюс(Plus);
• Конечный стиль(End Style) —Без точки(No Cap);
• Величина погрешности(Error Amount) —фиксированное значение(Fixed value) — 1С величиной погрешности для горизонтальных планок чуть подробнее: 1 выбираем, т.к. у нас данные указаны в таблице ежедневные. Т.е. шаг оси между данными получается 1(один день). Если бы данные поступали каждые 20 дней и в таблице они были бы занесены тоже с промежутком через каждые 20 дней — то фиксированное значение необходимо было бы указать 20.

Далее, не закрывая окно свойств ряда идем на вкладку Макет(Layout) -группа кнопок Текущий фрагмент(Current Selection) —Планки погрешностей по оси Y(Y Error Bars). Здесь указываем:

• Направление(Display) —Минус(Minus);
• Конечный стиль(End Style) —Без точки(No Cap);
• Величина погрешности(Error Amount) —пользовательская(Custom). Жмем Укажите значения(Specify Value) и в появившемся окне для Отрицательные значения ошибки(Negative Error Value) указываем столбец с теми формулами, которые записаны у нас в столбце С (в примере C2:C23). Ок. Закрыть.

И пара последних косметических штришков:

• Убираем «лишнюю» линию графика: выделяем Ряд «Выручка»(это наша основная линия после создания графика) -правая кнопка мыши —Формат ряда данных(Format Data Series). Переходим к свойствам Цвет линии(Line Color) и ставим Нет линий(No line):
  
• Т.к. тип диаграммы Точечная строится по своим законам, то на диаграмме скорее всего перед данными и после будут пропуски:
  
  Происходит это потому, что шаг в таких диаграммах выбирается автоматически и «с запасом». Чтобы убрать эти пропуски надо посмотреть значение самой первой даты исходных данных и самой последней. Запомнить эти значения. Далее в диаграмме на оси с датами щелкнуть правой кнопкой мыши —Формат оси(Format axis) —Формат оси(Axis options) -выставляем для Минимум(Minimum) и Максимум(Maximum) значение первой и последней даты. Теперь пропуски «исчезнут».

Вот график и построен. Остается лишь навести красоту. Например, увеличить ширину линий, изменить цвет. Чтобы увеличить ширину линий можно сразу при установке планок погрешностей после установления основных параметров перейти к свойствам Цвет линии(Line Color)(для задания нужного цвета) и Тип линии(Line Style)(для задания нужной ширины).

Если же не сделали этого сразу, то это можно сделать в любой момент: вкладка Макет(Layout) -группа кнопок Текущий фрагмент(Current Selection) —Планки погрешностей по оси X(X Error Bars). И так для любого ряда.

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

Сделать это можно, выделив любой из элементов -правая кнопка мыши —Формат «имя элемента»(Format «имя элемента»)
Пример результата графика через погрешности приведен в самом начале статьи.

Способ 2: «Растягиваем» данные

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

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

1. =ИНДЕКС($A$2:$B$23;ЦЕЛОЕ(СТРОКА()-СТРОКА(A2)/2);1)
   =INDEX($A$2:$B$23,INT(ROW()-ROW(A2)/2),1)
2. =ИНДЕКС($A$2:$B$23;ЦЕЛОЕ(СТРОКА(A1)-СТРОКА(B1)/2)+1;2)
   =INDEX($A$2:$B$23,INT(ROW(A1)-ROW(B1)/2)+1,2)

в столбец E так же прописываем формулу, но чуть другую:

Эти формулы надо будет скопировать на количество строк, большее в два раза, чем исходные данные. Как вариант можно протягивать формулу до тех пор, пока формула не вернет значение ошибки #ССЫЛКА!(#REF!). А теперь останется только вставить на основании этих данных диаграмму типа График:

• Excel 2003:
  Вставка(Insert) —Диаграмма(Chart) —График(Line) —График(Line)
• Excel 2007 и выше:
  вкладка Вставка(Insert) -группа Диаграммы(Charts) —График(Line) —График(Line)

Статья помогла? Поделись ссылкой с друзьями!

Источник: https://www.excel-vba.ru/chto-umeet-excel/stupenchatyj-grafik-v-excel/

Может ли Excel строить сложные кривые?

Меня трудно чем-то удивить в Excel, но когда я впервые увидел вот этот сайт, то у меня чуть глаз не выпал на клавиатуру. Очень рекомендую вам пройти по ссылке и поглазеть. В моём личном рейтинге эта команда (там двое мужчин и девушка) является №1 в мире по Excel.

Сегодня я вам покажу нечто из их арсенала (с моими некоторыми адаптациями). Посмотрим на то, как при помощи точечной диаграммы с гладкими кривыми строить ТАКИЕ сложные вещи, что, когда глядишь на это в первый раз, просто не веришь, что это вообще можно нарисовать в Excel. Ну, например, как вам это:

Дело в том, что мы все привыкли, что такие графики строятся по точкам, которые рассчитываются в ячейках таблицы.

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

Всё что вам нужно — это точечная диаграмма с гладкими кривыми, несколько констант и несколько именованных формул.

Те граждане, кто пока плохо понимает, зачем это всё надо, сейчас просто откроют файлик, включат макросы и нажмут кнопку Старт, потому что это ПРОСТО КРАСИВО. На это можно смотреть минут 15 и всё равно будет интересно. Дольше смотреть не рекомендую, так как глаза и мозг устанут.

Смотреть красоту ТУТ

А с теми, кому интересно, как это устроено, продолжаем разговор.

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

Эпициклоиды и гипоциклоиды (подмножество циклоидальных кривых) строятся по следующим параметрическим формулам:

Эпициклоиды строятся так:

Гипоциклоиды так:

Знаки у R1 и R2 относительно друг друга определяют тип кривой (по какой поверхности круга R1 — по внутренней или внешней — будет катиться круг R2). Обратите внимание, что t — это угод в радианах. Напоминаю, что углы в 5' и 365' — разные углы.

Если мы заглянем в ряды данных диаграммы, то увидим это:

Сердце решения располагается тут:

Как видите, большинство именованных диапазонов просто сылаются на конкретные ячейки и ничего сложного из себя не представляют. Кроме трёх диапазонов: arr, x_e, y_e. Можно заметить, что x_e и y_e — это то, на что ссылается ряд данных в диаграмме, и, что это формулы, описывающие кривые, которые я приводил выше. А вот ИД arr надо осмыслить:

=СТРОКА( СМЕЩ( Лепота!$A$1; ; ; x) )

X у нас равен 5400. Функция СМЕЩ() сформирует диапазон A1:A5400. А формула СТРОКА() будет вычислена для каждой строки данного диапазона, поэтому в результате мы получим массив чисел от 1 до 5400.

Этот массив, будучи переданным в x_e, y_e тоже породит соответствующие массивы. Обратите внимание, что arr пропускается через функцию РАДИАНЫ(). Таким образом мы градусы (1..5400) переводим в радианы.

Если честно, то я не знал, что ТАК можно 🙂

Вот собственно и всё. А тот макрос, который у меня есть в книге, он просто случайным образом, но плавно меняет параметры R1, R2, D в пределах от From до To с шагом Step.

P.S. Любителям WinAPI с его Sleep рекомендую обратить внимание на конструкцию Application.Wait Now + 1 / 24 / 60 / 60 / 2 🙂

Ну а теперь залипайте дальше на лицезрение циклоид 🙂

Источник: http://perfect-excel.ru/publ/excel/grafiki_i_diagrammy/mozhet_li_excel_stroit_slozhnye_krivye/6-1-0-104

Точечная диаграмма в Excel

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

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

Давайте на данном примере разберем все шаги построения точечной диаграммы.

Как построить точечный график в Excel?

Предположим, что у нас имеется таблица с двумя рядами данных (затраченное время и количество решенных вопросов):

Чтобы построить точечную диаграмму, выделяем диапазон с данными (A1:B10) и на панели вкладок выбираем Вставка -> Диаграмма -> Точечная -> Точечная с маркерами:

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

• С маркерами — точки выделяются в виде маркеров;
• С гладкими кривыми и маркерами — диаграмма с соединением точек гладкими кривыми и выделением точек маркерами;
• С гладкими кривыми — диаграмма с соединением точек гладкими кривыми;
• С прямыми отрезками и маркерами — диаграмма с соединением точек прямыми отрезками и выделением точек маркерами;
• С прямыми отрезками — диаграмма с соединением точек прямыми отрезками.

Удачи вам и до скорой встречи на страницах блога Tutorexcel.ru!

• Функция ВЫБОР в Excel
• Зависимые выпадающие списки в Excel

Источник: https://tutorexcel.ru/diagrammy/tochechnaya-diagramma-v-excel/

Сглаживание графика в Excel. Настройка линии диаграммы

Чуть ранее мы уже писали, как красиво оформить нулевые/пустые значения на графике, чтобы диаграмма не получалась «зубчатой». Помимо этого, для лучшей визуализации информации иногда нужно сделать сглаживание графика в Excel. Как это сделать? Читайте ниже

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

Сглаживание графика в Excel. Как быстро сделать?

Часто соединения узлов графика выглядят некрасиво, если линии на графике расположены под острыми углами. Как сделать плавную линию? Правой кнопкой мыши нажимаем на сам график — выплывает окно —

Формат ряда данных (см. первую картинку) выбираем — пункт Тип линии -ставим галочку — Сглаженная линия

Теперь линия сгладилась.

 Экспоненциальное сглаживание в Excel

• В Excel можно подключить пакет анализа для сглаживания самих данных.
• Такое сглаживание это метод применяемый для сглаживания временных рядом — статья википедии
• Зайдите в меню — Параметры Excel  —  Надстройки —  Пакет анализа (в правом окне) и в самом низу нажимайте Перейти

В открывшемся окне находим Экспоненциальное сглаживание.

Как найти прямую приближенных значений

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

Поделитесь нашей статьей в ваших соцсетях:

Источник: https://excelworks.ru/2017/02/01/sglazhivanie-grafika-v-excel/

Нормальное распределение. Построение графика в Excel. Концепция шести сигм

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

• Функция НОРМРАСП имеет следующий синтаксис:
• НОРМРАСП (Х; среднее; стандартное_откл; интегральная)
• Х — аргумент функции; фактически НОРМРАСП можно трактовать как y=f(x); при этом функция возвращает вероятность реализации события Х
• Среднее (µ) — среднее арифметическое распределения; чем дальше Х от среднего, тем ниже вероятность реализации такого события
• Стандартное_откл (σ) — стандартное отклонение распределения; мера кучности; чем меньше σ, тем выше вероятность у тех Х, которые расположены ближе к среднему
• Интегральная — логическое значение, определяющее форму функции. Если «интегральная» имеет значение ИСТИНА, функция НОРМРАСП возвращает интегральную функцию распределения, тот есть суммарную вероятность всех событий для аргументов от -∞ до Х; если «интегральная» имеет значение ЛОЖЬ, возвращается вероятность реализации события Х, точнее говоря, вероятность событий находящихся в некотором диапазоне вокруг Х
• Например, для µ=0 имеем:
• 
• Скачать заметку в формате Word, пример в формате Excel

Здесь по оси абсцисс единица измерения – σ, или (что то же самое), можно сказать, что график построен для σ = 1. То есть, «-2» на графике означает -2σ. По оси ординат шкала убрана умышленно, так как она лишена смысла.

Точнее говоря, высота кривой зависит от плотности точек на оси абсцисс, по которым мы строим график. Например, если на интервал от 0 до 1σ приходится 10 точек, то высота в максимуме составит 4%, а если 20 точек – 2%.

Здесь проценты означают вероятность попадания случайной величины в узкий диапазон окрестности точки на оси абсцисс. Зато имеет смысл площадь под кривой на определенном интервале. И эта площадь не зависит от плотности точек.

Теперь, наверное, вам будет лучше понятен смысл выражения «качество шести сигм». Оно означает, что производство налажено таким образом, что случайная величина Х (например, диаметр вала) находясь в диапазон µ ± 6σ, всё еще удовлетворяет техническим условиям (допускам).

Это достигается за счет значительного уменьшения сигмы, то есть случайная величина Х очень близка к нормативному значению µ.

На графике ниже представлено три ситуации, когда границы допуска остаются неизменными, а благодаря повышению качества (уменьшению вариабельности, сужению сигма) доля брака сокращается:

На первом рисунке только 1,5σ попадают в границы допуска, то есть только 86,6% деталей являются годными. На втором рисунке уже 3σ попадают в границы допуска, то есть 99,75% являются годными. Но всё еще 25 деталей из каждых 10 000 произведенных являются браком. На третьем рисунке целых 6σ попадают в границы допуска, то есть в брак попадут только две детали на миллиард изготовленных!

Вообще-то говоря, измерение качества в терминах сигм использует не совсем нормальное распределение. ???? Вот что пишет на эту тему Википедия:

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

Опытным путём было установлено, что изменение параметров во времени можно учесть с помощью смещения в 1,5 сигма.

Другими словами, с течением времени длина промежутка между границами поля допуска под кривой нормального распределения уменьшается до 4,5 сигма вследствие того, что среднее процесса с течением времени смещается и/или среднеквадратическое отклонение увеличивается.

Широко распространённое представление о «процессе шесть сигма» заключается в том, что такой процесс позволяет получить уровень качества 3,4 дефектных единиц на миллион готовых изделий при условии, что длина под кривой слева или справа от среднего будет соответствовать 4,5 сигма (без учёта левого или правого конца кривой за границей поля допуска).

Таким образом, уровень качества 3,4 дефектных единиц на миллион готовых изделий соответствует длине промежутка 4,5 сигма, получаемых разницей между 6 сигма и сдвигом в 1,5 сигма, которое было введено, чтобы учесть изменение показателей с течением времени.

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

С моей точки зрения, не вполне внятное объяснение. Тем не менее, во всем мире принята следующая таблица соответствия числа дефектов и уровня качества в сигмах:

Число сигм	Число дефектов на миллион измерений	Процент дефектов (несоответствий)	Уровень качества
6σ	3,4	0,00034%	идеал
5σ	233	0,023%	мировой уровень
4σ	6210	0,62%	приемлемый уровень
3σ	66 807	6,68%	недостаточный уровень
2σ	308 537	30,9%	неприемлемый уровень
1σ	691 462	69,1%

Для сравнения приведу таблицу для нормального распределения:

Сигма окрестность среднего значения	Число случаев на миллион за пределами сигма окрестности	Процент случаев за пределами сигма окрестности
6σ	0,002	0,0000002%
5σ	0,6	0,00006%
4σ	63	0,01%
3σ	2 700	0,27%
2σ	45 500	4,6%
1σ	317 311	31,7%

Источник: http://baguzin.ru/wp/normalnoe-raspredelenie-postroeni/

Как сделать линейную калибровочную кривую в Excel

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

В этой статье мы рассмотрим, как использовать Excel для создания диаграммы, построить линейную калибровочную кривую, отобразить формулу калибровочной кривой, а затем настроить простые формулы с помощью функций НАКЛОН и ПЕРЕКЛЮЧИТЬ, чтобы использовать уравнение калибровки в Excel.

Что такое калибровочная кривая и как Excel полезен при ее создании?

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

Двухточечная калибровка термометра с использованием точек замерзания и кипения воды будет иметь две пары данных: одну с момента, когда термометр находится в ледяной воде (32 ° F или 0 ° C) и один в кипящей воде (212 ° F или 100 ° C).

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

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

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

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

Давайте посмотрим на пример

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

Значения Y будут «откликами», и они будут представлять собой показания прибора, полученные при измерении каждого химического раствора, или измеренное расстояние, на котором расстояние от пусковой установки, на которую упал мрамор, используя каждое входное значение.

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

Шаг первый: создайте свою диаграмму

• Наш простой пример электронной таблицы состоит из двух столбцов: X-Value и Y-Value.
• Начнем с выбора данных для построения графика.
• Сначала выберите ячейки столбца «X-значение».
• Теперь нажмите клавишу Ctrl и затем щелкните ячейки столбца Y-значения.
• Перейдите на вкладку «Вставить».
• Перейдите в меню «Графики» и выберите первый вариант в раскрывающемся меню «Разброс».

scatter «width =» 314 «height =» 250 «onload =» pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); «onerror =» this.onerror = null; pagespeed.lazyLoad.

loadIfVisibleAndMaybeBeacon (это); «/>

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

Выберите серию, нажав на одну из синих точек. После выбора Excel обрисовывает в общих чертах точки.

Щелкните правой кнопкой мыши одну из точек и выберите опцию «Добавить линию тренда».

На графике появится прямая линия.

В правой части экрана появится меню «Format Trendline». Установите флажки рядом с «Показать уравнение на графике» и «Показать значение R-квадрата на графике».

«Значение R-квадрата — это статистика, показывающая, насколько точно линия соответствует данным. Наилучшее значение R-квадрата равно 1.000, что означает, что каждая точка данных касается линии.

По мере роста различий между точками данных и линией значение r-квадрата уменьшается, причем 0,000 является наименьшим возможным значением.

Уравнение и R-квадрат статистики трендовой линии появятся на графике. Обратите внимание, что в нашем примере корреляция данных очень хорошая, значение R-квадрата равно 0,988.

1. Уравнение имеет вид «Y = Mx + B», где M — наклон, а B — пересечение оси y прямой.
2. Теперь, когда калибровка завершена, давайте приступим к настройке диаграммы, отредактировав заголовок и добавив заголовки осей.
3. Чтобы изменить заголовок диаграммы, щелкните по нему, чтобы выделить текст.
4. Теперь введите новый заголовок, который описывает диаграмму.
5. Чтобы добавить заголовки к осям X и Y, сначала перейдите к «Инструменты диаграммы»> «Дизайн».

design «width =» 650 «height =» 225 «onload =» pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); «onerror =» this.onerror = null; pagespeed.lazyLoadImages. loadIfVisibleAndMaybeBeacon (это); «/>

Нажмите «Добавить элемент диаграммы».

Теперь перейдите к Названия осей> Первичная горизонтальная.

основная горизонтальная «ширина =» 650 «высота =» 500 «onload =» pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); «onerror =» this.onerror = null; pagespeed.lazyLoadImages .loadIfVisibleAndMaybeBeacon (это); «/>

• Появится название оси.
• Чтобы переименовать заголовок оси, сначала выделите текст, а затем введите новый заголовок.
• Теперь перейдите к Названию осей> Первичная вертикаль.
• Появится название оси.
• Переименуйте этот заголовок, выделив текст и введя новый заголовок.
• Ваша диаграмма теперь завершена.

Шаг второй: Рассчитать линейное уравнение и R-квадрат

Теперь давайте вычислим линейное уравнение и R-квадрат, используя встроенные в Excel функции SLOPE, INTERCEPT и CORREL.

К нашему листу (в строке 14) мы добавили заголовки для этих трех функций. Мы выполним фактические вычисления в ячейках под этими заголовками.

Сначала рассчитаем НАКЛОН. Выберите ячейку A15.

Перейдите к формулам> Дополнительные функции> Статистические> НАКЛОН.

Дополнительные функции> Статистические> НАКЛОН »width =» 650 «height =» 435 «onload =» pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); «onerror =» this.onerror = null ; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (это); «/>

В поле «Known_xs» выберите или введите ячейки столбца X-Value. Порядок полей «Known_ys» и «Known_xs» имеет значение в функции SLOPE.

1. Нажмите «ОК». Окончательная формула в строке формул должна выглядеть следующим образом:
2. = СКЛОН (С3: С12, В3: В12)
3. Обратите внимание, что значение, возвращаемое функцией SLOPE в ячейке A15, соответствует значению, отображенному на графике.
4. Затем выберите ячейку B15 и перейдите к «Формулы»> «Дополнительные функции»> «Статистические данные»> «ПЕРЕКРЫТЬ».

Дополнительные функции> Статистические> INTERCEPT «width =» 650 «height =» 435 «onload =» pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); «onerror =» this.onerror = null ; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (это); «/>

Откроется окно «Аргументы функции». Выберите или введите в ячейки столбца Y-значение для поля «Known_ys».

Выберите или введите в ячейки столбца X-Value поле «Known_xs». Порядок полей «Known_ys» и «Known_xs» также имеет значение в функции INTERCEPT.

• Нажмите «ОК». Окончательная формула в строке формул должна выглядеть следующим образом:
• = ОТРЕЗОК (С3: С12, В3: В12)
• Обратите внимание, что значение, возвращаемое функцией INTERCEPT, соответствует точке пересечения y, отображаемой на диаграмме.
• Затем выберите ячейку C15 и перейдите к «Формулы»> «Дополнительные функции»> «Статистические данные»> «CORREL».

Дополнительные функции> Статистические> CORREL «width =» 650 «height =» 435 «onload =» pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); «onerror =» this.onerror = null ; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (это); «/>

Откроется окно «Аргументы функции». Выберите или введите любой из двух диапазонов ячеек для поля «Массив1». В отличие от SLOPE и INTERCEPT, порядок не влияет на результат функции CORREL.

1. Выберите или введите другой из двух диапазонов ячеек для поля «Array2».
2. Нажмите «ОК». Формула должна выглядеть следующим образом на панели формул:
3. = КОРРЕЛ (В3: В12, С3: С12)

Обратите внимание, что значение, возвращаемое функцией CORREL, не соответствует значению «r-квадрат» на графике. Функция CORREL возвращает «R», поэтому мы должны возвести ее в квадрат, чтобы вычислить «R-квадрат».

• Щелкните внутри панели функций и добавьте «^ 2» в конец формулы, чтобы возвести в квадрат значение, возвращаемое функцией CORREL. Заполненная формула теперь должна выглядеть так:
• = КОРРЕЛ (В3: В12, С3: С12) ^ 2
• Нажмите Ввод.
• После изменения формулы значение «R-квадрат» теперь соответствует значению, отображенному на графике.

Шаг третий: настройка формул для быстрого расчета значений

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

2. Эти шаги настроят формулы, необходимые для того, чтобы вы могли ввести значение X или значение Y и получить соответствующее значение на основе калибровочной кривой.

3. Уравнение линии наилучшего соответствия имеет вид «Y-значение = НАКЛОН * X-значение + INTERCEPT», поэтому решение для «Y-значения» выполняется путем умножения значения X и SLOPE, а затем добавив ИНТЕРЦЕПТ.

В качестве примера мы вводим ноль в качестве значения X. Возвращаемое значение Y должно быть равно ПЕРЕКЛЮЧЕНИЮ линии наилучшего соответствия. Это соответствует, поэтому мы знаем, что формула работает правильно.

Решение для значения X на основе значения Y выполняется путем вычитания INTERCEPT из значения Y и деления результата на НАКЛОН:

X-значение = (Y-значение-ОТРЕЗОК)/СКЛОН

В качестве примера мы использовали INTERCEPT в качестве значения Y. Возвращаемое значение Х должно быть равно нулю, но возвращаемое значение равно 3.14934E-06. Возвращаемое значение не равно нулю, потому что мы непреднамеренно обрезали результат INTERCEPT при вводе значения. Однако формула работает правильно, потому что результат формулы равен 0,00000314934, что по существу равно нулю.

Вы можете ввести любое X-значение в первую ячейку с толстыми границами, и Excel автоматически вычислит соответствующее значение Y.

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

В этом случае прибор показывает «5», поэтому при калибровке будет предложена концентрация 4,94, или мы хотим, чтобы шарик прошел пять единиц расстояния, поэтому при калибровке предлагается ввести 4,94 в качестве входной переменной для программы, управляющей пусковой установкой мрамора. Мы можем быть достаточно уверены в этих результатах из-за высокого значения R-квадрата в этом примере.

Источник: https://tutorybird.ru/excel/kak-sdelat-linejnuyu-kalibrovochnuyu-k/