excel中返回一個區域的引用的函數有哪些？

以前一直覺得excel很簡單，后來深入學習發現excel真的是很有用，今天總結一下excel里常用的查找與引用函數。

1.vlookup函數 （lookup_value,table_array,col_index_num,[range_lookup]）

查找值一般在范圍的第一列，只能從左往右查

參數1：查找值

參數2：查找區域

參數3：返回查找區域內第幾列的數據

參數4：精確匹配/F 還是模糊匹配/T

例：查找各學生數學課的成績

查找值：姓名B2

查找范圍：B10：C14 固定范圍需要絕對引用

返回數據：數學成績在查找范圍的第二列，返回2

精確查找：F

2.OFFSET引用函數 --offset（reference，rows，cols，[height],[width]）

以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以為一個單元格/區域

參數1：基點 可隨機指定

參數2：行偏 基點與偏移后的基點的偏差 A3與C5的行偏為2

參數3：列偏 A3與C5的列偏為2

參數4：行高 需要偏移后的區域C5:E7 行高為3

參數5：列高 列高為3

注意：如果是多行多列，必須選中足夠的區域，按下ctrl+shift+enter

例：將A12：C17區域的數據移動到E18開始的單元格

3.match函數--match(lookup_value,lookup_array,[match_type])

知道這個值是多少，查它的位置

返回一個值的相對位置(數字)

參數1： 查找的內容

參數2：查找的范圍

參數3： 1--小于（向下查找最大值），0--精確匹配，-1--大于（向上查找最小值）

例：查找10在區域內的位置

=MATCH(A3,A1:A8,0) 10在該區域內的位置為3，則返回值3

查找兩列數據中的相同個數

4.index---index（array，row_num,[column_num]）

給定這個位置，查它的值是多少

參數1：范圍

參數2：相對行號

參數3：相對行號

例：查找A12：A19區域中第五行第一列的數據，位置是在該區域的相對位置。

indirect函數 找到單元格的地址 INDIRECT(ref_text, [a1])

5.累計函數

例：根據日銷量得到累計銷量

1.sum求和 SUM($B$2:B2)

2.offset求累計 問題：如何確定行高

ROW()引用行的數據 / COLUMN()引用列的數據

SUM(OFFSET($B$2,0,0,DAY(A2),1))

SUM(OFFSET($B$2,0,0,ROW(A1),1))

SUM(OFFSET($B$2,0,0,COUNT($B$2:B2),1))

6.案例分析

對員工入職表工齡及地區進行填充

1.根據身份證號得出員工出生日期----截取文本

mid（） 從字符串指定位置截取指定長度 text（）將數值轉換為文本形式

函數公式：TEXT(MID(D2,7,8),"#-00-00")*1 *1是將文本轉化為日期

2.根據出生日期計算員工年齡

函數：DATEDIF(start_date,end_date,unit)

Start_date 為一個日期，它代表時間段內的第一個日期或起始日期。(起始日期必須在1900年之后)

End_date 為一個日期，它代表時間段內的最后一個日期或結束日期。

Unit 為所需信息的返回類型。

計算公式：datedif(G2,TODAY(),"y")

3.根據省份證得到所屬省/自治區/直轄市

計算公式：vlookup(left(D2,2),全國地區表！A:B,2,F)

遇到的問題 只輸入上面的公式一直報錯，不知道問題是什么，后來發現是因為left截取的是文本，在函數中找值找不到，后來將文本改為數值型就可以了。

VLOOKUP(VALUE(LEFT(D2,2)),全國地區表!A:B,2,FALSE)

4.計算員工工齡

datedif（E2，today（），“y”）

5.計算不足一年的工齡

DATEDIF(E2,TODAY(),"ym")