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

یک‌شنبه ۳ تیر ۱۴۰۳ - ۱۷:۳۰
مطالعه 12 دقیقه
نمودار ستونی و خطی سه‌بعدی و لوگوی اکسل روی پس‌زمینه سبز
نمودار دینامیک در اکسل به‌شما امکان می‌دهد یک نمودار برای مقادیر مختلف داشته باشید که با انتخاب یک گزینه، داده‌های دلخواه را در قالب نمودار نمایش دهد.
تبلیغات

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

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

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

مرحله اول: ایجاد فهرست آبشاری

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

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

محیط اکسل در مایکروسافت ۳۶۵
  • سلولی که می‌خواهید فهرست آبشاری در آن قرار بگیرد، انتخاب کنید.
  • از زبانه‌های بالا روی Data بزنید و در گزینه‌های نوار Ribbon روی Data Validation کلیک کنید.
افزودن Data Validation در اکسل
  • در پنجره بازشده در منو آبشاری بخش Allow گزینه List را انتخاب کنید.
  • یک‌ بار در کادر قسمت Source کلیک کنید و سپس نشانگر ماوس را روی ردیف ماه‌ها ببرید و انتخاب کنید. بدین‌ترتیب داده‌های مربوط به فهرست Drop-Down مشخص می‌شوند.
  • درنهایت روی دکمه Apply بزنید.
محیط اکسل در مایکروسافت 365
شکل نهایی فهرست آبشاری
  • فهرست آبشاری براساس ردیف ماه‌ها ایجاد شده است و می‌توانید در زبانه Home در بخش Cell Style برای نمایش بهتر آن رنگ سلول را تغییر دهید.

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

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

ازاین‌پس جدول داده‌های خام را جدول (۱) و جدول فراخوانی داده‌ها را جدول (۲) می‌نامیم.

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

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

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

می‌توانید با انتخاب نام ماه‌های مختلف مطمئن شوید که نام سلول جدول (۲) نیز تغییر می‌کند.

مرحله سوم: استخراج داده‌ها از جدول (۱) به جدول (۲)

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

استخراج داده از جدول خام
  • روی سلول مقابل اولین محصول کلیک کنید.
  • مانند تصویر = و بعد از آن )index را تایپ کنید.
انتخاب داده برای افزودن به تابع در اکسل
  • بدون اینکه دکمه‌ای را بزنید با ماوس تمام اعداد جدول (۱) را انتخاب کنید.
تثبیت تابع با کلید F4 در اکسل
  • سپس دکمه F4 را از روی کیبورد بزنید تا فرمول تا به‌ اینجای کار تثبیت شود.
افزودن تابع Match در سلول اکسل
  • حالا برای ادامه فراخوانی داده موردنیاز، یک ویرگول درادامه فرمول بزنید و کلمه )match را تایپ کنید.
  • سپس روی نام محصول در ستون کناری بزنید تا کد سلول آن درج شود و بعد از آن مجدداً یک ویرگول تایپ کنید.
انتخاب داده برای تابع در اکسل
  • ستون اسامی محصولات را با ماوس انتخاب کنید.
  • مجدداً کلید F4 را از روی کیبورد فشار دهید تا مقادیر ثابت شود.
  • دوباره یک ویرگول بگذارید و عدد صفر را تایپ کنید و پرانتز را ببندید.

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

افوزدن مقادیر در تعریف تابع در اکسل
  • ادامه فرمول را مجدداً یک ویرگول بگذارید و )match را تایپ کنید.
  • روی نام ماه در ستون دوم جدول (۲) کلیک کنید تا کد سلول آن اضافه شود و سپس کلید F4 را فشار دهید، دقت کنید که شکل کد سلول از حالت Q4 تغییر می‌کند.
انتخاب داده در تعریف تابع در اکسل
  • یک ویرگول تایپ کنید. حالا باید محدوده جست‌وجوی نام ماه را با انتخاب ردیف نام‌ ماه‌ها در جدول (۱) مشخص کنید.
تکمیل تابع در سلول اکسل
  • برای تثبیت دوباره کلید F4 را فشار دهید و یک ویرگول و صفر تایپ کنید و پرانتز‌ها را ببندید.
افزودن تابع یک سلول به سلول‌های دیگر در اکسل
  • عدد متناسب با محصول در ماه ژانویه نمایش داده می‌شود. نشانگر ماوس را در گوشه سلول ببرید و وقتی به‌شکل به‌علاوه کشیده و مشکی تبدیل شد، کلیک‌چپ ماوس را نگه دارید و به‌سمت پایین بکشید.
  • داده‌های سایر محصولات نیز باید نمایش داده شوند.
محیط اکسل در مایکروسافت ۳۶۵ در مرورگر اج
داده‌های فراخوانده‌شده از جدول (۱) و نمایش در جدول (۲)

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

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

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

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

مرحله پنجم: افزودن خط میانگین

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

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

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

افزودن نمودار خطی میانگین به نمودار اصلی
  • روی یکی از ستون‌های نمودار میانگین کلیک‌راست کنید.
  • گزینه Format را انتخاب کنید تا ابزار آن در سمت راست باز شود.
افزودن نمودار خطی میانگین به نمودار ستونی در اکسل
  • در زبانه Data روی Average بزنید.
  • سپس روی Clustered Column بزنید.
  • حالا روی Line کلیک کنید.
تبدیل نمودار ستونی میانگین به نمودار خطی در اکسل
شکل نهایی نمودار میانگین به‌صورت خطی

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

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

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

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

تبلیغات
داغ‌ترین مطالب روز
تبلیغات
تبلیغات

نظرات