EXCEL技巧.docx
《EXCEL技巧.docx》由会员分享，可在线阅读，更多相关《EXCEL技巧.docx（20页珍藏版）》请在冰豆网上搜索。
EXCEL技巧
一、数据录入技巧
☆减少无用击键次数
例：
复制：
ctrl+c粘贴：
ctrl+v配合鼠标。
快速录入数据：
横向走用tab键，换行用回车键
竖向走先选择数据录入区域后按回车键即可
☆数字小键盘的盲打
掌握指法，化点时间下载有关小键盘学习指法软件多练习提高录入速度
☆批量录入相同的数据
先框写需录入相同数字区域，在公式栏中输入数据后按ctrl+回车键
☆快捷键（code（）函数可查询代号）
√：
Alt+41420；×：
Alt+41409；笔记本必须把数字键打开。
平方号：
Alt+178；立方号：
Alt+179；
☆分数录入法
分数输入法：
先输入一整数，空格，分数。
身份证号码输入或银行帐号输入：
先输入’后才输入数字
☆日期时间录入法
Ctrl+;显示当前日期；ctrl+shift+;显示当前时间；
☆输入特殊字符
多数常用特殊符号在：
插入特殊符号“对话框中选择特殊字符；如果找不到所需字符，可在“插入”“符号”对话框中作更多的选择。
通过选择“字体“、”来自“和进制不同的”子集“，几乎可以找到任何你所见过的在电脑上使用的字符。
如果选择“Windings”系列字体（共3个），可以选择许多有趣的图形。
二、编辑工作表技巧
☆选取单元格区域的键盘技巧
按住键，使用方向键或、、、键来扩展区域。
例：
要选择A1:
H50区域
方法一：
先定位A1，然后握住键，再按右箭头键直到A1:
H1都被选择，接着按下箭头到达50行即可。
要加快速度，如选择A1:
H500，可用键代替。
方法二：
先定位H1，然后握住键，现按，这样A1:
H1都被选择，接着按下箭头到达50行即可。
对于特别大的区域，则使用鼠标和键盘配合操作。
先选中待先区域左上角单元格，然后拖动工作表滚动条到待先区域的右下角，按住键，再单击最右下角的单元格。
☆选择当前数据区域：
先选中矩形区域任一单元格，然后按组合键。
（*应为数字小键盘）
☆选定非连续区域：
方法一：
按住同时，用鼠标去选择不同区域。
方法二：
先选中一个区域，再按键，再去选择另一个区域。
（取消应按“esc”键后单击任意键。
）
☆反向选择行、列：
先整行（列）选择指定行（列）以外的下一行（列），然后按，就能选择从指定行（列）开始到所有的行（列）。
☆选取多个工作表的相同区域：
先在其中一张工作表中选择好数据区域，然后按住键，单击其他工作表。
此时所有被选中的工作表标签会反白显示，标题栏也会显示“[工作组]”。
☆快速选择“特殊”区域
利用excel定位功能选择具有某种特性的单元格：
单击“编辑”“定位”或使用,在引用位置中输入定位区域后，单击“定位条件”并选择其中的一项，excel就会在目标区域内选定所有符合该条件的单元格。
所谓目标区域，如在使用定位以前只选定了一个单元格，那么定位的区域就是工作表的整个活动区域；如选定了单元格区域，那么目标区域就是选定的单元格。
如果定位功能找不到符合条件的单元格，excel会弹出一个提示“未找到单元格”。
例：
定位目标区域中带公式的单元格。
☆快速填充所有空白单元格
如用0或其它数字去填充所有空白格时，利用定位功能快速找到区域中的空白格，然后输入0或在输入的数据，按组合键即可。
☆快速切换工作表
方法一：
光标停在水平滚动条左边垂直小条上往右拖动，让窗口显示更多的工作表标签。
方法二：
如工作表太多，还可以右击工作表导航栏，这里会显示一个工作表标签列表，单击其中任何一项就能到达相应的工作表。
（显示前15张工作表）。
如果显示更多，可单击“其他工作表”选项。
双击其中一项即可激活相应的工作表。
☆省心省力的重复操作
如果希望取消刚才的操作，可用组合或F4键来恢复。
但恢复操作必须紧跟在撤消操作之后，否则就无效了。
当没有撤消操作可恢复时，组合键就变成了“重复”命令。
重复就是把刚才所做的操作再执行一次，在很多时候可以把复杂的操作简化成只需要一步就可以再做一次。
如：
在当前工作表薄中添加一张工作表，方法是单击“插入”“工作表”。
如果要插入5张工作表时，只需再连续按4次组合键
如在一个单元格中设置了比较复杂的格式，也可以用组合键把同样的操作应用到其他单元格中去。
此时的重复操作和用格式刷效果不一定相同，因为格式刷是完全复制格式，而重复操作只是复制刚才所做的格式设置。
与撤消不同，重复永远只有一级，也就中说用户只能重复上一步的操作，而不能重复更之前的操作。
☆改变工作表颜色
用户可以为工夫和表标签设置不同的颜色，以便区别显示。
方法是右键单击标签，在快捷菜单中选择“工作表标签颜色”命令，在“设置工作表标签颜色”对话框中任意选择一种预设的颜色作为标签的背景色。
如果一次性为多个工作表标签改变颜色，可以按住键再单击所有目标工作表，然后右键单击标签，余下的操作相同。
☆快速插入多个单元格
键快捷的办法是：
先选定目标单元格或单元区域，然后按住键，把光标移动到选区的右下角，当光标变成分隔箭头时，往右或往下拖动鼠标，拖动的距离就等于插入的单元格数量，拖动的方向等于：
活动单元格：
移动的方向。
☆快速插入多个行和列
快捷方法：
1、单击待插入行的下一行号，然后向下拖动鼠标，拖动的行数等于希望插入的行数。
2、单击菜单“插入”“行”，或者用快捷键即可。
3、插入多列也是同样的原理。
☆快速改变行列的次序
快捷方法：
如图所示
1、单击D列标；
2、把光标移动到C列右侧的黑色边框上，按住键，开始往左拖动，这时可以看到光标左侧出现一条T字形虚线；
3、把这条虚线拖动到A列和B列之间的单元格边框上，松开鼠标左键即可。
☆快速删除所有空行
如果数据区域中含有大量空行，如何快速找到并删除方法：
方法一：
利用自动筛选功能，在每一列筛选条件下筛选出“空白”项，删除空白项后取消自动筛选功能。
方法二：
使用辅助列配合自动筛选功能来查找。
例：
图所示
1、在J2单元格中输入公式：
=COUNTA（A2:
J2）,把这个公式复制到J3:
J200.
2、选定数据区域中任一单元格，然后进行自动筛选功能。
3、在J列的筛选条件下，选择0。
4、选定所有蓝色的行并删除。
5、取消自动筛选功能。
☆隔行插入的绝招
方法：
如图所示
1、在A列插入一空列；
2、在A1中输入数字1，然后用序列方式填充到区域的最后一行A14。
3、在A15中输入数字1.5，然后用序列方式填充到区域的最后一行A27。
4、选定A1:
A27，单击菜单“排序”，在排序对话框中选择A列为主关键字进行升序排列，单击“确定”按钮。
5、删除A列。
☆格式刷的用途
格式刷主要用来复制单元格的格式（或者叫属性），具体包括你点"格式"->"单元格"后出来的所有内容。
另外，格式刷可以复制设定的条件格式如字体、颜色等，但不能复制一般的公式，复制公式要用填充柄。
鼠标单击格式刷按钮后只能复制一次；双击格式刷按钮后可以重复多次复制，直到你再单击格式刷按钮为止。
☆神奇的选择性粘贴
•
选择性粘贴选项包括如下：
公式：
只粘帖文本和公式，不粘帖字体，格式（字体、对齐、文字方向、数字格式、底纹等），边框，注释，内容校验等。
值：
只粘帖文本，单元格的内容是计算公式的话只粘帖计算结果，这两项不改变目标单元格的格式。
格式：
粘帖格式，包括字体，对齐，文字方向，边框，底纹等，不改变目标单元格的文字内容。
功能相当于格式刷。
注释：
把源单元格的注释内容拷贝过来，不改变目标单元格的内容和格式。
校验：
只粘帖校验内容，其他的保持不变。
添加校验的作用是阻止用户输入不符合要求的内容。
粘帖除边框外的所有内容和格式：
保持目标单元格和源单元格相同的内容和格式
列宽度：
使目标单元格和源单元格拥有同样的列宽，不改变内容和格式。
公式和数字格式：
类似于格式，但要保留数字格式（如保留几位少数点、是否时间、要不要用百分比等）。
值和数字格式：
类似于Values，但要保留数字格式（如保留几位少数点、是否时间、要不要用百分比等）。
选项：
实现目标单元格和源单元格的计算。
当粘贴选项选中的是格式,注释或校验时，这些选项不可用。
其他选项：
跳过空白单元格：
源单元格为空的区域，不覆盖对应的目标单元格的内容。
转置：
实现行列互换。
粘帖链接：
让目标单元格等于源单元格（插入的是“=源单元格”这样的公式，不是值）。
如果更新源单元格的值，目标单元格的内容也会同时更新。
（例：
同时进行多个单元格的运算—将表中数字同时+300方法：
先选定一单元格输入300，然后点复制键，选择要加300的所有单元格后，点选择性粘贴中的加运算功能）
☆如何把Word中的某一段内容以图片形式插入到Excel表格中
如果要把Word中的某一段内容以图片形式插入到Excel表格中，可按照如下步骤进行:
首先在Word中选中要复制的内容，然后单击“编辑→复制”命令，进入Excel中，按住Shift键，单击“编辑→粘贴图片”命令，即可将刚才复制的内容以图片格式插入到Excel表格中，而且在该图片上双击，还可进行文字修改。
利用这种方法也可以把“记事本”等其他程序中的内容以图片格式粘贴到Excel中。
☆隐藏秘密数据
想要只隐藏部分单元格，或者在显示所有行号列标的情况下仍能隐藏数据，可用以下办法实现：
方法1：
使用特殊的自定义数字格式。
在“单元格格式”对话框选择“数字”选项卡中选择“分类“自定义”，在“类型”框中输入“;;;”三个半角的分号，然后单击“确定”。
方法2：
把所有需要隐藏内容的单元格的字体颜色设置为与其背景颜色完全相同。
方法3：
利用绘图功能创建一个自选图形覆盖在需要隐藏的单元格上。
此法简单，但难看。
以上方法当用户在选定单元格时，编辑栏中仍能显示数据内容，所以还需要补充一些操作，让它们在编辑栏中消失。
选定“隐藏”的单元格，单击：
“格式”“单元格”，在“单元格格式”对话框的“保护”选项卡中，勾选“隐藏”复选框，然后单击“确定”。
最后单击菜单“工具”“保护”“保护工作表”，在“保护工作表”对话框中单击“确定”。
这样，编辑栏中、单元格中的内容被彻底隐藏了。
三、美化工作表技巧
☆快速设置适合的列宽
1、标准方法：
先选中一列或多列，再单击“格式”“列”“最适合的列宽”。
2、更快捷的方法：
先选中一列或多列。
把光标移动到列标题之间的位置上，当它变成十字架形状的时候双击。
3、这个方法同样也适合设置最适合的行高。
☆制作带斜线的表头
1、单线表头：
“在单元格格式”对话框的“边框”选项卡中单击斜线按钮。
之后在填充表头内容方法有三种。
1）使用文本框：
选择“插入”“文本框”“横排”（视情况也可选择竖排）；在单元格中连续插入两个文本框，并编辑文本框中的相关文字，调整框相适应的大小和文字字号。
选定文本框，单击“格式”“文本框”，在“设置文本框格式”对话框的“颜色与线条”选项卡中，分别将“填充颜色”与“线条颜色”设置为“无”。
然后用鼠标把两个文本框分别移动到画有斜线的单元格的合适位置。
2）使用上下标：
在斜线的不同部位设置为上标或下标格式。
根据需要，还可在不同部分之间插入适量空格进行美化。
3）使用空格进行中分（适用于文字少时）。
2、多斜线表头：
借助于自选图形来做。
利用“绘图”按钮，绘制两条或多条直线到单元格中。
双击直线，在弹出的“设置自选图形格式”对话框中选择“属性”选项卡，并选中“大小、位置随单元格而变”和“打印对象”复选框；使用插入文本框的方式逐个添加表头项目。
☆在一个单元格内输入多行内容
方法：
强制换行+对齐方式：
ALT+
四、工作表的显示方式
•☆使用视图管理器：
使用此功能可创建工作表的不同视图，并为视图取名，通过不同的名称，可在这些命名的视图中进行切换，不必将不同的视图存为多个文件即可显示、打印和储存不同的视图。
如要删除某种视图，可选此视图后单击“视图管理器”中的“删除”按钮即可。
☆拆分和冻结窗口
•
1、拆分窗格：
1）垂直拆分:
将鼠标指向垂直拆分框，指针变为双向箭头形状时，按住左键不放并向下拖动，至适应位置处松开左键即可。
2）水平拆分：
用同样的方法拖动水平拆分框。
3）使用拆分命令拆分窗格:
首先选择待拆分的单元格B6（行或列），选择“窗口”“拆分”命令，系统会自动在选定的单元格上方和左方拆分窗口，移动鼠标在拆分处，当鼠标变为拆分指针时，按住左键不放拖动鼠标，可改变上下或左右拆分窗口的大小。
4）取消拆分：
鼠标双击拆分条；或使用“窗口”菜单中的“取消拆分”命令。
例：
查看同一表格中相距较远的两列数据
•
2、冻结窗口：
选等分处右下方的单元格；选择“窗口”“冻结窗口”命令。
若要取消“冻结窗口”，选择“撤消冻结窗口”命令。
☆保护工作薄
1、作用：
保护工作薄结构不被删除、移动或重命名工作表，也不能插入新的工作表。
还可以保护工作薄的窗口不被移动、缩放、隐藏或关闭等。
2、方法：
选择“工具““保护”“保护工作薄”命令，在对话框中选中“结构”和“窗口”两个复选框，然后“确定”。
输入密码和确认密码后“确定”。
3、撤消工作薄保护：
选择“撤消工作薄保护”命令，输入密码，单击“确定”。
4、工作薄保护后，再次打开“编辑”菜单时，其中的“删除工作表”及“移动或复制工作表”命令不能执行，并且工作表窗口的“还原”及“窗口最小化”按钮都将消失。
☆保护工作表
1、作用：
不希望别人修改工作表中的内容或防止他人删除、复制、移动和编辑等改变工作表性质时使用。
2、方法：
选择“工具““保护”“保护工作表”命令，选定“保护工作表及锁定的单元格内容“框，然后在“允许此工作表的所有用户进行”下拉列表框中选择所要设定的选项，如选择“选择锁定单元格”及选定未锁定的单元格“两个复选框，输入密码和确认密码后“确定”。
☆设置密码的技巧
1、密码的长度：
至少6位。
2、不要只使用某一类字符（特别是数字）作为密码，excel的密码支持字母（区分大小写）、数字、符号（区分全半角），建议混合作用。
当密码时多一类字符、多一位长度，破解就会以几何级数增长。
3、千万不要以英文单词、生日、电话号码之类的信息当密码。
4、excel提供的保护功能仅对普通用户起作用，如果用户需要确保数据安全，excel的保护功能不是最佳选择。
☆工作簿的自动保存：
用户可在“工具”的选项命令的保存选项卡中设定自动保存的时间间隔和自动恢复文件的保存位置，以免停电时文件丢失。
☆如何禁止复制隐藏行或列中的数据
如果你复制了包含隐藏列（或行）的一个数据区域，然后把它粘贴到一个新的工作表，那么Excel把隐藏列也粘贴过来了。
要想避免这种情况，可以选取你要复制的数据区域，然后选择“编辑→定位”命令，单击“定位条件”按钮，出现“定位条件”对话框，选中“可见单元格”选项，再复制和粘贴这个选定区域就会得到你所希望的结果。
五、工作表的打印
☆打印整个工作薄
任意激活一张工作表，在“文件”“打印”，在“打印内容”区域中选择“整个工作薄”即可。
☆打印部分工作表
希望打印工作薄的某几张工作表时，可以在按住键的同时，逐个单击待打印的工作表，最后单击工具栏上的“打印”快捷键。
或在“打印内容”区域中选择“选定工作表”即可。
（页码连续）
☆打印工作表的特定区域
方法一：
如果需打印一张工作表中的部分区域，先设定待打印的单元格区域，然后在“打印内容”区域中选择“选定区域”即可
方法二：
先设定待打印的单元格区域，然后单击“文件”“打印区域”“设置打印区域”
取消设置打印区域的方法是：
文件”“打印区域”“取消打印区域”，工作表又恢复到全部内容可打印的状态。
如果打印区域设置为工作表中的几个不连续的单元格区域，则每个区域都会被打印到不同的页次上。
•
☆打印特定页面：
对于页次较多的工作表打印内容，可以选择性地只打印其中的某几页，方法是“文件”“打印”，在“打印范围”区中输入起始页码和结束页码。
如需确定要打印哪几页，可在“文件”“打印预览”，预览待打印的内容。
☆重复打印顶端标题行和左侧标题列
单击“页面设置”菜单，在工作表选项卡中选定标题
☆在同一页上打印不连续的区域
在“工具”“自定义”“命令”“工具”，在命令项里找到照相机功能
办法就是利用excel的照相机功能，把需要打印的每个区域都“拍摄”下来，然后以某种格式排列到一张页面上。
☆控制打印的输出比例
在实际工作中，出于美观或节约纸张的缘故，用户常常要调整打印输出的比例。
方法一：
单击“页面设置”菜单，在页面选项卡中调整“缩放比例”和百分比，或者直接单击“调整为”单选按钮，然后输入指定的页数和页高数。
方法二：
使用分页预览视图，在分页预览视图中，可以使用工具栏里的“显示比例”控件来把显示比例调整得更小，从而能更方便地进行后面的分页设置。
还能利用鼠标单击并拖动分页符改变分页。
☆克隆工作表的页面设置：
为多张工作表快速设置相同或相近的页面设置。
方法一：
所有工作表尚未进行过页面设置：
按住键逐个单击要进行页面设置的工作表标签，单击“页面设置”，然后进行相同部分的设置，忽略掉可能不相同的部分设置。
（如页眉页脚）后“确定”。
方法二：
已经有一张工作表进行过页面设置：
先选定进行过页面设置的工作表，按住键逐个单击还要进行页面设置的工作表标签，单击“页面设置”，不要做任何改动，直接“确定”关闭“页面设置”对话框
注意：
无论是同时进行多个工作表页面设置地。
还是把源工作表的页面设置克隆到其他工作表上，“页面设置”对话框的“工作表”选项卡中的“打印区域”和“打印标题”都是不可用的。
☆在报表的每一页上打印公司的LOGO
利用“页面设置”的“页面/页脚”选项卡中，单击“自定义页脚”按钮，在“右”框中“插入图片”还可在“图片设置”对话框中对图片进一步调整。
单击“确定”。
六、其它
☆如何根据条件选择单元格（查找和替换功能｜）
1、简单的查找和替换功能：
2、模糊查找数据：
搜索目标
模糊搜索方法
以A字开头的货物名称
A*
以B结尾的货物编码
*B
包含66的电话号码
*66*
王姓双名的人名
王?
?
任意单姓、单名强的人名
?
强
如果要查找的内容中包含有?
和*，那么就需要在其前面加上~。
如下表所示：
搜索目标
模糊搜索方法
?
ABC
~?
ABC
**
~**
3、高级查找和替换（例：
查找替换单元格背景色格式—将灰色替换成蓝色背景）
☆将小数点对齐
随便在excel上输入点数据，然后选中数据所在单元格点“格式”--“单元格”--数值--自定义然后在类型下面输入?
?
?
.?
?
，就完成了小数点对齐。
☆图表和超级链接
在excel2003中，表示超链接的文本用下划线显示。
以下是创建超链接的方法：
选择希望用于创建超链接的单元格或单元格区域，右单击并选择“超链接”（或单击常用工具栏上的“插入超链接”图标），弹出“插入超链接”窗口，在“要显示的文字”的输入框中将显示所选择的文本，用户可以自行修改，如想设置鼠标放在超链接上所显示的文字，请点击右上角的“屏幕提示”按钮，并输入相关文本。
如图所示，接着按以下不同的类型进行操作：
1、创建指向文件或网页的超链接
在“插入超链接”窗口中点击左侧“链接到：
”之下单击“原有文件或网页”，导航至所需的页或文件。
2、创建指向当前工作簿中某个位置的超链接
在“插入超链接”窗口中点击左侧“链接到：
”之下单击“本文档中的位置”，在右侧的“请键入单元格引用”的输入框中输入引用的单元格，或者在“或在这篇文档中选择位置”的列表框中选择一个位置。
如图所示
3、创建指向新文件的超链接
在“插入超链接”窗口中点击左侧“链接到：
”之下单击“新建文档”。
键入新文件的名称。
若要更改新文档的路径，请单击“更改”按钮。
选择“以后再编辑新文档”或“开始编辑新文档”。
4、创建电子邮件的超链接
在“插入超链接”窗口中点击左侧“链接到：
”之下单击“电子邮件地址”，然后在右侧的“电子邮件地址”框中键入所需的电子邮件地址，或者在“最近用过的电子邮件地址”框中选取所需的电子邮件地址。
在主题框中，键入电子邮件消息的主题。
●删除超链接：
方法一：
选择带有超链接的单元格，右单击并选择“删除超链接”，将为所选单元格去掉超链接。
方法二：
选定空白单元格，按键进行复制，按住键单击所要取消超链接的单元格。
单击<选择性粘贴>选择“加”后“确定”。
方法三：
直接用键进行复制后粘贴。
●更改超链接：
选择带有超链接的单元格，右单击并选择“更改超链接”，将弹出“编辑超链接”窗口，此窗口上面所说的“插入超链接”窗口完全一样，操作也一样。
●修改<工具><自动更正>功能可取消键入邮箱地址后自动超链功能
七、常用函数
函数就其功能分有九大类。
它们是：
1、逻辑函数；2、日期与时间函数；3、数学与三角函数；4、统计函数；5、工程函数；6、信息函数；7、数据库函数；8、财务函数；9、文本函数。
常用的就以下几个最常用的函数：
逻辑函数：
IF、AND、OR；日期与时间函数：
DATE、YEAR、MONTH、DAY、NOW、WEEKDAY；数学与三角函数：
SUM、FLOOR、INT、MOD、ROUND；统计函数：
MIN函数、MAX函数、COUNT函数和AVERAGE函数、COUNT、COUNTA、COUNTIF；文本函数：
CHAR、LEFT、RIGHT、MID。
几乎可以应付全部的日常应用。
★逻辑函数
1、AND函数
函数名称：
AND
主要功能：
返回逻辑值：
如果所有参数值均为逻辑“真（TRUE）”，则返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。
使用格式：
AND（logical1,logical2,...）
参数说明：
Logical1,Logical2,Logical3……：
表示待测试的条件值或表达式，最多这30个。
应用举例：
在C5单元格输入公式：
=AND（A5>=60,B5>=60），确认。
如果C5中返回TRUE，说明A5和B5中的数值均大于等于60，如果返回FALSE，说明A5和B5中的数值至少有一个小于60。
特别提醒：
如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值“#VALUE!
”或“#NAME”。
AND函数
2、OR函数
函数名称：
OR
主要功能：
返回逻辑值，仅当所有参数值均为逻辑“假（FALSE）”时返回函数结果逻辑“假（FALSE）”，否则都返回逻辑“真（TRUE）”。
使用格式：
OR（logical1,logical2,...）
参数说明：
Logical1,Logical2,Logical3……：
表示待测试的条件值或表达式，最多这30个。
应用举例：
在C62单元格输入公式：
=OR（A62>=60,B62>=60），确认。
如果C62中返回TRUE，说明A62和B62中的数值至少有一个大于或等于60，如果返回FALSE，说明A6
升级会员 