excel常用函数-查找、线性插值等

ref: excel_formulas_and_functions

0 示例表格

	A	B	C
1	NO	key	value
2	1	0	0
3	2	5	25
4	3	10	100
5	4	15	225
6	5	20	400
7	6	25	625
8	7	30	900
9	8	35	1225
10	9	40	1600
11	10	45	2025

1 已知key，查找对应value

• VLOOKUP(lookup value, lookup array, column, range lookup)

	F	G	H
4	key	formula	value
5	20	=VLOOKUP(F5,B2:C11,2)	400
6	25	=VLOOKUP(F6,B2:C11,2,FALSE)	625
7	24	=VLOOKUP(F7,B2:C11,2)	400
8	24	=VLOOKUP(F8,B2:C11,2,FALSE)	#N/A

2 已知value，查找对应序号

• MATCH(lookup_value, lookup_array, [match_type])
  • match_type：0 精确；1小于；-1大于

	F	G	H
4	value	formula	NO
5	400	=MATCH(F5,C2:C11)	5
6	500	=MATCH(E6,C2:C11,0)	#N/A
7	500	=MATCH(F7,C2:C11,1)	5

3 已知在数组中的序号，查找value

• INDEX(array, row number )

	F	G	H
4	NO	formula	value
5	5	=INDEX(C2:C11,F5)	400
6	6	=INDEX(C2:C11,F6)	625

4 已知 value1，查找偏移n_rows行，n_cols列后对应的 value2

• OFFSET(reference, rows, cols, [height], [width])
• value1 设为数组的表头A1, 即参数 reference

	F	G	H	I
4	n_rows	n_clns	formula	value2
5	5	1	=OFFSET(A1,F5,G5)	20
6	5	2	=OFFSET(A1,F6,G6)	400

5 线性趋势

• TREND(known_y's, [known_x's], [new_x's], [const])

	F	G	H
4	NO	formula	key
5	4	=TREND(B2:B11,A2:A11,K5)	15
6	4.5	=TREND(B2:B11,A2:A11,K6)	17.5

6 线性插值

• 线性插值需要用到3个组合函数
• MATCH(key,x_s)
• OFFSET(y_title,MATCH(key,x_s),,2)
• OFFSET(x_title,MATCH(key,x_s),,2)
• TREND(OFFSET(y_title,MATCH(key,x_s),,2),OFFSET(x_title,MATCH(key,x_s),,2),key)

	F	G	H
4	NO	key	value
5	20	=TREND(OFFSET(C1,MATCH(K5,B2:B11),,2),	400
		OFFSET(B1,MATCH(K5,B2:B11),,2),K5)
6	24	=TREND(OFFSET(C1,MATCH(K6,B2:B11),,2),	580
		OFFSET(B1,MATCH(K6,B2:B11),,2),K6)
7	25	=TREND(OFFSET(C1,MATCH(K7,B2:B11),,2),	625
		OFFSET(B1,MATCH(K7,B2:B11),,2),K7)