كيفية تحليل البيانات باستخدام إكسل

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

لمحة حول خطوات تحليل البيانات باستخدام إكسل

يُوفر تحليل البيانات باستخدام إكسل الإجابة على الأسئلة حول بياناتك بطريقة سهلة كما في الخطوات التالية:

• يمكن إنشاء جدول إكسل بالضغط على مكان ما ثم Ctrl+T.
• تأكد من أن تسمية الرؤوس فريدة وغير فارغة لكل عمود، تجنب الصفوف المزدوجة من الرؤوس والخلايا المدمجة.
• يمكنك التحليل بالضغط على زر تحليل البيانات في تبويبة الصفحة الرئيسية، فهو يحلل البرنامج بياناتك ويرجع مرئيات حولها في جزء المهام.
• إذا أردت معرفة معلومات أكثر، تستطيع إدخال سؤال في مربع الاستعلام في أعلى الجزء، ثم الضغط على Enter. فيعود لك بالإجابات بمختلف المرئيات على سبيل المثال: الجداول أو المخططات أو PivotTables يمكنك إدراجها في المصنف.
• كما يوفر قائمة بالأسئلة المقترحة المخصصة وفق بياناتك، يمكنك الوصول إليها عبر مربع الاستعلام.
• توجد وظيفة استعلامات اللغة الطبيعية في تحليل البيانات للعملاء تدريجياً، فقد لا يكون متاحاً في كل البلدان حالياً.

إذا كانت لديك بيانات معقدة أو متداخلة، فيمكنك استخدام Power Query لتحويل الجداول التي تحتوي على علامات تبويب متقاطعة أو صفوف متعددة من الرؤوس.

أبرز الدوال لتحليل البيانات في برنامج Excel

تُساعد دوال إكسل في فهم البيانات وإجراء العمليات عليها بحيث يمكن البحث عن قيمة في الجدول والقيام بالعمليات الحسابية والتحليلات الاحصائية في نطاقات معينة. من أبرز الدوال المستخدمة عند تحليل البيانات باستخدام إكسل:

CONCATENATE:

CONCATENATE تسمح بدمج النصوص والأرقام والتواريخ والبيانات الأخرى من عدة خلايا في خلية واحدة.  

الصيغة:

= CONCATENATE (cells)

 LEN:

تُرجع الدالة LEN عدد الأحرف في خلية معينة بسرعة، مفيدة بشكل خاص عند محاولة التمييز بين المعرفات الفريدة المميزة (UIDs)، والتي قد تكون طويلة وليست بالتسلسل الصحيح.

الصيغة:

 (cell) LEN =

 Trim:

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

الصيغة:

  Trim (STRING)=

FIND:

تُرجع الدالة FIND مطابقة حساسة لحالة الأحرف، أي إذا استعلمت عن كلمة “كبير”، ستحصل على نتائج كبير فقط. 

الصيغة:

(TEXT,WITHIN TEXT,[START NUMBER]) FIND = 

SEARCH:

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

الصيغة:

SEARCH (TEXT, WITHIN TEXT, [START NUMBER]) =

SORT:

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

تستطيع فرز نطاق محدد أو جدول بيانات، حسب أعمدة متعددة أو عمود واحد، وبترتيب إما تصاعدي أو تنازلي.

للفرز حسب عمود واحد اتبع الخطوات التالية:

1. اضغط على أي خلية في العمود الذي تريد فرزه.
2. بعد ذلك، للفرز التصاعدي، اضغط على خيار AZ من تبويبة “بيانات”، في المجموعة “فرز وتصفية”، للفرز تنازلي، اضغط على خيار ZA.

للفرز حسب أعمدة متعددة اتبع الخطوات التالية:

1. اضغط على خيار “فرز” من مجموعة “فرز وتصفية” في تبويبة “البيانات”.
2. سيظهر مربع حوار الفرز، أضف المستويات التي تريد الفرز حسبها، انقر موافق.

 FILTER:

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

1. انقر على أي خلية مفردة داخل بياناتك.
2. انتقل إلى علامة تبويب البيانات، ثم الفرز والتصفية، التصفية.
3. سيظهر رؤوس الأسهم في الأعمدة.
4. تستطيع التصفية بحسب احتياجاتك.

COUNT:

COUNT هي دالة شائعة في إكسل تستخدم لحساب الخلايا في نطاق معين. 

 الصيغة:

(RANGE) = COUNTIF

SUM:

تُرجع الدالة SUM مجموع الخلايا المحددة في نطاق معين.

 الصيغة:

 (rang [sum_range],) SUMIF =

AVERAGE:

تُرجع الدالة AVERAGE المتوسط الحسابي لمجموع الخلايا المحددة في نطاق معين.

(rang, Certiera [sum_range],) AVERAGE =

COUNTA:

تُحدد الدالة COUNTA ما إذا كانت الخلية فارغة أم لا، حيث ستواجهه في عملك كمحلل بيانات الكثير من مجموعات بيانات غير المكتملة.  يسمح COUNTA بفحص أي ثغرات في مجموعة البيانات دون الحاجة إلى إعادة هيكلتها.

الصيغة:

(CELL) COUNTA =

COUNTIF: 

COUNTIF هي دالة Excel شائعة الاستخدام تستخدم لحساب الخلايا في نطاق يستوفي شرطاً واحداً. 

الصيغة:

(RANGE, Certiera) = COUNTIF

SUMIF:

تُرجع الدالة SUMIF مجموع الخلايا التي تستوفي شرطًا واحدًا.

 الصيغة:

(rang, Certiera [sum_range],) SUMIF =

SUMIFS:

تُعطي الدالة SUMIFS قيمة المتوسط بشرط تحقق معلمة واحدة أو أكثر.

الصيغة:

SUMIFS (CELL, CRITERION, RANG) =

AVERAGEIFS:

تُعطي الدالة AVERAGEIFS قيمة المتوسط بشرط تحقق معلمة واحدة أو أكثر.

الصيغة:

AVERAGEIF (CELL, CRITERION, RANG) =

كيفية تصوير البيانات وإعداد التقارير في Excel 

بُعد تنظيف وتحليل البيانات في إكسل يمكنك تصويرها وتمثيلها بمخططات ورسوميات أسهل للقراءة والفهم، ومن طرق تمثيل البيانات باستخدام إكسل:

الرسوم البيانية

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

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

يمكنك إنشاء مخططات دائرية أو خطية أو عمودية أو شريطية بسرعة باتباع الخطوات التالية:

1. حدد نطاق البيانات المراد تصويره.
2. بعد ذلك حدد إدراج، ثم اختر نوع المخطط المطلوب من الرموز.
3. أخيراً، عدل المخطط المدرج حسب الحاجة.

التنسيق مشروط

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

1. اضغط على خيار التنسيق الشرطي في تبويبة الصفحة الرئيسية، ونفذ الإجراءات التالية:
2. إذا أردت تغيير القيم في الخلايا الفردية، حدد Highlight Cells Rules أو Top/Bottom Rules، ثم اختر الخيار الذي يتوافق مع احتياجاتك.
3. يشير مقياس اللون إلى شدة لون الخلية المتوافقة مع موضع القيمة في أعلى أو أسفل النطاق يؤكد على العلاقة بين القيم في نطاق الخلايا على سبيل المثال: توزيعات المبيعات بين المناطق.  أشر إلى مقاييس الألوان ثم اضغط على المقياس المطلوب.
4. للتأكيد على العلاقة بين القيم في نطاق الخلايا، أشر إلى أشرطة البيانات ثم اضغط على التعبئة المطلوبة، فينشأ شريط ملون عبر الخلية. على سبيل المثال إجراء مقارنات عدد السكان في المدن الكبرى.
5. لتمييز نطاق خلايا يحتوي على عدة مجموعات من القيم، لكل منها حد خاص بها، أشر إلى Icon Sets ثم اضغط على خيار مجموعة.  

ميزات متقدمة في Excel لتحليل البيانات

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

ميزة Analyse-it 

لكي تعمل ميزة Analyse-it مع بياناتك، يجب عليك اتباع بعض الإرشادات عند تنظيم البيانات في ورقة  Excelوهي:

• يجب أن يصف العنوان البيانات بشكل كافٍ.
• اجعل ال head يحتوي تسميات قابلة للتكوين، إذ يجب أن يكون لكل متغير اسم مميز. كما يمكن دمج وحدات القياس في الملصق عبر وضعها بين قوسين بعد الاسم.
• اجعل الصفوف تحمل معلومات لكل مثيل، والأعمدة تحمل بيانات لكل متغير.
• اجعل التسميات في العمود الأول تدل على أسماء/معرفات ذات معنى.

 الجداول المحورية

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

1. اضغط على خلية معينة في مجموعة البيانات.
2. ثم من تبويبة إدراج، مجموعة جداول، اضغط على PivotTable.
3. سيظهر مربع الحوار، يحدد Excel مجموعة البيانات تلقائيًا، كما ينشئ ورقة عمل جديدة لجدولك المحوري.
4. اضغط على موافق، ليتم إنشاء ورقة عمل الجدول المحوري.
5. اسحب حقل الرئيسي إلى منطقة الصفوف، حقل العناصر إلى منطقة القيم.

 حل مشكلة باستخدام Solver

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

يُعتبر الحل مثالياً لتحليل ماذا لو، وهو عبارة عن برنامج إضافي لـ Microsoft Excel مفيد على العديد من المستويات. يمكنك استخدامها لإيجاد القيمة المثلى (الحد الأقصى أو الأدنى) للصيغة في خلية واحدة، والمعروفة باسم الخلية الهدف.  

يعمل Solver مع مجموعة من الخلايا، تُسمى متغيرات القرار أو ببساطة الخلايا المتغيرة، المستخدمة في حساب الصيغ في الخلايا الهدف والخلايا المقيدة. كما يقوم Solver بضبط قيم خلايا متغير القرار للعمل على حدود خلايا القيد، مما يساعد بالتالي في إنتاج النتيجة المرجوة للخلية الهدف.

يمكن تفعيل الوظيفة الإضافية Solver باتباع الخطوات التالية:

1. في تبويبة ملف، على خيارات.
2. انتقل إلى الوظائف الإضافية، وحدد وظيفة Solver، ثم اضغط على زر “انتقال”.
3. تحقق من وظيفة Solver الإضافية واضغط على “موافق”.

عيوب تحليل البيانات باستخدام إكسل

رغم أن َّتحليل البيانات في إكسل شائع وسهل الاستخدام ولديه الكثير من المميزات، لكنه لا زال لديه بعض العيوب فقد لا يعمل لعدة أسباب أبرزها:

1. في الوقت الحالي، لا يدعم إكسل تحليل مجموعات البيانات التي تزيد عن 1.5 مليون خلية، هنا يمكنك تصفية بياناتك، ونسخها إلى موقع آخر لتشغيل تحليل البيانات عليها.
2. تحلل تواريخ السلسلة مثل “01-08-2024” كسلاسل نصية، هنا يجب إنشاء عمود جديد يستخدم الدالتين DATE أو DATEVALUE، وتنسيقه كتاريخ.
3. لا يعمل تحليل البيانات عندما يكون Excel في وضع التوافق أي عندما يكون الملف بتنسيق .xls)، لذا يجب حفظ ملفك بصيغة ‎.xlsx، أو ‎.xlsm، أو ‎.xlsb.
4. قد يصعب فهم الخلايا المدمجة، فإذا كنت تحاول توسيط البيانات كرأس التقرير، تستطيع إزالة كافة الخلايا المدمجة، ثم تنسيق الخلايا باستخدام Center Across Selection.  لفعل ذلك اضغط على Ctrl+1، ثم انتقل إلى Alignment > Horizontal > Center Across Selection.

في الختام،

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