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

標題: 請問字串要如何統計次數不重復? [打印本頁]

作者: oiggu 時間: 2010-7-24 12:23 標題: 請問字串要如何統計次數不重復?

本帖最後由 oiggu 於 2010-7-24 12:47 編輯

請問字串要如何統計次數不重復

作者: kimbal 時間: 2010-7-24 16:27

本帖最後由 kimbal 於 2010-7-24 16:31 編輯

在d3 放上這個公式:

=SUMPRODUCT((MID($A$1:$A$30,2,4)=D$2&"")*(LEFT($A$1:$A$30,1)=$C3)*($A$1:$A$30<>"")/COUNTIF($A$1:$A$30,$A$1:$A$30))

複製代碼

然後抄到其他格

作者: Hsieh 時間: 2010-7-24 17:01

回復 1# oiggu
選取d3儲存格,加入定義名稱x
公式=OFFSET(Sheet1!$A:$A,,,COUNTA(Sheet1!$A:$A),)
[attach]1955[/attach]
d3陣列公式
{=IF(ISNUMBER(MATCH($C3&D$2,LEFT(x,5),0)),SUM(1/COUNTIF(OFFSET($A$1,MATCH($C3&D$2,LEFT(x,5),0)-1,,COUNTIF(x,"="&$C3&D$2&"*")),OFFSET($A$1,MATCH($C3&D$2,LEFT(x,5),0)-1,,COUNTIF(x,"="&$C3&D$2&"*")))),0)}
向下向右複製

作者: gong 時間: 2010-7-24 17:06

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

d3=SUMPRODUCT((LEFT($A$1:$A$30,5)=$C3&D$2)*1/COUNTIF($A$1:$A$30,$A$1:$A$30))

作者: oiggu 時間: 2010-7-24 18:25

謝謝各位:
經測試都可以使用，但遇到一個問題資料有5000筆會影響執行速度會非常摱
請問是否可改vba 再匯入格子加快執行速度。

作者: Hsieh 時間: 2010-7-24 23:34

回復 5# oiggu

Sub MyCount()
Set d = CreateObject("Scripting.Dictionary")
With Sheet1
For Each a In .Range(.[A1], .[A65536].End(xlUp))
If d(Left(a, 5)) = "" Then
d(Left(a, 5)) = a
ElseIf InStr(d(Left(a, 5)), a) = 0 Then
d(Left(a, 5)) = d(Left(a, 5)) & "," & a
End If
Next
For Each a In .[C3:C5]
For Each b In .[D2:F2]
.Cells(a.Row, b.Column) = UBound(Split(d(a & b), ",")) + 1
Next
Next
End With
End Sub

複製代碼

作者: oiggu 時間: 2010-7-25 01:05

本帖最後由 oiggu 於 2010-7-26 06:29 編輯

謝謝超級版主，太神了。

已經可以統計次數
請問要如何用VBA把金額統計出來
放入格式內、字串開頭+年月統計

作者: Hsieh 時間: 2010-7-25 20:52

回復 7# oiggu

Sub MyCount()
Set d = CreateObject("Scripting.Dictionary")
Set d1 = CreateObject("Scripting.Dictionary")
With Sheet1
For Each a In .Range(.[A1], .[A65536].End(xlUp))
d1(Left(a, 5)) = d1(Left(a, 5)) + Application.Sum(a.Offset(, 1).Resize(, 3))
If d(Left(a, 5)) = "" Then
d(Left(a, 5)) = a
ElseIf InStr(d(Left(a, 5)), a) = 0 Then
d(Left(a, 5)) = d(Left(a, 5)) & "," & a
End If
Next
For Each a In .[F5:F7]
For Each b In .[G3:L3]
If (b.Column Mod 2) = 1 Then Cells(a.Row, b.Column) = UBound(Split(d(a & b), ",")) + 1
If (b.Column Mod 2) = 0 Then Cells(a.Row, b.Column) = d1(a & b)
Next
Next
End With
End Sub

複製代碼

作者: oiggu 時間: 2010-7-26 06:36

回復 8# Hsieh

感謝超級版主

你寫的 vba 簡潔有力，已經可以統計次數使用正常，
還請教一個問題，如何把同名稱的數字總計起來、
對照材料項目分類計算，再用 VBA 匯入格式要如何作?

作者: oiggu 時間: 2010-7-29 19:03

回復 9# oiggu

Sub sum()
For k = 2 To 10
Set d = CreateObject("Scripting.Dictionary")
With Sheets("date")
For Each a In Sheets("date").Range(.[a3], .[a65536].End(xlUp))
d(a) = d(a) + a.Offset(, k - 1)
Next
For Each a In Sheets("a").Range([a3], [a65536].End(xlUp))
Sheets("a").Cells(a.Row, k) = d(a)
Next
End With
Next
End Sub

感謝板主
不曉得那裏出錯、測試出來的值=0 不正確
請問 Sheets("a").Cells(a.Row, k) = d(a) 錯在那裏?

作者: GBKEE 時間: 2010-7-29 19:35

回復 10# oiggu

Sub Ex()
Dim D As Range, R As Range, M$
Set D = CreateObject("Scripting.Dictionary")
With Sheets("date")
For Each R In .Range("b2", .Cells(.[a1].End(xlDown).Row, .[a1].End(xlToRight).Column))
M = .Cells(R.Row, 1) & .Cells(1, R.Column)
D(M) = D(M) + R
Next
End With
With Sheets("A")
For Each R In .Range("b3", .Cells(.[a3].End(xlDown).Row, .[a1].End(xlToRight).Column))
M = .Cells(R.Row, 1) & .Cells(1, R.Column)
R = D(M)
Next
End With
End Sub

複製代碼

作者: Hsieh 時間: 2010-7-29 19:51

本帖最後由 Hsieh 於 2010-7-29 22:26 編輯

回復 10# oiggu
可見你對程式碼並未真正了解含意
況且這種統計用樞紐分析很快就能解決
建議你先把EXCEL基本功能了解
VBA是輔助工具
有了基本操作的基礎,VBA的運用才會靈活

[attach]2063[/attach]
[attach]2069[/attach]

Sub nn()
Dim A As Range, B As Range
Set d = CreateObject("Scripting.Dictionary")
With Sheets("DATE")
For Each A In .Range(.[B1], .[B1].End(xlToRight))
For Each B In .Range(.[A2], .[A2].End(xlDown))
d(A & B) = d(A & B) + Val(.Cells(A.Column, B.Row))
Next
Next
End With
With Sheets("A")
For Each A In .Range(.[B1], .[B1].End(xlToRight))
For Each B In .Range(.[A3], .[A3].End(xlDown))
.Cells(A.Column, B.Row) = d(A & B)
Next
Next
End With
End Sub

複製代碼

作者: zz5151353 時間: 2010-7-31 20:18

個人建議
採用合併彙算跟樞紐分析表
都是快速有效方法

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