طريقة المربعات الصغرى(الشركات المتعددة الجنسيات)

نظام المعادلات الخطية m مع المجهول n له الشكل:

ثلاث حالات ممكنة: م ن. الحالة التي تم فيها النظر في m=n في الفقرات السابقة. عندما م

إذا كان m>n والنظام متسقين، فإن المصفوفة A تحتوي على الأقل على صفوف تعتمد خطيًا على m - n. هنا يمكن الحصول على الحل عن طريق اختيار n أي معادلات مستقلة خطيا (إن وجدت) وتطبيق الصيغة X = A -1 CV، أي تقليل المشكلة إلى مشكلة تم حلها مسبقًا. في هذه الحالة، فإن الحل الناتج سيحقق دائمًا معادلات m - n المتبقية.

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

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

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

عن طريق التقليل من القاعدة الإقليدية

فأس؟ ب؟ > الوقود النووي المشع. (1.2)

تحليل البيانات التجريبية

دعونا نفكر في بعض التجارب التي يتم خلالها في لحظات من الزمن

على سبيل المثال، يتم قياس درجة الحرارة Q(t). دع نتائج القياس يتم تحديدها بواسطة مصفوفة

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

ف(ر) = + + + ... +،

تحديد المعاملات المجهولة، ...، من الاعتبارات التي تحددها القيمة E(,...,,) بالمساواة

تقريب غاوس جبري إكسيل

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

إذا استبدلنا P(t) بالتعبير الخاص بها، نحصل على

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

إذا قمت بإدخال مصفوفة م × ن A = ()، i = 1، 2...، m؛ ي = 1، 2، ...، ن، أين

أنا = 1، 2...، م؛ ي = 1، 2، ...، ن،

ثم تأخذ المساواة المكتوبة الشكل

دعونا نعيد كتابة المساواة المكتوبة بدلالة العمليات على المصفوفات. من خلال تعريف ضرب المصفوفة في العمود، لدينا

بالنسبة للمصفوفة المنقولة، تبدو العلاقة المشابهة هكذا

دعونا نقدم الترميز: سوف نشير إلى المكون i للمتجه Ax وفقًا لمعادلات المصفوفة المكتوبة، سيكون لدينا

في شكل مصفوفة يمكن إعادة كتابة هذه المساواة ك

أ تي × = أ تي ب (1.3)

هنا A هي مصفوفة m×n مستطيلة. وعلاوة على ذلك، في مشاكل تقريب البيانات، كقاعدة عامة، م > ن. تسمى المعادلة (1.3) بالمعادلة العادية.

كان من الممكن منذ البداية، باستخدام القاعدة الإقليدية للمتجهات، كتابة المشكلة في شكل مصفوفة مكافئة:

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

2 ايه تي بي + 2 ايه تي فأس

وبالتالي يجب أن يفي الحل بنظام المعادلات الخطية

(أ ت أ) س = (أ ت ب).

تسمى هذه المعادلات المعادلات العادية. إذا كانت A عبارة عن مصفوفة m×n، فإن A>A - n × n عبارة عن مصفوفة، أي. مصفوفة المعادلة العاديةدائما مصفوفة متماثلة مربعة. علاوة على ذلك، فهي تتمتع بخاصية التحديد الإيجابي بمعنى أن (A>Ax, x) = (Ax, Ax) ؟ 0.

تعليق. في بعض الأحيان يسمى حل معادلة من الشكل (1.3) بحل النظام Ax = B، حيث A عبارة عن مصفوفة مستطيلة m × n (m > n) باستخدام طريقة المربعات الصغرى.

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

الشركات المتعددة الجنسيات في إكسيل

تفترض الخوارزمية أدناه لتطبيق OLS في Excel أن جميع البيانات الأولية معروفة بالفعل. نقوم بضرب طرفي معادلة المصفوفة AЧX=B للنظام الموجود على اليسار بالمصفوفة المنقولة للنظام А Т:

أ تي أكس = أ تي ب

ثم نضرب طرفي المعادلة على اليسار بالمصفوفة (A T A) -1. إذا كانت هذه المصفوفة موجودة، فسيتم تعريف النظام. معتبرا أن

(A T A) -1 *(A T A)=E، حصلنا على ذلك

X=(أ ت أ) -1 أ ت ب.

معادلة المصفوفة الناتجة هي حل لنظام المعادلات الخطية m مع المجهول n لـ m>n.

دعونا نفكر في تطبيق الخوارزمية المذكورة أعلاه باستخدام مثال محدد.

مثال. فليكن من الضروري حل النظام

في Excel، تبدو ورقة الحل في وضع عرض الصيغة لهذه المشكلة كما يلي:

نتائج الحساب:

يقع المتجه X المطلوب في النطاق E11:E12.

عند حل نظام معين من المعادلات الخطية، تم استخدام الدوال التالية:

1. MOBR - يقوم بإرجاع المصفوفة العكسية للمصفوفة المخزنة في المصفوفة.

بناء الجملة: MOBR(صفيف).

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

2. MULTIpult - يُرجع ناتج المصفوفات (يتم تخزين المصفوفات في صفائف). والنتيجة هي مصفوفة بنفس عدد الصفوف مثل array1 ونفس عدد الأعمدة مثل array2.

بناء الجملة: متعددة (array1، array2).

Array1، array2 عبارة عن صفائف قابلة للضرب.

بعد إدخال دالة في الخلية العلوية اليسرى لنطاق الصفيف، حدد الصفيف، بدءًا من الخلية التي تحتوي على الصيغة، واضغط على F2، ثم اضغط على CTRL+SHIFT+ENTER.

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

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

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

تمكين الوظيفة الإضافية Find Solution

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

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

شروط المشكلة

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

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

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

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

حل

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

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

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

بيان المشكلة باستخدام مثال محدد

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

لذا، دع X تكون مساحة البيع بالتجزئة لمتجر بقالة، مقاسة بالمتر المربع، وY هي حجم المبيعات السنوي، مقاسًا بملايين الروبلات.

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

بضع كلمات حول صحة البيانات الأولية المستخدمة للتنبؤ

لنفترض أن لدينا جدولًا تم إنشاؤه باستخدام بيانات عدد n من المتاجر.

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

جوهر الطريقة

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

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

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

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

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

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

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

للقيام بذلك، تحتاج إلى مساواة المشتقات الجزئية بالنسبة للمتغيرين الجديدين a وb بالصفر، وحل نظام بدائي يتكون من معادلتين مع مجهولين من الصيغة:

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

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

كيفية تنفيذ المربعات الصغرى في إكسل

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

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

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

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

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

بعض الملامح

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

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

وظيفة التنبؤ

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

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

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

طريقة المربع الأصغر(إنجليزي) عادي الأقل مربعات , O.L.S.) هي إحدى الطرق الأساسية لتحليل الانحدار من حيث تقدير المعلمات غير المعروفة نماذج الانحداروفقا لبيانات العينة.

لنفكر في التقريب بالوظائف التي تعتمد على متغير واحد فقط:

• خطي: y=ax+b (هذه المقالة)
• : ص=أ*Ln(x)+ب
• : ص=أ*س م
• : y=a*EXP(b*x)+с
• : ص=الفأس 2 +بكس+ج

ملحوظة: حالات التقريب لكثيرة الحدود من الدرجة الثالثة إلى الدرجة السادسة تتناولها هذه المقالة. يعتبر التقريب بواسطة كثيرات الحدود المثلثية هنا.

الاعتماد الخطي

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

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

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

وعلى هذا النحو نستخدم التعبير:

أين ŷ أنا = أ * × ط + ب ; ن – عدد أزواج القيم (في حالتنا ن = 20)

التعبير أعلاه هو مجموع المسافات المربعة بين القيم المرصودة لـ y i و ŷ i وغالباً ما يشار إليه بـ SSE ( مجموع ل تربيع أخطاء (بقايا)، مجموع الأخطاء التربيعية (المتبقية)) .

طريقة المربع الأصغرهو اختيار مثل هذا الخط ŷ = فأس + ب، حيث يأخذ التعبير أعلاه القيمة الدنيا.

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

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

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

كما يتبين من الصيغة، المعلمة أ يمثل نسبة التباين، وبالتالي في MS EXCEL لحساب المعلمة أ يمكنك استخدام الصيغ التالية (انظر ملف مثال للورقة الخطية):

= كوفار (B26:B45;C26:C45)/ DISP.G(B26:B45)أو

= التباين.B(B26:B45;C26:C45)/DISP.B(B26:B45)

أيضا لحساب المعلمة أ يمكنك استخدام الصيغة = إمالة (C26:C45;B26:B45). للمعلمة ب استخدم الصيغة = الساق (C26:C45؛B26:B45) .

وأخيرًا، تتيح لك الدالة LINEST()‎ حساب كلا المعلمتين مرة واحدة. لإدخال صيغة لينست(C26:C45;B26:B45)تحتاج إلى تحديد خليتين متتاليتين والنقر فوق كنترول + يحول + يدخل(انظر المقال حول). سيتم إرجاع القيمة في الخلية اليسرى أ ، على اليمين - ب .

ملحوظة: لتجنب العبث بالإدخال صيغ المصفوفةستحتاج إلى استخدام الدالة INDEX() بالإضافة إلى ذلك. الصيغة = الفهرس(LINEST(C26:C45,B26:B45),1)أو فقط = لينست(C26:C45;B26:B45)سيُرجع المعلمة المسؤولة عن ميل الخط، أي أ . الصيغة = الفهرس(LINEST(C26:C45,B26:B45),2)سيُرجع المعلمة المسؤولة عن تقاطع الخط مع المحور Y، أي. ب .

وبعد حساب المعلمات مخطط مبعثريمكنك رسم الخط المقابل.

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

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

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

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

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

لاينست(Values_y; قيم-X; مقدار ثابت; إحصائيات),

Values_y- مجموعة من القيم ص،

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

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

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

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

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

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

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

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

ذ		0.5		1.5		2.5		3.5
س		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

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

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

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

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

C7=كوريل(B3:J3;B2:J2).

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

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

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

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

أرز. 6

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

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

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

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

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

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

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

تحديد وبناء الاعتماد التقريبي. إنشاء رسوم بيانية للاعتمادات التحليلية الجدولية والمختارة.

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

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

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

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

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

· اسم الاعتماد التقريبي.

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

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

· إظهار الدالة التقريبية على الرسم البياني أم لا (خيار إظهار المعادلة على الرسم البياني)؛

· هل سيتم وضع قيمة الانحراف المعياري على الرسم البياني أم لا (خيار وضع قيمة موثوقية التقريب على الرسم البياني).

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

بالمثل باستخدام خطوط الاتجاهيمكنك تحديد معلمات هذه التبعيات مثل

خطي ذ=أ∙x+ب,

لوغاريتمي ذ=a∙ln(س)+ب,

· متسارع ذ=أ∙ه ب,

· رزين ذ=أ∙س ب,

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

· الترشيح الخطي.

4.3. باستخدام كتلة حلالا

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

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

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

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

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

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

دع القيم أ, في, مع, دو لالمخزنة في الخلايا ج7:ه7. دعونا نحسب القيم النظرية للوظيفة ز(ر)=عند 4 +BT3 +Ct2 +Dt+Kل معين ر(ب2:ج2). للقيام بذلك، في الخلية ب4أدخل قيمة الوظيفة عند النقطة الأولى (cell ب2):

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

لننسخ هذه الصيغة إلى النطاق C4:J4والحصول على القيمة المتوقعة للدالة عند النقاط التي يتم تخزين حروفها في الخلايا ب2:ج2.

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

B5=(B4-B3)^2،

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

F7 = مجموع (B5:J5).

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

سيتم إخراج نتيجة كتلة القرار إلى الخلايا ج7:ه7قيمه المعاملالمهام ز(ر)=عند 4 +BT3 +Ct2 +Dt+K. في الخلايا ب4:ج4نحن نحصل قيمة الدالة المتوقعةفي نقاط البداية. في زنزانة F7سوف يخزن إجمالي مربع الخطأ.

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

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

5. المراجع

1. Alekseev E.R.، Chesnokova O.V.، حل مشاكل الرياضيات الحسابية في حزم Mathcad12، MATLAB7، Maple9. – إن تي برس، 2006.–596 ص. :انا. -(درس تعليمي)

2. ألكسيف إ.ر.، تشيسنوكوفا أو.في.، إ.أ. رودشينكو، سيلاب، حل المسائل الهندسية والرياضية. –م.، بينوم، 2008.–260 ص.

3. بيريزين آي إس، زيدكوف إن بي، الأساليب الحسابية - م: ناوكا، 1966. - 632 ص.

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

5. ديميدوفيتش بي.بي.، مارون آي.إيه.، شوفالوفا في.زد.، الطرق العدديةتحليل.- م.: ناوكا، 1967.- 368 ص.

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

7. ألكسيف إي.آر.، تشيسنوكوفا أو.في. المبادئ التوجيهية للتنفيذ العمل المختبريفي مايكروسوفت اكسل. للطلاب من جميع التخصصات. دونيتسك، دونتو، 2004. 112 ص.