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