#excel #validation #datatable #xlookup
Вопрос:
У меня есть таблица данных, основанная на запросе -> tblPrimaryProducts
tblPrimaryProducts содержит множество столбцов, некоторые из которых являются Категорией, подкатегорией и Товаром.
Если бы я использовал фильтр по категориям, я, конечно, смог бы увидеть подкатегории и элементы в этой категории. Аналогично, если бы я, в свою очередь, применил фильтр к подкатегории, я бы увидел список элементов в этой конкретной категории выбор подкатегории. В конечном счете, это цель проверки данных, которую я пытаюсь реализовать.
Есть таблица ввода данных -> tblPackages
В tblPackages много полей, но важно отметить, что три перечисленных выше необходимо реализовать контролируемым образом с помощью проверки данных, чтобы в каждой строке была правильно выбрана Категория Подкатегория Элемент
Я не могу изменить таблицу данных в массив или простой (или динамический) именованный диапазон, потому что она будет обновляться любое количество раз, и позиции столбцов потенциально изменятся. Это означает, что я должен использовать систему именования на основе таблиц для обеих таблиц.
До сих пор у меня есть следующее для формулы проверки данных с использованием XLOOKUP:
XLOOKUP([@[Primary Category]] amp; [@[Primary SubCategory]], tblPrimaryProducts[Category] amp; tblPrimaryProducts[SubCategory], tblPrimaryProducts[Item],,0)
[@[Основная категория]] и [@[Основная подкатегория]] являются полями в таблице ввода данных tblPackages. Остальные перечисленные поля взяты из tblPrimaryProducts. До сих пор я вводил данные вручную, чтобы проверить это.
В окне формулы XL (где вы можете «видеть», что делает ваша формула, пока вы заполняете поля) это, похоже, работает и обеспечивает ожидаемый результат.
Когда я пытаюсь реализовать это в качестве проверки данных, он жалуется на ошибку в формуле. Если я помещу КОСВЕННОЕ в начале, я получу всплывающее окно с синтаксической ошибкой. В прошлом у меня были проблемы с КОСВЕННЫМИ, которые были решены с помощью осторожного использования кавычек. Кажется, я не могу найти способ сделать это здесь.
У кого-нибудь есть какие-либо идеи о том, как я мог бы реализовать некоторую проверку данных на основе таблицы данных (в отличие от массива) или запроса, на котором основана таблица в этой ситуации? При нормальных обстоятельствах я бы использовал динамические именованные диапазоны, но здесь я не могу.
Любая помощь или идеи будут высоко оценены
Ответ №1:
В Office365 по следующей формуле вы получите список «Товаров», которые удовлетворяют условиям «Категория» и «Подкатегория».
=FILTER(tblPrimaryProducts[Item],(tblPrimaryProducts[Category]=[@[Primary Category]])*(tblPrimaryProducts[Subcategory]=[@[Primary Subcategory]]),"")
Это даст вам #РАЗЛИВ! ошибка, если она вставлена в таблицу и возвращает несколько элементов, соответствующих двум критериям. Возможно, вам придется изменить дизайн вашей выходной таблицы.
Если вы собираетесь отображать только отфильтрованные значения, вы можете использовать два раскрывающихся списка со списком категорий и подкатегорий. Затем функция фильтра может использовать эти ячейки для фильтрации элементов из исходных данных и отображения в виде списка ниже.
Комментарии:
1. Спасибо, что попробовали это сделать. Я понимаю, что вы написали, однако это не касается самого вопроса. Этот подход приводит к точно такой же проблеме с использованием его в качестве исходной формулы для проверки данных. Это просто не работает как формула проверки данных. Неважно, как это, по-видимому, применяется, точно такие же проблемы, как и в моих первоначальных попытках. Можете ли вы предложить способ, которым я могу реализовать это в проверке данных в качестве полезной формулы?