دالتا SORT وSORTBY في الاكسل: الفرز بصيغة

تعيد =SORT(A2:C7,3,-1) الجدول A2:C7 مرتبًا حسب عموده الثالث، المبيعات، من الأكبر إلى الأصغر. يبقى الجدول الأصلي كما هو؛ وتمتد النسخة المرتبة من خلية الصيغة وتعيد فرز نفسها عندما يتغير رقم. غيّر C7 إلى 300 فينتقل Finn إلى الأعلى.

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

E2fx

=SORT(A2:C7,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		Dan	East	150
4	Cara	North	200		Ann	North	120
5	Dan	East	150		Eve	South	95
6	Eve	South	95		Ben	South	80
7	Finn	North	60		Finn	North	60

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

صيغة دالة SORT

=SORT(array, [sort_index], [sort_order], [by_col])

• array هو النطاق المراد فرزه وإرجاعه.
• sort_index هو رقم العمود داخل array الذي يُفرز حسبه. القيمة الافتراضية 1.
• sort_order قيمته 1 للترتيب التصاعدي (الافتراضي) أو -1 للتنازلي.
• by_col قيمته FALSE (الافتراضية) لفرز الصفوف، وTRUE لفرز الأعمدة من اليسار إلى اليمين.

تحتاج SORT وSORTBY إلى Excel 2021 أو Excel 2024 أو Microsoft 365؛ وتعرض الإصدارات الأقدم #NAME?. وتحتوي Google Sheets على الدالتين، لكن SORT فيها تأخذ رقم عمود وTRUE أو FALSE على شكل أزواج.

يضع الترتيب التصاعدي الأرقام أولًا، ثم النص من A إلى Z، ثم FALSE وTRUE. ويُقارن النص دون اعتبار لحالة الأحرف. وتعود الخلايا الفارغة في array بالقيمة 0 في النتيجة.

الترتيب الأبجدي في الاكسل

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

للإبقاء على الترتيب الأصلي والحصول على نسخة أبجدية تتحدث بنفسها، استخدم SORT دون وسائط أخرى:

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

C2fx

=SORT(A2:A8)

	A	B	C
1	Name		A to Z
2	Mia		Ann
3	Ben		Ben
4	Zoe		Cara
5	Ann		Dan
6	Leo		Leo
7	Cara		Mia
8	Dan		Zoe

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

اكتب Abe في A3 فيظهر أولًا. وتعطي =SORT(A2:A8,,-1) الترتيب من Z إلى A؛ وتتخطى الفاصلتان sort_index. ولترتيب قائمة أبجديًا دون تكرار، افرز نتيجة UNIQUE: =SORT(UNIQUE(A2:A8)) (راجع UNIQUE).

دالة SORTBY: الفرز حسب عمود لا تعيده

لا تستطيع SORT الفرز إلا حسب عمود من النطاق الذي تعيده. أما SORTBY فتأخذ مفتاح الفرز كنطاق مستقل، فيمكنك إرجاع الأسماء وحدها مرتبة حسب المبيعات:

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

E2fx

=SORTBY(A2:A7,C2:C7,-1)

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

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

النتيجة Cara وDan وAnn وEve وBen وFinn. والصيغة هي =SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...). ويجب أن يكون لكل by_array عدد صفوف array نفسه، وإلا تعيد SORTBY الخطأ #VALUE!.

لأفضل ثلاثة، خذ الصفوف الأولى من النتيجة: تعيد =TAKE(SORTBY(A2:A7,C2:C7,-1),3) القيم Cara وDan وAnn. وتحتاج TAKE إلى Microsoft 365 أو Excel 2024؛ وفي Excel 2021 استخدم =INDEX(SORTBY(A2:A7,C2:C7,-1),SEQUENCE(3)).

الفرز حسب عدة أعمدة

أعطِ SORTBY زوجًا واحدًا من المفتاح والترتيب لكل مستوى. تفرز هذه الصيغة حسب المنطقة من A إلى Z، ثم داخل كل منطقة حسب المبيعات من الأعلى إلى الأدنى:

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

E2fx

=SORTBY(A2:C7,B2:B7,1,C2:C7,-1)

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

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

تأتي East أولًا، ثم صفوف North الثلاثة بالترتيب Cara (200) وAnn (120) وFinn (60)، ثم South. وتستطيع SORT فعل الشيء نفسه بمصفوفات من أرقام الأعمدة والترتيبات، وإن كانت SORTBY أسهل قراءة:

=SORT(A2:C7,{2,3},{1,-1})

الفرز بترتيب مخصص

الترتيب الأبجدي ليس دائمًا ما تريده. لفرز المناطق بالترتيب North ثم East ثم South، حوّل كل منطقة إلى موضعها في تلك القائمة بالدالة MATCH وافرز حسب المواضع:

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

E2fx

=SORTBY(A2:C7,MATCH(B2:B7,{"North","East","South"},0))

	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		Dan	East	150
6	Eve	South	95		Ben	South	80
7	Finn	North	60		Eve	South	95

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

تعيد MATCH القيمة 1 للمنطقة North، و2 للمنطقة East، و3 للمنطقة South، وتفرز SORTBY حسب هذه الأرقام. وتحافظ الصفوف ذات المفتاح نفسه على ترتيبها الأصلي، فتبقى صفوف North بالترتيب Ann وCara وFinn. والحيلة نفسها تفرز الأشهر أو أيام الأسبوع بأسمائها: اكتبها بترتيب التقويم داخل MATCH. والمنطقة غير الموجودة في القائمة تحصل على #N/A من MATCH؛ استخدم IFERROR(MATCH(B2:B7,{"North","East","South"},0),99) كمفتاح لوضع هذه الصفوف في النهاية.

تمرين: الأسماء مرتبة حسب المبيعات

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

E2fx

	A	B	C	D	E
1	Name	Region	Sales		By 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، أعد الأسماء فقط، مرتبة حسب المبيعات من الأعلى إلى الأدنى.

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

دالة SORT أم زر فرز

كلاهما يفرز، لكنهما يؤديان مهمتين مختلفتين:

استخدم الزر لترتيب جدول مرة واحدة. واستخدم الدالة لتقرير أو لوحة معلومات يجب أن تبقى مرتبة بينما تتغير البيانات. وإذا لم تكن الخلايا تحت الصيغة أو على يمينها فارغة، تعرض الدالة #SPILL! بدلًا من القائمة المرتبة؛ امسحها فتظهر (راجع خطأ #SPILL!).