چگونه در اکسل فرمول نویسی کنیم؟ (قسمت چهارم)

پیش از شروع قسمت سوم، بد نیست نگاهی بر قسمت‌های پیشین این آموزش داشته باشید:

• چگونه در اکسل فرمول نویسی کنیم؟ (قسمت اول)
• چگونه در اکسل فرمول نویسی کنیم؟ (قسمت دوم)
• چگونه در اکسل فرمول نویسی کنیم؟ (قسمت سوم)

تابع PMT

تابع PMT مقدار قسط دوره‌ای وام را محاسبه می‌کند. تابع، ۵ ورودی دارد که ۲ ورودی آخر آن اختیاری هستند و در این آموزش بررسی نخواهند شد؛ به عبارت دیگر از مقدارهای پیش فرض ورودی‌های آخر استفاده خواهد شد. سه ورودی اجباری تابع که برای محاسبات ساده کاربرد دارد به شرح زیر هستند:

(مبلغ وام،تعداد قسط،مقدار بهره)PMT

نکته‌ی مهمی که در استفاده از تابع PMT باید در نظر داشته باشید این است که دوره قسط و دوره مقدار بهره یکسان باشد.

اگر به عنوان مثال قصد دارید قسط‌های ماهانه را محاسبه کنید، حتماً باید مقدار بهره را هم ماهانه وارد نمایید. اگر بهره سالیانه ۱۲٪ باشد، بهره ماهانه ۱٪ خواهد بود.

فرض کنید قصد دارید مبلغ 1،000،000 تومان وام بگیرید و این وام را در ۱۰ قسط با بهره ۱۸٪ باز پس دهید. محاسبه به این صورت خواهد بود:

(PMT(18%/12,10,1000000

شکل زیر کاربرگی را نشان می‌دهد که محاسبه‌ی این چنینی را انجام می‌‌دهد.

مقدار بهره سالیانه بر ۱۲ تقسیم شده است تا بهره ماهانه به دست آید. خروجی این مثال با رنگ قرمز و به صورت ($108,434,18) نمایش داده می‌شود. این شیوه نمایش متعلق به مبالغ مالی است؛ وجود پرانتز و رنگ قرمز منفی بودن مقادیر مالی را نشان می‌دهد. اگر تمایلی به استفاده از این قالب‌بندی ندارید، می‌توانید نوع داده سلول را به General یا Number تبدیل کنید. شکل زیر عدد محاسبه شده بعد از تبدیل قالب سلول از قسمت Format به قالب General را نشان می‌دهد.

به عبارت دیگر، برای بازپس دادن وامی به مبلغ 1،000،000 تومان در 10 ماه با بهره سالیانه 18٪ باید ماهی 108،000 تومان پرداخت کرد. منفی بودن عدد به معنی بدهکاری است.

مطلب مرتبط: آموزش مرتب کردن سلول‌های یک ردیف Excel به ترتیب الفبا یا مقدار عددی

اصلاح ارجاع‌ها

اگر به طور مثال در دو سلول C3 و C4 دو عدد ۱۰ و ۱۲ و در سلول C5 فرمول جمع کردن این دو عدد (۱۲+۱۰) را وارد کرده باشید، به نظر شما در صورتی که یک ردیف دیگر را بالای ردیف شماره ۳ درج کنیم چه اتفاقی خواهد افتاد؟ در این حالت C3 و C4 به ترتیب به C4 و C5 تبدیل خواهند شد. آیا فرمولی که در C5 وارد شده بود (که اکنون در C6 قرار خواهد داشت) دچار مشکل خواهد شد؟

شکل زیر وضعیت را بعد از درج ردیفی در بالای ردیف شماره ۳ نشان می‌دهد.

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

بسیاری از اشکالاتی که در طراحی کاربرگ‌ها رخ می‌دهد به خاطر صحیح نبودن ارجاع‌ها است. در ارجاع دادن دقت کنید و در صورتی که نتایج صحیح بود درستی ارجاع‌ها را بررسی کنید.

اکنون وضعیت دیگری را در نظر بگیرید؛ فرض کنید سلول C4 را انتخاب کرده و آن را Cut کرده و در سلول دیگری مانند D4 را انتخاب کرده و Paste کنید. به این ترتیب محتوای سلول C4 به D4 منتقل می‌شود. فکر می‌کنید در این حالت چه مسئله‌ای برای فرمول C6 به وجود آید؟ شکل زیر وضعیت را نشان می‌دهد.

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

مطلب مرتبط: چگونه خیلی سریع تمام سلول‌های خالی در اکسل را پاک کنیم؟

مرور ارجاع‌ها

یک راه ساده برای مرور ارجاع‌ها وجود دارد: روی سلول حاوی فرمول دبل کلیک کنید تا در حالت ویرایشی قرار گیرد. در این حالت تمام ارجاع‌ها با کادرهای رنگی مشخص می‌شوند. شکل زیر مثالی از این مورد است.

کپی کردن فرمول‌ها

فرض کنید جدولی از مقادیر مانند زیر در اختیار داریم:

اکنون باید فرمولی برای سلول‌های ستون E بنویسیم که بهای کل ردیف‌ها را محاسبه کند. فرمول مناسب برای E3 به صورت C3+D3= است، که شکل زیر نشان‌دهنده آن است.

پس از این فرمول، سایر ردیف‌ها را چگونه خواهید نوشت؟ نوشتن فرمول برای تک تک ردیف‌ها سخت و وقت‌گیر است و احتمال اشتباه دارد؛ و احتمال اشتباه دارد؛ اگر هم لازم باشد که فرمول را تغییر دهید، باید تغییر را در تک تک آنها تکرار کنید. اگر جدول چند هزار ردیف داشته باشد، این کار عملاً ممکن نخواهد بود. برای رفع این مشکل قابلیت جالبی در Excel قرار داده شده است. سلولی را که فرمول آن نوشته شده است، انتخاب کرده، آن را کپی و در سلول‌های دیگر Paste کنید. راه دیگر برای انجام این کار این است که فرمول را در سلول E3 نوشته و سپس با استفاده از اشاره‌گر موس سلول را از قسمتی که علامت + ایجاد می‌شود گرفته و به سمت پایین و تا مقابل سلول آخر بکشیم تا فرمول نوشته شده برای همه سطرها اعمال شود. شکل‌های زیر نحوه اعمال فرمول از یک سلول به سلول‌های دیگر را نشان می‌دهد.

همانطور که در شکل نیز دیده می‌شود، ارجاع‌های نسخه‌های کپی شده فرمول به طور خودکار اصلاح می‌شوند، به عنوان مثال اگر فرمولی به ستون سمت چپ خود ارجاع داده باشد، نسخه‌های کپی شده نیز به ستون چپ خودشان ارجاع می‌دهند نه به همان ستونی که فرمول اصلی ارجاع می‌داد.

به پایان این قسمت از آموزش فرمول نویسی در اکسل می‌رسیم، امیدواریم از این آموزش استفاده کافی را برده باشید، ما را در قسمت‌های دیگر از آموزش این نرم‌افزار کاربردی همراهی کنید.