【正文】 點(diǎn)擊錄制宏按紐在彈出的錄制新宏對(duì)話框中輸入宏名和快捷鍵（可不填）進(jìn)行事選設(shè)定的操作停止錄制（其實(shí)這個(gè)和平時(shí)生活中錄音或錄像一樣，開(kāi)始錄制停止），補(bǔ)充一點(diǎn)，就是如果以后想用按紐來(lái)執(zhí)行宏，可以直接在彈出的指定宏對(duì)話框中點(diǎn)擊錄制宏，其余步驟同上。下面的圖示中右邊的下拉框中都是WORKSHEET（工作表）的事件事件有什么用呢？程序我們平常并不希望它時(shí)時(shí)運(yùn)行，所以我們需要在使用到它時(shí)再讓它運(yùn)行，比如：你可以設(shè)置一個(gè)按紐用單擊這個(gè)事件去觸發(fā)程序的運(yùn)行，當(dāng)我們單擊按紐時(shí)就運(yùn)行，不單擊就不運(yùn)行。VBE是Visual basic Editor的縮寫(xiě),是用來(lái)編寫(xiě)VBA應(yīng)用程序的編輯器,它與VB編器有什么區(qū)別呢?Visual basic是用來(lái)開(kāi)發(fā)完整程序的工具,必須使用相當(dāng)大的系統(tǒng)資源,但是在控制EXCEL或其他OFFICE辦公軟件時(shí),并不需要使用這么龐大的資源,因此Microsoft將Visual basic編寫(xiě)及執(zhí)行程序的功能附屬于OFFICE之下，使其可以使用較少的系統(tǒng)資源，達(dá)到編輯出應(yīng)用程序的目的。3. 雙擊模塊下的某一模塊，右側(cè)將彈出該模塊的代碼窗口，我們將在此代碼窗口中編寫(xiě)自定義函數(shù)。規(guī)劃求解可以求解多元方程，可以解決求極值的問(wèn)題，可以替代計(jì)算較為復(fù)雜的計(jì)算過(guò)程。在規(guī)劃求解對(duì)話框中，進(jìn)行相關(guān)設(shè)置。使用單變量求解的前提：需要在單元格中將一元方程的方程式描述清楚?！?”, e1:e100) 對(duì)A列中包含“?！弊值腅列的值求和4. sumif對(duì)多個(gè)條件進(jìn)行求和=SUM(SUMIF(F:F,F2:F3,E:E))=SUM(SUMIF(F:F,{陳露,程靜},E:E))criteria為常量，普通公式；criteria為單元格引用，需要使用數(shù)組公式。=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. 計(jì)算工資及所得稅=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. 自動(dòng)計(jì)算加班費(fèi)=IF(ISERROR(VLOOKUP(D2,$L$2:$L$12,1,FALSE)),IF(OR(G2=6,G2=7),周末加班,工作日加班),節(jié)假日加班)5. 自動(dòng)計(jì)算請(qǐng)假扣除=IF(OR(D5=病假,D5=事假),E5*1,IF(D5=曠工,E5*3,0))6. 隱藏錯(cuò)誤提示=IF(ISERROR(表達(dá)式),表達(dá)式)案例20 計(jì)數(shù)與求和1. 條件計(jì)數(shù)countif(range，criteria)countif(A1:A100，”8”)2. 條件求和如果滿足某個(gè)條件，就對(duì)該記錄里的指定數(shù)值字段求和。案例19 邏輯判斷1. IF(條件判斷，如果條件滿足則返回的結(jié)果，如果條件不滿足則返回的結(jié)果)根據(jù)工齡計(jì)算員工年假。此時(shí)縱坐標(biāo)次序已經(jīng)反轉(zhuǎn)雙擊開(kāi)始日期數(shù)據(jù)系列，在數(shù)據(jù)系列格式對(duì)話框中將邊框和內(nèi)部都改選為無(wú)，此時(shí)開(kāi)始日期數(shù)據(jù)系列隱藏。39。39。制作三維餅圖，其數(shù)據(jù)區(qū)域?yàn)锳3:I3,A9:I9,需要手工選取。3. 氣泡圖：利用下表制作氣泡圖：在圖表向?qū)е羞x擇“氣泡圖”因?yàn)闅馀輬D沒(méi)有分類(lèi)軸（其橫坐標(biāo)為序列號(hào)），我們需要添加數(shù)據(jù)標(biāo)識(shí)，點(diǎn)擊下一步，切換到數(shù)據(jù)標(biāo)志標(biāo)簽，將系列名稱(chēng)打勾：縱坐標(biāo)表示%Growth,氣泡大小表示Cost:4. 單元格嵌入式圖表：利用下表制作如圖中所示的嵌入式圖表在單元格中敲入如下公式：=REPT(|,B2/400)其中，除數(shù)的值由源數(shù)據(jù)的大小決定，源數(shù)據(jù)大，除數(shù)應(yīng)相應(yīng)變大，其意義在于縮減豎線的數(shù)量，使得單元格可以容納由一系列豎線構(gòu)成的圖表。圖表用于形象地展示數(shù)據(jù)。序列：在一個(gè)時(shí)間序列內(nèi)進(jìn)行的比較與分析。1. 趨勢(shì)線預(yù)測(cè)1) 根據(jù)歷史數(shù)據(jù)制作出折線圖2) 選中圖表中的折線，右鍵菜單－添加趨勢(shì)線3) 選擇預(yù)測(cè)類(lèi)型（可以先選擇默認(rèn)的線性）4) 切換到選項(xiàng)標(biāo)簽，根據(jù)需要預(yù)測(cè)的期間數(shù)，在“前推”處輸入預(yù)測(cè)期間個(gè)數(shù)，并選中“顯示公式”和“顯示R平方值”選項(xiàng)5) 根據(jù)出現(xiàn)的回歸系數(shù)（R平方）判斷預(yù)測(cè)類(lèi)型是否適合，R平方越接近1越好（多項(xiàng)式除外，需要考慮業(yè)務(wù)數(shù)據(jù)是否存在較大的波動(dòng)，如果沒(méi)有則不適用多項(xiàng)式分析類(lèi)型）6) 根據(jù)回歸方程求預(yù)測(cè)值7) 移動(dòng)平均沒(méi)有R平方，不適用此分析方法。透視表生成后如下圖所示，已經(jīng)將多個(gè)表格合并成一個(gè)：雙擊字段名，可以對(duì)字段名進(jìn)行修改：案例14 回歸與預(yù)測(cè)回歸分析表明事物之間相互影響的關(guān)系，主要用于分析單個(gè)因變量如何受一個(gè)或多個(gè)自變量影響的。216。 創(chuàng)建財(cái)務(wù)比率銷(xiāo)售毛利率=銷(xiāo)售毛利/銷(xiāo)售凈額——毛利率大，表示經(jīng)營(yíng)能力強(qiáng)。ISNUMBER值為數(shù)字。ISERROR值為任意錯(cuò)誤值（N/A、VALUE!、REF!、DIV/0!、NUM!、NAME? 或 NULL!）。 創(chuàng)建損益表格式 216。11. 制作試算平衡表 點(diǎn)擊“數(shù)據(jù)”并拖拽到“匯總” 216。10. 透視圖可以單獨(dú)生成數(shù)據(jù)透視圖，也可以基于現(xiàn)有的透視表生成透視圖。數(shù)據(jù)百分比顯示：右鍵菜單－字段設(shè)置－選項(xiàng)，將數(shù)據(jù)顯示方式改為“占同列數(shù)據(jù)總和的百分比”2. 同時(shí)顯示數(shù)據(jù)與百分比：1) 在【布局】里將銷(xiāo)售收入兩次拖入數(shù)據(jù)區(qū)域2) 將其中一個(gè)銷(xiāo)售收入改為百分比顯示3) 在報(bào)表項(xiàng)目上輸入新的名稱(chēng)可以修改項(xiàng)目名稱(chēng)4) 將報(bào)表項(xiàng)目拖拽到列標(biāo)題位置，可以將數(shù)值和百分比改為按列排列3. 篩選數(shù)據(jù)：點(diǎn)擊字段名稱(chēng)后面的下拉菜單，可以對(duì)字段內(nèi)容進(jìn)行篩選。點(diǎn)擊“下一步”，選擇“布局”按鈕。案例11 表格的幾種類(lèi)型數(shù)據(jù)表可以分為數(shù)據(jù)列表，二維表，多維表和帶有格式的報(bào)表等幾種類(lèi)型。注意鏈接方式請(qǐng)使用并聯(lián)而非串聯(lián)。選中該按鈕，右鍵菜單“編輯文字”，可以修改按鈕名稱(chēng)。案例09 按鈕的制作選擇“視圖”－“工具欄”－“窗體”，調(diào)出窗體工具條。右鍵“設(shè)置單元格格式”，在“保護(hù)”標(biāo)簽上將鎖定選項(xiàng)取消。需要?jiǎng)h除一張表格時(shí)，只需要在合并計(jì)算界面上選中該表格，點(diǎn)擊刪除按鈕。選擇“工具”－“選項(xiàng)”－“視圖”，將窗口選項(xiàng)中的行號(hào)列標(biāo)，水平滾動(dòng)條，垂直滾動(dòng)條，工作表標(biāo)簽選項(xiàng)去掉。根據(jù)報(bào)銷(xiāo)單上填報(bào)的日期自動(dòng)生成該報(bào)銷(xiāo)單的起止日期。選中該下拉菜單，右鍵選擇“設(shè)置控件格式”在“控制”菜單上，去選擇相應(yīng)的數(shù)據(jù)源。列出需要分析的工資區(qū)間，選中需要計(jì)算統(tǒng)計(jì)結(jié)果的單元格區(qū)域，輸入函數(shù)：按下組合鍵：ctrl + shift + enter5. 排名次=rank(需排名數(shù)值，排名數(shù)據(jù)范圍，排序方式)排序方式：0或忽略降序，否則為升序。3. 條件格式條件格式可以使得符合特定條件的記錄按照某種設(shè)定的格式顯示。利用VLOOKUP函數(shù)查詢與索引字段相關(guān)的信息。2. HLOOKUP()Hlookup中的H是水平的意思，在橫向排列的表格中查詢數(shù)據(jù)用Hlookup，垂直排列的表格查詢用Vlookup函數(shù)。col_ index_num 為需要調(diào)轉(zhuǎn)的數(shù)據(jù)位于第二個(gè)參數(shù)中定義的范圍的第幾列。也可以理解為：兩表共有的索引字段。4. 兩個(gè)日期間的工作日天數(shù)=networkdays(開(kāi)始日期，結(jié)束日期，節(jié)假日列表)需要首先加載“分析工具庫(kù)”才能使用此工具案例03 數(shù)據(jù)查詢1. VLOOKUP（）VLOOKUP在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。選擇“工具”－“公式審核”－“顯示公式審核工具欄”，點(diǎn)擊“圈示無(wú)效數(shù)據(jù)”按鈕。輸入信息：事前提醒。YEAR(NOW())amp。第2步：點(diǎn)擊“數(shù)據(jù)”－“分列”，在文本分列向?qū)е羞x擇適合的分隔符?？梢圆捎靡韵路椒ń鉀Q?？梢栽O(shè)置Excel允許進(jìn)行循環(huán)引用：工具選項(xiàng)重新計(jì)算：將迭代計(jì)算選項(xiàng)打鉤選中。與普通計(jì)算公式的區(qū)別是錄入公式結(jié)束后，需要同時(shí)按下 ctrl + shift + enter ，其特征是在公式兩端會(huì)出現(xiàn)一對(duì)大括號(hào)。動(dòng)態(tài)引用的作用：實(shí)現(xiàn)對(duì)于單元格的動(dòng)態(tài)引用；進(jìn)行動(dòng)態(tài)分析；制作動(dòng)態(tài)報(bào)表。 =offset(A1,1,1) 將得到B2單元格的值3. 動(dòng)態(tài)引用動(dòng)態(tài)引用是通過(guò)引用函數(shù)實(shí)現(xiàn)對(duì)于單元格或區(qū)域的相對(duì)引用。名稱(chēng)的引用：需要引用某單元格時(shí)輸入該單元格的名稱(chēng)：＝名稱(chēng)第1步：選中需要命名的某個(gè)單元格或單元格區(qū)域。名稱(chēng)的定義：選中需要命名的單元格或區(qū)域，在界面左上角名稱(chēng)框中輸入名稱(chēng)后回車(chē)；名稱(chēng)的刪除：插入－名稱(chēng)－定義，選中需要?jiǎng)h除的名稱(chēng)點(diǎn)擊刪除按鈕，點(diǎn)確定。名稱(chēng)命名的優(yōu)點(diǎn)：1) 避免絕對(duì)引用的錯(cuò)誤2) 對(duì)公式進(jìn)行文字化表述，讓公式更加容易理解3) 可以在整個(gè)工作簿中通用，引用方便2. 常用引用函數(shù)row: 返回指定單元格的行號(hào)column: 返回指定單元格的列標(biāo)match: 返回查找值在查找范圍中的序號(hào) =match(查找值，查找范圍，0) 其第三個(gè)參數(shù)為0，表示查找精確值address: 返回單元格名稱(chēng)，其參數(shù)為行號(hào)列標(biāo)或計(jì)算行號(hào)列標(biāo)的表達(dá)式 =address(行號(hào)，列標(biāo)) =address(1,1) 此公式返回A1indirect: =indirect(“A1”),假設(shè)A1=10,則indirect返回10index: 在某區(qū)域內(nèi)查找某個(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和row,column嵌套；index和match，indirect,address嵌套等；引用函數(shù)與控件結(jié)合使用，此類(lèi)控件包括組合框，滾動(dòng)條。改變引用方式的方法：1) 在公式欄中選中需要改變引用方式的單元格，按F4鍵2) 給需要引用的單元格定義名稱(chēng)，然后在公式中引用該名稱(chēng)7. 數(shù)組公式對(duì)單元格區(qū)域進(jìn)行多重計(jì)算的計(jì)算方式。9. 有取值區(qū)間的隨機(jī)數(shù)=最小值＋（最大值－最小值）*rand()10. 循環(huán)引用是單元格引用其自身的引用方式。5 以顯示值為準(zhǔn)工具－選項(xiàng)－重新計(jì)算－以顯示精度為準(zhǔn)行的合計(jì)與列的合計(jì)有時(shí)出現(xiàn)不相等的情況。案例02 一 文本的處理1. 等長(zhǎng)文本的分割從字符串的左邊取字符：=Left (字符串，文本長(zhǎng)度)從字符串的右邊取字符：=Right (字符串，文本長(zhǎng)度)從字符串的中間取字符：=Mid (字符串，文本起始位置，文本長(zhǎng)度)2. 不等長(zhǎng)文本的分割第1步：選中要進(jìn)行分割的字符串區(qū)域。：動(dòng)態(tài)表頭：=ABC公司amp。月報(bào)表4. 有效性選中需要設(shè)置有效性的區(qū)域，點(diǎn)擊“數(shù)據(jù)”－“有效性”，在有效性條件中選擇“序列”，在來(lái)源中錄入或選取列表。6. 圈示無(wú)效數(shù)據(jù)。=Datedif(開(kāi)始日期，結(jié)束日期，“y”)第三個(gè)參數(shù)：“y”：表示年數(shù)“m”：表示月數(shù)“d”：表示天數(shù)2. 生日提醒=DATEDIF(TODAY(),DATE(YEAR(TODAY()),MONTH(G2),DAY(G2)),d)3. 某日期的星期=weekday(A2,2) 返回的值是3，則表示該日期是星期三。Lookup_value 可以為數(shù)值、引用或文本字符串。必須使得共有字段位于該范圍的第一列。False或0:精確匹配；true或忽略或1：如果無(wú)法找到精確匹配的值，那么就查找并匹配比查找值小的最近似的值。利用有效性實(shí)現(xiàn)索引字段的切換。在對(duì)話框中設(shè)定：列表區(qū)域?yàn)閿?shù)據(jù)表區(qū)域；條件區(qū)域?yàn)閯偛旁跀?shù)據(jù)表外部設(shè)定的條件區(qū)域，要包括字段名稱(chēng)和條件所在的單元格區(qū)域。此函數(shù)是根據(jù)數(shù)值的大小劃分區(qū)間進(jìn)行統(tǒng)計(jì)的。 按下鼠標(biāo)左鍵，拖放出合適的大小后釋放鼠標(biāo)，出現(xiàn)下拉菜單形狀。我們經(jīng)常利用該序列號(hào)引用控件選中的值，還可以利用該值制作動(dòng)態(tài)圖表。使用窗體工具條上的“選項(xiàng)按鈕”2. 界面定制行號(hào)列標(biāo)，滾動(dòng)條，工作表標(biāo)簽的隱藏。2. 對(duì)于分布在不同文件中的表格匯總：首先創(chuàng)建格式完全一致的匯總表的表結(jié)構(gòu)將光標(biāo)置于起始報(bào)表項(xiàng)目上選擇【數(shù)據(jù)】－【合并計(jì)算】“引用位置”選擇各個(gè)需要被合并的報(bào)表，點(diǎn)擊“添加”，直至所有需要被合并的報(bào)表添加完成。首先取消不需要保護(hù)的單元格區(qū)域的鎖定狀態(tài)。隱藏公式右鍵菜單－設(shè)置單元格格式－保護(hù)－選中隱藏選項(xiàng)，然后執(zhí)行對(duì)工