كيفية استخدام وظيفة Excel FILTER

يوفر Office 365 بعض الميزات الرائعة مثل XLOOKUP و SORT و FILTER.

عند تصفية البيانات في Excel ، في عالم ما قبل Office 365 ، كنا نعتمد في الغالب على عوامل التصفية المضمنة في Excel أو على عوامل التصفية الأكثر تقدمًا أو صيغ SUMPRODUCT المعقدة.غالبًا ما يكون هذا حلًا بديلًا معقدًا إذا كان عليك تصفية جزء من مجموعة البيانات.

ولكن مع ميزة FILTER الجديدة ، أصبح من السهل الآن تصفية أجزاء من مجموعة البيانات بسرعة بناءً على الظروف.

في هذا البرنامج التعليمي ، سأوضح لك مدى روعة ميزة FILTER الجديدة ، وبعض الأشياء المفيدة التي يمكنك فعلها بها.

لكن قبل أن أدخل في المثال ، دعنا نلقي نظرة سريعة على صيغة دالة FILTER.

إذا كنت ترغب في الحصول على هذه الميزات الجديدة في Excel ، يمكنك ذلكقم بالترقية إلى Office 365(انضم إلى البرنامج الداخلي للوصول إلى جميع الميزات / الصيغ)

وظيفة تصفية Excel - النحو

فيما يلي بناء جملة الدالة FILTER:

= FILTER (مصفوفة ، تتضمن ، [if_empty])

• مجموعة - هذا هو نطاق الخلايا حيث لديك البيانات وتريد تصفية بعض البيانات منه
• تتضمن - هذا هو الشرط الذي يخبر الوظيفة التي تسجل للتصفية
• [إذا_الفراغ] - هذه معلمة اختيارية حيث يمكنك تحديد ما تريد إرجاعه إذا لم تعثر وظيفة FILTER على نتائج.بشكل افتراضي (عندما لا يتم تحديده) تقوم بإرجاع #CALC!خطأ

الآن دعونا نلقي نظرة على بعض الأمثلة المدهشة لوظائف المرشح وما يمكن أن تفعله في السابق كان معقدًا للغاية بدونها.

مثال 1: تصفية البيانات بناءً على شرط واحد (منطقة)

لنفترض أن لديك مجموعة بيانات مثل المجموعة أدناه ، وتريد فقط تصفية جميع السجلات في الولايات المتحدة.

هذه هي صيغة FILTER التي تقوم بهذا:

= عامل التصفية ($ A $ 2: $ C $ 11 ، $ B $ 2: $ B $ 11 = "US")

تأخذ الصيغة أعلاه مجموعة البيانات كمصفوفة ، والشرط هو $ B $ 2: $ B $ 11 = ”US”

سيتسبب هذا الشرط في قيام الدالة FILTER بفحص كل خلية في العمود B (الخلايا ذات النطاق) وتصفية السجلات التي تطابق هذا الشرط فقط.

أيضًا ، في هذا المثال ، أضع البيانات الأصلية والمفلترة في نفس الورقة ، ولكن يمكنك أيضًا وضعها في أوراق منفصلة أو حتى في مصنفات.

ترجع دالة التصفية مصفوفة ديناميكية من النتائج (بمعنى أنه بدلاً من إرجاع قيمة ، فإنها تُرجع مصفوفة تتدفق إلى خلايا أخرى).

لهذا ، يجب أن يكون لديك منطقة تكون فيها النتيجة فارغة.يوجد بالفعل شيء ما في أي خلية في النطاق (E2: G5 في هذا المثال) وستعطيك الوظيفة الخطأ #SPILL.

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

في الصيغة أعلاه ، قمت بترميز قيمة النطاق بشكل ثابت ، ولكن يمكنك أيضًا وضعها في خلية والإشارة إلى تلك الخلية بقيمة النطاق.

على سبيل المثال ، في المثال أدناه ، لدي قيمة النطاق في الخلية I2 ثم أشير إليها في الصيغة:

=FILTER($A$2:$C$11,$B$2:$B$11=I1)

هذا يجعل الصيغة أكثر فائدة ، الآن تحتاج فقط إلى تغيير قيمة النطاق في الخلية I2 وسيتغير عامل التصفية تلقائيًا.

يمكنك أيضًا الحصول على قائمة منسدلة في الخلية I2 حيث يمكنك ببساطة إجراء تحديد وسيتم تحديث البيانات التي تمت تصفيتها على الفور.

مثال 2: تصفية البيانات بناءً على معيار واحد (أكبر أو أقل من)

يمكنك أيضًا استخدام عوامل المقارنة في وظائف التصفية واستخراج جميع السجلات الأكبر أو الأقل من قيمة معينة.

على سبيل المثال ، افترض أن لديك مجموعة البيانات الموضحة أدناه ، وتريد تصفية جميع السجلات التي تزيد مبيعاتها عن 10000.

يمكن للصيغة التالية القيام بذلك:

=FILTER($A$2:$C$11,($C$2:$C$11>10000))

تشير وسيطة المصفوفة إلى مجموعة البيانات بأكملها ، وفي هذه الحالة الحالة ($ C $ 2: $ C $ 11> 10000).

تتحقق الصيغة من كل سجل للقيمة الموجودة في العمود C.إذا كانت القيمة أكبر من 10000 ، يتم تصفيتها ، وإلا يتم تجاهلها.

إذا كنت ترغب في الحصول على جميع السجلات التي تقل عن 10000 ، يمكنك استخدام الصيغة التالية:

=FILTER($A$2:$C$11,($C$2:$C$11<10000))

يمكنك أيضًا أن تصبح أكثر إبداعًا باستخدام صيغة FILTER.على سبيل المثال ، إذا أردت تصفية أهم ثلاثة سجلات بناءً على المبيعات ، يمكنك استخدام الصيغة التالية:

=FILTER($A$2:$C$11,($C$2:$C$11>=LARGE(C2:C11,3)))

تستخدم الصيغة أعلاه الدالة LARGE للحصول على ثالث أكبر قيمة في مجموعة البيانات.ثم استخدم هذه القيمة في شرط دالة FILTER للحصول على جميع السجلات ذات المبيعات الأكبر من ثالث أكبر قيمة أو مساوية لها.

مثال 3: تصفية البيانات باستخدام شروط متعددة (و)

افترض أن لديك مجموعة البيانات التالية وتريد تصفية جميع السجلات في الولايات المتحدة بقيمة مبيعات أكبر من 10000.

هذا شرط "و" ، تحتاج إلى التحقق من شيئين - يجب أن تكون المنطقة في الولايات المتحدة ويجب أن تكون المبيعات أكثر من 10000.إذا تم استيفاء شرط واحد فقط ، فلا ينبغي تصفية النتائج.

فيما يلي معادلة التصفية التي ستعمل على تصفية السجلات باستخدام الولايات المتحدة كمنطقة ومع أكثر من 10000 من المبيعات:

=FILTER($A$2:$C$11,($B$2:$B$11="US")*($C$2:$C$11>10000))

請注意，標準（稱為包含參數）是 ($B$2:$B$11=”US”)*($C$2:$C$11>10000)

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

إذا لم تكن هناك سجلات متطابقة ، فستُرجع الدالة #CALC!خطأ.

إذا كنت تريد إرجاع شيء ذي معنى (وليس خطأ) ، فيمكنك استخدام صيغة مثل هذه:

=FILTER($A$2:$C$11,($B$2:$B$11="USA")*($C$2:$C$11>10000),"Nothing Found")

هنا ، لقد استخدمت "غير موجود" كمعامل ثالث ، والذي يتم استخدامه عند عدم العثور على سجل مطابق.

مثال 4: تصفية البيانات باستخدام معايير متعددة (أو)

يمكنك أيضًا تعديل معلمة "يحتوي على" في وظيفة FILTER للتحقق من شروط "أو" (حيث يمكن أن يكون أي شرط محدد صحيحًا).

على سبيل المثال ، لنفترض أن لديك مجموعة البيانات الموضحة أدناه ، وتريد تصفية السجلات حيث تكون الدولة هي الولايات المتحدة أو كندا.

هذه هي الصيغة للقيام بذلك:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+($B$2:$B$11="Canada"))

لاحظ أنه في الصيغة أعلاه ، أقوم فقط بإضافة الشرطين باستخدام عامل الجمع.نظرًا لأن كل شرط من هذه الشروط يُرجع مصفوفة من TRUE و FALSE ، يمكنني إضافة مصفوفة مجمعة ستكون TRUE إذا تم استيفاء أي من الشرطين.

مثال آخر قد يكون عندما تريد تصفية جميع السجلات حيث تكون الدولة هي الولايات المتحدة أو تكون قيمة المبيعات فيها أكثر من 10000.

الصيغة التالية ستفعل ذلك:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+(C2:C11>10000))

ملاحظة: عند استخدام الشرط AND في وظيفة التصفية ، استخدم عامل الضرب (*) ، وعند استخدام الشرط OR ، استخدم عامل الإضافة (+).

مثال 5: تصفية البيانات للسجلات أعلى / أقل من المتوسط

يمكنك استخدام الصيغ في دالة FILTER لتصفية السجلات واستخراجها بقيم أعلى أو أقل من المتوسط.

على سبيل المثال ، لنفترض أن لديك مجموعة البيانات الموضحة أدناه ، وتريد تصفية جميع السجلات بقيمة مبيعات أعلى من المتوسط.

يمكنك القيام بذلك باستخدام الصيغة التالية:

=FILTER($A$2:$C$11,C2:C11>AVERAGE(C2:C11))

مرة أخرى ، يمكنك استخدام الصيغة التالية للحصول على أقل من المتوسط:

=FILTER($A$2:$C$11,C2:C11<AVERAGE(C2:C11))

مثال 6: تصفية السجلات الزوجية فقط (أو السجلات الفردية)

إذا كنت بحاجة إلى تصفية جميع السجلات واستخراجها بسرعة في صفوف زوجية أو صفوف فردية ، فيمكنك استخدام وظيفة FILTER للقيام بذلك.

للقيام بذلك ، تحتاج إلى التحقق من رقم السطر في وظيفة التصفية وتصفية أرقام الأسطر التي تطابق شرط رقم السطر فقط.

لنفترض أن لديك مجموعة بيانات مثل أدناه وأريد فقط استخراج حتى السجلات من مجموعة البيانات هذه.

هذه هي الصيغة للقيام بذلك:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=0)

تستخدم الصيغة أعلاه وظيفة MOD للتحقق من رقم الصف (المعطى بواسطة الدالة ROW) لكل سجل.

تُرجع الصيغة MOD (ROW (A2: A11) -1,2،0) = XNUMX TRUE عندما يكون رقم الصف زوجيًا و FALSE عندما يكون فرديًا.لاحظ أنني قمت بطرح 2 من الجزء ROW (A11: A1) لأن السجل الأول موجود في الصف الثاني ، والذي يعدل أرقام الصفوف للتعامل مع الصف الثاني باعتباره السجل الأول.

وبالمثل ، يمكنك تصفية جميع السجلات الفردية بالصيغة التالية:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=1)

مثال 7: فرز البيانات التي تمت تصفيتها باستخدام صيغة

يتيح لنا استخدام وظيفة FILTER مع وظائف أخرى القيام بالمزيد.

على سبيل المثال ، إذا كنت تستخدم وظيفة FILTER لتصفية مجموعة بيانات ، فيمكنك استخدام الدالة SORT للحصول على نتائج مرتبة.

افترض أن لديك مجموعة بيانات كما هو موضح أدناه وتريد تصفية جميع السجلات التي تزيد مبيعاتها عن 10000.يمكنك استخدام وظيفة SORT مع هذه الوظيفة لضمان فرز البيانات الناتجة وفقًا للمبيعات.

الصيغة التالية ستفعل ذلك:

=SORT(FILTER($A$2:$C$11,($C$2:$C$11>10000)),3,-1)

تستخدم الوظيفة المذكورة أعلاه وظيفة FILTER للحصول على البيانات في العمود C بمبيعات أكبر من 10000.ثم استخدم المصفوفة التي تُرجعها الدالة FILTER في الدالة SORT لفرز البيانات وفقًا للمبيعات.

المعلمة الثانية في دالة SORT هي 3 ، وهي الفرز وفقًا للعمود الثالث.المعلمة الرابعة هي -1 ، والتي تفرز البيانات بترتيب تنازلي.

هذه 7 أمثلة لاستخدام وظيفة FILTER في Excel.

آمل أن تكون قد وجدت هذا البرنامج التعليمي مفيد.