計(jì)算機(jī)二級(jí)Office2010Eexcel公式匯總

1、Error! No text of specified style in document.EXCEL函數(shù)公式難點(diǎn)大全第1套1第2套2第3套3第4套3第5套4第6套6第7套7第8套7第9套8第10套9第11套11第12套11第13套11第14套12第15套12第16套13第17套14第18套14第19套15第20套1517第1套(2)圖書(shū)名稱=VLOOKUP(D3,編號(hào)對(duì)照!$A$3:$C$19,2,FALSE)(3)單價(jià)=VLOOKUP(D3,編號(hào)對(duì)照!$A$3:$C$19,3,FALSE)(4)小計(jì)=單價(jià)*銷量（本）(5)所有訂單的總銷售額=SUM(訂單明細(xì)表!H3:H636)“撤銷計(jì)劃列

2、”(6) MS Office高級(jí)應(yīng)用圖書(shū)在2012年的總銷售額“降序” =SUMPRODUCT(1*(訂單明細(xì)表!E3:E262=”MS Office高級(jí)應(yīng)用”),訂單明細(xì)表!H3:H262)(7)隆華書(shū)店在2011年第3季度（7月1日9月30日）的總銷售額=SUMPRODUCT(1*(訂單明細(xì)表!C305:C461 =”隆華書(shū)店”),訂單明細(xì)表!H350:H461)(8)隆華書(shū)店在2011年的每月平均銷售額（保留2位小數(shù)）=SUMPRODUCT(1*(訂單明細(xì)表!C262:C636 =”隆華書(shū)店”),訂單明細(xì)表!H263:H636)/12使用SUMIFS：（5）=SUBTOTAL(9,訂單明

3、細(xì)表!H3:H636)（6）=SUMIFS(訂單明細(xì)表!H3:H636,訂單明細(xì)表!E3:E636,訂單明細(xì)表!E19,訂單明細(xì)表!B3:B636,">=2012-1-1",訂單明細(xì)表!B3:B636,"<=2012-12-31")（7）=SUMIFS(訂單明細(xì)表!H3:H636,訂單明細(xì)表!C3:C636,訂單明細(xì)表!C12,訂單明細(xì)表!B3:B636,">=2011-7-1",訂單明細(xì)表!B3:B636,"<=2011-9-30")（8）=SUMIFS(訂單明細(xì)表!H3:H636,訂單明

4、細(xì)表!C3:C636,訂單明細(xì)表!C12,訂單明細(xì)表!B3:B636,">=2011-1-1",訂單明細(xì)表!B3:B636,"<=2011-12-31")/12注意：用SUNPRODUCT函數(shù)之前先排序，尤其是涉及時(shí)間的時(shí)候。第2套(4)班級(jí)=LOOKUP(MID(A2,3,2),”01”,”02”,”03”,”1班”,”2班”,”3班”)第3套（1） 一季度銷售額(元）、二季度銷售額（元）=產(chǎn)品基本信息表!C2*C2（2） 一二季度銷售總量、一二季度銷售總額=一季度銷售情況表!C2+'二季度銷售情況表 '!C2=一季度銷售情

5、況表!D2+'二季度銷售情況表 '!D2(3)銷售額排名=RANK(D2,$D$2:$D$21,0) RANK函數(shù)排序區(qū)域絕對(duì)引用。第4套數(shù)據(jù)自網(wǎng)頁(yè)導(dǎo)入在考生文件夾下雙擊打開(kāi)網(wǎng)頁(yè)"第五次全國(guó)人口普查公報(bào).htm"，在工作表"第五次普查數(shù)據(jù)"中選中A1，單擊【數(shù)據(jù)】選項(xiàng)卡下【獲取外部數(shù)據(jù)】組中的"自網(wǎng)站"按鈕，彈出"新建Web查詢"對(duì)話框，在"地址"文本框中輸入網(wǎng)頁(yè)"第五次全國(guó)人口普查公報(bào).htm"的地址，單擊右側(cè)的"轉(zhuǎn)到"按鈕。單擊要選擇的

6、表旁邊的帶方框的黑色箭頭，使黑色箭頭變成對(duì)號(hào)，然后單擊"導(dǎo)入"按鈕。之后會(huì)彈出"導(dǎo)入數(shù)據(jù)"對(duì)話框，選擇"數(shù)據(jù)的放置位置"為"現(xiàn)有工作表"，在文本框中輸入"=$A$1"，單擊"確定"按鈕。步驟2：按照上述方法瀏覽網(wǎng)頁(yè)"第六次全國(guó)人口普查公報(bào).htm"，將其中的"2010年第六次全國(guó)人口普查主要數(shù)據(jù)"表格導(dǎo)入到工作表"第六次普查數(shù)據(jù)"中。合并計(jì)算雙擊工作表sheet3的表名，在編輯狀態(tài)下輸入"比較數(shù)據(jù)"

7、;。在該工作表的A1中輸入"地區(qū)"，在【數(shù)據(jù)】選項(xiàng)卡的【數(shù)據(jù)工具】組中單擊"合并計(jì)算"按鈕，彈出"合并計(jì)算"對(duì)話框，設(shè)置"函數(shù)"為"求和"，在"引用位置"文本框中鍵入第一個(gè)區(qū)域"第五次普查數(shù)據(jù)!$A$1:$C$34"，單擊"添加"按鈕，鍵入第二個(gè)區(qū)域"第六次普查數(shù)據(jù)!$A$1:$C$34"，單擊"添加"按鈕，在"標(biāo)簽位置"下勾選"首行"復(fù)選框和"最

8、左列"復(fù)選框，然后單擊"確定"按鈕。數(shù)據(jù)透視表篩選單擊行標(biāo)簽右側(cè)的"標(biāo)簽篩選"按鈕，在彈出的下拉列表中選擇"值篩選"，打開(kāi)級(jí)聯(lián)菜單，選擇"大于"，彈出"值篩選（地區(qū)）"對(duì)話框，在第一個(gè)文本框中選擇"求和項(xiàng)：2010年人口數(shù)（萬(wàn)人）"，第二個(gè)文本框選擇"大于"，在第三個(gè)文本框中輸入"5000"，單擊"確定"按鈕。第5套在日期后添加星期在"費(fèi)用報(bào)銷管理"工作表中，選中"日期&quo

9、t;數(shù)據(jù)列，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇"設(shè)置單元格格式"命令，彈出"設(shè)置單元格格式"對(duì)話框。切換至"數(shù)字"選項(xiàng)卡，在"分類"列表框中選擇"自定義"命令，在右側(cè)的"示例"組中"類型"列表框中輸入" yyyy"年"m"月"d"日" aaaa "。設(shè)置完畢后單擊"確定"按鈕即可。(2)是否加班=IF(WEEKDAY(A3,2)>5,”是”,”否”)

10、(3)地區(qū)=LEFT(C3,3)(5)2013年第二季度發(fā)生在北京市的差旅費(fèi)用金額總計(jì)為=SUMPRODUCT(1*(費(fèi)用報(bào)銷管理!D74:D340=”北京市”),費(fèi)用報(bào)銷管理!G74:G340)=SUMIFS(費(fèi)用報(bào)銷管理!G3:G401,費(fèi)用報(bào)銷管理!D3:D401,費(fèi)用報(bào)銷管理!D11,費(fèi)用報(bào)銷管理!A3:A401,">=2013-4-1",費(fèi)用報(bào)銷管理!A3:A401,"<=2013-6-30")(6)2013年錢順卓報(bào)銷的火車票總計(jì)金額為=SUMPRODUCT(1*(費(fèi)用報(bào)銷管理!B3:B401=”錢順卓”), 1*(費(fèi)用報(bào)銷管理!

11、F3:F401=”火車票”)，費(fèi)用報(bào)銷管理!G3:G401)=SUMIFS(費(fèi)用報(bào)銷管理!G3:G401,費(fèi)用報(bào)銷管理!F3:F401,費(fèi)用報(bào)銷管理!F10,費(fèi)用報(bào)銷管理!B3:B401,費(fèi)用報(bào)銷管理!B378) (7)2013年差旅費(fèi)用金額中，飛機(jī)票占所有報(bào)銷費(fèi)用的比例為（保留2位小數(shù)）=SUMPRODUCT(1*(費(fèi)用報(bào)銷管理!F3:F401=”飛機(jī)票”),費(fèi)用報(bào)銷管理!G3:G401)/SUM(費(fèi)用報(bào)銷管理!G3:G401)=SUMIFS(費(fèi)用報(bào)銷管理!G3:G401,費(fèi)用報(bào)銷管理!F3:F401,費(fèi)用報(bào)銷管理!F3)/SUM(費(fèi)用報(bào)銷管理!G3:G401) (8)2013年發(fā)生在周末（

12、星期六和星期日）中的通訊補(bǔ)助總金額為= SUMPRODUCT(費(fèi)用報(bào)銷管理!H3:H401=”是”)*(費(fèi)用報(bào)銷管理!F3:F401=”通訊補(bǔ)助”),費(fèi)用報(bào)銷管理！G3:G401)=SUMIFS(費(fèi)用報(bào)銷管理!G3:G401,費(fèi)用報(bào)銷管理!H3:H401,費(fèi)用報(bào)銷管理!H3,費(fèi)用報(bào)銷管理!F3:F401,費(fèi)用報(bào)銷管理!F391)第6套定義名稱在“平均單價(jià)”工作表中選中B3：C7區(qū)域，單擊鼠標(biāo)右鍵，在彈出的下拉列表中選擇"定義名稱"命令，打開(kāi)"新建名稱"對(duì)話框。在"名稱"中輸入"商品均價(jià)"后單擊“確定”按鈕即可。銷售

13、額 =VLOOKUP(D4,商品均價(jià),2,0)*E4第7套(5)應(yīng)交個(gè)人所得稅=ROUND(IF(K3<=1500,K3*0.03,IF(K3<=4500,K3*0.1-105,IF(K3<=9000,K3*0.2-555,IF(K3<=35000,K3*0.25-1005,IF(K3<=55000,K3*0.3-2755,IF(K3<=80000,K3*0.35-5505,IF(K3>80000,K3*0.45-13505),2)(8)管理部門應(yīng)付工資合計(jì)=SUMPRODUCT(1*(D3:D17=”行政”),I3:I17)管理部門實(shí)發(fā)工資=SUMP

14、RODUCT(1*(D3:D17=”行政”),M3:M17)第8套平均成績(jī)和總分=SUM(D3:L3)=AVERAGE(D3:L3)年級(jí)排名 =RANK(M3,M$3:M$102,0)學(xué)號(hào)的第三位為專業(yè)代碼、第四位代表班級(jí)序號(hào) =”法律”&TEXT(MID(B3,3,2),”DBNum1”)&”班” =LOOKUP(MID(B3,3,2),"01","02","03","04","法律一班","法律二班","法律三班","法律四班

15、")插入圖表到指定單元格按住AIT鍵，帶鼠標(biāo)指針出現(xiàn)斜向上箭頭是開(kāi)始拖動(dòng)第9套插入一列 “插入” 在C3中輸入 =IF(YEAR(銷售訂單!$B3)=2013,MONTH(銷售訂單!$B3),0)Office商務(wù)辦公好幫手 1月（銷售）=SUMIFS(銷售訂單!$H$3:$H$678,銷售訂單!$E$3:$E$678,'2013年圖書(shū)銷售分析'!$A4,銷售訂單!$C$3:$C$678,1)Office商務(wù)辦公好幫手 2月（銷售）=SUMIFS(銷售訂單!$H$3:$H$678,銷售訂單!$E$3:$E$678,'2013年圖書(shū)銷售分析'!$A4,銷售

16、訂單!$C$3:$C$678,2)Office商務(wù)辦公好幫手 3月（銷售）=SUMIFS(銷售訂單!$H$3:$H$678,銷售訂單!$E$3:$E$678,'2013年圖書(shū)銷售分析'!$A4,銷售訂單!$C$3:$C$678,3)每月圖書(shū)總銷量=SUBTOTAL(109,B4:B11) 添加匯總行第10套分列選中B列單元格，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“插入”選項(xiàng)。然后選中A1單元格，將光標(biāo)置于“學(xué)號(hào)”和“名字”之間，按3次空格鍵，然后選中A列單元格，單擊【數(shù)據(jù)工具】組中的“分列”按鈕，在彈出的對(duì)話框中選擇“固定寬度”單選按鈕，單擊“下一步”按鈕，然后建立分列線。單擊

17、“下一步”按鈕，保持默認(rèn)設(shè)置，單擊“完成”按鈕。(3)性別 =IF(MOD(MID(C2,17,1),2)=1,”男”,”女”)出生日期 =-TEXT(MID(C2,7,8),”0-00-00”) =CONCATENATE(MID(C8,7,4),"年",MID(C8,11,2),"月",MID(C8,13,2),"日") 年齡 =DATEDIF(-TEXT(MID(C2,7,8),”0-00-00”),TODAY(),”y”)=INT(TODAY()-E2)/365)(4)語(yǔ)文姓名 =VLOOKUP(A2,初三學(xué)生檔案!$A$1:$

18、B$56,2,FALSE)，其余方法一樣 按照三個(gè)比例計(jì)算學(xué)期成績(jī) =SUM(C2*30%)+(D2*30%)+(E2*40%) 按成績(jī)由高到低的順序排名，并按“第n名”的形式填入“班級(jí)名次”列中 ="第"&RANK(F2,$F$2:$F$45)&"名" “期末總評(píng)” 數(shù)學(xué)，語(yǔ)文：=IF(F2>=102,"優(yōu)秀",IF(F2>=84,"良好",IF(F2>=72,"及格",IF(F2>72,"及格","不及格")英語(yǔ)

19、及其他: =IF(F3>=90,"優(yōu)秀",IF(F3>=75,"良好",IF(F3>=60,"及格","不及格") 姓名 =VLOOKUP(A3,初三學(xué)生檔案!$A$2:$B$56,2,FALSE) 語(yǔ)文成績(jī)=VLOOKUP(A3,語(yǔ)文!A2:F45,6,FALSE)第11套(3)職稱 =VLOOKUP(E12,教師基本信息!$D$3:$E$22,2,FALSE) 學(xué)時(shí)數(shù) =SUMIF(授課信息表!$D$3:$D$72,E3,授課信息表!$F$3:$F$72)第12套(3) =IF(H2=0,”平

20、”,IF(H2>0,”借”,”貸”)第13套(3)銷量 =VLOOKUP(A4,銷量信息,3,FALSE)第14套條件格式選中F2：G14，單擊【開(kāi)始】選項(xiàng)卡下【樣式】組中的"條件格式"按鈕，選擇"突出顯示單元格規(guī)則"中的"其他規(guī)則"，彈出"新建格式規(guī)則"對(duì)話框，在"編輯規(guī)則說(shuō)明"選項(xiàng)下設(shè)置單元格值小于80。然后單擊"格式"按鈕，彈出"設(shè)置單元格格式"對(duì)話框，在"填充"選項(xiàng)卡下選擇"紅色"，單擊"確定

21、"按鈕。分類匯總選中C15，單擊【數(shù)據(jù)】選項(xiàng)卡下【分級(jí)顯示】組中的"分類匯總"按鈕，彈出"分類匯總"對(duì)話框，單擊"分類字段"組中的下拉按鈕選擇"班級(jí)"，單擊"匯總方式"組中的下拉按鈕選擇"平均值"，在"選定匯總項(xiàng)"組中勾選"高等代數(shù)"、"數(shù)學(xué)分析"、"大學(xué)英語(yǔ)"、"大學(xué)物理"、"VB程序設(shè)計(jì)"、"C語(yǔ)言程序設(shè)計(jì)"、"M

22、atlab"復(fù)選框，并勾選"每組數(shù)據(jù)分頁(yè)"復(fù)選框。其他分類匯總方法一致第15套(4)班級(jí) =IF(MID(A3,4,2)=”01”,”1班”,IF(MID(A3,4,2)=”02”,”2班”,”3班”)(5)姓名 =VLOOKUP(A3,學(xué)號(hào)對(duì)照!$A$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,”本

23、科”,員工檔案!K3:K37)(5)所有人的基礎(chǔ)工資 =SUM(員工檔案!M3:M37)(6)項(xiàng)目經(jīng)理的基本工資總額 =員工檔案!K3+員工檔案!K7(7)本科生平均基本工資 =AVERAGEIF(員工檔案!H3:H37,”本科”,員工檔案!K3:K37)第17套根據(jù)生成的數(shù)據(jù)透視表，在透視表下方創(chuàng)建一個(gè)簇狀柱形圖，圖表中僅對(duì)博達(dá)書(shū)店一月份的銷售額小計(jì)進(jìn)行比較。選中博達(dá)書(shū)店銷售額小計(jì)，單擊【開(kāi)始】選項(xiàng)卡下【圖表】組中的"柱形圖"按鈕，在彈出的下拉列表中選擇"簇狀柱形圖"命令。步驟2：在"數(shù)據(jù)透視圖"中單擊"書(shū)店名稱"

24、;右側(cè)下三角按鈕，在下拉列表中只選擇"博達(dá)書(shū)店"復(fù)選框。第18套條件格式選擇【B3:L14】單元格，切換至【開(kāi)始】選項(xiàng)卡，單擊【樣式】選項(xiàng)組下的【條件格式】下拉按鈕，在下拉列表中選擇【突出顯示單元格規(guī)則】-【大于】，在“為大于以下值的單元格設(shè)置格式”文本框中輸入“1000”，使用默認(rèn)設(shè)置“淺紅填充色深紅色文本”，單擊“確定”按鈕。步驟2：選擇【M3:M14】單元格，切換至【開(kāi)始】選項(xiàng)卡，單擊【樣式】選項(xiàng)組下的【條件格式】下拉按鈕，在彈出的下拉列表中選擇【突出顯示單元格規(guī)則】-【大于】，在“為大于以下值的單元格設(shè)置格式”文本框中輸入“=$M$15*110%”，設(shè)置顏色為“黃填充色深黃色文本”，單擊“確定”按鈕。季度 =”第”&INT(1+(MONTH(A3)-1)/3)&”季度”第19套停放時(shí)間 =DATEDIF(F2,H2,”YD”)*24+(I2-G2)收費(fèi)金額 =E2