Is uirlis iontach iad liostaí anuas Excel chun comhsheasmhacht sonraí agus éascaíocht iontrála a chinntiú. Mar sin féin, de réir réamhshocraithe, cuireann siad srian ort le mír amháin a roghnú. Ach cad a tharlóidh má theastaíonn uait go leor míreanna a roghnú ón liosta anuas céanna? Scrúdóidh an treoir chuimsitheach seo modhanna chun roghanna iolracha a chumasú i liostaí anuas Excel, dúbailt a bhainistiú, deighilteoirí saincheaptha a shocrú, agus raon feidhme na liostaí sin a shainiú.

• Ag baint úsáide as cód VBA
• Ag baint úsáide as Kutools le haghaidh Excel i gceann cúpla cad a tharlaíonn

• Míreanna dúblacha a cheadú
• Aon nithe atá ann cheana a bhaint
• Deighilteoir saincheaptha a shocrú
• Socrú raon sonraithe
• Forghníomhú i mbileog oibre cosanta

Ag Cumasú Ilroghanna sa Liosta Anuas

Soláthraíonn an chuid seo dhá mhodh chun cabhrú leat roghanna iolracha a chumasú sa liosta anuas in Excel.

Ag baint úsáide as Cód VBA

Chun roghanna iolracha a cheadú sa liosta anuas, is féidir leat é a úsáid Visual Basic d’Iarratais (VBA) in Excel. Is féidir leis an script iompar liosta anuas a mhodhnú chun liosta ilroghnacha a dhéanamh. Déan mar seo a leanas le do thoil.

Céim 1: Oscail an Eagarthóir Bileog (Cód).

1. Oscail an bhileog oibre ina bhfuil an liosta anuas ar mhaith leat ilroghnú a chumasú.
2. Cliceáil ar dheis ar an gcluaisín bileog agus roghnaigh Féach an cód ón roghchlár comhthéacs.
   

Céim 2: Bain úsáid as cód VBA

Anois cóipeáil an cód VBA seo a leanas agus greamaigh é chuig an bhfuinneog oscailte (Cód).

Cód VBA: Cumasaigh roghanna iolracha i liosta anuas Excel.

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim delimiter As String
    Dim TargetRange As Range

    Set TargetRange = Me.UsedRange ' Users can change target range here
    delimiter = ", " ' Users can change the delimiter here

    If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
    On Error Resume Next
    Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False

    xValue2 = Target.Value
    Application.Undo
    xValue1 = Target.Value
    Target.Value = xValue2
    If xValue1 <> "" And xValue2 <> "" Then
        If Not (xValue1 = xValue2 Or _
                InStr(1, xValue1, delimiter & xValue2) > 0 Or _
                InStr(1, xValue1, xValue2 & delimiter) > 0) Then
            Target.Value = xValue1 & delimiter & xValue2
        Else
            Target.Value = xValue1
        End If
    End If

    Application.EnableEvents = True
    On Error GoTo 0
End Sub

Toradh

Nuair a fhilleann tú ar an mbileog oibre, cuirfidh an liosta anuas ar do chumas roghanna iomadúla a roghnú, féach an taispeántas thíos:

Ag baint úsáide as Kutools le haghaidh Excel i gceann cúpla cad a tharlaíonn

Mura bhfuil tú compordach le VBA, tá rogha eile níos éasca Kutools le haghaidh Excel's Liosta anuas ilroghnacha gné. Déanann an uirlis seo atá éasca le húsáid a shimpliú chun roghanna iolracha a chumasú i liostaí anuas, rud a ligeann duit an deighilteoir a shaincheapadh agus dúbailt a bhainistiú gan stró chun freastal ar do riachtanais éagsúla.

Tar éis suiteáil Kutools le haghaidh Excel, téigh go dtí an Kutools tab, roghnaigh Liosta anuas > Liosta anuas ilroghnacha. Ansin ní mór duit a chumrú mar seo a leanas.

1. Sonraigh an raon ina bhfuil an liosta anuas óna gcaithfidh tú míreanna iolracha a roghnú.
2. Sonraigh an deighilteoir do na míreanna roghnaithe sa chill liosta anuas.
3. cliceáil OK chun na socruithe a chur i gcrích.
   

Toradh

Anois, nuair a chliceálann tú ar chill le liosta anuas sa raon sonraithe, beidh bosca liosta le feiceáil in aice leis. Níl le déanamh ach cliceáil ar an gcnaipe "+" in aice leis na míreanna chun iad a chur leis an gcill anuas, agus cliceáil ar an gcnaipe "-" chun aon rud nach dteastaíonn uait a bhaint a thuilleadh. Féach ar an taispeántas thíos:

Tuilleadh oibríochtaí le haghaidh liosta anuas ilroghnúcháin

Bailíonn an chuid seo na cásanna éagsúla a d’fhéadfadh a bheith ag teastáil agus roghanna iolracha á gcumasú sa liosta anuas um Bailíochtú Sonraí.

Ag ceadú míreanna dúblacha sa liosta anuas

Is féidir le dúblaigh a bheith ina fhadhb nuair a cheadaítear roghanna iolracha i liosta anuas. Ní cheadaíonn an cód VBA thuas míreanna dúblacha sa liosta anuas. Más gá duit míreanna dúblacha a choinneáil, bain triail as an gcód VBA san alt seo.

Cód VBA: Ceadaigh dúbailt sa liosta anuas bailíochtaithe sonraí

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim delimiter As String
    Dim TargetRange As Range

    Set TargetRange = Me.UsedRange ' Users can change target range here
    delimiter = ", " ' Users can change the delimiter here

    If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
    On Error Resume Next
    Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False

    xValue2 = Target.Value
    Application.Undo
    xValue1 = Target.Value
    Target.Value = xValue2
    If xValue1 <> "" And xValue2 <> "" Then
        Target.Value = xValue1 & delimiter & xValue2
    End If

    Application.EnableEvents = True
    On Error GoTo 0
End Sub

Toradh

Anois is féidir leat míreanna iomadúla a roghnú ó na liostaí anuas sa bhileog oibre reatha. Chun mír a dhéanamh arís i gcill liosta anuas, lean ar aghaidh ag roghnú an mhír sin ón liosta. Féach ar an scáileán:

Aon mhíreanna atá ann cheana a bhaint den liosta anuas

Tar éis duit go leor míreanna a roghnú ó liosta anuas, b'fhéidir go mbeidh ort uaireanta mír atá ann cheana a bhaint den chill liosta anuas. Soláthraíonn an chuid seo píosa eile de chód VBA chun cabhrú leat an tasc seo a chur i gcrích.

Cód VBA: Bain aon míreanna atá ann cheana féin ón gcill liosta anuas

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated by Extendoffice 20240118
    Dim xRngDV As Range
    Dim TargetRange As Range
    Dim oldValue As String
    Dim newValue As String
    Dim delimiter As String
    Dim allValues As Variant
    Dim valueExists As Boolean
    Dim i As Long
    Dim cleanedValue As String

    Set TargetRange = Me.UsedRange ' Set your specific range here
    delimiter = ", " ' Set your desired delimiter here

    If Target.CountLarge > 1 Then Exit Sub

    ' Check if the change is within the specific range
    If Intersect(Target, TargetRange) Is Nothing Then Exit Sub

    On Error Resume Next
    Set xRngDV = Target.SpecialCells(xlCellTypeAllValidation)
    If xRngDV Is Nothing Or Target.Value = "" Then
        ' Skip if there's no data validation or if the cell is cleared
        Application.EnableEvents = True
        Exit Sub
    End If
    On Error GoTo 0

    If Not Intersect(Target, xRngDV) Is Nothing Then
        Application.EnableEvents = False
        newValue = Target.Value
        Application.Undo
        oldValue = Target.Value
        Target.Value = newValue

        ' Split the old value by delimiter and check if new value already exists
        allValues = Split(oldValue, delimiter)
        valueExists = False
        For i = LBound(allValues) To UBound(allValues)
            If Trim(allValues(i)) = newValue Then
                valueExists = True
                Exit For
            End If
        Next i

        ' Add or remove value based on its existence
        If valueExists Then
            ' Remove the value
            cleanedValue = ""
            For i = LBound(allValues) To UBound(allValues)
                If Trim(allValues(i)) <> newValue Then
                    If cleanedValue <> "" Then cleanedValue = cleanedValue & delimiter
                    cleanedValue = cleanedValue & Trim(allValues(i))
                End If
            Next i
            Target.Value = cleanedValue
        Else
            ' Add the value
            If oldValue <> "" Then
                Target.Value = oldValue & delimiter & newValue
            Else
                Target.Value = newValue
            End If
        End If

        Application.EnableEvents = True
    End If
End Sub

Toradh

Ligeann an cód VBA seo duit míreanna iomadúla a roghnú ó liosta anuas agus aon rud atá roghnaithe agat cheana féin a bhaint go héasca. Tar éis duit go leor míreanna a roghnú, más mian leat ceann ar leith a bhaint, níl le déanamh ach é a roghnú ón liosta arís.

Deighilteoir saincheaptha a shocrú

Socraítear an teorannóir mar chamóg sna cóid VBA thuas. Is féidir leat an athróg seo a mhionathrú chuig aon charachtar is fearr le húsáid mar dheighilteoir do na roghanna liosta anuas. Seo mar is féidir leat a dhéanamh:

Mar is féidir leat a fheiceáil go bhfuil an líne seo a leanas ag na cóid VBA thuas go léir:

delimiter = ", "

Níl le déanamh agat ach an camóg a athrú go deighilteoir ar bith de réir mar is gá duit. Mar shampla, ba mhaith leat na míreanna a scaradh de réir leathstad, athraigh an líne go:

delimiter = "; "

delimiter = vbNewLine

Socrú raon sonraithe

Baineann na cóid VBA thuas le gach liosta anuas sa bhileog oibre reatha. Mura dteastaíonn uait ach na cóid VBA a chur i bhfeidhm ar raon áirithe liostaí anuas, is féidir leat an raon a shonrú sa chód VBA thuas mar seo a leanas.

Mar is féidir leat a fheiceáil go bhfuil an líne seo a leanas ag na cóid VBA thuas go léir:

Set TargetRange = Me.UsedRange

Níl le déanamh agat ach an líne a athrú go:

Set TargetRange = Me.Range("C2:C10")

Forghníomhú i mbileog oibre cosanta

Samhlaigh go bhfuil bileog oibre cosanta agat leis an bhfocal faire "123" agus socraigh na cealla liosta anuas go "unlocked" roimh an chosaint a ghníomhachtú, rud a chinnteoidh go bhfanfaidh an fheidhm ilroghnaithe gníomhach tar éis na cosanta. Mar sin féin, ní féidir leis na cóid VBA thuasluaite oibriú sa chás seo, agus cuireann an chuid seo síos ar script VBA eile atá deartha go sonrach chun feidhmiúlacht ilroghnaithe a láimhseáil i mbileog oibre faoi chosaint.

Cód VBA: Cumasaigh rogha iolrach sa liosta anuas gan dúbailt

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated by Extendoffice 20240118
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim delimiter As String
    Dim TargetRange As Range
    Dim isProtected As Boolean
    Dim pswd As Variant

    Set TargetRange = Me.UsedRange ' Set your specific range here
    delimiter = ", " ' Users can change the delimiter here

    If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
    
    ' Check if sheet is protected
    isProtected = Me.ProtectContents
    If isProtected Then
        ' If protected, temporarily unprotect. Adjust or remove the password as needed.
        pswd = "yourPassword" ' Change or remove this as needed
        Me.Unprotect Password:=pswd
    End If

    On Error Resume Next
    Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then
        If isProtected Then Me.Protect Password:=pswd
        Exit Sub
    End If
    Application.EnableEvents = False

    xValue2 = Target.Value
    Application.Undo
    xValue1 = Target.Value
    Target.Value = xValue2
    If xValue1 <> "" And xValue2 <> "" Then
        If Not (xValue1 = xValue2 Or _
                InStr(1, xValue1, delimiter & xValue2) > 0 Or _
                InStr(1, xValue1, xValue2 & delimiter) > 0) Then
            Target.Value = xValue1 & delimiter & xValue2
        Else
            Target.Value = xValue1
        End If
    End If

    Application.EnableEvents = True
    On Error GoTo 0

    ' Re-protect the sheet if it was protected
    If isProtected Then
        Me.Protect Password:=pswd
    End If
End Sub

Trí roghanna iolracha a chumasú i liostaí anuas Excel, is féidir leat feidhmiúlacht agus solúbthacht do bhileoga oibre a fheabhsú go mór. Cibé an bhfuil tú compordach le códú VBA nó más fearr leat réiteach níos simplí cosúil le Kutools, tá an cumas agat anois do liostaí caighdeánacha anuas a athrú go huirlisí dinimiciúla, ilroghnaithe. Leis na scileanna seo, tá tú in ann doiciméid Excel atá níos dinimiciúla agus níos so-úsáidte a chruthú. Dóibh siúd a bhfuil fonn orthu dul i mbun inniúlachta Excel, tá neart ranganna teagaisc ar ár suíomh Gréasáin. Faigh amach tuilleadh leideanna agus cleasanna Excel anseo.

Airteagail gaolmhara