感覺太簡單，就邊練習邊筆記了，根據微信讀書的《Excel函數與公式速查手冊》來練習，不區分大小寫

名稱的定義與使用

“名稱”就是給一個單元格、單元格區域、公式或常量值起一個易于理解和記憶的別名。

如果引用的數據區域發生了變化（比如從B2:B10變成了B2:B15），您只需在名稱管理器中修改一次名稱的定義范圍，所有使用該名稱的公式都會自動更新，無需逐個修改。

在名稱框（位于編輯欄左側）的下拉列表中選擇一個名稱，Excel會自動選中并跳轉到該名稱所定義的區域
例如：

• 原始引用方式：=SUM(Sheet1!B2:B10)
• 使用名稱后：=SUM(銷售額)

創建名稱

• 在工作簿中直接引用求和
  

• 范圍影響使用
  

邏輯函數

AND函數：檢驗一組數據是否都為真

AND(logical1,logical2,logical3,…)

• 三項考核分數是否同時都大于75
  

OR函數：檢驗一組數據是否有一個滿足條件

OR(logical1, [logical2], ...)

• 三項考核一項為合格，即為合格
  

• 選出B，C項合格或者D項合格
  

NOT函數：對所有參數求反

NOT(logical)

• 篩選出工資少于10000
  

IF函數：根據條件判斷真假

IF(logical_test,value_if_true,value_if_false)

logical_test:logical_test
value_if_true：表示當判斷條件為邏輯“真”（TRUE）時，顯示該處給定的內容。如果忽略，返回TRUE
value_if_false：表示當判斷條件為邏輯“假”（FALSE）時，顯示該處給定的內容。如果忽略，返回FALSE

• 當業績超過100000，輸出發獎金，否則輸出還需努力
  

• 當兩門成績都大于75時，輸出：合格，否則輸出：不合格
  

• 當業績<100000，輸出：還需努力；當業績<150000，輸出：業績*0.6；當業績>150000，輸出：業績*0.8
  

• 當工齡小于2，獎金：工資+200；當工齡大于等于2且小于4，獎金：工資+400；否則工資+600
  

• 選取組別為A組，B組的C列值相加
  

IFERROR函數

IFERROR(value, value_if_error)

• 如果數據報錯，則輸出：沒法獎金
  

數學函數

SUM函數：求和

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

• 計算一二車間產量總和
  

• 每一項銷售總價相加
  

• 在B列中匹配遲到早退的，（為True，True值為1）在與相對應的C列相乘累加
  

SUMIF函數：按照指定條件求和(就是sum+if的簡化)

SUMIF(range, criteria, [sum_range])

range：必需。用于條件計算的單元格區域
criteria：必需。用于確定對哪些單元格求和的條件，其形式可以為數字、表達式、單元格引用、文本或函數。
sum_range：表示根據條件判斷的結果要進行計算的單元格區域

• 求出部門為車間的職業工資總和
  

  • sum+if
    

• 在B列中找到匹配F列中值，然后累加
  

SUMIFS函數：對滿足多重條件的單元格求和

SUMIFS(sum_range, criteria_range1, criteria1,[criteria_range2, criteria2], ...)

sum_range：必需。對一個或多個單元格求和，包括數字或包含數字的名稱、區域或單元格引用(求和區間寫在最前面)
sum_range：必需。對一個或多個單元格求和，包括數字或包含數字的名稱、區域或單元格引用
criteria1：必需。條件的形式為數字、表達式、單元格引用或文本，可用來定義將對criteria_range1參數中的哪些單元格求和
criteria1：必需。條件的形式為數字、表達式、單元格引用或文本，可用來定義將對criteria_range1參數中的哪些單元格求和

• 計算一車間中初級技工工資總和
  

SUMPRODUCT函數：SUMPRODUCT函數

SUMPRODUCT(array1, [array2], [array3], ...)

array1：必需。其相應元素需要進行相乘并求和的第一個數組參數。
array1：必需。其相應元素需要進行相乘并求和的第一個數組參數。

• 每一項銷售總價相加
  

SUBTOTAL函數：返回分類匯總的值

SUBTOTAL(function_num,ref1,[ref2],...)

function_num：必需。1～11（包含隱藏值）或101～111（忽略隱藏值）之間的數字，用于指定使用何種函數在列表中進行分類匯總計算,具體如下

*ref1：必需。表示要對其進行分類匯總計算的第一個命名區域或引用
ref2,...：可選。表示要對其進行分類匯總計算的第2個至第254個命名區域或引用。

• 對分類匯總的數據重新進行平均值計算
  

ABS函數：求絕對值

ABS(number)

• 判斷上升還是下降，并算出絕對值
  

MOD函數：求兩個數值相除后的余數

MOD(number, divisor)

SUMSQ函數：SUMSQ函數用于返回參數的平方和

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

SUMXMY2函數：用于返回兩個數組中對應數值之差的平方和

SUMXMY2(array_x, array_y)

SUMX2MY2函數：用于返回兩個數組中對應數值的平方和之差

SUMX2MY2(array_x, array_y)

PRODUCT函數：用作參數的所有數字的乘積，然后返回該乘積。

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

RAND函數：返回大于或等于0小于1的隨機數

• 隨機返回大于或等于0及小于1的均勻分布隨機實數
  

RANDBETWEEN函數：返回指定數值之間的隨機數

RANDBETWEEN(bottom, top)

bottom：必需。表示函數RANDBETWEEN將返回的最小整數
bottom：必需。表示函數RANDBETWEEN將返回的最小整數

• 隨機生成10-100的隨機數
  

SQRT函數:用于返回正的平方根

SQRT(number)

RADIANS函數：用于將角度轉換為弧度

RADIANS(angle)

• 隨機生成角度，轉換成弧度
  

INT函數：不考慮四舍五入對數字直接取整

INT(number)

ROUND函數：可將某個數字四舍五入為指定的位數

ROUND(number, num_digits)

• 保留數據小數點后三位
  

ROUNDUP函數：向上舍入

ROUNDUP(number, num_digits)

• 保留兩位直接向上入
  

ROUNDDOWN函數：向下舍入

ROUNDDOWN(number, num_digits)

• 保留兩位直接向下舍
  

TRUNC函數：不考慮四舍五入對數字截斷

TRUNC(number, [num_digits])

• 直接截斷，默認值為0
  

統計函數

AVERAGE函數：求平均值

AVERAGE(number1,number2,...)

• 求平均值，自動忽略文本
  

AVERAGEA函數 ：求包括文本和邏輯值的平均值

AVERAGEA(value1,value2,...)

• 不忽略文本
  

AVERAGEIF函數：按條件求平均值

AVERAGEIF(range,criteria,average_range)

• 按條件求平均值
  

AVERAGEIFS函數：按多條件求平均值

AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2,…)

TRIMMEAN函數：截頭尾返回數據集的平均值

TRIMMEAN(array,percent)

• 去掉兩個數據點后取平均值
  

COUNT*函數：統計單元格個數

COUNT(value1,value2,...)：統計含有數字的單元格個數
COUNTA(value1,value2,...)：統計包括文本和邏輯值的單元格數目
COUNTIF(range,criteria)：統計滿足給定條件的單元格的個數
COUNTIFS(range1, criteria1,range2, criteria2, …)：統計同時滿足多個條件的單元格的個數
COUNTBLANK(range):計算空白單元格的數目

MAX*函數：最大值

MAX(number1,number2,...)：返回最大值
MAXA(value1,value2,...):返回參數列表（包括數字、文本和邏輯值）中的最大值

MIN*函數：最小值

MIN(number1,number2,...)：返回最小值
MINA(value1,value2,...)：返回參數列表（包括數字、文本和邏輯值）中的最小值。

LARGE函數：返回某數據集的某個最大值

LARGE(array,k)

array：表示為需要從中查詢第k個最大值的數組或數據區域。
k：表示為返回值在數組或數據單元格區域里的位置，即名次。

• 在兩個店鋪中找到銷售最多的前三名
  

SMALL函數：返回某數據集的某個最小值

SMALL(array,k)

參數同上

• 找出兩個店鋪中倒數三名
  

RANK.EQ函數：返回數據的排位，如果多個值具有相同的排位，則返回該組數值的最高排位

RANK.EQ(number,ref,[order])

number：表示要查找其排位的數字
ref：表示數字列表數組或對數字列表的引用。ref中的非數值型值將被忽略
order：可選。表示指定數字的排位方式的數字。指定為0時表示降序排名；指定為1時表示升序排名

• 
  

RANK.AVG函數：返回數據的排位，如果多個值具有相同的排位，則將返回平均排位

• 
  

VAR.S函數：計算基于樣本的方差，忽略樣本中的邏輯值和文本

VAR.S(number1,[number2],...)

number1：表示對應于樣本總體的第一個數值參數
number2, ...：可選。對應于樣本總體的2～254個數值參數

VARA函數：計算基于樣本的方差，不忽略樣本中的邏輯值和文本

VARA(value1,value2,...)

參數同上
其中文本被視為0，邏輯值TRUE被視為1，FALSE被視為0

VAR.P函數：計算基于樣本總體的方差，忽略樣本總體中的邏輯值和文本

VAR.P(number1,[number2],...])

VARPA函數：計算基于樣本總體的方差，不忽略樣本總體中的邏輯值和文本

VARPA(value1,value2,...)

其中文本被視為0，邏輯值TRUE被視為1，FALSE被視為0

VAR.S,VARA,VAR.P,VARPA 比較

STDEV.S函數：計算基于樣本估算標準偏差,忽略樣本中的邏輯值和文本

STDEV.S(number1,[number2],...)

STDEVA函數：計算基于給定樣本的標準偏差

STDEVA(value1,value2,...)

STDEV.P：計算樣本總體的標準偏差，忽略樣本中的邏輯值和文本

STDEV.P (number1,[number2],...)

STDEVPA：計算樣本總體的標準偏差

STDEVPA(value1,value2,...)

STDEV.S，STDEVA，STDEV.P，STDEVPA

文本查找提取

FIND函數：用于在第二個文本串中定位第一個文本串，并返回第一個文本串的起始位置的值，不可以使用通配符

FIND(find_text,within_text,[start_num])

• 在B列中找到“-”字符的位置
  

SEARCH函數：查找字符串的起始位置，可以使用通配符

SEARCH(find_text,within_text,[start_num])

• 定位“-”位置
  

MID函數：從任意位置提取指定數目的字符

MID(text, start_num, num_chars)

• 在A列中從第九個字符開始，截取三個
  

• 在B列中先找到“-”位置，往后移一位開始，截取五位字符
  

LEFT函數：按指定字符數從最左側提取字符串

LEFT(text, [num_chars])

• 從左側提取至找到字符前一個位置
  

RIGHT函數：按指定字符數從最右側提取字符串

RIGHT(text,[num_chars])

• 字符定位，然后從右邊截取
  

REPLACE函數：替換文本字符串中的部分文本

REPLACE(old_text, start_num, num_chars, new_text)

old_text：必需。表示要替換其部分字符的文本
start_num：必需。表示要用new_text替換的old_text中字符的位置
num_chars：必需。表示希望REPLACE使用new_text替換old_text中字符的個數
new_text：必需。表示將用于替換old_text中字符的文本

• 從字符串第7位，數8位數，替換成*
  

SUBSTITUTE函數：用新文本替換舊文本

SUBSTITUTE(text,old_text,new_text,instance_num)

SUBSTITUTE(text,old_text,new_text,instance_num)
old_text：必需。表示需要替換的舊文本
new_text：必需。用于替換old_text的新文本。
instance_num：可選。用來指定要以new_text替換第幾次出現的old_text。如果指定了instance_num，則只有滿足要求的old_text被替換；否則會將text中出現的每一處old_text都更改為new_text

• 替換第一個出現的“-”
  

LOWER函數：將文本轉換為小寫形式

LOWER(text)

• 字符串種所有的大寫字母轉換成小寫
  

UPPER函數：將文本轉換為大寫形式

UPPER(text)

• 將字符串中小寫字母改成大寫
  

PROPER函數：將文本字符串的首字母轉換成大寫

PROPER函數（將文本字符串的首字母轉換成大寫

• 首字母大寫
  

TEXT函數：設置數字格式并將其轉換為文本

TEXT(value,format_text)

• 按照指定格式輸出
  

CONCATENATE函數：合并兩個或多個文本字符串

CONCATENATE(text1, [text2], ...)

• 按照指定格式輸出
  

EXACT函數：比較兩個文本字符串是否完全相同

EXACT(text1, text2)

• 比較字符串是否相同
  

REPT函數：按照給定的次數重復文本

• 重復"*"
  

CHOOSE函數：從參數列表中選擇并返回一個值

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

ndex_num：表示指定所選定的值參數。index_num必須為1～254的數字，或者為公式或對包含1～254某個數字的單元格的引用
value1, value2, ...：value1是必需的，后續值是可選的。這些值參數的個數介于1～254，函數CHOOSE基于index_num從這些值參數中選擇一個數值或一項要執行的操作。參數可以為數字、單元格引用、已定義名稱、公式、函數或文本

• 根據判斷選出相應值后返回對應值
  

LOOKUP函數（向量型）?：按條件查找并返回值

LOOKUP(lookup_value, lookup_vector, [result_vector])

LOOKUP是一個模糊查找函數，所以在進行查找前必須要對查找的那一列先進行升序排列。

lookup_value：表示LOOKUP在第一個向量中搜索的值。lookup_value可以是數字、文本、邏輯值、名稱或對值的引用。
lookup_vector：表示只包含一行或一列的區域。lookup_vector中的值可以是文本、數字或邏輯值
lookup_vector：表示只包含一行或一列的區域。lookup_vector中的值可以是文本、數字或邏輯值

• 按條件查找并返回
  

• 第一個參數是查找的對象，始終是不變的，因此采用絕對引用。LOOKUP是在首列中查找，然后返回給定引用區域中最后一列的值，因此對A2∶B60單元格區域的引用，在復制公式時，A列始終不發生變化，要絕對引用，而B列因不同科目的成績位于不同列中，因此要不斷變化，所以使用相對引用
  

日期和時間函數

NOW函數：返回當前日期與時間

NOW()

• 日期相減格式化輸出
  

TODAY函數：返回當前的日期

TODAY()

• 輸出今天日期
  

DATE函數：構建標準日期

DATE(year,month,day)

TIME函數:構建標準時間

TIME(hour, minute, second)

DATEDIF函數：計算兩個日期之間的年數、月數、天數

DATEDIF(date1,date2,code)

date1：表示起始日期
date2：表示結束日期
code：表示要返回的兩個日期的參數代碼

• 計算工作年份
  

MATCH函數：查找并返回找到值所在位置，和index連用

IMATCH(lookup_value,lookup_array,match_type)

lookup_value：表示需要在數據表中查找的數值
lookup_array：表示可能包含所要查找數值的連續單元格區域
match_type：表示數字-1、0或1，指明如何在lookup_array中查找lookup_value。當match_type為1或省略時，函數查找小于或等于lookup_value的最大數值，lookup_array必須按升序排列；如果match_type為0，函數查找等于lookup_value的第一個數值，lookup_array可以按任何順序排列；如果match_type為-1，函數查找大于或等于lookup_value的最小值，lookup_array必須按降序排列

INDEX函數：返回指定位置上的值和match連用

INDEX(array, row_num, [column_num])

array：表示單元格區域或數組常量
row_num：表示選擇數組中的某行，函數從該行返回數值
column_num：可選。表示選擇數組中的某列，函數從該列返回數值

• match函數會因為開始范圍不同，返回不同的值進而會影響index的定位
  

• 對比不同的match開始范圍導致的index返回值不同
  

信息函數

CELL函數：返回有關單元格格式、位置或內容的信息

CELL(info_type, [reference])

• 顯示文件名
  

• 顯示所處單元格
  

INFO函數：返回當前操作環境的信息

INFO(type_text)

• 返回信息如下
  

ISBLANK函數：檢測單元格是否為空

ISBLANK(value)

• 檢測單元格是否為空
  

ISNUMBER函數：檢測給定值是否是數字

ISNUMBER(value)

• 檢測是否純數字
  

ISTEXT函數：檢測給定值是否是文本

ISTEXT(value)

• 檢測是否純文本
  

財務函數

DOLLAR函數：四舍五入數值，并添加千分位符號和$符號

DOLLAR(number,decimals)

• 將A列轉換成美元，保留兩位小數
  

RMB函數：四舍五入數值，并添加千分位符號和￥符號

RMB(number, [decimals])

• 將A列轉換成人民幣格式
  

BAHTTEXT函數：將數字轉換為泰銖

BAHTTEXT(number)

• 轉換成泰銖
  

PMT函數：返回貸款的每期付款額

PMT(rate,nper,pv,fv,type)

rate：表示貸款利率
nper：表示該項貸款的付款總數
pv：表示現值，即本金
fv：表示未來值，即最后一次付款后希望得到的現金余額
type：表示指定各期的付款時間是在期初還是期末。若type=0，為期末；若type=1，為期初

• 計算
  

IPMT函數：返回給定期限內的利息償還額

IPMT(rate,per,nper,pv,fv,type)

rate：表示各期利率
per：表示用于計算其利息數額的期數，范圍為1～nper
nper：表示總投資期
pv：表示現值，即本金
fv：表示未來值，即最后一次付款后的現金余額。如果省略fv，則假設其值為0
type：表示指定各期的付款時間是在期初還是期末。若type=0，為期末；若type=1，為期初

• 計算
  

PPMT函數：返回給定期間內本金償還額

PPMT(rate,per,nper,pv,fv,type)

參數同上