Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Matrix-Formel zum Füllen Drop Down

Forumthread: Matrix-Formel zum Füllen Drop Down

Matrix-Formel zum Füllen Drop Down
21.04.2018 12:41:36
Hansi
Hallo,
gleich vorneweg: Ich habe die Anforderung bereits umgesetzt, allerdings mit einer Hilfstabelle und möchte jetzt eigentlich nur wissen, ob das so notwendig war.
Ausgangslage: Eine Tabelle mit 2 Spalten, Spalte 1 "Legal Entity", Spalte 2 "Profit Center". Eine "Legal Entity" kann dabei mehrfach vorkommen, da eine "Legal Entity" mehrere "Profit Center" hat, also:
A A1
A A2
B B1
B B2
Es soll nur in einer Tabelle ein Dropdown-Feld (Datengültigkeit) eingebaut werden, in dem in Abhängigkeit von einer Auswahl einer "Legal Entity" die gültigen Profit Center vorgeschlagen werden.
In der jetzigen Version gibt es eine Hilfstabelle, in der mit folgender Formel alle Profit Center zu einer ausgewählten "Legal Entity" zusammen gestellt werden:
{=WENNFEHLER(INDEX('Master Data'!A:A;KKLEINSTE(WENN('Master Data'!$J$1:$J$100='Entry Sheet'!$B$3; ZEILE($1:$100));ZEILE(A1)));"") }
Ich muss gestehen, ich verstehe die Formel nur zur Hälfte, mit Matrix-Formeln stehe ich leicht auf Kriegsfuß. Der Bereich A:A stellt die Profit Center dar, die Spalte J die Legal Entity, B3 ist die Auswahl der "Legal Entity".
Ich frage mich, ob es eine Matrix-Formel gibt, die direkt als Quelle für die Datengültigkeit gibt, oder ob diese Hilfstabelle notwendig ist.
Besten Dank im Voraus.
Gruß,
hansi
Anzeige

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: zu Deinen Fragen im letzten Satz ...
21.04.2018 14:51:45
...
Hallo Hansi,
... zunächst gilt für Deine Excelversion, dass da für eine Dropdownzelle keine direkte Definition durch eine Matrixformel möglich ist. Dies geht nur über eine im Namensmanager definierte benannte Formel. Dazu benötigt man dann aber nicht zwingend einer Hilfsspalte und auch keiner klassischen Matrixformel.
Deine weiteren Angaben sind mE jedoch nicht eindeutig genug, so dass ich momentan keine konkreteren Aussagen treffen kann. Eine Beispieltabelle wäre hilfreicher als nur Deine Formelangabe.
Gruß Werner
.. , - ...
Anzeige
AW: zu Deinen Fragen im letzten Satz ...
22.04.2018 14:11:34
Hansi
Hallo Werner,
vielen Dank für deine Antwort. Ich habe gerade mal geschaut, meine gegenwärtige Version ist 2016 - aber das muss nicht für alle Anwender gelten...
Zu deiner Aussage "nur über eine im Namensmanager definierte benannte Formel" - das habe ich in der Tat noch nie ausprobiert, probier mich nachher mal dran.
Ich habe eine Beispieldatei mal hochgeladen:
- Tabelle "Entry Sheet" in Zelle B3 Auswahl einer Entity
- Tabelle "Help for Selection", Spalte A (Range "Profit Center") Ermittlung aller Profit Center zu dieser Entity
- Tabelle "Entry Sheet" in Zellen B9 - B100: User kann hier nun nur die Profit Center auswählen aus der Range "Profit Center".
Ich hoffe, mein Vorgehen konnte ich deutlich machen, ansonsten melde dich gerne.
Vielen Dank im Voraus.
Lg,
Hansi
Anzeige
AW: hierzu folgendes ...
22.04.2018 20:08:39
...
Hallo Hansi,
... ich geh nachfolgend davon aus, dass Deine Anwender zumindest Excel 2010 im Einsatz haben. Ich würde die Daten in "Master Data" in eine "intelligente" Tabelle formatieren, was den Vorteil für die Formellösung hätte, dass der Auswertungsbereich immer auf die wirklich vorhanden auszuwertenden Datensätze bezieht.
In nachfolgender Formellösung hab mich jedoch auf die Datenstruktur Deiner eingestellten Datei bezogen und darin angenommen, dass max 99 Datenzeilen in "Master Data" vorhanden sind.
Die Hilfsspalte C:C in "Help for Selection" ist notwendig, allerdings kannst Du wie folgt die Daten einfacher ermitteln.
In C3:

=WENNFEHLER(AGGREGAT(15;6;'Master Data'!J$2:J$99/('Master Data'!J$2:J$99>C2);1);"") 
und Formel nach unten kopieren.
Im Namensmanager änderst Du darauf hin die Definition für Company neu wie folgt:
='Help for Selection'!$C$3:INDEX('Help for Selection'!$C:$C;ANZAHL('Help for Selection'!$C:$C)+2)
Damit werden "Entry Sheet"!B3 immer nur die vorhandenen Company (ohne Leerzellen) in der Dropdownzelle angeboten.
Die Hilfsspalte A:A in "Help for Selection" ist nicht notwendig, wenn Du bei aktivierter (!) Zelle "Entry Sheet"!B9 im Namensmanager die Definition für Profit_Center wie folgt neu definierst:
=INDEX('Master Data'!A:A;VERGLEICH('Entry Sheet'!B$3;'Master Data'!J:J;0)):INDEX('Master Data'!A:A;VERGLEICH('Entry Sheet'!B$3;'Master Data'!J:J;0)-1+ZÄHLENWENN('Master Data'!J:J;'Entry Sheet'!$B$3))
Nun wird in den Dropdownzellen B9:B### auch hier immer nur die Profit Center gemäß Auswahl in B3 und auch ohne unnötige Leerzellen angeboten.
Einfacher ist für die anderen DropdownZellen. Z.B. Inventory_category würde ich im Namensmanager umdefinieren zu:
='Help for Selection'!$E$3:INDEX('Help for Selection'!$E:$E;ANZAHL2('Help for Selection'!$E:$E)+1)
analog auch für ....
Bei Rückfragen hierzu wisse, ich werde voraussichtlich nicht vor Dienstag/Mittwoch dazukommen zu antworten.
Gruß Werner
.. , - ...
Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

Matrix-Formel für Dropdown-Listen in Excel


Schritt-für-Schritt-Anleitung

  1. Vorbereitung der Daten: Erstelle eine Tabelle mit den Spalten "Legal Entity" und "Profit Center". Achte darauf, dass die Daten klar strukturiert sind.

  2. Hilfstabelle anlegen: Falls du eine Hilfstabelle benötigst, lege eine neue Tabelle an, in der du alle relevanten Profit Center für die ausgewählte Legal Entity zusammenstellst. Du kannst die Formel nutzen:

    {=WENNFEHLER(INDEX('Master Data'!A:A;KKLEINSTE(WENN('Master Data'!$J$1:$J$100='Entry Sheet'!$B$3;  ZEILE($1:$100));ZEILE(A1)));"")}
  3. Namensmanager verwenden: Gehe in den Namensmanager und definiere eine benannte Formel für die Dropdown-Liste. Zum Beispiel:

    =INDEX('Master Data'!A:A;VERGLEICH('Entry Sheet'!B$3;'Master Data'!J:J;0)):INDEX('Master Data'!A:A;VERGLEICH('Entry Sheet'!B$3;'Master Data'!J:J;0)-1+ZÄHLENWENN('Master Data'!J:J;'Entry Sheet'!$B$3))
  4. Dropdown erstellen: Wähle die Zelle, in der du das Dropdown-Menü haben möchtest. gehe zu „Daten“ > „Datengültigkeit“ und wähle „Liste“ aus. Gib die zuvor definierte benannte Formel in das Feld „Quelle“ ein.

  5. Testen: Überprüfe, ob die Dropdown-Liste die richtigen Werte anzeigt, wenn du eine "Legal Entity" auswählst.


Häufige Fehler und Lösungen

  • Fehler: Dropdown zeigt leere Werte an: Stelle sicher, dass die benannte Formel korrekt definiert ist und keine Leerzellen in den Daten vorhanden sind. Überprüfe auch die Formeln in der Hilfstabelle.

  • Fehler: Matrix-Formel funktioniert nicht: In älteren Excel-Versionen ist es nicht möglich, eine Matrix-Formel direkt für eine Dropdown-Liste zu verwenden. Nutze stattdessen den Namensmanager.

  • Fehler: Ungültiger Bereich: Achte darauf, dass die angegebenen Bereiche in der Formel korrekt sind und die maximale Anzahl an Datenzeilen nicht überschreiten.


Alternative Methoden

Eine Alternative zur Verwendung von Hilfstabellen ist die Anwendung von „intelligenten Tabellen“. Wenn du deine Daten in eine intelligente Tabelle umwandeln, wird der Bereich dynamisch angepasst und du kannst einfacher auf die Daten zugreifen.

  1. Intelligente Tabelle erstellen: Wähle deine Daten aus und gehe zu „Einfügen“ > „Tabelle“. Aktiviere die Option „Meine Tabelle hat Überschriften“.

  2. Verwenden der intelligenten Tabelle in Formeln: Anstelle von festen Zellbereichen kannst du die Tabellennamen in deinen Formeln verwenden, um diese flexibler zu gestalten.


Praktische Beispiele

  • Beispiel 1: Wenn du in der Dropdown-Liste „Legal Entity“ A auswählst, sollten in der Dropdown-Liste für „Profit Center“ die Werte A1 und A2 angezeigt werden.

  • Beispiel 2: Mit der intelligenten Tabelle kannst du auch eine Liste von Produkten filtern, sodass nur die verfügbaren Produkte angezeigt werden, wenn der Benutzer eine Kategorie auswählt.


Tipps für Profis

  • Formeloptimierung: Nutze die AGGREGAT-Funktion anstelle von KKLEINSTE, da diese Funktion flexibler ist und Fehler ignorieren kann.

  • Dynamische Bereiche: Definiere deine Bereiche dynamisch, um sicherzustellen, dass sie sich automatisch anpassen, wenn neue Daten hinzukommen.

  • Formeln dokumentieren: Halte deine Formeln gut dokumentiert, damit andere Benutzer (oder du selbst in der Zukunft) schnell verstehen, wie die Dropdowns funktionieren.


FAQ: Häufige Fragen

1. Kann ich Matrix-Formeln direkt in Dropdown-Listen verwenden?
Nein, in Excel-Versionen vor 2019 ist dies nicht möglich. Du musst eine benannte Formel im Namensmanager definieren.

2. Wie kann ich die Dropdown-Liste dynamisch gestalten?
Indem du deine Daten in eine intelligente Tabelle umwandelst, kannst du sicherstellen, dass die Dropdown-Liste immer die aktuellen Werte anzeigt.

3. Was mache ich, wenn meine Dropdown-Liste nicht aktualisiert wird?
Überprüfe die Definitionen im Namensmanager und stelle sicher, dass die verwendeten Bereiche korrekt sind. Achte darauf, dass keine leeren Zellen in den Daten vorhanden sind.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige