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

चेरचर 11.10.2019

Viber बाहेर

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

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

आता जेव्हा आपण टॅबवर जाऊ "डेटा", टूलबॉक्समधील रिबनवर "विश्लेषण"आम्हाला एक नवीन बटण दिसेल - "डेटा विश्लेषण".

प्रतिगमन विश्लेषणाचे प्रकार

रिग्रेशनचे अनेक प्रकार आहेत:

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

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

एक्सेलमध्ये रेखीय प्रतिगमन

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

सामान्य रेखीय प्रतिगमन समीकरण खालीलप्रमाणे आहे: Y = a0 + a1x1 +…+ akhk. या सूत्रात वायव्हेरिएबल म्हणजे ज्या घटकांचा आपण अभ्यास करण्याचा प्रयत्न करत आहोत त्यांचा प्रभाव. आमच्या बाबतीत, ही खरेदीदारांची संख्या आहे. अर्थ xव्हेरिएबलवर परिणाम करणारे विविध घटक आहेत. पर्याय aप्रतिगमन गुणांक आहेत. म्हणजेच तेच एखाद्या विशिष्ट घटकाचे महत्त्व ठरवतात. निर्देशांक kया घटकांची एकूण संख्या दर्शवते.

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

रीग्रेशन विश्लेषणाचे परिणाम सेटिंग्जमध्ये निर्दिष्ट केलेल्या ठिकाणी टेबलच्या स्वरूपात प्रदर्शित केले जातात.

मुख्य निर्देशकांपैकी एक आहे आर-चौरस. हे मॉडेलची गुणवत्ता दर्शवते. आमच्या बाबतीत, हे गुणांक 0.705 किंवा सुमारे 70.5% आहे. ही गुणवत्तेची स्वीकार्य पातळी आहे. ०.५ पेक्षा कमी अवलंबित्व वाईट आहे.

आणखी एक महत्त्वाचा निर्देशक रेषेच्या छेदनबिंदूवर असलेल्या सेलमध्ये स्थित आहे "Y-इंटरसेक्शन"आणि स्तंभ "विषमता". हे Y चे मूल्य कोणते असेल हे सूचित करते आणि आमच्या बाबतीत, ही खरेदीदारांची संख्या आहे, इतर सर्व घटक शून्य समान आहेत. या टेबलमध्ये, हे मूल्य 58.04 आहे.

आलेखाच्या छेदनबिंदूवरील मूल्य "व्हेरिएबल X1"आणि "विषमता" X वर Y च्या अवलंबनाची पातळी दर्शविते. आमच्या बाबतीत, ही तापमानावरील स्टोअर ग्राहकांच्या संख्येच्या अवलंबनाची पातळी आहे. 1.31 चा गुणांक हा बऱ्यापैकी उच्च प्रभाव निर्देशक मानला जातो.

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

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

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

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

रेखीय (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 च्या वजनासह सोडणाऱ्यांच्या संख्येवर प्रभाव टाकतो (हा प्रभावाचा एक लहान अंश आहे). "-" चिन्ह नकारात्मक प्रभाव दर्शवते: पगार जितका जास्त तितके कमी लोक सोडतात. जे न्याय्य आहे.

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

टेबल मध्ये प्रतिगमन आकडेवारीखालील मूल्ये दिली आहेत:

अनेक आर - एकाधिक सहसंबंध गुणांक;

आर- चौरस- निर्धाराचे गुणांक आर 2 ;

सामान्यीकृत आर - चौरस- समायोजित आर 2 स्वातंत्र्याच्या अंशांच्या संख्येसाठी समायोजित;

मानक त्रुटी- प्रतिगमन मानक त्रुटी एस;

निरीक्षणे -निरीक्षणांची संख्या n.

टेबल मध्ये भिन्नतेचे विश्लेषणदिले आहेत:

1. स्तंभ df - समान स्वातंत्र्याच्या अंशांची संख्या

स्ट्रिंगसाठी प्रतिगमन df = k;

स्ट्रिंगसाठी बाकीdf = n – k – 1;

स्ट्रिंगसाठी एकूणdf = n– 1.

2. स्तंभ एसएस -च्या समान वर्ग विचलनाची बेरीज

स्ट्रिंगसाठी प्रतिगमन ;

स्ट्रिंगसाठी बाकी ;

स्ट्रिंगसाठी एकूण .

3. स्तंभ एमएससूत्राद्वारे निर्धारित भिन्नता एमएस = एस.एस/df:

स्ट्रिंगसाठी प्रतिगमन- घटक फैलाव;

स्ट्रिंगसाठी बाकी- अवशिष्ट भिन्नता.

4. स्तंभ एफ - गणना केलेले मूल्य एफ-सूत्र वापरून निकष मोजले

एफ = एमएस(प्रतिगमन)/ एमएस(उर्वरित).

5. स्तंभ महत्त्व एफ - गणनाशी संबंधित महत्त्व पातळी मूल्य एफ- आकडेवारी .

महत्त्व एफ= FDIST( F-आकडेवारी, df(प्रतिगमन), df(उर्वरित)).

महत्त्व असल्यास एफ < стандартного уровня значимости, то आर 2 सांख्यिकीयदृष्ट्या महत्त्वपूर्ण आहे.

	शक्यता	मानक त्रुटी	t-सांख्यिकी	पी-मूल्य	तळ 95%	शीर्ष ९५%
वाय	65,92	11,74	5,61	0,00080	38,16	93,68
एक्स	0,107	0,014	7,32	0,00016	0,0728	0,142

हे सारणी दर्शवते:

1. शक्यता- गुणांक मूल्ये a, b.

2. मानक त्रुटी- प्रतिगमन गुणांकांच्या मानक त्रुटी एस ए, Sb.

3. t-आकडेवारी- गणना केलेली मूल्ये t - सूत्रानुसार मोजलेले मापदंड:

t-statistic = गुणांक/मानक त्रुटी.

4.आर-मूल्य (महत्त्व t) गणनाशी संबंधित महत्त्व पातळी मूल्य आहे t-आकडेवारी

आर-मूल्य = अभ्यासक(t- आकडेवारी, df(उर्वरित)).

जर आर- अर्थ< стандартного уровня значимости, то соответствующий коэффициент статистически значим.

5. तळ 95% आणि शीर्ष 95%- सैद्धांतिक रेखीय प्रतिगमन समीकरणाच्या गुणांकांसाठी 95% आत्मविश्वास मध्यांतरांची खालची आणि वरची मर्यादा.

बाकीचे मागे घेणे
निरीक्षण	अंदाज y	अवशेष ई
	72,70	-29,70
	82,91	-20,91
	94,53	-4,53
	105,72	5,27
	117,56	12,44
	129,70	19,29
	144,22	20,77
	166,49	24,50
	268,13	-27,13

टेबल मध्ये बाकीचे मागे घेणेसूचित:

स्तंभात निरीक्षण- निरीक्षण क्रमांक;

स्तंभात भाकीत केले y - अवलंबून व्हेरिएबलची गणना केलेली मूल्ये;

स्तंभात उरलेले e - अवलंबित व्हेरिएबलच्या निरीक्षण केलेल्या आणि गणना केलेल्या मूल्यांमधील फरक.

उदाहरण 3.6.अन्न खर्चावर डेटा (पारंपारिक युनिट्स) आहेत yआणि दरडोई उत्पन्न xकुटुंबांच्या नऊ गटांसाठी:

x
y

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

प्रतिगमन विश्लेषणाचे परिणाम सहसा फॉर्ममध्ये लिहिलेले असतात:

जेथे प्रतिगमन गुणांकांच्या मानक त्रुटी कंसात दर्शविल्या जातात.

प्रतिगमन गुणांक ए = 65,92 आणि ब= ०.१०७. दरम्यान संवादाची दिशा yआणि xप्रतिगमन गुणांकाचे चिन्ह निर्धारित करते b= ०.१०७, म्हणजे. कनेक्शन थेट आणि सकारात्मक आहे. गुणांक b= 0.107 दर्शविते की दरडोई उत्पन्नात 1 परंपरागत वाढ झाली आहे. युनिट्स अन्नाचा खर्च 0.107 पारंपारिक युनिट्सने वाढतो. युनिट्स

परिणामी मॉडेलच्या गुणांकांच्या महत्त्वाचे मूल्यांकन करूया. गुणांकांचे महत्त्व ( a, b) द्वारे तपासले जाते t-चाचणी:

P-मूल्य ( a) = 0,00080 < 0,01 < 0,05

P-मूल्य ( b) = 0,00016 < 0,01 < 0,05,

म्हणून, गुणांक ( a, b) 1% स्तरावर लक्षणीय आहेत, आणि त्याहूनही अधिक 5% महत्वाच्या पातळीवर. अशा प्रकारे, प्रतिगमन गुणांक लक्षणीय आहेत आणि मॉडेल मूळ डेटासाठी पुरेसे आहे.

प्रतिगमन अंदाज परिणाम केवळ प्रतिगमन गुणांकांच्या प्राप्त मूल्यांशीच नव्हे तर त्यांच्या विशिष्ट संचासह (आत्मविश्वास मध्यांतर) सुसंगत असतात. 95% संभाव्यतेसह, गुणांकांसाठी आत्मविश्वास अंतराल (38.16 - 93.68) आहेत aआणि (0.0728 – 0.142) साठी b

मॉडेलच्या गुणवत्तेचे मूल्यांकन गुणांकाने केले जाते आर 2 .

विशालता आर 2 = 0.884 याचा अर्थ असा की दरडोई उत्पन्नाचा घटक अन्न खर्चातील 88.4% फरक (विखुरलेला) स्पष्ट करू शकतो.

महत्त्व आर 2 द्वारे तपासले जाते F-चाचणी: महत्त्व एफ = 0,00016 < 0,01 < 0,05, следовательно, आर 2 हे 1% स्तरावर लक्षणीय आहे, आणि त्याहूनही अधिक म्हणजे 5% महत्त्वाच्या पातळीवर.

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

रेखीय प्रतिगमन पद्धत आम्हाला एका सरळ रेषेचे वर्णन करण्यास अनुमती देते जी क्रमबद्ध जोड्यांच्या मालिकेमध्ये (x, y) सर्वोत्तम बसते. सरळ रेषेचे समीकरण, ज्याला रेखीय समीकरण म्हणतात, खाली दिले आहे:

ŷ हे x च्या दिलेल्या मूल्यासाठी y चे अपेक्षित मूल्य आहे,

x एक स्वतंत्र चल आहे,

a हा सरळ रेषेसाठी y-अक्षावरील एक खंड आहे,

b हा सरळ रेषेचा उतार आहे.

खालील आकृती ही संकल्पना ग्राफिक पद्धतीने स्पष्ट करते:

वरील आकृती ŷ =2+0.5x या समीकरणाने वर्णन केलेली रेषा दाखवते. y-इंटरसेप्ट हा बिंदू आहे ज्यावर रेषा y-अक्षाला छेदते; आमच्या बाबतीत, a = 2. रेषेचा उतार, b, रेषेच्या वाढीचे रेषेच्या लांबीचे गुणोत्तर, याचे मूल्य 0.5 आहे. सकारात्मक उतार म्हणजे रेषा डावीकडून उजवीकडे वर येते. b = 0 असल्यास, रेषा क्षैतिज आहे, याचा अर्थ आश्रित आणि स्वतंत्र व्हेरिएबल्समध्ये कोणताही संबंध नाही. दुसऱ्या शब्दांत, x चे मूल्य बदलल्याने y च्या मूल्यावर परिणाम होत नाही.

ŷ आणि y अनेकदा गोंधळलेले असतात. आलेख दिलेल्या समीकरणानुसार बिंदूंच्या 6 क्रमबद्ध जोड्या आणि एक रेषा दर्शवितो

ही आकृती क्रमबद्ध जोडी x = 2 आणि y = 4 शी संबंधित बिंदू दर्शविते. लक्षात घ्या की वरील रेषेनुसार y चे अपेक्षित मूल्य एक्स= 2 हे ŷ आहे. आम्ही खालील समीकरणासह याची पुष्टी करू शकतो:

ŷ = 2 + 0.5х =2 +0.5(2) =3.

y मूल्य हे वास्तविक बिंदू दर्शवते आणि ŷ मूल्य हे x च्या दिलेल्या मूल्यासाठी रेखीय समीकरण वापरून y चे अपेक्षित मूल्य आहे.

पुढील पायरी म्हणजे क्रमबद्ध जोड्यांच्या संचाशी सर्वोत्तम जुळणारे रेखीय समीकरण निश्चित करणे, आम्ही मागील लेखात याबद्दल बोललो, जिथे आम्ही समीकरणाचा प्रकार द्वारे निर्धारित केला.

रेखीय प्रतिगमन परिभाषित करण्यासाठी एक्सेल वापरणे

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

टॅबमध्ये डेटागटात विश्लेषणएक नवीन बटण दिसेल डेटा विश्लेषण.

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

टॅबवर जा डेटा,गटात विश्लेषणक्लिक करा डेटा विश्लेषण.दिसत असलेल्या विंडोमध्ये डेटा विश्लेषणनिवडा प्रतिगमनचित्रात दाखवल्याप्रमाणे आणि ओके क्लिक करा.

विंडोमध्ये आवश्यक रीग्रेशन पॅरामीटर्स सेट करा प्रतिगमनचित्रात दाखवल्याप्रमाणे:

क्लिक करा ठीक आहे.खालील आकृती प्राप्त परिणाम दर्शवते:

मधील आमची स्वतःची गणना करून आम्ही मिळवलेल्या परिणामांशी हे परिणाम सुसंगत आहेत.

विश्लेषण पॅकेज कनेक्ट करत आहे

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

आता, जेव्हा आपण “डेटा” टॅबवर जातो तेव्हा “विश्लेषण” टूल ब्लॉकमधील रिबनवर आपल्याला एक नवीन बटण दिसेल - “डेटा विश्लेषण”.

प्रतिगमन विश्लेषणाचे प्रकार

रिग्रेशनचे अनेक प्रकार आहेत:

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

एक्सेलमध्ये रेखीय प्रतिगमन

सामान्य रेखीय प्रतिगमन समीकरण खालीलप्रमाणे आहे: Y = a0 + a1x1 +…+ akhk. या सूत्रात, Y म्हणजे चल ज्यावर आपण घटकांच्या प्रभावाचा अभ्यास करण्याचा प्रयत्न करत आहोत. आमच्या बाबतीत, ही खरेदीदारांची संख्या आहे. x चे मूल्य व्हेरिएबलवर परिणाम करणारे विविध घटक आहेत. पॅरामीटर्स a हे प्रतिगमन गुणांक आहेत. म्हणजेच तेच एखाद्या विशिष्ट घटकाचे महत्त्व ठरवतात. अनुक्रमणिका k या समान घटकांची एकूण संख्या दर्शवते.

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

मुख्य निर्देशकांपैकी एक आर-स्क्वेअर आहे. हे मॉडेलची गुणवत्ता दर्शवते. आमच्या बाबतीत, हे गुणांक 0.705 किंवा सुमारे 70.5% आहे. ही गुणवत्तेची स्वीकार्य पातळी आहे. ०.५ पेक्षा कमी अवलंबित्व वाईट आहे.

आणखी एक महत्त्वाचा निर्देशक सेलमध्ये “Y-इंटरसेक्शन” पंक्ती आणि “गुणक” स्तंभाच्या छेदनबिंदूवर स्थित आहे. हे Y चे मूल्य कोणते असेल हे सूचित करते आणि आमच्या बाबतीत, ही खरेदीदारांची संख्या आहे, इतर सर्व घटक शून्य समान आहेत. या टेबलमध्ये, हे मूल्य 58.04 आहे.

"व्हेरिएबल X1" आणि "गुणांक" या स्तंभांच्या छेदनबिंदूवरील मूल्य X वर Y च्या अवलंबनाची पातळी दर्शविते. आमच्या बाबतीत, ही तापमानावरील स्टोअर ग्राहकांच्या संख्येच्या अवलंबनाची पातळी आहे. 1.31 चा गुणांक हा बऱ्यापैकी उच्च प्रभाव निर्देशक मानला जातो.

आम्हाला आनंद आहे की आम्ही तुम्हाला समस्येचे निराकरण करण्यात मदत करू शकलो.

समस्येचे सार तपशीलवार वर्णन करून टिप्पण्यांमध्ये आपला प्रश्न विचारा. आमचे विशेषज्ञ शक्य तितक्या लवकर उत्तर देण्याचा प्रयत्न करतील.

या लेखाने तुम्हाला मदत केली का?

ŷ - x च्या दिलेल्या मूल्यासाठी y चे अपेक्षित मूल्य,

x - स्वतंत्र चल,

a - सरळ रेषेसाठी y-अक्षावरील विभाग,

b हा सरळ रेषेचा उतार आहे.

खालील आकृती ही संकल्पना ग्राफिक पद्धतीने स्पष्ट करते:

ŷ = 2 + 0.5х =2 +0.5(2) =3.

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

रेखीय प्रतिगमन परिभाषित करण्यासाठी एक्सेल वापरणे

टॅबमध्ये डेटागटात विश्लेषणएक नवीन बटण दिसेल डेटा विश्लेषण.

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

क्लिक करा ठीक आहे.खालील आकृती प्राप्त परिणाम दर्शवते:

हे परिणाम मागील लेखात आमची स्वतःची गणना करून मिळालेल्या परिणामांशी सुसंगत आहेत.

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

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

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

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

उदाहरण १

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

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

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

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

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

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

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

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

एक्सेलमध्ये रेखीय प्रतिगमन

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

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

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

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

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

सर्व प्रथम, आपण आर-वर्ग मूल्याकडे लक्ष दिले पाहिजे. हे निर्धाराचे गुणांक दर्शवते. या उदाहरणामध्ये, R-square = 0.755 (75.5%), म्हणजे, मॉडेलचे मोजलेले पॅरामीटर्स 75.5% ने विचाराधीन पॅरामीटर्समधील संबंध स्पष्ट करतात. निर्धाराच्या गुणांकाचे मूल्य जितके जास्त असेल तितके निवडलेले मॉडेल विशिष्ट कार्यासाठी अधिक योग्य असेल. जेव्हा आर-स्क्वेअर मूल्य 0.8 पेक्षा जास्त असते तेव्हा वास्तविक परिस्थितीचे अचूक वर्णन करणे मानले जाते. जर R-वर्ग 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 दशलक्ष यूएस डॉलर्स इतके फुगलेले आहे.

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

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