Профессиональные приемы работы в Microsoft Excel

Пожалуйста, войдите или зарегистрируйтесь.


Расширенный поиск  

Новости:

Прикрепить к сообщению можно только файлы xls, gif, jpg, rar, zip,7z, bas, frm, cls, doc размером до 150 Кб.

Автор Тема: Функция ГПР() отказывается принимать значение полученное с помощью ОКРУГЛВВЕРХ()  (Прочитано 1259 раз)

0 Пользователей и 1 Гость просматривают эту тему.

Silicium

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 2

Доброго дня форумчанам.
Возникла у меня такая вот странная проблема...

Функция ГПР() отказывается принимать значение 0,8 в качестве искомого (точнее, видимо просто ничего по нему не находит), если оно полученное с помощью функции ОКРУГЛВВЕРХ(). Тоже значение полученное другими способами принимается. Другие значения полученные с помощью ОКРУГЛВВЕРХ() тоже принимаются.

Формулы на Листе 3, таблица на Листе 5.
Записан

vikttur

  • Глобальный модератор
  • Ветеран
  • *****
  • Уважение: +50/-0
  • Оффлайн Оффлайн
  • Сообщений: 1 015

Странно...
0,61=0,7 (при округлении вверх), значение находится
0,71=0,8 (при округлении вверх), значение не находится.
Получается, что ищем число между 7,999999999999999 и 8,0
=ОКРУГЛ(ОКРУГЛВВЕРХ(0,71;1);1) - так работает...
Причину ошибки не нашел...
« Последнее редактирование: 08.05.2015, 16:04:43 от vikttur »
Записан

Silicium

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 2

О, ну хоть решение нашлось)
Сам как-то не догадался. Спасибо!

Хотя конечно, и причину понять интересно бы было...
Записан

vikttur

  • Глобальный модератор
  • Ветеран
  • *****
  • Уважение: +50/-0
  • Оффлайн Оффлайн
  • Сообщений: 1 015

Это не решение - костыль.
Причину бы понять.
прикрепил пример, созданный в чистом файле.

Параллельно задал вопрос об ошибке на Планете
http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=65988&TITLE_SEO=65988-oshibka-okrugleniya-okruglvverkh-ili-glyuk-gpr
« Последнее редактирование: 08.05.2015, 17:04:50 от vikttur »
Записан

Prist

  • Глобальный модератор
  • Старожил
  • *****
  • Уважение: +101/-0
  • Оффлайн Оффлайн
  • Сообщений: 736
  • Errare humanum est, stultum est in errore persever
    • Excel-VBA

Итак, в XML схему записываются значения не те, что отображаются. И выглядят они следующим образом:
Значение в K5:
<f>ROUNDDOWN(M5/N5,1)</f>
<v>0.8</v>

Значение в K6:
<f>ROUNDUP(M6/N6,1)</f>
<v>0.79999999999999993</v>

Значение в K7:
<f>ROUNDUP(E7/F7,1)</f>
<v>0.79999999999999993</v>

Поэтому и не находит. Как правило такие косяки проявляются, если файл был сформирован в программах типа 1С. При этом, если в К6 и К7 формулы заменить значениями и после этого F2+Enter - то все рассчитается корректно.

ВПР как ни странно, ищет данные бинарным типом и почему-то заглядывает внутрь XML значений, а не тех, которые отображаются в ячейках. Почему так - не скажу. Когда-то давно я это уже разбирал и задавал вопрос в тех.поддержку Microsoft - ничего вразумительного не ответили.
Записан
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
www.excel-vba.ru
Просто СПАСИБО [+оказать+]
Считаешь СПАСИБО мало? Яндекс.Деньги: 41001332272872; WM: R298726502453

vikttur

  • Глобальный модератор
  • Ветеран
  • *****
  • Уважение: +50/-0
  • Оффлайн Оффлайн
  • Сообщений: 1 015

В К5 ОКРУГЛВНИЗ, в К6 ОКРУГЛВВЕРХ.
Почему, в таком случае, ОКРУГЛВВЕРХ с 0,6 работает?
Цитировать
Как правило такие косяки проявляются, если файл был сформирован в программах типа 1С
Мой пример создан с чистого листа.
« Последнее редактирование: 08.05.2015, 18:10:51 от vikttur »
Записан

Prist

  • Глобальный модератор
  • Старожил
  • *****
  • Уважение: +101/-0
  • Оффлайн Оффлайн
  • Сообщений: 736
  • Errare humanum est, stultum est in errore persever
    • Excel-VBA

Витя, чудеса стандарта IEEE 754.
Для каких-то чисел вычисление пройдет верно, а для каких-то съедет. И как видно из моего предыдущего сообщения - мы попали в этой теме именно на одно из них. Витя, да ты и сам знаешь, что таких пар чисел миллионы.
Цитировать
Мой пример создан с чистого листа.
Верю. Т.к. у тебя проблема точно та же. В ячейке К4 значится:
<f>ROUNDUP(0.71,1)</f>
<v>0.79999999999999993</v>

Значит проблема именно в ОКРУГЛВВЕРХ. Вопрос-то в другом: почему ВПР упорно ищет не то, что отображается в ячейке, а то, что в схеме?

Ну и дабы окончательно убить: попробуй в файле записать 0,61 в ячейку А1. В А2 - формулу вида: =A1+0,1. Протяни ячеек на 20. В столбце В запиши формулу округлвверх. Все поплывет начиная с 0,71. Но. Теперь даже если вручную вбить значения в столбце А - формула все равно будет лажать. В XML схему она не перепрописывает новые значения, пока не прощелкать ячейки с формулами. Вот так вот...
Записан
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
www.excel-vba.ru
Просто СПАСИБО [+оказать+]
Считаешь СПАСИБО мало? Яндекс.Деньги: 41001332272872; WM: R298726502453

vikttur

  • Глобальный модератор
  • Ветеран
  • *****
  • Уважение: +50/-0
  • Оффлайн Оффлайн
  • Сообщений: 1 015

Спасибо.
Глюк.
Начинаю сомневаться в функциях. Ведь ОКРУГЛВВЕРХ должна игнорировать стандарт. Вернее, исправлять его огрехи. Ан нет, не хочет.
Записан

zs

  • Старожил
  • ****
  • Уважение: +12/-0
  • Оффлайн Оффлайн
  • Сообщений: 577

Товарищ, vikttur! А что, если общий формат числа 0,79999999 преобразовать формулой
=--ТЕКСТ(A1;"0,##")
Записан

vikttur

  • Глобальный модератор
  • Ветеран
  • *****
  • Уважение: +50/-0
  • Оффлайн Оффлайн
  • Сообщений: 1 015
Записан

Czeslaw

  • Пользователь
  • **
  • Уважение: +7/-0
  • Оффлайн Оффлайн
  • Сообщений: 83

Вместо "ОКРУГЛВВЕРХ" можно применииь формулу
=HLOOKUP(CEILING(M6/N6;0,1);Лист5!$R$7:$AA$30;D6;FALSE)
Записан
 



Темы без ответов

24.01.2020 14:03 На диаграмме Ганта несоответствие оси Y 1132
09.08.2019 14:09 Макрос для заполнения таблиц через форму 2880
18.07.2019 16:02 Рассылка почты из Excel при помощи почтовой программы TheBAT! 2498
09.07.2019 20:39 Кредит с уменьшением периода выплат 2595
28.05.2019 21:09 Сделать несколько скриптов для рабочей таблицы 3306
05.03.2019 17:00 Последовательный вывод таблиц Excel в один документ Word без шаблона 3200
05.03.2019 09:29 Нежелательные изменение размеров колонтитула при редактировании 2960
07.02.2019 01:36 Как удалить дубликаты из выпадающего связанного списка? 3122
20.01.2019 12:38 Все варианты частичного суммирования 3280
13.01.2019 12:24 Заполнение диапазона числами - в виде кластеров 2629





Яндекс цитирования msexcel.ru Яндекс.Метрика

Страница сгенерирована за 0.127 секунд. Запросов: 117.