Uso de Tablas en Excel - referencias estructuradas

sábado, marzo 29, 2014

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.

11 comments:

Sergio Castro 29 marzo, 2014 19:55  

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

Jorge Dunkelman 30 marzo, 2014 07:08  

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.

Juani 31 marzo, 2014 19:13  

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

Fabra Fabra 21 abril, 2014 23:56  

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

Jorge Dunkelman 22 abril, 2014 09:49  

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

Fabra Fabra 24 abril, 2014 02:41  

Buenas Jorge,

Como puedo hacerte llegar el modelo.

Muchisimas gracias por contetar tan pronto.

Ruben

Jorge Dunkelman 24 abril, 2014 07:55  

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

Fabra Fabra 01 mayo, 2014 21:45  

Buenas Jorge,

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

Un saludo.

Jorge Dunkelman 02 mayo, 2014 18:09  

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

Carlos,  31 mayo, 2016 12:45  

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

Jorge Dunkelman 31 mayo, 2016 17:24  

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

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP