Ich habe eine Liste mit IDs und Beschreibungen in Blatt 1. Diese Daten werden dann in einem zweiten Blatt (Blatt 2) verwendet, mit dem der Benutzer interagieren kann.
Die Liste in Blatt 1 hat eine ID, die sich wiederholt, aber eine andere Beschreibung hat. Wie könnte ich die Datenüberprüfung in Blatt 2 verwenden, um alle Wiederholungen der ID-Nummer 4 zu 1 zu kombinieren, dem Benutzer aber die Auswahl der gewünschten Beschreibung (mithilfe eines Dropdown-Felds) zu ermöglichen, nachdem die Auswahl in Spalte A getroffen wurde?
Ich möchte hierfür auf Makros verzichten und nur integrierte Funktionen verwenden.
Jede Hilfe ist willkommen!
Antwort1
Kombinieren Sie die Wiederholungen in Blatt1. Nachschlageformeln geben nur den ersten gefundenen Wert zurück. Wenn Sie also mehrere Werte mit derselben ID haben, müssen Sie stattdessen eine Nachschlagefunktion mit eindeutigen IDs erstellen. Bereiten Sie diese in einer Hilfstabelle vor und verwenden Sie die Hilfstabelle für die Datenüberprüfung, wenn Sie die ursprüngliche Tabelle nicht ändern möchten oder können.
Es ist ziemlich irreführend, dass ein Bezeichner (der nicht ohne Grund so heißt) nicht eindeutig ist.
Antwort2
Sie haben nicht erwähnt, welche Version von Excel Sie verwenden. Ich schlage eine mögliche Lösung vor, die auf meinem Verständnis Ihrer Frage basiert. Es werden jedoch auch Funktionen namens IFERROR & COUNTBLANK verwendet, die meiner Meinung nach vor Excel 2007 nicht verfügbar sind. Wenn Sie also Excel 2003 haben, ist möglicherweise ein anderer Ansatz erforderlich.
Dies ist möglicherweise keine ideale Lösung, da Hilfsspalten und auch ein Hilfsblatt verwendet werden!
In diesem Beispiel befinden sich die Beispieldaten in Blatt1!A2:B10, wie in diesem Screenshot zu sehen.
Zuerst müssen wir in Spalte D eine Hilfsspalte erstellen, um die eindeutige Liste aus Ihren IDs zu erhalten.
Geben Sie in D2 die folgende Formel ein und drücken Sie dann in der Formelleiste CTRL+ SHIFT+, um eine Array-Formel zu erstellen. Damit dies richtig funktioniert, müssen Sie auch eine Zeile (vorzugsweise die erste Zeile) als Überschrift freilassen.ENTER
Die Formel muss in geschweifte Klammern eingeschlossen sein, um anzuzeigen, dass es sich um eine Array-Formel handelt.
Ziehen Sie es nach unten bis zu den gewünschten Zeilen, basierend auf der erwarteten Anzahl eindeutiger Werte in Ihrer Hauptspalte. Irgendwann werden unten Leerzeichen zurückgegeben, dann können Sie aufhören. Dadurch wird eine eindeutige Hauptliste mit IDs erstellt.
=IFERROR(INDEX($A$2:$A$10, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$10), 0)),"")
Jetzt müssen wir den genauen Anfang und das Ende dieser Liste kennen, um die Validierungsliste zu füllen.
Geben Sie nun in G3 die folgende Formel ein.
=INDEX(Sheet1!D2:D10,1)
und geben Sie in G4 die folgende Array-Formel ein.
=MIN(IF(Sheet1!D2:D10="",ROW(Sheet1!D2:D10),9^99))-2
Vergessen Sie hierfür nicht CTRL+ SHIFT+ .ENTER
Gehen Sie nun zum Namensmanager und erstellen Sie einen neuen Namen namens MyList
Setzen Sie darin folgende Formel ein
=INDEX(Sheet1!$D$2:$D$10,Sheet1!$G$3):INDEX(Sheet1!$D$2:$D$10,Sheet1!$G$4)
Hier verwenden wir INDEX, um eine Zellreferenz anstelle eines Wertes zurückzugeben
Fügen Sie ein neues Blatt mit dem Namen Sheet2 ein.
Erstellen Sie nun in Spalte A2 und darunter eine Validierungsliste und fügen Sie =MyListdiese ein.
Jetzt werden alle eindeutigen Werte in der ersten Dropdown-Liste für IDs angezeigt.
Damit ist Teil 1 abgeschlossen.
Die nächste Aufgabe besteht nun darin, eine zweite Validierungsliste basierend auf den aus der Beschreibungsspalte extrahierten Daten zu füllen.
Fügen Sie ein drittes Hilfsblatt mit dem Namen Sheet3 ein. Sie können dieses Blatt einfach ausblenden.
In diesem Beispiel habe ich Daten aus Blatt3!B2:H10 genommen. Legen Sie die Anzahl der Spalten basierend auf der maximalen Anzahl von Duplikaten in Ihren IDs aus der Hauptliste fest. Lassen Sie auch die erste Spalte A frei, damit dies richtig funktioniert.
Geben Sie in B2 die folgende Formel ein und drücken Sie in der Formelleiste CTRL+ SHIFT+ , um eine Array-Formel zu erstellen.ENTER
Ziehen Sie es wie vorgesehen nach unten und unten.
=IFERROR(INDEX(Sheet1!$B$2:$B$10, SMALL(IF(Sheet2!$A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$A$2:$A$10)-1,999),COLUMN()-COLUMN($A$2))),"")
Erstellen Sie im Namens-Manager einen neuen Namen mit dem Namen „Trimmed“ und fügen Sie die folgende Formel ein.
=OFFSET(Sheet3!$B1,0,0):OFFSET(Sheet3!$B1,0,COUNTA(Sheet3!$B1:$H1)-COUNTBLANK(Sheet3!$B1:$H1)-1)
Dadurch wird eine Liste ohne Leerzeichen erstellt, sodass die Liste richtig gekürzt wird.
Erstellen Sie nun in Blatt2 für die Zellen B2 und abwärts eine Validierungsliste und fügen Sie sie =Trimmeddort ein.
Wenn sich Ihre Daten jetzt in Sheet1!A2:A10 ändern, wird die Liste der eindeutigen Werte automatisch in MyList angezeigt und außerdem ruft der Name „Trimmen“ nur die beabsichtigte Beschreibung aus der Beschreibungsspalte von Sheet1 ab.
Sehen Sie sich den folgenden Bildschirm als GIF an, um ein Gefühl dafür zu bekommen, wie das Ganze funktioniert.
