Статьи

Графік з похибками в Excel

  1. Як же бути?
  2. Якими бувають похибки
  3. Коли це буває?
  4. Як побудувати плавний графік в Excel з урахуванням похибок?
  5. Наносимо лінії похибок

Як побудувати графік в Excel з урахуванням похибок? Подібне завдання нерідко виникає у студента при обробці результатів лабораторних робіт. Результати представляють собою, як правило, два масиви даних (в загальному випадку Х і Y). Нехай, для прикладу, є такий експериментальна залежність:

Х 2,0 4,0 5,0 7,0 9,0 11,0 15,0 У 3,2 4,2 9,0 14,8 23,0 15,2 12,8

Вас можуть запитати цю залежність Y від Х графічно. Вийде приблизно те, що представлено на малюнку.

Як побудувати графік в Excel з урахуванням похибок

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

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

Чому? Адже, начебто, нічого складного немає. Натискаємо в Excel кнопку «Майстер діаграм», Вибираємо тип діаграми - точкова. Потім натискаємо «Далі», задаємо масиви для X і Y, потім знову «Далі» ... - і незабаром вийде те, що наведено на попередньому малюнку.

Начебто, все правильно. Та тільки графік побудований строго по точкам. Видно, що залежність немонотонна, з досить гострим максимумом - що не завжди має місце в реальності. Бо в реальності переважна більшість залежностей можуть бути більш плавними.

Звичайно, тут треба дивитися, які конкретно показники аналізуються. Якщо це, наприклад, динаміка біржового курсу цінних паперів; або - динаміка надходження платежів від покупців, так само як і інший аналогічний економічний показник, то, звичайно, можна з упевненістю сказати, що графік зовсім не зобов'язаний бути плавним. Справді, дуже багато економічні показники змінюються, як правило, стрибкоподібно.

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

Але, начебто, і тут немає жодних проблем: слід провести лінію тренда, яких Excel пропонує кілька типів. Так, можна вибрати лінійний, степеневої, експоненціальне і т.д. тренд.

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


Начебто, то, що проведено чорною лінією (тренд), вже набагато ближче до істини. Правда, зовсім ненабагато. Фактично, проведений тренд досить добре відповідає вихідним даним тільки для 3, 4 і 7-й точок. Для інших точок є істотні розбіжності, причому помилка доходить до 80%.

Ясно, що побудований тренд, в силу його високої похибки, в даному випадку ніяк не можна прийняти в якості якісного графіка, що відображає хід залежності, виявленої експериментально. Однак, і спочатку побудовану (синю) лінію, ймовірно, також не можна прийняти в якості такого, бо, повторимося, вона взагалі не враховує похибок.

Як же бути?

Звичайно, є можливість провести інтерполяцію і вже з її урахуванням визначити функцію, яка буде найбільш плавної (з урахуванням похибок) і побудувати її в якості графіка. Однак, це - завдання досить складна, що представляє собою предмет окремої розмови. Скажімо, в тому ж MatLab, звичайно, існують подібні функції, за допомогою яких можна це реалізувати.

Однак, тоді, в будь-якому випадку, доведеться писати програму (в MatLab це буде простіше, в Excel - складніше, бо там доведеться програмувати вручну готові функції, які вже є в MatLab). А для студента, якому потрібно обробити дані лабораторної або (рідше) контрольної роботи, це може бути складною, окремим завданням. Справді, замість аналізу результатів він змушений спочатку думати, яким чином побудувати ці результати, а потім програмувати. Добро, якщо лабораторна робота проходить на старшому курсі. Однак, найчастіше, студент робить їх, починаючи з самого початку свого навчання, тобто коли досвіду математичної обробки, не кажучи вже про вміння будувати інтерполяційні многочлени, немає взагалі.

Якими бувають похибки

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

Треба сказати, що старі версії програми Excel не дозволяють просто так нанести похибка на графік. А ось, починаючи, принаймні, з версії Excel 2007, це можна зробити досить просто. Ось приклад, як наносяться похибки в Excel .

Так, все начебто, чудово. Але тільки графік, наведений на сайті (за вказаним URL), також побудований, строго кажучи, невірно. Справа в тому, слід повторити, що графік повинен являти собою, по можливості, плавну лінію. Яка десь пройде через середину інтервалу похибки, а десь, можливо, через один з його країв. Так ось, стандартні засоби Excel навіть найновіших версій не дозволяють побудувати такий графік автоматично. Тим більше, якщо йдеться про те, що кожна експериментальна точка може мати, взагалі кажучи, різну похибка.

Коли це буває?

Наприклад, в разі, коли різні експериментальні точки були отримані на базі досліджень, проведених різними методами.

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

  1. Якщо частота дорівнює нулю (тобто відбувається рівномірна деформація матеріалу), то необхідно застосовувати установки для розтягування / стиснення, здатні виміряти роботу, витрачену на нагрівання матеріалу в процесі деформації.
  2. Якщо говорити про частоту, коли її значення лежать в межах 10 Гц ... 1000 Гц, то такі вимірювання проводяться за допомогою зовсім інших установок, наприклад, з використанням так званих крутильних маятників (тобто коли зразок здійснює вимушені крутильні коливання заданої частоти).
  3. Якщо вести мову про діапазоні частот 20 кГц ... 200 кГц то тут необхідно застосовувати ультразвукові установки.
  4. Нарешті, дослідження при гіперзвукових частотах (більше 10 9 Гц) проводяться за допомогою оптичних, п'єзоелектричних методів.

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

Як побудувати плавний графік в Excel з урахуванням похибок?

Отже, як побудувати нормальний, правильний графік? Який, з одного боку, був би, по можливості, плавним (тобто містив би поменше нестабільних, немонотонність ділянок - в межах похибки, звичайно), а з іншого - лежав би в рамках допустимих похибок.

Розглянемо найскладніший, загальний випадок - різних похибок для кожної з експериментальних точок і покажемо, як можна застосувати Excel для того, щоб побудувати коректний графік залежності.

Нехай похибки експериментальних точок рівні наступних значень:

± 10%; ± 7%; ± 5%; ± 12%; ± 20%; ± 17%; ± 23%.

Ясно, що як відносна, так і абсолютна похибки даних будуть різними для кожної з експериментальних точок. Однак, для кожної з них можна визначити мінімальне і максимальне значення (через інтервал яких і повинен пройти графік). Проведемо такий розрахунок (благо, за допомогою Excel це зробити дуже легко і швидко):

Х 2,0 4,0 5,0 7,0 9,0 11,0 15,0 Y 3,2 4,2 9,0 14,8 23,0 15,2 12,8 Відносна похибка (для Y) ,% 10 7 5 12 20 17 23 Мінімальне значення Y 2,9 3,9 8,6 13,0 18,4 12,6 9,9 Максимальне значення Y 3,5 4,5 9,5 16,5 27, 6 17,8 15,8

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

Відкладемо цей діапазон на малюнку

Нижня межа діапазону показана зеленою лінією, верхня - чорною. Товста чорна лінія - це раніше нанесені тренд. Чітко видно, що тренд (навіть, повторимося, оптимальний, обраний з пропонованого програмою Excel переліку) тільки в двох (з семи) експериментальних точках проходить в межах допустимої області. Отже, необхідно відмовитися від нього. Видалимо його з малюнка.

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

Його побудова (якщо розрахунок оптимального интерполяционного многочлена викликає складність) простіше провести вручну. Тобто роздрукувати на папері отриманий малюнок і вже на ньому нанести графік. Але, цілком можливо це зробити і за допомогою комп'ютера - щоб взагалі не возитися з засобами для писання, причому зробити це можна дуже швидко.

Для цього найзручніше використовувати програму Inkscape. Вона є абсолютно безкоштовною, якщо у Вас вона ще не встановлена, можете завантажити її з офіційного сервера. https://inkscape.org/ru/download/

Встановлюємо її, потім відкриваємо. Копіюємо малюнок (відзначимо, краще це зробити не з Word, а з першоджерела, тобто з Excel).

Потім, як завжди, натискаємо кнопку «Вставити», розміщуємо малюнок приблизно по центру робочої області. Якщо її розміри не співпадають з малюнком, потрібно її налаштувати, для чого натискаємо «Файл», «Властивості документа» і там встановлюємо режим пейзажу і вказуємо необхідні розміри (параметри «Ширина» та «Висота»). Приблизно ось що повинно вийти в результаті.


Так як Excel створює діаграми в векторному вигляді, вони без проблем редагуються в Incscape. Таким чином, наша задача - вручну таким чином виправити синю лінію, щоб вона стала якомога більш плавною.

Натиснувши клавішу F2, потім, утримуючи «Shift», натискаємо мишкою за елементами синьої кривої так, щоб на ній з'явилися сірі вузли. Коли вони з'являться на кожному з її ділянок, це означає, що ми виділили її ВРЮ в режимі редагування вузлів.

Потім натискаємо в меню пункт «Контур», «Спростити». Число вузлів значно знизиться, залишаться лише основні. До речі, якщо клікнути по кожному вузлу окремо, його можна буде стерти шляхом натискання клавіші «Delete». Але, це буде досить довго, тому простіше використовувати команду "Спростити".

Потім, утримуючи ліву кнопку миші на відповідному вузлі, рухаємо його в ту чи іншу сторону. Так повторюємо з іншими вузлами (при необхідності скорегувати кривизну лінії в конкретному вузлі, можна також рухати важелі кожного з них).

В результаті лінія приймає вигляд, показаний на малюнку.

В результаті лінія приймає вигляд, показаний на малюнку

Для наочності (щоб можна було пам'ятати, де проходила лінія графіка, побудована в Excel), експериментальні точки, у вигляді синіх ромбів, залишені.

Вставити малюнок з Incscape в Word досить просто - як завжди: натискаємо «Правка», «Виділити все» (як уже говорилося, робоча область повинна бути підігнана до розмірів малюнка; втім, можна і малюнок, шляхом його деформацій, підігнати до розмірів робочої області програми Inkscape), потім - «Скопіювати».

Перевіряємо, що малюнок виділився, як годиться. Тоді переходимо в Word і виконуємо вставку з буфера обміну, як зазвичай, шляхом натискання кнопки «Вставити» на панелі інструментів. Але, врахуйте, що, на жаль, виправити в Excel змінену діаграму вже не вийде (вона буде вставлено, як малюнок). Тому все, що необхідно зробити на діаграмі в Excel, треба робити заздалегідь, ДО ТОГО, тобто до перетворення її в Inkscape. Разом з тим, при бажанні, отриманий малюнок можна знову скопіювати в Inkscape провести, якщо потрібно, подальше його редагування.

Таким чином, ми отримали згладжений, більш плавний графік, ніж той, який був побудований автоматично в Excel. Навіть не взявши в руки олівець чи іншої пише інструмент: використовувалися лише деякі клавіші і миша.

Наносимо лінії похибок

Що ж стосується ліній похибок, то їх можна провести, використовуючи відповідні можливості Excel (якщо він у Вас, як мінімум, версії 2007 року). Правда, так як величина похибки в нашому прикладі для кожної з точок - різна, то можливості Excel з побудови похибок тут допоможуть мало.

Так що, в даному випадку, можна накреслити їх вручну (тобто намалювати мишкою відповідні вертикальні лінії, що проходять через кожну експериментальну точку).

Однак, це, на наш погляд, втомлює. Тому, якщо зовсім не хочеться возитися з ручним малюванням, вихід цілком є ​​- їх можна виконати, намалювавши відповідне (в даному випадку 7) додаткових графіків. Повторимося, це слід зробити ДО того, як перетворювати діаграму в Inkscape.

Отже, заходимо на вкладку «Діаграма». Натискаємо в панелі інструментів «Діаграма», «Вихідні дані». Вибираємо вкладку «Ряд», потім натискаємо «Додати».

Далі, тиснемо кнопку з червоною стрілкою - там, де значення Х. Переходимо на Лист, на якому знаходяться дані, за якими побудована ця діаграма і вказуємо, наприклад, четверте значення Х (рівне 7). Потім (увага!), Утримуючи клавішу «Ctrl», натискаємо знову мишкою по тому ж самому значенню Х (тобто по Х, що дорівнює 7). У підсумку в рядку, де задається адреса діапазону значень Х, адреса осередку, в якій розташована цифра 7, повинен бути присутнім ДВІЧІ, через крапку з комою. Переконавшись в цьому, натискаємо, як зазвичай, червону стрілку.

Тепер вибираємо значення Y. Після натискання червоної стрілочки, перейшовши на потрібний Лист, натискаємо на мінімальне значення Y, відповідне Х, рівному 7. Потім, утримуючи клавішу «Ctrl», натискаємо максимальне значення Y. В результаті, через крапку з комою в адресному рядку повинні з'явитися адреси двох осередків, відповідні мінімального і максимального значення Y (для Х = 7).

Нарешті, переходимо на діаграму, двічі натискаємо мишкою на отриманому вертикальному відрізку з маркерами, зайшовши, тим самим, в властивості щойно збудованого відрізка. Там встановлюємо вид маркера у вигляді знака мінус ( «-»). Потім встановлюємо, наприклад, чорні кольори ліній і маркера і натискаємо «ОК». Результат наших дій наведено на малюнку.

Результат наших дій наведено на малюнку

Бачимо, що для точки Х = 7 з'явилася лінія похибки. До речі, ширину цієї лінії можна, при бажанні, змінювати - шляхом зміни розміру маркера.

Поступаючи таким же чином з усіма іншими точками, можна незабаром побудувати лінії похибок для кожної з них (кому не хочеться виконувати цю нудну роботу, якщо точок багато, можна написати нескладний макрос - пишеться один раз, справедливість, деяким студентам, наприклад, учашихся на молодших курсах, це, можливо, буде важко, тому простіше буде зробити так, як говорилося вище). А лінії, що обмежують діапазони мінімальних і максимальних значень (зелену і чорну, відповідно) можна, до речі, потім видалити, якщо вони не потрібні. Тоді графік прийме повністю стандартний вид.

Його можна потім перенести в Inkscape і надати графіку (синя лінія) більш плавний вигляд.


З повагою до Вас.


Як же бути?
Як побудувати плавний графік в Excel з урахуванням похибок?
Чому?
Як же бути?
Коли це буває?
Як побудувати плавний графік в Excel з урахуванням похибок?
Отже, як побудувати нормальний, правильний графік?

Новости

Адрес:
пр. Пушкина 16

Телефоны клуба:
056-79-000-37
099-078-90-99
067-689-07-01
093-403-17-02

Режим работы:
ПН – ПТ: 9.00-21.00 СБ: 10.00-17.00

Следите за нами: