請問各位前輩網路抓下的資料有空格，如何處理比對

lifetw

請問各位前輩網路抓下的資料存入excel檔案(工作表名稱02)，存入B欄單一儲存格，但數字間有空格，如何處理比對疑問?
  A          B
0707   20 45 10 15 18 88 65 89 90 05
0708   20 30 21 56 44 88 65 89 90 05
.
.
.
.
共有約十萬筆資料
用另一工作表(名稱01)各儲存格內的數字有4~5碼如4, 13, 43, 59或10, 43, 59, 69, 72
(數字由小至大有排序，但","逗號與數字間有空格，比如, 72)也有約十萬筆資料
，主要是用工作表(名稱01)來比對工作表02中B欄位的數字，(B欄位的數字需排序或如何處理嗎?)
如符合則計算儲存格數目(用COUNTIF?)與每個符合之間的間隔數，
並抓到另一工作表，分別寫入儲存格，目地是統計工作表01各儲存格內的數字出現幾次(會用FREQUENCY函數繪製分布圖)
，統計每個符合之間間隔數的詳細分布圖
初學乍練，還請各位先進指教~~謝謝!!

lifetw

回復 12# ML089
ML版主大大您好
解說已經非常詳細，我會仔細解讀試作

非常謝謝撥冗幫忙，感謝!!!

ML089

回復 11# lifetw

=COUNT(0/(MMULT(--ISNUMBER(FIND(B1:E1,Sheet2!B$1:B$50)),{1;1;1;1})=4))

公式計算步驟
步驟1，以B1:E1查詢Sheet2!B$1:B$50是否存在，產生4行*50列陣列資料
FIND(B1:E1,Sheet2!B$1:B$50)

步驟2，有查到的資料轉為1，沒有查到的資料轉為0
--ISNUMBER(FIND(B1:E1,Sheet2!B$1:B$50))

步驟3，將4行*50列陣列資料使用MMULT轉為1行*50列資料
MMULT(--ISNUMBER(FIND(B1:E1,Sheet2!B$1:B$50)),{1;1;1;1})

步驟4，1行*50列資料若數值為4時表示4個全部有查到。
MMULT(--ISNUMBER(FIND(B1:E1,Sheet2!B$1:B$50)),{1;1;1;1})=4

步驟5，0/TRUE=0， 0/FALSE=#DIV/0!
0/(MMULT(--ISNUMBER(FIND(B1:E1,Sheet2!B$1:B$50)),{1;1;1;1})=4)

步驟6， COUNT 利用0/(...)計算出有多少TRUE的數量
=COUNT(0/(MMULT(--ISNUMBER(FIND(B1:E1,Sheet2!B$1:B$50)),{1;1;1;1})=4))

大致解釋，公式說明自行查詢HELP

lifetw

回復 10# ML089
ML版主大大您好
感謝幫忙，
原本資料分散多欄，所以如果copy 至同一欄，就4欄待處理，謝謝!!

=COUNT(0/(MMULT(--ISNUMBER(FIND(B1:E1,Sheet2!B$1:B$50)),{1;1;1;1})=4))
這部分能請ML版主大大開示一下嗎?  COUNT計算範圍中含有數字的儲存格總數，COUNT之後是如何比對相同呢?

看了各函數說明還是很模糊...特別是{1;1;1;1}=4，不知如何查起!!

連結是微軟官網說明，應該安全
FIND 函數
ISNUMBER 函數
MMULT 函數
COUNT 函數

非常謝謝幫忙，感謝!!

ML089

回復 9# lifetw

G1 =TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",99)),COLUMN(F1)*99-98,99))
若要分解的是F欄
修改為
G1 =TRIM(MID(SUBSTITUTE($F1,",",REPT(" ",99)),COLUMN(A1)*99-98,99))

COLUMN(A1) 表示取第一個數字
當公式右拉時公式中的COLUMN(A1)會變成COLUMN(B1)，表示取第二個數字，再又拉會變成COLUMN(C1)，表示取第參個數字

lifetw

回復 8# lifetw
啊!!不是騙積分，別別請誤會
><||
真的忘了附檔

lifetw

回復 7# ML089
ML版主大大您好
感謝幫忙，
在原範例檔ok，只是我給的範例檔跟原檔好像不太合
在多欄位資料輸入(=TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",99)),COLUMN(A1)*99-98,99))沒反應，(如附圖)
不知是否那裏要改動，附上範例檔test2

另外從最新資料往後比對都符合的四個號碼位置，能將間隔數顯示出來嗎?
比如104010754四個號碼符合，往後比對104010740又符合
則得出間隔數14

非常謝謝幫忙，感謝!!

ML089

回復 6# lifetw

下載 (150.77 KB)

2015-7-18 00:21

lifetw

回復 5# ML089
ML版大您好
非常感謝您的幫忙解惑
只是我尚未能下載檔案
只好等升級才能下載檔案
非常謝謝幫忙!!

ML089

回復 4# lifetw

沒有做出來的範例可以參考

只能猜想，請看看對不對
處理比對.rar (5.07 KB)

下載次數: 5

2015-7-16 22:02