एक्सेल में कम से कम वर्गों की विधि का अनुप्रयोग। एक्सेल में कम से कम वर्ग विधि

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

कम से कम वर्गों की विधि (OLS) एक चर की दूसरे पर निर्भरता का गणितीय विवरण है। इसका उपयोग भविष्यवाणी में किया जा सकता है।

सॉल्वर ऐड-इन को सक्षम करना

एक्सेल में ओएलएस का उपयोग करने के लिए, आपको ऐड-इन सक्षम करने की आवश्यकता है "समाधान खोजें"जो डिफ़ॉल्ट रूप से अक्षम है।

अब समारोह समाधान खोजनाएक्सेल में सक्रिय है, और इसके उपकरण रिबन पर दिखाई दिए हैं।

समस्या की शर्तें

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

फ़ंक्शन इस निर्भरता का सबसे सटीक वर्णन कर सकता है:

इसके अलावा, यह ज्ञात है कि के लिए एक्स = 0 आपभी बराबर है 0 ... इसलिए, इस समीकरण को निर्भरता द्वारा वर्णित किया जा सकता है वाई = एनएक्स .

हमें अंतर के वर्गों का न्यूनतम योग ज्ञात करना है।

समाधान

आइए विधि के प्रत्यक्ष अनुप्रयोग का वर्णन करने के लिए आगे बढ़ें।

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

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

एक विशिष्ट उदाहरण का उपयोग करके समस्या का विवरण

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

तो, एक्स को एक किराने की दुकान का खुदरा स्थान होने दें, जिसे वर्ग मीटर में मापा जाता है, और वाई - वार्षिक कारोबार, लाखों रूबल में मापा जाता है।

यह अनुमान लगाना आवश्यक है कि यदि किसी विशेष खुदरा स्थान के पास स्टोर का टर्नओवर (Y) क्या होगा। जाहिर है, फ़ंक्शन Y = f (X) बढ़ रहा है, क्योंकि हाइपरमार्केट स्टॉल से अधिक माल बेचता है।

भविष्यवाणी के लिए उपयोग किए गए प्रारंभिक डेटा की शुद्धता के बारे में कुछ शब्द

मान लें कि हमारे पास n स्टोर्स के लिए डेटा से निर्मित एक टेबल है।

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

विधि सार

तालिका डेटा को कार्तीय तल पर बिंदुओं M 1 (x 1, y 1),… M n (x n, y n) के रूप में प्रदर्शित किया जा सकता है। अब समस्या का समाधान सन्निकट फलन y = f (x) के चयन तक कम हो जाएगा, जिसमें एक ग्राफ जितना संभव हो सके बिंदुओं M 1, M 2, .. M n के करीब से गुजर रहा है।

बेशक, आप एक उच्च डिग्री बहुपद का उपयोग कर सकते हैं, लेकिन यह विकल्प न केवल लागू करना मुश्किल है, बल्कि यह भी गलत है, क्योंकि यह उस मुख्य प्रवृत्ति को प्रतिबिंबित नहीं करेगा जिसका पता लगाने की आवश्यकता है। सबसे उचित समाधान सीधी रेखा y = ax + b को खोजना है, जो प्रयोगात्मक डेटा का सबसे अच्छा अनुमान लगाता है, या इसके बजाय, गुणांक - ए और बी।

शुद्धता मूल्यांकन

किसी भी सन्निकटन के लिए, उसकी सटीकता का आकलन विशेष महत्व रखता है। आइए हम बिंदु x i के लिए कार्यात्मक और प्रायोगिक मूल्यों के बीच अंतर (विचलन) को e i द्वारा निरूपित करें, अर्थात e i = y i - f (x i)।

जाहिर है, सन्निकटन की सटीकता का अनुमान लगाने के लिए, विचलन के योग का उपयोग किया जा सकता है, अर्थात, Y पर X की निर्भरता के अनुमानित प्रतिनिधित्व के लिए एक सीधी रेखा का चयन करते समय, किसी को सबसे छोटे मान वाले को वरीयता देनी चाहिए सभी बिंदुओं पर विचाराधीन योग। हालांकि, सब कुछ इतना सरल नहीं है, क्योंकि सकारात्मक विचलन के साथ, नकारात्मक विचलन व्यावहारिक रूप से मौजूद होंगे।

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

कम से कम वर्ग विधि

एक्सेल में, जैसा कि आप जानते हैं, एक अंतर्निहित ऑटोसम फ़ंक्शन है जो आपको चयनित श्रेणी में स्थित सभी मानों के मूल्यों की गणना करने की अनुमति देता है। इस प्रकार, हमें व्यंजक के मान की गणना करने से कोई नहीं रोकता है (e 1 2 + e 2 2 + e 3 2 + ... e n 2)।

गणितीय संकेतन में, ऐसा लगता है:

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

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

इसके लिए नए चर ए और बी के संबंध में आंशिक डेरिवेटिव को शून्य करने की आवश्यकता है, और एक आदिम प्रणाली को हल करना जिसमें दो अज्ञात फॉर्म के साथ दो समीकरण शामिल हैं:

कुछ सरल परिवर्तनों के बाद, जिसमें 2 से भाग देना और योगों में हेर-फेर करना शामिल है, हम प्राप्त करते हैं:

इसे हल करना, उदाहरण के लिए, क्रैमर की विधि द्वारा, हम कुछ गुणांक a * और b * के साथ एक स्थिर बिंदु प्राप्त करते हैं। यह न्यूनतम है, अर्थात, यह अनुमान लगाने के लिए कि किसी निश्चित क्षेत्र के लिए स्टोर का टर्नओवर क्या होगा, सीधी रेखा y = a * x + b * उपयुक्त है, जो प्रश्न में उदाहरण के लिए एक प्रतिगमन मॉडल है। बेशक, यह आपको सटीक परिणाम खोजने की अनुमति नहीं देगा, लेकिन इससे आपको यह अंदाजा लगाने में मदद मिलेगी कि क्या किसी विशेष क्षेत्र के स्टोर के लिए क्रेडिट पर खरीदारी का भुगतान होगा।

एक्सेल में कम से कम वर्ग विधि कैसे लागू करें

एक्सेल में ओएलएस मान की गणना के लिए एक फ़ंक्शन है। इसका निम्न रूप है: "TREND" (ज्ञात Y मान; ज्ञात X मान; नए X मान; स्थिरांक।)। आइए एक्सेल में ओएलएस की गणना के लिए सूत्र को हमारी तालिका में लागू करें।

ऐसा करने के लिए, उस सेल में जिसमें एक्सेल में कम से कम वर्ग विधि द्वारा गणना का परिणाम प्रदर्शित किया जाना चाहिए, "=" चिह्न दर्ज करें और "ट्रेंड" फ़ंक्शन का चयन करें। खुलने वाली विंडो में, हाइलाइट करते हुए उपयुक्त फ़ील्ड भरें:

• वाई के लिए ज्ञात मूल्यों की सीमा (इस मामले में, टर्नओवर के लिए डेटा);
• रेंज x 1,… x n, यानी रिटेल स्पेस का आकार;
• x के ज्ञात और अज्ञात दोनों मान, जिसके लिए आपको टर्नओवर के आकार का पता लगाना होगा (वर्कशीट पर उनके स्थान की जानकारी के लिए नीचे देखें)।

इसके अलावा, सूत्र में बूलियन चर "कॉन्स्ट" होता है। यदि आप संबंधित क्षेत्र में 1 दर्ज करते हैं, तो इसका मतलब यह होगा कि गणना की जानी चाहिए, यह मानते हुए कि बी = 0।

यदि आपको x के एक से अधिक मान के लिए पूर्वानुमान जानने की आवश्यकता है, तो सूत्र दर्ज करने के बाद, आपको "एंटर" नहीं दबाना चाहिए, लेकिन आपको कीबोर्ड पर "Shift" + "Control" + "Enter" संयोजन टाइप करना होगा। ("प्रवेश करना")।

कुछ सुविधाएं

प्रतिगमन विश्लेषण डमी के लिए भी उपलब्ध हो सकता है। अज्ञात चरों की एक सरणी के मूल्य की भविष्यवाणी करने के लिए एक्सेल फॉर्मूला - "ट्रेंड" - का उपयोग उन लोगों द्वारा भी किया जा सकता है जिन्होंने कम से कम वर्गों की विधि के बारे में कभी नहीं सुना है। उसके काम की कुछ विशेषताओं को जानने के लिए बस इतना ही काफी है। विशेष रूप से:

• यदि आप y चर के ज्ञात मानों की श्रेणी को एक पंक्ति या स्तंभ में व्यवस्थित करते हैं, तो ज्ञात x मानों वाली प्रत्येक पंक्ति (स्तंभ) को प्रोग्राम द्वारा एक अलग चर के रूप में माना जाएगा।
• यदि "ट्रेंड" विंडो में ज्ञात x के साथ एक श्रेणी नहीं है, तो यदि फ़ंक्शन का उपयोग एक्सेल में किया जाता है, तो प्रोग्राम इसे पूर्णांकों से युक्त एक सरणी के रूप में मानेगा, जिसकी संख्या दिए गए मानों के साथ सीमा से मेल खाती है। वाई चर के।
• आउटपुट के रूप में "अनुमानित" मानों की एक सरणी प्राप्त करने के लिए, प्रवृत्ति अभिव्यक्ति को एक सरणी सूत्र के रूप में दर्ज किया जाना चाहिए।
• यदि नए x मान निर्दिष्ट नहीं हैं, तो TREND फ़ंक्शन उन्हें ज्ञात के बराबर मानता है। यदि वे निर्दिष्ट नहीं हैं, तो सरणी 1 को तर्क के रूप में लिया जाता है; 2; 3; 4;…, जो पहले से दिए गए पैरामीटर y के साथ सीमा के अनुरूप है।
• नए x-मानों वाली श्रेणी में दिए गए y-मानों वाली श्रेणी के समान या अधिक पंक्तियाँ या स्तंभ होने चाहिए। दूसरे शब्दों में, यह स्वतंत्र चर के अनुरूप होना चाहिए।
• ज्ञात x मानों वाली एक सरणी में कई चर हो सकते हैं। हालांकि, अगर हम केवल एक के बारे में बात कर रहे हैं, तो यह आवश्यक है कि x और y के दिए गए मानों वाली श्रेणियां अनुरूप हों। एकाधिक चर के मामले में, आप चाहते हैं कि दिए गए y मानों वाली श्रेणी एक कॉलम या एक पंक्ति में फ़िट हो।

पूर्वानुमान समारोह

एक्सेल में रिग्रेशन विश्लेषण कई कार्यों का उपयोग करके कार्यान्वित किया जाता है। उनमें से एक को "पूर्वानुमान" कहा जाता है। यह "TREND" के समान है, अर्थात यह कम से कम वर्ग विधि का उपयोग करके गणना का परिणाम देता है। हालाँकि, केवल एक X के लिए, जिसके लिए Y मान अज्ञात है।

अब आप एक्सेल में डमी के लिए सूत्रों को जानते हैं जो आपको एक रैखिक प्रवृत्ति के अनुसार किसी दिए गए संकेतक के भविष्य के मूल्य की भविष्यवाणी करने की अनुमति देते हैं।

4.1. अंतर्निहित कार्यों का उपयोग करना

हिसाब प्रतिगमन गुणांकफ़ंक्शन का उपयोग करके किया गया

लाइनेस्ट(Values_y; एक्स-मूल्यों; कॉन्स्टो; आंकड़े),

Values_y- y मानों की एक सरणी,

एक्स-मूल्यों- मूल्यों की वैकल्पिक सरणी एक्सअगर सरणी एन एसछोड़ दिया जाता है, तो यह माना जाता है कि यह एक सरणी (1; 2; 3; ...) के समान आकार है Values_y,

कॉन्स्टो- एक बूलियन मान जो इंगित करता है कि क्या स्थिरांक की आवश्यकता है बी 0 के बराबर था। अगर कॉन्स्टोअर्थ है सचया छोड़ा गया, तो बीसामान्य तरीके से गणना की जाती है। यदि तर्क कॉन्स्टो FALSE है, तो बी 0 और मानों के बराबर सेट किया गया है एइसलिए चुना जाता है कि संबंध वाई = कुल्हाड़ी।

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

यह याद रखना चाहिए कि कार्यों का परिणाम लाइनस्ट ()मूल्यों का एक सेट है - एक सरणी।

गणना के लिए सहसंबंध गुणांकफ़ंक्शन का उपयोग किया जाता है

कोरेली(सरणी1;Array2),

सहसंबंध गुणांक का मान लौटाना, जहाँ सरणी1- मूल्यों की सरणी आप, Array2- मूल्यों की सरणी एक्स. सरणी1तथा Array2एक ही आयाम का होना चाहिए।

उदाहरण 1... लत आप(एक्स) तालिका में प्रस्तुत किया गया है। निर्माण प्रतिगमन लाइनऔर गणना करें सहसंबंध गुणांक.

आप		0.5		1.5		2.5		3.5
एक्स		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

आइए एक एमएस एक्सेल शीट में मूल्यों की एक तालिका दर्ज करें और एक स्कैटर प्लॉट बनाएं। वर्कशीट अंजीर में दिखाया गया फॉर्म लेगा। 2.

प्रतिगमन गुणांक के मूल्यों की गणना करने के लिए एतथा बीकोशिकाओं को आवंटित करें ए7: बी7,फ़ंक्शन विज़ार्ड और श्रेणी में मुड़ें सांख्यिकीयसमारोह चुनें लाइनेस्ट... जैसा कि चित्र में दिखाया गया है, दिखाई देने वाले डायलॉग बॉक्स को भरें। 3 और दबाएं ठीक.

परिणामस्वरूप, परिकलित मान केवल सेल में दिखाई देगा ए6(अंजीर। 4)। सेल में मान प्रदर्शित होने के लिए बी -6संपादन मोड में प्रवेश करना आवश्यक है (कुंजी F2)और फिर कुंजी संयोजन दबाएं CTRL + SHIFT + ENTER.

प्रति सेल सहसंबंध गुणांक के मान की गणना करने के लिए सी 6निम्नलिखित सूत्र पेश किया गया था:

C7 = CORREL (B3: J3; B2: J2).

प्रतिगमन गुणांक जानना एतथा बीफ़ंक्शन के मानों की गणना करें आप=कुल्हाड़ी+बीमाफ़ कर दिया एक्स... ऐसा करने के लिए, हम सूत्र का परिचय देते हैं

बी5 = $ ए $ 7 ​​* बी 2 + $ बी $ 7

और इसे सीमा में कॉपी करें C5: J5(अंजीर। 5)।

आइए आरेख पर प्रतिगमन रेखा खींचते हैं। ग्राफ़ पर प्रयोगात्मक बिंदुओं का चयन करें, राइट-क्लिक करें और कमांड का चयन करें प्रारंभिक आंकड़े... दिखाई देने वाले डायलॉग बॉक्स में (चित्र 5), टैब चुनें पंक्तिऔर बटन पर क्लिक करें जोड़ें... इनपुट फ़ील्ड भरें, जैसा कि अंजीर में दिखाया गया है। 6 और बटन दबाएं ठीक है... प्रायोगिक डेटा प्लॉट में एक प्रतिगमन रेखा जोड़ी जाती है। डिफ़ॉल्ट रूप से, इसका ग्राफ उन बिंदुओं के रूप में प्रदर्शित किया जाएगा जो चौरसाई लाइनों से जुड़े नहीं हैं।

चावल। 6

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

आप लाइन के प्रकार, रंग और मोटाई को निम्नानुसार बदल सकते हैं। आरेख में एक पंक्ति का चयन करें, राइट-क्लिक करें और कमांड का चयन करें डेटा शृंखला प्रारूप...अगला, सेटिंग्स बनाएं, उदाहरण के लिए, जैसा कि अंजीर में दिखाया गया है। आठ।

सभी परिवर्तनों के परिणामस्वरूप, हम एक ग्राफिकल क्षेत्र (चित्र 9) में प्रयोगात्मक डेटा और एक प्रतिगमन रेखा का एक ग्राफ प्राप्त करेंगे।

4.2. एक ट्रेंड लाइन का उपयोग करना।

एमएस एक्सेल में विभिन्न अनुमानित निर्भरताओं का निर्माण चार्ट संपत्ति के रूप में कार्यान्वित किया जाता है - प्रवृत्ति रेखा.

उदाहरण 2... प्रयोग के परिणामस्वरूप, कुछ सारणीबद्ध निर्भरता निर्धारित की गई थी।

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

एक अनुमानित निर्भरता का चयन करें और उसका निर्माण करें। सारणीबद्ध और चयनित विश्लेषणात्मक निर्भरता के रेखांकन बनाएँ।

समस्या के समाधान को निम्नलिखित चरणों में विभाजित किया जा सकता है: प्रारंभिक डेटा इनपुट करना, एक डॉट प्लॉट बनाना और इस प्लॉट में एक ट्रेंड लाइन जोड़ना।

आइए इस प्रक्रिया पर विस्तार से विचार करें। आइए कच्चे डेटा को वर्कशीट में दर्ज करें और प्रयोगात्मक डेटा को प्लॉट करें। इसके बाद, ग्राफ़ पर प्रयोगात्मक बिंदुओं का चयन करें, राइट-क्लिक करें और कमांड का उपयोग करें जोड़ेंमैं प्रवृत्ति पहल(अंजीर। 10)।

प्रकट होने वाला संवाद बॉक्स आपको एक अनुमानित निर्भरता बनाने की अनुमति देता है।

इस विंडो का पहला टैब (चित्र 11) अनुमानित निर्भरता के प्रकार को दर्शाता है।

दूसरे (छवि 12) पर, निर्माण पैरामीटर निर्धारित किए जाते हैं:

· अनुमानित निर्भरता का नाम;

पूर्वानुमान आगे (पिछड़ा) द्वारा एनइकाइयाँ (यह पैरामीटर निर्धारित करता है कि प्रवृत्ति रेखा का विस्तार करने के लिए कितनी इकाइयाँ आगे (पीछे) आवश्यक हैं);

क्या एक सीधी रेखा वाले वक्र के प्रतिच्छेदन बिंदु को दिखाना है वाई = कॉन्स्ट;

· आरेख पर सन्निकटन फलन दिखाएं या नहीं (आरेख पर समीकरण दिखाने का विकल्प);

· मानक विचलन का मान आरेख पर रखना है या नहीं (आरेख पर पैरामीटर को सन्निकटन विश्वसनीयता का मान रखें)।

आइए हम दूसरी डिग्री के एक बहुपद को एक सन्निकटन निर्भरता के रूप में चुनें (चित्र 11) और ग्राफ पर इस बहुपद का वर्णन करने वाले समीकरण को प्राप्त करें (चित्र 12)। परिणामी आरेख अंजीर में दिखाया गया है। 13.

इसी तरह, का उपयोग करना प्रवृत्ति रेखाएंआप इस तरह की निर्भरता के मापदंडों को चुन सकते हैं:

रैखिक आप=एक एक्स+बी,

लघुगणक आप=एक ln(एक्स)+बी,

घातीय आप=एक ई बी,

शक्ति नियम आप=एक एक्स बी,

बहुपद आप=एक एक्स 2 +बी एक्स+सी, आप=एक एक्स 3 +बी एक्स 2 +सी ∙ एक्स + डीऔर इसी तरह, 6वीं डिग्री के बहुपद तक, समावेशी,

· रैखिक निस्पंदन।

4.3. निर्णय ब्लॉक का उपयोग करना

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

उदाहरण 3... प्रयोग के परिणामस्वरूप, निर्भरता z (t) प्राप्त की गई, जिसे तालिका में प्रस्तुत किया गया है

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

निर्भरता गुणांक चुनें जेड (टी) = 4 + बीटी 3 + सीटी 2 + डीटी + के . परन्यूनतम वर्ग विधि।

यह समस्या पाँच चरों के एक फलन का न्यूनतम ज्ञात करने की समस्या के तुल्य है

आइए अनुकूलन समस्या को हल करने की प्रक्रिया पर विचार करें (चित्र 14)।

मान दें ए, वी, साथ, डीतथा प्रतिकोशिकाओं में संग्रहित ए7: ई7... आइए फ़ंक्शन के सैद्धांतिक मूल्यों की गणना करें जेड(टी)=4 + बीटी 3 + सीटी 2 + डीटी + के . परमाफ़ कर दिया टी(बी2: जे2). ऐसा करने के लिए, सेल में बी 4पहले बिंदु पर फ़ंक्शन का मान दर्ज करें (सेल .) बी2):

बी 4 = $ ए $ 7 ​​* बी 2 ^ 4 + $ बी $ 7 * बी 2 ^ 3 + $ सी $ 7 * बी 2 ^ 2 + $ डी $ 7 * बी 2 + $ ई $ 7.

आइए इस सूत्र को श्रेणी में कॉपी करें C4: J4और उन बिंदुओं पर फ़ंक्शन का अपेक्षित मान प्राप्त करें, जिनमें से एब्सिसास कोशिकाओं में जमा हो जाते हैं बी2: जे2.

सेल में बी5हम एक सूत्र पेश करते हैं जो प्रयोगात्मक और परिकलित बिंदुओं के बीच अंतर के वर्ग की गणना करता है:

बी5 = (बी4-बी3) ^ 2,

और इसे सीमा में कॉपी करें C5: J5... एक सेल में F7हम कुल चुकता त्रुटि (10) संग्रहीत करेंगे। ऐसा करने के लिए, हम सूत्र पेश करते हैं:

F7 = योग (B5: J5).

आइए कमांड का उपयोग करें Service®समाधान के लिए खोजेंऔर बिना किसी प्रतिबंध के अनुकूलन समस्या को हल करें। अंजीर में दिखाए गए डायलॉग बॉक्स में इनपुट फ़ील्ड भरें। 14 और बटन दबाएं निष्पादित करना... यदि कोई समाधान मिल जाता है, तो अंजीर में दिखाई गई खिड़की। 15.

निर्णय ब्लॉक के काम का परिणाम कोशिकाओं को आउटपुट होगा ए7: ई7पैरामीटर मानकार्यों जेड(टी)=4 + बीटी 3 + सीटी 2 + डीटी + के . पर... कोशिकाओं में बी4: जे4पाना अपेक्षित फ़ंक्शन मानशुरुआती बिंदुओं पर। एक सेल में F7रखा जाएगा कुल चुकता त्रुटि.

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

चावल। 17 गणनाओं के बाद एमएस एक्सेल वर्कशीट प्रदर्शित करता है।

5. संदर्भ

1. अलेक्सेव ईआर, चेसनोकोवा ओवी, संकुल गणित की समस्याओं का समाधान Mathcad12, MATLAB7, Maple9। - एनटी प्रेस, 2006. - 596 एस। : बीमार। - (ट्यूटोरियल)

2. अलेक्सेव ईआर, चेसनोकोवा ओ.वी., ई.ए. रुडचेंको, साइलैब, इंजीनियरिंग और गणितीय समस्याओं को हल करना। -एम।, बिनोम, 2008। - 260s।

3. बेरेज़िन आईएस, झिडकोव एनपी, गणना के तरीके।-मास्को: नौका, 1966.-632p।

4. गर्नेव ए.यू., अर्थशास्त्र और वित्त में एमएस एक्सेल और वीबीए का उपयोग करना। - एसपीबी।: बीएचवी - पीटर्सबर्ग, 1999। - 332 एस।

5. डेमिडोविच बीपी, मैरोन आई ए, शुवालोवा वीजेड, विश्लेषण के संख्यात्मक तरीके। -एम .: नौका, 1967। - 368 पी।

6. कॉर्न जी., कॉर्न टी., हैंडबुक ऑफ मैथमेटिक्स फॉर साइंटिस्ट्स एंड इंजीनियर्स-एम., 1970, 720पी।

7. अलेक्सेव ईआर, चेसनोकोवा ओ.वी. एमएस एक्सेल में प्रयोगशाला के काम के लिए पद्धतिगत निर्देश। सभी विशिष्टताओं के छात्रों के लिए। डोनेट्स्क, डोनटु, 2004.112 पी.

खैर, काम पर उन्होंने निरीक्षण की सूचना दी, लेख सम्मेलन के लिए घर पर लिखा गया था - अब आप ब्लॉग पर लिख सकते हैं। जब मैं अपना डेटा संसाधित कर रहा था, मुझे एहसास हुआ कि मैं मदद नहीं कर सकता लेकिन एक्सेल में एक बहुत ही शांत और आवश्यक ऐड-इन के बारे में लिख सकता हूं, जिसे कहा जाता है। तो लेख इस विशेष ऐड-इन के लिए समर्पित होगा, और मैं आपको इसके बारे में उपयोग के एक उदाहरण का उपयोग करके बताऊंगा कम से कम वर्ग विधि(OLS) प्रयोगात्मक डेटा का वर्णन करते समय अज्ञात समीकरण गुणांक खोजने के लिए।

फाइंड सॉल्यूशन ऐड-इन कैसे इनेबल करें

सबसे पहले, आइए जानें कि इस ऐड-इन को कैसे सक्षम किया जाए।

1. "फ़ाइल" मेनू पर जाएं और "एक्सेल विकल्प" चुनें

2. दिखाई देने वाली विंडो में, "समाधान खोजें" चुनें और "जाओ" पर क्लिक करें।

3. अगली विंडो में, "समाधान की खोज करें" आइटम के सामने एक टिक लगाएं और "ओके" पर क्लिक करें।

4. ऐड-ऑन सक्रिय है - अब इसे "डेटा" मेनू आइटम में पाया जा सकता है।

कम से कम वर्ग विधि

अब संक्षेप में कम से कम वर्ग विधि (OLS) और जहां इसे लागू किया जा सकता है।

मान लें कि हमारे पास कुछ प्रयोग करने के बाद एक डेटासेट है जहां हमने Y मान पर X मान के प्रभाव का अध्ययन किया है।

हम इस प्रभाव का गणितीय रूप से वर्णन करना चाहते हैं, ताकि बाद में हम इस सूत्र का उपयोग कर सकें और जान सकें कि यदि हम X के मान को इतना बदल दें, तो हमें Y का मान मिलता है।

मैं एक सुपर-सरल उदाहरण लूंगा (अंजीर देखें।)

यह स्पष्ट है कि बिंदु एक के बाद एक स्थित हैं जैसे कि एक सीधी रेखा में, और इसलिए हम सुरक्षित रूप से मान लेते हैं कि हमारी निर्भरता एक रैखिक फ़ंक्शन y = kx + b द्वारा वर्णित है। साथ ही, हम निश्चित रूप से सुनिश्चित हैं कि जब एक्स शून्य के बराबर होता है, तो वाई का मान भी शून्य के बराबर होता है। इसका मतलब है कि निर्भरता का वर्णन करने वाला कार्य और भी सरल होगा: y = kx (स्कूल पाठ्यक्रम याद रखें)।

सामान्य तौर पर, हमें गुणांक k ज्ञात करना होता है। यही हम साथ करेंगे ओएलएस "समाधान की खोज" ऐड-इन का उपयोग करना।

विधि इस तथ्य में निहित है कि (यहां - ध्यान: आपको इसके बारे में सोचने की ज़रूरत है) प्रयोगात्मक रूप से प्राप्त और संबंधित गणना मूल्यों के बीच अंतर के वर्गों का योग न्यूनतम था। अर्थात्, जब X1 = 1 वास्तव में मापा गया मान Y1 = 4.6, और परिकलित y1 = f (x1) 4 है, तो अंतर का वर्ग होगा (y1-Y1) ^ 2 = (4-4.6) ^ 2 = 0.36 ... निम्नलिखित के साथ: जब X2 = 2, वास्तव में मापा गया मान Y2 = 8.1, और परिकलित y2 8 है, तो अंतर का वर्ग होगा (y2-Y2) ^ 2 = (8-8.1) ^ 2 = 0.01 . और इन सभी वर्गों का योग यथासंभव छोटा होना चाहिए।

तो, आइए ओएलएस के उपयोग पर प्रशिक्षण शुरू करें और समाधान खोजें एक्सेल ऐड-इन्स .

समाधान खोज ऐड-इन लागू करना

1. यदि आपने "समाधान की खोज" ऐड-ऑन चालू नहीं किया है, तो बिंदु पर वापस जाएं समाधान ऐड-इन के लिए खोज को कैसे सक्षम करें और सक्षम करें 🙂

2. सेल A1 में, "1" मान दर्ज करें। यह इकाई हमारी कार्यात्मक निर्भरता y = kx के गुणांक (k) के वास्तविक मान का पहला सन्निकटन होगी।

3. कॉलम बी में, हमारे पास एक्स पैरामीटर के मान हैं, कॉलम सी में - वाई पैरामीटर के मान। डी कॉलम की कोशिकाओं में, हम सूत्र दर्ज करते हैं: "गुणांक के मूल्य से गुणा किया जाता है एक्स" का। उदाहरण के लिए, सेल D1 में हम "= A1 * B1" दर्ज करते हैं, सेल D2 में हम "= A1 * B2" दर्ज करते हैं, और इसी तरह।

4. हम मानते हैं कि गुणांक k एक के बराबर है और फलन f (x) = y = 1 * x हमारे समाधान का पहला सन्निकटन है। हम Y के मापा मूल्यों और सूत्र y = 1 * x द्वारा गणना किए गए अंतरों के वर्गों के योग की गणना कर सकते हैं। हम यह सब मैन्युअल रूप से सूत्र में उपयुक्त सेल संदर्भों में चलाकर कर सकते हैं: "= (D2-C2) ^ 2 + (D3-C3) ^ 2 + (D4-C4) ^ 2 ... आदि। अंत में हम हैं गलत है और समझते हैं कि हमने बहुत समय खो दिया है। एक्सेल में, मतभेदों के वर्गों के योग की गणना के लिए, एक विशेष सूत्र है, "SUMKVRAZN", जो हमारे लिए सब कुछ करेगा। इसे सेल A2 में दर्ज करें और प्रारंभिक सेट करें डेटा: मापा मूल्यों की सीमा वाई (कॉलम सी) और परिकलित वाई मूल्यों की सीमा (कॉलम डी)।

4. वर्गों के अंतर के योग की गणना की गई है - अब हम "डेटा" टैब पर जाते हैं और "समाधान के लिए खोजें" का चयन करते हैं।

5. दिखाई देने वाले मेनू में, सेल A1 (गुणांक k वाला एक) को सेल के रूप में परिवर्तित करने के लिए चुनें।

6. लक्ष्य के रूप में सेल A2 का चयन करें और "न्यूनतम मान के बराबर सेट करें" शर्त सेट करें। याद रखें कि यह वह सेल है जहां हम गणना और मापा मूल्यों के बीच अंतर के वर्गों के योग की गणना करते हैं, और यह योग न्यूनतम होना चाहिए। "निष्पादित करें" पर क्लिक करें।

7. गुणांक k का चयन किया जाता है। अब आप सत्यापित कर सकते हैं कि परिकलित मान अब मापे गए मान के बहुत करीब हैं।

पी.एस.

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

समाधान खोजें ऐड-इनअन्य कार्यों के लिए इस्तेमाल किया जा सकता है, मुख्य बात सार को समझना है: एक सेल है जहां हम एक मूल्य का चयन करते हैं, और एक लक्ष्य सेल है जिसमें एक अज्ञात पैरामीटर का चयन करने के लिए एक शर्त निर्धारित की जाती है।
बस इतना ही! अगले लेख में मैं आपको छुट्टी के बारे में एक परी कथा बताऊंगा, ताकि लेख को याद न करने के लिए,