Excel中级培训教材

1、深圳市许继派尼美特电缆桥架有限公司 2022年3月6日星期日Excel 进阶培训教程进阶培训教程品质部：向泽林深圳许继大纲效果欣赏良好的Excel使用习惯单元格及工具栏常用快捷键Excel应用小技巧公式及简单函数的应用条件格式简单的数据处理与分析2022年3月6日星期日page 2 /静态图表的制作复杂函数的应用插入对象名称管理控件的使用宏/自定义快捷键图表的背景分割动态图表的制作深圳许继1.效果欣赏 条件格式 依不同颜色填充求和 抽奖器 自定义快捷键 宏 图表背景分割 不同的动态图表 多级录入选项 简单的工作提醒表 万年历2022年3月6日星期日page 3 /深圳许继2022年3月6日星期

2、日page 4 /2.良好的Excel习惯内部统一的标准化格式排版的规范化设置键盘功能快捷键使用函数的使用多人合作时需要适当的分工权限数据勾稽的检查养成经常思考的习惯深圳许继2022年3月6日星期日page 5 /3.1. 单元格格式设定A.设定单元格的属性:常规,文本,数值B.文本和数值属性的确定公式:TYPE()(数字 1 文本 2 逻辑值 4 ) 日期也表示为数字型日期也表示为数字型C.要强行输入文本型数据,在输入前先输入 D.文本和数值的互换的方式 1). “1”+0=11&”=“1”2). 数值转文本:=TEXT(B7,0.00) 2.00 =TEXT(B7,”0%”) 2%

3、 E.字体、字号、颜色的设定Information is Tyco Confidential & ProprietaryDo Not Reproduce or Distribute3.单元格及工具栏深圳许继2022年3月6日星期日page 6 /3.2. 单无格引用的方式 A.相对引用公式中的相对单元格引用（例如 A1）是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制公式，引用会自动调整。默认情况下，新公式使用相对引用。例如，如果将单元格 B2 中的相对引用复制到单元格 B3，将自动从 =A1 调整到 =A2。 B.绝对引

4、用 $单元格中的绝对单元格引用（例如 $A$1）总是在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引用保持不变。如果多行或多列地复制公式，绝对引用将不作调整。默认情况下，新公式使用相对引用，需要将它们转换为绝对引用。例如，如果将单元格 B2 中的绝对引用复制到单元格 B3，则在两个单元格中一样，都是 $A$1。3.单元格及工具栏深圳许继2022年3月6日星期日page 7 / C.混合引用混合引用具有绝对列和相对行，或是绝对行和相对列。绝对引用列采用 $A1、$B1 等形式。绝对引用行采用 A$1、B$1 等形式。如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。如果多行

5、或多列地复制公式，相对引用自动调整，而绝对引用不作调整。例如，如果将一个混合引用从 A2 复制到 B3，它将从 =A$1 调整到 =B$13.单元格及工具栏深圳许继2022年3月6日星期日page 8 / 3.3 单元格引用的分类u 一维引用：A1:A10、A2:H2(单行或者单列）u 二维引用：A1:C5(包含多行/多列的一个区域）u 三维引用：Sheet1:Sheet3!A1(包含多个工作表的区域） （也可以引用其它工作薄的区域）3.单元格及工具栏深圳许继2022年3月6日星期日page 9 / 3.4 工具栏u Excel选项设置u 自定义快速访问工具栏3.单元格及工具栏深圳许继2022

6、年3月6日星期日page 10 / 4.1 Ctrl+字母4.常用快捷键Ctrl+A全选Ctrl+I斜体Ctrl+B字体加黑Ctrl+K插入超链接Ctrl+C复制Ctrl+T创建表Ctrl+D向下复制内容Ctrl+N新建工作薄Ctrl+R向右复制内容Ctrl+O打开文档Ctrl+X剪切Ctrl+P打印Ctrl+V粘贴Ctrl+U下划线Ctrl+F查找Ctrl+S保存Ctrl+G定位Ctrl+W关闭工作薄Ctrl+H替换Ctrl+Z返回深圳许继2022年3月6日星期日page 11 / 4.2 ALT+数定4.常用快捷键2003(繁体版）2007版 2003(繁体版）2007版ALT+41405

7、ALT+41419ALT+41406【ALT+41420ALT+41407】ALT+41421ALT+41408ALT+41422ALT+41409ALT+41423ALT+41410ALT+41424ALT+41411ALT+41425ALT+41412ALT+174ALT+41413ALT+175ALT+41414ALT+176ALT+41415ALT+177ALT+41416ALT+178ALT+41417ALT+179ALT+41418深圳许继2022年3月6日星期日page 12 / 4.3 其它快捷键l Ctrl+; 输入当前日期l Ctrl+Shift+; 输入当前时间l Ctr

8、l+Shift+P 设置单元格式l Ctrl+Shift+L 建立/删除筛选l Shift+F3 插入函数l Ctrl+ + 插入行/列l Ctrl+ - 删除行/列l 4.常用快捷键 4.4 自定义快捷键（第14章讲解） 深圳许继2022年3月6日星期日page 13 /5. Excel应用小技巧5.1 怎样快速插入/删除行和列？ Ctrl+ +/-5.2 F4 的功能 (同Ctrl+Y，重复前一次操作，可能的情况下）5.3 在Excel中快速移动行或列 5.4 如何将按行排列的数据转换成按列排列？5.5 页面设定的使用（照相机功能）5.6 复制或移动整张工作表 5.7 怎样快速输入“”、“

9、”、“平方”、“立方”5.8 用下拉列表快速输入数据 深圳许继2022年3月6日星期日page 14 /5.9 在Excel中强制换行5.10 让Excel页脚设置的页码从第二页开始 5.11 快速实现Excel表格奇偶行交错着色5.12 利用弹出菜单可以迅速切换到任意工作表5.13 在Excel中制作打钩的方框 （第13章讲解）5.14 按Shift键 把Excel单元格区域转换为图片 5.15 快速实现整块数据的移动 5.16 在单元格中自动输入时间和日期 5. Excel应用小技巧深圳许继2022年3月6日星期日page 15 /5.3 .在Excel中快速移动行或列 在编辑Excel表

10、格时，有时需要调整表格行或列的位置。用下面的方法可以快速实现，以移动行为例，假设要把工作表的第3行移动到第10行，操作如下：单击第3行的行标选中第3行，把鼠标指向第3行的边缘，当指针变为双十字箭头时，按住“Shift”键，同时按住左键向下拖动鼠标。在拖动过程中，会出现一条虚线，当虚线到达第10行上边缘时，屏幕上会出现“10:10”的提示。此时，松开鼠标左键，即可完成行移动。 5. Excel应用小技巧深圳许继2022年3月6日星期日page 16 /5. Excel应用小技巧5.4 如何将按行排列的数据转换成按列排列？ 如何将按行排列的数据转换成按列排列: 选中要转换的数据，复制它，然后把光标

11、移到新数据的起始位置，点鼠标右键,选 ”选择性粘贴”,再选窗口中的 “转置” 选项,按确定就好了.深圳许继2022年3月6日星期日page 17 /5. Excel应用小技巧5. Excel应用小技巧5.5. 页面设定的使用（照相机功能） 深圳许继2022年3月6日星期日page 18 /5. Excel应用小技巧5.6.复制或移动整张工作表 1.打开目标工作簿，切换到包含要复制工作表的源工作簿。 2.右击该工作表的标签（如：sheet1），并在弹出的菜单中选择“移动或复制工作表”。 3.在“将选定工作表移至”下拉列表中，选择要复制到的工作簿，如要将选定工作表复制到新工作簿中，则单击下拉菜单中

12、的新工作簿。 4.勾选“建立副本”复选框，如果不选择此复选框的话，该工作表将被移动走而不是复制。深圳许继2022年3月6日星期日page 19 /5. Excel应用小技巧5.8 用下拉列表快速输入数据 要输入的数据已经在当前列的某一单元格， 在单元格内单击鼠标右键，出现图1， 选择“从下拉列表中选 择数据”； 出现图2，选择所要的数据；图图1图图2深圳许继2022年3月6日星期日page 20 /5. Excel应用小技巧5.9 在Excel中强制换行 ALT+回车5.10 让Excel页脚设置的页码从第二页开始（1） 1.单击“页面布局页面设置”，然后单击“页面”选项卡。 2.在“起始页码

13、”右侧的文本框中，将“自动”改为所需的工作表开始页的页码。 深圳许继2022年3月6日星期日page 21 /5.10 让Excel页脚设置的页码从第二页开始（2）（Excel2003) 5. Excel应用小技巧l单击菜单“文件页面设置”，选择“页眉/页脚”选项卡。l单击“自定义页脚”按钮，在中间的文本框中输入“&页码+1 ”，注意输入时不包括引号，且“+1”的后面有一空格。如果没有这个空格，首页的页码将显示为“11”的式样。依此类推，如果需要从第8页开始设置，则输入“&页码+7 ”即可。深圳许继2022年3月6日星期日page 22 /5.11 快速实现表格奇偶交错着色 5

14、. Excel应用小技巧执行“开始条件格式”，打开“条件格式”对话框。单击打开“新建规则”，选择“使用公式确定要设置格式的单元格”，在下方的文本框中输入公式“=MOD(ROW(),2)=1”。再单击“格式”按钮，打开“单元格格式”对话框，指定字体颜色和填充颜色。公式的作用是选出行号为奇数的行，凡符合此公式条件的单元格即采用所指定的颜色格式。按此方法，添加第二个条件公式“=MOD(ROW(),2)=0”，再按上面的方法指定第二种颜色。即凡选中区域中行号为偶数的行，将以此颜色填充。深圳许继2022年3月6日星期日page 23 /5.12 利用弹出菜单可以迅速切换到任意工作表 5. Excel应用

15、小技巧如果工作簿有太多的工作表，不能同时在屏幕上看到它们的名字，用滚动的方法切换工作表就比较麻烦了。右击左下角的工作表滚动箭头，如图，利用弹出菜单可以迅速切换到任意工作表。 深圳许继2022年3月6日星期日page 24 /5.14 按Shift键 把Excel单元格区域转换为图片 5. Excel应用小技巧Excel2003:首先选中需要复制成图片的单元格区域，然后按住Shift键，依次选择“编辑复制图片”命令，接着弹出“复制图片”窗口，选择“图片”单选项后点击“确定”按钮。这时选定的表格区域就已经被复制成图片了。再到需要使用这张图片的地方选择“粘贴”命令即可Excel2007打开自定义工具

16、栏，添加“复制为图片”工具，选 中要复制为图片 的区域，点击“复制为图片”工具，到其它地方粘贴； 注意：注意：如果选择“如打印效果”单选项，那么被转换为图片的数据区域是按照打印效果来处理的，比如设置有粗边框，那么粘贴出来的图片上就会有粗边框。”如屏幕所示”被转换为图片的区域是按照屏幕所显示的效果来所理深圳许继2022年3月6日星期日page 25 /5.15 快速实现整块数据的移动 5. Excel应用小技巧选 中需要移动的区域，将鼠标放在选区的边沿框线上，鼠标变成十字双箭头 时按下左键拖动区域到新的区域即可 5.16 在单元格中自动输入时间和日期 日期和时间的输入形式太麻烦，而且有时记不清楚

17、。请问有什么方法让系统自动输入时间和日期吗？ 答：可以执行以下方法来实现：选中需输入的单元格，直接按下“Ctrl+；”组合键可输入当前日期；如果直接按下“Ctrl+Shift+；”组合键即可输入当前时间；当然也可以在单元格中先输入其他文字然后再按以上组合键，如先输入“当前时间为：”再按下“Ctrl+ Shift+；”组合键，就会在单元格中显示“当前时间为：16：27”这样的结果。深圳许继2022年3月6日星期日page 26 /6. 公式及简单函数的应用6.1 公式 公式是对工作表中的数值执行计算的等式。公式以等号 (=) 开头。例如，在下面的公式中，结果等于 2 乘 3 再加 5。 =5+2

18、*3公式组成部份：函数 （函数：函数是预先编写的公式，可以对一个或多个值执行运算，并返回一个或多个值。函数可以简化和缩短工作表中的公式，尤其在用公式执行很长或复杂的计算时。）、引用、运算符 （运算符：一个标记或符号，指定表达式内执行的计算的类型。有数学、比较、逻辑和引用运算符等。）常量 （常量：不进行计算的值，因此也不会发生变化。例如，数字 210 以及文本“每季度收入”都是常量。表达式以及表达式产生的值都不是常量。）。深圳许继2022年3月6日星期日page 27 /运算符类型 ：算术、比较、文本连接和引用。6. 公式及简单函数的应用6.1 公式 算术算术含义含义比较比较含义含义文本连接文本

19、连接含义含义引用引用含义含义+加法=等于&将两个值串起来产生一个连续的文本值冒号区域运算符：生成两个引用之间所有单元格的引用-减法大于*乘法=大于等于%百分比=小于等于空格交集运算符；生成对两个引用共有的单元格的引用乘方不等 于深圳许继2022年3月6日星期日page 28 /6. 公式及简单函数的应用6.2 函数 函数的结构：函数的结构以等号 (=) 开始，后面紧跟函数名称和左括号，然后以逗号分隔输入该函数的参数，最后是右括号。 参数。参数可以是数字、文本、TRUE 或 FALSE 等逻辑值、数组 （数组：用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区

20、域共用一个公式；数组常量是用作参数的一组常量。）、错误值（如 #N/A）或单元格引用 （单元格引用：用于表示单元格在工作表上所处位置的坐标集。指定的参数都必须为有效参数值。参数也可以是常量 （常量：不进行计算的值，因此也不会发生变化。例如，数字 210 以及文本“每季度收入”都是常量。表达式以及表达式产生的值都不是常量。）、公式或其他函数。深圳许继2022年3月6日星期日page 29 /6. 公式及简单函数的应用6.3 简单常用函数的应用 p 求参数的和:SUM()p 求最大值:MAX()p 求最小值:MIN()p 求参数的平均值:AVERAGE()p 返回特定日期的序列号: DATE()p

21、 返回特定时间的序列号:TIME()p 返回当前日期和时间:NOW()p 返回内容格式:TYPE()深圳许继2022年3月6日星期日page 30 /6. 公式及简单函数的应用6.3 简单常用函数的应用 p 从左截取字符串:LEFT()p 从右截取字符串:RIGHT()p 从指定位置开始截取特定数目的字符:MID()p 返回文本字符串的长度:LEN()p 四舍五入:ROUND()p 将几个文本字符串合并:CONCATENATE(),&p 将数字的小数部分截去，返回整数:TRUNC()p 统计非空单元格:COUNTA()p 计算包含数字的单元格个数 :COUNT 函数深圳许继2022年3

22、月6日星期日page 31 /7. 条件格式条件格式的类型u使用双色刻度设置单元格格式u使用三色刻度设置单元格格式u使用色阶设置单元格格式u使用图标集设置单元格格式u对文件/数字/日期单元格设置格式u使用公式设置单元格格式 为符合条件的单元格指定格式，是一种视觉化管理数据的方法 深圳许继2022年3月6日星期日page 32 /8. 简单的数据处理与分析u 数据有效性设置u 排序和筛选u 假设分析u 分列u 行/列的组合u 分类汇总u 数据透视表深圳许继2022年3月6日星期日page 33 /9.静态图表的制作 1首先输入要制作图表的数据；2.选择要建立图表的区域3.在功能区选择“插入”，在

23、图表组选择适合自己需要的图表类型即可；4.依据自己的需求对图表格式进行修饰，如调整坐标轴、数据标签、背景设置、图例等等；深圳许继2022年3月6日星期日page 34 /10.复杂函数的应用 IFCOUNTIFSUMIFFREQUENCYMATCHLOOKUP与VLOOKUPOFFSETSUMPRODUCT深圳许继2022年3月6日星期日page 35 /10.复杂函数的应用 IF语法： IF(逻辑条件,值为真返回的结果,值为假返回的结果)例： IF(A160,”合格”,”不合格”)注意：在Excel2003里最多可以使用7级IF函数进行嵌套；而在Excel2007里最多可以使用64级IF函数

24、进行嵌套；IF(A289,A,IF(A279,B,IF(A269,C,IF(A259,D,F)Excel2003 IFExcel2003 IF函函数数超超过过7 7级怎么办级怎么办？深圳许继2022年3月6日星期日page 36 /COUNTIF10.复杂函数的应用 语法： COUNTIF(区域或单元格，条件) 区域内可包括数字或名称、数组或包含数字的引用。空值和文本值将被忽略。例： COUNTIF(A1:C10,”500”)计算区域中满足给定条件的单元格的个数。深圳许继2022年3月6日星期日page 37 /SUMIF10.复杂函数的应用 语法： SUMIF(区域或单元格，条件，符合条件后

25、要求和的单无格) 区域内可包括数字或名称、数组或包含数字的引用。 空值和文本值将被忽略。例：SUMIF(A1:C10,”500”,C1:C10)按给定条件对指定单元格求和。深圳许继2022年3月6日星期日page 38 /FREQUENCY语法： FREQUENCY(一个数组或数值的引用，区间数组）例：=FREQUENCY(A2:A10,B2:B4)计算数值在某个区域内的出现频率，然后返回一个垂直数。10.复杂函数的应用 注意：1.由于函数 FREQUENCY 返回一个数组，所以它必须以 数组公式的形式输入。返回的数组单元格比“区间数 组”多一个， 2.函数输入后选择返回的数组区域，按F2,

26、再按Ctrl+Shift+Enter,否则只有一个结果；深圳许继2022年3月6日星期日page 39 /MATCH语法： MATCH(查找的值，查找的区域，查找的类型 )例：=MATCH(“君雄”,B2:B4,0)返回在指定方式下与指定数值匹配的数组中元素的相应位置。10.复杂函数的应用 查找的类型为1,返回小于或等于”查找的值”的最大数值,查找的区域必须按升序排列查找的类型为0,返回等于”查找的值”的第一个数值,查找的区域任意排列查找的类型为-1,返回大于或等于”查找的值”的最小数值,查找的区域必须按降序排列如果省略”查找的类型”,则假设为1深圳许继2022年3月6日星期日page 40

27、/VLOOKUP语法：VLOOKUP(查找的值,查找的区域,返回的列号,匹配类型 )在表格数组的首列查找指定的值，并由此返回表格数组当前行中其他列的值。10.复杂函数的应用 u “查找的值”是需要在”查找的区域”第一列中查找的值u “匹配的类型”是逻辑值”真”或”假”，为“假”时，精确查找，如果有相同值时，则返回第一个找到的值；u 为“真”或省略时，近似查找，如果找不到精确匹配值，则返回小于 “查找值”的最大数值，需以升序排列，否则可能返回的值不正确深圳许继2022年3月6日星期日page 41 /OFFSET语法：OFFSET(基点,行数,列数,高度,宽度 )以指定的引用为参照系，通过给定偏

28、移量得到新的引用。10.复杂函数的应用 引用“基点”只能是单元格高度和宽度用来指定目标单元格区域的大小深圳许继2022年3月6日星期日page 42 /SUMPRODUCT参数为2-255个数组，参数必须具有相同的维数；否则返回错误值在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。10.复杂函数的应用 语法：SUMPRODUCT(数组1，数组2，数组3)深圳许继2022年3月6日星期日page 43 /根据身份证号提取出生日期根据身份证号提取出生日期 =IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2),IF(LEN(A1)=15,DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2), 错误身份证号 ) 10.复杂函数的应用 深圳许继2022年3月6日星期日page 44 /11.插入对象 选择功能菜单，“插入” “对象”深圳许继2022年3月6日星期日page 45 /12.名称管理 名称：代表单元格、单元格区域、公式或常量值的单词或字符串。名称更易于理解，例如，“产品”可以引用难于理解的区域“Sales!C20:C30”。）名称中包含哪些内容？我认为，我们用名称来称呼单元格区域、函数、常量或表