Board logo

標題: [發問] 請教關於截取儲存格中的唯一值 [打印本頁]
作者: 偉婕    時間: 2010-10-8 01:11     標題: 請教關於截取儲存格中的唯一值

本帖最後由 偉婕 於 2010-10-8 02:11 編輯

我有上千筆資料,在資料中有A[關鍵字]及B[關鍵字出現年代]二欄
B欄中的每一個儲存格,由多個年代組成,每個年代中間以頓號隔開
現在想要於D欄中將B欄中每個儲存格中的唯一年代顯示出來
不知有沒有較有效率的方法,謝謝!

例如
關鍵字         關鍵字出現年代
Slam        2004、2004、2003、2002
Object        1997、2002、1997、1996、1995

結果
關鍵字         關鍵字出現年代(唯一)
Slam        2004、2003、2002
Object        2002、1997、1996、1995
作者: 偉婕    時間: 2010-10-8 03:01

另外再問
若已經整理出B欄的資料
請問有沒有較有效率的方法
將A:B欄的資料整理成D:E欄的資料
謝謝!
作者: oobird    時間: 2010-10-8 09:19

稍改一下妳的函數即可。
[attach]3066[/attach]
作者: oobird    時間: 2010-10-8 10:00

991008-1
[attach]3067[/attach]
作者: 偉婕    時間: 2010-10-8 12:29

本帖最後由 偉婕 於 2010-10-8 14:03 編輯

回復 5# oobird

謝謝[oobird]版主
不過 [ 991008-1]  若其中有一個關鍵字,只出現在一個年代
則會使得它不是"字串",會使程式出錯,不知怎麼解決
謝謝!
作者: oobird    時間: 2010-10-8 14:43

Private Sub CommandButton1_Click()
Dim d As Object, arr, i%, j%, s$, s1$, sp
    Set d = CreateObject("Scripting.Dictionary")
    arr = Sheet1.Range([a2], [b2].End(4))
    For i = 1 To UBound(arr)
        s = arr(i, 2): s1 = arr(i, 1)
        If InStr(s, "、") = 0 And Not d.exists(s) Then d.Add s, s1
        sp = Split(s, "、")
        For j = 0 To UBound(sp)
            If Not d.exists(sp(j)) Then
                d.Add sp(j), s1
            Else
                d(sp(j)) = IIf(InStr(d(sp(j)), s1) = 0, d(sp(j)) & "、" & s1, d(sp(j)))
            End If
        Next j
    Next i
    [d2].Resize(d.Count, 1) = Application.Transpose(d.keys)
    [e2].Resize(d.Count, 1) = Application.Transpose(d.items)
    Set d = Nothing
End Sub
作者: 偉婕    時間: 2010-10-8 16:21

回復 7# oobird

謝謝[oobird]版主
上個問題解決了,可是現在不知是不是單字長度問題
執行時會出現∼∼執行階段錯誤"13"　　型態不符合
作者: oobird    時間: 2010-10-8 19:33

[attach]3075[/attach]是字串太長了，改成一列列輸出。
作者: 偉婕    時間: 2010-10-8 20:31

回復 9# oobird
謝謝[oobird]版主
我用公式比對了一下結果發現有些出入
關鍵字[Robot],在2002、2001、2000、1995皆出現
但執行後,卻只有出現在2002、2001、2000
我看了一下發現未出現在這些年中,皆有[Mobile Robot]
不過奇怪的是[Robotics],在2002、1997、2004、1999、1995皆出現
但執行後,卻都有出現,而除了1995外,皆有[Distributed Robotics]
不知什麼原因,謝謝!
作者: oobird    時間: 2010-10-8 22:19

呵呵，我眼睛看得快脫窗了，還是不知道那個錯誤。我把妳質疑的地方用紅字標起來，妳再看看那個錯誤標給我參考！[attach]3085[/attach]
作者: 偉婕    時間: 2010-10-9 06:52

回復 10# oobird

［oobird］版主,真是難為您了,經測試已正確,謝謝
作者: 偉婕    時間: 2010-10-13 00:34

回復 10# oobird

昨天再使用時又發現會有漏資料
後來將A欄排序後再執行就正常了
在此回報一下
謝謝!
作者: Hsieh    時間: 2010-10-13 10:45

倒可試著把資料整理後用樞紐分析
[attach]3148[/attach]
作者: oobird    時間: 2010-10-13 11:59

倒可試著把資料整理後用樞紐分析
Hsieh 發表於 2010-10-13 10:45



   不錯， 好點子。不過整理資料可能少不得還是要vba吧。
作者: Hsieh    時間: 2010-10-13 14:40

回復 14# oobird


    呵呵!
我沒用任何一句vbaㄋㄟ
可利用定義名稱來選取移動
作者: 偉婕    時間: 2010-10-13 16:26

回復 13# Hsieh

謝謝[Hsieh]版主提供另一種解法

我沒用任何一句vbaㄋㄟ
可利用定義名稱來選取移動
Hsieh 發表於 2010-10-13 14:40


我看到定義名稱X=OFFSET(關鍵字下的年代!$A5,,,,LOOKUP(9.9E+307,關鍵字下的年代!5:5,COLUMN(關鍵字下的年代!5:5)))
不明瞭為何這麼設?
另外,[關鍵字下的年代]工作表中的A及B欄是怎麼整理出來的?
以上還請[Hsieh]版主解說一下
謝謝!
作者: Hsieh    時間: 2010-10-13 18:49

本帖最後由 Hsieh 於 2010-10-13 19:29 編輯

回復 16# 偉婕
當然首先必須先將B欄資料剖析
因為必須每筆對應關鍵字所以每個、位置要多出一欄空白欄位
所以先用取代功能將、取代為、、
再以、做分隔符號剖析
x名稱是為了要選取整列有資料的範圍
定義好x名稱後
點選A2然後在名稱方塊輸入x按ENTER
按F5選特殊儲存格"空格"
輸入=$A2按CTRL+ENTER輸入公式便能填滿
每欄空格以第2列儲存格雙擊右下控點向下填滿
接下來由C欄開始2欄一起移至A欄資料底部

當然以上操作並非唯一方法，或許您有更理想的方法
我這樣的方法最主要是在說明利用定義名稱選取資料的方法
有更好的方法歡迎提出來讓大家練習
作者: 偉婕    時間: 2010-10-13 20:01

回復  偉婕
輸入=$A2按CTRL+ENTER輸入公式便能填滿
每欄空格以第2列儲存格雙擊右下控點向下填滿
接下來由C欄開始2欄一起移至A欄資料底部
Hsieh 發表於 2010-10-13 18:49


我試了一下,是不是輸入=$A1按CTRL+ENTER
因為若是=$A2,好像資料會往上跑一列

另外,"每欄空格以第2列儲存格雙擊右下控點向下填滿",不懂
不是都被公式填滿了,怎麼還有空格?

再麻煩［Hsieh］版主解疑,謝謝！
作者: oobird    時間: 2010-10-13 23:13

感覺完全不用公式也花不了多少時間。
錄得太大了，影響瀏覽，我把資料剖析的部分略過，重錄一個。
作者: Hsieh    時間: 2010-10-13 23:58

本帖最後由 Hsieh 於 2010-10-14 00:01 編輯

回復 18# 偉婕
我的做法反而複雜了
oobird的做法確實是簡單多了
在此特別一提，2007版有一資料/移除重複功能
當oobird版大完成轉置時，用此功能就可將重覆的年份移除
在此回覆偉婕
我的X定義在定義時要以相對參照的同列來下定義
就是說在定義的時候若作用儲存格在第2列時
參照到的公式是=OFFSET(關鍵字下的年代!$A2,,,,LOOKUP(9.9E+307,關鍵字下的年代!2:2,COLUMN(關鍵字下的年代!2:2)))
你看到=OFFSET(關鍵字下的年代!$A5,,,,LOOKUP(9.9E+307,關鍵字下的年代!5:5,COLUMN(關鍵字下的年代!5:5)))
是因為你作用儲存格停在第5列所以看到的公式是以第5列為相對參照
當你在A2也是輸入成=OFFSET(關鍵字下的年代!$A5,,,,LOOKUP(9.9E+307,關鍵字下的年代!5:5,COLUMN(關鍵字下的年代!5:5)))
所以會跳到第5列做選取

[attach]3165[/attach]
作者: 偉婕    時間: 2010-10-14 17:29

回復 19# oobird
謝謝[oobird]版主的用心良苦,還重新錄製
技巧已經學會囉!

回復 20# Hsieh
謝謝[Hsieh]的解說,我已經知道自己哪個地方做錯了,也已經成功了

再次感謝二位版主的指導
從您們兩位身上不僅學會很多公式的運用及技巧
也更靈活了自己的思路!



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