Excel Gratis

Plantillas de Excel gratis, sin contraseñas

Plantilla en libro de Excel que nos puede servir de ayuda o referencia para sumar las cantidades por meses de una lista por categorías, conceptos, etc.
Lo primero que tenemos que hacer es crear una lista por categorías, conceptos, etc. En la plantilla hay creada a modo de ejemplo una lista por categorías de los diversos gastos de hogar, que podemos modificar/cambiar o aumentar la existente.
Si la lista se hace por categorías debemos tener en cuenta que para llevar un buen control cada una debe incluir todos los criterios correspondientes a la categoría. También podemos hacer una lista personalizada con los criterios o referencia que deseemos que nos sume por la referencia y mes.
Podemos hacer una lista independiente indicando a que se refiere cada uno, por ejemplo, si es de Seguros, como puede ser:
Seguro coche
Seguro Salud
Seguro Vida …. Etc.
O incluirlos todos en una Categoría que con el nombre Seguros / Seguros Varios, etc.
En la columna A de la hoja Datos es donde creamos la lista que deseemos desde la celda A5 hacia abajo.
Una vez que hemos creado la lista nos aparecerá en la hoja plantilla en la columna desde la celda B5 hacia abajo que es donde vamos seleccionando la categoría/concepto o lo que proceda.
Desde la celda C6 hacia abajo ahí ponemos la referencia que deseamos si se hizo por Categorías y de la lista seleccionamos Seguro, podemos poner lo que proceda, seguro coche, salud, etc.
En esta misma hoja en la columna A desde la celda A6 (se introduce la fecha)
En la columna D desde la celda D6 es donde se introduce la cantidad.
Después de introducir los datos correspondientes para saber la suma de las cantidades dentro de un rango de fechas introducimos la Fecha inicio en la celda D2 y fecha final en la celda D3 y suma las cantidades de la columna D, que se encuentre dentro de ese rango.
La hoja Datos suma automáticamente las cantidades por meses de la lista que se ha creado, sólo hay que introducir el año en la celda M3.
Algunas celdas tienen un formato distinto, si lo deseamos lo podemos cambiar, tenemos que ir a la pestaña INICIO > Formato condicional y, hacemos clic en Administrar reglas.
La lista desplegable que aparecer en la columna B de la hoja plantilla, desde la celda B6, si deseamos modificar la Validación de las celdas tenemos que ir a la pestaña DATOS >Validación de datos y, en validación seleccionamos: configuración, mensaje de entrada o mensaje de error.
Datos:
En este libro se han aplicado fórmulas con diferentes criterios que hacen referencias a celdas o rango de celdas, de la hoja plantilla el rango de celdas son las siguientes:
Columna A (fechas desde la celda A6 hasta A50000)
Columna B (Categorías desde la celda B6 hasta B5000)
Columna D (Cantidad desde la celda D6 hasta D50000)
De la hoja datos se encuentras las siguientes referencias que se indican en las fórmulas:
En la celda M3 (que es fija y es donde se introduce el año)
Y otras que se refiere a las categorías que va desde la celda A5 hasta A33
En la plantilla Datos para sumar las cantidades del mes de enero de la categoría que se encuentra en la celda A5 de la hoja Datos y el año que se encuentra en la celda M3 también de la hoja Datos los demás datos e encuentran en la hoja Plantilla como se indica en la fórmula y rango indicado

=SUMAPRODUCTO(((MES('Plantilla '!$A$6:$A$50000)=1)*('Plantilla '!$B$6:$B$50000=A5)*(AÑO('Plantilla '!$A$6:$A$50000)=$M$3)*'Plantilla '!$D$6:$D$50000))

Para el mes de febrero cambiamos en la fórmula 1 por 2
=SUMAPRODUCTO(((MES('Plantilla '!$A$6:$A$50000)=2)*('Plantilla '!$B$6:$B$50000=A5)*(AÑO('Plantilla '!$A$6:$A$50000)=$M$3)*'Plantilla '!$D$6:$D$50000))

El año se encuentra en la celda M3, si deseamos poner directamente el año, por ejemplo 2018 cambiamos en la fórmula la referencia de la celda M3 por 2018
=SUMAPRODUCTO(((MES('Plantilla '!$A$6:$A$50000)=1)*('Plantilla '!$B$6:$B$50000=A5)*(AÑO('Plantilla '!$A$6:$A$50000)=2018)*'Plantilla '!$D$6:$D$50000))

Si deseamos cambiar el año y poner directamente el nombre de la categoría, cambiamos también en la fórmula la referencia de la celda A5 por la categoría que deseamos en este caso vamos a poner “Vivienda, agua, electricidad, gas y otros combustibles” y quedaría así la fórmula.
=SUMAPRODUCTO(((MES('Plantilla '!$A$6:$A$50000)=1)*('Plantilla '!$B$6:$B$50000="Vivienda, agua, electricidad, gas y otros combustibles")*(AÑO('Plantilla '!$A$6:$A$50000)=2018)*'Plantilla '!$D$6:$D$50000))

Podemos utilizar también otras fórmulas que nos devolverían el mismo resultado, teniendo en cuenta las mismas referencias que se indican anteriormente, pero en esta fórmula deseamos que coincida el mes de la celda B4 que se encuentra en escrito en letra que puede ser enero, febrero, etc., y se aplica la siguiente fórmula:
=SUMAPRODUCTO(((MES('Plantilla '!$A$6:$A$50000)=ELEGIR(COINCIDIR(B4;{"Enero";"Febrero";"Marzo";"Abril";"Mayo";"Junio";"Julio";"Agosto";"Septiembre";"Octubre";"Noviembre";"Diciembre"};0);1;2;3;4;5;6;7;8;9;10;11;12))*AÑO('Plantilla '!$A$6:$A$50000)=M3)*(('Plantilla '!$B$6:$B$50000)=A5)*'Plantilla '!$D$6:$D$50000)

La siguiente fórmula es la misma que la anterior lo único que cambia es la referencia que se hace al mes de la celda B4 y la referencia al año M3, se ha puesto el mes de “Enero” en vez de B4 y 2018 en vez de M3 y se aplicó la siguiente formula:
=SUMAPRODUCTO(((MES('Plantilla '!$A$6:$A$50000)=ELEGIR(COINCIDIR("Enero";{"Enero";"Febrero";"Marzo";"Abril";"Mayo";"Junio";"Julio";"Agosto";"Septiembre";"Octubre";"Noviembre";"Diciembre"};0);1;2;3;4;5;6;7;8;9;10;11;12))*AÑO('Plantilla '!$A$6:$A$50000)=2018)*(('Plantilla '!$B$6:$B$50000)=A5)*'Plantilla '!$D$6:$D$50000)

En la siguiente fórmula es la misma fórmula lo que se cambia es la celda que hace referencia a la categoría celda A5 por =”Vivienda, agua, electricidad, gas y otros combustibles” como se puede comprobar:
=SUMAPRODUCTO(((MES('Plantilla '!$A$6:$A$50000)=ELEGIR(COINCIDIR("Enero";{"Enero";"Febrero";"Marzo";"Abril";"Mayo";"Junio";"Julio";"Agosto";"Septiembre";"Octubre";"Noviembre";"Diciembre"};0);1;2;3;4;5;6;7;8;9;10;11;12))*AÑO('Plantilla '!$A$6:$A$50000)=2018)*(('Plantilla '!$B$6:$B$50000)="Vivienda, agua, electricidad, gas y otros combustibles")*'Plantilla '!$D$6:$D$50000)

En la anterior fórmula el mes es “Enero”, también podemos hacer referencia al mes que se encuentra en la hoja Datos celda B4 y quedaría así la primer parte de la fórmula:
=SUMAPRODUCTO(((MES(‘Plantilla ‘!$A$6:$A$50000)=ELEGIR(COINCIDIR(B4;

Por lo tanto, lo que tenemos que tener en cuenta es si la referencia se encuentra en la misma hoja u en otra.
Por ejemplo:
En la fórmula primera la celda que hace referencia al año se encuentra en la celda M3 de la hoja Datos que es donde está puesta la fórmula, sin embargo si estuviese en la hoja Plantilla de la celda M3 hacemos referencia a esa celda y la hoja donde se encuentra ‘Plantilla ‘!M3 como se indica a continuación.
=SUMAPRODUCTO(((MES('Plantilla '!$A$6:$A$50000)=1)*('Plantilla '!$B$6:$B$50000=A5)*(AÑO('Plantilla '!$A$6:$A$50000)='Plantilla '!M3)*'Plantilla '!$D$6:$D$50000))

Las fórmulas se aplican en la misma celda, aunque aparezca aquí separado el texto es toda la misma fórmula y no se separa.

Aquí dejo la imagen de la hoja plantilla donde se van introduciendo los datos en los campos correspondientes (clic para ampliar)

La siguiente imagen es de la hoja Datos, donde introducimos la lista correspondiente por categorias o lo que eligamos (clic para ampliar)

La plantilla está protegida con contraseña para que no se borren las fórmulas (la contraseña está en blanco), para desproteger la hoja Seleccionamos en la pestaña Rvisar y hacemos clic en Desproteger hoja.
Para descargar el libro de Excel con la platilla hacemos clic en el siguiente enlace:
Plantilla suma por meses y categorías

Hojas de Excel gratis

2 Responses so far.

  1. Rafael says:

    Hola … de antemano gracias

  2. Rafael says:

    Gracias… realmente la necesito