График по измерениям и метод наименьших квадратов: Excel и Python

Как оформить график для отчёта, провести прямую методом наименьших квадратов, найти наклон и его погрешность вручную, в Excel и в Python.

В лабораторной почти всегда нужен график: напряжение от тока, удлинение от массы, путь от времени. А по графику нужно найти наклон прямой и его погрешность, потому что в наклоне и сидит искомая величина. Отчёт часто возвращают из-за графика, а не из-за расчётов: точки соединены ломаной, оси без единиц, прямая проведена на глаз. Ниже правила оформления и метод наименьших квадратов на одном примере: вручную, в Excel и в Python.

Как должен выглядеть график в отчёте

  • Подписи осей с единицами: обозначение величины и единица через запятую, например «I, мА» и «U, В».
  • Удобный масштаб. Деления круглые (0, 10, 20…), точки занимают большую часть поля. Начинать ось с нуля не обязательно, если только по графику не нужно найти точку пересечения с осью.
  • Точки маркерами, без ломаной. Между измерениями данных нет, поэтому соединять точки отрезками нельзя. Через точки проводят одну гладкую линию, которую даёт теория: прямую, параболу, экспоненту.
  • Прямая по расчёту. Если приложить линейку к первой и последней точке, прямая проигнорирует все остальные измерения. Прямую строят по методу наименьших квадратов.
  • Погрешности. Если они известны, на точках рисуют отрезки погрешностей.
  • Подпись рисунка с номером и названием зависимости, как требует методичка.

Пример: закон Ома для резистора

Через резистор пропускали ток и меряли напряжение. По закону Ома U = R·I, значит, наклон прямой U(I) равен сопротивлению.

I, мА 10 20 30 40 50 60
U, В 0,48 0,93 1,42 1,90 2,33 2,85

Метод наименьших квадратов вручную

Ищем прямую y = a + b·x, у которой сумма квадратов отклонений точек от прямой минимальна. Минимум даёт формулы:

b = (n·Σxy - Σx·Σy) / (n·Σx² - (Σx)²)
a = (Σy - b·Σx) / n

Посчитаем суммы для наших данных (x это ток в мА, y напряжение в В, n = 6):

Σx  = 210
Σy  = 9,91
Σxy = 10·0,48 + 20·0,93 + 30·1,42 + 40·1,90 + 50·2,33 + 60·2,85 = 429,5
Σx² = 100 + 400 + 900 + 1600 + 2500 + 3600 = 9100

Подставляем:

b = (6·429,5 - 210·9,91) / (6·9100 - 210²) = (2577 - 2081,1) / (54600 - 44100) = 495,9 / 10500 = 0,04723 В/мА
a = (9,91 - 0,04723·210) / 6 = (9,91 - 9,918) / 6 = -0,0013 В

Следите за единицами наклона. В/мА это кОм, поэтому 0,04723 В/мА = 47,23 Ом. Перевод забывают часто, и в отчёте появляется «R = 0,047 Ом».

Ещё одна ловушка: не округляйте b перед расчётом a. С b = 0,047 получится a = +0,0067 В, совсем другое число.

Погрешность наклона

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

Войлочные бусины на натянутой нити выстроились почти по прямой, рядом бумажная линейка и карандаш

Сначала считаем остатки, то есть отклонения точек от прямой: y − (a + b·x). У нас это 0,009; −0,013; 0,004; 0,012; −0,030; 0,018 В. Дальше:

Σ(остаток²)  = 0,001642 В²
x_ср         = 35 мА
Σ(x - x_ср)² = 1750 мА²

S_b = √( Σ(остаток²) / ((n - 2)·Σ(x - x_ср)²) ) = √( 0,001642 / (4·1750) ) = 0,000484 В/мА

В знаменателе n − 2, потому что по тем же точкам мы нашли два параметра, a и b. Коэффициент Стьюдента берут тоже для n − 2 = 4 степеней свободы: при P = 0,95 он равен 2,78.

Δb = 2,78 · 0,000484 = 0,00134 В/мА = 1,3 Ом
R  = (47,2 ± 1,3) Ом,  P = 0,95

Так же считается погрешность свободного члена: a = (−0,001 ± 0,052) В. Ноль внутри этого интервала, значит, прямая проходит через начало координат, как и требует закон Ома. Если бы a заметно отличался от нуля, стоило бы проверить нулевое показание прибора.

Если методичка требует прямую строго через ноль (y = b·x), формула другая: b = Σxy / Σx² = 429,5 / 9100 = 0,04720 В/мА.

Прямая и наклон в Excel

Тип диаграммы: точечная, только маркеры. Не берите «График»: он расставляет значения X через равные промежутки как подписи, и при неравномерном шаге картина исказится.

  1. Выделите оба столбца с данными, вкладка «Вставка», точечная диаграмма.
  2. Кнопка «Элементы диаграммы» рядом с диаграммой, флажок «Линия тренда». Тип линейный.
  3. Правый щелчок по линии тренда, «Формат линии тренда». Внизу панели включите показ уравнения и R² на диаграмме.
  4. Для отрезков погрешностей там же отметьте «Планки погрешностей», затем «Дополнительные параметры», «Пользовательская» и «Указать значение».

Уравнение на диаграмме удобно для глаза, но для отчёта нужны числа и погрешность. Их дают функции. Пусть ток в A2:A7, напряжение в B2:B7:

=НАКЛОН(B2:B7;A2:A7)                              → 0,04723
=ОТРЕЗОК(B2:B7;A2:A7)                             → -0,00133
=ИНДЕКС(ЛИНЕЙН(B2:B7;A2:A7;ИСТИНА;ИСТИНА);2;1)    → 0,000484  (S_b)
=СТЬЮДЕНТ.ОБР.2Х(0,05;4)                          → 2,776

В английской версии:

=SLOPE(B2:B7,A2:A7)
=INTERCEPT(B2:B7,A2:A7)
=INDEX(LINEST(B2:B7,A2:A7,TRUE,TRUE),2,1)
=T.INV.2T(0.05,4)

Обратите внимание на порядок аргументов: сначала Y, потом X. Функция ЛИНЕЙН (LINEST) с последним аргументом ИСТИНА возвращает таблицу из пяти строк и двух столбцов: в первой строке b и a, во второй их стандартные ошибки, в третьей R² (справка Microsoft). В Excel 365 её можно ввести в одну ячейку, результат сам займёт соседние.

То же в Python

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

import numpy as np
import matplotlib.pyplot as plt
from scipy import stats

I = np.array([10, 20, 30, 40, 50, 60])                 # ток, мА
U = np.array([0.48, 0.93, 1.42, 1.90, 2.33, 2.85])     # напряжение, В

res = stats.linregress(I, U)
t = stats.t.ppf(0.975, len(I) - 2)                     # 2.776
print(f"b = {res.slope:.5f} В/мА, S_b = {res.stderr:.6f} В/мА")
print(f"R = ({res.slope*1000:.1f} ± {t*res.stderr*1000:.1f}) Ом")

plt.errorbar(I, U, yerr=0.015, fmt="o", capsize=3, label="измерения")
x = np.linspace(0, 65, 100)
plt.plot(x, res.intercept + res.slope * x, label="МНК: U = a + bI")
plt.xlabel("I, мА")
plt.ylabel("U, В")
plt.grid(True)
plt.legend()
plt.savefig("ohm.png", dpi=200)

Программа напечатает R = (47.2 ± 1.3) Ом, как и ручной расчёт. Отрезки погрешностей здесь 0,015 В: это вольтметр класса 0,5 на пределе 3 В. Параметр fmt="o" рисует только маркеры, без соединительной линии.

Вместо linregress можно взять np.polyfit(I, U, 1, cov=True). Он возвращает коэффициенты от старшей степени к младшей, то есть сначала b, потом a, а корень из первого диагонального элемента матрицы ковариаций равен S_b. Порядок коэффициентов путают часто, проверяйте по смыслу: наклон у нас около 0,047.

Где чаще всего ошибаются

  • Точки соединены ломаной или прямая проведена через крайние точки.
  • В Excel выбран «График» вместо точечной диаграммы.
  • Наклон записан без перевода единиц: В/мА вместо Ом.
  • R² = 0,9996 подан как погрешность. R² показывает, насколько точки похожи на прямую, но погрешность наклона из него не прочитать.
  • Коэффициент Стьюдента взят для n − 1, а не для n − 2.
  • Оси без единиц, точки сжаты в углу поля.

Как это решает Tilpi

В чате задания Tilpi можно приложить таблицу измерений и методичку: он объяснит расчёт наклона по шагам, напишет и объяснит код для графика. Исполнитель запустит этот код в отдельной машине, где уже стоят Python, numpy, scipy и matplotlib, и вернёт график и числа в задание. Без тарифа исполнитель работает на уровне «Быстрая» из бесплатных ежемесячных кредитов, общих с чатом; нужна подтверждённая почта.

ИИ для учёбы. По вашим лекциям.

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

Построить график в Tilpi