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

پیش بینی با روش حدقل مجذورات و رگرسیون

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

در این درس با روش‌‌های رگرسیون خطی یک متغیره و چند متغیره آشنا می‌شویم که برای کشف رابطه‌ی یک علت و معلول (مثلاً تعداد تراکت و تعداد مشتریان) یا چند علت و یک معلول (مثلاً تأثیر قیمت محصول و هزینه بازاریابی روی تعداد مشتریان) به کار گرفته می‌شوند و این رابطه را به شکل معادله‌ی ریاضی نمایش می‌دهند.

یادآوری کوتاه در مورد متغیرهای مستقل و وابسته

به نظر شما چه عواملی باعث موفقیت یک ازدواج می‌شوند؟ صداقت؟ وفاداری؟ پول؟  اگر این سه پارامتر موثر باشند یعنی موفقیت در ازدواج معلول است و علت‌های آن صداقت و وفاداری و پول هستند، از طرفی صداقت و وفاداری و پول ظاهراً مستقل هستند و ربطی به هم ندارند چون شاید کسی پولدار باشد اما صادق نباشد یا صادق باشد اما پول‌دار نباشد و … پس این‌ها متغیر مستقل هستند (علت‌ها) و موفقیت در ازدواج متغیر وابسته است (معلول).

اگر بخواهیم موفقیت در ازدواج و دلایل آن را با روابط ریاضی نشان دهیم، شبیه زیر خواهد بود:

موفقیت در ازدواج

اما مسأله اینجاست که احتمالاً تأثیر پول و وفاداری و صداقت اندازه‌ی هم نیست و برای این‌که میزان تأثیر هر کدام مشخص شود باید از ضرایب استفاده کنیم:

ضرایب A و B و C میزان اهمیت هر یک از پارامترهای صداقت و وفاداری و پول را نمایش می‌دهند، مثلاً شاید ضرایب A و B عدد چهار و ضریب C عدد یک باشد.

یادآوری کوتاه در مورد معادلات خطی و غیرخطی

ما گفتیم موفقیت در ازدواج با چند پارامتر در ارتباط است اما در بخش قبلی فرض کردیم که این ارتباط خطی است، یعنی متغیرها همگی درجه‌ی یک بودند اما شاید یک معادله شبیه زیر باشد:

در این حالت اگر نمودار Y را رسم کنیم خطی نیست و به همین علت به چنین معادلاتی غیر خطی گفته می‌شود.

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

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

در این درس روابط علت و معلولی را در حالت‌های زیر بررسی می‌کنیم:

حالت اول: یک علت و یک معلول

# روش کمترین مجذورات یا رگرسیون خطی یک متغیره

در این حالت فرض می‌کنیم که فقط یک علت (متغیر مستقل) روی معلول (متغیر وابسته) اثر می‌گذارد و با روش کمترین مجذورات، رابطه‌ی ریاضی بین آن‌ها را پیدا می‌کنیم. در ادامه از یک مثال استفاده کردیم و فرض‌مان این است که قیمت محصول تنها عامل تأثیرگذار روی میزان فروش باشد؛ فرض کنید به اطلاعات زیر دسترسی داریم که میزان فروش همان پارامتر وابسته (مثلاً y) و قیمت فروش همان پارامتر مستقل (مثلاً x) است.

زمانی پیش‌بینی خطی دو پارامتر دقیق است که رابطه‌ی آن‌ها خطی باشد، پس نمودار مربوط به این جدول را ترسیم می‌کنیم تا خطی بودن آن را بررسی کنیم:

همان‌طور که می‌بینید رفتار x و y تا حد بسیار زیادی خطی است و می‌توانیم با اطمینان خاطر از روش کمترین مجذورات استفاده کنیم.

روش دستی

می‌خواهیم با روش کمترین مجذورات، رابطه‌ی قیمت و میزان فروش را پیش‌بینی کنیم که با فرض خطی بودن به شکل F=a+bX خواهد بود و باید مقادیر a و b را پیدا کنیم تا رابطه‌ی میان آن‌ها پیدا شود، ضمناً F مقدار فروش پیش‌بینی شده است و از Y استفاده نکردیم تا با مقادیر واقعی اشتباه نشود، مثلاً می‌دانیم که وقتی  X=4000 مقدار فروش یا Y برابر با ۸۰۰ است اما شاید فروش پیش‌بینی شده (F) برابر با عدد دیگری مثل ۷۹۹ باشد؛ پس در ادامه می‌خواهیم مقادیر a و b را پیدا کنیم تا مقدار فروش برای مقادیر مختلف X پیش‌بینی شود و مقدارشان بر اساس روش مجذورات مطابق رابطه‌ی زیر است:

روابط مربوط به پیش‌بینی با حدأقل مجذورات

برای استفاده از روابط بالا، جدول را به‌صورت زیر تکمیل می‌کنیم:

با جاگذاری مقادیر در روابط a و b داریم:

پس میزان فروش پیش‌بینی شده (F) به ازای قیمت فروش (X) برابر است با:

اگر در معادله‌ی پیش‌بینی شده، مقدار X را ۴۰۰۰ قرار دهیم به حدود ۷۱۱ فروش می‌رسیم که با مقدار اصلی (۸۰۰) فاصله دارد و اگر قیمت محصول را ۵۰۰ تومان انتخاب کنیم (x=500) انتظار می‌رود که  ۳۶۰۷ واحد فروش داشته باشیم.

ضریب تعیین و کاربرد آن در پیش‌بینی

برای ارزیابی رابطه‌ی پیش‌بینی شده می‌توانیم از ضریب همبستگی (r) و ضریب تعیین (مجذور r) استفاده کنیم که حاکی از رابطه‌ی علت و معلولی نیستند اما ارتباط ظاهری پارامترها را می‌سنجند و می‌توانند مبنای قضاوت باشند. در حالت خطی می‌توانیم ضریب همبستگی را از رابطه‌ی زیر حساب کنیم اما در ادامه‌ی روش‌ها ترجیح‌مان این است که این مقادیر را توسط اکسل استخراج کنیم:

ضریب همبستگی

ضریب همبستگی (r) همیشه بین -۱ و ۱ است و هر چقدر به صفر نزدیک‌تر باشد، یعنی رفتار پارامترها هم‌خوانی کمتری داشته است؛ همچنین مقدار مثبت r نشان‌دهنده‌ی رابطه‌ی مستقیم پارامترها و مقدار منفی نشان‌دهنده رابطه‌ی معکوس میان آن‌هاست.

با مجذور شدن مقدار r به شاخص دیگری به‌نام ضریب تعیین می‌رسیم که نشان می‌دهد رفتار پارامتر وابسته چقدر با رفتار پارامتر مستقل هم‌خوانی داشته است، مثلاً مقدار ۰٫۸ یعنی ظاهراً پارامتر وابسته تا ۸۰ درصد از تغییرات پارامتر مستقل تبعیت کرده و احتمالاً تأثیر پارامترهای دیگر معادل ۲۰ درصد بوده است اما تأکید می‌کنیم که این نتایج از روی ظاهر اعداد است و شاید عملاً چنین ارتباط عمیقی بین پارامترها وجود نداشته باشد.

معمولاً زمانی که مقدار ضریب تعیین از ۰٫۶ یا ۰٫۵ کمتر می‌شود به این معناست که رابطه‌ی پیش‌بینی شده چندان گویای واقعیت نیست و باید پیش‌بینی را با روش دیگر یا با پارامترهای دیگر تکرار کنیم؛ در درس خطاهای پیش‌بینی به موارد کامل‌تری در این خصوص اشاره خواهیم کرد.

استفاده از روش حدأقل مجذورات برای سری‌های زمانی

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

در درس‌های بعدی با تکنیک‌های پیش‌بینی سری‌های زمانی آشنا می‌شوید اما روش کمترین مجذورات هم می‌تواند برای تحلیل آن‌ها به کار گرفته شود و برای این‌کار ابتدا دوره‌های زمانی را شماره‌گذاری می‌کنیم:

یعنی به‌جای مهر ماه از شماره‌ی دوره‌ی زمانی استفاده کردیم، حالا فرض می‌کنیم که شماره‌ی دوره‌های زمانی (X) متغیر مستقل است و بر این اساس به‌ازای X=6 داریم Y=10 و می‌توانیم با تعیین رابطه‌ی بین X و Y، مقدار فروش آینده را برای دوره‌های زمانی مختلف (مثلاً X=16) پیش‌بینی کنیم.

پیش‌بینی معادلات خطی یک متغیره با Trend-line‌های اکسل

دو ستون X و Y را در اکسل وارد کنیم:

در منو روی Insert کلیک کرده و در بخش Charts روی Scatter  کلیک می‌کنیم:

روی جعبه‌ی سفید باز شده کلیک رایت کرده و گزینه‌ی Select Data را انتخاب می‌کنیم:

در پنجره‌ی باز شده اگر در باکس‌های چپ یا راست چیزی وجود دارد با کمک دکمه‌ی Remove تمام‌شان را پاک می‌کنیم، سپس روی Add کلیک می‌کنیم:

در پنجره‌ی باز شده، فیلد اول برای عنوان نمودار است که به دلخواه پر کنید، در فیلد دوم مقادیر عددی مربوط به X ها و در فیلد سوم مقادیر عددی مربوط به Y ها را انتخاب می‌کنیم:

مثلاً برای فیلد دوم، روی دکمه‌ی کوچک سمت راست آن کلیک می‌کنیم و از عدد ۴۰۰۰ تا ۱۰۰۰ را انتخاب می‌کنیم:

بعد از اینکه مقادیر y هم در فیلد سوم وارد کردیم، داده‌ها به‌صورت نقاطی در نمودار نمایش داده می‌شوند:

حالا باید از اکسل بخواهیم تا نمودار جایگزنی را برای پیش‌بینی رسم کند، پس روی نمودار کلیک می‌کنیم، سپس از منوی Design رویAdd Chart Element کلیک و منوی Trendline را باز می‌کنیم.

در این منو اگر روی Linear کلیک کنیم، معادله‌ی خطی پیش‌بینی می‌شود و Exponential برای پیش‌بینی نمایی است؛ اگر روی Linear کلیک کنیم به نمودار زیر میرسیم:

دوباره از Trendlines گزینه‌ی Exponential را انتخاب می‌کنیم تا نمودار آن هم اضافه شود:

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

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

حالت دوم. استفاده از معادله‌ی خطی با دو متغیر مستقل

# رگرسیون خطی دو متغیره

در تکمیل حالت اول، فرض می‌کنیم که پارامتر دیگری هم روی متغیر وابسته اثر می‌گذارد، یعنی دو علت داریم و یک معلول.

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

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

در این حالت، معادله‌ی فروش (F) پیش‌بینی شده شبیه زیر است و باید مقادیر a ، b1 و b2 را محاسبه کنیم.

در این حالت اگر نمودار را رسم کنیم به شکلی شبیه زیر می‌رسیم، در حقیقت به جز بعدهای x1 و y  ، یک بعد دیگر به‌نام x2 به نمودار اضافه می‌شود. (یادگیری این نمودار تأثیری در حل معادله ندارد و می‌توانید از آن عبور کنید)

هر یک از ضرایب a و b1 و b2 با روابط زیر محاسبه می‌شوند:

جدول را برای استفاده از این روابط تکمیل می‌کنیم:

داده‌ها ۷ ردیف هستند (n=7) و با جاگذاری مقادیر در روابط a و b و c به این نتایج می‌رسیم:

یکی از داده‌های جدول اصلی، X1=2000 و X2=1.5 است که به ازای آن‌ها Y=2400 واحد از محصول فروخته شده است.

اگر همین اطلاعات را در رابطه‌ی پیش‌بینی شده قرار دهیم با عدد ۲۵۸۴ می‌رسیم که کمتر از ۲۰۰ واحد با فروش واقعی فاصله دارد.

یا اگر فرض کنیم که قیمت محصول ۱۰۰۰۰ تومان (X1=10000) و هزینه‌های بازاریابی بیست میلیون تومان (X2=20) مقدار پیش‌بینی شده‌ی فروش معادل ۳۴۸۶ واحد می‌شود.

حل رگرسیون در اکسل

# قابل استفاده برای رگرسیون خطی با هر تعداد متغیر

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

فعال کردن تحلیل رگرسیون در اکسل

به‌صورت پیش‌فرض گزینه‌های حل به روش رگرسیون در اکسل فعال نیست، پس از منوی File به بخش Options بروید و در فهرست سمت چپ روی Add-ins کلیک کنید. در بخش Manage، گزینه‌ی Excel Add-ins را انتخاب کرده و روی Go  کلیک کنید:

در پنجره‌ای که باز می‌شود گزینه‌ی Analysis Toolpak را فعال کنید تا برای همیشه گزینه‌های مربوط به حل رگرسیون به اکسل اضافه شود؛ پس از این نیازی به انجام مجدد این تنظیمات نخواهید داشت.

مثال برای پیش‌بینی رگرسیون با چهار پارامتر مستقل

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

ابتدا اطلاعات را در اکسل وارد می‌کنیم:

در منوی Data روی Data Analysis کلیک کرده و در پنجره‌ی باز شده گزینه‌ی Regression را انتخاب می‌کنیم:

توجه کنید که گزینه‌ی Data Analysis در حالت پیش‌فرض روی اکسل فعال نیست و باید به روشی که بالاتر توضیح دادیم آن را فعال کرده باشید. پس از فشردن دکمه‌ی OK پنجره‌ی جدیدی باز می‌شود:

در منوی بالا روی Data کلیک کنید و در پنجره‌ی باز شده گزینه‌ی Regression را انتخاب و روی OK کلیک کنید. در فیلد اول باید مقادیر مربوط به y انتخاب شود:

در فیلد دوم باید مقادیر مربوط به x1 تا x4 را انتخاب کنیم:

تیک Labels را فعال کنید تا از عناوین X1 و X2 و … در تحلیل استفاده کند و در Output Range مشخص کنید که نتایج از کدام سلول نمایش داده شوند، در حقیقت سلول انتخاب شده گوشه‌ی بالا و سمت چپ برگه‌ی تحلیل خواهد بود:

با کلیک کردن روی OK نتایج تحلیل رگرسیون نمایش داده می‌شود:

در بخش R-Square عدد ۰٫۹۹۹  نشان می‌دهد که رابطه‌ی پیش‌بینی شده تقریباً نزدیک به صد درصد حقیقت را بازگو می‌کند و نتایج بسیار دقیق به نظر می‌رسند، کمی پایین‌تر می‌بینیم که مقادیری برای ضرایب Intercept و X4 تا X1 مشخص شده است که مقادیر a و b4 تا b1 هستند، یعنی مقدار ۱۶۸۷٫۱۵۹ برای intercept همان عدد a است و به‌صورت کلی داریم:

برای تست رابطه‌ی بالا می‌توانیم میزان فروش را برای قیمت ۲۰۰۰۰ تومان (x1=20000)، هزینه‌ی تبلیغات چاپی معادل ۸۰۰۰۰۰ تومان (x2=800000)، هزینه تبلیغاتی اینترنتی معادل ۲۰۰۰۰۰ تومان (X3=200000) و شش نمایندگی فروش (X4=6) محاسبه کنیم که ۲۳۸۴ واحد می‌شود که فقط ۱۶ واحد با ۲۴۰۰ اختلاف دارد و با دقت زیادی پیش‌بینی شده است.

شماره و نام درسی که هم‌اکنون خواندید:

درس پنجم. شناسایی رابطه‌ی ریاضی میان علت و معلول از دوره‌ی پیش‌بینی تقاضا

درس اولپیش بینی تقاضا چیست و چه کاربردی دارد؟
درس دومتفاوت روش‌های کمی و کیفی پیش بینی
درس سومروش‌های کیفی پیش‌بینی
درس چهارممقدمه‌ی روش‌های کمی
درس پنجم شناسایی رابطه‌ی ریاضی میان علت و معلول
درس ششمپیش بینی با روش‌های مبتنی بر میانگین
درس هفتمپیش‌بینی با روش هموارسازی نمایی (نمو هموار)
درس هشتمپیش‌بینی داده‌های فصلی
درس نهممعیارهایی برای سنجش دقت پیش‌بینی
درس دهممثال کاربردی از پیش‌بینی با اکسل