Деякі помилки в роботі Microsoft Excel

Анотація

Табличний процесор Microsoft Excel є масовим і могутнім засобом для підтримки розрахунків практично в будь якій сфері життя. Разом з тим необхідно застерегти від сприйняття отриманих за допомогою нього результатів як істини в останній інстанції. Мова йде не тільки про помилки і недоліки в функціях статистичної обробки в Microsoft Excel, які викликані недостатньою обізнаністю зі статистичними методами тих, хто ці функції створював, що приводить до неправильного застосування методів математичної статистики, і не про не врахування нестійкості деяких процедур чисельних методів (розв’язання систем лінійних рівнянь, обернення матриця), результатом чого є отримання невірних результатів як наслідок цієї нестійкості. Мова також йде про програмні помилки, походження яких поясненню стороннім спостерігачем не піддається і які через те є особливо небезпечними.

В роботі приводиться приклад задачі по побудові регресійної моделі, яка засобами Microsoft Excel несподівано розв’язується абсолютно неправильно.

Використовується матриця повного факторного експерименту (ПФЕ) 3¹×4¹//12, яка є ідеальним варіантом з точки зору теорії планування експериментів за усіма показниками, в тому числі обчислювальна стійкість і структурна стійкість ідеальна.

         Отримана модель як за допомогою надбудови  «Анализ данных», так і використовуючи функцію =ЛИНЕЙН($K3:$K14;C3:C14) має чотири нульових регресійних  коефіцієнта з дев’яти, що не відповідає розрахункам іншими програмними засобами. При цьому статистичні характеристики моделі співпадають з іншими розрахунками. Оскільки в нас матриця ортогональна, то ми можемо виконати обчислення, використовуючи вбудовані формули для роботи з масивами без небезпеки отриманні невірних результатів. Коефіцієнти регресії в такому випадку визначаються за формулою B = (X^TX)^-1X^TY.

Цей результат співпадає з тестовим і не співпадає з отриманим за спеціалізованими статистичними функціями і надбудовою табличного процесора.

Отже:

1. В статистичних функціях і надбудовах Microsoft Excel існують помилки, в тому числі і такі, що роблять результати непридатними для використання.

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

3. Рекомендація перераховувати іншими засобами рівноцінна відмові від Microsoft Excel, або збільшенні зусиль в кілька разів і втраті зручності, що є неприйнятим.

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