請問excel高手--欄位比對

al1107
個人積分：6分
文章編號：61083651

6分

樓主

2016-07-27 1:23

因為公司是做藥品買賣的,每兩個月要做一次藥價更新,而公司現有的進銷存軟體在不更動的前提下,多年前有委託外聘工程師寫一隻程式,可以去比對健保局以及進銷存軟體的某特定欄位的健保價格,然後我們再針對有異動的去修正...
沒想到健保局的格式最近有修正,導致之前的程式無法使用,所以公司內部評估看可否透過excel來搞定此問題,但這比對的過程要用公式之類的,我們一竅不通,只好請大家幫忙~~
已下有兩個圖片,一個是現有進銷存軟體轉出的貨品資料檔(圖A),一個是健保局的藥價檔(圖B),要比對的狀況是：
1.用圖A現有的編號(AC01624100),去比對圖B,看價格1.5元是否兩邊一樣,如果是,就可以忽略,如果不是,就要跳出來~~
2.圖B中的AC01662100有兩筆,關鍵在於日期,一個只到1001130,所以不管,另一個是到9991231,所以要用圖A的那筆來比對9991231的這筆,以此範例,一個是1.4元,一個是1.5元,所以要能被篩選出來~~
以上是我目前能表達的概念,請高手指導該如何用公式去篩選,是否要和併在一個檔案之類的,然後若有任何疑問再請告知,看如何做進一步的說明,感謝~~

請問excel高手--欄位比對

2016-07-27 1:23 發佈

文章關鍵字 excel 欄位比對高手

Mystique Hsiao

Mystique Hsiao
個人積分：7527分
文章編號：61084029

7527分

2樓

2016-07-27 4:33

只截取需要用到的欄位來使用，
以現有資料測試結果 OK，
但因樣本數不夠多，無法驗證正確性！

不過，既然是處理公司的公事，
由公司（花錢？）請先前的工程師修改原程式或許比較好一點！

=SUMPRODUCT(--($A2=PriceB!$A:$A), --($B2<>PriceB!$B:$B), --(LEFT(PriceB!$C:$C, 3)="999"))

My Interior Knowledge is Extraordinaire

al1107

al1107
個人積分：6分
文章編號：61087504

6分

樓主

2016-07-27 12:00

Mystique Hsiao wrote:
只截取需要用到的欄...(恕刪)

--
就是當初的工程師不願意再維護,付錢也不肯,只好放棄...
想請問格式化的公式完整的指令為何,煩請告知,感謝~~

Mystique Hsiao

Mystique Hsiao
個人積分：7527分
文章編號：61088194

7527分

4樓

2016-07-27 12:51

al1107 wrote:
想請問格式化的公式完整的指令為何

請參考！

My Interior Knowledge is Extraordinaire

al1107

al1107
個人積分：6分
文章編號：61088897

6分

樓主

2016-07-27 13:42

Mystique Hsiao wrote:
只截取需要用到的欄...(恕刪)

--
真的很抱歉,有照你第一次回覆的方式編排excel,然後我有一個字一個字打過比對,但不曉得哪裡有誤,無法看到紅字的效果,可否再幫忙一下~~^^\\

附加壓縮檔: 201607/mobile01-d9f0325513c4e47d04be97f4a77fcb5e.zip

Mystique Hsiao

Mystique Hsiao
個人積分：7527分
文章編號：61089471

7527分

6樓

2016-07-27 14:23

al1107 wrote:
無法看到紅字的效果

要先選擇欲變色的欄位範圍（此處為 A 工作表），
輸入完成後確認一下套用範圍（紅框處）！

My Interior Knowledge is Extraordinaire

al1107

al1107
個人積分：6分
文章編號：61094456

6分

樓主

2016-07-27 21:22

Mystique Hsiao wrote:
要先選擇欲變色的欄...(恕刪)

--
拍謝,還是我...兩個狀況
1.依照你留下的指令我輸入為=SUMPRODUCT(--($A2=PriceB!$A:$A),--($B2<>PriceB!$B:$B),--LEFT(PriceB!$C:$C, 3)="999")
最後的地方要是有兩個括弧,會被提示為公式錯誤,所以我改掉一個才能存進規則~~
2.存進規則後,在規則管理員,預設會跳成$H$8,我改成你回覆圖片中的$A$2:$B$18,套用,整個EXCEL就會當掉,吃掉CPU的效能,只能強制關掉~~
想請問以上兩點哪裡有誤,再附上EXCEL檔~~
附加壓縮檔: 201607/mobile01-e63219e79b184f35043a8b89550f8677.zip

Mystique Hsiao

Mystique Hsiao
個人積分：7527分
文章編號：61095178

7527分

8樓

2016-07-27 22:28

al1107 wrote:
1.依照你留下的指令我輸入為
=SUMPRODUCT(--($A2=PriceB!$A:$A),--($B2<>PriceB!$B:$B),--(LEFT(PriceB!$C:$C, 3)="999"))
最後的地方要是有兩個括弧,會被提示為公式錯誤,所以我改掉一個才能存進規則~~

您在公式中 LEFT 函數的左側少了一個『(』，所以最後面才會多出『)』！
（上面紅字部分）

al1107 wrote:
2.存進規則後,在規則管理員,預設會跳成$H$8,
我改成你回覆圖片中的$A$2:$B$18,套用,
整個EXCEL就會當掉,吃掉CPU的效能,只能強制關掉~~

因為我在公式中用了『$A:$A』、『$B:$B』、『$C:$C』，
搜尋判斷的範圍太廣，所以容易加重系統處理的負荷，
若要減輕佔用系統的情況，可以把它們改為
『$A$2:$A$9999』、『$B$2:$B$9999』、『$C$2:$C$9999』，
（其中的 9999 要視 A 工作表中內容的多寡，至少必須大於或等於資料量）

此範例檔中我是把範圍設在 2~99，您可視自己的需求再做調整！

My Interior Knowledge is Extraordinaire

al1107

al1107
個人積分：6分
文章編號：61096913

6分

樓主

2016-07-28 1:37

Mystique Hsiao wrote:
您在公式中 LEFT...(恕刪)

--
高手真的就隱藏在民間中阿...
經過微調修正,果然就跑出想要的結果了~~我人在新北市,若你也在北部,不嫌棄的話可以請杯珍奶啦,感謝再感謝~~