|
財務人員實戰(zhàn)Excel之五---------辦公室管理工作表(未完，接下章，待續(xù))
第1節(jié)、辦公用品領用記錄表
第2節(jié)、財務報表中車輛使用管理表
第3節(jié)、考勤管理表的制作
第5章 Excel 辦公室管理工作表
第1節(jié)、辦公用品領用記錄表
辦公室用品分為消耗性物品和非消耗性物品，領用需登記在冊。一來可以掌控耗材的使用情況，控制成本，二來對于物品的領用做到心中有數(shù)，特別是非消耗性辦公室用品原則不能重復申領，登記可做到有賬可查。
辦公室用品領用表效果圖
步驟01 新建工作表
啟動Excel2007，新建工作簿，將Sheet1改名為“辦公用品領用記錄表”。選中A1單元格，在編輯欄中輸入“辦公用品領用記錄表”，在A2:I2單元格區(qū)域輸入標題，在對齊方式中點擊“居中”按鈕。選中A1:I1單元格區(qū)域，點擊上方“合并居中”按鈕。
步驟02 設置格式
選中A3單元格，點擊鼠標右鍵，在彈出的快捷菜單中選擇“設置單元格格式”→“數(shù)字”→“日期”，在右邊的“類型”列表中選擇“01-3-14”，點擊確定。選中A3單元格，點擊“格式刷”按鈕，當鼠標變化后按著Shift鍵不放點擊A17，就完成了A列單元格區(qū)域的格式復制。
步驟03 錄入數(shù)據(jù)
按照當月辦公用品的領用情況，逐一將數(shù)據(jù)錄入表格。
步驟04 計算價值
選中F3單元格，在編輯欄中輸入公式：“=PRODUCT(D3:E3)”，按回車鍵確定。
選中F3單元格，將光標放在單元格右下角，當光標變成黑十字形狀時，按住鼠標左鍵不放，向下拖動鼠標到F17松開，就完成了F4:F17單元格區(qū)域的公式復制。
知識點：PRODUCT函數(shù)PRODUCT函數(shù)將所有以參數(shù)形式給出的數(shù)字相乘，并返回乘積值。
函數(shù)語法PRODUCT（number1,number2,…）
函數(shù)說明①當參數(shù)為數(shù)字、邏輯值或數(shù)字的文字型表達式時可以被計算；當參數(shù)為錯誤值或是不能轉(zhuǎn)換為數(shù)字的文字時，將導致錯誤。
②如果參數(shù)為數(shù)組或引用，只有其中的數(shù)字將被計算。數(shù)組或引用中的空白單元格、邏輯值、文本或錯誤值將被忽略。
步驟05 完善表格
表格中數(shù)據(jù)已經(jīng)完成，現(xiàn)在對表格的樣式做進一步美化。設置字體、字號、邊框線和填充色，并適當調(diào)整列寬，保證表中內(nèi)容完整顯示。
第2節(jié)、財務報表中車輛使用管理表
很多辦公用品在使用中會產(chǎn)生費用，而使用又存在公事或私事兩種情況，費用上就會有所區(qū)別。該怎么處理這類賬務呢？這里以車輛使用為例，做簡單講解，財務人員可在實際應用中舉一反三。
車輛使用管理表效果圖
步驟01 新建工作表
啟動Excel2007，創(chuàng)建新的工作簿，將Sheet1改名為“車輛使用管理表”。選中A1單元格，在編輯欄輸入“公司車輛使用管理表”。在A2:J2單元格區(qū)域輸入標題，并適當調(diào)整列寬，保證單元格中內(nèi)容完整顯示。選中A1:J1單元格區(qū)域，點擊“合并居中”按鈕。
步驟02 數(shù)據(jù)錄入
將當月用車記錄逐一錄入，在錄入時，按部門順序錄入。選中H3單元格，點擊鼠標右鍵彈出快捷菜單，選擇“設置單元格格式”→“數(shù)字”→“貨幣”→設置“小數(shù)位數(shù)”的值為“0”→“貨幣符號”選擇人民幣符號，點擊確定完成設置。選中H3單元格，點擊“格式刷”按鈕，當光標變化后按著Shift鍵不放，點擊J12單元格，完成H3:J12單元格區(qū)域的格式復制?，F(xiàn)在，這個單元格區(qū)域的數(shù)字前自動生成了一個人民幣符號。
步驟03 報銷費公式的編制
當車輛使用時為了辦公事，車輛消耗費可以報銷，如果車輛使用為私事，那么車輛產(chǎn)生的消耗費則不予報銷。本著這個原則，來編制報銷費的公式。選中I3單元格，在編輯欄中輸入公式：“=IF(D3="公事",H3,0)”，按回車鍵確定。
步驟04 報銷費公式的復制
選中I3單元格，將光標放在單元格的右下角，當光標變成黑十字形狀時，按著鼠標左鍵不放，向下拖動鼠標到I12單元格松開，就完成了I列單元格區(qū)域公式的復制。
步驟05 編制駕駛員補助費
選中J3單元格，在編輯欄中輸入公式：“=IF((G3-F3)*24>8,INT((G3-F3)*24-8) *30,0)”，按回車鍵確定。選中J3單元格，將光標放在單元格右下角，當光標變成黑十字形狀時，按住鼠標左鍵不放，向下拖動光標到J12單元格松開，完成J列公式的復制。
步驟06 插入部門合計行
為了方便觀察和統(tǒng)計各部門用車情況，需要按部門進行分類統(tǒng)計。在不同的部門后插入兩個空行，然后在C列按部門的不同，分別輸入“業(yè)務部 計數(shù)”和“業(yè)務部門 匯總”，同時調(diào)整列寬保證單元格中內(nèi)容完整顯示。
步驟07 編制各部門計數(shù)、匯總公式
選中H6單元格，在編輯欄中輸入公式：“=SUBTOTAL(3,H3:H5)”，按回車鍵確認。點擊鼠標右鍵，在彈出的快捷菜單中選擇“設置單元格格式”→數(shù)字→數(shù)值→點擊確定按鈕。選中H7單元格，在編輯欄中輸入公式：“=SUBTOTAL(9,H3:H5)”，按回車鍵確認。
知識點：SUBTOTAL函數(shù)返回列表或數(shù)據(jù)庫中的分類匯總。
函數(shù)語法SUBTOTAL(function_num, ref1, ref2, ...)function_num：為1到11（包含隱藏值）或101到111（忽略隱藏值）之間的數(shù)字，指定使用何種函數(shù)在列表中進行分類匯總計算。
ref1、ref2：為要進行分類匯總計算的1到254個區(qū)域或引用。
函數(shù)說明如果在ref1、ref2……中有其他的分類匯總（嵌套分類匯總），將忽略這些嵌套分類匯總，以避免重復計算。
當function_num為從1到11的常數(shù)時，SUBTOTAL 函數(shù)將包括通過“隱藏行”命令所隱藏的行中的值，當你要對列表中的隱藏和非隱藏數(shù)字進行分類匯總時，請使用這些常數(shù)。當function_num為從101到111的常數(shù)時，SUBTOTAL 函數(shù)將忽略通過“隱藏行”命令所隱藏的行中的值。當你只對列表中的非隱藏數(shù)字進行分類匯總時，就使用這些常數(shù)。
function_num對應的函數(shù)如下：
本例公式說明=SUBTOTAL(3,H3:H5)，“3”對應COUNTA函數(shù)，表示返回H3:H5單元格區(qū)域中非空值的單元格個數(shù)。
=SUBTOTAL(9,H3:H5)，“9”對應SUM函數(shù)，表示對H3:H5單元格區(qū)域求和并返回值。
步驟08 跨行公式復制
在前面的例子中，我們已經(jīng)掌握了連續(xù)單元格公式的復制，但是當單元格不間斷不連續(xù)時，如果復制公式呢？方法很簡單，就是我們熟悉的CTRL+C和CTRL+V命令。選中H6單元格，同時按下CTRL+C鍵，然后用鼠標選中H10、H14、H17、H21單元格，并同時按下CTRL+V鍵，公式和格式就同時復制完成了。比如，選中H21單元格，編輯欄中顯示的公式就是：“=SUBTOTAL(3,H18:H20)”，Excel的職能化就此體現(xiàn)出來。使用同樣的方法，對匯總公式進行復制。
步驟09 總計數(shù)與總計公式的編制
對本月車輛使用情況進行匯總統(tǒng)計，選中C23單元格，輸入“總計數(shù)”，在C24單元格輸入“總計”。選中H23單元格，在編輯欄中輸入公式：“=SUBTOTAL(3,H3:H20)”，按回車鍵確認。選中H24單元格，在編輯欄中輸入公式：“=SUBTOTAL(9,H3: H20)”，按回車鍵確認。
步驟10 完善表格
到此為止，工作表中的內(nèi)容已經(jīng)完成，現(xiàn)在來進行工作表最后一步的美化工作。首先，取消零值的顯示。點擊Office按鈕→Excel選項→高級→此工作表的顯示選項→去掉復選項“在具有零值的單元格中顯示零”→確定。然后對字體、字號、邊框線和填充色進行設置。
第3節(jié)、考勤管理表的制作
傳統(tǒng)的考勤表是用手工記錄的，根據(jù)員工的出勤情況，劃上叉叉、勾勾或圈圈。到了月末，挨個數(shù)清楚那些標記，統(tǒng)計出員工當月出勤情況。這種方法很原始，還容易出錯。現(xiàn)代公司當然要實行現(xiàn)代化管理，于是考勤機便應運而生了。但很快弊端就出現(xiàn)了。產(chǎn)品再高科技，也不如人腦來得靈活，因為人懂得如何去作弊?？偛荒茉倩貧w到原始的考勤辦法吧，利用Excel可以講手工和電腦勞動相結(jié)合，起碼現(xiàn)在你不用去數(shù)勾勾叉叉了。
考勤管理表效果圖
步驟01 新建表格
啟動Excel2007創(chuàng)建新的工作簿，將Sheet1改名為“考勤管理表”，保存。在A1單元格中輸入“2009年3月考勤表”，在A2單元格輸入“員工編號”，在A3單元格輸入“A101”。選中A3單元格，將光標放在單元格右下角，當光標變成黑十字形狀時，按住鼠標左鍵不放，向下拉動光標到A22單元格松開，在A列自動生成了員工編號。在B2輸入“1號”，同樣使用剛才的方法自動生成日期編號，不過這次是向右拉動鼠標。
步驟02 設置多列列寬
現(xiàn)在，你也能發(fā)現(xiàn)這個表格的問題，太寬了，以至于無法完整瀏覽。選中B列，按住Shift鍵不放選中AF列，點擊鼠標右鍵，在彈出的右鍵菜單中選擇“列寬”，在彈出的“列寬”對話框中輸入“2.5”，點擊確定按鈕。用同樣的方法將AG到AL列的列寬設置為“3.5”。適當調(diào)整第二行的行高，保證單元格中文字的完整顯示。
步驟03 錄入數(shù)據(jù)
為了省事，我們用“a”表示正常出勤，“b”表示遲到，“c”表示早退，“d”表示礦工，“e”表示事假，“f”表示病假。每天根據(jù)員工的出勤情況，進行記錄。
步驟04 公式的編制
月末，記錄完成，需要對出勤表進行統(tǒng)計。有Excel的幫助，我們并不需要去數(shù)有幾個a、b、c、d、e、f，通過公式可以非常容易實現(xiàn)統(tǒng)計功能。選中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 多項公式的復制
以前的例子中，我們都是對單列的公式進行復制，當連續(xù)幾列都需要復制公式時，該怎么辦呢？其實，方法是相同的。選中AG3:AL3單元格區(qū)域，將光標放在AL3右下角，當光標變成黑十字形狀時，按住鼠標左鍵不放，向下拉動光標至第22行，松開鼠標左鍵，就完成了AG4:AL22單元格區(qū)域的公式復制。
步驟06 完善表格
表格中內(nèi)容已經(jīng)完善，接下來就是表格外觀的美化了。對于這種看上有些單調(diào)的表格，除了用邊框線區(qū)別外，最好還要使用不同的填充色，將數(shù)據(jù)記錄區(qū)域和數(shù)據(jù)統(tǒng)計區(qū)域區(qū)分，這樣更便于瀏覽。
|
