office-2016高效辦公案例教程(項(xiàng)目六)

Office2016

第二部分Excel高效辦公篇項(xiàng)目六高效計(jì)算表格數(shù)據(jù)CONTENTS目錄任務(wù)一計(jì)算水電費(fèi)開支任務(wù)二計(jì)算與分析成績表任務(wù)三制作工資表123CONTENTS目錄任務(wù)四合并計(jì)算生產(chǎn)和銷售情況表Excel高效辦公技巧45學(xué)習(xí)利用成組工作表功能同時(shí)輸入并編輯多個(gè)工作表中的數(shù)據(jù)的方法。學(xué)習(xí)使用公式和函數(shù)計(jì)算數(shù)據(jù)的方法。學(xué)習(xí)對數(shù)值數(shù)據(jù)設(shè)置數(shù)字格式的方法。學(xué)習(xí)對多個(gè)工作表進(jìn)行合并計(jì)算的方法。學(xué)習(xí)為單元格添加批注的方法。學(xué)習(xí)要點(diǎn)任務(wù)一

計(jì)算水電費(fèi)開支

陳大叔的租戶有10個(gè)人，但已經(jīng)連續(xù)兩個(gè)月沒交水電費(fèi)了。為此，他需要統(tǒng)計(jì)一下所有租戶這兩個(gè)月的水費(fèi)和電費(fèi)，準(zhǔn)備上門收繳。利用Excel制作，借助其中的公式和函數(shù)，不但計(jì)算速度快，而且準(zhǔn)確率也高。

假設(shè)你是陳大叔的好朋友，請你根據(jù)上述情況，利用Excel2016幫他制作一份水電費(fèi)開支表格。情景引入wa作品展示水電費(fèi)開支文件效果

由圖可知，該水電費(fèi)開支工作簿由3個(gè)工作表組成。因此，需再新建兩個(gè)工作表，并將其分別命名為“8月”“9月”和“合計(jì)”，然后將“8月”和“9月”工作表成組，再在其中的一個(gè)工作表中輸入基礎(chǔ)數(shù)據(jù)并格式化，取消成組后分別修改其個(gè)一個(gè)工作表中的數(shù)據(jù)，并在“合計(jì)”工作表中制作表格，最后在各工作表中輸入公式計(jì)算工作表數(shù)據(jù)。制作思路任務(wù)實(shí)施一、輸入工作表基本數(shù)據(jù)新建“水電費(fèi)開支”工作簿，“8月”、“9月”和“合計(jì)”工作表。在成組工作表中輸入數(shù)據(jù)并設(shè)置格式。將“9月”工作表中的數(shù)據(jù)進(jìn)行修改。在“合計(jì)”工作表中制作表格。在成組工作表中輸入數(shù)據(jù)并設(shè)置格式修改后“9月”工作表數(shù)據(jù)任務(wù)實(shí)施二、使用公式計(jì)算工作表數(shù)據(jù)計(jì)算出8月份所有租戶的用電量。計(jì)算第一個(gè)租戶的電費(fèi)。計(jì)算其他租戶的電費(fèi)。計(jì)算出8月租戶的用水量。計(jì)算出電費(fèi)合計(jì)和水費(fèi)合計(jì)。計(jì)算8月電費(fèi)表底和水費(fèi)表底數(shù)據(jù)；9月份同理計(jì)算。計(jì)算第一個(gè)租戶的電費(fèi)任務(wù)實(shí)施二、使用公式計(jì)算工作表數(shù)據(jù)計(jì)算“9月”工作表水電費(fèi)任務(wù)實(shí)施三、計(jì)算水電費(fèi)合計(jì)值計(jì)算8-9月電費(fèi)合計(jì)值。計(jì)算8-9月水費(fèi)合計(jì)值。計(jì)算8-9月電費(fèi)合計(jì)值計(jì)算8-9月水費(fèi)合計(jì)值任務(wù)二

計(jì)算與分析成績表

期末考試成績出來了，老師要求各班學(xué)習(xí)委員對本班成績進(jìn)行計(jì)算和分析，要求計(jì)算出學(xué)生的平均分和總分，并按平均分判斷其成績等級，按總分列出每位同學(xué)的名次，再對各科成績的最高分、最低分，以及各分?jǐn)?shù)段的人數(shù)進(jìn)行統(tǒng)計(jì)。

假設(shè)你是2班的學(xué)習(xí)委員，請你根據(jù)上述要求，對本班的考試成績進(jìn)行計(jì)算與分析。情景引入作品展示勞務(wù)外包合同文檔效果wa作品展示計(jì)算和分析后的文件效果

首先打開素材文件，重命名工作表后依次計(jì)算學(xué)生的平均分、總分、等級和名次，接下來對各科分?jǐn)?shù)段所在人數(shù)進(jìn)行統(tǒng)計(jì)，最后為單元格添加批注。制作思路任務(wù)實(shí)施一、計(jì)算平均分和總分新建并保存“成績表”，重命名“Sheet1”工作表為“2班”。計(jì)算學(xué)生的平均分。設(shè)置小數(shù)位數(shù)。計(jì)算學(xué)生的總分。利用數(shù)組公式求總分提示

數(shù)組公式是指可以同時(shí)對一組或兩組以上的數(shù)據(jù)進(jìn)行計(jì)算的公式，計(jì)算的結(jié)果可能是一個(gè)，也可能是多個(gè)。例如，本案例中學(xué)生的總分也可利用數(shù)組公式計(jì)算，具體操作為：選擇要放置計(jì)算結(jié)果的單元格或單元格區(qū)域M2:M30，然后輸入數(shù)組公式“=(C2:C30+D2:D30+E2:E30+F2:F30+G2:G30+H2:H30+I2:I30+J2:J30+K2:K30)”并按【Ctrl+Shift+Enter】組合鍵，效果如圖6-17所示。

在數(shù)組公式中使用的數(shù)據(jù)稱為數(shù)組參數(shù)，公式中的數(shù)組參數(shù)必須為單元格區(qū)域，如A1:D5。

任務(wù)實(shí)施二、判斷成績等級設(shè)置函數(shù)的判斷條件及返回值。設(shè)置IF函數(shù)。判斷學(xué)生的等級。提示

使用IF函數(shù)可對數(shù)值和公式進(jìn)行真假值條件判斷，并根據(jù)邏輯判斷情況返回不同結(jié)果，其語法格式為：IF(logical_test,value_if_true,value_if_false)其中：logical_test為判斷條件，可用比較運(yùn)算符（=，>,<,>=,<=,<>）表示。value_if_true”表示當(dāng)“l(fā)ogical_test”為“true”時(shí)返回的值。value_if_false表示當(dāng)“l(fā)ogical_test”為“false”時(shí)返回的值，返回值可以是常量或表達(dá)式。Excel2007以上版本中，IF函數(shù)最多可嵌套64層。

任務(wù)實(shí)施三、計(jì)算總分排名按總分對學(xué)生成績進(jìn)行排名。選擇“RANK.EQ”函數(shù)。設(shè)置函數(shù)參數(shù)。復(fù)制公式到其他單元格。選擇“RANK.EQ”函數(shù)提示

RANK.EQ函數(shù)的功能是返回一個(gè)數(shù)字在數(shù)字列表中的排位，其語法格式：RANK.EQ(number,ref,order)其中：number為需要排序的數(shù)值。ref為要排序的單元格區(qū)域。order指明排序方式。如果為“0”或者缺省，則按降序排名，即數(shù)值越大，排名結(jié)果數(shù)值越??；否則按升序排名，即數(shù)值越大，排名結(jié)果數(shù)值越大。RANK.EQ賦予重復(fù)數(shù)相同的排位，但重復(fù)數(shù)的存在將影響后續(xù)數(shù)值的排位。

任務(wù)實(shí)施四、分析成績數(shù)據(jù)制作成績統(tǒng)計(jì)與分析表格。計(jì)算“財(cái)政法規(guī)與職業(yè)道德”最高分。計(jì)算“財(cái)政法規(guī)與職業(yè)道德”最低分。計(jì)算所有課程的最高分和最低分。選擇“COUNTIF”函數(shù)。設(shè)置函數(shù)參數(shù)。提示

COUNTIF函數(shù)的功能是用來統(tǒng)計(jì)單元格區(qū)域中滿足給定條件的單元格的個(gè)數(shù)，其語法格式：COUNTIF(range,criteria)其中：Range為需要統(tǒng)計(jì)的單元格區(qū)域，即（條件區(qū)域）。Criteria為判斷條件，其形式可以為數(shù)字、表達(dá)式或文本，即（條件）。

統(tǒng)計(jì)所有分?jǐn)?shù)段的人數(shù)任務(wù)實(shí)施五、為單元格添加批注為單元格添加批注。確定單元格后單擊“新建批注”按鈕。輯批注，可選中添加批注的單元格。確定單元格后單擊“新建批注”按鈕為單元格添加批注提示

默認(rèn)情況下，將鼠標(biāo)指針指向帶有批注的單元格時(shí)

批注會(huì)顯示出來。此外，單擊“審閱”選項(xiàng)卡“批注”組中的“顯示所有批注”按鈕，可顯示工作表中的所有批注。再次單擊該按鈕，則隱藏所有批注。

任務(wù)三

制作工資表

某學(xué)院為了確保8月份教師工資的準(zhǔn)時(shí)發(fā)放，需要人事部將該月的工資數(shù)據(jù)進(jìn)行核算，以工資條的形式發(fā)放到教師手上，方便教師查看各項(xiàng)工資數(shù)據(jù)。

假設(shè)你是人事部的一員根據(jù)8月份教師的已知工資數(shù)據(jù)，繪制工資表和工資條文件。情景引入wa作品展示工資表效果wa作品展示工資條文件效果

打開素材工作表后先利用條件格式標(biāo)識(shí)“加班天數(shù)”列和“請假天數(shù)”列數(shù)據(jù)，接下來依次計(jì)算教師的職稱津貼、課時(shí)費(fèi)、應(yīng)發(fā)工資、稅前總額、扣稅和實(shí)發(fā)工資，然后設(shè)置各項(xiàng)工資數(shù)據(jù)的數(shù)字格式，最后制作工資條。制作思路任務(wù)實(shí)施一、利用條件格式標(biāo)識(shí)數(shù)據(jù)新建并保存”工資表”。選擇“大于”項(xiàng)。選擇“大于”項(xiàng)設(shè)置條件格式設(shè)置條件格式。將設(shè)置大于0的數(shù)據(jù)所在單元格的顯示為“淺紅填充色深紅色文本”。任務(wù)實(shí)施二、計(jì)算職稱津貼和課時(shí)費(fèi)計(jì)算職稱津貼。計(jì)算出其他教師的職稱津貼。計(jì)算課時(shí)費(fèi)。計(jì)算出其他教師的課時(shí)費(fèi)。計(jì)算第1位教師的課時(shí)費(fèi)任務(wù)實(shí)施三、計(jì)算應(yīng)發(fā)工資和稅前總額計(jì)算應(yīng)發(fā)工資。計(jì)算稅前總額。任務(wù)實(shí)施四、計(jì)算扣稅和實(shí)發(fā)工資計(jì)算扣稅。計(jì)算實(shí)發(fā)工資。個(gè)人所得稅計(jì)算7級標(biāo)準(zhǔn)任務(wù)實(shí)施五、設(shè)置各項(xiàng)工資數(shù)據(jù)的數(shù)字格式將各項(xiàng)工資數(shù)據(jù)的格式設(shè)置為貨幣。選擇“貨幣”項(xiàng)知識(shí)庫貨幣格式和會(huì)計(jì)專用格式的區(qū)別在于幣種符號的位置不同。貨幣的幣種符號是與數(shù)字連在一起靠右的，而會(huì)計(jì)專用的幣種符號是靠左，數(shù)字靠右的，可使一列數(shù)值以小數(shù)點(diǎn)對齊。任務(wù)實(shí)施六、計(jì)算應(yīng)發(fā)工資和稅前總額新建“工資條”工作表，并輸入公式。復(fù)制公式計(jì)算數(shù)據(jù)，設(shè)置單元格樣式。提示

在輸入圖6-45所示的公式時(shí)，可借助“公式”選項(xiàng)卡“函數(shù)庫”組“邏輯”下拉列表中的“IF”函數(shù)輸入。在輸入公式中的A$2和$A:$R時(shí)，可先輸入A2和A:R，然后按【F4】鍵即可。圖6-45所示的公式中，各函數(shù)的作用如下：①ROW函數(shù)：表示返回一個(gè)引用的行號，其語法格式為：ROW(reference)其中：reference為需要得到其行號的單元格或單元格區(qū)域。如果省略reference，則表示對函數(shù)ROW所在單元格的引用；如果reference為一個(gè)單元格區(qū)域，并且函數(shù)ROW作為垂直數(shù)組輸入，則函數(shù)ROW將reference的行號以垂直數(shù)組的形式返回。reference不能引用多個(gè)區(qū)域。提示

②MOD函數(shù)：是一個(gè)求余函數(shù)，即兩個(gè)數(shù)值表達(dá)式作除法運(yùn)算后的余數(shù)，其語法格式為：MOD(number,divisor)其中：number為被除數(shù)；divisor為除數(shù)。如果divisor為零，函數(shù)MOD返回值為number。③COLUMN函數(shù)：表示返回一引用的列號，其語法格式為：COLUMN(reference)其中：reference為需要得到其列標(biāo)的單元格或單元格區(qū)域。如果省略reference，則表示對函數(shù)COLUMN所在單元格的引用；如果reference為一個(gè)單元格區(qū)域，并且函數(shù)COLUMN作為水平數(shù)組輸入，則函數(shù)COLUMN將reference中的列標(biāo)以水平數(shù)組的形式返回。reference不能引用多個(gè)區(qū)域。提示

④INDEX函數(shù)：表示返回表或區(qū)域中的值，或?qū)χ档囊?，其語法格式為：INDEX(array,row_num,column_num)其中：array為一個(gè)單元格區(qū)域或數(shù)組常量；row_num表示要從中返回值的數(shù)組中的行；column_num表示要從中返回值的數(shù)組中的列。⑤INT函數(shù)：表示將數(shù)值向下或向上取整為最接近的整數(shù)，其語法格式為：INT(number)number為需要進(jìn)行向下或向上舍入取整的實(shí)數(shù)。圖6-45所示公式中，“MOD(ROW(),3)=0,""”表示被3整除的行，該行為空行（對應(yīng)工資條中的第3行）；“MOD(ROW(),3)=1,'8月工資表'!A$2”表示被3除余1時(shí)，該行顯示8月工資表中的A2行（對應(yīng)工資條中的第1行），否則，按“INDEX('8月工資表'!工資條!,INT((ROW()+10)/3),COLUMN())”輸出。任務(wù)四

合并計(jì)算生產(chǎn)和銷售情況表

公司已經(jīng)將3個(gè)分廠的生產(chǎn)和銷售數(shù)據(jù)錄入到一個(gè)工作表中，現(xiàn)在讓小悅利用合并計(jì)算功能快速將3個(gè)分廠的數(shù)據(jù)進(jìn)行合計(jì)和求平均值，并將計(jì)算結(jié)果以表格形式放置在原工作表中，方便公司高層領(lǐng)導(dǎo)隨時(shí)查看。情景引入wa作品展示生產(chǎn)和銷售情況表文件效果

打開素材文件后在源工作表的右側(cè)分別制作一個(gè)求合計(jì)和平均值的工作表格并進(jìn)行格式化，然后利用合并計(jì)算功能進(jìn)行各產(chǎn)品生產(chǎn)量和銷售量的合計(jì)及平均值計(jì)算。制作思路任務(wù)實(shí)施一、計(jì)算合計(jì)數(shù)據(jù)打開現(xiàn)有表格，并新建新表格。單擊“合并計(jì)算”按鈕。添加所有合并數(shù)據(jù)合并計(jì)算結(jié)果選擇數(shù)據(jù)區(qū)域。添加所有合并數(shù)據(jù)。合并計(jì)算結(jié)果。任務(wù)實(shí)施二、計(jì)算平均數(shù)據(jù)在“函數(shù)”下拉列表中選擇“平均值”函數(shù)。將合并計(jì)算的函數(shù)改為“平均值”。合并計(jì)算平均值。將合并計(jì)算的函數(shù)改為“平均值”合并計(jì)算平均值效果Excel高效辦公技巧

可將一些中重要的計(jì)算公式隱藏

為此，可選中要隱藏公式的單元格或單元格區(qū)域，然后打開“設(shè)置單元格格式”對話框，切換到“保護(hù)”選項(xiàng)卡，選中“隱藏”復(fù)選框并確定，如下圖所示，最后對工作表進(jìn)行保護(hù)（設(shè)置保護(hù)選項(xiàng)時(shí)，要選中“保護(hù)工作表及鎖定的單元格內(nèi)容”復(fù)選框。后面的項(xiàng)目中有介紹）。一、防止在編輯欄顯示公式選中“隱藏”復(fù)選框

在要放置結(jié)果的單元格中輸入“=”單擊“開始”選項(xiàng)卡“編輯”組中的“求和”按鈕，此時(shí)出現(xiàn)公式“=SUM()”，單擊要進(jìn)行計(jì)算的單元格，輸入逗號后再單擊要進(jìn)行，求和的單元格，重復(fù)該操作，最后按【Enter