Михаил С. написал: И ВПР и ПОИСКПОЗ принимают не более 10 знаков, а в таблице - 15-значные числа.
Михаил, если бы всё было так просто. Увы, сделал копию листа. Достаточно в А2 (с В2 не выполняется) F2, удаляем последнюю 4 и тут же вводим 4, Enter. Функции работают. В Immediate на исходном листе
Код
?[A2]=[B2]
False
?[A2]-[B2]
4,65661287307739E-10
С точки зрения VBA числа не равны! Учитывая, что работает только при любом изменении А2, можно минус удалить и тут же его добавить - ВПР начинает работать. Есть подозрение, что в силу какого-то ввода (учитывая разницу в VBA, не возможную для 15 значного Double) число в А2 рассматривается Excel как тип Extern (10байтовое с плавающей запятой, с чем реально работает математический сопроцессор). Мелкомягкие сообщали, что перешли на использование в вычислениях с 2007 (если не ошибаюсь) на повышенную точность, а это возможно, только если задействовать этот тип. Ждём, что сообщит bedvit. Какую такую штуку он туда ввёл (Может вывод в ячейку был с Delphi или С++).
Если заглянуть в XML структуру файла, то в ячейке A2 там записано число: -4154717.7200256395, а не -4154717.72002564, как это отображается в Excel (15 значащих цифр). Такое число в A2 с лишними разрядами может быть как результатом записи внешней программы или подтягивания значение из внешнего источника, так и результатом сохранение формулы как числа в Excel. Движок вычисления формул Excel всегда использует максимально возможную точность (более 15-ти разрядов представления чисел с плавающей точкой). Но лишние десятичные разряды результата вычисления формулы могут как оставаться, так и отбрасываться. Если вычислительный движок по своей логике посчитает, что лишние разряды являются результатом математики с плавающей точкой по используемой им спецификации EEE 754, то лишние разряды будут отброшены. Обычно (не всегда) такое отбрасывание цифрового мусора в результате формулы происходит, когда последнее действие в формуле является вычитанием или сложением. И в некоторых логических операциях. Например, в таблице из сообщения #1 формула =B2-A2 выдаст чистый ноль, мусор отброшен. А чтобы получить результат без отбрасывания лишних разрядов, достаточно заключить формулу в круглые скобки: =(B2-A2) и это правило действует всегда. Или в данном случае можно прибавить ноль: =B2-A2+0 , тогда в последнем действии складываются два близких друг к другу (при нормализация) значения, поэтому усечения до нуля в результате нет. Описанное выше усечение результата имеет смысл, так как по сравнению с древними версиями Excel, результат формул чаще всего более точен и понятен, но, к сожалению, не всегда. О некоторых нюансах математики с плавающей точкой можно почитать здесь - Numeric precision in Microsoft Excel С помощью VBA можно так подчистить значение A2: Range("A2").Value = Val(Str(Range("A2").Value)) и формулы заработают.
Кратко: sql-запросом тянутся данные из Oracle-серверов в Excel, складывается в сводные и делается аналитика (все автоматом). Аналитику делать стандартными функциями Excel в описанной ситуации не всегда выходит, приходится изобретать велосипед.
Набросал как это выглядит, файл прилагаю. Все просто-нужно вывести фамилию субъекта и сумму в тыс. руб по разным аналитикам.
...Cast(sum("Сумма") As Numeric(15, 4)) As "Field Name For Excel" ...
Но с учетом множества CASE WHEN, JOIN (здесь конечно с плав. точкой редко используются), UNION и всяких фильтров (WHERE) - много править, легче костыль в Excel сделать.
Так Cast нужен только в финальных полях Select - так ли уж много? Ну, а если нужен именно костыль, то через VBA Math.Round по загруженным числовым столбцам.
-вообщем-то вариант годный (спасибо за идею), но быстрее получилось через костыль в виде равенства, когда отбрасывается "мусор", в последнем моем файле:
Андрей VG, вот я здесь подумал и пришло в голову следующее: а не может так быть, что Oracle - мне отдает 15 знаков(может и меньше), а движок вычисления формул/данных Excel - при формировании разных срезов в сводных таблицах, при группировках/суммировании/вычислении формул мне выдает больше знаков, тогда Cast никак не поможет (надо бы проверить). Хех, тогда вообще весело.
bedvit написал: а не может так быть, что Oracle - мне отдает 15 знаков(может и меньше), а движок вычисления формул/данных Excel
Что в Oracle, если есть доступ, можно же посмотреть, какие определения полей у таблиц, вполне может быть какое-нибудь Numeric(32, 16) - 32 разряда с 16 знаками после запятой. Плюс, ещё один аргумент в защиту Excel, у вас же число в ячейке - константа, записанная, как вы сказали выгрузкой запросом из Oracle. Или всё же Excel поместил эти числа?
Не совсем понятно, почему вы связываете данные по числовым вычислениям? Чтобы с этим бороться, нужно округлять, либо, что в принципе тоже самое, вводить понятие эпсилон, некоторого минимального расстояния до нуля, и если абсолютная разница между числами меньше него, то числа равны, если больше не равны, тут сложно что-то придумать.
Андрей VG написал: Или всё же Excel поместил эти числа?
Источником для сводной таблицы является SQL-запрос. не знаю потроха сводной - не могу сказать делает ли там что-то Excel.
Цитата
Андрей VG написал: почему вы связываете данные по числовым вычислениям?
Потому, что в аналитике используется например 10 наибольший или 10 наименьший и т.д., которые можно получить только по числовым данным. На самом деле проблема решена - писал в #18, но сам факт бага был интересен
ZVI написал: С помощью VBA можно так подчистить значение A2
Спасибо! эти закоулки формул и IEEE754 никогда не дают спокойно жить...
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)