☰

Excel 2013 — Проверка данных — Создание раскрывающегося списка со значениями при соответствии второму критерию

tag-icon • tag-icon microsoft-excel vba
Excel 2013 — Проверка данных — Создание раскрывающегося списка со значениями при соответствии второму критерию

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

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

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

С нетерпением жду ваших ответов, ребята! Было бы здорово получить решение на Excel или VBA.

решение1

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

=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/

решение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 не могут безопасно пересекать столбец, Areaесли только они не гарантированно не содержат значений, совпадающих со значениями таблицы Location Area.

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

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

Скриншот рабочего листа «Активы»

На этот раз 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()
  )
)

Связанный контент