cypd

帖子: 246
主題: 42
精華: 0
積分: 320
點名: 22
作業系統: XP
軟體版本: Office2007
閱讀權限: 20
註冊時間: 2015-2-20
最後登錄: 2025-10-20

1^# 跳轉到 » 正序看帖

打印

字體大小: tT

發表於 2020-8-12 23:08 | 只看該作者

[發問] 新舊帳號姓名轉換之疑惑?

本帖最後由 cypd 於 2020-8-12 23:17 編輯

新舊帳號姓名轉換之疑惑?

如圖所示:
1.資料來源為電腦轉換格式(從其他檔案複製貼上未改變其格式-E2:E597)
2.(E2:E597之格式不一，有數字(含全型數字)、中文姓名+數字參雜…)
3.(E2:E597資料來源數字為帳號代碼-舊帳號+新帳號混合)
4.經由複製資料來源電腦轉換格式貼上至 (預設 E2:E1001區間)
5.希望結果為 K欄更新帳號(需 4 碼) 及 L欄產生帳號對應之姓名
6.貼上帳號(舊、新)欄位 E2:E1001，經由公式轉換為 K欄為新帳號及 L欄產生帳號對應之姓名
7.若 K欄內之帳號經查核非新帳號(B2:B93)則 L欄相關位址顯示無此代碼
8.(E2:E1001區間內含有中文姓名或姓名+數字或符號…)該如何轉換成新帳號???
＊因為 E2:E597內容帳號格式不一筆數資料多甚為困擾，難以辨識舊帳號為何人?)

新舊帳號姓名轉換.rar (39.1 KB)

樓主熱帖

cypd

中學生 Rank: 2

帖子: 246
主題: 42
精華: 0
積分: 320
點名: 22
作業系統: XP
軟體版本: Office2007
閱讀權限: 20
註冊時間: 2015-2-20
最後登錄: 2025-10-20

10^#

發表於 2020-8-15 22:53 | 只看該作者

回復 9# 准提部林

真是太神了!!
所遇的問題由准提部林版主指導之公式已迎刃於解
將近 1000 多筆的舊帳號(格式參差不齊...)
經以上高手指導 2~3 秒就轉換完成 ^^
感恩 :victory:

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2843
主題: 10
精華: 0
積分: 2899
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2025-10-18

9^#

發表於 2020-8-15 09:31 | 只看該作者

回復 7# cypd

純姓名如何顯示???
=IFERROR(-LOOKUP(1,-SUBSTITUTE(MID(E2,MATCH(,MID(E2,ROW($1:$19),1)*0,),{1,2,3,4,5}),"-",)),TRIM(E2))

TOP

cypd

中學生 Rank: 2

帖子: 246
主題: 42
精華: 0
積分: 320
點名: 22
作業系統: XP
軟體版本: Office2007
閱讀權限: 20
註冊時間: 2015-2-20
最後登錄: 2025-10-20

8^#

發表於 2020-8-15 00:33 | 只看該作者

回復 5# jcchiang

水啦!!針對重點在...I 欄公式轉換時會導致CPU計算:(1~100處理器)"1~100%
經過  jcchiang 高手修正後貼上帳號代碼後執行非常順暢 ^^感恩

另外若是儲存格內之數據為  15-9B  B15-9  505-A...
會呈現 #VALUE!  B15-9  #VALUE! 錯誤!該如何修正公式?

TOP

cypd

中學生 Rank: 2

帖子: 246
主題: 42
精華: 0
積分: 320
點名: 22
作業系統: XP
軟體版本: Office2007
閱讀權限: 20
註冊時間: 2015-2-20
最後登錄: 2025-10-20

7^#

發表於 2020-8-14 23:58 | 只看該作者

回復 6# 准提部林
感謝准提部林不吝指導

F2/陣列公式:輔助公式
=IFERROR(-LOOKUP(1,-MID(E2,MIN(FIND(ROW($1:$10)-1,ASC(E2)&1/17)),{1,2,3,4})),"")

1.問題:若貼上之代碼為中文姓名時上述陣列公式無法正確產生所要之結果...如 (F564~F597)

2.問題:E499之儲存格內數據為 32-2
         E562之儲存格內數據為 55-0
         同樣是三位數加-，經公式轉換後為何?
         E499之儲存格內數據為 32-2轉換為 11720
         E562之儲存格內數據為 55-0轉換為 0050
轉換後的代碼 11720、0050 與姓名新帳號代碼不同卻能顯是相對應姓名?
(是否能將 F欄的公式修正取 E 欄的數字為主比較好辨識例-933(0933) 32-2(0322) 55-0 Z(0550)

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2843
主題: 10
精華: 0
積分: 2899
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2025-10-18

6^#

發表於 2020-8-14 10:31 | 只看該作者

F2/陣列公式:輔助公式
=IFERROR(-LOOKUP(1,-MID(E2,MIN(FIND(ROW($1:$10)-1,ASC(E2)&1/17)),{1,2,3,4})),"")

K2:
=IF(F2="","",IFERROR(INDEX(B:B,-LOOKUP(1,IF({1,0},-MATCH(F2,B:B,),-MATCH(F2,C:C,)))),F2))
L2:
=IF(K2="","",IFERROR(INDEX(A:A,MATCH(K2,B:B,)),"無此代碼"))

TOP

jcchiang

中學生 Rank: 2

帖子: 234
主題: 19
精華: 0
積分: 276
點名: 0
作業系統: Windows XP
軟體版本: office 2003
閱讀權限: 20
性別: 男
註冊時間: 2013-1-7
最後登錄: 2021-10-7

5^#

發表於 2020-8-14 09:28 | 只看該作者

回復 4# jcchiang

忘了I欄是放工號
I2=IFERROR(INDEX(B:B,MATCH(G2,C:C,0),1),IFERROR(INDEX(B:B,MATCH(G2,A:A,0),1),G2))
J2=IFERROR(INDEX(A:A,MATCH(I2,B:B,0),1),"無此代碼")

TOP

jcchiang

中學生 Rank: 2

帖子: 234
主題: 19
精華: 0
積分: 276
點名: 0
作業系統: Windows XP
軟體版本: office 2003
閱讀權限: 20
性別: 男
註冊時間: 2013-1-7
最後登錄: 2021-10-7

4^#

發表於 2020-8-14 09:02 | 只看該作者

本帖最後由 jcchiang 於 2020-8-14 09:09 編輯

回復 3# cypd

G2=IF(ISERROR(VALUE(LEFT(E2,1))),IF(ISERROR(VALUE(SUBSTITUTE(UPPER(SUBSTITUTE(RIGHT(E2,2*LEN(E2)-LENB(E2)),"-","")),"Z",""))),TRIM(E2),VALUE(SUBSTITUTE(UPPER(SUBSTITUTE(RIGHT(E2,2*LEN(E2)-LENB(E2)),"-","")),"Z",""))),VALUE(LEFT(SUBSTITUTE(UPPER(SUBSTITUTE(E2,"-","")),"Z",""),4)))
I2=IFERROR(INDEX(B:B,MATCH(G2,C:C,0),1),G2)
J2=IFERROR(INDEX(A:A,MATCH(I2,B:B,0),1),IFERROR(INDEX(A:A,MATCH(I2,A:A,0),1),"無此代碼"))

TOP

cypd

中學生 Rank: 2

帖子: 246
主題: 42
精華: 0
積分: 320
點名: 22
作業系統: XP
軟體版本: Office2007
閱讀權限: 20
註冊時間: 2015-2-20
最後登錄: 2025-10-20

3^#

發表於 2020-8-13 14:35 | 只看該作者

回復 2# jcchiang

非常感謝您的用心指導

1.帳號識別
G2=IF(ISERROR(VALUE(LEFT(E2,1))),IF(ISERROR(VALUE(LEFT(SUBSTITUTE(RIGHT(E2,2*LEN(E2)-LENB(E2)),"-",""),4))),TRIM(E2),VALUE(LEFT(SUBSTITUTE(RIGHT(E2,2*LEN(E2)-LENB(E2)),"-",""),4))),VALUE(LEFT(SUBSTITUTE(E2,"-",""),4)))

2.帳號轉換(G欄設定公式是避免I欄公式過長)
I2=IFERROR(VLOOKUP(G2,IF({1,0},C:C,B:B),2,0),IFERROR(VLOOKUP(G2,IF({1,0},A:A,B:B),2,0),G2))

3.產生姓名
J2=IFERROR(VLOOKUP(I2,IF({1,0},B:B,A:A),2,0),"無此代碼")

重點在...I 欄公式轉換時會導致CPU計算:(1~100處理器)"1~100%

是否能再針對(.I 欄公式)此問題再行修正讓檔案執行更順暢...感恩

TOP

jcchiang

中學生 Rank: 2

帖子: 234
主題: 19
精華: 0
積分: 276
點名: 0
作業系統: Windows XP
軟體版本: office 2003
閱讀權限: 20
性別: 男
註冊時間: 2013-1-7
最後登錄: 2021-10-7

2^#

發表於 2020-8-13 11:09 | 只看該作者

回復 1# cypd

做帳號識別
G2=IF(ISERROR(VALUE(LEFT(E2,1))),IF(ISERROR(VALUE(LEFT(SUBSTITUTE(RIGHT(E2,2*LEN(E2)-LENB(E2)),"-",""),4))),TRIM(E2),VALUE(LEFT(SUBSTITUTE(RIGHT(E2,2*LEN(E2)-LENB(E2)),"-",""),4))),VALUE(LEFT(SUBSTITUTE(E2,"-",""),4)))
帳號轉換
I2=IFERROR(VLOOKUP(G2,IF({1,0},C:C,B:B),2,0),IFERROR(VLOOKUP(G2,IF({1,0},A:A,B:B),2,0),G2))
產生姓名
J2=IFERROR(VLOOKUP(I2,IF({1,0},B:B,A:A),2,0),"無此代碼")
另外在G欄設定公式是避免I欄公式過長,看到眼花
以上是以所提供的資料格式做解讀,如有其他格式請自行修改

TOP