財務人員實戰Excel之5---------辦公室管理工作表

財務人員實戰Excel之五---------辦公室管理工作表(未完，接下章，待續)

第1節、辦公用品領用記錄表

第2節、財務報表中車輛使用管理表

第3節、考勤管理表的製作

第5章　Excel 辦公室管理工作表

第1節、辦公用品領用記錄表

　　辦公室用品分為消耗性物品和非消耗性物品，領用需登記在冊。一來可以掌控耗材的使用情況，控制成本，二來對於物品的領用做到心中有數，特別是非消耗性辦公室用品原則不能重複申領，登記可做到有賬可查。

辦公室用品領用表效果圖

步驟01 新建工作表　　啟動Excel2007，新建工作簿，將Sheet1改名為「辦公用品領用記錄表」。選中A1單元格，在編輯欄中輸入「辦公用品領用記錄表」，在A2:I2單元格區域輸入標題，在對齊方式中點擊「居中」按鈕。選中A1:I1單元格區域，點擊上方「合併居中」按鈕。

步驟02 設置格式　　選中A3單元格，點擊滑鼠右鍵，在彈出的快捷菜單中選擇「設置單元格格式」→「數字」→「日期」，在右邊的「類型」列表中選擇「01-3-14」，點擊確定。選中A3單元格，點擊「格式刷」按鈕，當滑鼠變化後按著Shift鍵不放點擊A17，就完成了A列單元格區域的格式複製。

步驟03 錄入數據　　按照當月辦公用品的領用情況，逐一將數據錄入表格。

步驟04 計算價值　　選中F3單元格，在編輯欄中輸入公式：「=PRODUCT(D3:E3)」，按回車鍵確定。

選中F3單元格，將游標放在單元格右下角，當游標變成黑十字形狀時，按住滑鼠左鍵不放，向下拖動滑鼠到F17鬆開，就完成了F4:F17單元格區域的公式複製。

　　知識點：PRODUCT函數PRODUCT函數將所有以參數形式給出的數字相乘，並返回乘積值。　　函數語法PRODUCT（number1,number2,…）　　函數說明①當參數為數字、邏輯值或數字的文字型表達式時可以被計算；當參數為錯誤值或是不能轉換為數字的文字時，將導致錯誤。　　②如果參數為數組或引用，只有其中的數字將被計算。數組或引用中的空白單元格、邏輯值、文本或錯誤值將被忽略。

步驟05 完善表格　　表格中數據已經完成，現在對表格的樣式做進一步美化。設置字體、字型大小、邊框線和填充色，並適當調整列寬，保證表中內容完整顯示。

第2節、財務報表中車輛使用管理表

　　很多辦公用品在使用中會產生費用，而使用又存在公事或私事兩種情況，費用上就會有所區別。該怎麼處理這類賬務呢？這裡以車輛使用為例，做簡單講解，財務人員可在實際應用中舉一反三。

車輛使用管理表效果圖

步驟01 新建工作表　　啟動Excel2007，創建新的工作簿，將Sheet1改名為「車輛使用管理表」。選中A1單元格，在編輯欄輸入「公司車輛使用管理表」。在A2:J2單元格區域輸入標題，並適當調整列寬，保證單元格中內容完整顯示。選中A1:J1單元格區域，點擊「合併居中」按鈕。

步驟02 數據錄入　　將當月用車記錄逐一錄入，在錄入時，按部門順序錄入。選中H3單元格，點擊滑鼠右鍵彈出快捷菜單，選擇「設置單元格格式」→「數字」→「貨幣」→設置「小數位數」的值為「0」→「貨幣符號」選擇人民幣符號，點擊確定完成設置。選中H3單元格，點擊「格式刷」按鈕，當游標變化後按著Shift鍵不放，點擊J12單元格，完成H3:J12單元格區域的格式複製。現在，這個單元格區域的數字前自動生成了一個人民幣符號。

步驟03 報銷費公式的編製　　當車輛使用時為了辦公事，車輛消耗費可以報銷，如果車輛使用為私事，那麼車輛產生的消耗費則不予報銷。本著這個原則，來編製報銷費的公式。選中I3單元格，在編輯欄中輸入公式：「=IF(D3="公事",H3,0)」，按回車鍵確定。

步驟04 報銷費公式的複製　　選中I3單元格，將游標放在單元格的右下角，當游標變成黑十字形狀時，按著滑鼠左鍵不放，向下拖動滑鼠到I12單元格鬆開，就完成了I列單元格區域公式的複製。

步驟05 編製駕駛員補助費　　選中J3單元格，在編輯欄中輸入公式：「=IF((G3-F3)*24>8,INT((G3-F3)*24-8) *30,0)」，按回車鍵確定。選中J3單元格，將游標放在單元格右下角，當游標變成黑十字形狀時，按住滑鼠左鍵不放，向下拖動游標到J12單元格鬆開，完成J列公式的複製。

步驟06 插入部門合計行　　為了方便觀察和統計各部門用車情況，需要按部門進行分類統計。在不同的部門後插入兩個空行，然後在C列按部門的不同，分別輸入「業務部 計數」和「業務部門 匯總」，同時調整列寬保證單元格中內容完整顯示。

步驟07 編製各部門計數、匯總公式

　　選中H6單元格，在編輯欄中輸入公式：「=SUBTOTAL(3,H3:H5)」，按回車鍵確認。點擊滑鼠右鍵，在彈出的快捷菜單中選擇「設置單元格格式」→數字→數值→點擊確定按鈕。選中H7單元格，在編輯欄中輸入公式：「=SUBTOTAL(9,H3:H5)」，按回車鍵確認。

　　知識點：SUBTOTAL函數返回列表或資料庫中的分類匯總。　　函數語法SUBTOTAL(function_num, ref1, ref2, ...)function_num：為1到11（包含隱藏值）或101到111（忽略隱藏值）之間的數字，指定使用何種函數在列表中進行分類匯總計算。　　ref1、ref2：為要進行分類匯總計算的1到254個區域或引用。　　函數說明如果在ref1、ref2……中有其他的分類匯總（嵌套分類匯總），將忽略這些嵌套分類匯總，以避免重複計算。　　當function_num為從1到11的常數時，SUBTOTAL 函數將包括通過「隱藏行」命令所隱藏的行中的值，當你要對列表中的隱藏和非隱藏數字進行分類匯總時，請使用這些常數。當function_num為從101到111的常數時，SUBTOTAL 函數將忽略通過「隱藏行」命令所隱藏的行中的值。當你只對列表中的非隱藏數字進行分類匯總時，就使用這些常數。　　function_num對應的函數如下：

本例公式說明=SUBTOTAL(3,H3:H5)，「3」對應COUNTA函數，表示返回H3:H5單元格區域中非空值的單元格個數。=SUBTOTAL(9,H3:H5)，「9」對應SUM函數，表示對H3:H5單元格區域求和並返回值。

步驟08 跨行公式複製　　在前面的例子中，我們已經掌握了連續單元格公式的複製，但是當單元格不間斷不連續時，如果複製公式呢？方法很簡單，就是我們熟悉的CTRL+C和CTRL+V命令。選中H6單元格，同時按下CTRL+C鍵，然後用滑鼠選中H10、H14、H17、H21單元格，並同時按下CTRL+V鍵，公式和格式就同時複製完成了。比如，選中H21單元格，編輯欄中顯示的公式就是：「=SUBTOTAL(3,H18:H20)」，Excel的職能化就此體現出來。使用同樣的方法，對匯總公式進行複製。

步驟09 總計數與總計公式的編製

　　對本月車輛使用情況進行匯總統計，選中C23單元格，輸入「總計數」，在C24單元格輸入「總計」。選中H23單元格，在編輯欄中輸入公式：「=SUBTOTAL(3,H3:H20)」，按回車鍵確認。選中H24單元格，在編輯欄中輸入公式：「=SUBTOTAL(9,H3:　　H20)」，按回車鍵確認。步驟10 完善表格　　到此為止，工作表中的內容已經完成，現在來進行工作表最後一步的美化工作。首先，取消零值的顯示。點擊Office按鈕→Excel選項→高級→此工作表的顯示選項→去掉複選項「在具有零值的單元格中顯示零」→確定。然後對字體、字型大小、邊框線和填充色進行設置。

第3節、考勤管理表的製作

　　傳統的考勤表是用手工記錄的，根據員工的出勤情況，划上叉叉、勾勾或圈圈。到了月末，挨個數清楚那些標記，統計出員工當月出勤情況。這種方法很原始，還容易出錯。現代公司當然要實行現代化管理，於是考勤機便應運而生了。但很快弊端就出現了。產品再高科技，也不如人腦來得靈活，因為人懂得如何去作弊。總不能再回歸到原始的考勤辦法吧，利用Excel可以講手工和電腦勞動相結合，起碼現在你不用去數勾勾叉叉了。考勤管理表效果圖

步驟01 新建表格　　啟動Excel2007創建新的工作簿，將Sheet1改名為「考勤管理表」，保存。在A1單元格中輸入「2009年3月考勤表」，在A2單元格輸入「員工編號」，在A3單元格輸入「A101」。選中A3單元格，將游標放在單元格右下角，當游標變成黑十字形狀時，按住滑鼠左鍵不放，向下拉動游標到A22單元格鬆開，在A列自動生成了員工編號。在B2輸入「1號」，同樣使用剛才的方法自動生成日期編號，不過這次是向右拉動滑鼠。

步驟02 設置多列列寬　　現在，你也能發現這個表格的問題，太寬了，以至於無法完整瀏覽。選中B列，按住Shift鍵不放選中AF列，點擊滑鼠右鍵，在彈出的右鍵菜單中選擇「列寬」，在彈出的「列寬」對話框中輸入「2.5」，點擊確定按鈕。用同樣的方法將AG到AL列的列寬設置為「3.5」。適當調整第二行的行高，保證單元格中文字的完整顯示。

步驟03 錄入數據　　為了省事，我們用「a」表示正常出勤，「b」表示遲到，「c」表示早退，「d」表示礦工，「e」表示事假，「f」表示病假。每天根據員工的出勤情況，進行記錄。

步驟04 公式的編製　　月末，記錄完成，需要對出勤表進行統計。有Excel的幫助，我們並不需要去數有幾個a、b、c、d、e、f，通過公式可以非常容易實現統計功能。選中AG3單元格，在編輯欄中輸入公式：「=COUNTIF(B3:AF3,"a")」，按回車鍵確定。使用同樣的方法完成其餘單元格的公式，如下：　　 AH3=COUNTIF(B3:AF3,"b")

AI3=COUNTIF(B3:AF3,"c")

AJ3=COUNTIF(B3:AF3,"d")

AK3=COUNTIF(B3:AF3,"e")

AL3=COUNTIF(B3:AF3,"f")

步驟05 多項公式的複製　　以前的例子中，我們都是對單列的公式進行複製，當連續幾列都需要複製公式時，該怎麼辦呢？其實，方法是相同的。選中AG3:AL3單元格區域，將游標放在AL3右下角，當游標變成黑十字形狀時，按住滑鼠左鍵不放，向下拉動游標至第22行，鬆開滑鼠左鍵，就完成了AG4:AL22單元格區域的公式複製。

步驟06 完善表格　　表格中內容已經完善，接下來就是表格外觀的美化了。對於這種看上有些單調的表格，除了用邊框線區別外，最好還要使用不同的填充色，將數據記錄區域和數據統計區域區分，這樣更便於瀏覽。