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

در این آموزش راهی سریع برای محاسبه درصد در اکسل (درصدگیری) پیدا کردن فرمول درصد پایه و چند فرمول دیگر برای محاسبه درصد افزایش، درصد جمع کل و … فرا خواهید گرفت.

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

در این آموزش قصد داریم چند تکنیک را بررسی کنیم که به شما در محاسبه مؤثر درصد در اکسل کمک می‌کند و با یادگیری فرمول‌های درصد پایه از حدس و قیاس در محاسباتتان می‌کاهید.

درصد پایه

همان‌طور که احتمالاً از درس ریاضیات به یاد دارید، یک درصد کسری از ۱۰۰ است که با تقسیم صورت بر مخرج و ضرب نتیجه در ۱۰۰ محاسبه می‌شود.

فرمول درصد پایه به شرح زیر است:

(کل / بخش) * 100 = درصد

به عنوان مثال، اگر شما ۲۰ عدد سیب داشتە باشید و ۵ عدد را به دوستان خود بدهید، چقدر هدیه داده‌اید، با درصد؟ با انجام یک محاسبه ساده = ۵/۲۰ * ۱۰۰ جواب را می‌گیرید – ۲۵٪.

به طور معمول این‌گونه درصد را در مدرسه و زندگی روزمره محاسبه می‌کنید. محاسبه درصد در اکسل حتی راحت‌تر است زیرا اکسل در پس‌زمینه برخی از عملیات را برای شما به صورت خودکار انجام می‌دهد.

متأسفانه، هیچ فرمول عمومی در اکسل برای درصد وجود ندارد که بتواند تمام سناریوهای ممکن را پوشش دهد. اگر از کسی بپرسید “از کدام فرمول درصدی استفاده کنم تا نتیجه‌ای را که می‌خواهم بدست آورم؟”، به احتمال زیاد پاسخ خواهد داد “خوب، به این بستگی دارد که دقیقاً به چه نتیجه‌ای می‌خواهید برسید.”

بنابراین، بگذارید چند فرمول ساده برای محاسبه درصد در اکسل مانند فرمول افزایش درصدی، فرمول درصد از جمع کل و … به شما نشان دهیم.

فرمول درصد پایه در اکسل

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

کل / بخش = درصد

اگر آن را با فرمول ریاضی پایه برای درصد مقایسه کنید، متوجه خواهید شد که فرمول درصد اکسل فاقد قسمت * ۱۰۰ است. هنگام محاسبه درصدی در اکسل، لازم نیست کسر حاصل را در ۱۰۰ ضرب کنید زیرا اکسل به طور خودکار این کار را انجام می‌دهد اگر فرمت درصد در سلول اعمال شده باشد.

حال بیایید ببینید که چگونه می‌توانید از فرمول درصد اکسل در داده‌های واقعی استفاده کنید. فرض کنید، شما در ستون B تعداد “موارد سفارش‌شده” (“Ordered items“) در ستون C “موارد تحویل‌شده” (“Delivered items“) را دارید. برای اطلاع از درصد محصولات تحویل‌شده، مراحل زیر را انجام دهید:

• فرمول = C2 / B2 را در سلول D2 وارد کنید و آن را به تعداد ردیف مورد نیاز کپی کنید.
• بر روی دکمه‌ی Percent Style (درصد) (زبانه‌ی Home > گروه Number) کلیک کنید تا کسر اعشاری حاصل به صورت درصد نمایش داده شود.
• به یاد داشته باشید که در صورت لزوم تعداد ارقام اعشاری را افزایش دهید، همان‌طور که در نکات نمایش درصد توضیح داده شده‌است.
• انجام شد!

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

در مثال زیر، ستون D درصد گردشده از اقلام تحویل‌شده را نشان می‌دهد، بدون این که ارقام اعشاری را نشان دهد.

محاسبه درصد جمع کل در اکسل

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

مثال ۱. جمع کل در انتهای جدول در یک سلول خاص است

یک سناریو بسیار متداول زمانی است که جمع کل در انتهای یک جدول در یک سلول واحد قرار دارد. در این حالت، فرمول درصد شبیه به نمونه‌ای خواهد بود که بحثش رفت تنها با این تفاوت که ارجاع سلول در مخرج یک ارجاع مطلق (با علامت دلار $) است. علامت دلار ارجاع مربوط به یک سلول معین را ثابت می‌کند، بنابراین هرگز تغییر نمی‌کند و مهم نیست که فرمول کجا کپی شده باشد.

به عنوان مثال، اگر مقداری در ستون B داشته باشید و جمع کل آن‌ها در سلول B10، می‌توانید از این فرمول برای محاسبه درصد جمع کل استفاده کنید: = B2/$B$10

شما از یک ارجاع نسبی سلول به سلول B۲ استفاده می‌کنید زیرا می‌خواهید وقتی که فرمول را در سلول‌های دیگر ستون B کپی می‌کنید، تغییر کند. اما $B$10 را به عنوان یک ارجاع مطلق سلول وارد می‌کنید زیرا می‌خواهید هنگام پر کردن خودکار فرمول تا ردیف ۹ مقدار مخرج ثابت بماند.

نکته: برای ایجاد مخرج به صورت ارجاع مطلق، یا علامت دلار ($) را به صورت دستی تایپ کنید یا روی نوار فرمول روی ارجاع سلول کلیک کنید و F4 را فشار دهید.

تصویر زیر نتایج برگردانده‌شده با فرمول فوق را نشان می‌دهد؛ ستون درصد جمع کل به صورت درصد با ۲ رقم اعشار قالب‌بندی شده‌است.

مثال ۲. بخش‌هایی از جمع کل در چندین سطر هستند

در مثال بالا، فرض کنید چندین سطر برای یک محصول مشابه دارید و می‌خواهید بدانید که همه‌ی سفارشات آن محصول خاص، چه بخشی از کل را دربرمی‌گیرد.

در این حالت، می‌توانید از تابع SUMIF برای جمع همه‌ی سطرهای مربوط به یک محصول معین استفاده کنید، و سپس آن عدد را به تعداد کل تقسیم کنید، مانند این:

=SUMIF(range, criteria, sum_range) / total

با توجه به این‌که ستون A شامل همه‌ی نام‌های محصول است، ستون B مقادیر مربوطه را نشان می‌دهد، سلول E1 نام محصولی است که شما به آن علاقه دارید و جمع کل در سلول B10 است، فرمول واقعی شما ممکن است شبیه به این باشد:

=SUMIF(A2:A9 ,E1, B2:B9) / $B$10

طبیعتاً می‌توانید نام محصول را مستقیماً در فرمول قرار دهید، مانند این:

=SUMIF(A2:A9, “cherries”, B2:B9) / $B$10

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

=(SUMIF(A2:A9, “cherries”, B2:B9) + SUMIF(A2:A9, “apples”, B2:B9)) / $B$10

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

از بین تمام فرمول‌های محاسبه درصد در اکسل، فرمول تغییر درصدی احتمالاً فرمولی است که اغلب استفاده می‌شود.

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

برای محاسبه درصد تغییر بین مقادیر A و B، از فرمول زیر استفاده کنید:

درصد تغییر = (B - A) / A

هنگام استفاده از این فرمول در داده‌های واقعی، مهم است که شما به درستی تعیین کنید که کدام مقدار A است و کدام B. به عنوان مثال، دیروز ۸۰ سیب داشتید و الان ۱۰۰ عدد دارید، به این معنی است که اکنون شما ۲۰ سیب بیشتر از گذشته دارید، که ۲۵٪ افزایش یافته‌است. اگر ۱۰۰ سیب داشتید و اکنون ۸۰ عدد دارید، تعداد سیب‌های شما ۲۰ عدد کاهش یافته‌است یعنی۲۰٪ کاهش.

با در نظر گرفتن موارد فوق فرمول اکسل ما برای درصد تغییر شکل زیر را به خود می‌گیرد:

(مقدار جدید - مقدار قدیمی) / مقدار قدیمی

و حال، بیایید ببینید که چگونه می‌توانید از این فرمول تغییر درصدی (فرمول افزایش درصد اکسل) در صفحات گسترده (spreadsheet) خود استفاده کنید.

مثال ۱. محاسبه درصد تغییر بین ۲ ستون

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

=(C2-B2)/B2

این فرمول تغییر درصد، درصد افزایش / کاهش را در این ماه (ستون C) نسبت به ماه گذشته (ستون B) محاسبه می‌کند.

پس از کپی کردن فرمول در سطرهای دیگر، به یاد داشته باشید که بر روی دکمه‌ی Percent Style روی روبان کلیک کنید تا اعشار را به صورت درصد نشان دهد و نتیجه‌ای مشابه آنچه در تصویر زیر می‌بینید دریافت خواهید کرد. در این مثال، درصدهای مثبت که نشان دهنده افزایش درصد هستند به رنگ مشکی معمول قالب‌بندی شده‌اند، در حالی که درصد منفی (درصد کاهش) با استفاده از تکنیک توضیح داده شده در این نکته به رنگ قرمز قالب‌بندی شده‌اند.

توجه: در صورتی کە ستون مقادیر قدیمی حاوی اعداد منفی باشد، برای نمایش نتایج صحیح لازم است از فرمول زیر استفاده شود. در این فرمول تفاضل مقدار قدیمی و جدید بر قدر مطلق مقدار قدیمی تقسیم شده‌است (با استفاده از تابع ABS).

=(C2-B2)/ABS(B2)

مثال ۲. محاسبه درصد تغییر بین ردیف‌ها

در صورت داشتن یک ستون عدد داشته باشید، و ستون C فروش هفتگی یا ماهانه را نشان می‌دهد، می‌توانید درصد تغییر را با استفاده از این فرمول محاسبه کنید:

=(C3-C2)/C2

در صورتی که C2 اولین سلول است و C3 دومین سلول داده باشد.

توجه: لطفاً توجه داشته باشید که شما باید سطر اول دارای داده را رها کنید و فرمول درصد اکسل خود را در دومین سلول قرار دهید که در این مثال D3 است.

پس از اعمال فرمت درصد در ستون با فرمول درصد تغییر، نتیجه‌ی زیر را می‌گیرید:

اگر می‌خواهید درصد تغییر را در مقایسه با یک سلول خاص محاسبه کنید، باید با استفاده از ارجاع مطلق سلول با علامت $، ارجاع را به آن سلول ثابت کنید، مانند $C$2.

به عنوان مثال، در این‌جا فرمول تغییر درصد اکسل آمده که درصد افزایش / کاهش برای هر ماه را نسبت به ژانویه (C2) محاسبه می‌کند:

=(C3-$C$2)/$C$2

وقتی فرمول را به پایین بکشید تا آن را در سلول‌های دیگر کپی کنید، ارجاع مطلق ($C$2) به همین صورت باقی خواهد ماند، در حالی که ارجاع نسبی (C3) به C4، C5 و غیره تغییر می‌کند.

محاسبه مقدار و جمع کل بر حسب درصد

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

مثال ۱. محاسبه مقدار بر حسب جمع کل و درصد

فرض کنید که شما یک لپ‌تاپ جدید را با قیمت ۹۵۰ دلار خریداری می‌کنید و آن‌ها ۱۱٪ مالیات بر ارزش افزوده را برای این خرید در نظر می‌گیرند. سؤال این است – چقدر باید هزینه علاوه بر هزینه‌ی خالص بپردازید؟ به عبارت دیگر، ۱۱٪ از ۹۵۰ دلار چقدر است؟

فرمول زیر به شما کمک می‌کند:

کل * درصد = مقدار

با فرض این‌که مقدار کل در سلول A2 و درصد در B2 باشد، فرمول فوق به فرمول ساده =A2*B2 تبدیل می‌شود و ۱۰۴٫۵۰ را برمی‌گرداند.

به یاد داشته باشید، وقتی عددی را که علامت درصد (٪) بعد از آن است در اکسل درج می‌کنید، این عدد به عنوان یک‌صدم از مقدار آن تعبیر می‌شود. به عنوان مثال، ۱۱٪ در واقع به عنوان ۰٫۱۱ ذخیره می‌شود و اکسل از این مقدار اساسی در کلیه‌ی فرمول‌ها و محاسبات استفاده می‌کند.

به عبارت دیگر فرمول =A2*11% معادل =A2*0.11 است. طبیعتاً در صورتی که این کار برای شیت شما بهتر باشد، می‌توانید از عدد اعشار متناظر با درصد به طور مستقیم در فرمول استفاده کنید.

مثال ۲. محاسبه مقدار کل بر حسب مقدار و درصد

به عنوان مثال، دوست شما رایانه‌ی قدیمی خود را با ۴۰۰ دلار به شما پیشنهاد داده که شامل ۳۰٪ تخفیف بر روی قیمت اصلی است. شما می‌خواهید بدانید که قیمت اصلی چقدر است؟

از آنجا که ۳۰٪ تخفیف داریم، ابتدا آن را از ۱۰۰٪ کسر کنید تا بدانید که در واقع چه میزان باید پرداخت کنید (100% – 30% = 70%). اکنون برای محاسبه‌ی قیمت اصلی، به فرمول نیاز دارید، یعنی برای پیدا کردن عددی که ۷۰٪ آن برابر با ۴۰۰ باشد.

فرمول به شرح زیر است:

مقدار / درصد = کل

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

=A2/B2 یا =A2/0.7 یا =A2/70%

افزایش / کاهش اعداد بر حسب درصد

فصل تعطیلات پیش رو است و این نشانگر تغییر در هزینه‌های هفتگی معمول شما است. ممکن است بخواهید گزینه‌های مختلفی را برای یافتن جیره‌ی هفتگی بهینه‌ی خود در نظر بگیرید.

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

=  (1 + %) * مقدار

به عنوان مثال فرمول =A1*(1-20%) مقدار سلول A1 را ۲۰٪ افزایش می‌دهد.

برای کاهش مقدار درصدی:

=  (1 - %) * مقدار

به عنوان مثال، فرمول =A1*(1-20%) مقدار سلول A1 را ۲۰٪ کاهش می‌دهد.

در مثال ما، اگر A2 مخارج فعلی شما باشد و B2 درصدی است که می‌خواهید آن مقدار را افزایش یا کاهش دهید، در این‌جا فرمول‌هایی که می‌خواهید در سلول C۲ وارد کنید آمدە است:

افزایش درصد: =A2*(1+B2)

کاهش درصد: =A2*(1-B2)

نحوه‌ی افزایش / کاهش کل یک ستون بر حسب درصد

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

در این‌جا ۵ گام سریع برای انجام این کار آورده شده‌است:

1. تمام اعدادی را که می‌خواهید افزایش یا کاهش دهید در یک ستون وارد کنید، در این مثال، ستون B.
2. در یک سلول خالی یکی از فرمول‌های زیر را وارد کنید: افزایش بر حسب درصد: =1+20% کاهش بر حسب درصد: =1-20%
3. طبیعتاً شما می‌توانید ۲۰٪ فرمول‌های فوق را با درصد مورد نظر خود جایگزین کنید.
4. سلول دارای فرمول را (C2 در این‌جا) انتخاب کرده و با فشار دادن Ctrl+C آن را کپی کنید.
5. محدوده‌ی سلول‌هایی را که می‌خواهید تغییر دهید انتخاب کنید، بر روی موارد انتخابی راست‌کلیک کرده و سپس روی Paste Special… کلیک کنید.
6. در پنجر‌ه‌ی Paste Special در زیر Paste مقدار Values و در زیر Operation مقدار Multiply را انتخاب کرده و OK را بزنید.

و در این‌جا نتیجه آمده است – تمام اعداد موجود در ستون B به مقدار 20% افزایش یافته‌اند:
با همین روش، می‌توانید یک ستون از اعداد را به درصد مشخصی ضرب (multiply) یا تقسیم (divide) کنید. به سادگی درصد مورد نظر را در یک سلول خالی وارد کرده و مراحل بالا را دنبال کنید.

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