نمودار داینامیک یا پویا در اکسل (Dynamic Chart)، نموداری است که با اضافه کردن، کم کردن یا هر نوع تغییر داده‌ها به‌صورت خودکار بروزرسانی می‌شود. رسم این نوع نمودار در تعداد داده‌های بالای جدول بسیار مفید است و دقت و سرعت گزارش‌گیری را افزایش می‌دهد. در تنظیم گزارش‌های مالی، بازاریابی و کمپین‌های دیجیتالی استفاده از نمودار داینامیک کاربرد زیادی دارد. در این مطلب از مجله فرادرس چهار روش رسم نمودار داینامیک در اکسل را با ابزارها و توابع مختلف این نرم‌افزار همراه مثال یاد می‌گیریم.

روش‌های رسم نمودار داینامیک در اکسل

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

1. ابزار Excel Table
2. ابزار Pivot Table
3. فرمول‌نویسی با تابع OFFSET و تابع INDEX
4. افزونه‌ Chart Expo

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

رسم نمودار داینامیک با ابزارهای اکسل

استفاده از بخش‌های مختلف نوار ابزار اکسل روشی ساده برای رسم نمودار داینامیک اکسل است. در این حالت دو ابزار «Excel Table» و «Pivot Table» جدول داده‌های اکسل را به حالت داینامیک در می‌آوریم.

ابزار Excel Table

یکی از ساده‌ترین روش‌های رسم نمودار داینامیک در اکسل استفاده از ابزار جدول یا (Excel Table) است. این روش برای گزارش‌های روزانه و ساده بسیار مناسب است، اما در تعداد زیاد داده‌ها تغییرات را کندتر از روش‌های دیگر انجام می‌دهد. همچنین در رسم نمودارهای پیشرفته مانند لیکرت یا چند محوره به تنهایی کافی نیست.

مثال

فرض می‌کنیم نموداری مانند تصویر زیر از میزان فروش یک فروشگاه در شش ماه اول سال را داریم.

حال اگر بخواهیم نمودار رسم شده برای نیمه اول سال با اضافه کردن مقدار فروش در ماه‌های بعدی به‌صورت خودکار تغییر کند، به ترتیب مراحل را انجام می‌دهیم.

• در زبانه «Insert» از نوار ابزار اکسل روی گزینه «Table» کلیک می‌کنیم.

• بعد از انتخاب سلول‌های مربوط به سرتیترهای جدول، یعنی ماه و تعداد فروش، در پنجره باز شده باکس مربوط به «My table has headers» را علامت می‌زنیم.

• بعد از این کار، تیترهای جدول به‌ شکل تصویر زیر در می‌آیند که نشانه داینامیک شدن جدول است. در این حالت با اضافه کردن مقدار فروش در ماه‌های بعد، نمودار رسم شده کاملا خودکار تغییر می‌کند.
  

  

ابزار Pivot Table

ایجاد جدول‌های محوری در اکسل یا Pivot Table، برای خلاصه‌سازی و دسته‌بندی داده‌ها با هدف آسان‌تر کردن تحلیل بهتر اطلاعات و گزارش‌گیری آسان‌تر انجام می‌شود. با ایجاد این جدول‌ها می‌توانیم نمودارهای داینامیک را نیز در اکسل رسم کنیم. استفاده از این جدول در رسم نمودار داینامیک حتی برای تعداد داده‌های بالا بسیار سریع است و هر تغییری در داده‌های اکسل بلافاصله روی نمودار بروزرسانی می‌شود. با این حال داینامیک کردن برخی نمودارهای خاص و پیشرفته مانند لیکرت یا نمودارهای کمبو را از این روش نمی‌توانیم انجام دهیم.

مثال

برای درک بهتر مطلب داده‌های مربوط به جدول اکسل قبلی را این بار با ابزار «Pivot Table» بروزرسانی می‌کنیم. مراحل انجام کار به شرح زیر است.

• ابتدا همه سطر و ستون‌های جدول فرضی تعداد فروش ماهیانه کالا را انتخاب می‌کنیم. سپس از زبانه «Insert» به بخش «Pivot Table» و گزینه «From Table/Range» می‌رویم.

• در پنجره باز شده، گزینه «New worksheet» را انتخاب می‌کنیم تا جدول در کاربرگ جدیدی باز شود. البته می‌توانیم گزینه دوم را هم انتخاب کنیم، اما امکان بی‌نظمی و در هم ریختگی جدول بالا می‌رود.

• بعد از کلیک روی دکمه «Ok» جدول جدیدی همانند تصویر ایجاد می‌شود که همان «Pivot Table» است. برای ادامه کار در این جدول تیک مربوط به تیترهای اصلی جدول یعنی باکس‌های «ماه» و «تعداد فروش» را می‌زنیم.

• از زبانه «Insert» و قسمت «Pivot chart» نمودار دلخواه خود را رسم می‌کنیم.

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

• در کاربرگ جدید روی یک سلول دلخواه از «Pivot Table» رسم شده کلیک می‌کنیم. سپس در نوار ابزار اکسل به بخش «PivotTable Analysis» و «Change Data Source» می‌رویم.

• بعد از کلیک روی این بخش، پنجره‌ای در کاربرگ اصلی باز می‌شود. برای بروز شدن جدول، یک بار دیگر همه داده‌ها را انتخاب می‌کنیم و دکمه «Ok» را می‌زنیم.

همان‌طور که در تصویر مشخص است، نمودار به شکل خودکار بروزرسانی می‌شود.

 رسم نمودار داینامیک در اکسل با فرمول‌نویسی

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

مثال

برای رسم نمودار داینامیک در اکسل با تابع OFFSET دو مرحله ایجاد محدوده داینامیک و رسم نمودار دلخواه را به‌ شکل جداگانه انجام می‌دهیم. در این مثال همان جدول داده‌های فروشگاه فرضی قبلی را استفاده می‌کنیم.

مرحله اول: ایجاد محدوده داینامیک در جدول

• در زبانه «Formula» به بخش «Name Manager» و «New» می‌رویم.

• بعد از کلیک رو قسمت «New» در پنجره باز شده کلیک می‌کنیم. سپس برای هر یک از محدوده اعداد با تعریف نام‌های مشخص، فرمول‌های مربوط را می‌نویسیم.

به این شکل که برای محدوده اعداد مربوط به ماه، بعد از تعریف کلمه «Month» در باکس «Name»، فرمول ترکیبی تابع OFFSET و تابع COUNTA به شکل زیر را در باکس «Refers to» می‌نویسیم.

=OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A:$A)-1, 1)

• بعد از اجرای فرمول، بار دیگر روی دکمه «New» کلیک می‌کنیم و در پنجره باز شده و باکس «Name» عبارت «Sales» و در باکس «Referes to» فرمول زیر را می‌نویسیم.

=OFFSET(Sheet1!$B$2, 0, 0, COUNTA(Sheet1!$B:$B)-1, 1)

در این مرحله بعد از کلیک روی دکمه «Ok» پنجره اصلی مربوط به داینامیک کردن سلول‌ها به شکل تصویر زیر در می‌آید.

مرحله دوم: رسم نمودار داینامیک

تا این بخش از فرمول‌نویسی، محدوده‌های داینامیک در جدول درست کرده‌ایم. بنابراین کافی است تا مطابق روش معمولی، نمودار دلخواه خود را از زبانه «Insert» و بخش «Chart» رسم کنیم. بعد از رسم نمودار دلخواه لازم است تا داده‌های مربوط به جدول را برای نمودار به شکل دستی تعریف کنیم.

• بعد از کلیک راست روی نمودار، گزینه «Select Data» را انتخاب می‌کنیم.

• داده‌های محور «x» و «y» را با کلیک روی بخش «Edit» به شکل جداگانه تعریف می‌کنیم. برای محور «x» در قسمت «Series value» عبارت =Sheet1!Month برای محور «y» عبارت =Sheet1!Sale را می‌نویسیم. البته اگر در مرحله رسم نمودار دکمه ماوس در یک سلول خالی به‌جز جدول قرار داشته باشد، نموداری رسم نمی‌شود و فقط با یک صفحه خالی روبرو می‌شویم. در این حالت، بعد از کلیک راست روی صفحه و انتخاب بخش «Select Data» از بخش «Add» به همین ترتیب بکس مربوط به «Series name» و «Series value» را تعریف می‌کنیم.

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

البته کار با تابع OFFSET هم نکات مخصوص به‌ خود را دارد و اگر سلول‌های جدول از لحاظ فرمت مشکلی داشته باشند، نمودار داینامیک به‌درستی رسم نمی‌شود. برای آشنایی بیشتر با این تابع، در مطلب زیر از مجله فرادرس آموزش کامل استفاده از آن را توضیح داده‌ایم.

سایر توابع مورد استفاده برای رسم نمودار داینامیک در اکسل

به‌جز تابع OFFSET توابع دیگری مانند تابع INDEX هم برای رسم نمودار داینامیک استفاده می‌شود. اما نحوه کاربرد و فرمول‌نویسی آن‌ها کمی متفاوت است. فرمول ترکیبی تابع INDEX برای کاربرگ فرضی «Sheet1» و ستون A در جدول اکسل به شرح زیر است.

=Sheet1!$A$1:INDEX(Sheet1!$A:$A, COUNTA(Sheet1!$A:$A))

این نوع فرمول‌نویسی با توجه به سرعت بالاتر و کم‌خطا بودن آن برای تعداد بالای داده‌ها بسیار مناسب است.

همچنین تابع OFFSET در ترکیب با تابع MATCH نیز برای داینامیک کردن محدوده جدول تا یک سلول خاص استفاده می‌شود. فرمول نمونه برای کاربرگ فرضی «Sheet1» و داده‌های موجود جدول در ستون A به شکل زیر است.

=OFFSET(Sheet1!$A$1, 0, 0, MATCH("End", Sheet1!$A:$A, 0), 1)

در این حالت برای تعیین سلول پایینی لازم است کلمه «End» را به‌صورت دستی در پایان محدوده داینامیک کردن سلول‌ها در جدول بنویسیم. در مطلب زیر کار با تابع MATCH را به‌صورت کامل توضیح داده‌ایم.

یادگیری ترفندهای حرفه‌ای رسم نمودار با اکسل در فرادرس

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

همچنین در دو مجموعه فیلم آموزش زیر امکان انتخاب درس‌های بیشتر بر حسب نیاز و علاقه‌مندی وجود دارد.

رسم نمودار داینامیک با افزونه‌های اکسل

«Chart Expo» یک افزونه غیررایگان و حرفه‌ای در نرم‌افزار اکسل است که با آن می‌توانیم بیش از ۵۰ نوع نمودار تعاملی و پیشرفته‌ را بدون نیاز به دانش برنامه‌نویسی یا فرمول‌نویسی رسم کنیم. البته این افزونه یک اشتراک هفت روزه رایگان را نیز در اختیار کاربران قرار می‌دهد. مراحل رسم نمودار داینامیک در اکسل با «Chart Expo» بسیار ساده است.

برای رسم نمودار داینامیک با «ChartExpo» ابتدا لازم است این افزونه را در نرم‌افزار خود نصب کنیم. مراحل نصب به شرح زیر است.

1. ورود به زبانه Developer
2. کلیک روی بخش Add-ins
3. ورود به بخش Store
4. جستجوی عبارت Chart Expo
5. کلیک روی دکمه Add

معمولا افزونه «ChartExpo» در رسم نمودارهای داینامیک پیشرفته‌ای مانند نمودارهای چند محوره خطی (Multi Axis Line Chart)، نمودار سانکی (Sankey Chart)، نمودار لیکرت (Likert Chart)، نمودار میله‌ای مقایسه‌ای (Comparison Bar Chart) و نمودار دایره‌ای (Circular Org chart) کاربرد دارد. برای درک بهتر مطلب، یک مثال از نحوه استفاده این افزونه در رسم نمودار داینامیک چند محوره خطی را بررسی می‌کنیم.

مثال

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

برای رسم نمودار داینامیک چند محوره خطی با افزونه «Chart Expo» بعد از جستجوی عبارت «Multi Axis Line Chart» در افزونه، روی باکس مربوط کلیک می‌کنیم.

بعد از انتخاب جدول و کلیک روی دکمه «Create Chart from Selection» نمودار چند محوره را رسم می‌کنیم.

در این مرحله نحوه نمایش نمودار از بخش «Data Representation» به شکل دلخواه در فرمت میله‌ای یا خطی بر حسب متغیرهای مختلف تعداد دنبال‌کنندگان (فالوئر‌ها) و نرخ تعامل قابل تغییر است.

همچنین می‌توانیم تنظیمات دلخواه مانند اضافه کردن تیتر، تغییر فونت، اضافه کردن علامت درصد به محور «x» از بخش «Edit Chart» انجام دهیم.

بعد از تکمیل مراحل و انجام تنظیمات دلخواه، نمودار مثال فرضی به شکل زیر در می‌آید.

فواید و چالش‌های استفاده از نمودار‌های داینامیک

داینامیک کردن نمودارها در اکسل تا ۴۰ درصد زمان تهیه گزارش را کاهش می‌دهد. به‌همین دلیل بسیاری از کسب و کارهای بزرگ در تنظیم گزارش‌های مالی یا استارت‌آپ‌ها در کمپین‌های دیجیتال از آن استفاده می‌کنند. رسم نمودار داینامیک در اکسل علاوه بر فواید بسیار خود چالش‌هایی نیز دارد که با افزایش مهارت و آموزش درست می‌توان مشکلات اندک آن را نیز برطرف کرد.

فواید استفاده از نمودار داینامیک در اکسل

علاوه بر بروزرسانی خودکار با تغییر داده‌ها، سایر فواید نمودار داینامیک به شرح زیر هستند.

• نظارت بهتر بر زمان‌بندی و بودجه کمپین‌های تبلیغاتی در تیم‌های بازاریابی
• سازگاری با انواع داده‌ها و انعطاف‌پذیری مناسب
• ردیابی آسان داده‌های متغیر
• کاهش خطای ناشی از بروزرسانی دستی نمودار
• آسان‌تر کردن تحلیل داده‌ها و بررسی روندها
• نمایش حرفه‌ای و شفاف داده‌ها
• افزایش همکاری تیمی، بهبود ارتباطات و تصمیم‌گیری بهتر

چالش‌های کار با نمودار داینامیک در اکسل

نمودارهای داینامیک یکی از ابزارهای تصویری پیشرفته در اکسل هستند که کار با آن‌ها ممکن است در برخی موارد مشکلاتی را ایجاد کند. مهم‌ترین چالش‌های کار با این نمودارها به شرح زیر هستند.

• دشواری رسم آن‌ها برای افراد مبتدی
• احتمال ایجاد خطا در صورت فرمول‌نویسی نادرست
• امکان سازگار نبودن با نسخه‌های قدیمی نرم‌افزار اکسل
• احتمال کاهش سرعت رسم نمودار توسط اکسل در تعداد داده‌های بالا

جمع‌بندی پایانی

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