☰

Wie erstelle ich in Excel 2010 eine Dropdown-Liste für eine Spalte mit ständig wechselnden Werten?

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
Wie erstelle ich in Excel 2010 eine Dropdown-Liste für eine Spalte mit ständig wechselnden Werten?

Ich habe eine Excel-Tabelle mit einer Spalte für Jahre und einer Überschrift:

Years
1993
1993
1994
1994
1994
...
2011
2011

Für die Jahre gibt es doppelte Werte und im Laufe der Zeit werden weitere Zeilen hinzugefügt.

Ich habe eine andere Zelle, die eine Dropdown-Liste für die Jahre anzeigen muss, aber nur die eindeutigen Jahre. Ich habe versucht, die Datenüberprüfungsfunktion in Excel 2011 zu verwenden, aber es gibt zwei Probleme:

  1. Es werden die doppelten Jahre angezeigt.
  2. Ich weise es an, die gesamte Spalte zu verwenden, und es schließt die leeren Zellen in die Dropdown-Liste ein.

Wie erhalte ich eine Dropdown-Liste mit Jahren, in der nur eindeutige Werte angezeigt werden und die automatisch aktualisiert wird, wenn zusätzliche Zeilen hinzugefügt werden?

Bearbeiten: ein paar weitere Informationen. Die Dropdown-Liste wird in einem separaten Blatt verwendet, um berechnete Daten anzuzeigen, wie in einem Access-Formular. Der Benutzer kann einen Jahresbereich auswählen und die Daten werden entsprechend aktualisiert. Das Originalblatt ist nur eine Liste aller Daten.

Antwort1

Für diese Art von Validierungen verwende ich VBA + einen schmutzigen Trick:

Rufen Sie zunächst den VBA-Editor mit Alt+F11 auf. Dann füge ich meinen „Dynamic List Validation Code“ (tm) :) in das entsprechende Arbeitsblatt ein.

Private Sub Worksheet_SelectionChange(ByVal rTarget As Excel.Range)

On Error GoTo noVal

With rTarget.Validation
    .Modify xlValidateList, xlValidAlertStop, xlBetween, Excel.Evaluate(.ErrorTitle)
End With

noVal:

End Sub

Dieser Code aktualisiert die Zellvalidierungsliste mit der Liste, die durch die in Daten->Validierung->Fehlermeldung->Titel eingegebene Formel generiert wurde. Auf diese Weise kann jede Zelle mit Listenvalidierung ihre eigene Formel haben.

Dann füge ich ein Modul hinzu (Einfügen->Modul) und füge anschließend diesen Code in das neue Modul ein:

Function GenDynList(rRng As Range)

sRet = ""

For Each rCell In rRng
    If Not IsEmpty(rCell.Value) And InStr(sRet, rCell.Value) = 0 Then
        sRet = sRet & "," & rCell.Value
    End If
Next

GenDynList = Mid(sRet, 2)

End Function

Diese Funktion gibt alle Zellen im Bereich ohne Leerzeichen oder Wiederholungen zurück. Dann füge ich in jeder Zelle mit Listenvalidierung „GenDynList(range)“ in den Titel der Fehlermeldung der Datenvalidierung ein.

Antwort2

Unordentlich. Es gibt keine integrierte Möglichkeit, dies automatisch zu aktualisieren. Es wäre einfacher, einfach eine separate Liste mit allen möglichen Jahren zu erstellen, die Sie interessieren könnten, anstatt zu versuchen, sie auf die in Ihrem Datensatz enthaltenen zu beschränken.

Antwort3

Fügen Sie eine Pivot-Tabelle aus den Daten eines anderen Blattes hinzu. Verwenden Sie Jahre als Zeilen, der Rest der Tabelle ist irrelevant. Sortieren und filtern Sie die Zeilen nach Bedarf (z. B. explizit „[leer]“ herausfiltern). Machen Sie die Zellen, in denen sich die Zeilenbeschriftungen befinden, zum Ziel Ihrer Datenüberprüfung.

Verwenden Sie einen erweiterbaren benannten Bereich, um immer alle neuen Beschriftungen zu verwenden: http://www.ozgrid.com/Excel/DynamicRanges.htm

Wiederholen Sie dies für andere Spalten wie Land, Staat usw. Wenn Sie alle Pivots auf demselben Datenbereich basieren, werden sie alle gemeinsam aktualisiert, wenn Sie eine davon aktualisieren. Ihr Prozess sieht nun so aus: Neue Daten hinzufügen, Pivots aktualisieren, aktualisierte Datenüberprüfung verwenden.

Weitere Überlegungen: Die Verwendung einer Tabelle für die Quelldaten erleichtert das Aktualisieren der Pivot-Tabellen, da diese immer die gesamte Tabelle verwenden. Alternativ können Sie einen erweiterbaren benannten Bereich als Datenquelle verwenden, um Probleme beim Hinzufügen weiterer Zeilen zu vermeiden.

Beachten Sie, dass Sie einen Bereich aus einem anderen Arbeitsblatt als Quelle für die Datenüberprüfung verwenden können, wenn Sie dazu einen benannten Bereich definieren. Sie können keinen normalen Bereich verwenden, der explizit auf ein anderes Blatt verweist.

verwandte Informationen