Как найти точку пересечения на графике в excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949

ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

Как найти точку пересечения на графике в excel

Задали мне тут недавно вопрос. Есть график, на котором имеем 3 показателя работы производства: удельный расход материала, норматив расхода и объём производства, а по горизонтальной оси временной период (недели, например).

Исходные данные выглядят так:

Удельный расход материала зависит от объёма производства. Он то выше, то ниже норматива по итогам периода и возникает естественный вопрос показать на диаграмме те моменты времени и те объёмы производства, когда линия расхода пересекает линию норматива. Может быть эта проблема и лишена какого-то великого смысла, но тем не менее вопрос был задан, значит потребность такая у людей есть.

А это наша конечная цель:

Скачать пример

ChartsIntersection.xlsx

Решение

Шаг 1

Для начала обратим внимание, что исходный тип диаграммы — График с маркерами .

Ряды этого типа описываются так:

Заметьте, что тут нет значений координат по оси X, а есть только координаты по условной оси Y. Этим подразумевается, что условная ось X — общая для всех рядов, а также, что при их построении не используются значения, отличные от заранее фиксированного ряда оси X. Вот есть у нас периоды: 1, 2, 3 и т.д., нанесенные на ось Х, и использовать можно только их. Периода, где X=3.5 не существует для данного типа диаграммы.

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

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

Шаг 2

Теперь нам необходимо нанести на диаграмму дополнительные ряды данных:

Точки пересечения линий расхода материала с нормами расхода. Таких точек у нас будет 3, исходя из условий примера. Эти точки нам необходимо будет расчитать, вспомнив школьный курс геометрии (см. лист Треугольники ). В файле учебного примера на листе После в таблице N3:Q5 мы получили координаты нужных точек путем нехитрых формул. Тип диаграммы Точечная в отличие от рядов с показателями работы производства.
Точки пересечения найденных значений по оси X с объемом производства. Расчитывается в таблице N8:Q10 по близким формулам. Тип диаграммы Точечная .
Пунктирные линии, которые мы рисуем из эстетических соображений. Таблица с их координатами находится в N12:P18 .

Добавляем ряды данных в соответствии с теми точками, которые мы расчитали. Щёлкните ПКМ по диаграмме и выберите » Выбрать данные. «, далее воспользуйтесь кнопкой » Добавить «.

Пример одного из рядов:

Шаг 3

Отформатируйте линии и маркеры новых рядов данных, так как вам необходимо. Готово! 🙂

Добавление линии тренда или скользящего среднего на диаграмму

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

Примечание: Эти действия применимы Office 2013 и более новых версиях. Ищете по Office 2010 г.?

Добавление линии тренда

Ваш браузер не поддерживает видео.

Выделите диаграмму.
Щелкните значок «+» в правом верхнем углу диаграммы.
Выберите пункт Линия тренда.

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

Форматирование линии тренда

замещающий текст

Щелкните в любом месте диаграммы.
На вкладке Формат в группе Текущий выделение выберите в списке вариант линии тренда.
Нажмите кнопку Формат выделения.
В области Формат линии тренда выберите параметр Линия тренда, чтобы выбрать линию тренда для диаграммы. Форматирование линии тренда — это статистический способ измерения данных:
Задайте значение в полях «Вперед» и «Назад», чтобы проецируемые данные в будущем.

Добавление линии скользящего среднего

Линию тренда можно отформатирование на линию скользящего среднего.

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

Примечание: Число точек в линии тренда скользящего среднего равно общему числу точек в ряду, которое меньше числа, заданная для точки.

Добавление линии тренда или скользящего среднего на диаграмму в Office 2010 г.

Добавление линии тренда (Office 2010)

замещающий текст

На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните ряд данных, для которого требуется добавить линию тренда или линейную фильтрацию, или выполните указанные ниже действия, чтобы выбрать ряд данных из списка элементов диаграммы.
1. Щелкните диаграмму. Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
Примечание: Если выбрана диаграмма с несколькими рядами данных, но сам ряд данных не выбран, откроется диалоговое окно Добавление линии тренда. В поле со списком выберите нужный ряд данных, а затем нажмите кнопку ОК.
На вкладке Макет в группе Анализ выберите пункт Линия тренда.
Выполните одно из указанных ниже действий.
1. Выберите подходящий предопределенный параметр линии тренда.
Примечание: Линия тренда будет применена без возможности выбора конкретных параметров.

Используемый тип

Создание прямой линии тренда путем расчета по методу наименьших квадратов с помощью следующего уравнения:

где m — это наклон, а b — смещение.

Логарифмическая

Построение логарифмической линии тренда путем расчета точек методом наименьших квадратов с помощью следующего уравнения:

где c и b — константы, а «ln» — натуральный логарифм.

Полиномиальная

Построение полиномиальной или криволинейной линии тренда путем расчета точек методом наименьших квадратов с помощью следующего уравнения:

где b и являются константами.

Построение степенной линии тренда путем расчета точек методом наименьших квадратов с помощью следующего уравнения:

где c и b — константы.

Примечание: При наличии нулевых или отрицательных значений данных этот параметр недоступен.

Экспоненциальная

Построение экспоненциальной линии тренда путем расчета точек методом наименьших квадратов с помощью следующего уравнения:

где c и b — константы, e — основание натурального логарифма.

Примечание: При наличии нулевых или отрицательных значений данных этот параметр недоступен.

Линейная фильтрация

Построение линии тренда с линейной фильтрацией по следующей формуле:

Примечание: Число точек, образующих линию тренда с скользящее среднее, равно общему числу точек ряда за вычетом числа, указанного для параметра «Точки».

Величина достоверности аппроксимации

Построение линии тренда с указанием на диаграмме величина достоверности аппроксимации, вычисляемой по следующей формуле:

Этот параметр линии тренда располагается на вкладке Параметры диалогового окна Добавление линии тренда или Формат линии тренда.

Примечание: Отображаемая вместе с линией тренда величина достоверности аппроксимации не является скорректированной. Для логарифмической, степенной и экспоненциальной линий тренда в Excel используется видоизмененная модель регрессии.
- Если выбран тип Полиномиальная, введите в поле Степень наибольшую степень для независимой переменной.
- Если выбран тип Линейная фильтрация, введите в поле Точки число точек, используемых для расчета линейного фильтра.
- При добавлении скользящего среднего на точечная диаграмма скользящие средние значения основаны на порядке, за исключением значений X, относящегося к диаграмме. Чтобы получить нужный результат, перед добавлением скользящего среднего может потребоваться отсортировать значения X.
- При добавлении линии тренда в график, гистограмму, диаграмму с областями или линейчатую диаграмму линия тренда рассчитывается исходя из предположения, что значения x равны 1, 2, 3, 4, 5, 6 и т. д. Такое допущение делается вне зависимости от того, являются ли значения x числовыми или текстовыми. Чтобы рассчитать линию тренда на основе числовых значений x, следует использовать точечную диаграмму.
- В Excel название линии тренда назначается автоматически, но его можно изменить. В диалоговом окне Формат линии тренда в категории Параметры линии тренда в разделе Название аппроксимирующей (сглаженной) кривой выберите параметр Другое, а затем укажите название в поле Другое.
- Кроме того, можно добавить линейную фильтрацию, которая сглаживает отклонения в данных и более четко показывает форму линии тренда.
- Если изменить диаграмму или ряд данных таким образом, что они больше не будут поддерживать соответствующую линию тренда (например, если изменить тип диаграммы на объемную диаграмму или изменить представление отчет сводной диаграммы или связанный отчет сводной таблицы), линия тренда больше не будет отображаться на диаграмме.
- Для данных в строке (без диаграммы) наиболее точные прямые или экспоненциальные линии тренда можно создать с помощью автозаполнения или статистических функций, таких как РОСТ() или ТЕНДЕНЦИЯ().
Изменение формата линии тренда (Office 2010)
1. На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, которую необходимо изменить, или выполните следующие действия, чтобы выбрать ее из списка элементов диаграммы.
  1. Щелкните диаграмму. Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
  2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
2. На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда.
3. Чтобы изменить параметры цвета, типа или тени линии тренда, выберите категорию Цвет линии, Тип линии или Тень и задайте нужные значения.
Укажите количество периодов, которые нужно включить в прогноз (Office 2010 г.)
1. На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, которую необходимо изменить, или выполните следующие действия, чтобы выбрать ее из списка элементов диаграммы.
  1. Щелкните диаграмму. Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
  2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
2. На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда.
3. Чтобы указать число периодов для включения в прогноз, в разделе Прогноз укажите число периодов в поле вперед на или назад на.
Укажите точку пересечения линии тренда с вертикальной осью (значений) (Office 2010).
1. На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, которую необходимо изменить, или выполните следующие действия, чтобы выбрать ее из списка элементов диаграммы.
  1. Щелкните диаграмму. Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
  2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
2. На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда.
3. Установите флажок пересечение кривой с осью Y в точке, а затем в поле пересечение кривой с осью Y в точке введите значение, чтобы задать точку пересечения линии тренда с вертикальной осью (осью значений).
  
  Примечание: Это можно сделать только при использовании экспоненциальной, прямой или полиномиальной линии тренда.
  
  Отображение уравнения линии тренда на диаграмме (Office 2010)
  1. На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, которую необходимо изменить, или выполните следующие действия, чтобы выбрать ее из списка элементов диаграммы.
    1. Щелкните диаграмму. Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
    2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
  2. На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда.
  3. Чтобы показать на диаграмме уравнение линии тренда, установите флажок показывать уравнение на диаграмме.
    
    Примечание: Уравнения линии тренда нельзя показать для скользящего среднего.
    
    Совет: Формула линии тренда округлена, чтобы сделать ее более понятной. Однако вы можете изменить количество цифр для выбранной подписи линии тренда в поле Число десятичных разрядов на вкладке Число в диалоговом окне Формат подписи линии тренда. (Вкладка Формат, группа Текущий выделение,кнопка Формат выделения).
    
    Отображение величины квадрата R для линии тренда (Office 2010)
    1. На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, для которой требуется показать величина достоверности аппроксимации, или выполните указанные ниже действия, чтобы выбрать линию тренда из списка элементов диаграммы.
      1. Щелкните диаграмму. Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
      2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
    2. На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда.
    3. На вкладке Параметры линии тренда установите флажок поместить на диаграмму величину достоверности аппроксимации (R^2).
    Примечание: Для скользящего среднего величину достоверности аппроксимации отобразить нельзя.
    
    Удаление линии тренда (Office 2010)
    1. На диаграмме без накопления, плоской диаграмме, диаграмме с областями, линейчатой диаграмме, гистограмме, графике, биржевой, точечной или пузырьковой диаграмме щелкните линию тренда, которую необходимо удалить, или выполните следующие действия, чтобы выбрать линию тренда из списка элементов диаграммы.
      1. Щелкните диаграмму. Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
      2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
    2. Выполните одно из указанных ниже действий.
      1. На вкладке Макет в группе Анализ нажмите Линия тренда, а затем выберите пункт Нет.
      2. Нажмите клавишу DELETE.
    Совет: Вы также можете удалить линию тренда сразу после ее добавления на диаграмму, нажав кнопку Отменить панели быстрого доступа или нажав CTRL+Z.
    
    Добавление линии тренда
    1. В меню Вид выберите пункт Разметка страницы.
    2. На диаграмме выберите ряд данных, к который вы хотите добавить линию тренда, а затем перейдите на вкладку Конструктор диаграмм. Например, щелкните одну из линий графика. Будут выделены все маркер данных этого ряд данных.
    3. На вкладке Конструктор нажмите кнопку Добавить элемент диаграммыи выберите линия тренда.
    4. Выберите вариант линии тренда или нажмите кнопку Дополнительные параметры линии тренда.Вы можете выбрать один из следующих вариантов:
      - Экспоненциальная
      - Линейная
      - Логарифмическая
      - Полиномиальная
      - Электропитание
      - Линейная фильтрация
    Вы также можете дать линии тренда имя и выбрать параметры прогноза.
    
    Удаление линии тренда
    1. В меню Вид выберите пункт Разметка страницы.
    2. Щелкните диаграмму с линией тренда и перейдите на вкладку Конструктор диаграмм.
    3. Нажмите кнопку Добавить элемент диаграммы,выберите линия трендаи щелкните Нет. Также можно щелкнуть линию тренда и нажать кнопку DELETE.
    1. Щелкните в любом месте диаграммы, чтобы отдемонстрировать вкладку Диаграмма на ленте.
    2. Нажмите кнопку Формат, чтобы открыть параметры форматирования диаграммы.
    Откройте ряд, который вы хотите использовать в качестве основы для линии
    
    тренда
  4. Настройте линию тренда в нужном вам направлении.
  5. Используйте переключатель, чтобы показать или скрыть линию тренда.
  Важно: Начиная с Excel 2005 г., Excel скорректировал способ расчета значения R2 для линейных линий тренда на диаграммах, для которых для перехватить линию тренда установлено значение 0 (0). Эта корректировка исправит вычисления, которые дают неправильные значения R 2, и выровняет вычисление R2 с функцией LINEST. В результате на диаграммах, созданных в предыдущих Excel, могут отображаться разные значения R2. Дополнительные сведения см. в таблице Изменения внутренних вычислений линейных линий тренда на диаграмме.
  
  Дополнительные сведения
  
  Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
  
  Работа с диаграммами и графиками в Excel
  
  Примеры создания и настройки графиков или диаграмм для визуализации данных отчетов. Возможность создания инфо-графики стандартными средствами с примерами на картинках и описанием.
  
  Создание и настройка диаграмм
  
  Как построить график функции в Excel.
  Быстрый способ правильного построения графика математической линейной функции. Особенности выбора графиков для графического представления разного типа данных.
  
  Автоматическое создание графиков и диаграмм в Excel.
  Примеры быстрого автоматизированного создания диаграмм и графиков для таблиц с данными. Особенности автоматического построения графиков и использование шаблонов по умолчанию.
  
  Точки пересечения графиков в Excel.
  Как найти точные координаты точки их пересечения. Если значения рассчитаны с помощью формул, то для нахождения точки пересечения можно решить уравнения.
  
  Процентная диаграмма в Excel инструкция по созданию.
  Как построить процентную диаграмму: круговую и столбчатую (гистограмму). Пошаговое руководство с картинками. Процентное соотношение на разных типах диаграмм.
  
  Диаграмма рассеяния в Excel и сферы ее применения.
  Как построить диаграмму рассеяния: пошаговая инструкция с картинками. Учимся использовать диаграмму разброса в целях анализа двумерных данных, делать по ней выводы.
  
  Сравнительная диаграмма в Excel и примеры ее особенностей построения.
  Как построить сравнительную диаграмму. Анализ диаграмм на примере сравнительных показателей экономического развития. Построение сравнительных лепестковых диаграмм.
  
  Как делать объемные диаграммы разных типов в Excel.
  Объемные диаграммы их трехмерные форматы для отображения разных типов данных. Построим объемную круговую диаграмму, гистограмму, поверхностную, линейчатую.
  
  Комбинированные диаграммы в Excel и способы их построения.
  Как можно комбинировать разные типы диаграмм: способы создания смешанных диаграмм средствами офисной программы. Пошаговая инструкция построения с картинками.
  
  Диаграмма с двумя осями в Excel скачать пример.
  Как построить диаграмму с двумя вертикальными и с двумя горизонтальными осями: пошаговое руководство с картинками к добавлению и удалению вспомогательных осей.
  
  Как использовать формулы в диаграммах Excel: примеры.
  Примеры использования формул в диаграммах: аргументы функции РЯД, применение именованных диапазонов вместо ссылок на ряд данных при построении графиков. Условное форматирование.
  - Excel Formula Examples
  - Создать таблицу
  - Форматирование
  - Функции Excel
  - Формулы и диапазоны
  - Фильтр и сортировка
  - Диаграммы и графики
  - Сводные таблицы
  - Печать документов
  - Базы данных и XML
  - Возможности Excel
  - Настройки параметры
  - Уроки Excel
  - Макросы VBA
  - Скачать примеры
  Похожие публикации: