محاسبه درصد تغییر (افزایش / کاهش درصد) در اکسل

توسط Svetlana Cheusheva ، در 7 فوریه 2023 به روز شد

این آموزش نشان می دهد که چگونه می توان فرمول اکسل را برای درصد افزایش یا کاهش و از آن با هر دو عدد مثبت و منفی استفاده کرد.

در مایکروسافت اکسل ، 6 عملکرد مختلف برای محاسبه واریانس وجود دارد. با این حال ، هیچ یک از آنها برای محاسبه درصد اختلاف بین دو سلول مناسب نیست. توابع داخلی برای یافتن واریانس به معنای کلاسیک طراحی شده اند ، یعنی مجموعه ای از مقادیر تا چه اندازه از میانگین آنها پخش می شود. واریانس درصد چیزی متفاوت است. در این مقاله فرمول مناسبی برای محاسبه درصد تغییر در اکسل پیدا خواهید کرد.

تغییر درصد چیست؟

، واریانس درصد یا اختلاف ، یک تغییر متناسب بین دو مقدار ، یک مقدار اصلی و یک مقدار جدید است.

محاسبه می کند که چقدر بین دو دوره از نظر درصد تغییر می کند. به عنوان مثال ، شما می توانید واریانس بین فروش را در سال جاری و سال گذشته ، بین یک پیش بینی و دمای مشاهده شده ، بین هزینه بودجه و واقعی محاسبه کنید.

به عنوان مثال ، در ماه ژانویه شما 1000 دلار درآمد کسب کرده اید و در فوریه 1200 دلار ، بنابراین تفاوت 200 دلار افزایش درآمد است. اما از نظر درصد چقدر است؟برای پیدا کردن این موضوع ، شما از یک فرمول درصدی تغییر استفاده می کنید.

فرمول تغییر درصد اکسل

دو فرمول اساسی برای یافتن درصد اختلاف بین دو عدد وجود دارد.

فرمول واریانس درصد کلاسیک

در اینجا فرمول به طور کلی استفاده شده برای محاسبه درصد تغییر است:

(new_value - old_value) / old_value

در ریاضی ، شما معمولاً 3 مرحله برای محاسبه واریانس درصد بین هر دو مقدار عددی انجام می دهید:

1. مقدار جدید را از زمان قدیمی کم کنید.
2. تفاوت را با شماره قدیمی تقسیم کنید.
3. نتیجه را با 100 ضرب کنید.

در اکسل ، شما با استفاده از قالب درصد ، آخرین مرحله را رد می کنید.

فرمول تغییر درصد اکسل

و در اینجا یک فرمول ساده تر برای تغییر درصد در اکسل وجود دارد که همان نتیجه را برمی گرداند.

new_value / old_value - 1

نحوه محاسبه درصد تغییر در اکسل

برای یافتن درصد اختلاف بین دو عدد در اکسل ، می توانید از هر یک از فرمول های فوق استفاده کنید. بیایید بگوییم که شما فروش تخمین زده شده در ستون B و فروش واقعی در ستون C را دارید

فرمول های فوق اعداد را در ردیف 3 مقایسه می کنند. برای محاسبه درصد تغییر در کل ستون ، این همان کاری است که شما باید انجام دهید:

1. فرمول اختلاف درصد را در هر سلول خالی در ردیف 3 وارد کنید ، در D3 یا E3 بگویید.
2. با انتخاب سلول فرمول ، روی دکمه درصد سبک روی روبان کلیک کنید یا میانبر Ctrl + Shift + ٪ را فشار دهید. این تعداد اعشاری برگشتی را به درصد تبدیل می کند.
3. فرمول را به همان اندازه ردیف های مورد نیاز بکشید.

پس از کپی کردن فرمول ، یک ستون تغییر درصدی از داده های خود دریافت خواهید کرد.

چگونه فرمول تغییر درصد اکسل کار می کند

هنگام انجام محاسبات به صورت دستی ، یک مقدار قدیمی (اصلی) و یک مقدار جدید می گیرید ، تفاوت بین آنها را پیدا کرده و آن را با مقدار اصلی تقسیم می کنید. برای به دست آوردن نتیجه به عنوان درصد ، آن را 100 برابر می کنید.

به عنوان مثال ، اگر مقدار اولیه 120 باشد و مقدار جدید 150 باشد ، می توان درصد اختلاف را از این طریق محاسبه کرد:

استفاده از قالب درصد تعداد در اکسل تعداد اعشاری را به صورت خودکار به صورت خودکار نشان می دهد ، بنابراین قسمت 100 *حذف می شود.

فرمول اکسل برای افزایش /کاهش درصد

از آنجا که افزایش یا کاهش درصد فقط یک مورد خاص از واریانس درصد است ، با همان فرمول محاسبه می شود:

(new_value - اولیه_Value) / اولیه_Value new_value / rialed_value - 1

به عنوان مثال ، برای محاسبه درصد افزایش بین دو مقدار (B2 و C2) ، فرمول:

فرمولی برای محاسبه درصد کاهش دقیقاً یکسان است.

Excel درصد تغییر ارزش مطلق

به طور پیش فرض ، فرمول واریانس درصد در اکسل ارزش مثبت را برای افزایش درصد و ارزش منفی برای کاهش درصد باز می گرداند. برای به دست آوردن درصد تغییر به عنوان یک مقدار مطلق بدون توجه به علامت آن ، فرمول را در عملکرد ABS مانند این بسته بندی کنید:

abs ((new_value - old_value) / old_value)

در مورد ما ، فرمول این فرم را می گیرد:

این یکی نیز خوب خواهد بود:

درصد تخفیف را محاسبه کنید

این مثال یک استفاده عملی دیگر از فرمول تغییر درصد اکسل را نشان می دهد - درصد تخفیف. بنابراین ، خانمها ، وقتی به خرید می روید ، این را به یاد داشته باشید:

تخفیف ٪ = (قیمت تخفیف - قیمت منظم) / قیمت معمولی

تخفیف ٪ = قیمت تخفیف / قیمت معمولی - 1

درصد تخفیف به عنوان یک ارزش منفی نمایش داده می شود زیرا قیمت تخفیف جدید از قیمت اولیه کوچکتر است. برای خروج نتیجه به عنوان یک عدد مثبت ، فرمول های لانه در داخل عملکرد ABS مانند مثال قبلی:

مقدار پس از تغییر درصد را محاسبه کنید

برای به دست آوردن ارزش پس از افزایش یا کاهش درصد ، فرمول عمومی:

اولیه_و *(1+ درصد_چان)

فرض کنید مقادیر اصلی را در ستون B و درصد اختلاف در ستون C. برای محاسبه مقدار جدید پس از تغییر درصد ، فرمول موجود در D2 کپی شده است:

ابتدا درصد کلی را که باید با مقدار اصلی ضرب شود ، می یابید. برای این کار ، فقط درصد را به 1 (1+C2) اضافه کنید. و سپس ، شما درصد کلی را با شماره های اصلی ضرب می کنید تا نتیجه مطلوب را بدست آورید.

همانطور که مشاهده می کنید ، این راه حل به خوبی برای افزایش درصد و کاهش کار می کند:

برای افزایش یا کاهش کل ستون با درصد مشخصی ، می توانید مقدار درصد را مستقیماً در یک فرمول تهیه کنید. بگویید ، برای افزایش تمام مقادیر موجود در ستون B 5 ٪ ، فرمول زیر را در C2 وارد کنید و سپس آن را در ردیف های باقیمانده بکشید:

در اینجا ، شما به سادگی مقدار اصلی را 105 ٪ ضرب می کنید ، که مقداری 5 ٪ بیشتر است.

برای راحتی ، می توانید مقدار درصد را در یک سلول از پیش تعریف شده (F2) وارد کرده و به آن سلول مراجعه کنید. این ترفند در حال قفل کردن مرجع سلول با علامت $ است ، بنابراین فرمول به درستی کپی می کند:

مزیت این رویکرد این است که برای افزایش یک ستون با درصد دیگر ، فقط باید مقدار را در یک سلول واحد تغییر دهید. از آنجا که تمام فرمول ها به آن سلول مرتبط هستند ، آنها به صورت خودکار محاسبه می شوند.

محاسبه درصد واریانس با مقادیر منفی

اگر برخی از مقادیر شما توسط اعداد منفی نشان داده شود ، فرمول اختلاف درصد سنتی نادرست کار خواهد کرد. یک راه حل معمولاً پذیرفته شده ، تبدیل شدن به مخرج با کمک عملکرد ABS است.

در اینجا یک فرمول اکسل عمومی برای تغییر درصد با شماره های منفی آورده شده است:

(new_value - old_value) / abs (old_value)

با ارزش قدیمی در B2 و مقدار جدید در C2 ، فرمول واقعی به شرح زیر است:

توجه داشته باشید. اگرچه این تنظیم ABS از نظر فنی صحیح است ، در صورتی که مقدار اصلی منفی و ارزش جدید مثبت باشد ، فرمول ممکن است نتایج گمراه کننده ای ایجاد کند.

Excel درصد تغییر تقسیم بر خطای صفر (#div/0)

اگر مجموعه داده های شما حاوی مقادیر صفر است ، احتمالاً هنگام محاسبه درصد تغییر در اکسل ، با خطای صفر (#div/0!) به یک تقسیم تقسیم می شوید زیرا نمی توانید یک عدد را به صفر در ریاضی تقسیم کنید. عملکرد IFError می تواند به غلبه بر این مشکل کمک کند. بسته به انتظارات خود برای نتیجه نهایی ، از یکی از راه حل های زیر استفاده کنید.

راه حل 1: اگر مقدار قدیمی صفر است ، 0 را برگردانید

اگر مقدار قدیمی صفر باشد ، درصد تغییر 0 ٪ صرف نظر از اینکه مقدار جدید صفر باشد یا خیر ، 0 ٪ خواهد بود.

راه حل 2: اگر مقدار قدیمی صفر است ، 100 ٪ بازگردید

این راه حل رویکرد دیگری را با فرض اینکه مقدار جدید 100 ٪ از صفر رشد کرده است ، پیاده سازی می کند:

در این حالت ، اگر مقدار قدیمی صفر باشد (ردیف 5) یا هر دو مقدار صفر (ردیف 9) 100 ٪ اختلاف 100 ٪ باشد.

با نگاهی به سوابق برجسته زیر ، آشکار می شود که هیچ یک از فرمول ها کامل نیستند:

برای به دست آوردن نتایج بهتر ، می توانید دو فرمول را با استفاده از بیانیه تو در تو در لانه های تو در تو ترکیب کنید:

= if (c2<>0 ، IFError ((C2-B2)/B2 ، 1) ، IFError ((C2-B2)/B2 ، 0))

این فرمول بهبود یافته باز خواهد گشت:

• اگر مقادیر قدیمی و جدید صفر باشد ، درصد تغییر می کند.
• اگر مقدار قدیمی صفر باشد و مقدار جدید صفر نباشد ، 100 ٪ تغییر می کند.

این نحوه محاسبه درصد افزایش یا کاهش اکسل است. برای تجربه دستی ، شما می توانید کتاب کار نمونه ما را در زیر بارگیری کنید. من از شما برای خواندن تشکر می کنم و امیدوارم که هفته آینده شما را در وبلاگ ما ببینید!

کتاب کار را برای بارگیری تمرین کنید

شما همچنین ممکن است به آن علاقه مند باشید

• نحوه محاسبه درصد در اکسل
• نحوه نمایش درصد در اکسل
• فرمول علاقه مرکب اکسل