न्यूनतम वर्ग विधि और एक्सेल में समाधान ढूँढना। एक्सेल में न्यूनतम वर्ग विधि। एक्सेल पावर फ़ंक्शन में प्रतिगमन विश्लेषण न्यूनतम वर्ग विधि

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

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

मान लीजिए कि दो संकेतक हैं विशिष्ट समस्या.

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

यदि स्टोर में यह या वह खुदरा स्थान है तो उसका टर्नओवर (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 की खोज करना है, जो प्रयोगात्मक डेटा, या अधिक सटीक रूप से, गुणांक a और b का सबसे अच्छा अनुमान लगाता है।

सटीकता मूल्यांकन

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

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

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

न्यूनतम वर्ग विधि

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

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

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

इस प्रकार, उस सीधी रेखा को खोजने का कार्य जो मात्राओं

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

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

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

एक्सेल में न्यूनतम वर्ग कैसे लागू करें

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

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

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

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

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

कुछ सुविधाएं

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

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

भविष्यवाणी समारोह

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

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

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

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

मान लीजिए कि दो संकेतक हैं विशिष्ट समस्या.

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

यदि स्टोर में यह या वह खुदरा स्थान है तो उसका टर्नओवर (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 की खोज करना है, जो प्रयोगात्मक डेटा, या अधिक सटीक रूप से, गुणांक a और b का सबसे अच्छा अनुमान लगाता है।

सटीकता मूल्यांकन

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

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

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

न्यूनतम वर्ग विधि

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

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

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

इस प्रकार, उस सीधी रेखा को खोजने का कार्य जो मात्राओं

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

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

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

एक्सेल में न्यूनतम वर्ग कैसे लागू करें

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

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

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

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

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

कुछ सुविधाएं

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

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

भविष्यवाणी समारोह

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

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

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

"समाधान खोजें" ऐड-ऑन कैसे सक्षम करें

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

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

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

3. अगली विंडो में, "समाधान खोजें" के बगल में स्थित बॉक्स को चेक करें और "ओके" पर क्लिक करें।

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

न्यूनतम वर्ग विधि

अब संक्षेप में इसके बारे में न्यूनतम वर्ग विधि (एलएसएम) और इसका उपयोग कहां किया जा सकता है.

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

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

मैं एक अत्यंत सरल उदाहरण लूँगा (चित्र देखें)।

इसमें कोई संदेह नहीं है कि बिंदु एक के बाद एक स्थित हैं जैसे कि एक सीधी रेखा में, और इसलिए हम सुरक्षित रूप से मानते हैं कि हमारी निर्भरता एक रैखिक फ़ंक्शन y=kx+b द्वारा वर्णित है। साथ ही, हमें पूरा यकीन है कि जब X शून्य के बराबर है, तो Y का मान भी शून्य के बराबर है। इसका मतलब यह है कि निर्भरता का वर्णन करने वाला फ़ंक्शन और भी सरल होगा: 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. कॉलम बी में हमारे पास पैरामीटर X के मान हैं, कॉलम C में हमारे पास पैरामीटर Y के मान हैं। कॉलम D की कोशिकाओं में हम सूत्र दर्ज करते हैं: "गुणांक k को मान X से गुणा किया जाता है। ” उदाहरण के लिए, सेल 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... आदि। अंत में हम गलती करें और महसूस करें कि हमने बहुत समय बर्बाद किया है। एक्सेल में, वर्ग अंतर के योग की गणना करने के लिए, एक विशेष सूत्र है, "SUMQUARRENT", जो हमारे लिए सब कुछ करेगा। इसे सेल A2 में दर्ज करें और सेट करें प्रारंभिक डेटा: मापे गए मानों की सीमा Y (कॉलम C) और परिकलित Y मानों की सीमा (कॉलम D)।

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

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

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

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

पी.एस.

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

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

न्यूनतम वर्ग विधि (एलएस) अध्ययन के तहत डेटा से चयनित फ़ंक्शन के वर्ग विचलन के योग को कम करने पर आधारित है। इस लेख में हम एक रैखिक फ़ंक्शन का उपयोग करके उपलब्ध डेटा का अनुमान लगाएंगेय = ए एक्स + बी .

न्यूनतम वर्ग विधि(अंग्रेज़ी) साधारण कम से कम वर्गों , ओ.एल.एस.) अज्ञात मापदंडों के आकलन के संदर्भ में प्रतिगमन विश्लेषण के बुनियादी तरीकों में से एक है प्रतिगमन मॉडलनमूना डेटा के अनुसार.

आइए उन कार्यों द्वारा सन्निकटन पर विचार करें जो केवल एक चर पर निर्भर करते हैं:

• रैखिक: y=ax+b (यह आलेख)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+с
• : y=ax 2 +bx+c

टिप्पणी: इस लेख में तीसरी से छठी डिग्री तक एक बहुपद द्वारा सन्निकटन के मामलों पर विचार किया गया है। यहां त्रिकोणमितीय बहुपद द्वारा सन्निकटन पर विचार किया गया है।

रैखिक निर्भरता

हम दो चरों के बीच संबंध में रुचि रखते हैं एक्सऔर य. ऐसी धारणा है कि यपर निर्भर करता है एक्सरैखिक कानून के अनुसार य = कुल्हाड़ी + बी. इस संबंध के मापदंडों को निर्धारित करने के लिए, शोधकर्ता ने अवलोकन किया: x i के प्रत्येक मान के लिए, y i का माप बनाया गया था (उदाहरण फ़ाइल देखें)। तदनुसार, मान लीजिए कि मानों के 20 जोड़े हैं (x i; y i)।

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

आरेख से यह स्पष्ट है कि चरों के बीच संबंध रैखिक के करीब है। यह समझने के लिए कि कई सीधी रेखाओं में से कौन सी रेखाएं चर के बीच संबंध का सबसे "सही ढंग से" वर्णन करती हैं, उस मानदंड को निर्धारित करना आवश्यक है जिसके द्वारा रेखाओं की तुलना की जाएगी।

ऐसे मानदंड के रूप में हम अभिव्यक्ति का उपयोग करते हैं:

कहाँ ŷ मैं = ए * एक्स मैं + बी ; n - मानों के जोड़े की संख्या (हमारे मामले में n=20)

उपरोक्त अभिव्यक्ति y i और ŷ i के देखे गए मानों के बीच वर्ग दूरी का योग है और इसे अक्सर SSE के रूप में दर्शाया जाता है ( जोड़ का Squared त्रुटियाँ (बच गया), चुकता त्रुटियों का योग (अवशेष)) .

न्यूनतम वर्ग विधिऐसी लाइन का चयन करना है ŷ = कुल्हाड़ी + बी, जिसके लिए उपरोक्त अभिव्यक्ति न्यूनतम मान लेती है।

टिप्पणी:द्वि-आयामी अंतरिक्ष में कोई भी रेखा 2 मापदंडों के मानों द्वारा विशिष्ट रूप से निर्धारित होती है: ए (ढलान) और बी (बदलाव)।

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

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

जैसा कि सूत्र से देखा जा सकता है, पैरामीटर ए सहप्रसरण के अनुपात का प्रतिनिधित्व करता है और, इसलिए पैरामीटर की गणना करने के लिए MS EXCEL में ए आप निम्नलिखित सूत्रों का उपयोग कर सकते हैं (देखें रैखिक शीट उदाहरण फ़ाइल):

= कोवर(बी26:बी45;सी26:सी45)/डीआईएसपी.जी(बी26:बी45)या

= सहप्रसरण.बी(बी26:बी45;सी26:सी45)/डीआईएसपी.बी(बी26:बी45)

पैरामीटर की गणना करने के लिए भी ए आप सूत्र का उपयोग कर सकते हैं = झुकाव(सी26:सी45;बी26:बी45). पैरामीटर के लिए बी सूत्र का प्रयोग करें = लेग(सी26:सी45;बी26:बी45) .

अंत में, LINEST() फ़ंक्शन आपको एक साथ दोनों मापदंडों की गणना करने की अनुमति देता है। एक सूत्र दर्ज करने के लिए लाइनेस्ट(C26:C45;B26:B45)आपको एक पंक्ति में 2 सेल का चयन करना होगा और क्लिक करना होगा CTRL + बदलाव + प्रवेश करना(इसके बारे में लेख देखें)। मान बाएँ कक्ष में लौटाया जाएगा ए , दायीं तरफ - बी .

टिप्पणी: इनपुट के साथ गड़बड़ी से बचने के लिए सरणी सूत्रआपको अतिरिक्त रूप से INDEX() फ़ंक्शन का उपयोग करना होगा। सूत्र = सूचकांक(लिनेस्ट(C26:C45,B26:B45),1)या बस = लाइनेस्ट(C26:C45;B26:B45)लाइन के ढलान के लिए जिम्मेदार पैरामीटर लौटाएगा, यानी। ए . सूत्र = सूचकांक(लिनेस्ट(C26:C45,B26:B45),2) Y अक्ष के साथ रेखा के प्रतिच्छेदन के लिए जिम्मेदार पैरामीटर लौटाएगा, अर्थात। बी .

मापदंडों की गणना करने के बाद, तितरबितर आकृतिआप संबंधित रेखा खींच सकते हैं.

न्यूनतम वर्ग विधि का उपयोग करके एक सीधी रेखा खींचने का दूसरा तरीका ग्राफ़ टूल है प्रवृत्ति रेखा. ऐसा करने के लिए, आरेख का चयन करें, मेनू से चयन करें लेआउट टैब, वी समूह विश्लेषणक्लिक प्रवृत्ति रेखा, तब रैखिक सन्निकटन .

डायलॉग बॉक्स में "आरेख में समीकरण दिखाएं" बॉक्स को चेक करके, आप यह सुनिश्चित कर सकते हैं कि ऊपर पाए गए पैरामीटर आरेख में मानों से मेल खाते हैं।

टिप्पणी: मापदंडों के मिलान के लिए, आरेख प्रकार होना चाहिए। मुद्दा यह है कि आरेख का निर्माण करते समय अनुसूचीएक्स-अक्ष मान उपयोगकर्ता द्वारा निर्दिष्ट नहीं किया जा सकता (उपयोगकर्ता केवल ऐसे लेबल निर्दिष्ट कर सकता है जो बिंदुओं के स्थान को प्रभावित नहीं करते हैं)। X मानों के बजाय, अनुक्रम 1 का उपयोग किया जाता है; 2; 3; ... (श्रेणियों की संख्या के लिए)। इसलिए, यदि आप निर्माण करते हैं प्रवृत्ति रेखाएक प्रकार के आरेख पर अनुसूची, तो X के वास्तविक मानों के बजाय इस अनुक्रम के मानों का उपयोग किया जाएगा, जिससे गलत परिणाम आएगा (जब तक कि, निश्चित रूप से, X के वास्तविक मान अनुक्रम 1 से मेल नहीं खाते; 2; 3; ...).

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

· एक्सेल में विधि का उपयोग करना

o "समाधान खोज" ऐड-ऑन को सक्षम करना

o समस्या की स्थितियाँ

ओ समाधान

Excel में विधि का उपयोग करना

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

समाधान खोजें ऐड-ऑन सक्षम करना

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

1. टैब पर जाएं "फ़ाइल".

2. अनुभाग नाम पर क्लिक करें "विकल्प".

3. खुलने वाली विंडो में, उपधारा का चयन करें "ऐड-ऑन".

4. ब्लॉक में "नियंत्रण", जो विंडो के नीचे स्थित है, स्विच को स्थिति पर सेट करें "एक्सेल ऐड-इन्स"(यदि इसका कोई भिन्न मान है) और बटन पर क्लिक करें "जाना...".

5. एक छोटी सी खिड़की खुलती है. हम पैरामीटर के आगे एक टिक लगाते हैं "समाधान ढूँढना". बटन पर क्लिक करें "ठीक है".

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

पाठ:एक्सेल में समाधान ढूँढना

समस्या की स्थितियाँ

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

इस निर्भरता को फ़ंक्शन द्वारा सबसे सटीक रूप से वर्णित किया जा सकता है:

साथ ही यह भी पता चल जाता है कि कब x=0 yभी बराबर 0 . इसलिए, इस समीकरण को निर्भरता द्वारा वर्णित किया जा सकता है y=nx.

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

समाधान

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

1. पहले मान के बाईं ओर एक्सएक नंबर डालो 1 . यह प्रथम गुणांक मान का अनुमानित मान होगा एन.

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

3. भरण मार्कर का उपयोग करके, इस सूत्र को नीचे दिए गए कॉलम में तालिका की संपूर्ण श्रेणी में कॉपी करें।

4. एक अलग सेल में, मानों के वर्गों के बीच अंतर के योग की गणना करें यऔर एनएक्स. ऐसा करने के लिए, बटन पर क्लिक करें "सम्मिलित करें फ़ंक्शन".

5. खुले में "फ़ंक्शन विज़ार्ड"एक प्रविष्टि की तलाश है "सुम्कवर्ण". इसे चुनें और बटन दबाएँ "ठीक है".

6. तर्क विंडो खुलती है. खेत मेँ "ऐरे_एक्स" य. खेत मेँ "सरणी_य"स्तंभ कक्षों की श्रेणी दर्ज करें एनएक्स. मान दर्ज करने के लिए, बस कर्सर को फ़ील्ड में रखें और शीट पर संबंधित श्रेणी का चयन करें। एंटर करने के बाद बटन पर क्लिक करें "ठीक है".

7. टैब पर जाएं "डेटा". टूलबॉक्स में रिबन पर "विश्लेषण"बटन पर क्लिक करें "समाधान ढूँढना".

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

9. समाधान गुणांक सेल में प्रदर्शित किया जाएगा एन. यह मान फ़ंक्शन का सबसे छोटा वर्ग होगा। यदि परिणाम उपयोगकर्ता को संतुष्ट करता है, तो बटन पर क्लिक करें "ठीक है"एक अतिरिक्त विंडो में.

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

http://multitest.semico.ru/mnk.htm

सामान्य प्रावधान

निरपेक्ष मान में संख्या जितनी छोटी होगी, चुनी गई सीधी रेखा (2) उतनी ही बेहतर होगी। एक सीधी रेखा (2) के चयन की सटीकता की विशेषता के रूप में, हम वर्गों का योग ले सकते हैं

S के लिए न्यूनतम शर्तें होंगी

	(6)
	(7)

समीकरण (6) और (7) को इस प्रकार लिखा जा सकता है:

	(8)
	(9)

समीकरण (8) और (9) से xi और y i के प्रयोगात्मक मानों से a और b खोजना आसान है। समीकरण (8) और (9) द्वारा परिभाषित रेखा (2), न्यूनतम वर्ग विधि द्वारा प्राप्त रेखा कहलाती है (यह नाम इस बात पर जोर देता है कि वर्गों का योग एस न्यूनतम है)। समीकरण (8) और (9), जिनसे सीधी रेखा (2) निर्धारित होती है, सामान्य समीकरण कहलाते हैं।

आप सामान्य समीकरण बनाने का एक सरल और सामान्य तरीका बता सकते हैं। प्रायोगिक बिंदु (1) और समीकरण (2) का उपयोग करके, हम ए और बी के लिए समीकरणों की एक प्रणाली लिख सकते हैं

आप 1 =कुल्हाड़ी 1 +बी,
आप 2 =कुल्हाड़ी 2 +बी, ...		(10)
y n = ax n + b,

आइए इनमें से प्रत्येक समीकरण के बाएँ और दाएँ पक्षों को पहले अज्ञात a के गुणांक से गुणा करें (अर्थात x 1, x 2, ..., x n से) और परिणामी समीकरण जोड़ें, जिसके परिणामस्वरूप पहला सामान्य समीकरण (8) प्राप्त होगा। .

आइए हम इनमें से प्रत्येक समीकरण के बाएँ और दाएँ पक्षों को दूसरे अज्ञात b के गुणांक से गुणा करें, अर्थात। 1 से, और परिणामी समीकरण जोड़ें, परिणाम दूसरा सामान्य समीकरण (9) है।

सामान्य समीकरण प्राप्त करने की यह विधि सामान्य है: उदाहरण के लिए, यह फ़ंक्शन के लिए उपयुक्त है

एक स्थिर मान है और इसे प्रयोगात्मक डेटा (1) से निर्धारित किया जाना चाहिए।

K के लिए समीकरणों की प्रणाली लिखी जा सकती है:

न्यूनतम वर्ग विधि का उपयोग करके सीधी रेखा (2) खोजें।

समाधान।हम देखतें है:

एक्स आई =21, वाई आई =46.3, एक्स आई 2 =91, एक्स आई वाई आई =179.1।

हम समीकरण (8) और (9)91a+21b=179.1 लिखते हैं,

21a+6b=46.3, यहाँ से हम पाते हैं
ए=0.98 बी=4.3.