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

ترسیم نمودار

تجزیه و تحلیل رگرسیون

معادله رگرسیون Yاز جانب ایکسوابستگی عملکردی نامیده می شود y=f(x)، و نمودار آن یک خط رگرسیون است.

اکسل به شما امکان می دهد نمودارها و نمودارهایی با کیفیت نسبتاً قابل قبول ایجاد کنید. اکسل یک ابزار ویژه دارد - Chart Wizard که کاربر تحت هدایت آن هر چهار مرحله از فرآیند ساخت نمودار یا نمودار را طی می کند.

به عنوان یک قاعده، رسم با انتخاب محدوده ای که حاوی داده هایی است که باید بر روی آن رسم شود، آغاز می شود. این شروع، پیشرفت بیشتر نقشه کشی را ساده می کند. با این حال، محدوده با داده های اصلی را می توان در مرحله دوم گفتگو با تقسیم کرد دیاگرام استاد. در اکسل 2003 دیاگرام استادبا کلیک بر روی برگه می توان در منو به عنوان یک دکمه یا یک نمودار ایجاد کرد درج کنیدو در لیستی که باز می شود مورد را پیدا کنید نمودار.در اکسل 2007 نیز تب را پیدا می کنیم درج کنید(شکل 31).

برنج. 31. دیاگرام استاددر اکسل 2007

ساده ترین راه انتخاب محدوده ای از داده های منبع است که در آن این داده ها در ردیف های مجاور (ستون ها یا ردیف ها) قرار دارند - باید روی سلول سمت چپ بالای محدوده کلیک کنید و سپس نشانگر ماوس را به سلول پایین سمت راست بکشید. محدوده. هنگام انتخاب داده هایی که در ردیف های غیر مجاور قرار دارند، با فشار دادن کلید Ctrl، نشانگر ماوس را در امتداد ردیف های انتخاب شده بکشید. اگر یکی از سری های داده سلولی با عنوان داشته باشد، سری انتخابی باقی مانده نیز باید یک سلول مربوطه داشته باشد، حتی اگر خالی باشد.

برای انجام تحلیل رگرسیون، بهتر است از نمودار پراکندگی استفاده شود (شکل 30). هنگام ساختن آن، اکسل اولین ردیف از محدوده انتخاب شده از داده های منبع را به عنوان مجموعه ای از مقادیر آرگومان توابعی که نمودارهای آنها باید رسم شود (مجموعه یکسان برای همه توابع) درک می کند. ردیف های زیر به عنوان مجموعه ای از مقادیر خود توابع درک می شوند (هر ردیف حاوی مقادیر یکی از توابع مربوط به مقادیر آرگومان مشخص شده در ردیف اول محدوده انتخاب شده است).

در اکسل 2007، نام محورها در تب منو قرار می گیرد چیدمان(شکل 32).

برنج. 32. تنظیم نام محورهای نمودار در اکسل 2007

برای به دست آوردن یک مدل ریاضی، باید یک خط روند روی نمودار رسم کرد. در اکسل 2003 و 2007، باید روی نقاط نمودار کلیک راست کنید. سپس در اکسل 2003 یک برگه با لیستی از مواردی که از بین آنها انتخاب می کنیم ظاهر می شود خط روند را اضافه کنید(شکل 33).

برنج. 33. خط روند را اضافه کنید

پس از کلیک بر روی مورد خط روند را اضافه کنیدیک پنجره ظاهر می شود خط روند(شکل 34). در تب TYPE می توانید انواع خطوط زیر را انتخاب کنید: خطی، لگاریتمی، نمایی، توانی، چند جمله ای، فیلتر خطی.

برنج. 34. پنجره خط رونددر اکسل 2003

در برگه گزینه ها(شکل 35) کادر کنار موارد SHOW EQUATION ON DIAGRAM را علامت بزنید، سپس یک مدل ریاضی از این رابطه روی نمودار ظاهر می شود. همچنین یک چک باکس در کنار آیتم قرار می دهیم روی نمودار، ارزش پایایی تقریب را نشان دهید (R^2).هرچه مقدار اطمینان تقریبی به 1 نزدیکتر باشد، منحنی انتخاب شده به نقاط نمودار نزدیکتر می شود. بعد، روی دکمه کلیک کنید خوب. یک خط روند، معادله مربوطه و مقدار قابلیت اطمینان تقریبی روی نمودار ظاهر می شود.

برنج. 35. تب گزینه ها

در اکسل 2007، پس از کلیک راست روی نقاط نمودار، لیستی از آیتم های منو ظاهر می شود که از آن افزودن خط روند را انتخاب کنید(شکل 36).

برنج. 36. خط روند را اضافه کنید

برنج. 37. تب پارامترهای خط روند

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

یک خط روند، معادله مربوطه و مقدار قابلیت اطمینان تقریبی روی نمودار ظاهر می شود.

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

شاید من سری مقالات پیش بینی را با ساده ترین چیز - ساخت تابع روند - شروع کنم. به عنوان مثال، بیایید داده های فروش را در نظر بگیریم و مدلی بسازیم که وابستگی فروش به زمان را توصیف کند.

مفاهیم اساسی

من فکر می کنم همه از دوران مدرسه با تابع خطی آشنا بوده اند؛ این دقیقاً همان چیزی است که زمینه ساز این روند است:

Y(t) = a0 + a1*t + E

Y حجم فروش است، متغیری که بر اساس زمان توضیح خواهیم داد و به آن بستگی دارد، یعنی Y(t).

t تعداد دوره (شماره ترتیبی ماه) است که طرح فروش Y را توضیح می دهد.

a0 ضریب رگرسیون صفر است که مقدار Y (t) را در غیاب تأثیر عامل توضیحی (t = 0) نشان می دهد.

a1 ضریب رگرسیون است که نشان می دهد شاخص فروش مورد مطالعه Y چقدر به عامل تأثیرگذار t بستگی دارد.

E اختلالات تصادفی هستند که منعکس کننده تأثیر عوامل دیگری هستند که در مدل در نظر گرفته نشده اند، به جز زمان t.

ساختمان نمونه

بنابراین، ما از حجم فروش در 9 ماه گذشته اطلاع داریم. علامت ما به این صورت است:

کار بعدی که باید انجام دهیم تعیین ضرایب است a0و a1برای پیش بینی حجم فروش برای ماه دهم.

تعیین ضرایب مدل

ما در حال ایجاد یک برنامه زمانبندی هستیم. به صورت افقی ماه های معوق را می بینیم، به صورت عمودی حجم فروش را می بینیم:

در Google Sheets انتخاب می کنیم ویرایشگر نمودار -> اضافیو یک تیک در کنار آن قرار دهید خطوط روند. در تنظیماتی که انتخاب می کنیم برچسب — معادلهو نمایش R^2.

اگر همه کارها را در MS Excel انجام می دهید، سپس روی نمودار کلیک راست کرده و از منوی کشویی «Add trend line» را انتخاب کنید.

به طور پیش فرض، یک تابع خطی ساخته شده است. در سمت راست، "Show equation on diagram" و "Value of approximation reliability R^2" را انتخاب کنید.

این چیزی است که اتفاق افتاد:

در نمودار معادله تابع را می بینیم:

y = 4856*x + 105104

این حجم فروش را بسته به تعداد ماهی که می خواهیم این فروش ها را برای آن پیش بینی کنیم، توصیف می کند. در نزدیکی ما ضریب تعیین R^2 را می بینیم که نشان دهنده کیفیت مدل و میزان فروش ما (Y) است. هر چه به 1 نزدیکتر باشد بهتر است.

من R^2 = 0.75 دارم. این یک شاخص متوسط ​​است، نشان می دهد که مدل هیچ عامل مهم دیگری را به جز زمان t در نظر نمی گیرد، به عنوان مثال، ممکن است فصلی باشد.

پیش بینی می کنیم

y = 4856*10 + 105104

ما 153664 فروش در ماه آینده دریافت می کنیم. اگر یک نقطه جدید به نمودار اضافه کنیم، بلافاصله می بینیم که R^2 بهبود یافته است.

به این ترتیب، می‌توانید داده‌ها را از چند ماه قبل پیش‌بینی کنید، اما بدون در نظر گرفتن سایر عوامل، پیش‌بینی شما روی خط روند قرار می‌گیرد و آنطور که می‌خواهید آموزنده نخواهد بود. علاوه بر این، پیش بینی بلندمدتی که به این صورت انجام شود، بسیار تقریبی خواهد بود.

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

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

بیایید نحوه اضافه کردن خط روند به نمودار در اکسل را بررسی کنیم.

افزودن خط روند به نمودار

به عنوان مثال، بیایید میانگین قیمت نفت از سال 2000 را از منابع باز در نظر بگیریم. بیایید داده ها را برای تجزیه و تحلیل در جدول وارد کنیم:

خط روند در اکسل نمودار یک تابع برازش است. چرا لازم است - پیش بینی بر اساس داده های آماری. برای این منظور باید خط را گسترش داد و مقادیر آن را تعیین کرد.

اگر R2 = 1، آنگاه خطای تقریب صفر است. در مثال ما، انتخاب تقریب خطی قابلیت اطمینان کم و نتایج ضعیفی را به همراه داشت. پیش بینی نادرست خواهد بود.

توجه!!! شما نمی توانید خط روند را به انواع نمودارها و نمودارهای زیر اضافه کنید:

• گلبرگ
• گرد؛
• سطح؛
• حلقوی
• جلد؛
• با تجمع



معادله خط روند در اکسل

در مثال بالا، تقریب خطی تنها برای نشان دادن الگوریتم انتخاب شد. همانطور که مقدار قابلیت اطمینان نشان داد، انتخاب کاملاً موفق نبود.

باید نوع صفحه نمایشی را انتخاب کنید که با دقت بیشتری روند ورودی کاربر را نشان می دهد. بیایید به گزینه ها نگاه کنیم.

تقریب خطی

تصویر هندسی آن یک خط مستقیم است. بنابراین، از تقریب خطی برای نشان دادن شاخصی استفاده می شود که با نرخ ثابت افزایش یا کاهش می یابد.

بیایید تعداد مشروط قراردادهای منعقد شده توسط مدیر را در طول 10 ماه در نظر بگیریم:

بر اساس داده های جدول اکسل، یک نمودار پراکندگی ایجاد می کنیم (این به نشان دادن نوع خطی کمک می کند):

نمودار را انتخاب کنید - "افزودن خط روند". در پارامترها، نوع خطی را انتخاب کنید. ما مقدار اطمینان تقریبی و معادله خط روند را در اکسل اضافه می کنیم (فقط کادرهای پایین پنجره "پارامترها" را علامت بزنید).

نتیجه را می گیریم:

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

y = 4.503x + 6.1333

• که در آن 4.503 شاخص شیب است.
• 6.1333 – جابجایی ها;
• y - دنباله ای از مقادیر،
• x - شماره دوره.

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

برای پیش بینی تعداد قراردادهای منعقد شده، به عنوان مثال، در دوره 11، باید عدد 11 را به جای x در معادله جایگزین کنید. طی محاسبات متوجه می شویم که در دوره یازدهم این مدیر 55 تا 56 قرارداد منعقد خواهد کرد.

خط روند نمایی

این نوع در صورتی مفید است که مقادیر ورودی با سرعت افزایش مداوم تغییر کنند. برازش نمایی زمانی که مشخصه های صفر یا منفی وجود دارد استفاده نمی شود.

بیایید یک خط روند نمایی در اکسل بسازیم. به عنوان مثال، مقادیر مشروط عرضه برق تولیدی در منطقه X را در نظر بگیرید:

ما در حال ایجاد یک برنامه زمانبندی هستیم. یک خط نمایی اضافه کنید.

معادله به شکل زیر است:

y = 7.6403е^-0.084x

• که در آن 7.6403 و -0.084 ثابت هستند.
• e پایه لگاریتم طبیعی است.

شاخص قابلیت اطمینان تقریبی 0.938 بود - منحنی با داده ها مطابقت دارد، خطا حداقل است، پیش بینی ها دقیق خواهند بود.

خط روند لگاریتمی در اکسل

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

در مرحله اولیه، وظیفه سازنده افزایش پایگاه مشتری است. وقتی محصولی خریدار خاص خود را دارد، نیاز به نگهداری و ارائه خدمات دارد.

بیایید یک نمودار بسازیم و یک خط روند لگاریتمی برای پیش بینی فروش یک محصول مشروط اضافه کنیم:

مقدار R2 نزدیک به 1 (0.9633) است که نشان دهنده حداقل خطای تقریب است. بیایید حجم فروش را در دوره های بعدی پیش بینی کنیم. برای این کار باید به جای x عدد دوره را در معادله جایگزین کنید.

مثلا:

دوره زمانی	14	15	16	17	18	19	20
پیش بینی	1005,4	1024,18	1041,74	1058,24	1073,8	1088,51	1102,47

برای محاسبه ارقام پیش بینی، از فرمول فرم استفاده شد: =272.14*LN(B18)+287.21. جایی که B18 عدد دوره است.

خط روند چند جمله ای در اکسل

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

روند چند جمله ای در اکسل برای تجزیه و تحلیل مجموعه بزرگی از داده ها در مورد یک کمیت ناپایدار استفاده می شود. بیایید به مثال مجموعه اول مقادیر (قیمت نفت) نگاه کنیم.

برای به دست آوردن چنین مقداری از قابلیت اطمینان تقریبی (0.9256)، لازم بود آن را روی درجه 6 تنظیم کنید.

اما این روند به ما امکان می دهد پیش بینی های کم و بیش دقیقی انجام دهیم.

اگر اندازه‌گیری زمان‌بندی برای حجم/اندازه‌های خاصی از محصولات وجود ندارد، چه باید بکنم؟ یا اینکه تعداد اندازه گیری ها کافی نیست و نمی توان در آینده نزدیک مشاهدات اضافی انجام داد؟ بهترین راه برای حل این مشکل، ساخت وابستگی های محاسبه شده (معادلات رگرسیون) با استفاده از خطوط روند در MS Excel است.

بیایید یک وضعیت واقعی را در نظر بگیریم: در یک انبار، به منظور تعیین میزان هزینه های کار برای انتخاب جعبه یک سفارش، مشاهدات زمان بندی انجام شد. نتایج این مشاهدات در جدول 1 در زیر ارائه شده است.

متعاقباً نیاز به تعیین زمان مورد نیاز برای انتخاب 0.6 و 0.9 متر مکعب کالا/سفارش بوجود آمد. به دلیل عدم امکان انجام مطالعات زمان بندی اضافی، زمان صرف شده برای انتخاب این حجم های سفارش با استفاده از معادلات رگرسیون در MS Excel محاسبه شد. برای رسیدن به این هدف، جدول 1 به جدول 2 تبدیل شد.

انتخاب نمودار پراکندگی، شکل. 1

مرحله بعدی: نشانگر ماوس روی یکی از نقاط نمودار قرار گرفت و منوی زمینه با استفاده از دکمه سمت راست ماوس فراخوانی شد، که در آن مورد انتخاب شد: "افزودن خط روند" (شکل 2).

اضافه کردن یک خط روند، شکل. 2

در پنجره ای که برای تنظیم فرمت خط روند ظاهر می شود (شکل 3)به ترتیب انتخاب شدند: نوع خطی/خط برق و موارد زیر بررسی شدند: «معادله را روی نمودار نشان دهید» و «مقدار قابلیت اطمینان تقریبی (R^2) را روی نمودار قرار دهید». (ضریب تعیین).

فرمت خط روند، شکل. 3

در نتیجه، نمودارهای ارائه شده در شکل 1 به دست آمد. 4 و 5.

وابستگی محاسبه شده خطی، شکل. 4

وابستگی محاسبه شده با قانون قدرت، شکل. 5

تجزیه و تحلیل بصری نمودارها به وضوح نزدیک بودن وابستگی های به دست آمده را نشان می دهد. علاوه بر این، مقدار قابلیت اطمینان تقریبی (R^2) که به آن ضریب تعیین نیز می گویند، در مورد هر دو وابستگی مقدار یکسان 0.97 است. مشخص است که هر چه ضریب تعیین به 1 نزدیکتر باشد، خط روند بیشتر با واقعیت مطابقت دارد. همچنین می توان بیان کرد که 97 درصد از تغییر در زمان صرف شده برای پردازش سفارش با تغییر در مقدار کالا توضیح داده می شود. بنابراین، در این مورد مهم نیست: وابستگی محاسبه شده باید به عنوان وابستگی اصلی برای محاسبه بعدی هزینه های زمانی انتخاب شود.

اجازه دهید وابستگی محاسبه شده خطی را به عنوان وابستگی اصلی در نظر بگیریم. سپس هزینه های زمانی بسته به مقدار کالا با فرمول تعیین می شود: y = 54.511x + 0.1489.نتایج این محاسبات برای مقدار کالاهایی که قبلاً مشاهدات زمانبندی برای آنها انجام شده است در جدول 3 زیر ارائه شده است.

بیایید میانگین انحراف زمان صرف شده محاسبه شده با استفاده از معادله رگرسیون را از زمان صرف شده محاسبه شده از مشاهدات حفظ زمان تعیین کنیم: (0.05-0.10-0.05+0.01)/4=0.0019. بنابراین، هزینه زمانی محاسبه شده با استفاده از معادله رگرسیون با هزینه زمانی محاسبه شده از داده های زمان نگه داشتن تنها توسط 0,19%. اختلاف بین داده ها ناچیز است.

با استفاده از فرمول: y = 54.511x + 0.1489، هزینه های زمانی را برای مقدار کالایی که قبلاً مشاهدات زمان بندی برای آنها انجام نشده است تعیین می کنیم. (جدول 4).

بنابراین، ساخت وابستگی های محاسبه شده با استفاده از خطوط روند در MS Excel - این یک راه عالی برای تعیین زمان صرف شده برای عملیاتی است که به دلایل مختلف مشاهدات زمان‌بندی پوشش داده نمی‌شوند.

آنچه انجام وظایف برای ساخت یک خط روند را متفاوت می کند این است که داده منبع می تواند مجموعه ای از اعداد غیر مرتبط با یکدیگر باشد.

پیش‌بینی با استفاده از یک برنامه منظم غیرممکن است، زیرا ضریب جبر آن (R^2) نزدیک به صفر خواهد بود.

به همین دلیل است که از توابع ویژه استفاده می شود.

اکنون آنها را می سازیم، پیکربندی و آنالیز می کنیم.

نسخه ساخت آسان

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

بیایید با ورود داده ها شروع کنیم.

1. یک جدول در اکسل با داده های منبع ایجاد کنید.

(تصویر 1)

2. سلول های B3:B17 را انتخاب کنید و به تب "Insert" بروید و "Graph" را انتخاب کنید.

(شکل 2)

3. پس از ایجاد نمودار، می توانید عنوان و عنوان اضافه کنید.

ابتدا روی حاشیه نمودار کلیک چپ کنید تا آن را انتخاب کنید.

سپس به تب “Design” رفته و “Layout 1” را انتخاب کنید.

(شکل 3)

4. بیایید به ساخت یک خط روند برویم. برای انجام این کار، نمودار را دوباره انتخاب کنید و به تب "Layout" بروید.

(شکل 4)

5. روی دکمه «خط روند» کلیک کنید و «برازش خطی» یا «برازش نمایی» را انتخاب کنید.

(شکل 5)

بنابراین ما یک خط روند اولیه ایجاد کردیم که ممکن است چندان با واقعیت مطابقت نداشته باشد.

این نتیجه میانی ماست.

(شکل 6)

و بنابراین ما باید پارامترهای خط روند خود را تنظیم کنیم یا تابع دیگری را انتخاب کنیم.

نسخه حرفه ای: انتخاب خط روند و تنظیم پارامترها

6. روی دکمه "خط روند" کلیک کنید و "پارامترهای اضافی و خطوط روند" را انتخاب کنید.

(شکل 7)

7. در پنجره “Trend line format” کادر کنار “place the approximation reliability value R^2 on the diagram” را علامت بزنید و روی دکمه “close” کلیک کنید.

در نمودار ضریب R^2= 0.6442 را می بینیم

(شکل 8)

8. تغییرات را لغو کنید. نمودار را انتخاب کنید، بر روی زبانه "Layout"، دکمه "Trend line" کلیک کنید و "No" را انتخاب کنید.

9. به پنجره "Trend Line Format" بروید، اما برای انتخاب خط روند "Polynomial"، درجه را تغییر می دهیم و به شاخص های ضریب R^2 = 0.8321 می رسیم.

(شکل 9)

پیش بینی

اگر بخواهیم حدس بزنیم چه داده هایی را می توان در اندازه گیری بعدی به دست آورد، در پنجره "فرمت خط روند"، تعداد دوره هایی را که پیش بینی برای آنها انجام شده است را نشان می دهیم.

(شکل 10)

بر اساس پیش بینی می توان حدس زد که در 25 ژانویه تعداد امتیازات کسب شده بین 60 تا 70 باشد.

نتیجه

و در نهایت، اگر به فرمولی که روند بر اساس آن ساخته می شود علاقه مند هستید، در بخش «فرمت خط روند»، کادر کنار «نمایش معادله در نمودار» را علامت بزنید.

اکنون می دانید که چگونه می توانید کار را کامل کنید و یک خط روند ایجاد کنید، حتی در برنامه ای مانند اکسل 2010.

سوال بپرسید، خجالتی نباشید.