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])
|
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) |
|
