Rango Dinámico en Microsoft Excel
Para descargar el archivo de ejemplo, pinche en Rha-Olympics-RangoDinamico
En muchas ocasiones resulta provechoso el empleo de Rangos Dinámicos, en especial para el caso de funciones de Excel que emplean rangos como parámetros.
Definamos Rango Dinámico como aquél Rango de datos que abarca un determinado número de filas y de columnas en una determinada Hoja pero que, por la naturaleza de la información que almacena, puede variar de un momento a otro. Esto es, puede cambiar el número de filas y/o el número de columnas asignadas a ese rango en cada momento.
Pongamos, por ejemplo, el rango de datos sobre los Juegos Olímpicos que se muestra a continuación:
| Olympics from Wikipedia | ||||||
| No. | Nation | Games | Gold | Silver | Bronze | Total |
| 1 | United States (USA) | 26 | 976 | 757 | 666 | 2.399 |
| 2 | Soviet Union (URS) | 9 | 395 | 319 | 296 | 1.010 |
| 3 | Great Britain (GBR) | 27 | 236 | 272 | 272 | 780 |
| 4 | France (FRA) | 27 | 202 | 223 | 246 | 671 |
| 5 | China (CHN) | 9 | 201 | 145 | 127 | 473 |
| 6 | Italy (ITA) | 26 | 198 | 166 | 185 | 549 |
| 7 | Germany (GER) | 15 | 174 | 182 | 217 | 573 |
| 8 | Hungary (HUN) | 25 | 167 | 144 | 165 | 476 |
| 9 | East Germany (GDR) | 5 | 153 | 129 | 127 | 409 |
| 10 | Sweden (SWE) | 26 | 143 | 164 | 176 | 483 |
| 11 | Australia (AUS) | 27 | 138 | 153 | 180 | 471 |
| 12 | Russia (RUS) | 5 | 133 | 121 | 142 | 396 |
Rango de datos está almacenado en la Hoja de nombre Resultados
Para sumar los datos almacenados en la columna D entre las filas 4 y 15, podemos emplear la fórmula siguiente:
=SUMA(D4:D15), suma las celdas de la columna D comprendidas entre las filas 4 y 15.
Una alternativa sería el uso de la fórmula:
=SUMA(D:D), en cuyo caso se sumarían todas las filas de la columna D
Imaginemos que hemos utilizado la fórmula =SUMA(D4:D15)para calcular la suma de medallas de oro de todos los países en todos los Juegos.
Cuando concluyan los Juegos de Río, que ahora mismo se celebran, nuestro rango de datos deberá ampliarse en una fila más, donde se muestren los resultados de estos Juegos. Así, por tanto, nuestro rango de datos ha variado a D4:D16, y sin embargo la fórmula que empleamos para calcular la suma de las medallas de oro fue =SUMA(D4:D15),por lo que tenemos dos opciones:
- o tenemos que modificar constantemente aquellas fórmulas en las que hemos definido rangos estáticos;
- o escribimos la fórmula de manera tal que el rango D4:D15 no sea estático, sino que, dinámicamente, se ajuste a la última fila de los datos.
Veamos la utilización práctica de lo descrito anteriormente.
| Rango Dinámico | |||
| A | B | Fórmula asociada a la columna B | |
| Fila inicial | 4 | =COINCIDIR(«No.»;Resultados!A:A;0)+1 (1) | |
| Fila final | 15 | =COINCIDIR(0;Resultados!A:A;-1) (2) | |
| Rango en texto | Resultados!A4:A15 | =»Resultados!» & «A» & B3 & «:A» & B4 (3) | |
| Aplicar Función sobre Rango Dinámico | 78 | =SUMA(INDIRECTO(B5)) (4) |
- La fila inicial del rango de datos la obtenemos aplicando la función (1)
=COINCIDIR(«No.»;Resultados!A:A;0)+1
Se busca la cadena de caracteres “No. “ en la columna A de la Hoja de nombre Resultados y se obtiene la posición que ésta tiene dentro del rango A:A. A esa posición se le suma 1, para obtener la primera fila de nuestro rango de datos.
- Para buscar la fila final del rango de datos, aplicamos la fórmula (2):
=COINCIDIR(0;Resultados!A:A;-1)
La fórmula nos dará la posición de la última celda en la columna A que contiene un valor de tipo numérico (por eso empleamos en el primer parámetro el valor 0). Resultado 15
Nota: Para obtener la posición de la última celda en la columna A que contiene un valor de tipo texto, emplearíamos en lugar del 0, la combinación “*”.
=COINCIDIR(«*»;Resultados!A:A;-1)
- Para conformar la cadena de caracteres que identifican al rango que queremos obtener utilizamos la fórmula (3):
=»Resultados!» & «A» & B3 & «:A» & B4
que no es más que la concatenación de cadenas de caracteres fijas con contenidos de celdas. Obtenemos como resultado, en la celda B5, la cadena de caracteres:
Resultados!A4:A15
- La cadena de caracteres obtenida con anterioridad, representa, en forma de texto, el rango dinámico que hemos construido para su aplicación posterior en una determinada función. Así, en nuestro ejemplo, aplicamos ese rango dinámico sobre la función (4):
=SUMA(INDIRECTO(B5))
que nos dará como resultado la suma del rango A4:A15 de la hoja de nombre Resultados.
Conclusión
El método que hemos explicado en este artículo, podemos aplicarlo para cualquier circunstancia en que un rango no tiene un tamaño fijo, sino que su cantidad de filas puede variar.
Esta solución nos permite no tener que estar modificando fórmulas cada vez que un determinado rango camba de tamaño. Basta con trabajar con este concepto de rango dinámico para que nuestra fórmulas se creen una vez y sean aplicables siempre, independientemente de las variaciones que pueda sufrir el tamaño del rango.
Sintaxis y ejemplos de uso de las funciones utilizadas en este artículo
Función COINCIDIR
Se aplica a: Excel 2016 , Excel 2013 , Excel 2010 , Excel 2007 , Excel 2016 para Mac
Descripción
La función COINCIDIR busca un elemento determinado en un intervalo de celdas y después devuelve la posición relativa de dicho elemento en el rango. Por ejemplo, si el rango A1:A3 contiene los valores 5, 25 y 38, la fórmula =COINCIDIR(25,A1:A3,0)devuelve el número 2, porque 25 es el segundo elemento del rango.
Sintaxis
COINCIDIR(valor_buscado,matriz_buscada, [tipo_de_coincidencia])
La sintaxis de la función COINCIDIR tiene los siguientes argumentos:
- Valor_buscado Es el valor que desea buscar en matriz_buscada. Por ejemplo, cuando busca un número en la guía telefónica, usa el nombre de la persona como valor de búsqueda, pero el valor que desea es el número de teléfono.
El argumento de valor_buscadopuede ser un valor (número, texto o valor lógico) o una referencia de celda a un número, texto o valor lógico. - Matriz_buscada Es el rango de celdas en que se realiza la búsqueda.
- Tipo_de_coincidencia Puede ser el número -1, 0 o 1. El argumento tipo_de_coincidencia especifica cómo Excel hace coincidir el valor_buscadocon los valores de matriz_buscada. El valor predeterminado de este argumento es 1.La siguiente tabla describe la manera en que la función encuentra valores basados en la configuración del argumento tipo_de_coincidencia.
| Tipo_de_coincidencia | Comportamiento |
| 1 u omitido | COINCIDIR encuentra el mayor valor que es menor o igual que el valor_buscado. Los valores del argumento matriz_buscada se deben colocar en orden ascendente, por ejemplo: …-2, -1, 0, 1, 2, …, A-Z, FALSO, VERDADERO. |
| 0 | COINCIDIR encuentra el primer valor que es exactamente igual que el valor_buscado. Los valores del argumento matriz_buscada pueden estar en cualquier orden. |
| -1 | COINCIDIR encuentra el menor valor que es mayor o igual que el valor_buscado. Debe colocar los valores del argumento matriz_buscada en orden descendente, por ejemplo: VERDADERO, FALSO, Z-A, …2, 1, 0, -1, -2, …, etc. |
- COINCIDIRdevuelve la posición del valor coincidente dentro de matriz_buscada, no el valor en sí. Por ejemplo, COINCIDIR(«b»,{«a»,»b»,»c»},0) devuelve 2, la posición relativa de «b» dentro de la matriz {«a»,»b»,»c»}.
- COINCIDIRno distingue entre mayúsculas y minúsculas cuando busca valores de texto.
- Si COINCIDIRno puede encontrar una coincidencia, devuelve el valor de error #N/A.
- Si tipo_de_coincidenciaes 0 y valor_buscado es una cadena de texto, puede usar los caracteres comodín de signo de interrogación (?) y asterisco (*) en el argumento valor_buscado. Un signo de interrogación coincide con cualquier carácter individual; un asterisco coincide con cualquier secuencia de caracteres. Si desea buscar un signo de interrogación o un asterisco real, escriba una tilde (~) antes del carácter.
Ejemplo
| Producto | Recuento | |
| Plátanos | 25 | |
| Naranjas | 38 | |
| Manzanas | 40 | |
| Peras | 41 | |
| Fórmula | Descripción | Resultado |
| =COINCIDIR(39;B2:B5;1) | Como no hay ninguna coincidencia exacta, se devuelve la posición del siguiente valor inferior (38) dentro del rango B2:B5. | 2 |
| =COINCIDIR(41;B2:B5;0) | La posición del valor 41 en el rango B2:B5. | 4 |
| =COINCIDIR(40;B2:B5;-1) | Devuelve un error porque los valores del rango B2:B5 no están en orden descendente. | #N/A |
_______________________________________________________________________________________________________________________________________________________________________________________
Función INDIRECTO
Se aplica a: Excel 2016 , Excel 2013 , Excel 2010 , Excel 2007 , Excel 2016 para Mac
Descripción
Devuelve la referencia especificada por una cadena de texto. Las referencias se evalúan de inmediato para presentar su contenido. Use INDIRECTO cuando desee cambiar la referencia a una celda en una fórmula sin cambiar la propia fórmula.
Sintaxis
INDIRECTO(ref; [a1])
La sintaxis de la función INDIRECTO tiene los siguientes argumentos:
- Ref Una referencia a una celda que contiene una referencia de tipo A1 o F1C1, un nombre definido como referencia o una referencia a una celda como cadena de texto. Si ref no es una referencia de celda válida, INDIRECTO devuelve el valor de error #¡REF!.
- Si ref hace referencia a otro libro (una referencia externa), el otro libro debe estar abierto. Si el libro de origen no está abierto, INDIRECTO devolverá el valor de error #¡REF!.Nota Las referencias externas no son compatibles con Excel Web App.
- Si ref hace referencia a un rango de celdas fuera del límite de filas de 1.048.576 o del límite de columnas de 16.384 (XFD), INDIRECTO devolverá el error #¡REF!.Nota Este comportamiento es diferente al de otras versiones de Excel anteriores a Microsoft Office Excel 2007, que ignoran el límite superado y devuelven un valor.
- A1 Un valor lógico que especifica el tipo de referencia que contiene la celda ref.
- Si a1 es VERDADERO o se omite, ref se interpreta como una referencia estilo A1.
- Si a1 es FALSO o se omite, ref se interpreta como una referencia estilo F1C1.
Ejemplo
| Datos | ||
| B2 | 1,333 | |
| B3 | 45 | |
| Jorge | 10 | |
| 5 | 62 | |
| Fórmula | Descripción | Resultado |
| ‘=INDIRECTO(A2) | Valor de la referencia en la celda A2. La referencia es a la celda B2, que contiene el valor 1,333. | 1,333 |
| ‘=INDIRECTO(A3) | Valor de la referencia en la celda A3. La referencia es a la celda B3, que contiene el valor 45. | 45 |
| ‘=INDIRECTO(A4) | Puesto que la celda B4 tiene el nombre definido «George,» la referencia al nombre definido es para la celda B4, que contiene el valor 10. | 10 |
| ‘=INDIRECTO(«B»&A5) | Combina «B» con el valor de A5, que es 5. Esto, a su vez, se refiere a la celda B5, que contiene el valor 62. | 62 |