學Excel公式·函數與圖表（上

學Excel公式·函數與圖表（上）1、顯示或隱藏公式：通常情況下要查看公式時，需要單擊公式所在的單元格，然後在編輯欄中查看公式。為了查看方便，可以進行相關的設置。1）單擊[工具]—[選項]菜單項；2）此時即可打開[選項]對話框，切換到[視圖]選項卡中，然後在[窗口選項]組合框中選中[公式]複選框；3）單擊[確定]按鈕即可得到相應的結果。2、將公式結果轉換為數值：如果不再需要修改公式，可以將其轉換為數值格式。轉換後，即使公式中引用單元格中的數據，其結果也不會隨之改變。1）選中用公式計算得出的數值所在的列或行並[複製]；2）單擊[編輯]—[選擇性粘貼]菜單項打開[選擇性粘貼]對話框，選擇[粘貼]組合框中的[數值]單選按鈕；3）單擊[確定]按鈕即可將公式結果轉化為數值，此時從編輯欄中即可看到剛才選中的單元格區域中顯示的為數值了。3、相對引用：相對引用是指直接輸入單元格的位置名稱。在默認情況下複製與填充公式時，公式中單元格的引用位置會做相應的變化。1）在G3單元格中輸入公式「=AVERAGE（C3：F3）」，然後將公式向下填充到單元格G4；2）釋放滑鼠後即可求得相應的結果，然後將公式結果顯示為公式，即可看到公式中相對引用的相應變化，G4單元格顯示「=AVERAGE（C4：F4）」 。4、絕對引用：絕對引用是指在引用單元格的同時添加符號「$」，表示引用的位置是絕對的。在複製公式時，公式中單元格的引用始終固定不變。1）在G3單元格中輸入公式「=AVERAGE（$C$3：$F$3）」，然後將公式向下填充到單元格G4；2）釋放滑鼠後即可求得相應的結果，然後將公式結果顯示為公式，即可看到公式中相對引用的相應變化，G4單元格顯示「=AVERAGE（$C$4：$F$3）」 。5、混合引用：混合引用是將兩種單元格的引用混合使用。在行號或者列號的前面加符號「$」，該符合後面的位置就是絕對引用。當進行公式的複製時，其中絕對引用不發生改變，而相對引用則發生變化。1）在G3單元格中輸入公式「=F3/$F$6」，然後將公式向下填充到單元格G4；2）釋放滑鼠後即可求得相應的結果，然後將公式結果顯示為公式，即可看到公式中相對引用的相應變化，G4單元格顯示「=F4/$F$6」 。6、運算符的優先順序：運 算 符說明運 算 符說明—（負號）1+、—（加與減）5%（百分比）2&（文本連接）6^（乘方）3=、>、<、>=、<=、<>（比較運算符）7*、/（乘與除）47、函數種類：邏輯函數、時間與日期函數、數學與三角函數、文本函數、信息函數、財務函數、資料庫函數、統計函數、查看與引用函數、工程函數。8、逐步計算嵌套公式：在單元格G2中輸入以下公式：「=IF（SUM（D2：F2）/3）>=85,」優」,」良」)」。ABCDEFG1學號姓名高等數學英語C語言等級22002256001肖欣80967832002256002董傑81857642002256003劉峰7267909、日期函數實例應用：在C3單元格中輸入公式：「=DATA（2006,12,25）—TODAY()&」天」」。ABC1倒計時顯示牌2名稱倒計時天數3距2006年聖誕節10、計算指定日期之間的年數、月數和天數：1）在E3單元格中輸入公式：「=DATEDIF（B3,C3,」Y」」；2）在E4單元格中輸入公式：「=DATEDIF（B4,C4,」M」」；3）；3）在E5單元格中輸入公式：「=DATEDIF（B5,C5,」D」」。ABCDE1計算不同間隔條件下的時間差2起始日期結束日期間隔條件時間差31990-10-12006-10-25年數41990-10-12006-10-26月數51990-10-12006-10-27天數11、判斷年齡是否已滿：1）計算年齡：在E3單元格中輸入公式：「=YEAR（$E$1）—YEAR（D3）」；2）判斷年齡是否已滿：在F3單元格中輸入公式：「=IF（$E$1<=DATE（YEAR($E$1),MONTH(D3),DAY(D3),」未滿」, 」已滿」」。ABCDEF1當前日期2007年12月19日2編號姓名出生日期年齡是否已滿3G0001劉明1988年11月5日4G0002江風1983年11月5日5G0003董傑1982年6月17日12、商品過期提醒：在E3單元格中輸入公式：「=IF（D3<TODAY(),」過期」, 」 」）」。ABCDE12商品編號商品名稱進貨時間保質到期過期與否3CP0001麵包2006-10-42006-10-214CP0002牛奶2006-10-82006-10-2513、計算租車費：某公園出租雙人腳踏車，每小時20元，出租時間小於等於30分鐘則按0.5小時計算費用，出租時間大於30分鐘而小於等於1小時則按1小時計算費用。1）計算租車「分鐘數」：在F4單元格中輸入公式：「=MINUTE（D4—C4）」；2）計算租車「總時間」：在G4單元格中輸入公式：「=E4+IF（E4<=30,0.5,1）」；3）計算「收費金額」：在H4單元格中輸入公式：「=G4*20」。ABCDEFGH1租車計時收費記錄表2車編號租車時間還車時間時間收費金額3小時數分鐘數總時間4GL00012006-10-1 9:402006-10-1 11:265GL00022006-10-1 9:462006-10-1 11:3514、計算話吧話費：有一家話吧，通話計費按分鐘進行計算，並規定30秒以內按0.5分鐘計算，大於30秒按1分鐘計算。1）計算「小時」值：在E3單元格中輸入公式：「=HOUR（D3）—HOUR（C3）」；2）計算「分鐘」：在F3單元格中輸入公式：「=MINUTE（D3）—MINUTE（C3）」；3）計算「秒」：在G3單元格中輸入公式：「=SECOND（D3）」；4）計算「合計時間」：在H3單元格中輸入公式：「E3*60+F3+IF（G3<=30,0.5,1）」；5）計算「總話費」：在J3單元格中輸入公式：「=I3*H3」。ABCDEFGHIJ1電話編號接通時間掛斷時間通話時間合計時間每分話費總話費2小時分鐘秒3DH000110:4510:56:254DH000210:2310:26:285DH000311:3611:59:2015、MID函數：MID函數的功能是返迴文本字元串中從指定位置開始的特定字元，該數目由用戶指定。其語法為：MID（text,start_num,num_chars）。其中text是包含要提取字元的文本字元串；start_num是文本中要提取的第一個字元的位置，文本中第一個字元的start_num為1，依次類推；num_chars指定希望MID從文本中返回字元的個數。關於此函數的應用，在下面的「21、提取用戶資料的相關信息：1）提取出生日期：」裡面中有詳細運用和講解。16、LEN函數：LEN函數的功能是返迴文本字元串中的字元數。其語法為：LEN（text）。其中text是要查找其長度的文本。本例利用LEN函數計算單元格中字元的個數：在D4單元格中輸入公式：「=LEN（A1）」。ABCDEFG1在上半年的工作中，我做到了按照上級領導的安排，完成了應完成的任務，而且超額完成了實際任務的2%。當然還存在許多不足，我決定在下半年的工作中再上一個台階。234總字數17、REPLACE函數：REPLACE函數的功能是使用其他的文本字元串並根據所指定的字元數替換某個文本字元串中的文本。其語法為：REPLACE（old_text,start_num,num_chars,new_text）。其中old_text是要替換其部分字元的文本；start_num是要用new_text替換的old_text中字元的個數，new_text是要用於替換old_text中字元的文本。本例利用REPLACE函數更新手機號：在F3單元格中輸入公式：「=REPLACE（E3,1,3, 」0325 」）」。ABCDEF1職員基本情況表2編號姓名所屬部門聯繫方式3R001李飛業務部032562354R002董傑銷售部0325689318、REM函數：REM函數的功能是依照貨幣格式將小數四捨五入到指定的位數並轉換成文本。其語法為：RMB（number,decimals），其中number為數字、包含數字的文本引用或者計算結果為數字的公式；decimals為十進位的小數位數，如果為負數number則從小數點向左按相應的位數取整，如果省略其值則為2。本例利用DOLLAR和REM函數轉換貨款的貨幣格式：1）在K3單元格中輸入公式：「=DOLLAR（J3,1）」；2）在M3單元格中輸入公式：「=RMB（L3,2）」。GHIJKLM12編號商品名進口價進口價($)銷售價銷售價(￥)3G0001商品123518004G0002商品2239150019、提取區號和電話號碼：1）提取區號：提取D3單元格中左邊的4位數字，在E3單元格中輸入公式：「=LEFT（D3,4）」；2）提取電話號碼：提取D3單元格中右邊的7位數字，在F3單元格中輸入公式：「=RIGHT（D3,7）」。ABCDEF1職員資料2姓名性別聯繫方式區號電話號碼3李冰女053526358644董坤男0633845126320、返回個人稱呼：在E3單元格中輸入公式：「=ONCATENATE（D3,」市」,LEFT(B3,1),JF(C3=」女」,」女士」,」先生」」，按下[Ctrl]+[Enter]組合鍵即可在E3中顯示出：「煙台市李女士」。ABCDE12姓名性別所在城市稱呼3李冰女煙台4董坤男日照21、提取用戶資料的相關信息：1）提取出生日期：在E3單元格中輸入公式：「=IF（LEN（C3）=15,MID(C3,7,6),MID(C3,9,6)）」，此公式的意思是從身份證號碼中提取出生日期時，如果是15位的身份證號，出生日期為從第7位數字開始的6位數字；如果是18位的身份證號，出生日期為從第9位數字開始的6位數字；2）返回性別的順序碼：在H3單元格中輸入公式：「=VALUE（IF（LEN（C3）=15,RIGHT(C3,1),MID(C3,17,1)」，回車後H3：H10單元格中顯示如下；3）判斷性別：在F3單元格中輸入公式：「=IF（OR（H3=1, H3=3, H3=7, H3=9）, 」男」,」女」）」。在15位的身份證號碼中最後一位為順序碼，奇數為男性，偶數為女性，由於升級為18位身份證號碼後最末位處添加了一位校驗碼，因此判斷性別的順序碼為倒數第2位；4）確定稱呼的方法同上例。ABCDEFGH1用戶資料2姓名身份證號碼家庭住址出生日期性別稱呼3李冰37110219830225**12山東煙台14董坤37110219810618**38山東日照35尚雷372321841209**4山東濱州46郭明37110219801123**45山東青島47王風37110219820815**76山東濰坊78肖欣371102841208**8山東臨沂89姜聰37110219850519**76山東威海710蔡冬37110219831009**21山東濟南222、資料庫函數DAVERAGE函數：DAVERAGE函數的功能是返回列表或者資料庫中滿足指定條件的列中數值的平均值，其語法為：DAVERAGE（database,field,criteria），其中database是構成列表或者資料庫的單元格區域。資料庫是包含一組相關數據的列表，其中包含相關信息的行為記錄，包含數據的列為欄位。列表的第一行包含著每一列的標誌項，field指定函數所使用的數據列，criteria為一組包含給定條件的單元格區域。該函數的應用例如下表：計算「面霜的平均銷售量」，在E11單元格中輸入公式：「= DAVERAGE (A2:E8,5,C10:C11)」，按下[Ctrl]+[Enter]組合鍵即可求得相應的結果，此公式中的5是指資料庫表中的第5列E「銷售數量」，另外求「次數」用「DOCUNT函數」；求「價格」用「DGET函數」；求「最高價格」用「DMAX函數」；求「最低價格」用「DMN函數」；求「總金額」用「DSUM函數」；以上所有函數的公式使用方法同DAVERAG函數。ABCDEFG1銷售數據表2產品名稱品牌供應商單價銷售數量金額銷售日期3面霜雅芳萊山雅芳店80.02318402006-10-14爽膚水李醫生佳樂商城34.5186212006-10-25爽膚水李醫生白雪超市34.5186212006-10-66乳液丁佳宜佳樂商城30.0206002006-10-77眼霜天使雪薇陽光商場98.02322542006-10-88面霜雅芳萊山雅芳店80.02419202006-10-10910產品名稱計算結果11面霜23、計算商品受歡迎程度：1）選中單元格B1，然後單擊[插入]—[符號]—[]，如下表；2）選中單元格「J4：J13」，單擊[插入]—[函數]—[文本]—[REPT]選項；3）單擊[確定]按鈕打開[函數參數]對話框，單擊[TEXT]文本框右側的[摺疊]按鈕，選擇單元格B1，然後單擊[展開]按鈕，展開[函數參數]對話框；4）在[Number_times]文本框中輸入以下公式：J4：J8/80，按下[Ctrl]+[Shift]組合鍵，然後單擊[確定]按鈕即可求得相應的結果如下表中的K4：K8單元格中所顯示。ABCDEFGHIJK12編號書名半年銷售量合計受歡迎程度31234564B001新手學上網3556573268723205B002新手學裝軟體3225463921422056B003新編WinXP手冊45768512386724877B004新編Excel公司辦公10257896876694618B005新手學硬體維護23654535716029924、常用數學函數SUBTOTAL：SUBTOTAL函數的功能是返回列表或者資料庫中的分類匯總。通常使用「數據」菜單中的「分類匯總」菜單項可很容易地創建帶有分類匯總的列表。一旦創建了分類匯總，就可以通過編輯SUBTOTAL函數對該列表進行修改。其語法為：SUBTOTAL（function_num,ref1,ref2,…），其中function_num為1到11或者101到111之間的數字，指定使用何種函數在列表中進行分類匯總計算；參數ref1,ref2,…為需要進行分類匯總計算的1到29個區域或引用。參數function_num的具體含義如下表所示：function_num函數返回值function_num函數返回值function_num函數返回值function_num函數返回值1AVERAGE4MAX7STDEV10VAP2COUNT5MIN8STDEVP11WARP3COUNTA6PRODUCT9SUM例如：計算銷售總量和月平均銷量：1）計算「銷售總量」：在I4單元格中輸入以下公式：「=SUM（C4:H4）」;2）計算「月平均銷售量」：在J4單元格中輸入以下公式：「=SUBTOTAL（1,C4:H4）」。ABCDEFGHIJ1宏偉集團上半年銷售情況2編號營銷員銷售量（件）銷售總量月平均銷量31234564CP0001高峰2312511622401893055CP0002李曉敏123320275261138926CP0003張桐10921417529135026725、SUMIF函數：SUMIF函數的功能是根據指定的條件對若干個單元格求和。其語法為：SUMIF(range,criteria,sum_range)，其中range為用於條件判斷的單元格區域，criteria為確定哪些單元格將被相加求和的條件，其形式可以為數字、表達式或文本，sum_range是需要求和的實際單元格。例如：統計成績表，計算「平均分大於80分的人數」，在E8單元格中輸入以下公式：「=SUMIF（C3:C6,」>80」,H3:H12）」。ABCDEFGH1計022-2班2004年度期末成績表2學號姓名C語言高等數學英語離散數學平均分人數3200202001李冰8067829280.2514200202002董坤9280627376.7515200202003尚雷6882739078.2516200202004郭明8592787682.75178平均分大於80分的人數26、CEILING函數：CEILING函數的功能是將參數number向上舍入（沿絕對值增大的方向）為最接近的significance的倍數。其語法為CEILING(number, significance)，其中number為要四捨五入的數值，significance為需要四捨五入的乘數。例如：計算書稿的印紙：1）計算「實需紙張數」：在E3單元格中輸入以下公式：「=C3/D3」；2）計算「應需紙張數」：在F3單元格中輸入以下公式：「CEILING(E3,0.25)」，大家可以看到在下表的F列中，所有的數字都是以0.25的差值向上增大。ABCDEF12書稿名稱總頁數開本實需紙張數應需紙張數3新手學Excel2641616.516.54新手學函數與圖表2521615.7515.755新手學Office3021618.875196新手學商務辦公2841617.7517.757新手學Access2601616.2516.258新手學Photoshop2981618.62518.7527、ROUND函數：ROUND函數的功能是返回某個數字按指定位數取整後的數字。其語法為：ROUND(number, num_digits)。其中number為需要進行四捨五入的數字，num_digits是指定的位數，按此位數四捨五入。28、PRODUCT函數：PRODUCT函數的功能是將所有的以參數形式給出的數字相乘並返回乘積值。其語法為：PRODUCT（number1, number2,…）。其中number1, number2,…為1到30個需要相乘的數字參數。29、ROUNDUP函數：ROUNDUP函數的功能是實現遠離零值，向上舍入數字，其語法為：ROUNDup(number, num_digits)。其中number為需要向上舍入的任意實數，num_digits表示四捨五入後的數字的位數。例如計算工資：某企業業務部在月底要根據員工的業績發工資。其中獎金按照業績的15%提成，基本工資為800元，總工資為兩者之和。1）計算「業績」：在單元格G4中輸入以下公式：「=SUMPRODUCT($C$9: $E$9,C4:E4)」；2）計算「獎金」：在單元格H4中輸入以下公式：「=ROUNDUP(G4*15%,1)」；3）計算「總工資」：在單元格I4中輸入以下公式：「=SUM(F4:H4)」。ABCDEFGHI12業務員銷售產品基本工資業績獎金總工資3滑鼠鍵盤顯示器4高峰23182￥800.005李曉敏16284￥800.006張桐21225￥800.0078產品名稱滑鼠鍵盤顯示器9單價￥32.80￥64.50￥1,358.6030、INT函數：INT函數的功能是將數字向下舍入到最接近的整數。其語法為：INT(number)。其中number為需要進行向下舍入取整的實數。31、MOD函數：MOD函數的功能是返回兩數相除的餘數，結果的正負號與除數相同。其語法為：MOD(number,divisor)，其中number表示被除數，divisor表示除數。例如：計算各種面額的數量：1）計算面額為100元的數量：在單元格F4中輸入以下公式：「=INT(E4/100)」；2）計算面額為50元的數量：在單元格G4中輸入以下公式：「=INT(MOD(E4,100)/50)」；3）計算面額為10元的數量：在單元格H4中輸入以下公式：「=INT(MOD(E4,50)/10)」；4）計算「本月餘額」：在單元格I4中輸入以下公式：「=MOD(E4,10)」。ABCDEFGHI1工資表2業務員基本工資獎金總工資面額本月餘額3100元50元10元4高峰800.00694.901,494.905李曉敏800.001,164.801,964.806張桐800.001,335.202,135.20
推薦閱讀：