محاسبه درصد در اکسل یکی از آموزش‌های محوری و بسیار پرکاربرد در این نرم‌افزار به شمار می‌رود. درصد همانطور که از نامش پیداست، یک نسبت از صد است و برای هر شی قابل شمارشی می‌توان درصد در نظر گرفت.
در این مطلب از مجله تکنولوژی ایده آل تک، روش‌های سریع محاسبه درصد در نرم افزار Excel را آموزش می‌دهیم. این روش‌ها شامل فرمول اصلی محاسبه درصد در اکسل و چند فرمول دیگر برای محاسبه درصد افزایش و کاهش، درصد کل، درصد ترکیبی، محاسبه درصد به صورت شرطی و سایر موارد است.
برای مطالعه کامل‌ترین آموزش قدم به قدم و تصویری حالت‌های مختلف درصدگیری در اکسل به زبان ساده، با ما همراه باشید.
چرا درصد می‌گیریم؟
با دانستن نحوه محاسبه درصد، می‌توانیم نه تنها در یک آزمون ریاضی، بلکه در دنیای واقعی نیز نمره خوبی کسب کنیم؛ زیرا مفهوم درصد مفهومی بسیار کاربردی در زندگی حرفه‌ای افراد و حتی در فعالیت‌های روزمره همه انسان‌ها به شمار می‌رود.
اکثر سود و تخفیفات به صورت درصدی است. اگر نحوه محاسبه درصد را ندانیم، گاهی نمی‌فهمیم چقدر از یک معامله سود می‌کنیم. آیا این سود ارزش سرمایه‌گذاری را دارد یا خیر.
زندگی ما با درصدگیری عجین شده است و در تمام این موارد به محاسبه درصد احتیاج داریم: برای محاسبه تخفیفات در رستوران‌ها، پی بردن به ارزش غذایی ثبت شده روی بسته مواد غذایی، تحلیل‌ها در حوزه بورس، تعیین آمار تیم ورزشی مورد علاقه‌مان، تحلیل اینکه آیا گرفتن یک وام با توجه به درصد سودش مقرون به صرفه است یا خیر و ….
بسیاری از کاربران اکسل با نحوه گرفتن درصد در این نرم افزار به مشکل برخورده‌اند؛ به همین دلیل ما در ادامه و به صورت مرحله به مرحله و همراه با آموزش تصویری، به بررسی این کار خواهیم پرداخت.
محاسبه درصد ترکیبی دراکسل
در بعضی مواقع، بخشی از کل به ما داده می شود. در مواقعی دیگر، ممکن است دو قسمت به دست بیاوریم که با کنار هم گذاشتنشان یک کل به‌دست بیاید. مهم است که تشخیص دهیم «چند درصد» از «چه چیزی» را میخواهیم.
مثال برای محاسبه درصد ترکیبی
یک قوطی شامل 1199 تیله قرمز و 485 تیله آبی داریم که در کل می‌شود 1684 تیله. اکنون چند درصد تیله قرمز و چند درصد تیله آبی داریم؟
حل مسئله:
- ابتدا لیستی از موارد مورد نظر ایجاد کرده و با فرمول زیر، جمع مقادیر را محاسبه می‌کنیم:
برای یافتن درصد تیله‌ها، ستونی به همین نام ایجاد کرده، محدوده مورد نظر را انتخاب و از مسیر زیر درصد (٪) را برای محدوده انتخابی فعال می‌کنیم. یا اینکه خودمان مقابل عدد یک درصد تایپ می کنیم.
فرمول زیر فرمول پایه‌ای درصدگیری در اکسل است:
- در ستون درصد تیله‌ها و روبروی تیله قرمز، فرمول زیر را وارد می‌کنیم:
- یک مساوی در سلول تایپ می‌کنیم
- روی سلول B1 کلیک کرده تا به فرمول اضافه شود؛ سپس یک اسلش به عنوان علامت تقسیم تایپ می‌کنیم
- روی B3 کلیک کرده تا به فرمول اضافه شود؛
- در نهایت اینتر می‌زنیم.
اکنون برای اینکه بتوانیم این فرمول را برای تیله آبی هم اعمال کنیم:
- مقدار ثابت را که مخرج کسر است، در فرمول انتخاب کرده، سپس با فشار کلید F4، آن را به عنوان مقدار ثابت تعیین می‌کنیم. (پس از این کار B3، در نوار فرمول تبدیل به $B$3 می‌شود.)
- سلول فرمول را انتخاب و با کلید ترکیبی Ctrl+C آن را کپی می‌کنیم
- سپس محدوده مورد نظر در ستون را انتخاب کرده و اینتر می‌زنیم
با این کار فرمول روی محدوده انتخابی ما اعمال می‌شود.
لازم به ذکر است ممکن است به جای دو سلول، ستونی از صدها سلول داشته باشیم که به درصدگیری ترکیبی احتیاج داشته باشد. می‌توانیم با همین روش، فرمول را برای کل ستون با تعداد زیادی سلول اعمال کنیم.
توجه داشته باشید که در ادامه مطلب هر جا در مورد وارد کردن فرمول سخن گفتیم منظور طی کردن مراحل بالاست.
محاسبه درصد یک ستون در اکسل
به این مثال توجه کنید:
یک شرکت لوازم خانگی سه لیست در اختیار دارد:
- ستون A: محصولات
- ستون B: سفارشات ثبت شده
- ستون C: سفارشات وصول شده
اکنون این شرکت می خواهد درصد سفارشات وصول شده خود را محاسبه کند تا به یک نمودار عملکرد مشتری مداری و موجودی انبار دست یابد.
برای محاسبه این ستون، مطابق دستورالعمل زیر پیش می‌رویم.
می‌دانیم که باید مطابق فرمول زیر عمل کنیم:
در نتیجه فرمول زیر را در سلول D2 وارد می‌کنیم:
اکنون این فرمول را روی کل ستون اعمال می‌کنیم.
نحوه محاسبه قسمت و کل از روی درصد
همانطور که مشاهده می‌کنید، درصدگیری در اکسل بسیار ساده است. همچنین می‌توان به راحتی قسمت یا کل را با در اختیار داشتن درصد، محاسبه کرد. اگر دو جزء از سه جزء قسمت، کل و درصد معلوم باشد، می‌توانیم مجهول را پیدا کنیم. در ادامه و آموزش حالت‌های مختلف، با ما همراه باشید.
محاسبه عددی «درصد» از روی «قسمت» و «کل»
تصور کنید که میخواهیم کالایی با قیمت 250 هزار تومان خریداری کنیم و 9٪ نیز مالیات بر ارزش افزوده روی فاکتور ما اضافه خواهد شد. حال برای خرید این کالا، مبلغ تومانی مالیات بر ارزش افزوده چقدر است و در کل چه مقدار باید بیشتر از قیمت روی برچسب کالا پرداخت کنیم؟
برای محاسبه عددی درصد ارزش افزوده، از فرمول زیر استفاده می‌کنیم.
اکنون برای پیاده سازی فرمول، دستورالعمل زیر را دنبال می‌کنیم:
ابتدا سه ستون در اکسل ایجاد می‌کنیم:
- ستون A: قیمت خالص کالا
- ستون B: مالیات بر ارزش افزوده
- ستون C : محاسبه قیمت کل
ابتدا محدوده‌ای از ستون B2 را انتخاب می کنیم و از مسیر زیر درصد را برای این محدوده فعال می‌کنیم.
سپس فرمول را به صورت زیر وارد می‌کنیم.
- تایپ مساوی و پرانتز
- کلیک روی ستون A2 برای افزودن آن به فرمول
- تایپ علامت ضربدر (×) و کلیک روی ستون B2 برای افزودن آن به فرمول و بستن پرانتز
- کلیک اینتر
با این کار در می‌یابیم که قرار است چند تومان بر قیمت خالص کالا اضافه شود.
همینطور که در تصویر بالا مشاهده کردید، 9٪ از 250.000 تومان 22.500 تومان می‌شود.
محاسبه «کل» از روی «قسمت» و «درصد»
فرض کنید که میخواهیم کالایی را خریداری کنیم که با 30 درصد تخفیف 545 هزار تومان می‌شود. اکنون میخواهیم بدانیم که قیمت آن، قبل از تخفیف چقدر بوده است.
از آنجایی که کالا دارای 30٪ تخفیف است، ابتدا آن را از 100٪ کسر می‌کنیم تا دریابیم که باید چند درصد قیمت اصلی را پرداخت کنیم.
این یعنی ما در واقع 70٪ قیمت اصلی کالا را پرداخت می‌کنیم. در نتیجه برای محاسبه قیمت اصلی باید عددی را پیدا کنیم که 70٪ آن برابر 545.000 باشد.
از این فرمول استفاده می‌کنیم:
در مثال ما : قیمت اصلی = قیمت تخفیف خورده / ( درصد تخفیف – 100)
یا به عبارت دیگر:
ممکن است لیستی از قیمت‌های تخفیف خورده داشته باشیم و حال بخواهیم قیمت اصلی را محاسبه کنیم. با انتخاب سلول و کپی آن، سپس با انتخاب محدوده مورد نظر و اینتر زدن، می‌توان فرمول را روی بقیه مقادیر نیز اعمال کرد.
محاسبه درصد افزایش و کاهش در اکسل
برای محاسبه کل با یک مقدار درصد افزایش از فرمول زیر استفاده می‌کنیم:
برای محاسبه کل با یک مقدار درصد کاهش از فرمول زیر استفاده می‌کنیم:
چطور درصد افزایش را در اکسل محاسبه کنیم؟
به این مثال توجه فرمایید: میخواهیم یک کالا به ارزش 412.000 تومان با 9٪ ارزش افزوده خریداری کنیم، قیمت قابل پرداخت چقدر می‌شود؟
برای پاسخ به این سؤال سه ستون در اکسل ایجاد می‌کنیم:
- ستون A: قیمت روی کالا: 412.000
- ستون B: درصد افزایش: 9٪
- ستون C: قابل پرداخت: ؟
فرمول زیر را وارد کرده و اینتر می‌زنیم.
چطوردرصد کاهش را در اکسل محاسبه کنیم؟
به این مثال توجه فرمایید: میخواهیم یک کالا به ارزش 209.000 تومان با 17٪ تخفیف خریداری کنیم، قیمت قابل پرداخت چقدر است؟
برای پاسخ به این سؤال سه ستون در اکسل ایجاد می‌کنیم:
- ستون A : قیمت روی کالا: 209.000
- ستون B: درصد کاهش: 17٪
- ستون C: قابل پرداخت: ؟
فرمول زیر را وارد کرده و اینتر می‌زنیم.
نحوه محاسبه درصد چندین سطر انتخابی با تابع جمع شرطی
در اکثر مواقع، ما به درصد کل یک ستون احتیاج داریم. این درصد، در یک سلول و در آخرین سطر محاسبه می‌شود. در این حالت فرمول درصد گیری برابر است با قسمت/ کل × 100 که در قسمت قبل توضیح داده شد.
فرض کنیم که فروشنده لوازم خانگی هستیم. یک محصول از چند برند داریم و اکنون میخواهیم با مقایسه درصد فروش در روزهای مختلف، دریابیم که کدام برند پرفروش‌تر است.
در این حالت باید از تابع SUMIF استفاده کنیم. این تابع در اکسل جمع مقادیر را بر اساس یک شرط محاسبه می‌کند. شرط تابع SUMIF می‌تواند تاریخ، عدد و متن باشد. این تابع برای شرط از عملگرهای منطقی و کاراکترهای Wild Card هم پشتیبانی می‌کند.
هدف ار استفاده تابع SUMIF، این است که ابتدا تعداد کل سفارشات هر برند را جمع زده و سپس تقسیم بر کل تعداد کالا کنیم.
ساختار تابع SUMIF در اکسل
تابع SUMIF برای جمع زدن سلول‌ها به صورت شرطی استفاده می‌شود و مانند همه توابع Excel، از اجزای مختلفی تشکیل شده است که برای دست‌یابی به نتیجه مورد نظر، باید آنها را درست وارد کنیم.
Range: در این قسمت، محدوده سلول‌هایی را که قرار است در آن شرط جستجو شود، انتخاب می‌کنیم. این محدوده می‌تواند شامل یک ستون از مقادیر عددی یا متنی باشد. با نگاهی به این تابع متوجه می‌شویم که از شرط‌های زیر تشکیل شده است:
- Criteria: شرط مورد نظر را در این قسمت وارد کنیم. شرط ما می‌تواند عدد، تاریخ، متن، عملکرد منطقی به همراه عدد، آدرس سلول و کاراکترهای حذف شده یا Wild card باشد. برای مثال می‌توانیم شرط‌هایی مانند “اتو بوش”، ”۱۰/۲/۲۰۱۴” و ”<5″ را وارد کنیم.
- sum_range: باید محدوده سلول‌هایی را که قرار است، با توجه به شرط، جمع زده شود؛ در این قسمت وارد کنیم. فقط مقادیر عددی در این قسمت پذیرفته می‌شوند.
نکات:
- دستور اول و دوم اجباری و دستور سوم اختیاری است. در صورت حذف دستور سوم، اعداد تمامی سلول‌های موجود در ستون Range ، با یک‌دیگر جمع بسته می شوند.
- باید در قسمت شرط، عبارت متنی و عملکردهای ریاضی در میان دو گیومه ” ” قرار گیرند. البته اعداد را می‌توان بدون علامت گیومه نیز درج کرد.
چگونه درصد را در چندین سطر انتخابی محاسبه کنیم؟
برگردیم به مثال تعیین پرفروش ترین برند در یک محصول:
- ابتدا لیستی از محصولمان را در اکسل تهیه می‌کنیم.
- ستون «برند» و «درصد از کل» را ایجاد می‌کنیم.
- مقادیر زیر را در تابع SUMIF جای‌گذاری می‌کنیم:
در سلولی که میخواهیم درصد محاسبه شود، کلیک می‌کنیم.دستورالعمل وارد کردن فرمول:
- عبارت زیر را تایپ می‌کنیم
با درگ کردن، تمامی سلول‌ها در ستون داده‌هایمان را انتخاب می‌کنیم؛ زیرا میخواهیم اکسل محصول «اتوی بوش» را در آن محدوده از ستون پیدا کند. با این کار، مشاهده می‌کنیم در سلولی که در آن فرمول را تایپ کرده‌ایم، A2:A7 با رنگ آبی اضافه می‌شود.
- پس از تایپ یک ویرگول، می‌توانیم به دو صورت نام محصول مورد نظر را به فرمول اضافه کنیم:
1- عبارت “اتوی بوش” را در فرمول تایپ کنیم. این عبارت حتما باید داخل گیومه تایپ شود.
2- روی سلول محتوی عبارت “اتوی بوش” کلیک کنیم تا نام سلول به فرمول افزوده شود.
- پس از تایپ یک ویرگول، با درگ کردن، سلول‌های ستون «سفارشات ثبت شده» را انتخاب می‌کنیم. با این کار، بازه سلول‌ها در فرمول ظاهر می‌شود. (B2:B7)
- پرانتز را بسته، یک اسلش تایپ کرده، روی سلول حاوی جمع کل کلیک می کنیم.
- اینتر می‌زنیم. درصد تعداد محصولات درخواستی “اتوی بوش” مشخص می‌شود.
- اکنون مقادیر ثابت در فرمول را که A2:A7 ، B2:B7 و B8 است انتخاب کرده و کلید F4 را فشار می‌دهیم. با این کار این سه بخش در فرمول تثبیت می‌شوند. متغیر ما برندهای اتو است (A2) که به آن دست نمی‌زنیم.
همینطور که در نوار فرمول مشاهده می‌کنید، فرمول به صورت زیر در می‌آید:
محاسبه درصد تغییر(افزایش یا کاهش) در اکسل
محاسبه درصد تغییر در اکسل بسیار ساده و کاربردی است. به همین دلیل، فرمول محاسبه کاهش یا افزایش درصد، یکی از پرکاربردترین فرمول‌ها درمیان تمامی فرمول‌های درصد در اکسل است.
این فرمول برای صاحبان فروشگاه یا صنایع، بسیار ضروری است و خیال آنها را از بابت محاسبات طولانی مدت راحت می‌کند. زیرا به طور مثال، هر گاه قیمت یک محصول با کاهش یا افزایش روبرو شد، به راحتی می‌توانند با استفاده از این فرمول، درصد اختلاف قیمت قبل و بعد را محاسبه کرد.
این فرمول به صورت زیر است:
چگونه در اکسل درصد تغییر را بین ستون‌ها محاسبه کنیم؟
به مثال زیر توجه کنید:
در فروشگاه لوازم خانگی برخی اقلام، نسبت به ماه قبل، افزایش قیمت و برخی دیگر، کاهش قیمت داشته‌اند. اکنون ما سه ستون در اختیار داریم و میخواهیم درصد تغییر قیمت را محاسبه کنیم.
- ستون A: نام کالا
- ستون B: قیمت سابق
- ستون C: قیمت جدید کالا
- ستون D: درصد تغییر قیمت
این فرمول را در سلول D2 وارد می‌کنیم:
- روی سلول C2 که قیمت جدید است، کلیک می‌کنیم
- یک علامت منها ( – ) می‌گذاریم
- روی سلول B2 کلیک می‌کنیم
- یک علامت اسلش می‌گذاریم
- دو مرتبه روی سلول B2 کلیک می‌کنیم. فرمول مطابق تصویر زیر نوشته می‌شود
- اکنون اینتر می‌زنیم. همانطور که در تصویر زیر مشاهده می‌کنیم اختلاف قیمت ماه قبل و ماه جاری یخچال، 29٪ است. یعنی یخچال نسبت به ماه قبل 29 درصد گران‌تر شده است.
- برای یافتن درصد اختلاف قیمت محصولات در دو ماه، ابتدا سلولی را که در آن فرمول نوشته‌ایم، انتخاب می‌کنیم؛ با فشار کلید Ctrl+C سلول را کپی می‌کنیم؛ سپس سلول‌های خالی ستون D را با درگ کردن، انتخاب کرده و اینتر می‌زنیم.
درصدهای مثبت، نشان‌دهنده افزایش و درصدهای منفی نمایان‌گر کاهش مقادیر جدید نسبت به مقادیر قبلی هستند.
چگونه در اکسل درصد تغییر بین سطرها را محاسبه کنیم؟
در صورتی که تنها یک ستون در اختیار داریم و میخواهیم به طور مثال تغییر قیمت یک محصول را در حراجی‌های هفتگی با ماهیانه محاسبه کنیم، از فرمول تغییر مقدار بین سطری استفاده می‌کنیم.
فرض کنید یک محصول فروشگاهی با نرخی ثابت به طور دوره‌ای افزایش یا کاهش قیمت داشته است. اکنون میخواهیم درصد این تغییر قیمت را محاسبه کنیم.
- در ستون A دوره‌های زمانی که در آن تغییر قیمت صورت گرفته، لیست شده است
- در ستون B، قیمت‌های مختلف این محصول در بازه‌های مختلف زمانی قید شده است
- در ستون C، قصد داریم درصد تغییر قیمت را محاسبه کنیم
در نتیجه با فایلی مطابق شکل زیر مواجه هستیم:
در مثال بالا، درصد تغییر بین سطرها، با فرمول زیر در اکسل محاسبه می‌شود:
ابتدا ستون و محدوده‌ای را که میخواهیم در آن درصد بگیریم، انتخاب می‌کنیم.
- در برگه Home و قسمت Number گزینه (٪) را انتخاب می‌کنیم.
- پس از وارد کردن فرمول اینتر می‌زنیم.
- برای اعمال فرمول در محدوده مورد نظر، ابتدا سلولی را که در آن فرمول نوشته‌ایم، انتخاب می‌کنیم؛ با فشار کلید Ctrl+C سلول را کپی می‌کنیم؛ سپس سلول‌های خالی ستون D را با درگ کردن، انتخاب کرده و اینتر می‌زنیم.
(برای مشاهده تصویری نحوه وارد کردن فرمول و اعمال آن روی یک ستون به صورت قدم به قدم، در همین مطلب به بخش «طریقه وارد کردن فرمول» مراجعه فرمایید.)
سخن پایانی
در برنامه اکسل می‌توانیم برای هر محاسبه، یک فرمول را وارد و سپس آن را در چند مرحله ساده، روی ستونی متشکل از صدها داده، اعمال کنیم. در نتیجه آموزش نحوه کار با نرم‌افزار مفید اکسل، می‌تواند از ساعت‌ها و گاهی روزها کار زمان‌بر جلوگیری کند. به همین علت آموزش کار با اکسل یکی از محوری‌ترین آموزش‌ها برای صاحبان مشاغل مختلف به شمار می‌رود.
در مجله اینترنتی ایده آل تک، هر آموزشی که شما از اکسل انتظار دارید، به زبان ساده و قدم به قدم یافت می‌شود. می‌توانید این آموزش‌ها را که هر روز در حال کامل‌تر شدن هستند، با دنبال کردن ما به دست بیاورید.