Guía práctica de funciones de Excel

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é únicamente 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? Lee el artículo hasta el final y te enterarás:

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 a introducir:

formatos de texto excel

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.

concatenar textos excel

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).

eliminar espacios excel

 

 

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.

sustituir excel caracteres letras

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.

convertir texto en valor

 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.

textos iguales comparar excel

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.

cambiar formato excel

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.

extraer excel caracteres

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.

funciones logicas excel

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.

funcion si ejemplos excel

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.

corregir formulas errores excel

 

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.

sumas excel condicionales

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.

funcion sumaproducto excel

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

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 algunos ejemplos:

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 excel estadísticas

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.

funciones contar celdas excel

¿Quieres algún ejemplo más?

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 BUSCARV

¿Quién no ha oido 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

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:

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.

ejemplo funcion indirecto

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:

 

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:

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:

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á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:

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:

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… 🙂

¡Compartir es vivir!
Sergio
 

La destreza y el perfeccionismo quizá sean las dos virtudes que me permiten ayudar a mis clientes a facilitar las tareas administrativas de sus negocios.

Haz clic aquí para dejar un comentario 0 comentarios

Escribe una respuesta:

Powered by WishList Member - Membership Software