كيف تُصفِّي البيانات بناءً على اختيارك من القائمة المنسدلة في Excel؟
في Excel، يعرف العديد من المستخدمين كيفية تصفية البيانات باستخدام ميزة التصفية القياسية. ومع ذلك، قد ترغب أحيانًا في تصفية البيانات أو عرضها بشكل تفاعلي باستخدام اختيار من قائمة منسدلة. على سبيل المثال، قد تحتاج إلى تحديث صفوف البيانات ديناميكيًا لعرض المعلومات التي تتطابق فقط مع ما اخترته من قائمة منسدلة، كما هو موضح في لقطة الشاشة أدناه. يمكّنك هذا الأسلوب من إنشاء تقارير ولوحات تحكم وأشكال تفاعلية أكثر سهولة في الاستخدام. يتناول هذا المقال عدة طرق عملية لتصفية البيانات أو تمييزها بصريًا استنادًا إلى اختيارات قائمة منسدلة في ورقة عمل واحدة أو ورقتين، مما يوفّر لك خيارات مرنة تناسب احتياجاتك المختلفة.
تصفية البيانات من اختيار القائمة المنسدلة في ورقة عمل واحدة باستخدام صيغ مساعدة
تصفية البيانات من اختيار القائمة المنسدلة في ورقتَي عمل باستخدام كود VBA
استخدم تنسيق الشروط - نطاق الصف المميز تطابق اختيار القائمة المنسدلة
تصفية البيانات من اختيار القائمة المنسدلة في ورقة عمل واحدة باستخدام صيغ مساعدة
لتصفية البيانات بناءً على قائمة منسدلة، يمكنك إعداد مجموعة من الأعمدة المساعدة باستخدام الصيغ لإنشاء استخراج ديناميكي للصفوف المتطابقة. يُعدّ هذا الأسلوب مثاليًا عندما تريد عرض السجلات ذات الصلة فقط في نفس ورقة العمل دون الحاجة إلى استخدام ماكرو. اتبع الخطوات التالية:
1. ابدأ بإدراج قائمة منسدلة: حدد الخلية التي ترغب في إضافة القائمة المنسدلة إليها، ثم انتقل إلىبيانات > التحقق من صحة البيانات > التحقق من صحة البيانات. بهذه الخطوة، يتم إنشاء خلية يُمكن للمستخدمين من خلالها اختيار عنصر للتصفية بناءً عليه.
2. في مربع حوارالتحقق من صحة البيانات، ضمن علامة التبويبالإعدادات، اخترقائمةمن القائمة المنسدلةالسماح، ثم انقر فوق الزر
لتمييز نطاق القيم الخاص بقائمتك المنسدلة. ويمكن أن يساعد استخدام نطاق مسمّى أو جدول كمصدر للقائمة في تحديث قوائمك تلقائيًا لاحقًا.
3. بعد إعداد قائمة التصفية المنسدلة الخاصة بك، اختر أي عنصر لتطبيق التصفية. في الخلية D2، أدخل الصيغة التالية (بافتراض أن اختيار القائمة المنسدلة موجود في العمود H):
=ROWS($A$2:A2)هنا، يشيرA2 إلى الخلية الأولى في العمود الذي يحتوي على البيانات المراد مطابقتها. اسحب مقبض التعبئة لأسفل لتعبئة جميع الصفوف ذات الصلة. ويُنشئ هذا العمود المساعد أرقامًا متتالية للصفوف، مما يسهّل الرجوع إليها لاحقًا.
4. بعد ذلك، أدخل في الخلية E2:
=IF(A2=$H$2,D2,"")تتحقّق هذه الصيغة مما إذا كانت القيمة فيA2 تطابق عنصر القائمة المنسدلة المحدد فيH2. فإذا تطابقت، تُخرِج رقم الصف منD2؛ وإلا، تترك الخلية فارغة. هذه خطوة تصفية أساسية: تأكد من أن مرجع خلية القائمة المنسدلة (هنا)H2) لا يتغيّر بشكل غير متوقّع.
5. في الخلية F2، أدخل:
=IFERROR(SMALL($E$2:$E$17,D2),"")تستخرج هذه الصيغة عدد صفوف بيانات التصفية، مما يتيح لك لاحقًا إرجاع الإدخالات المقابلة. تأكد من أن النطاقE2:E17 يغطي جميع خلايا التصفية التي تحتوي على صيغك. قم بسحب مقبض التعبئة لأسفل حسب الحاجة.
6. لإظهار نتائج التصفية، أدخل الصيغة التالية في الخلية J2:
=IFERROR(INDEX($A$2:$C$17,$F2,COLUMNS($J$2:J2)),"")انسخ هذه الصيغة منJ2 إلىL2 لعرض أول سجل مطابق. تعتمد هذه الخطوة على نتائج أعمدتك المساعدة لاسترجاع صفوف البيانات الفعلية بناءً على اختيارك من القائمة المنسدلة. عدّل الأعمدة إذا كان نطاق بياناتك الأصلي مختلفًا.
ملاحظة: A2:C17 هو جدولك الأصلي، وF2 هو العمود المساعد المُصفّى، وJ2 هو المكان الذي تريد أن يظهر فيه الناتج.
7. اسحب مقبض التعبئة لأسفل عبر جميع أعمدة الناتج لعرض كل السجلات المطابقة.
8. الآن، كلما اخترت عنصرًا من القائمة المنسدلة، يتجدّد الجدول أدناه تلقائيًا ليعرض فقط الصفوف المتوافقة مع اختيارك.
عزّز قوائم Excel المنسدلة بميزات Kutools المحسّنة
ارفع مستوى إنتاجيتك مع ميزات القوائم المنسدلة المحسّنة من Kutools لـ Excel! تتجاوز هذه المجموعة القدرات الأساسية في Excel لتبسيط سير عملك، وتشمل:
- إنشاء قائمة منسدلة تدعم التحديد المتعدد: اختر إدخالات متعددة دفعة واحدة لمعالجة البيانات بكفاءة أعلى.
- قائمة منسدلة مع مربع اختيار: عزِّز تفاعل المستخدم ووضوح جداول البيانات الخاصة بك.
- إنشاء قائمة منسدلة ديناميكية...: يتم تحديثها تلقائيًا بناءً على تعديلات البيانات، مما يضمن دقتها.
- اجعل قائمة الانسحاب قابلة للبحث: اعثر بسرعة على الإدخالات التي تريدها، ووفّر الوقت والجهد!
تصفية البيانات من اختيار القائمة المنسدلة في ورقتَي عمل باستخدام كود VBA
قد تحتاج أحيانًا إلى تصفية البيانات في ورقة عمل بعد اختيار عنصر من قائمة منسدلة موجودة في ورقة عمل مختلفة. فعلى سبيل المثال، قد تحتوي الورقة "Sheet1" على القائمة المنسدلة، بينما تحتوي الورقة "Sheet2" على الجدول الذي تريد تصفية بياناته. في مثل هذه الحالات، يُعد استخدام VBA حلاً عمليًا، إذ لا تستطيع الصيغ تحديث أوراق أخرى مباشرةً استجابةً لأحداث المستخدم. ويُعد هذا الأسلوب مثاليًا لوحات التحكم والتقارير أو كتب العمل الملخّصة، حيث يُفصَل بين نطاق البيانات المصدر ومدخلات المستخدم لتعزيز الوضوح والتنظيم.
1. انقر بزر الماوس الأيمن على لسان ورقة العمل (مثل Sheet1) التي تحتوي على خلية القائمة المنسدلة، ثم اخترعرض الكود. في نافذةمايكروسوفت فيجوال بيسيك للتطبيقات، انسخ والصق الكود التالي في الوحدة النمطية الفارغة:
كود VBA: تصفية البيانات من اختيار القائمة المنسدلة في ورقتَي عمل:
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
On Error Resume Next
If Not Intersect(Range("A2"), Target) Is Nothing Then
Application.EnableEvents = False
If Range("A2").Value = "" Then
Worksheets("Sheet2").ShowAllData
Else
Worksheets("Sheet2").Range("A2").AutoFilter 1, Range("A2").Value
End If
Application.EnableEvents = True
End If
End Sub
ملاحظة: في الكود، يشيرA2 إلى خلية القائمة المنسدلة، وتشيرSheet2 إلى الورقة التي تُطبَّق عليها التصفية، بينما يعيِّنفلتر تلقائي 1العمود الذي سيتم التصفية بناءً عليه. عدِّل هذه القيم وفقًا لتخطيط بياناتك. تأكد من أن أسماء أوراق العمل والخلايا تتوافق مع هيكل ملفك الفعلي لتجنب أخطاء التشغيل. وإذا واجهت سلوكًا غير متوقع، فتحقق من وجود حماية على الورقة أو مدمجات أو بيانات مخفية قد تتداخل مع آلية الفلترة التلقائية.
2. الآن، يؤدي اختيار أي عنصر من القائمة المنسدلة في Sheet1 إلى تصفية البيانات في Sheet2 فورًا، مما يجعل التحليل عبر أوراق العمل سلسًا وسهلًا للتقارير والمراجعة.
لاحظ أن الحلول القائمة على VBA تتطلب تمكين الماكرو. احفظ دائمًا ملفك كملف من نوع.xlsm إذا كنت ترغب في الاحتفاظ بالكود. وإذا لم تُحدَّث الفلترة، فراجع إعدادات أمان الماكرو وتأكد من تطابق المراجع وأسماء أوراق العمل. وتجنّب استخدام بيانات حساسة أو حرجة للأعمال دون نسخ احتياطي مناسب، لأن الماكرو قادر على إجراء تغييرات جماعية!
استخدم تنسيق الشروط - تمييز جميع الصفوف المطابقة لاختيار القائمة المنسدلة تلقائيًا
إذا كان هدفك ليس إخفاء الصفوف أو استخراجها، بل تمييزها بصريًا فقط لتحديد الصفوف المطابقة لاختيار القائمة المنسدلة، فإن التنسيق الشرطي يوفّر أسلوبًا سريعًا وسهل الاستخدام. استخدمه عندما تريد تركيز انتباه المستخدمين على الأسطر ذات الصلة دون إزالة البيانات أو نقلها.
يُستخدم هذا الأسلوب بشكلٍ شائع في لوحات التحكم والتقارير أو القوائم الطويلة، حيث يُبرز التمييز فورًا الإدخالات المرتبطة بالاختيار الحالي، مما يعزز وضوح البيانات ويسهّل قراءتها.
- حدد نطاق بياناتك:على سبيل المثال، حدد A2:C100.
- الوصول إلى أداة «استخدم تنسيق الشروط»:انتقل إلىالصفحة الرئيسية > استخدم تنسيق الشروط > قاعدة جديدة.
- أنشئ قاعدتك:اختراستخدام صيغة لتحديد الخلايا التي سيتم تنسيقها، ثم أدخل صيغة مثل:
وهذا سيُبرز أي صف تكون فيه القيمة في العمود A مطابقة لاختيار القائمة المنسدلة في الخلية H2.=$A2=$H$2 - عيّن التنسيق:انقر فوقتنسيق، ثم اختر تعبئة اللون أو تنسيق النص، واضغط «موافق» للتأكيد.
المزايا: إعدادٌ سريع، ويعمل فورًا بمجرد تغيير التحديدات، ولا يؤثر على هيكل الجدول. ومع ذلك، فإن هذه الطريقة تُبرز السجلات فقط (ولا تقوم بفلترتها أو استخراجها). وللجدول الكبير، استخدم ألوانًا عالية التباين لضمان وضوح نطاق الصف المميز. تعتمد قواعد تنسيق الشروط على الخلايا—فإذا كانت مراجع الخلايا غير صحيحة، فقد لا يتم تمييز جميع الصفوف كما هو متوقع. لذا، استخدم المراجع المطلقة (مثل $H$2) في صيغتك لضمان الاتساق.
إذا أردت إزالة التمييز، فما عليك سوى الانتقال إلىاستخدم تنسيق الشروط > مسح القواعد. وللتمييز متعدد الشروط أو متعدد الأعمدة، عدّل صيغتك للتحقق من أعمدة إضافية أو استخدم دالةAND.
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في 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 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل