تقريب البيانات التجريبية هو طريقة تعتمد على استبدال البيانات التي تم الحصول عليها تجريبيًا بوظيفة تحليلية تمر بشكل وثيق أو تتزامن عند النقاط العقدية مع القيم الأصلية (البيانات التي تم الحصول عليها أثناء التجربة أو التجربة). حاليًا، هناك طريقتان لتحديد الوظيفة التحليلية:
من خلال بناء متعدد الحدود من الدرجة n الذي يمر مباشرة من خلال جميع النقاطمجموعة بيانات معينة. في في هذه الحالةيتم تمثيل الدالة التقريبية على النحو التالي: استيفاء متعدد الحدود في شكل لاغرانج أو استيفاء متعدد الحدود في شكل نيوتن.
من خلال بناء كثيرة الحدود التقريبية من الدرجة n التي تمر في المنطقة المجاورة مباشرة للنقاطمن مجموعة بيانات معينة. وبالتالي، تعمل وظيفة التقريب على تنعيم كل الضوضاء (أو الأخطاء) العشوائية التي قد تنشأ أثناء التجربة: تعتمد القيم المقاسة أثناء التجربة على عوامل عشوائية تتقلب وفقًا لعواملها الخاصة قوانين عشوائية(أخطاء القياس أو الأجهزة، عدم الدقة أو الأخطاء التجريبية). في هذه الحالة، يتم تحديد الدالة التقريبية باستخدام الطريقة المربعات الصغرى.
طريقة المربع الأصغر(في الأدب الإنجليزي المربعات الصغرى العادية، OLS) هي طريقة رياضية تعتمد على تحديد دالة تقريبية يتم إنشاؤها في أقرب نقطة من النقاط من مجموعة معينة من البيانات التجريبية. يتم تحديد تقارب الوظائف الأصلية والتقريبية F(x) بواسطة مقياس عددي، وهو: يجب أن يكون مجموع الانحرافات التربيعية للبيانات التجريبية من المنحنى التقريبي F(x) هو الأصغر.
منحنى تقريبي تم إنشاؤه باستخدام طريقة المربعات الصغرى
يتم استخدام طريقة المربعات الصغرى:
لحل أنظمة المعادلات المفرطة التحديد عندما يتجاوز عدد المعادلات عدد المجهولين؛
لإيجاد الحل في الحالة العادية (غير متجاوزة) الأنظمة غير الخطيةالمعادلات؛
لتقريب قيم النقاط مع بعض الوظائف التقريبية.
يتم تحديد دالة التقريب باستخدام طريقة المربعات الصغرى من شرط الحد الأدنى لمجموع الانحرافات التربيعية لدالة التقريب المحسوبة من مجموعة معينة من البيانات التجريبية. يتم كتابة هذا المعيار لطريقة المربعات الصغرى بالتعبير التالي:
قيم دالة التقريب المحسوبة عند النقاط العقدية،
مجموعة معينة من البيانات التجريبية في النقاط العقدية.
يحتوي المعيار التربيعي على عدد من الخصائص "الجيدة"، مثل قابلية التفاضل، مما يوفر حلاً فريدًا لمشكلة التقريب مع دوال التقريب متعددة الحدود.
اعتمادًا على ظروف المشكلة، تكون الدالة التقريبية متعددة الحدود من الدرجة m
لا تعتمد درجة الدالة التقريبية على عدد النقاط العقدية، ولكن يجب أن يكون بعدها دائمًا أقل من البعد (عدد النقاط) لمصفوفة بيانات تجريبية معينة.
∙ إذا كانت درجة الدالة التقريبية هي m=1، فإننا نقوم بتقريب الدالة الجدولية بخط مستقيم (الانحدار الخطي).
∙ إذا كانت درجة الدالة التقريبية هي m=2، فإننا نقوم بتقريب دالة الجدول القطع المكافئ التربيعي(التقريب التربيعي).
∙ إذا كانت درجة الدالة التقريبية هي m=3، فإننا نقوم بتقريب دالة الجدول بقطع مكافئ مكعب (تقريب مكعب).
في الحالة العامةعندما يكون من الضروري إنشاء متعدد الحدود تقريبي للدرجة m المعطاة قيم الجدول، تتم إعادة كتابة شرط الحد الأدنى لمجموع الانحرافات التربيعية على جميع النقاط العقدية بالشكل التالي:
- معاملات غير معروفة لتقريب متعدد الحدود من الدرجة م؛
عدد قيم الجدول المحدد.
الشرط الضروري لوجود أدنى دالة هو مساواة مشتقاتها الجزئية بالصفر بالنسبة للمتغيرات المجهولة . ونتيجة لذلك نحصل النظام التاليالمعادلات:
دعونا نحول النتيجة النظام الخطيالمعادلات: افتح الأقواس وانقل الحدود الحرة إلى الجانب الأيمن من التعبير. النظام الناتج الخطي تعبيرات جبريةسيتم كتابتها على الشكل التالي:
يمكن إعادة كتابة هذا النظام من التعبيرات الجبرية الخطية في شكل مصفوفة:
وكانت النتيجة نظاما المعادلات الخطيةالبعد m+1، والذي يتكون من m+1 مجهولة. يمكن حل هذا النظام باستخدام أي طريقة لحل المسائل الخطية. المعادلات الجبرية(على سبيل المثال، طريقة غاوس). نتيجة للحل، سيتم العثور على معلمات غير معروفة للدالة التقريبية توفر الحد الأدنى لمجموع الانحرافات المربعة للدالة التقريبية من البيانات الأصلية، أي. أفضل تقريب تربيعي ممكن. يجب أن نتذكر أنه إذا تغيرت قيمة واحدة من البيانات المصدر، فإن جميع المعاملات ستغير قيمها، حيث يتم تحديدها بالكامل بواسطة البيانات المصدر.
تقريب البيانات المصدر عن طريق الاعتماد الخطي
(الانحدارالخطي)
على سبيل المثال، النظر في تقنية تحديد الوظيفة التقريبية، والتي ترد في النموذج الاعتماد الخطي. وفقا لطريقة المربعات الصغرى، يتم كتابة شرط الحد الأدنى لمجموع الانحرافات المربعة بالشكل التالي:
إحداثيات عقد الجدول؛
معاملات غير معروفة للدالة التقريبية، والتي تم تحديدها على أنها اعتماد خطي.
الشرط الضروري لوجود الحد الأدنى من الدالة هو مساواة مشتقاتها الجزئية بالصفر بالنسبة للمتغيرات غير المعروفة. ونتيجة لذلك نحصل على نظام المعادلات التالي:
دعونا نحول نظام المعادلات الخطي الناتج.
نحن نحل النظام الناتج من المعادلات الخطية. يتم تحديد معاملات الدالة التقريبية في الصورة التحليلية على النحو التالي (طريقة كرامر):
تضمن هذه المعاملات بناء دالة تقريبية خطية وفق معيار تقليل مجموع مربعات الدالة التقريبية من القيم الجدولية المعطاة (بيانات تجريبية).
خوارزمية لتنفيذ طريقة المربعات الصغرى
1. البيانات الأولية:
تم تحديد مجموعة من البيانات التجريبية بعدد القياسات N
يتم تحديد درجة كثيرة الحدود التقريبية (م).
2. خوارزمية الحساب:
2.1. يتم تحديد المعاملات لبناء نظام المعادلات ذات الأبعاد
معاملات نظام المعادلات ( الجهه اليسرىالمعادلات)
- فهرس رقم عمود المصفوفة المربعة لنظام المعادلات
المصطلحات الحرة لنظام المعادلات الخطية ( الجزء الأيمنالمعادلات)
- فهرس رقم الصف للمصفوفة المربعة لنظام المعادلات
2.2. تشكيل نظام المعادلات الخطية ذات البعد .
2.3. حل نظام من المعادلات الخطية لتحديد المعاملات غير المعروفة لمتعددة الحدود التقريبية من الدرجة m.
2.4 تحديد مجموع الانحرافات التربيعية لتقريب كثير الحدود من القيم الأصلية في جميع النقاط العقدية
القيمة التي تم العثور عليها لمجموع الانحرافات المربعة هي الحد الأدنى الممكن.
التقريب باستخدام وظائف أخرى
تجدر الإشارة إلى أنه عند تقريب البيانات المصدر وفقا لطريقة المربعات الصغرى، يتم أحيانا استخدام الدالة اللوغاريتمية كدالة تقريبية، وظيفة الأسيةووظيفة الطاقة.
التقريب اللوغاريتمي
لنفكر في الحالة التي يتم فيها إعطاء الدالة التقريبية بواسطة دالة لوغاريتمية من النموذج:
جوهر طريقة المربعات الصغرى هو في العثور على معلمات نموذج الاتجاه الذي يصف بشكل أفضل ميل تطور أي ظاهرة عشوائية في الزمان أو المكان (الاتجاه هو الخط الذي يميز ميل هذا التطور). تتمثل مهمة طريقة المربعات الصغرى (LSM) في العثور ليس فقط على بعض نماذج الاتجاه، ولكن أيضًا في العثور على النموذج الأفضل أو الأمثل. سيكون هذا النموذج هو الأمثل إذا كان مجموع الانحرافات المربعة بين القيم الفعلية المرصودة وقيم الاتجاه المحسوبة المقابلة ضئيلًا (الأصغر):
أين - الانحراف المعياريبين القيمة الفعلية المرصودة
وقيمة الاتجاه المحسوبة المقابلة،
القيمة الفعلية (المرصودة) للظاهرة محل الدراسة،
القيمة المحسوبة لنموذج الاتجاه،
عدد مشاهدات الظاهرة محل الدراسة.
نادرًا ما يتم استخدام MNC بمفرده. كقاعدة عامة، يتم استخدامه في أغلب الأحيان فقط كتقنية فنية ضرورية في دراسات الارتباط. يجب أن نتذكر أن أساس المعلومات الخاص بالشركة متعددة الجنسيات لا يمكن الاعتماد عليه إلا سلسلة إحصائية، ويجب ألا يقل عدد الملاحظات عن 4، وإلا فإن إجراءات تجانس OLS قد تفقد المنطق السليم.
تتلخص مجموعة أدوات MNC في الإجراءات التالية:
الإجراء الأول. اتضح ما إذا كان هناك أي ميل على الإطلاق لتغيير السمة الناتجة عندما تتغير وسيطة العامل المحدد، أو بمعنى آخر، هل هناك علاقة بين "" في " و " X ».
الإجراء الثاني. ويتم تحديد الخط (المسار) الذي يمكنه وصف هذا الاتجاه أو وصفه بشكل أفضل.
الإجراء الثالث.
مثال. لنفترض أن لدينا معلومات حول متوسط إنتاجية عباد الشمس للمزرعة قيد الدراسة (الجدول 9.1).
الجدول 9.1
|
رقم الملاحظة |
||||||||||
|
الإنتاجية، ج/هك |
نظرًا لأن مستوى التكنولوجيا في إنتاج عباد الشمس في بلدنا ظل دون تغيير تقريبًا على مدار السنوات العشر الماضية، فهذا يعني، على ما يبدو، أن التقلبات في الإنتاج خلال الفترة التي تم تحليلها كانت تعتمد إلى حد كبير على التقلبات في الطقس والظروف المناخية. هل هذا صحيح حقا؟
إجراء OLS الأول. تم اختبار الفرضية القائلة بوجود اتجاه في تغيرات محصول زهرة الشمس اعتمادا على التغيرات في الطقس والظروف المناخية خلال السنوات العشر التي تم تحليلها.
في هذا المثال ل" ذ "ينصح بأخذ محصول عباد الشمس، و" س » – رقم السنة المرصودة في الفترة التي تم تحليلها. اختبار الفرضية حول وجود أي علاقة بين " س " و " ذ "يمكن القيام بذلك بطريقتين: يدويًا وباستخدام برامج الكمبيوتر. بالطبع إذا كان متاحا معدات الحاسوبهذه المشكلة تحل نفسها. ولكن من أجل فهم أدوات MNC بشكل أفضل، فمن المستحسن اختبار الفرضية حول وجود علاقة بين " س " و " ذ » يدويًا، عندما لا يكون في متناول اليد سوى قلم وآلة حاسبة عادية. في مثل هذه الحالات، من الأفضل التحقق من فرضية وجود الاتجاه بصريًا من خلال موقع الصورة الرسومية لسلسلة الديناميكيات التي تم تحليلها - مجال الارتباط:
يقع حقل الارتباط في مثالنا حول خط يتزايد ببطء. وهذا في حد ذاته يدل على وجود اتجاه معين في التغيرات في محصول زهرة الشمس. من المستحيل التحدث عن وجود أي اتجاه فقط عندما يبدو مجال الارتباط وكأنه دائرة أو دائرة أو سحابة رأسية أو أفقية تمامًا أو تتكون من نقاط متناثرة بشكل فوضوي. وفي جميع الحالات الأخرى فإن الفرضية حول وجود علاقة بين “ س " و " ذ "، ومواصلة البحث.
إجراء OLS الثاني. يتم تحديد الخط (المسار) الذي يمكن أن يصف أو يصف بشكل أفضل اتجاه التغيرات في محصول عباد الشمس خلال الفترة التي تم تحليلها.
إذا كان لديك تكنولوجيا الكمبيوتر، فسيتم اختيار الاتجاه الأمثل تلقائيا. عند المعالجة يدويًا، يكون الاختيار الوظيفة المثاليةيتم تنفيذه، كقاعدة عامة، بصريا - من خلال موقع حقل الارتباط. أي أنه بناءً على نوع الرسم البياني، يتم تحديد معادلة الخط الذي يناسب الاتجاه التجريبي (المسار الفعلي) بشكل أفضل.
كما هو معروف، يوجد في الطبيعة مجموعة كبيرة ومتنوعة من التبعيات الوظيفية، لذلك من الصعب للغاية تحليل جزء صغير منها بصريًا. لحسن الحظ، في الممارسة الاقتصادية الحقيقية، يمكن وصف معظم العلاقات بدقة تامة إما عن طريق القطع المكافئ، أو القطع الزائد، أو الخط المستقيم. في هذا الصدد، مع الخيار "اليدوي" لاختيار أفضل وظيفة، يمكنك قصر نفسك على هذه النماذج الثلاثة فقط.
|
القطع الزائد: |
||
|
|
|
القطع المكافئ من الدرجة الثانية: 
:
من السهل أن نرى أنه في مثالنا، فإن أفضل وصف للاتجاه في تغيرات إنتاجية عباد الشمس على مدى السنوات العشر التي تم تحليلها هو الخط المستقيم، وبالتالي فإن معادلة الانحدار ستكون معادلة الخط المستقيم.
الإجراء الثالث. يتم حساب المعلمات معادلة الانحداروصف خط معين، أو بمعنى آخر، يتم تحديد صيغة تحليلية تصف أفضل نموذجاتجاه.
العثور على قيم معلمات معادلة الانحدار، في حالتنا المعلمات و، هو جوهر OLS. تهدف هذه العملية إلى حل نظام من المعادلات العادية.
(9.2)
يمكن حل نظام المعادلات هذا بسهولة تامة باستخدام طريقة غاوس. دعونا نتذكر أنه نتيجة للحل، في مثالنا، تم العثور على قيم المعلمات. وبالتالي فإن معادلة الانحدار التي تم العثور عليها سيكون لها الشكل التالي: 
وله العديد من التطبيقات، لأنه يسمح بتمثيل تقريبي لوظيفة معينة بواسطة وظائف أخرى أبسط. يمكن أن يكون 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 بواسطة البرنامج كمتغير منفصل.
- إذا كانت نافذة TREND لا تشير إلى نطاق معروف بـ x، إذا تم استخدام الدالة في برنامج اكسلسوف نتعامل معها على أنها مصفوفة تتكون من أعداد صحيحة، يتوافق عددها مع النطاق بالقيم المحددة للمتغير y.
- لإخراج مصفوفة من القيم "المتوقعة"، يجب إدخال التعبير الخاص بحساب الاتجاه كصيغة مصفوفة.
- إذا لم يتم تحديد قيم جديدة لـ x، فإن الدالة TREND تعتبرها مساوية للقيم المعروفة. إذا لم يتم تحديدها، فسيتم أخذ الصفيف 1 كوسيطة؛ 2؛ 3؛ 4;...، وهو ما يتناسب مع النطاق مع المعلمات المحددة بالفعل ذ.
- يجب أن يحتوي النطاق الذي يحتوي على قيم x الجديدة على نفس الصفوف أو الأعمدة أو أكثر مثل النطاق الذي يحتوي على قيم y المحددة. وبعبارة أخرى، يجب أن تكون متناسبة مع المتغيرات المستقلة.
- يمكن أن تحتوي المصفوفة ذات قيم x المعروفة على متغيرات متعددة. ومع ذلك، إذا كنا نتحدث عن واحد فقط، فمن الضروري أن تكون النطاقات ذات القيم المعطاة x و y متناسبة. في حالة وجود عدة متغيرات، من الضروري أن يتناسب النطاق مع قيم y المحددة في عمود واحد أو صف واحد.
وظيفة التنبؤ
نفذت باستخدام عدة وظائف. واحد منهم يسمى "التنبؤ". وهو مشابه لـ "TREND"، أي أنه يعطي نتيجة العمليات الحسابية باستخدام طريقة المربعات الصغرى. ومع ذلك، فقط لـ X واحد، قيمة Y غير معروفة.
الآن أنت تعرف الصيغ في Excel للدمى التي تسمح لك بالتنبؤ بالقيمة المستقبلية لمؤشر معين وفقًا للاتجاه الخطي.
مثال.
بيانات تجريبية عن قيم المتغيرات Xو فيوترد في الجدول.
ونتيجة لمواءمتها، يتم الحصول على الوظيفة 
استخدام طريقة المربعات الصغرى، قم بتقريب هذه البيانات من خلال الاعتماد الخطي ص=الفأس+ب(ابحث عن المعلمات أو ب). اكتشف أي من الخطين هو الأفضل (بمعنى طريقة المربعات الصغرى) الذي يقوم بمحاذاة البيانات التجريبية. جعل الرسم.
جوهر طريقة المربعات الصغرى (LSM).
وتتمثل المهمة في العثور على معاملات الاعتماد الخطية التي تكون فيها وظيفة متغيرين أو ب
يأخذ أصغر قيمة. وهذا هو، نظرا أو بسيكون مجموع الانحرافات التربيعية للبيانات التجريبية عن الخط المستقيم الموجود هو الأصغر. هذا هو بيت القصيد من طريقة المربعات الصغرى.
وبالتالي، فإن حل المثال يتلخص في إيجاد الحد الأقصى لدالة لمتغيرين.
اشتقاق الصيغ لإيجاد المعاملات.
تم تجميع وحل نظام من معادلتين بمجهولين. إيجاد المشتقات الجزئية للدالة 
بواسطة المتغيرات أو ب، نحن نساوي هذه المشتقات بالصفر. 
نقوم بحل نظام المعادلات الناتج باستخدام أي طريقة (على سبيل المثال بطريقة الاستبدالأو طريقة كريمر) والحصول على صيغ لإيجاد المعاملات باستخدام طريقة المربعات الصغرى (LSM). 
منح أو بوظيفة 
يأخذ أصغر قيمة. تم تقديم الدليل على هذه الحقيقة أدناه في النص في نهاية الصفحة.
هذه هي الطريقة الكاملة للمربعات الصغرى. صيغة للعثور على المعلمة أيحتوي على المبالغ،،، والمعلمة ن- كمية البيانات التجريبية. نوصي بحساب قيم هذه المبالغ بشكل منفصل. معامل في الرياضيات او درجة بوجدت بعد الحساب أ.
حان الوقت لتذكر المثال الأصلي.
حل.
في مثالنا ن = 5. نقوم بملء الجدول لسهولة حساب المبالغ المضمنة في صيغ المعاملات المطلوبة. 
يتم الحصول على القيم الموجودة في الصف الرابع من الجدول عن طريق ضرب قيم الصف الثاني في قيم الصف الثالث لكل رقم أنا.
يتم الحصول على القيم الموجودة في الصف الخامس من الجدول عن طريق تربيع القيم الموجودة في الصف الثاني لكل رقم أنا.
القيم الموجودة في العمود الأخير من الجدول هي مجموع القيم عبر الصفوف.
نستخدم صيغ طريقة المربعات الصغرى لإيجاد المعاملات أو ب. نستبدل فيها القيم المقابلة من العمود الأخير من الجدول: 
لذلك، ص = 0.165س+2.184- الخط المستقيم التقريبي المطلوب.
يبقى لمعرفة أي من الخطوط ص = 0.165س+2.184أو 
تقريب البيانات الأصلية بشكل أفضل، أي إجراء تقدير باستخدام طريقة المربعات الصغرى.
تقدير الخطأ لطريقة المربعات الصغرى.
للقيام بذلك، تحتاج إلى حساب مجموع الانحرافات التربيعية للبيانات الأصلية من هذه الخطوط 
و 
، تتوافق القيمة الأصغر مع السطر الذي يقترب بشكل أفضل من البيانات الأصلية بمعنى طريقة المربعات الصغرى. 
منذ , ثم على التوالي ص = 0.165س+2.184تقريب البيانات الأصلية بشكل أفضل.
رسم توضيحي لطريقة المربعات الصغرى (LS).
كل شيء واضح للعيان على الرسوم البيانية. الخط الأحمر هو الخط المستقيم الموجود ص = 0.165س+2.184، الخط الأزرق هو 
النقاط الوردية هي البيانات الأصلية.
في الممارسة العملية، عند نمذجة العمليات المختلفة - على وجه الخصوص، الاقتصادية والفيزيائية والتقنية والاجتماعية - يتم استخدام طريقة أو أخرى لحساب القيم التقريبية للوظائف من قيمها المعروفة في نقاط ثابتة معينة على نطاق واسع.
غالبًا ما ينشأ هذا النوع من مشكلة تقريب الوظيفة:
عند بناء صيغ تقريبية لحساب قيم الكميات المميزة للعملية قيد الدراسة باستخدام البيانات الجدولية التي تم الحصول عليها نتيجة للتجربة؛
في التكامل العددي والتمايز والحل المعادلات التفاضليةإلخ.؛
إذا لزم الأمر، حساب قيم الوظائف عند النقاط المتوسطة للفاصل الزمني المدروس؛
عند تحديد قيم الكميات المميزة لعملية ما خارج الفترة الزمنية المدروسة، خاصة عند التنبؤ.
إذا قمنا، من أجل نمذجة عملية معينة محددة بواسطة جدول، ببناء دالة تصف تقريبًا هذه العملية بناءً على طريقة المربعات الصغرى، فسيتم تسميتها بوظيفة تقريبية (الانحدار)، وسيتم استدعاء مشكلة إنشاء الدوال التقريبية نفسها مشكلة التقريب.
تتناول هذه المقالة إمكانيات حزمة MS Excel لحل هذا النوع من المشكلات، بالإضافة إلى أنها توفر طرقًا وتقنيات لإنشاء (إنشاء) الانحدارات للوظائف المجدولة (التي تعد أساس تحليل الانحدار).
لدى Excel خياران لبناء الانحدارات.
إضافة التراجعات المحددة ( خطوط الاتجاه- خطوط الاتجاه) في رسم تخطيطي مبني على أساس جدول بيانات لخاصية العملية قيد الدراسة (متوفر فقط في حالة وجود رسم تخطيطي مُنشأ)؛
استخدام الوظائف الإحصائية المضمنة في ورقة عمل Excel، مما يسمح لك بالحصول على الانحدارات (خطوط الاتجاه) مباشرة من جدول البيانات المصدر.
إضافة خطوط الاتجاه إلى الرسم البياني
بالنسبة لجدول البيانات الذي يصف العملية ويمثله رسم تخطيطي، فإن Excel لديه أداة فعالة لتحليل الانحدار تسمح لك بما يلي:
البناء على أساس طريقة المربعات الصغرى وإضافة خمسة أنواع من الانحدارات إلى الرسم التخطيطي، والتي تمثل العملية قيد الدراسة بدرجات متفاوتة من الدقة؛
إضافة معادلة الانحدار التي تم إنشاؤها إلى الرسم التخطيطي؛
تحديد درجة توافق الانحدار المحدد مع البيانات المعروضة على الرسم البياني.
استنادًا إلى بيانات المخطط، يتيح لك Excel الحصول على أنواع الانحدارات الخطية ومتعددة الحدود واللوغاريتمية والقوة والأسية، والتي تحددها المعادلة:
ص = ص(س)
حيث x هو متغير مستقل غالباً ما يأخذ قيم سلسلة من الأعداد الطبيعية (1؛ 2؛ 3؛ ...) وينتج، على سبيل المثال، عداً تنازلياً لزمن العملية قيد الدراسة (الخصائص).
1 . يعد الانحدار الخطي مفيدًا لنمذجة الخصائص التي تزيد أو تنقص قيمها بمعدل ثابت. هذا هو أبسط نموذج تم إنشاؤه للعملية قيد الدراسة. يتم بناؤه وفقا للمعادلة:
ص = م س + ب
حيث m هو ظل زاوية الميل الانحدارالخطيإلى محور الإحداثي السيني. ب - إحداثيات نقطة تقاطع الانحدار الخطي مع المحور الإحداثي.
2 . يعد خط الاتجاه متعدد الحدود مفيدًا لوصف الخصائص التي لها عدة حدود متطرفة مميزة (الحد الأقصى والحد الأدنى). يتم تحديد اختيار درجة متعددة الحدود من خلال عدد الحدود القصوى للخاصية قيد الدراسة. وبالتالي، يمكن لكثيرة الحدود من الدرجة الثانية أن تصف عملية لها حد أقصى أو أدنى واحد فقط؛ متعدد الحدود من الدرجة الثالثة - لا يزيد عن حدين متطرفين؛ متعدد الحدود من الدرجة الرابعة - لا يزيد عن ثلاثة حدود قصوى، وما إلى ذلك.
في هذه الحالة، يتم إنشاء خط الاتجاه وفقًا للمعادلة:
ص = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6
حيث المعاملات c0، c1، c2،... c6 هي ثوابت يتم تحديد قيمها أثناء الإنشاء.
3 . يتم استخدام خط الاتجاه اللوغاريتمي بنجاح عند نمذجة الخصائص التي تتغير قيمها بسرعة في البداية ثم تستقر تدريجياً.
ص = ج قانون الجنسية (س) + ب
4 . ويعطي خط اتجاه قانون القوة نتائج جيدة إذا كانت قيم العلاقة محل الدراسة تتميز بالتغير المستمر في معدل النمو. مثال على هذا الاعتماد هو الرسم البياني لحركة السيارة المتسارعة بشكل منتظم. إذا كانت هناك قيم صفرية أو سلبية في البيانات، فلا يمكنك استخدام خط اتجاه الطاقة.
شيدت وفقا للمعادلة:
ص = ج إكس ب
حيث المعاملات ب، ج هي الثوابت.
5 . يجب استخدام خط الاتجاه الأسي عندما يتزايد معدل التغير في البيانات بشكل مستمر. بالنسبة للبيانات التي تحتوي على قيم صفرية أو سلبية، لا ينطبق هذا النوع من التقريب أيضًا.
شيدت وفقا للمعادلة:
ص = ج إبكس
حيث المعاملات ب، ج هي الثوابت.
عند تحديد خط الاتجاه، يقوم Excel تلقائيًا بحساب قيمة R2، التي تميز موثوقية التقريب: من قيمة أقرب R2 للوحدة، كلما كان خط الاتجاه أكثر موثوقية يقترب من العملية قيد الدراسة. إذا لزم الأمر، يمكن دائمًا عرض قيمة R2 على الرسم البياني.
يتم تحديده بواسطة الصيغة:
لإضافة خط اتجاه إلى سلسلة بيانات:
تنشيط مخطط بناءً على سلسلة من البيانات، أي انقر داخل منطقة المخطط. سيظهر عنصر الرسم البياني في القائمة الرئيسية؛
بعد النقر على هذا العنصر، ستظهر قائمة على الشاشة يجب عليك فيها تحديد أمر إضافة خط الاتجاه.
يمكن تنفيذ نفس الإجراءات بسهولة عن طريق تحريك مؤشر الماوس فوق الرسم البياني المطابق لإحدى سلاسل البيانات والنقر بزر الماوس الأيمن؛ في قائمة السياق التي تظهر، حدد الأمر إضافة خط الاتجاه. سيظهر مربع الحوار خط الاتجاه على الشاشة مع فتح علامة التبويب "النوع" (الشكل 1).
بعد هذا تحتاج:
حدد نوع خط الاتجاه المطلوب في علامة التبويب النوع (يتم تحديد النوع الخطي افتراضيًا). بالنسبة لنوع كثير الحدود، في حقل الدرجة، حدد درجة كثير الحدود المحدد.
1 . يسرد الحقل "مبني على السلسلة" كافة سلاسل البيانات في المخطط المعني. لإضافة خط اتجاه إلى سلسلة بيانات معينة، حدد اسمه في الحقل "مبني على السلسلة".
إذا لزم الأمر، بالانتقال إلى علامة التبويب "المعلمات" (الشكل 2)، يمكنك تعيين المعلمات التالية لخط الاتجاه:
قم بتغيير اسم خط الاتجاه في اسم حقل المنحنى التقريبي (الملس).
تعيين عدد الفترات (للأمام أو للخلف) للتنبؤ في حقل التنبؤ؛
عرض معادلة خط الاتجاه في منطقة الرسم البياني، والتي يجب عليك تمكين إظهار المعادلة في خانة اختيار الرسم البياني؛
عرض قيمة موثوقية التقريب R2 في منطقة الرسم التخطيطي، والتي يجب عليك تمكين خانة الاختيار وضع قيمة موثوقية التقريب فيها على الرسم التخطيطي (R^2)؛
قم بتعيين نقطة تقاطع خط الاتجاه مع المحور Y، حيث يجب عليك تمكين مربع الاختيار الخاص بتقاطع المنحنى مع المحور Y عند نقطة ما؛
انقر فوق الزر "موافق" لإغلاق مربع الحوار.
من أجل البدء في تعديل خط الاتجاه المرسوم بالفعل، هناك ثلاث طرق:
استخدم أمر خط الاتجاه المحدد من قائمة التنسيق، بعد تحديد خط الاتجاه مسبقًا؛
حدد أمر تنسيق خط الاتجاه من قائمة السياق، والذي يتم استدعاؤه بالنقر بزر الماوس الأيمن على خط الاتجاه؛
انقر مرتين على خط الاتجاه.
سيظهر مربع حوار تنسيق خط الاتجاه على الشاشة (الشكل 3)، يحتوي على ثلاث علامات تبويب: عرض، ونوع، ومعلمات، ويتزامن محتوى الأخيرين تمامًا مع علامات التبويب المشابهة لمربع حوار خط الاتجاه (الشكل 1). -2). في علامة التبويب عرض، يمكنك ضبط نوع الخط ولونه وسمكه.
لحذف خط الاتجاه الذي تم رسمه بالفعل، حدد خط الاتجاه المراد حذفه واضغط على مفتاح الحذف.
مزايا أداة تحليل الانحدار المدروسة هي:
السهولة النسبية لإنشاء خط الاتجاه على الرسوم البيانية دون إنشاء جدول بيانات له؛
قائمة واسعة إلى حد ما من أنواع خطوط الاتجاه المقترحة، وتشمل هذه القائمة أنواع الانحدار الأكثر استخدامًا؛
القدرة على التنبؤ بسلوك العملية قيد الدراسة من خلال عدد تعسفي (في حدود الفطرة السليمة) من الخطوات للأمام وكذلك للخلف ؛
القدرة على الحصول على معادلة خط الاتجاه في شكل تحليلي.
إمكانية، إذا لزم الأمر، الحصول على تقييم لموثوقية التقريب.
تشمل العيوب ما يلي:
يتم تنفيذ بناء خط الاتجاه فقط في حالة وجود رسم تخطيطي مبني على سلسلة من البيانات؛
إن عملية توليد سلاسل البيانات للخاصية قيد الدراسة بناءً على معادلات خط الاتجاه التي تم الحصول عليها لها تكون مزدحمة إلى حد ما: يتم تحديث معادلات الانحدار المطلوبة مع كل تغيير في قيم سلسلة البيانات الأصلية، ولكن فقط ضمن منطقة المخطط ، بينما سلسلة البيانات، التي تم إنشاؤها بناءً على معادلة خط الاتجاه القديم، لم تتغير؛
في تقارير PivotChart، لا يؤدي تغيير طريقة عرض المخطط أو تقرير PivotTable المرتبط إلى الحفاظ على خطوط الاتجاه الموجودة، مما يعني أنه قبل رسم خطوط الاتجاه أو تنسيق تقرير PivotChart، يجب عليك التأكد من أن تخطيط التقرير يلبي المتطلبات المطلوبة.
يمكن استخدام خطوط الاتجاه لتكملة سلسلة البيانات المقدمة على الرسوم البيانية مثل الرسم البياني، والرسم البياني، والمخططات المسطحة غير القياسية، والمخططات الشريطية، والمخططات المبعثرة، والمخططات الفقاعية، ومخططات الأسهم.
لا يمكنك إضافة خطوط الاتجاه إلى سلسلة البيانات في المخططات ثلاثية الأبعاد والمقيسة والرادارية والدائرية والدائرية.
استخدام وظائف Excel المضمنة
يحتوي Excel أيضًا على أداة تحليل الانحدار لرسم خطوط الاتجاه خارج منطقة المخطط. هناك عدد من وظائف ورقة العمل الإحصائية التي يمكنك استخدامها لهذا الغرض، ولكن جميعها تسمح لك فقط ببناء تراجعات خطية أو أسية.
يحتوي برنامج Excel على عدة وظائف لإنشاء الانحدار الخطي، على وجه الخصوص:
المنحدر والقطع.
اتجاه؛
فضلا عن عدة وظائف لبناء خط الاتجاه الأسي، على وجه الخصوص:
LGRFRIBL.
تجدر الإشارة إلى أن تقنيات إنشاء الانحدارات باستخدام دالتي TREND وGROWTH هي نفسها تقريبًا. ويمكن قول الشيء نفسه عن زوج الدالتين LINEST وLGRFPRIBL. بالنسبة لهذه الوظائف الأربع، يستخدم إنشاء جدول القيم ميزات Excel مثل صيغ المصفوفة، والتي تشوش عملية بناء الانحدارات إلى حد ما. لاحظ أيضًا أن بناء الانحدار الخطي، في رأينا، يتم إنجازه بسهولة أكبر باستخدام الدالتين SLOPE و INTERCEPT، حيث تحدد الأولى منهما ميل الانحدار الخطي، والثانية تحدد الجزء الذي يعترضه الانحدار على y -محور.
مزايا أداة الوظائف المضمنة لتحليل الانحدار هي:
عملية موحدة وبسيطة إلى حد ما لإنشاء سلسلة بيانات للخاصية قيد الدراسة لجميع الوظائف الإحصائية المضمنة التي تحدد خطوط الاتجاه؛
المنهجية القياسية لبناء خطوط الاتجاه على أساس سلسلة البيانات التي تم إنشاؤها؛
القدرة على التنبؤ بسلوك العملية قيد الدراسة بعدد الخطوات المطلوبة للأمام أو للخلف.
تشمل العيوب حقيقة أن Excel لا يحتوي على وظائف مضمنة لإنشاء أنواع أخرى (باستثناء الخطية والأسية) من خطوط الاتجاه. في كثير من الأحيان، لا يسمح هذا الظرف باختيار نموذج دقيق بما فيه الكفاية للعملية قيد الدراسة، وكذلك الحصول على توقعات قريبة من الواقع. بالإضافة إلى ذلك، عند استخدام الدالتين TREND وGROWTH، لا تكون معادلات خطوط الاتجاه معروفة.
تجدر الإشارة إلى أن المؤلفين لم يشرعوا في تقديم مسار تحليل الانحدار بأي درجة من الاكتمال. وتتمثل مهمتها الرئيسية في إظهار إمكانيات حزمة 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.
باستخدام دالتي الاتجاه والنمو، قم بالتنبؤ بأرباح المؤسسة لعامي 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 (انظر الشكل 1). 9).
تتم تعبئة سلسلة البيانات بالمثل باستخدام الدالة GROWTH، والتي تُستخدم في تحليل التبعيات غير الخطية وتعمل تمامًا بنفس الطريقة التي تعمل بها نظيرتها الخطية TREND.
يوضح الشكل 10 الجدول في وضع عرض الصيغة.
بالنسبة للبيانات الأولية وسلسلة البيانات التي تم الحصول عليها، يظهر الرسم التخطيطي في الشكل 1. أحد عشر.
المشكلة 4
من خلال جدول البيانات الخاص باستلام طلبات الخدمات عن طريق خدمة الإرسال الخاصة بمؤسسة النقل بالسيارات للفترة من الأول إلى الحادي عشر من الشهر الحالي، يجب عليك تنفيذ الإجراءات التالية.
الحصول على سلسلة بيانات للانحدار الخطي: باستخدام الدالتين SLOPE وINTERCEPT؛ باستخدام الدالة LINEST.
الحصول على سلسلة من البيانات للانحدار الأسي باستخدام الدالة LGRFPRIBL.
باستخدام الوظائف المذكورة أعلاه، قم بالتنبؤ باستلام الطلبات إلى خدمة الإرسال للفترة من 12 إلى 14 من الشهر الحالي.
قم بإنشاء رسم تخطيطي لسلسلة البيانات الأصلية والمستلمة.
حل المشكلة
لاحظ أنه، على عكس الدالتين TREND وGROWTH، لا تعد أي من الدالات المذكورة أعلاه (SLOPE، وINTERCEPT، و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: =SLOPE(B4:B14,A4:A14);
باستخدام تقنية مشابهة، يتم تحديد قيمة المعلمة b في الخلية D19. وستبدو محتوياته كما يلي: =SEGMENT(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؛
· الفاصل الزمني للإخراج - الخلية اليسرى العليا للفاصل الزمني الذي سيتم فيه وضع نتائج الحساب (يوصى بوضعها في ورقة عمل جديدة)؛
4) انقر فوق "موافق" وقم بتحليل النتائج.
طريقة المربع الأصغرتستخدم لتقدير معلمات معادلة الانحدار.إحدى طرق دراسة العلاقات العشوائية بين الخصائص هي تحليل الانحدار.
تحليل الانحدار هو اشتقاق معادلة الانحدار، والتي يتم من خلالها العثور على متوسط قيمة المتغير العشوائي (سمة النتيجة) إذا كانت قيمة متغير آخر (أو غيره) (سمات العامل) معروفة. ويتضمن الخطوات التالية:
- اختيار شكل الاتصال (نوع معادلة الانحدار التحليلي)؛
- تقدير معلمات المعادلة؛
- تقييم جودة معادلة الانحدار التحليلية.
في حالة وجود علاقة زوجية خطية، فإن معادلة الانحدار سوف تأخذ الشكل: y i =a+b·x i +u i . يتم تقدير المعلمات a و b لهذه المعادلة من البيانات المراقبة الإحصائيةس و ص. نتيجة هذا التقييم هي المعادلة: حيث تكون تقديرات المعلمات a و b هي قيمة السمة الناتجة (المتغير) التي تم الحصول عليها من معادلة الانحدار (القيمة المحسوبة).
غالبا ما تستخدم لتقدير المعلمات طريقة المربعات الصغرى (LSM).
توفر طريقة المربعات الصغرى أفضل التقديرات (المتسقة والفعالة وغير المتحيزة) لمعلمات معادلة الانحدار. ولكن فقط في حالة استيفاء بعض الافتراضات المتعلقة بالمصطلح العشوائي (u) والمتغير المستقل (x) (راجع افتراضات OLS).
مشكلة تقدير معلمات المعادلة الزوجية الخطية باستخدام طريقة المربعات الصغرىكما يلي: للحصول على مثل هذه التقديرات للمعلمات، حيث يكون مجموع الانحرافات التربيعية للقيم الفعلية للخاصية الناتجة - y i من القيم المحسوبة - في حده الأدنى.
رسميا اختبار OLSيمكن كتابتها مثل هذا: 
.
تصنيف طرق المربعات الصغرى
- طريقة المربع الأصغر.
- طريقة الاحتمالية القصوى (بالنسبة لنموذج الانحدار الخطي الكلاسيكي العادي، يتم افتراض الحالة الطبيعية لبقايا الانحدار).
- يتم استخدام طريقة المربعات الصغرى المعممة OLS في حالة الارتباط التلقائي للأخطاء وفي حالة التغايرية.
- طريقة المربعات الصغرى المرجحة ( حالة خاصة OLS مع بقايا متغايرة).
دعونا توضيح هذه النقطة الطريقة الكلاسيكيةالمربعات الصغرى بيانيا. للقيام بذلك، سنقوم بإنشاء مخطط مبعثر بناءً على بيانات الرصد (x i، y i، i=1;n) في نظام إحداثي مستطيل (مثل هذا المخطط المبعثر يسمى حقل الارتباط). دعنا نحاول تحديد الخط المستقيم الأقرب إلى نقاط مجال الارتباط. وفقا لطريقة المربعات الصغرى، يتم تحديد الخط بحيث يكون مجموع مربعات المسافات العمودية بين نقاط مجال الارتباط وهذا الخط في حده الأدنى. 
الترميز الرياضي لهذه المشكلة: 
.
قيم y i وx i =1...n معروفة لنا، وهي بيانات رصدية. في الدالة S تمثل الثوابت. المتغيرات في هذه الدالة هي التقديرات المطلوبة للمعلمات - , . للعثور على الحد الأدنى لدالة لمتغيرين، من الضروري حساب المشتقات الجزئية لهذه الدالة لكل من المعلمات ومساواتها بالصفر، أي. 
.
ونتيجة لذلك، نحصل على نظام من معادلتين خطيتين عاديتين: 
اتخاذ القرار هذا النظام، نجد تقديرات المعلمة المطلوبة: 
يمكن التحقق من صحة حساب معلمات معادلة الانحدار من خلال مقارنة المبالغ (قد يكون هناك بعض التناقض بسبب تقريب الحسابات).
لحساب تقديرات المعلمات، يمكنك بناء الجدول 1.
إشارة معامل الانحدار b تشير إلى اتجاه العلاقة (إذا كان b >0، فإن العلاقة مباشرة، إذا كان b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
رسميًا، قيمة المعلمة a هي القيمة المتوسطة لـ y حيث تساوي x صفرًا. إذا لم يكن لعامل السمة قيمة صفرية ولا يمكن أن يكون لها، فإن التفسير أعلاه للمعلمة a ليس له معنى.
تقييم مدى قرب العلاقة بين الخصائص
تم تنفيذها باستخدام معامل الارتباط الزوجي الخطي - r x,y. ويمكن حسابها باستخدام الصيغة: 
. بالإضافة إلى ذلك يمكن تحديد معامل الارتباط الزوجي الخطي من خلال معامل الانحدار b: 
.
نطاق القيم المقبولة لمعامل الارتباط الزوجي الخطي هو من -1 إلى +1. تشير إشارة معامل الارتباط إلى اتجاه العلاقة. إذا كان r x, y >0، فإن الاتصال يكون مباشرًا؛ إذا ص س، ص<0, то связь обратная.
إذا كان هذا المعامل قريبًا من وحدة الحجم، فيمكن تفسير العلاقة بين الخصائص على أنها علاقة خطية قريبة إلى حد ما. إذا كانت وحدتها تساوي واحدًا ê r x , y ê =1، فإن العلاقة بين الخصائص تكون خطية وظيفية. إذا كانت الميزات x وy مستقلة خطيًا، فإن r x,y قريبة من 0.
لحساب r x,y، يمكنك أيضًا استخدام الجدول 1.
الجدول 1
| ملاحظات ن | × ط | ذ ط | س ط ∙ ذ ط | ||
| 1 | × 1 | ذ 1 | × 1 ذ 1 | ||
| 2 | × 2 | ذ 2 | س 2 ص 2 | ||
| ... | |||||
| ن | س ن | ذ ن | س ن ذ ن | ||
| مجموع العمود | ∑س | ∑y | ∑xy | ||
| متوسط القيمة |
 |
 |
,
حيث d 2 هو تباين y الموضح بمعادلة الانحدار؛
e 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.
