【文章內(nèi)容簡介】 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(F310,9+F3,20))2. IF 和OR ，AND嵌套使用用于執(zhí)行更為復(fù)雜的判斷if(or(條件1，條件2，…)，若條件滿足則返回的結(jié)果，若條件不滿足則返回的結(jié)果)if(and(條件1，條件2，…)，若條件滿足則返回的結(jié)果，若條件不滿足則返回的結(jié)果)3. 計算工資及所得稅=IF((K31600)100000,(K31600)*,IF((K31600)80000,(K31600)*,IF((K31600)60000,(K31600)*,IF((K31600)40000,(K31600)*,IF((K31600)20000,(K31600)*,IF((K31600)5000,(K31600)*,IF((K31600)2000,(K31600)*,IF((K31600)500,(K31600)*,(K31600)*))))))))4. 自動計算加班費=IF(ISERROR(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(range, criteria, sum_range)sumif(A1:A100, “???海*”, E1:E100) 對A列中第4個字為海的E列的值求和3. 模糊條件求和sumif(a1:a100, “*”amp。”?！盿mp。”*”, e1:e100) 對A列中包含“?！弊值腅列的值求和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)手求和{=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)整的單價、直接成本、固定成本、銷售量為多少？可變單元格：要求解的值所在的單元格目標(biāo)單元格：關(guān)系式表達所在的單元格目標(biāo)值：關(guān)系式的值單變量求解的原理：根據(jù)在單元格中描述的一元方程求解方程中的未知數(shù)。使用單變量求解的前提：需要在單元格中將一元方程的方程式描述清楚。如果是函數(shù)關(guān)系，則需要在單元格中引用該函數(shù)。案例23　規(guī)劃求解步驟：構(gòu)建已知條件的關(guān)系模型，然后運行規(guī)劃求解工具。構(gòu)建模型的過程實際上是利用Excel單元格引用的方式將條件描述清楚的過程。在規(guī)劃求解對話框中，進行相關(guān)設(shè)置。目標(biāo)單元格為求解后達到的目標(biāo)。可變單元格為要求解的單元格。在約束中添加已知的其他條件。規(guī)劃求解可以求解多元方程，可以解決求極值的問題，可以替代計算較為復(fù)雜的計算過程。案例24 自定義函數(shù)宏的安全性級別調(diào)整點擊：工具宏安全性如果定義了宏或者自定義函數(shù)并且想使用它們，那么需要將宏的安全性級別調(diào)整為中或者低。自定義函數(shù)：1. 進入VB編輯器2. 選中左側(cè)工程管理器中的當(dāng)前工作簿，查看其下項目中是否存在“模塊”。如果不存在，點擊【插入】菜單，選擇“模塊”；如果存在，則略過此步驟。3. 雙擊模塊下的某一模塊，右側(cè)將彈出該模塊的代碼窗口，我們將在此代碼窗口中編寫自定義函數(shù)。4. 在右側(cè)代碼窗口輸入以下語句：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é)束語句。帶有判斷的自定義函數(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 = 400Else: limitation = 100End IfEnd Function附：VBA簡介VBA簡介：VBA 是 Visual basic for Application 的縮寫。是用來開發(fā)應(yīng)用程序的Visual basic語言，除了它無法象Visual basic般獨立存在外，其它的如程序的編輯方法，對象的使用等，都與Visual basic相同，因此如果學(xué)習(xí)過Visual basic語言，入門VBA將是一件很容易的事。VBE是Visual basic Editor的縮寫,是用來編寫VBA應(yīng)用程序的編輯器,它與VB編器有什么區(qū)別呢?Visual basic是用來開發(fā)完整程序的工具,必須使用相當(dāng)大的系統(tǒng)資源,但是在控制EXCEL或其他OFFICE辦公軟件時,并不需要使用這么龐大的資源,因此Microsoft將Visual basic編寫及執(zhí)行程序的功能附屬于OFFICE之下，使其可以使用較少的系統(tǒng)資源，達到編輯出應(yīng)用程序的目的。因此將提供這些功能的編輯器稱為Visual basic Editor。EXCELVBA基本構(gòu)架對象、屬性、事件、方法對象： 所謂的對象，我們可以將其想象成日常生活中所用的各類物品比如車子桌子、椅子等以車子的組成為例，車子是由輪子、車座、車燈、外殼等組合而成的一個大對象而這些具有單一功能的對象又可以分為更小的對象如：螺絲、電線等。 就EXCEL而言其對象就是工作薄、工作表、圖表工作表或其他更細(xì)微的窗體組件。屬性： 所謂屬性就是對象所屬的特征，例如當(dāng)年到一個蘋果時，可以馬上知道它的顏色形狀及大小這些都可以用來形容對象的形容詞也就是對象的屬性，比如工作表的名稱就是工作表的一個屬性。取得屬性值的語法：變量=比如我們要取得當(dāng)前工作表的名子和工作表的數(shù)量AAA=BBB=設(shè)定屬性的語法:=屬性值例:sheets(1).name=我的工作薄1 設(shè)置第一個工作表的名稱是我的工作薄1事件: 所謂事件,就是發(fā)生在對象上的事情例如在按按上單擊鼠標(biāo)這個動作就是發(fā)生于按紐上的事件，工作表的增加，單元格的修改都是事件。下面的圖示中右邊的下拉框中都是WORKSHEET（工作表）的事件事件有什么用呢？程序我們平常并不希望它時時運行，所以我們需要在使用到它時再讓它運行，比如：你可以設(shè)置一個按紐用單擊這個事件去觸發(fā)程序的運行，當(dāng)我們單擊按紐時就運行，不單擊就不運行。再如，我們有時候需要在改變某個工作表中的單元格時運行某個程序這時就可以用Worksheet_Change這個事件。方法:控制對象的動作或功能稱為方法,簡單的說就是為了達到某種效果而下達的命令:比如:.使用方法的程序語法.例:上例中SHEET1的部分為對象,而.后方的即為對象所提供的方法,至于對象提供的方法有哪些,并不需要特意去記憶它們,當(dāng)輸入了對象名稱然后輸入.時，系統(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 Range(a21).Value = .Sum(Range(a1:a20)) 注意SUM前面要有個點才行Range(a22).Value = .Average(Range(a1:a20))End With在VBA不直接支持在EXCEL工作表中的一些函數(shù)，宏：許多VBA高手都是從錄制宏入門VBA的，宏的功能就是將所操作的步驟記錄下來,以便以后的任何時候只要使用宏,就可以重復(fù)執(zhí)行操作過的步驟。有時在編程序時，忘記了EXCEL某些基本功能的編寫，錄制宏就是一個很好的方法，比如你忘記了不連續(xù)單元格的表示方法，你就可以在工作表中把你選取不連續(xù)單元格的過程錄制下來，代碼自然就出來了。 錄制宏的操作： “工具”—“宏”點擊錄制宏按紐在彈出的錄制新宏對話框中輸入宏名和快捷鍵（可不填）進行事選設(shè)定的操作停止錄制（其實這個和平時生活中錄音或錄像一樣，開始錄制停止），補充一點，就是如果以后想用按紐來執(zhí)行宏，可以直接在彈出的指定宏對話框中點擊錄制宏，其余步驟同上。案例25　資產(chǎn)折舊216。 直線折舊法計算資產(chǎn)折舊=SLN（Cost,Satvage,Life）216。 固定余額遞減折舊法計算資產(chǎn)折舊=DB（Cost,Satvage,Life,Year）216。 雙倍余額遞減折舊法計算資產(chǎn)折舊=DDB（Cost,Satvage,Life,Year,2）216。 可變余額遞減折舊法計算資產(chǎn)折舊=VDB（Cost,Satvage,Life,StartPeriod,EndPeriod,Factor,NoSwitch）216。 年數(shù)總和折舊法計算資產(chǎn)折舊=SYD（Cost,Satvage,Life,Year）附1：投資決策分析進行投資分析需要為ｅｘｃｅｌ提供一系列現(xiàn)金流量的值。凈現(xiàn)值。內(nèi)部報酬率。凈現(xiàn)值和內(nèi)部報酬率是關(guān)系緊密的一對函數(shù)。當(dāng)內(nèi)部報酬率＝資金成本時，凈現(xiàn)值＝０。附2： 整合數(shù)據(jù)表我們經(jīng)常需要將幾個表格中的數(shù)據(jù)整合成一張表，如下圖所示，將5個月的數(shù)據(jù)（目前分布在5張表格上）整合到一個總表上：我們使用的方法是利用office的查詢工具query進行的，這樣做的優(yōu)點在于今后對于新數(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ù)表的界面：如果以上界面提示錯誤，點擊以上界面的“選項”按鈕，出現(xiàn)以下界面，將系統(tǒng)表打勾即可：任選一個字段到右側(cè)，點下一步按鈕：直到以下界面出現(xiàn)，選擇第二項，點完成按鈕：此時出現(xiàn)query程序界面，點擊工具條上SQL按鈕：出現(xiàn)以下界面：在界面中將SQL語句改為以下：出現(xiàn)以下提示，點確定按鈕：出現(xiàn)數(shù)據(jù)查詢結(jié)果，選文件菜單將數(shù)據(jù)返回EXCEL:選擇在新工作表中存儲數(shù)據(jù)：幾張數(shù)據(jù)表被成功地整合在一起。該查詢數(shù)據(jù)表與數(shù)據(jù)源保持著動態(tài)鏈接關(guān)系，可以隨時刷新數(shù)據(jù)，不需要重復(fù)操作。右鍵菜單里選擇刷新數(shù)據(jù)即可對數(shù)據(jù)進行刷新。附3：對于數(shù)據(jù)庫的訪問鏈接方法ODBC