Kavin 發問時間: 電腦與網際網路軟體 · 1 0 年前

EXCEL問題!!(急)

請問

我要用EXCEL抓身分證字號的第二碼來判別性別

那我性別那個欄位該輸入甚麼的指令??

已更新項目:

不好意思@@ 請問可以弄成就是如果身分證號碼欄第二碼打成不是1跟2 會顯示錯誤的公式嗎?

還有就是如果我要判讀第一碼的英文字母抓戶籍地又該怎弄呢?

2 個已更新項目:

拍謝>"< 再問一個問題!! 就是我如果要判斷實歲 用YEARFRAC函數的話要怎麼用呢@@?

實在看不懂他的說明= =

2 個解答

評分
  • 1 0 年前
    最佳解答

    假設A1為你要輸入身份證字號:

    請在B1輸入下列公式:

    =IF(A1="","",IF(MID(A1,2,1)="1","男","女"))

    2010-11-07 19:56:05 補充:

    若判斷第二字為1或2,用資料/驗證/設定即可!

    公式自訂為:

    =OR(MID(B3,2,1)="1",MID(B3,2,1)="2")

    =CHOOSE(CODE(LEFT(A1,1))-64,"台北市","台中市","基隆市","台南市","高雄市","台北縣","宜蘭縣","桃園縣","嘉義市","新竹縣","曲栗縣","台中縣","南投縣","彰化縣","新竹市","雲林縣","嘉義縣","台南縣","高雄縣","屏東縣","台東縣","花蓮縣","金門縣","澎湖縣","陽明山","連江縣")&IF(MID(A1,2,1)="1","男","女")&"性"

    2010-11-07 19:56:35 補充:

    若判斷第二字為1或2,用資料/驗證/設定即可!

    公式自訂為:

    =OR(MID(A1,2,1)="1",MID(A1,2,1)="2")

    =CHOOSE(CODE(LEFT(A1,1))-64,"台北市","台中市","基隆市","台南市","高雄市","台北縣","宜蘭縣","桃園縣","嘉義市","新竹縣","曲栗縣","台中縣","南投縣","彰化縣","新竹市","雲林縣","嘉義縣","台南縣","高雄縣","屏東縣","台東縣","花蓮縣","金門縣","澎湖縣","陽明山","連江縣")&IF(MID(A1,2,1)="1","男","女")&"性"

    2010-11-08 08:20:06 補充:

    YEARFRAC 函數是2007版的函數。

    計算兩日間的天數於一年中的比例。用 YEARFRAC 工作表函數來指明要指定給某個特定項目的整年利潤或負擔的部分。

    語法

    YEARFRAC(start_date,end_date,basis)

    重要事項:使用 DATE 函數輸入日期或其他的公式。如: DATE(2008,5,23) 。不可用文字格式輸入日期。

    註:

    Basis係指所採用的日計利基的類型。

    利基 日計利基

    0 或省略 US (NASD) 30/360

    1 實際天數/實際天數

    2 實際/360

    3 實際/365

    4 歐洲 30/360

  • 甯鈞
    Lv 7
    1 0 年前

    A1是輸入身分證號碼

    B1公式如下

    =CHOOSE(CODE(LEFT(A1,1))-64,"台北市","台中市","基隆市","台南市","高雄市","台北縣","宜蘭縣","桃園縣","嘉義市","新竹縣","曲栗縣","台中縣","南投縣","彰化縣","新竹市","雲林縣","嘉義縣","台南縣","高雄縣","屏東縣","台東縣","花蓮縣","金門縣","澎湖縣","陽明山","連江縣")&IF(MID(A1,2,1)="1","男性",IF(MID(A1,2,1)="2","女性","輸入錯誤"))

    參考資料: 啊係哇第一 ( 知識長 )
還有問題?馬上發問,尋求解答。