أفضل 12 وظيفة فى الأكسيل ( Excel ) تستخدم فى تحليل البيانات
هناك أكثر من 475 وظيفة في أكسيل . هذا يمكن أن يجعلها ساحقة عندما تبدأ في تحليل البيانات. مع هذه المجموعة الكبيرة من الوظائف ، قد يكون من الصعب معرفة الوظيفة التي يجب استخدامها لمهام محددة.
وظائف Excel الأكثر فائدة هي تلك التي تجعل المهمة تبدو سهلة. والخبر السار هو أن معظم مستخدمي Excel لديهم مجموعة أدوات من عدد قليل من الوظائف التي تكمل معظم احتياجاتهم.
فى مدونة بالعربى سوف تتعمل ماهى 12 وظيفة Excel الأكثر فائدة لتحليل البيانات. توفر لك هذه الوظائف الأدوات اللازمة للتعامل مع غالبية مهام تحليل بيانات Excel. مع مدونات بالعربى سوف تتعلم اكثر عن هذه الدوال فى الدروس القادمة.
1- وظيفة (IF)
وظيفة IF مفيدة للغاية. هذه الوظيفة تعني أنه يمكننا انجاز عملية صنع القرار في جداول البيانات الخاصة بنا.
باستخدام IF ، يمكننا إجراء عملية حسابية مختلفة أو عرض قيمة مختلفة تعتمد على نتيجة اختبار منطقي (قرار).
تطلب منك الدالة IF إجراء الاختبار المنطقي، وما الإجراء الذي يجب اتخاذه إذا كان الاختبار صحيحا، والإجراء البديل إذا كانت نتيجة الاختبار خاطئة.
كيفية استخدام هذه الوظيفة؟
( الإجراء لو الاختبار المنطقى خطأ,الإجراء لو الاختبار المنطقى صح,الاختبار المنطقى) =IF
=IF(logical test, value if true, value if false)
فى الكثال التالى سوف نقوم بعرض كلمة نعم (Yes) اذا كان الفارق بين تاريخ التسليم (Delivery date) و تاريخ الطلب (Order date) اكبر من 7 ايام . وذلك بالمعادلة المعادلة السابقة :
=IF(logical test, value if true, value if false)
=IF (D2>7, "Yes","No") 👇
2- وظيفة (SUMIFS)
هي واحدة من وظائف الأكسيل الأكثر فائدة. وهو يجمع القيم التي تفي بمعايير محددة.
يحتوي الأكسيل أيضا على وظيفة تسمى SUMIF تقوم بنفس المهمة باستثناء أنه يمكنها اختبار شرط واحد فقط ، بينما يمكن ل SUMIFS اختبار العديد منها.
لذلك يمكنك تجاهل SUMIF بشكل أساسي لأن SUMIFS هي وظيفة متفوقة.
تطلب منك الدالة جمع نطاق القيم، ثم كل نطاق لاختباره والمعايير التي يجب اختبارها.
=SUMIFS(sum range, criteria range 1, criteria 1, …)
في هذا المثال، نقوم بجمع القيم في العمود C للمنطقة التي تم إدخالها في الخلية E3.
=SUMIFS(C2:C9,B2:B9,E3)
3- وظيفة ( COUNTIFS )
تقوم الدالة COUNTIFS بحساب عدد القيم التي تفي بالمعايير المحددة.
=COUNTIFS(criteria range 1, criteria 1, …)
في هذا المثال، نحسب عدد المبيعات من المنطقة التي تم إدخالها في الخلية E3 والتي تبلغ قيمتها 200 أو أكثر.
=COUNTIFS(B2:B9,E3,C2:C9,">=200”)
ملحوظة
عند استخدام الدالتين SUMIFS وCOUNTIFS، يجب إدخال المعايير كنص أو كمرجع خلية. يستخدم هذا المثال كلتا التقنيتين في نفس الصيغة.
يوجد ايضا بعد الدوال مثل AVERAGEIFS, MAXIFS, and MINIFS .. يسرى عليها نفس الاحكام.
4- وظيفة ( TRIM )
ستزيل هذه الوظيفة الرائعة جميع المسافات من الخلية باستثناء المسافات الفردية بين الكلمات.
الاستخدام الأكثر شيوعا لهذه الوظيفة هو إزالة المسافات الزائدة. يحدث هذا عادة عندما يتم لصق المحتوى من مكان آخر أو عندما يكتب المستخدمون مسافات عن طريق الخطأ في نهاية النص.
في المثال التالى، لا تعمل الدالة COUNTIFS من قبل لأنه تم استخدام مسافة عن طريق الخطأ في نهاية الخلية B6.
لا يمكن للمستخدمين رؤية هذه المساحة ، مما يعني أنه لا يتم تحديدها حتى يتوقف شيء ما عن العمل.
ستطالبك وظيفة TRIM بإزالة النص من المسافات.
=TRIM (نص)
في هذا المثال، يتم استخدام الدالة TRIM في عمود منفصل لتنظيف البيانات في عمود المنطقة الجاهزة للتحليل.
= TRIM(B2)
سوف نلاحظ فى الصورة التالية تحتوي الدالة COUNTIFS على بيانات نظيفة وتعمل بشكل صحيح.
تجمع الدالة CONCATENATE القيم من خلايا متعددة في خلية واحدة.
هذا مفيد لتجميع الأجزاء المختلفة من النص معا مثل اسم شخص ما أو عنوان أو رقم مرجعي أو مسار ملف أو عنوان URL.
يطالبك بالقيم المختلفة لاستخدامها.
=CONCATENATE(text1, text2, text3, …)
في المثال التالى، يتم استخدام CONCATENATE لدمج الاسم الأول واسم العائلة في اسم كامل. يتم إدخال مسافة للوسيطة text2.
=CONCATENATE(A2," ",B2)
6- وظيفة ( LEFT/RIGHT )
ستقوم الدالتان اليسرى واليمنى بالإجراء المعارض ل CONCATENATE. سوف يستخرجون عددا محددا من الأحرف من بداية النص ونهايته.
يمكن استخدام هذا لاستخراج أجزاء من عنوان أو عنوان URL أو مرجع لمزيد من التحليل.
تتطلب الدالتان LEFT و RIGHT نفس المعلومات. إنهم يريدون معرفة مكان النص وعدد الأحرف التي تريد استخراجها.
=LEFT(text, num chars)
=RIGHT(text, num chars)
في هذا المثال، يحتوي العمود A على مرجع يتكون من معرف العميل (ID Client) (أول حرفين) ومعرف المعاملة (transaction ID) ثم رمز المنطقة (ٌRegion Id ) (الحرف النهائي).
يتم استخدام الدالة LEFT التالية لاستخراج معرف العميل.
=LEFT(A2,2)
يمكن استخدام الدالة RIGHT لاستخراج الحرف الأخير من الخلايا الموجودة في العمود A. يشير هذا المثال إلى ما إذا كان العميل في الجنوب أو الشمال.
=RIGHT(A2,1)
تعد الدالة VLOOKUP واحدة من الوظائف الأكثر استخداما والتي يمكن التعرف عليها في Excel.
وتستخدم هذ الدالة للبحث عن قيمة في جدول وإرجاع معلومات من عمود آخر يتعلق بهذه القيمة.
إنه لأمر رائع لدمج البيانات من قوائم مختلفة في قائمة واحدة أو مقارنة قائمتين لمطابقة العناصر أو فقدانها. إنها أداة مهمة في تحليل بيانات Excel.
ولكى تستخدم هذه الدالة يجب الاشارة إلى أربع معلومات:
1 - القيمة التي تريد البحث عنها. 2 - الجدول الذي يجب البحث فيه.
3 - العمود الذي يحتوي على المعلومات التي تريد إرجاعها. 4- ما نوع البحث الذي ترغب في إجرائه.
=VLOOKUP (قيمة البحث، صفيف الجدول، رقم فهرس العمود، البحث عن النطاق)
=VLOOKUP(lookup value, table array, column index number, range lookup)
في المثال التالى، لدينا جدول يحتوي على مبيعات من موظفينا. هناك جدول آخر يحتوي على مزيد من المعلومات حول هؤلاء الموظفين (يتم الاحتفاظ بالجداول صغيرة على سبيل المثال).
نود إحضار البيانات التي توضح المنطقة التي يستند إليها الموظف في جدول المبيعات لتحليلها.
يتم استخدام الصيغة التالية في العمود D:
=VLOOKUP(B2,$G$2:$H$12,2,FALSE)
8- وظيفة ( IFERROR )
في بعض الأحيان تحدث أخطاء قد تكون بريئة وأحيانا قد تكون هذه الأخطاء أشياء يمكنك التنبؤ بها. الدالة VLOOKUP من قبل هي مثال نموذجي على ذلك.
لدينا خطأ بسبب وجود خطأ مطبعي في الاسم في جدول المبيعات. هذا يعني أن VLOOKUP لا يمكن العثور على هذا الاسم وينتج خطأ.
باستخدام IFERROR ، يمكننا عرض خطأ أكثر أهمية من الخطأ الذي يوفره Excel ، أو حتى إجراء عملية حسابية مختلفة.
تتطلب وظيفة IFERROR شيئين. القيمة التي يجب التحقق من وجود الخطأ والإجراء المطلوب تنفيذه بدلا من ذلك.
في هذا المثال، نقوم بلف الدالة IFERROR حول VLOOKUP لعرض رسالة أكثر معنى.
=IFERROR(VLOOKUP(B2,$G$2:$H$12,2,FALSE),"Name not found. Check both lists")
9- وظيفة ( VALUE)
غالبا ما يتم استيراد مجموعة البيانات التي تحتاج إلى تحليلها من نظام آخر أو نسخها ولصقها من مكان ما.
يمكن أن يؤدي ذلك في كثير من الأحيان إلى أن تكون البيانات بتنسيق خاطئ ، مثل تخزين رقم كنص. لا يمكنك تنفيذ مهام تحليل البيانات مثل المجموع إذا لم يتعرف Excel عليها كرقم.
لحسن الحظ ، فإن وظيفة VALUE موجودة هنا للمساعدة. وتتمثل مهمتها في تحويل الأرقام المخزنة كنص إلى أرقام.
في المثال التالى، تقوم الصيغة التالية بتحويل قيم المبيعات المخزنة كنص في العمود B إلى رقم.
=VALUE(B2)
10- وظيفة ( UNIQUE)
الدالة UNIQUE هي وظيفة جديدة متوفرة لأولئك الذين يستخدمون إصدار Microsoft 365 فقط.
تريد الوظيفة معرفة ثلاثة أشياء:
النطاق المطلوب إرجاع القائمة الفريدة منه
ما إذا كنت ترغب في التحقق من وجود قيم فريدة حسب العمود أو الصف
سواء كنت تريد قائمة فريدة أو قائمة متميزة (العناصر التي تحدث مرة واحدة فقط).
=UNIQUE(array, by col, exactly once)
في المثال التالى لدينا قائمة بمبيعات المنتجات ونريد استخراج قائمة فريدة بأسماء المنتجات. لهذا ، نحتاج فقط إلى توفير النطاق.
=UNIQUE(B2:B15)
11- وظيفة ( SORT)
هذه وظيفة أخرى متوفرة فقط لمشتركي Microsoft 365. كما يوحي الاسم ، سيتم فرز قائمة.
تطالب الدالة SORT بأربع وسيطات:
النطاق المطلوب فرزه
العمود الذي سيتم فرز النطاق حسبه
ما ترتيب فرز النطاق (تصاعديا أو تنازليا)
ما إذا كنت تريد فرز الصفوف أو الأعمدة.
=SORT(array, sort index,sort order, by col)
هذا رائع. ويمكن استخدامه مع المثال(UNIQUE) السابق لفرز أسماء المنتجات بالترتيب.
لهذا ، نحتاج فقط إلى تزويده بالنطاق المطلوب فرزه.
=SORT(UNIQUE(B2:B15))
تستخدم هذه الدالة لتصفية قائمة. وظيفة أخرى متوفرة فقط لمستخدمي Microsoft 365.
ستقوم هذه الوظيفة بتصفية نطاق. هذه وظيفة قوية للغاية وهي حلم لتحليل البيانات وإنتاج التقارير.
تأخذ الدالة FILTER ثلاث وسيطات:
النطاق المطلوب تصفيته
المعايير التي تحدد النتائج التي يجب إرجاعها
ما الإجراء الذي يجب اتخاذه إذا لم يتم إرجاع أي نتائج.
=FILTER(array, include, if empty)
في هذا المثال، يتم إرجاع نتائج الموضوع الذي تم إدخاله في الخلية F2 فقط.
=FILTER(B2:C12,A2:A12=F2,"No scores")
الخاتمة
إن تعلم وظائف Excel الأكثر فائدة لتحليل البيانات المذكورة في هذه المقالة سيقطع شوطا طويلا لجعل تحليل بيانات Excel أسهل.
ولكن لا يزال هناك العديد من الوظائف الأخرى وكذلك ميزات Excel يجب تعلمها لكى تحترف تحليل البيانات الحقيقي.
هناك أداتان أساسيتان أخريان ل Excel لإتقانهما Power Query و Power Pivot.
يجعل Power Query استيراد البيانات وتحويلها للتحليل أمرا سهلا. ويعد Power Pivot الأداة المثالية إذا قمت بتحليل كميات كبيرة من البيانات. يمكنه تخزين كميات هائلة من البيانات خارج Excel ولديه لغة صيغة خاصة به تسمى DAX.
مع مدونات بالعربى هتبقى محلل بيانات ممتاز.
تعليقات
إرسال تعليق