Блог

У меня есть некоторые материалы в столбце B, некоторые из них находятся в определении таблицы под названием Material_List. В D49 я пытаюсь написать условный оператор таким образом, чтобы, если данные в B49 уже существуют в определении таблицы, затем напечатать имя заголовка или другое КОСВЕННОЕ($49). C49 имеет независимый выпадающий список, а D49 будет зависимым.

В D49 я использовал следующую формулу в данных—>Проверка данных—>>Источник=

 =IF(MAX((ISNUMBER(MATCH(Material_List;$B49;0))*COLUMN(Material_List)))=0;
INDIRECT($C49);
INDEX(Material_List[#Headers];1;MAX((ISNUMBER(MATCH(Material_List;$B49;0))*
COLUMN(Material_List))))))
 

с помощью списка Разрешить=. Но там написано Ошибка «Есть проблема с этой формулой».

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

 =IF(MAX((ISNUMBER(MATCH(Material_List;$B50;0))*COLUMN(Material_List)))=0;
INDIRECT($C50);
INDEX(Material_List[#Headers];1;MAX((ISNUMBER(MATCH(Material_List;$B50;0))*
COLUMN(Material_List))))))
 

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

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

Не спрашивай меня, почему. Я думаю, что это просто выдающаяся ошибка Excel, которая еще не исправлена. Пожалуйста, смотрите эту ссылку для получения дополнительной информации: https://exceloffthegrid.com/using-an-excel-table-within-a-data-validation-list/

Лучший способ, который я нашел, чтобы обойти это, — создать именованный диапазон, который ссылается на нужные вам ссылки на таблицы («Список материалов» и «Список материалов[#Заголовки]» в вашем случае). Затем вы можете использовать эти именованные диапазоны при проверке данных вместо прямых ссылок на таблицы.

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

 MATCH(Material_List;$B50;0)
 

Обычно a MATCH будет в формате:

 MATCH(<single value to look for>, <range to look in>, 0)
 

Похоже, у вас это перевернуто, что означает, что он всегда должен возвращать #VALUE! ошибку.

Кроме того, я не думаю, что вы можете использовать сопоставление в 2D-массиве, поэтому, если ваша таблица «Список материалов» состоит более чем из одного столбца, это также приведет к возвращению #VALUE! ошибки.

Обновить:

Способ, которым я бы справился с зависимыми выпадающими списками, был бы следующим. Я бы создал таблицу «Список материалов», аналогичную приведенной ниже (может быть на скрытом листе).:

Затем я бы создал 3 именованных диапазона. Один для диапазона тела таблицы, называемый «MaterialList_TblRange».:

 =Material_List
 

Один для диапазона заголовков таблицы, называемый «MaterialList_TblHeaderRange».:

 =Material_List[#Headers]
 

И один из них для ссылки на зависимые параметры выпадающего списка, называемые «DropDownOptions» (это, безусловно, самая сложная часть).:

 =INDEX(MaterialList_TblRange,1,MATCH(Sheet1!$B23,MaterialList_TblHeaderRange,0)):INDEX(MaterialList_TblRange,COUNTA(INDEX(MaterialList_TblRange,1,MATCH(Sheet1!$B23,MaterialList_TblHeaderRange,0)):INDEX(MaterialList_TblRange,ROWS(MaterialList_TblRange),MATCH(Sheet1!$B23,MaterialList_TblHeaderRange,0))),MATCH(Sheet1!$B23,MaterialList_TblHeaderRange,0))
 

Я сейчас объясню, что это значит.

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

Там, где мы хотим, чтобы появлялись основные списки, мы можем просто ввести:

 =MaterialList_TblHeaderRange
 

И проверка раскрывающегося списка ответчика может быть введена как:

 =DropDownOptions
 

Это и есть результат:

Теперь вернемся к длинной формуле диапазона с именем «Выпадающие варианты»…

В основном мы используем INDEX:INDEX для выбора первой/последней ячейки в диапазоне, который мы хотим использовать в раскрывающемся списке «Выход».

Первый INDEX :

 =INDEX(MaterialList_TblRange,1,MATCH(Sheet1!$B23,MaterialList_TblHeaderRange,0))
 

Просто выберите первую ячейку из столбца, заголовок которого соответствует выбору в нашем первом раскрывающемся списке.

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

Это означает, что в этой таблице не должно быть пробелов, иначе в конце может быть пропущен какой-либо вариант.

Я надеюсь, что в этом есть смысл.