FILTER function With Examples (دالة FILTER مع الأمثلة)
FILTER function With Examples
(دالة FILTER مع الأمثلة)
محتويات البرنامج التعليمي الشامل عن (FILTER function):
1- ماهي دالة (FILTER).
2- كيفية استخدام دالة (FILTER)
How to use the FILTER function
3- دالة (FILTER) أفقياََ Horizontally
4- طريقة أستخدام (FILTER function) مع:
1- دالة (FILTER) مع (ISBLANK)
2- دالة (FILTER) مع (AND & OR Formula)
3- دالة (FILTER) مع (Sum, Average, Min, Max, Count)
4- دالة (FILTER) مع (SORT function)
2- دالة (FILTER) مع (AND & OR Formula)
3- دالة (FILTER) مع (Sum, Average, Min, Max, Count)
4- دالة (FILTER) مع (SORT function)
5- كيفية تصفية أعمدة معينة (متجاورة أو غير متجاورة).
How to FILTER only specific columns
1- Filter some adjacent columns (متجاورة)
2- Filter non-adjacent columns (غير متجاورة)
2- Filter non-adjacent columns (غير متجاورة)
6- كيفية تصفية البيانات مع حساب القيم الفريدة
How to filter data with count unique values
7- كيفية تصفية البيانات وتجاهل الفراغات مع (FILTER & UNIQUE)
Unique values ignore blanks with FILTER & UNIQUE
8- عامل التصفية يستبعد القيم الفارغة
Filter exclude blank values
أولآ: ماهي دالة (FILTER function)
تقوم الدالة (FILTER) بتصفية نطاق من البيانات بناء على المعايير التي تحددها وإستخراج النتائج المتطابقة لهذه المعايير.
الغرض منها:
تصفية البيانات حسب معايير معينة
القيمة المرجعة (الناتج):
القيم التي تمت تصفيتها
بناء الجملة (Syntax):
=FILTER (array, include, [if_empty])
الحجج أو العناصر أو الوسائط (Arguments):
(array) - "هذا مطلوب"- نطاق أو مصدر من البيانات ليتم تصفيتها
(include) - "مطلوب أيضآ" - هذا يحدد الشرط (الشروط) التي يجب الإلتزام بها.
([if_empty]) - "إختياري" - هذا ما يتم استرجاعه في حالة عدم استيفاء أي بيانات للمعايير المحددة مسبقآ في الوسائط .. أو من الممكن إستخدام علامتي الإقتباس ("") وسوف يتم توضيحها مع الأمثلة.
----------------------------------------
ثانياََ : كيفية استخدام دالة (FILTER)
How to use the FILTER function
مثال 1:
لدينا جدول من المعلومات تحتوي على (أسماء , مجموعة من المستخدمات , عدد إستخدامها) .. في هذا المثال نريد أن نعرف كل مجموعة من يستخدمها من الأسماء. ثم وضعنا المجموعات في قائمة منسدلة ليسهل علينا معرفة الأسماء بسهولة. ولمعرفة كيفية عمل قائمة منسدلة (Data Validation) أذهب الى هذا الرابط إنشاء قائمة منسدلة فيه شرح كامل عن طريقة إنشاء قائمة.
كما هو موضح في الصورة أستخدمنا دالة (FILTER) لأنها الأنسب والأفضل لتعطينا النتائج التي نحتاجها. هنا كتبنا في أول خانة تحت (Name) هذه الصيغة أو المعادلة
=FILTER(A2:C12,B2:B12=F1,"")
فكانت النتيجة إظهار جدول يحتوي على كل مجموعة بالأسماء المستخدمة لها وعددها.
----------------------------------------
ثالثاََ : 3- دالة (FILTER) أفقياََ Horizontally
مثال 2 :
هنا أستخدمنا كما هو موضح في الصورة نفس المثال السابق لأننا نريد أن نبين أن دالة (FILTER) لا تعمل عامودياََ فقط (Vertically) بل أفقياََ أيضاََ (Horizontally).
-------------------------------------
رابعاََ : طريقة أستخدام (FILTER function) مع الأمثلة:
1- دالة (FILTER) مع (ISBLANK)
ترجع الدالة (ISBLANK) القيمة (TRUE) عندما تكون الخلية فارغة ، وتعيد (FALSE) عندما لا تكون الخلية فارغة.
الغرض منها:
قيمة منطقية (TRUE أو FALSE)
القيمة المرجعة (الناتج):
القيم التي تمت تصفيتها
بناء الجملة (Syntax)
=ISBLANK (value)
الحجج أو العناصر أو الوسائط (Arguments):
(value) - القيمة المطلوب التحقق منها.
مثال بسيط عن كيفية إستخدام دالة (ISBLANK):
مثال دالة (FILTER) مع (ISBLANK):
كما هو موضح في الفديو .. لدينا جدول من المعلومات تحتوي على (أسماء , مجموعة من المستخدمات , عدد إستخدامها) نفس المثال السابق , ولكن هنا نريد إظهار نتائج مختلفة مع إستخدام دالة (ISBLANK).
والنتائج هي على ثلاث مراحل مختلفة وهي:
المرحلة الأولى: نريد إظهار الأسماء التي تكون مجموعتها فارغة , وأستخدمنا هذه الصيغة أو المعادلة:
=FILTER(A2:C6,ISBLANK(C2:C6))
المرحلة الثانية: نريد إظهار الأسماء التي تكون عدد مجموعتها فارغة , وأستخدمنا هذه الصيغة أو المعادلة:
=FILTER(A2:C6,ISBLANK(B2:B6))
المرحلة الثالثة: نريد إظهار الأسماء التي تحتوي على مجموعات , وأستخدمنا هذه الصيغة أو المعادلة:
=FILTER(A2:C6,B2:B6=50)
ملاحظة:
سوف يظهر (0) في الخلايا الفارغة عند إستخدام الصيغ , فلو أردنا إخفاء الصفر نذهب الى (Excel preferences) ثم (view) ثم نزيل علامة الصح أو الإختيار عن القيم الصفرية (zero values). هذا لمستخدمي الماك (Mac)
لمستخدمي الويندوز (Windows)
نذهب الى (Excel options) ثم (advance) ثم ضمن خيارات (Display options for this worksheets) ثم نزيل علامة الصح أو الإختيار عن القيم الصفرية (zero values).
-------------------------------------
2- دالة (FILTER) مع (AND & OR Formula)
- Filter with multiple criteria (AND)
(multiple criteria) = معايير متعددة
مثال:
هنا لدينا نفس المثال السابق ولكن بمعايير متعددة يعني عدة شروط. أستخدمنا أولاََ دالة (FILTER) مع (AND) وهي تعني علامة الضرب (*) .. وذلك بضرب عامودين بعد اخذ البيانات كاملة , فتصبح الصيغة كما يلي:
=FILTER(A2:C12,(B2:B12=F2)*(C2:C12=F3),"No results")
"No results" معناها .. عندما تكون القيم غير ظاهرة في القائمة أو عندما نحدد قيم غير موجودة في جدول البيانات.
بعد ذلك كما هو موضح في الصورة أدناه قمنا بتحديد مجموعة معينة من القائمة وكتابة العدد الذي نريده فتظهر النتائج كما حددناها مسبقاََ.
- Filter with multiple criteria (OR)
مثال:
لدينا هنا نفس المثال السابق مع معايير متعددة ولكن أستخدمنا دالة (FILTER) مع (OR) وهي تعني علامة الضرب (+) .. وذلك بجمع عامودين بعد اخذ البيانات كاملة , فتصبح الصيغة كما يلي :
=FILTER(A2:C12,(B2:B12=J2)+(C2:C12=J3),"No results")
ما الفرق بين (AND) & (OR)
(AND) .. تظهر لنا ماهي المجموعات التي تأخذها نفس القيمة المحددة في القائمة فقط .
(OR) .. تظهر لنا ما هي المجموعات التي تأخذها نفس القيمة المحددة وأيضاََ جميع القيم التي تخضع للمجموعة المحددة في القائمة.
أنظر الى الصورة أدناه ليتضح لك كيفية إستخدام الصيغة أو المعادلة وما هي النتائج التي تظهرها لنا هذه الصيغة.
- Filter with multiple criteria (AND)&(OR)
مثال:
في هذا المثال أستخدمنا (AND) & (OR) مع بعض في نفس المعادلة وأيضاََ إنشاء قائمتين تحتوي على نفس المجموعات , فتصبح المعادلة كالتالي:
=FILTER(A2:C12,(C2:C12=N2)*(B2:B12=M2)+(B2:B12=M3),"No results")
وهنا كما موضح في الصورة سوف تظهر لنا هذه المعادلة نتائج مختلفة تعتمد على إختيارنا للمجموعات والقيمة المحددة لها.
------------------------------------
3- دالة (FILTER) مع (Sum, Average, Min, Max, Count)
مثال:
في هذا المثال كما هو موضح في الفديو أستخدمنا نفس جدول البيانات في الأمثلة السابقة. ولكن هنا نريد طريقة حساب القيم بإستخدام الدالة (FILTER) مع (Sum, Average, Min, Max, Count). فكانت نتيجة الصيغ كالتالي :
(Sum)
=SUM(FILTER($C$2:$C$12,$B$2:$B$12=$G$1,0))
(Average)
=AVERAGE(FILTER($C$2:$C$12,$B$2:$B$12=$G$1,0))
(Min)
=MIN(FILTER($C$2:$C$12,$B$2:$B$12=$G$1,0))
(Max)
=MAX(FILTER($C$2:$C$12,$B$2:$B$12=$G$1,0))
(Count)
=COUNT(FILTER($C$2:$C$12,$B$2:$B$12=$G$1,0))
إنتباه - لاحظنا أننا أستخدمنا الصفر للوسيطة (if_empty) في جميع الصيغ ، لذا فإن الصيغ سترجع (0) إذا لم يتم العثور على قيم تفي بالمعايير . سيؤدي تقديم أي نص مثل (No results) إلى الخطأ (#VALUE) ، وهذا ما لا نريده.
-----------------------------------
4- دالة (FILTER) مع (SORT function)
مثال:
هنا أستخدمنا دالة (FILTER) مع (SORT function) لإظهار نتائج مختلفة تعتمد على الشروط المحددة مسبقاَ. وفي مثالنا كانت الصيغة أو المعادلة التي أستخدمناها لإظهار القيم التي أكبر من (60) كالتالي:
=SORT(FILTER(A2:C12,C2:C12>=60,""))
------------------------------------
خامساََ: كيفية تصفية أعمدة معينة (متجاورة أو غير متجاورة).
How to FILTER only specific columns
Filter some adjacent columns (متجاورة)
مثال:
في هذا المثال نريد أن نعرف طريقة إظهار قيم عامودين متجاورين مع دالة (FILTER) فكانت الصيغة المستخدمة كالتالي كما هو موضح في الصورة أدناه:
=FILTER(A2:B12,A2:A12=F1,"No results")
Filter non-adjacent columns (غير متجاورة)
مثال:
أما هنا نريد أن نعرف طريقة إظهار قيم عامودين غير متجاورين مع دالة (FILTER) فكانت الصيغة المستخدمة كالتالي كما هو موضح في الصورة أدناه:
=FILTER(FILTER(A2:C12,A2:A12=J1,""),{1,0,1})
{1,0,1} .. معناها أن العامود الأول (1) يتم إظهار نتائجه , والعامود الثاني (0) لا يتم إظهاره , والثالث (1) يتم إظهاره.
------------------------------------
سادساََ : كيفية تصفية البيانات مع حساب القيم الفريدة
How to filter data with count unique values
مثال :
هنا في هذا المثال نريد معرفة عدد القيم الفريدة , وعدد اسماء القيم الفريدة في جدول البيانات , وعدد الأسماء الفريدة في كل مجموعة. لهذا أستخدمنا الدالة (FILTER) مع (UNIQUE) و (COUNTA). والطريقة كما هي موضحة في الفديوا ادناه .. والصيغ التي تم كتابتها بالترتيب بدأََ من
(TOTAL)
=COUNTA(UNIQUE(Table2[Name]))
(Note)
=COUNTA(UNIQUE(FILTER(Table2[Name],Table2[Group]=F1)))
(File)
=COUNTA(UNIQUE(FILTER(Table2[Name],Table2[Group]=E1)))
لمعرفة كيفية إستخدم الدالة (UNIQUE) إذهب الى شرح مفصل عن هذه الدالة الدالة UNIQUE
لمعرفة كيفية إستخدم الدالة (COUNTA) إذهب الى شرح مفصل عن هذه الدالة الدالة COUNTA
-----------------------------------
سابعاََ : كيفية تصفية البيانات وتجاهل الفراغات مع (FILTER & UNIQUE)
Unique values ignore blanks with FILTER & UNIQUE
مثال:
في هذا المثال لدينا بيانات في عامود تحتوي على خلايا فارغة ونريد تصفية هذه البيانات بدون ظهور الخلايا الفارغة , فأستخدمنا دالة (FILTER) مع الدالة (UNIQUE) كما هو موضح في الشاشة أدناه وكانت الصيغة كما يلي:
=UNIQUE(FILTER(A2:A15,A2:A15<>""))
-----------------------------
ثامناََ: عامل التصفية يستبعد القيم الفارغة
Filter exclude blank values
مثال أخر لإستبعاد أو تجاهل الخلايا الفارغة بطريقة أخرى:
في هذا المثال لدينا جدول بيانات يحتوي على خلايا فارغة ونريد تصفية هذه البيانات مع إستبعاد الخلايا الفاؤغة. لذلك أستخدمنا دالة (Filter) فكانت المعادلة أو الصيغة كالتالي:
=FILTER(Table224,(Table224[Name]<>"")*(Table224[Group]<>"")*(Table224[Amount]<>""))
(<>"") .. هذه معناها لا تساوي الخلايا الفارغة. بمعنى أننا نخبر الأكسل نريد تصفية البيانات بدون الخلايا الفارغة. والصورة أدناه توضح كيفية إستخدام دالة (Filter) لتصفية البيانات وإستبعاد الخلايا الفارغة.
FILTER is a new function available in Excel 365 only.
وهنا أتينا الى نهاية البرنامج التعليمي أشكر لكم صبركم ولمزيد من التوضيح أرجوا تحميل ملف التدريب العملي ومشاهدة الفديو. وأذا لديكم أي إستفسارات أرجو وضعها في التعليقات.
تعليقات
إرسال تعليق