در اکسل برای بررسی مقادیر مختلف و نمایش نتیجه متناسب با هر مقدار، معمولاً از توابع شرطی استفاده می‌شود. یکی از کاربردی‌ترین گزینه‌ها، تابع SWITCH در اکسل است که می‌تواند چندین مقدار را بررسی کند و بدون نیاز به نوشتن فرمول‌های طولانی و تودرتو، نتیجه موردنظر را نمایش دهد.
تابع SWITCH به‌خصوص زمانی مفید است که بخواهید کدها را به نام تبدیل کنید، برای نمرات دانش‌آموزان وضعیت تعیین کنید یا بر اساس نوع محصول، توضیحات و دسته‌بندی‌های متفاوتی نمایش دهید. در این آموزش اکسل با ساختار تابع SWITCH، نحوه فرمول نویسی در اکسل با مثال واقعی، تفاوت این تابع با IF و IFS و محدودیت‌های آن آشنا می‌شویم.
تابع SWITCH در اکسل چیست و چه کاربردی دارد؟
تابع SWITCH یکی از توابع منطقی اکسل است که مقدار یک سلول یا نتیجه یک عبارت را با چند مقدار مشخص مقایسه می‌کند و نتیجه مربوط به اولین مقدار منطبق را برمی‌گرداند.
به عنوان مثال، فرض کنید در یک جدول اکسل، وضعیت سفارش‌ها با کدهای عددی مشخص شده است. با استفاده از تابع SWITCH می‌توانید کاری کنید که به جای کد ۱ عبارت «در انتظار بررسی»، به جای کد ۲ عبارت «ارسال‌شده» و به جای کد ۳ عبارت «تحویل داده‌شده» نمایش داده شود.
مزیت اصلی این تابع آن است که مقدار مورد بررسی فقط یک بار در فرمول نوشته می‌شود. به همین دلیل، فرمول خواناتر است و اصلاح آن نسبت به چندین تابع IF تودرتو معمولاً ساده‌تر خواهد بود.
تابع SWITCH در اکسل ۲۰۱۹ و نسخه‌های جدیدتر و همچنین مایکروسافت ۳۶۵ پشتیبانی می‌شود. در نسخه‌های قدیمی‌تر مانند اکسل ۲۰۱۶ ممکن است با خطای #NAME? روبه‌رو شوید.
این تابع برای بررسی تطابق دقیق مقادیر طراحی شده و می‌تواند تا ۱۲۶ جفت مقدار و نتیجه را در یک فرمول مدیریت کند.
ساختار تابع SWITCH در اکسل و نحوه نوشتن فرمول
برای استفاده از تابع SWITCH باید ابتدا مقدار مورد بررسی را مشخص کنید و سپس جفت‌های مقدار و نتیجه را به‌ترتیب در فرمول قرار دهید.
ساختار کلی تابع به این صورت است:
=SWITCH(expression,value1,result1,value2,result2,default)
اجزای این فرمول عبارت‌اند از:
- expression: مقدار یا عبارتی که باید بررسی شود. این مقدار می‌تواند عدد، متن، آدرس سلول یا نتیجه یک فرمول دیگر باشد.
- value1: اولین مقداری که با عبارت اصلی مقایسه می‌شود.
- result1: نتیجه‌ای که در صورت تطابق با مقدار اول نمایش داده می‌شود.
- value2 و result2: جفت بعدی مقدار و نتیجه برای بررسی شرایط بیشتر.
- default: نتیجه پیش‌فرض که در صورت پیدا نشدن هیچ مقدار منطبق نمایش داده می‌شود. این بخش اختیاری است.
به عنوان مثال، فرمول زیر مقدار سلول A2 را بررسی می‌کند و بر اساس عدد موجود در آن، وضعیت سفارش را مشخص می‌کند:
=SWITCH(A2,1,"در انتظار بررسی",2,"ارسال‌شده",3,"تحویل داده‌شده","نامشخص")
اگر مقدار سلول A2 برابر با ۲ باشد، خروجی فرمول «ارسال‌شده» خواهد بود. اگر مقدار آن ۴ باشد، چون هیچ‌کدام از مقادیر تعریف‌شده مطابقت ندارند، عبارت «نامشخص» نمایش داده می‌شود.
هنگام استفاده از این تابع باید چند نکته را رعایت کنید. متن‌ها باید داخل علامت نقل‌قول انگلیسی قرار بگیرند، اما برای اعداد معمولی نیازی به این علامت نیست. همچنین اگر چند مقدار تکراری در فرمول وجود داشته باشد، تابع نتیجه اولین مورد منطبق را انتخاب می‌کند.
در بعضی تنظیمات منطقه‌ای اکسل، جداکننده آرگومان‌ها به جای کامای انگلیسی، علامت نقطه‌ویرگول (;) است. اگر فرمول با خطای نحوی روبه‌رو شد، جداکننده‌های آن را مطابق تنظیمات اکسل تغییر دهید.
آموزش استفاده از تابع SWITCH در اکسل با مثال نمرات دانش‌آموزان
برای یادگیری عملی تابع SWITCH، فرض کنید جدولی از نام دانش‌آموزان و نمرات آن‌ها در اکسل داریم و می‌خواهیم بر اساس نمره هر فرد، وضعیت آموزشی او به‌صورت خودکار مشخص شود.
در این مثال، ستون A شامل نام دانش‌آموز، ستون B شامل نمره و ستون C مربوط به وضعیت آموزشی است.
|
نام |
نمره |
وضعیت آموزشی |
|---|---|---|
| علی | A | ارتقا به سطح بعد |
| سارا | B | ادامه سطح فعلی |
| رضا | C | بازگشت به سطح قبل |
| مریم | خالی | نمره نامشخص |
برای ایجاد چنین خروجی‌ای، مراحل زیر را انجام دهید:
- ابتدا اطلاعات نام و نمره دانش‌آموزان را در ستون‌های A و B وارد کنید.
- روی سلول C2 کلیک کنید و فرمول زیر را بنویسید.
=SWITCH(B2,"A","ارتقا به سطح بعد","B","ادامه سطح فعلی","C","بازگشت به سطح قبل","نمره نامشخص")
- کلید Enter را فشار دهید تا نتیجه مربوط به نمره سلول B2 نمایش داده شود.
- برای اعمال فرمول به سایر ردیف‌ها، مربع کوچک گوشه پایین سلول C2 را به سمت پایین بکشید یا روی آن دبل‌کلیک کنید.
اگر اطلاعات را با استفاده از قابلیت Format as Table به جدول رسمی اکسل تبدیل کرده باشید، می‌توانید از ارجاع ساختاریافته نیز استفاده کنید:
=SWITCH([@Grade],"A","ارتقا به سطح بعد","B","ادامه سطح فعلی","C","بازگشت به سطح قبل","نمره نامشخص")
در این فرمول فرض شده است که نام ستون نمره Grade باشد. در جدول‌های رسمی اکسل، فرمول معمولاً به‌صورت خودکار در سایر ردیف‌های همان ستون اعمال می‌شود.
با این روش، اگر نمره دانش‌آموز تغییر کند، وضعیت آموزشی او نیز بدون نیاز به ویرایش دستی به‌روزرسانی خواهد شد.
تفاوت تابع SWITCH با IF و IFS و XLOOKUP در اکسل
در فرمول نویسی در اکسل چندین تابع وجود دارد که می‌توانند مقادیر را بررسی کنند و بر اساس شرایط مختلف نتیجه نمایش دهند. با این حال، تفاوت مهم تابع SWITCH با IF و IFS در نحوه بررسی شرط‌ها و سادگی ساختار فرمول است.
به عنوان مثال، برای بررسی سه نمره A و B و C می‌توان از تابع IF تودرتو استفاده کرد:
=IF(B2="A","عالی",IF(B2="B","خوب",IF(B2="C","متوسط","نامشخص")))
همین نتیجه با تابع IFS نیز قابل دستیابی است:
=IFS(B2="A","عالی",B2="B","خوب",B2="C","متوسط",TRUE,"نامشخص")
اما با تابع SWITCH در اکسل، نیازی نیست آدرس سلول B2 را چندین بار تکرار کنید:
=SWITCH(B2,"A","عالی","B","خوب","C","متوسط","نامشخص")
این تفاوت به‌خصوص زمانی اهمیت پیدا می‌کند که تعداد مقادیر مورد بررسی افزایش یابد. البته هرکدام از این توابع برای کاربرد مشخصی مناسب هستند.
- تابع SWITCH: برای مقایسه یک مقدار با چند مقدار ثابت و نمایش نتیجه متناسب با آن‌ها مناسب است.
- تابع IF: برای بررسی یک شرط یا ترکیب چند شرط منطقی کاربرد دارد و از عملگرهایی مانند بزرگ‌تر و کوچک‌تر پشتیبانی می‌کند.
- تابع IFS: چندین شرط مستقل را به‌ترتیب بررسی می‌کند و برای مقایسه بازه‌های عددی نیز گزینه مناسبی است.
- تابع XLOOKUP: برای جست‌وجوی مقادیر در جدول‌ها و برگرداندن اطلاعات مرتبط مناسب‌تر است. همچنین می‌تواند خروجی چندسلولی داشته باشد.
برای فهرست‌های کوتاه و نسبتاً ثابت، SWITCH معمولاً انتخاب ساده‌تری است. اما اگر تعداد کدها و نتایج زیاد باشد یا این اطلاعات مرتب تغییر کنند، بهتر است آن‌ها را در یک جدول جداگانه نگه دارید و از XLOOKUP استفاده کنید.
محدودیت‌ها و رفع خطاهای تابع SWITCH در اکسل
با وجود سادگی و کاربرد فراوان، تابع SWITCH محدودیت‌هایی دارد که باید هنگام ساخت فرمول در نظر بگیرید.
یکی از مهم‌ترین محدودیت‌های آن، مقایسه مستقیم مقادیر بر اساس برابری است. به عنوان مثال، اگر بخواهید بررسی کنید که نمره دانش‌آموز بیشتر از ۱۵ است یا کمتر از ۱۰، استفاده از تابع IFS معمولاً خواناتر خواهد بود.
البته می‌توان با قرار دادن TRUE در ابتدای تابع SWITCH، نتیجه عبارت‌های منطقی را نیز بررسی کرد:
=SWITCH(TRUE,B2>=18,"عالی",B2>=12,"قبول",B2<12,"مردود")
در این مثال، ترتیب شرط‌ها اهمیت دارد؛ زیرا اولین شرط درست، نتیجه فرمول را تعیین می‌کند. بنابراین باید شرط‌های مربوط به بازه‌های عددی را با دقت مرتب کنید.
از طرف دیگر، اگر هیچ‌کدام از مقادیر با عبارت اصلی برابر نباشند و نتیجه پیش‌فرض تعریف نشده باشد، اکسل خطای #N/A را نمایش می‌دهد.
به عنوان مثال، فرمول زیر در صورت وجود عدد ۴ در سلول A2 با خطا مواجه می‌شود:
=SWITCH(A2,1,"کم",2,"متوسط",3,"زیاد")
برای رفع این مشکل کافی است نتیجه پیش‌فرض را به انتهای فرمول اضافه کنید:
=SWITCH(A2,1,"کم",2,"متوسط",3,"زیاد","نامشخص")
اگر با خطای #NAME? مواجه شدید، ابتدا نسخه اکسل را بررسی کنید. تابع SWITCH در نسخه‌های قدیمی پشتیبانی نمی‌شود. همچنین املای نام تابع و استفاده صحیح از علامت‌های نقل‌قول را کنترل کنید.
از دیگر نکات مهم این است که SWITCH بین حروف کوچک و بزرگ انگلیسی تفاوتی قائل نمی‌شود. همچنین به‌صورت معمول از کاراکترهای جایگزین برای جست‌وجوی بخشی از متن پشتیبانی نمی‌کند و مقادیر عددی ذخیره‌شده به‌صورت متن ممکن است با اعداد واقعی تطابق نداشته باشند.
در مجموع، اگر می‌خواهید چند مقدار مشخص را به نتایج ثابت تبدیل کنید، تابع SWITCH در اکسل یکی از ساده‌ترین انتخاب‌ها است. اما برای جست‌وجو در جدول‌های بزرگ، مقایسه بازه‌ها یا مدیریت اطلاعاتی که مرتب تغییر می‌کنند، بهتر است سراغ توابع دیگری مانند IFS و XLOOKUP بروید.
گیم پلنت