EXCEL公式與函數

1.概概念

• 公式：公式是Excel的核心功能，功能強大。以=開頭，對位址進行引用的計算形式，它是确立資料之間的關聯關系，實作的是一種算法，通過其結果來描述這種關系
• 函數：函數實際上是Excel預定義的一種内置公式，他通過使用一些稱為參數的特定數值來按照特定的順序或結構執行計算

2.運算符

• 算術運算符
  • +(加号)
  • -(減号）
  • *(乘)
  • /(除)
  • %(百分比)
  • ^(乘方）
• 關系運算符
  • =(等号)

  • >

    (大于号)
  • <(小于号)

  • >=

    (大于等于号)
  • <=(小于等于号)
  • <>(不等于)

3.位址的引用

• 相對引用:你變他就變，如影随形，A2:A5
• 絕對引用，以不變應萬變,不變， $A$2
• 混合引用：根據情況變,$A2或a$2

$就像小别針一樣，别在誰的前面，誰就不能動

F4鍵可以互相轉換

4.邏輯函數

• if(logical_test,value_if_true,value_if_false)

  參數
  logical_test:給定的判斷條件
  value_if_true：條件成立傳回值
  value_if_false：條件不成立傳回值
  
  功能：邏輯判斷，根據真假傳回對應的結果
  
  例如：
  單一條件判斷:if(B2>=1500,0.1,0.08)
  多條件判斷:if(F2>=4,5000,IF(f2=3,3000,0))
  與and和or聯合使用:if(and(E2='A類'，D2>=10),5000,0)
             

  • 注意

    and(邏輯判斷1，邏輯判斷2...),判斷都為真，傳回True,否則傳回false
    or(邏輯判斷1，邏輯判斷2...),判斷有一個為真，傳回True,否則傳回false
               

5.文本函數

• len

  文法：len(text)
  參數：text文本内容
  功能：傳回文本字元串中字元的個數，也叫文本長度，不分中英文和數字，都是1個字元
             

• left

  文法：left(text,[num_chars])
  參數：text文本内容
  	 [num_chars]可選參數，指定left提取字元的個數
  注意：
  	num_chars必須大于或等于0
  	如果num_chars大于文本長度，則傳回全部文本
  	如果省略，預設值為1
  功能：從文本字元串左邊(開頭)起第一個字元開始傳回指定個數的字元
             

• right

  文法：right(text,[num_chars])
  參數：text文本内容
  	 [num_chars]可選參數，指定right提取字元的個數
  注意：
  	num_chars必須大于或等于0
  	如果num_chars大于文本長度，則傳回全部文本
  	如果省略，預設值為1
  功能：從文本字元串右邊(結尾)起第一個字元開始傳回指定個數的字元
             

• mid

  文法：mid(text,start_num,num_chars)
  參數：text文本内容
  	 start_num:必選參數，從文本中哪個位置提取，1代表第一個位置，内容包含第一個值
  	 num_chars：必選參數，取多長，也就是去幾個字元
  
  功能：從文本字元串的指定位置提取指定長度的字元
             

• left,right,mid執行個體，身份證提取案例

  生日：mid(D2,7,8)
  後6位：right(D2,6)
  前3位：left(D2,3)
             

• text

  文法：text(内容，格式)
  參數：内容：要進行格式轉換的内容
  	 格式：轉換指定格式
  	 
  功能：格式轉換，類似于設定單元格格式中自定義模式的功能
  
  例如：text(A2,"yyyy-mm-dd")
             

• replace

  文法：replace(old_text，start_num,num_chars,new_text)
  參數：old_text:原來文本
  	 start_num:指定原文本的哪個位置開始,1是第一個
  	 num_chars:取多長
  	 new_text:把原來截取的内容替換成新的内容
  	 
  功能：根據指定的内容，将原文本部分内容替換為新的内容
  例如：replace(A2,8,3,"***")
             

• find

  傳回查到的内容的起始位置
  文法：find(find_text，within_text,[start_num])
  參數：find_text:要查找的文本
  	 within_text:包含要查找文本的文本
  	 start_num:可選參數，指定從哪開始找
  	 
  功能：根據指定内容查找，傳回要查找的文本所在位置的起始值
  例如：find("EXCEL",A1)
             

6.統計函數

• int

  取整
  文法：int(number)
  參數：number:一般是個小數，正負都可以
  	 
  功能：将數字向下舍入取整(取比這個數小的最大整數)
  例如：int(A1)
             

• round

  四舍五入
  文法：round(number,num_digits)
  參數：number:要四舍五入的數字
  	 num_dihits:要留幾位小數
  	 
  功能：将數字四舍五入到指定的小數位
  例如：round(A8,2)
             

• mod

  求餘數
  文法：mod(number,divisor)
  參數：number:計算餘數的被除數(分子)
  	 divisor:除數(分母)
  	 
  功能：計算兩數相處的餘數
  例如：mod(A12,B12)
             

• average

  ，求均值

• max

  ,求最大值

• min

  ,最小值

• sum

  ,求和

• count

  ，計數

  文法：average(number1,[number2],[number3]...)
  參數：numer都是要參數計算的數字，單元格引用或單元格區域，最多可包含255個
  	 
  功能：用于計算
             

• sumif

  條件求和
  文法：sumif(range,criteria,[sum_range])
  參數：range:條件所在的資料區域(資料範圍)
  	 criteria:給定求和的篩選條件(條件)
  	 sum_range:求和區域，若省略，則代表求和區域與條件所在區域是一樣的
  	 
  功能：根據條件求和
  例如：sumif(C2:C40,"A類",D2:D40)
             

• sumifs

  條件求和
  文法：sumifs(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2]...)
  參數：sum_range:求和資料範圍
  	 criteria_range1:條件資料範圍
  	 criteria1:條件
  	 
  功能：多條件求和
  例如：sumifs(D2:D40,C2:C40,"A類",B2:B40,">5")
             

• countif

  條件求和
  文法：counif(range,criteria)
  參數：range:要計算其中非空單元格數目的區域   
  	criteria:給定的條件
  	
  功能：條件計數
  例如：countif(C2:C40,"A類")
             

• countifs

  多條件求和
  文法：countifs(criteria_range1,criteria1,[criteria_range2,criteria2],...)
  參數：criteria_range1:條件範圍
  	 criteria1:條件
  	 
  功能：多條件求和
  例如：countifs(B2:B40,">=10",D2:D40,">=2000")
             

7.查找與引用函數

• vlookup

  多條件求和
  文法：vlookup(lookup_value,table_arry,col_index_num,range_lookup)
  參數：lookup_value:要查找的内容
  	 table_arry:要查找的區域
  	 col_index_num：包含要傳回的值的區域中的列号
  	 range_lookup：傳回近似或精準比對--表示為1/True或0/False
  	 
  注意：要查找内容必須在要查找的區域的第一列
  
  功能：查找比對資料
             

• match

  多條件求和
  文法：match(lookup_value,lookup_arry,[match_type])
  參數：lookup_value:要查找的内容
  	 lookup_arry:要查找的區域
  	 match_type：查找方式
  
  功能：傳回查找值所在區域的位置
  row:傳回行
  column；傳回列
  例如：MATCH(F1,$A$1:$A$18,0)
             

補充内容

offset
index
indirect
sumproduct
           

8.日期函數

• today()

  ,傳回目前的系統日期，

  today()   2022-1-17
             

• now()

  ,傳回目前系統的日期和時間，

  now() 2022-1-17 13:14:15
             

• year(日期)

  ,傳回日期所在的年份

  year(2022-1-17)    2022
             

• month(日期)

  ,傳回日期所在的月份

  month(2022-1-17)  1
             

• day(日期)

  ,傳回日期所在月份的天數

  day(2022-1-17)   17
             

• date

  文法：date(year,month,day)
  參數：year:年
  	 month:月
  	 day：日
  
  功能：拼接日期格式
             

• dateif

  文法：dateif(start_date,end_date,unit)
  參數：start_date:開始時間
  	 end_date:結束時間
  	 unit：計算機關(D天，N月，Y年)
  
  功能：計算日期之間的內插補點
             

9.常見錯誤資訊

• div/0!

  0做除數
             

• NAME?

  在公式中使用了不能識别的名稱
  删除了公式中使用的名稱，或者使用了不存在的名稱，函數名拼寫錯誤
             

• VALUE!

  使用了不正确的參數或運算符
  在需要數字或邏輯值是輸入了文本
             

• REF!

  引用了無效的單元格位址
  删除了由其他公式引用的單元格
  将移動單元格粘貼到由其它公式引用的單元格中
             

• NULL!

  指定了兩個并不相交的區域，故無效
  使用了不正确的區域運算符或不正确的單元格引用
             

• N/A

  在函數或公式中引用了無法使用的數值
  内部函數或自定義工作表函數中缺少一個或多個參數
  使用的自定義工作表函數不存在
  vlookup()函數中的查找值，lookup_value,False/True指定了不正确的值域
             

• NUM!

  數字類型不正确
  在需要數字參數的函數中使用了不能接受的參數
  由公式産生的數字太大或太小
             

• #####

  列寬設定問題，不是錯誤值，輸入到單元格中的數值太長，在單元格中顯示不下