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

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

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

• ربط تصفية الجدول المحوري بخلية معينة باستخدام كود VBA
• صيغة Excel - استخدام الصيغ (مثل GETPIVOTDATA) بالاشتراك مع شرائح التصفية أو تصفية التقرير المرجعي
• طرق Excel المدمجة الأخرى - توصيل شرائح الجداول المحورية واللوحات للتصفية التفاعلية

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

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

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

فكر في المثال التالي: يحتوي الجدول المحوري على حقل تصفية اسمه الفئة، مع قيمتي تصفية: "المصاريف" و"المبيعات". من خلال ربط خلية بتصفية الجدول المحوري، يمكنك التحكم في البيانات المعروضة عن طريق كتابة "المصاريف" أو "المبيعات" في الخلية التي اخترتها.

لتنفيذ هذا:

• حدد الخلية التي تريد استخدامها كوحدة تحكم في التصفية (على سبيل المثال، الخلية H6)، وأدخل إحدى قيم التصفية مسبقًا. تأكد من أن القيمة تطابق تمامًا تلك المتاحة في حقل تصفية الجدول المحوري.
• انتقل إلى ورقة العمل التي تحتوي على الجدول المحوري الخاص بك. انقر بزر الماوس الأيمن على علامة تبويب الورقة واختر عرض الكود من القائمة. سيتم فتح نافذة Visual Basic for Applications.

في نافذة 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")) 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

ملاحظات:

بعد لصق الكود، اضغط على Alt + Q لإغلاق نافذة محرر VBA والعودة إلى Excel.

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

في كل مرة تقوم فيها بتعديل محتوى الخلية، يقوم الجدول المحوري بتحديث بياناته المُرشحة وفقًا لذلك.

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

اكتشف سحر Excel مع Kutools AI

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

عزز قدرات Excel لديك باستخدام أدوات تعمل بالذكاء الاصطناعي. قم بالتنزيل الآن وعش تجربة الكفاءة كما لم يحدث من قبل!

صيغة Excel - استخدام الصيغ (مثل GETPIVOTDATA) بالاشتراك مع شرائح التصفية أو تصفية التقرير المرجعي

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

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

مثال: عرض ملخص الجدول المحوري بناءً على قيمة الخلية

لنفترض أن لديك جدولًا محوريًا يلخص المبيعات حسب الفئة (مثل "المبيعات"، "المصاريف"). يمكنك استخدام GETPIVOTDATA لاستخراج القيمة ذات الصلة لفئة محددة في خلية.

1. افترض أن الخلية H6 تحتوي على الفئة التي ترغب في عرضها (مثل "المبيعات"). ضع الصيغة التالية في الخلية الملخصة الخاصة بك (مثل I6):

=GETPIVOTDATA("Sum of Amount",$B$4,"Category",H6)

2. بعد إدخال الصيغة في I6، اضغط على Enter. الآن، كلما قمت بتغيير H6 إلى فئة صالحة (مثل "المصاريف" أو "المبيعات")، سيتم تحديث I6 فورًا لعرض الإجمالي لهذه الفئة، وفقًا للجدول المحوري الحالي.

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

استكشاف الأخطاء وإصلاحها: إذا أعادت الصيغة خطأ #REF! أو #VALUE!، تحقق من أن مراجع الخلايا الخاصة بك صحيحة، والفئة المدخلة موجودة في الجدول المحوري الخاص بك، واسم الحقل/المجموع يتطابق تمامًا.

طرق Excel المدمجة الأخرى - توصيل شرائح الجداول المحورية واللوحات للتصفية التفاعلية

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

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

لإنشاء شريحة وربطها:

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

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

استكشاف الأخطاء وإصلاحها: إذا لم تتمكن شريحة من الاتصال بعدة جداول محورية، تأكد من أن جميع الجداول مبنية من نفس ذاكرة التخزين المؤقت/مصدر البيانات. الخيار "اتصالات التقارير" يظهر فقط إذا كانت الجداول متوافقة.

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

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

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