חבר התקשר אלי ושאל אם יש דרך לקבל מספרים סידוריים בצורה כזו שהם לא יהיו כפילות במספרים סידוריים ב- Excel.
משהו כפי שמוצג להלן:
הוא רצה שהמספר הסידורי של הודו יהיה 1 בכל מקום שהוא מתרחש. באופן דומה, ארה"ב היא המדינה השנייה ותמיד צריכה להיות לה 2 כמספר הסידורי שלה.
זה גרם לי לחשוב.
והנה שתי הדרכים שבהן יכולתי להמציא כדי למנוע כפילות במספרים סידוריים ב- Excel.
שיטה מס '1 - שימוש בפונקציית VLOOKUP
הדרך הראשונה היא להשתמש בפונקציית VLOOKUP האהובה שלנו.
לשם כך עלינו קודם כל לקבל רשימה ייחודית של מדינות. להלן השלבים לעשות זאת:
- צור עותק של רשימת המדינות (העתק הדבק אותו באותו גליון עבודה או בגליון עבודה אחר).
- בחר את הנתונים שהועתקו ועבור לנתונים -> הסר כפילויות. זה יפתח את תיבת הדו -שיח להסרה כפולה.
- ודא שהאפשרות - לנתונים שלי יש כותרות מסומנת (למקרה שלנתונים שלך יש את הכותרת. אחרת בטל את הסימון).
- בחר את העמודה ממנה ברצונך להסיר את הכפילויות.
- לחץ על אישור.
- זהו זה. תהיה לך רשימה של שמות מדינה ייחודיים.
ראה גם: המדריך האולטימטיבי לאיתור והסרה של כפילויות ב- Excel.
כעת הקצה את המספרים הסידוריים לכל מדינה. ודא שמספרים אלה הוזנו מימין לרשימת המדינות הייחודיות, מכיוון ש- VLOOKUP אינו יכול להביא נתונים משמאל לערך החיפוש.
בתא, שבו אתה רוצה את המספרים הסידוריים (B3: B15), השתמש בנוסחת VLOOKUP שלהלן:
= VLOOKUP (C3, $ F $ 3: $ G $ 8,2,0)
נוסחת VLOOKUP זו לוקחת את שם המדינה כערך החיפוש, בודקת אותו בנתונים ב- F3: G8 ומחזירה את המספר הסידורי שלה.
שיטה מס '2 - נוסחה דינאמית
למרות ששיטת VLOOKUP היא דרך מצוינת לעשות זאת, היא אינה דינאמית.
אז אם אני מוסיף מדינה חדשה או משנה מדינה קיימת, שיטה זו לא הייתה עובדת ותצטרך לחזור על כל התהליך של שיטה מס '1 שוב.
להלן נוסחה שהופכת אותה לדינאמית:
= IF (COUNTIF ($ C $ 3: $ C4, $ C4) = 1, MAX ($ B $ 3: $ B3)+1, INDEX ($ B $ 3: $ C $ 18, MATCH ($ C4, $ C $ 3: $ C4,0), 1))
כדי להשתמש בנוסחה זו, עליך להזין ידנית 1 בתא הראשון, ואת הנוסחה שלעיל בכל שאר התאים הנותרים.
איך זה עובד:
הוא משתמש בפונקציית IF שבודקת את מספר הפעמים שמדינה אירעה לפני השורה הזו. אם שם המדינה מופיע בפעם הראשונה, הספירה היא 1 והתנאי הוא TRUE, ואם שם המדינה התרחש גם קודם לכן, הספירה היא יותר מ -1 והתנאי הוא FALSE.
- כשהמצב נכון:
= MAX ($ B $ 3: $ B3) +1
אם הערך הוא TRUE, כלומר שם המדינה מופיע בפעם הראשונה, הוא מזהה את הערך המרבי של המספר הסידורי עד אז ומוסיף לו 1 כדי לתת את ערך המספר הסידורי הבא.
- כאשר ערך אם שקר:
= INDEX ($ B $ 3: $ C $ 18, MATCH ($ C4, $ C $ 3: $ C4,0), 1)
אם המדינה כבר התרחשה קודם לכן, נוסחה זו עוברת לתא שבו היא מופיעה תחילה ומחזירה את המספר הסידורי של המופע הראשון של אותה מדינה.
הורד את קובץ הדוגמה
אולי תאהב גם את מדריכי האקסל הבאים:
- כיצד להשתמש ב- Flash מלא ב- Excel.
- מיין אוטומטית נתונים בסדר אלפביתי באמצעות נוסחה.
- כיצד למלא במהירות מספרים בתאים מבלי לגרור.
- כיצד להשתמש בידית מילוי ב- Excel.
