您所在的位置:首页 > 新闻中心 > 没那么容易,别人无法再更改你的Excel表格
公司要闻
没那么容易,别人无法再更改你的Excel表格
发布时间:2020-04-04 13:36
访问量:359

excel表格的功能很是强大,我们在使用时为了不让别人能够随意更改我们的表格,有些时候需要设置表格保护,今天就来说说Excel表格如何设置工作表格被保护

图片 1

本文介绍如何利用保护工作表和保护工作簿等功能,限制可编辑区域,设置数据验证,以保证复杂的逻辑关系不会被随意改动。

你有没有遇到这样的情况,想发给别人一个数据表,但是又不想让人看到里面的某些数据?需要

首先我们需要选中表格不需要被保护的部分

我们在工作当中经常需要下发一些表格模板,以便收集我们需要的数据,但是经常下发去的表格回来的时候经常被改的面目全非,小编今天就和大家一起学习,让他们乖乖的把表格按照我们下发的表格再发回来~


让很多人编辑,但是只想让这些人可以编辑指定的区域,甚至不同的人需要编辑各自不同的区

图片 2

选中任意一列,点击鼠标右键,单击设置单元格格式,弹出【设置单元格格式】对话框,切换到【保护】选项卡,取消勾选的【锁定】按钮,点击【确定】返回表格中。

系列教程索引和配套练习文件,请点这里。

域?给特定用户“特权”,让他(她)可以编辑所有数据?在这里,我将一一给大家介绍,我相

然后我们可以直接键入,或者再选中区域点击鼠标右键选择设置单元格格式,那么会弹出一个小窗口

图片 3


信有你知道,我也相信有你不知道的,一定要耐心看完哦!

图片 4

切换到【审阅】选项卡,在【更改】组中,单击【保护工作表】,弹出【保护工作表】对话框,输入密码后,点击【确定】按钮,系统会提醒再次输入密码,点击【确定】后返回表格中。此时【排序和筛选】功能变成灰色,其他人将不能再次进行排序啦,也就无法再更改顺序啦。同时,除了被取消锁定的那一列能够更改信息,其他的单元格都不再允许修改。

经过前面几番折腾,咱手上这份 Excel 文档的公式引用结构可谓相当复杂。发给同事使用的时候,难免人家因为不清楚背后的逻辑,而在无意中进行一些破坏性的修改,导致失效。

1 设置文件打开和修改密码

在设置单元格格式中我们选择右边的

图片 5

作为一个工具性的 Excel 表格,我们制作的目的是让电脑自动完成识别分类的工作,节省人力。这个过程可以简化为,用户粘贴进来三级分类,复制走一级二级分类;有需要时可以改动分类体系,其余的功能可以暂时丢掉。

这个是最基础的一个功能,相信大家都不陌生。

图片 6

但是,有一些人对于表格比较熟悉,可以直接全选复制粘贴,形成新的表格,这种情况我们也可以将它避免。在【保护工作表】对话框中,取消勾选【选定锁定单元格】。其他列将不再有选中的权力,自然就不能够再进行复制和粘贴了。我们可以看到还有其他的权限,根据需要可以自行选定。


方法:

我们接着需要点掉锁定前面的小对勾,然后选择确定

图片 7

打开 Example 6.xlsx,切换至 Tamplate 工作表。

点击Excel表的【文件】菜单的【另存为】,选择一个保存位置后,在弹出的对话框右下方有

图片 8

以上

首先为大量的分类工作预留位置,将 A3 和 B3 的公式,向下填充至 A5000 和 B5000 行,这里的数字根据日常工作经验确定。但是发现在三级分类为空的地方,一二级分类出现了 #N/A 的错误,所以在一二级公式中加入三级分类是否为空的判断,修改 A3 处公式如下

个【工具】下拉列表,选择其中的【常规选项】,在这里可以分别设置“打开权限密

此时在最上方的选项卡中我们选择右边区域的

希望大家在阅读之余,多加练习办公软件Office的使用,提高我们的工作效率,成为职场高效率的一员。我将在每周都进行内容更新,大家一起学习,共同进步。

=IF(C3="", "", VLOOKUP($C3, CHOOSE({1,2}, INDIRECT(Settings!$B$5):INDIRECT(Settings!$B$8), INDIRECT(Settings!$B$3):INDIRECT(Settings!$B$6)), 2, FALSE))

码”和“修改权限密码”。

图片 9

图片 10

同理,B3 处公式如下

2 保护工作表

在审阅选项卡中我们选择选项

=IF(C3="", "", VLOOKUP($C3, CHOOSE({1,2}, INDIRECT(Settings!$B$5):INDIRECT(Settings!$B$8), INDIRECT(Settings!$B$4):INDIRECT(Settings!$B$7)), 2, FALSE))

在【审阅】菜单中点击【保护工作表】,弹出保护工作表的对话框。这里可以设置保护工作

图片 11

然后再将 A3 和 B3 向下填充至 5000 行即可。

表的各种参数。

这时弹出保护工作表小窗口,我们需要设置一下在取消工作表保护时使用的密码,然后点击确定,可能会提示你再次输入同样密码,输入即可

使用保护工作表时,默认情况下除了选取单元格外,对整个工作表的任何编辑是被禁止的,而我们需要用户输入三级分类,所以需要将三级分类的位置设置为 “
允许用户编辑区域 ”,这样在启动保护工作表时,该部分才可以被修改。

方法:

图片 12

切换至 “ 审阅 ” 标签,点击 “ 允许用户编辑区域 ”。

Step 1、在“取消工作表保护时使用的密码”框中输入一个密码,点【确定】按钮会让你重

最后我们可以发现,如果不知道密码的他人只能在选中区域内操作更改,其他区域没有操作权限

图片 13

复输入密码以确认。

图片 14

审阅 - 允许用户编辑区域

Step 2、设置“允许此工作表的所有用户进行”的操作,比如设置格式、排序、使用自动筛

在弹出的退化框中点击 “ 新建 ” 按钮,添加一个可允许编辑的区域。

选、插入或删除、使用数据透视表和透视图等。此步可以省略。

图片 15

还有两点需要特别注意:

允许用户编辑区域对话框

要想在“保护工作表”时保护数据不被更改,一定要在设置单元格格式对话框中的【保

为该区域设置一个名称;并且选中三级分类所在的区域,这里根据常用工作量来判断,将 C3:C5000 设置为可编辑区域;下方的区域密码为空,这样才可以不受限制的编辑该区域。

护】选项卡中勾选“锁定”复选框,否则即使在保护了工作表的情况下也可以编辑该区域

图片 16

的内容。不过Excel在默认情况下都是已勾选了“锁定”的,所以不必担心会忘掉。

修改区域对话框

在“锁定”复选框下面还有个“隐藏”复选框是来干什么用的呢?如果勾选了“隐藏”,

单击 “ 确定 ” 按钮,可以看到刚刚设置的区域已经被添加到了允许用户编辑的列表中。再次单击 “ 确定 ” 按钮。

在工作表保护的情况下,用户就不可以查看这个单元格的公式或内容了。

接着,在顶部 Ribbon 工具栏中单击 “ 保护工作表 ” 按钮。

3 设置允许用户编辑的区域

图片 17

这个功能的作用是,当设置了工作表保护时,双击该区域并在弹出的对话框中输入一个密码

审阅 - 保护工作表

来编辑该区域的内容。所以这个功能需要跟“保护工作表”同时使用。

在弹出的对话框中,输入密码,例如 “ amazing ”,下方的权限采用默认设置即可,单击 “ 确定 ” 按钮。

方法:

图片 18

Step 1、在【审阅】菜单中点击【允许用户编辑区域】。

保护工作表对话框

Step 2、在弹出的对话框中点击【新建】按钮。

在新弹出的对话框中再次输入一遍刚刚输入的密码,单击 “ 确定 ” 按钮。

Step 3、在“引用单元格”中选定一个单元格区域,在“区域密码”中输入一个密码。这里

图片 19

如果不输入密码,则在受保护的工作表中,用户可以直接编辑该区域。现在很多人员比较多

确认密码对话框

的公司的电脑都使用域管理,那么你可以继续点击【权限】按钮,在弹出的对话框中继续选

至此即完成了对 Tamplate 工作表的保护,除 C3:C5000 区域外其他位置都不可以被编辑改动了。

择【添加】按钮,参考给出的示例,将域中的某些用户添加进来,设置之后,这些人就不需

针对 Settings 工作表,采用类似的处理,保留 tab 参数,也即 C9 位置的编辑权,其余用密码保护起来。如此,用户可以更改 tab 参数来切换分类体系,但是不会轻易将 Settings 中的其余参数搞混淆。

要密码就可以直接编辑受保护的工作表的指定区域,相当于给这些人加了“特权”。


Step 4、在【审阅】中点击【保护工作表】。这样就设置完成了。

现在的工作簿结构是可以被修改的,也即用户可以增加和删除工作表。如果分类体系相对固定,则可以使用保护工作簿功能,限制对工作表的增加和删除。

4 深度隐藏工作表

单击工具栏中的 “ 保护工作簿 ” 按钮。

大家都知道在工作表的标签上点右键,在弹出的菜单中点击【隐藏】就可以隐藏工作表,但

图片 20

是这种方式很容易被别人取消隐藏。那么有没有一种方法能不让别人取消隐藏呢?当然是有

审阅 - 保护工作簿

的。

在弹出的对话框中输入密码,单击 “ 确定 ” 按钮,然后再次输入密码以确认。

方法:

图片 21

Step 1、在工作表标签上点右键,在弹出的菜单中点击【查看代码】进入VBA编辑器。工程

保护结构和窗口对话框

资源管理器可以通过【视图】菜单来打开。

这里的密码可以与刚刚设置的 “ amazing ” 不同,但在本例中,仍然使用刚才的密码。

Step 2、在左侧的VBA工程资源管理器中点击需要深度隐藏的工作表,在下方的属性列表中

至此,各个工作表的结构被锁定,无法增加或删除。整个工作簿中唯一能改动的内容即刚刚所设置的两个允许编辑的区域,以及 Genre 和 Hehe 工作表。在日常使用中,只需将三级分类粘贴到 C3:C5000 的位置,即可从 A3:B5000 的位置上复制一级分类和二级分类,非常方便。

设置Visible属性=xlSheetVeryHidden。


设置到这里,如果别人不知道这种方法,那么你就算基本完成了。但是假如别人也看了这个

一些说明

文章,就可以很轻易地取消隐藏并查看数据。要想防止别人取消隐藏,我们还需要继续进行

  • 如果不对工作簿进行保护,而又不想用户随意修改 Settings 工作表,可以将其隐藏,右键工作表选项卡单击 “ 隐藏 ” 即可。

设置。

图片 22

Step 3、点击VBA编辑器的【工具】菜单,进入【VBA Project属性】,点击【保护】选项

隐藏工作表

卡,勾选“查看时锁定工程”,设置密码并保存。

  • 设置密码只是为了防止意外的修改将现有体系破坏,当工作表的功能需要继续升级或者进行修正时,需要取消保护工作表,以及取消保护工作簿,因而这套密码的目的并非出于保密。我个人推荐将密码作为参数写到 Settings 中,以防遗忘,也能方便他人在头脑清醒的情况下对表格做出修缮。

Step 4、在ThisWorkbook的代码区域输入以下代码(以下代码不执行具体功能,只是为了

图片 23

让Excel识别有代码存在),然后将文件另存为.xlsm格式(.xlsx格式是不能存储代码的)。

记录密码

Private Sub Workbook_Open()


' this is test

最后,做一点锦上添花的工作,为三级分类添加数据验证。该操作需要在工作表没有被保护的情况下进行。首先切换到 Tamplate 工作表,单击上方工具栏中的 “ 撤销保护工作表 ” 按钮。

End Sub

图片 24

这样别人就不能取消隐藏工作表,也不能进入VBA编辑器去手动更改属性来取消隐藏了。

审阅 - 撤销保护工作表

5 保护工作簿的结构

在弹出的对话框中输入密码,并确定。

这个也很简单,相信大家也有所了解了。

图片 25

方法:

撤销保护工作表对话框

在【审阅】中点击【保护工作簿】,默认是勾选“结构”,输入密码后保存即可。设置完之

然后选择 C3:C5000 区域,在 “ 数据 ” 选项卡中单击 “ 数据验证 ” 按钮。

后,别人将

图片 26

不能查看隐藏的工作表

数据 - 数据验证

不能移动、删除、隐藏或更改工作表的名称

在弹出的对话框中,将 “ 允许 ” 下拉列表设置为 “ 序列 ”,勾选 “ 提供下拉箭头 ” 复选框,并在 “ 公式 ” 处填写如下公式

不能插入新工作表或图表工作表

=OFFSET(INDIRECT(Settings!$B$10), 0, 0, Settings!$B$2)

不能将工作表移动或复制到另一工作簿中

即,所有符合规定的值必须是所使用的三级分类体系里面的值。

不能通过双击数据透视表的单元格查看源数据

图片 27

6 隐藏单元格内容

Paste_Image.png

通过设置单元格格式也能隐藏工作表内容。这里可不是简单地更改字体颜色来隐藏哦。

在 “ 出错警告 ” 选项卡中,按如下内容设置,并确定。

方法:

图片 28

Step 1、选中单元格区域,设置单元格格式,在【数字】选项卡的“分类”中选择“自定

数据验证对话框

义”,在右侧的输入框中输入三个分号(英文状态下输入)。

这样就给 C3:C5000 区域设置了数据验证,单击其中的一个可以发现在右侧有一个下拉箭头,里面提供了所有合法的三级分类值,点击其中一个即可选择。

Step 2、转到【保护】选项卡,勾选“隐藏”,然后点击【确定】按钮返回。

当手动输入的三级分类无效时,Excel 会给出如下图所示的警告,提示用户进行修改或确认。

Step 3、在【审阅】中保护工作表。

图片 29

这样别人即使点击到这个单元格也看不到任何内容了。很神奇吧!

数据验证警告

关于保护表格和数据的内容就全部介绍完了,大家有什么想法或经验可以通过以下方式参与


讨论。大家有不明白的也可以联系我解答。

两个说明

关注李老师公众号【Excel轻松学】,轻松变成Excel高手!

  • 数据保护只能检测用户输入的数据,对于粘贴到 C3:C5000 区域内的数据,是无能为力的。
  • 如果公示太长了不易阅读,可以将公示拆开换行,使用 Alt + Enter 键。Excel 会忽略空格和换行符,所以可以有更直观的公式排版方式,像下面这样。
=IF(
      C3 = "",
      "",
      VLOOKUP(
          $C3,
          CHOOSE(
              {1,2},
              INDIRECT(Settings!$B$5):INDIRECT(Settings!$B$8),
              INDIRECT(Settings!$B$3):INDIRECT(Settings!$B$6)
          ),
          2,
          FALSE
      )
)

如此便完成了 Example 6.xlsx


下一课中,将做一个简要的总结,谈谈心得。