Excel操作技术与技巧集锦

Excel操作技术与技巧集锦

Excel操作技术与技巧
下面为自已在前阶段学习Excel的过程中所归纳的一些操作技术与技巧，有些可能是最基本的技术，有些可能是对您的操作很实用的技巧。有时，一个小小的技巧可能会大大加快您的工作效率。
(1)输入数据时控制单元格移动方向。选择菜单中的“工具——选项”命令，单击“编辑”选项卡，在标有“按Enter键后移动”复选框中的方向下拉列表中选择单元格输入完成后按Enter键的移动方向(向下、向上、向下、向左)。若取消复选框的选择，则在单元格中输入完成后，按Enter键不会移动。这样，也可以关闭这个选项，在工作表中按方向键在单元格中进行移动。
另外，按PageUp和PageDown键将向上和向下移动一页。
(2)在单元格区域中输入数据。若要在选择的单元格区域中输入数据，按Enter键至想要输入数据的单元格；若区域包括几行，则Excel将根据“按Enter键的移动方向”中的设置在区域中移动，当到达区域一列(或行)的最后一个单元格时，会自动转到下一列(或行)的第一个单元格；按Shift+Enter键向回移动。要按行向右移动，按Tab键；向左移动，按Shift+Tab键。
(3)在多个单元格中同时输入相同的数据。选择所有需要输入数据的单元格，输入数据(包括数值、文本或公式)，然后按Ctrl+Enter即可。
如果想把单元格区域中活动单元格的内容复制到所选单元格区域的所有其他单元格中，先按F2键，然后按Ctrl+Enter键。
(4)自动输入小数点。若需要输入大量的数字，而这些数字有固定的小数位数，可选择菜单“工具——选项”命令，单击“编辑”选项卡。选中“自动设置小数点”复选框，并设置所需的小数位数，这时，再在Excel中输入数字时就会自动设置小数点，如输入123，则为12.3。若想使输入变为正常状态，取消选择“自动设置小数点”复选框。
注：改变此项设置不会影响到已经输入的数值，但会影响以后所有工作簿上新输入的数值。
(5)输入当前日期或时间。若要输入当前日期，按Ctrl+；键；若要输入当前时间，按Ctrl+Shift+；键。
注：用此方法输入的日期和时间在工作表重新计算时不会改变。
(6)输入带分数的数字。只需在整数和分数之间留一空格。如输入6 1/8，若输入小于1的分数，则应先输入0，即0 1/8。
(7)使用自动更正功能快速输入数据。对于比较长的单词、词组等，可以运用自动更正功能建立自定义的自动更正条目，从而创建一些快捷方式。选择“工具——自动更正选项…”命令，单击“自动更正”选项卡，选中“键入时自动替换”复选框，输入所需要的条目。如果在“替换”栏内输入cor，在“替换为”栏内输入Corporation，那么无论何时输入cor时，Excel都会自动将其改为Corporation。
(8)使用“自动填充”功能输入一系列数值。使用Excel的自动填充手柄(输入一个数字后，按右下角黑十字向下拖动，点自动填充选项手柄选择相应选项进行填充)。
如果要用某个值以增量形式填充单元格区域，拖动单元格右下角填充手柄的同时按Ctrl键。
如果复制单元格的时候不需要增值，则拖放选定单元格右下角的填充手柄即可。
创建自定义的“自动填充”序列，使用“选项”对话框“自定义序列”选项卡定义“新序列”。
(9)使用记忆式键入功能自动输入数据。只需在单元格中输入文本的前几个字母，Excel就会以本列中其他已输入文本为基础，自动完成整个文本的输入，还可自动更改大小写，保持与前面输入的一致性。
也可以在单元格上单击右键，从弹出的菜单中选择“从下拉列表中选择”选项，选择所需的选项即可。
注：记忆式键入只在连续的一列单元格中起作用，若有空行，则其只标识空行下的单元格内容。若不喜欢此功能，可取消“选项”对话框中“编辑”选项卡的“记忆式键入”复选框。
(10)强制文本换行。在需要换行处按Alt+Enter键即可。
(11)输入制表符。按Ctrl+Alt+Tab键即可。
(12)设置数据有效性。如果要在单元格或单元格区域只接受某种类型(或者在某个值范围内)数据，则选择菜单“数据——有效性……”命令，在“数据有效性”对话框中设置。
(13)引用模式切换。在输入和编辑Excel公式时，可以按F4键将公式所选部分在相对、绝对和混合模式间进行切换。
(14)公式输入技巧。以小写形式在公式中输入Excel函数，如果Excel支持这个函数，则会将函数名变为大写；否则，整个公式结果为#NAME，并将不支持的函数名保持为小写。可帮助找了公式中的命名错误。
(15)快速命名。选取要命名的范围后，直接在名称框中输入要命名的名字即可。
(16)在批注中使用图片。①创建想要在批注中显示的图片；②选中要放置批注的单元格。若还未创建，先创建批注；③在菜单栏中选择“插入”——“编辑批注”，或右键单击单元格并选择“编辑批注”； ④右键单击批注边框并选择“设置批注格式”； ⑤选中“颜色和线条”选项卡，并单击“填充”中的“颜色”下拉箭头；⑥选中“填充效果，接着选中“图片”选项卡并单击“选择图片”按钮；⑦找到想要的图片并选中，单击“确定”。
(17)调整奇数行行高[使用辅助列]
将数据区域中所有奇数行设置为相同的行高，一般的做法是，按住Ctrl键一一点选不连续的奇数行，然后进行行高设置。但若数据有成千上万行，很不方便。解决方法：
①在数据区域右侧选一空白列(辅助列)，在第一个奇数行单元格输入“=1/0”得到一个错误代码“#DIV/0！”，选中此单元格及下面一个空白单元格，当鼠标填充柄指针变为十字形时，按住左键向下拖动直到需要更改行高的奇数行的最后一个奇数行单元格处，此时，所有奇数行都被填充“#DIV/0！”。
②选中此辅助列，选择菜单“编辑——定位”命令，打开“定位条件”对话框，选中“公式”下的“错误”项，取消“数字”、“文本”、“逻辑值”的选择。单击“确定”按钮，此时，所有带有“#DIV/0！”错误值的单元格被选中，即奇数行单元格被选中。
③选择菜单“格式——行——行高”命令，打开“行高”对话框，在文本框中输入要更改的行高值。单击“确定”按钮，所有要求更改高度的奇数行的行高设置完毕。
④清空或删除辅助列。
当然，也可以选择其它的定位条件。
(18)对数据区域所有奇数行进行格式设置[使用辅助列]
①在数据区域右侧选一空白列用为辅助列，在第一个奇数行单元格中输入“0”，在下面单元格中输入“1”。
②选中“0”、“1”两个单元格，按住Ctrl键，拖动填充柄向下填充数据，直到数据区域最后一行。
③选中辅助列中的某一个单元格，选择菜单“数据——筛选——自动筛选”。单击辅助列首行的按钮，选择筛选条件“0”，此时，在数据区域中，偶数列自动隐藏。
此时，可以直接对奇数列进行操作。
④选择菜单“数据——筛选——自动筛选”，还原数据。
⑤清空或删除辅助列。
(19)巧用边框做连接图。选择菜单“工具——选项”，在“选项”对话框中选择“视图”选项卡，在“窗口选项”中去掉“网格线”选择，点击“确定”按钮。此时，工作表中无网格线显示。然后，利用添加上、下、左、右边框线，合理安排，可以做出组织机构图、赛程图、比赛对阵图等。
(20)实现非连续选择。按住Ctrl键，可以选择非连续的单元格，或非连续的行。
(21)有时候，当工作表中有大量的数据时，不便于用鼠标拖放，如何选取单元格区域中最后一个单元格或单元格区域？
①单元格区域是连续的情况。比如第一列中从第1行到第1000行的单元格中都有数据，可以先选择第1列中包含数据的第1个单元格，按Ctrl键和向下键头键即可到达最后一行第1000行的位置。
②单元格区域是连续的，要求选取单元格区域。同上，先选择第1列中包含数据的第1个单元格，按Shift+Ctrl键的同时按向下箭头键即可；也可以先按Ctrl键和向下键头键到达最后一行，然后按Shift+Ctrl键的同时按向上箭头键。
③单元格中的数据是不连续的，其中有两三处的单元格为空，且空单元格是随机的。如第1列中有1000行数据，其中有两三处不同位置为空单元格，要求选中最后一个数据单元格。先选择右边1列(即第2列)的单元格，按Ctrl键和向下箭头键，到达第65536行(即工作表最底部)，向左移动1列(即到第1列)，按Ctrl键和向上箭头键即可到达第1列最后一个数据单元格。
(21)使用数据有效性，实现数据分类。即在第一个单元格中的下拉列表中选类别，在第二个单元格中的下拉列表显示在前面所属类别的子类，依次类推。
[实现步骤]
假设环境：数据在工作表Sheet2中，在工作表Sheet1中的单元格A1、B1中实现数据下拉列表。
①在Sheet2中A1至A2中依次输入“湖北”、“湖南”，选中A1:A2范围并在名称框中输入“省份”。
②在Sheet2中B1至B4中依次输入“武汉”、“黄石”、荆州”、“宜昌”，选中B1:B4范围并在名称框中输入“湖北”。
③在Sheet2中C1至C2中依次输入“长沙”、“岳阳”，选中C1:C2范围并在名称框中输入“湖南”。
④在Sheet1中选取单元格A1。在菜单栏中选“数据——有效性”，在“允许”下拉列表中选择“序列”，在“来源”输入框中输入“=省份”，按“确定”按钮。
⑤在Sheet1中选取单元格B1。在菜单栏中选“数据——有效性”，在“允许”下拉列表中选择“序列”，在“来源”输入框中输入“=INDIRECT(A1)”，按“确定”按钮。
此时，在单元格A1中单击，选相应的省份，在单元格B1中单击后将显示相应省的城市。
(22)使用动态的公式定义范围名称，这样，当新数据添加时，范围自动扩展。
[应用一]动态命名
选择菜单栏“插入——名称——定义”，在“在当前工作薄中的名称”框中输入要命名的名称，如“动态列表”；在“引用位置”框中输入公式(基于列，可用相应更改使之基于行)，如=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)。
注：公式中使用了OFFSET函数，其参数说明依次为：Sheet1$A$1参照单元格，行偏移0，列偏移0，引用行数COUNTA(Sheet1$A:$A)表示列A非空单元格数目，引用列数为1(若为动态列数使用COUNTA(Sheet1!$A:$A))。

图1
[应用二]用动态命名公式命名范围，对满足条件的数据汇总形成数据，创建动态更新图表。根据选择和更新的数据相应更新图表。
[步骤]
①创建列表
将Sheet1重命名为SalesData，将Sheet2重命名为Chart。在SalesData工作表的单元格区域A1:D1，添加表头标题Date, Item, Colour, Units。然后在每列中输入相应的数据。

图2
②在Date列创建第一个动态命名区域。在菜单栏选择“插入——名称——定义”，在“在当前工作薄中的名称”框中输入要命名的名称，如“DateList”；在“引用位置”框中输入公式：=OFFSET(SalesData!$A$2,0,0,COUNTA(SalesData!$A:$A)-1,1)

图3
③在DateList命名区域创建另一个动态命名公式。在菜单栏选择“插入——名称——定义”，在“在当前工作薄中的名称”框中输入要命名的名称，如“ColourList”；在“引用位置”框中输入公式：=OFFSET(DateList,0,2)。

图4
接着，在菜单栏选择“插入——名称——定义”，在“在当前工作薄中的名称”框中输入要命名的名称，如“UnitsList”；在“引用位置”框中输入公式：=OFFSET(DateList,0,3)。
④使用数据有效性创建日期范围选择单元格，下拉列表可以选择图形的开始和结束日期。在Chart工作表中，选择单元格C2和E2。在菜单栏中选“数据——有效性”，在“允许”下拉列表中选择“序列”，在“来源”输入框中输入“=DateList”，按“确定”按钮。
⑤使用SUMPRODUCT公式汇总产品每类颜色的销售量。在Chart工作表的B15:B18单元格中输入颜色类型Red、Yellow、Green、Blue。在C15单元格中输入下面的公式：
=SUMPRODUCT(--(ColourList=B15),(UnitsList),--(DateList>=$C$2),--(DateList<=$E$2))
并一直拖拉至C18单元格。

图5
⑥从上面的汇总表中创建图形。选择单元格区域B15:C18，启动工具栏中图表向导，选中柱形图，移除图例并在日期选择单元格和汇总列表间调整图形尺寸，选中图形系列，单击红色列并双击找开数据格式对话框并选红色，同理，对剩余的柱状进行同样的操作。

图6
⑦测试图表。选择不同的开始和结束日期，可以看到汇总列表数据变化，并且图表立即更新。
(23)两种特殊情况下的排序技巧
1、超过三个以上关键字的排序步骤(下面为列排序步骤，同样适用于行排序)
①先确定用于排序的关键字(列)，按从高到低的优先级将关键字排序。即假设用于排序的关键字有N个，可以将它们标为1～N，1为优先级最高的关键字，N为优先级最低的关键字。
②选中工作表中所要排序的数据，从菜单中选择“数据——排序”，打开“排序”对话框。若N>3，则将标为N的关键字(或列)放在“第三关键字”中，标为N-1的关键字(或列)放在“次要关键字”中，标为N-2的关键字(或列)放在“首要关键字”中。确保准确设置了每个关键字的“升序/降序”属性，然后单击“确定”按钮。
③如果经过第2步后新的N值仍大于3，则重复第2步，否则按下面步骤操作。
④如果剩下的排序关键字为三个，则把第三个关键字放在“第三关键字”中，把第二个关键字放在“次要关键字”中，把第一个关键字放在“首要关键字”中。
如果剩下的排序关键字为二个，则把第二个关键字放在“次要关键字”中，把第一个关键字放在“首要关键字”中，清空“第三关键字”的项目。
如果剩下的排序关键字只有一个，则把第一个关键字放在“首要关键字”中，清空“次要关键字”和“第三关键字”的项目。
确保准确设置了每个关键字的“升序/降序”属性，然后单击“确定”按钮。
排序完成。
2、块排序[使用辅助列]
所谓块，即数据清单按某规律纵向排列。例如ID(人员编号)、姓名、工作单位、邮政编码构成单个的块，这些块并不是按顺序排列的，现要将ID为8的信息移至编号为7的人员信息下方。
假设ID(人员编号)、姓名、工作单位、邮政编码等项的名称在第1列，具体信息在第2列。
①搭建支架(即创建辅助列)，将所有辅助信息(姓名、工作单位、邮政编码)与人员编号捆绑在一起。然后，利用这个支架作为排序关键字进行排序。
如在第3列人员编号旁对应单位格输入公式=IF(RC[-2]=“ID”,RC[-1],R[-1]C)。(注：此为R1C1样式公式，应先切换为R1C1状态；另ID必须为英文，中文字符如“编号”不认？？)
数据应从第2行开始。
②将公式下拉复制至数据行尾。
③进行排序。选择“菜单——排序”命令，在“排序”对话框中，选择“无标题行”单选钮，在“主要关键字”中选辅助列，单击“确定”。即可按ID号进行了排序。
④若不想保留构架(辅助列)，可选中辅助列后，删除即可。
(24)从已关闭的工作簿中获取值。如获取C盘根目录下Book1工作簿中的工作表Sheet1中单元格A1的值，可以在当前工作表中输入“= ‘C：/ [ Book1.xls]Sheet1’!$A$1”。
一般的引用规则是：
(1)若同一工作簿中工作表sheet1需要引用工作表中sheet2中的单元格，应写为=Sheet2!A1；
(2)若工作簿Book1要引用工作簿Book2中工作表sheet1中的单元格，应写为=[Book2.xls]sheet1!A1；
(3)若工作簿Book1要引用工作簿My Book(即工作簿名包含空格)中工作表sheet1中的单元格，应写为='[My Book.xls]Sheet1'!A1，即要加上一对单引号；
(4)如果要引用的工作簿关闭了，则必须加上该工作簿的完整路径，如='C:/[My Book.xls]Sheet1'!A1。
(25)“摄影”功能
在Excel的工具菜单中，有一个未列出的“摄影”功能，您可以使用它将单格或单元格区域转化成图片。
选择菜单“工具——自定义”，弹出“自定义”对话框，在“命令”选项卡左侧选择“工具”，右侧找到“照相机”，点击“照相机”并按住将其拖至菜单栏工具条中或某一菜单下。您就可以看到菜单工具条或某菜单下有一个照相机图标形式的按钮。
在工作表中选择单元格或单元格区域后，单击“照相机”按钮，此时会生成一个包含此单元格或单元格区域的图片。该图片会随单元格或单元格区域的变化而自动变化哟。