Excel培訓(xùn)教程高級篇

會(huì)計(jì)學(xué)1Excel培訓(xùn)教程高級篇第一部分

函數(shù)的高級應(yīng)用第1頁/共96頁引用1.這是生產(chǎn)統(tǒng)計(jì)用的“生產(chǎn)損耗及回修報(bào)表”。其中的一些數(shù)據(jù)需要引用到“染單進(jìn)度表”里的數(shù)據(jù)。怎樣通過公式快速的進(jìn)行輸入呢？

應(yīng)用VLOOKUP第2頁/共96頁引用2.找出需要從“染單進(jìn)度表”里引用的字段。在字段名的上方插入一行，標(biāo)注上它們在“染單進(jìn)度表”中的列數(shù)。第3頁/共96頁引用3.在表格的最左列也插入一列，注明序列號(hào)，此序號(hào)從“染單進(jìn)度表”的第一個(gè)記錄所在行開始。依次向下遞增。第4頁/共96頁引用4.在單元格B5輸入如下公式。第5頁/共96頁引用5.公式解釋：以$A5的值（即4）為索引值,在“染單進(jìn)度表（6月）”的$A$1:$AQ$1000區(qū)域范圍中查找相對于索引值所在列第B$3列（即4）的記錄，查找方式為精確查找。VLOOKUP($A5,'[染單進(jìn)度表（6月）.xls]Sheet1'!$A$1:$AQ$1000,B$3,FALSE)第6頁/共96頁引用應(yīng)用INDEX+MATCH1.下面再介紹一種引用數(shù)據(jù)的方法。同樣，將數(shù)字標(biāo)記保留。第7頁/共96頁引用2.在單元格B5輸入如下公式。第8頁/共96頁引用3.公式解釋INDEX('[染單進(jìn)度表（6月）.xls]Sheet1'!$A$1:$AQ$1000,MATCH($A5,'[染單進(jìn)度表（6月）.xls]Sheet1'!$A$1:$A$1000,0),MATCH(B$3,'[染單進(jìn)度表（6月）.xls]Sheet1'!$A$2:$AQ$2,0))查找區(qū)域返回行數(shù)返回列數(shù)第9頁/共96頁引用4.那么運(yùn)用此公式的目的為：在“染單進(jìn)度表”中查找記錄的行、列數(shù)，從而準(zhǔn)確的引用了“染單進(jìn)度表”中的記錄。將公式拖拽填充，就完成了所有記錄的引用。第10頁/共96頁引用5.MATCH函數(shù)語法：MATCH（需要查找的數(shù)值，查找的區(qū)域，查找的類型）查找的類型有三種：1表示在查找區(qū)域查找小于或等于需要查找的數(shù)值的最大數(shù)值。0表示在查找區(qū)域查找等于需要查找的數(shù)值的第一個(gè)數(shù)。2表示在查找區(qū)域查找大于或等于需要查找的數(shù)值的最小數(shù)值。第11頁/共96頁引用MATCH($A5,'[染單進(jìn)度表（6月）.xls]Sheet1'!$A$1:$A$1000,0)在區(qū)域‘[染單進(jìn)度表（6月）.xls]Sheet1’!$A$1:$A$1000中查找與$A5（4）相同的數(shù)值,返回該數(shù)值在區(qū)域中的位置.即“4”所在行數(shù)。第12頁/共96頁MATCH(B$3,'[染單進(jìn)度表（6月）.xls]Sheet1'!$A$2:$AQ$2,0))在區(qū)域‘[染單進(jìn)度表（6月）.xls]Sheet1’!$A$2:$AQ$2中查找與B$3（4）相同的數(shù)值,返回該數(shù)值在區(qū)域中的位置.即“染單號(hào)”所在列數(shù)。引用第13頁/共96頁END引用—例1第14頁/共96頁引用1.這是用于倉庫的“成品紗進(jìn)銷存帳”工作薄，打開“成品紗進(jìn)銷存”工作表，這是一份原始手工錄入表，現(xiàn)在需要將這份表做成報(bào)表形式。首先將表格做好數(shù)字標(biāo)記。如圖所示：第15頁/共96頁引用2.先將表頭做好，給字段區(qū)分不同的顏色。按它們在“分類帳”中的位置來設(shè)置顏色，即在同一行的字段標(biāo)記同種顏色。并在字段的上方添加一行，標(biāo)上它們在“分類帳”中所在的列數(shù)。在表格的最左列添加一列序號(hào)。第16頁/共96頁引用3.在單元格B5輸入如圖所示的公式。第17頁/共96頁引用4.公式解釋：在成品紗進(jìn)銷存!$A$1:$Q$6000區(qū)域范圍,查找與“$A$8+14*$A4”的值相匹配的值,并返回相對于此值所在列的第“B$2”列.（這里為了便于公式的填充,通過引用B$2單元格的值13來代替輸入數(shù)字13），查找方式為精確查找。VLOOKUP($A$8+14*$A4,成品紗進(jìn)銷存!$A$1:$Q$6000,B$2,FALSE)第18頁/共96頁引用5.把兩個(gè)表格對比一下,可以很清楚的看到：在“分類帳”的第一筆帳中“100%COMBEDCOTTON”所在的行是“4”，與第二筆帳中“100%COMBEDCOTTON”相隔14行，即每一筆帳中相同記錄相隔14行。用表達(dá)式“$A$8+14*$A4”來表示．所在列值保持不變。第19頁/共96頁引用6.因?yàn)椴煌伾珮?biāo)注的字段所在行是不一樣的，所以可通過改變表達(dá)式“$A$8+14*$A4”中“$A$8”的值來確定它們的行數(shù)。最后將公式進(jìn)行拖拽添充,完成所有數(shù)據(jù)的錄入。第20頁/共96頁END引用—例2第21頁/共96頁匯總1.以上介紹了如何根據(jù)原始分類帳，利用公式自動(dòng)生成報(bào)表，但報(bào)表制作完成后，最后還要進(jìn)行匯總。即將相同“品名”，“支數(shù)”和“色相”的記錄進(jìn)行匯總。第22頁/共96頁匯總2.建立一個(gè)“成品紗(出口)進(jìn)銷存報(bào)表”的副本。在A列右邊插入一列，在這一列中，將“品名”，“支數(shù)”和“色相”三項(xiàng)內(nèi)容合并成一項(xiàng)。第23頁/共96頁匯總3.利用“高級篩選”篩選出B列不重復(fù)的記錄。這里“條件區(qū)域”用到的“*”，表示查找出全部記錄，但在“高級篩選”對話框中需在“選擇不重復(fù)的記錄”單選框中劃“√”。第24頁/共96頁匯總有關(guān)高級篩選中的通配符。以下通配符可作為篩選以及查找和替換內(nèi)容時(shí)的比較條件。請使用若要查找?（問號(hào)）

任何單個(gè)字符

例如，sm?th查找“smith”和“smyth”*（星號(hào)）任何字符數(shù)

例如，*east查找“Northeast”和“Southeast”~（波形符）后跟?、*或~問號(hào)、星號(hào)或波形符

例如，“fy91~?”將會(huì)查找“fy91?”第25頁/共96頁匯總4.然后將這些不重復(fù)的記錄后面對應(yīng)記錄（不包括需要求和匯總的記錄）利用INDEX+MATCH公式引用過來。選擇單元格AB5，輸入如圖所示的公式。第26頁/共96頁匯總5.公式解釋INDEX($A$1:$Z$1000,MATCH($AA5,$B$1:$B$1000,0),MATCH(AB$3,$A$3:$J$3,0))查找區(qū)域AA5所在行數(shù)AA3所在列數(shù)第27頁/共96頁匯總6.利用公式填充，將其他記錄項(xiàng)顯示出來。第28頁/共96頁匯總7.此時(shí)最關(guān)鍵的是對滿足條件的數(shù)值進(jìn)行求和匯總。在單元格AJ5中輸入公式，如圖所示：第29頁/共96頁匯總8.公式解釋：條件——在$B$5:$B$1000區(qū)域中與$AA5相同的記錄。求和——對K$5:K$1000區(qū)域中滿足條件的數(shù)字求和。SUMIF($B$5:$B$1000,$AA5,K$5:K$1000)第30頁/共96頁匯總9.將公式填充，匯總其他需要求和的數(shù)值。第31頁/共96頁匯總10.以上已經(jīng)完成了初步的匯總，但顯得比較混亂，那么，以AA列為關(guān)鍵字排序一下。第32頁/共96頁END匯總第33頁/共96頁條件判斷1.

這是6月份的染單進(jìn)度表，這里綜合運(yùn)用了兩個(gè)以上函數(shù)。條件2條件1第34頁/共96頁條件判斷2.公式解釋：在SHEET5的B1：AN6區(qū)域內(nèi)查找與AV列“顏色深度”相匹配的值，并返回區(qū)域中第二列的值，查找方式為精確查找。VLOOKUP($AV4,Sheet5!$B$1:$AN$6,2,FALSE)索引值第35頁/共96頁條件判斷3.再看一下“進(jìn)缸時(shí)間”所用到的函數(shù)公式。第36頁/共96頁條件判斷4.公式解釋：當(dāng)滿足條件1時(shí)，即P5=P4，P5=P6同時(shí)成立，返回AS4的值；否則返回條件2的判斷，即當(dāng)滿足P5=P6，P5不等于P4時(shí)，返回AR$2的值;否則返回條件3的判斷，即當(dāng)滿足P5不等于P6，P5=P4時(shí)，返回AS4的值。如果以上條件均不滿足，則返回“0”。IF(AND(P5=P4,P5=P6),AS4,IF(AND(P5=P6,P5<>P4),AR$2,IF(AND(P5<>P6,P5=P4),AS4,0)))條件1條件2條件3第37頁/共96頁條件判斷5.這里，條件1和條件3返回的結(jié)果都是AS4，那么可以將這兩個(gè)條件合并成一個(gè)條件。此時(shí)的條件1用了OR函數(shù)將兩個(gè)條件合并到一起。IF(OR(AND(P5=P4,P5=P6),AND(P5<>P6,P5=P4)),AS4,IF(AND(P5=P6,P5<>P4),AR$2,0))IF(AND(P5=P4,P5=P6),AS4,IF(AND(P5=P6,P5<>P4),AR$2,IF(AND(P5<>P6,P5=P4),AS4,0)))第38頁/共96頁條件判斷6.仔細(xì)看一下公式，還可以將它簡化。條件中，當(dāng)P5=P4時(shí)，不管P5=P6，還是P5<>P6,返回的值都是AS4。將P5與P6的判斷省略，只要P5=P4條件成立，就會(huì)返回值A(chǔ)S4。IF(OR(AND(P5=P4,P5=P6),AND(P5<>P6,P5=P4)),AS4,IF(AND(P5=P6,P5<>P4),AR$2,0))IF(P5=P4,AS4,IF(AND(P5=P6,P5<>P4),AR$2,0))第39頁/共96頁END條件判斷—例1第40頁/共96頁條件判斷1.這是行政用的一份表格，這里“單價(jià)”與“紗支”是對應(yīng)的。即每一類型的紗支對應(yīng)一種價(jià)格。那么，可以用IF條件函數(shù)進(jìn)行判斷。第41頁/共96頁條件判斷2.在單元格H3中輸入公式。如圖所示：這里有6種紗支類型，那么對應(yīng)6種不同的價(jià)格。第42頁/共96頁條件判斷3.公式解釋:當(dāng)$C3滿足條件1時(shí),返回28.22；滿足條件2時(shí),返回31.53；滿足條件3時(shí),返回34.17；滿足條件4時(shí),返回9.81；滿足條件5時(shí),返回7.5；滿足條件6時(shí),返回4.72；如果以上條件均不滿足，則返回0。IF($C3="2/20100%CARDEDCOTTON",28.22,IF($C3="2/20100%COMBEDCOTTON",31.53,IF($C3="2/32100%COMBEDCOTTON",34.17,IF($C3="1/1560%LINEN40%COTTON",9.81,IF($C3="2/20100%WOOL",7.5,IF($C3="100%COTTON(DEFECTIVEYARN)",4.72,0))))))條件1條件2條件3條件4條件5條件6第43頁/共96頁條件判斷4.公式輸完后,將它拖拽填充就可以了.第44頁/共96頁條件判斷5.IF函數(shù)有七層嵌套的限制,以上已經(jīng)進(jìn)行了六次條件判斷,嵌套了5層,如果以后有新類型的砂支,且對應(yīng)不同的單價(jià),嵌套多于七層,那么該如何判斷?

例子：假如

A1＝1，則

B1＝A；A1＝2，則

B1＝B

……

A1＝16，則

B1＝P=IF(A1=1,"A",IF(A1=2,"B",IF(A1=3,"C",IF(A1=4,"D",IF(A1=5,"E",IF(A1=6,"F",IF(A1=7,"G",IF(A1=8,"H",""))))))))&IF(A1=9,"I",IF(A1=10,"J",IF(A1=11,"K",IF(A1=12,"L",IF(A1=13,"M",IF(A1=14,"N",IF(A1=15,"O",IF(A1=16,"P",""))))))))

連接符,表示合并.第45頁/共96頁條件判斷關(guān)于&運(yùn)算符&（和號(hào)）運(yùn)算符可以代替函數(shù)CONCATENATE實(shí)現(xiàn)文本項(xiàng)的合并。在這里相當(dāng)于運(yùn)用CONCATENATE函數(shù)將IF嵌套函數(shù)作為它的參數(shù)項(xiàng)來合并。由于CONCATENATE可以包含最多30個(gè)參數(shù)，所以這里&運(yùn)算符最多也只能連接30個(gè)函數(shù)公式。第46頁/共96頁END條件判斷—例2第47頁/共96頁篩選１.這是一份Ｄ.Ｏ總表。根據(jù)出口編號(hào)的不同，需要將“YES總表”分類成“YGS內(nèi)銷總表”，“YGS出口排序”和“WOOL”。一般方法第48頁/共96頁篩選2.點(diǎn)擊“數(shù)據(jù)”—“自動(dòng)篩選”，在各字段名稱的右側(cè)出現(xiàn)下拉箭頭按鈕，單擊“出口DO編號(hào)”右側(cè)的下拉箭頭按鈕，選取“自定義”。第49頁/共96頁篩選3.彈出自定義對話框。在“出口DO編號(hào)”文本框下輸入“等于”“E*”，單擊“確定”按鈕。第50頁/共96頁篩選4.這樣就篩選出“出口DO編號(hào)”第一個(gè)字母為E的所有記錄。然后根據(jù)下單日先后進(jìn)行排序。選擇“數(shù)據(jù)”—“排序”，彈出排序?qū)υ捒颍x擇主要關(guān)鍵字“下單日”，“升序”，單擊“確定”按鈕。第51頁/共96頁篩選5.然后將篩選結(jié)果復(fù)制到“YGS出口排序”表格中去。根據(jù)這種方法，按出口編號(hào)的不同，再將“YES總表”分類成“YGS內(nèi)銷總表”，和“WOOL”表。第52頁/共96頁篩選1.要求篩選出“出口DO編號(hào)”第一個(gè)字母為E的所有記錄，返回到“E”這個(gè)工作表中。首先，做好數(shù)字標(biāo)記。如圖所示：運(yùn)用公式第53頁/共96頁篩選2.在“E”工作表單元格B4輸入如下公式。第54頁/共96頁篩選3.公式解釋：如果滿足條件的判斷。返回VLOOKUP查找的記錄，否則返回空。IF(LEFT(YGS總表!$E4)="E",VLOOKUP($A4,YGS總表!$A$1:$L$1000,B$2,FALSE),"")條件判斷：YGS總表!$E4單元格中文本字符串的第一個(gè)字符等于“E”。

以$A4（4）為索引值，在YGS總表!$A$1:$L$1000區(qū)域范圍查找，并返回相對于索引值所在列的第B$2（2）列的記錄，查找方式為精確查找。第55頁/共96頁篩選4.這樣將公式進(jìn)行拖拽填充，就完成了記錄的篩選。但是此時(shí)篩選出的結(jié)果比較凌亂，還需要整理一下。第56頁/共96頁篩選5.選擇區(qū)域A3：L1000，再選擇“數(shù)據(jù)”—“排序”，在排序?qū)υ捒蛑校饕P(guān)鍵字選“按列B”，“升序”，選擇“有標(biāo)題行”單選框按鈕。第57頁/共96頁篩選6.單擊“確定”后，就完成了排序。第58頁/共96頁END篩選第59頁/共96頁條件格式1.這是船務(wù)用的出貨排期表。為了清晰的顯示出貨日期，這里用條件格式來標(biāo)明顏色。第60頁/共96頁條件格式2.如果染單要求出貨期已經(jīng)過了，顯示黃色；如果染單要求出貨期為當(dāng)日，顯示紅色；如果染單要求出貨期未到，顯示藍(lán)色。第61頁/共96頁條件格式3.選擇“格式”—“條件格式”命令，打開“條件格式”對話框。輸入以下公式。第62頁/共96頁條件格式4.公式解釋：用IF函數(shù)進(jìn)行判斷，如果K4為空值，則返回空，否則進(jìn)行判斷，若(TEXT(NOW(),“YYYY-M-D”))-(TEXT(K4,“YYYY-M-D”))=0，則單元格底紋顯示紅色。表示染單出貨期為今日。當(dāng)前日期日期格式轉(zhuǎn)換為文本第63頁/共96頁條件格式5.按照這種方法進(jìn)行判斷，若(TEXT(NOW(),“YYYY-M-D”))-(TEXT(K4,“YYYY-M-D”))>0，則單元格底紋顯示黃色，表示染單出貨期已過；若(TEXT(NOW(),“YYYY-M-D”))-(TEXT(K4,“YYYY-M-D”))<0，則單元格底紋顯示藍(lán)色，表示染單出貨期未到。第64頁/共96頁條件格式6.將條件格式拖拽進(jìn)行復(fù)制，顯示如下效果。第65頁/共96頁條件格式7.對于實(shí)際出貨日期，也可以通過標(biāo)注顏色來識(shí)別。若實(shí)際出貨日期提前或準(zhǔn)時(shí)，則顯示日期字體的顏色為藍(lán)色；若實(shí)際出貨日期推遲，則顯示日期字體的顏色為紅色。第66頁/共96頁條件格式8.最后的效果如圖。當(dāng)然你也可以隨時(shí)根據(jù)自己的需要來調(diào)整條件格式。第67頁/共96頁END條件格式第68頁/共96頁第二部分宏第69頁/共96頁第二部分宏

有關(guān)概念所謂“宏”，就是將一系列的命令和指令組合在一起，形成一個(gè)命令，以實(shí)現(xiàn)任務(wù)執(zhí)行的自動(dòng)化。它可以替代人工進(jìn)行一系列費(fèi)時(shí)而重復(fù)的操作，是一個(gè)極為靈活的自定義命令。在Excel中運(yùn)用宏可有兩種途徑，一種是錄制宏，另一種是采用Excel自帶的VisualBasic編輯器來編輯宏命令。前一種使用較多，操作簡練，也易于理解。這里主要介紹錄制宏的方法。

第70頁/共96頁第二部分宏宏錄制器excel宏錄制器的整個(gè)操必須預(yù)演從哪一個(gè)操作”開始”,一直到結(jié)束的操作程序。首先準(zhǔn)備所需要操作的“工作表”,再啟動(dòng)”宏錄制器”,然后開始執(zhí)行需要錄制的excel的操作,再關(guān)閉”宏錄制器”。這樣您或其它用戶就具有了這種錄好的自動(dòng)程序,并且可以指定一個(gè)按鈕來執(zhí)行此自動(dòng)程序的功能。宏錄制工具欄第71頁/共96頁第二部分宏

應(yīng)用實(shí)例選擇一張公司表格，將分類匯總這個(gè)操作命令錄制成宏命令，并設(shè)置快捷鍵。如果以后需要對表格進(jìn)行分類匯總操作時(shí)，只需執(zhí)行這個(gè)宏命令就可以了。第72頁/共96頁一.錄制宏

操作過程1.打開工作表，選擇“工具”—“宏”—“錄制宏”命令。第73頁/共96頁一.錄制宏2.在彈出的“錄制新宏”對話框中輸入宏的名稱，定義執(zhí)行宏的快捷鍵，并選擇保存在“當(dāng)前工作薄”選項(xiàng)中，然后單擊確定。注意：定義快捷鍵時(shí)，選取的字母必須為大寫。第74頁/共96頁一.錄制宏3.單擊完確定之后的同時(shí)彈出一個(gè)宏錄制工具（如圖），就可以開始執(zhí)行excel的操作.這里在工作表中執(zhí)行一個(gè)分類匯總的動(dòng)作。這里點(diǎn)擊一下相對引用按鈕。第75頁/共96頁一.錄制宏4.選擇“數(shù)據(jù)”—“分類匯總”命令，這里以“合約號(hào)”為分類字段，將下單量和實(shí)際重量匯總求和。第76頁/共96頁一.錄制宏5.完成以后，點(diǎn)擊一下宏錄制器的停止按鈕。第77頁/共96頁一.錄制宏6.為了使用方便，可將其嵌入工具欄中。單擊“工具”—“自定義”，選中“命令”對話框中“類別”下拉框中的“宏”，在右邊可出現(xiàn)“自定義按鈕”，選中它并將其拖至想要放的工具欄上。第78頁/共96頁一.錄制宏7.此時(shí)在工具欄上會(huì)發(fā)現(xiàn)多出一個(gè)形狀的按鈕，單擊“自定義”對話框中的“更改所選內(nèi)容”按鈕，會(huì)出現(xiàn)一下拉菜單，選擇“指定宏”。第79頁/共96頁一.錄制宏8.彈出“指定宏”對話框，選擇宏名。單擊“確定”按鈕。第80頁/共96頁一.錄制宏9.這時(shí)還可更改按鈕圖標(biāo)，選中你喜歡的圖標(biāo)。第81頁/共96頁一.錄制宏10.為便于理解，可選中“圖標(biāo)與文字”選項(xiàng)，工具欄中的該按鈕便改為“圖標(biāo)+說明”的形式，把它命名為“分類匯總”。這樣就完成宏的引用工作。第82頁/共96頁一.錄制宏11.建立了宏以后，還必須設(shè)置宏安全性，選擇“工具”—“選項(xiàng)”命令，打開“選項(xiàng)”對話框，單擊“安全性”選項(xiàng)卡。第83頁/共96頁一.錄制宏12.

單擊“宏安全性”按鈕，打開“安全性”對話框，再單擊“安全級”選項(xiàng)卡，設(shè)置安全級為“中”。第84頁/共96頁一.錄制宏13.設(shè)置安全等級為“中”后，以后每次打開該工作薄，系統(tǒng)將顯示提示，單擊“取消宏”，則宏命令失效，單擊“啟用宏”，便可啟動(dòng)宏