77

Dear All,

小公司的人資在建立員工資料時候,手動輸入個人資料非常耗時耗神,其實身分證號碼(字號)可以自動判斷員工的性別、出生地,而且非常簡單。

使用到的函數: = ISODD, ISEVEN, IF,VLOOKUP

完成圖如下:

image

  一、介紹ISODD ISEVEN函數  

  • ISODD 函數是非常簡單,判斷數值如果是偶數顯示FALSE;奇數TRUE
  • ISEVEN 函數則剛好相反,判斷數值如果是偶數顯示TRUE;奇數FALSE

如下表:

函數

偶數

奇數

ISODD

FALSE

TRUE

ISEVEN

TRUE

FALSE

 

1. 公式: =ISODD (判斷的數值)

2. 案例公式: =ISODD (5),顯示FALSE=ISEVEN (5),顯示TRUE

 

  二、利用身分證判斷性別  

會使用到的函數:IF, ISODD, MID

image

1. 案例公式: C3=IF(FALSE=ISODD(MID(B4,2,1)),"","")

2. 解釋: 可先從MID函數開始了解 → ISODD函數 → 最後IF函數。

  • MID函數: B4儲存格從左至右第2個字元,並擷取開始的第1個字。值:1
  • ISODD函數: ISODD(1),案例: FALSE
  • IF函數: FALSE=FALSE的時候,顯示"",否則顯示""

 

ISEVEN函數,則相反,不在多作解釋。以下是比較:

  • C4=IF(FALSE=ISODD(MID(B4,2,1)),"","")
  • D4=IF(FALSE=ISEVEN(MID(B5,2,1)),"","")

 

公式延伸閱讀參考:

MID函數:

7. EXCEL 教學:擷取、合併文字、分割 不用自己KeyLEFT / RIGHT / CONCATENATE (&)/MID

IF函數:

10. EXCEL 教學:IF 函數應用可以更好 ()IF函數 / 新增規則

11. EXCEL 教學:IF 函數應用可以更好 ()IF函數 / AND / OR / NOT / 複製格式

 

  三、利用身分證,自動顯示出生地  

聰明的讀者應該很聰明的想到辦法了,沒錯!! 就是利用身分證第一個英文字母和 VLOOKUP 來顯示出生地。

首先要去找個大戶政事務所網站找出生地,連結按此處。

會使用到的函數:VLOOKUP, LEFT

image

接下來是公式說明:

1. 案例公式: =VLOOKUP(LEFT(B3,1),G:H,2,0)

2. 解釋: 可先從LEFT函數開始了解→最後VLOOKUP函數。

  • LEFT函數: 顯示B3儲存格,左邊開始的第1個字元,顯示: A
  • VLOOKUP函數: 顯示”A” G:H數列內,由左至右第二數列的對應數值,顯示: 臺北市。

 

備註:

  • G:H」意思: 涵蓋G:H欄位所有數列的數值。
  • 你該注意: 框選時候只要按著左鍵,拉選 image 從 欄位 → 欄位即可。

此種方式可以避免以後新增資料時候,需要修改公式的問題;下拉公式時候,公式跑掉的問題。以下是比較:

  • 不方便方式: =VLOOKUP(LEFT(B3,1),G2:H28,2,0)
  • 高效率方式: =VLOOKUP(LEFT(B3,1),G:H,2,0)
  •  

image

 

公式延伸閱讀參考:

VLOOKUP函數:

7. EXCEL 教學:擷取、合併文字、分割 不用自己KeyLEFT / RIGHT / CONCATENATE (&)/MID

20. EXCEL 教學:VLOOKUP 函數應用,比想像還簡單 / 附練習檔

 

延伸閱讀:

76. Excel教學 – 正數,負數值在區間內出現的次數 (FREQUENCY)

75. Excel教學 – 兩個表格,不同工作表的篩選 (VLOOKUP、IFERROR)


    愛偷懶先生 發表在 痞客邦 留言(0) 人氣()