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

標題: [轉貼] 討論不重複清單 [打印本頁]

作者: Hsieh 時間: 2010-6-18 23:41 標題: 討論不重複清單

本帖最後由 Hsieh 於 2010-6-19 11:08 編輯

http://gb.twbts.com/index.php/topic,466.0.html
舊站以上連結曾經討論過此一問題
其中附件大都遺失,把他歸納一下供大家參考
有更好公式歡迎繼續參與討論
[attach]1327[/attach]

作者: zz5151353 時間: 2010-6-30 15:41

不錯
小弟也來熱鬧一下

{=INDEX(A:A,SMALL(IF(MATCH(A$2:A$100&"",A$2:A$100&"",)=ROW($2:$100)-1,ROW($2:$100),4^8),ROW(1:1)))&""}

作者: basarasy 時間: 2010-6-30 22:12

本帖最後由 Hsieh 於 2010-6-30 22:23 編輯

謝謝 Hsieh和zz5151353版主.
我不能下載><

這個如果要指定地方 D3:D40 請問要如果改?
{=INDEX(A:A,SMALL(IF(MATCH(A$2:A$100&"",A$2:A$100&"",)=ROW($2:$100)-1,ROW($2:$100),4^8),ROW(1:1)))&""}

作者: Hsieh 時間: 2010-6-30 22:28

回復 3# basarasy

{=INDEX(A:A,SMALL(IF(MATCH(A$2:A$100&"",A$2:A$100&"",)=ROW($2:$100)-1,ROW($2:$100),4^8),ROW(1:1)))&""}
此公式資料範圍是A2:A100
你可套用看看將$A$2:$A$100改成你的資料範圍$D$3:$D$40
{=INDEX(A:A,SMALL(IF(MATCH($D$3:$D$40&"",$D$3:$D$40&"",)=ROW($3:$40)-1,ROW($3:$40),4^8),ROW(1:1)))&""}

作者: basarasy 時間: 2010-7-1 00:55

本帖最後由 gong 於 2010-7-1 08:24 編輯

回復 4# Hsieh

圖1 d5會有空格公式{=INDEX(A:A,SMALL(IF(MATCH(A$2:A$100&"",A$2:A$100&"",)=ROW($2:$100)-1,ROW($2:$100),4^8),ROW(1:1)))&""}

圖2 沒有出數公式{=INDEX(A:A,SMALL(IF(MATCH($D$3:$D$40&"",$D$3:$D$40&"",)=ROW($3:$40)-1,ROW($3:$40),4^8),ROW(1:1)))&""}

作者: zz5151353 時間: 2010-7-1 07:52

請上傳檔案吧

{=INDEX(D:D,SMALL(IF((D$3:D$40<>"")*MATCH(D$3:D$40&"",D$3:D$40&"",)=ROW($3:$40)-2,ROW($3:$40),4^8),ROW(1:1)))&""}

作者: basarasy 時間: 2010-7-1 08:03

本帖最後由 gong 於 2010-7-1 08:23 編輯

回復 6# zz5151353

謝謝zz大大.
大大教的這個沒有問題了.
{=INDEX(D:D,SMALL(IF((D$3:D$40<>"")*MATCH(D$3:D$40&"",D$3:D$40&"",)=ROW($3:$40)-2,ROW($3:$40),4^8),ROW(1:1)))&""}

作者: wqfzqgk 時間: 2010-7-1 13:14

还是2007以上版本的删除重复值来的快一些

作者: wqfzqgk 時間: 2010-7-1 13:15

在VBA中一般用集合或字典，做个自定义函数

作者: PD961A 時間: 2010-7-1 13:25

舊站以上連結曾經討論過此一問題
其中附件大都遺失,把他歸納一下供大家參考
有更好公式歡迎繼續參與討論 ...
Hsieh 發表於 2010-6-18 23:41

請問版主及各位先進
下載這個檔案裏的內容都整理好了
可是每做一個動作
都會有一直"重算儲存格"的動作
請問各位先進下載後的檔案會有這樣的情形嗎?

[attach]1572[/attach]

[attach]1573[/attach]

[attach]1574[/attach]

若沒有大概是自己EXCEL的問題了....謝謝

作者: Hsieh 時間: 2010-7-1 16:22

有公式當然就會重算
除非你把工具/選項中的自動重算改手動

作者: asimov 時間: 2010-7-1 22:25

回復 10# PD961A

陣列公式是一種循環式計算的強大功能，雖然好用，但也有缺點
借用zz5151353 版主公式說明
{=INDEX(A:A,SMALL(IF(MATCH(A$2:A$100&"",A$2:A$100&"",)=ROW($2:$100)-1,ROW($2:$100),4^8),ROW(1:1)))&""}
短短的一行共使用了 INDEX、SMALL、IF、MATCH、ROW等至少5個函數
而其計算範圍有99列(ROW($2:$100))
如果將此公式複製10個，CPU的負擔已經有點重

又如果計算範圍是3000列，公式複製為12個月，又有10幾項要統計，想當然爾，CPU一定跑得氣喘吁吁
試過跑一個重新計算可以喝一杯咖啡，一點也不誇張
所以減少使用大量的陣列公式，有助於提升工作效率喔

作者: gong 時間: 2010-7-1 23:46

回復 PD961A

陣列公式是一種循環式計算的強大功能，雖然好用，但也有缺點
借用zz5151353 版主公式說 ...
asimov 發表於 2010-7-1 22:25

贊成!
少用陣列公式
可以減少重複運算

純研究可行,如用於實務上經常會跑不動
幾乎不會用於實務上
基本上陣列公式可透過輔助欄可以輕鬆完成

作者: victorl 時間: 2010-7-2 01:10

回復 10# PD961A

"重算儲存格"正常現象，如果不想"重算儲存格"可以用附圖方法解除，But 改成手動後就不會自動處理了喔

[attach]1589[/attach]

附圖為工具 > 選項設定的選單

作者: PD961A 時間: 2010-7-2 07:04

回復 14# victorl
回復 13# gong
回復 12# asimov

所以結論
1."重算儲存格"是正常現象
2.所以減少使用大量的陣列公式，有助於提升工作效率喔
3.純研究可行,如用於實務上經常會跑不動
幾乎不會用於實務上
基本上陣列公式可透過輔助欄可以輕鬆完成

非常謝謝各位先進及版主的回答

作者: PD961A 時間: 2010-7-2 07:08

回復 15# 老夏

謝謝夏版主
我覺得您這個最好用
而且不會當機.....又不用喝咖啡等時間...

[attach]1591[/attach]

謝謝您

作者: gong 時間: 2010-7-2 08:56

本帖最後由 gong 於 2010-7-2 09:13 編輯

重覆資料在A欄

加輔助欄B
B2=IF(COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,"")
下拉

不重覆數據
C2=IF(ROW()>MAX(B:B),"",INDEX(A:A,MATCH(ROW()-1,B:B,0)))
下拉

作者: OFFICE專家 時間: 2011-1-7 22:10

本帖最後由 OFFICE專家於 2011-1-7 22:37 編輯

這則裡面有
[原創] Excel 進階篩選大全

作者: charce 時間: 2012-1-3 03:55

本帖最後由 charce 於 2012-1-3 04:01 編輯

回復 17# gong
個人摹仿上方gong的函數進行練習，發現總是會缺掉最後的一筆資料
問題是出在那邊呢？可不可以指正一下
謝謝您

作者: ANGELA 時間: 2012-1-3 09:53

=IF(ROW(1:1)>MAX(B:B),"",INDEX(A:A,MATCH(ROW()-1,B:B,0)))

作者: charce 時間: 2012-1-4 04:54

回復 20# ANGELA
謝謝 ANGELA
已經解決問題了

作者: sunnyso 時間: 2013-4-30 22:53

回復 17# gong

感謝 gong 大大, 又學到一種方法.

不過用動態範圍要比用 MAX 更有效率.

數據在A欄

加輔助欄I, J
I2 =COUNTIF(OFFSET($A2,,,COUNTA(A:A)-1,),A2)
J1 = 0
J2 =MATCH(1,OFFSET($I$2,J1,,COUNTA(A:A)-1,),0)+J1
下拉

不重覆數據
K2 = IFERROR(INDEX(A:A,J2+1),"")
下拉

====================================
用字典法寫了VBA Function UNIQUEp

N2=IFERROR(UNIQUEp(A:A,ROW()),"")
下拉

不知哪位大大可否寫一段code來計算一下各種方法所需的計算時間

Function UNIQUEp(source As Range, num As Integer)
Dim newArray, myArray As Variant
rows_num = source.Rows.Count
'myArray = Range(Cells(source.Row, source.Column), Cells(source.Row + rows_num - 1, source.Column)).Value
myArray = Sheets(source.Parent.Name).Range(source.Address).Value
Set Dic = CreateObject("scripting.dictionary")
For i = 1 To rows_num
If myArray(i, 1) <> "" Then
Dic((myArray(i, 1))) = ""
End If
Next
arr = Dic.Keys
UNIQUEp = arr(num - 1)
End Function

複製代碼

[attach]14835[/attach]

作者: sunnyso 時間: 2013-4-30 23:49

本帖最後由 sunnyso 於 2013-4-30 23:50 編輯

回復 11# Hsieh
回復 13# gong

剛剛想到另一種方法不用 MAX, LARGE 等陣列函數和動態範圍的方法, (本來以爲MAX, LARGE等陣列函數的效率較低), 不過好像還是動態範圍有效率

數據在A欄

加輔助欄O
O1 = 0
O2 = IF(COUNTIF(A$2:A2,A2)=1,((8*SUM(L$1:L1)+1)^0.5-1)/2+1,"")
下拉

不重覆數據
P2 = INDEX(A:A,MATCH(ROW(A1),O:O,0))
下拉

真的想知道哪種方法最有效率. 請大大寫一段code來計算一下各種方法所需的計算時間
[attach]14836[/attach]

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