Excel 2013 - проверка данных - создайте раскрывающийся список со значениями, если второй критерий соответствует

Question

Я собираюсь максимально упростить мою проблему. Я получил два листа в Excel. На первом листе я создаю список мест. Мне нужно выбрать область, где местоположение находится в столбце A, прежде чем я смогу ввести местоположение в столбце B. В следующем столбце (C) будет автоматически создан идентификатор (для первого идентификатора местоположения-001, второй получает ID 002 и т. д.).

На моем втором листе я создаю список активов. Поэтому, опять же, я выбираю область в столбце A и набираю имя ресурса в столбце B. Теперь я хочу, чтобы в столбце C был раскрывающийся список, в котором отображаются только те идентификаторы из первой таблицы, где эта область тот же самый. Я пытался использовать функцию OFFSET, но отсутствует точка - это что-то вроде «выберите, если» (например, countif или sumif), чтобы ограничить список идентификаторов теми, кто имеет ту же область.

В каждой области может быть много местоположений и активов, но каждое местоположение и каждый актив могут быть сопоставлены только одному местоположению.

Ждем ваших ответов, ребята! Либо решение Excel, либо решение VBA было бы здорово.

Answer 1

Я предлагаю создать вспомогательный столбец, содержащий уникальные значения для использования в VLOOKUP. Например; скажем, у вас есть Area1, Area2, Area3 и Area1 (снова) и продолжается. Объедините эти значения с инкрементными значениями (например, 01, 02, 03, ...) и создайте 01Area1, 01Area2, 01Area3 и 02Area1. Теперь вы знаете, что вызывать для каждого элемента списка на втором листе. Если вы выбираете Area1 в столбце A, то вам нужно искать инкрементный номер & Area1. Инкрементные числа по значениям могут быть созданы функцией COUNTIF, которая использует расширяющийся диапазон:

=COUNTIF($A$2:A2,A2)

Расширяя диапазон, формула считает только значения до своей собственной строки. Единственный недостаток этого метода - создавать элементы списка в другом месте.

Здесь две ссылки, описывающие подобное использование.

1. https://www.spreadsheetweb.com/how-to-get-nth-match-with-vlookup/
2. https://www.spreadsheetweb.com/pricing-list-quoting-tool-part-1/

Answer 2

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

Настройте два примера рабочих таблиц, « Locations и « Assets , например:

  

Добавьте два определенных имени:

1. Areas → =Locations!$A:$A
2. IDs → =Locations!$C:$C

Наконец, добавьте проверку данных в выпадающем списке в ячейке в C2 на листе Assets со следующей формулой, а затем заполните / скопируйте и вставьте ячейку вниз:

=INDEX(IDs,MATCH(A2,Areas,0)):INDEX(IDs,MATCH(A2,Areas,0)+COUNTIF(Areas,A2)-1)

Недостатки этого метода:

• Определенные имена должны быть использованы
• Таблица Locations должна быть отсортирована
• Другие таблицы на листе Locations не могут безопасно пересекать столбец Area если они не гарантированно не содержат значений, соответствующих значениям Area таблицы Location.
  
  

---

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

Рабочий лист Активы настроен с дополнительными столбцами:

На этот раз формула проверки данных C2 :

=IF(SUMPRODUCT(--ISNA(C2:INDEX(2:2,COLUMN(C2)-1+COUNTA(C2:INDEX(2:2,COLUMNS(2:2))))))=0,$I$1,C2:INDEX(2:2,COLUMN(C2)-1+COUNTA(C2:INDEX(2:2,COLUMNS(2:2)))-SUMPRODUCT(--ISNA(C2:INDEX(2:2,COLUMN(C2)-1+COUNTA(C2:INDEX(2:2,COLUMNS(2:2))))))))

Введите эту формулу в D2 и заполните:

=
IF(
  SUMPRODUCT(
    --ISNA(
      E2:INDEX(
        (2:2),
        COLUMN(E2)-1+MAX(1,COUNTA(E2:INDEX(2:2,COLUMNS(2:2))))
      )
    )
  )=0,
  "ERROR",
  "ok"
)

Многоэлементный массив - введите эту последнюю формулу в ячейки, начиная с E2 и доходя до максимального количества ожидаемых местоположений в области (для показанного примера я использовал E2:I2):

=
INDEX(
  Locations!$C:$C,
  IFERROR(
    SMALL(
      IFERROR(1/(1/(
        (Locations!$A$2:INDEX(Locations!$A:$A,COUNTA(Locations!$A:$A))=IF($A2="",NA(),$A2))
        *ROW($A$2:INDEX($A:$A,COUNTA(Locations!$A:$A)))
      )),FALSE ),
      COLUMN(INDEX(2:2,1):INDEX(2:2,MAX(2,COUNTIF(Locations!$A$2:INDEX(Locations!$A:$A,COUNTA(Locations!$A:$A)),$A2))))
    ),
    NA()
  )
)