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

发表于 分类于 本文字数： 426 阅读时长 ≈ 2 分钟

ref: excel_formulas_and_functions, https://support.office.com/en-us/article/overview-of-formulas-in-excel-ecfdc708-9162-49e8-b993-c311f47ca173?wt.mc_id=fsn_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 Formula 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)