تابع SWITCH در اکسل – نحوه استفاده، فرمول و مثال‌ های کاربردی

در ادامه مطلب آموزشی گسترش اندیشه پویا، با مفاهیم شبکه آشنا می‌شویم:

تابع SWITCH یکی از توابع منطقی اکسل است که بعد از مقایسه یک مقدار مشخص با فهرستی از مقادیر دیگر در جدول، اولین مقدار منطبق را به‌عنوان نتیجه برمی‌گرداند.همچنین اگر مقدار مشابهی پیدا نکند، یک عبارت پیش‌فرض مانند «پیدا نشد» را نشان می‌دهد. این تابع که از نسخه ۲۰۱۶ اکسل به این نرم‌افزار اضافه شده است، در بیشتر موارد به‌عنوان جایگزین تابع شرطی IF تودرتو به‌کار می‌رود. این تابع در مواردی مانند دسته‌بندی داده‌ها، تعیین قوانین شرطی چند حالته و تبدیل کدهای سیستمی به عبارت‌های خوانا نیز کاربرد دارد. در این مطلب از مجله فرمول کلی تابع SWITCH در اکسل و نحوه کار با آن در دو سطح ساده و پیشرفته را همراه مثال‌های مختلف یاد می‌گیریم.

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

فرمول کلی تابع SWITCH در اکسل

فرمول کلی تابع SWITCH در اکسل به شرح زیر است.

=SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])

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

expression(اجباری): مقداری که تابع قرار است آن را مقایسه کند. این مقدار می‌تواند عدد، تاریخ، متن یا آدرس سلول باشد.value1،value2و سایر موارد مشابه تا آرگومانvalue126(اجباری):مقادیری که با آرگومانexpressionمقایسه می‌شوند.result1،result2و سایر موارد مشابه تا آرگومانresult126(اجباری): نتایجی که تابع در صورت تطبیق مقایسه برمی‌گرداند.default(اختیاری):مقداری که تابع در صورت عدم تطابق مقایسه برمی‌گرداند.

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

از آنجا که ممکن استفرمول‌نویسیبا تابع SWITCH در اکسل کمی پیچیده به‌نظر برسد، بررسی مثال‌ها در ادامه بحث راهنمای خوبی خواهد بود. با این حال پیشنهاد می‌کنیم علاوه بر این مثال‌ها،فیلم آموزش توابع و فرمول‌نویسی در اکسلدر را نیز مشاهده کنید.

مثال های کاربردی استفاده از تابع SWITCH در اکسل

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

۱. فرمول‌نویسی ساده با تابع SWITCH

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

مثال اول: ثبت وضعیت سفارش‌های فروشگاه

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

REG: سفارش ثبت شدهPAY: پرداخت شدهSEND: ارسال شدهDEL: تحویل داده شدهCAN: لغو شده

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

با استفاده از تابع SWITCH فرمول زیر را برای ردیف اول جدول در بخش «توضیح وضعیت» می‌نویسیم.

=SWITCH(C2, "REG", "ثبت شده", "PAY", "پرداخت شده", "SEND", "ارسال شده", "DEL", "تحویل داده شد", "CAN", "لغو شده", "نامشخص")

بعد از کپی کردن فرمول در ردیف‌های دیگر نتیجه به‌صورت تصویر زیر در می‌آید.

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

=IF(C2="REG","ثبت شده",IF(C2="PAY","پرداخت شده",IF(C2="SEND","ارسال شده",IF(C2="DEL","تحویل داده شد",IF(C2="CAN","لغو شده","نامشخص")))))

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

مثال دوم: ثبت خودکار تعرفه هزینه‌های پستی

جدولی از تعرفه ارسال پستی پست از تهران برای سه استان را مانند تصویر زیر در اختیار داریم.

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

=SWITCH(B2, "نامشخص", 98000, "مشهد", 45000, "شیراز", 57000, "اصفهان")

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

۲. فرمول نویسی پیشرفته با تابع SWITCH

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

مثال اول: محاسبه تاریخ تاخیر پروژه

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

مدیر پروژه برای پیگیری وظایف می‌خواهد به‌صورت خودکار مشخص کند که با توجه به تاریخ روز، به‌عنوان مثال «۲۶ اردیبهشت ۱۴۰۵»، آیا هر فرد وظایف خود را سرموعد تحویل می‌دهد یا خیر. او برای این کار با استفاده از تابع TODAY، تابع DAYS و تابع SWITCH فرمول زیر را می‌نویسد.

=SWITCH(DAYS(C2, TODAY()),
0, "سررسید امروز",
-1, "سر وقت (یک روز زودتر)",
-2, "سر وقت (دو روز زودتر)",
1, "تاخیر (یک روز)",
2, "تاخیر (دو روز)",
3, "تاخیر (سه روز)",
"تاخیر زیاد یا بدون برنامه")

در این فرمول ابتدا تابع TODAY تاریخ امروز را برمی‌گرداند. سپس تابع DAYS اختلاف روزهای بین تاریخ مشخص شده پروژه در جدول و تاریخ امروز را محاسبه می‌کند. اگر اختلاف آن برابر عدد «۱» باشد، یعنی تاریخ پروژه یک روز از امروز عقب‌تر است یا یک روز تاخیر دارد. اما اگر اختلاف «۱-» شود، یعنی تاریخ پروژه یک روز از امروز جلوتر است و سر موعد انجام می‌شود. به همین شکل برای تاخیرهای بالاتر از یک روز عبارت «تاخیر زیاد» را نشان می‌دهد.

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

مثال دوم: کدبندی فصل بودجه

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

طبق این دسته‌بندی می‌خواهیم با استفاده از تابع SWITCH فرمولی بنویسیم که برای سه ماهه اول عبارت «Q1»، سه ماهه دوم عبارت «Q2» و برای موارد بعدی به ترتیب عبارت «Q3» و «Q4» را نشان دهد. بنابراین لازم است ابتدا با کمک تابع MONTH ماه هر فاکتور را استخراج کنیم و بعد از آن با تابع SWITCH عبارت مربوط را در جدول درج کنیم. در این حالت فرمول را به شکل زیر می‌نویسیم.

=SWITCH(MONTH(B2),4, "Q1",5, "Q1",6, "Q1",7, "Q2",8, "Q2",9, "Q2",10, "Q3",11, "Q3",12, "Q3",1, "Q4",2, "Q4",3, "Q4","خطا")

در این فرمول ماه آپریل تا ژوئن سه ماهه اول مالی است و به همین ترتیب بقیه فاکتور بر حسب ماه دسته‌بندی می‌شوند. به این شکل که ابتدا تابع MONTH ماه میلادی را از تاریخ فاکتور استخراج می‌کند. سپس مطابق فرمول اگر عدد ماه مقدار «۴»، «۵» یا «۶» را داشته باشد، عبارت «Q1» به‌عنوان خروجی توسط تابع SWITCH نمایش داده می‌شود. به همین ترتیب برای بقیه ماه‌ها عبارت‌های «Q2» تا «Q4» به‌صورت خودکار در جدول نوشته می‌شوند.

براینصب اپلیکیشنرایگانمجله کلیک کنید.

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

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

برای شروع مسیر پیشنهاد اول مشاهده فیلم‌های منتخب آموزشی زیر است.

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

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

مجموعه فیلم آموزش توابع و فرمول‌ در اکسل در فرادرسمجموعه فیلم آموزش اکسل برای کسب و کار در فرادرس

پیام های خطا در فرمول نویسی با تابع SWITCH در اکسل

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

بر خلاف انتظار، تابع SWITCH در محاسبات به شکل برگشتی کار می‌کند. به این معنی که حتی در صورت پیدا کردن اولین مقدار منطبق، ارزیابی بقیه داده‌ها را متوقف نمی‌کند و تا انتها ادامه می‌دهد. این عملکرد به‌خصوص در محاسبات پیچیده عملیات را کند می‌کند. بسیاری از موارد با مشاهده پیام خطای#NUM!در چنین مواردی بهتر است از تابع IF تودرتو استفاده کنیم.اگر در فرمول تابع SWITCH آرگومان پیش‌فرضdefaultکه نشان‌دهنده پیدا نکردن تطابق است را تعریف نکنیم، پیام خطای#N/Aرا می‌بینیم. بنابراین بهتر است همیشه با توجه به نوع داده‌ها عبارتی پیش‌فرض را برای عدم تطابق در فرمول تعریف کنیم.اگر فرمول نوشته شده با تابع SWITCH در نسخه‌های جدید اکسل را در نسخه‌های قدیمی ویرایش کنیم، با خطای#NAME?روبرو می‌شویم. بنابراین لازم است در زمان انتقال فایل‌های اکسل به نسخه‌های قدیمی‌تر، این نکته را در نظر بگیریم.

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

مجموعه فیلم آموزش توابع و فرمول در اکسل – مقدماتی تا پیشرفتهفیلم آموزش ترفندهای کاربردی در اکسل Excel + گواهینامهمجموعه فیلم آموزش اکسل برای کسب و کارفرمول های اکسل مهم که باید بلد باشید – معرفی ۳۵ تابعآموزش ترکیب if و and در اکسل – ساخت شرط های چندگانه از صفر تا صد

گسترش اندیشه پویا از سال ۱۳۸۲ در حوزه مشاوره فناوری اطلاعات و آموزش تخصصی فعالیت می‌کند. برای دریافت مشاوره با ما تماس بگیرید.