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

1. سلسل

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

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

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

2. لين

= 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 مفيدة للبحث عن بيانات محددة بناءً على قيمة إدخال. يمكن للفهرس والمطابقة ، عند استخدامهما معًا ، التغلب على قيود 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 للحصول على نفس النتيجة.

معادلة: = الفهرس (عمود البيانات التي تريد إرجاعها ، 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 (أدنى_نطاق ، نطاق_المعايير 1 ، معايير 1 ، ...)

صيغة MAXIFS: = MAXIFS (أقصى_نطاق ، نطاق_المعايير 1 ، معايير 1 ، ...)

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 في ضرب النطاقات أو المصفوفات معًا ثم إرجاع مجموع المنتجات. إنها وظيفة متعددة الاستخدامات تمامًا ويمكن استخدامها لحساب المصفوفات وجمعها مثل 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_text، within_text، [start_num])

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