Ejemplos2

Tenemos las siguientes provincias, las cuales se repiten, este modelo, está en formato de tabla, y, se llama Datos.

Queremos conseguir una lista única de provincias, que lo haremos con las funciones INDICE, BUSCARV, ESTEXTO, y, COINCIDIR.

Si uso la función INDICE, donde como argumento matriz, selecciono la columna de provincia, como argumento número de fila, pongo 1, y, omito el argumento número de columna, por lo cual, usara el valor 1.

=INDICE(Datos[Provincia];1)

Obtengo la primera provincia, siendo única, ya que es el primer valor.

Voy a usar la función BUSCARV, para que me encuentre Granada, en la columna provincia, para ello, escribo el signo igual, seguido de BUSCARV, y, abro un paréntesis.

=BUSCARV(

Como argumento valor buscado, debería de poner el valor de la celda C4, donde se encuentra Granada, y, que lo busque en la columna de provincia, pero lo voy a hacer, al revés, es decir, como argumento valor buscado, va a ser la columna provincia, y, como argumento matriz_tabla, va a ser el valor de la celda C4, es decir, Granada.

=BUSCARV(Datos[Provincia];C4;

Como argumento, numero de columna, ponemos 1, y, como tipo de coincidencia, seleccionamos exacta.

=BUSCARV(Datos[Provincia];C4;1;FALSO)

Obtenemos una matriz desbordada, con los valores de Granada, donde hay coincidencia, y, error donde no la hay.

Al principio, hemos puesto la función INDICE, donde como argumento, numero de fila, hemos puesto 1.

Queremos que este ejemplo, sea dinámico, es decir, que, si añadimos, o, eliminamos provincias, el modelo se actualice, pues los pasos que vamos a dar, incluida ya, la función BUSCARV, es para que ese argumento número de fila, se convierta en dinámico.

Seguimos con la función BUSCARV, vemos que obtenemos texto, pero debemos de convertirlo en números, para poder ser usado, como argumento número de fila, dentro de la función INDICE.

Tenemos una función llamada, ESTEXTO, la cual devuelve VERDADERO, si el valor de la celda es texto, y, FALSO, si el valor de la celda, no lo es, pues después del signo igual, ponemos la función ESTEXTO.

=ESTEXTO(BUSCARV(Datos[Provincia];C4;1;FALSO))

Ahora, tenemos una matriz desbordada, con VERDADEROS, y FALSOS.

Pero, siguen sin ser números, pues, vamos a usar el doble signo negativo, que convierte VERDADERO en 1, y, FALSO en cero, pues, después del signo igual, ponemos el doble signo negativo, abrimos un paréntesis, porque vamos a englobar la función anterior, y, cerramos el paréntesis.

=--(ESTEXTO(BUSCARV(Datos[Provincia];C4;1;FALSO)))

Ahora, tenemos 1 donde es Granada, y, 0, donde no lo es, como una matriz desbordada.

Lo siguiente que voy a hacer, es buscar el valor cero, dentro de la función anterior, para ello, usamos la función COINCIDIR, donde como argumento valor buscado, ponemos cero, la matriz es la función anterior, y, una coincidencia exacta.

=COINCIDIR(0;--(ESTEXTO(BUSCARV(Datos[Provincia];C4;1;FALSO)));0)

Como resultado, obtengo el número 2, que es la siguiente provincia, porque primero, tenemos un 1, que corresponde a Granada, el siguiente valor es cero, porque no corresponde a Granada.

Lo bueno de usar la función COINCIDIR, es que cuando encuentra la primera coincidencia, se para, no sigue buscando, es decir, no nos va a devolver la posición de todos los valores ceros, y, de momento, lo que necesitamos en la siguiente posición.

Estamos buscando en C4, pero buscar en C4, no nos vale, porque entonces, debemos de tener dos columnas, y, queremos una sola columna, bien, si en la función BUSCARV, en vez de C4, pongo la matriz E3:E3, donde fijo la primera referencia E3, de la siguiente manera:

=COINCIDIR(0;--(ESTEXTO(BUSCARV(Datos[Provincia];$E$3:E3;1;FALSO)));0)

Obtengo como resultado el número 1, ¿Por qué?, pues, BUSCARV, no va a encontrar un blanco, dentro de la columna de provincia, lo que devolverá el numero cero, y, como con la función COINCIDIR, estamos buscando el numero cero, pues nos dice que se encuentra en la posición 1, que es la posición de la provincia de Granada, si arrastro, todas las celdas contendrán el número 1, pues, vamos a usar la función anterior, como argumento número de fila de la función INDICE, quedaría:

=INDICE(Datos[Provincia];COINCIDIR(0;--(ESTEXTO(BUSCARV(Datos[Provincia];$E$3:E3;1;FALSO)));0))

Obtengo Granada, vamos a arrastrar una vez, vemos, que obtenemos la provincia de Madrid.

¿Por qué?

Si nos fijamos en la función BUSCARV, en el argumento matriz_tabla, hemos puesto el rango E3:E3, donde hemos fijado la primera referencia E3, pues, vamos a echar un vistazo a la segunda función, aun sin la función INDICE.

=COINCIDIR(0;--(ESTEXTO(BUSCARV(Datos[Provincia];$E$3:E4;1;FALSO)));0)

Vemos que ahora pone, desde, E3 hasta E4, ¿Qué hay en E3?, pues nada, y, ¿en E4?, pues tenemos Granada, entonces, la función BUSCARV, está buscando todos los valores de la columna provincia, que coincida con Granada, encuentra una coincidencia, en la primera celda (A2), es decir, la función BUSCARV, nos devuelve 1, pero la siguiente, no tiene coincidencia, es decir, es cero, que lo encuentra la función COINCIDIR, y, nos devuelve su posición, que es 2, pues es el argumento, numero de fila, de la función INDICE, por lo que obtenemos la provincia de Madrid.

Si arrastramos una sola vez, obtenemos la provincia de Toledo, echemos un vistazo a la función.

=INDICE(Datos[Provincia];COINCIDIR(0;--(ESTEXTO(BUSCARV(Datos[Provincia];$E$3:E5;1;FALSO)));0))

Ahora, está buscando todas las provincias de la columna provincia, que sean iguales a los valores desde E3 a E5, en esas celdas tenemos los siguientes valores.

Traduciendo la función BUSCARV, nos devolvería 1, 1, 1, y, 0.

Y, como la función CONCIDIR, tiene que encontrar el valor de cero, pues lo encuentra en la posición 4, que es la provincia de Toledo.

Así funciones con el resto.

Vamos a arrastrar hacia abajo, y, vemos las provincias únicas, y, donde ya no hay más provincias únicas, nos aparece un error.

Vamos a usar la función SI.ERROR, y, ponemos un texto en blanco, donde haya error.

Pues, ya lo tenemos.

Vamos a ver varios ejemplos de funciones con derrame, y, sin derrame, para ver la diferencia y el tiempo que se ahorra al usar formulas con y sin derrame.

Tenemos el siguiente modelo de datos.

El modelo de datos son ventas de teléfonos en diferentes países.

Lo primero que queremos saber es la cantidad de teléfonos vendidos por cada país, primero lo haremos con la función SUMAR.SI sin derrame.

Hay que comentar que este modelo de datos tiene creado nombres de rango para cada encabezado de columna, lo que nos va a permitir buscarlo como si fuera una formula, por ejemplo, si pongo “pa”, me aparecerá todas las fórmulas que comiencen por “pa” y todos los nombres de rangos que comiencen por “pa”, para hacerlo desde el teclado, seleccionamos con el cursor, y, tabulamos.

Para crear dichos nombres de rangos, con una celda dentro del modelo, pulsamos CTRL más asterisco (*), quedando todo el rango seleccionado.

Vamos a la pestaña formulas, dentro del grupo nombres definidos, hacemos clic en crear desde la selección.

Se abre la ventana crear nombres a partir de la seleccion, pues, seleccionamos fila superior, para que utilice cada encabezado, como nombre de rango.

Aceptamos.

Si, ahora, desplegamos el cuadro de nombres, veremos estos nombres que acabamos de crear.

Tenemos que crear una lista única de países, ya que los países se repiten, para ello, en la celda G2, usamos la función UNICOS, la cual la aplicamos al nombre de rango país.

=UNICOS(País)

Bien, lo primero que vamos a calcular es la cantidad vendida por cada país, pero, lo haremos de la forma normal, es decir, sin derrame, para ello, usaremos la formula SUMAR.SI, de la siguiente manera, donde usamos como rango de criterios, el nombre de rango país, como criterio, la celda G2, y, rango de suma, el nombre de rango cantidad.

=SUMAR.SI(País;G2;Cantidad)

Tenemos el total para el primer país, recordar, que es sin derrame.

Arrastramos hacia abajo, y, tenemos los totales para cada país.

Ahora, vamos a hacer lo mismo, pero como una matriz derramada, pondremos la siguiente sintaxis, donde como argumento rango de criterios, sigue siendo el nombre de rangos, país, es decir, la columna de los países, como criterio, el valor de la celda G2, pero podemos observar que le hemos añadido, el operador de rango derramado, aunque, si seleccionamos el rango con el ratón, nos pondrá el operador de rango derramado, de forma automática, quiere decir que se va a seleccionar desde la celda G2, hasta la última celda ocupada, y, como rango de suma, seguimos usando el nombre de rango cantidad.

=SUMAR.SI(País;G2#;Cantidad)

Aceptamos.

Vemos que los resultados de ambas columnas son los mismos, a diferencia, que, con la matriz derramada, lo hemos hecho en un solo paso, no hemos tenido que usar referencias absolutas, ni, copiar hacia abajo, por lo que este último sistema es más rápido y seguro, ya que no podemos borrar un elemento de la matriz.

Bien, lo siguiente es saber las veces que aparece cada país, para ello, primero lo haremos sin derrame, usaremos la siguiente sintaxis, donde usamos la función CONTAR.SI, que nos hará un recuento de las veces que aparece, en el rango seleccionado, como argumento rango, ponemos el nombre de rango país, y, como criterio es la celda G2.

=CONTAR.SI(País;G2)

Aceptamos, y, vemos que el primer país, aparece 73 veces.

Ahora, debemos de arrastrar hacia abajo, y, vemos las veces que aparece cada país.

Ahora, lo haremos, pero con derrame, para ello, usaremos la sintaxis, donde usamos de nueva, la función CONTAR.SI, la única diferencia es que, en el argumento criterio, ponemos el operador de rango derramado, para que seleccione desde G2, hasta la última celda ocupada, esto lo convierte en dinámico, porque da igual el número de celdas ocupadas que haya a partir de G2, el operador de rango derramado se encargara de seleccionarlas.

=CONTAR.SI(País;G2#)

Aceptamos, y, obtenemos los mismos resultados que sin derrame, pero en un solo paso, sigue siendo más rápido y seguro.

Lo siguiente que vamos a realizar, es calcular la diferencia porcentual de cada país respecto al total, lo vamos a realizar como lo hemos estado haciendo, primero sin derrame, y, luego con derrame.

Para calcularlo sin derrame, debemos de dividir la cantidad del primer país entre la suma de la cantidad total, la formula seria, el valor de la celda I2, dividido entre la suma desde I2 a I25.

=I2/SUMA(I2#)

Aceptamos.

El porcentaje para el primer país, respecto al total de todos los países es de 5,28%.

Antes de arrastrar, debemos de fijar el rango dentro de la funcion SUMA, para que al copiar no se actualicen.

=I2/SUMA ($I$2#)

Arrastramos, y, ya tenemos el porcentaje de cada país, respecto al total.

Ahora, lo haremos con derrame, aquí ya no vale la formula anterior, ¿Por qué?

Pues como va a devolver una matriz, no podemos poner, =I2/SUMA($I$2#), porque I2, devolverá un valor, no una matriz desbordada, pero, el único cambio que debemos de hacer es añadir el operador de rango derramado a la referencia I2, de esta manera, dividirá cada número entre el total general.

=I2#/SUMA($I$2#)

Vemos que tenemos las mismas cantidades obtenidas anteriormente.

Si hacemos un auto suma de cada columna de porcentajes, si todo está bien, debe devolver el 100%, para hacer el auto suma, seleccionamos las dos celdas, después del último porcentaje.

Y, pulsamos ALT más igual.

Podemos ver, que ambos resultados nos devuelven el 100%.

Vamos a ver otro ejemplo, pero ahora, todo con matrices dinámicas.

El modelo de datos va a ser el mismo, pero en este caso, vamos a trabajar con la columna operador.

Lo primero, que vamos a hacer es crear una lista única de operadores, que haremos en la celda H4, para ello, usamos la función UNICOS, aplicada a la columna de operador.

=UNICOS(Operador)

Lo siguiente, que vamos a hacer, es calcular la cantidad vendida por cada operador, lo haremos con los nombres de columnas, no con nombres de rangos, para ello, vamos a usar la función SUMAR.SI, donde como argumento rango de criterios, seleccionamos la columna de operador, como criterio, usamos el valor de la celda H4 seguido del operador de rango derramado (#), para que se seleccionen las celdas desde H4 hasta la última celda ocupada, y, como argumento rango de suma, es decir, las celdas que se sumaran, en caso de haber coincidencia, va a ser la columna de cantidad.

=SUMAR.SI(Tabla27[Operador];H4#;Tabla27[Cantidad])

Aceptamos, y, tenemos las cantidades vendidas para cada operador.

Lo siguiente es calcular el porcentaje de cada operador respecto al total, para ello, usaremos la siguiente sintaxis, vamos a usa la expresión usada anteriormente, donde tenemos las cantidades vendidas por operador, y, lo debemos de dividir entre el total general, pues para ello, vamos a usar la función SUMA, y, como argumento, usamos la función anterior, es decir, va a dividir cada cantidad de cada operador, entre la cantidad total, porque con la función SUMA, conseguimos que todos los resultados devueltos, sean sumados.

=SUMAR.SI(Tabla27[Operador];H4#;Tabla27[Cantidad])/SUMA(SUMAR.SI(Tabla27[Operador];H4#;Tabla27[Cantidad]))

Aceptamos, y, ya tenemos los porcentajes, para cada operador.

Para acortar la expresión, podemos usar la funcion LET, donde creamos una variable, y, almacenamos la funcion SUMAR.SI.

=LET(a;SUMAR.SI(Tabla27[Operador];H4#;Tabla27[Cantidad])

Como argumento cálculo de LET, dividimos la variable “a” entre la suma de la variable “a”.

=LET(a;SUMAR.SI(Tabla27[Operador];H4#;Tabla27[Cantidad]);a/SUMA(a))

Aceptamos, y, tenemos los mismos resultados.

Lo siguiente va a ser crear un gráfico de líneas de todos los porcentajes, para ello, seleccionamos los porcentajes.

Vamos a la ficha insertar, y, elegimos grafico de líneas.

Quitamos ejes vertical y horizontal, titulo, líneas de cuadricula.

Cambiamos el color de la línea, para ello, seleccionamos la línea, botón alternativo de raton, y, dar formato a serie de datos.

En línea, elegimos línea sólida, y, elegimos el color que más nos convenga.

Teniendo la línea seleccionada, botón alternativo de raton, y, agregar línea de tendencia.

Vemos que la tendencia es al alza.

Pero, podemos pronosticar como será esa línea de tendencia, para ello, teniendo la línea de tendencia marcada, en la ventana de la derecha marcamos:

Esta es un valor que va desde 0 a 1, contra más cerca este del 1, más fiable es el pronóstico.

Además, indicaremos que queremos un pronóstico a dos meses.

Vemos que el pronóstico es al alza, pero si nos fijamos en el número, es de 0,2856, quiere decir que falta bastante para llegar a 1, por lo que este pronóstico no es nada fiable.

Por último, quitamos las líneas de contorno del grafico de líneas.

Pues, con esto, hemos terminado estos ejemplos.

A partir del siguiente modelo de datos, que son ventas de teléfonos en diferentes países, vamos a crear un cuadro, donde aparezcan en horizontal los años, y, en vertical los meses, pero, esto lo vamos a hacer como una matriz dinámica, es decir, que, si aumentan los años, en dicha matriz quedaran reflejados.

Lo primero que vamos a hacer, es como crear los años, en horizontal, pero de manera dinámica.

Todo tiene que salir de una formula.

Lo vamos a realizar paso a paso.

Vamos a usar la función TEXTO, esta función, tiene dos argumentos, que es valor, y, formato, la vamos a usar para extraer el año de la columna fecha, para ello, como argumento valor, usamos la columna fecha, y, como argumento formato, entre comillas dobles, ponemos “AAAA”.

=TEXTO(Fecha;"aaaa")

Obtenemos una matriz, con el mismo número de filas que la columna fecha, pero solo con los años, esto de momento, no nos vale para mucho, debido a que los años se repiten.

Lo siguiente, va a ser dejar solo los valores únicos, para ello, delante de la formula, vamos a poner la función UNICOS, de la siguiente manera:

=UNICOS(TEXTO(Fecha;"aaaa"))

Ya tenemos los años únicos.

Tenemos que usar la función SECUENCIA, y, debemos de decirle de cuantos números va a estar formada la secuencia, en este caso, será el número de años, entonces, ¿Cuántos valores son?, pues, a la expresión anterior, después del signo igual, vamos a usar la función CONTARA, esta función nos hace un recuento dentro de un rango de celdas alfanuméricas, la usamos para que nos devuelva el número de valores, lo haremos de la siguiente manera:

=CONTARA(UNICOS(TEXTO(Fecha;"aaaa")))

Y como resultado tenemos le valor 5, que es la cantidad de años que hay.

Pues, ya tenemos el argumento ancho de la función SECUENCIA.

Escribimos el signo igual, seguido del nombre de la función, y, abrimos un paréntesis.

=SECUENCIA(

Como argumento filas, ponemos 1 fila, es decir, nuestra matriz desbordada, va a estar compuesta de una fila.

=SECUENCIA(1;

Ahora, viene el argumento columnas, es decir, de cuantas columnas va a estar formada nuestra matriz desbordada, pues lo va a decidir, la expresión CONTARA.

=SECUENCIA(1;CONTARA(UNICOS(TEXTO(Fecha;"aaaa")));

Lo siguiente es el argumento inicio, es decir, donde debe de empezar, pues va a ser el primer año, que es el año 2010.

=SECUENCIA(1;CONTARA(UNICOS(TEXTO(Fecha;"aaaa")));2010;

Como ultimo argumento, es paso, es decir, a partir de 2010, cuanto debe de contar, pues ponemos 1.

Cerramos paréntesis.

=SECUENCIA(1;CONTARA(UNICOS(TEXTO(Fecha;"aaaa")));2010;1)

Es decir, nos va a crear una matriz desbordada, de una fila de alto, en este caso, de 5 columnas, empezando desde 2010, e, incrementando 1, hasta llegar al resultado de la función CONTARA, como he dicho, en este caso 5.

Podemos ver que todo queda en una formula.

Pues ya tenemos los años.

Esta matriz ya es dinámica, prueben a añadir en el modelo de datos, una fecha más, pero del año 2015, verán que ya aparece de forma automática en esta matriz dinámica.

Bien, ahora vamos con los meses, también, por supuesto como matriz dinámica.

Usaremos también la función TEXTO, junto con la función FECHA, para extraer el mes, la función FECHA, tiene tres argumentos, que son año, mes, y, día, como argumento año, usamos la función AÑO, para extraer el año de la fecha actual, como argumento mes, ponemos 1, es decir, enero, y, como argumento día, ponemos 1.

=FECHA(AÑO(HOY());1;1)

Obtenemos la fecha:

Esto solo nos devolverá el mes de enero, no es nuestra matriz dinámica, para que sea dinámica, dentro de la función FECHA, en el argumento mes, vamos a usar la función SECUENCIA, para que nos genere los 12 meses, para ello, en el argumento filas de la función SECUENCIA, ponemos 12, en el argumento columnas, ponemos 1, en el argumento inicio, también ponemos 1, es decir, va a crear una matriz desbordada, de 12 filas, 1 columna, empezando por el número 1, pero, como lo hemos puesto dentro de la función FECHA, en el argumento mes, pues, nos va a devolver una matriz desbordada, de 12 filas, empezando por el día 1 de enero del año actual para la primera celda, el día 1 de febrero del año actual para la siguiente fecha, y, así con el resto.

=FECHA(AÑO(HOY());SECUENCIA(12;1;1);1)

Ahora, englobamos esta expresión, dentro de la función TEXTO, y, como argumento formato, entre comillas dobles, ponemos “MMMM”, para que nos devuelva el nombre del mes.

Ya tenemos nuestra matriz dinámica, lista para ser rellenada.

Si en la celda H7, pongo la siguiente expresión, donde comparo el nombre del mes de la columna fecha con el valor de G7, que es el mes de enero.

=SI(TEXTO(Fecha;"mmmm")=G7;Cantidad))

Me va a devolver una matriz desbordada, con todos los valores vendidos en el mes de enero, pero de todos los años, y, donde no hay coincidencia, nos devuelve el valor da FALSO.

Vamos a poner después del signo igual, la función SUMA, para obtener el total.

Aquí lo tenemos.

Pero, es el total del mes de enero de todos los años, y, debe de aparecer el total de enero, solo del año 2010.

Quiere decir, que debemos de hacer otra pregunta, que será que el año de la columna fecha, debe de ser igual al valor de la celda H6, que es el año 2010.

Podemos concatenar tanto preguntas, como respuestas dentro del condicional SI.

Esto, quiere decir que si a la primera condición, le concateno, con el símbolo de ampersan, otra función TEXTO para extraer el año de la columna fecha, como sigue:

=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"AAAA")

Estamos preguntando, que si el mes de la columna fecha, y, el año de la columna fecha, como argumento valor si verdadero, vamos a concatenar la celda G7 con la celda H6, es decir, mes y año.

=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"AAAA")=G7&H6

Por último, en el argumento valor si falso, ponemos, de nuevo, la columna cantidad.

=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"AAAA")=G7&H6;Cantidad))

Aceptamos, y, tenemos una matriz desbordada, con el total del mes de enero del año 2010.

Solo queda copiar la función a todo el rango, hacia abajo, y, hacia la derecha, pero antes tenemos que fijar algunas filas y algunas columnas para que esto funcione bien, debemos de fijar la columna de G7, para que, al copiar hacia la derecha, no se actualice, porque los meses, siempre van a estar en la columna G, y, tenemos que fijar la fila de H6, para que no actualice el año al copiar hacia la abajo, porque los años, siempre van a estar en la fila 2, quedando de la siguiente manera:

=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"AAAA")=$G7&H$6;Cantidad))

Nos colocamos en la celda H7, presionamos CTRL más C para copiar, a continuación, seleccionamos todo el rango, y, pulsamos CTRL más V para pegar, y, ya tenemos nuestro cuadro relleno.

Lo siguiente que voy a hacer es añadir la fecha 01/01/2015 al modelo de datos con un importe de 2000.

Vemos que el año aparece en el cuadro.

Ahora, para que se actualicen los datos, nos colocamos en la celda H7, copiamos y volvemos a pegar, pero en este caso, seleccionamos también el año 2015, y, vemos como la cantidad de 2000, ya aparece.

Si añadimos valores, nuevos, o, modificamos los ya existentes, se actualizarán.

También, podríamos añadir una lista desplegable con los países, para ello, creamos una lista única de países con la función UNICOS, lo hacemos en la celda W3.

=UNICOS(País)

Creamos una validación de datos con los países, para ello, nos colocamos en la celda G6, donde vamos a crear la validación, vamos a la pestaña de datos, dentro del grupo nombres definidos, hacemos clic en validación de datos, desplegamos permitir, y, elegimos lista, y, en la ventana origen, seleccionamos la celda W3 junto con el operador de rango derramado (#).

Aceptamos.

Dejamos seleccionado un país.

Ahora, modificamos la formula, y, añadimos país, como hemos hecho antes, concatenamos la columna de país en el argumento prueba lógica, y, concatenamos el país seleccionado en el argumento valor si verdadero.

Fijamos G6.

=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"AAAA")&País=$G7&H$6&$G$6;Cantidad))

Volvemos a copiar y pegar.

En este caso, tenemos las cantidades vendidas por el país Paraguay.

Podemos cambiar el país, y, veremos que los datos cambian.

Vamos a añadir los totales por años, para ello, seleccionamos el rango H19:M19, y, pulsamos CTRL mas igual.

Estos totales no son correctos, porque el encabezado es numérico, y, también es sumado.

Vamos a la expresión de la celda H19, y, restamos la celda H6, que es el año 2010.

=SUMA(H6:H18)-H6

Arrastramos hacia la derecha, y, tenemos los totales correctos.

Lo siguiente que vamos a realizar, es un modelo de datos donde tenemos los totales por años.

En la celda G22, entre comillas dobles ponemos la palabra Total y dejamos un espacio, concatenamos con los años, y, transponemos.

=TRANSPONER("Total "&H6#)

Tenemos el titulo de los años de forma dinámica.

En la celda H22, vamos a poner los totales, pero, deben de ser de forma dinámica, es decir, debe de poner los años que se muestran en cada momento, para ello, usamos la funcion DESREF, como argumento referencia, ponemos H19, que es la fila donde se encuentran los totales, omitimos el argumento filas, columnas, y, alto, como argumento ancho, usamos la funcion COLUMNAS, como argumento matriz, ponemos la celda H6 junto con el operador de rango derramado, y, transponemos.

=TRANSPONER(DESREF(H19;;;;COLUMNAS(H6#)))

Ya tenemos los totales de forma dinámica.

Y vamos a crearle unos gráficos.

Lo primero que voy a hacer es crear unos mini gráficos para cada mes del año, para ello, selecciono los valores del mes de enero.

Vamos a la pestaña insertar, y, en el grupo de mini gráficos, elijo de columnas.

Se abre la siguiente ventana.

Seleccionamos una ubicación, y, aceptamos.

Aceptamos, y, ya lo tenemos.

Lo siguiente es ir a la pestaña de mini gráficos, y, en el grupo mostrar, marcamos punto alto, para que el valor más alto quede de otro color.

Quedaría:

Arrastramos hacia abajo.

Ahora, tenemos los años, y, los totales de para cada año.

Vamos a crear unos mini gráficos, pero personalizados, en este caso, dependiendo del valor del año, queremos que aparezcan unas manos con el dedo hacia arriba, pero dependiendo de la cantidad, así será el número de manos.

Para ello, vamos a usar la función REPETIR, la cual repite un número de veces un carácter, la sintaxis es simplemente el carácter para repetir, y, número de veces a repetir.

Nuestras reglas para este mini grafico van a ser:

·        Si el valor es mayor o igual a 500, o, el valor es menor o igual a 700, se debe de repetir 2 veces.

·        Si el valor es mayor a 700, o, el valor es menor o igual a 900, se debe de repetir 4 veces,

·        Si el valor es mayor a 900, se debe de repetir 6 veces.

·        Si el valor es menor a 500, no debe de poner nada.

Para ello, vamos a usar el condicional SI, junto con el operador lógico Y, y, la función REPETIR.

Para usar el carácter de la mano, debemos de hacerlo de la siguiente manera, si ponemos el signo igual, la función REPETIR, abrimos paréntesis, y, a continuación, pulsamos la tecla de Windows junto con el punto, se abre la siguiente ventana:

En nuestro caso, vamos a seleccionar la mano con el dedo hacia arriba, la ponemos entre comillas dobles, y, por ejemplo, numero de repeticiones va a ser 3.

El símbolo, debe de ir entre comillas.

=REPETIR("👍";3)

Esta función, la vamos a poner dentro del condicional SI, quedando de la siguiente manera:

=SI(Y(H22>=500;H22<=700);REPETIR("👍";2);SI(Y(H22>700;H22<=900);REPETIR("👍";4);SI(H22>900;REPETIR("👍";6);"")))

De esta manera, cumple con las reglas que habíamos citado.

Quedaría:

Lo siguiente va a ser crear un gráfico de personas, este tipo de grafico es un complemento, el cual, si no lo tenemos instalado, debemos de hacerlo.

Para ello, vamos a la pestaña inicio –complementos.

Se abre la ventana de mis complementos.

Hacemos clic en más complementos.

En la ventana buscar, vamos a poner la palabra “persona”, y, clic en la lupa.

Aparece la siguiente ventana donde está el complemento.

Simplemente, tendríamos que hacer clic en agregar, en mi caso no, porque ya lo tengo instalado.

Bueno, pues vamos a crear ese grafico de personas.

Clic en inicio – complementos, se abre la siguiente ventana.

Seleccionamos el grafico de personas.

Aparece la siguiente imagen.

Si hacemos clic en cualquier parte del gráfico, podemos ver que se habilitan dos pestañas.

Si hacemos clic en el icono que es una hoja de cálculo, se abre la siguiente ventana:

Hacemos clic en seleccionar datos.

Donde debemos de seleccionar los datos que van a componer nuestro grafico de personas.

Seleccionamos los siguientes datos:

Clic en cerrar.

Este es el resultado.

Tanto podemos cambiar el nombre, o título, si volvemos a hacer clic en el icono de una hoja de cálculo, lo haremos:

Si hacemos clic en la rueda dentada, podemos cambiar el formato, por ejemplo, vamos a cambiar el icono de una persona por otro.

Para ello, hacemos clic en forma, y, voy a elegir el ordenador.

También, podía cambiarle el fondo en la pestaña tema.

Y, también, podría cambiarle el tipo.

Y este sería el resultado final.

Por último, vamos a crear un gráfico de columnas, también para mostrar los valores.

Para ello, seleccionamos los valores.

Vamos a la pestaña insertar – gráficos – columnas.

El título, líneas de división, y, eje vertical lo quitamos.

En la pestaña de diseño de gráfico, clic en seleccionar datos, se abre la siguiente ventana.

Clic en editar, de etiquetas del eje horizontal.

En rango de rótulos del eje, seleccionamos los años.

Aceptamos.

Con el botón alternativo del raton en uno de los laterales del gráfico, elegimos, formato del área del gráfico.

En la ventana de la derecha, en relleno, elegimos relleno sólido, y, elegimos un color.

Clic en una columna, y, también le cambiamos el color a las columnas.

Botón alternativo de raton en una columna, y, elegimos añadir etiquetas de datos, y, observaremos que no se ven.

Volvemos a botón alternativo de raton en una columna, y, elegimos formato de etiquetas de datos, las cuales quedaran señaladas.

Vamos a la pestaña de inicio, y, cambiamos color y tamaño.

Ahora, en la ventana de la derecha, elegimos número, y, seleccionamos moneda, y, sin decimales.

Seleccionamos el eje horizontal, y, también cambiamos tamaño, y, color.

Clic en uno de los laterales del gráfico, y, ponemos las esquinas redondeadas.

Ya tendríamos nuestro gráfico, pero con otra imagen.

Miguel Angel Franco