چطور در اکسل نمودار دینامیک رسم کنیم؟

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

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

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

ابتدا براساس داده‌های خام فهرست Drop-Down ایجاد می‌کنید که با انتخاب هر ماه میزان فروش هر دستگاه را نمایش دهد. روش‌های مختلفی برای ایجاد چنین فهرستی وجود دارد؛ اما بهترین روش افزودن جدولی جداگانه برای فراخوانی داده‌های نمودار از داده‌های خام است که ازطریق انتخاب ماه در فهرست آبشاری انجام می‌شود.

استفاده از جدول Data Prep. Table امکان کنترل و مدیریت بهتر داده‌ها و افزودن آسان ویژگی‌های دیگر به نمودار را فراهم می‌کند. علاوه‌براین، اگر مشکلی در نمودار ایجاد شود، معمولاً مربوط به جدول Data Prep می‌شود و بدین‌ترتیب، تشخیص آن آسان‌تر است.

• سلولی که می‌خواهید فهرست آبشاری در آن قرار بگیرد، انتخاب کنید.
• از زبانه‌های بالا روی Data بزنید و در گزینه‌های نوار Ribbon روی Data Validation کلیک کنید.

• در پنجره بازشده در منو آبشاری بخش Allow گزینه List را انتخاب کنید.
• یک‌ بار در کادر قسمت Source کلیک کنید و سپس نشانگر ماوس را روی ردیف ماه‌ها ببرید و انتخاب کنید. بدین‌ترتیب داده‌های مربوط به فهرست Drop-Down مشخص می‌شوند.
• درنهایت روی دکمه Apply بزنید.

• فهرست آبشاری براساس ردیف ماه‌ها ایجاد شده است و می‌توانید در زبانه Home در بخش Cell Style برای نمایش بهتر آن رنگ سلول را تغییر دهید.

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

• ستون نام محصولات را انتخاب کنید و با فشردن کلیدهای ترکیبی میان‌بر Ctrl+C کپی و در جایی که می‌خواهید جدول (۲) قرار بگیرد، پیست (Ctrl+V) کنید.

• داده‌هایی که تازه پیست کرده‌اید، انتخاب و روی آن کلیک‌راست کنید.
• از منو بازشده روی Sort بروید و از گزینه‌های آن Sort A To Z را انتخاب کنید تا براساس حروف الفبا مرتب شوند.

اکنون باید به‌ جدول (۲) نشان دهید که کاربر کدام ماه را انتخاب کرده است تا براساس آن داده‌ها را نمایش دهد.

• سلولی که می‌خواهید نام ماه را نمایش دهد، انتخاب کنید.
• درون سلول علامت مساوی (=) را تایپ کنید و در همین حالت روی سلولی که فهرست آبشاری را ایجاد کرده‌اید، کلیک کنید. در مثال ما، نام ماه در سلول Q4 نمایش داده خواهد شد و درون آن = را تایپ و روی سلول B11 کلیک کردیم.
• کلید Enter را فشار دهید تا تغییر اعمال شوند. اکنون باید سلول مدنظر، نام ماه مشخص‌شده در فهرست آبشاری را نمایش دهد.

اکنون برای استخراج داده براساس نام ماه و نام محصول از جدول (۱) و نمایش آن در جدول (۲) از ترکیب توابع INDEX و MATCH در اکسل کمک بگیرید.

• روی سلول مقابل اولین محصول کلیک کنید.
• مانند تصویر = و بعد از آن )index را تایپ کنید.

• بدون اینکه دکمه‌ای را بزنید با ماوس تمام اعداد جدول (۱) را انتخاب کنید.

• سپس دکمه F4 را از روی کیبورد بزنید تا فرمول تا به‌ اینجای کار تثبیت شود.

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

• ستون اسامی محصولات را با ماوس انتخاب کنید.
• مجدداً کلید F4 را از روی کیبورد فشار دهید تا مقادیر ثابت شود.
• دوباره یک ویرگول بگذارید و عدد صفر را تایپ کنید و پرانتز را ببندید.

این فرمول امکان جست‌وجوی دوطرفه در ماتریکس را فراهم می‌کند. تا اینجای کار محصول Android Phone را در ردیف‌ها جست‌وجو کردید و حالا باید فرمول جست‌وجوی ماه را وارد کنید.

• ادامه فرمول را مجدداً یک ویرگول بگذارید و )match را تایپ کنید.
• روی نام ماه در ستون دوم جدول (۲) کلیک کنید تا کد سلول آن اضافه شود و سپس کلید F4 را فشار دهید، دقت کنید که شکل کد سلول از حالت Q4 تغییر می‌کند.

• یک ویرگول تایپ کنید. حالا باید محدوده جست‌وجوی نام ماه را با انتخاب ردیف نام‌ ماه‌ها در جدول (۱) مشخص کنید.

• برای تثبیت دوباره کلید F4 را فشار دهید و یک ویرگول و صفر تایپ کنید و پرانتز‌ها را ببندید.

• عدد متناسب با محصول در ماه ژانویه نمایش داده می‌شود. نشانگر ماوس را در گوشه سلول ببرید و وقتی به‌شکل به‌علاوه کشیده و مشکی تبدیل شد، کلیک‌چپ ماوس را نگه دارید و به‌سمت پایین بکشید.
• داده‌های سایر محصولات نیز باید نمایش داده شوند.

برای رسم نمودار در اکسل مانند انواع نمودار به زبانه Insert و بخش Chart مراجعه کنید.

• داده‌های جدول (۲) را انتخاب کنید.
• وارد زبانه Insert شوید.
• از بخش Charts نمودار دلخواه را انتخاب کنید. ما نمودار Column و نمایش دوبُعدی را انتخاب کردیم.

می‌توانید برای تغییر ظاهر نمودار و شخصی‌سازی آن مقاله آموزش رسم نمودار در اکسل را مطالعه کنید.

برای حرفه‌ای‌ترشدن نمودار، پیشنهاد می‌کنیم خط میانگین را به نمودار اضافه کنید.

• بعد از ستون داده‌ها در جدول (۲)، ستونی را با نام Average یا هر نام دلخواهی اضافه کنید.
• در ردیف مقابل محصول اول جدول فرمول میانگین را تایپ کنید. علامت = بگذارید و average را تایپ کنید و ازبین گزینه‌های به‌نمایش درآمده روی تابع میانگین کلیک کنید.

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

• حالا از گوشه سمت راست سلول با ماوس نگه دارید و به پایین بکشید تا فرمول روی همه سلول‌ها اعمال شود.

• روی نمودار کلیک کنید تا داده‌های مرتبط با آن در جدول (۲) هایلایت شوند.
• با ماوس از گوشه جدول بگیرید و ستون میانگین (Average) را نیز انتخاب کنید.

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

• روی یکی از ستون‌های نمودار میانگین کلیک‌راست کنید.
• گزینه Format را انتخاب کنید تا ابزار آن در سمت راست باز شود.

• در زبانه Data روی Average بزنید.
• سپس روی Clustered Column بزنید.
• حالا روی Line کلیک کنید.

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

• روی برچسب اعداد یک‌ بار کلیک کنید.
• اکنون کلیک‌راست و در نوار ابزار روی Fill کلیک کنید.
• رنگ سفید را انتخاب کنید.

تصویر نهایی نمودار بدین‌ترتیب می‌شود و خط میانگین با اعداد تداخلی نخواهد داشت.

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