miércoles, enero 06, 2010

Usos de Listas (Excel 2003) o Tablas (Excel 2007)

En Excel 2003 Microsoft introdujo una nueva funcionalidad: las listas. En Excel 2007 las listas evolucionaron en funcionalidad y eficiencia y pasaron a llamarse Tablas. Las Listas/Tablas son una de las funcionalidades más subestimadas por los usuarios de Excel.

En la nota anterior sobre rangos dinámicos vimos qué fácil es crearlos usando Listas o Tablas y cómo nos permiten sobreponernos a las limitaciones de la función INDIRECTO para crear listas desplegables dependientes.

En esto nota me extenderé sobre las otras bondades de esta funcionalidad.

Empecemos por la más trivial de las preguntas: ¿qué es una Lista/Tabla? Volvamos al ejemplo de la cadena de tiendas que mostramos en las notas sobre tableros de comandos (dashboards)






Las columnas F y G son índices que nos muestras las compras y la ganancia por cliente que calculamos con una sencilla operación aritmética.

La matriz B2:G6 es una rango rectangular de datos ordenados, pero aún no es una Lista /Tabla en términos de Excel. Para convertir este rango en una Tabla (Excel 2007), hacemos lo siguiente:

1 – seleccionamos alguna de las celdas del rango

2 – en la cinta activamos la pestaña Insertar y elegimos Tabla








3 – Seleccionamos el rango de la tabla y marcamos la opción “La tabla tiene encabezados”



4 – Excel convierte el rango seleccionado en una Tabla y abre la pestaña Diseño de Herramientas de tablas.

Como vimos en la nota anterior, aquí podemos darle un nombre significativo a la tabla




En Excel 2003 el proceso es similar, pero algo diferente



Las Tablas/Listas se diferencian de los rangos normales en, entre otras cosas:

  • al crear una Lista/Tabla Excel agrega automáticamente el Autofiltro
  • la Lista/Tabla se expande automáticamente al agregar una celda. Todas las referencias ligadas a la Lista/Tablas se adaptan al cambio automáticamente (gráficos, nombres, fórmulas)
  • al agregar valores a la Lista/Tabla, todas las fórmulas en columnas con fórmulas son copiadas automáticamente
  • si usamos la tecla TAB para navegar, Excel selecciona automáticamente la celda siguiente de izquierda a derecha y de arriba hacia abajo

Todo esto hace que el uso de Listas/Tablas sea muy eficiente para construcción de modelos dinámicos, en especial tableros de comandos.

Veamos algunos ejemplos. Vamos a agregar la sucursal 5 a nuestra base de datos



Primero hemos seleccionado la celda B6; luego nos movemos pulsando la tecla TAB (esto no es necesario en la vida real, pero quería demostrar cómo navegamos la tabla con TAB). Al alcanzar la celda G6 y pulsar TAB, Excel nos lleva automáticamente a la celda B7 y expande la tabla. Vemos que esto es cierto para el formato de las celdas y también para las fórmulas en las celdas F7 y G7.
Como todavía no hemos introducido datos, el resultado de las fórmulas es DIV/0. Al introducir los datos, vemos el error desaparece. Lo mismo sucede si copiamos/pegamos datos



Otra característica importante es la posibilidad de agregar una fila de totales al final de la Tabla. Esta fila se adapta automáticamente a los cambios en las dimensiones de la tabla Para agregar la fila de totales en Excel 2007 vamos a la pestaña de Herramientas de tabla y marcamos la opción Fila de Totales.

En nuestro caso, Excel pone el total sólo en la última columna con datos numéricos


Podemos ver que Excel ha agregado la función SUBTOTALES con la opción 109. Es decir que si filtramos la tabla, el resultado mostrará sólo el total de las filas visibles.

Podemos cambiar esta función por otras pulsando la flecha en el borde izquierda de la celda



Para poner totales en las otras columnas con valores numéricos seleccionamos la última celda de la columna lo que hace aparecer la flecha de opciones de totales 





24 comentarios:

  1. Alberto,
    La verdad que es alucinante la manera tan clara de explicar estos conceptos y el dominio que tienes de los mismos.
    Si se pueden hacer sugerencias: alguna entrada sobre tablas dinámicas y/o importación de datos(web, query,.txt...); que ya sé que los has publicado pero para profundizar más.

    Saludos Jorge

    ResponderBorrar
  2. Alberto,
    efectivamente, ya he tocado todos esos temas en el blog. El próximo paso será publicar guías que estoy planeando ya hace un tiempo.
    ¿Hay algún aspecto en particular que te interese?

    ResponderBorrar
  3. Alberto,
    Pues la verdad que me interesaba lo concerniente a bases de datos (importación,...), así como lo relacionado con Tablas Dinámicas; de todas formas, cualquier tema que plantees será bienvenido por mi parte.

    Gracias de nuevo

    ResponderBorrar
  4. Enhorabuena. Tus explicaciones son muy claras. Podrías publicar algo sobre cubos OLAP?
    Fernando

    ResponderBorrar
  5. Hi, it's a very great blog.
    I could tell how much efforts you've taken on it.
    Keep doing!

    ResponderBorrar
  6. Hola Jorge,
    Por ponerle alguna pega, haría notar que un libro que haga uso de tablas no se puede compartir y, también, que sobre una tabla no se puede aplicar una herramienta tan util, al menos para mí, como los Subtotales.

    Gracias y un Saludo

    ResponderBorrar
  7. Chuck,
    todo tiene su precio en esta vida :)
    De todas maneras, se puede totalizar la tabla (o lista) en una tabla dinámica.

    ResponderBorrar
  8. Buenas, estupendo blog. La verdad es que estoy un poco "pez" en estos temas y viene muy bien la ayuda.

    A ver si me podéis echar un cable...Estoy haciendo una tabla con excel, en la que pretendo guardar datos del cliente, telefonos, mail...y...donde me lío es cuándo en el apartado artículos, quisiera poner varios para que cuándo haga un listado por productos, aparezcan reflejados en ese cliente...no se si me explico...Hasta ahora lo había solucionado, creando tantos clientes iguales, como artículos distintos le vendo a ese cliente...pero claro, llega un momento en el que tengo más clientes que artículos.

    Si me podeis echar una mano, os estaría muy agradecido...y si no, pues también!

    Gracias por vuestra ayuda.

    ResponderBorrar
  9. Hola,
    para consultas como la tuya hay que dirigirse a mi por mail.
    En la pestaña Ayuda (en la parte superior del blog) hay un enlace a una nota que explica cómo consultar.

    ResponderBorrar
  10. Hola Jorge.

    Felicitaciones por el blog, expresas mucho cariño por lo que haces y disciplina al sostenerlo.

    Abordarás también el Excel 2010?

    ResponderBorrar
  11. Gracias por los conceptos. Si, en el futuro estaré publicando notas sobre Excel 2010

    ResponderBorrar
  12. Saludos Jorge,
    Estoy intentando realizar una tabla de excel en la quisiera incluir listas desplegables en las que al señalar un elemento este me dirija aun submenu es posible realizarlo? por favor tu ayuda

    ResponderBorrar
  13. ¿Podrías explicar con más detalle que sería un submenú?

    ResponderBorrar
  14. Hola Jorge,

    Siguiendo el hilo de las guías (o de las notas) a mí me gustaría alguna profundización sobre las distintas posibilidades de publicar hojas (o parte de ellas) en formato web. Sé que una vez publicaste algo sobre el sitio Widgenie pero ahora me refiero a las propias herramientas de Excel.
    Un ejemplo de lo que te digo es agregar interactividad al guardar com html. No termino de darme cuenta si después es necesario tener instalado Excel para abrirlo en otro ordenador.
    Otro ejemplo: el formato xml. De forma sencilla: qué se puede hacer con esto?
    Lo último: microsoft sharepoint, para qué sirve?
    Gracias de antemano.
    Sergio

    ResponderBorrar
  15. Buenas muy bueno este blog,
    tengo unas situaciones que quiesiera resolver
    1 - Quiesiere hacer listas desplegables dependientes de lo que se seleccione en otra celda, pero lo quiero hacer para varias celdas, por ejemplo, en A1 tiene una lista desplegable y la lista de B1 va a salir valores según lo escogido en A1, en A2 tengo la misma lista desplegable de A1 y en B2 va a salir según lo que se escoja en A2 y así sucesivamente, pero el problema es que lo quiero hacer para toda una columna. Como se podría hacer esto ??

    ResponderBorrar
  16. COn la técnica que se muestra en esta nota. Con validación de datos creas unalista desplegable para celda donde deninimos la validación.

    ResponderBorrar
  17. Como simepre.....

    Sin palabras!!!!

    Queda todo dicho....

    Saludos

    ResponderBorrar
  18. Jorge, apreciable amigo, existe la posibilidad de que teneiendo listas en dos hojas diferentes, al capturar un nuevo dato en una de ellas aparezca automáticamente en la otra, es decir que una lista vaya creciendo conforme crece la otra?
    Gracias
    Saludos
    Alexx
    Los Mochis, Sinaloa, México

    ResponderBorrar
  19. En principio se puede, pero la pregunta es ¿por qué duplicar los valores? Si ya tienes una tabla de valores en el cuaderno, ¿para que la necesitas en otra hoja del cuaderno?

    ResponderBorrar
  20. Jorge. ¿Cómo puedo extender el formato tipo hora(H:M:S) con esta opción tipo tabla(2007-2010), lista(2003)?

    Saludos y gracias por tu material....

    ResponderBorrar
  21. Los formatos de las celdas de cada columna se extienden automáticamente (si todas las celdas de la columna tienen el mismo formato antes de agregar una nueva fila).

    ResponderBorrar
  22. Hola, gracias por todas las horas que has pasado explicando claramente como trabajar con excell.
    Necesito de tu ayuda. Creé una tabla y tiene fórmulas. En algún momento tuve que cambiar un par de fórmulas pero al agregar una fila, me copia la fórmula antigua y no la nueva, como lo puedo solucionar?

    ResponderBorrar
  23. Muy raro...Te sugiero que me envíes el cuaderno para que pueda hacerme una idea más cabal del problema (el mai aparece en el enlace Ayuda, en la parte superior del blog).

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.