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.
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 formulas 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 mas 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 los valores de la, 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 pais.
=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 pais, 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 pais, recordar, que es sin derrame.
Arrastramos hacia abajo, y, tenemos los totales para cada pais.
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, pais, 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 ultima 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 ultimo 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, el criterio especificado, pues, el rango de búsqueda es el nombre de rango, pais, y, como criterio es la celda G2.
=CONTAR.SI(País;G2)
Aceptamos, y, vemos que el primer pais, aparece 73 veces.
Ahora, debemos de arrastrar hacia abajo, y, vemos las veces que aparece cada pais.
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 criterio, hemos puesto el operador de rango derramado, para que selecciones desde G2, hasta la ultima celda ocupada, esto lo convierte en dinámico, porque da igual el numero 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:I25)
Aceptamos.
El porcentaje para el primer pais, respecto al total de todos los países es de 5,28%.
Antes de arrastrar, debemos de fijar el rango dentro de la función SUMA, para que al copiar no se actualicen.
=I2/SUMA ($I$2: $I$25)
Arrastramos, y, ya tenemos el porcentaje de cada pais, 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:$I$25), 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:$I$25)
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 autosuma, 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, para ello, usaremos la siguiente sintaxis, como queremos un total, vamos a usar la función SUMAR.SI, donde como argumento rango de criterios, usamos el nombre de rango, 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 cantidad.
=SUMAR.SI(Operador;H4#;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(Operador;H4#;Cantidad)/SUMA(SUMAR.SI(Operador;H4#;Cantidad))
Aceptamos, y, ya tenemos los porcentajes, para cada operador.
Ahora, vamos a calcular cual es el porcentaje mayor de los operadores, para ello, vamos a usar el condicional SI, donde preguntas, que si el valor máximo desde J4 hasta la última celda ocupada, donde volvemos a usar el operador de rango derramado (#), para sacar el valor máximo, usamos la función SUMA, es igual, a uno de los valores de la matriz desbordada, es decir, desde J4 hasta la ultima celda ocupada, entonces, que nos ponga dicho valor, en caso contrario, debe de poner un texto en blanco.
=SI(MAX(J4#)=J4#;J4#;"")
Como resultado tenemos una matriz desbordada, con un solo valor, que es el porcentaje mayor.
Este ejemplo, es porque lo vamos a adornar con un par de gráficos.
El primero grafico que vamos a crear es una especie de termómetro, donde aparecerá el porcentaje máximo, es decir, 100% y, el porcentaje máximo de operadores.
Para ello, vamos a la ficha insertar, dentro de la pestaña gráficos, insertamos un gráfico de columnas, sin datos.
Cuando seleccionamos el grafico, se habilitan las pestañas de diseño de gráfico, y, formato.
Pues, vamos a la pestaña diseño de gráfico, y, hacemos clic en seleccionar datos.
Se abre la ventana seleccionar origen de datos.
Clic en agregar.
Se abre la ventana modificar serie.
Clic en valores de serie, y, seleccionamos el dato, que es la celda con el porcentaje mayor.
Volvemos a la ventana modificar serie, pues, aceptamos.
Lo siguiente, que vamos a agregar, es el porcentaje mayor, para ello, volvemos a la pestaña de diseño de gráfico, hacemos clic en seleccionar datos, y, clic en agregar, y, seleccionamos la celda del porcentaje mayor.
Aceptamos, y, el grafico queda:
Hacemos clic en una columna, y, de nuevo clic en esa columna, botón alternativo de ratón, y, elegimos dar formato a serie de datos
En superposición de series, que es la distancia que hay entre columnas, vamos a poner 100%, es decir, que se ponga una columna encima de otra, quedaría como sigue:
Lo siguiente es que vamos a hacer clic en la columna de color de azul, para cambiarle el color, le vamos a poner un verde oscuro.
Clic en la columna, botón alternativo de ratón, y, seleccionamos formato de punto de datos.
En la ventana que se abre a la derecha, en relleno, elegimos color sólido, y, seleccionamos el color que queremos, en mi caso, verde oscuro.
Hacemos lo mismo para la columna de color naranja, y, le ponemos un color verde claro.
Borramos el título, y, quitamos las líneas de cuadriculo, el eje horizontal, y, vertical.
Lo siguiente va a ser crear una elipse, para ello, vamos a la pestaña insertar, dentro del grupo ilustraciones, desplegamos formas, y, elegimos elipse, para que este círculo sea perfecto dejamos pulsado la tecla de <SHIFT>.
Cambiamos el color del circulo, y, también lo ponemos de color verde oscuro.
Ahora, lo vamos a centrar con la columna.
Seleccionamos tanto el grafico de barras, como el circulo, vamos a la pestaña formato de forma, y, agrupamos, para que ambas figuras sean una.
Estrechamos el grafico.
Seleccionamos la columna de mayor tamaño, hacemos clic con botón alternativo de ratón, y, seleccionamos agregar etiquetas de datos.
Seleccionamos la etiqueta de dato, vamos a la pestaña de inicio, y, dentro del grupo fuente, cambiamos el color y el tamaño de la letra.
Seleccionamos la columna inferior, y, también vamos a agregar etiquetas de datos.
No la vemos, porque esta dentro de la columna, para sacarla, volvemos a seleccionar la columna inferior, y, botón alternativo de ratón, y, formato de etiqueta de datos, quedara seleccionada, con el ratón la sacamos fuera de la columna.
Seleccionamos la etiqueta de datos, haciendo doble clic sobre ella, en la ventana de la derecha, desmarcamos mostrar línea de guía.
Vamos a la ficha de inicio, ponemos la letra mayor, y, cambiamos color.
La metemos dentro de la columna inferior.
Teniendo uno de los extremos marcados del gráfico, en la ventana de la derecha, en líneas, marcamos sin línea.
Ahora, vamos a la pestaña vista, en el grupo mostrar, desmarcamos líneas de cuadricula, y, el resultado es:
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 ratón, 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 ratón, 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 mas cerca este del 1, mas fiable es el pronóstico.
Además, indicaremos que queremos un pronóstico a dos meses.
Vemos que el pronostico 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 pronostico no es nada fiable.
Por último, quitamos las líneas de contorno del grafico de líneas.
Pues, con esto, hemos terminado estos ejemplos.
Comments