Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Почему ВПР не просматривает весь заданный диапазон?
 
#1
10.04.2020 21:30:54
Доброго времени суток.
Увожаемые форумчане, подскажите пожалуйста, какой формулой мне заменить мою существующую формулу =ВПР(F11;Тарификатор!$A$7:$I$2155;3;0).
Проблема в том что на сколько я знаю ВПР может обрабатывать до 255 строк, а во вкладке Тарификатор необходимо обрабатывать более 2000 строк.
Пытался воспользоваться функциями ИНДЕКС и ПОИСКПОЗ но не получилось создать правильную формулу.
Прошу Вас помочь с правильной формулой.
За ранее всем спасибо!
 
 
 
#2
11.04.2020 17:50:25
Marselo, так от вас никто не просит весь файл достаточно 10-50 строк
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#3
11.04.2020 18:35:42
Вот файл
 
 
 
#4
13.04.2020 08:12:30
Marselo, Суть формул ВПР Индекс+Поискпоз в том, что Вам необходимо указать в качестве списка из которого вы добавляете позиции на лист - Уникальный список не имеющий повторений.
Логика работы формулы ВПР следующая:

=ВПР
(ПозицияИзСписка1;
ТаблицаЗначений,вКоторойПервыйСтолбецСодержитИскомоеЗначение;
НомерСтолбцаВТаблицеКоторыйСодержитВозвращаемоеЗначение;
ИСТИНАилиЛОЖЬ(при_выборе_ложь_ТочныйПоиск_Иначе_ПриблизительныйПоиск)

Т.е. Начиная поиск позиции из списка №1, и просматривая позиции из списка №2, вы должны быть уверены в том, что позиции в списке №2  не имеют повторов.
В том случае, если Вам безразлично есть у Вас повторы, или нет, то вы можете указать в качестве возвращаемого значения ИСТИНУ, или Эквивалент - 1, в качестве последнего параметра.
На моей памяти за более чем 10 лет работы с разными таблицами я не использовал параметр Истина ни разу. Обычно поиск всегда точный, по точному соответсвию.

Таким образом, подытожив все вышенаписанное, в свей формуле на листе ОРЕХ(грецкий или фундук) в колонке "O" Вы ищите значение из столбца "F", в столбце "A" на листе Тарификатор, при этом указываете, что Вам нужно ТочноеСоответсвие.

После чего искренне удивляетесь, и негодуете тому, что получаете ошибку поиска, сообщающую о том, что искомого значения нет в списке.
Вы в каком столбце на листе Тарификатор хотите найти свой Боченок "Бочонок стальной Ду25" в СтолбцеА? или в СтолбцеВ?
Если в столбцеБ, то Вы уверены в том, что все значения из этого списка уникальны и не имеют повторений?
Вам нужно немного с базисом формулы разобраться, надеюсь что этот пост слегка поможет в этом. Удачи.
 
 
 
#5
13.04.2020 10:31:14
lostandleft,Спасибо большое! :)

Разобрался где была ошибка.
 
 
 
#6
13.04.2020 11:53:26
Цитата
lostandleft написал:
за более чем 10 лет работы с разными таблицами я не использовал параметр Истина
- оказывается зря, могли бы сильно ускорить работу...
Если список отсортировать, и использовать ИСТИНА - то можно искать бинарно. И если поиск в первом столбце нашёл именно то, что искали - то можно поискать ещё раз, уже взяв из нужного столбца.
 
 
 
#7
13.04.2020 13:28:58
Цитата
lostandleft написал: На моей памяти... я не использовал параметр Истина ни разу
Или не знали возможностей такого использования, или 10 лет постоянно решали одну и ту же задачу :)

При точном соответствии перебираются все строки по очереди. Если искомое на строке миллионной - миллион итераций.
Если значение есть в массиве, можно поискать по-другому.
Сортируем массив, поиск неточного соответствия. Функция режет массив посередине. Граничное значение сравнивается с искомым, выбирается нужная половина. Опять режет, опять сравнивает... Итог: поиск точного соответствия во всех 1048576 строках листа - 20 итераций (2^20). И это МАКСИМУМ, до которого дело доходит не всегда.

Как Вам выигрыш? )
 
 
 
#8
13.04.2020 14:27:58
Цитата
Marselo написал:
Почему ВПР не просматривает весь заданный диапазон?
потому что в формуле:
Код
=ВПР(F11;Тарификатор!$A$7:$I$2155;3;0)
написано - просмативать до первого совпадения и еще потому, что ВПР работает так, как описана ее работа в документации Excel, а не так как того хотелось бы вам
Изменено: Ігор Гончаренко - 13.04.2020 14:52:38
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#9
13.04.2020 19:18:50
Цитата
vikttur написал:
Или не знали возможностей такого использования, или 10 лет постоянно решали одну и ту же задачу
Честно, признаюсь, до недавнего времени обработки 40тысяч строк вполне хватало. И это были даже большие файлы, очень долгое время мне хватало и эксель 2003, с его ограничением на количество строк.
Заниматься искусственной сортировкой для оптимизации не приходилось. Однако об этой особенности узнал еще эдак в 2009 году, в то время всех сотрудников усиленно пересаживали на OPENOFFICE я в том числе был инициатором. Вот тогда то и намучился с несоответсвием заданных параметрам у пользователей, которые привыкли ВПРить в экселе, а VlookАппить им было тяжеловато, приходилось пояснять про 1 или 0 в конце каждому.
Ну а сейчас столкнувшись с большими табличками, подался в изучение powerQuery, потому что оптимизация формулами - тупиковый путь, все больше и больше в этом убеждаюсь, с каждым днем....это все мертвое уже, нужны оптимизации иди в PQ и DAX...Vbaшники конечно сила и завтра, и послезавтра, но обычный рядовой формулист - наверное дело уже вчерашнее, если уж не совсем вчерашнее, то раннеутрешнеее точно. Нужно учить dax и PQ.Но это все к теме не относится. Сорри за оффтоп.
 
 
 
Страницы: 1
Наверх