فرمول نرخ رشد در اکسل – آموزش محاسبه به زبان ساده
رشد به معنی افزایش یک کمیت در نظر گرفته میشود. انتظار داریم که بسیاری از پدیدهها، با گذشت زمان تغییر کرده و افزایش داشته باشند. البته در مواقعی نیز علاقمند به رشد منفی هستیم، برای مثال انتظار داریم با مصرف یک داروی ضد سرطان، اندازه غده کاهش داشته، و نرخ رشد منفی را تجربه کنیم. مقادیری مثل سن افراد، از مثالهایی است که رشد آن وابسته به زمان بوده و به صورت یکنواخت صورت میگیرد. همچنین درآمد در ماه برای افراد با توجه به کسب تجربه در حرفهای که دارند، باید به صورت افزایشی رشد کرده ولی میزان یا نرخ رشد آن مانند سن، ثابت نخواهد بود. در این نوشتار از مجله فرادرس به فرمول نرخ رشد در اکسل خواهیم پرداخت و بوسیله رسم نمودار و جدولهای مناسب با استفاده از فرمولها، نرخ رشد را برای بعضی از مسائل محاسبه میکنیم.
از آنجایی که معمولا نرخ رشد، به صورت درصدی نمایش داده یا محاسبه میشود، بهتر است نوشتارهای دیگر مجله فرادرس با عنوان روش محاسبه درصد در اکسل — از صفر تا صد و آموزش اکسل — به زبان ساده را مطالعه کنید. همچنین خواندن مطالب نرخ تورم چیست و چگونه محاسبه میشود؟ — به زبان ساده و مفهوم نرخ رشد مرکب سالانه و روش محاسبه آن — به زبان ساده نیز خالی از لطف نیست.
فرمول نرخ رشد در اکسل
هر چند رشد به منظور نمایش یا نشان دادن افزایش یک کمیت در نظر گرفته میشود، ولی میتوان از آن برای نمایش تغییرات کاهشی یک کمیت نیز استفاده کرد. اغلب در بازار بورس و سرمایه (بازار سهام) برای مشخص کردن سهام قابل قبول برای سرمایهگذاری، نرخ رشد شرکت یا سود دهی آن در گذشته را مبنا قرار میدهند یا از سهامی که نرخ رشد منفی داشته، پرهیز میکنند.
همانطور که گفتیم، تغییرات و نرخ رشد برای مقادیر عددی و کمی قابل محاسبه است و نمیتوان برای دادههای کیفی، نرخ رشد را محاسبه کرد. البته رشد در شاخصهای کیفی، میتواند به صورت ارتقا کیفیت در نظر گرفته شود که شاید به سادگی قابل بیان به شکل اعداد نباشد. بنابراین در این متن به تغییرات و رشد مقادیر عددی (کمی) توجه خواهیم داشت.
محاسبه نرخ رشد
مشخص است که زمانی از تغییرات صحبت میکنیم که یک کمیت با گذشت زمان دچار دگرگونی شده باشد. بنابراین هنگامی نرخ رشد قابل محاسبه است که دو کمیت را با یکدیگر مقایسه کرده و تغییرات را بیان کنیم.
درک مفاهیمی مانند میزان رشد و درصد رشد به ما کمک میکند تا تصویری بهتر از محیط پیرامون خود داشته باشیم. برای مثال رشد درآمد یا سوددهی یک شرکت را محاسبه، یا درصد رشد یک سرمایهگذاری در بازار بورس را بدست آوریم.
فرض کنید شما یک وبلاگ دارید و بازدید کنندگان در مرحله اولیه یا اول ماه، 1000 نفر بودهاند. در پایان ماه، وبلاگ شما 1200 بازدید کننده داشته است. میخواهیم فرمول نرخ رشد در اکسل را به کار بگیریم. برای دانستن درصد رشد وبلاگ در آن ماه خاص، کافی است تعداد بازدیدکننده فعلی خود را از بازدیدکنندگان اولیه کسر کنید و نتیجه را بر مقدار اولیه تقسیم نمایید. به این ترتیب میزان رشد نسبت به ماه قبل مشخص میشود.
اگر این عدد را به صورت درصدی نمایش دهیم، مشخص است که تعداد بازدیدکنندگان وبلاگ شما نسبت به ماه قبل، ۲۰٪ رشد داشته است.
نکته: از آنجایی که در مخرج کسر، مقدار اولیه قرار گرفته، میزان رشد را برمبنای آن مشخص کردهایم. بنابراین میگوییم که تعداد بازدیدکنندگان نسبت به ماه قبل، ۲۰٪ رشد داشته است. از همین شیوه نیز برای محاسبه نرخ تورم نقطه به نقطه استفاده میکنند.
پس میتوان به طور کلی، فرمول میزان رشد یا نرخ رشد (Growth Rate) یا به اختصار GR را به صورت زیر در نظر گرفت. فرض کنید مقدار اولیه به صورت و مقدار بعدی با نماد مشخص شده باشد.
رابطه ۱: محاسبه نرخ رشد
در اغلب موارد، درصد رشد، مثبت است زیرا انتظار داریم که مقدار بعدی (یعنی ) بزرگتر از مقدار قبلی (یعنی ) باشد. ولی توجه داشته باشید که در صورت افول یا رشد منفی، مقدار کوچکتر از مقدار بوده و در نتیجه، نرخ رشد، منفی بدست میآید. در ادامه فرمول نرخ رشد در اکسل با رابطه ۱ را طی یک مثال بیشتر مورد بررسی قرار میدهیم.
این بار، فرض کنید میخواهیم مقدار بعدی (یعنی ) را برمبنای نرخ رشد مشخص کنیم. کافی است رابطه ۱ را تغییر داده و این بار را برحسب و محاسبه کنیم.
پس، میتوان نوشت:
رابطه ۲: محاسبه مقدار بعدی برحسب نرخ رشد
در ادامه از فرمول یا رابطههای ۱ و ۲، برای محاسبه نرخ رشد و همچنین مقدار بعدی برحسب نرخ رشد، طی یک مثال عددی، استفاده خواهیم کرد.
نرخ رشد ثابت و میزان تغییرات ثابت
فرض کنید شرکتی میخواهد به طور پیوسته و در هر سال، مبلغ ثابتی را به حقوق کارکنان اضافه کند. میخواهیم در این حالت، نرخ رشد حقوق کارکنان را در طول ۱۰ سال محاسبه کنیم. از طرفی در حالت دیگر، فرض کنید بخواهیم با نرخ رشد ثابت، حقوق افراد شرکت تغییر کند. بنابراین میخواهیم مشخص کنیم یک فرد خاص حقوق سالانهاش در طول این ۱۰ سال چه خواهد بود و مثلا در پایان سال دهم، به چه میزان، دریافتی خواهد داشت.
واضح است که در حالت اول، میزان اختلاف ثابت ولی میزان نرخ رشد، متغیر است و در حالت دوم، میزان رشد ثابت ولی میزان اختلاف حقوق در دو سال پیاپی متفاوت خواهد بود. برای هر دو حالت در ادامه متن، جدول و نمودارهای مربوطه را در محیط اکسل اجرا و ترسیم کرده و نتایج را با یکدیگر مقایسه خواهیم کرد.
میزان تغییرات ثابت
جدول 1، نشانگر میزان میزان افزایش حقوق یکی از کارکنان شرکت مورد نظر در طول ۱۰ سال است. همانطور که میبینید، میزان افزایش حقوق او هر سال با سال قبلی برابر است. بنابراین، اختلاف حقوق دریافت شده در بین دو سال متوالی، برای همه سالها، ثابت خواهد بود.
جدول ۱: میزان حقوق یک کارمند در طی ۱۰ سال در یک شرکت با افزایش ثابت سالانه
سال | حقوق | سال | حقوق |
اول | ۱۰۰ | دوم | 120 |
سوم | 140 | چهارم | 160 |
پنجم | 180 | ششم | 200 |
هفتم | 220 | هشتم | 240 |
نهم | 260 | دهم | 280 |
با توجه به این جدول، واضح است که میزان افزایش یا تغییر در حقوق سالانه، برابر با ۲۰ واحد و برای هر سال ثابت فرض شده و قرار است فرمول نرخ رشد در اکسل را با استفاده از رابطه ۱، محاسبات کنیم. حال مطابق با تصویر ۲، در اکسل نرخ رشد را برای سال دوم محاسبه کرده و نتیجه را برای سلولهای بعدی، با عمل کپی کردن، به کار میبریم.
فرمول نرخ رشد در اکسل با خط قرمز برای سال دوم نسبت به سال اول، مشخص شده است. برای محاسبه نرخ رشد در سالهای بعدی، کافی است همین فرمول را در سلولهای پایینی کپی کنید. توجه دارید که همه آدرسها به صورت مرجع نسبی در فرمول به کار رفتهاند. همانطور که میبینید، ستون نرخ رشد تا سه رقم اعشار، گرد (Round) شده است.
نکته: باز هم تاکید میکنیم که این میزان یا نرخ رشد در اینجا، مربوط به مثلا سال جاری نسبت به سال قبل است. به همین دلیل، ابتدا حقوق سال جاری را از سال قبل کم کرده، سپس بر حقوق سال قبل طبق فرمول نرخ رشد، در اکسل تقسیم کردهایم. این موضوع نشانگر آن است که حقوق سال قبل به عنوان مبنای رشد در نظر گرفته شده.
نرخ رشد ثابت
این بار فرض کنید که شرکت، هر ساله با نرخ رشد ثابتی، حقوق فرد را افزایش میدهد. سال مبنا یعنی سال اول نیز حقوق همان فرد را مانند قبل، ۱۰۰ در نظر گرفتهایم. به این ترتیب برای محاسبه میزان تغییرات یا حقوق در سالهای مختلف، از فرمول یا رابطه ۲ کمک میگیریم. به تصویر ۳ توجه کنید. توجه کنید که فرمول نرخ رشد در اکسل به صورت برعکس اعمال شده است.
به فرمولی که برای سلول C3 نوشته شده، توجه کنید. برای آنکه از تکرار مقدار رشد (که در فرمول باید ثابت باشد) از مرجع مطلق این آدرس استفاده کردهایم. علامتهای $ در ابتدا و انتهای آدرس B2، به این منظور نوشته شدهاند. توجه داشته باشید که تغییرات حقوق و مقدار حقوق، با توجه به رقم اعشار گرد شده و نمایش داده شده است.
حال نرخ رشد و میزان تغییرات را در دو حالت گفته شده با یکدیگر طی دو نمودار مقایسه میکنیم. در نمودار اول، نرخ رشد را با دو شیوه گفته شده، نمایش دادهایم. این نمودار در تصویر ۴ قابل مشاهده است.
همانطور که میبینید، با توجه به میزان ثابت برای تغییرات حقوق، در ستونهای نارنجی رنگ، درصد رشد به صورت نزولی است. در حالیکه ستونهای آبی رنگ میزان یا نرخ رشد ثابت ۲۰٪ را نشان میدهند. تغییرات ستونهای نارنجی به صورت یک تابع نمایی ولی نزولی است که بیانگر رشد نزولی است. این امر به این معنی است که با گذشت زمان، نرخ رشد کاهش مییابد ولی توجه داشته باشید که نرخ رشد منفی نیست.
در عوض تصویر ۵، حالتی را نشان میدهد که در آن میزان نرخ ثابت بوده و حقوق برای مقایسه نشان داده شده است. واضح است که اگر میزان ثابتی به حقوق اضافه شود، رشد به کندی صورت گرفته که در نمودار با ستونهای آبی مشخص شده. ولی اگر نرخ ثابت ۲۰٪ را در نظر بگیریم، افزایش یا تغییر حقوق از یک سال به سال دیگر به صورت نمایی رشد یا تغییر خواهد کرد.
به منظور دستیابی به فرمول نرخ رشد در اکسل و نحوه محاسبه آن، فایل اطلاعاتی و کارپوشه مربوط به تصاویر ۲ و ۳ و همچنین نمودارهای مربوط به تصویر ۴ و ۵ را در اختیارتان قرار دادهایم. کافی است که فایل را با قالب فشرده از اینجا دریافت کرده، پس از خارج کردن از حالت فشرده، در برنامه اکسل، بارگذاری نمایید. تمامی فرمولها و محاسبات صورت گرفته در کاربرگها و سلولهای مربوطه مشخص شده است.
توجه داشته باشید که اگر بازه زمانی بین سالها، ثابت نبوده یا مثلا در دو مقطع زمانی (مثلا سال اول و سال پنجم) از حقوق فرد خبردار بودیم برای محاسبه نرخ رشد سالانه (متوسط نرخ رشد سالانه) باید از تکنیک نرخ رشد مرکب به جای فرمول نرخ رشد در اکسل استفاده میکردیم.
خلاصه و جمعبندی
در این نوشتار از مجله فرادرس با فرمول نرخ رشد در اکسل آشنا شده و شیوه محاسبه آن را به کمک فرمولهای مربوطه فرا گرفتید. البته محاسبه میانگین نرخ رشد از مباحثی است که بخصوص در مسائل مالی شرکتها و بورس مورد توجه است. البته واضح است که ابتدا باید نرخ رشد برای هر سال بدست آمده و مثلا از میانگین هندسی برای محاسبه میانگین نرخ رشد استفاده کرد. البته در اغلب اوقات از آنجایی که به همه دادههای مالی شرکت دسترسی نداریم، میتوانیم از نرخ رشد مرکب سالانه برای مشخص کردن درصد رشد یا سوددهی شرکت استفاده کنیم.
سلام.
چرا حتما باید قدر مطلق بگیریم؟ یعنب نمیشه عدد منفی رو برد رو نمودار؟
من دارم نرخ رشد وزنی صادرات رو محاسبه میکنم که برخی اعداد منفی میشن ، از لحاظ ریاضی اشتباه که نرخ منفی رو روی نمودار نشون بدم؟
این فرمول غلطه. وقتی مشخص میشه غلطه که شما اعداد منفی هم داشته باشی. فرمول صحیح نرخ رشد رو می نویسم:
ABS((B1-A1)/A1*100)*(B1-A1)/ABS(B1-A1)
اگر درصد رو نمیخواید اون ضربدر 100 رو حذف کنید.
با سلام،
فایل موردنظر اصلاح شد.
با تشکر از همراهی شما با مجله فرادرس
با سلام
من میخواهم نرخ رشد سالانه رو محاسبه کنم. سال اول تعداد ۲۰ رو دارم. سال دوم ۳۰ ولی سال سوم عدد صفر هست. برای گرفتن ندپرخ رشد سال چهارم که به عدد ۵ رسیده است چطور باید عمل کنم؟ چون با روشی که شما گفتید در اکسل خطا می دهد.
تشکر
باید if یا iferror استفاده کنید.
با سلام
اگر ما بخواهیم تولیدات علمی یک کشور را براساس نرخ رشد در هر سال محاسبه کنیم و در این بین بعضی از سالها هیچ تولید علمی نداشته باشه چگونه محاسبه می شود؟ بعنوان مثال
سال تعداد تولیدات نرخ رشد
1390 0 #DIV/0!
1391 3 -0.666666667
1392 1 1
1393 2 -0.5
1394 1 -1
1395 0 #DIV/0!
1396 0 #DIV/0!
1397 2 -1
1398 0 #DIV/0!
با سلام نحوه محاسبته نرخ رشد در سألهاي متوالي را بفرماييد
سلام و درود،
همانطور که در متن اشاره شد، سال ها را به عنوان یک ستون از جدول محاسباتی در نظر گرفتیم. البته خود مقدار سال مهم نیست بلکه توالی آنها است که برای محاسبه نرخ رشد اهمیت دارد. اما اگر به متوسط نرخ رشد در یک دوره مثلا ۵ ساله احتیاج دارید، باید از میانگین هندسی استفاده کنید. در متن مربوط به میانگین هندسی، نحوه محاسبه به خوبی مشخص شده است. همچنین خواندن مفهوم نرخ رشد مرکب سالانه و روش محاسبه آن — به زبان ساده در این زمینه موثر است.
موفق و پیروز باشید.