宏表函数是个“老古董”,实际上是现在广泛使用VBA的“前身”。虽然后来的各版本已经不再使用它,但还能支持。宏表函数很奇葩,必须定义名称才能使用,脱离了名称就没有存在的价值。 1.excel宏表函数大全之一:EVALUATE宏表函数 EVALUATE用于统计引用单元格中以文本形式表示的算术表达式的值。 举例: 下表中G列数据为包裹的长宽高数据,现在需要根据G列数据统计包裹体积。 第一步:打开【名称管理器】新建一条名称记录如下: 第二步:单击【确定】关闭名称管理器,在H2单元格输入=体积,向下填充即可。 2.excel宏表函数大全之二:get.cell宏表函数 有些人喜欢将需要统计的数据用背景色填充,以为这样比较醒目,统计起来就方便。当然前提是你用计算器统计,如果用Excel那就是自找麻烦。 现在对需要统计的津贴用不同背景色填充,要分别统计两种颜色的津贴总数。 Step 01选择C2单元格,单击“公式”选项卡中的“定义名称”按钮,在“名称”文本框中输入:颜色,“引用位置”输入下面的公式,最后单击“确定”按钮。
Step 02 定义完名称后,只需在C2单元格输入公式,并向下填充公式,即可自动统计表达式,有背景色的就大于0,没有背景色的就是0。 橙色的背景色为46,所以公式可设置为:
绿色的背景色为10,所以公式可设置为:
如果背景色多的话,需要再次定义一个名称作为辅助列,获取D列的颜色对应值。 除了背景色,常用的还有一个字体颜色,参数1为24代表字体颜色。
GET.CELL参数1可以设置成1-66,共66种,有兴趣的可以去测试。 知识扩展: 高版本的Excel可以按背景色进行筛选,再借助SUBTOTAL函数就可以按背景色筛选求和。 在C17输入公式:
筛选橙色的背景色,就可以统计出来。不过这种有一个局限性就是只能按筛选的颜色进行统计,不能分别统计各种颜色。 3.excel宏表函数大全之三:get.workbook宏表函数 函数语法为get.workbook(type_num,name_text),即提取工作表信息,参数type_num表示提取的类型编号,name_text表示是打开的工作表名称,如果省略则表示当前活动工作簿 参数type_num包含的代码较多,我们主要使用的是1,表示“正文值的水平数组,返回工作簿中所有工作表的名称” 举例: 下表是某公司产品型号明细表,汇总表A列是工作表名称,现在需将工作表名称提取放置在A列。 第一步:单击【公式】选项中的【名称管理】。 第二步:单击【新建】打开【新建名称对话框】,输入名称以及引用位置。 第三步:单击【确定】后我们可以在【名称管理器】中看到刚刚添加的一条记录,单击关闭。 第四步:在汇总表A2单元格中输入函数公式=INDEX(名称,ROW(A1)),通过INDEX引用之前定义的宏函数。ROW(A1)目的是为了INDEX函数的第二个参数随之向下填充而变化,这样我们就能依次提取第1、2、3、4……N个工作表的名称。 我们还可以通过=TRANSPOSE(名称)公式来完成。选中A2:A10单元格区域后输入=TRANSPOSE(名称): 然后使用数组公式快捷键ctrl+shift+enter即可完成提取。 4.excel宏表函数大全之四:GET.FORMULA函数 GET.FORMULA作用是返回引用单元格内的公式。 函数语法:GET.FORMULA(reference),reference:指定引用的单元格。
举例:
上图是某员工通过函数公式计算包裹体积。现在需要将K列的公式提取出来并以文本形式显示。 第一步:选中K列数据区域,打开【名称管理器】,新建一个MM的名称记录,引用位置为: =GET.FORMULA(GET.FORMULA!$K$2:$K$10) 第二步:单击【确定】关闭名称管理器后L2单元格中输入=MM即可显示K2单元格中所使用的函数公式。 大家看到公式中的[RC-4]是不是有点疑问?其实这是单元格引用的另一种表示形式——R1C1形式,R后面的数字表示行数,C后面的数字表示列数。 不加“[]”的数字表示的是从第一行(列)数起的第几行(列); 加“[]”的数字表示从公式所在单元格算起,行位置向上或下移动的行数,列位置向左或向右移动的列数。 比如: rc[-3]*rc[-2]表示当前单元格(公式所在单元格)向左移动3格所在单元格的数值,乘以当前单元格向左移动2格所在单元格的数值。 本例中的RC-4表示K2单元格向左数第四列。 6.excel宏表函数大全之五:GET.DOCUMENT宏表函数 GET.DOCUMENT用于按照指定信息类型返回名称。 GET.DOCUMENT函数语法: GET.DOCUMENT(type_num, name_text),type_num:指明信息类型的数字,一共有88中数字代码表示88种类型。 通常使用GET.DOCUMENT(76)和GET.DOCUMENT(88)来返回活动工作表和活动工作簿的文件名。 举例: 第一步:打开【名称管理器】新建一条名称记录。我们设置名称为MC,引用位置为=GET.DOCUMENT(76),单击【确定】后关闭【名称管理器】。 第二步:在任意单元跟中输入=MC后即可返回当前工作表名称。
7.excel宏表函数大全之六:FILES宏表函数 FILES宏表函数的作用是返回指定目录下的文件名,FILES宏表函数以一维数组的形式返回结果。 举例: 我们现在要返回本计算机C盘下的所有文件名称。 第一步:打开【名称管理器】,【新建名称】对话框中输入名称为CP,引用位置为:=FILES('C:\*.*')。其中'C:\*.*'就表示路径C盘下的所有带后缀的文件,星号通配符表示所有,不包含文件夹。 第二步:关闭【名称管理器】,在A1单元格输入=INDEX(CP,ROW(A1))后向下填充。 与C盘文件对比完全一致。 宏表函数本质也是函数,只是多了一步定义名称而已,别把它想得太复杂。其实普通函数也可以通过定义名称,让公式更容易解读。 |
|