☰

Excel 2013 – Datenüberprüfung – Erstellen Sie eine Dropdown-Liste mit Werten, wenn ein zweites Kriterium erfüllt ist

tag-icon • tag-icon microsoft-excel vba
Excel 2013 – Datenüberprüfung – Erstellen Sie eine Dropdown-Liste mit Werten, wenn ein zweites Kriterium erfüllt ist

Ich werde mein Problem so weit wie möglich vereinfachen. Ich habe zwei Tabellenblätter in Excel. Im ersten Blatt erstelle ich eine Liste mit Standorten. Ich muss in Spalte A ein Gebiet auswählen, in dem sich der Standort befindet, bevor ich den Standort in Spalte B eingeben kann. In der nächsten Spalte (C) wird eine ID angezeigt, die automatisch erstellt wird (für den ersten Standort ID-001, für den zweiten die ID 002 usw.).

In meinem zweiten Blatt erstelle ich eine Liste von Vermögenswerten. Dazu wähle ich wieder einen Bereich in Spalte A und gebe den Namen des Vermögenswerts in Spalte B ein. Jetzt möchte ich eine Dropdown-Liste in Spalte C haben, in der mir nur die IDs aus der ersten Tabelle angezeigt werden, deren Bereich derselbe ist. Ich habe versucht, die OFFSET-Funktion zu verwenden, aber was fehlt, ist eine Art „Auswählen, wenn“ (wie Zählenwenn oder Summewenn), um die Liste der IDs auf diejenigen zu beschränken, die denselben Bereich haben.

In jedem Gebiet kann es viele Standorte und Vermögenswerte geben, aber jeder Standort und jeder Vermögenswert kann nur einem Standort zugeordnet werden.

Ich freue mich auf eure Antworten, Leute! Entweder eine Excel-Lösung oder eine VBA-Lösung wäre super.

Antwort1

Ich schlage vor, eine Hilfsspalte zu erstellen, die eindeutige Werte enthält, die in SVERWEIS verwendet werden können. Nehmen wir beispielsweise an, Sie haben Area1, Area2, Area3 und Area1 (wieder) und so weiter. Fügen Sie diese Werte mit inkrementellen Werten zusammen (z. B. 01, 02, 03, ...) und erstellen Sie 01Area1, 01Area2, 01Area3 und02Bereich1. Jetzt wissen Sie, was Sie für jedes Listenelement im zweiten Blatt aufrufen müssen. Wenn Sie auswählenBereich1in Spalte A, dann müssen Sie suchenfortlaufende NummerundBereich1. Inkrementelle Nummern nach Werten können erstellt werden durchZÄHLENWENNFunktion, die den erweiterten Bereich nutzt:

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

Durch die Bereichserweiterung zählt die Formel nur die Werte bis zur eigenen Zeile. Einziges Handicap dieser Methode ist, dass Sie Listenelemente inanderswo

Hier zwei Links, die eine ähnliche Verwendung beschreiben:

  1. https://www.spreadsheetweb.com/wie-man-nth-match-mit-vlookup-erhält/
  2. https://www.spreadsheetweb.com/pricing-list-quoting-tool-part-1/

Antwort2

Vorausgesetzt, es ist akzeptabel, die Tabelle „Standorte“ zu sortieren, besteht die einfachste Lösung nur aus einer relativ kurzen Datenüberprüfungsformel und einigen benannten Bereichen. Es sind keine Hilfsspalten oder -zeilen erforderlich.

Richten Sie zwei Beispielarbeitsblätter ein, Locationsund Assetszwar wie folgt:

Screenshot des Arbeitsblatts „Standorte“  Screenshot des Arbeitsblatts „Assets“

Fügen Sie zwei definierte Namen hinzu:

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

Fügen Sie abschließend mit der folgenden Formel eine In-Cell-Dropdown-Liste zur Datenüberprüfung C2in das AssetsArbeitsblatt ein und füllen Sie die Zelle anschließend aus bzw. kopieren Sie sie und fügen Sie sie nach unten ein:

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

Die Nachteile dieser Methode sind:

  • Es müssen definierte Namen verwendet werden
  • Die Tabelle Standorte muss sortiert sein
  • Andere Tabellen im Arbeitsblatt „Standorte“ können die AreaSpalte nicht sicher schneiden, es sei denn, es kann garantiert werden, dass sie keine Werte enthalten, die mit den Werten der Tabelle „Standort“ übereinstimmen Area.

Die folgende alternative Lösung überwindet die Nachteile mit Ausnahme desjenigen, der sich auf andere Tabellen bezieht. Allerdings werden Zellen im Arbeitsblatt „Assets“ verwendet, um die Elemente der Dropdown-Listen zu speichern.

Das Arbeitsblatt „Vermögenswerte“ ist mit zusätzlichen Spalten eingerichtet:

Screenshot des Arbeitsblatts „Assets“

Dieses Mal C2lautet die Formel zur Datenüberprüfung:

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

Geben Sie diese Formel ein D2und füllen Sie sie aus:

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

Mehrzelliges Array - geben Sie diese letzte Formel in die Zellen ein, beginnend bei E2und so weit rechts wie die maximale Anzahl der erwarteten Standorte in einem Bereich (für das gezeigte Beispiel habe ich verwendet 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()
  )
)

verwandte Informationen