Excel数据处理与分析实战精粹
Excel数据处理与分析实战技巧精粹 1、使用工作表保护:1)选定所有允许访问的单元格，单击菜单,格式,—,单元格,;2)在打开的“单元格格式”对话框“保护”选项卡中取消勾选“锁定”复选框，然后单击“确定”按钮;3)单击菜单,工具,—,保护,—,保护工作表,，在打开的“保护工作表”对话框中取消勾选“选定锁定单元格”复选框;4)如果需要，可以加上工作表保护密码并确认密码，最后单击“确定”按钮即可;5)此时在工作表中只有未锁定的单元格可以被选中和编辑，而其他的单元格都会受到Excel的保护，无法被选中更无法进行编辑。
注意:也可以把你需要隐藏的文字设置成白色，然后再进行以上的工作表保护，那别人既看不到你的内容也没办法修改了。
2、标题行的重复打印:1)单击菜单,文件,—,页面设置,，在打开的“页面设置”对话框中单击“工作表”选项卡;2)如果要指定在顶部重复的一行或连续的几行，则可单击“顶端标题行”文本框最右边的按钮，然后在工作表中进行相应的指定;3)如果要指定在左侧重复的一行或连续的几行，则可单击“左端标题列”文本框最右边的按钮，然后在工作表中进行相应的指定，如下图所示;4)单击“确定”按钮关闭“页面设置”对话框。
3、巧用右键和双击填充:如果用户先输入内容到一个单元格，然后用鼠标右键拖曳此单元格，那么松开右键，就会出现一个快捷菜单，菜单上显示了本次填充可以选用的类型，甚至可以链接到“序列”对话框进行更复杂的设置，如下图所示:
当用户在数据区域中对某一列的内容进行填充时，不必从第一个单元格一直拖曳到最后一个单元格，只需要双击第一个单元格的填充柄即可。但是使用双击的方法进行填充的时候，填充到的最后一个单元格的位置则取决于左边一列中第一个空白单元格的位置(如果填充列是第一列，则参考右边列中的单元格)。例如在下图中因为A5是空白单元格，所以B列中的填充只进行到B4单元格就会停止。
4、快速输入对号、错号、平方与立方:下面的数字均必须在小键盘上输入。
名称 快捷键 名称 快捷键 名称 快捷键 名称 快捷键 对号 错号 平方 立方
ALT+41420 ALT+41409 ALT+178 ALT+179 5、神奇的选择性粘贴:1)全部:在绝大多数情况下等效于常规的粘贴;2)公式:只复制原始区域的公式;3)数值:只复制数值，如果原始区域是公式，则只复制公式的计算结果;4)格式:只复制原始区域的格式;5)批注:只复制原始区域的批注;6)有效性验证:只复制原始区域中设置的数字有效性;7)边框除外:复制边框之外的所有内容;8)列宽:从一列到另一列复制列宽信息;9)
跳过空单元格:可以有效地防止原始区域中的空单元格覆盖粘贴目标区域中的单元格内容;10)转置:能够让原始区域在复制后行列互换;11)粘贴链接:将建立一个由公式组成的连接原始区域的动态链接;12)加:允许用户进行一次简单的数值运算。例如:选定任意一个空白单元格，然后按
6、创建动态名称:如果需要创建一个名称来引用C列中的数据，但又不希望这个引用区域包含空白单元格，在这种情况下可以创建动态名称，根据用户追加或删除数据的结果来自动地调整引用的位置，以达到始终引用非空白单元格区域的结果。创建动态名称的方法如下:1)单击菜单,插入,—,名称,—,定义,;2)打开“定义名称”对话框，在“在当前工作薄中的名称”文本框中输入“Date”，在“引用位置”文本框中输入公式:=OFFSET(Sheet1!$C$4,,,COUNTA(Sh
eet1!$C:$C)-1)，如下图所示，单击“确定”按钮。
以上公式先计算C列中除了列标题以外的非空白单元格的数量，然后以C4单元格(首个数据单元格)为基准开始向下定位，定位的行数等于刚才计算出来的数据。下面可以在C列以外的单元格中通过计算来验证此名称的引用是否正确，比如在B1中输入公式:=SUM(Date)，如下图所示。
如果继续追加记录，名称“Date”的引用位置就会自动地发生改变，B2中的计算结果能够体现这一点，如下图所示。
注意:以上公式只能正确计算不间断的连续数据，如果表格中的数据有空白单元格，那么动态名称的引用位置将发生错误。
7、单元格中切换中英文输入法:例如选定A列，单击菜单,数据,—,有效性,打开“数据有效性”对话框，选择“输入法模式”选项卡，在“模式”下拉列表中选择“打开”选项，然后单击“确定”按钮即可，用同样的方法可以把B列的“输入法模式”设置为“关闭(英文模式)”，如果用户打开了某一种中文输入法，那么选定A列的单元格时将激活该输入法，选定B列的单元格时将屏蔽该输入法，而显示“英语(美国)”图标，当用户需要限制某个单元格区域不能输入中文时就可以使用此方法，以免重复地切换中英文输入法而带来麻烦。 8、限制输入重复数据:选定A列，单击菜单,数据,—,有效性,打开“数据有效性”对话框;2)选择“设置”选项卡，在“允许”下拉列表中选择“自定义”选项，在“公式”文本框中输入“=COUNTIF(A:A,A1)=1”，单击“确定”按钮。
9、禁止重复报餐:某公司为方便员工中午用餐，在3家餐馆为员工定餐，员工可以根据个人的喜好选择其中的一家餐馆报餐。负责报餐的工作人员制作了一个
Excel文件，如下图所示:
让每位员工自己在工作表上报餐，方法为:在自己名字与日期交叉的单元格输入数字1，工作人员只需对相应的单元格区域求和就可以算出某家餐馆共有几位员工报餐，以便与餐馆结账，为了防止同一员工同一天在多家餐馆重复报餐，可以设置数据有效性来禁止，方法如下:1)选定B2单元格，单击菜单,插入,—,名称,—,定义,打开“定义名称”对话框;2)在“在当前工作簿中的名称”文本框中输入名称“Count”，在“引用位置”文本框中输入“=”，鼠标单击第1张工作表(鑫德海)的标签，按住
3)选定“鑫海德”工作表的单元格区域B2:F26，单击菜单,数据,—,有效性,打开“数据有效性”对话框;4)选择“设置”选项卡，在“允许”下拉列表中选择“自定义”选项，在“公式”文本框中输入“=SUM(COUNT)=1”，并取消“忽略空值”复选框的勾选状态;5)切换到“出错警告”选项卡，在“样式”下拉列表中选择“停止”选项，在“标题”文本框中输入“注意～”，在“错误信息”文本框中输入“请不要重复报餐～”，然后单击“确定”按钮;6)重复步骤3,步骤5，为另外两个工作表设置相同的数据有效性。
通过设置以上的数据有效性后，同一员工在同一天就不能在多家餐馆重复报餐了。如A12单元格的“西门雪”在“鑫德海”报餐后，如果要在“禄鼎记”
再报一次餐，Excel就会弹出“注意～”对话框，阻止用户继续报餐。
公式解析:名称Count是一个三维引用的名称，返回3个工作表的同一位置单元格的引用，B2单元格的有效性公式“=SUM(COUNT)”即表示“=鑫德海～B2+禄鼎记～B2+一品套餐～B2”。
10、创建二级下拉菜单_源为单个工作表:下图所示的是“查询”表的B1单元格创建下拉菜单，可供选择的选项为“烟”和“酒”，C1单元格能根据B1单元格所选择的不同内容产生不同序列内容的下拉菜单，并且下拉菜单中不能包含空格，要创建这样的下拉菜单，方法如下:
1)使用直接在有效性中输入序列内容创建下拉菜单的方法，为“查询”表B1单元格创建序列为“烟”和“酒”的下拉菜单，如下图所示:
2)单击菜单,插入,—,名称,—,定义,弹出“定义名称”对话框，在“当前工作薄中的名称”文本框中输入“PingMing”，在“引用位置”文本框中输入公式:=OFFSET(数据!$A$2,,MATCH(查询!$B2,数据!$1:$1,)-1,COUNTA(OFFSE
T(数据!$A$2,,MATCH(查询!$B2,数据!$1:$1,)-1,65535)))，如下图12所示，然后单击“确定”按钮。
3)选定“查询”表中的C1单元格，单击菜单,数据,—,有效性,打开“数据有效性”对话框; 4)切换到“设置”选项卡，在“允许”下拉列表中选择“序列”选项，在“来源”文本框中输入“=PinMing”， 然后单击“确定”按钮。
5)在“查询”表的D1单元格中输入公式:“=SUMIF(数据!A:C,C2,数据!B:D)”。 11、导入Word文档中的表格:Word文档中的表格不能直接导入Excel工作表中，不过用户可以采用,复制,—,粘贴,的方法将Word文档中的表格复制到Excel工作表中。但如果文档中的表格较多时，复制起来就会很不方便。这里介绍一下通过网页文件快速导入Word文档中的数据表格的方法。1)打开Word文档“示例5”，在Word工作窗口中单击菜单,文件,—,另存为,对话框，在“保存类型”下拉列表中选择“单个文件网页”，然后单击“保存”按钮将该文档另存为网页文件;2)单击菜单,数据,—,导入外部数据,—,新建Web查询,打开“新建Web查询”对话框;3)在“新建Web查询”对话框的“地址”下拉列表文本框中输入刚才保存的文件的完整路径，如file:///H:/fscommand
/project/第2篇%20数据的输入和导入/Chapter7%20导入外部数据/示例5.mht，然后单击“转到”按钮打开网页文件;4)在“新建Web查询”对话框中分别单击两个表格左上角的“”标识将其选中，标识同时分别会变为“”，然后单击“导入”按钮打开“导入数据”对话框;5)在“数据的放置位置”组合框中选中“现有工作表”单选按钮，并在文本框中输入数据导入的起始单元格位置“=$A$1”;6)单击“确定”按钮即可导入数据，完成Word文档中表格的导入工作。
12、快速插入多个单元格:更快捷的方法是:先选定目标单元格区域，然后按住
13、快速改变行列的次序:把光标移动到C列右侧的黑色边框上，按住
14、快速缩放数值:许多用户在工作中常常需要处理很大的数字，而利用下面的自定义数字格式就能够在不改变数值本身的同时对它们进行缩放，如下图所示。
15、单元格文本数据分行:在下图中，A1单元格是由一个由多个成语连接而成的字符串，各个字符之间没有间隔，现需要将这一字符串中的各个成语分成多行并排显示，方法如下:
1)将A列的列宽调整到显示4个汉字的宽度;2)选中A1单元格，单击菜单[编辑]—[填充]—[内容重排]弹出“文本将超出选定区域”的警告窗口;3)单击警告窗口中的“确定”按钮即可得到分行结果。
注意:使用这种方法也可以将多行数据合并成一行。
16、多行多列数据转为单列数据: