实用EXCEL技巧.docx
《实用EXCEL技巧.docx》由会员分享，可在线阅读，更多相关《实用EXCEL技巧.docx（35页珍藏版）》请在冰点文库上搜索。
实用EXCEL技巧
Excel实用技巧40招
6、Excel避免计算误差
可以进行如下操作：
依次在Excel菜单栏中点击“工具—选项—重新计算”，将“工作薄选项”中的“以显示值为准”复选框选中，确定退出即可。
也可以用函数，方法：
round（（计算公式），2），其中的“round”是四舍五入函数，其中的“计算公式”中输入你的计算算式，例如A1*A2，其中的“2”是代表你四舍五入后小数点的位数，这里是两位。
7、Excel中快速绘制文本框
如果按住Alt键不放再绘制文本框可实现文本框与单元格边线的重合，从而减轻调整文本框位置的工作量。
9、轻松搞定单元格数据斜向排
在Excel中打开“工具—自定义—命令—类别—格式”，在“命令”列表中找到“顺时针斜排”和“逆时针斜排”两个命令按钮拖到工具栏的合适位置，然后选中需要斜排的单元格，执行命令即可。
另外，也可以选择“格式/单元格/对齐/方向”，旋转红色的指针或直接输入度数。
10、如何将一个表格垂直拆分为两个的表格
选择需要拆分的列，选择“格式”菜单中的“边框和底纹”命令或在右键菜单中选择“边框和底纹”，单击“边框”标签，在预览区中单击顶边框、中边框和底边框，以将其边框线删除，保留左边框线和右边框线，单击“确定”按钮即可完成。
11、Excel单元格多于15位数字的输入
先选定需输入数字的单元格，然后单击“格式”菜单中的“单元格”命令，找到“数字”项中的“自定义”命令，然后在其右面的“类型”框中选择“@”项，“确定”之后再输入即可。
12、在Excel中复制上一单元格
在Excel的某一单元格中，按住“Ctrl”键的同时，按下“’”（单引号）键，可复制上一单元格的内容。
13、开关网格线
在Excel中，要去掉网格线，在“工具—自定义—命令”类别中找到“窗体”，在右边找到“网格线开关”（Excel中是“切换网格”），把它拖到工具条上，关闭对话框，以后在需要去掉网格线时，单击一下，想恢复，再单击一下。
14、在Excel中实现自动换行
选择菜单栏中的“格式—单元格—对齐”，接着选中“文本控制”标题下的“自动换行”复选框即可（打勾）。
如果单元格中还要将文字分段，按Alt+Enter键来实现硬回车换行操作。
15、Excel玩“转置”
对于Excel工作表中的数据，有时需要将其进行行列转换，即将原来的列变为行，原来的行变为列。
方法：
选中要进行操作的单元格，点“复制”，然后确定需要“转置”操作的目标位置，单击菜单中的“编辑/选择性粘贴”，然后勾选“转置”，点“确定”即可。
20、隐藏累赘的文字
笔者单位的财务分析报告有固定的格式，不过有些内容平时不需要打印出来，每次打印都要手工删除，很是麻烦。
其实利用隐藏文字功能即可轻松解决。
在Word中选择要隐藏的文本，点击“格式/字体”，进入“字体”选项卡，勾选“隐藏文字”复选框，这时刚才选中的文字会消失或者出现点式下划线（选中隐藏的文字部分，按工具栏中的“显示/隐藏编辑标记”按钮即可以这两种显示效果之间切换
21、选中目标单元格->数据->有效性->设置->在“允许”下选择“序列”－＞在“来源”中输入“是,否”->确认
22、请注意：
“是,否”中间的“，”必须是半角状态或英文状态下输入的，如果是全角状态或中文的逗号就会出现你所说的情况，而不会上下列了。
具体操作非常简单，首先确保Excel视图菜单的状态栏被勾选，在选定需要进行运算的单元格后，用鼠标右键单击一下Excel状态栏右侧的NUM区域会弹出一个小菜单（如图1），里面的“求和（S）、最小值（I）、最大值（M）、计数（C）、均值（A）”就分别对应Excel的SUM函数、MIN函数、MAX函数、COUNT函数和AVERAGE函数，要想进行其中一项运算只需用鼠标作相应选择、NUM区域的左边就会显示出运算结果。
23、合并单元格内的内容用锁定符号&，如合并A1B1C1的内容，就用=A1&BI&C1。
24、“$”的功用：
Excel一般使用相对地址来引用单元格的位置，当把一个含有单元格地址的公式拷贝到一个新的位置，公式中的单元格地址会随着改变。
你可以在列号或行号前添加符号“$”来冻结单元格地址，使之在拷贝时保持固定不变。
25、如何用汉字名称代替单元格地址？
如果你不想使用单元格地址，可以将其定义成一个名字。
定义名字的方法有两种：
一种是选定单元格区域后在“名字框”直接输入名字，另一种是选定想要命名的单元格区域，再选择“插入”＼“名字”＼“定义”，在“当前工作簿中名字”对话框内键人名字即可。
使用名字的公式比使用单元格地址引用的公式更易于记忆和阅读，比如公式“=SUM（实发工资）”显然比用单元格地址简单直观，而且不易出错。
39、如何在公式中快速输入不连续的单元格地址？
在SUM函数中输入比较长的单元格区域字符串很麻烦，尤其是当区域为许多不连续单元格区域组成时。
这时可按住Ctrl键，进行不连续区域的选取。
区域选定后选择“插入”＼“名字”＼“定义”，将此区域命名，如Group1，然后在公式中使用这个区域名，如“＝SUM（Group1）”。
26、如何定义局部名字？
在默认情况下，工作薄中的所有名字都是全局的。
其实，可以定义局部名字，使之只对某个工作表有效，方法是将名字命名为“工作表名！
名字”的形式即可。
27、如何命名常数？
有时，为常数指定一个名字可以节省在整个工作簿中修改替换此常数的时间。
例如，在某个工作表中经常需用利率4.9％来计算利息，可以选择“插入”＼“名字”＼“定义”，在“当前工作薄的名字”框内输入“利率”，在“引用位置”框中输入“＝0.04.9”，按“确定”按钮。
28、工作表名称中能含有空格吗？
能。
例如，你可以将某工作表命名为“ZhuMeng”。
有一点结注意的是，当你在其他工作表中调用该工作表中的数据时，不能使用类似“＝ZhUMeng！
A2”的公式，否则Excel将提示错误信息“找不到文件Meng”。
解决的方法是，将调用公式改为“＝'ZhuMg'!
A2”就行了。
当然，输入公式时，你最好养成这样的习惯，即在输入“＝”号以后，用鼠标单由ZhuMeng工作表，再输入余下的内容。
29、给工作表命名应注意的问题？
有时为了直观，往往要给工作表重命名（Excel默认的荼表名是sheet1、sheet2.....），在重命名时应注意最好不要用已存在的函数名来作荼表名，否则在下述情况下将产征收岂义。
我们知道，在工作薄中复制工作表的方法是，按住Ctrl健并沿着标签行拖动选中的工作表到达新的位置，复制成的工作表以“源工作表的名字+
（2）”形式命名。
例如，源表为ZM，则其“克隆”表为ZM
（2）。
在公式中Excel会把ZM
（2）作为函数来处理，从而出错。
因而应给ZM
（2）工作表重起个名字。
31、如何给工作簿扩容？
选取“工具”＼“选项”命令，选择“常规”项，在“新工作薄内的工作表数”对话栏用上下箭头改变打开新工作表数。
一个工作薄最多可以有255张工作表，系统默认值为6。
32、如何减少重复劳动？
我们在实际应用Excel时，经常遇到有些操作重复应用（如定义上下标等）。
为了减少重复劳动，我们可以把一些常用到的操作定义成宏。
其方法是：
选取“工具”菜单中的“宏”命令，执行“记录新宏”，记录好后按“停止”按钮即可。
也可以用VBA编程定义宏。
34、如何快速删除特定的数据？
假如有一份Excel工作薄，其中有大量的产品单价、数量和金额。
如果想将所有数量为0的行删除，首先选定区域（包括标题行），然后选择“数据”＼“筛选”＼“自动筛选”。
在“数量”列下拉列表中选择“0”，那么将列出所有数量为0的行。
此时在所有行都被选中的情况下，选择“编辑”＼“删除行”，然后按“确定”即可删除所有数量为0的行。
最后，取消自动筛选。
35、如何快速删除工作表中的空行？
方法三：
使用上例“如何快速删除特定的数据”的方法，只不过在所有列的下拉列表中都选择“空白”。
1、两列数据查找相同值对应的位置
=MATCH（B1,A:
A，0）
2、已知公式得结果
定义名称=EVALUATE（Sheet1!
C1）
已知结果得公式
定义名称=GET.CELL（6,Sheet1!
C1）
3、强制换行
用Alt+Enter
7、Excel是怎么加密的
（1）、保存时可以的另存为>>右上角的"工具">>常规>>设置
（2）、工具>>选项>>安全性
8、关于COUNTIF
COUNTIF函数只能有一个条件，如大于90，为=COUNTIF（A1:
A10,">=90"）
介于80与90之间需用减，为=COUNTIF（A1:
A10,">80"）-COUNTIF（A1:
A10,">90"）
9、根据身份证号提取出生日期
（1）、IF（LEN（A1）=18,DATE（MID（A1,7,4）,MID（A1,11,2）,MID（A1,13,2））,IF（LEN（A1）=15,DATE（MID（A1,7,2）,MID（A1,9,2）,MID（A1,11,2））,"错误身份证号"））
（2）、=TEXT（MID（A2,7,6+（LEN（A2）=18）*2）,"#-00-00"）*1
10、想在SHEET2中完全引用SHEET1输入的数据
工作组，按住Shift或Ctrl键，同时选定Sheet1、Sheet2。
11、一列中不输入重复数字
[数据]--[有效性]--[自定义]--[公式]
输入=COUNTIF（A:
A,A1）=1
如果要查找重复输入的数字
条件格式》公式》=COUNTIF（A:
A,A5）>1》格式选红色
12、直接打开一个电子表格文件的时候打不开
“文件夹选项”-“文件类型”中找到.XLS文件，并在“高级”中确认是否有参数1%,如果没有，请手工加上
13、excel下拉菜单的实现
[数据]-[有效性]-[序列]
14、10列数据合计成一列
=SUM（OFFSET（$A$1,（ROW（）-2）*10+1,,10,1））
15、查找数据公式两个（基本查找函数为VLOOKUP,MATCH）
（1）、根据符合行列两个条件查找对应结果
=VLOOKUP（H1，A1：
E7，MATCH（I1，A1：
E1，0），FALSE）
（2）、根据符合两列数据查找对应结果（为数组公式）
=INDEX（C1：
C7，MATCH（H1&I1,A1:
A7&B1:
B7,0））
16、如何隐藏单元格中的0
单元格格式自定义0;-0;;@或选项》视图》零值去勾。
呵呵，如果用公式就要看情况了。
17、多个工作表的单元格合并计算
=Sheet1!
D4+Sheet2!
D4+Sheet3!
D4，更好的=SUM（Sheet1:
Sheet3!
D4）
18、获得工作表名称
（1）、定义名称：
Name
=GET.DOCUMENT（88）
（2）、定义名称：
Path
=GET.DOCUMENT
（2）
（3）、在A1中输入=CELL（"filename"）得到路径级文件名
在需要得到文件名的单元格输入
=MID（A1,FIND（"*",SUBSTITUTE（A1,"","*",LEN（A1）-LEN（SUBSTITUTE（A1,"",""））））+1,LEN（A1））
（4）、自定义函数
PublicFunctionname（）
DimfilenameAsString
filename=ActiveWorkbook.name
name=filename
EndFunction
EXCEL快速操作技巧
7、快速输入大量含小数点的数字
方法一：
自动设置小数点
用鼠标依次单击“工具”/“选项”/“编辑”标签，在弹出的对话框中选中“自动设置小数点”复选框，然后在“位数”微调编辑框中键入需要显示在小数点右面的位数就可以了。
以后我们再输入带有小数点的数字时，直接输入数字，而小数点将在回车键后自动进行定位。
例如，要在某单元格中键入0.06，可以在上面的设置中，让“位数”选项为2，然后直接在指定单元格中输入6，回车以后，该单元格的数字自动变为“0.06”。
怎么样，简单吧？
编辑提示：
可以通过自定义“类型”来定义数据小数位数，在数字格式中包含逗号，可使逗号显示为千位分隔符，或将数字缩小一千倍。
如对于数字“1000”，定义为类型“＃ ＃＃＃”时将显示为“1 000”，定义为“＃ ”时显示为“1”。
不难看出，使用以上两种方法虽然可以实现同样的功能，但仍存在一定的区别：
使用方法一更改的设置将对数据表中的所有单元格有效，方法二则只对选中单元格有效，使用方法二可以针对不同单元格的数据类型设置不同的数据格式。
使用时，用户可根据自身需要选择不同的方法.
8、快速录入文本文件中的内容
如果您需要将纯文本数据制作成ExcelXP的工作表，那该怎么办呢？
重新输入一遍，大概只有头脑有毛病的人才会这样做；将菜单上的数据一个个复制/粘贴到工作表中，也需花很多时间。
其实只要在ExcelXP中巧妙使用其中的文本文件导入功能，就可以大大减轻工作量。
依次用鼠标单击菜单“数据/获取外部数据/导入文本文件”，然后在导入文本会话窗口选择要导入的文本文件，再按下“导入”钮以后，程序会弹出一个文本导入向导对话框，您只要按照向导的提示进行操作，就可以把以文本格式的数据转换成工作表的格式了。
10、快速进行中英文输入法切换
一张工作表常常会既包含有数字信息，又包含有文字信息，要录入这样一种工作表就需要我们不断地在中英文之间反复切换输入法，非常麻烦，为了方便操作，我们可以用以下方法实现自动切换：
首先用鼠标选中需要输入中文的单元格区域，然后在输入法菜单中选择一个合适的中文输入法；接着打开“有效性”对话框，选中“输入法模式”标签，在“模式”框中选择打开，单击“确定”按钮；然后再选中输入数字的单元格区域，在“有效数据”对话框中，单击“输入法模式”选项卡，在“模式”框中选择关闭（英文模式）；最后单击“确定”按钮，这样用鼠标分别在刚才设定的两列中选中单元格，五笔和英文输入方式就可以相互切换了
12、快速对不同单元格中字号进行调整
其实，您可以采用下面的方法来减轻字号调整的工作量：
首先新建或打开一个工作簿，并选中需要ExcelXP根据单元格的宽度调整字号的单元格区域；其次单击用鼠标依次单击菜单栏中的“格式”/“单元格”/“对齐”标签，在“文本控制”下选中“缩小字体填充”复选框，并单击“确定”按钮；此后，当你在这些单元格中输入数据时，如果输入的数据长度超过了单元格的宽度，ExcelXP能够自动缩小字符的大小把数据调整到与列宽一致，以使数据全部显示在单元格中。
如果你对这些单元格的列宽进行了更改，则字符可自动增大或缩小字号，以适应新的单元格列宽，但是对这些单元格原设置的字体字号大小则保持不变。
13、快速输入多个重复数据
我们经常要输入大量重复的数据,如果依次输入，工作量无疑是巨大的。
现在我们可以借助ExcelXP的“宏”功能，来记录首次输入需要重复输入的数据的命令和过程，然后将这些命令和过程赋值到一个组合键或工具栏的按钮上，当按下组合键时，计算机就会重复所记录的操作。
打开工作表，在工作表中选中要进行操作的单元格；接着再用鼠标单击菜单栏中的“工具”菜单项，并从弹出的下拉菜单中选择“宏”子菜单项，并从随后弹出的下级菜单中选择“录制新宏”命令；设定好宏后，我们就可以对指定的单元格，进行各种操作，程序将自动对所进行的各方面操作记录复制。
14、快速处理多个工作表
无论打开多少工作表，在某一时刻我们只能对一个工作表进行编辑，那么能够同时处理多个工作表么？
您可采用以下方法：
首先按住“Shift"键或“Ctrl"键并配以鼠标操作，在工作簿底部选择多个彼此相邻或不相邻的工作表标签，然后就可以对其实行多方面的批量处理了。
在选中的工作表标签上按右键弹出快捷菜单，进行插入和删除多个工作表的操作；然后在“文件”菜单中选择“页面设置……”，将选中的多个工作表设成相同的页面模式；再通过“编辑”菜单中的有关选项，在多个工作表范围内进行查找、替换、定位操作；通过“格式”菜单中的有关选项，将选中的多个工作表的行、列、单元格设成相同的样式以及进行一次性全部隐藏操作；接着在“工具”菜单中选择“选项……”，在弹出的菜单中选择“视窗”和“编辑”按钮，将选中的工作表设成相同的视窗样式和单元格编辑属性；最后选中上述工作表集合中任何一个工作表，并在其上完成我们所需要的表格，则其它工作表在相同的位置也同时生成了格式完全相同的表格
15.快速插入空行
如果想在工作表中插入连续的空行，用鼠标向下拖动选中要在其上插入的行数，单击鼠标右键，从快捷菜单中选择“插入”命令，就可在这行的上面插入相应行数的空行。
如果想要在某些行的上面分别插入一个空行，可以按住Ctrl键，依次选中要在其上插入空行的行标将这些行整行选中，然后单击鼠标右键，从快捷菜单中选择“插入”命令即可。
16.快速互换两列中的数据
在Excel中有一个很简单的方法可以快速互换两列数据的内容。
选中A列中的数据，将鼠标移到A列的右边缘上，光标会变为十字箭头形。
按下Shift键的同时按住鼠标左键，向右拖动鼠标，在拖动过程中，会出现一条虚线，当拖到B列右边缘时，屏幕上会出现“C:
C”的提示。
这时松开Shift键及鼠标左键，就完成A，B两列数据的交换。
17.快速在单元格中输入分数
如果要想在Excel中输入2/3时，仅在单元格中输入“2/3”，按回车键后，单元格中的内容会变为“2月3日”，那么如何在单元格中输入分数呢？
有两种方法可供选择：
一种是在单元格中先输入一个0，接着输入一个空格，然后输入“2/3”,再按回车键就可以了。
另一种方法是，在输入分数前先输入一个英文半角的单引号，然后再输入“2/3”,这样按回车键后，也可以正确输入分数2/3。
18.快速输入日期
看了上面的第3条技巧，在输入日期时，如果要输入“2月3日”，也不用一个字一个字地输入了，只要在单元格中输入“2/3”，按一下回车键就可以了。
变为“2月3日”后，双击这个单元格，还会显示为“2006－2－3”的格式，但鼠标离开后，又恢复为“2月3日”的样式。
如果要输入当前日期，按“Ctrl+;”组合键就可快速输入。
输入技巧
1、数字输入
对于分数，在输入可能和日期混淆的数值时，应在分数前加数字“0”和空格。
例如，在单元格中输入“2/3”，Excel将认为你输入的是一个日期，在确认输入时将单元格的内容自动修改为“2月3日”。
如果希望输入的是一个分数，就必须在单元格中输入“02/3”，请注意0后面的空格。
2、文本输入
如果需要在某个单元格中显示多行文本，可选中该单元格，鼠标右键选择“设置单元格格式”项，进入“单元格格式”设置界面，单击“对齐”选项卡，勾选“自动换行”复选框即可。
提示：
如果需要在单元格中输入硬回车，按“Alt＋回车键”即可。
3、输入日期和时间
Excel把日期和时间当作数字处理，工作表中的时间或日期的显示方式取决于所在单元格中的数字格式。
在键入了Excel可以识别的日期或时间数据后，单元格格式会从“常规”数字格式改为某种内置的日期或时间格式。
如果要在同一单元格中同时键入日期和时间，需要在日期和时间之间用空格分隔；
如果要基于十二小时制键入时间，需要在时间后键入一个空格，然后键入AM或PM（也可只输入A或P），用来表示上午或下午。
否则，Excel将基于二十四小时制计算时间。
例如，如果键入3：
00而不是3：
00PM，则被视为3：
00AM保存；
如果要输入当天的日期，请按Ctrl＋；（分号）；如果输入当前的时间，请按Ctrl＋Shift＋：
（冒号）。
时间和时期可以相加、相减，并可以包含到其他运算中。
如果要在公式中使用日期或时间，请用带引号的文本形式输入日期或时间值。
例如，公式＝"2005/4/30"-"2002/3/20"”，将得到数值1137。
4、输入网址和电子邮件地址
对于在单元格中输入的网址或电子邮件地址，Excel在默认情况下会将其自动设为超级链接。
如果想取消网址或电子邮件地址的超级链接，可以在单元格上单击鼠标右键，选择“超级链接/取消超级链接”即可。
此外，还有两个有效办法可以有效避免输入内容成为超级链接形式：
1.在单元格内的录入内容前加入一个空格；
2.单元格内容录入完毕后按下“Ctrl＋z”组合键，撤消一次即可。
5.快速输入固定有规律的数据
有时我需要大量输入形如“3405002005XXXX”的号码，前面的一长串数字（“3405002005”）都是固定的，对于这种问题，用“自定义”单元格格式的方法可以加快输入的速度：
选中需要输入这种号码的单元格区域，执行“格式→单元格”命令，打开“单元格格式”对话框（如图），在“数字”标签中，选中“分类”下面的“自定义”选项，然后在右侧“类型”下面的方框中输入:
"3405002005"0000，确定返回。
以后只要在单元格中输入“1、156……”等，单元格中将显示出“34050020050001、34050020050156”字符。
注意:
有时，我们在输入6位的邮政编码时，为了让前面的“0”显示出来，只要“自定义”"000000"格式就可以了。
单元格
6.快速输入平方和立方符号
在按住Alt键的同时，按下小键盘上的数字“178”、“179”即可输入“上标2”和“上标3”。
注意：
在按住Alt键的同时，试着按小键盘上的一些数字组合（通常为三位），可以得到一些意想不到的字符（例如Alt+137—‰、Alt+177—±等）。
8.自定义特殊序号
如果想让一些特殊的序号也能像上面一样进行自动填充的话，那可以把这些特殊序号加入到自定义序列中。
点击菜单“工具” “选项”，在弹出的对话框中点击“自定义序列”标签，接着在右面输入自定义的序号，如“A、B、C……”，完成后点击“添加”按钮，再点击“确定”按钮就可以了（如图2）。
设置好自定义的序号后，我们就可以使用上面的方法先输入头二个序号，然后再选中输入序号的单元格，拖拽到序号的最后一个单元格就可以自动填充了。
9.自动输入序号
word中有个自动输入序号的功能，其实在Excel中也有这个功能，可以使用函数来实现。
点击A2单元格输入公式：
=IF（B2="","",COUNTA（$B$2:
B2）），然后把鼠标移到A2单元格的右下方，鼠标就会变成十字形状，按住拖拽填充到A列下面的单元格中，这样我们在B列输入内容时，A列中就会自动输入序号了（如图3）。
11.自动调整序号
有时候我们需要把部分行隐藏起来进行打印，结果却会发现序号不连续了，这时就需要让序号自动调整。
在A2单元格输入公式：
=SUBTOTAL（103,$B$2:
B2），然后用上面介绍的方法拖拽到A列下面的单元格，这样就会自动调整序号了。
输入分数六种方法
Excel在数学统计功能方面确实很强大，但在一些细节上也有不尽如人意的地方，例如想输入一个分数，其中可有一些学问啦。
笔者现在总结了六种常用的方法，与大家分享。
整数位+空格+分数
例：
要输入二分之一，可以输入：
0（空格）1/2；如果要输入一又三分之一，可以输入：
1（空格）1/3。
方法优缺点：
此方法输入分数方便，可以计算，但不够美观（因为我们常用竖式表示分数，这样输入不太符合我们的阅读习惯）。
使用ANSI码输入
例：
要输入二分之一，可以先按住“Alt”键，然后输入“189”，再放开“Alt”键即可（“189”要用小键盘输入，在大键盘输入无效）。
方法优缺点：
输入不够方便，想要知道各数值的ANSI码代表的是什么可不容易，而且输入的数值不可以计算。
但此方法
升级会员 