15個Excel函數使用方法
Excel的靈魂在于資料的分析與統計,而分析與統計就離不開函數或公式,今天要給大家分享的15個函數公式,是工作中常用的,可以直接套用。
一、從身份證号碼中提取出生年月。
函數1:Text。
作用:将指定的值用指定的代碼轉換為文本。
文法結構:=Text(值,格式代碼)。
函數2:Mid。
作用:傳回文本字元串中從指定位置開始,長度為指定值的字元串。
文法結構:=Mid(字元串,開始位置,字元長度)。
目的:從身份證号碼中提取出生年月。
方法:在目标單元格中輸入公式:=TEXT(MID(C3,7,8),00-00-00)。
解讀:首先利用Mid函數提取“出生年月”的8位數字,然後用Text将其設定為“00-00-00”格式。
二、從身份證号碼中提取性别。
函數1:If
作用:根據指定條件傳回相應的值。
文法結構:=If(判斷條件,條件為真時的傳回值,條件為假時的傳回值)。
函數2:Mod
作用:傳回兩數相除的餘數。
文法結構:=Mod(被除數,除數)。
目的:根據身份證号碼提取性别。
方法:在目标單元格中輸入公式:=IF(MOD(MID(C3,17,1),2),男,女)。
解讀:身份證号碼的第17位代表性别,如果為奇數,則為男性,如果為偶數,則為女性,是以公式中,首先用Mid函數提取第17位,并作為Mod函數的被除數,除數為2,計算餘數,然後用IF函數判斷餘數,如果餘數為1(即奇數),則傳回“男”,如果餘數為0(即偶數),則傳回“女”。
三、根據身份證号碼計算年齡。
函數:datedif。
作用:以指定的方式統計兩個日期之間的內插補點。
文法結構:=Datedif(開始日期,結束日期,統計方式)。常見的統計方式有“Y”、“M”、“D”,即“年”、“月”、“日”。
目的:根據身份證号碼計算年齡。
方法:在目标單元格中輸入公式:=DATEDIF(TEXT(MID(C3,7,8),00-00-00),TODAY(),y)。
解讀:1、Datedif函數是系統隐藏函數,作用就是計算兩個日期之間的內插補點。
2、Today()函數的作用為擷取當天的日期。
3、上述公式中的年齡是随着日期的變化自動更新的,原因在于Today()是變化的,具有很強的實用性。
四、行列快速求和。
目的:按“銷售員”、“季度”彙總資料。
方法:標明目标單元格,包括求和區域,快捷鍵Alt+=。
解讀:Alt+=其實就是Sum函數的快捷鍵。
五、合并單元格求和。
目的:按“部門”計算銷售額。
方法:在目标單元格中輸入公式:=SUM(I4:I11)-SUM(J5:J11)。
解讀:1、合并單元格的值存儲在目前合并區域的左上角單元格中。
2、目前區域的和值減去除合并單元格的值之外的和值,則為目前合并單元格區域的和值。
六、合并單元格計數。
函數:Counta。
功能:統計指定區域中非空單元格的個數。
文法結構:=Counta(值或單元格引用)。
目的:統計“部門”人數。
方法:在目标單元格中輸入公式:=COUNTA(I4:I11)-SUM(J5:J11)。
解讀:公式中首先用Counta統計出I4:I11區域非空單元格的個數,即7個,然後減去除目前合并單元格之外的人數,則為目前部門的人數。
七、合并單元格填充序号。
函數:Max。
作用:計算最大值。
文法結構:=Max(值或單元格引用)。
目的:填充不規則合并單元格的序号。
方法:在目标單元格中輸入公式:=MAX(A$3:A3)+1。
解讀:也可以用Counta函數來填充,公式為:=COUNTA(A$2:A2)。
八、按照類别填充序号。
函數:countif。
功能:計算指定區域中滿足條件的單元格個數。
文法結構:=Countif(條件範圍,條件)。
目的:根據“部門”填充按順序填充序号。
方法:在目标單元格中輸入公式:=COUNTIF(D$4:D4,D4)。
解讀:此公式的關鍵在于了解參數的引用方式,每次計算時都是從D4單元格開始,條件為D列目前行的值。
九、查找重複值。
目的:檢查“銷售員”的姓名是否有重複。
方法:在目标單元格中輸入公式:=IF(COUNTIF(B$4:B$10,B4)1,重複,)。
解讀:用If函數判斷Countif的統計值和1的比較結果,如果成立,則傳回“重複”,否則傳回空值。
十、單條件計數。
目的:按性别統計銷售員人數。
方法:在目标單元格中輸入公式:=COUNTIF(C4:C10,M4)。
十一、多條件計數。
函數:Countifs。
功能:統計一組給定條件下的單元格個數。
文法結構:=Countifs(條件1範圍,條件1,[條件2範圍],[條件2]……[條件N範圍],[條件N])。
目的:按“性别”統計銷售總額在指定範圍内的人數。
方法:在目标單元格中輸入公式:=COUNTIFS(C4:C10,M4,I4:I10,N4)。
解讀:當多個條件同時符合時,此條資料才被統計,當然也可以實作單條件計數的功能。
十二、單條件求和。
函數:Sumif。
功能:對滿足條件的單元格求和。
文法結構:=Sumif(條件範圍,條件,[求和範圍]),當“條件範圍”和“求和範圍”相同時,可以省略“求和範圍”。
目的:按“性别”統計總銷售額。
方法:在目标單元格中輸入公式:=SUMIF(C4:C10,M4,I4:I10)。
十三、多條件求和。
函數:Sumifs。
功能:對一組給定條件指定的單元格求和。
文法結構:=Sumifs(求和範圍,條件1範圍,條件1,[條件2範圍],[條件2]……[條件N範圍],[條件N])。
目的:按“性别”統計指定範圍内的銷售總額 。
方法:在目标單元格中輸入公式:=SUMIFS(I4:I10,C4:C10,M4,I4:I10,N4)。
解讀:當多個條件同時符合時,此條資料才被統計,當然也可以實作單條件求和的功能。
十四、生成指定範圍的随機值。
函數:Randbetween。
功能:生成指定範圍内的随機值。
文法結構:=Randbetween(最小值,最大值)。
目的:生成200-500之間的随機值。
方法:在目标單元格中輸入公式:=RANDBETWEEN(200,500)。
解讀:如果對生成的随機值不滿意,可以按F9重新整理,直到滿意為止。
十五、排名。
函數:Rank。
功能:對指定範圍内的值按照指定的順序排序。
文法結構:=Rank(值,資料範圍,[排序方式])。排序方式分為0和1兩種,即降序和升序,省略時預設為降序。
目的:對總“銷售額”按降序排序。
方法:在目标單元格中輸入公式:=RANK(I4,I$4:I$10)。
解讀:省略第三個參數時預設為降序排序。
結束語:本文從實際出發,對工作中常用的函數公式進行了解讀。