【正文】 鈕”2. 界面定制行號(hào)列標(biāo)，滾動(dòng)條，工作表標(biāo)簽的隱藏。案例07 匯總報(bào)表1. 對(duì)于分布在同一文件中的表格匯總：首先創(chuàng)建格式完全一致的匯總表的表結(jié)構(gòu)在匯總表需要匯總的項(xiàng)目上輸入：=sum()將光標(biāo)放入括號(hào)，用鼠標(biāo)選擇第一張工作表，按下shift鍵，再選取最后一張工作表，然后選擇需要匯總的單元格，回車(chē)。2. 對(duì)于分布在不同文件中的表格匯總：首先創(chuàng)建格式完全一致的匯總表的表結(jié)構(gòu)將光標(biāo)置于起始報(bào)表項(xiàng)目上選擇【數(shù)據(jù)】－【合并計(jì)算】“引用位置”選擇各個(gè)需要被合并的報(bào)表，點(diǎn)擊“添加”，直至所有需要被合并的報(bào)表添加完成。當(dāng)子表格數(shù)據(jù)發(fā)生變化需要更新匯總表數(shù)據(jù)時(shí)，只需執(zhí)行：數(shù)據(jù)合并計(jì)算，進(jìn)入合并計(jì)算界面后直接點(diǎn)擊確定退出該界面即可完成刷新數(shù)據(jù)的操作。首先取消不需要保護(hù)的單元格區(qū)域的鎖定狀態(tài)。如果需要隱藏單元格公式內(nèi)容，可以將隱藏選項(xiàng)選中。隱藏公式右鍵菜單－設(shè)置單元格格式－保護(hù)－選中隱藏選項(xiàng)，然后執(zhí)行對(duì)工作表的保護(hù)隱藏工作表：隱藏工作表后需要保護(hù)工作簿。選擇工具條上的命令按鈕。在Sub和End Sub之間輸入：sheets (“要跳轉(zhuǎn)的工作表名稱(chēng)”).select，關(guān)閉該窗口。案例10 財(cái)務(wù)報(bào)表利用excel創(chuàng)建財(cái)務(wù)報(bào)表，要注意報(bào)表之間的勾稽關(guān)系。為了能夠自動(dòng)維護(hù)報(bào)表間的數(shù)據(jù)關(guān)系，我們經(jīng)常需要使用單元格鏈接，將所有的表鏈接起來(lái)。串聯(lián)導(dǎo)致鏈接關(guān)系更加復(fù)雜且難以維護(hù)，而并聯(lián)的方式將只有一個(gè)統(tǒng)一的數(shù)據(jù)源，鏈接關(guān)系簡(jiǎn)單不容易出錯(cuò)，而且易于管理。（具體公式參見(jiàn)電子文檔）。數(shù)據(jù)表類(lèi)型操作目標(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ì)算不需要有非標(biāo)準(zhǔn)數(shù)據(jù)表匯總建議修改原表的結(jié)構(gòu)或者僅作為終端報(bào)表，上層再建立一個(gè)數(shù)據(jù)源表作為收集數(shù)據(jù)使用需要無(wú)案例12 數(shù)據(jù)透視表1. 應(yīng)用數(shù)據(jù)透視表進(jìn)行各類(lèi)數(shù)據(jù)分析。點(diǎn)擊“下一步”，選擇正確的數(shù)據(jù)范圍。在如下圖界面上，將右側(cè)的字段拖入左側(cè)相應(yīng)區(qū)域內(nèi)。生成如下的透視表后，將鼠標(biāo)懸停在想要移動(dòng)的字段上，鼠標(biāo)左鍵按下，將字段拖放到其他區(qū)域，生成自己需要的數(shù)據(jù)顯示。4. 顯示明細(xì)數(shù)據(jù)：雙擊需要查看明細(xì)的數(shù)據(jù)，在新的工作表上將顯示構(gòu)成此匯總數(shù)據(jù)的所有明細(xì)數(shù)據(jù)。6. 按照日期分組：1) 將日期放入行區(qū)域，【右鍵菜單】【組及顯示明細(xì)數(shù)據(jù)】【組合】2) 在【步長(zhǎng)】中選擇需要的分組標(biāo)準(zhǔn)7. 按照數(shù)值分組：1) 將銷(xiāo)售收入放入行區(qū)域，【右鍵菜單】【組及顯示明細(xì)數(shù)據(jù)】【組合】2) 在【步長(zhǎng)】中選擇需要的分組標(biāo)準(zhǔn)8. 插入計(jì)算字段：在【名稱(chēng)】后給計(jì)算字段定義名稱(chēng)在【公式】后輸入計(jì)算字段的計(jì)算公式，可以引用下面的字段9. 自動(dòng)更新：【右鍵菜單】【表格選項(xiàng)】【打開(kāi)時(shí)刷新】可以讓報(bào)表始終有更新后的數(shù)據(jù)顯示。如果已經(jīng)有現(xiàn)成的透視表，通過(guò)點(diǎn)擊數(shù)據(jù)透視表工具條上的圖表按鈕可以生成一個(gè)透視圖。隱藏透視圖字段按鈕：把光標(biāo)懸停在任意字段上，右鍵菜單，選擇【隱藏?cái)?shù)據(jù)透視圖字段按鈕】，即可以將字段按鈕隱藏；再次顯示字段按鈕：點(diǎn)擊透視圖工具條上第一項(xiàng)，在出現(xiàn)的下拉菜單中選擇【隱藏?cái)?shù)據(jù)透視圖字段按鈕】如果有些數(shù)據(jù)無(wú)法在數(shù)據(jù)透視表內(nèi)部分析，可以引用透視表的數(shù)據(jù)作為分析的基礎(chǔ)。 隱藏?cái)?shù)據(jù)透視表中的匯總字段或216。[紅色]金額借貸不平衡；[藍(lán)色]”借貸平衡”Ctrl+拖拽標(biāo)簽來(lái)復(fù)制工作表編制損益表216。 創(chuàng)建IS嵌套函數(shù)IS 類(lèi)函數(shù)，可以檢驗(yàn)數(shù)值的類(lèi)型并根據(jù)參數(shù)取值返回 TRUE 或 FALSE。ISERR值為任意錯(cuò)誤值（除去 N/A）。ISLOGICAL值為邏輯值。ISNONTEXT值為不是文本的任意項(xiàng)（注意此函數(shù)在值為空白單元格時(shí)返回 TRUE）。ISREF值為引用。216。銷(xiāo)售成本率=銷(xiāo)售成本/銷(xiāo)售凈額——成本率低，表示經(jīng)營(yíng)能力好。營(yíng)業(yè)比率=（銷(xiāo)售成本+營(yíng)業(yè)費(fèi)用）/銷(xiāo)售凈額，營(yíng)業(yè)比率越低，表示經(jīng)營(yíng)能力越強(qiáng)營(yíng)業(yè)費(fèi)用率=銷(xiāo)售費(fèi)用/銷(xiāo)售凈額——營(yíng)業(yè)費(fèi)用越低，表示經(jīng)營(yíng)績(jī)效越好。 用MAX、MIN函數(shù)顯示制表日期MAX，返回一組值中的最大值MIN，返回一組值中的最小值案例13 多重合并計(jì)算數(shù)據(jù)如下圖格式的即為二維表：現(xiàn)在我們需要將數(shù)個(gè)格式相同的二維表匯總為一張表格，且可以區(qū)分不同表格屬性進(jìn)行分析，比如三張表分別為北京，上海，深圳分公司的表格。選擇數(shù)據(jù)數(shù)據(jù)透視表和數(shù)據(jù)透視圖選擇第3個(gè)數(shù)據(jù)源類(lèi)型：多重合并計(jì)算數(shù)據(jù)區(qū)域，點(diǎn)擊下一步按鈕：在出現(xiàn)的界面上選擇：自定義字段選擇需要被合并的表格區(qū)域，點(diǎn)擊添加將其添加到所有區(qū)域；將頁(yè)字段數(shù)據(jù)改為1，在項(xiàng)標(biāo)志處輸入該表格的標(biāo)志，使用相同的方法將其他需要合并的表格全部添加。比如某地空調(diào)的銷(xiāo)量與氣溫的變化之間的關(guān)系?？梢岳盟鼛椭?jīng)營(yíng)，財(cái)務(wù)，銷(xiāo)售，營(yíng)銷(xiāo)等方面的決策。2. Correl函數(shù)判斷相關(guān)性。案例1516 數(shù)據(jù)分析與圖表分析日常工作中主要用到的數(shù)據(jù)分析有：構(gòu)成：局部與整體比例關(guān)系。差異：預(yù)算與實(shí)際數(shù)的版本差異等。勾稽：數(shù)據(jù)之間固有的相互聯(lián)系的屬性，利用此屬性進(jìn)行數(shù)據(jù)合理性的判斷。利用圖表可以更好地對(duì)業(yè)務(wù)數(shù)據(jù)進(jìn)行分析。選擇“視圖”“工具欄”“圖表”，調(diào)出圖表工具條：點(diǎn)開(kāi)圖表工具條上下拉菜單，選擇 系列“完成率”點(diǎn)擊圖表工具條上“數(shù)據(jù)系列格式”按鈕：在數(shù)據(jù)系列格式界面上，切換到坐標(biāo)軸標(biāo)簽，將系列繪制在由“主坐標(biāo)軸”改為“次坐標(biāo)軸”再次選中系列“完成率”，右鍵菜單選擇“圖表類(lèi)型”選擇折線圖，點(diǎn)確定：雙坐標(biāo)圖表制作完成。5. 復(fù)合餅圖：當(dāng)數(shù)據(jù)系列內(nèi)值的大小差異較大時(shí)，可以制作復(fù)合餅圖以便數(shù)據(jù)顯示更為清楚，如下圖所示：選擇餅圖中的復(fù)合餅圖子圖表類(lèi)型：點(diǎn)擊下一步，切換到圖例標(biāo)簽，將顯示圖例的勾去掉：切換到數(shù)據(jù)標(biāo)志標(biāo)簽，把類(lèi)別名稱(chēng)和百分比打勾：選中數(shù)據(jù)系列，右鍵菜單，選擇數(shù)據(jù)系列格式：切換到選項(xiàng)標(biāo)簽，將第二繪圖區(qū)包含最后的值改為：4復(fù)合餅圖制作完成。7. 下拉菜單式圖表：寫(xiě)入如下的公式：index函數(shù)查找出的值由A9單元格來(lái)指定。點(diǎn)擊“視圖”“工具欄”“窗體”，調(diào)出窗體工具條：在窗體工具條上選擇組合框，在餅圖上拖放出一個(gè)組合框：右鍵選中組合框，在菜單中選擇“設(shè)置控件格式”：在控制標(biāo)簽中做如下設(shè)置：其中單元格鏈接為存儲(chǔ)控件選中項(xiàng)目序號(hào)的單元格。定義兩個(gè)動(dòng)態(tài)引用的名稱(chēng)：Period: = offset(39。!$A$1,39。!$D$1,0,10,1)Data: = offset(39。!$B$1,39。!$D$1,0,10,1)制作折線圖，選擇系列標(biāo)簽，刪除period系列：將“值”和“分類(lèi)（X）軸標(biāo)志”設(shè)置為如下圖內(nèi)容：調(diào)出窗體工具條，在圖表外繪制滾動(dòng)條，右鍵選中滾動(dòng)條，選擇設(shè)置控件格式：在單元格鏈接中設(shè)置為D1:9. 甘特圖如果想制作如下圖的甘特圖，需要以下4列數(shù)據(jù)：首先制作堆積條形圖：點(diǎn)擊下一步，切換到系列標(biāo)簽，刪除結(jié)束日期系列：生成的條形圖如下圖所示：下面轉(zhuǎn)換縱坐標(biāo)的項(xiàng)目排列次序：雙擊縱坐標(biāo)，切換到刻度，將分類(lèi)次序反轉(zhuǎn)和數(shù)據(jù)軸交叉于最大分類(lèi)選項(xiàng)打勾。此時(shí)需要將橫坐標(biāo)開(kāi)始日期改為真正的項(xiàng)目開(kāi)始日期：在excel中，每一個(gè)日期都對(duì)應(yīng)一個(gè)數(shù)值，選中B2,查看200871對(duì)應(yīng)數(shù)值為39630雙擊橫坐標(biāo)，切換到刻度，將最小值改為39630，同樣將橫坐標(biāo)最大值改為真正的項(xiàng)目結(jié)束日期。案例17　動(dòng)態(tài)圖表選中北京，深圳，上海以下的區(qū)域，分別定義北京，深圳，上海的名稱(chēng)選擇視圖工具欄窗體，選擇選項(xiàng)按鈕，畫(huà)出如下三個(gè)選項(xiàng)按鈕：右鍵選中選項(xiàng)按鈕，在右鍵菜單中選擇設(shè)置控件格式，在跳出的對(duì)話(huà)框中選擇控制標(biāo)簽，在單元格鏈接中選擇A16：在A17單元格中輸入公式：=choose(a16,”北京”,”上海”,”深圳”)，為A17定義名稱(chēng)：choose在A2,A3,A4單元格中輸入以下內(nèi)容：在B16單元中寫(xiě)入以下公式，并復(fù)制到該行112月的單元格中：=VLOOKUP(A3,INDIRECT(choose),COLUMN()1,0)依據(jù)此數(shù)據(jù)表制作雙曲線圖：案例18 比率分析利用透視表靈活的特性，將兩個(gè)結(jié)構(gòu)完全相同的透視表相除，即得到可以進(jìn)行任意兩個(gè)因素間的比率分析圖表。年假規(guī)則：規(guī)定：公司工齡小于1年的，享受10天年假；大于1年小于10年的，工齡每增加一年，年假增加1天；增長(zhǎng)到20天不再增加。單元格F3為工齡。在第一個(gè)參數(shù)所在的區(qū)域里面查找第二個(gè)參數(shù)指定的值，找到后對(duì)第三個(gè)參數(shù)指定的字段進(jìn)行求和?！焙！盿mp。5. sum與數(shù)組公式聯(lián)手計(jì)數(shù){=sum((a1:a1001000)*(a1:a1005000))}6. sum與數(shù)組公式聯(lián)手求和{=sum((條件1)*(條件2)*…..*求和區(qū)域)}7. sumproduct的使用返回相應(yīng)的數(shù)組或區(qū)域的乘積的和。sumproduct((條件1)*(條件2)*…) 用于計(jì)數(shù)sumproduct((條件1)*(條件2)*…*求和區(qū)域) 用于求和案例21 模擬運(yùn)算表（略）案例22 單變量求解如果希望利潤(rùn)達(dá)到200萬(wàn)，則需要相應(yīng)調(diào)整的單價(jià)、直接成本、固定成本、銷(xiāo)售量為多少？可變單元格：要求解的值所在的單元格目標(biāo)單元格：關(guān)系式表達(dá)所在的單元格目標(biāo)值：關(guān)系式的值單變量求解的原理：根據(jù)在單元格中描述的一元方程求解方程中的未知數(shù)。如果是函數(shù)關(guān)系，則需要在單元格中引用該函數(shù)。構(gòu)建模型的過(guò)程實(shí)際上是利用Excel單元格引用的方式將條件描述清楚的過(guò)程。目標(biāo)單元格為求解后達(dá)到的目標(biāo)。在約束中添加已知的其他條件。案例24 自定義函數(shù)宏的安全性級(jí)別調(diào)整點(diǎn)擊：工具宏安全性如果定義了宏或者自定義函數(shù)并且想使用它們，那么需要將宏的安全性級(jí)別調(diào)整為中或者低。如果不存在，點(diǎn)擊【插入】菜單，選擇“模塊”；如果存在，則略過(guò)此步驟。4. 在右側(cè)代碼窗口輸入以下語(yǔ)句：Function valuation(a, b, c, d)valuation = (a + b) * + (c + d) * End Function其中，valuation是該函數(shù)的函數(shù)名，a,b,c,d是該自定義函數(shù)的參數(shù)，function 與end function是自定義函數(shù)的起始與結(jié)束語(yǔ)句。是用來(lái)開(kāi)發(fā)應(yīng)用程序的Visual basic語(yǔ)言，除了它無(wú)法象Visual basic般獨(dú)立存在外，其它的如程序的編輯方法，對(duì)象的使用等，都與Visual basic相同，因此如果學(xué)習(xí)過(guò)Visual basic語(yǔ)言，入門(mén)VBA將是一件很容易的事。因此將提供這些功能的編輯器稱(chēng)為Visual basic Editor。 就EXCEL而言其對(duì)象就是工作薄、工作表、圖表工作表或其他更細(xì)微的窗體組件。取得屬性值的語(yǔ)法：變量=比如我們要取得當(dāng)前工作表的名子和工作表的數(shù)量AAA=BBB=設(shè)定屬性的語(yǔ)法:=屬性值例:sheets(1).name=我的工作薄1 設(shè)置第一個(gè)工作表的名稱(chēng)是我的工作薄1事件: 所謂事件,就是發(fā)生在對(duì)象上的事情例如在按按上單擊鼠標(biāo)這個(gè)動(dòng)作就是發(fā)生于按紐上的事件，工作表的增加，單元格的修改都是事件。再如，我們有時(shí)候需要在改變某個(gè)工作表中的單元格時(shí)運(yùn)行某個(gè)程序這時(shí)就可以用Worksheet_Change這個(gè)事件。有時(shí)在編程序時(shí)，忘記了EXCEL某些基本功能的編寫(xiě)，錄制宏就是一個(gè)很好的方法，比如你忘記了不連續(xù)單元格的表示方法，你就可以在工作表中把你選取不連續(xù)單元格的過(guò)程錄制下來(lái)，代碼自然就出來(lái)了。案例25　資產(chǎn)折舊216。 固定余額遞減折舊法計(jì)算資產(chǎn)折舊=DB（Cost,Satvage,Life,Year）216。 可變余額遞減折舊法計(jì)算資產(chǎn)折舊=VDB（Cost,Satvage,Life,StartPeriod,EndPeriod,Factor,NoSwitch）216。凈現(xiàn)值。凈現(xiàn)值和內(nèi)部報(bào)酬率是關(guān)系緊密的一對(duì)函數(shù)。附2： 整合數(shù)據(jù)表我們經(jīng)常需要將幾個(gè)表格中的數(shù)據(jù)整合成一張表，如下圖所示，將5個(gè)月的數(shù)據(jù)（目前分布在5張表格上）整合到一個(gè)總表上：我們使用的方法是利用office的查詢(xún)工具query進(jìn)行的，這樣做的優(yōu)點(diǎn)在于今后對(duì)于新數(shù)據(jù)的更新可以自動(dòng)化刷新，而不需要重復(fù)的進(jìn)行整合操作（如復(fù)制粘貼）。l 在光驅(qū)中插入安裝光盤(pán)后，安裝過(guò)程中選擇自定義安裝；點(diǎn)開(kāi)Excel前面的加號(hào)，選中要安裝的功能前面的下拉菜單，選擇【從本機(jī)運(yùn)行】；點(diǎn)開(kāi)【Office工具】前面的加號(hào)，選中【Microsoft Query】的下拉菜單，選擇【從本機(jī)運(yùn)行】，然后執(zhí)行余下的安裝過(guò)程即可。該查詢(xún)數(shù)據(jù)表與數(shù)據(jù)源保持著動(dòng)態(tài)鏈接關(guān)系，可以隨時(shí)刷新數(shù)據(jù)，不需要重復(fù)操作。附3：對(duì)于數(shù)據(jù)庫(kù)的訪問(wèn)鏈接方法ODBC的介紹存儲(chǔ)數(shù)據(jù)并不是Excel的特長(zhǎng)，Excel