نموذج الانحراف المعياري في التفوق. حساب التشتت والانحراف المعياري (المعياري) ومعامل الاختلاف في برنامج Excel

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

الحد الأقصى والحد الأدنى للقيمة

متوسط ​​الانحراف الخطي

متوسط ​​الانحراف الخطي هو متوسط ​​الانحرافات المطلقة (المعيارية) في مجموعة البيانات التي تم تحليلها. الصيغة الرياضية هي:

أ- متوسط ​​الانحراف الخطي،

X- تحليل المؤشر،

X̅- متوسط ​​قيمة المؤشر،

ن

في Excel تسمى هذه الوظيفة سروتكل.

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

تشتت

(الوحدة 111)

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

ق 2- تباين العينة المحسوب من بيانات الرصد،

X- القيم الفردية،

X̅- الوسط الحسابي للعينة،

ن– عدد القيم في مجموعة البيانات التي تم تحليلها.

وظيفة Excel المقابلة هي ديسب.ز. عند تحليل عينات صغيرة نسبيًا (تصل إلى حوالي 30 ملاحظة)، يجب عليك استخدام، والتي يتم حسابها باستخدام الصيغة التالية.

الفرق، كما ترون، هو فقط في المقام. يحتوي Excel على وظيفة لحساب التباين غير المتحيز للعينة ديسب.ب.

حدد الخيار المطلوب (عام أو انتقائي)، وحدد النطاق، ثم انقر فوق الزر "موافق". قد تكون القيمة الناتجة كبيرة جدًا بسبب التربيع الأولي للانحرافات. يعد التشتت في الإحصائيات مؤشرًا مهمًا جدًا، لكنه لا يستخدم عادة في شكل نقي، ولمزيد من الحسابات.

الانحراف المعياري

الانحراف المعياري (RMS) هو جذر التباين. ويسمى هذا المؤشر أيضًا الانحراف المعياري ويتم حسابه باستخدام الصيغة:

من قبل عامة السكان

عن طريق العينة

يمكنك ببساطة أخذ جذر التباين، ولكن لدى Excel وظائف جاهزة للانحراف المعياري: STDEV.Gو STDEV.V(للمجتمع العام والعينة على التوالي).

وأكرر أن الانحراف المعياري والمعياري مترادفان.

بعد ذلك، كالعادة، حدد النطاق المطلوب وانقر على "موافق". يحتوي الانحراف المعياري على نفس وحدات القياس مثل المؤشر الذي تم تحليله، وبالتالي فهو قابل للمقارنة بالبيانات الأصلية. المزيد عن هذا أدناه.

معامل الاختلاف

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

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

STANDARDDEVIATION.G()/المتوسط()

تتم الإشارة إلى نطاق البيانات بين قوسين. إذا لزم الأمر، استخدم نموذج الانحراف المعياري (STDEV.B).

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

يمكنك أيضًا تغيير التنسيق عن طريق الاختيار من قائمة السياق بعد تحديد الخلية المطلوبة والنقر بزر الماوس الأيمن.

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

معامل التذبذب

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

يُظهر معامل التذبذب مدى التباين بالنسبة للمتوسط، والذي يمكن استخدامه أيضًا لمقارنة مجموعات البيانات المختلفة.

بشكل عام، باستخدام برنامج 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

دعونا نحسب فيآنسةاكسلتباين العينة والانحراف المعياري. وسنقوم أيضًا بحساب تباين المتغير العشوائي إذا كان توزيعه معروفًا.

دعونا نفكر أولا تشتت، ثم الانحراف المعياري.

تباين العينة

تباين العينة (تباين العينة,عينةالتباين) يميز انتشار القيم في المصفوفة بالنسبة إلى .

جميع الصيغ الثلاث متكافئة رياضيا.

ومن الصيغة الأولى يتضح ذلك تباين العينةهو مجموع الانحرافات التربيعية لكل قيمة في المصفوفة من المتوسطمقسومًا على حجم العينة ناقص 1.

الفروق عيناتيتم استخدام الدالة DISP() باللغة الإنجليزية. الاسم VAR، أي. فارانس. من الإصدار MS EXCEL 2010، يوصى باستخدام التناظرية DISP.V()، باللغة الإنجليزية. اسم VARS، أي. نموذج التباين. بالإضافة إلى ذلك، بدءًا من إصدار MS EXCEL 2010، توجد وظيفة DISP.Г() باللغة الإنجليزية. الاسم VARP، أي. التباين السكاني، الذي يحسب تشتتل سكان. يعود الاختلاف بالكامل إلى المقام: بدلاً من n-1 مثل DISP.V()، يحتوي DISP.G() على n فقط في المقام. قبل MS EXCEL 2010، تم استخدام الدالة VAR() لحساب تباين المحتوى.

تباين العينة
=QUADROTCL(عينة)/(COUNT(عينة)-1)
=(SUM(عينة)-COUNT(عينة)*المتوسط(عينة)^2)/ (COUNT(عينة)-1)- الصيغة المعتادة
=SUM((العينة -المتوسط(العينة))^2)/ (COUNT(العينة)-1) –

تباين العينةيساوي 0، فقط إذا كانت جميع القيم متساوية مع بعضها البعض، وبالتالي متساوية متوسط ​​القيمة. عادة، كلما كانت القيمة أكبر الفروقكلما زاد انتشار القيم في المصفوفة.

تباين العينةهو تقدير نقطة الفروقتوزيع المتغير العشوائي الذي تم تكوينه منه عينة. حول البناء فترات الثقةعند التقييم الفروقيمكن قراءتها في المقال.

تباين متغير عشوائي

لحساب تشتتالمتغير العشوائي، عليك أن تعرفه.

ل الفروقغالبًا ما يُشار إلى المتغير العشوائي X بـ Var(X). تشتتيساوي مربع الانحراف عن المتوسط ​​E(X): Var(X)=E[(X-E(X)) 2 ]

تشتتتحسب بواسطة الصيغة:

حيث x i هي القيمة التي يمكن أن يأخذها المتغير العشوائي، و μ هي القيمة المتوسطة ()، و p(x) هو احتمال أن يأخذ المتغير العشوائي القيمة x.

إذا كان المتغير العشوائي يحتوي على تشتتتحسب بواسطة الصيغة:

البعد الفروقيتوافق مع مربع وحدة قياس القيم الأصلية. على سبيل المثال، إذا كانت القيم في العينة تمثل قياسات الوزن الجزئي (بالكجم)، فإن بعد التباين سيكون كجم 2 . قد يكون من الصعب تفسير ذلك، لذا لوصف انتشار القيم، قيمة تساوي الجذر التربيعي لـ الفروق – الانحراف المعياري.

بعض الخصائص الفروق:

Var(X+a)=Var(X)، حيث X متغير عشوائي وa ثابت.

فار(aХ)=أ 2 فار(X)

فار(X)=E[(X-E(X)) 2 ]=E=E(X 2)-E(2*X*E(X))+(E(X)) 2 =E(X 2)- 2*E(X)*E(X)+(E(X)) 2 =E(X 2)-(E(X)) 2

يتم استخدام خاصية التشتت هذه في مقالة عن الانحدار الخطي.

Var(X+Y)=Var(X) + Var(Y) + 2*Cov(X;Y)، حيث X وY متغيران عشوائيان، Cov(X;Y) هو التباين المشترك لهذه المتغيرات العشوائية.

إذا كانت المتغيرات العشوائية مستقلة، فإنها التغايريساوي 0، وبالتالي Var(X+Y)=Var(X)+Var(Y). يتم استخدام خاصية التشتت هذه في الاشتقاق.

دعونا نبين أنه بالنسبة للكميات المستقلة Var(X-Y)=Var(X+Y). في الواقع، Var(X-Y)= Var(X-Y)= Var(X+(-Y))= Var(X)+Var(-Y)= Var(X)+Var(-Y)= Var(X)+(- 1) 2 فار(Y)= فار(X)+فار(Y)= فار(X+Y). يتم استخدام خاصية التشتت هذه في البناء.

عينة الانحراف المعياري

عينة الانحراف المعياريهو مقياس لمدى انتشار القيم في العينة بالنسبة إلى قيمها.

حسب التعريف، الانحراف المعيارييساوي الجذر التربيعي ل الفروق:

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

دعونا نحسب الانحراف المعياري لعينتين: (1؛ 5؛ 9) و (1001؛ 1005؛ 1009). في كلتا الحالتين، ق = 4. ومن الواضح أن نسبة الانحراف المعياري إلى قيم المصفوفة تختلف بشكل كبير بين العينات. لمثل هذه الحالات يتم استخدامه معامل الاختلاف(معامل التباين، السيرة الذاتية) - النسبة الانحراف المعياريإلى المتوسط الحساب، معبرا عنها كنسبة مئوية.

في MS EXCEL 2007 والإصدارات السابقة للحساب عينة الانحراف المعيارييتم استخدام الدالة =STDEVAL() باللغة الإنجليزية. اسم STDEV، أي. الانحراف المعياري من إصدار MS EXCEL 2010، يوصى باستخدام نظيره =STANDDEV.B() باللغة الإنجليزية. اسم STDEV.S، أي. عينة الانحراف المعياري.

بالإضافة إلى ذلك، بدءًا من إصدار MS EXCEL 2010، توجد وظيفة STANDARDEV.G() باللغة الإنجليزية. اسم STDEV.P، أي. السكان DEViation القياسي، الذي يحسب الانحراف المعياريل سكان. يعود الاختلاف بالكامل إلى المقام: بدلاً من n-1 كما في STANDARDEV.V()، يحتوي STANDARDEVAL.G() على n فقط في المقام.

الانحراف المعيارييمكن أيضًا حسابه مباشرةً باستخدام الصيغ أدناه (انظر ملف المثال)
=ROOT(QUADROTCL(عينة)/(COUNT(عينة)-1))
=ROOT((SUM(Sample)-COUNT(Sample)*AVERAGE(Sample)^2)/(COUNT(Sample)-1))

تدابير أخرى للتشتت

تقوم الدالة SQUADROTCL () بالحساب باستخدام مجموع الانحرافات التربيعية للقيم منها متوسط. ستُرجع هذه الدالة نفس النتيجة مثل الصيغة =DISP.G( عينة)*يفحص( عينة) ، أين عينة- إشارة إلى نطاق يحتوي على مجموعة من قيم العينة (). يتم إجراء الحسابات في الدالة QUADROCL() وفقًا للصيغة:

تعتبر الدالة SROTCL() أيضًا مقياسًا لانتشار مجموعة البيانات. تقوم الدالة SROTCL () بحساب متوسط ​​القيم المطلقة لانحرافات القيم عنها متوسط. ستعيد هذه الوظيفة نفس نتيجة الصيغة =SUMPRODUCT(ABS(عينة-متوسط(عينة)))/COUNT(عينة)، أين عينة- رابط لنطاق يحتوي على مجموعة من قيم العينة.

يتم إجراء الحسابات في الدالة SROTCL () وفقًا للصيغة:

مساء الخير

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

يستخدم Excel العديد من الأشكال المختلفة لوظيفة التباين هذه:

النظرية الرياضية

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

ما الذي يفعله الانحراف المعياري بالضبط؟ ويقدر الانحراف المعياري للمتغير العشوائي X بالنسبة لتوقعاته الرياضية بناءً على تقدير غير متحيز لتباينه. أوافق على أن الأمر يبدو محيرًا، ولكن أعتقد أن الطلاب سيفهمون ما نتحدث عنه بالفعل!

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

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

التنفيذ العملي في برنامج Excel

حسنًا، دعونا الآن نبتعد عن النظرية المملة ونرى عمليًا كيف تعمل الدالة STANDARDEVAL. لن أتناول جميع أشكال دالة الانحراف المعياري في Excel؛ يكفي واحد، ولكن في الأمثلة. على سبيل المثال، دعونا نلقي نظرة على كيفية تحديد إحصائيات استقرار المبيعات.

أولاً، انظر إلى تهجئة الوظيفة، وكما ترون، فهي بسيطة جدًا:

الانحراف المعياري.G(_number1_;_number2_; ....)، حيث:

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

أولاً، نحتاج إلى حساب متوسط ​​القيمة حسب الشهر. سوف نستخدم الدالة AVERAGE لهذا ونحصل على الصيغة: = AVERAGE(C4:E4).
الآن، في الواقع، يمكننا العثور على الانحراف المعياري باستخدام الدالة STANDARDEVAL.G، والتي نحتاج إلى إدخال مبيعات المنتج لكل فترة. والنتيجة هي صيغة بالشكل التالي: =STANDARD DEVIATION.Г(C4;D4;E4).
حسنًا، تم إنجاز نصف العمل. والخطوة التالية هي تشكيل "التباين"، ويتم الحصول عليه عن طريق القسمة على القيمة المتوسطة والانحراف المعياري وتحويل النتيجة إلى نسب مئوية. نحصل على الجدول التالي:
حسنًا، لقد اكتملت الحسابات الأساسية، ولم يتبق سوى معرفة ما إذا كانت المبيعات مستقرة أم لا. دعونا نأخذ كشرط أن الانحرافات بنسبة 10% تعتبر مستقرة، ومن 10 إلى 25% تعتبر انحرافات صغيرة، ولكن أي شيء أعلى من 25% لم يعد مستقرًا. للحصول على النتيجة حسب الشروط سنستخدم طريقة منطقية وللحصول على النتيجة سنكتب الصيغة:

إذا (H4<0,1;"стабильно";ЕСЛИ(H4<0,25;"нормально";"не стабильно"))

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

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

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

استخدام VBA للدالة STDEV.Y

يمكن لأي شخص مهتم أتمتة حساباته باستخدام وحدات الماكرو واستخدام الوظيفة التالية:

الدالة MyStDevP(Arr) Dim x, aCnt&, aSum#, aAver#, tmp# لكل x In Arr aSum = aSum + x "احسب مجموع عناصر الصفيف aCnt = aCnt + 1" احسب عدد العناصر Next x aAver = aSum / aCnt "متوسط ​​القيمة لكل x In Arr tmp = tmp + (x - aAver) ^ 2 "احسب مجموع مربعات الفرق بين عناصر المصفوفة ومتوسط ​​القيمة Next x MyStDevP = Sqr(tmp / aCnt ) "احسب دالة النهاية STANDARDEV.G()

الدالة MyStDevP(Arr) خافت x، aCnt &، aSum #، aAver#، tmp# لكل x في Arr مجموع = مجموع + س "احسب مجموع عناصر المصفوفة

معامل الاختلاف هو مقارنة تشتت قيمتين عشوائيتين. الكميات لها وحدات قياس مما يؤدي إلى نتيجة قابلة للمقارنة. هذا المعامل ضروري لإعداد التحليل الإحصائي.

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

حساب الانحراف المعياري

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

تحضير البيانات: افتح الصفحة التي ستتم فيها الحسابات. في حالتنا، هذه صورة، ولكن من الممكن أن تكون أي ملف آخر. الشيء الرئيسي هو جمع المعلومات التي ستستخدمها في الجدول للحساب.

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

احفظ هذا المستند.

الآن دعنا ننتقل إلى الحساب نفسه. حدد خلية باستخدام المؤشربعد القيمة الأخيرة التي تم إدخالها أدناه.

أدخل علامة "=" وأدخل الصيغة أدناه. علامة المساواة مطلوبة. وإلا فإن البرنامج لن يقوم بحساب البيانات المقترحة. يتم إدخال الصيغة بدون مسافات.

ستعرض الأداة المساعدة أسماء العديد من الصيغ. يختار " الانحراف المعياري" هذه هي الصيغة لحساب الانحراف المعياري. هناك نوعان من الحساب:

• مع حساب العينة.
• مع الحساب على أساس عموم السكان.

عن طريق اختيار واحد منهم، حدد نطاق البيانات. ستبدو الصيغة المدخلة بالكامل كما يلي: "=STDEV (B2: B5)".

ثم اضغط على الزر " يدخل" ستظهر البيانات المستلمة في العنصر المحدد.

حساب الوسط الحسابي

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

• سيكون هناك فقط حدد النطاقوانقر على زر "أدخل". وستعرض الخلية الآن نتيجة البيانات المأخوذة أعلاه.

حساب معامل الاختلاف

صيغة لحساب معامل الاختلاف:

V= S/X، حيث S هو الانحراف المعياري وX هو المتوسط.

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

للقيام بذلك، افتح Excel، املأ حقلين حيث يجب عليك إدخال الأرقام الناتجة للانحراف المعياري والقيمة المتوسطة.

الآن حدد الخلية المخصصة للرقم لحساب التباين. افتح علامة التبويب " بيت"إذا لم يكن مفتوحا. انقر على الأداة " رقم" حدد تنسيق النسبة المئوية.

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

وهذه هي النتيجة بعد الضغط على "Enter":

يمكنك أيضًا استخدام الآلات الحاسبة عبر الإنترنت لحساب معامل التباين، على سبيل المثال، Planetcalc.ru وallcalc.ru. يكفي إدخال الأرقام اللازمة وبدء الحساب، وبعد ذلك ستتلقى المعلومات اللازمة.

الانحراف المعياري

يتم حل الانحراف المعياري في Excel باستخدام صيغتين:

وبكلمات بسيطة، يتم استخراج جذر التباين. كيفية حساب التباين تمت مناقشته أدناه.

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

معامل التذبذب

وتسمى نسبة نطاق التباين إلى المتوسط ​​بمعامل التذبذب. لا توجد صيغ جاهزة في Excel، لذلك تحتاج إلى تجميعهاعدة وظائف في واحدة.

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

تشتت

التباين هو وظيفة من خلالها وصف انتشار البياناتحول التوقعات الرياضية. يتم حسابها باستخدام المعادلة التالية:

تأخذ المتغيرات القيم التالية:

يحتوي Excel على وظيفتين تحددان التباين:

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

الحد الأقصى والحد الأدنى

هناك حاجة إلى الحد الأقصى والحد الأدنى حتى لا يتم البحث يدويًا بين عدد كبير من الأرقام عن الحد الأدنى أو الحد الأقصى للرقم.

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

أنت تفعل الشيء نفسه للحصول على الحد الأدنى. ما عليك سوى اختيار وظيفة "الحد الأدنى".