آموزش اکسلاخبار آموزشگاهاخبار تصویریدسته‌بندی نشده

  تابع Subtotal در اکسل

تابع Subtotal در اکسل و کاربردهای جالب آن

یکی از راه های رایج و پر استفاده برای نمایش داده ها در اکسل، دسته بندی کردن داده ها از طریق Hide/Unhide و یا Filter هست. برای دسته بندی کردن داده ها و انجام عملیات بر روی آنها، ابزاری رو معرفی کردیم به نام ابزار Subtotal. و گفتیم که این ابزار ترکیبی است از تابع Subtotal و گروه بندی دستی داده ها (که در واقع همون Hide/Unhide کردن راحت تر و شکیل تر هست). در این مقاله میخوایم به آموزش تابع Subtotal بپردازیم که ۳ ویژگی و کاربرد منحصر بفرد داره. یکی از کاربردهای این تابع انجام محاسبات روی داده های فیلتر شده هست که در ادامه توضیح داده میشه.

آرگومان های این تابع به شرح زیر است:

Function_Num: عددی است از ۱ تا ۱۱ و ۱۰۱ تا ۱۱۱ که هر کدوم یک عمل رو انجام میده.

Ref1,[Ref2],…: محدوده ای که میخوایم عملیات مورد نظر روی اون انجام بشه.

بررسی خطاهای تابع

اگر مقدار آرگومان اول عددی خارج از محدوده ۱ تا ۱۱ و یا ۱۰۱ تا ۱۱۱ باشد با خطای #VALUE مواجه می‌شویم.
اگر در توابعی که SUBTOTAL در محاسبات از آنها استفاده می‌کند مانند میانگین واریانس یا انحراف‌معیار حالت تقسیم بر صفر اتفاق بیفتد با خطای #NUM! مواجه می‌شویم.

اموزش تابع PMT در اکسل – محاسبه مبلغ قسط

حتمالا برای شما هم پیش آمده که حداقل یکبار از بانک وام گرفته باشید. معمولا زمان گرفتن وام حس کنجکاوی و حسابگری، ذهن شما را درگیر این سوالات می‌کند که مبلغ هر قسط چقدر می‌شود؟ یا اینکه اگر نرخ سود کمتر یا بیشتر باشد، مبلغ هر قسط چقدر تغییر می‌کند؟! خب برای اینکار می‌توانید به راحتی از تابع PMT در اکسل استفاده کنید.  به کمک این تابع می‌توانید قبل از گرفتن وام، مبلغ دوره‌های مختلف پرداخت وام را با هم مقایسه کنید تا موردی را پیدا کنید که برای شما مناسب‌تر باشد. تابع PMT در دسته توابع مالی (Financial) قرار دارد و  مبلغ قسط را بر اساس نرخ بهره، تعداد اقساط و مبلغ وام محاسبه می‌کند. در این آموزش نحوه استفاده از تابع PMT در اکسل را توضیح می‌دهیم.

نکته: “PMT” مخفف “Payment” به معنی پرداخت است.

ساختار تابع PMT در اکسل

  1. Rate: نرخ بهره در هر دوره را در این قسمت وارد کنید. عدد مربوط به این قسمت می‌تواند به صورت درصد یا اعشاری باشد.
  2. Nper:تعداد اقساط وام را در این قسمت وارد کنید.
  3. Pv:مبلغ قابل پرداخت، یعنی کل مبلغ وام را در این قسمت وارد کنید.
  4. Fv:مقدار مانده نقدی است که می‌خواهید بعد از آخرین پرداخت، پرداخت کنید. در صورت وارد نکردن مقداری در این قسمت، مقدار (۰) فرض می‌شود.
  5. Type:زمان پرداخت وام را در این قسمت تعیین کنید.
    • اگر عدد ۰ را در این قسمت وارد کنید یا هیچ عددی وارد نکنید. پرداخت‌ در پایان هر دوره انجام می‌شود.
    • اگر عدد ۱ را وارد کنید، پرداخت در ابتدای هر دوره انجام می‌شود.

نکته: ۳ آرگومان اول ضروری و دو آرگومان آخر اختیاری هستند.

محاسبه اصل و سود از هر پرداخت با توابع PPMT و IPMT

دو تابع دیگر نیز با تابع PMT‌ همراه و همگام هستند. هر قسط وام از دو بخش تشکیل شده است. قسمتی از این پرداخت، اصل مبلغ وام را تشکیل می‌دهد و قسمت دیگر متعلق به سود وام است. تابع PPMT بخش اصل و تابع IPMT بخش سود از هر پرداخت را مشخص می‌کنند. هر دو این توابع دارای پارامترهای یکسانی هستند و تفاوت آن‌ها فقط در نحوه محاسبه است. بنابراین در اینجا فقط پارامترهای IPMT را معرفی می‌کنیم.

 

همانطور که مشخص است، تنها پارامتر per در تابع IPMT نسبت به PMT‌ اضافه شده است که نشان دهنده دوره‌ای است که می‌خواهید سود وام را در آن محاسبه کنید. شرط هم واحد بودن زمان برای پارامترهای این تابع نیز به قوت خود باقی است.

مثال ۵: براساس داده‌های مربوط به مثال ۱، جدول زیر را تشکیل داده‌ایم تا بخش‌های سود و اصل از پرداخت هر قسط در ۱۲ ماه سال مشخص شود. همانطور که در تصویر زیر دیده می‌شود، مجموع اصل و سود پرداختی از وام با کل مبلغ قسط در هر ماه برابر است. به این معنی که مجموع دو مقدار حاصل از IPMT و PPMT‌، همیشه با مقدار کل قسط که با PMT محاسبه شده، یکسان است.

کپارچه کردن (Consolidate) داده های چندین شیت در یک شیت جداگانه

سریع ترین راه برای یکپارچه کردن داده ها در اکسل (در یک یا چندین ورک‌بوک) استفاده از ابزار Consolidate است. این ابزار از ابزارهای اصلی نرم افزار اکسل است و نیازی به افزونه نداره. پس براحتی میتونیم از این ابزار استفاده کنیم.

تابع Subtotal در اکسل و کاربردهای جالب آن

یکی از راه های رایج و پر استفاده برای نمایش داده ها در اکسل، دسته بندی کردن داده ها از طریق Hide/Unhide و یا Filter هست. برای دسته بندی کردن داده ها و انجام عملیات بر روی آنها، ابزاری رو معرفی کردیم به نام ابزار Subtotal. و گفتیم که این ابزار ترکیبی است از تابع Subtotal و گروه بندی دستی داده ها (که در واقع همون Hide/Unhide کردن راحت تر و شکیل تر هست). در این مقاله میخوایم به آموزش تابع Subtotal بپردازیم که ۳ ویژگی و کاربرد منحصر بفرد داره. یکی از کاربردهای این تابع انجام محاسبات روی داده های فیلتر شده هست که در ادامه توضیح داده میشه.

آرگومان های این تابع به شرح زیر است:

Function_Num: عددی است از ۱ تا ۱۱ و ۱۰۱ تا ۱۱۱ که هر کدوم یک عمل رو انجام میده.

Ref1,[Ref2],…: محدوده ای که میخوایم عملیات مورد نظر روی اون انجام بشه.

بررسی خطاهای تابع

اگر مقدار آرگومان اول عددی خارج از محدوده ۱ تا ۱۱ و یا ۱۰۱ تا ۱۱۱ باشد با خطای #VALUE مواجه می‌شویم.
اگر در توابعی که SUBTOTAL در محاسبات از آنها استفاده می‌کند مانند میانگین واریانس یا انحراف‌معیار حالت تقسیم بر صفر اتفاق بیفتد با خطای #NUM! مواجه می‌شویم.

اموزش تابع PMT در اکسل – محاسبه مبلغ قسط

حتمالا برای شما هم پیش آمده که حداقل یکبار از بانک وام گرفته باشید. معمولا زمان گرفتن وام حس کنجکاوی و حسابگری، ذهن شما را درگیر این سوالات می‌کند که مبلغ هر قسط چقدر می‌شود؟ یا اینکه اگر نرخ سود کمتر یا بیشتر باشد، مبلغ هر قسط چقدر تغییر می‌کند؟! خب برای اینکار می‌توانید به راحتی از تابع PMT در اکسل استفاده کنید.  به کمک این تابع می‌توانید قبل از گرفتن وام، مبلغ دوره‌های مختلف پرداخت وام را با هم مقایسه کنید تا موردی را پیدا کنید که برای شما مناسب‌تر باشد. تابع PMT در دسته توابع مالی (Financial) قرار دارد و  مبلغ قسط را بر اساس نرخ بهره، تعداد اقساط و مبلغ وام محاسبه می‌کند. در این آموزش نحوه استفاده از تابع PMT در اکسل را توضیح می‌دهیم.

نکته: “PMT” مخفف “Payment” به معنی پرداخت است.

ساختار تابع PMT در اکسل

  1. Rate: نرخ بهره در هر دوره را در این قسمت وارد کنید. عدد مربوط به این قسمت می‌تواند به صورت درصد یا اعشاری باشد.
  2. Nper:تعداد اقساط وام را در این قسمت وارد کنید.
  3. Pv:مبلغ قابل پرداخت، یعنی کل مبلغ وام را در این قسمت وارد کنید.
  4. Fv:مقدار مانده نقدی است که می‌خواهید بعد از آخرین پرداخت، پرداخت کنید. در صورت وارد نکردن مقداری در این قسمت، مقدار (۰) فرض می‌شود.
  5. Type:زمان پرداخت وام را در این قسمت تعیین کنید.
    • اگر عدد ۰ را در این قسمت وارد کنید یا هیچ عددی وارد نکنید. پرداخت‌ در پایان هر دوره انجام می‌شود.
    • اگر عدد ۱ را وارد کنید، پرداخت در ابتدای هر دوره انجام می‌شود.

نکته: ۳ آرگومان اول ضروری و دو آرگومان آخر اختیاری هستند.

محاسبه اصل و سود از هر پرداخت با توابع PPMT و IPMT

دو تابع دیگر نیز با تابع PMT‌ همراه و همگام هستند. هر قسط وام از دو بخش تشکیل شده است. قسمتی از این پرداخت، اصل مبلغ وام را تشکیل می‌دهد و قسمت دیگر متعلق به سود وام است. تابع PPMT بخش اصل و تابع IPMT بخش سود از هر پرداخت را مشخص می‌کنند. هر دو این توابع دارای پارامترهای یکسانی هستند و تفاوت آن‌ها فقط در نحوه محاسبه است. بنابراین در اینجا فقط پارامترهای IPMT را معرفی می‌کنیم.

 

همانطور که مشخص است، تنها پارامتر per در تابع IPMT نسبت به PMT‌ اضافه شده است که نشان دهنده دوره‌ای است که می‌خواهید سود وام را در آن محاسبه کنید. شرط هم واحد بودن زمان برای پارامترهای این تابع نیز به قوت خود باقی است.

مثال ۵: براساس داده‌های مربوط به مثال ۱، جدول زیر را تشکیل داده‌ایم تا بخش‌های سود و اصل از پرداخت هر قسط در ۱۲ ماه سال مشخص شود. همانطور که در تصویر زیر دیده می‌شود، مجموع اصل و سود پرداختی از وام با کل مبلغ قسط در هر ماه برابر است. به این معنی که مجموع دو مقدار حاصل از IPMT و PPMT‌، همیشه با مقدار کل قسط که با PMT محاسبه شده، یکسان است.

کپارچه کردن (Consolidate) داده های چندین شیت در یک شیت جداگانه

سریع ترین راه برای یکپارچه کردن داده ها در اکسل (در یک یا چندین ورک‌بوک) استفاده از ابزار Consolidate است. این ابزار از ابزارهای اصلی نرم افزار اکسل است و نیازی به افزونه نداره. پس براحتی میتونیم از این ابزار استفاده کنیم.

نوشته های مشابه

دکمه بازگشت به بالا