EXCEL在人力資源管理中的應(yīng)用案例與技巧教學(xué)提綱

1、Good is good, but better carries it.精益求精，善益求善。EXCEL在人力資源管理中的應(yīng)用案例與技巧Excel在管理中的應(yīng)用案例01第1部分Excel基礎(chǔ)概念名稱名稱可以代表一個(gè)單元格或者一個(gè)單元格區(qū)域，或者是常量，公式。名稱的定義：選中需要命名的單元格或區(qū)域，在界面左上角名稱框中輸入名稱后回車；名稱的刪除：插入名稱定義，選中需要?jiǎng)h除的名稱點(diǎn)擊刪除按鈕，點(diǎn)確定。名稱的引用：需要引用某單元格時(shí)輸入該單元格的名稱：名稱第1步：選中需要命名的某個(gè)單元格或單元格區(qū)域。第2步：在左上角名稱框輸入命名后回車。刪除已有命名：選擇“插入”“名稱”“定義”；選中需要?jiǎng)h除的名稱，

2、點(diǎn)擊“刪除”按鈕。名稱命名的優(yōu)點(diǎn)：避免絕對(duì)引用的錯(cuò)誤對(duì)公式進(jìn)行文字化表述，讓公式更加容易理解可以在整個(gè)工作簿中通用，引用方便常用引用函數(shù)row:返回指定單元格的行號(hào)column:返回指定單元格的列標(biāo)match:返回查找值在查找范圍中的序號(hào)=match(查找值，查找范圍，0)其第三個(gè)參數(shù)為0，表示查找精確值address:返回單元格名稱，其參數(shù)為行號(hào)列標(biāo)或計(jì)算行號(hào)列標(biāo)的表達(dá)式=address(行號(hào)，列標(biāo))=address(1,1)此公式返回A1indirect:返回單元格的值.其參數(shù)為單元格名稱=indirect(“A1”),假設(shè)A1=10,則indirect返回10index:在某區(qū)域內(nèi)查找某

3、個(gè)位置的值=index(查找區(qū)域，查找值所在的行號(hào)，查找值所在的列號(hào))offset：指定基點(diǎn)，指定位移量，得到單元格引用單個(gè)單元格引用：=offset(基點(diǎn)單元格，向下移動(dòng)的行，向右移動(dòng)的列)區(qū)域的引用：=offset(基點(diǎn)單元格，向下移動(dòng)的行，向右移動(dòng)的列，區(qū)域包括的行數(shù)，區(qū)域包括的列數(shù))向上和向左移動(dòng)時(shí)，位移量為負(fù)值。=offset(A1,1,1)將得到B2單元格的值動(dòng)態(tài)引用動(dòng)態(tài)引用是通過(guò)引用函數(shù)實(shí)現(xiàn)對(duì)于單元格或區(qū)域的相對(duì)引用。它和相對(duì)引用的效果很相似，但比簡(jiǎn)單的相對(duì)引用用途廣泛。常用的動(dòng)態(tài)引用的實(shí)現(xiàn)方法有：引用函數(shù)嵌套。比如offset和row,column嵌套；index和match，

4、indirect,address嵌套等；引用函數(shù)與控件結(jié)合使用，此類控件包括組合框，滾動(dòng)條。動(dòng)態(tài)引用的作用：實(shí)現(xiàn)對(duì)于單元格的動(dòng)態(tài)引用；進(jìn)行動(dòng)態(tài)分析；制作動(dòng)態(tài)報(bào)表。運(yùn)算類型數(shù)值運(yùn)算：1+1=2邏輯運(yùn)算：10=TRUE在邏輯運(yùn)算中，TRUE=1，F(xiàn)ALSE=0我們利用邏輯運(yùn)算進(jìn)行條件判斷在Excel中常用的一些邏輯函數(shù)：if,and,or,not,iserror等and:只有當(dāng)所有條件全部滿足，才會(huì)返回true的邏輯值語(yǔ)法：and(條件1，條件2，)or:滿足其中任何一個(gè)條件，都會(huì)返回true的邏輯值語(yǔ)法：or(條件1，條件2，條件3,)函數(shù)調(diào)用的語(yǔ)法函數(shù)名稱（參數(shù)1，參數(shù)2，）絕對(duì)引用和相對(duì)引用

5、：絕對(duì)引用：所引用的單元格不隨著公式的復(fù)制而移動(dòng)的引用方式。相對(duì)引用：所引用的單元格隨著公式的復(fù)制而移動(dòng)的引用方式。改變引用方式的方法：在公式欄中選中需要改變引用方式的單元格，按F4鍵給需要引用的單元格定義名稱，然后在公式中引用該名稱數(shù)組公式對(duì)單元格區(qū)域進(jìn)行多重計(jì)算的計(jì)算方式。與普通計(jì)算公式的區(qū)別是錄入公式結(jié)束后，需要同時(shí)按下ctrl+shift+enter，其特征是在公式兩端會(huì)出現(xiàn)一對(duì)大括號(hào)。如上圖例，使用一個(gè)公式計(jì)算出所有產(chǎn)品的金額合計(jì)，引用的是所有的單價(jià)和所有的數(shù)量，執(zhí)行的是多重計(jì)算。錯(cuò)誤提示Excel中存在錯(cuò)誤的類型，比如1/0=#DIV/0!,todas()=#NAMES!iserr

6、or是一個(gè)邏輯函數(shù)，用以判斷某個(gè)單元格內(nèi)的值是否是一個(gè)錯(cuò)誤，是錯(cuò)誤則返回TRUE,不是錯(cuò)誤則返回FALSE.iserror有時(shí)可以和if函數(shù)嵌套進(jìn)行一些較為復(fù)雜的判斷。有取值區(qū)間的隨機(jī)數(shù)=最小值（最大值最小值）*rand()循環(huán)引用是單元格引用其自身的引用方式?？梢栽O(shè)置Excel允許進(jìn)行循環(huán)引用：工具-選項(xiàng)-重新計(jì)算：將迭代計(jì)算選項(xiàng)打鉤選中。行列互換（函數(shù)方式）：利用transpose函數(shù)+數(shù)組公式實(shí)現(xiàn)。首先選中行列數(shù)和原區(qū)域相反的一個(gè)區(qū)域；然后輸入transpose函數(shù)最后按下組合鍵ctrl+shift+enter第2部分Excel設(shè)置顯示當(dāng)前文件的完整路徑菜單區(qū)域右鍵菜單Web顯示菜單項(xiàng)全

7、部菜單視圖工具欄自定義選項(xiàng)始終顯示整個(gè)菜單鼠標(biāo)移動(dòng)方向工具選項(xiàng)編輯按Enter鍵后移動(dòng)方向隱藏界面要素工具選項(xiàng)視圖包括：網(wǎng)格線，滾動(dòng)條，工作表標(biāo)簽，行號(hào)列標(biāo)等。以顯示值為準(zhǔn)工具選項(xiàng)重新計(jì)算以顯示精度為準(zhǔn)行的合計(jì)與列的合計(jì)有時(shí)出現(xiàn)不相等的情況?？梢圆捎靡韵路椒ń鉀Q。選擇“工具”“選項(xiàng)”“重新計(jì)算”“以顯示精度為準(zhǔn)”，選中該選項(xiàng)。自定義序列工具選項(xiàng)自定義序列改變文件保存位置工具選項(xiàng)常規(guī)默認(rèn)文件位置改變文件用戶名工具選項(xiàng)用戶名單元格自動(dòng)換行格式單元格對(duì)齊自動(dòng)換行第3部分Excel基礎(chǔ)操作快速選中數(shù)據(jù)表的整行或者整列ctrl+shift+下箭頭/右箭頭快速選中區(qū)域ctrl+shift+8行列互換復(fù)制需

8、要進(jìn)行行列互換的區(qū)域后，將光標(biāo)放置在數(shù)據(jù)表外面位置，選擇性粘貼選中“轉(zhuǎn)置”選項(xiàng)一個(gè)單元格內(nèi)容輸入為多行alt+回車鍵顯示公式ctrl+凍結(jié)窗口選擇需要進(jìn)行凍結(jié)的單元格位置，選擇窗口凍結(jié)窗格自定義格式語(yǔ)法：大于條件值格式；小于條件值格式；等于條件值格式；文本格式縮放數(shù)值0.00，按百萬(wàn)縮放0“.”0，按萬(wàn)縮放0.00,按千縮放不復(fù)制隱藏的行或列a首先選中需要復(fù)制的被隱藏了一些行或列的表格區(qū)域；b然后點(diǎn)擊“編輯”-“定位”-“定位條件”，在其中選擇“可見(jiàn)單元格”；c復(fù)制表格區(qū)域，粘貼即可。案例02一文本的處理等長(zhǎng)文本的分割從字符串的左邊取字符：=Left(字符串，文本長(zhǎng)度)從字符串的右邊取字符：=

9、Right(字符串，文本長(zhǎng)度)從字符串的中間取字符：=Mid(字符串，文本起始位置，文本長(zhǎng)度)不等長(zhǎng)文本的分割第1步：選中要進(jìn)行分割的字符串區(qū)域。第2步：點(diǎn)擊“數(shù)據(jù)”“分列”，在文本分列向?qū)е羞x擇適合的分隔符。第3步：設(shè)置需要導(dǎo)入的列以及放置該列的位置。文本的合并=Concatenate(文本1，文本2，)使用連接符&：動(dòng)態(tài)表頭：=ABC公司&YEAR(NOW()&年&MONTH(NOW()&月報(bào)表有效性選中需要設(shè)置有效性的區(qū)域，點(diǎn)擊“數(shù)據(jù)”“有效性”，在有效性條件中選擇“序列”，在來(lái)源中錄入或選取列表。輸入信息：事前提醒。出錯(cuò)警告：事后提示。如果待選列表不在當(dāng)前工作表中，需要首先為該列表定義

10、名稱，然后在有效性來(lái)源框中輸入：“=該列表的名稱”有效性的其他用法不允許錄入重復(fù)數(shù)據(jù)的有效性設(shè)定：countif(e:e,e12)=1輸入的內(nèi)容中必須包括某字符：=not(iserror(find(中國(guó),g30)二級(jí)選項(xiàng)：首先將一級(jí)選項(xiàng)的每個(gè)項(xiàng)目定義一個(gè)名稱，該名稱內(nèi)容包括相應(yīng)的二級(jí)項(xiàng)目；制作一級(jí)項(xiàng)目的有效性；制作二級(jí)項(xiàng)目的有效性：內(nèi)容為：=INDIRECT(g6)，其中g(shù)6為設(shè)定了有效性的一級(jí)選項(xiàng)所在的單元格。圈示無(wú)效數(shù)據(jù)。選擇“工具”“公式審核”“顯示公式審核工具欄”，點(diǎn)擊“圈示無(wú)效數(shù)據(jù)”按鈕。該工具可以將所有不符合有效性設(shè)定的內(nèi)容圈示出來(lái)。二日期的處理Datedif函數(shù)：用于計(jì)算兩個(gè)日期

11、之間的年數(shù)，月數(shù)，天數(shù)。=Datedif(開始日期，結(jié)束日期，“y”)第三個(gè)參數(shù)：“y”：表示年數(shù)“m”：表示月數(shù)“d”：表示天數(shù)生日提醒=DATEDIF(TODAY(),DATE(YEAR(TODAY(),MONTH(G2),DAY(G2),d)某日期的星期=weekday(A2,2)返回的值是3，則表示該日期是星期三。兩個(gè)日期間的工作日天數(shù)=networkdays(開始日期，結(jié)束日期，節(jié)假日列表)需要首先加載“分析工具庫(kù)”才能使用此工具案例03數(shù)據(jù)查詢VLOOKUP（）VLOOKUP在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。VLOOKUP中的V代表垂

12、直。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value為需要在數(shù)組第一列中查找的數(shù)值。Lookup_value可以為數(shù)值、引用或文本字符串。也可以理解為：兩表共有的索引字段。Table_array為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表?？梢允褂脤?duì)區(qū)域或區(qū)域名稱的引用，例如數(shù)據(jù)庫(kù)或列表。必須使得共有字段位于該范圍的第一列。col_index_num為需要調(diào)轉(zhuǎn)的數(shù)據(jù)位于第二個(gè)參數(shù)中定義的范圍的第幾列。必須為單純數(shù)值。range_lookup定義大致匹配或精確匹配。False或0:精確匹配；true或忽略或1：如果無(wú)

13、法找到精確匹配的值，那么就查找并匹配比查找值小的最近似的值。HLOOKUP()Hlookup中的H是水平的意思，在橫向排列的表格中查詢數(shù)據(jù)用Hlookup，垂直排列的表格查詢用Vlookup函數(shù)。其參數(shù)含義與VLOOKUP近似。案例04查詢表信息查詢表利用有效性和VLOOKUP函數(shù)實(shí)現(xiàn)。利用有效性實(shí)現(xiàn)索引字段的切換。利用VLOOKUP函數(shù)查詢與索引字段相關(guān)的信息。案例05數(shù)據(jù)篩選自動(dòng)篩選（略）高級(jí)篩選由于自動(dòng)篩選只能篩選出簡(jiǎn)單條件的數(shù)據(jù)，因此如要在復(fù)雜條件下進(jìn)行篩選就需要采用高級(jí)篩選的方式。在需要進(jìn)行篩選的數(shù)據(jù)表外設(shè)定篩選條件。在對(duì)話框中設(shè)定：列表區(qū)域?yàn)閿?shù)據(jù)表區(qū)域；條件區(qū)域?yàn)閯偛旁跀?shù)據(jù)表外部設(shè)

14、定的條件區(qū)域，要包括字段名稱和條件所在的單元格區(qū)域。條件格式條件格式可以使得符合特定條件的記錄按照某種設(shè)定的格式顯示。選中要進(jìn)行條件格式設(shè)定的單元格范圍執(zhí)行【格式】【條件格式】在條件格式設(shè)置界面進(jìn)行條件的設(shè)定：統(tǒng)計(jì)Frequency(需要進(jìn)行統(tǒng)計(jì)的數(shù)據(jù)區(qū)域，分段點(diǎn))對(duì)數(shù)據(jù)進(jìn)行區(qū)間統(tǒng)計(jì)。此函數(shù)需要和數(shù)組公式一起使用。此函數(shù)是根據(jù)數(shù)值的大小劃分區(qū)間進(jìn)行統(tǒng)計(jì)的。列出需要分析的工資區(qū)間，選中需要計(jì)算統(tǒng)計(jì)結(jié)果的單元格區(qū)域，輸入函數(shù)：按下組合鍵：ctrl+shift+enter排名次=rank(需排名數(shù)值，排名數(shù)據(jù)范圍，排序方式)排序方式：0或忽略降序，否則為升序。案例06窗體控件的使用下拉菜單的制作。選

15、擇“視圖”“工具欄”“窗體”，出現(xiàn)“窗體”工具條，在工具條上選擇“組合框”。按下鼠標(biāo)左鍵，拖放出合適的大小后釋放鼠標(biāo)，出現(xiàn)下拉菜單形狀。選中該下拉菜單，右鍵選擇“設(shè)置控件格式”在“控制”菜單上，去選擇相應(yīng)的數(shù)據(jù)源?！叭S陰影”選項(xiàng)是下拉菜單顯示效果設(shè)定。單元格鏈接：該處指定的單元格中將存儲(chǔ)在控件中被選中的項(xiàng)目在數(shù)據(jù)源中的序列號(hào)。我們經(jīng)常利用該序列號(hào)引用控件選中的值，還可以利用該值制作動(dòng)態(tài)圖表。根據(jù)報(bào)銷單上填報(bào)的日期自動(dòng)生成該報(bào)銷單的起止日期。使用最大值函數(shù)MAX和最小值函數(shù)MIN。制作單選按鈕。使用窗體工具條上的“選項(xiàng)按鈕”界面定制行號(hào)列標(biāo)，滾動(dòng)條，工作表標(biāo)簽的隱藏。選擇“工具”“選項(xiàng)”“視圖

16、”，將窗口選項(xiàng)中的行號(hào)列標(biāo)，水平滾動(dòng)條，垂直滾動(dòng)條，工作表標(biāo)簽選項(xiàng)去掉。案例07匯總報(bào)表對(duì)于分布在同一文件中的表格匯總：首先創(chuàng)建格式完全一致的匯總表的表結(jié)構(gòu)在匯總表需要匯總的項(xiàng)目上輸入：=sum()將光標(biāo)放入括號(hào)，用鼠標(biāo)選擇第一張工作表，按下shift鍵，再選取最后一張工作表，然后選擇需要匯總的單元格，回車。將該公式復(fù)制到其他需要匯總的單元格即可實(shí)現(xiàn)整張表的匯總。對(duì)于分布在不同文件中的表格匯總：首先創(chuàng)建格式完全一致的匯總表的表結(jié)構(gòu)將光標(biāo)置于起始報(bào)表項(xiàng)目上選擇【數(shù)據(jù)】【合并計(jì)算】“引用位置”選擇各個(gè)需要被合并的報(bào)表，點(diǎn)擊“添加”，直至所有需要被合并的報(bào)表添加完成。需要?jiǎng)h除一張表格時(shí)，只需要在合并

17、計(jì)算界面上選中該表格，點(diǎn)擊刪除按鈕。當(dāng)子表格數(shù)據(jù)發(fā)生變化需要更新匯總表數(shù)據(jù)時(shí)，只需執(zhí)行：數(shù)據(jù)-合并計(jì)算，進(jìn)入合并計(jì)算界面后直接點(diǎn)擊確定退出該界面即可完成刷新數(shù)據(jù)的操作。案例08報(bào)表保護(hù)單元格區(qū)域的保護(hù)。首先取消不需要保護(hù)的單元格區(qū)域的鎖定狀態(tài)。右鍵“設(shè)置單元格格式”，在“保護(hù)”標(biāo)簽上將鎖定選項(xiàng)取消。如果需要隱藏單元格公式內(nèi)容，可以將隱藏選項(xiàng)選中。選擇“工具”“保護(hù)”“保護(hù)工作表”，輸入保護(hù)密碼。隱藏公式右鍵菜單設(shè)置單元格格式保護(hù)選中隱藏選項(xiàng)，然后執(zhí)行對(duì)工作表的保護(hù)隱藏工作表：隱藏工作表后需要保護(hù)工作簿。案例09按鈕的制作選擇“視圖”“工具欄”“窗體”，調(diào)出窗體工具條。選擇工具條上的命令按鈕。繪

18、制出按鈕的基本形狀后，自動(dòng)跳出“指定宏”對(duì)話框，點(diǎn)擊“新建”按鈕，出現(xiàn)VBA編程界面。在Sub和EndSub之間輸入：sheets(“要跳轉(zhuǎn)的工作表名稱”).select，關(guān)閉該窗口。選中該按鈕，右鍵菜單“編輯文字”，可以修改按鈕名稱。案例10財(cái)務(wù)報(bào)表利用excel創(chuàng)建財(cái)務(wù)報(bào)表，要注意報(bào)表之間的勾稽關(guān)系。一方面是出于高效率管理報(bào)表的考慮，另一方面，可以幫助我們分析報(bào)表中的數(shù)據(jù)，并快速檢查出報(bào)表中的可能錯(cuò)誤。為了能夠自動(dòng)維護(hù)報(bào)表間的數(shù)據(jù)關(guān)系，我們經(jīng)常需要使用單元格鏈接，將所有的表鏈接起來(lái)。注意鏈接方式請(qǐng)使用并聯(lián)而非串聯(lián)。串聯(lián)導(dǎo)致鏈接關(guān)系更加復(fù)雜且難以維護(hù)，而并聯(lián)的方式將只有一個(gè)統(tǒng)一的數(shù)據(jù)源，鏈接

19、關(guān)系簡(jiǎn)單不容易出錯(cuò)，而且易于管理。比如損益表，資產(chǎn)負(fù)債表，現(xiàn)金流量表，需要使用公式和鏈接使得這些表鏈接起來(lái)，該鏈接關(guān)系能夠自動(dòng)維護(hù)和更新多表之間的勾稽關(guān)系，時(shí)刻能夠保證報(bào)表數(shù)據(jù)之間的勾稽的正確性。（具體公式參見(jiàn)電子文檔）。案例11表格的幾種類型數(shù)據(jù)表可以分為數(shù)據(jù)列表，二維表，多維表和帶有格式的報(bào)表等幾種類型。數(shù)據(jù)表類型操作目標(biāo)匯總方法是否需要重復(fù)制作是否與數(shù)據(jù)源有鏈接關(guān)系多字段數(shù)據(jù)列表記錄疊加并按表名區(qū)分導(dǎo)入外部數(shù)據(jù)+SQL語(yǔ)句不需要有二維表多個(gè)二維表生成透視表透視表多維數(shù)據(jù)區(qū)域合并不需要有同一文件內(nèi)的報(bào)表數(shù)據(jù)匯總=sum(begin:end!B2)不需要有不同文件中的報(bào)表數(shù)據(jù)匯總合并計(jì)算不需

20、要有非標(biāo)準(zhǔn)數(shù)據(jù)表匯總建議修改原表的結(jié)構(gòu)或者僅作為終端報(bào)表，上層再建立一個(gè)數(shù)據(jù)源表作為收集數(shù)據(jù)使用需要無(wú)案例12數(shù)據(jù)透視表應(yīng)用數(shù)據(jù)透視表進(jìn)行各類數(shù)據(jù)分析。選擇“數(shù)據(jù)”“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”，進(jìn)入透視表設(shè)置向?qū)?。點(diǎn)擊“下一步”，選擇正確的數(shù)據(jù)范圍。點(diǎn)擊“下一步”，選擇“布局”按鈕。在如下圖界面上，將右側(cè)的字段拖入左側(cè)相應(yīng)區(qū)域內(nèi)。形成如下圖的布局，點(diǎn)擊確定按鈕。生成如下的透視表后，將鼠標(biāo)懸停在想要移動(dòng)的字段上，鼠標(biāo)左鍵按下，將字段拖放到其他區(qū)域，生成自己需要的數(shù)據(jù)顯示。數(shù)據(jù)百分比顯示：右鍵菜單字段設(shè)置選項(xiàng)，將數(shù)據(jù)顯示方式改為“占同列數(shù)據(jù)總和的百分比”同時(shí)顯示數(shù)據(jù)與百分比：在【布局】里將銷售收入兩

21、次拖入數(shù)據(jù)區(qū)域?qū)⑵渲幸粋€(gè)銷售收入改為百分比顯示在報(bào)表項(xiàng)目上輸入新的名稱可以修改項(xiàng)目名稱將報(bào)表項(xiàng)目拖拽到列標(biāo)題位置，可以將數(shù)值和百分比改為按列排列篩選數(shù)據(jù)：點(diǎn)擊字段名稱后面的下拉菜單，可以對(duì)字段內(nèi)容進(jìn)行篩選。顯示明細(xì)數(shù)據(jù)：雙擊需要查看明細(xì)的數(shù)據(jù)，在新的工作表上將顯示構(gòu)成此匯總數(shù)據(jù)的所有明細(xì)數(shù)據(jù)。對(duì)數(shù)據(jù)進(jìn)行排序：把光標(biāo)放在行位置的報(bào)表項(xiàng)目上，【右鍵菜單】【字段設(shè)置】，【高級(jí)】按鈕，左側(cè)可以進(jìn)行排序設(shè)置。按照日期分組：將日期放入行區(qū)域，【右鍵菜單】【組及顯示明細(xì)數(shù)據(jù)】【組合】在【步長(zhǎng)】中選擇需要的分組標(biāo)準(zhǔn)按照數(shù)值分組：將銷售收入放入行區(qū)域，【右鍵菜單】【組及顯示明細(xì)數(shù)據(jù)】【組合】在【步長(zhǎng)】中選擇需要

22、的分組標(biāo)準(zhǔn)插入計(jì)算字段：在【名稱】后給計(jì)算字段定義名稱在【公式】后輸入計(jì)算字段的計(jì)算公式，可以引用下面的字段自動(dòng)更新：【右鍵菜單】【表格選項(xiàng)】【打開時(shí)刷新】可以讓報(bào)表始終有更新后的數(shù)據(jù)顯示。透視圖可以單獨(dú)生成數(shù)據(jù)透視圖，也可以基于現(xiàn)有的透視表生成透視圖。如果已經(jīng)有現(xiàn)成的透視表，通過(guò)點(diǎn)擊數(shù)據(jù)透視表工具條上的圖表按鈕可以生成一個(gè)透視圖。通過(guò)拖動(dòng)透視圖上的各個(gè)字段到右側(cè)或者底部的位置，可以方便地改變圖表組織數(shù)據(jù)的方式。隱藏透視圖字段按鈕：把光標(biāo)懸停在任意字段上，右鍵菜單，選擇【隱藏?cái)?shù)據(jù)透視圖字段按鈕】，即可以將字段按鈕隱藏；再次顯示字段按鈕：點(diǎn)擊透視圖工具條上第一項(xiàng)，在出現(xiàn)的下拉菜單中選擇【隱藏?cái)?shù)據(jù)

23、透視圖字段按鈕】如果有些數(shù)據(jù)無(wú)法在數(shù)據(jù)透視表內(nèi)部分析，可以引用透視表的數(shù)據(jù)作為分析的基礎(chǔ)。制作試算平衡表點(diǎn)擊“數(shù)據(jù)”并拖拽到“匯總”隱藏?cái)?shù)據(jù)透視表中的匯總字段或單元格格式自定義功能格式設(shè)定為：紅色借貸不平衡;紅色金額借貸不平衡；藍(lán)色”借貸平衡”Ctrl+拖拽標(biāo)簽來(lái)復(fù)制工作表編制損益表創(chuàng)建損益表格式創(chuàng)建IS嵌套函數(shù)IS類函數(shù)，可以檢驗(yàn)數(shù)值的類型并根據(jù)參數(shù)取值返回TRUE或FALSE。函數(shù)如果為下面的內(nèi)容，則返回TRUEISBLANK值為空白單元格。ISERR值為任意錯(cuò)誤值（除去#N/A）。ISERROR值為任意錯(cuò)誤值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?

24、或#NULL!）。ISLOGICAL值為邏輯值。ISNA值為錯(cuò)誤值#N/A（值不存在）。ISNONTEXT值為不是文本的任意項(xiàng)（注意此函數(shù)在值為空白單元格時(shí)返回TRUE）。ISNUMBER值為數(shù)字。ISREF值為引用。ISTEXT值為文本。創(chuàng)建財(cái)務(wù)比率銷售毛利率=銷售毛利/銷售凈額毛利率大，表示經(jīng)營(yíng)能力強(qiáng)。銷售成本率=銷售成本/銷售凈額成本率低，表示經(jīng)營(yíng)能力好。凈利率率=凈利潤(rùn)/銷售凈額凈利潤(rùn)率越高，表示經(jīng)營(yíng)能力強(qiáng)。營(yíng)業(yè)比率=（銷售成本+營(yíng)業(yè)費(fèi)用）/銷售凈額，營(yíng)業(yè)比率越低，表示經(jīng)營(yíng)能力越強(qiáng)營(yíng)業(yè)費(fèi)用率=銷售費(fèi)用/銷售凈額營(yíng)業(yè)費(fèi)用越低，表示經(jīng)營(yíng)績(jī)效越好。用MAX、MIN函數(shù)顯示制表日期MAX，返回

25、一組值中的最大值MIN，返回一組值中的最小值案例13多重合并計(jì)算數(shù)據(jù)如下圖格式的即為二維表：現(xiàn)在我們需要將數(shù)個(gè)格式相同的二維表匯總為一張表格，且可以區(qū)分不同表格屬性進(jìn)行分析，比如三張表分別為北京，上海，深圳分公司的表格。我們使用的方法是利用透視表多重合并計(jì)算數(shù)據(jù)區(qū)域的功能。選擇數(shù)據(jù)-數(shù)據(jù)透視表和數(shù)據(jù)透視圖選擇第3個(gè)數(shù)據(jù)源類型：多重合并計(jì)算數(shù)據(jù)區(qū)域，點(diǎn)擊下一步按鈕：在出現(xiàn)的界面上選擇：自定義字段選擇需要被合并的表格區(qū)域，點(diǎn)擊添加將其添加到所有區(qū)域；將頁(yè)字段數(shù)據(jù)改為1，在項(xiàng)標(biāo)志處輸入該表格的標(biāo)志，使用相同的方法將其他需要合并的表格全部添加。透視表生成后如下圖所示，已經(jīng)將多個(gè)表格合并成一個(gè)：雙擊字段

26、名，可以對(duì)字段名進(jìn)行修改：案例14回歸與預(yù)測(cè)回歸分析表明事物之間相互影響的關(guān)系，主要用于分析單個(gè)因變量如何受一個(gè)或多個(gè)自變量影響的。比如某地空調(diào)的銷量與氣溫的變化之間的關(guān)系?？梢杂媒y(tǒng)計(jì)獲得的歷史數(shù)據(jù)，對(duì)未來(lái)的數(shù)據(jù)進(jìn)行預(yù)測(cè)。可以利用它幫助經(jīng)營(yíng)，財(cái)務(wù)，銷售，營(yíng)銷等方面的決策。趨勢(shì)線預(yù)測(cè)根據(jù)歷史數(shù)據(jù)制作出折線圖選中圖表中的折線，右鍵菜單添加趨勢(shì)線選擇預(yù)測(cè)類型（可以先選擇默認(rèn)的線性）切換到選項(xiàng)標(biāo)簽，根據(jù)需要預(yù)測(cè)的期間數(shù)，在“前推”處輸入預(yù)測(cè)期間個(gè)數(shù)，并選中“顯示公式”和“顯示R平方值”選項(xiàng)根據(jù)出現(xiàn)的回歸系數(shù)（R平方）判斷預(yù)測(cè)類型是否適合，R平方越接近1越好（多項(xiàng)式除外，需要考慮業(yè)務(wù)數(shù)據(jù)是否存在較大的波

27、動(dòng)，如果沒(méi)有則不適用多項(xiàng)式分析類型）根據(jù)回歸方程求預(yù)測(cè)值移動(dòng)平均沒(méi)有R平方，不適用此分析方法。Correl函數(shù)判斷相關(guān)性。相關(guān)系數(shù)在0.8以上為強(qiáng)相關(guān)，0.6以上為弱相關(guān)。案例15-16數(shù)據(jù)分析與圖表分析日常工作中主要用到的數(shù)據(jù)分析有：構(gòu)成：局部與整體比例關(guān)系。序列：在一個(gè)時(shí)間序列內(nèi)進(jìn)行的比較與分析。差異：預(yù)算與實(shí)際數(shù)的版本差異等。增長(zhǎng)：增長(zhǎng)率與增速，進(jìn)行趨勢(shì)預(yù)測(cè)。勾稽：數(shù)據(jù)之間固有的相互聯(lián)系的屬性，利用此屬性進(jìn)行數(shù)據(jù)合理性的判斷。圖表用于形象地展示數(shù)據(jù)。利用圖表可以更好地對(duì)業(yè)務(wù)數(shù)據(jù)進(jìn)行分析。差異分析：利用上面的表格制作面積圖，數(shù)據(jù)區(qū)域選擇C25:D31選中B公司數(shù)據(jù)系列，右鍵菜單“數(shù)據(jù)系列格

28、式”將其內(nèi)部（填充色）和邊框色都改為“無(wú)”圖表上將只顯示差異部分，如下圖：雙坐標(biāo)圖表：利用下面的數(shù)據(jù)制作雙坐標(biāo)圖表：選擇“插入”-“圖表”，進(jìn)入圖表向?qū)Вx擇柱形圖，第一種子圖表類型簇狀柱形圖。選擇“視圖”-“工具欄”-“圖表”，調(diào)出圖表工具條：點(diǎn)開圖表工具條上下拉菜單，選擇系列“完成率”點(diǎn)擊圖表工具條上“數(shù)據(jù)系列格式”按鈕：在數(shù)據(jù)系列格式界面上，切換到坐標(biāo)軸標(biāo)簽，將系列繪制在由“主坐標(biāo)軸”改為“次坐標(biāo)軸”再次選中系列“完成率”，右鍵菜單選擇“圖表類型”選擇折線圖，點(diǎn)確定：雙坐標(biāo)圖表制作完成。氣泡圖：利用下表制作氣泡圖：在圖表向?qū)е羞x擇“氣泡圖”因?yàn)闅馀輬D沒(méi)有分類軸（其橫坐標(biāo)為序列號(hào)），我們需

29、要添加數(shù)據(jù)標(biāo)識(shí)，點(diǎn)擊下一步，切換到數(shù)據(jù)標(biāo)志標(biāo)簽，將系列名稱打勾：縱坐標(biāo)表示%Growth,氣泡大小表示Cost:單元格嵌入式圖表：利用下表制作如圖中所示的嵌入式圖表在單元格中敲入如下公式：=REPT(|,B2/400)其中，除數(shù)的值由源數(shù)據(jù)的大小決定，源數(shù)據(jù)大，除數(shù)應(yīng)相應(yīng)變大，其意義在于縮減豎線的數(shù)量，使得單元格可以容納由一系列豎線構(gòu)成的圖表。復(fù)合餅圖：當(dāng)數(shù)據(jù)系列內(nèi)值的大小差異較大時(shí)，可以制作復(fù)合餅圖以便數(shù)據(jù)顯示更為清楚，如下圖所示：選擇餅圖中的復(fù)合餅圖子圖表類型：點(diǎn)擊下一步，切換到圖例標(biāo)簽，將顯示圖例的勾去掉：切換到數(shù)據(jù)標(biāo)志標(biāo)簽，把類別名稱和百分比打勾：選中數(shù)據(jù)系列，右鍵菜單，選擇數(shù)據(jù)系列格

30、式：切換到選項(xiàng)標(biāo)簽，將第二繪圖區(qū)包含最后的值改為：4復(fù)合餅圖制作完成。圖表組合：一個(gè)圖表中的不同數(shù)據(jù)系列可以采用不同的圖表類型顯示，如下圖：首先制作一個(gè)柱形圖：在圖表工具條上選擇數(shù)據(jù)系列Compensation:在圖表工具條上選擇數(shù)據(jù)系列格式按鈕：在數(shù)據(jù)系列格式界面上，切換到坐標(biāo)軸標(biāo)簽，將主坐標(biāo)軸改為次坐標(biāo)軸：右鍵點(diǎn)擊Compensation,選擇圖表類型：將其圖表類型改為折線圖；用同樣的方式操作Production系列，將其圖表類型改為面積圖；完成圖表組合的制作。下拉菜單式圖表：寫入如下的公式：index函數(shù)查找出的值由A9單元格來(lái)指定。制作三維餅圖，其數(shù)據(jù)區(qū)域?yàn)锳3:I3,A9:I9,需要

31、手工選取。點(diǎn)擊“視圖”-“工具欄”-“窗體”，調(diào)出窗體工具條：在窗體工具條上選擇組合框，在餅圖上拖放出一個(gè)組合框：右鍵選中組合框，在菜單中選擇“設(shè)置控件格式”：在控制標(biāo)簽中做如下設(shè)置：其中單元格鏈接為存儲(chǔ)控件選中項(xiàng)目序號(hào)的單元格。完成下拉菜單式圖表的制作：帶有滾動(dòng)條的圖表：該圖表中可以隨著點(diǎn)擊滾動(dòng)條的動(dòng)作而動(dòng)態(tài)翻看源數(shù)據(jù)中的大量數(shù)據(jù)。定義兩個(gè)動(dòng)態(tài)引用的名稱：Period:=offset(7.動(dòng)態(tài)圖表!$A$1,7.動(dòng)態(tài)圖表!$D$1,0,10,1)Data:=offset(7.動(dòng)態(tài)圖表!$B$1,7.動(dòng)態(tài)圖表!$D$1,0,10,1)制作折線圖，選擇系列標(biāo)簽，刪除period系列：將“值”和“

32、分類（X）軸標(biāo)志”設(shè)置為如下圖內(nèi)容：調(diào)出窗體工具條，在圖表外繪制滾動(dòng)條，右鍵選中滾動(dòng)條，選擇設(shè)置控件格式：在單元格鏈接中設(shè)置為D1:甘特圖如果想制作如下圖的甘特圖，需要以下4列數(shù)據(jù)：首先制作堆積條形圖：點(diǎn)擊下一步，切換到系列標(biāo)簽，刪除結(jié)束日期系列：生成的條形圖如下圖所示：下面轉(zhuǎn)換縱坐標(biāo)的項(xiàng)目排列次序：雙擊縱坐標(biāo)，切換到刻度，將分類次序反轉(zhuǎn)和數(shù)據(jù)軸交叉于最大分類選項(xiàng)打勾。此時(shí)縱坐標(biāo)次序已經(jīng)反轉(zhuǎn)雙擊開始日期數(shù)據(jù)系列，在數(shù)據(jù)系列格式對(duì)話框中將邊框和內(nèi)部都改選為無(wú)，此時(shí)開始日期數(shù)據(jù)系列隱藏。此時(shí)需要將橫坐標(biāo)開始日期改為真正的項(xiàng)目開始日期：在excel中，每一個(gè)日期都對(duì)應(yīng)一個(gè)數(shù)值，選中B2,查看2008

33、-7-1對(duì)應(yīng)數(shù)值為39630雙擊橫坐標(biāo)，切換到刻度，將最小值改為39630，同樣將橫坐標(biāo)最大值改為真正的項(xiàng)目結(jié)束日期。甘特圖制作完成。案例17動(dòng)態(tài)圖表選中北京，深圳，上海以下的區(qū)域，分別定義北京，深圳，上海的名稱選擇視圖-工具欄-窗體，選擇選項(xiàng)按鈕，畫出如下三個(gè)選項(xiàng)按鈕：右鍵選中選項(xiàng)按鈕，在右鍵菜單中選擇設(shè)置控件格式，在跳出的對(duì)話框中選擇控制標(biāo)簽，在單元格鏈接中選擇A16：在A17單元格中輸入公式：=choose(a16,”北京”,”上?！?”深圳”)，為A17定義名稱：choose在A2,A3,A4單元格中輸入以下內(nèi)容：在B16單元中寫入以下公式，并復(fù)制到該行1-12月的單元格中：=VLOO

34、KUP(A3,INDIRECT(choose),COLUMN()-1,0)依據(jù)此數(shù)據(jù)表制作雙曲線圖：案例18比率分析利用透視表靈活的特性，將兩個(gè)結(jié)構(gòu)完全相同的透視表相除，即得到可以進(jìn)行任意兩個(gè)因素間的比率分析圖表。案例19邏輯判斷IF(條件判斷，如果條件滿足則返回的結(jié)果，如果條件不滿足則返回的結(jié)果)根據(jù)工齡計(jì)算員工年假。年假規(guī)則：規(guī)定：公司工齡小于1年的，享受10天年假；大于1年小于10年的，工齡每增加一年，年假增加1天；增長(zhǎng)到20天不再增加。使用IF函數(shù)嵌套實(shí)現(xiàn)。單元格F3為工齡。=IF(F31,10,IF(F3100000,(K3-1600)*0.45-15375,IF(K3-1600)8

35、0000,(K3-1600)*0.4-10375,IF(K3-1600)60000,(K3-1600)*0.35-6375,IF(K3-1600)40000,(K3-1600)*0.3-3375,IF(K3-1600)20000,(K3-1600)*0.25-1375,IF(K3-1600)5000,(K3-1600)*0.2-375,IF(K3-1600)2000,(K3-1600)*0.15-125,IF(K3-1600)500,(K3-1600)*0.1-25,(K3-1600)*0.05)自動(dòng)計(jì)算加班費(fèi)=IF(ISERROR(VLOOKUP(D2,$L$2:$L$12,1,FALSE)

36、,IF(OR(G2=6,G2=7),周末加班,工作日加班),節(jié)假日加班)自動(dòng)計(jì)算請(qǐng)假扣除=IF(OR(D5=病假,D5=事假),E5*1,IF(D5=曠工,E5*3,0)隱藏錯(cuò)誤提示=IF(ISERROR(表達(dá)式),表達(dá)式)案例20計(jì)數(shù)與求和條件計(jì)數(shù)countif(range，criteria)countif(A1:A100，”8”)條件求和如果滿足某個(gè)條件，就對(duì)該記錄里的指定數(shù)值字段求和。在第一個(gè)參數(shù)所在的區(qū)域里面查找第二個(gè)參數(shù)指定的值，找到后對(duì)第三個(gè)參數(shù)指定的字段進(jìn)行求和。sumif(range,criteria,sum_range)sumif(A1:A100,“?海*”,E1:E100)

37、對(duì)A列中第4個(gè)字為海的E列的值求和模糊條件求和sumif(a1:a100,“*”&”?！?”*”,e1:e100)對(duì)A列中包含“?！弊值腅列的值求和sumif對(duì)多個(gè)條件進(jìn)行求和=SUM(SUMIF(F:F,F2:F3,E:E)=SUM(SUMIF(F:F,陳露,程靜,E:E)criteria為常量，普通公式；criteria為單元格引用，需要使用數(shù)組公式。sum與數(shù)組公式聯(lián)手計(jì)數(shù)=sum(a1:a1001000)*(a1:a1005000)sum與數(shù)組公式聯(lián)手求和=sum(條件1)*(條件2)*.*求和區(qū)域)sumproduct的使用返回相應(yīng)的數(shù)組或區(qū)域的乘積的和?？梢允褂胹umproduct

38、替代sum數(shù)組公式的使用用于多條件求和。sumproduct(條件1)*(條件2)*)用于計(jì)數(shù)sumproduct(條件1)*(條件2)*求和區(qū)域)用于求和案例21模擬運(yùn)算表（略）案例22單變量求解如果希望利潤(rùn)達(dá)到200萬(wàn)，則需要相應(yīng)調(diào)整的單價(jià)、直接成本、固定成本、銷售量為多少？可變單元格：要求解的值所在的單元格目標(biāo)單元格：關(guān)系式表達(dá)所在的單元格目標(biāo)值：關(guān)系式的值單變量求解的原理：根據(jù)在單元格中描述的一元方程求解方程中的未知數(shù)。使用單變量求解的前提：需要在單元格中將一元方程的方程式描述清楚。如果是函數(shù)關(guān)系，則需要在單元格中引用該函數(shù)。案例23規(guī)劃求解步驟：構(gòu)建已知條件的關(guān)系模型，然后運(yùn)行規(guī)劃求

39、解工具。構(gòu)建模型的過(guò)程實(shí)際上是利用Excel單元格引用的方式將條件描述清楚的過(guò)程。在規(guī)劃求解對(duì)話框中，進(jìn)行相關(guān)設(shè)置。目標(biāo)單元格為求解后達(dá)到的目標(biāo)?？勺儐卧駷橐蠼獾膯卧?。在約束中添加已知的其他條件。規(guī)劃求解可以求解多元方程，可以解決求極值的問(wèn)題，可以替代計(jì)算較為復(fù)雜的計(jì)算過(guò)程。案例24自定義函數(shù)宏的安全性級(jí)別調(diào)整點(diǎn)擊：工具-宏-安全性如果定義了宏或者自定義函數(shù)并且想使用它們，那么需要將宏的安全性級(jí)別調(diào)整為中或者低。自定義函數(shù)：進(jìn)入VB編輯器選中左側(cè)工程管理器中的當(dāng)前工作簿，查看其下項(xiàng)目中是否存在“模塊”。如果不存在，點(diǎn)擊【插入】菜單，選擇“模塊”；如果存在，則略過(guò)此步驟。雙擊模塊下的某一模

40、塊，右側(cè)將彈出該模塊的代碼窗口，我們將在此代碼窗口中編寫自定義函數(shù)。在右側(cè)代碼窗口輸入以下語(yǔ)句：Functionvaluation(a,b,c,d)valuation=(a+b)*0.6+(c+d)*0.4EndFunction其中，valuation是該函數(shù)的函數(shù)名，a,b,c,d是該自定義函數(shù)的參數(shù)，function與endfunction是自定義函數(shù)的起始與結(jié)束語(yǔ)句。帶有判斷的自定義函數(shù)：Functionlimitation(job)Ifjob=“總經(jīng)理”O(jiān)rjob=“市場(chǎng)銷售”Thenlimitation=1500ElseIfjob=“客戶服務(wù)”O(jiān)rjob=“副總經(jīng)理”Thenlimi

41、tation=1200ElseIfjob=“職能管理”O(jiān)rjob=“業(yè)務(wù)總監(jiān)”Thenlimitation=800ElseIfjob=“產(chǎn)品”O(jiān)rjob=“采購(gòu)”Thenlimitation=600ElseIfjob=“研發(fā)技術(shù)”O(jiān)rjob=“生產(chǎn)運(yùn)作”Thenlimitation=400Else:limitation=100EndIfEndFunction附：VBA簡(jiǎn)介VBA簡(jiǎn)介：VBA是VisualbasicforApplication的縮寫。是用來(lái)開發(fā)應(yīng)用程序的Visualbasic語(yǔ)言，除了它無(wú)法象Visualbasic般獨(dú)立存在外，其它的如程序的編輯方法，對(duì)象的使用等，都與Visua

42、lbasic相同，因此如果學(xué)習(xí)過(guò)Visualbasic語(yǔ)言，入門VBA將是一件很容易的事。VBE是VisualbasicEditor的縮寫,是用來(lái)編寫VBA應(yīng)用程序的編輯器,它與VB編器有什么區(qū)別呢?Visualbasic是用來(lái)開發(fā)完整程序的工具,必須使用相當(dāng)大的系統(tǒng)資源,但是在控制EXCEL或其他OFFICE辦公軟件時(shí),并不需要使用這么龐大的資源,因此Microsoft將Visualbasic編寫及執(zhí)行程序的功能附屬于OFFICE之下，使其可以使用較少的系統(tǒng)資源，達(dá)到編輯出應(yīng)用程序的目的。因此將提供這些功能的編輯器稱為VisualbasicEditor。EXCELVBA基本構(gòu)架對(duì)象、屬性、事

43、件、方法對(duì)象：所謂的對(duì)象，我們可以將其想象成日常生活中所用的各類物品比如車子桌子、椅子等以車子的組成為例，車子是由輪子、車座、車燈、外殼等組合而成的一個(gè)大對(duì)象而這些具有單一功能的對(duì)象又可以分為更小的對(duì)象如：螺絲、電線等。就EXCEL而言其對(duì)象就是工作薄、工作表、圖表工作表或其他更細(xì)微的窗體組件。屬性：所謂屬性就是對(duì)象所屬的特征，例如當(dāng)年到一個(gè)蘋果時(shí)，可以馬上知道它的顏色形狀及大小這些都可以用來(lái)形容對(duì)象的形容詞也就是對(duì)象的屬性，比如工作表的名稱就是工作表的一個(gè)屬性。取得屬性值的語(yǔ)法：變量=對(duì)象.屬性比如我們要取得當(dāng)前工作表的名子和工作表的數(shù)量AAA=ABBB=Activesheet.count設(shè)

44、定屬性的語(yǔ)法:對(duì)象.屬性=屬性值例:sheets(1).name=我的工作薄1設(shè)置第一個(gè)工作表的名稱是我的工作薄1事件:所謂事件,就是發(fā)生在對(duì)象上的事情例如在按按上單擊鼠標(biāo)這個(gè)動(dòng)作就是發(fā)生于按紐上的事件，工作表的增加，單元格的修改都是事件。下面的圖示中右邊的下拉框中都是WORKSHEET（工作表）的事件事件有什么用呢？程序我們平常并不希望它時(shí)時(shí)運(yùn)行，所以我們需要在使用到它時(shí)再讓它運(yùn)行，比如：你可以設(shè)置一個(gè)按紐用單擊這個(gè)事件去觸發(fā)程序的運(yùn)行，當(dāng)我們單擊按紐時(shí)就運(yùn)行，不單擊就不運(yùn)行。再如，我們有時(shí)候需要在改變某個(gè)工作表中的單元格時(shí)運(yùn)行某個(gè)程序這時(shí)就可以用Worksheet_Change這個(gè)事件。方

45、法:控制對(duì)象的動(dòng)作或功能稱為方法,簡(jiǎn)單的說(shuō)就是為了達(dá)到某種效果而下達(dá)的命令:比如:對(duì)工作表這個(gè)對(duì)象進(jìn)行新建或刪除的功能.而新建及刪除功能即為工作表這個(gè)對(duì)象所提供的方法.使用方法的程序語(yǔ)法.對(duì)象.方法例:SHEET1.DELETE上例中SHEET1的部分為對(duì)象,而.后方的即為對(duì)象所提供的方法,至于對(duì)象提供的方法有哪些,并不需要特意去記憶它們,當(dāng)輸入了對(duì)象名稱然后輸入.時(shí)，系統(tǒng)就會(huì)顯示該對(duì)象提供的方法菜單以便從菜單中選取適用的方法.舉例：點(diǎn)擊“新建”并加入Sheets(sheet1).Range(a1)=100Sheets(sheet1).Cells(2,1)=200DimiAsIntegerDI

46、M變量名稱AS變量類型Fori=1To20Sheets(sheet1).Cells(i,1)=iNext添加：WithApplication.WorksheetFunctionRange(a21).Value=.Sum(Range(a1:a20)注意SUM前面要有個(gè)點(diǎn)才行Range(a22).Value=.Average(Range(a1:a20)EndWith在VBA不直接支持在EXCEL工作表中的一些函數(shù)，如果要調(diào)用就必須加上Application.WorksheetFunction宏：許多VBA高手都是從錄制宏入門VBA的，宏的功能就是將所操作的步驟記錄下來(lái),以便以后的任何時(shí)候只要使用宏

47、,就可以重復(fù)執(zhí)行操作過(guò)的步驟。有時(shí)在編程序時(shí)，忘記了EXCEL某些基本功能的編寫，錄制宏就是一個(gè)很好的方法，比如你忘記了不連續(xù)單元格的表示方法，你就可以在工作表中把你選取不連續(xù)單元格的過(guò)程錄制下來(lái)，代碼自然就出來(lái)了。錄制宏的操作：“工具”“宏”點(diǎn)擊錄制宏按紐在彈出的錄制新宏對(duì)話框中輸入宏名和快捷鍵（可不填）進(jìn)行事選設(shè)定的操作停止錄制（其實(shí)這個(gè)和平時(shí)生活中錄音或錄像一樣，開始錄制-停止），補(bǔ)充一點(diǎn)，就是如果以后想用按紐來(lái)執(zhí)行宏，可以直接在彈出的指定宏對(duì)話框中點(diǎn)擊錄制宏，其余步驟同上。案例25資產(chǎn)折舊直線折舊法計(jì)算資產(chǎn)折舊=SLN（Cost,Satvage,Life）固定余額遞減折舊法計(jì)算資產(chǎn)折舊

48、=DB（Cost,Satvage,Life,Year）雙倍余額遞減折舊法計(jì)算資產(chǎn)折舊=DDB（Cost,Satvage,Life,Year,2）可變余額遞減折舊法計(jì)算資產(chǎn)折舊=VDB（Cost,Satvage,Life,StartPeriod,EndPeriod,Factor,NoSwitch）年數(shù)總和折舊法計(jì)算資產(chǎn)折舊=SYD（Cost,Satvage,Life,Year）附1：投資決策分析進(jìn)行投資分析需要為提供一系列現(xiàn)金流量的值。凈現(xiàn)值。內(nèi)部報(bào)酬率。凈現(xiàn)值和內(nèi)部報(bào)酬率是關(guān)系緊密的一對(duì)函數(shù)。當(dāng)內(nèi)部報(bào)酬率資金成本時(shí)，凈現(xiàn)值。附2：整合數(shù)據(jù)表我們經(jīng)常需要將幾個(gè)表格中的數(shù)據(jù)整合成一張表，如下圖所示

49、，將5個(gè)月的數(shù)據(jù)（目前分布在5張表格上）整合到一個(gè)總表上：我們使用的方法是利用office的查詢工具query進(jìn)行的，這樣做的優(yōu)點(diǎn)在于今后對(duì)于新數(shù)據(jù)的更新可以自動(dòng)化刷新，而不需要重復(fù)的進(jìn)行整合操作（如復(fù)制粘貼）。office的查詢工具query在典型安裝office時(shí)是不會(huì)被安裝的，我們需要首先檢查我們的office是否已經(jīng)安裝了query，方法如下：打開Excel，點(diǎn)擊【數(shù)據(jù)】菜單中【導(dǎo)入外部數(shù)據(jù)】【新建數(shù)據(jù)庫(kù)查詢】，若系統(tǒng)提示安裝，則說(shuō)明該功能未被安裝，此時(shí)可能需要在光驅(qū)中插入Office安裝光盤完成該功能的安裝。在光驅(qū)中插入安裝光盤后，安裝過(guò)程中選擇自定義安裝；點(diǎn)開Excel前面的加號(hào)，

50、選中要安裝的功能前面的下拉菜單，選擇【從本機(jī)運(yùn)行】；點(diǎn)開【Office工具】前面的加號(hào)，選中【MicrosoftQuery】的下拉菜單，選擇【從本機(jī)運(yùn)行】，然后執(zhí)行余下的安裝過(guò)程即可。下面開始整合工作：選擇excelfiles*:選擇要導(dǎo)入數(shù)據(jù)的excel文件：出現(xiàn)選擇數(shù)據(jù)表的界面：如果以上界面提示錯(cuò)誤，點(diǎn)擊以上界面的“選項(xiàng)”按鈕，出現(xiàn)以下界面，將系統(tǒng)表打勾即可：任選一個(gè)字段到右側(cè)，點(diǎn)下一步按鈕：直到以下界面出現(xiàn)，選擇第二項(xiàng)，點(diǎn)完成按鈕：此時(shí)出現(xiàn)query程序界面，點(diǎn)擊工具條上SQL按鈕：出現(xiàn)以下界面：在界面中將SQL語(yǔ)句改為以下：出現(xiàn)以下提示，點(diǎn)確定按鈕：出現(xiàn)數(shù)據(jù)查詢結(jié)果，選文件菜單-將數(shù)據(jù)

51、返回EXCEL:選擇在新工作表中存儲(chǔ)數(shù)據(jù)：幾張數(shù)據(jù)表被成功地整合在一起。該查詢數(shù)據(jù)表與數(shù)據(jù)源保持著動(dòng)態(tài)鏈接關(guān)系，可以隨時(shí)刷新數(shù)據(jù)，不需要重復(fù)操作。右鍵菜單里選擇刷新數(shù)據(jù)即可對(duì)數(shù)據(jù)進(jìn)行刷新。附3：對(duì)于數(shù)據(jù)庫(kù)的訪問(wèn)鏈接方法ODBC的介紹存儲(chǔ)數(shù)據(jù)并不是Excel的特長(zhǎng)，Excel設(shè)計(jì)和優(yōu)化的目的是綜合、分析及顯示數(shù)據(jù)。它僅有一些基本的數(shù)據(jù)庫(kù)功能，當(dāng)用戶覺(jué)得簡(jiǎn)便的重用性大于功能時(shí)，就應(yīng)該使用它們。Excel中有一些方法，可以將這些工具和真實(shí)數(shù)據(jù)庫(kù)管理系統(tǒng)緊密結(jié)合在一起，利用這些方法，用戶可以創(chuàng)建一些系統(tǒng)，在數(shù)據(jù)庫(kù)管理系統(tǒng)的強(qiáng)大功能和工作表的功能中達(dá)到一種平衡。ODBC是開放式數(shù)據(jù)庫(kù)互連（OpenDat

52、abaseConnectivity）的縮寫。它是一種標(biāo)準(zhǔn)，許多數(shù)據(jù)庫(kù)程序和數(shù)據(jù)庫(kù)管理系統(tǒng)都采用這種標(biāo)準(zhǔn)。如果用戶有一個(gè)數(shù)據(jù)源，如與ODBC兼容的Access數(shù)據(jù)庫(kù)，那么就可以將數(shù)據(jù)輕松的導(dǎo)入到Excel中。不是所有的數(shù)據(jù)庫(kù)都有同樣的結(jié)構(gòu)。為了確定這些數(shù)據(jù)庫(kù)是否可以和其他應(yīng)用軟件相連，ODBC軟件生產(chǎn)商提供了驅(qū)動(dòng)程序，在安裝MicrosoftOffice時(shí)，按轉(zhuǎn)程序會(huì)自動(dòng)地為Access、Paradox、dBASE以及SQLServer等安裝幾個(gè)驅(qū)動(dòng)程序。在將數(shù)據(jù)從數(shù)據(jù)庫(kù)導(dǎo)入到Excel中之前，用戶需要確認(rèn)自己想使用的數(shù)據(jù)庫(kù)類型。這樣，Excel才能知道使用何種驅(qū)動(dòng)程序和數(shù)據(jù)庫(kù)相連。在確定想要使

53、用的數(shù)據(jù)源類型后，還必須提供一個(gè)查詢（query）。查詢是用標(biāo)準(zhǔn)的結(jié)構(gòu)查詢語(yǔ)言寫出的一系列指令，這種查詢語(yǔ)言一般形成SQL.那些指令提供了一些信息，如使用數(shù)據(jù)庫(kù)中的哪些表，需要表中哪些字段的數(shù)據(jù)，以及在選擇要檢索記錄的過(guò)程中是否運(yùn)用某些標(biāo)準(zhǔn)等。MicrosoftOffice還有一個(gè)叫做MicrosoftQuery的程序。從數(shù)據(jù)庫(kù)導(dǎo)入數(shù)據(jù)不久，MicrosoftQuery就開始運(yùn)行了。它將幫助用戶設(shè)計(jì)查詢，并從數(shù)據(jù)庫(kù)中檢索所需要的記錄。從數(shù)據(jù)庫(kù)導(dǎo)入數(shù)據(jù)是一個(gè)能夠兩步完成的過(guò)程：首先確定數(shù)據(jù)源，然后再創(chuàng)建查詢命令。既可以通過(guò)數(shù)據(jù)透視表進(jìn)行外部數(shù)據(jù)源鏈接，也可以通過(guò)“數(shù)據(jù)”“導(dǎo)入外部數(shù)據(jù)”進(jìn)行外部數(shù)

54、據(jù)源鏈接，這里用數(shù)據(jù)透視表連接，該方法的優(yōu)點(diǎn)在于連接好數(shù)據(jù)后可以直接利用透視表進(jìn)行分析。在透視表向?qū)ы?yè)面上選擇“外部數(shù)據(jù)源”。點(diǎn)擊“獲取數(shù)據(jù)”。選擇“新數(shù)據(jù)源”，按確定按鈕。輸入數(shù)據(jù)源名稱，選擇相應(yīng)的數(shù)據(jù)庫(kù)驅(qū)動(dòng)，點(diǎn)擊“連接”。在下圖界面上點(diǎn)擊“選擇”按鈕。在下圖界面上去相應(yīng)路徑選擇數(shù)據(jù)庫(kù)，在左側(cè)選中數(shù)據(jù)庫(kù)后點(diǎn)擊確定按鈕?；氐较聢D界面后選擇默認(rèn)表。新數(shù)據(jù)源建好后出現(xiàn)在數(shù)據(jù)源列表中，選中后確定。在數(shù)據(jù)表中選擇需要的字段?？梢岳媚匙侄螌?duì)數(shù)據(jù)進(jìn)行篩選。點(diǎn)擊完成，進(jìn)入透視表設(shè)置界面。對(duì)透視表進(jìn)行布局，以下步驟同透視表操作。PowerPoint在管理中的應(yīng)用第一部分：基礎(chǔ)概念演示文稿構(gòu)成要素文本圖示表格

55、色彩明確演示文稿的用途演講目的文檔目的演示文稿的分類展示型：演講目的，以圖示為主內(nèi)容型：文檔目的，以文本，表格為主展示型演示文稿制作原則：信息可視化提綱挈領(lǐng)的文本圖示替代文本圖表替代數(shù)據(jù)的羅列信息以一種直觀的方式展現(xiàn)，可以更好地傳遞演講內(nèi)容。要求一定程度的視覺(jué)效果。賞心悅目的畫面有利于人們?cè)鰪?qiáng)對(duì)演示內(nèi)容的記憶，但是不要為了裝飾而裝飾，如此反而會(huì)分散觀眾的注意力。不追求內(nèi)容的全面，而是追求重點(diǎn)的突出，強(qiáng)化觀眾對(duì)于最主要內(nèi)容的關(guān)注。如果想詳細(xì)地全面地對(duì)主題進(jìn)行說(shuō)明，可以使用WORD或者EXCE。大段的文字演示的效果會(huì)很差，是演示文稿的忌諱。要想方設(shè)法把文字轉(zhuǎn)化為各種直觀的圖形來(lái)說(shuō)明和展示。展示型演

56、示文稿的特點(diǎn)：邏輯性可視化內(nèi)容型演示文稿制作原則框架布局必要的修飾第二部分：演示文稿構(gòu)成元素操作新建一個(gè)演示文稿。ctrl+M：插入新幻燈片頁(yè)面應(yīng)用版式。選擇“格式”“幻燈片版式”，調(diào)出版式工作區(qū)，選擇一種適合的版式。版式：指幻燈片內(nèi)容在幻燈片上的排列方式，由占位符組成。占位符：一種帶有虛線或陰影線邊緣的框，絕大部分幻燈片版式中都有這種框。在這些框內(nèi)可以放置標(biāo)題及正文，或者是圖表、表格和圖片等對(duì)象?？梢詫媸街械恼嘉环苿?dòng)到不同位置，調(diào)整其大小，以及使用填充顏色和邊框設(shè)置其格式。也可以自己進(jìn)行版式設(shè)定。應(yīng)用模版。選擇“格式”“幻燈片設(shè)計(jì)”，調(diào)出設(shè)計(jì)工作區(qū)，選擇一種適合的模版?？梢詫?duì)某頁(yè)幻燈片或

57、者整個(gè)文件應(yīng)用某個(gè)設(shè)計(jì)模版。修改模版：進(jìn)入母版視圖。在應(yīng)用設(shè)計(jì)模板時(shí)，會(huì)在演示文稿上添加幻燈片母版?；脽羝赴媸谴鎯?chǔ)關(guān)于模板信息的設(shè)計(jì)模板（設(shè)計(jì)模板：包含演示文稿樣式的文件，包括項(xiàng)目符號(hào)和字體的類型和大小、占位符大小和位置、背景設(shè)計(jì)和填充、配色方案以及幻燈片母版和可選的標(biāo)題母版）。這些模板信息包括字形、占位符大小和位置、背景設(shè)計(jì)和配色方案?；脽羝赴娴哪康氖鞘鼓M(jìn)行全局更改（如替換字形），并使該更改應(yīng)用到演示文稿中的所有幻燈片。通?？梢允褂没脽羝赴孢M(jìn)行下列操作：更改字體或項(xiàng)目符號(hào)插入要顯示在多個(gè)幻燈片上的藝術(shù)圖片（如徽標(biāo)）更改占位符的位置、大小和格式更改幻燈片母版時(shí)，已對(duì)單張幻燈片進(jìn)行的更改

58、將被保留。一個(gè)演示文稿，可以應(yīng)用多個(gè)設(shè)計(jì)模版。選定幻燈片，選擇新的模版，選擇“應(yīng)用于選定幻燈片”。自定義模板：新建PowerPoint文件；點(diǎn)擊“視圖”-“母版”-“幻燈片母版”；在母版視圖上進(jìn)行模板的設(shè)計(jì)，包括背景效果，標(biāo)題區(qū)，頁(yè)腳區(qū)效果，字體，項(xiàng)目符號(hào)等；設(shè)計(jì)完畢后關(guān)閉母版視圖；點(diǎn)擊“文件”-“另存為”，將文件保存類型改為“演示文稿設(shè)計(jì)模板”，模板制作完成。導(dǎo)入演示文稿【插入】【幻燈片（從文件）】通過(guò)設(shè)置左下角的【保留源格式】選項(xiàng)，可以選擇是否保留被導(dǎo)入的頁(yè)面的原始格式。插入的頁(yè)面將沿用它上面一頁(yè)的格式信息。文本項(xiàng)目符號(hào)級(jí)別如果存在多個(gè)級(jí)別的項(xiàng)目符號(hào)，按【Tab】可以將文本降為下一級(jí)別，【

59、Shift】【Tab】將文本升為上一級(jí)別。如果需要調(diào)整同一級(jí)別下項(xiàng)目符號(hào)的前后順序，可以點(diǎn)擊【視圖】【工具欄】【大綱】，調(diào)出大綱工具條，點(diǎn)擊【上移】按鈕向上調(diào)整順序，點(diǎn)擊【下移】按鈕向下調(diào)整順序。插入頁(yè)腳，頁(yè)碼【視圖】【頁(yè)眉和頁(yè)腳】，在界面上定制相應(yīng)內(nèi)容：自動(dòng)調(diào)整自動(dòng)調(diào)整智能標(biāo)記可以用來(lái)調(diào)整文本，使之適合當(dāng)前占位符的大小。段落格式首行縮進(jìn)左縮進(jìn)首行縮進(jìn)：將段落中的第一行向右縮進(jìn)。左縮進(jìn)：所有行均向右縮進(jìn)。動(dòng)態(tài)表格。在excel中將表格選中，切換到PPT界面，選擇“編輯”“選擇性粘貼”，選擇粘貼鏈接。為了避免其他閱讀者打開文件時(shí)看到是否更新鏈接的提示信息，可以在將文件發(fā)送給對(duì)方之前斷開鏈接?！熬?/p>

60、輯”“鏈接”“斷開鏈接”使用控件ActiveX控件是用于向用戶提供選項(xiàng)或者運(yùn)行使任務(wù)自動(dòng)化的宏或者腳本?？梢栽赩BA中編寫控件的宏或者在腳本編輯器中編寫腳本?？梢栽诨脽羝刑砑涌丶?，以在幻燈片放映時(shí)與用戶交互信息。比如，在為展臺(tái)設(shè)計(jì)的幻燈片中可以使用控件使得觀眾可以選擇放映的內(nèi)容?？丶诘奈恢茫簩傩圆榭串?dāng)前所選控件的屬性查看代碼啟動(dòng)VB編輯器查看或編輯當(dāng)前控件代碼復(fù)選框文本框命令按鈕單擊時(shí)可以啟動(dòng)某個(gè)操作的按鈕選項(xiàng)按鈕單選按鈕列表框包含項(xiàng)目列表的框組合框帶有下拉列表框的文本框；可以選擇或輸入切換按鈕單擊時(shí)保持按下?tīng)顟B(tài)，再次單擊還原數(shù)值調(diào)節(jié)鈕可附加到文本框的按鈕滾動(dòng)條滾動(dòng)顯示內(nèi)容標(biāo)簽用于提供有