كيف تُصفّي جدولًا محوريًّا في Excel استنادًا إلى قيمة خلية معيّنة؟
في Excel، يُستخدم الجدول المحوري على نطاق واسع لتلخيص البيانات وتحليلها واستكشافها بكفاءة. افتراضيًّا، تتم عملية التصفية داخل الجدول المحوري عادةً باختيار العناصر المرغوبة من القائمة المنسدلة للتصفية. وعلى الرغم من أن هذا الأسلوب يوفّر مرونة كبيرة، إلا أن هناك سيناريوهات معينة تتطلب أسلوب تصفية أكثر ديناميكية — كأن تتغيّر نتائج الجدول المحوري تلقائيًّا بناءً على القيمة المُدخلة في خلية معيّنة بورقة العمل. ويكون هذا النهج مفيدًا بشكل خاص عند إعداد لوحات المعلومات، أو أتمتة سير العمل، أو بناء تقارير تفاعلية للمستخدمين النهائيين الذين قد لا يكونون على دراية بالتصفية اليدوية.
لا يوفّر Excel ميزةً قياسيةً تربط قيمة خليةٍ مباشرةً بمرشّح جدول محوري (بدون استخدام التعليمات البرمجية). ومع ذلك، توجد عدة تقنيات عملية لتحقيق هذا الهدف، ولكلٍّ منها مزاياه واعتباراته الخاصة. يقدم هذا البرنامج التعليمي أولًا طريقةً مباشرةً تعتمد على VBA لربط خليةٍ بمرشّح جدول محوري، بحيث يتجدّد الجدول المحوري فورًا بمجرد تغيير قيمة الخلية. بالإضافة إلى ذلك، سنستعرض طرقًا بديلة، مثل استخدام دوال Excel (مثل GETPIVOTDATA وFILTER) لعرض نتائج مُرشَّحة، وكذلك استخدام المُقطِّعات (Slicers) كعناصر تحكم رسومية للتصفية. ويُمكّنك فهم هذه الخيارات من اختيار الأنسب منها لينسجم مع سير عملك في Excel ومتطلبات تجربة المستخدم لديك.
➤ تصفية جدول بيانات محوري بناءً على قيمة خلية معيّنة باستخدام كود VBA
➤ صيغة Excel – عرض نتائج جدول بيانات محوري المُرشَّحة بناءً على قيمة خلية
➤ طرق Excel المدمجة الأخرى – استخدام أدوات التقطيع (Slicers) كمرشحات تفاعلية لجداول Pivot
تصفية جدول بيانات محوري استنادًا إلى قيمة خلية معيّنة باستخدام كود VBA
إذا كنت تبحث عن تفاعل ديناميكي حقيقي—أي أن مرشّح الجدول المحوري يستجيب تلقائيًّا لأي قيمة تُدخلها في خلية—فإن VBA يوفّر لك حلاً مباشرًا وفعّالًا. ويُعدّ هذا الحل مثاليًّا خصوصًا في لوحات المعلومات أو القوالب المخصصة للزملاء، أو في أي حالة تتطلب تحديث المرشّح فورًا بمجرد تعديل خلية واحدة. ومع ذلك، تتطلب هذه الطريقة معرفة أساسية بمحرر VBA، كما يجب حفظ مصنف العمل الخاص بك بتنسيق يدعم الماكروهات ().xlsm).
يتيح لك كود VBA التالي ربط خلية بورقة العمل ديناميكيًّا بمرشّح جدول محوري. اتبع هذه الخطوات بعناية، وتأكد من تعديل اسم ورقة العمل واسم الجدول المحوري ومراجع الحقول وفقًا لمتطلبات مصنف العمل الخاص بك:
الخطوة 1:أدخل القيمة التي تريد استخدامها لتصفية الجدول المحوري في خلية بورقة العمل (على سبيل المثال، اكتب أو اختر قيمة التصفية في الخلية)H6).
الخطوة 2:افتح ورقة العمل التي تحتوي على الجدول المحوري المستهدف. انقر بزر الماوس الأيمن على لسان تبويب الورقة في أسفل Excel، ثم اخترView Code من القائمة السياقية. وسيؤدي ذلك إلى فتح نافذة محرر VBA الخاصة بورقة العمل.
الخطوة 3:في نافذةMicrosoft Visual Basic for Applications(VBA) المفتوحة، الصق الكود التالي في وحدة كود ورقة العمل (وليس في وحدة قياسية):
كود VBA: تصفية جدول بيانات محوري استنادًا إلى قيمة خلية
Private Sub Worksheet_Change(ByVal Target As Range)
'تم التحديث بواسطة 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 Analyze.
- «Category» هو الحقل الذي تريد تصفيةه، ويجب أن يتطابق تمامًا مع اسم الشرط.
- H6 هي خلية التصفية. تأكد من أن القيمة تطابق عنصرًا في قائمة التصفية.
- يجب أن تتطابق قيم التصفية حرفيًّا؛ إذ قد تؤدي المسافات الزائدة أو الأخطاء المطبعية إلى أخطاء أو نتائج فارغة.
الخطوة 4:اضغط علىAlt + Q لإغلاق محرر VBA والعودة إلى Excel.
الآن، سيقوم جدولك المحوري تلقائيًّا بتصفية البيانات لعرض القيم التي تتطابق فقط مع المحتوى المُدخل في الخليةH6. ويُفعَّل هذا الماكرو كلما تغيّرت القيمة في الخلية H6، مما يمنحك تحكمًا ديناميكيًّا سلسًا في ملخص بياناتك.
يمكنك تعديل القيمة في خلية التصفية في أي وقت—ويتم تحديث الجدول المحوري فورًا كلما غيّرت محتوى الخلية أو استبدلته.
استكشاف الأخطاء وإصلاحها:
- تأكد من تفعيل ماكروهات في مصنف العمل الخاص بك.
- تأكد مرة أخرى من أن ورقة العمل، والجدول المحوري، واسم الشرط تتطابق جميعها مع إعداداتك الفعلية.
- تأكد من أن قيمة التصفية في الخلية 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، وأن عمودCategory موجود في العمود 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 توفّر طريقة تفاعلية أخرى لتصفية الجداول المحورية. فأدوات التقطيع هي عناصر تحكم مرئية تتيح لك تصفية البيانات عبر واجهة بسيطة تعتمد على النقر. وعلى الرغم من أنه لا يمكن ربطها مباشرة بقيم الخلايا—ما يعني أنه لا يمكنك التحكم بأداة التقطيع عن طريق تغيير محتوى خلية—فإنها تظل بديهية وفعّالة جدًّا في اللوحات التفاعلية والتقارير التي يستخدمها الأشخاص غير التقنيين.
كيفية إضافة أداة تقطيع واستخدامها:
- حدد أي خلية داخل جدولك المحوري.
- انتقل إلى علامة التبويبPivotTable Analyze(أو علامة التبويب)Analyze في الإصدارات الأقدم)، ثم انقر فوقInsert Slicer.
- في مربع الحوارInsert Slicers، حدد الحقل الذي تريد التصفية به (مثل)Category)، ثم انقر فوقOK.
- سيظهر المُقطِّع (Slicer) على ورقة العمل الخاصة بك. انقر فوق زر لتصفية جدول البيانات المحوري حسب تلك القيمة. اضغط مع الاستمرار على مفتاحCtrl لتحديد عناصر متعددة.
يمكن تنسيق أدوات التقطيع وتغيير حجمها وربطها بعدة جداول بيانات محورية، مما يمكّنك من تطبيق تصفية متزامنة عبر تقارير متعددة. وهي مثالية خصوصًا في اللوحات التفاعلية أو المصنفات المشتركة، حيث قد لا يرتاح المستخدمون لاستخدام مرشحات القوائم المنسدلة، لكنهم ما زالوا بحاجة إلى تصفية البيانات بسهولة—بدون الحاجة إلى استخدام VBA أو تعديل الصيغ.
القيود:لا تدعم أدوات التقطيع الربط الأصلي بقيم الخلايا. لذا، إذا كان سير عملك يتطلب تصفية ديناميكية يُتحكم بها من خلال إدخال خلية، فعليك اعتبار أدوات التقطيع حلاً مكمّلاً وليست بديلاً عن الطرق المبنية على VBA أو الصيغ.
بالإضافة إلى ذلك، إذا كانت بياناتك مخزّنة فيجدول Excel (وليس جدول بيانات محوري)، فلا يزال بإمكانك استخدام أدوات التقطيع! ما عليك سوى تحديد الجدول، ثم الانتقال إلى تبويبتصميم الجدول (Table Design) > إدراج أداة تقطيع (Insert Slicer).
استكشاف الأخطاء وإصلاحها:إذا لم تبدُ أداة التقطيع وكأنها تُرشّح جدول البيانات المحوري، فتحقق مناتصالات التقرير (Report Connections)(ضمن تبويب)أداة التقطيع (Slicer)أوتحليل (Analyze)) للتأكد من أنها متصلة بشكل صحيح بجدول البيانات المحوري المطلوب.
كلٌّ من الطرق المذكورة أعلاه يخدم غرضًا مختلفًا: إذ يتيح لك VBA تصفيةً مرتبطة مباشرة بالخلايا، وتوفر الصيغ عرضًا ديناميكيًّا للنتائج، بينما توفّر أدوات التقطيع واجهةً رسوميةً سهلة الاستخدام للتصفية. اختر الطريقة الأنسب لاحتياجاتك من حيث الأتمتة والمرونة وسهولة الاستخدام. ولا تزال مرشحات القوائم المنسدلة التقليدية في الجداول المحورية متاحة كخيار احتياطي أساسي.
مقالات ذات صلة:
- كيف تدمج أوراق عمل متعددة في جدول محوري واحد في Excel؟
- كيف تُنشئ جدولًا محوريًّا من ملف نصي في Excel؟
- كيف تربط مرشح جدول محوري بخلية محددة في Excel؟
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في 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 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل