佛山小老鼠精华帖整理

按情景EXCEL函数

1. 根据某列值查找同行另一列的值
2. Vlookup与Hlookup函数比较
3. lookup函数应用实例
4. 隔行填色
5. 人民币金额的小写转大写
6. 数组公式-从文本里提取数字

1.根据某列值查找同行另一列的值

根据在某列(如C列)查找到的值获取同行另一列的值，不管该列在C列左侧还是右侧,以下提供两个公式：Vlookup公式，index和match组合公式。

=VLOOKUP($B$56,IF({1,0},C47:C52,B47:B52),2,0)
采用IF函数重新构建查找区域
=INDEX($B$46:$E$52,MATCH($E$60,$C$46:$C$52,0),MATCH(F59,$B$46:$E$46,0))
match函数确定查找区域的行号和列号. 公式好理解，优先采用。

2. Vlookup与Hlookup函数比较

• Vlookup函数先垂直搜索确定行，再确定列，最终确定查找结果。
• Hlookup函数先水平搜索确定列，在确定行，最终确定查找结果。

3. lookup函数应用实例

• lookup函数的参数有二种形式，一是向量，二是数组
• 如果是向量，一定要先升序排序第二参数
• 向量：第一参：查找值，第二参查找值所在的区域，第三参返回的结果
• 数组：第一参：查找值，第二参：查找区域是数组

实例一： 根据分数算成绩（数组式）

=LOOKUP(L14,{0,60,70,80;"不及格","及格","良好","优秀"})
第二个参数是个2*1的数组，第1行为查找值所在区间，升序排序，查找值区间左闭右开    

实例二： 提取一行最后一个非空单元格的数据(向量式)

=LOOKUP(1,0/(C27:K27<>""),C27:K27)
如果查找值在查找区域里有许多个时，就返回最后一个，然后在第三参数相应的位置找到查找的结果

实例三：没有排序时的lookup查找（向量式）

=LOOKUP(1,0/($B$39:$B$44=$H39),C$39:C$44)
第二个参数确定查找值位置，第三个为向量

4. 隔行填色

选择条件格式要作用的单元格区域，采用如下公式：

=MOD(ROW(),2)=0
mod取模值

5. 人民币金额的小写转大写

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
TEXT(F10*100,"[Dbnum2]0百0拾0元0角0分"),"零百",""),"零拾",
""),"零元",""),"零角",""),"零分","")

6. 数组公式-从文本里提取数字

E7单元格内容：吃饭15元。要在E7单元格中提取数字“15”。在E8中输入数组公式：

=--MID(D7,MIN(FIND(ROW($A$1:$A$10)-1,D7&5/19,1)),LEN(D7)-(LENB(D7)-LEN(D7)))
公式理解：
1. 同时按CTRL+SHIFT+ENTER使数组公式生效 
2. "ROW($A$1:$A$10)-1"生成一个{0，1,2,3....9}的数组
3. "D7&5/19" 为了防止因找不到数字产生的错误,5/19会产生包含0-9的数字。怎么记忆“我要喝酒”
4. LENB函数，1个汉子算2(2字节)，字母、数字算1。LEN函数，汉子、数字、字母个数均按1算。
5. 公式前的"--"目的是为了将文本转为数字