1、-. z.E*cel案例01第1局部 E*cel根底概念名稱名稱可以代表一個單元格或者一個單元格區域，或者是常量，公式。名稱的定義：選中需要命名的單元格或區域，在界面左上角名稱框中輸入名稱后回車；名稱的刪除：插入名稱定義，選中需要刪除的名稱點擊刪除按鈕，點確定。名稱的引用：需要引用*單元格時輸入該單元格的名稱：名稱第1步：選中需要命名的*個單元格或單元格區域。第2步：在左上角名稱框輸入命名后回車。刪除已有命名：選擇插入名稱定義；選中需要刪除的名稱，點擊刪除按鈕。名稱命名的優點：防止絕對引用的錯誤對公式進展文字化表述，讓公式更加容易理解可以在整個工作簿中通用，引用方便常用引用函數row: 返回指

2、定單元格的行號column: 返回指定單元格的列標match: 返回查找值在查找*圍中的序號 =match(查找值，查找*圍，0) 其第三個參數為0，表示查找準確值address: 返回單元格名稱，其參數為行號列標或計算行號列標的表達式 =address(行號，列標) =address(1,1) 此公式返回A1indirect: 返回單元格的值.其參數為單元格名稱 =indirect(A1),假設A1=10,則indirect返回10inde*: 在*區域內查找*個位置的值 =inde*(查找區域，查找值所在的行號，查找值所在的列號)offset：指定基點，指定位移量，得到單元格引用 單個單元

3、格引用：=offset(基點單元格，向下移動的行，向右移動的列) 區域的引用：=offset(基點單元格，向下移動的行，向右移動的列，區域 包括的行數，區域包括的列數) 向上和向左移動時，位移量為負值。 =offset(A1,1,1) 將得到B2單元格的值動態引用動態引用是通過引用函數實現對于單元格或區域的相對引用。它和相對引用的效果很相似，但比簡單的相對引用用途廣泛。常用的動態引用的實現方法有：引用函數嵌套。比方offset和row,column嵌套；inde*和match，indirect,address嵌套等；引用函數與控件結合使用，此類控件包括組合框，滾動條。動態引用的作用：實現對于單

4、元格的動態引用；進展動態分析；制作動態報表。運算類型數值運算：1 + 1 = 2邏輯運算：1 0 = TRUE在邏輯運算中，TRUE=1，FALSE=0我們利用邏輯運算進展條件判斷在E*cel中常用的一些邏輯函數：if,and,or,not,iserror等and: 只有當所有條件全部滿足，才會返回true的邏輯值 語法：and(條件1，條件2，)or: 滿足其中任何一個條件，都會返回true的邏輯值 語法：or(條件1，條件2，條件3,)函數調用的語法函數名稱參數1，參數2，絕對引用和相對引用：絕對引用：所引用的單元格不隨著公式的復制而移動的引用方式。相對引用：所引用的單元格隨著公式的復制而

5、移動的引用方式。改變引用方式的方法：在公式欄中選中需要改變引用方式的單元格，按F4鍵給需要引用的單元格定義名稱，然后在公式中引用該名稱數組公式對單元格區域進展多重計算的計算方式。與普通計算公式的區別是錄入公式完畢后，需要同時按下 ctrl + shift + enter ，其特征是在公式兩端會出現一對大括號。如上圖例，使用一個公式計算出所有產品的金額合計，引用的是所有的單價和所有的數量，執行的是多重計算。錯誤提示E*cel中存在錯誤的類型，比方1/0=#DIV/0!, todas()=#NAMES!iserror是一個邏輯函數，用以判斷*個單元格內的值是否是一個錯誤，是錯誤則返回TRUE,不是

6、錯誤則返回FALSE.iserror有時可以和if函數嵌套進展一些較為復雜的判斷。有取值區間的隨機數=最小值最大值最小值*rand()循環引用是單元格引用其自身的引用方式。可以設置E*cel允許進展循環引用：工具-選項-重新計算：將迭代計算選項打鉤選中。行列互換函數方式：利用transpose函數+數組公式實現。首先選中行列數和原區域相反的一個區域；然后輸入transpose函數最后按下組合鍵ctrl+shift+enter第2局部 E*cel設置顯示當前文件的完整路徑菜單區域右鍵菜單Web顯示菜單項全部菜單視圖工具欄自定義選項始終顯示整個菜單鼠標移動方向工具選項編輯按Enter鍵后移動方向隱

7、藏界面要素工具選項視圖包括：網格線，滾動條，工作表標簽，行號列標等。以顯示值為準工具選項重新計算以顯示精度為準行的合計與列的合計有時出現不相等的情況。可以采用以下方法解決。選擇工具選項重新計算以顯示精度為準，選中該選項。自定義序列工具選項自定義序列改變文件保存位置工具選項常規默認文件位置改變文件用戶名工具選項用戶名單元格自動換行格式單元格對齊自動換行第3局部 E*cel根底操作快速選中數據表的整行或者整列ctrl + shift + 下箭頭/右箭頭快速選中區域ctrl + shift + 8行列互換復制需要進展行列互換的區域后，將光標放置在數據表外面位置，選擇性粘貼選中轉置選項一個單元格內容輸

8、入為多行alt + 回車鍵顯示公式ctrl + 凍結窗口選擇需要進展凍結的單元格位置，選擇窗口凍結窗格自定義格式語法： 大于條件值格式；小于條件值格式；等于條件值格式；文本格式縮放數值 0.00， 按百萬縮放 0.0，按萬縮放 0.00, 按千縮放不復制隱藏的行或列a首先選中需要復制的被隱藏了一些行或列的表格區域；b 然后點擊編輯-定位-定位條件，在其中選擇可見單元格；c 復制表格區域，粘貼即可。案例02 一 文本的處理等長文本的分割從字符串的左邊取字符：=Left (字符串，文本長度)從字符串的右邊取字符：=Right (字符串，文本長度)從字符串的中間取字符：=Mid (字符串，文本起始位

9、置，文本長度)不等長文本的分割第1步：選中要進展分割的字符串區域。第2步：點擊數據分列，在文本分列向導中選擇適合的分隔符。第3步：設置需要導入的列以及放置該列的位置。文本的合并=Concatenate(文本1，文本2，)使用連接符&：動態表頭：=ABC公司&YEAR(NOW()&年&MONTH(NOW()&月報表有效性選中需要設置有效性的區域，點擊數據有效性，在有效性條件中選擇序列，在來源中錄入或選取列表。輸入信息：事前提醒。出錯警告：事后提示。如果待選列表不在當前工作表中，需要首先為該列表定義名稱，然后在有效性來源框中輸入：=該列表的名稱有效性的其他用法不允許錄入重復數據的有效性設定：cou

10、ntif(e:e,e12)=1輸入的內容中必須包括*字符：=not(iserror(find(中國,g30)二級選項：首先將一級選項的每個工程定義一個名稱，該名稱內容包括相應的二級工程；制作一級工程的有效性；制作二級工程的有效性：內容為：=INDIRECT(g6)，其中g6為設定了有效性的一級選項所在的單元格。圈示無效數據。選擇工具公式審核顯示公式審核工具欄，點擊圈示無效數據按鈕。該工具可以將所有不符合有效性設定的內容圈示出來。二 日期的處理Datedif 函數：用于計算兩個日期之間的年數，月數，天數。=Datedif(開場日期，完畢日期，y)第三個參數：y：表示年數m：表示月數d：表示天數生

11、日提醒=DATEDIF(TODAY(),DATE(YEAR(TODAY(),MONTH(G2),DAY(G2),d)*日期的星期=weekday(A2,2) 返回的值是3，則表示該日期是星期三。兩個日期間的工作日天數=networkdays(開場日期，完畢日期，節假日列表)需要首先加載分析工具庫才能使用此工具案例03 數據查詢VLOOKUPVLOOKUP在表格或數值數組的首列查找指定的數值，并由此返回表格或數組當前行中指定列處的數值。VLOOKUP 中的 V 代表垂直。VLOOKUP(lookup_value,table_array,col_inde*_num,range_lookup)Loo

12、kup_value 為需要在數組第一列中查找的數值。Lookup_value 可以為數值、引用或文本字符串。也可以理解為：兩表共有的索引字段。Table_array 為需要在其中查找數據的數據表。可以使用對區域或區域名稱的引用，例如數據庫或列表。必須使得共有字段位于該*圍的第一列。col_inde*_num 為需要調轉的數據位于第二個參數中定義的*圍的第幾列。必須為單純數值。range_lookup 定義大致匹配或準確匹配。False或0:準確匹配；true或忽略或1：如果無法找到準確匹配的值，則就查找并匹配比查找值小的最近似的值。HLOOKUP()Hlookup中的H是水平的意思，在橫向排列

13、的表格中查詢數據用Hlookup，垂直排列的表格查詢用Vlookup函數。其參數含義與VLOOKUP近似。案例04 查詢表信息查詢表利用有效性和VLOOKUP函數實現。利用有效性實現索引字段的切換。利用VLOOKUP函數查詢與索引字段相關的信息。案例05 數據篩選自動篩選略高級篩選由于自動篩選只能篩選出簡單條件的數據，因此如要在復雜條件下進展篩選就需要采用高級篩選的方式。在需要進展篩選的數據表外設定篩選條件。在對話框中設定：列表區域為數據表區域；條件區域為剛剛在數據表外部設定的條件區域，要包括字段名稱和條件所在的單元格區域。條件格式條件格式可以使得符合特定條件的記錄按照*種設定的格式顯示。選中

14、要進展條件格式設定的單元格*圍執行【格式】【條件格式】在條件格式設置界面進展條件的設定：統計Frequency(需要進展統計的數據區域，分段點)對數據進展區間統計。此函數需要和數組公式一起使用。此函數是根據數值的大小劃分區間進展統計的。列出需要分析的工資區間，選中需要計算統計結果的單元格區域，輸入函數：按下組合鍵：ctrl + shift + enter排名次=rank(需排名數值，排名數據*圍，排序方式)排序方式：0或忽略降序，否則為升序。案例06 窗體控件的使用下拉菜單的制作。選擇視圖工具欄窗體，出現窗體工具條，在工具條上選擇組合框。按下鼠標左鍵，拖放出適宜的大小后釋放鼠標，出現下拉菜單形

15、狀。選中該下拉菜單，右鍵選擇設置控件格式在控制菜單上，去選擇相應的數據源。三維陰影選項是下拉菜單顯示效果設定。單元格：該處指定的單元格中將存儲在控件中被選中的工程在數據源中的序列號。我們經常利用該序列號引用控件選中的值，還可以利用該值制作動態圖表。根據報銷單上填報的日期自動生成該報銷單的起止日期。使用最大值函數MA*和最小值函數MIN。制作單項選擇按鈕。使用窗體工具條上的選項按鈕界面定制行號列標，滾動條，工作表標簽的隱藏。選擇工具選項視圖，將窗口選項中的行號列標，水平滾動條，垂直滾動條，工作表標簽選項去掉。案例07 匯總報表對于分布在同一文件中的表格匯總：首先創立格式完全一致的匯總表的表構造在

16、匯總表需要匯總的工程上輸入：=sum()將光標放入括號，用鼠標選擇第一*工作表，按下shift鍵，再選取最后一*工作表，然后選擇需要匯總的單元格，回車。將該公式復制到其他需要匯總的單元格即可實現整*表的匯總。對于分布在不同文件中的表格匯總：首先創立格式完全一致的匯總表的表構造將光標置于起始報表工程上選擇【數據】【合并計算】引用位置選擇各個需要被合并的報表，點擊添加，直至所有需要被合并的報表添加完成。需要刪除一*表格時，只需要在合并計算界面上選中該表格，點擊刪除按鈕。當子表格數據發生變化需要更新匯總表數據時，只需執行：數據-合并計算，進入合并計算界面后直接點擊確定退出該界面即可完成刷新數據的操作

17、。案例08 報表保護單元格區域的保護。首先取消不需要保護的單元格區域的鎖定狀態。右鍵設置單元格格式，在保護標簽上將鎖定選項取消。如果需要隱藏單元格公式內容，可以將隱藏選項選中。選擇工具保護保護工作表，輸入保護密碼。隱藏公式右鍵菜單設置單元格格式保護選中隱藏選項，然后執行對工作表的保護隱藏工作表：隱藏工作表后需要保護工作簿。案例09 按鈕的制作選擇視圖工具欄窗體，調出窗體工具條。選擇工具條上的命令按鈕。繪制出按鈕的根本形狀后，自動跳出指定宏對話框，點擊新建按鈕，出現VBA編程界面。在Sub和End Sub之間輸入：sheets (要跳轉的工作表名稱).select，關閉該窗口。選中該按鈕，右鍵菜

18、單編輯文字，可以修改按鈕名稱。案例10 財務報表利用e*cel創立財務報表，要注意報表之間的勾稽關系。一方面是出于高效率管理報表的考慮，另一方面，可以幫助我們分析報表中的數據，并快速檢查出報表中的可能錯誤。為了能夠自動維護報表間的數據關系，我們經常需要使用單元格，將所有的表起來。注意方式請使用并聯而非串聯。串聯導致關系更加復雜且難以維護，而并聯的方式將只有一個統一的數據源，關系簡單不容易出錯，而且易于管理。比方損益表，資產負債表，現金流量表，需要使用公式和使得這些表起來，該關系能夠自動維護和更新多表之間的勾稽關系，時刻能夠保證報表數據之間的勾稽的正確性。具體公式參見電子文檔。案例11 表格的幾

19、種類型數據表可以分為數據列表，二維表，多維表和帶有格式的報表等幾種類型。數據表類型操作目標匯總方法是否需要重復制作是否與數據源有關系多字段數據列表記錄疊加并按表名區分導入外部數據 + SQL語句不需要有二維表多個二維表生成透視表透視表多維數據區域合并不需要有同一文件內的報表數據匯總=sum(begin:end!B2)不需要有不同文件中的報表數據匯總合并計算不需要有非標準數據表匯總建議修改原表的構造或者僅作為終端報表，上層再建立一個數據源表作為收集數據使用需要無案例12 數據透視表應用數據透視表進展各類數據分析。選擇數據數據透視表和數據透視圖，進入透視表設置向導。點擊下一步，選擇正確的數據*圍。

20、點擊下一步，選擇布局按鈕。在如以下圖界面上，將右側的字段拖入左側相應區域內。形成如以下圖的布局，點擊確定按鈕。生成如下的透視表后，將鼠標懸停在想要移動的字段上，鼠標左鍵按下，將字段拖放到其他區域，生成自己需要的數據顯示。數據百分比顯示：右鍵菜單字段設置選項，將數據顯示方式改為占同列數據總和的百分比同時顯示數據與百分比：在【布局】里將銷售收入兩次拖入數據區域將其中一個銷售收入改為百分比顯示在報表工程上輸入新的名稱可以修改工程名稱將報表工程拖拽到列標題位置，可以將數值和百分比改為按列排列篩選數據：點擊字段名稱后面的下拉菜單，可以對字段內容進展篩選。顯示明細數據：雙擊需要查看明細的數據，在新的工作表

21、上將顯示構成此匯總數據的所有明細數據。對數據進展排序：把光標放在行位置的報表工程上，【右鍵菜單】【字段設置】，【高級】按鈕，左側可以進展排序設置。按照日期分組：將日期放入行區域，【右鍵菜單】【組及顯示明細數據】【組合】在【步長】中選擇需要的分組標準按照數值分組：將銷售收入放入行區域，【右鍵菜單】【組及顯示明細數據】【組合】在【步長】中選擇需要的分組標準插入計算字段：在【名稱】后給計算字段定義名稱在【公式】后輸入計算字段的計算公式，可以引用下面的字段自動更新：【右鍵菜單】【表格選項】【翻開時刷新】可以讓報表始終有更新后的數據顯示。透視圖可以單獨生成數據透視圖，也可以基于現有的透視表生成透視圖。如

22、果已經有現成的透視表，通過點擊數據透視表工具條上的圖表按鈕可以生成一個透視圖。通過拖動透視圖上的各個字段到右側或者底部的位置，可以方便地改變圖表組織數據的方式。隱藏透視圖字段按鈕：把光標懸停在任意字段上，右鍵菜單，選擇【隱藏數據透視圖字段按鈕】，即可以將字段按鈕隱藏；再次顯示字段按鈕：點擊透視圖工具條上第一項，在出現的下拉菜單中選擇【隱藏數據透視圖字段按鈕】如果有些數據無法在數據透視表內局部析，可以引用透視表的數據作為分析的根底。制作試算平衡表點擊數據并拖拽到匯總隱藏數據透視表中的匯總字段或單元格格式自定義功能格式設定為：紅色借貸不平衡;紅色金額借貸不平衡；藍色借貸平衡Ctrl+拖拽標簽來復制

23、工作表編制損益表創立損益表格式創立IS嵌套函數IS 類函數，可以檢驗數值的類型并根據參數取值返回 TRUE 或 FALSE。函數如果為下面的內容，則返回 TRUEISBLANK值為空白單元格。ISERR值為任意錯誤值除去 #N/A。ISERROR值為任意錯誤值#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME 或 #NULL!。ISLOGICAL值為邏輯值。ISNA值為錯誤值 #N/A值不存在。ISNONTE*T值為不是文本的任意項注意此函數在值為空白單元格時返回 TRUE。ISNUMBER值為數字。ISREF值為引用。ISTE*T值為文本。創立財務比率銷售毛利率=銷

24、售毛利/銷售凈額毛利率大，表示經營能力強。銷售本錢率=銷售本錢/銷售凈額本錢率低，表示經營能力好。凈利率率=凈利潤/銷售凈額凈利潤率越高，表示經營能力強。營業比率=銷售本錢+營業費用/銷售凈額，營業比率越低，表示經營能力越強營業費用率=銷售費用/銷售凈額營業費用越低，表示經營績效越好。用MA*、MIN函數顯示制表日期MA*，返回一組值中的最大值MIN，返回一組值中的最小值案例13 多重合并計算數據如以下圖格式的即為二維表：現在我們需要將數個格式一樣的二維表匯總為一*表格，且可以區分不同表格屬性進展分析，比方三*表分別為，*分公司的表格。我們使用的方法是利用透視表多重合并計算數據區域的功能。選擇

25、數據-數據透視表和數據透視圖選擇第3個數據源類型：多重合并計算數據區域，點擊下一步按鈕：在出現的界面上選擇：自定義字段選擇需要被合并的表格區域，點擊添加將其添加到所有區域；將頁字段數據改為1，在項標志處輸入該表格的標志，使用一樣的方法將其他需要合并的表格全部添加。透視表生成后如以下圖所示，已經將多個表格合并成一個：雙擊字段名，可以對字段名進展修改：案例14 回歸與預測回歸分析說明事物之間相互影響的關系，主要用于分析單個因變量如何受一個或多個自變量影響的。比方*地空調的銷量與氣溫的變化之間的關系。可以用統計獲得的歷史數據，對未來的數據進展預測。可以利用它幫助經營，財務，銷售，營銷等方面的決策。趨

26、勢線預測根據歷史數據制作出折線圖選中圖表中的折線，右鍵菜單添加趨勢線選擇預測類型可以先選擇默認的線性切換到選項標簽，根據需要預測的期間數，在前推處輸入預測期間個數，并選中顯示公式和顯示R平方值選項根據出現的回歸系數R平方判斷預測類型是否適合，R平方越接近1越好多項式除外，需要考慮業務數據是否存在較大的波動，如果沒有則不適用多項式分析類型根據回歸方程求預測值移動平均沒有R平方，不適用此分析方法。Correl函數判斷相關性。相關系數在0.8以上為強相關，0.6以上為弱相關。案例15-16 數據分析與圖表分析日常工作中主要用到的數據分析有：構成：局部與整體比例關系。序列：在一個時間序列內進展的比擬與

27、分析。差異：預算與實際數的版本差異等。增長：增長率與增速，進展趨勢預測。勾稽：數據之間固有的相互聯系的屬性，利用此屬性進展數據合理性的判斷。圖表用于形象地展示數據。利用圖表可以更好地對業務數據進展分析。差異分析：利用上面的表格制作面積圖，數據區域選擇C25:D31選中B公司數據系列，右鍵菜單數據系列格式將其內部填充色和邊框色都改為無圖表上將只顯示差異局部，如以下圖：雙坐標圖表：利用下面的數據制作雙坐標圖表：選擇插入-圖表，進入圖表向導，選擇柱形圖，第一種子圖表類型簇狀柱形圖。選擇視圖-工具欄-圖表，調出圖表工具條：點開圖表工具條上下拉菜單，選擇 系列完成率點擊圖表工具條上數據系列格式按鈕：在數

28、據系列格式界面上，切換到坐標軸標簽，將系列繪制在由主坐標軸改為次坐標軸再次選中系列完成率，右鍵菜單項選擇擇圖表類型選擇折線圖，點確定：雙坐標圖表制作完成。氣泡圖：利用下表制作氣泡圖：在圖表向導中選擇氣泡圖因為氣泡圖沒有分類軸其橫坐標為序列號，我們需要添加數據標識，點擊下一步，切換到數據標志標簽，將系列名稱打勾：縱坐標表示%Growth,氣泡大小表示Cost:單元格嵌入式圖表：利用下表制作如圖中所示的嵌入式圖表在單元格中敲入如下公式：=REPT(|,B2/400)其中，除數的值由源數據的大小決定，源數據大，除數應相應變大，其意義在于縮減豎線的數量，使得單元格可以容納由一系列豎線構成的圖表。復合餅

29、圖：當數據系列內值的大小差異較大時，可以制作復合餅圖以便數據顯示更為清楚，如以下圖所示：選擇餅圖中的復合餅圖子圖表類型：點擊下一步，切換到圖例標簽，將顯示圖例的勾去掉：切換到數據標志標簽，把類別名稱和百分比打勾：選中數據系列，右鍵菜單，選擇數據系列格式：切換到選項標簽，將第二繪圖區包含最后的值改為：4復合餅圖制作完成。圖表組合：一個圖表中的不同數據系列可以采用不同的圖表類型顯示，如以下圖：首先制作一個柱形圖：在圖表工具條上選擇數據系列 pensation:在圖表工具條上選擇數據系列格式按鈕：在數據系列格式界面上，切換到坐標軸標簽，將主坐標軸改為次坐標軸：右鍵點擊pensation,選擇圖表類型

30、：將其圖表類型改為折線圖；用同樣的方式操作Production系列，將其圖表類型改為面積圖；完成圖表組合的制作。下拉菜單式圖表：寫入如下的公式：inde*函數查找出的值由A9單元格來指定。制作三維餅圖，其數據區域為A3:I3,A9:I9,需要手工選取。點擊視圖-工具欄-窗體，調出窗體工具條：在窗體工具條上選擇組合框，在餅圖上拖放出一個組合框：右鍵選中組合框，在菜單中選擇設置控件格式：在控制標簽中做如下設置：其中單元格為存儲控件選中工程序號的單元格。完成下拉菜單式圖表的制作：帶有滾動條的圖表：該圖表中可以隨著點擊滾動條的動作而動態翻看源數據中的大量數據。定義兩個動態引用的名稱：Period: =

31、offset(7.動態圖表!$A$1,7.動態圖表!$D$1,0,10,1)Data: =offset(7.動態圖表!$B$1,7.動態圖表!$D$1,0,10,1)制作折線圖，選擇系列標簽，刪除period系列：將值和分類*軸標志設置為如以下圖內容：調出窗體工具條，在圖表外繪制滾動條，右鍵選中滾動條，選擇設置控件格式：在單元格中設置為D1:甘特圖如果想制作如以下圖的甘特圖，需要以下4列數據：首先制作堆積條形圖：點擊下一步，切換到系列標簽，刪除完畢日期系列：生成的條形圖如以下圖所示：下面轉換縱坐標的工程排列次序：雙擊縱坐標，切換到刻度，將分類次序反轉和數據軸穿插于最大分類選項打勾。此時縱坐標次

32、序已經反轉雙擊開場日期數據系列，在數據系列格式對話框中將邊框和內部都改選為無，此時開場日期數據系列隱藏。此時需要將橫坐標開場日期改為真正的工程開場日期：在e*cel中，每一個日期都對應一個數值，選中B2,查看2008-7-1對應數值為39630雙擊橫坐標，切換到刻度，將最小值改為39630，同樣將橫坐標最大值改為真正的工程完畢日期。甘特圖制作完成。案例17動態圖表選中，*以下的區域，分別定義，*的名稱選擇視圖-工具欄-窗體，選擇選項按鈕，畫出如下三個選項按鈕：右鍵選中選項按鈕，在右鍵菜單中選擇設置控件格式，在跳出的對話框中選擇控制標簽，在單元格中選擇A16：在A17單元格中輸入公式：=choo

33、se(a16,)，為A17定義名稱：choose在A2,A3,A4單元格中輸入以下內容：在B16單元中寫入以下公式，并復制到該行1-12月的單元格中：=VLOOKUP(A3,INDIRECT(choose),COLUMN()-1,0)依據此數據表制作雙曲線圖：案例18 比率分析利用透視表靈活的特性，將兩個構造完全一樣的透視表相除，即得到可以進展任意兩個因素間的比率分析圖表。案例19 邏輯判斷IF(條件判斷，如果條件滿足則返回的結果，如果條件不滿足則返回的結果)根據工齡計算員工年假。年假規則：規定：公司工齡小于1年的，享受10天年假；大于1年小于10年的，工齡每增加一年，年假增加1天；增長到20

34、天不再增加。使用IF函數嵌套實現。單元格F3為工齡。=IF(F31,10,IF(F3100000,(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

35、,(K3-1600)*0.1-25,(K3-1600)*0.05)自動計算加班費=IF(ISERROR(VLOOKUP(D2,$L$2:$L$12,1,FALSE),IF(OR(G2=6,G2=7),周末加班,工作日加班),節假日加班)自動計算請假扣除=IF(OR(D5=病假,D5=事假),E5*1,IF(D5=曠工,E5*3,0)隱藏錯誤提示=IF(ISERROR(表達式),表達式)案例20 計數與求和條件計數countif(range，criteria)countif(A1:A100，8)條件求和如果滿足*個條件，就對該記錄里的指定數值字段求和。在第一個參數所在的區域里面查找第二個參數指定

36、的值，找到后對第三個參數指定的字段進展求和。sumif(range, criteria, sum_range)sumif(A1:A100, 海*, E1:E100) 對A列中第4個字為海的E列的值求和模糊條件求和sumif(a1:a100, *&海&*, e1:e100) 對A列中包含海字的E列的值求和sumif對多個條件進展求和=SUM(SUMIF(F:F,F2:F3,E:E)=SUM(SUMIF(F:F,陳露,程靜,E:E)criteria為常量，普通公式；criteria為單元格引用，需要使用數組公式。sum與數組公式聯手計數=sum(a1:a1001000)*(a1:a1005000)

37、sum與數組公式聯手求和=sum(條件1)*(條件2)*.*求和區域)sumproduct的使用返回相應的數組或區域的乘積的和。可以使用sumproduct替代sum數組公式的使用用于多條件求和。sumproduct(條件1)*(條件2)*) 用于計數sumproduct(條件1)*(條件2)*求和區域) 用于求和案例21 模擬運算表略案例22 單變量求解如果希望利潤到達200萬，則需要相應調整的單價、直接本錢、固定本錢、銷售量為多少？可變單元格：要求解的值所在的單元格目標單元格：關系式表達所在的單元格目標值：關系式的值單變量求解的原理：根據在單元格中描述的一元方程求解方程中的未知數。使用單變

38、量求解的前提：需要在單元格中將一元方程的方程式描述清楚。如果是函數關系，則需要在單元格中引用該函數。案例23規劃求解步驟：構建條件的關系模型，然后運行規劃求解工具。構建模型的過程實際上是利用E*cel單元格引用的方式將條件描述清楚的過程。在規劃求解對話框中，進展相關設置。目標單元格為求解后到達的目標。可變單元格為要求解的單元格。在約束中添加的其他條件。規劃求解可以求解多元方程，可以解決求極值的問題，可以替代計算較為復雜的計算過程。案例24 自定義函數宏的平安性級別調整點擊：工具-宏-平安性如果定義了宏或者自定義函數并且想使用它們，則需要將宏的平安性級別調整為中或者低。自定義函數：進入VB編輯器

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

40、mitation(job)If job = 總經理Or job = 市場銷售 Thenlimitation = 1500ElseIf job = 客戶效勞 Or job = 副總經理 Thenlimitation = 1200ElseIf job = 職能管理 Or job = 業務總監 Thenlimitation = 800ElseIf job = 產品 Or job = 采購 Thenlimitation = 600ElseIf job = 研發技術Or job = 生產運作 Thenlimitation = 400Else: limitation = 100End IfEnd Func

41、tion附：VBA簡介VBA簡介：VBA 是 Visual basic for Application 的縮寫。是用來開發應用程序的Visual basic語言，除了它無法象Visual basic般獨立存在外，其它的如程序的編輯方法，對象的使用等，都與Visual basic一樣，因此如果學習過Visual basic語言，入門VBA將是一件很容易的事。VBE是Visual basic Editor的縮寫,是用來編寫VBA應用程序的編輯器,它與VB編器有什么區別呢Visual basic是用來開發完整程序的工具,必須使用相當大的系統資源,但是在控制E*CEL或其他OFFICE辦公軟件時,并不

42、需要使用這么龐大的資源,因此Microsoft將Visual basic編寫及執行程序的功能附屬于OFFICE之下，使其可以使用較少的系統資源，到達編輯出應用程序的目的。因此將提供這些功能的編輯器稱為Visual basic Editor。E*CELVBA根本構架對象、屬性、事件、方法對象：所謂的對象，我們可以將其想象成日常生活中所用的各類物品比方車子桌子、椅子等以車子的組成為例，車子是由輪子、車座、車燈、外殼等組合而成的一個大對象而這些具有單一功能的對象又可以分為更小的對象如：螺絲、電線等。 就E*CEL而言其對象就是工作薄、工作表、圖表工作表或其他更細微的窗體組件。屬性：所謂屬性就是對象所

43、屬的特征，例如當年到一個蘋果時，可以馬上知道它的顏色形狀及大小這些都可以用來形容對象的形容詞也就是對象的屬性，比方工作表的名稱就是工作表的一個屬性。取得屬性值的語法：變量=對象.屬性比方我們要取得當前工作表的名子和工作表的數量AAA=ABBB=Activesheet.count設定屬性的語法:對象.屬性=屬性值例:sheets(1).name=我的工作薄1 設置第一個工作表的名稱是我的工作薄1事件:所謂事件,就是發生在對象上的事情例如在按按上單擊鼠標這個動作就是發生于按紐上的事件，工作表的增加，單元格的修改都是事件。下面的圖示中右邊的下拉框中都是WORKSHEET

44、工作表的事件事件有什么用呢？程序我們平常并不希望它時時運行，所以我們需要在使用到它時再讓它運行，比方：你可以設置一個按紐用單擊這個事件去觸發程序的運行，當我們單擊按紐時就運行，不單擊就不運行。再如，我們有時候需要在改變*個工作表中的單元格時運行*個程序這時就可以用Worksheet_Change這個事件。方法:控制對象的動作或功能稱為方法,簡單的說就是為了到達*種效果而下達的命令:比方:對工作表這個對象進展新建或刪除的功能.而新建及刪除功能即為工作表這個對象所提供的方法.使用方法的程序語法.對象.方法例:SHEET1.DELETE上例中SHEET1的局部為對象,而.前方的即為對象所提供的方法,

45、至于對象提供的方法有哪些,并不需要特意去記憶它們,當輸入了對象名稱然后輸入.時，系統就會顯示該對象提供的方法菜單以便從菜單中選取適用的方法.舉例：點擊新建并參加Sheets(sheet1).Range(a1) = 100Sheets(sheet1).Cells(2, 1) = 200Dim i As Integer DIM變量名稱AS 變量類型For i = 1 To 20Sheets(sheet1).Cells(i, 1) = iNe*t添加：With Application.WorksheetFunctionRange(a21).Value = .Sum(Range(a1:a20) 注意S

46、UM前面要有個點才行Range(a22).Value = .Average(Range(a1:a20)End With在VBA不直接支持在E*CEL工作表中的一些函數，如果要調用就必須加上Application.WorksheetFunction宏：許多VBA高手都是從錄制宏入門VBA的，宏的功能就是將所操作的步驟記錄下來,以便以后的任何時候只要使用宏,就可以重復執行操作過的步驟。有時在編程序時，忘記了E*CEL*些根本功能的編寫，錄制宏就是一個很好的方法，比方你忘記了不連續單元格的表示方法，你就可以在工作表中把你選取不連續單元格的過程錄制下來，代碼自然就出來了。錄制宏的操作： 工具宏點擊錄制

47、宏按紐在彈出的錄制新宏對話框中輸入宏名和快捷鍵可不填進展事選設定的操作停頓錄制其實這個和平時生活中錄音或錄像一樣，開場錄制-停頓，補充一點，就是如果以后想用按紐來執行宏，可以直接在彈出的指定宏對話框中點擊錄制宏，其余步驟同上。案例25資產折舊直線折舊法計算資產折舊=SLNCost,Satvage,Life固定余額遞減折舊法計算資產折舊=DBCost,Satvage,Life,Year雙倍余額遞減折舊法計算資產折舊=DDBCost,Satvage,Life,Year,2可變余額遞減折舊法計算資產折舊=VDBCost,Satvage,Life,StartPeriod,EndPeriod,Facto

48、r,NoSwitch年數總和折舊法計算資產折舊=SYDCost,Satvage,Life,Year附1：投資決策分析進展投資分析需要為提供一系列現金流量的值。凈現值。內部報酬率。凈現值和內部報酬率是關系嚴密的一對函數。當內部報酬率資金本錢時，凈現值。附2： 整合數據表我們經常需要將幾個表格中的數據整合成一*表，如以下圖所示，將5個月的數據目前分布在5*表格上整合到一個總表上：我們使用的方法是利用office的查詢工具query進展的，這樣做的優點在于今后對于新數據的更新可以自動化刷新，而不需要重復的進展整合操作如復制粘貼。office的查詢工具query在典型安裝office時是不會被安裝的，

49、我們需要首先檢查我們的office是否已經安裝了query，方法如下：翻開E*cel，點擊【數據】菜單中【導入外部數據】【新建數據庫查詢】，假設系統提示安裝，則說明該功能未被安裝，此時可能需要在光驅中插入Office安裝光盤完成該功能的安裝。在光驅中插入安裝光盤后，安裝過程中選擇自定義安裝；點開E*cel前面的加號，選中要安裝的功能前面的下拉菜單，選擇【從本機運行】；點開【Office工具】前面的加號，選中【Microsoft Query】的下拉菜單，選擇【從本機運行】，然后執行余下的安裝過程即可。下面開場整合工作：選擇e*cel files*:選擇要導入數據的e*cel文件：出現選擇數據表的

50、界面：如果以上界面提示錯誤，點擊以上界面的選項按鈕，出現以下界面，將系統表打勾即可：任選一個字段到右側，點下一步按鈕：直到以下界面出現，選擇第二項，點完成按鈕：此時出現query程序界面，點擊工具條上SQL按鈕：出現以下界面：在界面中將SQL語句改為以下：出現以下提示，點確定按鈕：出現數據查詢結果，選文件菜單-將數據返回E*CEL:選擇在新工作表中存儲數據：幾*數據表被成功地整合在一起。該查詢數據表與數據源保持著動態關系，可以隨時刷新數據，不需要重復操作。右鍵菜單里選擇刷新數據即可對數據進展刷新。附3：對于數據庫的訪問方法ODBC的介紹存儲數據并不是E*cel的特長，E*cel設計和優化的目的

51、是綜合、分析及顯示數據。它僅有一些根本的數據庫功能，當用戶覺得簡便的重用性大于功能時，就應該使用它們。E*cel中有一些方法，可以將這些工具和真實數據庫管理系統嚴密結合在一起，利用這些方法，用戶可以創立一些系統，在數據庫管理系統的強大功能和工作表的功能中到達一種平衡。ODBC是開放式數據庫互連Open Database Connectivity的縮寫。它是一種標準，許多數據庫程序和數據庫管理系統都采用這種標準。如果用戶有一個數據源，如與ODBC兼容的Access數據庫，則就可以將數據輕松的導入到E*cel中。不是所有的數據庫都有同樣的構造。為了確定這些數據庫是否可以和其他應用軟件相連，ODBC

52、軟件生產商提供了驅動程序，在安裝Microsoft Office時，按轉程序會自動地為Access、Parado*、d BASE以及SQL Server等安裝幾個驅動程序。在將數據從數據庫導入到E*cel中之前，用戶需要確認自己想使用的數據庫類型。這樣，E*cel才能知道使用何種驅動程序和數據庫相連。在確定想要使用的數據源類型后，還必須提供一個查詢query。查詢是用標準的構造查詢語言寫出的一系列指令，這種查詢語言一般形成SQL.那些指令提供了一些信息，如使用數據庫中的哪些表，需要表中哪些字段的數據，以及在選擇要檢索記錄的過程中是否運用*些標準等。Microsoft Office還有一個叫做M

53、icrosoft Query的程序。從數據庫導入數據不久，Microsoft Query就開場運行了。它將幫助用戶設計查詢，并從數據庫中檢索所需要的記錄。從數據庫導入數據是一個能夠兩步完成的過程：首先確定數據源，然后再創立查詢命令。既可以通過數據透視表進展外部數據源，也可以通過數據導入外部數據進展外部數據源，這里用數據透視表連接，該方法的優點在于連接好數據后可以直接利用透視表進展分析。在透視表向導頁面上選擇外部數據源。點擊獲取數據。選擇新數據源，按確定按鈕。輸入數據源名稱，選擇相應的數據庫驅動，點擊連接。在以下圖界面上點擊選擇按鈕。在以下圖界面上去相應路徑選擇數據庫，在左側選中數據庫后點擊確定

54、按鈕。回到以下圖界面后選擇默認表。新數據源建好后出現在數據源列表中，選中后確定。在數據表中選擇需要的字段。可以利用*字段對數據進展篩選。點擊完成，進入透視表設置界面。對透視表進展布局，以下步驟同透視表操作。PowerPoint在管理中的應用第一局部：根底概念演示文稿構成要素文本圖示表格色彩明確演示文稿的用途演講目的文檔目的演示文稿的分類展示型：演講目的，以圖示為主內容型：文檔目的，以文本，表格為主展示型演示文稿制作原則：信息可視化提綱挈領的文本圖示替代文本圖表替代數據的羅列信息以一種直觀的方式展現，可以更好地傳遞演講內容。要求一定程度的視覺效果。賞心悅目的畫面有利于人們增強對演示內容的記憶，但

55、是不要為了裝飾而裝飾，如此反而會分散觀眾的注意力。不追求內容的全面，而是追求重點的突出，強化觀眾對于最主要內容的關注。如果想詳細地全面地對主題進展說明，可以使用WORD或者E*CE。大段的文字演示的效果會很差，是演示文稿的忌諱。要想方設法把文字轉化為各種直觀的圖形來說明和展示。展示型演示文稿的特點：邏輯性可視化內容型演示文稿制作原則框架布局必要的修飾第二局部：演示文稿構成元素操作新建一個演示文稿。ctrl+M：插入新幻燈片頁面應用版式。選擇格式幻燈片版式，調出版式工作區，選擇一種適合的版式。版式：指幻燈片內容在幻燈片上的排列方式，由占位符組成。占位符：一種帶有虛線或陰影線邊緣的框，絕大局部幻燈

56、片版式中都有這種框。在這些框內可以放置標題及正文，或者是圖表、表格和圖片等對象。可以將版式中的占位符移動到不同位置，調整其大小，以及使用填充顏色和邊框設置其格式。也可以自己進展版式設定。應用模版。選擇格式幻燈片設計，調出設計工作區，選擇一種適合的模版。可以對*頁幻燈片或者整個文件應用*個設計模版。修改模版：進入母版視圖。在應用設計模板時，會在演示文稿上添加幻燈片母版。幻燈片母版是存儲關于模板信息的設計模板設計模板：包含演示文稿樣式的文件，包括工程符號和字體的類型和大小、占位符大小和位置、背景設計和填充、配色方案以及幻燈片母版和可選的標題母版。這些模板信息包括字形、占位符大小和位置、背景設計和配

57、色方案。幻燈片母版的目的是使您進展全局更改如替換字形，并使該更改應用到演示文稿中的所有幻燈片。通常可以使用幻燈片母版進展以下操作：更改字體或工程符號 插入要顯示在多個幻燈片上的藝術圖片如徽標 更改占位符的位置、大小和格式 更改幻燈片母版時，已對單*幻燈片進展的更改將被保存。一個演示文稿，可以應用多個設計模版。選定幻燈片，選擇新的模版，選擇應用于選定幻燈片。自定義模板：新建PowerPoint文件；點擊視圖-母版-幻燈片母版；在母版視圖上進展模板的設計，包括背景效果，標題區，頁腳區效果，字體，工程符號等；設計完畢后關閉母版視圖；點擊文件-另存為，將文件保存類型改為演示文稿設計模板，模板制作完成。

58、導入演示文稿【插入】【幻燈片從文件】通過設置左下角的【保存源格式】選項，可以選擇是否保存被導入的頁面的原始格式。插入的頁面將沿用它上面一頁的格式信息。文本工程符號級別如果存在多個級別的工程符號，按【Tab】可以將文本降為下一級別，【Shift】【Tab】將文本升為上一級別。如果需要調整同一級別下工程符號的前后順序，可以點擊【視圖】【工具欄】【大綱】，調出大綱工具條，點擊【上移】按鈕向上調整順序，點擊【下移】按鈕向下調整順序。插入頁腳，頁碼【視圖】【頁眉和頁腳】，在界面上定制相應內容：自動調整自動調整智能標記可以用來調整文本，使之適合當前占位符的大小。段落格式首行縮進左縮進首行縮進：將段落中的第

59、一行向右縮進。左縮進： 所有行均向右縮進。動態表格。在e*cel中將表格選中，切換到PPT界面，選擇編輯選擇性粘貼，選擇粘貼。為了防止其他閱讀者翻開文件時看到是否更新的提示信息，可以在將文件發送給對方之前斷開。編輯斷開使用控件Active*控件是用于向用戶提供選項或者運行使任務自動化的宏或者腳本。可以在VBA中編寫控件的宏或者在腳本編輯器中編寫腳本。可以在幻燈片中添加控件，以在幻燈片放映時與用戶交互信息。比方，在為展臺設計的幻燈片中可以使用控件使得觀眾可以選擇放映的內容。控件所在的位置：屬性查看當前所選控件的屬性查看代碼啟動VB編輯器查看或編輯當前控件代碼復選框文本框命令按鈕單擊時可以啟動*個

60、操作的按鈕選項按鈕單項選擇按鈕列表框包含工程列表的框組合框帶有下拉列表框的文本框；可以選擇或輸入切換按鈕單擊時保持按下狀態，再次單擊復原數值調節鈕可附加到文本框的按鈕滾動條滾動顯示內容標簽用于提供有關控件信息的文本圖像將圖片嵌入到演示文稿中的控件其他控件在有限區域內顯示更多文本內容功能的實現選擇控件工具箱中文字框選項，在編輯區按住鼠標左鍵拖拉出一個文本框，調整位置及大小。在文字框上擊鼠標右鍵，選擇屬性，彈出文字框屬性窗口，在屬性窗口中對文字框的一些屬性進展設置。EnterKeyBehavior屬性：設為True時允許使用回車鍵換行。MultiLine屬性：設為True時允許輸入多行文字。Scr