webentwicklung-frage-antwort-db.com.de

Excel 2010: Verwendung der automatischen Vervollständigung in der Validierungsliste

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.

19
Pieter

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 Sheet1A1 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.

  1. 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))

  2. Fügen Sie die Validierungsliste in die Zelle Sheet1!A1 ein, die auf die Liste =MyList verweist.

Vorsichtsmaßnahmen

  1. 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

  2. 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

  3. Ihr Orderbuch muss sortiert sein

11
JMax

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.

1
Joel Spolsky

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))
1
gwapongkabayo
=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)
  1. Erstellen Sie den Blattnamen als Namelist. In Spalte A Datenliste füllen.

  2. Erstellen Sie einen anderen Blattnamen als FillData, um die Liste zur Validierung der Daten nach Ihren Wünschen zu erstellen.

  3. Geben Sie das erste Alphabet ein und wählen Sie. Das Dropdown-Menü wird abhängig von Ihrer Eingabe angezeigt.

0
Anan Phungmit

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.

0
Donna C

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
0
tomashm