Funciones: DESREF, COINCIDIR y CONTAR.SI

Descripcion de las 3 funciones:

FUNCION “DESREF”

Categoría: Búsqueda y referencia

¿QUÉ HACE?

Devuelve una referencia a una celda o un rango de celdas que ha sido desplazada en base a otra referencia dada.

SINTAXIS

DESREF(ref, filas, columnas, [alto], [ancho])

·         ref (obligatorio): Referencia en la que se basa la desviación.

·         filas (obligatorio): Número de filas hacia abajo (positivo) o hacia arriba (negativo) de la celda superior izquierda del resultado.

·         columnas (obligatorio): Número de columnas hacia la derecha (positivo) o hacia la izquierda (negativo) de la celda superior izquierda del resultado.

·         alto (opcional): El número de filas (alto) que se desea que tenga la referencia devuelta.

·         ancho (opcional): El número de columnas (ancho) que se desea que tenga la referencia devuelta.

EJEMPLOS

DESREF(A1, 4, 1) = Devuelve el valor de la celda B5

FUNCION “COINCIDIR”

Categoría: Búsqueda y referencia

¿QUÉ HACE?

Busca un valor dentro de una lista y regresa la posición relativa del valor dentro del rango.

SINTAXIS

COINCIDIR(valor_buscado, rango, [tipo_de_coincidencia])

·         valor_buscado (obligatorio): Valor que se buscará en el rango

·         rango (obligatorio): El rango de celdas con los valores.

·         tipo_de_coincidencia (opcional): Indica si será una coincidencia exacta ó aproximada. Si se omite se toma como aproximada.

EJEMPLOS

COINCIDIR(«Excel», A1:A10, 0) = Encuentra «Excel» en la columna A y regresa la posición dentro de la lista donde fue encontrado el valor

FUNCION “CONTAR.SI”

Categoría: Estadísticas

¿QUÉ HACE?

Cuenta las celdas de un rango que cumplen con la condición especificada.

SINTAXIS

CONTAR.SI(rango, condición)

·         rango (obligatorio): El rango de celdas a las que se le aplicará la condición para considerar su cuenta.

·         condición (obligatorio): La condición que se debe cumplir para ser incluido en la cuenta.

EJEMPLOS

CONTAR.SI(A1:A5, «>5») = Cuenta las celdas que tengan un valor mayor a 5
CONTAR.SI(B1:B5, «excel») = Cuenta las celdas que tengan el valor «excel»

VALORES DE UNA LISTA BASADOS EN LA SELECCIÓN DE OTRA LISTA

Cuando creamos listas desplegables en Excel, es común tener un par de ellas que sean dependientes entre sí, es decir una lista cuyos valores dependerán de la selección realizada en otra lista desplegable.

Aunque existe más de una técnica para crear listas dependientes en Excel, hoy utilizaremos las funciones DESREF, COINCIDIR y CONTAR.SI para vincular nuestras listas.

En nuestro ejemplo de tipo contable crearemos la primera lista en la Columna A con los nombres de seis (6) rubros del Plan de Cuentas de una Empresa, a saber: DISPONIBILIDADES, CREDITOS, BIENES DE CAMBIO, INVERSIONES, BIENES DE USO y CARGOS DIFERIDOS.

En las Columnas C colocaremos el nombre de algún Rubro de los Colocados en la Columna A; y en la Columna D colocaremos el Nombre de las Cuentas que pertenece al Rubro colocado en la Columna C. Estos pasos de colocar datos en Columnas C y D debemos repetirlos hasta completar todas las Cuentas del Plan de Cuentas.

Una vez que se elija una opción de la primera lista, la segunda lista mostrará los nombres de las Cuentas que pertenecen a dicho Rubro.

LISTAS DEPENDIENTES EN EXCEL

1) Lista de Rubros: Nuestra hoja contiene toda la información necesaria para crear nuestras listas desplegables tal como lo puedes ver en la siguiente imagen: (“Imagen: Listas Dependientes_001”)

Imagen: Listas Dependientes_001

Una vez que tenemos el formato adecuado en los datos, podemos crear nuestra primera lista desplegable para elegir el Rubro.

Pare ello seleccionaré la celda H1, y luego haremos:

Pestaña DATOS -> VALIDACION DE DATOS y crearemos la lista desplegable como se muestra en la (“Imagen: Listas Dependientes_002”):

Imagen: Listas Dependientes_002

Al hacer clic en Aceptar, la celda H1 mostrará los Rubros del Plan de Cuentas de la empresa,  indicados en el rango A2:A7 de nuestra hoja de cálculo y aparecerá el botón hacia abajo a la derecha de la Celda H1, como se ve en la siguiente: (“Imagen: Listas Dependientes_003”)

Imagen: Listas Dependientes_003

2) Lista de Cuentas: Ahora crearemos la lista vinculada, la cual dependerá de la selección realizada en la celda H1. Ese dinamismo de la segunda lista es lo que nos obliga a utilizar funciones de Excel que nos permitirán especificar el rango de datos origen en base a la selección realizada en la primera lista.

La función DESREF nos permite crear referencias a un rango de celdas en “tiempo real”, es decir, en el momento exacto en el que las necesitamos sin que la referencia esté previamente definida. Y lo mejor es que sus argumentos nos permiten indicar la celda inicial y final del rango, que es el comportamiento que necesitamos para nuestra lista.

Por ejemplo: si elegimos el Rubro “DISPONIBILIDADES”, de la primera lista, queremos que la Lista Dependiente tenga como origen de datos el rango D2:D3. Pero si elegimos el Rubro “CREDITOS”, de la primera lista, queremos que la Lista Dependiente tenga como origen de datos el Rango D4:D5.

Este comportamiento lo lograremos con la función DESREF y en especial con el uso de su segundo y cuarto argumentos. La fórmula que utilizaremos como el origen de la Lista Dependiente y en la Celda H2, será la siguiente:

=DESREF(D2;COINCIDIR(H1;C2:C13;0)-1; 0; CONTAR.SI(C2:C13; H1); 1)

La Lista Dependiente nos mostrará las Cuentas del Rubro “DISPONIBILIDADES”, o del Rubro “CREDITOS”, como se muestra en: (“Imagen: Listas Dependientes_004”)

Imagen: Listas Dependientes_004

EXPLICACIÓN DE LA FÓRMULA

El primer argumento de la función DESREF es la celda donde comenzará nuestro rango. En este caso será la celda D2 porque ahí están los datos de origen de nuestra lista dependiente.

El segundo argumento de la función DESREF nos permite indicar un desplazamiento en filas a partir de la posición inicial que hemos indicado en el primer argumento. Es decir, si el segundo argumento tiene el valor 2, la función DESREF se moverá 2 filas hacia abajo y nos devolverla una referencia a la celda D5.

En lugar de que el segundo argumento de la función DESREF tenga un número fijo de filas, utilizamos la función COINCIDIR para encontrar la celda donde inician los valores del área indicada en la celda H1. Por ejemplo, si la celda H1 tiene el Rubro  “DISPONIBILIDADES”, la función COINCIDIR devolverá el valor 2, si se elige el Rubro “CREDITOS”, se devolverá el valor 4.

Esto quiere decir que Excel ha descubierto que el Rubro “CREDITOS” inicia en la fila 4 del rango C2:C13. Este dato es muy importante porque nos ayuda a indicar a la función DESREF la celda exacta donde comienzan los nombres de las Cuentas que pertenecen al área seleccionada.

Observa que el segundo argumento de la función DESREF hace la resta del valor 1 al resultado de la función COINCIDIR. Esto es importante para indicar la cantidad exacta de celdas que deberá desplazarse hacia abajo hasta llegar al inicio de los datos.

El tercer argumento de la función DESREF es cero porque no queremos desplazarnos ninguna columna. De esta manera, los primeros tres argumentos de la función DESREF nos sirven para indicar la celda inicial del rango que deseamos obtener.

El cuarto argumento nos permite indicar la cantidad de filas que deseamos incluir en la referencia devuelta por la función DESREF. Para eso utilizaremos la función CONTAR.SI que nos ayudará a obtener la cantidad exacta de Cuentas que pertenecen a un Rubro del Plan de Cuentas de la empresa.

Como lo mencioné al principio del artículo, existen diferentes métodos para crear Listas Dependientes. Este método descripto precedentemente es una forma de crear Listas Dependientes, que requiere que los Rubros se repitan en la Columna C, por cada una de las cuentas que correspondan en la Columna D (NO podremos por ejemplo: colocar en C2 el Rubro Disponibilidades, en D2 la Cuenta: Caja; luego en C3 el Rubro Créditos, en D3 la Cuenta: Cuentas a Cobrar; luego en C4 el Rubro Disponibilidades, en D4 la Cuenta: Banco Nación; etc…). Es decir, debemos colocar en orden todas las cuentas del Rubro Disponibilidades, luego las del Rubro Créditos, luego las del Rubro Bienes de Cambio, etc. como se pueden ver en el las imágenes precedentes.