كيفية العثور على الانحراف المعياري في إكسل. وظيفة الانحراف المعياري

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

حساب التباين

تباين المتغير العشوائي هو التوقع الرياضي لمربع انحراف المتغير العشوائي عن توقعه الرياضي.

نحن نحسب على أساس عموم السكان

لحساب حصيرة. في انتظار أن يستخدم البرنامج الدالة DISP.G، ويبدو بناء الجملة الخاص به كما يلي: “=DISP.G(Number1;Number2;…)”.

يمكن استخدام 255 وسيطة كحد أقصى، لا أكثر. يمكن أن تكون الحجج الأعداد الأوليةأو روابط إلى الخلايا التي يشار إليها. دعونا نلقي نظرة على كيفية حساب التباين في Microsoft Excel:

1. الخطوة الأولى هي تحديد الخلية التي سيتم عرض نتيجة الحساب فيها، ثم النقر فوق الزر "إدراج دالة".

2. سيتم فتح غلاف إدارة الوظائف. هناك تحتاج إلى البحث عن وظيفة "DISP.G"، والتي يمكن أن تكون ضمن فئة "إحصائية" أو "قائمة أبجدية كاملة". عندما يتم العثور عليه، حدده وانقر فوق "موافق".

3. سيتم فتح نافذة تحتوي على وسيطات الوظيفة. تحتاج فيه إلى تحديد السطر "رقم 1" وعلى الورقة حدد نطاق الخلايا مع سلسلة الأرقام.

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

هذه هي الطريقة التي يمكنك من خلالها بسهولة العثور على التباين في Excel.

نقوم بإجراء الحسابات على أساس العينة

في هذه الحالة، يتم حساب تباين العينة في برنامج Excel مع الإشارة إلى المقام ليس العدد الإجمالي للأرقام، بل رقم أقل. ويتم ذلك لخطأ أصغر باستخدام الدالة الخاصة DISP.V، التي يكون تركيبها =DISP.V(Number1;Number2;...). خوارزمية الإجراءات:

• كما في الطريقة السابقة، تحتاج إلى تحديد خلية للنتيجة.
• في معالج الوظائف، يجب أن تجد "DISP.B" ضمن فئة "القائمة الأبجدية الكاملة" أو فئة "الإحصائيات".

• بعد ذلك، ستظهر نافذة، وعليك المتابعة بنفس الطريقة كما في الطريقة السابقة.

فيديو: حساب التباين في برنامج Excel

خاتمة

يتم حساب التباين في Excel بكل بساطة وأسرع بكثير وأكثر ملاءمة من القيام بذلك يدويًا، لأن وظيفة التوقع الرياضي معقدة للغاية ويمكن أن يستغرق حسابها الكثير من الوقت والجهد.

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

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

ما هذا

لنبدأ بالنظرية. عادة ما يسمى الانحراف المعياري الجذر التربيعي، تم الحصول عليها من المتوسط ​​الحسابي لجميع الفروق المربعة بين الكميات المتاحة، وكذلك الوسط الحسابي لها.

بالمناسبة، عادة ما تسمى هذه القيمة بالحرف اليوناني "سيجما". يتم حساب الانحراف المعياري باستخدام الصيغة STANDARDEVAL وفقًا لذلك، يقوم البرنامج بذلك للمستخدم نفسه. النقطة هيهذا المفهوم

هو تحديد درجة تباين الأداة، أي أنها بطريقتها الخاصة مؤشر أصلاً من الإحصاء الوصفي. ويحدد التغيرات في تقلب الأداة خلال أي فترة زمنية. يمكن استخدام صيغ STDEV لتقدير الانحراف المعياري للعينة، مع تجاهل القيم المنطقية والنصية.

صيغة

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

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

فيديو للمساعدة

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

ق 2 - تباين العينة؛

x av - متوسط ​​العينة؛

ن — حجم العينة (عدد قيم البيانات)،

(x i - x avg) هو الانحراف عن متوسط ​​القيمة لكل قيمة في مجموعة البيانات.

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

الخطوة الأولى في حساب التباين هي تحديد متوسط ​​العينة، وهو في مثالنا 7.8 مرة شهريًا. ويمكن إجراء بقية الحسابات بشكل أسهل باستخدام الجدول التالي.

تبدو المرحلة النهائية لحساب التباين كما يلي:

بالنسبة لأولئك الذين يحبون إجراء جميع الحسابات دفعة واحدة، ستبدو المعادلة كما يلي:

استخدام طريقة العد الخام (مثال الطبخ)

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

هو مجموع كل قيمة بيانات بعد التربيع،

هو مربع مجموع كل قيم البيانات.

لا تفقد عقلك الآن. لنضع كل هذا في جدول وسترى أن هناك عددًا أقل من العمليات الحسابية المتضمنة مقارنة بالمثال السابق.

وكما ترون، كانت النتيجة هي نفسها عند استخدام الطريقة السابقة. تصبح مزايا هذه الطريقة واضحة مع زيادة حجم العينة (n).

حساب التباين في Excel

كما خمنت على الأرجح، يحتوي برنامج Excel على صيغة تسمح لك بحساب التباين. علاوة على ذلك، بدءًا من Excel 2010، يمكنك العثور على 4 أنواع من صيغ التباين:

1) VARIANCE.V - يُرجع تباين العينة. يتم تجاهل القيم المنطقية والنص.

2) DISP.G - إرجاع تباين المحتوى. يتم تجاهل القيم المنطقية والنص.

3) التباين - يُرجع تباين العينة، مع مراعاة القيم المنطقية والنصية.

4) التباين - إرجاع تباين المحتوى، مع مراعاة القيم المنطقية والنصية.

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

على سبيل المثال، نحن مهتمون بمجموعة من الطلاب من إحدى الجامعات الروسية ونحتاج إلى تحديد متوسط ​​درجات المجموعة. يمكننا حساب متوسط ​​​​أداء الطلاب، ومن ثم سيكون الرقم الناتج معلمة، حيث سيشارك جميع السكان في حساباتنا. ومع ذلك، إذا أردنا حساب المعدل التراكمي لجميع الطلاب في بلدنا، فستكون هذه المجموعة هي عينتنا.

الفرق في صيغة حساب التباين بين العينة والمجتمع هو المقام. حيث بالنسبة للعينة ستكون مساوية (n-1)، وبالنسبة لعموم السكان فقط n.

الآن دعونا نلقي نظرة على وظائف حساب التباين مع النهايات أ،ينص الوصف على أن القيم النصية والمنطقية تؤخذ بعين الاعتبار في الحساب. في هذه الحالة، عند حساب تباين مجموعة بيانات معينة حيث تحدث قيم غير رقمية، سيقوم Excel بتفسير القيم النصية والقيم المنطقية الخاطئة على أنها تساوي 0، والقيم المنطقية الحقيقية تساوي 1.

لذا، إذا كان لديك مصفوفة بيانات، فلن يكون حساب تباينها أمرًا صعبًا باستخدام إحدى وظائف Excel المذكورة أعلاه.

تدخل الإدارة ضروري لتحديد أسباب الانحرافات.

لإنشاء مخطط التحكم، أستخدم البيانات الأولية والمتوسط ​​(μ) والانحراف المعياري (σ). في Excel: μ = AVERAGE($F$3:$F$15)، σ = STANDARDEVAL($F$3:$F$15)

يتضمن مخطط التحكم نفسه: البيانات الأولية والمتوسط ​​(μ) والحد الأدنى للتحكم (μ – 2σ) والحد الأعلى للتحكم (μ + 2σ):

قم بتنزيل المذكرة بالتنسيق والأمثلة بالتنسيق

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

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

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

يتيح لك برنامج Excel إنشاء خط اتجاه باستخدام وظيفة FORECAST. نحن بحاجة إلى صف إضافي A3:A15 القيم المعروفة لـ Xكانت سلسلة مستمرة (لا تشكل أرقام الكتل مثل هذه السلسلة المستمرة). بدلاً من القيمة المتوسطة في العمود H، نقدم الدالة FORECAST:

يتم حساب الانحراف المعياري σ (دالة STDEVAL في Excel) باستخدام الصيغة:

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

يمكن لصيغة الصفيف إرجاع قيمة واحدة أو صفيف. في حالتنا، ستُرجع صيغة الصفيف قيمة واحدة:

دعونا نلقي نظرة فاحصة على كيفية عمل صيغة الصفيف في الخلية G3

SUM(($F$3:$F$15-$H$3:$H$15)^2) يحدد مجموع الفروق المربعة؛ في الواقع، تحسب الصيغة المجموع التالي = (F3 – H3) 2 + (F4 – H4) 2 + … + (F15 – H15) 2

COUNTA($F$3:$F$15) – عدد القيم في النطاق F3:F15

SQRT(SUM(($F$3:$F$15-$H$3:$H$15)^2)/(COUNTA($F$3:$F$15)-1)) = σ

قيمة 6.2% هي نقطة حد التحكم الأدنى = 8.3% – 2σ

تشير علامات الاقتباس المتعرجة على جانبي الصيغة إلى أنها صيغة صفيف. لإنشاء صيغة صفيف، بعد إدخال الصيغة في الخلية G3:

H4 – 2*ROOT(SUM(($F$3:$F$15-$H$3:$H$15)^2)/(COUNT($F$3:$F$15)-1))

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

يمكن "سحب" صيغة الصفيف التي ترجع قيمة واحدة مثل الصيغة العادية.

ونتيجة لذلك، حصلنا على مخطط تحكم مصمم للبيانات التي تميل إلى الانخفاض

ملاحظة: بعد كتابة الملاحظة، تمكنت من تحسين الصيغ المستخدمة لحساب الانحراف المعياري للبيانات الشائعة. ويمكنك الاطلاع عليها في ملف Excel

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

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

كيفية العثور على الوسط الحسابي للأرقام؟

للعثور على الوسط الحسابي، تحتاج إلى جمع جميع الأرقام في المجموعة وتقسيم المجموع على الكمية. على سبيل المثال، درجات الطالب في علوم الكمبيوتر: 3، 4، 3، 5، 5. ما يتضمنه الربع: 4. وجدنا الوسط الحسابي باستخدام الصيغة: =(3+4+3+5+5) /5.

كيف يمكنك القيام بذلك بسرعة باستخدام وظائف Excel؟ لنأخذ على سبيل المثال سلسلة من الأرقام العشوائية في سلسلة:

أو: أنشئ الخلية النشطة وأدخل الصيغة يدويًا: =AVERAGE(A1:A8).

الآن دعونا نرى ما يمكن أن تفعله الدالة AVERAGE أيضًا.

دعونا نجد الوسط الحسابي لأول رقمين وآخر ثلاثة أرقام. الصيغة: = المتوسط ​​(A1:B1،F1:H1). نتيجة:



الحالة متوسطة

يمكن أن يكون شرط إيجاد الوسط الحسابي معيارًا رقميًا أو معيارًا نصيًا. سوف نستخدم الدالة: =AVERAGEIF().

أوجد الوسط الحسابي للأعداد الأكبر من أو تساوي 10.

الوظيفة: =AVERAGEIF(A1:A8،">=10")

نتيجة استخدام الدالة AVERAGEIF بالشرط ">=10":

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

انتباه! يمكن تحديد معيار البحث في الخلية. وقم بعمل رابط لها في الصيغة.

دعونا نجد القيمة المتوسطة للأرقام باستخدام معيار النص. على سبيل المثال متوسط ​​مبيعات المنتج “الجداول”.

ستبدو الدالة كما يلي: =AVERAGEIF($A$2:$A$12,A7,$B$2:$B$12). النطاق - عمود بأسماء المنتجات. معيار البحث هو رابط لخلية تحتوي على كلمة "جداول" (يمكنك إدراج كلمة "جداول" بدلاً من الرابط A7). نطاق المتوسط ​​– تلك الخلايا التي سيتم أخذ البيانات منها لحساب القيمة المتوسطة.

ونتيجة لحساب الدالة نحصل على القيمة التالية:

انتباه! بالنسبة لمعيار النص (الشرط)، يجب تحديد نطاق المتوسط.

كيفية حساب متوسط ​​السعر المرجح في إكسيل؟

كيف عرفنا متوسط ​​السعر المرجح؟

الصيغة: =SUMPRODUCT(C2:C12,B2:B12)/SUM(C2:C12).

باستخدام صيغة SUMPRODUCT، يمكننا معرفة إجمالي الإيرادات بعد بيع الكمية الكاملة من البضائع. والدالة SUM تلخص كمية البضائع. وبقسمة إجمالي الإيرادات من بيع البضائع على إجمالي عدد وحدات البضائع، وجدنا متوسط ​​السعر المرجح. يأخذ هذا المؤشر في الاعتبار "وزن" كل سعر. حصتها في الكتلة الإجمالية للقيم.

الانحراف المعياري: الصيغة في Excel

هناك انحرافات معيارية لعموم السكان وللعينة. وفي الحالة الأولى، هذا هو أصل التباين العام. وفي الثاني من تباين العينة.

لحساب هذا المؤشر الإحصائي، يتم تجميع صيغة التشتت. ويستخرج منه الجذر. ولكن في Excel هناك وظيفة جاهزة للعثور على الانحراف المعياري.

يرتبط الانحراف المعياري بمقياس البيانات المصدر. وهذا لا يكفي للحصول على تمثيل مجازي لتنوع النطاق الذي تم تحليله. للحصول على المستوى النسبي لتشتت البيانات، يتم حساب معامل الاختلاف:

الانحراف المعياري / المتوسط ​​الحسابي

تبدو الصيغة في Excel كما يلي:

STDEV (نطاق القيم) / المتوسط ​​(نطاق القيم).

يتم حساب معامل الاختلاف كنسبة مئوية. لذلك، قمنا بتعيين تنسيق النسبة المئوية في الخلية.

فيسبوك

تغريد

فكونتاكتي

زملاء الدراسة

جوجل+