2016年計算機二級MS-Office-Excel函數(shù)公式大集合

1、2016 年計算機二紡 MS-Office-Excel 函數(shù)公式大集 合第1套(2)圖書名稱=VLOOKUP(D3,編號對照！$A$3:$C$19,2,FALSE)(3)單價=VLOOKUP(D3,編號對照！$A$3:$C$19,3,FALSE)小計=單價口銷量(本)(5)所有訂單的總銷售額=SUM(訂單明細(xì)表！H3:H636)撤銷計劃列”(6)MS Office高級應(yīng)用圖書在2012年的總銷售額降序”=SUMPRODUCT(1*(訂單明細(xì)表!E3:E262= "MS Office 高級應(yīng)用”)訂單明細(xì) 表!H3:H262)(7)隆華書店在2011年第3季度(7月1日9月30日)的總

2、銷售額=SUMPRODUCT(1*(訂單明細(xì)表！C305c461 = "隆華書店”訂單明細(xì)表！H350:H461)(8)隆華書店在2011年的每月平均銷售額(保留2位小數(shù))=SUMPRODUCT(1*( 訂單明細(xì)表！C262c636 ="隆華書店”)，訂單明細(xì) 表!H263:H636)/12 使用 SUMIFS :(5) =SUBTOTAL(9,訂單明細(xì)表!H3:H636)(6) =SUMIFS(訂單明細(xì)表！H3:H636，訂單明細(xì)表！E3:E636，訂單明細(xì)表!E19,訂單明細(xì)表 舊3:B636,”>=2012-1-1",訂單明細(xì)表 舊3:B636,“&l

3、t;=2012-12-31") (7) =SUMIFS(訂單明細(xì)表！H3:H636，訂單明細(xì)表！C3c636，訂單明細(xì)表!C12,訂單明細(xì)表 舊3:B636,”>=2011-7-1",訂單明細(xì)表 舊3:B636,“<=2011-9-30”)(8) =SUMIFS(訂單明細(xì)表！H3:H636，訂單明細(xì)表！C3c636，訂單明細(xì)表 ！C12,訂單明細(xì)表舊3:B636,”>=2011-1-1", 訂單明細(xì) 表!B3:B636,“<=2011-12-31")/12注意：用SUNPRODUCT函數(shù)之前先排序，尤其是涉及時間的時候。第2套(4

4、)班級=LOOKUP(MID(A2,3,2), “ 01“，“ 02“，" 038),“班 1“，“班" )第3套(1) 一季度銷售額(元)、二季度銷售額(元)=產(chǎn)品基本信息表！C2*C2(2) 一二季度銷售總量、一二季度銷售總額二一季度銷售情況表！C2+'二季度銷售情況表!C2二一季度銷售情況表！D2+'二季度銷售 情況表!D2(3)銷售額排名=RANK(D2,$D$2:$D$21,0) RANK 函數(shù)排序區(qū)域絕對引用。第4套合并計算雙擊工作表sheet3的表名，在編輯狀態(tài)下輸入"比較數(shù)據(jù)”。在該工作表的A1中輸入” 地區(qū)”，在【數(shù)據(jù)】選項卡的【

5、數(shù)據(jù)工具】組中單擊 “合并計算”按鈕，彈出“合并計算” 對話框，設(shè)置"函數(shù)"為"求和"，在"引用位置"文本框中鍵入第一個區(qū)域"第五次普查數(shù) 據(jù)!$A$1:$C$34",單擊"添加"按鈕，鍵入第二個區(qū)域"第六次普查數(shù)據(jù)！$A$1:$C$34", 單擊"添加"按鈕，在"標(biāo)簽位置"下勾選"首行"復(fù)選框和"最左列”復(fù)選框，然后單擊" 確定"按鈕。數(shù)據(jù)透視表篩選單擊行標(biāo)簽右側(cè)的"標(biāo)簽篩選

6、"按鈕，在彈出的下拉列表中選擇"值篩選"，打開級聯(lián)菜 單，選擇"大于"，彈出"值篩選(地區(qū))”對話框，在第一個文本框中選擇"求和項：2010 年人口數(shù)(萬人)”，第二個文本框選擇“大于"，在第三個文本框中輸入"5000”,單擊 "確定"按鈕。在日期后添加星期在"費用報銷管理”工作表中，選中“日期”數(shù)據(jù)列，單擊鼠標(biāo)右鍵，在彈出的快捷菜單 中選擇”設(shè)置單元格格式”命令，彈出”設(shè)置單元格格式”對話框。切換至“數(shù)字”選項卡, 在“分類"列表框中選擇“自定義"命令，

7、在右側(cè)的"示例"組中"類型"列表框中輸入"yyyy"年"m"月"d"日"aaaa "。設(shè)置完畢后單擊"確定"按鈕即可。（2）是否加班=IF（WEEKDAY（A3,2）>5, “ 是“，否”）地區(qū) =LEFT（C3,3）（5）2013年第二季度發(fā)生在北京市的差旅費用金額總計為=SUMPRODUCT（1*（費用報銷管理！D74:D340="北京市”庚用報銷管理！G74:G340）=SUMIFS（費用報銷管理！G3:G401,費用報銷管理！D3:

8、D401,費用報銷管理！D11,費用報銷管理!A3:A401,">=2013-4-T',費用報銷管理！A3:A401,”<=2013-6-30"）（6）2013年錢順卓報銷的火車票總計金額為=SUMPRODUCT（1*（費用報銷管理 舊3:B401= "錢順卓”）1*（費用報銷管理！F3:F401= "火車票”）費用報銷管理!G3:G401）=SUMIFS（費用報銷管理！G3:G401,費用報銷管理！F3:F401,費用報銷管理！F10,費用報銷管理舊3:B401,費用報銷管理 舊378）（7）2013年差旅費用金額中，飛機票占所有報

9、銷費用的比例為（保留 2位小數(shù)）=SUMPRODUCT（1*（費用報銷管理！F3:F401= ”飛機票”），費用報銷管理!G3:G401）/SUM（費用報銷管理!G3:G401）=SUMIFS（費用報銷管理！G3:G401,費用報銷管理！F3:F401,費用報銷管理！F3）/SUM（費用報銷管理!G3:G401）（8）2013年發(fā)生在周末（星期六和星期日）中的通訊補助總金額為=SUMPRODUCT（費用報銷管理！H3:H401= "是“）*費用報銷管理！F3:F401= "通訊補助”添用報銷管理！ G3:G401）=SUMIFS（費用報銷管理！G3:G401,費用報銷管理！

10、H3:H401,費用報銷管理！H3,費用報銷管理!F3:F401,費用報銷管理!F391）第6套定義名稱在 平均單價”工作表中選中B3: C7區(qū)域，單擊鼠標(biāo)右鍵，在彈出的下拉列表中選擇 " 定義名稱“命令，打開"新建名稱”對話框。在"名稱"中輸入"商品均價”后單擊 確定”按 鈕即可。銷售額：=VLOOKUP（D4,商品均價,2,0）*E4第7套(5)應(yīng)交個人所得稅=ROUND(IF(K3<=1500,K3*0.03,IF(K3<=4500,K3*0.1-105,IF(K3<=9000,K3*02555,IF (K3<=3

11、5000K3*0.25-1005,IF(K3<=55000K3*0.3-2755,IF(K3<=80000K3*0.35-5505,IF( K3>80000,K3*0.45-13505),2)(8)管理部門應(yīng)付工資合計=SUMPRODUCT(1*(D3:D17=行政”),I3:I17)管理部門實發(fā)工資=SUMPRODUCT(1*(D3:D17=行政" ),M3:M17)第8套平均成績和總分 =SUM(D3:L3) =A VERAGE(D3:L3)年級排名 =RANK(M3,M$3:M$102,0)學(xué)號的第三位為專業(yè)代碼、第四位代表班級序號="法律” &am

12、p;TEXT(MID(B3,3,2), ” DBNuml )&"班”=LOOKUP(MID(B3,3,2),"01","02","03","04","法律一班","法律二班","法律三班","法律四班")插入圖表到指定單元格按住AIT鍵，帶鼠標(biāo)指針出現(xiàn)斜向上箭頭是開始拖動第9套插入一列插入”在C3中輸入=IF(YEAR(銷售訂單!$B3)=2013,MONTH(銷售訂單!$B3),0)« Office商務(wù)辦公

13、好幫手1月(銷售)=SUMIFS(銷售訂單！$H$3:$H$678，銷售訂單！$E$3:$E$678,'2013 年圖書銷售分析'!$A4, 銷售訂單!$C$3:$C$678,1)Office商務(wù)辦公好幫手2月(銷售)=SUMIFS(銷售訂單！$H$3:$H$678，銷售訂單！$E$3:$E$678,'2013 年圖書銷售分析'!$A4, 銷售訂單!$C$3:$C$678,2)Office商務(wù)辦公好幫手3月(銷售)=SUMIFS(銷售訂單！$H$3:$H$678，銷售訂單！$E$3:$E$678,'2013 年圖書銷售分析'!$A4, 銷售訂單!

14、$C$3:$C$678,3)每月圖書總銷量：=SUBTOTAL(109,B4:B11)添加匯總行第10套分列選中B列單元格，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇 單元格，將光標(biāo)置于 學(xué)號”和 名字”之間，按3次空格鍵, 擊【數(shù)據(jù)工具】組中的 分列”按鈕，在彈出的對話框中選擇插入”選項。然后選中 然后選中A列單元格,A1單同定寬度”單選按鈕，單擊下一步”按鈕，然后建立分列線。單擊 下一步"按鈕，保持默認(rèn)設(shè)置，單擊 完成"按鈕(3)性別 =IF(MOD(MID(C2,17,1),2)=1,男“，女”)出生日期 =-TEXT(MID(C2,7,8), ” 小00-00”)年”,M

15、ID(C8,11,2),“月=CONCATENATE(MID(C8,7,4),“”,MID(C8,13,2),"日") 年齡：=DATEDIF(- TEXT(MID(C2,7,8), ” 小00-00"),TODAY(), " y" )=INT(TODAY()-E2)/365) (4)語文姓名=VLOOKUP(A2,初三學(xué)生檔案！$A$1:$B$56,2,FALSE),其余方法一樣按照三個比例計算學(xué)期成績=SUM(C2*30%)+(D2*30%)+(E2*40%)按成績由高到低的順序排名，并按第n名”的形式填入 班級名次”列中="第&

16、quot;&RANK(F2,$F$2:$F$45)&"名"期末總評”數(shù)學(xué)，語文：=IF(F2>=102,"優(yōu)秀",IF(F2>=84,"良好",IF(F2>=72,"及格",IF(F2>72,"及 格","不及格")英語及其他：=IF(F3>=90,"優(yōu)秀",IF(F3>=75,"良好",IF(F3>=60,"及格"，"不及格")姓名：=V

17、LOOKUP(A3,初三學(xué)生檔案！$A$2:$B$56,2,FALSE)語文成績：=VLOOKUP(A3,語文!A2:F45,6,FALSE)第11套(3)職稱：=VLOOKUP(E12,教師基本信息！$D$3:$E$22,2,FALSE) 學(xué)時數(shù)：=SUMIF(授課信息表！$D$3:$D$72,E3,授課信息表！$F$3:$F$72)第12套(3) =IF(H2=0，“平"，IF(H2>0,借“，貸”)第13套(3)銷量：=VLOOKUP(A4,銷量信息,3,FALSE)第14套條件格式選中F2: G14,單擊【開始】選項卡下【樣式】組中的“條件格式"按鈕，選擇”突

18、出顯 示單元格規(guī)則”中的"其他規(guī)則"，彈出"新建格式規(guī)則”對話框，在"編輯規(guī)則說明"選項 下設(shè)置單元格值小于 80。然后單擊"格式"按鈕，彈出”設(shè)置單元格格式”對話框，在" 填充”選項卡下選擇"紅色"，單擊"確定"按鈕。分類匯總選中C15,單擊【數(shù)據(jù)】選項卡下【分級顯示】組中的 “分類匯總”按鈕，彈出”分類匯 總”對話框，單擊"分類字段"組中的下拉按鈕選擇"班級",單擊"匯總方式"組中的下拉 按鈕選擇"平均

19、值"，在"選定匯總項"組中勾選"高等代數(shù)"、"數(shù)學(xué)分析"、"大學(xué)英語"、 "大學(xué)物理"、"VB程序設(shè)計"、"C語言程序設(shè)計"、"Matlab"復(fù)選框，并勾選"每組數(shù) 據(jù)分頁"復(fù)選框。其他分類匯總方法一致第15套(4)班級:=IF(MID(A3,4,2)= " 01" 班1,IF(MID(A3,4,2)=" 02班：”,了班”)(5)姓名：=VLOOKUP(A3,學(xué)號對照！$A$

20、3:$B$20,2,FALSE)第16套(2)出生日期:=MID(F3,7,4)& ”年" &MID(F3,11,2)& 月" &MID(F3,13,2)& 舊”(3)工齡:=INT(TODAY()-I3)/365)(4)工齡工資：=AVERAGEIF(員工檔案！H3:H37, “本科”員工檔案！K3:K37)(5)所有人的基礎(chǔ)工資：=SUM(員工檔案！M3:M37)(6)項目經(jīng)理的基本工資總額：=員工檔案！K3+員工檔案！K7本科生平均基本工資： =AVERAGEIF(員工檔案！H3:H37, “本科”員工檔 案!K3:K37)第1

21、7套根據(jù)生成的數(shù)據(jù)透視表，在透視表下方創(chuàng)建一個簇狀柱形圖，圖表中僅對博達(dá)書店一月 份的銷售額小計進(jìn)行比較。選中博達(dá)書店銷售額小計，單擊【開始】選項卡下【圖表】組中的“柱形圖”按鈕，在彈出的下拉列表中選擇"簇狀柱形圖”命令。步驟2:在“數(shù)據(jù)透視圖“中單擊"書店名稱“右側(cè)下三角按鈕，在下拉列表中只選擇“博 達(dá)書店”復(fù)選框。第18套條件格式選擇【B3:L14】單元格，切換至【開始】選項卡，單擊【樣式】選項組下的【條件格式】下拉按鈕，在下拉列表中選擇【突出顯示單元格規(guī)則】-【大于】，在為大于以下值的單元格設(shè)置格式”文本框中輸入“1000；使用默認(rèn)設(shè)置 淺紅填充色深紅色文本”， 單擊

22、確定”按鈕。步驟2:選擇【M3:M14 單元格，切換至【開始】選項卡，單擊【樣式】選項組下的【條件格式】下拉按鈕，在彈出的下拉列表中選擇【突出顯示單元格規(guī)則】-【大于】，在為大于以下值的單元格設(shè)置格式”文本框中輸入“二$M$15*110% ,設(shè)置顏色為黃填 充色深黃色文本”，單擊 確定”按鈕。季度：="第" &INT(1+(MONTH(A3) -1)/3)& “季度”第19套停放時間：=DATEDIF(F2,H2," YDD )*24+(I2 -G2)收費金額:=E2*ROUNDUP(HOUR(J2)*60+MINUTE(J2)/15),0)擬收費金額:=E2*ROUNDDOWN(HOUR(J2)*60+MI