LINEST (الدالة LINEST)

الوصف

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

معادلة الخط هي:

y = mx + b

– أو –

y = m1x1 + m2x2 + ... + b

إذا كانت هناك نطاقات متعددة من قيم x، حيث تكون قيم y التابعة هي دالة لقيم x المستقلة. قيم m عبارة عن مُعاملات مطابقة لكل قيمة من قيم x، وتكون b قيمة ثابتة. لاحظ أن y وx وm يمكن أن تكون متجهات. والصفيف الذي تقوم الدالة LINEST بإرجاعه هو {mn,mn-1,...,m1,b}. كما يمكن أن تقوم الدالة LINEST بإرجاع إحصاءات انحدار إضافية.

بناء الجملة

LINEST(known_y's, [known_x's], [const], [stats])‎

يحتوي بناء جملة الدالة LINEST على الوسيطات التالية:

بناء الجملة

• known_y's    مطلوبة. مجموعة قيم y التي تعرفها مسبقاً في العلاقة y = mx + b.

  • إذا كان نطاق known_y's مضمّناً في عمود واحد، يتم عندئذٍ تفسير كل عمود من known_x's كمتغير منفصل.

  • إذا كان نطاق قيم known_y's مضمّناً في صف واحد، يتم عندئذٍ تفسير كل صف من قيم known_x's كمتغير منفصل.

• known_x's    اختيارية. مجموعة قيم x التي تعرفها مسبقاً في العلاقة y = mx + b.

  • يمكن لنطاق known_x's أن يتضمّن مجموعة أو أكثر من المتغيرات. إذا تم استخدام متغير واحد فقط، يمكن لقيم known_y's وknown_x's أن تكون نطاقات من أي شكل، طالما كانت ذات أبعاد متساوية. إذا تم استخدام أكثر من متغير واحد، يجب أن تكون قيم known_y's عبارة عن متجه (أي، نطاق بارتفاع من صف واحد أو بعرض من عمود واحد).

  • إذا تم حذف قيم known_x's، فسيتم افتراض أنها الصفيف {3,2,1،...} بالحجم نفسه لقيم known_y's.

• const    اختيارية. قيمة منطقية تحدد ما إذا كان سيتم فرض الثابت b ليساوي 0.

  • إذا كانت قيمة const تساوي TRUE أو إذا تم حذفها، يتم حساب b بالشكل المعتاد.

  • إذا كانت قيمة const تساوي FALSE، يتم تعيين b ليساوي 0 ويتم ضبط قيم m للحصول على y = mx.

• stats    اختيارية. قيمة منطقية تحدد ما إذا كان سيتم إرجاع إحصاءات انحدار إضافية.

  • إذا كانت الإحصائيات TRUE، فترجع الدالة LINEST إحصائيات الانحدار الإضافية؛ نتيجة لذلك، الصفيف الذي تم إرجاعه هو {mn,mn-1,...,m1,b; sen,sen-1,...,se1,seb; r^2,sey; F,df; ssreg,ssresid}.

  • إذا كانت قيمة stats تساوي FALSE أو محذوفة، تقوم LINEST بإرجاع معاملات m والثابت b فقط.

    تكون إحصاءات الانحدار الإضافية كما يلي.

يعرض الرسم التوضيحي التالي ترتيب إرجاع إحصاءات الانحدار الإضافية.

ملاحظات

• يمكنك وصف أي خط مستقيم بالميل وتقاطع y:

  المنحدر (m):
  للعثور على ميل خط، غالبا ما تتم كتابته ك m، خذ نقطتين على السطر، (x1,y1) و (x2,y2)؛ الميل يساوي (y2 - y1)/(x2 - x1).

  Y-intercept (b):
  التقاطع ص للخط، الذي يكتب غالبا على أنه b، هو قيمة y عند النقطة التي يعبر فيها الخط المحور ص.

  معادلة الخط المستقيم هي y = mx + b. بمجرد معرفة قيم m وb، يمكنك حساب أي نقطة على الخط بواسطة تضمين قيمة y أو x في تلك المعادلة. ويمكنك أيضاً استخدام الدالة TREND.

• عندما يتوفر لديك متغير x مستقل واحد فقط، يمكنك الحصول على قيم الميل وتقاطع y مباشرةً باستخدام الصيغ التالية:

  المنحدر:
  =INDEX(LINEST(known_y,known_x),1)

  Y-intercept:
  =INDEX(LINEST(known_y,known_x),2)

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

  

  

  حيث x وy عبارة عن وسطي العينة؛ أي أن x = AVERAGE(known x's)‎ وy = AVERAGE(known_y's)‎.

• يمكن لدالتي ملاءمة الخط والمنحنى LINEST وLOGEST أن تقوما بحساب أفضل خط مستقيم أو منحنى أسي يلائم البيانات. وعلى الرغم من ذلك، يجب عليك أن تقرر أي النتيجتين أمثل للبيانات. يمكنك حساب TREND(‎known_y's,known_x's)‎ للخط المستقيم أو GROWTH(‎known_y's, known_x's)‎ للمنحنى الأسي. تُرجع الدالتان، من دون الوسيطة new_x's، صفيف قيم y التي تم توقعها على هذا الخط أو المنحنى في نقاط البيانات الفعلية. يمكنك بعد ذلك مقارنة القيم التي تم توقعها بالقيم الفعلية. قد تريد رؤية مخطط كل منهما للحصول على مقارنة مرئية.

• في تحليل الانحدار، يقوم Excel بحساب الفرق التربيعي لكل نقطة بين قيمة y المقدرة لهذه النقطة وقيمة y الفعلية. يسمى مجموع فروق تلك التربيعات بمجموع المربعات، ssresid. ثم يقوم Excel بحساب إجمالي مجموع المربعات، sstotal. عندما تساوي قيمة الوسيطة const = TRUE، أو عند حذفها، يصبح إجمالي مجموع المربعات هو إجمالي فروق التربيعات بين قيم y الفعلية ومتوسط قيم y. عندما تساوي قيمة الوسيطة const = FALSE، يصبح إجمالي مجموع المربعات هو مجموع مربعات قيم y الفعلية (دون طرح متوسط قيمة y من كل قيمة y فردية). يمكن الحصول على انحدار مجموع المربعات، من <ltr>: ssreg = sstotal - ssresid</ltr>. كلما كان مجموع المربعات المتبقي أصغر، مقارنة بالمجموع الإجمالي للمربعات، كلما كانت قيمة معامل التحديد r² أكبر، وهو مؤشر على مدى جودة تفسير المعادلة الناتجة عن تحليل الانحدار للعلاقة بين المتغيرات. قيمة r² تساوي ssreg/sstotal.

• في بعض الحالات، لا يكون لواحد أو أكثر من أعمدة X (افترض أن لكل من Y وX أعمدة) قيمة تنبؤية إضافية في وجود أعمدة X أخرى. بمعنى آخر، قد يؤدي حذف عمود أو أكثر من أعمدة X إلى معرفة قيم Y المتوقعة والتي تكون على نفس مستوى الدقة. وفي هذه الحالة يجب حذف أعمدة X المكررة من نموذج الانحدار. تسمى هذه الظاهرة "القيم الخطية" حيث إنه يمكن التعبير عن أي عمود X مكرر كمجموع ضرب أعمدة X غير المكررة. تقوم الدالة LINEST بالتحقق من القيم الخطية وإزالة أعمدة X المكررة من نموذج الانحدار عند تعريفها. ويمكن التعرف على أعمدة X التي تمت إزالتها في إخراج LINEST عند الحصول على معاملات صفر بالإضافة إلى قيم se الصفرية. في حالة إزالة عمود أو أكثر كأعمدة مكررة، فإن df ستتأثر حيث إنها تعتمد على عدد من أعمدة X المستخدمة بالفعل في أغراض تنبؤية. لمزيد من التفاصيل حول حساب df، راجع المثال 4. إذا تغيرت df بسبب إزالة أعمدة X المكررة، فإن قيم sey وF ستتأثر كذلك. يجب أن تكون القيم الخطية المرتبطة نادرة التكرار في التمرين. على الرغم من ذلك، فإن هناك حالة أكثر عرضة للحدوث وهي احتواء بعض أعمدة X على قيم صفرية وقيم أحادية كمؤشرات حول ما إذا كان الموضوع في التجربة يعتبر أو لا يعتبر جزءاً من مجموعة معينة. إذا كانت const تساوي = TRUE أو تم حذفها، تقوم الدالة LINEST بإدراج عمود X إضافي لكافة القيم الأحادية لتمثيل التقاطع. إذا كان لديك عمود به القيمة 1 لكل حالة إذا كانت مذكراً، أو 0 إذا لم تكن كذلك، وكان لديك كذلك عمود به القيمة 1 لكل حالة إذا كانت مؤنثاً أو 0 إذا لم تكن كذلك، فإن العمود الأخير يعتبر مكرراً حيث إن الإدخالات الموجودة به يمكن الحصول عليها من طرح الإدخال في عمود "المؤشر المذكر" من إدخال العمود الإضافي لكافة القيم الأحادية التي أضافتها الدالة LINEST.

• يتم حساب قيمة df كما يلي، عندما لا يتم حذف أية أعمدة X من النموذج بسبب القيم الخطية: إذا كان هناك k أعمدة في known_x’s وكانت const تساوي= TRUE أو تم حذفها، فإن df = n – k – 1. إذا كانت const تساوي = FALSE، فإن df = n - k. وفي كلتا الحالتين، تؤدي إزالة كل عمود من أعمدة X بسبب القيم الخطية إلى زيادة df بمقدار 1.

• عند إدخال ثابت صفيف (مثل known_x's) كوسيطة، استخدم الفواصل لفصل القيم في الصف نفسه والفواصل المنقوطة لفصل الصفوف. يمكن أن تختلف الأحرف الفاصلة حسب الإعدادات الإقليمية.

• لاحظ أن قيم y التي توقعتها معادلة الانحدار قد لا تكون صحيحة إذا كانت خارج نطاق قيم y المستخدمة لتحديد المعادلة.

• تختلف الخوارزمية الأساسية المستخدمة في دالة LINEST عن الخوارزمية الأساسية المستخدمة في الدالتين SLOPE وINTERCEPT. يمكن أن يؤدي هذا التباين بين الخوارزميات إلى نتائج مختلفة عندما تكون البيانات غير محددة أو محورية. على سبيل المثال، إذا كانت قيمة نقاط البيانات لوسيطة known_y's صفر وقيمة نقاط البيانات لوسيطة known_x's‏ 1:

  • تقوم LINEST بإرجاع القيمة 0. تم تصميم خوارزمية الدالة LINEST لإرجاع نتائج منطقية لبيانات محورية، وفي هذه الحالة يتم العثور على إجابة واحدة على الأقل.

  • ترجع الدالة SLOPE و INTERCEPT #DIV/0! #REF!. تم تصميم خوارزمية الدالتين SLOPE و INTERCEPT للبحث عن إجابة واحدة فقط، وفي هذه الحالة يمكن أن يكون هناك أكثر من إجابة واحدة.

• بالإضافة إلى استخدام LOGEST لحساب إحصاءات أنواع الانحدار الأخرى، يمكنك استخدام LINEST لحساب نطاق من أنواع الانحدار الأخرى من خلال إدخال دالات المتغيرين x وy كسلسلة x وy للدالة LINEST. على سبيل المثال، الصيغة التالية:

  ‎=LINEST(yvalues, xvalues^COLUMN($A:$C))‎

  تعمل عندما يكون لديك عمود واحد لقيم y وعمود واحد لقيم x لحساب التقريب التكعيبي للشكل (متعدد الحدود للترتيب 3).

  y = m1*x + m2*x^2 + m3*x^3 + b

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

• تختلف قيمة F-test التي يتم إرجاعها بواسطة الدالة LINEST‏ عن قيمة F-test التي يتم إرجاعها بواسطة الدالة FTEST. تُرجع الدالة LINEST إحصائية F، بينما تُرجع الدالة FTEST الاحتمال.

أمثلة