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

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

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

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

PMT – Payment

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

COUNT و COUNTIF

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

اگر بخواهید ببینید که نام یک نفر چند بار در این فهرست نمایش یافته است، می توانید یک فرمولCount را با فرمول IF ترکیب کنید. بدین ترتیب ابتدا یک ستون ایجاد می کنیم که آیا فردی به نام «Michelle» در فهرست وجود دارد یا نه. اگر این نام در فهرست باشد مقدار 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 می توانید مقدار سال را از ستون مربوطه استخراج کنید

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

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

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

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

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

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

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

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

نحوه ترسیم جدول در ورد word