1、Exceel在管理理中的應(yīng)應(yīng)用案例011第1部分分 Exxcell基礎(chǔ)概概念名稱名稱可以以代表一一個(gè)單元元格或者者一個(gè)單單元格區(qū)區(qū)域，或或者是常常量，公公式。名名稱的定定義：選選中需要要命名的的單元格格或區(qū)域域，在界界面左上上角名稱稱框中輸輸入名稱稱后回車車；名稱稱的刪除除：插入入名稱稱定義義，選中中需要?jiǎng)h刪除的名名稱點(diǎn)擊擊刪除按按鈕，點(diǎn)點(diǎn)確定。名稱的的引用：需要引引用某單單元格時(shí)時(shí)輸入該該單元格格的名稱稱：名名稱第1步：選選中需要要命名的的某個(gè)單單元格或或單元格格區(qū)域。第2步：在左上上角名稱稱框輸入入命名后后回車。刪除已有有命名：選擇“插入”“名稱”“定義”；選中中需要?jiǎng)h刪除的名名稱，點(diǎn)點(diǎn)擊

2、“刪除”按鈕。名稱命名名的優(yōu)點(diǎn)點(diǎn)：避免絕對(duì)對(duì)引用的的錯(cuò)誤對(duì)公式進(jìn)進(jìn)行文字字化表述述，讓公公式更加加容易理理解可以在整整個(gè)工作作簿中通通用，引引用方便便常用引用用函數(shù)rrow: 返回指指定單元元格的行行號(hào)collumnn: 返回指指定單元元格的列列標(biāo)mattch: 返回查查找值在在查找范范圍中的的序號(hào) =mattch(查找值值，查找找范圍，0) 其第三三個(gè)參數(shù)數(shù)為0，表示示查找精精確值adddresss: 返回單單元格名名稱，其其參數(shù)為為行號(hào)列列標(biāo)或計(jì)計(jì)算行號(hào)號(hào)列標(biāo)的的表達(dá)式式 =adddreess(行號(hào)，列列標(biāo)) =aaddrresss(1,1) 此公式式返回A1iindiirecct: 返回

3、單單元格的的值.其參數(shù)數(shù)為單元元格名稱稱 =inndirrectt(“A1”),假設(shè)A1=10,則inddireect返回10iindeex: 在某區(qū)區(qū)域內(nèi)查查找某個(gè)個(gè)位置的的值 =inddex(查找區(qū)區(qū)域，查查找值所所在的行行號(hào)，查查找值所所在的列列號(hào))offfseet：指定基基點(diǎn)，指指定位移移量，得得到單元元格引用用 單個(gè)單單元格引引用：=offfseet(基點(diǎn)單單元格，向向下移動(dòng)動(dòng)的行，向向右移動(dòng)動(dòng)的列) 區(qū)域的的引用：=offfseet(基點(diǎn)單單元格，向向下移動(dòng)動(dòng)的行，向向右移動(dòng)動(dòng)的列，區(qū)區(qū)域 包括的的行數(shù)，區(qū)區(qū)域包括括的列數(shù)數(shù)) 向上和和向左移移動(dòng)時(shí)，位位移量為為負(fù)值。 =offfs

4、ett(A11,1,1) 將得到B2單元格格的值動(dòng)態(tài)引用用動(dòng)態(tài)引引用是通通過引用用函數(shù)實(shí)實(shí)現(xiàn)對(duì)于于單元格格或區(qū)域域的相對(duì)對(duì)引用。它和相相對(duì)引用用的效果果很相似似，但比比簡(jiǎn)單的的相對(duì)引引用用途途廣泛。常用的的動(dòng)態(tài)引引用的實(shí)實(shí)現(xiàn)方法法有：引引用函數(shù)數(shù)嵌套。比如offfsett和roww,coolummn嵌套；inddex和mattch，inddireect,adddresss嵌套等等；引用用函數(shù)與與控件結(jié)結(jié)合使用用，此類類控件包包括組合合框，滾滾動(dòng)條。動(dòng)態(tài)引引用的作作用：實(shí)實(shí)現(xiàn)對(duì)于于單元格格的動(dòng)態(tài)態(tài)引用；進(jìn)行動(dòng)動(dòng)態(tài)分析析；制作作動(dòng)態(tài)報(bào)報(bào)表。運(yùn)算類型型數(shù)值運(yùn)運(yùn)算：1 + 1 = 22邏輯運(yùn)運(yùn)算：1

5、0 = TTRUEE在邏輯輯運(yùn)算中中，TRUUE=11，F(xiàn)ALLSE=0我們們利用邏邏輯運(yùn)算算進(jìn)行條條件判斷斷在Exccel中常用用的一些些邏輯函函數(shù)：if,andd,orr,noot,iiserrrorr等andd: 只有當(dāng)當(dāng)所有條條件全部部滿足，才才會(huì)返回回truue的邏輯輯值 語法：andd(條件1，條件2，)orr: 滿足其其中任何何一個(gè)條條件，都都會(huì)返回回truue的邏輯輯值 語法：or(條件1，條件2，條件3,)函數(shù)調(diào)用用的語法法函數(shù)數(shù)名稱（參參數(shù)1，參數(shù)2，）絕對(duì)引用用和相對(duì)對(duì)引用：絕對(duì)引用用：所引引用的單單元格不不隨著公公式的復(fù)復(fù)制而移移動(dòng)的引引用方式式。相對(duì)引用用：所引引用的

6、單單元格隨隨著公式式的復(fù)制制而移動(dòng)動(dòng)的引用用方式。改變引引用方式式的方法法：在公式欄欄中選中中需要改改變引用用方式的的單元格格，按F4鍵給需要引引用的單單元格定定義名稱稱，然后后在公式式中引用用該名稱稱數(shù)組公式式對(duì)單元元格區(qū)域域進(jìn)行多多重計(jì)算算的計(jì)算算方式。與普通通計(jì)算公公式的區(qū)區(qū)別是錄錄入公式式結(jié)束后后，需要要同時(shí)按按下 cttrl + sshifft + ennterr ，其特特征是在在公式兩兩端會(huì)出出現(xiàn)一對(duì)對(duì)大括號(hào)號(hào)。如上上圖例，使使用一個(gè)個(gè)公式計(jì)計(jì)算出所所有產(chǎn)品品的金額額合計(jì)，引引用的是是所有的的單價(jià)和和所有的的數(shù)量，執(zhí)執(zhí)行的是是多重計(jì)計(jì)算。錯(cuò)誤提示示Exccel中存在在錯(cuò)誤的的類型，

7、比比如1/00=#DDIV/0!, toodass()=#NAAMESS!isserrror是一個(gè)個(gè)邏輯函函數(shù)，用用以判斷斷某個(gè)單單元格內(nèi)內(nèi)的值是是否是一一個(gè)錯(cuò)誤誤，是錯(cuò)錯(cuò)誤則返返回TRUUE,不是錯(cuò)錯(cuò)誤則返返回FALLSE.iseerroor有時(shí)可可以和if函數(shù)嵌嵌套進(jìn)行行一些較較為復(fù)雜雜的判斷斷。有取值區(qū)區(qū)間的隨隨機(jī)數(shù)=最小值值（最最大值最小值值）*raand()循環(huán)引用用是單元元格引用用其自身身的引用用方式?？梢栽O(shè)設(shè)置Exccel允許進(jìn)進(jìn)行循環(huán)環(huán)引用：工具-選項(xiàng)-重新計(jì)計(jì)算：將將迭代計(jì)計(jì)算選項(xiàng)項(xiàng)打鉤選選中。行列互換換（函數(shù)數(shù)方式）：利用traanspposee函數(shù)+數(shù)組公公式實(shí)現(xiàn)現(xiàn)。首先

8、先選中行行列數(shù)和和原區(qū)域域相反的的一個(gè)區(qū)區(qū)域；然然后輸入入traanspposee函數(shù)最最后按下下組合鍵鍵ctrrl+sshifft+eenteer第2部分分Exccel設(shè)置顯示當(dāng)前前文件的完整路徑徑菜單區(qū)區(qū)域右右鍵菜單單Webb顯示菜單單項(xiàng)全部部菜單視視圖工工具欄自定義義選項(xiàng)項(xiàng)始終終顯示整整個(gè)菜單單鼠標(biāo)移動(dòng)動(dòng)方向工工具選選項(xiàng)編編輯按按Entter鍵后移移動(dòng)方向向隱藏界面面要素工工具選選項(xiàng)視視圖包括括：網(wǎng)格格線，滾滾動(dòng)條，工工作表標(biāo)標(biāo)簽，行行號(hào)列標(biāo)標(biāo)等。以顯示值值為準(zhǔn)工工具選選項(xiàng)重重新計(jì)算算以顯顯示精度度為準(zhǔn)行的合計(jì)計(jì)與列的的合計(jì)有有時(shí)出現(xiàn)現(xiàn)不相等等的情況況。可以采用用以下方方法解決決。選擇“

9、工工具”“選項(xiàng)”“重新計(jì)計(jì)算”“以顯示示精度為為準(zhǔn)”，選中中該選項(xiàng)項(xiàng)。自定義序序列工具具選項(xiàng)項(xiàng)自定定義序列列改變文件件保存位位置工具具選項(xiàng)項(xiàng)常規(guī)規(guī)默認(rèn)認(rèn)文件位位置改變文件件用戶名名工具選項(xiàng)用戶名名單元格自自動(dòng)換行行格式單元格格對(duì)齊齊自動(dòng)動(dòng)換行第3部分分 Exccel基礎(chǔ)操操作快速選中中數(shù)據(jù)表表的整行行或者整整列ctrrl + shhiftt + 下箭頭/右箭頭頭快速選中中區(qū)域ctrrl + shhiftt + 8行列互換換復(fù)制需需要進(jìn)行行行列互互換的區(qū)區(qū)域后，將光標(biāo)放置在數(shù)據(jù)表外面位置，選擇性粘貼選中“轉(zhuǎn)置”選項(xiàng)一個(gè)單元元格內(nèi)容容輸入為為多行altt + 回車鍵鍵顯示公式式ctrrl + 凍結(jié)

10、窗口口選擇需需要進(jìn)行行凍結(jié)的的單元格格位置，選選擇窗口口凍結(jié)結(jié)窗格自定義格格式語法法： 大于條條件值格式；小于條條件值格式；等于條條件值格式；文本格格式縮放數(shù)值值 00.000， 按百萬萬縮放 00“.”0，按萬萬縮放 00.000, 按千縮縮放不復(fù)制隱隱藏的行行或列a首先選選中需要要復(fù)制的的被隱藏藏了一些些行或列列的表格格區(qū)域；b 然后點(diǎn)點(diǎn)擊“編輯”-“定位”-“定位條條件”，在其其中選擇擇“可見單單元格”；c 復(fù)制表表格區(qū)域域，粘貼貼即可。案例022 一 文本本的處理理等長(zhǎng)文本本的分割割從字符符串的左左邊取字字符：=Leftt(字符串串，文本本長(zhǎng)度)從字符符串的右右邊取字字符：=Righh

11、t(字符串串，文本本長(zhǎng)度)從字符符串的中中間取字字符：=Mid(字符串串，文本本起始位位置，文文本長(zhǎng)度度)不等長(zhǎng)文文本的分分割第1步：選選中要進(jìn)進(jìn)行分割割的字符符串區(qū)域域。第2步：點(diǎn)擊“數(shù)據(jù)”“分列”，在文文本分列列向?qū)е兄羞x擇適適合的分分隔符。第3步：設(shè)置需需要導(dǎo)入入的列以以及放置置該列的的位置。文本的合合并=CConccateenatte(文本1，文本2，)使用連連接符&：動(dòng)態(tài)態(tài)表頭：=AABC公司&YYEARR(NOOW()&年&MMONTTH(NNOW()&月報(bào)表有效性選選中需要要設(shè)置有有效性的的區(qū)域，點(diǎn)點(diǎn)擊“數(shù)據(jù)”“有效性”，在有有效性條條件中選選擇“序列”，在來來源中錄錄入或選選取

12、列表表。輸入信息息：事前前提醒。出錯(cuò)警告告：事后后提示。如果待選選列表不不在當(dāng)前前工作表表中，需需要首先先為該列列表定義義名稱，然然后在有有效性來來源框中中輸入：“=該列表表的名稱稱”有效性的的其他用用法不允許錄錄入重復(fù)復(fù)數(shù)據(jù)的的有效性性設(shè)定：couuntiif(ee:e,e122)=11輸入的內(nèi)內(nèi)容中必必須包括括某字符符：=noot(iiserrrorr(fiind(中國,gg30)二級(jí)選項(xiàng)項(xiàng)：首先先將一級(jí)級(jí)選項(xiàng)的的每個(gè)項(xiàng)項(xiàng)目定義義一個(gè)名名稱，該該名稱內(nèi)內(nèi)容包括括相應(yīng)的的二級(jí)項(xiàng)項(xiàng)目；制作一級(jí)級(jí)項(xiàng)目的的有效性性；制作二級(jí)級(jí)項(xiàng)目的的有效性性：內(nèi)容容為：=INDDIREECT(g6)，其中g(shù)6為設(shè)

13、定定了有效效性的一一級(jí)選項(xiàng)項(xiàng)所在的的單元格格。圈示無效效數(shù)據(jù)。選擇“工工具”“公式審審核”“顯示公公式審核核工具欄欄”，點(diǎn)擊“圈示無無效數(shù)據(jù)據(jù)”按鈕。該工具可可以將所所有不符符合有效效性設(shè)定定的內(nèi)容容圈示出出來。二日期的的處理Dateediff 函數(shù)：用于計(jì)計(jì)算兩個(gè)個(gè)日期之之間的年年數(shù)，月月數(shù)，天天數(shù)。=Dateediff(開始日日期，結(jié)結(jié)束日期期，“y”)第三個(gè)個(gè)參數(shù)：“y”：表示示年數(shù)“m”：表示示月數(shù)“d”：表示示天數(shù)生日提醒醒=DAATEDDIF(TODDAY(),DDATEE(YEEAR(TODDAY(),MONNTH(G2),DAAY(GG2),dd)某日期的的星期=weeekda

14、ay(AA2,22) 返回的的值是3，則表表示該日日期是星星期三。兩個(gè)日期期間的工工作日天天數(shù)=neetwoorkddayss(開始日日期，結(jié)結(jié)束日期期，節(jié)假假日列表表)需要首首先加載載“分析工工具庫”才能使使用此工工具案例033數(shù)據(jù)查查詢VLOOOKUPP（）VLOOOKUPP在表格格或數(shù)值值數(shù)組的的首列查查找指定定的數(shù)值值，并由由此返回回表格或或數(shù)組當(dāng)當(dāng)前行中中指定列列處的數(shù)數(shù)值。VLOOOKUUP 中的 V 代表垂垂直。VLOOOKUPP(loookuup_vvaluue,ttablle_aarraay,ccol_inddex_numm,raangee_loookuup)Lookkup_

15、vallue 為需要要在數(shù)組組第一列列中查找找的數(shù)值值。Loookupp_vaaluee 可以為為數(shù)值、引用或或文本字字符串。也可以以理解為為：兩表表共有的的索引字字段。Tablle_aarraay 為需要要在其中中查找數(shù)數(shù)據(jù)的數(shù)數(shù)據(jù)表。可以使使用對(duì)區(qū)區(qū)域或區(qū)區(qū)域名稱稱的引用用，例如如數(shù)據(jù)庫庫或列表表。必須須使得共共有字段段位于該該范圍的的第一列列。col_inddex_numm 為需要要調(diào)轉(zhuǎn)的的數(shù)據(jù)位位于第二二個(gè)參數(shù)數(shù)中定義義的范圍圍的第幾幾列。必必須為單單純數(shù)值值。rangge_llookkup 定義大大致匹配配或精確確匹配。Falsse或0:精確匹匹配；truue或忽略略或1：如果無無法

16、找到到精確匹匹配的值值，那么么就查找找并匹配配比查找找值小的的最近似似的值。HLOOOKUPP()HHloookupp中的H是水平平的意思思，在橫橫向排列列的表格格中查詢?cè)償?shù)據(jù)用用Hloookuup，垂直直排列的的表格查查詢用Vloookuup函數(shù)。其參數(shù)數(shù)含義與與VLOOOKUUP近似。案例044 查詢表表信息查詢?cè)儽砝糜糜行孕院蚔LOOOKUUP函數(shù)實(shí)實(shí)現(xiàn)。利利用有效效性實(shí)現(xiàn)現(xiàn)索引字字段的切切換。利利用VLOOOKUUP函數(shù)查查詢與索索引字段段相關(guān)的的信息。案例055 數(shù)據(jù)篩篩選自動(dòng)篩選選（略）高級(jí)篩選選由于自動(dòng)動(dòng)篩選只只能篩選選出簡(jiǎn)單單條件的的數(shù)據(jù)，因因此如要要在復(fù)雜雜條件下下進(jìn)行篩

17、篩選就需需要采用用高級(jí)篩篩選的方方式。在需要進(jìn)進(jìn)行篩選選的數(shù)據(jù)據(jù)表外設(shè)設(shè)定篩選選條件。在對(duì)話框框中設(shè)定定：列表表區(qū)域?yàn)闉閿?shù)據(jù)表表區(qū)域；條件區(qū)區(qū)域?yàn)閯倓偛旁跀?shù)數(shù)據(jù)表外外部設(shè)定定的條件件區(qū)域，要要包括字字段名稱稱和條件件所在的的單元格格區(qū)域。條件格式式條件格式式可以使使得符合合特定條條件的記記錄按照照某種設(shè)設(shè)定的格格式顯示示。選中要進(jìn)進(jìn)行條件件格式設(shè)設(shè)定的單單元格范范圍執(zhí)行【格格式】【條條件格式式】在條件格格式設(shè)置置界面進(jìn)進(jìn)行條件件的設(shè)定定：統(tǒng)計(jì)Frrequuenccy(需要進(jìn)進(jìn)行統(tǒng)計(jì)計(jì)的數(shù)據(jù)據(jù)區(qū)域，分分段點(diǎn))對(duì)數(shù)據(jù)據(jù)進(jìn)行區(qū)區(qū)間統(tǒng)計(jì)計(jì)。此函函數(shù)需要要和數(shù)組組公式一一起使用用。此函函數(shù)是根根據(jù)數(shù)值值

18、的大小小劃分區(qū)區(qū)間進(jìn)行行統(tǒng)計(jì)的的。列出出需要分分析的工工資區(qū)間間，選中中需要計(jì)計(jì)算統(tǒng)計(jì)計(jì)結(jié)果的的單元格格區(qū)域，輸輸入函數(shù)數(shù)：按下組合合鍵：ctrl + sshifft + ennterr排名次=rannk(需排名名數(shù)值，排排名數(shù)據(jù)據(jù)范圍，排排序方式式)排序方方式：0或忽略略降序，否否則為升升序。案例066 窗體控控件的使使用下拉菜單單的制作作。選擇“視視圖”“工具欄”“窗體”，出現(xiàn)“窗體”工具條條，在工工具條上上選擇“組合框”。按下鼠標(biāo)標(biāo)左鍵，拖拖放出合合適的大大小后釋釋放鼠標(biāo)標(biāo)，出現(xiàn)現(xiàn)下拉菜菜單形狀狀。選中中該下拉拉菜單，右右鍵選擇擇“設(shè)置控控件格式式”在“控制制”菜單上上，去選選擇相應(yīng)應(yīng)的

19、數(shù)據(jù)據(jù)源。“三維陰陰影”選項(xiàng)是是下拉菜菜單顯示示效果設(shè)設(shè)定。單元格鏈鏈接：該該處指定定的單元元格中將將存儲(chǔ)在在控件中中被選中中的項(xiàng)目目在數(shù)據(jù)據(jù)源中的的序列號(hào)號(hào)。我們們經(jīng)常利利用該序序列號(hào)引引用控件件選中的的值，還還可以利利用該值值制作動(dòng)動(dòng)態(tài)圖表表。根據(jù)報(bào)銷銷單上填填報(bào)的日日期自動(dòng)動(dòng)生成該該報(bào)銷單單的起止止日期。使用最大大值函數(shù)數(shù)MAX和最小小值函數(shù)數(shù)MIN。制作單選選按鈕。使用窗體體工具條條上的“選項(xiàng)按按鈕”界面定制制行號(hào)列標(biāo)標(biāo)，滾動(dòng)動(dòng)條，工工作表標(biāo)標(biāo)簽的隱隱藏。選選擇“工具”“選項(xiàng)”“視圖”，將窗窗口選項(xiàng)項(xiàng)中的行行號(hào)列標(biāo)標(biāo)，水平平滾動(dòng)條條，垂直直滾動(dòng)條條，工作作表標(biāo)簽簽選項(xiàng)去去掉。案例077

20、 匯總報(bào)報(bào)表對(duì)于分布布在同一一文件中中的表格格匯總：首先創(chuàng)建建格式完完全一致致的匯總總表的表表結(jié)構(gòu)在匯總表表需要匯匯總的項(xiàng)項(xiàng)目上輸輸入：=suum()將光標(biāo)標(biāo)放入括括號(hào)，用用鼠標(biāo)選選擇第一一張工作作表，按按下shiift鍵，再再選取最最后一張張工作表表，然后后選擇需需要匯總總的單元元格，回回車。將該公式式復(fù)制到到其他需需要匯總總的單元元格即可可實(shí)現(xiàn)整整張表的的匯總。對(duì)于分布布在不同同文件中中的表格格匯總：首先創(chuàng)建建格式完完全一致致的匯總總表的表表結(jié)構(gòu)將光標(biāo)置置于起始始報(bào)表項(xiàng)項(xiàng)目上選擇【數(shù)數(shù)據(jù)】【合并并計(jì)算】“引用位位置”選擇各各個(gè)需要要被合并并的報(bào)表表，點(diǎn)擊擊“添加”，直至至所有需需要被合合并

21、的報(bào)報(bào)表添加加完成。需要?jiǎng)h除除一張表表格時(shí)，只只需要在在合并計(jì)計(jì)算界面面上選中中該表格格，點(diǎn)擊擊刪除按按鈕。當(dāng)子表格格數(shù)據(jù)發(fā)發(fā)生變化化需要更更新匯總總表數(shù)據(jù)據(jù)時(shí)，只只需執(zhí)行行：數(shù)據(jù)據(jù)-合并計(jì)計(jì)算，進(jìn)進(jìn)入合并并計(jì)算界界面后直直接點(diǎn)擊擊確定退退出該界界面即可可完成刷刷新數(shù)據(jù)據(jù)的操作作。案例088 報(bào)表表保護(hù)單元格區(qū)區(qū)域的保保護(hù)。首先取消消不需要要保護(hù)的的單元格格區(qū)域的的鎖定狀狀態(tài)。右右鍵“設(shè)置單單元格格格式”，在“保護(hù)”標(biāo)簽上上將鎖定定選項(xiàng)取取消。如如果需要要隱藏單單元格公公式內(nèi)容容，可以以將隱藏藏選項(xiàng)選選中。選擇“工工具”“保護(hù)”“保護(hù)工工作表”，輸入入保護(hù)密密碼。隱藏公式式右鍵菜菜單設(shè)設(shè)置單元

22、元格格式式保護(hù)護(hù)選中中隱藏選選項(xiàng)，然然后執(zhí)行行對(duì)工作作表的保保護(hù)隱藏工作作表：隱藏工作作表后需需要保護(hù)護(hù)工作簿簿。案例099 按鈕的的制作選擇“視視圖”“工具欄”“窗體”，調(diào)出出窗體工工具條。選擇工工具條上上的命令令按鈕。繪制出出按鈕的的基本形形狀后，自自動(dòng)跳出出“指定宏”對(duì)話框框，點(diǎn)擊擊“新建”按鈕，出出現(xiàn)VBA編程界界面。在Subb和Endd Suub之間輸輸入：sheeetss (“要跳轉(zhuǎn)轉(zhuǎn)的工作作表名稱稱”).sseleect，關(guān)閉閉該窗口口。選中中該按鈕鈕，右鍵鍵菜單“編輯文文字”，可以以修改按按鈕名稱稱。案例100 財(cái)務(wù)報(bào)報(bào)表利用exxcell創(chuàng)建財(cái)財(cái)務(wù)報(bào)表表，要注注意報(bào)表表之間的

23、的勾稽關(guān)關(guān)系。一一方面是是出于高高效率管管理報(bào)表表的考慮慮，另一一方面，可可以幫助助我們分分析報(bào)表表中的數(shù)數(shù)據(jù)，并并快速檢檢查出報(bào)報(bào)表中的的可能錯(cuò)錯(cuò)誤。為了能夠夠自動(dòng)維維護(hù)報(bào)表表間的數(shù)數(shù)據(jù)關(guān)系系，我們們經(jīng)常需需要使用用單元格格鏈接，將將所有的的表鏈接接起來。注意鏈鏈接方式式請(qǐng)使用用并聯(lián)而而非串聯(lián)聯(lián)。串聯(lián)聯(lián)導(dǎo)致鏈鏈接關(guān)系系更加復(fù)復(fù)雜且難難以維護(hù)護(hù)，而并并聯(lián)的方方式將只只有一個(gè)個(gè)統(tǒng)一的的數(shù)據(jù)源源，鏈接接關(guān)系簡(jiǎn)簡(jiǎn)單不容容易出錯(cuò)錯(cuò)，而且且易于管管理。比如損益益表，資資產(chǎn)負(fù)債債表，現(xiàn)現(xiàn)金流量量表，需需要使用用公式和和鏈接使使得這些些表鏈接接起來，該該鏈接關(guān)關(guān)系能夠夠自動(dòng)維維護(hù)和更更新多表表之間的的勾稽關(guān)

24、關(guān)系，時(shí)時(shí)刻能夠夠保證報(bào)報(bào)表數(shù)據(jù)據(jù)之間的的勾稽的的正確性性。（具具體公式式參見電電子文檔檔）。案例111 表格的的幾種類類型數(shù)據(jù)表可可以分為為數(shù)據(jù)列列表，二二維表，多多維表和和帶有格格式的報(bào)報(bào)表等幾幾種類型型。數(shù)據(jù)表類類型操作目標(biāo)標(biāo)匯總方法法是否需要要重復(fù)制制作是否與數(shù)數(shù)據(jù)源有有鏈接關(guān)關(guān)系多字段數(shù)數(shù)據(jù)列表表記錄疊加加并按表表名區(qū)分分導(dǎo)入外部部數(shù)據(jù) + SQLL語句不需要有二維表多個(gè)二維維表生成成透視表表透視表多多維數(shù)據(jù)據(jù)區(qū)域合合并不需要有同一文件件內(nèi)的報(bào)報(bào)表數(shù)據(jù)匯總總=summ(beeginn:ennd!BB2)不需要有不同文件件中的報(bào)報(bào)表數(shù)據(jù)匯總總合并計(jì)算算不需要有非標(biāo)準(zhǔn)數(shù)數(shù)據(jù)表匯總建議修改

25、改原表的的結(jié)構(gòu)或或者僅作作為終端端報(bào)表，上上層再建建立一個(gè)個(gè)數(shù)據(jù)源源表作為為收集數(shù)數(shù)據(jù)使用用需要無案例122 數(shù)據(jù)透透視表應(yīng)用數(shù)據(jù)據(jù)透視表表進(jìn)行各各類數(shù)據(jù)據(jù)分析。選擇“數(shù)數(shù)據(jù)”“數(shù)據(jù)透透視表和和數(shù)據(jù)透透視圖”，進(jìn)入入透視表表設(shè)置向向?qū)?。點(diǎn)擊“下下一步”，選擇擇正確的的數(shù)據(jù)范范圍。點(diǎn)擊“下下一步”，選擇“布局”按鈕。在如下圖圖界面上上，將右右側(cè)的字字段拖入入左側(cè)相相應(yīng)區(qū)域域內(nèi)。形成如下下圖的布布局，點(diǎn)點(diǎn)擊確定定按鈕。生成如下下的透視視表后，將將鼠標(biāo)懸懸停在想想要移動(dòng)動(dòng)的字段段上，鼠鼠標(biāo)左鍵鍵按下，將將字段拖拖放到其其他區(qū)域域，生成成自己需需要的數(shù)數(shù)據(jù)顯示示。數(shù)據(jù)百分分比顯示示：右鍵鍵菜單字段設(shè)設(shè)

26、置選選項(xiàng)，將將數(shù)據(jù)顯顯示方式式改為“占同列列數(shù)據(jù)總總和的百百分比”同時(shí)顯示示數(shù)據(jù)與與百分比比：在【布局局】里將將銷售收收入兩次次拖入數(shù)數(shù)據(jù)區(qū)域域?qū)⑵渲幸灰粋€(gè)銷售售收入改改為百分分比顯示示在報(bào)表項(xiàng)項(xiàng)目上輸輸入新的的名稱可可以修改改項(xiàng)目名名稱將報(bào)表項(xiàng)項(xiàng)目拖拽拽到列標(biāo)標(biāo)題位置置，可以以將數(shù)值值和百分分比改為為按列排排列篩選數(shù)據(jù)據(jù)：點(diǎn)擊字段段名稱后后面的下下拉菜單單，可以以對(duì)字段段內(nèi)容進(jìn)進(jìn)行篩選選。顯示明細(xì)細(xì)數(shù)據(jù)：雙擊需要要查看明明細(xì)的數(shù)數(shù)據(jù)，在在新的工工作表上上將顯示示構(gòu)成此此匯總數(shù)數(shù)據(jù)的所所有明細(xì)細(xì)數(shù)據(jù)。對(duì)數(shù)據(jù)進(jìn)進(jìn)行排序序：把光標(biāo)放放在行位位置的報(bào)報(bào)表項(xiàng)目目上，【右右鍵菜單單】【字字段設(shè)置置】，【

27、高高級(jí)】按按鈕，左左側(cè)可以以進(jìn)行排排序設(shè)置置。按照日期期分組：將日期放放入行區(qū)區(qū)域，【右右鍵菜單單】【組組及顯示示明細(xì)數(shù)數(shù)據(jù)】【組組合】在【步長(zhǎng)長(zhǎng)】中選選擇需要要的分組組標(biāo)準(zhǔn)按照數(shù)值值分組：將銷售收收入放入入行區(qū)域域，【右右鍵菜單單】【組組及顯示示明細(xì)數(shù)數(shù)據(jù)】【組組合】在【步長(zhǎng)長(zhǎng)】中選選擇需要要的分組組標(biāo)準(zhǔn)插入計(jì)算算字段：在【名稱稱】后給給計(jì)算字字段定義義名稱在【公式式】后輸輸入計(jì)算算字段的的計(jì)算公公式，可可以引用用下面的的字段自動(dòng)更新新：【右鍵菜菜單】【表表格選項(xiàng)項(xiàng)】【打打開時(shí)刷刷新】可以讓報(bào)報(bào)表始終終有更新新后的數(shù)數(shù)據(jù)顯示示。透視圖可以單獨(dú)獨(dú)生成數(shù)數(shù)據(jù)透視視圖，也也可以基基于現(xiàn)有有的透視視

28、表生成成透視圖圖。如果果已經(jīng)有有現(xiàn)成的的透視表表，通過過點(diǎn)擊數(shù)數(shù)據(jù)透視視表工具具條上的的圖表按按鈕可以以生成一一個(gè)透視視圖。通過拖動(dòng)動(dòng)透視圖圖上的各各個(gè)字段段到右側(cè)側(cè)或者底底部的位位置，可可以方便便地改變變圖表組組織數(shù)據(jù)據(jù)的方式式。隱藏透視視圖字段段按鈕：把光標(biāo)懸懸停在任任意字段段上，右右鍵菜單單，選擇擇【隱藏藏?cái)?shù)據(jù)透透視圖字字段按鈕鈕】，即即可以將將字段按按鈕隱藏藏；再次顯示示字段按按鈕：點(diǎn)擊透視視圖工具具條上第第一項(xiàng)，在在出現(xiàn)的的下拉菜菜單中選選擇【隱隱藏?cái)?shù)據(jù)據(jù)透視圖圖字段按按鈕】如果有些些數(shù)據(jù)無無法在數(shù)數(shù)據(jù)透視視表內(nèi)部部分析，可可以引用用透視表表的數(shù)據(jù)據(jù)作為分分析的基基礎(chǔ)。制作試算算平衡

29、表表點(diǎn)擊“數(shù)數(shù)據(jù)”并拖拽拽到“匯總”隱藏?cái)?shù)據(jù)據(jù)透視表表中的匯匯總字段段或單元格格格式自定定義功能能格式設(shè)定定為：紅色借貸不不平衡;紅色金額借借貸不平平衡；藍(lán)色”借貸平平衡”Ctrll+拖拽標(biāo)標(biāo)簽來復(fù)復(fù)制工作作表編制損益益表創(chuàng)建損益益表格式式創(chuàng)建ISS嵌套函函數(shù)IS 類類函數(shù)，可可以檢驗(yàn)驗(yàn)數(shù)值的的類型并并根據(jù)參參數(shù)取值值返回 TRRUE 或 FAALSEE。函數(shù)如果為下下面的內(nèi)內(nèi)容，則則返回 TRRUEISBLLANKK值為空白白單元格格。ISERRR值為任意意錯(cuò)誤值值（除去去 #NN/A）。ISERRRORR值為任意意錯(cuò)誤值值（#N/A、#VAALUEE!、#REEF!、#DIIV/00!、#

30、NUUM!、#NAAME? 或 #NNULLL!）。ISLOOGICCAL值為邏輯輯值。ISNAA值為錯(cuò)誤誤值 #NN/A（值不不存在）。ISNOONTEEXT值為不是是文本的的任意項(xiàng)項(xiàng)（注意意此函數(shù)數(shù)在值為為空白單單元格時(shí)時(shí)返回 TRRUE）。ISNUUMBEER值為數(shù)字字。ISREEF值為引用用。ISTEEXT值為文本本。創(chuàng)建財(cái)務(wù)務(wù)比率銷售毛利利率=銷售毛毛利/銷售凈凈額毛利率率大，表表示經(jīng)營(yíng)營(yíng)能力強(qiáng)強(qiáng)。銷售成本本率=銷售成成本/銷售凈凈額成本率率低，表表示經(jīng)營(yíng)營(yíng)能力好好。凈利率率率=凈利潤(rùn)/銷售凈凈額凈利潤(rùn)潤(rùn)率越高高，表示示經(jīng)營(yíng)能能力強(qiáng)。營(yíng)業(yè)比率率=（銷售售成本+營(yíng)業(yè)費(fèi)費(fèi)用）/銷售凈凈額

31、，營(yíng)營(yíng)業(yè)比率率越低，表表示經(jīng)營(yíng)營(yíng)能力越越強(qiáng)營(yíng)業(yè)費(fèi)用用率=銷售費(fèi)費(fèi)用/銷售凈凈額營(yíng)業(yè)費(fèi)費(fèi)用越低低，表示示經(jīng)營(yíng)績(jī)績(jī)效越好好。用MAXX、MIN函數(shù)顯顯示制表表日期MAX，返回一一組值中中的最大大值MIN，返回一一組值中中的最小小值案例一三三多重合合并計(jì)算算數(shù)據(jù)如下圖格格式的即即為二維維表：現(xiàn)在我們們需要將將數(shù)個(gè)格格式相同同的二維維表匯總總為一張張表格，且且可以區(qū)區(qū)分不同同表格屬屬性進(jìn)行行分析，比比如三張張表分別別為北京京，上海海，深圳圳分公司司的表格格。我們們使用的的方法是是利用透透視表多多重合并并計(jì)算數(shù)數(shù)據(jù)區(qū)域域的功能能。選擇數(shù)據(jù)據(jù)-數(shù)據(jù)透透視表和和數(shù)據(jù)透透視圖選擇第33個(gè)數(shù)據(jù)據(jù)源類型型：多重重

32、合并計(jì)計(jì)算數(shù)據(jù)據(jù)區(qū)域，點(diǎn)點(diǎn)擊下一一步按鈕鈕：在出現(xiàn)的的界面上上選擇：自定義義字段選擇需要要被合并并的表格格區(qū)域，點(diǎn)點(diǎn)擊添加加將其添添加到所所有區(qū)域域；將頁頁字段數(shù)數(shù)據(jù)改為為1，在項(xiàng)項(xiàng)標(biāo)志處處輸入該該表格的的標(biāo)志，使使用相同同的方法法將其他他需要合合并的表表格全部部添加。透視表生生成后如如下圖所所示，已已經(jīng)將多多個(gè)表格格合并成成一個(gè)：雙擊字段段名，可可以對(duì)字字段名進(jìn)進(jìn)行修改改：案例144 回歸與與預(yù)測(cè)回歸分析析表明事事物之間間相互影影響的關(guān)關(guān)系，主主要用于于分析單單個(gè)因變變量如何何受一個(gè)個(gè)或多個(gè)個(gè)自變量量影響的的。比如如某地空空調(diào)的銷銷量與氣氣溫的變變化之間間的關(guān)系系?？梢砸杂媒y(tǒng)計(jì)計(jì)獲得的的歷史

33、數(shù)數(shù)據(jù)，對(duì)對(duì)未來的的數(shù)據(jù)進(jìn)進(jìn)行預(yù)測(cè)測(cè)?？梢砸岳盟鼛椭?jīng)經(jīng)營(yíng)，財(cái)財(cái)務(wù)，銷銷售，營(yíng)營(yíng)銷等方方面的決決策。趨勢(shì)線預(yù)預(yù)測(cè)根據(jù)歷史史數(shù)據(jù)制制作出折折線圖選中圖表表中的折折線，右右鍵菜單單添加加趨勢(shì)線線選擇預(yù)測(cè)測(cè)類型（可可以先選選擇默認(rèn)認(rèn)的線性性）切換到選選項(xiàng)標(biāo)簽簽，根據(jù)據(jù)需要預(yù)預(yù)測(cè)的期期間數(shù)，在在“前推”處輸入入預(yù)測(cè)期期間個(gè)數(shù)數(shù)，并選選中“顯示公公式”和“顯示R平方值”選項(xiàng)根據(jù)出現(xiàn)現(xiàn)的回歸歸系數(shù)（R平方）判判斷預(yù)測(cè)測(cè)類型是是否適合合，R平方越越接近1越好（多多項(xiàng)式除除外，需需要考慮慮業(yè)務(wù)數(shù)數(shù)據(jù)是否否存在較較大的波波動(dòng)，如如果沒有有則不適適用多項(xiàng)項(xiàng)式分析析類型）根據(jù)回歸歸方程求求預(yù)測(cè)值值移動(dòng)平均均沒有

34、R平方，不不適用此此分析方方法。Corrrel函數(shù)判判斷相關(guān)關(guān)性。相關(guān)系數(shù)數(shù)在0.8以上為為強(qiáng)相關(guān)關(guān)，0.6以上為為弱相關(guān)關(guān)。案例一五五-166 數(shù)據(jù)分分析與圖圖表分析析日常工作作中主要要用到的的數(shù)據(jù)分分析有：構(gòu)成：局局部與整整體比例例關(guān)系。序列：在在一個(gè)時(shí)時(shí)間序列列內(nèi)進(jìn)行行的比較較與分析析。差異：預(yù)預(yù)算與實(shí)實(shí)際數(shù)的的版本差差異等。增長(zhǎng)：增增長(zhǎng)率與與增速，進(jìn)進(jìn)行趨勢(shì)勢(shì)預(yù)測(cè)。勾稽：數(shù)數(shù)據(jù)之間間固有的的相互聯(lián)聯(lián)系的屬屬性，利利用此屬屬性進(jìn)行行數(shù)據(jù)合合理性的的判斷。圖表用于于形象地地展示數(shù)數(shù)據(jù)。利利用圖表表可以更更好地對(duì)對(duì)業(yè)務(wù)數(shù)數(shù)據(jù)進(jìn)行行分析。差異分析析：利用上面面的表格格制作面面積圖，數(shù)數(shù)據(jù)區(qū)域域

35、選擇C255:D331選中B公公司數(shù)據(jù)據(jù)系列，右右鍵菜單單“數(shù)據(jù)系系列格式式”將其內(nèi)部部（填充充色）和和邊框色色都改為為“無”圖表上將將只顯示示差異部部分，如如下圖：雙坐標(biāo)圖圖表：利用下面面的數(shù)據(jù)據(jù)制作雙雙坐標(biāo)圖圖表：選擇“插插入”-“圖表”，進(jìn)入入圖表向向?qū)Вx選擇柱形形圖，第第一種子子圖表類類型簇狀狀柱形圖圖。選擇“視視圖”-“工具欄”-“圖表”，調(diào)出出圖表工工具條：點(diǎn)開圖表表工具條條上下拉拉菜單，選選擇 系列“完成率”點(diǎn)擊圖表表工具條條上“數(shù)據(jù)系系列格式式”按鈕：在數(shù)據(jù)系系列格式式界面上上，切換換到坐標(biāo)標(biāo)軸標(biāo)簽簽，將系系列繪制制在由“主坐標(biāo)標(biāo)軸”改為“次坐標(biāo)標(biāo)軸”再次選中中系列“完成率”

36、，右鍵鍵菜單選選擇“圖表類類型”選擇折線線圖，點(diǎn)點(diǎn)確定：雙坐標(biāo)圖圖表制作作完成。氣泡圖：利用下表表制作氣氣泡圖：在圖表向向?qū)е羞x選擇“氣泡圖”因?yàn)闅馀菖輬D沒有有分類軸軸（其橫橫坐標(biāo)為為序列號(hào)號(hào)），我我們需要要添加數(shù)數(shù)據(jù)標(biāo)識(shí)識(shí)，點(diǎn)擊擊下一步步，切換換到數(shù)據(jù)據(jù)標(biāo)志標(biāo)標(biāo)簽，將將系列名名稱打勾勾：縱坐標(biāo)表表示%Grrowtth,氣泡大大小表示示Cosst:單元格嵌嵌入式圖圖表：利用下表表制作如如圖中所所示的嵌嵌入式圖圖表在單元格格中敲入入如下公公式：=REPPT(|,B2/4000)其中，除除數(shù)的值值由源數(shù)數(shù)據(jù)的大大小決定定，源數(shù)數(shù)據(jù)大，除除數(shù)應(yīng)相相應(yīng)變大大，其意意義在于于縮減豎豎線的數(shù)數(shù)量，使使得單

37、元元格可以以容納由由一系列列豎線構(gòu)構(gòu)成的圖圖表。復(fù)合餅圖圖：當(dāng)數(shù)據(jù)系系列內(nèi)值值的大小小差異較較大時(shí)，可可以制作作復(fù)合餅餅圖以便便數(shù)據(jù)顯顯示更為為清楚，如如下圖所所示：選擇餅圖圖中的復(fù)復(fù)合餅圖圖子圖表表類型：點(diǎn)擊下一一步，切切換到圖圖例標(biāo)簽簽，將顯顯示圖例例的勾去去掉：切換到數(shù)數(shù)據(jù)標(biāo)志志標(biāo)簽，把把類別名名稱和百百分比打打勾：選中數(shù)據(jù)據(jù)系列，右右鍵菜單單，選擇擇數(shù)據(jù)系系列格式式：切換到選選項(xiàng)標(biāo)簽簽，將第第二繪圖圖區(qū)包含含最后的的值改為為：4復(fù)合餅圖圖制作完完成。圖表組合合：一個(gè)圖表表中的不不同數(shù)據(jù)據(jù)系列可可以采用用不同的的圖表類類型顯示示，如下下圖：首先制作作一個(gè)柱柱形圖：在圖表工工具條上上選擇數(shù)

38、數(shù)據(jù)系列列 Coompeensaatioon:在圖表工工具條上上選擇數(shù)數(shù)據(jù)系列列格式按按鈕：在數(shù)據(jù)系系列格式式界面上上，切換換到坐標(biāo)標(biāo)軸標(biāo)簽簽，將主主坐標(biāo)軸軸改為次次坐標(biāo)軸軸：右鍵點(diǎn)擊擊Commpennsattionn,選擇圖圖表類型型：將其圖表表類型改改為折線線圖；用同樣的的方式操操作Prooducctioon系列，將將其圖表表類型改改為面積積圖；完成圖表表組合的的制作。下拉菜單單式圖表表：寫入如下下的公式式：inddex函數(shù)查查找出的的值由A9單元格格來指定定。制作三維維餅圖，其其數(shù)據(jù)區(qū)區(qū)域?yàn)锳3:I3,A9:I9,需要手手工選取取。點(diǎn)擊“視視圖”-“工具欄”-“窗體”，調(diào)出出窗體工工具條

39、：在窗體工工具條上上選擇組組合框，在在餅圖上上拖放出出一個(gè)組組合框：右鍵選中中組合框框，在菜菜單中選選擇“設(shè)置控控件格式式”：在控制標(biāo)標(biāo)簽中做做如下設(shè)設(shè)置：其其中單元元格鏈接接為存儲(chǔ)儲(chǔ)控件選選中項(xiàng)目目序號(hào)的的單元格格。完成下拉拉菜單式式圖表的的制作：帶有滾動(dòng)動(dòng)條的圖表：該圖表中中可以隨隨著點(diǎn)擊擊滾動(dòng)條條的動(dòng)作作而動(dòng)態(tài)態(tài)翻看源源數(shù)據(jù)中中的大量量數(shù)據(jù)。定義兩個(gè)個(gè)動(dòng)態(tài)引引用的名名稱：Periiod: =ooffsset(7.動(dòng)態(tài)圖圖表!$A$11,77.動(dòng)態(tài)圖圖表!$D$11,0,10,1)Dataa: =ooffsset(7.動(dòng)態(tài)圖圖表!$B$11,77.動(dòng)態(tài)圖圖表!$D$11,0,10,1)制作

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

41、數(shù)據(jù)系列列格式對(duì)對(duì)話框中中將邊框框和內(nèi)部部都改選選為無，此此時(shí)開始始日期數(shù)數(shù)據(jù)系列列隱藏。此時(shí)需要要將橫坐坐標(biāo)開始始日期改改為真正正的項(xiàng)目目開始日日期：在exccel中，每每一個(gè)日日期都對(duì)對(duì)應(yīng)一個(gè)個(gè)數(shù)值，選選中B2,查看20008-7-11對(duì)應(yīng)數(shù)數(shù)值為396630雙擊橫坐坐標(biāo)，切切換到刻刻度，將將最小值值改為396630，同樣樣將橫坐坐標(biāo)最大大值改為為真正的的項(xiàng)目結(jié)結(jié)束日期期。甘特圖制制作完成成。案例177動(dòng)態(tài)態(tài)圖表選中北京京，深圳圳，上海海以下的的區(qū)域，分分別定義義北京，深深圳，上上海的名名稱選擇視圖圖-工具欄-窗體，選選擇選項(xiàng)項(xiàng)按鈕，畫畫出如下下三個(gè)選選項(xiàng)按鈕鈕：右鍵選中中選項(xiàng)按按鈕，在在

42、右鍵菜菜單中選選擇設(shè)置置控件格格式，在在跳出的的對(duì)話框框中選擇擇控制標(biāo)標(biāo)簽，在在單元格格鏈接中中選擇A16：在A177單元格格中輸入入公式：=chhoosse(aa16,”北京”,”上?！?”深圳”)，為A17定義名名稱：chooosee在A2,A3,A4單元格格中輸入入以下內(nèi)內(nèi)容：在B166單元中中寫入以以下公式式，并復(fù)復(fù)制到該該行1-112月的單單元格中中：=VLOOOKUUP(AA3,IINDIIRECCT(cchooose),COOLUMMN()-1,0)依據(jù)此數(shù)數(shù)據(jù)表制制作雙曲曲線圖：案例一八八 比率分分析利用透視視表靈活活的特性性，將兩兩個(gè)結(jié)構(gòu)構(gòu)完全相相同的透透視表相相除，即即得到

43、可可以進(jìn)行行任意兩兩個(gè)因素素間的比比率分析析圖表。案例199邏輯判判斷IF(條條件判斷斷，如果果條件滿滿足則返返回的結(jié)結(jié)果，如如果條件件不滿足足則返回回的結(jié)果果)根據(jù)工工齡計(jì)算算員工年年假。年年假規(guī)則則：規(guī)定定：公司司工齡小小于1年的，享享受10天年假假；大于于1年小于10年的，工工齡每增增加一年年，年假假增加1天；增增長(zhǎng)到20天不再再增加。使用IF函數(shù)嵌嵌套實(shí)現(xiàn)現(xiàn)。單元元格F3為工齡齡。=IF(F31,110,IIF(FF310000000,(K3-16000)*0.445-一五3755,IFF(KK3-116000)8800000,(K3-16000)*0.44-1003755,IFF(K

44、K3-116000)6600000,(K3-16000)*0.335-663755,IFF(KK3-116000)4400000,(K3-16000)*0.33-33375,IF(K33-16600)2000000,(KK3-116000)*00.255-一三75,IF(K33-16600)50000,(K33-16600)*0.2-3375,IF(K33-16600)20000,(K33-16600)*0.一五-1225,IIF(K3-16000)5000,(KK3-116000)*00.1-25,(K33-16600)*0.05)自動(dòng)計(jì)算算加班費(fèi)費(fèi)=IFF(ISSERRROR(VLOOOK

45、UUP(DD2,$L$22:$LL$122,1,FALLSE),IIF(OOR(GG2=66,G22=7),周末加加班,工作日日加班),節(jié)假日日加班)自動(dòng)計(jì)算算請(qǐng)假扣扣除=IFF(ORR(D55=病假,DD5=事假),E5*1,IIF(DD5=曠工,E5*3,00)隱藏錯(cuò)誤誤提示=IF(ISEERROOR(表達(dá)式),表達(dá)式)案例200計(jì)數(shù)與與求和條件計(jì)數(shù)數(shù)couuntiif(rrangge，criiterria)couuntiif(AA1:AA1000，”8”)條件求和和如果滿滿足某個(gè)個(gè)條件，就就對(duì)該記記錄里的的指定數(shù)數(shù)值字段段求和。在第一一個(gè)參數(shù)數(shù)所在的的區(qū)域里里面查找找第二個(gè)個(gè)參數(shù)指指定的

46、值值，找到到后對(duì)第第三個(gè)參參數(shù)指定定的字段段進(jìn)行求求和。ssumiif(rrangge, criiterria, suum_rrangge)ssumiif(AA1:AA1000, “?海*”, EE1:EE1000) 對(duì)A列中第4個(gè)字為為海的E列的值值求和模糊條件件求和summif(a1:a1000, “*”&”海”&”*”, ee1:ee1000) 對(duì)A列中包包含“?！弊值腅列的值值求和sumiif對(duì)多個(gè)個(gè)條件進(jìn)進(jìn)行求和和=SUUM(SSUMIIF(FF:F,F2:F3,E:EE)=SUMM(SUUMIFF(F:F,陳露,程靜,E:EE)ccritteriia為常量量，普通通公式；criit

47、erria為單元元格引用用，需要要使用數(shù)數(shù)組公式式。sum與與數(shù)組公公式聯(lián)手手計(jì)數(shù)=suum(a1:a1000110000)*(a1:a1000550000)sum與與數(shù)組公公式聯(lián)手手求和=suum(條件1)*(條件2)*.*求和區(qū)區(qū)域)sumpprodductt的使用用返回相相應(yīng)的數(shù)數(shù)組或區(qū)區(qū)域的乘乘積的和和?？梢砸允褂胹ummprooducct替代sum數(shù)組公公式的使使用用于于多條件件求和。summprooducct(條件1)*(條件2)*) 用于計(jì)計(jì)數(shù)summprooducct(條件1)*(條件2)*求和區(qū)區(qū)域) 用于求求和案例211 模擬運(yùn)運(yùn)算表（略略）案例222 單變變量求解解如果希

48、望望利潤(rùn)達(dá)達(dá)到200萬，則則需要相相應(yīng)調(diào)整整的單價(jià)價(jià)、直接接成本、固定成成本、銷銷售量為為多少？可變單元元格：要要求解的的值所在在的單元元格目標(biāo)單元元格：關(guān)關(guān)系式表表達(dá)所在在的單元元格目標(biāo)值：關(guān)系式式的值單變量求求解的原原理：根根據(jù)在單單元格中中描述的的一元方方程求解解方程中中的未知知數(shù)。使使用單變變量求解解的前提提：需要要在單元元格中將將一元方方程的方方程式描描述清楚楚。如果果是函數(shù)數(shù)關(guān)系，則則需要在在單元格格中引用用該函數(shù)數(shù)。案例233規(guī)劃求求解步驟：構(gòu)構(gòu)建已知知條件的的關(guān)系模模型，然然后運(yùn)行行規(guī)劃求求解工具具。構(gòu)建建模型的的過程實(shí)實(shí)際上是是利用Exccel單元格格引用的的方式將將條件描描

49、述清楚楚的過程程。在規(guī)劃求求解對(duì)話話框中，進(jìn)進(jìn)行相關(guān)關(guān)設(shè)置。目標(biāo)單單元格為為求解后后達(dá)到的的目標(biāo)。可變單單元格為為要求解解的單元元格。在在約束中中添加已已知的其其他條件件。規(guī)劃求解解可以求求解多元元方程，可可以解決決求極值值的問題題，可以以替代計(jì)計(jì)算較為為復(fù)雜的的計(jì)算過過程。案例244 自定義義函數(shù)宏的安全全性級(jí)別別調(diào)整點(diǎn)擊：工工具-宏-安全性性如果定義義了宏或或者自定定義函數(shù)數(shù)并且想想使用它它們，那那么需要要將宏的的安全性性級(jí)別調(diào)調(diào)整為中中或者低低。自定義函函數(shù)：進(jìn)入VBB編輯器器選中左側(cè)側(cè)工程管管理器中中的當(dāng)前前工作簿簿，查看看其下項(xiàng)項(xiàng)目中是是否存在在“模塊”。如果果不存在在，點(diǎn)擊擊【插入

50、入】菜單單，選擇擇“模塊”；如果果存在，則則略過此此步驟。雙擊模塊塊下的某某一模塊塊，右側(cè)側(cè)將彈出出該模塊塊的代碼碼窗口，我我們將在在此代碼碼窗口中中編寫自自定義函函數(shù)。在右側(cè)代代碼窗口口輸入以以下語句句：Funcctioon vvaluuatiion(a, b, cc, dd)valuuatiion = (a + b) *0.66 + (c + dd) * 0.4End Funnctiion其中，vvaluuatiion是該函函數(shù)的函函數(shù)名，a,bb,c,d是該自自定義函函數(shù)的參參數(shù)，funnctiion 與endd fuuncttionn是自定定義函數(shù)數(shù)的起始始與結(jié)束束語句。帶有判斷斷的自

51、定定義函數(shù)數(shù)：Funcctioon llimiitattionn(joob)If jjob = “總經(jīng)理”O(jiān)r jobb = “市場(chǎng)銷銷售” Thhenlimiitattionn = 一五00ElseeIf jobb = “客戶服服務(wù)” Orr joob = “副總經(jīng)經(jīng)理” Thhenlimiitattionn = 12000ElseeIf jobb = “職能管管理” Orr joob = “業(yè)務(wù)總總監(jiān)” Thhenlimiitattionn = 8000ElseeIf jobb = “產(chǎn)品” Orr joob = “采購” Thhenlimiitattionn = 6000ElseeIf

52、jobb = “研發(fā)技技術(shù)”O(jiān)r jobb = “生產(chǎn)運(yùn)運(yùn)作” Thhenlimiitattionn = 4000Elsee: llimiitattionn = 1000End IfEnd Funnctiion附：VBBA簡(jiǎn)介VBA簡(jiǎn)簡(jiǎn)介：VBA 是 Viisuaal bbasiic ffor Apppliccatiion 的縮寫寫。是用用來開發(fā)發(fā)應(yīng)用程程序的Vissuall baasicc語言，除除了它無無法象Vissuall baasicc般獨(dú)立立存在外外，其它它的如程程序的編編輯方法法，對(duì)象象的使用用等，都都與Vissuall baasicc相同，因因此如果果學(xué)習(xí)過過Vissuall b

53、aasicc語言，入入門VBA將是一一件很容容易的事事。VBE是是Vissuall baasicc Edditoor的縮寫,是用來來編寫VBA應(yīng)用程程序的編編輯器,它與VB編器有有什么區(qū)區(qū)別呢?Viisuaal bbasiic是用來來開發(fā)完完整程序序的工具具,必須使使用相當(dāng)當(dāng)大的系系統(tǒng)資源源,但是在在控制EXCCEL或其他OFFFICEE辦公軟軟件時(shí),并不需需要使用用這么龐龐大的資資源,因此Miccrossoftt將Vissuall baasicc編寫及及執(zhí)行程程序的功功能附屬屬于OFFFICEE之下，使使其可以以使用較較少的系系統(tǒng)資源源，達(dá)到到編輯出出應(yīng)用程程序的目目的。因因此將提提供這些些

54、功能的的編輯器器稱為Vissuall baasicc Edditoor。EXCEELVBBA基本構(gòu)構(gòu)架對(duì)象、屬屬性、事事件、方法對(duì)象：所所謂的對(duì)對(duì)象，我我們可以以將其想想象成日日常生活活中所用用的各類類物品比比如車子子桌子、椅子等等以車子子的組成成為例，車車子是由由輪子、車座、車燈、外殼等等組合而而成的一一個(gè)大對(duì)對(duì)象而這這些具有有單一功功能的對(duì)對(duì)象又可可以分為為更小的的對(duì)象如如：螺絲絲、電線線等。 就就EXCCEL而言其其對(duì)象就就是工作作薄、工工作表、圖表工工作表或或其他更更細(xì)微的的窗體組組件。屬性：所謂屬性性就是對(duì)對(duì)象所屬屬的特征征，例如如當(dāng)年到到一個(gè)蘋蘋果時(shí)，可可以馬上上知道它它的顏色色形

55、狀及及大小這這些都可可以用來來形容對(duì)對(duì)象的形形容詞也也就是對(duì)對(duì)象的屬屬性，比比如工作作表的名名稱就是是工作表表的一個(gè)個(gè)屬性。取得屬性性值的語語法：變變量=對(duì)象.屬性比如我們們要取得得當(dāng)前工工作表的的名子和工工作表的的數(shù)量AAA=Acttiveesheeet.nammeBBB=Acttiveesheeet.couunt設(shè)定屬性性的語法法:對(duì)象.屬屬性=屬性值值例:shheetts(11).nnamee=我的工工作薄1 設(shè)置第第一個(gè)工工作表的的名稱是是我的工工作薄1事件:所謂事件件,就是發(fā)發(fā)生在對(duì)對(duì)象上的的事情例例如在按按按上單單擊鼠標(biāo)標(biāo)這個(gè)動(dòng)動(dòng)作就是是發(fā)生于于按紐上上的事件件，工作作表的增增加，

56、單單元格的的修改都都是事件件。下面面的圖示示中右邊邊的下拉拉框中都都是WORRKSHHEETT（工作作表）的的事件事件有什什么用呢呢？程序序我們平平常并不不希望它它時(shí)時(shí)運(yùn)運(yùn)行，所所以我們們需要在在使用到到它時(shí)再再讓它運(yùn)運(yùn)行，比比如：你你可以設(shè)設(shè)置一個(gè)個(gè)按紐用用單擊這這個(gè)事件件去觸發(fā)發(fā)程序的的運(yùn)行，當(dāng)當(dāng)我們單單擊按紐紐時(shí)就運(yùn)運(yùn)行，不不單擊就就不運(yùn)行行。再如如，我們們有時(shí)候候需要在在改變某某個(gè)工作作表中的的單元格格時(shí)運(yùn)行行某個(gè)程程序這時(shí)時(shí)就可以以用Worrkshheett_Chhangge這個(gè)事事件。方法:控制對(duì)象象的動(dòng)作作或功能能稱為方方法,簡(jiǎn)單的的說就是是為了達(dá)達(dá)到某種種效果而而下達(dá)的的命令:

57、比如:對(duì)對(duì)工作表表這個(gè)對(duì)對(duì)象進(jìn)行行新建或或刪除的的功能.而新建建及刪除除功能即即為工作作表這個(gè)個(gè)對(duì)象所所提供的的方法.使用方法法的程序序語法.對(duì)象.方方法例:SHHEETT1.DDELEETE上例中SSHEEET1的部分分為對(duì)象象,而.后方的的即為對(duì)對(duì)象所提提供的方方法,至于對(duì)對(duì)象提供供的方法法有哪些些,并不需需要特意意去記憶憶它們,當(dāng)輸入入了對(duì)象象名稱然然后輸入入.時(shí)，系系統(tǒng)就會(huì)會(huì)顯示該該對(duì)象提提供的方方法菜單單以便從從菜單中中選取適適用的方方法.舉例：點(diǎn)擊“新新建”并加入SSheeets(shheett1).Raangee(aa1) = 1000Sheeetss(ssheeet1).CCe

58、llls(22, 11) = 2000Dim i AAs IInteegerr DDIM變量名名稱AS 變量類類型For i = 1 To 20SSheeets(shheett1).Ceellss(i, 1) = iNeext添加：WWithh Apppliicattionn.WoorkssheeetFuuncttionnRannge(a221).Vaaluee = .Suum(RRangge(a1:a200) 注意SUM前面要要有個(gè)點(diǎn)點(diǎn)才行RRangge(a222).Vallue = .Aveeragge(RRangge(a1:a200)Endd Wiith在VBAA不直接接支持在在EXCC

59、EL工作表表中的一一些函數(shù)數(shù)，如果果要調(diào)用用就必須須加上AAppllicaatioon.WWorkksheeetFFuncctioon宏：許多多VBA高手都都是從錄錄制宏入入門VBA的，宏宏的功能能就是將將所操作作的步驟驟記錄下下來,以便以以后的任任何時(shí)候候只要使使用宏,就可以以重復(fù)執(zhí)執(zhí)行操作作過的步步驟。有有時(shí)在編編程序時(shí)時(shí)，忘記記了EXCCEL某些基基本功能能的編寫寫，錄制制宏就是是一個(gè)很很好的方方法，比比如你忘忘記了不不連續(xù)單單元格的的表示方方法，你你就可以以在工作作表中把把你選取取不連續(xù)續(xù)單元格格的過程程錄制下下來，代代碼自然然就出來來了。錄制宏的的操作： “工具”“宏”點(diǎn)擊錄錄制宏按

60、按紐在彈出出的錄制制新宏對(duì)對(duì)話框中中輸入宏宏名和快快捷鍵（可可不填）進(jìn)行事事選設(shè)定定的操作作停止錄錄制（其其實(shí)這個(gè)個(gè)和平時(shí)時(shí)生活中中錄音或或錄像一一樣，開開始錄制-停止），補(bǔ)補(bǔ)充一點(diǎn)點(diǎn)，就是是如果以以后想用用按紐來來執(zhí)行宏宏，可以以直接在在彈出的的指定宏宏對(duì)話框框中點(diǎn)擊擊錄制宏宏，其余余步驟同同上。案例255資產(chǎn)產(chǎn)折舊直線折舊舊法計(jì)算算資產(chǎn)折折舊=SLNN（Cosst,SSatvvagee,Liife）固定余額額遞減折折舊法計(jì)計(jì)算資產(chǎn)產(chǎn)折舊=DB（Cosst,SSatvvagee,Liife,Yeaar）雙倍余額額遞減折折舊法計(jì)計(jì)算資產(chǎn)產(chǎn)折舊=DDBB（Cosst,SSatvvagee,Lii