Skip to main content

عملکرد روابط ریاضی در ایجاد یک فرمول برای نمایش درصد تغییرات

Creating a percentage-change formula

هر کاربر اکسل باید قبل از شروع نوشتن فرمول باید این درس را مشاهده کند تا با برخی از متدال ترین اشکالات در فرمول نویسی اکسل آشنایی کامل پیدا کنند. نوشتن علائم ریاضی و الویت بندی آنها و رعایت برخی اصول پیش پا افتاده موجب میشوند که ما یک فرمول صحیح بنویسیم که همه این موضوعات در این درس مورد پوشش قرار میگیرند.

لینک کمکی (official link) – تأثیر علائم ریاضی در الویت بندی و محاسبه صحیح فرمولهای اکسل

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

در این ورک شیت که اسم آن YTD and Percent Increase است، ما نیاز داریم درصد افزایش سود خود را از هر ماه تا ماه دیگر یا احتمالاً مخارج کلی یا overhead و یا احتمالاً فروش یا sales را حساب کنیم. به نظر شما واقعاً درست است که اینجا کلمه increase را نوشتیم؟ شاید درست نباشد، پس اجازه بدهید آن را ویرایش کنم. بهتر است کلمه increase را به change تغییر بدهم تا مفهوم از افزایش سود به تغییرات سود تغییر کند. فکر کنم اینطور بهتر باشد. روی سلول A6 دابل کلیک میکنم. حالا روی کلمه increase دابل کلیک میکنم و کلمه change را تایپ میکنم. پس با دو بار دابل کلیک توانستم به راحتی کلمه Change را اینجا قرار بدهم. ممکن است من آدم خوشبینی باشم، به همین دلیل بود که در سلول کلمه increase را گذاشته بودم و همیشه افزایش سود را میدیدم شاید من کمتر سود کنم، اما، از طرفی، میخواهیم سود ما افزایش پیدا کند نه مخارج ما.
با این حال ما ورودی های خود را بدست آوردیم، درصد تغییر برای ماه ژانویه چون اولین ماه است و از قبل عددی نداریم که با آن مقایسه کنیم، بی معنی است. ولی برای ماه فوریه در سلول C6، نیاز به محاسبه درصد تغییر آن داریم. در حال حاضر، ممکن است مدت زمان زیادی گذشته باشد که شما از این جور کارها انجام نداده باشید. احتمالاً این چیزی است که شما طی دوران دبیرستان انجام میدادید. خب؛ حالا چطور ما اینجا درصد را محاسبه کنیم؟ یعنی وقتی سود از 20 به 30 افزایش پیدا کرده، چند درصد تغییر کرده است. روشی که ما انجام میدهیم این است که اول بیاییم اختلاف مقادیر دو تا سلول از ماههای متمادی سطر profits را حساب کنیم. بعد حاصل را بر مقدار profits یا سود ماه اولی تقسیم کنیم، بنابراین اجازه بدهید نوشتن این فرمول را شروع کنیم.

اشتباهات رایج فرمول نویسی در اکسل

این فرمولی که میخواهیم بنویسیم، چیزی است که میخواهد یکی از خطاهای شایع در اکسل را آشکار کند. و به راحتی میشود به آن غلبه کرد، اما از طرفی، آن چیزی است که اگر دقت نکنید میتواند شما را اذیت کند. فرض کنیم که شما با یک ماشین حساب استاندارد میخواهید اینکار را انجام بدهید یعنی با اعداد حقیقی سر و کار دارید. پس یکبار می‌آیید 30 را از 20 کم میکنید و در نهایت عدد 10 بدست آمده را بر 20 تقسیم میکنید و همانطور که میدانید نمیشود اینطور زد که 30 منهای 20 و همانجا اسلش به معنی دکمه تقسیم و بعد عدد 10. در هر صورت شما میخواهید جواب صحیح را بدست بیاورید.
اما در اکسل چه اتفاقی می‌افتد؟ برای شروع فرمول نویسی همانطور که قبلاً هم گفتم باید اول علامت = تایپ کنیم، پس من یک = میگذارم، روش محاسبه به این صورت که تفاوت این دو را حساب کنیم. پس بعد از مساوی روی سلول C4 کلیک میکنم علامت منها را تایپ میکنم و حالا روی B4 کلیک میکنم. ما میدانیم که جواب این 10 میشود، الان باید بیایم آنرا تقسیم کنیم. پس علامت اسلش را تایپ میکنم. ما میخواهیم اینرا بر مقدار شروع یعنی مقدار ماه اولی تقسیم کنیم، که اینجا مقدار شروع 20 بوده، پس روی B4 کلیک میکنم. انتظار داریم جواب ما 0.5 شود که همان مقدار 50 درصد است، اما وقتی روی دکمه اینتر صفحه کلید، کلیک کنم شاید کمی شوکه شویم.
جواب 29 شد، یعنی چه اتفاقی افتاد؟

استفاده از علائم برای الویت بخشی به محاسبات ریاضی فرمولهای اکسل

در حال حاضر، چیزی که ممکن است در صفحه ورکشیت اکسل شما وجود نداشته باشد همین علامتهایی است که من در سلولهای A8 تا A11 دارم. بعضی از این علامتها، البته نه همه آنها، اما خیلی از آنها برای نوشتن فرمول در اکسل استفاده میشوند. ترتیب اینها از بالا به پایین، اولویت آنها را در عملیات ریاضی در نرم افزار اکسل نشان میدهد. بعنوان مثال همانطور که میبینید عملیات ضرب اولویت دارد به عملیات جمع پس هر موقع اکسل یک فرمول میبیند که هم ضرب در آن است هم جمع، اول ضرب را انجام میدهد و حاصل را با مقادیر دیگر جمع میزند. اجازه بدهید به این فرمول برگردیم، وقتی اکسل یک فرمول را میبینید، اول آن عملیاتی را انجام میدهد که اولویت بالاتری دارد، پس یک لحظه بیایید به علامتهایی که از ستون A8 شروع میشوند نگاه کنیم. خب؛ با توجه به این علامتها، تقسیم اولویت بالاتری نسبت به + دارد، پس اکسل اول عمل تقسیم را انجام میدهد.
B4 تقسیم بر B4 برابر با یک میشود. مقدار C4 چند است؟ خب؛ آن سی است. پس میشود 30 منها یک، که برابر با 29 میشود. شما الان دوباره به لیست علامتها نگاه کنید، خب؛ بالاترین اولویت مربوط به پرانتز است. پس من دوباره به فرمول بر میگردم، روی سلول C6 دابل کلیک میکنم، از سمت چپ فرمول، بعد از علامت مساوی علامت پرانتز باز میگذارم، و بعد از B4ای که قبل از اسلش است، هم یک علامت پرانتز بسته میگذارم. الان فکر میکنید بعد از اینکه ما دکمه اینتر را فشار بدهیم، وقتی اکسل این فرمول را ببیند، چه کاری انجام میدهد؟ دوباره به لیست پایین نگاه میکنم، اولین چیزی که من میبینم پرانتز است.
پس اولین کاری که انجام میدهد، عملیات داخل پرانتز را حساب میکند. که مقدار آن 10 میشود و بعد آنرا بر مقدار B4 که بیست است تقسیم میکند. و ما الان انتظار داریم جواب فرمول ما برابر با 0.5 شود. پس دکمه اینتر را میزنیم. و الان جواب مورد انتظار ما را میتوانیم بینیم.

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

احتمالاً خیلی از ما دوست داریم که نحوه نمایش این سطر ما که بیانگر درصد تغییرات سود است را به صورت درصدی مشاهده کنیم پس خیلی راحت از منو Home وقتی که این سلول فعال است بر روی دکمه percent کلیک میکنیم. خب؛ ممکن است شما بخواهید عدد را با یک رقم اعشار اضافی ببینید بنابراین میتوانید از همینجا، روی دکمه increase decimal کلیک کنید.
اگر دقت کرده باشید این فرمول بر اساس سلول B4 و C4 نوشته شده. پس اگر ما این فرمول را در سلولهای کناری سمت راست کپی کنیم، فرمول این بر اساس این دو تا سلول است و فرمول این یکی بر اساس این دو تا سلول است. البته بعضی موقعها مثل اینجا جواب صفر میشود. یعنی وقتی سود از 50 به 50 میرسد در واقع درصد تغییرات ما صفر میشود و ما تغییرات دیگری را هم میتوانیم ببینیم. و شما میتوانید تصور کنید که چطور یک فرمول مثل این میتواند مقادیر مربوط به یک بازه زمانی، مثل ماه به ماه و یا سال به سال را حساب کند.

نکات کاربردی در نگارش علائم ریاضی فرمولهای اکسل

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

خطایابی فرمولها توسط اکسل

دوباره برگردید اینجا، دکمه‌های Control-Z را فشار میدهم تا به حالت قبل برگردم. باز هم میگویم اشکالی ندارد آن پرانتزها باشند ولی بودن آن ضرورتی ندارد. اما وقتی شما یکی از این پرانتزها را فراموش میکنید گاهی اوقات اکسل به شما یک پیغام خطا میدهد گاهی اوقات هم نه. در واقع حواس شما باشد باید تعداد پرانتزهای باز با پرانتزهای بسته مطابقت داشته باشد. پس من اینجا یک پرانتز باز میگذارم و فرض میکنم فراموش کردم پرانتز بسته را بگذارم، پس دکمه اینتر را میزنم. خب؛ میبینید که یک پنجره اخطاری باز میشود و به ما میگوید که پرانتزها با هم مطابقت ندارند. یعنی تعداد پرانتزهای باز و بسته با هم برابر نیست.
دکمه اینتر صفحه کلید را فشار میدهم. خود اکسل می‌آید و میگوید ” یک اشتباه تایپی وجود دارد”. در این مثال، اکسل برای اصلاح اشتباه ما یک پیشنهاد میدهد، هر چند خیلی به درد ما نمیخورد اجازه بدهید روی yes کلیک کنم ببینیم چه اتفاقی می‌افتد. اگر روی سلول c6 دابل کلیک کنیم میبینیم که قسمت آخر فرمول کنار B4 پرانتز بسته گذاشته شده. در هر صورت فرمول دارد درست کار میکند. در تجربه کاری من با فرمولها، وقتی اشتباهی مثل این را انجام میدهیم یعنی ممکن است یک پرانتز بسته اضافی گذاشته باشیم، در 90 درصد مواقع پیشنهادی که اکسل برای تصحیح آن میدهد درست است. خب؛ الان من دکمه اینتر صفحه کلید را فشار میدهم، دوباره پنجره اخطار باز میشود، اجازه بدهید روی yes کلیک کنم، اگر روی سلول دابل کلیک کنم، میبینیم که پرانتز اضافی را حذف کرده. پس در این مورد اکسل درست عمل کرده. در حال حاضر، هر چند ما قصد نداریم تو این درس جزئیات فرمول را پوشش بدهیم، ولی یک وضعیت خاصی اینجا وجود داشت که خیلی هم ساده بود، ولی از این به بعد وقتی پیغام هشدار را میبینید بدانید که فقط شما خودتان باید در مورد فرمولها بررسی لازم را انجام بدهید و نه اینکه با کلیک بر روی دکمه yes به نرم افزار اجازه بدهید که خود آن تغییرات پیشنهادی آنرا اعمال کند، البته بعداً در این دوره این مفاهیم آموزشی پوشش داده میشوند.

ورود اطلاعات غلط موجب ساقط شدن فرمول میشوند

اگر ما بخواهیم مقدار profits ما یک مقدار منفی باشد کافی من بیام و مقدار overheard را به 260 تغییر بدهم و حالا اینتر را از صفحه کلید فشار میدهم. خب؛ الان اینجا بعضی از نتایجی را که بدست آوردیم هیچ مفهومی ندارند. به طور مثال، این عدد مقایسه درصد تغییر اینجا است. -120 درصد مفهومی برای ما ندارد قطعاً اینطور نیست که ارقام افزایش پیدا نکنند. اینکه سود شما منفی 10 باشد، بعد به 65 برسد. اجازه بدهید ببینیم که چطور این اتفاق اینجا افتاده. همینطور در بعضی موارد، شما میخواهید برای این موضوع به شما هشدار داده شود، چون این اعداد خیلی ناجور هستند. فعلاً اجازه دهید وارد جزئیات آماری آن نشویم که من میخواستم مخارج نسبت به سود چه باشد. ولی به شما بگویم اینجا میفهمیمم خطایی در وارد کردن دیتاها رخ داده. اجازه بدهید Control-Z را فشار بدهم. 
اما اگر مثل قبل باشد، ما مقدار صفر درصد را بدست می‌آوریم. که این یک مقدار درست است، و همینطور این یکی هم درست است. بنابراین من اینجا انواع فرمولهایی را مثال زدم که شما در زمانهای مختلفی در اکسل به آنها نیاز دارید. من روشی را پیشنهاد نمیکنم، هر کسی خود نیاز دارد بداند بهتر است چطور این درصدها تغییر پیدا کند، اما من فکر میکنم برای همه این کاملاً معمولی است که میتواند مورد استفاده قرار بگیرد، و این یکی از فرمولهای بیشماری است که ممکن است زمانی بخواهیم آنرا به ورک شیت اعمال کنیم. ما فقط فراموش کرده بودیم که چطور باید آنرا انجام بدهیم، اما یادتان باشد، نکته اصلی در خیلی از فرمولها نیاز به استفاده از پرانتزها است که باید آنها را به صورت مرتب و در جای مناسبی از فرمول قرار بدهیم.

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

دوره ها
درس ها
Tha PMIS
طهاکو من
0