Excel - Ayuda con búsqueda y aplicación de fórmula adecuada...

Buenos días

Estoy entrenándome en excel ya que estoy aprendiendo estadística de aerolíneas y de aeropuertos, actividad en el que llevo algo más de ocho meses. Yo compilo los datos de unas estadísticas que publica oficialmente la aeronáutica civil de mi país y con ellas puedo hacer los análisis respectivos.

De momento hago una compilación manual de las rutas hacia y desde el aeropuerto de mi ciudad seleccionando todos los datos de la base de datos y crear una tabla dinámica en la que el filtro es ORIGEN y la fila es DESTINO, las columnas los MESES (ya que cada base de datos es anual) y los valores la SUMA de PASAJEROS, tal así que si yo en ORIGEN filtro a BAQ (que es el código IATA del aeropuerto de mi ciudad) en las filas aparecen los destinos con su respectivo número de pasajeros por mes PERO esta suma de pasajeros es solo de ida. Para yo averiguar los pasajeros de vuelta simplemente invierto los parámetros así: DESTINO en filtro y ORIGEN en fila. Lo malo de hacer esto es lo engorroso que es consolidar los datos de ida y los de vuelta en uno solo (suma de pasajeros bidireccional) ya que, como siempre he hecho, me tocaría copiar y pegar los valores en otra hoja, sumarlos y tener así la suma de pasajeros en cada ruta hacia y desde BAQ.

Dicho esto, pensé en usar una fórmula SI, creando una columna adicional en mi hoja de excel que les muestro en la imagen abajo y la nombré DESTINO NN pues la idea era que con esa fórmula poder "jalar" el destino que hay entre BAQ y BAQ (por ejemplo un BAQ-MIA-BAQ), es decir un viaje redondo o ida y vuelta y de esa manera simplemente en mi tabla dinámica tener por fin la suma de pasajeros TOTAL en ese trayecto o cualquier otro hacia y desde BAQ. Lo malo es que no me ha funcionado.



¿Hay alguna fórmula que me ayude con dicha tarea? Necesito avanzar más rápido por que debo analizar el movimiento de paxes (pasajeros) del aeropuerot de mi ciudad en un lapso de 15 años y no quiero "manualizar" todo por que debo cumplir unos tiempos de entrega y así poder crear otros análisis: tendencias, crecimientos, decrecimientos, factibilidad de rutas potenciales, rutas no servidas, etc.

Espero haber sido claro.

Gracias mil por su ayuda.

Hola Alberto:
No entiendo bien tu ejemplo, que no lo veo en la imagen. Caso de MIA, por ejemplo. Lo citas en el texto pero no se ve en la imagen.
En cualquier caso, la solución está en la tabla dinámica. Pon el destino (MIA), el que sea, en el área de filtros.
Danos más detalles, que entendamos bien el caso.
https://wordexperto.com/blog/

Buenos días, gracias por tu pronta respuesta.

Vamos a ver si ahora me explico un poco mejor. Yo debo calcular la suma de pasajeros movidos entre BAQ-CIUDAD X-BAQ en el transcurso de doce meses. Yo hasta ayer lo hacía manualmente creando una tabla dinámica con el origen como FILTROS, destino como FILAS, meses como columnas y SUMA de pasajeros como VALORES, entonces así obtenía la suma de pasjaeros solo de ida con origen BAQ, copiaba y pegaba todos esos valores en una hoja nueva y volvía a la tabla dinámica para sacar la suma de los pasajeros de vuelta con destino BAQ, copiaba y pegaba dicha tabla resultante en la hoja creada anterioremente para los de ida y me tocaba manualmente sumar ambos trayectos uno por uno para obtener pasajeros totales vuelo redondo en cada ruta para tabularlos en otro documento de excel global así:




En dicha tabla (creada por mi) cada mes tiene la suma de los pasajeros tanto de ida como de vuelta entre el aeropuerto BAQ y cada aeropuerto servido (o no) tanto a nivel doméstico como a nivel internacional. Es muy laborioso hacerlo así pero de momento no tengo otra forma de hacerlo.

Entonces estuve investigando y se me ocurrió aplicar la fórmula SI de la siguiente manera: en el libro original de excel (base de datos origen-destino BAQ 2014) lo que hice fué agregar una columna adicional y lo nombre DESTINO NN y a la primera celda de dicha columna apliqué la fórmula SI de tal manera que si el destino de la celda de la columna de DESTINO es igual a BAQ entonces el ORIGEN debe ser cualquiera que arroje en el que haya registros de movimiento de pax desde tal destino haica BAQ. Tal como está en la imagen que adjunto:




Y la idea mía era que en la siguiente tabla dinámica me arrojara tanto los destinos servidos como la suma total de pasajeros mensual y total anual desde y hacia BAQ en todos los destinos en los que haya registros, pero como verás en la imagen, no sucedió así teniendo origen y destino como FILTROS y filtrando solo a BAQ:




No hay resultados. Más sin embargo cuando simplemente dejo origen en FILTROS, ahí si me arroja los destinos en las filas con la respectiva suma mensual de pasajeros:




Pero es lo mismo ya que solo es la suma de los pasajeros registrados HACIA dichos destinos pero no están registrados los de vuelta DESDE esos destinos.

Evidentemente mi razonamiento de aplicar la fórmula SI le falta algo para que registre BAQ como origen y al tiempo como destino y en la columna DESTINO NN me arroje todas las ciudades con movimiento de pax registradas tanto desde BAQ como hacia BAQ.

Espero ahora si haberme hecho entender

¿Qué debo hacer? Agradezco de antemano cualquier ayuda, me falta tabular como 12 tablas anuales más.

Sube el ejemplo, para que podamos experimentar. Que tal probando, como te comenté, si todos los vuelos salen o parten de BAQ, que los filtros los hagas por el otro aeropuerto.
https://wordexperto.com/blog/

Adjunto el archivo de marras. Me ha sacado canas, espero a uds les vaya mejor.

• Origen-Destino-Mes-2014-baq.xlsx.zip(2,9 MB)

Lo siento Alberto, ahora mismo no se me ocurre otra forma de hacerlo que filtrar en dos tablas dinámicas, origen y destino por BAQ o la inversa, menos eficiente. No veo lo de la columna adicional. Lo siento.
https://wordexperto.com/blog/

• Origen-Destino-Mes-2014-baq.rar(2,6 MB)

Gracias, seguiré investigando de todas maneras...

Ahora te he subido las tablas que he estado probando. No se me ocurre que se pueda hacer otra cosa.
https://wordexperto.com/blog/

Ojalá algún otro forista pueda orientarnos más con alguna manera creativa. La idea es que en la columna DESTINOS NN vaya una fórmula lógica que sea algo así como "Si DESTINO es BAQ y ORIGEN es BAQ entonces DESTINOS NN debe ser aeropuerto X" en el que el DESTINOS NN me arrojen el código IATA del aeropuerto con movimientos de pax hacia y desde BAQ.

La fórmula para la columna NN es: =SI(O([@Origen]="BAQ";[@Destino]="BAQ");[@Pasajeros];""), luego se filtra quitando las vacías. A ver si le encuentras utilidad,
https://wordexperto.com/blog/

Vamos a intentarlo, ya te comentaré...

UPDATE

Puse la fórmula como sugeriste:


Pero, en la tabla dinámica, en el campo de filas para DESTINO NN no me salen los destinos ni su respectiva suma de pasajeros

Si sale. Será problema de actualizar.
Mira la aportación de Rafael. Puede ser la solución. Poner todos los vuelos en una sola columna y otra, al lado, que indique si es origen o destino. Así se puede analizar mejor lo que necesitas con la TD.
https://wordexperto.com/blog/

• Origen-Destino-Mes-2014-baq.rar(3,4 MB)

Buenas Alberto,

A ver si con lo que te he preparado te puedo ayudar a agilizar tu tarea.

Te cuento, en la pestaña Base de Datos en principio deberías meter toda la información, como decías que tenías que sacar varios años pues aquí pega de forma vertical cada año.

Después tienes una macro, que se ejecuta con un botón desde esa pestaña.

Lo que hace es algo muy sencillo, copiar y pegar de forma que nos coja tanto el origen como el destino para igualarlo todo en la pestaña MONTATABLA.



Después actualiza una tabla dinámica que hay en TABLADINAMICA.




Como resultado obtienes Año, Mes y Terminal (eso he entendido yo) y finalmente a la derecha tienes una fórmula que lo único que hace es sumar tanto si es origen como si es destino:

=SUMAR.SI.CONJUNTO('Base de Datos'!D:D;'Base de Datos'!B:B;C2;'Base de Datos'!G:G;A2;'Base de Datos'!H:H;B2)+SUMAR.SI.CONJUNTO('Base de Datos'!D:D;'Base de Datos'!C:C;C2;'Base de Datos'!G:G;A2;'Base de Datos'!H:H;B2)

Es un poco lenta, pero creo que te puede solucionar el problema. Había pensado en que la macro continuara copiando estos datos y pegándolos como valores para terminar montando una tabla dinámica estupenda para que puedas trabajar con ella, pero tiene un problema y es que hay que asegurarse que la formula, que como decía era lenta, calcule completamente, y a veces esto falla en las macros aunque hay maneras de conseguirlo.

Dime si te sirve así y te termino de montar esa macro estupenda.

Un saludo.
Rafa
http://officepostit.es

• Origen-Destino-Mes-2014-baq.zip(3,4 MB)

Interesante tu propuesta, la cuestión es que solo necesito sacar los paxes movidos hacia y desde BAQ (y tienes razón, son aeropuertos/terminales). La idea de la columna DESTINOS NN (o cualquier nombre que uno quiera asignarle, yo le puse ese) es que en ella aparezcan los destinos con movimiento de pasajeros hacia Y desde BAQ, cosa que cuando yo haga la tabla dinámica simplemente ponga en el campo filtros a DESTINO y ORIGEN, en el campo filas a DESTINOS NN y en valores la suma de PASAJEROS, cosa que solo tenga que filtrar a ORIGEN como BAQ y a DESTINO como BAQ para que en las filas de destinos NN aparezcan los aeropuertos con movimiento de pax hacia y desde mi ciudad (BAQ) y ya. No lo he logrado. Espero haberme hecho entender.

Adicional, si alguien quiere comunicarse conmigo por Skype para ayudarme sería fabuloso.


Gracias mil.

¿Necesitas tener el lugar de Destino y el de Origen? Si no es así es mucho más fácil que lo que hemos estado haciendo. Tan sólo poner en la columna NN la fórmula que te comentaba wordexperto, lo único es que en la tabla dinámica lo que tienes que utilizar es esa columna en los filtros. También lo que te comentaba el compañero, usa la macro que te monté para traerte también si son Origen o Destino, y así la Tabla Dinámica funcionará a las mil maravillas.

DESTINO NN BAQ

Etiquetas de fila Suma de Pasajeros
2014 2377717
1 197032
2 163427
3 191903
4 190040
5 180626
6 191318
7 209331
8 202898
9 194254
10 219814
11 208038
12 229036
Total general 2377717

Tirale así y sino lo que te comentaba, modificar la macro para que te traiga tambien las columnas de Origen y Destino y listo. Mandame un mensaje con tu Skype y luego intento contactarte, que ahora mismo me pillas muy liado.

Un saludo
Rafa
http://officepostit.es

Hola

Te envio tu archivo a ver si te sirve de algo, le agregue otra columna ORIGEN NN

La columna ORIGEN NN puede ser no indispensable, unicamente te sirve para que al momento de actualizar tu tabla dinamica no tengas que estar verificando el filtro de DESTINO NN, por que te agrega los codigos que no tienen relacion con el aeropuerto a buscar (un unico renglon con codigo en blanco).

por cierto en la tabla de los datos esta una celda en verde, ahi pondrias el codigo del aeropuerto a buscar.

• Origen-Destino-Mes-2014-baq.rar(3,4 MB)

OMG! ¡Así es!!!

Ahora me toca desmenuzarlo para poder aplicarlo a todas las demás bases de datos. ¡Gracias mil!

Buenas tardes Pedro.

He tratado de replicar las fórmulas que aplicaste en tu ejemplo y no me resultan, creo que no estoy familiarizado con usar el símbolo de la arroba en una fórmula (que según mi sentido común señala a una etiqueta en la hoja de excel).

¿Podrías explicarme la construcción lógica de esta fórmula? Gracias mil

Buen dia!

Disculpa por no haber contestado.

te envío de nueva cuenta el archivo sin la utilización de la @, para que sea mas comprensible.

y respondiendo a tu solicitud te explico primero lo siguiente;

1.-a tus datos le di formato de tabla.
2.- cuando en una formula, haces mención a una celda que esta dentro de una tabla, excel automáticamente cambia la referencia tradicional que conocemos, ejemplo en tus datos yo seleccione a la celda B4 y excel la cambio automáticamente a [@Origen]. le asigno el nombre de la columna y la @ significa que se esta refiriendo a una fila especifica de esa columna en este caso la fila 4.
3.- también en la formula se encuentra una parte que dice Codigo, lo que hice fue asignarle un nombre a la celda que esta de color verde, desde el menu formulas/administrador de nombres lo que puedes sustituir por $B$1.

Saludos

• Origen-Destino-Mes-2014-baq.rar(3,3 MB)

¡Gracias!

Apenas estoy abriendo mi compu, son las 7am acá. Después de dos o tres tazas de café checaré lo que me comentas. Y gracias por aclarar que le diste formato de tabla a la hoja de excel. Ahora si espero lograr entender bien la fórmula.

Si tengo más dudas te conctaré

Saludos

Beto