מיון ללא מיון ב"אקסל" - פרק ב'
כזכור לכם, בפרק הקודם הדגמנו כיצד ניתן למַיֵּן תחום מספרים ב"אקסל"
כפי שאפשר לראות בתמונה 1, בתאים: A2:A19 מופיעה רשימת מספרים לא ממוינת.
לאחר הקשת הנוסחא:
=LARGE($A$2:$A$19,ROW()-1)
בתא: G2 וגרירתה כלפי מטה באמצעות "ידית המלוי" - עד לתא G19, נקבל בעמודה G (תאים: G2:G19) את הנתונים הממוינים מהגדול לקטן (מיון יורד)
תמונה 1: עמודה G מציגה במיון יורד את המספרים הלא-ממוינים בעמודה A
ואם נקיש את הנוסחא:
=SMALL($A$2:$A$19,ROW()-1)
בתא: G2 ונגרר עד G19 - נקבל את הנתונים ממוינים מהקטן לגדול (מיון עולה)
תמונה 2: הפונקציהSMALL בעמודה G מציגה במיון עולה את המספרים בעמודה A
כעת, יש לי מצב מעט יותר מורכב.
יש לי רשימת סוכנים ומכירות.
לכל סוכן, סכום המכירות שלו.
תמונה 3: טבלת סוכנים ומכירות
אם נרצה למַיֵּן את הטבלה לפי סכומי המכירות (מהגדול לקטן- כדי לדעת מי הסוכן שמכר הכי הרבה) - כתוצאה מהמיון, סכומי המכירות ישנו מקום בעמודה הממוינת. אם למשל, הסכום הגבוה ביותר (1000.00) היה בתא הרביעי של הטבלה (B5 - מכיון שהנתונים מתחילים בתא B2)- לאחר המיון, הוא יהיה התא הראשון בטבלה החדשה.
נצטרך כעת גם לדאוג שלאחר המיון, שם הסוכן יהיה עדיין צמוד לסכום המכירה שלו. אחרת סכום המכירה לא ישקף את הסוכן המקורי.
הערה חשובה: הפתרון המוצע נכון רק כאשר רשימת סכומי המכירות היא יחודית, כלומר: אם כל סכום מופיע רק פעם אחת...
הפתרון: בשלשה שלבים
שלב א: נגדיר את תחום המספרים (B2:B19) בשם: Sales. נסמן את התחום וב- Name Box (תיבת השם) נכתֹב: Sales. כך יקבל התחום את השם: Sales.
השִמּוּש בשם יהפך את כל הנוסחאות לפשוטות יותר ולקריאות יותר...
תמונה 4: הגדרת השם Sales לתחום הסכומים: B2:B19
שלב ב:
שלב זה זהה לפתרון בפרק א: אנו ממַיְנִים את המספרים בעמודה חדשה (נניח, עמודה I)
נקליד (בתא: I2) את הנוסחא
=LARGE(Sales, ROW()-1)
ונגרֹר כלפי מטה:
והתוצאה:
תמונה 5: שלב ב - מיון סכומי המכירות (B2:B19) בעמודה I (I2:I19)
שלב ג:
נקליד את הנוסחא הבאה בתא H2:
=INDIRECT("A"&1+MATCH($I,Sales,0))
ונגרר כלפי מטה (עד H19):
et voilà!
תמונה 6: עמודות H,I מציגות את הטבלה הממוינת לפי סכום מכירות לסוכן (בסדר יורד)
אנו רואים שבאמת בטבלה החדשה (H2:I19) נשמרו הקשרים המקוריים בין הסוכן ובין סכום מכירה שלו.
למשל: הסוכן James (A5, H2) מכר ב- 1000.00 (B5, I2), הסוכן Ben(A12, H7) מכר ב- 540.00 (B12, I7) וכו'.
הסבר על הנוסחא:
=INDIRECT("A"&1+MATCH($I,Sales,0))
הנוסחא מורכבת משתי פונקציות:
1) MATCH
2) INDIRECT
1) הפונקציה: MATCH
=INDIRECT("A"&1+ MATCH($I2,Sales,0))
א. כללי:
הפונקציה MATCH מחזירה את המיקוםשל ערך מסוים בתחום ערכים/טבלה (בנגוד ל- VLOOKUP, למשל, שמחזירה את הערך עצמו)
ב. ארגומנטים:
1. מה לחפש (lookup_value)- אנו מחפשים את הערך ב: $I2 (כלומר, את הערך: 1000.00)
2. היכן לחפש (lookup_array)- המערך/טבלה שבה אמור ערך זה להִמָּצֵא (בדוגמא שלנו: Sales)
3. כיצד לחפש (match_type) - 3 אפשרויות לחפוש
א. 0 - יש למצוא את הערך הראשון הזהה בדיוק לערך החפוש (כלומר, מארגומנט מס. 1)
ב. 1 - יש למצוא את הערך הגבוה ביותר שאיננו גדול מערך החפוש (כלומר, קטן או שווה לערך החפוש)
ג. 1- - יש למצוא הערך הקטן ביותר שאינו קטן מערך החפוש (כלומר, גדול או שווה לערך החפוש)
ג. תוצאה: בדוגמא שלנו, כאשר הפרמטרים הם:
1. ערך החפוש = $I2 (העמודה - מקובעת והשורה - יחסית)
2. הטבלה לחפוש = Sales
3. כיצד לחפש = 0 [מצא ערך זהה בדיוק לערך החפוש]
התוצאה היא: 4 - הערך 1000.00 נמצא בשורה הרביעית של הטבלה (כלומר, ב-B5)
2) הפונקציה: INDIRECT
א. כללי:
הפונקציה INDIRECT מחזירה את הערךשנמצא בכתובת תא.
למשל, אם תא A1 ערכו: 7, אזי הפונקציה:
=INDIRECT(“A1”)
תחזיר את הערך 7
ב. ארגומנטים:
1. כתובת התא (ref_text)- בדוגמא שלנו, כתובת התא היא תוצאה של שרשור המחרוזת
“A”&1עם תוצאת נוסחת ה- MATCH:
“A”&1+MATCH($I2,Sales,0) -> “A”&1+4 -> “A5”
כלומר, תוצאת הבטוי כולו תהיה “A5”
2. שיטת הכתובות בגליון [a1] - האם שיטת הכתובות היא A1 (השיטה הרגילה) או שיטת R1C1. אנו משתמשים בשיטת A1 ולכן פרמטר זה מיותר.
ג. תוצאה: בדוגמא שלנו, כאשר הפרמטרים הם:
1. כתובת התא =A5
2. שיטת הכתובות = A1
התוצאה היא: הערך שנמצא בתא A5,
=INDIRECT(“A”&1+4) -> =INDIRECT(“A5”) -> James
╬╬╬╬╬╬╬╬╬╬╬╬╬╬╬╬╬╬
