מיין אוטומטית נתונים בסדר אלפביתי באמצעות נוסחה

מיון הנתונים המובנה ב- Excel מדהים, אך אינו דינאמי. אם אתה ממיין נתונים ולאחר מכן מוסיף להם נתונים, יהיה עליך למיין אותם שוב.

מיין את הנתונים בסדר אלפביתי

בפוסט זה אראה לך דרכים שונות למיון נתונים בסדר אלפביתי באמצעות נוסחאות. המשמעות היא שאתה יכול להוסיף נתונים, וזה ימיין אותם עבורך באופן אוטומטי.

כאשר הנתונים כולם טקסט ללא כפילויות

נניח שיש לך נתונים כפי שמוצג להלן:

בדוגמה זו, כל הנתונים בפורמט טקסט (ללא מספרים, ריקים או כפילויות). כדי למיין זאת, אשתמש בעמוד עזר. בעמודה שלצד הנתונים, השתמש בנוסחת COUNTIF הבאה:

= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)

נוסחה זו משווה ערך טקסט עם כל ערכי הטקסט האחרים ומחזירה את הדירוג היחסי שלה. לדוגמה, בתא B2, הוא מחזיר 8, שכן ישנם 8 ערכי טקסט שהם נמוכים או שווים לטקסט 'ארה"ב' (סדר אלפביתי).

כעת למיון הערכים, השתמש בשילוב הבא של פונקציות INDEX, MATCH ו- ROWS:

= INDEX ($ A $ 2: $ A $ 9, MATCH (ROWS ($ B $ 2: B2), $ B $ 2: $ B $ 9,0))

נוסחה זו פשוט מחלצת את השמות בסדר האלפביתי. בתא הראשון (C2) הוא מחפש את שם המדינה בעל המספר הנמוך ביותר (לאוסטרליה יש 1). בתא השני, הוא מחזיר את קנדה (שיש לה את המספר 2) וכן הלאה …

אלרגי לעמודי עזר ??

להלן נוסחה שתעשה את אותו הדבר ללא עמודת העזר.

= INDEX ($ A $ 2: $ A $ 9, MATCH (ROWS ($ A $ 2: A2), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), 0))

זו נוסחת מערך, אז השתמש Control + Shift + Enter במקום Enter.

אני אשאיר לך לבטל את הקוד.

נסה זאת בעצמך … הורד קובץ דוגמה

נוסחה זו פועלת היטב אם יש לך טקסט או ערכים אלפאנומריים.

אבל זה נכשל כישלון חרוץ אם:

• יש לך כפילויות בנתונים (נסה לשים את ארה"ב פעמיים).
• יש נתונים ריקים בנתונים.
• יש לך שילוב של מספרים וטקסט (נסה להכניס 123 לאחד התאים).

כאשר הנתונים הם שילוב של מספרים, טקסט, כפילויות וחסר

עכשיו זה קצת מסובך. אני אשתמש ב -4 עמודות עוזר כדי להראות לך כיצד זה עובד (ולאחר מכן אתן לך נוסחה ענקית שתעשה זאת ללא עמודות העוזר). נניח שיש לך נתונים כפי שמוצג להלן:

אתה יכול לראות שיש ערכים כפולים, ריקים ומספרים. אז אשתמש בעמודות עזר כדי לטפל בכל אחת מהנושאים הללו.

טור עוזר 1

הזן את נוסחת COUNTIF הבאה בעמודה מסייעת 1

= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)

נוסחה זו עושה את הפעולות הבאות:

• הוא מחזיר 0 עבור החסר.
• במקרה של כפילויות, הוא מחזיר את אותו מספר.
• טקסט ומספרים מעובדים במקביל ונוסחה זו מחזירה את אותו מספר עבור טקסט ומספר (למשל 123 והודו מקבלים 1).

טור עוזר 2

הזן את פונקציית ה- IS הבאה בעמודה מס '2:

=-ISNUMBER (A2)

טור עוזר 3

הזן את הנוסחה הבאה בעמודה מס '3:

=-ISBLANK (A2)

טור עוזר 4

הזן את הנוסחה הבאה בעמודה מסייעת 4

= IF (ISNUMBER (A2), B2, IF (ISBLANK (A2), B2, B2+$ C $ 10))+$ D $ 10

הרעיון של נוסחה זו הוא הפרדת החסר, המספרים וערכי הטקסט.

• אם התא ריק, הוא מחזיר את הערך בתא B2 (שתמיד יהיה 0) ומוסיף את הערך בתא D10. בקצרה, הוא יחזיר את המספר הכולל של תאים ריקים בנתונים
• אם התא הוא ערך מספרי, הוא יחזיר את הדירוג ההשוואתי ויוסיף את מספר החסר הכולל. לדוגמה, עבור 123 הוא מחזיר 2 (1 הוא הדירוג של 123 בנתונים, ויש תא ריק אחד)
• אם זהו טקסט, הוא מחזיר את הדירוג ההשוואתי ומוסיף את המספר הכולל של ערכים ומספרים מספריים. לדוגמה, בהודו היא מוסיפה את הדירוג ההשוואתי של הטקסט בטקסט (שהוא 1) ומוסיפה את מספר התאים הריקים ואת מספר הערכים המספריים.

התוצאה הסופית - נתונים ממוינים

כעת נשתמש בעמודות עזר אלה כדי לקבל את הרשימה הממוינת. להלן הנוסחה:

= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (SMALL ($ E $ 2: $ E $ 9, ROWS ($ F $ 2: F2)+$ D $ 10), $ E $ 2: $ E $ 9,0)) , "")

שיטת המיון הזו הופכת כעת לחסינת טיפש. הראיתי לך את השיטה עבור 8 פריטים, אך תוכל להרחיב אותה לכמה פריטים שתרצה.

נסה זאת בעצמך … הורד קובץ דוגמה

נוסחה אחת למיון הכל (ללא עמודות עוזר)

אם אתה יכול להתמודד עם נוסחאות קיצוניות, הנה נוסחת הכל-על-אחת שתמיין את הנתונים בסדר אלפביתי (ללא עמודת עזר).

להלן הנוסחה:

= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (SMALL (NOT ($ A $ 2: $ A $ 9 = "")*IF (ISNUMBER ($ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9)+SUM (-ISNUMBER ($ A $ 2: $ A $ 9)))), ROWS ($ A $ 2: A2)+SUM (-ISBLANK ($ A $ 2: $ A $ 9)))), NOT ($ A $ 2: $ A $ 9 = "")*IF (ISNUMBER ($ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9)+SUM (-ISNUMBER ($ A $ 2: $ A $ 9))), 0)), "")

הזן נוסחה זו בתא וגרור אותה כלפי מטה כדי לקבל את הרשימה הממוינת. כמו כן, מכיוון שזו נוסחת מערך, השתמש Control + Shift + Enter במקום Enter.

לנוסחה זו יש תועלת בעולם האמיתי. מה אתה חושב? אשמח ללמוד ממך. השאר את עקבותיך בקטע ההערות!