《計(jì)算機(jī)應(yīng)用基礎(chǔ)立體化教程》課件第5章

第5章報(bào)銷單設(shè)計(jì)與制作5.1任務(wù)書5.2任務(wù)示范5.3知識(shí)拓展

本項(xiàng)目的任務(wù)是財(cái)務(wù)上經(jīng)常用到的報(bào)表單據(jù)報(bào)銷單的制作。在一個(gè)企業(yè)中，員工出差或者外出培訓(xùn)等都需要報(bào)銷單，那么報(bào)銷單中需要包含哪些必要元素，如何避免報(bào)銷單的誤填，怎樣可以更好地提醒報(bào)銷人正確填寫，如何設(shè)置保護(hù)區(qū)使普通用戶無法修改或刪除報(bào)銷單的固定文字、格式和公式，對(duì)于這些問題，我們可以通過如圖5-1所示的報(bào)銷單的設(shè)計(jì)和制作學(xué)會(huì)相應(yīng)的方法。

圖5-1報(bào)銷單

5.1任務(wù)書

1.輸入相關(guān)內(nèi)容

通過本任務(wù)熟悉Excel2010的數(shù)據(jù)輸入和單元格的合并。

2.報(bào)銷單格式設(shè)置

通過本任務(wù)熟悉Excel2010相關(guān)單元格格式設(shè)置，掌握字體、填充、邊框等設(shè)置，學(xué)會(huì)調(diào)整單元格數(shù)字類型，調(diào)整行高和列寬，使報(bào)銷單看起來更加清晰、美觀。

3.公式和函數(shù)的使用

對(duì)報(bào)銷單中的金額及日期進(jìn)行計(jì)算，將一些日期和小計(jì)、合計(jì)項(xiàng)目自動(dòng)算出。通過本任務(wù)掌握基本函數(shù)SUM()、MAX()，MIN()及日期函數(shù)TODAY()的使用。

4.設(shè)置數(shù)據(jù)有效性及插入批注

本任務(wù)通過對(duì)報(bào)銷單進(jìn)行有效性設(shè)置，避免輸入錯(cuò)誤。身份證必須是18位，職務(wù)可以采用下拉菜單方式進(jìn)行選擇，避免輸入失誤。添加批注，對(duì)填寫內(nèi)容起到提示作用。

5.保護(hù)工作表

本任務(wù)通過對(duì)報(bào)銷單工作表進(jìn)行保護(hù)，防止用戶在填表時(shí)修改或刪除表格固有的文字和相關(guān)的格式；通過對(duì)文字和公式計(jì)算部分進(jìn)行保護(hù)，使表格在安全情況下進(jìn)行填寫。

6.隱藏標(biāo)題和網(wǎng)格線

本任務(wù)通過對(duì)報(bào)銷單隱藏其標(biāo)題和網(wǎng)格線，使其看起來更干凈。

5.2任務(wù)示范

1.輸入相關(guān)內(nèi)容

報(bào)銷單成品

【任務(wù)實(shí)施】輸入相關(guān)標(biāo)題、文字、金額、身份證、日期等信息。

操作步驟1：新建一個(gè)工作簿，取名“報(bào)銷單.xlsx”。

操作步驟2：打開后，雙擊sheet1工作表標(biāo)簽，重命名為“報(bào)銷單”。

操作步驟3：在報(bào)銷單工作表中輸入相關(guān)文字，并合并相應(yīng)的單元格。

2.報(bào)銷單格式設(shè)置

【任務(wù)實(shí)施】

(1)設(shè)置相關(guān)字體格式。

操作步驟1：設(shè)置標(biāo)題字體格式。

選擇標(biāo)題所在單元格，設(shè)置字體為“幼圓”，字號(hào)為“22”“加粗”，水平居中、底端對(duì)齊。

操作步驟2：設(shè)置固定文字的字體格式。

“單據(jù)號(hào)”字體格式：字體為“楷體”，字號(hào)為“10”，靠下右對(duì)齊。

“簡要說明：”“報(bào)銷人基本信息”“票據(jù)期限”等固定文字的字體格式：字體均為“楷體”，字號(hào)為11”“加粗”。

報(bào)銷人基本信息框里面的固定文字及右側(cè)的“從”和“至”單元格格式：字體為“楷體”，字號(hào)為“10”“加粗”，右對(duì)齊。

第12行的列標(biāo)題文字為居中對(duì)齊，合計(jì)、預(yù)支款和總計(jì)為右對(duì)齊?？傆?jì)大寫金額的字體格式：字體為“楷體”，字號(hào)為“11”“加粗”，右對(duì)齊。

操作步驟3：設(shè)置需填寫區(qū)域的字體格式。

需填寫數(shù)字、日期格式的字體格式：字體為“TimesNewRoman”，字號(hào)為“10”；需填寫文字說明的單元格字體格式：字體為“宋體”，字號(hào)為“10”。文字格式相同的部分可直接用格式刷。

(2)將所有需要輸入公式的區(qū)域填充為淺藍(lán)色，并為報(bào)銷單添加邊框，如圖5-1所示。

操作步驟1：設(shè)置填充色。

選擇L9:L10、L13:L22、F23:K23、L24和L26單元格，設(shè)置填充色為“淺藍(lán)色”。

操作步驟2：為報(bào)銷單添加邊框。

打開“簡要說明：”后的單元格格式設(shè)置對(duì)話框，設(shè)置藍(lán)色下邊線。

“單據(jù)號(hào)：”“姓名”“證件號(hào)碼”“部門”“從”“至”“總計(jì)大寫金額”“報(bào)銷日期”后面的單元格設(shè)置黑色下框線，其余部分如圖5-1所示設(shè)置邊框線。(B27:G27設(shè)置藍(lán)色下框線，B33:L33設(shè)置黑色粗上框線)

操作步驟3：對(duì)單元格調(diào)整數(shù)字類型。

單據(jù)號(hào)如果是以0開頭的數(shù)字，需要在前面加上“’”，使之轉(zhuǎn)化成文本格式，或者直接將此單元格設(shè)置為文本格式，如圖5-2所示。涉及金額費(fèi)用的空白單元格均要設(shè)置為會(huì)計(jì)專用格式(人民幣符號(hào)，精確兩位小數(shù)，千分位)，如圖5-3所示。設(shè)置票據(jù)期限和日期的空白單元格格式為自定義格式“yyyy-mm-dd”，如圖5-4所示。報(bào)銷日期后的單元格設(shè)置為短日期格式?？傆?jì)大寫金額后的單元格設(shè)置為特殊，中文大寫數(shù)字，并在自定義中設(shè)置，在已有設(shè)置后面加上“元整”，如圖5-5所示。

圖5-2設(shè)置文本類型格式圖5-3設(shè)置會(huì)計(jì)專用格式圖5-4設(shè)置自定義日期格式圖5-5設(shè)置自定義貨幣格式

(3)設(shè)置行高、列寬。

操作步驟1：設(shè)置行高。

第1～4行、第7～8行、第11行、第13～26行行高設(shè)置為自動(dòng)調(diào)整行高，第5行行高為23，第9、第10行行高為19，第12行高為16，第27～32行行高為15。

操作步驟2：設(shè)置列寬。

A、M列寬設(shè)置為自動(dòng)調(diào)整列寬，B列寬為17，C列寬為10，D列寬為12，E列寬為10，F(xiàn)～K列寬為11，L列寬為13。

3.公式和函數(shù)的使用

【任務(wù)實(shí)施】

所有公式都是以“=”開頭的。

(1)在“從”后的單元格輸入公式“=MIN(B13:B22)”，計(jì)算報(bào)銷單的起始日期。

(2)在“至”后的單元格輸入公式“=MAX(B13:B22)”，計(jì)算報(bào)銷單的終止日期。

(3)小計(jì)是對(duì)某天報(bào)銷費(fèi)用的求和，輸入公式“=SUM(F13:K13)”，自動(dòng)向下填充。

(4)對(duì)每類票據(jù)也需要小計(jì)，23行輸入相應(yīng)的求和公式，只是修改下參數(shù)，公式為“=SUM(F13:F22)”，向右填充。

(5)在合計(jì)中，利用SUM函數(shù)，對(duì)小計(jì)求和，公式為“=SUM(L13:L22)”。

(6)輸入預(yù)支款后，就能計(jì)算出總計(jì)?？傆?jì)=合計(jì)-預(yù)支款，公式為“=L24-L25”。

(7)總計(jì)大寫金額就是總計(jì)單元格的引用，公式為“=L26”。

(8)填報(bào)日期后的公式可輸入當(dāng)天日期，公式為“=TODAY()”；也可按快捷鍵“Ctrl?+?；”，插入當(dāng)天的日期。

4.設(shè)置數(shù)據(jù)有效性及插入批注

【任務(wù)實(shí)施】

(1)將身份證單元格設(shè)置“數(shù)據(jù)有效性”為允許“文本長度”為18位。

操作步驟：在“數(shù)據(jù)”選項(xiàng)卡中單擊“有效性”按鈕，在彈出的“數(shù)據(jù)有效性”對(duì)話框中設(shè)置“有效性條件”為允許“文本長度”，數(shù)據(jù)等于長度18，如圖5-6所示。

圖5-6有效性設(shè)置文本長度

(2)設(shè)置職務(wù)單元格有效性為序列，可在下拉框選擇允許的職務(wù)(“銷售員，工人，技師，工程師，高工，售后人員”)。

操作步驟：在“數(shù)據(jù)”選項(xiàng)卡單擊“有效性”按鈕，在彈出的“數(shù)據(jù)有效性”對(duì)話框中設(shè)置有效性條件為允許序列，來源輸入“銷售員，工人，技師，工程師，高工，售后人員”，如圖5-7所示。

圖5-7有效性允許自定義序列

(3)為報(bào)銷單添加批注。

操作步驟1：在“審閱”選項(xiàng)卡中對(duì)L9單元格新建批注，內(nèi)容為“起始日期自動(dòng)計(jì)算，請(qǐng)勿填寫?！?/p>

操作步驟2：對(duì)L12單元格新建批注，內(nèi)容為“藍(lán)色單元格自動(dòng)計(jì)算，請(qǐng)勿填寫?！?/p>

5.保護(hù)工作表

【任務(wù)實(shí)施】

(1)對(duì)需要填寫文字或輸入公式的單元格設(shè)置為非鎖定單元格。

操作步驟：選中所有需要填寫文字或輸入公式的單元格后，按快捷鍵“Ctrl?+?1”，打開單元格設(shè)置對(duì)話框，設(shè)置保護(hù)選項(xiàng)卡，將鎖定前面的鉤去掉，如圖5-8所示。

圖5-8取消鎖定單元格

(2)設(shè)置保護(hù)工作表。

操作步驟：在“審閱”選項(xiàng)卡中單擊“保護(hù)工作表”按鈕，在彈出的對(duì)話框內(nèi)設(shè)置“允許此工作表的所有用戶進(jìn)行”勾上“選定未鎖定的單元格”，此處密碼可以為空，也可以設(shè)置，如圖5-9所示。

圖5-9“保護(hù)工作表”對(duì)話框

6.隱藏標(biāo)題和網(wǎng)格線

【任務(wù)實(shí)施】

操作步驟1：在“視圖”選項(xiàng)卡中選擇“網(wǎng)格線”和“標(biāo)題”，將前面的鉤去掉即可，如圖5-10所示。

圖5-10隱藏網(wǎng)格線和標(biāo)題

操作步驟2：將34行之后的行都選中(按快捷鍵“Ctrl?+?Shift?+↓”)，單擊右鍵在彈出菜單中設(shè)置為隱藏；然后從N列開始選擇后面的列(按快捷鍵“Ctrl?+?Shift?+?→”)，單擊右鍵設(shè)置這些列為隱藏即可。

5.3知識(shí)拓展

1.多工作表的操作

一個(gè)Excel文件最多可以包含255張工作表。在選定多張工作表時(shí)可分為兩種情況：一種是全部工作表，另一種是部分工作表。前一種情況，在工作表底部右擊鼠標(biāo)，在彈出的快捷菜單中選擇“選定全部工作表“；后一種情況，通過按住“Shift”鍵再用鼠標(biāo)選定相鄰的多個(gè)工作表，或者按住“Ctrl”鍵再用鼠標(biāo)選定不相鄰的多個(gè)工作表來實(shí)現(xiàn)。選定之后，就可以對(duì)頁面及格式等進(jìn)行統(tǒng)一設(shè)置，而不必逐張進(jìn)行；也可以在多張工作表內(nèi)執(zhí)行查找和替換操作。

2.函數(shù)

Excel函數(shù)是一種預(yù)定義的內(nèi)置公式，它使用一些稱為參數(shù)的特定數(shù)值按特定的順序或結(jié)構(gòu)進(jìn)行計(jì)算，然后返回結(jié)果。所有函數(shù)都包含三部分：函數(shù)名、參數(shù)和圓括號(hào)。

3.單元格引用

單元格的引用分為相對(duì)引用、絕對(duì)引用和混合引用。

所謂相對(duì)引用，是指公式中引用的單元格隨公式所在單元格位置的變化而變化。例如，在B2中設(shè)置公式“=A2”，當(dāng)把B2中的公式復(fù)制、粘貼到B3中時(shí)，B3中的公式根據(jù)B2到B3的位置變化，相應(yīng)地變成“=A3”。

所謂絕對(duì)引用，是指公式中引用的單元格不隨公式所在單元格位置的變化而變化。例如，在B2中設(shè)置公式“=$A$2”($號(hào)是絕對(duì)引用的標(biāo)志)，把B2中的公式復(fù)制到B3中時(shí)公式仍為“=$A$2”。假設(shè)B2的公式為“=$A2”，則這就是所謂的混合引用。該式中，列標(biāo)前有$號(hào)，表示將公式復(fù)制到其他位置時(shí)，列不發(fā)生變化，但行號(hào)會(huì)隨之變化；而“=A$2”表示將公式復(fù)制到其他位置時(shí)，列變化，但行號(hào)不會(huì)變動(dòng)。

使用“F4”功能鍵可以在相對(duì)引用、絕對(duì)引用和混合引用之間進(jìn)行轉(zhuǎn)換。

4.隱藏單元格中的所有值

有時(shí)候，需要將單元格中的所有值隱藏起來，此時(shí)可以選擇包含要隱藏值的單元格。單擊“開始”→“格式”→“設(shè)置單元格格式”→“數(shù)字”→“自定義”，然后將“類型”框中已有的代碼刪除，鍵入“；；；”(三個(gè)分號(hào))即可。其原因是單元格數(shù)字的自定義格式是由正數(shù)、負(fù)數(shù)、零和文本四部分組成的。這四部分用三個(gè)分號(hào)分隔，哪個(gè)部分空，相應(yīng)的內(nèi)容就不會(huì)在單元格中顯示。

5.解決SUM函數(shù)參數(shù)中的數(shù)量限制

Excel中SUM函數(shù)的參數(shù)不得超過30個(gè)。

6.利用公式計(jì)算大寫金額(包含角、分的計(jì)算)

解決方法：在I26單元格輸入公式，其中L26為合計(jì)單元格。

=IF((INT(L26*10)-INT(L26)*10)=0，TEXT(INT(L26)，"[DBNum2]G/通用格式")&"元

"&IF((INT(L26*100)-INT((L26)*10)*10)=0，"整"，"零

"&TEXT(INT(L26*100)-INT(L26*10)*10，"[DBNum2]G/通用格式")&"分")，

TEXT(INT(L26)，"[DBNum2]G/通用格式")&"元

"&IF((INT(L26*100)-INT((L26)*10)*10)=0，TEXT((INT(L26*10)-INT(L26)*10)，

"[DBNum2]G/通用格式")&