تمرين: استخدام Excel لاستكشاف البيانات
حسنا، الآن هي فرصتك للحصول على المعلومات. في هذا التمرين المعملي، يمكنك استخدام Microsoft Excel على الويب لاستكشاف مجموعة بيانات بسيطة.
قبل أن تبدأ
إذا لم يكن لديك حساب حساب Microsoft بالفعل (مثل hotmail.com، live.com، أو outlook.com)، سجل في واحد على https://signup.live.com.
نظرة عامة على المعمل
روزي ريفز هي طالبة في المدرسة المتوسطة للمشاريع تبيع عصير الليمون المصنوع محليا من موقف في الحديقة بالقرب من منزلها. للترويج لموقفها من عصير الليمون، توزع منشورات في الحديقة. تسجل روزي تفاصيل مبيعاتها وتوزيعها (النشرة) الإعلانية، إلى جانب قياسات الطقس بما في ذلك درجة الحرارة وهطول الأمطار كل يوم.
في هذا المختبر، يمكنك استكشاف وتصور البيانات التي سجلتها Rosie.
التمرين 1: عرض جدول بيانات في Excel
في هذا التمرين، يمكنك تحميل مصنف Excel الذي يحتوي على بيانات Rosie إلى حساب التخزين السحابي OneDrive المرتبط حساب Microsoft، ثم استكشاف البيانات في Microsoft Excel على الويب.
قم برفع المصنف إلى OneDrive
في مستعرض الويب، انتقل إلى https://onedrive.live.com، وسجل الدخول باستخدام بيانات اعتماد حساب Microsoft. يجب أن تشاهد الملفات والمجلدات في OneDrive، كما يلي:
في القائمة + جديد ، حدد مجلد لإنشاء مجلد جديد. يمكنك تسمية هذا أي شيء تريده، على سبيل المثال DataFund. عند ظهور المجلد الجديد، حدده لفتحه.
قم بتنزيل ملف Excel الذي يحتوي على بيانات عصير الليمون الخاصة ب Rosie من العنوان التالي إلى جهاز الكمبيوتر الخاص بك:
https://github.com/MicrosoftLearning/mslearn-data-concepts/raw/main/labfiles/Lemonade.xlsxفي المجلد الفارغ الجديد، في قائمة ⤒ Upload ، حدد Files. ثم عند المطالبة، حدد ملف Lemonade.xlsx الذي تم تنزيله وحدد فتح لتحميله، كما هو موضح هنا:
بعد بضع ثوانٍ، يجب أن يظهر ملف Lemonade.xlsx في المجلد الخاص بك كما يلي:
افتح الكتاب في Excel على الويب
اختر ملف Lemonade.xlsx في مجلد OneDrive لفتحه في Excel على الويب. عند فتحه، يجب أن يبدو كما يلي:
قد تكون التواريخ في العمود A عريضة جدا بحيث لا يمكن عرضها، لذلك قد تحتوي ####### الخلايا كما هو موضح سابقا. لمشاهدة التواريخ، انقر نقرا مزدوجا فوق الخط بين رأسي العمودين AوB . ثم تظهر التواريخ بتنسيق اللغة المقترنة حساب Microsoft. على سبيل المثال، في الصورة التالية، يتم عرض التواريخ بتنسيق المملكة المتحدة (dd/MM/yyyy).
تصفية البيانات وفرزها
حدد الخلية A1، ثم في علامة التبويب إدراج على الشريط أعلى ورقة العمل، حدد جدول. تحقق من أن Excel قد اكتشف البيانات تلقائيا في النطاق A1:G366، ومن تحديد خانة الاختيار يحتوي الجدول على رؤوس ، ثم حدد موافق؛ كما هو موضح هنا:
يقوم Excel تلقائيا بتنسيق البيانات كجدول ويضيف أزرارا منسدلة إلى صف الرأس كما هو موضح هنا:
حدد أي خلية لإلغاء تحديد الجدول، ثم حدد زر القائمة المنسدلة لعمود اليوم ، وقم بإلغاء تحديد خانة الاختيار (تحديد الكل)، ثم حدد خانتي الاختيار السبتوالأحد فقط كما هو موضح هنا قبل تحديد تطبيق:
تتم تصفية جدول البيانات لإظهار السجلات الخاصة بأيام عطلة نهاية الأسبوع فقط (السبت والأحد).
حدد سهم القائمة المنسدلة للعمود هطول الأمطار وحدد فرز من الأكبر إلى الأصغر. يتم فرز جدول البيانات بترتيب تنازلي لهطول الأمطار، لذلك يحتوي الصف الأول على بيانات يوم عطلة نهاية الأسبوع مع المطر الأكثر. كان هذا الأحد الذي كان هناك 2.50 سم من المطر كما هو موضح هنا:
حدد سهم القائمة المنسدلة لعمود اليوم مرة أخرى ثم حدد مسح عامل التصفية من "اليوم". يعرض الجدول الآن جميع البيانات.
حدد سهم القائمة المنسدلة ل Date وحدد Sort Oldest to Newest لإعادة ترتيب البيانات بترتيب زمني.
التحدي: العثور على يوم الأسبوع بأقل درجة حرارة
- استخدم قدرات التصفية والفرز في Excel على الويب لتصفية البيانات بحيث تظهر فقط أيام الأسبوع (من الاثنين إلى الجمعة)، ثم قم بفرز البيانات بحيث يحتوي الصف الأول على بيانات ليوم الأسبوع ذو أقل درجة حرارة.
- دون اليوم ودرجة الحرارة، ثم امسح عامل التصفية ولجأ إلى البيانات مرة أخرى إلى ترتيب زمني.
التمرين 2: استخدام الصيغ لاستكشاف البيانات في Excel
في هذا التمرين، يمكنك استخدام الصيغ لإنشاء أعمدة مشتقة تعمل على توسيع البيانات المسجلة بواسطة Rosie.
إضافة أعمدة مشتقة
على علامة التبويب الشريط الرئيسي ، في الجانب الأيسر من ناحية القائمة، استخدم الزر تبديل الأشرطة (v) لتوسيع الشريط وإظهار شريط أدوات الشريط الكلاسيكي الكامل:
حدد رأس العمود B لتحديد العمود B بأكمله. ثم على علامة التبويب الشريط الرئيسي في الشريط، في المقطع خلايا ، في القائمة المنسدلة إدراج ، حدد إدراج أعمدة إلى اليسار (استنادا إلى حجم نافذة المستعرض، قد تحتاج إلى توسيع قائمة خلايا لرؤية القائمة إدراج ).
يؤدي ذلك إلى إدراج عمود Column1 جديد بين عموديالتاريخ واليوم كما هو موضح هنا:
في الخلية B1، أعد تسمية Column1 إلى Month. ثم مع تحديد الخلية B2 ، في شريط fx أعلى البيانات، أدخل الصيغة التالية:
=TEXT(A2, "mmmm")بعد إدخال الصيغة، يجب نسخها تلقائيا إلى كافة خلايا الشهر الأخرى في الجدول، ويجب عرض اسم الشهر لكل سجل كما هو موضح هنا:
في الخلية I1، أدخل النص Revenue لإضافة عمود Revenue جديد إلى الجدول. ثم مع تحديد الخلية I2 ، في شريط fx أعلى البيانات، أدخل الصيغة التالية:
=G2*H2يتم نسخ الصيغة مرة أخرى تلقائيا إلى الصفوف المتبقية في الجدول، ويتم عرض الإيرادات (المحسوبة كسعر مضروب في المبيعات) كما هو موضح هنا:
حدد رأس العمود I لتحديد العمود بأكمله، ثم في علامة التبويب الصفحة الرئيسية على الشريط، في المقطع رقم ، في $ القائمة المنسدلة، حدد $ English (الولايات المتحدة). يعمل هذا على تنسيق بيانات الإيرادات كدولار أمريكي:
مرر لأسفل إلى أسفل جدول البيانات، وحدد الخلية I367 (ضمن عمود الإيرادات ). ثم على علامة التبويب الصفحة الرئيسية على الشريط، في المقطع تحرير ، في القائمة المنسدلة جمع تلقائي (Σ)، حدد Σ Sum.
يؤدي ذلك إلى إدخال الصيغة التالية:
=SUBTOTAL(109,[Revenue])تشير هذه الصيغة إلى الإيرادات ك عمود مسمى في الجدول وتحسب إجمالي القيم في هذا العمود. يمكنك تحقيق نفس النتيجة عن طريق إدخال
=SUM(I2:I366)ولكن باستخدام الدالة جمع تلقائي، يتم تضمين القيمة الناتجة في تعريف الجدول (قد تحتاج إلى توسيع العمود I لرؤية القيمة):قم بتصفية عمود الشهر لإظهار سجلات شهر يوليو فقط، ثم انظر إلى الإجمالي الفرعي في أسفل عمود الإيرادات (قد تحتاج إلى التمرير للعثور عليه). يظهر الآن إجمالي الإيرادات ل يوليو.
قم بإلغاء تحديد عامل التصفية على Month لإظهار جميع البيانات، وتحقق من أن إجمالي الإيرادات يعكس جميع الأشهر مرة أخرى.
التحدي: العثور على إجمالي عدد النشرات الإعلانية الموزعة
- أضف خلية أسفل عمود النشرات الإعلانية التي تحتوي على العدد الإجمالي للرسايليات الموزعة Rosie. قم بتنسيق هذا العمود باستخدام تنسيق الأرقام نمط الفاصلة (،) بحيث يتم تنسيق الإجمالي مثل 00,000.00.
- لاحظ المبلغ الإجمالي للسنة، ثم قم بتصفية البيانات للعثور على عدد النشرات الإعلانية الموزعة في شهر يناير. لا تنس مسح عامل التصفية عند الانتهاء!
التمرين 3: استخدام التنسيق الشرطي لاستكشاف البيانات
في هذا التمرين، يمكنك تطبيق التنسيق الشرطي على البيانات لتمييز القيم الرئيسية ذات الاهتمام.
تمييز القيم المتطرفة والنواجهات الخارجية
حدد الخلية D2 ثم اضغط باستمرار على المفتاحين ShiftوCtrl واضغط على مفتاح السهم لأسفل لتحديد جميع القيم في عمود درجة الحرارة (إذا كنت تستخدم كمبيوتر macOS، فاضغط باستمرار على المفتاحين Shift و ⌘ واضغط على مفتاح السهم لأسفل ).
على علامة التبويب الشريط الرئيسي في الشريط، في القائمة المنسدلة تنسيق شرطي ، أشر إلى مقاييس الألوان، وحدد تدرج الألوان الأحمرWhite-Blue (باللون الأحمر في الأعلى والأبيض في الوسط والأزرق في الأسفل). يتم إعادة تنسيق خلايا درجة الحرارة بحيث يتم تلوين الأيام الأكثر سخونة باللون الأحمر الشديد، وأبرد الأيام هي الأزرق العميق. التمرير عبر البيانات الآن، من الأسهل العثور على الأيام التي تكون ساخنة أو باردة بشكل خاص.
حدد كافة القيم في العمود هطول الأمطار ، ثم في القائمة المنسدلة تنسيق شرطي ، أشر إلى أشرطة البيانات، وحدد تعبئة تدرج شريط البيانات الأزرق الفاتح . يتم تنسيق الخلايا مع إشارة مرئية للمستوى المقارن لهطول الأمطار لكل يوم.
حدد جميع القيم في عمود Sales ، ثم في القائمة المنسدلة Conditional Formatting ، أشر إلى القواعد العليا/السفلى، وحدد Top 10%. ثم في مربع الحوار أعلى 10% ، حدد تعبئة خضراء مع نص أخضر داكن وحدد تم. يتم تمييز الخلايا التي تحتوي على قيم مبيعات في أعلى 10% باللون الأخضر (قد تحتاج إلى التمرير لرؤيتها) ويظل جزء التنسيق الشرطي مفتوحا.
أعد تحديد القيم في عمود المبيعات إذا قمت بإلغاء تحديدها، ثم في جزء التنسيق الشرطي ، حدد + لإضافة قاعدة جديدة بالإعدادات التالية:
- تطبيق على النطاق: H2:H366
- إذا كان صحيحا، فتوقف: غير محدد
-
نوع القاعدة: أعلى/أسفل
- أدنى 10 بالمائة
- التنسيق باستخدام: تعبئة حمراء فاتحة مع نص أحمر داكن
حدد تم لتطبيق التنسيق ثم أغلق جزء التنسيق الشرطي مع أيقونة x في أعلى يمينه. يتم تمييز الخلايا التي تحتوي على قيم مبيعات في أسفل 10% باللون الأحمر (مرة أخرى، قد تحتاج إلى التمرير لمشاهدتها).
التحدي: مقارنة درجة الحرارة وهطول الأمطار والمبيعات
الآن بعد أن قمت بتمييز الخلايا، يمكنك بسهولة أكبر إجراء مقارنات مرئية بين قيم درجة الحرارة وهطول الأمطار والمبيعات.
قم بالتمرير عبر البيانات، وفقط من خلال النظر إلى التنسيق المرئي الذي أضفته، حاول معرفة ما إذا كان يمكنك اكتشاف أي علاقة بين درجة الحرارة وهطول الأمطار والمبيعات التي قد تشكل أساس فرضية للتحقيق بشكل أكثر شمولا.