کاربردی ترین فرمول های اکسل :اکسل ابزاری ارزشمند است، بنابراین برای سرعت بخشیدن به قالب بندی صفحه گسترده و تبدیل شدن به یک استاد اکسل، باید کاربردی ترین فرمول های اکسل را بشناسید. تا به حال با بخش‌های مختلف و ویژگی‌های مفیدی از برنامه اکسل آشنا شده‌اید و اکنون وقت آن است که مفیدترین فرمول‌ها را در نرم‌افزار اکش بشناسید. اکسل ابزاری ارزشمند است زیرا می‌تواند محسبات ریاضی زیادی را به صورت خودکار انجام دهد. شرح مفیدترین فرمول‌ها به شکل زیر است:
جمع (SUM):
متوسط(AVERAGE) از کاربردی ترین فرمول های اکسل
SUM به شما این امکان را می‌دهد که هر تعداد ستون یا ردیف را با انتخاب آن یا تایپ کردن آنها جمع کنید، به عنوان مثال:
= SUM (A1: A8
تمام مقادیر را بین A1 و A8 و غیره جمع می کند.
COUNT تعداد سلول‌های یک آرایه را که مقدار عددی در آنها وجود دارد را، محاسبه می‌کند. این برای تعیین اینکه آیا کسی پول پرداخت کرده است یا در سایر شرایط پایگاه داده مفید خواهد بود.
AVERAGE همانطور که از اسمش پیداست میانگین اعدادی را که وارد می‌کنید می‌گیرد.
ضرب(Multiplication) در اکسل
برای انجام فرمول ضرب در اکسل، سلول‌هایی را که در قالب ضرب می‌کنید وارد کنید :
= A1 * B1
در این فرمول از ستاره برای ضرب سلول A1 در سلول B1 استفاده میشود. به عنوان مثال، اگر A1 10 و B1 6 بود ، = A1 * B1 مقدار 60 را نشان می‌دهد.
برای ضرب دو یا چند مقدار در صفحه گسترده اکسل، یک سلول خالی را انتخاب کنید. سپس، مقادیر یا سلول‌هایی را که می‌خواهید در قالب ضرب کنی ، وارد کنید :
= A1 * B1 * C1 … و
ستاره به طور موثر هر مقدار موجود در فرمول را ضرب می‌کند.
تقسیم(Division)
برای انجام فرمول تقسیم در اکسل، سلول‌هایی را که می‌خواهید تقسیم کنید وارد کنید:
= A1 / B1
این فرمول برای تقسیم سلول A1 به سلول B1 از یک برش رو به جلو، “/” استفاده می‌کند.به عنوان مثال، اگر A1 5 و B1 10 باشد، = A1 / B1 مقدار اعشاری 0.5 را نشان می‌دهد.
تقسیم در اکسل یکی از ساده‌ترین کارهایی است که میتوانید انجام دهید. برای انجام این کار، یک سلول خالی را انتخاب کنید، یک علامت برابر با “=” وارد کنید و آن را با دو مقداری (یا بیشتر) که می‌خواهید با یک برش رو به جلو، “/” در میان تقسیم کنید. نتیجه باید در قالب زیر باشد:
= B2 / A2
عملکرد Not
تا به حال در یک لیست واقعاً طولانی از داده‌ها جستجو کرده‌اید و آرزو کنید که بتوانید تمام ورودی‌هایی را که اعمال نمی‌شوند حذف کنید؟ به عنوان مثال، من می‌خواهم همه چیز در مورد انرژی جایگزین به جز هسته ای (یا نه) موجود را ببینم.
در اکسل، این یک کار آسان است. لیستی از چند شرکتی که دارای منابع مختلف انرژی را ارائه می‌دهند تهیه کنید و اینکه این منابع چه هستند (ستون های A ، B ، C ؛ از ردیف 4 شروع می شود). فرمول زیر را در سلول C4 وارد کنید:
=NOT(B4=”هسته‌ای“).
سپس فرمول را از C4 به C5: C28 کپی کنید.
اگر پاسخ درست باشد، منبع انرژی هسته‌ای نیست. اگر پاسخ نادرست باشد، منبع انرژی هسته‌ای است. بله، پاسخ معکوس است و شما ممکن است بلافاصله نیازی به این عملکرد نبینید، اما اگر کاربر مشتاق اکسل باشید، دلایل زیادی برای استفاده از این فرمول در آینده خواهید یافت.
تابع شرطی (IF) یکی از کاربردی ترین فرمول های اکسل
عبارات IF در بسیاری از موارد بسیار مفید هستند و این عملکرد به شما امکان می‌دهد در صورت معتبر یا نادرست بودن متن، متن را خارج کنید. به عنوان مثال ، می توانید:
= IF (A1> A2 ، “GOOD” ، “BAD”)
در این حالت A1> A2 وجود دارد ، “GOOD” در صورت درست بودن، خروجی و “BAD در صورت نادرست بودن، خروجی است.
SUMIF ، COUNTIF ، AVERAGEIF
این توابع ترکیبی از توابع SUM ، COUNT ، AVERAGE با پیوست به عبارات IF هستند. ساختار همه این توابع به یک شکل انجام میشود:
= FUNCTION (دامنه ، معیارها ، دامنه عملکرد)
بنابراین در SUM می توانید بنویسید:
SUM (A1: A15، “GOOD”، B1: B13)
اگر مقادیر A1 تا A15 همه خوب باشند، این B1 تا B13 را اضافه می‌کند.
حداکثر(MAX) و حداقل(MIN)
این توابع بسیار ساده هستن ، کافیست ستون یا ردیف اعدادی که می‌خواهید را انتخاب کنید تا تایپ شوند و بسته به عملکردی که استفاده می‌کنید، MAX یا MIN تولید میشود. به عنوان مثال
= MAX (A1: A10)
حداکثر مقدار عددی را در آن ردیف‌ها تولید می‌کند.
AND
این تابع یک عملکرد منطقی دیگر در اکسل است و بررسی می‌کند که برخی موارد درست هستند یا نادرست. برای مثال ، اگر AND A1 خوب باشد و مقدار B2 بزرگتر از 10 باشد:
= AND (A1 = “GOOD”، B2> 10)
شما میتوانید مقادیر بیشتر از دو را نیز به راحتی با اضافه کردن ویرگول دیگر بررسی کنید.
انتخاب (choose)
تابع CHOOSE برای تجزیه و تحلیل سناریو در مدل سازی مالی عالی است. به شما امکان می‌دهد بین تعداد مشخصی گزینه انتخاب کنید و چیزی را که انتخاب کرده اید بازیابی کنید. به عنوان مثال، تصور کنید برای رشد درآمد سال آینده سه فرضیه متفاوت دارید: 5٪ ، 12٪ و 18٪. اگر به اکسل بگویید گزینه شماره 2 را می‌خواهید، با استفاده از فرمول CHOOSE میتوانید 12٪ را بازیابی کنید.
= CHOOSE (انتخاب ، گزینه 1 ، گزینه 2 ، گزینه 3)
VLOOKUP
این تابع به شما امکان را می‌دهد که چیزی را در ستون سمت چپ صفحه گسترده جستجو کنید و آن را به عنوان یک مقدار بازیابی کنید. مثالی از نحوه استفاده از این تابع به شرح زیر است:
= VLOOKUP (مقدار جستجو ، جدول در حال جستجو ، شماره فهرست ، شناسه مرتب سازی)
CONCATENATE
الحاق نه تنها یک کلمه خارق العاده برای گفتن است، بلکه در صورت نیاز به ترکیب داده‌ها در یک سلول عملکرد مفیدی نیز است. برای مثال بگویید که به ترتیب در سلول های A1 و A2 نام و نام خانوادگی داشته اید. شما
= CONCATENATE (A1 ، “” ، B2)
را تایپ می‌کنید، تا نام‌ها را در یک سلول ترکیب کند و “” بین آنها فاصله می دهد.
PROPER
PROPER زمانی مفید است که پایگاه داده شما متن زیادی با قالب عجیب و غریب داشته باشد که به نظر می‌رسد در جای اشتباه قرار گرفته باشد.
تکرار (Rept) یکی از کاربردی ترین فرمول های اکسل
در اکسل، این ویژگی با عملکرد REPT اداره میشود. این فرمول خیلی کارآمد نیست زیرا باید کاراکتر را به فرمول اضافه کنید، سپس مشخص کنید که چند بار می‌خواهید آن شخصیت تکرار شود. این بدان معنی است که اگر عرض سلول افزایش یابد ، شخصیت تکرار نشده است و اگر عرض سلول کاهش یابد، شخصیت تکرار شده به سلول مجاور منتقل میشود.
= REPT (“*” ، 5)؛ = REPT (“-” ، 10) ، = REPT (“+” ، 12).
میتوانید هر کاراکتر را روی صفحه کلید به همراه نمادها تکرار کنید.
درصد(Percentage)
برای انجام فرمول درصد در اکسل، سلول‌هایی را که درصدی را برای آنها پیدا می‌کنید با قالب، = A1 / B1 وارد کنید. برای تبدیل مقدار اعشاری حاصل به یک درصد، سلول را برجسته کنید، روی Home کلیک کنید و از لیست کشویی اعداد “درصد” را انتخاب کنید.فرمول اکسل برای درصدها فی نفسه وجود ندارد، اما اکسل تبدیل مقدار هر سلول را به درصد آسان می‌کند، بنابراین خودتان در محاسبه و وارد کردن مجدد اعداد گیر نمی‌کنید.
لینک کردن برگه (Linking Worksheets)
شما ممکن است نیاز داشته باشید از داده‌های یک سلول و یا فرمول نوشته شده از سلولی در برگه جاری در برگه ای دیگر استفاده کنید. میتوانید با کمک این تابع و با تایپ فرمول زیر میتوانید به راحتی این کار را انجام دهید:
سلول مرجع!نام صفحه مرجع =
قالب بندی شرطی (CONDITIONAL FORMATTING) در اکسل
این تابع از نظر فنی یک فرمول نیست، اما ابزاری فوق العاده مفید است که درست در اکسل تعبیه شده است.اگر به خانه(home) بروید -> سبک ها Styles) (-> قالب بندی مشروط (CONDITIONAL FORMATTING)، میتوانید گزینه‌های زیادی را انتخاب کنید که در صورت صحت برخی موارد خروجی ایجاد کند. شما میتوانید بسیاری از این کارها را با فرمول‌هایی که قبلاً ذکر شد هم انجام دهید، اما چرا اجازه نمی‌دهید اکسل کارهای سخت را انجام دهد.
INDEX + MATCH
این ترکیب توابع به شما امکان می‌دهد تا محدودیت‌های آزار دهنده VLOOKUP را دور بزنید. با ترکیب این توابع مانند این
، = INDEX (لیست مقادیر ، MATCH (آنچه می خواهید جستجو کنید ، ستون جستجو ، مرتب سازی شناسه)) ،
میتوانید یک صفحه گسترده را برای مقادیر جستجو کنید به جای اینکه مجبور شوید فقط ستون سمت چپ را جستجو کنید .
XNPV و XIRR
اگر تحلیلگری هستید که در بانکداری سرمایه گذاری، تحقیقات سهام، برنامه ریزی و تجزیه و تحلیل مالی (FP&A) یا هر زمینه مالی دیگری که نیاز به تخفیف در جریان نقدی دارد، کار می‌کند ، این فرمول ها یک نجات دهنده هستند:
= XNPV (نرخ تخفیف ، جریان های نقدی ، تاریخ ها)
به زبان ساده، XNPV و XIRR به شما این امکان را می‌دهند که تاریخ‌های مشخصی را برای هر جریان نقدی منفرد که تخفیف دارد اعمال کنید. مشکلی که در فرمولهای NPV و IRR اکسل وجود دارد این است که فرض می‌کنند بازه‌های زمانی بین جریان نقدی برابر هستند. به طور معمول، به عنوان یک تحلیلگر، موقعیت‌هایی خواهید داشت که جریان‌های نقدی به طور یکنواخت تنظیم نمی‌شوند و این فرمول راه حل آن است.
PMT و IPMT
اگر در بانکداری تجاری، املاک و مستغلات، FP&A یا هر موقعیت دیگر، تحلیلگر مالی هستید که با برنامه‌های بدهی سروکار دارد، باید این دو فرمول را دقیق یاد بگیرید:
فرمول PMT ارزش پرداخت برابر را در طول عمر وام ،به شما می‌دهد. میتوانید از آن به همراه IPMT (که پرداخت سود برای همان نوع وام را به شما می‌گوید) استفاده کنید، سپس پرداخت اصل و سود را جدا کنید.
= PMT (نرخ بهره ، # دوره ، ارزش فعلی)
LEN و TRIM
فرمول ها: = LEN (متن) و = TRIM (متن)
این فرمول‌ها کمی کمتر رایج هستند، اما مطمئناً فرمولهای بسیار پیچیده اما کاربردی هستند. این برنامه‌ها برای تحلیل گران مالی که نیاز به تنظیم و دستکاری مقدار زیادی از داده‌ها دارند بسیار مناسب است. متأسفانه، داده‌هایی که به دست می‌آوریم همیشه کاملاً منظم نیستند و گاهی اوقات ممکن است مواردی مانند فضاهای اضافی در ابتدا یا انت‌های سلول‌ها وجود داشته باشد
تابع (Clean):
از این تابع می‌توان برای حذف شکستگی خطوط در هر سلول استفاده کرد، به جای تابع trim
توابع CELL ، LEFT ، MID و RIGHT
این توابع پیشرفته اکسل را می‌توان با هم ترکیب کرد و فرمولهای بسیار پیشرفته و پیچیده‌ای را برای استفاده ایجاد کرد. عملکرد CELL می‌تواند اطلاعات مختلفی را در مورد محتویات سلول (مانند نام ، محل ، سطر ، ستون و موارد دیگر) بازگرداند. عملکرد LEFT می‌تواند متن را از ابتدای سلول (چپ به راست) ، MID متن را از هر نقطه شروع سلول (چپ به راست) و RIGHT متن را از انت‌های سلول (راست به چپ) برمی‌گرداند.
عملکرد RAND در اکسل
عملکرد RAND بسیار ساده است و به طور سنتی برای تجزیه و تحلیل آماری، رمزنگاری، بازی، قمار و نظریه احتمالات، در میان ده‌ها مورد دیگر، استفاده میشود. در اکسل، تابع RAND یک عدد تصادفی بین 0 تا 1 ایجاد می‌کند. با این حال، هر زمان که داده‌های جدیدی وارد می‌کنید و کلید Enter را می‌فشارید، لیست اعداد تصادفی که ایجاد کرده‌اید تغییر می‌کند. اگر می‌خواهید لیست اعداد تصادفی خود را حفظ کنید، باید سلول‌ها را به عنوان مقادیر قالب بندی کنید. در قسمت‌های بعدی بیشتر با آموزش نرم افزار اکسل با شما خواهیم بود.