Recuentos transpuestos para muchas columnas.

Question

La entrada:

Tengo que contar el número de '1' y '0' en cada columna de esta manera:

¿Hay alguna forma en Excel/macro de hacer esto?

Answer 1

Si decimos que las columnas comienzan en "D1"e imprimimos hasta "A3", podría verse así:

Sub countAndTranspose()
Dim section As Range, count As Range, entry As Range, sectionPrint As Range

Set section = Range("D1", Cells(1, Columns.count).End(xlToLeft)) 'All headers
For Each entry In section
    Set sectionPrint = Cells(Rows.count, 1).End(xlUp) 'Last row in "A"
    Set count = Range(Cells(entry.Row + 1, entry.Column), Cells(Rows.count, entry.Column).End(xlUp))
    sectionPrint.Offset(1).Value = entry.Value 'Print header below last row in A
    sectionPrint.Offset(1, 1).Value = WorksheetFunction.CountIf(count, 1) 'Print sum of 1
    sectionPrint.Offset(1, 2).Value = WorksheetFunction.CountIf(count, 0) 'Print sum of 0
Next entry

End Sub

Comenzamos definiendo nuestro rango de columnas, comenzando "D1"y yendo hacia la luz más alejada de la fila.
Luego recorremos este rango y elegimos dónde imprimir el nombre de la columna (A3 y abajo deben estar VACÍOS)

Answer 2

Solución de consulta de energía de Excel

Resultado:

---

Para profesionales- procedimiento completo:

let
    Source = Excel.CurrentWorkbook(),
    #"Expanded Content" = Table.ExpandTableColumn(Source, "Content", {"OPERATION", "MEDIA", "IT"}, {"OPERATION", "MEDIA", "IT"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Content",{"Name"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Attribute", "Value"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", {{"Value", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"Value", type text}}, "en-US")[Value]), "Value", "Count", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"Attribute", "1", "0"})
in
    #"Replaced Value"

---

Para otros- pasos detallados.

Seleccione su tabla de datos de entrada (incluidos los encabezados).
Insertar -> Tabla

DE ACUERDO

Haga clic en las flechas dobles junto a "Contenido"
DesmarqueUtilice el nombre de la columna original como prefijo

DE ACUERDO

Haga clic derecho en la columna "Nombre" -> Eliminar

Seleccione toda la tabla
Transformar -> Desvincular columnas

Inicio -> Agrupar por

DE ACUERDO

Seleccione la columna "Valor"
Transformar -> Columna dinámica
como columna de valores seleccione "Contar"

DE ACUERDO

Seleccione toda la tabla
Transformar -> Reemplazar valores
Complete así:

DE ACUERDO

Inicio -> Cerrar y cargar

Answer 3

Debajo de las entradas de las columnas de OPERACIÓN, MEDIOS y TI, ingrese dos fórmulas:

COUNTIF ( A2:A10, 1 )por unos
COUNTIF ( A2:A10, 0 )por ceros.

Copie las fórmulas debajo de MEDIOS y TI.

Answer 4

Para una solución que no sea VBA, puede aprovechar LET si tiene Excel 2016 o Microsoft 365. Esto también funcionará en Excel para Mac (a diferencia de Power BI).

=LET( array, A1:E4,
       hdr, INDEX( array, 1, ),
       rSeqBody, SEQUENCE( ROWS( array ) - 1 ),
       cSeq, SEQUENCE( 1, COLUMNS( array ) ),
       body, INDEX( array, rSeqBody + 1, cSeq ),
       onesCount, MMULT( TRANSPOSE( SIGN( rSeqBody ) ), body ),
       t1s, IFERROR( INDEX( TRANSPOSE( hdr ), TRANSPOSE( cSeq ), {1,2} ), TRANSPOSE( onesCount ) ),
       t10s, IFERROR( INDEX( t1s, TRANSPOSE( cSeq ), {1,2,3} ), ROWS( body ) - TRANSPOSE( onesCount ) ),
       t10s )

Esta es una aplicación de ejemplo:

Donde la entrada es la matriz, incluida la fila del encabezado con los nombres de los campos y la salida es una matriz dinámica que se extiende a las celdas de abajo y una columna a la derecha.

Cómo funciona

Primero obtiene los nombres de los campos y los coloca en una matriz llamadahdr. Luego crea secuencias de filas y columnas para calcular y dar forma a los resultados. Luego crea una matriz que contiene los 1 y 0 llamadacuerpo. Luego suma los 1 por columna enunos cuentanusando una multiplicación de matrices.

Esto sin contar los 1. Es aprovechar que se pueden sumar. Si fuera un símbolo, como "x", no funcionaría. Del mismo modo, los ceros no se cuentan, sino que simplemente se calculan a partir de los unos.

Para entregar el resultado, une el hdr.(transpuesto)sobre los unosCuenta(transpuesto)sobreindexando las columnas del hdr transpuesto para forzar errores en la columna 2 de cada fila, luego usa IFERROR para reemplazar los errores !REF# con los onesCount transpuestos.

Esto se repite una vez más para agregar una columna de recuento de 0 que se calcula restando el recuento de 1 del número de filas en la columna.cuerpo.

Si desea el encabezado 1COUNT y 0COUNT, también puede coserlo, pero probablemente sea más fácil escribirlo en la hoja de trabajo con el texto que desee.

Esto también es posible sin LET, pero es complicado y difícil de depurar. LET brinda claridad y elimina la necesidad de células auxiliares.