Страницы: 1
RSS
Получение информации из прайс листа, с выбором диапазона исходных данных в зависимости от маски артикула
 
Уважаемы Гуру!

Есть прайс с данными в формате excel 95-97 (ограничение а 65K строк), которые хранятся на одном листе в несколько групп по 3 колонки (лист Price - артикул, описание, цена) , всего сейчас артикулов ~300K, и их количество  постоянно увеличивается . В каждой группе, находятся артикулы подходящие под определенную маску (задается начало артикула, длинна артикулов может быть разной). На отдельном листе (Setup) хранится информация в каких колонках находятся артикулы c указанной маской, не использовать «*» и указать все варианты на которые могут начинаться артикулы практически не реально. Задача: с использованием стандартных функций Excel (без VBA и формул массивов требующих ввод через Ctrl-Shift-Enter) сделать поиск по артикулу его описания и цены в прайсе. В случае когда маски всех групп уникальные и не пересекаются – у меня получилось (лист  Test с 1 по 24 строки). В случае когда есть пересечения масок (например SBS* и SB*, при этом заранее известно что более «точная» маска SBS* идет первой) - не получилось выделить именно первое попадание. Пользовался формулой СУММПРОИЗВ, в ней почему-то функция ПОИСКПОЗ использованная 1 раз работает как надо (работает с переданным ей диапазоном как с массивом и возвращает массив),  а если использую функцию ПОИСКПОЗ 2 раза (одна вложена в другую) – то у «внутренняя» функция ПОИСКПОЗ перестает работать с передаваемым диапазоном как с массивом и возвращает одно значение вместо массива. Может подскажете где ошибка?
 
Цитата
denon написал: «внутренняя» функция ПОИСКПОЗ перестает работать с передаваемым диапазоном как с массивом и возвращает одно значение вместо массива. Может подскажете где ошибка?
вот тут:
Цитата
без VBA и формул массивов требующих ввод через Ctrl-Shift-Enter
вы пытаетесь сделать взаимоисключающие вещи, увы. далеко не все формулы могут так работать, как СУММПРОИЗВ.

Но, если отсортировать Setup по возрастанию, то возможно вот так:
Код
=ВПР($A1;ДВССЫЛ(ИНДЕКС(Setup!$B$1:$B$10;ПРОСМОТР(2;--(ПОИСКПОЗ(Setup!$A$1:$A$10;$A1;0));СТРОКА($A$1:$A$10))));СТОЛБЕЦ();0)
F1 творит чудеса
 
Максим,
спасибо за быстрый ответ и интересное решение, поскольку с работой функции ПРОСМОТР в деталях разбираюсь только сейчас - можно 2 вопроса по ней?
1) на сколько важно искать значение "2" в "ПРОСМОТР(2;"  или в данном случае (когда значения массива могут быть только "1" и "#Н/Д";) можно "2" заменить на "1" ?
2) на сколько важно наличие двойного унарного минуса в "--(ПОИСКПОЗ(Setup!$A$1:$A$10;$A1;0))" ?

>вы пытаетесь сделать взаимоисключающие вещи, увы. далеко не все формулы могут так работать, как СУММПРОИЗВ.
В целом задача такая - есть прайс лист, который обновляется ежемесячно, по нему через ВПР (ранее он был меньше 65K строк) легко вытягивалась информацию о описании и цене по артикулу. Сейчас кол-во строк стало значительно больше 65K, надо преобразовать исходные данные его так, чтобы для любой (в том числе и старой версии Excel, у которой есть ограничение в 65K строк на лист) можно было используя только стандартные функции (без помощи VBA и формул массивов Ctrl-Shift-Enter) по артикулу находить описание и цену. Пока лучшее что смог придумать - это по заранее заданным маскам сгруппировать данные в несколько разных колонок одного листа и с помощью тех же масок определять в какой колонке искать. Насколько в целом выбран логичный/оптимальный путь решения задачи?
 
1) Справка говорит, что "Если функции ПРОСМОТР не удается найти искомое_значение, то в просматриваемом_векторе выбирается наибольшее значение, которое меньше искомого_значения или равно ему". Соответственно, если искать среди массива {#Н/Д:1:1:#Н/Д:#Н/Д:#Н/Д:#Н/Д:#Н/Д:#Н/Д:#Н/Д} любое число больше 1, то ПРОСМОТР вернет соответственно последнюю из единиц. Чтобы вернуть его позицию, добавляем в просмотр счетчик строк. Так как ПОИСКПОЗ возвращает либо 1, либо #Н/Д, то искать можно всё, что больше 1.
Если Setup отсортирован по возрастанию, то SBS* будет иметь больший порядковый номер строки, чем SB*. Соотв., ПРОСМОТР годится. А вот найти в обратном порядке, наименьший номер - без формул массива что-то не хочет.

2) не важно, это осталось от предыдущих вариантов, можно удалить, вместе с соотв. скобками.

ЗЫ так и не понял, чем не угодили формулы массива.
F1 творит чудеса
 
Цитата
Максим Зеленский написал: ЗЫ так и не понял, чем не угодили формулы массива.
поскольку формула будет отдаваться для использования самым начинающим пользователям - велика вероятность того, что при копировании или случайном редактировании (нажмут Enter вместо Ctrl-Shift-Enter) формула массива "слетит"  и "всё поломается"
Страницы: 1
Наверх