تعد وظيفة التصفية في Microsoft Excel واحدة من أهم الوظائف التي يجب إتقانها. وبدون ذلك، سيكون من الصعب عليك العثور على البيانات التي تحتاجها. فيما يلي دورة تدريبية مكثفة حول استخدام FILTER في Excel.
من المفيد أيضًا ملاحظة أن الوظيفة ليست الطريقة الوحيدة لتصفية البيانات في MS Excel. لديك أدوات مثل Auto Filter وAdvanced Filter لتحقيق نفس الشيء، مع بعض التحذيرات المهمة التي سنناقشها في هذا الدليل.
ما هي وظيفة التصفية؟
وظائف أو صيغ Excel هي العناصر الأساسية لبرنامج Excel، حيث تتيح لك القيام بأشياء مثل العثور على متوسط مجموعة بيانات كبيرة أو إنشاء رسم بياني لمنحنى الجرس. كل وظيفة لها تركيبها الخاص، والذي يمكنك عادةً التحقق منه بمجرد إدخال اسم الوظيفة في Excel.
يتم استخدام وظيفة مرشح Excel، كما يوضح الاسم، "لتصفية" قيم نطاق محدد وفقًا لشروط معينة. يتم إدخال كل من النطاق والشروط التي سيتم استخدامها مع الوظيفة، مما يجعلها قابلة للتخصيص للغاية.
باستخدام المعلمات الصحيحة، يمكنك استخراج المعلومات الدقيقة التي تحتاجها من جدول البيانات دون الحاجة إلى مراجعة كل شيء للعثور على الإدخالات المطابقة يدويًا. وبما أن المخرجات موجودة في خلية، يمكنك ربط المزيد من الوظائف لإجراء العمليات الحسابية أو تصور النتائج في رسم بياني.
لماذا تُفضل وظيفة التصفية على وظيفة التصفية؟ مرشح متقدم؟
يلتزم معظم مبتدئين اكسل بأدوات تصفية البيانات المضمنة في برنامج Excel بدلاً من محاولة تعلم بناء جملة دالة. يعد عامل التصفية التلقائي هو الأسهل، حيث يسمح لك باستبعاد الأعمدة وتعيين معايير التصفية من واجهة قائمة على القائمة. ثم هناك عامل التصفية المتقدم الذي يتميز بقدرته على تطبيق معايير متعددة لتنفيذ أنظمة التصفية المعقدة.
فلماذا إذن تهتم باستخدام وظيفة التصفية؟
الميزة الرئيسية لاستخدام وظائف Excel بدلاً من إجراء أي عمليات يدويًا (باستخدام أداة Excel أخرى أو حتى أي برنامج آخر) هي أن الوظائف ديناميكية. يمنحك عامل التصفية التلقائي أو عامل التصفية المتقدم نتائج لمرة واحدة لا تتغير عند تعديل البيانات المصدر. من ناحية أخرى، تقوم الدالة FILTER بتحديث نتائجها وفقًا لذلك عندما تتغير البيانات.
تركيب دالة FILTER
إن بناء جملة صيغة FILTER واضح ومباشر بما فيه الكفاية:
=FILTER(array, include, [if_empty])
المصفوفة عبارة عن مجموعة فرعية مستطيلة من جدول البيانات، تتم الإشارة إليها عن طريق تحديد نطاق بين الخلية العلوية اليسرى والخلية السفلية اليمنى. على سبيل المثال، A3:E10 عبارة عن صفيف يتكون من الأعمدة من A إلى E والصفوف من 3 إلى 10..
المعلمة التالية هي ببساطة المعايير التي سيتم استخدامها، أو من الناحية الفنية مصفوفة منطقية. يتم إدخال ذلك في شكل تعبير يقيم قيمة نطاق من الخلايا (عادةً عمود) يُرجع TRUE أو FALSE. على سبيل المثال، A3:A10=”Pass” سوف يُرجع TRUE عندما تتطابق قيمة الخلية مع السلسلة المحددة.
أخيرًا، يمكنك إدخال قيمة ليتم إرجاعها بواسطة الدالة FILTER عندما لا تتطابق أي صفوف مع الشروط. يمكن أن تكون هذه سلسلة بسيطة مثل "لم يتم العثور على سجلات".
ذات صلة: هل تحتاج إلى معرفة مدى دقة عينة البيانات التي تتناسب مع مجموعة بيانات أكبر؟ راجع دليلنا على حساب الخطأ القياسي في Excel.
استخدام وظيفة التصفية
الآن بعد أن عرفنا بناء جملة الدالة FILTER، فلنلقِ نظرة على كيفية استخدام FILTER فعليًا في جدول بيانات.
تحتوي عينة البيانات التي نستخدمها في هذا العرض التوضيحي على مصفوفة من A2 إلى F11، حيث يتم جدولة درجات علم الأحياء لعشرة طلاب بالإضافة إلى التوزيع الطبيعي.
لنكتب دالة لتصفية الإدخالات بناءً على درجات الاختبار الخاصة بها (المدرجة في العمود D) وإرجاع فقط تلك التي حصلت على درجات أقل من 30. يجب أن يكون هذا هو بناء الجملة:
=FILTER(A2:F11,D2:D11<30,لم يتم العثور على أي تطابقات")
نظرًا لأن النتائج التي تمت تصفيتها هي مجموعة فرعية من المصفوفة، استخدم الوظيفة في خلية بها مساحة كافية بعدها. سنفعل ذلك أسفل الجدول الأصلي:
ونحصل على النتائج المتوقعة. يتم اختيار جميع الإدخالات ذات النقاط الأقل من 30 وعرضها بنفس تنسيق الجدول.
أنت لست مقيدًا بشرط واحد أيضًا. استخدم عامل التشغيل AND (*)، لربط تعبيرات متعددة كمعلمة واحدة، مما يؤدي إلى إنشاء مرشح أكثر تعقيدًا.
لنقم بإنشاء دالة تُرجع الإدخالات الواقعة بين 30 و70 علامة. هنا هو بناء الجملة والنتائج:
=FILTER(A2:F11,(D2:D11>30)*(D2:D11<70),“لم يتم العثور على أي تطابقات”)
بالنسبة للمعايير غير الحصرية، يمكنك أيضًا استخدام عامل التشغيل OR (+). وهذا يطابق عامل التصفية حتى إذا تم تقييم شرط واحد فقط من الشروط المضمنة على TRUE.
في الصيغة التالية، نستخدمها للعثور على القيم المتطرفة، عن طريق تصفية النتائج الأقل من 15 أو أكثر من 70.
=FILTER(A2:F11,(D2:D11<30)+(D2:D11>70),“لم يتم العثور على سجلات”)
أخيرًا، بدلاً من استخدام قيمة واحدة أو سلسلة واحدة للإرجاع عندما لا تعثر الدالة FILTER على أي شيء، يمكنك تحديد قيم لكل عمود للتأكد من أن الإخراج يظل دائمًا بتنسيق متسق..
أولاً، دعونا نجرب شرطًا نعرف أنه خطأ لنرى كيف يبدو افتراضيًا:
=FILTER(A2:F11,D2:D11>90,لم يتم العثور على أي تطابقات")
كما ترى، فإن النتيجة تحتوي على سلسلة واحدة فقط، وهي تتعارض مع التنسيق المتوقع. هذه ليست مشكلة عادة إلا إذا كنت تريد تغذية النتائج (أو بعض القيم منها) في صيغة أخرى.
لذلك دعونا نحاول إعطاء القيم الافتراضية بنفس تنسيق إدخال المصفوفة. يمكننا القيام بذلك عن طريق تحديد قيم مفصولة بفواصل ومحاطة بأقواس متعرجة. مثل هذا:
=FILTER(A2:F11,D2:D11>90,{"لا يوجد سجل"، "لا يوجد سجل"، "لا يوجد سجل"، 0})
يمنحنا هذا نتائج أكثر قبولًا، تتوافق مع بقية تنسيق جدول البيانات.
هل تستحق وظيفة التصفية كل هذا العناء؟
حتى إذا كنت تستخدم برنامج MS Excel فقط للاحتفاظ بالسجلات ولا تنوي إجراء أي حسابات معقدة، فإن وظيفة FILTER هي واحدة من الوظائف القليلة التي لا يزال يتعين عليك النظر فيها.
لأنه بمجرد وصول المصنف الخاص بك إلى حجم معين، يمكن أن يكون تحديد موقع البيانات يدويًا أمرًا مؤلمًا. وعلى الرغم من أن أدوات التصفية التلقائية والتصفية المتقدمة سهلة الاستخدام، إلا أن استخدام إحدى الوظائف يكون في الواقع أكثر ملاءمة على المدى الطويل حيث يتم تحديث النتائج من تلقاء نفسها ويمكن إقرانها بوظائف أخرى.
.