单元格锁定与解锁

单元格锁定与解锁 企业管理的一项重要手段就是数据报表，同一归口管理的各个子单位的报表都用同一个和结构的软件，这就是常说的模板。在这个模板中为了避免数据输入的重复或手工计算的工作量，有不少项是自动计算出来的。EXCEL是最普通最常用的数据处理软件，有很多的企业报表模板就用它。模板中有很多的计算，如果不对这些公式进行保护，在输入数据的过程中可能一不小心就会把公式给删除了，造成的后果是可想而知的。必竟使用者对EXCEL的熟悉程度是有差异的，不少人都说会用，绝大多数只是停留在表面的一些简单使用上。如何来保护那些不需要数据录入者动的单元格呢,原来我用过，可昨天突然有同事问我时还一时想不起来了，试了半天才搞清。还是写下来，以后就不会再忘了。 在制作大量的Excel的时候，我们常常自己都可能搞忘记哪些数据是非常重要的，可能一个误操作就会导致一些自己辛苦的数据丢失了。所以，每当我们做完一些重要的数据的时候不仅要保存备份，而且最好是将一些“单元格锁定”起来，这样我们就不会出现误操作或误删除而导致删除重要数据。下面来给大家说一下如何灵活运用“Excel锁定单元格”以及锁定某列和某行的技巧～这里我所说的“锁定单元格”含义是指将某块区域的一些单元格保护起来，保护后是无法进行编辑与修改的，只有通过输入先前设置的密码正确后才可以重新开始编辑和修改～具体操作如下: 锁定单个或多个单元格与解锁方法 ?首先，利用Excel快捷键 Ctrl + A 全选所以的单元格，然后，右键选择“设置单元格格式”; ?在弹出的“单元格格式”中选择“保护”，取消“锁定”前面的钩去掉; welding procedures of steel pressure vessel NDT 9 JB/T4744-2000 steel pressure vessel products mechanical properties test of welded plate II, mechanical equipment installation engineering 1 GB150-98 2 GB50128-2005 vertical cylindrical steel pressure vessel steel welded specification for construction and acceptance of oil tank 3 JB/ T4735-1997 steel welded atmospheric pressure vessel 4 GB50231-2009 mechanical equipment installation engineering construction and acceptance specification for 5 GB50275-98 compressor, fan and pump installation engineering construction and acceptance specification for lifting 6 HG20201-2000 construction installation engineering construction standard 7, HG ... Pressure gauges, using an installed spark arrestor for acetylene cylinders, illegal carrying, using gas cylinders, each operator fined 20 Yuan. Gas bottle without the hot sun exposure measures, responsibilities of team a fine of 50 Yuan. (7) at height and the opening and provisional protective measures have been taken, the responsible unit fined 200 Yuan, construction person in charge a fine of 50 Yuan. (8) no permit to work and the job in accordance with the provisions, on the job unit fined 300 yuan, the unit will charge a fine of 100 Yuan. 7.5.3 accident penalties (1) injuries fatalities directly punished 1~2 million. Direct responsibility for the accident and have the corresponding responsibility of leadership, such as concerning administrative sanctions, should be brought to the company or the relevant Department. (2) personal injury accident occurred, the direct punishment 0.5-10,000 yuan, responsible for the direct responsibility for the accident and the responsibility of leadership, such as concerning administrative sanctions should be dealt with by the personnel Department of the company. (3) personal injury ?选中你所想要锁定的单个或多个单元格，再次右键选择“设置单元格格式”; ?同样，在弹出的“单元格格式”窗口中选择“保护”，再将“锁定”前面的钩打上，确定; ?继续选中需要锁定的单元格，然后单击菜单栏的“工具”-->保护-->保护工作表，在弹出的“保护工作表”窗口中我们输入锁定该单元格的密码(千万别把密码忘记了哟); ?默认选中头两个“选定锁定单元格”、“选定未锁定单元格”，取消它们后，用户将不能选中单元格。 下面那个“允许用户编辑区域”可以设置可以改动的区域，这样就实现用户输入区和锁定区分开的功能了。 welding procedures of steel pressure vessel NDT 9 JB/T4744-2000 steel pressure vessel products mechanical properties test of welded plate II, mechanical equipment installation engineering 1 GB150-98 2 GB50128-2005 vertical cylindrical steel pressure vessel steel welded specification for construction and acceptance of oil tank 3 JB/ T4735-1997 steel welded atmospheric pressure vessel 4 GB50231-2009 mechanical equipment installation engineering construction and acceptance specification for 5 GB50275-98 compressor, fan and pump installation engineering construction and acceptance specification for lifting 6 HG20201-2000 construction installation engineering construction standard 7, HG ... Pressure gauges, using an installed spark arrestor for acetylene cylinders, illegal carrying, using gas cylinders, each operator fined 20 Yuan. Gas bottle without the hot sun exposure measures, responsibilities of team a fine of 50 Yuan. (7) at height and the opening and provisional protective measures have been taken, the responsible unit fined 200 Yuan, construction person in charge a fine of 50 Yuan. (8) no permit to work and the job in accordance with the provisions, on the job unit fined 300 yuan, the unit will charge a fine of 100 Yuan. 7.5.3 accident penalties (1) injuries fatalities directly punished 1~2 million. Direct responsibility for the accident and have the corresponding responsibility of leadership, such as concerning administrative sanctions, should be brought to the company or the relevant Department. (2) personal injury accident occurred, the direct punishment 0.5-10,000 yuan, responsible for the direct responsibility for the accident and the responsibility of leadership, such as concerning administrative sanctions should be dealt with by the personnel Department of the company. (3) personal injury 到这里锁定单元格就已经设置完毕了～现在你可以回到Excel表格中看看，刚才被我们锁定的单元格貌似不可以编辑和修改了，每次编辑它都会提示:“正在视图更改被保护的只读单元格或图标”。如果想重新对锁定的单元格进行编辑或修改，单击菜单栏的“工具”中的“保护”下的“撤销工作表保护”，在窗口中重新输入设置的密码，即可撤销工作表的锁定。 ===================================================================================================================== 数据隐藏: 设置自定义格式为 ;;; 或 ;;; 均可。 这样就不会显示出来，但是选中单元格，会在编辑栏出显示。 结合 单元格保护权限，禁止用户选中单元格，实现完全隐。 问:我使用的Excel 2003，它只有在锁定工作表的情况下，才能锁定单元格。请问，能不能在没有锁定工作表的情况下，锁定指定的单元格, welding procedures of steel pressure vessel NDT 9 JB/T4744-2000 steel pressure vessel products mechanical properties test of welded plate II, mechanical equipment installation engineering 1 GB150-98 2 GB50128-2005 vertical cylindrical steel pressure vessel steel welded specification for construction and acceptance of oil tank 3 JB/ T4735-1997 steel welded atmospheric pressure vessel 4 GB50231-2009 mechanical equipment installation engineering construction and acceptance specification for 5 GB50275-98 compressor, fan and pump installation engineering construction and acceptance specification for lifting 6 HG20201-2000 construction installation engineering construction standard 7, HG ... Pressure gauges, using an installed spark arrestor for acetylene cylinders, illegal carrying, using gas cylinders, each operator fined 20 Yuan. Gas bottle without the hot sun exposure measures, responsibilities of team a fine of 50 Yuan. (7) at height and the opening and provisional protective measures have been taken, the responsible unit fined 200 Yuan, construction person in charge a fine of 50 Yuan. (8) no permit to work and the job in accordance with the provisions, on the job unit fined 300 yuan, the unit will charge a fine of 100 Yuan. 7.5.3 accident penalties (1) injuries fatalities directly punished 1~2 million. Direct responsibility for the accident and have the corresponding responsibility of leadership, such as concerning administrative sanctions, should be brought to the company or the relevant Department. (2) personal injury accident occurred, the direct punishment 0.5-10,000 yuan, responsible for the direct responsibility for the accident and the responsibility of leadership, such as concerning administrative sanctions should be dealt with by the personnel Department of the company. (3) personal injury 回答:可以的，先选中所有单元格，单击“格式?单元格?保护”，去掉“锁定”前面的“?”。然后就可以跟平常一样，用鼠标右键单击需要锁定的一个或部分单元格，单击鼠标右键菜单上的“单元格?保护”选项卡，选中“锁定”将单元格锁定。最后单击“工具?保护?保护工作表”，在上面输入密码即可锁定单元格。 默认状态下，excel单元格是锁定状态。但为什么锁定还可以编辑呢。因为少了一步保护工作表，保护工作表后，就无法再编辑单元格了。 也许你会问，我想让编辑其中一部分单元格怎么办,凉拌，只需要把需要编辑的通过右键--设置单元格格式--保护--去掉锁定前的勾即可。 Excel单元格锁定与数据隐藏 学校考试完毕一般要进行各种数据的处理，例如总分、平均分、名次，最高分，最低分，不及格分等，利用Excel的公式和函数功能很容易实现。因为多为雷同性工作，如果做成内含公式的格式表格推广下去，就可以大大减轻老师们的工作量。但问题是老师们对电子表格使用水平参差不齐，常常发生误操作，有意无意删改公式导致数据面目全非。 其实，利用Excel中保护工作表的相应功能，就可以避免此类错误。思路如下:将表格中重要的使用公式和函数计算的单元格锁定，不允许修改，留下其他单元格进行基本数据的输入，这样就可以保护重要单元格中数据和公式的安全性和稳定性。 我们通过下面一个简单的例子看看如何实现。 打开Excel 2003，按以下步骤进行操作: 1(首先定制表格的基本框架 黑色区域为数据输入区，红色区域为锁定区。如图1。 2(设定非锁定区域 welding procedures of steel pressure vessel NDT 9 JB/T4744-2000 steel pressure vessel products mechanical properties test of welded plate II, mechanical equipment installation engineering 1 GB150-98 2 GB50128-2005 vertical cylindrical steel pressure vessel steel welded specification for construction and acceptance of oil tank 3 JB/ T4735-1997 steel welded atmospheric pressure vessel 4 GB50231-2009 mechanical equipment installation engineering construction and acceptance specification for 5 GB50275-98 compressor, fan and pump installation engineering construction and acceptance specification for lifting 6 HG20201-2000 construction installation engineering construction standard 7, HG ... Pressure gauges, using an installed spark arrestor for acetylene cylinders, illegal carrying, using gas cylinders, each operator fined 20 Yuan. Gas bottle without the hot sun exposure measures, responsibilities of team a fine of 50 Yuan. (7) at height and the opening and provisional protective measures have been taken, the responsible unit fined 200 Yuan, construction person in charge a fine of 50 Yuan. (8) no permit to work and the job in accordance with the provisions, on the job unit fined 300 yuan, the unit will charge a fine of 100 Yuan. 7.5.3 accident penalties (1) injuries fatalities directly punished 1~2 million. Direct responsibility for the accident and have the corresponding responsibility of leadership, such as concerning administrative sanctions, should be brought to the company or the relevant Department. (2) personal injury accident occurred, the direct punishment 0.5-10,000 yuan, responsible for the direct responsibility for the accident and the responsibility of leadership, such as concerning administrative sanctions should be dealt with by the personnel Department of the company. (3) personal injury 此例是选中B3:F10，在鼠标右键菜单中选择“设置单元格格式...”，在“保护”选项卡中取消“锁定”项的选定(此项默认为选中状态)，如果下面的“隐藏”被选中了，同样把它取消。在这个对话框中我们可以看到相关说明:只有在工作表被保护时，锁定单元格或隐藏公式或数据才有效。将来在进行工作表保护后，因为此区域的锁定状态已被取消，可以自由输入数据、或者对它们进行修改。如图2。 3(设定保护区域 选中图1中的红色区域，在图2的对话框中勾选“锁定”和“隐藏”。选中“隐藏”的目的是让单元格中的数据不在公式栏出现，从而避免其过长而遮掩下面的表格内容。当然，如果公式较短可以不选此项，好处是可以看到某一单元格中完整的公式，从而了解计算结果的来历。 4(设定单元格格式 因为考号可能较长、班级前面可能带“0”，为防止Excel把它们进行科学计数处理或省略“0”，所以将它们的数字格式设为“文本”;将“数学”、“语文”、“总分”、“平均分”相关区域设为“数值”，保留一位小数。 以下我们来看如何自动计算显示相应的数据: 5(在A3单元格中输入公式 “=IF(OR(B3<>"",C3<>""),COUNTBLANK($A$2:A2)+COUNTA($A$2:A2),"")”，拖动A3右下角的填充柄向下填充至A10，意为以B3、C3单元格为参考，只要它们中有一个存在数据，则在A3格中显示相应的序号，否则不显示。 welding procedures of steel pressure vessel NDT 9 JB/T4744-2000 steel pressure vessel products mechanical properties test of welded plate II, mechanical equipment installation engineering 1 GB150-98 2 GB50128-2005 vertical cylindrical steel pressure vessel steel welded specification for construction and acceptance of oil tank 3 JB/ T4735-1997 steel welded atmospheric pressure vessel 4 GB50231-2009 mechanical equipment installation engineering construction and acceptance specification for 5 GB50275-98 compressor, fan and pump installation engineering construction and acceptance specification for lifting 6 HG20201-2000 construction installation engineering construction standard 7, HG ... Pressure gauges, using an installed spark arrestor for acetylene cylinders, illegal carrying, using gas cylinders, each operator fined 20 Yuan. Gas bottle without the hot sun exposure measures, responsibilities of team a fine of 50 Yuan. (7) at height and the opening and provisional protective measures have been taken, the responsible unit fined 200 Yuan, construction person in charge a fine of 50 Yuan. (8) no permit to work and the job in accordance with the provisions, on the job unit fined 300 yuan, the unit will charge a fine of 100 Yuan. 7.5.3 accident penalties (1) injuries fatalities directly punished 1~2 million. Direct responsibility for the accident and have the corresponding responsibility of leadership, such as concerning administrative sanctions, should be brought to the company or the relevant Department. (2) personal injury accident occurred, the direct punishment 0.5-10,000 yuan, responsible for the direct responsibility for the accident and the responsibility of leadership, such as concerning administrative sanctions should be dealt with by the personnel Department of the company. (3) personal injury 6(在H3单元格中输入公式“=IF(OR(B3<>"",C3<>""),(E3+F3)/2,"")”，拖动H3右下角的填充柄向下填充至H10，显示每名学生的平均分 7(在G3单元格中输入公式“=IF(OR(B3<>"",C3<>""),SUM(E3:F3),"")”，向下填充至G10，显示总分。 8(在I3单元格中输入公式 “=IF(OR(B3<>"",C3<>""),RANK(H3,$H$3:$H$10,0),"")”，向下填充至G10，动态显示排序结果。 9(.最关键的一步:“工具?保护?保护工作表”，在“保护工作表”对话框中保持“保护工作表及锁定的单元格内容”的选中状态，设定密码(可以设置为空)，在下面“允许此工作表的所有用户进行:”中作如图3选择。 在Excel 2000中缺少Excel 2003的“允许此工作表的所有用户进行:”选项，所以功能较差。而在此处选中“设置列格式”、“设置行格式”目的是为了在锁定状态下可以对表格的列框和行高进行调整，以便适应打印要求。 好了，把做好的电子表格作为模板分发下去，如果有人想对红色的锁定区域进行修改编辑的话，马上就会弹出一个对话框，提示“只有撤销了工作表保护才能修改”。哈，我的地盘，听我的，如果想发挥，到你的自留地(黑色区域)去吧～ 输入数据、显示效果见图4。 welding procedures of steel pressure vessel NDT 9 JB/T4744-2000 steel pressure vessel products mechanical properties test of welded plate II, mechanical equipment installation engineering 1 GB150-98 2 GB50128-2005 vertical cylindrical steel pressure vessel steel welded specification for construction and acceptance of oil tank 3 JB/ T4735-1997 steel welded atmospheric pressure vessel 4 GB50231-2009 mechanical equipment installation engineering construction and acceptance specification for 5 GB50275-98 compressor, fan and pump installation engineering construction and acceptance specification for lifting 6 HG20201-2000 construction installation engineering construction standard 7, HG ... Pressure gauges, using an installed spark arrestor for acetylene cylinders, illegal carrying, using gas cylinders, each operator fined 20 Yuan. Gas bottle without the hot sun exposure measures, responsibilities of team a fine of 50 Yuan. (7) at height and the opening and provisional protective measures have been taken, the responsible unit fined 200 Yuan, construction person in charge a fine of 50 Yuan. (8) no permit to work and the job in accordance with the provisions, on the job unit fined 300 yuan, the unit will charge a fine of 100 Yuan. 7.5.3 accident penalties (1) injuries fatalities directly punished 1~2 million. Direct responsibility for the accident and have the corresponding responsibility of leadership, such as concerning administrative sanctions, should be brought to the company or the relevant Department. (2) personal injury accident occurred, the direct punishment 0.5-10,000 yuan, responsible for the direct responsibility for the accident and the responsibility of leadership, such as concerning administrative sanctions should be dealt with by the personnel Department of the company. (3) personal injury 以上只是一个极简单的例子，实际应用中公式、函数的编制可能远比此例复杂的多，尤其是在某些中，一旦错误操作，牵一发而动全身，这时，这个单元格区域保护的功能就显得尤为实用了。 对EXCEL单元格进行读写保护 EXCEL最强大的功能是计算，EXCEL最基本的单位是单元格，为了EXCEL文件的安全问题，如果我们不希望某些单元格的内容被随意修改，我们就需要对EXCEL的单元格进行读写保护，对单元格中的数据进行保护，以提高EXCEL文件的安全性。 写保护 对EXCEL单元格的读写保护有两种方法: 方法一，锁定EXCEL单元格 在日常生活中提到保护某某东西，我们最先想到的就是上锁，例如保险箱上锁可以保护很贵重的东西。给EXCEL单元格加把锁就可以实现读写保护了， 首先选择需要锁定的一个或多个EXCEL单元格; 然后右键选择“设置单元格格式”，选择对话框中的“保护”选项卡，勾选锁定; 最后，在工具菜单中选择“保护”，设置一个只有我们自己才知道的密码，这个密码越复杂越好，位数一定要多，大小写字母和数字混合，这样的密码才不会轻易被盗。如此设置之后EXCEL单元格就被写保护，以后只有输入正确的密码才能对锁上的这些EXCEL单元格进行写修改。 方法二，过滤法 既然我们的目的是不让别人修改我们的EXCEL单元格，那么，只要对单元格的输入数据进行检测，把我们不希望写进来的东西过滤掉就OK了，选择需要锁定的一个或多个EXCEL单元格，然后在数据菜单中选择“有效性”选项，依次设定有效条件就可以对单元格welding procedures of steel pressure vessel NDT 9 JB/T4744-2000 steel pressure vessel products mechanical properties test of welded plate II, mechanical equipment installation engineering 1 GB150-98 2 GB50128-2005 vertical cylindrical steel pressure vessel steel welded specification for construction and acceptance of oil tank 3 JB/ T4735-1997 steel welded atmospheric pressure vessel 4 GB50231-2009 mechanical equipment installation engineering construction and acceptance specification for 5 GB50275-98 compressor, fan and pump installation engineering construction and acceptance specification for lifting 6 HG20201-2000 construction installation engineering construction standard 7, HG ... Pressure gauges, using an installed spark arrestor for acetylene cylinders, illegal carrying, using gas cylinders, each operator fined 20 Yuan. Gas bottle without the hot sun exposure measures, responsibilities of team a fine of 50 Yuan. (7) at height and the opening and provisional protective measures have been taken, the responsible unit fined 200 Yuan, construction person in charge a fine of 50 Yuan. (8) no permit to work and the job in accordance with the provisions, on the job unit fined 300 yuan, the unit will charge a fine of 100 Yuan. 7.5.3 accident penalties (1) injuries fatalities directly punished 1~2 million. Direct responsibility for the accident and have the corresponding responsibility of leadership, such as concerning administrative sanctions, should be brought to the company or the relevant Department. (2) personal injury accident occurred, the direct punishment 0.5-10,000 yuan, responsible for the direct responsibility for the accident and the responsibility of leadership, such as concerning administrative sanctions should be dealt with by the personnel Department of the company. (3) personal injury 进行写保护。如果有人再想改我们的EXCEL单元格，那么，不是这个EXCEL单元格容许的内容就不会被输入EXCEL单元格。 我们已经学会了对EXCEL单元格的写保护，那么，与之对应的就是读保护了。读保护也就是使别人看不到我们在EXCEL单元格里写了什么。 最简单的方法就是把字体设成与EXCEL单元格底色一样。如EXCEL单元格的背景颜色与字体颜色同时设为白色，这样一来，别人可能会以为该单元格没有变化，就会忽略掉。其他读保护的方法也很多，但是最有效的读保护其实就是把EXCEL文件加密后关掉，这样不知道密码的人无法打开该EXCEL文件，也就无从谈起读EXCEL单元格里的数据了。 对单元格的读保护其他的三种方法: 1、通过对单元格颜色的设置进行读保护。例如:将选定单元格或单元格集合的背景颜色与字体颜色同时设为白色，这样，从表面看起来单元格中好像是没有输入任何内容，用户无法直接读出单元格中所存储的信息。 2、用其他画面覆盖在需要保护的单元格之上，遮住单元格的本来面目，以达到读保护目的。例如:使用绘图工具，画一不透明矩形覆盖在单元格之上，从格式菜单中选定矩形的“锁定”选项，然后保护工作表，以保证矩形不能被随意移动。这样，用户所看到的只是矩形，而看不到单元格中所存储的内容。 3、通过设置单元格的行高和列宽，隐藏选定的单元格，然后保护工作表，使用户不能直接访问被隐藏的单元格，从而起到读保护的作用。 welding procedures of steel pressure vessel NDT 9 JB/T4744-2000 steel pressure vessel products mechanical properties test of welded plate II, mechanical equipment installation engineering 1 GB150-98 2 GB50128-2005 vertical cylindrical steel pressure vessel steel welded specification for construction and acceptance of oil tank 3 JB/ T4735-1997 steel welded atmospheric pressure vessel 4 GB50231-2009 mechanical equipment installation engineering construction and acceptance specification for 5 GB50275-98 compressor, fan and pump installation engineering construction and acceptance specification for lifting 6 HG20201-2000 construction installation engineering construction standard 7, HG ... Pressure gauges, using an installed spark arrestor for acetylene cylinders, illegal carrying, using gas cylinders, each operator fined 20 Yuan. Gas bottle without the hot sun exposure measures, responsibilities of team a fine of 50 Yuan. (7) at height and the opening and provisional protective measures have been taken, the responsible unit fined 200 Yuan, construction person in charge a fine of 50 Yuan. (8) no permit to work and the job in accordance with the provisions, on the job unit fined 300 yuan, the unit will charge a fine of 100 Yuan. 7.5.3 accident penalties (1) injuries fatalities directly punished 1~2 million. Direct responsibility for the accident and have the corresponding responsibility of leadership, such as concerning administrative sanctions, should be brought to the company or the relevant Department. (2) personal injury accident occurred, the direct punishment 0.5-10,000 yuan, responsible for the direct responsibility for the accident and the responsibility of leadership, such as concerning administrative sanctions should be dealt with by the personnel Department of the company. (3) personal injury