Suma por trimestre

=SUMIFS(values,quarters,A1)

Para sumar valores por trimestre, puede usar una fórmula basada en el Función SUMAR.SI.CONJUNTO junto con un columna auxiliar que contiene cuartos. En el ejemplo que se muestra, la fórmula en G5 es:

=SUMIFS(data[Amount],data[Qtr],F5)

dónde datos es un Tabla de Excel en el rango B5:D16, y los trimestres en la columna E se genera otra fórmula que se detalla a continuación.

En este ejemplo, el objetivo es sumar los montos de la columna C por trimestre, utilizando las fechas de la columna B para determinar los trimestres. Los números de los trimestres en la columna F se ingresan manualmente. Los resultados finales deben aparecer en la columna G. Todos los datos están en una tabla de Excel llamada datos en el rango B5:E16. Este problema se puede solucionar con el Función SUMAR.SI.CONJUNTO y una columna de ayuda, o sin una columna de ayuda usando el función SUMAPRODUCTO . Ambos enfoques se explican a continuación.

Cálculo de trimestres

El primer paso en este problema es generar un trimestre para cada fecha en la columna B. En la tabla que se muestra, la columna D es una columna auxiliar con números de trimestre calculados con una fórmula separada. La fórmula en D5, copiada hacia abajo, es:

=ROUNDUP(MONTH([@Date])/3,0) // get quarter

Nota: porque estamos usando un Tabla de Excel para almacenar los datos, obtenemos automáticamente el referencia estructurada visto arriba. La referencia [@Date] significa: fila actual en la columna Fecha. Si es nuevo en referencias estructuradas, vea este breve video: Introducción a las referencias estructuradas .

los Función MES devuelve un número de mes entre 1 y 12 para cada fecha, que se divide por 3. El función REDONDEAR luego se usa para redondear el resultado al número entero más cercano. Esta fórmula es explicado con más detalle aquí .

solución SUMAR.FIS

El próximo paso en el problema es sumar las cantidades en la columna C usando los números de trimestre en la columna D. Esto se puede hacer fácilmente con el Función SUMAR.SI.CONJUNTO . La función SUMAR.SI.CONJUNTO está diseñada para sumar valores en rangos condicionalmente basado en múltiples criterios. La firma de la función SUMAR.SI.CONJUNTO se ve así:

que es una referencia de celda absoluta

=SUMIFS(sum_range,range1,criteria1,range2,criteria2,...)

Observe la rango suma viene primero, seguido de rango/criterio pares Cada par de argumentos rango/criterio representa otra condición.

En este caso, necesitamos configurar SUMAR.SI.CONJUNTO para sumar valores por número de trimestre usando solo una condición: necesitamos verificar el trimestre en la columna D para una coincidencia en el trimestre en la columna F. Comenzamos con el rango suma :

=SUMIFS(data[Amount]

A continuación, añadimos criterios como rango/criterio par, donde criterio_rango1 es el Fecha columna, y criterio1 es el cuarto número en la columna F:

=SUMIFS(data[Amount],data[Qtr],F5)

A medida que se copia la fórmula, obtenemos un total para cada trimestre en la columna F.

SUMPRODUCT sin ayudante

También es posible resolver este problema sin una columna de ayuda con la función SUMAPRODUCTO como esta:

=SUMPRODUCT((ROUNDUP(MONTH(data[Date])/3,0)=F5)*data[Amount])

Trabajando de adentro hacia afuera, la primera parte de la expresión dentro de SUMAPRODUCTO genera un número de trimestre para cada fecha en la columna Fecha como esta:

ROUNDUP(MONTH(data[Date])/3,0)

Esta es básicamente la misma fórmula utilizada anteriormente, la diferencia es que alimentamos el Función MES la totalidad fecha de Datos] columna en lugar de una celda. Como hay 12 fechas en la columna, obtenemos un formación que contiene números de 12 meses como este:

{1;2;3;4;5;6;7;8;9;10;11;12}

Esta matriz se entrega a la función ROUNDUP como el número argumento:

ROUNDUP({1;2;3;4;5;6;7;8;9;10;11;12}/3,0)

Y ROUNDUP devuelve una matriz de 12 números de trimestre:

{1;1;1;2;2;2;3;3;3;4;4;4}

Nota: estamos usando un conjunto de datos muy pequeño en este ejemplo por simplicidad, pero el mismo enfoque manejará cientos o miles de fechas.

A continuación, la matriz de ROUNDUP se compara con F5 y el resultado es una matriz que contiene 12 valores VERDADERO y FALSO. Cuando esta matriz se multiplica por datos[Cantidad] , la operación matemática cambia los valores VERDADERO y FALSO a 1 y 0. En este punto tenemos:

cómo poner la contraseña en el archivo de Excel 2013

=SUMPRODUCT({1;1;1;0;0;0;0;0;0;0;0;0}*data[Amount])

Multiplicar las dos matrices juntas da como resultado una sola matriz. En esta matriz, solo sobreviven los montos asociados con el trimestre 1; los montos de otros trimestres se 'reducen a cero' de manera efectiva:

=SUMPRODUCT({127;130;450;0;0;0;0;0;0;0;0;0})

Con solo una matriz para procesar, SUMPRODUCT suma los valores de la matriz y devuelve un resultado final, 707. Como la fórmula se copia en la columna G, devuelve un total para cada trimestre, sin necesidad de una columna auxiliar.