ایجاد نمودار پویا در اکسل
ایجاد نمودار پویا در اکسل
یکی از بهترین راه های ارائه اطلاعات و گزارش ها، نمودارها هستند. نمودارها بدلیل ویژگی هایی که دارند، اختلافها، نقاط حساس و … . بطور کلی تحلیل وضعیت را تسهیل می کنند. رسم نمودار در اکسل، با توجه به تنوع و امکانات گرافیکی جذابی که وجود داره، از اهمیت ویژه ای برخوردار هست. نمودار پویا حالت داینامیک و تغییر پذیر نمودارهای معمولی هست. منظور از نمودار پویا این هست که با انتخاب و تعیین پارامترهای مختلف، نمودار هم تغییر کنه. میخوایم در مورد ایجاد نمودار پویا در اکسل صحبت کنیم.
رسم نمودار پویا یا داینامیک یکی از مهم ترین مسائل در تهیه داشبورد و یا گزارش های پویاست. در ادامه یک مثال ساده از نمودار پویا (نمودار شرطی) رو شرح میدیم که منطق و نوع عملکرد این نمودارها به خوبی درک بشه.
فرض کنید میخواهیم نمودار مربوط به داده های فروش رو به تفکیک ماه نمایش بدیم. یعنی ماه مورد نظر رو انتخاب کنیم و نمودار تا همون ماه نمایش داده بشه.
گام اول: ایجاد لیست ماه ها
برای این کار مطابق شکل ۱ از تب Data و گزینه Data Validation لیست مربوط به ماه ها رو ایجاد میکنیم.
شکل ۱- ایجاد نمودار پویا در اکسل – ایجاد لیست ماه ها
گام دوم: رسم نمودار اولیه و ثابت
برای اینکه نمودار رو داینامیک کنیم، اول باید نمودار ثابت رو رسم کنیم بعد با فرمول نویسی به نمودار پویا تبدیل کنیم. برای این کار مطابق شکل ۲ داده ها رو انتخاب کرده و نمودار ستونی رو انتخاب و رسم میکنیم.
شکل ۲- ایجاد نمودار پویا در اکسل – رسم نمودار اولیه
گام سوم: ایجاد محدوده پویا
حالا برای اینکه بتونیم محدوده داینامیک ایجاد کنیم باید برای محور عمودی نمودار، نام ایجاد کنیم. سپس نام ایجاد شده رو فرمول نویسی کنیم. از مسیر زیر محدوده مربوط به محور عمودی رو فرمول نویسی میکنیم.
Formula> Name Manager> New
شکل ۳- ایجاد نمودار پویا در اکسل – فرمول نویسی
پس طبق توضیحات بالا محدوده نامگذاری برای محور عمودی به صورت زیر خواهیم داشت:
=OFFSET(‘Dynamic Chart’!$C$1,1,0,MATCH(‘Dynamic Chart’!$F$1,’Dynamic Chart’!$B$1:$B$13,0)-1,1) | Sale_Y |
تشریح فرمول محدوده Sale_Y
از سلول C1، یکی بیاد پایین تر، بدون تغییر به سمت راست و چپ، به اندازه یک محدوده به ارتفاع مکان ماه در محدوده ماه ها (که از Match حاصل میشه. یعنی مکان سلول F1 در محدوده B2:B13 میشه ارتفاع محدوده مورد نظر) و پهنای ۱ سلول رو در نظر میگیره.
گام چهارم: تخصیص نام ها به نمودار
حالا باید محدوده نامگذاری شده رو به نمودار اول تخصیص بدیم. برای این کار، از مسیر زیر و مطابق شکل ۴ عمل میکنیم.
Design> Select Data> Legend Entries (Series)> Edit
شکل ۴- تخصیص نام فرمول نویسی شده به محور عمودی نمودار
گام پنجم: داینامیک کردن عنوان نمودار
برای اینکه نمودار عنوان داینامیک هم داشته باشه اول در یک سلول عنوان متحرک رو میسازیم و وصل میکنیم به تیتر نمودار. برای این کار از فرمول نشان داده شده در شکل ۵ استفاده میکنیم.
=”فروش تا پایان ” & F1 & ” ماه “
شکل ۵- فرمول نویسی عنوان نمودار
کافیه طبق تصویر زیر عنوان نمودار رو به سلول F2 وصل کنیم.
همونطور که میبینید فرمول نویسی در Name Manager و تابع Offset که از دسته توابع جستجو هست اساس ایجاد محدوده های پویا هستند.
یکی از راه های دیگه برای ساخت نمودار پویا استفاده از Form Control ها در اکسل هست.
دانلود فایل این آموزش
برای دانلود این فایل از لینک زیر استفاده کنید:
جناب مهندس سلام وقت عالی بخیر
خیلی ممنون بابت آموزش خوبتان
با کمک شما توانستم شروع و پایان داینامیک بکنم
الان برای مثال شروع ماه تیر هست و پایان ماه بهمن هست الان مثلا ماه آذر دیتا نداریم وسط نمودار خالی می افتد اگر خواسته باشیم وقتی یک ماه دیتا نداریم نمایش ندهد در نمودار خالی نیافتد چه کاری باید انجام بدهیم
خیلی ممنون میشوم بهم کمک کنید
و من الله التوفیق
سلام
کافیه در داده های نمودار برای مقدار خالی خطای NA بگذارید و در تنظیمات نمودار تعیین کنید که مقدار NA رو به صورت مورد نظر نمایش دهد. مقاله کاربرد خطای NA و تابع NA رو مشاهده کنید.
خیلی ممنون مهندس واقعا لطف کردید
سلام وقت عالی بخیر
خیلی ممنون از آموزش خوبتان
یه سوالی که از حضرتعالی داشتم الان فقط نقطه پایان متغییر هست و نقطه شروع ثابت هست یعنی میزان فروش از فروردین نشان میدهد تا ماه دلخواه
حالا اگر خواسته باشیم که ماه شروع متغییر باشد باید چیکار بکنیم
مثلا( میزان فروش از ماه تیر الی آذر نشان بدهد )
خیلی ممنون میشوم اگر راهنمایی ام بکنید
سلام
وقت شما هم بخیر
کافیه در تعریف Name که از تابع Offset استفاده شده، مقدار Base یا آرگومان اول آن هم از یک تابع Offset دیگه استفاده بشه.
خیلی ممنون جناب مهندس لطف کردید
فقط بی زحمت میشود فرمول Offset بی زحمت درج بکنید
چون هر کاری انجام میدهم درست نمیشود
سلام
میتونید از کد زیر در تعریف Name استفاده کنید:
سلام وقت بخیر
جدولی از اطلاعات دارم که شامل ۵ ستون و ۵ ردیف می باشد. چطور می شود نمودار داینامیکی رسم کرد که در آن هم تعداد ستون و هم تعداد سطر متغیر باشد یعنی با افزودن یا کاهش داده در ستون و ردیف نمودار تغییر کند. ممنون
درود
وقت یمنطق نمودار پویا رو خوب یاد بگیرید، براحتی میتونید این کار و انجام بدید
مقاله رو مطالعه کنید و سعی کنید خوب درک کنید
و بعد روی داده های خودتون پیاده کنید
با سلام ببخشید فایلی که برای دانلود گذاشتید باز نمیشه انگار که اکسل نمیشناستش
ممنون
درود بر شما
فایل که اکسل هست
شاید ناقص دانلود شده
winzip اپدیت شده هم استفاده کنید حتما
ممنون
سلام در خصوص مواردی که تعداد داده ها متفاوته (تصور کنید چارت برای سه شرکت قراره رسم بشه و یک شرکت اطلاعات ۱۰ سال دومی ۲۰ سال و سومی ۳۰ سال در اختیار ما گذاشته ) چطوری میشه رسم کرد بدون نشون دادن سلول های خالی در نمودار ؟؟؟
درود
منطق رسم نمودارهای پویا رو که یاد بگیرید همه مدلشو میتونید رسم کنید
پس نامگذاری محدوده ها و فرمولن نویسی داینامیک رو خوب یاد بگیرید
سلام
اگر داده های من سه ستون باشن اون وقت باید چیکار
کنم میشه راهنماییم کنید
ممنون
درود
منطق کار رو یاد بگیرید، هر مدلی که خبواید میتونید تغییر بدید
راهنمایی خاصی نیاز نداره
سلام خسته نباشید
سوالی داشتم من ۱۰ عدد در ۱۰ سلول به صورت ستونی دارم که با هم متفاوت هستند و هر کدام نسبت به هم یک ارزش داره که مجموع اون اعداد باهم یک عدد صحیح می باشد .
امکان این هست که من یکی از اعداد رو در داخل سلول تغییر بدم و بعد بدون اینکه مجموع اعداد تغییری بوجود نیاد یعنی اینکه اون مقداری که اضافه شده از دیگر سلول ها با توجه به نسبیت هر کدام از اعداد نسبت به جمع کل به همون میزان از عدد اضافه شده کم بشه
نمیدونم درست توضیح دادم یا نه امیدوارم منظورم رو رسونده باشم
امکانی در اکسل هست که بشه برای این کار به صورت متواری گذاشت و هر کدام از اعداد رو مدام تعییر دارد تا ارزششون نسبت به هم تعییر کنه.
سلام
اگر فقط یک سلول رو بخواید تغییر بدید میشه با استفاده از فرمول بقیه سلول ها رو به این سلول وابسته کرد و نهایتا با استفاده از ابزار Goal Seek میتونید مقدار سلول اول رو تغییر بدید.
اگر قصد دارید سلول های مختلف رو تغییر بدید فقط با VBA میشه این کار رو انجام داد.