توابع شرطی و متنی در اکسل را با مثال بیاموزید؛ از IF، AND، OR و IFERROR تا LEFT، RIGHT، MID، LEN، CONCAT، TEXTJOIN و TRIM.

مقدمه

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

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

در این آموزش، توابع IF پیشرفته، AND، OR، IFERROR، LEFT، RIGHT، MID، LEN، CONCAT، TEXTJOIN و TRIM را همراه با مثال‌های کاربردی بررسی می‌کنیم. یادگیری این توابع کمک می‌کند داده‌ها را سریع‌تر پاک‌سازی، تحلیل و برای تهیه گزارش آماده کنید.

تابع IF پیشرفته در اکسل

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

content_copy text

=IF(logical_test,value_if_true,value_if_false)

فرض کنید نمره دانش‌آموز در سلول B2 قرار دارد. فرمول زیر وضعیت او را مشخص می‌کند:

content_copy text

=IF(B2>=10,”قبول”,”مردود”)

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

content_copy text

=IF(B2>=17,”عالی”,IF(B2>=12,”متوسط”,”نیازمند تلاش”))

اکسل ابتدا بررسی می‌کند که نمره حداقل ۱۷ است یا خیر. اگر شرط درست باشد، عبارت «عالی» نمایش داده می‌شود. در غیر این صورت، شرط دوم بررسی خواهد شد. اگر نمره حداقل ۱۲ باشد، نتیجه «متوسط» و در غیر این صورت «نیازمند تلاش» خواهد بود.

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

تابع AND در اکسل

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

ساختار تابع AND عبارت است از:

content_copy text

=AND(logical1,logical2,…)

فرض کنید نمره دانش‌آموز در B2 و درصد حضور او در C2 ثبت شده است. برای قبولی، نمره باید حداقل ۱۰ و حضور حداقل ۷۵ درصد باشد:

content_copy text

=AND(B2>=10,C2>=75)

برای نمایش نتیجه‌ای قابل‌فهم، می‌توان AND را داخل IF قرار داد:

content_copy text

=IF(AND(B2>=10,C2>=75),”قبول”,”مردود”)

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

برای مثال، اگر مبلغ فروش در D2 بیشتر از ده میلیون و تعداد سفارش در E2 حداقل ۲۰ باشد، کارمند پاداش می‌گیرد:

content_copy text

=IF(AND(D2>10000000,E2>=20),”دارای پاداش”,”بدون پاداش”)

تابع OR در اکسل

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

ساختار تابع OR به‌شکل زیر است

content_copy text

=OR(logical1,logical2,…)

فرض کنید مشتریانی که خریدشان بیشتر از پنج میلیون تومان است یا عضویت ویژه دارند، تخفیف دریافت می‌کنند. مبلغ خرید در B2 و نوع عضویت در C2 قرار دارد:

content_copy text

=IF(OR(B2>5000000,C2=”ویژه”),”دارای تخفیف”,”بدون تخفیف”)

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

ترکیب AND و OR نیز امکان‌پذیر است؛ ولی هنگام ساخت فرمول‌های ترکیبی باید پرانتزها و ترتیب بررسی شرط‌ها را با دقت کنترل کنید.

تابع IFERROR برای مدیریت خطاها

برخی فرمول‌ها ممکن است خطاهایی مانند #DIV/0!، #N/A یا #VALUE! ایجاد کنند. نمایش این خطاها ظاهر گزارش را نامناسب می‌کند. تابع IFERROR اجازه می‌دهد به‌جای خطا، پیام یا مقدار دلخواه نمایش داده شود.

ساختار این تابع عبارت است از:

content_copy text

=IFERROR(value,value_if_error)

برای مثال، تقسیم مقدار A2 بر B2 در صورت صفر یا خالی بودن B2 خطا ایجاد می‌کند:

content_copy text

=A2/B2

برای مدیریت این خطا می‌توان نوشت:

content_copy text

=IFERROR(A2/B2,0)

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

content_copy text

=IFERROR(A2/B2,”امکان محاسبه وجود ندارد”)

تابع IFERROR در کنار توابع جست‌وجو نیز بسیار کاربردی است؛ زیرا در صورت پیدا نشدن مقدار، می‌تواند پیام مناسبی مانند «یافت نشد» نمایش دهد. بااین‌حال، بهتر است ابتدا علت خطا بررسی شود؛ زیرا IFERROR فقط نحوه نمایش خطا را تغییر می‌دهد و مشکل اصلی داده‌ها را برطرف نمی‌کند.

تابع LEFT در اکسل

تابع LEFT تعداد مشخصی کاراکتر را از سمت چپ یک متن استخراج می‌کند. ساختار این تابع به‌صورت زیر است:

content_copy text

=LEFT(text,num_chars)

اگر کد محصول PRD-1402-25 در سلول A2 قرار داشته باشد، فرمول زیر سه حرف اول را برمی‌گرداند:

content_copy text

=LEFT(A2,3)

نتیجه این فرمول PRD خواهد بود. تابع LEFT برای استخراج پیش‌شماره، کد استان، بخش ابتدایی شماره حساب و حروف اول کدهای سازمانی مفید است.

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

content_copy text

=LEFT(A2)

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

تابع RIGHT در اکسل

تابع RIGHT مشابه LEFT است، با این تفاوت که کاراکترها را از سمت راست متن استخراج می‌کند:

content_copy text

=RIGHT(text,num_chars)

اگر کد سفارش ORD-2025-4587 در سلول A2 قرار داشته باشد، فرمول زیر چهار کاراکتر پایانی را نمایش می‌دهد:

content_copy text

=RIGHT(A2,4)

نتیجه برابر 4587 خواهد بود. این تابع برای استخراج رقم‌های پایانی شماره تماس، شناسه سفارش، سال ثبت، کد شعبه و قسمت انتهایی یک شناسه کاربرد دارد.

توابع LEFT و RIGHT متن اصلی را تغییر نمی‌دهند؛ بلکه بخش موردنظر را در سلولی که فرمول داخل آن نوشته شده است نمایش می‌دهند. اگر متن اصلی تغییر کند، نتیجه فرمول نیز خودکار به‌روزرسانی خواهد شد.

تابع MID در اکسل

تابع MID بخشی از متن را از یک موقعیت مشخص استخراج می‌کند. برخلاف LEFT و RIGHT، استخراج در این تابع می‌تواند از میان متن آغاز شود.

ساختار تابع MID عبارت است از:

content_copy text

=MID(text,start_num,num_chars)

آرگومان اول متن، آرگومان دوم شماره کاراکتر شروع و آرگومان سوم تعداد کاراکترهای موردنیاز است.

اگر مقدار AB-1403-258 در سلول A2 باشد، فرمول زیر سال را استخراج می‌کند:

content_copy text

=MID(A2,4,4)

استخراج از کاراکتر چهارم آغاز می‌شود و چهار کاراکتر ادامه می‌یابد؛ بنابراین نتیجه 1403 خواهد بود. شمارش کاراکترها از عدد یک شروع می‌شود و فاصله، خط تیره و علامت‌ها نیز هرکدام یک کاراکتر محسوب می‌شوند.

تابع MID برای جدا کردن بخش میانی کد ملی، شماره پرونده، کد محصول و اطلاعات دارای ساختار ثابت بسیار مناسب است.

تابع LEN در اکسل

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

ساختار آن بسیار ساده است:

content_copy text

=LEN(text)

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

content_copy text

=LEN(A2)

LEN برای کنترل طول کدها و شناسایی داده‌های ناقص کاربرد دارد. برای مثال، فرمول زیر بررسی می‌کند که کد واردشده دقیقاً ده کاراکتر دارد یا خیر:

content_copy text

=IF(LEN(A2)=10,”معتبر”,”نیازمند بررسی”)

اگر در ابتدا یا انتهای متن فاصله اضافی وجود داشته باشد، LEN آن را نیز می‌شمارد. به همین دلیل ممکن است دو عبارت ظاهراً یکسان، طول متفاوتی داشته باشند. برای حذف فاصله‌های ناخواسته می‌توان از تابع TRIM استفاده کرد.

توابع CONCAT و TEXTJOIN

تابع CONCAT چند متن یا مقدار را به یکدیگر متصل می‌کند. فرض کنید نام در A2 و نام خانوادگی در B2 قرار دارد:

content_copy text

=CONCAT(A2,” “,B2)

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

تابع TEXTJOIN امکانات بیشتری دارد و اجازه می‌دهد یک جداکننده مشخص بین چند متن قرار دهیم:

content_copy text

=TEXTJOIN(” – “,TRUE,A2:C2)

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

برای مثال، اگر نام شهر، استان و کشور در سه سلول جدا باشند، TEXTJOIN می‌تواند آن‌ها را با ویرگول ترکیب کند:

content_copy text

=TEXTJOIN(“، “,TRUE,A2:C2)

تفاوت اصلی این است که CONCAT متن‌ها را مستقیماً متصل می‌کند، اما TEXTJOIN مدیریت جداکننده‌ها و سلول‌های خالی را ساده‌تر می‌سازد.

تابع TRIM در اکسل

تابع TRIM فاصله‌های اضافی متن را حذف می‌کند. این تابع فاصله‌های ابتدای و انتهای متن را پاک کرده و فاصله‌های متعدد میان کلمات را به یک فاصله تبدیل می‌کند.

ساختار آن به‌شکل زیر است:

content_copy text

=TRIM(text)

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

content_copy text

=TRIM(A2)

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

توجه داشته باشید که TRIM همیشه همه کاراکترهای نامرئی را حذف نمی‌کند. داده‌های دریافت‌شده از اینترنت ممکن است دارای فاصله‌های خاص باشند که برای پاک‌سازی آن‌ها به توابعی مانند CLEAN یا SUBSTITUTE نیاز است.

تمرین عملی توابع شرطی و متنی در اکسل

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

1. با CONCAT نام و نام خانوادگی را ترکیب کنید.
2. فاصله‌های اضافی نام‌ها را با TRIM حذف کنید.
3. تعداد کاراکترهای کد پرسنلی را با LEN بررسی کنید.
4. حروف ابتدایی کد را با LEFT استخراج کنید.
5. چهار رقم پایانی کد را با RIGHT نمایش دهید.
6. بخش میانی کد را با MID جدا کنید.
7. با ترکیب IF و AND کارکنان واجد پاداش را مشخص کنید.
8. با OR مشتریان ویژه یا دارای خرید بالا را شناسایی کنید.
9. خطاهای محاسبات را با IFERROR مدیریت کنید.
10. اطلاعات تماس را با TEXTJOIN در یک سلول قرار دهید.

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

جمع‌بندی

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

در بخش توابع متنی یاد گرفتیم که LEFT، RIGHT و MID قسمت‌های مشخصی از متن را استخراج می‌کنند. LEN تعداد کاراکترها را می‌شمارد، CONCAT و TEXTJOIN اطلاعات را ترکیب می‌کنند و TRIM فاصله‌های اضافی را حذف می‌کند.

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