12 فرمول مفید اکسل

بیش از 475 تابع در اکسل وجود دارد. این می تواند زمانی که شما با تجزیه و تحلیل داده ها شروع به کار می کنید، بسیار زیاد باشد.

با چنین تنوع زیادی از توابع، تشخیص اینکه از کدام یک برای وظایف خاص اکسل استفاده کنید، دشوار است.

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

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

---

1. IF

تابع IF بسیار مفید است. این تابع به این معنی است که ما می توانیم تصمیم گیری را در صفحات گسترده خود به طور خودکار انجام دهیم.

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

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

 (تست منطقی، مقدار اگر درست است، مقدار اگر نادرست است)IF=

در این مثال اگر تاریخ تحویل ستون C بیش از 7 روز دیرتر از تاریخ سفارش ستون B باشد کلمه بله را نمایش داده ایم در غیر این صورت کلمه خیر نمایش داده می شود.

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

2. SUMIFS

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

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

بنابراین می توانید اساساً SUMIF را نادیده بگیرید زیرا SUMIFS یک عملکرد برتر است.

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

( … معیار 1 ،محدوده معیار 1،مجموع محدوده)SUMIFS=

در این مثال، ما مقادیر ستون C را برای ناحیه وارد شده به سلول E3 جمع می کنیم.

SUMIFS(C2:C9،B2:B9،E3)=

قطعاً ارزش دارد که عملکرد SUMIFS را با جزئیات بیشتری بررسی کنید. این یک تابع اکسل بسیار مفید است.

3. COUNTIFS

تابع COUNTIFS یکی دیگر از تابع های مهم برای تجزیه و تحلیل داده های اکسل است.

بسیار شبیه تابع SUMIFS است. توابع AVERAGEIFS، MAXIFS و MINIFS نیز وجود دارد. ولی به عنوان بخشی از 12 توابع مفید اکسل برای تجزیه و تحلیل داده ها ذکر نشده است.

تابع COUNTIFS تعداد مقادیری را که معیارهای مشخص شده را برآورده می کنند، شمارش می کند . بنابراین، به محدوده جمعی مانند SUMIFS نیاز ندارد.

(محدوده معیار 1، معیار 1، …)COUNTIFS=

در این مثال، تعداد فروش از ناحیه وارد شده به سلول E3 که دارای ارزش 200 یا بیشتر است را می شماریم.

COUNTIFS(B2:B9,E3,C2:C9,”>=200″)=

هنگام استفاده از توابع SUMIFS و COUNTIFS، معیارها باید به عنوان متن یا به عنوان مرجع سلول وارد شوند. این مثال از هر دو تکنیک در یک فرمول استفاده می کند.

4. TRIM

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

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

در این مثال، تابع COUNTIFS از قبل کار نمی کند زیرا به طور تصادفی از یک فاصله در انتهای سلول B6 استفاده شده است.

کاربران نمی توانند این فضا را ببینند، به این معنی که تا زمانی که چیزی کار نکند، شناسایی نمی شود.

تابع TRIM از شما می‌خواهد که متن را حذف کنید.

(متن)TRIM=

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

TRIM(B2)=

سپس تابع COUNTIFS داده های تمیزی دارد و به درستی کار می کند.

5. CONCATENATE

تابع CONCATENATE مقادیر چندین سلول را در یک سلول ترکیب می کند.

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

از شما می خواهد که از مقادیر مختلف استفاده کنید.

CONCATENATE(text1، text2، text3،…)=

در این مثال، CONCATENATE برای ترکیب نام و نام خانوادگی در یک نام کامل استفاده می شود. یک فاصله برای آرگومان text2 وارد می شود.

CONCATENATE(A2، “، B2)=

6. LEFT/RIGHT

توابع LEFT و RIGHT عمل مخالف CONCATENATE را انجام خواهند داد. آنها تعداد مشخصی کاراکتر را از ابتدا و انتهای متن استخراج می کنند.

این می تواند برای استخراج بخش هایی از آدرس، URL یا مرجع برای تجزیه و تحلیل بیشتر استفاده شود.

توابع LEFT و RIGHT اطلاعات یکسانی را درخواست می کنند. آنها می خواهند بدانند متن کجاست و چند کاراکتر را می خواهید استخراج کنید.

 (متن، تعداد کاراکترها)LEFT=

 (متن، تعداد کاراکترها)RIGHT=

در این مثال، ستون A حاوی یک مرجع است که از شناسه مشتری (دو کاراکتر اول)، شناسه تراکنش و سپس کد منطقه (نویسه نهایی) تشکیل شده است.

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

LEFT(A2,2)=

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

RIGHT(A2,1)=

7. VLOOKUP

تابع VLOOKUP یکی از متداول ترین و قابل تشخیص ترین توابع در اکسل است .

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

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

چهار بخش از اطلاعات را درخواست می کند:

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

 (مقدار جستجو، آرایه جدول، شماره فهرست ستون، جستجوی محدوده)VLOOKUP=

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

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

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

VLOOKUP(B2,$G$2:$H$12,2,FALSE)=

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

8. IFERROR

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

به دلیل وجود اشتباه تایپی در نام در جدول فروش، خطا داریم. این بدان معنی است که VLOOKUP نمی تواند آن نام را پیدا کند و یک خطا ایجاد می کند.

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

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

در این مثال، تابع IFERROR را به دور VLOOKUP می‌پیچیم تا پیام معنادارتری را نمایش دهیم.

IFERROR(VLOOKUP(B2,$G$2:$H$12,2,FALSE),”Name not found. Check both lists”)=

9. VALUE

اغلب مجموعه داده‌هایی که نیاز به تجزیه و تحلیل دارید، از سیستم دیگری وارد شده یا از جایی کپی و پیست شده است.

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

خوشبختانه، تابع VALUE اینجاست تا به شما کمک کند. وظیفه آن تبدیل اعدادی که به عنوان متن ذخیره شده اند به اعداد است .

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

 (متن)VALUE=

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

VALUE (B2)=

10. UNIQUE

تابع UNIQUE یک عملکرد جدید است که فقط برای کسانی که از نسخه Microsoft 365 استفاده می کنند در دسترس است.

تابع می خواهد سه چیز را بداند:

• محدوده ای که باید لیست منحصر به فرد را از آن برگرداند
• این که آیا می خواهید مقادیر منحصر به فرد را بر اساس ستون یا ردیف بررسی کنید
• خواه یک لیست منحصر به فرد می خواهید، یا یک لیست متمایز (مواردی که فقط یک بار رخ می دهند).

UNIQUE(array, by col, exactly once)=

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

UNIQUE(B2:B15)=

این یک تابع آرایه پویا است و بنابراین نتایج را بیرون می‌دهد. حاشیه آبی محدوده ریخته شده را نشان می دهد.

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

11. SORT

این عملکرد دیگری است که فقط برای مشترکین Microsoft 365 در دسترس است . همانطور که از نام آن پیداست، لیستی را مرتب می کند.

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

• محدوده برای مرتب سازی
• بر اساس کدام ستون محدوده را مرتب کنیم
• ترتیب مرتب سازی محدوده (صعودی یا نزولی)
• ردیف‌ها یا ستون‌ها را مرتب کنیم.

SORT(array, sort index,sort order, by col)=

این فوق العاده است. و می توان از آن با مثال UNIQUE قبلی برای مرتب کردن نام محصولات به ترتیب استفاده کرد.

برای این کار، فقط باید محدوده ای را برای مرتب سازی در اختیار آن قرار دهیم.

SORT(UNIQUE(B2:B15))=

12. FILTER

به دنبال تابع SORT ، یک تابع برای فیلتر کردن لیست نیز وجود دارد. عملکرد دیگری که فقط برای کاربران Microsoft 365 در دسترس است .

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

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

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

FILTER(array, include, if empty)=

در این مثال، فقط نتایج مربوط به موضوع وارد شده در سلول F2 برگردانده می شود.

FILTER(B2:C12,A2:A12=F2,”No scores”)=