En una tabla de grupos y miembros, ¿cómo reducir un determinado porcentaje de miembros por grupo?

Question

Tengo una hoja de cálculo de Excel con una tabla de grupos y miembros. Por ejemplo:

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

Lo que quiero hacer es reducir el número de miembros por grupo digamos en un 60%. No importa cuáles de los miembros quedan fuera. Por ejemplo, el grupo B contiene 2 miembros, por lo que quiero eliminar 1 miembro (2*0,6=1,2, por lo que debe redondearse hacia abajo; quiero que se redondee hacia arriba si fuera más de 0,5).

¿Es posible hacerlo con fórmulas de Excel?

Answer 1

Una tabla dinámica como la que ha mostrado @EngineerToast es una forma mucho más sencilla de hacer esto. Pero me encantan las buenas soluciones ridículamente diseñadas en exceso, así que aquí hay una forma de hacerlo en VBA. Sospecho que lo complicé demasiado con el Diccionario, probablemente podría haberlo hecho usando algún otro conteo rápido y luego agregando la letra ya utilizada a una matriz, ¡pero ahí 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 una acción única, puede hacerlo con una tabla dinámica. Si necesita hacerlo con frecuencia, este enfoque puede resultar complicado.

Primero, agregue encabezados de columna. Yo usé Groupy Name. seleccione cualquier celda de la lista y haga clic en "Tabla dinámica" en la cinta Insertar (botón del extremo izquierdo).

ÉldeberíaAdivine automáticamente el rango al que desea aplicar la tabla dinámica. Debería poder hacer clic en "Aceptar" para agregar su tabla dinámica a una nueva hoja. Luego, agregue los campos Groupy luego Namea la parte Filas de la tabla arrastrándolos desde la lista de campos a la lista Filas. También necesitas arrastraralgoen la parte de Valores o no le permitirá filtrar por el porcentaje superior.

Haga clic derecho en uno de los nombres en la tabla dinámica y luego haga clic en Filtro > Top 10...

Configure el filtro como desee y haga clic en Aceptar.

Para que se parezca más a sus datos originales, haga clic derecho en la tabla, vaya a Opciones de tabla dinámica y luego a la pestaña Mostrar y marque "Diseño de tabla dinámica clásica". A continuación, haga clic derecho en uno de los grupos en la tabla dinámica y haga clic en Configuración de campo. Establezca Subtotales en Ninguno y, en la pestaña Diseño e impresión, marque "Repetir etiquetas de elementos".