حيث y x هي القيمة المحسوبة من المعادلة.
ويشير متوسط خطأ التقريب الذي يصل إلى 15% إلى نموذج معادلة مناسب.
بالنسبة لسبع مناطق من منطقة الأورال لعام 199X، فإن قيم الخاصيتين معروفة.
مطلوب:1. لتوصيف اعتماد y على x، احسب معلمات الوظائف التالية:
أ) خطي؛
ب) القوة؛
ج) توضيحي.
د) القطع الزائد متساوي الأضلاع (تحتاج أيضًا إلى معرفة كيفية جعل هذا النموذج خطيًا مسبقًا).
2. تقييم كل نموذج من خلاله متوسط خطأ التقريب Cf واختبار فيشر F.
لقد قمنا بحل المشكلة باستخدام الآلة الحاسبة الإلكترونية معادلة الانحدار الخطي.
أ) معادلة الانحدار الخطي.
باستخدام الطريقة الرسومية.
تستخدم هذه الطريقة لتصوير شكل الارتباط بين المؤشرات الاقتصادية المدروسة بشكل مرئي. للقيام بذلك، يتم رسم رسم بياني في نظام إحداثيات مستطيل، ويتم رسم القيم الفردية للخاصية الناتجة Y على طول المحور الإحداثي، ويتم رسم القيم الفردية للعامل المميز X على طول محور الإحداثي السيني.
تسمى مجموعة النقاط لخصائص المحصلة والعامل مجال الارتباط.
بناءً على مجال الارتباط، يمكننا أن نفترض (بالنسبة للمجتمع) أن العلاقة بين جميع القيم الممكنة لـ X وY هي علاقة خطية.
معادلة الانحدار الخطي هي y = bx + a + ε
هنا ε خطأ عشوائي (الانحراف، الاضطراب).
أسباب وجود الخطأ العشوائي:
1. الفشل في تضمين متغيرات تفسيرية مهمة في نموذج الانحدار.
2. تجميع المتغيرات. على سبيل المثال، تعد دالة الاستهلاك الإجمالي محاولة للتعبير بشكل عام عن إجمالي قرارات الإنفاق الفردية. هذا مجرد تقريب للعلاقات الفردية التي لها معلمات مختلفة.
3. وصف غير صحيح لهيكل النموذج؛
4. المواصفات الوظيفية غير صحيحة.
5. أخطاء القياس.
بما أن الانحرافات ε i لكل ملاحظة محددة i عشوائية وقيمها في العينة غير معروفة، إذن:
1) من الملاحظات x i و y i يمكن الحصول على تقديرات المعلمات α و β فقط
2) تقديرات المعلمات α و β لنموذج الانحدار هي القيم a و b على التوالي، وهي عشوائية بطبيعتها، لأن تتوافق مع عينة عشوائية.
بعد ذلك، سيكون لمعادلة الانحدار التقديرية (المنشأة من بيانات العينة) الشكل y = bx + a + ε، حيث e i هي القيم (التقديرات) المرصودة للأخطاء ε i و a و b، على التوالي، تقديرات لـ المعلمات α و β لنموذج الانحدار الذي ينبغي العثور عليه.
لتقدير المعلمات α و β - يتم استخدام طريقة المربعات الصغرى (طريقة المربعات الصغرى).
نحصل على ب = -0.35، أ = 76.88
معادلة الانحدار:
ص = -0.35 س + 76.88
| س | ذ | × 2 | ذ 2 | س ص | ص (خ) | (ص ط -y cp) 2 | (ص-ص(خ)) 2 | |y - yx |:y |
| 45,1 | 68,8 | 2034,01 | 4733,44 | 3102,88 | 61,28 | 119,12 | 56,61 | 0,1094 |
| 59 | 61,2 | 3481 | 3745,44 | 3610,8 | 56,47 | 10,98 | 22,4 | 0,0773 |
| 57,2 | 59,9 | 3271,84 | 3588,01 | 3426,28 | 57,09 | 4,06 | 7,9 | 0,0469 |
| 61,8 | 56,7 | 3819,24 | 3214,89 | 3504,06 | 55,5 | 1,41 | 1,44 | 0,0212 |
| 58,8 | 55 | 3457,44 | 3025 | 3234 | 56,54 | 8,33 | 2,36 | 0,0279 |
| 47,2 | 54,3 | 2227,84 | 2948,49 | 2562,96 | 60,55 | 12,86 | 39,05 | 0,1151 |
| 55,2 | 49,3 | 3047,04 | 2430,49 | 2721,36 | 57,78 | 73,71 | 71,94 | 0,172 |
| 384,3 | 405,2 | 21338,41 | 23685,76 | 22162,34 | 405,2 | 230,47 | 201,71 | 0,5699 |
ملحوظة: تم العثور على قيم y(x) من معادلة الانحدار الناتجة:
ص(45.1) = -0.35*45.1 + 76.88 = 61.28
ص(59) = -0.35*59 + 76.88 = 56.47
... ... ...
خطأ في التقريب
دعونا نقيم جودة معادلة الانحدار باستخدام خطأ التقريب المطلق. متوسط خطأ التقريب- متوسط انحراف القيم المحسوبة عن القيم الفعلية:
وبما أن الخطأ أقل من 15%، فيمكن استخدام هذه المعادلة كانحدار.
إحصائيات F. معيار فيشر.
3. يتم تحديد القيمة المجدولة من جداول توزيع فيشر لمستوى دلالة معين مع الأخذ في الاعتبار أن عدد درجات الحرية لمجموع المربعات الكلية (التباين الأكبر) هو 1 وعدد درجات الحرية للمجموع المتبقي مجموع المربعات (التباين الأصغر) في الانحدار الخطي هو n-2 .
4. إذا كانت القيمة الفعلية لاختبار F أقل من القيمة الجدولية، فيقولون أنه لا يوجد سبب لرفض الفرضية الصفرية.
وبخلاف ذلك يتم رفض الفرضية الصفرية وقبول الفرضية البديلة حول الأهمية الإحصائية للمعادلة ككل باحتمال (1-α).
< Fkp, то коэффициент детерминации статистически не значим (Найденная оценка уравнения регрессии статистически не надежна).
ب) انحدار القوة.
يتم تنفيذ الحل باستخدام خدمة الانحدار غير الخطي. عند التحديد، حدد Power y = ax b
ج) الانحدار الأسي.
د) نموذج القطع الزائد متساوي الأضلاع.
نظام المعادلات العادية.
بالنسبة لبياناتنا، فإن نظام المعادلات له الشكل
7أ + 0.1291ب = 405.2
0.1291أ + 0.0024ب = 7.51
من المعادلة الأولى نعبر عن a ونعوض به في المعادلة الثانية
نحصل على ب = 1054.67، أ = 38.44
معادلة الانحدار:
ص = 1054.67 / س + 38.44
خطأ في التقريب.
دعونا نقيم جودة معادلة الانحدار باستخدام خطأ التقريب المطلق.
وبما أن الخطأ أقل من 15%، فيمكن استخدام هذه المعادلة كانحدار.
معيار فيشر.
يتم اختبار أهمية نموذج الانحدار باستخدام اختبار Fisher's F، والذي يتم العثور على قيمته المحسوبة كنسبة التباين في سلسلة الملاحظات الأصلية للمؤشر قيد الدراسة والتقدير غير المتحيز لتباين التسلسل المتبقي لهذا النموذج.
إذا كانت القيمة المحسوبة بدرجات الحرية k1=(m) وk2=(n-m-1) أكبر من القيمة المجدولة عند مستوى دلالة معين، فإن النموذج يعتبر مهمًا.
حيث m هو عدد العوامل في النموذج.
يتم تقييم الأهمية الإحصائية للانحدار الخطي المقترن باستخدام الخوارزمية التالية:
1. تم طرح فرضية صفرية مفادها أن المعادلة ككل غير ذات دلالة إحصائية: H 0: R 2 =0 عند مستوى الأهمية α.
2. بعد ذلك، حدد القيمة الفعلية للمعيار F:
حيث m=1 للانحدار الزوجي.
القيمة الجدولية للمعيار مع درجات الحرية k1=1 و k2=5، Fkp = 6.61
منذ القيمة الفعلية لـ F< Fkp, то коэффициент детерминации статистически не значим (Найденная оценка уравнения регрессии статистически не надежна).
تذكر أن تحليل الانحدار هو نوع من التحليل الإحصائي المستخدم للتنبؤ. يتيح لك تحليل الانحدار تقدير قوة العلاقة بين المتغيرات من خلال تقديم آلية لحساب القيمة المتوقعة للمتغير من عدة قيم معروفة بالفعل.
يمكن أن تكمل خطوط الاتجاه سلسلة البيانات المقدمة في المخططات المساحية غير الطبيعية، والمخططات الشريطية، والمخططات التكرارية، والرسوم البيانية، ومخططات الأسهم، والمخططات المبعثرة، والمخططات الفقاعية. يتم تحديد استخدام خط الاتجاه من نوع أو آخر حسب نوع البيانات. لا يمكنك إضافة خطوط الاتجاه إلى سلسلة البيانات في مخططات ثلاثية الأبعاد، أو مخططات عادية، أو مخططات رادارية، أو مخططات دائرية، أو مخططات دائرية مجوفة.
يُظهر المنحنى السلس النمط في تطوير البيانات بشكل أكثر وضوحًا. وهو مبني على نقاط المتوسط المتحرك، حيث يعني المتوسط المتحرك سلسلة من أرقام المتوسط، يتم حساب كل منها من مجموعة فرعية معينة من سلسلة البيانات.
إضافة خط الاتجاه أو المتوسط المتحرك إلى سلسلة البيانات
يستخدم برنامج Excel ستة أنواع مختلفة من خطوط الاتجاه (الملاءمة والتجانس) التي يمكن إضافتها إلى المخطط (الشكل 18.11):
- التقريب الخطي(الخطي) هو الخط المستقيم الذي يصف مجموعة البيانات بشكل أفضل. معادلة الخط المستقيم هي y=ax+b، حيث a هو ظل زاوية الميل، وb هي نقطة تقاطع الخط المستقيم مع المحور y. يستخدم التقريب الخطي للمتغيرات التي تزيد أو تنقص بمعدل ثابت.
- التقريب اللوغاريتمي(اللوغاريتمي) يصف جيدًا الكميات الموجبة والسالبة، والتي تزيد أو تنقص بسرعة في البداية، ثم تستقر تدريجيًا. يستخدم التقريب اللوغاريتمي المعادلة y=c*lnx+b، حيث c وb ثوابت، وIn هو اللوغاريتم الطبيعي.
- تقريب متعدد الحدود(متعددة الحدود) تستخدم لوصف الكميات التي تتزايد وتنقص بالتناوب. يُنصح باستخدامه لتحليل مجموعة كبيرة من البيانات ذات الحجم غير المستقر. يتم تحديد درجة كثير الحدود من خلال عدد الحدود القصوى (الحد الأقصى والحد الأدنى) للمنحنى. يمكن أن تصف متعددة الحدود من الدرجة الثانية حدًا أقصى أو أدنى واحد فقط. كثيرة الحدود من الدرجة الثالثة لها حد أو اثنان. لا يمكن أن يحتوي كثير الحدود من الدرجة الرابعة على أكثر من ثلاثة حدود متطرفة. يتم وصف التقريب متعدد الحدود بالمعادلة y=a+ciXi+C2X2++Cigx18، حيث a وCj-Cjg ثوابت. يتم تحديد الدرجة المطلوبة لكثير الحدود في حقل الدرجة (الشكل). الحد الأقصى لقيمة الدرجة هو 18.
- تقريب القوة(القوة) تعطي نتائج جيدة إذا كان الاعتماد الموجود في البيانات يتميز بمعدل نمو ثابت. مثال على هذه العلاقة هو الرسم البياني لتسارع السيارة. إذا كانت البيانات تحتوي على قيم صفرية أو سلبية، فلا يمكن استخدام تقريب قانون القوة. يتم وصف تقريب قانون القوة بالمعادلة y=a * xn، حيث a و n ثوابت.
- التقريب الأسييجب استخدام (الأسي) إذا كان معدل تغير البيانات في تزايد مستمر. ومع ذلك، بالنسبة للبيانات التي تحتوي على قيم صفرية أو سلبية، لا ينطبق هذا النوع من التقريب. يتم وصف التقريب الأسي بالمعادلة y = a ebx، حيث a وb ثوابت.
- الترشيح الخطي(المتوسط المتحرك) يسمح لك بتخفيف تقلبات البيانات وبالتالي إظهار طبيعة الاعتماد بشكل أكثر وضوحًا. يتم إنشاء خط الاتجاه هذا على عدد معين من النقاط (يتم تحديده بواسطة معلمة الفترة) ويتم حساب متوسط عناصر البيانات، ويتم استخدام النتيجة الناتجة كقيمة متوسطة للتقريب، وبالتالي، إذا كانت معلمة Tonki متساوية إلى 2، يتم تعريف النقطة الأولى من منحنى التجانس على أنها متوسط أول عنصرين من عناصر البيانات، والنقطة الثانية هي متوسط العنصرين التاليين، وهكذا لحساب المتوسط المتحرك، يتم استخدام المعادلة y = (Aj يتم استخدام +Aj_i++Aj_n+i)/n.
إضافة خط الاتجاه إلى سلسلة البيانات
لإضافة خط اتجاه إلى سلسلة بيانات، اتبع الخطوات التالية:
- حدد سلسلة البيانات التي تريد إضافة خط اتجاه أو متوسط متحرك إليها؛
- حدد الفريق أضف خط الاتجاه(إضافة خط الاتجاه) في القائمة رسم بياني(جدول). على علامة التبويب يكتب(اكتب) حدد النوع المطلوب من خط اتجاه الانحدار أو خط المتوسط المتحرك (الشكل 18.11)؛
- عند اختيار النوع متعدد الحدود(متعدد الحدود) أدخل في هذا المجال درجة(الترتيب) أعلى درجة للمتغير المستقل؛
- عند اختيار النوع المتوسط المتحرك(المتوسط المتحرك) أدخل في الحقل نقاط(الفترة) عدد النقاط المستخدمة لحساب المتوسط المتحرك.
أرز. 18.11. اختيار خط الاتجاه
التبعيات
يحتوي Excel على أدوات تسمح لك بالتنبؤ بالعمليات. تنشأ مشكلة التقريب عندما يكون من الضروري وصف الظواهر التي تحدث في الحياة بشكل تحليلي ويتم تقديمها في شكل جداول تحتوي على قيم الوسيطة (الحجج) والوظائف. إذا أمكن العثور على الاعتماد، فمن الممكن التنبؤ بسلوك النظام قيد الدراسة في المستقبل وربما اختيار الاتجاه الأمثل لتطوره. مثل هذه الوظيفة التحليلية (وتسمى أيضًا الاتجاه) يمكن أن يكون لها أشكال مختلفة ومستويات مختلفة من التعقيد اعتمادًا على مدى تعقيد النظام والدقة المطلوبة للتمثيل.
10.1. الانحدار الخطي
الأبسط والأكثر شيوعًا هو تقريب الخط المستقيم - الانحدار الخطي.
دعونا نحصل على معلومات فعلية حول مستويات الربح Y اعتمادًا على حجم استثمار X - Y(X). في الشكل. يوضح الشكل 10.1-1 أربع نقاط من هذا القبيل M(Y,X). دعونا أيضًا يكون لدينا سبب لنفترض أن هذا الاعتماد خطي، أي. يبدو ص=أ+بكس.إذا تمكنا من العثور على المعاملين A وB واستخدامهما لإنشاء خط مستقيم (على سبيل المثال، مثل ذلك الموجود في الشكل)، فيمكننا في المستقبل وضع افتراضات مستنيرة حول ديناميكيات العمل والحالة التجارية المحتملة للمؤسسة في المستقبل. من الواضح أننا سنكتفي بخط مستقيم يقع في أقرب مكان ممكن من النقاط المعروفة M(Y,X)، أي. وجود حد أدنى من الانحرافات أو مجموع الأخطاء (في الشكل، تظهر الانحرافات بخطوط منقطة). ومن المعروف أنه لا يوجد سوى خط واحد من هذا القبيل.
|
|
لحل هذه المشكلة، يتم استخدام طريقة خطأ المربعات الصغرى. الفرق (الخطأ) بين القيمة المعروفة Y1 للنقطة M1(Y1,X1) والقيمة Y(X1) المحسوبة باستخدام معادلة الخط المستقيم لنفس القيمة X1 سيكون
D1 = Y1 – أ – ب X1.
نفس الفرق
لأن X=X2 سيكون D2 = Y2 – A – B X2؛
لـ X=X3 D3 = Y3 – A – B X3؛
وبالنسبة لـ X=X4 D4 = Y4 – A – B X4.
دعونا نكتب تعبيرًا لمجموع مربعات هذه الأخطاء
Ф(A,В)=(Y1–A–B X1) 2 +(Y2–A–B X2) 2 +(Y3–A–B X3) 2 +(Y4–A–B X4) 2
أو مختصرة Ф(B,A) = å(Yi – A – BXi) 2.
نحن هنا نعرف كل X وY والمعاملات غير المعروفة A وB. دعونا نرسم الخط المستقيم المطلوب بهذه الطريقة (أي اختر A وB) بحيث يكون مجموع الأخطاء المربعة Ф(A,B) في حده الأدنى. وشروط الحد الأدنى هي العلاقات المعلومة
¶Ф(A,B)/¶A=0 و ¶Ф(A,B)/¶B=0.
دعونا نشتق هذه التعبيرات (نحذف الحروف السفلية عند علامة المجموع):
¶[å(Yi–A–B Xi) 2 ]/¶A = å(Yi–A–B Xi)(–1)
¶[å(Yi–A–B Xi) 2 ]/¶B = å(Yi–A–B Xi)(–Xi).
دعونا نحول الصيغ الناتجة ونساويها بالصفر
المعلومات النظرية
في الممارسة العملية، عند نمذجة العمليات المختلفة - على وجه الخصوص، الاقتصادية والفيزيائية والتقنية والاجتماعية - يتم استخدام طريقة أو أخرى لحساب القيم التقريبية للوظائف من قيمها المعروفة في نقاط ثابتة معينة على نطاق واسع.
غالبًا ما ينشأ هذا النوع من مشكلة تقريب الوظيفة:
- عند بناء صيغ تقريبية لحساب قيم الكميات المميزة للعملية قيد الدراسة باستخدام البيانات الجدولية التي تم الحصول عليها نتيجة للتجربة؛
- في التكامل العددي، والتمايز، وحل المعادلات التفاضلية، وما إلى ذلك؛
- إذا كان من الضروري حساب قيم الدوال عند النقاط المتوسطة للفاصل الزمني المدروس؛
- عند تحديد قيم الكميات المميزة لعملية ما خارج الفترة الزمنية المدروسة، خاصة عند التنبؤ.
إذا قمنا، لنمذجة عملية معينة محددة بواسطة جدول، ببناء دالة تصف تقريبًا هذه العملية بناءً على طريقة المربعات الصغرى، فسيتم تسميتها دالة تقريبية (الانحدار)، وسيتم استدعاء مشكلة إنشاء الدوال التقريبية نفسها مشكلة التقريب.
تتناول هذه المقالة إمكانيات حزمة 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). في علامة التبويب عرض، يمكنك ضبط نوع الخط ولونه وسمكه.
لحذف خط الاتجاه الذي تم رسمه بالفعل، حدد خط الاتجاه المراد حذفه واضغط على مفتاح الحذف.
مزايا أداة تحليل الانحدار المدروسة هي:
- السهولة النسبية لإنشاء خط الاتجاه على الرسوم البيانية دون إنشاء جدول بيانات له؛
- قائمة واسعة إلى حد ما من أنواع خطوط الاتجاه المقترحة، وتشمل هذه القائمة أنواع الانحدار الأكثر استخدامًا؛
- القدرة على التنبؤ بسلوك العملية قيد الدراسة من خلال عدد تعسفي (في حدود الفطرة السليمة) من الخطوات للأمام وكذلك للخلف ؛
- القدرة على الحصول على معادلة خط الاتجاه في شكل تحليلي.
- إمكانية، إذا لزم الأمر، الحصول على تقييم لموثوقية التقريب.
تشمل العيوب ما يلي:
يتم تنفيذ بناء خط الاتجاه فقط في حالة وجود رسم تخطيطي مبني على سلسلة من البيانات؛يمكن استخدام خطوط الاتجاه لاستكمال سلسلة البيانات المقدمة على الرسوم البيانية مثل الرسم البياني، والرسم البياني، والمخططات المسطحة غير القياسية، والمخططات الشريطية، والمخططات المبعثرة، والمخططات الفقاعية، ومخططات الأسهم.
لا يمكنك إضافة خطوط الاتجاه إلى سلسلة البيانات في المخططات ثلاثية الأبعاد والمقيسة والرادارية والدائرية والدائرية.
استخدام وظائف 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. 11.
المشكلة 4
من خلال جدول البيانات الخاص باستلام طلبات الخدمات عن طريق خدمة الإرسال الخاصة بمؤسسة النقل بالسيارات للفترة من الأول إلى الحادي عشر من الشهر الحالي، يجب عليك تنفيذ الإجراءات التالية.
- الحصول على سلسلة البيانات للانحدار الخطي:باستخدام وظائف SLOPE وCUT؛ باستخدام الدالة 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 . نحن نبني الانحدار الأسي بالمعادلة:
ذ = بي إم إكس
باستخدام الدالة LGRFRIBL يتم تنفيذه بالمثل:
في نطاق الخلايا C21:D21، ندخل الدالة LGRFPRIBL كصيغة صفيف: =( LGRFPRIBL (B4:B14,A4:A14)). في هذه الحالة، سيتم تحديد قيمة المعلمة m في الخلية C21، وسيتم تحديد قيمة المعلمة b في الخلية D21؛
في الشكل. يوضح الشكل 13 جدولاً يمكنك من خلاله رؤية الوظائف التي نستخدمها مع نطاقات الخلايا المطلوبة، بالإضافة إلى الصيغ.
بالنسبة للبيانات الأولية وسلسلة البيانات التي تم الحصول عليها، يظهر الرسم التخطيطي في الشكل 1. 14.