sábado, marzo 29, 2014

Uso de Tablas en Excel - referencias estructuradas

El tema del uso de Tablas (Listas en Excel 2003) no es nuevo en este blog (un clic en la etiqueta Tablas/Listas en la nube de etiquetas les mostrará todas las notas). En mi opinión es una de las mejores innovaciones en Excel en los últimos años. Sin embargo no creo equivocarme si digo que relativamente pocos usuarios sacan provecho de esta herramienta.
Un posible motivo para esta reticencia sea la nueva sintaxis empleada para referenciar las celdas y rangos dentro de la tabla. Al principio esta sintaxis parece un tanto críptica para el usuario habituado a las referencias clásicas "columna/fila" (por ejemplo, A1).

Consideremos esta tabla de ventas por sucursal y zona

informe dinamico sencillo


En la celda G4 usamos esta fórmula para extraer de la tabla las ventas de cierta zona

=SUMAR.SI($C$3:$C$12,G3,$D$3:$D$12)

Ahora convirtamos esta tabla en  una Tabla (cuando uso tabla con minúscula me refiero a un rango normal de datos) y veamos cómo aparecen las referencias en la fórmula

Tabla de datos

La fórmula aparece ahora así

=SUMAR.SI(TablaVentas[Zona],G3,TablaVentas[Ventas])

La sintaxis es evidente:

"TablaVentas" se refiere al nombre de la tabla

Nombre de la tabla

[Zona] es la referencia al campo (columna) que define la zona de cada sucursal; de la misma manera [Ventas] se refiere a la columna de las ventas.

Para referirnos a otros elementos de la tabla, como la fila de los encabezamientos, usamos distintas formas que resumo en esta tabla

Objeto Sintaxis
Referencia a la tabla =TablaVentas
La celda de la fila en la columna =[@Ventas]
Fila de encabezados TablaVentas[#Encabezados]
Toda la tabla =SUMA(TablaVentas[#Todo])
Fila de totales =TablaVentas[[#Totales],[Ventas]]

Una ventaja evidente de esta sintaxis es la claridad. No hace falta ver el rango referenciado para saber qué calcula la fórmula.

Esto nos permite crear fórmulas con mucha facilidad, como podemos ver

escribir fórmula

Al comenzar a escribir la fórmula Excel nos muestra las tablas como un rango definido; pero la mejor parte viene ahora

definicion de formula

 Al poner el "["Excel nos muestra todos los rangos disponibles para nuestra fórmula. Así que si queremos calcular el total de ventas elegimos Ventas, ponemos el "]" para cerrar la definición del campo, cerramos el o los paréntesis necesarios y apretamos Enter. 

Para referirse a una celda dentro de la Tabla, Excel usa la notación [@nombre del campo]. Por ejemplo, si tenemos una columna de descuentos en nuestra tabla y queremos calcular las ventas neto

referencia a celda dentro de Tabla


Más sobre la sintaxis y el uso de referencias  estructuradas pueden leer en esta nota del sitio Office de Microsoft.

13 comentarios:

  1. A mi me ocurre que muchas veces me sale el comentario de que hay referencias circulares. A qué es debido? Creo que en mi tabla no utilizo ninguna fórmula porque la utilizo como base de datos para powerpivot

    ResponderBorrar
  2. Sergio, si tienes una referencia circular necesariamente hay alguna fórmula en algún lado. Para encontrar la celda que contiene la formula que genera la referencia circular puedes usar Fórmulas-Auditoría de fórmulas-Comprobación de errores-Referencias Circulares.

    ResponderBorrar
  3. Hola Jorge,
    en mi humilde opinión creo que Microsoft comunica muy mal el uso de "Tablas", ya de por sí la llamo solo "Tabla", dentro de la solapa Insertar ????? WTF!!!
    Si cualquiera con conocimientos muy básicos de excel puede hacer una tabla común ¿para que va a insertar una "Tabla"? Ese nombre no dice nada a primera vista, NO te motiva a usarlo, ni curiosidad te da saber que es. Tendrían que poner un nombre mas descriptivo y explicar mejor las bondades, que son muchas.
    Hace un tiempo aprendí que era esto de las "Tablas" gracias a este blog y no podia entender como nadie me lo había enseñado antes. El usuario medio no lo sabe, hasta utilizan macros para cosas que se pueden hacer con "Tablas".
    El año pasado me puse a hacer una pequeña aplicación en excel para control de inventarios utilizando "Tablas" (las que encontre eran muy básicas) para que cualquiera pueda bajarla, utilizarla libremente, aprender (como yo hice en su momento) y mejorarlas, esta es la URL (me gustaría saber tu opinión): http://xerbn.wordpress.com/2013/09/24/planilla-excel-gestion-control-inventario/

    Desde ya super agradecido a vos por enseñarme las bondades de las "Tablas" :)

    Saludos

    ResponderBorrar
  4. Muy buenas, tengo un problema con una hoja que estoy haciendo para sacar unos datos y que se reflejen en la tabla principal, el problema comienza cuando tengo distintos proveedores de información cada uno con su forma de extraerla, lo cual hago cada uno de forma distinta de las hojas del mismo libro.
    Había pensado en hacer un desplegable para elegir el proveedor de los datos y que se modificara la forma en la que se saca el dato en cuestión [ejemplo:=CONSULTAV(Tabla4[CODIGO];Tabla2;2;FALSO)
    lo he probado con "Tabla4[@CODIGO"]
    si yo ingreso en la celda de destino esto
    [=CONSULTAV([CAMBIO];Tabla2t;2;FALSO)]
    funciona correctamente dado que esta dentro de la misma tabla.
    Si lo preparo para que se ponga sola la formula según el proveedor de los datos me responde #¡VALOR!, si pongo la celda especifica en la tabla sin asignar valor absoluto en todas las filas me saca el mismo resultado.

    Alguna idea? me estoy volviendo loco, agradecería tu ayuda o algo de luz al respecto.

    Muchísimas gracias.

    Un saludo

    Ruben

    ResponderBorrar
  5. Ruben, tendrás que enviarme el archivo; me cuesta seguir tu explicación sin ver el modelo.

    ResponderBorrar
  6. Buenas Jorge,

    Como puedo hacerte llegar el modelo.

    Muchisimas gracias por contetar tan pronto.

    Ruben

    ResponderBorrar
  7. Fijate en el enlace Ayuda, en la parte superior del blog.

    ResponderBorrar
  8. Buenas Jorge,

    Te llego el documento que te envié? o lo envío de nuevo?

    Un saludo.

    ResponderBorrar
  9. No lo recibí. Por favor, mandalo de nuevo señalando el tema en la referencia.

    ResponderBorrar
  10. Hola Jorge, gracias por el artículo.

    Hay una cosa que m está volviendo loco.

    Dado una tabla

    Column1 Column2 Esigual?
    1 2 =SI([ [@Column1]=>[@Column2] ];"OK";"NO")

    ¿En qué me estoy equivocando?
    No consigo hacerla funcionar de ninguna manera.

    He probado con sistaxis diferentes,

    =SI(Table2[[Column1]=>[Column2]];"OK";"NO")
    Con un único corchete, con dos...

    No hay manera.

    ¿Puedes darme alguna pista?

    Gracias

    ResponderBorrar
  11. Hola Carlos,el problema es sencillo pero sutil. En lugar de => (igual o mayor que) Excel quiere que uses >= (mayor o igual que).

    ResponderBorrar
  12. Buenas tardes

    Cómo es la sintaxis de buscarv para tablas?
    Quiero buscar un dato correspondiente a una fecha en particular, pero me da error: =+BUSCARV($C$6;Produccion;Produccion[Granel ExS];VERDADERO) donde C6 es la fecha a buscar, Produccion es el nombre de la tabla y Granel Exs es el nombre de la columna.

    Desde ya muchas gracias por la atencion.

    Juan

    ResponderBorrar
  13. La sintáxis es la misma que con referencias "comunes". En tu fórmula hay errores. La fórmula debería ser

    =BUSCARV($C$6;Produccion[#Datos];x;VERDADERO), donde

    "x" es el número de columna de donde queremos traer el dato y Produccion[#Datos] significa que nos referemimos a toda la tabla sin los encabezamientos de las columnas.



    ResponderBorrar

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