۱۲ تابع پرکاربرد اکسل برای تحلیل داده‌ها

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

در این مقاله، ۱۲ تابع بسیار کاربردی اکسل برای تحلیل داده‌ها معرفی شده‌اند که ابزارهای ضروری و مؤثری برای انجام بسیاری از عملیات تحلیلی در اکسل در اختیار شما قرار می‌دهند.

1. تابع  IF

تابع IF از شما سه ورودی می‌خواهد:

1. شرط یا آزمون منطقی که باید بررسی شود
2. عملی که در صورت درست بودن شرط (True) انجام می‌شود
3. عملی که در صورت نادرست بودن شرط (False) انجام می‌شود

ساختار فرمول به شکل زیر است:

=IF(شرط منطقی، مقدار در صورت درست بودن، مقدار در صورت نادرست بودن)

در مثال زیر، اگر تاریخ تحویل در ستون C بیش از 4 روز بعد از تاریخ سفارش در ستون B باشد، عبارت “دارد“ نمایش داده می‌شود؛ در غیر این صورت، عبارت “ندارد“ نمایش داده خواهد شد:

=IF(D2>7,”Yes”,”No”)

2. تابع SUMIFS

بنابراین، در بیشتر موارد می‌توان به جای SUMIF از SUMIFS استفاده کرد، چرا که تابعی پیشرفته‌تر و جامع‌تر است.

ساختار کلی این تابع به شکل زیر است:

=SUMIFS(محدوده جمع، محدوده شرط اول، شرط اول، …)

در مثال زیر، مقادیر موجود در ستون D جمع زده می‌شوند، اما تنها برای ردیف‌هایی که ناحیه آنها با مقدار وارد شده در سلول F4 مطابقت دارد:

=SUMIFS(D2:D9,C2:C9,F4)

قطعاً ارزش دارد که تابع SUMIFS را با دقت و جزئیات بیشتری بررسی کنید، چرا که این یکی از کاربردی‌ترین توابع اکسل به شمار می‌آید.

3. تابع  COUNTIFS

تابع COUNTIFS تعداد مقادیری را می‌شمارد که با شرایط مشخصی مطابقت داشته باشند؛ بنابراین برخلاف  SUMIFS، نیازی به تعریف محدوده جمع (sum range) ندارد.

ساختار کلی تابع به شکل زیر است:

=COUNTIFS(محدوده شرط اول، شرط اول …)

در مثال زیر، تعداد فروش‌هایی شمرده می‌شود که متعلق به ناحیه وارد شده در سلول F4 باشند و مقدار آنها ۲۰۰ یا بیشتر باشد:

=COUNTIFS(C3:C9,F4,D3:D9,”>200″)

هنگام استفاده از توابع SUMIFS  و COUNTIFS، شرط‌ها باید به‌صورت متنی (Text) یا ارجاع به یک سلول وارد شوند.
در این مثال، هر دو روش در یک فرمول واحد به کار گرفته شده‌اند.

4. تابع  TRIM

این تابع کاربردی، تمامی فاصله‌های اضافی (Space) را از یک سلول حذف می‌کند، به جز فاصله‌های بین کلمات.
رایج‌ترین کاربرد تابع TRIM، حذف فاصله‌های انتهایی (Trailing Spaces) است؛ مشکلی که معمولاً هنگام کپی‌کردن محتوا از منابع دیگر یا واردکردن تصادفی فاصله در انتهای متن رخ می‌دهد.

در این مثال، تابع COUNTIFS که قبلاً استفاده شده بود، به دلیل وجود یک فاصله اضافی در ابتدای سلول C4  به درستی کار نمی‌کند.

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

ساختار فرمول به صورت زیر است:

=TRIM(text)

در این مثال، تابع TRIM در یک ستون جداگانه استفاده شده است تا داده‌های موجود در ستون ناحیه (Region) را پاک‌سازی کرده و آن‌ها را برای تحلیل آماده کند:

=TRIM(C3)

سپس تابع COUNTIFS  با استفاده از داده‌های پاک‌سازی‌شده به درستی اجرا می‌شود و نتیجه‌ی صحیحی ارائه می‌دهد.

5. تابع  CONCATENATE

این تابع بسیار کاربردی است برای کنار هم قرار دادن بخش‌های مختلف یک متن، مانند نام و نام خانوادگی، آدرس، شماره مرجع یا مسیر فایل و آدرس URL.

تابع از شما می‌خواهد مقادیر مختلفی را وارد کنید که قصد دارید به هم متصل شوند.

ساختار فرمول به شکل زیر است:

=CONCATENATE(text1, text2, text3, …)

در این مثال، از تابع CONCATENATE برای ترکیب نام کوچک  و نام خانوادگی  استفاده شده است تا نام کامل (نام + نام خانوادگی) ساخته شود.
برای آرگومان text2، یک فاصله (space) وارد شده است تا بین نام و نام خانوادگی فاصله ایجاد شود:

=CONCATENATE(B3,” “,C3)

6. توابع LEFT و RIGHT

این قابلیت برای استخراج بخش‌هایی از آدرس، آدرس اینترنتی (URL) یا شماره مرجع به منظور تحلیل‌های دقیق‌تر بسیار کاربردی است.

توابع LEFT و RIGHT اطلاعات مشابهی را از شما دریافت می‌کنند؛
آن‌ها می‌پرسند متن مورد نظر کجاست و چند کاراکتر باید استخراج شود.

ساختار فرمول‌ها به این شکل است:

1. =LEFT(text, تعداد کاراکتر)
2. =RIGHT(text, تعداد کاراکتر)

در این مثال، ستون A شامل یک کد مرجع است که از شناسه مشتری (دو حرف اول)، شناسه تراکنش و در انتها کد ناحیه (یک حرف آخر) تشکیل شده است.

در ادامه، از تابع LEFT برای استخراج شناسه مشتری استفاده شده است:

=LEFT(B3, 2)

تابع RIGHT  برای استخراج آخرین کاراکتر از سلول‌های ستون B کاربرد دارد.
در این مثال، این کاراکتر نشان‌دهنده موقعیت مشتری در جنوب (South) یا شمال (North) است.

فرمول به شکل زیر است:

=RIGHT(B3, 1)

7. تابع VLOOKUP

VLOOKUP  به‌ویژه زمانی مفید است که بخواهید داده‌ها را از لیست‌های مختلف ترکیب کنید یا دو لیست را با هم مقایسه کرده و موارد مشترک یا غایب را پیدا کنید. این تابع از ابزارهای کلیدی در تحلیل داده‌های اکسل به‌شمار می‌رود.

برای استفاده از VLOOKUP، باید چهار ورودی را مشخص کنید:

1. مقداری که قصد جستجوی آن را دارید
2. محدوده جدولی که در آن جستجو انجام می‌شود
3. شماره ستونی که می‌خواهید نتیجه را از آن بگیرید
4. نوع جستجو (دقیق یا تقریبی)

ساختار فرمول به شکل زیر است:

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

در این مثال، جدولی داریم که اطلاعات فروش کارکنان را نمایش می‌دهد. جدول دیگری نیز وجود دارد که شامل جزئیات بیشتری درباره همین کارکنان است (برای ساده‌سازی، جداول در اندازه کوچک ارائه شده‌اند).

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

برای این منظور، از فرمول زیر در ستون E استفاده شده است:

=VLOOKUP(C3,$G$2:$H$9,2,0)

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

8. تابع IFERROR

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

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

تابع IFERROR دو ورودی اصلی دارد:

• مقداری که باید برای خطا بررسی شود
• عملی که در صورت بروز خطا باید اجرا شود

در این مثال، تابع IFERROR دور تابع VLOOKUP پیچیده شده است تا در صورت بروز خطا، پیام خطای واضح‌تری نمایش داده شود:

=IFERROR(VLOOKUP(C3,$G$2:$H$9,2,0),”Not Found”)

9. تابع VALUE

بسیاری از داده‌هایی که قصد تحلیل آن‌ها را دارید، ممکن است از سیستم‌های دیگر وارد شده یا از منابع مختلف کپی و در اکسل جای‌گذاری شده باشند.
این مسئله گاهی باعث می‌شود داده‌ها در قالب نادرستی ذخیره شوند، مثلاً اعداد به صورت متن ذخیره شوند. زمانی که اکسل نتواند یک مقدار را به عنوان عدد شناسایی کند، انجام عملیات‌هایی مانند جمع (SUM) روی آن‌ها امکان‌پذیر نخواهد بود.

خوشبختانه، تابع VALUE برای رفع این مشکل طراحی شده است. این تابع وظیفه تبدیل اعدادی که به صورت متن ذخیره شده‌اند به قالب عددی واقعی را بر عهده دارد.

تابع از شما می‌خواهد متنی که باید تبدیل شود را وارد کنید.

ساختار فرمول به شکل زیر است:

=VALUE(text)

در این مثال، فرمول زیر مقادیر فروش ذخیره‌شده به صورت متن در ستون B را به عدد تبدیل می‌کند:

=VALUE(D3)

10. تابع UNIQUE

تابع UNIQUE  تنها در محیط آرایه‌های پویا اکسل در دسترس است؛ یعنی در نسخه‌های Excel 365، Excel آنلاین و Excel 2021 یا نسخه‌های جدیدتر.

این تابع سه آرگومان اصلی می‌گیرد:

• بازه‌ای که می‌خواهید از آن لیست یکتا (unique) استخراج شود
• مشخص می‌کند که بررسی یکتا بودن بر اساس ستون انجام شود یا سطر
• تعیین می‌کند که آیا می‌خواهید لیست یکتا (تمام مقادیر بدون تکرار) دریافت کنید یا لیست متمایز (تنها آیتم‌هایی که دقیقاً یک بار تکرار شده‌اند)

ساختار فرمول به شکل زیر است:

=UNIQUE(array, by_col, exactly_once)

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

=UNIQUE(C3:C12)

این یک تابع آرایه پویا است و به همین دلیل نتایج آن به‌صورت خودکار در سلول‌های مجاور (spill) نمایش داده می‌شوند. محدوده‌ای که نتایج در آن قرار می‌گیرند، با یک کادر آبی رنگ مشخص شده است.
پس از آن، می‌توانیم از تابع SUMIFS  که پیش‌تر در این مقاله معرفی شد، برای جمع‌زدن فروش هر یک از این محصولات استفاده کنیم.

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

11. تابع SORT

تابع SORT  چهار آرگومان ورودی می‌گیرد:

• بازه‌ای که می‌خواهید مرتب شود
• ستونی که بر اساس آن مرتب‌سازی انجام شود
• ترتیب مرتب‌سازی (صعودی یا نزولی)
• مشخص می‌کند مرتب‌سازی بر اساس سطرها انجام شود یا ستون‌ها

ساختار فرمول به صورت زیر است:

=SORT(array, sort_index, sort_order, by_col)

این تابع بسیار کاربردی است و می‌توان آن را با تابع UNIQUE  که قبلاً معرفی شد، ترکیب کرد تا نام محصولات را به صورت مرتب شده نمایش دهد.

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

=SORT(UNIQUE(C3:C12))

12. تابع  FILTER

تابع FILTER  سه آرگومان دریافت می‌کند:

• بازه‌ای که قصد فیلتر کردن آن را دارید
• شرطی که مشخص می‌کند کدام نتایج باید بازگردانده شوند
• عملی که در صورت پیدا نشدن هیچ نتیجه‌ای باید اجرا شود

ساختار فرمول به شکل زیر است:

=FILTER(array, include, if_empty)

در این مثال، تنها نتایجی که مربوط به موضوع وارد شده در سلول G2 هستند، بازگردانده می‌شوند:

=FILTER(C3:D12,B3:B12=G2)

جمع‌بندی

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

دو ابزار کلیدی که تسلط بر آن‌ها برای تحلیل‌گران داده حیاتی است، Power Query  و Power Pivot  هستند.
Power Query  فرآیند وارد کردن و تبدیل داده‌ها را بسیار آسان و کارآمد می‌کند. از سوی دیگر، Power Pivot  ابزار ایده‌آلی برای کار با حجم‌های بسیار بالا از داده‌هاست؛ این ابزار قادر است داده‌ها را خارج از محیط اکسل ذخیره کند و دارای زبان فرمول‌نویسی قدرتمندی به نام DAX  است.

برای تسریع یادگیری مهارت‌های تحلیل داده در اکسل، می‌توانید دوره‌های Power Query  و  Power Pivot را در سایت Excellearn  دنبال کنید.

مطالعه بیشتر