برای فرمول‌نویسی حرفه‌ای در اکسل باید با توابع شرطی نظیر IF و AND و OR و همین‌طور انواع توابعی که نامشان با کلمه‌ی IS شروع می‌شود نظیر ISBLANK آشنایی داشته باشیم تا فرمول‌های مفید و کاربردی و بدون ارور بنویسیم.
در این مقاله به معرفی مهم‌ترین توابع که کاربرد زیادی دارند می‌پردازیم و با بررسی مثال‌های ساده از جمله مثال های تابع if در اکسل و مدیریت سلول های خالی و سلول‌های حاوی عدد صفر و رفع خطای #div/0 در اکسل را توضیح می‌دهیم.
فرمول if و and در اکسل
فرض کنید که جدولی حاوی اطلاعات کارکنان یک سازمان در اختیار شماست و می‌خواهید با فرمول‌نویسی، اطلاعات را سریع‌تر تحلیل کنید. تابع IF ساده‌ترین تابع شرط در اکسل هست و اگر به تابع if چند شرطی در اکسل داشته باشید، می‌توانید از سایر توابع شرط مثل AND استفاده کنید.
به عنوان مثال فرض کنید که می‌خواهید سن افراد که در ستون Age درج شده را بررسی کنید و افرادی که سن بالاتر از ۱۸ سال دارند و در عین حال گواهی درجه طلایی دارند را پیدا کنید. گواهی موردبحث در ستون D درج شده است.
۲ شرط متفاوت داریم و تابع AND مناسب است، داخل تابع کافی است تعدادی شرط به صورت جداگانه با ویرگول نوشته شود و خروجی تابع، در صورتی که تمام شرایط برقرار باشد، True و در غیر این صورت False است. بنابراین در ستون جدیدی مثل F و در واقع سلول F2 از این فرمول استفاده می‌کنیم:
=AND(B2>18,D2="Gold")
می‌توانید این فرمول را برای ردیف‌های بعدی با تغییر ارجاع استفاده کنید که در واقع با کپی و پیست کردن به روش‌های مختلف انجام می‌شود.
استفاده از OR به جای تابع if چند شرطی در اکسل
مثال دیگر از فرمول if در اکسل با دو شرط این است که اگر یکی از چند شرط برقرار باشد، نتیجه مثبت باشد و اگر هیچ کدام از شرایط برقرار نبود، نتیجه منفی است. برای این حالت از OR استفاده می‌کنیم. و اما مثالی ساده، فرض کنید که می‌خواهیم افرادی که در کشوری دیگر کار می‌کنند یا گواهی رانندگی دارند را پیدا کنیم که این اطلاعات در دو ستون E و C مثال ما درج شده است. لذا مثال تابع OR در اکسل را با این فرمول تکمیل می‌کنیم:
=OR(C2="Full",E2="YES")
معنی این فرمول این است که اگر محتوای سلول C2 یا E2 کلمه‌ی Full باشد، خروجی True و اگر هیچ کدام از این شرایط برقرار نباشد، نتیجه False است.
فرمول NOT در اکسل
تابع NOT درست برعکس تابع IF است به این صورت که اگر شرطی که داخل تابع می‌نویسیم، برقرار نباشد، خروجی آن True است و در غیر این صورت False است.
یک مثال ساده این است که می‌خواهیم افرادی که سن بیش از 50 ندارند را شناسایی کنیم. برای این کار می‌توانید از فرمول زیر استفاده کنید:
NOT(B2>50)
فرمول نویسی با تابع AND و OR و تابع if با سه شرط در اکسل
در نهایت به ترکیبی از چند شرط با توابع مختلف می‌رسیم که لازمه‌ی فرمول‌نویسی برای کارهای حرفه‌ای‌تر در Excel است. به عنوان مثال فرض کنید که می‌خواهیم افرادی که در کشوری دیگر کار می‌کنند و توان رانندگی کردن و مسافرت دارند را مشخص کنیم و نتیجه را به جای True و False با یک جمله مشخص کنیم. برای این کار ترکیب OR با IF ضروری است. به عبارت دیگر شرط را با OR می‌نویسم و اگر برقرار بود، جمله‌ی Can travel به معنی توانایی مسافرت کردن و در در حالت برعکس جمله‌ی Cannot trave نمایش داده می‌شود. فرمول موردبحث به صورت زیر است:
=IF(OR(C2="Full",E2="YES"),"Can travel","Cannot travel")
یا مثال دیگر این است که می‌خواهیم افراد مبتدی و حرفه‌ای را با توجه به گواهی و سن مشخص کنیم. سن بالای ۱۸ سال و داشتن گواهی درجه‌ی Gold به معنی حرفه‌ای یا Senior بودن شخص است و در غیر این صورت فرد جزو افراد مبتدی‌تر یا Junior محسوب می‌شود. برای این مثال نیز تابع AND به همراه IF مناسب است:
=IF(AND(B2>18,D2="Gold"),"Senior","Junior")
با همین روش می‌توان تابع NOT و IF را ترکیب کرد و خلاصه دستور شرط بین دو عدد در اکسل یا دستور شرط بین چند متن و کلمه در اکسل به سادگی نوشته می‌شود و جداولی حرفه‌ای از اطلاعات ایجاد می‌شود. فراموش نکنید که با استفاده از فرمت کردن شرطی می‌توان رنگ سلول‌هایی که عبارت یا عدد خاصی داخل آنهاست را تغییر داد و این به شناسایی افراد موردنظر کمک می‌کند.
تابع IFERROR برای رفع خطا نظیر رفع خطای #div/0 در اکسل
یکی دیگر از توابع بسیار کاربردی Excel، تابعی به اسم IFERROR است که به مدیریت کردن خطاها کمک می‌کند. به عنوان مثال اگر عدد را بر صفر تقسیم کنید یا سلول موردنظر حاوی عدد نباشد و به رفع خطای value در اکسل یا رفع خطای num در اکسل نیاز داشته باشید، بسیاری از این موارد ارور با تابع IFERROR قابل مدیریت کردن است.
و اما یک مثال ساده، فرض کنید که سلولی در ستون B حاوی صفر است و نتیجه‌ی تقسیم کردن، ارور است. به عنوان مثال خروجی فرمول زیر ارور #DIV/0 است:
=SUM(C2/B2)
نمایش ارور ظاهر جدول را خراب می‌کند و بهتر است به جای ارور، متن یا علامت خاصی نمایش داده شود.
برای جلوگیری از نمایش ارور می‌توانید از دستور زیر استفاده کنید که در صورت صفر بودن مخرج کسر، علامت - چاپ می‌شود.
=IFERROR(SUM(C2/B2),"-")
فراموش نکنید که این فرمول را در همه‌ی سلول‌های ردیف D پیست کنید.
توابع شرطی ISBLANK و ISERROR و غیره در Excel
اکسل تعداد زیادی تابع شرطی برای چک کردن محتوای سلول یا خروجی فرمول‌ها دارد که با کلمه‌ی is آغاز می‌شوند. به عنوان مثال شاید به تابع چک کردن سلول خالی در اکسل نیاز دارید که ISBLANK است و خروجی آن صحیح و غلط است. برخی از مهم‌ترین توابع IS در اکسل به همراه کاربردشان به صورت زیر است:
- ISERROR: اگر خروجی فرمول خطا و ارور باشد، نتیجه True و اگر خطایی وجود نداشته باشد، False است.
- ISERR: اگر اروری به جز #N/A وجود داشته باشد، خروجی صحیح و در غیر این صورت False است.
- ISNA: اگر ارور #N/A وجود داشته باشد خروجی صحیح است و در غیر این صورت False را برمی‌گرداند.
- ISFORMULA: اگر ورودی تابع از نوع فرمول باشد، خروجی این تابع صحیح و در غیر این صورت غلط است.
- ISLOGICAL: این تابع وجود دستور شرطی را در عبارت داده شده به آن بررسی می‌کنید و True یا False را برمی‌گرداند.
- ISTEXT: اگر مقدار داده شده به تابع از نوع متن یا فرمولی با خروجی متنی باشد، نتیجه True است و در غیر این صورت False را برمی‌گرداند.
- ISNONTEXT: درست برعکس تابع ISTEXT است و اگر مقدار داده شده به آن متن نباشد، نتیجه True است و در غیر این صورت False خروجی این تابع است.
- ISNUMBER: خروجی این تابع در صورت عددی بودن ورودی، True است و در غیر این صورت False را برمی‌گرداند.
- ISEVEN: اگر عدد زوج یا سلول خالی به این تابع بدهیم، خروجی آن True است و در غیر این صورت False را برمی‌گرداند.
- ISODD: این تابع فرد بودن ورودی را با true مشخص می‌کند و اگر عدد فرد نباشد، False خروجی آن است. توجه کنید که اگر سلول غیرعددی به این تابع بدهید، خروجی آن ارور است!
مثالی از خروجی این توابع در بررسی سلول‌های ستون A به صورت زیر است. به عنوان مثال ستون E را در نظر بگیرید که خروجی تابع ISNA را نمایش می‌دهد. با توجه به اینکه در سلول A3 عبارت #N/A دیده می‌شود، خروجی فرمول زیر True خواهد بود:
=ISNA(A3)
و همین فرمول برای سایر ردیف‌ها، نتیجه‌ی False خواهد داشت.
در این مقاله به صورت مفید و مختصر با بررسی مثال‌های کاربردی، به روش نوشتن دستور شرط بین اعداد و متن با IF و AND و OR و NOT در اکسل پرداختیم و توابعی برای مدیریت خطاها معرفی کردیم. برای تسلط بر فرمول نویسی در اکسل با IF و غیره، به تمرین و مطالعه بیشتر نیاز است.
اگر به اکسل به صورت آنلاین نیاز دارید، بهتر است از گوگل شیتس استفاده کنید، خوشبختانه فرمول نوشتن در Google Sheets که سرویس آنلاین گوگل برای کاربران Excel است، تقریباً با اکسل یکسان است و نیازی به آموزش اصول ابتدایی نیست.
howtogeekاینتوتک