ישנם מצבים שבהם עליך להוסיף אפסים מובילים למערך נתונים ב- Excel. זה יכול להיות המקרה אם אתה שומר רשומות ב- Excel, כגון מזהי עובדים או מזהי עסקה.
לדוגמה, ייתכן שתרצה לקבל מבט עקבי במערך הנתונים שלך כפי שמוצג להלן:
במדריך זה תלמד דרכים שונות להוספת אפסים מובילים ב- Excel:
- המרת הפורמט לטקסט
- שימוש בעיצוב מספר מותאם אישית
- שימוש בפונקציית טקסט
- שימוש בפונקציות REPT/LEN
- שימוש ב- VBA
לכל אחת מהשיטות הללו יש כמה יתרונות וחסרונות (מכוסה בכל סעיף).
בואו נראה כיצד כל אחד מאלה פועל.
הוסף אפסים מובילים על ידי המרת הפורמט לטקסט
מתי להשתמש: כאשר יש לך מערך נתונים מספריים קטן, ואתה מתכנן לבצע עריכה זו באופן ידני.
נניח שיש לך מזהי עובדים של מחלקת השיווק כפי שמוצג להלן ואתה רוצה לגרום לזהות להיראות עקביות על ידי הוספת אפסים מובילים.
אז אתה מנסה לשנות את המזהה על ידי הזנת אפסים מובילים (00001 במקום 1).
אך לתדהמתך, Excel מחזיר אותו ל -1.
זה קורה מכיוון שאקסל מבינה ש- 00001 ו- 1 הם אותם מספרים ועליהם לפעול לפי אותם כללי תצוגה.
כעת מתסכל ככל שיהיה עבורך, ל- Excel יש את הסיבות לכך.
לכן, בכדי לבצע את העבודה מבלי לכופף כללי Excel, יהיה עליך לנצל את העובדה כי כלל זה אינו חל על עיצוב טקסט.
אז הנה מה שאתה צריך לעשות:
- בחר את התאים שבהם ברצונך להוסיף באופן ידני אפסים מובילים.
- עבור לדף הבית → קבוצת מספרים ובחר טקסט מהתפריט הנפתח.
זהו זה!
כעת, כאשר אתה מזין אפסים מובילים באופן ידני, Excel יעמוד בזריזות.
זהירות: בעת המרת הפורמט לטקסט, חלק מפונקציות Excel לא יפעלו כראוי. לדוגמה, הפונקציה SUM/COUNT תתעלם מהתא מכיוון שהוא בתבנית הטקסט.
הוסף אפסים מובילים באמצעות עיצוב מספר מותאם אישית
מתי להשתמש: כאשר יש לך מערך נתונים מספרי ואתה רוצה שהתוצאה תהיה מספרית (לא טקסט).
כאשר אתה מציג מספר בפורמט ספציפי, הוא אינו משנה את הערך הבסיסי של המספר. לדוגמה, אני יכול להציג את המספר 1000 כ- 1000 או 1,000 או 1000.00 או 001000 או 26-09-1902 (אפילו תאריכים הם מספרים בקצה האחורי ב- Excel).
בכל הדרכים השונות להצגת המספר, ערך המספר לעולם אינו משתנה. רק האופן שבו הוא מוצג משתנה.
כדי להוסיף אפסים מובילים, אנו יכולים לעצב אותו כדי להציג זאת כך, בעוד שהערך הבסיסי יישאר ללא שינוי.
להלן השלבים לשימוש בטכניקה זו להוספת אפסים מובילים ב- Excel:
- בחר את התאים שבהם ברצונך להוסיף אפסים מובילים.
- עבור לדף הבית → קבוצת מספרים ולחץ על משגר הדו -שיח (חץ קטן מוטה בפינה השמאלית התחתונה). פעולה זו תפתח את תיבת הדו -שיח עיצוב תאים. לחלופין, תוכל גם להשתמש בקיצור המקשים: Control + 1.
- בתיבת הדו -שיח עיצוב תאים, בכרטיסיה מספר, בחר מותאם אישית ברשימת הקטגוריות.
- בשדה סוג, הזן 00000
- לחץ על אישור.
פעולה זו תציג תמיד את כל המספרים כחמש ספרות, כאשר המספר 0 המוביל יתווסף אוטומטית אם המספר קטן מ -5 ספרות. אז 10 יהפוך ל- 00010 ו -100 יהפכו ל- 00100.
במקרה זה, השתמשנו בשישה אפסים, אך אם לנתונים שלך יש מספרים עם יותר ספרות, עליך להשתמש בפורמט בהתאם.
הערה: טכניקה זו תפעל רק עבור מערך נתונים מספרי. במקרה שיש לך מזהי עובדים כמו A1, A2, A3 וכן הלאה, אלה הם טקסט ולא ישתנו בעת החלת הפורמט המותאם אישית כפי שמוצג למעלה.
הוסף אפסים מובילים באמצעות פונקציית TEXT
מתי להשתמש: כאשר אתה רוצה שהתוצאה תהיה טקסט.
פונקציית TEXT מאפשרת לשנות את הערך לפורמט הרצוי.
לדוגמה, אם ברצונך ש -1 יוצג כ- 001, תוכל להשתמש בפונקציית TEXT לשם כך.
עם זאת, זכור כי הפונקציה TEXT תשנה את הפורמט ותהפוך אותו לטקסט. המשמעות היא שכאשר אתה יוצר 1 כ- 001, Excel מתייחס לתוצאה החדשה כטקסט בעל שלושה תווים (בדיוק כמו abc או xyz).
להלן אופן הוספת אפסים מובילים באמצעות פונקציית TEXT:
- אם יש לך את המספרים בעמודה A (נניח מ- A2: A100), בחר B2: B100 והזן את הנוסחה הבאה:
= טקסט (A2, ”00000 ″) - הקש Control + Enter כדי להחיל את הנוסחה על כל התאים שנבחרו.
זה יציג את כל המספרים כחמש ספרות, כאשר 0 המובילים יתווספו אוטומטית אם המספר קטן מ -5 ספרות.
אחד היתרונות של המרת נתונים לטקסט הוא שעכשיו תוכל להשתמש בהם בנוסחאות חיפוש כגון VLOOKUP או INDEX/MATCH כדי להביא את פרטיו של עובד באמצעות מזהה העובד שלו.
הערה: טכניקה זו תפעל רק עבור מערך נתונים מספרי. במקרה שיש לך מזהי עובדים כמו A1, A2, A3 וכן הלאה, אלה הם טקסט ולא ישתנו בעת החלת הפורמט המותאם אישית כפי שמוצג למעלה..
הוסף אפסים מובילים באמצעות פונקציות REPT ו- LEN
מתי להשתמש: כשיש לך מערך נתונים שהוא מספרי/אלפאנומרי, ואתה רוצה שהתוצאה תהיה טקסט.
החיסרון בשימוש בפונקציית TEXT היה שזה יעבוד רק עם נתונים מספריים. אבל במקרה שיש לך מערך נתונים אלפאנומרי (נניח A1, A2, A3 וכן הלאה), אז הפונקציה TEXT תיכשל.
במקרים כאלה, שילוב של פונקציית REPT ו- LEN עושה את העבודה.
הנה איך לעשות את זה:
- אם יש לך את המספרים בעמודה A (נניח מ- A2: A100), בחר B2: B100 והזן את הנוסחה הבאה:
= REPT (0,5-LEN (A2)) & A2 - הקש Control + Enter כדי להחיל את הנוסחה על כל התאים שנבחרו.
זה יגרום לכל המספרים/מחרוזות באורך של 5 תווים עם אפסים מובילים בכל מקום שצריך.
כך פועלת נוסחה זו:
- LEN (A2) נותן את אורך המחרוזת/מספרים בתא.
- = REPT (0,5-LEN (A2)) ייתן את המספר 0 שיש להוסיף. כאן השתמשתי ב -5 בנוסחה מכיוון שזהו האורך המרבי של מחרוזת/מספרים במערך הנתונים שלי. אתה יכול לשנות זאת בהתאם לנתונים שלך.
- = REPT (0,5-LEN (A2)) & A2 פשוט יוסיפו את מספר האפסים לערך התא. לדוגמה, אם הערך בתא הוא 123, הדבר יחזיר 00123.
הוסף אפסים מובילים באמצעות פונקציה מותאמת אישית (VBA)
אם הוספת אפסים מובילים ב- Excel היא משהו שאתה נדרש לעשות לעתים קרובות למדי, שימוש בפונקציה מותאמת אישית הוא רעיון טוב.
להלן קוד ה- VBA שייצור פונקציה פשוטה להוספת אפסים מובילים:
'קוד על ידי Sumit Bansal מ- http://trumpexcel.com פונקציה AddLeadingZeroes (ref כטווח, אורך כמכלול) Dim i As Integer Dim Result As String Dim StrLen As Integer StrLen = Len (ref) For i = 1 to Length if i <= StrLen ואז התוצאה = תוצאה ואמצע (ref, i, 1) אחר תוצאות = "0" ותוצאה מסתיימת אם הבא i AddLeadingZeroes = תוצאה סיום התוצאה
פשוט הוסף קוד זה לחלון קוד המודול, ותוכל להשתמש בו בדיוק כמו כל פונקציה אחרת של דף עבודה.
או צור תוסף עבורו ותוכל לשתף אותו עם עמיתיך.
