एक्सेलमध्ये प्रतिगमन विश्लेषण कसे करावे. मानसशास्त्रातील गणितीय पद्धती

इकोनोमेट्रिक्ससारख्या विषयासह, क्रियाकलापांच्या विविध क्षेत्रात उपयुक्त म्हणून ओळखले जाते, जिथे ही सॉफ्टवेअर उपयुक्तता कामात वापरली जाते. मूलभूतपणे, व्यावहारिक आणि प्रयोगशाळा वर्गांच्या सर्व क्रिया एक्सेलमध्ये केल्या जातात, जे काही क्रियांचे तपशीलवार स्पष्टीकरण देऊन कार्य मोठ्या प्रमाणात सुलभ करते. अशाप्रकारे, विश्लेषण साधनांपैकी एक "रिग्रेशन" कमीत कमी वर्ग पद्धती वापरून निरीक्षणांच्या संचासाठी आलेख निवडण्यासाठी वापरला जातो. हे प्रोग्राम टूल काय आहे आणि वापरकर्त्यांसाठी त्याचे फायदे काय आहेत ते पाहूया. खाली आम्ही रीग्रेशन मॉडेल तयार करण्यासाठी संक्षिप्त परंतु स्पष्ट सूचना देखील देतो.

मुख्य कार्ये आणि प्रतिगमनचे प्रकार

प्रतिगमन हे दिलेल्या व्हेरिएबल्समधील संबंध दर्शवते, ज्याद्वारे या चलांच्या भविष्यातील वर्तनाचा अंदाज निर्धारित केला जाऊ शकतो. व्हेरिएबल्स मानवी वर्तनासह विविध नियतकालिक घटना आहेत. या प्रकारचे एक्सेल विश्लेषण एक किंवा अनेक व्हेरिएबल्सच्या मूल्यांच्या विशिष्ट अवलंबून व्हेरिएबलवरील प्रभावाचे विश्लेषण करण्यासाठी वापरले जाते. उदाहरणार्थ, स्टोअरमधील विक्रीवर वर्गीकरण, किमती आणि स्टोअरचे स्थान यासह अनेक घटकांचा प्रभाव पडतो. एक्सेलमध्ये प्रतिगमन केल्याबद्दल धन्यवाद, आपण विद्यमान विक्रीच्या परिणामांवर आधारित या प्रत्येक घटकाच्या प्रभावाची डिग्री निर्धारित करू शकता आणि नंतर प्राप्त केलेला डेटा दुसर्या महिन्यासाठी किंवा जवळपास असलेल्या दुसऱ्या स्टोअरसाठी विक्रीचा अंदाज लावण्यासाठी लागू करू शकता.

सामान्यतः, प्रतिगमन हे एक साधे समीकरण म्हणून सादर केले जाते जे चलांच्या दोन गटांमधील संबंध आणि सामर्थ्य प्रकट करते, जेथे एक गट अवलंबून किंवा अंतर्जात असतो आणि दुसरा स्वतंत्र किंवा बहिर्जात असतो. आंतरसंबंधित संकेतकांचा समूह असल्यास, आश्रित व्हेरिएबल Y हे तर्कशास्त्राच्या आधारे निर्धारित केले जाते आणि बाकीचे स्वतंत्र X चल म्हणून कार्य करतात.

रीग्रेशन मॉडेल तयार करण्याची मुख्य कार्ये खालीलप्रमाणे आहेत:

1. लक्षणीय स्वतंत्र व्हेरिएबल्सची निवड (X1, X2, ..., Xk).
2. फंक्शनचा प्रकार निवडणे.
3. गुणांकांसाठी अंदाज बांधणे.
4. आत्मविश्वास मध्यांतर आणि प्रतिगमन कार्ये तयार करणे.
5. गणना केलेल्या अंदाजांचे महत्त्व आणि तयार केलेले प्रतिगमन समीकरण तपासत आहे.

रीग्रेशन विश्लेषणाचे अनेक प्रकार आहेत:

• जोडलेले (1 आश्रित आणि 1 स्वतंत्र चल);
• एकाधिक (अनेक स्वतंत्र चल).

रीग्रेशन समीकरणे दोन प्रकारची आहेत:

1. रेखीय, चलांमधील कठोर रेखीय संबंध स्पष्ट करते.
2. अरेखीय - समीकरणे ज्यामध्ये शक्ती, अपूर्णांक आणि त्रिकोणमितीय कार्ये समाविष्ट असू शकतात.

मॉडेल तयार करण्यासाठी सूचना

Excel मध्ये दिलेले बांधकाम करण्यासाठी, आपण सूचनांचे अनुसरण करणे आवश्यक आहे:

पुढील गणनेसाठी, Y मूल्ये, X मूल्ये, Const आणि सांख्यिकी निर्दिष्ट करून, “लिनियर()” फंक्शन वापरा. यानंतर, "ट्रेंड" फंक्शन वापरून रीग्रेशन लाइनवरील बिंदूंचा संच निश्चित करा - Y मूल्ये, एक्स मूल्ये, नवीन मूल्ये, कॉन्स्ट. दिलेल्या पॅरामीटर्सचा वापर करून, समस्येच्या दिलेल्या परिस्थितीच्या आधारे गुणांकांच्या अज्ञात मूल्याची गणना करा.

परिणामी वैशिष्ट्यपूर्ण y मध्ये होणारा बदल x या घटकाच्या वैशिष्ट्यातील फरकामुळे होतो. परिणामी वैशिष्ट्याच्या एकूण भिन्नतेमध्ये प्रतिगमन द्वारे स्पष्ट केलेल्या भिन्नतेचा वाटा R 2 च्या निर्धाराचे गुणांक दर्शवते. रेखीय संबंधांसाठी, निर्धाराचा गुणांक सहसंबंध गुणांकाच्या वर्गाइतका असतो:

R 2 = r xy 2 , जेथे r xy हा सहसंबंध गुणांक आहे.

उदाहरणार्थ, R 2 = 0.83 चे मूल्य म्हणजे 83% प्रकरणांमध्ये x मधील बदल y मध्ये बदल करतात. दुसऱ्या शब्दांत, प्रतिगमन समीकरण निवडण्याची अचूकता जास्त आहे.

प्रतिगमन समीकरण फिट करण्याच्या गुणवत्तेचे मूल्यांकन करण्यासाठी गणना केली जाते. स्वीकार्य मॉडेल्ससाठी, असे सुचवले जाते की निर्धाराचे गुणांक 50% पेक्षा जास्त असावे. 80% वरील निर्धाराचे गुणांक असलेले मॉडेल बरेच चांगले मानले जाऊ शकतात. R 2 = 1 च्या निर्धाराच्या गुणांकाचे मूल्य म्हणजे चलांमधील कार्यात्मक संबंध.

बाबतीत अरेखीय प्रतिगमनया कॅल्क्युलेटरचा वापर करून निर्धाराचा गुणांक काढला जातो. मल्टिपल रिग्रेशनसह, मल्टिपल रिग्रेशन सेवेद्वारे निर्धाराचे गुणांक शोधले जाऊ शकतात
सर्वसाधारणपणे, निर्धाराचे गुणांक सूत्रानुसार आढळतात: किंवा
भिन्नता जोडण्यासाठी नियम:
,
चौरस विचलनांची एकूण बेरीज कुठे आहे;
- प्रतिगमन ("स्पष्टीकरण" किंवा "घटकीय") मुळे वर्ग विचलनाची बेरीज;
- वर्ग विचलनांची अवशिष्ट बेरीज.

या ऑनलाइन कॅल्क्युलेटरचा वापर करून तुम्ही गणना करू शकता निर्धार गुणांकआणि त्याचे महत्त्व तपासले जाते (उदाहरण उपाय).

सूचना. इनपुट डेटाचे प्रमाण निर्दिष्ट करा. परिणामी समाधान वर्ड फाइलमध्ये जतन केले जाते. एक्सेलमध्ये सोल्यूशनची चाचणी घेण्यासाठी टेम्पलेट देखील स्वयंचलितपणे तयार केले जाते.

प्रतिगमन विश्लेषण ही एक सांख्यिकीय संशोधन पद्धत आहे जी तुम्हाला एक किंवा अधिक स्वतंत्र व्हेरिएबल्सवर विशिष्ट पॅरामीटरचे अवलंबित्व दर्शवू देते. प्री-कॉम्प्युटर युगात, त्याचा वापर करणे खूप कठीण होते, विशेषत: जेव्हा डेटा मोठ्या प्रमाणात येतो. आज, एक्सेलमध्ये रीग्रेशन कसे तयार करायचे हे शिकल्यानंतर, आपण फक्त काही मिनिटांत जटिल सांख्यिकीय समस्या सोडवू शकता. खाली अर्थशास्त्राच्या क्षेत्रातील विशिष्ट उदाहरणे आहेत.

प्रतिगमनाचे प्रकार

ही संकल्पना स्वतःच 1886 मध्ये गणितात आणली गेली. प्रतिगमन घडते:

• रेखीय
• पॅराबॉलिक
• उपशामक
• घातांकीय
• हायपरबोलिक;
• प्रात्यक्षिक
• लॉगरिदमिक

उदाहरण १

6 औद्योगिक उपक्रमांमध्ये सरासरी पगारावर सोडलेल्या टीम सदस्यांच्या संख्येचे अवलंबित्व निश्चित करण्याच्या समस्येचा विचार करूया.

कार्य. सहा उपक्रमांमध्ये, सरासरी मासिक पगार आणि स्वेच्छेने सोडलेल्या कर्मचाऱ्यांच्या संख्येचे विश्लेषण केले गेले. सारणीच्या स्वरूपात आमच्याकडे आहे:

		सोडलेल्या लोकांची संख्या	पगार
			30,000 रूबल
			35,000 रूबल
			40,000 रूबल
			45,000 रूबल
			50,000 रूबल
			55,000 रूबल
			60,000 रूबल

6 एंटरप्राइझमधील सरासरी पगारावर सोडणाऱ्या कामगारांच्या संख्येचे अवलंबित्व निश्चित करण्याच्या कार्यासाठी, प्रतिगमन मॉडेलमध्ये Y = a 0 + a 1 x 1 + ... a k x k असे समीकरण आहे, जेथे x i आहेत चलांवर प्रभाव पाडणारे, a i हे प्रतिगमन गुणांक आहेत आणि k ही घटकांची संख्या आहे.

या समस्येसाठी, Y हा सोडलेल्या कर्मचाऱ्यांचा सूचक आहे आणि प्रभावित करणारा घटक हा पगार आहे, जो आम्ही X द्वारे दर्शवतो.

एक्सेल स्प्रेडशीट प्रोसेसरची क्षमता वापरणे

विद्यमान टॅब्युलर डेटामध्ये अंगभूत फंक्शन्स लागू करून एक्सेलमधील प्रतिगमन विश्लेषण आधी केले जाणे आवश्यक आहे. तथापि, या हेतूंसाठी अतिशय उपयुक्त "विश्लेषण पॅकेज" ऍड-ऑन वापरणे चांगले आहे. ते सक्रिय करण्यासाठी आपल्याला आवश्यक आहे:

• "फाइल" टॅबमधून "पर्याय" विभागात जा;
• उघडलेल्या विंडोमध्ये, "ॲड-ऑन" ओळ निवडा;
• “व्यवस्थापन” ओळीच्या उजवीकडे खाली असलेल्या “गो” बटणावर क्लिक करा;
• "विश्लेषण पॅकेज" नावाच्या पुढील बॉक्समध्ये खूण करा आणि "ओके" वर क्लिक करून तुमच्या कृतींची पुष्टी करा.

सर्वकाही योग्यरित्या केले असल्यास, आवश्यक बटण "डेटा" टॅबच्या उजव्या बाजूला दिसेल, एक्सेल वर्कशीटच्या वर स्थित आहे.

एक्सेल मध्ये

आता आमच्याकडे अर्थमितीय गणना करण्यासाठी सर्व आवश्यक आभासी साधने आहेत, आम्ही आमच्या समस्येचे निराकरण करण्यास सुरवात करू शकतो. हे करण्यासाठी:

• "डेटा विश्लेषण" बटणावर क्लिक करा;
• उघडलेल्या विंडोमध्ये, "रिग्रेशन" बटणावर क्लिक करा;
• दिसत असलेल्या टॅबमध्ये, Y साठी मूल्यांची श्रेणी (कर्मचारी सोडण्याची संख्या) आणि X (त्यांचे पगार) एंटर करा;
• आम्ही "ओके" बटण दाबून आमच्या क्रियांची पुष्टी करतो.

परिणामी, प्रोग्राम रिग्रेशन विश्लेषण डेटासह स्वयंचलितपणे नवीन स्प्रेडशीट भरेल. लक्ष द्या! एक्सेल तुम्हाला या उद्देशासाठी तुम्हाला प्राधान्य देणारे स्थान मॅन्युअली सेट करण्याची परवानगी देते. उदाहरणार्थ, हे समान पत्रक असू शकते जेथे Y आणि X मूल्ये स्थित आहेत, किंवा विशेषत: असा डेटा संचयित करण्यासाठी डिझाइन केलेली नवीन कार्यपुस्तिका देखील असू शकते.

R-squared साठी प्रतिगमन परिणामांचे विश्लेषण

एक्सेलमध्ये, विचाराधीन उदाहरणातील डेटाच्या प्रक्रियेदरम्यान प्राप्त झालेल्या डेटाचे स्वरूप आहे:

सर्व प्रथम, आपण आर-वर्ग मूल्याकडे लक्ष दिले पाहिजे. हे निर्धाराचे गुणांक दर्शवते. या उदाहरणामध्ये, R-square = 0.755 (75.5%), म्हणजे, मॉडेलचे मोजलेले पॅरामीटर्स 75.5% ने विचाराधीन पॅरामीटर्समधील संबंध स्पष्ट करतात. निर्धाराच्या गुणांकाचे मूल्य जितके जास्त असेल तितके निवडलेले मॉडेल विशिष्ट कार्यासाठी अधिक योग्य असेल. जेव्हा आर-स्क्वेअर मूल्य 0.8 पेक्षा जास्त असते तेव्हा वास्तविक परिस्थितीचे अचूक वर्णन करणे मानले जाते. जर आर-वर्ग<0,5, то такой анализа регрессии в Excel нельзя считать резонным.

शक्यता विश्लेषण

64.1428 हा आकडा दाखवतो की जर आपण विचार करत असलेल्या मॉडेलमधील सर्व व्हेरिएबल्स xi शून्यावर रीसेट केल्यास Y चे मूल्य काय असेल. दुसऱ्या शब्दांत, असा युक्तिवाद केला जाऊ शकतो की विश्लेषण केलेल्या पॅरामीटरचे मूल्य विशिष्ट मॉडेलमध्ये वर्णन केलेले नसलेल्या इतर घटकांद्वारे देखील प्रभावित होते.

सेल B18 मध्ये स्थित पुढील गुणांक -0.16285, Y वर व्हेरिएबल X च्या प्रभावाचे वजन दर्शविते. याचा अर्थ विचाराधीन मॉडेलमधील कर्मचाऱ्यांचा सरासरी मासिक पगार -0.16285 च्या वजनासह सोडणाऱ्यांच्या संख्येवर परिणाम करतो, म्हणजे. त्याच्या प्रभावाची डिग्री पूर्णपणे लहान आहे. "-" चिन्ह दर्शवते की गुणांक ऋण आहे. हे स्पष्ट आहे, कारण प्रत्येकाला माहित आहे की एंटरप्राइझमध्ये पगार जितका जास्त असेल तितके कमी लोक रोजगार करार संपुष्टात आणण्याची किंवा सोडण्याची इच्छा व्यक्त करतात.

एकाधिक प्रतिगमन

हा शब्द फॉर्मच्या अनेक स्वतंत्र चलांसह संबंध समीकरणाचा संदर्भ देतो:

y=f(x 1 +x 2 +…x m) + ε, जेथे y हे परिणामी वैशिष्ट्य (आश्रित चल) आहे आणि x 1, x 2, …x m ही घटक वैशिष्ट्ये आहेत (स्वतंत्र चल).

पॅरामीटर अंदाज

मल्टिपल रिग्रेशन (एमआर) साठी, हे कमीतकमी स्क्वेअर पद्धत (OLS) वापरून चालते. Y = a + b 1 x 1 +…+b m x m + ε फॉर्मच्या रेखीय समीकरणांसाठी आपण सामान्य समीकरणांची एक प्रणाली तयार करतो (खाली पहा)

पद्धतीचे तत्त्व समजून घेण्यासाठी, दोन-घटकांचा विचार करा. मग आपल्याकडे सूत्राने वर्णन केलेली परिस्थिती आहे

येथून आम्हाला मिळते:

जेथे σ हा निर्देशांकामध्ये परावर्तित केलेल्या संबंधित वैशिष्ट्याचा फरक आहे.

OLS हे MR समीकरणाला प्रमाणित स्केलवर लागू आहे. या प्रकरणात, आम्हाला समीकरण मिळते:

ज्यामध्ये t y, t x 1, … t xm हे प्रमाणित व्हेरिएबल्स आहेत, ज्यासाठी सरासरी मूल्ये 0 च्या समान आहेत; β i हे प्रमाणित प्रतिगमन गुणांक आहेत आणि मानक विचलन 1 आहे.

कृपया लक्षात घ्या की या प्रकरणात सर्व β i सामान्यीकृत आणि केंद्रीकृत म्हणून निर्दिष्ट केले आहेत, म्हणून त्यांची एकमेकांशी तुलना योग्य आणि स्वीकार्य मानली जाते. याव्यतिरिक्त, सर्वात कमी βi मूल्ये असलेल्या घटकांचा त्याग करून घटक तपासण्याची प्रथा आहे.

रेखीय प्रतिगमन समीकरण वापरताना समस्या

समजा, आमच्याकडे गेल्या 8 महिन्यांतील विशिष्ट उत्पादन N साठी किंमत गतीशीलतेचे सारणी आहे. त्याची बॅच 1850 रूबल/टी किंमतीवर खरेदी करण्याच्या सल्ल्यानुसार निर्णय घेणे आवश्यक आहे.

	महिन्याची संख्या	महिन्याचे नाव	उत्पादनाची किंमत एन
			1750 रूबल प्रति टन
			1755 रूबल प्रति टन
			1767 रूबल प्रति टन
			1760 रूबल प्रति टन
			1770 रूबल प्रति टन
			1790 रूबल प्रति टन
			1810 रूबल प्रति टन
			1840 रूबल प्रति टन

एक्सेल स्प्रेडशीट प्रोसेसरमध्ये या समस्येचे निराकरण करण्यासाठी, तुम्हाला "डेटा विश्लेषण" साधन वापरावे लागेल, जे वर सादर केलेल्या उदाहरणावरून आधीच ज्ञात आहे. पुढे, “रिग्रेशन” विभाग निवडा आणि पॅरामीटर्स सेट करा. हे लक्षात ठेवले पाहिजे की "इनपुट इंटरव्हल Y" फील्डमध्ये अवलंबून व्हेरिएबलसाठी मूल्यांची श्रेणी प्रविष्ट करणे आवश्यक आहे (या प्रकरणात, वर्षाच्या विशिष्ट महिन्यांत वस्तूंच्या किंमती), आणि "इनपुट इंटरव्हल X" मध्ये. - स्वतंत्र व्हेरिएबलसाठी (महिना क्रमांक). "ओके" वर क्लिक करून कृतीची पुष्टी करा. नवीन शीटवर (असे सूचित केले असल्यास) आम्ही प्रतिगमनासाठी डेटा प्राप्त करतो.

त्यांचा वापर करून, आम्ही y=ax+b या फॉर्मचे एक रेखीय समीकरण तयार करतो, जेथे पॅरामीटर्स a आणि b हे महिन्याच्या क्रमांकाच्या नावासह रेषेचे गुणांक आहेत आणि गुणांक आणि शीटमधील "Y- छेदनबिंदू" या ओळी प्रतिगमन विश्लेषणाचे परिणाम. अशा प्रकारे, कार्य 3 साठी रेखीय प्रतिगमन समीकरण (LR) असे लिहिले आहे:

उत्पादन किंमत N = 11.714* महिना क्रमांक + 1727.54.

किंवा बीजगणितीय नोटेशनमध्ये

y = 11.714 x + 1727.54

परिणामांचे विश्लेषण

परिणामी रेखीय प्रतिगमन समीकरण पुरेसे आहे की नाही हे ठरवण्यासाठी, एकाधिक सहसंबंध (MCC) आणि निर्धाराचे गुणांक, तसेच फिशर चाचणी आणि विद्यार्थी t चाचणी वापरली जातात. प्रतिगमन परिणामांसह एक्सेल स्प्रेडशीटमध्ये, त्यांना अनुक्रमे एकाधिक R, R-squared, F-statistic आणि t-statistic म्हणतात.

KMC R स्वतंत्र आणि अवलंबित व्हेरिएबल्समधील संभाव्य संबंधांच्या जवळचे मूल्यांकन करणे शक्य करते. त्याचे उच्च मूल्य "महिन्याची संख्या" आणि "उत्पादन N ची किंमत प्रति 1 टन रूबलमध्ये" व्हेरिएबल्समधील बऱ्यापैकी मजबूत कनेक्शन दर्शवते. तथापि, या संबंधाचे स्वरूप अज्ञात आहे.

गुणांक R2 (RI) चा वर्ग हा एकूण स्कॅटरच्या प्रमाणात एक संख्यात्मक वैशिष्ट्य आहे आणि प्रायोगिक डेटाच्या कोणत्या भागाचा स्कॅटर दर्शवितो, उदा. अवलंबून व्हेरिएबलची मूल्ये रेखीय प्रतिगमन समीकरणाशी संबंधित आहेत. विचाराधीन समस्येमध्ये, हे मूल्य 84.8% च्या बरोबरीचे आहे, म्हणजे, परिणामी SD द्वारे सांख्यिकीय डेटाचे वर्णन उच्च प्रमाणात अचूकतेसह केले जाते.

F-सांख्यिकी, ज्याला फिशर्स टेस्ट देखील म्हणतात, रेखीय नातेसंबंधाचे महत्त्व मूल्यांकन करण्यासाठी, त्याच्या अस्तित्वाच्या गृहीतकाचे खंडन किंवा पुष्टी करण्यासाठी वापरले जाते.

(विद्यार्थ्याची चाचणी) रेखीय संबंधाच्या अज्ञात किंवा मुक्त शब्दासह गुणांकाचे महत्त्व मूल्यांकन करण्यास मदत करते. जर t-test > tcr चे मूल्य असेल, तर रेखीय समीकरणाच्या मुक्त पदाच्या क्षुल्लकतेबद्दलचे गृहितक नाकारले जाईल.

एक्सेल टूल्सचा वापर करून फ्री टर्मसाठी विचाराधीन असलेल्या समस्येमध्ये, t = 169.20903, आणि p = 2.89E-12, म्हणजे, आमच्याकडे शून्य संभाव्यता आहे की फ्री टर्मच्या क्षुल्लकतेबद्दल योग्य गृहितक नाकारले जाईल. . अज्ञात t=5.79405, आणि p=0.001158 साठी गुणांकासाठी. दुसऱ्या शब्दात, अज्ञातासाठी गुणांकाच्या क्षुल्लकतेबद्दल योग्य गृहीतक नाकारले जाण्याची संभाव्यता 0.12% आहे.

अशा प्रकारे, असा युक्तिवाद केला जाऊ शकतो की परिणामी रेखीय प्रतिगमन समीकरण पुरेसे आहे.

समभागांचा ब्लॉक खरेदी करण्याच्या व्यवहार्यतेची समस्या

एक्सेलमध्ये एकाधिक प्रतिगमन समान डेटा विश्लेषण साधन वापरून केले जाते. चला एका विशिष्ट अनुप्रयोग समस्येचा विचार करूया.

NNN कंपनीच्या व्यवस्थापनाने MMM JSC मधील 20% स्टेक खरेदी करण्याच्या सल्ल्याबाबत निर्णय घेणे आवश्यक आहे. पॅकेजची किंमत (SP) 70 दशलक्ष यूएस डॉलर आहे. NNN तज्ञांनी समान व्यवहारांवर डेटा गोळा केला आहे. लाखो यूएस डॉलर्समध्ये व्यक्त केलेल्या अशा पॅरामीटर्सनुसार शेअरहोल्डिंगच्या मूल्याचे मूल्यांकन करण्याचा निर्णय घेण्यात आला, जसे:

• देय खाती (VK);
• वार्षिक उलाढाल खंड (VO);
• प्राप्त करण्यायोग्य खाती (VD);
• निश्चित मालमत्तेची किंमत (COF).

याव्यतिरिक्त, हजारो यूएस डॉलर्समधील एंटरप्राइझच्या वेतन थकबाकीचे मापदंड (V3 P) वापरले जाते.

एक्सेल स्प्रेडशीट प्रोसेसर वापरून उपाय

सर्व प्रथम, आपल्याला स्त्रोत डेटाची सारणी तयार करण्याची आवश्यकता आहे. हे असे दिसते:

• "डेटा विश्लेषण" विंडोवर कॉल करा;
• "रिग्रेशन" विभाग निवडा;
• "इनपुट इंटरव्हल Y" बॉक्समध्ये, कॉलम G मधून अवलंबून व्हेरिएबल्सच्या मूल्यांची श्रेणी प्रविष्ट करा;
• “इनपुट इंटरव्हल X” विंडोच्या उजवीकडे लाल बाण असलेल्या चिन्हावर क्लिक करा आणि शीटवरील स्तंभ B, C, D, F मधील सर्व मूल्यांची श्रेणी हायलाइट करा.

"नवीन वर्कशीट" आयटम चिन्हांकित करा आणि "ओके" क्लिक करा.

दिलेल्या समस्येसाठी प्रतिगमन विश्लेषण मिळवा.

परिणाम आणि निष्कर्षांचा अभ्यास

आम्ही एक्सेल स्प्रेडशीटवर वर सादर केलेल्या गोलाकार डेटामधून प्रतिगमन समीकरण "संकलित" करतो:

SP = 0.103*SOF + 0.541*VO - 0.031*VK +0.405*VD +0.691*VZP - 265.844.

अधिक परिचित गणितीय स्वरूपात, हे असे लिहिले जाऊ शकते:

y = 0.103*x1 + 0.541*x2 - 0.031*x3 +0.405*x4 +0.691*x5 - 265.844

MMM JSC साठी डेटा टेबलमध्ये सादर केला आहे:

त्यांना प्रतिगमन समीकरणात बदलून, आम्हाला 64.72 दशलक्ष यूएस डॉलर्सचा आकडा मिळेल. याचा अर्थ MMM JSC चे शेअर्स खरेदी करण्यासारखे नाहीत, कारण त्यांचे मूल्य 70 दशलक्ष यूएस डॉलर्स इतके फुगलेले आहे.

तुम्ही बघू शकता, एक्सेल स्प्रेडशीट प्रोसेसरचा वापर आणि रिग्रेशन समीकरणामुळे एखाद्या विशिष्ट व्यवहाराच्या व्यवहार्यतेबाबत माहितीपूर्ण निर्णय घेणे शक्य झाले.

आता तुम्हाला माहिती आहे की प्रतिगमन काय आहे. वर चर्चा केलेली एक्सेल उदाहरणे तुम्हाला इकॉनॉमेट्रिक्सच्या क्षेत्रातील व्यावहारिक समस्या सोडवण्यास मदत करतील.

अवलंबून व्हेरिएबलवर काही मूल्यांचा (स्वतंत्र, स्वतंत्र) प्रभाव दर्शवितो. उदाहरणार्थ, आर्थिकदृष्ट्या सक्रिय लोकसंख्येची संख्या उद्यमांची संख्या, वेतन आणि इतर पॅरामीटर्सवर कशी अवलंबून असते. किंवा: परकीय गुंतवणूक, ऊर्जेच्या किमती इत्यादींचा जीडीपीच्या पातळीवर कसा परिणाम होतो.

विश्लेषणाचा परिणाम आपल्याला प्राधान्यक्रम हायलाइट करण्यास अनुमती देतो. आणि मुख्य घटकांवर आधारित, अंदाज लावा, प्राधान्य क्षेत्राच्या विकासाचे नियोजन करा आणि व्यवस्थापन निर्णय घ्या.

प्रतिगमन घडते:

रेखीय (y = a + bx);

· पॅराबॉलिक (y = a + bx + cx 2);

· घातांक (y = a * exp(bx));

· शक्ती (y = a*x^b);

हायपरबोलिक (y = b/x + a);

लॉगरिदमिक (y = b * 1n(x) + a);

· घातांक (y = a * b^x).

एक्सेलमध्ये रीग्रेशन मॉडेल तयार करण्याचे आणि परिणामांचे स्पष्टीकरण करण्याचे उदाहरण पाहू. रीग्रेशनचा रेषीय प्रकार घेऊ.

कार्य. 6 उपक्रमांमध्ये, सरासरी मासिक पगार आणि सोडलेल्या कर्मचाऱ्यांच्या संख्येचे विश्लेषण केले गेले. सरासरी पगारावर सोडलेल्या कर्मचार्यांच्या संख्येचे अवलंबित्व निश्चित करणे आवश्यक आहे.

रेखीय प्रतिगमन मॉडेल असे दिसते:

Y = a 0 + a 1 x 1 +…+a k x k.

जेथे a प्रतिगमन गुणांक आहेत, x हे चलांवर प्रभाव टाकत आहेत, k ही घटकांची संख्या आहे.

आमच्या उदाहरणात, Y हे कर्मचारी सोडण्याचे सूचक आहे. प्रभावित करणारा घटक म्हणजे वेतन (x).

एक्सेलमध्ये अंगभूत कार्ये आहेत जी तुम्हाला रेखीय प्रतिगमन मॉडेलच्या पॅरामीटर्सची गणना करण्यात मदत करू शकतात. परंतु "विश्लेषण पॅकेज" ॲड-ऑन हे जलद करेल.

चला एक शक्तिशाली विश्लेषणात्मक साधन सक्रिय करूया:

1. "ऑफिस" बटणावर क्लिक करा आणि "एक्सेल पर्याय" टॅबवर जा. "ॲड-ऑन".

2. तळाशी, ड्रॉप-डाउन सूचीच्या खाली, "व्यवस्थापित करा" फील्डमध्ये "एक्सेल ॲड-इन्स" शिलालेख असेल (जर ते नसेल तर, उजवीकडे चेकबॉक्सवर क्लिक करा आणि निवडा). आणि "जा" बटण. क्लिक करा.

3. उपलब्ध ॲड-ऑनची सूची उघडते. "विश्लेषण पॅकेज" निवडा आणि ओके क्लिक करा.

एकदा सक्रिय झाल्यानंतर, ॲड-ऑन डेटा टॅबमध्ये उपलब्ध होईल.

आता प्रतिगमन विश्लेषण स्वतः करूया.

1. “डेटा विश्लेषण” टूलचा मेनू उघडा. "रिग्रेशन" निवडा.

2. इनपुट मूल्ये आणि आउटपुट पर्याय निवडण्यासाठी एक मेनू उघडेल (परिणाम कुठे प्रदर्शित करायचा). प्रारंभिक डेटासाठी फील्डमध्ये, आम्ही वर्णन केलेल्या पॅरामीटरची श्रेणी (Y) आणि त्यावर प्रभाव टाकणारा घटक (X) सूचित करतो. उर्वरित भरले जाऊ शकत नाही.

3. ओके क्लिक केल्यानंतर, प्रोग्राम नवीन शीटवर गणना प्रदर्शित करेल (आपण सध्याच्या शीटवर प्रदर्शित करण्यासाठी मध्यांतर निवडू शकता किंवा नवीन वर्कबुकमध्ये आउटपुट नियुक्त करू शकता).

सर्व प्रथम, आम्ही आर-वर्ग आणि गुणांकांकडे लक्ष देतो.

R-वर्ग हा निर्धाराचा गुणांक आहे. आमच्या उदाहरणात - 0.755, किंवा 75.5%. याचा अर्थ असा की मॉडेलचे गणना केलेले पॅरामीटर्स अभ्यास केलेल्या पॅरामीटर्समधील 75.5% संबंध स्पष्ट करतात. निर्धाराचे गुणांक जितके जास्त असेल तितके चांगले मॉडेल. चांगले - ०.८ च्या वर. खराब - ०.५ पेक्षा कमी (असे विश्लेषण क्वचितच वाजवी मानले जाऊ शकते). आमच्या उदाहरणात - "वाईट नाही".

गुणांक 64.1428 हे दर्शविते की विचाराधीन मॉडेलमधील सर्व चल 0 च्या समान असल्यास Y काय असेल. म्हणजेच, विश्लेषण केलेल्या पॅरामीटरचे मूल्य मॉडेलमध्ये वर्णन न केलेल्या इतर घटकांद्वारे देखील प्रभावित होते.

गुणांक -0.16285 व्हेरिएबल X चे Y वर वजन दर्शविते. म्हणजेच, या मॉडेलमधील सरासरी मासिक पगार -0.16285 च्या वजनासह सोडणाऱ्यांच्या संख्येवर प्रभाव टाकतो (हा प्रभावाचा एक लहान अंश आहे). "-" चिन्ह नकारात्मक प्रभाव दर्शवते: पगार जितका जास्त तितके कमी लोक सोडतात. जे न्याय्य आहे.

इतरांवर काही व्हेरिएबलचे अवलंबित्व दाखवण्याचा हा सर्वात सामान्य मार्ग आहे, उदाहरणार्थ, कसे जीडीपी पातळीआकार पासून परदेशी गुंतवणूककिंवा पासून नॅशनल बँक कर्ज दरकिंवा पासून मुख्य ऊर्जा संसाधनांसाठी किंमती.

मॉडेलिंग आपल्याला या अवलंबनाची (गुणांक) परिमाण दर्शवू देते, ज्यामुळे आपण थेट अंदाज लावू शकता आणि या अंदाजांवर आधारित काही प्रकारचे नियोजन करू शकता. तसेच, प्रतिगमन विश्लेषणाच्या आधारे, अंतिम निकालावर परिणाम करणाऱ्या प्राधान्य कारणांना उत्तेजन देण्याच्या उद्देशाने व्यवस्थापन निर्णय घेणे शक्य आहे.

रेखीय प्रतिगमन मॉडेलचे सामान्य दृश्य:

Y=a 0 +a 1 x 1 +...a k x k

कुठे a - प्रतिगमन पॅरामीटर्स (गुणांक), x - प्रभावित करणारे घटक, k - मॉडेल घटकांची संख्या.

प्रारंभिक डेटा

प्रारंभिक डेटामध्ये, आम्हाला डेटाच्या एका विशिष्ट संचाची आवश्यकता आहे जी अंतिम पॅरामीटर Y (उदाहरणार्थ, GDP) ची अनेक सलग किंवा परस्पर जोडलेली मूल्ये आणि ज्या निर्देशकांच्या प्रभावाचा आम्ही अभ्यास करत आहोत त्यांची समान संख्या दर्शवेल ( उदाहरणार्थ, परदेशी गुंतवणूक).

वरील आकृती या समान प्रारंभिक डेटासह एक सारणी दर्शवते; Y हे आर्थिकदृष्ट्या सक्रिय लोकसंख्येचे सूचक आहे आणि उद्योगांची संख्या, भांडवली गुंतवणूकीची रक्कम आणि घरगुती उत्पन्न हे घटक प्रभावित करतात, म्हणजेच X चे.

आकृतीच्या आधारे, एक चुकीचा निष्कर्ष देखील काढू शकतो की मॉडेलिंग केवळ वेळेच्या मालिकेबद्दल असू शकते, म्हणजे, वेळेनुसार क्रमाने रेकॉर्ड केलेली क्षण मालिका, परंतु हे असे नाही, एक संदर्भात मॉडेलिंग करू शकते; रचना, उदाहरणार्थ, सारणीमध्ये दर्शविलेली मूल्ये वर्षानुसार नाही तर प्रदेशानुसार मोडली जाऊ शकतात.

पुरेशी रेखीय मॉडेल्स तयार करण्यासाठी, स्त्रोत डेटामध्ये मजबूत थेंब किंवा कोलॅप्स नसणे इष्ट आहे, अशा परिस्थितीत, स्मूथिंग करणे उचित आहे, परंतु आम्ही पुढच्या वेळी स्मूथिंगबद्दल बोलू.

विश्लेषण पॅकेज

रेखीय रीग्रेशन मॉडेलचे पॅरामीटर्स देखील ऑर्डिनरी लीस्ट स्क्वेअर मेथड (OLS) वापरून व्यक्तिचलितपणे मोजले जाऊ शकतात, परंतु हे खूप वेळ घेणारे आहे. एक्सेलमधील सूत्रे वापरून हीच पद्धत वापरून थोड्या वेगाने गणना केली जाऊ शकते, जिथे प्रोग्राम स्वतः गणना करेल, परंतु तरीही तुम्हाला सूत्रे व्यक्तिचलितपणे प्रविष्ट करावी लागतील.

एक्सेलमध्ये ॲड-इन आहे विश्लेषण पॅकेज, जे विश्लेषकाला मदत करण्यासाठी एक शक्तिशाली साधन आहे. हे टूलकिट, इतर गोष्टींसह, समान किमान वर्ग पद्धती वापरून रीग्रेशन पॅरामीटर्सची गणना करू शकते, खरं तर, हे साधन कसे वापरावे याबद्दल पुढे चर्चा केली जाईल.

विश्लेषण पॅकेज सक्रिय करा

डीफॉल्टनुसार, हे ॲड-ऑन अक्षम केलेले आहे आणि तुम्हाला ते टॅब मेनूमध्ये सापडणार नाही, म्हणून आम्ही ते कसे सक्रिय करायचे ते चरण-दर-चरण पाहू.

Excel मध्ये, डावीकडे वरती, टॅब सक्रिय करा फाईल, उघडणाऱ्या मेनूमध्ये, आयटम शोधा पर्यायआणि त्यावर क्लिक करा.

उघडणाऱ्या विंडोमध्ये, डावीकडे, आयटम शोधा ॲड-ऑनआणि ते सक्रिय करा, या टॅबमध्ये तळाशी एक ड्रॉप-डाउन नियंत्रण सूची असेल, जिथे ते डीफॉल्टनुसार लिहिले जाईल एक्सेल ॲड-इन्स, ड्रॉप-डाउन सूचीच्या उजवीकडे एक बटण असेल जा, तुम्हाला त्यावर क्लिक करावे लागेल.

एक पॉप-अप विंडो तुम्हाला उपलब्ध ॲड-ऑन निवडण्यासाठी सूचित करेल, त्यात तुम्हाला बॉक्स चेक करणे आवश्यक आहे विश्लेषण पॅकेजआणि त्याच वेळी, फक्त बाबतीत, उपाय शोधणे(एक उपयुक्त गोष्ट देखील), आणि नंतर बटणावर क्लिक करून आपल्या निवडीची पुष्टी करा ठीक आहे.

विश्लेषण पॅकेज वापरून रेखीय प्रतिगमन पॅरामीटर्स शोधण्यासाठी सूचना

विश्लेषण पॅक ॲड-ऑन सक्रिय केल्यानंतर, ते नेहमी मुख्य मेनू टॅबमध्ये उपलब्ध असेल डेटादुव्याखाली डेटा विश्लेषण

सक्रिय साधन विंडोमध्ये डेटा विश्लेषणशक्यतांच्या सूचीमधून आम्ही शोधतो आणि निवडतो प्रतिगमन

पुढे, रिग्रेशन मॉडेलच्या पॅरामीटर्सची गणना करण्यासाठी स्त्रोत डेटा सेट करण्यासाठी आणि निवडण्यासाठी एक विंडो उघडेल. येथे तुम्हाला प्रारंभिक डेटाचे अंतराल सूचित करणे आवश्यक आहे, म्हणजे वर्णन केलेले पॅरामीटर (Y) आणि त्यावर प्रभाव पाडणारे घटक (X), खाली दिलेल्या आकृतीमध्ये दर्शविल्याप्रमाणे, तत्त्वतः, कॉन्फिगर करण्यासाठी पर्यायी आहेत;

तुम्ही स्त्रोत डेटा निवडल्यानंतर आणि ओके बटणावर क्लिक केल्यानंतर, एक्सेल सक्रिय वर्कबुकच्या नवीन शीटवर गणना तयार करते (जोपर्यंत ते सेटिंग्जमध्ये सेट केले जात नाही), ही गणना यासारखी दिसते:

मुख्य पेशी पिवळ्या रंगाने भरलेल्या आहेत; हे सर्व प्रथम आपल्याला लक्ष देण्याची आवश्यकता आहे, परंतु त्यांच्या तपशीलवार विश्लेषणासाठी एक स्वतंत्र पोस्ट आवश्यक आहे.

तर, 0,865 - हे आर २- निर्धाराचे गुणांक, हे दर्शविते की मॉडेलच्या गणना केलेल्या पॅरामीटर्सपैकी 86.5%, म्हणजे, मॉडेलच, अभ्यासात असलेल्या पॅरामीटरमधील अवलंबित्व आणि बदल स्पष्ट करतात - वायअभ्यासलेल्या घटकांमधून - X चे. अतिशयोक्ती असेल तर हे मॉडेलच्या गुणवत्तेचे सूचक आहेआणि ते जितके जास्त असेल तितके चांगले. हे स्पष्ट आहे की ते 1 पेक्षा जास्त असू शकत नाही आणि जेव्हा R 2 0.8 च्या वर असेल तेव्हा चांगले मानले जाते आणि जर ते 0.5 पेक्षा कमी असेल, तर अशा मॉडेलच्या वाजवीपणावर सुरक्षितपणे प्रश्नचिन्ह उपस्थित केले जाऊ शकते.

आता पुढे जाऊया मॉडेल गुणांक:
2079,85 - हे एक 0- एक गुणांक जो मॉडेलमध्ये वापरलेले सर्व घटक 0 च्या समान असल्यास Y काय असेल हे दर्शविते, हे समजले जाते की हे मॉडेलमध्ये वर्णन न केलेल्या इतर घटकांवर अवलंबून आहे;
-0,0056 - a 1- एक गुणांक जो Y वर x 1 फॅक्टरच्या प्रभावाचे वजन दर्शवितो, म्हणजेच दिलेल्या मॉडेलमधील एंटरप्राइझची संख्या केवळ -0.0056 च्या वजनासह आर्थिकदृष्ट्या सक्रिय लोकसंख्येच्या सूचकावर प्रभाव पाडते (त्यापेक्षा कमी प्रमाणात प्रभाव ). वजा चिन्ह दर्शविते की हा प्रभाव नकारात्मक आहे, म्हणजे, अधिक उद्योग, कमी आर्थिकदृष्ट्या सक्रिय लोकसंख्या, हे कितीही विरोधाभासी असले तरीही;
-0,0026 - a 2- आर्थिकदृष्ट्या सक्रिय लोकसंख्येच्या आकारावर भांडवलाच्या गुंतवणुकीच्या प्रभावाचे गुणांक, हा प्रभाव देखील नकारात्मक आहे;
0,0028 - a 3- आर्थिकदृष्ट्या सक्रिय लोकसंख्येच्या आकारावर लोकसंख्येच्या उत्पन्नाच्या प्रभावाचे गुणांक, येथे प्रभाव सकारात्मक आहे, म्हणजेच, मॉडेलनुसार, उत्पन्नात वाढ आर्थिकदृष्ट्या सक्रिय लोकसंख्येच्या आकारात वाढ करण्यास हातभार लावेल.

चला मॉडेलमध्ये गणना केलेले गुणांक गोळा करू:

Y = 2079.85 - 0.0056x 1 - 0.0026x 2 + 0.0028x 3

वास्तविक, हे एक रेखीय प्रतिगमन मॉडेल आहे, जे उदाहरणामध्ये वापरलेल्या प्रारंभिक डेटासाठी अगदी यासारखे दिसते.

मॉडेल अंदाज आणि अंदाज

आम्ही आधीच वर चर्चा केल्याप्रमाणे, मॉडेल केवळ प्रभावशाली घटकांवर अभ्यासल्या जाणाऱ्या पॅरामीटरच्या अवलंबित्वाची विशालता दर्शविण्यासाठी तयार केले गेले नाही, तर हे प्रभावित करणारे घटक जाणून घेतल्यास, अंदाज बांधणे शक्य होईल. हा अंदाज बांधणे अगदी सोपे आहे; परिणामी मॉडेल समीकरणात तुम्हाला फक्त संबंधित X च्या जागी प्रभाव पाडणाऱ्या घटकांची मूल्ये बदलण्याची आवश्यकता आहे. खालील आकृतीमध्ये, ही गणना एक्सेलमध्ये वेगळ्या स्तंभात केली आहे.

वास्तविक मूल्ये (जी प्रत्यक्षात घडली) आणि त्याच आकृतीतील मॉडेलनुसार गणना केलेली मूल्ये फरक दर्शविण्यासाठी आलेखांच्या स्वरूपात प्रदर्शित केली जातात आणि म्हणूनच मॉडेलची त्रुटी.

मी पुन्हा एकदा पुनरावृत्ती करतो, मॉडेल वापरून अंदाज बांधण्यासाठी, ज्ञात प्रभावशाली घटक असणे आवश्यक आहे आणि जर आपण वेळेच्या मालिकेबद्दल बोलत आहोत आणि त्यानुसार, भविष्यासाठीचा अंदाज, उदाहरणार्थ, पुढीलसाठी वर्ष किंवा महिना, मग या भविष्यात परिणाम करणारे घटक काय असतील हे शोधणे नेहमीच शक्य नसते. अशा प्रकरणांमध्ये, प्रभावित करणाऱ्या घटकांचा अंदाज लावणे देखील आवश्यक आहे बहुतेकदा हे ऑटोरेग्रेसिव्ह मॉडेल वापरून केले जाते - एक मॉडेल ज्यामध्ये प्रभाव पाडणारे घटक अभ्यासाच्या अधीन असतात, म्हणजेच निर्देशकाचे अवलंबन; ते भूतकाळात काय होते यावर मॉडेल केलेले आहे.

आपण पुढील लेखात ऑटोरिग्रेसिव्ह मॉडेल कसे तयार करायचे ते पाहू, परंतु आता असे गृहीत धरू की भविष्यात (उदाहरणार्थ, 2008) आणि या मूल्यांच्या जागी प्रभाव टाकणाऱ्या घटकांची मूल्ये काय असतील हे आपल्याला माहीत आहे. गणनेमध्ये आम्हाला 2008 साठी आमचा अंदाज मिळेल.