تحديد عدة عناصر في Excel قائمة منسدلة – دليل شامل

تُعد قوائم التحقق من البيانات (Data Validation) في Excel أداة رائعة لضمان اتساق البيانات وسهولة إدخالها. ومع ذلك، فهي تقتصر افتراضيًا على اختيار عنصر واحد فقط. فماذا لو احتجتَ إلى تحديد عدة عناصر من القائمة المنسدلة نفسها؟ يقدّم لك هذا الدليل الشامل طرق تمكين التحديد المتعدد في قوائم التحقق من البيانات بـ Excel، وإدارة التكرارات، وتعيين فواصل مخصصة، وتحديد نطاق هذه القوائم.

• استخدام كود VBA
• استخدام Kutools لـ Excel بنقرات قليلة

• السماح بعناصر مكررة
• إزالة أي عناصر موجودة مسبقًا
• تعيين فاصل مخصص
• تعيين نطاق محدود
• التنفيذ في ورقة عمل محمية

تمكين التحديد المتعدد في قائمة منسدلة

يقدّم هذا القسم طريقتين تساعدك على تمكين التحديد المتعدد في القائمة المنسدلة في Excel.

استخدام كود VBA

لتمكين التحديد المتعدد من قائمة منسدلة في Excel، يمكنك استخدام «Visual Basic for Applications» (VBA). يتيح لك البرنامج النصي تعديل سلوك القائمة المنسدلة لتحويلها إلى قائمة تدعم اختيار عدة خيارات دفعة واحدة. يُرجى اتباع الخطوات التالية:

الخطوة 1: فتح محرر ورقة الشيفرة (Code)

1. افتح ورقة العمل التي تحتوي على القائمة المنسدلة التي ترغب في تمكين الاختيار المتعدد لها.
2. انقر بزر الماوس الأيمن على لسان تبويب الورقة، ثم اختر «عرض الكود» من القائمة السياقية.
   

الخطوة 2: استخدام كود VBA

الآن، انسخ كود VBA التالي والصقه في نافذة ورقة الشيفرة المفتوحة.

كود VBA: تمكين التحديد المتعدد في قوائم التحقق من البيانات في 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

النتيجة

عند العودة إلى ورقة العمل، سيتيح لك قائمة منسدلة اختيار عدة خيارات، انظر العرض التوضيحي أدناه:

باستخدام Kutools لـ Excel بنقرات قليلة

إذا لم تكن مرتاحًا لاستخدام VBA، فثمة بديلٌ أسهل: ميزة «إنشاء قائمة منسدلة مع تحديدات متعددة» من «Kutools لـ Excel». تُبسّط هذه الأداة سهلة الاستخدام عملية تمكين التحديد المتعدد في القوائم المنسدلة، وتتيح لك تخصيص الفاصل وإدارة القيم المكررة بسلاسة لتلبية مختلف احتياجاتك.

بعد تثبيت Kutools لـ Excel، انتقل إلى علامة التبويب «Kutools»، ثم اختر «قائمة منسدلة» > «إنشاء قائمة منسدلة مع تحديدات متعددة». بعد ذلك، قم بإجراء الإعدادات التالية.

1. حدّد النطاق الذي يحتوي على القائمة المنسدلة التي تريد اختيار عناصر متعددة منها.
2. حدّد الفاصل المستخدم بين العناصر في الخلية التي تحتوي على قائمة منسدلة.
3. انقر «موافق» لإتمام الإعدادات.
   

النتيجة

الآن، عند النقر على خلية تحتوي على قائمة منسدلة ضمن نطاق محدود، سيظهر بجانبها مربع القائمة. ما عليك سوى النقر على الزر «+» بجانب العناصر لإضافتها إلى خلية القائمة المنسدلة، أو النقر على الزر "-" لإزالة أي عنصر لم تعد تريده. شاهد العرض التوضيحي أدناه:

مزيد من العمليات للتحديد المتعدد في قائمة منسدلة

يجمع هذا القسم بين مختلف السيناريوهات التي قد تحتاجها عند تمكين التحديد المتعدد في قوائم التحقق من البيانات المنسدلة.

السماح بالعناصر المكررة في قائمة منسدلة

يمكن أن تُشكّل العناصر المكررة تحديًا عند تمكين التحديد المتعدد في قائمة منسدلة. الكود أعلاه بلغة VBA لا يسمح بوجود عناصر مكررة في القائمة المنسدلة. فإذا كنت بحاجة إلى الاحتفاظ بالعناصر المكررة، جرّب استخدام كود VBA المتوفر في هذا القسم.

كود VBA: السماح بالتكرارات في قوائم التحقق من البيانات قائمة منسدلة

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

النتيجة

يمكنك الآن تحديد عدة عناصر من القائمة المنسدلة في ورقة العمل الحالية. ولتكرار عنصر في خلية القائمة المنسدلة، ما عليك سوى الاستمرار في تحديده من القائمة. انظر لقطة الشاشة:

إزالة أي عناصر موجودة من قائمة منسدلة

بعد تحديد عدة عناصر من قائمة منسدلة، قد تجد نفسك أحيانًا بحاجة إلى إزالة عنصر معيّن من خلية القائمة المنسدلة. ويقدّم هذا القسم جزءًا إضافيًّا من كود VBA لمساعدتك على إنجاز هذه المهمة بسلاسة.

كود VBA: إزالة أي عناصر موجودة من خلية قائمة منسدلة

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

النتيجة

يتيح لك كود VBA هذا تحديد عدة عناصر من القائمة المنسدلة، كما يمكّنك من إزالة أي عنصر سبق أن اخترته بسهولة. فبمجرد تحديد عدة عناصر، إذا أردت إزالة عنصر معيّن، فما عليك سوى النقر عليه مرة أخرى في القائمة.

تعيين فاصل مخصص

تم ضبط الفاصل على الفاصلة في أكواد VBA أعلاه، ويمكنك بسهولة تغيير هذه القيمة إلى أي رمز تفضّله ليكون فاصلًا للعناصر في القائمة المنسدلة. إليك كيفية القيام بذلك:

كما ترى، فإن جميع أكواد VBA أعلاه تحتوي على السطر التالي:

delimiter = ", "

كل ما عليك فعله هو استبدال الفاصلة بالفاصل الذي تحتاجه. على سبيل المثال، إذا أردت فصل العناصر بفاصلة منقوطة، غيّر السطر إلى:

delimiter = "; "

delimiter = vbNewLine

تعيين نطاق محدود

تنطبق أكواد VBA المذكورة أعلاه على جميع القوائم المنسدلة في ورقة العمل الحالية. وإذا رغبت في تطبيق أكواد VBA على نطاق معيّن من القوائم المنسدلة فقط، فيمكنك تحديد ذلك النطاق في كود VBA أعلاه كما يلي.

كما ترى، فإن جميع أكواد VBA أعلاه تحتوي على السطر التالي:

Set TargetRange = Me.UsedRange

كل ما عليك فعله هو تغيير السطر إلى:

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

التنفيذ في ورقة عمل محمية

تخيّل أنك قمت بحماية ورقة العمل بكلمة المرور "123"، وعيّنت خلايا القائمة المنسدلة كـ«غير مقفلة» قبل تفعيل الحماية، مما يضمن استمرار عمل وظيفة التحديد المتعدد بعد تطبيق الحماية. ومع ذلك، فإن أكواد VBA المذكورة أعلاه لن تعمل في هذا السيناريو، ويصف هذا القسم برنامجًا نصيًا آخر مكتوبًا بلغة VBA، صُمّم خصيصًا للتعامل مع وظيفة التحديد المتعدد داخل ورقة عمل محمية.

كود VBA: تمكين التحديد المتعدد في قائمة منسدلة دون تكرارات

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

بتمكين التحديد المتعدد في قوائم التحقق من البيانات في Excel، يمكنك تعزيز وظائف وأداء أوراق العمل بشكل كبير. سواء كنت مرتاحًا لكتابة أكواد VBA أو تفضّل حلاً أكثر بساطة مثل Kutools، فإنك الآن قادر على تحويل قوائم التحقق من البيانات القياسية إلى أدوات ديناميكية تدعم التحديد المتعدد. ومع هذه المهارات، ستتمكن من إنشاء مستندات Excel أكثر ديناميكية وسهولة في الاستخدام. ولمن يرغب في استكشاف المزيد من إمكانيات Excel، يحتوي موقعنا على كنزٍ من البرامج التعليمية.اكتشف المزيد من نصائح وحيل Excel هنا.

مقالات ذات صلة