Agregar recuentos acumulativos a una tabla dinámica en Excel

Question

Tengo datos en Excel que se ven así:

Shop   | Division    | Launch Year
-------+-------------+-------------
Shop 1 | Division 1  | 2005
Shop 2 | Division 1  | 2006
Shop A | Division 2  | 2005
Shop B | Division 2  | 2007
...

Etcétera. Hay alrededor de 100 tiendas en total en 4 divisiones. Necesito obtener una tabla que muestre cuántas tiendas están abiertas al final de cada año, por división. Con una tabla dinámica, puedo mostrar fácilmente cuántas tiendas nuevas se abrieron durante ese año, pero no cuántas en total.

Sé cómo sumar totales acumulados a una tabla dinámica, pero en mi caso eso no funciona, ya que no tengo números para sumar; en su lugar, necesito agregar recuentos acumulados. ¿Cómo se puede hacer eso?

Estoy buscando este resultado final:

Year | Division 1 | Division 2 | ...
-----+------------+------------+----
2005 | 1          | 1          |
2006 | 2          | 1          |
2007 | 2          | 2          |
...

Answer 1

Un conteo continuo es un total acumulado de 1.
Si los datos lo permiten, simplemente agregue una columna adicional llena de unos y utilícelas en su pivote para obtener un total acumulado.

Answer 2

Si su tabla es relativamente estática (las columnas y filas realmente no cambian mucho), utilice esos encabezados de columna y encabezados de fila. En el cuerpo, utilice fórmulas como (en pseudocódigo):

=countifs('Launch Year',"<="&'row header', 'Division', 'column header')

Si utiliza el anclaje adecuado, puede crear la fórmula una vez y copiarla y pegarla en todo el cuerpo.

Answer 3

No necesitas una tabla dinámica para esto.

Cree su tabla como se muestra en el resultado deseado. Complete el área de datos con la siguiente fórmula matricial, ingresada Ctrl Shift Entery luego copiada hacia arriba y hacia abajo:

=SUMPRODUCT(--IF($C$2:$C$5<=$E2,1,0),IF($B$2:$B$5=F$1,1,0))

Actualice las referencias de las celdas según sea necesario, prestando atención al anclaje.

Función sumaproductodocumentaciónde EM.

Está utilizando IFdeclaraciones dos que generan dos matrices de valores coincidentes de tamaño idéntico (los --giros TRUEa 1y FALSEa 0), multiplicándolos entre sí para encontrar registros que coincidan con ambos criterios y luego sumando la lista resultante de 1's y 0's para obtener su total.

EDITAR: Para responder a su comentario/pregunta

La configuración que se muestra aquí hasta ahora es una pequeña tabla de datos y resultados para prueba de concepto. Para hacer esto más extensible, probablemente convertirá la tabla de entrada en una tabla con nombre. Para hacer esto, seleccione cualquier celda en el rango relevante (ejemplo: B4en la configuración como se muestra) y presione Ctrl+ T, luego confirme en la ventana modal resultante. Eso facilitará la escritura de referencias de celda y actualizará automáticamente cualquier referencia de celda a esos rangos de datos a medida que se agreguen datos.

También puede convertir la tabla de resultados en una tabla utilizando el mismo método. De esta manera, cuando agregas un nuevo año, todo lo que tienes que hacer es ingresar el año en la siguiente fila debajo de esa tabla y todas las fórmulas se pegarán automáticamente:

Tenga en cuenta que esta actualización automática de la tabla de resultados no funcionará para agregar una nueva División. La tabla se expandirá para acomodarla, pero tendrás que copiar manualmente la SUMPRODUCTfórmula en el nuevo campo.

¡Buena suerte y disfruta!