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

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

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

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

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

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

Установите фильтр на все, что вы хотите, и нажмите ОК.

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

Answer 2

Сводная таблица, подобная показанной @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