几个函数使用的小体会（适合初学者，高手略过）

yellowhawk

       学习函数已经很长时间了，一直没有提升，我想也是自己太不上进的原因，加上在实际中没有用武之地，所以一直停留在初学的水平。
最近由于事情不太忙，所以经常来论坛解答一些问题，在解答过程中，有些想法是正确的，但在使用函数时，经常遇到一些阻碍，也就促使自己努力去查找解决的办法，或问高手（如佛山小老鼠等），有些小体会，特小结如下：
如图：
1、SUBSTITUTE函数中替换0，而不替换10中的0的小收获。
要求把D列中的数据对应E列的条件，分别写在对应的项目（F3:I3)下：
我的公式如下：=IFERROR(INDEX($D$4:$D$29,SMALL(SUBSTITUTE(($E$4:$E$29=F$3)*ROW($1:$26)-1,"-1",99)+1,ROW(A1))),"")
解析：首先使用($E$4:$E$29=F$3)判断$E$4:$E$29中值为F$3的一个数组，然后乘以ROW($1:$26)26个行序号，得到
{1;0;3;4;5;0;0;0;0;10;11;12;13;14;15;0;17;18;19;20;21;0;23;24;25;26}这样一个数组
开始我简单的以为使用SUBSTITUTE这个函数可以替换这个数组中的0为99，但实际过程中，10中的0也替换成99了，即199，这样就是错误的了。如何解决这个问题呢，我想到的是，将数组全部减1，这样0-1就得到-1，那么再用SUBSTITUTE这个替换-1这个数，就不会影响到0值的数了。替换后，由于数组都减了一个1，所以在替换后应该加上1
这里加1还有一个好处。
如果直接运算SMALL(SUBSTITUTE(($E$4:$E$29=F$3)*ROW($1:$26)-1,"-1",99),ROW(A1))这个函数时会出错，思考很久没有弄明白，最后在佛山小老鼠老师的指导下，原来SUBSTITUTE这个函数得出来的值是文本格式，一般可以在前面加“--”来转换成数值，而我正好使用加1，则自然转换成了数值。可以被small取值了。

在https://club.excelhome.net/forum ... ;page=1#pid11373181这个帖子中，我也是使用了相同的思路来解决的。
=IFERROR(INDEX(F$3:F$310,SMALL(--(SUBSTITUTE(((TEXT($I$3:$I$310,"yyyy/mm")=$O$1)*(($H$3:$H$310="转账加款")+($H$3:$H$310="转账退款"))*ROW($1:$308))-1,"-1",999)+1),ROW(F58))),"")

yellowhawk

2，0次方的使用，就是使一个数组中的所有值（0除外）都等于1.

具体例子如图：
=SUM(IFERROR(FIND(E6,$C$6:$C$12)^0,0)*($A$6:$A$12/$B$6:$B$12))
解析：
首先，我是找到E6在$C$6:$C$12中的位置，
{1;#VALUE!;3;#VALUE!;1;1;#VALUE!}
这中间有个3，我要统计变成1，所以使用FIND(E6,$C$6:$C$12)^0即0次方
这就是0次方的妙用。划重点。
{1;#VALUE!;1;#VALUE!;1;1;#VALUE!}
然后用iferror则将错误值替换成0值。
然后也($A$6:$A$12/$B$6:$B$12)相乘，就得到了每一个对应的值
最后将其sum加起来，就实现了要求。

丢丢表格

图1 题 ，我是这样做的。

1. =IFNA(MODE(MAX(F$3:F3)+ROW($1:20),IF(E$4:E23=F$3,D$4:D23)),"")

复制代码

丢丢表格

高版本的，可以写成：

=MODE.MULT(ROW($1:99),IF(E$4:E23=F$3,D$4:D23))

dambcer

学习了，谢谢分享经验

mountain119

丢丢表格 发表于 2023-11-15 21:47
高版本的，可以写成：

=MODE.MULT(ROW($1:99),IF(E$4:E23=F$3,D$4:D23))

我真糊涂了，这个题不就是FILTER的一个标准用法就解决了么？大佬们，你们在干什么？

cam333

学习了，有相同体会

yellowhawk

mountain119 发表于 2023-11-16 09:53
我真糊涂了，这个题不就是FILTER的一个标准用法就解决了么？大佬们，你们在干什么？

我们是初学者，哈哈，向您学习。

wlc5201130

感谢分享，对新手确实实用，善于发现问题和解决问题的思路值得借鉴。