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) |
网友评论