Excel資料分析入門-函式和公式

江米小棗tonylua發表於2017-09-21
Excel函式公式

I. 使用公式

公式是指使用運算子和函式，對工作表資料以及普通常量進行運算的方程式

=SUM(A1:B5)

【一個公式的組成】

  • 等號=：表示之後的字元為公式
  • 運算子：+ - 等算數運算子，或: ,等對單元格的引用運算子
  • 函式：一些預定義的計算方法
  • 單元格引用：
  • 常量：參與計算的常數

【簡單公式的基本用法】

  1. 準備好一組資料，比如從A1到B5的單元格內輸入一組數字
  2. 單擊某個空白單元格，作為函式計算值輸出的目標單元格
  3. 在“公式”工具欄中，選擇第一項“自動求和/平均值/最大值...”中的一項
  4. 把介面出現的藍色選框調整為需要統計的範圍
  5. 雙擊空白單元格或回車，目標單元格中即出現計算結果

1.1 - 公式中的運算子

Excel公式中包含四種運算子：算術、比較、文字、引用

算術運算子 含義 舉例
+ 加 1+1
- 負號/減 -1 或 2-1
* 乘 2*2
/ 除 4/2
% 百分比 10%
^ 乘冪 10^2（10的兩次方，也就是100）
比較運算子 含義 舉例
= 等於 A1=B1
> 大於 A1>B1
< 小於 A1<B1
>= 大於等於 A1>=B1
<= 小於等於 A1<=B1
<> 不等於 A1<>B1
文字運算子 含義 舉例
& 連字元 "hello"&" world"&"!" 產生 "hello world!"
引用運算子 含義 舉例
: 區域 A1:B2 表示A1、A2、B1、B2四個單元格
, 聯合 SUM(A1:B2,A4:B4) 表示聯合計算兩個區域的總和
空格 交叉 SUM(A1:C2 B2:D2) 計算兩個區域的交集，也就是B2:C2的總和

1.2 - 對單元格的引用

通過引用，可以在公式中使用指定單元格中的資料

【按地址引用】

方法 舉例
單個 A1
多個 A1,B2,C3
按行 5:10
按列 A:A
區域 A1:B2
交叉 A1:C2 B:B
跨工作表 SUM(B1,sheet2!A1:B1) --- 對B1和sheet2中的A1:B1
或 SUM(sheet1:sheet3!A1) --- 對sheet1:sheet3中的所有A1
跨檔案 SUM(B1,'/Path/[excel2.xlsx]工作表1'!A1)
  • 注意其中如果選擇單行或單列要用 x:x 的形式
  • 如果工作表名稱或檔名稱中包含空格，則必須用引號包住

【相對引用、絕對引用、混合引用】

image
image

[圖 1.2]

  • 相對引用
    • 形式：A1:D9 A1,B1 等
    • 定義：對於定義好的公式，在挪動單元格或複製公式時，公式中的引用地址會跟隨變化
    • 對於A1,B1，將B1單元格中的值拖動到E5後，公式自動變為A1,E5
    • 對於定義在目標單元格B19中的公式A1:B1，將其複製到B12中後，公式按相對位置自動變為A2:B2
  • 絕對引用
    • 形式：$A$1:$D$9 $A$1,$B$1 等
    • 定義：對於定義好的公式，其單元格引用不會發生變化
  • 混合引用
    • 形式：A$1:$D$9 $A1,$B$1 等
    • 結合了相對和絕對引用，在行或列的方向上採用相應的引用方式

1.3 - 公式錯誤值

如果輸入的公式出錯，會在目標單元格顯示錯誤值

image
image

[圖 1.3]

錯誤值 產生的原因
#####! 計算結果過長，單元格容納不下；增加列寬可解決
#DIV/0 除數為0，或公式被空單元格除
#N/A 公式中無可用數值，或缺少函式引數
#NAME? 引用了無法識別的名稱
#NULL! 使用了錯誤的區域運算子，或錯誤的單元格引用
#NUM! 在需要數字引數的地方使用了無法接受的引數，或計算結果過大過小
#RFF! 引用了無效的單元格
#VALUE! 公式中含有一個錯誤型別的引數或運算元

II. 常用Excel函式

2.1 - 最常用函式

2.1.1 - SUM函式

對單元格中的值求和

SUM(number1,[number2],...)

image
image

[圖 2.1.1]

引數名稱 是否必須 說明
number1 必須 要相加的第一個數字。該數字可以是 4 之類的數字，B6 之類的單元格引用或 B2:B8 之類的單元格範圍
number2-255 可選 要相加的第二個數字。可以按照這種方式最多指定 255 個數字

舉例：

//統計一個區域中的值總和
SUM(A2:A10)

//從兩個區域中統計總和
SUM(A2:A10,C2:C10)

//統計命名過的不同工作表中同一單元格的總和
SUM('一月銷售 工作表:十二月銷售 工作表'!A2)

//加減乘除
SUM(A1:A10)+B1
SUM(A1:A10)-10
SUM(A1:A10)*B1
SUM(A1:A10)/10

//結合其他函式，計算月平均值
//用SUM選取1至12月的值總和
//除以相應的月數(COUNTA取得範圍內非空單元格的個數)
`SUM(A2:L2)/COUNTA(A2:L2)`複製程式碼

2.1.2 - IF函式

對值和期待值進行邏輯比較

IF(logical_test, value_if_true, [value_if_false])

image
image

[圖 2.1.2]

引數名稱 是否必須 說明
logical_test 必須 比較條件
value_if_true 必須 logical_test 為真時的返回值
value_if_false 可選 logical_test 為假時的返回值

舉例：

IF(C2=”Yes”,1,2)
IF(C2=100,”Yes”,”No”)
IF(C2>B2,”預算超標”,”預算內”)
IF(C2>B2,C2-B2,0)

//結合其他函式，判斷單元格是否為空
IF(ISBLANK(D2),"未填寫","已填寫")

//巢狀使用
IF(D2=1,”YES”,IF(D2=2,”No”,”Maybe”))複製程式碼

2.1.3 - LOOKUP函式

查詢一行或一列並查詢另一行或列中的相同位置的值

LOOKUP(lookup_value, lookup_vector, [result_vector])

image
image

[圖 2.1.3]

引數名稱 是否必須 說明
lookup_value 必須 要搜尋的基準值，可以是數字、文字、邏輯值、名稱或對值的引用
lookup_vector 必須 基準值所在的區域，只能包含一行或一列
result_vector 可選 目標值所在的區域，只能包含一行或一列

舉例：

//比如A3的值是99，則顯示對應的C3的值
LOOKUP(99,A1:A9,C1:C9)複製程式碼

2.1.4 - CHOOSE函式

從最多 254 個數值中選擇一個

CHOOSE(index_num, value1, [value2], ...)

image
image

[圖 2.1.4]

引數名稱 是否必須 說明
index_num 必須 指定所選定的數值引數 或 公式 或 單元格引用，範圍在1-256之間
value1, value2, ... 必須 從中選擇一個數值或一項要執行的操作

舉例：

//第二個列表引數的值（單元格 A3 中的值）
CHOOSE(2,A2,A3,A4,A5)

//第三個列表引數的值 "world"
CHOOSE(3,"Wide",115,"world",8)

//相當於：SUM(B1:B10)
SUM(CHOOSE(2,A1:A10,B1:B10,C1:C10))

//計算單元格區域 A2:A4 中所有數值的和
SUM(A2:CHOOSE(2,A3,A4,A5))

//根據分數取得一個索引值，進而獲得對應的評價
CHOOSE(IF(B2>=90,1,IF(B2>=60,2,3)),"優","合格","差")複製程式碼

2.1.5 - DATE 函式

根據三個單獨的年月日值並將它們合併為一個日期

DATE(year,month,day)

image
image

[圖 2.1.5]

舉例：

//將單元格 C2 中的年2017、單元格 A2 中的月01 以及單元格 B2 中的日02 合併在一起
//目標單元格中按照設定的日期格式顯示為2017/1/2
DATE(C2,A2,B2) 

//結合 YEAR、MONTH 和 DAY 函式，建立基於另一個日期單元格的新日期
DATE(YEAR(C2)+5,MONTH(C2),DAY(C2))

//結合 LEFT、MID 和 RIGHT 函式，將字串'20170102'轉換為日期
DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2))

//如果已有日期單元格A1，則可在目標單元格中對齊加減指定天數，直接獲得新日期
A1+7複製程式碼

2.1.6 - DAYS 函式

返回兩個日期之間的天數

DAYS(end_date, start_date)

image
image

[圖 2.1.6]

  • 如果兩個日期引數為數字，DAYS 使用 EndDate–StartDate 計算兩個日期之間的天數
  • 如果任何一個日期引數為文字，該引數將被視為 DATEVALUE(date_text) 並返回整型日期，而不是時間元件
  • 如果日期引數是超出有效日期範圍的數值，DAYS 返回 #NUM! 錯誤值
  • 如果日期引數是無法解析為字串的有效日期，DAYS 返回 #VALUE! 錯誤值

舉例：

//直接在函式中輸入日期時，需要將其用引號引起
DAYS("2011-3-15","2011-2-1") 

//查詢 A2 中的結束日期和 A3 中的開始日期之間的天數
DAYS(A2,A3)複製程式碼

2.1.7 - FIND 函式

用於在第二個文字串中定位第一個文字串，並返回第一個文字串的起始位置的值，該值從第二個文字串的第一個字元算起

FIND(find_text, within_text, [start_num])

image
image

[圖 2.1.7]

引數名稱 是否必須 說明
find_text 必須 要查詢的文字
within_text 必須 可能包含要查詢文字的目標文字
start_num 可選 指定開始進行查詢的字元索引值，預設為1
  • FIND區分大小寫，並且不允許使用萬用字元（相反，SEARCH就沒有這些限制）
  • 如果 find_text 為空文字 ("")，則 FIND 會匹配搜尋字串中的首字元（即編號為 start_num 或 1 的字元）
  • 如果 within_text 中沒有 find_text、或 start_num 不大於 0、或 start_num 大於 within_text 的長度，則返回 錯誤值 #VALUE!

舉例：

//單元格 A2 中第一個“M”的位置
FIND("M",A2)

//從單元格 A2 的第三個字元開始查詢第一個“M”的位置
FIND("M",A2,3)

//結合MID函式，提取單元格 A2 中從第一個字元到“#”之前的文字
MID(A2,1,FIND("#",A2,1)-1)複製程式碼

2.1.8 - INDEX 函式

返回表格或區域中的值或值的引用

image
image

[圖 2.1.8.1]

陣列形式：INDEX(array, row_num, [column_num])

引數名稱 是否必須 說明
array 必須 單元格區域或陣列常量
row_num 必須 選擇陣列中的某行，函式從該行返回數值
column_num 可選 選擇陣列中的某列，函式從該列返回數值

image
image

[圖 2.1.8.2]

引用形式: INDEX(reference, row_num, [column_num], [area_num])

引數名稱 是否必須 說明
reference 必須 對一個或多個單元格區域的引用
row_num 必須 引用中某行的行號，函式從該行返回一個引用
column_num 可選 引用中某列的列標，函式從該列返回一個引用
area_num 可選 在引用中選擇要從中返回 Row_num 和 Column_num 的交叉處的區域

舉例：

//位於區域 A2:B3 中第二行和第二列交叉處的數值
INDEX(A2:B3,2,2)

//陣列的第一行、第二列中找到的數值，結果也就是 2
INDEX({1,2;3,4},0,2)

//返回以單元格 B2 開始到單元格區域 A2:A6 中第五行和第二列交叉處結束的單元格區域的和
SUM(B2:INDEX(A2:C6, 5, 2))

//引用形式：第二個區域 A8:C11 中第二行和第二列的交叉處
INDEX((A1:C6, A8:C11), 2, 2, 2)複製程式碼

III. 統計分析函式實戰

使用統計分析函式，能從複雜繁瑣的資料中，提取和篩選需要的資料

3.1 自動插入球隊排名

image
image

[圖3.1]

RANK.EQ(L16,$L$16:$L$25)

涉及函式：

  • RANK.EQ 函式：返回某數字在一列數字中相對於其他數值的大小排名；如果多個數值排名相同，則返回該組數值的最佳排名

思路解析:

  1. 新建一列放置排名
  2. RANK.EQ 函式取得表格中第一個球隊的排名值
  3. 向下拖動，快速複製出所有球隊的排名，因為第一個引數L16是相對值，所以其他單元格也自動更新為對應值

3.2 統計排名前3球隊的平均積分

image
image

[圖3.2]

AVERAGE(LARGE($L$16:$L$25,{1;2;3}))

涉及函式：

  • LARGE 函式：返回資料集中第 k 個或前 k 個最大值
  • AVERAGE 函式：返回其引數的平均值

思路解析:

  1. 利用 LARGE 函式求得前 3 名的值
  2. 用 AVERAGE 函式求均值
  3. 將 LARGE 換成 SMALL，就能得到後3名的平均分

3.3 拼接排名前3球隊的名稱

image
image

[圖3.3]

CONCATENATE(LOOKUP(1,$M$16:$M$25,$G$16:$G$25),",",LOOKUP(2,$M$16:$M$25,$G$16:$G$25),",",LOOKUP(3,$M$16:$M$25,$G$16:$G$25))

涉及函式：

  • CONCATENATE 函式：將多個文字字串合併成一個
  • LOOKUP 函式：從單行或單列或從陣列中查詢一個值

思路解析:

  1. 將3.1中的排名列，設定為文字格式，以符合LOOKUP的要求
  2. 根據此列，分別求出1至3名對應的隊名 LOOKUP(n,$M$16:$M$25,$G$16:$G$25)
  3. 用CONCATENATE函式，拼合隊名和分隔用的逗號

3.4 取得低於35分球隊的最高分

image
image

[圖3.4]

LOOKUP(COUNTIF($L$16:$L$25,">=35")+1,$M$16:$M$25,$L$16:$L$25)

涉及函式：

  • LOOKUP 函式：查詢一行或一列並查詢另一行或列中的相同位置的值
  • COUNTIF 函式：計算某個區域中滿足給定條件的單元格數目

思路解析:

  1. 用COUNTIF算出區域中大於等於35分的球隊個數
  2. 在排名列中找出比此個數大1的那個排名，並由LOOKUP在分數列中找到對應的分數

3.5 利用簡單的VBA統計低分球隊

函式的功能還是有所限制，而VBA指令碼則能極大的豐富我們的能力。這裡演示用一段簡單的VBA實現幾個球隊名拼接的功能，和3.3中的函式相比，明顯邏輯清楚和自動化了很多

image
image

[圖3.5]

01| Public Function TailTeams()
02|    Dim TNames As String '定義一個字串遍歷
03|    For Each c In Worksheets("工作表1").Range("$L$16:$L$25").Cells '遍歷分數列
04|        If c.Value < 35 Then '如果滿足條件
05|            TNames = TNames & ActiveSheet.Range("$G$" & c.Row).Cells(1).Value
06|            TNames = TNames & "," '用逗號分隔
07|        End If
08|    Next
09|    TNames = Left(TNames, Len(TNames) - 1) '截掉最後一位多餘的逗號
10|    TailTeams = TNames '返回值
11| End Function複製程式碼

注意事項：

  • 在Excel偏好設定中，找到檢視一項，開啟“開發工具”選項卡
  • 儲存檔案時選擇允許巨集的xslm格式

這裡僅做基本演示，具體功能步驟和更詳細的VBA知識請自行深入學習

3.6 統計有效調查問卷

image
image

[圖 3.6.1]

  • COUNTA函式：計算區域中非空單元格的個數

image
image

[圖 3.6.2]

  • COUNTBLANK函式：計算區域中空單元格的個數

image
image

[圖 3.6.3]

  • COUNTIFS函式：統計一組給定條件所指定的單元格數

IV. 其他部分實用函式索引

  • 日期和時間
    • DATE 函式 : 返回特定日期的序列號
    • DATEDIF 函式 : 計算兩個日期之間的天數、月數或年數。 此函式在用於計算年齡的公式中很有用。
    • DATEVALUE 函式 : 將文字格式的日期轉換為序列號
    • DAY 函式 : 將序列號轉換為月份日期
    • DAYS360 函式 : 以一年 360 天為基準計算兩個日期間的天數
    • EDATE 函式 : 返回用於表示開始日期之前或之後月數的日期的序列號
    • EOMONTH 函式 : 返回指定月數之前或之後的月份的最後一天的序列號
    • HOUR 函式 : 將序列號轉換為小時
    • ISOWEEKNUM 函式: 返回給定日期在全年中的 ISO 週數
    • MINUTE 函式 : 將序列號轉換為分鐘
    • MONTH 函式 : 將序列號轉換為月
    • NETWORKDAYS 函式 : 返回兩個日期間的完整工作日的天數
    • NETWORKDAYS.INTL 函式 : 返回兩個日期之間的完整工作日的天數（使用引數指明週末有幾天並指明是哪幾天）
    • NOW 函式 : 返回當前日期和時間的序列號
    • SECOND 函式 : 將序列號轉換為秒
    • TIME 函式 : 返回特定時間的序列號
    • TIMEVALUE 函式 : 將文字格式的時間轉換為序列號
    • TODAY 函式 : 返回今天日期的序列號
    • WEEKDAY 函式 : 將序列號轉換為星期日期
    • WEEKNUM 函式 : 將序列號轉換為代表該星期為一年中第幾周的數字
    • WORKDAY 函式 : 返回指定的若干個工作日之前或之後的日期的序列號
    • WORKDAY.INTL 函式 : 返回日期在指定的工作日天數之前或之後的序列號（使用引數指明週末有幾天並指明是哪幾天）
    • YEAR 函式 : 將序列號轉換為年
    • YEARFRAC 函式 : 返回代表 start_date 和 end_date 之間整天天數的年分數
  • 資訊
    • CELL 函式: 返回有關單元格格式、位置或內容的資訊
    • ERROR.TYPE 函式: 返回對應於錯誤型別的數字
    • INFO 函式: 返回有關當前操作環境的資訊
    • ISBLANK 函式: 如果值為空，則返回 TRUE
    • ISERR 函式: 如果值為除 #N/A 以外的任何錯誤值，則返回 TRUE
    • ISERROR 函式: 如果值為任何錯誤值，則返回 TRUE
    • ISEVEN 函式: 如果數字為偶數，則返回 TRUE
    • ISFORMULA 函式 : 如果有對包含公式的單元格的引用，則返回 TRUE
    • ISLOGICAL 函式: 如果值為邏輯值，則返回 TRUE
    • ISNA 函式: 如果值為錯誤值 #N/A，則返回 TRUE
    • ISNONTEXT 函式: 如果值不是文字，則返回 TRUE
    • ISNUMBER 函式: 如果值為數字，則返回 TRUE
    • ISODD 函式: 如果數字為奇數，則返回 TRUE
    • ISREF 函式: 如果值為引用值，則返回 TRUE
    • ISTEXT 函式: 如果值為文字，則返回 TRUE
    • N 函式: 返回轉換為數字的值
    • NA 函式: 返回錯誤值 #N/A
    • SHEET 函式: 返回引用工作表的工作表編號
    • SHEETS 函式: 返回引用中的工作表數
    • TYPE 函式: 返回表示值的資料型別的數字
  • 邏輯
    • AND 函式: 如果其所有引數均為 TRUE，則返回 TRUE
    • FALSE 函式: 返回邏輯值 FALSE
    • TRUE 函式: 返回邏輯值 TRUE
    • IF 函式: 指定要執行的邏輯檢測
    • IFERROR 函式: 如果公式的計算結果錯誤，則返回您指定的值；否則返回公式的結果
    • IFNA 函式: 如果該表示式解析為 #N/A，則返回指定值；否則返回該表示式的結果
    • IFS 函式: 檢查是否滿足一個或多個條件，且是否返回與第一個 TRUE 條件對應的值。
    • NOT 函式: 對其引數的邏輯求反
    • OR 函式: 如果任一引數為 TRUE，則返回 TRUE
    • SWITCH 函式: 根據值列表計算表示式，並返回與第一個匹配值對應的結果。如果不匹配，則可能返回可選預設值。
    • XOR 函式 : 返回所有引數的邏輯“異或”值
  • 查詢和引用
    • ADDRESS 函式: 以文字形式將引用值返回到工作表的單個單元格
    • AREAS 函式: 返回引用中涉及的區域個數
    • COLUMN 函式: 返回引用的列號
    • COLUMNS 函式: 返回引用中包含的列數
    • FORMULATEXT 函式: 將給定引用的公式返回為文字
    • GETPIVOTDATA 函式: 返回儲存在資料透視表中的資料
    • HLOOKUP 函式: 查詢陣列的首行，並返回指定單元格的值
    • HYPERLINK 函式: 建立快捷方式或跳轉，以開啟儲存在網路伺服器、Intranet 或 Internet 上的文件
    • INDEX 函式: 使用索引從引用或陣列中選擇值
    • INDIRECT 函式: 返回由文字值指定的引用
    • MATCH 函式: 在引用或陣列中查詢值
    • OFFSET 函式: 從給定引用中返回引用偏移量
    • ROW 函式: 返回引用的行號
    • ROWS 函式: 返回引用中的行數
    • RTD 函式: 從支援 COM 自動化的程式中檢索實時資料
    • TRANSPOSE 函式: 返回陣列的轉置
    • VLOOKUP 函式: 在陣列第一列中查詢，然後在行之間移動以返回單元格的值
  • 數學和三角函式
    • ABS 函式: 返回數字的絕對值
    • ACOS 函式: 返回數字的反餘弦值
    • ACOSH 函式: 返回數字的反雙曲餘弦值
    • ACOT 函式 : 返回一個數的反餘切值
    • ACOTH 函式 : 返回一個數的雙曲反餘切值
    • AGGREGATE 函式: 返回列表或資料庫中的聚合
    • ARABIC 函式: 將羅馬數字轉換為阿拉伯數字
    • ASIN 函式: 返回數字的反正弦值
    • ASINH 函式: 返回數字的反雙曲正弦值
    • ATAN 函式: 返回數字的反正切值
    • ATAN2 函式: 返回 X 和 Y 座標的反正切值
    • ATANH 函式: 返回數字的反雙曲正切值
    • BASE 函式 : 將一個數轉換為具有給定基數的文字表示
    • CEILING 函式: 將數字舍入為最接近的整數或最接近的指定基數的倍數
    • CEILING.MATH 函式 : 將數字向上舍入為最接近的整數或最接近的指定基數的倍數
    • CEILING.PRECISE 函式: 將數字舍入為最接近的整數或最接近的指定基數的倍數。無論該數字的符號如何，該數字都向上舍入。
    • COMBIN 函式: 返回給定數目物件的組合數
    • COMBINA 函式 : 返回給定數目物件具有重複項的組合數
    • COS 函式: 返回數字的餘弦值
    • COSH 函式: 返回數字的雙曲餘弦值
    • COT 函式 : 返回角度的餘弦值
    • COTH 函式 : 返回數字的雙曲餘切值
    • CSC 函式: 返回角度的餘割值
    • CSCH 函式: 返回角度的雙曲餘割值
    • DECIMAL 函式: 將給定基數內的數的文字表示轉換為十進位制數
    • DEGREES 函式: 將弧度轉換為度
    • EVEN 函式: 將數字向上舍入到最接近的偶數
    • EXP 函式: 返回 e 的 n 次方
    • FACT 函式: 返回數字的階乘
    • FACTDOUBLE 函式: 返回數字的雙倍階乘
    • FLOOR 函式: 向絕對值減小的方向舍入數字
    • FLOOR.MATH 函式 : 將數字向下舍入為最接近的整數或最接近的指定基數的倍數
    • FLOOR.PRECISE 函式: 將數字向下舍入為最接近的整數或最接近的指定基數的倍數。無論該數字的符號如何，該數字都向下舍入。
    • GCD 函式: 返回最大公約數
    • INT 函式: 將數字向下舍入到最接近的整數
    • ISO.CEILING 函式: 返回一個數字，該數字向上舍入為最接近的整數或最接近的有效位的倍數
    • LCM 函式: 返回最小公倍數
    • LN 函式: 返回數字的自然對數
    • LOG 函式: 返回數字的以指定底為底的對數
    • LOG10 函式: 返回數字的以 10 為底的對數
    • MDETERM 函式: 返回陣列的矩陣行列式的值
    • MINVERSE 函式: 返回陣列的逆矩陣
    • MMULT 函式: 返回兩個陣列的矩陣乘積
    • MOD 函式: 返回除法的餘數
    • MROUND 函式: 返回一個舍入到所需倍數的數字
    • MULTINOMIAL 函式: 返回一組數字的多項式
    • MUNIT 函式 : 返回單位矩陣或指定維度
    • ODD 函式: 將數字向上舍入為最接近的奇數
    • PI 函式: 返回 pi 的值
    • POWER 函式: 返回數的乘冪
    • PRODUCT 函式: 將其引數相乘
    • QUOTIENT 函式: 返回除法的整數部分
    • RADIANS 函式: 將度轉換為弧度
    • RAND 函式: 返回 0 和 1 之間的一個隨機數
    • RANDBETWEEN 函式: 返回位於兩個指定數之間的一個隨機數
    • ROMAN 函式: 將阿拉伯數字轉換為文字式羅馬數字
    • ROUND 函式: 將數字按指定位數舍入
    • ROUNDDOWN 函式: 向絕對值減小的方向舍入數字
    • ROUNDUP 函式: 向絕對值增大的方向舍入數字
    • SEC 函式 : 返回角度的正割值
    • SECH 函式: 返回角度的雙曲正切值
    • SERIESSUM 函式: 返回基於公式的冪級數的和
    • SIGN 函式: 返回數字的符號
    • SIN 函式: 返回給定角度的正弦值
    • SINH 函式: 返回數字的雙曲正弦值
    • SQRT 函式: 返回正平方根
    • SQRTPI 函式: 返回某數與 pi 的乘積的平方根
    • SUBTOTAL 函式: 返回列表或資料庫中的分類彙總
    • SUM 函式: 求引數的和
    • SUMIF 函式: 按給定條件對指定單元格求和
    • SUMIFS 函式: 在區域中新增滿足多個條件的單元格
    • SUMPRODUCT 函式: 返回對應的陣列元素的乘積和
    • SUMSQ 函式: 返回引數的平方和
    • SUMX2MY2 函式: 返回兩陣列中對應值平方差之和
    • SUMX2PY2 函式: 返回兩陣列中對應值的平方和之和
    • SUMXMY2 函式: 返回兩個陣列中對應值差的平方和
    • TAN 函式: 返回數字的正切值
    • TANH 函式: 返回數字的雙曲正切值
    • TRUNC 函式: 將數字截尾取整
  • 文字
    • ASC 函式: 將字串中的全形（雙位元組）英文字母或片假名更改為半形（單位元組）字元
    • BAHTTEXT 函式: 使用 ß（泰銖）貨幣格式將數字轉換為文字
    • CHAR 函式: 返回由程式碼數字指定的字元
    • CLEAN 函式: 刪除文字中所有非列印字元
    • CODE 函式: 返回文字字串中第一個字元的數字程式碼
    • CONCAT 函式 : 將多個區域和/或字串的文字組合起來，但不提供分隔符或 IgnoreEmpty 引數。
    • CONCATENATE 函式: 將幾個文字項合併為一個文字項
    • DBCS 函式: 將字串中的半形（單位元組）英文字母或片假名更改為全形（雙位元組）字元
    • DOLLAR 函式: 使用 ￥（人民幣）貨幣格式將數字轉換為文字
    • EXACT 函式: 檢查兩個文字值是否相同
    • FIND、FINDB 函式: 在一個文字值中查詢另一個文字值（區分大小寫）
    • FIXED 函式: 將數字格式設定為具有固定小數位數的文字
    • LEFT、LEFTB 函式: 返回文字值中最左邊的字元
    • LEN、LENB 函式: 返回文字字串中的字元個數
    • LOWER 函式: 將文字轉換為小寫
    • MID、MIDB 函式: 從文字字串中的指定位置起返回特定個數的字元
    • NUMBERVALUE 函式 : 以與區域設定無關的方式將文字轉換為數字
    • PHONETIC 函式: 提取文字字串中的拼音（漢字注音）字元
    • PROPER 函式: 將文字值的每個字的首字母大寫
    • REPLACE, REPLACEB: 替換文字中的字元
    • REPT 函式: 按給定次數重複文字
    • RIGHT、RIGHTB 函式: 返回文字值中最右邊的字元
    • SEARCH、SEARCHB 函式: 在一個文字值中查詢另一個文字值（不區分大小寫）
    • SUBSTITUTE 函式: 在文字字串中用新文字替換舊文字
    • T 函式: 將引數轉換為文字
    • TEXT 函式: 設定數字格式並將其轉換為文字
    • TEXTJOIN 函式 : 將多個區域和/或字串的文字組合起來，幷包括你在要組合的各文字值之間指定的分隔符。如果分隔符是空的文字字串，則此函式將有效連線這些區域。
    • TRIM 函式: 刪除文字中的空格
    • UNICHAR 函式 : 返回給定數值引用的 Unicode 字元
    • UNICODE 函式: 返回對應於文字的第一個字元的數字（程式碼點）
    • UPPER 函式: 將文字轉換為大寫形式
    • VALUE 函式: 將文字引數轉換為數字
  • Web
    • ENCODEURL 函式: 返回 URL 編碼的字串
    • FILTERXML 函式: 通過使用指定的 XPath，返回 XML 內容中的特定資料
    • WEBSERVICE 函式: 返回 Web 服務中的資料

參考資料

相關文章