計算機(jī)二級考試MS-OFFICE表格題-步驟詳解

計算機(jī)二級考試MS-OFFICE表格題-步驟詳解第1套小李今年畢業(yè)，在一家計算機(jī)圖書銷售公司擔(dān)任市場部助理，主要的工作職責(zé)是為部門經(jīng)理提供銷售信息的分析和匯總。（2）圖書名稱=VLOOKUP([@圖書編號],表2[#全部],2,FALSE))（2的含義：返回數(shù)據(jù)在查找區(qū)域的第2列數(shù)）（3）單價=VLOOKUP([@圖書編號],表2[#全部],3,FALSE)（3的含義：返回數(shù)據(jù)在查找區(qū)域的第3列）（4）小計=[@單價]*[@銷量（本）]（5）所有訂單的總銷售額=SUM(表3[小計])（6）《MSOffice高級應(yīng)用》圖書在2012年的總銷售額=SUMIFS(表3[小計],表3[圖書名稱],"《MSOffice高級應(yīng)用》",表3[日期],">=2012-1-1",表3[日期],"<=2012-12-31")（7）隆華書店在2011年第3季度（7月1日~9月30日）的總銷售額=SUMIFS(表3[小計],表3[書店名稱],"隆華書的準(zhǔn)確性（5）應(yīng)交個人所得稅（提示：應(yīng)交個人所得稅=應(yīng)納稅所得額*對應(yīng)稅率-對應(yīng)速算扣除數(shù)）=IF(K3<=1500,K3*3/100,IF(K3<=4500,K3*10/100-105,IF(K3<=9000,K3*20/100-555,IF(K3<=35000,K3*25%-1005,IF(K3<=55000,K3*30%-2755,IF(K3<=80000,K3*35%-5505,IF(K3>80000,K3*45%-13505)))))))（請不要吐在屏幕或者鍵盤上）第8套小李是北京某政法學(xué)院教務(wù)處的工作人員，法律系提交了2012級四個法律專業(yè)教學(xué)班的期末成績單。（3）年級排名=RANK([@總分],[總分],0)（4）班級=IF(MID(B3,3,2)="01","法律一班",IF(MID(B3,3,2)="02","法律二班",IF(MID(B3,3,2)="03","法律三班","法律四班")))（又是一個IF嵌套，下面圖片是依次從上往下的步驟）第9套（SUMIFS大集合）銷售部助理小王需要根據(jù)2012年和2013年的圖書產(chǎn)品銷售情況進(jìn)行統(tǒng)計分析，以便制訂新一年的銷售計劃和工作任務(wù)。（1）圖書編號=VLOOKUP([@圖書名稱],表3,2,0)（3）在“2013年圖書銷售分析”工作表中，統(tǒng)計2013年各類圖書在每月的銷售量，并將統(tǒng)計結(jié)果填充在所對應(yīng)的單元格中。為該表添加匯總行，在匯總行單元格中分別計算每月圖書的總銷量。1月《Office商務(wù)辦公好幫手》銷售分析=SUMIFS(表1[銷量（本）],表1[圖書名稱],A4,表1[日期],">=2013-1-1",表1[日期],"<2013-2-1")其余月份設(shè)置方式：選中單元格，在編輯欄中將公式復(fù)制，然后雙擊2月，光標(biāo)在單元格里面時再粘貼，然后修改日期就好了。第10套期末考試結(jié)束了，初三（14）班的班主任助理王老師需要對本班學(xué)生的各科考試成績進(jìn)行統(tǒng)計分析，并為每個學(xué)生制作一份成績通知單下發(fā)給家長。（3）性別：（又是一個嵌套）=IF(MOD(MID(C2,17,1),2)=1,"男","女")出生日期：（兩種方法）=MID(C2,7,4)&"年"&MID(C2,11,2)&"月"&MID(C2,13,2)&"日"這個是用三次MID函數(shù)，用&連接也可以用：=TEXT(MID(C2,7,8),"0000年00月00日")年齡：=INT((TODAY()-E2)/365)（4）語文姓名=VLOOKUP(A2,檔案[[學(xué)號]:[姓名]],2,0)學(xué)期成績：=C2*30%)+(D2*30%)+(E2*40%)班級名次="第"&RANK(F2,$F$2:$F$45)&"名期末總評=IF(F2>=102,"優(yōu)秀",IF(F2>=84,"良好",IF(F2>=72,"及格",IF(F2>72,"及格","不及格"))))第11套小李是某政法學(xué)院教務(wù)處的工作人員，為更好地掌握各個教學(xué)班級學(xué)習(xí)的整體情況，教務(wù)處領(lǐng)導(dǎo)要求她制作成績分析表。（2）"班內(nèi)排名"=RANK([@總分],$L$3:$L$27,0)第12套某公司銷售部門主管大華擬對本公司產(chǎn)品前兩季度的銷售情況進(jìn)行統(tǒng)計（2）產(chǎn)品單價、銷售額=VLOOKUP(B2,產(chǎn)品基本信息表!$B$1:$C$21,2,0)銷售額=D2*E2（3）一季度銷量、銷售額，二季度銷量、銷售額一季度銷量=SUMIF(一季度銷售情況表!$B$2:$B$44,[@產(chǎn)品型號],一季度銷售情況表!$D$2:$D$44)一季度銷售額：=SUMIF(一季度銷售情況表!$B$2:$B$44,[@產(chǎn)品型號],一季度銷售情況表!$F$2:$F$44)二季度銷量=SUMIF('二季度銷售情況表'!$B$2:$B$43,[@產(chǎn)品型號],'二季度銷售情況表'!$D$2:$D$43)二季度銷售額=SUMIF('二季度銷售情況表'!$B$2:$B$43,[@產(chǎn)品型號],'二季度銷售情況表'!$F$2:$F$43)一二季度銷售總量=C2+E2一二季度銷售總額=D2+F2總銷售額排名=RANK(H2,$H$2:$H$21)第13套（最沒人性的一道題，都可以用數(shù)據(jù)透視表解決非要用公式）濱海市對重點(diǎn)中學(xué)組織了一次物理統(tǒng)考，并生成了所有考生和每一個題目的得分（2）完成“按班級匯總”考試學(xué)生數(shù)=COUNTIFS(成績單!$A$2:$A$950,按班級匯總!$A2,成績單!$B$2:$B$950,按班級匯總!$B2)最高分、最低分、平均分個人建議用數(shù)據(jù)透視表做完了粘貼過去，因為函數(shù)你根本看不懂?？陀^題平均分=SUM(小分統(tǒng)計!$C2:$AP2)主觀題平均分=SUM(小分統(tǒng)計!$AQ2:$AZ2)（2）按學(xué)校匯總考試學(xué)生數(shù)=COUNTIF(成績單!$A$2:$A$950,按學(xué)校匯總!$A2)最高分、最低分、平均分還是用數(shù)據(jù)透視表做完了粘貼過去客觀題平均分，主觀題平均分：太惡心，不整了第14套小趙是一名參加工作不久的大學(xué)生。他習(xí)慣使用Excel表格來記錄每月的個人開支情況。（6）插入新的列，整個“季度”（很爛的做法）=INT(1+(MONTH(A14)-1)/3)&"季度"另外一種做法：=ROUNDUP(MONTH(A3)/3,0)&"季度"（Roundup是用月份除以3，然后向上舍入數(shù)字）第15套為讓利消費(fèi)者，提供更優(yōu)惠的服務(wù)，某大型收費(fèi)停車場規(guī)劃調(diào)整收費(fèi)標(biāo)準(zhǔn)，（2）收費(fèi)標(biāo)準(zhǔn)=VLOOKUP(C2,standard,2,FALSE)（3）停放時間=(H2-F2)*24+(I2-G2)收費(fèi)金額=ROUNDUP((HOUR(J2)*60+MINUTE(J2))/15,0)*E2擬收費(fèi)金額=INT((HOUR(J2)*60+MINUTE(J2))/15)*E2第16套銷售部助理小王需要根據(jù)2012年和2013年的圖書產(chǎn)品銷售情況進(jìn)行統(tǒng)計分析，以便制訂新的銷售計劃和工作任務(wù)。（3）“圖書定價”=VLOOKUP([@圖書名稱],表2,2,0)（4）“銷量額小計”=IF([@銷量（本）]>=40,[@單價]*[@銷量（本）]*0.93,[@單價]*[@銷量（本）])（5）所在區(qū)域=VLOOKUP(MID([@發(fā)貨地址],1,3),表3,2,0)（7）統(tǒng)計報告2013年所有圖書訂單的銷售額=SUMIFS(表1[銷售額小計],表1[日期],">=2013-1-1",表1[日期],"<=2013-12-31")《MSOffice高級應(yīng)用》圖書在2012年的總銷售額=SUMIFS(表1[銷售額小計],表1[圖書名稱],訂單明細(xì)!D7,表1[日期],">=2012-1-1",表1[日期],"<=2012-12-31")隆華書店在2013年第3季度（7月1日~9月30日）的總銷售額=SUMIFS(表1[銷售額小計],表1[書店名稱],訂單明細(xì)!C14,表1[日期],">=2013-7-1",表1[日期],"<=2013-9-30")隆華書店在2012年的每月平均銷售額（保留2位小數(shù)）=SUMIFS(表1[銷售額小計],表1[書店名稱],訂單明細(xì)!C14,表1[日期],">=2012-1-1",表1[日期],"<=2012-12-31")/122013年隆華書店銷售額占公司全年銷售總額的百分比（保留2位小數(shù)）=SUMIFS(表1[銷售額小計],表1[書店名稱],訂單明細(xì)!C14,表1[日期],">=2013-1-1",表1[日期],"<=2013-12-31")/SUMIFS(表1[銷售額小計],表1[日期],">=2013-1-1",表1[日期],"<=2013-12-31")第17套小林是北京某師范大學(xué)財務(wù)處的會計，計算系計算機(jī)基礎(chǔ)教研室提交了該教研室2012年的課程授課情況。（3）課時費(fèi)職稱=VLOOKUP([@姓名],教師基本信息!$D$3:$E$22,2,0)課時標(biāo)準(zhǔn)=VLOOKUP([@職稱],課時費(fèi)標(biāo)準(zhǔn)!$A$3:$B$6,2,0)學(xué)時數(shù)=SUMIF(授課信息表!$D$3:$D$72,E3,授課信息表!$F$3:$F$72)課時費(fèi)=[@學(xué)時數(shù)]*[@課時標(biāo)準(zhǔn)]第18套小李是公司的出納，單位沒有購買財務(wù)軟件，因此她只能用手工記賬。（3）方向列=IF(H2=0,"平",IF(H2>0,"借","貸"))第19套小趙是某書店的銷售人員，負(fù)責(zé)計算機(jī)類圖書的銷售情況，并按月份上報分析結(jié)果。銷量=VLOOKUP(A4,銷量信息,3,0)銷售額=C4*D4第20套小偉在自己在讀的學(xué)院里面勤工助學(xué)，兼職當(dāng)副院長的助理一職，平時主要負(fù)責(zé)對各種文案或者數(shù)據(jù)的整理。最簡單的sum和average一個容易出錯地方：分類匯總，“并將每組結(jié)果分頁顯示”第21套小劉是一所初中的學(xué)生處負(fù)責(zé)人，負(fù)責(zé)本院學(xué)生的成績管理。（4）根據(jù)學(xué)號提取班級=MID([@學(xué)號],5,1)&"班"（5）根據(jù)學(xué)號提取姓名=VLOOKUP([@學(xué)號],學(xué)號對照!$A$3:$B$20,2,0)第22套小李在東方公司擔(dān)任行政助理，年底小李統(tǒng)計了公司員工檔案信息的分析和匯總（2）根據(jù)身份證號碼提取生日=MID(F3,7,4)&"年"&MID(F3,11,2)&"月"&MID(F3,13,2)&"日"（3）根據(jù)入職時間，計算工齡=INT((TODAY()-I3)/365)（4）計算工齡工資，基礎(chǔ)工資=J3*50=K3+L3（5）所有人基礎(chǔ)工資總額=SUM(員工檔案!K3:K37)（6）項目經(jīng)理基礎(chǔ)工資總額=員工檔案!K6+員工檔案!K7（7）本科生基本工資=AVERAGEIF(員工檔案!H3:H37,"本科",員工檔案!K3:K37)第23套小王今年畢業(yè)后，在一家計算機(jī)圖書銷售公司擔(dān)任市場部助理（3）使用Vlookup函數(shù)完成填充=VLOOKUP(D3,表2[#全部],3,0)第24套銷售部助理小王對上半年產(chǎn)品銷售統(tǒng)計分析（2）前6個月銷售總額=SUM(D3:I3)（3）銷售排名="第"&RANK.EQ([@個人銷售總計],[個人銷售總計])&"名"="第"&RANK([@個人銷售總計],[個人銷售總計])&"名"（這道題用Rank函數(shù)和Rank.EQ沒有區(qū)別。）（4）達(dá)標(biāo)率=COUNTIF(表1[一月份],">60000")/COUNT(表1[一月份])（5）每個月第一名銷售業(yè)績=LARGE(表1[一月份],1)第25套李東陽是某家電器企業(yè)的戰(zhàn)略規(guī)劃人員，正在參與制定本年度的生產(chǎn)與營銷計劃。（5）在“銷售記錄”工作表中，價格=VLOOKUP(C4,價格表!$B$2:$C$5,2,0)（6）金額=D4*E4*(1-VLOOKUP(C4,折扣表!$B$9:$F$11,IF(D4<1000,2,IF(D4<1500,3,IF(D4<2000,4,5)))))（7）條件格式的公式=OR(WEEKDAY($B4,2)=6,WEEKDAY($B4,2)=7)第26套每年年終，太平洋公司都會給在職員工發(fā)放年終獎金，公司會計小任負(fù)責(zé)計算工資獎金的個人所得稅。（4）“員工基礎(chǔ)檔案”男女=IF(MOD(MID(E2,17,1),2)=1,"男","女")出生日期=MID(E2,7,4)&"年"&MID(E2,11,2)&"月"&MID(E2,13,2)&"日"年齡=INT(DAYS360([@出生日期],DATE(2015,9,30))/360)月工齡工資=IF(K2<1,0,IF(K2<10,K2*20,IF(K2<30,K2*30,K2*50)))基本月工資=L2+M2（5）“年終獎金”姓名=VLOOKUP(A4,檔案,2,0)部門=VLOOKUP(A4,檔案,3,0)月基本工資=VLOOKUP(A4,檔案,14,0)應(yīng)發(fā)獎金=D4*12*0.15月應(yīng)稅所得額=E4/12應(yīng)交個稅=IF(F4<=1500,E4*0.03,IF(F4<=4500,E4*0.1-105,IF(F4<=9000,E4*0.2-555,IF(F4<=35000,E4*0.25-1005,IF(F4<=55000,E4*0.3-2755,IF(F4<=80000,E4*0.35-5505,E4*0.45-13505))))))實發(fā)獎金=E4-G4（8）“工資條”放棄比較好，做別的吧第27套正則明事務(wù)所的統(tǒng)計員小任需要對本所外匯報告的完成情況進(jìn)行統(tǒng)計分析（5）在數(shù)據(jù)區(qū)域的最左側(cè)增加“完成情況”列，判斷其完成情況=IF(AND(F3="是",G3="是",H3="是"),"完成","未完成")（6）在“完成情況”列的右側(cè)增加“報告獎金”=IF(I3="完成",IF(D3<=1000,100,IF(D3<=2800,D3*0.08,D3*0.1))+30,IF(D3<=1000,100,IF(D3<=2800,D3*0.08,D3*0.1)))9）在工作表“員工個人情況統(tǒng)計”中對每個員工完成情況統(tǒng)計撰寫報告數(shù)=COUNTIF(表1[責(zé)任人],[@姓名])修改過0次的報告數(shù)=COUNTIFS(表1[責(zé)任人],[@姓名],表1[報告修改次數(shù)],0)修改過1次的報告數(shù)=COUNTIFS(表1[責(zé)任人],[@姓名],表1[報告修改次數(shù)],1)第28套銷售經(jīng)理小李通過Excel制作了銷售情況統(tǒng)計表，根據(jù)下列要求幫助小李對數(shù)據(jù)進(jìn)行整理和分析（6）商品單價-vlookup函數(shù)=VLOOKUP(E3,商品單價!$A$3:$B$7,2,0)（7）成交金額-簡單公式=[@成交數(shù)量]*[@商品單價]（9）計算每位銷售經(jīng)理每月的成交金額-sumifs函數(shù)張樂1月=SUMIFS(表1[成交金額],表1[銷售經(jīng)理],A3,表1[咨詢?nèi)掌赸,">=2014-1-1",表1[咨詢?nèi)掌赸,"<=2014-1-31")張樂2月=SUMIFS(表1[成交金額],表1[銷售經(jīng)理],A3,表1[咨詢?nèi)掌赸,">=2014-2-1",表1[咨詢?nèi)掌赸,"<=2014-2-28")張樂3月=SUMIFS(表1[成交金額],表1[銷售經(jīng)理],A3,表1[咨詢?nèi)掌赸,">=2014-3-1",表1[咨詢?nèi)掌赸,"<=2014-3-31")總和=SUM(B3:D3)李耀1月=SUMIFS(表1[成交金額]