EXCEL应用技能new.doc

1、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

2、、序列“00001” 的输入（ 例 3.2）方法：（ 00001） 提示：在单元格中先输入，之后再输入 00001 即可，中间有无控格都可3、日期的输入（ 例 3.3）方法：如果要输入“4 月 5 日”，直接输入“4/5” ，再敲回车就行了。如果要输入当前系统日期，按一下“Ctrl+；” 键4、时间的输入（ 例 3.4）方法：在单元格中输入 Ctrl+Shift+: 提示: 显示的是当前系统时间如何让多页的内容打印在一张纸张上（ 例 2）方法：1、可以通过“文件”“页面设置”中的“缩放比例”进行设置。2、可以通过“视图”“分页预览”进行操作如何使用 EXCEl 中的快捷键和功能键( 例 4)重

3、点掌握：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!

4、、#DIV、#NAME、#N/A，是为什么1、#！产生原因：如果单元格所含的数字、日期或时间比单元格宽，或者单元格的日期时间公式产生了一个负值，就会产生#！。方法：通过拖动列表之间的宽度来修改列宽（ 例 5.1）2、#VALUE!产生原因：当使用错误的参数或运算对象类型时，或者当公式自动更正功能不能更正公式时，将产生错误值#VALUE!。 （ 例 5.2.1）解决方法：确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值。例如：如果单元格 A1 包含一个数字，单元格 A2 包含文本，则公式 =“A1+A2“将返回错误值#VALUE! 。可以用 SUM 工作表函数将这两个值

5、相加（ 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、元格之后，立即单击“撤消“ 按钮，以恢复工作表中的单元格6、 #NUM ！#Name ？产生原因：当公式或函数中某个数字有问题时将产生错误值#NUM！。是无效数值的意思（ 例 5.6）输入 e 的 177000 时结果显示#NUM!7、 #NULL！（ 例 5.7）产生原因：使用了不正确的区域运算符或不正确的单元格引用。当试图为两个并不相交的区域指定交叉点时将产生错误值#NULL8、#REF! （ 例 5.8）产生原因：删除了由其他公式引用的单元格，或将移动单元格粘贴到由其他公式引用的单元格中。当单元格引用无效时将产生错误值#REF！。六、文本函数及经典例子1、随意抽取文本的一部分 （ 例 6

7、）一 、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)，也就是从

8、 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）在工作簿中加入漂亮的横条纹

9、，可以利用对齐方式中的填充功能。方法：先在一单元格内填入“*” 或“” 等符号，然后单击此单元格， 向右拖动鼠标，选中横向若干单元格，单击“格式”菜单，选中“单元格” 命令，在弹出的“单元格格式”菜单中，选择“ 对齐”选项卡，在水平对齐下拉列表中选 择“填充”，单击“确定” 按钮2、多张工作表中输入相同的内容（ 例 8.2）几个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住 Ctrl 键，再单击窗口左下角的 Sheet1、 Sheet2来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入这些相同的数据，此时这些数据会自动出现在选中的其 它工作表之中。输入完毕之

10、后，再次按下键盘上的 Ctrl 键，然后使用鼠标左键单击所选择的多个工作表，解除这些工作表的联系，否则在一张表单中输入的数据 会接着出现在选中的其它工作表内。3、单元格内容的合并根据需要，有时想把 B 列与 C 列的内容进行合并，如果行数较少，可以直接用“剪切”和“粘贴” 来完成操作，但如果有几万行，就不能这样办了。方法 1 是（ 例 8.3）：在 C 行后插入一个空列（如果 D 列没有内容，就直接在 D 列操作），在1 中输入“=B1&C1” ，D1 列的内容 就是 B、C 两列的和了。选中 D1 单元格，用鼠标指向单元格右下角的小方块“”，当光标变成“ “后，按住鼠标拖动光标向下拖到要合并

11、的结尾行处，就完成 了 B 列和 C 列的合并。这时先不要忙着把 B 列和 C 列删除，先要把 D 列的结果复制一下，再用“选择性粘贴”命令，将数据粘贴到一个空列上。这时再删掉 B、C、 D 列的数据。方法 2 是：（ 例 8.3.1）利用 CONCATENATE 函数，此函数的作用是将若干文字串合并到一个字串中，具体操作为 “=CONCATENATE（B1,C1）”。比如，假设在某一河流生态调查工作表中，B2 包含“物种 ”、 B3 包含 “河鳟鱼” ，B7 包含总数 45，那么： 输入 “=CONCATENATE（“本次河流生态调查结果：“，B2 ，“ ，B3 ，“ 为“，B7 ，“条/公

12、里。“ ）” 计算结果为：本次河流生态调查结果：河鳟鱼物种为 45 条/公里。4、格式刷的另一种用法（ 例 8.3.2）格式刷单击和双击的用法九：EXCEL 中行列互换（ 例）方法：复制，选择性粘贴，选中转置，确定即可常用统计函数和统计方法在工作中的应用（ 例 10）1、max（） ；2、min() 3、Today()、数据透视表的应用（ 例 11）方法：通过“数据“”数据透视表和透视图“进行补充：插入图表的使用（例）、如何保护公式不被误修改（ 例 12）方法：1、全选工作表，按 Ctrl+1 打开“单元格格式“窗口，选其中保护，取消勾选“锁定”，确定2、按 F5，定位条件，选“公式”，确定3

13、、再按 Ctrl+F1，保护，勾选“锁定”，确定。上面这三步，使有公式的单元格勾选“锁定”，其余不勾选。4、工具-保护- 保护工作表，设定密码，确定。完成、如何设置可编辑区域和不可编辑区域（ 例 39）方法、：1、先选定所有单元格, 点“ 格式“-“单元格“-“保护“,取消“锁定“ 前面的“ 。2、再选定你要保护的单元格, 点“ 格式“-“单元格“-“保护“,在“锁定“ 前面打上“ 。3、点 “工具“-“保护“-“保护工作表“, 输入两次密码,点两次“确定“ 即可。、如何设置文档访问密码方法：，通过工具-选项，再点 安全性，里面就有设置密码的选项如何设置工作表隐藏，以增加工作表的安全性（ 例

14、15）在实际工作时，往往需要将一些重要的工作表隐藏起来，然而很多朋友在隐藏工作表时，都是采用菜单“格式/工作表/隐藏”来完成，岂不知如此隐藏工作表，对于略懂 EXCEL 的人来说，基本上是形同虚设，因为我们同样可以用“格式/工作表 /取消隐藏”来将其显示出来，那么究竟如何才能做到真正的隐藏呢？其实方法很简单，步骤如下： 1、选择菜单“工具/宏/Visual Basic 编辑器”命令，打开 Microsoft Visual Basic 程序窗口，在左侧可以看到“工程”和“属性”面板。 2、在“工程”面板中选择要隐藏的工作表，并在“属性”面板中设置“Visible ”属性值为“2-xlSheetV

15、eryHidden”如果设置为 0-xlSheetHidden 则为一般性隐藏，通过“格式/工作表/取消隐藏”仍可以将其取消。 3、选择“工具/VBAProject 属性”命令，打开“VBAProject 工程属性”对话框，选择“保护”选项卡，勾选“查看时锁定工程”，并设置查看工程属性的密码，单击“确定 ”按钮。 4、按下 Ctrl+S 保存文件，返回 Excel 界面即可看到工作表已经被隐藏。此时菜单 “格式/工作表/取消隐藏”也是处于灰色状态。即使有人知道这种方法，没有密码也是不能显示出隐藏的工作表，赶快试试吧十六、如何灵活引用单元格（ 同 26）十七、如何建立在同一工作簿中的工作表之间的

16、联系（例十七）十八、如何建立不在同一工作簿中的工作表之间的联系（ 例 18）十九、如何增加和减少单元格中的小数位数方法：1、可以通过格式工具栏中的“增加小数位数”按钮和“减少小数位数”按钮进行操作2、或通过“单元格格式”设置窗口中的”数字“选项卡中的“数值”小数位数进行二十、如何在 excel 里面设置数据的有效性（ 例 20）方法：通过：“数据”“有效性”进行设置即可二十一、如何在 excel 里面实现数据的自动筛选（ 例 21）方法：通过“数据“菜单”筛选“”自动筛选“进行二十二、如何在 excel 里面实现数据的高级筛选（ 例 22）方法：通过“数据“菜单”筛选“”高级筛选“进行补充：分

17、类汇总（例）方法：1 先“数据“”排序“2 再“数据“”分类汇总“二十三、“填充”与“序列“功能的应用如何快速输入数据序列1、在第一个单元格内输入起始数据，在下一个单元格内输入第二个数据，选定这两个单元格，将光标指向单元格右下方的填充柄，沿着要填充的方向拖动填充柄，拖过的单元格中会自动按 Excel 内部规定的序列进行填充。 （ 例 23）2、如果能将自己经常要用到的某些有规律的数据（如办公室人员名单） ，定义成序列，以备日后自动填充，岂不一劳永逸！选择 “工具”菜单中的“ 选项”命令，再选择“自定义序列”标签，在输入框中输入新序列，注意在新序列各项之间要输入半角符号的逗号加以分隔（例如：张三

18、，李四，王二） ，单击“增加” 按钮将输入的序列保存起来。 （ 例 23.2）二十四、 “排序”功能在工作的应用（ 例 24）方法：通过“数据“菜单中的”排序“进行二十五、如何使用 IF 函数实现逻辑判断方法：利用 If 函数，可以实现按照条件显示。一个常用的例子，就是教师在统计学生成绩时，希望输入 60 以下的分数时，能显示为 “不及格” ；输入 60 以上的分数时，显示为“ 及格“。这样的效果，利用IF 函数可以很方便地实现。 假设成绩在 A2 单元格中，判断结果在 A3 单元格中。那么在 A3 单元格中输入公式： =if（A2“单元格“-“保护“,取消“锁定“ 前面的“ 。2、再选定你要

19、保护的单元格, 点“ 格式“-“单元格“-“保护“,在“锁定“ 前面打上“ 。3、点 “工具“-“保护“-“保护工作表“, 输入两次密码,点两次“确定“ 即可。四十：EXCEL 中怎样批量地处理按行排序（ 例 40）假定有大量的数据(数值) ，需要将每一行按从大到小排序，如何操作方法：1、假定你的数据在 A 至 E 列，请在 F1 单元格输入公式：=LARGE($A1:$E1,COLUMN(A1)用填充柄将公式向右向下复制到相应范围。你原有数据将按行从大到小排序出现在 F 至 J 列。如有需要可用 “选择性粘贴/数值”复制到其他地方。注：第 1 步的公式可根据你的实际情况（数据范围）作相应的修

20、改。如果要从小到大排序,公式改为:=SMALL($A1:$E1,COLUMN(A1)四十一：EXCEL 中某个单元格内文字行间距调整方法。 （ 例 41）当某个单元格内有大量文字时，很多人都觉得很难将其行间距按自己的要求进行调整。现介绍一种方法可以让你任意调整单元格内文字的行间距：右击单元格,点“设置单元格格式“-“对齐“,将“ 水平对齐“选择“靠左“,将“ 垂直对齐“选择“分散对齐“,选中“自动换行 “，点“确定” 。你再用鼠标将行高根据你要求的行距调整到适当高度即可。注：绿色内容为关键点，很多人就是这一点设置不对而无法调整行间距四十二：在同一工作薄中具有相同格式的多工作表的汇总求和方法（

21、例 42）方法：=SUM(Sheet1!A1:Sheet1!F14), (Sheet2!A1:Sheet2!F14),(Sheet3!A1:Sheet3!F14)四十三：在不同工作薄中的求和方法( 例 43.1， 例 43.2)方法：在例 43.2.xls 中输入 =SUM(A1:E11,例 43.1.xlsSheet1!$A$1:$F$14)九、财务上的的使用例 91、九九乘法表（例）2、有效性应用（例）方法：首先选中要填写员工部门的所有单元格，点击功能区“数据”“有效性” ，打开“数据有效性”对话框。在“允许”下方的下拉列表中选择“序列”选项，然后在下面“来源”输入框中输入各部门名称（人事部,一车间 ,二车间, 生产部, 技术部,办公室） ，部门之间用英文的逗号隔开即可3、同事小张为了买新房准备到银行贷款 30 万元，商定 20 年还清。如果用等额本息法，会把贷款总额的本息之和平均分摊到整个还款期，按月等额还款；其用法是 PMT(月利率,还款期限,贷款总额) ，非常简单。所以，我们只要如图 1 所示表格的 B5 单元格中，输入公式“=PMT(B2/12,B3,B4)” ，回车后就可以得到按等额本息法计算的每月还款额，其中第一个参数“B2/12”是为了得到月贷款利率。再在 B6 单元格输入公式 “=B5*B3”，回车就可以轻松得到 20年的还款总额了。 （例）