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

 

با در نظر گرفتن جدول درآمد افراد مطابق با شکل ذیل، میزان مالیات بر درآمد را به صورت ذیل محاسبه نمایید. (با فرض آنکه مالیات برای هر نفر با یک نرخ ثابت حساب می شود)

نرخ مالیات برای درآم کمتر از ۱ میلیون برابر است با ۰%

نرخ مالیات برای درآمد بین ۱ تا ۲ میلیون برابر است با ۱۰%

نرخ مالیات برای درآمد بین ۲ تا ۳ میلیون برابر است با ۲۰%

نرخ مالیات برای درآمد بین ۳ تا ۴ میلیون برابر است با ۳۰%

نرخ مالیات برای درآمد بیش از ۴ میلیون برابر است با ۴۰%

 

روش حل:

روش حل ۱ با استفاده از تابع IF

با توجه به وجود ۵ حالت برای محاسبه نرخ مالیات، در صورت استفاده از تابع IF نیاز به ثبت ۴ بار IF تو در تو وجود دارد که فرمول آن برای سلول C2 به صورت ذیل می باشد.

=IF(B2<1000000,0,IF(B2<2000000,0.1*B2,IF(B2<3000000,0.2*B2,IF(B2<4000000,0.3*B2,0.4*B2))))

که نوشتن آن بسیار طولانی می باشد ولی در صورت استفاده نتیجه به صورت ذیل حاصل می شود.

 

روش حل ۲ با استفاده از تابع IFS

در روش دیگر می توان با استفاده از تابع IFS کمی فرمول نوشته شده را کوتاه تر کرد و از فرمول ذیل به منظور حل این مسئله استفاده نمود.

=IFS(B2<1000000,0,B2<2000000,0.1*B2,B2<3000000,0.2*B2,B2<4000000,0.3*B2,B2>=4000000,0.4*B2)

که در صورت استفاده از این فرمول، نتیجه به صورت شکل ذیل خواهد بود.

 

روش حل ۳ با استفاده از تابع VLOOKUP

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

بدین منظور قبل از حل مسئله، ابتدا جدول کمکی به صورت ذیل درون محدوده G2 تا H4 ایجاد می نماییم.

 

در این مرحله می توان با استفاده از فرمول ذیل (برای سلول C2) مالیات ردیف دوم را محاسبه نمود.

=VLOOKUP(B2,G2:H6,2,TRUE)*B2

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

=VLOOKUP(B2,$G$2:$H$6,2,TRUE)*B2

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

 

نکته اصلی این روش ورودی چهارم تابع VLOOKUP می باشد که در این مسئله از حالت APPROXIMAT MATCH استفاده شده است.

در این حالت باید ستون جدول جست و جو (جدول کمکی) حتما بر اساس درآمد از کوچک به بزرگ مرتب شده باشد. در هنگام محاسبه میزان مالیات برای فردی با درآمد ۱۷۰۲۰۰۰، تابع VLOOKUP به دنبال این عدد بر روی ستون درآمد در جدول کمکی می پردازد.

اولین عدد از جدول کمکی عدد ۰ است که با عدد مد نظر متفاوت بوده، لذا تابع VLOOKUP به سراغ عدد دوم که برابر با ۱۰۰۰۰۰۰ است می رود. در این حالت حقوق مد نظر (۱۷۰۲۰۰۰) با عدد ۱۰۰۰۰۰۰ مقایسه میشود و با توجه به تفاوت، تابع VLOOKUP به سراغ عدد بعدی (۲۰۰۰۰۰۰) می رود.در زمان مقایسه حقوق ۱۷۰۲۰۰۰ با عدد ۲۰۰۰۰۰۰ به علت بزرگتر بودن عدد ۲۰۰۰۰۰۰ از حقوق مد نظر (عددی که به دنبال آن می گردیم) تابع VLOOKUP عملیات جست و جو را متوقف نموده و عدد مربوط به پله قبلی (درآمد ۱۰۰۰۰۰۰) را به عنوان خروجی نمایش می دهد.