کاربردهای حرفه ای توابع اطلاعاتی

فرمول نویسی در Conditional Formatting و سایر ابزارهای اکسل

حتما تا بحال پیش اومده که بخوایم چک کنیم ببینیم که خروجی یک سلول، از جنس عدد هست یا متن؟ یا اینکه آیا خطا در سلول وجود داره یا نه؟ یک عدد زوج هست یا فرد؟ شاید به فکر خیلی ها نرسه که توابع اطلاعاتی در کارهای گرافیکی هم استفاده میشند! یا شاید میدونند که ممکنه کاربرد داشته باشه اما فرمول نویسی در Conditional Formatting یا Data Validation رو هنوز یاد نگرفتند که در این صورت این مقاله رو خوب بخونید.

اطلاعاتی از این قبیل رو میتونیم از طریق توابعی که در دسته Information قرار دارند، بدست بیاریم. توابع اطلاعاتی در اکسل توابعی هستن که عموما خروجی منطقی، یعنی True/False دارند. مثلا چک میکنه که آیا محتوای موجود در یک سلول، عددی هست یا نه و …

حالا بریم با ساختار برخی از این توابع و چند مثال از اونها، آشنا بشیم. توابعی که از این دسته انتخاب کردم برای این مقاله عبارتند از:

IsNumber

IsText

IsNontext

IsEven

IsOdd

IsBlank

IsFormula

ساختار همه این توابع با هم مشابه هست. یعنی همگی یک آرگومان دارند به شرح زیر:

Value: جنس ورودی توابع IsNumber, IsText, IsNontext و IsBlank از جنس Value هست. یعنی هم متن، هم عدد و هم سلول، میتونه ورودی این تابع باشه.

Number: جنس ورودی تابع IsEven و IsOdd عددی است. یا محدوده ای که شامل عدد است.

Reference: جنس ورودی تابع IsFormula فقط از جنس محدوده (range) هست.

کاربرد این توابع کجاست؟

به دلیل اینکه خروجی این توابع True/False هست، بیشتر در قسمت Logical Test یا شرط منطقی در فرمول نویسی Logical کاربرد دارند. همچنین در ابزارهایی مثل Conditional formatting یا Data Validation بسیار پرکاربرد هستن، چرا که منطق فرمول نویسی در این دو ابزار به گونه ای باید باشه که حتما خروجی True/False داشته باشیم. در ادامه چند مثال از این توابع حل میکنیم:

مثال اول: کاربرد تابع IsNumber

میخواهیم تنظیمی انجام بدیم که در یک سلول فقط عدد وارد بشه و داده متنی امکان ثبت نداشته باشه. همونطور که میدونیم ابزاری که ورود داده در اکسل رو کنترل میکنه ابزار Data validation هست. از طرفی میدونیم که منطق فرمول نویسی در این ابزار Logical هست. یعنی اگر فرمول نوشته شده، True بشه، امکان ثبت داده رو به ما میده و اگر False بشه، امکان ثبت داده به ما داده نمیشه. پس برای این کار، روی سلول A1 کلیک میکنیم و از تب Data/ Data validation/ Settings/ Custom فرمول زیر رو می نویسیم:

=IsNumber(A1)

شکل ۱- توابع اطلاعاتی-تابع IsNumber

حالا اگر در سلول A1 داده متنی وارد کنیم، خطا خواهد داد.

با همین منطق میتونیم متن بودن داده ورودی رو با تابع IsText چک کنیم.

مثال دوم- کاربرد توابع IsEven/ IsOdd

فرض کنید جدولی داریم از داده ها و میخواهیم یک در میون ردیف ها رو رنگ کنیم یا Border خاصی بدیم. برای این کار کافیه که ردیف های زوج یا ردیف های فرد جدول رو فرمت دهی کنیم. پس با توجه به این توضیحات متوجه میشیم که باید از ابزار Conditional Formatting استفاده کنیم. منطق فرمول نویسی در این ابزار هم مثل Data Validation منطق Logical هست. یعنی فرمولی باید در این ابزار بنویسیم که خروجی آن، True/False باشه. اگر True باشه، فرمت مورد نظر اعمال میشه و اگر False باشه، فرمت تعیین شده اعمال نمیشه.

برای رسیدن به این خروجی، اول از همه باید تشخیص بدیم که شماره ردیف یک سلول چنده و اینکه زوجه یا فرد. شماره ردیف رو تابع Row() و تشخیص فرد و زوج رو توابع IsOdd و IsEven انجام میده. پس تابعی که در این ابزار می نویسیم، به شرح زیر است:

=IsOdd(Row())

برای ادامه کار، محدوده مورد نظر رو انتخاب میکنیم و از تب Home/ Conditional Formatting/ New Rule در قسمت Use a formula to determine which cells to format فرمول بالا رو می نویسیم و فرمت دلخواه رو انتخاب میکنیم. (شکل ۲)

شکل ۲- توابع اطلاعاتی-تابع IsEven/IsOdd

وقتی Ok رو میزنیم، محدوده بصورت شکل ۳ فرمت دهی میشه و یکی از مزایای این روش، اینه که اگه ردیف حذف یا اضافه بشه، فرمت آپدیت میشه و همیشه الگوی یکی در میون حفظ خواهد شد.

شکل ۳- توابع اطلاعاتی-فرمت دهی یکی در میان با استفاده از تابع IsEven/IsOdd

برای این فرمول میشه شرط های بیشتری اضافه کرد، مثلا اینکه اگه سلول پر شد، فرمت مورد نظر اعمال بشه. برا یاین کار باید شرط پر بودن سلول رو هم به فرد بودن شماره ردیف اضافه کنیم. چون بیش از یک شرط داریم، متوجه میشیم که باید بریم سراغ تابع AND. در واقع تابع AND هست که هم شرط فرد بودن ردیف و هم شرط پر بودن سلول رو چک میکنه. برای حل این مسئله، مثال بعدی رو ملاحظه کنید.

مثال سوم کاربرد توابع IsBlank / IsEven / IsOdd

حالا میخوایم جدول رو بصورت یک درمیون فرمت دهی کنیم ولی این بار به شرط اینکه اولین سلول هر ردیف پر باشه. همونطور که میدونیم، تابع IsBlank چک میکنه که یک سلول خالیه یا نه. اما ما شرط پر بودن رو نیاز داریم. یعنی برعکس تابع IsBlank. برای این کار تابع IsBlank رو داخل تابع Not می نویسیم که نتیجه IsBlank رو برعکس کنه. در واقع تابع Not(IsBlank()) پر بودن سلول رو چک میکنه. پس حالا کافیه این دو شرط رو داخل یک تابع AND بنویسیم به شرح زیر:

=AND ( Not(IsBlank($A1)) , IsOdd(Row()))

حالا کافیه این فرمول رو داخل Conditional Formatting بنویسیم: (شکل ۴)

شکل ۴- توابع اطلاعاتی- تابع IsBlank و تابع Not

این فرمول دو شرط رو چک میکنه؛ یکی اینکه آیا شماره ردیف سلول مورد نظر فرد هست یا نه و اینکه آیا اولین سلول در هر ردیف (ستون A) پر هست یا نه. اگر هر دو شرط برقرار بود، فرمت تعیین شده اعمال میشه. به تصویر زیر دقت کنید.

مثال چهارم کاربرد تابع IsFormula

فرض کنید میخواهیم در یک شیت، هر سلولی که حاوی فرمول هست رو با رنگ مشخص کنیم. برای این کار کافیه محدوده مورد نظر رو انتخاب کرده و از تب Home/ Conditional Formatting/ New Rule در قسمت Use a formula to determine which cells to format فرمول زیر رو بنویسیم و فرمت دلخواه رو انتخاب کنیم.

=IsFormula(A1)

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

در این مقاله کاربرد برخی توابع Information مثل فرمول نویسی در Conditional Formatting تشریح شد، سعی کنید بقیه توابع این دسته رو بررسی کنید. مثلا دو تا از مهم ترین ها، توابع IsErr و IsError هست. حتما بررسی کنید که این دو تابع چه تفاوتی با هم دارند و چطور میتونیم از این توابع برای مدیریت خطا استفاده کنیم. همچنین بقیه توابع از این دسته که خروجی True/False ندارند رو هم بررسی کنید. توابع اطلاعاتی بیشتری در اکسل وجود دارند که کاربردهای متنوعی دارند، سعی کنید به مرور با این توابع و کاربردشان آشنا بشید.