Как в таблице групп и участников уменьшить определенный процент участников в каждой группе?

Question

У меня есть таблица Excel с таблицей групп и участников. Например:

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

Я хочу уменьшить количество участников в группе, скажем, на 60%. Неважно, какие из участников будут исключены. Например, группа B содержит 2 участников, поэтому я хочу удалить 1 участника (2*0,6=1,2, поэтому округление должно быть выполнено в меньшую сторону, а если больше 0,5, то округление должно быть выполнено в большую сторону).

Можно ли это сделать с помощью формул Excel?

Answer 1

Сводная таблица, как показал @EngineerToast, — гораздо более простой способ сделать это. Но мне нравятся хорошие смехотворно переусложненные решения, поэтому вот один из способов сделать это в VBA. Я подозреваю, что я слишком усложнил это со словарем, возможно, можно было бы сделать это просто с помощью какого-то другого быстрого подсчета, а затем добавить уже использованную букву в массив, но вот что мы имеем..!

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

Для одноразового действия вы можете сделать это с помощью сводной таблицы. Если вам нужно делать это часто, этот подход может стать неуклюжим.

Сначала добавьте заголовки столбцов. Я использовал Groupи Name. выберите любую ячейку в списке и нажмите «Сводная таблица» на ленте «Вставка» (крайняя левая кнопка).

Этодолженавтоматически угадывает диапазон, к которому вы хотите применить сводную таблицу. Вы должны просто нажать «ОК», чтобы добавить сводную таблицу на новый лист. Затем добавьте поля, Groupа затем Nameв раздел «Строки» таблицы, перетащив их из списка полей в список «Строки». Вам также нужно перетащитьчто-нибудьв раздел «Значения», иначе вы не сможете отфильтровать верхний процент.

Щелкните правой кнопкой мыши по одному из имен в сводной таблице, а затем нажмите Фильтр > Топ-10...

Настройте фильтр по своему усмотрению и нажмите «ОК».

Чтобы сделать его более похожим на исходные данные, щелкните правой кнопкой мыши по таблице, перейдите в «Параметры сводной таблицы», затем на вкладке «Отображение» и отметьте «Классический макет сводной таблицы». Затем щелкните правой кнопкой мыши по одной из групп в сводной таблице и выберите «Параметры полей». Установите для параметра «Промежуточные итоги» значение «Нет», а на вкладке «Макет и печать» отметьте «Повторять метки элементов».