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