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!).