دالة FILTER في الاكسل: عدة شروط مع AND وOR

تعيد =FILTER(A2:C7,B2:B7="North") كل صف من A2:C7 تكون منطقته في العمود B هي North. تكتبها في خلية واحدة فتمتد الصفوف المطابقة إلى الخلايا التي تحتها وعلى يمينها. غيّر منطقة في العمود B إلى North، أو North إلى South، فتتحدث القائمة.

الصيغإعادة الضبط

E2fx

=FILTER(A2:C7,B2:B7="North")

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Finn	North	60
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

E2 وحدها تحتوي على صيغة. أما الخلايا الممتلئة الأخرى في E:G فهي نتيجتها الممتدة: انقر F3 فترى أنها تابعة للصيغة في E2. وإذا كُتب شيء في تلك المنطقة، تعرض FILTER الخطأ #SPILL! بدلًا من الصفوف (راجع أخطاء #SPILL!).

صيغة دالة FILTER

=FILTER(array, include, [if_empty])

• array هو ما تريد إرجاعه: عمود واحد، أو عدة أعمدة، أو الجدول كله.
• include شرط فيه قيمة TRUE أو FALSE واحدة لكل صف من array، مثل B2:B7="North". ويجب أن يكون عدد صفوفه مساويًا تمامًا لعدد صفوف array. (ولتصفية الأعمدة بدلًا من ذلك، أعطه قيمة واحدة لكل عمود.)
• if_empty هو ما يُعرض عندما لا يطابق أي صف. وبدونه تكون النتيجة الفارغة الخطأ #CALC!.

تحتاج FILTER إلى Excel 2021 أو Excel 2024 أو Microsoft 365. وفي Excel 2019 وما قبله تعرض #NAME?، وتكون التصفية هناك بزر تصفية في علامة التبويب بيانات. وتحتوي Google Sheets على FILTER أيضًا، ويمكن فيها كذلك إعطاء كل شرط كوسيط مستقل.

مقارنات النص لا تميّز حالة الأحرف: يطابق B2:B7="north" القيمة North. وتُبقي FILTER الصفوف بترتيبها الأصلي؛ وفرز النتيجة خطوة منفصلة، موضحة في الأسفل.

التصفية حسب قيمة خلية

كتابة "North" داخل الصيغة تعني تعديل الصيغة في كل مرة. ضع القيمة في خلية وقارن بالخلية بدلًا من ذلك. اختر منطقة أخرى في F1 فتتبعها النتيجة:

الصيغإعادة الضبط

E3fx

=FILTER(A2:C7,B2:B7=F1,"No sales")

	A	B	C	D	E	F	G
1	Name	Region	Sales		Region	North▾
2	Ann	North	120
3	Ben	South	80		Ann	North	120
4	Cara	North	200		Cara	North	200
5	Dan	East	150		Finn	North	60
6	Eve	South	95
7	Finn	North	60

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

لا توجد صفوف للمنطقة West، لذا يعرض اختيارها نص if_empty، أي No sales.

والأرقام تعمل بالطريقة نفسها. تُبقي C2:C7>=F1 مع 100 في F1 كل صف مبيعاته 100 على الأقل، وتجعل C2:C7>F1 المقارنة "أكبر من" فقط.

دالة FILTER بعدة شروط (AND)

للإبقاء على صف فقط عندما يتحقق شرطان معًا، اضربهما. تعيد هذه الصيغة صفوف North التي تتجاوز مبيعاتها 100:

الصيغإعادة الضبط

E2fx

=FILTER(A2:C7,(B2:B7="North")*(C2:C7>100))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

ينجح Ann (120) وCara (200). أما Finn فمنطقته North لكن مبيعاته 60 لا تتجاوز 100، فيُستبعد.

لماذا الضرب: كل شرط عمود من TRUE وFALSE، وفي الحساب تُعدّ TRUE واحدًا وFALSE صفرًا. فلا يحصل الصف على 1 إلا عندما يكون كل عامل 1، لذا تعمل * عمل AND. ويحتاج كل شرط إلى قوسين خاصين به، ويمكنك ربط أي عدد من الشروط: (B2:B7="North")*(C2:C7>100)*(C2:C7<500).

لا تعمل AND() هنا. تختصر AND(B2:B7="North",C2:C7>100) النطاق كله إلى قيمة TRUE أو FALSE واحدة بدلًا من قيمة لكل صف، فتحصل FILTER على شكل خاطئ.

دالة FILTER مع OR

اجمع الشروط للإبقاء على صف عندما يتحقق أحدها على الأقل. تعيد هذه الصيغة صفوف North وEast:

الصيغإعادة الضبط

E2fx

=FILTER(A2:C7,(B2:B7="North")+(B2:B7="East"))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Dan	East	150
5	Dan	East	150		Finn	North	60
6	Eve	South	95
7	Finn	North	60

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

الصف الذي يحقق الشرطين يكون مجموعه 2، وتُبقي FILTER أي صف نتيجته ليست 0، لذا يعمل الجمع عمل OR. ويمكنك المزج بين الاثنين: تعني ((B2:B7="North")+(B2:B7="East"))*(C2:C7>100) (North أو East) مع مبيعات تتجاوز 100. وهنا تعيد Ann وCara وDan.

FILTER تعيد #CALC! عندما لا يطابق شيء

عندما لا ينجح أي صف، لا يكون لدى FILTER ما تعيده. وبدون وسيط ثالث تكون النتيجة الخطأ #CALC!؛ ومعه تحصل على نصك الخاص:

الصيغإعادة الضبط

E2fx

=FILTER(A2:A7,B2:B7="West")

	A	B	C	D	E	F
1	Name	Region	Sales		No if_empty	With if_empty
2	Ann	North	120		#CALC!	No match
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

#CALC! لا توجد نتيجة للحساب، مثل FILTER لا يجد شيئًا.

تعرض E2 الخطأ #CALC! وتعرض F2 النص No match. غيّر B3 من South إلى West فتعيد الصيغتان Ben. ولعدم عرض أي شيء، استخدم نصًا فارغًا: =FILTER(A2:A7,B2:B7="West","").

توضح هذه الورقة أيضًا تصفية عمود واحد: array هو A2:A7، فلا تعود إلا الأسماء. وللحصول على بعض أعمدة الجدول لا كلها، ضع النتيجة داخل CHOOSECOLS: تعيد =CHOOSECOLS(FILTER(A2:C7,B2:B7="North"),1,3) الأسماء والمبيعات دون المنطقة. وتحتاج CHOOSECOLS إلى Microsoft 365 أو Excel 2024.

فرز نتيجة FILTER

تعيد FILTER الصفوف بالترتيب الذي تظهر به في الجدول. ضعها داخل SORT لترتيب النتيجة: هنا صفوف North مرتبة حسب المبيعات، الأكبر أولًا. الرقم 3 هو عمود النتيجة الذي يُفرز حسبه، و-1 يعني تنازليًا.

الصيغإعادة الضبط

E2fx

=SORT(FILTER(A2:C7,B2:B7="North"),3,-1)

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Cara	North	200
3	Ben	South	80		Ann	North	120
4	Cara	North	200		Finn	North	60
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

تأتي Cara (200) أولًا، ثم Ann (120) وFinn (60). ولإرجاع الصفوف العليا فقط، ضعها مرة أخرى داخل TAKE: تُبقي =TAKE(SORT(FILTER(A2:C7,B2:B7="North"),3,-1),2) أول صفين (وتحتاج TAKE إلى Microsoft 365 أو Excel 2024). وتشرح SORT وSORTBY خيارات الفرز الأخرى.

تصفية الصفوف التي تحتوي على نص

لا تدعم FILTER أحرف البدل، لذا يبحث B2:B7="*th*" عن النص الحرفي *th*. وللإبقاء على الصفوف التي يحتوي اسمها على نص معين، اختبر كل خلية بالدالة SEARCH، التي تعيد موضعًا عند العثور على النص وخطأً عند عدمه، وضعها داخل ISNUMBER:

الصيغإعادة الضبط

E2fx

=FILTER(A2:C7,ISNUMBER(SEARCH("an",A2:A7)))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Dan	East	150
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

تعيد هذه الصيغة Ann وDan: تتجاهل SEARCH حالة الأحرف، لذا تطابق "an" أيضًا الجزء An من Ann. استخدم FIND بدلًا من SEARCH لمطابقة تميّز حالة الأحرف.

تمرين: FILTER بشرطين

الصيغإعادة الضبط

E2fx

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

دورك: في E2، أعد صفوف (الأعمدة الثلاثة كلها) مندوبي منطقة South الذين تتجاوز مبيعاتهم 85.

تحققعرض الإجابة

تمرين: FILTER حسب خلية، مع قيمة بديلة

الصيغإعادة الضبط

F3fx

	A	B	C	D	E	F
1	Name	Region	Sales		Region	North
2	Ann	North	120
3	Ben	South	80		Names
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

دورك: في F3، اعرض أسماء (العمود A فقط) المندوبين في المنطقة المكتوبة في F1. وإن لم يوجد أحد، اعرض None.

تحققعرض الإجابة

أخطاء شائعة في FILTER

• نطاقات بارتفاعات مختلفة. تفحص =FILTER(A2:C7,B2:B6="North") خمسة صفوف لجدول من ستة صفوف، فيعيد الاكسل #VALUE!. اجعل include يبدأ وينتهي عند صفوف array نفسها.
• أصفار حيث المصدر فارغ. تعيد FILTER القيمة 0 للخلية الفارغة في array. استبدل الفراغات بنص فارغ قبل التصفية: =FILTER(IF(A2:C7="","",A2:C7),B2:B7="North").
• أعمدة كاملة. تعمل =FILTER(A:C,B:B="North")، لكن إذا كانت الصيغة نفسها في الأعمدة من A إلى C فإنها تشير إلى نفسها. ضع النتيجة بجانب الجدول، أو استخدم نطاقًا ثابتًا مثل A2:C1000.
• علامات اقتباس حول الأرقام. تقارن C2:C7>"100" أرقامًا بنص فلا تُبقي شيئًا. اكتب C2:C7>100.
• توقع سلوك زر التصفية. تنسخ FILTER الصفوف المطابقة إلى مكان جديد وتترك الجدول كما هو. ولإخفاء صفوف في الجدول نفسه، استخدم بيانات > تصفية.