ساخت صورت جریان نقدی در اکسل
ساخت صورت جریان نقدی در اکسل : چرا باید جریان نقدی کسب‌وکار را کنترل کنیم؟...
آموزش حسابداری در اکسل
آموزش تابع IF تودرتو در اکسل برای محاسبه پاداش و جریمه
فهرست مطالب در این مقاله:
آموزش تابع IF تودرتو در اکسل : در بسیاری از کسب‌وکارها، محاسبه پاداش کارکنان، پورسانت فروش، جریمه تأخیر و امتیاز عملکرد بر اساس چند شرط انجام می‌شود. برای مثال، ممکن است کارمندی که فروش بیشتری دارد پاداش بالاتری دریافت کند یا میزان جریمه تأخیر بر اساس تعداد روزهای غیبت تعیین شود.
تابع IF در اکسل برای بررسی شرط‌ها و انتخاب نتیجه مناسب استفاده می‌شود. با قرار دادن یک تابع IF داخل تابع دیگر، می‌توان چند سطح شرط ایجاد کرد؛ روشی که به آن IF تودرتو (Nested IF) می‌گویند.
در این آموزش، با مثال‌های ساده و کاربردی یاد می‌گیرید چگونه از IF تودرتو برای محاسبه پاداش، پورسانت و جریمه استفاده کنید و در صورت نیاز، فرمول‌ها را با تابع‌های IFS و AND ترکیب کنید.
تابع IF در اکسل چیست؟
تابع IF یک شرط را بررسی می‌کند و بر اساس درست یا نادرست بودن آن، یکی از دو نتیجه را برمی‌گرداند.
ساختار تابع به این شکل است:
=IF(logical_test, value_if_true, value_if_false)اجزای تابع عبارت‌اند از:
logical_test: شرطی که باید بررسی شود.value_if_true: نتیجه‌ای که در صورت درست بودن شرط نمایش داده می‌شود.value_if_false: نتیجه‌ای که در صورت نادرست بودن شرط نمایش داده می‌شود.
برای مثال، فرض کنید مبلغ فروش یک کارمند در سلول B2 ثبت شده است. اگر فروش او حداقل ۱۰۰ میلیون تومان باشد، پاداش ۵ میلیون تومان دریافت کند و در غیر این صورت پاداشی نگیرد:
=IF(B2>=100000000,5000000,0)اگر مقدار B2 برابر با ۱۲۰,۰۰۰,۰۰۰ باشد، نتیجه فرمول ۵,۰۰۰,۰۰۰ خواهد بود؛ اما اگر فروش کمتر از ۱۰۰,۰۰۰,۰۰۰ باشد، نتیجه صفر است.
IF تودرتو چیست و چه زمانی استفاده می‌شود؟
وقتی بیش از دو نتیجه احتمالی داشته باشید، می‌توانید یک تابع IF را داخل بخش نتیجه تابع دیگر قرار دهید.
برای مثال، فرض کنید سیاست پاداش فروش شرکت به این صورت باشد:
| میزان فروش ماهانه | پاداش |
|---|---|
| کمتر از ۵۰ میلیون تومان | صفر |
| از ۵۰ تا کمتر از ۱۰۰ میلیون تومان | ۲ میلیون تومان |
| از ۱۰۰ تا کمتر از ۱۵۰ میلیون تومان | ۵ میلیون تومان |
| ۱۵۰ میلیون تومان و بیشتر | ۱۰ میلیون تومان |
این اعداد صرفاً یک نمونه آموزشی هستند و باید با سیاست واقعی کسب‌وکار جایگزین شوند.
برای محاسبه پاداش بر اساس این جدول، می‌توان از فرمول زیر استفاده کرد:
=IF(B2>=150000000,10000000,IF(B2>=100000000,5000000,IF(B2>=50000000,2000000,0)))در این فرمول، اکسل ابتدا بررسی می‌کند که فروش حداقل ۱۵۰ میلیون تومان است یا خیر. اگر شرط برقرار نباشد، شرط بعدی بررسی می‌شود و این روند تا تعیین پاداش ادامه پیدا می‌کند.
نکته مهم: در IF تودرتو، ترتیب بررسی شرط‌ها اهمیت دارد. بهتر است در مثال‌های مبتنی بر حداقل مبلغ، شرط‌ها را از بزرگ‌ترین آستانه به کوچک‌ترین آستانه بنویسید تا نتیجه درست محاسبه شود.
مثال اول: محاسبه خودکار پاداش فروش کارکنان
فرض کنید جدولی مانند زیر دارید:
| نام کارمند | فروش ماهانه | پاداش |
|---|---|---|
| علی | ۴۰,۰۰۰,۰۰۰ | ۰ |
| سارا | ۸۰,۰۰۰,۰۰۰ | ۲,۰۰۰,۰۰۰ |
| رضا | ۱۲۰,۰۰۰,۰۰۰ | ۵,۰۰۰,۰۰۰ |
| مریم | ۱۷۰,۰۰۰,۰۰۰ | ۱۰,۰۰۰,۰۰۰ |
در این جدول، نام کارمند در ستون A، مبلغ فروش در ستون B و پاداش در ستون C قرار دارد.
در سلول C2 فرمول زیر را وارد کنید:
=IF(B2>=150000000,10000000,IF(B2>=100000000,5000000,IF(B2>=50000000,2000000,0)))سپس فرمول را به ردیف‌های پایین کپی کنید.
اکسل برای هر کارمند مقدار فروش را بررسی می‌کند و پاداش متناظر با بازه فروش را نمایش می‌دهد. با اضافه‌شدن کارکنان جدید، کافی است فرمول را برای ردیف آن‌ها نیز اعمال کنید.
چرا شرط‌ها از بالا به پایین نوشته شده‌اند؟
اگر ابتدا شرط B2>=50000000 را بررسی کنید، فردی با فروش ۱۷۰ میلیون تومان هم در همان شرط اول قرار می‌گیرد و پاداش ۲ میلیون تومانی دریافت می‌کند؛ در نتیجه شرط‌های بزرگ‌تر هرگز بررسی نمی‌شوند.
به همین دلیل، وقتی چند آستانه حداقل دارید، ابتدا بزرگ‌ترین آستانه را بررسی کنید.
مثال دوم: محاسبه جریمه تأخیر بر اساس تعداد روزها
فرض کنید شرکت برای تأخیر کارکنان یک سیاست نمونه تعریف کرده است:
| تعداد روزهای تأخیر | مبلغ جریمه |
|---|---|
| صفر روز | صفر تومان |
| ۱ تا ۲ روز | ۲۰۰,۰۰۰ تومان |
| ۳ تا ۵ روز | ۵۰۰,۰۰۰ تومان |
| بیشتر از ۵ روز | ۱,۰۰۰,۰۰۰ تومان |
فرض کنید تعداد روزهای تأخیر در سلول B2 ثبت شده است. فرمول زیر را برای محاسبه مبلغ نمونه جریمه وارد کنید:
=IF(B2>5,1000000,IF(B2>=3,500000,IF(B2>=1,200000,0)))این فرمول جریمه را بر اساس تعداد روزهای ثبت‌شده محاسبه می‌کند.
توجه: این جدول و مبالغ فقط نمونه آموزشی‌اند. پیش از استفاده واقعی در حقوق و دستمزد، باید سیاست مصوب سازمان و مقررات قانونی مربوط به کسر از حقوق بررسی شود؛ هر نوع جریمه‌ای الزاماً مجاز نیست.
مثال سوم: محاسبه پورسانت فروش با چند سطح
گاهی پورسانت به‌صورت درصدی از فروش محاسبه می‌شود و درصد آن با رسیدن به آستانه‌های مشخص تغییر می‌کند.
برای نمونه، سیاست فرضی زیر را در نظر بگیرید:
| فروش ماهانه | نرخ پورسانت |
|---|---|
| کمتر از ۵۰ میلیون تومان | ۲٪ |
| از ۵۰ تا کمتر از ۱۰۰ میلیون تومان | ۳٪ |
| ۱۰۰ میلیون تومان و بیشتر | ۵٪ |
فرض کنید مبلغ فروش در سلول B2 قرار دارد و می‌خواهید نرخ پورسانت را در سلول C2 محاسبه کنید:
=IF(B2>=100000000,5%,IF(B2>=50000000,3%,2%))سپس برای محاسبه مبلغ پورسانت در سلول D2 بنویسید:
=B2*C2برای مثال، اگر فروش برابر با ۱۲۰,۰۰۰,۰۰۰ تومان باشد، نرخ پورسانت ۵ درصد و مبلغ آن ۶,۰۰۰,۰۰۰ تومان خواهد بود.
در این روش، نرخ پورسانت و مبلغ نهایی در دو ستون جداگانه محاسبه می‌شوند؛ بنابراین بررسی و تغییر نرخ‌ها آسان‌تر خواهد بود.
نکته: در این مثال، نرخ تعیین‌شده بر کل مبلغ فروش اعمال می‌شود. اگر سیاست شرکت بر مبنای پورسانت پلکانی باشد، باید هر بخش از فروش را با نرخ مربوط به همان بازه محاسبه کنید؛ فرمول آن با این مثال متفاوت است.
مثال چهارم: تعیین وضعیت عملکرد کارکنان با IF تودرتو
فرض کنید امتیاز عملکرد کارکنان از ۱۰۰ محاسبه می‌شود و می‌خواهید سطح عملکرد آن‌ها را مشخص کنید:
| امتیاز | وضعیت |
|---|---|
| ۹۰ و بیشتر | عالی |
| ۷۵ تا کمتر از ۹۰ | خوب |
| ۶۰ تا کمتر از ۷۵ | قابل قبول |
| کمتر از ۶۰ | نیازمند بهبود |
اگر امتیاز در سلول B2 باشد، فرمول زیر وضعیت را تعیین می‌کند:
=IF(B2>=90,"عالی",IF(B2>=75,"خوب",IF(B2>=60,"قابل قبول","نیازمند بهبود")))این فرمول برای دسته‌بندی امتیازها، رتبه‌بندی عملکرد و ساخت گزارش منابع انسانی مفید است.
اگر امتیاز ممکن است خالی باشد، بهتر است ابتدا خالی بودن سلول را بررسی کنید:
=IF(B2="","",IF(B2>=90,"عالی",IF(B2>=75,"خوب",IF(B2>=60,"قابل قبول","نیازمند بهبود"))))در این نسخه، اگر B2 خالی باشد، خروجی هم خالی می‌ماند؛ در غیر این صورت سطح عملکرد محاسبه می‌شود.
ترکیب IF با AND برای بررسی چند شرط هم‌زمان
گاهی پاداش فقط به میزان فروش وابسته نیست و باید شرط دیگری نیز برقرار باشد؛ برای مثال، کارمند باید هم به حد فروش برسد و هم حداقل امتیاز رضایت مشتری را کسب کند.
تابع AND زمانی مقدار TRUE می‌دهد که تمام شرط‌های داخل آن درست باشند.
فرض کنید:
- فروش در سلول
B2قرار دارد. - امتیاز رضایت مشتری در سلول
C2قرار دارد. - برای دریافت پاداش، فروش باید حداقل ۱۰۰ میلیون تومان و امتیاز رضایت حداقل ۸۰ باشد.
فرمول:
=IF(AND(B2>=100000000,C2>=80),5000000,0)اگر هر دو شرط برقرار باشند، پاداش ۵ میلیون تومان نمایش داده می‌شود؛ در غیر این صورت، خروجی صفر خواهد بود.
ترکیب IF با OR
اگر برای دریافت پاداش کافی باشد یکی از دو شرط برقرار باشد، از OR استفاده کنید.
برای مثال، اگر فروش حداقل ۱۵۰ میلیون تومان باشد یا امتیاز رضایت مشتری به ۹۵ برسد، پاداش ویژه تعلق بگیرد:
=IF(OR(B2>=150000000,C2>=95),3000000,0)در این فرمول، برقرار بودن هر یک از شرط‌ها برای دریافت پاداش کافی است.
آیا به‌جای IF تودرتو می‌توان از تابع IFS استفاده کرد؟
در نسخه‌هایی از اکسل که تابع IFS را پشتیبانی می‌کنند، می‌توانید چند شرط را بدون قرار دادن IF داخل یکدیگر بنویسید.
برای مثال، فرمول پاداش فروش را می‌توان به این شکل نوشت:
=IFS(B2>=150000000,10000000,B2>=100000000,5000000,B2>=50000000,2000000,B2<50000000,0)این تابع شرط‌ها را به ترتیب بررسی می‌کند و نتیجه مربوط به نخستین شرط درست را برمی‌گرداند.
| ویژگی | IF تودرتو | IFS |
|---|---|---|
| چند شرط | بله | بله |
| خوانایی در شرط‌های متعدد | ممکن است پیچیده شود | معمولاً ساده‌تر است |
| سازگاری با نسخه‌های قدیمی | گسترده‌تر | وابسته به نسخه اکسل |
| تعریف نتیجه پیش‌فرض | با آخرین بخش IF | بهتر است شرط نهایی همه حالت‌های باقی‌مانده را پوشش دهد |
در مثال بالا، شرط‌ها باید طوری تنظیم شوند که تمام حالت‌های ممکن را پوشش دهند. اگر هیچ شرطی برقرار نباشد، IFS ممکن است خطا برگرداند؛ بنابراین در فایل‌های واقعی، مقادیر ورودی و محدوده‌های مجاز را بررسی کنید.
خطاهای رایج در IF تودرتو و روش رفع آن‌ها
۱. نمایش نتیجه اشتباه
معمولاً علت این مشکل، ترتیب نادرست شرط‌هاست. در محاسبات مبتنی بر حداقل فروش، شرط‌های بزرگ‌تر را زودتر بررسی کنید تا افراد واجد شرایط سطح بالاتر، در یک شرط ساده‌تر متوقف نشوند.
۲. خطای پرانتز
در فرمول‌های تودرتو باید تعداد پرانتزهای باز و بسته با یکدیگر برابر باشد. اگر فرمول طولانی است، ابتدا آن را با دو شرط آزمایش کنید و سپس شرط‌های بعدی را اضافه کنید.
۳. نمایش صفر به‌جای سلول خالی
اگر نمی‌خواهید برای ردیف‌هایی که هنوز اطلاعات ندارند عدد صفر نمایش داده شود، شرط خالی بودن سلول را در ابتدای فرمول قرار دهید:
=IF(B2="","",IF(B2>=100000000,5000000,0))۴. اشتباه در آستانه‌ها
اگر مرز بازه‌ها دقیق تعریف نشده باشد، ممکن است یک مقدار در گروه اشتباه قرار گیرد. برای مثال، مشخص کنید که فروش دقیقاً برابر با ۱۰۰ میلیون تومان در کدام سطح قرار می‌گیرد و سپس از عملگرهای >= یا > متناسب با سیاست خود استفاده کنید.
۵. اختلاف جداکننده فرمول‌ها
در بسیاری از تنظیمات اکسل، آرگومان‌ها با کاما جدا می‌شوند؛ اما در برخی سیستم‌ها باید از نقطه‌ویرگول استفاده شود. اگر فرمول با کاما پذیرفته نشد، جداکننده متناسب با تنظیمات اکسل خود را به کار ببرید.
نکات مهم برای ساخت فایل پاداش و جریمه
- قوانین محاسبه را مستند کنید. آستانه‌ها، درصدها و مبلغ‌ها باید مطابق سیاست واقعی سازمان باشند.
- ورودی‌ها را استاندارد کنید. برای جلوگیری از اشتباه، از Data Validation برای محدودکردن نوع ورودی یا انتخاب وضعیت استفاده کنید.
- آستانه‌ها را در سلول‌های جداگانه قرار دهید. اگر نرخ‌ها و مبالغ مرتب تغییر می‌کنند، نگهداری آن‌ها در جدول تنظیمات از اصلاح مکرر فرمول جلوگیری می‌کند.
- فرمول‌ها را با چند مقدار مرزی آزمایش کنید. مقادیر کمتر از آستانه، برابر آستانه و بیشتر از آن را بررسی کنید.
- فرمول‌ها را از تغییر ناخواسته محافظت کنید. در فایل‌های مورد استفاده چند کاربر، می‌توانید سلول‌های فرمول را قفل و از شیت محافظت کنید.
پرسش‌های متداول
تابع IF تودرتو در اکسل چیست؟
IF تودرتو یعنی قرار دادن یک تابع IF داخل تابع IF دیگر برای بررسی چند شرط و انتخاب یکی از چند نتیجه احتمالی.
چند شرط را می‌توان با IF تودرتو بررسی کرد؟
در نسخه‌های جدید اکسل، امکان تو در تو کردن تعداد زیادی تابع IF وجود دارد؛ اما هرچه فرمول طولانی‌تر شود، نگهداری و عیب‌یابی آن دشوارتر خواهد بود. برای شرط‌های زیاد، بررسی تابع IFS یا استفاده از جدول نرخ‌ها می‌تواند انتخاب بهتری باشد.
برای محاسبه پاداش فروش از کدام فرمول استفاده کنم؟
اگر پاداش در چند سطح ثابت تعیین می‌شود، می‌توانید از IF تودرتو استفاده کنید. اگر پاداش درصدی است، ابتدا نرخ مناسب را تعیین و سپس آن را در مبلغ فروش ضرب کنید.
آیا می‌توان با IF تودرتو جریمه تأخیر را محاسبه کرد؟
بله. می‌توانید تعداد روزهای تأخیر را با چند آستانه مقایسه کنید و بر اساس نتیجه، مبلغ نمونه جریمه را نمایش دهید؛ البته سیاست محاسبه و مجاز بودن هرگونه کسر از حقوق باید جداگانه بررسی شود.
IF تودرتو بهتر است یا IFS؟
برای دو یا سه شرط ساده، IF تودرتو معمولاً کافی است. اگر شرط‌های زیادی دارید و نسخه اکسل شما از IFS پشتیبانی می‌کند، این تابع اغلب خواناتر است.
جمع‌بندی
تابع IF تودرتو به شما امکان می‌دهد محاسباتی مانند پاداش فروش، پورسانت، سطح عملکرد و دسته‌بندی مقادیر را بر اساس چند شرط انجام دهید. برای استفاده صحیح، ترتیب شرط‌ها، مرز بازه‌ها و نتیجه هر حالت را با دقت مشخص کنید.
برای فایل‌های مالی حرفه‌ای‌تر، بهتر است نرخ‌ها و آستانه‌ها را در جدول تنظیمات نگهداری کنید، ورودی‌ها را اعتبارسنجی کنید و فرمول را با مقادیر مرزی آزمایش کنید. به این ترتیب، تغییر سیاست‌های محاسباتی و نگهداری فایل آسان‌تر خواهد شد.
