آموزش تابع SWITCH در اکسل

معرفی تابع SWITCH

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

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

فرض کنید در سلول A2 عدد وضعیت سفارش قرار دارد: عدد 1 یعنی «ثبت شده»، عدد 2 یعنی «در حال ارسال» و عدد 3 یعنی «تحویل شده». فرمول زیر وضعیت را به متن تبدیل می‌کند:

=SWITCH(A2,1,"ثبت شده",2,"در حال ارسال",3,"تحویل شده","وضعیت نامشخص")

اگر مقدار A2 برابر با 2 باشد، نتیجه فرمول «در حال ارسال» خواهد بود. اگر مقدار A2 هیچ‌کدام از اعداد 1، 2 یا 3 نباشد، نتیجه «وضعیت نامشخص» نمایش داده می‌شود.

کاربردهای اصلی تابع SWITCH

تابع SWITCH در بسیاری از سناریوهای روزمره اکسل کاربرد دارد؛ مخصوصاً وقتی با دسته‌بندی، کدگذاری یا تبدیل مقادیر سر و کار دارید.

• تبدیل کدهای عددی یا متنی به توضیحات قابل فهم
• جایگزین کردن IFهای تو در تو برای افزایش خوانایی فرمول
• دسته‌بندی وضعیت‌ها، سطح‌ها، رتبه‌ها و نوع مشتریان
• نمایش پیام‌های متفاوت بر اساس مقدار یک سلول
• ساخت گزارش‌های مدیریتی با خروجی‌های متنی ساده و شفاف
• ترکیب با توابعی مثل SUM، AND، OR، COUNTIF و XLOOKUP برای تصمیم‌گیری‌های پیشرفته‌تر
• ایجاد فرمول‌های شرطی مرتب‌تر در داشبوردها و فایل‌های تحلیلی

ساختار (Syntax)

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

=SWITCH(expression, value1, result1, [value2], [result2], ..., [default])

ساختار مفهومی تابع با جداکننده فارسی به شکل زیر قابل نمایش است:

=SWITCH(عبارت_مورد_بررسی; مقدار۱; نتیجه۱; مقدار۲; نتیجه۲; نتیجه_پیش‌فرض)

تابع SWITCH ابتدا expression یا همان عبارت مورد بررسی را ارزیابی می‌کند. سپس آن را به ترتیب با valueها مقایسه می‌کند. به محض اینکه یک مقدار مطابق پیدا شود، نتیجه متناظر با آن را نمایش می‌دهد. اگر هیچ مقداری مطابق نبود، در صورت تعریف شدن مقدار پیش‌فرض، همان نمایش داده می‌شود.

آرگومان‌ها

expression / عبارت مورد بررسی

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

مثلاً می‌توانید مقدار سلول A2 را بررسی کنید و بر اساس آن نتیجه متفاوتی بگیرید.

value1 / مقدار اول

اولین مقداری است که expression با آن مقایسه می‌شود. اگر expression با این مقدار برابر باشد، تابع نتیجه مربوط به result1 را برمی‌گرداند.

result1 / نتیجه اول

نتیجه‌ای است که اگر expression با value1 برابر باشد، نمایش داده می‌شود. این نتیجه می‌تواند متن، عدد، تاریخ، خروجی یک محاسبه یا حتی نتیجه یک تابع دیگر باشد.

value2 و result2 / مقدار و نتیجه‌های بعدی

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

default / نتیجه پیش‌فرض

این آرگومان اختیاری است و زمانی استفاده می‌شود که هیچ‌کدام از مقدارهای تعریف‌شده با expression برابر نباشند. استفاده از نتیجه پیش‌فرض بسیار توصیه می‌شود، چون اگر هیچ تطابقی پیدا نشود و default تعریف نشده باشد، اکسل خطای #N/A نمایش می‌دهد.

مثال‌های ساده و پایه

مثال ۱: تبدیل شماره روز هفته به نام روز

فرض کنید در سلول A2 عدد روز هفته وارد شده است. عدد 1 برای شنبه، عدد 2 برای یکشنبه و عدد 3 برای دوشنبه در نظر گرفته شده است.

=SWITCH(A2,1,"شنبه",2,"یکشنبه",3,"دوشنبه","عدد نامعتبر")

اگر مقدار A2 برابر با 1 باشد، نتیجه «شنبه» خواهد بود. اگر مقدار A2 برابر با 3 باشد، نتیجه «دوشنبه» نمایش داده می‌شود. اگر عددی خارج از موارد تعریف‌شده وارد شود، نتیجه «عدد نامعتبر» خواهد بود.

مثال ۲: تبدیل کد محصول به نام محصول

فرض کنید در ستون A کد محصول نوشته شده است و می‌خواهید در ستون B نام محصول را نمایش دهید.

=SWITCH(A2,"P100","لپ‌تاپ","P200","مانیتور","P300","کیبورد","کد نامعتبر")

اگر در A2 مقدار P200 باشد، خروجی فرمول «مانیتور» می‌شود. این روش برای فایل‌هایی که در آن‌ها با کدهای متنی کار می‌کنید بسیار مفید است.

مثال ۳: نمایش وضعیت پرداخت

فرض کنید در سلول B2 یکی از وضعیت‌های Paid، Pending یا Canceled وارد می‌شود و می‌خواهید معادل فارسی آن را نمایش دهید.

=SWITCH(B2,"Paid","پرداخت شده","Pending","در انتظار پرداخت","Canceled","لغو شده","نامشخص")

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

مثال‌های کاربردی و واقعی

مثال ۱: دسته‌بندی نمره با SWITCH و TRUE

تابع SWITCH معمولاً برای مقایسه‌های دقیق استفاده می‌شود، اما اگر expression را TRUE قرار دهید، می‌توانید شرط‌های منطقی هم تعریف کنید. فرض کنید نمره دانشجو در A2 قرار دارد.

=SWITCH(TRUE,A2>=90,"عالی",A2>=75,"خوب",A2>=60,"قابل قبول","نیاز به تلاش")

در این روش اکسل شرط‌ها را از چپ به راست بررسی می‌کند. اگر نمره 92 باشد، نتیجه «عالی» می‌شود. اگر نمره 78 باشد، نتیجه «خوب» خواهد بود.

مثال ۲: محاسبه مبلغ نهایی با SWITCH و SUM

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

=SWITCH(E2,"تهران",SUM(B2:D2)*1.09,"اصفهان",SUM(B2:D2)*1.07,"شیراز",SUM(B2:D2)*1.05,SUM(B2:D2))

اگر شهر «تهران» باشد، جمع فروش در ضریب 1.09 ضرب می‌شود. اگر شهر در لیست نباشد، همان جمع ساده فروش نمایش داده می‌شود.

مثال ۳: تصمیم‌گیری با SWITCH و AND

فرض کنید امتیاز فروشنده در B2 و وضعیت قرارداد او در C2 قرار دارد. اگر امتیاز حداقل 80 باشد و قرارداد فعال باشد، پاداش پرداخت شود.

=SWITCH(TRUE,AND(B2>=80,C2="فعال"),"پاداش کامل",AND(B2>=60,C2="فعال"),"پاداش متوسط","بدون پاداش")

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

مثال ۴: تصمیم‌گیری با SWITCH و OR

فرض کنید در سلول B2 نوع مشتری نوشته شده است. اگر مشتری VIP یا سازمانی باشد، باید اولویت بالا داشته باشد.

=SWITCH(TRUE,OR(B2="VIP",B2="سازمانی"),"اولویت بالا",B2="عادی","اولویت معمولی","نیاز به بررسی")

در این فرمول، اگر مقدار B2 برابر با VIP یا سازمانی باشد، نتیجه «اولویت بالا» نمایش داده می‌شود.

مثال ۵: شناسایی مقدار تکراری با SWITCH و COUNTIF

فرض کنید لیستی از کدهای مشتری در محدوده A2 تا A100 دارید و می‌خواهید بدانید کد هر ردیف چند بار تکرار شده است.

=SWITCH(TRUE,COUNTIF($A$2:$A$100,A2)=1,"یکتا",COUNTIF($A$2:$A$100,A2)>=2,"تکراری","نامشخص")

اگر کد مشتری فقط یک بار در محدوده وجود داشته باشد، نتیجه «یکتا» می‌شود. اگر دو بار یا بیشتر وجود داشته باشد، نتیجه «تکراری» نمایش داده می‌شود.

مثال ۶: ترکیب SWITCH با XLOOKUP

فرض کنید در A2 کد مشتری قرار دارد. در جدول F2:G10 کد مشتری و نوع مشتری ذخیره شده است. می‌خواهید ابتدا نوع مشتری را پیدا کنید و سپس بر اساس آن میزان تخفیف را نمایش دهید.

=SWITCH(XLOOKUP(A2,$F$2:$F$10,$G$2:$G$10,"نامشخص"),"VIP","تخفیف 20%","عادی","تخفیف 5%","سازمانی","تخفیف 15%","بدون تخفیف")

در این مثال، XLOOKUP نوع مشتری را پیدا می‌کند و سپس SWITCH بر اساس آن، تخفیف مناسب را برمی‌گرداند.

ترکیب تابع SWITCH با فرمول‌های دیگر

یکی از نقاط قوت SWITCH این است که می‌تواند هم در بخش expression و هم در بخش result با توابع دیگر ترکیب شود. این موضوع باعث می‌شود بتوانید تصمیم‌گیری‌های پیچیده‌تری بسازید.

• ترکیب با SUM برای محاسبه جمع و اعمال ضریب‌های مختلف

=SWITCH(A2,"طلایی",SUM(B2:D2)*1.2,"نقره‌ای",SUM(B2:D2)*1.1,SUM(B2:D2))

• ترکیب با AND برای بررسی چند شرط هم‌زمان

=SWITCH(TRUE,AND(B2>=100,C2="فعال"),"تایید",AND(B2>=50,C2="فعال"),"بررسی بیشتر","رد")

• ترکیب با OR برای بررسی چند حالت جایگزین

=SWITCH(TRUE,OR(A2="تهران",A2="کرج"),"منطقه مرکزی",OR(A2="مشهد",A2="نیشابور"),"منطقه شرق","سایر مناطق")

• ترکیب با COUNTIF برای تشخیص تکراری بودن داده‌ها

=SWITCH(TRUE,COUNTIF($A$2:$A$200,A2)=1,"جدید",COUNTIF($A$2:$A$200,A2)>=2,"تکراری","نامشخص")

• ترکیب با XLOOKUP برای پیدا کردن مقدار و سپس تصمیم‌گیری بر اساس آن

=SWITCH(XLOOKUP(A2,$H$2:$H$20,$I$2:$I$20,"نامشخص"),"A","سطح یک","B","سطح دو","C","سطح سه","بدون سطح")

• ترکیب با TODAY برای تصمیم‌گیری بر اساس تاریخ روز

=SWITCH(TRUE,B2>=TODAY(),"فعال",B2>=TODAY()-30,"اخیراً منقضی شده","منقضی شده")

خطاهای رایج و روش رفع آنها

۱) خطای #N/A به دلیل نبود مقدار پیش‌فرض

اگر هیچ‌کدام از valueها با expression برابر نباشند و مقدار پیش‌فرض هم تعریف نشده باشد، اکسل خطای #N/A نمایش می‌دهد.

روش رفع: همیشه در انتهای تابع SWITCH یک نتیجه پیش‌فرض قرار دهید تا اگر هیچ تطابقی پیدا نشد، پیام مناسبی نمایش داده شود.

=SWITCH(A2,1,"ثبت شده",2,"ارسال شده",3,"تحویل شده","وضعیت نامشخص")

۲) جابه‌جا نوشتن value و result

یکی از اشتباهات رایج این است که مقدار و نتیجه به ترتیب درست نوشته نشوند. در SWITCH باید بعد از هر مقدار، نتیجه همان مقدار قرار بگیرد.

روش رفع: فرمول را به صورت جفت‌های مقدار و نتیجه بخوانید. یعنی مقدار اول، نتیجه اول، مقدار دوم، نتیجه دوم و همین‌طور ادامه دهید.

=SWITCH(B2,"A","عالی","B","خوب","C","متوسط","نامشخص")

۳) استفاده از SWITCH برای شرط‌های بازه‌ای بدون TRUE

تابع SWITCH به صورت عادی برای مقایسه برابر بودن استفاده می‌شود. اگر بخواهید شرط‌هایی مثل بزرگ‌تر یا مساوی را بررسی کنید، بهتر است expression را TRUE قرار دهید.

روش رفع: برای شرط‌های منطقی و بازه‌ای، از الگوی SWITCH همراه با TRUE استفاده کنید.

=SWITCH(TRUE,A2>=90,"عالی",A2>=70,"خوب",A2>=50,"متوسط","ضعیف")

۴) حساس نبودن به حروف بزرگ و کوچک

تابع SWITCH معمولاً بین حروف بزرگ و کوچک انگلیسی تفاوت قائل نمی‌شود. بنابراین ممکن است مقادیری مثل vip و VIP برای شما یکسان در نظر گرفته شوند.

روش رفع: اگر لازم است متن دقیقاً از نظر حروف بزرگ و کوچک بررسی شود، بهتر است از توابع کمکی مناسب یا روش‌های دقیق‌تر استفاده کنید.

۵) استفاده بیش از حد از SWITCH در فرمول‌های بسیار طولانی

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

روش رفع: برای تعداد حالت‌های زیاد، بهتر است یک جدول مرجع بسازید و از XLOOKUP یا VLOOKUP استفاده کنید.

=XLOOKUP(A2,$F$2:$F$100,$G$2:$G$100,"کد نامعتبر")

۶) اشتباه در جداکننده آرگومان‌ها

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

روش رفع: اگر اکسل شما با کاما کار نمی‌کند، همان فرمول را با سمی‌کالن امتحان کنید.

=SWITCH(A2;1;"ثبت شده";2;"ارسال شده";"نامشخص")

نکات حرفه‌ای و ترفندهای مهم

• همیشه برای SWITCH مقدار پیش‌فرض تعریف کنید تا از خطای #N/A جلوگیری شود.
• برای شرط‌های بازه‌ای، از الگوی SWITCH همراه با TRUE استفاده کنید.
• اگر تعداد حالت‌ها کم و مشخص است، SWITCH انتخابی خوانا و مناسب است.
• اگر تعداد حالت‌ها زیاد است، بهتر است به جای SWITCH از جدول مرجع و XLOOKUP استفاده کنید.
• ترتیب شرط‌ها در حالت SWITCH همراه با TRUE بسیار مهم است؛ شرط‌های خاص‌تر و بزرگ‌تر را زودتر بنویسید.
• برای خوانایی بیشتر، حالت‌ها را با ترتیب منطقی بنویسید؛ مثلاً از سطح بالا به پایین یا از کدهای کوچک به بزرگ.
• اگر خروجی‌ها متن هستند، حتماً آن‌ها را داخل علامت نقل‌قول قرار دهید.
• برای جلوگیری از خطا، مقدارهای متنی را دقیقاً مطابق داده‌های موجود در سلول‌ها بنویسید.
• در فایل‌های تیمی، بهتر است از پیام‌های پیش‌فرض واضح مثل «نامشخص»، «کد نامعتبر» یا «نیاز به بررسی» استفاده کنید.
• SWITCH برای جایگزینی IFهای تو در تو عالی است، اما برای جستجو در جدول‌های بزرگ همیشه بهترین گزینه نیست.

تفاوت تابع SWITCH با توابع مشابه

تفاوت SWITCH با IF

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

=IF(A2=1,"ثبت شده",IF(A2=2,"ارسال شده",IF(A2=3,"تحویل شده","نامشخص")))

=SWITCH(A2,1,"ثبت شده",2,"ارسال شده",3,"تحویل شده","نامشخص")

همان‌طور که می‌بینید، نسخه SWITCH خواناتر و کوتاه‌تر است.

تفاوت SWITCH با IFS

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

=IFS(A2>=90,"عالی",A2>=70,"خوب",A2>=50,"متوسط",TRUE,"ضعیف")

=SWITCH(TRUE,A2>=90,"عالی",A2>=70,"خوب",A2>=50,"متوسط","ضعیف")

تفاوت SWITCH با CHOOSE

تابع CHOOSE بر اساس شماره موقعیت، یک گزینه را انتخاب می‌کند. یعنی اگر عدد 1 بدهید، گزینه اول را برمی‌گرداند؛ اگر عدد 2 بدهید، گزینه دوم را برمی‌گرداند. اما SWITCH می‌تواند مقدارهای عددی یا متنی مختلف را بررسی کند و محدود به شماره موقعیت نیست.

=CHOOSE(A2,"شنبه","یکشنبه","دوشنبه")

=SWITCH(A2,1,"شنبه",2,"یکشنبه",3,"دوشنبه","نامعتبر")

تفاوت SWITCH با XLOOKUP

تابع XLOOKUP برای جستجو در جدول‌ها و محدوده‌ها استفاده می‌شود. اگر تعداد داده‌ها زیاد باشد یا بخواهید کدها و توضیحات را در یک جدول جداگانه نگهداری کنید، XLOOKUP انتخاب حرفه‌ای‌تری است. اما برای چند حالت محدود، SWITCH سریع‌تر و ساده‌تر نوشته می‌شود.

=XLOOKUP(A2,$F$2:$F$50,$G$2:$G$50,"نامعتبر")

=SWITCH(A2,"A","سطح یک","B","سطح دو","C","سطح سه","نامعتبر")

سازگاری با نسخه‌های مختلف اکسل

تابع SWITCH در نسخه‌های جدید اکسل پشتیبانی می‌شود و برای کاربران Microsoft 365 و نسخه‌های جدید اکسل گزینه‌ای مناسب و قابل اعتماد است.

• Microsoft 365: پشتیبانی می‌شود
• Excel 2024: پشتیبانی می‌شود
• Excel 2021: پشتیبانی می‌شود
• Excel 2019: پشتیبانی می‌شود
• Excel Online: پشتیبانی می‌شود
• Excel برای Mac در نسخه‌های جدید: پشتیبانی می‌شود
• Excel 2016: در برخی نسخه‌های به‌روزشده و اشتراکی ممکن است پشتیبانی شود، اما در نسخه‌های قدیمی‌تر همیشه در دسترس نیست
• Excel 2013 و قدیمی‌تر: معمولاً پشتیبانی نمی‌شود

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

سؤالات پرتکرار درباره تابع SWITCH

آیا SWITCH همان IF است؟

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

آیا SWITCH می‌تواند شرط‌های بزرگ‌تر و کوچک‌تر را بررسی کند؟

به صورت عادی SWITCH برای مقایسه برابر بودن است، اما با قرار دادن TRUE به عنوان expression می‌توانید شرط‌های منطقی و بازه‌ای را هم بررسی کنید.

=SWITCH(TRUE,A2>=100,"عالی",A2>=50,"قابل قبول","ضعیف")

اگر هیچ موردی مطابق نباشد چه اتفاقی می‌افتد؟

اگر مقدار پیش‌فرض تعریف نکرده باشید، اکسل خطای #N/A نمایش می‌دهد. برای جلوگیری از این خطا، در انتهای SWITCH یک نتیجه پیش‌فرض قرار دهید.

آیا SWITCH به حروف بزرگ و کوچک حساس است؟

در حالت معمول، SWITCH نسبت به بزرگ و کوچک بودن حروف انگلیسی حساس نیست. بنابراین بهتر است داده‌های متنی خود را استاندارد و یکدست نگه دارید.

چه زمانی بهتر است به جای SWITCH از XLOOKUP استفاده کنیم؟

وقتی تعداد حالت‌ها زیاد است یا اطلاعات شما در یک جدول مرجع نگهداری می‌شود، XLOOKUP انتخاب بهتری است. SWITCH برای تعداد حالت‌های محدود و ثابت مناسب‌تر است.

حداکثر چند حالت می‌توان در SWITCH تعریف کرد؟

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

جمع‌بندی و پیشنهاد یادگیری بعدی

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

مهم‌ترین کاربردهای SWITCH شامل تبدیل کدها به توضیحات، دسته‌بندی وضعیت‌ها، جایگزینی IFهای تو در تو و ساخت گزارش‌های خوانا است. همچنین با ترکیب آن با توابعی مثل SUM، AND، OR، COUNTIF و XLOOKUP می‌توانید فرمول‌های حرفه‌ای‌تری بسازید.

برای یادگیری بعدی پیشنهاد می‌شود توابع IF، IFS، XLOOKUP، COUNTIF و SUMIFS را هم یاد بگیرید؛ چون این توابع در کنار SWITCH، مجموعه‌ای قدرتمند برای تحلیل داده و ساخت گزارش‌های حرفه‌ای در اکسل ایجاد می‌کنند.