不确定这是否可能。如下所示,想象一下以下 Excel 表:
STOCK|VALUE|TAGS
ABCDE|10000|Z,X
FGHIJ|20000|X,Y
KLMNO|30000|X,Y,Z
PQRST|10000|Z,Y
Total|70000|
现在,我想在工作表或工作簿的某个地方生成每个标签相对于总值的百分比。例如,它会向我显示(根据使用的“TAGS”值动态生成):
X = 60,000 / 85.71%
Y = 60,000 / 85.71%
Z = 50,000 / 71.43%
我想到的第一个(也是唯一的)方法是针对每个可能的“TAG”值设置一个 if 语句。但是,这又长又麻烦、又脏又低效,而且不动态。肯定有更好的方法吧?我更喜欢严格的 Excel 解决方案;但如果不可能的话 - 我想就必须使用 VBA 了...
//编辑 > 目前我所拥有的 VBA 不起作用。我不使用 VBA;所以请原谅代码不佳。目前,这会尝试在唯一标记字段的第 5/6 列上生成一个列表,并将它们与第 7 列中的百分比字段相加。它在当前状态下不起作用,因为它将整个标记字段作为单个值。
Sub ProcessData1()
Dim dict As Dictionary
Dim i As Integer
Dim targetRow As Integer
Dim stock As String
Dim value As Double
Dim more As Boolean
Set dict = New Dictionary
more = True
'Row Start
i = 2
targetRow = 1
While more
tags = Worksheets("holdings").Cells(i, 3).value
If dict.Exists(tags) Then
value = Worksheets("holdings").Cells(dict.Item(tags), 6) + Worksheets("holdings").Cells(i, 2).value
Worksheets("holdings").Cells(dict.Item(tags), 6) = value
'Assumes Total value on Row 21
Worksheets("holdings").Cells(dict.Item(tags), 7) = value * 100 / Worksheets("holdings").Cells(21, 2)
Else
targetRow = targetRow + 1
Worksheets("holdings").Cells(targetRow, 5) = Worksheets("holdings").Cells(i, 3).value
Worksheets("holdings").Cells(targetRow, 6) = Worksheets("holdings").Cells(i, 2).value
Worksheets("holdings").Cells(targetRow, 7) = Worksheets("holdings").Cells(i, 2).value * 100 / Worksheets("holdings").Cells(21, 2)
dict.Item(tags) = targetRow
End If
i = i + 1
If Len(Worksheets("holdings").Cells(i, 1)) = 0 Then more = False
Wend
End Sub
答案1
答案2
好的,所以我想出了自己的答案。它是 VBA,而正如我上面所说,我不使用 VBA,所以我确信下面的代码远非完美的解决方案,但它确实有用。
Sub ProcessData1()
Dim dict As Dictionary
Dim i As Integer
Dim j As Integer
Dim targetRow As Integer
Dim stock As String
Dim value As Double
Dim more As Boolean
Dim tags() As String
Dim tag As String
Set dict = New Dictionary
more = True
'Row Start
i = 2
targetRow = 1
While more
tags() = Split(Worksheets("holdings").Cells(i, 3).value, ",")
For j = LBound(tags) To UBound(tags)
tag = tags(j)
'MsgBox tag
If dict.Exists(tag) Then
value = Worksheets("holdings").Cells(dict.Item(tag), 6) + Worksheets("holdings").Cells(i, 2).value
Worksheets("holdings").Cells(dict.Item(tag), 6) = value
'Assumes Total value on Row 21
Worksheets("holdings").Cells(dict.Item(tag), 7) = value * 100 / Worksheets("holdings").Cells(21, 2)
Else
targetRow = targetRow + 1
Worksheets("holdings").Cells(targetRow, 5) = tag
Worksheets("holdings").Cells(targetRow, 6) = Worksheets("holdings").Cells(i, 2).value
Worksheets("holdings").Cells(targetRow, 7) = Worksheets("holdings").Cells(i, 2).value * 100 / Worksheets("holdings").Cells(21, 2)
dict.Item(tag) = targetRow
End If
Next j
i = i + 1
If Len(Worksheets("holdings").Cells(i, 1)) = 0 Then more = False
Wend
End Sub