اکسل فقط برای تجارت نیست. در اینجا چندین فرمول مایکروسافت اکسل وجود دارد که به شما در حل مشکلات پیچیده روزانه کمک می کند.
بسیاری از مردم مایکروسافت اکسل را به عنوان ابزاری می بینند که فقط در تجارت مفید است. حقیقت این است که راه های زیادی وجود دارد که می تواند برای شما در امور خانه نیز مفید باشد. کلید یافتن کاربردهای اکسل در زندگی روزمره، انتخاب فرمول های مناسب برای حل مشکلات است.
چه در حال خرید وام خودروی جدید هستید، چه بخواهید بفهمید کدام سرمایه گذاری که اخیرا انجام داده اید برای برای شما بهترین است، یا اگر فقط می خواهید حساب بانکی خود را بررسی کنید، اکسل ابزار قدرتمندی است که می تواند به شما کمک کند.
ما برای شما 15 فرمول اکسل را که هم ساده هستند و هم در عمل قدرتمند هستند و میتواند در مشکلات پیچیده به کمک شما بیایند را انتخاب کردیم که در این مقاله در مورد آن ها برای شما توضیح دهیم.
فرمول های مالی
در حال خرید یک خانه جدید هستید و سردرگم از شرایط بهره وام مسکن؟ آیا به دنبال یک ماشین جدید هستید و با شرایط وام خودرو که فروشنده مدام به شما می‌گوید گیج می‌شوید؟
ترس نداشته باش قبل از گرفتن وام، تحقیق خود را به همراه اکسل در کنار خود انجام دهید!
1. PMT—پرداخت
هر زمان که شرایط وام را با هم مقایسه می کنید و می خواهید به سرعت پرداخت ماهانه واقعی خود را با توجه به شرایط مختلف محاسبه کنید، از فرمول قدرتمند (و ساده) PMT استفاده کنید.
در این مقاله چیزی است که برای استفاده از این فرمول نیاز دارید:
- نرخ بهره وام
- مدت وام (چند پرداخت؟)
- زمان شروع وام
- ارزش آتی، اگر به دلایلی وام قبل از رسیدن به صفر پرداخت شده در نظر گرفته شود (اختیاری)
- نوع وام — 0 در صورت سررسید پرداخت در پایان هر ماه، یا 1 در صورت سررسید در ابتدا (اختیاری)
در اینجا یک روش جالب برای مقایسه سریع انواع وام ها وجود دارد تا ببینید پرداخت های شما چگونه خواهد بود. یک برگه اکسل ایجاد کنید که هر وام احتمالی و تمام اطلاعات موجود در مورد آنها را فهرست می کند. سپس، یک ستون “پرداخت ها” ایجاد کنید و از فرمول PMT استفاده کنید.
فقط گوشه سمت راست پایین سلول PMT را که ایجاد کرده‌اید بگیرید، و آن را به پایین بکشید تا مجموع پرداخت را برای تمام شرایط وام ذکر شده در برگه محاسبه کند. ویژگی autofill اکسل یکی از ویژگی هایی است که با این ترفندها بسیار استفاده خواهید کرد.
اکنون می توانید پرداخت های ماهانه برای انواع مختلف وام را مقایسه کنید.
(یک تشکر بسیار بزرگ از مارک جونز (redtexture@ در توییتر) که به این نکته اشاره کرد که برای فرمول های PMT و FV، باید در استفاده از همان دوره بسیار مراقب باشید — در این مورد استفاده از پرداخت های ماهانه مستلزم تقسیم مدت بهره بر 12 ماه است)
به همین دلیل است که خوانندگان ما بسیار عالی هستند. با تشکر برای کمک به این اصلاح مارک!
2. FV — ارزش آینده
فرمول بعدی زمانی مفید است که شما به دنبال سرمایه گذاری مقداری پول در چیزی مانند گواهی سپرده (CD) هستید و می خواهید بدانید که در پایان دوره چقدر ارزش دارد.
برای استفاده از فرمول FV باید بدانید:
- نرخ بهره وام
- تعداد پرداخت ها (یا مدت سرمایه گذاری بر حسب ماه)
- پرداخت برای هر دوره (معمولا ماهانه)
- موجودی اولیه فعلی (اختیاری)
- نوع وام — 0 در صورت سررسید پرداخت در پایان هر ماه، یا 1 در صورت سررسید در ابتدای (اختیاری)
بنابراین بیایید چندین CD را با استفاده از عباراتی که از اطلاعاتی که بانک ها به شما داده اند، مقایسه کنیم. در مثال زیر، فرض کنید 20000 دلار وراثت برای سرمایه گذاری در یک CD دارید.
نرخ های بهره مجدداً در قالب اعشاری نشان داده می شوند (نرخ بهره ای که بانک به شما داده است را بگیرید و بر 100 تقسیم کنید). پرداخت‌ها صفر هستند، زیرا CD ها معمولاً بر اساس ارزش اولیه و ارزش آتی پرداخت می شوند. وقتی از فرمول FV برای هر CD استفاده می کنید، مقایسه به این صورت است.
بدون شک، CD با سود بیشتر در مدت زمان طولانی تری سود بیشتری دارد. تنها ایرادش این است که سه سال تمام نمی توانید به هیچ کدام از پول هایتان دست بزنید، اما طبیعت سرمایه گذاری همین است!
3-4.فرمول اکسل: فرمول های منطقی — IF و AND
این روزها اکثر بانک ها به شما این امکان را می دهند که تقریباً یک سال تراکنش های بانکی را با فرمتی مانند CSV دانلود کنید. این یک قالب عالی برای تجزیه و تحلیل هزینه های شما با استفاده از اکسل است، اما گاهی اوقات داده هایی که از بانک ها دریافت می کنید بسیار نامرتب است.
استفاده از فرمول های منطقی یک راه عالی برای تشخیص هزینه های بیش از حد است.
در حالت ایده‌آل، بانک یا به طور خودکار مخارج شما را دسته بندی می کند یا شما حساب خود را طوری تنظیم کرده اید که چیزها در دسته های مخارج قرار گیرند. به عنوان مثال، هر رستورانی که می رویم با برچسب DiningOut برچسب گذاری می شود.
این کار استفاده از یک فرمول منطقی را برای شناسایی هر زمان که برای غذا خوردن بیرون رفته ایم و بیش از 20 دلار خرج کرده ایم، آسان می کند.
برای انجام این کار، فقط یک فرمول منطقی در یک ستون جدید ایجاد کنید و به دنبال هر مقداری بگردید که در آن ستون دسته “DiningOut” و ستون تراکنش بزرگتر از -20 دلار باشد.
توجه: مقایسه زیر “<” را نشان می دهد، کمتر از، زیرا مقادیر در ستون C همه منفی هستند.
در اینجا به نظر می رسد:
استفاده از IF و AND با هم در یک فرمول کمی دشوار به نظر می رسد، اما در واقع بسیار ساده است.
اگر عبارت AND درست باشد، عبارت IF مقدار دلار (C2) یا اگر درست نباشد، FALSE را نشان می دهد. بیانیه AND بررسی می کند که آیا دسته “DiningOut” است و تراکنش بیشتر از 20 دلار است.
شما اطلاعات مورد نیازتان را دارید! بدون نیاز به بررسی دستی تمام آن تراکنش ها، اکنون دقیقاً زمان هایی را می دانید که در یک دسته خاص بیش از حد خرج کرده اید.
ایجاد حس لیست ها
فهرست ها بخش بزرگی از زندگی روزمره هستند. اگر شما یک خانواده را مدیریت می کنید، دائماً از لیست ها استفاده می کنید. اکسل ابزارهای بسیار قدرتمندی برای کارآمد بودن با چک لیست ها و همچنین انواع دیگر قالب های لیست دارد.
5-6.فرمول اکسل: COUNT و COUNTIF
اکسل می تواند به شما در سازماندهی سریع و مرتب کردن مقادیر یک لیست کمک کند. بیایید مثال PTC را در نظر بگیریم. در اینجا لیستی از کمک های مالی اعضای انجمن آمده است.
می‌خواهیم ببینیم نام یک فرد چند بار در لیست نمایش داده می‌شود. برای این کار می توانید فرمول COUNT را با فرمول IF ترکیب کنید. ابتدا یک ستون ایجاد کنید تا بررسی کنید که آیا آن شخص میشل است یا خیر. در صورت درست بودن این فرمول از یک دستور IF برای پر کردن سلول با یک “1” استفاده می کند.
سپس، ستون دیگری ایجاد کنید که تعداد دفعاتی که میشل جانسون را در لیست پیدا کرده اید، محاسبه می کند.
این به شما تعداد هر مکان در ستون E را می دهد که در آن 1 به جای خالی وجود دارد.
بنابراین، این ساده ترین راه برای انجام این نوع کارها است، اما نیاز به دو مرحله دارد.
6-8. SUMIF، COUNTIF، AVERAGEIF
اگر مشکلی ندارید از فرمول کمی پیشرفته تر استفاده کنید، ممکن است یکی از فرمول‌های ترکیبی «IF» مانند SUMIF، COUNTIF یا AVERAGEIF را در نظر بگیرید. اگر شرط منطقی درست باشد، اینها به شما امکان می دهند فرمول (COUNT، SUM یا AVERAGE) را انجام دهید. در اینجا نحوه کار با استفاده از مثال بالا آمده است.
این فرمول به ستون A نگاه می کند، که شامل همه نام های اهدا کننده است، و اگر سلول در محدوده با معیارهای نقل قول مطابقت داشته باشد، یک عدد به حساب می آید. این به شما تعداد دفعاتی را می دهد که نام اهداکننده در یک مرحله با “میشل جانسون” برابری می کند.
این بسیار سریعتر از استفاده از دو ستون است، اما کمی پیچیده است – بنابراین از رویکردی استفاده کنید که برای موقعیت شما بهترین کار را دارد. فرمول‌های SUMIF و AVERAGEIF به همین شکل عمل می کنند، فقط با نتایج ریاضی متفاوت. استفاده از SUMIF در این مثال، اگر به جای آن از آن استفاده کنید، کل دلارهای اهدایی میشل جانسون را به شما می دهد.
9.فرمول اکسل: LEN
فرمول دیگری که گاهی اوقات می توانید خلاقانه از آن استفاده کنید، فرمول LEN است. این فرمول یکی از بسیاری از فرمول های متن اکسل است که به شما می گوید چند کاراکتر در یک رشته متن وجود دارد. یکی از راه‌های جالب استفاده از این مورد در مثال بالا، برجسته کردن اهداکنندگانی است که بیش از 1000 دلار با شمارش تعداد ارقام در ستون اهدا کمک کرده اند.
اگر طول عدد 4 یا بیشتر باشد، حداقل 1000 دلار کمک مالی کردند.
اکنون می توانید قالب بندی اضافی اضافه کنید تا کار روی چشم ها آسان تر شود. برای انجام این کار، باید تمام سلول های ستون Donation را هایلایت کنید، تب Home را در منو انتخاب کنید و روی Conditional Formatting در نوار ابزار کلیک کنید.
سپس use a formula to determine which cells to format را انتخاب کنید. محدوده را در مقادیر Format values where this formula is tru تنظیم کنید: به ستون/محدوده ای که تمام خروجی های فرمول LEN شما نمایش داده می شود.
در این مثال، اگر شرط “>3” را ایجاد کنید، هر چیزی که بیش از 1000 دلار باشد، قالب بندی ویژه را دریافت می کند. فراموش نکنید که روی دکمه Format… کلیک کنید و نوع قالب بندی خاصی را برای این موارد انتخاب کنید.
همچنین، یک یادداشت سریع. متوجه خواهید شد که محدوده من به عنوان “E2:$E11$” تعریف شده است، نه “E$2:$E$11$”. وقتی محدوده را انتخاب می‌کنید، به طور پیش فرض روی حالت اول قرار می گیرد که کار نمی کند.
همانطور که در تصویر بالا نشان داده شده است باید از آدرس دهی نسبی استفاده کنید. سپس، قالب بندی شرطی شما بر اساس شرایط محدوده دوم کار خواهد کرد.
فرمول اکسل: ساماندهی دانلودهای بانکی و مالی
گاهی اوقات، زمانی که اطلاعاتی را از کسب و کارها دانلود می کنید — خواه بانک شما باشد یا شرکت بیمه درمانی شما، قالب داده های دریافتی همیشه با آنچه شما نیاز دارید مطابقت ندارد.
به عنوان مثال، فرض کنید که در داده های صادر شده از بانک شما، تاریخ در قالب استاندارد به شما داده شده است.
اگر می خواهید یک ستون جدید به ستون خود اضافه کنید که پیش گفتار آن سال است و شامل اطلاعات گیرنده پرداخت (برای اهداف مرتب سازی خودتان) است، استخراج اطلاعات از یک ستون واقعاً آسان است.
10-14. RIGHT، LEFT، TEXT و CNCATENATE
با استفاده از فرمول RIGHT می توانید سال را از متن آن ستون خارج کنید.
فرمول بالا به اکسل می گوید که متن ستون D را بگیرد و چهار کاراکتر را از سمت راست استخراج کند. فرمول CONCATENATE آن چهار رقم را با متن گیرنده وجه از ستون E کنار هم می‌آورد.
به خاطر داشته باشید که اگر می خواهید متنی را از تاریخ استخراج کنید، باید آن را با استفاده از فرمول “=TEXT(D2,mm/dd/yyyy)” به قالب متن (به جای تاریخ) تبدیل کنید. سپس می توانید از فرمول RIGHT برای بیرون کشیدن سال استفاده کنید.
اگر اطلاعات شما در سمت چپ باشد باید چه کاری انجام داد؟ خوب، در عوض از فرمول LEFT استفاده کنید و می توانید متن را از چپ به راست بکشید.
CONCATENATE واقعاً زمانی مفید است که متنی از دسته ای از ستون های مختلف داشته باشید که می خواهید آنها را در یک رشته طولانی کنار هم قرار دهید. می‌توانید در راهنمای ما درباره نحوه ترکیب ستون‌های اکسل اطلاعات بیشتری کسب کنید.
همچنین اگر می خواهید نحوه دستکاری کامل رشته ها را یاد بگیرید، چند راه برای جداسازی متن در اکسل وجود دارد.
انتخاب نام های تصادفی از یک کلاه
15.فرمول اکسل: RANDBETWEEN
آخرین فرمول سرگرم کننده، فرمولی است که اگر مجبور به انجام کاری مانند انتخاب نام از کلاه برای جشن کریسمس هستید، می توانید از آن استفاده کنید. آن کلاه و آن تکه های کاغذ را کنار بگذارید و در عوض لپ تاپ خود را بیرون بکشید و اکسل را راه اندازی کنید!
با استفاده از فرمول RANDBETWEEN، می توانید اکسل را به طور تصادفی از بین طیفی از اعدادی که مشخص کرده اید انتخاب کنید.
دو مقداری که باید استفاده کنید، پایین ترین و بالاترین اعداد هستند که باید در انتهای محدوده اعدادی که برای نام هر فرد اعمال کرده اید باشد.
پس از زدن کلید Enter، فرمول به طور تصادفی یکی از اعداد داخل محدوده را انتخاب می کند.
این تقریباً تا حد امکان تصادفی و ضد دستکاری است. بنابراین به جای انتخاب یک عدد از کلاه، به جای آن یک عدد از اکسل انتخاب کنید!
استفاده از اکسل برای مشکلات روزمره
همانطور که می بینید، اکسل فقط برای متخصصان حرفه ای در زمینه تجزیه و تحلیل داده ها نیست. هر کسی می تواند از فرمول های زیادی که در اکسل پیدا می کنید بهره مند شود. این فرمول ها را یاد بگیرید و می توانید شروع به حل مسائل واقعی در اکسل کنید.
یادگیری اکسل را متوقف نکنید. فهرست طولانی ای از فرمول ها و توابع اکسل وجود دارد که می توانید استفاده از آنها را بیاموزید، ممکن است ترفندهای کوچکی پیدا کنید که هرگز فکر نمی کردید اکسل بتواند انجام دهد.
