التباين هو مقياس للتشتت يصف الانحراف المقارن بين قيم البيانات والمتوسط. وهو مقياس التشتت الأكثر استخدامًا في الإحصائيات، ويتم حسابه عن طريق جمع وتربيع انحراف كل قيمة بيانات عن المتوسط. صيغة حساب التباين موضحة أدناه:
ق 2 - تباين العينة؛
x av - متوسط العينة؛
ن — حجم العينة (عدد قيم البيانات)،
(x i - x avg) هو الانحراف عن متوسط القيمة لكل قيمة في مجموعة البيانات.
لفهم الصيغة بشكل أفضل، دعونا نلقي نظرة على مثال. أنا لا أحب الطبخ حقًا، لذلك نادرًا ما أفعله. ومع ذلك، لكي لا أتضور جوعا، من وقت لآخر، يجب أن أذهب إلى الموقد لتنفيذ خطة تشبع الجسم بالبروتينات والدهون والكربوهيدرات. توضح مجموعة البيانات أدناه عدد المرات التي تقوم فيها رينات بالطهي كل شهر:
الخطوة الأولى في حساب التباين هي تحديد متوسط العينة، وهو في مثالنا 7.8 مرة شهريًا. ويمكن إجراء بقية الحسابات بشكل أسهل باستخدام الجدول التالي.
تبدو المرحلة النهائية لحساب التباين كما يلي:
بالنسبة لأولئك الذين يحبون إجراء جميع الحسابات دفعة واحدة، ستبدو المعادلة كما يلي:
استخدام طريقة العد الخام (مثال الطبخ)
هناك طريقة أكثر فعالية لحساب التباين، تعرف باسم طريقة العد الأولي. على الرغم من أن المعادلة قد تبدو مرهقة للغاية للوهلة الأولى، إلا أنها في الواقع ليست مخيفة إلى هذا الحد. يمكنك التأكد من ذلك، ومن ثم تحديد الطريقة التي تفضلها.
هو مجموع كل قيمة بيانات بعد التربيع،
هو مربع مجموع كل قيم البيانات.
لا تفقد عقلك الآن. لنضع كل هذا في جدول وسترى أن هناك عددًا أقل من العمليات الحسابية المتضمنة مقارنة بالمثال السابق.
وكما ترون، كانت النتيجة هي نفسها عند استخدام الطريقة السابقة. تصبح مزايا هذه الطريقة واضحة مع زيادة حجم العينة (ن).
حساب التباين في Excel
كما خمنت على الأرجح، يحتوي برنامج Excel على صيغة تسمح لك بحساب التباين. علاوة على ذلك، بدءًا من Excel 2010، يمكنك العثور على 4 أنواع من صيغ التباين:
1) VARIANCE.V - يُرجع تباين العينة. يتم تجاهل القيم المنطقية والنص.
2) DISP.G - إرجاع تباين المحتوى. يتم تجاهل القيم المنطقية والنص.
3) التباين - يُرجع تباين العينة، مع مراعاة القيم المنطقية والنصية.
4) التباين - إرجاع تباين المحتوى، مع مراعاة القيم المنطقية والنصية.
أولا، دعونا نفهم الفرق بين العينة والمجتمع. الغرض من الإحصائيات الوصفية هو تلخيص البيانات أو عرضها حتى تتمكن بسرعة من الحصول على الصورة الكبيرة، أو نظرة عامة إذا جاز التعبير. يسمح لك الاستدلال الإحصائي بإجراء استنتاجات حول السكان بناءً على عينة من البيانات من هذا السكان. يمثل السكان جميع النتائج أو القياسات المحتملة التي تهمنا. العينة هي مجموعة فرعية من السكان.
على سبيل المثال، نحن مهتمون بمجموعة من الطلاب من إحدى الجامعات الروسية ونحتاج إلى تحديد متوسط درجات المجموعة. يمكننا حساب متوسط أداء الطلاب، ومن ثم سيكون الرقم الناتج معلمة، حيث سيشارك جميع السكان في حساباتنا. ومع ذلك، إذا أردنا حساب المعدل التراكمي لجميع الطلاب في بلدنا، فستكون هذه المجموعة هي عينتنا.
الفرق في صيغة حساب التباين بين العينة والمجتمع هو المقام. حيث بالنسبة للعينة ستكون مساوية (n-1)، وبالنسبة لعموم السكان فقط n.
الآن دعونا نلقي نظرة على وظائف حساب التباين مع النهايات أ،ينص الوصف على أن القيم النصية والمنطقية تؤخذ بعين الاعتبار في الحساب. في هذه الحالة، عند حساب تباين مجموعة بيانات معينة حيث تحدث قيم غير رقمية، سيقوم Excel بتفسير القيم النصية والقيم المنطقية الخاطئة على أنها تساوي 0، والقيم المنطقية الحقيقية تساوي 1.
لذا، إذا كان لديك مصفوفة بيانات، فلن يكون حساب تباينها أمرًا صعبًا باستخدام إحدى وظائف Excel المذكورة أعلاه.
تستخدم الإحصائيات عددًا كبيرًا من المؤشرات، وأحدها هو حساب التباين في برنامج 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، لذلك فالأمر بسيط للغاية.
بعد ذلك، ستظهر أمامك نافذة ستحتاج فيها إلى إدخال البيانات للحساب. على وجه الخصوص، يجب إدخال رقمين في حقول خاصة، وبعد ذلك سيقوم البرنامج نفسه بحساب الانحراف المعياري للعينة.
مما لا شك فيه أن الصيغ والحسابات الرياضية هي مسألة معقدة إلى حد ما، ولا يستطيع جميع المستخدمين التعامل معها على الفور. ومع ذلك، إذا قمت بحفر أعمق قليلا ونظرت إلى السؤال بمزيد من التفاصيل، فقد اتضح أنه ليس كل شيء حزينا للغاية. أتمنى أن تقتنع بذلك باستخدام مثال حساب الانحراف المعياري.
يشير مفهوم نسبة الانحراف إلى الفرق بين قيمتين رقميتين كنسبة مئوية. دعونا نعطي مثالا محددا: لنفترض أنه في يوم من الأيام تم بيع 120 حبة من مستودع الجملة، وفي اليوم التالي - 150 قطعة. الفرق في حجم المبيعات واضح؛ فقد تم بيع 30 قرصًا إضافيًا في اليوم التالي. عند طرح الرقم 120 من 150 نحصل على انحراف يساوي الرقم +30. السؤال الذي يطرح نفسه: ما هي نسبة الانحراف؟
كيفية حساب نسبة الانحراف في إكسل
يتم حساب نسبة الانحراف عن طريق طرح القيمة القديمة من القيمة الجديدة، ثم قسمة النتيجة على القيمة القديمة. يجب أن يتم عرض نتيجة حساب الصيغة في Excel بتنسيق النسبة المئوية للخلية. في هذا المثال، صيغة الحساب هي كما يلي (150-120)/120=25%. من السهل التحقق من الصيغة: 120+25%=150.
انتبه!إذا قمنا بتبديل الأرقام القديمة والجديدة، فسيكون لدينا صيغة لحساب العلامات.
يوضح الشكل أدناه مثالاً لكيفية تقديم الحساب أعلاه كصيغة Excel. تحسب الصيغة الموجودة في الخلية D2 النسبة المئوية للانحراف بين قيم المبيعات للعام الحالي والعام الماضي: =(C2-B2)/B2
من المهم الانتباه إلى وجود الأقواس في هذه الصيغة. بشكل افتراضي، في Excel، يكون لعملية القسمة دائمًا الأولوية على عملية الطرح. لذلك، إذا لم نضع قوسين، فسيتم تقسيم القيمة أولاً، ثم سيتم طرح قيمة أخرى منها. مثل هذا الحساب (بدون وجود الأقواس) سيكون خاطئًا. يؤدي إغلاق الجزء الأول من العملية الحسابية في صيغة ذات أقواس إلى رفع أولوية عملية الطرح تلقائيًا فوق عملية القسمة.
أدخل الصيغة بشكل صحيح بين قوسين في الخلية D2، ثم انسخها ببساطة إلى الخلايا الفارغة المتبقية في النطاق D2:D5. لنسخ الصيغة بأسرع طريقة، ما عليك سوى تحريك مؤشر الماوس إلى علامة مؤشر لوحة المفاتيح (إلى الزاوية اليمنى السفلية) بحيث يتغير مؤشر الماوس من سهم إلى علامة متقاطعة سوداء. ثم انقر نقرًا مزدوجًا فقط باستخدام زر الماوس الأيسر وسيقوم Excel تلقائيًا بملء الخلايا الفارغة بالصيغة وسيحدد النطاق من D2:D5، الذي يجب ملؤه حتى الخلية D5 وليس أكثر. يعد هذا اختراقًا مفيدًا للغاية لبرنامج Excel.
صيغة بديلة لحساب النسبة المئوية للانحراف في Excel
في صيغة بديلة تحسب الانحراف النسبي لقيم المبيعات عن العام الحالي، يتم القسمة فورًا على قيم مبيعات العام السابق، وعندها فقط يتم طرح واحد من النتيجة: =C2/B2-1.
وكما ترون في الشكل، فإن نتيجة حساب الصيغة البديلة هي نفسها كما في الصيغة السابقة، وبالتالي فهي صحيحة. لكن الصيغة البديلة أسهل في الكتابة، رغم أنها قد تكون أكثر صعوبة على البعض في قراءتها لفهم مبدأ عملها. أو قد يكون من الصعب فهم القيمة التي تنتجها صيغة معينة كنتيجة لعملية حسابية إذا لم يتم التوقيع عليها.
العيب الوحيد لهذه الصيغة البديلة هو عدم القدرة على حساب نسبة الانحراف للأعداد السالبة في البسط أو في البديل. حتى لو استخدمنا الدالة ABS في الصيغة، فإن الصيغة ستعيد نتيجة خاطئة إذا كان الرقم الموجود في البديل سالبًا.
نظرًا لأن برنامج 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 (نطاق القيم) / المتوسط (نطاق القيم).
يتم حساب معامل الاختلاف كنسبة مئوية. لذلك، قمنا بتعيين تنسيق النسبة المئوية في الخلية.