تصفية البيانات في Excel - سهلة وشاملة
يمكن أن يساعد أمر Excel Filter في تصفية البيانات في نطاق أو جدول لإظهار البيانات التي تحتاجها فقط وإخفاء الباقي. يمكنك تطبيق عوامل التشغيل المضمنة به لتصفية الأرقام أو النصوص أو التواريخ بسهولة مثل تصفية جميع الأرقام التي تزيد عن أو تساوي رقمًا معينًا ، أو يبدأ نص التصفية أو ينتهي أو يحتوي على حرف أو كلمة معينة ، أو إظهار الصفوف فقط حيث تاريخ الاستحقاق قبل أو بعد تاريخ معين وما إلى ذلك. بمجرد تصفية البيانات في نطاق أو جدول ، إذا تم تغيير البيانات ، يمكنك إما إعادة تطبيق عامل تصفية للحصول على البيانات الجديدة ، أو مسح عامل تصفية لعرض كافة البيانات.
في هذا البرنامج التعليمي ، سنشرح كيفية إضافة عوامل التصفية أو استخدامها أو إزالتها في Excel. علاوة على ذلك ، سنوجهك إلى كيفية تحسين ميزة التصفية للتعامل مع مشاكل Excel الأكثر تعقيدًا.
جدول المحتويات: [ يخفي ]
1. كيفية إضافة عامل تصفية في Excel
لتصفية البيانات في نطاق أو جدول ، تحتاج أولاً إلى إضافة عامل تصفية إلى بياناتك. يوفر هذا القسم 3 طرق لإضافة عامل تصفية في Excel.
1.1 أمر التصفية في علامة التبويب البيانات
حدد أي خلايا في نطاق أو جدول تريد إضافة عامل تصفية ، انقر فوق البيانات > منقي.
1.2 أمر التصفية في علامة التبويب الصفحة الرئيسية
حدد أي خلايا في نطاق أو جدول تريد إضافة عامل تصفية ، انقر فوق الصفحة الرئيسية > فرز وتصفية > منقي.
1.3 إضافة عامل تصفية مع الاختصار
حدد أي خلايا في نطاق أو جدول تريد إضافة عامل تصفية ، ثم اضغط على CTRL + تغير + L مفاتيح.
بعد تطبيق إحدى العمليات المذكورة أعلاه ، يمكنك رؤية إضافة أسهم منسدلة في رؤوس الأعمدة للخلايا المحددة.
2. كيفية تطبيق عامل التصفية في Excel (معيار واحد أو عدة معايير)
بعد إضافة الفلتر ، تحتاج إلى تطبيقه يدويًا. سيوضح لك هذا القسم كيفية تطبيق عامل التصفية في عمود واحد أو أكثر في Excel.
2.1 تطبيق عامل التصفية على عمود واحد (معيار واحد)
إذا كنت تريد فقط تطبيق المرشح على عمود واحد ، مثل تصفية البيانات في العمود C كما هو موضح أدناه لقطة الشاشة. يرجى الانتقال إلى هذا العمود ، ثم القيام بما يلي.
- 1) انقر فوق سهم القائمة المنسدلة في رأس العمود.
- 2) حدد شرط التصفية حسب حاجتك.
- شنومكس) انقر فوق OK زر لبدء التصفية. انظر لقطة الشاشة:
يتم الآن تطبيق عامل التصفية على العمود C. وسيتم عرض جميع البيانات التي تفي بمعايير التصفية وسيتم إخفاء الباقي.
بعد تطبيق الفلتر ، يمكنك رؤية سهم القائمة المنسدلة يتحول إلى أيقونة مرشح .
من المهم جدًا أنه عند تحريك المؤشر فوق أيقونة الفلتر ، سيتم عرض معايير التصفية التي حددتها كتلميح للشاشة كما هو موضح في لقطة الشاشة أدناه. لذلك ، إذا نسيت المعايير التي حددتها لمرشح ، فما عليك سوى تحريك المؤشر فوق أيقونة الفلتر.
2.2 تطبيق مرشح بمعايير متعددة على أعمدة متعددة (معايير متعددة)
2.2.1 تطبيق مرشح بمعايير متعددة على أعمدة متعددة واحدًا تلو الآخر
إذا كنت تريد تطبيق الفلتر على أعمدة متعددة بمعايير متعددة ، فقط كرر الطريقة المذكورة أعلاه لعدة أعمدة واحدة تلو الأخرى.
بعد تطبيق عامل التصفية على أعمدة متعددة ، يمكنك رؤية الأسهم المنسدلة في الأعمدة المصفاة وقد تحولت إلى رموز التصفية.
2.2.2 تطبيق مرشح بمعايير متعددة في نفس الوقت على أعمدة متعددة
باستخدام الطريقة المذكورة أعلاه ، تحتاج إلى تطبيق مرشح على الأعمدة واحدًا تلو الآخر ، والنقطة الأكثر أهمية هي أن هذه الطريقة تدعم فقط لأي لبس معايير. قدم هنا الطرق الخاصة بك ليس فقط تطبيق عامل التصفية على أعمدة متعددة في وقت واحد ، ولكن أيضًا تطبيق كلا من لأي لبس و OR المعايير.
لنفترض أن لديك جدول بيانات كما هو موضح في لقطة الشاشة أدناه ، وتريد تصفية البيانات من أعمدة متعددة بناءً على معايير متعددة: المنتج = AAA-1 والنظام> 80, or السعر الإجمالي> 10000. يرجى تجربة إحدى الطرق التالية لإنجازها.
2.2.2.1 تطبيق مرشح على أعمدة متعددة مع وظيفة تصفية متقدمة
قد تساعدك وظيفة التصفية المتقدمة في حل هذه المشكلة ، يرجى القيام بما يلي خطوة بخطوة.
1. أولاً ، قم بإنشاء المعايير في ورقة العمل كما هو موضح أدناه لقطة الشاشة.
ملحوظة: بالنسبة لمعايير AND ، يرجى وضع قيم المعايير في خلايا مختلفة من نفس الصف. وضع قيمة المعايير "أو" في الصف الآخر.
2. انقر البيانات > متقدم لتشغيل تصفية متقدم وظيفة.
3. في ال تصفية متقدم مربع الحوار ، يرجى تكوين على النحو التالي.
الآن يتم تصفية الأعمدة في وقت واحد بناءً على المعايير المحددة كما هو موضح في لقطة الشاشة أدناه.
2.2.2.2 قم بتطبيق المرشح بسهولة على أعمدة متعددة باستخدام أداة مذهلة
كما لأي لبس و OR ليس من السهل إدارة معايير التصفية بالطريقة المذكورة أعلاه ، نوصي بشدة هنا باستخدام سوبر تصفية سمة من سمات كوتولس ل إكسيل. باستخدام هذه الميزة ، يمكنك تطبيق عامل التصفية على أعمدة متعددة بمعياري "و" و "أو" بسهولة في Excel.
1. بعد التثبيت كوتولس ل إكسيل، اضغط هنا كوتولس بلس > سوبر تصفية.
ثم سوبر تصفية يتم عرض الجزء على الجانب الأيمن من ورقة العمل.
بشكل افتراضي ، تتم إضافة مجموعتي معايير فارغتين بعلاقة OR بينهما في ملف سوبر تصفية جزء. والعلاقة بين المعايير في نفس المجموعة هي AND. يمكنك تغيير العلاقة بين المجموعات المختلفة بناءً على احتياجاتك.
2. في ال سوبر تصفية جزء ، يرجى تكوين معايير التصفية على النحو التالي.
نصيحة: القائمة المنسدلة الأولى لرؤوس الأعمدة ، والثانية لأنواع المرشحات (يمكنك الاختيار نص ، رقم ، تاريخ ، سنة ، تنسيق نص وهكذا من هذه القائمة المنسدلة) ، والثالث لأنواع المعايير ، وآخر مربع نص لقيمة المعايير.
كمثال ذكرناه أعلاه ، أختار هنا منتج > نص > يساوي بشكل منفصل عن القوائم المنسدلة الثلاث ، ثم اكتب AAA-1 في مربع النص. انظر لقطة الشاشة:
الآن يتم عرض البيانات المتطابقة فقط في نطاق البيانات الأصلي ، ويتم إخفاء الباقي. انظر لقطة الشاشة:
نصيحة: باستخدام هذه الميزة المفيدة ، يمكنك إضافة المزيد من المعايير في مجموعة وإضافة المزيد من المجموعات وحفظ إعدادات التصفية الحالية كسيناريو للاستخدام المستقبلي وما إلى ذلك. إنها أداة لا غنى عنها يمكنها توفير الكثير من وقت العمل وتعزيز كفاءة العمل.
إذا كنت ترغب في الحصول على نسخة تجريبية مجانية (30 يومًا) من هذه الأداة المساعدة ، الرجاء الضغط لتنزيلهثم انتقل لتطبيق العملية حسب الخطوات المذكورة أعلاه.
انقر لمعرفة المزيد عن هذه الميزة.
3. كيفية استخدام عامل التصفية في Excel
في هذا القسم ، ستتعلم كيفية استخدام أمر التصفية لتصفية أنواع مختلفة من أنواع البيانات مثل النصوص والأرقام والتواريخ والتنسيقات.
3.1 تصفية القيم النصية
3.1.1 تصفية الخلايا النصية بمعايير محددة (تبدأ بـ ، وتنتهي ، وتحتوى ، وما إلى ذلك)
في الواقع ، عامل التصفية المدمج - مرشحات النص يوفر العديد من المعايير المفيدة لك لتصفية النص بسهولة. لنفترض أنك تريد تصفية الخلايا التي تبدأ بحرف معين مثل J ، يرجى القيام بما يلي لإنجازها.
1. أضف عامل تصفية إلى رأس العمود الخاص بنطاق البيانات الأصلي. انقر لمعرفة كيف.
2. انقر فوق سهم القائمة المنسدلة في خلية الرأس لفتح قائمة التصفية.
3. انقر مرشحات النص > يبدأ ب.
4. في ال تصفية تلقائية مخصصة في مربع الحوار ، أدخل الحرف المحدد (هنا أكتب حرف J) في مربع النص ، ثم انقر فوق حسنا.
نصيحة: يمكنك إضافة آخر و or Or معايير العلاقة كما تحتاج.
الآن تبدأ جميع الخلايا بالحرف J معروضة في العمود D كما هو موضح في لقطة الشاشة أدناه.
يبدو أنه من السهل تصفية الخلايا النصية بناءً على معايير محددة باستخدام عامل التصفية المدمج. ومع ذلك ، نظرًا لأن وظيفة التصفية لا تدعم تصفية النص بحساسية حالة الأحرف ، كيف يمكننا عمل مرشح حساس لحالة الأحرف في Excel؟ سيوضح لك هذا القسم طرق تحقيق ذلك.
3.1.2.1 ترشيح نص معين بحساسية حالة الأحرف بواسطة الصيغة والأمر تصفية
لنفترض أنك تريد تصفية جميع الأحرف الكبيرة لنص معين مثل "TEXT TOOLS" في العمود B ، يرجى القيام بما يلي.
1. قم بإنشاء عمود مساعد بجانب نطاق البيانات الأصلي (هنا أختار العمود D كعمود مساعد). أدخل الصيغة أدناه في الخلية الثانية ثم اضغط على مفتاح Enter. حدد خلية النتيجة ، واسحب لها مقبض الملء التلقائي أسفل للحصول على النتائج الأخرى.
= دقيق (B2، UPPER (B2))
ملحوظة: تساعد هذه الصيغة في تحديد الخلايا الكبيرة والصغيرة. إذا كانت الخلية تحتوي على جميع الأحرف الكبيرة ، فستكون النتيجة صحيح، خلاف ذلك ، ستحصل على النتيجة كـ خاطئة.
2. حدد العمود B و D (سيتم تحديد العمود C ، لا تهتم) ، انقر فوق Daتا> منقي لإضافة عوامل تصفية لهم.
3. انتقل إلى العمود B (يحتوي العمود على النصوص التي ستقوم بترشيحها) ، ثم قم بتكوينها على النحو التالي.
الآن فقط الأحرف الكبيرة والصغيرة من "أدوات النص" معروضة في العمود B.
4. انقر فوق سهم القائمة المنسدلة في العمود D ، وقم بإلغاء تحديد حدد الكل مربع الاختيار ، حدد خانة الاختيار الموجودة بجوار الحقيقة العنصر ، ثم انقر فوق OK .
ثم يتم تصفية جميع "أدوات النص" بأحرف كبيرة من النص في العمود B كما هو موضح في لقطة الشاشة أدناه.
3.1.2.2 ترشيح الخلايا بسهولة باستخدام أداة مدهشة
إذا كنت تريد فقط تصفية جميع الأحرف الكبيرة أو الأحرف الصغيرة في عمود ، فنوصيك هنا بامتداد مرشح خاص سمة من سمات كوتولس ل إكسيل. باستخدام هذه الميزة ، ستتم تصفية جميع النصوص ذات الأحرف الكبيرة أو الصغيرة بسهولة بعدة نقرات فقط.
1. حدد نطاق العمود الذي ستقوم بتصفية النصوص بداخله ، ثم انقر فوق كوتولس بلس > مرشح خاص > مرشح خاص.
2. في ال مرشح خاص مربع الحوار ، يرجى القيام بالإعدادات أدناه.
3. ثم أ كوتولس ل إكسيل ينبثق مربع الحوار ليخبرك بعدد الخلايا التي تم العثور عليها والتي سيتم تصفيتها ، انقر فوق OK .
الآن يتم تصفية جميع الخلايا الكبيرة أو الصغيرة على الفور كما هو موضح في لقطة الشاشة أدناه.
إذا كنت ترغب في الحصول على نسخة تجريبية مجانية (30 يومًا) من هذه الأداة المساعدة ، الرجاء الضغط لتنزيلهثم انتقل لتطبيق العملية حسب الخطوات المذكورة أعلاه.
انقر لمعرفة المزيد عن هذه الميزة.
إذا كنت تريد تصفية الخلايا حسب طول النص ، على سبيل المثال ، لتصفية الخلايا بأطوال نصية تساوي 10 أحرف ، فماذا يجب أن تفعل؟ هنا يمكن للطرق الثلاث التالية أن تقدم لك خدمة.
3.1.3.1 ترشيح الخلايا حسب طول النص باستخدام الأمر Filter
في الواقع ، يحتوي أمر التصفية على عامل تشغيل مضمن لحل هذه المشكلة ، يرجى القيام بما يلي.
1. حدد نطاق الخلايا المراد تصفيتها (هنا أحدد B1: B27) ، ثم أضف عامل تصفية إلى نطاق الأعمدة هذا بالنقر فوق البيانات > منقي.
2. انقر فوق سهم القائمة المنسدلة في رأس العمود ، ثم انقر فوق مرشحات النص > تصفية مخصص. انظر لقطة الشاشة:
3. في ال تصفية تلقائية مخصصة في مربع الحوار ، حدد المعايير على أنها متساوية ، واكتب 10 علامات استفهام (؟) كوضع نقش في مربع النص ، ثم انقر فوق OK .
نصائح: تشير علامات الاستفهام العشر هذه إلى أنها ستطابق السلسلة النصية التي يبلغ طولها 10.
يتم الآن تصفية جميع الخلايا التي يبلغ طول سلسلة النص فيها 10 (بما في ذلك المسافات) على الفور.
3.1.3.2 تصفية الخلايا حسب طول النص باستخدام الصيغة والأمر تصفية
بالإضافة إلى ذلك ، يمكنك استخدام وظيفة LEN لحساب طول سلسلة النص لكل خلية ، ثم تطبيق الأمر Filter لتصفية خلايا طول النص المطلوبة بناءً على النتيجة المحسوبة.
1. قم بإنشاء عمود مساعد مجاور لنطاق البيانات الأصلي. أدخل الصيغة أدناه فيه ثم اضغط على مفتاح Enter. حدد خلية النتيجة ثم اسحبها مقبض الملء التلقائي أسفل للحصول على النتائج الأخرى.
= LEN (B2)
أنت الآن تحصل على طول النص لكل خلية في العمود المحدد.
2. حدد عمود المساعد (بما في ذلك الرأس) ، انقر فوق البيانات > منقي لإضافة عامل تصفية إليه.
3. انقر فوق سهم القائمة المنسدلة ، وقم بإلغاء تحديد حدد الكل مربع لإلغاء تحديد كل العناصر ، ثم حدد المربع المجاور للرقم 10 فقط ، وأخيراً انقر فوق OK .
يتم الآن تصفية جميع الخلايا التي يبلغ طول سلسلة النص فيها 10 (بما في ذلك المسافات) على الفور.
3.1.3.3 ترشيح الخلايا بسهولة حسب طول النص باستخدام أداة مذهلة
هنا نوصي مرشح خاص فائدة كوتولس ل إكسيل لمساعدتك في تصفية الخلايا بسهولة حسب طول النص في Excel.
1. حدد نطاق العمود الذي ستقوم بتصفية الخلايا بناءً على طول نص معين ، انقر فوق كوتولس بلس > مرشح خاص > مرشح خاص. انظر لقطة الشاشة:
2. في ال مرشح خاص مربع الحوار ، يرجى تكوين على النحو التالي.
3. كوتولس ل إكسيل ينبثق مربع الحوار ليخبرك بعدد الخلايا التي تم العثور عليها والتي سيتم تصفيتها ، انقر فوق OK على المضي قدما.
ثم يتم تصفية جميع الخلايا ذات أطوال السلسلة النصية التي تساوي 10 كما هو موضح في لقطة الشاشة أدناه.
إذا كنت ترغب في الحصول على نسخة تجريبية مجانية (30 يومًا) من هذه الأداة المساعدة ، الرجاء الضغط لتنزيلهثم انتقل لتطبيق العملية حسب الخطوات المذكورة أعلاه.
3.2 أرقام التصفية
في Excel ، من السهل جدًا تصفية الأرقام باستخدام أمر Number Filters.
لنفترض أنك تريد تصفية الخلايا بأرقام بين 15000 و 20000 في عمود (مثل العمود C كما هو موضح أدناه) ، يمكنك القيام بما يلي لتحقيق ذلك.
1. حدد نطاق العمود الذي يحتوي على الأرقام التي ستعمل على تصفيتها ، انقر فوق البيانات > منقي لإضافة عامل تصفية.
2. بعد إضافة عامل التصفية ، يرجى التهيئة على النحو التالي.
نصيحة: نظرًا لأنني أرغب في تصفية الخلايا بأرقام بين 15000 و 20000 ، فأنا هنا أدخل 15000 و 20000 بشكل منفصل في مربعات النص.
الآن يتم تصفية الخلايا التي تحتوي على أرقام بين 15000 و 20000 كما هو موضح في لقطة الشاشة أدناه.
3.3 تصفية التواريخ
بشكل افتراضي ، توفر ميزة "عوامل تصفية التاريخ" المضمنة العديد من المعايير المشتركة لتصفية التواريخ. كما ترى ، لا يوجد خيار مدمج لتصفية التواريخ حسب اليوم من الأسبوع أو عطلات نهاية الأسبوع أو يوم العمل. سيعلمك هذا القسم كيفية تحقيق هذه العمليات.
3.3.1 تصفية التواريخ حسب اليوم من الأسبوع أو عطلات نهاية الأسبوع
لنفترض أن لديك جدول بيانات كما هو موضح في لقطة الشاشة أدناه ، إذا كنت تريد تصفية التواريخ حسب اليوم من الأسبوع أو عطلات نهاية الأسبوع ، فيرجى تطبيق إحدى الطرق أدناه.
3.3.1.1 تصفية التواريخ حسب اليوم من الأسبوع أو عطلات نهاية الأسبوع باستخدام الصيغة والأمر تصفية
في هذا القسم ، ستقوم بتطبيق وظيفة WEEKDAY لحساب يوم الأسبوع من كل تاريخ ، ثم تقوم بتطبيق المرشح لتصفية يوم معين من الأسبوع أو عطلات نهاية الأسبوع حسب حاجتك.
1. في خلية فارغة (D2 في هذه الحالة) ، أدخل الصيغة أدناه واضغط على أدخل مفتاح. حدد خلية النتيجة ثم اسحب ملف مقبض الملء التلقائي فوق الخلايا أدناه لتطبيق هذه الصيغة.
= WEEKDAY (A2،XNUMX)
نصيحة:
ملحوظة: كما ترى ، ترجع الصيغة أرقامًا من 1 إلى 7، الذي يشير إلى يوم الأسبوع بدءًا من الأحد إلى السبت (1 ليوم الأحد و 7 ليوم السبت).
2. حدد نتائج الصيغة بالكامل (بما في ذلك خلية الرأس) ، انقر فوق البيانات > منقي.
3. انقر فوق سهم القائمة المنسدلة ، ثم قم بإلغاء تحديد حدد الكل مربع.
ثم يتم تصفية الخلايا كل عطلات نهاية الأسبوع أو أيام معينة من الأسبوع. انظر لقطة الشاشة:
3.3.1.2 يمكنك بسهولة تصفية التواريخ حسب اليوم من الأسبوع أو عطلات نهاية الأسبوع باستخدام أداة مذهلة
إذا لم تكن الطريقة المذكورة أعلاه مناسبة لك ، فنوصي هنا باستخدام مرشح خاص فائدة كوتولس ل إكسيل. باستخدام هذه الميزة ، يمكنك بسهولة تصفية الخلايا التي تحتوي على أي يوم من أيام الأسبوع بعدة نقرات فقط.
1. حدد الخلايا التي تحتوي على التواريخ التي تريد تصفيتها بناءً على يوم معين من الأسبوع.
2. انقر كوتولس بلس > مرشح خاص > مرشح خاص.
3. في ال مرشح خاص مربع الحوار ، يرجى تكوين على النحو التالي.
4. ثم أ كوتولس ل إكسيل ينبثق مربع الحوار مع عدد الخلايا التي تم العثور عليها وسيتم تصفيتها ، انقر فوق OK على المضي قدما.
الآن يتم تصفية الخلايا في جميع عطلات نهاية الأسبوع أو أي يوم من أيام الأسبوع.
إذا كنت ترغب في الحصول على نسخة تجريبية مجانية (30 يومًا) من هذه الأداة المساعدة ، الرجاء الضغط لتنزيلهثم انتقل لتطبيق العملية حسب الخطوات المذكورة أعلاه.
3.3.2 تصفية الخلايا حسب أيام العمل باستخدام أداة مذهلة
إلى جانب تصفية الخلايا حسب اليوم من الأسبوع أو عطلات نهاية الأسبوع ، فإن مرشح خاص فائدة كوتولس ل إكسيل يمكن أن يساعد أيضًا في تصفية الخلايا حسب أيام العمل.
1. تطبيق نفس الخطوات المذكورة أعلاه لتمكين أداة تصفية خاصة.
2. في ال مرشح خاص مربع الحوار ، قم بالإعدادات أدناه.
3. ثم أ كوتولس ل إكسيل ينبثق مربع الحوار. انقر OK على المضي قدما.
الآن يتم تصفية جميع خلايا يوم العمل.
3.4 تنسيقات التصفية
عادةً ما يدعم Excel تصفية البيانات بناءً على المعايير المرئية مثل لون الخط أو لون الخلية أو مجموعات الرموز كما هو موضح في لقطة الشاشة أدناه.
ومع ذلك ، إذا كنت تريد تصفية البيانات بناءً على معايير مرئية أخرى مثل نمط الخط (غامق ومائل) أو تأثيرات الخط (يتوسطه خط) أو خلية خاصة (تحتوي على صيغ) ، فلن يساعد Excel في تحقيق ذلك. يوفر هذا القسم طرقًا لمساعدتك في حل هذه المشكلات.
3.4.1 التصفية حسب النص المنسق الغامق / المائل
لنفترض أنك تريد تصفية البيانات عن طريق تنسيق النص الغامق أو المائل كما هو موضح في لقطة الشاشة أدناه ، فإن الطرق التالية يمكن أن تفيدك. الرجاء القيام بما يلي.
3.4.1.1 تصفية النص الغامق / المائل بتنسيق مع الصيغة والأمر تصفية
يمكن أن يساعد الجمع بين صيغة Get.Cell والأمر Filter في تصفية النص المنسق الغامق في نطاق عمود.
1. انقر الصيغ > حدد الاسم.
2. في ال اسم جديد مربع الحوار ، تحتاج إلى:
صيغة الصيغة:
=GET.CELL(type_num, reference)
حجج الصيغة
3. حدد خلية فارغة في نفس الصف من B2 ، واكتب الصيغة أدناه فيها ثم اضغط على أدخل مفتاح. حدد خلية النتيجة ، واسحب لها مقبض الملء التلقائي فوق الخلايا أدناه لتطبيق هذه الصيغة.
= Filter_Bold_Cells
4. حدد خلايا النتيجة بالكامل (بما في ذلك الرأس) ، انقر فوق البيانات > منقي.
5. انقر فوق سهم القائمة المنسدلة ، وحدد فقط المربع المجاور لـ الحقيقة الخيار، ثم انقر فوق حسنا.
ثم يتم تصفية جميع الخلايا النصية الغامقة أو المائلة. انظر لقطة الشاشة:
3.4.1.2 قم بتصفية النص المنسق بالخط العريض أو المائل باستخدام الأمرين Find and Replace and Filter
يمكنك أيضًا استخدام مجموعة أوامر البحث والاستبدال والتصفية لتحقيق ذلك.
1. حدد نطاق الأعمدة الذي يحتوي على الخلايا النصية الغامقة أو المائلة التي ستتم تصفيتها ، ثم اضغط على CTRL + F مفاتيح.
2. في ال بحث واستبدال مربع الحوار ، تحتاج إلى تكوين على النحو التالي.
3. الآن يتم تحديد جميع خلايا النص الغامق أو المائل في النطاق الأصلي ، انقر فوق الصفحة الرئيسية > لون التعبئة ثم اختر لون تعبئة للخلايا المحددة.
4. حدد نطاق العمود بالكامل مرة أخرى ، انقر فوق البيانات > منقي لإضافة عامل تصفية إليه.
5. انقر فوق سهم القائمة المنسدلة ، حدد تصفية حسب اللون، ثم انقر فوق لون التعبئة الذي حددته للتو أسفله تصفية حسب لون الخلية. انظر لقطة الشاشة:
ثم يتم تصفية جميع الخلايا النصية الغامقة أو المائلة.
3.4.1.3 يمكنك تصفية النص المنسق بالخط العريض أو المائل بسهولة باستخدام أداة رائعة
كما ذكرنا أعلاه ، فإن مرشح خاص فائدة كوتولس ل إكسيل يمكن أن يساعد في التصفية بسهولة باستخدام حساس لحالة الأحرف ، والتصفية حسب طول النص ، وتواريخ التصفية. هنا ، سوف نعلم كيفية تطبيق هذه الميزة لتصفية الخلايا النصية ذات التنسيق الغامق أو المائل في Excel.
1. حدد نطاق العمود (بما في ذلك الرأس) الذي يحتوي على الخلايا ذات التنسيق الغامق أو المائل التي ستتم تصفيتها.
2. انقر كوتولس بلس > مرشح خاص > تصفية جريئة / تصفية مائل. انظر لقطة الشاشة:
3. انقر OK في ظهرت كوتولس ل إكسيل للمضي قدمًا (ينبثق مربع الحوار هذا ليخبرك بعدد الخلايا التي تفي بالمعايير).
يتم الآن تصفية جميع الخلايا النصية المنسقة الغامقة أو المائلة.
إذا كنت ترغب في الحصول على نسخة تجريبية مجانية (30 يومًا) من هذه الأداة المساعدة ، الرجاء الضغط لتنزيلهثم انتقل لتطبيق العملية حسب الخطوات المذكورة أعلاه.
3.4.2 التصفية حسب نص منسق يتوسطه خط
لنفترض أنك تلقيت قائمة بأشخاص قاموا بتقطيعهم عن طريق إضافة خط يتوسطه خط إليهم ، وتحتاج إلى معرفة جميع الخلايا التي يتوسطها خط من خلال التصفية ، يمكن للطرق التالية أن تفيدك.
3.4.2.1 تصفية النص المنسق يتوسطه خط مع وظيفة معرّفة من قبل المستخدم والأمر تصفية
يمكنك تطبيق الوظيفة المعرفة من قبل المستخدم لتحديد خلايا النص المنسق يتوسطه خط ، ثم استخدام الأمر تصفية لتصفية جميع الخلايا التي يتوسطها خط بناءً على النتائج.
1. اضغط على قديم + F11 مفاتيح لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
2. في ال ميكروسوفت فيسوال باسيك للتطبيقات الإطار، انقر فوق إدراج > وحدة. ثم انسخ رمز VBA أدناه إلى ملف رمز نافذة.
Function HasStrike(Rng As Range) As Boolean
HasStrike = Rng.Font.Strikethrough
End Function
3. اضغط على قديم + Q مفاتيح لإغلاق ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
4. حدد خلية فارغة (يجب أن تكون هذه الخلية في نفس صف الخلية التي تريد حسابها) ، أدخل الصيغة أدناه واضغط على أدخل مفتاح. حدد خلية النتيجة ، واسحب لها مقبض الملء التلقائي فوق الخلية أدناه لتطبيق هذه الصيغة.
= HasStrike (B2)
ملحوظة: إذا كانت الخلية المقابلة لها تأثير خط يتوسطه خط ، فإنها ترجع صحيح، وإلا فإنه يعود خاطئة.
5. حدد خلايا النتيجة بالكامل (بما في ذلك خلية الرأس) ، انقر فوق البيانات > منقي.
6. ثم انقر فوق سهم القائمة المنسدلة> حدد فقط المربع الموجود بجوار خيار TRUE> انقر فوق حسنا. انظر لقطة الشاشة المعروضة.
يمكنك الآن رؤية جميع الخلايا المنسقة التي يتوسطها خط يتم ترشيحها.
3.4.2.2 يمكنك بسهولة تصفية النص المنسق الذي يتوسطه خط باستخدام أداة رائعة
مع مرشح خاص فائدة كوتولس ل إكسيل، يمكن تصفية جميع الخلايا المنسقة يتوسطها خط مباشرةً بعدة نقرات فقط.
1. حدد نطاق العمود الذي ستقوم بتصفية جميع الخلايا المنسقة يتوسطه خط ، انقر فوق كوتولس بلس > مرشح خاص > تصفية يتوسطها خط.
2. ثم أ كوتولس ل إكسيل ينبثق مربع الحوار ليخبرك بعدد الخلايا المؤهلة ، انقر فوق "موافق" OK على المضي قدما.
ثم يتم تصفية جميع الخلايا المنسقة يتوسطها خط كما هو موضح في لقطة الشاشة أدناه.
إذا كنت ترغب في الحصول على نسخة تجريبية مجانية (30 يومًا) من هذه الأداة المساعدة ، الرجاء الضغط لتنزيلهثم انتقل لتطبيق العملية حسب الخطوات المذكورة أعلاه.
3.4.3 التصفية حسب الخط أو لون الخلفية
كما ذكرنا في البداية في قسم تنسيقات عامل التصفية هذا ، يدعم Excel تصفية البيانات بناءً على المعايير المرئية مثل لون الخط أو لون الخلية أو مجموعات الرموز مع الميزة المضمنة فيه. يوضح هذا القسم كيفية تطبيق تصفية حسب اللون ميزة لتصفية الخلايا حسب الخط أو لون الخلفية في التفاصيل. وفي الوقت نفسه ، نوصي بوظيفة جهة خارجية مفيدة للمساعدة في حل هذه المشكلة.
3.4.3.1 التصفية حسب الخط أو لون الخلفية باستخدام الأمر Filter
يمكنك تطبيق ميزة "التصفية حسب اللون" مباشرةً في الأمر "تصفية" لتصفية الخلايا حسب الخط المحدد أو لون الخلفية في Excel.
1. حدد نطاق العمود الذي ستقوم بتصفية الخلايا حسب لون الخط أو الخلفية ، ثم انقر فوق البيانات > منقي.
2. انقر فوق سهم القائمة المنسدلة> تصفية حسب اللون. ثم يمكنك رؤية جميع ألوان الخلايا وألوان الخطوط الخاصة بنطاق الأعمدة الحالي مدرجة. انقر فوق أي لون خلية أو لون خط سيؤدي إلى تصفية جميع الخلايا بناءً عليه.
3.4.3.2 تصفية بألوان خلفية متعددة مع وظيفة معرّفة من قبل المستخدم والأمر تصفية
إذا كنت ترغب في تصفية الخلايا بألوان خلفية متعددة ، فيرجى تطبيق الطريقة أدناه.
لنفترض أنك تريد تصفية جميع الخلايا بألوان الخلفية البرتقالية والزرقاء في العمود B كما هو موضح في لقطة الشاشة أدناه. أولاً ، تحتاج إلى حساب مؤشر اللون لهذه الخلايا.
1. اضغط على قديم + F11 مفاتيح في نفس الوقت.
2. في ال ميكروسوفت فيسوال باسيك للتطبيقات الإطار، انقر فوق إدراج > وحدة. ثم انسخ كود فبا أدناه في نافذة الكود.
كود فبا: احصل على فهرس لون خلفية الخلية
Function GetColor(x As Range) As Integer
GetColor = x.Interior.ColorIndex
End Function
3. اضغط على قديم + Q مفاتيح لإغلاق ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
4. في عمود جديد ، اكتب رأسًا في الخلية الأولى (يجب أن تكون هذه الخلية في نفس صف رأس النطاق الأصلي).
5. حدد خلية فارغة بجوار خلية الرأس (هنا أحدد E2) ، أدخل الصيغة أدناه واضغط على أدخل مفتاح. حدد خلية النتيجة ، ثم اسحبها مقبض الملء التلقائي فوق الخلايا أدناه لتطبيق هذه الصيغة.
= GetColor (B2)
ملحوظة: إذا كانت الخلية لا تحتوي على لون تعبئة ، فإنها ترجع -4142.
6. حدد خلايا العمود المساعد (بما في ذلك الرأس) ، انقر فوق البيانات > منقي لإضافة عامل تصفية إلى العمود.
7. انقر فوق سهم القائمة المنسدلة لفتح القائمة المنسدلة ، ثم قم بتكوينها على النحو التالي.
الآن يتم تصفية الخلايا حسب ألوان الخلفية المحددة كما هو موضح في لقطة الشاشة أدناه.
3.4.3.3 يمكنك التصفية بسهولة حسب الخط أو لون الخلفية باستخدام أداة رائعة
مما لا شك فيه أنه من السهل تطبيق ميزة التصفية حسب اللون المدمجة لتصفية الخلايا حسب الخط أو لون الخلفية. ومع ذلك ، فإن العيب هو أن مربع القائمة المنسدلة يغطي محتوى البيانات الأصلية بحيث لا يمكننا عرض البيانات في أي وقت لاختيار الخط أو لون الخلفية بشكل صحيح. لتجنب هذه المشكلة ، نوصي هنا ببرنامج مرشح خاص فائدة كوتولس ل إكسيل.
1. حدد نطاق العمود الذي ستقوم بتصفية الخلايا حسب لون الخط أو الخلفية ، ثم انقر فوق كوتولس بلس > مرشح خاص > مرشح خاص.
2. في ال مرشح خاص مربع الحوار ، يرجى تكوين على النحو التالي.
ثم يتم تصفية جميع الخلايا ذات لون الخط المحدد أو لون الخلفية في النطاق المحدد.
إذا كنت ترغب في الحصول على نسخة تجريبية مجانية (30 يومًا) من هذه الأداة المساعدة ، الرجاء الضغط لتنزيلهثم انتقل لتطبيق العملية حسب الخطوات المذكورة أعلاه.
3.4.4 ترشيح الخلايا التي تحتوي على الصيغ
إذا كانت لديك قائمة طويلة من البيانات تحتوي على قيم وصيغ فعلية ، وتحتاج فقط إلى تصفية خلايا الصيغة ، فماذا يمكنك أن تفعل؟ يقدم هذا القسم طريقتين لتحقيق ذلك.
3.4.4.1 تصفية خلايا الصيغة بوظيفة معرّفة من قبل المستخدم والأمر تصفية
أولاً ، تحتاج إلى معرفة جميع خلايا الصيغة في القائمة باستخدام الوظيفة المعرفة من قبل المستخدم ، ثم قم بتطبيق الأمر Filter لتصفية خلايا الصيغة بناءً على النتائج.
1. اضغط على قديم + F11 مفاتيح لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
2. في ال ميكروسوفت فيسوال باسيك للتطبيقات الإطار، انقر فوق إدراج > وحدة، ثم انسخ كود فبا أدناه في نافذة الكود.
Function HasFormula(Cell)
HasFormula = Cell.HasFormula
End Function
3. اضغط على قديم + Q مفاتيح لإغلاق ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
4. حدد خلية فارغة ، يجب أن تكون هذه الخلية في نفس صف الخلية التي تريد التحقق مما إذا كانت خلية معادلة ، أدخل الصيغة أدناه فيها ثم اضغط على أدخل مفتاح. حدد خلية النتيجة ، واسحب لها مقبض الملء التلقائي فوق الخلايا أدناه لتطبيق هذه الصيغة.
= HasFormula (C2)
كما هو موضح في لقطة الشاشة أعلاه ، فإن النتائج خاطئة و صحيح، التي تشير إلى أنه إذا كانت الخلية المقابلة هي خلية صيغة ، فإنها ترجع TRUE ، وإلا فإنها ترجع FALSE.
5. حدد خلايا النتيجة (بما في ذلك خلية الرأس) ، انقر فوق البيانات > منقي لإضافة عامل تصفية إليه.
6. انقر فوق سهم القائمة المنسدلة ، وحدد فقط المربع المجاور لـ الحقيقة مربع، ثم انقر فوق حسنا.
بعد ذلك يمكنك رؤية جميع خلايا الصيغة التي تمت تصفيتها.
3.4.4.2 يمكنك تصفية خلايا الصيغة بسهولة باستخدام أداة مذهلة
اشرح هنا أداة Special Filter الخاصة بـ Kutools for Excel لمساعدتك على تصفية خلايا الصيغة بسهولة في قائمة بعدة نقرات فقط.
1. حدد القائمة التي تريد تصفية جميع خلايا الصيغة ، ثم انقر فوق كوتولس بلس > مرشح خاص > صيغة المرشح.
2. كوتولس ل إكسيل ينبثق مربع الحوار ليخبرك بعدد الخلايا المؤهلة ، انقر فوق "موافق" OK على المضي قدما.
ثم يتم تصفية جميع خلايا الصيغة كما هو موضح أدناه.
بالإضافة إلى ذلك ، يمكنك استخدام ملف مرشح خاص سمة من سمات كوتولس ل إكسيل لتصفية الخلايا بسهولة باستخدام تنسيقات أخرى ، مثل:
تصفية جميع الخلايا مع التعليقات ، انقر لمعرفة المزيد ...
تصفية جميع الخلايا المدمجة بناءً على قيمة معينة ، انقر لمعرفة المزيد ...
إذا كنت ترغب في الحصول على نسخة تجريبية مجانية (30 يومًا) من هذه الأداة المساعدة ، الرجاء الضغط لتنزيلهثم انتقل لتطبيق العملية حسب الخطوات المذكورة أعلاه.
3.5 التصفية باستخدام حرف البدل
في بعض الأحيان ، قد تنسى معايير البحث الدقيقة عند التصفية. في هذه الحالة ، نوصيك باستخدام أحرف البدل.
لا يوجد سوى 3 أحرف بدل في Excel:
حرف البدل | الوصف | مثال |
* (علامة النجمة) | يمثل أي عدد من الأحرف | على سبيل المثال، * التوت يجد "التوت الأسود"،"فراولة"،"التوت الأزرق" وما إلى ذلك وهلم جرا |
؟ (علامة استفهام) | يمثل أي حرف واحد | على سبيل المثال، l؟ ck يجد "لعق"،"قفل"،"نقص" وما إلى ذلك وهلم جرا |
~ (المد) تليها *, ?الطرق أو ~ | تمثل حقيقة * ،؟ or ~ حرف | على سبيل المثال، الوجه ~ * تسوق ويرى "متجر الوجه *" |
دعونا نرى كيفية استخدام أحرف البدل في التصفية.
لنفترض أنك بحاجة إلى تصفية جميع الخلايا التي تنتهي بـ Market في العمود B مثل لقطة الشاشة أدناه ، يرجى القيام بما يلي.
1. أولاً ، قم بإنشاء نطاق معايير. أدخل رأسًا مماثلًا لرأس العمود الأصلي ، ثم اكتب معايير التصفية في الخلية أدناه. انظر لقطة الشاشة:
2. انقر البيانات > المتقدمة.
3. في الافتتاح تصفية متقدم مربع الحوار ، تكوين على النحو التالي.
ثم يتم تصفية جميع الخلايا التي تنتهي بـ Market. انظر لقطة الشاشة:
استخدام * و ~ أحرف البدل في التصفية هو نفس العمليات المذكورة أعلاه.
3.6 التصفية مع مربع البحث المدمج
إذا كنت تستخدم Excel 2010 أو الإصدارات الأحدث ، فقد تلاحظ وجود مربع بحث مضمن في عامل تصفية Excel. سيوضح هذا القسم كيفية استخدام مربع البحث هذا لتصفية البيانات في Excel.
كما هو موضح في لقطة الشاشة أدناه ، فأنت تريد تصفية جميع الخلايا التي تحتوي على "Market" ، ويمكن أن يساعدك مربع البحث في إنجازها بسهولة.
1. حدد نطاق العمود الذي ستقوم بتصفية البيانات ، انقر فوق البيانات > منقي لإضافة عامل تصفية إليه.
2. انقر فوق سهم القائمة المنسدلة ، أدخل "سوق" في مربع البحث ، ثم انقر فوق حسنا.
يمكنك رؤية جميع النصوص المؤهلة مدرجة في الوقت الفعلي أثناء إدخال البيانات في مربع البحث.
ثم يتم تصفية جميع الخلايا التي تحتوي على "السوق" كما هو موضح في لقطة الشاشة أدناه.
4. نسخ البيانات المرئية فقط (تجاهل البيانات المخفية أو المصفاة)
بشكل افتراضي ، ينسخ Excel كلاً من الخلايا المرئية والمخفية. إذا كنت تريد نسخ الخلايا المرئية فقط بعد التصفية ، فيمكنك تجربة إحدى الطرق أدناه.انسخ البيانات المرئية فقط باستخدام مفاتيح الاختصار
يمكنك استخدام مفاتيح الاختصار لتحديد الخلايا المرئية فقط ، ثم نسخها ولصقها يدويًا في المكان المطلوب.
1. حدد النطاق الذي تريد نسخ الخلايا المرئية فقط. يتم تحديد كل من الخلايا المرئية والمخفية في هذه الخطوة.
2. اضغط على قديم و ; مفاتيح في نفس الوقت. الآن يتم تحديد الخلايا المرئية فقط.
3. اضغط على CTRL + C لنسخ الخلايا المحددة ، ثم اضغط على CTRL + V مفاتيح للصقها.
انسخ البيانات المرئية بسهولة باستخدام أداة رائعة فقط
نقدم هنا ملف لصق في المرئي سمة من سمات كوتولس ل إكسيل لك لنسخ البيانات المرئية بسهولة في Excel فقط. إلى جانب ذلك ، باستخدام هذه الميزة ، يمكنك نسخ القيم ولصقها إلى الخلايا المرئية فقط في النطاق الذي تمت تصفيته.
1. حدد النطاق المصفى الذي تريد نسخ الخلايا المرئية فقط ، ثم انقر فوق كوتولس > الفترة (من ... إلى) > لصق tس مرئي > الكل / فقط لصق القيم.
بالنسبة لخلايا الصيغة ، اختر الكل نسخ كل من النتيجة والصيغ ، اختر فقط لصق القيم ينسخ القيم الفعلية فقط.
2. في ظهرت لصق إلى النطاق المرئي في مربع الحوار ، اختر خلية فارغة لإخراج الخلايا المنسوخة ، ثم انقر فوق "نعم" OK.
ثم يتم نسخ ولصق الخلايا المرئية فقط في النطاق المصفى المحدد في مكان جديد.
ملحوظة: إذا تمت تصفية النطاق الوجهة ، فسيتم لصق القيم المحددة في الخلايا المرئية فقط.
إذا كنت ترغب في الحصول على نسخة تجريبية مجانية (30 يومًا) من هذه الأداة المساعدة ، الرجاء الضغط لتنزيلهثم انتقل لتطبيق العملية حسب الخطوات المذكورة أعلاه.
5. حذف الصفوف المخفية أو المرئية بعد التصفية
بالنسبة لقائمة تمت تصفيتها ، قد تحتاج إلى حذف الصفوف المخفية للاحتفاظ بالبيانات المرئية فقط. هنا في هذا القسم ، ستتعلم ثلاث طرق لحذف الصفوف المخفية أو المرئية في قائمة تمت تصفيتها في Excel.
احذف جميع الصفوف المخفية من ورقة العمل الحالية برمز VBA
يمكن أن يساعد رمز VBA أدناه في حذف جميع الصفوف المخفية من ورقة العمل الحالية في Excel.
ملحوظة: لا يقوم VBA بإزالة الصف المخفي في القائمة المصفاة فحسب ، بل يقوم أيضًا بإزالة الصفوف التي أخفيتها يدويًا.
1. في ورقة العمل تحتوي على صفوف مخفية تريد إزالتها ، اضغط على قديم + F11 مفاتيح في نفس الوقت لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
2. في ال ميكروسوفت فيسوال باسيك للتطبيقات الإطار، انقر فوق إدراج > وحدة، ثم انسخ رمز فبا أدناه في نافذة الوحدة النمطية.
كود فبا: احذف جميع الصفوف المخفية من ورقة العمل الحالية
Sub RemoveHiddenRows()
Dim xRow As Range
Dim xRg As Range
Dim xRows As Range
On Error Resume Next
Set xRows = Intersect(ActiveSheet.Range("A:A").EntireRow, ActiveSheet.UsedRange)
If xRows Is Nothing Then Exit Sub
For Each xRow In xRows.Columns(1).Cells
If xRow.EntireRow.Hidden Then
If xRg Is Nothing Then
Set xRg = xRow
Else
Set xRg = Union(xRg, xRow)
End If
End If
Next
If Not xRg Is Nothing Then
MsgBox xRg.Count & " hidden rows have been deleted", , "Kutools for Excel"
xRg.EntireRow.Delete
Else
MsgBox "No hidden rows found", , "Kutools for Excel"
End If
End Sub
3. اضغط على F5 مفتاح لتشغيل الكود.
4. ثم أ كوتولس ل إكسيل ينبثق مربع الحوار ليخبرك بعدد الصفوف المخفية التي تمت إزالتها ، انقر فوق OK لإغلاقه.
الآن تتم إزالة جميع الصفوف المخفية (بما في ذلك الصفوف المخفية التلقائية والصفوف المخفية يدويًا).
احذف الصفوف المرئية بعد التصفية باستخدام ميزة Go To
إذا كنت تريد فقط إزالة الصفوف المرئية في نطاق معين ، فإن ملف إذهب إلى يمكن أن تساعدك الميزة.
1. حدد النطاق المصفى الذي تريد إزالة الصفوف المرئية ، اضغط على F5 مفتاح لفتح إذهب إلى صندوق المحادثة.
2. في ال إذهب إلى مربع الحوار، انقر فوق ملصقات خاصة .
3. في ال انتقل إلى خاص في مربع الحوار ، حدد ملف الخلايا المرئية فقط الخيار، ثم انقر فوق OK .
4. الآن يتم تحديد جميع الخلايا المرئية. انقر بزر الماوس الأيمن فوق النطاق المحدد وانقر فوق احذف صف في قائمة السياق.
ثم يتم حذف جميع الخلايا المرئية.
احذف الصفوف المخفية أو المرئية بسهولة بعد التصفية باستخدام أداة مذهلة
الأساليب المذكورة أعلاه مملة وتستغرق وقتًا طويلاً. هنا نوصي حذف الصفوف والأعمدة المخفية (المرئية) سمة من سمات كوتولس ل إكسيل. باستخدام هذه الميزة ، يمكنك بسهولة حذف الصفوف المخفية أو المرئية ليس فقط في النطاق المحدد ، ولكن أيضًا في ورقة العمل الحالية أو أوراق العمل المتعددة المحددة أو المصنف بأكمله. الرجاء القيام بما يلي.
1. حدد النطاق الذي تريد إزالة جميع الصفوف المخفية أو المرئية منه.
الملاحظات:
2. انقر كوتولس > حذف > حذف الصفوف والأعمدة المخفية (المرئية).
3. في ال حذف الصفوف والأعمدة المخفية (المرئية) مربع الحوار ، تحتاج إلى تكوين على النحو التالي.
4. ثم يتم حذف جميع الصفوف المرئية أو المخفية مرة واحدة. في هذه الأثناء ، ينبثق مربع حوار لإخبارك بعدد الصفوف المحذوفة ، انقر فوق OK لإنهاء العملية برمتها.
إذا كنت ترغب في الحصول على نسخة تجريبية مجانية (30 يومًا) من هذه الأداة المساعدة ، الرجاء الضغط لتنزيلهثم انتقل لتطبيق العملية حسب الخطوات المذكورة أعلاه.
6. تصفية عبر أوراق متعددة
عادة ، من السهل تصفية البيانات في ورقة العمل. في هذا القسم ، ستتعلم كيفية تصفية البيانات بنفس المعايير عبر أوراق عمل متعددة لها بنية بيانات مشتركة.
لنفترض أن مصنفًا يحتوي على ثلاث أوراق عمل كما هو موضح في لقطة الشاشة أدناه ، فأنت الآن تريد تصفية البيانات عبر أوراق العمل الثلاثة هذه في نفس الوقت بنفس المعايير "المنتج = KTE"، يمكن أن يقدم لك رمز VBA أدناه معروفًا.
1. اضغط على قديم + F11 مفاتيح لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
2. في ال ميكروسوفت فيسوال باسيك للتطبيقات الإطار، انقر فوق إدراج > وحدة، ثم انسخ رمز VBA أدناه في نافذة الوحدة النمطية.
كود فبا: تصفية البيانات عبر أوراق عمل متعددة في نفس الوقت
Sub apply_autofilter_across_worksheets()
'Updateby Extendoffice 20210518
Dim xWs As Worksheet
On Error Resume Next
For Each xWs In Worksheets
xWs.Range("A1").AutoFilter 1, "=KTE"
Next
End Sub
ملحوظة: الخط "Xws.Range (“A1”. AutoFilter 1، “= KTE”)"في الرمز يشير إلى أنك ستقوم بتصفية البيانات في العمود A بالمعايير = KTE ، والرقم 1 هو رقم العمود في العمود A. يمكنك تغييرها حسب حاجتك. على سبيل المثال ، إذا كنت تريد تصفية جميع الأرقام الأكبر من 500 في العمود B ، فيمكنك تغيير هذا السطر إلى "Xws.Range (“B1”. AutoFilter 2، “> 500”)".
3. اضغط على F5 مفتاح لتشغيل الكود.
ثم يتم تصفية الأعمدة المحددة في نفس الوقت في جميع أوراق العمل الخاصة بالمصنف الحالي. انظر النتائج أدناه.
7. أعد تطبيق المرشح بعد تغيير البيانات
في بعض الأحيان قد تكون أجريت تغييرات على نطاق تمت تصفيته. ومع ذلك ، تظل نتيجة التصفية كما هي بغض النظر عن التغييرات التي أجريتها على النطاق (انظر لقطة الشاشة أدناه). في هذا القسم ، سنعرض لك طريقتين لإعادة تطبيق الفلتر يدويًا أو تلقائيًا على النطاق الحالي بحيث يتم تضمين التغييرات التي أجريتها.
أعد تطبيق المرشح يدويًا باستخدام الأمر "إعادة تطبيق"
يحتوي Excel على ملف أعد تطبيق للمساعدة في إعادة تطبيق الفلتر يدويًا. يمكنك التقديم على النحو التالي.
انقر البيانات > أعد تطبيق لإعادة تطبيق المرشح في الورقة الحالية.
بعد ذلك يمكنك رؤية النطاق الذي تمت تصفيته يتم إعادة تطبيقه لتضمين التغييرات التي أجريتها.
أعد تطبيق المرشح تلقائيًا باستخدام كود VBA
إذا كانت القائمة التي تمت تصفيتها بحاجة إلى التغيير بشكل متكرر ، فيجب عليك النقر بشكل متكرر لتطبيق ميزة إعادة التطبيق هذه. قم هنا بتوفير رمز VBA للمساعدة في إعادة تطبيق عامل التصفية تلقائيًا في الوقت الحقيقي عندما تتغير البيانات.
1. في ورقة العمل تحتوي على عامل التصفية الذي تريد إعادة تطبيقه تلقائيًا ، انقر بزر الماوس الأيمن فوق علامة تبويب الورقة وحدد مشاهدة الكود.
2. في الافتتاح ميكروسوفت فيسوال باسيك للتطبيقات نافذة ، انسخ رمز فبا أدناه في نافذة التعليمات البرمجية.
كود فبا: إعادة تطبيق عامل التصفية تلقائيًا عند تغيير البيانات
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet2").AutoFilter.ApplyFilter
End Sub
ملحوظة: في الكود ، "الورقة 2" هو اسم ورقة العمل الحالية. يمكنك تغييره حسب احتياجاتك.
3. اضغط على قديم + Q مفاتيح لإغلاق ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
من الآن فصاعدًا ، عند تغيير البيانات في القائمة المصفاة ، سيتم تعديل النتيجة المصفاة ديناميكيًا. انظر الصورة أدناه gif.
8. مسح أو إزالة عامل التصفية
لقد تعلمنا كيفية إضافة وتطبيق واستخدام عامل التصفية في المحتوى أعلاه. هنا سوف نتعلم كيفية مسح أو إزالة عامل التصفية في Excel.
8.1 امسح عامل التصفية من العمود
بعد تطبيق عامل التصفية في عمود ، إذا كنت تريد مسحه ، فانقر فوق الأيقونة التي تمت تصفيتها ، ثم انقر فوق مسح عامل التصفية من "اسم العنوان" من القائمة المنسدلة.
8.2 امسح كل عوامل التصفية في ورقة العمل
إذا قمت بتطبيق عامل تصفية على أعمدة متعددة ، وتريد مسحها جميعًا في نفس الوقت ، فانقر فوق البيانات > واضح.
ثم يتم مسح جميع المرشحات كما هو موضح في لقطة الشاشة أدناه.
8.3 امسح عوامل التصفية من جميع أوراق العمل في المصنف الحالي
لنفترض أنك قمت بتطبيق عوامل التصفية عبر أوراق عمل متعددة في مصنف وتريد مسح هذه المرشحات مرة واحدة. يمكن أن يقدم لك رمز VBA أدناه معروفًا.
1. افتح المصنف الذي ستقوم بمسح جميع عوامل التصفية منه ، ثم اضغط على قديم + F11 مفاتيح في نفس الوقت.
2. في الافتتاح ميكروسوفت فيسوال باسيك للتطبيقات الإطار، انقر فوق إدراج > وحدة، ثم انسخ رمز VBA أدناه في نافذة الوحدة النمطية.
كود فبا: امسح عوامل التصفية من كل أوراق العمل في المصنف الحالي
Sub Auto_Open()
'Updated by Extendoffice 20201113
Dim xAF As AutoFilter
Dim xFs As Filters
Dim xLos As ListObjects
Dim xLo As ListObject
Dim xRg As Range
Dim xWs As Worksheet
Dim xIntC, xF1, xF2, xCount As Integer
Application.ScreenUpdating = False
On Error Resume Next
For Each xWs In Application.Worksheets
xWs.ShowAllData
Set xLos = xWs.ListObjects
xCount = xLos.Count
For xF1 = 1 To xCount
Set xLo = xLos.Item(xF1)
Set xRg = xLo.Range
xIntC = xRg.Columns.Count
For xF2 = 1 To xIntC
xLo.Range.AutoFilter Field:=xF2
Next
Next
Next
Application.ScreenUpdating = True
End Sub
3. اضغط على F5 مفتاح لتشغيل الكود. ثم يتم مسح جميع عوامل التصفية من جميع أوراق العمل في المصنف الحالي.
8.4 إزالة جميع المرشحات في ورقة العمل
تساعد الطرق المذكورة أعلاه فقط في مسح الحالة المصفاة ، ولا تزال عوامل التصفية في ورقة العمل. إذا كنت تريد إزالة جميع عوامل التصفية من ورقة العمل ، فجرّب الطرق التالية.
قم بإزالة جميع عوامل التصفية في ورقة العمل عن طريق إيقاف تشغيل عامل التصفية
انقر البيانات > منقي لإيقاف تشغيل الميزة (الزر تصفية ليس في حالة التمييز).
قم بإزالة جميع عوامل التصفية في ورقة العمل عن طريق مفتاح الاختصار
بالإضافة إلى ذلك ، يمكنك تطبيق مفتاح الاختصار لإزالة جميع عوامل التصفية في ورقة العمل.
في ورقة العمل تحتوي على عوامل تصفية تريد إزالتها ، اضغط على CTRL + تغير + L مفاتيح في نفس الوقت.
ثم تتم إزالة جميع عوامل التصفية في ورقة العمل الحالية على الفور.
أفضل أدوات إنتاجية المكتب
عزز مهاراتك في Excel باستخدام Kutools for Excel، واختبر كفاءة لم يسبق لها مثيل. يقدم Kutools for Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية وتوفير الوقت. انقر هنا للحصول على الميزة التي تحتاجها أكثر...
يجلب Office Tab الواجهة المبوبة إلى Office ، ويجعل عملك أسهل بكثير
- تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
- فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
- يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!