iT邦幫忙

0

excel2016 資料比對與差異的公式

a810911366 6 月前818 瀏覽

請問我舉例 四行 A B C D

A等於統一發票號碼
B等於其金額
C等於統一發票號碼
D等於其金額

我想比對 AB的號碼跟金額 CD有沒有或是金額對不對

這樣應該如何使用工具或是公式

麻煩各位教我

slime iT邦大師 1 級 ‧ 6 月前 檢舉
大量的話可以考慮 LibreOffice Base 或 MS Access

建兩個資料表, 第一個資料表對第二個資料表使用 outer join .

2 個回答

1
做工仔人!
iT邦高手 1 級 ‧ 6 月前

在E欄下:
=IF(ISERROR(VLOOKUP(C1,$A:$B,2,0)),'找不到',VLOOKUP(C1,$A:$B,2,0))
在F欄下:=IF(D1=E1,1,0)
說明:
VLOOKUP(比較值,被查詢比較的資料區間,傳回資料區間的第幾個欄位內容,比較精度)
比較值: C1 (C欄的發票號碼)
被查詢比較的資料區間: $A:$B (A,B二欄的全部資料)
傳回資料區間的第幾個欄位內容 :2 (傳回B欄的內容)
比較精度 : 0 (要完全符合)
ISERROR: 錯誤處理,主要在比對不到資料時不顯示 #N/A

看更多先前的回應...收起先前的回應...
做工仔人! iT邦高手 1 級 ‧ 6 月前 檢舉

請記得適時修改C1為C2,C3,C4.....
$A:$B , 2,0 =>不必修改
=IF(D1=E1,1,0) 也要改D1,E1 為D2,E2.... =>這個的目的是要快速找到金額一致的資料,對F欄做篩選或排序就可以快速找到資料.

a810911366 iT邦新手 4 級 ‧ 6 月前 檢舉

你好 目前這方法有使用
只是太花時間 找到之後還是要人工比對差異性
如果資料上千 差異資料有幾百就有點花時間

目前希望的是AB 能夠連結再一起 CD連結再一起
最主要的目的希望
A有的統一編號 C沒有
就標記出來是少了哪個

請問有這方法嗎 感謝你

做工仔人! iT邦高手 1 級 ‧ 6 月前 檢舉

就二邊比啊 !!
增加 G/H欄.
G欄: =IF(ISERROR(VLOOKUP(A1,$C:$D,2,0)),'找不到',VLOOKUP(A1,$C:$D,2,0))
H欄就是:=IF(B1=G1,1,0)
這樣就可以二邊互比.
E欄中為"找不到" 的部份就是C欄發票號碼不存在於A欄中.
F欄中為"0"者就是找不到或金額與D欄不同的資料.
G欄中為"找不到" 的部份就是A欄發票號碼不存在於C欄中.
H欄中為"0"者就是找不到或金額與B欄不同的資料.

做工仔人! iT邦高手 1 級 ‧ 6 月前 檢舉

另一定方式:
E欄: =CONCATENATE(A1,B1)
E欄: =CONCATENATE(C1,D1)
再將E欄與F欄做VLOOKUP 比對. 也是要二邊互比.
CONCATENATE : 將兩個或多個欄位資料,以"文字字串"方式合併成一個字串。

1
海綿寶寶
iT邦超人 1 級 ‧ 6 月前
1.把 CD 接在 AB 後面
2.用樞紐分析表, 收工

http://ithelp.ithome.com.tw/upload/images/20161104/20001787cSRYNhGeYt.png

選我正解
/images/emoticon/emoticon13.gif

我要發表回答

立即登入回答