項(xiàng)目9：產(chǎn)品銷售統(tǒng)計(jì)分析

項(xiàng)目9：產(chǎn)品銷售統(tǒng)計(jì)分析教師：電話：輔導(dǎo)時(shí)間：郵箱：第二部分Excel2016高級(jí)應(yīng)用案例知識(shí)技能點(diǎn)跨表選擇數(shù)據(jù)跨表查詢數(shù)據(jù)分類匯總復(fù)雜多級(jí)分類匯總單條件匯總多條件匯總數(shù)據(jù)篩選高級(jí)篩選數(shù)據(jù)透視圖和數(shù)據(jù)透視表統(tǒng)計(jì)提成額度Excel圖表利用條件格式顯示特殊數(shù)據(jù)保護(hù)工作表9.1項(xiàng)目背景由于小王能熟練的運(yùn)用Excel處理數(shù)據(jù)，并能熟練的運(yùn)用Word排版，得到了公司銷售經(jīng)理的青睞，希望小王能在每個(gè)月的月末，幫助他統(tǒng)計(jì)分析銷售部門每個(gè)員工的銷售情況，將基本的銷售記錄表交由小王處理，同時(shí)還希望小王能協(xié)助銷售部門錄入銷售數(shù)據(jù)。為簡化錄入過程，小王做了很多處理，包括員工姓名自動(dòng)從銷售員工表獲取，用函數(shù)從其他表格里面獲取產(chǎn)品相關(guān)信息，并設(shè)計(jì)了公式自動(dòng)計(jì)算每筆銷售產(chǎn)品的總價(jià)，極大的簡化了銷售數(shù)據(jù)的錄入，并在此基礎(chǔ)上進(jìn)行進(jìn)一步分析。9.2.項(xiàng)目簡介在該項(xiàng)目中，可以通過設(shè)計(jì)序列的方式，從其他表中獲取員工和產(chǎn)品數(shù)據(jù)，輸入數(shù)據(jù)時(shí)，直接從下拉菜單中選擇員工和產(chǎn)品名稱，當(dāng)選擇產(chǎn)品名稱后，在表格中自動(dòng)從其他表中查詢產(chǎn)品分類、編號(hào)、定價(jià)等數(shù)據(jù)，簡化數(shù)據(jù)錄入，提高錄入工作效率和準(zhǔn)確性在正確錄入數(shù)據(jù)基礎(chǔ)上，完成分類匯總、復(fù)雜多級(jí)分類匯總、單條件匯總、多條件匯總、數(shù)據(jù)篩選、高級(jí)篩選、制作數(shù)據(jù)透視圖和透視表、統(tǒng)計(jì)提成額度、用圖表的形式分析銷售情況，利用條件格式標(biāo)注特殊數(shù)據(jù)，為了保證數(shù)據(jù)安全，還需要對(duì)文檔的安全做設(shè)置。9.3.項(xiàng)目制作9.3.1.跨表自動(dòng)選擇員工姓名和產(chǎn)品名稱在錄入產(chǎn)品銷售記錄表中的員工姓名時(shí)，我們總希望能夠直接從列表中選擇而不用重新錄入。員工的姓名，已經(jīng)在員工表中存在了，因此，我們可以在銷售記錄表中的員工姓名列，引用員工信息表中員工姓名，直接從列表中選擇，當(dāng)員工信息表中員工姓名變化后，在銷售記錄表中會(huì)自動(dòng)改變，極大的提高錄入效率和準(zhǔn)確性。具體步驟如下：步驟1：打開“員工信息表.xlsx”（此處我們用案例一種的員工信息表為數(shù)據(jù)源），選中員工的所有姓名（當(dāng)然也可以選擇整個(gè)姓名列），選擇【公式】選項(xiàng)卡下的【定義名稱】按鈕。如圖9-1所示。圖9-1定義引用名稱選項(xiàng)步驟2：在彈出的【新建名稱】中輸入引用名稱“員工表姓名”，點(diǎn)擊【確定】按鈕，如圖9-2所示圖9-2命名引用名稱步驟3：打開“銷售記錄表.xlsx”文件，選中“員工姓名”下的所有單元格，用步驟1的方式，定義引用名稱，如圖9-3所示。圖9-3定義銷售表引用名稱步驟4：在彈出的【新建名稱】對(duì)話框中，在名稱欄輸入“銷售表員工姓名”，在【引用位置】填寫“=員工信息表.xlsx!員工表姓名”，點(diǎn)擊【確定】按鈕，如圖9-4所示。圖9-4定義名稱的引用范圍【小貼士】在整個(gè)步驟中，步驟4是非常重要的一個(gè)環(huán)節(jié)，特別是在【引用位置】欄目，一定要填寫正確，以等號(hào)“=”開頭，接著明確寫明要引用的另外一個(gè)表的名稱，此處為“員工信息表.xlsx”，后面跟上英文的嘆號(hào)“！”，后面寫上在步驟2定義的引用名稱，此處一定要對(duì)應(yīng)上，否則不能正確引用。注意，所有的符號(hào)，都應(yīng)在英文輸入狀態(tài)下輸入。步驟5：在銷售記錄表中，選中“員工姓名”下的單元格，點(diǎn)擊【數(shù)據(jù)】菜單下的【數(shù)據(jù)驗(yàn)證】按鈕，如圖9-5所示。圖9-5選擇數(shù)據(jù)驗(yàn)證步驟6：在彈出的【數(shù)據(jù)驗(yàn)證】對(duì)話框中，【允許】列表中選擇【序列】，確保選中【提供下拉箭頭】前的復(fù)選框，在【來源】文本框中輸入步驟4定義的名稱，此處為“銷售表員工姓名”，點(diǎn)擊【確定】按鈕，如圖9-6所示。圖9-6定義數(shù)據(jù)驗(yàn)證上述設(shè)置完畢，即完成了對(duì)員工姓名列表選擇的設(shè)置，通過單元格旁邊的下小三角形符號(hào)選擇員工姓名，當(dāng)員工信息表的數(shù)據(jù)發(fā)生了變化，此處的數(shù)據(jù)跟著自動(dòng)變化，極大的方便了數(shù)據(jù)錄入，如圖9-7所示?？梢杂孟嗤霓k法設(shè)置產(chǎn)品名稱的輸入方式，此處不再贅述?！拘≠N士】由于要根據(jù)員工信息表.xlsx和產(chǎn)品信息表.xlsx的表名稱引用，因此當(dāng)做了上述設(shè)置之后，對(duì)產(chǎn)品信息表.xlsx和員工信息表.xlsx的文件名不能再做改動(dòng)，否則不能在下拉列表中出現(xiàn)相應(yīng)的數(shù)據(jù)。圖9-7下拉列表選擇員工姓名9.3.2.用Vlookup函數(shù)跨表查詢當(dāng)通過上述設(shè)置從下拉列表選擇產(chǎn)品名稱數(shù)據(jù)后，為了方便錄入，還要根據(jù)產(chǎn)品名稱自動(dòng)填入相應(yīng)的類別、單價(jià)、產(chǎn)品編號(hào)等數(shù)據(jù)，避免錄入過程錯(cuò)誤，提高數(shù)據(jù)錄入效率和準(zhǔn)確性。由于涉及到跨越不同表進(jìn)行查詢，可以采用VLOOKUP函數(shù)實(shí)現(xiàn)。具體方法如下：步驟1：打開銷售記錄表和產(chǎn)品信息表，定位到銷售記錄表要查找編號(hào)的單元格（本例為E3）內(nèi)輸入公式：“=VLOOKUP(D3,[產(chǎn)品信息表.xlsx]Sheet1!$B$3:$E$13,3,FALSE)”（不包含引號(hào)），下圖描述了VLOOKUP每個(gè)參數(shù)對(duì)應(yīng)的數(shù)據(jù)項(xiàng)：D3，表示要查找的對(duì)象，為“銷售記錄表”的產(chǎn)品名稱，第二個(gè)參數(shù)為查閱區(qū)域，用鼠標(biāo)選擇，定位到“產(chǎn)品信息表”的$B$3:$E$13區(qū)域查找數(shù)據(jù)，第三個(gè)參數(shù)“3”表示要取“產(chǎn)品信息表”$B$3:$E$13區(qū)域中第3列數(shù)據(jù)，即“產(chǎn)品編號(hào)”，第四個(gè)參數(shù)，表示在查閱數(shù)據(jù)時(shí)，要精確匹配?；剀囨I確認(rèn)，完成根據(jù)產(chǎn)品名稱自動(dòng)從產(chǎn)品信息表查閱產(chǎn)品編號(hào)的設(shè)置。如圖9-8所示。圖9-8使用VLOOKUP函數(shù)跨表查閱數(shù)據(jù)步驟2：設(shè)置銷售記錄表的產(chǎn)品編號(hào)下的其他單元格自動(dòng)填充。當(dāng)用下拉方式完成自動(dòng)填充后，發(fā)現(xiàn)沒有選擇產(chǎn)品的單元給出現(xiàn)“#N/A”的錯(cuò)誤提示，原因是在產(chǎn)品名稱列沒有產(chǎn)品，導(dǎo)致數(shù)據(jù)不能顯示出來，影響美觀，為讓下拉不出現(xiàn)該錯(cuò)誤提示，可以將E3單元格的公式改為：“=IFERROR(VLOOKUP(D4,[產(chǎn)品信息表.xlsx]Sheet1!$B$3:$E$13,3,FALSE),"")”（不包含引號(hào)），再向下拉填充手柄完成自動(dòng)填充，則不再出現(xiàn)該錯(cuò)誤提示。步驟3：用相同的方法，設(shè)置產(chǎn)品類別和產(chǎn)品單價(jià)價(jià)格的查詢。在銷售記錄表的F3單元格輸入的公式為：=IFERROR(VLOOKUP(D3,[產(chǎn)品信息表.xlsx]Sheet1!$B$3:$E$13,2,FALSE),"")，產(chǎn)品單價(jià)設(shè)置類似。自此，在銷售記錄表中，當(dāng)我們選擇產(chǎn)品名稱后，產(chǎn)品編號(hào)、產(chǎn)品類別和產(chǎn)品單價(jià)會(huì)自動(dòng)產(chǎn)生，減少了大量的錄入工作，同時(shí)又提高了準(zhǔn)確性。當(dāng)在選擇產(chǎn)品后，若發(fā)現(xiàn)不能自動(dòng)產(chǎn)生產(chǎn)品類別、產(chǎn)品編號(hào)和產(chǎn)品單價(jià)數(shù)據(jù)，可以在上一個(gè)單元格向下拉動(dòng)填充手柄自動(dòng)填充，即可正確產(chǎn)生數(shù)據(jù)?！拘≠N士】如果需要在表格或區(qū)域中按行查找內(nèi)容，可使用VLOOKUP，它是一個(gè)查找和引用函數(shù)。例如，按產(chǎn)品名稱查找對(duì)應(yīng)的價(jià)格。VLOOKUP函數(shù)表示：=VLOOKUP（要查找的值、要在其中查找值的區(qū)域、區(qū)域中包含返回值的列號(hào)、精確匹配或近似匹配–指定為0/FALSE或1/TRUE）。（1）要查找的值，也被稱為查閱值。（2）查閱值所在的區(qū)域。請記住，查閱值應(yīng)該始終位于所在區(qū)域的第一列，這樣VLOOKUP才能正常工作。（3）區(qū)域中包含返回值的列號(hào)。例如，如果指定B2：D11作為區(qū)域，那么應(yīng)該將B算作第一列，C作為第二列，以此類推。（4）如果需要返回值的近似匹配，可以指定TRUE；如果需要返回值的精確匹配，則指定FALSE。如果沒有指定任何內(nèi)容，默認(rèn)值將始終為TRUE或近似匹配。9.3.3.設(shè)置價(jià)格單元格格式為讓銷售記錄表的“產(chǎn)品單價(jià)”、“銷售單價(jià)”和“銷售總價(jià)”等列顯示為特殊的貨幣格式，如“￥68.00”，需要將每個(gè)單元格的數(shù)據(jù)格式設(shè)置為貨幣格式，選中列或單元格，點(diǎn)擊右鍵，選擇【設(shè)置單元格格式】，在彈出的對(duì)話框中，在【分類】列表中選擇【貨幣】，設(shè)置小數(shù)位數(shù)為2，貨幣符號(hào)選擇人民幣格式，點(diǎn)擊【確定】按鈕，即完成了貨幣格式設(shè)置。如圖9-9所示。用相同方法，完成其他單元格格式的顯示設(shè)置。圖9-9設(shè)置單元格貨幣格式9.3.4.分類匯總按員工姓名完成對(duì)各產(chǎn)品的銷售總價(jià)匯總。特別注意的是，在進(jìn)行分類匯總之前，需將分類字段進(jìn)行排序，步驟如下：步驟1：點(diǎn)擊【數(shù)據(jù)】選項(xiàng)卡，選擇【排序】按鈕，彈出排序?qū)υ捒?，在【主關(guān)鍵字】處選擇【員工姓名】，先對(duì)數(shù)據(jù)按員工姓名排序；步驟2：選中數(shù)據(jù)表（必須將標(biāo)題行選中），單擊【數(shù)據(jù)】選項(xiàng)卡，選擇【分類匯總】按鈕。步驟3：在【分類匯總】對(duì)話框中，選擇分類字段為“員工姓名”，匯總方式為【求和】，【選定匯總項(xiàng)】為“銷售總額”，在此處可以多選，如圖9-10所示圖9-10分類匯總設(shè)置圖9-11分類匯總結(jié)果分類匯總后，在數(shù)據(jù)表的左上角出現(xiàn)的標(biāo)識(shí)，表示有3個(gè)層次的數(shù)據(jù)，其中第3層次，表示數(shù)據(jù)的明細(xì)層次，第2層次顯示每個(gè)員工的匯總數(shù)據(jù)，第1層次，顯示全部員工的匯總結(jié)果。9.3.5.復(fù)雜多級(jí)分類匯總默認(rèn)情況下，根據(jù)一個(gè)分類字段對(duì)數(shù)據(jù)的分類匯總只有3級(jí)，如果要實(shí)現(xiàn)更多級(jí)別的分類匯總，需要在單字段分類匯總的基礎(chǔ)之上，做更多的設(shè)置。案例要求：將數(shù)據(jù)按產(chǎn)品類別、產(chǎn)品名稱和員工姓名進(jìn)行分類匯總，并對(duì)銷售總價(jià)和欠款數(shù)求和。具體步驟如下：步驟1：首先將數(shù)據(jù)分別按產(chǎn)品類別、產(chǎn)品名稱和員工排序。點(diǎn)擊菜單【數(shù)據(jù)】，選擇【排序】按鈕，彈出排序?qū)υ捒?，在【排序】?duì)話框中通過【添加條件】按鈕，選擇列和排序次序，設(shè)置完畢后如圖9-12所示。點(diǎn)擊【確定】按鈕后完成對(duì)數(shù)據(jù)的多條件排序。圖9-12多關(guān)鍵字排序步驟2：設(shè)置按產(chǎn)品類別分類匯總。選中匯總數(shù)據(jù)區(qū)域，點(diǎn)擊【數(shù)據(jù)】選項(xiàng)卡，選擇【分類匯總】按鈕，在【分類字段】選擇“產(chǎn)品類別”，【匯總方式】選擇“求和”，【選定匯總項(xiàng)】同時(shí)勾選“銷售總價(jià)”和“欠款數(shù)”，點(diǎn)擊【確定】按鈕，完成按產(chǎn)品種類的分類匯總。如圖9-13所示圖9-13按產(chǎn)品類別分類匯總按產(chǎn)品類別的分類匯總結(jié)果，如圖9-14所示圖9-14產(chǎn)品類別分類匯總結(jié)果步驟3：再次點(diǎn)擊【分類匯總】，在【分類字段】選擇“產(chǎn)品名稱”，一定確保不選擇【替換當(dāng)前分類匯總】前的復(fù)選框，點(diǎn)擊確定按鈕，如圖9-15所示圖9-15按產(chǎn)品名稱分類匯總匯總后的結(jié)果如圖9-16所示。圖9-16二級(jí)分類匯總步驟4：再次點(diǎn)擊【分類匯總】，在【分類字段】選擇“員工姓名”，一定確保不選擇【替換當(dāng)前分類匯總】前的復(fù)選框，點(diǎn)擊確定按鈕，如圖9-17所示圖9-17三級(jí)分類匯總當(dāng)設(shè)置完畢后，點(diǎn)擊表格左上角的層次等級(jí)，可顯示不同級(jí)別的分類匯總數(shù)據(jù)，如圖9-18所示。圖9-18多級(jí)分類匯總結(jié)果9.3.6.單條件匯總實(shí)際應(yīng)用中，我們經(jīng)常遇到需要根據(jù)一定的條件來匯總數(shù)據(jù)，比如，需要計(jì)算“銷售高于20000元的所有員工銷售總價(jià)之和”、“銷售高于20000元的所有欠款之和”等，要求在匯總銷售總價(jià)的時(shí)候，需要判斷該行數(shù)據(jù)中的銷售總價(jià)是否滿足20000條件。根據(jù)條件的個(gè)數(shù)，分為單條件匯總和多條件匯總。單條件匯總，可以用SUMIF()函數(shù)實(shí)現(xiàn)，多條件匯總可以采用SUMIFIS()函數(shù)實(shí)現(xiàn)。本節(jié)僅講解單條件匯總，多條件匯總請參見下一節(jié)。在匯總單元格輸入公式“=SUMIF(J3:J14,">=20000")”（不包含引號(hào)），其中J3:J14，表示要根據(jù)條件計(jì)算的單元格，“>=20000”是條件，該公式的意思是，在J3:J14單元格范圍內(nèi)，計(jì)算“>=20000”的所有數(shù)值之和。如圖9-19所示。圖9-19單條件匯總與前一個(gè)不同，統(tǒng)計(jì)“銷售高于20000的所有欠款之和”是在判斷J3:J14單元格數(shù)據(jù)是否大于等于20000的基礎(chǔ)之上，對(duì)欠款列（,K3:K14）的數(shù)據(jù)進(jìn)行求和。在需要求和的單元格內(nèi)，輸入公式“=SUMIF(J3:J14,">=20000",K3:K14)”（不包含引號(hào)，所有標(biāo)點(diǎn)符號(hào)，均要求在英文輸入法狀態(tài)下輸入），其中J3:J14為條件比較區(qū)域，比較條件為“>=20000”，若某行滿足條件，則在K3:K14范圍內(nèi)找到該數(shù)據(jù)求和。如圖9-20所示。圖9-20單條件不同區(qū)域求和【小貼士】1、SUMIF函數(shù)的用法和COUNTIF的用法類似；2、SUMIF函數(shù)的使用說明：

語法：SUMIF(range,criteria,[sum_range])Range：區(qū)域，必需。根據(jù)條件進(jìn)行計(jì)算的單元格的區(qū)域，每個(gè)區(qū)域中的單元格必須是數(shù)字或名稱、數(shù)組或包含數(shù)字的引用。空值和文本值將被忽略。criteria條件，必需。用于確定對(duì)哪些單元格求和的條件，其形式可以為數(shù)字、表達(dá)式、單元格引用、文本或函數(shù)。例如，條件可以表示為32、">32"、B5、"32"、"蘋果"或TODAY()。sum_range求和區(qū)域，可選。要求和的實(shí)際單元格（如果要對(duì)未在range參數(shù)中指定的單元格求和）。如果省略sum_range參數(shù)，Excel會(huì)對(duì)在range參數(shù)中指定的單元格（即應(yīng)用條件的單元格）求和。

可以在criteria參數(shù)中使用通配符（包括問號(hào)(?)和星號(hào)(*)）。問號(hào)匹配任意單個(gè)字符；星號(hào)匹配任意一串字符。9.3.7.多條件匯總?cè)缫y(tǒng)計(jì)不同產(chǎn)品類別，銷售數(shù)量大于等于40的銷售總和，涉及到兩個(gè)條件，第一是比較產(chǎn)品類別，第二個(gè)是銷量大于等于40，可以用SUMIFS函數(shù)來實(shí)現(xiàn)多條件的匯總。在需要匯總的單元格中輸入公式“=SUMIFS($J$3:$J$14,$F$3:$F$14,N3,$I$3:$I$14,O3)”（不包含引號(hào)，所有的符號(hào)均在英文下輸入，比較區(qū)域用絕對(duì)引用），公式中的第一個(gè)參數(shù)，表示滿足條件需要求和的區(qū)域，第二個(gè)參數(shù)為第一個(gè)比較區(qū)域，第三個(gè)參數(shù)表示第一個(gè)條件區(qū)域，第四個(gè)參數(shù)表示第二個(gè)比較區(qū)域，第五個(gè)參數(shù)表示第二個(gè)條件區(qū)域，以此類推。如圖9-21所示。圖9-21多條件匯總【小貼士】1、SUMIFS函數(shù)的用法與COUNTIFS的用法類似；SUMIFS函數(shù)的用法：語法：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)數(shù)名稱說明Sum_range

（必需）要求和的單元格區(qū)域。

Criteria_range1

（必需）使用Criteria1測試的區(qū)域。Criteria_range1和Criteria1設(shè)置用于搜索某個(gè)區(qū)域是否符合特定條件的搜索對(duì)。一旦在該區(qū)域中找到了項(xiàng)，將計(jì)算Sum_range中的相應(yīng)值的和。Criteria1

（必需）定義將計(jì)算Criteria_range1中的哪些單元格的和的條件。例如，可以將條件輸入為32、">32"、B4、"蘋果"或"32"。Criteria_range2,criteria2,…

(optional)附加的區(qū)域及其關(guān)聯(lián)條件。最多可以輸入127個(gè)區(qū)域/條件對(duì)。9.3.8.數(shù)據(jù)篩選使用自動(dòng)篩選或內(nèi)置比較運(yùn)算符（例如“大于”和“前10個(gè)”等）可顯示所需的數(shù)據(jù)并隱藏其余數(shù)據(jù)。數(shù)據(jù)經(jīng)過篩選后，可以重新應(yīng)用篩選器獲取最新結(jié)果，或清除篩選器重新顯示所有數(shù)據(jù)。經(jīng)篩選過的數(shù)據(jù)僅顯示滿足指定條件的行，并隱藏不希望顯示的行。篩選數(shù)據(jù)之后，對(duì)于篩選過的數(shù)據(jù)的子集，不需要重新排列或移動(dòng)就可以復(fù)制、查找、編輯、設(shè)置格式、制作圖表和打印，還可以按多個(gè)列進(jìn)行篩選。篩選器是累加的，這意味著每個(gè)追加的篩選器都基于當(dāng)前篩選結(jié)果，從而進(jìn)一步減少了數(shù)據(jù)的子集。點(diǎn)擊【數(shù)據(jù)】選項(xiàng)卡下的【篩選】按鈕，如圖9-22所示。圖9-22數(shù)據(jù)篩選單擊列標(biāo)題中的箭頭，然后單擊【文本篩選】或【數(shù)字篩選】。單擊其中一個(gè)比較運(yùn)算符，可以完成多種類型的篩選。例如，若要顯示介于下限與上限之間的數(shù)字，請選擇【介于】，如圖9-23所示。圖9-23篩選選項(xiàng)在【自定義自動(dòng)篩選】框中，鍵入和選擇條件篩選數(shù)據(jù)。例如，在大于或等于后面的文本框中，鍵入200，然后在小于或等于框中，輸入1000，點(diǎn)擊【確定】按鈕后，將在表格中顯示大于等于200并且小于或等于1000的數(shù)據(jù)。如圖9-24所示。圖9-24自定義篩選當(dāng)要清除篩選時(shí)，選中數(shù)據(jù)列，單擊列標(biāo)題中的箭頭，在出現(xiàn)的框中，勾選【全選】之前的復(fù)選框即可，如圖9-25所示。也可以選中篩選列，再次點(diǎn)擊【數(shù)據(jù)】選項(xiàng)卡下的【篩選】按鈕即可。圖9-25取消篩選9.3.9.高級(jí)篩選當(dāng)需要篩選的條件比較復(fù)雜的時(shí)候，比如，要找員工是田水冬或者王三明，并且銷售總價(jià)在5000元到28000元的所有信息，則可以采用“高級(jí)篩選”來完成。具體步驟如下：步驟1：在表格的空白區(qū)域，復(fù)制原表的列名，比如復(fù)制“員工姓名”和兩個(gè)“銷售總價(jià)”到空白區(qū)域；步驟2：在員工姓名列，分別復(fù)制田水冬和王三明兩個(gè)人的姓名，在條件區(qū)域中的“銷售總額”列，分別輸入條件“>=5000”和“<=20000”，如圖9-26所示圖9-26高級(jí)篩選條件區(qū)域步驟3：選擇【數(shù)據(jù)】-【排序和篩選】功能組中的【高級(jí)】按鈕，彈出【高級(jí)篩選】對(duì)話框，在【方式】下選擇“將篩選結(jié)果復(fù)制到其他位置”，以避免篩選結(jié)果覆蓋原處的數(shù)據(jù)，可以幫助我們檢驗(yàn)設(shè)置是否正確，在【列表區(qū)域】選擇原數(shù)據(jù)（注意要包含數(shù)據(jù)的標(biāo)題行），【條件區(qū)域】選擇篩選條件（也要包含復(fù)制過來的標(biāo)題行），在【復(fù)制到】欄點(diǎn)擊表格的空白單元格，最后點(diǎn)擊【確定】按鈕，即將篩選的結(jié)果，放在以【復(fù)制到】文本框指定的為第一個(gè)單元格的范圍內(nèi)。如圖9-27所示。圖9-27高級(jí)篩選對(duì)話框【小貼士】對(duì)不同高級(jí)篩選條件的解讀員工姓名銷售總價(jià)解讀：篩選姓名為“田水冬”并且銷售總價(jià)>=5000的數(shù)據(jù)田水冬>=5000員工姓名銷售總價(jià)解讀：篩選姓名為“田水冬”或者銷售總價(jià)>=5000的數(shù)據(jù)田水冬

>=5000員工姓名銷售總價(jià)解讀：篩選姓名為“田水冬”或者為“王三明”的數(shù)據(jù)田水冬

王三明

員工姓名銷售總價(jià)解讀：篩選姓名為“田水冬”，或者姓名為“王三明”并且銷售總價(jià)>=5000的數(shù)據(jù)，結(jié)果田水冬的所有數(shù)據(jù)，和銷售總價(jià)>=5000的王三明數(shù)據(jù)田水冬

王三明>=5000員工姓名銷售總價(jià)銷售總價(jià)解讀：篩選姓名為“田水冬”，并且銷售總額在5000到20000之間的數(shù)據(jù);或者姓名為“王三明”并且銷售總價(jià)大于等于5000的數(shù)據(jù)。田水冬>=5000<=20000王三明>=5000

員工姓名銷售總價(jià)解讀：篩選姓名第一個(gè)字為“田”，即姓“田”的并且銷售總價(jià)>=5000的數(shù)據(jù)田*>=50009.3.10.制作數(shù)據(jù)透視圖和數(shù)據(jù)透視表要求：統(tǒng)計(jì)不同產(chǎn)品類別下不同產(chǎn)品的銷售總額的數(shù)據(jù)透視表和數(shù)據(jù)透視圖。具體步驟如下：步驟1：點(diǎn)擊【插入】選項(xiàng)卡，選擇【圖表】功能組的【數(shù)據(jù)透視圖】按鈕，選擇【數(shù)據(jù)透視圖和數(shù)據(jù)透視表】命令。如圖9-28所示。圖9-28數(shù)據(jù)透視圖和數(shù)據(jù)透視表選項(xiàng)步驟2：在彈出的【創(chuàng)建數(shù)據(jù)透視表】對(duì)話框中，【選擇一個(gè)表或區(qū)域】欄選擇要?jiǎng)?chuàng)建透視圖和透視表的數(shù)據(jù)區(qū)域，【選擇放置數(shù)據(jù)透視表的位置】根據(jù)自己需要選擇適當(dāng)位置，既可以新建一個(gè)數(shù)據(jù)表來存放，也可以在本表存放，設(shè)置完畢，點(diǎn)擊【確定】按鈕后，將出現(xiàn)數(shù)據(jù)數(shù)據(jù)透視表1和圖表1兩個(gè)窗口，可以分別在其中創(chuàng)建透視表和透視圖。如圖9-29所示圖9-29創(chuàng)建數(shù)據(jù)透視表對(duì)話框步驟3：設(shè)置透視圖和透視表數(shù)據(jù)項(xiàng)。選擇圖表1或數(shù)據(jù)透視表1，將在右邊出現(xiàn)“數(shù)據(jù)透視圖字段”框，可以設(shè)置數(shù)據(jù)透視圖的統(tǒng)計(jì)字段。選擇字段，拖動(dòng)到相應(yīng)區(qū)域，在左邊的透視圖和透視表實(shí)時(shí)顯示設(shè)置效果。設(shè)置完畢，點(diǎn)擊表格的任意空白處，即可查看透視圖和透視表，可以通過選擇“產(chǎn)品類別”下的下三角形箭頭，選擇某類，具體查看，也可以點(diǎn)擊某一行中的“產(chǎn)品名稱”，選擇某產(chǎn)品查看，如圖9-30所示。圖9-30透視圖和透視表設(shè)置9.3.11.統(tǒng)計(jì)提成額度產(chǎn)品種類不同，銷售提成的比例也不同，那么，如何計(jì)算每個(gè)員工所銷售產(chǎn)品的提成額度呢？以本例為例，假設(shè)藍(lán)牙設(shè)備的提成額度為10%，存儲(chǔ)設(shè)備的提成額度為8%，軟件類產(chǎn)品的提成額度為25%，要求快速計(jì)算每個(gè)員工的所有銷售提成額度。具體思路為：第一步：首先需要統(tǒng)計(jì)每個(gè)人每類產(chǎn)品的銷售總價(jià)，可以用透視表實(shí)現(xiàn)；第二步：在透視表的基礎(chǔ)上，根據(jù)不同產(chǎn)品不同的提成比例，計(jì)算每個(gè)人的提成額度，可以自己寫公式來完成。具體實(shí)現(xiàn)步驟如下：步驟1：建立行名稱為“員工姓名”，列名稱為“產(chǎn)品類別”的透視表，如圖9-31所示圖9-31統(tǒng)計(jì)每個(gè)員工銷售產(chǎn)品的總額步驟2：可以直接在透視圖表基礎(chǔ)上直接用公式計(jì)算，但為簡便起見，將透視表復(fù)制到其他單元格，使得公式更為簡潔。選中透視表內(nèi)容，按【Ctrl+C】鍵復(fù)制，然后點(diǎn)擊【開始】菜單下的【粘貼】按鈕，點(diǎn)擊【粘貼數(shù)字】組中某一個(gè)格式，此處選擇第三個(gè)，將原表的格式一并復(fù)制，但只保留數(shù)字，如圖9-32所示圖9-32選擇性粘貼步驟3：在粘貼后的表格基礎(chǔ)上，根據(jù)不同類別的提成比例，寫公式完成計(jì)算，再向下自動(dòng)填充即可，如圖9-33所示。【小貼士】用此方法計(jì)算的提成數(shù)據(jù)，不會(huì)隨原始數(shù)據(jù)的變化而自動(dòng)變化，如果在分類匯總基礎(chǔ)上直接計(jì)算提成，則可隨基礎(chǔ)數(shù)據(jù)的變化而自動(dòng)變化，請讀者自行嘗試。圖9-33自動(dòng)計(jì)算提成額度9.3.12.圖表分析銷售情況在數(shù)據(jù)透視圖的基礎(chǔ)上，用圖表展示每類產(chǎn)品的銷售量占總銷量的百分比統(tǒng)計(jì)圖。具體步驟如下：步驟1：將數(shù)據(jù)透視圖復(fù)制到適當(dāng)位置（采用選擇性粘貼-粘貼數(shù)值）方式粘貼。步驟2：選擇要制作圖的數(shù)據(jù)區(qū)域，本例中選擇B24:D24，再按住CTRL鍵，選擇B31:D31，即可選定圖表需要的數(shù)據(jù)，如圖9-34所示。圖9-34選擇不連續(xù)的數(shù)據(jù)區(qū)域步驟3：選擇【插入】菜單下的【圖表】功能組，選擇餅圖，如圖9-35所示。圖9-35插入圖表選項(xiàng)步驟4：配置餅圖。選擇餅圖，設(shè)置圖表的各種屬性，如圖9-36所示。圖9-36設(shè)置圖表屬性9.3.13.利用條件格式顯示特殊數(shù)據(jù)條件格式，可以將顏色應(yīng)用于根據(jù)特定條件，如顯示重復(fù)值數(shù)據(jù)，滿足特定條件如大于或等100的數(shù)據(jù)，也可以顯示單元格排名、數(shù)據(jù)條、色階和圖標(biāo)集等。條件格式是動(dòng)態(tài)的，當(dāng)數(shù)值改變時(shí)，格式將自動(dòng)調(diào)整。使用顏色顯示重復(fù)值1、選擇數(shù)據(jù)區(qū)域，在【開始】選項(xiàng)卡上，單擊【條件格式】->【突出顯示單元格規(guī)則】->【重復(fù)值】，如圖9-37所示。圖9-37顯示重復(fù)值2、從下拉列表中，選擇一種格式，單擊確定以設(shè)置單元格格式，如圖9-38所示。圖9-38重復(fù)值顏色設(shè)置【小貼士】將設(shè)置重復(fù)值之后的數(shù)據(jù)按顏色排序，可以快速修改或刪除重復(fù)數(shù)據(jù)。選擇數(shù)據(jù)區(qū)域，然后在【數(shù)據(jù)】選項(xiàng)卡，單擊任意位置，然后單擊【排序和篩選】，點(diǎn)擊【排序】。選擇排序上的排序依據(jù)為員工姓名>單元格顏色，然后次序?yàn)椋骸具x擇排序顏色】，放在最前面，然后單擊【確定】按鈕即可。突出顯示前10個(gè)項(xiàng)目【前10項(xiàng)】選項(xiàng)可以快速確定排名前10的數(shù)據(jù)，、最后10項(xiàng)等選項(xiàng)。1.選擇要設(shè)置格式的區(qū)域，本例選擇“銷售總價(jià)”列2.在開始選項(xiàng)卡上，單擊【條件格式】->【選取規(guī)則】->【前10項(xiàng)】，如圖9-39所示。3.設(shè)置填充顏色，點(diǎn)擊【確定】按鈕，應(yīng)用條件格式設(shè)置。圖9-39突出顯示排名前10的數(shù)據(jù)用彩色數(shù)據(jù)條標(biāo)識(shí)數(shù)據(jù)的大小可以使用帶有顏色的數(shù)據(jù)條，標(biāo)識(shí)數(shù)據(jù)的大小，數(shù)據(jù)越大，數(shù)據(jù)條越長，可以直觀的標(biāo)注數(shù)據(jù)的大小。選擇【條件格式】->【數(shù)據(jù)條】，選擇適當(dāng)?shù)臉?biāo)注方式。如果不能滿足自己需求，可以通過【其他規(guī)則】命令自建規(guī)則，如圖9-40所示。圖9-40用數(shù)據(jù)條標(biāo)注數(shù)據(jù)設(shè)置結(jié)果如圖9-41所示圖9-41數(shù)據(jù)條標(biāo)注數(shù)據(jù)結(jié)果用色階標(biāo)注數(shù)據(jù)可以選擇用不同顏色標(biāo)注數(shù)據(jù)的大小，數(shù)據(jù)越大，顏色越深（當(dāng)然可以自行設(shè)置其他規(guī)則）。選擇數(shù)據(jù)，選擇【條件格式】->【色階】，選擇適當(dāng)顏色，即可完成標(biāo)注。如圖9-42所示。圖9-42使用色階標(biāo)注數(shù)據(jù)使用圖標(biāo)標(biāo)注數(shù)據(jù)可以使用更直觀的圖標(biāo)來標(biāo)注數(shù)據(jù)，各圖標(biāo)所代表的意思，可以是與平均值比較，也可以與特定值比較。選擇數(shù)據(jù)，點(diǎn)擊【條件格式】->【圖標(biāo)】，選擇適當(dāng)圖標(biāo)即可。如圖9-43所示。圖9-43使用圖標(biāo)標(biāo)注數(shù)據(jù)若上述規(guī)則并不能滿足要求，可點(diǎn)擊圖標(biāo)最下面的【新建規(guī)則】命令，創(chuàng)建自定義條件的顯示規(guī)則，如圖9-44所示圖9-44創(chuàng)建圖標(biāo)顯示規(guī)則設(shè)置結(jié)果如圖9-45所示，可以明顯標(biāo)注特定條件下的顯示結(jié)果。圖9-45設(shè)置圖表顯示規(guī)則結(jié)果9.3.14.保護(hù)工作表若要防止其他用戶意外或有意更改、移動(dòng)或刪除工作表中的數(shù)據(jù)，可以鎖定Excel工作表上的單元格，再使用密碼保護(hù)工作表。假設(shè)你擁有團(tuán)隊(duì)狀態(tài)報(bào)告工作表，在該工作表中，你希望團(tuán)隊(duì)成員僅可在特定單元格中添加數(shù)據(jù)且無法修改任何其他內(nèi)容。通過使用工作表保護(hù)，可以使工作表的特定部分可編輯，而用戶將無法修改工作表中任何其他區(qū)域中的數(shù)據(jù)。保護(hù)工作表和公式如果你不希望別人修改表格中的內(nèi)容，以防止其他用戶修改或刪除數(shù)據(jù)；或者不希望其他人查看或更改你的公式，你可以隱藏并保護(hù)這些公式，以防止其他人在工作表單元格以及工作表頂部的編輯欄中查看它們。具體步驟如下：1.單擊工作表左上角中的“全選”按鈕，選定整個(gè)工作表。2.右鍵單擊工作表中的任何單元格，然后選擇【設(shè)置單元格格式】。3.單擊【保護(hù)】選項(xiàng)卡，清除【鎖定】復(fù)選框，然后單擊【確定】，如所示。圖9-46清除鎖定復(fù)選框4.當(dāng)做完上述設(shè)置之后，會(huì)在有公式的單元格中以“左上三角形符號(hào)”的樣式標(biāo)注該單元格使用了公式，選定要隱藏公式的單元格區(qū)域，按Ctrl選擇非相鄰區(qū)域。5.右鍵單擊選定的單元格，然后選擇【設(shè)置單元格格式】。6.單擊【保護(hù)】選項(xiàng)卡，選擇【鎖定】和【隱藏】復(fù)選框，然后單擊“確定”。7.在【審閱】選項(xiàng)卡上，單擊【保護(hù)工作表】，如圖9-47所示圖9-47保護(hù)工作表選項(xiàng)8.請確保選擇了【保護(hù)工作表及鎖定的單元格內(nèi)容】復(fù)選框，如圖9-48所示。此時(shí)，可以選擇輸入密碼。如果不使用密碼，則任何人可以通過單擊【審閱】選項(xiàng)卡上的【撤消工作表保護(hù)】來撤消對(duì)工作表的保護(hù)。如果創(chuàng)建了密碼，則其他用戶想編輯公式時(shí)，會(huì)要求輸入該密碼。