طريقة المربعات الصغرى في طاقة انحدار Excel. طريقة المربعات الصغرى في برنامج 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 ...).

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

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

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

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

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

3. في النافذة التالية، حدد المربع بجوار "البحث عن حل" وانقر فوق "موافق".

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

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

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

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

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

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

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

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

الطريقة هي (هنا - انتبه: عليك التفكير في الأمر) أن مجموع مربعات الاختلافات بين القيم المحسوبة التي تم الحصول عليها تجريبياً والقيم المحسوبة المقابلة لها هو الحد الأدنى. أي أنه عندما X1=1 القيمة الفعلية المقاسة Y1=4.6، والقيمة المحسوبة y1=f (x1) هي 4، فإن مربع الفرق سيكون (y1-Y1)^2=(4-4.6)^2= 0.36 . الأمر نفسه ينطبق على ما يلي: عندما X2=2، والقيمة المقاسة الفعلية لـ Y2=8.1، وy2 المحسوبة هي 8، سيكون مربع الفرق (y2-Y2)^2=(8-8.1)^2 =0.01. ويجب أن يكون مجموع كل هذه المربعات صغيرًا قدر الإمكان.

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

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

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

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

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

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

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

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

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

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

ملاحظة:

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

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

وله العديد من التطبيقات، لأنه يسمح بتمثيل تقريبي لوظيفة معينة بواسطة وظائف أخرى أبسط. يمكن أن يكون 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 المحددة في عمود واحد أو صف واحد.

وظيفة التنبؤ

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

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

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