آغاز ثبت نام دوره آنلاین مقدماتی تا میانی اکسل (ظرفیت محدود)
سبد خرید
0

سبد خرید شما خالی است.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

کاربرد تابع NA و خطای #N/A

کاربرد تابع NA() و خطای #N/A
۳/۵ - (۲ امتیاز)

چطور از تابع NA و خطای NA استفاده کنم؟

حتما تا حالا با خطای #N/A برخورد کردید. این خطا وقتی رخ میده که تابع مورد نظر داده دلخواه رو پیدا نکنه و عبارت Not Available رو نمایش میده. اما این خطا با سایر خطاهای فرمول نویسی متفاوت هست و کاربردهایی داره و بخاطر همین موضوع تابعی داریم به نام NA() برای تولید خطای #N/A. همچنین تفاوت دو تابع ISERR و ISERROR هم در خطا انگاشتن خطای #N/A هست. اگر با خطاهای فرمول نویسی و مبحث مدیریت خطا آشنایی ندارید حتما مقاله مدیریت خطا در اکسل رو مطالعه کنید. در این مقاله قصد داریم یک کاربرد خیلی خوب از این خطا رو ببینیم.

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

نمودار فروش محصولات در ماه های مختلف

شکل ۱- نمودار فروش محصولات در ماه های مختلف با استفاده از تابع NA

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

  1. با استفاده از پیوت تیبل. (داخل ویدئو با Pivot انجام شده).
  2. با استفاده از فرمول نویسی.

برای اینکه با هر دو روش آشنا بشیم، در این مقاله، من با استفاده از فرمول نویسی این گزارش رو ایجاد میکنم. تابعی که برای این کار استفاده میکنیم تابع SUMIFS هست و برای اینکه محدوده داینامیک باشه از table و Structured reference استفاده میکنیم.

پس طبق زیر عمل میکنیم:

  1. محدوده داده ها رو به Table تبدیل میکنیم. برای این کار محدوده رو انتخاب کرده و با کلید Ctrl+T یا از تب Insert/Table محدوده رو به Table تبدیل میکنیم. اگر با امکانات و ویژگی های تیبل آشنا نیستید حتما مقاله مربوط به Table رو مطالعه کنید.

تبدیل محدوده Range به Table

شکل ۲- تبدیل محدوده Range به Table

  1. حالا در یک ستون جداگانه، دوازده ماه سال رو وارد میکنیم. من این کار رو از طریق Custom List انجام میدم.
  2. سپس با استفاده از تابع SUMIFS مجموع فروش هر ماه رو محاسبه میکنیم. برای استفاده از Table داخل فرمول نویسی و نحوه درگ کردن و … حتما مقاله Structured reference رو مطالعه کنید.

=SUMIFS (Table4[فروش] ,Table4[ماه] , E2)

محاسبه مجموع فروش هر ماه

شکل ۳ – محاسبه مجموع فروش هر ماه

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

رسم نمودار روی داده های محاسبه شده

شکل ۴- رسم نمودار روی داده های محاسبه شده

  1. پس برای رفع این مشکل باید کاری کنیم که به ازای مقادیر صفر، داده ای نمایش داده بشه که داخل نمودار رسم نشه. این داده عبرت است از خطای #N/A. همونطو رکه میدونید تابع NA() این مقدار رو تولید میکنه. پس کافیه که فرمول SUMIF رو با تابع IF و NA() ترکیب کنیم. به این صورت:

=IF( SUMIFS( Table4[فروش], Table4[ماه], E2)>0  ,SUMIFS( Table4[فروش], Table4[ماه], E2) , NA())

ترکیب تابع NA برای ایجاد خطای #N/A

شکل ۵- ترکیب تابع NA برای ایجاد خطای #N/A

نکته:
اگر بجای NA() از “” استفاده کنیم، باز هم نتیجه نمیگیریم. چون ظاهر سلول خالی نشون داده میشه و عملا داهل سلول پر هست و نمودار صفر در نظر میگیره و باز هم نموداری شبیه شکل ۴ نمایش خواهد داد. پس حتما باید از تابع NA() برای این کار استفاده کنیم.
  1. مرحله آخر هم رسم نمودار هست. کافیه داده ها رو انتخاب کنیم، و از تب Insert نمودار خطی رو انتخاب کنیم. بعد از انجام تنظیمات گرافیکی دلخواه، نموداری مشابه شکل ۶ خواهیم داشت.

اصلاح نمودار نمایش مجموع فروش با استفاده از تابع NA

شکل ۶- نمودار نمایش مجموع فروش به تفکیک ماه

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

نمایش دو قسمت دور از هم در یک شیت با استفاده از Split و تابع NA

در این مقاله به کاربرد خیلی مهم تابع NA() پرداختیم. این ترفند در رسم نمودارهای پویا، داشبوردسازی و … بسیار مهم و پرکاربرد هست.

کلیدواژه : تابع IFتابع SUMIFS
127

فارغ التحصیل لیسانس مهندسی صنایع، ارشد مدیریت صنعتی از دانشگاه تربیت مدرس و عاشق اکسل هستم. از سال 1388 که ترم 2 لیسانس بودم، به توصیه استاد مشاورم شروع به خوندن اکسل بصورت حرفه ای کردم و همچنان در حال مطالعه و یادگیری و البته آموزش به بقیه هستم.

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

توسط
تومان