1. CONCATENATE

تابع =CONCATENATE یکی از حیاتی‌ترین توابع برای تحلیل داده است زیرا به شما امکان می‌دهد متن، اعداد، تاریخ‌ها و غیره را از چندین سلول به یک سلول ترکیب کنید. برای استفاده از این تابع، از سینتکس زیر پیروی کنید: 

=CONCATENATE(Cell 1, Cell 2)

سلول 1 و سلول 2 به سلول‌هایی اشاره دارند که می‌خواهید مقادیر آن‌ها را ادغام کنید. این روش همچنین برای ایجاد پارامترهای ردیابی برای کمپین‌های بازاریابی، ساخت "API queries"، افزودن متن به "a number format" و چندین کار دیگر مفید است.

2. "LEN" یک تابع مفید در برنامه‌نویسی است که برای به دست آوردن طول یک "string" یا تعداد عناصر یک "array" استفاده می‌شود.

LEN یک "function" کاربردی دیگر برای تحلیل داده است که به طور کلی تعداد "characters" را در هر "cell" داده شده خروجی می‌دهد. این "function" عمدتاً در هنگام ایجاد "title tags" یا توضیحات با محدودیت کاراکتر قابل استفاده است. "Syntax" این "function" به صورت زیر است:

=LEN(Cell)

سلول در نگارش بالا ممکن است دارای مقداری مانند A1، B2 و غیره باشد. این "function" همچنین می‌تواند زمانی که تفاوت‌های بین "unique identifiers" مختلف را کشف می‌کنیم، مفید باشد، که اغلب طولانی هستند و به ترتیب صحیح نیستند.

3. VLOOKUP

تابع "=VLOOKUP" می‌تواند داده‌ها را از یک جدول با یک مقدار ورودی تطبیق دهد. این تابع دو حالت تطبیق ارائه می‌دهد: دقیق و تقریبی که توسط محدوده جستجو کنترل می‌شود. اگر محدوده را روی "FALSE" تنظیم کنید، به دنبال یک تطبیق دقیق خواهد بود، اما اگر روی "TRUE" تنظیم کنید، به دنبال یک تطبیق تقریبی خواهد بود. در زیر سینتکس مربوطه آمده است: 

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

عناصر این "function" به صورت زیر تقسیم شده‌اند:

  • Lookup_value: این عنصر چیزی است که شما باید به دنبال آن بگردید.
  • Table_array: محدوده‌ای از سلول‌ها که حاوی داده‌ای است که می‌خواهید جستجو کنید.
  • col_index_num: شماره ستون در "table_array" که شامل مقداری است که می‌خواهید برگردانید.
  • [range_lookup]: آرگومان اختیاری که مشخص می‌کند تابع چگونه "lookup_value" را پیدا کند. TRUE برای تطابق‌های تقریبی، FALSE برای تطابق‌های دقیق.

۴. INDEX/MATCH

مانند تابع "VLOOKUP"، توابع "INDEX" و "MATCH" برای جستجوی داده‌های خاص بر اساس یک مقدار ورودی بسیار مفید هستند.
هنگامی که این دو تابع با هم استفاده می‌شوند، می‌توانند محدودیت‌های "VLOOKUP" را در ارائه نتایج اشتباه (اگر دقت نکنید) برطرف کنند. بنابراین، وقتی این دو تابع را ترکیب می‌کنید، می‌توانند مرجع داده را دقیقاً مشخص کرده و برای یک مقدار در یک آرایه تک‌بعدی جستجو کنند. این ترکیب مختصات داده‌ها را به عنوان یک عدد برمی‌گرداند. در زیر نحو این توابع آمده است: 

=INDEX(column of the data you want to return, MATCH(common data point you're trying to match, column of the other data source that has the common data point, 0))

در مثال بالا، من می‌خواستم تعداد بازدیدها در ماه ژانویه را پیدا کنم. برای این کار، از فرمول زیر استفاده کردم: 

 =INDEX (A2:C13, MATCH("Jan", A2:A13,0), 3)

  • A2:C13 ستون داده‌ای است که می‌خواهم "فرمول" آن را برگرداند.
  • Jan مقداری است که می‌خواهم با آن مطابقت دهم.
  • ستون A2:A13 ستونی است که فرمول در آن Jan را پیدا خواهد کرد.
  • 0 نشان می‌دهد که می‌خواهم فرمول یک تطابق دقیق برای مقدار پیدا کند.

اگر می‌خواهید یک تطابق تقریبی پیدا کنید، باید 0 را با 1 یا -1 جایگزین کنید. بنابراین، 1 بزرگ‌ترین مقداری که کوچکتر یا برابر با "lookup value" است را پیدا می‌کند، و -1 کوچک‌ترین مقداری که کوچکتر یا برابر با "lookup value" است را پیدا می‌کند. توجه داشته باشید که اگر از 0، 1، یا -1 استفاده نکنید، "formula" به‌طور پیش‌فرض از 1 استفاده خواهد کرد.

5. MINIFS/MAXIFS

=MINIFS و =MAXIFS مشابه توابع =MIN و =MAX هستند، با این تفاوت که به شما اجازه می‌دهند حداقل/حداکثر مجموعه‌ای از مقادیر را گرفته و آن‌ها را بر اساس معیارهای خاص تطبیق دهید. به طور اساسی، این تابع به دنبال مقادیر حداقل/حداکثر می‌گردد و آن‌ها را با معیارهای ورودی تطبیق می‌دهد. در اینجا نحو آن آمده است:

=MINIFS(min_range, criteria_range1, criteria1,…)
=MAXIFS(max_range, criteria_range1, criteria1,…)

در مثال بالا، من می‌خواستم حداقل نمرات را بر اساس جنسیت دانش‌آموز پیدا کنم. برای این کار از فرمول زیر استفاده کردم: 

=MINIFS (C2:C10, B2:B10, "M")

من نتیجه 27 را گرفتم. اینجا،

  • C2:C10 ستونی است که فرمول در آن به دنبال نمرات خواهد گشت.
  • B2:B10 ستونی است که فرمول در آن به دنبال معیار (جنسیت) خواهد گشت.
  • M معیار است.

به طور مشابه، من از فرمول زیر برای حداکثر امتیازات استفاده کردم و نتیجه "100" را به دست آوردم. 

=MAXIFS(C2:C10, B2:B10, "M")

۶. AVERAGEIFS

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

=AVERAGEIFS(average_range, criteria_range1, criteria1,…)

در مثال بالا، من می‌خواستم میانگین نمره را بر اساس جنسیت دانش‌آموز پیدا کنم. برای این منظور، از فرمول زیر استفاده کردم و عدد 56.8 را به دست آوردم. اینجا،

  • C2:C10 محدوده‌ای است که فرمول در آن به دنبال "average" خواهد گشت.
  • B2:B10 محدوده معیار است.
  • ام معیار است. 
=AVERAGEIFS(C2:C10, B2:B10, "M")

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

اگر می‌خواهید تعداد مواردی را که یک مجموعه داده معیارهای خاصی را برآورده می‌کند بشمارید، باید از تابع "=COUNTIFS" استفاده کنید. این تابع به شما اجازه می‌دهد تا معیارهای بی‌پایانی به جستجوی خود اضافه کنید و این کار را به ساده‌ترین روش برای پیدا کردن تعداد بر اساس معیارهای ورودی تبدیل می‌کند. در اینجا نحو آن آورده شده است: 

=COUNTIFS(criteria_range1, criteria1,…)

در این مثال، من می‌خواستم تعداد دانشجویان مرد یا زنی که نمرات قبولی (یعنی، ">=40") کسب کرده‌اند را پیدا کنم. برای این کار، از فرمول زیر استفاده کردم: 

=COUNTIFS(B2:B10, "M", C2:C10, ">=40")

این‌جا،

  • B2:B10 محدوده‌ای است که "فرمول" برای اولین معیار (جنسیت) جستجو خواهد کرد.
  • M اولین معیار است.
  • C2:C10 بازه‌ای است که فرمول در آن به دنبال معیار دوم (marks) خواهد گشت.
  • 40>= دومین معیار است.

8. SUMPRODUCT

تابع =SUMPRODUCT به شما کمک می‌کند تا دامنه‌ها یا آرایه‌ها را با هم ضرب کنید و سپس جمع حاصل‌ضرب‌ها را برگردانید. این یک تابع همه‌کاره است و می‌تواند مانند "COUNTIFS" یا "SUMIFS" برای شمارش و جمع‌آوری آرایه‌ها استفاده شود، اما با انعطاف‌پذیری بیشتر. شما همچنین می‌توانید از توابع دیگر درون "SUMPRODUCT" برای گسترش بیشتر قابلیت‌های آن استفاده کنید. در اینجا نحو آن آمده است: 

=SUMPRODUCT(array1, [array2], [array3],…)

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

=SUMPRODUCT(B2:B8, C2:C8)

اینجا،

  • B2:B8 اولین آرایه است (مقدار محصولات فروخته شده).
  • C2:C8 آرایه دوم است (قیمت هر محصول). سپس فرمول مقدار هر محصول فروخته شده را در قیمت آن ضرب می‌کند و همه آن را جمع می‌کند تا فروش کل را به دست آورد.

9. TRIM

تابع =TRIM به‌ویژه زمانی مفید است که با مجموعه‌داده‌ای کار می‌کنید که دارای فضاهای زیاد یا کاراکترهای ناخواسته است. این تابع به شما اجازه می‌دهد تا به‌آسانی این فضاها یا کاراکترها را از داده‌های خود حذف کنید و در هنگام استفاده از توابع دیگر نتایج دقیقی بگیرید. در اینجا نحو استفاده آمده است:

=TRIM(text)

در این مثال، می‌خواستم تمام فاصله‌های اضافی بین کلمات Mouse و pad در A7 را حذف کنم. برای این کار، از فرمول زیر استفاده کردم: 

 =TRIM(A7)

10. پیدا کردن/جستجو

در پایان، توابع FIND/SEARCH قرار دارند که به شما کمک می‌کنند متن خاصی را در یک مجموعه داده جدا کنید. هر دو تابع در کاری که انجام می‌دهند مشابه هستند، به جز یک تفاوت عمده — تابع =FIND تنها تطابق‌های حساس به حروف بزرگ و کوچک را برمی‌گرداند. در همین حال، تابع =SEARCH چنین محدودیتی ندارد. در اینجا سینتکس Find و Search به ترتیب آمده است: 

=FIND(find_text, within_text, [start_num])
=SEARCH(find_text, within_text, [start_num])

در این مثال، می‌خواستم تعداد دفعاتی که Gui در Guiding Tech ظاهر شده را پیدا کنم، بنابراین از فرمول زیر استفاده کردم که نتیجه 1 را به من داد. 

=FIND(A2, B2)

اگر بخواهم تعداد دفعاتی که gui درون Guiding Tech ظاهر شده را پیدا کنم، باید از فرمول =SEARCH استفاده کنم زیرا به حروف بزرگ و کوچک حساس نیست. این توابع به ویژه زمانی مفید هستند که به دنبال ناهنجاری‌ها یا شناسه‌های منحصربه‌فرد هستیم.

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

=TEXT(value, "format_text")

در مثال بالا، من از فرمول زیر استفاده می‌کنم تا مقدار موجود در G1 را به "words" تبدیل کنم. 

=TEXT(G1, "mmmm d, yyyy")

12. "IFERROR"

تابع =IFERROR یک مدیریت‌کننده خطاست که به شما اجازه می‌دهد مشخص کنید که می‌خواهید برنامه Excel هنگام بروز خطا چه چیزی را نشان دهد. این تابع کمک می‌کند تا از نمایش بسیاری از علامت‌های # خطا در برگه‌هایتان جلوگیری شود. اینجا نحوه نوشتن آن آورده شده است: 

=IFERROR(value, value_if_error)

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

=IFERROR(A1/B1, "Division by zero error")

13. "FILTER"

تابع =FILTER مجموعه داده‌های خاصی را بر اساس یک یا چند معیار استخراج می‌کند. شما می‌توانید نتایج را به صورت پویا فیلتر کنید بدون اینکه تغییراتی در داده‌ها ایجاد کنید. بنابراین، می‌توانید زیرمجموعه‌های مختلفی از داده‌های خود را بررسی و تحلیل کنید. در اینجا نحو دستور آمده است:

=FILTER(array, include, [if_empty])

ما می‌توانیم از مثال زیر استفاده کنیم تا نشان دهیم مقادیر ستون B برابر با "Sales" است.

=FILTER(A1:C10, B1:B10 = "Sales")

14. مرتب‌سازی

تابع =SORT داده‌ها را به ترتیب صعودی یا نزولی سازماندهی می‌کند. اینجا سینتکس آن آمده است: 

=SORT(array, sort_col1, sort_order1, [sort_col2, sort_order2], ...)

در مثال زیر، ما داده‌ها را در بازه A1:C10 مرتب می‌کنیم، 3 به ستون سوم در بازه اشاره دارد و ما از -1 برای مرتب‌سازی به ترتیب نزولی استفاده می‌کنیم.

=SORT(A1:C10, 3, -1)

ممکن است برخی از "Excel shortcuts" را هنگام استفاده از این توابع مفید بیابید.