Ganancias y costos por compañia

Jaime Franco Jimenez
5 nov 2023
4 Min. de lectura

Tenemos el siguiente modelo, donde tenemos una compañía, un departamento, y ganancias y costos.

Vemos que aparece Ganancia y Gan, que es lo mismo, pero no podemos cambiar nada.

Debemos de crear un modelo donde tengamos el total de ganancias y costos por compañía, y, la diferencia, el modelo debe de quedar como sigue:

El departamento no nos va a hacer falta, vamos a crear un nuevo modelo, donde en la columna compañía, donde es blanco que aparezca la compañía, omitimos la columna departamento, como encabezado, vamos a crear uno donde aparezca las tres primeras letras de los años, y, no aparezcan los años.

Después, debe de aparecer las cantidades para cada compañía, ganancia y costo.

En la celda A19, usamos la funcion SCAN, omitimos el argumento valor inicial, como argumento array, seleccionamos el rango A2:A15 (compañías), ponemos LAMBDA, creamos dos variables, como argumento calculo de LAMBDA, preguntamos si la variable “b” es igual a nada, que devuelva la variable “a”, en caso contrario, que devuelva la variable “b”.

= SCAN(;A2:A15;LAMBDA(a;b;SI(b="";a;b)))

Cuando ejecutamos la expresión, valor inicial, vale cero, y, el argumento array vale MSFT, preguntamos si la variable “b”, es decir, MSFT, es igual a nada, como no lo es, nos devuelve el argumento valor si falso, que es la variable “b”, por lo que nos devuelve MSFT.

Así con el resto de los valores.

Aceptamos, y, vemos como los blancos han sido rellenados con las compañías correspondientes.

Después del signo igual, ponemos la funcion APILARH, como argumento matriz1, es la funcion SCAN, como argumento matriz2, seleccionamos el rango C2:J15.

=APILARH(SCAN(;A2:A15;LAMBDA(a;b;SI(b="";a;b)));C2:J15)

Tenemos el modelo.

Vamos por los encabezados.

En la celda A18, escribimos Compañía.

En la celda B18, usamos la funcion TEXTODESPUES, como argumento texto, seleccionamos el rango C1:J1, como argumento delimitador, ponemos un espacio.

=TEXTODESPUES(C1:J1;" ")

Tenemos Ganancia y Costo.

Después del signo igual, ponemos la funcion IZQUIERDA, como argumento texto, es la funcion TEXTODESPUES, como argumento numero de caracteres, ponemos 2.

=IZQUIERDA(TEXTODESPUES(C1:J1;" ");3)

Aceptamos, y, ya tenemos el encabezado.

Es decir, el modelo con el que vamos a trabajar.

Vamos a usar la funcion MAP, donde vamos a buscar cada compañía y cada ganancia y costo, por ejemplo, vamos a buscar MSFT y Gan, si hay coincidencia, sumamos las cantidades, pero, debemos de usar una sola funcion, si nos traemos los valores únicos de compañías, obtenemos:

Si usamos la siguiente expresión:

=MAP(K25#;B18;LAMBDA(x;y;SUMA(SI(x=A19:A32;SI(y=B18#;B19:I32)))))

Donde la variable “x” vale las compañías únicas, y, la variable “y” vale el primer valor del encabezado, es decir, “Gan”.

Estamos preguntando si cada compañía del rango A19:A32, es igual a cada valor único de compañías, y, si los encabezados son iguales a “Gan”, tenemos tres compañías, y, un Gan.

Esta sería la comparación que realiza:

Por lo que solo vamos a obtener el total para MSFT y Gan, porque las siguientes comparaciones son Amazon y blanco, y, Metromar y blanco, donde no hay coincidencia, por lo que nos devuelve un error.

Para obtener los totales correctos, debemos de comparar cada compañía con Gan, quiere decir que Gan debe de aparecer una vez para cada compañía.

Lo mismo para Costo.

Pues, vamos a empezar…

En una celda, usamos LET, creamos una variable, y, nos traemos los valores únicos de compañías.

=LET(a;UNICOS(A19:A32)

Creamos otra variable, usamos la funcion BYROW, como argumento array, ponemos la variable “a”, como argumento funcion, ponemos LAMBDA, creamos una variable, como argumento calculo de LAMBDA, preguntamos que, si la variable “x” es diferente a blanco, que nos devuelva Gan, omitimos el argumento valor si falso.

Probamos variable.

=LET(a;UNICOS(A19:A32);b;BYROW(a;LAMBDA(x;SI(x<>"";"Gan")));b)

Tenemos una matriz desbordada en vertical de tres filas con la palabra Gan.

Creamos otra variable, usamos la misma expresión que para la variable “b”, pero cambiamos Gan por Costo.

=LET(a;UNICOS(A19:A32);b;BYROW(a;LAMBDA(x;SI(x<>"";"Gan")));c;BYROW(a;LAMBDA(x;SI(x<>"";"Costo")));c)

Obtenemos tres veces la palabra Costo.

Creamos otra variable, usamos MAP, como argumento array, ponemos la variable “a”, como argumento array2, ponemos la variable “b”, ponemos la funcion LAMBDA, creamos dos variables.

=LET(a;UNICOS(A19:A32);b;BYROW(a;LAMBDA(x;SI(x<>"";"Gan")));c;BYROW(a;LAMBDA(x;SI(x<>"";"Costo")));d;MAP(a;b;LAMBDA(x;y

Como argumento cálculo de LAMBDA, ponemos la funcion SUMA, como argumento numero1, usamos el condicional SI, como argumento prueba lógica, preguntamos si la variable “x” es igual al rango A19:A32 (compañías), como argumento valor si verdadero, ponemos otro condicional SI, como argumento prueba lógica, preguntamos si la variable “y” es igual a B18# (encabezados), como argumento valor si verdadero, seleccionamos el rango B19:I32, omitimos el argumento valor si falso.

=LET(a;UNICOS(A19:A32);b;BYROW(a;LAMBDA(x;SI(x<>"";"Gan")));c;BYROW(a;LAMBDA(x;SI(x<>"";"Costo")));d;MAP(a;b;LAMBDA(x;y;SUMA(SI(x=A19:A32;SI(y=B18#;B19:I32)))));d)

Aceptamos, y, tenemos las ganancias para cada compañía.

Creamos otra variable donde es la misma funcion MAP que la anterior, lo único que debemos de cambiar es el argumento array2, y, ponemos la variable “c” (Cos).

Obtenemos los costos para cada empresa.

Creamos otra variable, la llamamos resta, usamos MAP, como argumento array, ponemos la variable “d”, como argumento array2, ponemos la variable “e”, ponemos LAMBDA, creamos dos variables, ponemos la funcion SUMA, como argumento numero1, ponemos en negativo la variable “y”, y, como argumento numero2, ponemos la variable “x”.

Tenemos la resta de entre costos y ganancias.

Creamos otra variable, usamos APILARH, como argumento matriz1, ponemos la variable “d”, como argumento matriz2, ponemos la variable “e”, como argumento matriz3, ponemos la variable “resta”.

Tenemos las ganancias, los costos, y, la diferencia entre ganancias y costos.

Creamos otra variable, la llamamos sumas, usamos BYCOL, como argumento array, ponemos la variable “h”, ponemos LAMBDA, creamos una variable, y, sumamos la variable “x”.

=LET(a;UNICOS(A19:A32);b;BYROW(a;LAMBDA(x;SI(x<>"";"Gan")));c;BYROW(a;LAMBDA(x;SI(x<>"";"Cos")));d;MAP(a;b;LAMBDA(x;y;SUMA(SI(x=A19:A32;SI(y=B18#;B19:I32)))));e;MAP(a;c;LAMBDA(x;y;SUMA(SI(x=A19:A32;SI(y=B18#;B19:I32)))));resta;MAP(d;e;LAMBDA(x;y;SUMA(-y;x)));h;APILARH(d;e;resta);sumas;BYCOL(h;LAMBDA(x;SUMA(x)));sumas)

Tenemos el total por columnas.

Creamos otra variable, usamos APILARV, como argumento matriz1, ponemos la variable “h”, como argumento matriz2, ponemos la variable “sumas”.

Tenemos el modelo formado con las ganancias por compañías, los costos por compañía, el total para cada compañía, y, el total de ganancias, costos y diferencia.

Creamos otra variable, usamos una constante de matriz, entre comillas dobles, ponemos, "Compañia", "Ganancias", "Costos", "Diferencia", como delimitador usamos la barra inclinada, cerramos llaves.

Usamos el argumento cálculo de LET, usamos APILARV, como argumento matriz1, ponemos la variable “j”.

Como argumento matriz2, ponemos APILARH, como argumento matriz1, ponemos la variable “a”, como argumento matriz2, ponemos la variable “i”.

Obtenemos el modelo con los encabezados y empresas.

Vemos que tenemos un error, usamos la funcion SI.ERROR delante de APILARV, en caso de que la funcion APILARV nos devuelva un error, que ponga la palabra “Total”.

Ya tenemos nuestro ejemplo resuelto.

Miguel Angel Franco

¿ Listo para empezar a usar Microsoft Excel?

Ganancias y costos por compañia

Entradas recientes

Comentarios