《跟著名師學電腦Excel應用入門實例》課件-第9章

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

學習情境

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

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

編排效果

掌握技能

通過本實例，將學會以下技能：

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

插入多列或多行。

隱藏列和取消隱藏。

插入實用函數(shù)。

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

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

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

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

2.單擊“文件”選項卡→“打開”命令。

3.在“打開”對話框中，單擊“文件類型”按鈕。

4.在下拉列表中，單擊“所有文件”項。

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

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

7.單擊“打開”按鈕，彈出“文本導入向?qū)А睂υ捒颉?/p>

在向?qū)е校筛鶕?jù)文本文檔中數(shù)據(jù)的特征，自動為數(shù)據(jù)分列，以適應Excel文檔的排版特征。6.單擊7.單擊

文本導入向?qū)?/p>

1.在“文本導入向?qū)?第1步，共3步”對話框的“請選擇最合適的文件類型”項下選中“分隔符號”項。

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

2.單擊“下一步”按鈕打開“文本導入向?qū)?第2步，共3步”對話框。1.選中2.單擊

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

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

5.單擊“下一步”按鈕打開“文本導入向?qū)?第3步，共3步”對話框。

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

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

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

變更文檔類型

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

1.單擊“文件”選項卡→“另存為”命令，打開“另存為”對話框。

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

3.單擊“保存類型”項。

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

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

一次性插入多列或多行

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

1.單擊需要在其前方插入列的列號，如C列。

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

3.單擊“開始”選項卡→“單元格”組→“插入”按鈕，一次性地插入5列。

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

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

隱藏列

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

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

2.右鍵單擊選中列號的任意位置。

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

取消隱藏

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

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

2.右鍵單擊選中的列號

位置。

3.在快捷菜單中，單擊“取消隱藏”項，恢復隱藏的列。1.選中2.右擊3.單擊

插入取值函數(shù)

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

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

1.單擊C2單元格。

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

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

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

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

插入求余函數(shù)

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

1.單擊C2單元格。

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

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

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

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

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

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

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

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

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

1.單擊C2單元格。

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

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

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

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

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

白處。

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

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

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

插入日期型函數(shù)

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

1.單擊D2單元格。

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

3.在下拉列表中，單擊“DATE”函數(shù)，打開“函數(shù)參數(shù)”對話框。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.拖動鼠標句柄復制公式，填充至D3:D7單元格。8.拖動

插入TODAY函數(shù)

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

1.單擊E2單元格。

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

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

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

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

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

插入取整函數(shù)

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

1.單擊E2單元格。

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

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

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

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

6.單擊“確定”按鈕完成計算。

插入查找函數(shù)

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

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

1.單擊F2單元格。

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

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

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

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

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

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

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

8.單擊“確定”按鈕完成計算。

添加條件判斷

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

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

貼板。

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

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

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

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

6.單擊“確定”按鈕完成計算。

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

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

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

1.單擊C9單元格。

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

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

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

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

6.單擊“確定”按鈕完成計算。

應用條件格式