Fórmulas útiles en Excel
Por una parte vamos a ver las funciones útiles más habituales de Excel, con una breve explicación acompañada de un ejemplo.
Por otra parte se mostrará funciones combinadas para obtener resultados específicos.
Funciones habituales en Excel
- Función SUMA: Esta función nos permite sumar una serie de valores que hayamos introducido en una columna o fila. La sintaxis es: =SUMA(rango de celdas). Por ejemplo, si tenemos una columna con los valores 5, 6, 7 y 8, podemos utilizar la función SUMA para obtener el resultado 26:
=SUMA(A1:A4) - Función PROMEDIO: Nos permite calcular el promedio de una serie de valores en una columna o fila. La sintaxis es: =PROMEDIO(rango de celdas). Por ejemplo, si tenemos una fila con los valores 2, 4, 6 y 8, podemos utilizar la función PROMEDIO para obtener el resultado 5:
=PROMEDIO(A1:D1) - Función MAX: Con esta función podemos encontrar el valor máximo dentro de una serie de datos. La sintaxis es: =MAX(rango de celdas). Por ejemplo, si tenemos una columna con los valores 10, 20, 30 y 40, podemos utilizar la función MAX para obtener el valor 40:
=MAX(A1:A4) - Función MIN: Al igual que la función MAX, esta función nos permite encontrar el valor mínimo dentro de una serie de datos. La sintaxis es: =MIN(rango de celdas). Por ejemplo, si tenemos una fila con los valores 3, 5, 2 y 8, podemos utilizar la función MIN para obtener el valor 2:
=MIN(A1:D1) - Función BUSCARV: Esta función nos permite buscar un valor en una tabla y devolver el valor correspondiente en otra columna de la misma fila. La sintaxis es: =BUSCARV(valor buscado, rango de la tabla, número de columna, [correspondencia exacta]). Por ejemplo, si tenemos una tabla con los valores "Manzana", "Naranja", "Pera" en la columna A, y los valores "Rojo", "Naranja", "Verde" en la columna B, podemos utilizar la función BUSCARV para buscar el valor "Naranja" en la columna A y obtener el valor correspondiente "Verde" en la columna B:
=BUSCARV("Naranja", A1:B3, 2, FALSO) - Función BUSCARH: Al igual que la función BUSCARV, esta función nos permite buscar un valor en una tabla y devolver el valor correspondiente en otra fila de la misma columna. La sintaxis es: =BUSCARH(valor buscado, rango de la tabla, número de fila, [correspondencia exacta]). Por ejemplo, si tenemos una tabla con los valores "Manzana", "Naranja", "Pera" en la fila 1, y los valores "Rojo", "Naranja", "Verde" en la fila 2, podemos utilizar la función BUSCARH para buscar el valor "Naranja" en la fila 1 y obtener el valor.
- Función CONCATENAR (continuación): …textoN). Por ejemplo, si tenemos las celdas A1, B1 y C1 con los valores "Juan", "Pérez" y "26 años", respectivamente, podemos utilizar la función CONCATENAR para unir los tres textos en una sola celda:
=CONCATENAR(A1, " ", B1, " tiene ", C1) - Función IZQUIERDA: Con esta función podemos extraer una cantidad determinada de caracteres desde el principio de un texto. La sintaxis es: =IZQUIERDA(texto, [número de caracteres]). Por ejemplo, si tenemos la celda A1 con el valor "Excel es una herramienta muy útil", podemos utilizar la función IZQUIERDA para extraer los primeros 5 caracteres y obtener el resultado "Excel":
=IZQUIERDA(A1, 5) - Función DERECHA: Al igual que la función IZQUIERDA, esta función nos permite extraer una cantidad determinada de caracteres desde el final de un texto. La sintaxis es: =DERECHA(texto, [número de caracteres]). Por ejemplo, si tenemos la celda A1 con el valor "Excel es una herramienta muy útil", podemos utilizar la función DERECHA para extraer los últimos 4 caracteres y obtener el resultado "útil":
=DERECHA(A1, 4) - Función LARGO: Esta función nos permite contar el número de caracteres de un texto. La sintaxis es: =LARGO(texto). Por ejemplo, si tenemos la celda A1 con el valor "Excel es una herramienta muy útil", podemos utilizar la función LARGO para contar el número de caracteres y obtener el resultado 33: =LARGO(A1).
- Función SI: Con esta función podemos establecer una condición y devolver un valor si se cumple dicha condición, y otro valor si no se cumple. La sintaxis es: =SI(condición, valor si verdadero, valor si falso). Por ejemplo, si tenemos la celda A1 con el valor 5, podemos utilizar la función SI para comprobar si es mayor que 3 y devolver el valor "Aprobado" si se cumple la condición, o "Suspenso" si no se cumple:
=SI(A1>3, "Aprobado", "Suspenso") - Función Y: Esta función nos permite comprobar si se cumplen varias condiciones a la vez. La sintaxis es: =Y(condición1, condición2, … condiciónN). Por ejemplo, si tenemos las celdas A1 y B1 con los valores 5 y 6, respectivamente, podemos utilizar la función Y para comprobar si ambos valores son mayores que 3:
=Y(A1>3, B1>3) - Función O: Al igual que la función Y, esta función nos permite comprobar si se cumple al menos una de varias condiciones. La sintaxis es: =O(condición1, condición2, … condiciónN). Por ejemplo, si tenemos las celdas A1 y B1 con los valores 2 y 6, respectivamente, podemos utilizar la función O para comprobar si al menos uno de los valores es mayor que 3:
=O(A1>3, B1>3) - Función SI.ERROR: Con esta función podemos establecer una condición y devolver un valor si se cumple dicha condición, y otro valor si no se cumple. Además, en caso de que ocurra un error en la fórmula, podemos definir un valor a devolver. La sintaxis es: =SI.ERROR(valor a comprobar, valor si error, valor si verdadero, valor si falso). Por ejemplo, si tenemos la celda A1 con el valor "10" y la celda B1 con el valor "0", podemos utilizar la función SI.ERROR para evitar un error de división por cero y devolver el valor "Error" en caso de que B1 sea igual a cero, o el resultado de la división si no lo es:
=SI.ERROR(A1/B1, "Error", A1/B1) - Función BUSCARV: Con esta función podemos buscar un valor en una tabla y devolver el valor correspondiente de otra columna de la misma fila. La sintaxis es: =BUSCARV(valor a buscar, tabla de búsqueda, número de columna, [correspondencia exacta]). Por ejemplo, si tenemos la tabla de búsqueda en las celdas A1:B3 con los valores "Juan", "Pérez", "María" y "García", respectivamente, y queremos buscar el apellido correspondiente al nombre "María", podemos utilizar la función BUSCARV de la siguiente manera, que en este caso, el resultado sería "García".:
=BUSCARV("María", A1:B3, 2, VERDADERO). - Función SUMA: Con esta función podemos sumar un rango de celdas. La sintaxis es: =SUMA(celda inicial:celda final). Por ejemplo, si queremos sumar los valores de las celdas A1:A3, podemos utilizar la función SUMA de la siguiente manera: =SUMA(A1:A3).
- Función PROMEDIO: Con esta función podemos calcular el promedio de un rango de celdas. La sintaxis es: =PROMEDIO(celda inicial:celda final). Por ejemplo, si queremos calcular el promedio de los valores de las celdas A1:A3, podemos utilizar la función PROMEDIO de la siguiente manera:
=PROMEDIO(A1:A3) - Función CONTAR: Con esta función podemos contar el número de celdas que contienen datos en un rango determinado. La sintaxis es: =CONTAR(rango). Por ejemplo, si queremos contar el número de celdas con datos en las celdas A1:A3, podemos utilizar la función CONTAR de la siguiente manera:
=CONTAR(A1:A3) - Función MAX: Con esta función podemos encontrar el valor máximo de un rango de celdas. La sintaxis es: =MAX(celda inicial:celda final). Por ejemplo, si queremos encontrar el valor máximo de las celdas A1:A3, podemos utilizar la función MAX de la siguiente manera:
=MAX(A1:A3) - Función MIN: Con esta función podemos encontrar el valor mínimo de un rango de celdas. La sintaxis es: =MIN(celda inicial:celda final). Por ejemplo, si queremos encontrar el valor mínimo de las celdas A1:A3, podemos utilizar la función MIN de la siguiente manera:
=MIN(A1:A3)
Funciones específicas en Excel
Para obtener resultados específicos hay que combinar distintas funciones dependiendo de las necesidades. Estructurando bien cada función, respetando posiciones de parámetros, paréntesis y por supuesto el orden secuencial, se obtienen resultados sorprendentes que de realizarlos a mano se vuelven tediosos e interminables.
Crear un slug para una URL
Dejar una url solamente con minúsculas, caracteres válidos y guiones en lugar de espacios. Se puede agregar cualquier carácter a sustituir para completar la fórmula.
=MINUSC(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(LIMPIAR(A1);":";"");" ";"-");"_";"-");"á";"a");"é";"e");"í";"i");"ó";"o");"ú";"u");"ü";"u");"Á";"A");"É";"E");"Í";"I");"Ó";"O");"Ú";"U");"Ü";"U");";";"-"))Y también al revés, para crear una metasdescripción de un slug o nombre de imagen.
=MINUSC(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(LIMPIAR(A2);":";"");"-";" ");"_";" ");"á";"a");"é";"e");"í";"i");"ó";"o");"ú";"u");"ü";"u");"Á";"A");"É";"E");"Í";"I");"Ó";"O");"Ú";"U");"Ü";"U");";";" "))Buscar y remplazar o añadir datos a partir de otras celdas
Buscamos el código C011711 en otra hoja para extraer la celda de su derecha que es 430011711
código - valor que queremos traer C011711 - 430011711
desde hoja del mismo libro
=SI(H2="";"";BUSCARV(H2;'NOMBRE_HOJA'!$A$2:$C$1180;2;FALSO))desde fichero
=SI(B2="";"";BUSCARV(B2;'[conversionCodigos.xlsx]CLIENTES PROVEEDORES'!$A$1:$B$10000;2;FALSO))
Desglose:
no copiar blancos para que no de el erro #REF - si es blanco pones blanco si no pon lo que hay en la casilla
SI(H2="";""se pone $ para que sean variables no fijas
$A$2:$C$1180 2 es el numero de la columna que se trae de la matriz
FALSO si ya va ordenado y TRUE si queremos ordenarlo
Buscar contenido dentro de una celda de un rango
=BUSCARV(""&A1&"";Hoja2!$B$1:$B$3;1;0)Cuando ejecutamos CONTAR.SI nos va a devolver el número de veces que cuenta el valor buscado, con la función SI indicamos que cuando el resultado sea verdadero (superior a 0) indique que contiene el valor buscado.
Esta fórmula permite comodines y en este caso son necesarios dado que para indicar que busque en todo el contenido de la celda debemos especificar los asteriscos al inicio y al final del valor, pero podríamos especificar que solo sea al inicio o al final. No distingue entre mayúsculas y minúsculas:
=SI(CONTAR.SI(A2;"Sig");"Contiene valor";"No lo contiene")Otra posibilidad es una variante de la primera fórmula pero usando la función ENCONTRAR. Tiene la limitación de no permitir comodines y distingue entre mayúsculas y minúsculas:
=SI(ESNUMERO(ENCONTRAR("Sig";A5));"Contiene valor";"No lo contiene")Por último, otra función que tampoco permite comodines y se debe respetar las mayúsculas y minúsculas, es la siguiente:
=SI(SUSTITUIR(A4;"Sig";"")<>A4=VERDADERO;"Contiene valor";"No lo contiene")Rellenar con ceros
Lo que hace la fórmula es primero determinar cuantos dígitos tiene tu número (usando la función LARGO), luego la resta para saber cuantos ceros tiene que poner, luego repite esa cantidad de ceros (función REPETIR), finalmente concatena, pega los ceros y luego el número (usando la función concatenar)
rellenar con 0 hasta un cierto numero, en este caso 5:
=REPETIR("0";5-LARGO(A2))
concatena lo obtenido para finalizar el resultado de longitud 5:
=CONCATENAR( REPETIR("0";5-LARGO(A2)); A2)Ejemplo para cuentas bancarias
=CONCATENAR(CONCATENAR(REPETIR("0";4-LARGO(P3));P3); CONCATENAR(REPETIR("0";4-LARGO(Q3));Q3); CONCATENAR(REPETIR("0";2-LARGO(R3));R3); CONCATENAR(REPETIR("0";10-LARGO(S3));S3))Ejemplo para cuentas contables
=CONCATENAR(C2; REPETIR("0";10-LARGO(C2)))
Calcular porcentaje de IVA
Para un desglose de IVA
=SI(ABS(REDONDEAR(K640/1,21;2))=ABS(REDONDEAR(K641;2));21;
SI(ABS(REDONDEAR(K640/1,10;2))=ABS(REDONDEAR(K641;2));10;
SI(ABS(REDONDEAR(K640/1,04;2))=ABS(REDONDEAR(K641;2));4;
SI(ABS(REDONDEAR(K640/1,18;2))=ABS(REDONDEAR(K641;2));18;"")))))Para varios desgloses de IVA
PARA VARIOS DESGLOSES
=SI(ABS(REDONDEAR(K684;2))=ABS(REDONDEAR(K683*21/100;2));21;
SI(ABS(REDONDEAR(K684;2))=ABS(REDONDEAR(K683*18/100;2));18;
SI(ABS(REDONDEAR(K684;2))=ABS(REDONDEAR(K683*10/100;2));10;
SI(ABS(REDONDEAR(K684;2))=ABS(REDONDEAR(K683*4/100;2));4;""))))Para combinado de los dos anteriores
COMBINADO
=SI(ABS(REDONDEAR(K640/1,21;2))=ABS(REDONDEAR(K641;2));21;
SI(ABS(REDONDEAR(K640/1,10;2))=ABS(REDONDEAR(K641;2));10;
SI(ABS(REDONDEAR(K640/1,04;2))=ABS(REDONDEAR(K641;2));4;
SI(ABS(REDONDEAR(K640/1,18;2))=ABS(REDONDEAR(K641;2));18;
SI(ABS(REDONDEAR(K684;2))=ABS(REDONDEAR(K683*21/100;2));21;
SI(ABS(REDONDEAR(K684;2))=ABS(REDONDEAR(K683*18/100;2));18;
SI(ABS(REDONDEAR(K684;2))=ABS(REDONDEAR(K683*10/100;2));10;
SI(ABS(REDONDEAR(K684;2))=ABS(REDONDEAR(K683*4/100;2));4;""))))
)))))Extraer números de una celda
Ahora empleamos la función EXTRAE para integrar las distintas partes de la fórmula, como se muestra en el ejemplo siguiente.
=EXTRAE(A1;COINCIDIR(VERDADERO;ESNUMERO(1EXTRAE(A1;FILA($1:$9);1));0);CONTAR(1EXTRAE(A1;FILA($1:$9);1)))En lenguaje sencillo, podemos plantear el problema del modo siguiente: Determínese la posición del primer número en la cadena alfanumérica (en la celda A1). Devolver ese número y los números siguientes.
Para convertir los caracteres resultantes en números, multiplique la fórmula por 1. Aunque esta operación no es estrictamente necesaria, debe realizarse si va a efectuar operaciones matemáticas en el resultado. Ésta es la fórmula final especificada en la celda B1:
=1EXTRAE(A1;COINCIDIR(VERDADERO;ESNUMERO(1EXTRAE(A1;FILA($1:$9);1));0);CONTAR(1*EXTRAE(A1;FILA($1:$9);1)))Importante es que se debe escribir estas fórmulas como matrices presionando CTRL+MAYÚS+ENTRAR.
Si queremos extraer las letras tenemos que modificar la fórmula
- cambiando el argumento VERDADERO en la fórmula a FALSO
- agregando la función LARGO para calcular la cantidad de letras a extraer, reatando de ella la función CONTAR
=EXTRAE(A1;COINCIDIR(FALSO;ESNUMERO(1EXTRAE(A1;FILA($1:$9);1));0);LARGO(A1)-CONTAR(1EXTRAE(A1;FILA($1:$9);1)))Se irá actualizado este post conforme nos surjan nuevas necesidades.
Deja una respuesta