Esta es la tabla que aparece en la primer hoja (que llamaremos "Datos")
Queremos que cada línea aparezca en otra hoja. Los viajes de la ruta 1 en una hoja que se llamará "Ruta 1", los de la ruta 2 en "Ruta 2", etc.
Hay dos formas más o menos inmediatas de resolver el problema: macros y tablas dinámicas. Con tablas dinámicas no estaríamos distribuyendo los datos, pero podríamos generar con facilidad listas por ruta.
Pero el desafío es hacerlo con fórmulas. No es que esté aburrido y no tenga lo que hacer. Pero curiosamente he recibido varias veces esta consulta en las últimas semanas. A veces se trata de transporte por rutas, como en el ejemplo, a veces una escuela de fútbol que lleva un registro de alumnos por edad, a veces una tienda que quiere manejar el inventario por tipo de producto.
Finalmente decidí aceptar el desafío, a pesar que la solución que propondré más adelante no es eficiente como el uso de tablas dinámicas o macros.
El primer paso es crear tres hojas, una para cada ruta, donde pondremos las fórmulas que reflejaran los datos correspondientes de la hoja Datos. En estas hoja ponemos los mismos encabezamientos como en Datos, pero agregamos una columna, "Orden"
En la celda A2 ponemos esta fórmula: =SI(Datos!$D2=1,Datos!A2,"")
Esta fórmula trae el contenido de la celda A2 de Datos, si el valor de la columna D de Datos es 1, es decir, la ruta 1. En la Hoja Ruta 2 copiamos la misma fórmula, pero cambiamos el argumento Datos!$D2=1 por Datos!$D2=2.
Copiamos la fórmula en las celdas B2 y C2 (prestar atención a los símbolos $ en las direcciones de la celda) y luego al rango A3:C11.
Como se puede ver, los valores que aparecen en el rango son los de la ruta 1. Sino, aparece una celda en blanco.
En la celda D2 ponemos esta fórmula, que ya explicamos en la nota sobre ordenar texto en Excel con fórmulas,
=CONTAR.SI($A$2:$A$11,"<="&A2) y la copiamos al rango D3:D11.
Como se puede apreciar, esta fórmula hace las veces de la función JERARQUIA para textos. Si bien las fechas son números, y por lo tanto podríamos usar JERARQUIA, el problema surge con las celdas en blanco. Por ese motivo usamos la fórmula señalada.
Ahora creamos una segunda tablas en el rango F1:H11. En la primer fila copiamos los encabezamientos. En la celda F2 ponemos esta fórmula
=INDICE($A$2:$E$11,COINCIDIR(FILA()-1,$D$2:$D$11,0),COINCIDIR(F$1,$A$1:$C$1,0))
que copiamos al rango F2:H11
En la nueva tabla, las líneas que pertenecen a la ruta aparecen ordenadas por fecha, y las que pertenecen a otras rutas aparecen como #N/A. Para mejorar la apariencia de la lista podemos aplicar Formato Condicional
dándole color blanco a la fuente de la celda que cumple la condición, para hacer "desaparecer" el resulta #N/A. También hemos ocultado las columnas A:E.
¿Cómo funciona la fórmula con al función INDICE? Esta función da como resultado el valor de una matriz que se encuentra en la celda determinada por el valor del segundo argumento (fila) y del tercer argumento (columna). La matriz es el rango A2:E11, que contiene también la columna Orden.
La fila es determinada por la función COINCIDIR(FILA()-1,$D$2:$D$11,0), que encuentra en que lugar del rango D2:D11 se encuentra la fila con el número de orden 1, 2 etc. El número de orden es determinado por el número de fila de la celda que contiene la fórmula, menos 1. Así, en la fila 2 aparecerán los datos que tienen el número de orden 1, en la fila tres los que tienen el número de orden 2, y así sucesivamente.
La segunda función COINCIDIR, hace que los datos en la celda correspondan a la columna indicada.
Como verán, hemos encontrado una solución con fórmulas al problema. Pero esta solución tienen dos problemas importantes, si queremos trabajar con gran cantidad de datos:
- la función FILA(), es volátil y causa que cada cambio en la hoja provoque un recálculo de toda lo hoja;
- INDICE y COINCIDIR tienden a ser lentas cuando trabajamos con gran cantidad de datos.
El archivo se puede descargar 
aquí
Technorati Tags: MS Excel
Amigo, te felicito por tu blog, necesito esta formula para resolver varias cosas, pero de un problemita, me gustaria saber porque cuando inserto la formula =CONTAR.SI($A$2:$A$11,"<="&A2) me arroja un error: #NAME?
ResponderBorrarAmigo muchas gracias de nuevo, me puedes ayudar a hacer esta clasificacion, pero que cada hoja resultante sea de acuerdo a la fecha, es decir una hoja por fecha, y en orden de aparicion.
ResponderBorrarGracias
Hola Galileo
ResponderBorrarprueba usar ";" (punto y coma) en lugar de "," como separador de argumentos en la función.
Si no se soluciona con eso, puedes mandarme el archivo para que le de un vistazo
Estimado Curioso
ResponderBorrartienes que usar el msimo método del ejemplo, sólo que creas una hoja para cada fecha y en lugar de Ruta para identificar la hoja, usas el 1 para la primer fecha, el 2 para la segunda y así sucesivamente.
Hola quisiera hacerte una consulta siguiendo el ejemplo de reparto lo que sucede es que el mismo dia varios choferes llevan varios pedidos a distintos lugares y como puedo hacer para que en un cuadro adjunto me aparezca la efectividad de cada uno es decir cuantos entrego satisfactoriamente y cuantos no, gracias por tu ayuda.
ResponderBorrarNecesitarías crear un campo auxiliar que muestr si la entrega fue satisfactoria o no. Luego en base a este campo puedes calcular con CONTAR.SI cuantos fueron entregados satisfactoriamente por chofer.
ResponderBorrarExcelente pagina, me ha sacado de muchos problemas. Quisiera que me colaborara en la construcion de una macro que me permita ordenar las hojas de acuerdo a una lista ya establecida
ResponderBorrarHe tratado el tema en esta nota
ResponderBorrarHola buenas tardes desde España, en primer lugar felicitarle por el blog, me ha servido de consulta en numerosas ocasiones.
ResponderBorrarMi consulta es la siguiente, He creado una hoja muy parecida a la de su ejemplo, el problema me surge cuando doy de alta dos operaciones con la misma fecha. Me deja las celdas correspondientes en blanco.
Lo he intentado solucionar con la función Jerarquia, pero soy incapaz.
Muchas gracias de antemano por su atención.
Hola,
ResponderBorrarefectivamente,el modelo no toma en cuenta la situación en la cual una fecha se repite. Hay que modificar dos fórmulas en las hojas de las rutas:
- en la columna A hay que poner
=SI(Datos!$D2=1,Datos!A2,NOD())
en lugar de
=SI(Datos!$D2=2,Datos!A2,"")
para generar resultados #N/A cuando una fecha no existe para la ruta. Con esto evitamos que la fórmula en la columna D le de un número de orden.
- En la columna D modificamos la fórmula a:
=SI(ESERROR(A2),NOD(),CONTAR.SI($A$2:A2,"<="&A2))
Además de agregar las funciones ESERROR y NOD es importante notar que en el rango de la función CONTAR.SI el segundo término del rango es una dirección relativa (sin los símbolos $). Al copiar esta fórmula el rango va cambiando a $A$2:A3, $A$2:A4, etc. Esto evita que se produzcan duplicados.
Hola buenos días de nuevo.
ResponderBorrarEn primer lugar agradecierle su rápida respuesta.
He hecho las modificaciones de las funciones que me recomendó y desafortunadamente no doy con la solución, ahora la hoja no me reconoce algunas fechas, solo algunas, que son las que me coloca por orden. las duplicadas si me las ordena, pero me deja sin leer algunas otras que no están duplicadas.
Lo he repasado unas cuantas veces y no veo el fallo´: ¿En que puedo estar fallando?
Gracias de antemano por su atención.
Te sugiero que me mandes el archivo y sigamos la consulta por línea privado. Por favor, fijate en la nota del enlace "Ayuda" (en la parte superior del blog)
ResponderBorrarHola buenos días.
ResponderBorrarCordiales Saludos.
Ya había leído en más de una ocasión, la nota de ayuda que tiene a principio del blog. De ahí que no haya optado por enviarle el archivo por correo; preferí, comentarlo en su blog.
De todas formas se lo envío por mail.
Un saludo y mi más sincero agradecimiento.