13 de febrero de 2014
Rangos dinámicos
Básicamente un rango dinámico en Excel es un rango de datos que puede cambiar (aumentar o disminuir) según nuestras necesidades, y las referencias de dicho rango se actualizan automáticamente.
Lo primero que debemos saber es que usaremos la función DESREF la cual devuelve una referencia a un rango que es un número de filas y de columnas de una celda o rango de celdas;
Sintaxis: =DESREF(Ref;Filas;Columnas;Alto;Ancho) los campos opcionales son Alto y Ancho.
también usaremos la función CONTARA para contar el número de celdas que contienen datos en un rango o matriz.
Sintaxis: =CONTARA(Valor1)
Luego de esto, podemos iniciar formulando las referencias y con ello definimos un nombre para nuestro rango dinámico.
Para ello, en la barra de herramientas buscamos la opción Fórmulas y luego “Asignar un nombre al rango”.
En este recuadro le daremos el nombre a nuestro rango: Campo "NOMBRE", además le daremos el rango en el Campo "SE REFIERE A" de la siguiente manera: =DESREF($A$1;0;0;CONTARA($A:$A))
Con esto le estamos diciendo que tome toda la columna A, desde la fila 1, columna 1 y le asigne el Rango Dinámico.
En caso que deseáramos que el rango inicie en la fila 2. Es decir que inicie desde la A2, la fórmula sería =DESREF($A$1;1;0;CONTARA($A:$A))
Y si deseáramos que el rango incluya no solo la columna A sino la A y B la fórmula sería =DESREF($A$1;0;0;CONTARA($A:$A);2) en este caso el 2 al final quiere decir que tome 2 columnas a partir de la primera (A)
Pero si lo que necesitamos es otras columnas donde no esté la A ni la B sino la E y F que vaya de la fila 9 a la fila 99 la formula seria =DESREF($E$9;0;0; CONTARA($E1:$E$99 ); 2)
Si queremos un rango dinámico infinito hacia abajo y hacia la derecha, esto es que se puedan agregar tantas filas como columnas, la fórmula a introducir será: =DESREF($A$1;0;0;CONTARA($A:$A);CONTARA($1:$1))
Ahora por ejemplo podremos usar la función básica SUMA
Mi rango dinámico se llama "EjemploDinamico" y al usar la función SUMA quedaría así: =SUMA(EjemploDinamico)
Y si yo agregara mas valores hacia abajo en la A11, la función automáticamente me sumaría estos valores.
Si te gustó, síguenos en Twitter para recibir nuestras actualizaciones y trucos
Suscribirse a:
Comentarios de la entrada (Atom)
Cual es la diferencia en la formula cuando inicia en la casilla A1 y cuando inicia en la A2?..
ResponderBorrarHola, muchas gracias por escribirnos. En la función Desref el primer campo obligatorio es REF, la cual es la referencia a partir de la cual se desea basar, en otras palabras es como el encabezado de la columna, este no es tomado en la operación que se quiera realizar en excel. Con respecto a su pregunta, si REF=A1, la operación matemática iniciara desde A2 hacia abajo, y si REF=A2 la operación matemática iniciara desde A3 hacia abajo.
ResponderBorrarEspero que te haya sido útil la información.
Hola,
ResponderBorrarGracias por esta explicacion. Me gustaria preguntar como podria utilizar estos rangos dinamicos en formulas que no tengan en cuenta todas las celdas del rango al mismo tiempo sino que las vayan considerando una por una para cada fila, y que en caso de poner una fila mas incluya una fila también en la columna de la formula.
Es decir, por ejemplo, tengo una columna (A) con datos, de los cuales la cantidad de filas puede variar. Me gustaria poder en la columna B hacer la siguiente operacion: A1*A1 en la celda B1, A2*A2 en la celda B2, y asi sucesivamente para cada fila de la columna B, sabiendo que A a veces tiene 5 filas y otras puede tener 6,7 o mas. Me gustaria entonces que la segunda columna se ajuste a la cantidad de filas de la primera, igual que lo hace el grafico, añadiendo puntos cada vez que se añade un dato.
Gracias y un saludo
Hola: en la columna L tengo una serie de valores que se van agregando desde L6 hacia abajo.
ResponderBorrarNecesito:
1) Una formula que cuente los ceros
2) Una formula que cuente las celdas en blanco.
No logro armar la formula.
Muchas gracias de antemano.
Hola, tengo un inconveniente con el rango dinamico de mi TD. Resulta que tengo una tabla de 8 columnas y 984 filas. Necesito seguir introduciendo datos a la misma pero cuando coloco la funcion:
ResponderBorrar=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))
me cuenta solo hasta la fila 975, las demas las ignora. como puedo hacer para solucionar este problema? Gracias
Estimados:
ResponderBorrarse puede utilizar los rangos dinamicos para enlazarlas a una lista en la validacion de datos.
saludos.
Estimado.
ResponderBorrarMuy clara la explicación de la formula "desref".
Tengo una hoja con una matriz de 4 columnas y filas dinámicas, y otra hoja donde tengo una matriz que extrae el valor de una columna de la matriz de 4 columnas. Para esto estoy usando una formula matricial que combina indice y coincidir.
Definí los nombres de cada una de las 4 columnas de acuerdo a lo indicado en su blog (DESREF($A$1;0;0;CONTARA($A:$A)), pero al ejecutarse la formula me aparece un error de nombre, pero al usar la misma formula pero con los rangos en lugar de los nombres, me aparece el resultado buscado.
No sé si fui claro en la explicación, pero si fue así, tienen alguna idea de qué estoy haciendo mal?
Muchas gracias,
Manuel
Estimado.
ResponderBorrarMuy clara la explicación de la formula "desref".
Tengo una hoja con una matriz de 4 columnas y filas dinámicas, y otra hoja donde tengo una matriz que extrae el valor de una columna de la matriz de 4 columnas. Para esto estoy usando una formula matricial que combina indice y coincidir.
Definí los nombres de cada una de las 4 columnas de acuerdo a lo indicado en su blog (DESREF($A$1;0;0;CONTARA($A:$A)), pero al ejecutarse la formula me aparece un error de nombre, pero al usar la misma formula pero con los rangos en lugar de los nombres, me aparece el resultado buscado.
No sé si fui claro en la explicación, pero si fue así, tienen alguna idea de qué estoy haciendo mal?
Muchas gracias,
Manuel
Diego Alzate hola puedes ayudarme la formula me da error y quiero insertar rango dinamicos infinitos estoy usando excel 2013
ResponderBorrarcuento con una hoja de 1085 lineas y 16 colunmas, lo que quiero es que se me actualizen los datos automaticamente en la tabla dinamica