《跟著名師學(xué)電腦Excel應(yīng)用入門實(shí)例》課件-第9章

實(shí)例9制作社區(qū)常住人口信息表—導(dǎo)入TXT文本數(shù)據(jù)—插入多列或多行—隱藏列和取消隱藏—插入實(shí)用函數(shù)—設(shè)置函數(shù)的參數(shù)及條件格式

學(xué)習(xí)情境

重陽(yáng)節(jié)就要到了，祥和小區(qū)準(zhǔn)備組織本社區(qū)內(nèi)所有退休的老人參加重陽(yáng)節(jié)登高活動(dòng)，目前需要統(tǒng)計(jì)本社區(qū)退休人數(shù)，以及退休人員的基本信息。

因此，社區(qū)辦事處要求秘書小李根據(jù)現(xiàn)有人員信息，制作本社區(qū)的常住人口信息表，主要內(nèi)容包括：姓名、身份證號(hào)、性別、出生年月、年齡、在崗情況，并使用特殊格式標(biāo)明已退休人員，以方便查看和篩選。最后還需要統(tǒng)計(jì)出本社區(qū)可參加重陽(yáng)節(jié)活動(dòng)的人數(shù)。

編排效果

掌握技能

通過(guò)本實(shí)例，將學(xué)會(huì)以下技能：

導(dǎo)入TXT文本數(shù)據(jù)。

插入多列或多行。

隱藏列和取消隱藏。

插入實(shí)用函數(shù)。

設(shè)置函數(shù)的參數(shù)及條件格式。

導(dǎo)入TXT文本數(shù)據(jù)

為Excel文檔添加數(shù)據(jù)，除了使用鍵盤錄入之外，還可以通過(guò)后綴名為“.txt”的文本文檔導(dǎo)入。

1.啟動(dòng)Excel程序，自動(dòng)新建名為“工作簿1”的文檔。

2.單擊“文件”選項(xiàng)卡→“打開(kāi)”命令。

3.在“打開(kāi)”對(duì)話框中，單擊“文件類型”按鈕。

4.在下拉列表中，單擊“所有文件”項(xiàng)。

5.選擇文本文檔所在的位置。1.新建2.單擊4.單擊3.單擊5.選擇

6.在文件預(yù)覽區(qū)域，單擊文件名稱。

7.單擊“打開(kāi)”按鈕，彈出“文本導(dǎo)入向?qū)А睂?duì)話框。

在向?qū)е?，可根?jù)文本文檔中數(shù)據(jù)的特征，自動(dòng)為數(shù)據(jù)分列，以適應(yīng)Excel文檔的排版特征。6.單擊7.單擊

文本導(dǎo)入向?qū)?/p>

1.在“文本導(dǎo)入向?qū)?第1步，共3步”對(duì)話框的“請(qǐng)選擇最合適的文件類型”項(xiàng)下選中“分隔符號(hào)”項(xiàng)。

此項(xiàng)需根據(jù)文本文檔的數(shù)據(jù)類型來(lái)選擇。若數(shù)據(jù)中存在類似空格、逗號(hào)或制表符的，可選“分隔符號(hào)”項(xiàng)，自動(dòng)調(diào)整列寬；若數(shù)據(jù)中無(wú)明顯分隔符特征的，可選“固定寬度”項(xiàng)，手動(dòng)定義列寬。

2.單擊“下一步”按鈕打開(kāi)“文本導(dǎo)入向?qū)?第2步，共3步”對(duì)話框。1.選中2.單擊

3.根據(jù)文檔中分隔符的類型，選中分隔符號(hào)，如“Tab鍵”。

4.選中“連續(xù)分隔符號(hào)視為單個(gè)處理”復(fù)選框。

5.單擊“下一步”按鈕打開(kāi)“文本導(dǎo)入向?qū)?第3步，共3步”對(duì)話框。

6.單擊“數(shù)據(jù)預(yù)覽”區(qū)域→“身份證號(hào)”列。

7.選中“列數(shù)據(jù)格式”→“文本”項(xiàng)。

8.單擊“完成”按鈕返回Excel文檔。4.選中3.選中5.單擊6.單擊7.選中8.單擊

變更文檔類型

文本文檔數(shù)據(jù)導(dǎo)入后，僅僅是使用Excel程序打開(kāi)了TXT文檔，還需要將該文檔另存為Excel文檔，數(shù)據(jù)才最終導(dǎo)入成功。

1.單擊“文件”選項(xiàng)卡→“另存為”命令，打開(kāi)“另存為”對(duì)話框。

2.在文件地址欄中，選擇文檔另存的路徑。

3.單擊“保存類型”項(xiàng)。

4.在下拉列表中，選中“Excel工作簿”項(xiàng)。此時(shí)，文件名稱不變，而文件后綴名稱由“.txt”變?yōu)椤?xlsx”。．

5.單擊“保存”按鈕，完成文檔的轉(zhuǎn)存。

一次性插入多列或多行

制作Excel文檔時(shí)，除了插入單列或單行，有時(shí)還需要插入多列或多行，此時(shí)如果一行一列地插入，過(guò)程難免繁瑣，因此下面介紹一種可以一次性完成多列或多行插入的操作。

1.單擊需要在其前方插入列的列號(hào)，如C列。

2.自C列拖動(dòng)鼠標(biāo)至G列，此時(shí)在選中區(qū)域的右側(cè)出現(xiàn)文字標(biāo)簽“5C”，表示選中了5列。2.拖動(dòng)1.單擊

3.單擊“開(kāi)始”選項(xiàng)卡→“單元格”組→“插入”按鈕，一次性地插入5列。

4.單擊插入列右側(cè)的“格式刷”按鈕。

5.在下拉列表中，選中“清除格式”項(xiàng)，為插入列清除格式。3.單擊4.單擊5.選中

隱藏列

表格設(shè)計(jì)完成后，有時(shí)會(huì)有許多用不到的行或列，干擾對(duì)數(shù)據(jù)的查看或操作。此時(shí)，可以選擇將其隱藏，當(dāng)需要使用時(shí)再取消隱藏。

1.選中不再需要查看的列，如列H至列M。

2.右鍵單擊選中列號(hào)的任意位置。

3.在快捷菜單中，單擊“隱藏”項(xiàng)隱藏選中的列。此時(shí)，列G和列N之間存在一條黑線，標(biāo)志兩列之間有列隱藏。2.右擊1.選中3.單擊隱藏效果

取消隱藏

列號(hào)是否連續(xù)標(biāo)志著兩列中是否有列隱藏。取消隱藏的步驟如下：

1.選中列號(hào)不連續(xù)的兩列。

2.右鍵單擊選中的列號(hào)

位置。

3.在快捷菜單中，單擊“取消隱藏”項(xiàng)，恢復(fù)隱藏的列。1.選中2.右擊3.單擊

插入取值函數(shù)

Excel根據(jù)功能不同對(duì)函數(shù)進(jìn)行了分類，包括財(cái)務(wù)類、邏輯類、文本類、日期和時(shí)間類、查找和引用類等。

我國(guó)公民的身份證號(hào)包含很多信息，從中可以提取籍貫、出生日期和性別信息。一般身份證號(hào)為15位或18位，15位身份證號(hào)的第15位代表性別，18位身份證號(hào)的第17位代表性別，其中奇數(shù)表示男性，偶數(shù)表示女性。

1.單擊C2單元格。

2.單擊“公式”選項(xiàng)卡→“函數(shù)庫(kù)”組→“文本”按鈕。2.單擊1.單擊

3.在下拉列表中，選中并單擊“MID”函數(shù)，打開(kāi)“函數(shù)參數(shù)”對(duì)話框。3.選中

4.設(shè)置“Text”項(xiàng)為“B2”，表示從B2單元格中提取信息；設(shè)置“Start_num”項(xiàng)為“15”，表示從第15位開(kāi)始取值；設(shè)置“Num_chars”項(xiàng)為“3”，表示向后取包括第15位的3位數(shù)。

5.單擊“確定”按鈕完成計(jì)算。此時(shí)，計(jì)算結(jié)果為“152”。5.單擊4.設(shè)置

插入求余函數(shù)

取出表示性別的值后，將其除以2，若余數(shù)為0則表示該值為偶數(shù)，否則表示該值為奇數(shù)。

1.單擊C2單元格。

2.選中編輯欄等號(hào)后面的公式，按組合鍵“Ctrl+

X”，將公式剪切至剪貼板。

3.單擊“公式”選項(xiàng)卡→“函數(shù)庫(kù)”組→“數(shù)學(xué)和三角函數(shù)”按鈕。

4.在下拉列表中，單擊“MOD”函數(shù)，打開(kāi)“函數(shù)參數(shù)”對(duì)話框。3.單擊4.單擊

5.單擊“Number”參數(shù)后面的編輯框，按組合鍵“Ctrl+V”，將剛才剪切的公式粘貼至空白處。

6.單擊“Divisor”參數(shù)后面的編輯框，輸入?yún)?shù)“2”。

7.單擊“確定”按鈕完成計(jì)算。此時(shí)，計(jì)算結(jié)果為“0”。

插入條件判斷函數(shù)

通過(guò)上面的兩步，已成功獲得身份證號(hào)中標(biāo)識(shí)性別的位是奇數(shù)還是偶數(shù)。接下來(lái)，就需要條件判斷函數(shù)IF根據(jù)奇偶性判斷單元格如何填寫，即如果值為偶數(shù)需填充“女”，值為奇數(shù)需填充“男”。

1.單擊C2單元格。

2.選中編輯欄等號(hào)后面的公式，按組合鍵“Ctrl

+X”，將公式剪切至剪貼板。1.單擊2.按組合鍵“Ctrl+X”

3.單擊“公式”選項(xiàng)卡→“函數(shù)庫(kù)”組→“邏輯”按鈕。

4.在下拉列表中，單擊“IF”函數(shù)，打開(kāi)“函數(shù)參數(shù)”對(duì)話框。

5.單擊“Logical_test”參數(shù)后面的編輯框，按組合鍵“Ctrl+V”，將剛才剪切的公式粘貼至空

白處。

6.單擊“Value_if_true”參數(shù)后面的編輯框，輸入?yún)?shù)“女”。

7.單擊“Value_if_false”參數(shù)后面的編輯框，輸入?yún)?shù)“男”。

8.單擊“確定”按鈕完成計(jì)算。此時(shí)，計(jì)算結(jié)果為“女”。3.單擊4.單擊

插入日期型函數(shù)

身份證號(hào)還可以提取公民的出生年月，其中，第7～10位提供出生的年份，第11和12位提供出生的月份，第13和14位提供出生的日期。提取到數(shù)值后，再將返回值嵌套在日期型函數(shù)中，轉(zhuǎn)換為日期即可。

1.單擊D2單元格。

2.單擊“公式”選項(xiàng)卡→“函數(shù)庫(kù)”組→“日期和時(shí)間”按鈕。

3.在下拉列表中，單擊“DATE”函數(shù)，打開(kāi)“函數(shù)參數(shù)”對(duì)話框。2.單擊1.單擊3.單擊

4.單擊“Year”參數(shù)后面的編輯框，輸入取值函數(shù)“MID(B2,7,4)”，表示從B2單元格的數(shù)值中取第7～10位的值，并轉(zhuǎn)換為年。

5.單擊“Month”參數(shù)后面的編輯框，輸入取值函數(shù)“MID(B2,11,2)”，表示從B2單元格的數(shù)值中取第11和12位的值，并轉(zhuǎn)換為月。

6.單擊“Day”參數(shù)后面的編輯框，輸入取值函數(shù)“MID(B2,13,2)”，表示從B2單元格的數(shù)值中取第13和14位的值，并轉(zhuǎn)換為日。

7.單擊“確定”按鈕完成設(shè)置，填充單元格的值為“1956-1-1”。5.輸入6.輸入4.輸入7.單擊

8.拖動(dòng)鼠標(biāo)句柄復(fù)制公式，填充至D3:D7單元格。8.拖動(dòng)

插入TODAY函數(shù)

Excel提供了多種日期型函數(shù)，其中TODAY函數(shù)能夠返回系統(tǒng)當(dāng)前日期，用當(dāng)前日期減去出生年月，可得出生天數(shù)，再除以365，即得年齡。

1.單擊E2單元格。

2.單擊“公式”選項(xiàng)卡→“函數(shù)庫(kù)”組→“日期和時(shí)間”按鈕→“TODAY”函數(shù)。1.單擊2.單擊

3.切換輸入法至英文狀態(tài)。

4.激活編輯欄，輸入“(TODAY()-D2)/365”。

5.單擊“輸入”按鈕完成公式編輯。

計(jì)算結(jié)果如圖所示。觀察發(fā)現(xiàn)，年齡值存在多位小數(shù)，與年齡的格式不符。一般地，實(shí)際年齡都以整數(shù)計(jì)算，未到生日則不能算作一年。如：21歲零9個(gè)月，則仍為21歲。而此處若以設(shè)置單元格格式的方法將小數(shù)位縮減，會(huì)造成四舍五入的效果，與實(shí)際年齡不符。3.切換5.單擊4.輸入計(jì)算結(jié)果

插入取整函數(shù)

INT取整函數(shù)屬于數(shù)學(xué)和三角函數(shù)的一種，它的功能是：向下取整，即為小數(shù)舍去所有小數(shù)位。INT函數(shù)設(shè)置簡(jiǎn)單，僅需要在參數(shù)位置輸入數(shù)值或者其他函數(shù)的返回值即可。

1.單擊E2單元格。

2.選中編輯欄等號(hào)后面的公式，按組合鍵“Ctrl+X”，將公式剪切至剪貼板。

3.單擊“公式”選項(xiàng)卡→“函數(shù)庫(kù)”組→“數(shù)學(xué)和三角函數(shù)”按鈕。

4.在下拉列表中，單擊“INT”函數(shù)，打開(kāi)“函數(shù)參數(shù)”對(duì)話框。3.單擊4.單擊

5.單擊“Number”參數(shù)后面的編輯框，按組合鍵“Ctrl+V”，將剛才剪切的公式粘貼至空白處。

6.單擊“確定”按鈕完成計(jì)算。

插入查找函數(shù)

根據(jù)年齡，可以粗略獲得此人的在崗情況，一般情況，22歲以下為學(xué)生，22歲～60歲在崗，60歲以上退休。Excel提供的查找函數(shù)中，能夠較好地解決多條件匹配問(wèn)題。

LOOKUP函數(shù)屬于查找與引用的一種，它的功能是：從單行或單列或數(shù)組中查找一個(gè)值，返回另一行或另一列或數(shù)組對(duì)應(yīng)位置的其他值。

1.單擊F2單元格。

2.單擊“公式”選項(xiàng)卡→“函數(shù)庫(kù)”組→“查找與引用”按鈕。

3.在下拉列表中，單擊“LOOKUP”函數(shù)，打開(kāi)“選定參數(shù)”對(duì)話框。

4.該函數(shù)有多種參數(shù)組合方式，此例使用數(shù)

組方式，在此單擊選中“l(fā)ookup_value,array”?項(xiàng)。

5.單擊“確定”按鈕，打開(kāi)“函數(shù)參數(shù)”對(duì)話框。

6.單擊“Lookup_value”參數(shù)后面的編輯框，在工作表中單擊單元格E2引用該單元格的值。

7.單擊“Array”參數(shù)后面的編輯框，輸入數(shù)組{0,22,60;"學(xué)生","在崗","退休"}。

8.單擊“確定”按鈕完成計(jì)算。

添加條件判斷

我國(guó)目前的退休年齡為：女性，55歲；男性，60歲。為了更加精確地計(jì)算在崗情況，需要為以上查找的數(shù)值添加條件判斷。

1.雙擊F2單元格，將單元格的公式剪切至剪

貼板。

2.單擊“公式”選項(xiàng)卡→“函數(shù)庫(kù)”組→“最近使用的函數(shù)”按鈕→“IF”函數(shù)，打開(kāi)“函數(shù)參數(shù)”對(duì)話框。

3.單擊“Logical_test”參數(shù)后面的編輯框，設(shè)置判斷條件“C2="男"”，表示當(dāng)性別為男時(shí)，匹配60歲退休，否則為55歲退休。

4.單擊“Value_if_true”參數(shù)后面的編輯框，將剛才剪切的公式粘貼至空白處。

5.單擊“Value_if_false”參數(shù)后面的編輯框，將剛才剪切的公式粘貼至空白處，并將“60”修改為“55”。

6.單擊“確定”按鈕完成計(jì)算。

插入數(shù)據(jù)統(tǒng)計(jì)函數(shù)

最后，根據(jù)在崗情況，就可以得出本社區(qū)重陽(yáng)節(jié)參加活動(dòng)的人數(shù)。

COUNTIF函數(shù)屬于統(tǒng)計(jì)類函數(shù)的一種，它的功能是：計(jì)算某區(qū)域內(nèi)，滿足條件的單元格數(shù)目。同類的計(jì)數(shù)函數(shù)大多以COUNT開(kāi)頭，根據(jù)滿足的條件不同，計(jì)算單元格的數(shù)目。

1.單擊C9單元格。

2.單擊“公式”選項(xiàng)卡→“函數(shù)庫(kù)”組→“其他”按鈕。

3.在下拉列表中，單擊“統(tǒng)計(jì)”→“COUNTIF”函數(shù)，打開(kāi)“函數(shù)參數(shù)”對(duì)話框。

4.單擊“Range”參數(shù)后面的編輯框，引用“F2:F7”單元格，表示條件判斷的區(qū)域。

5.單擊“Criteria”參數(shù)后面的編輯框，輸入“=退休”，表示計(jì)算在F2:F7單元格中滿足值=“退休”的單元格數(shù)目。

6.單擊“確定”按鈕完成計(jì)算。

應(yīng)用條件格式