一、Excel工作表函式:求和類。
(一)Sum。
功能:計算指定的單元格區域中所有數值的和。
語法結構:=Sum(值1,值2……值N)。
目的:計算總“月薪”。
方法:
在目標單元格中輸入公式:=SUM(1*G3:G12),並用Ctrl+ShIft+Enter填充。
解讀:
如果直接用Sum函式進行求和,結果為0,究其原因就在於“月薪”為文字型的數值,如果不想調整資料型別,可以給每個引數乘以1將其強制轉換為數值型別,然後用Sum函式進行求和。
(二)Sumif。
功能:對滿足條件的單元格求和,即單條件求和。
語法結構:=Sumif(條件範圍,條件,[求和範圍]),當“條件範圍”和“求和範圍”相同時,可以省略“求和範圍”。
目的:根據“性別”計算總“月薪”。
在目標單元格中輸入公式:=SUMIF(D3:D12,J3,G3:G12)。
由於“條件範圍”和“求和範圍”不相同,所以不能省略引數“求和範圍”。
(三)Sumifs。
功能:對一組給定條件指定的單元格求和。
語法結構:=Sumifs(求和範圍,條件1範圍,條件1,條件2範圍,條件2……)
目的:根據“性別”統計相應“學歷”下的總“月薪”。
在目標單元格中輸入公式:=SUMIFS(G3:G12,D3:D12,J3,F3:F12,K3)。
引數“條件範圍”和“條件”必須成對出現,否則公式無法正確執行!
二、Excel工作表函式:判斷類。
(一)If。
功能:判斷是否滿足某個條件,如果滿足返回一個值,如果不滿足則返回另一個值。
語法結構:=If(判斷條件,條件為真時的返回值,條件為假時的返回值)。
目的:判斷“月薪”的檔次,如果≥4000,則返回“高”,如果≥3000,則返回“中”,否則返回“低”。
在目標單元格中輸入公式:=IF(G3>=4000,"高",IF(G3>=3000,"中",IF(G3<3000,"低")))。
If函式除了單獨判斷外,還可以巢狀使用,但多級巢狀時,需要理清邏輯關係,否則容易出錯!
(二)Ifs。
功能:判斷是否滿足一個或多個條件並返回與第一個TRUE條件對應的值。
語法結構:=Ifs(條件1,返回值1,條件2,返回值2……)。
在目標單元格中輸入公式:=IFS(G3>=4000,"高",G3>=3000,"中",G3<3000,"低")。
引數中的“條件”和“返回值”必須成對出現,但該函式僅能應用於16及以上版本的Excel中,在WPS高版本中也可以使用哦!
三、Excel工作表函式:查詢類。
(一)Vlookup。
功能:搜尋表區域首列滿足條件的元素,確定待檢索單元格在區域中的的序號,再進一步返回選定單元格的值。
語法結構:=Vlookup(查詢值,資料範圍,返回值列數,查詢模式),查詢模式分為精準查詢和模糊查詢。
目的:根據“員工姓名”查詢對應的“月薪”。
在目標單元格中輸入公式:=VLOOKUP(J3,B3:G12,6,0)。
引數“返回值列數”要根據“資料範圍”來確定,是返回值所在的的相對列數。
(二)Lookup。
功能:從單行或單列或單陣列中查詢一個值。
Lookup函式具有兩種語法結構:向量形式和陣列形式。
1、向量形式。
功能:從單行或單列中查詢查詢指定的值,返回第二個單行或單列中相同位置的值。
語法結構:=Lookup(查詢值,查詢值所在範圍,[返回值所在範圍]),當“查詢值所在範圍”和“返回值所在範圍”相同時,可以省略“返回值所在範圍”。
1、以“員工姓名”為主要關鍵字進行升序排序。
2、在目標單元格中輸入公式:=LOOKUP(J3,B3:B12,G3:G12)。
在使用Lookup函式查詢資料時,首次要以“查詢值”為主要關鍵字進行升序排序,否則無法得到正確的結果。
2、陣列形式。
功能:從指定的範圍第一列或第一行中查詢指定的值,返回指定範圍中最後一列或最後一行對應位置上的值。
語法:=Lookup(查詢值,查詢範圍)。
重點解讀:
從“功能”中可以看出,Lookup函式的陣列形式,查詢值必須在查詢範圍的第一列或第一行中,返回的值必須是查詢範圍的最後一列或最後一行對應的值。即:查詢值和返回值在查詢範圍的“兩端”。
2、在目標單元格中輸入公式:=LOOKUP(J3,B3:G12)。
查詢值必須在資料範圍的第一列,返回值必須在資料範圍得最後一列。
3、最佳化形式。
在目標單元格中輸入公式:=LOOKUP(1,0/(B3:B12=J3),G3:G12)。
“最佳化形式”其本質還是向量形式,但在此必須瞭解Lookup函式的一個特定,就是當查詢不到指定的值時,會自動向下匹配,原則為小於當前值的最大值。如果公式中的條件不成立,則返回錯誤值,如果公式成立,則返回0,小於查詢值的最大值為0,所以返回相應位置的值。
四、Excel工作表函式:統計類。
(一)Countif。
功能:計算指定區域中的滿足條件的單元格數量,即單條件計數。
語法結構:=Countif(條件範圍,條件)。
目的:計算“月薪”在指定範圍內的人數。
在目標單元格中輸入公式:=COUNTIF(G3:G12,">"&J3)。
條件計數函式除了Countif函式外,還有多條件計數函式Countifs。
(二)Averageifs。
功能:查詢一組給定條件指定的單元格的算術平均值。
語法結構:=Averageifs(數值範圍,條件1範圍,條件1,條件2範圍,條件2……)
目的:根據“性別”統計相應“學歷”下的平均“月薪”。
在目標單元格中輸入公式:=AVERAGEIFS(G3:G12,D3:D12,J3,F3:F12,K3)。
引數“條件範圍”和“條件”必須成對出現。
五、Excel工作表函式:提取類。
(一)Left。
功能:從一個字串中的第一個字元開始返回指定個數的字元。
語法結構:=Left(字串,[字元長度]),當省略“字元長度”時,預設值為1。
目的:提取“員工姓名”中的“姓”。
在目標單元格中輸入公式:=LEFT(B3,1)。
也可以使用公式:=LEFT(B3)。
(二)Mid。
功能:從指定字串中的指定位置起返回指定長度的字元。
語法結構:=Mid(字串,開始位置,字元長度)。
目的:返回“月薪”中的第2、3位。
在目標單元格中輸入公式:=MID(G3,2,2)。
六、Excel工作表函式:日期類。
(一)Datedif。
功能:以指定的方式計算兩個日期之間的差值。
語法結構:=Datedif(開始日期,結束日期,統計方式),常見的統計方式有“Y”、“M”、“D”,即“年”、“月”、“日”。
目的:計算距離2021年元旦的天數。
在目標單元格中輸入公式:=DATEDIF(TODAY(),"2021-1-1","d")。
用Today函式獲取當前日期,計算距離2021年1月1日的天數。
(二)Days。
功能:返回兩個日期之間的天數。
語法結構:=Days(結束日期,開始日期)。
在目標單元格中輸入公式:=DAYS("2021-1-1",TODAY())。
Days函式的引數順序為“結束日期”、“開始日期”,而並不是“開始日期”、“結束日期”,和Datedif函式要區別使用!
七、Excel工作表函式:數字處理類。
(一)Round。
功能:按指定的位數對數值四捨五入。
語法結構:=Round(值或單元格引用,小數位數)。
目的:對“月薪”四捨五入後保留2位小數。
在目標單元格中輸入公式:=ROUND(G3,2)。
(二)Randbetween。
功能:返回介於指定的數值之間的隨機值。
語法結構:=Randbetween(下限值,上限值)。
目的:生成1000至2000之間的隨機值。
在目標單元格中輸入公式:=RANDBETWEEN(1000,2000)。
如果要更改隨機值,按F9即可。
八、Excel 工作表函式:資料庫函式。
(一)Dsum。
功能:求滿足給定條件的資料庫中記錄的欄位(列)資料的和。
語法結構:=Dsum(資料庫區域,返回值所在的相對列數(列標題的相對引用、列標題),條件區域)。
注意事項:
1、引數“資料庫區域”和“條件區域”必須包含有效的列標題。
2、第二個引數用“列標題”作為返回依據時,其值必須包含在""(英文雙引號)中,如“月薪”、“婚姻”等。
目的:根據“性別”統計“月薪”。
在目標單元格中輸入公式:=DSUM(B2:G12,"月薪",J2:J3)。
(二)Dget。
功能:從資料庫中提取符合指定條件且唯一存在的記錄。
語法結構:=Dget(資料庫區域,返回值所在的相對列數(列標題的相對引用、列標題),條件區域)。
在目標單元格中輸入公式:=DGET(B2:G12,"月薪",J2:J3)。
九、Excel工作表函式:其他類。
(一)Len。
功能:返回文字字串中的文字個數。
語法結構:=Len(值或單元格引用)。
目的:計算“員工姓名”的長度。
在目標單元格中輸入公式:=LEN(B3)。
(二)Cell。
功能:返回引用中第一個單元格的格式,位置或內容的有關資訊。
語法結構:=Cell(返回型別,[單元格區域])。
目的:顯示當前工作表的檔案路徑。
在目標單元格中輸入公式:=CELL("filename")。
一、Excel工作表函式:求和類。
(一)Sum。
功能:計算指定的單元格區域中所有數值的和。
語法結構:=Sum(值1,值2……值N)。
目的:計算總“月薪”。
方法:
在目標單元格中輸入公式:=SUM(1*G3:G12),並用Ctrl+ShIft+Enter填充。
解讀:
如果直接用Sum函式進行求和,結果為0,究其原因就在於“月薪”為文字型的數值,如果不想調整資料型別,可以給每個引數乘以1將其強制轉換為數值型別,然後用Sum函式進行求和。
(二)Sumif。
功能:對滿足條件的單元格求和,即單條件求和。
語法結構:=Sumif(條件範圍,條件,[求和範圍]),當“條件範圍”和“求和範圍”相同時,可以省略“求和範圍”。
目的:根據“性別”計算總“月薪”。
方法:
在目標單元格中輸入公式:=SUMIF(D3:D12,J3,G3:G12)。
解讀:
由於“條件範圍”和“求和範圍”不相同,所以不能省略引數“求和範圍”。
(三)Sumifs。
功能:對一組給定條件指定的單元格求和。
語法結構:=Sumifs(求和範圍,條件1範圍,條件1,條件2範圍,條件2……)
目的:根據“性別”統計相應“學歷”下的總“月薪”。
方法:
在目標單元格中輸入公式:=SUMIFS(G3:G12,D3:D12,J3,F3:F12,K3)。
解讀:
引數“條件範圍”和“條件”必須成對出現,否則公式無法正確執行!
二、Excel工作表函式:判斷類。
(一)If。
功能:判斷是否滿足某個條件,如果滿足返回一個值,如果不滿足則返回另一個值。
語法結構:=If(判斷條件,條件為真時的返回值,條件為假時的返回值)。
目的:判斷“月薪”的檔次,如果≥4000,則返回“高”,如果≥3000,則返回“中”,否則返回“低”。
方法:
在目標單元格中輸入公式:=IF(G3>=4000,"高",IF(G3>=3000,"中",IF(G3<3000,"低")))。
解讀:
If函式除了單獨判斷外,還可以巢狀使用,但多級巢狀時,需要理清邏輯關係,否則容易出錯!
(二)Ifs。
功能:判斷是否滿足一個或多個條件並返回與第一個TRUE條件對應的值。
語法結構:=Ifs(條件1,返回值1,條件2,返回值2……)。
目的:判斷“月薪”的檔次,如果≥4000,則返回“高”,如果≥3000,則返回“中”,否則返回“低”。
方法:
在目標單元格中輸入公式:=IFS(G3>=4000,"高",G3>=3000,"中",G3<3000,"低")。
解讀:
引數中的“條件”和“返回值”必須成對出現,但該函式僅能應用於16及以上版本的Excel中,在WPS高版本中也可以使用哦!
三、Excel工作表函式:查詢類。
(一)Vlookup。
功能:搜尋表區域首列滿足條件的元素,確定待檢索單元格在區域中的的序號,再進一步返回選定單元格的值。
語法結構:=Vlookup(查詢值,資料範圍,返回值列數,查詢模式),查詢模式分為精準查詢和模糊查詢。
目的:根據“員工姓名”查詢對應的“月薪”。
方法:
在目標單元格中輸入公式:=VLOOKUP(J3,B3:G12,6,0)。
解讀:
引數“返回值列數”要根據“資料範圍”來確定,是返回值所在的的相對列數。
(二)Lookup。
功能:從單行或單列或單陣列中查詢一個值。
Lookup函式具有兩種語法結構:向量形式和陣列形式。
1、向量形式。
功能:從單行或單列中查詢查詢指定的值,返回第二個單行或單列中相同位置的值。
語法結構:=Lookup(查詢值,查詢值所在範圍,[返回值所在範圍]),當“查詢值所在範圍”和“返回值所在範圍”相同時,可以省略“返回值所在範圍”。
目的:根據“員工姓名”查詢對應的“月薪”。
方法:
1、以“員工姓名”為主要關鍵字進行升序排序。
2、在目標單元格中輸入公式:=LOOKUP(J3,B3:B12,G3:G12)。
解讀:
在使用Lookup函式查詢資料時,首次要以“查詢值”為主要關鍵字進行升序排序,否則無法得到正確的結果。
2、陣列形式。
功能:從指定的範圍第一列或第一行中查詢指定的值,返回指定範圍中最後一列或最後一行對應位置上的值。
語法:=Lookup(查詢值,查詢範圍)。
重點解讀:
從“功能”中可以看出,Lookup函式的陣列形式,查詢值必須在查詢範圍的第一列或第一行中,返回的值必須是查詢範圍的最後一列或最後一行對應的值。即:查詢值和返回值在查詢範圍的“兩端”。
目的:根據“員工姓名”查詢對應的“月薪”。
方法:
1、以“員工姓名”為主要關鍵字進行升序排序。
2、在目標單元格中輸入公式:=LOOKUP(J3,B3:G12)。
解讀:
查詢值必須在資料範圍的第一列,返回值必須在資料範圍得最後一列。
3、最佳化形式。
目的:根據“員工姓名”查詢對應的“月薪”。
方法:
在目標單元格中輸入公式:=LOOKUP(1,0/(B3:B12=J3),G3:G12)。
解讀:
“最佳化形式”其本質還是向量形式,但在此必須瞭解Lookup函式的一個特定,就是當查詢不到指定的值時,會自動向下匹配,原則為小於當前值的最大值。如果公式中的條件不成立,則返回錯誤值,如果公式成立,則返回0,小於查詢值的最大值為0,所以返回相應位置的值。
四、Excel工作表函式:統計類。
(一)Countif。
功能:計算指定區域中的滿足條件的單元格數量,即單條件計數。
語法結構:=Countif(條件範圍,條件)。
目的:計算“月薪”在指定範圍內的人數。
方法:
在目標單元格中輸入公式:=COUNTIF(G3:G12,">"&J3)。
解讀:
條件計數函式除了Countif函式外,還有多條件計數函式Countifs。
(二)Averageifs。
功能:查詢一組給定條件指定的單元格的算術平均值。
語法結構:=Averageifs(數值範圍,條件1範圍,條件1,條件2範圍,條件2……)
目的:根據“性別”統計相應“學歷”下的平均“月薪”。
方法:
在目標單元格中輸入公式:=AVERAGEIFS(G3:G12,D3:D12,J3,F3:F12,K3)。
解讀:
引數“條件範圍”和“條件”必須成對出現。
五、Excel工作表函式:提取類。
(一)Left。
功能:從一個字串中的第一個字元開始返回指定個數的字元。
語法結構:=Left(字串,[字元長度]),當省略“字元長度”時,預設值為1。
目的:提取“員工姓名”中的“姓”。
方法:
在目標單元格中輸入公式:=LEFT(B3,1)。
解讀:
也可以使用公式:=LEFT(B3)。
(二)Mid。
功能:從指定字串中的指定位置起返回指定長度的字元。
語法結構:=Mid(字串,開始位置,字元長度)。
目的:返回“月薪”中的第2、3位。
方法:
在目標單元格中輸入公式:=MID(G3,2,2)。
六、Excel工作表函式:日期類。
(一)Datedif。
功能:以指定的方式計算兩個日期之間的差值。
語法結構:=Datedif(開始日期,結束日期,統計方式),常見的統計方式有“Y”、“M”、“D”,即“年”、“月”、“日”。
目的:計算距離2021年元旦的天數。
方法:
在目標單元格中輸入公式:=DATEDIF(TODAY(),"2021-1-1","d")。
解讀:
用Today函式獲取當前日期,計算距離2021年1月1日的天數。
(二)Days。
功能:返回兩個日期之間的天數。
語法結構:=Days(結束日期,開始日期)。
目的:計算距離2021年元旦的天數。
方法:
在目標單元格中輸入公式:=DAYS("2021-1-1",TODAY())。
解讀:
Days函式的引數順序為“結束日期”、“開始日期”,而並不是“開始日期”、“結束日期”,和Datedif函式要區別使用!
七、Excel工作表函式:數字處理類。
(一)Round。
功能:按指定的位數對數值四捨五入。
語法結構:=Round(值或單元格引用,小數位數)。
目的:對“月薪”四捨五入後保留2位小數。
方法:
在目標單元格中輸入公式:=ROUND(G3,2)。
(二)Randbetween。
功能:返回介於指定的數值之間的隨機值。
語法結構:=Randbetween(下限值,上限值)。
目的:生成1000至2000之間的隨機值。
方法:
在目標單元格中輸入公式:=RANDBETWEEN(1000,2000)。
解讀:
如果要更改隨機值,按F9即可。
八、Excel 工作表函式:資料庫函式。
(一)Dsum。
功能:求滿足給定條件的資料庫中記錄的欄位(列)資料的和。
語法結構:=Dsum(資料庫區域,返回值所在的相對列數(列標題的相對引用、列標題),條件區域)。
注意事項:
1、引數“資料庫區域”和“條件區域”必須包含有效的列標題。
2、第二個引數用“列標題”作為返回依據時,其值必須包含在""(英文雙引號)中,如“月薪”、“婚姻”等。
目的:根據“性別”統計“月薪”。
方法:
在目標單元格中輸入公式:=DSUM(B2:G12,"月薪",J2:J3)。
(二)Dget。
功能:從資料庫中提取符合指定條件且唯一存在的記錄。
語法結構:=Dget(資料庫區域,返回值所在的相對列數(列標題的相對引用、列標題),條件區域)。
注意事項:
1、引數“資料庫區域”和“條件區域”必須包含有效的列標題。
2、第二個引數用“列標題”作為返回依據時,其值必須包含在""(英文雙引號)中,如“月薪”、“婚姻”等。
目的:根據“員工姓名”查詢對應的“月薪”。
方法:
在目標單元格中輸入公式:=DGET(B2:G12,"月薪",J2:J3)。
九、Excel工作表函式:其他類。
(一)Len。
功能:返回文字字串中的文字個數。
語法結構:=Len(值或單元格引用)。
目的:計算“員工姓名”的長度。
方法:
在目標單元格中輸入公式:=LEN(B3)。
(二)Cell。
功能:返回引用中第一個單元格的格式,位置或內容的有關資訊。
語法結構:=Cell(返回型別,[單元格區域])。
目的:顯示當前工作表的檔案路徑。
方法:
在目標單元格中輸入公式:=CELL("filename")。