اگر با جدولهای بزرگ اکسل کار میکنید، لازم نیست صدها تابع را حفظ باشید. چند تابع کاربردی میتوانند بخش زیادی از کارهای روزمره مثل پیدا کردن اطلاعات، بررسی چند شرط، ساخت فهرستهای بدون تکرار و محاسبه مجموعهای شرطی را انجام دهند. در نسخههای جدید اکسل نیز توابعی مانند XLOOKUP و UNIQUE در کنار آرایههای پویا کار را سادهتر کردهاند.
XLOOKUP برای جستوجوی سریع اطلاعات در اکسل
تابع XLOOKUP یکی از کاربردیترین روشها برای پیدا کردن یک مقدار در جدول و برگرداندن اطلاعات مرتبط با آن است. برخلاف VLOOKUP لازم نیست شماره ستون نتیجه را به صورت دستی محاسبه کنید و جستوجو میتواند در جهتهای مختلف انجام شود. همچنین اگر نتیجهای پیدا نشود میتوانید متن دلخواهی نمایش دهید.
به عنوان مثال اگر نام مشتری در سلول A2 باشد و بخواهید ایمیل او را از یک جدول دریافت کنید، ساختار ساده تابع میتواند چنین باشد:
=XLOOKUP(A2,Table1[Name],Table1[Email],"پیدا نشد")
یکی از مزیتهای مهم این تابع امکان استفاده از یک محدوده برای جستوجو و محدودهای دیگر برای نتیجه است. بنابراین تغییر ترتیب ستونهای جدول معمولاً دردسر کمتری ایجاد میکند.
IFS و SWITCH برای مدیریت چند شرط در اکسل
وقتی یک فرمول باید چند حالت مختلف را بررسی کند، استفاده از چند IF تو در تو میتواند خوانایی فایل را کاهش دهد. تابع IFS برای چنین شرایطی طراحی شده و اولین شرط درست را بررسی کرده و نتیجه مربوط به همان شرط را برمیگرداند. مایکروسافت نیز IFS را به عنوان راهی برای سادهتر کردن شرطهای متعدد معرفی میکند.
به عنوان مثال برای دستهبندی فروش میتوانید بنویسید:
=IFS(B2>=5000,"عالی",B2>=1000,"مناسب",TRUE,"نیازمند بررسی")
تابع SWITCH کمی متفاوت است و زمانی کاربرد بیشتری دارد که بخواهید یک مقدار مشخص را با چند مقدار دیگر مقایسه کنید. به عنوان مثال برای تبدیل کدهای وضعیت به عبارتهای قابل فهم میتوان از این ساختار استفاده کرد:
=SWITCH(A2,"A","فعال","P","در انتظار","C","بسته","نامشخص")
در نتیجه میتوان گفت IFS برای شرطهایی مانند بزرگتر یا کوچکتر بودن یک عدد مناسبتر است. SWITCH بیشتر برای تطبیق یک مقدار ثابت با چند گزینه مشخص کاربرد دارد.
UNIQUE برای استخراج و تحلیل دادههای تکراری
اگر در یک ستون نام مشتریها یا محصولات چند بار تکرار شده باشد، تابع UNIQUE میتواند فهرستی از مقادیر متمایز ایجاد کند، بدون اینکه اطلاعات اصلی را تغییر دهید. این تابع در نسخههای جدید اکسل از آرایههای پویا استفاده میکند و با تغییر دادههای منبع میتواند نتیجه را نیز بهروزرسانی کند.
ساختار ساده آن به شکل زیر است:
=UNIQUE(A2:A100)
این قابلیت برای ساخت فهرست مشتریها، شهرها، محصولات یا دستهبندیها بسیار کاربردی است. میتوانید نتیجه UNIQUE را با توابعی مانند SORT و FILTER نیز ترکیب کنید تا فهرست مرتب یا فیلترشده ایجاد شود..
جمع زدن با در نظر گرفتن چند شرط به کمک تابع SUMIFS
SUMIFS زمانی به کار میآید که بخواهید مجموع اعداد را بر اساس یک یا چند شرط محاسبه کنید. تمام شرطهای تعیینشده باید برقرار باشند تا مقدار مربوط به محاسبه وارد نتیجه شود.
به عنوان مثال برای محاسبه مجموع فروش یک منطقه میتوانید از چنین فرمولی استفاده کنید:
=SUMIFS(Table1[Amount],Table1[Region],F2)
اگر بخواهید وضعیت فروش را نیز به شرط اضافه کنید، یک محدوده و شرط دیگر به فرمول اضافه میشود:
=SUMIFS(Table1[Amount],Table1[Region],F2,Table1[Status],G2)
یکی از نکات مهم درباره SUMIFS این است که محدودههای شرط باید از نظر اندازه با محدوده جمع هماهنگ باشند. همچنین این تابع از عملگرهای مقایسهای و برخی الگوهای جستوجو پشتیبانی میکند.
کدام تابع اکسل را انتخاب کنیم؟
انتخاب تابع به نوع مسئله بستگی دارد و لازم نیست همه آنها را در هر فایل استفاده کنید.
- برای پیدا کردن اطلاعات مرتبط با یک مقدار از XLOOKUP استفاده کنید.
- برای بررسی چند شرط مختلف IFS گزینه مناسبی است.
- برای تطبیق یک مقدار با چند مقدار مشخص SWITCH کاربرد دارد.
- برای ساخت فهرست بدون مقادیر تکراری UNIQUE را به کار ببرید.
- برای محاسبه مجموع بر اساس چند شرط SUMIFS انتخاب مناسبی است.
نکته مهم این است که استفاده از Table در فایلهای بزرگ میتواند فرمولها را خواناتر کند و با اضافه شدن دادههای جدید نیز محدودهها را بهتر مدیریت کند. همچنین در نسخههای جدید اکسل میتوان این توابع را با FILTER و SORT ترکیب کرد تا بخش زیادی از کارهای معمول تحلیل داده بدون ابزارهای پیچیدهتر انجام شود.
گیم پلنت