فرمول نویسی در 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 چک کنیم.
میتونیم پیام اخطار رو مطابق سلیقه خودمون تنظیم کنیم. برای دیدن جزئیات بیشتر مقاله مربوط به Data Validation رو مطالعه کنید.
مثال دوم- کاربرد توابع 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 ندارند رو هم بررسی کنید. توابع اطلاعاتی بیشتری در اکسل وجود دارند که کاربردهای متنوعی دارند، سعی کنید به مرور با این توابع و کاربردشان آشنا بشید.
سلام در اکسل من میخام وقتی یک تاریخ در یک سل به ثبت میرسونم ، ردیف آن تاریخ با ثبت کردن تاریخ کلا ، مثلا سبز بشه چیکار باید بکنم
ممنون
سلام
برای انجام این کار میتونید از Conditional Formatting استفاده کنید.
سلام
در استفاده از دستور INDIRECT مشکل دارم.فقط در سطر ابتدا قابل استفاده است و در ردیف های دیگر همان گزینه های مشخص شده در ردیف اول را نشان می دهد و می بایستی مابقی سطر ها بصورت دستی تغییر مقادیر داده شود.
لطفا” راهنمایی کنید.با تشکر
درود
ننوشتید ورودی تابع چی هست و چطور نوشته شده. به نظر میرسه $ ها رو باید اصلاح کنید
این دو مقاله رو بخونید، احتمالا حل میشه مشکلتون
https://excelpedia.net/address-function/
https://excelpedia.net/cell-address/
سلام خسته نباشید من میخوام ی لیست بزارم که وقتی انتخاب میکنی سل روبروش قیمتش بیاد
مثلا لیست داخلش باشه سیب پرتقال کیوی هر کدوم رو انتخاب میکنم سل جلوش قیمت کیلوش رو بیاره
ممنون میشم کمکم کنید
سلام، ممنون
برای این کار از تابع Vlookup استفاده کنید.
سلام در مبحث دیتا ولیدیشن برای یک ستون خاص مثل کدملی من می خوام دو شرط بگذارم
یکی شرط اینکه فقط عدد باشن و دیگری تکراری نبلشن
برای جدا کردن شرط ها در قسمت custom و قسمت formual چه چیزی رو باید بنویسم
درود بر شما
بیش از یک شرط رو با تابع AND وارد میکنیم
یک شرط عدد بودن و دیگری تکراری نبودن
سلام با and زدم
درست نشد در صورتی که هر کدوم از شرط ها به تنهایی کار رو درست انجام میده
اگه لطف کنید کدش رو بگذارید ممنونم
این فرمول رو در دیتا ولیدیشن روی سلول A1 واردکردم و درست کار میکنه