أهم صيغ الإكسيل: 10 صيغ متقدمة للمحللين الماليين
يقضي أي محلل مالي وقتاً طويلاً في برنامج الإكسيل، أكثر مما قد يعترف به. استناداً إلى سنوات من الخبرة، يقدم هذا المقال أهم صيغ الإكسيل المتقدمة التي يجب على كل محلل مالي من الطراز العالمي معرفتها وفهمها. تعتبر صيغ الإكسيل أداة أساسية لا غنى عنها لعمليات التحليل المالي.
1. INDEX MATCH: قوة أهم صيغ الإكسيل في عمليات البحث المتقدمة
تعد INDEX MATCH من أهم صيغ الإكسيل ، و هي تركيبة مرنة لصيغ الإكسيل، والتي قد تساعد في الأبحاث والنماذج المالية. INDEX هي دالة جدول تقوم بإرجاع قيمة الخلية بناءً على عدد الصفوف والأعمدة في الجدول. MATCH تُرجع اتجاه صف أو عمود الخلية.
إليك كيفية دمج صيغ INDEX و MATCH. في هذا السيناريو، يمكنك البحث عن ارتفاع شخص وإرجاعه بناءً على اسمه. يجب تعديل الاسم والارتفاع في الحساب لأنهما عاملان.
2. IF في تركيبة مع AND/OR في التحليل المالي
الصيغة موضحة كالتالي:
=IF (AND (C2 أكبر من أو تساوي C4, C2 أقل من أو تساوي C5), C6, C7)
يعرف أي شخص أمضى وقتاً كبيراً في التعامل مع نماذج مالية مختلفة مدى صعوبة صيغ IF المتداخلة. ستكون الصيغ أسهل للتحقق والفهم إذا تم استخدام ميزة IF مع دالة AND / OR. يوضح الرسم البياني أدناه كيفية دمج الوظائف المنفصلة لإنشاء صيغة أكثر تعقيداً.
3. OFFSET في تركيبة مع SUM أو AVERAGE لتعزيز أهم صيغ الإكسيل
الصيغة موضحة كالتالي:
=SUM (B4 : OFFSET (B4,0, E2 ناقص 1))
دالة OFFSET ليست صعبة جداً، ولكن عند إقرانها بوظائف أخرى مثل SUM أو AVERAGE، يمكنك إنشاء صيغة معقدة. ضع في اعتبارك الحالة التالية: تريد إنشاء ميزة معقدة يمكنها إضافة عدد متغير من الخلايا معاً. يمكن لصيغة SUM المتوسطة فقط إجراء حساب ثابت، ولكن يمكنك تغيير علاقة الخلية عن طريق إضافة OFFSET.
إنها تعمل على النحو التالي: يمكنك الاستفادة من دالة OFFSET بدلاً من خلية المقارنة النهائية لدالة SUM لجعل هذا الحساب فعالاً. هذا يعقد الأسلوب؛ ومع ذلك، يمكنك إخبار Excel بعدد الخلايا المتتالية التي تريد جمعها في الخلية E2. صيغ Excel المعقدة متاحة لنا الآن. يصور لقطة الشاشة أدناه صيغة أكثر تعقيداً.
4. CHOOSE: أداة أساسية في التحليل المالي باستخدام صيغ الإكسيل
الصيغة موضحة كالتالي:
=CHOOSE (اختيار, خيار1, 2, 3)
بالنسبة لتحليل السيناريو في المحاكاة المالية، فإن دور CHOOSE مثالي. إنه يدعوك للاختيار من بين الخيارات المختلفة ويسمح لك بإرجاع “قرارك”. افترض أن لديك ثلاثة تقديرات مختلفة لنمو المبيعات للعام المقبل: 5% و 12% و 18%. باستخدام صيغة CHOOSE، يمكنك إخبار Excel بأنك ترغب في الاختيار رقم 2 والحصول على عائد بنسبة 12 بالمائة.
5. XNPV و XIRR: أدوات حيوية في أهم صيغ الإكسيل لتقييم الاستثمارات
الصيغة: = XNPV (معدل الخصم، تدفقات النقد، التواريخ)
إذا كنت تعمل في إدارة الاستثمار، وأبحاث السوق، والتخطيط المالي والتحليل (FP&A)، أو أي فرع من فروع الأعمال المالية التي تخصم تدفقات العملات، فقد تكون هذه الصيغ مفيدة.
تسمح لك XIRR و XNPV بتأريخ كل تدفق نقدي بخصم على حدة. تكمن المشكلة في صيغ NPV و IRR البسيطة في Excel في أنها تفترض أن الفواصل الزمنية بين تدفقات النقد متساوية. كخبير اقتصادي، ستصادف مواقف لا يتم فيها توزيع أرصدة النقد بالتساوي بشكل متكرر، وستساعدك هذه الصيغة في معالجة المشكلة.
6. SUMIF و COUNTIF في التحليل المالي: الصيغ الشرطية الهامة في صيغ الإكسيل
الصيغة: = COUNTIF (D5 : D12, “أكبر من أو تساوي 21”)
في هاتين الصيغتين الراسختين، يتم تطبيق الوظائف الشرطية بكفاءة. يتضمن SUMIF جميع الخلايا التي تحتاج إلى معايير، بينما يقوم COUNTIF بإحصاء جميع الخلايا التي تتطلب قياسات. على سبيل المثال، افترض أنك تريد حساب عدد حاويات الشمبانيا التي ستحتاجها لحدث عميل عن طريق حساب جميع الخلايا الأكبر من أو تساوي 21. (الحد الأدنى لسن الاستهلاك في الولايات المتحدة). يمكن استخدام COUNTIF كتقنية متقدمة، كما هو موضح في الصورة أدناه.
7. PMT و IPMT: صيغ أساسية للمحللين الماليين و تحليل الدين في أهم صيغ الإكسيل
الصيغة: = PMT (عدد الفترات، معدل الفائدة، القيمة الحالية)
إذا كنت تعمل في مجال العقارات، أو الخدمات المصرفية التجارية، أو التخطيط المالي والتحليل (FP&A)، أو غيرها من وظائف المحلل المالي التي تتعامل مع جداول الديون، فستحتاج إلى معرفة هاتين الصيغتين.
يحدد نظرية PMT قيمة سداد أقساط متساوية على مدار فترة القرض. يجب القيام بذلك بالتزامن مع IPMT (التي تخبرك بمقدار الفائدة التي ستدفعها مقابل نفس نوع القرض)، ثم يجب سداد أقساط رأس المال والفائدة المختلفة. باستخدام أداة PMT هنا، إليك كيفية حساب الدفعة الشهرية للرهن العقاري على قرض بقيمة مليون دولار بمعدل فائدة 5% على مدى 30 عاماً.
8. LEN و TRIM في صيغ الإكسيل: أدوات لتنظيم البيانات والتحضير للتحليل المالي
الصيغ معطاة أدناه:
=TRIM (نص) و
=LEN (نص)
الصيغ الموضحة أعلاه أكثر تحديداً، لكنها لا تزال معقدة. قد تكون مفيدة للمتخصصين في المالية الذين يحتاجون إلى إدارة وتعديل كميات كبيرة من البيانات. لسوء الحظ، لا يتم دائماً تنظيم البيانات التي تحصل عليها بشكل جيد، مما قد يؤدي إلى مشكلات مثل المسافات غير الضرورية في بداية الخلايا أو نهايتها.
عندما تحتاج إلى حساب عدد الأحرف الموجودة في نص ما، توفر صيغة LEN عدد الأحرف الموجودة في سلسلة نصية معينة. في الرسم البياني أدناه، يمكنك أن ترى كيف تقوم خوارزمية TRIM بتنظيف بيانات Excel.
9. CONCATENATE في أهم صيغ الإكسيل لدمج البيانات لتسهيل التحليل المالي
الصيغة موضحة كالتالي:
=A1& “المزيد من النصوص.”
الدمج ليس وظيفة؛ إنها طريقة ذكية لدمج البيانات من العديد من الخلايا وجعل جداول البيانات أكثر تعقيداً. هذه أداة مفيدة للمحللين الماليين الذين يقومون بإجراء محاكاة مالية.
يتم دمج كلمة “نيويورك” مع “NY” في المثال أدناه لإنتاج “نيويورك، نيويورك”. يتيح لك هذا إنشاء رؤوس وتسميات ديناميكية لأوراق العمل الخاصة بك. بدلاً من تحديث الخلية B8، ستقوم بتحسين الخلايا B2 و D2 بشكل منفصل. هذه جودة ممتازة يجب توفرها عند التعامل مع كميات كبيرة من البيانات.
10. CELL و MID و LEFT و RIGHT في صيغ الإكسيل: الأدوات المتقدمة والضرورية في التحليل المالي
يمكن استخدام إمكانات Excel المتطورة هذه لإنشاء صيغ معينة وصعبة للغاية. يمكن لميزة CELL إرجاع مجموعة متنوعة من المعلومات حول محتويات الخلية (مثل اسمها وموقعها وصفها وعمودها وغير ذلك). تقوم الدالة LEFT بإرجاع النص من نقطة بداية الخلية (من اليسار إلى اليمين)، وتقوم الدالة MID بإرجاع النص من نقطة بداية أي خلية (من اليسار إلى اليمين)، وتقوم الدالة RIGHT بإرجاع النص من نقطة نهاية الخلية (من اليمين إلى اليسار).
يوضح الرسم البياني أدناه الصيغ الثلاث.
4.1 صيغ Excel: دليل الغش لتعزيز التحليل المالي وزيادة الكفاءة
إذا كنت ترغب في إتقان التحليل المالي في Excel وتصبح خبيراً في تطوير النماذج المالية، فقد وصلت إلى المكان الصحيح. يتكون هذا الفصل من ورقة غش لصيغ Excel تتضمن أهم الصيغ والوظائف لإتقان جداول البيانات.
الوقت والتاريخ في صيغ Excel في ورقة الغش
=EDATE – في Excel، أدخل تاريخاً لعدد معين من الأشهر.
=DATE – في Excel، تُرجع هذه الدالة عدداً صحيحاً يمثل التاريخ (yyyy/mm/dd).
=EOMONTH – غيّر التاريخ إلى اليوم النهائي من الشهر (على سبيل المثال، من 18 يوليو إلى 31 يوليو).
=YEAR – في Excel، يستخرج ويعرض السنة من تاريخ (على سبيل المثال، 18/7/2018 إلى 2018).
=YEARFRAC – يمثل نسبة سنة بين تاريخين (على سبيل المثال، 1/1/2018 – 3/31/2018 = 0.25)
=NETWORKDAYS – تعيد هذه الدالة عدد أيام العمل الكاملة بين التاريخين المقدمين.
=TODAY – في خلية، اكتب تاريخ اليوم وقم بعرضه.
تحويل الوقت إلى ثوان – يحول الفاصل الزمني إلى ثوانٍ (على سبيل المثال، 5 دقائق إلى ثلاثمائة ثانية)
صيغ التنقل في Excel لورقة الغش
ابحث عن جميع الرموز الثابتة والصيغ والمزيد من الخلايا بالانتقال إلى خاص والضغط على F5.
إذا ضغطت على Ctrl + F، يمكنك تغيير أقسام من العديد من الصيغ في وقت واحد.
صيغ البحث
INDEX MATCH – مجموعة من إجراءات البحث التي تعد أكثر فاعلية من VLOOKUP
=VLOOKUP – وظائف البحث التي تسعى عمودياً في جدول
=HLOOKUP – وظائف البحث التي تسعى أفقياً في جدول
=INDEX – طريقة بحث في الجدول تبحث عمودياً وأفقياً
=MATCH – طريقة بحث في الجدول تبحث عمودياً وأفقياً
=OFFSET – ينقل مرجع خلية بعدد الصفوف و/أو الأعمدة.
الدوال الحسابية لصيغ Excel في ورقة الغش
=SUM – إضافة مجموعات من الأرقام
=AVERAGE – دالة تحسب متوسط سلسلة من الأعداد الصحيحة
=MEDIAN – ترجع رقم متوسط لمجموعة
=SUMPRODUCT – تحسب المتوسط المرجح، وهو أمر مفيد في التحليل المالي
=PRODUCT – تضرب جميع الأعداد الصحيحة في قائمة
=ROUNDDOWN – تقريب الرقم إلى العدد المطلوب من الخانات
=ROUNDUP – يتم تقريب الرقم إلى العدد المحدد من الخانات باستخدام الصيغة.
AutoSum – اختصار لحساب مجموعة من الأرقام بسرعة
=ABS – إرجاع القيمة المطلقة للرقم
=PI – يرجع القيمة إلى 15 رقم دقة.
=SUMQ – يتم إرجاع مجموع مربعات الوسيطات.
=SUMIF – مجموع قيم المقياس الذي يتم تقييده بمطلب
الصيغ المالية
=NPV – بناءً على معدل خصم، تحسب القيمة الحالية المتبقية لتدفقات النقد
=XNPV – تحسب صافي القيمة الحالية (NPV) لتدفقات النقد المستقبلية بناءً على تواريخ معينة ومعدل خصم
=IRR – يتم حساب المعدل الداخلي للعائد باستخدام هذه الصيغة (معدل الخصم الذي يعدل NPV إلى الصفر)
=XIRR – مع الأخذ في الاعتبار التواريخ، قم بحساب المعدل الداخلي للعائد (معدل الخصم الذي يقلل NPV إلى الصفر).
=YIELD – تحصل على عائد على ضمان يعتمد على قيمته الاسمية وتاريخ استحقاقه وسعر الفائدة.
=FV – تحسب القيمة المستقبلية للاستثمار مع دفعات شهرية ثابتة وسعر فائدة ثابت.
=PV – يقيم قيمة الاستثمار الحالية
=INTRATE – سعر الفائدة على الأوراق المالية المستثمرة بالكامل
=IPMT – يتم احتساب مدفوعات الفائدة باستخدام هذه الصيغة (سند الدين)
=PMT – تعيد هذه الطريقة إجمالي الدفع على سند الدين (الدين بالإضافة إلى الفائدة).
=PRICE – تقديرات سعر السند الذي يكرر القسيمة لكل قيمة اسمية قدرها 100 دولار
=DB – يتم حساب الإهلاك باستخدام نهج الرصيد المتناقص الثابت.
=DDB – يتم استخدام نهج الرصيد المتناقص المزدوج لحساب الإهلاك.
تساعد هذه الصيغ و التقنيات المحللين الماليين على إجراء تقييمات دقيقة. تعد هذه الصيغ و الأدوات هي أساس عمل المحلل المالي، وتساعد على تقديم تقييمات واضحة لقراراتهم في مجال التحليل المالي .
=SLN – يحسب الإهلاك باستخدام نهج السطر المتتالي
الدوال الشرطية
=IF – إذا تم تلبية شرط ما، فإنه يرجع قيمة؛ إذا لم يكن كذلك، فإنه يرجع قيمة فارغة.
=OR – لا تُرجع إلا “FALSE” أو “TRUE” إذا تم تلبية أي متطلبات.
=XOR – إذا كان عدد تأكيدات TRUE فردياً، فإن تأكيد “xor الحصري” يعيد القيمة True.
=AND – لا يُرجع إلا “TRUE” أو “FALSE” إذا تم استيفاء جميع المتطلبات.
=NOT – يصبح “TRUE” “FALSE”، ويصبح “FALSE” “TRUE.”
=IF AND – إذا قمت بخلط IF مع AND، فسيكون لديك الكثير من الاحتمالات.
=IFERROR – إذا احتوت خلية على خطأ أو خطأ، فيمكنك توجيه Excel لعرض نتيجة مختلفة.
الدوال والصيغ الأخرى
Sheet Name Code – صيغة تعرض اسم ورقة العمل باستخدام وظائف MID و CELL و FIND
Consolidate – كيفية إيجاد قطعة بيانات معينة عبر العديد من مصنفات Excel
