大家好,欢迎来到IT知识分享网。
EFunction高级函数插件,让低版本Excel(2019、2016、2013等低版本Excel)也能够使用XLOOKUP等365高级版本Excel才能够使用的函数了。
以下函数均为EFunction对标365新增函数,最大程度实现365才有函数功能,使得Excel低版本(2016及以下版本Excel)也能够使用这些高级函数。
这些函数均为模拟实现,可能没有考虑到所有应用场景差异,如果使用过程之中发现有不一致的地方,欢迎通过社区方式反馈,以便于后期修复。
EFunction对标函数,函数名和365高级函数保持同名,如果在365等高级版本之中使用EFunction,这些函数将会有两个相同的函数名,给使用造成困扰。如果不想使用默认函数名,可以以此点击菜单:EFunction-》函数重定义,打开函数重命名面板。EFunction默认已经将这些同名函数前添加“EF”前缀,也可以自己重新命名,可以在面板内启动这些函数的别名。需要改名的函数完成改名后,点击“更新”。重启Excel即可生效。
图2 函数重命名
在低级版本,例如2016版本编写好的EFunction对标函数,在365等高级版本之中打开时,高级版本Excel会提示函数名无效,需要手动激活下,函数才能够被高级版本Excel识别并转化为365同名函数。如果365版本Excel安装了EFunction插件,则能够被正常识别EFunction同名函数。
如果365等高级版本编写好的的高级函数(Excel自带函数),在低级版本Excel之中打开时,所有的函数名前都会有“_dfunc”前缀,需要将这些前缀去除,才能够被EFunction对标函数识别。
2.60 XLOOKUP函数教程
2.60.1 函数基本参数
- XLOOKUP 函数功能为,实现数据匹配功能,该函数为VLOOKUP函数的升级版本,相比VLOOKUP函数,该函数可以实现数据逆向匹配,并对二分查找进行了优化。
- 函数参数:参数1:lookup_value 待查找的数据值;参数2:lookup_array 要搜索的数区域或者数组;参数3:return_array,返回数组,如果参数2之中有符合条件的数,则返回该数组对应位置的数据;参数4:if_not_found,如果函数没有找到符合条件的数据,则返回该数;参数5:match_model 匹配模式:0值,精确匹配,如果未找到符合条件数据,则返回#N/A;-1值,精确匹配,如果未找到,则返回下一个较小值;1值,精确匹配,如果未找到,则返回下一个较大值;2值,通配符匹配,主要应用在文本数据匹配场合“?”和“*”分别表示,匹配一个字符或多个字符;参数5:search_mode搜索模式,搜索模式:1值,表示从上到下,从左到右顺序搜索;-1值,表示从下到上,从右到左,逆向搜索;2值,二分搜索,升序排序;-2值,二分搜索,降序排序。
2.60.2 应用案例
XLOOKUP相比VLOOKUP函数,函数参数改变较大。VLOOKUP函数第二个参数被拆分成两个参数。XLOOKUP灵活性更大。例如想查找“09班”工资,完整公式为:=XLOOKUP(I1,A2:A16,C2:C16,””,0,1),该公式能够查找从上到下第一个“09班”的工资信息。
图2.60.1 XLOOKUP函数正向精确查找
如果想实现逆向查找,只需要将第六个参数,既查找模式改为1即可,完整公式为:=XLOOKUP(I1,A2:A16,C2:C16,””,0,-1)。
图2.60.2 数据逆向匹配
XLOOKUP函数也能够实现二分查找,实现二分查找只需要将查找模式参数改为2即可,完整公式为:=XLOOKUP(I1,A2:A16,C2:C16,””,1,2)。需要注意二分查找前需要先对第二个参数进行排序,否则返回结果可能不是你想要的结果。参数2表示数据按照升序二分查找。
图2.60.3 二分查找
如果数据想按照降序二分查找,只需要将查找模式,改为-2即可,完整公式为:=XLOOKUP(I1,A2:A16,C2:C16,””,-1,-2)。
图2.60.4 降序二分查找
通过观察可以发现,VLOOKUP函数第四个参数含义在XLOOKUP函数内被拆解为两个参数,分别控制,被拆解后,增加了函数的灵活性,同时也增加了函数的使用难度,大家需要对比分析下,才能掌握。
2.60.3 注意事项
XLOOKUP函数第二、第三个参数维数必须相同,如果维数不相同可能会报错。
XLOOKUP第二个参数必须为一维数组,如果是二维数组函数会报错。
XLOOKUP第三个参数,可以为多维数组。
图2.60.5 第二参数为多维数组
2.61 XMATCH函数教程
2.61.1 函数基本参数
- XMATCH函数功能和XLOOKUP函数一样,区别是XLOOKUP函数直接提取匹配结果值,XMATCH函数功能为提取匹配结果所在位置。通过对比参数,可以发现二者函数用法几乎一样。因为XMATCH只提取了匹配结果的位置,不需要将匹配结果提取出来,因此相比XLOOKUP函数,该函数少了两个参数,分别为,return_array和if_not_found。
- 函数参数:参数1:lookup_value 待查找的数据值;参数2:lookup_array 要搜索的数区域或者数组;参数3:match_model 匹配模式:0值,精确匹配,如果未找到符合条件数据,则返回#N/A;-1值,精确匹配,如果未找到,则返回下一个较小值;1值,精确匹配,如果未找到,则返回下一个较大值;2值,通配符匹配,主要应用在文本数据匹配场合“?”和“*”分别表示,匹配一个字符或多个字符;参数4:search_mode搜索模式,搜索模式:1值,表示从上到下,从左到右顺序搜索;-1值,表示从下到上,从右到左,逆向搜索;2值,二分搜索,升序排序;-2值,二分搜索,降序排序。
2.61.2 应用案例
例如想查找“09班”工资,完整公式为:=XMATCH(I1,A2:A16,0,1),函数会返回“09班”第一位匹配位置3。再配合index函数,就可以实现XLOOKUP函数效果,完整公式为:=INDEX(C2:C16,XMATCH(I1,A2:A16,0,1),1)。
图2.61.1 匹配结果位置
图2.61.2 配合index函数实现XLOOKUP函数效果
2.61.3 注意事项
函数XMATCH注意事项和XLOOKUP函数相同。
2.62 RANDARRAY函数教程
2.62.1 函数基本参数
- RANDARRAY 函数功能为生成随机数组,可以理解为RAND函数的升级版。
- 函数参数:参数1:rows 生成随机数组的行数;参数2:columns 生成随机数组的列数;参数3:min 随机数组的下限值;参数4:max 随机数组的上限值;参数5:integer 是否显示整数形式。
2.62.2 应用案例
函数可生成随机数组,默认情况下生存随机小数,需要注意生存随机数后,将随机数值粘贴,样例完整公式为:=EFRANDARRAY(5,6,1,10,false)。
图2.62.1 生成随机数
只将最后一个参数改为TRUE,函数将返回最小值和最大值之间随机整数,完整公式为:=EFRANDARRAY(I2,J2,K2,L2,TRUE)。
图2.62.2 生成随机整数
2.62.3 注意事项
EFRANDARRAY最小值参数必须比最大值参数小。
当第5个为取整模式时,最小值和最大值必须为整数。
2.63 MAXIFS函数教程
2.63.1 函数基本参数
- MAXIFS 函数功能为,按条件求最大值,该函数可以理解为是IF+MAX函数组合函数。
- 函数参数:参数1:max_range 待求最大值的数值区域;参数2:criteria_range1 条件区域1,该参数可以为一行或者一列值;参数3:criteria1,条件值1。参数n:criteria_range_n/ criteria_n 条件n。
2.63.2 应用案例
案例需求,获取“09班”并且工资小于12000的最高工资,完整公式为:=MAXIFS(C2:C16,A2:A16,I1,C2:C16,”<“&J1)。
图2.63.1 条件最大值
2.63.3 注意事项
MAXIFS函数条件值必须成对存在。
MAXIFS函数max_range和criteria_range行数或者列数必须相同。
2.64 MINIFS函数教程
2.64.1 函数基本参数
- MINIFS 函数功能为,按条件求最小值,该函数可以理解为是IF+MIN函数组合函数。
- 函数参数:参数1:min_range 待求最小值的数值区域;参数2:criteria_range1 条件区域1,该参数可以为一行或者一列值;参数3:criteria1,条件值1。参数n:criteria_range_n/ criteria_n 条件n。
2.64.2 应用案例
函数求“09班”且工资大于12000的最低工资,完整公式为:=MINIFS(C2:C16,A2:A16,I1,C2:C16,”>”&J1)
图2.64.1 求“09班”小于大于12000最低工资
2.64.3 注意事项
MINIFS函数使用注意事项和MAXIFS函数相同,函数条件值必须成对存在。
MMINIFS函数max_range和criteria_range行数或者列数必须相同。
2.65 FILTER函数教程
2.65.1 函数基本参数
- FILTER 函数功能为条件过滤函数,该函数可以理解为Excel高级筛选功能的函数实现。
- 函数参数:参数1:array 待过滤的数组,该参数可以为Excel选区或者其他函数返回的数组数据或者常量数组;参数2:include 过滤条件,多个条件如果是并且关系,使用“*”拼接条件,如果是或的关系使用“+”条件;参数3:if_empty 如果结果为。
2.65.2 应用案例
案例需要过滤出来“09班”所有人员信息,如果未匹配到结果,使用空字符填充,完整公式为:=FILTER(A2:G16,A2:A16=I1,””)。FILTER过滤函数可以实现数据匹配效果,而且是数据多匹配,既有多少条记录,该函数都能够给你匹配出来。
图2.65.1 匹配出来09班所有的记录
如果想将“09班”并且工资大于12000的所有人员信息匹配出来,可以使用“*”将多个条件拼接起来,完整公式为:=EFFILTER(A2:G16,(A2:A16=I1)*(C2:C16>J1),””)
图2.65.2 多条件是用“*”进行拼接
2.65.3 注意事项
FILTER函数只能够按行对数据进行过滤,如果需要按列对数据进行过滤,可以使用TRANSPOSE函数进行转置,然后再对数据进行过滤。
FILTER第二个参数,虽然案例之中写成逻辑判断的形式,其实第二个参数本质上是逻辑值。例如下图第二个参数选中H2:H16逻辑值时,数据前三行被过滤出来。
图2.65.3第二个参数本质为逻辑值
2.66 TEXTJOIN函数教程
2.66.1 函数基本参数
- TEXTJOIN 函数功能为,使用分割符对数据进行合并拼接。
- 函数参数:参数1:delimiter 文本拼接的分割符(拼接符),默认缺省使用空字符拼接;参数2:ignore_empty 是否忽略空单元格,默认TRUE表示忽略空单元格,FALSE表示空单元格参加数据拼接;参数3:text1 拼接的数据1,该参数可以为常量数据、数组、Excel选区或者其他函数返回的数组数据;参数n:textn 拼接的数据n,可变参数,理论上最多有255个参数。
2.66.2 应用案例
TEXTJOIN函数拼接文本非常简单,拼接文本参数可以为多维数组或者Excel选区,案例完整公式为=TEXTJOIN(“-“,TRUE,B2:B16,D2:D16,E2:E16)。
如果拼接文本为多维数组,数据遍历方式为,逐行遍历。如果想逐列遍历,可以使用TRANSPOSE将拼接的数据进行转置,再用TEXTJOIN函数拼接文本。
图2.66.1 TEXTJOIN拼接文本
2.66.3 注意事项
TEXTJOIN函数拼接文本禁止出现错误类型数,如果有错误类型数据,函数会报错。
EFunction插件工具ETCombine函数也能够实现数据拼接,注意比较和TEXTJOIN函数区别。
2.67 SORT函数教程
2.67.1 函数基本参数
- SORT 函数功能为,对选区内的数据进行排序,该函数能够实现按行或者按列排序。
- 函数参数:参数1:array 待排序的数据区域,该参数可以为Excel选区或者其他函数返回结果数组;参数2:sort_index 排序索引号,如果按列排序,则是列号,如果按行排序,则是行号;参数3:sort_order,排序方式,数字1表示按升序排序,-1表示按降序排序。参数4:by_col 是否按列排序,默认TRUE按列排序,FALSE按行排序。
2.67.2 应用案例
函数按第1列和第二列排序,第1列升序,第2列升序,则完整公式为:=SORT(A2:G16,I1:J1,K1:L1,FALSE)。
sort_index参数可以为Excel选区,也可以是其他函数返回数组数据。参数3维度必须和参数2相同。
图2.67.1 按行排序
2.67.3 注意事项
SORT排序函数无特殊使用禁忌,这里需要对比下和EFunction工具ETSort排序徐函数区别。
2.68 SORTBY函数教程
2.68.1 函数基本参数
- SORTBY 函数功能为,根据指定的行或者行对数据排序,该函数相比SORT排序函数,使用更加灵活。
- 函数参数:参数1:array 待排序的数组数据;参数2:by_array 指定的排序行或者排序的列;参数3:sort_order 排序方式,1表示升序排序,-1表示降序排序;参数n:by_array_n/sort_order_n 第n个排序条件。
2.68.2 应用案例
根据班级和工资对数据进行排序,实现SROT排序函数同样效果,完整公式为:=EFSORTBY(A2:G16,A2:A16,1,C2:C16,-1)。通过对比可知,两个函数排序效果完全一样。
图2.68.1 根据第1列、第3列队数据排序
SORTBY 函数根据行对数据排序也非常直观,将样例数据转置后,对转置后的数据排序,完整公式为:=SORTBY(B18:P24,B18:P18,1,B20:P20,-1)。
图2.68.2 根据第1行、第3行对数据排序
2.68.3 注意事项
SORTBY函数排序的行或者列必须和数据区域相同,例如根据指定列排序,则指定列的行数必须和排序数据的行数相同。
SORTBY函数排序的行可以不在排序数据区域内,可以在排序数据区域外。
需要注意ETSortBy函数和SORTBY排序函数的区别。
2.69 ARRAYTOTEXT函数教程
2.69.1 函数基本参数
- ARRAYTOTEXT 函数功能为,将选区内的数据转化为文本,转化有两种模式,精简模式和严格模式。
- 函数参数:参数1:array 待转化的数据区域,该参数可以为Excel选区或者数组数据;参数2:format 转化方式默认“0”,表示精简模式,“1”表示精确模式,精确模式下,转化的文本数据为常量数组,可以用数组公式恢复成array数组;
2.69.2 应用案例
函数ARRAYTOTEXT 将左侧数据转化为常量数组形式,完整公式为:=ARRAYTOTEXT(A2:G16,1)。需要注意转化后的数据不能够有空单元格。
图2.69.1 将数组数据转化为常量数组文本
如果单元格没有空单元格,数据时可以恢复成数组公式形式。
={“09班”,”雷秀英”,11458.3831061942,”山西”,TRUE,”15㎡”,”合顺家园”;”02班”,”李丽”,9042.22802859722,”天津”,”5室3厅5卫”,TRUE,”合顺家园”;”09班”,”金平”,12777.701755979,”内蒙古”,”4室1厅2卫”,”180㎡”,TRUE;”09班”,”金平”,12777.701755979,”内蒙古”,”4室1厅2卫”,”180㎡”,TRUE;”01班”,”魏艳”,6682.07975569962,”江苏”,”4室1厅2卫”,”120㎡”,”海德花园”;”06班”,”仲敏”,13836.8376525414,”山西”,”6室2厅4卫”,280,”永安家园”;”08班”,”傅娟”,6005.58549209602,”湖北”,”2室2厅1卫”,92777,”绿岛家园”;”01班”,”酆桂英”,8668.34466926012,”广东”,”7室1厅3卫”,”300㎡”,”合顺家园”;”10班”,”俞平”,6439.97549533181,”新疆”,”2室1厅1卫”,”115㎡”,”深南花园”;”06班”,”贺明”,14470.4214470766,”江苏”,”4室1厅2卫”,”88㎡”,”北坞嘉园”;”10班”,”邹丽”,11458.3831061942,”新疆”,”1室2厅1卫”,”53㎡”,”祥祺花园”;”05班”,”董强”,6175.64637896457,”湖南”,”3室2厅2卫”,”28㎡”,”拱辰星园”;”07班”,”韶静”,6641.6820857269,”上海”,”5室2厅4卫”,”240㎡”,”佟馨家园”;”04班”,”水敏”,7308.19927690341,”湖南”,”2室2厅1卫”,”12㎡”,”大运家园”;”08班”,”丁强”,14687.1029986503,”山西”,”5室2厅2卫”,”268㎡”,”清景园”}
图2.69.2 将转化后的文本,恢复成数组数据
2.69.3 注意事项
函数ARRAYTOTEXT如果转化的数据之中有空单元格数据,转换后的文本数据,不能够恢复成原始数据形式。
2.70 SEQUENCE函数教程
2.70.1 函数基本参数
- SEQUENCE 函数功能为,生成等差序列数组。
- 函数参数:参数1:rows 等差序列数组的行数;参数2:columns 等差序列数组的列数;参数3:start 等差序列的开始至;参数4:step 等差序列步长。
2.70.2 应用案例
函数生成5行、6列,初始值为1,步长为-1的等差序列数组,完整公式为:=EFSEQUENCE(5,6,1,-1)。观察生成的等差序列数组,可以发现生成的数组逐行填充。如果希望生成逐列填充的等差序列数组,可以用TRANSPOSE函数,将数组数据转置实现。
图2.70.1 等差序列数组
2.70.3 注意事项
函数SEQUENCE无特殊使用禁忌。需要注意和EFunction函数ETSequence函数对比区别。
2.71 CONCAT函数教程
2.71.1 函数基本参数
- CONCAT 函数功能为,实现对文本数据拼接,CONCAT函数拼接逐行遍历数据,拼接数据之间无拼接符。
- 函数参数:参数1:text1 拼接数组数据1,该参数可以为Excel选区,也可以为其他函数的返回值;参数2:text_n 拼接数组数据,该参数可以为Excel选区,也可以为其他函数的返回值。
2.71.2 应用案例。
案例数据,将数据转化为文本数据,并拼接起来,完整公式为:=CONCAT(A2:B4,D6:E9,F11:G13),
图2.71.1 文本数据拼接
2.71.3 注意事项
函数CONCAT可以实现文本、数字和布尔类型数据拼接,如果数据之中存在错误类型数据,函数会返回错误类型数据,停止转化文本数。需注意该函数和TEXTJOIN函数的区别和联系。
2.72 VALUETOTEXT函数教程
2.72.1 函数基本参数
- VALUETOTEXT 函数功能为,将选取内数据转化为文本数据形式。
- 函数参数:参数1:value 待转化的文本数据,该参数可以为Excel选区,或者其他函数的返回值;参数2:format 转化格式,默认“0”精简模式,“1”精确模式。
2.72.2 应用案例
函数VALUETOTEXT将选区内的数据转化为文本格式,完整公式为:=VALUETOTEXT(A2:G16,FALSE),精简模式下结果数组所有数据都被转化为文本格式。
图2.72.1 精简模式
将第二个参数改为“TRUE”,原始数据之中,文本类型数据将被添加双引号,完整公式为:VALUETOTEXT(A2:G16,TRUE)。
图2.72.2 精确模式
2.72.3 注意事项
函数VALUETOTEXT无特殊使用禁忌。
2.73 UNIQUE函数教程
2.73.1 函数基本参数
- UNIQUE 函数功能为,提取选区内数据的唯一值。
- 函数参数:参数1:array 待提取唯一数据的数据区域,该参数可以为Excel选区或者其他函数返回的数组数据;参数2:by_col 是否按列提取唯一值,默认FALSE,按行提取唯一值,TRUE表示按列提取唯一值;参数3:exactly_onece,是否提取仅仅出现一次的唯一值,默认FALSE,提取所有数据的唯一值,TRUE表示提取仅仅出现一次的唯一值。
2.73.2 应用案例
按行提取数据的唯一值,完整公式为=UNIQUE(A2:G16,FALSE,FALSE),相当于数据去重。
图2.73.1 提取所有数据的唯一值
将最后一个参数该为TRUE,函数功能就变为将数据区域内两条以上相同记录的数据都被过滤掉,函数仅仅保留唯一值数据,完整公式为:=UNIQUE(A2:G16,FALSE,TRUE)。
图2.73.2 提取仅仅出现一次的唯一值
2.73.3 注意事项
UNIQUE为提升数据处理效率,禁止使用整行或者整列引用。
UNIQUE提取唯一值,会根据所有的列提取唯一值。EFunction工具ETUniqueBy函数,能够根据指定的列提取唯一值。可以对比下两个函数的差异。
2.75 TEXTSPLIT函数教程
2.75.1 函数基本参数
- TEXTSPLIT 函数功能为,文本分割函数,该函数能够指定行分割符和列分割符,对文本数据进行分割。
- 函数参数:参数1:text 待分割的文本数据;参数2:col_delimiter 列分割符;参数3:row_delimiter,行分隔符;参数4:ignore_empty是否忽略空单元格数据;参数5:pad_with,如果数据不足,则用该参数填充。
2.75.2 应用案例
以下文本案例,想将该数据分割成数组数据样式,完整公式为:=TEXTSPLIT(I1,”,”,”;”,FALSE,””),最终结果如下图所示。TEXTSPLIT函数成功将文本数据还原成原来数据数据方式。
09班,雷秀英,11458.3831061942,山西,2室2厅1卫,15㎡,拱辰星园;02班,李丽,9042.22802859722,天津,5室3厅5卫,300㎡,佟馨家园;08班,傅娟,6005.58549209602,湖北,2室2厅1卫,92777,绿岛家园;08班,傅娟,6005.58549209602,湖北,2室2厅1卫,92777,绿岛家园;01班,魏艳,6682.07975569962,江苏,4室1厅2卫,120㎡,海德花园;06班,仲敏,13836.8376525414,山西,6室2厅4卫,280,永安家园;08班,傅娟,6005.58549209602,湖北,2室2厅1卫,92777,绿岛家园;01班,酆桂英,8668.34466926012,广东,7室1厅3卫,300㎡,合顺家园;10班,俞平,6439.97549533181,新疆,2室1厅1卫,115㎡,深南花园;06班,贺明,14470.4214470766,江苏,4室1厅2卫,88㎡,北坞嘉园;10班,邹丽,11458.3831061942,新疆,1室2厅1卫,53㎡,祥祺花园;05班,董强,6175.64637896457,湖南,3室2厅2卫,28㎡,拱辰星园;07班,韶静,6641.6820857269,上海,5室2厅4卫,240㎡,佟馨家园;04班,水敏,7308.19927690341,湖南,2室2厅1卫,12㎡,大运家园;08班,丁强,14687.1029986503,山西,5室2厅2卫,268㎡,清景园
图2.75.1 textsplit函数分割文本
2.75.3 注意事项
TEXTSPLIT函数无特殊使用禁忌。
2.76 TEXTBEFORE函数教程
2.76.1 函数基本参数
- TEXTBEFORE 函数功能为,对文本数据进行分割,并提取分割文本之前的文本数据。
- 函数参数:参数1:text 待分割的文本;参数2:delimiter 分割字符;参数3:instance_num,实例数目。参数4:ignore_case 是否忽略大小写,默认TRUE表示忽略大小写,FALSE表示大小写敏感。
2.76.2 应用案例
提取以下文本之中第5个分号“;”之前所有文本,则完整公式为:=TEXTBEFORE(E2,”;”,5,TRUE)。
chevrolet chevelle malibu;buick skylark 320;plymouth satellite; amc rebel sst;ford torino;ford galaxie 500;chevrolet impala;plymouth fury iii;pontiac catalina;amc ambassador dpl
图2.76.1 提取指定支付前所有文本
2.76.3 注意事项
TEXTBEFORE函数无特殊使用禁忌。
2.77 TEXTAFTER函数教程
2.77.1 函数基本参数
- TEXTAFTER 函数功能为,对文本数据进行分割,并提取分割文本之后的文本数据。
- 函数参数:参数1:text 待分割的文本;参数2:delimiter 分割字符;参数3:instance_num,实例数目。参数4:ignore_case 是否忽略大小写,默认TRUE表示忽略大小写,FALSE表示大小写敏感。
2.77.2 应用案例
提取以下文本之中第5个分号“;”之后所有文本,则完整公式为:= TEXTAFTER (E2,”;”,5,TRUE)。
chevrolet chevelle malibu;buick skylark 320;plymouth satellite; amc rebel sst;ford torino;ford galaxie 500;chevrolet impala;plymouth fury iii;pontiac catalina;amc ambassador dpl
图2.77.1 提取指定文本之后所有文本
2.77.3 注意事项
TEXTAFTER 函数无特殊使用禁忌。
2.78 TOROW函数教程
2.78.1 函数基本参数
- TOROW 函数功能为,将二维数组转化为一行数组。
- 函数参数:参数1:array 待转化的二维数组;参数2:ignore 是否忽略某种类型数据;参数3:scan_by_column 是否按列遍历数据。
2.78.2 应用案例
函数TOROW用法简单,按行遍历数据完整公式为:=TOROW(A2:G16,1,TRUE)
图2.78.1 二维数据转化为一行
2.10.3 注意事项
函数支持数组公式,在实际应用场景之中,为了提升效率,第二参数尽量不用整列引用。
2.79 TOCOL函数教程
2.79.1 函数基本参数
- TOCOL 函数功能为将二维数组转化为一列数组。
- 函数参数:参数1:array 待转化的二维数组;参数2:ignore 是否忽略某种类型数据;参数3:scan_by_column 是否按列遍历数据。
2.79.2 应用案例
函数TOCOL用法简单,按列遍历数据完整公式为:=TOCOL(A2:G16,1,FALSE)。
图2.79.1 将二维数据转化为一列数据
2.79.3 注意事项
函数支持数组公式,在实际应用场景之中,为了提升效率,第二参数尽量不用整列引用。
2.80 HSTACK函数教程
2.80.1 函数基本参数
- HSTACK 函数功能为,按照列对数组数据进行拼接。
- 函数参数:参数1:array 待拼接的数组数据1;参数2:array_n 待拼接的数组数据n。
2.80.2 应用案例
利用HSTACK函数,将姓名、工资和面积数据拼接为一个数组数据,完整公式为:=HSTACK(B2:B16,C2:C16,F2:F16)。
图2.80.1 使用HSTACK函数,将多列数据拼接成一个大数组
2.80.3 注意事项
HSTACK函数,各个拼接数组数据行数应该相同。
2.81 VSTACK函数教程
2.81.1 函数基本参数
- VSTACK 函数功能为,将多个数组数据按行进行拼接。
- 函数参数:参数1:array 待拼接的数组数据1;参数2:array_n 待拼接的数组数据n。
2.81.2 应用案例
利用VSTACK函数,将不联系的数据拼接为一个完整数组数据,完整公式为:=VSTACK(A2:G5,A8:G10,A13:G16)。
图2.82.2 VSTACK拼接数据
2.81.3 注意事项
VSTACK函数,各个拼接数组数据列数应该相同。
2.82 WRAPROWS函数教程
2.82.1 函数基本参数
- WRAPROWS 将一维数组转化为二维数组,转化二维数组,按照逐行填充,该二维数组的列数据,由函数参数指定,二维数组的行数由计算动态获得。
- 函数参数:参数1:vector 一维数组数据;参数2:wrap_count,转为为二维数组的列数;参数3:pad_width,如果转化为二维数组时,元素不足时,使用该元素填充。
2.82.2 应用案例
下图案例,左边数据为一维数组,该一维数组也是由二维数组转化而来,现在需要使用WRAPROWS函数将该一维数组,恢复成二维数组,该转化完整公式为:=WRAPROWS(H2#,7)。
图2.82.1将左边一维数组转化为7列二维数组
2.82.3 注意事项
WRAPROWS函数无特殊使用禁忌。
2.83 WRAPCOLS函数教程
2.83.1 函数基本参数
- WRAPCOLS 将一维数组转化为二维数组,转化二维数组时,按照逐列填充,该二维数组的行数由函数参数指定,二维数组的列数由计算动态获得。
- 函数参数:参数1:vector 一维数组数据;参数2:wrap_count,转为为二维数组的行数;参数3:pad_width,如果转化为二维数组时,元素不足时,使用该元素填充。
2.83.2 应用案例
下图左边一维数组为二维数组转化而来,现在使用WRAPCOLS函数将该一维数组恢复成原来二维数组样式,完整公式为:=EFWRAPCOLS(H2#,15)。
图2.83.1 将左边一维数组转化为15行二维数组
2.83.3 注意事项
WRAPCOLS函数无特殊使用禁忌。
2.84 TAKE函数教程
2.84.1 函数基本参数
- TAKE 函数功能为,从选区内开头或者结尾截取指定行数或者列数数据,生成子数组。
- 函数参数:参数1:array 待截取数据数组,该参数可以为Excel选区、常量数组或者其他函数返回的数组数据;参数2:rows,截取函数,如果为正数表示从开头截取n行数据,如果为负数,表示从结尾截取|n|行数据;参数3:columns,截取函数,如果为正数表示从开头截取m列数据,如果为负数,表示从结尾截取|m|列数据。。
2.84.2 应用案例
提取数组前4行前4列数据,完整公式为:=TAKE(A2:G16,4,4)。
图2.84.1 提取前4行前4列数据
提取倒数4行,倒数4列数据,完整公式为=TAKE(A2:G16,-4,-4)。
图2.84.2 提取倒数4行、倒数4列数据
2.84.3 注意事项
TAKE函数用法简单,无特殊使用禁忌。
2.85 DROP函数教程
2.85.1 函数基本参数
- DROP 函数功能为,从数组内丢弃指定行数或者列数数据。
- 函数参数:参数1:array 待丢弃数据的数组;参数2:rows 丢弃数据的行数,如果为正数表示丢弃前n行数据,如果为负数,表示丢弃尾行|n|行数据;参数3:columns丢弃数据的列数,如果为正数表示丢弃前m列数据,如果为负数,表示丢弃尾列|m|列数据。
2.85.2 应用案例
想将左侧数据前4行、5列数据丢弃,完整公式为=DROP(A2:G16,4,5)。
图2.85.1 丢弃前4行、5列数据
丢弃后4行、5列数据完整公式为=DROP(A2:G16,-4,-5)。
图2.85.2 丢弃后4行、后5列数据
2.85.3 注意事项
DROP函数用法简单,无特殊使用禁忌。
2.86 CHOOSEROWS函数教程
2.86.1 函数基本参数
- CHOOSEROWS函数功能为,对选区内数据提取指定的行。
- 函数参数:参数1:array 待提取的数组数据,该参数可以为Excel选区,也可以为其他函数的返回数组;参数2:row_num1 待提取的行1;参数n:row_numn,待提取的行n。
2.86.2 应用案例
使用CHOOSEROWS函数提取数据数组数据前三行,完整公式为:CHOOSEROWS(A2:G16,1,2,3)
图2.86.1 提取数组数据前3行
CHOOSEROWS函数配合MATCH函数,实现数据匹配查找功能,完整公式为: =CHOOSEROWS(A2:G16,MATCH(I1,B2:B16,0))。
图2.86.2 实现数据匹配查找功能
2.86.3 注意事项
函数支持数组公式,在实际应用场景之中,为了提升效率,第二参数尽量不用整列引用。
2.87 CHOOSECOLUMNS函数教程
2.87.1 函数基本参数
- EFCHOOSECOLUMNS 函数功能为,对选区内数据提取指定的列数据,可以简单理解为提取数组数据的子数组。
- 函数参数:参数1:array 待提取的数组数据,该参数可以为Excel选区,也可以为其他函数的返回数组;参数2:column_num1,待提取的数列1。参数n:column_num2,待提取的数据列n。
2.87.2 应用案例
使用函数提取如下数组数据的第二列和第一列,将两列数据拼接成一个新数组,完整公式为:=EFCHOOSECOLUMNS(A2:G16,2,1)。
图2.87.1 提取子数组,实现数据提取
利用CHOOSECOLUMNS函数数据交换能力,实现VLOOKUP函数逆向匹配查找,完整公式为=VLOOKUP(L1,CHOOSECOLUMNS(A2:G16,2,1),2,0)。
图2.87.2 实现VLOOKUP函数逆向匹配
2.87.3 注意事项
CHOOSECOLUMNS函数提取是的数据列,应该在array数组范围内。
2.88 EXPAND函数教程
2.61.1 函数基本参数
- EXPAND 函数功能为,对数组数据进行扩充,扩充原始数据指定的数据填充。
- 函数参数:参数1:array待扩充的数组数据;参数2:rows 扩充行数,数组array将会被扩充到rows行;参数3:columns 扩充列数,数据array将会被扩充到columns列。
2.10.2 应用案例
将左边数据扩充到15行10列,扩充后不足的数据使用“-”填充。完整公式为:=EXPAND(A2:G10,15,10,”-“)
图1 数据扩充
2.10.3 注意事项
EXPAND函数用法简单,无特殊使用禁忌。
免责声明:本站所有文章内容,图片,视频等均是来源于用户投稿和互联网及文摘转载整编而成,不代表本站观点,不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益,请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权,请联系删除,如若转载,请注明出处:https://yundeesoft.com/65326.html