Conditional Formatting در اکسل

ابزار Conditional Formatting در تب Home و در گروه Style قرار گرفته است، همانطور که از نام این ابزار پیداست، Conditional Formatting در اکسل برای تغییر فرمت سلول هایی که دارای شرایط خاصی هستند مورد استفاده قرار می گیرد. قبلاً با فرمت سلول ها در اکسل آشنا شدیم، فرمت سلول ها در واقع شامل تمام مواردی است که مربوط به ظاهر سلول می باشد مانند رنگ، فونت و سایز نوشته و یا رنگ و حاشیه سلول، بنابراین ابزار Conditional Formatting در اکسل به کاربر این امکان را می دهد که سلول هایی که شرایط مورد نظر وی را دارند با فرمت دلخواه نمایش داده و مشخص نماید.

در این قسمت برخی گزینه ها برای متمایز کردن دسته ای از سلول ها در اختیار کاربر قرار گرفته است. برای استفاده از ابزار Conditional Formatting در اکسل ابتدا مجموعه سلول مورد نظر را انتخاب نمایید، سپس در قسمت Highlight Cells Rules با استفاده از گزینه ی اول (Greater Than…)می توانید مثلاً از اکسل بخواهید سلول هایی که در آنها عددی بزرگتر 13 می باشد را برای شما رنگی کند.

سایر گزینه ها:

Greater Than سلول هایی که عدد نوشته شده در آنها بزرگتر از یک مقدار هستند را رنگی می کند
• Less Than سلول هایی که عدد نوشته شده در آنها کوچکتر از یک مقدار هستند را رنگی می کند
• Between سلول هایی که عدد نوشته شده در آنها بین دو عدد خاص باشد را رنگی می کند
• Equal To سلول هایی که عدد نوشته شده در آنها برابر با یک مقدار باشد رنگی می کند
• Text That Contains سلول هایی که نوشته ی درون آنها دارای یک داده ی متنی خاص باشد رنگی می کند
• A Date Occurring سلول هایی که تاریخ نوشته شده در آنها تاریخ مشخصی باشد را رنگی می کند
• Duplicate Values سلول هایی که محتوای تکراری در مجموعه انتخاب شده داشته باشند را رنگی می کند

• 
  

  قسمت Top/Bottom Rules

  همانند بالا، در این قسمت نیز برخی گزینه ها برای رنگی کردن سلول ها در اختیار کاربران قرار گرفته است:

• Top 10 Items ده سلولی که اعداد نوشته شده در آنها بیشترین باشد را رنگی می کند
• Top 10% ده درصد تعداد سلول های انتخاب شده که اعداد نوشته شده در آنها بیشترین باشد را رنگی می کند
• Bottom 10 Items ده سلولی که اعداد نوشته شده در آنها کمترین باشد را رنگی می کند
• Bottom 10% ده درصد تعداد سلول های انتخاب شده که اعداد نوشته شده در آنها کمترین باشد را رنگی می کند
• Above Average سلول هایی که اعداد نوشته شده در آنها بیش از میانگین اعداد تمام سلول ها باشد را رنگی می کند
• Below Average سلول هایی که اعداد نوشته شده در آنها کمتر از میانگین اعداد تمامسلول ها باشد را رنگی می کند

این سه گزینه مشابه یکدیگر می باشند، اگر مجموعه ای از سلول ها که حاوی عدد هستند انتخاب شوند، هریک از این سه گزینه می تواند با ظاهری متفاوت بزرگی و کوچکی داده ها را نشان دهد، مثلاً Data Bars درون سلول ها یک نوار رنگی رسم می کند و سلولی محتوی بزرگترین عدد می باشد طولانی ترین نوار رنگی را در خود جای می دهد، به این ترتیب می توان با یک نگاه متوجه شد که کدام سلول دارای بیشترین مقدار است.

مهلت پرداخت حق‌بیمه هرماه کارکنان تا آخرین روز ماه بعد است و در صورتی که حتی یک روز پرداخت حق‌بیمه به تاخیر بیفتد، سیستم پرداخت حق‌بیمه را نمی‌پذیرد و مبلغ مذکور مشمول جریمه خواهد بود. جریمه تاخیر در پرداخت حق‌بیمه ماهانه 2 درصد و سالانه 24 درصد است و تا زمانی که حق‌بیمه و جرائم مربوط به آن تادیه نشود، جریمه‌ها کماکان ادامه خواهد یافت. جریمه ارسال نکردن لیست کارکنان به شعب سازمان 10 درصد است که فقط برای یکبار تعیین و وصول می‌شود. توصیه‌های لازم در مورد پرداخت حق‌بیمه و وظایف کارفرمایان:

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

لیست ها در اکسل کار ورود داده ها را ساده تر کرده و از اشتباهات احتمالی در هنگام ورود اطلاعات جلوگیری می کنند.

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

فرض کنید تا اسامی کارکنان را بخواهیم به یک لیست تبدیل کنیم. ابتدا لیست کارکنان را در یک ستون، مثلاً ستون A و بدون فاصله وارد می کنیم. از سربرگ Data گزینه Data validation را انتخاب می کنیم. از پنجره ظاهر شده و از قسمت Allow از کادر پایین افتادنی گزینه List را انتخاب می کنیم. از قسمت پایین همین پنجره و در قسمت Source آدرس محدوده ای را که اسامی در آن قرار گرفته را وارد می کنیم. (به جای آدرس محدوده، اگر این محدوده دارای اسم باشد می توان از اسم به جای آدرس محدوده استفاده نمود).

می توان در قسمت Input Massage پیام دلخواه را تایپ کنیم تا هنگام انتخاب سلول پیام مناسب نمایش داده شود.

در قسمت Error Alert نیز می توان پیامی درج کرد تا در صورت انتخاب نادرست، (مثلاً تایپ نامی که در لیست وجود ندارد)این پیام ظاهر شود

مراحل اجرای کار در تصویرهای زیر توضیح داده شده اند.

تذکر: اطلاعات برای تبدیل به لیست باید در یک محدوده پیوسته (بدون سلول خالی در بین داده ها)وارد شوند.

تذکر: اطلاعاتی که به لیست تبدیل می شوند، می توانند در کاربرگ جاری یا یک کاربرگ دیگر (مثلاً Sheet2، Sheet و … قرار داشته باشند)

تذکر: با قراردادن کرسر موس در یک سلول و انجام عملیات فوق علامت انتخاب لیست در همان سلول ظاهر خواهد شد و با خروج از سلول علامت انتخاب ناپدید می شود.

یکی ازترفند های کاربردی در اکسل تعریف جایگزین ها می باشد. فرض کنید شما در حال آماده کردن فایل اکسلی برای پروژه خود هستید و عنوان پروژه شما چند کلمه ای می باشد. فرض کنید این نام در بسیاری از سلول های اکسل شما درج می شود و هر بار برای درج این نام شما باید آن را یا از سلولی دیگر کپی نمایید و یا مجدداً تایپ کنید. در صورت زیاد بودن تعداد تکرار این نام شاید به این فکر کنیم که آیا راه دیگری برای درج این نام بدون تایپ تمام حروف آن وجود دارد یا خیر؟

برای پاسخ به این سوال، از منوی File در اکسل وارد قسمت Excel Options شوید. در بخش Proofing بر روی دکمه Auto Correct Option کلیک نمایید. در پنجره ی باز شده می توانیم در کادر Replace عبارت خلاصه ای را که میخواهیم اکسل آن را با عبارت دلخواه درج شده در کادر With جایگزین کند را درج کنیم. از این به بعد در هر جایی از فایل اکسل که عبارت خلاصه نوشته شود، اکسل آن را با عبارت چند کلمه ای جایگزین می نماید.

در یک سلول فایل اکسل کلیک کنید سپس:+Ctrl+Shift را بزنید.مشاهده می کنید ساعت برای شما نمایش داده می شود.

اگر بخواهیم داده ها و اطلاعاتی که به صورت سطری ثبت کردیم را به صورت ستونی یا بالعکس دربیاریم به این صورت عمل می کنیم:

پس از باز کردن نرم افزار اکسل، سلول های مربوط به سطر یا ستون رو به حالت انتخاب درآورده و با راست کلیک بر روی آن گزینه copy را انتخاب، سپس بر روی یک سلول که خالیست راست کلیک کرده و گزینه past spicial را انتخاب و پس از تیک زدن بر روی گزینهTranspose دکمه ok را میزنیم.

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

ابتدا به تب developer رفته و از گزینه زیر یک دکمه بر روی صفحه اکسل خود درگ کنید.

سپس با زدن دکمه new بر روی پنجره ای که باز شده است، وارد محیط vba اکسل شوید و کد زیر را در رویداد کلیک دکمه کپی کنید:

Dim soal As Integer

If soal = vbYes Then

Cells.ClearContents

Else

بعد از خارج شدن از محیط vba بر روی دکمه ایجاد شده کلیک کنید و با باز شدن پنجره سوال و انتخاب دکمه yes تمام اطلاعات داخل شیت پاک خواهد شد.

برای اینکه بخواهید محیط ویژوال بیسیک (vba)فایل اکسل خود را رمزگذاری کنید به این روش عمل کنید:

ابتدا پس از باز کردن فایل اکسل و زدن کلیدهای ترکیبی (alt+F11)، وارد منوی tools شده و گزینه VBAProject Properties. را انتخاب کنید