Numa tabela de grupos e membros, como reduzir um determinado percentual de membros por grupo?

Question

Tenho uma planilha Excel com uma tabela de grupos e membros. Por exemplo:

A Jonathan
A Roger
A Donald
B John
B Lilya
C Richard
C Lorry

O que eu quero fazer é reduzir o número de membros por grupo em, digamos, 60%. Não importa quais dos membros ficam de fora. Por exemplo, o grupo B contém 2 membros, então quero remover 1 membro (2*0,6=1,2 então deve ser arredondado para baixo, quero que seja arredondado para cima se for maior que 0,5).

É possível fazer isso com fórmulas do Excel?

Answer 1

Uma tabela dinâmica como o @EngineerToast mostrou é uma maneira muito mais simples de fazer isso. Mas eu adoro uma boa solução ridiculamente projetada, então aqui está uma maneira de fazer isso em VBA. Suspeito que compliquei demais com o Dicionário, provavelmente poderia ter feito isso apenas usando alguma outra contagem rápida e depois adicionando a letra já usada a um array, mas aí estamos ..!

Public Sub ReduceMembers()
    Dim GroupColumn: GroupColumn = 1                        'Original column containing your Groups
    Dim MemberColumn: MemberColumn = 2                      'Original column containing your Members
    Dim ReductionAmount: ReductionAmount = 0.6              'Percentage as a decimal fraction
    Range("D1").Select                                      'Start cell for your new columns

    Dim LastRow: LastRow = GetLastRow(GroupColumn)
    Dim Dictionary, DictionaryKey, I
    Set Dictionary = CreateObject("Scripting.Dictionary")

    For I = 1 To LastRow
        Dim Value: Value = Cells(I, GroupColumn).Value
        If Dictionary.Exists(Value) Then
            Dictionary(Value) = Dictionary(Value) + 1
        Else
            Dictionary.Add Value, 1
        End If
    Next

    For Each DictionaryKey In Dictionary.Keys
        Dim MaxAmount: MaxAmount = Math.Round(Dictionary(DictionaryKey) * ReductionAmount, 0)
        Dim CurrentAmount: CurrentAmount = 0
        For I = 1 To LastRow
            If Cells(I, GroupColumn) = DictionaryKey Then
                If CurrentAmount >= MaxAmount Then
                    Exit For
                End If
                ActiveCell.Value = DictionaryKey
                ActiveCell.Offset(0, 1).Value = Cells(I, MemberColumn).Value
                ActiveCell.Offset(1, 0).Select

                CurrentAmount = CurrentAmount + 1
            End If
        Next
    Next

End Sub

Function GetLastRow(GroupColumn)
    Dim Count
    Count = 0
    Do
        Count = Count + 1
    Loop Until IsEmpty(Cells(Count, GroupColumn).Value)
    GetLastRow = Count - 1
End Function

Answer 2

Para uma ação única, você pode fazer isso com uma tabela dinâmica. Se você precisar fazer isso com frequência, essa abordagem pode ficar desajeitada.

Primeiro, adicione títulos de coluna. Eu usei Groupe Name. selecione qualquer célula da lista e clique em "Tabela Dinâmica" na faixa Inserir (botão mais à esquerda).

Istodeveadivinhe automaticamente o intervalo ao qual deseja que a tabela dinâmica seja aplicada. Você deve apenas conseguir clicar em "OK" para adicionar sua tabela dinâmica a uma nova planilha. Em seguida, adicione os campos Groupe, em seguida, Nameà parte Linhas da tabela, arrastando-os da lista de campos para a lista Linhas. Você também precisa arrastaralgona parte Valores ou não permitirá filtrar a porcentagem superior.

Clique com o botão direito em um dos nomes da tabela dinâmica e clique em Filtro > 10 principais...

Configure o filtro como desejar e clique em OK.

Para torná-lo mais parecido com seus dados originais, clique com o botão direito na tabela, vá para Opções da Tabela Dinâmica e depois na guia Exibir e marque "Layout da Tabela Dinâmica Clássica". Em seguida, clique com o botão direito em um dos grupos da tabela dinâmica e clique em Configurações de campo. Defina Subtotais como Nenhum e, na guia Layout e impressão, marque "Repetir rótulos de itens".