Funciones

La función UNICOS

La función UNICOS se ha incorporado en el año 2020 y pertenece al grupo de funciones de búsqueda y referencia y además, es una función de matrices dinámicas, lo que significa que el resultado que devuelve puede ocupar más de una celda.

La función UNICOS devuelve una lista de valores únicos de una lista o rango.

Sintáxis de la función UNICOS

Su sintáxis es la siguiente:

=UNICOS(matriz;[by_col];[exactly_once])

Matriz: Es el rango de celdas o matriz de la que queremos extraer los valores únicos. Puede estar formada por valores de cualquier tipo.

By_col: Opcional. Con este argumento le indicamos si queremos que el resultado se muestre por columnas o filas. Es un valor lógico (FALSO o VERDADERO). Por defecto aparece FALSO.

Si se pone FALSO se devolverá el resultado por filas.
Si se pone VERDADERO se devuelve el resultado por columnas.

Exactly_one: Opcional. Con este argumento le indicamos si queremos que nos muestre solo los valores que se encuentran exactamente una vez. Es un valor lógico (FALSO o VERDADERO). El valor por defecto es FALSO.

Si ponemos FALSO devolverá todos los valores una vez.
Si ponemos VERDADERO devolverá solo aquellos valores que aparezca una única vez.

Ejercicio con la función UNICOS

En el siguiente listado aparecen un rango de datos con Países y Ciudades.

Si deseamos tener un listado con los diferentes países indicados nos situaríamos en la celda en la que queremos mostrar el resultado y escribimos =UNICOS(A2:A23)

De esa forma aparecen los 4 países que aparecen en el listado.

Aplicar UNICOS a pares de valores

También podemos aplicar la función para ver los pares de valores. Así si aplicamos la función UNICOS a un rango de datos con más de una columna nos devolverá los valores únicos para las combinaciones de País-Ciudad.

Devolver valores únicos por columna

Si utilizamos el segundo argumento y le indicamos VERDADERO los resultados aparecerán por columna. Esto sería aplicable cuando los valores de origen se encuentran por columnas.

Valores que aparecen una única vez

Si lo que queremos es mostrar los valores que aparecen exactamente una vez tendremos que utilizar el tercer argumento, escribiendo VERDADERO.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

La función FORMULATEXTO

La función FORMULATEXTO es una función de búsqueda y referencia, que sirve para devolver una fórmula como cadena de texto. Es decir se muestra en la celda la fórmula tal y como está escrita en la barra de fórmulas.

La sintaxis de la función FORMULATEXTO es:

=FORMULATEXTO(referencia)

donde referencia es una referencia a una celda o rango de celdas.

Ejercicio con la función FORMULATEXTO

En este ejemplo tenemos una serie de personas y sus ingresos.

En la celda B11 tenemos el cálculo de la suma de los ingresos. Para ello se ha utilizado en la celda B11 la fórmula SUMA(B2:B10).

Si deseamos que en la celda C11 se muestre la fórmula que hemos utilizado en la celda B11, lo único que tenemos que hacer es escribir en la celda C11 la fórmula =FORMULATEXTO(B11)

De esa forma nos muestra la fórmula utilizada.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

La función SIFECHA en Excel

La función SIFECHA es una función bastante especial, ya que aunque está implementada en Excel, no tenemos acceso a la misma desde el catálogo de funciones. Es una función muy útil si lo que queremos conocer es la edad de alguna persona.

La función SIFECHA

Calcula el número de días, meses o años entre dos fechas.

Sintaxis de la función SIFECHA

SIFECHA(fecha_inicial;fecha_final;unidad)

Fecha_inicial: Este argumento representa la primera fecha del período o la fecha inicial.
Fecha_final: Una fecha que representa la última del período o al fecha de finalización.
Unidad: Corresponde al tipo de información que desea obtener:
- «Y»: Devuelve el número de años completos en el período.
- «M»: Devuelve el número de meses completos en el período.
- «D»: Devuelve el número de días en el período.
- «MD»: Devuelve la diferencia entre los días en fecha_inicial y fecha_final, excluyendo los meses y años. Tiene limitaciones por lo que no se recomienda su uso.
- «YM»: Devuelve la diferencia entre los meses de fecha_inicial y fecha_final, excluyendo los años.
- «YD»: Devuelve la diferencia entre los días de fecha_inicial y fecha_final, excluyendo los años.

Ejemplo de utilización de la función SIFECHA

Cálculo de la edad en años

Consideremos que deseamos conocer la edad en años de las siguientes personas:

La forma más sencilla de realizarlo es utilizando la función SIFECHA con la unidad «Y»

Cálculo de la antigüedad en años, meses y días

Consideremos que deseamos conocer la antigüedad en una empresa en años, meses y días, de los siguientes trabajadores:

En este caso necesitamos hacer uso de las unidades «Y» para el cálculo de años completos, las unidades «YM» para el cálculo de los meses sin considerar los años completos, y las unidades «MD» para el cálculo de los días sin considerar los meses completos.

En la fila 6 indicamos las fórmulas que se han utilizado en cada caso.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Función ORDENAR en Excel

La función ORDENAR es una nueva función implementada en Excel en 2020, por lo que en la actualidad solo está disponible para suscriptores a Excel 365.

La función ORDENAR ordena el contenido de un rango o matriz.

Sintaxis de la Función ORDENAR

=ORDENAR(matriz;[ordenar_indice];[criterio_ordenación];[por_col])

matriz (obligatorio): Rango o matriz para ordenar.
ordenar_indice (opcional): Un número que indica la fila o columna por la que ordenar. Si no se indica nada ordena por primera columna o fila.
criterio_ordenación (opcional): Un número que indica el orden deseado, 1 para orden ascendente (predeterminado), -1 para orden descendente
por_col (opcional): Un valor lógico que indica la dirección de ordenación deseada:
- FALSO para ordenar por fila (predeterminado)
- VERDADERO para ordenar por columna

Ejemplo de la función ORDENAR

Para utilizar la función ORDENAR comenzamos seleccionando el rango de celdas original que queremos transponer.

En nuestro ejemplo son una lista de datos que se encuentran en el rango A2:B15 y que deseamos ordenar según el sueldo de cada persona de forma ascendente.

Lo primero que hacemos es situarnos en la celda E3 y escribir la fórmula siguiente:

=ORDENAR(A3:B15;2;1)

Podíamos haber prescindido del tercer argumento ya que por defecto la ordenación la realiza de forma ascendente.

Como vemos no mantiene el formato del rango de datos original por lo que lo mejor es hacer un Pegar formato.

Lo mejor es que, al aplicar la función ORDENAR, cada vez que se cambia algún dato en el rango original, nos actualiza la ordenación de forma automática.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

La función FILTRAR: Trucos avanzados

La función FILTRAR es una función novedosa que nos ofrece múltiples posibilidades. Posiblemente es una de las últimas funciones que más utilidad nos ofrece.

Así lo vimos en un artículo anterior de la función FILTRAR, donde vimos que la función FILTRAR permite filtrar un rango de datos en función de los criterios que se definan.

En este video iremos un paso más allá, analizaremos todas las posibilidades que nos ofrece la función FILTRAR. Veremos todos los trucos de la función FILTRAR en Excel.

La función FILTRAR

Primero vamos a recordar cómo funciona la función FILTRAR en Excel.

Esta es un función de matriz dinámica, lo que significa que muestra el resultado, no en una celda, sino en un rango de celdas.

Para entender el funcionamiento de una función lo mejor es entender su sintaxis. Después ya exprimiremos todas sus posibilidades.

Sintaxis de la Función FILTRAR

=FILTRAR(matriz;incluir;[si_vacío])

matriz (obligatorio): Rango o matriz para filtrar.
incluir (obligatorio): Es una matriz booleana cuyo alto o ancho es el mismo que el de la matriz.
si_vacío (opcional): Es el valor a devolver si todos los valores de la matriz incluida están vacíos (el filtro no devuelve nada)

Ahora veremos las diferentes posibilidades de aplicación de la función FILTRAR.

Filtrar datos según un criterio

En este caso veremos un ejemplo simple y básico de la función FILTRAR, en el cual estamos filtrando una tabla en función de un criterio.

La tabla que tenemos es la siguiente:

Ahora deseamos filtrar según un solo criterio esa tabla.

En este caso deseamos filtrar todos los datos que tienen un abono anual.

El primer argumento de la función sería la tabla de datos que deseamos filtrar. El segundo argumento es el criterio a cumplir. En este caso que sean un abono «anual».

Filtrar datos según un criterio devolviendo 1 campo

No es necesario que cuando se aplica un filtro con la función FILTRAR nos devuelva todas las columnas de la tabla.

Podemos decidir que solamente nos devuelva una columna en función del filtro realizado.

En este caso solamente queremos que nos devuelva el nombre de los clientes que tienen el abono anual.

La diferencia con el caso anterior es que en lugar de indicar como primer argumento de la función toda la tabla, seleccionamos unicamente el campo que queremos que nos devuelva.

De la misma forma podemos seleccionar varias columnas consecutivas.

Filtrar datos según un criterio devolviendo varias columnas no consecutivas

Cuando los datos filtrados que necesitamos obtener tienen los datos de algunas columnas no necesariamente consecutivas podemos utilizar la novedosa función ELEGIRCOLS.

Así, si lo que queremos es nos filtre los datos por Tipo de abono anual, pero solo nos presente el nombre, apellidos y edad, tendremos que anidar la función FILTRAR dentro de la función ELEGIRCOLS.

Filtrar datos que cumplan varios criterios a la vez

Si queremos que se filtren los datos que cumplan dos criterios a la vez lo que hacemos es indicar en el segundo argumento de la función una multiplicación de los criterios.

De esa forma solo nos devuelve los datos que cumplan los dos criterios indicados.

Esto también sería aplicable si tenemos más de dos criterios.

Filtrar datos que cumplan algunos criterios

Puede darse el caso de que queramos que los datos que nos devuelva sean los que cumplan alguno de los criterios indicados, no siendo necesarios que cumpla los dos criterios a la vez. Con que cumpla uno de ellos sería suficiente.

Para ello, en lugar de usar una multiplicación utilizaríamos la suma.

El caso es igual que el anterior pero cambiando la multiplicación en el segundo argumento de la función por la suma.

Esto también sería aplicable si queremos que cumpla dos posibles criterios en una misma columna. Es decir, si por ejemplo, queremos que nos filtre todos los datos que cumplan que el tipo de abono sea Anual o Semestral.

También aquí podemos utilizar la suma como en el caso anterior.

Función FILTRAR cuando incluye una cadena de texto

También es posible filtrar valores que incluyan parte de un texto.

Si por ejemplo, deseamos filtrar con todos los apellidos que incluyan cadena de texto «ar» en el nombre.Para ello, anidaremos varias funciones dentro de la función filtrar, como vemos en la imagen siguiente.

Como podemos observar, la función FILTRAR es muy versatil siendo muy útil para ser utilizada en diversos casos prácticos que se nos pueden presentar.

Video explicativo:

Para suscribirte a mi canal de YouTube:

Suscríbete

Realizar una búsqueda por cadena de texto en Excel

En este artículo veremos cómo podemos hacer una búsqueda de una cadena de texto en un listado de datos, de forma que conforme vayamos escribiendo una cadena de texto en una celda nos vaya mostrando todos aquellos datos que incluyan esa cadena de texto,

Eso puede ser muy útil cuando deseamos filtrar entre todos los artículos de nuestro inventario de productos.

Para ello utilizaremos tres columnas auxiliares y diversas fórmulas que se pueden implementar fácilmente para el cualquier otro caso que pueda requerirse.

Caso práctico: búsqueda de cadena de texto en Excel

En este caso práctico tenemos en la columna A un listado con todos los países del mundo. Conforme escribamos un texto en la celda G2 queremos que debajo, de G3 hacia abajo de todos los países que contengan esa cadena de texto.

En el ejemplo que tenemos al escribir «UN» aparecen todos los países que contienen UN en su nombre (Burundi, Hungría, etc.).

Para poder realizar este ejercicio necesitaremos 3 columnas auxiliares que tendremos ocultas.

Las tres columnas auxiliares son las columnas B, C y D.

Asignar número de fila

La columnas B lo que hace es asignarle un número de fila a cada uno de los países y lo vamos a obtener de una forma dinámica, con la función FILAS.

La función FILAS devuelve el número de filas de una referencia o matriz.

La formula a introducir en este caso sería =FILAS(A$3:A3).

De esa forma obtenemos todos los países numerados según su posición en filas.

Ver si el dato contiene la cadena de texto

En la columna auxiliar C introduciremos un fórmula para que, en el caso de que cumpla la condición de que el país contenga el texto introducido en la celda G2, nos devuelva el número de fila de ese país. En caso contrario queremos que la celda quede en blanco.

Con la función ENCONTRAR buscaría la cadena de texto en el nombre del país. En caso de encontrarlo, nos devuelve la posición de esa cadena de texto. Es decir, devolvería un número. Por tanto, si contiene la cadena de texto devuelve un número y en caso contrario, devolvería el error #¡VALOR!.

Con la función ESNUMERO obtenemos el valor VERDADERO si encuentra un número en la celda y FALSO si no tiene un número.

En consecuencia realizamos utilizaremos la función SI para que nos devuelva el número de fila del país si el VERDADERO y la celda en blanco si es FALSO.

Anidando las tres funciones (ENCONTRAR, ESNUMERO y SI) en una fórmula obtenemos el resultado buscado: el número de fila del país que cumple la condición de incluir la cadena de texto.

La fórmula a utilizar en nuestro caso práctico sería la siguiente:

=SI(ESNUMERO(ENCONTRAR($G$2:A3));B3;»»)

Agrupar los países que cumplen la condición

En la tercera columna, la columna D, lo que haremos es colocar todos los países que cumplen la fórmula anterior (es decir, que contienen la cadena de texto).

Para ello utilizaremos la función K.ESIMO.MENOR.

Con esa función ordenaremos en la columna D todos los países de menor a mayor número de fila.

Lo anidaremos en la función SI.ERROR para que no aparezca un error cuando el país no contenga la cadena de texto.

La fórmula a utilizar es la siguiente:

=SI.ERROR(K.ESIMO.MENOR($C$3:$C$238;B3);»»)

Vemos que todos los países que cumplen que contienen la cadena de texto, su número de fila aparece en la columna D, ordenado de menos a más.

Obtener el nombre del país, una vez que tenemos la fila sería sencilla.

Obtener el listado de países

Para obtener el nombre del país utilizaremos la función INDICE.

Realizaría la búsqueda del rango de datos de países y como tenemos en la columna auxiliar D el número de fila, ese sería el segundo argumento de la función.

También aquí lo anidaremos en la función SI.ERROR.

La fórmula que utilizamos es =SI.ERROR(INDICE(A$3:A$238;D3);»»)

Una vez realizados todos estos cálculos podemos ocultar las columnas auxiliares y utilizar la hoja para hacer una búsqueda de datos según que contengan una cadena de texto o no.

Video explicativo:

Para suscribirte a mi canal de YouTube:

Suscríbete

Función FILTRAR en Excel

La función FILTRAR es una nueva función implementada en Excel en 2020, por lo que en la actualidad solo está disponible para suscriptores a Excel 365.

La función FILTRAR permite filtrar un rango de datos en función de los criterios que se definan.

Sintaxis de la Función FILTRAR

=FILTRAR(matriz;incluir;[si_vacío])

matriz (obligatorio): Rango o matriz para filtrar.
incluir (obligatorio): Es una matriz booleana cuyo alto o ancho es el mismo que el de la matriz.
si_vacío (opcional): Es el valor a devolver si todos los valores de la matriz incluida están vacíos (el filtro no devuelve nada)

Ejemplo de la función FILTRAR

Para utilizar la función FILTRAR comenzamos seleccionando el rango de celdas original que queremos filtrar.

En nuestro ejemplo son una lista de datos que se encuentran en el rango A3:B15 y que deseamos filtrar según el critrerio de sueldos superiores a 2000€.

Lo primero que hacemos es situarnos en la celda D6 y escribir la fórmula siguiente:

=FILTRAR(A3:B15;B3:B15>E3)

El segundo argumento, B3:B15>E3, establece el criterio por el cual se va a filtrar. También se podía haber escrito B3:B15>2000.

Lo mejor es que se puede combinar con la función ORDENAR, filtrándose lo datos y ordenándose a la misma vez, combinando para ello las funciones FILTRAR y ORDENAR, como podemos ver en el video explicativo.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

La función IMAGEN en Excel

La función IMAGEN es una fórmula que nos permite insertar una imagen dentro de una celda.

Esa imagen se muestra dentro de la celda, con diferentes formas de ajuste dentro de la misma.

El uso de esta función puede ser muy útil, por ejemplo, para la creación de un listado de mercancías de una empresa, incluir el logotipo de una empresa o para mostrar los inmuebles de una inmobiliaria.

En la actualidad esta novedosa función solamente está disponible para usuarios de Office 365.

Sintaxis de la función IMAGEN

La sintaxis de la función IMAGEN es la siguiente:

=IMAGEN(origen, [texto_alternativo], [dimensiones], [alto], [ancho])

Donde:

origen (obligatorio): La ruta de acceso URL, mediante un protocolo «https», del archivo de imagen. Entre los formatos de archivo admitidos se incluyen BMP, JPG/JPEG, GIF, TIFF, PNG, ICO y también WEBP (WEBP no se admite en Web y Android). Se puede enlazar a una celda que incluya la ruta o escribir directamente la ruta entre comillas.
texto_alternativo (opcional): Texto alternativo que describe la imagen para accesibilidad.
dimensiones (opcional): Especifica las dimensiones de la imagen. Hay varios valores posibles:
- 0 Ajuste la imagen en la celda y mantenga su relación de aspecto.
- 1 Rellene la celda con la imagen y omita su relación de aspecto.
- 2 Mantenga el tamaño de la imagen original, que puede superar el límite de la celda.
- 3 Personalice el tamaño de la imagen con los argumentos de altura y anchura.
alto (opcional): La altura personalizada de la imagen en píxeles.
ancho (opcional): La anchura personalizada de la imagen en píxeles.

Errores de la función IMAGEN

Excel devuelve un error #VALOR! en las siguientes circunstancias:

Si el archivo de imagen no es un formato admitido.
Si el origen o alt_text no es una cadena.
Si el tamaño no está entre 0 y 3.
Si el tamaño es 3, pero el alto y el ancho están en blanco o contienen valores menores que 1.
Si el tamaño es 0, 1 o 2 y también proporciona un ancho o alto.

Ejemplo de utilización de la función IMAGEN

Inserción de la imagen de un tigre en una celda en Excel

En este caso deseamos insertar una imagen de un tigre en la celda B1 utilizando para ello la función IMAGEN.

Obtener URL de la imagen

Primero tendremos que obtener la dirección URL de la imagen que queremos incluir en esa celda. Para ello vamos a Google y escribimos «tigre» y hacemos clic en la opción Imágenes.

Aparecen multitud de imágenes.

Ahora seleccionamos la imagen que queramos y vemos que aparece en la parte derecha.

Sobre esa imagen que nos muestra en la parte derecha hacemos clic con el botón derecho del ratón y elegimos «Copiar vínculo de imagen», con lo que la URL de esa imagen se ha copiado en el portapapeles.

Utilizar la fórmula IMAGEN

Ahora volvemos a la hoja de Excel y escribimos en la celda B1 la función IMAGEN y como primer argumento (y único obligatorio) escribimos la URL copiada entre comillas.

Le damos a Intro y vemos que inserta la imagen en la celda.

Además, si cambiamos el alto o ancho de la columna o fila, la imagen se va ajustando, sin perder la relación de aspecto.

Como segundo argumento, podemos indicarle un texto alternativo que describe la imagen.

El tercer argumento es muy interesante ya que configura las dimensiones de la imagen.

Lo más habitual es utilizar las siguientes opciones:

0: Es la opción por defecto, en la que se ajusta a las celdas, manteniendo la relación de aspecto.
1: La imagen se ajusta totalmente en ancho y alto a la celda, aunque pierda la relación de aspecto, como vemos en la imagen siguiente.
3: Se puede personalizar el alto y ancho en píxeles de la imagen, para lo cual habría que indicar el alto y ancho en el 4º y 5º argumento, respectivamente.

Video explicativo:

Para suscribirte a mi canal de YouTube:

Suscríbete

Las funciones de base de datos en Excel

Excel dispone de diversas funciones de base de datos que podemos utilizar, aunque realmente las que se suelen utilizar en la mayoría de los casos son los que veremos a continuación.

Las funciones de base de datos que describiremos a continuación son BDSUMA, BDPROMEDIO, BDCONTARA, BDMAX y BDMIN.

Cada una de esas funciones nos puede venir bien en determinados casos, por lo que es aconsejable conocerlas.

La función BDSUMA

La función BDSUMA suma los números de un campo (columna) de registros de una lista o base de datos que cumplen las condiciones especificadas.

Sintaxis

=BDSUMA(base_de_datos, nombre_de_campo, criterios)

La sintaxis de la función BDSUMA tiene los siguientes argumentos:

base_de_datos Obligatorio. El rango de celdas que compone la lista o base de datos.
nombre_de_campo Obligatorio. Indica qué columna se usa en la función. Escriba el rótulo de la columna entre comillas, como por ejemplo «Edad» o «Rendimiento», o un número (sin las comillas) que represente la posición de la columna en la lista: 1 para la primera columna, 2 para la segunda y así sucesivamente.
criterios Obligatorio. Es el rango de celdas que contiene las condiciones especificadas. Puede usar cualquier rango en el argumento Criterios mientras este incluya por lo menos un rótulo de columna y al menos una celda debajo del rótulo de columna en la que se pueda especificar una condición de columna.

Observaciones

Cualquier rango se puede usar como argumento criterios, siempre que incluya por lo menos un nombre de campo y por lo menos una celda debajo del nombre de campo para especificar un valor de comparación de criterios.

La función BDPROMEDIO

La función BDPROMEDIO devuelve el promedio de los valores de un campo (columna) de registros en una lista o base de datos que cumple las condiciones especificadas.

Sintaxis

=BDPROMEDIO(base_de_datos, nombre_de_campo, criterios)

La sintaxis de la función BDPROMEDIO tiene los siguientes argumentos:

base_de_datos Obligatorio. El rango de celdas que compone la lista o base de datos.
nombre_de_campo Obligatorio. Indica qué columna se usa en la función. Escriba el rótulo de la columna entre comillas, como por ejemplo «Edad» o «Rendimiento», o un número (sin las comillas) que represente la posición de la columna en la lista: 1 para la primera columna, 2 para la segunda y así sucesivamente.
criterios Obligatorio. Es el rango de celdas que contiene las condiciones especificadas. Puede usar cualquier rango en el argumento Criterios mientras este incluya por lo menos un rótulo de columna y al menos una celda debajo del rótulo de columna en la que se pueda especificar una condición de columna.

Observaciones

Cualquier rango se puede usar como argumento criterios, siempre que incluya por lo menos un nombre de campo y por lo menos una celda debajo del nombre de campo para especificar un valor de comparación de criterios.

La función BDCONTARA

La función BDCONTARA cuenta las celdas que no están en blanco de un campo (columna) de registros de una lista o base de datos que cumplen las condiciones especificadas.

Sintaxis

=BDCONTARA(base de datos, campo, criterios)

La sintaxis de la función BDCONTARA tiene los siguientes argumentos:

base_de_datos Obligatorio. El rango de celdas que compone la lista o base de datos.
nombre_de_campo Opcional. Indica qué columna se usa en la función. Escriba el rótulo de la columna entre comillas, como por ejemplo «Edad» o «Rendimiento», o un número (sin las comillas) que represente la posición de la columna en la lista: 1 para la primera columna, 2 para la segunda y así sucesivamente. El argumento nombre_de_campo es opcional. Si lo omite, BDCONTARA cuenta todos los registros de la base de datos que coinciden con los criterios.
criterios Obligatorio. Es el rango de celdas que contiene las condiciones especificadas. Puede usar cualquier rango en el argumento Criterios mientras este incluya por lo menos un rótulo de columna y al menos una celda debajo del rótulo de columna en la que se pueda especificar una condición de columna

La función BDMAX

La función BDMAX devuelve el valor máximo de un campo (columna) de registros en una lista o base de datos que cumple las condiciones especificadas.es.

Sintaxis

=BDMAX(base_de_datos, nombre_de_campo, criterios)

La sintaxis de la función BDMAX tiene los siguientes argumentos:

base_de_datos Obligatorio. El rango de celdas que compone la lista o base de datos.
nombre_de_campo Opcional. Indica qué columna se usa en la función. Escriba el rótulo de la columna entre comillas, como por ejemplo «Edad» o «Rendimiento», o un número (sin las comillas) que represente la posición de la columna en la lista: 1 para la primera columna, 2 para la segunda y así sucesivamente.
criterios Obligatorio. Es el rango de celdas que contiene las condiciones especificadas. Puede usar cualquier rango en el argumento Criterios mientras este incluya por lo menos un rótulo de columna y al menos una celda debajo del rótulo de columna en la que se pueda especificar una condición de columna.

La función BDMIN

La función BDMIN devuelve el valor mínimo de un campo (columna) de registros en una lista o base de datos que cumple las condiciones especificadas.es.

Sintaxis

=BDMIN(base_de_datos, nombre_de_campo, criterios)

La sintaxis de la función BDMIN tiene los siguientes argumentos:

base_de_datos Obligatorio. El rango de celdas que compone la lista o base de datos.
nombre_de_campo Opcional. Indica qué columna se usa en la función. Escriba el rótulo de la columna entre comillas, como por ejemplo «Edad» o «Rendimiento», o un número (sin las comillas) que represente la posición de la columna en la lista: 1 para la primera columna, 2 para la segunda y así sucesivamente.
criterios Obligatorio. Es el rango de celdas que contiene las condiciones especificadas. Puede usar cualquier rango en el argumento Criterios mientras este incluya por lo menos un rótulo de columna y al menos una celda debajo del rótulo de columna en la que se pueda especificar una condición de columna.

Video explicativo:

A continuación veremos un video en el que utilizaremos las diferentes funciones de base de datos en un ejercicio práctico.

Para suscribirte a mi canal de YouTube:

Las funciones estadísticas en Excel

Excel dispone de diversas funciones estadísticas que podemos utilizar, aunque realmente las que se suelen utilizar en la mayoría de los casos son los que veremos a continuación.

Las funciones estadísticas CONTARA, CONTAR, MAX, MIN, PROMEDIO, MODA, MEDIANA, K.ESIMO.MAYOR y K.ESIMO.MENOR.

Cada una de esas funciones nos puede venir bien en determinados casos, por lo que es aconsejable conocerlas.

La función CONTARA

La función CONTARA cuenta la cantidad de celdas que no están vacías en un intervalo.

Sintaxis

=CONTARA(valor1;valor2;…)

La sintaxis de la función CONTARA tiene los siguientes argumentos:

valor1 Obligatorio. Primer argumento que representa los valores que desea contar.
valor2 Opcional. Argumentos adicionales que representan los valores que se desea contar, hasta un máximo de 255 argumentos.

Observaciones

La función CONTARA cuenta las celdas que contienen cualquier tipo de información, incluidos los valores de error y texto vacío («»).
Si no necesita contar valores lógicos, texto o valores de error (en otras palabras, si desea contar solo las celdas que contienen números), es mejor utilizar la función CONTAR.
Si deseamos contar solo celdas que cumplan con determinados criterios, debemos utilizar la función CONTAR.SI o la función CONTAR.SI.CONJUNTO.

La función CONTAR

La función CONTAR cuenta la cantidad de celdas que contienen números y cuenta los números dentro de la lista de argumentos.

Sintaxis

=CONTAR(valor1;valor2;…)

La sintaxis de la función CONTAR tiene los siguientes argumentos:

valor1 Obligatorio. Primer elemento, referencia de celda o rango en el que desea contar números.
valor2 Opcional. Hasta 255 elementos, celdas de referencia o rangos adicionales en los que desea contar números.

Observaciones

Se cuentan argumentos que son números, fechas o una representación de texto de los números (por ejemplo, un número entre comillas, como «1»).
Se tienen en cuenta los valores lógicos y las representaciones textuales de números escritos directamente en la lista de argumentos.
No se cuentan los argumentos que sean valores de error o texto que no se puedan traducir a números.
Si un argumento es una matriz o una referencia, solo se considerarán los números de esa matriz o referencia. No se cuentan celdas vacías, valores lógicos, texto o valores de error de la matriz o de la referencia.

La función MAX

La función MAX devuelve el valor máximo de un conjunto de valores.

Sintaxis

=MAX(número1; número2,…)

La sintaxis de la función MAX tiene los siguientes argumentos:

número1 Obligatorio. El primer número, referencia de celda o rango para el cual desea el valor máximo.
número2 Opcional. Números, referencias de celda o rangos adicionales para los que desea el valor máximo, hasta un máximo de 255.

Observaciones

Los argumentos pueden ser números o nombres, matrices o referencias que contengan números.
Se tienen en cuenta los valores lógicos y las representaciones textuales de números escritos directamente en la lista de argumentos.
Si el argumento es una matriz o una referencia, solo se usarán los números contenidos en la matriz o en la referencia. Se ignorarán las celdas vacías, los valores lógicos o el texto contenidos en la matriz o en la referencia.
Si el argumento no contiene números, MAX devuelve 0 (cero).

La función MIN

La función MIN devuelve el valor mínimo de un conjunto de valores.

Sintaxis

=MIN(número1; número2,…)

La sintaxis de la función MIN tiene los siguientes argumentos:

número1 Obligatorio. El primer número, referencia de celda o rango para el cual desea el valor mínimo.
número2 Opcional. Números, referencias de celda o rangos adicionales para los que desea el valor mínimo, hasta un máximo de 255.

Observaciones

Los argumentos pueden ser números o nombres, matrices o referencias que contengan números.
Se tienen en cuenta los valores lógicos y las representaciones textuales de números escritos directamente en la lista de argumentos.
Si el argumento es una matriz o una referencia, solo se usarán los números contenidos en la matriz o en la referencia. Se ignorarán las celdas vacías, los valores lógicos o el texto contenidos en la matriz o en la referencia.
Si los argumentos no contienen números, MIN devuelve 0.

La función PROMEDIO

La función PROMEDIO devuelve el promedio (media aritmética) de los argumentos.

Sintaxis

=PROMEDIO(número1; número2,…)

La sintaxis de la función PROMEDIO tiene los siguientes argumentos:

número1 Obligatorio. El primer número, referencia de celda o rango para el cual desea el promedio.
número2 Opcional. Números, referencias de celda o rangos adicionales para los que desea el promedio, hasta un máximo de 255.

Observaciones

Los argumentos pueden ser números o nombres, rangos o referencias de celda que contengan números.
No se tienen en cuenta los valores lógicos y las representaciones textuales de los números que escriba directamente en la lista de argumentos.
Si el argumento de un rango o celda de referencia contiene texto, valores lógicos o celdas vacías, estos valores se pasan por alto; sin embargo, se incluirán las celdas con el valor cero.
Los argumentos que sean valores de error o texto que no se pueda traducir a números provocan errores.

La función MODA

La función MODA el valor que se repite o se produce con más frecuencia en una matriz o un rango de datos.

Sintaxis

=MODA(número1; número2,…)

La sintaxis de la función MODA tiene los siguientes argumentos:

número1 Obligatorio. Es el primer argumento numérico para el que desea calcular la moda.
número2 Opcional. De 2 a 255 argumentos numéricos cuya moda se desea calcular.

Observaciones

Los argumentos pueden ser números o nombres, matrices o referencias que contengan números.
Si el argumento de matriz o referencia contiene texto, valores lógicos o celdas vacías, estos valores se ignoran; sin embargo, se incluyen las celdas con el valor cero.
Los argumentos que son valores de error o texto que no se pueden traducir a números provocan errores.
Si el conjunto de datos no contiene puntos de datos duplicados, MODA devuelve el valor de error #N/A.

La función MEDIANA

La función MEDIANA devuelve la mediana de los números dados. La mediana es el número que se encuentra en medio de un conjunto de números.

Sintaxis

=MEDIANA(número1; número2,…)

La sintaxis de la función MEDIANA tiene los siguientes argumentos:

número1 Obligatorio. Es el primer argumento numérico para el que desea calcular la mediana.
número2 Opcional. De 2 a 255 argumentos numéricos cuya mediana se desea calcular.

Observaciones

Si la cantidad de números en el conjunto es par, MEDIANA calcula el promedio de los números centrales.
Los argumentos pueden ser números o nombres, matrices o referencias que contengan números.

La función K.ESIMO.MAYOR

La función K.ESIMO.MAYOR devuelve el k-ésimo mayor valor de un conjunto de datos.

Sintaxis

=K.ESIMO.MAYOR(matriz;k)

La sintaxis de la función K.ESIMO.MAYOR tiene los siguientes argumentos:

matriz Obligatorio. Es la matriz o rango de datos cuyo k-ésimo mayor valor desea determinar.
k Obligatorio. Representa la posición, dentro de la matriz o rango de celdas, de los datos que se van a devolver.

Observaciones

Si la matriz está vacía, devuelve la #NUM! #¡VALOR!
Si k ≤ 0 o si k es mayor que el número de puntos de datos, devuelve la #NUM! #¡VALOR!

La función K.ESIMO.MENOR

La función K.ESIMO.MENOR devuelve el k-ésimo mayor valor de un conjunto de datos.

Sintaxis

=K.ESIMO.MENOR(matriz;k)

La sintaxis de la función K.ESIMO.MENOR tiene los siguientes argumentos:

matriz Obligatorio. Es la matriz o rango de datos cuyo k-ésimo menor valor desea determinar.
k Obligatorio. Representa la posición, dentro de la matriz o rango de celdas, de los datos que se van a devolver.

Observaciones

Si la matriz está vacía, devuelve la #NUM! #¡VALOR!
Si k ≤ 0 o si k es mayor que el número de puntos de datos, devuelve la #NUM! #¡VALOR!

Video explicativo:

A continuación veremos un video en el que utilizaremos las diferentes funciones estadísticas en un ejercicio práctico.

Para suscribirte a mi canal de YouTube:

Función PRONOSTICO.LINEAL en Excel

La función PRONOSTICO.LINEAL en Excel es una función estadística que se utiliza para calcular o anticipar un valor futuro usando valores existentes.

Por tanto, toma los valores existentes (valores x y valores y conocidos) y pronostica el valor futuro de y para un valor x dado. Realiza esa estimación mediante regresión lineal.

Esta función puede ser muy útil por ejemplo para predecir las ventas futuras o gestionar un inventario.

En Excel 2016, la función PRONOSTICO se reemplazó con PRONOSTICO. LINEAL, siendo la sintaxis y el uso de las dos funciones iguales, aunque la función PRONOSTICO quedará obsoleta.

Sintaxis de la Función PRONOSTICO.LINEAL en Excel

PRONOSTICO.LINEAL(x;conocido_y;conocido_x)

x (obligatorio): Es el punto de datos cuyo valor se desea predecir.
conocido_y (obligatorio): Es la matriz o rango de datos dependientes.
conocido_x (obligatorio): Es la matriz o rango de datos independientes.

Ejemplo de la función PRONOSTICO.LINEAL

Veamos un ejemplo en el que tenemos las ventas correspondientes a los 9 primeros meses del año.

Deseamos pronosticar las ventas de los siguientes meses.

Para ello nos situamos encima de la celda correspondiente al mes 10 y le indicamos la fórmula =PRONOSTICO.LINEAL(A13;B4:B12;A4:A12)

Realizando esto mismo para los meses 11 y 12 obtendremos las siguientes ventas estimadas para los todos meses del año, siendo los meses señalados en color amarillo valores estimados.

Si le incluimos al gráfico una linea de tendencia veremos que los valores estimados coinciden para los meses que se han estimado.

Video explicativo:

Suscríbete a mi canal de YouTube:

Suscríbete

Calcular la TIR de una inversión

La tasa interna de retorno (TIR) es una forma de calcular el rendimiento de una inversión. Esto quiere decir que, es una herramienta excelente para entender la rentabilidad de las inversiones, permitiéndonos comparar diferentes inversiones y proyectos, con distintos períodos de tiempo y tipos de interés.

La tasa interna de retorno se expresa como un porcentaje. Es la rentabilidad que ofrece una inversión. Su cálculo indica cuál sería el rendimiento anual de una inversión si se compusiera continuamente a una tasa específica.

Sus resultados se pueden interpretar de forma simple, a mayor TIR, se estima mayor rentabilidad en el proyecto de inversión. Si comparamos varios proyectos a priori debemos decantarnos por el que tenga mayor TIR.

Sin embargo eso no significa que se deba decidir invertir en proyectos con una rentabilidad positiva. Tendríamos que compararlo con la tasa mínima aceptable para realizar una inversión, que sería la tasa de rentabilidad libre de riesgo, coste de oportunidad, o con el tipo de interés que se aplicará a la financiación de un proyecto.

Si la TIR supera la tasa de rentabilidad libre de riesgo o coste de oportunidad podríamos plantearnos invertir en el proyecto.
Si necesitamos financiación externa para invertir, la TIR deberá ser superior en todo caso al tipo de interés de la financiación.

Relación entre VAN y TIR

La fórmula de la TIR está estrechamente relacionada con la fórmula del VAN, dado que la TIR es tasa de descuento con la que el valor actual neto (VAN) se iguala a cero.

La fórmula del VAN era la siguiente:

Donde indicábamos una tasa de descuento y la variable que calculábamos era el VAN.

En cambio, cuando calculamos la TIR igualamos el valor del VAN a cero y la variable a estimar el el tipo de descuento.

Fórmula de la TIR

Despejando el valor de r, obtenemos la TIR del proyecto.

Cálculo de la TIR en Excel

En Excel tenemos varias funciones que nos sirven para realizar el cálculo de la TIR.

La función TIR en Excel

Devuelve la tasa interna de retorno para un flujo de caja periódico.

La tasa interna de retorno equivale a la tasa de interés producida por un proyecto de inversión con pagos (valores negativos) e ingresos (valores positivos) que se producen en períodos regulares.

Sintaxis de la función TIR

=TIR(valores; [estimar])

Valores (Obligatorio): Es una matriz o una referencia a celdas que contienen los números para los cuales desea calcular la tasa interna de retorno.

Estimar (Opcional): Es un número que el usuario estima que se aproximará al resultado de TIR.

El argumento de valores debe contener al menos un valor positivo y uno negativo para calcular la tasa interna de retorno.

TIR interpreta el orden de los flujos de caja siguiendo el orden del argumento de valores, por lo que debemos asegurarnos de escribir los valores del flujo de caja en el orden correcto.

Ejercicio de cálculo de la TIR con la función TIR

En la siguiente imagen vemos en las columnas A y B los flujos derivados de un proyecto de inversión. La inversión inicial es de 75.000 euros y ello originará flujos de caja durante los siguientes 6 años.

Si realizamos el cálculo del VAN con la función VNA vemos que con una tasa de descuento del 10% obtenemos un VAN de -12.007,27 euros.

En cambio si utilizamos una tasa de descuento del 4% los ofrece un VAN de 820,41%.

Eso significa que la rentabilidad real de este proyecto debe ser un porcentaje algo superior al 4%. Podríamos realizar cálculos hasta obtener un VAN de 0, pero sería más rápido y sencillo calcular la TIR con la función TIR de Excel.

En la celda F8 utilizamos la función TIR con los flujos indicados y nos devuelve una TIR de 4,315965%. Esa es la rentabilidad real del proyecto.

Vamos a comprobar que si utilizamos esa tasa en la fórmula del VAN obtenemos un valor actual neto de cero.

Efectivamente comprobamos que es así, por lo que la TIR se ha calculado correctamente.

La función TIR.NO.PER en Excel

Devuelve la tasa interna de retorno para un flujo de caja que no es necesariamente periódico.

Sintaxis de la función TIR

=TIR(valores;fechas;[estimar])

Valores (Obligatorio): Es una matriz o una referencia a celdas que contienen los números para los cuales desea calcular la tasa interna de retorno.

Fechas (Obligatorio): Es el calendario de fechas de flujo de caja. Las fechas pueden producirse en cualquier orden.

Estimar (Opcional): Es un número que el usuario estima que se aproximará al resultado de TIR.NO.PER

El argumento de valores debe contener al menos un valor positivo y uno negativo para calcular la tasa interna de retorno.

Ejercicio de cálculo de la TIR con la función TIR.NO.PER

En la realidad, cuando evaluamos la idoneidad de invertir en un proyecto, los flujos no suelen ser periódicos, sino que se generarán en determinadas fechas. Lo normal es que tengamos que utilizar la función TIR.NO.PER para calcular la rentabilidad de un proyecto.

En el siguiente ejercicio tenemos un ejemplo de proyecto de inversión con flujos no periódicos.

Para calcular el VAN en este caso debemos utilizar la función VAN.NO.PER.

Por el mismo motivo, para calcular la TIR tendremos que utilizar la función TIR.NO.PER.

Obtenemos una TIR de este proyecto del 5.907066%.

De nuevo vamos a realizar la prueba de calcular el valor actual neto con esa tasa y veremos que obtenemos un VAN de cero.

La función TIRM en Excel

Devuelve la tasa interna de retorno modificada para una serie de flujos de caja periódicos. TIRM toma en cuenta el costo de la inversión y el interés obtenido por la reinversión del dinero.

Sintaxis de la función TIRM

=TIR(valores;tasa_financiación;tasa_reinversión)

Valores (Obligatorio): Es una matriz o una referencia a celdas que contienen los números para los cuales desea calcular la tasa interna de retorno.

Tasa_financiamiento (Obligatorio): Es la tasa de interés que se paga por el dinero usado en los flujos de caja. Es la tasa que se aplica a los flujos de caja negativos, es decir equivale al costo de financiamiento de los flujos negativos.

Tasa_reinversión (Obligatorio): Es la tasa de interés obtenida por los flujos de caja a medida que se reinvierten. Es la tasa que se aplica a los flujos de caja positivos.

Ejercicio de cálculo de la TIR con la función TIRM

Cuando se calcula una tasa interna de retorno se considera que los flujos negativos y positivos de caja se financian y reinvierten al mismo porcentaje de la TIR.

Eso lo vemos en el siguiente pantallazo. Si establezco una tasa de financiamiento y de reinversión igual al valor de la TIR, el resultado obtenido con la función TIRM es es igual a obtenida con TIR.

Pero esto no es lo que suele ocurrir en la realidad.

Generalmente, cuando necesitamos financiación para pagar los flujos negativos, por ejemplo con un préstamo bancario, el tipo de interés exigido será el acordado con el banco.

En cuanto a los flujos positivos, podremos utilizarlo para invertir en otro proyecto, con otra rentabilidad, dejarlo en una cuenta bancaria remunerada, invertir en renta fija,…

Por ello, debemos contar con una tasa de financiamiento (aplicable a los flujos negativos) y una tasa de reinversión (aplicable a los flujos positivos). Nosotros vamos a considerar que la tasa de financiamiento es del 7%, mientras que la de reinversión será del 2%.

Vemos que en ese supuesto, la TIR va a ser menor.

Video explicativo:

Para suscribirte a mi canal de YouTube:

Suscríbete