[發問] 請問如何比對兩表格資料後，帶出相符資料

jackson7015

請問如何參照(表一)的數據(變動值)，去比對(表二)資料，且為支票別
有相符號碼(編號)的資料則帶出到(表三)
其中表一的數據有兩頁，中間有分段
表二則是中間會有空白欄位

嘗試使用VLOOKUP去比對，但是試不出來，再請各位前輩們幫忙
感激不盡~

累計支票.rar (8.87 KB)

下載次數: 21

2014-8-13 16:38

p212

回復 1# jackson7015
請參考

Hsieh

回復 1# jackson7015

A18陣列公式
=IF(ROW(A1)>SUMPRODUCT(ISNUMBER(MATCH($B$4:$B$12&"支票",$G$3:$G$36&$I$3:$I$36,0))*1),"",INDEX($A$1:$E$12,SMALL(IF(ISNUMBER(MATCH($B$4:$B$12&"支票",$G$3:$G$36&$I$3:$I$36,0)),ROW($B$4:$B$12),""),ROW(A1)),1))
B18陣列公式
=IF(ROW(A1)>SUMPRODUCT(ISNUMBER(MATCH($B$4:$B$12&"支票",$G$3:$G$36&$I$3:$I$36,0))*1),"",INDEX($A$1:$E$12,SMALL(IF(ISNUMBER(MATCH($B$4:$B$12&"支票",$G$3:$G$36&$I$3:$I$36,0)),ROW($B$4:$B$12),""),ROW(A1)),2))
C18陣列公式
=IF(ROW(A1)>SUMPRODUCT(ISNUMBER(MATCH($B$4:$B$12&"支票",$G$3:$G$36&$I$3:$I$36,0))*1),"",INDEX($A$1:$E$12,SMALL(IF(ISNUMBER(MATCH($B$4:$B$12&"支票",$G$3:$G$36&$I$3:$I$36,0)),ROW($B$4:$B$12),""),ROW(A1)),5))

jackson7015

回復 2# p212
感謝p212大大的協助，雖然要以日期做搜尋標的，無法完全搜尋
不過也讓小弟有另外的思考方向，嘗試變更為票別方式搜尋
   
回復 3# Hsieh
感謝版主大大的幫忙，公式計算正常；但是還需要消化一下內容，尤其是ROW(A1)的計算方式有點特別
公式有點攏長，且有部分的儲存格是合併儲存格，無法使用矩陣公式，所以把日期和金額的搜尋方式變更了一下
A18=IF(ISERROR(VLOOKUP($B18,IF({1,0},B4:B12,A4:A12),2,0)),"",VLOOKUP($B18,IF({1,0},B4:B12,A4:A12),2,0))
C18=IF(ISERROR(VLOOKUP($B18,$B$4:$E$12,4,0)),"",VLOOKUP($B18,$B$4:$E$12,4,0))
雖然上式公式只能使用特定表格，不過簡短一點自己比較好檢查

感謝大大們不吝指教~:)

jackson7015

回復 3# Hsieh
不好意思，請問Hsieh版大
因為自設格式有不同，所以公式的替代好像有問題
可否麻煩在幫我看看哪裡需要更正的嗎

因為在運算ROW的部份我不太了解是怎麼運作的
照本宣料的改過去有問題
在請板大幫忙修正
感謝~
累計支票.rar (19.54 KB)

下載次數: 8

2014-9-16 09:36

ML089

回復 5# jackson7015


工作表1
B4 =IFERROR(INDEX(工作表3!C:C,MATCH(ROW()-3,工作表3!$H:$H,)),"")
C4 =IFERROR(INDEX(工作表3!D:D,MATCH(ROW()-3,工作表3!$H:$H,)),"")
D4 =IFERROR(INDEX(工作表3!G:G,MATCH(ROW()-3,工作表3!$H:$H,)),"")


工作表3
H7 =IF(IFERROR(VLOOKUP(D7,工作表2!F:H,3,)="支票",FALSE),MAX(工作表3!H$1:H6)+1,"")

以上各公式向下複製

jackson7015

回復 6# ML089
感謝ML089版大的回覆

測試後發現幾個問題點
MAX在運算部分只會根據前5格分析，如果差距5格會出現判斷錯誤
如果號碼有重複的話，只能運算最先數值；有時候會有2筆或以上同號碼數值(金額/日期不同)
不曉得是否如何修正這問題?

使用Hsieh版大的陣列公式，可以運算相同號碼的部分
但是自己沒有修改成功

ML089

回復 7# jackson7015

修改
工作表3
H7 =IF(IFERROR(VLOOKUP(D7,工作表2!F:H,3,)="支票",FALSE),MAX(工作表3!H$1:H6)+1,"")

ML089

回復 7# jackson7015

如果號碼有重複的話，只能運算最先數值；有時候會有2筆或以上同號碼數值(金額/日期不同)
不曉得是否如何修正這問題?

   
是指工作表三會有2筆或以上同號碼數值(金額/日期不同)，那工作表二開支票時也會分兩筆或多筆嗎? 或合成單筆

ML089

回復 7# jackson7015

日期        號碼        (不含稅)        稅         (含稅)
103/9/12        1001219        9,524-        476-        10,000- 明細表一
103/3/25        1001219        9,524-        476-        10,000- 明細表二

工作表三 有2個明細表，各有 1001219 編號，工作表二中 1001219 編號是要對應哪一個?
這是不合理對應方式，讓EXCEL作業變得複雜，資料一多EXCEL作業會越來越慢

工作表二應該要增加 日期，這樣才能以 編號+日期 作為對應。