=IF(SUM((主表[公司]=[@公司])*(主表[商品]=[@商品])*1),"無異動","新增至主表")
,按下CTRL+SHIFT+ENTER公司+商品
,公式為=A2&"-"&B2
=IF(ISNUMBER(MATCH(E2&"-"&F2,$C$2:$C$8,0)),"無異動","新增至主表")
按下ENTER即可。=IF(COUNTIFS(主表[公司],[@公司],主表[商品],[@商品]),"無異動","新增至主表")
,按下ENTER即可。感謝你 看得懂方法2 方法1有點難看不懂
滿想知道方法1是怎麼達成的 可以大概解釋一下嗎 感恩~!!
方法一看不懂,是步驟一還是步驟二不懂?
為了爭取最佳解答,我加送一個方法三,捨棄陣列公式,改用COUNTIFS函數。看看能不能讓你懂。
其實自從EXCEL新增這些xIFS函數之後,陣列公式已經少用了。
步驟2不太了解
SUM的那一段
SUM((主表[公司]=[@公司])*(主表[商品]=[@商品])*1)
是一組陣列公式。基本上SUM裡面是三組數相乘。我們以F2儲存格為例。
(主表[公司]=[@公司])
,等號左邊是一個數列,代表主表的公司欄位,數列為{甲, 甲, 甲, 乙, 乙, 乙, 乙}
。等號右邊是一個數,代表調查表中該列的公司欄位,也就是D2儲存格的值,甲
。這一組是在判斷等號左邊數列的每一個元素是否等於等號右邊的值,等於就傳回true
,否則就是false
。比對的結果為{T, T, T, F, F, F, F}
。(主表[商品]=[@商品])
同理,就是比對{A, B, D, A, B, F, G}
是否等於E2儲存格的值A
。比對結果為{T, F, F, T, F, F, F}
。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?
我這邊嘗試使用ISNUMBER + SEARCH + IF的方式來解決你的問題
=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)),"無異動","新增至主表"
)
)
【Excel-IF使用三個條件判斷式(AND,OR,*運算子)】
配合Search就可以
【 檢查儲存格是否包含文字 (不區分大小寫) 】
感謝你的回應 因為主表的公司其實不會按照順序排列
所以如上的公式也會造成對應不起來的狀況 像是調查表中甲公司的B商品 應該要是"無異動"的
對 我有發現這問題
明天假如還沒解決,我再想想怎麼做
更新內容 可以測試一下 看是不是你要的
用 lookup 試試看
今天下班回家試試 3Q
剛剛試了一下 好像有些對有些錯 可是感覺公式沒問題啊
甚麼問題呢
下面是我寫範例的檔案,看有沒有不一樣的地方
https://drive.google.com/file/d/1BqDsbdJ4qQEHNp9Qfb8pSthnhdPXFz-g/view?usp=sharing
您好 甲B應該要是無異動才對
C1=A1&B1 (C2..C7 公式由 C11 複製)
F1=D1&E1 (F2..F6 公式由 F1 複製)
G1=IF(ISNA(VLOOKUP(F1,$C:$C,1,FALSE)),"新增至主表","無異動")
(G2..G6 公式由 G1 複製)