Lista de las 10 mejores fórmulas y funciones avanzadas de Excel
Puede descargar esta Plantilla de fórmulas avanzadas de Excel aquí - Plantilla de fórmulas avanzadas de Excel# 1 - Fórmula VLOOKUP en Excel
Esta función avanzada de Excel es una de las fórmulas más utilizadas en Excel. Se debe principalmente a la simplicidad de esta fórmula y su aplicación en la búsqueda de un cierto valor de otras tablas que tiene una variable común en estas tablas. Suponga que tiene dos tablas que tienen detalles sobre el salario y el nombre del empleado de una empresa, siendo la ID de empleado una columna principal. Desea obtener el salario de la Tabla B en la Tabla A.
Puede utilizar BUSCARV como se muestra a continuación.
Resultará en la siguiente tabla cuando apliquemos esta fórmula avanzada de Excel en otras celdas de la columna Salario del empleado.
Arrastre la fórmula al resto de las celdas.
Hay tres delimitaciones principales de BUSCARV:
- No puede tener una columna principal a la derecha de la columna para la que desea completar el valor de otra tabla. En este caso, la columna Salario del empleado no puede estar antes del ID de empleado.
- En el caso de valores duplicados en la columna principal de la Tabla B, el primer valor se completará en la celda.
- Si inserta una nueva columna en la base de datos (por ejemplo, inserte una nueva columna antes del salario del empleado en la Tabla B), el resultado de la fórmula podría ser diferente en función de una posición que haya mencionado en la fórmula (en el caso anterior, el resultado estaría en blanco)
# 2 - Fórmula de índice en Excel
Esta fórmula avanzada de Excel se utiliza para obtener el valor de una celda en una tabla determinada especificando el número de filas, columnas o ambas. Ex. Para obtener el nombre de un empleado en la quinta observación, a continuación se muestran los datos.
Podemos usar la fórmula avanzada de Excel de la siguiente manera:
Se puede usar la misma fórmula de ÍNDICE para obtener valor a lo largo de la fila. Al usar tanto el número de fila como de columna, la sintaxis se vería así:
La fórmula anterior devolvería "Rajesh Ved".
Nota: Si inserta otra fila en los datos de la 5ª fila, la fórmula devolverá "Chandan Kale". Por lo tanto, la salida dependería de cualquier cambio que ocurra en la tabla de datos a lo largo del tiempo.
# 3 - Fórmula COINCIDIR en Excel
Esta fórmula avanzada de Excel devuelve el número de fila o columna cuando hay una coincidencia de cierta cadena o número en el rango dado. En el siguiente ejemplo, estamos tratando de encontrar la posición de "Rajesh Ved" en la columna Nombre del empleado.
La fórmula sería la siguiente:
La función COINCIDIR devolvería 5 como valor.
El tercer argumento se utiliza para la coincidencia exacta. También puede utilizar +1 y -1 según sus requisitos.
Nota: Uno puede combinar INDICE y COINCIDIR para superar la limitación de BUSCARV.
# 4 - Fórmula SI Y en Excel
Hay muchos casos en los que es necesario crear banderas en función de algunas restricciones. Todos estamos familiarizados con la sintaxis básica de IF. Usamos esta función IF de Excel avanzada para crear un nuevo campo basado en alguna restricción en un campo ya existente. Pero, ¿qué pasa si necesitamos tener en cuenta varias columnas al crear una bandera? Ex. En el siguiente caso, queremos marcar a todos los empleados cuyo salario sea superior a 50K pero ID de empleado superior a 3.
Usaríamos SI Y en tales casos. A continuación, encontrará la captura de pantalla correspondiente.
Devolvería el resultado como 0.
Podemos tener muchas condiciones o restricciones para crear una bandera basada en múltiples columnas usando AND.
# 5 - Fórmula SI O en Excel
De manera similar, también podemos usar la función OR en excel en lugar de AND si solo necesitamos satisfacer una de las condiciones de muchos.
En los casos anteriores, si se cumple alguna de las condiciones, la celda se rellenará como 1 más 0. También podemos sustituir 1 o 0 por algunas subcadenas con comillas dobles (“”).
# 6 - Fórmula SUMIF en Excel
En algunos análisis, es posible que deba filtrar algunas observaciones al aplicar la función de suma o recuento. En tales casos, esta función avanzada SUMIF de Excel en Excel está a nuestro rescate. Filtra todas las observaciones basadas en ciertas condiciones dadas en esta fórmula avanzada de Excel y las resume. Ex. ¿Qué pasa si queremos saber la suma de los salarios de solo aquellos empleados que tienen una identificación de empleado superior a 3?
Aplicando la fórmula SUMIFS:
La fórmula devuelve los resultados como 322000.
También podemos contar el número de empleados en la organización que tienen un ID de empleado mayor que 3 cuando usamos CONTAR.SI en lugar de SUMAR.
# 7 - Fórmula CONCATENAR en Excel
Esta función avanzada de Excel es una de las fórmulas que se pueden utilizar con múltiples variantes. Esta fórmula avanzada de Excel nos ayuda a unir varias cadenas de texto en una sola cadena de texto. Por ejemplo, si queremos mostrar la identificación del empleado y el nombre del empleado en una sola columna.
Podemos usar esta fórmula CONCATENAR solo para hacer eso.
La fórmula anterior resultará en "1Aman Gupta".
Podemos tener una variante más poniendo un solo guión entre ID y NAME. Ex. CONCATENAR (B3, ”-“, C3) dará como resultado “1-Aman Gupta”. También podemos usar esto en BUSCARV cuando BUSCAR en el valor de Excel es una mezcla de más de una variable.
# 8 - Fórmula IZQUIERDA, MEDIA y DERECHA en Excel
Podemos usar esta fórmula avanzada de Excel si queremos extraer una determinada subcadena de una determinada cadena. Las fórmulas mencionadas se pueden utilizar en función de nuestros requisitos. Ex. Si queremos extraer los primeros 5 caracteres del Nombre del empleado, podemos usar la fórmula IZQUIERDA en Excel con el nombre de la columna y el segundo parámetro como 5.
La salida se da a continuación:
La aplicación de la fórmula DERECHA en Excel también es la misma, es solo que estaríamos mirando el carácter desde la derecha de la cadena. Sin embargo, en el caso de una función MID en Excel, debemos dar la posición inicial de la cadena de texto requerida y la longitud de la cadena.
# 9 - Fórmula OFFSET en Excel
Esta función avanzada de Excel con una combinación de otras funciones como SUMA o PROMEDIO podría ser útil para dar un toque dinámico a los cálculos. Se utiliza mejor en los casos en que estamos insertando filas continuas en una base de datos existente. OFFSET Excel nos da un rango donde necesitamos mencionar la celda de referencia, el número de filas y columnas. Ex. Si queremos calcular el promedio de los primeros 5 empleados en la empresa donde tenemos un salario de empleados ordenados por ID de empleado, podemos hacer lo siguiente. El siguiente cálculo siempre nos dará salario.
- Nos dará la suma de los sueldos de los primeros 5 empleados.
# 10 - Fórmula TRIM en Excel
Esta fórmula de Excel avanzada se utiliza para limpiar los espacios sin importancia del texto. Ex. Si queremos eliminar los espacios al comienzo de algún nombre, podemos usarlo usando la función TRIM en Excel como se muestra a continuación:
La salida resultante sería "Chandan Kale" sin ningún espacio antes de Chandan.
