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

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

فرمول CONCATENATE

این فرمول یکی از مهم ترین و کاربردی‌ترین فرمول های اکسل است که به کمک آن می توان متن، اعداد، تاریخ و … را در چند سلول متفاوت با یکدیگر ترکیب کرده و در یک سلول نمایش داد.

CONCATENATE (SELECT CELLS YOU WANT TO COMBINE)

فرمول LEN

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

LEN (SELECT CELL)

فرمول COUNTA

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

COUNTA (SELECT CELLS)

DAYS/ NETWORKDAYS

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

فرمول SUMIFS

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

SUMIFS (Sum_ range; Criteria_ range1; Criteria1; Criteria_ range2; Criteria2;…)

Sum_ range: یک یا چند سلول که میخواهیم با یکدیگر جمع بزنیم.

Criteria_ range1: انتخاب محدوده اول که باید به وسیله شرط اول مورد بررسی قرار گیرد.

Criteria1: اولین شرطی که می بایست بررسی شود.

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

فرمول AVERAGEIFS

این تابع همانند تابع SUMIFS عمل می کند که برای به دست آوردن میانگین مقادیر چند سلول براساس معیارها و شرط‌های مختلف به کار می رود.

AVERAGEIFS (Average_ range; Criteria_ range1; Criteria1; Criteria_ range2; Criteria2;…)

Average_ range: در این قسمت محدوده ای که باید عملیات میانگین در آن صورت بگیرد را وارد می کنیم.

Criteria_ range1: محدوده شرط اول در این قسمت وارد می شود.

Criteria1: در این قسمت شرط اول نوشته می شود.

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

فرمول VLOOKUP

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

VLOOKUP(lookup_ value, table_ array, col_ index_ num, [range_ lookup])

Lookup_ Value: عبارت یا cell ای که میخواهیم جست و جو کنیم.

Table_ Array: جدولی که جست و جو در آن انجام می شود.

Col_ index_ Num: شماره ستونی از جدول است که میخواهیم جست و جو در آن انجام پذیرد.

فرمول FIND/SEARCH

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

SEARCH (find_ text, within, [start_num])

فرمول IFERROR

گاهی اوقات در اکسل با خطاهایی از جمله خطای #REF!، #NUM! و… مواجه می شویم. بااستفاده از تابع IFERROR می توان تعیین کرد که به جای خطا عبارت مورد دلخواه ما به عنوان خروجی نمایش داده شود.

فرمول COUNTIFS

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

COUNTIFS (criteria_ range1, criteria1,..)

فرمول LEFT/ RIGHT

تابع LEFT در اکسل تعداد کاراکتر دلخواه را از ابتدای کلمه یا سلول مدنظر و تابع RIGHT تعداد کاراکتر مد نظر را از انتهای کلمه جدا می کند.

فرمول RANK

این تابع رتبه اعداد داده شده در اکسل را تعیین می کند. اگر یک عدد چندین بار تکرار شده باشد این تابع رتبه یکسانی برای هریک نمایش می دهد.

RANK (number, ref, [order])

Number: مقادیری که قصد دارید رتبه آن را به دست آورید.

Ref: محدوده ای از اعداد که می خواهید در آن رتبه یک عدد اختیاری را به دست آورید.

Order: ورودی اختیاری که می تواند به دو صورت صفر و یک باشد، صفر (ترتیب نزولی) ، یک (ترتیب صعودی).

فرمول MINIFS

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

فرمول MAXIFS

از این تابع برای به دست آوردن حداکثر مقدار در یک ناحیه با توجه به چندین شرط استفاده می شود.

فرمول SUMPRODUCT

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