EXCEL_运用与技巧(2015-8-31单证用)

1、EXCEL运用与技巧11、快捷键 使用快捷键2操作操作EXCEL时时,按按ALT键键,就会显示出各就会显示出各选项卡的快捷键选项卡的快捷键按下某选项卡的快捷键字母按下某选项卡的快捷键字母后后,就会显示出该选项卡中各就会显示出该选项卡中各命令按钮的快捷键命令按钮的快捷键 按所指方向选取直到非空格的所有项。 在所选范围内，复制最上一栏的公式(效果于用鼠标拖动单格左下角的黑十字架) 选定单元格区域，输入值，能在不同单元格中输入同一内容。Ctrl+PgDn快捷键到下一个工作表Ctrl+PgUP快捷键到上一个工作表Ctrl+ 至连续区域的最一个单元格 wCtrl+Home快捷键是强迫回到最前一个单元格w

2、Ctrl+End快捷键到区域的结束处公式中引用方式的切换 在三种引用（相对引用、绝对引用、混合引用）间切换的方法：光标定位于单元格地址上，按F4键。 如C5，$C$5，C$5，$C5之间的切换快速选定特定区域 按F5，出现“定位”窗口，在“引用”栏内输入需选取的区域。nCTRL+Z：取消上一次操作nCTRL+;：快速输入日期=today()nCtrl+N ：新增工作簿 nCtrl+S：保存当前工作簿nCtrl+P ：打印当前工作表nCtrl+F：查询 nCtrl+H ：替换nCtrl+A：全选当前工作表nCtrl+X：剪切nCtrl+C：复制nCtrl+V：粘贴nCtrl+ 1：调出单元格格式

3、 Ctrl+复制上一单元格的内容 CTRL+TAB EXCEL活动窗口间切换 CTRL+BACKPAE 返回选中区域，例如把光标定位在一个地方（如C10），然后用鼠标滚轮向下滚几屏，这是如果想看刚才的C10，按ctrl+backpage TAB 单元格之间移动 ALT+TAB 切换不同窗口 CTRL+2 加粗 CTRL+3 斜体 CTRL+4 下划线n 当输入分数时，为了避免将输入的分数视为日期，可在分数前面添加空格。n 要输入作为文本处理的数字（如职工编号、学号、电话号码等）或者输入前面为0的数字，可输入一个单撇号“”，然后输入数字。92. 认识EXCEL函数函数公式选项板样式函数公式选项板

4、样式函数参数常用符号或表示方法10 公式中所有的运算符都必须在英文半角状态下输入公式中所有的运算符都必须在英文半角状态下输入 函数公式中的文本必须用半角引号，如：函数公式中的文本必须用半角引号，如：东南汽车东南汽车; ;而非直接输入而非直接输入 东南汽车或东南汽车或“东南汽车东南汽车” 连接符连接符: &: &如如：东南东南& & 汽车汽车的值为的值为 东南汽车东南汽车 空值空值/ /空格的表示空格的表示法法: : 空值：空值： 空格：空格： 相关数学符号相关数学符号:（不等于）（不等于）;=;=（大于等于）（大于等于）;=;=60，及格，不及格)利用IF函数避

5、免出错信息（1）当我们在C1单元格输入公式：=A1/B1，当B1单元格没有输入数据时，C1单元格则会出现“#DIV/0！”的错误信息。如果将公式改为：=IF（B1=0,A1/B1），就不会显示错误信息了。（2）如果我们在C1单元格中输入：=A1B1，当A1、B1没有输入数值时，C1则显示出“0”，如果将公式改为：=IF（AND（A1=，B1=）, ,A1B1），这样，如果A1与B1单元格均没有输入数值时，C1也不会显示 “0”了。日期函数日期函数17函数名称函数名称含义含义样式例举样式例举TODAYTODAY返回日期格式的当前日期=TODAY()WEEKDAYWEEKDAY返回代表一周中第几天

6、的数值（1-7分别代表星期日到星期六）=WEEKDAY(A1)NOWNOW返回日期格式的当前日期和时间=NOW()DATEDATE返回日期时间代码中代表日期的数字=DATE(YYYY,MM,DD)YEARMONTHDYEARMONTHDAYAY返回某日期的年份/月份/日期=YEAR(2008-01-05)DATEVALUEDATEVALUE数字转换日期格式=DATEVALUE(A1)用途：返回某日期的星期数。在默认情况下，它的值为1(星期天)到7(星期六)之间的一个整数。语法：WEEKDAY(日期，返回类型)参数：“返回类型 ”为确定返回值类型的数字， 1 1或省略：或省略： 数字数字1 1（

7、星期日）到数字（星期日）到数字7 7（星期六）。（星期六）。 2 2数字数字 ：1 1（星期一）到数字（星期一）到数字7 7（星期日）。（星期日）。 3 3数字数字 ：0 0（星期一）到数字（星期一）到数字6 6（星期日）。（星期日）。实例：公式“=WEEKDAY(”2008/8/20“，2)”返回3(星期三)，=WEEKDAY(“2008/8/20”，3)返回2(星期三)。文本函数文本函数函数名称函数名称含义含义样式例举样式例举TEXTTEXT将数值转换为按指定数字格式表示的文本=TEXT(a1,dbnum2)FINDFIND查询文本并引用所在位置(单元格A2中第一个“中”字的位置）=FIN

8、D(“中”,A2)REPLACEREPLACE替换指定位置字符(单元格A1中第三个字符起后的两个字符替换为“”）=REPLACE(A1,3,2,” ”)19查询与引用函数查询与引用函数函数名称函数名称含义含义样式例举样式例举INDEXINDEX在给定的区域内，返回特定行列交叉处的单元格的值=INDEX(A1:F10,2,3)OFFSETOFFSET以特定的引用为参考，通过给定偏移量返回新的引用=OFFSET(A1,1,2)20用途：返回表格或区域中的数值。语法：INDEX(单元格区域，行序号，列序号)返回数组中指定的单元格或单元格数组的数值。案例：题一：=INDEX（A1：C5，2，2）的返回

9、值是？题二：=INDEX（A1：A5，5，1）的返回值是？n用途：以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以是一个单元格或单元格区域（并不返回值）。n语法：OFFSET(作为偏移量参照系的引用位置，上下偏移的行数，左右偏移的列数，height，width)。n参数： 作为偏移量参照系的引用位置：它必须是单元格或相连单元格区域的引用； 上(下)偏移的行数：正数代表在起始引用下边;负数代表在起始引用的上边；0代表没有上（下）偏移。 左(右)偏移的列数:正数代表在起始引用右边;负数代表在起始引用的左边；0代表没有左（右）偏移。 Height：是要返回的引用区域的总行数（必须为正

10、数） Width：是要返回的引用区域的总列数（必须为正数）。数学函数数学函数函数名称函数名称含义含义样式例举样式例举SUMSUM计算单元格（区域）中所有数值的和=SUM(A1,C3)SUMIFSUMIF对满足条件的单元格求和（在A列中的值，凡是等于B1里的值时，自动求和C列中与A列相对应的行的值。）=SUMIF(A:A,B1,C:C)SUMPRODUCTSUMPRODUCT返回相应的数组或区域乘积的和=SUMPRODUCT(A:A)*(B:B)ROUNDROUND按指定的位数将数值四舍五入。如果为了避免excel公式运算受小数位的影响，可以在运算中通过此函数固定运算时的小数位=ROUND(A1

11、,2)23输入公式=SUMIF(A2:A13,D2,B2:B13)公式说明，指定求和条件为成都发货平台，然后再众多发货平台（条件区域）中计算出成都发货平台的总发货量（求和区域）。2022-2-2124统计函数统计函数函数名称函数名称含义含义样式例举样式例举AVERAGEAVERAGE返回其参数的算术平均值=AVERAGE(A1:A10)RANKRANK返回某数字在一列数字中相对于其他数值的大小排名=RANK(A1,A1:A10)MAXMAX（MINMIN）返回一组数值中的最大值（最小值）=MAX(A1:A10)25单元格格式单元格中的内容虽然只有主要的两大类：文本和数字（含时间、日期），但单元

12、格的格式却是十分丰富。26重点学习内容重点学习内容单元格类型设置单元格类型设置对齐对齐保护保护单元格格式单元格的自动换成行格式自动换行能够根据表格的列宽自动将单元格中的内容排成一行或多行，对于制订内容较多的工作表标题行时很有用。下图第2行的标题就是通过单元格的自动换行格式设计而成的。27单击它可设置或取消当前单击它可设置或取消当前选中单元格或区域的自动选中单元格或区域的自动换行格式换行格式单元格格式锁定与隐藏 锁定：工作表中选定的单元格区域不能被更改、移动、调整或删除； 隐藏：工作表中选定的单元格区域中的公式（包括函数）不会在编辑栏中显示。注意：“锁定”与“隐藏”操作必须先设置单元格格式中的保

13、护功能，且只有在实施工作表保护后才有效，顺序不能颠倒。28选择性粘贴复制与粘贴是EXCEL最为常用的操作方式之一，充分掌握选择性粘贴的奇妙运用，很多工作通过粘贴即可迎刃而解。29重点学习内容重点学习内容粘贴类型粘贴类型运算运算跳过空单元格与跳过空单元格与转置转置30选择性粘贴【全部】：包括内容、和格式等，其效果等于直接粘贴；【公式】：只粘帖文本和公式，不粘帖字体、格式（字体、对齐、文字方向、数字格式、底纹等）、边框、注释、内容校验等；【数值】：只粘帖文本，单元格的内容是计算公式的话只粘帖计算结果，这两项不改变目标单元格的格式；【格式】：仅粘贴源单元格格式，功能相当于格式刷；【批注】：把源单元格

14、的批注内容拷贝过来，不改变目标单元格的内容和格式；【有效性验证】：将复制单元格的数据有效性规则粘贴到粘贴区域，只粘帖有效性验证内容，其他的保持不变；【除边框外的所有内容和格式】：粘帖除边框外的所有内容和格式，保持目标单元格和源单元格相同的内容和格式；【列宽】：将某个列宽或列的区域粘贴到另一个列或列的区域，使目标单元格和源单元格拥有同样的列宽，不改变内容和格式；【公式和数字格式】：仅从选中的单元格粘贴公式和所有数字格式选项；【值和数字格式】：仅从选中的单元格粘贴值和所有数字格式选项；【所有使用源主题的单元】粘贴使用复制数据应用的文档主题格式的所有单元格内容。粘贴方式区域各项功能介绍：31选择性粘

15、贴运算方式区域各项功能介绍：【无】：对源区域，不参与运算，按所选择的粘贴方式粘贴；【加】：把源区域内的值，与新区域相加，得到相加后的结果；【减】：把源区域内的值，与新区域相减，得到相减后的结果；【乘】：把源区域内的值，与新区域相乘，得到相加乘的结果；【除】：把源区域内的值，与新区域相除，得到相除后的结果（此时如果源区域是0，那么结果就会显示#DIV/0!错误）；特殊处理设置区域各项功能介绍：【跳过空白单元格】：当复制的源数据区域中有空单元格时，粘贴时空单元格不会替换粘贴区域对应单元格中的值；【转置】：将被复制数据的列变成行，将行变成列。源数据区域的顶行将位于目标区域的最左列，而源数据区域的最左

16、列将显示于目标区域的顶行；【粘帖链接】：将被粘贴数据链接到活动工作表。粘贴后的单元格将显示公式。32选择性粘贴33选择性粘贴筛选功能Excel中提供了两种数据的筛选操作，即“自动筛选”和“高级筛选”，通过筛选时将不满足条件的数据暂时隐藏起来，只显示符合条件的数据。34重点学习内容重点学习内容自动筛选自动筛选自定义筛选自定义筛选高级筛选高级筛选筛选功能35数据-高级筛选1、定制多条件筛选2、去除重复数据查找与替换查找与替换是数据处理过程中经常执行的操作，在Excel中除了可查找和替换文字外，还可查找和替换公式和附注，其应用更为广泛，进一步提高了编辑处理的效率。36重点学习内容重点学习内容模糊查找

17、模糊查找精确查找精确查找替换替换有效性利用Excel数据菜单中的有效性功能可以控制一个区域内的数据类型、范围等，其实利用它还可以快速、准确地输入一些数据。37重点学习内容重点学习内容限定输入限定输入出错提示出错提示序列序列条件格式单元格条件格式充分体现了EXCEL在数据呈现方面的强大功能，眼花缭乱的数据往往遮人耳目，通过条件格式，清晰呈现数据，帮您提升商业洞察力。38重点学习内容重点学习内容突出数值呈现突出数值呈现显示重复数据显示重复数据20072007新功能新功能条件格式40条件格式41多窗口查看数据（1）单击“视图”“窗口”组中“新建窗口”按钮（2）单击“视图”“窗口”组中“并排查看”按钮

18、若不满意“并排查看”，可以选择其它查看方式。42多窗口查看数据（1）单击要进行窗口分隔的单元格位置（2）单击“视图”“窗口”组中“拆分”按钮若不满意“拆分”，可以拖动分隔线重新定位分隔位置。43多窗口查看数据（1）单击要冻结行下边，要冻结列右边的交叉点单元格。（2）单击“视图”“窗口”组“冻结窗格”，然后在弹出的菜单中选择“冻结拆分窗格”。44分类汇总45对于需要进行分类汇总的数据库，要求该数据库的每个字段都有字段名，也就是数据库的每列都有列标题。另外，在对某一列进行分类汇总时，应该先对该列进行排序，这样对该列进行的分类汇总，究会按一定的次序给出排列结果，否则会出现偏离预期的结果。分类汇总46

19、分类汇总47 多次分类汇总Excel可以对同一分类进行多重汇总，若要在同一汇总表中显示两个以上的汇总数据，只需对同一数据清单进行两次不同的汇总运算。第二次分类汇总在第一次的汇总结果上进行。（在前面的汇总结果基础上，统计每部门的人数。）单击-插入-对象由文件创建-选择要插入的文件确定即可点击显示为图标，就会以小图标的形式显示在文件中详细操作可参考以下网址http:/ word文件也可以用同样的方式插入文件2022-2-2149数据透视表数据透视表的功能：对大量数据的快速汇总、建立交叉列表的交互式表格（多维表格）；可以转换行和列以查看源数据的不同汇总结果；可以显示不同页面以筛选数据；可以根据需要显示区域中的明细数据。50数据透视表准备工作51制作数据透视表的数据区域必须是数据清单数据区域第一行为列标题列标题不能重名数据区域中不能有空行和空列数据区域中不能有合并单元格每列数据为同一种类型的数据整理数据区域修改非法日期把文本型数字转换为纯数字删除数据区域内的所有空行和空列取消合并单元格并进行填充去掉字符串前后的空格删除数字中的特殊字符删除不必要的小计行和总计行将二维表格整理为数据清单等等数据透视表制作521.Execl中点击菜单栏中数据项2.选择数据透视表3.弹出对话框,选择数据来源，点击下一步4.选择需要处理的数据5.选择数据透视表