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

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

多條件匯總【小貼士】1、SUMIFS函數(shù)的用法與COUNTIFS的用法類似；2、SUMIFS函數(shù)的用法：語法：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)11數(shù)據(jù)篩選數(shù)據(jù)篩選使用自動篩選或內(nèi)置比較運算符（例如“大于”和“前10個”等）可顯示所需的數(shù)據(jù)并隱藏其余數(shù)據(jù)。數(shù)據(jù)經(jīng)過篩選后，可以重新應(yīng)用篩選器獲取最新結(jié)果，或清除篩選器重新顯示所有數(shù)據(jù)。經(jīng)篩選過的數(shù)據(jù)僅顯示滿足指定條件的行，并隱藏不希望顯示的行。篩選數(shù)據(jù)之后，對于篩選過的數(shù)據(jù)的子集，不需要重新排列或移動就可以復(fù)制、查找、編輯、設(shè)置格式、制作圖表和打印，還可以按多個列進(jìn)行篩選。篩選器是累加的，這意味著每個追加的篩選器都基于當(dāng)前篩選結(jié)果，從而進(jìn)一步減少了數(shù)據(jù)的子集。點擊【數(shù)據(jù)】選項卡下的【篩選】按鈕數(shù)據(jù)篩選數(shù)據(jù)篩選數(shù)據(jù)篩選單擊列標(biāo)題中的箭頭，然后單擊【文本篩選】或【數(shù)字篩選】。單擊其中一個比較運算符，可以完成多種類型的篩選。例如，若要顯示介于下限與上限之間的數(shù)字，請選擇【介于】數(shù)據(jù)篩選在【自定義自動篩選】框中，鍵入和選擇條件篩選數(shù)據(jù)。例如，在大于或等于后面的文本框中，鍵入200，然后在小于或等于框中，輸入1000，點擊【確定】按鈕后，將在表格中顯示大于等于200并且小于或等于1000的數(shù)據(jù)。數(shù)據(jù)篩選自定義篩選數(shù)據(jù)篩選取消篩選12高級篩選高級篩選當(dāng)需要篩選的條件比較復(fù)雜的時候，比如，要找員工是田水冬或者王三明，并且銷售總價在5000元到28000元的所有信息，則可以采用“高級篩選”來完成。具體步驟如下：步驟1：在表格的空白區(qū)域，復(fù)制原表需要篩選的列名，比如復(fù)制“員工姓名”和兩個“銷售總價”到空白區(qū)域；步驟2：在員工姓名列，分別復(fù)制田水冬和王三明兩個人的姓名，在條件區(qū)域中的“銷售總額”列，分別輸入條件“>=5000”和“<=20000”高級篩選步驟3：選擇【數(shù)據(jù)】-【排序和篩選】功能組中的【高級】按鈕，彈出【高級篩選】對話框，在【方式】下選擇“將篩選結(jié)果復(fù)制到其他位置”，以避免篩選結(jié)果覆蓋原處的數(shù)據(jù)，可以幫助我們檢驗設(shè)置是否正確，在【列表區(qū)域】選擇需要篩選的數(shù)據(jù)（注意要包含數(shù)據(jù)的標(biāo)題行），【條件區(qū)域】選擇篩選條件（也要包含復(fù)制過來的標(biāo)題行），在【復(fù)制到】欄點擊表格的空白單元格，最后點擊【確定】按鈕，即將篩選的結(jié)果，放在以【復(fù)制到】文本框指定的為第一個單元格的范圍內(nèi)13制作數(shù)據(jù)透視圖和數(shù)據(jù)透視表制作數(shù)據(jù)透視圖和數(shù)據(jù)透視表要求：統(tǒng)計不同產(chǎn)品類別下不同產(chǎn)品的銷售總額的數(shù)據(jù)透視表和數(shù)據(jù)透視圖。具體步驟如下：步驟1：點擊【插入】選項卡，選擇【圖表】功能組的【數(shù)據(jù)透視圖】按鈕，選擇【數(shù)據(jù)透視圖和數(shù)據(jù)透視表】命令。制作數(shù)據(jù)透視圖和數(shù)據(jù)透視表步驟2：在彈出的【創(chuàng)建數(shù)據(jù)透視表】對話框中，【選擇一個表或區(qū)域】欄選擇要創(chuàng)建透視圖和透視表的數(shù)據(jù)區(qū)域，【選擇放置數(shù)據(jù)透視表的位置】根據(jù)自己需要選擇適當(dāng)位置，既可以新建一個數(shù)據(jù)表來存放，也可以在本表存放，設(shè)置完畢，點擊【確定】按鈕后，將出現(xiàn)數(shù)據(jù)數(shù)據(jù)透視表1和圖表1兩個窗口，可以分別在其中創(chuàng)建透視表和透視圖。制作數(shù)據(jù)透視圖和數(shù)據(jù)透視表步驟3：設(shè)置透視圖和透視表數(shù)據(jù)項。選擇圖表1或數(shù)據(jù)透視表1，將在右邊出現(xiàn)“數(shù)據(jù)透視圖字段”框，可以設(shè)置數(shù)據(jù)透視圖的統(tǒng)計字段。選擇字段，拖動到相應(yīng)區(qū)域，在左邊的透視圖和透視表實時顯示設(shè)置效果。設(shè)置完畢，點擊表格的任意空白處，即可查看透視圖和透視表，可以通過選擇“產(chǎn)品類別”下的下三角形箭頭，選擇某類，具體查看，也可以點擊某一行中的“產(chǎn)品名稱”，選擇某產(chǎn)品查看制作數(shù)據(jù)透視圖和數(shù)據(jù)透視表logo透視圖和透視表設(shè)置14統(tǒng)計提成額度統(tǒng)計提成額度產(chǎn)品種類不同，銷售提成的比例也不同，那么，如何計算每個員工所銷售產(chǎn)品的提成額度呢？以本例為例，假設(shè)藍(lán)牙設(shè)備的提成額度為10%，存儲設(shè)備的提成額度為8%，軟件類產(chǎn)品的提成額度為25%，要求快速計算每個員工的所有銷售提成額度。具體思路為：第一步：首先需要統(tǒng)計每個人每類產(chǎn)品的銷售總價，可以用透視表實現(xiàn)；第二步：在透視表的基礎(chǔ)上，根據(jù)不同產(chǎn)品不同的提成比例，計算每個人的提成額度，可以自己寫公式來完成。具體實現(xiàn)步驟如下：步驟1：建立行名稱為“員工姓名”，列名稱為“產(chǎn)品類別”的透視表統(tǒng)計提成額度步驟2：可以直接在透視圖表基礎(chǔ)上直接用公式計算，但為簡便起見，將透視