समीकरण उदाहरण: Excel में प्रतिगमन। रेखीय प्रतीपगमन

प्रतिगमन विश्लेषण - एक या अधिक स्वतंत्र चर का एक पैरामीटर की निर्भरता को दिखाने के लिए एक सांख्यिकीय अध्ययन विधि। पूर्व कंप्यूटर युग में, इसके उपयोग नहीं बल्कि मुश्किल हो गया है, खासकर जब यह डेटा की बड़ी मात्रा के लिए आया था। आज, Excel में प्रतिगमन निर्माण करने के लिए सीखने के लिए, आप बस कुछ ही मिनटों में जटिल सांख्यिकीय समस्याओं को हल कर सकते हैं। नीचे अर्थशास्त्र के विशिष्ट उदाहरण हैं।

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

इस अवधारणा को गणित के लिए पेश किया गया था फ्रांसिस गैलटॉन द्वारा 1886 में। प्रतिगमन है:

• रैखिक;
• परवलयिक;
• शक्ति;
• घातीय;
• अतिशयोक्तिपूर्ण;
• घातीय;
• लघुगणक।

उदाहरण 1

6 औद्योगिक उद्यमों में औसत मजदूरी का स्टाफ के सदस्यों के इस्तीफे की संख्या की निर्भरता का निर्धारण करने की समस्या पर विचार करें।

टास्क। छह कंपनियों औसत मासिक वेतन और कर्मचारियों को जो स्वेच्छा से छोड़ने की संख्या का विश्लेषण किया है। सारणी के रूप में हमने:

	एक	बी	सी
1	एक्स	इस्तीफे की संख्या	वेतन
2		y	30000 रूबल
3	1	60	35000 रूबल
4	2	35	40000 रूबल
5	3	20	45,000 रूबल
6	4	20	50,000 रूबल
7	5	15	55000 रूबल
8	6	15	60000 रूबल

6 उद्यमों प्रतिगमन मॉडल के लिए औसत वेतन से राशि विभाजन श्रमिकों की निर्भरता का निर्धारण करने की समस्या के लिए स्वरूप समीकरण y = एक ₀ + एक ₁ x ₁ + ... + एक _{कश्मीर} एक्स _{कश्मीर,} x _मैं - को प्रभावित चर, एक _मैं - प्रतिगमन गुणांक, एके - कारकों की संख्या।

वाई दिए गए कार्य के लिए - वेतन, जो एक्स से दर्शाया जाता है - यह एक कर्मचारी आग एक संकेतक, एक योगदान कारक है

"एक्सेल" स्प्रेडशीट की शक्ति का दोहन

Excel में प्रतिगमन विश्लेषण बिल्ट-इन कार्य मौजूदा तालिका डेटा के लिए एक आवेदन से पहले किया जाना चाहिए। हालांकि, इन उद्देश्यों के लिए यह एक बहुत ही उपयोगी ऐड-इन "पैकेट विश्लेषण" का उपयोग करने के लिए बेहतर है। इसे सक्षम करने के लिए आपको करने की जरूरत है:

• साथ टैब "फ़ाइल" "सेटिंग" जाना;
• खुलने वाली विंडो में, 'एड-ऑन' का चयन करें;
• बटन "गो" लाइन "प्रबंधन" के नीचे दाईं ओर स्थित पर क्लिक करें;
• एक जाँच चिह्न "विश्लेषण ToolPak" के आगे रख दिया और दबाने "ठीक है" कर अपनी क्रिया की पुष्टि करें।

यदि सही ढंग से किया, "डाटा" टैब के दाईं ओर, काम चादर "एक्सेल" के ऊपर स्थित है, वांछित बटन को दर्शाता है।

Excel में रैखिक प्रतिगमन

अब आप अर्थमितीय गणना के लिए सभी आवश्यक उपकरण आभासी हाथ पर है, हम अपने समस्या का समाधान करने के लिए शुरू कर सकते हैं। ऐसा करने के लिए:

• बटन "डेटा विश्लेषण" पर क्लिक किया जाता है;
• खुली खिड़की में बटन "प्रतिगमन" पर क्लिक करें;
• एक टैब पर मानों की श्रेणी को पेश करने प्रतीत होता है कि वाई (विभाजन श्रमिकों की संख्या) और एक्स (अपने वेतन);
• «ठीक है» बटन दबाने से उनके कार्यों की पुष्टि।

नतीजतन, प्रोग्राम स्वचालित रूप से नए पत्रक स्प्रेडशीट डेटा प्रतिगमन विश्लेषण भर जाएगा। ध्यान! एक्सेल में, वहाँ जगह है कि आप इस उद्देश्य के लिए पसंद करते हैं स्थापित करने के लिए एक अवसर है। उदाहरण के लिए, यह एक ही चादर, जहां मूल्यों Y और एक्स, या यहां तक कि एक नई किताब, विशेष रूप से इस तरह के आंकड़ों के भंडारण के लिए बनाया गया हो सकता है।

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

एक्सेल डेटा माना उदाहरण डेटा में प्राप्त फ़ॉर्म दिया गया है:

सबसे पहले, हम आर-वर्ग के मूल्य के ध्यान देना चाहिए। यह दृढ़ संकल्प के गुणांक प्रतिनिधित्व करता है। इस उदाहरण में, आर-वर्ग = 0.755 (75.5%), एम। ई मॉडल की गणना मापदंडों मापदंडों 75.5% द्वारा विचार के बीच संबंधों की व्याख्या करने के। उच्च दृढ़ संकल्प के गुणांक का मान, चयनित मॉडल विशेष कार्यों के लिए अधिक उपयोगी माना जाता है। यह सही ढंग से 0.8 ऊपर आर-वर्ग मूल्य पर वास्तविक स्थिति का वर्णन करने के लिए माना जाता है। आर-वर्ग <0.5, तो Excel में प्रतिगमन विश्लेषण उचित नहीं माना जा सकता है।

अनुपात विश्लेषण

संख्या 64.1428 पता चलता है कि, वाई के मूल्य में हो सकता है अगर हमारे मॉडल में सभी चर xi रीसेट हो जाएगा। दूसरे शब्दों में, यह तर्क दिया जा सकता है कि विश्लेषण किया पैरामीटर का मान विशिष्ट मॉडल में वर्णित के अलावा अन्य कारकों से प्रभावित होता।

अगले कारक -०.१६२८५ सेल B18 में स्थित है, से पता चलता वाई चर एक्स के महत्वपूर्ण प्रभाव इसका मतलब है कि मॉडल के भीतर कर्मचारियों की औसत वेतन -०.१६२८५ के वजन, टी से इस्तीफे की संख्या को प्रभावित करता है। ई सब पर इसके प्रभाव की डिग्री छोटे। हस्ताक्षर "-" इंगित करता है कि गुणांक नकारात्मक है। यह स्पष्ट के बाद से हम सभी जानते हैं कि उद्यम में और अधिक वेतन, कम लोगों को रोजगार या खारिज की अनुबंध समाप्त करने की इच्छा व्यक्त की है,।

कई प्रतिगमन

इस अवधि के तहत फार्म के कई स्वतंत्र चर के साथ संचार समीकरण को दर्शाता है:

y = f (x ₁ + x ₂ + ... एक्स _एम) + ε, जहां y - एक फीचर स्कोर (आश्रित चर) है, और एक्स _1, एक्स _2, ... एक्स _मीटर - संकेत कारकों (स्वतंत्र चर) कर रहे हैं।

पैरामीटर आकलन

कई प्रतिगमन (एमआर) के लिए यह एक न्यूनतम वर्ग विधि (LSM) का उपयोग किया जाता है। प्रपत्र वाई = ए + बी ₁ x _{1 + ... + ख मीटर x मीटर +} के रेखीय समीकरण के लिए ε सामान्य समीकरणों की एक प्रणाली के निर्माण (सेमी। नीचे)

विधि के सिद्धांत को समझने के लिए, हम दो कारक मामले पर विचार करें। फिर हम स्थिति सूत्र द्वारा वर्णन किया है

इसलिए, हम प्राप्त:

जहां σ - संबंधित सुविधा की भिन्नता है, सूचकांक में परिलक्षित।

MNC के लिए समीकरण एमआर पैमाने standartiziruemom लिए लागू है। इस मामले में, हम समीकरण मिलती है:

जिसमें टी _y, टी _{एक्स 1, ...} टी _xm - चर जिसके लिए औसत मान 0 हैं standartiziruemye; _मैं बीटा - मानकीकृत प्रतिगमन गुणांक और मानक विचलन - 1।

कृपया ध्यान दें कि सभी _मैं बीटा इस मामले में, इसलिए बीच तुलना सामान्यीकृत और tsentraliziruemye के रूप में परिभाषित एक वैध और स्वीकार्य माना। इसके अलावा, यह कारकों की स्क्रीनिंग बाहर ले जाने के स्वीकार किया जाता है, उन है कि βi के निम्नतम मान को त्यागकर।

रेखीय प्रतीपगमन समीकरण का उपयोग कर के साथ समस्या

मान लीजिए आप पिछले 8 महीने के लिए एक विशेष उत्पाद एन की कीमत की गतिशीलता की एक तालिका है। यह है कि क्या 1850 रूबल की कीमत पर उनकी पार्टी के अधिग्रहण का फैसला करने के लिए आवश्यक है। / टी

	एक	बी	सी
1	माह	महीने के नाम	मूल्य एन
2	1	जनवरी	प्रति टन 1750 रूबल
3	2	फरवरी	प्रति टन 1755 रूबल
4	3	मार्च	प्रति टन 1767 रूबल
5	4	अप्रैल	प्रति टन 1760 रूबल
6	5	मई	प्रति टन 1770 रूबल
7	6	जून	प्रति टन 1790 रूबल
8	7	जुलाई	प्रति टन 1810 रूबल
9	8	अगस्त	प्रति टन 1840 रूबल

सारणीबद्ध प्रोसेसर "एक्सेल" पहले से ही उदाहरण उपकरण "डेटा विश्लेषण" ऊपर प्रस्तुत के लिए जाना जाता उपयोग करने की आवश्यकता में इस समस्या को हल करने के लिए। बगल में, चुनें "प्रतिगमन" खंड पैरामीटर सेट। हमें याद रखना चाहिए कि में इनपुट अंतराल एक्स »" इनपुट रेंज Y »आश्रित चर के मूल्यों की एक श्रृंखला के लिए (इस मामले में माल की कीमत साल के विशिष्ट महीनों में) और में पेश किया जाना चाहिए" - एक स्वतंत्र (माह) के लिए। हम «ठीक है» पर क्लिक करके इसकी पुष्टि करें। एक नए कार्यपत्रक (यदि ऐसा है तो इंगित) में, हम प्रतिगमन के लिए डेटा प्राप्त।

हम प्रपत्र y = ax + b, जहां मानकों ए और बी के रूप में रेखा और गुणांकों के महीने की संख्या नाम और «Y-चौराहे" प्रतिगमन विश्लेषण के परिणामों के साथ चादर की रेखा से गुणांकों हैं रेखीय समीकरण उन पर निर्माण कर रहे हैं। इस प्रकार, रेखीय प्रतीपगमन समीकरण (EQ) 3 समस्या के लिए के रूप में लिखा जा सकता है:

माल की कीमत एन = 11,714 * 1727.54 महीने संख्या +।

या बीजीय अंकन में

y = 11,714 x + 1727,54

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

तय करने के लिए प्राप्त है कि क्या कई सहसंबंध गुणांक (सीएमसी) और दृढ़ संकल्प के साथ ही परीक्षण और फिशर के टी परीक्षण का उपयोग कर पर्याप्त रूप से रेखीय प्रतीपगमन समीकरण। परिणाम वे नाम कई आर, आर स्क्वायर, एफ टी सांख्यिकी और आंकड़े क्रमश: के तहत कार्य के साथ तालिका "एक्सेल" प्रतिगमन में।

KMC आर स्वतंत्र और आश्रित चर के बीच निकटता संभाव्य संबंध अनुमान लगाने के लिए सक्षम बनाता है। इसकी उच्च मूल्य चर "महीने की संख्या" और के बीच एक मजबूत पर्याप्त कनेक्शन इंगित करता है "1 प्रति टन रूबल में एन उत्पाद की कीमत।" हालांकि, इस रिश्ते की प्रकृति अज्ञात है।

दृढ़ संकल्प आर ² के गुणांक के वर्ग (आरआई) है, यानी कुल बिखराव के अनुपात का एक अंकीय विशेषता है और प्रयोगात्मक डेटा भाग के बिखराव पता चलता है, आश्रित चर एक रेखीय प्रतीपगमन समीकरण के लिए इसी के मूल्यों। इस समस्या में, यह मान 84.8%, मध्य प्रदेश। सटीकता प्राप्त की एक उच्च डिग्री के साथ ई सांख्यिकी वर्णित हैं एसडी है।

एफ सांख्यिकी, भी फिशर कसौटी के रूप में जाना रैखिक निर्भरता या उसके अस्तित्व की पुष्टि परिकल्पना खंडन करने के महत्व का आकलन किया।

टी आंकड़ा (विद्यार्थी t- परीक्षण) के मूल्य में किसी भी मुफ्त अज्ञात रैखिक निर्भरता सदस्य पर गुणांक के महत्व का मूल्यांकन में मदद करता है। टी परीक्षण> टी _{करोड़} का मान _है, तो मुक्त अवधि की एक रेखीय समीकरण निरर्थकता की परिकल्पना को अस्वीकार कर दिया गया है।

उपकरणों के माध्यम से एक नि: शुल्क अवधि के लिए इस समस्या में "एक्सेल" यह पाया गया टी = कि 169,20903, और पी = 2,89E -12, टी। ई एक शून्य संभावना है कि वफादार मुक्त अवधि की निरर्थकता की परिकल्पना अस्वीकार कर दिया जाएगा है। टी = 5,79405 पर अज्ञात गुणांक, और पी = 0,001158 लिए। दूसरे शब्दों में, संभावना है कि एक को अस्वीकार कर दिया सही परिकल्पना अज्ञात के लिए गुणांक के निरर्थकता जाएगा, 0.12% है।

इस प्रकार, यह तर्क दिया जा सकता है कि पर्याप्त रूप से प्राप्त रेखीय प्रतीपगमन समीकरण।

शेयरों को खरीदने के औचित्य की समस्या

एकाधिक प्रतिगमन एक ही "डेटा विश्लेषण" उपकरण का उपयोग कर Excel में प्रदर्शन किया था। विशिष्ट अनुप्रयोग पर विचार करें।

गाइड कंपनी «NNN» तय करना होगा जेएससी «MMM» के शेयरों का 20% खरीद करने के लिए है या नहीं। पैकेज की कीमत (सपा) 70 लाख अमरीकी डॉलर है। «NNN» के विशेषज्ञ समान लेन-देन पर डाटा एकत्र। यह इस तरह के मानकों के आधार पर शेयरों के मूल्य का आकलन करने का निर्णय लिया गया था, जैसे करोड़ों अमेरिकी डॉलर, में व्यक्त किया:

• देय (वीके);
• सालाना कारोबार की मात्रा (VO);
• प्राप्तियों (VD);
• अचल संपत्ति (SOF) का मूल्य।

इसके अलावा, दैनिक वेतन हजारों डॉलर में उद्यमों के ऋण (वी 3 यू) का उपयोग करें।

निर्णय तालिका प्रोसेसर एक्सेल साधन

सबसे पहले आप इनपुट डेटा की एक तालिका बनाने के लिए की जरूरत है। यह इस प्रकार है:

अगला:

• कॉल बॉक्स "डेटा विश्लेषण";
• चयनित "प्रतिगमन" खंड;
• खिड़की "इनपुट अंतराल Y» प्रशासित रेंज निर्भर चर स्तंभ जी से मूल्यों;
• खिड़की "इनपुट अंतराल एक्स» के अधिकार के लिए एक लाल तीर के साथ आइकन पर क्लिक करें और स्तंभ बी, सी, डी, एफ के सभी मानों के एक पत्रक सीमा पर अलग-थलग

मार्क बिंदु "नया वर्कशीट" और "ठीक" क्लिक।

इस कार्य के लिए एक प्रतिगमन विश्लेषण प्राप्त करें।

अध्ययन के परिणाम और निष्कर्ष

"इकट्ठा" चादर तालिका एक्सेल प्रोसेसर प्रतिगमन समीकरण से ऊपर प्रस्तुत इन आंकड़ों से गोल:

एसडी = 0.103 * SOF + 0541 * VO - 0031 * वीके + 0405 * वीडी + 0691 * VZP - 265,844।

अधिक सामान्य गणितीय रूप में यह रूप में लिखा जा सकता है:

y = 0103 * x 1 + 0541 * x2 - 0031 * x3 + 0405 * x4 + 0691 * x5 - 265,844

«MMM» जेएससी के लिए डेटा नीचे दी गई तालिका में प्रस्तुत किया:

SOF, अमरीकी डालर	VO, अमरीकी डालर	वीके, अमरीकी डालर	वीडी, अमरीकी डालर	VZP, अमरीकी डालर	जेवी, अमरीकी डालर
102.5	535.5	45.2	41.5	21.55	64.72

उन्हें प्रतिगमन समीकरण में स्थानापन्न, 64,72 लाख अमरीकी डॉलर का एक आंकड़ा प्राप्त की। इसका मतलब है कि जेएससी «MMM» के शेयरों को खरीदने नहीं करना चाहिए, क्योंकि उनकी लागत काफी 70 लाख अमेरिकी डॉलर में अधिक है।

आप देख सकते हैं, स्प्रेडशीट के उपयोग "एक्सेल" और प्रतिगमन समीकरण एक सूचित औचित्य काफी विशिष्ट लेन-देन के बारे में निर्णय करने के लिए अनुमति दी।

अब आप जानते हैं कि एक प्रतिगमन। एक्सेल में उदाहरण हैं, ऊपर चर्चा की, अर्थमिति के व्यावहारिक समस्याओं को सुलझाने में मदद मिलेगी।