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

Office2016

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

計算水電費開支

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

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

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

計算與分析成績表

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

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

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

數(shù)組公式是指可以同時對一組或兩組以上的數(shù)據(jù)進(jìn)行計算的公式，計算的結(jié)果可能是一個，也可能是多個。例如，本案例中學(xué)生的總分也可利用數(shù)組公式計算，具體操作為：選擇要放置計算結(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è)置IF函數(shù)。判斷學(xué)生的等級。提示

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

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

RANK.EQ函數(shù)的功能是返回一個數(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ù)據(jù)制作成績統(tǒng)計與分析表格。計算“財政法規(guī)與職業(yè)道德”最高分。計算“財政法規(guī)與職業(yè)道德”最低分。計算所有課程的最高分和最低分。選擇“COUNTIF”函數(shù)。設(shè)置函數(shù)參數(shù)。提示

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

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

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

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

任務(wù)三

制作工資表

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

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

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

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

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

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

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

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

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

可將一些中重要的計算公式隱藏

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

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