ساخت کاربرگهای حرفهای با قالببندی شرطی در اکسل
کاربرگهای حرفهای با قالببندی شرطی
کاربرگهای حرفهای با قالببندی شرطی در اکسل تعداد زیادی از پیش تنظیم شده است. با این حال، شما میتوانید با فرمولهای سفارشی خود قوانینی را ایجاد کنید.
قوانین قالببندی شرطی
۳ گام اصلی برای قالببندی شرطی
- تعیین محدوده: شما با انتخاب سلولهایی که میخواهید قانون موردنظر روی آنها اعمال شود، شروع میکنید. این محدوده ممکن است کل صفحه گسترده شما یا فقط مجموعهای از ردیفها یا ستونها باشد.
- تعیین قانون: در گام دوم باید قانون مورد نظر را انتخاب کنید. میتوانید بیش از دهها گزینه از جمله گزینههای بزرگتر از، کمتر از ، بین و غیره را انتخاب کنید.
- تعیین قالببندی: این گام پس از تعیین قانون است. اکسل برای هر قانون یک فرمت طراحی پیش فرض دارد، اما شما میتوانید آن را شخصیسازی کنید.
معرفی بخشهای Conditional Formatting
برای استفاده از Conditional Formatting به سربرگ Home بروید و در گروه Styles روی Conditional Formatting کلیک کنید.
این ابزار شامل ۳ بخش است:
بخش اول:
- Highlight cell rules
- Top/Bottom Rules
بخش دوم:
- Data Bars
- Color Scales
- Icon Sets
بخش سوم:
- New Rule
- Clear Rule
- Manage Rule
برجسته کردن دادههای مهم
گزینههای Highlight cells rules شامل موارد زیر است:
- Greater than: سلول (هایی) که محتوای آن از یک مقدار مشخص بیشتر است، متمایز میکند.
- Less than: سلول (هایی) که محتوای آن از یک مقدار مشخص کمتر است، متمایز میکند.
- Between: سلول (هایی) که محتوای آن بین دو مقدار مشخص است، متمایز میکند.
- Equal to: سلول (هایی) که محتوای آن دقیقا برابر یک مقدار مشخص است، متمایز میکند.
- Text that contains: سلول (هایی) که حاوی رشتهای خاص است، متمایز میکند.
- A date occurring: سلول (هایی) که حاوی تاریخی خاص است، متمایز میکند.
- Duplicate values: سلول (هایی) که محتوای تکراری دارند، متمایز میکند.
- More rules: اگر از قالببندی پیشفرض Excel رضایت ندارید، میتوانید خودتان قالببندی موردنظر را بسازید. روی More rules کلیک کنید سپس روی دکمه Format کلیک کنید و قالببندی موردنظر را اعمال کنید. همچنین میتوانید با استفاده از این قسمت قوانین جدید تعریف کنید.
در اینجا من ابتدا برای گزینه Less than و Text that contains و Duplicate values مثالی ذکر میکنم تا موضوع را بهتر درک کنید.
مثال گزینه: Less than
وقتی شما مجموعه بزرگی از دادهها را دارید و در آن به دنبال عددی خاص هستید، این کار بسیار خستهکننده است. مثلا فرض کنید شما تعدادی نمایندگی فروش دارید و میخواهید ببینید کدام نمایندگی در حوزه فروش سختافزار فروش کمتر از 650000 داشته است. این کار را به Conditional formatting بسپارید تا نتایج را در کثری از ثانیه با رنگ مشخص به شما نشان دهد.
ابتدا ستون موردنظر را انتخاب کنید، سپس روی Conditional formatting کلیک کنید. گزینه Highlight cell rules را انتخاب کنید و روی Less than کلیک کنید.
پنجره Less than باز میشود. سپس در کادر سمت چپ مقدار650000 را تایپ کنید ، در کادر سمت راست به صورت پیشفرض گزینه Light red fill with dark red text فعال است، من در اینجا Light Green fill with dark Green text را انتخاب کردم.
اگر روی OK کلیک کنید میبینید، رنگ عدد مربوط به سلولهایی که کمتر از 650000 است، سبز تیره و رنگ خود سلول به سبز روشن در میآید. می توانید با استفاده از گزینههای موجود در لیست کشویی حالتهای دیگر را هم امتحان کنید.
مثال گزینه : Text that contains
اگر به دنبال رشته خاصی در یک لیست هستید میتوانید با استفاده از قالببندی شرطی به راحتی آن رشته خاص را پیدا کنید. مثلا لیستی از مشخصات کارمندان را در اختیار دارید و میخواهید افرادی که تحصیلات دارند، متمایز شود. ابتدا ستون تحصیلات را انتخاب کنید. سپس روی conditional formatting کلیک کنید. سپس از قسمت Highlight cells rules گزینه Text that contains را انتخاب کنید.
پنجره Text That Contains باز میشود. در کادر سمت چپ فراز نتورک را وارد کنید و در کادر سمت راست رنگ موردنظر را انتخاب کنید و روی OK کلیک کنید.
مثال گزینه: Duplicate value
از قالببندی شرطی میتوان برای شناسایی مقادیر تکراری در یک مجموعه داده استفاده کرد. بین حذف تکراری و مشخص کردن مقادیر تکراری تفاوت وجود دارد. ابزار Remove duplicate در اکسل مقادیر تکراری را حذف میکند بدون اینکه به شما اطلاع دهد کدام یک را حذف کردهاید.
این ابزار در سربرگ Data قرار دارد.
این گزینه میتواند خیلی سریع کار را برای شما انجام دهد و باعث صرفهجویی در وقت شما شود، اما گاهی اوقات لازم است بیشتر مراقب دادههای خود باشید و بر ورودیهایی که حذف میشوند، نظارت داشته باشید. انجام این کار به صورت دستی کاری بسیار خستهکننده است پس این کار را به قالببندی شرطی بسپارید.
برای این کار مراحل زیر را انجام دهید:
دادههای خود را انتخاب کنید. سپس Conditional formatting را انتخاب کنید و گزینه Highlight cell rules را انتخاب کنید و سپس Duplicate values را انتخاب کنید و مقادیر تکراری که موردنظرتان هست را حذف کنید.
قوانین بیشترین/کمترین (Top/Bottom rules)
گزینههای Top/Bottom rulesشامل موارد زیر است:
- Top 10 items: محتوای سلولهای یک محدوده خاص را بررسی کرده و ۱۰ سلولی که بیشترین مقدار را دارند، متمایز میکند.
- Top 10 %: محتوای سلولهای یک محدوده خاص را بررسی کرده و سلولهایی که مقدار آنها برابر ۱۰% بالای یک مقدار مشخص است، متمایز میکند.
- Bottom 10 items: محتوای سلولهای یک محدوده خاص را بررسی کرده و ۱۰ سلولی که کمترین مقدار را دارند، متمایز میکند.
- Bottom 10 %: محتوای سلولهای یک محدوده خاص را بررسی کرده و سلولهایی که مقدار آنها برابر ۱۰% پایین یک مقدار مشخص است، متمایز میکند.
- Above average: محتوای سلولهای یک محدوده خاص را بررسی کرده و سلولی که محتوای آن بیشتر از میانگین است، متمایز میکند.
- Below average: محتوای سلولهای یک محدوده خاص را بررسی کرده و سلولی که محتوای آن کمتر از میانگین است، متمایز میکند.
- More rules: اگر از قالببندی پیشفرض Excel رضایت ندارید، میتوانید خودتان قالببندی موردنظر را بسازید. روی More rules کلیک کنید سپس روی دکمه Format کلیک کنید و قالببندی موردنظر را اعمال کنید. همچنین میتوانید با استفاده از این قسمت قوانین جدید تعریف کنید.
قوانین بیشترین/کمترین:
مثلا در لیست نمایندگیها میخواهید ۱۰ فروش بیشتر برای شما متمایز شود. محدوده موردنظر را انتخاب کنید، روی Conditional formatting کلیک کنید و سپس روی Top/Bottom rules کلیک کنید و بعد از آن روی Top 10 items کلیک کنید.
برای سفارشی کردن این گزینه میتوانید روی گزینه More rules کلیک کنید.
مثلا میخواهید تعداد فروش بیشتر نمایش داده شود، روی More rules کلیک کنید. پنجره New formatting rule باز میشود. در کادر سمت راست عدد را تایپ کنید
نکته: اگر تیک گزینه Of the selected range را بزنید برحسب درصد محاسبه میشود.
سپس با کلیک روی دکمه Format پنجره Format cells باز میشود. رنگ موردنظر را انتخاب کنید و روی OK کلیک کنید.
استفاده از میلههای داده برای نمایش سریع
اگر مجموعه داده شما خیلی بزرگ باشد و به دنبال مقادیر خاص یا متن در دادههای خود نیستید، میلههای داده مناسب هستند تا مقادیر بالا و پایین را به شما نشان دهند. ابتدا محدوده دادههای موردنظر را انتخاب کنید و سپس روی Conditional formatting کلیک کنید و روی Data bars کلیک کنید. این قسمت شامل دو بخش است:
- Gradient Fill: رنگ هر نوار رنگی از پررنگ شروع و رفته رفته کمرنگتر میشود.
- Solid Fill: کل نوار یک رنگ است.
در مثال زیر من برای مشاهده درصد پیشرفت پروژهها از Data bars و قسمت Solid Fill کمک گرفتهام.
مقیاس رنگ (Color scales)
تفاوت این گزینه با گزینه قبل فقط در نحوه ارائه آن است. استفاده از مقیاس رنگی راهی برای اختصاص رنگ به تمام سلولهای پر ارزش موردنظر شماست. یک رنگ برای سلولی با کمترین مقدار در نظر گرفته میشود. رنگ دیگری برای سلول با بالاترین مقدار در نظر گرفته میشود و به تمام سلولها و مقادیر موجود در بین، رنگهایی اختصاص داده میشود که ترکیبی از آن دو رنگ است.
مجموعه آیکون
اگر به دنبال جلوههایی خاص هستید از Icon sets کمک بگیرید.
این قسمت شامل Directional، Shapes، Indicators، Ratings است.
محدودهٔ موردنظر را انتخاب کنید. روی Conditional formatting کلیک کنید.
گزینه Icon sets را انتخاب کنید. اکسل مقادیری را به صورت پیشفرض برای تعیین رنگ دایرهها در نظر میگیرد شما میتوانید این مقادیر را بر حسب نیاز خود تغییر دهید. (در قسمت Manage rule به صورت مفصل توضیح داده شده است.)
استفاده از گزینه New Rule
با استفاده از گزینه New rule میتوانید قوانین جدید تعریف کنید
- روی conditional formatting کلیک کنید گزینه New rule را کلیک کنید و سپس روی Use a Formula to determine which cells to format کلیک کنید
- در کادر کلیک کنید علامت مساوی را قرار دهید و سپس محدوده موردنظر را انتخاب کنید و علامت $ را از پشت 5 و 50 پاک کنید (تا مقدار هر سلول با مقدار مقایسه شود) و سپس علامت بزرگتر قرار دهید و عدد را تایپ کنید.
- از قسمت Format رنگ موردنظر را انتخاب کنید.
حذف قالببندی شرطی در اکسل
برای این کار مراحل زیر را انجام دهید.
- روی Conditional formatting کلیک کنید و سپس روی Clear rules کلیک کنید.
- اگر میخواهید فقط قوانین مربوط به سلولهای انتخابی حدف شود، گزینه Clear rules from Selected cells را انتخاب کنید
- اگر میخواهید تمامی قوانین موجود در Sheet که در آن قرار دارید، حذف شود گزینه Clear rules from Entire sheet را انتخاب کنید.
برای حذف یک قانون خاص روی گزینه Manage rules کلیک کنید.
پنجره Conditional Formatting rules manager باز میشود.
- از قسمت Show Formatting rules کاربرگ موردنظر را انتخاب کنید
- سپس روی قانون موردنظر کلیک کنید
- سپس دکمه Delete rules را بزنید.
- در نهایت روی OK کلیک کنید.
ویرایش قالببندی شرطی با گزینه Manage rule
شاید نیاز باشد قالببندی شرطی را ویرایش کنید،
- برای این کار به قسمت Manage rules (مدیریت قوانین) بروید.
- با کلیک روی آن، پنجره Conditional Formatting rules manager باز میشود.
- ابتدا از قسمت Show Formatting rules for کاربرگ موردنظر را انتخاب کنید.
- قانونی که در لیست میخواهید ویرایش کنید انتخاب کنید.
- سپس روی Edit rules کلیک کنید.
پنجره Edit Formatting rule باز میشود. در قسمت Value مقادیر مورد نظر را وارد کنید و روی OK کلیک کنید.
پس اگر در قانونی اشتباه کردید نیاز نیست آن را حذف کنید بلکه آن را ویرایش کنید.
فهرست مطالب آموزشی اکسل
- ویژگی ردیابی تغییرات Track Changesدر اکسل
- ویژگی فیلترهای Pivot Tables در اکسل
- مدیریت ادغام Separate سلول ها اکسل
- قالب بندی شرطی Conditional درماکروسافت آفیس
- داده ها را با ابزار Goal seek در اکسل تجزیه و تحلیل کنید.
- مدیریت پیشرفته Filters اطلاعات در اکسل
- ایجاد یک تابع با استفاده از دستور AutoSum
- کاربرد ساختار فرمولهای Mathematical مایکروسافت Excel
- مزایای استفاده از OneDrive در Excel
- روش رفع مشکل اعداد بزرگ در Excel
- کاربردهای اکسل و تابع Average Excel
- تعیین دقیق تعداد کاراکتر در سلول Excel
- تغییر عرض سلول در اکسل روی سلول (Cell)
- تنظیمات ذخیره اتوماتیک و بازیابی فایل اکسل
- نمایش ستون پنهان شده یا hide شده در اکسل
- انتقال اطلاعات شیت به شیت دیگر در اکسل
- ثابت کردن سطر و ستون در Excel و گزینه Find
- قالب بندی اعداد با Persentage در اکسل
- انتخاب سطرهای زوج و فرد در Excel
- جمع دو کلمه در اکسل و مشکل چسبیدن کلمات بهم چیست؟
- ترکیب جدول ها در Excel
- ایجاد یک spreadsheet در Excel
- نحوه استفاده از تابع شرطی IF و کاربرد آن در اکسل
- ایجاد لیست کشویی در Excel
- ساخت کاربرگهای حرفهای با قالببندی شرطی در اکسل
- نوار ابزار دسترسی سریع در اکسل چیست ؟
جهت مشاهده دوره های آموزشی مختلف و مقالات کاربردی بر روی این لینک کلیک نمایید.
دیدگاهتان را بنویسید
برای نوشتن دیدگاه باید وارد بشوید.