EXCEL在人力資源管理中的應(yīng)用(案例與技巧)

1、Excel案例01第1部分 Excel基礎(chǔ)概念1. 名稱名稱可以代表一個單元格或者一個單元格區(qū)域，或者是常量，公式。名稱的定義：選中需要命名的單元格或區(qū)域，在界面左上角名稱框中輸入名稱后回車；名稱的刪除：插入名稱定義，選中需要刪除的名稱點擊刪除按鈕，點確定。名稱的引用：需要引用某單元格時輸入該單元格的名稱：名稱第1步：選中需要命名的某個單元格或單元格區(qū)域。第2步：在左上角名稱框輸入命名后回車。刪除已有命名：選擇“插入”“名稱”“定義”；選中需要刪除的名稱，點擊“刪除”按鈕。名稱命名的優(yōu)點：1) 避免絕對引用的錯誤2) 對公式進行文字化表述，讓公式更加容易理解3) 可以在整個工作簿中通用，引用方

2、便2. 常用引用函數(shù)row: 返回指定單元格的行號column: 返回指定單元格的列標match: 返回查找值在查找范圍中的序號 =match(查找值，查找范圍，0) 其第三個參數(shù)為0，表示查找精確值address: 返回單元格名稱，其參數(shù)為行號列標或計算行號列標的表達式 =address(行號，列標) =address(1,1) 此公式返回A1indirect: 返回單元格的值.其參數(shù)為單元格名稱 =indirect(“A1”),假設(shè)A1=10,則indirect返回10index: 在某區(qū)域內(nèi)查找某個位置的值 =index(查找區(qū)域，查找值所在的行號，查找值所在的列號)offset：指定基

3、點，指定位移量，得到單元格引用 單個單元格引用：=offset(基點單元格，向下移動的行，向右移動的列) 區(qū)域的引用：=offset(基點單元格，向下移動的行，向右移動的列，區(qū)域 包括的行數(shù)，區(qū)域包括的列數(shù)) 向上和向左移動時，位移量為負值。 =offset(A1,1,1) 將得到B2單元格的值3. 動態(tài)引用動態(tài)引用是通過引用函數(shù)實現(xiàn)對于單元格或區(qū)域的相對引用。它和相對引用的效果很相似，但比簡單的相對引用用途廣泛。常用的動態(tài)引用的實現(xiàn)方法有：引用函數(shù)嵌套。比如offset和row,column嵌套；index和match，indirect,address嵌套等；引用函數(shù)與控件結(jié)合使用，此類控件

4、包括組合框，滾動條。動態(tài)引用的作用：實現(xiàn)對于單元格的動態(tài)引用；進行動態(tài)分析；制作動態(tài)報表。4. 運算類型數(shù)值運算：1 + 1 = 2邏輯運算：1 0 = TRUE在邏輯運算中，TRUE=1，F(xiàn)ALSE=0我們利用邏輯運算進行條件判斷在Excel中常用的一些邏輯函數(shù)：if,and,or,not,iserror等and: 只有當所有條件全部滿足，才會返回true的邏輯值 語法：and(條件1，條件2，)or: 滿足其中任何一個條件，都會返回true的邏輯值 語法：or(條件1，條件2，條件3,)5. 函數(shù)調(diào)用的語法函數(shù)名稱（參數(shù)1，參數(shù)2，）6. 絕對引用和相對引用：絕對引用：所引用的單元格不隨著

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

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

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

8、ft + 下箭頭/右箭頭2. 快速選中區(qū)域ctrl + shift + 83. 行列互換復(fù)制需要進行行列互換的區(qū)域后，將光標放置在數(shù)據(jù)表外面位置，選擇性粘貼選中“轉(zhuǎn)置”選項4. 一個單元格內(nèi)容輸入為多行alt + 回車鍵5. 顯示公式ctrl + 6. 凍結(jié)窗口選擇需要進行凍結(jié)的單元格位置，選擇窗口凍結(jié)窗格7. 自定義格式語法： 大于條件值格式；小于條件值格式；等于條件值格式；文本格式8. 縮放數(shù)值 0.00， 按百萬縮放 0“.”0，按萬縮放 0.00, 按千縮放9. 不復(fù)制隱藏的行或列a首先選中需要復(fù)制的被隱藏了一些行或列的表格區(qū)域；b 然后點擊“編輯”-“定位”-“定位條件”，在其中選擇

9、“可見單元格”；c 復(fù)制表格區(qū)域，粘貼即可。案例02 一 文本的處理1. 等長文本的分割從字符串的左邊取字符：=Left (字符串，文本長度)從字符串的右邊取字符：=Right (字符串，文本長度)從字符串的中間取字符：=Mid (字符串，文本起始位置，文本長度)2. 不等長文本的分割第1步：選中要進行分割的字符串區(qū)域。第2步：點擊“數(shù)據(jù)”“分列”，在文本分列向?qū)е羞x擇適合的分隔符。第3步：設(shè)置需要導(dǎo)入的列以及放置該列的位置。3. 文本的合并=Concatenate(文本1，文本2，)使用連接符&：動態(tài)表頭：=ABC公司&YEAR(NOW()&年&MONTH(NOW()&月報表4. 有效性選中

10、需要設(shè)置有效性的區(qū)域，點擊“數(shù)據(jù)”“有效性”，在有效性條件中選擇“序列”，在來源中錄入或選取列表。輸入信息：事前提醒。出錯警告：事后提示。如果待選列表不在當前工作表中，需要首先為該列表定義名稱，然后在有效性來源框中輸入：“=該列表的名稱”5. 有效性的其他用法不允許錄入重復(fù)數(shù)據(jù)的有效性設(shè)定：countif(e:e,e12)=1輸入的內(nèi)容中必須包括某字符：=not(iserror(find(中國,g30)二級選項：首先將一級選項的每個項目定義一個名稱，該名稱內(nèi)容包括相應(yīng)的二級項目；制作一級項目的有效性；制作二級項目的有效性：內(nèi)容為：=INDIRECT(g6)，其中g(shù)6為設(shè)定了有效性的一級選項所在

11、的單元格。6. 圈示無效數(shù)據(jù)。選擇“工具”“公式審核”“顯示公式審核工具欄”，點擊“圈示無效數(shù)據(jù)”按鈕。該工具可以將所有不符合有效性設(shè)定的內(nèi)容圈示出來。二 日期的處理1. Datedif 函數(shù)：用于計算兩個日期之間的年數(shù)，月數(shù)，天數(shù)。=Datedif(開始日期，結(jié)束日期，“y”)第三個參數(shù)：“y”：表示年數(shù)“m”：表示月數(shù)“d”：表示天數(shù)2. 生日提醒=DATEDIF(TODAY(),DATE(YEAR(TODAY(),MONTH(G2),DAY(G2),d)3. 某日期的星期=weekday(A2,2) 返回的值是3，則表示該日期是星期三。4. 兩個日期間的工作日天數(shù)=networkdays

12、(開始日期，結(jié)束日期，節(jié)假日列表)需要首先加載“分析工具庫”才能使用此工具案例03 數(shù)據(jù)查詢1. VLOOKUP（）VLOOKUP在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當前行中指定列處的數(shù)值。VLOOKUP 中的 V 代表垂直。VLOOKUP(lookup_value, table_array, col_ index_num, range_lookup)Lookup_value 為需要在數(shù)組第一列中查找的數(shù)值。Lookup_value 可以為數(shù)值、引用或文本字符串。也可以理解為：兩表共有的索引字段。Table_array 為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表。可以使用對區(qū)域或區(qū)域名

13、稱的引用，例如數(shù)據(jù)庫或列表。必須使得共有字段位于該范圍的第一列。col_ index_num 為需要調(diào)轉(zhuǎn)的數(shù)據(jù)位于第二個參數(shù)中定義的范圍的第幾列。必須為單純數(shù)值。range_lookup 定義大致匹配或精確匹配。False或0:精確匹配；true或忽略或1：如果無法找到精確匹配的值，那么就查找并匹配比查找值小的最近似的值。2. HLOOKUP()Hlookup中的H是水平的意思，在橫向排列的表格中查詢數(shù)據(jù)用Hlookup，垂直排列的表格查詢用Vlookup函數(shù)。其參數(shù)含義與VLOOKUP近似。案例04 查詢表信息查詢表利用有效性和VLOOKUP函數(shù)實現(xiàn)。利用有效性實現(xiàn)索引字段的切換。利用VLO

14、OKUP函數(shù)查詢與索引字段相關(guān)的信息。案例05 數(shù)據(jù)篩選1. 自動篩選（略）2. 高級篩選由于自動篩選只能篩選出簡單條件的數(shù)據(jù)，因此如要在復(fù)雜條件下進行篩選就需要采用高級篩選的方式。在需要進行篩選的數(shù)據(jù)表外設(shè)定篩選條件。在對話框中設(shè)定：列表區(qū)域為數(shù)據(jù)表區(qū)域；條件區(qū)域為剛才在數(shù)據(jù)表外部設(shè)定的條件區(qū)域，要包括字段名稱和條件所在的單元格區(qū)域。3. 條件格式條件格式可以使得符合特定條件的記錄按照某種設(shè)定的格式顯示。1) 選中要進行條件格式設(shè)定的單元格范圍2) 執(zhí)行【格式】【條件格式】3) 在條件格式設(shè)置界面進行條件的設(shè)定：4. 統(tǒng)計Frequency(需要進行統(tǒng)計的數(shù)據(jù)區(qū)域，分段點)對數(shù)據(jù)進行區(qū)間統(tǒng)計

15、。此函數(shù)需要和數(shù)組公式一起使用。此函數(shù)是根據(jù)數(shù)值的大小劃分區(qū)間進行統(tǒng)計的。列出需要分析的工資區(qū)間，選中需要計算統(tǒng)計結(jié)果的單元格區(qū)域，輸入函數(shù)：按下組合鍵：ctrl + shift + enter5. 排名次=rank(需排名數(shù)值，排名數(shù)據(jù)范圍，排序方式)排序方式：0或忽略降序，否則為升序。案例06 窗體控件的使用1. 下拉菜單的制作。選擇“視圖”“工具欄”“窗體”，出現(xiàn)“窗體”工具條，在工具條上選擇“組合框”。 按下鼠標左鍵，拖放出合適的大小后釋放鼠標，出現(xiàn)下拉菜單形狀。選中該下拉菜單，右鍵選擇“設(shè)置控件格式”在“控制”菜單上，去選擇相應(yīng)的數(shù)據(jù)源?！叭S陰影”選項是下拉菜單顯示效果設(shè)定。單元格

16、鏈接：該處指定的單元格中將存儲在控件中被選中的項目在數(shù)據(jù)源中的序列號。我們經(jīng)常利用該序列號引用控件選中的值，還可以利用該值制作動態(tài)圖表。根據(jù)報銷單上填報的日期自動生成該報銷單的起止日期。使用最大值函數(shù)MAX和最小值函數(shù)MIN。制作單選按鈕。使用窗體工具條上的“選項按鈕”2. 界面定制行號列標，滾動條，工作表標簽的隱藏。選擇“工具”“選項”“視圖”，將窗口選項中的行號列標，水平滾動條，垂直滾動條，工作表標簽選項去掉。案例07 匯總報表1. 對于分布在同一文件中的表格匯總：首先創(chuàng)建格式完全一致的匯總表的表結(jié)構(gòu)在匯總表需要匯總的項目上輸入：=sum()將光標放入括號，用鼠標選擇第一張工作表，按下sh

17、ift鍵，再選取最后一張工作表，然后選擇需要匯總的單元格，回車。將該公式復(fù)制到其他需要匯總的單元格即可實現(xiàn)整張表的匯總。2. 對于分布在不同文件中的表格匯總：首先創(chuàng)建格式完全一致的匯總表的表結(jié)構(gòu)將光標置于起始報表項目上選擇【數(shù)據(jù)】【合并計算】“引用位置”選擇各個需要被合并的報表，點擊“添加”，直至所有需要被合并的報表添加完成。需要刪除一張表格時，只需要在合并計算界面上選中該表格，點擊刪除按鈕。當子表格數(shù)據(jù)發(fā)生變化需要更新匯總表數(shù)據(jù)時，只需執(zhí)行：數(shù)據(jù)-合并計算，進入合并計算界面后直接點擊確定退出該界面即可完成刷新數(shù)據(jù)的操作。案例08 報表保護單元格區(qū)域的保護。首先取消不需要保護的單元格區(qū)域的鎖定

18、狀態(tài)。右鍵“設(shè)置單元格格式”，在“保護”標簽上將鎖定選項取消。如果需要隱藏單元格公式內(nèi)容，可以將隱藏選項選中。選擇“工具”“保護”“保護工作表”，輸入保護密碼。隱藏公式右鍵菜單設(shè)置單元格格式保護選中隱藏選項，然后執(zhí)行對工作表的保護隱藏工作表：隱藏工作表后需要保護工作簿。案例09 按鈕的制作選擇“視圖”“工具欄”“窗體”，調(diào)出窗體工具條。選擇工具條上的命令按鈕。繪制出按鈕的基本形狀后，自動跳出“指定宏”對話框，點擊“新建”按鈕，出現(xiàn)VBA編程界面。在Sub和End Sub之間輸入：sheets (“要跳轉(zhuǎn)的工作表名稱”).select，關(guān)閉該窗口。選中該按鈕，右鍵菜單“編輯文字”，可以修改按鈕名

19、稱。案例10 財務(wù)報表利用excel創(chuàng)建財務(wù)報表，要注意報表之間的勾稽關(guān)系。一方面是出于高效率管理報表的考慮，另一方面，可以幫助我們分析報表中的數(shù)據(jù)，并快速檢查出報表中的可能錯誤。為了能夠自動維護報表間的數(shù)據(jù)關(guān)系，我們經(jīng)常需要使用單元格鏈接，將所有的表鏈接起來。注意鏈接方式請使用并聯(lián)而非串聯(lián)。串聯(lián)導(dǎo)致鏈接關(guān)系更加復(fù)雜且難以維護，而并聯(lián)的方式將只有一個統(tǒng)一的數(shù)據(jù)源，鏈接關(guān)系簡單不容易出錯，而且易于管理。比如損益表，資產(chǎn)負債表，現(xiàn)金流量表，需要使用公式和鏈接使得這些表鏈接起來，該鏈接關(guān)系能夠自動維護和更新多表之間的勾稽關(guān)系，時刻能夠保證報表數(shù)據(jù)之間的勾稽的正確性。（具體公式參見電子文檔）。案例11

20、 表格的幾種類型數(shù)據(jù)表可以分為數(shù)據(jù)列表，二維表，多維表和帶有格式的報表等幾種類型。數(shù)據(jù)表類型操作目標匯總方法是否需要重復(fù)制作是否與數(shù)據(jù)源有鏈接關(guān)系多字段數(shù)據(jù)列表記錄疊加并按表名區(qū)分導(dǎo)入外部數(shù)據(jù) + SQL語句不需要有二維表多個二維表生成透視表透視表多維數(shù)據(jù)區(qū)域合并不需要有同一文件內(nèi)的報表數(shù)據(jù)匯總=sum(begin:end!B2)不需要有不同文件中的報表數(shù)據(jù)匯總合并計算不需要有非標準數(shù)據(jù)表匯總建議修改原表的結(jié)構(gòu)或者僅作為終端報表，上層再建立一個數(shù)據(jù)源表作為收集數(shù)據(jù)使用需要無案例12 數(shù)據(jù)透視表1. 應(yīng)用數(shù)據(jù)透視表進行各類數(shù)據(jù)分析。選擇“數(shù)據(jù)”“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”，進入透視表設(shè)置向?qū)?。點擊

21、“下一步”，選擇正確的數(shù)據(jù)范圍。點擊“下一步”，選擇“布局”按鈕。在如下圖界面上，將右側(cè)的字段拖入左側(cè)相應(yīng)區(qū)域內(nèi)。形成如下圖的布局，點擊確定按鈕。生成如下的透視表后，將鼠標懸停在想要移動的字段上，鼠標左鍵按下，將字段拖放到其他區(qū)域，生成自己需要的數(shù)據(jù)顯示。數(shù)據(jù)百分比顯示：右鍵菜單字段設(shè)置選項，將數(shù)據(jù)顯示方式改為“占同列數(shù)據(jù)總和的百分比”2. 同時顯示數(shù)據(jù)與百分比：1) 在【布局】里將銷售收入兩次拖入數(shù)據(jù)區(qū)域2) 將其中一個銷售收入改為百分比顯示3) 在報表項目上輸入新的名稱可以修改項目名稱4) 將報表項目拖拽到列標題位置，可以將數(shù)值和百分比改為按列排列3. 篩選數(shù)據(jù)：點擊字段名稱后面的下拉菜單

22、，可以對字段內(nèi)容進行篩選。4. 顯示明細數(shù)據(jù)：雙擊需要查看明細的數(shù)據(jù)，在新的工作表上將顯示構(gòu)成此匯總數(shù)據(jù)的所有明細數(shù)據(jù)。5. 對數(shù)據(jù)進行排序：把光標放在行位置的報表項目上，【右鍵菜單】【字段設(shè)置】，【高級】按鈕，左側(cè)可以進行排序設(shè)置。6. 按照日期分組：1) 將日期放入行區(qū)域，【右鍵菜單】【組及顯示明細數(shù)據(jù)】【組合】2) 在【步長】中選擇需要的分組標準7. 按照數(shù)值分組：1) 將銷售收入放入行區(qū)域，【右鍵菜單】【組及顯示明細數(shù)據(jù)】【組合】2) 在【步長】中選擇需要的分組標準8. 插入計算字段：在【名稱】后給計算字段定義名稱在【公式】后輸入計算字段的計算公式，可以引用下面的字段9. 自動更新：【

23、右鍵菜單】【表格選項】【打開時刷新】可以讓報表始終有更新后的數(shù)據(jù)顯示。10. 透視圖可以單獨生成數(shù)據(jù)透視圖，也可以基于現(xiàn)有的透視表生成透視圖。如果已經(jīng)有現(xiàn)成的透視表，通過點擊數(shù)據(jù)透視表工具條上的圖表按鈕可以生成一個透視圖。通過拖動透視圖上的各個字段到右側(cè)或者底部的位置，可以方便地改變圖表組織數(shù)據(jù)的方式。隱藏透視圖字段按鈕：把光標懸停在任意字段上，右鍵菜單，選擇【隱藏數(shù)據(jù)透視圖字段按鈕】，即可以將字段按鈕隱藏；再次顯示字段按鈕：點擊透視圖工具條上第一項，在出現(xiàn)的下拉菜單中選擇【隱藏數(shù)據(jù)透視圖字段按鈕】如果有些數(shù)據(jù)無法在數(shù)據(jù)透視表內(nèi)部分析，可以引用透視表的數(shù)據(jù)作為分析的基礎(chǔ)。11. 制作試算平衡表

24、 點擊“數(shù)據(jù)”并拖拽到“匯總” 隱藏數(shù)據(jù)透視表中的匯總字段或 單元格格式自定義功能格式設(shè)定為：紅色借貸不平衡;紅色金額借貸不平衡；藍色”借貸平衡”Ctrl+拖拽標簽來復(fù)制工作表編制損益表 創(chuàng)建損益表格式 創(chuàng)建IS嵌套函數(shù)IS 類函數(shù)，可以檢驗數(shù)值的類型并根據(jù)參數(shù)取值返回 TRUE 或 FALSE。函數(shù)如果為下面的內(nèi)容，則返回 TRUEISBLANK值為空白單元格。ISERR值為任意錯誤值（除去 #N/A）。ISERROR值為任意錯誤值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!）。ISLOGICAL值為邏輯值。ISNA值為錯誤值 #N/A（

25、值不存在）。ISNONTEXT值為不是文本的任意項（注意此函數(shù)在值為空白單元格時返回 TRUE）。ISNUMBER值為數(shù)字。ISREF值為引用。ISTEXT值為文本。 創(chuàng)建財務(wù)比率銷售毛利率=銷售毛利/銷售凈額毛利率大，表示經(jīng)營能力強。銷售成本率=銷售成本/銷售凈額成本率低，表示經(jīng)營能力好。凈利率率=凈利潤/銷售凈額凈利潤率越高，表示經(jīng)營能力強。營業(yè)比率=（銷售成本+營業(yè)費用）/銷售凈額，營業(yè)比率越低，表示經(jīng)營能力越強營業(yè)費用率=銷售費用/銷售凈額營業(yè)費用越低，表示經(jīng)營績效越好。 用MAX、MIN函數(shù)顯示制表日期MAX，返回一組值中的最大值MIN，返回一組值中的最小值案例13 多重合并計算數(shù)據(jù)

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

27、互影響的關(guān)系，主要用于分析單個因變量如何受一個或多個自變量影響的。比如某地空調(diào)的銷量與氣溫的變化之間的關(guān)系?？梢杂媒y(tǒng)計獲得的歷史數(shù)據(jù)，對未來的數(shù)據(jù)進行預(yù)測?？梢岳盟鼛椭?jīng)營，財務(wù)，銷售，營銷等方面的決策。1. 趨勢線預(yù)測1) 根據(jù)歷史數(shù)據(jù)制作出折線圖2) 選中圖表中的折線，右鍵菜單添加趨勢線3) 選擇預(yù)測類型（可以先選擇默認的線性）4) 切換到選項標簽，根據(jù)需要預(yù)測的期間數(shù)，在“前推”處輸入預(yù)測期間個數(shù)，并選中“顯示公式”和“顯示R平方值”選項5) 根據(jù)出現(xiàn)的回歸系數(shù)（R平方）判斷預(yù)測類型是否適合，R平方越接近1越好（多項式除外，需要考慮業(yè)務(wù)數(shù)據(jù)是否存在較大的波動，如果沒有則不適用多項式分析

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

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

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

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

32、據(jù)區(qū)域為A3:I3,A9:I9,需要手工選取。點擊“視圖”-“工具欄”-“窗體”，調(diào)出窗體工具條：在窗體工具條上選擇組合框，在餅圖上拖放出一個組合框：右鍵選中組合框，在菜單中選擇“設(shè)置控件格式”：在控制標簽中做如下設(shè)置：其中單元格鏈接為存儲控件選中項目序號的單元格。完成下拉菜單式圖表的制作：8. 帶有滾動條的圖表：該圖表中可以隨著點擊滾動條的動作而動態(tài)翻看源數(shù)據(jù)中的大量數(shù)據(jù)。定義兩個動態(tài)引用的名稱：Period: = offset(7.動態(tài)圖表!$A$1,7.動態(tài)圖表!$D$1,0,10,1)Data: = offset(7.動態(tài)圖表!$B$1,7.動態(tài)圖表!$D$1,0,10,1)制作折線圖

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

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

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

36、(K3-1600)*0.45-15375,IF(K3-1600)80000,(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)4. 自動計算加班費=IF(IS

37、ERROR(VLOOKUP(D2,$L$2:$L$12,1,FALSE),IF(OR(G2=6,G2=7),周末加班,工作日加班),節(jié)假日加班)5. 自動計算請假扣除=IF(OR(D5=病假,D5=事假),E5*1,IF(D5=曠工,E5*3,0)6. 隱藏錯誤提示=IF(ISERROR(表達式),表達式)案例20 計數(shù)與求和1. 條件計數(shù)countif(range，criteria)countif(A1:A100，”8”)2. 條件求和如果滿足某個條件，就對該記錄里的指定數(shù)值字段求和。在第一個參數(shù)所在的區(qū)域里面查找第二個參數(shù)指定的值，找到后對第三個參數(shù)指定的字段進行求和。sumif(rang

38、e, criteria, sum_range)sumif(A1:A100, “?海*”, E1:E100) 對A列中第4個字為海的E列的值求和3. 模糊條件求和sumif(a1:a100, “*”&”?！?”*”, e1:e100) 對A列中包含“海”字的E列的值求和4. sumif對多個條件進行求和=SUM(SUMIF(F:F,F2:F3,E:E)=SUM(SUMIF(F:F,陳露,程靜,E:E)criteria為常量，普通公式；criteria為單元格引用，需要使用數(shù)組公式。5. sum與數(shù)組公式聯(lián)手計數(shù)=sum(a1:a1001000)*(a1:a1005000)6. sum與數(shù)組公式聯(lián)

39、手求和=sum(條件1)*(條件2)*.*求和區(qū)域)7. sumproduct的使用返回相應(yīng)的數(shù)組或區(qū)域的乘積的和?？梢允褂胹umproduct替代sum數(shù)組公式的使用用于多條件求和。sumproduct(條件1)*(條件2)*) 用于計數(shù)sumproduct(條件1)*(條件2)*求和區(qū)域) 用于求和案例21 模擬運算表（略）案例22 單變量求解如果希望利潤達到200萬，則需要相應(yīng)調(diào)整的單價、直接成本、固定成本、銷售量為多少？可變單元格：要求解的值所在的單元格目標單元格：關(guān)系式表達所在的單元格目標值：關(guān)系式的值單變量求解的原理：根據(jù)在單元格中描述的一元方程求解方程中的未知數(shù)。使用單變量求解的

40、前提：需要在單元格中將一元方程的方程式描述清楚。如果是函數(shù)關(guān)系，則需要在單元格中引用該函數(shù)。案例23規(guī)劃求解步驟：構(gòu)建已知條件的關(guān)系模型，然后運行規(guī)劃求解工具。構(gòu)建模型的過程實際上是利用Excel單元格引用的方式將條件描述清楚的過程。在規(guī)劃求解對話框中，進行相關(guān)設(shè)置。目標單元格為求解后達到的目標。可變單元格為要求解的單元格。在約束中添加已知的其他條件。規(guī)劃求解可以求解多元方程，可以解決求極值的問題，可以替代計算較為復(fù)雜的計算過程。案例24 自定義函數(shù)宏的安全性級別調(diào)整點擊：工具-宏-安全性如果定義了宏或者自定義函數(shù)并且想使用它們，那么需要將宏的安全性級別調(diào)整為中或者低。自定義函數(shù)：1. 進入V

41、B編輯器2. 選中左側(cè)工程管理器中的當前工作簿，查看其下項目中是否存在“模塊”。如果不存在，點擊【插入】菜單，選擇“模塊”；如果存在，則略過此步驟。3. 雙擊模塊下的某一模塊，右側(cè)將彈出該模塊的代碼窗口，我們將在此代碼窗口中編寫自定義函數(shù)。4. 在右側(cè)代碼窗口輸入以下語句：Function valuation(a, b, c, d)valuation = (a + b) *0.6 + (c + d) * 0.4End Function其中，valuation是該函數(shù)的函數(shù)名，a,b,c,d是該自定義函數(shù)的參數(shù)，function 與end function是自定義函數(shù)的起始與結(jié)束語句。帶有判斷的

42、自定義函數(shù)：Function limitation(job)If job = “總經(jīng)理”O(jiān)r job = “市場銷售” Thenlimitation = 1500ElseIf job = “客戶服務(wù)” Or job = “副總經(jīng)理” Thenlimitation = 1200ElseIf job = “職能管理” Or job = “業(yè)務(wù)總監(jiān)” Thenlimitation = 800ElseIf job = “產(chǎn)品” Or job = “采購” Thenlimitation = 600ElseIf job = “研發(fā)技術(shù)”O(jiān)r job = “生產(chǎn)運作” Thenlimitation = 40

43、0Else: limitation = 100End IfEnd Function附：VBA簡介VBA簡介：VBA 是 Visual basic for Application 的縮寫。是用來開發(fā)應(yīng)用程序的Visual basic語言，除了它無法象Visual basic般獨立存在外，其它的如程序的編輯方法，對象的使用等，都與Visual basic相同，因此如果學(xué)習過Visual basic語言，入門VBA將是一件很容易的事。VBE是Visual basic Editor的縮寫,是用來編寫VBA應(yīng)用程序的編輯器,它與VB編器有什么區(qū)別呢?Visual basic是用來開發(fā)完整程序的工具,必須

44、使用相當大的系統(tǒng)資源,但是在控制EXCEL或其他OFFICE辦公軟件時,并不需要使用這么龐大的資源,因此Microsoft將Visual basic編寫及執(zhí)行程序的功能附屬于OFFICE之下，使其可以使用較少的系統(tǒng)資源，達到編輯出應(yīng)用程序的目的。因此將提供這些功能的編輯器稱為Visual basic Editor。EXCELVBA基本構(gòu)架對象、屬性、事件、方法對象： 所謂的對象，我們可以將其想象成日常生活中所用的各類物品比如車子桌子、椅子等以車子的組成為例，車子是由輪子、車座、車燈、外殼等組合而成的一個大對象而這些具有單一功能的對象又可以分為更小的對象如：螺絲、電線等。 就EXCEL而言其對象

45、就是工作薄、工作表、圖表工作表或其他更細微的窗體組件。屬性： 所謂屬性就是對象所屬的特征，例如當年到一個蘋果時，可以馬上知道它的顏色形狀及大小這些都可以用來形容對象的形容詞也就是對象的屬性，比如工作表的名稱就是工作表的一個屬性。取得屬性值的語法：變量=對象.屬性比如我們要取得當前工作表的名子和工作表的數(shù)量AAA=ABBB=Activesheet.count設(shè)定屬性的語法:對象.屬性=屬性值例:sheets(1).name=我的工作薄1 設(shè)置第一個工作表的名稱是我的工作薄1事件: 所謂事件,就是發(fā)生在對象上的事情例如在按按上單擊鼠標這個動作就是發(fā)生于按紐上的事件，工

46、作表的增加，單元格的修改都是事件。下面的圖示中右邊的下拉框中都是WORKSHEET（工作表）的事件事件有什么用呢？程序我們平常并不希望它時時運行，所以我們需要在使用到它時再讓它運行，比如：你可以設(shè)置一個按紐用單擊這個事件去觸發(fā)程序的運行，當我們單擊按紐時就運行，不單擊就不運行。再如，我們有時候需要在改變某個工作表中的單元格時運行某個程序這時就可以用Worksheet_Change這個事件。方法:控制對象的動作或功能稱為方法,簡單的說就是為了達到某種效果而下達的命令:比如:對工作表這個對象進行新建或刪除的功能.而新建及刪除功能即為工作表這個對象所提供的方法.使用方法的程序語法.對象.方法例:SH

47、EET1.DELETE上例中SHEET1的部分為對象,而.后方的即為對象所提供的方法,至于對象提供的方法有哪些,并不需要特意去記憶它們,當輸入了對象名稱然后輸入.時，系統(tǒng)就會顯示該對象提供的方法菜單以便從菜單中選取適用的方法.舉例： 點擊“新建”并加入Sheets(sheet1).Range(a1) = 100Sheets(sheet1).Cells(2, 1) = 200Dim i As Integer DIM變量名稱AS 變量類型For i = 1 To 20Sheets(sheet1).Cells(i, 1) = iNext添加：With Application.WorksheetFun

48、ctionRange(a21).Value = .Sum(Range(a1:a20) 注意SUM前面要有個點才行Range(a22).Value = .Average(Range(a1:a20)End With在VBA不直接支持在EXCEL工作表中的一些函數(shù)，如果要調(diào)用就必須加上Application.WorksheetFunction宏：許多VBA高手都是從錄制宏入門VBA的，宏的功能就是將所操作的步驟記錄下來,以便以后的任何時候只要使用宏,就可以重復(fù)執(zhí)行操作過的步驟。有時在編程序時，忘記了EXCEL某些基本功能的編寫，錄制宏就是一個很好的方法，比如你忘記了不連續(xù)單元格的表示方法，你就可以在

49、工作表中把你選取不連續(xù)單元格的過程錄制下來，代碼自然就出來了。 錄制宏的操作： “工具”“宏”-點擊錄制宏按紐-在彈出的錄制新宏對話框中輸入宏名和快捷鍵（可不填）-進行事選設(shè)定的操作-停止錄制（其實這個和平時生活中錄音或錄像一樣，開始-錄制-停止），補充一點，就是如果以后想用按紐來執(zhí)行宏，可以直接在彈出的指定宏對話框中點擊錄制宏，其余步驟同上。案例25資產(chǎn)折舊 直線折舊法計算資產(chǎn)折舊=SLN（Cost,Satvage,Life） 固定余額遞減折舊法計算資產(chǎn)折舊=DB（Cost,Satvage,Life,Year） 雙倍余額遞減折舊法計算資產(chǎn)折舊=DDB（Cost,Satvage,Life,Ye

50、ar,2） 可變余額遞減折舊法計算資產(chǎn)折舊=VDB（Cost,Satvage,Life,StartPeriod,EndPeriod,Factor,NoSwitch） 年數(shù)總和折舊法計算資產(chǎn)折舊=SYD（Cost,Satvage,Life,Year）附1：投資決策分析進行投資分析需要為提供一系列現(xiàn)金流量的值。凈現(xiàn)值。內(nèi)部報酬率。凈現(xiàn)值和內(nèi)部報酬率是關(guān)系緊密的一對函數(shù)。當內(nèi)部報酬率資金成本時，凈現(xiàn)值。附2： 整合數(shù)據(jù)表我們經(jīng)常需要將幾個表格中的數(shù)據(jù)整合成一張表，如下圖所示，將5個月的數(shù)據(jù)（目前分布在5張表格上）整合到一個總表上：我們使用的方法是利用office的查詢工具query進行的，這樣做的優(yōu)

51、點在于今后對于新數(shù)據(jù)的更新可以自動化刷新，而不需要重復(fù)的進行整合操作（如復(fù)制粘貼）。office的查詢工具query在典型安裝office時是不會被安裝的，我們需要首先檢查我們的office是否已經(jīng)安裝了query，方法如下：l 打開Excel，點擊【數(shù)據(jù)】菜單中【導(dǎo)入外部數(shù)據(jù)】【新建數(shù)據(jù)庫查詢】，若系統(tǒng)提示安裝，則說明該功能未被安裝，此時可能需要在光驅(qū)中插入Office安裝光盤完成該功能的安裝。l 在光驅(qū)中插入安裝光盤后，安裝過程中選擇自定義安裝；點開Excel前面的加號，選中要安裝的功能前面的下拉菜單，選擇【從本機運行】；點開【Office工具】前面的加號，選中【Microsoft Query】的下拉菜單，選擇【從本機運行】，然后執(zhí)行余下的安裝過程即可。下面開始整合工作：選擇excel files*:選擇要導(dǎo)入數(shù)據(jù)的excel文件：出現(xiàn)選擇數(shù)據(jù)表的