לְהִצטַיֵן

מיין טקסט ומספרים בעזרת נוסחה

Sort Text Numbers With Formula

נוסחת Excel: מיין טקסט ומספרים בעזרת נוסחהנוסחה כללית | _+_ | סיכום

כדי למיין נתונים באופן דינמי עם מספרים וטקסט בסדר אלפביתי אתה יכול להשתמש בנוסחה ליצירת דירוג מספרי בעמודת עזר, ולאחר מכן השתמש ב- INDEX וב- MATCH כדי להציג ערכים המבוססים על דירוג. בדוגמה המוצגת הנוסחה ב- C5 היא:



להצטיין בסבב עד 1000 הקרובים
= COUNTIF (data,'<='&A1)+( COUNT (data)* ISTEXT (A1))

היכן 'הנתונים' הם טווח בשם B5: B13.

הֶסבֵּר

נוסחה זו מייצרת תחילה ערך דירוג באמצעות ביטוי המבוסס על COUNTIF:





 
= COUNTIF (data,'<='&B5)+( COUNT (data)* ISTEXT (B5))

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

 
= COUNTIF (data,'<='&B5)

כאן אנו משתמשים בפונקציה COUNT כדי לקבל ספירה של ערכים מספריים בנתונים, ולאחר מכן להכפיל את התוצאה בתוצאה הלוגית של ISTEXT, אשר בודקת אם הערך הוא טקסט ומחזיר TRUE או FALSE. זה למעשה מבטל את התוצאה COUNT כאשר אנו עובדים עם מספר בשורה הנוכחית.



טיפול בכפילויות

אם הנתונים מכילים כפילויות, ניתן לשנות את הנוסחה כפי שמוצג להלן כדי להקצות דרגה רציפה לערכים המופיעים יותר מפעם אחת:

 
+( COUNT (data)* ISTEXT (B7))

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

הצג ערכים ממוינים

כדי לאחזר ולהציג ערכים ממוינים בסדר אלפביתי באמצעות ערך הדירוג המחושב, E5 מכיל את הדברים הבאים נוסחת INDEX ו- MATCH :

 
= COUNTIF (data,'<'&B5)+( COUNT (data)* ISTEXT (B5))+ COUNTIF ($B:B5,B5)

היכן 'הנתונים' הם טווח בשם B5: B13, ו'דרגה 'הוא הטווח הנקרא C5: C13.

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

התמודדות עם החסר

תאים ריקים ייצרו דרגה של אפס. בהנחה שאתה רוצה להתעלם מתאים ריקים, זה עובד מצוין כי הנוסחה INDEX ו- MATCH לעיל מתחילה ב -1. עם זאת, תראה #שגיאות N/A בסוף הערכים הממוינים, אחת לכל תא ריק. דרך קלה להתמודד עם זה היא לעטוף את נוסחת INDEX ו- MATCH ב- IFERROR כך:

 
= INDEX (data, MATCH ( ROWS ($E:E5),rank,0))
הסופר דייב בראנס


^