filter函数计算出来的na怎么消除（filteroffsetchoose）

本文主要讲解以下几个函数，区域筛选函数filter，排序函数sort,sortby,组合函数choose randbetween ,合并文本函数concat,textjoin,phonetic,间接引用函数indirect，动态引用函数offset ,清空函数TRIM ，替换函数REPLACE,substitute,行号函数row,列号函数column,行数函数rows，列数函数columns

本例完成以下问题：

问题1筛选部门为华山派且性别男的全部表格数据

问题2筛选部门为（华山派或者日月神教）且性别男的全部表格数据

问题3筛选部门为（华山派或者日月神教）且性别男的全部表格数据并按总成绩大小进行排序(倒序）

问题4先按语文分数高低进行排序，如果语文分数一样再按数学成绩进行排序：

问题5从花名册中随机抽取一个姓名,以东方不败、令狐冲、仪琳、任盈盈、岳灵珊几个人的名字随机填充20行数据

问题6把每行省，市，县名称进行连接到一个单元格，把所有行省，市，县名称进行连接到一个单元格

问题7把不同工作表里的销售额数据进行汇总到一张表，

问题8根据月份参数实现动态累计销售额统计，比如输入B11单元格输入10，则统计1到10月份的累计销售额

问题9根据时间月份的更新,每次自动提取最新月份的汇率到指定单元格列

问题10清空前后空字符串，将姓名的第二个字替换为*号，用替换文本将第二个任盈盈替换为任大小姐

问题11如果单元格值为0则显示为空

问题1：一种方法是直接在表上用筛选，如下

还有一种是用filter函数，L1单元格中输入公式=FILTER(A2:I19,(C2:C19="华山派")*(B2:B19="男"),"")

即可完成，这个区域是动态显示的，有多少行就显示多少行

filter函数

语法=FILTER(array,include,[if_empty])filter英文单词有过滤的意思，根据定义条件筛选一系列数据

filter对整个范围进行筛选，返回一个符合条件的范围,相当于我们数据多个列进行筛选后的结果

第一个参数是范围，是要进行过滤筛选的数组或区域

第二个参数是条件判断，返回的是一个布尔值数组，要求其高度或宽度与数组相同

第三个参数是在所含数组中的所有值都为空（筛选器不返回任何内容）时返回的值

FILTER 将动态创建相应大小的数组范围，函数将返回一个数组，如果该数组是公式的最终结果，则将溢出,在WPS当前版本下无法实现此功能，需要选中和原表一样大的区域范围，按ctrl shift 回车三个键组合，会生成如下结果，红框内的数据即是我们需要的，但是会产生一些其他数据变为#N/A显示

如果数据集可能返回空值(即没有满足条件的数据区域)，需要填第三个参数，否则将导致#CALC! 错误 ，因为 Excel当前不支持空数组

例如我们筛选一个不存在的门派和性别，如果没填第三个参数

L2中公式=FILTER(A1:I19,(C1:C19="")*(B1:B19="no"))

L2中公式=FILTER(A1:I19,(C1:C19="")*(B1:B19="no"),"不存在")

此时返回数组里只有一个元素，也就是“不存在”三个字，所以只在L2单元格里显示，并不溢出到其他单元格

问题2：筛选部门为（华山派或者日月神教）且性别男的全部表格数据

公式=FILTER(A1:I19,((C1:C19="华山派") (C1:C19="日月神教"))*(B1:B19="男"),"")

问题3：筛选部门为（华山派或者日月神教）且性别男的全部表格数据并按总成绩大小进行排序(倒序）

公式=SORT(FILTER(A1:I19,((C1:C19="华山派") (C1:C19="日月神教"))*(B1:B19="男"),""),COLUMN(I1),-1)

这里面用到了复合函数，里面加了一个SORT排序函数

SORT(array,[sort_index],[sort_order],[by_col]) sort英文是排序的意思，这个函数主要是对某个数组进行排序，引入分类这个条件，而rank函数只对单一列进行排序

第一个参数是要排序的范围，这里我们直接以前面filter函数的返回数组作为sort函数的第一个参数

第二个参数是一个数字，表示要按其排序的行或列，这里因为我们要按总成绩列进行排列，我们用COLUMN函数直接返回总成绩列的列号，如果这个参数不填则默认为1

第三个参数也是一个数字，表示所需的排序顺序；1表示升序（默认值），-1表示降序

第四个参数是一个逻辑值，指示所需的排序方向，False表示按行排序（默认值），True 表示按列排序

例：我们对东方不账的成绩项按列进行排序，输入公式=SORT(E1:I2,2,-1,TRUE)

我们还可以用SORTBY进行排序，这是一个可以多条件排序,如下：

问题4：先按语文分数高低进行排序，如果语文分数一样再按数学成绩进行排序：

公式=SORTBY(A2:I19,E2:E19,-1,F2:F19,-1)

我们可以看到语文成绩相等的几个人则是按数学成绩高低进行排序，还可以再加条件依次类推

函数用法SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

第一个参数是数组，要进行排序的全部范围

第二个参数是分类区域

第三个参数是排序方式1是顺序，-1是倒弃序，不填则默认为是顺序

choose要筛选的数组或区域

CHOOSE(index_num, value1, [value2], ...)choose英文是选择的意思，函如其名，是对参数进行选择

第一个是选择的索引数字，就是选第几个值如果小于1或大于列表中最后一个值的索引号，则 CHOOSE 返回 #VALUE! 错误值，

第二个参数到最后一个参数都是所待选的值，最多254个，该值也可以是一个区间范围

=CHOOSE(2,1,2) 返回值为2

问题5：从花名册中随机抽取一个姓名：

此时我们用到CHOOSE函数,在C2单元格输入如下公式：按F9刷新可实现每次随机抽取一个姓名

=CHOOSE(RANDBETWEEN(1,18),B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B13,B14,B15,B16,B17,B18,B19)

以东方不败、令狐冲、仪琳、任盈盈、岳灵珊几个人的名字随机填充20行数据

=CHOOSE(RANDBETWEEN(1,5),"令狐冲","东方不败","任盈盈","岳灵珊","仪琳","")按F9键每次刷新

CHOOSE还可以结合VLOOKUP函数，可以实现逆向查询，也是构建数组区域，之前说过建议直接用XLOOKUP,所以在这里就不作介绍了

ROW([reference]) row英文是行的意思，即返回行的序号，如果参数省略，则返回当前单元格的行号在L2单元格输入=ROW()，返回2

如果参数输入的是一个区域，则返回的是区域的第一行的行号，如=ROW(A3:I7)返回3

ROWS(array):返回数组的行数

=ROWS(A:A) 返回1048576行，因为表格行数极限就是这个数

公式=ROWS(A1:I19)，返回19，即共有19行

我们把整个表并名为“成绩单”

输入公式=ROWS(成绩单)，直接用名称作为参数

COLUMN([reference]) column英文是列的意思，这个函数返回给定字段的列单元格的列号，如果为空，则返回当前列号：

例：在L2输入=COLUMN()，返回12，因为L列从左到右排在第12位

如果参数为一个区域，则返回区域里最左列的列号，如=COLUMN(成绩单)返回1

COLUMNS(array)，返回数组的列数，和rows()返回行数一个道理

=COLUMNS(成绩单) 返回数字9，即表示成绩单这个工作表一共有9列

=COLUMNS(1:1)返回第一行中所有的列数和，一共有16384列，也就是Excel表列数的极限值

问题6把每行省，市，县名称进行连接到一个单元格，把所有行省，市，县名称进行连接到一个单元格

最简单的办法是用连接符号&进行连接，如下：=A2&B2&C2

还可以用CONCAT函数=CONCAT(A2:C2)

在运算速度上，用&符号连接更快一些，但是&有个难点，就是如果连接的单元格过多，比如十几个以上，一个一个敲&符号太麻烦，这时最好是用连接函数进行连接

CONCAT(text1, [TEXT2],…)， concat是英文concatenate的缩写，意思就是连接的意思

第一个参数是需要要联接的文本项，可以是单个字符串，也可以是字符串数组（比如一个区域）

第二个参数是可选项，最多可以有253个

本例中每一个参数就是用的引用A1:C1这一行单元格区域

我们还可以在中间加上特定符号，比如“-”进行连接：=CONCAT(A2,"-",B2,"-",C2)

这里面破折号部分还可以用其他任意文本，都可以进行连接

还可以用PHONOTIC函数进行连接=PHONETIC(A2:C2)

PHONETIC(reference):phonetic英文是音标的意思，这也是个隐藏函数，官方介绍说是为提取文本字符串中的拼音 (furigana) 字符，该函数只适用于日文版，而我们这里可以把它作为一个合并区域文本到一个单元格的函数使用

如果用这个函数也进行-号进行连接，则需要在省市县中间插入辅助列，如下：=PHONETIC(A2:E2)

还可以用TEXTJOIN进行连接：=TEXTJOIN("",TRUE,A2:C2)

=TEXTJOIN("-",TRUE,A2:C2)

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

TEXTJOIN 函数将多个区域或字符串的文本组合起来，并包括你在要组合的各文本值之间指定的分隔符。 如果分隔符是空的文本字符串，则此函数将有效连接这些区域。

第一个参数是分隔符，delimiter英文就是分隔符的意思，我们这里用空””，或者”-”

第二个参数是，是否忽略空单元格，如果为TRUE，则忽略空白单元格，FALSE为不忽略

第三个参数是要联接的文本项,文本字符串或字符串数组，如单元格区域,我们这里就是取的行区域

把所有行省，市，县名称进行连接到一个单元格：

这个问题理论上也是可以用&符号完成的，只不过要一个单元格一个单元格地敲，所以不现实，那么我们用函数来完成：=CONCAT(A2:C4)

PHONETIC(A2:C4)

=TEXTJOIN("",TRUE,A2:C4)

如果在每行县我后加豆号，则是=TEXTJOIN(",",TRUE,D2:D4)

问题7：把不同工作表里的销售额数据进行汇总到一张表

当然把所有的月份表格合并在一张表上是一个办法，假如我们不用合并的方法，而利用间接引用函数进行取数

我们可以用=INDIRECT(A2&"!A1:D2")引用出每个表里的数据，假如销售额都在每个表的第二行，而姓名顺序为不固定的，则我们要用到INDEX MATCH,进行取数

=INDEX(INDIRECT($A2&"!A2:D2"),MATCH(B$1,INDIRECT($A2&"!A1:D1"),0))

INDIRECT(ref_text, [a1])：indirect单词是间接的意思，也就是说这个间接引用函数

第一个参数，是引用文本对包含 A1样式引用、R1C1样式引用、定义为引用的名称或作为文本字符串对单元格的引用的单元格的引用。

如果引用外部引用 (工作簿) ，则必须打开另一个工作簿,如果源工作簿未打开，INDIRECT 返回#REF！ 错误值

第二个参数可选， 一个逻辑值，用于指定包含在单元格中的引用的类型。

如果 a1 为 TRUE 或省略，ref_text 被解释为 A1-样式的引用。

如果 a1 为 FALSE，则将ref_text 解释为R1C1样式的引用。

例=INDIRECT("R1C1",0)，此时代表绝对引用每一个单元格，R是行的意思，R1表示第一行，C是列的意思，C1表示第一列

如果想用indirect函数引用整个成绩表，在L1输入公式：=INDIRECT("A1:I19")

或者在K1单元格输入”A1:I19”,=INDIRECT(K1)

问题8：根据月份参数实现动态累计销售额统计，比如输入B11单元格输入10，则统计1到10月份的累计销售额

在C11输入公式=SUM(OFFSET(A2,0,0,1,B11 1))，B11可手动更改参数

OFFSET(reference, rows, cols, [height], [width])

offset英文单词有偏离的意思，个人觉得这是Excel里面不算（金融函数工程函数）外最烧脑的一个函数了，它返回的是一个区域，返回对单元格或单元格区域中指定行数和列数的区域的引用。

第一个参数：Reference 要基于偏移量的引用,引用必须引用单元格或相邻单元格区域;否则OFFSET 返回#VALUE！错误值，这个参数可以是一个单元格，也可以是一个区域

第二个参数是需要左上角单元格引用的向上或向下行数，正数为向下，负数为向上

第三个参数需要结果的左上角单元格引用的从左到右的列数。 正数为向右，负数为向左

第四个参数高度,可选，是需要返回的引用的行高，必须为正数。

第五个参数宽度，可选是需要返回的引用的列宽，必须为正数。

比如通过A1作为第一个参数进行引用整个成绩表区域，公式如下：

=OFFSET(A1,0,0,ROWS(A1:A19),COLUMNS(A1:I1))

如果引用岳灵珊（包括）上面的姓名的数据，公式

=OFFSET(A11,-ROW(A11) 1,0,ROWS(A1:A11),COLUMNS(A1:I1))

如果引用岳灵珊（包括）下面的姓名的数据=OFFSET(A11,0,0,ROWS(A11:A19),COLUMNS(A1:I1))

问题9：根据时间月份的更新，每次自动提取最新月份的汇率到指定单元格列：如下

让O列总是引用最新月份的汇率 =OFFSET(C2,0,COUNTA(D2:N2))

问题10清空前后空字符串，将姓名的第二个字替换为*号，用替换文本将第二个任盈盈替换为任大小姐

例：我们在东方不败，任盈盈，方证名字前面加个空格，在令狐冲的名字中间加上空格

输入公式=TRIM(A2)

TRIM(text),trim英文有修剪的意思，作用是除了单词之间的单个空格之外，移除文本中的所有空格

我们还有更快的方法，就是选中第一个有空格的文本，按ctrl shift end（注意不是回车）键，这时我们会看到右上方有个惊叹号，然后点开

选择清空前后空字符串即可

附：有个小技巧，如果单元格数据较大，我们找不到或者不知道第一个带有空格的单元格在哪里，这时我们可以自己手动在数据最左上角加一个空单元格，选中按ctrl shift end键，清除空格即可

上面这令狐冲是文本中间的空格，Excel会默认是文本中的正确空格，如果我们想要删除里面的空格，这时需要用到替换，选中要替换的单元格，如果是多个值则选择整个区域，查找内容里面输入一下“空格”键，下面替换为不输入

点全部替换，即可看到完成替换，注意这个空格要在英文输入法下的空格

我们还可以用replace函数，公式如下：=replace(A3,3,1,"")

这里我们只有一个，且知道只有一个空格，位置在第3位，

REPLACE(old_text, start_num, num_chars, new_text)，replace英文是替换的意思

第一个参数是要替换其部分字符的文本，我们这里选指定单元格

第二个参数是文本中要替换部分的开始位置

第三个参数是文本中来进行替换的字符数

第四个参数是替换的新文本

将姓名的第二个字替换为*号=REPLACE(B2,2,1,"*")

如果是下面这种，一个单元格里可能多个空格，并且位置也不固定，这种情况，建议直接用ctrl H替换,要使用公式的话则用substitude函数:=SUBSTITUTE(B2," ","")

SUBSTITUTE(text, old_text, new_text, [instance_num])

substitute英文是替换的意思，在某一文本字符串中替换指定的文本，这个函数和replace函数的区别在于这个是不需要知道要被替换的文本的具体位置，而replace需要知道特定位置

第一个参数是需要替换其中字符的文本，或对含有文本（需要替换其中字符）的单元格的引用。

第二个参数是需要替换的文本。

第三个参数替换的新文本

第四个参数Instance_num，instance是实例的意思，默认为空，表示全替换，如果指定要将第几个 旧文本替换为新文本，则需要填入参数。例：将下面单元格”任盈盈任盈盈“里面的第二个“任盈盈”替换为”任大小姐“：=SUBSTITUTE(A2,"任盈盈","任大小姐",2)

问题11：如果单元格值为0则显示为空

比如A列有10个0，我们要让它不显示出来，则可以在单元格格式里找到自定义，

在类型里面输入[=0]””，这个意思就是等于0时，显示为空

此时单元格即显示为空，但是此单元格并不是空值，我们可以用ISBLANK函数进行查看

很明显前10个单元不是空

,