مخفی کردن صفر در اکسل

گاهی اوقات پیش میاد که در قسمتی که محاسبات انجام دادیم، میخواهیم هر جا صفر وجود داشت، سلول خالی نمایش داده بشه. در واقع میخواهیم مقادیر صفر نمایش داده نشه. در این مقاله برای حل مسئله مخفی کردن صفر چند روش ارائه میکنیم که بنا به شرایط، از هر کدوم در جای خود استفاده میکنیم.

پنهان کردن صفر در سلول

روش اول: تنظیمات اکسل

ما میتونیم از قسمت تنظیمات اکسل، در قسمت File/ Option تنظیماتی رو انجام بدیم که هر سلولی که صفر وجود داشت، خالی نمایش داده بشه. برای این کار طبق شکل ۱ عمل میکنیم. از مسیر نمایش داده شده تیک گزینه Show a zero in cells that have zero value رو برمیداریم.

شکل ۱ – عدم نمایش صفر- تنظیمات اکسل

توجه داشته باشید که این روش برای کل فایل (همه شیت ها) هست. در واقع با این کار هر سلولی در هر کجای فایل حاوی مقدار صفر باشه، نمایش داده نمیشه.

روش دوم: فرمت سل Format Cell

در این روش میتونیم فرمت سلول رو طوری تنظیم کنیم که صفر موجود در سلول رو نمایش نده.

همونطور که میدونیم فرمت سل در اکسل قابلیت ویژه سازی و کدنویسی داره که بخشی از این کدنویسی رو در مقاله فرمت سل تشریح کردیم.

اما قبل از اینکه به ادامه تشریح این روش بپردازیم اول باید با ساختار و الگوی کلی کدنویسی در این قسمت آشنا بشیم. ساختار کلی برای کدنویسی در فرمت سل اکسل عبارت است از:

پس با توجه به الگوی بالا ما باید برای صفر کدی بنویسیم که نمایش داده نشه. در واقع میخوایم بگیم، اعداد مثبت و منفی و متن همانطور که هستن نمایش داده بشوند، فقط صفر ها نمایش داده نشه. برای این کار کدی مطابق زیر مینویسیم:

۰ ; -۰ ; ; @

در واقع معنی این کد این هست که اعداد مثبت به شکل خود و اعداد منفی به شکل خود نمایش داده شوند، اگر داده داخل سلول صفر بود، خالی گذاشته بشه (مشاهده میکنید در فاصله بین دو ; چیزی تایپ نشده است). در نهایت هم اگر متن بود، همان متن نمایش داده بشه.

حالا کافیه محدوده مورد نظر رو انتخاب کنیم و از قسمت Format Cell/Custom کد بالا رو ثبت کنیم.

شکل ۲- عدم نمایش صفر – کدنویسی در فرمت سل

همونطور که در تصویر شماره ۲ مشاهده میشه، با وارد کردن کد مورد نظر در محدوده C1:C6 سلول های شامل صفر، خالی نمایش داده میشن.

ویژگی این روش این هست که در محدوده دلخواه میتونیم این تنظیم رو انجام بدیم و لازم نیست روی کل فایل اعمال بشه.

روش سوم: فرمت دهی شرطی Conditional Formatting

این روش مشابه روش دوم هست که در ادامه به تشریح آن می پردازیم. در این قسمت میتونیم با فرمت دهی در conditional formatting شرط مورد نظر، یعنی صفر بودن مقدار موجود در سلول رو بررسی کنیم.

پس داده ها رو انتخاب میکنیم و در قسمت highlight cells Rules گزینه Equal to رو انتخاب کرده و صفر رو در قسمت شرط وارد میکنیم. بعد از قسمت فرمت یا رنگ فونت رو با پس زمینه مشابه قرار میدیم (پیشنهاد نمیشه) یا اینکه فرمت ;;; رو در قسمت Custom در Format/ Number تعیین میکنیم.

شکل ۳- عدم نمایش صفر – فرمت دهی از طریق Conditional Formatting

روش چهارم: فرمول نویسی

در این روش میتونیم با فرمول نویسی و استفاده از توابع، این کار رو انجام بدیم. برای عدم نمایش صفر در سلول از فرمول IF استفاده میکنیم. به این صورت:

=IF ( A1=0 , “” , A1 )

شکل ۴- عدم نمایش صفر – استفاده از تابع IF

پنهان کردن صفر در Pivottable

گاهی اوقات در دیتابیس داده هایی وجود داره که مقدار ندارند، این مقادیر بصورت ۰ در گزارش نهایی پیوت تیبل نمایش داده میشه که میتونیم این مقادیر رو از طریق قسمت Options مطابق با خواست خودمون تغییر بدیم.

همونطور که در شکل ۵ نمایش داده شده، برای ماه خرداد و اسفند که در دیتابیس داده ای وجود نداشته، مقدار صفر نمایش داده شده (ردیف های رنگی). حالا باید نحوه نمایش داده های ناموجود رو تعیین کنیم. اگر تیک گزینه For empty cells show رو برداریم، سلول خالی بجای صفر نمایش داده میشه.

شکل ۵ – پنهان کردن صفر در پیوت تیبل

عدم نمایش صفر در نمودار

گاهی اوقات پیش میاد که بخوایم داده هایی که مقادیر صفر دارند، در نمودار نمایش داده نشه. برای این کار باید مقادیر صفر رو به سلول خالی یا مقدار #N/A تبدیل کنیم.

این کار رو میتونیم با فرمول نویسی یا ابزار Find / Replace انجام بدیم. میتونیم در ابزار Find در قسمت Find what رو خالی بذاریم و در قسمت Replace مقدار #N/A رو تایپ کنیم. (مطابق شکل ۶).

دقت کنید حتما تیک match entire cell رو بزنید که اعدادی مثل ۱۰ که شامل صفر هستند خراب نشه.

شکل ۶ – پنهان کردن صفر در نمودار با ابزار

یا اینکه با استفاده از فرمول IF مقدار صفر رو با سلول خالی یا مقدار #N/A جایگزین میکنیم.

شکل ۷- عدم نمایش صفر- عدم نمایش صفر در نمودار با فرمول

حالا که داده ها آماده شد، میتونیم نمودار رو روی داده های آماده شده رسم کنیم. نمودار مطابق شکل ۸ رسم میشه. در واقع داده صفر که به سلول خالی یا #N/A تبدیل میشه، در نمودار نمایش داده نمیشه.

شکل ۸- پنهان کردن صفر در نمودار

حالا میتونیم تصمیم بگیریم که سلول های خالی در نمودار به چه صورت نمایش داده بشن. برای این کار نمودار رو انتخاب میکنیم و از تب Design/ select data روی گزینه Hidden and empty cells کلیک میکنیم. در پنجره نمایش داده دشه تعیین میکنیم که سلول های خالی (یا #N/A) چطور نمایش داده بشن. (شکل ۹)

شکل ۹ – عدم نمایش صفر – تعیین نحوه نمایش سلول های خالی (مقادیر #N/A) در نمودار

با تنظیمات موجود در شکل ۹، انتخاب کردیم که نقاط به هم وصل بشن . د رواقع نقطه قبل و بعد از مقدار صفر، به هم وصل بشن. نمودار به شکل زیر نمایش داده میشه.

شکل ۱۰ – عدم نمایش مقدار صفر در نمودار