LSM لدالة من متغيرين. تقريب البيانات التجريبية. طريقة المربعات الصغرى. التنفيذ العملي لـ LSM للاعتماد الخطي على آلة حاسبة غير قابلة للبرمجة

مثال.

بيانات تجريبية على قيم المتغيرات Xو فيترد في الجدول.

نتيجة محاذاة الوظيفة

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

جوهر طريقة المربعات الصغرى (LSM).

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

وبالتالي ، يتم تقليل حل المثال إلى إيجاد الحد الأقصى لدالة من متغيرين.

اشتقاق الصيغ لإيجاد المعاملات.

نظام من معادلتين مع مجهولين يتم تجميعها وحلها. إيجاد مشتقات جزئية لدالة فيما يتعلق بالمتغيرات أو ب، فنحن نساوي هذه المشتقات بصفر.

نقوم بحل نظام المعادلات الناتج بأي طريقة (على سبيل المثال طريقة الاستبدالأو) والحصول على الصيغ لإيجاد المعاملات باستخدام طريقة المربعات الصغرى (LSM).

مع البيانات أو بوظيفة يأخذ أصغر قيمة. يتم تقديم الدليل على هذه الحقيقة.

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

حان الوقت لتذكر المثال الأصلي.

حل.

في مثالنا ن = 5. نقوم بملء الجدول لتسهيل حساب المبالغ التي تم تضمينها في صيغ المعاملات المطلوبة.

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

يتم الحصول على القيم الموجودة في الصف الخامس من الجدول بتربيع قيم الصف الثاني لكل رقم أنا.

قيم العمود الأخير في الجدول هي مجاميع القيم عبر الصفوف.

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

لذلك، ص = 0.165 س + 2.184هو الخط المستقيم التقريبي المطلوب.

يبقى معرفة أي من الخطوط ص = 0.165 س + 2.184أو تقترب بشكل أفضل من البيانات الأصلية ، أي لعمل تقدير باستخدام طريقة المربعات الصغرى.

تقدير خطأ طريقة المربعات الصغرى.

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

منذ ذلك الحين الخط ص = 0.165 س + 2.184تقرب البيانات الأصلية بشكل أفضل.

رسم توضيحي لطريقة المربعات الصغرى (LSM).

كل شيء يبدو رائعا على الرسوم البيانية. الخط الأحمر هو الخط الموجود ص = 0.165 س + 2.184، الخط الأزرق ، النقاط الوردية هي البيانات الأصلية.

ما هذا ، ولماذا كل هذه التقريبات؟

أنا شخصياً أستخدمه لحل مشاكل تنعيم البيانات والاستيفاء والاستقراء (في المثال الأصلي ، قد يُطلب منك العثور على قيمة القيمة المرصودة ذفي س = 3او متى س = 6وفقًا لطريقة MNC). لكننا سنتحدث أكثر عن هذا لاحقًا في قسم آخر من الموقع.

دليل.

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

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

بيان المشكلة في مثال محدد

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

لذلك ، لنفترض أن 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.

درجة الدقة

لأي تقريب ، تقييم دقتها له أهمية خاصة. قم بالإشارة بواسطة e i إلى الفرق (الانحراف) بين القيم الوظيفية والتجريبية للنقطة x i ، أي e i = y i - f (x i).

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

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

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

في Excel ، كما تعلم ، توجد وظيفة تجميع تلقائي مضمنة تسمح لك بحساب قيم جميع القيم الموجودة في النطاق المحدد. وبالتالي ، لن يمنعنا أي شيء من حساب قيمة التعبير (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

في التدوين الرياضي ، يبدو هذا كما يلي:

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

وبالتالي ، فإن مهمة إيجاد خط مستقيم يصف على أفضل وجه علاقة محددة بين X و Y تعني حساب الحد الأدنى لوظيفة من متغيرين:

يتطلب هذا معادلة صفر مشتقات جزئية فيما يتعلق بالمتغيرات الجديدة أ و ب ، وحل نظام بدائي يتكون من معادلتين مع 2 مجهول من النموذج:

بعد التحولات البسيطة ، بما في ذلك القسمة على 2 ومعالجة المبالغ ، نحصل على:

لحلها ، على سبيل المثال ، بطريقة كرامر ، نحصل على نقطة ثابتة مع معاملات معينة a * و b *. هذا هو الحد الأدنى ، أي للتنبؤ بحجم دوران المتجر لمنطقة معينة ، فإن الخط المستقيم y = a * x + b * مناسب ، وهو نموذج انحدار للمثال المعني. بالطبع ، لن يسمح لك بالعثور على النتيجة الدقيقة ، لكنه سيساعدك في الحصول على فكرة عما إذا كان شراء متجر بالائتمان لمنطقة معينة سيؤتي ثماره.

كيفية تنفيذ طريقة المربعات الصغرى في Excel

لدى Excel وظيفة لحساب قيمة المربعات الصغرى. لها الشكل التالي: TREND (قيم Y المعروفة ؛ قيم X المعروفة ؛ قيم X الجديدة ؛ ثابت). دعنا نطبق صيغة حساب OLS في Excel على جدولنا.

للقيام بذلك ، في الخلية التي يجب أن يتم فيها عرض نتيجة الحساب باستخدام طريقة المربعات الصغرى في Excel ، أدخل علامة "=" وحدد وظيفة "TREND". في النافذة التي تفتح ، املأ الحقول المناسبة ، مع تحديد:

نطاق القيم المعروفة لـ Y (في هذه الحالة بيانات دوران) ؛
النطاق x 1 ، ... x n ، أي حجم مساحة البيع بالتجزئة ؛
والقيم المعروفة وغير المعروفة لـ x ، والتي تحتاج إلى معرفة حجم دورانها (للحصول على معلومات حول موقعها في ورقة العمل ، انظر أدناه).

بالإضافة إلى ذلك ، هناك متغير منطقي "Const" في الصيغة. إذا أدخلت 1 في الحقل المقابل له ، فهذا يعني أنه يجب إجراء الحسابات ، على افتراض أن ب \ u003d 0.

إذا كنت بحاجة إلى معرفة التوقعات لأكثر من قيمة x واحدة ، فبعد إدخال الصيغة ، يجب ألا تضغط على "Enter" ، ولكن عليك كتابة المجموعة "Shift" + "Control" + "Enter" ("Enter" ) على لوحة المفاتيح.

بعض الملامح

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

إذا قمت بوضع نطاق القيم المعروفة للمتغير y في صف أو عمود واحد ، فسيتم إدراك كل صف (عمود) بقيم x المعروفة بواسطة البرنامج كمتغير منفصل.
إذا لم يتم تحديد النطاق مع x المعروف في نافذة TREND ، فعند استخدام الوظيفة في Excel ، سيعتبرها البرنامج كمصفوفة تتكون من أعداد صحيحة ، وعددها يتوافق مع النطاق مع القيم المحددة من المتغير y.
لإخراج صفيف من القيم "المتوقعة" ، يجب إدخال تعبير الاتجاه كصيغة صفيف.
إذا لم يتم تحديد قيم x جديدة ، فإن دالة TREND تعتبرها مساوية للقيم المعروفة. إذا لم يتم تحديدها ، فسيتم اعتبار المصفوفة 1 كوسيطة ؛ 2 ؛ 3 ؛ 4 ؛ ... ، الذي يتناسب مع النطاق مع المعلمات المعطاة بالفعل y.
يجب أن يحتوي النطاق الذي يحتوي على قيم x الجديدة على نفس أو أكثر من الصفوف أو الأعمدة مثل النطاق الذي يحتوي على قيم y المحددة. بمعنى آخر ، يجب أن تكون متناسبة مع المتغيرات المستقلة.
يمكن أن تحتوي المصفوفة ذات قيم x المعروفة على متغيرات متعددة. ومع ذلك ، إذا كنا نتحدث عن واحد فقط ، فمن الضروري أن تكون النطاقات ذات القيم المعطاة لـ x و y متناسبة. في حالة وجود العديد من المتغيرات ، من الضروري احتواء النطاق مع قيم y المقدمة في عمود واحد أو صف واحد.

دالة FORECAST

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

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

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

دعونا نفكر في إجراء تقدير معلمات نموذج الاقتصاد القياسي الخطي باستخدام طريقة المربعات الصغرى بمزيد من التفصيل. يمكن تمثيل هذا النموذج بشكل عام بالمعادلة (1.2):

y t = a 0 + a 1 x 1 t + ... + a n x nt + t.

البيانات الأولية عند تقدير المعلمات a 0 ، a 1 ، ... ، a n هي متجه قيم المتغير التابع ذ= (y 1، y 2، ...، y T) "ومصفوفة قيم المتغيرات المستقلة

حيث يتوافق العمود الأول المكون من الآحاد مع معامل النموذج.

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

أمثلة على حل المشكلات بطريقة المربعات الصغرى

مثال 2.1.تمتلك المؤسسة التجارية شبكة تتكون من 12 متجرًا ، وترد معلومات عن أنشطتها في الجدول. 2.1.

تود إدارة الشركة معرفة كيف يعتمد الحجم السنوي على منطقة المبيعات بالمخزن.

الجدول 2.1

رقم المحل	حجم الأعمال السنوي ، مليون روبل	منطقة التجارة ألف م 2

حل المربعات الصغرى.دعونا نحدد - حجم المبيعات السنوي لمتجر -th ، مليون روبل ؛ - مساحة البيع بالمخزن - الالف م 2.

الشكل 2.1. مخطط مبعثر للمثال 2.1

لتحديد شكل العلاقة الوظيفية بين المتغيرات وبناء مخطط مبعثر (الشكل 2.1).

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

يتم عرض معلومات لمزيد من العمليات الحسابية في الجدول. 2.2. باستخدام طريقة المربعات الصغرى ، قمنا بتقدير معاملات النموذج الاقتصادي القياسي الخطي أحادي العامل

الجدول 2.2

هكذا،

لذلك ، مع زيادة مساحة التجارة بمقدار 1000 متر مربع ، مع تساوي الأشياء الأخرى ، يزداد متوسط حجم التداول السنوي بمقدار 67.8871 مليون روبل.

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

الجدول 2.3

حل.دلالة - متوسط عدد زوار المتجر في اليوم ، ألف شخص.

لتحديد شكل العلاقة الوظيفية بين المتغيرات وبناء مخطط مبعثر (الشكل 2.2).

استنادًا إلى الرسم التخطيطي المبعثر ، يمكننا أن نستنتج أن معدل الدوران السنوي يرتبط ارتباطًا إيجابيًا بمتوسط عدد الزوار يوميًا (أي أن y ستزداد مع نمو). شكل الاعتماد الوظيفي خطي.

أرز. 2.2. مخطط مبعثر على سبيل المثال 2.2

الجدول 2.4

بشكل عام ، من الضروري تحديد معلمات نموذج الاقتصاد القياسي ذي العاملين

y t \ u003d a 0 + a 1 x 1 t + a 2 x 2 t + t

يتم عرض المعلومات المطلوبة لمزيد من العمليات الحسابية في الجدول. 2.4

دعونا نقدر معلمات نموذج اقتصادي قياسي خطي من عاملين باستخدام طريقة المربعات الصغرى.

هكذا،

يُظهر تقييم المعامل = 61.6583 أنه ، مع تساوي الأشياء الأخرى ، مع زيادة منطقة التداول بمقدار 1000 م 2 ، سيزداد حجم التداول السنوي بمعدل 61.6583 مليون روبل.

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

طريقة التربيع الصغرى ( MNK ، OLS ، المربعات الصغرى العادية) - إحدى الطرق الأساسية لتحليل الانحدار لتقدير المعلمات غير المعروفة لنماذج الانحدار من بيانات العينة. تعتمد الطريقة على تقليل مجموع مربعات بقايا الانحدار.

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

جوهر الشركات متعددة الجنسيات

دع بعض النماذج (البارامترية) للاعتماد الاحتمالي (الانحدار) بين المتغير (الموضح) ذوالعديد من العوامل (المتغيرات التفسيرية) x

أين متجه معلمات النموذج غير المعروفة

- خطأ نموذج عشوائي.

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

تعتمد قيمة القيم المتبقية على قيم المعلمات ب.

يتمثل جوهر LSM (عادي ، كلاسيكي) في إيجاد مثل هذه المعلمات ب التي يكون مجموع مربعات القيم المتبقية (eng. المجموع المتبقي للمربعات) سيكون ضئيلاً:

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

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

LSM في حالة النموذج الخطي

دع تبعية الانحدار تكون خطية:

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

ثم سيكون متجه تقديرات المتغير الموضح ومتجه بقايا الانحدار مساوياً لـ

وفقًا لذلك ، سيكون مجموع مربعات قيم الانحدار المتبقية مساويًا لـ

عند التفريق بين هذه الوظيفة فيما يتعلق بمتجه المعلمة ومعادلة المشتقات بالصفر ، نحصل على نظام من المعادلات (في شكل مصفوفة):

يعطي حل نظام المعادلات هذا الصيغة العامة لتقديرات المربعات الصغرى للنموذج الخطي:

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

خاصية مهمة لتقديرات LLS للنماذج مع ثابت- يمر خط الانحدار المركب عبر مركز ثقل بيانات العينة ، أي أن المساواة تتحقق:

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

مثال: الانحدار البسيط (الزوجي)

في حالة الانحدار الخطي المقترن ، يتم تبسيط معادلات الحساب (يمكنك الاستغناء عن جبر المصفوفة):

خصائص تقديرات OLS

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

التوقع الرياضي للأخطاء العشوائية هو صفر ، و
العوامل والأخطاء العشوائية هي متغيرات عشوائية مستقلة.

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

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

يمكن صياغة هذه الافتراضات لمصفوفة التغاير لمتجه الخطأ العشوائي

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

المربعات الصغرى المعممة

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

ثبت (نظرية Aitken) أنه بالنسبة لنموذج الانحدار الخطي المعمم (حيث لا يتم فرض قيود على مصفوفة التباين المشترك للأخطاء العشوائية) ، فإن الأكثر فعالية (في فئة التقديرات الخطية غير المتحيزة) هي تقديرات لما يسمى. OLS المعمم (OMNK ، GLS - المربعات الصغرى المعممة)- طريقة LS بمصفوفة وزن تساوي مصفوفة التغاير العكسي للأخطاء العشوائية:.

يمكن إثبات أن صيغة تقديرات GLS لمعلمات النموذج الخطي لها الشكل

مصفوفة التغاير لهذه التقديرات ، على التوالي ، ستكون مساوية لـ

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

المربعات الصغرى المرجحة

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

بعض الحالات الخاصة لتطبيق LSM في الممارسة

تقريب خطي

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

طاولة. نتائج القياس.

رقم القياس
1
2
3
4
5
6

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

كان ضئيلاً

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

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

قصة

حتى بداية القرن التاسع عشر. لم يكن لدى العلماء قواعد معينة لحل نظام معادلات يكون فيه عدد المجهول أقل من عدد المعادلات ؛ حتى ذلك الوقت ، تم استخدام طرق معينة ، اعتمادًا على نوع المعادلات وبراعة الآلات الحاسبة ، وبالتالي توصلت الآلات الحاسبة المختلفة ، بدءًا من نفس بيانات الملاحظة ، إلى استنتاجات مختلفة. يرجع الفضل إلى Gauss (1795) في أول تطبيق لهذه الطريقة ، واكتشفها Legendre (1805) بشكل مستقل ونشرها تحت اسمها الحديث (fr. ميثود دي مويندر المحاجر ). ربط لابلاس الطريقة بنظرية الاحتمالية ، واعتبر عالم الرياضيات الأمريكي Adrain (1808) تطبيقاتها الاحتمالية. هذه الطريقة منتشرة وتم تحسينها من خلال إجراء مزيد من البحث بواسطة Encke و Bessel و Hansen وغيرهم.

الاستخدام البديل للشركات متعددة الجنسيات

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

أحد التطبيقات هو "حل" أنظمة المعادلات الخطية التي يكون فيها عدد المعادلات أكبر من عدد المتغيرات

حيث المصفوفة ليست مربعة بل مستطيلة.

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

مثال.

بيانات تجريبية على قيم المتغيرات Xو فيترد في الجدول.

نتيجة محاذاة الوظيفة

جوهر طريقة المربعات الصغرى (LSM).

وبالتالي ، يتم تقليل حل المثال إلى إيجاد الحد الأقصى لدالة من متغيرين.

اشتقاق الصيغ لإيجاد المعاملات.

نظام من معادلتين مع مجهولين يتم تجميعها وحلها. إيجاد المشتقات الجزئية للدوال بالمتغيرات أو ب، فنحن نساوي هذه المشتقات بصفر.

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

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

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

حان الوقت لتذكر المثال الأصلي.

حل.

في مثالنا ن = 5. نقوم بملء الجدول لتسهيل حساب المبالغ التي تم تضمينها في صيغ المعاملات المطلوبة.

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

يتم الحصول على القيم الموجودة في الصف الخامس من الجدول بتربيع قيم الصف الثاني لكل رقم أنا.

قيم العمود الأخير في الجدول هي مجاميع القيم عبر الصفوف.

لذلك، ص = 0.165 س + 2.184هو الخط المستقيم التقريبي المطلوب.

تقدير خطأ طريقة المربعات الصغرى.

منذ ذلك الحين الخط ص = 0.165 س + 2.184تقرب البيانات الأصلية بشكل أفضل.

رسم توضيحي لطريقة المربعات الصغرى (LSM).

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

غالبًا ما تنشأ مشاكل تقريب الوظائف من هذا النوع:

عند إنشاء صيغ تقريبية لحساب قيم الكميات المميزة للعملية قيد الدراسة وفقًا للبيانات المجدولة التي تم الحصول عليها نتيجة للتجربة ؛

في التكامل العددي ، والتفاضل ، وحل المعادلات التفاضلية ، وما إلى ذلك ؛

إذا كان من الضروري حساب قيم الوظائف عند نقاط وسيطة من الفترة المدروسة ؛

عند تحديد قيم الكميات المميزة للعملية خارج الفترة الزمنية قيد النظر ، على وجه الخصوص ، عند التنبؤ.

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

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

هناك خياران لبناء الانحدارات في Excel.

إضافة الانحدارات المحددة (خطوط الاتجاه) إلى مخطط مبني على أساس جدول البيانات لخاصية العملية المدروسة (متاح فقط إذا تم إنشاء مخطط) ؛

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

إضافة خطوط الاتجاه إلى الرسم البياني

بالنسبة لجدول بيانات يصف عملية معينة ويمثلها رسم تخطيطي ، يحتوي Excel على أداة تحليل انحدار فعالة تسمح لك بما يلي:

البناء على أساس طريقة المربعات الصغرى وإضافة خمسة أنواع من الانحدارات إلى الرسم التخطيطي والتي تمثل العملية قيد الدراسة بدرجات متفاوتة من الدقة ؛

إضافة معادلة الانحدار المركب إلى الرسم التخطيطي ؛

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

استنادًا إلى بيانات المخطط ، يسمح لك Excel بالحصول على أنواع الانحدار الخطية ، متعددة الحدود ، اللوغاريتمية ، الأسية ، الأسية ، والتي يتم توفيرها بواسطة المعادلة:

ص = ص (س)

حيث x هو متغير مستقل ، والذي غالبًا ما يأخذ قيم سلسلة من الأرقام الطبيعية (1 ؛ 2 ؛ 3 ؛ ...) وينتج ، على سبيل المثال ، عدًا تنازليًا لوقت العملية قيد الدراسة (الخصائص) .

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

ص = م س + ب

حيث m هو ظل منحدر الانحدار الخطي إلى المحور x ؛ ب - تنسيق نقطة تقاطع الانحدار الخطي مع المحور الصادي.

2 . يعتبر خط الاتجاه متعدد الحدود مفيدًا في وصف الخصائص التي لها العديد من الحدود القصوى المتميزة (الارتفاعات والانخفاضات). يتم تحديد اختيار درجة كثير الحدود من خلال عدد القيم القصوى للخاصية قيد الدراسة. وبالتالي ، يمكن أن تصف كثير الحدود من الدرجة الثانية بشكل جيد عملية لها حد أقصى أو أدنى واحد فقط ؛ متعدد الحدود من الدرجة الثالثة - لا يزيد عن اثنين من القيم القصوى ؛ متعدد الحدود من الدرجة الرابعة - لا يزيد عن ثلاث نقاط قصوى ، إلخ.

في هذه الحالة ، يتم إنشاء خط الاتجاه وفقًا للمعادلة:

ص = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

حيث المعاملات c0 و c1 و c2 و ... c6 هي ثوابت يتم تحديد قيمها أثناء البناء.

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

y = c ln (x) + b

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

تم بناؤه وفقًا للمعادلة:

ص = cxb

حيث المعامِلات ب ، ج ثوابت.

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

تم بناؤه وفقًا للمعادلة:

ص = سيبكس

حيث المعامِلات ب ، ج ثوابت.

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

تحددها الصيغة:

لإضافة خط اتجاه إلى سلسلة بيانات:

قم بتنشيط المخطط المبني على أساس سلسلة البيانات ، أي انقر داخل منطقة المخطط. سيظهر عنصر المخطط في القائمة الرئيسية ؛

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

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

بعد ذلك تحتاج إلى:

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

1 . يسرد الحقل "مبني على السلسلة" جميع سلاسل البيانات في المخطط المعني. لإضافة خط اتجاه إلى سلسلة بيانات معينة ، حدد اسمها في حقل سلسلة مبنية.

إذا لزم الأمر ، بالانتقال إلى علامة التبويب المعلمات (الشكل 2) ، يمكنك تعيين المعلمات التالية لخط الاتجاه:

قم بتغيير اسم خط الاتجاه في اسم حقل المنحنى التقريبي (المتجانس).

تعيين عدد الفترات (للأمام أو للخلف) للتنبؤ في حقل التنبؤ ؛

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

عرض قيمة موثوقية التقريب R2 في منطقة الرسم التخطيطي ، والتي يجب أن تقوم بتمكين مربع الاختيار لها وضع قيمة موثوقية التقريب (R ^ 2) على الرسم التخطيطي ؛

تعيين نقطة تقاطع خط الاتجاه مع المحور ص ، والتي يجب أن تقوم بتمكين مربع الاختيار لتقاطع المنحنى مع المحور ص عند نقطة ما ؛

انقر فوق الزر "موافق" لإغلاق مربع الحوار.

هناك ثلاث طرق لبدء تحرير خط اتجاه تم إنشاؤه بالفعل:

استخدم أمر خط الاتجاه المحدد من قائمة التنسيق ، بعد تحديد خط الاتجاه ؛

حدد أمر تنسيق خط الاتجاه من قائمة السياق ، والتي يتم استدعاؤها بالنقر بزر الماوس الأيمن فوق خط الاتجاه ؛

عن طريق النقر المزدوج على خط الاتجاه.

سيظهر مربع حوار تنسيق خط الاتجاه على الشاشة (الشكل 3) ، ويحتوي على ثلاث علامات تبويب: العرض والنوع والمعلمات ومحتويات الأخيرين تتطابق تمامًا مع علامات التبويب المماثلة في مربع حوار خط الاتجاه (الشكل 1-2) ). في علامة التبويب عرض ، يمكنك تعيين نوع الخط ولونه وسمكه.

لحذف خط اتجاه تم إنشاؤه بالفعل ، حدد خط الاتجاه المراد حذفه واضغط على مفتاح Delete.

مزايا أداة تحليل الانحدار المدروسة هي:

السهولة النسبية لرسم خط اتجاه على المخططات دون إنشاء جدول بيانات له ؛

قائمة واسعة إلى حد ما لأنواع خطوط الاتجاه المقترحة ، وتشمل هذه القائمة أنواع الانحدار الأكثر استخدامًا ؛

إمكانية التنبؤ بسلوك العملية قيد الدراسة لعدد تعسفي (في حدود الحس السليم) من الخطوات للأمام ، وكذلك للخلف ؛

إمكانية الحصول على معادلة خط الاتجاه في شكل تحليلي ؛

إمكانية ، إذا لزم الأمر ، للحصول على تقييم لموثوقية التقريب.

تشمل العيوب النقاط التالية:

يتم تنفيذ إنشاء خط الاتجاه فقط إذا كان هناك مخطط مبني على سلسلة من البيانات ؛

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

في تقارير PivotChart ، عندما تقوم بتغيير طريقة عرض المخطط أو تقرير PivotTable المرتبط ، لا يتم الاحتفاظ بخطوط الاتجاه الموجودة ، مما يعني أنه قبل رسم خطوط الاتجاه أو تنسيق تقرير PivotChart ، يجب التأكد من أن تخطيط التقرير يلبي متطلباتك.

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

لا يمكنك إضافة خطوط الاتجاه إلى سلسلة البيانات في المخططات ثلاثية الأبعاد والقياسية والرادارية والدائرية والدائرية المجوفة.

استخدام وظائف Excel المضمنة

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

يحتوي Excel على عدة وظائف لبناء الانحدار الخطي ، ولا سيما:

اتجاه؛

المنحدر والقطع.

بالإضافة إلى العديد من الوظائف لإنشاء خط اتجاه أسي ، وعلى وجه الخصوص:

LGRFP تقريبًا.

وتجدر الإشارة إلى أن تقنيات إنشاء الانحدارات باستخدام وظائف TREND و GROWTH هي نفسها عمليًا. يمكن قول الشيء نفسه عن زوج الوظائف LINEST و LGRFPRIBL. بالنسبة إلى هذه الوظائف الأربع ، عند إنشاء جدول قيم ، يتم استخدام ميزات Excel مثل صيغ الصفيف ، مما يؤدي إلى تشويش عملية بناء الانحدارات إلى حد ما. نلاحظ أيضًا أن بناء الانحدار الخطي ، في رأينا ، أسهل في التنفيذ باستخدام الدالتين SLOPE و INTERCEPT ، حيث يحدد أولهما ميل الانحدار الخطي ، والثاني يحدد المقطع المقطوع بالانحدار على المحور ص.

مزايا أداة الدوال المضمنة لتحليل الانحدار هي:

عملية بسيطة إلى حد ما من نفس النوع من تكوين سلاسل البيانات للخاصية قيد الدراسة لجميع الوظائف الإحصائية المضمنة التي تحدد خطوط الاتجاه ؛

تقنية قياسية لإنشاء خطوط الاتجاه بناءً على سلسلة البيانات المتولدة ؛

القدرة على التنبؤ بسلوك العملية قيد الدراسة لعدد الخطوات المطلوبة للأمام أو للخلف.

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

وتجدر الإشارة إلى أن المؤلفين لم يحددوا هدف المقالة لتقديم مسار تحليل الانحدار بدرجات متفاوتة من الاكتمال. وتتمثل مهمتها الرئيسية في إظهار قدرات حزمة Excel في حل مشكلات التقريب باستخدام أمثلة محددة ؛ توضيح الأدوات الفعالة التي يمتلكها Excel لبناء الانحدارات والتنبؤ ؛ وضح كيف يمكن حل هذه المشكلات بسهولة نسبيًا حتى من قبل مستخدم ليس لديه معرفة عميقة بتحليل الانحدار.

أمثلة على حل مشاكل محددة

ضع في اعتبارك حل مشكلات معينة باستخدام الأدوات المدرجة في حزمة Excel.

مهمة 1

مع جدول بيانات عن أرباح شركة النقل بالسيارات 1995-2002. عليك أن تفعل ما يلي.

بناء مخطط.

أضف خطوط اتجاه خطية ومتعددة الحدود (تربيعية وتكعيبية) إلى المخطط.

باستخدام معادلات خط الاتجاه ، احصل على بيانات مجدولة عن ربح المؤسسة لكل خط اتجاه للفترة 1995-2004.

قم بعمل توقعات للأرباح للمؤسسة لعامي 2003 و 2004.

حل المشكلة

في نطاق الخلايا A4: C11 من ورقة عمل Excel ، ندخل ورقة العمل الموضحة في الشكل. 4.

بعد تحديد نطاق الخلايا B4: C11 ، نقوم ببناء مخطط.

نقوم بتنشيط الرسم البياني المُنشأ وباستخدام الطريقة الموضحة أعلاه ، بعد تحديد نوع خط الاتجاه في مربع حوار خط الاتجاه (انظر الشكل 1) ، نضيف خطوط الاتجاه الخطية والتربيعية والتكعيبية بالتناوب إلى المخطط. في مربع الحوار نفسه ، افتح علامة التبويب المعلمات (انظر الشكل 2) ، في اسم حقل المنحنى التقريبي (المتجانس) ، أدخل اسم الاتجاه المراد إضافته ، وفي التنبؤ إلى الأمام لـ: حقل الفترات ، قم بتعيين القيمة 2 ، حيث أنه من المخطط وضع توقعات للأرباح لمدة عامين مقبلين. لعرض معادلة الانحدار وقيمة موثوقية التقريب R2 في منطقة الرسم التخطيطي ، قم بتمكين خانات الاختيار إظهار المعادلة على الشاشة ووضع قيمة موثوقية التقريب (R ^ 2) على الرسم التخطيطي. لتحسين الإدراك البصري ، نقوم بتغيير نوع ولون وسمك خطوط الاتجاه المرسومة ، والتي نستخدم لها علامة التبويب عرض في مربع الحوار تنسيق خط الاتجاه (انظر الشكل 3). يظهر الرسم البياني الناتج مع خطوط الاتجاه المضافة في الشكل. 5.

للحصول على بيانات مجدولة عن ربح المؤسسة لكل خط اتجاه للفترة 1995-2004. دعنا نستخدم معادلات خطوط الاتجاه الواردة في الشكل. 5. للقيام بذلك ، في خلايا النطاق D3: F3 ، أدخل معلومات نصية حول نوع خط الاتجاه المحدد: الاتجاه الخطي ، الاتجاه التربيعي ، الاتجاه التكعيبي. بعد ذلك ، أدخل صيغة الانحدار الخطي في الخلية D4 ، وباستخدام علامة التعبئة ، انسخ هذه الصيغة مع المراجع النسبية إلى نطاق الخلايا D5: D13. تجدر الإشارة إلى أن كل خلية تحتوي على صيغة انحدار خطية من نطاق الخلايا D4: D13 لها خلية مقابلة من النطاق A4: A13 كوسيطة. وبالمثل ، بالنسبة للانحدار التربيعي ، يتم تعبئة نطاق الخلايا E4: E13 ، وبالنسبة للانحدار التكعيبي ، يتم تعبئة نطاق الخلايا F4: F13. وبالتالي ، تم عمل توقع لأرباح المؤسسة لعامي 2003 و 2004. مع ثلاثة اتجاهات. يظهر جدول القيم الناتج في الشكل. 6.

المهمة 2

بناء مخطط.

أضف خطوط الاتجاه اللوغاريتمي والأسي والأسي إلى الرسم البياني.

اشتق معادلات خطوط الاتجاه التي تم الحصول عليها ، وكذلك قيم موثوقية التقريب R2 لكل منها.

باستخدام معادلات خط الاتجاه ، احصل على بيانات جدولية عن ربح المؤسسة لكل خط اتجاه للفترة 1995-2002.

قم بعمل توقع ربح للأعمال لعامي 2003 و 2004 باستخدام خطوط الاتجاه هذه.

حل المشكلة

باتباع المنهجية الواردة في حل المشكلة 1 ، نحصل على رسم تخطيطي مع إضافة خطوط الاتجاه اللوغاريتمي والأسي والأسي (الشكل 7). علاوة على ذلك ، باستخدام معادلات خط الاتجاه التي تم الحصول عليها ، نقوم بملء جدول القيم لأرباح المؤسسة ، بما في ذلك القيم المتوقعة لعامي 2003 و 2004. (الشكل 8).

على التين. 5 والتين. يمكن ملاحظة أن النموذج ذو الاتجاه اللوغاريتمي يتوافق مع أدنى قيمة لموثوقية التقريب

R2 = 0.8659

تتوافق أعلى قيم R2 مع النماذج ذات الاتجاه متعدد الحدود: تربيعي (R2 = 0.9263) وتكعيبي (R2 = 0.933).

المهمة 3

باستخدام جدول بيانات عن أرباح مؤسسة النقل بالسيارات للفترة 1995-2002 ، الواردة في المهمة 1 ، يجب عليك تنفيذ الخطوات التالية.

احصل على سلسلة بيانات لخطوط الاتجاه الخطية والأسية باستخدام دالتي TREND و GROW.

باستخدام دالتي TREND و GROWTH ، قم بعمل توقع للأرباح للمؤسسة لعامي 2003 و 2004.

بالنسبة للبيانات الأولية وسلسلة البيانات المستلمة ، قم بإنشاء رسم تخطيطي.

حل المشكلة

دعنا نستخدم ورقة عمل المهمة 1 (انظر الشكل 4). لنبدأ بوظيفة TREND:

حدد نطاق الخلايا D4: D11 ، والتي يجب ملؤها بقيم دالة TREND المقابلة للبيانات المعروفة عن ربح المؤسسة ؛

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

في مربع الحوار "وسيطات الوظيفة" الذي يظهر ، أدخل نطاق الخلايا C4: C11 في الحقل Known_values_y ؛ في الحقل Known_values_x - نطاق الخلايا B4: B11 ؛

لجعل الصيغة المدخلة صيغة صفيف ، استخدم مجموعة المفاتيح + +.

ستبدو الصيغة التي أدخلناها في شريط الصيغة كما يلي: = (TREND (C4: C11؛ B4: B11)).

نتيجة لذلك ، يتم ملء نطاق الخلايا D4: D11 بالقيم المقابلة لوظيفة TREND (الشكل 9).

لعمل توقع لأرباح الشركة لعامي 2003 و 2004. ضروري:

حدد نطاق الخلايا D12: D13 ، حيث سيتم إدخال القيم التي تنبأت بها الدالة TREND.

قم باستدعاء دالة TREND وفي مربع حوار وسيطات الوظيفة الذي يظهر ، أدخل حقل Known_values_y - نطاق الخلايا C4: C11 ؛ في الحقل Known_values_x - نطاق الخلايا B4: B11 ؛ وفي الحقل New_values_x - نطاق الخلايا B12: B13.

حول هذه الصيغة إلى صيغة مصفوفة باستخدام اختصار لوحة المفاتيح Ctrl + Shift + Enter.

ستبدو الصيغة المدخلة على النحو التالي: = (TREND (C4: C11؛ B4: B11؛ B12: B13)) ، وسيتم ملء نطاق الخلايا D12: D13 بالقيم المتوقعة لوظيفة TREND (انظر الشكل. 9).

وبالمثل ، يتم ملء سلسلة البيانات باستخدام دالة GROWTH ، والتي تُستخدم في تحليل التبعيات غير الخطية وتعمل تمامًا مثل نظيرتها الخطية TREND.

يوضح الشكل 10 الجدول في وضع عرض الصيغة.

للبيانات الأولية وسلسلة البيانات التي تم الحصول عليها ، الرسم البياني الموضح في الشكل. أحد عشر.

المهمة 4

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

الحصول على سلسلة بيانات للانحدار الخطي: استخدام الدالتين SLOPE و INTERCEPT ؛ باستخدام دالة LINEST.

استرجع سلسلة بيانات للانحدار الأسي باستخدام الدالة LYFFPRIB.

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

بالنسبة لسلسلة البيانات الأصلية والمستلمة ، قم بإنشاء رسم تخطيطي.

حل المشكلة

لاحظ أنه ، بخلاف دالات TREND و GROW ، لا تعتبر أي من الوظائف المذكورة أعلاه (SLOPE ، INTERCEPTION ، LINEST ، LGRFPRIB) انحدارات. تلعب هذه الوظائف دورًا مساعدًا فقط ، حيث تحدد معاملات الانحدار الضرورية.

بالنسبة للانحدار الخطي والأسي الذي تم إنشاؤه باستخدام الدالات SLOPE و INTERCEPT و LINEST و LGRFPRIB ، فإن مظهر معادلاتهم معروف دائمًا ، على عكس الانحدار الخطي والأسي المقابل للوظائف TREND و GROWTH.

1 . لنقم ببناء انحدار خطي له المعادلة:

ص = م س + ب

باستخدام الدالتين SLOPE و INTERCEPT ، مع تحديد ميل الانحدار m بواسطة وظيفة SLOPE ، والمصطلح الثابت b - بواسطة دالة INTERCEPT.

للقيام بذلك ، نقوم بالإجراءات التالية:

أدخل الجدول المصدر في نطاق الخلايا A4: B14 ؛

سيتم تحديد قيمة المعلمة m في الخلية C19. حدد من الفئة الإحصائية وظيفة المنحدر ؛ أدخل نطاق الخلايا B4: B14 في حقل known_values_y ونطاق الخلايا A4: A14 في الحقل known_values_x. سيتم إدخال الصيغة في الخلية C19: = ميل (B4: B14 ؛ A4: A14) ؛

باستخدام طريقة مماثلة ، يتم تحديد قيمة المعلمة b في الخلية D19. وسيبدو محتواه كما يلي: = INTERCEPT (B4: B14؛ A4: A14). وبالتالي ، سيتم تخزين قيم المعلمات m و b الضرورية لإنشاء انحدار خطي ، على التوالي ، في الخلايا C19 ، D19 ؛

ثم ندخل صيغة الانحدار الخطي في الخلية C4 بالشكل: = $ C * A4 + $ D. في هذه الصيغة ، تتم كتابة الخلايا C19 و D19 بمراجع مطلقة (يجب ألا يتغير عنوان الخلية مع إمكانية النسخ). يمكن كتابة علامة المرجع المطلق $ إما من لوحة المفاتيح أو باستخدام المفتاح F4 ، بعد وضع المؤشر على عنوان الخلية. باستخدام مقبض التعبئة ، انسخ هذه الصيغة إلى نطاق الخلايا C4: C17. نحصل على سلسلة البيانات المطلوبة (الشكل 12). نظرًا لحقيقة أن عدد الطلبات عدد صحيح ، يجب عليك تعيين تنسيق الأرقام في علامة التبويب رقم في نافذة تنسيق الخلية مع عدد المنازل العشرية إلى 0.

2 . لنقم الآن ببناء انحدار خطي معطى بالمعادلة:

ص = م س + ب

باستخدام دالة LINEST.

لهذا:

أدخل دالة LINEST كصيغة صفيف في نطاق الخلايا C20: D20: = (LINEST (B4: B14؛ A4: A14)). نتيجة لذلك ، نحصل على قيمة المعلمة m في الخلية C20 ، وقيمة المعلمة b في الخلية D20 ؛

أدخل الصيغة في الخلية D4: = $ C * A4 + $ D ؛

انسخ هذه الصيغة باستخدام علامة التعبئة إلى نطاق الخلايا D4: D17 واحصل على سلسلة البيانات المطلوبة.

3 . نبني انحدارًا أسيًا له المعادلة:

بمساعدة وظيفة LGRFPRIBL ، يتم إجراؤها بالمثل:

في نطاق الخلايا C21: D21 ، أدخل الدالة LGRFPRIBL كصيغة صفيف: = (LGRFPRIBL (B4: B14 ؛ A4: A14)). في هذه الحالة ، سيتم تحديد قيمة المعلمة m في الخلية C21 ، وسيتم تحديد قيمة المعلمة b في الخلية D21 ؛

يتم إدخال الصيغة في الخلية E4: = $ D * $ C ^ A4 ؛

باستخدام علامة التعبئة ، يتم نسخ هذه الصيغة إلى نطاق الخلايا E4: E17 ، حيث سيتم تحديد موقع سلسلة البيانات للانحدار الأسي (انظر الشكل 12).

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

قيمة ر 2 مُسَمًّى معامل التحديد.

تتمثل مهمة بناء تبعية الانحدار في العثور على متجه المعامِلات m للنموذج (1) الذي يأخذ فيه المعامل R القيمة القصوى.

لتقييم أهمية R ، يتم استخدام اختبار Fisher's F ، محسوبًا بالصيغة

أين ن- حجم العينة (عدد التجارب) ؛

k هو عدد معاملات النموذج.

إذا تجاوز F بعض القيمة الحرجة للبيانات نو كومستوى الثقة المقبول ، فإن قيمة R تعتبر كبيرة. يتم إعطاء جداول القيم الحرجة لـ F في الكتب المرجعية حول الإحصاء الرياضي.

وبالتالي ، لا يتم تحديد أهمية R من خلال قيمتها فحسب ، بل أيضًا من خلال النسبة بين عدد التجارب وعدد معاملات (معلمات) النموذج. في الواقع ، فإن نسبة الارتباط لـ n = 2 لنموذج خطي بسيط هي 1 (من خلال نقطتين على المستوى ، يمكنك دائمًا رسم خط مستقيم واحد). ومع ذلك ، إذا كانت البيانات التجريبية متغيرات عشوائية ، فيجب الوثوق بهذه القيمة من R بعناية كبيرة. عادة ، من أجل الحصول على R وانحدار موثوق به ، فإنه يهدف إلى ضمان أن عدد التجارب يتجاوز بشكل كبير عدد معاملات النموذج (n> k).

لبناء نموذج انحدار خطي ، يجب عليك:

1) قم بإعداد قائمة n من الصفوف والأعمدة m تحتوي على البيانات التجريبية (العمود الذي يحتوي على قيمة الإخراج صيجب أن يكون إما الأول أو الأخير في القائمة) ؛ على سبيل المثال ، لنأخذ بيانات المهمة السابقة ، بإضافة عمود يسمى "رقم الفترة" ، وترقيم أرقام الفترات من 1 إلى 12. (ستكون هذه هي القيم X)

2) انتقل إلى القائمة البيانات / تحليل البيانات / الانحدار

إذا كان عنصر "تحليل البيانات" في قائمة "الأدوات" مفقودًا ، فيجب عليك الانتقال إلى عنصر "الوظائف الإضافية" في القائمة نفسها وتحديد مربع "حزمة التحليل".

3) في مربع حوار "الانحدار" ، اضبط:

الفاصل الزمني للإدخال Y ؛

فاصل الإدخال X ؛

الفاصل الزمني للإخراج - الخلية اليسرى العلوية للفاصل الزمني الذي سيتم فيه وضع نتائج الحساب (يوصى بوضعها في ورقة عمل جديدة) ؛