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张工作表(鑫德海)
