17 معادلة إكسل متقدمة - يجب أن يعرفها جميع المحترفين

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

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

تصفح القائمة وأخبرنا بمفضلتك!

يحصل المتعلمون على متوسط ​​زيادة في الراتب بنسبة 58٪ مع ارتفاع أعلى يصل إلى 400٪.

1. مطابقة الفهرس

الصيغة = INDEX (C3: E9، MATCH (B13، C3: C9،0)، MATCH (B14، C3: E3،0))

بديل ممتاز لصيغة VLOOKUP أو HLOOKUP على Excel يحتوي على بعض العيوب لأداء مهام البحث. INDEX MATCH هي صيغة مركبة من وظيفتين منفصلتين:

INDEX: تُرجع هذه الصيغة قيمة خلية في جدول بناءً على العمود ورقم الصف.

تطابق: تعرض هذه الصيغة موضع خلية في صف أو عمود.

مثال على الجمع بين الصيغتين INDEX و MATCH هو: عندما تبحث عن ارتفاع شخص ما وتعود إليه بناءً على اسمه ، يمكنك استخدام هذه الصيغة لتغيير كلا المتغيرين (في هذه الحالة ، الاسم والارتفاع) باستخدام صيغة INDEX MATCH .

مصدر

دعونا نقسمها خطوة بخطوة:

كيفية الجمع بين INDEX و MATCH

• اكتب INDEX
• حدد المنطقة التي تريد فهرستها
• قفل المنطقة باستخدام F4
• ابحث عن الصف الذي تريد البحث عن البيانات منه
• اكتب MATCH مع سلسلة المعلومات وأغلق المنطقة باستخدام F4
• اكتب 0 للتطابق التام ، أغلق الأقواس
• هاهنا

أفكار مثيرة للاهتمام لمشروع علوم البيانات

الآن لديك مجموعة ديناميكية ووظيفية بالكامل من الأعمدة والصفوف في الورقة حيث يتم كل التمشيط عبر الخلايا والصفوف للحصول على البيانات الصحيحة تلقائيًا.

2. متوسط

لتنفيذ صيغة المتوسط ​​للعثور على متوسط ​​أي نطاق من الأرقام ، إليك الخطوات التي تحتاج إلى اتباعها:

أدخل القيم أو الخلايا أو مقياس الخلايا التي تحسبها بالتنسيق.

يجب أن تبدأ الصيغة بـ = AVERAGE (رقم 1 ، رقم 2 ، إلخ.)

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

• أدخل القيم أو الخلايا أو مقياس الخلايا التي تحسبها بالتنسيق ، تمامًا كما فعلت أعلاه
• يجب أن تكون الصيغة مثل = AVERAGE (قيمة البداية: القيمة النهائية).

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

3. SUMIF

يشار إلى هذه الصيغة في Excel على أنها SUMIF (النطاق ، المعايير ، [مجموع النطاق]). قد ينتج عن ذلك مجموع القيم ضمن نطاق الخلايا المطلوب الذي يلبي المتطلبات التي حددتها. على سبيل المثال ، تُرجع = SUMIF (C3: C12، “> 70000) مجموع القيم بين خلايا C3 و C12 من الخلايا التي تحتوي على قيمة أكثر من 70000 فقط.

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

يمكن أن تكون صيغة الشرط أعلاه = SUMIF (النطاق ، المعايير ، [sum_range]) ، حيث يمكن تعريف النطاق على أنه نطاق الورقة الذي تحتاج إلى اختيار القيم منه.

يتم تعريف [sum_range] على أنه النطاق الإضافي أو الاختياري الذي ستقوم بإضافته بالإضافة إلى النطاق الأول الذي تم إدخاله.

هذا مثال:

مصدر

4. تعويض مجمعة مع SUM

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

لذلك لمعرفة مجموع القيم في الخلايا المتغيرة ، يجب أن تكون الصيغة:

= SUM (B4: OFFSET (B4،0، E2-1))

مصدر

هنا يتم استبدال مرجع النهاية الخاص بوظيفة SUM بوظيفة OFFSET. تنتهي صيغة SUM التي تبدأ في B4 بمتغير وهي صيغة OFFSET تبدأ من B4 ، وتستمر بالقيمة في E2 ("3") مطروحًا منها واحد. يساعد هذا الأسلوب في التحرك على خليتين وتلخيص ثلاث سنوات من البيانات. في المرجع أعلاه ، يمكنك أن ترى أن الخلية F7 تحتوي على مجموع الخلايا B4: D4 = 15.

5. إذا كان و

هذه الصيغة مفيدة في المواقف التي تحتاج فيها إلى إنشاء شروط معينة لتمشيط كومة من البيانات. تساعد عبارة IF في استخدام وظيفة Excel IF المتقدمة لإنشاء حقل جديد بناءً على هذه الشروط في مسار موجود بالفعل.

على سبيل المثال ، إذا كنت تريد تمييز الموظفين الذين تزيد رواتبهم عن 50 ألفًا ومعرف الموظف أكبر من 3 ، فستكون الصيغة:

إذا (و (E4> 3، F4> 50000) 1،0)

مصدر

نظرًا لعدم وجود حالات حقيقية في البيانات أعلاه ، ستُرجع الصيغة القيمة 0.

6. تسلسل

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

= CONCATENATE (B3، C3)

مصدر

7. PMT

ستساعدك هذه الوظيفة في معرفة الدفعات المستقبلية المستحقة للقرض ، مع الأخذ في الاعتبار معدل الفائدة المحدد ، والمبلغ الأساسي ، ومدة القرض. إليك ما تحتاجه للبدء:

• مدة القرض
• أصل القرض (المال)
• القيمة المستقبلية
• نوع القرض

الآن ، إذا كنت تريد العثور على الدفعة الشهرية للمبلغ الأساسي ، فستكون صيغة نفس المبلغ:

= PMT (معدل ، لكل ، PV ، [fv] ، [النوع])

دعونا نفهم هذا بمثال:

مصدر

= PMT (C2 / 12.B2.A2)

وأفضل جزء هو أنه يمكنك سحب الزاوية اليمنى السفلية لخلايا PMT عبر الحقول لحساب مبلغ الدفع الشهري تلقائيًا لجميع الحقول!

الأنواع الشائعة لوظائف علوم البيانات

8. تقليم

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

= TRIM (C6)

مصدر

سيعيدك هذا بقيمة Chandan Kale دون أي مسافات إضافية متصلة بالأمام أو النهاية.

9. لين

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

= LEN (B2)

مصدر

وإذا كنت تريد تمييز جميع الخلايا في عمود التبرع ، فأنت بحاجة إلى:

• حدد علامة التبويب الصفحة الرئيسية في القائمة
• انقر فوق تنسيق شرطي (في شريط الأدوات)
• حدد استخدام صيغة لتحديد الخلايا المراد تنسيقها

مصدر

هنا ، إذا قمت بإجراء الشرط "> 3" ، فسيحصل أي شيء يزيد عن 1000 دولار على التنسيق الفريد ، الذي يمكنك اختياره بالنقر فوق خيار التنسيق.

10. اختر

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

الصيغة هي:

= اختر (C7، D3، D4، D5)

مصدر

11. CELL ، LEFT ، MID ، و RIGHT

يمكن دمج جميع الوظائف المذكورة أعلاه بعدة طرق للحصول على بعض الصيغ المتقدمة.

• تُستخدم وظيفة CELL لإرجاع أنواع مختلفة من المعلومات حول محتويات الخلية
• تُستخدم خدمة LEFT لاستبدال النص من بداية الخلية.
• يمكن للدالة MID إرجاع النص من أي نقطة بداية للخلية.
• تقوم الدالة RIGHT بإرجاع النص من نهاية الخلية فقط.

مصدر

12. XNPV و XIRR

بالنسبة لجميع المحللين الذين يصادفون الخدمات المصرفية الاستثمارية أو أبحاث الأسهم أو أي مجال آخر من مجالات تمويل الشركات التي تتطلب خصم التدفقات النقدية ، فمن المؤكد أن هذه الصيغ ستوفر لك الكثير من الوقت والتذمر!

أسئلة وأجوبة مقابلة علوم البيانات

على سبيل المثال ، إذا كنت تريد حساب القيمة الصافية الحالية (NPV) لأي استثمار باستخدام معدل محدد للخصم والتدفقات النقدية التي تحدث على فترات غير منتظمة ، فاستخدم الوظيفة التالية.

= XNPV (معدل الخصم ، التدفقات النقدية ، التواريخ)

مصدر

من ناحية أخرى ، تخبرك الدالة XIRR بمعدل العائد الداخلي (حيث التدفق = التدفق) لسلسلة من التدفقات النقدية التي تحدث على فترات غير منتظمة ، مثل:

13. كونتا ()

غالبًا ما يجد المحللون صعوبة في العثور على عدد الخلايا ذات القيم (الأرقام والأخطاء والنصوص والقيم المنطقية) وتلك الفارغة. يمكن أن تساعدك وظيفة COUNTA () في معرفة اسم الخلايا غير الفارغة في النطاق المحدد. على سبيل المثال ، صيغة حساب قيم ورقة البيانات التي تحتوي على أعمدة A1-A10 هي:

= COUNTA (A1: A10)

مصدر

14. FV

هذه الصيغة مفيدة إذا كنت تتطلع إلى استثمار الأموال في شيء ما ومعرفة قيمته. متطلبات صيغة FV هي:

• معدل الفائدة على القرض
• عدد الدفعات
• الدفع عن كل فترة
• رصيد البداية الحالي
• نوع القرض

على سبيل المثال ، إذا كنت تريد مقارنة عدة أقراص مضغوطة فارغة ولديك ميراثًا بقيمة 20000 دولار لاستثماره في قرص مضغوط. يتم تمثيل أسعار الفائدة في شكل عشري ؛ المدفوعات صفر. ستكون صيغة السيناريو:

= FV (A2 / 12، B2، C2، D2)

مصدر

ستكون النتائج:

مصدر

15. راندبيتوين

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

= RANDBETWEEN (نقطة البداية ، نقطة النهاية)

مصدر

16. صغير

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

بناء جملة الصيغة

= صغير (مرات ، نطاق)

علي سبيل المثال،

مصدر

نظرًا لأن وظيفة SMALL تلقائية ، فأنت بحاجة إلى توفير نطاق وعدد صحيح لـ "nth" لتحديد القيمة المرتبة. الأسماء الرسمية لهذه الوسيطات هي "مصفوفة" و "ك".

17. رباعي

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

بناء الجملة: = QUARTILE (مصفوفة ، ربع)

مصدر

خاتمة:

يعد Microsoft Excel أمرًا لا مفر منه عندما يتعلق الأمر بمكان العمل في القرن الحادي والعشرين ، ولكن الحيلة هي أنه لا يجب أن يكون شاقًا للغاية. اجعلها صديقك ، وشاهد إنتاجيتك تزداد!