كيفية حساب النسبة المئوية للتغيير في Excel [صيغة]

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

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

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

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

حساب النسبة المئوية للتغيير في Excel

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

توجد صيغتان مختلفتان يمكنك استخدامهما لحساب النسبة المئوية للتغيير. تركيبها كما يلي:

= (new_value - original_value) / original_value

أو

= (القيمة الجديدة / القيمة الأصلية) - 1

• قيمة جديدة - هي القيمة الحالية / النهائية للرقمين اللذين تقارنانهما.
• القيمة السابقه - هي القيمة الأولية للرقمين اللذين تقارنانهما لحساب النسبة المئوية للتغيير.

مثال:

دعونا نلقي نظرة على قائمة طلبات الفاكهة الافتراضية مع العمود B الذي يحتوي على عدد الفاكهة التي تم طلبها في الشهر الماضي ، ويحتوي العمود C على عدد الفاكهة التي تم طلبها هذا الشهر:

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

= (C2-B2) / B2

سيتم إدخال الصيغة أعلاه في الخلية D2 للعثور على النسبة المئوية للتغيير بين C2 (new_value) و B2 (original_value). بعد كتابة الصيغة في خلية ، اضغط على Enter لتنفيذ الصيغة. تحصل على النسبة المئوية للتغيير في القيم العشرية كما هو موضح أدناه.

لم يتم تنسيق النتيجة كنسبة مئوية ، حتى الآن. لتطبيق تنسيق النسبة المئوية على خلية (أو نطاق من الخلايا) ، حدد الخلية (الخلايا) ، ثم انقر فوق الزر "نمط النسبة المئوية" في مجموعة الأرقام بعلامة التبويب "الصفحة الرئيسية".

سيتم تحويل القيمة العشرية إلى نسبة مئوية.

احسب النسبة المئوية للتغيير بين عمودين من الأرقام

الآن ، نحن نعرف النسبة المئوية للتغير بين طلبين لـ Apple ، فلنجد النسبة المئوية للتغيير لجميع العناصر.

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

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

الآن حصلت على قيم النسبة المئوية للتغيير لعمودين.

عندما تكون القيمة الجديدة أعلى من القيمة الأصلية ، ستكون النسبة المئوية للنتيجة موجبة. إذا كانت القيمة الجديدة أقل من القيمة الأصلية ، فستكون النتيجة سالبة.

كما ترى ، تمت زيادة النسبة المئوية للتغيير في "التفاح" بنسبة 50٪ ، وبالتالي فإن القيمة موجبة. انخفضت نسبة الأفوكودو بنسبة 14٪ ومن ثم جاءت النتيجة سلبية (-14٪).

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

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

في نافذة تنسيق الخلايا التي تظهر ، انقر على "مخصص" أسفل القائمة اليسرى واكتب الرمز أدناه في مربع النص "النوع:":

0.00٪ ؛ [أحمر] -0.00٪

ثم انقر فوق الزر "موافق" لتطبيق التنسيق.

الآن ، سيتم تمييز النتائج السلبية باللون الأحمر. يؤدي هذا التنسيق أيضًا إلى زيادة عدد المنازل العشرية لإظهار النسب المئوية الدقيقة.

يمكنك أيضًا استخدام صيغة أخرى لحساب النسبة المئوية للتغيير بين قيمتين (ترتيب الشهر الماضي وترتيب هذا الشهر):

= (C2 / B2) -1

تقسم هذه الصيغة new_value (C2) على القيمة الأصلية (B2) وتطرح "1" من النتيجة للعثور على النسبة المئوية للتغيير.

حساب النسبة المئوية للتغيير بمرور الوقت

يمكنك حساب النسبة المئوية للتغيير من فترة إلى أخرى (التغيير من شهر إلى آخر) لفهم معدل النمو أو الانخفاض من فترة إلى أخرى (لكل شهر). هذا مفيد للغاية عندما تريد معرفة النسبة المئوية للتغيير لكل شهر أو سنة خلال فترة زمنية محددة.

في المثال أدناه ، لدينا سعر الفاكهة لشهر مارس في العمود B والسعر لشهر يوليو ، بعد 5 أشهر.

استخدم هذه الصيغة العامة للعثور على النسبة المئوية للتغيير بمرور الوقت:

= ((Current_value / Original_value) / Original_value) * N

هنا ، تشير N إلى عدد الفترات (السنوات ، الأشهر ، إلخ) بين قيمتي الأولي والحالي.

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

= ((C2-B2) / B2) / 5

تشبه هذه الصيغة الصيغة التي استخدمناها من قبل ، باستثناء أننا نحتاج إلى قسمة قيمة النسبة المئوية للتغيير على عدد الأشهر بين شهرين (5).

حساب النسبة المئوية للنمو / التغيير بين الصفوف

لنفترض أن لديك عمودًا واحدًا من الأرقام يسرد السعر الشهري للبنزين لأكثر من عام.

الآن ، إذا كنت ترغب في حساب النسبة المئوية للنمو أو الانخفاض بين الصفوف حتى تتمكن من فهم التغييرات الشهرية في الأسعار ، فجرّب الصيغة التالية:

= (B3-B2) / B2

نظرًا لأننا نحتاج إلى إيجاد النسبة المئوية للنمو لشهر فبراير (B3) من سعر يناير (B2) ، فإننا نحتاج إلى ترك الصف الأول فارغًا لأننا لا نقارن شهر يناير بالشهر السابق. أدخل الصيغة في الخلية C3 واضغط على Enter.

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

يمكنك أيضًا حساب النسبة المئوية للتغيير لكل شهر مقارنة بشهر يناير (B2). للقيام بذلك ، تحتاج إلى جعل هذه الخلية مرجعًا مطلقًا عن طريق إضافة علامة $ إلى مرجع الخلية ، على سبيل المثال 2 دولار كندي. لذلك ستبدو الصيغة كما يلي:

= (B3- $ B $ 2) / $ B $ 2

تمامًا كما كان من قبل ، نتخطى الخلية الأولى وأدخلنا الصيغة في الخلية C3. عندما تنسخ الصيغة إلى باقي الخلايا ، فإن المرجع المطلق ($ B $ 2) لا يتغير ، بينما المرجع النسبي (B3) سيتغير إلى B4 ، B5 ، وهكذا.

ستحصل على نسبة تضخم أو انكماش عن كل شهر مقارنة بشهر يناير.

حساب النسبة المئوية للزيادة في Excel

يشبه حساب زيادة النسبة المئوية حساب النسبة المئوية للتغيير في Excel. النسبة المئوية للزيادة هي معدل الزيادة إلى القيمة الأولية. ستحتاج إلى رقمين لحساب النسبة المئوية للزيادة ، والرقم الأصلي ورقم New_number.

بناء الجملة:

زيادة النسبة المئوية = (New_number - Original_number) / Original_number

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

على سبيل المثال ، لديك قائمة بالفواتير ومبالغها لشهرين (أبريل ومايو). إذا زادت فواتير شهر أبريل في شهر مايو ، فما هي النسبة المئوية للزيادة من أبريل إلى مايو؟ يمكنك استخدام الصيغة أدناه لمعرفة:

= (C2-B2) / B2

هنا ، نطرح فاتورة الكهرباء لشهر مايو (C2) من فاتورة شهر أبريل (B2) ونقسم النتيجة على فاتورة شهر أبريل. ثم انسخ الصيغة إلى خلايا أخرى باستخدام مقبض التعبئة.

إذا حصلت على نتيجة إيجابية ($ 24.00٪) ، زادت النسبة بين الشهرين ، وإذا حصلت على نتيجة سلبية (على سبيل المثال - $ 13.33٪) ، فإن النسبة المئوية تنخفض بالفعل بدلاً من زيادتها.

حساب نسبة الانخفاض في Excel

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

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

بناء الجملة:

النسبة المئوية النقصان = (Original_number - New_number) / Original_number

لنفترض أن لديك هذه القائمة من أجهزة التخزين وأسعارها لمدة عامين (2018 و 2020).

على سبيل المثال ، كانت أسعار أجهزة التخزين أعلى في عام 2018 ، وانخفضت الأسعار في عام 2020. ما هي النسبة المئوية لانخفاض الأسعار لعام 2020 مقارنة بعام 2018؟ يمكنك استخدام هذه الصيغة لمعرفة:

= (B2-C2) / B2

هنا ، نطرح سعر 2018 (B2) من سعر 2020 (C2) ونقسم المجموع على سعر 2018. بعد ذلك ، نقوم بنسخ الصيغة إلى خلايا أخرى لإيجاد النسبة المئوية للانخفاض للأجهزة الأخرى.

إذا حصلت على نتيجة إيجابية ($ 20.00٪) ، فإن النسبة تنخفض بين السنتين ، وإذا حصلت على نتيجة سلبية (على سبيل المثال - $ 13.33٪) ، فستزداد النسبة بالفعل بدلاً من تقليلها.

الأخطاء الشائعة التي تحصل عليها عند استخدام الصيغ أعلاه

عند استخدام الصيغ أعلاه ، قد تصادف أحيانًا قائمة الأخطاء الشائعة هذه:

• # DIV / 0!: يحدث هذا الخطأ عند محاولة قسمة رقم على صفر (0) أو على خلية فارغة. على سبيل المثال القيمة B6 في الصيغة "= (B6-C6) / B6" تساوي الصفر ، ومن هنا جاءت قيمة # DIV / 0! خطأ.

عند إدخال "صفر" بتنسيق العملة ، سيتم استبداله بشرطة (-) كما هو موضح أعلاه.

• #القيمة: يُظهر Excel هذا الخطأ عند إدخال قيمة ليست من النوع المعتمد أو عندما تُترك الخلايا فارغة. يمكن أن يحدث هذا غالبًا عندما تحتوي قيمة الإدخال على مسافات أو أحرف أو نص بدلاً من أرقام.

• NUM!: يحدث هذا الخطأ عندما تحتوي الصيغة على رقم غير صالح ينتج عنه رقم كبير جدًا أو صغير جدًا بحيث لا يمكن عرضه في Excel.

تحويل خطأ إلى صفر

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

بناء جملة الدالة IFERROR:

= IFERROR (القيمة ، value_if_error)

أين،

• القيمة هي القيمة أو المرجع أو الصيغة المطلوب التحقق منها.
• القيمة_إذا_خطأ هي القيمة التي نريد عرضها إذا كانت الصيغة تُرجع قيمة خطأ.

دعنا نطبق هذه الصيغة في أحد أمثلة الخطأ (# DIV / 0! خطأ):

= IFERROR ((B6-C6) / B6،0٪)

في هذه الصيغة ، تكون الوسيطة "value" هي صيغة النسبة المئوية للتغيير ووسيطة "value_if_error" هي "0٪". عندما تواجه صيغة النسبة المئوية للتغير خطأ (خطأ # DIV / 0!) ، ستعرض الدالة IFERROR "0٪" كما هو موضح أدناه.

هذا كل ما تحتاج لمعرفته حول حساب النسبة المئوية للتغيير في Excel.