کاربرد تابع NA و خطای #N/A
چطور از تابع NA و خطای NA استفاده کنم؟
حتما تا حالا با خطای #N/A برخورد کردید. این خطا وقتی رخ میده که تابع مورد نظر داده دلخواه رو پیدا نکنه و عبارت Not Available رو نمایش میده. اما این خطا با سایر خطاهای فرمول نویسی متفاوت هست و کاربردهایی داره و بخاطر همین موضوع تابعی داریم به نام NA() برای تولید خطای #N/A. همچنین تفاوت دو تابع ISERR و ISERROR هم در خطا انگاشتن خطای #N/A هست. اگر با خطاهای فرمول نویسی و مبحث مدیریت خطا آشنایی ندارید حتما مقاله مدیریت خطا در اکسل رو مطالعه کنید. در این مقاله قصد داریم یک کاربرد خیلی خوب از این خطا رو ببینیم.
مثال کار با تابع NA
دیتابیسی داریم که فروش یک محصول در ماه های مختلف رو در خودش ذخیره کرده. حالا میخواهیم تا ماهی که داده وجود داره، نمودار نمایش داده بشه و ما بقی ماه ها خالی باشه.
شکل ۱- نمودار فروش محصولات در ماه های مختلف با استفاده از تابع NA
برای این کار اول باید گزارشی ایجاد کنیم که مجموع فروش هر ماه رو محاسبه کنه. این کار رو با دو روش انجام میدیم:
- با استفاده از پیوت تیبل. (داخل ویدئو با Pivot انجام شده).
- با استفاده از فرمول نویسی.
برای اینکه با هر دو روش آشنا بشیم، در این مقاله، من با استفاده از فرمول نویسی این گزارش رو ایجاد میکنم. تابعی که برای این کار استفاده میکنیم تابع SUMIFS هست و برای اینکه محدوده داینامیک باشه از table و Structured reference استفاده میکنیم.
پس طبق زیر عمل میکنیم:
- محدوده داده ها رو به Table تبدیل میکنیم. برای این کار محدوده رو انتخاب کرده و با کلید Ctrl+T یا از تب Insert/Table محدوده رو به Table تبدیل میکنیم. اگر با امکانات و ویژگی های تیبل آشنا نیستید حتما مقاله مربوط به Table رو مطالعه کنید.
شکل ۲- تبدیل محدوده Range به Table
- حالا در یک ستون جداگانه، دوازده ماه سال رو وارد میکنیم. من این کار رو از طریق Custom List انجام میدم.
- سپس با استفاده از تابع SUMIFS مجموع فروش هر ماه رو محاسبه میکنیم. برای استفاده از Table داخل فرمول نویسی و نحوه درگ کردن و … حتما مقاله Structured reference رو مطالعه کنید.
=SUMIFS (Table4[فروش] ,Table4[ماه] , E2)
شکل ۳ – محاسبه مجموع فروش هر ماه
همونطور که در شکل ۳ نمایش داده شده، مجموع فروش به ازای ماه هایی که هنوز دیتا وارد نشده، صفر نمایش داده میشه. که اگر روی این داده ها نمودار رسم کنیم، نموداری مطابق با شکل ۴ خواهیم داشت که مطلوب نیست. ما میخوایم به ازای ماه هایی که فروش ثبت نشده، نمودار رسم نشه و فقط ماه ها در محور افقی نمایش داده بشن.
شکل ۴- رسم نمودار روی داده های محاسبه شده
- پس برای رفع این مشکل باید کاری کنیم که به ازای مقادیر صفر، داده ای نمایش داده بشه که داخل نمودار رسم نشه. این داده عبرت است از خطای #N/A. همونطو رکه میدونید تابع NA() این مقدار رو تولید میکنه. پس کافیه که فرمول SUMIF رو با تابع IF و NA() ترکیب کنیم. به این صورت:
=IF( SUMIFS( Table4[فروش], Table4[ماه], E2)>0 ,SUMIFS( Table4[فروش], Table4[ماه], E2) , NA())
شکل ۵- ترکیب تابع NA برای ایجاد خطای #N/A
اگر بجای NA() از “” استفاده کنیم، باز هم نتیجه نمیگیریم. چون ظاهر سلول خالی نشون داده میشه و عملا داهل سلول پر هست و نمودار صفر در نظر میگیره و باز هم نموداری شبیه شکل ۴ نمایش خواهد داد. پس حتما باید از تابع NA() برای این کار استفاده کنیم.
- مرحله آخر هم رسم نمودار هست. کافیه داده ها رو انتخاب کنیم، و از تب Insert نمودار خطی رو انتخاب کنیم. بعد از انجام تنظیمات گرافیکی دلخواه، نموداری مشابه شکل ۶ خواهیم داشت.
شکل ۶- نمودار نمایش مجموع فروش به تفکیک ماه
حالا اگر در انتهاب دیتابیس، فروش ماه های جدید رو اضافه کنیم، بدون نیاز به زدن دکمه خاصی، فرمول نوشته شده و به تبع اون نمودار ،آپدیت خواهد شد. در ویدئو زیر ، برای نمایش دو قسمت دور از هم در یک شیت از Split استفاده شده.
در این مقاله به کاربرد خیلی مهم تابع NA() پرداختیم. این ترفند در رسم نمودارهای پویا، داشبوردسازی و … بسیار مهم و پرکاربرد هست.
درود بر شما همراه با احترام و سپاس تام