Блог

Я хотел бы добиться того, чтобы продавцы могли выбирать МАГАЗИН в синей ячейке (либо с помощью раскрывающегося списка, либо путем ввода названия МАГАЗИНА), и, основываясь на выборе в синей ячейке, доступные ПОЗИЦИИ для этого конкретного ПРОДУКТА и этого конкретного МАГАЗИНА отображаются в зеленой ячейке в виде раскрывающегося списка.

Допустим, у меня есть книга Excel, которая содержит рабочий лист с этой таблицей с данными о продуктах, которые ежедневно автоматически импортируются с нашего навигационного сервера с помощью этого макета. Он имеет 4 столбца, включая КОД ПРОДУКТА, ОПИСАНИЕ, МАГАЗИН, В КОТОРОМ ОН МОЖЕТ БЫТЬ РАСПОЛОЖЕН, и РАСПОЛОЖЕНИЕ ВНУТРИ МАГАЗИНА (пожалуйста, проверьте скриншот). Он содержит 1,5 тыс. строк и динамически изменяется, например, добавляются новые элементы или происходит обмен позициями.

Как вы можете видеть, один и тот же товар (ТОВАР 2) может находиться в нескольких магазинах (МАГАЗИНЫ 1, 2 и 3), и он может находиться в нескольких местах в каждом магазине (ПОЗИЦИИ 2, 3, 1 и 4).

Теперь мне нужно, чтобы продавцы сообщали, какой из этих товаров они выбирают и откуда, не только в МАГАЗИНЕ, но и в его ПОЛОЖЕНИИ внутри магазина. Они делают это с помощью другого рабочего листа в той же книге Excel. Это выглядит более или менее так (пожалуйста, проверьте скриншот).

Я знаю, что выпадающий список достигается с помощью проверки данных, но я не могу понять формулу для этого. Я пробовал несколько подходов, таких как:

• Формула массива для возврата всех ПОЗИЦИЙ в одной СТРОКЕ, следующая (Формула 2.): https://www.ablebits.com/office-addins-blog/2017/02/22/vlookup-multiple-values-excel/. Вычисление по 1,5 тыс. элементам выполняется довольно медленно, и после этого я не могу понять, как выполнить проверку данных для поиска 4, 5 или 10 ПОЗИЦИЙ, возвращаемых формулой массива, которые также необходимо отфильтровать по ХРАНИЛИЩУ (пожалуйста, проверьте скриншот для ближайшего, где я был, формула массива, возвращающая ПОЗИЦИИ из столбца E).

• Та же формула, что и выше, непосредственно в списке проверки данных, который возвращает только первую найденную ПОЗИЦИЮ.
• Пользовательские функции VBA, которые не разрешены в поле проверки данных.

Я чувствую себя комфортно как с Power Query, так и с VBA, а также с forumla, и могу адаптировать большую часть кода, который я вижу, но я не знаю, почему я просто не могу понять, как этого достичь, может быть, это только я заблокирован или что-то в этом роде, но каждый путь, по которому я начинаю следовать, заканчивается тупиком.

У кого-нибудь есть идеи о том, как подойти к этому? На самом деле это не кажется таким уж сложным, но для меня это становится невозможным.

Большое вам спасибо за потраченное время!!

Это то, что я, наконец, сделал, на случай, если кто-то еще столкнется с такой ситуацией.

1. Вместо таблицы с простым текстом для ПОЗИЦИЙ я создал запрос доступа, импортирующий этот CSV. Назвал этот лист _LOCATIONS.
2. Добавлен пользовательский столбец (столбец E), объединяющий ПРОДУКТ и МАГАЗИН, поэтому у меня было что-то вроде уникального идентификатора, что привело к чему-то подобному, но в PowerQuery.

Комбинированная колонна:

3. Отсортированный столбец E и субсортированный столбец D, поэтому я уверен, что список всегда будет упорядочен по мере необходимости, и сохранил запрос.
4. Затем в ОТЧЕТЕ рабочего листа я ввел эту формулу, чтобы создать раскрывающийся список для проверки данных в ячейке D2:

   OFFSET(_LOCATIONS!$D$1,MATCH($A2amp;"-"amp;$C2,_LOCATIONS!$E:$E,0)-1,0,COUNTIF(_LOCATIONS!$E:$E,$A2amp;"-"amp;$C2))

И я могу выбирать из доступных ПОЗИЦИЙ для выбранного ТОВАРА в выбранном МАГАЗИНЕ.

Краткое объяснение: Я устанавливаю ссылку для функции СМЕЩЕНИЯ в самую первую ПОЗИЦИЮ (D1), а затем перемещаю ее на количество строк, обнаруженных функцией СОПОСТАВЛЕНИЯ (которая ищет строку «ПРОДУКТ 2-МАГАЗИН 2» во вновь созданном объединенном столбце) минус 1 (таблица PoweryQuery содержит заголовки) и 0 столбцов. Это оставляет меня при первом вхождении моей строки (но в столбце ПОЗИЦИИ). Затем я делаю смещение, равное количеству строк, обнаруженных функцией COUNTIF (которая подсчитывает все вхождения моей пары «ПРОДУКТ-МАГАЗИН»), возвращая массив всех позиций (столбец D), соответствующих паре «ПРОДУКТ-МАГАЗИН».

Попросите формулу на испанском языке, если она вам нужна.