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
Color Scales:
این قسمت بین یک محدوده عدد، طیف رنگی ایجاد میکنه. کوچیک به بزرگ یا بزرگ به کوچیک. مثلا یک سری داده مربوط به هزینه داریم که میخوایم بیشترین مقادیر قرمز باشه و این طیف تا رنگ سبز (کمترین مقدار) تغییر کنه. (شکل ۴)
شکل ۴- Conditional Formatting در اکسل – نمایش داده ها در یک طیف رنگی
Icon Sets:
این قسمت با توجه به اعداد، آیکون هایی از شکل ۵ رو تخصیص میده. پیش فرض این ابزار درصد داده هاست. مثلا برای آیکون ۳ قسمتی چراغ خطر، ۳۳ درصد اول، چراغ خطر سبز، ۳۳ درصد دوم چراغ خطر زرد و ۳۳ درصد سوم هم چراغ خطر قرمز تخصیص داده میشه.
شکل ۵- Conditional Formatting در اکسل – استفاده از Icon set برای دسته بندی داده ها
همه این الگوهای آماده که تشریح شد، بر اساس پیش فرض هایی تعیین شده بر روی داده ها اعمال میشن. این تنظیمات تا حد زیادی قابل تغییر هست. کافیست بعد از انتخاب یکی از این الگو ها، از قسمت Manage Rules و پنجره باز شده، نوع فرمت شرطی مورد نظر رو انتخاب و Edit Rule رو بزنیم. که با توجه به ویژگی های این شروط و فرمت ها، پنجره تنظیمات مربوط به آنها ظاهر میشه. شکل ۶ پنجره تغییر تنظیمات، بازه ها و نوع محاسبه مربوط به Icon Set رو نشون میده.
شکل ۶- Conditional Formatting در اکسل – پنجره تغییر تنظیمات فرمت های شرطی
در این آموزش با کلیات و الگوهای آماده فرمت دهی شرطی آشنا شدیم. این ابزار نکات ریز زیادی داره. در مورد نحوه و منطق فرمول نویسی، ویرایش و حذف این فرمت ها در آموزش های بعدی حتما صحبت خواهیم کرد.
مباحث زیر رو برای یادگیری بیشتر فرمت دهی در اکسل حتما بخونید:
سلام وقت بخیر
چه فرمولی استفاده کنم که فرضا یک سلول خاص برای مثال a1 سبز شد سلول های دیگری که مدنظرمه برای مثال a4 تا a8 هم سبز بشه
درود بر شما
روی رنگ شدن سلول نمیتونید با فرمول اکشنی بگیرید
بهتره علت سبز شدن رو فرموله کنید
AND ( $E3=0 ) = جواب نمیده ، erorr میده
ایراد میگیره به تایپ
درود
بنظر میاد اسپیس اضافه داره
و الا ساختار فرمول غلط نداره ظاهرا
سلام و خسته نباشید
من یه مشکلی دارم میخام یه سلولم وقتی از ۲۰٪ یه سلول دیگه بیشتر شد تغییر رنگ بده.و اینکه اون سلول مرجعم تاریخ.مثلا از ۲۰٪ تاریخ تولید کیک رد شد رنگی بشه.تابع today رو زدم فقط قسمت تغییر رنگش موندم.ممنون
درود
این مقاتله رو مطالعه کنید. کمک میکنه
https://excelpedia.net/excel-alarm/
سلام
من یک ستون دارم که شامل چند متن تکراری هست که از فرمول 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 دارای دوره ۱ الی ۵ روزه میباشد ولی فایل دیگر فقط اسم آیتم را دارم ودر فایل دومی یک آیتم بجای ۵ بار ۱۰ بار تکرار شده به چه شکل باید آن را پیدا کنم؟
ممنون از سایت خوبتون
درود بر شما
سوال نامفهومه
باید عکس یا نمونه فایل گذاشته بشه
تشریف ببرید داخل گروه پرسش و پاسخ (لینک در فوتر سایت هست)، اونجا مطرح کنید