10 وظائف أساسية لـ Microsoft Excel لتحليل البيانات

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

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

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

1. التسلسل

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

في المثال أعلاه ، أردت الشهر والمبيعات معًا في عمود واحد. لذلك ، استخدمت الصيغة = CONCATENATE (A2 ، B2) في الخلية C2 للحصول على 700 دولار من يناير كنتيجة.

الصيغة: = CONCATENATE (الخلايا التي تريد دمجها)

2. LEN

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

في المثال أعلاه ، أردت حساب الأرقام الخاصة بعدد المشاهدات التي كنت أحصل عليها كل شهر. لهذا ، استخدمت الصيغة = LEN (C2) في الخلية D2 للحصول على 5 كنتيجة.

الصيغة: = LEN (خلية)

3. VLOOKUP

= من المحتمل أن تكون VLOOKUP واحدة من أكثر الوظائف التي يمكن التعرف عليها لأي شخص على دراية بتحليل البيانات. يمكنك استخدامه لمطابقة البيانات من جدول بقيمة إدخال. توفر الوظيفة وضعين للمطابقة – دقيق وتقريبي ، يتم التحكم فيهما من خلال نطاق البحث. إذا قمت بتعيين النطاق على FALSE ، فسيبحث عن تطابق تام ، ولكن إذا قمت بتعيينه على TRUE ، فسيتم البحث عن تطابق تقريبي.

في المثال أعلاه ، أردت البحث عن عدد المشاهدات في شهر معين. لذلك ، استخدمت الصيغة = VLOOKUP (“Jun” ، A2: C13 ، 3) في الخلية G4 وحصلت على 74992 كنتيجة لذلك. هنا ، “Jun” هي قيمة البحث ، A2: C13 هي مصفوفة الجدول التي أبحث فيها عن “Jun” و 3 هي رقم العمود الذي ستعثر فيه الصيغة على طرق العرض المقابلة لشهر يونيو.

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

الصيغة: = VLOOKUP (lookup_value، table_array، col_index_num، [range_lookup])

4. فهرس / تطابق

مثل وظيفة VLOOKUP ، تكون وظيفة INDEX و MATCH مفيدة للبحث عن بيانات محددة بناءً على قيمة إدخال. يمكن لـ INDEX و MATCH ، عند استخدامهما معًا ، التغلب على قيود VLOOKUP لتقديم نتائج خاطئة (إذا لم تكن حريصًا). لذلك عند دمج هاتين الوظيفتين ، يمكنهم تحديد مرجع البيانات والبحث عن قيمة في مصفوفة ذات بُعد واحد. يُرجع ذلك إحداثيات البيانات كرقم.

في المثال أعلاه ، أردت البحث عن عدد المشاهدات في كانون الثاني (يناير). لذلك ، استخدمت الصيغة = INDEX (A2: C13، MATCH (“Jan”، A2: A13،0)، 3). هنا ، A2: C13 هو عمود البيانات الذي أريد أن تعيده الصيغة ، “Jan” هي القيمة التي أريد مطابقتها ، A2: A13 هو العمود الذي ستجد فيه الصيغة “Jan” و 0 تشير إلى أنني أريد الصيغة للعثور على تطابق تام للقيمة.

إذا كنت تريد العثور على تطابق تقريبي ، فسيتعين عليك استبدال 0 بـ 1 أو -1. بحيث يجد 1 أكبر قيمة أقل من أو تساوي قيمة البحث و -1 سيجد أصغر قيمة أقل من أو تساوي قيمة البحث. لاحظ أنه إذا لم تستخدم 0 أو 1 أو -1 ، فستستخدم الصيغة 1 ، بواسطة.

الآن إذا كنت لا تريد كتابة اسم الشهر بشكل ثابت ، يمكنك استبداله برقم الخلية. لذلك يمكننا استبدال “Jan” في الصيغة المذكورة أعلاه بـ F3 أو A2 للحصول على نفس النتيجة.

الصيغة: = INDEX (عمود البيانات التي تريد إرجاعها ، MATCH (نقطة البيانات الشائعة التي تحاول مطابقتها ، عمود مصدر البيانات الآخر الذي يحتوي على نقطة البيانات المشتركة ، 0))

5. MINIFS / MAXIFS

= MINIFS و = MAXIFS تشبهان إلى حد بعيد الدالتين = MIN و = MAX ، باستثناء حقيقة أنها تسمح لك بأخذ الحد الأدنى / الأقصى لمجموعة القيم ومطابقتها وفقًا لمعايير معينة أيضًا. لذلك بشكل أساسي ، تبحث الوظيفة عن القيم الدنيا / القصوى وتطابقها مع معايير الإدخال.

في المثال أعلاه ، أردت العثور على الحد الأدنى من الدرجات بناءً على جنس الطالب. لذلك ، استخدمت الصيغة = MINIFS (C2: C10 ، B2: B10 ، “M”) وحصلت على النتيجة 27. هنا C2: C10 هو العمود الذي ستبحث فيه الصيغة عن الدرجات ، B2: B10 هو عمود تبحث فيه الصيغة عن المعايير (الجنس) ، والحرف “M” هو المعيار.

وبالمثل ، بالنسبة إلى الحد الأقصى من الدرجات ، استخدمت الصيغة = MAXIFS (C2: C10 ، B2: B10 ، “M”) وحصلت على النتيجة 100.

صيغة MINIFS: = MINIFS (min_range ، criteria_range1 ، criteria1 ، …)

صيغة MAXIFS: = MAXIFS (max_range ، criteria_range1 ، criteria1 ، …)

6. المتوسطات

تتيح لك الوظيفة = AVERAGEIFS العثور على متوسط لمجموعة بيانات معينة بناءً على معيار واحد أو أكثر. أثناء استخدام هذه الوظيفة ، يجب أن تضع في اعتبارك أن كل معيار ونطاق متوسط يمكن أن يكون مختلفًا. ومع ذلك ، في الدالة = AVERAGEIF ، يجب أن يكون لكل من نطاق المعايير ونطاق المجموع نفس نطاق الحجم. لاحظ الفرق في صيغة المفرد والجمع بين هاتين الدالتين؟ حسنًا ، هذا هو المكان الذي يجب أن تكون فيه حذرًا.

في هذا المثال ، أردت العثور على متوسط الدرجات بناءً على جنس الطلاب. لذلك استخدمت الصيغة = AVERAGEIFS (C2: C10، B2: B10، “M”) وحصلت على 56.8 نتيجة لذلك. هنا ، C2: C10 هو النطاق الذي ستبحث فيه الصيغة عن المتوسط ، B2: B10 هو نطاق المعايير ، و “M” هو المعيار.

الصيغة: = AVERAGEIFS (متوسط_نطاق ، نطاق_المعايير 1 ، معايير 1 ، …)

7. COUNTIFS

الآن إذا كنت تريد حساب عدد المثيلات التي تفي بها مجموعة بيانات مع معايير محددة ، فستحتاج إلى استخدام الدالة = COUNTIFS. تتيح لك هذه الوظيفة إضافة معايير غير محدودة إلى استعلامك ، وبالتالي تجعله أسهل طريقة للعثور على العدد بناءً على معايير الإدخال.

في هذا المثال ، أردت أن أجد عدد الطلاب أو الطالبات الذين حصلوا على علامات النجاح (أي> = 40). لذلك استخدمت الصيغة = COUNTIFS (B2: B10، “M”، C2: C10، “> = 40”). هنا ، B2: B10 هو النطاق الذي ستبحث فيه الصيغة عن المعيار الأول (الجنس) ، “M” هو المعيار الأول ، C2: C10 هو النطاق الذي ستبحث فيه الصيغة عن المعيار الثاني (العلامات) ، و “> = 40” هو المعيار الثاني.

الصيغة: = COUNTIFS (فئة_المعايير 1 ، معايير 1 ،…)

8. SUMPRODUCT

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

في هذا المثال ، أردت العثور على إجمالي جميع المنتجات المباعة. لذلك ، استخدمت الصيغة = SUMPRODUCT (B2: B8 ، C2: C8). هنا ، B2: B8 هي المصفوفة الأولى (كمية المنتجات المباعة) و C2: C8 هي المصفوفة الثانية (سعر كل منتج). تقوم الصيغة بعد ذلك بضرب كمية كل منتج يتم بيعه بسعره ثم تضيفها كلها لتسليم إجمالي المبيعات.

الصيغة: = SUMPRODUCT (array1، [array2]، [array3]، …)

9. إزالة

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

في هذا المثال ، أردت إزالة جميع المسافات الزائدة بين الكلمتين Mouse and pad في A7. لذلك استخدمت الصيغة = TRIM (A7).

قامت الصيغة ببساطة بإزالة المسافات الزائدة وقدمت لوحة الماوس الناتجة بمسافة واحدة.

الصيغة: = TRIM (نص)

10. إيجاد/ البحث

تقريب الأشياء هي وظائف FIND / SEARCH والتي ستساعدك على عزل نص معين داخل مجموعة بيانات. كلتا الوظيفتين متشابهتان تمامًا في ما تقومان به ، باستثناء اختلاف رئيسي واحد – تقوم وظيفة = FIND بإرجاع المطابقات الحساسة لحالة الأحرف فقط. وفي الوقت نفسه ، لا تحتوي وظيفة = SEARCH على مثل هذه القيود. هذه الوظائف مفيدة بشكل خاص عند البحث عن الانحرافات أو المعرفات الفريدة.

في هذا المثال ، أردت العثور على عدد المرات التي ظهرت فيها كلمة “Gui” في Guiding Tech التي استخدمت فيها الصيغة = FIND (A2، B2) ، والتي أعطت النتيجة 1. الآن إذا أردت العثور على عدد المرات ” gui ‘داخل Guiding Tech بدلاً من ذلك ، يجب أن أستخدم صيغة = SEARCH لأنها ليست حساسة لحالة الأحرف.

صيغة الايجاد : = FIND (find_text، within_text، [start_num])

صيغة البحث: = SEARCH (find_text، within_text، [start_num])

إتقان تحليل البيانات

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