سبد خرید
0

سبد خرید شما خالی است.

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

اطلاعات تکراری و Condtional Formatting

conditional formatting در اکسل
۴.۸/۵ - (۱۴ امتیاز)

گیفConditional Formatting در اکسل

فرمت دهی شرطی یا Conditional Formatting در اکسل ابزاری فوق العاده کاربردی در زمینه بصری سازی داده هاست. بصری سازی یا Visualization، انجام تنظیماتی روی داده هاست برای اینکه تفسیر و درک داده ها راحت تر بشه. این ابزار از طریق تغییر فرمت (همه فرمت های در دسترس از قسمت Format Cell) به نمایش بهتر داده ها کمک میکنه. در واقع داده هایی که با شرایط مورد نظر ما همخوانی داشته باشه رو رنگی میکنه.

فرمت دهی شرطی در اکسلفرمت دهی شرطی از تب Home در دسترس هست. در این آموزش به معرفی قسمت های مختلف این ابزار قدرتمند می پردازیم. این ابزار یک سری الگوهای آماده (شکل ۱) ارائه میکنه که کاربرد خیلی زیادی هم دارند. جایی که این الگوهای آماده نیاز ما رو پاسخ نده، این ابزار این امکان رو به ما داده که بتونیم فرمول نویسی انجام بدیم و خواسته خودمون و دقیقا پیاده کنیم. الگوهای آماده در ابزار Conditional Formatting به شرح زیر است:

Highlight cells Rules در Conditional Formatting:

این قسمت روی مقدار داده ها اعمال میشه. اینکه هر سلول چه مقداری داره.

شرط مورد نظر مفهوم
Greater Than… اعداد بزرگتر از مقدار مورد نظر
Less Than… اعداد کوچکتر از مقدار مورد نظر
Between… اعداد بین بازه مورد نظر
Equal To… اعداد برابر با مقدار مورد نظر
Text That Contains… متن های شامل عبارت مورد نظر
A Date Occurring… تاریخ مورد نظر
Duplicate Values/Unique Value داده های تکراری/داده های منحصربفرد

با انتخاب محدوده داده مورد نظر و انتخاب یکی از شروط مورد نظر، فرمت داده هایی که با شرط تطابق داشته باشن، تغییر خواهد کرد. مثلا برای پیدا کردن داده های تکراری کافیه شرط Duplicate Values رو انتخاب کنیم.

فرمت پیش فرض این ابزار زمینه با قرمز کمرنگ و رنگ فونت قرمز پر رنگ هست. در صورتیکه بخوایم تغییر کنه، باید از Custom Format تعیین بشه. (شکل ۲)

فرمت دهی شرطی در اکسل - پنجره تعیید شرط و فرمت روی داده ها

شکل ۲- Conditional Formatting در اکسل – پنجره تعیید شرط و فرمت روی داده ها

Top/Bottom Rules:

این قسمت روی محاسباتی که روی محدوده داده محاسبه میشه اعمال میشه. مثلا میانگین یک مجموعه داده، ده درصد بالا و …

شرط مورد نظر مفهوم
Top 10 items… ۱۰ آیتم بزرگتر از بین یک محدوده
Top 10%… ۱۰ درصد بزرگتر از بین یک محدوده
Bottom 10 items… ۱۰ آیتم کوچکتر از بین یک محدوده
Bottom 10%… ۱۰ درصد کوچکتر از بین یک محدوده
Above average… اعداد بالای میانگین یک محدوده
Below average… اعداد زیر میانگین یک محدوده
نکته:
عدد ۱۰ پیش فرض این قسمت از ابزار Conditional Formatting هست. با انتخاب یکی از این گزینه ها، پنجری ای باز میشه که عدد ۱۰ رو میشه تعیین کرد.

 

Data Bars:

این قسمت میزان پیشرفت و یا تکمیل رو نشون میده (مشابه نمودار میله ای). یعنی بین یک محدوده عدد، بزرگترین عدد رو پر میکنه و بقیه اعداد رو نسبت به اون، محاسبه میکنه و سلول رو پر میکنه. (شکل ۳)

Conditional Formatting در اکسل - نمایش میزان پیشرفت Data Bars

شکل۳- Conditional Formatting در اکسل – نمایش میزان پیشرفت Data Bars

Color Scales:

این قسمت بین یک محدوده عدد، طیف رنگی ایجاد میکنه. کوچیک به بزرگ یا بزرگ به کوچیک. مثلا یک سری داده مربوط به هزینه داریم که میخوایم بیشترین مقادیر قرمز باشه و این طیف تا رنگ سبز (کمترین مقدار) تغییر کنه. (شکل ۴)

Conditional Formatting در اکسل - نمایش داده ها در یک طیف رنگی

شکل ۴- Conditional Formatting در اکسل – نمایش داده ها در یک طیف رنگی

Icon Sets:

 Conditional Formatting در اکسلاین قسمت با توجه به اعداد، آیکون هایی از شکل ۵ رو تخصیص میده. پیش فرض این ابزار درصد داده هاست. مثلا برای آیکون ۳ قسمتی چراغ خطر، ۳۳ درصد اول، چراغ خطر سبز، ۳۳ درصد دوم چراغ خطر زرد و ۳۳ درصد سوم هم چراغ خطر قرمز تخصیص داده میشه.

Conditional Formatting در اکسل - استفاده از Icon set برای دسته بندی داده ها

شکل ۵- Conditional Formatting در اکسل – استفاده از Icon set برای دسته بندی داده ها

نکته:
همه این الگوهای آماده که تشریح شد، بر اساس پیش فرض هایی تعیین شده بر روی داده ها اعمال میشن. این تنظیمات تا حد زیادی قابل تغییر هست. کافیست بعد از انتخاب یکی از این الگو ها، از قسمت Manage Rules و پنجره باز شده، نوع فرمت شرطی مورد نظر رو انتخاب و Edit Rule رو بزنیم. که با توجه به ویژگی های این شروط و فرمت ها، پنجره تنظیمات مربوط به آنها ظاهر میشه. شکل ۶ پنجره تغییر تنظیمات، بازه ها و نوع محاسبه مربوط به Icon Set رو نشون میده.

 

Conditional Formatting در اکسل - پنجره تغییر تنظیمات فرمت های شرطی

شکل ۶- Conditional Formatting در اکسل – پنجره تغییر تنظیمات فرمت های شرطی

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

مباحث زیر رو برای یادگیری بیشتر فرمت دهی در اکسل حتما بخونید:

تفکیک اعداد با استفاده از فرمت دهی شرطی

 روش برای نمایش خط روی عبارات و کلمات در اکسل

126

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

دیدگاه کاربران
  • بهادرجلیلیان ۲۳ اردیبهشت ۱۳۹۹ / ۳:۱۳ ب٫ظ

    سلام و خسته نباشید
    من یه مشکلی دارم میخام یه سلولم وقتی از ۲۰٪ یه سلول دیگه بیشتر شد تغییر رنگ بده.و اینکه اون سلول مرجعم تاریخ.مثلا از ۲۰٪ تاریخ تولید کیک رد شد رنگی بشه.تابع today رو زدم فقط قسمت تغییر رنگش موندم.ممنون

  • قنبری ۱۸ اردیبهشت ۱۳۹۹ / ۱۰:۰۱ ق٫ظ

    سلام
    من یک ستون دارم که شامل چند متن تکراری هست که از فرمول if بدست می اید میتونم با کاندیشن هر کدوم از پاسخ ها رو به یک رنگ در بیارم یعنی اگه جواب if “سفارش داده شود” بود خودکار قرمز شود و دوجواب دیگر زرد و سبز شود.
    البته من این کار رو برای هر متن در conditional انجام دادم یعنی هر متن رو انتخاب کردم و رنگ دلخواه رو تعریف کردم ولی دربرخی از سلولها کلا شرط رنگی بودن قاطی میکنه و جواب نمیده نه اون بلکه کل ستون دچار پاشیدگی میشه ؟

    • آواتار
      حسنا خاکزاد ۱۸ اردیبهشت ۱۳۹۹ / ۷:۵۶ ب٫ظ

      درود
      rule ها رو نباید طوری بنویسید که تداخل داشته باشند
      روی یک سلول کلیک کنید و از قسمت formula مثلا بنویسید =a1=سفارش داده شود” بعد رنگ دلخواه رو انتخاب کنید
      دوباره همونجا Add rule بزنید و شرط بعدی و همینطور الی اخر. بعد فرمت رو انتقال بدید روی بقیه سلول ها

      • سحر قنبری ۲۰ اردیبهشت ۱۳۹۹ / ۳:۳۵ ب٫ظ

        بسیار ممنونم

  • حمید ۱۶ اردیبهشت ۱۳۹۹ / ۰:۳۱ ق٫ظ

    در ستون شماره دانشجویی نیازی به وارد کردن چهار رقم اول شماره دانشجویی (۸۷۴۷) نباشد، به طور مثال با وارد کردن عدد ،۱۰۶۲شماره دانشجویی ۸۷۴۷۱۰۶۲وارد گردد.
    اینو چطوری با data validation بنویسم (از چه تابعی استفاده کنم)
    مرسی

    • آواتار
      حسنا خاکزاد ۱۶ اردیبهشت ۱۳۹۹ / ۱:۰۳ ب٫ظ

      دیتا ولیدیشن نمیتونه داده به داده ها اضاف کنه. فقط میتونه کنترل کنه

      برای اینکه اینکار و بکنید، میتونید در فرمت سل، مقار ثابت رو وارد کنید و این شکلی بنویسید:
      ۰″۸۷۴۷″
      اینطوری فقط کافیه قسمت دوم رو بنویسید

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

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

  • حمید ۱۴ اردیبهشت ۱۳۹۹ / ۱:۵۸ ب٫ظ

    سلام
    اگر در یک جدول بخوام مثلا در ستون “نام کارمند” اسامی کارمندانی که عضو گروه الف بوده اند و نیز وضعیت آنها Cبوده است را به رنگ آبی نشان دهد در Conditional formatting چیکار باید کنم ؟

    • آواتار
      حسنا خاکزاد ۱۴ اردیبهشت ۱۳۹۹ / ۵:۰۵ ب٫ظ

      درود
      در قسمت فرمول نویسی، از تابع and استفاده کنید و دو شرط گروه و و ضعیت رو بهش بدید

  • امیر ۳۱ فروردین ۱۳۹۹ / ۸:۱۲ ق٫ظ

    با سلام و خسته نباشید
    دو تا ستون دارم و میخوام بگم مثلا اگر a1>b1 سلول a1 سبز بشود.تا اینجای کار مشکلی نیست ولی من میخوام این شرط را برای سلول تا a10>b10 ببرم
    باید دونه دونه برای سلولهای a2 تا a10 شرط بگذارم یا میشه یه کاری کرد این فرمول تو اون سلول ها هم کپی بشود؟

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

      درود
      $ فرمولی که می نویسید و درست کنید
      و در قسمت APPLY TO ALL محدوده A1:B10 رو انتخاب کنید

  • رضوانی ۲۰ شهریور ۱۳۹۸ / ۱۱:۴۲ ق٫ظ

    با سلام
    من در یک سلول( مثلا A1) با یک if دو عدد دارم (۱۱۰ و ۹۰ )که تحت شرایطی مقدار A1 مساوی ۹۰ است و در شرایط دیگر ۱۱۰، میخواهم در سلول دیگر مثلا B1 طوری تنظیم شود که اگر مقدار A1 مساوی ۱۱۰ شد، در سلول B1 اعداد کمتر از ۵۶ قرمز و بزرگتر از آن سبز شود و همچنین در حالت دیگر اگر مقدار A1 مساوی ۹۰ شد، در همان سلول B1 اعداد کمتر از ۴۰ قرمز و بزرگتر از آن سبز شود. لطفا راهنمایی ام کنید ، شرط conditional formatting به تنهایی این قابلیت دو گانه را ندارد . با تشکر

    • آواتار
      حسنا خاکزاد ۲۷ شهریور ۱۳۹۸ / ۱۲:۴۳ ب٫ظ

      درود بر شما
      دو تا ظرط رو جدا بنویسید
      برای یک سلول
      یک بار برای ۱۱۰ بودن
      یکبار برای ۹۰ بودن

  • رضا ۹ بهمن ۱۳۹۷ / ۱۰:۴۹ ق٫ظ

    سلام- دو ستون در اکسل دارم که هر کدام مجموعه ای از اعداد در آنها هست– اگر بخواهم تعداد سلول هایی که مقدار دارند و بین دو ستون مشترک هستند رو پیدا کنم چه تابعی رو باید استفاده کنم؟ منظورم از اشتراک ۲ ستون این هست که چه تعداد سلول مقدار دار در دو ستون مشترک هست؟ یعنی مقدار های عددی قید شده در ستون ها برام مهم نیست فقط تعداد سلول ها از لحاظ دارا بودن عدد (نه صفر) و اشتراک اونها رو میخوام
    تشکر

    • آواتار
      حسنا خاکزاد ۹ بهمن ۱۳۹۷ / ۱:۳۵ ب٫ظ

      درود بر شما
      این مقاله رو بخونید

      https://excelpedia.net/compare-lists/

      کلا با همین countif میتونید تعداد رو بشمارید و با if مقایسه کنید

  • حمیدرضا ۲ دی ۱۳۹۷ / ۱۰:۱۵ ق٫ظ

    باسلام وخسته نباشید
    دوتا فایل اکسل دارم که در هر دو یک سلول ۵ بار داده یکسان دارد ومقدار هر دو برابر ۲۵۰ سلول میباشد ولی در یکی دوره روز دارم مثلن آیتم a دارای دوره ۱ الی ۵ روزه میباشد ولی فایل دیگر فقط اسم آیتم را دارم ودر فایل دومی یک آیتم بجای ۵ بار ۱۰ بار تکرار شده به چه شکل باید آن را پیدا کنم؟
    ممنون از سایت خوبتون

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

      درود بر شما
      سوال نامفهومه
      باید عکس یا نمونه فایل گذاشته بشه
      تشریف ببرید داخل گروه پرسش و پاسخ (لینک در فوتر سایت هست)، اونجا مطرح کنید

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

توسط
تومان