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

學習情境

重陽節就要到了，祥和小區準備組織本社區內所有退休的老人參加重陽節登高活動，目前需要統計本社區退休人數，以及退休人員的基本信息。

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

編排效果

掌握技能

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

導入TXT文本數據。

插入多列或多行。

隱藏列和取消隱藏。

插入實用函數。

設置函數的參數及條件格式。

導入TXT文本數據

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

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

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

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

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

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

6.在文件預覽區域，單擊文件名稱。

7.單擊“打開”按鈕，彈出“文本導入向導”對話框。

在向導中，可根據文本文檔中數據的特征，自動為數據分列，以適應Excel文檔的排版特征。6.單擊7.單擊

文本導入向導

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

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

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

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

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

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

6.單擊“數據預覽”區域→“身份證號”列。

7.選中“列數據格式”→“文本”項。

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

變更文檔類型

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

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

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

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

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

5.單擊“保存”按鈕，完成文檔的轉存。

一次性插入多列或多行

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

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

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

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

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

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

隱藏列

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

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

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

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

取消隱藏

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

1.選中列號不連續的兩列。

2.右鍵單擊選中的列號

位置。

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

插入取值函數

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

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

1.單擊C2單元格。

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

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

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

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

插入求余函數

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

1.單擊C2單元格。

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

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

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

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

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

6.單擊“Divisor”參數后面的編輯框，輸入參數“2”。

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

插入條件判斷函數

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

1.單擊C2單元格。

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

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

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

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

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

白處。

6.單擊“Value_if_true”參數后面的編輯框，輸入參數“女”。

7.單擊“Value_if_false”參數后面的編輯框，輸入參數“男”。

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

插入日期型函數

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

1.單擊D2單元格。

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

3.在下拉列表中，單擊“DATE”函數，打開“函數參數”對話框。2.單擊1.單擊3.單擊

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

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

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

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

8.拖動鼠標句柄復制公式，填充至D3:D7單元格。8.拖動

插入TODAY函數

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

1.單擊E2單元格。

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

3.切換輸入法至英文狀態。

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

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

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

插入取整函數

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

1.單擊E2單元格。

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

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

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

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

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

插入查找函數

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

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

1.單擊F2單元格。

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

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

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

組方式，在此單擊選中“lookup_value,array”?項。

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

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

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

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

添加條件判斷

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

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

貼板。

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

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

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

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

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

插入數據統計函數

最后，根據在崗情況，就可以得出本社區重陽節參加活動的人數。

COUNTIF函數屬于統計類函數的一種，它的功能是：計算某區域內，滿足條件的單元格數目。同類的計數函數大多以COUNT開頭，根據滿足的條件不同，計算單元格的數目。

1.單擊C9單元格。

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

3.在下拉列表中，單擊“統計”→“COUNTIF”函數，打開“函數參數”對話框。

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

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

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

應用條件格式