Ich verwende eine umfangreiche Validierungsliste, von der einige Funktionen von vlookup () abhängen. Diese Liste wird immer größer. Gibt es eine Möglichkeit, die ersten Buchstaben des gesuchten Listenelements einzugeben, anstatt die Liste manuell nach unten durchzublättern und nach dem Element zu suchen?
Ich habe ein bisschen gegoogelt, aber dies legt nahe, dass dies in früheren Versionen von Excel tatsächlich möglich ist, nicht aber in Excel 2010. Hoffe, ihr könnt helfen.
Hier ist eine sehr gute Möglichkeit, damit umzugehen (gefunden auf ozgrid ):
Angenommen, Ihre Liste befindet sich auf Sheet2
und Sie möchten die Validierungsliste mit AutoVervollständigen auf Sheet1
verwenden.
Auf Sheet1
A1
Geben Sie =Sheet2!A1
ein und kopieren Sie so viele Ersatzzeilen wie erforderlich (sagen Sie 300 Zeilen insgesamt). Blenden Sie diese Zeilen aus und verwenden Sie diese Formel in den Verweisen auf: für einen dynamischen benannten Bereich namens MeineListe:
=OFFSET(Sheet1!$A$1,0,0,MATCH("*",Sheet1!$A$1:$A$300,-1),1)
Verwenden Sie nun in der Zelle unmittelbar unter der letzten ausgeblendeten Zeile die Datenüberprüfung und für die Listenquelle =MyList
.
[EDIT] Angepasste Version für Excel 2007+ (konnte 2010 jedoch nicht getestet werden, aber AFAIK, es gibt nichts wirklich Spezifisches für eine Version).
Angenommen, Ihre Datenquelle befindet sich in Sheet2!A1:A300
und Ihre Validierungsliste (auch bekannt als Autocomplete ) befindet sich in Zelle Sheet1!A1
.
Erstellen Sie einen dynamischen benannten Bereich MyList
, der vom Wert der Zelle abhängt, in die Sie die Validierung stellen
=OFFSET(Sheet2!$A$1,MATCH(Sheet1!$A$1&"*",Sheet2!$A$1:$A$300,0)-1,0,COUNTA(Sheet2!$A:$A))
Fügen Sie die Validierungsliste in die Zelle Sheet1!A1
ein, die auf die Liste =MyList
verweist.
Vorsichtsmaßnahmen
Dies ist keine echte automatische Vervollständigung, da Sie zuerst eingeben und dann auf den Überprüfungspfeil klicken müssen: Die Liste beginnt dann mit am ersten übereinstimmenden Element Ihrer Liste
Die Liste läuft bis zum Ende Ihrer Daten. Wenn Sie genauer sein möchten (behalten Sie in der Liste nur die übereinstimmenden Elemente), können Sie die COUNTA
mit einer SUMLPRODUCT
ändern, die die berechnet Anzahl übereinstimmender Elemente
Ihr Orderbuch muss sortiert sein
Excel führt dies automatisch aus, wenn Sie eine vertikale Spalte mit Elementen haben. Wenn Sie die leere Zelle unter (oder über) der Spalte auswählen und mit der Eingabe beginnen, wird die Vervollständigung automatisch anhand der gesamten Spalte ausgeführt.
Basierend auf der Antwort von JMax verwenden Sie diese Formel für den dynamischen benannten Bereich, damit die Lösung für mehrere Zeilen funktioniert:
=OFFSET(Sheet2!$A$1,MATCH(INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN(),4))&"*",Sheet2!$A$1:$A$300,0)-1,0,COUNTA(Sheet2!$A:$A))
=OFFSET(NameList!$A$2:$A$200,MATCH(INDIRECT("FillData!"&ADDRESS(ROW(),COLUMN(),4))&"*",NameList!$A$2:$A$200,0)-1,0,COUNTIF($A$2:$A$200,INDIRECT("FillData!"&ADDRESS(ROW(),COLUMN(),4))&"*"),1)
Erstellen Sie den Blattnamen als Namelist
. In Spalte A Datenliste füllen.
Erstellen Sie einen anderen Blattnamen als FillData
, um die Liste zur Validierung der Daten nach Ihren Wünschen zu erstellen.
Geben Sie das erste Alphabet ein und wählen Sie. Das Dropdown-Menü wird abhängig von Ihrer Eingabe angezeigt.
Wie andere Leute vorgeschlagen haben, müssen Sie eine Combobox verwenden. Die meisten Tutorials zeigen Ihnen jedoch, wie Sie nur eine Combobox einrichten, und der Vorgang ist ziemlich langwierig.
Da ich bei der Eingabe einer großen Datenmenge aus einer Liste zuvor auf dieses Problem gestoßen bin, kann ich vorschlagen, dass Sie dieses automatisch vervollständigende Add-In verwenden. Es hilft Ihnen, das Kombinationsfeld für die ausgewählten Zellen zu erstellen, und Sie können eine Liste definieren, die in der Dropdown-Liste angezeigt wird.
Hier ist eine weitere Option. Es funktioniert, indem eine ActiveX-ComboBox mit aktivierter Validierung über der Zelle platziert wird und dann stattdessen Autocomplete in der ComboBox bereitgestellt wird.
Option Explicit
' Autocomplete - replacing validation lists with ActiveX ComboBox
'
' Usage:
' 1. Copy this code into a module named m_autocomplete
' 2. Go to Tools / References and make sure "Microsoft Forms 2.0 Object Library" is checked
' 3. Copy and paste the following code to the worksheet where you want autocomplete
' ------------------------------------------------------------------------------------------------------
' - autocomplete
' Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' m_autocomplete.SelectionChangeHandler Target
' End Sub
' Private Sub AutoComplete_Combo_KeyDown(ByVal KeyCode As msforms.ReturnInteger, ByVal Shift As Integer)
' m_autocomplete.KeyDownHandler KeyCode, Shift
' End Sub
' Private Sub AutoComplete_Combo_Click()
' m_autocomplete.AutoComplete_Combo_Click
' End Sub
' ------------------------------------------------------------------------------------------------------
' When the combobox is clicked, it should dropdown (expand)
Public Sub AutoComplete_Combo_Click()
Dim ws As Worksheet: Set ws = ActiveSheet
Dim cbo As OLEObject: Set cbo = GetComboBoxObject(ws)
Dim cb As ComboBox: Set cb = cbo.Object
If cbo.Visible Then cb.DropDown
End Sub
' Make it easier to navigate between cells
Public Sub KeyDownHandler(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Const UP As Integer = -1
Const DOWN As Integer = 1
Const K_TAB_______ As Integer = 9
Const K_ENTER_____ As Integer = 13
Const K_ARROW_UP__ As Integer = 38
Const K_ARROW_DOWN As Integer = 40
Dim direction As Integer: direction = 0
If Shift = 0 And KeyCode = K_TAB_______ Then direction = DOWN
If Shift = 0 And KeyCode = K_ENTER_____ Then direction = DOWN
If Shift = 1 And KeyCode = K_TAB_______ Then direction = UP
If Shift = 1 And KeyCode = K_ENTER_____ Then direction = UP
If Shift = 1 And KeyCode = K_ARROW_UP__ Then direction = UP
If Shift = 1 And KeyCode = K_ARROW_DOWN Then direction = DOWN
If direction <> 0 Then ActiveCell.Offset(direction, 0).Activate
AutoComplete_Combo_Click
End Sub
Public Sub SelectionChangeHandler(ByVal Target As Range)
On Error GoTo errHandler
Dim ws As Worksheet: Set ws = ActiveSheet
Dim cbo As OLEObject: Set cbo = GetComboBoxObject(ws)
Dim cb As ComboBox: Set cb = cbo.Object
' Try to hide the ComboBox. This might be buggy...
If cbo.Visible Then
cbo.Left = 10
cbo.Top = 10
cbo.ListFillRange = ""
cbo.LinkedCell = ""
cbo.Visible = False
Application.ScreenUpdating = True
ActiveSheet.Calculate
ActiveWindow.SmallScroll
Application.WindowState = Application.WindowState
DoEvents
End If
If Not HasValidationList(Target) Then GoTo ex
Application.EnableEvents = False
' TODO: the code below is a little fragile
Dim lfr As String
lfr = Mid(Target.Validation.Formula1, 2)
lfr = Replace(lfr, "INDIREKTE", "") ' norwegian
lfr = Replace(lfr, "INDIRECT", "") ' english
lfr = Replace(lfr, """", "")
lfr = Application.Range(lfr).Address(External:=True)
cbo.ListFillRange = lfr
cbo.Visible = True
cbo.Left = Target.Left
cbo.Top = Target.Top
cbo.Height = Target.Height + 5
cbo.Width = Target.Width + 15
cbo.LinkedCell = Target.Address(External:=True)
cbo.Activate
cb.SelStart = 0
cb.SelLength = cb.TextLength
cb.DropDown
GoTo ex
errHandler:
Debug.Print "Error"
Debug.Print Err.Number
Debug.Print Err.Description
ex:
Application.EnableEvents = True
End Sub
' Does the cell have a validation list?
Function HasValidationList(Cell As Range) As Boolean
HasValidationList = False
On Error GoTo ex
If Cell.Validation.Type = xlValidateList Then HasValidationList = True
ex:
End Function
' Retrieve or create the ComboBox
Function GetComboBoxObject(ws As Worksheet) As OLEObject
Dim cbo As OLEObject
On Error Resume Next
Set cbo = ws.OLEObjects("AutoComplete_Combo")
On Error GoTo 0
If cbo Is Nothing Then
'Dim EnableSelection As Integer: EnableSelection = ws.EnableSelection
Dim ProtectContents As Boolean: ProtectContents = ws.ProtectContents
Debug.Print "Lager AutoComplete_Combo"
If ProtectContents Then ws.Unprotect
Set cbo = ws.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, _
Left:=50, Top:=18.75, Width:=129, Height:=18.75)
cbo.name = "AutoComplete_Combo"
cbo.Object.MatchRequired = True
cbo.Object.ListRows = 12
If ProtectContents Then ws.Protect
End If
Set GetComboBoxObject = cbo
End Function