La función INDICE, todo (o casi todo) lo que hay que saber

lunes, marzo 17, 2014

En la nota Traducción automática en hoja de Excel propuse dar un premio (el Planificador de Tareas JLD) a los primeros tres lectores que respondieron acertádamente a la pregunta qué tiene de particular la forma la forma en que se usa la función INDICE en el ejemplo de la nota.
Si bien hubo tres ganadores, ninguna respuesta fue totalmente exacta, lo que me impulsó a escribir esta nota.

La función INDICE es una de las más importantes y versátiles funciones del arsenal de Excel (y hay quien la corona como la más importante endilgándole superlatívamente el adjetivo "imponente").

La función INDICE permite encontrar el valor de un elemento dentro de una rango. Este rango puede ser un "vector" (un rango unidimensional de una fila como A2:M2 o de una columna como  A2:A10), o una matriz (un rango "rectangular", por ejemplo A2:M10). Para encontrar el elemento buscado debemos incluir uno o dos índices dependiendo se si trata de un vector o una matriz.

En este ejemplo


INDICE crea una referencia a la celda A2, cuyo valor es 40.  En este otro ejemplo


usamos la misma sintaxis a pesar de que el rango es "horizontal". En este caso INDICE crea una referencia a la celda B1.

Podemos concluir que:
  • que cuando usamos vectores (rangos unidimensionales) INDICE interpreta el segundo argumento como fila o columna dependiendo de la orientación de vector




  • cuando se refiere a un rango, la función INDICE crea una referencia a una celda (o celdas) en el rango.
Cuando usamos INDICE para referirnos a rangos bi-dimensionales, usamos los argumentos de fila y columna para referirnos al elemento buscado en la matriz

En este ejemplo INDICE crea una referencia a la celda B3.

Hasta aquí posiblemente no haya aportado nada dramático a los conocimientos de mis lectores. Ahora los invito a ver esta situación:


Cuando cuando falta el parámetro de fila, o es 0,  INDICE se refiere a toda la columna (en nuestro caso la columna B ya que el parámetro de columna es 2).  Pueden comprobarlo con
34+44+50+26+79+51+57 = 341
De la misma manera, si falta el parámetro columna, INDICE se refiere a toda la fila

47+50+18 = 115
Podemos aprovechar esta propiedad de INDICE para crear rangos dinámicos. La ventaja de usar INDICE en lugar de DESREF o INDIRECTO es que estas dos últimas son volátiles mientras que INDICE no lo es.
Un rango dinámico con INDICE se ve así

=A1:INDICE(A:A,CONTARA(A:A))
donde A1 es la primer celda del rango. Esta fórmula supone que no hay celdas vacías entre A1 y la última celda con valor de la columna A. Para usar esta fórmula como rango dinámica creamos un nombre que se refiera a la fórmula, por ejemplo el nombre "rango_dinamico"

nombre definido

Ahora podemos ver que a medida que agregamos valores en la columna a, el resultado en la celda C1 se va actualizando

rango dinámico



8 comments:

ram6n 17 marzo, 2014 10:56  

Muy interesante. Gracias!

Anónimo,  18 marzo, 2014 17:54  

Muy buena publicación

Javier Alexander Pérez Caballero 04 abril, 2014 07:13  

Hola Jorge:

Como simpre excelente tu blog.

Tengo una pregunta, estoy calculando un resultado con sumproduct a partir de una tabla con filtros, estoy usando la funcion subtotal dentro de los parametros de la funcion desref para contar cuantas filas quedan despues de filtrar la tabla, sin embargo aun me hace falta calcular cuantas filas debe moverse hacia abajo a partir de la celda de referencia, (segundo argumento del desref), para realizar el calculo correctamente.
Me puedes ayudar?

Jorge Dunkelman 05 abril, 2014 09:44  

Javier, la consulta no está relacionada al tema de la nota. Te sugiero que me la dirijas por mail privado (fijate en el enlace Ayuda, en la parte superior del mail).

Anónimo,  25 julio, 2014 23:39  

Muy interesante tu aporte Jorge..sigue asi!

Anónimo,  06 septiembre, 2014 00:04  

Cordial saludo,

Antes que todo, agradecerte por compartir estas útiles herramientas y felicitarte por tanto conocimiento!

A qué te refieres exactamente cuándo dices que las funciones DESREF E INDIRECTO son volátiles mientras que INDICE no lo es?

Jorge Dunkelman 06 septiembre, 2014 21:27  

Excel tiene un "motor" de cálculo intelligente. Cuando introducimos un cambio en una hoja, Excel identifica que fórmulas serán afectadas por este cambio y sólo recalcula esas fórmulas. Sin embargo hay funciones que son recalculadas con cualquier cambio en la hoja, aún si no serán afectadas por este cambio. Esas funciones son "volátiles". La normas de buenas prácticas recomiendan evitar en lo posible el uso de estas funciones.
He tocaso el tema tangencialmente en esta nota

Anónimo,  08 septiembre, 2014 22:14  

Queda claro, gracias por tu tiempo.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP