يعود استخدام جداول البيانات الإلكترونية إلى عام 1979 ولا يزال أحد أكثر الطرق شيوعًا لمراجعة البيانات ومعالجتها. اليوم، يمتلك برنامج جداول البيانات Excel في كل مكان من Microsoft أكثر من 750 مليون مستخدم ويتم استخدامه في بعض أكبر الشركات في جميع أنحاء العالم. أنا شخصياً أستخدم Excel كل يوم تقريبًا لأنه قد يؤدي أحيانًا إلى نتائج أسرع من أي أداة أخرى شبيهة.
من خلال اكسيل, يمكنك اختصار الساعات والأيام في تحليل البيانات وعرضها بصورة منظمة قابلة للتعديل والإستخدام على عدة منصات وأماكن مختلفة كما تسمح لك بتطبيق المعادلات بكل سهولة وإنشاء ملفات عمل متنوعة.
تحليل البيانات باستخدام Excel بكل سهولة
أحد الأسباب التي تجعل برنامج Excel شائعًا للغاية هو أنه مليء بالميزات والوظائف التي يمكن استخدامها لتنظيف البيانات وتجميعها وتحويلها إلى محاور أو رسوم بيانية. في هذه المقالة، سنتطرق إلى الميزات والوظائف السبع لاستخدام تحليل البيانات في Excel وأعتقد أن كل مستخدم يحتاج إلى معرفة تلك المميزات في تحليل البيانات:
الجداول والمخططات المحورية
إحدى ميزاتي المفضلة في Excel هي المخططات المحورية، وكميزة أخرى اكتشفتها مؤخرًا، الجداول المحورية. تصور المخططات المحورية البيانات المعبر عنها في جدول محوري، مما يمنحنا نظرة ثاقبة في لمحة. توفر الجداول المحورية طريقة بسيطة لإعادة تنسيق الأعمدة والصفوف وتحويلها إلى مجموعات أو إحصائيات أو ملخصات. يمكننا إنشاء مخطط مع الجدول باستخدام ميزة PivotChart ضمن إدراج أو Insert.
لنفترض أننا أردنا حساب السجلات المجمعة حسب الحالة الاجتماعية. إن استخدام الجدول المحوري يجعل هذا الأمر غاية في البساطة، لأنه يحول البيانات ويجمعها لنا.
لإنشاء مخطط محوري وجدول محوري ، حدد أولاً نطاق البيانات التي تريد تضمينها ثم انقر فوق إدراج> PivotChart> PivotChart & PivotTable. سينبثق محرر إنشاء PivotTable.
سيظهر النطاق المحدد في حقل الجدول / النطاق افتراضيًا. انقر فوق “موافق” وسيتم إنشاء الجدول المحوري.
في حقول PivotChart، اسحب Marital_Status من قائمة الحقول إلى مربع المحور (الفئات). ثم اسحب Marital_Status من قائمة الحقول إلى مربع القيم. نظرًا لأن بيانات Marital_Status عبارة عن سلسلة، يجب أن يكون تجميع القيم افتراضيًا على العدد. إذا كان نوع البيانات رقميًا، فسيتم تعيين الإعداد الافتراضي على Sum.
يجب ملء الجدول المحوري والرسم البياني. يمكنك إضافة أبعاد أو عوامل تصفية إضافية عن طريق سحب حقول جديدة إلى المربع المقابل. لا يتطلب الأمر سوى بضع نقرات لتجميع البيانات وتصورها في Excel، وهذا هو سبب استخدامها على نطاق واسع.
التنسيق الشرطي
بالتفكير في الأمر، ربما أستخدم التنسيق الشرطي أكثر من أي ميزة أخرى في Excel. يسمح لك التنسيق الشرطي بتمييز الخلايا أو إخفائها بناءً على قاعدة تحددها. قم بتطبيق القواعد على خلية واحدة أو عدة خلايا في نفس ورقة العمل. إنه مفيد لإبراز القيم المتطرفة أو التكرارات أو الأنماط في البيانات.
لنفترض أننا نريد إبراز جميع قيم عام الميلاد الأكبر من 1987 في مجموعة البيانات.
حدد العمود Year_Birth وانقر فوق تنسيق شرطي> قواعد تمييز الخلايا> أكبر من. سينبثق المحرر:
أدخل 1987 وانقر فوق موافق. ستتحول الخلايا الموجودة في العمود بقيمة أكبر من 1987 إلى اللون الأحمر الفاتح.
إذا قررنا أننا نريد تعديل قاعدة التنسيق الشرطي التي أنشأناها للتو، فاتبع المسار التنسيق الشرطي> مدير قواعد التنسيق الشرطي.
من المدير يمكننا إنشاء قواعد جديدة أو تعديل القواعد الحالية. من الممكن أن يكون لديك عدة قواعد تؤثر على جدول البيانات.
إزالة التكرارات في اكسل
غالبًا ما تكون البيانات فوضوية، لذلك من المهم أن تعرف كيفية إزالة التكرارات. باستخدام قواعد التنسيق الشرطي، يمكنك تمييز البيانات المكررة لمراجعتها قبل حذفها. تتوفر ميزة إزالة التكرارات ضمن البيانات> أدوات البيانات> إزالة التكرارات.
قم بتمييز مجموعة البيانات في Excel وانقر فوق إزالة التكرارات. يظهر محرر Remove Duplicates:
يسمح لنا المحرر بتحديد الأعمدة التي يجب تضمينها عند حذف التكرارات. تأكد من وضع علامة على خانة الاختيار تحتوي بياناتي على رؤوس إذا لم يتم عرض أسماء الأعمدة.
انقر فوق موافق. سيتم تجاهل التكرارات من مجموعة البيانات. سيخبرنا كم عدد القيم الفريدة المتبقية.
DAYS و NETWORKDAYS في اكسل
DAY’s و NETWORKDAYS هي وظائف منفصلة ، لكنها متشابهة بما يكفي لعدم ضمان إدخالات فردية في قائمتي. ترجع الدالة DAYS ببساطة عدد الأيام بين تاريخين، بينما تختلف الدالة NETWORKDAYS قليلاً من حيث أنها تستبعد عطلات نهاية الأسبوع والعطلات المحددة. تقوم فقط بإرجاع عدد أيام العمل بين تاريخين. من السهل تذكر بناء الجملة لكليهما:
= DAYS (start_date، end_date)
=NETWORKDAYS(start_date, end_date, [holidays])
في وظيفتي، أحد الأشياء التي أحللها هي بيانات الاستخدام. أستخدم هذه الوظائف لأشياء مثل حساب عدد الأيام منذ أن قام المستخدم بتسجيل الدخول أو استخدام أداة. نظرًا لاستخدام برنامجنا في المدارس، فمن المنطقي في بعض الأحيان استبعاد عطلات نهاية الأسبوع والعطلات من بيانات الاستخدام الخاصة بنا، لذلك أنا ممتن لميزة NETWORKDAYS.
مقال متعلق: 12 من أهم توابع اكسل – Excel Function التي يجب على الجميع معرفتها
التصنيف
تطلب الدالة RANK رقمًا حسب حجمها بالنسبة إلى القيم الأخرى في القائمة وتُرجع الترتيب المطلوب. هذا يعني أن مرتبة الرقم ستكون موضعه إذا تم فرز القائمة بترتيب تصاعدي أو تنازلي. على سبيل المثال، قم بفرز الدخل بترتيب تنازلي بحيث تكون القيمة الأكبر في الأعلى ويمكن أن تكون هذه المرتبة 1. تعطي RANK الأرقام المكررة نفس الترتيب، ولكن يتم حسابها بشكل تراكمي. هذا يعني أنه إذا كانت قيمتان في المرتبة الرابعة، فإن المرتبة التالية ستكون ستة، وليس خمسة (1،2،3،4،4،6). تأخذ صيغة RANK ثلاثة وسيطات.
=RANK(number, ref, [order])
من المهم ملاحظة أنه يمكن ضبط [الترتيب] على 0 للتنازلي و 1 (أو أكبر) للصعود.
نريد ترتيب الدخل (العمود E) لعملائنا ، لذلك سنستخدم الصيغة التالية في الخلية AH2:
= RANK (E2، E2: E2241، 0)
في الزاوية اليمنى السفلية للخلية AH2، انقر فوق المربع واسحبه لأسفل إلى آخر صف من البيانات وسيتم نسخ الصيغة تلقائيًا مما يسمح لنا بإنشاء تصنيف سريع لكل قيمة دخل.
وظيفة COUNTBLANK في اكسل
تعد وظيفة COUNTBLANK بديهية إلى حد ما، ولكنها وظيفة مهمة في جدل البيانات في التحليلات لأن العديد من خوارزميات التعلم الآلي حساسة للقيم الصفرية. من خلال معرفة عدد القيم الفارغة، يكون لديك فهم أفضل لكيفية التعامل معها. على سبيل المثال، إذا كانت الكثير من القيم فارغة، فيجب عليك إسقاط العمود. إذا كانت بعض القيم فارغة، فيجب أن تنسب قيمة لملء القيمة الخالية. تحسب COUNTBLANK عدد الخلايا الفارغة في النطاق. بناء الجملة بسيط:
=COUNTBLANK(range)
نريد حساب عدد القيم الخالية في عمود الدخل Income، لذا أضف هذه الصيغة إلى الخلية AG2:
=COUNTBLANK(E2:E2241)
XLOOKUP
تُشبه XLOOKUP توليفة من VLOOKUP و HLOOKUP، حيث يمكنها الانتقال عموديًا أو أفقيًا للبحث عن قيمة من نطاق. يسمح لك بشكل أساسي باستخدام نطاق محدد كجدول بحث وإرجاع نتيجة “تم البحث عنها” إلى خلية. بناء الجملة كما يلي:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
لنفترض أننا نريد البحث عن عام الميلاد Year_Birth بناءً على قيمة المعرف التي تم إدخالها. في الخلية AD2، أدخل قيمة المعرف — على سبيل المثال، 8755. في الخلية AE2، أدخل صيغة XLOOKUP:
=XLOOKUP(
lookup_value هي القيمة التي نريد البحث عنها بواسطة الوظيفة، لذلك ندخل AD2.
الجدير بالذكر إن lookup_array عبارة عن عمود أو صف يحتوي على قيمة البحث، لذلك نقوم بإدخال A2: A2241 لأن ذلك سيعطينا مصفوفة من المعرفات.
return_array هو العمود أو الصف الذي يحتوي على القيمة التي نريد إرجاعها، لذلك نختار B2: B2241 لأن ذلك سيعطينا قيم Year_Birth.
ستبدو الصيغة المكتملة كما يلي:
= XLOOKUP (AD2، A2: A2241، B2: B2241)
أدخل معرفات مختلفة وسيعود Year_Birth المقابل.
تعد وظائف البحث قوية للغاية، ويمكنك حتى ضم البيانات من أوراق مختلفة أو وظائف بحث متداخلة داخل بعضها البعض. على سبيل المثال، يمكنك جمع قيمة عمليات البحث المتعددة.
الملخص
تعرفنا في هذا المقال على عدة طرق من أجل تحليل البيانات باستخدام excel بطريقة اكثر احترافية واختصاراً للوقت , نتمنى أن يكن المقال اعجبكم وكان يستحق المشاركة مع أصدقائكم.
