iT邦幫忙

0

想請教使用excel比對兩張表格的問題

有兩張表格分別是 "主表" 及 "調查表",
https://ithelp.ithome.com.tw/upload/images/20180318/20107039URwyyvduux.png
主表內有兩個欄位 1.公司 2.賣的商品,每家公司可能會賣一樣的商品
調查表也是同樣的兩個欄位,我想比對兩個表格,
如果調查表中甲公司今年多賣一個商品F,則在第三欄"動作"會顯示"新增至主表"
我就可以手動把這個商品F加入主表中的甲公司了
可是想了半天想不出來第三欄"動作"那欄的公司要如何寫才好

請大家不吝賜教 感謝~!

pcw iT邦研究生 5 級 ‧ 2018-03-19 10:14:05 檢舉
><"
3
pcw
iT邦研究生 5 級 ‧ 2018-03-19 10:48:15
最佳解答

方法一、表格加上陣列公式:

  1. 先將主表與調查表,分別套用「格式化為表格」功能。
  2. 再於F2寫下=IF(SUM((主表[公司]=[@公司])*(主表[商品]=[@商品])*1),"無異動","新增至主表"),按下CTRL+SHIFT+ENTER

方法二、輔助欄位加上MATCH函數:

  1. 在主表加上輔助欄位公司+商品,公式為=A2&"-"&B2
  2. 在G2儲存格寫下=IF(ISNUMBER(MATCH(E2&"-"&F2,$C$2:$C$8,0)),"無異動","新增至主表")按下ENTER即可。

方法三、表格加上COUNTIFS公式:

  1. 先將主表與調查表,分別套用「格式化為表格」功能。
  2. 再於F2寫下=IF(COUNTIFS(主表[公司],[@公司],主表[商品],[@商品]),"無異動","新增至主表"),按下ENTER即可。

看更多先前的回應...收起先前的回應...

感謝你 看得懂方法2 方法1有點難看不懂

滿想知道方法1是怎麼達成的 可以大概解釋一下嗎 感恩~!!

pcw iT邦研究生 5 級 ‧ 2018-03-19 23:02:38 檢舉

方法一看不懂,是步驟一還是步驟二不懂?
為了爭取最佳解答,我加送一個方法三,捨棄陣列公式,改用COUNTIFS函數。看看能不能讓你懂。

其實自從EXCEL新增這些xIFS函數之後,陣列公式已經少用了。

步驟2不太了解

SUM的那一段

pcw iT邦研究生 5 級 ‧ 2018-03-21 00:24:33 檢舉

SUM((主表[公司]=[@公司])*(主表[商品]=[@商品])*1)是一組陣列公式。基本上SUM裡面是三組數相乘。我們以F2儲存格為例。

  1. 第一組(主表[公司]=[@公司]),等號左邊是一個數列,代表主表的公司欄位,數列為{甲, 甲, 甲, 乙, 乙, 乙, 乙}。等號右邊是一個數,代表調查表中該列的公司欄位,也就是D2儲存格的值,。這一組是在判斷等號左邊數列的每一個元素是否等於等號右邊的值,等於就傳回true,否則就是false。比對的結果為{T, T, T, F, F, F, F}
  2. 第二組(主表[商品]=[@商品])同理,就是比對{A, B, D, A, B, F, G}是否等於E2儲存格的值A。比對結果為{T, F, F, T, F, F, F}
  3. 第三組為1。相當於一個元素都是1的數列{1, 1, 1, 1, 1, 1, 1}

把這三組數列的各自對應位置的元素相乘,就是{T, T, T, F, F, F, F} * {T, F, F, T, F, F, F} * {1, 1, 1, 1, 1, 1, 1}會等於{1, 0, 0, 0, 0, 0, 0}

再用SUM函數把該數列的各個元素相加,SUM({1, 0, 0, 0, 0, 0, 0}) 等於 1

以上。

感謝你的回覆 終於了解了,第一次看到這種用法大開眼界
不過還有一點像不明白不能只有前面兩組數相乘就好了嗎,為什麼還要乘以1?

pcw iT邦研究生 5 級 ‧ 2018-03-21 22:20:15 檢舉

當然可以。
乘以1是我的習慣。

嗯嗯 感謝你的回答

pcw iT邦研究生 5 級 ‧ 2018-03-22 13:29:49 檢舉

很高興能幫到你。

2
暐翰
iT邦大師 7 級 ‧ 2018-03-18 23:49:45

我這邊嘗試使用ISNUMBER + SEARCH + IF的方式來解決你的問題

結果圖:

Script:

=IF(
     AND((D5=A5),ISNUMBER(SEARCH(E5,B:B))),"無異動","新增至主表"
)

解析:

需要變更的地方,E列代表新的商品,B列代表舊的產品
所以把E加上指定行,去遍歷(B:B)比對是否有過這個值


寫到這裡我卡點了,問題點:

怎麼在使用Search發現有值的情況下,又取得它是第幾行資料
這樣才能比對公司一樣又包含商品

這邊我明天在想想怎麼解決...(應該用Indexof可以解決)
以上邏輯先提供給你

解決方式:

新增一個"商品編號列",裡面值由A列跟B列字串相加(如圖-1)

=CONCATENATE(A3, "-",B3)

接著把之前的script改成:

=IF(
	(CONCATENATE(E2, "-",F2) = "-"),"",
	IF(
		ISNUMBER(SEARCH(CONCATENATE(E2, "-",F2),C:C)),"無異動","新增至主表"
    )
)
  • 其中欄位行號要自己下拉變化
  • 前一個IF為了防新商品的公司跟商品都是空白字串情況
  • 第二個IF判斷邏輯:"公司+商品字串"是否包含在"商品編號"裡面

以下參考連結:

【Excel-IF使用三個條件判斷式(AND,OR,*運算子)】
配合Search就可以
【 檢查儲存格是否包含文字 (不區分大小寫) 】

看更多先前的回應...收起先前的回應...

感謝你的回應 因為主表的公司其實不會按照順序排列
所以如上的公式也會造成對應不起來的狀況 像是調查表中甲公司的B商品 應該要是"無異動"的

暐翰 iT邦大師 7 級 ‧ 2018-03-19 00:38:00 檢舉

對 我有發現這問題
明天假如還沒解決,我再想想怎麼做

暐翰 iT邦大師 7 級 ‧ 2018-03-19 09:08:40 檢舉

更新內容 可以測試一下 看是不是你要的

用 lookup 試試看

今天下班回家試試 3Q

剛剛試了一下 好像有些對有些錯 可是感覺公式沒問題啊

暐翰 iT邦大師 7 級 ‧ 2018-03-20 08:17:28 檢舉

甚麼問題呢
下面是我寫範例的檔案,看有沒有不一樣的地方
https://drive.google.com/file/d/1BqDsbdJ4qQEHNp9Qfb8pSthnhdPXFz-g/view?usp=sharing

您好 甲B應該要是無異動才對

1
海綿寶寶
iT邦超人 1 級 ‧ 2018-03-19 10:25:53

https://ithelp.ithome.com.tw/upload/images/20180319/2000178703BG4Af6Sl.png

C1=A1&B1 (C2..C7 公式由 C11 複製)
F1=D1&E1 (F2..F6 公式由 F1 複製)
G1=IF(ISNA(VLOOKUP(F1,$C:$C,1,FALSE)),"新增至主表","無異動")

(G2..G6 公式由 G1 複製)
pcw iT邦研究生 5 級 ‧ 2018-03-19 10:49:27 檢舉

試MATCH

我要發表回答

立即登入回答