Skip to main content

Kutools for Office — مجموعة واحدة. خمسة أدوات. أنجز المزيد.

كيفية تصفية جدول محوري بناءً على قيمة خلية محددة في Excel؟

Author Siluvia Last modified

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

لا توفر Excel ميزة قياسية تقوم بتوصيل قيمة الخلية بتصفية الجدول المحوري بشكل أصلي (بدون استخدام الكود). ومع ذلك، هناك عدة تقنيات عملية للتعامل مع هذا الشرط، لكل منها مزاياها ونقاطها التي يجب مراعاتها. يقدم هذا البرنامج التعليمي أولاً طريقة VBA مباشرة لتوصيل الخلية بتصفية الجدول المحوري بحيث يتم تحديث الجدول المحوري فورًا عند تغيير قيمة الخلية. بالإضافة إلى ذلك، سنغطي طرقًا بديلة مثل استخدام صيغ Excel (مثل GETPIVOTDATA، FILTER) لعرض نتائج الجدول المحوري المصفاة، واستخدام Slicers كأداة تحكم رسومية للتصفية. فهم هذه الخيارات يساعدك على اختيار الطريقة الأنسب لسير عملك في Excel وتجربة المستخدم.

A screenshot showing a Pivot Table with a drop-down filter in Excel

تصفية الجدول المحوري بناءً على قيمة خلية محددة باستخدام كود VBA

إذا كنت تريد تفاعلية ديناميكية حقيقية - أي عندما تقوم بإدخال قيمة في خلية ويقوم مرشح الجدول المحوري بالاستجابة تلقائيًا للتغيير - فإن VBA يوفر حلًا مباشرًا. هذا مفيد بشكل خاص في اللوحات المعلوماتية، القوالب الخاصة بالزملاء، أو الحالات التي تكون فيها هناك حاجة لتعديل المرشحات بسرعة عن طريق تغيير خلية واحدة فقط. ومع ذلك، يتطلب هذا الأسلوب معرفة أساسية بمحرر VBA، وكما هو الحال مع جميع الماكرو، يجب حفظ المصنف الخاص بك بتنسيق يدعم الماكرو (.xlsm).

يتيح لك الكود التالي الخاص بـ VBA ربط خلية ورقة العمل بمرشح الجدول المحوري بشكل ديناميكي. اتبع هذه الخطوات بعناية، وتأكد من تعديل اسم ورقة العمل، اسم الجدول المحوري، ومراجع الحقل كما هو مطلوب في المصنف الخاص بك:

الخطوة 1: أدخل القيمة التي تريد تصفية الجدول المحوري بها في خلية ورقة العمل (على سبيل المثال، اكتب أو حدد قيمة التصفية في الخلية H6).

الخطوة 2: افتح ورقة العمل التي تحتوي على الجدول المحوري المستهدف. انقر بزر الماوس الأيمن فوق علامة تبويب الورقة في الجزء السفلي من Excel وحدد عرض الكود من قائمة السياق. سيؤدي هذا إلى فتح نافذة محرر VBA الخاصة بورقة العمل.

A screenshot showing the View Code option for a worksheet in Excel

الخطوة 3: في نافذة Microsoft Visual Basic for Applications (VBA) المفتوحة، الصق الكود التالي في وحدة كود الورقة (وليست الوحدة القياسية):

كود VBA: تصفية الجدول المحوري بناءً على قيمة الخلية

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180702
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String
    On Error Resume Next
    If Intersect(Target, Range("H6:H7")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Worksheets("Sheet1").PivotTables("PivotTable2")
    Set xPFile = xPTable.PivotFields("Category")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    Application.ScreenUpdating = True
End Sub

📝 ملاحظات:

  • "Sheet1" هي ورقة العمل التي تحتوي على الجدول المحوري. قم بالتعديل حسب الحاجة.
  • "PivotTable2" هو اسم الجدول المحوري الخاص بك. يمكنك العثور عليه في تبويب تحليل PivotTable.
  • "Category" هو الحقل الذي تريد تصفيته. يجب أن يتطابق تمامًا مع اسم الحقل.
  • H6 هي الخلية التي تحتوي على قيمة التصفية. تأكد من أن القيمة تتطابق مع أحد العناصر الموجودة في قائمة التصفية.
  • يجب أن تتطابق قيم التصفية حرفيًا. يمكن أن تسبب المسافات الإضافية أو الأخطاء الكتابية مشكلات أو نتائج فارغة.

الخطوة 4: اضغط على Alt + Q لإغلاق محرر VBA والعودة إلى Excel.

الآن، يجب أن يقوم الجدول المحوري بتصفية البيانات تلقائيًا لعرض البيانات التي تتطابق فقط مع القيمة المدخلة في الخلية H6. يعمل هذا الماكرو كل مرة تتغير فيها القيمة في H6، مما يجعل من السهل ضبط ملخص البيانات الخاص بك بشكل ديناميكي.

Pivot Table filtered based on a specific cell value

يمكنك تعديل القيمة في خلية التصفية في أي وقت - سيتم تحديث الجدول المحوري فورًا عند تغيير محتوى الخلية أو استبداله.

Result of changing the filter cell value for the Pivot Table

استكشاف الأخطاء وإصلاحها:

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

صيغة Excel – عرض نتائج الجدول المحوري المصفاة بناءً على قيمة الخلية

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

هذا الحل مفيد بشكل خاص عند إنشاء جداول ملخص مخصص، لوحة المعلومات أو التقارير التي تعكس معايير متغيرة يدخلها المستخدم - دون تغيير عرض الجدول المحوري الأصلي.

استخدام GETPIVOTDATA:

لنفترض أن الجدول المحوري الخاص بك (يُدعى "PivotTable2") يلخص المبيعات حسب الفئة، وقيمة المرشح مدخلة في الخلية H6. يمكنك استخدام GETPIVOTDATA لعرض إجمالي المبيعات للفئة المحددة في H6:

1. حدد الخلية التي تريد عرض نتيجة الملخص فيها (مثل I6):

=GETPIVOTDATA("Sum of Sales", $A$4, "Category", $H$6)

2. اضغط Enter. عند تغيير القيمة في H6، يتم تحديث النتيجة في I6 تلقائيًا لتعكس الملخص المقابل من الجدول المحوري.

إذا كان الجدول المحوري الخاص بك يستخدم أسماء حقول أو تخطيط مختلفًا، قم بتعديل الصيغة وفقًا لذلك. لتكوين صيغة GETPIVOTDATA تلقائيًا، اكتب = في خلية، ثم انقر على خلية قيمة داخل الجدول المحوري الخاص بك. ستقوم Excel بإدراج الصيغة المناسبة، والتي يمكنك بعد ذلك تعديلها حسب الحاجة.

استخدام FILTER مع جدول مساعد:

إذا كنت ترغب في استخراج السجلات التفصيلية من مجموعة البيانات الأصلية (بدلاً من مجرد ملخصات الجدول المحوري)، وتستخدم Excel 365 أو Excel 2019، فإن وظيفة FILTER تسمح بالتصفية الديناميكية بناءً على قيمة الخلية:

لنفترض أن بيانات المصدر الخاصة بك موجودة في النطاق A1:C100 والفئة في العمود A.

1. حدد الخلية البدء التي يجب أن تظهر فيها السجلات المصفاة (مثل J6):

=FILTER(A2:C100, A2:A100 = H6, "No data")

2. اضغط Enter. الصفوف المتطابقة ستظهر في الخلايا المجاورة، حيث يتم سرد جميع السجلات التي تتطابق فئتها مع القيمة في H6. تحديث H6 سيقوم بتحديث النتائج فورًا.

لمطابقة تجميعات الجدول المحوري أو التصفية بناءً على معايير متعددة، فكر في الجمع بين GETPIVOTDATA وFILTER، أو قم بتوسيع الصيغة بشروط منطقية إضافية.

📝 نصائح وتحذيرات:

  • هذه الصيغ لا تعدل مرشح الجدول المحوري الفعلي. فهي توفر فقط عرضًا ديناميكيًا مستقلًا بناءً على قيم الخلية.
  • لتغيير مرشحات الجدول المحوري مباشرة، يلزم استخدام VBA.
  • تأكد من أن أسماء الحقول المستخدمة في GETPIVOTDATA تتطابق تمامًا مع تلك الموجودة في الجدول المحوري (الحالة والمسافة).
  • إذا رأيت أخطاء #REF!، تحقق من أن المراجع صالحة ولم يتغير هيكل الجدول المحوري.

طرق أخرى مضمنة في Excel - استخدام Slicers كمرشحات تفاعلية للجداول المحورية

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

كيفية إضافة واستخدام Slicer:

  1. حدد أي خلية داخل الجدول المحوري الخاص بك.
  2. انتقل إلى تبويب تحليل PivotTable (أو تبويب Analyze في الإصدارات القديمة)، وانقر على إدراج Slicer.
  3. في مربع حوار إدراج Slicers، تحقق من الحقل الذي تريد تصفيته به (مثل Category)، ثم انقر موافق.
  4. سيظهر Slicer على ورقة العمل الخاصة بك. انقر على زر لتصفية الجدول المحوري بهذه القيمة. اضغط Ctrl لتحديد عناصر متعددة.

يمكن تنسيق Slicers وتغيير حجمها وربطها بجداول محورية متعددة لتصفية متزامنة عبر تقارير مختلفة. وهي مفيدة بشكل خاص في اللوحات المعلوماتية أو المصنفات المشتركة حيث قد لا يكون المستخدمون مرتاحين لفلاتر القوائم المنسدلة ولكن لا يزالون بحاجة إلى تصفية البيانات بسهولة دون استخدام VBA أو تعديل الصيغ.

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

بالإضافة إلى ذلك، إذا كانت بياناتك مخزنة في جدول Excel (وليس جدول محوري)، فلا يزال بإمكانك استخدام Slicers عن طريق تحديد الجدول والانتقال إلى تبويب تصميم الجدول > إدراج Slicer.

استكشاف الأخطاء وإصلاحها: إذا لم يظهر Slicer ليقوم بتصفية الجدول المحوري، تحقق من اتصالات التقرير (تحت تبويب Slicer أو Analyze) لضمان اتصاله بشكل صحيح بالجداول المحورية المقصودة.

كل من الطرق أعلاه تخدم غرضًا مختلفًا: تتيح VBA تصفية مرتبطة مباشرة بالخلايا، وتوفر الصيغ عرضًا ديناميكيًا للنتائج، وتوفر Slicers تصفية رسومية سهلة الاستخدام. اختر النهج الذي يناسب احتياجاتك لأتمتة، المرونة، وسهولة الاستخدام. تظل مرشحات القائمة المنسدلة التقليدية للجدول المحوري متاحة كخيار احتياطي أساسي.

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

أفضل أدوات الإنتاجية لمكتب العمل

🤖 Kutools AI Aide: أحدث ثورة في تحليل البيانات اعتمادًا على: التنفيذ الذكي |  توليد الأكواد |  إنشاء الصيغ المخصصة |  تحليل البيانات وتوليد الرسوم البيانية |  استخدام Kutools Functions...
ميزات شائعة: البحث، تمييز أو وضع علامة على المكررات | حذف الصفوف الفارغة | دمج الأعمدة أو الخلايا بدون فقدان البيانات | تقريب بدون الصيغة...
بحث متقدم: بحث بمعايير متعددة VLookup | بحث بقيم متعددة VLookup | بحث في عدة ورقات VLookup | مطابقة غامضة...
قائمة منسدلة متقدمة: إنشاء قائمة منسدلة بسرعة | قائمة منسدلة معتمدة | قائمة منسدلة متعددة الاختيارات...
مدير الأعمدة: إضافة عدد محدد من الأعمدة | نقل الأعمدة | تبديل حالة إظهار الأعمدة المخفية | مقارنة النطاقات والأعمدة...
ميزات مميزة: التركيز على الشبكة | عرض التصميم | شريط الصيغ المحسن | مدير أدوات المصنف وورقة العمل | مكتبة النص التلقائي | منتقي التاريخ | دمج البيانات | تشفير/فك تشفير الخلايا | إرسال البريد الإلكتروني حسب الجدول | مرشح متقدم | تصفية خاصة (تصفية الخلايا التي تحتوي على خط عريض/مائل/يتوسطه خط...)...
أفضل15 مجموعة أدوات:12 أداة نصية (إضافة نص، حذف الأحرف المحددة، ...) | أكثر من50 نوع رسم بياني (مخطط جانت، ...) | أكثر من40 صيغة عملية (حساب العمر بناءً على تاريخ الميلاد، ...) |19 أداة إدراج (إدراج رمز الاستجابة السريعة، إدراج صورة من المسار، ...) |12 أداة تحويل (تحويل إلى كلمات، تحويل العملة، ...) |7 أدوات دمج وتقسيم (دمج متقدم للصفوف، تقسيم الخلايا، ...) | ... والمزيد
استخدم Kutools بلغتك المفضلة – يدعم الإنجليزية، الإسبانية، الألمانية، الفرنسية، الصينية، وأكثر من40 لغة أخرى!

عزز مهاراتك في 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 Tabs PowerPoint
  • حزمة الكل في واحد — إضافات Excel وWord وOutlook وPowerPoint + Office Tab Pro
  • مثبّت واحد، ترخيص واحد — إعداد في دقائق (جاهز لـ MSI)
  • الأداء الأفضل معًا — إنتاجية مُبسطة عبر تطبيقات Office
  • تجربة كاملة لمدة30 يومًا — بدون تسجيل، بدون بطاقة ائتمان
  • قيمة رائعة — وفر مقارنة بشراء الإضافات بشكل منفرد