標題:
[發問]
請問如何比對兩表格資料後,帶出相符資料
[打印本頁]
作者:
jackson7015
時間:
2014-8-13 16:38
標題:
請問如何比對兩表格資料後,帶出相符資料
請問如何參照(表一)的數據(變動值),去比對(表二)資料,且為支票別
有相符號碼(編號)的資料則帶出到(表三)
其中表一的數據有兩頁,中間有分段
表二則是中間會有空白欄位
嘗試使用VLOOKUP去比對,但是試不出來,再請各位前輩們幫忙
感激不盡~
[attach]18894[/attach]
作者:
p212
時間:
2014-8-14 10:14
回復
1#
jackson7015
請參考
作者:
Hsieh
時間:
2014-8-14 15:04
回復
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
時間:
2014-8-15 08:50
本帖最後由 jackson7015 於 2014-8-15 08:53 編輯
回復
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
時間:
2014-9-16 09:36
回復
3#
Hsieh
不好意思,請問Hsieh版大
因為自設格式有不同,所以公式的替代好像有問題
可否麻煩在幫我看看哪裡需要更正的嗎
因為在運算ROW的部份我不太了解是怎麼運作的
照本宣料的改過去有問題
在請板大幫忙修正
感謝~
[attach]19155[/attach]
作者:
ML089
時間:
2014-9-16 11:26
本帖最後由 ML089 於 2014-9-16 17:44 編輯
回復
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
時間:
2014-9-16 17:03
回復
6#
ML089
感謝ML089版大的回覆
測試後發現幾個問題點
MAX在運算部分只會根據前5格分析,如果差距5格會出現判斷錯誤
如果號碼有重複的話,只能運算最先數值;有時候會有2筆或以上同號碼數值(金額/日期不同)
不曉得是否如何修正這問題?
使用Hsieh版大的陣列公式,可以運算相同號碼的部分
但是自己沒有修改成功
作者:
ML089
時間:
2014-9-16 17:43
回復
7#
jackson7015
修改
工作表3
H7 =IF(IFERROR(VLOOKUP(D7,工作表2!F:H,3,)="支票",FALSE),MAX(工作表3!H
$
1:H6)+1,"")
作者:
ML089
時間:
2014-9-16 17:52
回復
7#
jackson7015
如果號碼有重複的話,只能運算最先數值;有時候會有2筆或以上同號碼數值(金額/日期不同)
不曉得是否如何修正這問題?
是指工作表三會有2筆或以上同號碼數值(金額/日期不同),那工作表二開支票時也會分兩筆或多筆嗎? 或合成單筆
作者:
ML089
時間:
2014-9-16 18:13
回復
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作業會越來越慢
工作表二應該要增加 日期,這樣才能以 編號+日期 作為對應。
作者:
jackson7015
時間:
2014-9-17 08:11
回復
10#
ML089
抱歉沒有說明清楚
工作表二的部分是客戶資料,金額為預定金額
工作表三為客戶繳費紀錄,金額和日期不固定;亦有兩筆以上的可能
工作表一則是要運算出有客戶資料的繳費紀錄
因工作表三的資料不會超過1百筆(實際表單即是5#附件的格式),且運算出來的結果不會超過50筆
所以應該不會有運算數量過多的問題
感謝ML089版主的耐心指導
作者:
ML089
時間:
2014-9-17 09:14
回復
11#
jackson7015
回復 10# ML089
抱歉沒有說明清楚
工作表二的部分是客戶資料,金額為預定金額
工作表三為客戶繳費紀錄,金額和日期不固定;亦有兩筆以上的可能
工作表一則是要運算出有客戶資料的繳費紀錄
因工作表三的資料不會超過1百筆(實際表單即是5#附件的格式),且運算出來的結果不會超過50筆
所以應該不會有運算數量過多的問題
如果照你的上面所敘述的,我的公式應該沒有問題
工作表三是客戶繳費紀錄(可能有多筆)
工作表二是客戶資料,金額為預定金額,
(應該是單筆)
工作表三去查工作表二,票別為"支票"時,工作表一顯示工作表三資料
是否將你認為有問題的地方,模擬資料及正確答案,將檔案上傳來看看
作者:
jackson7015
時間:
2014-9-17 09:35
回復
12#
ML089
ML089版大非常抱歉
重新書寫公式後,測試重複項目已經可以正常運作了
之前錯誤的地方可能是沒有某部分公式沒有填寫完成
公式部分自己在慢慢了解
再次感謝各位前輩們的指教
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)