Supongo que ya sabes que Excel cuenta con cientos de funciones

con las que puedes analizar, auditar y hacer un sinfín de cálculos

diferentes. Muchas de estas funciones de Excel las utilizamos a

diario en nuestros empleos y otras están diseñadas solo para

pequeños grupos (financieros, estadísticos, ingenieros) que realizan

tareas muy específicas.

En esta guía no trataré todas las funciones de Excel. Dejaré a un

lado las menos utilizadas. y me ceñiré a las más usadas y más útiles.

Para más información sobre todas las funciones de hoja que tiene

Excel te invito a que visites la lista completa en la web de Microsoft.

¿Todavía no sabes qué pueden hacer por ti las funciones de Excel?

En ese caso, lee el artículo hasta el final y podrás comenzar a hacer

tus propios cálculos:

Funciones de texto

Gracias a las funciones de texto puedes manipular celdas que

contienen cadenas de texto. Estas funciones son algunas de las más

importantes:

Función TEXTO

Se utiliza para convertir un número o una fecha en una cadena de

texto en un formato específico. Uno de los usos que yo le doy con

más frecuencia es para la creación de títulos donde han de

intervenir fechas dinámicas. Al concatenar una fecha con un texto,

la fecha siempre pierde el formato mostrándose como número de

serie. Con TEXTO se puede evitar esto.

=TEXTO(valor;formato)

• valor: Es el valor numérico que quieres convertir en texto. • formato: Es la máscara de entrada que corresponde al

formato que le quieres dar al texto. Estos son los códigos para introducir:

En la imagen puedes ver unos pocos ejemplos de formatos que se

pueden crear.

Función CONCATENAR

Permite combinar el contenido de dos o más celdas (hasta 255) en

una sola. También puedes utilizar el operador de concatenación “&“.

Yo personalmente utilizo CONCATENAR porque le da a la fórmula

un aspecto más “ordenado”.

=CONCATENAR(texto1,[texto2],[…])

• Texto1, texto2…: Son las cadenas de texto que se van a unir.

Sólo es obligatorio el primer argumento, admitiéndose hasta 255.

Función ESPACIOS

Al extraer información de bases de datos externas u otros

programas, a veces se suelen agregar espacios delante o detrás de

los datos. La función ESPACIOS permite eliminar estos espacios

innecesarios, dejando únicamente los propios entre palabras.

=ESPACIOS(texto)

• texto: Es el texto del que quieres eliminar el exceso de

espacios. Normalmente se suelen introducir referencias a celdas que contienen el texto, pero también puedes utilizar directamente cadenas de texto (entre comillas).

Función SUSTITUIR

Permite sustituir un texto por otro en una cadena de caracteres.

Ten cuidado al utilizarla, ya que diferencia entre mayúsculas y

minúsculas.

En caso de que en vez de sustituir un texto por otro, quieras hacer

una sustitución según la posición del texto, debes

utilizar REEMPLAZAR.

La verdad es que apenas he utilizado esta función. Cuando tengo

que hacer alguna sustitución de texto, utilizo la herramienta Buscar

y Reemplazar (Ctrl + L).

=SUSTITUIR(texto;texto_original;texto_nuevo;num_repeticion)

• Texto: Es el texto o la referencia a la celda donde se encuentra el texto a sustituir.

• Texto_original: Es la cadena de texto que se reemplazará. • Texto_nuevo: Es el texto por el que se va a reemplazar el

texto_original. • Num_repeticion: Es el número de la repetición que se

sustituirá. Si escribes un 1, se reemplazará solamente el texto la primera vez que se encuentre. Si escribes un 2, se reemplazará la segunda, etc. En caso de dejar el argumento en blanco, se sustituirán todas las repeticiones que se encuentren en el texto.

Función VALOR

Ya has visto que TEXTO convierte números en texto. Pues VALOR

hace lo opuesto: intenta convertir texto en números. Digo “intenta”

porque no siempre se reconocen los formatos de texto.

=VALOR(texto)

• Texto: Es el texto que quieres convertir en número.

Te pongo el mismo ejemplo que con TEXTO. Cuando extraes datos

externos de otros programas, a veces los valores numéricos tienen

formato de texto, de forma que no se pueden hacer operaciones

matemáticas con ellos. La función VALOR soluciona este problema.

Función IGUAL

Permite hacer comparaciones simples de dos valores. Aunque yo

prefiero utilizar directamente el signo igual (=3=2, que devuelve

FALSO), IGUAL parece que le da un aspecto más “ordenado a la

fórmula donde la utilices.

Dos de las situaciones en las que te será realmente útil es al utilizar

formatos condicionales y validación de datos. Si no coinciden dos

valores (si no son iguales), la condición no se cumplirá o no

permitirá introducir un dato.

=IGUAL(texto1;texto2)

• Texto1: Es el primer valor a comparar. • Texto2: Es el segundo valor a comparar. Tanto éste como

texto1 pueden aceptar texto, valores lógicos y datos numéricos.

Ten cuidado: IGUAL distingue entre mayúsculas y minúsculas.

Funciones para cambiar el formato del texto

Excel contiene tres funciones que son muy útiles para cambiar la

apariencia del texto de las celdas:

• MAYUSC (texto): Transforma todos los caracteres del texto en letras mayúsculas.

• MINUSC(texto): Transforma todos los caracteres el texto en letras minúsculas.

• NOMPROPIO(texto): Convierte en mayúscula el primer carácter de cada palabra, y en minúscula el resto.

En las tres funciones, el argumento texto puede ser una cadena de

caracteres entre comillas, una referencia a una celda que contenga

texto o una fórmula que devuelva un texto.

Funciones para extraer caracteres del texto

Si necesitas fórmulas para extraer de un texto, un número

determinado de caracteres, aquí tienes las tres funciones más

utilizadas:

• IZQUIERDA(texto;[núm_caracteres]): Se utiliza para devolver un número concreto de caracteres desde el comienzo de la cadena de texto.

• DERECHA(texto;[núm_caracteres]): Devuelve un número concreto de caracteres desde el final de la cadena de texto hacia atrás.

• EXTRAE(texto;núm_inicial;núm_caracteres): Devuelve un número concreto de caracteres desde la posición indicada en núm_inicial.

En estas funciones el argumento texto indica el texto sobre el que

se va a trabajar y núm_caracteres es el número de caracteres a

devolver desde la izquierda, derecha o núm_inicial.

Funciones lógicas

Para establecer condiciones lógicas puedes utilizar estas funciones,

que proporcionan toda la flexibilidad que necesitas.

Funciones Y, O y XO

• Y(valor_lógico1;[valor_lógico2];…): Devuelve VERDADERO si todos los argumentos de la función se cumplen.

• O(valor_lógico1;[valor_lógico2];…): Devuelve VERDADERO si uno o varios argumentos de la función se cumplen.

• XO(valor_lógico1;[valor_lógico2];…): Devuelve un Exclusivo lógico de todos los argumentos.

Función NO

La función NO se utiliza para negar el resultado lógico de una

fórmula, es decir, si la fórmula de la celda devuelve VERDADERO, la

función NO hará que sea FALSO y viceversa.

Por ejemplo, estas dos fórmulas devuelven FALSO:

=NO(VERDADERO)

=NO(2*2=4)

Función SI

Es la función lógica más conocida y versátil. A veces se la conoce

como “función condicional” por su capacidad de devolver un valor

según una condición que se especifique:

=SI(condicion;valor_si_verdadero;[valor_si_falso])

La función SI comprueba los criterios expresados en el

argumento condicion, y devuelve un valor (valor_si_verdadero) si

se cumplen y otro valor (valor_si_falso) si no se cumplen.

Por ejemplo, la fórmula =SI(A1<>””;”Bueno”;”Malo”), devuelve el

valor “Bueno” si la celda A1 no está vacía. Si A1 se encuentra vacía,

devuelve “Malo”.

Un ejemplo algo más complejo: La

fórmula =SI(F4>9;”¡Perfecto”;SI(F4>6;”Bien”;SI(F4>3;”Mal”;”Muy

mal”))) devolverá “¡Perfecto!” si F4 contiene un número mayor que

9, “Bien” si el valor se encuentra entre 6 y 9, “Mal”, si se encuentra

entre 3 y 6 y “Muy mal”, si es menor de 3.

Función SI.ERROR

SI.ERROR es una función muy útil que se utiliza para manejar los

posibles errores que se pueden dar en una fórmula.

=SI.ERROR(formula;valor_si_error)

Comprueba si la fórmula da como resultado un error y, si lo hace,

devuelve un valor en su lugar. Es capaz de identificar los siete

errores que se pueden dar en la fórmula.

Funciones matemáticas

Excel también cuenta con muchísimas funciones básicas y

avanzadas para realizar cálculos matemáticos, desde simples

sumas y restas hasta operaciones logarítmicas, factoriales y

similares.

Algunas de las operaciones más básicas pueden ser:

Funciones para sumar datos

Sumar es la operación matemática que más se utiliza. Excel cuenta

con varias funciones que te ayudarán en esto:

Función SUMA

Esta función devuelve la suma de los argumentos introducidos.

Estos argumentos pueden ser números, referencias de celdas o

resultados de fórmulas que devuelven números

=SUMA(número1;[número2];…)

En esta sintaxis sólo es obligatorio el primero de los argumentos.

Los demás, hasta 255, puedes introducirlos o no.

En la fórmula =SUMA(A4;B6;3), se suman los valores de las celdas

A4 y B6 y, a continuación, se suma 3.

Esta es la forma de suma más sencilla, pero hay otras más completas

y complejas.

Funciones SUMAR.SI y SUMAR.SI.CONJUNTO

Ambas funciones suman las celdas de un rango específico, siempre

que los valores cumplan ciertas condiciones.

La diferencia entre las funciones es que SUMAR.SI sólo puede

evaluar una condición mientras que SUMAR.SI.CONJUNTO, puede

hacerlo hasta con 127. Será complicado que llegues a exceder este

número..

=SUMAR.SI(rango,criterio,[rango_suma])

=SUMAR.SI.CONJUNTO(rango_suma;rango_criterios_1;criterio_1

;[rango_criterios_2];[criterio_2];…)

Como ves, las dos funciones tienen los mismos tipos de argumentos

pero su sintaxis es ligeramente diferente:

• rango_suma: es el rango de celdas donde se encuentran los valores a sumar.

• rango y rango_criterios: es el rango de celdas que contiene el criterio a evaluar.

• criterio y criterio_1: es la condición que debe ser evaluada.

Función SUMAPRODUCTO

SUMAPRODUCTO es una función un tanto especial porque es de

las pocas que trabaja con matrices. Su misión es la de multiplicar los

componentes de dos rangos de datos y sumar su resultado (dos

acciones en una).

Uno de los usos que le suelo dar es el de multiplicar cantidades por

precios en un listado de productos, para hallar el importe total.

• Reto 04: Sumar las horas de un calendario de turnos

Funciones para generar números aleatorios

Excel tiene dos funciones para generar números aleatorios. A

simple vista parece que no tienen mucha utilidad, pero sirven para

muchas actividades: asignar personas a grupos de forma aleatoria,

ordenar datos aleatoriamente o crear estudios de probabilidades.

• ALEATORIO(): Genera un número aleatorio entre 0 y 1. • ALEATORIO.ENTRE(valor_menor;valor_mayor): Genera un

número aleatorio entre los límites dados.

Mira estos ejemplos

• Cómo generar valores aleatorios con decimales entre dos números

• Números aleatorios estáticos • Función ALEATORIO excluyendo rangos

Funciones de redondeo

¿Tienes que hacer algún tipo de redondeo a un número? Sin

problemas, Excel cuenta con un sinfín de funciones que permiten

crear cualquier redondeo imaginable:

• REDONDEAR: Redondea un número al número de decimales especificado.

• REDONDEAR.MAS: Redondea un número hacia arriba, en dirección contraria a cero.

• REDONDEAR.MENOS: Redondea el número hacia abajo, en dirección hacia cero.

• REDOND.MULT: Devuelve un número redondeado al múltiplo deseado.

• MULTIPLO.INFERIOR: Redondea un número hacia abajo, hasta el múltiplo significativo.

• MULTIPLO.SUPERIOR: Redondea un número hacia arriba, hasta el múltiplo significativo.

• ENTERO: Redondea un número hacia abajo hasta el entero más próximo.

• TRUNCAR: Trunca un número a un entero. • REDONDEA.PAR: Redondea un número hasta el entero par

más próximo, en sentido contrario a cero. • REDONDEA.IMPAR: Redondea un número hasta el entero

impar más próximo, en sentido contrario a cero.

Te muestro un ejemplo:

• 7 ejemplos con funciones REDONDEAR

Funciones estadísticas

La categoría estadística cuenta con muchas funciones para realizar

todo tipo de cálculos. Seguro que en alguna ocasión has tenido que

contar un rango de celdas o hallar el valor más alto de un grupo de

números… Con Excel es posible realizar estudios estadísticos

complejos. A una escala más sencilla, esto es lo que podrías llegar a

necesitar:

Funciones para encontrar valores más altos, más bajos y promedios

• =MIN(numero_1;numero_2;…): Devuelve el valor mínimo de una lista de valores omitiendo los valores lógicos y los de texto.

• =MAX(número1,[número2],[…]): Devuelve el valor más alto de una lista de valores omitiendo los valores lógicos y los de texto.

• =PROMEDIO(número1;[número2];[…]): Devuelve la media aritmética de todos sus argumentos (hasta 255).

• =K.ESIMO.MAYOR(matriz;k): Devuelve el número que se encuentra en la n-ava posición cuando los valores se encuentran ordenados de mayor a menor.

• =K.ESIMO.MENOR(matriz;k): Devuelve el número que se encuentra en la n-ava posición cuando los valores se encuentran ordenados de menor a mayor.

Funciones para contar celdas

Excel nos ayuda a contar celdas con estas funciones:

• =CONTAR(valor1;[valor2];[…]): Devuelve la cantidad de celdas que contienen números o fechas.

• =CONTARA(valor_1;valor_2;…): Cuenta el número de celdas no vacías de uno o varios rangos de datos.

• =CONTAR.BLANCO(rango): Cuenta las celdas que están en blanco.

• =CONTAR.SI(rango;criterios): Cuenta el número de celdas que cumplen con los criterios de una condición.

• =CONTAR.SI.CONJUNTO(rango_criterio1;criterio1;[rango_criterio2;criterio2];[…]): Cuenta el número de celdas que contienen valores según uno o varios criterios.

¿Quieres algún ejemplo más?

• Cómo contar celdas que NO contienen números • Contar el número de facturas emitidas en un año

determinado

Funciones de búsqueda y referencia

Una de las tareas que se hacen con más frecuencia es la de buscar

un dato. Las funciones de búsqueda y referencia son realmente

útiles cuando necesitas buscar información o quieres mostrar

referencias de celdas

Función BUSCARX

Desde su publicación se ha convertido sin duda en la nueva reina de

las funciones de búsqueda, destronando a BUSCARV. Es mil veces

mejor. Úsala cuando:

• No te quieras complicar la vida haciendo búsquedas de datos hacia la izquierda.

• Preveas que se van a mover o eliminar columnas en el futuro (BUSCARX mantiene las referencias).

• Prefieres hacer búsquedas de datos de abajo a arriba. • Buscas varios valores.

Si te parecen pocas ventajas con respecto a BUSCARV te diré que

incluso ¡es más fácil de usar! Mira su sintaxis:

=BUSCARX(valor_buscado;matriz_buscada;matriz_devuelta;[si_n

o_se_encuentra];[modo_de_coincidencia];[modo_de_búsqueda])

Ejemplos con BUSCARX

• Por qué la función BUSCARX está revolucionando las búsquedas

Función BUSCARV

¿Quién no ha oído hablar de BUSCARV o la utiliza todos los días? Es

la protagonista de las funciones de búsqueda y la más utilizada por

los que trabajamos con Excel. BUSCARV te permite buscar un valor

en la primera columna de un rango y devuelve el dato de la misma

fila que se encuentra n columnas hacia la derecha.

=BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas

;[ordenado])

• valor_buscado: (obligatorio) Es el valor que quieres buscar. • matriz_buscar_en: (obligatorio) El rango de datos o tabla

donde se buscará el valor_buscado.

• indicador_columnas: (obligatorio) El número de columna en la tabla en la que se encuentra el valor devuelto.

• ordenado: (opcional) El valor lógico que indica si BUSCARV debe buscar una coincidencia exacta o aproximada.

Ejemplos con BUSCARV

• 6 trucos para optimizar BUSCARV (todavía más) • ¿BUSCARV hacia la izquierda? • Cómo utilizar comodines en la función BUSCARV • Dos sustitutas para BUSCARV

Función INDICE

INDICE es una de las pocas funciones de Excel que acepta dos

sintaxis: una de referencia y otra matricial. Ambas sintaxis tienen el

mismo objetivo: encontrar un valor en una matriz o rango de celdas,

conociendo solo un número de columna y de fila.

Esta función, junto con COINCIDIR, son las sustitutas perfectas

para BUSCARV, que además tienen una ventaja: la posibilidad de

hacer búsquedas hacia la izquierda.

Función COINCIDIR

La función COINCIDIR devuelve la posición relativa de un elemento

en una matriz que coincida con un valor específico en un

determinado orden. Esto significa que, dentro de un rango de

celdas, es posible conocer la posición exacta del valor buscado. Esto

es realmente útil, por ejemplo, para construir fórmulas complejas

relacionadas con datos que pueden variar de posición (dinámicos).

Échale un vistazo a estos ejemplos de uso:

• Reto 01: ¿Cuál es el producto más vendido? • Cómo debes utilizar los nombres de rango para simplificar

tus megafórmulas

Función INDIRECTO

¿El dato que estás buscando puede encontrarse en un rango de

celdas o en otro? INDIRECTO te ayuda a construir referencias de

celdas dinámicas para que hagas la búsqueda en el sitio correcto.

Particularmente yo la suelo utilizar para crear referencias a otras

hojas del libro de Excel.

Función DESREF

DESREF es de mis funciones de referencia favoritas. Desde una

referencia de celda dada, permite desplazarte un número de filas y

un número de columnas, para devolver el valor que se encuentre en

esa celda.

Normalmente suele utilizarse en combinación contras funciones

como INDICE o INDIRECTO.

Función TRANSPONER

TRANSPONER te permite cambiar las filas por columnas y las

columnas por filas, modificando así la posición de los datos. Se trata

de una función matricial.

La sintaxis es muy sencilla, simplemente hay que aplicar la matriz

que se desea transponer. Pero previamente a introducir la fórmula,

debes seleccionar el rango de celdas de destino (ya transpuesto)

donde quieres pegar los datos.

En este artículo te hablo más a fondo de esta función:

• Las fórmulas matriciales en Excel

La verdadera potencia de las funciones de búsqueda y referencia se

encuentra en combinarlas adecuadamente.

Funciones de fecha y hora

Las operaciones con fechas y horas suelen ser también bastante

habituales (tareas como sumar o restar días o tiempos, cálculos con

días laborables o hallar fechas en función de determinados

parámetros).

Algo importante que debes saber es que el formato de las celdas

que contienen fechas y horas juega un papel muy importante. A

veces un simple cambio de formato te da la respuesta que estabas

buscando. Esto es porque Excel almacena las fechas y las horas

como números de serie. Por ejemplo, el 02/06/2018 a las 15:37,

Excel lo guarda como 43253,65069 (43253 son los días

transcurridos desde el 01/01/1900 hasta el 02/006/18 y 0,65069

es la fracción del día transcurrido desde las 0:00 hasta las 15:37).

Excel trabaja de esta manera para poder realizar cálculos con este

tipo de datos de forma más eficiente.

Funciones para crear fechas

• FECHA: Para crear fechas recopilando los diferentes componentes (día, mes y año).

• FECHA.MES: Devuelve una fecha según un número de meses antes o después.

• FECHANUMERO: Convierte una cadena de texto en una fecha.

Mira estos artículos:

• Cálculos con fechas y horas en Excel • En profundidad: Guía de uso de fechas horas en Excel

Fecha y hora actuales

En ocasiones puede que necesites conocer la hora y la fecha

actuales para hacer algún cálculo de vencimientos o de control

horario.

• HOY(): Devuelve la fecha actual • AHORA(): Devuelve la fecha y hora actuales. Modificando el

formato puedes mostrar únicamente la hora.

Otra forma de conocer la fecha actual:

• Atajo de teclado: Introducir la fecha actual

Extracción de fechas y componentes de fecha

Dada una fecha, es posible obtener mucha información de forma

individual:

• DIA: Devuelve el número del día de la fecha especificada. • MES: Devuelve el número del mes. • AÑO: Devuelve el año.

• FIN.MES: Devuelve el último día del mes, un número de meses antes o después de la fecha.

• DIASEM: Devuelve el día de la semana, según el parámetro especificado.

• NUM.DE.SEMANA: Devuelve el número de la semana del año en la que se encuentra la fecha.

Mira estos artículos:

• Cómo extraer datos de celdas que contienen fecha y hora + UDF

• Convertir los Timestamps de UNIX a fechas en un instante

Cálculo de diferencias entre fechas

• SIFECHA: Devuelve la diferencia entre dos fechas. • FRAC.AÑO: Calcula la fracción del año que hay entre dos

fechas

Mira este artículo:

• Diferencia en meses entre dos fechas

Operaciones con días laborables

Para operaciones relacionadas con días laborables tienes a tu

disposición estas funciones:

• DIA.LAB: Calcula la fecha resultante de agregar o quitar un número de días laborables a una fecha inicial.

• DIAS.LAB: Calcula el número de días laborables entre dos fechas.

• DIA.LAB.INTL: Calcula la fecha resultante de agregar o quitar un número de días laborables a una fecha inicial, utilizando parámetros de fin de semana personalizados.

• DIAS.LAB.INTL: Calcula el número de días laborables entre dos fechas, utilizando parámetros de fin de semana personalizados

Echa un vistazo a este artículo:

• Cómo calcular las horas de trabajo entre dos fechas con jornadas diferentes

Funciones definidas por el usuario

Existe un último tipo de función que es el creado por el propio

usuario. Para crear una función personalizada debes tener un

mínimo conocimiento de VBA que te permita llegar a tu objetivo de

cálculo.

Si Excel no cuenta con la función que necesitas (lo dudo…) o no eres

capaz de combinar las funciones que ofrece para llegar al resultado

que necesitas (también lo dudo…), puedes crear tu propia función y

darle tu propio nombre.

Este es un ejemplo de una función personalizada que sirve para

mostrar el código del color del fondo de la celda:

Function ColorProper(celda As Range) As String

Dim C As Long

Dim R As Long

Dim G As Long

Dim B As Long

C = celda.Interior.Color

R = C Mod 256

G = C \ 256 Mod 256

B = C \ 65536 Mod 256

ColorProper = "R=" & R & ", G=" & G & ", B=" & B

End Function

Para utilizar la función sólo tienes que introducir la fórmula

=ColorProper(A1) para obtener el color de fondo de la celda A1.

Resumen

¿Cuáles de estas tareas realizas en tu trabajo? ¿Qué funciones usas?

Supongo que más de una, ya que te he mostrado las más comunes

en cualquier trabajo en que se utilice Excel.

Como has visto, el único límite que hay para hacer cualquier tipo de

cálculo es la imaginación de tu jefe… 🙂