辦公自動(dòng)化高級(jí)應(yīng)用案例教程（Office 2016）（第2版） 課件 項(xiàng)目7-9 利用開發(fā)工具定制員工入職表、員工信息表制作與統(tǒng)計(jì)、產(chǎn)品銷售統(tǒng)計(jì)分析

演講人利用開發(fā)工具定制員工入職表01利用開發(fā)工具定制員工入職表內(nèi)容導(dǎo)圖02項(xiàng)目背景項(xiàng)目背景小王在幫助人力資源經(jīng)理做公司職員信息登記的時(shí)候，發(fā)現(xiàn)許多人填寫信息時(shí)，格式不規(guī)范，比如出生日期、照片大小、學(xué)歷等不規(guī)范，導(dǎo)致在統(tǒng)計(jì)數(shù)據(jù)時(shí)，出現(xiàn)一些問題，整個(gè)表格格式也不美觀。小王是個(gè)愛動(dòng)腦筋的同學(xué)，希望能制作一個(gè)表格，讓同事填寫表格內(nèi)容時(shí)，減少手工錄入的工作量，讓部分可以選擇的內(nèi)容，比如性別、學(xué)歷等選項(xiàng)較少的項(xiàng)目，可以直接從下拉列表中選擇，希望固定出生日期的格式，用戶只需要從列表中選擇時(shí)間即可，固定照片的大小等自動(dòng)化程度高的表格，經(jīng)多方學(xué)習(xí)，發(fā)現(xiàn)采用Word的【開發(fā)工具】，完全可以實(shí)現(xiàn)上述功能。Office套件中的開發(fā)工具，提供了大量控件，如：文本控件、圖片控件、復(fù)選框控件、組合框控件、下拉列表控件、日期選取器控件等，用戶可以對(duì)這些控件進(jìn)行簡單的設(shè)置，就可以完成以前需要寫程序才能完成的功能，提升用戶在填寫內(nèi)容時(shí)的體驗(yàn)。03項(xiàng)目簡介項(xiàng)目簡介本項(xiàng)目采用Word的【開發(fā)工具】，制作定制化的員工入職登記表，實(shí)現(xiàn)的要求如下：1（1）用戶只需選擇即可填入標(biāo)準(zhǔn)格式的出生日期；2（2）用戶直接從下拉列表中選擇性別、婚姻狀況、健康狀況、學(xué)歷等選項(xiàng)比較少的項(xiàng)目3（3）點(diǎn)擊按鈕上傳呢照片，照片的大小固定，同時(shí)根據(jù)照片的尺寸自動(dòng)調(diào)整大小，使得照片不變形；4（4）身份證欄不允許換行輸入5（5）家庭住址欄可以換行輸入6（6）直接選擇個(gè)人愛好，可以多選7項(xiàng)目簡介由于許多項(xiàng)目類似，本項(xiàng)目主要講解【開發(fā)工具】的主要應(yīng)用，并不在于制作一個(gè)完整的表格，讀者完全可以根據(jù)自己需要，靈活運(yùn)用上述各部分所用到的控件，設(shè)計(jì)完整的表格出來，提升用戶填寫表格的體驗(yàn)，提高管理水平。04顯示“開發(fā)工具”選項(xiàng)卡顯示“開發(fā)工具”選項(xiàng)卡默認(rèn)情況下，Word不顯示“開發(fā)工具”選項(xiàng)卡。要實(shí)現(xiàn)本章所要求的各項(xiàng)功能，需要首先打開“開發(fā)工具”選項(xiàng)卡。打開文檔，點(diǎn)擊最左上角的【文件】選項(xiàng)卡，選擇【選項(xiàng)】，彈出【W(wǎng)ord選項(xiàng)】對(duì)話框，在左側(cè)選擇【自定義工具欄】，在【主選項(xiàng)卡】列表中，將【開發(fā)工具】前的復(fù)選框勾選，點(diǎn)擊【確定】按顯示“開發(fā)工具”選項(xiàng)卡設(shè)置完畢后，在Word的選項(xiàng)卡上，將出現(xiàn)“開發(fā)工具”選項(xiàng)卡，表示設(shè)置成功05點(diǎn)擊選擇出生日期點(diǎn)擊選擇出生日期如果讓員工自己輸入出生日期，有時(shí)候格式會(huì)不一樣，回來再自己改就很麻煩，我們可以設(shè)置為直接讓員工自己選擇日期，并且指定格式，選擇完畢后，會(huì)自動(dòng)出現(xiàn)指定的日期格式，既方便員工，又規(guī)范了格式。采用【開發(fā)工具】中的“日期選取器內(nèi)容”控件來完成該功能，具體步驟如下：步驟1：定位到需要填寫出生日期的單元格，點(diǎn)擊【開發(fā)工具】選項(xiàng)卡，在【控件】功能組中，選擇【日期選取器內(nèi)容控件】命令【小貼士】當(dāng)鼠標(biāo)放置于控件上方時(shí)，會(huì)自動(dòng)出現(xiàn)該控件的功能描述提示，可根據(jù)提示選擇。點(diǎn)擊選擇出生日期步驟2：設(shè)置日期控件格式。選取日期控件，修改控件提示內(nèi)容，點(diǎn)擊【開發(fā)工具】下【控件】功能組的【屬性】按鈕，彈出【內(nèi)容控件屬性】對(duì)話框點(diǎn)擊選擇出生日期步驟3：設(shè)置控件屬性。在【內(nèi)容控件屬性】的標(biāo)題欄輸入提示信息，如“選擇出生日期”，再選擇適當(dāng)?shù)娜掌陲@示方式，點(diǎn)擊【確定】按鈕點(diǎn)擊選擇出生日期【小貼士】1、由于出生日期一般比較靠前，用戶可以在選定日期后，手工修改其中的部分?jǐn)?shù)字即可。2、對(duì)控件內(nèi)文字，可以自行修改，也可以如普通文本一樣，設(shè)置顏色、字體等。3、如果希望用戶在輸入內(nèi)容后不再出現(xiàn)控件的提示框，可以在【內(nèi)容控件屬性】對(duì)話框中勾選【內(nèi)容被編輯后刪除內(nèi)容】復(fù)選框。06下拉列表選擇性別下拉列表選擇性別性別、學(xué)歷、婚姻狀況等選項(xiàng)很少，并且選項(xiàng)內(nèi)容比較固定，為減少用戶輸入，可以采用下拉列表的方式，用戶直接選擇即可?？梢酝ㄟ^【開發(fā)工具】的【下拉列表內(nèi)容控件】實(shí)現(xiàn)，具體步驟如下。步驟1：定位到需要填寫性別的單元格，點(diǎn)擊【開發(fā)工具】選項(xiàng)卡，在【控件】功能組中，選擇【下拉列表內(nèi)容控件】命令下拉列表選擇性別步驟2：設(shè)置控件屬性。選取下拉列表控件，修改控件提示內(nèi)容。點(diǎn)擊【開發(fā)工具】下【控件】功能組的【屬性】按鈕，彈出【內(nèi)容控件屬性】對(duì)話框。在【標(biāo)題】處輸入提示信息“選擇性別”，為簡化用戶選擇，可以將原有選項(xiàng)刪除，選擇列表項(xiàng)，點(diǎn)擊【刪除】按鈕，即可將選項(xiàng)刪除。點(diǎn)擊【添加】按鈕，為列表添加選項(xiàng)。下拉列表選擇性別步驟3：添加選項(xiàng)。在彈出的【添加選項(xiàng)】對(duì)話框中，填入列表項(xiàng)，如“男”，填寫完畢，點(diǎn)擊【確定】按鈕，即將選項(xiàng)“男”添加到列表框中。單擊【添加】按鈕，添加其他選項(xiàng)。下拉列表選擇性別圖7-SEQ圖7-\*ARABIC10添加選項(xiàng)步驟4：修改列表選項(xiàng)。選中控件，點(diǎn)擊【開發(fā)工具】下的【屬性】按鈕，在下拉列表屬性表中，選中列表項(xiàng)，可以刪除、修改、上移和下移等，調(diào)整列表的位置。07點(diǎn)擊上傳照片點(diǎn)擊上傳照片在照片單元格，一般用插入圖片的方式來處理，插入圖片后需要設(shè)置圖片的大小，可能會(huì)讓圖片變形。為解決這個(gè)問題，采用“圖片內(nèi)容控件”可以實(shí)現(xiàn)點(diǎn)擊控件，上傳圖片，并自動(dòng)按圖片的比例設(shè)置圖片的大小，非常方便。具體步驟如下。步驟1：進(jìn)入需要插入圖片的單元格，點(diǎn)擊【開發(fā)工具】選項(xiàng)卡，在【控件】功能組中，選擇【圖片內(nèi)容控件】命令點(diǎn)擊上傳照片步驟2：設(shè)置圖片內(nèi)容控件屬性。選中控件，拖動(dòng)鼠標(biāo)，調(diào)整圖片的大小。點(diǎn)擊【開發(fā)工具】下的【屬性】按鈕，設(shè)置控件屬性。為避免用戶刪除或者更改圖片大小，可以選擇【鎖定】下的“無法刪除內(nèi)容控件”和【無法編輯內(nèi)容】復(fù)選框，表示不允許刪除和更改。點(diǎn)擊上傳照片設(shè)置圖片控件屬性08設(shè)置固定格式的單行文本內(nèi)容設(shè)置固定格式的單行文本內(nèi)容在錄入數(shù)據(jù)的時(shí)候，有些數(shù)據(jù)要求只能在一行內(nèi)輸入，不能跨行，比如銀行賬戶、身份證號(hào)碼等，以避免出現(xiàn)不必要的錯(cuò)誤，當(dāng)要求輸入的內(nèi)容具備特定的格式，可以通過添加“格式文本內(nèi)容控件”來實(shí)現(xiàn)。具體步驟如下。步驟1：定位到需要特定格式單行文本的單元格，點(diǎn)擊【開發(fā)工具】選項(xiàng)卡，在【控件】功能組中，選擇【格式文本內(nèi)容控件】命令設(shè)置固定格式的單行文本內(nèi)容步驟2：設(shè)置控件屬性。選中剛添加的控件，點(diǎn)擊【開發(fā)工具】選項(xiàng)卡下的【屬性】按鈕，進(jìn)入【內(nèi)容控件屬性】對(duì)話框，設(shè)置提示語，選擇【使用樣式設(shè)置鍵入空控件中的文本格式】復(fù)選框，直接選擇【樣式】列表中的樣式，或者點(diǎn)擊【新建樣式】按鈕，創(chuàng)建文本的新樣式，點(diǎn)擊【確定】按鈕。由于【格式文本內(nèi)容控件】中的內(nèi)容不能換行，只能在一行內(nèi)輸入，實(shí)現(xiàn)了在單行內(nèi)輸入固定格式文本內(nèi)容的要求。設(shè)置固定格式的單行文本內(nèi)容格式化文本控件屬性設(shè)置09設(shè)置固定格式的多行文本內(nèi)容設(shè)置固定格式的多行文本內(nèi)容在實(shí)際應(yīng)用中，有些內(nèi)容要求必須按行輸入不同的內(nèi)容，如獎(jiǎng)懲情況、學(xué)習(xí)情況等，需要在輸入前給用戶相應(yīng)的提示，并按固定的文本格式顯示出來。這可以通過添加【純文本內(nèi)容控件】來實(shí)現(xiàn)，具體步驟如下。步驟1：定位到需要特定格式文本的單元格，點(diǎn)擊【開發(fā)工具】選項(xiàng)卡，在【控件】功能組中，選擇【純文本內(nèi)容控件】命令設(shè)置固定格式的多行文本內(nèi)容步驟2：設(shè)置控件屬性。選中控件，點(diǎn)擊【開發(fā)工具】選項(xiàng)卡下【屬性】按鈕，在彈出的【內(nèi)容控件屬性】對(duì)話框中，輸入標(biāo)題，如需要特定的格式，勾選【使用樣式設(shè)置鍵入空控件中的文本格式】復(fù)選框，可從樣式表中選擇適當(dāng)?shù)臉邮?，或者單擊【新建樣式】?chuàng)建新的文本樣式，如要求用戶一定要按多行輸入，則必須勾選最下面的【允許回車（多個(gè)段落）】復(fù)選框。設(shè)置完畢后點(diǎn)擊【確定】按鈕，完成控件屬性設(shè)置。設(shè)置固定格式的多行文本內(nèi)容多行格式文本控件屬性設(shè)置10設(shè)置多選選項(xiàng)設(shè)置多選選項(xiàng)在實(shí)際應(yīng)用中，用戶根據(jù)情況可以選擇多個(gè)選項(xiàng)，比如選擇特長、愛好等，可以通過【復(fù)選框內(nèi)容控件】來完成多選內(nèi)容的設(shè)置，具體步驟如下。步驟1：定位到需要用戶多選的單元格，首先輸入要多選的各項(xiàng)內(nèi)容，然后鼠標(biāo)定位到某一選項(xiàng)前，點(diǎn)擊【開發(fā)工具】選項(xiàng)卡，在【控件】功能組中，選擇【復(fù)選框內(nèi)容控件】命令設(shè)置多選選項(xiàng)步驟2：設(shè)置控件屬性。選中復(fù)選框控件，點(diǎn)擊【開發(fā)工具】選項(xiàng)卡下【屬性】按鈕，在彈出的【內(nèi)容控件屬性】對(duì)話框中，輸入提示標(biāo)題。如果需要設(shè)置復(fù)選框的選中標(biāo)記為“(”符號(hào)，而不用默認(rèn)的“(”，可點(diǎn)擊【復(fù)選框?qū)傩浴肯碌摹靖摹堪粹o，選擇適當(dāng)?shù)臉?biāo)記符號(hào)。如果不用更改選中標(biāo)記，點(diǎn)擊【確定】按鈕即可設(shè)置多選選項(xiàng)步驟3：更改選中標(biāo)記。點(diǎn)擊【更改】按鈕，彈出【符號(hào)】對(duì)話框，在【字體】列表框中選擇“Wingding2”，選中“(”，點(diǎn)擊【確定】按鈕，回到【內(nèi)容控件屬性】對(duì)話框，點(diǎn)擊【確定】按鈕，完成對(duì)第一個(gè)多選向的設(shè)置。11設(shè)置單選選項(xiàng)設(shè)置單選選項(xiàng)在實(shí)際應(yīng)用中，除了有多選選項(xiàng)之外，還有很多單選的選項(xiàng)，比如性別、職位等?？梢酝ㄟ^設(shè)置單選按鈕的方式來實(shí)現(xiàn)。Word2016沒有自身的單選按鈕，但我們可以采用插入“ActiveX控件”的方式來實(shí)現(xiàn)單選按鈕的設(shè)定。具體步驟如下。步驟1：定位到需要用戶單選的單元格，點(diǎn)擊【開發(fā)工具】選項(xiàng)卡，在【控件】功能組中，選擇【舊式工具】按鈕，在【ActiveX控件】組中，選擇【選項(xiàng)按鈕（ActiveX控件）】命令，則在當(dāng)前位置插入了一個(gè)單選選項(xiàng)按鈕設(shè)置單選選項(xiàng)圖7-SEQ圖7-\*ARABIC21選擇單選按鈕步驟2：選中剛插入的單項(xiàng)選項(xiàng)按鈕，確保【開發(fā)工具】選項(xiàng)卡下的【設(shè)置模式】處于灰色狀態(tài)，表示當(dāng)前的單選選項(xiàng)按鈕處于可以修改的模式，再點(diǎn)擊【屬性】按鈕，彈出單選按鈕的屬性設(shè)置窗口。設(shè)置單選選項(xiàng)步驟3：設(shè)置單選按鈕屬性。在彈出的【屬性】窗口中，將鼠標(biāo)置于屬性【AutoSize】后，點(diǎn)擊符號(hào)，選擇“True”，表示控件的大小隨內(nèi)容的多少自動(dòng)調(diào)整。在屬性【Caption】（標(biāo)題）后，將原有的“OptionButton1”改為我們需要設(shè)置成的選項(xiàng)，在此處修改為“三級(jí)”，關(guān)閉此屬性框。將鼠標(biāo)放置于屬性【Font】之后，點(diǎn)擊按鈕，選擇恰當(dāng)?shù)淖煮w、字形和大小，此處設(shè)置為“宋體常規(guī)小四號(hào)字”設(shè)置單選選項(xiàng)步驟4：按上述相同的方法，添加其他選項(xiàng)，設(shè)置每個(gè)選項(xiàng)的屬性，完成所有單選內(nèi)容的設(shè)置。設(shè)置單選選項(xiàng)步驟5：關(guān)閉【設(shè)計(jì)模式】?，F(xiàn)在如果點(diǎn)擊每個(gè)選項(xiàng)，發(fā)現(xiàn)還不能選擇，原因是目前控件處于‘設(shè)計(jì)模式’，必須關(guān)閉“設(shè)計(jì)模式”才能實(shí)現(xiàn)單選。關(guān)閉的方法是：選擇任何一個(gè)單選項(xiàng)目，進(jìn)入【開發(fā)工具】選項(xiàng)卡，點(diǎn)擊【設(shè)計(jì)模式】，使之由灰色變?yōu)榘咨赐顺隽司庉嬆Ｊ?，現(xiàn)在就可以實(shí)現(xiàn)單擊選擇項(xiàng)目了。12拓展訓(xùn)練-設(shè)計(jì)調(diào)查表拓展訓(xùn)練-設(shè)計(jì)調(diào)查表按照以上所學(xué)內(nèi)容，參照右圖，創(chuàng)建調(diào)查問卷謝謝項(xiàng)目1員工信息表制作與統(tǒng)計(jì)演講人01員工信息表制作與統(tǒng)計(jì)內(nèi)容導(dǎo)圖02項(xiàng)目背景項(xiàng)目背景小王在收集到員工的登記信息之后，希望通過Excel表格來統(tǒng)計(jì)分析員工的各項(xiàng)指標(biāo)數(shù)據(jù)，如部門、學(xué)歷、年齡等分布情況，并希望在員工生日快到的時(shí)候能夠自動(dòng)提醒，為保證輸入的準(zhǔn)確性，需要對(duì)表格中的部分?jǐn)?shù)據(jù)進(jìn)行驗(yàn)證，當(dāng)輸入錯(cuò)誤的時(shí)候，能自動(dòng)提醒，為減少錄入的數(shù)據(jù)量，當(dāng)輸入部分?jǐn)?shù)據(jù)時(shí)，與之相關(guān)聯(lián)的數(shù)據(jù)能夠自動(dòng)產(chǎn)生。Word強(qiáng)大之處在于文檔的排版，對(duì)于涉及大量數(shù)據(jù)計(jì)算，我們建議讀者采用Excel來完成，借助功能強(qiáng)大的公式，既能大幅提高工作效率，又保證數(shù)據(jù)的準(zhǔn)確性，可以將分析統(tǒng)計(jì)結(jié)果復(fù)制到Word文檔中，提交完整的分析報(bào)告。03項(xiàng)目簡介項(xiàng)目簡介本項(xiàng)目利用Excel2016，實(shí)現(xiàn)有規(guī)律數(shù)據(jù)的批量填充、重復(fù)性檢驗(yàn)、將姓名按筆畫排序、從下拉列表中選擇數(shù)據(jù)，減少輸入工作量、驗(yàn)證數(shù)據(jù)的有效性、自動(dòng)從身份證中提取出生的年月日、在任何時(shí)間打開，都能根據(jù)當(dāng)前時(shí)間自動(dòng)計(jì)算員工的年齡、在員工生日前的10天之內(nèi)，在表格中自動(dòng)顯示提醒、在選擇了學(xué)歷之后，自動(dòng)根據(jù)學(xué)歷生成學(xué)位、為針對(duì)不同的人設(shè)置不同的表格視圖，只展示與其相關(guān)的數(shù)據(jù)、凍結(jié)窗格、設(shè)置表格樣式、自定義表格樣式以及單條件、多條件計(jì)數(shù)等功能。04批量填充序列號(hào)批量填充序列號(hào)表格中的序號(hào)，表示該行在表格中的所處行的位置，一般每往下一行就增加1，對(duì)于這類有規(guī)律的序列（如等差或等比性質(zhì)的數(shù)據(jù)），可以采用序列填充的方式完成數(shù)據(jù)錄入?？焖偬畛涠喾N類型的數(shù)據(jù)序列，首先選擇要用作其他單元格填充基礎(chǔ)的單元格，然后拖動(dòng)填充手柄，將填充柄橫向或縱向拖過填充的單元格。步驟如下：批量填充序列號(hào)步驟1：選擇包含要填充到相鄰單元格的數(shù)據(jù)的單元格。步驟2：拖動(dòng)填充柄，使其經(jīng)過要填充的單元格，步驟3：要更改選定區(qū)域的填充方式，請(qǐng)單擊“自動(dòng)填充選項(xiàng)”，然后單擊所需的選項(xiàng)。批量填充序列號(hào)【小貼士】通過拖動(dòng)自動(dòng)填充手柄，既可以實(shí)現(xiàn)有規(guī)律數(shù)據(jù)的自動(dòng)填充，同時(shí)可以完成單元格公式、樣式的自動(dòng)更新。05拒絕錄入重復(fù)值拒絕錄入重復(fù)值在每個(gè)單位中，工號(hào)都是唯一的，在錄入數(shù)據(jù)時(shí)，是不允許重復(fù)的，可以采用“數(shù)據(jù)有效性”驗(yàn)證，來設(shè)置拒絕錄入重復(fù)的工號(hào)。具體步驟如下：步驟1：選中不允許重復(fù)的列，此處為B列步驟2：選擇【數(shù)據(jù)】菜單，點(diǎn)擊【數(shù)據(jù)驗(yàn)證】按鈕旁邊的符號(hào)，點(diǎn)擊【數(shù)據(jù)驗(yàn)證】命令，彈出【數(shù)據(jù)驗(yàn)證】對(duì)話框。拒絕錄入重復(fù)值步驟3：用公式設(shè)置不允許重復(fù)。在【允許】下拉列表中選擇【自定義】選項(xiàng)，在【公式】文本框中輸入“=COUNTIF(B:B,B1)=1”（不包含括號(hào)，在英文輸入狀態(tài)下輸入）拒絕錄入重復(fù)值【小貼士】輸入公式時(shí)，一定要在英文狀態(tài)輸入，包括括號(hào)、冒號(hào)和逗號(hào)，否則公式不能正確運(yùn)行。步驟4：設(shè)置輸入提示信息。選擇【輸入信息】選項(xiàng)卡，在【輸入信息】文本框內(nèi)輸入提示消息“不允許重復(fù)”拒絕錄入重復(fù)值步驟5：設(shè)置出錯(cuò)警告提示信息。選擇【出錯(cuò)警告】選項(xiàng)卡，在【樣式】下拉列表中選擇【警告】提示類型，在【標(biāo)題】和【錯(cuò)誤消息】文本框中分別輸入提示信息，點(diǎn)擊確定。拒絕錄入重復(fù)值圖8-SEQ圖8-\*ARABIC5設(shè)置出錯(cuò)警告信息按上述步驟設(shè)置完畢后，當(dāng)在工號(hào)列（即本例的B列）輸入相同工號(hào)的時(shí)候，將會(huì)顯示如下的提示消息，提示輸入有誤，點(diǎn)擊【否】按鈕，關(guān)閉提示信息框，輸入正確的數(shù)據(jù)06姓名按筆畫排序姓名按筆畫排序在Excel中，除了可以將數(shù)字?jǐn)?shù)據(jù)按升序或降序排序之外，還可以對(duì)文本數(shù)據(jù)按照一定的規(guī)律排序，本例將完成對(duì)姓名按筆畫數(shù)排序。選擇需要排序的數(shù)據(jù)，點(diǎn)擊【數(shù)據(jù)】菜單，選擇【排序】按鈕，在彈出的對(duì)話框中選擇【擴(kuò)展選定區(qū)域】選項(xiàng)，在【排序】對(duì)話框中，將【主要關(guān)鍵詞】設(shè)置為【姓名】，【次序】列表中選擇【升序】，點(diǎn)擊【選項(xiàng)】按鈕，在【排序選項(xiàng)】對(duì)話框的【方法】組中，選擇【筆畫順序】選項(xiàng)，最后確定后，即完成了對(duì)姓名按筆畫排序。姓名按筆畫排序【小貼士】從【排序選項(xiàng)】對(duì)話框中，我們看到，還可以按字母排序，可以區(qū)分大小寫，另外，還可以通過點(diǎn)擊【添加條件】增加更多的排序條件，多條件排序是在先按第一個(gè)條件排序后，再在排序后的結(jié)果中再按第二個(gè)條件來排序，依次類推，可以實(shí)現(xiàn)更為精確的排序方式，讀者可自行嘗試。07下拉列表選擇性別下拉列表選擇性別在諸如性別、學(xué)歷等選項(xiàng)，有固定的范圍，并且選項(xiàng)不多，如性別只有“男”和“女”，學(xué)歷只有“研究生”、“本科”、“大專”和“高中”等，為避免輸入格式不規(guī)范，減少輸入工作量，可以采用下拉列表的方式讓用戶選擇，提升用戶體驗(yàn)。具體步驟如下：步驟1：選擇需要提供下拉列表選擇的單元格，此處選擇“性別”列小的單元格，點(diǎn)擊【數(shù)據(jù)】選項(xiàng)卡，選擇【數(shù)據(jù)驗(yàn)證】按鈕，彈出【數(shù)據(jù)驗(yàn)證】對(duì)話框。步驟2：在【允許】下拉列表中選擇【序列】，確保選中【提供下拉箭頭】復(fù)選框，在【來源】文本框中輸入“男,女”，注意一定要確保男和女中間是用英文的逗號(hào)分隔，點(diǎn)擊【確定】按鈕，即完成了下拉列表的數(shù)據(jù)項(xiàng)的設(shè)置。下拉列表選擇性別用同樣的方式，完成“學(xué)歷”欄的填寫下拉列表選擇性別設(shè)置學(xué)歷下拉列表08輸入并驗(yàn)證身份證號(hào)位數(shù)輸入并驗(yàn)證身份證號(hào)位數(shù)身份證位數(shù)，只能為15位或者18位，為了確保身份證輸入位數(shù)正確，可以通過【數(shù)據(jù)驗(yàn)證】來實(shí)現(xiàn)自動(dòng)驗(yàn)證身份證號(hào)碼的位數(shù)。具體方法如下。步驟1：定位到需要設(shè)置身份證長度的單元格，此處為“F3”單元格，點(diǎn)擊【數(shù)據(jù)】菜單，選擇【數(shù)據(jù)驗(yàn)證】按鈕，彈出【數(shù)據(jù)驗(yàn)證】對(duì)話框。步驟2：設(shè)置驗(yàn)證條件。在【允許】下拉列表中選擇【自定義】，在【公式】文本框中輸入“=OR(LEN(F3)=15,LEN(F3)=18”（不包含引號(hào)），特別需要注意的是，所有的括號(hào)和逗號(hào)，都需要在英文輸入狀態(tài)下輸入，步驟4：設(shè)置相應(yīng)的輸入信息和出錯(cuò)警告信息，最后點(diǎn)擊【確定】按鈕，完成對(duì)身份證輸入長度的驗(yàn)證和錯(cuò)誤提示。輸入并驗(yàn)證身份證號(hào)位數(shù)設(shè)置身份證輸入長度【小貼士】（1）為了讓輸入的身份證號(hào)碼不顯示為數(shù)字，需要先輸入符號(hào)“'”，即英文狀態(tài)下的單引號(hào)，表示將輸入的內(nèi)容以文本形式顯示。如輸入“。（2）LEN函數(shù)的作用是取得當(dāng)前單元格內(nèi)容字符串的長度，如LEN(F3)作用為取得F3單元格字符長度，OR函數(shù)為邏輯函數(shù)，用于測(cè)試條件中是否為TRUE，“=OR(LEN(F3)=15,LEN(F3)=18”公式的作用為驗(yàn)證F3字符的是否為15或18，若都不成立，表示數(shù)據(jù)輸入錯(cuò)誤。09從身份證號(hào)提取出生年月日從身份證號(hào)提取出生年月日從文本中提取部分字符，需要用Excel中的LEFT()，RIGHT(),MID()等函數(shù)。下面以提取身份證號(hào)碼中的年月日為例說明如何用字符截取函數(shù)，來提取年、月、日的方法。MID(text,start_num,num_chars)：從字符串中提取子串，text必需，包含要提取字符的文本字符串，start_num必需，文本中要提取的第一個(gè)字符的位置，文本中第一個(gè)字符的start_num為1，以此類推，num_chars必需，指定希望MID從文本中返回字符的個(gè)數(shù)。LEFT(text,[num_chars])：從字符串左側(cè)開始提取子串。Text必需，包含要提取的字符的文本字符串，num_chars，可選，指定要由LEFT提取的字符的數(shù)量。Num_chars必須大于或等于零，如果num_chars大于文本長度，則LEFT返回全部文本。從身份證號(hào)提取出生年月日RIGHT(text,[num_chars])：從字符串右側(cè)開始提取子串。text必需，包含要提取字符的文本字符串，num_chars可選，指定希望RIGHT提取的字符數(shù)，Num_chars必須大于或等于零，如果num_chars大于文本長度，則RIGHT返回所有文本。在身份證號(hào)碼中，從第7位開始的后4位為出生日期中的年份，后2位為月份，再后兩位為出生的日期，可以采用MID函數(shù)來提取相應(yīng)數(shù)據(jù)。選中“出生年月”的下一個(gè)單元格，此處為G3，在單元格中輸入“=MID(F3,7,4)&"/"&MID(F3,11,2)”。MID(F3,7,4)表示從單元格F3的字符中的第7個(gè)字符開始，截取4個(gè)字符，得到出生的年，即“1979”，MID(F3,11,2)表示從F3單元格中的第11個(gè)字符開始，截取2個(gè)字符，得到出生的月份，即“05”，兩者用“&”號(hào)將年與“/”和月連接起來，即組成了“1979/05”效果。從身份證號(hào)提取出生年月日上述公式，僅能截取長度為18位的身份證號(hào)碼的年月數(shù)據(jù)，但是，由于身份證號(hào)碼有可能有15位數(shù)字，因此，為了實(shí)現(xiàn)根據(jù)身份證號(hào)碼不同長度，能自動(dòng)自動(dòng)實(shí)現(xiàn)年月的截取，需要將公式修改為：“=IF(LEN(F3)=18,MID(F3,7,4)&"/"&MID(F3,11,2),"19"&MID(F3,6,2)&"/"&MID(F3,8,2))”上述公式的含義是：如果單元格F3的長度為18，即LEN(F3)=18條件滿足，如字符，執(zhí)行MID(F3,7,4)，將F3單元格字符串從第7個(gè)字符開始，截取4個(gè)字符，得到“1979”作為年份，拼接符號(hào)“/”，再截取后兩個(gè)字符，得到字符“05”作為月份，最后的結(jié)果就為“1979/05”從身份證號(hào)提取出生年月日如果LEN(F3)=18條件不滿足，如字符“510108890305405”，就由字符“19”拼接從F3單元格第6個(gè)字符開始的兩個(gè)字符，得到字符“89”，拼接后的字符為“1989”，拼接字符“/”，再截取后兩個(gè)字符，得到“03”作為月份，最后的結(jié)果就為“1989/03”，滿足了需要的格式。從身份證號(hào)提取出生年月日【小貼士】按上述方式提取出來的數(shù)據(jù)，都是文本格式，如果需要日期格式的數(shù)據(jù)，可以用Date()函數(shù)將文本格式數(shù)據(jù)轉(zhuǎn)化為日期格式，公式如下：=DATE(MID(F3,7,4),MID(F3,11,2),MID(F3,13,2))，此處僅以18位的身份證為例。10自動(dòng)計(jì)算年齡自動(dòng)計(jì)算年齡當(dāng)身份證號(hào)確定之后，出生日期等信息可以從其中獲得，就可以自動(dòng)計(jì)算年齡了。年齡的計(jì)算方式為當(dāng)前日期與身份證中的出生年份之差。計(jì)算時(shí)間差，需要用到Excel的DATEDIF()函數(shù)和TODAY()函數(shù)。TODAY()函數(shù)自動(dòng)獲得當(dāng)前的日期，當(dāng)每一次打開文件時(shí)，TODAY()函數(shù)的值會(huì)自動(dòng)改變。DATEDIF()函數(shù)的基本格式如下：DATEDIF(start_date,end_date,unit)，即DATEDIF(開始日期,結(jié)束日期,返回參數(shù))，返回參數(shù)有Y、M、D、YM、YD、MD六種，返回的內(nèi)容大致如下表所示：根據(jù)以上分析，通過如下公式來完成年齡的自動(dòng)計(jì)算：“=DATEDIF(H3,TODAY(),”Y”)”，開始的日期為H3中的數(shù)據(jù)，結(jié)束的時(shí)間為TODAY()函數(shù)，自動(dòng)取得的當(dāng)前日期，以“Y”代表獲取年份之差。如8-12所示。將I3單元格向下自動(dòng)填充，即可實(shí)現(xiàn)年齡的自動(dòng)計(jì)算。自動(dòng)計(jì)算年齡自動(dòng)計(jì)算年齡11員工生日自動(dòng)提醒員工生日自動(dòng)提醒假如公司在員工生日當(dāng)天送禮物，一般需要提前購買禮品，因此可以在員工信息表里能設(shè)置提醒，提示還有多少天就是員工生日了。假設(shè)從還有10天就開始提醒，過了生日之后，就自動(dòng)取消提醒。還是可以用DATEDIF函數(shù)拉實(shí)現(xiàn)，在生日中，必須包含月份和日期，否則就不能準(zhǔn)確到提前幾天的提醒功能了。如果直接用公式，DATEDIF(H3,TODAY(),"yd"）比如今天10月21日，員工出生日期是1979年10月23日，用上面這個(gè)公式返回結(jié)果是364天，假如需要提前10天提醒，需要設(shè)置為DATEDIF(H3-10,TODAY(),”yd”)來計(jì)算兩個(gè)的日期之差。員工生日自動(dòng)提醒公式“=TEXT(10-DATEDIF(H3-10，TODAY(),”yd”),”還有0天生日;;今天生日”)”表示的意思是：DATEDIF函數(shù)的計(jì)算結(jié)果大于0的，顯示為“還有N天生日”；小于0的不顯示；等于0的顯示為“今天生日”。TEXT函數(shù)的作用，是將結(jié)果以文本的形式顯示出來。12根據(jù)學(xué)歷自動(dòng)產(chǎn)生學(xué)位根據(jù)學(xué)歷自動(dòng)產(chǎn)生學(xué)位在現(xiàn)行的教育體系中，學(xué)歷一般有博士研究生、碩士研究生、本科、大專和高中等，對(duì)應(yīng)的學(xué)位分別為博士、碩士、學(xué)士，大專和高中沒有學(xué)位?，F(xiàn)要求根據(jù)選擇或輸入的學(xué)歷，自動(dòng)在學(xué)位欄輸出相應(yīng)的學(xué)位，如果學(xué)歷為大?；蚋咧校瑒t在學(xué)位欄輸出“無”。根據(jù)學(xué)歷自動(dòng)產(chǎn)生學(xué)位使用的公式如下：=IF(K3="博士研究生","博士",IF(K3="碩士研究生","碩士",IF(K3="本科","學(xué)士","無")))，所代表的意思為：如果K3的值為“博士研究生”，則當(dāng)前單元格的內(nèi)容輸出“博士”，否則繼續(xù)判斷K3單元格的內(nèi)容是否為“碩士研究生”，如果為是，設(shè)置當(dāng)前單元格內(nèi)容為“碩士”，否則繼續(xù)判斷K3單元格的內(nèi)容是否為“本科”，如果是，則當(dāng)前單元格的內(nèi)容填入“學(xué)士”，若果為K3單元格的內(nèi)容不為“本科”，則代表K3單元格的內(nèi)容既不為博士研究生、也不為碩士研究生和本科，那么當(dāng)前單元格的內(nèi)容輸出為“無”。【小貼士】IF函數(shù)是在Excel中最常用的函數(shù)之一，它允許對(duì)單元格數(shù)據(jù)進(jìn)行邏輯判斷，簡單的形式理解為：根據(jù)學(xué)歷自動(dòng)產(chǎn)生學(xué)位如果（內(nèi)容為True，則執(zhí)行某些操作，否則就執(zhí)行其他操作）因此IF語句有兩個(gè)結(jié)果。第一個(gè)結(jié)果是條件為True時(shí)的結(jié)果，如果條件比較為False，則執(zhí)行第二個(gè)操作。IF語句是可以嵌套的，如下面例子，可以將學(xué)生的考試成績轉(zhuǎn)化為相應(yīng)的等級(jí)：相應(yīng)的公式為：根據(jù)學(xué)歷自動(dòng)產(chǎn)生學(xué)位=IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))此復(fù)雜嵌套IF語句遵循一個(gè)簡單邏輯：1.如果TestScore（單元格D2）大于89，則學(xué)生獲得A2.如果TestScore大于79，則學(xué)生獲得B3.如果TestScore大于69，則學(xué)生獲得C4.如果TestScore大于59，則學(xué)生獲得D根據(jù)學(xué)歷自動(dòng)產(chǎn)生學(xué)位5.否則，學(xué)生獲得F我們發(fā)現(xiàn)，當(dāng)條件比較多的時(shí)候，用IF語句將形成多層的嵌套，語義比較復(fù)雜，這時(shí)可以使用IFS函數(shù)，簡化多條件判斷語句的書寫，并且更容易理解。在上述轉(zhuǎn)化等級(jí)的多條件判斷語句中，可以用如下形式來完成：=IFS(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",TRUE,"F")。所代表的意思就是：如果（D2大于89，則返回“A”，如果D2大于79，則返回“B”并以此類推，對(duì)于所有小于59的值，返回“F”）。13設(shè)置入職日期為當(dāng)前日期設(shè)置入職日期為當(dāng)前日期在Excel中，插入當(dāng)前日期的方式用快捷鍵【Ctrl】+【;】組合鍵，或者使用函數(shù)Today（）可以快速插入當(dāng)前的日期。14設(shè)置EXCEL不同顯示視圖設(shè)置EXCEL不同顯示視圖如果需要在Excel中反復(fù)多次按照不同條件進(jìn)行篩選、隱藏行列等操作，當(dāng)表格數(shù)據(jù)較多、自動(dòng)篩選條件比較復(fù)雜時(shí)，反復(fù)操作就變成很麻煩的一件事了，采用“自定義視圖”，可以對(duì)每次的篩選結(jié)果都保留下來，方便在后面任何時(shí)候可以方便的打開查看，并且可以將處理好了后的內(nèi)容打印出來，極大的方便針對(duì)不同的人或不同篩選后的打印管理。為生成沒有身份證號(hào)碼的員工信息信息表，在“員工信息表模板.xlsx”文件中，在“身份證號(hào)碼”列點(diǎn)擊右鍵，選擇【隱藏】命令，表示將該列隱藏起來，再選擇【視圖】菜單，點(diǎn)擊【自定義視圖】按鈕，彈出【視圖管理器】對(duì)話框，點(diǎn)擊【添加】按鈕，在彈出的對(duì)話框中輸入視圖的名稱，如“隱藏身份證號(hào)的員工信息”，點(diǎn)擊【確定】按鈕設(shè)置EXCEL不同顯示視圖視圖管理器設(shè)置EXCEL不同顯示視圖用同樣方法，建立其他的視圖（將身份證號(hào)碼列取消隱藏）。當(dāng)需要查看、打印不同信息時(shí)，選擇【視圖】->【自定義視圖】在彈出的【視圖管理器】對(duì)話框中，選擇不同的視圖名稱，點(diǎn)擊【顯示】按鈕，即可顯示預(yù)先定義好的內(nèi)容，并可快速打印出來。15凍結(jié)窗格凍結(jié)窗格若要使工作表的某一區(qū)域在滾動(dòng)到工作表的另一區(qū)域時(shí)仍保持可見，可采用“凍結(jié)窗格”的方法來完成。點(diǎn)擊【視圖】選項(xiàng)卡，在此選項(xiàng)卡中，選擇【凍結(jié)窗格】，將特定的行和列鎖定到位，也可以“拆分”窗格，創(chuàng)建同一工作表的單獨(dú)窗口。如果電子表格中的第一行包含標(biāo)題，可以凍結(jié)該行，確保在電子表格中向下滾動(dòng)時(shí)列標(biāo)題保持可見。首行被凍結(jié)后，下面的內(nèi)容滾動(dòng)，首行仍然保持不動(dòng)。凍結(jié)窗格在選擇凍結(jié)工作表中的行或列之前，以下問題需要注意：（1）只能凍結(jié)工作表中的頂行和左側(cè)的列，無法同時(shí)凍結(jié)工作表中間的行和列。（2）當(dāng)位于單元格編輯模式（即，你正在單元格中輸入公式或數(shù)據(jù)）或工作表受保護(hù)時(shí)，“凍結(jié)窗格”命令不可用。若要取消單元格編輯模式，請(qǐng)按Enter或Esc。（3）可以選擇只凍結(jié)工作表的頂行，或只凍結(jié)工作表的左側(cè)列，或同時(shí)凍結(jié)多個(gè)行和列。例如，如果凍結(jié)了行1，然后又決定凍結(jié)列A，則行1將無法再凍結(jié)。如果要凍結(jié)凍結(jié)窗格行和列，需要同時(shí)凍結(jié)它們。?若要僅鎖定一行，選擇“視圖”選項(xiàng)卡，然后單擊“凍結(jié)首行”。?若要僅鎖定一列，選擇“視圖”選項(xiàng)卡，然后單擊“凍結(jié)首列”。?若要鎖定多行或多列，或同時(shí)鎖定行和列，選擇“視圖”選項(xiàng)卡，然后單擊“凍結(jié)窗格”，然后將光標(biāo)置于要凍結(jié)的行下以及任何要凍結(jié)的列的右側(cè)。?若要鎖定多行（從第1行開始），選擇要凍結(jié)的最后一行下方的一行，選擇“視圖”選項(xiàng)卡，然后單擊“凍結(jié)窗格”。?若要鎖定多列，選擇要凍結(jié)的最后一列右側(cè)的一列，選擇“視圖”選項(xiàng)卡，然后單擊“凍結(jié)窗格”。16設(shè)置跨列居中設(shè)置跨列居中一般情況下，表格的第一行，總是表格的標(biāo)題，標(biāo)題一般需要設(shè)置跨列居中對(duì)齊，可以通過“合并居中”的來實(shí)現(xiàn)，首先選中需要合并的單元格，點(diǎn)擊【開始】選項(xiàng)卡，選擇【合并后居中】按鈕，即可完成對(duì)選定單元格的合并，并讓單元格內(nèi)容居中對(duì)齊。17設(shè)置表格樣式設(shè)置表格樣式Excel提供了可用于快速設(shè)置表格格式的許多預(yù)定義的表格樣式。如果預(yù)定義的表格樣式不能滿足需要，可以創(chuàng)建并應(yīng)用自定義表格樣式。可以進(jìn)一步調(diào)整表格的格式選擇快速樣式表的元素，如標(biāo)題和匯總行、第一個(gè)和最后一列、鑲邊行和列，以及自動(dòng)篩選選項(xiàng)等。套用表格格式3.單擊要使用的表格樣式，即可快速應(yīng)用格式。18創(chuàng)建自定義表格樣式創(chuàng)建自定義表格樣式選擇要用于創(chuàng)建自定義樣式表中的任意單元格，在【開始】選項(xiàng)卡上，單擊【套用表格格式】，點(diǎn)擊【新建表格樣式】，這將啟動(dòng)新的表格樣式對(duì)話框。在【名稱】框中，鍵入新的表格樣式的名稱。在表元素框中，可以執(zhí)行如下的操作︰創(chuàng)建自定義表格樣式1（1）選擇要設(shè)置格式的元素，然后單擊【格式】按鈕，然后從字體、邊框或填充選項(xiàng)卡中選擇所需的格式設(shè)置選項(xiàng)；2（2）若要?jiǎng)h除現(xiàn)有元素的格式，單擊相應(yīng)元素，然后單擊清除。3（3）在預(yù)覽中，可以看到所做的更改格式對(duì)表的影響。4（4）若要用作當(dāng)前工作簿中的默認(rèn)表格樣式的新表樣式，選擇設(shè)置為此文檔的默認(rèn)表格樣式復(fù)選框。19刪除自定義表格樣式刪除自定義表格樣式01.選擇要從中刪除自定義表格樣式表中的任意單元格。02.在開始選項(xiàng)卡上，單擊【套用表格格式】。03.在【自定義】組中，右鍵單擊要?jiǎng)h除的表格樣式，然后單擊快捷菜單上的刪除。20單條件計(jì)數(shù)單條件計(jì)數(shù)使用CountIF函數(shù)，可以統(tǒng)計(jì)某個(gè)區(qū)域內(nèi)滿足某個(gè)條件的單元格數(shù)量，如本例要統(tǒng)計(jì)每個(gè)部門的人數(shù)。步驟如下：步驟1：輸入部門數(shù)據(jù)，按列錄入；步驟2：在“研發(fā)部”后面的單元格，輸入公式“=COUNTIF($L$3:$L$12,P2)”，表示將在絕對(duì)定位為L3到L12的范圍內(nèi)，尋找P2單元格中內(nèi)容出現(xiàn)的次數(shù)。錄入完畢回車即輸出研發(fā)部出現(xiàn)的次數(shù)。單條件計(jì)數(shù)步驟3：在Q2單元格，拖動(dòng)鼠標(biāo)，向下填充。進(jìn)入售后支持部后的單元格內(nèi)，檢查公式，發(fā)現(xiàn)“$L$3:$L$12”并沒有隨著拖動(dòng)而自動(dòng)變化，而P5由于拖動(dòng)，已經(jīng)自動(dòng)發(fā)生了變化。單條件計(jì)數(shù)圖8-SEQ圖8-\*ARABIC24分析絕對(duì)引用與相對(duì)引用【小貼士】絕對(duì)引用、相對(duì)引用和混合引用之間的區(qū)別相對(duì)引用公式中的相對(duì)單元格引用（如A1）是基于包含公式和單元格引用的單元格的相對(duì)位置。如果公式所在單元格的位置改變，引用也隨之改變。如果多行或多列地復(fù)制或填充公式，引用會(huì)自動(dòng)調(diào)整。默認(rèn)情況下，新公式使用相對(duì)引用。例如，如果將單元格B2中的相對(duì)引用復(fù)制或填充到單元格B3，將自動(dòng)從=A1調(diào)整到=A2。復(fù)制的公式具有相對(duì)引用絕對(duì)引用公式中的絕對(duì)單元格引用（如$A$1）總是在特定位置引用單元格。如果公式所在單元格的位置改變，絕對(duì)引用將保持不變。如果多行或多列地復(fù)制或填充公式，絕對(duì)引用將不作調(diào)整。默認(rèn)情況下，新公式使用相對(duì)引用，因此您可能需要將它們轉(zhuǎn)換為絕對(duì)引用。例如，如果將單元格B2中的絕對(duì)引用復(fù)制或填充到單元格B3，則該絕對(duì)引用在兩個(gè)單元格中一樣，都是=$A$1。復(fù)制的公式具有絕對(duì)引用復(fù)制的公式具有相對(duì)引用混合引用混合引用具有絕對(duì)列和相對(duì)行或絕對(duì)行和相對(duì)列。絕對(duì)引用列采用$A1、$B1等形式。絕對(duì)引用行采用A$1、B$1等形式。如果公式所在單元格的位置改變，則相對(duì)引用將改變，而絕對(duì)引用將不變。如果多行或多列地復(fù)制或填充公式，相對(duì)引用將自動(dòng)調(diào)整，而絕對(duì)引用將不作調(diào)整。例如，如果將一個(gè)混合引用從單元格A2復(fù)制到B3，它將從=A$1調(diào)整到=B$1。復(fù)制的公式具有混合引用復(fù)制的公式具有相對(duì)引用21多條件計(jì)數(shù)多條件計(jì)數(shù)在對(duì)部門員工進(jìn)行統(tǒng)計(jì)的時(shí)候，條件只有一個(gè)，就是“部門名稱”，現(xiàn)要統(tǒng)計(jì)每個(gè)部門中年齡在30歲以上的人數(shù)，涉及到兩個(gè)條件，第一個(gè)條件是“部門名稱”，第二個(gè)條件是“年齡大于30歲”，多條件計(jì)數(shù)，可以使用多條件統(tǒng)計(jì)函數(shù)COUNTIFS，詳細(xì)步驟如下：步驟1：在單元格中輸入要統(tǒng)計(jì)的條件多條件計(jì)數(shù)步驟2：在R3單元格中輸入公式:“=COUNTIFS(L$3:L$12,P3,H$3:H$12,Q3)”其中“L$3:L$12”采用了混合引用單元格的方法，表示L列固定從第3行到第12行的位置，當(dāng)在R3單元格往下自動(dòng)填充的時(shí)候，數(shù)據(jù)的引用范圍不隨填充的變化而變化。多條件計(jì)數(shù)【小貼士】COUNTIFS函數(shù)將條件應(yīng)用于跨多個(gè)區(qū)域的單元格，然后統(tǒng)計(jì)滿足所有條件的次數(shù)，其基本語法為：COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],…)，COUNTIFS函數(shù)語法具有以下參數(shù)：criteria_range1必需。在其中計(jì)算關(guān)聯(lián)條件的第一個(gè)區(qū)域。criteria1必需。條件的形式為數(shù)字、表達(dá)式、單元格引用或文本，它定義了要計(jì)數(shù)的單元格范圍。criteria_range2,criteria2,...可選。附加的區(qū)域及其關(guān)聯(lián)條件。最多允許127個(gè)區(qū)域/條件對(duì)。21同一張表內(nèi)核對(duì)數(shù)據(jù)同一張表內(nèi)核對(duì)數(shù)據(jù)現(xiàn)要對(duì)同一商品的進(jìn)貨數(shù)，清點(diǎn)數(shù)量1和清點(diǎn)數(shù)量2，也就是B、C、D列進(jìn)行快速核對(duì)，步驟1：選中B2:D15單元格步驟2：依次點(diǎn)擊【開始】-編輯組中的【查找和選擇】-【定位條件】同一張表內(nèi)核對(duì)數(shù)據(jù)步驟3：在“定位條件”選項(xiàng)卡中，選擇【行內(nèi)容差異單元格】，確定后，將在表格中選擇同一行中有差異的內(nèi)容。將這部分單元格設(shè)置不同填充色，可快速識(shí)別差異單元格同一張表內(nèi)核對(duì)數(shù)據(jù)標(biāo)注差異數(shù)據(jù)單元格【小貼士】（1）選中B2:D15單元格，可按F5鍵或Ctrl+G組合鍵，可快速彈出【定位】對(duì)話框，點(diǎn)擊【定位條件】按鈕后參照第三步操作。（2）核對(duì)數(shù)據(jù)時(shí)，以已選區(qū)域的第一列為基準(zhǔn)，將其后的數(shù)據(jù)與第一例做對(duì)比，將后所有列不同的數(shù)據(jù)標(biāo)識(shí)出來（參見第6行數(shù)據(jù)），此時(shí)應(yīng)設(shè)置差異單元格字體或填充色，否則鼠標(biāo)點(diǎn)擊其他地方后，標(biāo)識(shí)的單元格將自動(dòng)消失。02核對(duì)行列順序相同的兩張表數(shù)據(jù)核對(duì)行列順序相同的兩張表數(shù)據(jù)財(cái)務(wù)部計(jì)算了上半年員工應(yīng)發(fā)的銷售提成數(shù)據(jù)，公司ERP系統(tǒng)自動(dòng)計(jì)算提成表，兩張表結(jié)構(gòu)相同，員工的排列順序相同（若排列順序不同，可對(duì)行進(jìn)行排序）現(xiàn)要對(duì)這兩個(gè)表的數(shù)據(jù)進(jìn)行核對(duì)，對(duì)有誤的數(shù)據(jù)進(jìn)行再次核實(shí)。方法如下：核對(duì)行列順序相同的兩張表數(shù)據(jù)步驟1：選中任意一個(gè)表格需要核對(duì)的數(shù)據(jù)，復(fù)制，切換到另一個(gè)表，選擇數(shù)據(jù)核對(duì)區(qū)域，點(diǎn)擊【開始】-【粘貼】的倒三角形符號(hào)-【選擇性粘貼】步驟2：在“選擇性粘貼”對(duì)話框中，選擇“運(yùn)算”組中的【減】，點(diǎn)擊確定核對(duì)行列順序相同的兩張表數(shù)據(jù)步驟3：在結(jié)果區(qū)域顯示當(dāng)前表格與前表格的數(shù)據(jù)差異，為負(fù)數(shù)，表示當(dāng)前表格比前一個(gè)表格少，為正數(shù)，表示當(dāng)前表格數(shù)據(jù)比前一個(gè)表格多，仔細(xì)核對(duì)數(shù)據(jù)，如REF_Ref32218643\h圖8-34所示。核對(duì)行列順序相同的兩張表數(shù)據(jù)減運(yùn)算粘貼結(jié)果【小貼士】采用該方法核對(duì)數(shù)據(jù)，要確保兩個(gè)表的行、列數(shù)據(jù)要一致。否則將得到錯(cuò)誤的結(jié)果。若要核對(duì)排列不同，或者行數(shù)據(jù)不同的兩張表，可參考下例。03核對(duì)兩張表數(shù)據(jù)核對(duì)兩張表數(shù)據(jù)現(xiàn)有兩張表，分別為2018年、2019年優(yōu)秀員工表，現(xiàn)需要找到2019年優(yōu)秀員工表中，是否有2018年優(yōu)秀員工，如果有，在“是否為2018年優(yōu)秀員工”列填入“是”，否則填入“否”。核對(duì)兩張表數(shù)據(jù)表名：2018年優(yōu)秀員工表名：2019年優(yōu)秀員工思路如下：以“編號(hào)”為關(guān)鍵字段，在“2019男優(yōu)秀員工”表中，找出“2018年優(yōu)秀員工”表中是否存在相同的編號(hào)，如存在，則標(biāo)識(shí)為“是”，否則標(biāo)識(shí)為“否”。核對(duì)兩張表數(shù)據(jù)在“2019年優(yōu)秀員工”表C3單元格，輸入公式：=IF(COUNTIF('18年優(yōu)秀員工'!A$3:A$15,A3)>0,"是","否")，將2019年表的A3單元格與18年優(yōu)秀員工表的A3:A15區(qū)域計(jì)數(shù)，如果找到了該編號(hào)，則COUNTIF計(jì)數(shù)結(jié)果大于0，相應(yīng)單元格標(biāo)識(shí)為是，否則標(biāo)注為否。另外，也在“2019年優(yōu)秀員工”表C3單元格，輸入公式：=VLOOKUP(A3,'18年優(yōu)秀員工'!A$3:B$15,1,FALSE)，顯示為“#N/A”（錯(cuò)誤）表示在2018年表中沒有找到相關(guān)數(shù)據(jù)核對(duì)兩張表數(shù)據(jù)若希望不出現(xiàn)“#N/A”錯(cuò)誤標(biāo)識(shí)，可輸入公式：=IFERROR(VLOOKUP(A3,'18年優(yōu)秀員工'!A$3:B$15,1,FALSE),"")。拓展訓(xùn)練—快速制作員工工資條工資條一般應(yīng)包含每個(gè)員工的各項(xiàng)數(shù)據(jù)，如工號(hào)、姓名、與薪酬有關(guān)的各項(xiàng)數(shù)據(jù)，并需要在每個(gè)員工工資數(shù)據(jù)之前，加上一行“工資條”，在工資數(shù)據(jù)后面再添加一行空行，以方便裁剪，但現(xiàn)有員工工資數(shù)據(jù)保存樣式不方便員工查看工資數(shù)據(jù)，該如何快速實(shí)現(xiàn)工資條制作呢？新建工資條工作簿在原工資數(shù)據(jù)文件中，新建一個(gè)工作簿，命名為“工資條”，將原表的各字段復(fù)制到“工資條”表格內(nèi)。插入一行，合并單元格居中，寫上標(biāo)題“工資條”，在“工資條”工作簿中的A3單元格寫上序號(hào)1。用Vlookup公式查閱數(shù)據(jù)在“工資條”表格的B3單元格輸入公式：=VLOOKUP(A3,基礎(chǔ)數(shù)據(jù)表!$A$2:$J$11,2,TRUE)，注意此處的單元格采用絕對(duì)引用，得到該員工的工號(hào)。接著，在工資條的C3、D3....單元格分別輸入公式，獲取姓名、基本工資等數(shù)據(jù)：C3單元格公式：=VLOOKUP(A3,基本數(shù)據(jù)表!$A$2:$J$11,3,TRUE)D3單元格公司：=VLOOKUP(A3,基本數(shù)據(jù)表!$A$2:$J$11,4,TRUE)一直將所有單元格數(shù)據(jù)全部查找出來。設(shè)置第一個(gè)員工工資條樣式在工資條工資數(shù)據(jù)之后，插入一空行，設(shè)置為無邊框，并將第2行和第3行設(shè)置為“所有框線”樣式。設(shè)置自動(dòng)填充選中工資條的第1、2、3和4行，將鼠標(biāo)置于選中的右下角位置，當(dāng)鼠標(biāo)變?yōu)椤?”號(hào)形狀時(shí)，按住鼠標(biāo)不動(dòng)，向下拖動(dòng)，Excel將自動(dòng)向下填充所有數(shù)據(jù)。填充后每個(gè)員工占4行，工資條標(biāo)題、工資項(xiàng)目、序號(hào)和數(shù)據(jù)以及之后的空行，拖動(dòng)時(shí)按每員工4行數(shù)據(jù)計(jì)算設(shè)置自動(dòng)填充工資條數(shù)據(jù)自動(dòng)填充謝謝項(xiàng)目1產(chǎn)品銷售統(tǒng)計(jì)分析演講人01產(chǎn)品銷售統(tǒng)計(jì)分析內(nèi)容導(dǎo)圖02項(xiàng)目背景項(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)一步分析。03項(xiàng)目簡介項(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ù)基礎(chǔ)上，完成分類匯總、復(fù)雜多級(jí)分類匯總、單條件匯總、多條件匯總、數(shù)據(jù)篩選、高級(jí)篩選、制作數(shù)據(jù)透視圖和透視表、統(tǒng)計(jì)提成額度、用圖表的形式分析銷售情況，利用條件格式標(biāo)注特殊數(shù)據(jù)，為了保證數(shù)據(jù)安全，還需要對(duì)文檔的安全做設(shè)置。04跨表自動(dòng)選擇數(shù)據(jù)跨表自動(dòng)選擇數(shù)據(jù)在錄入產(chǎn)品銷售記錄表中的員工姓名時(shí)，我們總希望能夠直接從列表中選擇而不用重新錄入。員工的姓名，已經(jīng)在員工表中存在了，因此，我們可以在銷售記錄表中的員工姓名列，引用員工信息表中員工姓名，直接從列表中選擇，當(dāng)員工信息表中員工姓名變化后，在銷售記錄表中會(huì)自動(dòng)改變，極大的提高錄入效率和準(zhǔn)確性。具體步驟如下：定義列名稱打開“員工信息表.xlsx”（此處我們用項(xiàng)目8的員工信息表為數(shù)據(jù)源），選中員工的所有姓名（當(dāng)然也可以選擇整個(gè)姓名列），選擇【公式】選項(xiàng)卡下的【定義名稱】按鈕定義列名稱步驟2：在彈出的【新建名稱】中輸入引用名稱“員工表姓名”，點(diǎn)擊【確定】按鈕定義列名稱步驟3：打開“銷售記錄表.xlsx”文件，選中“員工姓名”下的所有單元格，用步驟1的方式，定義引用名稱定義列名稱步驟4：引用列名稱在彈出的【新建名稱】對(duì)話框中，在名稱欄輸入“銷售表員工姓名”，在【引用位置】填寫“=員工信息表.xlsx!員工表姓名”，點(diǎn)擊【確定】按鈕定義列名稱【小貼士】在整個(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)證】按鈕定義列名稱步驟6：在彈出的【數(shù)據(jù)驗(yàn)證】對(duì)話框中，【允許】列表中選擇【序列】，確保選中【提供下拉箭頭】前的復(fù)選框，在【來源】文本框中輸入步驟4定義的名稱，此處為“銷售表員工姓名”，點(diǎn)擊【確定】按鈕，定義列名稱上述設(shè)置完畢，即完成了對(duì)員工姓名列表選擇的設(shè)置，通過單元格旁邊的下小三角形符號(hào)選擇員工姓名，當(dāng)員工信息表的數(shù)據(jù)發(fā)生了變化，此處的數(shù)據(jù)跟著自動(dòng)變化，極大的方便了數(shù)據(jù)錄入定義列名稱可以用相同的辦法設(shè)置產(chǎn)品名稱的輸入方式，此處不再贅述。【小貼士】由于要根據(jù)員工信息表.xlsx和產(chǎn)品信息表.xlsx的表名稱引用，因此當(dāng)做了上述設(shè)置之后，對(duì)產(chǎn)品信息表.xlsx和員工信息表.xlsx的文件名不能再做改動(dòng)，否則不能在下拉列表中出現(xiàn)相應(yīng)的數(shù)據(jù)。05用Vlookup函數(shù)跨表查詢用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)），對(duì)數(shù)據(jù)源的引用可用鼠標(biā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ù)，用Vlookup函數(shù)跨表查詢第三個(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è)置。如REF_Ref496447674\h圖9-8所示。圖9-SEQ圖9-\*ARABIC8使用VLOOKUP函數(shù)跨表查閱數(shù)據(jù)用Vlookup函數(shù)跨表查詢步驟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ò)誤提示。用Vlookup函數(shù)跨表查詢步驟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函數(shù)表示：=VLOOKUP（要查找的值、要在其中查找值的區(qū)域、區(qū)域中包含返回值的列號(hào)、精確匹配或近似匹配–指定為0/FALSE或1/TRUE）。（1）要查找的值，也被稱為查閱值。（2）查閱值所在的區(qū)域。請(qǐng)記住，查閱值應(yīng)該始終位于所在區(qū)域的第一列，這樣VLOOKUP才能正常工作。（3）區(qū)域中包含返回值的列號(hào)。例如，如果指定B2：D11作為區(qū)域，那么應(yīng)該將B算作第一列，C作為第二列，以此類推。（4）如果需要返回值的近似匹配，可以指定TRUE；如果需要返回值的精確匹配，則指定FALSE。如果沒有指定任何內(nèi)容，默認(rèn)值將始終為TRUE或近似匹配。06設(shè)置貨幣單元格設(shè)置貨幣單元格為讓銷售記錄表的“產(chǎn)品單價(jià)”、“銷售單價(jià)”和“銷售總價(jià)”等列顯示為特殊的貨幣格式，如“￥68.00”，需要將每個(gè)單元格的數(shù)據(jù)格式設(shè)置為貨幣格式，選中列或單元格，點(diǎn)擊右鍵，選擇【設(shè)置單元格格式】，在彈出的對(duì)話框中，在【分類】列表中選擇【貨幣】，設(shè)置小數(shù)位數(shù)為2，貨幣符號(hào)選擇人民幣格式，點(diǎn)擊【確定】按鈕，即完成了貨幣格式設(shè)置。用相同方法，完成其他單元格格式的顯示設(shè)置。設(shè)置貨幣單元格設(shè)置單元格貨幣格式07分類匯總分類匯總按員工姓名完成對(duì)各產(chǎn)品的銷售總價(jià)匯總。特別注意的是，在進(jìn)行分類匯總之前，需將分類字段進(jìn)行排序，步驟如下：步驟1：點(diǎn)擊【數(shù)據(jù)】選項(xiàng)卡，選擇【排序】按鈕，彈出排序?qū)υ捒颍凇局麝P(guān)鍵字】處選擇【員工姓名】，先對(duì)數(shù)據(jù)按員工姓名排序；步驟2：選中數(shù)據(jù)表（必須將標(biāo)題行選中），單擊【數(shù)據(jù)】選項(xiàng)卡，選擇【分類匯總】按鈕。步驟3：在【分類匯總】對(duì)話框中，選擇分類字段為“員工姓名”，匯總方式為【求和】，【選定匯總項(xiàng)】為“銷售總額”，在此處可以多選分類匯總分類匯總設(shè)置分類匯總分類匯總結(jié)果08復(fù)雜多級(jí)分類匯總復(fù)雜多級(jí)分類匯總?cè)绻獙?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ì)話框中通過【添加條件】按鈕，選擇列和排序次序。點(diǎn)擊【確定】按鈕后完成對(duì)數(shù)據(jù)的多條件排序。復(fù)雜多級(jí)分類匯總步驟2：設(shè)置按產(chǎn)品類別分類匯總。選中匯總數(shù)據(jù)區(qū)域，點(diǎn)擊【數(shù)據(jù)】選項(xiàng)卡，選擇【分類匯總】按鈕，在【分類字段】選擇“產(chǎn)品類別”，【匯總方式】選擇“求和”，【選定匯總項(xiàng)】同時(shí)勾選“銷售總價(jià)”和“欠款數(shù)”，點(diǎn)擊【確定】按鈕，完成按產(chǎn)品種類的分類匯總復(fù)雜多級(jí)分類匯總按產(chǎn)品類別分類匯總復(fù)雜多級(jí)分類匯總步驟3：再次點(diǎn)擊【分類匯總】，在【分類字段】選擇“產(chǎn)品名稱”，一定確保不選擇【替換當(dāng)前分類匯總】前的復(fù)選框，點(diǎn)擊確定按鈕復(fù)雜多級(jí)分類匯總復(fù)雜多級(jí)分類匯總步驟4：再次點(diǎn)擊【分類匯總】，在【分類字段】選擇“員工姓名”，一定確保不選擇【替換當(dāng)前分類匯總】前的復(fù)選框，點(diǎn)擊確定按鈕復(fù)雜多級(jí)分類匯總多級(jí)分類匯總結(jié)果09單條件匯總單條件匯總實(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é)僅講解單條件匯總，多條件匯總請(qǐng)參見下一節(jié)。在匯總單元格輸入公式“=SUMIF(J3:J14,">=20000")”（不包含引號(hào)），其中J3:J14，表示要根據(jù)條件計(jì)算的單元格，“>=20000”是條件，該公式的意思是，在J3:J14單元格范圍內(nèi)，計(jì)算“>=20000”的所有數(shù)值之和。單條件匯總與前一個(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ù)求和?！拘≠N士】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)用條件的單元格）求和?！拘≠N士】可以在criteria參數(shù)中使用通配符（包括問號(hào)(?)和星號(hào)(*)）。問號(hào)匹配任意單個(gè)字符；星號(hào)匹配任意一串字符。10多條件匯總多條件匯總?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ū)域，以此類推。多條件匯總

多條件匯總【小貼士】1、SUMIFS函數(shù)的用法與COUNTIFS的用法類似；2、SUMIFS函數(shù)的用法：語法：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)11數(shù)據(jù)篩選數(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)卡下的【篩選】按鈕數(shù)據(jù)篩選數(shù)據(jù)篩選數(shù)據(jù)篩選單擊列標(biāo)題中的箭頭，然后單擊【文本篩選】或【數(shù)字篩選】。單擊其中一個(gè)比較運(yùn)算符，可以完成多種類型的篩選。例如，若要顯示介于下限與上限之間的數(shù)字，請(qǐng)選擇【介于】數(shù)據(jù)篩選在【自定義自動(dòng)篩選】框中，鍵入和選擇條件篩選數(shù)據(jù)。例如，在大于或等于后面的文本框中，鍵入200，然后在小于或等于框中，輸入1000，點(diǎn)擊【確定】按鈕后，將在表格中顯示大于等于200并且小于或等于1000的數(shù)據(jù)。數(shù)據(jù)篩選自定義篩選數(shù)據(jù)篩選取消篩選12高級(jí)篩選高級(jí)篩選當(dāng)需要篩選的條件比較復(fù)雜的時(shí)候，比如，要找員工是田水冬或者王三明，并且銷售總價(jià)在5000元到28000元的所有信息，則可以采用“高級(jí)篩選”來完成。具體步驟如下：步驟1：在表格的空白區(qū)域，復(fù)制原表需要篩選的列名，比如復(fù)制“員工姓名”和兩個(gè)“銷售總價(jià)”到空白區(qū)域；步驟2：在員工姓名列，分別復(fù)制田水冬和王三明兩個(gè)人的姓名，在條件區(qū)域中的“銷售總額”列，分別輸入條件“>=5000”和“<=20000”高級(jí)篩選步驟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)13制作數(shù)據(jù)透視圖和數(shù)據(jù)透視表制作數(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ù)透視表】命令。制作數(shù)據(jù)透視圖和數(shù)據(jù)透視表步驟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)建透視表和透視圖。制作數(shù)據(jù)透視圖和數(shù)據(jù)透視表步驟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)品查看制作數(shù)據(jù)透視圖和數(shù)據(jù)透視表logo透視圖和透視表設(shè)置14統(tǒng)計(jì)提成額度統(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)品類別”的透視表統(tǒng)計(jì)提成額度步驟2：可以直接在透視圖表基礎(chǔ)上直接用公式計(jì)算，但為簡便起見，將透視