سبد خرید
0

هیچ محصولی در سبد خرید نیست.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

فرمول نویسی در Conditional Formatting
۳.۷/۵ - (۳ امتیاز)

فرمول نویسی در 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

شکل ۱- توابع اطلاعاتی-تابع 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

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

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

توابع اطلاعاتی-فرمت دهی یکی در میان-تابع Iseven/Isodd

شکل ۳- توابع اطلاعاتی-فرمت دهی یکی در میان با استفاده از تابع 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

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

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

استفاده از تابع IsBlank در ابزار Conditional Formatting

نکته:
مبحث $ در فرمول نویسی بسیار بسیار مهم و تعیین کننده هست. حتما مقاله مربوط به این موضوع رو مطالعه کنید.

 

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

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

=IsFormula(A1)

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

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

133

من سامان چراغی هستم. دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم.

دیدگاه کاربران
  • حمید ۲۳ دی ۱۴۰۰ / ۴:۳۳ ب٫ظ

    سلام در اکسل من میخام وقتی یک تاریخ در یک سل به ثبت می‌رسونم ، ردیف آن تاریخ با ثبت کردن تاریخ کلا ، مثلا سبز بشه چیکار باید بکنم
    ممنون

    • سامان چراغی ۲۳ دی ۱۴۰۰ / ۵:۴۳ ب٫ظ

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

  • مهدی بغدادی ۱۴ فروردین ۱۳۹۹ / ۲:۴۲ ب٫ظ

    سلام
    در استفاده از دستور INDIRECT مشکل دارم.فقط در سطر ابتدا قابل استفاده است و در ردیف های دیگر همان گزینه های مشخص شده در ردیف اول را نشان می دهد و می بایستی مابقی سطر ها بصورت دستی تغییر مقادیر داده شود.
    لطفا” راهنمایی کنید.با تشکر

  • حمید ۲۵ اسفند ۱۳۹۸ / ۰:۰۲ ق٫ظ

    سلام خسته نباشید من میخوام ی لیست بزارم که وقتی انتخاب میکنی سل روبروش قیمتش بیاد
    مثلا لیست داخلش باشه سیب پرتقال کیوی هر کدوم رو انتخاب میکنم سل جلوش قیمت کیلوش رو بیاره
    ممنون میشم کمکم کنید

    • سامان چراغی ۲۵ اسفند ۱۳۹۸ / ۷:۲۹ ق٫ظ

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

  • سیدسجادشیرمردی ۳۰ آذر ۱۳۹۸ / ۲:۳۳ ب٫ظ

    سلام در مبحث دیتا ولیدیشن برای یک ستون خاص مثل کدملی من می خوام دو شرط بگذارم
    یکی شرط اینکه فقط عدد باشن و دیگری تکراری نبلشن
    برای جدا کردن شرط ها در قسمت custom و قسمت formual چه چیزی رو باید بنویسم

    • آواتار
      حسنا خاکزاد ۳۰ آذر ۱۳۹۸ / ۵:۳۸ ب٫ظ

      درود بر شما
      بیش از یک شرط رو با تابع AND وارد میکنیم
      یک شرط عدد بودن و دیگری تکراری نبودن

      • سیدسجادشیرمردی ۱ دی ۱۳۹۸ / ۱۱:۰۰ ب٫ظ

        سلام با and زدم
        درست نشد در صورتی که هر کدوم از شرط ها به تنهایی کار رو درست انجام میده
        اگه لطف کنید کدش رو بگذارید ممنونم

        • آواتار
          حسنا خاکزاد ۲ دی ۱۳۹۸ / ۹:۳۶ ق٫ظ

          این فرمول رو در دیتا ولیدیشن روی سلول A1 واردکردم و درست کار میکنه

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان