miércoles, 24 de marzo de 2010

Reordenación de listados en horizontal.

Supondremos que disponemos de un listado de información cruzada, con datos alternos en nuestras filas, del cual pretendemos obtener a nuestra discrección un nuevo listado ordenado en vertical.
Para ello trabajaremos con distintas herramientas ya conocidas por los habituales del blog, entre otras asignaremos nombres a diferentes rangos, configuraremos una celda validada, y haremos uso de las funciones INDICE, JERARQUIA e INDIRECTO entre otras.
En el ejemplo que proponemos tenemos un listado de clientes de un club polideportivo, con información para cada socio de los deportes que practica.
Esta es nuestra tabla de información cruzada:

Reordenación de listados en horizontal.


Nuestro primer trabajo consistirá en construir una tabla auxiliar aneja a la original, donde por medio de una función SI CONDICIONAL, convertiremos las marcas de los deportes de cada socio en un texto; mediante la función
=SI(C2<>"";C$1;"")

Reordenación de listados en horizontal.


El paso siguiente de nuestro ejercicio será asignar nombres a los rangos recién creados (de nuestro hoja de cálculo 'Horizontal')
Aaa_Aaaaa =Horizontal!$H$2:$L$2
Bbbb_Bbbbb =Horizontal!$H$3:$L$3
Cccc_Ccccc =Horizontal!$H$4:$L$4
Dddd_Ddddd =Horizontal!$H$5:$L$5
Eeee_Eeeee =Horizontal!$H$6:$L$6
Ffff_Ffffff =Horizontal!$H$7:$L$7
Ggggg_Gggggg =Horizontal!$H$8:$L$8
Hhhhh_Hhhhh =Horizontal!$H$9:$L$9
Ya que los nombres de rangos no pueden tener espacios en blanco, he añadido un guión bajo en su lugar; puesto que para posteriores pasos me interesa mantener lo máximo posible la estructura original. Ver cómo realizar este paso en asignar nombres a rangos.

Otra etapa en nuestro proceso será generar una celda validada tipo Lista, con el rango de los nombres de los socios:

Reordenación de listados en horizontal.


Con esta validación no habremos terminado, ya que se trata de relacionar los Nombres y Apellido de nuestros Socios, con los Nombres definidos (que tienen un guión bajo entre Nombre y Apellido); por este motivo en una celda abajo, insertaremos la siguiente función
=SUSTITUIR(N2;" ";"_")
donde N2 es la celda con nuestra validación.

Reordenación de listados en horizontal.



Ya casi terminamos, un poco de paciencia...

Construimos unas columnas auxiliares que nos van a permitir ordenar esos deportes practicados por nuestro Socio elegido al desplegar nuestra celda N2. La primera columna auxiliar listará los 'deportes' del socio en cuestión, empleando el nombre asignado para cada socio. Con la función INDIRECTO anidada dentro de la función INDICE, con lo que convertimos un valor en un Rango:

Reordenación de listados en horizontal.


En otra de las columnas auxiliares, con la función
=SI(CELDA("contenido";P2)="";0;FILA())
damos un valor a cada deporte, según tenga valor esa fila o no.

Reordenación de listados en horizontal.


Hemos conseguido que para cada vez que haya un Deporte nos asigne un número, en concreto el número de fila en que se encuentra. Da igual qué fila sea, lo importante es tener un valor numérico con el que ordenar posteriormente.

En la última de las columnas auxiliares aplicamos sobre la anterior la función
=JERARQUIA(Q2;$Q$2:$Q$6)
con lo que conseguimos una ordenación de nuestros deportes.

Reordenación de listados en horizontal.


Acabamos por fin...
Construimos nuestro listado en vertical, ya ordenados, de nuestro Socio elegido.

Reordenación de listados en horizontal.


Con la función
=INDICE($P$2:$P$6;COINCIDIR(FILA()-5;$R$2:$R$6;0))
hemos conseguido lo que deseábamos; analicémosla:
  • con COINCIDIR(FILA()-5;$R$2:$R$6;0) obtenemos el número de columna que emplearemos en la función INDICE

  • con la función INDICE tomando como matriz la primera de nuestras columnas auxiliares, i.e., los Deportes listados; al asignarle la coincidencia anterior, tenemos el valor deseado.

Vemos que con cuatro pasos hemos sido capaces de convertir de manera automatizada unos listados desordenados en horizontal en un listado vertical ordenado.

6 comentarios:

  1. HOLA, QUISIERA SABER A QUE SE LLAMA TABLA AUXILIAR? GRACIAS

    ResponderEliminar
  2. Hola,
    llamo Tabla auxiliar a aquella que empleo como paso intermedio para lograr un fin, es decir, una tabla secundaria que se emplea mediante fórmulas (normalmente) para traer algún dato a la tabla principal.
    Espero haberlo explicado bien.
    Slds

    ResponderEliminar
  3. Hola!
    Podrías ayudarme con una validación de datos de una tabla que debo preparar. Tnego dos columnas: RUBRO y PRODUCTO. Dentro de rubro hay una lista de validacion con nombres de rubros, por ej: informatica, tiempo libre, educacion, etc. En la columna producto hay muchos productos que estan relacionados con los rubros anteriores, por ej. PC, libros, pelota de futbol.
    Lo que necesitaria es hacer algun tipo de validacion, para que cuando yo seleccione en RUBRO Informatica, solo me muetre en PRODUCTOS los relacionados con ese rubro, por ej, computadora, impresora, mouse, etc.
    Osea, que segun la palabra que seleccione en rubro, cambie la lista de validacion en la columna Producto. Me explico? Es posible? Desde ya agradezco tu ayuda!

    ResponderEliminar
  4. Hola Matías,
    lo que necesitas es una doble validación vinculada...
    echa un vistazo a estos posts subidos en general:
    http://excelforo.blogspot.com/search/label/Validacion

    y en particular:
    http://excelforo.blogspot.com/2009/10/ejemplo-de-doble-validacion.html

    http://excelforo.blogspot.com/2010/02/validacion-de-celdas-con-lista-o-tabla.html

    http://excelforo.blogspot.com/2010/04/validacion-de-celdas-anidadas-y.html

    En ellas encontraras la forma de solucionarlo.
    Un saludo

    ResponderEliminar
  5. hola amigo me parece interesante el articulo, pero yo tengo otro problemita espero me puedas ayudar. Tengo una lista de proveedores y cada proveedor tiene una lista de servicios y cada servicio tiene una tasa de impuesto q hay q calcular.

    Proveedor1 serv.1 16% Serv.2 0% Serv.3 11%
    Proveedor2 " " " " " "

    asi susecivamente uno de los problemas q tengo es que cada proveedor tiene distintos servicios y no siempre coinciden con el de arriba eso es en mi base de datos por asi decirlo. que necesito.
    1 desplegar una lista de proveedores que ya se crear con una validacion de datos como explicas en este tema.
    2 que al seleccionar el proveedor me despliegue una lista de los servicios.
    3 ya ubicando el servicio con una formula de buscar supongo asignar la tasa de impuesto. para despues dejar caer mas informacion. Espero me ayudes y te entretenga esto como me lo hace a mi.. jaja Saludos a todos en el foro

    ResponderEliminar
  6. Hola Gabriel,
    veo que la primera parte la tienes seleccionada, habrás creado una validación de datos, empleando como rango de datos la columna de 'Proveedores'. Para el segundo paso deberás asignar por cada fila un nombre al rango de los servicios.
    Necesitaría saber cómo lo tienes estructurado, pero deberíamos aplicar una nueva validación asociada al valor de la primera que despliega la lista de proveedores.
    El tercero podría construirse, dependiendo como tuvieras distribuido los datos, con alguna función de búsqueda, quizá matricial.
    Necesito saber como queda tu tabla de datos para darte una solución.
    Mándame un ejemplo a
    excelforo@gmail.com
    Slds

    ResponderEliminar