خطوط روند در قابلیت اطمینان اکسل. ساخت معادلات رگرسیون با استفاده از خطوط روند در MS Excel برای مشاهدات زمان

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

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

روش 1: خط روند

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

بیایید سعی کنیم میزان سود شرکت را در 3 سال بر اساس داده های این شاخص برای 12 سال گذشته پیش بینی کنیم.

روش 2: بیانیه FORECAST

برون یابی برای داده های جدولی را می توان از طریق تابع استاندارد Excel انجام داد پیش بینی. این آرگومان در دسته ابزارهای آماری قرار دارد و دارای نحو زیر است:

FORECAST(X، مقادیر_y_y، مقادیر x شناخته شده)

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

"مقادیر y شناخته شده"پایه مقادیر شناخته شده تابع است. در مورد ما نقش آن میزان سود دوره های قبل است.

"مقادیر x شناخته شده"آرگومان هایی هستند که با مقادیر شناخته شده تابع مطابقت دارند. در نقش آنها، شماره سالهایی را داریم که اطلاعات سود سالهای گذشته برای آنها جمع آوری شده است.

به طور طبیعی، بازه زمانی نباید به عنوان یک استدلال عمل کند. به عنوان مثال، می تواند دما باشد و مقدار تابع می تواند سطح انبساط آب هنگام گرم شدن باشد.

هنگام محاسبه به این روش، از روش استفاده می شود رگرسیون خطی.

بیایید به تفاوت های ظریف استفاده از اپراتور نگاه کنیم پیش بینیدر یک مثال خاص بیایید همین جدول را برداریم. ما باید پیش بینی سود سال 2018 را بدانیم.

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

روش 3: عملگر TREND

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

TREND(مقدارهای_y_معلوم، مقادیر_x_معروف، مقادیر_x_جدید، [const])

همانطور که می بینید، استدلال ها "مقادیر y شناخته شده"و "مقادیر x شناخته شده"به طور کامل با عناصر مشابه اپراتور مطابقت دارد پیش بینی، و استدلال "مقادیر x جدید"با استدلال مطابقت دارد "ایکس"ساز قبلی علاوه بر این، در روندیک استدلال اضافی وجود دارد "ثابت"، اما اجباری نیست و فقط در صورت وجود عوامل ثابت استفاده می شود.

این اپراتور در مواقعی که وجود داشته باشد بیشترین استفاده را دارد وابستگی خطیکارکرد.

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

روش 4: عملگر GROWTH

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

GROWTH(مقدارهای_y_معلوم، مقادیر_x_معروف، مقادیر_x_جدید، [const])

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

روش 5: عملگر LINEST

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

LINEST(Known_y، شناخته شده_x، new_x، [const]، [آمار])

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

همانطور که می بینید، ارزش سود پیش بینی شده، محاسبه شده با روش تقریب خطی، در سال 2019 4614.9 هزار روبل خواهد بود.

روش 6: عملگر LFPRIB

آخرین ابزاری که به آن نگاه خواهیم کرد این خواهد بود LGRFPRIBL. این عملگر محاسبات را بر اساس روش تقریب نمایی انجام می دهد. نحو آن ساختار زیر را دارد:

LGFPRIB(مقدار_y_معلوم، مقادیر_x_دانسته، مقادیر_x_نیوز، [const]، [آمار])

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

میزان سود پیش بینی شده در سال 2019 که با استفاده از روش تقریب نمایی محاسبه شده است، 4639.2 هزار روبل خواهد بود که باز هم تفاوت چندانی با نتایج به دست آمده با محاسبه روش های قبلی ندارد.

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

پیش زمینه نظری

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

مشکلات تقریب توابع از این نوع اغلب ایجاد می شود:

• هنگام ساخت فرمول های تقریبی برای محاسبه مقادیر مقادیر مشخصه فرآیند مورد مطالعه با توجه به داده های جدولی به دست آمده در نتیجه آزمایش.
• در ادغام عددی، تمایز، حل معادلات دیفرانسیلو غیره.؛
• در صورت نیاز به محاسبه مقادیر توابع در نقاط میانی بازه در نظر گرفته شده؛
• هنگام تعیین مقادیر مقادیر مشخصه فرآیند در خارج از فاصله مورد نظر، به ویژه هنگام پیش بینی.

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

دو گزینه برای ایجاد رگرسیون در اکسل وجود دارد.

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

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

برای جدولی از داده‌ها که یک فرآیند خاص را توصیف می‌کند و با یک نمودار نشان داده می‌شود، اکسل یک ابزار تحلیل رگرسیون موثر دارد که به شما امکان می‌دهد:

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

y = y(x)

که در آن x یک متغیر مستقل است که اغلب مقادیر دنباله‌ای از اعداد طبیعی (1؛ 2؛ 3؛ ...) را می‌گیرد و برای مثال، شمارش معکوس زمان فرآیند مورد مطالعه (ویژگی‌ها) را تولید می‌کند. .

1 . رگرسیون خطی در مدل‌سازی ویژگی‌هایی که با نرخ ثابت افزایش یا کاهش می‌یابند خوب است. این ساده ترین مدل فرآیند مورد مطالعه است. او

y=mx+b

که در آن m مماس شیب رگرسیون خطی بر محور x است. ب - مختصات نقطه تقاطع رگرسیون خطی با محور y.

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

در این مورد، خط روند مطابق با معادله ساخته می شود:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

که در آن ضرایب c0, c1, c2,...c6 ثابت هایی هستند که مقادیر آنها در حین ساخت تعیین می شود.

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

مطابق با معادله ساخته شده است:

y = c ln(x) + b

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

مطابق با معادله ساخته شده است:

y = cxb

که در آن ضرایب b و c ثابت هستند.

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

مطابق با معادله ساخته شده است:

y=cebx

که در آن ضرایب b و c ثابت هستند.

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

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

برای افزودن خط روند به یک سری داده:

• نمودار ساخته شده بر اساس سری داده ها را فعال کنید، یعنی در ناحیه نمودار کلیک کنید. مورد نمودار در منوی اصلی ظاهر می شود.
• پس از کلیک بر روی این مورد، منویی روی صفحه ظاهر می شود که در آن باید دستور Add trend line را انتخاب کنید.

اگر ماوس را روی نمودار مربوط به یکی از سری داده ها نگه دارید و راست کلیک کنید، همان اقدامات به راحتی انجام می شود. در ظاهر شد منوی زمینهدستور Add trendline را انتخاب کنید. کادر محاوره‌ای Trendline با باز شدن تب Type روی صفحه ظاهر می‌شود (شکل 1).

پس از آن شما نیاز دارید:

در تب Type، نوع خط روند مورد نیاز را انتخاب کنید (خطی به طور پیش فرض انتخاب شده است). برای نوع Polynomial در قسمت Degree درجه چند جمله ای انتخاب شده را مشخص کنید.

در صورت لزوم با رفتن به تب Parameters (شکل 2) می توانید پارامترهای زیر را برای خط روند تنظیم کنید:

• نام خط روند را در نام فیلد منحنی تقریبی (صاف) تغییر دهید.
• تعداد دوره ها (به جلو یا عقب) را برای پیش بینی در قسمت Forecast تنظیم کنید.
• معادله خط روند را در ناحیه نمودار نمایش دهید، که برای آن باید چک باکس نمایش معادله در نمودار را فعال کنید.
• مقدار قابلیت اطمینان تقریبی R2 را در ناحیه نمودار نمایش دهید، که برای آن باید چک باکس را فعال کنید و مقدار قابلیت اطمینان تقریبی (R^2) را روی نمودار قرار دهید.
• نقطه تقاطع خط روند را با محور Y تنظیم کنید، که برای آن باید چک باکس را برای تقاطع منحنی با محور Y در یک نقطه فعال کنید.
• روی دکمه OK کلیک کنید تا کادر محاوره ای بسته شود.

کادر محاوره‌ای Format Trendline بر روی صفحه ظاهر می‌شود (شکل 3)، که شامل سه زبانه است: View، Type، Parameters، و محتویات دو مورد آخر کاملاً با زبانه‌های مشابه کادر گفتگوی Trendline مطابقت دارد (شکل 1-2). ). در تب View می توانید نوع خط، رنگ و ضخامت آن را تعیین کنید.

برای حذف یک خط روند از قبل ساخته شده، خط روندی که باید حذف شود را انتخاب کنید و کلید Delete را فشار دهید.

مزایای ابزار تحلیل رگرسیون در نظر گرفته شده عبارتند از:

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

شما نمی توانید خطوط روند را به سری های داده در نمودارهای 3-D، Standard، Radar، Pie و Donut اضافه کنید.

استفاده از توابع داخلی اکسل

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

اکسل چندین توابع برای ایجاد رگرسیون خطی دارد، به ویژه:

• روند؛
• LINEST;
• SLOPE و CUT.

و همچنین چندین تابع برای ساخت یک خط روند نمایی، به ویژه:

• HEIGHT;
• LGRFPحدود

مزایای ابزار توابع داخلی برای تحلیل رگرسیون عبارتند از:

• یک فرآیند نسبتاً ساده از همان نوع تشکیل سری داده های مشخصه مورد مطالعه برای همه توابع آماری داخلی که خطوط روند را تعیین می کنند.
• یک تکنیک استاندارد برای ساخت خطوط روند بر اساس سری داده های تولید شده؛
• توانایی پیش بینی رفتار فرآیند مورد مطالعه برای تعداد مورد نیاز گام به جلو یا عقب.

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

نمونه هایی از حل مسائل خاص

حل مشکلات خاص را با استفاده از ابزارهای فهرست شده بسته اکسل در نظر بگیرید.

وظیفه 1

با جدولی از داده ها در مورد سود یک شرکت حمل و نقل موتوری برای سال 1995-2002. باید موارد زیر را انجام دهید

1. یک نمودار بسازید.
2. خطوط روند خطی و چند جمله ای (دو و مکعبی) را به نمودار اضافه کنید.
3. با استفاده از معادلات خط روند، داده های جدولی در مورد سود شرکت برای هر خط روند برای 1995-2004 به دست آورید.
4. برای سال های 2003 و 2004 سود شرکت را پیش بینی کنید.

راه حل مشکل

1. در محدوده سلول های A4:C11 کاربرگ اکسل، کاربرگ نشان داده شده در شکل را وارد می کنیم. 4.
2. با انتخاب محدوده سلول های B4:C11، نموداری می سازیم.
3. نمودار ساخته شده را فعال می کنیم و با استفاده از روشی که در بالا توضیح داده شد، پس از انتخاب نوع خط روند در کادر محاوره ای خط روند (نگاه کنید به شکل 1)، به طور متناوب خطوط روند خطی، درجه دوم و مکعبی را به نمودار اضافه می کنیم. در همان کادر محاوره‌ای، تب پارامترها را باز کنید (شکل 2 را ببینید)، در نام فیلد منحنی تقریبی (صاف) نام روندی که باید اضافه شود را وارد کنید و در قسمت Forecast forward for: periods را تنظیم کنید. مقدار 2، زیرا قرار است پیش بینی سود برای دو سال آینده انجام شود. برای نمایش معادله رگرسیون و مقدار قابلیت اطمینان تقریبی R2 در ناحیه نمودار، کادرهای بررسی Show the equation on the screen را فعال کنید و مقدار قابلیت اطمینان تقریبی (R^2) را روی نمودار قرار دهید. برای درک بصری بهتر، نوع، رنگ و ضخامت خطوط روند رسم شده را تغییر می دهیم، که برای آن از تب View در کادر محاوره ای Trend Line Format استفاده می کنیم (شکل 3 را ببینید). نمودار حاصل با خطوط روند اضافه شده در شکل نشان داده شده است. 5.
4. برای به دست آوردن داده های جدولی در مورد سود شرکت برای هر خط روند برای 1995-2004. بیایید از معادلات خطوط روند ارائه شده در شکل استفاده کنیم. 5. برای این کار در سلول های محدوده D3: F3 وارد کنید اطلاعات متنیدر مورد نوع خط روند انتخابی: روند خطی، روند چهارگانه، روند مکعبی. سپس فرمول رگرسیون خطی را در سلول D4 وارد کرده و با استفاده از نشانگر پر، این فرمول c را کپی کنید پیوندهای نسبیبه محدوده سلول های D5:D13. لازم به ذکر است که هر سلول با فرمول رگرسیون خطی از محدوده سلول های D4:D13 دارای یک سلول مربوطه از محدوده A4:A13 به عنوان آرگومان است. به طور مشابه، برای رگرسیون درجه دوم، محدوده سلولی E4:E13 و برای رگرسیون مکعبی، محدوده سلولی F4:F13 پر می شود. بنابراین پیش‌بینی سود شرکت برای سال‌های 2003 و 2004 انجام شد. با سه گرایش جدول مقادیر به دست آمده در شکل نشان داده شده است. 6.

وظیفه 2

1. یک نمودار بسازید.
2. خطوط روند لگاریتمی، نمایی و نمایی را به نمودار اضافه کنید.
3. معادلات خطوط روند به دست آمده و همچنین مقادیر قابلیت اطمینان تقریبی R2 را برای هر یک از آنها استخراج کنید.
4. با استفاده از معادلات خط روند، داده های جدولی در مورد سود شرکت برای هر خط روند برای 1995-2002 به دست آورید.
5. با استفاده از این خطوط روند، سود سال های 2003 و 2004 را برای کسب و کار پیش بینی کنید.

راه حل مشکل

با پیروی از روش ارائه شده در حل مسئله 1، نموداری با خطوط روند لگاریتمی، نمایی و نمایی اضافه شده به دست می آوریم (شکل 7). علاوه بر این، با استفاده از معادلات خط روند به‌دست‌آمده، جدول مقادیر سود شرکت را شامل مقادیر پیش‌بینی‌شده برای سال‌های 2003 و 2004 پر می‌کنیم. (شکل 8).

روی انجیر 5 و شکل مشاهده می شود که مدل با روند لگاریتمی با کمترین مقدار پایایی تقریبی مطابقت دارد.

R2 = 0.8659

بالاترین مقادیر R2 مربوط به مدل هایی با روند چند جمله ای است: درجه دوم (R2 = 0.9263) و مکعب (R2 = 0.933).

وظیفه 3

با جدول داده‌های سود یک شرکت حمل و نقل موتوری برای سال‌های 1995-2002، که در کار 1 ارائه شده است، باید مراحل زیر را انجام دهید.

1. با استفاده از توابع TREND و GROW سری داده ها را برای خطوط روند خطی و نمایی دریافت کنید.
2. با استفاده از توابع TREND و GROWTH، برای سال 2003 و 2004 سود شرکت را پیش بینی کنید.
3. برای داده های اولیه و سری داده های دریافتی، یک نمودار بسازید.

راه حل مشکل

بیایید از کاربرگ وظیفه 1 استفاده کنیم (شکل 4 را ببینید). بیایید با تابع TREND شروع کنیم:

1. محدوده سلول های D4:D11 را انتخاب کنید که باید با مقادیر تابع TREND مربوط به داده های شناخته شده در مورد سود شرکت پر شود.
2. دستور Function را از منوی Insert فراخوانی کنید. در کادر محاوره‌ای Function Wizard که ظاهر می‌شود، تابع TREND را از دسته Statistical انتخاب کنید و سپس روی دکمه OK کلیک کنید. همین عملیات را می توان با فشار دادن دکمه (عملکرد Insert) انجام داد. پنل استانداردابزار.
3. در کادر محاوره‌ای Function Arguments که ظاهر می‌شود، محدوده سلول‌های C4:C11 را در قسمت Known_values_y وارد کنید. در قسمت Known_values_x - محدوده سلول های B4:B11;
4. برای تبدیل فرمول وارد شده به فرمول آرایه از کلید ترکیبی + + استفاده کنید.

در نتیجه، محدوده سلول های D4:D11 با مقادیر مربوط به تابع TREND پر می شود (شکل 9).

1. محدوده سلول های D12:D13 را انتخاب کنید، جایی که مقادیر پیش بینی شده توسط تابع TREND وارد می شود.
2. تابع TREND را فراخوانی کنید و در کادر محاوره ای Function Arguments که ظاهر می شود، در قسمت Known_values_y - محدوده سلول های C4:C11 را وارد کنید. در قسمت Known_values_x - محدوده سلول های B4:B11; و در فیلد New_values_x - محدوده سلول های B12:B13.
3. این فرمول را با استفاده از ترکیب به فرمول آرایه تبدیل کنید کلیدهای Ctrl+ Shift + Enter.
4. فرمول وارد شده به صورت زیر خواهد بود: =(TREND(C4:C11;B4:B11;B12:B13))، و محدوده سلول های D12:D13 با مقادیر پیش بینی شده تابع TREND پر می شود (شکل 1 را ببینید). 9).

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

شکل 10 جدول را در حالت نمایش فرمول نشان می دهد.

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

وظیفه 4

با جدولی از داده ها در مورد دریافت برنامه های خدمات توسط سرویس اعزام یک شرکت حمل و نقل موتوری برای دوره 1 تا 11 ماه جاریباید موارد زیر را انجام دهید

1. دریافت سری داده ها برای رگرسیون خطی:با استفاده از توابع SLOPE و INTERCEPT. با استفاده از تابع LINEST
2. یک سری داده برای رگرسیون نماییبا استفاده از تابع LGRFPRIB
3. با استفاده از توابع فوق، پیش بینی دریافت درخواست ها به سرویس اعزام را برای بازه زمانی دوازدهم تا چهاردهم ماه جاری انجام دهید.
4. برای سری داده های اصلی و دریافتی، یک نمودار بسازید.

راه حل مشکل

توجه داشته باشید که برخلاف توابع TREND و GROW، هیچ یک از توابع ذکر شده در بالا (SLOPE، INTERCEPTION، LINEST، LGRFPRIB) رگرسیون نیستند. این توابع فقط نقش کمکی ایفا می کنند و پارامترهای رگرسیون لازم را تعیین می کنند.

برای رگرسیون های خطی و نمایی ساخته شده با استفاده از توابع SLOPE، INTERCEPT، LINEST، LGRFPRIB، ظاهرمعادلات آنها بر خلاف رگرسیون های خطی و نمایی مربوط به توابع TREND و GROWTH همیشه شناخته شده است.

1 . بیایید یک رگرسیون خطی بسازیم که دارای معادله باشد:

y=mx+b

برای انجام این کار، اقدامات زیر را انجام می دهیم:

1. جدول منبع را در محدوده سلول های A4:B14 وارد کنید.
2. مقدار پارامتر m در سلول C19 تعیین می شود. از دسته آماری تابع Slope را انتخاب کنید. محدوده سلول‌های B4:B14 را در قسمت Known_values_y و محدوده سلول‌های A4:A14 را در قسمت Known_values_x وارد کنید. فرمول در سلول C19 وارد می شود: =SLOPE(B4:B14;A4:A14);
3. با استفاده از روش مشابه، مقدار پارامتر b در سلول D19 تعیین می شود. و محتوای آن به این صورت خواهد بود: = INTERCEPT(B4:B14;A4:A14).بنابراین، مقادیر پارامترهای m و b لازم برای ساخت یک رگرسیون خطی، به ترتیب در سلول‌های C19، D19 ذخیره می‌شوند.
4. سپس فرمول رگرسیون خطی را در سلول C4 به شکل = $ C * A4 + $ D وارد می کنیم. در این فرمول، سلول های C19 و D19 با ارجاع مطلق نوشته می شوند (آدرس سلول نباید با کپی کردن احتمالی تغییر کند). علامت مرجع مطلق $ را می توان پس از قرار دادن مکان نما روی آدرس سلول یا از صفحه کلید یا با استفاده از کلید F4 تایپ کرد. با استفاده از دسته پر، این فرمول را در محدوده سلول های C4:C17 کپی کنید. سری داده های مورد نظر را بدست می آوریم (شکل 12). با توجه به اینکه تعداد درخواست ها یک عدد صحیح است، باید فرمت اعداد را در تب Number پنجره Cell Format با تعداد ارقام اعشار روی 0 قرار دهید.

y=mx+b

برای این:

1. تابع LINEST را به عنوان یک فرمول آرایه در محدوده سلول های C20:D20: =(LINEST(B4:B14;A4:A14) وارد کنید. در نتیجه، مقدار پارامتر m را در سلول C20 و مقدار پارامتر b را در سلول D20 دریافت می کنیم.
2. فرمول را در سلول D4 وارد کنید: =$C*A4+$D;
3. این فرمول را با استفاده از نشانگر پر در محدوده سلول های D4:D17 کپی کنید و سری داده های مورد نظر را دریافت کنید.

y=bmx

روی انجیر 13 جدولی را نشان می دهد که در آن می توانیم توابعی را که با محدوده سلولی لازم استفاده می کنیم و همچنین فرمول ها را مشاهده کنیم.

برای داده های اولیه و سری داده های به دست آمده، نمودار نشان داده شده در شکل. 14.

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

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

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

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

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

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

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

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

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

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

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

تقریب خطی

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

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

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

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

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

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

y = 4.503x + 6.1333

• که در آن 4.503 نشانگر شیب است.
• 6.1333 - افست.
• y دنباله ای از مقادیر است،
• x عدد دوره است.

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

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

خط روند نمایی

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

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

یک نمودار می سازیم. یک خط نمایی اضافه کنید.

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

y = 7.6403е^-0.084x

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

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

لاگین Trendline در اکسل

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

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

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

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

مثلا:

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

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

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

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

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

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

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

خط روند در اکسل. فرآیند ساخت

خط روند یکی از ابزارهای اصلی تجزیه و تحلیل داده ها است.

برای تشکیل یک خط روند، باید سه مرحله را تکمیل کنید، یعنی:
1. یک جدول ایجاد کنید.
2.
3. نوع خط روند را انتخاب کنید.

پس از جمع آوری همه اطلاعات لازم، می توانید مستقیماً نسبت به اجرای مراحل در مسیر رسیدن به نتیجه نهایی اقدام کنید.

اولین مرحله ایجاد جدول با داده های اصلی است. سپس محدوده مورد نیاز را انتخاب کرده و با رفتن به تب "Insert" تابع "Chart" را انتخاب کنید. پس از ساخت، نتیجه نهایی را می توان اعمال کرد ویژگی های اضافی، به صورت سربرگ و همچنین امضا. برای این کار کافیست بر روی نمودار کلیک چپ کرده، تبی به نام "Constructor" را انتخاب کرده و "Layout" را انتخاب کنید. مرحله بعدی این است که به سادگی عنوان را وارد کنید.

قدم بعدی ساخت خط روند خود است. بنابراین، برای این کار، باید دوباره نمودار را انتخاب کنید و برگه "Layout" را در نوار وظیفه انتخاب کنید. بعد، در این منو، باید روی دکمه "خط روند"و انتخاب کنید" تقریب خطی "یا" تقریب نمایی " کلیک کنید.

تغییرات مختلف lخطوط روند

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

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

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

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

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

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

چگونه استفاده می شود؟ Dبرای پیش‌بینی گزینه‌های اقتصادی، دقیقاً از آن استفاده می‌شود چند جمله ایخطی که درجه چند جمله ای آن بر اساس چندین اصل تعیین می شود: حداکثر کردن ضریب تعیین و همچنین پویایی اقتصادی شاخص در دوره ای که برای آن پیش بینی لازم است.

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

خط روند در اکسل. تنظیم پارامتردر خط عملکردی

با کلیک بر روی دکمه "Trendline" منوی لازم به نام" را انتخاب کنید. گزینه های اضافی". در پنجره ای که ظاهر می شود، روی " Format line trend" کلیک کنید و سپس یک علامت را در مقابل مقدار "Put on chart the value of approximation reliability R ^ 2" قرار دهید. پس از آن، منو را با کلیک بر روی دکمه مربوطه ببندید. ضریب R^2= 0.6442 در خود نمودار ظاهر می شود.

پس از آن تغییرات معرفی شده را لغو می کنیم. پس از انتخاب نمودار و کلیک بر روی تب "Layout"، سپس روی "Trendline" کلیک کنید و روی "No" کلیک کنید. بعد، با رفتن به تابع "Trendline Format"، روی آن کلیک کنید چند جمله ایخط و سعی کنید با تغییر درجه به مقدار R^2= 0.8321 برسید.

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

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

تقریب خطی

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

y = 4.503x + 6.1333

• 6.1333 - افست.
• x عدد دوره است.

y = 7.6403е^-0.084x

مثلا:

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

خط روند در اکسل

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

توطئه

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

1. ما در حال ساخت جدولی هستیم که در آن یک ستون شامل دوره های زمانی (در مورد ما، تاریخ ها) و ستون دیگر حاوی مقداری است که پویایی آن در نمودار نمایش داده می شود.
2. این جدول را انتخاب کنید. به تب "Insert" بروید. در آنجا، روی نوار در بلوک ابزار "نمودارها"، روی دکمه "گراف" کلیک کنید. از لیست ارائه شده، اولین گزینه را انتخاب کنید.
3. پس از آن، برنامه زمان بندی ساخته می شود، اما هنوز باید نهایی شود. ساخت عنوان نمودار. برای این کار روی آن کلیک کنید. در گروه برگه های «کار با نمودارها» که ظاهر می شود، به برگه «طرح بندی» بروید. در آن، روی دکمه "نام نمودار" کلیک کنید. در لیستی که باز می شود، مورد "در بالای نمودار" را انتخاب کنید.
4. در قسمتی که بالای نمودار ظاهر می شود، نامی را که مناسب می دانیم وارد کنید.
5. سپس محورها را امضا می کنیم. در همان تب Layout، روی دکمه روی نوار "Axis Names" کلیک کنید. ما به ترتیب موارد "نام محور افقی اصلی" و "نام زیر محور" را مرور می کنیم.
6. در فیلدی که ظاهر می شود، نام محور افقی را با توجه به زمینه داده های موجود در آن وارد کنید.
7. برای اختصاص یک نام به محور عمودی، از تب "Layout" نیز استفاده می کنیم. روی دکمه "Axis Names" کلیک کنید. به صورت متوالی در میان آیتم های منوی بازشو "نام محور عمودی اصلی" و "عنوان چرخشی" حرکت کنید. این نوع مکان نام محور است که برای نوع نمودارهای ما راحت تر خواهد بود.
8. در قسمت ظاهر شده برای نام محور عمودی، نام مورد نظر را وارد کنید.

درس: نحوه ساخت نمودار در اکسل

ایجاد خط روند

اکنون باید مستقیماً خط روند را اضافه کنید.

1. با قرار گرفتن در تب "Layout"، روی دکمه "Trend Line" که در بلوک ابزار "Analysis" قرار دارد، کلیک کنید. از لیستی که باز می شود، مورد "تقریبا نمایی" یا "تقریبا خطی" را انتخاب کنید.
2. پس از آن، یک خط روند به نمودار اضافه می شود. به طور پیش فرض سیاه است.

تنظیم خط روند

احتمالش وجود داره تنظیمات اضافیخطوط

1. به طور متوالی از طریق آیتم های منو "Analysis"، "Trend line" و "Additional line trend options..." به تب "Layout" بروید.
2. پنجره پارامتر باز می شود و می توان تنظیمات مختلفی را انجام داد. به عنوان مثال، می توانید با انتخاب یکی از شش گزینه، نوع صاف کردن و نصب را تغییر دهید:
   • چند جمله ای
   • خطی؛
   • قدرت؛
   • لگاریتمی؛
   • نمایی;
   • فیلتر خطی

   برای تعیین قابلیت اطمینان مدل ما، کادر کنار مورد «مقدار پایایی تقریبی را در نمودار قرار دهید» علامت بزنید. برای مشاهده نتیجه، روی دکمه "بستن" کلیک کنید.

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

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

پیش بینی

وظیفه اصلی خط روند، توانایی پیش بینی پیشرفت های بعدی در آن است.

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

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

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

سوال خود را در نظرات بپرسید و ماهیت مشکل را با جزئیات شرح دهید. کارشناسان ما سعی خواهند کرد در اسرع وقت پاسخ دهند.

آیا این مقاله به شما کمک کرد؟

چرا نمودارها مورد نیاز است؟ برای "زیبا کردن"؟ به هیچ وجه - وظیفه اصلی نمودار این است که به شما امکان می دهد اعداد مبهم را به شکل گرافیکی ارائه دهید که به راحتی قابل درک باشد. به طوری که در یک نگاه وضعیت کار مشخص بود و نیازی به اتلاف وقت برای مطالعه آمار خشک نبود.

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

چگونه نمودارها و نمودارها ما را فریب می دهند

با این حال، نمودارها (مخصوصاً وقتی صحبت از نمایش بصری مقدار زیادی داده می شود)، اگرچه برای درک بسیار راحت است، اما همیشه واضح نیستند.

بگذارید با یک مثال ساده نظرم را توضیح دهم:

نمودار بر اساس جدول در MS Excel

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

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

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

آیا نمودار واضح است؟ کاملا. اما آیا واضح است؟ بیایید آن را بفهمیم.

درک روندها در MS Excel

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

در نمودار چه چیزی می بینیم؟ ستون های نارنجی «پاییز» حداقل «بهار» بیشتر نیستند، یا حتی کمتر. این نشان دهنده موفقیت نیست، بلکه برعکس است - بازدیدکنندگان وارد می شوند، اما به طور متوسط ​​کمتر مطالعه می کنند و در سایت معطل نمی شوند!

وقت آن است که زنگ خطر را به صدا در آوریم و ... با چیزی مانند خط روند.

چرا به خط روند نیاز دارید؟

خط روند "به روش ساده" یک خط پیوسته است که بر اساس مقادیر متوسط ​​​​بر اساس الگوریتم های خاصی که نمودار ما از آنها ساخته شده است ، جمع آوری شده است. به عبارت دیگر، اگر داده‌های ما از سه نقطه گزارش از «-5» به «0» و سپس به «5+» بپرد، در پایان یک خط تقریباً صاف خواهیم داشت: «مضافات» وضعیت. بدیهی است که "معایب" را متعادل کنید.

بر اساس جهت خط روند، دیدن آن بسیار ساده تر است موقعیت واقعیموارد و همین روندها را ببینید، و بنابراین - برای آینده پیش بینی کنید. حالا دست به کار شوید!

نحوه رسم خط روند در MS Excel

اضافه کردن به نمودار در MS خط اکسلروند

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

اکنون صفحه نمودار نمایش داده می شود خط نقطه چینروند. همانطور که می بینید، 100٪ با مقادیر نمودار منطبق نیست - بر اساس میانگین وزنی ساخته شده است، فقط به طور کلی جهت خود را تکرار می کند. با این حال، این مانع از آن نمی شود که شاهد افزایش مداوم تعداد بازدیدکنندگان از سایت باشیم - حتی کاهش "تابستانی" بر نتیجه کلی تأثیر نمی گذارد.

خط روند برای ستون "بازدید کنندگان".

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

خط روند دیگری به شما امکان می دهد وضعیت را روشن کنید

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

بنابراین، صاحب پروژه باید فوراً آنچه را که در تابستان امسال انجام داده است به یاد بیاورد ("در بهار"، با قضاوت بر اساس برنامه، همه چیز کاملاً عادی بود) و فوراً اقداماتی را برای اصلاح وضعیت انجام دهد.

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

خط روند در اکسل. فرآیند ساخت

خط روند یکی از ابزارهای اصلی تجزیه و تحلیل داده ها است.

برای تشکیل یک خط روند، لازم است سه مرحله را تکمیل کنید، یعنی:
1. یک جدول ایجاد کنید.
2. یک نمودار بسازید.
3. نوع خط روند را انتخاب کنید.

پس از جمع آوری تمامی اطلاعات لازم، می توانید مستقیماً نسبت به اجرای مراحل در جهت حصول نتیجه نهایی اقدام نمایید.

اولین مرحله ایجاد جدول با داده های اصلی است. سپس محدوده مورد نیاز را انتخاب کرده و با رفتن به تب "Insert" تابع "Chart" را انتخاب کنید. پس از ساخت، ویژگی های اضافی را می توان در نتیجه نهایی، به صورت سرفصل ها و همچنین امضاها، اعمال کرد. برای این کار کافیست بر روی نمودار کلیک چپ کرده، تبی به نام "Constructor" را انتخاب کرده و "Layout" را انتخاب کنید. مرحله بعدی این است که به سادگی عنوان را وارد کنید.

قدم بعدی ساخت خط روند خود است. بنابراین، برای این کار، باید دوباره نمودار را انتخاب کنید و برگه "Layout" را در نوار وظیفه انتخاب کنید. در مرحله بعد، در این منو، باید روی دکمه «خط روند» کلیک کنید و «تقریبا خطی» یا «تقریبا نمایی» را انتخاب کنید.

تغییرات مختلف lخطوط روند

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

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

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

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

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

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

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

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

خط روند در اکسل. تنظیم پارامتردر خط عملکردی

با کلیک بر روی دکمه "Trend Line" منوی مورد نیاز به نام "Advanced Options" را انتخاب کنید. در پنجره ای که ظاهر می شود، روی "Trend Line Format" کلیک کنید و سپس یک علامت را در مقابل مقدار "put on chart the value of approximation reliability R ^ 2" قرار دهید. پس از آن، منو را با کلیک بر روی دکمه مربوطه ببندید. ضریب R^2= 0.6442 در خود نمودار ظاهر می شود.

پس از آن تغییرات معرفی شده را لغو می کنیم. پس از انتخاب نمودار و کلیک بر روی تب "Layout"، سپس روی "Trend Line" کلیک کرده و روی "None" کلیک کنید. در مرحله بعد، با رفتن به تابع "Trend Line Format"، روی خط چند جمله ای کلیک کنید و سعی کنید با تغییر درجه به مقدار R ^ 2 = 0.8321 برسید.

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

(بازدید شده 10 510 بار، 27 بازدید امروز)

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

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

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

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

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

1. بیایید یک نمودار بر اساس جدول بسازیم. محدوده را انتخاب کنید - به برگه "درج" بروید. از بین انواع نمودارهای پیشنهادی، یک نمودار ساده را انتخاب می کنیم. افقی - سال، عمودی - قیمت.
2. روی خود نمودار کلیک راست کنید. روی «افزودن خط روند» کلیک کنید.
3. پنجره ای برای پیکربندی پارامترهای خط باز می شود. بیایید نوع خطی را انتخاب کنیم و مقدار قابلیت اطمینان تقریبی را روی نمودار قرار دهیم.
4. یک خط اریب روی نمودار ظاهر می شود.

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

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

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

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

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

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

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

تقریب خطی

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

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

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

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

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

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

y = 4.503x + 6.1333

• که در آن 4.503 نشانگر شیب است.
• 6.1333 - افست.
• y دنباله ای از مقادیر است،
• x عدد دوره است.

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

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

خط روند نمایی

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

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

یک نمودار می سازیم. یک خط نمایی اضافه کنید.

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

y = 7.6403е^-0.084x

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

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

لاگین Trendline در اکسل

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

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

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

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

مثلا:

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

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

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

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

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

دانلود نمونه نمودارها با خط روند

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

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

1) روند چیست (این به عنوان نقطه شروع مهم است)
2) ایجاد خطوط روند
3) استفاده در تجارت عملی
4) موضوعی بودن روش

1) روند چیست
_________________
قبل از اقدام به ساخت خط روند، لازم است مستقیماً با خود روند برخورد کنید. ما وارد بحث های آکادمیک نمی شویم و برای سادگی فرمول زیر را می پذیریم:

روند (به سمت بالا) دنباله ای از بالا و پایین های صعودی است که هر بالا (و پایین) بعدی بالاتر از موارد قبلی است.

روند (نزولی) دنباله ای از نزولی (کاهش) اوج و پایین است، که در آن هر پایین (و بالا) بعدی کمتر از قبلی است.

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

2) ایجاد خطوط روند
____________________________

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

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

رعایت قوانین زیر هنگام ترسیم خطوط مهم است:

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

این قوانین به شما کمک می کند خطوط روند را به درستی بسازید!

3) با خطوط روند معامله کنید
____________________________

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

هر دو روش خوب هستند اگر بدانید چگونه آنها را به درستی بپزید.

بنابراین، ما یک خط در دو نقطه ایجاد کرده ایم. به محض اینکه قیمت به خط رسید، باید در جهت روند موجود وارد بازار شویم. برای ورود، از سفارش‌هایی از نوع «محدودیت خرید یا حد فروش» استفاده می‌کنیم.

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

اگر می‌خواهیم روی شکستن خط روند بازی کنیم، باید کمی متفاوت عمل کنیم:
1) منتظر لمس خط هستیم
2) ما منتظر یک ریباند هستیم
3) یک سفارش خرید توقف (یا توقف فروش) را در کادر انتخاب قرار دهید
به نقاشی توجه کنید.

ما منتظر ماندیم تا علامت چک ظاهر شود و یک سفارش توقف خرید را در حداکثر آن قرار دادیم.

بعد از مدتی سفارش عملی شد و وارد بازار شدیم.
ناشی می شود سوال مشروع- چرا ورود فوری به بازار غیرممکن بود؟
مسئله این است که ما نمی دانیم که آیا تست خط روند موفقیت آمیز خواهد بود یا خیر. و با منتظر ماندن برای "تیک"، ما به طور چشمگیری شانس موفقیت خود را افزایش می دهیم (سیگنال های نادرست را حذف می کنیم).

4) موضوعی بودن روش
_________________________

همه چیز ساده به نظر می رسد؟ در واقع با استفاده از این روش، با مشکلات زیر روبرو خواهیم شد:
الف) شیب خط (شما همیشه می توانید خطوط روند را با شیب های مختلف ترسیم کنید.
ب) چه چیزی شکست خط روند در نظر گرفته می شود (قیمت با چند نقطه یا درصد باید خط را بشکند تا این یک شکست در نظر گرفته شود)؟
ج) چه زمانی یک خط باید «منسوخ» در نظر گرفته شود و خط جدید ساخته شود؟

به نقاشی توجه کنید.

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

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

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

آیا در معاملات خود به این ابزار نیاز دارید - این به شما بستگی دارد!

موفق باشید و تجارت موفق باشید. آرتور
blog-forex.org

پست های مرتبط:

مفهوم تجارت روند (ویدئو)

الگوهای روند (شکل ها)

ویدیو برای این موضوع:

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

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

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

فرآیند انتخاب فرمول تجربی P(x)برای اعتیاد با تجربه F(x)تماس گرفت تقریب(صاف کردن). برای وابستگی‌هایی با یک مجهول، اکسل از نمودارها و برای وابستگی‌هایی با مجهولات زیاد، از جفت توابع از گروه استفاده می‌کند. آماری LINEST و TREND، LGRFP و GROWTH.

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

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

اکسل 5 نوع توابع تقریبی را ارائه می دهد:

1. خطی - y=cx+b. این ساده ترین عملکرد، که رشد و کاهش داده ها را با نرخ ثابتی منعکس می کند.

2. چند جمله ای – y=c0+c1x+c2x2+…+c6x6. تابع به طور متناوب افزایش و کاهش داده ها را توصیف می کند. یک چند جمله ای درجه 2 می تواند یک انتها داشته باشد (حداقل یا حداکثر)، یک چند جمله ای درجه 3 می تواند تا 2 منتهی داشته باشد، یک چند جمله ای درجه 4 می تواند تا 3 منتهی داشته باشد و غیره.

3. لگاریتمی - y=cلوگاریتم x+b. این تابع داده های به سرعت در حال افزایش (کاهش) را توصیف می کند که سپس تثبیت می شود.

4. توان - y=cxb, (ایکس> 0 و y> 0). این تابع داده ها را با نرخ رشد دائمی در حال افزایش (کاهش) نمایش می دهد.

5. نمایی – y=cebx, (هپایه لگاریتم طبیعی است). این تابع داده‌های در حال رشد (کاهش) سریع را توصیف می‌کند که سپس تثبیت می‌شود.

برای هر 5 نوع توابع، تقریب داده ها با روش حداقل مربعات استفاده می شود (به راهنمای F1 "خط روند" مراجعه کنید).

مانند مثالوابستگی فروش به تبلیغات را در نظر بگیرید که توسط آمار زیر برای یک شرکت خاص ارائه شده است:

ما باید یک تابع بسازیم بهترین راهمنعکس کننده این رابطه علاوه بر این، لازم است فروش برای سرمایه گذاری های تبلیغاتی 6 هزار روبل برآورد شود.

بریم سراغ راه حل. اول از همه، این داده ها را وارد اکسل کنید و یک نمودار بسازید، مانند شکل. 38. همانطور که می بینید، نمودار بر اساس محدوده B2:J2 است. در مرحله بعد، با کلیک راست بر روی نمودار، یک خط روند اضافه کنید، همانطور که در شکل نشان داده شده است. 38.

برای برچسب زدن محور X با مقادیر تبلیغاتی مربوطه (مانند شکل 38)، مورد را در منوی کشویی انتخاب کنید (شکل 38). وداده های مشابه. در پنجره باز شده به همین نام، در تب ردیف، در زمینه پبرچسب های محور x، محدوده سلول هایی را که مقادیر X نوشته شده است مشخص کنید (اینجا $B$1:$K$1):

در پنجره تنظیمات که باز می شود (شکل 39)، در زبانه تایپ کنیدبرای تقریب انتخاب کنید لگاریتمیخط روند (با توجه به نوع نمودار). نشانک گزینه هاکادرهایی را که معادله و ضریب تعیین را روی نمودار نشان می دهند علامت بزنید.

پس از فشار دادن خوبنتیجه ای مانند شکل بدست خواهید آورد. 40. ضریب تعیین R2= 0.9846 که درجه خوبی از نزدیکی است. برای تأیید صحت عملکرد انتخاب شده (از آنجایی که ملاحظات نظری دیگری وجود ندارد)، توسعه فروش را برای 10 دوره آینده پیش بینی کنید. برای این کار روی خط روند - تغییر فرمت - پس از آن در فیلد راست کلیک کنید پیش بینی: فوروارد توسط:مجموعه 10 (شکل

پس از تنظیم پیش بینی، تغییر منحنی نمودار را برای 10 دوره مشاهده پیش رو خواهید دید، مانند شکل. 42. به احتمال زیاد افزایش بیشتر در فروش را با افزایش سرمایه گذاری های تبلیغاتی منعکس می کند.

محاسبه طبق فرمول به دست آمده \u003d 237.96 * LN (6) + 5.9606 در اکسل مقدار 432 هزار روبل می دهد.

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

تابع پیش بینی برای مثال ما این خواهد بود: =PREDICTION(K1;B2:J2;B1:J1). آن را بنویسید - باید ارزش 643.6 هزار روبل دریافت کنید.

قسمت 11. کنترل وظایف

قبلی12345678910111213141516بعدی