LINEST (الدالة LINEST)

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

الوصف

تقوم الدالة 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    اختيارية. قيمة منطقية تحدد ما إذا كان سيتم إرجاع إحصاءات انحدار إضافية.

    • إذا كانت قيمة stats تساوي TRUE، تقوم LINEST بإرجاع إحصاءات الانحدار الإضافية؛ نتيجة لذلك، يتم إرجاع الصفيف التالي {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}.

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

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

الإحصاء

الوصف

se1,se2,...,sen

قيم الخطأ المعيارية للمُعاملات m1,m2,...,mn.

seb

قيمة الخطأ المعياري للثابت b ‏(seb = #N/A عندما const تساوي FALSE).

r2

مُعامل التحديد. مقارنة قيم y المقدرة والفعلية، وتتراوح بالقيمة من صفر إلى 1. إذا كانت قيمتها 1، يوجد ارتباط تام في العينة — لا يوجد فرق بين قيمة y المقدرة وقيمة y الفعلية. ومن ناحية أخرى، إذا كانت قيمة معامل التحديد 0، فلا تفيد معادلة الانحدار في التكهن بقيمة y. للحصول على معلومات حول كيفية حساب r2، راجع "ملاحظات" لاحقاً في هذا الموضوع.

sey

الخطأ المعياري لتقدير y.

F

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

df

درجات الحرية. استخدم درجات الحرية لتساعدك في العثور على قيم F الهامة في جدول إحصائي. قارن القيم التي تجدها في الجدول بالإحصائية F التي تُرجعها الدالة LINEST لتحديد مستوى الثقة للنموذج. لمزيد من المعلومات حول كيفية حساب df، راجع "ملاحظات" لاحقاً في هذا الموضوع. يعرض المثال 4 كيفية استخدام F وdf.

ssreg

مجموع الانحدار للمربعات.

ssresid

باقي مجموع المربعات. لمزيد من المعلومات حول كيفية حساب ssreg وssresid، راجع "ملاحظات" لاحقاً في هذا الموضوع.

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

مفتاح إحصائيات الانحدار

ملاحظات

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

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

    التقاطع Y‏ (b‏)‎:
    التقاطع y لخطٍ ما، يُكتب عادة b، هو قيمة y في النقطة حيث يتقاطع الخط مع محور y.

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

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

    الميل:‏
    =INDEX‏(LINEST(known_y's,known_x's),1)

    التقاطع Y:‏
    =INDEX‏(LINEST(known_y's,known_x's),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>. كلما صغر باقي مجموع المربعات، مقارنة بإجمالي مجموع المربعات، زادت قيمة معامل التحديد r2، وهو مؤشر على كيفية قيام المعادلة الناتجة عن تحليل الانحدار بشرح العلاقة بين المتغيرات. قيمة r2 تساوي 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.

  • يجب إدخال الصيغ التي تُرجع صفائف كصيغ صفيف.

    ملاحظة: في Excel Online، لا يمكنك إنشاء صيغ صفائف.

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

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

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

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

    • تقوم كل من SLOPE وINTERCEPT بإرجاع قيمة الخطأ ‎.#DIV/0!‎ تم تصميم خوارزمية الدالتين 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 الاحتمال.

أمثلة

المثال 1: المنحدر وتقاطع Y

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

معطيات ص

معطيات س

1

0

9

4

5

2

7

3

النتيجة (الميل)

النتيجة (التقاطع y)

2

1

الصيغة (صيغة الصفيف في الخلايا A7:B7)

‎=LINEST(A2:A5,B2:B5,,FALSE)‎

مثال 2: انحدار خطي بسيط

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

الشهر

المبيعات

1

3100 ر.س.

2

4500 ر.س.

3

4400 ر.س.

4

5400 ر.س.

5

7500 ر.س.

6

8100 ر.س.

الصيغة

النتيجة

‎=SUM(LINEST(B1:B6, A1:A6)*{9,1})‎

11000 ر.س.

حساب المبيعات المقدّرة للشهر التاسع، على أساس المبيعات من شهر 1 وحتى شهر 6.

مثال 3: انحدار خطي متعدد

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

مساحة الطابق (x1)

المكاتب (x2)

المداخل (x3)

العمر (x4)

القيمة المقدرة (y)

2310

2

2

20

142000 ر.س.

2333

2

2

12

144000 ر.س.

2356

3

1,5

33

151000 ر.س.

2379

3

2

43

150000 ر.س.

2402

2

3

53

139000 ر.س.

2425

4

2

23

169000 ر.س.

2448

2

1,5

99

126000 ر.س.

2471

2

2

34

142900 ر.س.

2494

3

3

23

163000 ر.س.

2517

4

4

55

169000 ر.س.

2540

2

3

22

149000 ر.س.

‎-234,2371645

13,26801148

0,996747993

459,7536742

1732393319

الصيغة (صيغة الصفيف المدرجة في الخلايا A14:A18)

‎=LINEST(E2:E12,A2:D12,TRUE,TRUE)‎

مثال 4: استخدام إحصاءات F وr2

في المثال السابق، يكون معامل التحديد، أو r2، هو 0.099675 (انظر الخلية A17 في ناتج LINEST) الذي يشير إلى وجود علاقة قوية بين المتغيرات المستقلة وسعر البيع. يمكنك استخدام إحصائية F لتحديد ما إذا كانت تلك النتائج، مع قيمة r2 العالية هذه، قد حدثت بالصدفة.

افترض الآن أنه لا يوجد بالفعل علاقة بين المتغيرات، بل أنك قد جئت بمجرد عينة من 11 مبنى إداري تؤدي إلى عرض علاقة قوية للتحليل الإحصائي. يُستخدم المصطلح "ألفا" لاحتمال خطأ استنتاج وجود علاقة.

يمكن استخدام قيمتي F و df في ناتج الدالة LINEST لتحديد احتمال حدوث قيمة F العليا بالصدفة. يمكن مقارنة قيمة F مع القيم المهمة الموجودة في جداول توزيع F التي تم نشرها أو يمكن استخدام الدالة FDIST في Excel لحساب احتمال حدوث قيمة F العليا بالصدفة. يكون لتوزيع F المناسب درجتان من الحرية هما v1 وv2. إذا كان n هو عدد نقاط البيانات وكان const = TRUE أو تم حذفها، فإن v1 = n – df – 1 وv2 = df. (إذا كانت قيمة const تساوى = FALSE, فستكون عندئذٍ قيمة v 1 = n-df وv1 = n – df وv2 = df‏). ستُرجع الدالة FDIST — بناء جملتها FDIST‎‎(F,v1,v2)‎ — احتمال حدوث قيمة F العليا بالصدفة. في هذا المثال، df = 6 (الخلية B18) و F = 459.753674 (الخلية A18).

بفرض أن قيمة ألفا هي 0.05، وv1 = 11 – 6 – 1 = 4 وv2 = 6، يكون المستوى المهم للقيمة F هو 4.53. ولأن F = 459.753674 أكبر بكثير من 4.53، فمن غير المحتمل أن تكون قيمة F العالية قد حدثت بالصدفة. (إذا كانت ألفا = 0.05، يجب رفض فرضية أنه لا توجد علاقة بين known_y’s وknown_x’s عندما تتجاوز F الحد الحرج، 4.53). يمكن استخدام الدلالة FDIST في تطبيق Excel للحصول على احتمال حدوث قيمة F العليا بالصدفة. فعلى سبيل المثال، FDIST(459.753674, 4, 6) = 1.37E-7، هي احتمال ضئيل للغاية. يمكنك الاستنتاج إما بالحصول على المستوى المهم لقيمة F في جدول أو باستخدام الدالة FDIST، وتفيد معادلة الانحدار في التنبؤ بالقيم المقدرة للمباني الإدارية في هذه المنطقة. تذكر أنه من الضروري استخدام القيم الصحيحة المحسوبة للقيمتين v1 و v2 في الفقرة السابقة.

مثال 5 ـ حساب إحصاءات t

يحدد اختبار افتراضي آخر ما إذا كان كل معامل ميل مفيداً في تقدير القيمة المقدرة لمبنى إداري في المثال 3. فعلى سبيل المثال، لاختبار معامل العمر للأهمية الإحصائية، اقسم -234.24 (معامل ميل العمر) على 13.268 (الخطأ المعياري المقدر لمعاملات العمر في الخلية A15). ما يلي قيمة t الملحوظة:

t = m4 ÷ se4 = -234,24 ÷ 13,268 = -17,7

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

إذا قمت بمراجعة جدول في دليل إحصاءات، فستجد أن قيمة t الحرجة ثنائية الطرف، مع 6 درجات للحرية وألفا=0.05 تكون 2.447. كما يمكن الحصول على هذه القيمة الحرجة باستخدام الدالة TINV في Excel. TINV (0.05,6) = 2.447. لأن القيمة المطلقة لـ t، وهي (17.7) أكبر من 2.447، يصبح العمر متغيراً مهماً عند تقدير القيمة المقدرة لمبنى إداري. يمكن اختبار كل متغير من المتغيرات المستقلة الأخرى لمعرفة الأهمية الإحصائية بطريقة مماثلة. فيما يلي قيم t الملاحظة لكل من المتغيرات المستقلة.

المتغير

قيمة t الملحوظة

مساحة الطابق

5,1

عدد المكاتب

31,3

عدد المداخل

4,8

العمر

17,7

تحتوي كافة تلك القيم على قيم مطلقة أكبر من 2.447؛ لذلك فإن كافة المتغيرات المستخدمة في معادلة الانحدار مفيدة في توقع القيمة المقدرة للمباني الإدارية في هذه المنطقة.

توسيع المهارات
استكشاف التدريب
الحصول على الميزات الجديدة أولاً
الانضمام إلى Office Insider

هل كانت المعلومات مفيدة؟

نشكرك على ملاحظاتك!

شكراً لك على الملاحظات! يبدو أنه من المفيد إيصالك بأحد وكلاء دعم Office لدينا.

×