У Excel є потворна помилка підрахунку. Здається, проблема входить глибоко в механізм обчислення Excel, і її буде нелегко виправити.
В основі проблеми лежить простий факт: Excel зберігає 15 цифр точності в одній комірці. Ви можете мати числа, що мають 20 цифр, але будь-які цифри між останньою значущою цифрою та десятковою комою повинні бути нульовими.
Нещодавно я бачив два випадки, коли механізм обчислення Excel повертав неправильні результати. Коли я розібрався з проблемою і подивився на базовий XML, я був здивований, побачивши, що Excel таємно зберігає 17 цифр у XML.
Проблема в тому, що Excel відображатиме лише 15 цифр. Отже, ви думаєте, що у вас є номер, який зберігається як 0.123456789012345, але він насправді зберігається як 0.12345678901234567.
Ви не можете побачити ці дві останні цифри. І більшість функцій Excel ігнорують ці дві останні цифри. Якби * всі * функції ігнорували дві останні цифри, у нас не було б проблем. Але поки що для сортування, RANK та FREQUENCY використовуються всі 17 цифр.
Нижче наведено добре відомий фокус для ранжирування комірок. Якщо вам потрібно, щоб кожен ранг з’явився рівно один раз, ви можете поєднати RANK і COUNTIF. На зображенні нижче Клер, Фло, Івана та Люсі мають 115%. Використовуючи формулу RANK + COUNTIF, їх слід віднести до 5, 6, 7 та 8.
Але формула не вдається. Два ряди оцінюються як 7. Це ніколи не трапляється. Чотири формули у стовпці D переконують, що 115% у B6, B9, B12 та B15 однакові. =B6=B15Формула повідомляє , що обидві клітини містять одні і ті ж дані.
Коли я намагався виявити проблему, подивіться лише на функцію RANK. Він повинен повідомляти про чотиристоронній зв’язок на 4-му для людей із 115%. Але якимось чином Люсі з 15-го ряду посідає перше місце серед трьох інших.
Щоб зрозуміти це, я надіслав запит про допомогу іншим MVP Excel. Ян Карел Пітерсе відкрив файл Excel і заглянув у XML. У XML ви можете бачити, що вони зберігають 17 цифр точності. Чотири комірки, які в Excel схожі на краватку, у XML не пов’язані. Один із 115% зберігається як 1.1500000000000001, а інші - 1.1499999999999999.
Поки що для сортування, ранжування та функції ЧАСТОТА використовуються зайві цифри. Чому це проблема? Оскільки ми розраховуємо на те, що RANK та COUNTIF використовуватимуть однакову кількість цифр. З однією функцією, яка використовує 15 цифр, а іншою - 17 цифр, у вас проблема.
На даний момент рішення, здається, перетворює всі ваші відповіді за допомогою =ROUND(A4,15).
Кожної п’ятниці я вивчаю помилку чи іншу рибну поведінку в Excel. Цю помилку підрахунку важко виявити, і вона кваліфікується як велика риба.
Думка дня в Excel
Я попросив своїх друзів Excel Master порадити їх щодо Excel. Сьогоднішня думка поміркувати:
"Кожного разу, коли ви зливаєте клітини, ви вбиваєте кошеня"
Сілвія Джухас
