Excel根据开始和结束编号以及标记创建列表

Question

我正在尝试从分组值的索引创建一个列表。

这与this非常相似，但是我的群组也有“标签”，这会使列表复杂化。

下面是我的索引选项卡的一个示例：

  | A  | B  | C  |  D  |
-------------------------
1 | 1  | 1  | 1  | CV  | 
2 | 1  | 2  | 2  | IS  |
3 | 1  | 3  | 3  | IS  |
4 | 2  | 4  | 5  | GN  |
5 | 2  | 6  | 7  | PS  |
6 | 4  | 8  | 11 | SQ  |
7 | 2  | 12 | 13 | SS  |
8 | 1  | 14 | 14 | AT  |
9 | 15 | 15 | 29 | AT  |
10| 4  | 30 | 33 | TYP |

其中A是页数，B是第一页，C是最后一页，D是标记。我还想添加列，以便我可以保持标签的运行计数。

  | A  | B  | C  |  D  |  E   |  F   |
---------------------------------------
1 | 1  | 1  | 1  | CV  | CV1  | CV1  |  
2 | 1  | 2  | 2  | IS  | IS1  | IS1  |
3 | 1  | 3  | 3  | IS  | IS2  | IS2  |
4 | 2  | 4  | 5  | GN  | GN1  | GN2  |
5 | 2  | 6  | 7  | PS  | PS1  | PS2  |
6 | 4  | 8  | 11 | SQ  | SQ1  | SQ4  |
7 | 2  | 12 | 13 | SS  | SS1  | SS2  |
8 | 1  | 14 | 14 | AT  | AT1  | AT1  |
9 | 15 | 15 | 29 | AT  | AT2  | AT16 |
10| 4  | 30 | 33 | TYP | TYP1 | TYP4 |

请注意，标记可以多次出现，并且它可能不在连续的行中。

下面是我想要的LIST标签页的样子：

  | A   |
---------
1 | CV1 |
2 | IS1 |
3 | IS2 |
4 | GN1 |
5 | GN2 |
6 | PS1 |
7 | PS2 |
8 | SQ1 |
9 | SQ2 |
10| SQ3 |
11| SQ4 |
and so on...

如何通过公式向索引页签添加附加列？

如何通过公式创建列表？(...is这是可能的吗？)

Answer 1

这些公式应该非常容易编写。想一想你想要实现的目标。

您的第一个公式(在E列中)只是对标签进行了连续计数(在D列中)。因此，您希望对从第一个标记到标记名称相同的相应标记的所有单元格进行计数。该计数将被附加到标记名。

=$D1 & COUNTIF($D$1:$D1, $D1)

第二个公式(在F列中)只是对页面计数(在A列中)进行累加。因此，您想要取从第一个标记到标记名称相同的相应标记的所有相应页面计数的总和。该和将被附加到标记名。

=$D1 & SUMIF($D$1:$D1, $D1, $A$1:$A1)

注意，列不会改变，范围的起始行也不会改变(因此需要使用绝对范围)。唯一变化的是标记的行和结束范围的行。

我认为不可能通过简单的公式来生成该列表。据我所知，公式需要与另一个范围一一对应。一个范围可以产生多个值，所以公式不会减少它。您需要编写一个VBA脚本来生成该脚本。

Sub GenerateList()

    Dim usedRange As Range
    Dim count As Dictionary

    Set usedRange = Worksheets("Index").usedRange
    Set count = CountValues(usedRange)

    Dim output As Range
    Dim row As Integer
    Dim key As Variant

    Set output = Worksheets("List").Columns("A").Rows
    output.ClearContents
    row = 1
    For Each key In count.Keys()
        Dim i As Integer

        For i = 1 To count(key)
            output(row) = key & i
            row = row + 1
        Next i
    Next key

End Sub

Function CountValues( _
        usedRange As Range, _
        Optional tagsColumn As String = "D", _
        Optional valuesColumn As String = "A") As Dictionary

    Dim tags As Range
    Dim values As Range

    Set tags = usedRange.Columns(tagsColumn).Rows
    Set values = usedRange.Columns(valuesColumn).Rows

    Dim map As New Dictionary
    Dim tag As Range
    For Each tag In tags
        map(tag.Value) = map(tag.Value) + values(tag.row)
    Next tag

    Set CountValues = map

End Function

它使用Dictionary，因此您必须引用脚本运行时。

Answer 2

听起来你只是想在一个单独的工作表上获得一个“唯一值”的列表，作为你的列表。尝试这些页面，有多个VBA方法可以在一个范围内粘贴唯一项。

此外，高级筛选器还可以选择将唯一值粘贴到另一个位置。所以你的任何重复标签都不会出现在这个列表中，只出现在你的“列表”标签中。

不管怎样，我不确定这是不是你想要的，但是这个问题有点模糊。

链接如下：

Create Unique list

Create Unique list 2