En la entrada de ayer sobre tablas dinámicas vimos que cada vez que agregamos datos a la base de datos, debemos actualizar la referencia al rango. De no hacer esto, las nuevas líneas de datos no aparecerán en la tabla dinámica.
En el ejemplo de ayer hicimos esto manualmente. Excel nos permite construir referencias dinámicas, que se actualizan con los cambios en el tamaño de la base de datos. La técnica para hacer esto es utilizar "nombres" (NAMES).
Si no estás familiarizado con este tema, puedes ver mi nota sobre uso de nombres en Excel.
Como ya explicamos, los nombres pueden referirse no sólo a rangos sino también a fórmulas. Para crear el rango dinámico en nuestro caso definiremos un nombre que contendrá la formula DESREF (Offset en la versión inglesa). El archivo de este ejemplo se puede 
descargar aquí.
La sintaxis de esta fórmula es la siguiente; DESREF(referencia ;filas;columnas;alto;ancho) donde:
referencia: la celda en el ángulo superior derecho de la lista (en nuestro caso será A1);
filas: para nuestro uso será siempre 0
columnas: para nuestro uso será siempre 0
alto: la cantidad de filas en nuestra lista
ancho: la cantidad de columnas en nuestra lista.
Para convertir esta fórmula en dinámica, usaremos la función CONTARA para determinar los valores de los parámetros alto y ancho. Esta es nuestra fórmula:
=DESREF(Hoja1!$A$1,0,0,CONTARA(Hoja1!$A:$A),CONTARA(Hoja1!$1:$1))
como se puede ver comenzamos en la celda A1, contamos cuantos valores hay en la columna A (que representan la cantidad de líneas de la tabla) y también contamos la cantidad de valores en la línea 1 (que representan la cantidad de columnas en la lista).
Ahora sólo nos queda reemplazar la referencia al rango de datos en la lista por el nombre que acabamos de definir. Los pasos a seguir son:
1 – activamos la Hoja2 y ubicamos el mouse en alguna de las celdas de la tabla dinámica
2 – abrimos el menú Tabla Dinámica--->Asistente
3 – pulsamos el botón "atrás"; en el diálogo que se abre reemplzamos el rango
con el nombre (la forma más práctica es pulsar F3)
A partir de este momento, la referencia de la tabla dinámica se adaptará automáticamente de acuerdo a la cantidad de líneas que agreguemos, o que borremos, a la base de datos.
Categorías: Funciones&Formulas_, Manejo de Datos_
Technorati Tags: Tablas Dinamicas, Pivot Tables
17 comentarios:
Por favor, podría decirme, en lenguaje sencillo -no demasiado técnico-, qué son las "referencias dinámicas". He buscado en internet qué son pero sólo encuentro trucos para usarlas mejor. No hay nada que diga qué son y para qué sirven. Muchas gracias de antemano por su ayuda.
Hola, supongamos esta fórmula:
=SUMA(A1:A10)
Esta fórmula contiene una referencia al rango A1:A10. Esta referencia es estática. Nuestra fórmula siempre sumará los valores que aparezcan en el rango A1:A10.
Supongamos que queremos que esta referencia a adapte automáticamente cada vez que agregamos un valor en la columna A. Por ejemplo si agregamos un valor en A11, queremos que la referencia se convierta en SUMA(A1:A11).
Para lograr esto, una de las técnicas es la de usar la función DESREF contenida en un nombre, como explico en mi nota.
Espero haber ayudado
Hola.
¿Cómo se podría hacer referencia a ese nombre desde otro Excel distinto?
Sólo puedo cuando el nombre hace referencia a un rango estático.
Muchas gracias.
Hola,
supongo que te refieres a DESREF con una refrencia a otro libro Excel. Si el otro libro está cerrado DESREF te dará error.
HOLA,
VI QUE LA OPCION OFFSET TAMBIEN SIRVE PARA PASAR LOS DATOS DE UNA COLUMNA DE EXCEL A QUE AHORA SEAN UNA FILA, PERO NO ENCUENTRRO MUCHO LA SINTAXIS CORRECTA, ADEMAS ES VERDAD QUE SOLO SIRVE EN LA VERSION 2003?
Hola Esmeralda,
para transponer un rango tienes que usar la función TRANSPONER. La sintaxis es muy sencilla: =TRANSPONER(rango). Tienes que tomar en cuenta que esta es una función matricial y para entrarla debes presionar conjuntamente Ctrl+Mayúsculas+Enter.
La función DESREF crea referencias a una celda única o a un rango de celdas, como lo explico en esta nota sobre la función DESREF.
SENCILLAMENTE NOTABLE.
EXCELENTE, MUCHAS GRACIAS
Quisiera pedirte una ayuda ya que me estoy quemando el coco y no encuentro la solucion.
Tengo varias matrices en donde estan detalladas las ventas de los clientes por item de productos y con subtotales por cada categoria de productos.
Las tablas de cada cliente son iguales y estan una debajo de la otra.
Quiero hacer un resumen por cliente mostrando solo las ventas de los subtotales (no el detalle de cada producto).
Pienso que esto se solucion con la formula DESREF (ancla, nro de filas, nro de columnas).
La pregunta es como puedo hacer para que la celda ancla se modifique de acuerdo a cada cliente.
Supongamos que la matriz de cliente "X" tiene como ancla la celda A1, la del cliente "Y" deberia tener como ancla la celda A32, etc.
Yo necesito que al colocar el cliente X la formula sea DESREF (A1:5,6) y que cuando coloco el cliente Y la formula DESREF sea(A32;5;6).
Desde ya muchismas gracias.
Hola Lalo
dado que lo que cambia es la fila en la columna A, podrías pasar el argumento en forma dinámica usando COINCIDIR para ublicar en que fila se encuentra el cliente (supongamos la fila 32). Luego "armamos" la direccion del ancla usando INDIRECTO y DIRECCION y finalmente usamos DESREF. Sería algo así:
=DESREF(INDIRECTO(DIRECCION(COINCIDIR(nombre del cliente;rango de clientes;0);1));5;6)
Fantástica aportación. Una pregunta: cómo has obtenido un conocimiento tan profundo de excel?
Existe algun manual de referencia que nos puedas recomendar.
Gracias!. Enorme trabajo!
Felicidades.
Gracias por los conceptos.
No conozco ningun manual de referencia en castellano. En inglés hay varios, en especial los de John Walkenbach.
Tal vez más adelante me anime a escribir algo en castellano.
De todas maneras, la mejor forma de aprender tratando de resolver problemas reales y consultando las casi innumerables fuentes que hay en la Internet.
Hola Jorge:
Un día me resolviste un asunto y ahora de nuevo requiero de tus servicios, si fuera posible.
Estoy aplicando la función DESREF para establecer nombres con carácter dinámico y en general me funcionan, pero en este caso no.
Se trata de una hoja con una lista y quiero buscar valores a la izquierda, por lo que me baso en el uso de la función INDICE.
Tengo las columnas DNI, Nombre, Apellidos y Saldo. El campo de búsqueda lo elijo yo con un desplegable. Por ejemplo: elijo buscar por Saldo, a continuación elijo por desplegable qué campo quiero recuperar (por ejemplo Apellidos) y todo me funciona bien combinando la función INDICE con COINCIDIR con INDIRECTO. Hago algo así como =INDICE($A$3:$D$8;COINCIDIR(F4;INDIRECTO(F3);0);COINCIDIR(G3;$A$3:$D$3;0))
En INDICE tengo el rango total en el primer argumento, luego el primer COINCIDIR me averigua la fila y en F3 puedo tener rótulos distintos (que corresponden con los nombres creados para buscar ese dato). Por último en el segundo COINCIDIR es para averiguar qué dato quiero recuperar y sirve para elegir el rótulo y buscar en el rango correspondiente por su nombre.
Todo funciona perfectamente pero cuando los nombres los establezco dinámicamente con DESREF no me funcionan y estoy seguro que la fórmula está bien.
En fin, es un poco de lío y no sé si me expliqué (quizás sea mejor que te mandara el archivo).
Gracias de antemano,
Sergio
Hola
si, mejor me mandas el archivo
Jorge, antes que nada, gracias por el gran aporte que haces. Descubri la pagina ayer, y me animo a hacerte una pregunta. Cuando yo utilizo Datos -> validacion -> Listas con un nombre cuyo rango es dinamico, en la hoja de calculo figuran todos los posibles valores, pero me permite TAMBIEN ingresar un dato que no este en ella. Esto me pasa solo cuando tengo rangos dinamico.
Se te ocurre alguna solucion para que solo acepte estos valores y no otros.
Nuevamente muchas gracias!!!
Gabriela
Hola Gabriela
validación de datos no te permite ingresar manualmente datos que no figuren en la lista. En tu caso pueden suceder que:
1 - estás pegando los datos en lugar de ingresarlos manualmente (cuando hacés copiar-pegar el mecanismo de validación no funciona)
2 - el rango dinámico está definido de tal manera que inbcluye un valor vacío, lo que hace que cualquier valor que ingrese sea "legal".
Hola Jorge,
Me puedes confirmar que NO se puede usar la función INDIRECTO con rangos dinámicos.
Muchas gracias y un saludo
La funcipon INDIRECTO acepta como argumento un rango, CUando usas un rango dinámico (por lo general creado con la función DESREF), lo que estás usando como argumento es un fórmula, no un rango y por eso la función INDIRECTO no funciona.
Publicar un comentario en la entrada