Descubriendo las funciones de Excel 3 BUSCARH (HLOOKUP)

Retomando los artículos sobre funciones de búsquedas, explicaremos en el artículo de hoy una función que no es muy utilizada pero es muy fácil ya que es similar a otra función que es una de las de mayor uso en Excel. Por supuesto no referimos a BUSCARV.

La función BUSCARH (HLOOKUP para la versión en inglés) nos permite realizar búsquedas en un rango y una vez encontrado devolverá una celda que se encuentra en la misma columna pero a una cantidad determinada de líneas hacia abajo.

Dicha función toma cuatro parámetros. El primero es el valor buscado, el segundo es el rango donde se va a realizar la búsqueda, el tercero es el número de la fila dentro del rango que se utilizará para devolver el valor y el último parámetro indica si queremos que el valor buscado coincida exactamente con algún valor en el rango.

Si no hay coincidencias se devuelva el error #N/A. Si deseamos evitar este error podemos utilizar la función SI.ERROR como se explicó en una nota anterior.

Como pueden ver los parámetros y su funcionamiento son muy similares a la función BUSCARV y por lo tanto surge la pregunta: ¿Por qué tenemos dos funciones tan similares?

En muchos casos en Excel tenemos que trabajar con datos de planillas creadas por otras personas y por lo tanto no podemos cambiar los formatos sino que tenemos que adaptarnos a ellos. De acuerdo como estén distribuidos la función BUSCARH puede ser mas conveniente que BUSCARV.


Veamos algunos ejemplos.


Disponemos de una lista de ventas donde se muestran en las columnas las regiones y en las filas los meses.


Si deseamos buscar las ventas del mes de Mayo para la región Sur podemos utilizar la siguiente fórmula =BUSCARH("Sur";B1:E13;6;FALSO) y para buscar las ventas del mes de Agosto para la región Oeste podemos utilizar =BUSCARH(“Oeste”;B1:E13;9;FALSO).

Podran ver que el número de la fila sumando uno es igual al nro del mes. Esto es porque la primer fila contiene los encabezados. De esta forma podemos fácilmente obtener los distintos meses utilizando los valores.

Formato condicional para resaltar los elementos buscados

El formato condicional es una herramienta que nos permite aplicar el formato que deseamos a una celda de acuerdo a cierto criterio. Estos criterios pueden ir desde algunos simples como por ejemplo si el valor de la celda es mayor que un número determinado a algunos muy complejos donde una formula de Excel se evalúa como verdadero o falso para determinar si aplica el formato o no.

Para los criterios simples, Excel dispone de varias combinaciones que podemos utilizar seleccionando la opción de Formato Condicional de la sección Estilos en la ficha Principal.



Para ver más información sobre el uso de estos criterios simples del formato condicional pueden visitar este link: http://office.microsoft.com/es-es/excel/HP100739393082.aspx?pid=CH100648453082



Veamos el ejemplo


Utilizando el formato condicional resaltaremos los valores de una lista que se encuentren en otra lista. Con este ejemplo daremos formato a todos los valores que coincidan y nuestra lista de valores buscados pueden contener más de uno.

Disponemos de dos listas que contienen valores, la lista en la columna "A" tiene todos los datos y la lista en la columna "C" son aquellos que queremos resaltar.


Definimos un nombre llamado Valores para el rango en la columna "C" donde se encuentran los datos buscados. De esta forma es más flexible la cantidad de datos a buscar y más sencilla la formula.

Ahora debemos ingresar la formula que será evaluada para definir si se tiene que aplicar o no el formato condicional. Para ello activar la celda que contiene el primer dato de la lista (en este ejemplo la celda A2) y debemos seleccionar la opción de formato condiciona y elegir la última opción Nueva Regla.

En la pantalla de Nueva regla de formato seleccionamos Usar una fórmula para determinar que celdas formatear. En el cuadro de texto ingresamos la siguiente fórmula: =COINCIDIR(A2;Valores;0) > 0

La función COINCIDIR buscará el valor en la lista Valores y devolverá la posición una posición mayor a cero significa que fue encontrado.


Es muy importante no utilizar referencias absolutas ya que esto nos permitirá aplicar el formato condicional y la formula siempre evaluará el contenido de la celda a formatear y no utilizar siempre el contenido de la celda A2.

Al ingresar la fórmula Excel nos muestra la pantalla del Gestor de Reglas de Formato Condicional aquí podemos indicar en qué rango de celdas aplicamos el formato condicional modificando el rango en la columna Aplicar a.


Finalmente vemos que Excel ha resaltado las celdas cuyos valores se encuentran en la columna "C".

Acceder los elementos de una tabla mediante fórmulas

Si bien podemos considerar que todas las hojas de Excel son una tabla, en Excel 2007 se introdujo una nueva herramienta llamada "Tablas". Esta herramienta permite darle un nombre a un rango determinado de celdas y poder acceder a los elementos de la tabla mediante una referencias, y no el nombre de la celda donde está el dato, que pueden usarse en formulas.

Las principales ventajas es que las referencias son más fáciles de usar que los nombres de las celdas ya que pueden tener el nombre que el usuario desee. Funciona de forma similar a la definición de un nombre para un rango.

Se puede cambiar el tamaño de la tabla o mover la tabla y esto ajustara todas las referencias a la tabla sin tener que cambiar las formulas una por una.

Si alguna de las columnas de la tabla tiene una fórmula esta se copiará automáticamente al resto de las líneas.

Las referencias a una tabla se componen de la siguiente forma : =NombreTabla[NombreColumna]

También se pueden acceder distintas partes de la tabla utilizando la siguiente notación

• =NombreTabla[#Todas] Para acceder a la tabla completa.


• =NombreTabla[#Datos] El rango de todos los datos, sin encabezados ni totales.


• =NombreTabla[#Encabezados] Solamente incluye la primer línea de encabezados.


• =NombreTabla[#Totales] Solamente incluye la última línea.

Veamos algunos ejemplos.


Utilizaremos una tabla de datos como la siguiente


Si todavía los datos no fueron convertidos a una tabla se debe seleccionar la opción tabla de la ficha Insertar. Luego se debe definir el nombre tblVentas. El nombre se puede cambiar seleccionado la ficha Herramientas de tabla (solo aparece cuando la celda activa es parte de la tabla) y completar el campo nombre de tabla.


Utilizando fórmulas similares a las explicadas en la nota de sumas condicionales con mas de un criterio, utilizaremos referencias a las tablas para obtener los resultados.

Si deseamos obtener la cantidad de productos para un determinado país podemos usar la fórmula =SUMAR.SI(TblVentas[Pais];"Argentina";TblVentas[Cantidad]).

Para obtener la cantidad de un producto determinado y en un solo país podemos usar la fórmula =SUMAR.SI.CONJUNTO(TblVentas[Cantidad];TblVentas[Pais];"Argentina";TblVentas[Producto];"Manzana").

También podemos combinar otras fórmulas de Excel y por ejemplo obtener un promedio de unidades =PROMEDIO(TblVentas[Cantidad]) o el precio promedio de todas las ventas =TblVentas[[#Totals];[Importe]]/TblVentas[[#Totals];[Cantidad]].

Para acceder al archivo del ejemplo pueden hacer clic aquí.

Ranking con posiciones duplicadas usando la función JERARQUIA

En el artículo de hoy explicaré como utilizar la función JERARQUIA (o RANK para la versión en Inglés), que nos permite determinar la posición de un elemento dentro de la lista con valores numéricos. Cuando existen dos o más elementos que tienen el mismo valor la función les asigna el mismo. En algunos casos esto no es el comportamiento deseado por lo tanto veremos una variante para solucionarlo.

Primero explicaré como funciona la función JERARQUIA. Esta función toma tres parámetros, el primero indica la celda que contiene el valor a evaluar. Luego se indica el rango de celdas donde están todos los valores que forman parte del ranking. Finalmente se indica el orden 0 para ascendente, es decir el mayor valor tiene ranking 1, y 1 para descendente.


Veamos algunos ejemplos


Tenemos una lista de precipitaciones promedios como la siguiente :


En la columna C aplicamos la siguiente fórmula =JERARQUIA(B2;$B$2:$B$13;0) y obtenemos el ranking de mayor a menor.


En el puesto 9 tenemos dos meses, Agosto y Septiembre que tienen el mismo valor. De ahí saltamos al puesto 11 y no tenemos un mes en el puesto 10. Para solucionar este problema podemos utilizar una variante en la fórmula antes descripta: =JERARQUIA(B2;$B$2:$B$13;0)+CONTAR.SI($B$2:B2;B2)-1


La segunda parte de la formula cuenta cuantas veces aparece el valor de la fila que estamos calculando el ranking desde el principio de la tabla hasta la fila actual. Por lo tanto si el valor esta repetido el que figure mas abajo tendrá un ranking mayor. Es muy importante poner la referencia "$B$2:B2" fijando el comienzo del rango para que siempre empiece desde el principio de la tabla pero si copiamos la fórmula hacia abajo se incrementa el rango.

Formulas con rangos dinámicos usando la función INDIRECTO

Muchas veces necesitamos realizar una misma operación pero sobre rangos de celdas diferentes y no deseamos estar cambiando la fórmula para ajustar las celdas sobre las cuales se realiza la operación.

Para lograr esto podemos utilizar la función INDIRECTO (INDIRECT para la versión en Inglés) que toma una valor de texto, como el ingresado por un usuario en una celda y lo devuelve como una referencia a una celda, tal cual como se utiliza cuando es ingresado en una formula.

La función INDIRECTO toma dos parámetros, el primero es el nombre de la celda o nombre de rango al cual se desea hacer referencia. El segundo indica el formato de la referencia, si es VERDADERO (valor por defecto) la referencia será del tipo "A1" si es FALSO será "R1C1".

Si en la celda B2 ingresamos =INDIRECTO("C3") o =INDIRECTO("R3C3",Falso) obtendremos el valor de la celda como vemos en la siguiente pantalla.


Una alternativa es definir nombres de rangos y luego cambiar las celdas a las que se refiere ese rango, esta alternativa brinda la ventaja de hacer este proceso directamente en la planilla de Excel sin tener que entrar a definir nuevos nombres.


Veamos un ejemplo.


En cada hoja tenemos un listado de ventas que representan las ventas de todo el mes. Todas las hojas tienen el mismo formato pero pueden variar en cantidad de líneas.


En cada hoja definimos nombres para los rangos para facilitar las formulas y que nos permita trabajar con distinta cantidad de datos por mes. La rango en columna B tiene el nombre Clientes.

Es importante definir los nombres de los rangos locales a la hoja, de esta forma podemos utilizar el mismo nombre de rango en varias hojas. Realizamos este proceso para todas las hojas con datos.


De manera similar definimos el nombre Productos para el rango en la columna C y Cantidades para el rango en la columna D.

En la hoja resumen tomamos información de las hojas presentando un estado de situación. El usuario puede seleccionar el mes a evaluar con solo seleccionar de la lista desplegable ubicada en la celda B2


Por ejemplo si deseamos calcular el total de productos vendidos usamos la fórmula: =SUMA(INDIRECTO(B2&"!Cantidades")), al cambiar el valor de B2 la función INDIRECTO hará referencia a distintos rangos.

Para acceder al archivo del ejemplo pueden hacer clic aquí.