Как аппроксимировать график в Excel

Обновлено: 03.07.2024

Хотите рассчитать площадь под построенной кривой в таблицах Excel, чтобы получить ценную информацию? Вот как вы можете это сделать.

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

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

Как рассчитать площадь под участком в Excel

Понятно, что площадь под графиком примерно равна оси x, умноженной на ось y. Однако в Excel нет функции, вычисляющей площадь под участком.

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

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

В качестве примера предположим, что у нас есть скорость автомобиля в определенное время. Если вы создадите диаграмму для этих данных, область под диаграммой покажет расстояние, пройденное автомобилем. Это потому, что по оси X отсчитываются секунды, а по оси Y — метры в секунду. Если вы умножите эти два числа вместе, вы получите метры или пройденное расстояние. Начнем с создания корзины.

1. Создание диаграммы

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

Выберите серию данных. В этом примере это будет скорость автомобиля, поэтому мы выберем ячейки с B2 по B9.
Перейдите в меню "Вставка" и нажмите на символ линейной диаграммы в разделе "Диаграммы".
Выберите 2D-область. Теперь у вас есть диаграмма, но ось X не совсем правильная, поскольку она не соответствует нашей временной шкале.
Щелкните правой кнопкой мыши диаграмму и выберите "Выбрать данные".
В разделе "Ярлыки горизонтальной оси" нажмите "Изменить".
Выберите категории в электронной таблице. В данном примере это будут ячейки с A2 по A9.
Нажмите "ОК". Теперь ваша ось X начинается с нуля.

Если вы совсем новичок в создании диаграмм в Excel, вы можете прочитать наше руководство о том, как создать диаграмму в Excel, чтобы быстро начать работу.

2. Настройка диаграммы

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

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

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

Выберите диаграмму.
Дважды щелкните ряд данных. Откроется панель «Формат ряда данных» справа.
В разделе "Формат ряда данных" в разделе "Параметры ряда" выберите "Заполнить и обвести".
Измените заливку с автоматической на сплошную.
Установите прозрачность на 30%. Это позволит вам увидеть линии сетки.

Наконец, чтобы увидеть высоту каждого входа, вы можете добавить на диаграмму метки данных.

Выберите диаграмму.
Перейдите на вкладку "Дизайн диаграммы".
Нажмите "Добавить элемент диаграммы", а затем перейдите в раздел "Ярлыки данных".
Выберите Показать.

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

3. Вычисление площади

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

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

Выберите ячейку, в которой вы хотите отобразить область для первого сегмента.В этом примере это также будет пройденное расстояние, поэтому для этой цели мы будем использовать столбец C.
Для первого сегмента введите 0. Это потому, что площадь в нулевое время также равна нулю. Теперь посчитаем площадь следующего сегмента.
Выберите ячейку ниже и введите приведенную ниже формулу в строку формул: Этот сегмент представляет собой прямоугольник. Площадь треугольника равна высоте, умноженной на основание, а затем деленной на два. Высота этого треугольника находится на оси X и равна A3-A2. Высота этого треугольника находится на оси Y, и это B3. После того как формула вычислит площадь, она суммирует ее с предыдущей ячейкой, чтобы получить общую площадь.
Выберите ячейку ниже и введите следующую формулу: На этот раз сегмент представляет собой трапецию. Площадь трапеции равна сумме двух оснований, умноженной на высоту, а затем разделенной на два. Основания B3 и B4, высота A4-A3. Опять же, когда формула имеет площадь сегмента, она суммирует ее с предыдущей ячейкой, чтобы получить общую площадь на данный момент.
Возьмите маркер заполнения и поместите его на ячейки ниже. Это автоматически рассчитает площадь для ячеек ниже, так как все они являются трапециями.

Если вас интересует прямоугольник, площадь прямоугольника также можно рассчитать по формуле площади трапеции. Это потому, что площадь прямоугольника — это просто произведение длины на ширину.

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

Рассчитать площадь под участком в Excel

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

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

Линия тренда применяется для иллюстрации тенденций изменения цен. Элементом технического анализа является геометрическое представление средних значений анализируемого показателя.

Вот как добавить линию тренда на диаграмму в Excel.

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

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

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

Если R2 = 1, ошибка аппроксимации равна нулю. В нашем примере выбор линейной аппроксимации дал низкую точность и плохой результат. Прогноз будет неточным.

Примечание. Линию тренда нельзя добавить к следующим типам диаграмм и графиков:

радар;
круговой;
поверхность;
пончик;
стереограмма;
сложены.

Уравнение линии тренда в Excel

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

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

Линейная аппроксимация

Его геометрическое представление — прямая линия. Следовательно, линейная аппроксимация используется для иллюстрации индикатора, который увеличивается или уменьшается с постоянной скоростью.

Рассмотрим условное количество договоров, заключенных менеджером на 10 месяцев:

На основе данных электронной таблицы Excel составьте точечную диаграмму (это поможет проиллюстрировать линейный тип):

Получаем результат:

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

y = 4 503 x + 6 1333

где 4,503 – показатель уклона;
6,1333 – смещение;
y - последовательность значений,
x - номер периода.

Прямая линия на графике показывает неуклонный рост качества работы менеджера. Значение R-квадрата равно 0,9929, что свидетельствует о хорошем согласии расчетной прямой с исходными данными. Прогнозы должны быть точными.

Чтобы спрогнозировать количество заключенных договоров, например, в периоде 11, необходимо вместо x в уравнении поставить число 11. После подсчета выясняем, что в 11 периоде менеджер заключит 55-56 договоров.

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

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

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

Уравнение выглядит следующим образом:

7,6403 и -0,084 – константы;
e – основание натурального логарифма.

Показатель R-квадрата равен 0,938, что означает, что кривая соответствует данным, ошибка минимальна, и прогнозы будут точными.

Логарифмическая линия тренда и прогноз продаж

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

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

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

R2 по значению близок к 1 (0,9558), что указывает на минимальную ошибку аппроксимации. Спрогнозируем объем продаж на последующие периоды. Для этого вместо x в уравнении подставьте номер периода.

Для расчета прогнозных значений используется формула: =304,52*LN(A18)+101,57, где A18 – номер периода.

Полиномиальная линия тренда в Excel

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

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

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

Рисунок 1. Окончательный результат: Аппроксимация кривой

Как подогнать кривую

Чтобы подогнать кривую под наши данные, мы выполняем следующие шаги:

Выберите данные для нашего графика, B2:C17, который представляет собой табличный результат взаимосвязи между температурой и объемом.

Рисунок 2. Пример данных для аппроксимации кривой

Рисунок 3. Вариант точечной диаграммы

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

Рисунок 4. Добавление параметров линии тренда

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

Рис. 5. Диалоговое окно "Формат линии тренда"

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

При подборе кривой мы хотим, чтобы значение R-квадрата было как можно ближе к значению 1. На изображении ниже показан наш точечный график с полиномиальной линией тренда порядка 2. Значение R-квадрата равно «0,9759».

Рисунок 6. Вывод: Как подогнать кривую

Настроить наиболее подходящую кривую

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

Рисунок 7. Вывод: настройка кривая наилучшего соответствия

Примечание.

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

Рисунок 8. Подбор линейной кривой

Мгновенное подключение к эксперту Excel

В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

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

Несмотря на то, что концепция площади под кривой довольно проста — она заключается в вычислении общей площади, покрытой кривой/линиию и осью, — в Excel нет прямого способа вычислить это.

Но это не так уж и сложно!

В этом кратком руководстве я покажу вам два способа расчета площади под кривой в Excel.

Итак, приступим!

Это руководство охватывает:

Формула для расчета площади под кривой в Excel

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

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

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

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

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

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

Ниже приведена формула для расчета площади трапеции

a – базовая длина одной стороны
b — базовая длина другой стороны.
h — высота

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

Примените приведенную выше формулу ко всем ячейкам в столбце (кроме последней).

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

Для этого я могу использовать простую формулу СУММ.

Вот как я могу рассчитать общую площадь под кривой для простой линейной диаграммы.

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

Использование уравнения линии тренда для площади под кривой

Позвольте мне показать вам еще один метод расчета площади под кривой (AUC) для диаграммы в Excel.

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

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

Ниже приведены шаги для получения уравнения линии тренда для нашего набора данных:

Выберите диаграмму.
На вкладке "Дизайн диаграммы" выберите параметр "Добавить элемент диаграммы".

Наведите курсор на параметр «Линия тренда» и в появившихся параметрах нажмите «Дополнительные параметры линии тренда». Откроется панель «Формат линии тренда».

Отметьте параметр «Отображать уравнение на диаграмме». Это покажет полиномиальное уравнение линии тренда на графике.

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

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

В моем случае это уравнение

Таким образом, определенный интеграл этого уравнения будет

Хотя я не являюсь экспертом в области исчисления, исходя из моего ограниченного понимания, вы можете преобразовать уравнение в определенный интеграл, увеличив степень x на 1 и погрузив его в ту же степень значения. Например, x 2 станет x 3 /3, а x станет x 2 /2, а любая константа (например, 1,85) станет 1,85x

Теперь, когда у меня есть уравнение, я могу вычислить площадь под кривой, найдя значение f(10)-f(1)

В нашем примере у меня есть уравнение – f(x) = (1,0038/3)*x 3 + (2,1826/2)*x 2 – 1,85x + c

Таким образом, F(1) можно рассчитать по следующей формуле:

и f(10) можно рассчитать по следующей формуле:

Чтобы получить площадь под кривой, нам нужно найти разницу между этими двумя значениями [f(10) – f(1)]

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

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

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

Читайте также: