麻辣家族討論版版 - Powered by Discuz! Board

標題: 利用Excel比對多筆有分隔符號資料的相同處 [打印本頁]

作者: parker 時間: 2013-10-6 20:46 標題: 利用Excel比對多筆有分隔符號資料的相同處

請問像下列有分格符號在同一欄，我想比對不同欄位是否有相同(重覆)的並且重覆的可變成紅色(別的色也可以)這種公式要如何寫??謝謝

  C1,C2,C3,C4,C5...................
  C5,C6,C7,C8..............
  R5,R6,R7,R8.................
  R12,R6,R17,R18...............
.
.
.
.

作者: p212 時間: 2013-10-7 08:34

本帖最後由 p212 於 2013-10-7 08:36 編輯

回復 1# parker
不太明瞭您的問題，可以提供一下您要的範例答案一起研究？謝謝！(如果能貼Excel內容畫面欄位分布說明更好)

作者: parker 時間: 2013-10-7 20:24

本帖最後由 parker 於 2013-10-7 20:26 編輯

回復 2# p212
請幫忙看附檔的文件,這是我們要建檔的BOM表我只劫取少部份，黃色欄位(位置)是我想要自動比對是否有重複文字，因為有分隔符號所以不知公式要如何寫。綠色欄位(數量)是我希望自動算出黃色欄位(位置)有幾個零件，所以不知是否有公式可告知??

謝謝

作者: p212 時間: 2013-10-8 10:09

本帖最後由 p212 於 2013-10-8 10:17 編輯

回復 3# parker
綠色C欄位(數量)的計算請使用下列公式，以位於C2儲存格為例：
=IF(D2="","",LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1)
向下複製公式
【說明】
利用計算D欄儲存格之「逗號數量+1」即可獲得答案
1、LEN(D2)：計算D2儲存格原有字數
2、SUBSTITUTE(D2,",","")：刪除D2儲存格中的逗號使字串為連續不中斷
3、LEN(SUBSTITUTE(D2,",",""))：計算刪除D2儲存格中的逗號後連續字串的字數
4、LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1：D2儲存格逗號數量再加1即為範例檔中的「零件」數
請參考！
至於黃色欄位(位置)自動比對部份，則有請高手指導，謝謝！

作者: parker 時間: 2013-10-8 16:41

回復 4# p212

感謝了請問有那些書本或網站有在教excel的這些公式寫法???我想要好好學習研究一下.

作者: parker 時間: 2013-10-8 17:08

回復 4# p212

P212大德;
我剛剛key入公式在C2欄位試驗發覺不行,變空白無任何數字???是我公式key錯欄位了嗎??

謝謝

作者: p212 時間: 2013-10-8 17:16

本帖最後由 p212 於 2013-10-8 17:21 編輯

回復 6# parker
複製4#之
=IF(D2="","",LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1)
逕貼於您提供範例之C2儲存格，OK！
若D2儲存格為空白，則C2儲存格必為空白。
因為 IF(D2="","",
請參考！

作者: p212 時間: 2013-10-8 17:25

回復 5# parker
來這兒就對啦！
「麻辣家族討論區」有超級版主與許多熱血的老師，常上來學習吧！

作者: parker 時間: 2013-10-8 19:38

回復 8# p212

我找到了,原來我多打個空格,以後我要常來這看看程式範例增加功力

謝謝了.

作者: parker 時間: 2013-10-8 19:49

回復 9# parker

另一個問題黃色那一欄(位置)要如何比對因該有點難度不知有高手可解答嗎??

作者: parker 時間: 2013-10-8 20:09

回復 4# p212

p212;
謝謝你解答,能用固然很好但我想了解公式的含意這樣才可以日新越新不斷進步所以我有下列問題
1、LEN(D2)：計算D2儲存格原有字數-------
ask:為何要先算儲存格原有字數??D2儲存格是"U1"或"C2,C3,C4,C5"這可以算嗎??那為何要下列第2部"刪除D2儲存格中的逗號使字串為連續不
中斷"

2、SUBSTITUTE(D2,",","")：刪除D2儲存格中的逗號使字串為連續不中斷

ask:若不刪除存格中的逗號使字串為連續不中斷是否可直接執行下列第3步

3、LEN(SUBSTITUTE(D2,",",""))：計算刪除D2儲存格中的逗號後連續字串的字數

ask:上列這些公式是固定的步驟嗎??還是可以像C語言一樣有語法或可簡化程式??

謝謝

作者: p212 時間: 2013-10-9 09:34

本帖最後由 p212 於 2013-10-9 09:45 編輯

回復 11# parker
假設：指定儲存格D2內字串為「C2,C3,C4,C5」
分析：字串「C2,C3,C4,C5」所有字元個數為「11」，不含逗號之字元個數則為「8」，逗號個數為「3」。
1、欲計算字串內含所有字元個數，則選用文字類函數LEN(text)，即 LEN(D2)=11。
2、今欲計算出不含逗號之字元個數，則需藉用文字類函數SUBSTITUTE(text,old_text,new_text,instance_num)來排除逗號。
如在指定儲存格D2內字串「C2,C3,C4,C5」，old_text即是對應到「,」，new_text則用「""」以無間隔取代「,」，如此SUBSTITUTE(D2,",","")結果回應為「C2C3C4C5」。
3、對SUBSTITUTE(D2,",","")的結果再次套用LEN(text)函數，即LEN(SUBSTITUTE(D2,",",""))=LEN(C2C3C4C5)=8。
4、故指定儲存格D2逗號個數=LEN(D2)-LEN(SUBSTITUTE(D2,",",""))=11-8=3
5、若今欲計算被逗號區隔開來的字串，則僅對「逗號個數」再加上「1」即可解決。(承上假設為C2、C3、C4與C5，字串個數為3+1=4。)

以上解說請參考！
ps.本人從未學過C語言，以上僅就邏輯思考而得。

作者: parker 時間: 2013-10-10 20:34

回復 12# p212

受教受教,了解了
謝謝

作者: toralo0614 時間: 2013-10-11 02:31

真是學到了 ~"~ 謝謝分享

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)