صيغ Excel المتقدمة - 10 صيغ يجب أن تعرفها!

كل محلل مالي The Analyst Trifecta® Guide الدليل النهائي لكيفية أن تكون محللًا ماليًا على مستوى عالمي. هل تريد أن تكون محلل مالي على مستوى عالمي؟ هل تتطلع إلى اتباع أفضل الممارسات الرائدة في المجال والتميز عن الآخرين؟ تتكون عمليتنا ، المسماة The Analyst Trifecta® ، من التحليلات والعرض التقديمي والمهارات الشخصية التي تقضي وقتًا أطول في Excel مما قد يهتمون بالاعتراف به. استنادًا إلى سنوات وسنوات من الخبرة ، قمنا بتجميع أهم صيغ Excel وأكثرها تقدمًا والتي يجب أن يعرفها كل محلل مالي عالمي.

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

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

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

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

تُرجع MATCH موضع خلية في صف أو عمود.

فيما يلي مثال على صيغتي INDEX و MATCH مجتمعين معًا. في هذا المثال ، نبحث ونرجع ارتفاع الشخص بناءً على اسمه. نظرًا لأن الاسم والارتفاع كلاهما متغيرين في الصيغة ، يمكننا تغيير كليهما!

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

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

2. إذا تم دمجها مع و / أو

الصيغة: = IF (AND (C2> = C4، C2 <= C5)، C6، C7)

أي شخص قضى وقتًا طويلاً في عمل أنواع مختلفة من النماذج المالية أنواع النماذج المالية تشمل أكثر أنواع النماذج المالية شيوعًا: نموذج بيان 3 ، نموذج DCF ، نموذج M&A ، نموذج LBO ، نموذج الميزانية. اكتشف أفضل 10 أنواع يعرف أن صيغ IF المتداخلة يمكن أن تكون كابوسًا. يمكن أن يكون دمج IF مع AND أو الدالة OR طريقة رائعة للحفاظ على سهولة تدقيق الصيغ وتسهيل فهمها على المستخدمين الآخرين. في المثال أدناه ، سترى كيف استخدمنا الوظائف الفردية معًا لإنشاء صيغة أكثر تقدمًا.

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

3. OFFSET مع SUM أو AVERAGE

الصيغة: = SUM (B4: OFFSET (B4،0، E2-1))

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

كيف تعمل: لجعل هذه الصيغة تعمل ، نستبدل الخلية المرجعية النهائية للدالة SUM بوظيفة OFFSET. هذا يجعل الصيغة ديناميكية والخلية المشار إليها كـ E2 هي المكان الذي يمكنك فيه إخبار Excel بعدد الخلايا المتتالية التي تريد إضافتها. الآن لدينا بعض صيغ Excel المتقدمة!

يوجد أدناه لقطة شاشة لهذه الصيغة الأكثر تعقيدًا في العمل.

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

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

4. اختر

الصيغة: = اختر (خيار ، خيار 1 ، خيار 2 ، خيار 3)

وظيفة CHOOSE CHOOSE Function يتم تصنيف وظيفة CHOOSE ضمن وظائف Excel للبحث والمراجع. سيعيد قيمة من مصفوفة تتوافق مع رقم الفهرس المقدم. ستعيد الوظيفة الإدخال رقم n في قائمة معينة. كمحلل مالي ، تعد وظيفة CHOOSE مفيدة في الاختيار من مجموعة معينة من البيانات. على سبيل المثال ، نحن رائعون لتحليل السيناريو في النمذجة المالية. يسمح لك بالاختيار بين عدد محدد من الخيارات ، وإرجاع "الاختيار" الذي حددته. على سبيل المثال ، تخيل أن لديك ثلاثة افتراضات مختلفة لنمو الإيرادات في العام المقبل: 5٪ و 12٪ و 18٪. باستخدام صيغة CHOOSE ، يمكنك إرجاع 12٪ إذا أخبرت Excel أنك تريد الخيار رقم 2.

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

لمشاهدة عرض توضيحي بالفيديو ، تحقق من دورة صيغ Excel المتقدمة.

5. XNPV و XIRR

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

إذا كنت محللًا يعمل في مجال الخدمات المصرفية الاستثمارية ، دليل المسار الوظيفي للخدمات المصرفية الاستثمارية - خطط مسار حياتك المهنية في البكالوريا الدولية. تعرف على رواتب الخدمات المصرفية الاستثمارية ، وكيفية الحصول عليها ، وماذا تفعل بعد مهنة في البكالوريا الدولية. يساعد قسم الخدمات المصرفية الاستثمارية (IBD) الحكومات والشركات والمؤسسات على زيادة رأس المال وإتمام عمليات الدمج والاستحواذ (M&A). ، وبحوث الأسهم ، والتخطيط والتحليل المالي (FP&A FP&A Analyst كن محلل FP&A في شركة. نحن نحدد الراتب ، والمهارات ، والشخصية ، والتدريب الذي تحتاجه لوظائف FP&A ومهنة مالية ناجحة. محللو FP&A والمديرون والمديرون هم مسؤول عن تزويد المديرين التنفيذيين بالتحليل والمعلومات التي يحتاجون إليها) ، أو أي مجال آخر من مجالات تمويل الشركات التي تتطلب خصم التدفقات النقدية ،فهذه الصيغ هي المنقذة!

ببساطة ، يتيح لك XNPV و XIRR تطبيق تواريخ محددة على كل تدفق نقدي فردي يتم خصمه. تكمن مشكلة معادلات Excel الأساسية NPV و IRR في أنها تفترض أن الفترات الزمنية بين التدفق النقدي متساوية. بشكل روتيني ، كمحلل ، سيكون لديك مواقف لا يتم فيها توقيت التدفقات النقدية بالتساوي ، وهذه الصيغة هي كيفية إصلاح ذلك.

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

6. SUMIF و COUNTIF

الصيغة: = COUNTIF (D5: D12، "> = 21 ″)

تعد هاتان الصيغتان المتقدمتان استخدامات رائعة للوظائف الشرطية. يضيف SUMIF جميع الخلايا التي تفي بمعايير معينة ، وتحسب COUNTIF جميع الخلايا التي تفي بمعايير معينة. على سبيل المثال ، تخيل أنك تريد حساب جميع الخلايا الأكبر من أو تساوي 21 (العمر القانوني للشرب في الولايات المتحدة) لمعرفة عدد زجاجات الشمبانيا التي تحتاجها لحدث العميل. يمكنك استخدام COUNTIF كحل متقدم ، كما هو موضح في لقطة الشاشة أدناه.

في دورة Excel المتقدمة لدينا ، قمنا بتقسيم هذه الصيغ بمزيد من التفصيل.

7. PMT و IPMT

الصيغة: = PMT (معدل الفائدة ، # الفترات ، القيمة الحالية)

إذا كنت تعمل في مجال الخدمات المصرفية التجارية ملف تعريف العمل في الخدمات المصرفية التجارية ، فإن المسار الوظيفي للخدمات المصرفية التجارية هو تزويد العملاء بمنتجات ائتمانية مثل القروض لأجل وخطوط الائتمان المتجددة والتسهيلات المشتركة وخدمات إدارة النقد ومنتجات الدخل الثابت الأخرى. كمحلل ائتماني أو مدير حساب ، فإنك تقدم المشورة المالية ، والعقارات ، والتخطيط والتحليل المالي FP&A FP&A (FP&A) وظيفة مهمة في الشركة. يدعم محترفو FP&A اتخاذ القرارات التنفيذية للرئيس التنفيذي والمدير المالي ومجلس الإدارة بالتخطيط والتحليل والنمذجة. تعرف على ما تتطلبه وظيفة محلل أو مدير أو مدير FP&A - الراتب أو المتطلبات أو التعليم أو المهارات أو أي منصب محلل مالي يتعامل مع جداول الديون ، فأنت تريد فهم هاتين الصيغتين التفصيليتين.

تمنحك صيغة PMT قيمة المدفوعات المتساوية على مدى عمر القرض. يمكنك استخدامه مع IPMT (الذي يخبرك بدفعات الفائدة لنفس النوع من القروض) ، ثم فصل مدفوعات رأس المال والفائدة.

فيما يلي مثال على كيفية استخدام وظيفة PMT للحصول على دفعة الرهن العقاري الشهرية لرهن عقاري بقيمة 1 مليون دولار بنسبة 5٪ لمدة 30 عامًا.

8. LEN و TRIM

الصيغ: = LEN (نص) و = TRIM (نص)

هذه الصيغ أقل شيوعًا ، لكنها بالتأكيد صيغ معقدة للغاية. هذه التطبيقات رائعة للمحللين الماليين The Analyst Trifecta® Guide هو الدليل النهائي لكيفية أن تكون محللًا ماليًا على مستوى عالمي. هل تريد أن تكون محلل مالي على مستوى عالمي؟ هل تتطلع إلى اتباع أفضل الممارسات الرائدة في المجال والتميز عن الآخرين؟ تتكون عمليتنا ، المسماة The Analyst Trifecta® ، من التحليلات والعرض التقديمي والمهارات اللينة التي تحتاج إلى تنظيم ومعالجة كميات كبيرة من البيانات. لسوء الحظ ، فإن البيانات التي نحصل عليها ليست دائمًا منظمة تمامًا ، وفي بعض الأحيان قد تكون هناك مشكلات مثل المساحات الإضافية في بداية الخلايا أو نهايتها

في المثال أدناه ، يمكنك أن ترى كيف تقوم صيغة TRIM بتنظيف بيانات Excel.

9. تسلسل

الصيغة: = A1 & "نص إضافي"

التسلسل ليس وظيفة بحد ذاتها - إنها مجرد طريقة مبتكرة لضم المعلومات من خلايا مختلفة وجعل أوراق العمل أكثر ديناميكية. هذه أداة قوية للغاية للمحللين الماليين الذين يؤدون النمذجة المالية (راجع دليل النمذجة المالية المجاني دليل النمذجة المالية المجانية.يغطي دليل النمذجة المالية هذا نصائح Excel وأفضل الممارسات حول الافتراضات والمحركات والتنبؤ وربط البيانات الثلاثة وتحليل DCF والمزيد أعرف أكثر).

في المثال أدناه ، يمكنك مشاهدة كيفية ربط النص "New York" بالإضافة إلى "،" مع "NY" لإنشاء "New York، NY". يتيح لك ذلك إنشاء عناوين وتسميات ديناميكية في أوراق العمل. الآن ، بدلاً من تحديث الخلية B8 مباشرةً ، يمكنك تحديث الخليتين B2 و D2 بشكل مستقل. مع وجود مجموعة كبيرة من البيانات ، تعد هذه مهارة قيّمة تحت تصرفك.

10. وظائف CELL و LEFT و MID و RIGHT

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

يوجد أدناه رسم توضيحي لهذه الصيغ الثلاث قيد التنفيذ.

لنرى كيف يمكن دمجها بطريقة قوية مع وظيفة CELL ، نقوم بتقسيمها لك خطوة بخطوة في فئة صيغ Excel المتقدمة الخاصة بنا.