fórmula

Suma por año

Suma Por Ano

Suma por año

  Fórmula de Excel: Suma por año fórmula genérica
=SUMPRODUCT((YEAR(dates)=A1)*amounts)
Resumen

Para sumar valores por año, puede usar una fórmula basada en el función SUMAPRODUCTO junto con el función AÑO . En el ejemplo que se muestra, la fórmula en la celda G5 es:





=SUMPRODUCT((YEAR(data[Date])=F5)*data[Amount])

dónde datos es un Tabla de Excel en el rango B5:D16. A medida que la fórmula se copia, devuelve un total para cada año que se muestra en la columna F.

Explicación

En este ejemplo, el objetivo es calcular un total para cada año que aparece en la columna F. Todos los datos están en un Tabla de Excel llamó datos en el rango B5:D16. El principal desafío es que tenemos fechas en la columna B, pero no tenemos valores de años aislados con los que trabajar. La forma más sencilla de resolver este problema es con el función SUMAPRODUCTO utilizando la fórmula que se muestra en la celda G5. El problema también se puede resolver con el Función SUMAR.SI.CONJUNTO , o con un fórmula de matriz dinámica . Los tres enfoques se explican a continuación.





función SUMAPRODUCTO

En el ejemplo que se muestra, la fórmula en la celda G5 es:

=SUMPRODUCT((YEAR(data[Date])=F5)*data[Amount])

Trabajando de adentro hacia afuera, el primer paso es extraer los valores del año de las fechas en la columna B, lo cual se hace con la función AÑO:



YEAR(data[Date]) // get years

Como hay 12 fechas en la columna, AÑO devuelve 12 resultados en un formación como esto:

{2020;2020;2021;2022;2020;2021;2021;2022;2020;2021;2022;2022}

Cada año en esta matriz corresponde a una fecha en data[Date]. A continuación, los valores del año se comparan con el año en la celda F5:

YEAR(data[Date])=F5

El resultado es una matriz de 12 valores VERDADERO y FALSO como este:

{TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}

Observe que los valores VERDADEROS corresponden a fechas que ocurren en 2020, el valor en F5. A continuación, esta matriz se multiplica por las cantidades en la columna D. La operación matemática convierte automáticamente los valores VERDADERO y FALSO en 1 y 0, por lo que podemos visualizar esta operación de esta manera:

{1;1;0;0;1;0;0;0;1;0;0;0}*data[Amount]

Recuerde que los 1 en la primera matriz corresponden a fechas que ocurren en 2021, mientras que los 0 indican fechas en otros años. Los ceros en la primera matriz efectivamente 'cancelan' las cantidades en otros años. La matriz resultante se devuelve a SUMPRODUCT así:

=SUMPRODUCT({1500;1250;0;0;1850;0;0;0;1250;0;0;0})

Con solo una matriz para procesar, SUMPRODUCT suma la matriz y devuelve un resultado final de 5850. A medida que se copia la fórmula, la referencia de celda F5, que es relativa, cambia en cada nueva fila y obtenemos el total correcto para cada año. mostrado.

Función SUMAR.SI.CONJUNTO

Otra forma de resolver este problema es con la función SUMAR.SI.CONJUNTO y una fórmula como esta:

=SUMIFS(data[Amount],data[Date],">="&DATE(O5,1,1),data[Date],"<="&DATE(O5,12,31))

Si es nuevo en SUMIFS, Este artículo cubre lo básico.

Esta fórmula es más complicada que la fórmula SUMAPRODUCTO porque SUMAR.FIS no tiene forma de obtener los valores del año directamente. Esta limitación es discutido con más detalle aquí . En cambio, necesitamos crear dos fechas para cada año con el Función FECHA :

DATE(O5,1,1) // start date
DATE(O5,12,31) // end date

Para la fecha de inicio, simplemente codificamos 1 para mes y día argumentos para obtener una fecha del 1 de enero. Para la fecha de finalización, codificamos 12 para mes y 31 para día para crear una fecha de 31 de diciembre. Para ambas fechas, año proviene de la celda O5, que contiene 2020.

También necesitamos operadores lógicos para 'entre paréntesis' cada año. Nosotros concatenar los operadores que necesitamos así:

">="&DATE(O5,1,1) // start date
"<="&DATE(O5,12,31) // end date

El requisito de concatenar operadores como este es nuevamente una 'característica' de la función SUMAR.SI.CONJUNTO, que comparte esta sintaxis peculiar con otras ocho funciones .

Después de evaluar la función FECHA y completar la concatenación, tenemos:

=SUMIFS(data[Amount],data[Date],">=43831",data[Date],"<=44196")

El número 43831 es el Fecha del número de serie de Excel para el 1 de enero de 2020. El número 44196 es el número de serie para el 31 de diciembre de 2020.

acceso directo de Excel seleccionar al final de la columna

Nota: con cualquiera de las dos opciones anteriores, puede usar el Función ÚNICA para obtener los años únicos a partir de las fechas como paso inicial, si tiene una versión moderna de Excel.

Solución de matriz dinámica

También es posible resolver este problema con una única fórmula de matriz dinámica como esta:

=LET(
years,YEAR(data[Date]),
values,data[Amount],
uyears,SORT(UNIQUE(years)),
sums, BYROW(uyears, LAMBDA(r, SUM(--(years=r)*values))),
VSTACK({"Year","Total"},HSTACK(uyears, sums))
)

Explicación próximamente.

Autor david bruns


^