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

# EXCEL問題!!(急)

2 個已更新項目:

### 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","女性","輸入錯誤"))

參考資料： 啊係哇第一 ( 知識長 )