用excel編輯電話號碼(15 points)

Question

我有上萬筆的資料，一欄是電話號碼，一欄是縣市地址。
但電話號碼欄的區碼殘缺不全，
例如北市(02)，有的缺一碼(0)，有的兩碼都缺(02)。

我不想逐筆檢視更正，用excel編輯的話，電腦可以幫助我嗎？

規則就是:1.我知道每縣市該有幾碼 2.我知道縣市地址

什麼什麼？聽不懂/_\

實在太專業了，甘拜下風Orz

那麼，如果我沒有( )呢？正確的桃園電話應該是03xxxxxxx，可是我面對的不是3xxxxxxx(缺0)，就是xxxxxxxx(缺03)，降可以嗎？

我現在是抓取後X碼的觀念，而迴避殘缺的區碼。(太少的就直接顯示錯誤)但哪個縣市要抓6,7,8碼，我無法讓程式自己判斷。因為用vlookup去找參考表，再納入另一個儲存格的變數中，好像會變成循環參照。Anyway,謝謝大家。你們都好厲害！我該給誰點數呢@@

忽然發現，大家都是研究生，我真是幸福！

G1->=IF(LEN(A1)>=F1,MID(A1,LEN(A1)-F1+1,A1),"號碼過少")

F1->=VLOOKUP(LEFT(B1,2),$I$2:$K$22,3,0)

G1看F1，F1又去看別人。所以，G1的IF判別永遠是false。是這樣嗎？

我又問了個新的excel問題喔。麻煩大家幫我解答，謝謝！

Answer 1

圖片參考：http://home.pchome.com.tw/my/gtfi/1105070707856.gif
1.          先製作如圖的黃色區碼參考表2.          電話分解欄位：先把原電話號碼與區碼分解開來D2 儲存格公式：=IF(ISERROR(FIND(")",C2)),C2,MID(C2,FIND(")",C2)+1,99))公式意義：假如搜尋 ")" 字串為錯誤值（意即沒有輸入括弧的電話號碼），結果就是原來的電話；否則使用 MID 函數擷取")"之後的號碼，其中 99 表示可以共抓 99 個字元。公式完成之後，可以往下複製。3.          新的電話欄位：擷取住址的前兩個字，再使用 VLOOKUP函數查詢「區碼參考」表，得到新的電話，E2 儲存格公式=VLOOKUP(LEFT(B2,2),$G$2:$H$22,2,0)&D2公式完成之後，可以往下複製。下載範例檔案第二次補充：分析台灣地區電話號碼區碼之後的尾碼，不外乎 5、6、7、8區碼參考尾碼金門(0823)5苗栗(037)6台東(089)6桃園(03)7新竹(03)7宜蘭(03)7花蓮(03)7彰化(04)7南投(049)7雲林(05)7嘉義(05)7台南(06)7高雄(07)7花蓮(03)7屏東(08)7澎湖(06)7馬祖(08)7基隆(02)8台北(02)8台中(04)8因此乾脆不要考慮是否已經打區碼，直接抓取尾碼，再使用 VLOOKUP 加以查表。因此將 D2 公式改為：=IF(LEFT(B2,2)="金門",RIGHT(C2,5),IF(OR(LEFT(B2,2)="苗栗",LEFT(B2,2)="台東"),RIGHT(C2,6),IF(OR(LEFT(B2,2)="台北",LEFT(B2,2)="基隆",LEFT(B2,2)="台中"),RIGHT(C2,8),RIGHT(C2,7))))其他不變，完成～第三次補充：很感謝牧童提醒我，加上錯誤判斷，如果沒有輸入地址，那結果就讓它得到原來的電話號碼，因此得到公式如下：=IF(B2="",C2,IF(LEFT(B2,2)="金門",RIGHT(C2,5),IF(OR(LEFT(B2,2)="苗栗",LEFT(B2,2)="台東"),RIGHT(C2,6),IF(OR(LEFT(B2,2)="台北",LEFT(B2,2)="基隆",LEFT(B2,2)="台中"),RIGHT(C2,8),RIGHT(C2,7)))))第四次補充：再次回應你的問題，如果電話號碼缺碼，也就數字個數是小於 5 ，讓公式寫式 "不正確" 因此再把公式修改如下：=IF(B2="","",IF(LEN(C2)<5,"不正確",IF(LEFT(B2,2)="金門",RIGHT(C2,5),IF(OR(LEFT(B2,2)="苗栗",LEFT(B2,2)="台東"),RIGHT(C2,6),IF(OR(LEFT(B2,2)="台北",LEFT(B2,2)="基隆",LEFT(B2,2)="台中"),RIGHT(C2,8),RIGHT(C2,7))))))

2005-07-09 15:26:06 補充：
還有更好的解法，等我一下～

2005-07-09 15:57:12 補充：
如果地址開頭不是縣市，那麼就先排序，加以篩選出，然後已經有縣市的先套用公式。還沒有縣市的，一一補上，再套用公式。
要不，筆者我也投降囉～～

2005-07-09 16:00:58 補充：
如果電話號碼有空白字元，就先使用 TRIM 函數清除囉～

2005-07-11 04:11:27 補充：
把你的工作表欄位與公式貼上來，好讓筆者加以判斷哪裏造成了[循環參照]?

2005-07-11 19:15:48 補充：
欄位名稱提供一下唷
~~

Answer 2

所以如果你想婚的念頭已經確定
就去找一家評價好一點的婚友社!
行動吧!
這是我之前參加的婚友社我覺得不錯

或搜尋 紅娘李姐

Answer 3

所以如果你想婚的念頭已經確定
就去找一家評價好一點的婚友社!
行動吧!
這是我之前參加的婚友社我覺得不錯

或搜尋 紅娘李姐

Answer 4

所以如果你想婚的念頭已經確定
就去找一家評價好一點的婚友社!
行動吧!
這是我之前參加的婚友社我覺得不錯

或搜尋 紅娘李姐

Answer 5

線上遊戲天堂 http://ts999.tw

運動遊戲
實況棒球、籃球、足球、冰球、網球等多種經典賽事

即時遊戲
百家、21點、牌九、二八杠、骰寶、龍虎、輪盤、牛牛、番攤

對戰遊戲
麻將、德州撲克、二八杠、吹牛、鬥地主、13支、暗棋、接龍

電子遊戲
5PK、7PK、水果盤遊戲、5輪遊戲、極限飆速、趙雲救主、西遊戲、金瓶梅、中國美人、英雄

彩球遊戲
大樂透、六合、時時彩、今彩539、3星彩、4星彩

最新優惠活動
1. 運動遊戲連過5關,彩今再加碼10%
2. 新會員加入,有機會獲得500元禮券與精美讀卡機
3. 遊戲積分可兌換汽機車、3C、精品等多種大獎

****體驗試玩喔~!
線上遊戲天堂 http://ts999.tw

Answer 6

如果你很確定自己很想結婚
婚友社的確是一個有效率的管道
年紀越大壓力越大
以前我去婚友社時
年輕的女生機會總是比較多
時間總是不站在女生這邊的
所以如果你想婚的念頭已經確定
就去找一家評價好一點的婚友社!
行動吧!
這是我之前參加的婚友社我覺得不錯

或搜尋"紅娘李姐"

不過還是建議你自己去諮詢過看是否適合你!

Answer 7

有規則嗎？如果沒有是很難用程式做的

2005-07-08 09:34:17 補充：
厲害厲害

2005-07-08 14:29:13 補充：
應該是無解吧，連 ( ) 都沒有，如何區分呢

2005-07-09 08:50:41 補充：
那我補充一下好了 (借用一下 Blanco 的資料)
圖片參考：http://tinypic.com/6ygxgz.jpg
=VLOOKUP(LEFT(B2,2),$E$2:$G$3,2) & RIGHT(C2,VLOOKUP(LEFT(B2,2),$E$2:$G$3,3))我是延用 Blanco 的概念，但是增加一個電話長度的查表欄位，所以我不判斷 ( )，而是直接抓取電話右邊的號碼 (假設是正確的)，然後再用原先 Blanco 的概念把區域號碼加上去我是有想到一些問題如果地址的最前面兩個字不是地區的話如果電話後面有其他空白字元我沒有加錯誤的判斷

Answer 8

還真神@@~

Answer 9

你會使用資料剖析及建立資料庫(與縣市相對應區碼的資料庫)嗎,
圈點要分割的儲存格後點選上表 資料-資料剖析-下一步 這時可以直接用滑鼠按左鍵建立分欄線將每一列的資料分開-完成 ,先將區碼及縣市前3個字個別分割出來,必須在你要資料剖析前在電話號碼欄位後面插入空白欄, 因為分割後會變成兩欄(建議你複製一份備分,否則資料不見別找我),
資料庫的建立:建立一個空白工作表--在第一列把各縣市名稱建立(不可重複)--
第二列在對應第一列的縣市名稱輸入區碼--就可使用函數中的DGET指令自動搓合完成

2005-07-07 19:41:43 補充：
用文字說明很複雜, 有問題mail我吧[e-mail]

2005-07-08 17:59:12 補充：
程式還有這樣解的, 太猛ㄌ!!!