2020年职场办公必会Excel快捷功能操作步骤19讲 (17)

目录 第17课： 宏表函数应用技术（上） 86、 宏表函数GET.CELL 87、 使用定义名称应用宏表函数 88、 给宏表函数名称添加自动重算功能 89、 按单元格背景颜色统计数据 90、 按字体颜色统计数据 91、 按单元格格式统计数据86宏表函数GET.CELLGET.CELL函数用于按指定要求提取单元格的信息。语法结构：GET.CELL（信息类型，参照单元格）信息类型：1至66的数字，用于按照指定要求提取单元格的哪种信息参照单元格：指明待提取信息的单元格的所在位置，注意与公式所在单元格的相对位置注意事项：1、GET.CELL的第一参数有66种信息类型，我背不过怎么办？不需要背过所有信息类型，只要你把最经典的几种用法对应的信息类型数字记住即可；其余的功能只需了解后心里有个印象，需要用的时候打开课件查阅信息类型数字即可，就跟平时我们查字典一样。2、我想提取单元格中某种信息时，一定要用GET.CELL吗？不一定，因为随着Excel版本的更新，很多功能逐渐被新增的Excel工作表函数所替代，能用Excel内置工作表函数解决的问题就不再需要使用GET.CELL宏表函数了。3、宏表函数与工作表函数谁的运算速度更快？工作表函数速度更快，宏表函数计算相对要慢，尤其是当文件中存在大量宏表函数时，可能会拖慢报表公式的更新速度。4、宏表函数需要在宏表中运行，而我的报表在工作表里用不了宏表怎么办？可以借助定义名称使宏表函数在工作表中正常使用，在下一小节中会有专门讲解。86-2 信息类型 说明 1 返回参照单元格绝对引用地址 2 返回参照单元格行号（数字） 3 返回参照单元格列号（数字） 4 返回参照单元格数据类型：数值1，空单元1，文本2，逻辑4，错误值16 5 返回参照单元格内容 6 返回参照单元格公式或值 7 返回参照单元格数字格式 8 返回参照单元格水平对齐方式：常规1靠左2居中3靠右4填充5两端对齐6跨列居中7分散对齐8 9 返回参照单元格左边框线的类型：无0细线1中等线2虚线3点线4粗线5双线6发丝线7中等虚线8点划线9中等点划线10双点划线11中等双点划线12花式线13 10 返回参照单元格右边框线的类型 11 返回参照单元格上边框线的类型 12 返回参照单元格下边框线的类型 13 返回参照单元格填充图案样式：无0实心1，50%灰色2，75%灰色3，25%灰色4，水平条纹5，垂直条纹6，逆对角线条纹7，对角线条纹8，对角线剖面线9，粗对角线剖面线10，细水平条纹11，细垂直条纹12，细逆对角线条纹13，细对角线条纹14，细水平剖面线15，细对角线剖面线16，12.5%灰色17，6.25%灰色18 14 返回参照单元格是否锁定状态：是TRUE，否FALSE 15 返回参照单元格是否隐藏公式：是TRUE，否FALSE 16 返回参照单元格列宽 17 返回参照单元格行高 18 返回参照单元格首字符的字体名称 19 返回参照单元格首字符的字号磅值 20 返回参照单元格首字符是否粗体：是TRUE，否FALSE 21 返回参照单元格首字符是否斜体：是TRUE，否FALSE 22 返回参照单元格首字符是否单下划线：是TRUE，否FALSE 23 返回参照单元格首字符是否删除线：是TRUE，否FALSE 24 返回参照单元格首字符的字体颜色编号数字（1至56），如果字体颜色为自动生成，返回0 25 返回参照单元格首字符是否空心体：是TRUE，否FALSE。（用于ExcelforMac，ExcelforWindows不支持空心字体） 26 返回参照单元格首字符是否阴影格式：是TRUE，否FALSE。（用于ExcelforMac，ExcelforWindows不支持阴影字体） 27 返回参照单元格手动分页状态：无0，上方1，左侧2，左侧和上方3 28 返回行的级数（分级显示） 29 返回列的级数（分级显示） 30 返回参照单元格是否位于分级列表的汇总行：是TRUE，否FALSE 31 返回参照单元格是否位于分级列表的汇总列：是TRUE，否FALSE 32 返回"[book1.xlsm]sheet1"形式的工作表名称。 33 返回参照单元格是否自动换行状态：是TRUE，否FALSE 34 1~16之间的一个数字，代表左侧边框线颜色。如颜色自动生成，返回0 35 1~16之间的一个数字，代表右侧边框线颜色。如颜色自动生成，返回0 36 1~16之间的一个数字，代表顶端边框线颜色。如颜色自动生成，返回0 37 1~16之间的一个数字，代表底端边框线颜色。如颜色自动生成，返回0 38 当图案为实心时，返回参照单元格背景色编号；其他时候返回图案前景色编号 39 当图案为实心时，返回参照单元格图案前景色编号；其他时候返回图案背景色编号 40 返回参照单元格样式名称 41 不经翻译返回参照单元格的公式（某些语言版本的Excel函数名称与英文版不同） 42 返回参照单元格左边界相对窗口左边界的偏移。如果窗口滚动到单元格外，可能为负值。 43 返回参照单元格上边界相对窗口上边界的偏移。如果窗口滚动到单元格外，可能为负值。 44 返回参照单元格右边界相对窗口左边界的偏移。如果窗口滚动到单元格外，可能为负值。 45 返回参照单元格下边界相对窗口上边界的偏移。如果窗口滚动到单元格外，可能为负值。 46 返回参照单元格是否包含批注：是TRUE，否FALSE 47 返回参照单元格是否包含声音批注：是TRUE，否FALSE 48 返回参照单元格是否包含公式：是TRUE，否FALSE 49 返回参照单元格是否包含数组公式：是TRUE，否FALSE 50 返回参照单元格垂直对齐方式：靠上1，居中2，靠下3，两端对齐4，分散对齐5 51 返回参照单元格文字方向：水平0，垂直1，向上(90度)2，向下(-90度)3，其他4 52 返回参照单元格前缀字符或对齐方式。若"Lotus1-2-3常用键"功能关闭，则只有'（撇号）这一种前缀，也就是强制文本型。当"Lotus1-2-3常用键"功能打开，有三种前缀：^表示居中，"（引号）为靠右，其他都是'（撇号）；如果单元格不包括任何内容，代表空文本(“”) 53 返回参照单元格的显示值 54 返回参照单元格所在的数据透视表名，如不在透视表中则返回#N/A 55 返回0~8的数字，代表参照单元格在数据透视表中的位置，如不在透视表中则返回#N/A 56 在数据透视表视图中，返回包含活动单元格引用的字段名称。 57 返回参照单元格首字符是否为上标：是TRUE，否FALSE 58 返回参照单元格首字符的字形，如常规、倾斜、加粗等 59 返回参照单元格首字符的下划线类型：无1，单下划线2，双下划线3，会计用单下划线4，会计用双下划线5 60 返回参照单元格首字符是否为下标：是TRUE，否FALSE 61 返回活动单元格在数据透视表中的项目名，活动单元格不在透视表中则返回#N/A 62 返回带工作簿名称的工作表名 63 返回单元格的填充(背景)颜色 64 返回单元格的图案(前景)颜色 65 返回两端分散对齐状态 66 返回工作簿名称86-3 经典信息类型 说明 6 返回参照单元格公式或值 7 返回参照单元格数字格式 24 返回参照单元格首字符的字体颜色编号数字（1至56），如果字体颜色为自动生成，返回0 62 返回带工作簿名称的工作表名 63 返回单元格的填充(背景)颜色 64 返回单元格的图案(前景)颜色 66 返回工作簿名称87使用定义名称应用宏表函数为什么要用定义名称？由于宏表函数无法在工作表中直接使用，而我们大多数工作都在工作表中完成，所以需要借助定义名称扩展宏表函数的适用范围。注意事项：1、注意定义名称时的活动单元格与参照单元格的相对位置在定义名称前要想好在哪输入公式，引用哪里的参照单元格，定义时在引用位置中注意相对位置关系。2、注意定义名称时绝对引用、相对引用、混合引用的灵活使用这四种引用方式的灵活使用，在第二期特训营（函数初级班）精讲过，注意区分和灵活使用。3、注意定义名称适用范围，以及多工作表名称的灵活写法如需多张工作表内都需使用的定义名称，注意定义工作簿范围且引用位置中不带工作表名的定义名称。4、注意定义名称的命名规范第二章第6课和第7课中专门讲过名称的基础知识及管理和使用方法，如有模糊前往回顾。88给宏表函数名称添加自动重算功能为什么要给宏表函数添加自动重算功能？由于大多数宏表函数在工作表中无法自动重新计算，也就无法自动更新结果。怎样手动操作才能强制Excel执行重新计算？按<Ctrl+Alt+F9>组合键或者<Ctrl+Alt+Shift+F9>组合键。怎样给宏表函数添加自动重算功能？在定义名称时加入易失性函数，作用是借助易失性函数触发Excel自动重算，从而带动包含宏表函数的名称也重新计算：（1）定义名称计算结果为文本时，在公式后&T(NOW())（2）定义名称计算结果为数值时，在公式后+NOW()*0注意事项：及时给宏表函数添加了自动重算功能的定义名称，也要在更新报表后按下F9手动重算更新公式计算结果，避免失误。Excel中的四种手动重算方式：F9：重算当前工作簿中的所有工作表Shift+F9：重算当前工作表Ctrl+Alt+F9：强迫重算当前进程中的所有工作簿Ctrl+Alt+Shift+F9：重建关系链，强迫重算当前进程中的所有工作簿这四种重算的区别和联系：1、重算和强迫重算的区别在于，是否依赖公式更改而重算，后两种即使没改动过数据也会重算。2、关系链指的是与公式关联的所有其他公式、名称、单元格引用，第四种会重建关系链。3、如果一个关系链关联的单元格超过65536个，任何重算都会引发这个超长关系链的所有关联单元格全部重算。4、无论哪种重算，都会对当前进程内所有包含易失函数的工作簿重算。5、如果外部链接的工作簿没有打开，除了打开文件时的自动更新，重算不会引发自动更新。89 日期 销售额 公式 11/15/18 1 11/16/18 2 11/17/18 3 11/18/18 4 11/19/18 5 11/20/18 6 11/21/18 7 11/22/18 8 11/23/18 9 11/24/18 10 11/25/18 11 11/26/18 12 11/27/18 13 11/28/18 14 11/29/18 15 11/30/18 16按单元格背景颜色统计数据90 日期 销售额 公式 11/15/18 1 11/16/18 2 11/17/18 3 11/18/18 4 11/19/18 5 11/20/18 6 11/21/18 7 11/22/18 8 11/23/18 9 11/24/18 10 11/25/18 11 11/26/18 12 11/27/18 13 11/28/18 14 11/29/18 15 11/30/18 16按字体颜色统计数据91 日期 小费金额 公式 11/15/18 ¥1 11/16/18 ¥2 11/17/18 $3 11/18/18 $4 11/19/18 ¥5 11/20/18 ¥6 11/21/18 ¥7 11/22/18 $8 11/23/18 $9 11/24/18 ¥10 11/25/18 ¥11 11/26/18 ¥12 11/27/18 ¥13 11/28/18 $14 11/29/18 $15 11/30/18 $16按单元格格式统计数据