Como lo prometido es deuda y como ya hablamos en un post anterior, a continuación vamos a desglosar una serie de trucos Excel para el día a día, ya que es una herramienta muy útil y utilizada en cualquier oficina del mundo.

Nos vamos a centrar en un supuesto real, en el que trataremos de analizar sin un sistema gestor de bases de datos profesional cómo podría ser SQL Server, Oracle, MySQL o el propio Access.

Aplicando trucos Excel

Partiremos de una hoja de Excel con datos de ejemplos relacionados con nuestra empresa. Todos estos trucos también se podrían aplicar a una hoja de Excel, donde anotáramos datos de gastos personales.

En ella tendremos 3 columnas: Motivo del Gasto, Importe y Fecha. Y veremos cómo conseguir una tabla con los gastos por meses, entre semana, fines de semana, etc… (Los datos que ponemos han sido generados de manera totalmente aleatoria, en otra entrega contaremos como generar una tabla con datos aleatorios).

Ahora queremos sacar una tabla que nos diga lo que hemos gastado cada mes. Para ello utilizaremos la fórmula =SUMAR.SI()

En primer lugar, sacaremos un campo que nos diga el mes en el que se encuentra el gasto. Por este motivo, añadiremos una columna en la tabla donde lo calcularemos con la fórmula =MES()

La fórmula ‘mes’ se aplica directamente sobre el campo en el que queremos hacer el cálculo.

Bien, ahora lo que haremos será una tabla con la siguiente estructura:

Es importante tener una columna con el número del mes, pues es la que usaremos para calcular el gasto por cada mes.

Finalmente, debemos aplicar la siguiente fórmula en Gastos:

=SUMAR.SI(Hoja1!D:D;Hoja2!A2;Hoja1!B:B)

Os la explicamos: la columna “D:D” es donde se encuentra el mes, y es donde le tenemos que decir qué buscamos. El segundo parámetro “Hoja2!A2” es donde se encuentra el número de mes que buscamos, que en este caso sería junio. El último parámetro, es la columna “B:B” que se corresponde a la columna que hay que sumar. ¿Alguna duda? Déjanos tu comentario y te la resolvemos.

Tras aplicar esta fórmula nos quedaría la tabla así:

Ahora imagina que es una hoja de gastos personales y dices: “pero es que quiero ver lo que gasto los fines de semana…” no hay problema, lo podemos hacer de la siguiente forma:

Añadimos una columna con el día de la semana en la tabla principal. ¿Cómo? Con la fórmula =DIASEM, que nos dará un valor del 1 al 7, que nos quedara así:

En una segunda tabla haremos el siguiente cálculo:

¿Cómo la rellenamos?

Para el número de días usamos la función =CONTAR.SI, parecida al =SUMAR.SI. Lo que hace es contar 1 registro por cada campo que cumple la condición en una tabla, en este caso, la utilizaremos tal que así:

=CONTAR.SI(E:E;”<=5”);

Esto lo que hace es contar cada registro que en la fila “E:E” (donde está el día de la semana) sea menor o igual que 5 (menor o igual que viernes). Para fines de semana haremos lo mismo poniendo “>=6” (mayor o igual que sábado).

Ya tendríamos nuestra tabla así:

Ahora toca ver lo que hemos gastado en cada uno de los períodos. Para ellos volveremos a SUMAR.SI (os dejamos esta fórmula sin desvelar por si queréis probarlos vosotros mismos), y ya en la última columna simplemente sacamos el promedio entre la cantidad y el número de días, quedando así finalmente:

Bueno, es sólo un ejemplo práctico, que podéis llevar a cualquier situación, tanto de vuestra vida profesional cómo personal.

Adjuntamos en descarga el Excel “virgen” por si queréis practicar un poco. Estamos atentos a vuestros comentarios 😉

Ejemplo en descarga

Como os comunicamos en el anterior post, en Grupo IOE ofrecemos cursos de Excel aplicados directamente a las finanzas. Un programa que ofrece los recursos y los instrumentos prácticos necesarios para gestionar de forma eficaz las finanzas de una empresa, aprender a recoger, analizar y sintetizar toda la información económica, financiera y patrimonial de la empresa; y a gestionar los recursos disponibles, pudiendo tomar las decisiones más adecuadas a nivel financiero en función de esa información, apoyado siempre en la herramienta de Excel. Además, se adapta a cualquier tipo de perfil.

Puedes informarte de ello sin ningún tipo de compromiso 😉