كيف يمكن تحديث القائمة المنسدلة تلقائيًا في إكسل؟
تُستخدم القوائم المنسدلة في إكسل بكثرة لجعل إدخال البيانات أكثر توحيدًا وكفاءة، خاصة في التقارير اليومية، وتحديد المخزون، ومهمات تصنيف البيانات. ومع ذلك، يواجه العديد من المستخدمين قيدًا شائعًا: عند إضافة عناصر جديدة مباشرة أسفل نطاق المصدر الأصلي، لا تتضمن القائمة المنسدلة هذه الإضافات تلقائيًا. افتراضيًا، يعترف إكسل فقط بـ نطاق محدود الأولية، لذلك لا تظهر الإدخالات الجديدة خارج هذا النطاق في القائمة المنسدلة بشكل افتراضي. ولحل هذه المشكلة، يوفّر إكسل عدة طرق لإنشاء قائمة منسدلة متجددة ديناميكيًا تتوسع تلقائيًا عند إضافة بيانات جديدة.
يقدّم هذا الدليل طرقًا عملية لإنشاء قائمة منسدلة ذات تحديث تلقائي في إكسل، مما يقلل جهد الصيانة ويحدّ من الأخطاء المحتملة أثناء الإدخال—خاصةً في الجداول والقوائم التي تتوسّع باستمرار.
تحديث القائمة المنسدلة تلقائيًا باستخدام صيغة
توجد عدة سيناريوهات تتطلب تحديث القائمة المنسدلة تلقائيًا — مثل الحفاظ على قائمة منتجات مُحدَّثة، أو إدارة أعضاء نموذج تسجيل، أو تتبع مهام مشروع تخضع لتعديلات مستمرة. وتعتمد هذه الطريقة على دالة OFFSET لإنشاء نطاق ديناميكي يضمن إدراج جميع العناصر تلقائيًا في قائمتك المنسدلة بمجرد إضافة إدخالات جديدة إلى العمود.
1. حدد الخلية التي تريد إدراج القائمة المنسدلة فيها، ثم انتقل إلىبيانات > التحقق من صحة البيانات > التحقق من صحة البيانات. انظر لقطة الشاشة:
2. في مربع حوارالتحقق من صحة البيانات، انتقل إلى علامة التبويبالإعدادات، وحددقائمةمن خياراتالسماح، ثم أدخل صيغة النطاق الديناميكي أدناه في مربعالمصدر:
=OFFSET($A$2,0,0,COUNTA(A:A)-1)
شرح المعاملات ونصائح عملية:
- A2 هي الخلية الأولى في نطاق البيانات المطلوب. غيّرها لتتوافق مع خلية البداية الفعلية لقائمتك.
- A:A يشير إلى العمود بأكمله الذي يحتوي على بيانات قائمتك. ويضمن هذا الإعداد أنه كلما أضفت عناصر جديدة في هذا العمود، تُعيد الدالة حساب نطاق البيانات ديناميكيًا.
- إذا كانت هناك خلايا فارغة داخل العمود أو كنت تستخدم عناوين فرعية، فقد تحتاج إلى تعديل الصيغة أو ضمان اتساق وضع البيانات لتجنب ظهور عناصر فارغة في القائمة المنسدلة.
- مع مجموعات البيانات الكبيرة، ضع في اعتبارك أن الدوال المتقلبة مثل OFFSET قد تؤثر قليلاً على الأداء، إذ تُعاد حسابها مع كل تغيير.
3. انقر فوقموافق. الآن، تكون قد أنشأت قائمة منسدلة تتجدّد تلقائيًا كلما أُدخلت بيانات جديدة في العمود الأصلي. وعند إضافة المزيد من العناصر ضمن النطاق المتوقع، ستظهر فورًا كخيارات متاحة في القائمة المنسدلة.
استكشاف الأخطاء وإصلاحها ونصائح:
- إذا عرضت القائمة المنسدلة إدخالات فارغة غير متوقعة، فتحقق من وجود مسافات زائدة أو صفوف مخفية في عمود المصدر.
- إذا أرجعت الصيغة خطأً، فتأكد من خلو بياناتك من النطاقات غير المتصلة أو الأعمدة الفارغة بالكامل.
- تذكر توسيع صيغة المصدر إذا كانت قائمتك تبدأ من صف آخر غير الصف 2، وقم بتعديل مرجع الخلية وCOUNTA(A:A) بشكل مناسب.
استخدام الجدول كمصدر للقائمة المنسدلة (يتوسع تلقائيًا مع العناصر الجديدة)
يعد استخدام جدول إكسل كـ نطاق المصدر لقائمتك المنسدلة نهجًا فعّالًا ومناسبًا للمبتدئين. تتوسع جداول إكسل تلقائيًا عند إضافة عناصر جديدة، لذا تظل القائمة المنسدلة محدثة دون الحاجة إلى تعديل مراجع النطاقات أو الصيغ يدويًا.
تناسب هذه الطريقة بشكل خاص المستخدمين الذين يديرون قوائم تنمو أو تتغير كثيرًا، مثل قوائم الموظفين أو المخزون أو نماذج تسجيل الفعاليات. وتتمثل الميزة الرئيسية في البساطة والموثوقية في الحفاظ على قوائم محدثة، ولكن لاحظ أن هذا الأسلوب يعمل بشكل أفضل عندما يكون البيانات الأصلية في نفس الورقة أو المصنف، لأن الجداول لا تدعم مراجع بين المصنفات في التحقق من صحة البيانات.
1. حدّد نطاق البيانات الأصلية الخاص بك (على سبيل المثال،)A2:A6).
2. انتقل إلى علامة التبويبإدراجواخترجدول. تأكد من تحديد خانة «يحتوي جدولي على رؤوس» إذا كانت قائمتك تتضمن رؤوسًا.
3. سيقوم إكسل بتنسيق نطاقك كجدول. افتراضيًا، قد يُسمَّىجدول1(يمكنك التحقق من اسم الجدول أو تغييره من علامة التبويب)تصميم الجدول، باستخدام مربع اسم الجدول الموجود على اليسار).
4. انقر على الخلية التي تريد إضافة القائمة المنسدلة إليها، ثم انتقل إلىبيانات > التحقق من صحة البيانات.
5. حدد خيار القائمة من قائمة السماح، ثم أدخل في مربعالمصدرمرجعًا إلى عمود جدولك، على سبيل المثال:
=INDIRECT("Table1[Column1]")6. انقر فوقموافق. الآن، في كل مرة تضيف بيانات جديدة أسفل الجدول، سيتم تحديث العمود والقائمة المنسدلة تلقائيًا ليشمل الإدخالات الجديدة.
ملاحظات ونصائح:
- توفر جداول إكسل نطاقًا منظمًا يتوسع ويتقلص تلقائيًا مع تغيّر البيانات، مما يجعلها الخيار المثالي للقوائم التي من المتوقع أن تتغير باستمرار.
- إذا كنت بحاجة إلى الرجوع إلى قائمتك المنسدلة في ورقة أخرى، استخدم
=INDIRECT("Table1[Column1]")، لأن مراجع الجداول المباشرة في التحقق من صحة البيانات قد تكون مقتصرة على ورقة العمل الحالية في بعض إصدارات إكسل. - يتجنب هذا الأسلوب ظهور قيم فارغة في القائمة المنسدلة إذا كانت قائمتك تحتوي فقط على إدخالات غير فارغة.
استخدام VBA لتحديث القائمة المنسدلة نطاق المصدر تلقائيًا
للسيناريوهات المتقدمة والآلية—خاصةً عند التعامل مع قوائم طويلة أو أتمتة مهام صيانة المصنف—يمكنك استخدام رمز VBA لتحديث النطاق المستخدم في القائمة المنسدلة تلقائيًا كلما أُضيفت بيانات جديدة. ويُعدّ هذا الحل مثاليًا للحلول المعقدة التي تتطلب تحديث قوائم منسدلة متعددة لتعكس التغييرات في قوائم المصدر، أو عند إدارة قوائم منسدلة مخصصة لعدة مستخدمين.
1. اضغط علىAlt+F11 لفتح محرر VBA، ثم انقر نقرًا مزدوجًا على ورقة العمل التي تحتوي على التحقق من صحة البيانات في مشروع VBA.
2. انسخ والصق الكود التالي في الوحدة النمطية.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sourceColumn As Range
Dim validationCell As Range
Dim lastRow As Long
Set sourceColumn = Me.Range("A:A") ' Change to your source column
If Not Intersect(Target, sourceColumn) Is Nothing Then
Application.EnableEvents = False
lastRow = Me.Cells(Me.Rows.Count, sourceColumn.Column).End(xlUp).Row
Set validationCell = Me.Range("D1:D100") ' Change to your validation cell
With validationCell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
Formula1:="=$A$1:$A$" & lastRow
End With
Application.EnableEvents = True
End If
End Sub
3. بعد ذلك، أغلق نافذة الكود. وستُحدَّث القائمة المنسدلة تلقائيًا في كل مرة تضيف فيها بيانات إلى نطاق المصدر الخاص بك.
- عمود المصدر («A:A» حيث تُضاف بياناتك)
- خلية/نطاق التحقق من الصلاحية ("D1:D100" حيث توجد القائمة المنسدلة)
- يُنفَّذ الكود تلقائيًا عند إجراء تغييرات في ورقة العمل
- يبحث عن الصف الأخير التي تحتوي على بيانات ويحدّث نطاق التحقق من الصلاحية وفقًا لذلك
- تأكد من تمكين ماكرو ليعمل هذا الحل
افتح سحر إكسل مع KUTOOLS AI
- التنفيذ الذكي: نفِّذ عمليات الخلايا، وحلِّل البيانات، وأنشئ المخططات البيانية — كل ذلك بأوامر بسيطة!
- الصيغ المخصصة: أنشئ صيغًا مخصصة لتبسيط سير عملك.
- برمجة VBA: اكتب وأَنفِذ أكواد VBA بسلاسة تامة.
- تفسير الصيغ: افهم الصيغ المعقدة بسهولة!
- ترجمة النصوص: اكسر الحواجز اللغوية في جداولك الإلكترونية!
مقالات ذات صلة:
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في Excel باستخدام Kutools لـ Excel، وعايش الكفاءة كما لم تفعل من قبل.يقدّم Kutools لـ Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية ووقت الحفظ.انقر هنا للحصول على الميزة التي تحتاجها أكثر من غيرها...
يجلب Office Tab واجهة ذات علامات تبويب إلى Office، ويجعل عملك أسهل بكثير
- تمكّن من التحرير والقراءة باستخدام علامات التبويب في Word وExcel وPowerPoint، وPublisher وAccess وVisio وProject.
- افتح وأنشئ مستندات متعددة في علامات تبويب جديدة داخل النافذة نفسها، بدلاً من فتح نوافذ جديدة.
- يزيد إنتاجيتك بنسبة 50% ويوفّر لك مئات نقرات الفأرة كل يوم!
جميع الإضافات من Kutools في برنامج تثبيت واحد!
Kutools for Office حزمةٌ تحتوي على إضافاتٍ مخصصة لتطبيقات Excel وWord وOutlook وPowerPoint، إلى جانب Office Tab Pro، مما يجعلها الخيار المثالي للفِرق التي تعمل عبر تطبيقات Office.
- حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
- برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
- يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
- تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل