Made on
Tilda
Контрольный запрос на годовую сверку прямых затрат должен был вернуть 14 строк, все со статусом Matched. Он их и вернул. Но столбец Difference, который в теории должен был состоять из одних нулей, показал ещё три значения: -7,27...×10⁻¹¹, -4,55...×10⁻¹⁰, 9,09...×10⁻¹⁰. Экономически — расхождение отсутствует. Формально — в данных что-то не бьётся. Разбираться пришлось отдельно.
Разбирал Excel-модель управленческого учёта крупного ресурсоснабжающего предприятия. Книга: 18 листов, 103 447 заполненных ячеек, 78 344 формулы — при распечатке для разбора получилось 112 страниц. Ни одной оформленной Excel-таблицы, ни одного именованного диапазона: вся экономическая логика зашита в положении ячеек и скрытых строках. Задача — перенести модель в Power BI, сохранив управленческую логику, а не просто внешний вид листов. Публикую по ходу разбора реальные решения, тупики и проверки — без прикрас.
Один из обязательных этапов миграции — построчная сверка нормализованного результата с оригиналом: для каждого центра затрат сумма по атомарным фактам обязана совпасть с годовым итогом, который стоял в исходной книге. Без этого шага верить нормализованным данным нельзя — слишком легко потерять или задвоить строку при разворотах и объединении 14 разнородных блоков затрат в одну книгу без единой Excel Table.
Первая версия контрольного запроса сравнивала разницу с нулём напрямую — и на части строк проверка падала, хотя визуально суммы совпадали до копейки. Чтобы понять масштаб, отфильтровал столбец Difference по всем 714 сравниваемым парам «центр × статья» — и увидел не «шум», а ровно четыре повторяющихся значения:
| Значение Difference |
|---|
| 0 |
| -0,0000000000727595761418259 |
| -0,0000000000454747350886464 |
| 0,0000000000909494701772928 |
То есть на 714 сравнений — только три ненулевых остатка, и самый крупный по модулю — ≈9,09×10⁻¹⁰. Дальше стало понятно, откуда это берётся.
Причина — в самой природе чисел с плавающей точкой. Excel считает годовой итог по формулам одним путём, Power Query агрегирует детальные месячные значения другим (List.Sum по 12 строкам) — и при суммировании сотен операций с двоичным представлением десятичных дробей накапливается остаток порядка 10⁻¹⁰–10⁻¹¹. Это стандартное, документированное поведение чисел с плавающей точкой, знакомое каждому, кто хоть раз получал 0.1 + 0.2 = 0.30000000000000004 на любом языке программирования — просто здесь оно всплывает не в тестовом примере, а в реальной финансовой сверке.
Сравнивать Difference с нулём напрямую (= 0) — плохая идея по этой же причине: рано или поздно контроль начнёт ложно падать не потому, что данные неверны, а потому что так устроена арифметика. Округлять сами фактические суммы, чтобы остаток исчез — тоже неверный путь: это меняет точность исходных данных ради того, чтобы контроль формально прошёл, вместо того чтобы проверять реальность.
Вместо точного равенства — сравнение абсолютной разницы с явно заданным порогом, отдельно на уровне каждой из 714 пар «центр × статья» и затем повторно на уровне итога по каждому из 14 центров:
// уровень отдельной пары «центр × статья»
AddItemReconciliationStatus =
Table.AddColumn(
AddDifference,
"ItemReconciliationStatus",
each
if [CostCenterCode] = null then
"UnmappedControl"
else if [AnnualControlStatus] = "Error" then
"Error"
else if [FactAnnualAmount] = null then
"MissingFact"
else if [MonthRowCount] <> 12
or [DistinctMonthCount] <> 12 then
"InvalidMonthCount"
else if Number.Abs([Difference]) <= 0.000001 then
"Matched"
else
"Mismatch",
type text
),
// итог по центру затрат: 51 статья, 612 факт-строк, 0 несовпадений
AddReconciliationStatus =
Table.AddColumn(
AddDifferenceByCenter,
"ReconciliationStatus",
each
if [ItemCount] = 51
and [FactMonthRowCount] = 612
and [MatchedItemCount] = 51
and [MismatchItemCount] = 0
and Number.Abs([Difference]) <= 0.000001
and [MaxAbsoluteItemDifference] <= 0.000001 then
"Matched"
else
"Mismatch",
type text
),Порог 0,000001 выбран сознательно на несколько порядков больше максимального наблюдаемого остатка (≈9,09×10⁻¹⁰) — то есть с запасом почти в четыре порядка. Этого достаточно, чтобы гарантированно отсекать вычислительный шум, но при этом достаточно строго, чтобы поймать реальное расхождение, если оно появится, — например, пропущенную строку при следующем обновлении источника.
Итоговый запрос вернул 14 строк и 13 столбцов, как и ожидалось: ItemCount — 51 на каждый центр, FactMonthRowCount — 612, MatchedItemCount — 51, MismatchItemCount — 0, ReconciliationStatus — Matched во всех 14 строках. Ни одна фактическая сумма при этом не округлялась и не менялась — точность данных сохранена полностью, изменился только способ сравнения при контроле.
После этого случая правило простое: если сравниваются суммы после агрегации значений с плавающей точкой — по-хорошему после любого Table.Group, Unpivot или объединения из нескольких источников — проверка никогда не использует ровное =. Только допуск, заданный осознанно: на несколько порядков больше типичного вычислительного шума и на порядки меньше минимальной значимой единицы ваших данных. Иначе рано или поздно контроль начнёт падать не потому, что данные неверны, а потому что математика работает именно так.
Автор — к.э.н., аналитик и консультант по BI. Часть серии о миграции модели в Power BI.