24 اکتبر ایجاد نمودار داینامیک در اکسل
سلام. امروز با آموزش نحوه رسم نمودار داینامیک در اکسل در خدمتتون هستم. خب اول باید ببینیم اصلاً نمودارهای داینامیک در اکسل چه نمودارهایی هستن. نمودارهای داینامیک نمودارهایی هستند که با کم یا زیاد کردن تعداد داده ها به صورت خودکار آپدیت میشن. ساخت نمودارهای داینامیک در اکسل مخصوصاً در ساخت داشبوردها و گزارش های مدیریتی خیلی میتونه مفید باشه. با من در ادامه این آموزش همراه باشید.
[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]
پرویز
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 اکتبرممنون پویا جان