نحوه استفاده از نمودار پراکندگی در مایکروسافت اکسل برای پیش بینی رفتار داده ها

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

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

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

ایجاد نمودار پراکندگی در مایکروسافت اکسل

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

متغیر مستقل معمولاً در محور افقی نمودار نمایش داده می شود، در حالی که می توانید متغیر وابسته را در محور عمودی آن پیدا کنید. سپس رابطه بین آنها با خط نمودار نشان داده می شود

برای ایجاد نمودار پراکندگی در صفحه اکسل، مراحل زیر را دنبال کنید:

1. کاربرگ حاوی داده هایی را که می خواهید در نمودار Scatter رسم کنید باز کنید.
2. متغیر مستقل را در ستون سمت چپ و متغیر وابسته را در ستون سمت راست قرار دهید.
3. مقدار هر دو ستونی را که می خواهید رسم کنید انتخاب کنید.
4. روی Insert Tab کلیک کنید و به گروه Charts بروید. اکنون بر روی Insert Scatter (X, Y) یا Bubble Chart کلیک کنید.
5. در اینجا، سبک های مختلف نمودار پراکندگی را خواهید یافت. با کلیک بر روی آن یکی از آنها را انتخاب کنید.
6. نمودار را روی صفحه نمایش می دهد. نام محورها و عنوان نمودار را تغییر دهید.

ترسیم خط روند روی نمودار پراکنده

برای ارائه رابطه بین متغیرهای نمودار، خط روند مورد نیاز است. خط روند باید مشابه یا همپوشانی با مقادیر داده در نمودار داشته باشد تا رابطه بین متغیرها به طور دقیق برآورد شود. برای رسم خط روند روی نمودار پراکندگی:

1. روی هر نقطه داده در نمودار پراکنده کلیک راست کنید.
2. از لیست گزینه هایی که ظاهر می شود، افزودن خط روند را انتخاب کنید.
3. یک پنجره Format Trendline در سمت راست ظاهر می شود که گزینه Linear به عنوان پیش فرض انتخاب شده است.

این یک خط روند (خط چین مستقیم) به نمودار پراکندگی شما اضافه می کند.

قالب‌بندی گزینه‌های خط روند به منحنی متناسب با مقادیر داده‌ها

ما می خواهیم منحنی را با خط روند تا حد امکان به نمودار منحنی نزدیک کنیم. به این ترتیب، ما می توانیم بینشی از رابطه تقریبی بین متغیرها بدست آوریم. برای این کار مراحل زیر را دنبال کنید:

1. منحنی های مختلف را از TRENDLINE OPTIONS در پنجره Format Trendline انتخاب کنید تا منحنی با خط روند مطابق با نمودار منحنی باشد.
2. تیک گزینه Display Equation on chart را بزنید تا معادله برازش منحنی در نمودار پراکندگی نمایش داده شود.

پیش بینی ارزش های رو به جلو و عقب مانده بر اساس روند

پس از برازش منحنی، می توانید از این خط روند برای پیش بینی مقادیر قبلی و آینده که بخشی از این مجموعه داده نیستند استفاده کنید. می توانید با اختصاص مقداری در قسمت Forecast پنجره Format Trendline به این هدف دست یابید. دوره های مورد نظر خود را در زیر گزینه های Forward و Backward اضافه کنید تا مقادیر مورد انتظار را در نمودار پراکندگی مشاهده کنید.

پیش بینی رابطه بین چند متغیر مستقل و وابسته برای فرمول یک معادله

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

در شکل زیر مجموعه داده ای داریم که شامل دو متغیر مستقل است. در نمودار، محور افقی نشان دهنده متغیر u و محور عمودی نشان دهنده متغیر وابسته حاصل است. هر خط روی نمودار نیز تابعی از متغیر T است.

در اینجا، ما راهی برای یافتن رابطه تقریبی بین متغیر وابسته Y(U,T) (یا مقدار حاصل) و متغیرهای مستقل U و T پیدا خواهیم کرد. این ما را قادر می سازد تا این مقادیر متغیر را برای پیش بینی رفتار داده ها برون یابی کنیم.

برای این کار مراحل زیر را دنبال کنید:

1. ابتدا، رابطه بین یک متغیر مستقل (U) و Y وابسته حاصل را پیدا خواهیم کرد. مقدار سایر مقادیر مستقل (T) را با انتخاب تنها یک ستون در هر زمان ثابت نگه دارید.
2. سلول های B3 تا B10 را برای انتخاب U و سلول های C3 تا C10 (مقدار حاصل در T=1) را انتخاب کنید و از نمودار پراکندگی برای رسم آنها استفاده کنید.
3. حالا خط روند را رسم کنید و از بهترین خط روند که در پنجره Format Trendline نشان داده شده است استفاده کنید که متناسب با مجموعه داده است. در این مورد، ما خط روند “خطی” را مشاهده کردیم که بهترین تناسب را با منحنی دارد.
4. در پنجره Format Trendline line بر روی Display equation on chart کلیک کنید.
5. نام محورهای نمودار را بر اساس متغیرهای داده تغییر دهید.
6. در مرحله بعد، باید یک نمودار پراکندگی برای همه متغیرهای دیگر زیر T ایجاد کنید. مراحل یک تا پنج را دنبال کنید، اما ستون‌های D3 تا D10 (T=2)، E3 تا E10 (T=5)، F3 تا F10 (T=) را انتخاب کنید. 7)، G3 تا G10 (T=10)، H3 تا H10 (T=15)، I3 تا I10 (T=20) و J3 تا J10 (T=20) به طور جداگانه با متغیر U حاوی سلول های B3 تا B10.
7. شما باید معادلات زیر را که در نمودارها نمایش داده شده است پیدا کنید.T Y T=1 Y=2U+12.2 T=2 Y=2U+21.2 T=5 Y=2U+48.2 T=7 Y=2U+66.2 T=10 Y=2U+ 93.2 T=15 Y=2U+138.2 T=20 Y=2U+183.2 T=25 Y=2U+228.2 می توانیم مشاهده کنیم که همه معادلات خطی هستند و ضریب یکسانی روی متغیر U دارند. این ما را به نتیجه گیری اینکه Y برابر است با 2U و برخی مقادیر مختلف دیگر که می تواند تابعی از متغیر T باشد.
8. این مقادیر را جداگانه یادداشت کنید و آنها را مطابق شکل زیر مرتب کنید (هر مقدار با مقدار متغیر ذکر شده خود، مانند 12.2 با T=1 و 228 با T=25 و غیره). اکنون این مقادیر را پراکنده کرده و معادله ای که رابطه بین این مقادیر را با متغیر T نشان می دهد نمایش می دهد.
9. در نهایت، می‌توانیم Y(U,T) را به عنوان مرتبط کنیم

T=1

Y=2U+12.2

T=2

Y=2U+21.2

T=5

Y=2U+48.2

T=7

Y=2U+66.2

T=10

Y=2U+93.2

T=15

Y=2U+138.2

T=20

Y=2U+183.2

T=25

Y=2U+228.2

Y(U,T)=2U+9T+3.2

شما می توانید این مقادیر را با ترسیم این معادله برای مقادیر مختلف U و T تأیید کنید. به طور مشابه، می توانید رفتار Y(U,T) را برای مقادیر مختلف متغیرهای U و T که با این مجموعه داده در دسترس نیستند، پیش بینی کنید.

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

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

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

منبع مقاله