[推荐] [答疑解惑]函数公式解释专用帖

gvntw版主的函数版：本页小计、累计 OR ONLY 本页小计公式解释
  

公式1、A列的序号公式及改写

=IF(MOD(ROW(),J$1+1)=2,"本页小计",(COUNT(A$2:A2)+1<=MAX(Data!A:A))*(COUNT(A$2:A2)+1))

解释：
  
1、  首先，J1单元格存放着每页要的行数（本例中为每20行一个小计）——这就是循环的周期。

2、  利用求余函数MOD来做循环：

公式从第3行开始ROW()取得当前行号即3，MOD(ROW(),J$1+1)èMOD(3,21)è得到余数3。

目标：在20行后的第1行，也就是第3+20行显示“本页小计”，第23行的MOD(23,21)=2，这就是我们要的条件。

修改：将Row()改为Row(1:1)，则公式改为=IF(MOD(ROW(1:1),J$1+1)=0,……也是同样的目的，区别在于不拘束与公式从第3行开始。

3、  (COUNT(A$2:A2)+1<=MAX(Data!A:A))* (COUNT(A$2:A2)+1)：Count只计算数值的个数，A$2:A2是一个变动的区域，下拖复制时变为A$2:A3、A$2:A4……（请先学习相对引用和绝对引用）

当序号小于等于Data原有序号的最大值时，用这个公式得到1、2、3等序号，超过时得到0，然后工具〉选项〉视图〉零值设置不显示，隐藏了0。

修改：
=IF(MOD(ROW(1:1),J$1+1),(MAX(I$2:I2)<MAX(Data!A:A))*(MAX(I$2:I2)+1),"本页小计")，用Max忽略文本值的方法也可以达到。

公式2、B列的查询公式及改写

=IF(N(A3),VLOOKUP($A3,Data!$A:$H,COLUMN(),0),"")——Vlookup根据序号查找姓名。

N(A3)——如果A3是文本，返回0，否则返回数值。

此处因Data的序号是升序排列，Vlookup最后一个参数可以不用0，即模糊查找即可。=IF(N(A3),VLOOKUP($A3,Data!$A:$H,COLUMN()),"")

修改：升序排列Lookup也可以。

=IF(N(A3),LOOKUP($A3,Data!$A:B),"")——注意$A:B——A列绝对引用，B列相对引用。

公式3、C列的小计公式及查询
 

=IF($A3="本页小计",SUM(INDIRECT("R[-"&$J$1&"]C:R[-1]C",0)),IF$A3,VLOOKUP($A3,Data!$A:$H,COLUMN(),0),))
如果A列是”本页小计”，则返回SUM求和，否则返回利用序号查询，同理Vlookup对升序的查询可改版为：LOOKUP($A3,Data!$A:C)

Indirect函数第2参数为1或默认，表示A1引用样式，为0或False表示R1C1引用样式（请先学习引用样式），R——表示行、C表示列。

将J1的数字（20行）代入"R[-"&$J$1&"]C:R[-1]C"è得到"R[-20]C:R[-1]C"è表示当前单元格往上20行至往上1行的区域。
也就是对当前行上面的20行求和——这就是小计。

老大，俺还是看不懂？流程图更是一窍不通？你能解释一下吗？麻烦了？

再谈二分法

不要急，一开始我看了许久也没看懂的，后来查了一些“二分法”（也称“半分法”）方面的东西就简单了。

比如数据=1、2、3……、100——升序排列，则用二分法在数据里查找X=33，第一次先对半分数据（这就要找那个Posi点了）。

1、流程图往左右分

N=100个数据，姑且按左向右排列，最左边为第1个数据，最右边为第N=100个数据，Posi=INT((1+100)/2)==>得到50，即第一次找到的Posi点就是第50个（Posi位置）数据。——33（查找值X）与第50个（位置）数据50（值）相比，33<50，所以，流程图往左边走，也就是说查找的X在数据中的位置在第1~50数据之间，绝不可能在第50~100之间。

于是新的循环开始：最左边的位置=1，最右边变成了Posi-1也就是上次找到的第50个（位置）=49

查对半新的Posi位置即=INT((1+49)/2)=25——第25个位置的数据，即25，与X=33相比，25<33，则流程图往右走，也就是说查找的X在数据第25~50个数据之间，绝不可能在第1~25个之间

……如此往复循环，（上面总共进行了2次比较运算）查找的数据都是对某个位置有目的地比较，而不是像“遍历法”（比如Countif(A1:A100,1)计算A1:A100中数值为1的单元格个数，它就需要从第1到第100个数据全部经历一遍，才能得到结果，用了100次比较运算）或者Match、Vlookup精确查找所用的类似“遍历法”一个个从头到尾比较过来，比到第1~33个终于找到X的时候才停下来（比较运算了33次）。

2、流程图往下走：

比如查找值X=50，第一步一下子就找到了50，在比较一下posi<right——即看这个位置（第50个）是否在最右边一个（第100个）的左边（除了找到第100个的时候，当然都是Yes了），如果是（即找到的位置50<100），则再比较一下第Posi+1个位置也就是第51个数是否等于查找值X，如果还满足（即第51个数据的值也是50），则Posi变成了51，再比较第52个数是否等于查找值X……一直找到最后一个=X的位置。（比如说第A50:A55都是数值50，那么Lookup(50,A1:A100,row(1:100))或者Match(50,A1:A100)得到的都是55。

这也就是为什么Lookup、Match、Vlookup采用二分法时效率极高的原因。

为何常见用Lookup(1,0/……、Lookup( 9E+307,……、Lookup("座座",……等查找公式：看流程图

当第一参数X与第二参数数组的某一个二分点（比如100个数据的第50个、第25个、第13个等）的值相等——A(posi)=X，流程图往下走，否则流程图往左右分。

所以，用一个永远大于第二参数的值X作为第一参数时，二分法将一直往流程图的左右分，直到找到最后一个满足条件的值。当第一参数=Max(第二参数)时，如果第二参数数组中的MAX值恰好处于二分点位置，且其左右不是Max值，则将返回找到的第一个符合的二分点位置的值。

以下三个公式结果一样，对偶用处很大，希望版主们给予解释。

具体链接：http://club.excelhome.net/dispbbs.asp?boardid=3&replyid=618920&id=204464&page=1&skin=0&Star=1

=LEFT(A2,LOOKUP(1,0/ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),ROW(INDIRECT("1:"&LEN(A2)))))

=LEFT(A2,MAX(IF(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),ROW(INDIRECT("1:"&LEN(A2))))))

=LEFT(A2,LEN(A2)+1-MATCH(,0/ISNUMBER(--LEFT(RIGHT(A2,ROW(INDIRECT("1:"&LEN(A2)))))),))

解答CANNA：这些公式都是=Left(A2,A2中最后一个数字的位置）

1、Row(INDIRECT("1:"&LEN(A2)))——比如A2字符个数为10，得到Row(1:10)，即1、2、……10的等差序列。——以下简称为Row

2、Mid(A2,Row,1)——对A1从第1个字符开始逐一取得每1个字符，用--将MID得到的文本转化为数值型，非数字则返回#VALUE！错误，在用ISNUMBER判断得到True、False的数组。

3、0/{True，False……}得到{0，#DIV/0！……}组成的数组，因为要找到的数字最大为0，所以Lookup(1,……，

Lookup(1,0/(条件),数组)的解释见本帖前几楼有关Lookup的阐述。并由此定位最有一个True所在的位置。

因为找到的数字最大是9，所以Lookup(10，……

——以上2个公式均为普通公式，利用Lookup第2、3参数对数组的支持。

利用Max(IF(条件，返回数组))得到数字所对应位置的最大值。

Left(Right(A2,ROW(INDIRECT("1:"&LEN(A2)))))——用Right由右边往左逐个截取尾巴段字符，比如"ABC123DE"——Right(A2,Row)得到的是，"E","DE"，"3DE"……再用Left(Right)得到"E","D","3"……

接下来就是Match(0,0/(条件),0)——头尾两个粗体的0省略，其中第3个0表示精确查找，第1个0表示要查找的值。也是利用Match函数忽略错误值的特性在0/（条件）得到的{0,#DIV/0!,……}数组中查找第一个0出现的位置（注意，这是从右往左开始算的，也就是从左往右最后一个数字的位置）

比如："ABC123DE"共有8个字符，最后一个数字是3，在第6位。从右往左数是第3位，Len(A2)=8，即8+1-3=6

这个公式的核心在于Match(1,0/(条件))——注意，没有第3参数，相当于Match(1,0/(条件)，1)大致匹配查找，其特性与Lookup的查找原理一致，具体请看[函数用法讨论系列10] LOOKUP的查找策略！之流程图。

因Match本身查找的就是位置，而不是数据本身，所以可以少用一个Row。

楼主你好,我想请教一个问题,关于字符串裁取,如我现在一个有字符串,

 "44"*180g莱克布绿色+4mmK329黑+28GT/C黑"红色部份为字符串,如何裁取从开头到"+"之间的字符串,用函数如何实现,如果不用函数,用其它方法实现也可以,

谢谢哈

to beefo:欢迎提问，但请注意本帖1楼的要求！

1、本帖是[解惑]，不是解题，这种问题完全可以在外面发帖求助。

2、补充问题，可以编辑自己曾经发过的帖子，方法是点击该楼层右下角的“编辑”按钮。不要占用楼层，以便其他朋友阅读。

3、针对补充的问题看，你是要得到44"*180g莱克布绿色、4mmK329黑、28GT/C黑，这样3个字符串，用数据〉分列〉分隔符号选其他，输入+号〉点击完成即可。不必用函数就轻松实现。

用函数截取的方法：假设字符在A1，供参考，方法还有其他的：

B1=LEFT(A1,FIND("+",A1)-1)截取第一段

C1=MID(A1,FIND("+",A1)+1,FIND("+",A1,FIND("+",A1)+1)-FIND("+",A1)-1)截取第2、3个+号之间

D1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,B1,),C1,),"+",)将A1中的+号和+号之间的字符串替换为空，即剩下的最后一段。

IF(ISERROR(FIND("-",A3,FIND("-",A3,FIND("-",A3)+1)+1)),IF(ISERROR(FIND("-",A3,FIND("-",A3)+1)),LEFT(A3,FIND("-",A3)-1),LEFT(A3,FIND("-",A3,FIND("-",A3)+1)-1)),LEFT(A3,FIND("-",A3,FIND("-",A3,FIND("-",A3)+1)+1)-1))

请问G先生这个公式的意思？

欢迎提问，请先阅读1楼规则。公式离开特定的附件要解释或理解比较枯燥，如果是本论坛的公式，可以给链接。

关于本公式：用“庖丁解牛”分段解读，只有繁琐没有难度。

FIND(要找的字符，所在的字符串，查找的起始位置），由此理解：FIND("-",A3)得到A3单元格字符串中，第一个负号-的位置。

FIND("-",A3,FIND("-",A3)+1))——在A3单元格字符串中，从第一个负号-的位置后面1个字符开始查找"-"出现的位置（也就是第二个负号的位置。

FIND("-",A3,FIND("-",A3,FIND("-",A3)+1))+1)——从第二个负号的位置后面1个字符开始查找"-"出现的位置（也就是第三个）……

ISERROR用于判断是否错误值，因为如果Find没有找到，返回#VALUE！错误。

LEFT(A3,FIND("-",A3)-1)——得到A3单元格第一个负号前面的字符……依此类推。

E1到E18里是文本 下面这个公式向下拉就可以得到其中不重复的

这是个啥道理 望高人解释解释

{=INDEX($E$1:$E$18,MATCH(,COUNTIF($A$8:A8,$E$1:$E$18),))}