أسبوع التخفيضات الكبرىClaude Skills — خصم 20%
Tips

كيفية إجراء تحليل التباين في Excel: خطوة بخطوة

Powerdrill Bloom·
كيفية إجراء تحليل التباين في Excel: خطوة بخطوة

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

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

ماذا يظهر تحليل التباين

التباين هو الفجوة بين ما خططت له وما حدث بالفعل. يسرد تحليل التباين تلك الفجوات بنداً ببند ويشرح الفجوات المؤثرة منها.

تقارن معظم الفرق النتائج الفعلية بواحد من ثلاثة خطوط أساس:

  • الميزانية (Budget). الخطة الموضوعة في بداية العام.
  • التوقعات (Forecast). خطة محدثة يتم وضعها خلال العام.
  • الفترة السابقة (Prior period). الشهر الماضي، أو الربع الماضي، أو نفس الفترة من العام الماضي.

تصف مهارة Copilot الخاصة بشركة Microsoft لهذه المهمة العمل بشكل جيد. مهارة تحليل التباين الخاصة بها "تقارن النتائج الفعلية بالميزانيات أو التوقعات أو الفترات السابقة وتشرح الاختلافات حسب السعر أو الحجم أو المزيج أو المعدل أو التوقيت".

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

الصيغ الأساسية

تقوم ثلاث صيغ بمعظم العمل. افترض أن الميزانية في العمود B والنتائج الفعلية في العمود C.

المقياس الصيغة ما تخبرك به
التباين بالدولار =C2-B2 مدى بعد الرقم الفعلي عن الميزانية، بالعملة
التباين بالنسبة المئوية =IFERROR((C2-B2)/ABS(B2),"") مدى حجم الفجوة بالنسبة للخطة
الحالة =IF(D2=0,"On plan",IF(D2>0,"Above plan","Below plan")) الاتجاه الذي تحرك فيه البند

تحافظ دالة ABS على صحة الإشارة عندما تكون الميزانية سالبة. وتترك دالة IFERROR الخلية فارغة عندما تكون الميزانية صفراً، بدلاً من إظهار خطأ.

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

كيفية إعداد ورقة العمل

التخطيط المنظم يجعل نسخ الصيغ أمراً سهلاً. استخدم صفاً واحداً لكل بند وهذه الأعمدة:

  1. البند. الإيرادات، تكلفة البضائع المباعة، الرواتب، التسويق، وما إلى ذلك.
  2. النوع. أدخل "Revenue" أو "Cost" حتى يعرف Excel أي اتجاه هو الأفضل.
  3. الميزانية (Budget). المبلغ المخطط له للفترة.
  4. الفعلي (Actual). المبلغ المستخرج من بياناتك المحاسبية.
  5. التباين (Variance). الفعلي ناقص الميزانية.
  6. نسبة التباين %. التباين مقسوماً على الميزانية.
  7. ملائم أو غير ملائم (Favorable or unfavorable). تسمية بناءً على النوع والاتجاه.
  8. التفسير. ملاحظة قصيرة للبنود الجوهرية.

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

قم بتنسيق الجدول كجدول Excel باستخدام Ctrl+T. ستحصل الصفوف الجديدة حينها على الصيغ تلقائياً.

كيفية حساب التباينات في Excel

بمجرد تجهيز التخطيط، تصبح الخطوات قصيرة. افترض أن الأعمدة المذكورة أعلاه تبدأ في الصف 2، مع وجود النوع في B، والميزانية في C، والفعلي في D.

  1. في E2، أدخل =D2-C2 للتباين بالدولار.
  2. في F2، أدخل =IFERROR((D2-C2)/ABS(C2),"") وقم بتنسيقها كنسبة مئوية.
  3. في عمود الحالة، أدخل اختبار الملاءمة الموضح أدناه.
  4. انسخ الصيغ لأسفل لكل الصفوف.
  5. أضف صفاً للإجمالي، واحسب تباينه من الإجماليات، وليس بجمع النسب المئوية.

إليك اختبار الملاءمة للصف 2 من عمود الحالة:

=IF(E2=0,"On plan",IF(B2="Revenue",IF(E2>0,"Favorable","Unfavorable"),IF(E2<0,"Favorable","Unfavorable")))

تقرأ الصيغة عمود النوع أولاً. بالنسبة للإيرادات، التباين الإيجابي ملائم. بالنسبة للتكاليف، التباين السلبي ملائم لأنك أنفقت أقل مما هو مخطط له.

تحتاج النسب المئوية إلى عناية في صف الإجمالي. فجمع نسب البنود يعطي نتيجة خاطئة. اقسم إجمالي التباين على إجمالي الميزانية بدلاً من ذلك.

كيفية تمييز التباينات المؤثرة

ليست كل فجوة تحتاج إلى ملاحظة. تضع معظم الفرق قاعدة للأهمية النسبية، مثل "أكثر من 5% وأكثر من 2,000 دولار". البنود التي تجتاز كلا الاختبارين تحصل على تفسير.

صيغة بسيطة تميزها. أضف عموداً للأهمية النسبية (Material) وأدخل =AND(ABS(F2)>0.05,ABS(E2)>2000) في الصف 2. ستظهر الخلية TRUE للبنود الجوهرية.

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

لإضافة واحدة، حدد عمود نسبة التباين %. ثم انتقل إلى Home > Conditional Formatting > Icon Sets. تشير Microsoft إلى أنه في مجموعة الأسهم الثلاثة، "يمثل السهم الأخضر المتجه للأعلى القيم الأعلى". بالنسبة لصفوف التكلفة، اعكس ترتيب الأيقونات بحيث يظهر الارتفاع كتحذير.

كيفية شرح المحركات

توضح الأرقام مكان الفجوة، بينما تشرح المحركات السبب. تسرد مهارة Microsoft خمسة محركات شائعة: السعر، والحجم، والمزيج، والمعدل، والتوقيت.

السعر. بعت أو اشتريت بسعر مختلف عما كان مخططاً له. تباين السعر = (السعر الفعلي − سعر الميزانية) × الوحدات الفعلية.

الحجم. بعت أو استخدمت كمية مختلفة. تباين الحجم = (الوحدات الفعلية − وحدات الميزانية) × سعر الميزانية.

المزيج. تغيرت حصة المنتجات أو المناطق أو العملاء. التحول نحو بنود ذات هامش ربح أقل يقلل الربح حتى لو ظل الحجم ثابتاً.

المعدل. تغير معدل التكلفة، مثل الأجر بالساعة أو رسوم المورد.

التوقيت. تحركت الأموال بين الفترات. فاتورة شهر مارس التي تم دفعها في أبريل تظهر كفجوة في كلا الشهرين.

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

مثال على تحليل التباين

إليك مثال تطبيقي لشركة وهمية تدعى Cedar and Pine Coffee. يقارن مسؤول المالية فيها النتائج الفعلية للربع الثالث بالميزانية.

البند النوع الميزانية الفعلي التباين نسبة التباين % الحالة
Revenue Revenue $250,000 $236,500 −$13,500 −5.4% Unfavorable
Cost of goods sold Cost $100,000 $97,200 −$2,800 −2.8% Favorable
Payroll Cost $60,000 $64,800 $4,800 8.0% Unfavorable
Marketing Cost $20,000 $26,000 $6,000 30.0% Unfavorable
Rent Cost $15,000 $15,000 $0 0.0% On plan
Operating profit $55,000 $33,500 −$21,500 −39.1% Unfavorable

الإجمالي صحيح. فقدت الإيرادات 13,500 دولار، ووفرت تكلفة البضائع المباعة 2,800 دولار، وكلفت الرواتب 4,800 دولار إضافية، وكلفت التسويق 6,000 دولار إضافية. مجموع ذلك هو −21,500 دولار، وهو ما يطابق فجوة الأرباح.

ثلاثة بنود تجتاز قاعدة الأهمية النسبية (أكثر من 5% وأكثر من 2,000 دولار): الإيرادات، والرواتب، والتسويق. تحركت تكلفة البضائع المباعة بنسبة 2.8% فقط، لذا لا تحصل على ملاحظة.

تحتاج فجوة الإيرادات إلى نظرة فاحصة. افترضت الميزانية 50,000 وحدة بسعر 5.00 دولارات. باع الربع 43,000 وحدة بسعر 5.50 دولارات.

  • تباين الحجم. (43,000 − 50,000) × $5.00 = −$35,000.
  • تباين السعر. ($5.50 − $5.00) × 43,000 = +$21,500.
  • الإجمالي. −$35,000 + $21,500 = −$13,500، وهو ما يطابق بند الإيرادات.

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

تحتاج الرواتب إلى فحص خاص بها. فجوة الـ 4,800 دولار قد تكون بسبب محرك المعدل، مثل زيادة الأجور، أو محرك الحجم، مثل ساعات عمل إضافية. يسأل مسؤول المالية مدير العمليات ويعلم أن متجرين أضافا نوبات عمل في عطلة نهاية الأسبوع. وهذا يجعلها فجوة حجم مرتبطة بساعات العمل، وليست زيادة في الأجور.

الملخص النهائي للقادة يتناسب في سطر واحد. جاء الربح أقل من الميزانية بمقدار 21,500 دولار، حيث كلفت زيادة السعر حجماً أكبر مما كسبته، وتجاوز التسويق ونوبات عطلة نهاية الأسبوع الخطة.

كيفية كتابة ملاحظات التباين

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

تتبع الملاحظة الجيدة نمطاً بسيطاً:

  • ماذا. "أنفق التسويق 6,000 دولار فوق الميزانية، بنسبة 30.0% فوق الخطة."
  • لماذا. "استمرت حملتان مدفوعتان لفترة أطول مما كان مخططاً له."
  • التالي. "أوقفنا حملة واحدة في 3 أكتوبر ونتوقع أن ينتهي الربع الرابع وفقاً للخطة."

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

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

اكتب الملاحظات مع مالك كل بند. يرى الفريق المالي الأرقام، لكن مالك الميزانية يعرف السبب. مراجعة قصيرة مع كل مالك تحول التخمين إلى تفسير.

أخيراً، أضف ملخصاً من سطر واحد في الأعلى. بالنسبة للمثال أعلاه، سيكون: "جاء الربح التشغيلي للربع الثالث أقل من الميزانية بمقدار 21,500 دولار، ويرجع ذلك أساساً إلى انخفاض الحجم وارتفاع الإنفاق التسويقي".

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

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

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

كيفية رسم التباينات بيانياً

يساعد الرسم البياني القادة على رؤية المسار من الميزانية إلى الفعلي. الأنسب هو مخطط الشلال (waterfall chart).

تقول Microsoft إن مخطط الشلال "يظهر إجمالياً تراكمياً مع إضافة القيم أو طرحها". وتضيف أن "مخططات الشلال تسمى أيضاً مخططات الجسر (bridge charts)".

لبناء واحد، اسرد الخطوات من ربح الميزانية إلى الربح الفعلي. ابدأ بـ 55,000 دولار، ثم أضف تأثير كل بند على الربح، وانتهِ بـ 33,500 دولار. حدد البيانات واختر Insert > Insert Waterfall > Waterfall.

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

للتتبع شهراً بشهر، يعمل أيضاً مخطط أعمدة بسيط لنسبة التباين %. يغطي دليلنا حول كيفية بناء تقرير الميزانية مقابل الفعلي التقرير الشهري الكامل حول هذا الجدول.

إجراء تحليل التباين باستخدام الذكاء الاصطناعي

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

إليك الطريقة في ثلاث خطوات، باستخدام Powerdrill Bloom.

الخطوة 1: رفع الميزانية والنتائج الفعلية

قم بتصدير الميزانية والنتائج الفعلية من أداة المحاسبة أو التخطيط الخاصة بك. تقبل أداة تحليل الميزانية بالذكاء الاصطناعي "صادرات Excel أو CSV أو PDF من المحاسبة وFP&A".

قم بتضمين بيانات الوحدات والأسعار إذا كنت تريد تقسيم السعر والحجم. حافظ على أسماء الحسابات كما هي في كلا الملفين.

Uploading budget and actuals files for a variance analysis in Powerdrill Bloom

الخطوة 2: اطلب التباينات والمحركات بلغة طبيعية

صف المخرجات التي تحتاجها. على سبيل المثال: "قارن النتائج الفعلية للربع الثالث بالميزانية حسب البند. ميز البنود التي تتجاوز 5% و2,000 دولار، وقسم فجوة الإيرادات إلى سعر وحجم".

تسرد صفحة الأداة "التباين حسب البند مع تطبيق عتبات الأهمية النسبية" كأحد العروض. كما تسرد دفتر عمل لإعادة التوقعات مع "الميزانية الأصلية، والنتائج الفعلية منذ بداية العام، ونهاية العام المنقحة".

الخطوة 3: تحقق من الأرقام وصدر التقرير

قارن الإجماليات بورقتك الخاصة قبل مشاركة أي شيء. ثم قم بتصدير النتيجة. تسرد صفحة تحليل الميزانية "لوحة معلومات التباين مع تمييز البنود الجوهرية وإرفاق المحركات".

للحصول على عرض مستمر، تغطي أداة بناء لوحة المعلومات المالية بالذكاء الاصطناعي الأرباح والخسائر، والنقد، ورأس المال العامل، والتوقعات. جرب Powerdrill Bloom مع ميزانية ونتائج الربع الماضي الفعلية.

Checking variance totals before exporting the report

أخطاء شائعة يجب تجنبها

تظهر بعض الأخطاء في كل تحليل تباين أول تقريباً.

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

الأسئلة الشائعة

ما هي صيغة التباين في Excel؟

لتباين الميزانية، استخدم الفعلي ناقص الميزانية، مثل =C2-B2. بالنسبة للنسبة المئوية، استخدم =IFERROR((C2-B2)/ABS(B2),"") وقم بتنسيق الخلية كنسبة مئوية.

كيف أعرف ما إذا كان التباين ملائماً؟

يعتمد ذلك على نوع البند. بالنسبة للإيرادات، الفعلي فوق الميزانية ملائم. بالنسبة للتكاليف، الفعلي تحت الميزانية ملائم. يمكن لعمود النوع وصيغة IF تسمية كل صف.

ما هو التباين الجوهري؟

هو فجوة كبيرة بما يكفي لتستحق التفسير. تضع الفرق قواعدها الخاصة، مثل أكثر من 5% وأكثر من 2,000 دولار. البنود التي تجتاز كلا الاختبارين تحصل على ملاحظة مكتوبة.

ما هو أفضل مخطط لتحليل التباين؟

مخطط الشلال، المسمى أيضاً مخطط الجسر، يوضح كيف يحرك كل بند الإجمالي من الميزانية إلى الفعلي. يوفره Excel تحت Insert > Insert Waterfall.

هل يمكن لـ Copilot في Excel إجراء تحليل تباين؟

تسرد Microsoft مهارة تحليل التباين لـ Copilot في Excel. فهي تقارن النتائج الفعلية بالميزانيات أو التوقعات أو الفترات السابقة وتصيغ تفسيرات للمراجعة. تتطلب ترخيص Copilot.

المصادر: دعم Microsoft، استخدام المهارات المدمجة مع Copilot في Excel · دعم Microsoft، إنشاء مخطط شلال · دعم Microsoft، استخدام التنسيق الشرطي لتمييز المعلومات في Excel.