سبد خرید
0

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

رنگی کردن سلول های پروتکت شده

انتخاب سلول های قفل شده
۵/۵ - (۱ امتیاز)

تفکیک سلول های قفل شده از قفل نشده

یکی از مهمترین موضوعات در اکسل امنیت اطلاعات هست که با قفل کردن شیت بخشی از این نیاز برطرف میشه. گاهی اوقات لازمه که سلول هایی که قفل هستن و یا قفل نیستن از هم تفکیک بشن. مثلا رنگ کنیم، تغییراتی بدیم و … . در این مقاله میخوایم این موضوع رو بررسی کنیم و ببینیم که چطور میشه سلول های قفل شده یا قفل نشده رو از هم تفکیک کرد. البته روش تفکیک اهمیت داره. اینکه بخوایم مثلا سلول های قفل شده رو انتخاب کنیم، رنگی کنیم و … همه اینها امکان پذیر هست.

انتخاب سلول های پروتکت نشده

همونطور که میدونیم، مشخصه سلول های پروتکت نشده اینه که در قسمت Format cell و در تب Protection، تیک قسمت Lock رو ندارن. پس از همین ویژگی استفاده میکنیم که بتونیم سلول هایی که این شرایط رو دارن انتخاب کنیم.

برای این کار مراحل زیر رو انجام میدیم:

  • ابزار Find & Replace رو باز میکنیم. برای این کار یا Ctrl+F میزنیم یا از تب Home و قسمت Editing روی Find کلیک میکنیم. روی Options کلیک کرده و از زبانه Format گزینه Format رو انتخاب میکنیم. (شکل ۱)

انتخاب سلول های قفل نشده-ابزار Find

شکل ۱- انتخاب سلول هایی که Lock نشدن با استفاده از ابزار Find

  • از پنجره باز شده و از تب Protection تیک گزینه Locked رو بر میداریم (چون میدونیم هر سلولی که این تیک رو نداره، قفل نیست) و Ok رو میزنیم.

تنظیمات فرمت سل

شکل ۲- انتخاب سلول های Lock نشده- تنظیمات فرمت سل

  • حالا کافیه روی گزینه Find All کلیک کنیم. آدرس همه سلول هایی که تیک Locked رو ندارن، نمایش داده میشه.

نمایش آدرس سلول ها

شکل ۳- انتخاب سلول های Lock نشده – نمایش آدرس سلول های قفل نشده

  • برای اینکه سلول های مورد نظر بصورت یک جا انتخاب بشن، کافیه که کلید ترکیبی Ctrl+A رو بزنیم. با این کار همه سلول هایی که در ابزار Find نمایش داده شده اند، انتخاب هم می شن.

انتخاب سلول های قفل نشده – انتخاب همه سلول های قفل نشده

شکل ۴- انتخاب سلول های Lock نشده – انتخاب همه سلول های قفل نشده

  • با همین روش میتونیم سلول های قفل شده (یعنی سلول هایی که تیک Locked دارن) رو هم انتخاب کنیم.

هایلایت کردن سلول های پروتکت شده بصورت شرطی

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

خب همونطور که میدونیم، تابع cell اطلاعات متنوعی رو راجع به یک سلول به ما میده. یکی از این اطلاعات، قفل بودن و یا نبودن (تیک Lock داشتن/نداشتن) یک سلول هست. به فرمول زیر دقت کنید:

=CELL(“protect”, A1)

خروجی این تابع صفر یا یک هست. اگر سلول تیک Locked داشته باشه، یک و اگر نداشته باشه، صفر رو نمایش میده. شکل ۵.

تابع Cell-Protect

شکل ۵- هایلایت سلول های Lock نشده- تابع Cell-Protect

میخوایم از این خروجی در ابزار conditional formatting استفاده کنیم ولی همونطور که میدونیم، به ازای خروجی True/1 فرمت اعمال میشه و به ازای خروجی False/0 فرمت اعمال نمیشه. اما میخوایم به ازای خروجی صفر، (یعنی تیک Lock نداشتن) سلول رنگی بشه. پس باید خروجی صفر و یک رو برعکس کنیم. روش های مختلفی برای این کار وجود داره، مثلا میتونیم از IF استفاده کنیم. اما در اینجا از تابع NOT استفاده میکنیم که با این تابع هم آشنا بشیم.

تابع NOT خروجی منطقی (True/False) رو برعکس میکنه. شکل ۶.

=NOT(CELL(“protect”,A1))

تابع Not

شکل ۶- هایلایت سلول های قفل نشده-تابع Not

حالا این فرمول رو در ابزار فرمت دهی شرطی یا Conditional formatting وارد میکنیم.

برای این کار محدوده مورد نظر رو انتخاب میکنیم و از مسیر زیر، گزینه new rule رو میزنیم و فرمول بالا رو وارد کرده و فرمت دلخواه رو انتخاب میکنیم: (شکل ۷)

Home/Conditional formatting/ New rule/ use a formula to determine which cells to format

هایلایت سلول ها-فرمت دهی شرطی Conditional formatting

شکل ۷- هایلایت سلول های قفل نشده-فرمت دهی شرطی Conditional formatting

حالا با زدن OK همه سلول هایی که تیک Lock رو ندارن، رنگی میشن. (شکل ۸)

هایلایت سلول ها با استفاده از فرمت دهی شرطی یا Conditional Formatting

شکل ۸- هایلایت سلول های قفل نشده با استفاده از فرمت دهی شرطی یا Conditional Formatting

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

نکته:
دقت داشته باشیم که این تیک Lock شرط لازم برای قفل شدن یک سلول هست، اما کافی نیست. حتما باید عملیات Protection انجام بشه تا سلول هایی که تیک دارند، قفل بشن. برای اطلاعات بیشتر مقاله مربوط به قفل کردن شیت ها رو مطالعه کنید.

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

دیدگاه کاربران
  • ناظم یونسی 24 فروردین 1399 / 12:40 ب.ظ

    با تشکر از مطالب مفیدتون.
    یه مشکلی در رابطه با حفاظت از سلول ها داشتم که اگه ممکنه راهنمایی کنید.
    یه فایل رو بین چند نفر به اشتراک گذاشتم و به هر کدوم اجازه دادم که چند سطر مربوط به خودشون رو ویرایش کنند. البته اجازه حذف واضافه کردن سطرها رو هم بهشون دادم. بعضی از سلولها رو قفل کردم تا افراد نتونند ویرایش کنند. منتها موقع حذف سطر اکسل پیام میده که چون در این سطر سلول قفل شده وجود داره امکان حذف سطر رو نمیده. چطور می تونم هم امکان حذف سطر رو داشته باشم وهم در اون سطر سلول قفل شده داشته باشم؟ ممنون

    • حسنا خاکزاد 24 فروردین 1399 / 6:01 ب.ظ

      درود
      در مقاله زیر در شکل ۲ توضیح داده شده که د رحین قفل شدن چط.ر میتونید امکانات دیگر رو مجاز کنید
      https://excelpedia.net/worksheet-security/

  • مسلم پورحسین 6 اسفند 1398 / 1:43 ب.ظ

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

  • محمدعلی 3 آبان 1398 / 9:49 ب.ظ

    سلام، خداقوت؛
    بنده یه فایل اکسل درست کردم که پر از متن‌های رنگی هستش،
    اما موقعی که میخوام از فایل اکسل خروجی بگیرم، بیشتر اون نوشته‌های رنگی خود به خود سیاه میشن داخل فایل PDF !

    این بحث قفل بودن یا نبودن رو هم چک کردم، بازم جواب نداد!!!

    چه کنم؟

    • سامان چراغی 24 اردیبهشت 1399 / 9:16 ب.ظ

      سلام
      این موارد معمولا به دلیل مشکلات درایور پرینتر هست ( که در اینجا درایور نرم افزار PDF شما میشه) و با بروزرسانی یا تغییر درایور حل میشه.

ارسال دیدگاه

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

توسط
تومان