Excel由内而外的公式分析方法：以INDEX+SMALL+IF+ROW组合公式为例

痛痛痛痛

由内而外的公式分析方法

公式函数作用真是奇妙无穷，组合起来真是功能强大，在学习的过程中也碰到了很多复杂的公式，自己也在一知半解中不断的获取着知识，下面分享一下自己研读公式的方法，大家集思广益，不断的进步。

由内而外的公式分析方法就是先分析组合公式中可以直接计算出值的简单的数据，不断往外延伸，最后把公式结果求解出来。

下面以刚看到的一个网友的公式为例来讲解。

这位网友的公式如下表所示，A1单元格即“单位地址”。

单位地址	请输入关键字：	学院
(单位地址)燕山大学教育学院大楼		(单位地址)燕山大学教育学院大楼
(单位地址)燕山大学老地		(单位地址)燕山大学医学部药学院无机化学教
(单位地址)燕山大学廖凯
(单位地址)燕山大学深圳医院
(单位地址)燕山大学审计
(单位地址)燕山大学生命科学
(单位地址)燕山大学图书馆114
(单位地址)燕大图书馆采访部(燕山大学主校
(单位地址)燕山大学物理楼北
(单位地址)燕山大学校刊编辑部（英杰交流
(单位地址)燕山大学遥感楼
(单位地址)燕山大学医学部药学院无机化学教

其要求是在C1单元格输入关键字，比如这里输入“学院”，然后在A2:A13中查找到包含关键字“学院”的的单元格，把符合条件的单元格数据筛选出放在C2单元格开始的C列中。

公式如下：

=INDEX(A:A,SMALL(IF(ISNUMBER(FIND(C$1,A:A)),ROW(A:A),4^8),ROW(A1)))&""

现在要解析这个公式。

解析公式一般可以通过“公式”选项卡中的“公式求值”来看验算步骤，但是这个方法有很大的局限性，特别是对于数组公式，很多关键步骤都被省略了，有时候并不能对公示做出很好的解释。所以很多时候用F9键来解析公式更详细。

利用F9键查看验算步骤的方法是：先选中公式编辑栏中想要了解的某个公式全部或者某个可以计算的算式整体，然后按下F9键，则公式编辑栏中的公式就会把计算结果代替被选中的公式或算式。

注意事项：当公式出现A:A这样的整列数据区域时，使用F9解析，需要将其转换成不影响运算的数据区域，比如本公式，实际只有A2:A13，这些有数据，那就用A2:A13去代替原来的公式即可。如果不转变，用F9解析包含A:A的地方时，会出现提示数据范围太大的警告提示。

痛痛痛痛

第一步：代入已知的的值

=INDEX(A:A,SMALL(IF(ISNUMBER(FIND("学院",A:A)),ROW(A:A),4^8),ROW(A1)))&""

第二步：计算简单的公式，这里计算ROW(A1)，可以选中“ROW(A1)”，按下F9，公式变化如下：

=INDEX(A:A,SMALL(IF(ISNUMBER(FIND("学院",A:A)),ROW(A:A),4^8), {1}))&""

第三步：计算可以计算的值：4^8

=INDEX(A:A,SMALL(IF(ISNUMBER(FIND("学院",A:A)),ROW(A:A),65536), {1}))&""

第四步：计算“FIND("学院",A:A)”。当选中“FIND(学院,A:A)”,按下F9会弹出提示框：公式太长，……不得超过8192个字符。

分析find函数，其是要在A列的数据中查找是否包含“学院”两个字，而A列数据只有A2:A13有数据，则可以将“FIND("学院",A:A)”更改为“FIND("学院", A2:A13)”。

公式变为

=INDEX(A:A,SMALL(IF(ISNUMBER(FIND("学院", A2:A13)),ROW(A:A),65536), {1}))&""

Find函数中，A2:A13是一个单元格区域，包含12个单元格数据，因此“FIND(学院, A2:A13)”返回值是一个12个值得数组，其依次在A2:A13 每个单元格中查找是否有“学院”两个字，有则在返回这两个字在对应单元格中的位置序号，如果没有则返回一个错误值”#value!”

选中“FIND(学院, A2:A13)”，按下F9，公式变为如下：

=INDEX(A:A,SMALL(IF(ISNUMBER({13;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;15}),ROW(A:A),65536),{1}))&""

可以发现，find在A2:A13单元格区域中查找，只有第1个和最后一个返回了数字，其它返回错误值。

第五步：

分析ISNUMBER函数，这里是判断find函数返回值是否为数值，因为find函数返回一个数组值，ISNUMBER函数同样返回一个数组值。F9后公式变为：

=INDEX(A:A,SMALL(IF({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE},ROW(A:A),65536),{1}))&""

第六步：

分析ROW(A:A)，和上面的道理一样，这里只有A2:A13有数据，ROW(A:A)改成ROW(A2:A13)，F9后公式如下：

=INDEX(A:A,SMALL(IF({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE},{2;3;4;5;6;7;8;9;10;11;12;13},65536),{1}))&""

第七步：

分析if函数，把if函数单独提出来，如下：

IF({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE},{2;3;4;5;6;7;8;9;10;11;12;13},65536)

我们知道if函数本身的用法如下：if(逻辑表达式,返回值1,返回值2)，对照刚才得出的if公式，发现这差距有点大，这里的逻辑表达式是一个逻辑数组，返回值1也是一个数组，刚遇到的人肯定不知道这个用法是什么意思，没关系，还是用F9，我们可以通过结果来分析过程，公式如下：

=INDEX(A:A,SMALL({2;65536;65536;65536;65536;65536;65536;65536;65536;65536;65536;13},{1}))&""

也就是IF({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE},{2;3;4;5;6;7;8;9;10;11;12;13},65536)的结果是：{2;65536;65536;65536;65536;65536;65536;65536;65536;65536;65536;13}

分析一下就可以发现，这之间还是符合if的基本原则，只是应用了数组。即：

逻辑表达式数组中第1个逻辑值是TRUE,则得到返回值1，因为返回值1是一个数组，则返回其第1个数组值2。

逻辑表达式数组中第2个逻辑值是FALSE,则得到返回值2，即65536。

逻辑表达式数组中第3个逻辑值是FALSE,则得到返回值2，即65536。

……

逻辑表达式数组中第12逻辑值是TRUE,则得到返回值1，因为返回值1是一个数组，则返回其第12个数组值13。

痛痛痛痛

第八步：

分析small公式：SMALL({2;65536;65536;65536;65536;65536;65536;65536;65536;65536;65536;13},{1}

在数据区域里，这里是数组数据，得到指定的第n个最小是，此处n=1，所以得到第1个最小值，也就是2，用F9分析samll后公式如下：

=INDEX(A:A,{2})&""

第九步：

分析INDEX公式，=INDEX(A:A,{2})，即返回A列数据中第二个单元格的数据，即A2的数据，这个单元格就是包含“学院”两个字的第一个数据，即“(单位地址)燕山大学教育学院大楼”

第十步：“(单位地址)燕山大学教育学院大楼”&“”

结果：“(单位地址)燕山大学教育学院大楼”

初次遇到这个问题的人会发现字符连接一个空字符，有点画蛇添足的意思，没啥用，那就接着往下看

只分析一个单元格的数据有时候不能很好地理解公式的用法，可以接着继续分析下一个单元格的公式加强理解，比如C3单元格的公式如下：

=INDEX(A:A,SMALL(IF(ISNUMBER(FIND(C$1,A:A)),ROW(A:A),4^8),ROW(A2)))&""

可以发现这里C3中的公式与C2中的公式差别在于“ROW(A2)”，那么分析方法系统，前七步都一样，第八步发生改变了，如下：

第八步：

分析small公式：SMALL({2;65536;65536;65536;65536;65536;65536;65536;65536;65536;65536;13},{2}

在数据区域里，这里是数组数据，得到指定的第n个最小是，此处n=2，所以得到第2个最小值，也就是13，用F9分析samll后公式如下：

=INDEX(A:A,{13})&""

第九步：

分析INDEX公式，=INDEX(A:A,{13})，即返回A列数据中第13个单元格的数据，即A13的数据。这个单元格就是包含“学院”两个字的第2个数据。即："(单位地址)燕山大学医学部药学院无机化学教"

第十步：“(单位地址)燕山大学教育学院大楼”&“”

结果：“(单位地址)燕山大学教育学院大楼”

还是没体现&“”的作用，继续往下看

到了C4单元格，前七步都一样，第八步发生改变了，如下：

第八步：

分析small公式：SMALL({2;65536;65536;65536;65536;65536;65536;65536;65536;65536;65536;13},{3}

在数据区域里，这里是数组数据，得到指定的第n个最小是，此处n=3，所以得到第3个最小值，也就是65536，用F9分析samll后公式如下：

=INDEX(A:A,{65536})&""

第九步：

分析INDEX公式，=INDEX(A:A,{65536})，即返回A列数据中第65536个单元格的数据，即A65536的数据。是个空白单元格，index返回值为：0

第十步：0&“”

结果为空值。如果不加&“”，则返回值是0，显然不符合要求。

以上就是公式由内而外的分析方法，只有公式出来，掌握方法分析往往是简单的，写出公式才是难点。

痛痛痛痛

分析完公式，可以继续来分析一下公式的设计思路。

我们人力来分析的过程如下：

1、将关键字“学院”与A列的第一个单元格（A2）值进行比对，包含，放到C2单元格，不包含略过

2、将关键字“学院”与A列的第2个单元格（A3）值进行比对，包含，若C2为空，放到C2单元格，否则放C3单元格，不包含略过。

3、将关键字“学院”与A列的第3个单元格（A4）值进行比对，包含，若C2为空，放到C2单元格，若不为空，判断C3单元格，若c3单元格为空，放在C3单元格，否则放在C4单元格。

……（总结：将关键字“学院”与A列的第n个单元格值进行比对，包含，放到C列中最后一个有数据单元格下发的空白单元格。否则略过。）

12、将关键字“学院”与A列的第12个单元格值进行比对，包含，放到C列中最后一个有数据单元格下发的空白单元格。否则略过。）

这个分析判断人力来判断很容易，但需要要很好的转换为Excel处理的行为，也就是利用Excel的函数、公式来实现。

根据人工分析特点，可以总结出一些规律

1、关键字依次比对，这个最常见的就是find函数。

2、比对后提取对应值，这个有很多种方法，比如lookup、vlookup、index、offset。

有了上面的思路可以先思考第一个关键字比对问题。

用FIND("学院", A2)，可以比对一个单元格，使用填充柄下拉公式可以依次得到12个单元格的比对结果，然后筛选一下，把符合要求的帅选出来，结果复制到C列也可以完成目的。但是这样做的目的与本例要求不相符合。

实际上我们也可以发现有些事情可以用简单的方法就能实现，只是操作要麻烦一些，对于有些“智能”的操作，比如本例改变关键字，结果自动随之改变的目的，还是需要更完美的函数公式来解决。

现在发现find函数一次比对一个单元格数据是无法满足要求的，但是find函数还具有一对多比对功能，就是本例中的用法FIND("学院", A2:A13)，一次比对12个单元格，结果是一个数组集合，这样我们可以一次性得到所有要比对的结果。

得到所有要比对的结果后，对于人工，直接找到对应的值放过来就行了，但是对于Excel来说不行，此时对于Excel只是得到了一个数组，这个数组包含12个值，值分为2类，一类是数字，也就是关键字在对应单元格出现的初始位置，代表该单元格包含关键字。一类是错误值“#VALUE!”，代表该单元格不包含关键字。

痛痛痛痛

★★特别强调：问题的关键是Excel软件只知道这有一组数据，并不知道这一组数据代表的意义，对于软件，数据是没有任何意义的，数据就是数据。但对于人来说，数据是有意义的，编写公式的人知道这个数据代表的意义。而编写公式的目的就是把我们认为有意义的数据找出来，也就是把我们的思维（解决方法）用软件能够处理的方式（Excel函数公式组合）表达出来，最后的结果还是数据，但是这些数据是我们思维的结果体现。

回过头来继续看公式FIND("学院", A2:A13)，得到一个数组，12个值，其中第1个和第12个是我们要的数据，中间10个值是我们不需要的数据。人的思维可以跳跃，但是软件是不能直接跳跃的，人知道第1个和第12个单元格数据就是我们要的数据，工作就完成了，但是Excel不知道，Excel也不知道什么时候工作完成，它只是按照我们的指令和操作进行处理并反馈最终数据结果。A列有12个数据，所以对比后最大的可能就是12个数据都符合要求，因此第一个公式写完后，我们需要往下拖动11个单元格，保持12个单元格进行公式计算。

现在问题成为：我需要第1个和第12个数据，其它中间的数据我不要了，利用公式下拉就是，第一个单元格C2中的公式，得到A2单元格数据，第二个单元格C3中的公式，得到A13单元格数据，第三个单元格C4公式的结果应该为空。

我们知道FIND("学院", A2:A13)得到的数据结果如下：

{13;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;15}

现在的目的是在12个数据值中找到符合要去的两个值，而且这两个值还有顺序关系，即从A2比对到A13，即对应相应的位置序号单元格。我们必然按照先后顺序依次找到这些符合要求的数据。也就是需要把这12个数据值转换成跟顺序有关的数据值，这12个数据是两类不同的数据，一类是数值，一类是错误值，处理判断很不方便，需要将其转换为相同性质的数据，这是一个问题，转换成相同的数据值后，并且还要能够按照顺序依次选择出第1个和第12个数据，即第一个公式选择出第1个数据，第2个公式选择出第2个数据，这是第二个问题。

先看第一个问题，将FIND("学院", A2:A13)得到的数据结果转换为相同性质的数据，这里应用了ISNUMBER函数

ISNUMBER({13;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;15})

计算后得到相同性质的数据，一组逻辑值数组

{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}

痛痛痛痛

现在看第二个问题，按照顺序依次选择出第1个和第12个数据，即第一个公式选择出第1个数据，第2个公式选择出第2个数据，逻辑值数据是没有顺序关系的，即使把true改成1，false改成0，数组{1;0;0;0;0;0;0;0;0;0;0;1}也是没有顺序关系的，本例中是使用if函数来处理这个问题，公式如下：

IF({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE},ROW(A2:A13),4^8)

里面ROW(A2:A13)，就是为了得到一个从小到大的序列值{2;3;4;5;6;7;8;9;10;11;12;13}。为什么要写成ROW(A2:A13)，因为刚好等于A列要比对的数据行号，等于数据行号便于提取对应的数据值到C列中。写成ROW(A1:A12)行不行？可以，但是公式也要稍微变一下。

4^8是一个大数字，这个大数字必须被要比对的数据行数要大，这里比对A列，A列只有12个数据，因此这个数字要比12大才行，又因为ROW(A2:A13)的最大数字是13，所以这个数字要大于13。

If函数的最终结果前面分析过了，如下：

{2;65536;65536;65536;65536;65536;65536;65536;65536;65536;65536;13}

数据变成有一定顺序了，符合要去的数据依次为2，13，有小到大，不符合要求的变成一个大数字。网上有另外一种写法，将不符合要求的也不变成大小不一致的数据，这里不说了。

现在第一个公式要得到数字2，下拉后第二个公式要得到数字13，就用small函数来解决。

SMALL({2;65536;65536;65536;65536;65536;65536;65536;65536;65536;65536;13},ROW(A1))

第一个公式用ROW(A1)=1，也就是选择数据中第一个最小值：2

下拉后第二个公式ROW(A1)变为ROW(A2)=2，也就是选择数据中第2个最小值：13

第一个公式得到2表示符合要求的单元格所在行，然后用index来提取数据

=INDEX(A:A,{2})，获得A2单元格的数据。

同理，下拉后第2个公式得到13表示符合要求的单元格所在行，然后用index来提取数据

同理，下拉后第3个公式得到65536表示符合要求的单元格所在行，然后用index来提取数据为0，显示0不符合要求，因此最后让：0 &""，产生空白单元格。

通过分析，对公式的用法也是获益良多，耗费了自己半天时间，自己才学疏浅，疏漏之处，大家见谅。

痛痛痛痛

现在看第二个问题，按照顺序依次选择出第1个和第12个数据，即第一个公式选择出第1个数据，第2个公式选择出第2个数据，逻辑值数据是没有顺序关系的，即使把true改成1，false改成0，数组{1;0;0;0;0;0;0;0;0;0;0;1}也是没有顺序关系的，本例中是使用if函数来处理这个问题，公式如下：

IF({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE},ROW(A2:A13),4^8)

里面ROW(A2:A13)，就是为了得到一个从小到大的序列值{2;3;4;5;6;7;8;9;10;11;12;13}。为什么要写成ROW(A2:A13)，因为刚好等于A列要比对的数据行号，等于数据行号便于提取对应的数据值到C列中。写成ROW(A1:A12)行不行？可以，但是公式也要稍微变一下。

4^8是一个大数字，这个大数字必须被要比对的数据行数要大，这里比对A列，A列只有12个数据，因此这个数字要比12大才行，又因为ROW(A2:A13)的最大数字是13，所以这个数字要大于13。

If函数的最终结果前面分析过了，如下：

{2;65536;65536;65536;65536;65536;65536;65536;65536;65536;65536;13}

数据变成有一定顺序了，符合要去的数据依次为2，13，有小到大，不符合要求的变成一个大数字。网上有另外一种写法，将不符合要求的也不变成大小不一致的数据，这里不说了。

现在第一个公式要得到数字2，下拉后第二个公式要得到数字13，就用small函数来解决。

SMALL({2;65536;65536;65536;65536;65536;65536;65536;65536;65536;65536;13},ROW(A1))

第一个公式用ROW(A1)=1，也就是选择数据中第一个最小值：2

下拉后第二个公式ROW(A1)变为ROW(A2)=2，也就是选择数据中第2个最小值：13

第一个公式得到2表示符合要求的单元格所在行，然后用index来提取数据

=INDEX(A:A,{2})，获得A2单元格的数据。

同理，下拉后第2个公式得到13表示符合要求的单元格所在行，然后用index来提取数据

同理，下拉后第3个公式得到65536表示符合要求的单元格所在行，然后用index来提取数据为0，显示0不符合要求，因此最后让：0 &""，产生空白单元格。

通过分析，对公式的用法也是获益良多，耗费了自己半天时间，自己才学疏浅，疏漏之处，大家见谅。

sbdk007

楼主辛苦了

micch

楼主辛苦了

jivy1212

很不错，但建议不引用整列，公式不卡比书写方便更重要些。