iT邦幫忙

0

excel比對多欄資料問題

excel比對資料
msmplayv121068 2018-06-29 23:27:12 ‧ 18335 瀏覽

  • 分享至 

  • xImage

https://ithelp.ithome.com.tw/upload/images/20180629/20109231bpSkuhBqyj.png

https://ithelp.ithome.com.tw/upload/images/20180630/20109231X4q5CW4JSZ.png

請問AA欄該如何設定公式
欲比對【比對資料】活頁的P欄、U欄、Z欄與【訂單來源】活頁的AP欄、AY欄、BI欄
三個欄位資料完全相符時，帶出【訂單來源】活頁的A欄訂單NO.到AA欄

如果比對無不符資料時，則帶出「查無對應資料」字樣

PS.資料共約3千多~1萬多筆，故此為範例資料
另AA欄帶出排序希望為尾字編號由小至大排序

以上跪求大師幫忙~~~~~~~~~~

測試檔

看更多先前的討論...收起先前的討論...
paicheng0111 iT邦大師 5 級 ‧ 2018-06-30 08:53:25 檢舉
【訂單來源】活頁 長得怎樣？
msmplayv121068 iT邦新手 4 級 ‧ 2018-06-30 11:02:47 檢舉
@pcw 訂單來源活頁在測試檔內有ㄛ
paicheng0111 iT邦大師 5 級 ‧ 2018-06-30 16:48:13 檢舉
sorry, 我的mac沒裝office
我質疑 【訂單來源】活頁 的 A欄中 有 尾字編號
msmplayv121068 iT邦新手 4 級 ‧ 2018-06-30 18:03:55 檢舉
@pcw 訂單來源的畫面已上傳，另訂單NO.的編號廠商提供即有，未人工調整過~~~
登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

3 個回答

2
paicheng0111
iT邦大師 5 級 ‧ 2018-07-01 13:50:25
最佳解答

1 - 在【訂單來源】活頁 建立輔助欄位

  1. 在某個未使用的欄位(例如:XX)建立輔助欄位，XX2寫上=AP2&"_"&AY2&"_"&BI2。
  2. 在XX2用填滿控點往下拉。

2 - 在【比對資料】活頁 建立輔助欄位1與輔助欄位2

  1. 在某個未使用的欄位(例如:XX)建立輔助欄位1，XX2寫上=P2&"_"&U2&"_"&Z2。
  2. 在XX2用填滿控點往下拉。
  3. 在某個未使用的欄位(例如:XY)建立輔助欄位2，XY2寫上=COUNTIF($XX$2:$XX2,XX2)。
  4. 在XY2用填滿控點往下拉。

3 - 在【比對資料】活頁 建立陣列公式

  1. 在AA2寫上=IFERROR(INDEX(訂單來源!$A:$A,LARGE(IF(訂單來源!XX:XX=XX2,ROW(訂單來源!XX:XX),""),XY2)),"查無對應資料")。
  2. 按下Ctrl+Shift+Enter。

msmplayv121068 iT邦新手 4 級 ‧ 2018-07-01 21:40:14 檢舉

@pcw 你真的好厲害喔!!!又解決我的一個大問題了~~~~非常感謝你喔!!!

richardsuma iT邦大師 1 級 ‧ 2018-07-01 21:55:55 檢舉

才想到，你已經做完了，漂亮。
只不過有點疑問，不知道 相同SP與品名，但出現不同帳款，這樣是不是會有問題?

paicheng0111 iT邦大師 5 級 ‧ 2018-07-01 22:26:11 檢舉

@msmplayv121068
這裡的高手多的是，我只是比較有空而已。

@richardsuma
建立輔助欄位的目的(除了輔助欄位2)，就是把SP_品名_帳款當作key。
所以應該不會有問題。

登入發表回應
0
richardsuma
iT邦大師 1 級 ‧ 2018-06-30 00:12:15

參考看看:
https://ithelp.ithome.com.tw/questions/10164231

抱歉，你資料的三個欄位值有重複，無法達到你想要的答案?

msmplayv121068 iT邦新手 4 級 ‧ 2018-06-30 02:00:14 檢舉

@richardsuma 因為三個欄位資料都會有重複，唯一值為【訂單來源】活頁的A欄訂單NO.不會重複，所以目前是使用人工手動篩選的方式去比對後，再手動複製貼上對應的訂單NO.，非常耗時~~~~

登入發表回應
0
小魚
iT邦大師 1 級 ‧ 2018-06-30 00:52:23

如果P1比對AP1, P2比對AP2 可以使用If
如果P1比對所有AP裡面有沒有，可以考慮使用VLOOKUP

看更多先前的回應...收起先前的回應...
msmplayv121068 iT邦新手 4 級 ‧ 2018-06-30 02:03:06 檢舉

@小魚 因為三個欄位資料由上至下都會有重複，所以可能沒辦法用Vlookup或index+match來比對抓取資料，因為醬都只會抓到由上至下的第一筆訂單NO.~~~

蟹老闆 iT邦大師 1 級 ‧ 2018-06-30 03:21:58 檢舉

https://ithelp.ithome.com.tw/upload/images/20180630/20006149MoJiBnhsPV.jpg
msmplayv121068
一定是哪兒搞錯了,當手工作業時條件一定不止這三個,應該還有其它沒說出來的條件,又或者應該從訂單來源查找比對資料。

小魚 iT邦大師 1 級 ‧ 2018-06-30 07:39:43 檢舉

這樣的資料設計不是很好...

msmplayv121068 iT邦新手 4 級 ‧ 2018-06-30 11:16:20 檢舉

@蟹老闆 因為公司系統很爛，只能導出【比對資料】的excel檔，裡面的資料不會有訂單NO.，而且是所有資料導出無法篩選，然後訂單來源是廠商提供當月需要做帳的檔案，而我需要做的事，就是從2份檔案內除了要比對出相同資料的單號，還要知道廠商這次的檔案有沒有漏掉哪些訂單沒提供給我。所以~~~~~如果有更好的方法可以提供，我就真的是萬分感激不盡了！

msmplayv121068 iT邦新手 4 級 ‧ 2018-06-30 11:16:51 檢舉

@小魚 +1

小魚 iT邦大師 1 級 ‧ 2018-06-30 15:04:08 檢舉

我想問一下，訂單編號同樣的，出現了四個同樣品名，代表那一次買了四個同樣的東西嗎?

msmplayv121068 iT邦新手 4 級 ‧ 2018-06-30 18:05:28 檢舉

@小魚 沒錯!!分成四張單，所以訂單NO.才會有-1、-2、-3、-4以此類推.....

小魚 iT邦大師 1 級 ‧ 2018-06-30 19:39:24 檢舉

這根本判斷不出來吧，
如果VBA應該可以。

paicheng0111 iT邦大師 5 級 ‧ 2018-06-30 19:55:58 檢舉

@小魚
我覺得用陣列公式可以做到，但要加輔助數列。
可惜手邊只有沒裝office的mac。

msmplayv121068 iT邦新手 4 級 ‧ 2018-06-30 22:21:36 檢舉

@pcw 網路爬文過陣列確實可以做到，但我找不到同時比對三個條件完全符合帶出所需資料的文，所以......

paicheng0111 iT邦大師 5 級 ‧ 2018-06-30 22:56:46 檢舉

@msmplayv121068
我認為可以把三個欄位的值用文字串接成一個輔助欄位。
另外何不把網路爬文的資料也分享一下，讓大家一起集思廣益。

登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1064 組
團體組數
40 組
累計文章數
22206 篇
完賽人數
600 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙