Excel 应用三.docx
- 文档编号:13736678
- 上传时间:2023-06-16
- 格式:DOCX
- 页数:24
- 大小:398.89KB
Excel 应用三.docx
《Excel 应用三.docx》由会员分享,可在线阅读,更多相关《Excel 应用三.docx(24页珍藏版)》请在冰点文库上搜索。
Excel应用三
Excel中的批处理技巧(3)
对于在办公室工作的同志来讲,日常工作多是搜集、整理、计算资料,并将其结果直观图形化并附以文字说明,微软公司的Excel可以称得上是说明我们处理这方面事务的“专家里手”。
笔者在工作实践中总结出多项Excel的“批处理”功能,简单实用,可以成倍提高工作效率。
1、活页簿的“批处理”
(1)打开多个活页簿:
在“档案”菜单中选取“打开档案……”,按住“Shift”键或“Ctrl”键并配以鼠标操作,在弹出的对话框档案列表中选取彼此相邻或不相邻的多个活页簿,然后按“确定”按钮,就可以一次打开多个活页簿。
(2)关闭所有打开活页簿而不退出Excel:
按下“Shift”键并在“档案”菜单中选取“关闭所有档案”,如无修改则活页簿被立即关闭;如果有未保存的修改,系统询问是否要保存修改。
2、工作表的“批处理”
按住“Shift”键或“Ctrl”键并配以鼠标操作,在活页簿底部选取多个彼此相邻或不相邻的工作表标签,然后就可以对其实行多方面的批量处理,笔者仅举几例进行说明。
(1)在选中的工作表标签上按右键弹出快捷菜单,进行插入和删除多个工作表的操作。
(2)在“档案”菜单中选取“页面设置……”,将选中的多个工作表设成相同的页面模式。
(3)通过“编辑”菜单中的有关选项,在多个工作表范围内进行搜寻、替换、定位操作。
(4)通过“格式”菜单中的有关选项,将选中的多个工作表的行、列、储存格设成相同的样式以及进行一次性全部隐藏操作。
(5)在“工具”菜单中选取“选项……”,在弹出的菜单中选取“窗口”和“编辑”按钮,将选中的工作表设成相同的窗口样式和储存格编辑属性。
(6)选中上述工作表集合中任何一个工作表,并在其上完成我们所需要的表格,则其它工作表在相同的位置也同时生成了格式完全相同的表格。
3、储存格的“批处理”
(1)“批量”调整列宽或行高。
对于相邻的多列,在其列标号处用鼠标选中整列,并将鼠标移至选中区域内,单按右键弹出快捷菜单,进行多列的插入与删除操作;将鼠标移至选中区域内任何一列的列标号处,当鼠标变成十字形时,按下左键并拖动,则将选中所有列的宽度调成相同的尺寸。
此时双按右键,则将选中的所有列的宽度调成最合适的尺寸,以和每列中输入最多内容的储存格相匹配。
行的操作与上述列的操作基本相同。
对于不相邻的多列,先按下“Ctrl”键并配合鼠标左键单点或按下左键并拖动的操作,选中整列,并将鼠标移至选中的区域内任何一列的列标号处,当鼠标变成十字形时,按下左键并拖动,则将选中所有列的宽度调成相同尺寸,而此时双按右键,则将选中的所有列的宽度调成最合适的尺寸。
行的操作与上述列的操作基本相同。
(2)“批量”设定格式
通过按下“Ctrl”键并配合鼠标左键单按或按下左键并拖动的操作选取不连续储存格,或者通过按下鼠标左键并拖动选取连续储存格,将鼠标移至选中的任何区域内,单按左键弹出快捷菜单,选取其中的“储存格格式……”一项进行“批量”设定储存格格式,或者利用“格式”工具条上的图标进行格式设定。
(3)“批量”输入
众所周知,在相邻储存格中输入相同的内容可以通过复制来实现。
对于不相邻储存格中输入相同的内容除通过复制粘贴来实现外,还可以采取如下快捷方法实现:
先按下“Ctrl”键并配合鼠标左键单点或按下左键并拖动的操作,选中所有要输入相同内容的储存格式,然后键入要输入内容,按下“Ctrl”键之后按“Enter”键,即可完成操作。
(4)“批量”求和
在Excel上工作时,对数字求和是经常遇到的操作,除传统的输入求和公式并复制外,对于连续区域求和可以采取如下方法:
假定求和的连续区域为m×n的矩阵型,并且此区域的右边一列和下面一行为空白,用鼠标将此区域选中并包含其右边一列或下面一行,也可以同时两者均选中,单按“常用”工具条上的“Σ”图标,则在选中区域的右边一列或下面一行自动生成了求和公式,并且系统能自动识别选中区域中的非数值型储存格,求和公式不会产生错误。
根据以上介绍,相信大家还能总结出许多对活页簿、工作表、储存格进行“批处理”的方法,来提高我们的工作效率。
充分利用Excel的样式功能
众所周知,Word具有定义样式的功能,我们可利用它快速的为文字定义字体、字号、字间距、颜色、文字对齐方式等格式内容,深受广大用户的欢迎。
其实Excel与Word一样也提供有样式功能,我们同样可利用它快速对某个活页簿的数字格式、对齐方式、字体字号、颜色、边框、图案、保护等内容进行设置。
一、利用样式功能快速定义活页簿格式
1.选定需要定义格式的活页簿范围。
2.执行“格式”菜单的“样式”指令,打开“样式”对话框。
3.从“样式名”列表框中选取合适的“样式”种类。
4.从“样式包括”列表框中选取是否使用该种样式的数字、字体、对齐、边框、图案、保护等格式内容。
5.单按“确定”按钮,关闭“样式”对话框,Excel活页簿的格式就会按照用户指定的样式发生变化,从而满足了用户快速、大批定义格式的要求。
二、增加新的样式或修改已有样式的内容
1.执行“格式”菜单的“样式”指令,打开“样式”对话框。
2.在“样式名”列表框输入新建样式的名称(用户若拟对老样式进行修改则应从“样式名”列表框中选取需要修改的样式)。
3.单按“更改”按钮,打开所示的“储存格格式”对话框。
4.利用“储存格格式”对话框中的数字、对齐、字体、边框、图案、保护等选项卡对该种样式的各项具体格式进行适当调整。
5.连续单按两次“确定”按钮,关闭所有对话框。
这样我们就完成了新增(或修改)指定样式具体内容的步骤,此后我们就可以利用这些新增或修改了的样式快速对Excel活页簿的格式进行修改了。
三、从其它活页簿中复制样式
除可对已有的样式进行修改及自定义所需的样式之外,Excel还允许我们将某个活页簿所包含的样式拷贝到其它活页簿中使用,以进一步扩大样式的使用范围,具体步骤为:
1.打开包含有需要复制样式的源活页簿及目标活页簿。
2.执行“格式”菜单的“样式”指令,打开“样式”对话框。
3.单按“合并”按钮,打开如图3所示的“合并样式”对话框。
4.在“合并样式来源”框中选取包含有要复制样式的源活页簿并单按“确定”按钮,源活页簿中所包含的一切样式就会拷贝到目标活页簿中(对于同名的样式,系统将会要求用户选取是否覆盖),然后我们就可以在目标活页簿中直接加以使用了,从而免去了重复定义之苦。
从上面的介绍中可以看出,利用Excel的样式功能快速定义活页簿的格式是非常方便的,广大用户应充分加以利用。
Excel中自定义格式的特殊运用
功能强大的MicrosoftExcel为我们的工作助了一臂之力,但在实际使用中因为要实现的效果经常会有些特殊性,所以需要我们自己来不断挖掘它的潜力,让它更高效率的为你完成手头的工作。
以下笔者来谈谈灵活运用Excel的自定义格式来减少工作量的话题:
一、实现具有共同特征的数据的快速输入
笔者在工作中需要用Excel记录大量进口发票的各种信息,这些发票的发票号码都很长,但是有一定的共同特征,就是它们总是以一个固定的文字字符串“NBGLE—”开头,后面是不同的数位。
如果每次输入完整的发票号,势必有着相当的工作量,经过一番研究,终于发现通过设置自定义格式可以解决。
右按需要输入发票号码的储存格,选取快捷菜单中的“设置储存格格式”——“数字”——“自定义”,可以创建一款专门用于发票号码的格式。
注意,自定义格式不能直接创建,只能通过修改Excel内置的格式来创建。
任意选取一个已有的格式(没关系,它们并不会真的被你改掉),然后将原来的格式修改成“NBGLE—”@,确定后,这个储存格在输入发票号码时就不必再输入前面的文字字符串了。
例如发票号码为“NBGLE—200001331”,则你只需要输入后面的数字“200001331”就行了,Excel会自动为你加上前面的“NBGLE—”,爽吧!
二、添加对资料的说明
笔者在工作中需要处理多种外币,当同时处理几种货币时需要标识出币别,下面就以日元为例来介绍一下该如何设置:
依然是右按需要输入数据的储存格,选取快捷菜单中的“设置储存格格式”——“数字”——“自定义”,选取一个与后面所讲格式相近的格式(这样修改起来比较方便),然后修改并设置为#,##0.00"JPY";-#,##0.00"JPY",这样一来,任何数据将在保留千分位及两位小数、负值显红的一般格式外,还在其末尾加上“JPY”说明,以标识出自己的币别是日元。
例如资料“12389.4”将显示为“12,389.40JPY”。
当然,大家可以根据自己的需要把“JPY”改换成任意文字,如“USD”、“DEM”等。
Excel对数据的安全管理
Excel不仅是一种功能齐全的电子表格处理软件,也是一种操作简便的数据库管理工具,因此,数据的安全管理也是Excel的主要功能之一。
据笔者的体会,Excel对数据的安全管理大致可分为三个层次:
一、对储存格进行读写保护
储存格是Excel执行其强大的计算功能最基本的载体,对储存格的读写保护是Excel对数据进行安全管理的基矗对储存格的保护基本可分为写保护和读保护两类,所谓写保护就是对储存格中输入信息加以限制,读保护是对储存格中已经存有信息的浏览和查看加以限制。
对储存格的写保护有两种方法:
(一)对储存格的输入信息进行有效性检测。
首先选定要进行有效性检测的储存格或储存格集合,然后从数据菜单中选取“有效数据”选项,通过设定有效条件、显示信息和错误警告,控制输入储存格的信息要符合给定的条件。
(二)设定储存格的锁定属性,以保护存入储存格的内容不能被改写。
可分为以下步骤:
1.选定需要锁定的储存格或储存格集合;
2.从格式菜单中选取“储存格”选项;
3.在设置储存格格式的弹出菜单中选取“保护”接口;
4.选中“锁定”;
5.从工具菜单中选取“保护”选项,设置保护口令。
至此即完成了对储存格的锁定设置,当有人企图对被保护的储存格进行修改时,Excel会立即发出警告。
对储存格的读保护有三种方法:
(一)通过对储存格颜色的设置进行读保护。
例如:
将选定储存格或储存格集合的背景颜色与字体颜色同时设为白色,这样,从表面看起来储存格中好像是没有输入任何内容,用户无法直接读出储存格中所存储的信息。
(二)用其它画面覆盖在需要保护的储存格之上,遮住储存格的本来面目,以达到读保护目的。
例如:
使用绘图工具,画一不透明矩形覆盖在储存格之上,从格式菜单中选定矩形的“锁定”选项,然后保护工作表,以保证矩形不能被随意移动。
这样,用户所看到的只是矩形,而看不到储存格中所存储的内容。
(三)通过设置储存格的行高和列宽,隐藏选定的储存格,然后保护工作表,使用户不能直接访问被隐藏的储存格,从而起到读保护的作用。
二、对工作表的保护
在Excel的应用中,对工作表的保护是Excel对数据进行安全管理的核心。
对于工作表的保护大致可从两个方面入手:
(一)运用Excel本身提供的工作表保护功能,通过设置用户自定义口令对工作表予以保护。
在这种方法中值得一提的是:
如果口令用英文字母设置,要求区分大小写。
(二)通过对工作表的Visible属性的设置隐藏工作表,以保护工作表中所有存储数据的安全。
下列程序代码是一个名为“隐藏工作表”的宏,将工作表sheet1的Visible属性设置为False,当运行该宏之后,工作表sheet1将变为隐藏的工作表,用户不能直接访问,从而对sheet1起到保护作用。
Sub隐藏工作表()
Worksheets(〃sheet1〃)Visible=False
EndSub
如果用户需要对sheet1进行必要的修改,只需将sheet1的Visible属性设置为True即可。
宏程序代码如下:
Sub显示工作表()
Worksheets(〃sheet1〃)Visible=True
EndSub
三、对活页簿的保护
在Excel对象的层次结构中,活页簿对象仅次于最顶层的Application。
保护活页簿的方法较多,在这里简要介绍三种较为有效的方法:
(一)利用Excel对活页簿的保护功能,用户通过设置自定义口令对活页簿予以保护。
(二)灵活运用Auto_open宏对活页簿予以保护。
Auto_open宏是打开Excel活页簿时自动执行的宏,Auto_open对Excel数据安全管理可起到两个方面的作用:
一是通过对操作环境的设置,取消Excel的默认菜单,使应用者不能轻易地调用Excel,以保护Excel活页簿;二是提示用户输入进入应用程序口令,此处的口令设置同第一种方法具有很大差异,在这里可以同时设置多个口令,根据程序开发者的限制,在用户应用程序中每个口令可代表不同的保护层次。
(三)创建和使用自定义用户窗体达到保护活页簿的目的。
由于在Excel中VBA执行Show方法,显示一个用户自定义窗体时,只能执行该窗体类模块中事件程序的程序代码,所以,用户可以完全通过自定义窗体来应用Excel,只需将VBAProject保护起来,就能使数据的安全管理达到最佳状态,同时给予应用程序开发者较大的活动空间,能充分运用开发者的经验和智慧。
综上所述,Excel对数据的安全管理具有独自的特点,加以灵活运用,会使你更加钟爱Excel。
对储存格的读保护有三种方法:
(一)通过对储存格颜色的设置进行读保护。
例如:
将选定储存格或储存格集合的背景颜色与字体颜色同时设为白色,这样,从表面看起来储存格中好像是没有输入任何内容,用户无法直接读出储存格中所存储的信息。
(二)用其它画面覆盖在需要保护的储存格之上,遮住储存格的本来面目,以达到读保护目的。
例如:
使用绘图工具,画一不透明矩形覆盖在储存格之上,从格式菜单中选定矩形的“锁定”选项,然后保护工作表,以保证矩形不能被随意移动。
这样,用户所看到的只是矩形,而看不到储存格中所存储的内容。
(三)通过设置储存格的行高和列宽,隐藏选定的储存格,然后保护工作表,使用户不能直接访问被隐藏的储存格,从而起到读保护的作用。
Excel中对交叉内容进行报表合并
在学校的成绩统计中,有的老师会遇到这样的问题:
比如说手头有两份成绩报表,一份是语文成绩,另一份是数学成绩,现在需要将这两份成绩报表合并起来,并计算出总分。
本来这在Excel中是非常容易实现的事情,但这还不算,最大的困难在于这两份成绩报表中的学生情况并不一致,即有一部分学生只有单科成绩,这样一来,两份成绩报表中的学生名单就出现了交叉(既有相同部分,又有不同部分)。
对于这种问题,我们应该怎样解决呢?
有的人可能会想到用VBA写一段程序代码来完成这样的工作,但毕竟这东东不是每个人都会的,单为了这么点小事情去专门学习VBA,又显得有些小题大做了。
况且这摆在眼前的工作,哪来得及等你去学习VBA。
其实,会者不难,只要你能灵活运用Excel中的函数与公式,这个问题也是能被轻松解决的。
在介绍笔者的具体方案之前,请大家先耐心来进行一些有关此方案的Excel函数及公式知识的准备工作。
(先别急嘛,所谓磨刀不误砍柴功!
)
首先我们要来学习的是Excel中的COUNTIF函数。
COUNTIF(range,criteria)函数的功能是计算给定区域内满足特定条件的储存格的数目。
Range自变量是需要计算其中满足条件的储存格数目的储存格区域。
而Criteria自变量则用以确定哪些储存格将被计算在内的条件,其形式可以为数字、陈述式或文字。
假设A1:
A5中的内容分别为“张三”、“李四”、“王五”、“张三”、“张三”,则COUNTIF(A1:
A5,"张三")等于3。
接下来上场的是笔者最钟情的VLOOKUP函数,VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)函数的功能是在表格或数值数组的首列搜寻指定的数值,并由此返回表格或数组当前行中指定列处的数值。
Lookup_value自变量为需要在数据表第一列中搜寻的数值。
索引卷标table_array自变量为需要在其中搜寻数据的数据表,可以使用对区域或区域名称的引用,例如数据库或数据清单。
Col_index_num自变量为table_array中待返回的匹配值的列序号。
Col_index_num为1时,返回table_array第一列中的数值;col_index_num为2,返回table_array第二列中的数值,以此类推。
最后一个自变量Range_lookup为一逻辑值,指明函数VLOOKUP返回时是精确匹配还是近似匹配。
当其值为0时,将返回精确值;当其值为1时,将返回近似匹配值。
关于VLOOKUP函数,在Excel的说明档案中有非常详细的解释,本文限于篇幅的原因不能在此讲得更多,大家有兴趣的话可以自己进行学习。
请参阅
【VLOOKUP
在一数组或表格的最左栏中寻找含有某特定值的字段,再传回同一列中某一指定储存格中的值。
如果用来比对的数值位于您所要寻找的数据之左边直栏时,就必须使用到VLOOKUP函数,而非HLOOKUP函数。
VLOOKUP中的V表示「Vertical」之意。
语法
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Lookup_value 是您打算在数组 (数组:
用来建立产生多个结果或排列在列或栏中操作一组自变量的单一公式。
数组范围共享公用公式;数组常数为作为自变量的一组常数。
)的最左栏中搜寻的值。
Lookup_value可以是数值、参照地址或文字字符串。
Table_array 是要在其中搜寻的数据表格。
通常是储存格范围的参照地址或类似数据库或清单的范围名称。
∙如果range_lookup为TRUE,则table_array第一栏的值必须以递增次序排列:
...,-2,-1,0,1,2,...,A-Z,FALSE,TRUE;否则VLOOKUP无法给予正确的值。
如果range_lookup为FALSE,则table_array不须事先排序。
∙您可以选择[资料]菜单上的[排序]指令设定[递增],以确保数组中的值依递增次序排列。
∙Table_array第一栏里的值,可以是文字、数字或逻辑值。
∙字母的大小写被视为是相同的。
Col_index_num 是个数值,代表所要传回的值位于table_array中的第几栏。
如果col_index_num自变量值为1,传回在table_array第一栏的值,如果col_index_num自变量值为2,传回table_array第二栏的值,依此类推。
如果col_index_num小于1,则VLOOKUP传回错误值#VALUE!
;如果col_index_num超过table_array总栏数,则VLOOKUP传回错误值#REF!
。
Range_lookup 是个逻辑值,用来指定VLOOKUP要寻找完全符合或部分符合的值。
当此自变量值为TRUE或被省略了,会传回部分符合的数值;也就是说,如果找不到完全符合的值时,会传回仅次于lookup_value的值。
当此自变量值为FALSE时,VLOOKUP函数只会寻找完全符合的数值,如果找不到,则传回错误值#N/A。
批注
∙如果VLOOKUP函数找不到lookup_value,且range_lookup为TRUE时,则使用仅次于lookup_value的值。
∙如果lookup_value比table_array第一栏中的最小值还小,则VLOOKUP传回错误值#N/A。
∙如果VLOOKUP函数找不到lookup_value,且range_lookup为TRUE时,则VLOOKUP传回错误值#N/A。
范例
如果将它复制到另一空白的工作表,将更易于了解此范例。
如何做?
1.建立空白活页簿或工作表。
2.在[说明]主题中选取范例。
请不要选取列或栏的表头。
选取[说明]中的范例
3.按CTRL+C。
4.在工作表中选取储存格A1,并按CTRL+V。
5.若要在检视结果与检视传回结果的公式之间切换,请按CTRL+`(重音符符号),或在[工具]菜单上,将鼠标指针点选[公式稽核],然后按[公式稽核模式]。
本范例假设空气的大气压力等于1。
1
2
3
4
5
6
7
8
9
10
A
B
C
密度
黏性
温度
0.457
3.55
500
0.525
3.25
400
0.616
2.93
300
0.675
2.75
250
0.746
2.57
200
0.835
2.38
150
0.946
2.17
100
1.09
1.95
50
1.29
1.71
0
公式
叙述〈结果〉
=VLOOKUP(1,A2:
C10,2)
于A栏中搜寻1,并传回B栏中同列的数值(2.17)
=VLOOKUP(1,A2:
C10,3,TRUE)
于A栏中搜寻1,并传回C栏中同列的数值(100)
=VLOOKUP(.7,A2:
C10,3,FALSE)
于A栏中搜寻0.746。
因于A栏中并无完全符合的数值,所以传回一个错误值(#N/A)
=VLOOKUP(0.1,A2:
C10,2,TRUE)
于A栏中搜寻0.1。
因为0.1小于A栏中最小的数值,所以传回一个错误值(#N/A)
=VLOOKUP(2,A2:
C10,2,TRUE)
于A栏中搜寻2。
并传回B栏中同列的数值(1.71)
另外在下面的方案中还将用到一些别的函数,如IF、ISNA等,就不单独介绍了,笔者将在介绍方案时一并向大家讲解。
最后我们要了解的是关于Excel公式中绝对参照和相对参照的概念。
相对参照是指公式中对储存格或储存格区域的引用仅仅是相对于包含公式的储存格的相对位置。
引用的源储存格或储存格区域会随着公式所在储存格的改变而改变。
例如A1储存格包含公式=B1,当我们把A1的公式复制到A2后,则A2储存格包含公式=B2,B1自动被调整为B2了。
这一点在大量复制公式时特别有用,因此也是Excel公式的默认引用方式。
绝对参照与相对参照恰恰相反,无论公式所在储存格怎么变动,引用的源储存格或储存格区域也不会发生任何改变。
绝对参照的标识符是美元符号“$”。
好了,下面就来正式介绍此问题的解决方案了:
假设已有活页簿档案“成绩单”,其工作表“语文”和“数学”分别是前文所说
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 应用三 应用