Работа функции ВПР со значениями

Question

Добрый день.

Вопрос больше филосовский, но вдруг кто в курсе.

Имеется файл, выгруженный из сторонней программы. Например 1С.

В 2010 Excel проявляется такая вещь(случай не единичный и не у одного человека): функция ВПР(Vlookup) не находят значений, которые стопроцентно есть на листе. И в ячейках отображаются верно, и формат одинаковый(это числа). И самое главное - если приравнять ячейки(=A3=H3) - результат будет ИСТИНА(TRUE). Функция СОВПАД(EXACT) так же не видит различий.

Мной в ходе изучения данных файлов было выявлено, что значения в ячейках хоть и отображаются одинаково, все же имеют различные значения, если заглянуть "под капот" - в XML-схему документа.

К примеру:

в ячейке А3 значение: 41617.791666666664

В ячейке Н3: 41617.791666666672

Однако в ячейках они отображаются как 41617,7916666667. Это именно значение ячейки, не формат, не округление. Все бы ничего - но совершенно неясно, почему ВПР (Vlookup)  видит эти различия, в то время как более простые функции нет. Складывается впечатление, что ВПР (Vlookup) берет значения с исходной XML-схемы. Может кто в курсе так ли это? И есть ли иные функции, ведущие себя так же?

Сразу оговорюсь: варианты решения проблемы мне не нужны, я сам знаю несколько:

1. Применить функцию ОКРУГЛ
2. Выделить столбцы с данными, Данные-Текст по столбцам(выбираем с разделителем и жмем готово на первом же шаге).
3. Выполнить замену разделителя(точка или запятая). Менее эффективно.

Но это не означает, что если есть иное решение(не входящее в перечисленные выше), то его не надо оглашать.

Всем заранее спасибо за интерес к теме.

Answer 1

Ответ получил на одном из форумов(в общем-то на том же, где этот баг и был обнаружен). Ответ дал MVP Excel под ником ZVI:

Формула и значение ячейки в Excel при загрузке файла помещаются в память во внутреннюю структуру XLOPER.Отображение значения в ячейки округляется так, чтобы не отображать пугающих лишних десятичных знаков,но само значение остается без изменения, если только не производится пересчет формулы ячейки.Принудительный пересчет всех ячеек замедлил бы загрузку файлов. Вспомним, что Excel-ю уже больше четверти века, алгоритмы загрузки были расчитаны для слабых компьютеров и "деревянных" накопителей, включая дисководы. Для совместимости, чтобы данные при загрузке в разных версиях были одинаковы, такие алгоритмы не меняются, так как это создало бы больше проблем. Excel, когда помещает значения в ячейки собственными методами, сам приводит в соответствие отображаемое значение с хранимым в памяти.Причем, насколько мне известно, вычисления для повышения точности производятся с 80-битной (18 разрядной) математикой, но результат приводится к стандартному 64-битному (15-разрядному) представлению.Но если файл не родной, то при этом подхватываются лишние и некорректные (с т.з. Excel) десятичные разряды, если они записаны в файле.То есть, если Excel файл создан не с помощью автоматизации Excel, а внешними средствами (есть множество сторонних библиотек), например, как это делает 1C, то соответствия между значением в памяти (с доп. десятичными разрядами) и отображаемым значением может и не быть. Причем, возможен даже вариант, когда формула ячейки одна, а значение - другое не соответствующее, и только пересчет формулы наведет порядок.Очевидно, что и встроенные функции, которые используют значения из памяти, будут работать с точки зрения своей логики корректно с такими значениями, но некорректно с точки зрения логики пользователя, не видящего значений доп. десятичных разрядов. Перечень функций, попадающих под этот эффект, я не составлял (в принципе, достаточно протестировать их на таких некорректных данных), но очевидно, что это функции, работающие с (бинарными) значениями, в частности - ВПР.

Поэтому обсуждение можно считать закрытым - истина найдена :-)

Answer 2

Жук, спасибо за ответ.

Не совсем понял, если честно, причем здесь формат с плавающей точкой. Я знаю про стандарт  IEEE 754 (стандарт формата представления чисел с плавающей точкой), который используется в Excel и ошибки, которые могут возникнуть в результате вычислений. И знаю про хранение формата даты. Но не думаю, что это применимо в данном случае - ничего не меняется, если ячейкам присвоить числовой формат. Даже если бы программа изначально выгрузила данные не в виде даты - проблема есть(встречал уже раньше проблему у пользователей

• собственно, не единичный случай и подвиг на это обсуждение).

Вопрос-то в другом: почему другие функции не видят разницу в данных и считают их равными, а ВПР видит? Если бы дело было в том, о чем Вы написали - все функции считали бы их либо равными, либо одинаковыми. СОВПАД же не делает вычислений? Она сравнивает текст. Но если посмотреть в приложенный файл - то там она так же берет значение, хранящееся именно в ячейке, а не в схеме XML. И возвращает ИСТИНА. А ВПР почему-то берет значения именно из схемы, а не с ячеек и в результате не находит значения.

До 15 знаков после запятой еще есть запас, поэтому грешить на округление и на ограничение в 15 знаков после запятой для Excel тоже не вижу смысла.

Иля я неверно понял Вашу мысль.

Answer 3

Excel, хранит введённые данные в числовом формате с плавующей точкой и точностью до 15 знаков. При вычислениях, тем более с использованием функций, это и может сыграть злую шутку над Пользователем.

Так, Дата в выводимом виде Excel 09.12.2013  19:00:00, имеет числовое значение: 41617.791666666664, таже дата полученная загрузкой из 1С 09.12.2013  19:00:00 может иметь числовое значение: 41617.791666666672, так как при выполнении вычислений, используется хранимое значение, то при выполнении функции ВПР возникает ситуация когда ВПР возвращает ошибку #НД, так как первое числовое значение Excel, меньше загруженного значения из 1С.

Answer 4

Спасибо за ответ и интерес к теме.

Я сразу написал, что 1С - это лишь пример. Но не только 1С так выгружает. Так же проявляется при выгрузке из БД mysql. Может и еще какие программы - по миру не собирал пока сведений. Подозреваю, что может проявится в любом файле, который создат через построение схемы XML.

Но мой непосредственный вопрос заключался именно в том, что простое сравнение ячеек и СОВПАД считают значения равными, в то время как ВПР видит различия. Т.е. допустим, что файл и выгружен откуда-то - но почему ВПР видит, что значения различаются(это видно только из схемы XML - в ячейках значения полностью одинаковые), а другие функции нет?

Именно это интересует, а не почему ВПР не находит.

Впечатление, что при визуализации значений в ячейках в таких файлах Excel округляет значения. Например VBA при попытке записать число 41617.791666666664 для присвоения переменной(переменная объявлена As Variant, чтобы можно было работать с типом Decimal)

• VBA сам округлит это значение до 41617.7916666667.

Sub Strange()

Dim h

' 41617.791666666664

h = 41617.7916666667

End Sub

Может быть при выгрузке на лист из XML-схемы происходит тоже самое?

Ссылка на файл в Skydrive: https://skydrive.live.com/redir?resid=B68D87B1E37FC612%21126

Answer 5

Здравствуйте, Дмитрий.

Проблема повторяется с любыми файлами или только с теми, которые были выгружены из программы 1с?

Разместите пример фала на ресурсе Skydrive в общей папке и предоставьте к ней ссылку.

Попробуйте также задать вопрос на форумах программы, в которой создавался данный файл, то есть 1с, или на форуме http://social.msdn.microsoft.com/Forums/ru-RU/home.