Выпадающий список Excel с несколькими критериями

Question

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

Как в:

Column A | Column B
_________|_____________    
person 1 | interviewer
person 2 | interviewed
person 3 | interviewed
person 4 | interviewer

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

У меня есть склонность, что это будет использовать комбинацию IF , но я не знаю, как это реализовать.

Answer 1

Это можно сделать, отсортировав столбец B по AZ, а затем открыв Диспетчер имен, нажав сочетание клавиш CTRL+F3. Это можно найти на вкладке ленты «Формулы» и в диспетчере имен в области «Определенные имена».

Нажмите New... , назовите этот будущий список для Interviewed и в поле Refers to: введите в этой формуле.

=OFFSET(INDEX(Sheet1!$A1:$A10,MATCH("interviewed",Sheet1!$B1:$B10,0)),,,COUNTIF(Sheet1!$B1:$B10,"interviewed"))

Вы можете повторить эти шаги для второго списка интервьюеров, но используйте эту формулу. =OFFSET(INDEX(Sheet1!$A1:$A10,MATCH("interviewer",Sheet1!$B1:$B10,0)),,,COUNTIF(Sheet1!$B1:$B10,"interviewer"))

Имена нуждаются в абсолютной ссылке на лист.Затем создайте свой список проверки данных и «Разрешить список», и в поле «Источник» вы можете нажать F3, чтобы вызвать ваши имена и выбрать его, чтобы он был вставлен для вас.

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

Функция OFFSET() использует функцию INDEX() чтобы просмотреть A1:A10 и вернуть нам ссылку на ячейку. MATCH() используется для предоставления строки, которая содержит первое вхождение "интервьюер". Таким образом, Index возвращает первый параметр Offset. три запятых в строке - потому что мы не хотим перемещать контрольную точку по строкам или столбцам.

Однако мы хотим изменить высоту! Это магия, потому что смещение может быть использовано для создания диапазона для нас. Наш рост вычисляется с помощью COUNTIF() который просматривает столбец B1:B10 и возвращает два в вашем примере. Теперь функции смещения используют первую контрольную ячейку и высоту два, чтобы построить для нас диапазон, в котором содержатся имена интервьюеров.

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