تصميم الجداول التكرارية بواسطة الاكسل

[محمد حجازي]

السلام عليكم ...

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

سنبدأ اليوم بأساسيات الإحصاء ، وسنبين طريقة إنشاء الجداول التكرارية بواسطة الاكسل :

من المحتمل جداً أنك تساءلت يوماً من أين أبدأ؟ ، و ذلك عندما طلب منك دراسة مجموعة ضخـمــــــــــــــــة من البيانات الخام العائدة لظاهرة معينة !!!

الجواب علي سؤالك سيكون بتلخيص هذه البيانات أولاً و تفريغها في جدول توزيع تكراري ، ولكن كيف؟

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

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

لتوضيح الفكرة نورد الحالة التالية :

في ما يلي توجد درجات لستين طالباً لمقرر معين في أحد الجامعات العربية :

60,51,48,98,64,84,79,59,80,55

68,38,32,41,44,63,74,10,42,74

42,56,63,57,27,71,95,88,35,25

54,15,40,68,78,93,29,52,61,25

53,76,18,37,54,44,20,32,58,36

66,82,42,55,30,50,65,98,46,62

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

ولكن كيف نحدد هذه الفئات؟

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

مثلاً : الفئة الأولى محددة بالمجال :

 Bin1 : [0,10[

أي :

المجال السابق مفتوح بالنسبة لليمين و مغلق بالنسبة لليسار (الدائرة البيضاء تدل على عدم انتماء القيمة 10 إلى مجال الفئة Bin1 ، و الدائرة السوداء تدل على انتماء القيمة 0 إلى مجال الفئة Bin1). وهكذا بالنسبة لبقية الفئات :

Bin2 : [10,20[ 
Bin3 : [20,30[
Bin4 : [30,40[
Bin5 : [40,50[
Bin6 : [50,60[
Bin7 : [60,70[
Bin8 : [70,80[
Bin9 : [80,90[
Bin10 : [90,100]

لاحظ أخي بأنه من أجل عدم تقاطع الفئات المختلفة ، فإن المجال يكون مفتوحاً من الطرف الذي يكون فيه المجال مغلقاً من قبل الفئة الأخرى. بعد تحديد مجال الفئات تأتي مرحلة تفريغ البيانات على الفئات المحددة ، وذلك عن طريق رسم جدول توضع فيه الفئات مع التكرار المقابل لها (عدد المفردات التي تنتمي إليها) ، وذلك كما سنرى لاحقاً . عند إعداد الجداول التكرارية و تصنيف البيانات الخام يجب التقيد بالملاحظات التالية : 1.يجب أن لا يقل عدد الفئات عن خمس و لا يزيد عن عشرين فئة ، وذلك تبعاً لطبيعة البيانات الخام وحجمها. 2. يجب أن لا تكون الفئات متباعدة أو متداخلة أو متقاطعة (كما رأينا سابقاً) ، و الهدف من ذلك ضمان عدم وقوع أي مفردة من مفردات البيانات الخام في الفجوات الواقعة بين الفئات المتتالية و كذلك ضمان عدم وقوع أي مفردة بأكثر من فئة. 3. اختيار طول ملائم للفئة ، بحيث لا يكون طويلاً جداً فتضيع بذلك معالم البيانات و يفقدها الكثير من التفاصيل الهامة ، و لا قصيراً جداً فتفقد عملية تصنيف و تلخيص البيانات الخام الهدف منها (و اختيار الطول الملائم يرجع طبعاً لنوع البيانات الخام التي يجري التعامل معها)، وكما ينصح أيضاً بأن تكون الفئات متساوية الأطوال وذلك تسهيلاً لإجراء عمليات التحليل الإحصائي . 4. يجب أن يتضمن الجدول التكراري كافة مفردات البيانات الخام من دون استبعاد أي منها. 5. يجب أن تكون مراكز الفئات (مركز الفئة: العدد الذي ينصف الفئة و هو عبارة عن الوسط الحسابي للحدين الأعلى و الأدنى) ملائمة لطبيعة البيانات الخام ، فمن غير المعقول أن يكون مركز الفئة في الجدول التكراري للبيانات الخام التي لا يمكن أن تتضمن أعداداً كسرية إلا عدداً صحيحاً! 6. يجب أن تكون مشاهدات الفئة (المفردات التي فرغت في هذه الفئة) قريبة قدر الإمكان من مركزها ، الأمر الذي يقلل من الخطأ الناتج عن تبويب البيانات الخام في الجدول التكراري . و الآن وبعد أن حصلنا على المعرفة النظرية الخاصة بإنشاء الجداول التكرارية تعالوا لنرى كيف يمكننا عمل ذلك بسهولة بالغة في برنامج الاكسل . نحتاج من أجل ذلك للوظيفة الإضافية Histogram التي يمكن الحصول عليها كما يلي : من القائمة Tools نختار Add-Ins لتظهر لنا النافذة التالية :

إذا لم يكن مربع الاختيار Analysis ToolPak مفعلاً فقم بتفعيله ثم اضغط OK . قم باختيار القائمة Tools مرة أخرى وستجد الخيار Data Analysis قد أدرج ضمنها :

قم باختيار Data Analysisحتى تظهر لك نافذة ، ستجد ضمنها الوظيفة Histogram :

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

 [0,100]

2. يعتبر كل من حصل على نتيجة أكبر من أو تساوي 50 درجة ناجحاً (حد النجاح 50 درجة) . 3. تقديرات النجاح موزعة على الشكل التالي :

- أكثر شيء مهم من وجهة نظري هو جعل حد النجاح بين فئتين متتاليتين ، بحيث تتضمن أحد الفئتين أفضل الراسبين و تتضمن الفئة التي تليها أسوأ الناجحين. - من البديهي جعل مدى كل فئة 10 درجات ، وذلك تماشياً مع تقديرات النجاح (بحيث تحتوي كل فئة واقعة فوق حد النجاح على بيانات الطلاب المتماثلين بتقديرات النجاح). مما سبق نستنتج بأن مدى الفئة سيكون 10 درجات ، وأن الفئات ستكون 10 موزعة على المجال العام البيانات (من0 إلى 100) . - نلاحظ أنه لا يوجد أي طالب ينتمي للفئة الأولى :

 [0,10[

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

Bin1 : [10,20[ 
Bin2 : [20,30[
Bin3 : [30,40[
Bin4 : [40,50[
Bin5 : [50,60[
Bin6 : [60,70[
Bin7 : [70,80[
Bin8 : [80,90[
Bin9 : [90,100]

ملاحظة : يمكننا دمج الفئات 1 و 2 و 3 و 4 في فئة واحدة ، ولكن من الأفضل تركهم على حالهم لتفادي الإرباكات التي قد يسببها هذا الدمج عند تحليل هذه البيانات في المستقبل ، و للاستفادة أيضاً من الفوائد الناتجة عن جعل المدى العائد لكل فئات الجدول التكراري متساوي . المرحلة الثانية : تنظيم مجال الفئات بصورة مفهومة للاكسل : كما قلنا سابقاً فإن الاكسل يتعرف على الفئات من خلال وضعها في عامود يحتوي على الحدود العليا لهذه الفئات مرتبة بشكل تصاعدي . ولكن المشكلة التي تواجهنا الآن في مثال درجات الطلاب هو أن المجالات التي وضعناها للفئات تختلف بعض الشيء عن المجالات الافتراضية للفئات في برنامج الـ Histogram ، كيف : من خلال الشرح السابق نجد أن الفئة الثانية (مثلاً) يتعرف عليها برنامج الـ Histogram على النحو التالي :

Bin2 : ]20,30]

بينما نحن حددناها على الشكل التالي :

Bin2 : [20,30[

و لذلك (و بالاستفادة من كون جميع العلامات عبارة عن أعداد صحيحة) فلا مناص من ترتيب الفئات على النحو التالي (المجال C1:C9) :

المرحلة الثالثة : تشغيل معالج الـ Histogram :

الآن وبعد أن وضعنا البيانات الخام في المجال A1:A60 و الحدود العليا للفئات المقترحة في المجال C1:C9 نبدأ بتشغيل معالج الـ Histogram لتظهر لنا النافذة التالية :

- في المربع Input Range نضع مجال البيانات الخام (المجال A1:A60) .

- في المربع Bin Range نضع المجال التي توجد فيه الحدود العليا للفئات بترتيب تصاعدي (المجال C1:C9) ، حيث يتم عد جميع القيم الأصغر من الحد الأعلى للفئة الأولى ، و جميع القيم الأكبر من الحد الأعلى للفئة الأخيرة (في التبويب More) . أما إذا أهمل هذا المربع فيقوم الاكسل عندئذ بإنشاء مجموعة من الفئات الموزعة بين القيمتين العليا Max و الدنيا Min للبيانات الخام .

- حدد خانة الاختيار Label في حالة وجود ترويسة (عنوان) لعامودي البيانات الخام و الحدود العليا للفئات المقترحة ، و أزل تحديده في حال عدم وجود ترويسة.

- في المربع Output Range يمكننا وضع مرجع للخلية الموجودة في الزاوية العليا من جدول الإخراج (في حال رغبتك بإدراج جدول الإخراج في نفس ورقة البيانات الخام أو في مجال محدد من ورقة أخرى) .

- في المربع New Worksheet Ply يمكننا وضع اسم ورقة العمل التي نريد إدراج جدول الإخراج فيها ، و يمكننا ترك هذا المربع فارغ في حال رغبتنا بإدراج جدول الإخراج في ورقة عمل جديدة باسم افتراضي .

- نحدد الخيار New Workbook في حال رغبتنا بإدراج جدول الإخراج في مصنف عمل جديد .

- حدد الخيار Chart Output في حال رغبتك بإدراج مدرج تكراري للجدول التكراري المنشأ.

- حدد الخيار Cumulative Percentage في حال رغبتك بإنشاء عامود النسب المئوية التراكمية في جدول الإخراج ، و إنشاء المنحني التجميعي الصاعد في نفس مساحة الرصد التي يوجد فيها المدرج التكراري (في حال تفعيل الخيار Chart Output) .

- حدد الخيار Pareto في حال رغبتك بإدراج التحليل من نوع Pareto المفهرس ، و الذي يعطي نتائج التحليل بشكل مفهرس تنازلياً .ً

الآن و بعد الضغط على زر OK يقوم الاكسل بإدراج جدول الإخراج و المخطط البياني (في حال تحديدك لذلك) .

يمكنك تطوير جدول الإخراج ، و ذلك بإضافة مجال الفئات و التقدير و التكرار النسبي Frequency % ، كما هو موضح في الشكل التالي :

ملاحظة1 : يعتبر التكرار النسبي مفيداً جداً في حالة مقارنة التوزيع التكراري لعينتين مختلفتين بالحجم .

ملاحظة2 : يمكننا تكبير التخطيط الناتج عن طريق سحب أحد مقابضه (المربعات الموجودة في زواياه) أو تغيير مكان توضعه إلى ورقة مستقلة عن طريق الخيار Location الموجود في القائمة Chart .

ملاحظة3 : يمكننا تغيير المدرج التكراري الناتج إلى مضلع تكراري أو منحنى تكراري عن طريق الخيار Chart Type الموجود في القائمة Chart .

ملاحظة4 : نلاحظ من التوزيع التكراري الناتج أنه يشبه التوزيع الطبيعي ، و يقترب بشكل كبير من التوزيع المتماثل و ذلك نتيجة كون قيم الوسط الحسابي و الوسيط و المنوال متقاربة فيه بشكل كبير .

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

أرجو أن أكون قد وفقت في منحكم شيئاً ذو فائدة .

مرفق مثال عن الحالة السابقة :

بالتوفيق

Bin1.rar

Histogram.rar

[حسام نور]

- تميز بلا حدود أخي محمد ... وفقك الله

[jakord]

ما شاء الله

هذا شرح رائع،،،،

إلى الأمام أخي محمد حجازي

جعله الله في ميزان حسناتك...