龙逸凡Excel培训手册之潜龙在渊三.docx
- 文档编号:30343319
- 上传时间:2023-08-13
- 格式:DOCX
- 页数:15
- 大小:166.49KB
龙逸凡Excel培训手册之潜龙在渊三.docx
《龙逸凡Excel培训手册之潜龙在渊三.docx》由会员分享,可在线阅读,更多相关《龙逸凡Excel培训手册之潜龙在渊三.docx(15页珍藏版)》请在冰豆网上搜索。
龙逸凡Excel培训手册之潜龙在渊三
十四、隐藏工作表、设置工作表标签颜色、设置背景
操作:
【格式】→〖工作表〗→“隐藏”
隐藏工作表分两个层次:
普通隐藏、深层隐藏。
普通隐藏:
在【格式】→〖工作表〗子菜单将选定的工作表隐藏起来。
此类隐藏可用同样的方法将隐藏的工作表取消。
深层隐藏要用VBA,将工作表的Visible属性设置为xlSheetVeryHidden即可。
用此方法所隐藏的表在【格式】→〖工作表〗→“取消隐藏”是看不见的,自然就无法取消对它的隐藏了。
链接1:
隐藏、取消隐藏工作簿窗口
隐藏:
打开工作簿,【窗口】→〖隐藏〗;
取消隐藏:
【窗口】→〖取消隐藏〗。
如果〖取消隐藏〗命令无效,则说明工作簿中没有隐藏的工作表。
如果〖重命名〗和〖隐藏〗命令均无效,则说明当前工作簿正处于防止更改结构的保护状态。
需要撤消保护工作簿之后,才能确定是否有工作表被隐藏。
设置工作表标签颜色:
【格式】→〖工作表〗→“工作表标签颜色”。
也可以在工作表标签上单击右键,再单击“工作表标签颜色”。
选择所需颜色,“确定”退出。
逸凡注:
如果工作表标签用颜色做了标记,则当选中该工作表标签时,将按用户指定的颜色为其名称添加下划线。
如果工作表标签显示有背景色,则该工作表处于未选中状态。
设置工作表背景:
如果你看腻了白色的工作表背景,不妨添加一张美女图片作为背景吧。
一边工作一边欣赏PLMM,美哉!
乐哉!
逸凡注:
背景图案不能打印,并且不会保留在保存为网页的单个工作表或项目中。
然而,如果将整个工作簿发布为网页,则背景将保留。
友情提醒:
增加背景图片将增大工作簿的大小。
技巧:
如果某个工作表背景的美女很漂亮,看得你口水都流出来了,想把她从工作表里揪出来设为墙纸,怎么揪呢?
?
方法:
【文件】→〖另存为网页〗,把工作簿存到某文件夹,然后到那文件夹就能找到了!
十五、条件格式
操作:
【格式】→〖条件格式〗
条件格式就是当单元格的值满足你预先设定的条件时,Excel自动将单元格设置成你预先设定的格式。
单元格的值变化,格式就相应变化。
此功能一般用于区分不同区间的值、不同长度的文本等,也可用于重复值提示。
条件格式的条件分为两类:
单元格数值、公式,如下图:
如果条件为“单元格数值”选项,则可以直接键入常量值或公式。
如果输入公式,则必须以等号(=)开始。
如果条件为“公式”选项,则输入用于逻辑判断的公式。
使用单条件的条件格式不难。
在此不详述。
使用多条件来定义格式
多条件格式的规则是:
条件1优先于条件2,条件2优先于条件3,也就是说,首先判断是否满足条件1,如果满足则执行条件1对应的格式,不满足条件1,才判断是否满足条件2,如果满足则执行条件2对应的格式,其余类推。
最多可以指定三个条件,如果指定条件中没有一个为真,则单元格将保持现有的格式。
要注意各条件的顺序,设置不合理,则达不到期望的效果。
实例见下表(双击打开表格查看条件格式设置)。
逸凡注:
1、在条件格式中你不能根据条件设置字体、改变字体大小、设置上下标。
2、将格式复制到其他单元格中,可以使用格式刷(请注意在条件中正确使用单元格的相对引用,否则使用格式刷或复制格式将达不到预期的效果)。
3、复制其他单元格到设有条件格式的单元格时,将覆盖条件格式。
如果要保留原条件格式,请使用选择性粘贴—粘贴数值。
第七讲、实用功能介绍及技巧之二
一、共享工作簿
操作:
【工具】→〖共享工作簿〗
1、什么是共享工作簿
共享工作簿是指允许网络上的多位用户同时查看和修订的工作簿(说白了就是几个人对同一工作簿同时打开并操作,其好处是不言而喻的)。
保存工作簿的用户可以看到其他用户所做的修订(必须使用Excel97或更高版本来修改共享工作簿)。
每次保存共享工作簿时,该工作簿就会按其他用户在您上次保存之后所做的修订进行更新。
如果要打开共享工作簿以监视进程,则可在指定的时间间隔内让Excel自动更新工作簿,可以保存工作簿也可以不保存。
那么如何创建共享工作簿呢?
在【工具】菜单里的〖共享工作簿〗子菜单,将编辑选项卡里的“允许多人编辑”勾选上即可多人同时操作工作簿了。
2、修订冲突
在向共享工作簿保存更改时,正在编辑该工作簿的其他人员可能已经将自己的更改保存在了同一个单元格中。
这种情况下就会产生修订冲突,这时将出现冲突解决对话框以便决定保存哪个人的更改。
若要保留自己的修订或其他人的修订并转到下一个修订冲突上,请单击“接受本用户”或“接受其他用户”。
若要保留自己的所有剩余修订或所有其他用户的修订,请单击“全部接受本用户”或“全部接受其他用户”。
若要使自己的修订覆盖所有其他用户的修订,而且不再看到“解决冲突”对话框,请关闭此功能。
3、不可用于共享工作簿中的功能
由于Excel的部分有一些功能在工作簿共享后只能查看或使用,而不能更改,所以在工作簿共享前应设置好这些功能。
只有在停止使用共享工作簿的条件下,才能使用下面功能:
不可用的功能
替换方法
创建列表
无。
成块插入或删除单元格
可以插入整行和整列。
删除工作表
无。
合并或拆分合并的单元格
无。
添加或更改条件格式
单元格值更改时,现有条件格式继续存在,但不可更改这些格式或重定义条件。
添加或更改数据有效性
键入新值时,单元格继续有效,但不能更改现有数据有效性的设置。
创建或更改图表或数据透视表
可以查看现有的图表和报表。
插入或更改图片或其他对象
可以查看现有的图片和对象。
插入或更改超链接
现有超链接继续有效。
使用绘图工具
可以查看现有的图形对象和图形。
指定、更改或删除密码
现有密码仍然有效。
保护或不保护工作表或工作簿
现有保护仍然有效。
创建、更改或查看方案
无。
创建组及分级显示数据
可以继续使用现有分级显示。
插入自动分类汇总
可以查看现有分类汇总。
创建数据表
可以查看现有数据表。
创建或更改数据透视表
可以查看现有报表。
写入、记录、更改、查看或分配宏
可以运行现有的只使用可用功能的宏。
可以将共享工作簿的操作录制在一个存储于其他非共享工作簿的宏中。
更改或删除数组公式
原有数组公式继续正确地计算。
技巧1:
若要查看另外还有谁打开工作簿,请单击“工具”菜单中的“共享工作簿”,再单击“编辑”选项卡。
技巧2:
如果希望定期自动更新其他用户的更改并加以保存或不保存,请单击“工具”菜单中的“共享工作簿”,再单击“高级”选项卡,然后在“更新”下,单击所需的选项。
技巧3、其他用户未保存前,其对共享工作簿进行的修订不会显示。
因而要停止共享工作簿必须请所有其他用户保存并关闭共享工作簿。
否则,他们未保存的数据将会丢失。
二、保护工作表、工作簿
操作:
【工具】→〖保护〗
1、保护工作表
★保护工作表及锁定单元格中的内容:
可防止未解除锁定的单元格、查看隐藏的行或列、查看隐藏的单元格中的公式
★选择锁定单元格:
将此选项去掉可阻止用户将鼠标指向您在“单元格格式”对话框中“保护”选项卡上的“锁定”复选框中已选中的单元格。
★选择解除锁定的单元格:
同上,不赘述。
★单元格格式:
清除此项时,可以防止用户更改“单元格格式”或“条件格式”对话框中的任何选项。
其他可禁止用户使用插入/删除行/列、排序、使用自动筛选、使用数据透视表等功能,
2、允许用户编辑区域
3、保护工作簿
★结构:
防止用户进行如下操作:
查看隐藏的工作表、移动删除隐藏或更改工作表名称、插入新工作表或图表工作表、将工作表移动或复制到其他工作簿中
★窗口:
防止用户进行如下操作:
在工作簿打开时,更改工作簿窗口的大小和位置、移动窗口、调整窗口大小或关闭窗口。
但是,用户可以隐藏或取消隐藏窗口
4、保护并共享工作簿
链接:
如要设定工作簿的打开或修改密码,点击【文件】菜单→〖另存为〗子菜单,打开“另存为”对话框,在此对话框的“工具”菜单下的“常规选项”里设置。
三、自定义工具栏
操作:
【工具】→〖自定义〗
分为工具栏、命令、选项三个选项卡。
在“命令”选项卡可以将你要经常用到的一些命令拖到工具栏上,以方便操作,不要时将其拖回去就是了。
建议增加的工具:
查找、插入/删除行\列、撤消合并单元格。
技巧1:
可将工具类里的“照相机”、“朗读单元格”工具拖至工具栏。
“照相机”类似于QQ中的截屏工具,它是将单元格象照相一下照下来(本手册“高级筛选”部分中的单元格图片就是使用“照相机”照下来的),照下来的图片还可根据源数据的变化而变化,呵呵,够先进吧。
照相机不能复制图表。
“朗读单元格”是将单元格内的文字朗读出来,这个功能在样对时很实用,只是语速有点慢、间隔有点长,当然,你也可在“控制面板→语音”里将朗读语速加快一点。
另:
也可在【工具】菜单→〖语音〗子菜单将“显示从文本到语音工具栏”勾选上,在“从文本到语音”工具栏,选择“按下回车键开始朗读”就可输完后一回车Excel就会自动读出你输入的内容,让你一边输入一边检查,提高工作的效率。
请将“选项”卡里的“始终显示整个菜单”勾选上,否则菜单仅显示最近使用的命令。
逸凡注:
“仅显示最近使用的命令”-----这是微软自作聪明的一个设计。
四、工作簿选项设置
操作:
【工具】→〖选项〗
你是否遇到过以下怪事:
Excel的网格线颜色变了、甚至不见了、单元格不能直接编辑或不能下拉填充了、编辑栏不见了、行号列标不见了、滚动条不见了、工作表标签也不见了、数据源变了而公式计算出的结果还是原来的,真是天下大乱了!
这些问题的根源都在【工具】→〖选项〗里。
具体奥秘自己去探索吧,不明白的请加入QQ群19648285讨论。
链接:
如果“常用”工具栏或“格式”工具栏不见了,请将【视图】菜单→〖工具栏〗子菜单里的“常用”或“格式”勾选上就是了。
技巧1:
自动添加公式
将【工具】→〖选项〗→“编辑”选项卡→“扩展数据列表格式及公式”复选框勾选上,则自动为添加到列表底端的新增项自动设置格式以与列表中其他项的格式相匹配,同时复制在每一行中重复使用的公式。
(逸凡注:
新增项目与数据列表的最后一行之间不应含有空行。
为了扩展格式和公式,新行之前的五行中至少应有三行必须包含格式和公式)。
技巧2:
自动设置小数点
将【工具】→〖选项〗→“编辑”选项卡→“自动设置小数点”复选框勾选上,并在“位数”框中输入小数位数。
可以指定Excel自动根据所输入的常数(如果输入的数字含有小数点则此选项不发生作用)将小数点添加到常数中的特定位置。
例如:
将自动设置小数点位数设置为4,如果输入123456,则自动变为12.3456。
如果“位数”框保留为空白或设置为0(零),则需要手动输入小数点。
五、对数据进行排序
操作:
【数据】→〖排序〗
可按三个关键字进行排序,还可选择排序的方向(按行或列)、排序的方法(按字母排序按笔划排序)。
排序的功能比较简单,主要了解一下排序的顺序。
在按升序排序时,MicrosoftExcel使用如下次序(在按降序排序时,除了空白单元格总是在最后外,其他的排序次序反转)
在按字母先后顺序对文本项进行排序时,Excel从左到右一个字符一个字符地进行排序。
文本以及包含数字的文本,按下列次序排序:
0123456789(空格)!
"#$%&()*,./:
;?
@[\]^_`{|}~+<=>ABCDEFGHIJKLMNOPQRSTUVWXYZ
在逻辑值中,FALSE排在TRUE之前;所有错误值的优先级相同;空格始终排在最后。
技巧:
如何按4个关键字进行排序
首先,打开排序对话框,在“第三关键字”框中,单击最不重要的数据列,确定退出。
然后再次打开排序对话框,在“主要关键字”和“次要关键字”框中,单击需要排序的其他3个数据列,选中所需的其他排序选项,再单击“确定”。
六、筛选数据
操作:
【数据】→〖筛选〗
尽管Excel的筛选查询功能远比不上Access,但其提供的筛选功能还是比较丰富了,基本可以满足一般需要。
其筛选菜单主要有如下功能:
(一)自动筛选
1、对最小或最大的前N个数进行筛选
先自动筛选,然后单击包含数字的列中的箭头
,再单击(“前10个”)。
然后进入自动筛选前10个的对话框进行相关设置。
逸凡注:
进行筛选并不仅限于前10个,而是前N个,可根据你的需要自行设定。
也可设置成最大或最小的前百分之N。
2、对包含或不包含特定文本的行进行筛选
单击包含数字的列中的箭头
,再单击(“自定义”),进入“自定义自动筛选方式”对话框进行相关设置。
可设置成“等于、不等于、包含、不包含”等关系。
逸凡注:
最多可按两个条件进行自动筛选、可使用通配符。
3、对空白或非空白单元格进行筛选
单击包含数字的列中的箭头
,再单击(“空白”或“非空白”)。
只有当前筛选的数据列中含有空白单元格时,“空白”和“非空白”选项才会出现。
4、对大于或小于另一个数字的数字进行筛选
5、对等于或不等于另一个数字的数字进行筛选
6、对文本字符串的开始或结尾进行筛选
如果你要筛选出以或不以某字符串开始或结尾的行请使用此功能。
单击包含数字的列中的箭头
,再单击(“自定义”)。
进入“自定义自动筛选方式”对话框进行相关设置,在左边的框中,选择“始于”或“并非起始于”,“止于”或“并非结束于”等条件。
右边框输入所需文本。
本功能可使用通配符。
7、对顶部或底部数字按百分比进行筛选
参见“对最小或最大的前N个数进行筛选”中的注释。
(二)高级筛选
高级筛选难在掌握如何设定筛选条件。
高级筛选的条件和数据库函数DSUM、DCOUNT等使用的条件一样。
逸凡注:
条件区域至少需有三行:
条件区域的列标签、条件行、空白行(条件区域必须具有列标签,在条件值与列表区域之间至少留一个空白行)。
条件行的行数视需要而定,但至少要有一行。
下面讲解一下如何设定筛选条件:
条件区域行与行的关系是“或(or)”的关系,列与列是“并(and)”的关系。
理解了这一句话就掌握了如何设定筛选条件。
举例说明:
1、如果要筛选出审计部、评估部和管理咨询部等三个部门的职工,筛选条件为图一的单元格区域。
2、如果要筛选出审计部中男性职工,筛选条件为图二的单元格区域。
3、如果要将符合以下三条件之一(审计部的职工或是所有的男性职工或工资大于5的职工)的职工筛选出,筛选条件为图三的单元格区域。
4、如果要将符合以下三条件之一(审计部中的女职工或是所有的男性职工或工资大于10的职工)的职工筛选出,筛选条件为图四的单元格区域。
(图一)(图二)(图三)
(图四)(图五)
逸凡注:
1、对列应用筛选后,可用于对其他列进行筛选的值只能是那些在当前筛选区域中显示的值,也就是只能在原来筛选的基础上再进行筛选,除非你先取消原筛选,切记!
!
2、当单击箭头
时,只显示列表中的前1000个独立的项。
3、筛选条件不能包含空行(如图五的筛选条件就是错误的),否则将会筛选出所有数据。
七、对数据进行分类汇总
操作:
【数据】→〖分类汇总〗
分类汇总可自动计算列表中各类别的汇总和总计值。
当插入自动分类汇总时,Excel将分级显示列表,以便为每个分类汇总显示和隐藏明细数据行。
分类汇总的方式有求和、计数、求平均值、最大(最小)值、乘积、方差等。
要删除分类汇总,请在含有分类汇总的列表中,单击任一单元格,在【数据】菜单上,单击“分类汇总”,单击“全部删除”即可。
逸凡注:
1、插入分类汇总前,请先将列表排序(以便将要进行分类汇总的行组合到一起),再分类汇总。
请确保数据列表的第一行的每一列都有标志,并且同一列中应包含相似的数据,在区域中没有空行或空列。
2、可再次使用“分类汇总”命令来添加多个具有不同汇总函数的分类汇总,也可以使用嵌套分类汇总将更小的分组的分类汇总插入现有的分类汇总组中。
若要防止覆盖已存在的分类汇总,请清除“替换当前分类汇总”复选框。
使用嵌套分类汇总的实例图片见上图。
3、分类汇总的实质就是使用subtotal函数进行统计。
关于此函数的用法请参看Excel联机帮助。
八、设置数据有效性
操作:
【数据】→〖有效性〗
数据有效性就是为了确保数据的正确而设定条件验证、限制单元格数据的输入。
还可以设定提示信息和出错警告,以帮助使用者正确使用工作表。
常见的有效性条件有:
整数、小数、序列、日期、时间、文本长度。
以上有效性条件除“序列”之外,其他的都可设定一个区间值。
“序列”所引用的单元格必须指向相同工作表上的单元格。
技巧1:
如果“序列”必须引用不同的工作表或工作簿,可使用定义的名称。
技巧2:
在“数据有效性”对话框的“输入法模式”选项卡可设置单元格的输入法模式,当选定单元格时自动更改输入法,实现了中英文输入方式之间的自动切换。
如果你认为有效性条件所设定的值只能是常量,那就太小瞧Excel了,这些条件值可以设定为公式,并非条件为“自定义”时才可设定公式。
如最小值可输入“=MIN(D8:
D21)”,最小值“=MIN(D8:
D21)”,也可输入更复杂的公式。
有效性除了可以限定输入的数值在某一区间外,还可利用其“自定义”功能引申出其它更牛的绝招,如:
1、不允许输入重复值:
自定义条件为“=COUNTIF($A$2:
$A$10,A2)=1”
2、仅允许输入特定格式的文本:
如只能输入以“CQ”或“HN”开头的六个字符的文本,条件为“=OR(AND(LEFT(C2,2)="cq",LEN(C2)=6),AND(LEFT(C2,2)="hn",LEN(C2)=6))”。
3、按大小顺序输入:
即后面输入的数据(日期或数字)不能大于前面的数据,则数据有效性条件为“=MAX($E$2:
E2)=E2”。
4、智能列表功能:
根据其它单元格的数值,自动提供相应的列表。
其实这个功能很简单,就是在序列条件里使用了if的嵌套功能“=IF(A2="审计部",$E$2:
$E$5,IF(A2="评估部",$E$6:
$E$9,IF(A2="管理咨询部",$E$10:
$E$12,$E$13:
$E$15)))”
上述有效性的实例参见下表(双击打开工作表):
网友一束阳光补充:
用IF嵌套功能做有效性的序列,有一定的局限性。
第一个不足是,IF只能嵌套7层,所以部门不能超过9个;第二个不足是,当部门增加时,序列不会自动增加;第三个不足是,当部门里的人员增加或减少时,有效性需要重新定义。
要解决这些问题,建议在有效性里定义序列时用OFFSET和MATCH两个函数。
九、分列
操作:
【数据】→〖分列〗
从其它文件复制过来的数据,粘贴到工作表后都在同一列,如果数据的各字段均以某字符分隔或各字段数据长度一致,如下图,则可以使用文本分列来将其分拆到各列。
如果文本文件中的项以制表符、冒号、分号或其他字符分隔,请选择“分隔符号”。
如果文本文件中的所有项的长度相同,请选择“固定宽度”。
在对话框的“分隔符号”区域选择数据所包含的分隔符。
如果所需的字符未列出,请选中“其他”复选框,然后在包含插入点的框中键入字符。
如果数据类型为“固定宽度”,则这些选项不可用。
如果在数据字段之间数据所包含的分隔符由多个字符组成,或者数据包含多个自定义分隔符,请单击“连续分隔符视为单个处理”。
从“文本识别符号”中选择要使用的符号以指定该符号中所包含的数据将作为文本处理。
在“数据预览”中,选择列。
然后在“列数据格式”下,单击要为转换文本的选定列应用的数据格式。
若要在导入文本文件时排除某个数据列,请在“数据预览”下选择该列,再选择“不导入此列(跳过)”。
具体方法按照“文本分列向导”的提示操作就是了。
十、合并计算
操作:
【数据】→〖分列〗
Excel可以将格式相同的工作表数据、格式不同但具有相同行标志(列标志)、甚至任意数据合并在一起。
方法分别是通过位置进行合并计算、按分类进行合并计算、使用三维公式合并计算。
此功能的使用方法详见Excel的联机帮助“关于合并计算数据”,本手册不予详述。
十一、数据透视表
操作:
【数据】→〖数据透视表和数据透视图〗
数据透视表是交互式报表,可快速合并和比较大量数据,功能非常强大。
本部分内容拟在《龙逸凡Excel培训手册》之飞龙在天讲解。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 龙逸凡 Excel 培训 手册