點樣set呢條excel formula...

2007-12-07 12:58 am
依家有d資料
A
1 中文25
2 英文38
3 拉丁文416

我想將佢拆開文字一個cell 數字一個cell

A B C
1 中文25 中文 25
2 英文38 英文 38
3 拉丁文416 拉丁文 416


點樣set條formula 可以搵到果個cell邊個位係數字...

回答 (3)

2007-12-07 2:59 am
✔ 最佳答案
Excel使用上,常可能得到的資料是從外部匯入,所以會有文字與數字在同一格儲存格的問題,若之間有分隔符號,那麼可以用「資料剖析」來完成分離,若是固定位數的字串,可以用Right、Left、Mid這些函數來達到分離,但若是數字與文字都不固定位數的字串,要怎麼分離呢?接下來就說明是怎麼去完成函數的。

原始範例如下:
1. 思考點是在於要怎麼知道數字與文字的分隔點在第幾位,所以就要用 ISNUMBER 來判別,那要怎麼把各個位數的字挑出來呢?就想到要用 MID 這函數,因為它可以自行設定要抓取的字元。這兩個思考點,就完成了這段程式: B2 =ISNUMBER(MID(A2,1,1))

2. 而 B2 的結果是:FALSE ,因為 A2 內容的第 1 的字是"中"所以是 FALSE 。接下來的思考,就是希望能抓取的是每個字,而不是只有第一個字,所以公式要改成: B2 =ISNUMBER(MID(A2,ROW(A1:A6),1))
這邊大概說明 Row(A1:A6) , Row 是抓取列數的函數,所以這樣就等於1~6,所以上面公式也可以改為: B2 =ISNUMBER(MID(A2,{1,2,3,4,5,6},1))
在輸入完後,可以嘗試把公式中 MID(A2,ROW(A1:A6),1)) 反白後按F9,就可以預覽公式計算過程(Esc鍵可取消預覽),結果為:B2 =ISNUMBER({"王","小","方","1","2","3"})

3. 前面兩步驟已經把字串中字元個別抓出,接著就用 ISNUMBER 來判別是不是數字,所以可以再嘗試 B2 全部公式反白後按F9,這樣看到的結果是:B2 ={FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}
但是依照 A2 的字串,應該在第 4 位就是數字,怎麼上面 6 位的判定都是 FALSE ?原因就在第 2 步的最後那行公式:B2 =ISNUMBER({"王","小","方","1","2","3"}) ,可以看到 1、2、3 都被 " " 所框選,這樣它就是字串,非數字。要避免這問題,那麼就要強制把字串轉換成數字,就把公式改為: B2=ISNUMBER(--MID(A2,ROW(A1:A6),1)) ,預覽結果為: B2={FALSE;FALSE;FALSE;TRUE;TRUE;TRUE} ,這樣就達到我們需要的判別功能了!

4. 以上就達到判定字串中各字元的屬性功能,但是儲存格顯示的依然是 FALSE ,因為上面所運用的都是 陣列 的觀念,但單一儲存格一般無法顯示陣列的結果,所以我們就透過函數 SUMPRODUCT 來統計 TRUE 的數目,那麼 B2=SUMPRODUCT(ISNUMBER(--MID(A2,ROW(A1:A6),1)))
但是明明有三個數字(TRUE),為何加總結果還是 0 ?原因很簡單,就跟第 3 步驟一樣問題, ISNUMBER 函數判別結果的表是方式是 TRUE、FALSE ,所以這是邏輯值不能做加總的,那麼就一樣把它強制轉換成數字吧,公式: B2=SUMPRODUCT(--ISNUMBER(--MID(A2,ROW(A1:A6),1))) ,這樣計算結果就是 3 個數字。

5. 前面四步驟就可以把數字的個數統計出來,但有一點需要注意的就是 ROW(A1:A6) ,這是代表要抓取 6 位字元來做判斷,但範例中有大於小於 6 位的字串,那麼我們就把公式改為讓它可以自動判定字串長度(函數LEN),所以這邊公式就改成: ROW(INDIRECT("A1:A"&LEN(A2))),以上達到的效果就是下圖:(B欄為A欄字串中數字的個數)



6. 到這一步只要搭配 RIGHT、MID、LEN 就可以達到擷取文字、數字字串的能力了,相反的若數字在左,文字在右的排列,就是用 LEFT、MID、LEN 來擷取!

字串擷取就用 MID、LEN ,所以就等於 =MID( 字串 , 從左邊第1個字開始 , 抓字串長度-數字位數 )

B2=MID(A2,1,LEN(A2)-SUMPRODUCT(--ISNUMBER(--MID(A2,ROW(INDIRECT("A1:A"&LEN(A2))),1))))

而數字部分用RIGHT,所以等於 =RIGHT( 字串 , 數字位數 )

C2=RIGHT(A2,SUMPRODUCT(--ISNUMBER(--MID(A2,ROW(INDIRECT("A1:A"&LEN(A2))),1))))
2015-10-03 2:22 am
我想SET OVERTIME TIME STAR
-MON -18:00 ONE HRS HKD40 TO SAT 2:30 PM ONE HOUR HKD50-
2007-12-07 1:10 am
你首先將它們剪下, 貼上記事本(附屬應用程式)上, 用"Tab"分開它們, 再剪下貼回excel就可以了.


收錄日期: 2021-04-13 14:40:22
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20071206000051KK01868

檢視 Wayback Machine 備份