ایجاد نمودار داینامیک در اکسل

ایجاد نمودار داینامیک در اکسل

ایجاد نمودار داینامیک در اکسل

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

[private role=specialsubscriber]برای اینکه بهتر متوجه بشین نمودار داینامیک چه نموداری هست جدول زیر رو در نظر بگیرید:

میزان فروش
228
137
461
456
500
227
222
396
330

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

ایجاد نمودار داینامیک در اکسل

حالا بخشی از داده های جدول رو پاک کنیم و جدول رو به شکل زیر در میاریم.

میزان فروش
228
137
461
456
500

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

ایجاد نمودار داینامیک در اکسل

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

میزان فروش
228
137
461
456
500
227
222
396
330
570
365
417

نتیجه نمودار به شکل زیر هست.

ایجاد نمودار داینامیک در اکسل

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

حالت اول (کم شدن اطلاعات جدول):

ایجاد نمودار داینامیک در اکسل

حالت دوم (زیاد شدن اطلاعات جدول):

ایجاد نمودار داینامیک در اکسل

برای رسم نمودار داینامیک مثال ما باید با توابع COUNT و OFFSET آشنا باشید. پس اگر با این توابع آشنایی ندارید بهتره اول مطالب مربوط به این توابع رو مطالعه کنید. خب بریم سراغ رسم نمودار. ابتدا ببینیم در حال حاضر نمودار ما بر اساس چه داده هایی داره رسم میشه. برای اینکار نموداری که رسم کردیم رو انتخاب کنید و مثل تصویر زیر از تب Design روی گزینه Select Data کلیک میکنیم:

ایجاد نمودار داینامیک در اکسل

با این کار پنجره زیر باز میشه:

ایجاد نمودار داینامیک در اکسل

حالا روی گزینه Edit  کلیک میکنیم تا پنجره زیر باز شه:

ایجاد نمودار داینامیک در اکسل

همونطور که در این پنجره مشاهده میکنید مقدار Series values برابر با =Sheet1!$A$2:$A$10 هست که یک مقدار ثابت هست. برای اینکه نمودار ما حالت داینامیک پیدا کنه باید این مقدار رو با یک فرمول متغیر به شکل زیر جایگزین کنیم.

ابتدا مثل تصویر زیر از تب Formula رو گزینه Name Manager کلیک میکنیم.

ایجاد نمودار داینامیک در اکسل

با این کار پنجره زیر باز میشه. از این پنجره برای ایجاد Name ها استفاده میشه. با استفاده از Name ها میتونیم به یک فرمول نامی تخصیص بدیم و در بخش های مختلف اکسل به جای نوشتن اون فرمول طولانی فقط نامی رو که تعریف کردیم بنویسیم. برای ایجاد یک نام جدید روی گزینه New کلیک میکنیم.

ایجاد نمودار داینامیک در اکسل

با کلیک روی دکمه New پنجره زیر باز میشه:

ایجاد نمودار داینامیک در اکسل

Name: در این قسمت نامی که میخوایم استفاده کنیم رو مینویسیم. من در اینجا نام sale رو انتخاب کردم. شما میتونید هر نام دیگه ای رو انتخاب کنید.

Scope: این بخش مشخص میکنه که این نام در کدوم قسمت های اکسل قابل دسترس باشه. از اونجایی که من میخوام از این نام فقط در Sheet1 استفاده کنم این گزینه رو برابر Sheet1 قرار میدم.

Refers to: این بخش مشخص میکنه که هر جا من در فرمول های اکسل عبارت sale رو نوشتم این عبارت با چه چیزی جایگزین بشه. در اینجا عبارت متغیر =OFFSET(Sheet1!$A$2;0;0;COUNT(Sheet1!$A:$A);1) رو مینویسیم که به جای عبارت ثابت =Sheet1!$A$2:$A$10 از اون استفاده خواهیم کرد. با هم بخش های مختلف این عبارت رو در ادامه بررسی خواهیم کرد.

Sheet1!$A$2: این پارامتر مشخص میکنه که محدوده متغیر ما از کدوم سلول شروع میشه.

COUNT(Sheet1!$A:$A): این پارامتر مشخص میکنه که ارتفاع محدوده متغیر ما باید چقدر باشه.از اونجایی که تابع Count تعداد اعداد موجود در ستون A رو شمارش میکنه اگر ما از اعدادمون کم کنیم یا به انتهای اعدادمون عددی رو اضافه کنیم ارتفاع محدوده ما به صورت خودکار تغییر میکنه. در واقع این قسمت از تابع OFFSET ما باعث میشه که محدوده ما داینامیک و متغیر باشه. در واقع محدوده ما از سلول A2 شروع میشه و به تعداد نتیجه تابع Count ادامه پیدا میکنه.

1: این پارامتر هم عرض محدوده ما رو مشخص میکنه. از اونجایی که داده های ما کلا در یک ستون هستند این عبارت رو به صورت ثابت و برابر با یک تعیین میکنیم تا محدوده ما همیشه عرضش برابر یک باشه.

تصویر زیر تاثیر بخش های مختلف این فرمول رو در تعیین محدوده متغیر ما نشون میده:

ایجاد نمودار داینامیک در اکسل

حالا برای جایگزین کردن فرمول متغییرمون با فرمول ثابت در نمودار، دوباره از بخش Select Data و انتخاب گزینه Edit وارد پنجره Edit Series میشیم و مثل تصویر زیر در بخش Series Vlaues به جای عبارت =Sheet1!$A$2:$A$10 عبارت =Sheet1!sale رو جایگزین میکنیم.

ایجاد نمودار داینامیک در اکسل

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

دانلود فایل:

DynamicChart.sample.worksheet.ir

[/private]

56 Comments
  • پرویز
    Posted at 20:30h, 17 ژانویه پاسخ

    سلام
    نمیدونم این آموزش مخصوص چه نسخه ای از اکسل هست ولی ایرادهای تایپی و فنی داره
    این عبارت باید با کُما نوشته بشه و نه سیمیکالُن
    عبارت متغیر =OFFSET(Sheet1!$A$2;0;0;COUNT(Sheet1!$A:$A);1)

    Scope هم باید گزینه Workbook انتخاب بشه و نه Sheet1

    در تعین بخش محدوده برای نمودار هم نباید از عبارت Sheet1 استفاده بشه بلکه باید اسم فایل اصلی گذاشته بشه

    در تعین بخش محدوده برای نمودارعبارت xlsx. هم فراموش شده

  • mahta
    Posted at 02:23h, 30 نوامبر پاسخ

    سلام وقتتون بخیر. سوالی خدمتتون داشتم. مثلا فایل اکسل ما ۱۵ سطر دارد و ما همیشه روی سلولهای سطر آخر عملیات ریاضی انجام میدهیم مثلا L2=D15+E15 یا M2=A15/B15 چطور میتوان با افزایش سطر ها محتوای این سلولها داینامیک تغییر کند . به این ترتیب : L2=D16+E16 یا M2=A16/B16

    • یاسین اسماعیل پور
      Posted at 16:40h, 02 دسامبر پاسخ

      سلام مهتا جان. برای اینکار میتونی ابتدا با تایع COUNT تعداد سطرها رو بشماری و بعد با تابع OFFSET رفرنس رو تغییر بدی:
      =offset(a1,count(a:a,0)

      • mahta
        Posted at 20:32h, 07 دسامبر پاسخ

        ممنون از شما.

        • یاسین اسماعیل پور
          Posted at 22:04h, 07 دسامبر پاسخ

          خواهش میکنم

  • مینا
    Posted at 11:50h, 31 مارس پاسخ

    با عرض سلام و وقت بخیر
    من وقتی رنج داده هامو در بخش series values وارد می کنم داده ها به شکل صفر نمایش داده می شوند و مقادیر اصلی داده ها نشان داده نمی شود و در نتیجه نمودار من هیچ میله ای را نشان نمی دهد و صفحه نمودار خالی نمایش داده میشه، لطفا راهنمایی کنید.
    سپاس

  • سمانه
    Posted at 09:42h, 02 نوامبر پاسخ

    با سلام ببخشی یه سوال داشتم و ان اینکه برا رسم نمودار من برای محور x اعداد 0.5 و 1 و 1.5 و 2 رو وارد کردم ولی نمودار من اعداد رو از 1 تا 4 نشون میده چطور میتونم ان رو درست کنم

    • محمد قاسم خانیکی
      Posted at 00:01h, 03 نوامبر پاسخ

      باسلام سمانه خانم
      من گمان می کنم که شما نوع نمودارتون رو بدرستی انتخاب نکردید
      در اکثر نمودارهای اکسل شما فقط می تونید یک ستون از اعداد رو داشته باشید
      و در محور دیگه فقط نام ستون ها و یا شماره اونها ذکر میشه
      اگه تمایل داشته باشین که یک نمودار دو بعدی داشته باشین که در هر دو بعد بتونید عدد بدین
      بایستی از نمودارهایی مثل X Y ( Scatter ) استفاده کنین
      موفق باشین – خانیکی

  • mostafa.salamat63
    Posted at 12:05h, 18 می پاسخ

    سلام. جناب اگر یک سلول که اطلاعاتش از طریق یک لینک وارد میشه و دائما تغییر می کنه رو چجوری میشه روی نمودار پیاده کرد. مثلا فرض کنید اگر اطلاعات یک سلول در هر دقیقه اعداد 1و2و5و6و8و9و… باشه رو چجوری میتونم براساس زمان روی نمودار پیاده سازی کنم .ممنون

    • یاسین اسماعیل پور
      Posted at 17:54h, 18 می پاسخ

      سلام مصطفی جان. منظور شما اینه که هر بار که مقدار سلول تغییر میکنه history یه جا ذخیره بشه و بعد به صورت چارت نمایش داده بشه؟

      • رهااااا
        Posted at 10:12h, 20 آگوست پاسخ

        سلااام امکانش هست چگونگی رسم نمودار شبیه سازی شده رو بگید
        یعنی هر چی متغییر رو تغیر بدیم بازم همون نمودار رو بهمون بده

  • عرفان
    Posted at 02:13h, 26 فوریه پاسخ

    سلام من تو اکسل وقتی insert chart رو میزنم یه صفحه سفید فقط باز میشه و نمودار نمایش داده نمیشه علت چیه؟

  • Vahid-Ghafari
    Posted at 14:31h, 23 ژانویه پاسخ

    با سلام
    این روش یک ایراد دارد اگر کابر به اشتباه یک خانه خالی گذاشته شود یا پس از ورود اطلاعات ، اطلاعات یکی از سلول ها را حذف نمایید.
    به تعدادی سلول خالی بین مقادیر از اخرین اطلاعات را در نظر نمی گیرد و در نمودار لحاظ نمی کند

  • احمد
    Posted at 21:57h, 13 دسامبر پاسخ

    سلام.من یک فایل اکسل دارم شامل چندین ستون.تعداد سطها تقریبا 40 هزار ردیف است.من چندین نمودار ترسیم کرده ام که با فیلتر کردن هر ستون نمودارها تغییر می کنند.مشکل من اینه که add data label را برای همه نمودارها فعال کردم ولی هنگامی که دیتاها را فیلتر میکنم و نمودارهای جدید بطور اتوماتیک رسم میشه data label پاک میشه و باید مجدد add data label را فعال کنم.آیا روشی وجود دارد که برای اولین بار که data labelرا فعال کنم با تغییر دیتاها و فیلتر کردن نمودار جدید نیاز به فعال کردن دوباره add data label نباشه؟
    ممنون اگه بتونید راهنمایی کنید.

    • Vahid-Ghafari
      Posted at 13:38h, 23 ژانویه پاسخ

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

  • Ahmad_k2
    Posted at 23:35h, 30 اکتبر پاسخ

    سلام میخواستم بدونم که اگر ما ده تا اسم در یک ستون داشته باشیم و برای هریک یک شیت باز شود و وقتی که ما ده تا را یکی بهش اضافه کنیم خودکار یک شیت باز شود

    اگه میشه راهنمایی کنید

    • یاسین اسماعیل پور
      Posted at 19:23h, 02 نوامبر پاسخ

      سلام. برای اینکار باید از vba استفاده کنید و راه ساده ای وجود نداره که بشه در یک کامنت توضیح داد

  • Soroush
    Posted at 08:57h, 18 می پاسخ

    سلام. فرض کنید که یک ستون با 10 داده داریم و حال داده 11 را به آن اضافه می کنیم. آیا راهی برای ایجاد یک نمودار پویا که با اضافه کردن داده 11، به صورت خودکار داده اول را حذف و داده یازدهم را در بر بگیرد، وجود دارد؟ به عبارت دیگر من به دنبال ایجاد یک نمودار پویا هستم که با اضافه کردن هر داده جدید، یکی از داده های قبلی را حدف و به جای آن داده جدید را در بر بگیرد؟

    • یاسین اسماعیل پور
      Posted at 09:24h, 18 می پاسخ

      بله امکانش هست با همین تکنیک میشه اینکار رو انجام داد

      • Soroush
        Posted at 11:14h, 19 ژوئن پاسخ

        از راهنمایی تان سپاسگذارم. با همین روش راه حل را پیدا کردم. از آن جا که فکر کردم ممکن است این راه حل برای بقیه مخاطبانتان هم مفید باشد، آن را بیان می کنم: برای این کار باید فرمول را به صورت زیر بنویسیم
        (OFFSET(Sheet1!$A$2; COUNT(Sheet1!$A:$A)-10;0;10;1=
        فقط یک سوال دیگر در مورد نام گذاری داشتم. آیا مطلب آموزشی در مورد نام گذاری دارید که در قسمت نظرات آنجا بتوانم سوالم را مطرح کنم؟

        • یاسین اسماعیل پور
          Posted at 18:45h, 21 ژوئن پاسخ

          ممنون از به اشتراک گذاری اطلاعات مفیدتون. خیر هنوز مطلبی در خصوص نام گذاری ننوشتم

  • amir ghorbani
    Posted at 14:40h, 02 سپتامبر پاسخ

    جناب اسماعیل پور من امروز log in شدم نمیتونم مطالب رو ببینم .

    • یاسین اسماعیل پور
      Posted at 09:23h, 18 می پاسخ

      سلام
      بررسی کنید مجدد باید حل شده باشه

  • amir ghorbani
    Posted at 08:41h, 30 آگوست پاسخ

    سلام جناب اسماعیل پور . بنده هم عضو ویژه شدم ولی نمیتونم مطالب رو ببینم . ممنون

    • amir ghorbani
      Posted at 08:42h, 30 آگوست پاسخ

      ok شد ممنون .

  • javad.kh
    Posted at 10:26h, 10 مارس پاسخ

    سلام ، خسته نباشید. من عضو شدم و اکانت 1 ماهه خریدم و عضو ویژه هستم روز اول دسترسی داشتم اما الان نمیتونم دسترسی داشته باشم لطفا راهنماییم کنید.
    با تشکر.

    • یاسین اسماعیل پور
      Posted at 10:51h, 15 مارس پاسخ

      سلام دوست عزیز. ضمن عذرخواهی بابت مشکل پیش اومده اکانتتون به مدت 6 ماه تمدید شد. لطفا مجدد بررسی کنید

  • Behin
    Posted at 14:17h, 15 فوریه پاسخ

    با سلام من اشتراک یک ماهه دارم پس چرا نمودار داینامیک باز نمیشه؟

  • f7062
    Posted at 14:19h, 08 فوریه پاسخ

    با سلام من اشتراک یک ماهه دارم پس چرا نمودار داینامیک باز نمیشه؟باتشکر

  • nasroallah.akbari
    Posted at 10:49h, 09 ژانویه پاسخ

    سلام ، خسته نباشید. من عضو شدم و اکانت 1 ماهه خریدم و عضو ویژه هستم روز اول دسترسی داشتم اما الان نمیتونم دسترسی داشته باشم .با تشکر.

    • یاسین اسماعیل پور
      Posted at 09:36h, 14 ژانویه پاسخ

      سلام دوست عزیز. ضمن عذرخواهی مشکل برطرف شد و برای شما شش ماه عضویت ویژه فعال شد

  • leila
    Posted at 15:58h, 24 دسامبر پاسخ

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

  • mvahabi
    Posted at 00:17h, 10 دسامبر پاسخ

    یه مشکل دیگه هم دارم استاد یاسین.combobox ایجاد شده ظاهرا پارمترها رو به صورت ستونی دریافت میکنه و نه عمودی درسته؟

    • یاسین اسماعیل پور
      Posted at 17:49h, 10 دسامبر پاسخ

      نه میشه عمودی هم تعریف کرد

  • mvahabi
    Posted at 16:48h, 09 دسامبر پاسخ

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

    • یاسین اسماعیل پور
      Posted at 19:22h, 09 دسامبر پاسخ

      سلام. بله امکانش وجود داره در اکسل

      • mvahabi
        Posted at 00:12h, 10 دسامبر پاسخ

        چطوری میتونم ازتون راهنمایی بگیرم؟

        • یاسین اسماعیل پور
          Posted at 17:48h, 10 دسامبر پاسخ

          سلام فایلتون رو به همراه توضیحات کامل از طریق فرم تماس ارسال کنید

  • shiva
    Posted at 14:44h, 24 اکتبر پاسخ

    من که عضو شدم پس چرا نشون نمیده مطالبو

    • یاسین اسماعیل پور
      Posted at 14:02h, 31 اکتبر پاسخ

      سلام شیوا جان. این مطلب مخصوص اعضای ویژه هست

  • shaahinz
    Posted at 01:07h, 01 جولای پاسخ

    آقا یاسین سلام! من مبلغ مربوطه جهت عضویت ویژه رو پرداخت کردم اما متاسفانه دسترسی به بخشهای مربوطه شو ندارم

  • Rahaa
    Posted at 10:46h, 24 می پاسخ

    با سلام
    چرا من نمیتونم این مطلب رو ببینم؟!

    • آموزش ماکرو نویسی در اینجا
      Posted at 15:58h, 24 می پاسخ

      سلام ، رها خانم – عضویت شما رو در این سایت بهتون تبریک و خوشامد میگم ، شما همه مطالب را باید ببینید!!
      کدوم مطلب رو نمیتونید ببینید؟!!
      خانیکی

      • shaahinz
        Posted at 10:58h, 04 جولای پاسخ

        salam manam moshkel vorod be safahate vizhe ro daram

        • یاسین اسماعیل پور
          Posted at 13:39h, 04 جولای پاسخ

          سلام شاهین جان. لطفا یوزر و پسوردت رو از طریق فرم تماس برام بفرست بررسی مبکنم. ممنون

          • سپهر
            Posted at 13:36h, 19 جولای

            ممنون یاسین جـــــــــــــان، فرستادم 🙂

          • یاسین اسماعیل پور
            Posted at 01:59h, 22 جولای

            چیزی برای من نیومده که سپهر جان 😮

    • یاسین اسماعیل پور
      Posted at 17:53h, 25 می پاسخ

      سلام رها جان

      این مطلب مخصوص کاربران ویژه سایت هست. برای دیدن این مطلب شما باید عضو ویژه سایت باشید.
      موفق باشی

  • sirvan
    Posted at 00:33h, 10 دسامبر پاسخ

    سلام . وقت بخیر . قبلاً شنیده بودم که هر کسی از نظر اسلام باید ذکات علم خود را بدهد واقاً که این عمل خداپسندانه را انجام دادید . ممنونم خیلی خیلی . امیدوارم همیشه موفق و سرافراز باشید . بای .

    • یاسین اسماعیل پور
      Posted at 02:01h, 15 دسامبر پاسخ

      نظر لطفته سیروان جان. مرسی

  • mozhgan
    Posted at 15:40h, 09 دسامبر پاسخ

    سلام وقت بخیر مثل همیشه عالی
    من یه شیت دارم حدود2000 سطر اطلاعات دارم میخوام به بالای هر سطر سه سطر دیگه اضافه کم ، آیا راه حلی داره که نیاز نباشه زیاد وقت بذارم

    • یاسین اسماعیل پور
      Posted at 02:03h, 15 دسامبر پاسخ

      بله راه حل داره مژگان جان. اما خیلی ساده نیست و نیاز داره که با VBA آشنا باشید

  • sadegh111
    Posted at 15:07h, 31 اکتبر پاسخ

    جالب بود

    • یاسین اسماعیل پور
      Posted at 17:58h, 31 اکتبر پاسخ

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

  • pooya4u
    Posted at 12:32h, 31 اکتبر پاسخ

    فوق العده عالی و کاربردی

    • یاسین اسماعیل پور
      Posted at 17:58h, 31 اکتبر پاسخ

      ممنون پویا جان

Post A Comment