حسنًا ، في العمل قاموا بإبلاغ التفتيش ، تمت كتابة المقالة في المنزل من أجل المؤتمر - الآن يمكنك الكتابة في المدونة. أثناء معالجة بياناتي ، أدركت أنه لا يمكنني المساعدة ولكن الكتابة عن وظيفة إضافية رائعة وضرورية للغاية في Excel ، والتي تسمى. لذلك سيتم تخصيص المقالة لهذه الوظيفة الإضافية المحددة ، وسأخبرك عنها باستخدام مثال على الاستخدام طريقة المربعات الصغرى(LSM) للبحث عن معاملات غير معروفة للمعادلة في وصف البيانات التجريبية.

كيفية تمكين الوظيفة الإضافية "البحث عن حل"

أولاً ، دعنا نتعرف على كيفية تمكين هذه الوظيفة الإضافية.

1. انتقل إلى قائمة "ملف" وحدد "خيارات Excel"

2. في النافذة التي تظهر ، حدد "البحث عن حل" وانقر على "انتقال".

3. في النافذة التالية ، ضع علامة اختيار أمام عنصر "البحث عن حل" وانقر على "موافق".

4. تم تنشيط الوظيفة الإضافية - الآن يمكن العثور عليها في عنصر قائمة "البيانات".

طريقة المربعات الصغرى

الآن باختصار طريقة المربعات الصغرى (LSM) وأين يمكن تطبيقه.

لنفترض أن لدينا مجموعة بيانات بعد أن أجرينا بعض التجارب حيث درسنا تأثيرات قيمة X على قيمة Y.

نريد وصف هذا التأثير رياضيًا ، حتى نتمكن لاحقًا من استخدام هذه الصيغة ونعلم أنه إذا قمنا بتغيير قيمة X كثيرًا ، فسنحصل على قيمة Y كذا وكذا ...

لنأخذ مثالًا بسيطًا للغاية (انظر الصورة).

لا يفكر في أن النقاط تقع واحدة تلو الأخرى كما لو كانت في خط مستقيم ، وبالتالي فإننا نفترض بأمان أن اعتمادنا موصوف دالة خطيةص = ك س + ب. في الوقت نفسه ، نحن على يقين من أنه عندما تساوي X صفرًا ، فإن قيمة Y تساوي صفرًا أيضًا. هذا يعني أن الوظيفة التي تصف التبعية ستكون أبسط: y = kx (تذكر المنهج الدراسي).

بشكل عام ، علينا إيجاد المعامل k. هذا ما سنفعله به MNC باستخدام الوظيفة الإضافية "البحث عن حل".

الطريقة (هنا - الانتباه: تحتاج إلى التفكير في الأمر) كان مجموع الفروق التربيعية بين القيم التي تم الحصول عليها تجريبياً والقيم المحسوبة المقابلة لها في حدها الأدنى. أي عندما تكون 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. ويجب أن يكون مجموع كل هذه المربعات صغيرًا قدر الإمكان.

لذا ، لنبدأ التدريب على استخدام LSM و وظائف Excel الإضافية "البحث عن حل" .

تطبيق إيجاد حل الوظيفة الإضافية

1. إذا لم تقم بتمكين الوظيفة الإضافية "البحث عن حل" ، فارجع إلى الخطوة كيفية تمكين الوظيفة الإضافية "البحث عن حل" وتمكينها 🙂

2. في الخلية A1 ، أدخل القيمة "1". ستكون هذه الوحدة أول تقريب للقيمة الحقيقية للمعامل (ك) لاعتمادنا الوظيفي y = kx.

3. في العمود B لدينا قيم المعلمة X ، في العمود C - قيم المعلمة Y. في خلايا العمود D نقوم بإدخال الصيغة: "المعامل k مضروبًا في القيمة X". على سبيل المثال ، في الخلية D1 ، أدخل "= A1 * B1" ، في الخلية D2 ، أدخل "= A1 * B2" ، وهكذا.

4. نعتقد أن المعامل k يساوي واحدًا وأن الوظيفة f (x) \ u003d y \ u003d 1 * x هي التقريب الأول لحلنا. يمكننا حساب مجموع تربيع الفروق بين القيم المقاسة لـ Y وتلك المحسوبة باستخدام الصيغة y = 1 * x. يمكننا القيام بكل هذا يدويًا عن طريق توجيه مراجع الخلايا المناسبة إلى الصيغة: "= (D2-C2) ^ 2 + (D3-C3) ^ 2 + (D4-C4) ^ 2 ... إلخ. مخطئون ونفهم أننا فقدنا الكثير من الوقت. في برنامج Excel ، لحساب مجموع الفروق التربيعية ، توجد صيغة خاصة ، "SUMQDIFF" ، والتي ستفعل كل شيء من أجلنا ، لندخلها في الخلية A2 وتعيين البيانات الأولية: نطاق القيم المقاسة Y (العمود C) ونطاق قيم Y المحسوبة (العمود D).

4. تم حساب مجموع الفروق بين المربعات - انتقل الآن إلى علامة التبويب "البيانات" وحدد "البحث عن حل".

5. في القائمة التي تظهر ، حدد الخلية A1 لتكون الخلية المطلوب تغييرها (الخلية التي تحتوي على المعامل k).

6. كهدف ، حدد الخلية A2 وعيّن الشرط "تعيين مساوٍ للحد الأدنى للقيمة". تذكر أن هذه هي الخلية حيث نحسب مجموع الفروق التربيعية بين القيم المحسوبة والمقاسة ، ويجب أن يكون هذا المقدار في حده الأدنى. نضغط على "تنفيذ".

7. تم اختيار المعامل k. الآن يمكن ملاحظة أن القيم المحسوبة أصبحت الآن قريبة جدًا من القيم المقاسة.

ملاحظة.

بشكل عام ، بالطبع ، لتقريب البيانات التجريبية في Excel ، هناك ادوات خاصة، والتي تتيح لك وصف البيانات باستخدام دالة خطية وأسية وقوية ومتعددة الحدود ، لذلك يمكنك غالبًا الاستغناء عن n الوظائف الإضافية "ابحث عن حل". لقد تحدثت عن كل طرق التقريب هذه في مقالتي ، لذا إذا كنت مهتمًا ، ألق نظرة. ولكن عندما يتعلق الأمر ببعض الوظائف الغريبة بمعامل واحد غير معروفأو مشكلات التحسين ، ثم هنا البنية الفوقيةبقدر الإمكان.

الوظيفة الإضافية "البحث عن حل"يمكن استخدامها في مهام أخرى ، الشيء الرئيسي هو فهم الجوهر: هناك خلية نختار فيها قيمة ، وهناك خلية مستهدفة يتم فيها تعيين شرط لاختيار معلمة غير معروفة.
هذا كل شئ! في المقال التالي سأروي قصة خرافية عن الإجازة ، حتى لا يفوتك إصدار المقال ،

أحد طرق دراسة العلاقات العشوائية بين السمات هو تحليل الانحدار.
تحليل الانحدار هو اشتقاق معادلة الانحدار ، والتي تُستخدم للعثور على متوسط ​​قيمة متغير عشوائي (نتيجة الميزة) ، إذا كانت قيمة متغيرات أخرى (أو غيرها) (عوامل مميزة) معروفة. يتضمن الخطوات التالية:

1. اختيار شكل الاتصال (نوع معادلة الانحدار التحليلي) ؛
2. تقدير معلمات المعادلة ؛
3. تقييم جودة معادلة الانحدار التحليلي.

الأكثر استخدامًا لتقدير المعلمات هو طريقة المربعات الصغرى (LSM).
تعطي طريقة المربعات الصغرى أفضل التقديرات (المتسقة والفعالة وغير المتحيزة) لمعلمات معادلة الانحدار. ولكن فقط إذا تم استيفاء افتراضات معينة حول المصطلح العشوائي (u) والمتغير المستقل (x) (انظر افتراضات OLS).

مشكلة تقدير معاملات معادلة زوج خطية بطريقة المربعات الصغرىيتألف مما يلي: للحصول على مثل هذه التقديرات للمعلمات ، حيث يكون مجموع الانحرافات التربيعية للقيم الفعلية للميزة الفعالة - y i من القيم المحسوبة - ضئيلاً.
رسميا معيار OLSيمكن كتابتها على هذا النحو: .

تصنيف طرق المربعات الصغرى

1. طريقة المربعات الصغرى.
2. طريقة الاحتمالية القصوى (بالنسبة لنموذج الانحدار الخطي الكلاسيكي العادي ، يتم افتراض الحالة الطبيعية لبقايا الانحدار).
3. يتم استخدام طريقة المربعات الصغرى المعممة لـ GLSM في حالة الارتباط التلقائي للخطأ وفي حالة عدم التجانس.
4. طريقة المربعات الصغرى المرجحة (حالة خاصة من GLSM مع بقايا متجانسة).

وضح الجوهر الطريقة الكلاسيكية للمربعات الصغرى بيانياً. للقيام بذلك ، سنقوم ببناء مخطط نقطي وفقًا لبيانات الرصد (x i ، y i ، i = 1 ؛ n) في نظام إحداثيات مستطيل (تسمى هذه النقطة النقطية حقل الارتباط). دعنا نحاول إيجاد خط مستقيم أقرب إلى نقاط حقل الارتباط. وفقًا لطريقة المربعات الصغرى ، يتم اختيار الخط بحيث يكون مجموع المسافات الرأسية المربعة بين نقاط حقل الارتباط وهذا الخط في حده الأدنى.

تدوين رياضي لهذه المشكلة: .
قيم y i و x i = 1 ... n معروفة لنا ، هذه بيانات رصدية. في الدالة S هم ثوابت. المتغيرات في هذه الوظيفة هي التقديرات المطلوبة للمعلمات - ،. للعثور على الحد الأدنى لدالة من متغيرين ، من الضروري حساب المشتقات الجزئية لهذه الوظيفة فيما يتعلق بكل من المعلمات ومعادلتها بالصفر ، أي .
نتيجة لذلك ، نحصل على نظام من معادلتين خطيتين عاديتين:
لحل هذا النظام ، نجد تقديرات المعلمات المطلوبة:

يمكن التحقق من صحة حساب معلمات معادلة الانحدار من خلال مقارنة المجاميع (بعض التناقض ممكن بسبب تقريب الحسابات).
لحساب تقديرات المعلمات ، يمكنك بناء الجدول 1.
تشير علامة معامل الانحدار ب إلى اتجاه العلاقة (إذا كانت ب> 0 ، تكون العلاقة مباشرة ، إذا ب<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
بشكل رسمي ، قيمة المعلمة a هي متوسط ​​قيمة y لـ x يساوي صفرًا. إذا لم يكن لعامل الإشارة قيمة صفرية ولا يمكن أن يكون لها ، فإن التفسير أعلاه للمعامل a لا معنى له.

تقييم مدى ضيق العلاقة بين السمات يتم تنفيذها باستخدام معامل الارتباط الزوجي الخطي - r x ، y. يمكن حسابها باستخدام الصيغة: . بالإضافة إلى ذلك ، يمكن تحديد معامل الارتباط الزوجي الخطي من حيث معامل الانحدار ب: .
يتراوح نطاق القيم المسموح بها للمعامل الخطي للارتباط الزوجي من -1 إلى +1. تشير علامة معامل الارتباط إلى اتجاه العلاقة. إذا كان r x ، y> 0 ، يكون الاتصال مباشرًا ؛ إذا ص س ، ذ<0, то связь обратная.
إذا كان هذا المعامل قريبًا من الوحدة في المعامل ، فيمكن تفسير العلاقة بين السمات على أنها علاقة خطية قريبة إلى حد ما. إذا كان معاملها يساوي واحد ê r x ، y ê = 1 ، فإن العلاقة بين السمات تكون وظيفية خطية. إذا كانت السمتان x و y مستقلتين خطيًا ، فإن r x و y قريبان من 0.
يمكن أيضًا استخدام الجدول 1 لحساب r x و y.

لتقييم جودة معادلة الانحدار التي تم الحصول عليها ، يتم حساب المعامل النظري للتحديد - R 2 yx:

,
حيث d 2 هو التباين y الذي تفسره معادلة الانحدار ؛
ه 2 - التباين المتبقي (غير المبرر بواسطة معادلة الانحدار) y ؛
s 2 y - إجمالي (إجمالي) التباين y.
يميز معامل التحديد حصة التباين (التشتت) للميزة الناتجة y ، التي يفسرها الانحدار (وبالتالي العامل x) ، في التباين الكلي (التشتت) y. معامل التحديد R 2 yx يأخذ قيمًا من 0 إلى 1. وفقًا لذلك ، تحدد القيمة 1-R 2 yx نسبة التباين y الناجم عن تأثير العوامل الأخرى التي لم تؤخذ في الاعتبار في النموذج وأخطاء المواصفات.
مع الانحدار الخطي المقترن R 2 yx = r 2 yx.

الذي يجد أوسع تطبيق في مختلف مجالات العلم والممارسة. يمكن أن تكون الفيزياء والكيمياء والبيولوجيا والاقتصاد وعلم الاجتماع وعلم النفس وما إلى ذلك. بإرادة القدر ، غالبًا ما أتعامل مع الاقتصاد ، وبالتالي سأرتب لك اليوم تذكرة إلى بلد رائع يسمى الاقتصاد القياسي=) ... كيف لا تريد ذلك ؟! إنه جيد جدًا هناك - عليك فقط أن تقرر! … ولكن ما تريده بالتأكيد هو أن تتعلم كيفية حل المشكلات المربعات الصغرى. وعلى وجه الخصوص ، سيتعلم القراء المجتهدون حلها ليس فقط بدقة ، ولكن أيضًا بسرعة كبيرة ؛-) ولكن أولاً بيان عام للمشكلة+ مثال ذو صلة:

دع المؤشرات تدرس في بعض المجالات التي لها تعبير كمي. في الوقت نفسه ، هناك كل الأسباب للاعتقاد بأن المؤشر يعتمد على المؤشر. يمكن أن يكون هذا الافتراض فرضية علمية وقائمة على الفطرة السليمة. دعونا نترك العلم جانبًا ، ونستكشف المزيد من المجالات الشهية - مثل محلات البقالة. للدلالة به:

- مساحة البيع بالتجزئة لمتجر بقالة ، متر مربع ،
- حجم المبيعات السنوي لمتجر بقالة مليون روبل.

من الواضح تمامًا أنه كلما زادت مساحة المتجر ، زاد حجم مبيعاته في معظم الحالات.

افترض أنه بعد إجراء الملاحظات / التجارب / الحسابات / الرقص باستخدام الدف ، لدينا بيانات رقمية تحت تصرفنا:

مع متاجر البقالة ، أعتقد أن كل شيء واضح: - هذه هي منطقة المتجر الأول ، - حجم مبيعاتها السنوي ، - مساحة المتجر الثاني ، - حجم مبيعاتها السنوية ، إلخ. بالمناسبة ، ليس من الضروري على الإطلاق الوصول إلى المواد المصنفة - يمكن الحصول على تقييم دقيق إلى حد ما لدوران باستخدام الإحصاء الرياضي. ومع ذلك ، لا تشتت انتباهك ، مسار التجسس التجاري مدفوع بالفعل =)

يمكن أيضًا كتابة البيانات الجدولية في شكل نقاط وتصويرها بالطريقة المعتادة بالنسبة لنا. النظام الديكارتي .

دعنا نجيب على سؤال مهم: كم عدد النقاط اللازمة لدراسة نوعية؟

كلما كان ذلك أفضل ، كلما كان ذلك أفضل. الحد الأدنى المسموح به للمجموعة يتكون من 5-6 نقاط. بالإضافة إلى ذلك ، مع وجود كمية صغيرة من البيانات ، لا ينبغي تضمين النتائج "غير الطبيعية" في العينة. لذلك ، على سبيل المثال ، يمكن لمتجر النخبة الصغير أن يساعد في تنفيذ أوامر من حيث الحجم أكبر من "زملائهم" ، مما يؤدي إلى تشويه النمط العام الذي يجب العثور عليه!

إذا كان الأمر بسيطًا للغاية ، فنحن بحاجة إلى اختيار وظيفة ، جدولالذي يمر أقرب ما يمكن من النقاط . تسمى هذه الوظيفة تقريبي (تقريب - تقريب)أو الوظيفة النظرية . بشكل عام ، يظهر هنا على الفور "متظاهر" واضح - متعدد الحدود من الدرجة العالية ، يمر الرسم البياني الخاص به عبر جميع النقاط. لكن هذا الخيار معقد ، وغالبًا ما يكون غير صحيح. (لأن الرسم البياني سوف "ينفخ" طوال الوقت ويعكس بشكل سيء الاتجاه الرئيسي).

وبالتالي ، يجب أن تكون الوظيفة المرغوبة بسيطة بما فيه الكفاية وتعكس في نفس الوقت التبعية بشكل كافٍ. كما قد تتخيل ، تسمى إحدى طرق العثور على هذه الوظائف المربعات الصغرى. أولاً ، دعنا نحلل جوهرها بطريقة عامة. دع بعض الوظائف تقرب البيانات التجريبية:


كيف تقيم دقة هذا التقريب؟ دعونا نحسب أيضًا الفروق (الانحرافات) بين القيم التجريبية والوظيفية (ندرس الرسم). الفكرة الأولى التي تتبادر إلى الذهن هي تقدير حجم المجموع ، لكن المشكلة هي أن الاختلافات يمكن أن تكون سلبية. (على سبيل المثال، ) والانحرافات نتيجة لهذا الجمع تلغي بعضها البعض. لذلك ، كتقدير لدقة التقريب ، فإنه يقترح نفسه لأخذ المجموع الوحداتالانحرافات:

أو في شكل مطوي: (فجأة ، من لا يعرف: هو رمز الجمع ، وهو متغير مساعد - "عداد" ، يأخذ القيم من 1 إلى).

من خلال تقريب النقاط التجريبية بوظائف مختلفة ، سنحصل على قيم مختلفة لـ ، ومن الواضح أنه عندما يكون هذا المجموع أصغر ، تكون هذه الوظيفة أكثر دقة.

مثل هذا الأسلوب موجود ويسمى طريقة المعامل الأقل. ومع ذلك ، فقد أصبح من الناحية العملية أكثر انتشارًا. طريقة التربيع الصغرى، حيث يتم التخلص من القيم السلبية المحتملة ليس بواسطة المعامل ، ولكن عن طريق تربيع الانحرافات:

، وبعد ذلك يتم توجيه الجهود لاختيار مثل هذه الوظيفة التي مجموع الانحرافات التربيعية كانت صغيرة بقدر الإمكان. في الواقع ، ومن هنا جاء اسم الطريقة.

والآن نعود إلى نقطة مهمة أخرى: كما هو مذكور أعلاه ، يجب أن تكون الوظيفة المحددة بسيطة للغاية - ولكن هناك أيضًا العديد من هذه الوظائف: خطي , القطعي, متسارع, لوغاريتمي, من الدرجة الثانية إلخ. وبالطبع أود هنا على الفور "تقليص مجال النشاط". أي فئة من الوظائف تختار للبحث؟ تقنية بدائية لكنها فعالة:

- أسهل طريقة لرسم النقاط على الرسم وتحليل موقعهم. إذا كانت تميل إلى أن تكون في خط مستقيم ، فعليك البحث عنها معادلة الخط المستقيم مع القيم المثلى و. بعبارة أخرى ، تتمثل المهمة في العثور على هذه المعاملات - بحيث يكون مجموع الانحرافات التربيعية هو الأصغر.

إذا كانت النقاط موجودة ، على سبيل المثال ، على طول مقارنة مبالغ فيها، فمن الواضح أن الدالة الخطية ستعطي تقريبًا ضعيفًا. في هذه الحالة ، نبحث عن أكثر المعاملات "ملاءمة" لمعادلة القطع الزائد - تلك التي تعطي الحد الأدنى لمجموع المربعات .

لاحظ الآن أننا نتحدث في كلتا الحالتين وظائف متغيرين، الحجج التي البحث عن خيارات التبعية:

وفي جوهرها ، نحتاج إلى حل مشكلة معيارية - لإيجاد على الأقل دالة من متغيرين.

تذكر مثالنا: لنفترض أن نقاط "المتجر" تميل إلى أن تكون موجودة في خط مستقيم وأن هناك كل الأسباب للاعتقاد بوجود الاعتماد الخطيدوران من منطقة التجارة. دعنا نجد معاملي هذه "أ" و "تكون" بحيث يكون مجموع الانحرافات التربيعية كان الأصغر. كل شيء كالمعتاد - أولاً المشتقات الجزئية من الدرجة الأولى. وفق القاعدة الخطيةيمكنك التفريق أسفل رمز المجموع:

إذا كنت ترغب في استخدام هذه المعلومات لمقال أو ورقة بحثية ، فسأكون ممتنًا جدًا للرابط الموجود في قائمة المصادر ، فلن تجد مثل هذه الحسابات التفصيلية في أي مكان:

لنصنع نظامًا قياسيًا:

نقوم باختزال كل معادلة بـ "اثنين" ، بالإضافة إلى "تفكيك" المجاميع:

ملحوظة : تحليل بشكل مستقل لماذا يمكن حذف "a" و "be" من رمز المجموع. بالمناسبة ، رسميًا يمكن عمل ذلك بالمجموع

دعنا نعيد كتابة النظام في شكل "تطبيقي":

وبعد ذلك يبدأ رسم خوارزمية حل مشكلتنا:

هل نعرف إحداثيات النقاط؟ نعلم. مسائل حسابية ممكن نجد بسهولة. نحن نؤلف أبسط نظام من معادلتين خطيتين مع مجهولين("a" و "beh"). نحل النظام ، على سبيل المثال ، طريقة كرامر، مما أدى إلى نقطة ثابتة. تدقيق حالة كافية لأقصى حد، يمكننا التحقق من أن الوظيفة في هذه المرحلة تصل بدقة الحد الأدنى. يرتبط التحقق بحسابات إضافية ، وبالتالي سنتركه وراء الكواليس. (إذا لزم الأمر ، يمكن عرض الإطار المفقود). نستخلص الاستنتاج النهائي:

وظيفة أفضل طريقة (على الأقل مقارنة بأي دالة خطية أخرى)تقرب النقاط التجريبية . بشكل تقريبي ، يمر الرسم البياني الخاص به في أقرب وقت ممكن من هذه النقاط. في التقاليد الاقتصاد القياسيتسمى أيضًا وظيفة التقريب الناتجة معادلة الانحدار الخطي المقترنة .

المشكلة قيد النظر لها أهمية عملية كبيرة. في الحالة مع مثالنا ، المعادلة يسمح لك بالتنبؤ بنوع دوران ("yig")سيكون في المتجر بقيمة أو أخرى من قيمة منطقة البيع (معنى واحد أو آخر لـ "س"). نعم ، ستكون التوقعات الناتجة مجرد توقع ، ولكن في كثير من الحالات ستصبح دقيقة تمامًا.

سأقوم بتحليل مشكلة واحدة فقط بالأرقام "الحقيقية" ، حيث لا توجد صعوبات فيها - كل الحسابات تتم على مستوى المناهج الدراسية في الصفوف من 7 إلى 8. في 95٪ من الحالات ، سيُطلب منك العثور على دالة خطية فقط ، لكن في نهاية المقالة سأوضح أنه لم يعد من الصعب العثور على معادلات القطع الزائد والأسس وبعض الدوال الأخرى.

في الواقع ، يبقى توزيع الأشياء الجيدة الموعودة - حتى تتعلم كيفية حل مثل هذه الأمثلة ليس فقط بدقة ، ولكن أيضًا بسرعة. ندرس بعناية المعيار:

مهمة

نتيجة لدراسة العلاقة بين مؤشرين ، تم الحصول على أزواج الأرقام التالية:

باستخدام طريقة المربعات الصغرى ، أوجد الدالة الخطية التي تقترب بشكل أفضل من العملية التجريبية (ذوي الخبرة)بيانات. ارسم رسمًا ، في نظام إحداثيات مستطيل ديكارتي ، يرسم نقاطًا تجريبية ورسمًا بيانيًا للوظيفة التقريبية . أوجد مجموع الانحرافات التربيعية بين القيم التجريبية والنظرية. اكتشف ما إذا كانت الوظيفة أفضل (من حيث طريقة المربعات الصغرى)نقاط تجريبية تقريبية.

لاحظ أن قيم "x" هي قيم طبيعية ، وهذا له معنى مميز وذا معنى ، والذي سأتحدث عنه بعد قليل ؛ لكنها بالطبع يمكن أن تكون كسرية. بالإضافة إلى ذلك ، اعتمادًا على محتوى مهمة معينة ، يمكن أن تكون قيمتا "X" و "G" سالبة كليًا أو جزئيًا. حسنًا ، لقد تم تكليفنا بمهمة "مجهولة الهوية" ، ونبدأها حل:

نجد معاملات الوظيفة المثلى كحل للنظام:

لأغراض تدوين أكثر إحكاما ، يمكن حذف متغير "العداد" ، لأنه من الواضح بالفعل أن الجمع يتم من 1 إلى.

من الأنسب حساب المبالغ المطلوبة في شكل جدول:


يمكن إجراء الحسابات باستخدام آلة حاسبة صغيرة ، ولكن من الأفضل استخدام برنامج Excel - سواء بشكل أسرع أو بدون أخطاء ؛ شاهد فيديو قصير:

وهكذا ، نحصل على ما يلي نظام:

هنا يمكنك ضرب المعادلة الثانية في 3 و اطرح الثاني من مصطلح المعادلة الأول حسب المصطلح. لكن هذا هو الحظ - في الممارسة العملية ، غالبًا ما تكون الأنظمة غير موهوبة ، وفي مثل هذه الحالات يتم حفظها طريقة كرامر:
، لذلك فإن النظام لديه حل فريد.

لنقم بفحص. أتفهم أنني لا أريد ذلك ، لكن لماذا تتخطى الأخطاء حيث لا يمكنك تفويتها مطلقًا؟ عوّض عن الحل الموجود في الجانب الأيسر من كل معادلة في النظام:

يتم الحصول على الأجزاء الصحيحة من المعادلات المقابلة ، مما يعني أن النظام قد تم حله بشكل صحيح.

وبالتالي ، فإن وظيفة التقريب المطلوبة: - from جميع الوظائف الخطيةمن الأفضل تقريب البيانات التجريبية بواسطتها.

على عكس مستقيم اعتماد دوران المتجر على منطقته ، والاعتماد الموجود هو يعكس (مبدأ "الأكثر - الأقل")، ويتم الكشف عن هذه الحقيقة على الفور من خلال السلبية معامل الزاوي. وظيفة يخبرنا أنه مع زيادة مؤشر معين بمقدار وحدة واحدة ، تنخفض قيمة المؤشر التابع متوسطبمقدار 0.65 وحدة. كما يقولون ، كلما ارتفع سعر الحنطة السوداء ، قل بيعها.

لرسم دالة التقريب ، نجد اثنين من قيمها:

وتنفيذ الرسم:


يسمى الخط الذي تم إنشاؤه خط الاتجاه (أي ، خط الاتجاه الخطي ، أي في الحالة العامة ، الاتجاه ليس بالضرورة خطًا مستقيمًا). الجميع على دراية بتعبير "أن نكون في الاتجاه" ، وأعتقد أن هذا المصطلح لا يحتاج إلى تعليقات إضافية.

احسب مجموع الانحرافات التربيعية بين القيم التجريبية والنظرية. هندسيًا ، هذا هو مجموع مربعات أطوال المقاطع "القرمزية" (اثنان منها صغيرتان جدًا ولا يمكنك حتى رؤيتهما).

دعونا نلخص العمليات الحسابية في جدول:


يمكن تنفيذها يدويًا مرة أخرى ، فقط في حالة ما إذا سأقدم مثالاً للنقطة الأولى:

ولكن من الأكثر فاعلية القيام بالطريقة المعروفة بالفعل:

دعنا نكرر: ما معنى النتيجة؟من جميع الوظائف الخطيةوظيفة الأس هو الأصغر ، أي أنه أفضل تقريب في عائلته. وهنا ، بالمناسبة ، السؤال الأخير للمشكلة ليس عرضيًا: ماذا لو الوظيفة الأسية المقترحة هل سيكون من الأفضل تقريب النقاط التجريبية؟

دعنا نجد المجموع المقابل للانحرافات التربيعية - لتمييزها ، سأقوم بتعيينها بالحرف "إبسيلون". التقنية هي نفسها تمامًا:


ومرة أخرى لكل حساب حريق للنقطة الأولى:

في Excel ، نستخدم الوظيفة القياسية EXP (يمكن العثور على بناء الجملة في تعليمات Excel).

خاتمة: ، لذا فإن الدالة الأسية تقترب من النقاط التجريبية أسوأ من الخط المستقيم .

ولكن تجدر الإشارة هنا إلى أن "الأسوأ" هو لا يعني بعد، ما الخطأ. لقد قمت الآن ببناء رسم بياني لهذه الدالة الأسية - وهو أيضًا يمر بالقرب من النقاط - لدرجة أنه بدون دراسة تحليلية يصعب تحديد الوظيفة الأكثر دقة.

هذا يكمل الحل ، وأعود إلى مسألة القيم الطبيعية للحجة. في دراسات مختلفة ، كقاعدة عامة ، يتم ترقيم الأشهر أو السنوات أو الفترات الزمنية المتساوية الأخرى بعلامة "X" طبيعية أو اقتصادية أو اجتماعية. ضع في اعتبارك ، على سبيل المثال ، مثل هذه المشكلة.

4.1 استخدام وظائف مدمجة

عملية حسابية معاملات الانحدارباستخدام الوظيفة

LINEST(القيم_ y; القيم_ x; كونست; إحصائيات),

القيم_ y- مجموعة من قيم y ،

القيم_ x- مصفوفة قيم اختيارية xإذا مجموعة Xإذا تم حذفها ، فمن المفترض أن هذه مصفوفة (1 ؛ 2 ؛ 3 ؛ ...) بنفس حجم القيم_ y,

كونست- قيمة منطقية تشير إلى ما إذا كان الثابت مطلوبًا بكان يساوي 0. إذا كونستله المعنى حقيقيأو تم حذفها ، إذن بمحسوبة بالطريقة المعتادة. إذا كانت الحجة كونستهو خطأ ، إذن بيفترض أن تكون 0 والقيم أيتم اختيارهم بحيث تكون العلاقة ص = الفأس.

إحصائيات- قيمة منطقية تشير إلى ما إذا كان يلزم إرجاع إحصائيات انحدار إضافية. إذا كانت الحجة إحصائياتله المعنى حقيقي، ثم الوظيفة LINESTإرجاع إحصائيات انحدار إضافية. إذا كانت الحجة إحصائياتله المعنى كذبأو تم حذفها ، ثم الوظيفة LINESTإرجاع المعامل فقط أودائم ب.

يجب أن نتذكر أن نتيجة الوظائف LINEST ()هي مجموعة من القيم - مصفوفة.

للحساب معامل الارتباطيتم استخدام الوظيفة

كوريل(صفيف 1;صفيف 2),

إرجاع قيم معامل الارتباط ، أين صفيف 1- مجموعة من القيم ذ, صفيف 2- مجموعة من القيم x. صفيف 1و صفيف 2يجب أن يكون بنفس الحجم.

مثال 1. مدمن ذ(x) في الجدول. يبني خط الانحدارواحسب معامل الارتباط.

دعنا ندخل جدول القيم في ورقة MS Excel ونبني مخطط مبعثر. ستأخذ ورقة العمل النموذج الموضح في الشكل. 2.

من أجل حساب قيم معاملات الانحدار أو بحدد الخلايا A7: B7 ،دعنا ننتقل إلى معالج الوظيفة وفي الفئة الإحصاءاختر وظيفة LINEST. املأ مربع الحوار الذي يظهر كما هو موضح في الشكل. 3 واضغط نعم.

نتيجة لذلك ، ستظهر القيمة المحسوبة في الخلية فقط أ 6(الشكل 4). لتظهر قيمة في خلية ب 6تحتاج إلى الدخول في وضع التحرير (مفتاح F2)ثم اضغط على مجموعة المفاتيح CTRL + SHIFT + ENTER.

لحساب قيمة معامل الارتباط لكل خلية ج 6تم تقديم الصيغة التالية:

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

معرفة معاملات الانحدار أو باحسب قيم الوظيفة ذ=فأس+بعلى سبيل المعطى x. للقيام بذلك ، نقدم الصيغة

B5 = $ A $ 7 * B2 + $ B $ 7

وانسخه إلى النطاق С5: J5(الشكل 5).

دعنا نرسم خط الانحدار على الرسم التخطيطي. حدد النقاط التجريبية على الرسم البياني ، وانقر بزر الماوس الأيمن وحدد الأمر البيانات الأولية. في مربع الحوار الذي يظهر (الشكل 5) ، حدد علامة التبويب صفوانقر على الزر يضيف. املأ حقول الإدخال ، كما هو موضح في الشكل. 6 واضغط على الزر نعم. سيتم إضافة خط الانحدار إلى مخطط البيانات التجريبية. بشكل افتراضي ، سيتم عرض الرسم البياني الخاص به كنقاط غير متصلة بخطوط تجانس.

أرز. 6

لتغيير مظهر خط الانحدار ، قم بتنفيذ الخطوات التالية. انقر بزر الماوس الأيمن فوق النقاط التي تصور الرسم البياني الخطي ، وحدد الأمر نوع التخطيطوقم بتعيين نوع مخطط التشتت ، كما هو موضح في الشكل. 7.

يمكن تغيير نوع الخط ولونه وسمكه على النحو التالي. حدد السطر في الرسم التخطيطي ، واضغط على زر الماوس الأيمن وحدد الأمر في قائمة السياق تنسيق سلسلة البيانات ...بعد ذلك ، قم بإجراء الإعدادات ، على سبيل المثال ، كما هو موضح في الشكل. 8.

نتيجة لجميع التحولات ، نحصل على رسم بياني للبيانات التجريبية وخط انحدار في منطقة بيانية واحدة (الشكل 9).

4.2 باستخدام خط الاتجاه.

يتم تنفيذ بناء التبعيات التقريبية المختلفة في MS Excel كخاصية مخطط - خط الاتجاه.

مثال 2. نتيجة للتجربة ، تم تحديد بعض التبعية الجدولية.

حدد وقم ببناء اعتماد تقريبي. بناء الرسوم البيانية للتبعيات التحليلية الجدولة والمجهزة.

يمكن تقسيم حل المشكلة إلى المراحل التالية: إدخال البيانات الأولية ، وإنشاء مخطط مبعثر وإضافة خط اتجاه إلى هذا المخطط.

دعونا نفكر في هذه العملية بالتفصيل. دعنا ندخل البيانات الأولية في ورقة العمل ونرسم البيانات التجريبية. بعد ذلك ، حدد النقاط التجريبية على الرسم البياني ، وانقر بزر الماوس الأيمن واستخدم الأمر يضيفل خط الاتجاه(الشكل 10).

يسمح لك مربع الحوار الذي يظهر ببناء تبعية تقريبية.

تشير علامة التبويب الأولى (الشكل 11) من هذه النافذة إلى نوع الاعتماد التقريبي.

يحدد الثاني (الشكل 12) معلمات البناء:

اسم الاعتماد التقريبي ؛

التنبؤ للأمام (للخلف) في نالوحدات (تحدد هذه المعلمة عدد الوحدات التي تحتاج إلى الأمام (للخلف) لتوسيع خط الاتجاه) ؛

ما إذا كان سيتم إظهار نقطة تقاطع المنحنى مع الخط ص = ثوابت;

ما إذا كنت تريد إظهار وظيفة التقريب على الرسم التخطيطي أم لا (اعرض المعادلة على معلمة الرسم التخطيطي) ؛

ما إذا كان سيتم وضع قيمة الانحراف المعياري على الرسم التخطيطي أم لا (تضع المعلمة قيمة موثوقية التقريب على الرسم التخطيطي).

دعونا نختار كثير الحدود من الدرجة الثانية كاعتماد تقريبي (الشكل 11) ونشتق معادلة تصف كثير الحدود على الرسم البياني (الشكل 12). يظهر الرسم البياني الناتج في الشكل. 13.

وبالمثل ، مع خطوط الاتجاهيمكنك اختيار معلمات مثل هذه التبعيات مثل

خطي ذ=أ ∙ س+ب,

لوغاريتمي ذ=أ ln(x)+ب,

متسارع ذ=أ ∙ eb,

قوة ذ=أ س ب,

متعدد الحدود ذ=أ ∙ س 2 +ب ∙ س+ج, ذ=أ ∙ س 3 +ب ∙ س 2 +ج ∙ س + دوما إلى ذلك ، حتى بما في ذلك كثير الحدود من الدرجة السادسة ،

التصفية الخطية.

4.3 باستخدام الفاصل

من الأهمية بمكان تنفيذ اختيار المعلمات في MS Excel بطريقة المربعات الصغرى باستخدام كتلة القرار. تسمح لك هذه التقنية باختيار معلمات وظيفة من أي نوع. لنفكر في هذا الاحتمال في مثال المشكلة التالية.

مثال 3. نتيجة للتجربة ، الاعتماد z (t) المعروض في الجدول

حدد معاملات التبعية Z (t) = عند 4 + Bt 3 + Ct 2 + Dt + K.بطريقة المربعات الصغرى.

هذه المسألة تعادل مشكلة إيجاد الحد الأدنى لدالة مكونة من خمسة متغيرات

ضع في اعتبارك عملية حل مشكلة التحسين (الشكل 14).

دع القيم أ, في, مع, دو لمخزنة في الخلايا A7: E7. احسب القيم النظرية للدالة ض(ر)=At4 + Bt3 + Ct2 + Dt + K.على سبيل المعطى ر(B2: J2). للقيام بذلك ، في الخلية ب 4أدخل قيمة الوظيفة في النقطة الأولى (الخلية B2):

B4 = $ A $ 7 * B2 ^ 4 + $ B $ 7 * B2 ^ 3 + $ C $ 7 * B2 ^ 2 + $ D $ 7 * B2 + $ E $ 7.

انسخ هذه الصيغة في النطاق С4: J4واحصل على القيمة المتوقعة للدالة عند النقاط ، التي يتم تخزين أحجارها في الخلايا B2: J2.

إلى الخلية ب 5نقدم صيغة تحسب مربع الفرق بين النقاط التجريبية والمحسوبة:

B5 = (B4-B3) ^ 2 ،

وانسخه إلى النطاق С5: J5. في الخلية F7سنقوم بتخزين الخطأ التربيعي الإجمالي (10). للقيام بذلك ، نقدم الصيغة:

F7 = SUM (B5: J5).

لنستخدم الأمر Service® ابحث عن حلوحل مشكلة التحسين دون قيود. املأ حقول الإدخال المناسبة في مربع الحوار الموضح في الشكل. 14 واضغط على الزر يجري. إذا تم العثور على حل ، تظهر النافذة في الشكل. 15.

ستكون نتيجة كتلة القرار هي الإخراج إلى الخلايا A7: E7قيمه المعاملالمهام ض(ر)=At4 + Bt3 + Ct2 + Dt + K.. في الخلايا B4: J4نحن نحصل قيمة الوظيفة المتوقعةفي نقاط البداية. في الخلية F7سيتم الاحتفاظ مجموع الخطأ التربيعي.

يمكنك عرض النقاط التجريبية والخط المناسب في نفس منطقة الرسم إذا قمت بتحديد النطاق B2: J4، يتصل معالج الرسم البياني، ثم تنسيق مظهر الرسوم البيانية الناتجة.

أرز. يعرض 17 ورقة عمل MS Excel بعد إجراء الحسابات.

5. المراجع

1. Alekseev E.R. ، Chesnokova O.V. ، حل مشاكل الرياضيات الحسابية في الحزم Mathcad12 ، MATLAB7 ، Maple9. - مطبعة إن تي ، ٢٠٠٦. - ٥٩٦ ثانية. :سوف. - (درس تعليمي)

2. ألكسيف إي آر ، تشيسنوكوفا أو في ، إي. Rudchenko ، Scilab ، حل المشكلات الهندسية والرياضية. –M. ، BINOM ، 2008. – 260 ثانية.

3. I. S. Berezin and N. P. Zhidkov، Methods of Composition، Moscow: Nauka، 1966.

4. Garnaev A.Yu. ، استخدام MS EXCEL و VBA في الاقتصاد والتمويل. - سانت بطرسبرغ: BHV - بطرسبورغ ، 1999. -332 ص.

5. B. P. Demidovich ، I. A. Maron ، and V. Z. Shuvalova ، Numerical Methods of Analysis.-M: Nauka، 1967. –368p.

6. كورن جي ، كورن ت. ، كتيب الرياضيات للعلماء والمهندسين ، 1970 ، 720 ص.

7. أليكسيف إي آر ، تشيسنوكوفا أو في. إرشادات لأداء العمل المخبري في MS EXCEL. للطلاب من جميع التخصصات. دونيتسك ، دونتو ، 2004. 112 ص.

طريقة المربعات الصغرى هي إجراء رياضي لبناء معادلة خطية تتطابق بشكل وثيق مع مجموعة من سلسلتين من الأرقام. الغرض من هذه الطريقة هو تقليل إجمالي الخطأ التربيعي. يحتوي Excel على أدوات يمكن استخدامها لتطبيق هذه الطريقة في العمليات الحسابية. دعونا نرى كيف يتم ذلك.

طريقة المربعات الصغرى (LSM) هي وصف رياضي لاعتماد متغير واحد على آخر. يمكن استخدامه للتنبؤ.

قم بتمكين الوظيفة الإضافية Solver

لاستخدام OLS في Excel ، تحتاج إلى تمكين الوظيفة الإضافية "ابحث عن حل"، والذي يتم تعطيله افتراضيًا.

الآن الوظيفة إيجاد حلفي Excel ، وتظهر أدواته على الشريط.

شروط المشكلة

دعونا نصف تطبيق LSM في مثال محدد. لدينا صفان من الأرقام x و ذ التسلسل الذي يظهر في الصورة أدناه.

يمكن وصف هذا الاعتماد بدقة من خلال الوظيفة:

في نفس الوقت ، من المعروف أن س = 0 ذمتساوية أيضا 0 . لذلك ، يمكن وصف هذه المعادلة بالتبعية ص = nx .

علينا إيجاد الحد الأدنى لمربعات الفرق.

حل

دعنا ننتقل إلى وصف التطبيق المباشر للطريقة.

كما ترى ، فإن تطبيق طريقة المربعات الصغرى هو إجراء رياضي معقد نوعًا ما. لقد أظهرنا ذلك في العمل بأبسط مثال ، ولكن هناك حالات أكثر تعقيدًا. ومع ذلك ، تم تصميم مجموعة أدوات Microsoft Excel لتبسيط العمليات الحسابية قدر الإمكان.