
تفکیک سلول های قفل شده از قفل نشده
یکی از مهمترین موضوعات در اکسل امنیت اطلاعات هست که با قفل کردن شیت بخشی از این نیاز برطرف میشه. گاهی اوقات لازمه که سلول هایی که قفل هستن و یا قفل نیستن از هم تفکیک بشن. مثلا رنگ کنیم، تغییراتی بدیم و … . در این مقاله میخوایم این موضوع رو بررسی کنیم و ببینیم که چطور میشه سلول های قفل شده یا قفل نشده رو از هم تفکیک کرد. البته روش تفکیک اهمیت داره. اینکه بخوایم مثلا سلول های قفل شده رو انتخاب کنیم، رنگی کنیم و … همه اینها امکان پذیر هست.
انتخاب سلول های پروتکت نشده
همونطور که میدونیم، مشخصه سلول های پروتکت نشده اینه که در قسمت Format cell و در تب Protection، تیک قسمت Lock رو ندارن. پس از همین ویژگی استفاده میکنیم که بتونیم سلول هایی که این شرایط رو دارن انتخاب کنیم.
برای این کار مراحل زیر رو انجام میدیم:
- ابزار Find & Replace رو باز میکنیم. برای این کار یا Ctrl+F میزنیم یا از تب Home و قسمت Editing روی Find کلیک میکنیم. روی Options کلیک کرده و از زبانه Format گزینه Format رو انتخاب میکنیم. (شکل ۱)
شکل ۱- انتخاب سلول هایی که Lock نشدن با استفاده از ابزار Find
- از پنجره باز شده و از تب Protection تیک گزینه Locked رو بر میداریم (چون میدونیم هر سلولی که این تیک رو نداره، قفل نیست) و Ok رو میزنیم.
شکل ۲- انتخاب سلول های Lock نشده- تنظیمات فرمت سل
- حالا کافیه روی گزینه Find All کلیک کنیم. آدرس همه سلول هایی که تیک Locked رو ندارن، نمایش داده میشه.
شکل ۳- انتخاب سلول های Lock نشده – نمایش آدرس سلول های قفل نشده
- برای اینکه سلول های مورد نظر بصورت یک جا انتخاب بشن، کافیه که کلید ترکیبی Ctrl+A رو بزنیم. با این کار همه سلول هایی که در ابزار Find نمایش داده شده اند، انتخاب هم می شن.
شکل ۴- انتخاب سلول های Lock نشده – انتخاب همه سلول های قفل نشده
- با همین روش میتونیم سلول های قفل شده (یعنی سلول هایی که تیک Locked دارن) رو هم انتخاب کنیم.
هایلایت کردن سلول های پروتکت شده بصورت شرطی
حالا فرض کنید میخواهیم که سلول های قفل نشده رو بصورت شرطی، هایلایت کنیم که به محض اینکه تنظمات یک سلول تغییر کرد مثلا از قفل شده به قفل نشده و یا برعکس تغییر کرد، نتیجه آپدیت بشه.
خب همونطور که میدونیم، تابع cell اطلاعات متنوعی رو راجع به یک سلول به ما میده. یکی از این اطلاعات، قفل بودن و یا نبودن (تیک Lock داشتن/نداشتن) یک سلول هست. به فرمول زیر دقت کنید:
=CELL(“protect”, A1)
خروجی این تابع صفر یا یک هست. اگر سلول تیک Locked داشته باشه، یک و اگر نداشته باشه، صفر رو نمایش میده. شکل ۵.
شکل ۵- هایلایت سلول های Lock نشده- تابع Cell-Protect
میخوایم از این خروجی در ابزار conditional formatting استفاده کنیم ولی همونطور که میدونیم، به ازای خروجی True/1 فرمت اعمال میشه و به ازای خروجی False/0 فرمت اعمال نمیشه. اما میخوایم به ازای خروجی صفر، (یعنی تیک Lock نداشتن) سلول رنگی بشه. پس باید خروجی صفر و یک رو برعکس کنیم. روش های مختلفی برای این کار وجود داره، مثلا میتونیم از IF استفاده کنیم. اما در اینجا از تابع NOT استفاده میکنیم که با این تابع هم آشنا بشیم.
تابع NOT خروجی منطقی (True/False) رو برعکس میکنه. شکل ۶.
=NOT(CELL(“protect”,A1))
شکل ۶- هایلایت سلول های قفل نشده-تابع Not
حالا این فرمول رو در ابزار فرمت دهی شرطی یا Conditional formatting وارد میکنیم.
برای این کار محدوده مورد نظر رو انتخاب میکنیم و از مسیر زیر، گزینه new rule رو میزنیم و فرمول بالا رو وارد کرده و فرمت دلخواه رو انتخاب میکنیم: (شکل ۷)
Home/Conditional formatting/ New rule/ use a formula to determine which cells to format
شکل ۷- هایلایت سلول های قفل نشده-فرمت دهی شرطی Conditional formatting
حالا با زدن OK همه سلول هایی که تیک Lock رو ندارن، رنگی میشن. (شکل ۸)
شکل ۸- هایلایت سلول های قفل نشده با استفاده از فرمت دهی شرطی یا Conditional Formatting
کاربرد این موضوع میتونه برای فایل های آماده گزارش یا نرم افزارهایی باشه که در قسمت هایی نیاز به ورود داده توسط کاربر داره. از طرفی بقیه قسمت های فایل فرمول نویسی و قفل شده. برای اینکه کاربر بدونه دقیقا کجاها و در چه محدوده هایی میتونه داده رو تغییر بده و یا داده جدید وارد کنه، میتونیم با این کار همه سلول هایی که قفل نشدن رو رنگی کنیم که برای کاربران هم مشخص باشه و مدام با خطای پروتکت بودن شیت مواجه نشن.
دقت داشته باشیم که این تیک Lock شرط لازم برای قفل شدن یک سلول هست، اما کافی نیست. حتما باید عملیات Protection انجام بشه تا سلول هایی که تیک دارند، قفل بشن. برای اطلاعات بیشتر مقاله مربوط به قفل کردن شیت ها رو مطالعه کنید.
با تشکر از مطالب مفیدتون.
یه مشکلی در رابطه با حفاظت از سلول ها داشتم که اگه ممکنه راهنمایی کنید.
یه فایل رو بین چند نفر به اشتراک گذاشتم و به هر کدوم اجازه دادم که چند سطر مربوط به خودشون رو ویرایش کنند. البته اجازه حذف واضافه کردن سطرها رو هم بهشون دادم. بعضی از سلولها رو قفل کردم تا افراد نتونند ویرایش کنند. منتها موقع حذف سطر اکسل پیام میده که چون در این سطر سلول قفل شده وجود داره امکان حذف سطر رو نمیده. چطور می تونم هم امکان حذف سطر رو داشته باشم وهم در اون سطر سلول قفل شده داشته باشم؟ ممنون
درود
در مقاله زیر در شکل ۲ توضیح داده شده که د رحین قفل شدن چط.ر میتونید امکانات دیگر رو مجاز کنید
https://excelpedia.net/worksheet-security/
با سلام
مطالبتون و شیوه آموزشی شما منحصر به فرده.
ممنون
سلام، خداقوت؛
بنده یه فایل اکسل درست کردم که پر از متنهای رنگی هستش،
اما موقعی که میخوام از فایل اکسل خروجی بگیرم، بیشتر اون نوشتههای رنگی خود به خود سیاه میشن داخل فایل PDF !
این بحث قفل بودن یا نبودن رو هم چک کردم، بازم جواب نداد!!!
چه کنم؟
سلام
این موارد معمولا به دلیل مشکلات درایور پرینتر هست ( که در اینجا درایور نرم افزار PDF شما میشه) و با بروزرسانی یا تغییر درایور حل میشه.