麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 核對兩個儲存格的資料 [打印本頁]

作者: seci 時間: 2011-3-5 10:38 標題: 核對兩個儲存格的資料

我想寫一組公式核對兩個儲存格的資料, 例如:

                  A                                                 B
1                組1                                           組2
2    GREEN SHIRT, 100件       STYLE# 12-TB-1028, 100件

我是想核對 A2 和 B2 儲存格內的字同樣有100PCS,
因資料是很大量和是從軟件汇出的, 所以很不方便把"件"的數目分開到另一儲存格.

希望各位高手幫忙一下,
THANKS. ^^

作者: gong 時間: 2011-3-5 11:14

1.未說明如何比對,是同列做比對嗎?,還是同欄做比對,還是...
2.結果要如何呈現?
3.用篩選可以嗎?
4.將"件"用編輯取代掉不就沒有"件"的干擾

作者: seci 時間: 2011-3-5 11:51

多謝你的回答, 不好意思沒有加以說明,

實際的情況是這樣的 :
                  欄  A                                              欄  B                                              欄  C
列1       Tom Co Ltd                                     Mary Co Ltd

列2    GREEN SHIRT, 100PCS          STYLE# 12-TB-1028, 109PCS
列3    RED SHIRT, 450PCS             STYLE# 14-TB-1089, 100PCS
列4    BLUE SHIRT, 109PCS             STYLE# 99-WB-987, 450PCS
列5    YELLOW SHIRT, 1589PCS       STYLE# 36-ME-1178, 13PCS
列6    BLACK SHIRT, 13PCS          STYLE# 66-GB-1330, 1589PCS
列7             "                                                             "
列8             "                                                             "
列200       "                                                             "

因兩個欄的資料內容是不同, 只有最後的PCS才會有機會一樣, 我就是想核對欄A 和欄B 的PCS數目,
比如說: 2A有100PCS 跟 3B的 PCS 數目是一樣就出給果在C2
或有沒有其他方法可以核對兩欄內最尾的PCS數目結果,
非常感謝..你的忙幫, 問題可能太長~!!

作者: gong 時間: 2011-3-5 12:12

c2
{="B"&MIN(IF(ISNUMBER(FIND(MID($A2,FIND(",",$A2)+2,10),$B$1:$B$6)),ROW($1:$6)))}

作者: seci 時間: 2011-3-5 12:30

真是超級THANK YOU ~MR. GONG
提供了這公式,
但我剛試了結果全是 B0?

作者: gong 時間: 2011-3-5 12:37

本帖最後由 gong 於 2011-3-5 16:29 編輯

{}是陣列公式
要shift+ctrl+enter
三鍵一起按自動產生
不是去掉{}直接貼裡面公式

作者: seci 時間: 2011-3-5 12:58

麻煩幫我再看一下圖,
我已經按了 SHIFT + CTRL + ENTER
但只有第二行是行的~!
THANKS

作者: gong 時間: 2011-3-5 13:08

上傳檔案吧
應該是a2的100pcs後面還有不明的空格,先自己刪一刪看看
a4:a6一樣

作者: seci 時間: 2011-3-5 13:40

完全搞定..., THANKS A LOT

excel 高手

作者: freeffly 時間: 2011-3-6 20:21

回復 8# gong

大大可以問一下陣列的思考邏輯要朝哪個方向
看大大寫的函數大概可以理解
不過將函數拆開看又感覺跟函數的作用不太一樣
MID($A5,FIND(",",$A5)+2,10) 結果是含pcs
FIND(MID($A5,FIND(",",$A5)+2,10),$B$1:$B$6) $B$1:$B$6這個是只有陣列才這樣做吧
ISNUMBER(FIND(MID($A5,FIND(",",$A5)+2,10),$B$1:$B$6)) 感覺到這裡就有點腦筋打結
因為我以為()裡面的結果不是數字
如果以我現在所學會想不到用陣列只會用輔助欄位拆

作者: gong 時間: 2011-3-6 21:15

MID($A5,FIND(",",$A5)+2,10)>>取得100PCS
FIND(MID($A5,FIND(",",$A5)+2,10),$B$1:$B$6)
>>在$B$1:$B$6中取得有"100PCS"的所在位置,可能是{#VALUE!;#VALUE!;22;#VALUE!;#VALUE!;#VALUE!}

= ISNUMBER(FIND(MID($A5,FIND(",",$A5)+2,10),$B$1:$B$6))
={FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}

IF(ISNUMBER(FIND(MID($A2,FIND(",",$A2)+2,10),$B$1:$B$6)),ROW($1:$6)
={FALSE;FALSE;3;FALSE;FALSE;FALSE} >>第3個位置為真

{="B"&MIN(IF(ISNUMBER(FIND(MID($A2,FIND(",",$A2)+2,10),$B$1:$B$6)),ROW($1:$6)))}
與B組合後即為B3這個位置有"100PCS"這個相同的

為什麼要加MIN是因為可能有很多位置有相同的"100PCS"只取最先出現的位置

作者: seci 時間: 2011-3-8 17:49

Mr Gong... 你好^^
上次請教你的excel , 在使用上出了點小問題,
想再請教你, 如圖中:
在我的資料中會出兩個", "號的
在那個excel公式內多少知道是靠確認","之後的資料,
如果我想同時檢查兩個","後的資料是否相同,
應該要怎樣?
再次麻煩你了........!

作者: Hsieh 時間: 2011-3-8 18:52

本帖最後由 Hsieh 於 2011-3-8 18:55 編輯

回復 12# seci
定義名稱
y=MAX(IF(MID(Sheet1!$A2,ROW(INDIRECT("A1:A"&LEN(Sheet1!$A2))),1)=",",ROW(INDIRECT("A1:A"&LEN(Sheet1!$A2))),""))

C2陣列公式
{="B"&MAX(IF(ISNUMBER(FIND(MID(A2,y+1,255),$B$2:$B$6)),ROW($A$2:$A$6),""))}
[attach]4948[/attach]

作者: seci 時間: 2011-3-9 13:41

Mr Hsieh, 多謝你的解答^^

作者: seci 時間: 2011-3-10 00:13

Mr Hsieh,
如圖中:
我試過將","後的兩組資料test過,
發覺光是一組相同也會顯示結果,
有沒有方法修改成,
一定要","後兩組資料一樣才顯示結果呢~!
萬分謝謝!

作者: Hsieh 時間: 2011-3-10 10:18

回復 15# seci
那你的資料的比對規則，到底是最後一組逗號分隔還是第一組以後的所有字串比對
請把你的資料作成EXCEL檔案後，壓縮上傳，解釋清楚你的比對規則

作者: seci 時間: 2011-3-11 19:51

Mr. Hsieh
附件是我實際工作要比對的資料,
如圖示 :
我要比對每格內最後兩組資料, 資料需要完全一樣.

Thank you very much~!

作者: ANGELA 時間: 2011-3-11 20:46

你要的是第二個逗號為分斷點,不是最後一個,改一下謝版主的公式
Y=LARGE(IF(MID(Sheet1!$A2,ROW(INDIRECT("A1:A"&LEN(Sheet1!$A2))),1)=",",ROW(INDIRECT("A1:A"&LEN(Sheet1!$A2))),""),2)
就是了

作者: Hsieh 時間: 2011-3-11 20:57

回復 17# seci

定義名稱x
=LEN(Sheet1!$A2)-LEN(SUBSTITUTE(Sheet1!$A2,",",""))-2
C2陣列公式
{="B"&MIN(IF(ISNUMBER(FIND(MID(A2,FIND(",",SUBSTITUTE(A2,",","",x))+3,255),$B$2:$B$4)),ROW($A$2:$A$4),""))}
[attach]4974[/attach]

作者: seci 時間: 2011-3-14 17:13

Thanks a lot !

作者: FAlonso 時間: 2011-3-15 18:49

本帖最後由 FAlonso 於 2011-3-15 18:52 編輯

回復 11# gong

依著gong的程式一步一步走
可是到了D11理應有數字,怎知沒有,不知問題出左何處?
[attach]5009[/attach]

作者: Hsieh 時間: 2011-3-15 19:27

回復 21# FAlonso

陣列公式的思考邏輯，是傳回一個序列的資料集
FIND函數在B欄資料串列中尋找
他傳回的是
FIND(搜尋字串,B2)
FIND(搜尋字串,B3)
FIND(搜尋字串,B4)
FIND(搜尋字串,B5)
.
.
.
這樣一連串的值
這樣在儲存格內顯示的值，會是該一連串的值的第一個值
但實際上FIND是對一個字串做搜尋，所以，只針對第一個儲存格字串做搜尋
必須利用IF及ISNUMBER才能傳回一個TRUE跟FALSE的陣列

作者: FAlonso 時間: 2011-3-17 15:20

回復 10# freeffly
看看我的附檔,有詳細解釋
另樓主說遇到B0問題,要注意如果不用ctrl+alt+Enter,是會給回0的數值,參考附檔C31,不用複合鍵試試看
[attach]5026[/attach]

作者: freeffly 時間: 2011-3-18 12:26

回復 23# FAlonso

謝謝
我會好好研讀

作者: Andy2483 時間: 2023-5-16 11:36

謝謝論壇,謝謝各位前輩
後學藉此帖練習陣列與字典,徐息方案如下,請各位前輩指教

執行前:
[attach]36366[/attach]

執行結果:
[attach]36367[/attach]

Option Explicit
Sub TEST() '↑
Dim Brr, Y, i&, T$, X&
'↑宣告變數
Set Y = CreateObject("Scripting.Dictionary")
'↑令Y變數是字典
Brr = [A1].CurrentRegion
'↑令Brr變數是二維陣列,以儲存格值帶入陣列中
For i = 2 To UBound(Brr)
'↑設順迴圈
T = Brr(i, 2)
'↑令T變數是Brr陣列第2欄的字串值
If Right(T, 4) Like "#PCS" Then
'↑如果T變數的右側4字符合條件?
   X = StrReverse(Val("1" & Mid(StrReverse(T), 4))) \ 10
   '↑令X變數是將T變數反轉化為數值再反轉回來的數值
   Y(X) = "B" & i
   '↑令以X變數當key,item是 "B"字母接i變數,納入Y字典裡
End If
Next
For i = 2 To UBound(Brr)
'↑設順迴圈
T = Brr(i, 1)
'↑令T變數是Brr陣列第1欄的字串值
If Right(T, 4) Like "#PCS" Then
'↑如果T變數的右側4字符合條件?
   X = StrReverse(Val("1" & Mid(StrReverse(T), 4))) \ 10
   '↑令X變數是將T變數反轉化為數值再反轉回來的數值
   Brr(i - 1, 1) = "PCS數同_" & Y(X)
   '↑令Brr陣列第1欄值是以X變數查Y字典得到item值
   Else
   Brr(i - 1, 1) = "無"
   '↑否則令Brr陣列第1欄值是 "無"
End If
Next
[C2].Resize(UBound(Brr) - 1, 1) = Brr
'↑令Brr陣列第1欄值從[C2]儲存格開始倒入
Set Y = Nothing: Erase Brr
'↑令釋放變數
End Sub

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)