EXCEL应用技能.doc

EXCEL高级应用技能培训如何简化输入重复性的文字（例1）方法1：先选中要输入重复性文字的区域（可以用鼠标拖动选中连续的区域，也可以用结合Ctrl键选中不连续的区域），然后在空白格中输入所需文字后，再按住Ctrl+Enter即可。方法2：通过“自动填充手柄”工具进行常用数据的输入技巧（在输入数据时输入法应该时英文输入法）1、分数的输入正分数 (例3.1) 方法：（0 n/m） 提示：在单元格中先输入0，敲一个空格后再输入n/m即可，不包括括弧 负分数(例3.1) 方法：（0 n/m） 提示：在单元格中先输入0，敲一个空格后再输入n/m即可，一定包括括弧2、序列“00001”的输入（例3.2） 方法：（ 00001） 提示：在单元格中先输入，之后再输入00001即可，中间有无控格都可3、日期的输入（例3.3） 方法：如果要输入“4月5日”，直接输入“4/5”，再敲回车就行了。如果要输入当前系统日期，按一下“Ctrl+；”键4、时间的输入（例3.4） 方法：在单元格中输入Ctrl+Shift+: 提示:显示的是当前系统时间如何让多页的内容打印在一张纸张上（例2）方法：1、可以通过“文件”“页面设置”中的“缩放比例”进行设置。 2、可以通过“视图”“分页预览”进行操作如何使用EXCEl中的快捷键和功能键(例4)重点掌握：1、home 2、Ctrl+home 3、Ctrl+ 4、Ctrl+5、Ctrl+ 6、Ctrl+ 7，在同一工作表中数据的移动和复制结合Ctrl和shift键 8、Ctrl+1（显示“单元格格式”对话框）9、Ctrl+9 隐藏选定的行10、Ctrl+0 （隐藏选定的列）11、Ctrl+shift+9 显示隐藏选定的行 12、Ctrl+ shift+0 （显示隐藏选定的列）13、Ctrl+ 在工作表中切换显示单元格值和公式14、Ctrl+shift+( 取消隐藏选定范围内所有隐藏的行。Ctrl+ shift+) 取消隐藏选定范围内所有隐藏的列。表格中会出现#！、#VALUE!、#DIV、#NAME、#N/A，是为什么 1、#！产生原因：如果单元格所含的数字、日期或时间比单元格宽，或者单元格的日期时间公式产生了一个负值，就会产生#！。方法：通过拖动列表之间的宽度来修改列宽（例5.1） 2、#VALUE! 产生原因：当使用错误的参数或运算对象类型时，或者当公式自动更正功能不能更正公式时，将产生错误值#VALUE!。（例5.2.1） 解决方法：确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值。例如：如果单元格A1包含一个数字，单元格A2包含文本，则公式=A1+A2将返回错误值#VALUE!。可以用SUM工作表函数将这两个值相加（SUM函数忽略文本）：=SUM（A1:A2）。 3、#DIV/O! 产生原因当公式被零除时，将会产生错误值#DIV/O!。（例5.3）方法：将零改为非零值4、#N/A 产生原因：当在函数或公式中没有可用数值时，将产生错误值#N/A。（例5.4）方法：如果工作表中某些单元格暂时没有数值，请在这些单元格中输入#N/A，公式在引用这些单元格时，将不进行数值计算，而是返回#N/A。5、#REF!产生原因：删除了由其他公式引用的单元格，或将移动单元格粘贴到由其他公式引用的单元格中。当单元格引用无效时将产生错误值#REF！（例5.5）方法：更改公式或者在删除或粘贴单元格之后，立即单击撤消按钮，以恢复工作表中的单元格 6、#NUM！#Name？产生原因：当公式或函数中某个数字有问题时将产生错误值#NUM！。是无效数值的意思（例5.6）输入e的177000时结果显示#NUM! 7、#NULL！（例5.7）产生原因：使用了不正确的区域运算符或不正确的单元格引用。当试图为两个并不相交的区域指定交叉点时将产生错误值#NULL8、#REF! （例5.8）产生原因：删除了由其他公式引用的单元格，或将移动单元格粘贴到由其他公式引用的单元格中。当单元格引用无效时将产生错误值#REF！。六、文本函数及经典例子 1、随意抽取文本的一部分 （例6）一、LEFT,RIGHT,MID LEFT,RIGHT,MID，这三个函数都是文本的提取的。前两个的格式是一样的，只是提取的方向正好相反。LEFT是从左向右取，RIGHT是从右向左取。 LEFT(text,num) RIGHT(text,num_chars) 第一个参数text,是文本，是你想要在那提取东西的文本，可以是一个字符串，或是一个单元格。第二个参数chars是想要提取的个数。 比如：=LEFT(A1,2)，也是从A1单元格的文本里，从左边第一位开始，向右提取两位。比如A1的数据是：“找吃的蚂蚁”，得到的结果是：“找吃”。 那么：=RIGHT(A1,2)，也就是从A1单元格的文本里，从右边第一位开，向左提取两位，这样就得到：“蚂蚁”。查找函数的应用MATCH()格式:MATCH(lookup_value,lookup_array,match_type) 从英文含义我们可以看出,它是一个匹配的函数,主要功能是按照match_type参数所指定的查找方式,在lookup_array参数所代表的数据组中查找lookup_value参数 代表的数值,如果查找成功则返回lookup_value在lookup_array中的位置,不成功返 回错误信息#N/A。（例7.1）技巧带来的方便 1、填充条纹（例8） 在工作簿中加入漂亮的横条纹，可以利用对齐方式中的填充功能。 方法：先在一单元格内填入“*”或“”等符号，然后单击此单元格， 向右拖动鼠标，选中横向若干单元格，单击“格式”菜单，选中“单元格”命令，在弹出的“单元格格式”菜单中，选择“对齐”选项卡，在水平对齐下拉列表中选 择“填充”，单击“确定”按钮2、多张工作表中输入相同的内容（例8.2）几个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住Ctrl键，再单击窗口左下角的Sheet1、 Sheet2.来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入这些相同的数据，此时这些数据会自动出现在选中的其 它工作表之中。输入完毕之后，再次按下键盘上的Ctrl键，然后使用鼠标左键单击所选择的多个工作表，解除这些工作表的联系，否则在一张表单中输入的数据 会接着出现在选中的其它工作表内。3、单元格内容的合并根据需要，有时想把B列与C列的内容进行合并，如果行数较少，可以直接用“剪切”和“粘贴”来完成操作，但如果有几万行，就不能这样办了。 方法1是（例8.3）：在C行后插入一个空列（如果D列没有内容，就直接在D列操作），在1中输入“=B1&C1”，D1列的内容 就是B、C两列的和了。选中D1单元格，用鼠标指向单元格右下角的小方块“”，当光标变成后，按住鼠标拖动光标向下拖到要合并的结尾行处，就完成 了B列和C列的合并。这时先不要忙着把B列和C列删除，先要把D列的结果复制一下，再用“选择性粘贴”命令，将数据粘贴到一个空列上。这时再删掉B、C、 D列的数据。 方法2是：（例8.3.1）利用CONCATENATE函数，此函数的作用是将若干文字串合并到一个字串中，具体操作为 “=CONCATENATE（B1,C1）”。比如，假设在某一河流生态调查工作表中，B2包含“物种”、B3包含“河鳟鱼”，B7包含总数45，那么： 输入“=CONCATENATE（本次河流生态调查结果：，B2，B3，为，B7，条/公里。）” 计算结果为：本次河流生态调查结果：河鳟鱼物种为45条/公里。 4、格式刷的另一种用法（例8.3.2） 格式刷单击和双击的用法九：EXCEL中行列互换（例） 方法：复制，选择性粘贴，选中转置，确定即可常用统计函数和统计方法在工作中的应用（例10） 1、max（）；2、min() 3、Today()、数据透视表的应用（例11） 方法：通过“数据“”数据透视表和透视图“进行补充：插入图表的使用（例）、如何保护公式不被误修改（例12） 方法：1、全选工作表，按Ctrl+1打开“单元格格式“窗口，选其中保护，取消勾选“锁定”，确定2、按F5，定位条件，选“公式”，确定3、再按Ctrl+F1，保护，勾选“锁定”，确定。上面这三步，使有公式的单元格勾选“锁定”，其余不勾选。4、工具-保护-保护工作表，设定密码，确定。完成、如何设置可编辑区域和不可编辑区域（例39） 方法、：1、先选定所有单元格,点格式-单元格-保护,取消锁定前面的。 2、再选定你要保护的单元格,点格式-单元格-保护,在锁定前面打上。 3、点工具-保护-保护工作表,输入两次密码,点两次确定即可。、如何设置文档访问密码 方法：，通过工具-选项，再点安全性，里面就有设置密码的选项如何设置工作表隐藏，以增加工作表的安全性（例15） 在实际工作时，往往需要将一些重要的工作表隐藏起来，然而很多朋友在隐藏工作表时，都是采用菜单“格式/工作表/隐藏”来完成，岂不知如此隐藏工作表，对于略懂EXCEL的人来说，基本上是形同虚设，因为我们同样可以用“格式/工作表/取消隐藏”来将其显示出来，那么究竟如何才能做到真正的隐藏呢？其实方法很简单，步骤如下： 1、选择菜单“工具/宏/VisualBasic编辑器”命令，打开MicrosoftVisualBasic程序窗口，在左侧可以看到“工程”和“属性”面板。 2、在“工程”面板中选择要隐藏的工作表，并在“属性”面板中设置“Visible”属性值为“2-xlSheetVeryHidden”如果设置为0-xlSheetHidden则为一般性隐藏，通过“格式/工作表/取消隐藏”仍可以将其取消。 3、选择“工具/VBAProject属性”命令，打开“VBAProject工程属性”对话框，选择“保护”选项卡，勾选“查看时锁定工程”，并设置查看工程属性的密码，单击“确定”按钮。 4、按下Ctrl+S保存文件，返回Excel界面即可看到工作表已经被隐藏。此时菜单“格式/工作表/取消隐藏”也是处于灰色状态。即使有人知道这种方法，没有密码也是不能显示出隐藏的工作表，赶快试试吧十六、如何灵活引用单元格（同26）十七、如何建立在同一工作簿中的工作表之间的联系（例十七）十八、如何建立不在同一工作簿中的工作表之间的联系（例18）十九、如何增加和减少单元格中的小数位数 方法：1、可以通过格式工具栏中的“增加小数位数”按钮和“减少小数位数”按钮进行操作 2、或通过“单元格格式”设置窗口中的”数字“选项卡中的“数值”小数位数进行二十、如何在excel里面设置数据的有效性（例20） 方法：通过：“数据”“有效性”进行设置即可二十一、如何在excel里面实现数据的自动筛选（例21） 方法：通过“数据“菜单”筛选“”自动筛选“进行二十二、如何在excel里面实现数据的高级筛选（例22）方法：通过“数据“菜单”筛选“”高级筛选“进行补充：分类汇总（例） 方法：1先“数据“”排序“ 2再“数据“”分类汇总“二十三、“填充”与“序列“功能的应用如何快速输入数据序列1、在第一个单元格内输入起始数据，在下一个单元格内输入第二个数据，选定这两个单元格，将光标指向单元格右下方的填充柄，沿着要填充的方向拖动填充柄，拖过的单元格中会自动按 Excel内部规定的序列进行填充。（例23）2、如果能将自己经常要用到的某些有规律的数据（如办公室人员名单），定义成序列，以备日后自动填充，岂不一劳永逸！选择 “工具”菜单中的“选项”命令，再选择“自定义序列”标签，在输入框中输入新序列，注意在新序列各项之间要输入半角符号的逗号加以分隔（例如：张三，李四，王二），单击“增加”按钮将输入的序列保存起来。（例23.2）二十四、“排序”功能在工作的应用（例24） 方法：通过“数据“菜单中的”排序“进行二十五、如何使用IF函数实现逻辑判断 方法：利用If函数，可以实现按照条件显示。一个常用的例子，就是教师在统计学生成绩时，希望输入60以下的分数时，能显示为 “不及格”；输入60以上的分数时，显示为“及格。这样的效果，利用IF函数可以很方便地实现。 假设成绩在A2单元格中，判断结果在A3单元格中。那么在A3单元格中输入公式： =if（A260，“不及格”，“及格”） 同时，在IF函数中还可以嵌套IF函数或其它函数。例1，如果输入： =if（A260，“不及格”，if（A2=90，“及格”，“优秀） 就把成绩分成了三个等级。例2，如果输入=if（A260，“差，if（A2=70，“中”，if（A2单元格-保护,取消锁定前面的。 2、再选定你要保护的单元格,点格式-单元格-保护,在锁定前面打上。 3、点工具-保护-保护工作表,输入两次密码,点两次确定即可。四十：EXCEL中怎样批量地处理按行排序（例40）假定有大量的数据(数值)，需要将每一行按从大到小排序，如何操作方法：1、假定你的数据在A至E列，请在F1单元格输入公式：=LARGE($A1:$E1,COLUMN(A1)用填充柄将公式向右向下复制到相应范围。你原有数据将按行从大到小排序出现在F至J列。如有需要可用“选择性粘贴/数值”复制到其他地方。注：第1步的公式可根据你的实际情况（数据范围）作相应的修改。如果要从小到大排序,公式改为:=SMALL($A1:$E1,COLUMN(A1)四十一：EXCEL中某个单元格内文字行间距调整方法。（例41）当某个单元格内有大量文字时，很多人都觉得很难将其行间距按自己的要求进行调整。现介绍一种方法可以让你任意调整单元格内文字的行间距：右击单元格,点设置单元格格式-对齐,将水平对齐选择靠左,将垂直对齐选择分散对齐,选中自动换行，点“确定”。你再用鼠标将行高根据你要求的行距调整到