एक्सेल में सन्निकटन कैसे करें? माइक्रोसॉफ्ट एक्सेल में प्रयोगात्मक डेटा का अनुमान।

पिछले पाठों की तरह, समान पाठ वाला यह पाठ एक्सेल शीट पर सबसे अच्छा देखा जाता है (अनुमान पाठ देखें.xls, शीट 1)

एक्सेल में अनुमान किसी ट्रेंडिंग प्रोग्राम का उपयोग करके सबसे आसानी से पूरा किया जाता है। सन्निकटन की विशेषताओं को स्पष्ट करने के लिए, आइए एक विशिष्ट उदाहरण लें। उदाहरण के लिए, एस.एल. रिवकिन और ए.ए. अलेक्जेंड्रोव की पुस्तक "जल और जल वाष्प के थर्मोफिजिकल गुण", एम., "ऊर्जा", 1980 के अनुसार संतृप्त भाप की एन्थैल्पी। कॉलम P में हम दबाव मान को kgf/cm2 में रखेंगे, कॉलम i" में - संतृप्ति रेखा पर भाप की एन्थैल्पी kcal/kg में रखेंगे और "चार्ट विज़ार्ड" विकल्प या बटन का उपयोग करके एक ग्राफ बनाएंगे।

आइए चित्र में लाइन पर राइट-क्लिक करें, फिर "ट्रेंड लाइन जोड़ें" विकल्प पर बायाँ-क्लिक करें और देखें कि एक्सेल में सन्निकटन को लागू करने के संदर्भ में यह विकल्प हमें कौन सी सेवाएँ प्रदान करता है।

हमें पाँच प्रकार के सन्निकटन का विकल्प दिया जाता है: रैखिक, घात, लघुगणक, घातीय और बहुपद। वे किसके लिए अच्छे हैं और वे हमारी कैसे मदद कर सकते हैं? - F1 बटन दबाएं, फिर "उत्तर विज़ार्ड" विकल्प पर क्लिक करें और दिखाई देने वाली विंडो में हमें जिस शब्द "सन्निकटन" की आवश्यकता है उसे दर्ज करें, फिर "ढूंढें" बटन पर क्लिक करें। दिखाई देने वाली सूची में, "ट्रेंड लाइनों के निर्माण के लिए सूत्र" अनुभाग का चयन करें।

हमें निम्नलिखित जानकारी हमारे द्वारा थोड़ी संशोधित रूप से प्राप्त होती है

संपादक:

रैखिक:

जहाँ b झुकाव का कोण है और a भुज अक्ष (मुक्त पद) के प्रतिच्छेदन का निर्देशांक है।

शक्ति:

समीकरण के अनुसार न्यूनतम वर्ग विधि का उपयोग करके डेटा को फ़िट करने के लिए उपयोग किया जाता है:

जहाँ c और b स्थिरांक हैं।

लघुगणक:

समीकरण के अनुसार न्यूनतम वर्ग विधि का उपयोग करके डेटा को फ़िट करने के लिए उपयोग किया जाता है:

जहाँ a और b स्थिरांक हैं।

घातीय:

समीकरण के अनुसार न्यूनतम वर्ग विधि का उपयोग करके डेटा को फ़िट करने के लिए उपयोग किया जाता है:

जहाँ b और k स्थिरांक हैं।

बहुपद:

समीकरण के अनुसार न्यूनतम वर्ग विधि का उपयोग करके डेटा को फ़िट करने के लिए उपयोग किया जाता है:

y=a+b1*x+b2*x^2+b3*x^3+...b6*x^6

जहाँ a, b1, b2, b3,... b6 स्थिरांक हैं।

ड्राइंग लाइन पर फिर से क्लिक करें, फिर "ट्रेंड लाइन जोड़ें" विकल्प पर, फिर "पैरामीटर" विकल्प पर और प्रविष्टियों के बाईं ओर के बॉक्स में चेक करें: "आरेख पर समीकरण दिखाएं" और "दबाएं" आरेख पर सन्निकटन विश्वास मान R^2, फिर ओके बटन पर क्लिक करें और सभी सन्निकटन विकल्पों को क्रम से आज़माएँ।

रैखिक सन्निकटन हमें R^2=0.9291 देता है - यह कम विश्वसनीयता और खराब परिणाम है।

पावर-लॉ सन्निकटन पर स्विच करने के लिए, ट्रेंड लाइन पर राइट-क्लिक करें, फिर "ट्रेंड लाइन फॉर्मेट" विकल्प पर बायाँ-क्लिक करें, फिर "टाइप" और "पावर" विकल्प पर क्लिक करें। इस बार हमें R^2=0.999 मिला।

आइए ट्रेंड लाइन समीकरण को एक्सेल शीट पर गणना के लिए उपयुक्त रूप में लिखें:

y=634.16*x^0.012

परिणामस्वरूप हमारे पास है:

अधिकतम सन्निकटन त्रुटि 0.23 किलो कैलोरी/किग्रा पाई गई। प्रयोगात्मक डेटा का अनुमान लगाने के लिए यह एक अद्भुत परिणाम होगा, लेकिन लुकअप तालिका का अनुमान लगाने के लिए यह बहुत अच्छा परिणाम नहीं है। इसलिए, आइए ट्रेंड बिल्डिंग प्रोग्राम का उपयोग करके एक्सेल में अन्य सन्निकटन विकल्पों की जांच करने का प्रयास करें।

लॉगरिदमिक सन्निकटन हमें R^2=0.9907 देता है - पावर संस्करण से कुछ हद तक खराब। ट्रेंड बिल्डिंग प्रोग्राम द्वारा प्रस्तुत संस्करण में घातांक बिल्कुल फिट नहीं था - R^2=0.927।

घात 2 के साथ बहुपद सन्निकटन (यह y=a+b1*x+b2*x^2 है) प्रदान किया गया R^2=0.9896। डिग्री 3 पर, हमने R^2=0.999 प्राप्त किया, लेकिन अनुमानित वक्र के स्पष्ट विरूपण के साथ, विशेष रूप से P>0.07 kgf/cm2 पर। अंत में, पाँचवीं शक्ति हमें R^2=1 देती है - इसे मूल डेटा और उनके सन्निकटन के बीच निकटतम संबंध कहा जाता है।

आइए एक्सेल शीट पर गणना के लिए उपयुक्त रूप में बहुपद समीकरण को फिर से लिखें:

y=1E+07*x^5-4E+06*x^4+469613*x^3-27728*x^2+1020.8*x+592.44

और सन्निकटन परिणाम की तुलना मूल तालिका से करें:

यह पता चला कि इस मामले में R^2=1 सिर्फ एक शानदार झूठ है। वास्तव में, बहुपद सन्निकटन का सर्वोत्तम परिणाम y=a+b1*x+b2*x^2 रूप के सरलतम बहुपद द्वारा दिया गया था। लेकिन इसका परिणाम पावर-लॉ सन्निकटन संस्करण y=634.16*x^0.012 से भी बदतर है, जहां अधिकतम सन्निकटन त्रुटि 0.23 kcal/kg के स्तर पर थी। ट्रेंडिंग प्रोग्राम से हम बस इतना ही बाहर निकल सकते हैं। आइए देखें कि हम लीनियर फ़ंक्शन से क्या प्राप्त कर सकते हैं। इसके लिए, हम पावर-लॉ सन्निकटन विकल्प का प्रयास करेंगे।

टिप्पणी। पाया गया दोष ट्रेंडिंग प्रोग्राम के संचालन से संबंधित है, लेकिन कम से कम वर्ग विधि से नहीं।

निर्भरताएँ

एक्सेल में ऐसे उपकरण हैं जो आपको प्रक्रियाओं की भविष्यवाणी करने की अनुमति देते हैं। सन्निकटन समस्या तब उत्पन्न होती है जब जीवन में घटित होने वाली घटनाओं का विश्लेषणात्मक रूप से वर्णन करना आवश्यक होता है और तर्क (तर्क) और कार्यों के मूल्यों वाली तालिकाओं के रूप में दिया जाता है। यदि निर्भरता पाई जा सकती है, तो भविष्य में अध्ययन के तहत प्रणाली के व्यवहार के बारे में भविष्यवाणी करना संभव है और, संभवतः, इसके विकास के लिए इष्टतम दिशा चुनना संभव है। इस तरह के एक विश्लेषणात्मक कार्य (जिसे प्रवृत्ति भी कहा जाता है) में सिस्टम की जटिलता और प्रतिनिधित्व की वांछित सटीकता के आधार पर विभिन्न रूप और जटिलता के विभिन्न स्तर हो सकते हैं।

10.1. रेखीय प्रतिगमन

सबसे सरल और सबसे लोकप्रिय सीधी रेखा सन्निकटन है - रैखिक प्रतिगमन।

आइए हम एक्स निवेश के आकार के आधार पर लाभ स्तर वाई के बारे में वास्तविक जानकारी प्राप्त करें - वाई(एक्स)। चित्र में. चित्र 10.1-1 ऐसे चार बिंदु M(Y,X) दिखाता है। आइए हमारे पास यह मानने का कारण भी है कि यह निर्भरता रैखिक है, यानी। की तरह लगता है वाई=ए+बीएक्स.यदि हम गुणांक ए और बी ढूंढने में सक्षम थे और उन्हें एक सीधी रेखा बनाने के लिए उपयोग करते थे (उदाहरण के लिए, जैसा कि चित्र में है), भविष्य में हम व्यवसाय की गतिशीलता और संभावित वाणिज्यिक स्थिति के बारे में सूचित धारणाएं बना सकते हैं भविष्य में उद्यम का. जाहिर है, हम ज्ञात बिंदुओं एम(वाई,एक्स) के जितना संभव हो उतना करीब स्थित एक सीधी रेखा से संतुष्ट होंगे, यानी। विचलनों का न्यूनतम योग या त्रुटियों का योग होना (आकृति में, विचलनों को बिंदीदार रेखाओं द्वारा दिखाया गया है)। मालूम हो कि ऐसी एक ही लाइन है.

इस समस्या को हल करने के लिए न्यूनतम वर्ग त्रुटि विधि का उपयोग किया जाता है। बिंदु M1(Y1,X1) के ज्ञात मान Y1 और समान मान X1 के लिए सीधी रेखा समीकरण का उपयोग करके गणना किए गए मान Y(X1) के बीच अंतर (त्रुटि) होगा

D1 = Y1 – A – B X1.

एक ही अंतर

X=X2 के लिए D2 = Y2 – A – B X2 होगा;

X=X3 के लिए D3 = Y3 – A – B X3;

और X=X4 D4 = Y4 – A – B X4 के लिए।

आइए इन त्रुटियों के वर्गों के योग के लिए एक व्यंजक लिखें

Ф(A,В)=(Y1–A–B X1) 2 +(Y2–A–B X2) 2 +(Y3–A–B X3) 2 +(Y4–A–B X4) 2

या संक्षिप्त Ф(बी,ए) = å(यी - ए - बीएक्सआई) 2.

यहाँ हम सभी न्यूनतमता की शर्तें ज्ञात संबंध हैं

¶Ф(A,B)/¶A=0 और ¶Ф(A,B)/¶B=0.

आइए हम इन अभिव्यक्तियों को प्राप्त करें (हम योग चिह्न पर सबस्क्रिप्ट को छोड़ देते हैं):

¶[å(यी-ए-बी शी) 2 ]/¶ए = å(यी-ए-बी शी)(-1)

¶[å(Yi–A–B Xi) 2 ]/¶B = å(Yi–A–B Xi)(–Xi).

आइए हम परिणामी सूत्रों को रूपांतरित करें और उन्हें शून्य के बराबर करें

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

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

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

लेकिन इसका निर्माण पाँच प्रकार के सन्निकटनों में से एक का उपयोग करके किया जा सकता है:

• रैखिक;
• घातीय;
• लघुगणक;
• बहुपद;
• ताकतवर।

आइए प्रत्येक विकल्प पर अलग से अधिक विस्तार से विचार करें।

विधि 1: रैखिक चौरसाई

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

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

इस मामले में प्रयुक्त स्मूथिंग को निम्नलिखित सूत्र द्वारा वर्णित किया गया है:

हमारे विशिष्ट मामले में, सूत्र निम्नलिखित रूप लेता है:

y=-0.1156x+72.255

हमारा सन्निकटन विश्वसनीयता मान बराबर है 0,9418 , जो एक काफी स्वीकार्य परिणाम है, जो स्मूथिंग को विश्वसनीय बनाता है।

विधि 2: घातांकीय सन्निकटन

आइए अब एक्सेल में घातीय प्रकार के सन्निकटन को देखें।

स्मूथिंग फ़ंक्शन का सामान्य स्वरूप इस प्रकार है:

कहाँ इप्राकृतिक लघुगणक का आधार है.

हमारे विशेष मामले में, सूत्र ने निम्नलिखित रूप लिया:

y=6282.7*e^(-0.012*x)

विधि 3: लॉगरिदमिक स्मूथिंग

अब बारी है लघुगणकीय सन्निकटन विधि पर विचार करने की।

सामान्य तौर पर, स्मूथिंग फॉर्मूला इस तरह दिखता है:

कहाँ एल.एनप्राकृतिक लघुगणक का मान है. इसलिए विधि का नाम.

हमारे मामले में, सूत्र निम्नलिखित रूप लेता है:

y=-62.81ln(x)+404.96

विधि 4: बहुपद चौरसाई

अब बहुपद चौरसाई विधि पर विचार करने का समय आ गया है।

इस प्रकार की स्मूथिंग का वर्णन करने वाला सूत्र निम्नलिखित रूप लेता है:

y=8E-08x^6-0.0003x^5+0.3725x^4-269.33x^3+109525x^2-2E+07x+2E+09

विधि 5: पावर स्मूथिंग

अंत में, आइए एक्सेल में पावर सन्निकटन विधि को देखें।

फ़ंक्शन डेटा में गहन परिवर्तन के मामलों में इस पद्धति का प्रभावी ढंग से उपयोग किया जाता है। यह ध्यान रखना महत्वपूर्ण है कि यह विकल्प केवल तभी लागू होता है जब फ़ंक्शन और तर्क नकारात्मक या शून्य मान स्वीकार नहीं करते हैं।

इस विधि का वर्णन करने वाला सामान्य सूत्र इस प्रकार है:

हमारे विशिष्ट मामले में, यह इस तरह दिखता है:

y = 6E+18x^(-6.512)

जैसा कि आप देख सकते हैं, विशिष्ट डेटा का उपयोग करते समय जिसे हमने उदाहरण के रूप में उपयोग किया था, विश्वसनीयता का उच्चतम स्तर छठी डिग्री के बहुपद के साथ बहुपद सन्निकटन की विधि द्वारा दिखाया गया था ( 0,9844 ), रैखिक विधि में विश्वसनीयता का निम्नतम स्तर होता है ( 0,9418 ). लेकिन इसका मतलब यह बिल्कुल नहीं है कि अन्य उदाहरणों का उपयोग करते समय भी वही प्रवृत्ति घटित होगी। नहीं, उपरोक्त विधियों की प्रभावशीलता का स्तर विशिष्ट प्रकार के फ़ंक्शन के आधार पर काफी भिन्न हो सकता है जिसके लिए ट्रेंड लाइन का निर्माण किया जाएगा। इसलिए, यदि चुनी गई विधि इस फ़ंक्शन के लिए सबसे प्रभावी है, तो इसका मतलब यह बिल्कुल नहीं है कि यह किसी अन्य स्थिति में भी इष्टतम होगी।

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

याद रखें कि प्रतिगमन विश्लेषण एक प्रकार का सांख्यिकीय विश्लेषण है जिसका उपयोग पूर्वानुमान के लिए किया जाता है। प्रतिगमन विश्लेषण आपको पहले से ज्ञात कई मूल्यों से एक चर के अपेक्षित मूल्य की गणना करने के लिए एक तंत्र की पेशकश करके चर के बीच संबंध की ताकत का अनुमान लगाने की अनुमति देता है।

ट्रेंड लाइनें असामान्य क्षेत्र चार्ट, बार चार्ट, हिस्टोग्राम, ग्राफ़, स्टॉक चार्ट, स्कैटर चार्ट और बबल चार्ट में प्रस्तुत डेटा श्रृंखला को पूरक कर सकती हैं। किसी न किसी प्रकार की ट्रेंड लाइन का उपयोग डेटा के प्रकार से निर्धारित होता है। आप 3-डी चार्ट, सामान्य चार्ट, रडार चार्ट, पाई चार्ट या डोनट चार्ट में डेटा श्रृंखला में ट्रेंड लाइनें नहीं जोड़ सकते।

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

डेटा श्रृंखला में एक ट्रेंड लाइन या मूविंग एवरेज जोड़ना

एक्सेल छह अलग-अलग प्रकार की ट्रेंड लाइनों (फिटिंग और स्मूथिंग) का उपयोग करता है जिन्हें चार्ट में जोड़ा जा सकता है (चित्र 18.11):

1. रैखिक सन्निकटन(रैखिक) एक सीधी रेखा है जो डेटा सेट का सबसे अच्छा वर्णन करती है। एक सीधी रेखा का समीकरण y=ax+b है, जहां a झुकाव के कोण की स्पर्शरेखा है, b, y-अक्ष के साथ सीधी रेखा का प्रतिच्छेदन बिंदु है। रैखिक सन्निकटन का उपयोग उन चरों के लिए किया जाता है जो स्थिर दर पर बढ़ते या घटते हैं।
2. लघुगणकीय सन्निकटन(लघुगणक) सकारात्मक और नकारात्मक दोनों मात्राओं का अच्छी तरह से वर्णन करता है, जो शुरू में तेजी से बढ़ती या घटती हैं, और फिर धीरे-धीरे स्थिर हो जाती हैं। लघुगणक सन्निकटन समीकरण y=c*lnx+b का उपयोग करता है, जहां c और b स्थिरांक हैं, In प्राकृतिक लघुगणक है।
3. बहुपद सन्निकटन(बहुपद) का उपयोग उन मात्राओं का वर्णन करने के लिए किया जाता है जो बारी-बारी से बढ़ती और घटती हैं। अस्थिर परिमाण के बड़े डेटा सेट का विश्लेषण करने के लिए इसका उपयोग करने की सलाह दी जाती है। बहुपद की डिग्री वक्र के एक्स्ट्रेमा (मैक्सिमा और मिनिमा) की संख्या से निर्धारित होती है। एक दूसरी डिग्री बहुपद केवल एक अधिकतम या न्यूनतम का वर्णन कर सकता है। तृतीय डिग्री बहुपद में एक या दो चरम होते हैं। चतुर्थ-डिग्री बहुपद में तीन से अधिक एक्स्ट्रेमा नहीं हो सकते। बहुपद सन्निकटन का वर्णन समीकरण y=a+ciXi+C2X2++Cigx18 द्वारा किया जाता है, जहां a, Cj-Cjg स्थिरांक हैं। बहुपद की आवश्यक डिग्री डिग्री फ़ील्ड (चित्र) में निर्दिष्ट है। अधिकतम डिग्री मान 18 है.
4. शक्ति सन्निकटन(पावर) अच्छे परिणाम देता है यदि डेटा में निहित निर्भरता निरंतर विकास दर की विशेषता है। ऐसे रिश्ते का एक उदाहरण कार का त्वरण ग्राफ है। यदि डेटा में शून्य या नकारात्मक मान हैं, तो पावर-लॉ सन्निकटन का उपयोग नहीं किया जा सकता है। शक्ति-नियम सन्निकटन को समीकरण y=a * xn द्वारा वर्णित किया गया है, जहां a और n स्थिरांक हैं।
5. घातीय सन्निकटनयदि डेटा परिवर्तन की दर लगातार बढ़ रही हो तो (एक्सपोनेंशियल) का उपयोग किया जाना चाहिए। हालाँकि, उस डेटा के लिए जिसमें शून्य या नकारात्मक मान हैं, इस प्रकार का सन्निकटन लागू नहीं है। घातीय सन्निकटन का वर्णन समीकरण y = a ebx द्वारा किया जाता है, जहाँ a और b स्थिरांक हैं।
6. रैखिक फ़िल्टरिंग(मूविंग एवरेज) आपको डेटा के उतार-चढ़ाव को सुचारू करने की अनुमति देता है और इस प्रकार निर्भरता की प्रकृति को अधिक स्पष्ट रूप से दिखाता है। ऐसी प्रवृत्ति रेखा का निर्माण एक निश्चित संख्या में बिंदुओं से किया जाता है (यह अवधि पैरामीटर द्वारा निर्दिष्ट किया जाता है)। से 2 तक, स्मूथिंग वक्र का पहला बिंदु पहले दो डेटा तत्वों के औसत के रूप में परिभाषित किया गया है, दूसरा बिंदु - अगले दो तत्वों के औसत के रूप में, आदि। चलती औसत की गणना करने के लिए, समीकरण y = (Aj+) Aj_i++Aj_n+i)/n का प्रयोग किया जाता है।

डेटा श्रृंखला में एक ट्रेंड लाइन जोड़ना

किसी डेटा श्रृंखला में ट्रेंडलाइन जोड़ने के लिए, इन चरणों का पालन करें:

• उस डेटा श्रृंखला का चयन करें जिसमें आप ट्रेंड लाइन या मूविंग एवरेज जोड़ना चाहते हैं;
• टीम का चयन एक ट्रेंड लाइन जोड़ें(ट्रेंडलाइन जोड़ें) मेनू में आरेख(चार्ट)। टैब पर प्रकार(प्रकार) वांछित प्रकार की प्रतिगमन प्रवृत्ति रेखा या चलती औसत रेखा का चयन करें (चित्र 18.11);
• एक प्रकार चुनते समय बहुपद(बहुपद) क्षेत्र में प्रवेश करें डिग्री(आदेश) स्वतंत्र चर के लिए उच्चतम डिग्री;
• एक प्रकार चुनते समय औसत चलन(मूविंग एवरेज) फ़ील्ड में प्रवेश करें अंक(अवधि) चलती औसत की गणना के लिए उपयोग किए जाने वाले अंकों की संख्या।

चावल। 18.11. एक ट्रेंड लाइन का चयन करना

क्षेत्र के क्षेत्रों के लिए, 200X का डेटा प्रदान किया गया है।

क्षेत्र क्रमांक	एक सक्षम व्यक्ति का प्रति दिन औसत प्रति व्यक्ति निर्वाह वेतन, रगड़, x	औसत दैनिक वेतन, रगड़, वाई
1	78	133
2	82	148
3	87	134
4	79	154
5	89	162
6	106	195
7	67	139
8	88	158
9	73	152
10	87	162
11	76	159
12	115	173

व्यायाम:

1. एक सहसंबंध क्षेत्र का निर्माण करें और कनेक्शन के रूप के बारे में एक परिकल्पना तैयार करें।

2. रैखिक प्रतिगमन समीकरण के मापदंडों की गणना करें

4. औसत (सामान्य) लोच गुणांक का उपयोग करते हुए, कारक और परिणाम के बीच संबंध की ताकत का तुलनात्मक मूल्यांकन दें।

7. यदि कारक का अनुमानित मूल्य उसके औसत स्तर से 10% बढ़ जाता है, तो परिणाम के अनुमानित मूल्य की गणना करें। महत्व स्तर के लिए पूर्वानुमान विश्वास अंतराल निर्धारित करें।

समाधान:

आइए एक्सेल का उपयोग करके इस समस्या को हल करें।

1. उपलब्ध डेटा x और y की तुलना करके, उदाहरण के लिए, उन्हें कारक x के बढ़ते क्रम में रैंकिंग करके, कोई विशेषताओं के बीच सीधे संबंध की उपस्थिति का निरीक्षण कर सकता है, जब औसत प्रति व्यक्ति निर्वाह स्तर में वृद्धि औसत दैनिक बढ़ जाती है वेतन। इसके आधार पर, हम यह धारणा बना सकते हैं कि विशेषताओं के बीच संबंध प्रत्यक्ष है और इसे एक सीधी रेखा समीकरण द्वारा वर्णित किया जा सकता है। चित्रात्मक विश्लेषण के आधार पर भी इसी निष्कर्ष की पुष्टि की जाती है।

सहसंबंध फ़ील्ड बनाने के लिए, आप एक्सेल पीपीपी का उपयोग कर सकते हैं। प्रारंभिक डेटा को क्रम में दर्ज करें: पहले x, फिर y।

उन कक्षों के क्षेत्र का चयन करें जिनमें डेटा है।

उसके बाद चुनो: प्लॉट डालें/स्कैटर करें/मार्कर्स के साथ स्कैटर करेंजैसा कि चित्र एक में दिखाया गया है।

चित्र 1 सहसंबंध क्षेत्र का निर्माण

सहसंबंध क्षेत्र का विश्लेषण निकट-सरलता निर्भरता की उपस्थिति को दर्शाता है, क्योंकि बिंदु लगभग एक सीधी रेखा में स्थित हैं।

2. रेखीय प्रतिगमन समीकरण के मापदंडों की गणना करने के लिए
आइए अंतर्निहित सांख्यिकीय फ़ंक्शन का उपयोग करें लाइनेस्ट.

इसके लिए:

1) विश्लेषित डेटा वाली मौजूदा फ़ाइल खोलें;
2) प्रतिगमन आंकड़ों के परिणाम प्रदर्शित करने के लिए खाली कोशिकाओं (5 पंक्तियाँ, 2 कॉलम) का 5x2 क्षेत्र चुनें।
3) सक्रिय करें फ़ंक्शन विज़ार्ड: मुख्य मेनू में चयन करें सूत्र/सम्मिलित फ़ंक्शन.
4)खिड़की में वर्गतुम ले रहे हो सांख्यिकीय, फ़ंक्शन विंडो में - लाइनेस्ट. बटन को क्लिक करे ठीक हैजैसा कि चित्र 2 में दिखाया गया है;

चित्र 2 फ़ंक्शन विज़ार्ड डायलॉग बॉक्स

5) फ़ंक्शन तर्क भरें:

के लिए ज्ञात मान

x के ज्ञात मान

स्थिर- एक तार्किक मान जो समीकरण में एक मुक्त पद की उपस्थिति या अनुपस्थिति को इंगित करता है; यदि स्थिरांक = 1, तो मुक्त पद की गणना सामान्य तरीके से की जाती है, यदि स्थिरांक = 0, तो मुक्त पद 0 है;

आंकड़े- एक तार्किक मान जो इंगित करता है कि प्रतिगमन विश्लेषण पर अतिरिक्त जानकारी प्रदर्शित की जानी चाहिए या नहीं। यदि सांख्यिकी = 1, तो अतिरिक्त जानकारी प्रदर्शित की जाती है, यदि सांख्यिकी = 0, तो केवल समीकरण मापदंडों के अनुमान प्रदर्शित किए जाते हैं।

बटन को क्लिक करे ठीक है;

चित्र 3 LINEST फ़ंक्शन तर्क संवाद बॉक्स

6) अंतिम तालिका का पहला तत्व चयनित क्षेत्र के ऊपरी बाएँ कक्ष में दिखाई देगा। संपूर्ण तालिका खोलने के लिए, बटन दबाएँ , और फिर कुंजी संयोजन के लिए ++ .

अतिरिक्त प्रतिगमन आँकड़े निम्नलिखित चित्र में दिखाए गए क्रम में आउटपुट होंगे:

गुणांक मान बी	गुणांक एक मान
मानक त्रुटि बी	मानक त्रुटि ए
मानक त्रुटि y
एफ आंकड़ा
वर्गों का प्रतिगमन योग

चित्र 4 LINEST फ़ंक्शन की गणना का परिणाम

हमें प्रतिगमन स्तर मिला:

हम निष्कर्ष निकालते हैं: औसत प्रति व्यक्ति निर्वाह स्तर में 1 रूबल की वृद्धि के साथ। औसत दैनिक वेतन औसतन 0.92 रूबल बढ़ जाता है।

इसका मतलब यह है कि मजदूरी (y) में 52% भिन्नता को कारक x की भिन्नता - औसत प्रति व्यक्ति जीवित मजदूरी, और 48% - मॉडल में शामिल नहीं किए गए अन्य कारकों की कार्रवाई द्वारा समझाया गया है।

निर्धारण के परिकलित गुणांक का उपयोग करके, सहसंबंध गुणांक की गणना की जा सकती है: .

कनेक्शन का मूल्यांकन करीबी के रूप में किया गया है।

4. औसत (सामान्य) लोच गुणांक का उपयोग करके, हम परिणाम पर कारक के प्रभाव की ताकत निर्धारित करते हैं।

एक सीधी रेखा समीकरण के लिए, हम सूत्र का उपयोग करके औसत (कुल) लोच गुणांक निर्धारित करते हैं:

हम x मान वाले कक्षों के क्षेत्र का चयन करके और चयन करके औसत मान ज्ञात करेंगे सूत्र/ऑटोसम/औसत, और हम y के मानों के साथ भी ऐसा ही करेंगे।

चित्र 5 औसत फ़ंक्शन मान और तर्क की गणना

इस प्रकार, यदि औसत प्रति व्यक्ति जीवन यापन की लागत उसके औसत मूल्य से 1% बदलती है, तो औसत दैनिक वेतन औसतन 0.51% बदल जाएगा।

डेटा विश्लेषण उपकरण का उपयोग करना वापसीउपलब्ध:
- प्रतिगमन सांख्यिकी के परिणाम,
- विचरण के विश्लेषण के परिणाम,
- आत्मविश्वास अंतराल के परिणाम,
- अवशिष्ट और प्रतिगमन रेखा फिटिंग ग्राफ़,
- अवशेष और सामान्य संभावना.

प्रक्रिया निम्नलिखित है:

1) तक पहुंच की जाँच करें विश्लेषण पैकेज. मुख्य मेनू में, चुनें: फ़ाइल/विकल्प/ऐड-ऑन.

2) ड्रॉपडाउन सूची में नियंत्रणवस्तु चुनें एक्सेल ऐड-इन्सऔर बटन दबाएँ जाना।

3)खिड़की में ऐड-ऑनबॉक्स को चेक करें विश्लेषण पैकेज, और फिर बटन पर क्लिक करें ठीक है.

अगर विश्लेषण पैकेजफ़ील्ड सूची में नहीं उपलब्ध ऐड-ऑन, बटन दबाएँ समीक्षाएक खोज करने के लिए.

यदि आपको एक संदेश प्राप्त होता है जो दर्शाता है कि विश्लेषण पैकेज आपके कंप्यूटर पर स्थापित नहीं है, तो क्लिक करें हाँइसे स्थापित करने के लिए.

4) मुख्य मेनू में, चुनें: डेटा/डेटा विश्लेषण/विश्लेषण उपकरण/प्रतिगमन, और फिर बटन पर क्लिक करें ठीक है.

5) डेटा इनपुट और आउटपुट पैरामीटर संवाद बॉक्स भरें:

इनपुट अंतराल Y- परिणामी विशेषता का डेटा युक्त श्रेणी;

इनपुट अंतराल एक्स- कारक विशेषता का डेटा युक्त श्रेणी;

टैग- एक ध्वज जो इंगित करता है कि पहली पंक्ति में कॉलम नाम हैं या नहीं;

स्थिरांक - शून्य- समीकरण में एक मुक्त पद की उपस्थिति या अनुपस्थिति को दर्शाने वाला ध्वज;

आउटपुट अंतराल- यह भविष्य की सीमा के ऊपरी बाएँ कक्ष को इंगित करने के लिए पर्याप्त है;

6) नई वर्कशीट - आप नई शीट के लिए एक मनमाना नाम निर्दिष्ट कर सकते हैं।

फिर बटन पर क्लिक करें ठीक है.

चित्र 6 रिग्रेशन टूल के लिए पैरामीटर दर्ज करने के लिए डायलॉग बॉक्स

समस्या डेटा के प्रतिगमन विश्लेषण के परिणाम चित्र 7 में प्रस्तुत किए गए हैं।

चित्र 7 प्रतिगमन उपकरण का उपयोग करने का परिणाम

5. आइए औसत सन्निकटन त्रुटि का उपयोग करके समीकरणों की गुणवत्ता का मूल्यांकन करें। आइए चित्र 8 में प्रस्तुत प्रतिगमन विश्लेषण के परिणामों का उपयोग करें।

चित्र 8 प्रतिगमन उपकरण "शेष की निकासी" का उपयोग करने का परिणाम

आइए एक नई तालिका बनाएं जैसा कि चित्र 9 में दिखाया गया है। कॉलम सी में, हम सूत्र का उपयोग करके सापेक्ष सन्निकटन त्रुटि की गणना करते हैं:

चित्र 9 औसत सन्निकटन त्रुटि की गणना

औसत सन्निकटन त्रुटि की गणना सूत्र का उपयोग करके की जाती है:

निर्मित मॉडल की गुणवत्ता अच्छी आंकी गई है, क्योंकि यह 8-10% से अधिक नहीं है।

6. प्रतिगमन आंकड़ों वाली तालिका (चित्र 4) से हम फिशर के एफ-परीक्षण का वास्तविक मूल्य लिखते हैं:

क्योंकि 5% महत्व स्तर पर, तो हम यह निष्कर्ष निकाल सकते हैं कि प्रतिगमन समीकरण महत्वपूर्ण है (संबंध सिद्ध हो चुका है)।

8. हम छात्र के टी-सांख्यिकी का उपयोग करके और प्रत्येक संकेतक के आत्मविश्वास अंतराल की गणना करके प्रतिगमन मापदंडों के सांख्यिकीय महत्व का आकलन करेंगे।

हमने संकेतकों और शून्य के बीच सांख्यिकीय रूप से महत्वहीन अंतर के बारे में परिकल्पना एच 0 को सामने रखा है:

.

स्वतंत्रता की डिग्री की संख्या के लिए

चित्र 7 में वास्तविक t-सांख्यिकी मान हैं:

सहसंबंध गुणांक के लिए टी-परीक्षण की गणना दो तरीकों से की जा सकती है:

विधि I:

कहाँ - सहसंबंध गुणांक की यादृच्छिक त्रुटि.

हम गणना के लिए चित्र 7 में दी गई तालिका से डेटा लेंगे।

विधि II:

वास्तविक टी-सांख्यिकी मान तालिका मानों से अधिक हैं:

इसलिए, परिकल्पना H 0 को अस्वीकार कर दिया गया है, अर्थात, प्रतिगमन पैरामीटर और सहसंबंध गुणांक संयोग से शून्य से भिन्न नहीं हैं, लेकिन सांख्यिकीय रूप से महत्वपूर्ण हैं।

पैरामीटर a के लिए विश्वास अंतराल को इस प्रकार परिभाषित किया गया है

पैरामीटर ए के लिए, चित्र 7 में दर्शाई गई 95% सीमाएँ थीं:

प्रतिगमन गुणांक के लिए विश्वास अंतराल को इस प्रकार परिभाषित किया गया है

प्रतिगमन गुणांक बी के लिए, चित्र 7 में दर्शाई गई 95% सीमाएँ थीं:

आत्मविश्वास अंतराल की ऊपरी और निचली सीमाओं के विश्लेषण से यह निष्कर्ष निकलता है कि संभाव्यता के साथ पैरामीटर ए और बी, निर्दिष्ट सीमा के भीतर होने के कारण, शून्य मान नहीं लेते हैं, अर्थात। सांख्यिकीय रूप से महत्वहीन नहीं हैं और शून्य से काफी भिन्न हैं।

7. प्रतिगमन समीकरण के प्राप्त अनुमान इसे पूर्वानुमान के लिए उपयोग करने की अनुमति देते हैं। यदि जीवन यापन की अनुमानित लागत है:

तब जीवन यापन की लागत का अनुमानित मूल्य होगा:

हम सूत्र का उपयोग करके पूर्वानुमान त्रुटि की गणना करते हैं:

कहाँ

हम एक्सेल पीपीपी का उपयोग करके विचरण की गणना भी करेंगे। इसके लिए:

1) सक्रिय करें फ़ंक्शन विज़ार्ड: मुख्य मेनू में चयन करें सूत्र/सम्मिलित फ़ंक्शन.

3) कारक विशेषता के संख्यात्मक डेटा वाली श्रेणी भरें। क्लिक ठीक है.

चित्र 10 विचरण की गणना

हमें विचरण मान मिल गया

स्वतंत्रता की प्रति डिग्री अवशिष्ट विचरण की गणना करने के लिए, हम विचरण के विश्लेषण के परिणामों का उपयोग करेंगे जैसा कि चित्र 7 में दिखाया गया है।

0.95 की संभावना के साथ y के व्यक्तिगत मूल्यों की भविष्यवाणी के लिए आत्मविश्वास अंतराल अभिव्यक्ति द्वारा निर्धारित किया जाता है:

अंतराल काफी व्यापक है, मुख्यतः अवलोकनों की छोटी मात्रा के कारण। सामान्य तौर पर, औसत मासिक वेतन का पूर्वानुमान विश्वसनीय निकला।

समस्या की स्थिति यहां से ली गई है: अर्थमिति पर कार्यशाला: प्रोक। भत्ता / आई.आई. एलिसेवा, एस.वी. कुरीशेवा, एन.एम. गोर्डीन्को और अन्य; ईडी। आई.आई. एलिसेवा। - एम.: वित्त और सांख्यिकी, 2003. - 192 पी.: बीमार।