محاسبه مالیات حقوق در اکسل با تابع VLOOKUP
محاسبه مالیات حقوق در اکسل با تابع VLOOKUP
در مقاله «آموزش تابع VLOOKUP در اکسل – قدم به قدم» شما با تابع VLOOKUP آشنا شده‌اید. در آن مقاله گفتیم که تابع VLOOKUP چهار عدد ورودی دارد و سپس سه ورودی آنرا به صورت دقیقی شرح دادیم اما ورودی چهارم را گفتیم که عدد 0 بگذارید و دلیلش را فعلا نپرسید. حال در این مقاله قصد دارم که ورودی چهارم (ورودی آخر) تابع VLOOKUP و کاربردهای آن را به خصوص در محاسبه مالیات حقوق را به شما آموزش دهم.
قبل از شروع چند نکته ساده بگویم:
1) ورودی چهارم (آخرین ورودی) تابع VLOOKUP می‌تواند 0 یا 1 باشد.
2) به جای عدد 0 می‌توانید FALSE بگذارید و به جای عدد 1 هم می‌توان TRUE گذاشت. هیچ مشکلی پیش نمی‌آید.
3) ورودی آخر تابع VLOOKUP اختیاری است. یعنی می‌توانید اصلا چیزی آنجا تایپ نکنید. در این صورت خود اکسل مقدار ورودی چهارم را 1 یا همان TRUE فرض می‌کند و تابع VLOOKUP کارش را انجام خواهد داد.
با توجه به این نکته‌ها هر سه فرمول زیر در اکسل نتیجه / خروجی یکسانی دارند:
=VLOOKUP(“BAHAR” , C:E , 3 , 1)
=VLOOKUP(“BAHAR” , C:E , 3 , TRUE)
=VLOOKUP(“BAHAR” , C:E , 3 )
شرح ورودی آخر VLOOKUP
در شکل زیر می‌بینید که مک کوئین برای پیمودن 100 کیلومتر از مسیر بنزین کافی دارد. قبل از مسابقه او جدول جایگاه‌های بنزین طی مسیر را بررسی می‌کند تا تصمیم بگیرد که در کدام جایگاه باید برای زدن بنزین توقف کند. بدیهی است که در واقعیت شانس کمی وجود دارد که دقیقا یک جایگاه در 100 کیلومتری موجود باشد.
معرفی تابع vlookup حالت مشابه
قطعا شما تایید می‌کنید که بهترین جایگاه برای مک کوئین، جایگاه «مجی» است که در 92 کیلومتری است.
تبریک می‌گم، تموم شد، این کل درس VLOOKUP بود که باید به شما ارائه می‌دادم!
بگذارید این تصمیم مک کوئین را دقیقا بررسی کنیم:
1) مک کوئین اگر به دنبال جایگاهی که «دقیقا در 100 کیلومتری است» بگردد، آنرا نمی‌یابد. (در اکسل خطای N/A# را می‌بینیم) و این کاملا درست است که گاهی دقیقا چیزی که می‌خواهیم وجود ندارد اما چیزی نزدیک (یا مشابه) به آن برای ما قابل قبول است.
2) جدولی که جلوی مک کوئین قرار دارد، یک ویژگی خیلی مهم دارد. این جدول «صورت سعودی» wink است (یعنی sort از کم به زیاد شده است). اگر این جدول این گونه مرتب نشده باشد، کاملا محتمل است که مک کوئین در یافتن جایگاه صحیح دچار اشتباه شود.
3) در جدول جایگاه‌های بنزین، نزدیک‌ترین عدد به 100، جایگاه ترجی در 106 کیلومتری است. اما مک کوئین به دنبال نزدیک‌ترین عدد نیست. بلکه برای او «کمترین نزدیک‌ترین» جایگاه به 100 مهم است. بنابراین جایگاه 92 را می‌یابد.
حال بیایید این جدول را در اکسل وارد کنیم و ببینم که با VLOOKUP در اکسل چگونه می‌توان نام جایگاه مناسب را یافت.
آخرین ورودی تابع VLOOKUP
حالت اول) اگر آخرین ورودی 0 باشد
بگذارید آخرین ورودی را مانند مقاله قبل عدد 0 بگذاریم و ببینم که خروجی تابع چه می‌شود.
=VLOOKUP(100, B:C, 2, 0)
خواهیم دید که اکسل خطای N/A# می‌دهد. یعنی تابع VLOOKUP نمی‌تواند در جدول فوق عدد 100 را بیابید و به ما می‌گوید که 100 نیست. بنابراین اگر ورودی آخر عدد 0 باشد یعنی ما به دنبال دقیقا عدد 100 هستیم و مشابه قبول نیست!
اگر ورودی آخر 0 باشد یعنی مشابه قبول نیست ! مشابه قبول نیست! فقط خود عدد ! فقط خود خود خود عدد! نه مشابه‌اش! نه نزدیکش! نه هیچ دیگری بجز خودش!
حالت دوم) اگر آخرین ورودی عدد 1 باشد
حال آخرین ورودی را به جای 0 عدد 1 می‌گذاریم:
=VLOOKUP(100, B:C, 2, 1)
و می‌بینیم که VLOOKUP برای ما در این جدول عدد 92 را می‌یابد و می‌گوید که در جلوی آن «ترجی» تایپ شده است .
جملات زیر را حداقل 5 بار با صدای بلند بخوانید و به خاطر بسپارید:
اگر ورودی آخر (چهارمین ورودی) عدد 1 باشد یعنی مشابه هم قبول است.
مشابه یعنی کمترین نزدیک‌ترین عدد به مقدار معلوم
مشابه به معنای نزدیک‌ترین عدد نیست، بلکه کمترین نزدیک‌ترین است.
اگر ورودی آخر عدد 1 باشد، باید جدول حتما به شکل صعودی (یعنی از کم به زیاد) Sort (مرتب) شده باشد.
سوال 1) با توجه به جدول فوق، نتیجه فرمول زیر چیست؟
=VLOOKUP(80, B:C, 2, 1)
پاسخ: «اجی» است. یعنی تابع VLOOKUP در جدول عدد 80 را یافت و گفت جلوی آن (دومین ستون جدول) مقدار «اجی» تایپ شده است.
سوال 2) با توجه به جدول فوق نتیجه فرمول زیر چیست؟
=VLOOKUP(105.99999 , B:C, 2, 1)
پاسخ: «مجی» است. اگر چه عدد 105.9999 بسیار نزدیک به 106 است، اما همانطور که گفتیم تابع VLOOKPU نزدیک‌ترین عدد را نمی‌یابد بلکه به دنبال کمترین نزدیک‌ترین عدد می‌گردد که همان 92 است و جلوی آن «مجی» است.
خلاصه و جمع بندی:
اگر ورودی آخر (چهارمین ورودی) تابع VLOOKUP ، عدد 0 یا FALSE باشد، VLOOKUP به دنبال مقدار معلوم در جدول می‌گردد و اگر نیابد، به ما خطا می‌دهد.
اگر ورودی آخر (چهارمین ورودی) تابع VLOOKUP، عدد 1 یا TRUE یا خالی باشد، VLOOKUP به دنبال معلوم و یا کمترین نزدیک‌ترین عدد به معلوم می‌گردد.
در حالت مشابه، حتما باید جدول از کم به زیاد (صعودی) مرتب (sort) شده باشد در غیر اینصورت VLOOKUP مقدار اشتباهی را می‌یابد.
حال وقت آن است که به چند نمونه کاربردی بپردازیم. باید بگویم که کاربرد یافتن مشابه در VLOOKUP در مواقعی کاربرد دارد که جدولی به صورت «بازه‌ای» برای ما تعریف می‌شود.
مثال 1) بازاریابان شرکتی به شرح زیر درصدی از مبلغ فروش را به عنوان پورسانت دریافت می‌کنند، فرمولی بنویسید که برای هر فرد میزان درصدی که باید دریافت کند را محاسبه نماید.
اگر مبلغ فروش بین 0 تا 99 بود، %0
اگر مبلغ فروش بین 100 تا 499 بود، %5
اگر مبلغ فروش بین 500 تا 999 بود، %8
برای مبالغ بیشتر از 1000، %10
راه حل 1 – کسانی که VLOOKUP و حالت مشابه آن را بلد نیستند، این فرمول را با IF می‌نویسند. فرض کنید که مبلغ فروش در سلول B2 وارد شده است، فرمول آن با IF می‌شود:
=IF(B2<100, 0%, IF(AND(B2>=100, B2<500), 5%, IF(AND(B2>=500, B2<1000), 8%, 10%)))
اگر چه این فرمول کاملا درست است اما بدیهی است که بسیار طولانی است و ویرایش آن سخت خواهد بود در ضمن آنکه اگر هر بازه‌ی جدیدی اضافه شود مثلا درصد فروش بین 200 تا 300 بخواهد %6 شود، باید کل این فرمول بازنویسی شود که کاری دشوار است.
راه حل 2 – کسانی که VLOOKUP و حالت مشابه آن را بلد هستند به سادگی این شرح را به صورت یک جدول در می‌آورند در سلول‌هایی جداگانه تایپ می‌کنند و سپس روی آن فقط یک VLOOKUP مشابه می‌نویسند.
محاسبه پورسانت فروش با vlookup
مثال 2) محاسبه مالیات حقوق در اکسل
اگر به جدول محاسبه مالیات حقوق و دستمزد نگاه کنید، خواهید دید که شبه جدولی است که مک کوئین دیده است. در واقع جدول مالیات حقوق به صورت بازه بازه نوشته می‌شود.
تقریبا اکثر حسابدارها مالیات حقوق را در اکسل با فرمول را با IF حل می‌کنند که سرانجام فرمولی بسیار شلوغ می‌شود و خطایابی و اصلاح آن برای سال‌های بعد دشوار خواهد بود.
یک نمونه از این فرمول اینگونه است (مطمئن نیستم که این فرمول صحیح باشد آخر من نیز مانند شما حوصله بررسی و چک کردن آنرا ندارم):
=IF(AI6<=23000000,0,) IF(AND(AI6>23000000,AI6<=92000000),((AI6-23000000)*0.1),IF(AND(AI6>92000000,AI6<=115000000),((6900000)+((AI6-92000000)*0.15)),IF(AND(AI6>115000000,AI6<=161000000),(10350000+(AI6-115000000)*0.25),IF(AND(AI6>161000000,AI6<=230000000),(21850000+(AI6-161000000)*0.35))))))
در حالی که می‌توانید این فرمول دشوار را با نوشتن VLOOKUP در حالت مشابه ، حل کنید و برای سالیان بعد هم کافی است که فقط جدول مالیات را تغییر دهید و لازم نیست که به فرمول محاسبه مالیات دست بزنید.
برای نوشتن فرمول محاسبه مالیات حقوق باید 3 تابع VLOOKUP بنویسیم:
1) یک VLOOKUP برای یافتن درصد مالیات متعلق به این حقوق
2) یک VLOOKUP برای یافتن مجموع مالیات‌های بازه‌های قبلی
3) یک VLOOKUP برای یافتن حد پایینی مالیات متعلق به این حقوق (که اختلاف آن با حقوق باید در مالیات متعلق به این حقوق ضرب شود)
در تصویر زیر فرمول نهایی محاسبه مالیات حقوق و دستمزد در اکسل را می‌توانید ببیند و البته فایل آن را از انتهای همین مقاله دانلود کنید.
محاسبه مالیات حقوق
نکته‌ها و پرسش‌های متداول:
1) فرق بین TRUE و 1 در آخرین ورودی تابع چیست؟
پاسخ: هیچ فرقی ندارند. گفتیم که TRUE یا 1 به معنای یافتن مشابه هستند.
2) اگر بخواهیم که نزدیک‌ترین مقداری را پیدا کنیم، باید چه کار کنیم؟
پاسخ: گفتیم که VLOOKUP نزدیک‌ترین کمترین مقدار را می‌یابد و نه نزدیک‌ترین را. به همین دلیل باید از تکنیک‌های دیگری استفاده کنید و VLOOKUP اینکار را برای شما انجام نمی‌دهد.
3) اگر لیست Sort (مرتب) نباشد، چه خواهد شد؟
پاسخ: توجه داشته باشید که وقتی VLOOKUP در حالت 0 یا False (یعنی دقیقا خودش نه مشابه‌اش) کار می‌کند، کل لیست را تا انتها (یعنی آخرین مقدار) بررسی می‌کند. اما در حالت 1 یا TRUE (یعنی حالت مشابه) ، VLOOKUP همین که به مقداری رسید که از معلوم بیشتر است، همان جا کارش را پایان می‌دهد و لیست را تا انتها بررسی نمی‌کند. بنابراین اگر لیست شما به صورت صعودی (از کم به زیاد) Sort نشده باشد، VLOOKUP همین که مقداری را یافت که از معلوم بزرگتر است، کارش را پایان می‌دهد و مقداری که قبل از آن وارد شده است را به عنوان مشابه در نظر می‌گیرد.
4) نتوانستم دقیقا متوجه شوم که فرمول محاسبه مالیات حقوق چگونه کار می‌کند، می‌شود بیشتر توضیح دهید؟
پاسخ: این فرمول کمی مشکل است و پیشنهاد می‌کنم که خودتان برای فهمیدن آن حداقل 2 ساعت وقت بگذارید و اگر بازهم متوجه نشدید، با 2 نفر از همکارانی که اکسل آنها خوب است جلسه‌ای 45 دقیقه‌ای بگذارید و سعی در بررسی، بازنویسی و درک این فرمول کنید. اگر پس از این جلسه متوجه نشدید، به من ایمیل بدهید. (در واقع خواستم تذکر دهم که درک این فرمول یا یک نگاه ساده و گذرا ممکن نیست و باید برای یافتن مفهوم آن تلاش کنید و وقت بگذارید. مطمئن باشد که پس از مدتی آنرا درک خواهید کرد).
5) تابع LOOKUP چه کاری می‌کند؟
اولا کسی از این تابع استفاده نمی‌کند و ثانیا این تابع دقیقا همان VLOOKUP در حالت مشابه است.
6) آیا از حالت مشابه می‌توان برای یافتن اسامی که مشابه هستند استفاده کرد؟
خیــــــــــــــر !! این یکی از تصورات غلط رایج است. VLOOKUP در حالت مشابه فقط برای اعداد کاربرد دارد و اگر بر روی متن‌ها بکار رود،آن متن به یک عدد (کد اسکی یا یونیکد) تبدیل می‌شود و بر اساس آن عدد محاسبه و یافتن انجام می‌شود.