搜索
Excel函數初級課程啟基科技工程部IE課聯繫人：李述艷分機：7108E-mail：Steven_sy_lee@wnc.com.twIF(邏輯測試，TRUE時傳回的值，FALSE時傳回的值)2.認識錯誤值3.絕對參照位址與相對應參照位址3.1絕對參照位址其表示方法，就是在儲存格位址的左側加上$3.2相對參照位址2.3兩者的轉換鍵為F4.4.常用函數函數的基本概念如果公式不能正確計算出結果，microsoftexcel會顯示錯誤值。每種錯誤類型都有不同的原因以及不同的解決方式。(#N/A、#NUM、#VALUE!、#REF!、#DIV/0!、#NAME?、#NULL!)是指：儲存格內公式所運用到的儲存格位址，並不會隨存放公式的儲存格的移動或復制而有所更動。是指：儲存格內公式所運用到的儲存格位址，會隨存放公式的儲存格的移動或復制而有所更動，更動的方式依一定的模式進行。1.什麼是函數？函數是預先定義的公式，使用特定值(引數)以特殊的順序(語法)來執行計算。MAX(數字1，數字3，……)CONCATENATE(文字1，文字2,……)函數的基本結構：4.1.統計函數對資料范圍執行統計分析。包括一般的數值統計、工程統計、機率、抽樣分配、求平均數等。4.2.日期及時間函數.日期函數可以在公式中分析和處理日期和時間值。函數名稱左括右括號逗號引數數字、文字、邏輯值、陣列、錯誤值、絕對位址、常數、公式或其它函數4.6.查找與引用函數.可以在清單或表格中尋找數值，或尋找儲存格的參照。4.3.文字函數.文字函數可以在公式中處理文字字串。如改變大小寫、決定文字字串的長度或是連接不同字串等。4.4.數學函數.可以執行簡單和複雜的數學計算。4.5.邏輯函數.可以使用邏輯函數來查看條件是否成立，或檢查多重條件。函數名稱例：資料計算結果C26-515C271521aC283020C2952bC30TRUE40SUMIF例：如果省略掉sum_range，則計算加總範圍中的儲存格。函數名稱例：資料19.5=INT(8.9)=INT(-8.9)=B52-INT(B52)例：公式=ROUND(2.15,1)=ROUND(2.149,將2.15四捨五入到小數第一位(2.2)無條件捨去-8.9(-9)傳回儲存格B52正實數的小數部分(0.5)敘述結果2.2財產價值1000002000003000002.1'=sum(c28:c32)-90.5常用函數講義------初級1.3.日期及時間函數.日期函數可以在公式中分析和處理日期和時間值。1.常用函數1.2.統計函數.對資料范圍執行統計分析。MAX/MIN/LARGE/SMALL/RANK/MODEAVERAGE/COUNT'=sum(c28:c29,3,2)'=sum(5,15,true)'=sum(5,15,false)'=sum(c31,c30,2)公式說明SUMIF1.Range為Criteria的搜尋範圍2.Criteria為條件，定義哪些單元格相加，是數字、表示式或文字形式的字符串3.Sum_range為實際要求和的單元格。按指定條件對指定範圍求和。SUMIF(Range,Criteria,Sum_range)佣金無條件捨去8.9小數點後數字(8)8將數值按所指定的位數四舍五入ROUND(Number,Num_digits)Number為要四舍五入的數字Num_digits為四舍五入計算時所設定的位數。敘述財產總值超過160000的佣金總和(63,000)400000公式63000結果將 2.149四捨五入到小數第一位(2.1)1.常用的數學函數------SUM/PRODUCT/SUMIFSUM計算參數中所有數值的總和。SUM(Number1,Number2……)SUM(數字1，數字2，……)語法備註1.引數可以是數字、邏輯值、或以文字格式表示的數值。2.陣列或儲存格參照中的空白儲存格、邏輯值、文字或錯誤值都會被忽略1.6.查找與引用函數.可以在清單或表格中尋找數值，或尋找儲存格的參照。1.4.文字函數.文字函數可以在公式中處理文字字串。1.1.數學函數.可以執行簡單和複雜的數學計算。LFET/MID/RIGHTSUM/SUMIF/INT/ROUNDIFTODAY/NOW/MONTH/DATEVALUE1.5.邏輯函數.可以使用邏輯函數來查看條件是否成立，或檢查多重條件。VLOOKUPROUND700014000INT語法備註把一個數向下舍位到最近的整數INT(Number)21000280002.常用的數學函數----INT、ROUND敘述(結果)=ROUND(-1.475,=ROUND(21.5,-1)如果num_digits大於0，則數字將被四捨五入到指定的小數位數。如果num_digits等於0，數字將被四捨五入成整數。如果num_digits小於0，數字將被四捨五入到小數點左邊的指定位數。函數名稱姓名分數排名cde黃琳33第一名100周遠寒67第二名96張林溪96倒數第一33李芹100倒數第二51陳湘萍51最高分數100=max(c87:c94)柳清風93最低分數33=min(c87:c94)尋江南63中間分數76=median(c87:c94)郭書茗84出現最多#N/A=mode(c87:c94)函數名稱姓名分數備註abc求有效分平均值所有參考人平均分數有效分人數所有參考人數雲飛揚68AVERAGEAVERAGEACOUNTCOUNTA楚天闊738072910黃琳70=AVERAGE(C112:C121)=AVERAGEA(C112:C121)=COUNT(C112:C121)=COUNTA(C112:C121)周遠寒97張林溪作弊COUNT教育訓練考核分數統計列入計算的引數有數字、日期、或代表數字的文字。不列入計算的引數有錯誤值，以及不能轉換成數字的文字。將-1.475四捨五入到小數第二位(-1.48)-1.48MIN用法同MAX函數。20語法備註引數可為數值、空白儲存格、邏輯值或以文字表示的數值。計算一組參數中的最大值MAX(Number1,Number2……)MAX(數字1，數字2，……)將21.5四捨五入到小數點左邊一位的整數位(20)3.常用的統計函數------MAX/MIN/MODE/RANKLARGE/SMALL語法計算參數的算數平均數AVERAGE(Number1,Number2……)AVERAGE(數字1,數字2……)4.常用的統計函數------AVERAGE/COUNT/AVERAGEA/COUNTALARGESMALLMAX注：large、small與max、min非常相像，但前者求得的是第K個值最大或最小值，後者求的是最終的大、小值。項目=rank(c94,c87:c94)備註忽略陣列或參照引數裡有文字、邏輯值或空的儲存格，但引數是0，則包含在計算內。統計指定範圍內數字的個數COUNT(Value1,Value2……)COUNT(數值1,數值2……)AVERAGERANK指定順序為1就由小到大遞增排序，如指定為0或未指定則由大到小遞減排序。=small(c87:c94,1)=large(c87:c94,1)公式說明求數據中第K個最小值SMALL(Array,K)SMALL(數組,欲傳回的位置)求數據中第K個最大值LARGE(Array,K)LARGE(數組,欲傳回的位置)計算一組參數中的最小值MIN(Number1,Number2……)MIN(數字1，數字2，……)結果計算某數字在指定範圍中的排序等級RANK(Number,Ref,Order)RANK(求等級的數字，數值陣列，順序)教育訓練考核分數統計MODE引數應為數字，或包含數字的名稱、陣列、或參照。資料組中不包含重復的資料點，則會傳回#N/A的錯誤值。出現頻率最高的數MODE(Number1,Number2……)李芹100陳湘萍88柳清風54尋江南71春天99dfeg720函數名稱計算結果1109例1：數值日期格式數字格式日期日期格式數字格式11900/1/1383392004/12/180000年天天天例2：工號姓名8140****李明8140****函數名稱簡例：=today()=now()=month(c154)2004/5/185=month(F155)函數名稱MIDLEFTRIGHT舉例：省略Date_text年的部分，則函數會使用電腦中內建時鐘的目前年份。日期序列值是指EXCEL在計算日期和時間時所使用的日期和時間碼。通常在函數中使用時，大都用文字取代數字業指定，如1999/10/22、16：22：11等，EXCEL會自動轉換為序列號碼，再由函數運算。日期系統為1900年時，其日期序列號碼由1900年1月1日到9999年12月31日，即1~2958465，以日為單位若超出則會傳回#VALUE!。語法1.Num_chars必須=0。2.Num_chars若省略，則假設其值為1。TODAY傳回電腦系統中內建時鍾的日期及時間。不需加上引數。=DATEVALUE(1900/1/1)=DATEVALUE(1900/1/10)=E130-E129將文字形式的日期轉換為EXCEL的序列值。DATEVALUE(Date_text)DATEVALUE(日期文字)NOW計算對應日期的月份數。Month(Serial_number)Month(序列值)傳回電腦系統中現在的日期。不需加上引數。從字符串的第幾個字開始，取出幾個字。MID(Text,Start_num,Num_chars)MID(文字字串，開始抽選字元，抽選字元長度)由最左邊開始，取出指定個數的字符串。LEFT(Test,Num_chars)LEFT(文字，字元數)Start_num大於文字長度，則傳回。備註備註語法用法與LEFT近似。從最右邊開始，取出指定個數的部份文字。RIGHT(Text,Num_chars)RIGHT(文字，字元數)備註語法DATEVALUE公式7.常用的文字函數------LFET/MID/RIGHT只有在計算工作表或執行包含這個函數的巨集時，才會執行自動更新的動作，它並不是連續地更新。MONTH2020/5/2114:0252020/5/21工號和姓名有對應關係。6.常用的日期及時間函數------TODAY/NOW/MONTH5.常用的日期及時間函數------DATEVALUE公式結果公式結果公式PleaseCPleaseC=LEFTB(B165,8)easeseC=MIDB(B165,5,4)re!！e!！=RIGHTB(B165,4)函數函=leftb(B168,2)應用實務的=midb(B168,4,4)函數名稱IF的應用：姓名分數等級JACK66通過考試前提條件條件A成立條件A不成立LILY80通過考試如果(..條件A..)B事件發生C事件發生NIKE55要補考如果(分數60)通過考試要補考JOY91通過考試IF(C18560,通過考試,要補考)COCO45要補考是否還有疑問？函數名稱hijk例一：工號姓名性別部門課別職位abcefh06132雲飛揚男行政一部人事課課長06198楚天闊男行政二部保安課副課長06476黃琳女行政三部環務課管理師07023周遠寒男行政一部人事課管理師11305張林溪男行政二部伙食課管理師11500李芹女行政二部人事課管理師工號姓名職位姓名部門性別abcabc11500李芹管理師黃琳06198楚天闊副課長楚天闊07023周遠寒管理師周遠寒06132雲飛揚課長雲飛揚06476黃琳管理師李芹公式字串全角狀態下輸入的標點也將被視為兩個字元。函數的應用實務=left(B168,2)=mid(B168,4,4)=IF(C18560,通過考試,要補考)Logical_test為條件式，表示計算結果為TRUE或FLASE的任意值或表達式。Value_if_true當條件成立時，應當傳回的值，也可以是另一個公式.value_if_false當條件不成立時，應當傳回的值，也可以是另一個公式