说在前面的话:
要看懂或验证一个公式, F9这个键必不可少, 它的作用是即时显示计算结果.
比如第一个公式, 在公式编辑栏将 4^8 用鼠标左键选中, 按 F9会显示65536, 也就是 4^8 的计算结果, 如再想看其它部分的结果, 继续选中按F9即可.
需要注意的是, 看过结果后要及时按EXC返回, 否则原公式就改变了.
1. 先说一下这个65536 , 它是4的8次方, 计算结果为65536. 早期的EXCEL都是2000或2003版的, 这个65536就是当时EXCEL行数的极限值, 一般很少有数据会写到这一行,所以在引用不满足条件的单元格时就将其指向65536这一行, 意思是取 B 列的最后一行的值, 当然这个值是空值, 我们要的就是这个. 现在随着EXCEL版本的不断提高, 最大行已经可以达到4^10 (1048576) 了, 不过 4^8 这个书写习惯却被保留了下来.
2. =INDEX(SHEET2!$B:$B,SMALL(IF(COUNTIF(A$1,SHEET2!A$1:A$1000)>0,ROW($1:$1000),4^8),ROW(A1)))&""
公式作用是提取SHEET2! B列满足SHEET2!A列等于当前A1值的数据
应用于下拉
从里向外看:
2.1 COUNTIF(A$1,SHEET2!A$1:A$1000)>0
COUNTIF(A$1,SHEET2!A$1:A$1000)作用是判断SHEET2!A$1:A$1000区域中每个单元格的值是否与A$1相等, 折开了就是
COUNTIF(A$1,SHEET2!A$1)
COUNTIF(A$1,SHEET2!A$2)
……
COUNTIF(A$1,SHEET2! A$1000)
COUNTIF(A$1,SHEET2!A$1:A$1000)>0, 说明第ROW($1:$1000)次比较成立
COUNTIF(A$1,SHEET2!A$1:A$1000)=0, 说明第ROW($1:$1000)次比较不成立
2.2 ROW($1:$1000)
选中并按F9可以看到这段函数的作用是形成 1-1000的一个一维数组, {“1”,”2”,”3”,…,”1000”}
它对应于COUNTIF(A$1,SHEET2!A$1:A$1000) 中的A$1:A$1000, COUNTIF的每一个结果它都会一一对应,怎么说呢? 即第1次COUNTIF返回值 =0 , 这次结果由ROW记录为1, 第2次COUNTIF返回值>0, 由ROW记录为2, ……
2.3 4^8
这个就不用说了, 它就是一个数, 一个极大到几乎不可能的数, 不满足条件就等于它, 反正它一般都是空值
2.4 IF(COUNTIF(A$1,SHEET2!A$1:A$1000)>0,ROW($1:$1000),4^8)
外面套个IF函数会得到一个满足条件与不满足条件的记录号的数组, 如上面例子:
COUNTIF(A$1,SHEET2!A$1)=0
COUNTIF(A$1,SHEET2!A$2)>0
……
COUNTIF(A$1,SHEET2! A$1000)=0
在IF的作用下会形成 {“65536”,”2”,…,”65536”} 这个数组, 也就是说
COUNTIF(A$1,SHEET2!A$1)=0 这个不满足相等条件的记录被IF指定记录号为4^8(65536),其它符合相等条件的被ROW指定为对应的序号
2.5 SMALL(IF(…),ROW(A1))
这段要取出满足条件的第ROW(A1)个最小序号, 因为INDEX的第二个参数只能是一个确定的数值, 所以SMALL可以使其得到满足相等条件的序号. 很明显, 满足相等条件的序号是由ROW(1:1000)生成的, 肯定要比4^8 (65536) 这个数要小.
2.6 = INDEX(SHEET2!$B:$B,SMALL(…))
做了那么多, 其实最终就是为了得到INDEX的第二个参数, 如SMALL(…)最终得到3, 那么INDEX负责从SHEET2的B列取出第3行即SHEET2!B3的数据
2.7 =INDEX(…)&””
最后这个&”” , 其作用是当INDEX返回B(65536)时出现0时, 将0转成””(空), 不显示0值.
3.
=INDIRECT("sheet1!b"&SMALL(IF($A1=Sheet1!$A$1:$A$100,ROW($A$1:$A$100),65536),COLUMN(A1)))
通过上面解释, 这个公式应该不难理解了, 不同的是它应用于右拉
其作用是引用SHEET1!B?单元格的数据, 这个?的值取决于SMALL(…)这段, 这是这个公式的关键, 中间部分就不再解释了, 说一下SMALL的第二个参数为什么不再用ROW而换成COLUMN.
因为这个公式要右拉, 要使公式随右拉时结果变化, 显然ROW就达不到目的了, COLUMN则可以在右拉时同样形成 {“1”,”2”,”3”,…,”1000”} 这个1-1000的数组
表达能力不好, 也不知道说明白没有.
4^8=65536，代表EXCEL最大的行（03版）
两个IF语句返回的结果都是一个数组，数组元素由满足条件的对应行号和n个65536组成
再通过SMALL函数取出数组中的对应元素
1式中的index有时会返回错误值(主要由countif引起)，通过&""可以将错误值转为空值
你可以试试取消&""，看看结果
1，4^8是四的八次方，也就表示一个很大的数。""是为了改变单元格的格式，前面是个数字，但希望以文本格式显示
2,65536是Excel支持的最大行数，也就是ROW函数能返回的最大值
4的8次方
&“” -----连接一个空格
表格的最大行数
内容全部来源于网络收集，如有侵权，请联系网站删除：QQ：24596024