برای اینکه بهتر متوجه بشین نمودار داینامیک چه نموداری هست جدول زیر رو در نظر بگیرید:
| میزان فروش |
| 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 مقادیر مختلف رو اضافه کنید تا ببینید نمودار ما به صورت خودکار تغییر میکنه تا داده های جدید رو هم نمایش بده.
فوق العده عالی و کاربردی
ممنون پویا جان
جالب بود
خوشحالم که برات مفید بوده صادق جان
سلام وقت بخیر مثل همیشه عالی
من یه شیت دارم حدود2000 سطر اطلاعات دارم میخوام به بالای هر سطر سه سطر دیگه اضافه کم ، آیا راه حلی داره که نیاز نباشه زیاد وقت بذارم
بله راه حل داره مژگان جان. اما خیلی ساده نیست و نیاز داره که با VBA آشنا باشید
سلام . وقت بخیر . قبلاً شنیده بودم که هر کسی از نظر اسلام باید ذکات علم خود را بدهد واقاً که این عمل خداپسندانه را انجام دادید . ممنونم خیلی خیلی . امیدوارم همیشه موفق و سرافراز باشید . بای .
نظر لطفته سیروان جان. مرسی
با سلام
چرا من نمیتونم این مطلب رو ببینم؟!
سلام ، رها خانم – عضویت شما رو در این سایت بهتون تبریک و خوشامد میگم ، شما همه مطالب را باید ببینید!!
کدوم مطلب رو نمیتونید ببینید؟!!
خانیکی
salam manam moshkel vorod be safahate vizhe ro daram
سلام شاهین جان. لطفا یوزر و پسوردت رو از طریق فرم تماس برام بفرست بررسی مبکنم. ممنون
ممنون یاسین جـــــــــــــان، فرستادم 🙂
چیزی برای من نیومده که سپهر جان 😮
سلام رها جان
این مطلب مخصوص کاربران ویژه سایت هست. برای دیدن این مطلب شما باید عضو ویژه سایت باشید.
موفق باشی
آقا یاسین سلام! من مبلغ مربوطه جهت عضویت ویژه رو پرداخت کردم اما متاسفانه دسترسی به بخشهای مربوطه شو ندارم
من که عضو شدم پس چرا نشون نمیده مطالبو
سلام شیوا جان. این مطلب مخصوص اعضای ویژه هست
سلام.آقا یاسین آیا امکان رسم نمودار بر مبنای اطلاعات ستونهای مشخص به وسیله ورود محدوده زمانی مشخص و یا فیلدهای مطلوب دیگر وجود دارد؟شبیه فرم های گزارش گیری…
سلام. بله امکانش وجود داره در اکسل
چطوری میتونم ازتون راهنمایی بگیرم؟
سلام فایلتون رو به همراه توضیحات کامل از طریق فرم تماس ارسال کنید
یه مشکل دیگه هم دارم استاد یاسین.combobox ایجاد شده ظاهرا پارمترها رو به صورت ستونی دریافت میکنه و نه عمودی درسته؟
نه میشه عمودی هم تعریف کرد
سلام ، خسته نباشید. یه سوال : می خواستم بدونم برای تحلیل ماهیانه ی داده های فروش یک شرکت و تهیه ی نمودارهای کاربردی در این زمینه باید کدوم بخش از اکسل رو مطالعه کنم ؟ با تشکر.
سلام ، خسته نباشید. من عضو شدم و اکانت 1 ماهه خریدم و عضو ویژه هستم روز اول دسترسی داشتم اما الان نمیتونم دسترسی داشته باشم .با تشکر.
سلام دوست عزیز. ضمن عذرخواهی مشکل برطرف شد و برای شما شش ماه عضویت ویژه فعال شد
با سلام من اشتراک یک ماهه دارم پس چرا نمودار داینامیک باز نمیشه؟باتشکر
با سلام من اشتراک یک ماهه دارم پس چرا نمودار داینامیک باز نمیشه؟
سلام ، خسته نباشید. من عضو شدم و اکانت 1 ماهه خریدم و عضو ویژه هستم روز اول دسترسی داشتم اما الان نمیتونم دسترسی داشته باشم لطفا راهنماییم کنید.
با تشکر.
سلام دوست عزیز. ضمن عذرخواهی بابت مشکل پیش اومده اکانتتون به مدت 6 ماه تمدید شد. لطفا مجدد بررسی کنید
سلام جناب اسماعیل پور . بنده هم عضو ویژه شدم ولی نمیتونم مطالب رو ببینم . ممنون
ok شد ممنون .
جناب اسماعیل پور من امروز log in شدم نمیتونم مطالب رو ببینم .
سلام
بررسی کنید مجدد باید حل شده باشه
سلام. فرض کنید که یک ستون با 10 داده داریم و حال داده 11 را به آن اضافه می کنیم. آیا راهی برای ایجاد یک نمودار پویا که با اضافه کردن داده 11، به صورت خودکار داده اول را حذف و داده یازدهم را در بر بگیرد، وجود دارد؟ به عبارت دیگر من به دنبال ایجاد یک نمودار پویا هستم که با اضافه کردن هر داده جدید، یکی از داده های قبلی را حدف و به جای آن داده جدید را در بر بگیرد؟
بله امکانش هست با همین تکنیک میشه اینکار رو انجام داد
از راهنمایی تان سپاسگذارم. با همین روش راه حل را پیدا کردم. از آن جا که فکر کردم ممکن است این راه حل برای بقیه مخاطبانتان هم مفید باشد، آن را بیان می کنم: برای این کار باید فرمول را به صورت زیر بنویسیم
(OFFSET(Sheet1!$A$2; COUNT(Sheet1!$A:$A)-10;0;10;1=
فقط یک سوال دیگر در مورد نام گذاری داشتم. آیا مطلب آموزشی در مورد نام گذاری دارید که در قسمت نظرات آنجا بتوانم سوالم را مطرح کنم؟
ممنون از به اشتراک گذاری اطلاعات مفیدتون. خیر هنوز مطلبی در خصوص نام گذاری ننوشتم
سلام میخواستم بدونم که اگر ما ده تا اسم در یک ستون داشته باشیم و برای هریک یک شیت باز شود و وقتی که ما ده تا را یکی بهش اضافه کنیم خودکار یک شیت باز شود
اگه میشه راهنمایی کنید
سلام. برای اینکار باید از vba استفاده کنید و راه ساده ای وجود نداره که بشه در یک کامنت توضیح داد
سلام.من یک فایل اکسل دارم شامل چندین ستون.تعداد سطها تقریبا 40 هزار ردیف است.من چندین نمودار ترسیم کرده ام که با فیلتر کردن هر ستون نمودارها تغییر می کنند.مشکل من اینه که add data label را برای همه نمودارها فعال کردم ولی هنگامی که دیتاها را فیلتر میکنم و نمودارهای جدید بطور اتوماتیک رسم میشه data label پاک میشه و باید مجدد add data label را فعال کنم.آیا روشی وجود دارد که برای اولین بار که data labelرا فعال کنم با تغییر دیتاها و فیلتر کردن نمودار جدید نیاز به فعال کردن دوباره add data label نباشه؟
ممنون اگه بتونید راهنمایی کنید.
با سلام
بعداز ورود اطلاعات ، محدوده اطلاعات را انتخاب کنید و فرمت جدول برای آنها انخاب کنید
سپس نمودار مورد نظر را انتخاب کنید
با اعمال فیلتر به صورت دینامیک تغییر می کند و data lable شما نیز مجدد اعمال می گردد و حذف نشده و نیاز به تعریف مجدد نمی باشد
با سلام
این روش یک ایراد دارد اگر کابر به اشتباه یک خانه خالی گذاشته شود یا پس از ورود اطلاعات ، اطلاعات یکی از سلول ها را حذف نمایید.
به تعدادی سلول خالی بین مقادیر از اخرین اطلاعات را در نظر نمی گیرد و در نمودار لحاظ نمی کند
سلام من تو اکسل وقتی insert chart رو میزنم یه صفحه سفید فقط باز میشه و نمودار نمایش داده نمیشه علت چیه؟
سلام. جناب اگر یک سلول که اطلاعاتش از طریق یک لینک وارد میشه و دائما تغییر می کنه رو چجوری میشه روی نمودار پیاده کرد. مثلا فرض کنید اگر اطلاعات یک سلول در هر دقیقه اعداد 1و2و5و6و8و9و… باشه رو چجوری میتونم براساس زمان روی نمودار پیاده سازی کنم .ممنون
سلام مصطفی جان. منظور شما اینه که هر بار که مقدار سلول تغییر میکنه history یه جا ذخیره بشه و بعد به صورت چارت نمایش داده بشه؟
سلااام امکانش هست چگونگی رسم نمودار شبیه سازی شده رو بگید
یعنی هر چی متغییر رو تغیر بدیم بازم همون نمودار رو بهمون بده
با سلام ببخشی یه سوال داشتم و ان اینکه برا رسم نمودار من برای محور x اعداد 0.5 و 1 و 1.5 و 2 رو وارد کردم ولی نمودار من اعداد رو از 1 تا 4 نشون میده چطور میتونم ان رو درست کنم
باسلام سمانه خانم
من گمان می کنم که شما نوع نمودارتون رو بدرستی انتخاب نکردید
در اکثر نمودارهای اکسل شما فقط می تونید یک ستون از اعداد رو داشته باشید
و در محور دیگه فقط نام ستون ها و یا شماره اونها ذکر میشه
اگه تمایل داشته باشین که یک نمودار دو بعدی داشته باشین که در هر دو بعد بتونید عدد بدین
بایستی از نمودارهایی مثل X Y ( Scatter ) استفاده کنین
موفق باشین – خانیکی
با عرض سلام و وقت بخیر
من وقتی رنج داده هامو در بخش series values وارد می کنم داده ها به شکل صفر نمایش داده می شوند و مقادیر اصلی داده ها نشان داده نمی شود و در نتیجه نمودار من هیچ میله ای را نشان نمی دهد و صفحه نمودار خالی نمایش داده میشه، لطفا راهنمایی کنید.
سپاس
سلام وقتتون بخیر. سوالی خدمتتون داشتم. مثلا فایل اکسل ما ۱۵ سطر دارد و ما همیشه روی سلولهای سطر آخر عملیات ریاضی انجام میدهیم مثلا L2=D15+E15 یا M2=A15/B15 چطور میتوان با افزایش سطر ها محتوای این سلولها داینامیک تغییر کند . به این ترتیب : L2=D16+E16 یا M2=A16/B16
سلام مهتا جان. برای اینکار میتونی ابتدا با تایع COUNT تعداد سطرها رو بشماری و بعد با تابع OFFSET رفرنس رو تغییر بدی:
=offset(a1,count(a:a,0)
ممنون از شما.
خواهش میکنم
سلام
نمیدونم این آموزش مخصوص چه نسخه ای از اکسل هست ولی ایرادهای تایپی و فنی داره
این عبارت باید با کُما نوشته بشه و نه سیمیکالُن
عبارت متغیر =OFFSET(Sheet1!$A$2;0;0;COUNT(Sheet1!$A:$A);1)
Scope هم باید گزینه Workbook انتخاب بشه و نه Sheet1
در تعین بخش محدوده برای نمودار هم نباید از عبارت Sheet1 استفاده بشه بلکه باید اسم فایل اصلی گذاشته بشه
در تعین بخش محدوده برای نمودارعبارت xlsx. هم فراموش شده