צפה בוידאו - כיצד להסתיר ערכי אפס ב- Excel + כיצד להסיר ערכי אפס
במקרה שאתה מעדיף לקרוא על פני צפייה בסרטון, להלן ההדרכה הכתובה המלאה.
לפעמים באקסל, אולי תרצה להסתיר ערכי אפס במערך הנתונים שלך והצג תאים אלה כחסר.
נניח שיש לך מערך נתונים כפי שמוצג להלן ואתה רוצה להסתיר את הערך 0 בכל התאים האלה (או שתרצה להחליף אותו במשהו כמו מקף או הטקסט 'לא זמין').
למרות שאתה יכול לעשות זאת באופן ידני עם מערך נתונים קטן, תצטרך דרכים טובות יותר בעת עבודה עם מערכי נתונים גדולים.
במדריך זה אראה לך מספר דרכים להסתיר ערכי אפס ב- Excel ושיטה אחת לבחירה והסרה של כל ערכי האפס ממערך הנתונים.
הערה חשובה: כאשר אתה מסתיר 0 בתא בשיטות המוצגות במדריך זה, הוא יסתיר רק את ה 0 ולא יסיר אותו. למרות שהתאים עשויים להיראות ריקים, תאים אלה עדיין מכילים את ה- 0s. ואם אתה משתמש בתאים האלה (שיש להם אפסים מוסתרים) בכל נוסחה, אלה ישמשו בנוסחה.בואו נתחיל וצלול לכל אחת מהשיטות האלה!
הסתר אוטומטית ערך אפס בתאים
ל- Excel יש פונקציונליות מובנית המאפשרת להסתיר אוטומטית את כל ערכי האפס עבור כל גליון העבודה.
כל שעליך לעשות הוא לבטל את הסימון בתיבה באפשרויות Excel, והשינוי יחול על כל גליון העבודה.
נניח שיש לך את מערך המכירות כפי שמוצג להלן ואתה רוצה להסתיר את כל ערכי האפס ולהציג תאים ריקים במקום זאת.
להלן השלבים להסתרת אפסים מכל התאים בחוברת עבודה ב- Excel:
- פתח את חוברת העבודה שבה ברצונך להסתיר את כל האפסים
- לחץ על הכרטיסייה קובץ
- לחץ על אפשרויות
- בתיבת הדו -שיח אפשרויות Excel שנפתחת, לחץ על האפשרות 'מתקדם' בחלונית השמאלית
- גלול מטה לקטע שאומר 'אפשרות תצוגה עבור גליון עבודה זה', ובחר את גליון העבודה שבו ברצונך להסתיר את האפסים.
- בטל את הסימון של האפשרות 'הצג אפס בתאים שיש להם ערך אפס'
- לחץ על אישור
השלבים שלעיל יסתירו אפסים באופן מיידי בכל התאים בגליון העבודה שנבחר.
שינוי זה מיושם גם על תאים שבהם אפס הוא תוצאה של נוסחה.
זכור כי שיטה זו מסתירה רק את ערך 0 בתאים ואינה מסירה אותם. ה 0 עדיין שם, הוא פשוט מוסתר.
זוהי השיטה הטובה והמהירה ביותר להסתיר ערכי אפס ב- Excel. אבל זה שימושי רק אם אתה רוצה להסתיר ערכי אפס בכל גליון העבודה. במקרה שאתה רק רוצה שזה יסתיר אפס ערכים בטווח תאים ספציפי, עדיף להשתמש בשיטות אחרות שנלמד להלן במדריך זה.הסתר ערך אפס בתאים באמצעות עיצוב מותנה
למרות שהשיטה לעיל היא הנוחה ביותר, היא לא מאפשרת לך להסתיר אפסים רק בטווח ספציפי (אלא היא מסתירה אפס ערכים בגיליון העבודה כולו).
במקרה שאתה רק רוצה להסתיר אפסים עבור מערך נתונים ספציפי (או מערכי נתונים מרובים), תוכל להשתמש בעיצוב מותנה.
בעזרת עיצוב מותנה, תוכל ליישם פורמט ספציפי המבוסס על הערך בתאים. אז אם הערך בחלק מהתאים הוא 0, אתה יכול פשוט לתכנת עיצוב מותנה כדי להסתיר אותו (או אפילו להדגיש אותו אם תרצה).
נניח שיש לך מערך נתונים כפי שמוצג להלן ואתה רוצה להסתיר אפסים בתאים.
להלן השלבים להסתרת אפסים ב- Excel באמצעות עיצוב מותנה:
- בחר את מערך הנתונים
- לחץ על הכרטיסייה 'דף הבית'
- בקבוצה 'סגנונות', לחץ על 'עיצוב מותנה'.
- מקם את הסמן מעל האפשרויות 'כללי תאים מדגישים' ובאפשרויות שמופיעות לחץ על האפשרות 'שווה ל'.
- בתיבת הדו -שיח 'שווה ל', הזן '0' בשדה השמאלי.
- בחר את התפריט הנפתח עיצוב ולחץ על 'פורמט מותאם אישית'.
- בתיבת הדו -שיח 'עיצוב תאים', בחר בכרטיסייה 'גופן'.
- מהתפריט הנפתח צבע, בחר את הצבע הלבן.
- לחץ על אישור.
- לחץ על אישור.
השלבים שלעיל משנים את צבע הגופן של התאים עם האפסים ללבן, מה שגורם לתאים האלה להיראות ריקים.
פתרון זה פועל רק כאשר יש לך רקע לבן בגליון העבודה שלך. במקרה שיש צבע רקע אחר בתאים, הצבע הלבן עדיין עשוי להשאיר את האפסים גלויים.
אם אתה באמת רוצה להסתיר את האפסים בתאים, שבהם התאים נראים ריקים, לא משנה איזה צבע רקע ניתן לתאים, תוכל להשתמש בשלבים שלהלן.
- בחר את מערך הנתונים
- לחץ על הכרטיסייה 'דף הבית'
- בקבוצה 'סגנונות', לחץ על 'עיצוב מותנה'.
- מקם את הסמן מעל האפשרויות 'כללי תאים מדגישים' ובאפשרויות שמופיעות לחץ על האפשרות 'שווה ל'.
- בתיבת הדו -שיח 'שווה ל', הזן 0 בשדה השמאלי.
- בחר את התפריט הנפתח עיצוב ולחץ על פורמט מותאם אישית.
- בתיבת הדו -שיח 'עיצוב תאים', בחר בכרטיסייה 'מספר' ולחץ על האפשרות 'מותאם אישית' בחלונית השמאלית.
- באפשרות פורמט מותאם אישית, הזן את הדברים הבאים בשדה סוג: ;;; (שלושה נקודתיים למחצה ללא מקום)
- לחץ על אישור.
- לחץ על אישור.
השלבים שלעיל משנים את העיצוב המותאם אישית של תאים בעלי הערך 0.
כשאתה משתמש בשלושה נקודתיים חצי כפורמט, הוא מסתיר את כל מה שבתא (בין אם זה מספרי או טקסט). ומכיוון שפורמט זה מיושם רק על אותם תאים שיש בהם את הערך 0, הוא מסתיר רק את האפסים ולא את שאר מערך הנתונים.
אתה יכול גם להשתמש בשיטה זו כדי להדגיש תאים אלה בצבע אחר (כגון אדום או צהוב). לשם כך, הוסף שלב נוסף (לאחר שלב 8) שבו עליך לבחור בכרטיסיה מילוי בתיבת הדו -שיח 'עיצוב תאים' ולאחר מכן בחר את הצבע שבו ברצונך להדגיש את התאים.שתי השיטות המכוסות לעיל פועלות גם כאשר 0 היא תוצאה של נוסחה בתא.
הערה: שתי השיטות שנכללות בסעיף זה מסתירות רק את ערכי האפס. זה לא מסיר את האפסים האלה. המשמעות היא שבמקרה שתשתמש בתאים אלה בחישובים, ישמשו גם את כל האפסים.הסתר ערך אפס באמצעות עיצוב מותאם אישית
דרך נוספת להסתיר ערכי אפס ממערך נתונים היא על ידי יצירת פורמט מותאם אישית שמסתיר את הערך בתאים שיש להם 0s, בעוד שכל ערך אחר מוצג כצפוי.
להלן השלבים לשימוש בפורמט מותאם אישית להסתרת אפסים בתאים:
- בחר את מערך הנתונים כולו שעבורו ברצונך ליישם פורמט זה. אם אתה רוצה את זה עבור כל גליון העבודה, בחר את כל התאים
- כשהתאים נבחרים, לחץ על הכרטיסייה דף הבית
- בקבוצה תאים, לחץ על האפשרות עיצוב
- מהאפשרות הנפתחת, לחץ על עיצוב תאים. פעולה זו תפתח את תיבת הדו -שיח עיצוב תאים
- בתיבת הדו -שיח עיצוב תאים, בחר בכרטיסיה מספר
- באפשרות בחלונית השמאלית, לחץ על האפשרות Custom.
- באפשרויות מימין, הזן את הפורמט הבא בשדה סוג: 0;-0;;@
- לחץ על אישור
השלבים שלעיל יחילו תבנית מותאמת אישית על התאים שנבחרו כאשר רק התאים בעלי הערך 0 מעוצבים כדי להסתיר את הערך, בעוד שאר התאים מציגים את הנתונים כצפוי.
כיצד פועל פורמט מותאם אישית זה?
ב- Excel, אתה מציין ארבעה סוגים של פורמטים של נתונים:
- מספרים חיוביים
- מספרים שליליים
- אפסים
- טֶקסט
ולכל אחד מסוגי הנתונים הללו, תוכל לציין פורמט.
ברוב המקרים יש פורמט ברירת מחדל - כללי, ששומר על כל סוגי הנתונים האלה גלויים. אבל יש לך את הגמישות ליצור פורמט ספציפי לכל אחד מסוגי הנתונים האלה.
להלן מבנה הפורמט המותאם אישית שעליך לעקוב אחריו כאשר אתה מחיל פורמט מותאם אישית לכל אחד מסוגי הנתונים הבאים:
;;;
בשיטה המכוסה למעלה ציינתי את הפורמט כך:
0;-0;;@
הפורמט שלמעלה מבצע את הפעולות הבאות:
- מספרים חיוביים מוצגים כפי שהם
- מספרים שליליים מוצגים עם סימן שלילי
- אפסים מוסתרים (מכיוון שלא פורמט פורמט)
- הטקסט מוצג כפי שהוא
החלף את האפסים במקף או טקסט ספציפי
במקרים מסוימים, ייתכן שתרצה להסתיר לא רק את האפסים בתאים, אלא להחליף אותם במשהו משמעותי יותר.
לדוגמה, ייתכן שתרצה להסיר את האפסים ולהחליף אותם במקף או בטקסט כגון "לא זמין" או "נתונים שעוד יגיעו"
הדבר מתאפשר שוב באמצעות עיצוב מספרים מותאם אישית, שבו תוכל לציין מה תא צריך להציג במקום אפס.
להלן השלבים להמרת 0 למקף ב- Excel:
- בחר את מערך הנתונים כולו שעבורו ברצונך ליישם פורמט זה. אם אתה רוצה את זה עבור כל גליון העבודה, בחר את כל התאים
- כשהתאים נבחרים, לחץ על הכרטיסייה דף הבית
- בקבוצה תאים, לחץ על האפשרות עיצוב
- מהאפשרות הנפתחת, לחץ על עיצוב תאים. פעולה זו תפתח את תיבת הדו -שיח עיצוב תאים
- בתיבת הדו -שיח עיצוב תאים, בחר בכרטיסיה מספר
- באפשרות בחלונית השמאלית, לחץ על האפשרות Custom.
- באפשרויות מימין, הזן את הפורמט הבא בשדה סוג: 0;-0;-;@
- לחץ על אישור
השלבים שלעיל ישמרו על כל התאים האחרים כפי שהם וישנו את התאים עם הערך 0 כדי להציג מקף (-).
הערה: בדוגמה זו, בחרתי להחליף את האפס במקף, אך ניתן להשתמש בכל טקסט גם כן. לדוגמה, אם ברצונך להציג את ה- NA בתאים, תוכל להשתמש בפורמט הבא: 0; -0; "NA";@טיפ למקצוענים: ניתן גם לציין כמה צבעי טקסט בעיצוב מספר מותאם אישית. לדוגמה, אם אתה רוצה שהתאים עם אפסים יציגו את הטקסט NA בצבע אדום, תוכל להשתמש בפורמט הבא: 0; -0; [אדום] ”NA”;@הסתר ערכי אפס בטבלאות ציר
יכולים להיות שני תרחישים שבהם טבלת צירים מציגה את הערך כ- 0:
- לתאי נתוני המקור המסוכמים בטבלת הצירים יש 0 ערכים
- תאי נתוני המקור המסוכמים בטבלת הצירים הינם ריקים וטבלת הציר עוצבה כך שתציגו את התאים הריקים כאפס
בואו נראה כיצד להסתיר את האפסים בטבלת הצירים בכל תרחיש:
כאשר לתאי נתוני מקור יש 0s
להלן מערך נתונים לדוגמה שבו השתמשתי ליצירת טבלת ציר. שים לב שמספרי הכמות/הכנסה/רווח עבור כל הרשומות של 'מערב' הן 0.
טבלת הצירים שנוצרה באמצעות מערך נתונים זה נראית כפי שמוצג להלן:
מכיוון שהיו 0 נתונים במערך הנתונים עבור כל הרשומות עבור אזור מערב, אתה רואה כאן 0 בטבלת הצירים למערב.
אם ברצונך להסתיר אפס בטבלת צירים זו, תוכל להשתמש בעיצוב מותנה לשם כך.
עם זאת, עיצוב מותנה בטבלת הצירים ב- Excel פועל מעט שונה מאשר נתונים רגילים.
להלן השלבים להחלת עיצוב מותנה על טבלת צירים להסתרת אפסים:
- בחר תא כלשהו בעמודה המכיל 0s בסיכום טבלת הצירים (אל תבחר את כל התאים, אלא רק תא אחד)
- לחץ על הכרטיסייה דף הבית
- בקבוצה 'סגנונות', לחץ על 'עיצוב מותנה'.
- מקם את הסמן מעל האפשרויות 'כללי תאים מדגישים' ובאפשרויות שמופיעות לחץ על האפשרות 'שווה ל'.
- בתיבת הדו -שיח 'שווה ל', הזן 0 בשדה השמאלי.
- בחר את התפריט הנפתח עיצוב ולחץ על פורמט מותאם אישית.
- בתיבת הדו -שיח 'עיצוב תאים', בחר בכרטיסיה מספר ולחץ על האפשרות 'מותאם אישית' בחלונית השמאלית.
- באפשרות פורמט מותאם אישית, הזן את הדברים הבאים בשדה סוג: ;;; (שלושה נקודתיים למחצה ללא מקום)
- לחץ על אישור. תראה סמל אפשרויות עיצוב קטן מימין לתא שנבחר.
- לחץ על סמל זה 'אפשרויות עיצוב'
- באפשרויות שיהפכו לזמינות, בחר - כל התאים המציגים ערכי "סכום הכנסה" עבור "אזור".
השלבים שלעיל מסתירים את האפס בטבלת הצירים באופן שאם תשנה את מבנה טבלת הצירים (על ידי הוספת כותרות שורות/עמודות אליו, האפסים ימשיכו להיות מוסתרים).
אם אתה רוצה ללמוד עוד על אופן הפעולה, להלן הדרכה מפורטת על הדרך הנכונה החלת עיצוב מותנה על טבלאות ציר.
כאשר לתאי נתוני מקור יש תאים ריקים
להלן דוגמה שבה יש לי מערך נתונים הכולל תאים ריקים עבור כל הרשומות עבור ווסט.
אם אני יוצר טבלת ציר באמצעות נתונים אלה, אקבל את התוצאה כפי שמוצג להלן.
כברירת מחדל, טבלת הצירים מציגה תאים ריקים כאשר כל התאים בנתוני המקור ריקים. אך במקרה שאתה עדיין רואה 0 במקרה זה, בצע את השלבים הבאים כדי להסתיר את האפס ולהציג תאים ריקים:
- לחץ לחיצה ימנית על כל אחד מהתאים בטבלת הצירים
- לחץ על 'אפשרויות טבלת צירים'
- בתיבת הדו -שיח 'אפשרויות PivotTable', לחץ על הכרטיסייה 'פריסה ועיצוב'
- באפשרויות העיצוב בדוק את האפשרויות ואת 'עבור תאים ריקים מוצגים:' והשאיר אותו ריק.
- לחץ על אישור.
השלבים שלעיל יסתירו את האפסים בטבלת הצירים ויציגו במקום תא ריק.
במקרה שתרצה שבטבלת הציר יופיע משהו במקום 0, תוכל לציין זאת בשלב 4. לדוגמה, אם ברצונך להציג את הטקסט - 'נתונים אינם זמינים', במקום 0, הקלד טקסט זה ב- שדה (כפי שמוצג להלן).
מצא והסר אפסים ב- Excel
בכל השיטות שלמעלה, הראיתי לך דרכים להסתיר את ערך 0 בתא, אך הערך עדיין נשאר בתא.
במקרה שאתה רוצה להסיר את ערכי האפס ממערך הנתונים (שישאיר אותך עם תאים ריקים), השתמש בשלבים הבאים:
- בחר את מערך הנתונים (או את כל גליון העבודה)
- לחץ על הכרטיסייה דף הבית
- בקבוצה 'עריכה', לחץ על 'מצא ובחר'
- באפשרויות המוצגות בתפריט הנפתח, לחץ על 'החלף'
- בצע את השינויים הבאים בתיבת הדו -שיח 'מצא והחלף':
- בשדה 'מצא מה', הזן 0
- בשדה 'החלף ב-' אל תזין דבר (השאר אותו ריק)
- לחץ על כפתור האפשרויות ובדוק את האפשרות 'התאמת תוכן התא כולו'
- לחץ על החלף הכל
השלבים שלעיל יחליפו באופן מיידי את כל התאים בעלי ערך אפס בחסר.
אם אינך רוצה להסיר את האפסים מייד ולבחור תחילה את התאים, בצע את השלבים הבאים:
- בחר את מערך הנתונים (או את כל גליון העבודה)
- לחץ על הכרטיסייה דף הבית
- בקבוצה 'עריכה', לחץ על 'מצא ובחר'
- באפשרויות המוצגות בתפריט הנפתח, לחץ על 'החלף'
- בצע את השינויים הבאים בתיבת הדו -שיח 'מצא והחלף':
- בשדה 'מצא מה', הזן 0
- בשדה 'החלף ב-' אל תזין דבר (השאר אותו ריק)
- לחץ על כפתור האפשרויות ובדוק את האפשרות 'התאמת תוכן התא כולו'
- לחץ על הלחצן 'מצא הכל'. זה ייתן לך רשימה של כל התאים שיש בהם 0
- הקש על Control-A (החזק את מקש השליטה והקש A). פעולה זו תבחר את כל התאים שיש בהם 0.
כעת, לאחר שבחרת את כל התאים עם 0, תוכל למחוק אותם, להחליף אותם במשהו אחר, או לשנות את הצבע ולהדגיש אותם.
הסתרת האפס נגד. הסרת האפסים
כאשר מטפלים בתאים עם 0 בתוכם, עליך לדעת את ההבדל בין הסתרת אלה לבין הסרתם.
כאשר אתה מסתיר את הערך האפס בתא, הוא אינו גלוי לך, אך הוא עדיין נשאר בתא. המשמעות היא שאם יש לך מערך נתונים ואתה משתמש בו לחישוב, ערך 0 זה ישמש גם בחישוב.
להיפך, כאשר יש לך תא ריק, השימוש בו בנוסחאות עשוי להיות שאקסל מתעלם אוטומטית מהתאים הללו (תלוי באיזו נוסחה אתה משתמש).
לדוגמה, להלן מה שאני מקבל כשאני משתמש בנוסחת AVERAGE כדי לקבל את הממוצע לשתי עמודות (התוצאה היא בשורה 14). בעמודה A יש תאים שבהם הערך 0 הוסתר (אך עדיין קיים בתא) ובעמודה B יש תאים שבהם ערך 0 הוסר.
אתה יכול לראות את ההבדל בתוצאה למרות שמערך הנתונים של שני מערכי הנתונים נראה אותו דבר.
זה קורה כאשר הנוסחה AVERAGE מתעלמת מתאים ריקים בעמודה B, אך היא משתמשת בתאים בעמודה A (כמו שלעמודה A עדיין יש את הערך 0).
