۱۶ فرمول اکسل برای حل مشکلات روزمره (+ دانلود فیلم آموزش رایگان)

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

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

16 فرمولی که در این نوشته معرفی می‌کنیم، به دقت انتخاب شده‌اند تا هم ساده باشند و هم به افراد عادی کمک کنند تا مسائل پیچیده زندگی روزمره خود را به روش کارآمدی حل کنند. اگر معلمی هستید که سعی می‌کنید نمرات دانش آموزان را مرتب کنید، یا پدر/مادری هستید که می‌خواهید مخارج خانه را مدیریت کنید و یا زوج جوانی هستید که نخستین تسهیلات مسکن خود را دریافت کرده‌اید، در هر صورت فرمول‌های زیر برای شما مفید خواهند بود.

فرمول‌های مالی

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

PMT – Payment

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

برای استفاده از این فرمول به موارد زیر نیاز دارید:

• درصد نرخ بهره وام
• تعداد اقساط وام
• نقطه آغاز پرداخت اقساط
• ارزش آتی، اگر به هر دلیلی خواستید قبل از پایان سرسید وام، آن را تسویه کنید. (این گزینه اختیاری است)
• نوع وام (0 اگر پرداخت اقساط در انتهای هر ماه و 1 اگر پرداخت اقساط در ابتدای هر ماه انجام می‌گیرد (اختیاری).

این فرمول روش جالبی برای مقایسه وام‌های مختلف و مشاهده شیوه پرداخت اقساط ارائه می‌کند. کافی است یک برگه جدید اکسل را باز کنید و همه انواع وام و اطلاعات مربوط به آن‌ها را در آنجا فهرست کنید. سپس یک ستون پرداخت ایجاد کنید و از فرمول PMT استفاده کنید.

کافی است گوشه سمت راست و پایین سلول PMT را بگیرید و آن را به طرف پایین بکشید تا کل پرداخت‌ها برای همه اقساط وام در برگه مورد نظر فهرست شوند.

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

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

فرمول FV - ارزش آتی

فرمول بعدی که بررسی می‌کنیم در مواردی کارآمد خواهد بود که می‌خواهید پول خود را در یک حساب سپرده سرمایه‌گذاری کنید. با استفاده از تابع FV می‌توانید بدانید که ارزش پول شما در انتهای دوره سرمایه‌گذاری چقدر خواهد بود.

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

برای استفاده از این فرمول باید موارد زیر را بدانید:

• نرخ سود حساب سپرده
• تعداد دریافتی‌ها (یا سررسیدهای ماهانه سود)
• دریافتی هر دوره (معمولاً ماهانه)
• تراز آغازین کنونی (اختیاری)
• نوع دریافت سود یعنی 0 اگر دریافت در انتهای هر ماه است و 1 اگر دریافت در ابتدای هر ماه است (اختیاری)

در ادامه چند سپرده سرمایه‌گذاری بانک‌های مختلف را بر حسب اطلاعاتی که بانک‌ها در اختیار شما قرار می‌دهند محاسبه می‌کنیم. در مثال زیر فرض می‌کنیم 20000 دلار دارید که می‌خواهید در یک حساب سپرده سرمایه‌گذاری کنید.

همچنان که می‌بینید نرخ‌های سود در این مورد نیز به صورت اعشاری نمایش یافته‌اند (برای این منظور باید نرخ سودی که بانک اعلام می‌کند را بر 100 تقسیم کنید) و دریافتی‌ها صفر هستند، چون این نوع حساب‌های سپرده معمولاً با یک مبلغی آغاز می‌شوند و در انتهای دوره مبلغ مشخصی باز می‌گردانند. در ادامه شیوه نمایش مقایسه حساب‌های سپرده سرمایه‌گذاری هنگام استفاده از فرمول FV را می‌بینیم.

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

تابع‌های منطقی

امروزه اغلب بانک‌ها در بخش اینترنت بانک خود امکان دانلود فهرست تراکنش‌های بانکی را در طی بازه‌هایی در حد یک سال یا بیشتر در قالب csv ارائه می‌کنند. این قالب برای تحلیل مخارجتان با استفاده از اکسل عالی است؛ اما گاهی اوقات داده‌هایی که از بانک دریافت می‌کنید، کاملاً نامرتب به نظر می‌رسند. با استفاده از تابع‌های منطقی می‌توان مخارج سنگین‌تر را فیلتر کرد.

بانک در بهترین حالت، تراکنش‌های شما را بر حسب نوع آن‌ها دسته‌بندی می‌کند. در برخی موارد شما ممکن است از چند حساب برای مصارف مختلف استفاده کنید و بدین ترتیب مخارج خود را دسته‌بندی کنید. برای نمونه همه صورت‌حساب‌های رستوران‌ها در فهرست زیر به صورت «غذای بیرون» (DiningOut) برچسب خورده‌اند.

بدین ترتیب امکان استفاده از تابع‌های منطقی برای فیلتر کردن مواردی که در یک رستوران غذا خورده‌اید و مبلغی بیش از 20 دلار هزینه کرده‌اید، آسان‌تر خواهد بود.

برای انجام این کار باید یک تابع منطقی در یک ستون جدید ایجاد کنید و تعیین کنید که همه تراکنش‌هایی که به صورت «غذای بیرون» هستند و مبلغ تراکنش 20-< است را انتخاب کند. دلیل این که چرا مبلغ بزرگ‌تر از 20- را انتخاب کردیم این است که تراکنش‌های خروجی به صورت اعداد منفی نمایش می‌یابند. نتیجه کار چیزی شبیه تصویر زیر است:

استفاده همزمان از دستورات IF و AND تا حدودی گیج‌کننده است؛ اما اگر کمی به آن فکر کنید متوجه می‌شوید که معنی دقیق آن چیست. عبارت IF مقدار هزینه (C2) را در صورتی که عبارت AND صحیح باشد نشان می‌دهد. عبارت AND بررسی می‌کند که آیا دسته مربوطه به صورت «غذای بیرون» است یا نه و همزمان تراکنش بالاتر از 20 دلار بوده است یا نه.

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

معنی دار کردن فهرست‌ها

فهرست‌ها بخش بزرگی از زندگی روزمره هستند. اگر مخارج یک خانوار را مدیریت می‌کنید، مرتباً از فهرست‌ها استفاده می‌کنید.

ممکن است در انجمن اولیا و مربیان عضو باشید و فهرست بلندبالایی از کمک‌های اهدایی انجمن برایتان ارسال شود. بدین ترتیب باید مشخص کنید که کدام اعضای انجمن به هر یک از پروژه‌های مدرسه کمک کرده است و میزان کمک چقدر بوده است. یا شاید مشغول یک کسب‌وکار خانوادگی هستید و مسئول پرداخت حقوق چند کارمند هستید و باید سهم هر یک از شرکا را در میزان هزینه‌ها محاسبه کنید.

COUNT و COUNTIF

فهرستی که استفاده می‌کنید هر چیزی که باشد در هر صورت اکسل می‌تواند به سازماندهی و مرتب‌سازی مقادیر فهرست‌ها کمک کند. برای مثال مورد انجمن اولیا و مربیان را بررسی می‌کنیم. در زیر تصویری از فهرست مبالغ کمک اعضای انجمن را می‌بینید.

اگر بخواهید ببینید که نام یک نفر چند بار در این فهرست نمایش یافته است، می‌توانید یک فرمول Count را با فرمول IF ترکیب کنید. بدین ترتیب ابتدا یک ستون ایجاد می‌کنیم که آیا فردی به نام «Michelle» در فهرست وجود دارد یا نه. اگر این نام در فهرست باشد مقدار 1 در سلول مربوطه نمایش می‌یابد و در غیر این صورت یک فاصله خالی « » نمایش خواهد یافت.

سپس یک ستون دیگر ایجاد کنید که نشان دهد نام «Michelle Johnson» چند بار در فهرست تکرار شده است.

بدین ترتیب تعداد خانه‌های ستون E که مقدارشان 1 بوده است، در این جا نمایش می‌یابد.

این ساده‌ترین روش برای این نوع کارها محسوب می‌شود؛ اما به دو مرحله نیاز دارد.

اگر نیازی به استفاده از تابع‌های پیشرفته‌تر نمی‌بینید، می‌توانید از یکی از تابع‌های ترکیبی مختلف IF مانند SUMIF، COUNTIF یا AVERAGEIF استفاده کنید. بدین ترتیب فقط زمانی آن تابع (COUNT، SUM یا AVERAGE) اجرا می‌شود که یک شرط منطقی برقرار باشد. نحوه عملکرد آن در مثال‌های فوق را در ادامه توضیح داده‌ایم.

این فرمول به ستون A نگاه می‌کند که شامل اسامی همه افرادی است که کمک کرده‌اند و اگر سلول درون محدوده با معیار تعریف شده مطابقت داشته باشد، در این صورت یک واحد به شمارش می‌افزاید. بدین ترتیب شمار همه نام‌هایی که «Michelle Johnson» هستند در یک گام به دست می‌آید.

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

تابع LEN

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

یک روش جالب برای استفاده از این تابع در مثال فوق می‌تواند برای هایلایت کردن اسامی کسانی باشد که مبلغی بالاتر از 1000 دلار کمک کرده‌اند. این کار از طریق شمارش تعداد ارقام ستون مبالغ کمک، امکان‌پذیر می‌شود.

همچنین در صورت تمایل می‌توان همه سلول‌هایی که در ستون مبالغ کمک هستند را هایلایت کرد. به برگه Home در منو بروید و در نوار ابزار بر روی Conditional Formatting کلیک کنید. سپس گزینه Use a formula to determine which cells to format را انتخاب کنید.

در بخش Format values where this formula is true: محدوده‌ای که می‌خواهید تابع LEN خروجی خود را در آن نمایش دهد انتخاب کنید.

در این مثال اگر شرط برابر با “>3” باشد در این صورت هر مبلغی بیشتر از 1000 دلار در یک قالب‌بندی خاص نمایش می‌یابد. می‌بایست بر روی دکمه Format… کلیک کرده و نوع فرمت بندی خاصی را که می‌خواهید انتخاب کنید.

ممکن است متوجه شوید که محدوده به صورت “$E2:$E11” تعریف شده است و نه “$E$2:$E$11”. بنا به برخی دلایل وقتی محدوده را انتخاب می‌کنید به طور پیش‌فرض در حالت اول است که صحیح نیست. شما باید از آدرس‌دهی نسبی که در شکل فوق نیز نمایش یافته است استفاده کنید. سپس فرمت بندی شرطی شما بر اساس شرطی که در محدوده دوم تعیین شده عمل می‌کند.

سازماندهی اطلاعات دانلود شده از بانک‌ها و مؤسسات مالی

گاهی اوقات وقتی اطلاعاتی را از مؤسسات مالی و تجاری مانند بانک، شرکت بیمه سلامت یا هر کسب‌وکار دیگری دانلود می‌کنید، فرمت داده‌های ورودی با نیازهای شما مطابقت ندارد.

فیلم آموزش رسم نمودار در اکسل – طراحی انواع نمودار و تنظیمات در فرادرس

کلیک کنید

برای مثال فرض کنید در داده‌هایی که بانک برای شما ارسال می‌کند، تاریخ به صورت قالب استاندارد نمایش می‌یابد.

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

RIGHT، LEFT، TEXT و CONCATENATE

با استفاده از فرمول RIGHT می‌توانید مقدار سال را از ستون مربوطه استخراج کنید

تابع فوق به اکسل می‌گوید که متن موجود در ستون D را گرفته و چهار کاراکتر از سمت راست آن را انتخاب کند. تابع CONCATENATE آن چهار رقم و عنوان پرداختی از ستون بعدی را کنار هم می‌گذارد.

به خاطر داشته باشید که اگر بخواهید متنی را از یک تاریخ استخراج کنید باید ابتدا با استفاده از تابع «=TEXT(D2,”mm/dd/yyyy”)» آن را به فرمت متنی تبدیل کنید. سپس می‌توانید از تابع Right برای استخراج سال استفاده کنید.

تابع CONCATENATE هنگامی که می‌خواهید متن‌هایی را از ستون‌های مختلف گرفته و آن‌ها را به یک رشته واحد ترکیب کنید کاملاً کارآمد است. برای مثال می‌توانید نام، نام خانوادگی، نشانی خیابان، شهر و استان را از فیلدهای مختلف گرفته و کل آدرس یک نفر را در یک ستون وارد کنید.

انتخاب نام‌های تصادفی

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

با استفاده از تابع RANDBETWEEN می‌توانید به طور تصادفی یک عدد بین محدوده‌ای از اعدادی که تعیین شده است را انتخاب کنید.

دو مقداری که نیاز دارید کمترین و بزرگ‌ترین عدد هستند که باید آخرین عددی باشد که برای محدوده اسامی افراد تعیین کرده‌اید.

زمانی که کلید اینتر را بزنید این تابع به طور تصادفی یکی از اعداد درون محدوده را انتخاب می‌کند.

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

استفاده از اکسل برای مسائل روزمره

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

اگر این نوشته ایده‌های خلاقانه‌ای برای استفاده از اکسل در جهت حل مسائل روزمره زندگی به شما داده است، یا اگر تاکنون از اکسل به چنین روش‌های خلاقانه‌ای استفاده کرده‌اید، چه خوب است که ایده‌ها و دیدگاه‌های خود را در بخش نظرات در ادامه با ما و دیگر خوانندگان فرادرس در میان بگذارید. اگر این مطلب مورد توجه شما واقع شده است، احتمالاً آموزش‌های زیر نیز برای شما مفید خواهند بود:

• آموزش اکسل (Microsoft Office Excel 2016)
• آموزش ترفندهای کاربردی در اکسل
• آموزش ابزارهای کاربردی اکسل
• آموزش استفاده از توابع و فرمول نویسی در اکسل
• آموزش محاسبات آماری در اکسل

==