تصفية العمود الأفقي في Excel

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

ومع ذلك ، لا توجد قواعد بدون استثناءات وبتكرار منتظم إلى حد ما ، يُطلب مني ما يجب فعله إذا ظهر في العمل جدول ذو اتجاه دلالي أفقي ، أو جدول حيث الصفوف والأعمدة لها نفس الوزن في المعنى:

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

الطريقة 1. وظيفة التصفية الجديدة

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

دعنا نضيف السطر التالي فوق جدولنا ونكتب حالة كل عمود فيه:

• لنفترض أننا نرغب دائمًا في عرض العمودين الأول والأخير (الرؤوس والإجماليات) ، لذلك قمنا بتعيين القيمة = TRUE بالنسبة لهم في الخليتين الأولى والأخيرة من المصفوفة.
• بالنسبة للأعمدة المتبقية ، ستكون محتويات الخلايا المقابلة عبارة عن صيغة تتحقق من الحالة التي نحتاجها باستخدام الدوال И (و) or OR (OR). على سبيل المثال ، أن المجموع يقع في النطاق من 300 إلى 500.

بعد ذلك ، يبقى فقط استخدام الوظيفة منقي لتحديد الأعمدة التي تحتوي المصفوفة المساعدة فوقها على قيمة TRUE:

وبالمثل ، يمكنك تصفية الأعمدة حسب قائمة معينة. في هذه الحالة ، ستساعد الوظيفة COUNTIF (كونتيف)، والذي يتحقق من عدد مرات ظهور اسم العمود التالي من رأس الجدول في القائمة المسموح بها:

الطريقة الثانية. وضع جدول محوري بدلاً من الجدول المعتاد

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

• أن يكون لديك سطر رأس "صحيح" من سطر واحد بدون خلايا فارغة ومدمجة - وإلا فلن ينجح إنشاء جدول محوري ؛
• لا تحتوي على نسخ مكررة في تسميات الصفوف والأعمدة - سوف "تنهار" في الملخص في قائمة من القيم الفريدة فقط ؛
• تحتوي فقط على أرقام في نطاق القيم (عند تقاطع الصفوف والأعمدة) ، لأن الجدول المحوري سيطبق بالتأكيد نوعًا من وظيفة التجميع عليها (مجموع ، متوسط ​​، إلخ) ولن يعمل هذا مع النص

إذا تم استيفاء جميع هذه الشروط ، فمن أجل بناء جدول محوري يشبه جدولنا الأصلي ، يجب توسيعه (الجدول الأصلي) من الجدول الترافقي إلى جدول مسطح (عادي). وأسهل طريقة للقيام بذلك هي باستخدام الوظيفة الإضافية Power Query ، وهي أداة تحويل بيانات قوية مضمنة في Excel منذ عام 2016. 

هذه هي:

1. لنحول الجدول إلى أمر ديناميكي "ذكي" الصفحة الرئيسية - تنسيق كجدول (الصفحة الرئيسية - تنسيق كجدول).
2. تحميل في Power Query باستخدام الأمر البيانات - من جدول / نطاق (بيانات - من جدول / نطاق).
3. نقوم بتصفية السطر بالمجموع (سيكون للملخص مجاميعه الخاصة).
4. انقر بزر الماوس الأيمن فوق عنوان العمود الأول وحدد قم بإلغاء انهيار الأعمدة الأخرى (أعمدة أخرى Unpivot). يتم تحويل جميع الأعمدة غير المحددة إلى عمودين - اسم الموظف وقيمة مؤشره.
5. تصفية العمود بالمجاميع التي دخلت العمود السمة.
6. نقوم ببناء جدول محوري وفقًا للجدول المسطح (المقيس) الناتج بالأمر الصفحة الرئيسية - إغلاق وتحميل - إغلاق وتحميل ... (الصفحة الرئيسية - إغلاق وتحميل - إغلاق وتحميل إلى ...).

يمكنك الآن استخدام القدرة على تصفية الأعمدة المتوفرة في الجداول المحورية - علامات الاختيار المعتادة أمام الأسماء والعناصر مرشحات التوقيع (مرشحات التسمية) or مرشحات حسب القيمة (مرشحات القيمة):

وبالطبع ، عند تغيير البيانات ، ستحتاج إلى تحديث استعلامنا والملخص باستخدام اختصار لوحة المفاتيح CTRL+قديم+F5 أو فريق البيانات - تحديث الكل (البيانات - تحديث الكل).

الطريقة الثالثة. ماكرو في VBA

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

لنفترض أننا نرغب في تصفية الأعمدة بشكل سريع حيث يلبي اسم المدير في رأس الجدول القناع المحدد في الخلية الصفراء A4 ، على سبيل المثال ، يبدأ بالحرف "A" (أي الحصول على "Anna" و "Arthur " نتيجة ل). 

كما في الطريقة الأولى ، نقوم أولاً بتنفيذ صف نطاق إضافي ، حيث سيتم التحقق من معيارنا في كل خلية بواسطة صيغة ويتم عرض القيم المنطقية TRUE أو FALSE للأعمدة المرئية والمخفية ، على التوالي:

ثم دعونا نضيف ماكرو بسيط. انقر بزر الماوس الأيمن فوق علامة تبويب الورقة وحدد الأمر مصدر (مصدر الرمز). انسخ والصق رمز فبا التالي في النافذة التي تفتح:

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Address = "$ A $ 4" ثم لكل خلية في النطاق ("D2: O2") إذا كانت الخلية = True ثم cell.EntireColumn.Hidden = خطأ آخر cell.EntireColumn.Hidden = True End إذا كانت الخلية التالية تنتهي إذا End Sub  

منطقها كما يلي:

• بشكل عام ، هذا هو معالج الحدث تغيير ورقة العمل، على سبيل المثال ، سيتم تشغيل هذا الماكرو تلقائيًا عند إجراء أي تغيير على أي خلية في الورقة الحالية.
• سيكون المرجع إلى الخلية التي تم تغييرها دائمًا في المتغير الهدف.
• أولاً ، نتحقق من أن المستخدم قد قام بتغيير الخلية بالضبط بالمعيار (A4) - ويتم ذلك بواسطة المشغل if.
• ثم تبدأ الدورة لكل ... للتكرار فوق الخلايا الرمادية (D2: O2) بقيم مؤشر TRUE / FALSE لكل عمود.
• إذا كانت قيمة الخلية الرمادية التالية هي TRUE (صواب) ، فلن يكون العمود مخفيًا ، وإلا فإننا نخفيه (الخاصية مخفي).

•  وظائف الصفيف الديناميكية من Office 365: FILTER و SORT و UNIC
• جدول محوري برأس متعدد الأسطر باستخدام Power Query
• ما هي وحدات الماكرو وكيفية إنشائها واستخدامها