Encontrar cuantas empresas operan una ruta

PolF · el 5 de marzo

Tengo una base de datos con datos de vuelos comerciales. Me interesa saber cuantas compañías operan cada ruta. Tengo 4 columnas con datos, en la primera el nombre de la compañía, en la segunda el orígen y destino, en la tercera que días de la semana opera y en la cuarta el año. Entiendo que la tercera no es necesaria para el cálculo, pero es la que hace que un mismo origen y destino operado por la misma compañía aparezca en dos filas distintas. Por ejemplo si hay un vuelo que opera los lunes y los miércoles y otro que solo los jueves, viernes y sábados.

Entiendo que la solución es utilizar alguna cadena de contar.si.conjunto, pero no me acaba de salir. La primera condición sería que sea del mismo año, la segunda la misma ruta y luego la compañía. El resultado que me gustaría tener en la columna "Num operadores" es un número que se corresponda al número de compañías que la operan. No se si se puede hacer de una sola vez o quizás debería hacerlo por pasos?

Adjunto un archivo con una muestra de los datos,

Muchas gracias,

Pol

Muestra aerolineas.xlsx

Oscar_MRF · el 5 de marzo

Que tal, verificando la consulta adjunto un propuesta de solución empleando Power Query.

Espero le sea útil.

Muestra aerolineas (Solucion PQ).xlsx

PolF · el 6 de marzo

Muchas garcias Oscar,

La verdad es que no he usado nunca power query, pero parece muy interesante!

Para este caso, para poder seguir luego analizando con tablas dinámicas, necesitaría que la información apareciera para cada una de las filas en el documento actual, es decir que en cada línea me dijera que esa rta la operan x operadores. Así luego puede filtrar por compañía y otras variables.

Muchas garcias de nuevo,

Pol

Victor7 · el 6 de marzo

Hola a ambos,

Por si acaso te es útil, te adjunto tu archivo con los datos de vuelos, compañías y número de compañías que operan, obtenido con fórmulas.

Veo que tienes M365, así que los cálculos pueden simplificarse en gran medida.

La única fórmula de F4 despliega una matriz vertical con los vuelos (sin repeticiones) de la columna B.

Y la fórmula de G4 recoge (horizontalmente, en este caso) las compañías que operan ese vuelo concreto. Esta fórmula de G4 la copiamos en vertical hasta G15 para completar todos los datos.

Por último, en la columna K tenemos el total de compañías que aparecen en el rango G:J

- - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Puedes ampliar esa matriz con más datos (días, año,...) corrigiendo el sentido de las fórmulas. Como ves, me he limitado a vuelos y compañías.

Saludos,

Muestra aerolíneas (B).xlsx

PolF · el 6 de marzo

Muchas garcias Victor,

No se porqué, al pasarlo a mi hoja de texto en muchas de las celdas me da un error: #CALC! a pesar que sí hay datos...

Pero con lo que me has mandado em he dado cuenta que con una tabla dinámica puedo ver el número de compañías que operan y por año (ver pestaña TD). Lo que me faltaría ahora es poder asociar esta info a cada una de las filas en la hoja uno. Es decir, si el texto en B coincide con alguno de los valores en el rango A5:16 y el valor en la celda Time series es 2019-9 que me devuelva en la columa E de la hoja uno el valor de la columna B en la hoja TD, si no, será de 2022 y quiero que me devuelva el mismo valor que en la columna C de la hojaTD.

No se si me explico muy bien. Al final lo que necesito es poder responder a preguntas como, cuantas rutas opera ryanair en monopolio, o cual es el número medio de competidores en todas las rutas de ryanair. Por esto me parecía que lo más útil sería poder poner en la columna E de la hoja 1 el número de competidores y así después con tablas dinámicas poder filtrar y hacer cálculos.

Muchas gracias de nuevo por la ayuda.

Saludos

Pol

Muestra aerolíneas (B)3.xlsx

Victor7 · el 6 de marzo

La verdad es que estas explicaciones, al menos a mí, me resultan un tanto nebulosas. Y también tengo la sensación de que hay planteamientos diferentes a los expresados inicialmente.

hace 8 horas, PolF dijo:

Lo que me faltaría ahora es poder asociar esta info a cada una de las filas en la hoja uno. Es decir, si el texto en B coincide con alguno de los valores en el rango A5:16 y el valor en la celda Time series es 2019-9 que me devuelva en la columa E de la hoja uno el valor de la columna B en la hoja TD, si no, será de 2022 y quiero que me devuelva el mismo valor que en la columna C de la hojaTD.

Después de este párrafo hablas de "...cuantas rutas opera ryanair en monopolio, o cual es el número medio de competidores en todas las rutas de ryanair...". De verdad, no sé si estás hablando de lo mismo, o son conceptos diferentes. Y por cierto, ¿qué entiendes por "número medio de competidores"? ............. Puedo entender que quieras saber cuántos competidores tiene Ryanair en el vuelo BCN-PMI (por ejemplo), pero el significado de "número medio de competidores", se me escapa.

En fin, creo que lo mejor sería que subieras otro archivo con el diseño y resultados finales que esperas obtener, introducidos a mano, en las celdas que corresponda. Tal vez viendo el esquema en su globalidad, pueda pensarse algo para llevarlo a buen fin.

Saludos,

Gerson Pineda · el 6 de marzo

¡Hola a todos!

hace 11 horas, PolF dijo:

No se si me explico muy bien. Al final lo que necesito es poder responder a preguntas como, cuantas rutas opera ryanair en monopolio, o cual es el número medio de competidores en todas las rutas de ryanair. Por esto me parecía que lo más útil sería poder poner en la columna E de la hoja 1 el número de competidores y así después con tablas dinámicas poder filtrar y hacer cálculos.

@PolF

Como veo que tus datos están tabulados, se pueden cargar a Power Pivot [modelo de datos] y desde ahí, poder realizar distintos cálculos

Quedando algo así la TD

También deje solo por mostrar otra forma, el recuento haciendo uso de funciones 365, como Reduce, Apilarv, Apilarh entre otras

Tus datos los convertí a Tabla de Excel, para subirla al modelo y que los rangos sean dinámicos con la formulación

Recuento Reduce-Apilarh 365 y Dax_GP.xlsx

PolF · el 13 de marzo

En 6/3/2023 at 20:57 , Victor7 dijo:

La verdad es que estas explicaciones, al menos a mí, me resultan un tanto nebulosas. Y también tengo la sensación de que hay planteamientos diferentes a los expresados inicialmente.

Después de este párrafo hablas de "...cuantas rutas opera ryanair en monopolio, o cual es el número medio de competidores en todas las rutas de ryanair...". De verdad, no sé si estás hablando de lo mismo, o son conceptos diferentes. Y por cierto, ¿qué entiendes por "número medio de competidores"? ............. Puedo entender que quieras saber cuántos competidores tiene Ryanair en el vuelo BCN-PMI (por ejemplo), pero el significado de "número medio de competidores", se me escapa.

En fin, creo que lo mejor sería que subieras otro archivo con el diseño y resultados finales que esperas obtener, introducidos a mano, en las celdas que corresponda. Tal vez viendo el esquema en su globalidad, pueda pensarse algo para llevarlo a buen fin.

Saludos,

Hola Victor,

Primero disculpad por tardar en responder, he estado fuera de viaje hasta ayer sin acceso al ordenador. Gracias por tu respuesta. Veo que queriendo dar más información al final estoy complicándolo. He hecho como decías tú, en el excel en adjunto he rellenado manualmente la columna E con la información que necesito que me de la fórmula. He puesto también al lado un cuadro de texto explicando lo que necesito.

Espero que ahora esté más claro.

Muchas gracias y un saludo,

Pol

Muestra aerolineas v2.xlsx

PolF · el 13 de marzo

En 6/3/2023 at 22:43 , Gerson Pineda dijo:

¡Hola a todos!

@PolF

Como veo que tus datos están tabulados, se pueden cargar a Power Pivot [modelo de datos] y desde ahí, poder realizar distintos cálculos

Quedando algo así la TD

También deje solo por mostrar otra forma, el recuento haciendo uso de funciones 365, como Reduce, Apilarv, Apilarh entre otras

Tus datos los convertí a Tabla de Excel, para subirla al modelo y que los rangos sean dinámicos con la formulación

Recuento Reduce-Apilarh 365 y Dax_GP.xlsx 137.07 kB · 5 descargas

Muchas gracias por la respuesta Gerson,

En lo que propones, si lo entiendo bien, el recuento cuenta tantas veces como aparece la ruta y me ha parecido que no discrimina por fecha. Yo necesitaría que solo contara una vez por ruta y compañía y año. Lo he intentado explicar mejor en el adjunto que he puesto en respuesta a Victor.

Muchas gracias de nuevo,

Pol

Victor7 · el 13 de marzo

Hola de nuevo a todos,

Te dejo una solución con fórmulas:

=CONTARA(UNICOS(FILTRAR(A$2:A$1000;(B$2:B$1000=B2)*(IZQUIERDA(D$2:D$1000;4)=IZQUIERDA(D2;4)))))
=TRANSPONER(UNICOS(FILTRAR(A2:A1000;(B2:B1000=B2)*(IZQUIERDA(D2:D1000;4)=IZQUIERDA(D2;4)))))

La primera fórmula te dará el número de operadores, y, por si te interesa, la segunda fórmula el nombre de cada uno de ellos.

Ambas pueden copiarse verticalmente hasta donde sea necesario. Si la 2ª fórmula no te interesa, simplemente bórrala.

Saludos,

Muestra aerolíneas (D).xlsx

PolF · el 13 de marzo

Buenas tardes Victor,

Muchísimas gracias, ahora me funciona perfecto.

Saludos,

Pol

Gerson Pineda · el 13 de marzo

hace 8 horas, PolF dijo:

Muchas gracias por la respuesta Gerson,

En lo que propones, si lo entiendo bien, el recuento cuenta tantas veces como aparece la ruta y me ha parecido que no discrimina por fecha. Yo necesitaría que solo contara una vez por ruta y compañía y año

¡Hola a todos!

Mirando tu nuevo archivo y explicación, ahora queda más claro lo que necesitas

Podría proponer una formula 365, pero Victor, ya lo hizo, por lo que lo hice con TD pero con fórmulas DAX, de tal forma que se puedan hacer más mediciones y análisis

Quedando así

Adjunto archivo

Recuento distintivo TD-DAX_GP.xlsx