بالنسبة للغالبية العظمى من مستخدمي Excel ، عندما تظهر كلمة "تصفية البيانات" في رؤوسهم ، فإن المرشح الكلاسيكي المعتاد فقط من علامة التبويب البيانات - تصفية (بيانات - تصفية):
مثل هذا المرشح شيء مألوف ، بلا شك ، ويفعله في معظم الحالات. ومع ذلك ، هناك حالات تحتاج فيها إلى التصفية حسب عدد كبير من الشروط المعقدة في عدة أعمدة في وقت واحد. المرشح المعتاد هنا ليس مناسبًا جدًا وأريد شيئًا أكثر قوة. يمكن أن تكون هذه الأداة تصفية متقدم، خاصة مع القليل من "التشطيب بملف" (حسب التقليد).
قاعدة
للبدء ، أدخل بضعة أسطر فارغة أعلى جدول البيانات وانسخ رأس الجدول هناك - سيكون هذا نطاقًا بشروط (مظللة باللون الأصفر للتوضيح):
يجب أن يكون هناك سطر فارغ واحد على الأقل بين الخلايا الصفراء والجدول الأصلي.
تحتاج إلى إدخال المعايير (الشروط) في الخلايا الصفراء ، والتي وفقًا لها سيتم إجراء التصفية بعد ذلك. على سبيل المثال ، إذا كنت بحاجة إلى اختيار الموز في موسكو "Auchan" في الربع الثالث ، فستبدو الشروط كما يلي:
للتصفية ، حدد أي خلية في النطاق ببيانات المصدر ، افتح علامة التبويب البيانات وانقر فوق وبالإضافة إلى ذلك (بيانات - متقدم). في النافذة التي تفتح ، يجب إدخال نطاق به بيانات تلقائيًا وسيتعين علينا فقط تحديد نطاق الشروط ، أي A1: I2:
يرجى ملاحظة أنه لا يمكن تخصيص نطاق الشروط "بهامش" ، أي لا يمكنك تحديد خطوط صفراء فارغة إضافية ، لأن خلية فارغة في نطاق الشروط ينظر إليها Excel على أنها عدم وجود معيار ، وكلها فارغة كخط طلب لعرض جميع البيانات بشكل عشوائي.
مفاتيح انسخ النتيجة إلى موقع آخر سيسمح لك بتصفية القائمة غير الموجودة هناك على هذه الورقة (كما هو الحال مع عامل التصفية العادي) ، ولكن لتفريغ الصفوف المحددة في نطاق آخر ، والتي ستحتاج بعد ذلك إلى تحديدها في الحقل ضع النتيجة في النطاق. في هذه الحالة ، لا نستخدم هذه الوظيفة ، نتركها قائمة الفلاتر في المكان ثم انقر OK. سيتم عرض الصفوف المحددة على الورقة:
إضافة ماكرو
"حسنًا ، أين الراحة هنا؟" تسأل وستكون على حق. لا تحتاج فقط إلى إدخال الشروط في الخلايا الصفراء بيديك ، ولكن أيضًا فتح مربع حوار ، وإدخال النطاقات هناك ، والضغط على OK. حزين ، أوافق! لكن "كل شيء يتغير عندما يأتون ©" - وحدات الماكرو!
يمكن تسريع العمل باستخدام مرشح متقدم وتبسيطه بشكل كبير باستخدام ماكرو بسيط يقوم تلقائيًا بتشغيل المرشح المتقدم عند إدخال الشروط ، أي تغيير أي خلية صفراء. انقر بزر الماوس الأيمن فوق علامة تبويب الورقة الحالية وحدد الأمر النص المصدر (مصدر الرمز). في النافذة التي تفتح ، انسخ الكود التالي والصقه:
Private Sub Worksheet_Change (ByVal Target As Range) إذا لم يكن التقاطع (الهدف ، النطاق ("A2: I5")) لا شيء ثم عند الخطأ ، استئناف التالي ActiveSheet.ShowAllData Range ("A7"). إجراء CurrentRegion.AdvancedFilter: = xlFilterInPlace، CriteriaRange : = Range ("A1"). CurrentRegion End If End Sub سيتم تشغيل هذا الإجراء تلقائيًا عند تغيير أي خلية في ورقة العمل الحالية. إذا كان عنوان الخلية التي تم تغييرها يقع في النطاق الأصفر (A2: I5) ، فإن هذا الماكرو يزيل جميع عوامل التصفية (إن وجدت) ويعيد تطبيق المرشح الموسع على جدول البيانات المصدر بدءًا من A7 ، أي سيتم تصفية كل شيء على الفور ، على الفور بعد دخول الشرط التالي:
إذن كل شيء أفضل بكثير ، أليس كذلك؟ 🙂
تنفيذ الاستفسارات المعقدة
الآن بعد أن تمت تصفية كل شيء بسرعة ، يمكننا التعمق قليلاً في الفروق الدقيقة وتفكيك آليات الاستعلامات الأكثر تعقيدًا في الفلتر المتقدم. بالإضافة إلى إدخال التطابقات التامة ، يمكنك استخدام العديد من أحرف البدل (* و؟) وعلامات عدم المساواة الرياضية في مجموعة من الشروط لتنفيذ بحث تقريبي. لا يهم حالة الشخصية. من أجل الوضوح ، قمت بتلخيص جميع الخيارات الممكنة في جدول:
| معيار | نتيجة |
| غرام * أو غرام | تبدأ كل الخلايا بـ GrIe Grوسدادة للأذن, Grأبفروت, Grعنات وما إلى ذلك. |
| = بصل | كل الخلايا بالضبط وبالكلمة فقط انحناءة، أي المطابقة التامة |
| * ليف * أو * ليف | تحتوي على خلايا ليف كيف تسطير ، أي Оليفأن, ليفep, وفقاليف وما إلى ذلك. |
| = ص * ت | كلمات تبدأ ب П وتنتهي مع В ie Пأولв, Пالأثيرв وما إلى ذلك. |
| كما | كلمات تبدأ ب А واحتواء كذلك СIe Аاااсin, Аناناс, Asai وما إلى ذلك. |
| = * الصورة | كلمات تنتهي بـ С |
| = ؟؟؟؟ | كل الخلايا التي تحتوي على نص مكون من 4 أحرف (أحرف أو أرقام ، بما في ذلك المسافات) |
| = م ؟؟؟؟؟؟ ن | كل الخلايا التي تحتوي على نص مكون من 8 أحرف تبدأ بـ М وتنتهي مع НIe Мانداريн, Мقلقн وما إلى ذلك. |
| = * ن ؟؟ أ | كل الكلمات التي تنتهي ب А، أين الحرف الرابع من النهاية НIe شعاعнikа, وفقاнozа وما إلى ذلك. |
| > = البريد | كل الكلمات تبدأ ب Э, Ю or Я |
| <> * س * | كل الكلمات التي لا تحتوي على حرف О |
| <> * فيتش | كل الكلمات ما عدا تلك المنتهية بـ فيروس نقص المناعة البشرية HIV (على سبيل المثال ، تصفية النساء حسب الاسم الأوسط) |
| = | كل الخلايا الفارغة |
| <> | جميع الخلايا غير الفارغة |
| > = 5000 | كل الخلايا ذات القيمة الأكبر من أو تساوي 5000 |
| 5 أو = 5 | كل الخلايا ذات القيمة 5 |
| > = 3/18/2013 | كل الخلايا التي لها تاريخ بعد 18 مارس 2013 (شامل) |
نقاط خفية:
- علامة * تعني أي رقم بأي أحرف ، و؟ - أي شخصية واحدة.
- يختلف المنطق في معالجة الاستعلامات النصية والرقمية قليلاً. لذلك ، على سبيل المثال ، لا تعني الخلية الشرطية التي تحتوي على الرقم 5 البحث عن جميع الأرقام التي تبدأ بخمسة ، ولكن خلية الشرط التي تحتوي على الحرف B تساوي B * ، على سبيل المثال ، ستبحث عن أي نص يبدأ بالحرف B.
- إذا لم يبدأ الاستعلام النصي بعلامة = ، فيمكنك حينئذٍ وضع * في النهاية.
- يجب إدخال التواريخ بالتنسيق الأمريكي شهر يوم سنة ومن خلال جزء صغير (حتى لو كان لديك إعدادات Excel والإعدادات الإقليمية).
الوصلات المنطقية AND-OR
تعتبر الشروط المكتوبة في خلايا مختلفة ، ولكن في نفس السطر ، مترابطة بواسطة عامل منطقي И (و):
أولئك. تصفية الموز بالنسبة لي في الربع الثالث ، على وجه التحديد في موسكو وفي نفس الوقت من أوشان.
إذا كنت بحاجة إلى ربط الشروط بمعامل منطقي OR (OR)، ثم يحتاجون فقط إلى إدخالها في سطور مختلفة. على سبيل المثال ، إذا احتجنا إلى العثور على جميع طلبات مدير Volina لخوخ موسكو وجميع الطلبات الخاصة بالبصل في الربع الثالث في سامارا ، فيمكن تحديد ذلك في مجموعة من الشروط على النحو التالي:
إذا كنت بحاجة إلى فرض شرطين أو أكثر على عمود واحد ، فيمكنك ببساطة تكرار عنوان العمود في نطاق المعايير وإدخال الثاني والثالث وما إلى ذلك تحته. مصلحات. لذلك ، على سبيل المثال ، يمكنك تحديد جميع المعاملات من مارس إلى مايو:
بشكل عام ، بعد "الانتهاء من ملف" ، يظهر عامل التصفية المتقدم ليكون أداة جيدة تمامًا ، في بعض الأماكن ليس أسوأ من الفلتر التلقائي الكلاسيكي.
- Superfilter على وحدات الماكرو
- ما هي وحدات الماكرو ، وأين وكيف يتم إدراج التعليمات البرمجية للماكرو في Visual Basic
- الجداول الذكية في Microsoft Excel