Funciones Búsqueda y Referencia Archives

Función DESREF en Excel

Escrito por Iván Antona en 26 julio, 2018. Publicado en Excel Avanzado, Funciones Búsqueda y Referencia.

La función DESREF en Excel sirve para devolvernos una referencia o un rango a partir de una celda o un rango cualquiera de Excel. Además podemos indicar el número de filas o columnas del rango devuelto.

Argumentos de la función DESREF en Excel.

La función DESREF en Excel admite cinco argumentos de los cuales tres de ellos son obligatorios y los dos últimos opcionales.

Referencia: Es la posición a partir de la que nos desplazamos para recuperar el rango deseado.
Filas: A partir de “la referencia” establecida como argumento anterior nos desplazaremos tantas filas como aquí definamos.
Columnas: A partir de “la referencia” establecida como argumento anterior nos desplazaremos tantas columnas como aquí definamos.
Alto: Establece el número de filas que tendrá el rango a recuperar.
Ancho: Establece el número de columnas que tendrá el rango a recuperar.

Ejemplo de uso de la función DESREF en Excel.

La función DESREF en Excel es muy interesante para devolver rangos a partir de una determinada posición. Así por ejemplo podremos devolver un valor concreto de una rango indicando un desplazamiento de filas y columnas desde una posición concreta.

En el ejemplo nos posicionamos en la celda A4. A partir de esa celda nos desplazamos una fila y una columna hasta llegar a la celda B5. Finalmente indicamos que queremos recuperar una matriz de una fila por una columna que se concreta en una celda la celda B5 cuyo valor es 5.

Sin embargo la función DESREF en Excel cobra especial interés cuando trabaja con otras funciones. Por ejemplo con la función SUMA.

Sabemos que en una celda solamente podemos devolver una valor. Así que apartir de los mismos datos utilizados en el ejemplo anterior vamos a ver como sumar la matrir completa a partir de la posición A4.

Nos hemos posicionado en A4 y hemos definido un desplazamiento de 0 filas y de 0 columnas. Sin embargo le decimos que queremos que nos devuelva un rango de 3 filas y 3 columnas, precisamente, la información que deseamos sumar.

Esta matriz resultante es el argumento de entrada de la función suma. Se suman los 9 datos para conseguir el resultado final 45.

Estos ejemplos son didácticos para poder comprender como actúa la función pero poco útiles en el entorno de la oficina.

No obstante existe un concepto “la búsqueda dinámica” que nos permite buscar un determinado valor dentro de un listado. Este listado puede ampliarse y reducirse y nuestra búsqueda dinámica seguirá funcionando. Para lograr este comportamiento es preciso combinar tres funciones muy poderosas de Excel. La función DESREF, la función BUSCARV y la función CONTARA o alguna de sus variantes. A continuación presentamos un ejemplo de búsqueda dinámica.

Ejemplo de uso de la función DESREF en Excel dentro de la función BUSCARV búsquedas dinámicas.

Partimos de un ejemplo muy sencillo. Un listado donde controlamos el estado de stock de una frutería. La frutería cuenta en su almacén con cajas adicionales de diferentes frutas y verduras. Por ejemplo, 1 caja de tomates, 2 cajas de lechugas…

Vamos a configurar en la celda D2 una búsqueda de por ejemplo “Plátanos”. Entonces lo definimos como BUSCARV(D2; $A$1:$B$6; 2; FALSO)

La problemática que se plantea es la siguiente. ¿Qué ocurre si entra un nuevo producto en el almacén? Por ejemplo “Cerezas”. Ampliamos el listado de stock de almacén. Incrementamos las 10 cajas. Pero la búsqueda que habíamos parametrizado no funciona. Tenemos como resultado un #N/A. ¿Por qué? Bueno si nos fijamos en nuestra matriz de búsqueda el rango A1:B6 ya no es válido ahora el rango correcto será A1:B7.

Pero también podría ocurrir que las ventas fuesen muy elevadas y hubiésemos vendido parcialmente parte de los elementos del almacén y entonces la matriz de búsqueda podría ser A1:B4

Entonces ¿Cómo podemos hacer que el rango de la matriz de búsqueda se ajuste al número de elementos de la matriz de búsqueda? Lo vamos a conseguir gracias a la combinación de tres funciones. BUSCARV, DESREF y CONTARA. Pero veámoslo paso a paso.

Partimos de la función de BUSQUEDA y el primer argumento será el producto del cuál queremos consultar el estado del STOCK.

BUSCARV( “Sandía”…

El segundo argumento de nuestra función BUSCARV es un rango o matriz de datos. Sabemos que la función DESREF es capaz de devolver un rango o matriz así haremos uso de ella en este punto.

BUSCARV(“Sandía”; DESREF(A1;0;0;

Nos hemos posicionado en la primera celda del rango y no nos hemos desplazado ni filas ni columnas puesto que nos interesa precisamente el rango que comienza en A1:B?

Seguimos dentro de la función DESREF y tenemos que definir en los dos argumentos que faltan el número de filas y número de columnas que queremos recuperar. Entonces utilizaremos la tercera función la función CONTARA para averiguar cuantos datos hay en la columna A que contiene los productos.

BUSCARV(“Sandía”; DESREF(A1;0;0;CONTARA(A:A);2)

Además decimos el número de columnas que queremos recuperar que para este ejemplo son 2 y así lo hemos indicado.

Ahora solo nos resta volver a nuestra función de búsqueda y rellenar los argumentos restantes. El indicar de columnas IC y tipo de coincidencia “Exacta”.

BUSCARV(“Sandía”; DESREF(A1;0;0;CONTARA(A:A);2);2;FALSO)

De esta manera si añadimos un nuevo producto la búsqueda seguirá funcionando correctamente.

Función DESREF de Excel en Inglés.

La función en inglés se puede escribir como

=OFSSET(reference; rows; cols;[height]; [width])

Para qué sirve la función COINCIDIR en Excel.

Escrito por Iván Antona en 16 julio, 2018. Publicado en Excel Intermedio, Funciones, Funciones Búsqueda y Referencia.

Para qué sirve la función COINCIDIR en Excel.

La función COINCIDIR en Excel nos busca un valor dentro de una lista y si lo localiza nos dice en qué posición concreta dentro de la lista se encuentra. Así por ejemplo si contamos con la lista {Pedro, Juan, Rosa, Marcos, Manuel, Aurora} y aplicamos la función sobre dicho listado indicando como valor buscado “Rosa” entonces el resultado de la función será “3” puesto que Rosa ocupa la posición “3” en la lista. Esta función nos devuelve un valor de tipo entero.

Argumentos de la función COINCIDIR.

La función COINCIDIR en Excel admite tres argumentos de los cuales dos de ellos son obligatorios y el tercero opcional.

• Valor_buscado: Es el valor que deseamos localizar dentro de la lista

• Matriz_Buscada: Se trata de una lista de una dimensión donde localizaremos el dato.

• [Tipo_de_coincidencia]: Es un valor numérico, 1(Menor que),0(Coincidencia Exacta), -1(Mayor que).

Ejemplo de uso de la función COINCIDIR en Excel.

La función coincidir por sí sola no tiene un interés específico. Sin embargo combinada con otras funciones tiene un gran potencial. Vamos a ver un primer ejemplo meramente académico. Contamos con una matriz de datos, en concreto queremos averiguar qué posición ocupa el mes de Junio dentro del “vector de etiquetas” de la matriz.

El resultado de la función es 3. La primera posición del vector se corresponde con “C2” la segunda posición para “Mayo” y finalmente posición 3 para “Junio”.

Ejemplo de uso de la función COINCIDIR dentro de la función BUSCARV.

Es habitual combinar la función COINCIDIR con otras funciones para aprovechar su potencial. Por ejemplo con la función BUSCARV podemos controlar el argumento “indicador de columnas” mediante esta función.

Partimos de una idea muy sencilla, la función COINCIDIR en Excel, devuelve un valor entero. El indicador de columnas es precisamente un valor entero que se refiere a la columna en la que reside el dato que queremos recuperar con BUSCARV.

Aprovechando el potencial de COINCIDIR podemos averiguar en qué posición se encuentra “Mayo” dentro del rango G2:K6 que son precisamente las etiquetas de meses de la “matriz de búsqueda”. Al localizar el valor en la posición 2 entonces la función COINCIDIR en Excel da como salida precisamente ese dato que entonces se convierte en argumento de entrada de BUSCARV.

Si quisiéramos extender la función para los meses de Junio, Julio y Agosto, tendremos que controlar las referencias relativas de las celdas implicadas en la operación.

Función COINCIDIR en Inglés.

La función en inglés se puede escribir como

=MATCH(array; row_num;[num_Colum])

Función INDICE en Excel.

Escrito por Iván Antona en 13 julio, 2018. Publicado en Excel Intermedio, Funciones, Funciones Búsqueda y Referencia.

Descripción de la función INDICE en Excel

La función INDICE en Excel permite dado un listado de datos y una posición dentro del listado recuperar el valor que ocupa esa posición. Así por ejemplo si contamos con la lista {Pedro, Juan, Rosa, Marcos, Manuel, Aurora} y aplicamos la función sobre dicho listado indicando como posición “3” la función nos devolverá “Rosa” puesto que “Rosa” ocupa la tercera posición en la lista. En definitiva la función INDIRECTO nos devuelve el contenido de una celda que hemos referenciado. Este contenido puede ser un texto, un número, etc…

Argumentos de la función.

La función índice en Excel admite tres argumentos cuando se trabaja en modo referencia de los cuales dos de ellos son obligatorios y el tercero opcional.

Matriz: Es la lista de datos
Número de fila: Es el número de la fila donde reside el dato que queremos recuperar.
[Número de columna]: Es el número de la columna donde reside el dato. Este argumento no es obligatorio si la lista es unidimensional.

La función índice en Excel admite cuatro argumentos cuando se trabaja en modo matricial

Referencia: Listados que se utilizarán en la función, cada listado se separa del anterior mediante “;”
Número de fila: Es el número de la fila donde reside el dato que queremos recuperar.
[Número de columna]: Es el número de la columna donde reside el dato. Este argumento no es obligatorio si la lista es unidimensional.
[Núm_área]: Se refiere a algún listado de los definidos en el primer argumento.

Ejemplo de uso de la función INDICE en Excel. Con una lista de una dimensión.

Decimos que una lista es de una dimensión cuando los datos se presentan en una única fila o en una única columna. Así por ejemplo las siguientes listas son de una dimensión.

Mediante la función INDICE en Excel podremos recuperar cualquier dato de la lista conociendo previamente que dato en concreto queremos recuperar. Así por ejemplo del primer listado si queremos recuperar “Ciudad lineal” Introducimos como argumentos de la función el listado B4:B16 y precisamente la posición de “Ciudad Lineal” en la lista que es 10.

De igual manera podremos recuperar valores de una lista que está configurada horizontalmente así por ejemplo si queremos recuperar el mes “Febrero” de la segunda lista tendremos que seleccionar el listado completo E4:H4 y la posición concreta en la lista 3.

Nótese que al tratarse de un listado configurado horizontalmente también podremos definir la función como:

=INDICE(E4:H4;3)

=INDICE(E4:H4;1;3)

=INDICE(E4:H4;;3)

Siendo las dos última la forma más lógica de plantear la función. Tengamos en cuenta que Excel no deja de ser “una matriz”. Lo veremos mucho más claro al trabajar con listas o matrices de más de una dimensión.

Ejemplo de uso de la función INDICE en Excel. Con una lista de varias dimensiones.

Cuando nuestro listado contempla más de una fila y más de una columna tendremos que definir la coordenada exacta del valor que deseamos recuperar. La forma que tenemos en Excel de referenciar una celda de forma unívoca es mediante la columna y la fila. Entonces para recuperar un dato de un listado mediante la función indirecto será preciso indicar la fila y columna donde se ubica el dato.

Así para recuperar el valor “11” tendremos que indicar que se encuentra en la fila 3 y la columna 3.

Ejemplo de uso de la función INDICE en Excel. Con varias listas de varias dimensiones. Formato matricial.

Para utilizar este formato de función precisamos de varios listados que normalmente estarán configurados de forma similar. Por ejemplo si tratamos las ventas de un conjuntos de vendedores en varios cuatrimestres tendremos una hoja con el siguiente modelo.

La función INDICE en formato matricial nos permite ver por ejemplo las ventas para “Marta” en cualquier cuatrimestre para el tercer mes del cuatrimestre.

Nótese que la función en su primer argumento precisa recibir los rangos en el orden que después controlaremos con el 4º argumento.

Así el primer cuatrimestre se corresponde con el valor 1, 2 para el segundo y 3 para el tercero. Internamente Excel enlaza el rango A2:E6 con el valor 1 el rango G2:K6 con el valor 2 y el rango M2:Q6 con el valor 3.

Así para ver las ventas de Marta en el segundo cuatrimestre para el mes de Julio la función será:

Función INDICE en Inglés.

La función en inglés se puede escribir como

=Index(array; row_num;[num_Colum])

=Index(ref,núm_fila;[núm_columna];[núm_área])

La función buscarh nos permite localizar un valor dentro de la primera fila de una lista y devolver cualquier elemento que se encuentre en la misma columna.

Función Buscarh en Excel.

Escrito por Iván Antona en 23 mayo, 2016. Publicado en Funciones, Funciones Búsqueda y Referencia.

Función BuscarH en Excel, para qué sirve.

La función buscarh nos permite localizar un valor dentro de la primera fila o “vector” de una lista o base de datos y devolver cualquier elemento que se encuentre en la misma columna pero indicando la fila. Esta función pertenece a la familia de funciones de búsqueda y referencia. Es hermana de la función buscarv ya que su funcionamiento es idéntico al de ella pero en vez de realizar la búsqueda en una columna realiza la búsqueda en una fila. De ahí es de donde viene el nombre de búsqueda horizontal la “h” que lleva al final la función. Búsqueda horizontal para buscarh y búsqueda vertical para buscarv.

La idea es poder averigua por ejemplo para el producto “Prod-6” el “Total”. La función buscarh buscará en la primera fila, la fila 1, el “Prod-6” y lo localizará en la columna “G”. Podremos devolver cualquier valor de esa columna indicando el argumento “Indicador de filas” que para este ejemplo concreto sería la fila 6 que es donde se encuentran los “Totales”.

Argumentos de la función.

La función buscarh admite cuatro argumentos siendo tres de ellos obligatorios y el cuarto argumento opcional. Igual que ocurre con la función buscarv el cuarto argumento se refiere al tipo de búsqueda, exacta o aproximada, se recomienda utilizar siempre búsqueda exacta para evitar problemas. Además la búsqueda aproximada precisa de la ordenación de los elementos de la fila de búsqueda.

Valor_buscado: Es el valor que deseamos localizar en la fila.
Matriz_buscar_en: Es la lista en la que realizaremos la búsqueda del “Valor_buscado”. Es la tabla dónde se encuentran toda la información.
Indicador_de_fila: Una vez localizado el elemento en la primera fila podemos recuperar cualquier elemento que se encuentre en la misma columna indicando cuantas filas hacia abajo se encuentra posicionado dicho valor.
[ordenado]: Se refiere al tipo de coincidencia. 0 o Falso para coincidencia exacta. Utilizaremos 1, Verdadero u omitido para una coincidencia aproximada.

Ejemplo de uso de la función.

Supongamos un listado de notas y alumnos dado horizontalmente. Nosotros deseamos rellenar las notas en función de los DNI de forma vertical para publicarlo en un tablón.

La estructura de la función para el ejemplo concreto mostrado en la imagen sería la siguiente:

=BUSCARH(A6;$A$1:$G$3;3;FALSO)

En Inglés:

=HLOOKUP(A6;$A$1:$G$3;3;FALSE)

La función BuscarH en Inglés.

La función Buscar en Inglés es:

HLOOKUP (lookup_value; table_array;row_index_num; [range_lookup])

Descarga el fichero ejemplo.

[wpdm_package id=’2255′]

La función buscar nos permite localizar un valor dentro de una columna o “vector”. La función precisa que los datos estén ordenados de forma ascendente.

Función Buscar en Excel.

Escrito por Iván Antona en 22 mayo, 2016. Publicado en Funciones, Funciones Búsqueda y Referencia.

Función Buscar en Excel, para qué sirve.

La función buscar nos permite localizar un valor dentro de una columna o “vector”. La función buscar precisa que los datos de la columna se encuentren ordenados de forma ascendente. Funciones de búsqueda más sofisticadas como son la función buscarv y la función buscarh hacen que esta función casi no se utilice. No obstante sigue dentro de las funciones de búsqueda y referencia. Si nos fijamos en la imagen la búsqueda es un éxito puesto que después del código V-002 le sigue el código V-003 ya que los datos se han ordenado de forma ascendente en ese campo. Pero si se elige el valor V-003 veremos que nos devuelve Moncloa-Aravaca. A diferencia de la función buscarv que devuelve la primera aparición del valor, la función buscar, devuelve el último valor.

[wpdm_package id=’2258′]

Argumentos de la función en formato vectorial.

La función buscar puede utilizarse en formato vectorial o en formato matricial. En cada una de sus modalidades admite un número de argumentos diferente.

Veamos en primer lugar el formato vectorial que además es el más intuitivo y sencillo. Esta forma admite 3 argumentos dos de ellos son obligatorios. El tercer argumento no es necesario.

Valor_buscado: Es el valor que deseamos localizar en la columna. En el caso anterior sería el V-002,V-003
Vector_buscar_en: Es la lista en la que realizaremos la búsqueda del “Valor_buscado”. En la tabla se refiere a todos los elementos de la columna C llamada “Cod_Vendedor”
Vector_resultado: Una vez que se localizó el código del vendedor en el vector de la columna C podemos indicar un segundo vector, por ejemplo el vector que contiene los barrios que son los elementos de la columna F. De este modo cuando se localice el código de vendedor se devolverá el elemento que este en la misma fila que el pero en la columna de barrios. Así podemos averiguar cuál fue el barrio. Si no seleccionamos el vector resultado puesto que es un parámetro no obligatorio. Entonces la función devuelve el valor buscado siempre que lo encuentre.

Argumentos de la función en formato matricial.

Para el formato matricial la función solo admite dos argumentos y ambos son obligatorios.

Valor_buscado: Es el valor que deseamos localizar en la columna.
Matriz: Es la lista en la que realizaremos la búsqueda del “Valor_buscado”. Una vez localizado nos devuelve el valor que se encuentre en la misma fila que el valor buscado pero en la última columna. En el ejemplo que se muestra más adelante quedará claro su funcionamiento.

Ejemplo de uso de la función en formato vectorial.

Tenemos un listado de empleados con sus códigos de empleado ordenados de forma ascendente. Queremos averiguar el salario de un empleado tecleando su código de empleado.

En la celda J2 tendremos que teclear la siguiente función:

=BUSCAR (I2;$A$2:$A$115;$E$2:$E$115)

Ejemplo de uso de la función en formato matricial.

En la forma matricial la función siempre devuelve el valor de la última columna de la matriz. Eso significa que si hacemos el mismo ejemplo del caso anterior en vez de obtener el sueldo de Luisa Sierra obtendremos su fecha de nacimiento. 21/11/1964.

Si lo que deseamos es que nos devuelva el sueldo entonces en el argumento “Array” o “Matriz” tendremos que seleccionar solamente hasta la columna E. La función quedaría escrita así:

=BUSCAR(I2;$A$2:$E$115)

[wpdm_package id=’2262′]

La función Buscar en Inglés.

La función Buscar en Inglés es:

LOOKUP (lookup_value; lookup_vector; [lookup_result])

LOOKUP (lookup_value; array)