Excel第5套考題解析教學(xué)教材

1、精品文檔步驟 1：打開考生文件夾下的 EXCEL.XLSX 。第5套請在【答題】菜單下選擇【進入考生文件夾】命令，并按照題目要求完成下面的操作。 注意：以下的文件必須保存在考試文件夾下。財務(wù)部助理小王需要向主管匯報 2013 年度公司差旅報銷情況，現(xiàn)在請按照如下需求，在 EXCEL.XISX 文檔中完成工作：1. 在“費用報銷管理 ”工作表 “日期 ”列的所有單元格中，標(biāo)注每個報銷日期屬于星期幾，例如日期為 “2013年1月20日”的單元格應(yīng)顯示為 “ 2013年1月 20日 星期日”，日期為 “2013年1月21日”的單元格應(yīng) 顯示為 “2013年 1 月 21 日 星期一 ”。步驟 2：在

2、費用報銷管理 工作表中，選中 日期 數(shù)據(jù)列，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇設(shè)置單元格格式 命令，彈出 設(shè)置單元格格式 對話框。切換至 數(shù)字選項卡，在 分類列表框中選擇 自定義 命令，在右側(cè)的 示例組中類型列表框中選擇 yyyy 年m月d日，然后在其后面輸入一個空格， 接著輸 入aaaa。設(shè)置完畢后單擊 確定 按鈕即可。精品文檔精品文檔2. 如果“日期”列中的日期為星期六或星期日，則在 “是否加班 ”列的單元格中顯示 “是”，否則顯示 “否 必須使用公式） 。步驟1：選擇H3單元格，然后選擇 【公式】中的【插入函數(shù)】 ，在選擇類別中選擇 “邏輯”，選擇“If函”數(shù)。精品文檔精品文檔步驟 2

3、：在“Logical_test中”輸入： weekday(A3,2)5 ，在“Value_if_true 中”輸入：是，在 “Value_if_false中” 輸入：否，表示在星期六或者星期日情況下顯示是，否則顯示 否，按 Enter鍵確認(rèn)。分析：weekday() 函數(shù)表示求某個日期的星期， weekday(A3,2) 表示求 A3 單元格日期中的星期， 其中 2 表 示星期一至星期日用 1 到 7 的數(shù)字表示。題目要求周六或周天為加班，因此 weekday(A3,2) 的值大于 5 即表示周六或周天。精品文檔精品文檔步驟 3： 然后向下填充公式到最后一個日期即可完成設(shè)置。3. 使用公式統(tǒng)計

4、每個活動地點所在的省份或直轄市，并將其填寫在 “地區(qū) ”列所對應(yīng)的單元格中，例如 “北京市 ”、 “浙江省 “。步驟 1：選擇 D3 單元格，然后選擇【公式】中的【插入函數(shù)】 ，在選擇類別中選擇 “文本 ”，選擇 “Letf 函數(shù)。精品文檔精品文檔步驟 2：在“ test中輸”入：C3 ，在“ Num_chars中”輸入：3，表示從 C3 單元格中截取左邊 3 個字符，按Enter 鍵確認(rèn)。精品文檔精品文檔分析： C 列“活動地點 ”中前面 3個字符為地區(qū)，因此使用 Letf() 函數(shù)取出最左邊 3個字符。步驟 3： 然后向下填充公式到最后一個地區(qū)即可完成設(shè)置。4. 依據(jù) “費用類別編號 “列

5、內(nèi)容，使用 VLOOKUP 函數(shù)，生成 ”費用類別 “列內(nèi)容。對照關(guān)系參考 ”費用 類別 “工作表。步驟 1：選擇 F3 單元格，然后選擇【公式】中的【插入函數(shù)】 ，在選擇類別中選擇 “查找與引用 ”，選擇 “ VLookup 函”數(shù)。精品文檔精品文檔步驟 2： 依次將 VLOOKUP 中的條件填寫進去，如下圖所示。精品文檔精品文檔步驟 3： 然后向下填充公式到最后一個費用類別即可完成設(shè)置。5. 在 “差旅成本分析表報告 “工作表 B3 單元格中，統(tǒng)計 2013 年第二季度發(fā)生在北京市的差旅費用總金 額。步驟 1： 選擇 B3 單元格，然后選擇【公式】中的【插入函數(shù)】 ，在選擇類別中選擇 “數(shù)

6、學(xué)與三角函數(shù) 選擇 “ sumifs函”數(shù)。精品文檔精品文檔步驟 2： 依次將 sumifs 中的條件填寫進去，如下圖所示。注意： sumif() 函數(shù)在第一套電子表格題目已經(jīng)詳細(xì)介紹，忘記的自行復(fù)習(xí)！精品文檔精品文檔精品文檔精品文檔精品文檔精品文檔注意： 2013年 3月1日至 2013年 6月 30日，剛好為 2013年的第二季度。6. 在“差旅成本分析報告 “工作 B4 單元格中，統(tǒng)計 2013年員工錢順卓報銷的火車票費用總額。 步驟 1： 選擇 B4 單元格，然后選擇【公式】中的【插入函數(shù)】 ，在選擇類別中選擇 “數(shù)學(xué)與三角函數(shù) 選擇 “ sumifs函”數(shù)。精品文檔精品文檔步驟 2：

7、 依次將 sumifs 中的條件填寫進去，如下圖所示。精品文檔精品文檔精品文檔精品文檔步驟：在 差旅成本分析報告 工作表的 B4單元格中輸入 =SUMPRODUCT(1*( 費用報銷管理 !B3:B401= 錢順 卓), 費用報銷管理 !G3:G401) ，按Enter 鍵確認(rèn)。7. 在 “差旅成本分析報告 “工作表 B5 單元格中，統(tǒng)計 2013 年差旅費用中，飛機票費用占所有報銷費用 的比例，并保留 2 位小數(shù)。步驟 1： 選擇 B5 單元格，然后選擇【公式】中的【插入函數(shù)】 ，在選擇類別中選擇 “數(shù)學(xué)與三角函數(shù) 選擇 “sumif 函”數(shù)。精品文檔精品文檔步驟 2： 依次將 sumif

8、中的條件填寫進去，如下圖所示。精品文檔精品文檔步驟 3：使用以上的公式除以總的報銷費用， 計算出飛機票占所有報銷費用的比例。 首先選中 B5 單元格， 然后在公式編輯區(qū)域 “=SUMIF(費用報銷管理 !F3:F401, 飛機票 ,費用報銷管理 !G3:G401) ”后面輸入： /sum()， 然后將光標(biāo)移動至小括號里面，然后選擇“費用管理 ”工作表中的 G3： G401單元格，敲入回車。精品文檔精品文檔步驟 4：選中 B5 單元格， 然后右擊， 選擇 “設(shè)置單元格格式 ”，然后選擇小數(shù)位數(shù)為 2，然后單擊確定。精品文檔精品文檔8. 在 “差旅成本分析報告 “工作表 B6 單元格中，統(tǒng)計 2013 年發(fā)生在周末（星期六和星期日）的通訊補 助總金額。步驟 1： 選擇 B6 單元格，然后選擇【公式