項(xiàng)目8：?jiǎn)T工信息表制作與統(tǒng)計(jì)

項(xiàng)目8：?jiǎn)T工信息表制作與統(tǒng)計(jì)教師：電話(huà)：輔導(dǎo)時(shí)間：郵箱：第二部分Excel2016高級(jí)應(yīng)用案例知識(shí)技能點(diǎn)批量填充序列號(hào)拒絕錄入重復(fù)值姓名按筆畫(huà)排序下拉列表選擇自動(dòng)驗(yàn)證身份證位數(shù)自動(dòng)從身份證提取出生年月日自動(dòng)計(jì)算年齡員工生日自動(dòng)提醒單元格數(shù)據(jù)聯(lián)動(dòng)設(shè)置表格不同視圖凍結(jié)窗格創(chuàng)建、刪除自定義表格樣式單條件與多條件計(jì)數(shù)8.1項(xiàng)目背景小王在收集到員工的登記信息之后，希望通過(guò)Excel表格來(lái)統(tǒng)計(jì)分析員工的各項(xiàng)指標(biāo)數(shù)據(jù)，如部門(mén)、學(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來(lái)完成，借助功能強(qiáng)大的公式，既能大幅提高工作效率，又保證數(shù)據(jù)的準(zhǔn)確性，可以將分析統(tǒng)計(jì)結(jié)果復(fù)制到Word文檔中，提交完整的分析報(bào)告。8.2項(xiàng)目簡(jiǎn)介本項(xiàng)目利用Excel2016，實(shí)現(xiàn)有規(guī)律數(shù)據(jù)的批量填充、重復(fù)性檢驗(yàn)、將姓名按筆畫(huà)排序、從下拉列表中選擇數(shù)據(jù)，減少輸入工作量、驗(yàn)證數(shù)據(jù)的位數(shù)、自動(dòng)從身份證中提取出生的年月日、在任何時(shí)間打開(kāi)，都能根據(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ù)等功能。8.3項(xiàng)目制作8.3.1.批量填充序列號(hào)表格中的序號(hào)，表示該行在表格中的所處行的位置，一般每往下一行就增加1，對(duì)于這類(lèi)有規(guī)律的序列（如等差或等比性質(zhì)的數(shù)據(jù)），可以采用序列填充的方式完成數(shù)據(jù)錄入。快速填充多種類(lèi)型的數(shù)據(jù)序列，首先選擇要用作其他單元格填充基礎(chǔ)的單元格，然后拖動(dòng)填充手柄，將填充柄橫向或縱向拖過(guò)填充的單元格。步驟如下：步驟1：選擇包含要填充到相鄰單元格的數(shù)據(jù)的單元格。步驟2：拖動(dòng)填充柄，使其經(jīng)過(guò)要填充的單元格，如圖8-1所示。步驟3：要更改選定區(qū)域的填充方式，請(qǐng)單擊“自動(dòng)填充選項(xiàng)”，然后單擊所需的選項(xiàng)?！拘≠N士】通過(guò)拖動(dòng)自動(dòng)填充手柄，既可以實(shí)現(xiàn)有規(guī)律數(shù)據(jù)的自動(dòng)填充，同時(shí)可以完成單元格公式、樣式的自動(dòng)更新。圖8-1自動(dòng)填充8.3.2.拒絕錄入重復(fù)值在每個(gè)單位中，工號(hào)都是唯一的，在錄入數(shù)據(jù)時(shí)，是不允許重復(fù)的，可以采用“數(shù)據(jù)有效性”驗(yàn)證，來(lái)設(shè)置拒絕錄入重復(fù)的工號(hào)。具體步驟如下：步驟1：選中不允許重復(fù)的列，此處為B列步驟2：選擇【數(shù)據(jù)】菜單，點(diǎn)擊【數(shù)據(jù)驗(yàn)證】按鈕旁邊的符號(hào)，點(diǎn)擊【數(shù)據(jù)驗(yàn)證】命令，如圖8-2數(shù)據(jù)驗(yàn)證命令所示。彈出【數(shù)據(jù)驗(yàn)證】對(duì)話(huà)框。圖8-2數(shù)據(jù)驗(yàn)證命令步驟3：用公式設(shè)置不允許重復(fù)。在【允許】下拉列表中選擇【自定義】選項(xiàng)，在【公式】文本框中輸入“=COUNTIF(B:B,B1)=1”（不包含括號(hào)，在英文輸入狀態(tài)下輸入），如圖8-3所示?！拘≠N士】輸入公式時(shí)，一定要在英文狀態(tài)輸入，包括括號(hào)、冒號(hào)和逗號(hào)，否則公式不能正確運(yùn)行。圖8-3設(shè)置不允許重復(fù)步驟4：設(shè)置輸入提示信息。選擇【輸入信息】選項(xiàng)卡，在【輸入信息】文本框內(nèi)輸入提示消息“不允許重復(fù)”。如圖8-4所示圖8-4設(shè)置輸入提示信息步驟5：設(shè)置出錯(cuò)警告提示信息。選擇【出錯(cuò)警告】選項(xiàng)卡，在【樣式】下拉列表中選擇【警告】提示類(lèi)型，在【標(biāo)題】和【錯(cuò)誤消息】文本框中分別輸入提示信息，點(diǎn)擊確定。如圖8-5所示。圖8-5設(shè)置出錯(cuò)警告信息按上述步驟設(shè)置完畢后，當(dāng)在工號(hào)列（即本例的B列）輸入相同工號(hào)的時(shí)候，將會(huì)顯示如下的提示消息，提示輸入有誤，點(diǎn)擊【否】按鈕，關(guān)閉提示信息框，輸入正確的數(shù)據(jù)。如圖8-6所示。圖8-6輸入錯(cuò)誤提示信息8.3.3.姓名按筆畫(huà)排序在Excel中，除了可以將數(shù)字?jǐn)?shù)據(jù)按升序或降序排序之外，還可以對(duì)文本數(shù)據(jù)按照一定的規(guī)律排序，本例將完成對(duì)姓名按筆畫(huà)數(shù)排序。選擇需要排序的數(shù)據(jù)，點(diǎn)擊【數(shù)據(jù)】菜單，選擇【排序】按鈕，在彈出的對(duì)話(huà)框中選擇【擴(kuò)展選定區(qū)域】選項(xiàng)，在【排序】對(duì)話(huà)框中，將【主要關(guān)鍵詞】設(shè)置為【姓名】，【次序】列表中選擇【升序】，點(diǎn)擊【選項(xiàng)】按鈕，在【排序選項(xiàng)】對(duì)話(huà)框的【方法】組中，選擇【筆畫(huà)順序】選項(xiàng)，最后確定后，即完成了對(duì)姓名按筆畫(huà)排序。如圖8-7所示圖8-7按筆畫(huà)排序【小貼士】從【排序選項(xiàng)】對(duì)話(huà)框中，我們看到，還可以按字母排序，可以區(qū)分大小寫(xiě)，另外，還可以通過(guò)點(diǎn)擊【添加條件】增加更多的排序條件，多條件排序是在先按第一個(gè)條件排序后，再在排序后的結(jié)果中再按第二個(gè)條件來(lái)排序，依次類(lèi)推，可以實(shí)現(xiàn)更為精確的排序方式，讀者可自行嘗試。8.3.4.下拉列表選擇性別在諸如性別、學(xué)歷等選項(xiàng)，有固定的范圍，并且選項(xiàng)不多，如性別只有“男”和“女”，學(xué)歷只有“研究生”、“本科”、“大專(zhuān)”和“高中”等，為避免輸入格式不規(guī)范，減少輸入工作量，可以采用下拉列表的方式讓用戶(hù)選擇，提升用戶(hù)體驗(yàn)。具體步驟如下：步驟1：選擇需要提供下拉列表選擇的單元格，此處選擇“性別”列小的單元格，點(diǎn)擊【數(shù)據(jù)】選項(xiàng)卡，選擇【數(shù)據(jù)驗(yàn)證】按鈕，彈出【數(shù)據(jù)驗(yàn)證】對(duì)話(huà)框。步驟2：在【允許】下拉列表中選擇【序列】，確保選中【提供下拉箭頭】復(fù)選框，在【來(lái)源】文本框中輸入“男,女”，注意一定要確保男和女中間是用英文的逗號(hào)分隔，點(diǎn)擊【確定】按鈕，即完成了下拉列表的數(shù)據(jù)項(xiàng)的設(shè)置。如圖8-8所示?？梢杂猛瑯拥姆绞剑瓿伞皩W(xué)歷”欄的填寫(xiě)。如圖8-9所示圖8-8設(shè)置性別下拉列表圖8-9設(shè)置學(xué)歷下拉列表8.3.5.輸入并驗(yàn)證身份證號(hào)位數(shù)身份證位數(shù)，只能為15位或者18位，為了確保身份證輸入位數(shù)正確，可以通過(guò)【數(shù)據(jù)驗(yàn)證】來(lái)實(shí)現(xiàn)自動(dòng)驗(yàn)證身份證號(hào)碼的位數(shù)。具體方法如下。步驟1：定位到需要設(shè)置身份證長(zhǎng)度的單元格，此處為“F3”單元格，點(diǎn)擊【數(shù)據(jù)】菜單，選擇【數(shù)據(jù)驗(yàn)證】按鈕，彈出【數(shù)據(jù)驗(yàn)證】對(duì)話(huà)框。步驟2：設(shè)置驗(yàn)證條件。在【允許】下拉列表中選擇【自定義】，在【公式】文本框中輸入“=OR(LEN(F3)=15,LEN(F3)=18”（不包含引號(hào)），特別需要注意的是，所有的括號(hào)和逗號(hào)，都需要在英文輸入狀態(tài)下輸入，如圖8-10所示。步驟4：設(shè)置相應(yīng)的輸入信息和出錯(cuò)警告信息，最后點(diǎn)擊【確定】按鈕，完成對(duì)身份證輸入長(zhǎng)度的驗(yàn)證和錯(cuò)誤提示?！拘≠N士】為了讓輸入的身份證號(hào)碼不顯示為數(shù)字，需要先輸入符號(hào)“'”，即英文狀態(tài)下的單引號(hào)，表示將輸入的內(nèi)容以文本形式顯示。如輸入“。公式中的OR關(guān)鍵詞，表示“或者”的意思，只要符合表達(dá)式計(jì)算結(jié)果之一即可，除了OR之外，還有AND，NOT關(guān)鍵詞，讀者可自行查閱圖8-10設(shè)置身份證輸入長(zhǎng)度8.3.6.從身份證號(hào)提取出生年月日從文本中提取部分字符，需要用Excel中的LEFT()，RIGHT(),MID()等函數(shù)。下面以提取身份證號(hào)碼中的年月日為例說(shuō)明如何用字符截取函數(shù)，來(lái)提取年、月、日的方法。MID(text,start_num,num_chars)：text必需，包含要提取字符的文本字符串，start_num必需，文本中要提取的第一個(gè)字符的位置，文本中第一個(gè)字符的start_num為1，以此類(lèi)推，num_chars必需，指定希望MID從文本中返回字符的個(gè)數(shù)。LEFT(text,[num_chars])：Text必需，包含要提取的字符的文本字符串，num_chars，可選，指定要由LEFT提取的字符的數(shù)量。Num_chars必須大于或等于零，如果num_chars大于文本長(zhǎng)度，則LEFT返回全部文本。RIGHT(text,[num_chars])：text必需，包含要提取字符的文本字符串，num_chars可選，指定希望RIGHT提取的字符數(shù)，Num_chars必須大于或等于零，如果num_chars大于文本長(zhǎng)度，則RIGHT返回所有文本。在身份證號(hào)碼中，從第7位開(kāi)始的后4位為出生日期中的年份，后2位為月份，再后兩位為出生的日期，可以采用MID函數(shù)來(lái)提取相應(yīng)數(shù)據(jù)。選中“出生年月”的下一個(gè)單元格，此處為G3，在單元格中輸入“=MID(F3,7,4)&"/"&MID(F3,11,2)”。MID(F3,7,4)表示從單元格F3的字符中的第7個(gè)字符開(kāi)始，截取4個(gè)字符，得到出生的年，即“1979”，MID(F3,11,2)表示從F3單元格中的第11個(gè)字符開(kāi)始，截取2個(gè)字符，得到出生的月份，即“05”，兩者用“&”號(hào)將年與“/”和月連接起來(lái)，即組成了“1979/05”效果。上述公式，僅能截取長(zhǎng)度為18位的身份證號(hào)碼的年月數(shù)據(jù)，但是，由于身份證號(hào)碼有可能有15位數(shù)字，因此，為了實(shí)現(xiàn)根據(jù)身份證號(hào)碼不同長(zhǎng)度，能自動(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的長(zhǎng)度為18，即LEN(F3)=18條件滿(mǎn)足，如字符，就從第7個(gè)字符開(kāi)始，截取4個(gè)字符，得到“1979”作為年份，拼接符號(hào)“/”，再截取后兩個(gè)字符，得到字符“05”作為月份，最后的結(jié)果就為“1979/05”，如果LEN(F3)=18條件不滿(mǎn)足，如字符“510108890305405”，就由字符“19”拼接從F3單元格第6個(gè)字符開(kāi)始的兩個(gè)字符，得到字符“89”，拼接后的字符為“1989”，拼接字符“/”，再截取后兩個(gè)字符，得到“03”作為月份，最后的結(jié)果就為“1989/03”，滿(mǎn)足了需要的格式。如圖8-11所示【小貼士】按上述方式提取出來(lái)的數(shù)據(jù)，都是文本格式，如果需要日期格式的數(shù)據(jù)，可以用Date()函數(shù)將文本格式數(shù)據(jù)轉(zhuǎn)化為日期格式，公式如下：=DATE(MID(F3,7,4),MID(F3,11,2),MID(F3,13,2))，此處僅以18位的身份證為例。圖8-11提取出生日期數(shù)據(jù)8.3.7.自動(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)每一次打開(kāi)文件時(shí)，TODAY()函數(shù)的值會(huì)自動(dòng)改變。DATEDIF()函數(shù)的基本格式如下：DATEDIF(start_date,end_date,unit)，即DATEDIF(開(kāi)始日期,結(jié)束日期,返回參數(shù))，返回參數(shù)有Y、M、D、YM、YD、MD六種，返回的內(nèi)容大致如下表所示："Y"一段時(shí)期內(nèi)的整年數(shù)。"M"一段時(shí)期內(nèi)的整月數(shù)。"D"一段時(shí)期內(nèi)的天數(shù)。"MD"start_date與end_date之間天數(shù)之差。忽略日期中的月份和年份?！癥M”start_date與end_date之間月份之差。忽略日期中的天和年份"YD"start_date與end_date的日期部分之差。忽略日期中的年份。根據(jù)以上分析，通過(guò)如下公式來(lái)完成年齡的自動(dòng)計(jì)算：“=DATEDIF(H3,TODAY(),”Y”)”，開(kāi)始的日期為H3中的數(shù)據(jù)，結(jié)束的時(shí)間為T(mén)ODAY()函數(shù)自動(dòng)取得的當(dāng)前日期，以“Y”代表獲取年份之差。如8-12所示。將I3單元格向下自動(dòng)填充，即可實(shí)現(xiàn)年齡的自動(dòng)計(jì)算。圖8-12自動(dòng)計(jì)算年齡8.3.8.員工生日自動(dòng)提醒假如公司在員工生日當(dāng)天送禮物，一般需要提前購(gòu)買(mǎi)禮品，因此可以在員工信息表里能設(shè)置提醒，提示還有多少天就是員工生日了。假設(shè)從還有10天就開(kāi)始提醒，過(guò)了生日之后，就自動(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”)來(lái)計(jì)算兩個(gè)的日期之差。具體輸入如圖8-13所示：公式“=TEXT(10-DATEDIF(H3-10，TODAY(),”yd”),”還有0天生日;;今天生日”)”表示的意思是：DATEDIF函數(shù)的計(jì)算結(jié)果大于0的，顯示為“還有N天生日”；小于0的不顯示；等于0的顯示為“今天生日”。TEXT函數(shù)的作用，是將結(jié)果以文本的形式顯示出來(lái)。圖8-13生日提醒公式8.3.9.根據(jù)學(xué)歷自動(dòng)產(chǎn)生學(xué)位在現(xiàn)行的教育體系中，學(xué)歷有博士研究生、碩士研究生、本科、大專(zhuān)和高中等，對(duì)應(yīng)的學(xué)位分別為博士、碩士、學(xué)士，大專(zhuān)和高中沒(méi)有學(xué)位。現(xiàn)要求根據(jù)選擇或輸入的學(xué)歷，自動(dòng)在學(xué)位欄輸出相應(yīng)的學(xué)位，如果學(xué)歷為大專(zhuān)或高中，則在學(xué)位欄輸出“無(wú)”。如圖8-14所示。圖8-14根據(jù)學(xué)歷自動(dòng)輸出學(xué)位使用的公式如下：=IF(K3="博士研究生","博士",IF(K3="碩士研究生","碩士",IF(K3="本科","學(xué)士","無(wú)")))，所代表的意思為：如果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)容輸出為“無(wú)”。【小貼士】IF函數(shù)是在Excel中最常用的函數(shù)之一，它允許對(duì)單元格數(shù)據(jù)進(jìn)行邏輯判斷，簡(jiǎn)單的形式理解為：如果（內(nèi)容為T(mén)rue，則執(zhí)行某些操作，否則就執(zhí)行其他操作）因此IF語(yǔ)句有兩個(gè)結(jié)果。第一個(gè)結(jié)果是條件為T(mén)rue時(shí)的結(jié)果，如果條件比較為False，則執(zhí)行第二個(gè)操作。IF語(yǔ)句是可以嵌套的，如下面例子，可以將學(xué)生的考試成績(jī)轉(zhuǎn)化為相應(yīng)的等級(jí)：相應(yīng)的公式為：=IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))此復(fù)雜嵌套IF語(yǔ)句遵循一個(gè)簡(jiǎn)單邏輯：1.如果TestScore（單元格D2）大于89，則學(xué)生獲得A2.如果TestScore大于79小于等于89，則學(xué)生獲得B3.如果TestScore大于69小于等于79，則學(xué)生獲得C4.如果TestScore大于59小于等于69，則學(xué)生獲得D5.否則，學(xué)生獲得F我們發(fā)現(xiàn)，當(dāng)條件比較多的時(shí)候，用IF語(yǔ)句將形成多層的嵌套，語(yǔ)義比較復(fù)雜，這時(shí)可以使用IFS函數(shù)，簡(jiǎn)化多條件判斷語(yǔ)句的書(shū)寫(xiě)，并且更容易理解。在上述轉(zhuǎn)化等級(jí)的多條件判斷語(yǔ)句中，可以用如下形式來(lái)完成：=IFS(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",TRUE,"F")。所代表的意思就是：如果（D2大于89，則返回“A”，如果D2大于79，則返回“B”并以此類(lèi)推，對(duì)于所有小于59的值，返回“F”）。讀者可以自行嘗試用IFS多條件語(yǔ)句修改前例。8.3.10.設(shè)置入職日期為當(dāng)前日期在Excel中，插入當(dāng)前日期的方式用快捷鍵【Ctrl】+【;】組合鍵，或者使用函數(shù)Today（）可以快速插入當(dāng)前的日期。8.3.11.設(shè)置不同顯示視圖如果需要在Excel中反復(fù)多次按照不同條件進(jìn)行篩選、隱藏行列等操作，當(dāng)表格數(shù)據(jù)較多、自動(dòng)篩選條件比較復(fù)雜時(shí)，反復(fù)操作就變成很麻煩的一件事了，采用“自定義視圖”，可以對(duì)每次的篩選結(jié)果都保留下來(lái)，方便在后面任何時(shí)候可以方便的打開(kāi)查看，并且可以將處理好了后的內(nèi)容打印出來(lái)，極大的方便針對(duì)不同的人或不同篩選后的打印管理。為生成沒(méi)有身份證號(hào)碼的員工信息信息表，在“員工信息表模板.xlsx”文件中，在“身份證號(hào)碼”列點(diǎn)擊右鍵，選擇【隱藏】命令，表示將該列隱藏起來(lái)，再選擇【視圖】菜單，點(diǎn)擊【自定義視圖】按鈕，彈出【視圖管理器】對(duì)話(huà)框，如圖8-15所示。點(diǎn)擊【添加】按鈕，在彈出的對(duì)話(huà)框中輸入視圖的名稱(chēng)，如“隱藏身份證號(hào)的員工信息”，點(diǎn)擊【確定】按鈕，如圖8-16所示。圖8-15視圖管理器圖8-16為視圖命名用同樣方法，建立其他的視圖（將身份證號(hào)碼列取消隱藏）。當(dāng)需要查看、打印不同信息時(shí)，選擇【視圖】->【自定義視圖】在彈出的【視圖管理器】對(duì)話(huà)框中，選擇不同的視圖名稱(chēng)，點(diǎn)擊【顯示】按鈕，即可顯示預(yù)先定義好的內(nèi)容，并可快速打印出來(lái)。圖8-17顯示不同視圖8.3.12.凍結(jié)窗格若要使工作表的某一區(qū)域在滾動(dòng)到工作表的另一區(qū)域時(shí)仍保持可見(jiàn)，可采用“凍結(jié)窗格”的方法來(lái)完成。點(diǎn)擊【視圖】選項(xiàng)卡，在此選項(xiàng)卡中，選擇【凍結(jié)窗格】，將特定的行和列鎖定到位，也可以“拆分”窗格，創(chuàng)建同一工作表的單獨(dú)窗口。如果電子表格中的第一行包含標(biāo)題，可以?xún)鼋Y(jié)該行，確保在電子表格中向下滾動(dòng)時(shí)列標(biāo)題保持可見(jiàn)。如圖8-18所示，首行被凍結(jié)后，下面的內(nèi)容滾動(dòng)，首行仍然保持不動(dòng)。圖8-18凍結(jié)窗格在選擇凍結(jié)工作表中的行或列之前，以下問(wèn)題需要注意：

（1）只能凍結(jié)工作表中的頂行和左側(cè)的列，無(wú)法同時(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將無(wú)法再凍結(jié)。如果要凍結(jié)行和列，需要同時(shí)凍結(jié)它們。若要僅鎖定一行，選擇“視圖”選項(xiàng)卡，然后單擊“凍結(jié)首行”。若要僅鎖定一列，選擇“視圖”選項(xiàng)卡，然后單擊“凍結(jié)首列”。若要鎖定多行或多列，或同時(shí)鎖定行和列，選擇“視圖”選項(xiàng)卡，然后單擊“凍結(jié)窗格”，然后將光標(biāo)置于要凍結(jié)的行下以及任何要凍結(jié)的列的右側(cè)。若要鎖定多行（從第1行開(kāi)始），選擇要凍結(jié)的最后一行下方的一行，選擇“視圖”選項(xiàng)卡，然后單擊“凍結(jié)窗格”。

若要鎖定多列，選擇要凍結(jié)的最后一列右側(cè)的一列，選擇“視圖”選項(xiàng)卡，然后單擊“凍結(jié)窗格”。8.3.13.設(shè)置跨列居中一般情況下，表格的第一行，總是表格的標(biāo)題，標(biāo)題一般需要設(shè)置跨列居中對(duì)齊，可以通過(guò)“合并居中”的來(lái)實(shí)現(xiàn)，首先選中需要合并的單元格，點(diǎn)擊【開(kāi)始】選項(xiàng)卡，選擇【合并后居中】按鈕，即可完成對(duì)選定單元格的合并，并讓單元格內(nèi)容居中對(duì)齊。如圖8-19所示。圖8-19多個(gè)單元格合并居中8.3.14.設(shè)置表格樣式Excel提供了可用于快速設(shè)置表格格式的許多預(yù)定義的表格樣式。如果預(yù)定義的表格樣式不能滿(mǎn)足需要，可以創(chuàng)建并應(yīng)用自定義表格樣式?？梢赃M(jìn)一步調(diào)整表格的格式選擇快速樣式表的元素，如標(biāo)題和匯總行、第一個(gè)和最后一列、鑲邊行和列，以及自動(dòng)篩選選項(xiàng)等。如圖8-20所示。圖8-20表格樣式選項(xiàng)當(dāng)有未格式化為表格的數(shù)據(jù)區(qū)域時(shí)，Excel將自動(dòng)將其轉(zhuǎn)換為表格時(shí)選擇表格樣式，可以選擇不同的格式來(lái)更改現(xiàn)有表的格式。1.選擇表中或要為表格設(shè)置格式的單元格。2.在開(kāi)始選項(xiàng)卡上，單擊套用表格格式。如圖8-21所示3.單擊要使用的表格樣式，即可快速應(yīng)用格式。圖8-21快速表格樣式8.3.15.創(chuàng)建自定義表格樣式選擇要用于創(chuàng)建自定義樣式表中的任意單元格，在【開(kāi)始】選項(xiàng)卡上，單擊【套用表格格式】，點(diǎn)擊【新建表格樣式】，這將啟動(dòng)新的表格樣式對(duì)話(huà)框。如8-22所示圖8-22新建表格樣式在【名稱(chēng)】框中，鍵入新的表格樣式的名稱(chēng)。在表元素框中，可以執(zhí)行如下的操作︰

（1）選擇要設(shè)置格式的元素，然后單擊【格式】按鈕，然后從字體、邊框或填充選項(xiàng)卡中選擇所需的格式設(shè)置選項(xiàng)；

（2）若要?jiǎng)h除現(xiàn)有元素的格式，單擊相應(yīng)元素，然后單擊清除。

（3）在預(yù)覽中，可以看到所做的更改格式對(duì)表的影響。

（4）若要用作當(dāng)前工作簿中的默認(rèn)表格樣式的新表樣式，選擇設(shè)置為此文檔的默認(rèn)表格樣式復(fù)選框。8.3.16.刪除自定義表格樣式選擇要從中刪除自定義表格樣式表中的任意單元格。（1）在開(kāi)始選項(xiàng)卡上，單擊【套用表格格式】。（2）在【自定義】組中，右鍵單擊要?jiǎng)h除的表格樣式，然后單擊快捷菜單上的刪除。8.3.17.單條件計(jì)數(shù)使用CountIF函數(shù)，可以統(tǒng)計(jì)某個(gè)區(qū)域內(nèi)滿(mǎn)足某個(gè)條件的單元格數(shù)量，如本例要統(tǒng)計(jì)每個(gè)部門(mén)的人數(shù)。步驟如下：步驟1：輸入部門(mén)數(shù)據(jù)，按列錄入；步驟2：在“研發(fā)部”后面的單元格，輸入公式“=COUNTIF($L$3:$L$12,P2)”，表示將在絕對(duì)定位為L(zhǎng)3到L12的范圍內(nèi)，尋找P2單元格中內(nèi)容出現(xiàn)的次數(shù)。錄入完畢回車(chē)即輸出研發(fā)部出現(xiàn)的次數(shù)。如圖8-23所示。圖8-23使用COUNTIF函數(shù)計(jì)數(shù)步驟3：在Q2單元格，拖動(dòng)鼠標(biāo)，向下填充。進(jìn)入售后支持部后的單元格內(nèi)，檢查公式，發(fā)現(xiàn)“$L$3:$L$12”并沒(méi)有隨著拖動(dòng)而自動(dòng)變化，而P5由于拖動(dòng)，已經(jīng)自動(dòng)發(fā)生了變化。如圖8-24所示圖8-24分析絕對(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ì)引用絕