اکسل پدیا، دانشنامه پارسی اکسل

021-47625755
info@excelpedia.net

ورود یا ثبت نام

  • دانشنامه اکسل
    • اکسل چیست؟
    • اکسل مقدماتی
    • توابع اکسل
    • نمودار ها
    • وی بی – ماکرو
  • آکادمی اکسل
    • ویدئوهای آموزشی
      • رسم نمودارهای حرفه ای
      • اکسل و شروع حرفه ای
      • اکسل نینجا (اکسل پیشرفته)
      • صفر تا صد Pivot Table
      • برنامه نویسی وی بی (VBA)
    • کتاب ها
      • کتاب های فارسی
      • کتاب های انگلیسی
  • داشبورد های مدیریتی
    • آموزش ساخت داشبورد
    • دانلود داشبورد نمونه
  • افزونه های کاربردی
  • دوره های حضوری
    • اکسل نینجا
    • برنامه نویسی وی بی (VBA)
    • Cart

      0

توابع اکسل

  • خانه
  • بلاگ
  • توابع اکسل
  • کاربردهای حرفه ای توابع اطلاعاتی

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

  • ارسال شده توسط سامان چراغی
  • دسته بندی توابع اکسل
  • تاریخ ۱۲ آذر ۱۳۹۸
  • نظرات ۸ دیدگاه ها
فرمول نویسی در Conditional Formatting
5 / 5 ( 2 امتیاز )

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

حتما تا بحال پیش اومده که بخوایم چک کنیم ببینیم که خروجی یک سلول، از جنس عدد هست یا متن؟ یا اینکه آیا خطا در سلول وجود داره یا نه؟ یک عدد زوج هست یا فرد؟ شاید به فکر خیلی ها نرسه که توابع اطلاعاتی در کارهای گرافیکی هم استفاده میشند! یا شاید میدونند که ممکنه کاربرد داشته باشه اما فرمول نویسی در 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

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

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

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

نکته:
میتونیم پیام اخطار رو مطابق سلیقه خودمون تنظیم کنیم. برای دیدن جزئیات بیشتر مقاله مربوط به Data Validation رو مطالعه کنید.

 

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

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

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

حتما بخوانید:  تابع Choose در اکسل با کاربردی جالب

=IsOdd(Row())

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

توابع اطلاعاتی-تابع Iseven/Isodd

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

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

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

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

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

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

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

حتما بخوانید:  چرا تابع Vlookup درست کار نمیکنه؟

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

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

توابع اطلاعاتی -تابع Isblank-not

شکل 4- توابع اطلاعاتی- تابع 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 ندارند رو هم بررسی کنید. توابع اطلاعاتی بیشتری در اکسل وجود دارند که کاربردهای متنوعی دارند، سعی کنید به مرور با این توابع و کاربردشان آشنا بشید.

برچسب:Conditional Formatting, Data Validation, تابع IsBlank, تابع IsEven, تابع IsFormula, تابع IsNontext, تابع IsNumber, تابع IsOdd, تابع IsText, تابع NOT

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

مطلب قبلی

توابع بانک اطلاعاتی توابع شرطی در اکسل
۱۲ آذر ۱۳۹۸

مطلب بعدی

جستجو و فراخوانی عکس در اکسل
۱۲ آذر ۱۳۹۸

ممکن است همچنین دوست داشته باشید

Text-Functions-Cover
توابع ویرایش متن انگلیسی در اکسل
۱۷ مرداد, ۱۳۹۹
Replace Function Cover-min
آموزش کار با تابع Replace
۱۲ فروردین, ۱۳۹۹
Mid Function Cover-min
تابع Mid و چند کاربرد در اکسل
۱ دی, ۱۳۹۸

    8 نظر

  1. profile avatar
    مهدی بغدادی
    ۱۴ فروردین ۱۳۹۹
    پاسخ

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

    • profile avatar
      حسنا خاکزاد
      ۱۴ فروردین ۱۳۹۹
      پاسخ

      درود
      ننوشتید ورودی تابع چی هست و چطور نوشته شده. به نظر میرسه $ ها رو باید اصلاح کنید
      این دو مقاله رو بخونید، احتمالا حل میشه مشکلتون

      https://excelpedia.net/address-function/
      http://excelpedia.net/cell-address/

  2. profile avatar
    حمید
    ۲۵ اسفند ۱۳۹۸
    پاسخ

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

    • profile avatar
      سامان چراغی
      ۲۵ اسفند ۱۳۹۸
      پاسخ

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

  3. profile avatar
    سیدسجادشیرمردی
    ۳۰ آذر ۱۳۹۸
    پاسخ

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

    • profile avatar
      حسنا خاکزاد
      ۳۰ آذر ۱۳۹۸
      پاسخ

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

      • profile avatar
        سیدسجادشیرمردی
        ۱ دی ۱۳۹۸
        پاسخ

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

        • profile avatar
          حسنا خاکزاد
          ۲ دی ۱۳۹۸
          پاسخ

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

          1
          =AND(ISNUMBER(A1),COUNTIF($A$1:A1,A1)<2)

نظر بدهید لغو پاسخ

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

-- بارگیری کد امنیتی --

زودتر از دیگران با خبر بشید

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

آخرین مطالب

حل مسئله رتبه بندی داده های تکراری
۱۱آذر۱۳۹۹
ابزار Mail Merge
ترکیب Mail Merge ورد و اکسل
۲۳آبان۱۳۹۹
سورت تاریخ
مرتب سازی انواع تاریخ در اکسل
۰۲شهریور۱۳۹۹
توابع متنی: تابع Proper، تابع Lower، تابع Upper در اکسل
توابع ویرایش متن انگلیسی در اکسل
۱۷مرداد۱۳۹۹

وجود یک منبع جامع و به روز که بدونی همیشه پشتیبانته برای یادگیری ضروریه. اکسل پدیا رو برای همین به وجود آوردیم.

پاسخ سوالات شما

اگر سوالات اکسلی دارید تو تالار گفتمان اکسل پدیا میتونید مطرح کنید. اساتید بسیاری هستند که به شما کمک میکنند.

کانال و تالار گفتمان اکسل پدیا

ورود به تالار گفتگو تلگرام

عضویت در خبرنامه
با عضویت در خبرنامه، ایمیل‌های آموزشی درباره اکسل و برنامه نویسی وی بی را دریافت خواهید کرد.

  • این فیلد برای اعتبار سنجی است و باید بدون تغییر باقی بماند .

021-47625755

info@excelpedia.net

آدرس: تهران، میدان دوم صادقیه، برج گلدیس، طبقه 7 واحد 721

دانلود اپ اکسل پدیادانلود از کافه بازار

تمامی حقوق برای اکسل پدیا محفوظ است.

  • اکسل از راه دور
  • شرایط و قوانین
  • درباره ما
  • تماس با ما