Para el siguiente ejemplo, tenemos un modelo, donde tenemos un IDEmpresa, una empresa al que corresponde cada IDEmpresa, y, las unidades vendidas por cada empresa.
La empresa 1001, trabaja con tres empresas, donde vemos su ID.
Que son las empresas:
La empresa 2001, trabaja con otras tres empresas.
En la primera tabla tenemos las ventas de cada empresa, la tabla se llama Ventas.
En la segunda tabla, tenemos las unidades promocionadas por las subcontratas que trabajan para las empresas 1001, y, 2001, la tabla se llama Promociones.
En la tercera tabla, tenemos las subcontratas que pertenecen a cada empresa, la tabla se llama Equivalencia.
Queremos crear un informe donde ver el nombre de cada empresa, las unidades promocionadas, y, las unidades vendidas.
Vemos que en la tabla promociones tenemos el IDSegundaEmpresa, pero no el nombre, el cual necesitamos para encontrar las subcontratas de cada empresa, para ello, en una celda, usamos FILTRAR, donde como argumento array, seleccionamos IDSegundaEmpresa de la tabla Equivalencia.
=FILTRAR(Equivalencia[IDSegundaEmpresa]
Punto y coma, como argumento include, seleccionamos IDEmpresa de la tabla Equivalencia, y, lo igualamos al valor de la celda C4, que es la empresa que pertenece al primer ID.
Cerramos paréntesis.
=FILTRAR(Equivalencia[IDSegundaEmpresa];Equivalencia[IDEmpresa]=C4)
Aceptamos, y, tenemos los IDSegundaEmpresa que pertenece a la primera empresa.
Lo siguiente es buscar cada IDSegundaEmpresa obtenido con la función FILTRAR en IDSegundaEmpresa de la tabla Promociones, y, que nos devuelva UnidadesPromocionadas de la tabla Promociones, para ello, usamos BUSCARX.
=BUSCARX(FILTRAR(Equivalencia[IDSegundaEmpresa];Equivalencia[IDEmpresa]=C4);Promociones[IDSegundaEmpresa];Promociones[UnidadesPromocionadas])
Obtenemos las unidades vendidas por cada empresa.
Sumamos los resultados con la función SUMA.
=SUMA(BUSCARX(FILTRAR(Equivalencia[IDSegundaEmpresa];Equivalencia[IDEmpresa]=C4);Promociones[IDSegundaEmpresa];Promociones[UnidadesPromocionadas]))
Tenemos 10 unidades promocionadas.
Estas unidades promocionadas hay que restarlas a las unidades vendidas por la empresa 1001.
Para saber de las unidades vendidas, cuantas fueron promocionadas.
Vamos a concatenar la expresión anterior:
=SUMA(BUSCARX(FILTRAR(Equivalencia[IDSegundaEmpresa];Equivalencia[IDEmpresa]=C4);Promociones[IDSegundaEmpresa];Promociones[UnidadesPromocionadas]))
Con la frase, entre comillas dobles, " unidades promocionadas de un total de ".
=SUMA(BUSCARX(FILTRAR(Equivalencia[IDSegundaEmpresa];Equivalencia[IDEmpresa]=C4);Promociones[IDSegundaEmpresa];Promociones[UnidadesPromocionadas])) & " unidades promocionadas de un total de "
Concatenamos con el valor de la celda D4, que son las unidades vendidas para la primera empresa.
=SUMA(BUSCARX(FILTRAR(Equivalencia[IDSegundaEmpresa];Equivalencia[IDEmpresa]=C4);Promociones[IDSegundaEmpresa];Promociones[UnidadesPromocionadas])) & " unidades promocionadas de un total de " & D4
Concatenamos con la palabra, entre comillas dobles, " unidades".
=SUMA(BUSCARX(FILTRAR(Equivalencia[IDSegundaEmpresa];Equivalencia[IDEmpresa]=C4);Promociones[IDSegundaEmpresa];Promociones[UnidadesPromocionadas])) & " unidades promocionadas de un total de " & D4 & " unidades"
Aceptamos, y, vemos de las unidades vendidas cuales fueron promocionadas.
Arrastramos.
Pero, hay empresas que no han promocionados unidades, por lo que nos devuelve un error, pues, vamos a usar la función SI.ERROR, y, en caso de que la expresión anterior devuelva un error, que ponga Sin promoción.
=SI.ERROR(SUMA(BUSCARX(FILTRAR(Equivalencia[IDSegundaEmpresa];Equivalencia[IDEmpresa]=C4);Promociones[IDSegundaEmpresa];Promociones[UnidadesPromocionadas]))&" unidades promocionadas de un total de "&D4&" unidades";"Sin promocion")
Volvemos a arrastrar, y, ya lo tenemos.
Para el siguiente ejemplo, tenemos el siguiente modelo.
Tenemos también una empresa, y, una serie de subcontratas que trabajan para cada empresa.
Pues, queremos casi lo mismo que para el primer ejemplo, pero cambia el orden, aquí tenemos una empresa, y, de ella cuelga las subcontratas que trabajan para dicha empresa, después tenemos los productos que han vendido cada subcontrata, la cantidad el precio, total, cuantas unidades de las vendidas han sido promocionadas, y, el porcentaje de descuento por unidad promocionada.
Uno de los problemas que se nos presenta, es que en la columna centro, tenemos tanto los centros como las subcontratas, y, debemos de tener una lista solo con los centros, pero, vemos que tenemos la columna producto, donde tenemos un espacio donde se encuentra cada centro comercial.
El modelo está en formato de tabla, y, se llama Ventas2.
En una celda, vamos a usar la función ESBLANCO sobre la columna Producto.
=ESBLANCO(Ventas2[Producto])
Obtenemos una matriz desbordada con VERDADERO donde hay coincidencia, y, FALSO donde no la hay.
Nos interesan los valores VERDADEROS, pues, vamos a usar el condicional SI, donde preguntamos que, si el resultado de ESBLANCO es igual a VERDADERO, el cual no debemos de ponerlo, porque de forma predeterminada, lo comparara con VERDADERO. Este es el argumento prueba lógica del condicional SI.
=SI(ESBLANCO(Ventas2[Producto])
Punto y coma, como argumento valor si verdadero, ponemos la columna centro.
=SI(ESBLANCO(Ventas2[Producto]);Ventas2[Centro]
Punto y coma, como argumento valor si falso, ponemos un texto en blanco.
Cerramos paréntesis.
=SI(ESBLANCO(Ventas2[Producto]);Ventas2[Centro];"")
Aceptamos, y, vemos que donde hay coincidencia, tenemos el centro, y, donde no la hay, tenemos blanco.
Para quedarnos solo con los centros, debemos de deshacernos de los blancos, como la expresión anterior la vamos a usar más de una vez, después del signo igual, ponemos LET, creamos una variable, donde se almacenará la expresión anterior.
=LET(a;UNICOS(SI(ESBLANCO(Ventas2[Producto]);Ventas2[Centro];""))
Punto y coma, como argumento calculo, filtramos la variable A, siempre que dicha variable sea diferente a blanco.
=LET(a;UNICOS(SI(ESBLANCO(Ventas2[Producto]);Ventas2[Centro];""));FILTRAR(a;a<>""))
Aceptamos, y, ya tenemos los centros comerciales.
Debemos de conocer la fila donde comienza cada centro, porque, una fila más comienza las subcontratas, para ello, vamos a preguntar que, si es blanco algún valor de la columna producto, en ese caso, que nos devuelva la fila, pero le sumamos 1, en caso contrario, que nos devuelva un texto en blanco.
SI(ESBLANCO(Ventas3[Producto]);FILA(Ventas3[Producto])+1;"")
Obtenemos una matriz desbordada con el número de fila más 1 donde hay coincidencia, y, blanco donde no la hay.
Después del signo igual, ponemos LET, creamos una variable, y, almacenamos la expresión anterior.
=LET(a;SI(ESBLANCO(Ventas3[Producto]);FILA(Ventas3[Producto])+1;"")
Punto y coma, como argumento calculo, filtramos la variable A, siempre que dicha variable sea distinta a blanco.
=LET(a;SI(ESBLANCO(Ventas3[Producto]);FILA(Ventas3[Producto])+1;"");FILTRAR(a;a<>""))
Aceptamos, y, ya tenemos los números de filas donde debemos de empezar a rescatar.
Ya tenemos donde comenzar, pero, lo siguiente es saber dónde terminar, porque no trabajan las mismas subcontratas para cada empresa, para ello, volvemos a la expresión anterior, en la celda K2, vamos a restar 2 al valor de J3, porque si restamos 1, ira al siguiente centro comercial.
Obtenemos la fila donde terminar para el primer centro comercial, en otras palabras, primero vamos a rescatar las cantidades, y, la vamos a sumar, quiere decir que de la columna C, debemos de rescatar las filas desde la 3 hasta la 6, y, después sumarla.
Arrastramos, y, ya tenemos comienzo y fin para cada centro comercial, pero vemos que la fila de fin para el ultimo centro comercial aparece en negativo.
Si vemos la expresión de la celda K4, vemos que aparece J5-2, y, ¿Qué hay en J5?, pues nada, es una celda vacía, por lo que al restar 2, nos devuelve un valor negativo, para solventar este problema, nos colocamos en la celda K2, donde tenemos la primera expresión, vamos a usar el condicional SI, donde vamos a preguntar que si el valor de J3 menos 2, es menor a 0.
=SI(J3-2<0
En ese caso, que nos devuelva la fila total de cualquier columna de la tabla, pero debemos de sumar 1.
=SI(J3-2<0;FILAS(Ejemplo2!$B$2:$B$14)+1
En caso contrario, que nos devuelva la operación J3-2.
Cerramos paréntesis.
=SI(J3-2<0;FILAS(Ejemplo2!$B$2:$B$14)+1;J3-2)
Aceptamos y arrastramos, y, vemos como la fila donde aparecía en negativo, ahora, aparece el numero de fila final a rescatar para el tercer centro comercial.
Como hemos dicho, las cantidades se encuentran en la columna cantidad, que es la tercera columna, como ya tenemos la fila a rescatar, vamos a usar DIRECCION, donde como argumento fila es la fila de comienzo para el primer centro, y, como argumento columna, ponemos 3.
=DIRECCION(J2;3)
Obtenemos la referencia C3 en absoluta.
Lo concatenamos con los dos pontos (:) entre comillas dobles.
=DIRECCION(J2;3)&":"
Concatenamos, de nuevo, con DIRECCION, donde el argumento fila, es la referencia K2, es la fila donde termina, y, columna, sigue siendo la 3.
=DIRECCION(J2;3)&":"&DIRECCION(K2;3)
Obtenemos el rango C3:C6 en absoluto.
Para rescatar los valores, usamos la función INDIRECTO, y, como argumento es la expresión anterior.
=INDIRECTO(DIRECCION(J2;3)&":"&DIRECCION(K2;3))
Obtenemos una matriz desbordada con cada cantidad para el primer centro comercial.
Sumamos.
=SUMA(INDIRECTO(DIRECCION(J2;3)&":"&DIRECCION(K2;3)))
Ya tenemos la cantidad vendida por el primer centro comercial.
Arrastramos, y, tenemos las cantidades vendidas para centro comercial.
Para calcular las cantidades promocionadas, la expresión es la misma, lo único que debemos de cambiar es el argumento columna de DIRECCION, para este caso, es la columna 6.
=SUMA(INDIRECTO(DIRECCION(J2;6)&":"&DIRECCION(K2;6)))
Aceptamos, arrastramos, y, ya tenemos el total de las cantidades promocionadas.
Si sabemos que el encabezado a sumar se llama Cantidad, pero no sabemos en qué posición se encuentra, podemos usar la función COINCIDIR, donde como argumento valor buscado, entre comillas dobles, ponemos “Cantidad”.
=COINCIDIR("Cantidad"
Punto y coma, como argumento matiz de búsqueda, ponemos el nombre de la tabla, abrimos un corchete, ponemos almohadilla, y, en la ventana que aparece, seleccionamos encabezados, cerramos corchete.
=COINCIDIR("Cantidad";Ventas3[#Encabezados]
Punto y coma, como argumento tipo de coincidencia, seleccionamos exacta.
Cerramos paréntesis.
=COINCIDIR("Cantidad";Ventas3[#Encabezados];0)
Aceptamos, y, nos devuelve la posición 3, pues, vamos a la expresión INDIRECTO, y, sustituimos el argumento columna de DIRECCION, por la función COINCIDIR.
=SUMA(INDIRECTO(DIRECCION(J2;COINCIDIR("Cantidad";Ventas3[#Encabezados];0))&":"&DIRECCION(K2;COINCIDIR("Cantidad";Ventas3[#Encabezados];0))))
Tenemos el mismo resultado, pero, ahora, es dinámico.
Hacemos lo mismo para cantidades promocionadas.
=SUMA(INDIRECTO(DIRECCION(J2;COINCIDIR("Promocion";Ventas3[#Encabezados];0))&":"&DIRECCION(K2;COINCIDIR("Promocion";Ventas3[#Encabezados];0))))
Ocultamos las columnas donde hemos tenemos la fila de inicio y de fin.
Miguel Angel Franco
Comments