17 fórmulas avanzadas de Excel: imprescindible para todos los profesionales

Si se encuentra a menudo flexionando sus manos y perdiendo horas para hacer cosas manualmente en Excel, se está perdiendo lo poderoso que puede ser Excel si sabe cómo usarlo de la manera correcta. Microsoft Excel es conocido por sus fórmulas que pueden funcionar de manera eficiente con una gran cantidad de datos en la celda, sin necesidad de mucha participación manual en el proceso.

Hemos compilado 17 fórmulas avanzadas de Excel que tienen una asombrosa capacidad para transformar largas tareas manuales mundanas en unos pocos segundos de trabajo. Son fórmulas que todo profesional debería tener al alcance de la mano para ahorrar su preciado tiempo, o para impresionar a su jefe en un momento crítico.

¡Revisa la lista y dinos tu favorito!

Los estudiantes reciben un aumento salarial promedio del 58 %, siendo el más alto de hasta el 400 %.

1. COINCIDENCIA DE ÍNDICE

Fórmula = ÍNDICE(C3:E9,COINCIDIR(B13, C3:C9,0),COINCIDIR(B14,C3:E3,0))

Una excelente alternativa a la fórmula BUSCARV o BUSCARH en Excel que tiene algunos inconvenientes para realizar tareas de búsqueda. El ÍNDICE MATCH es una fórmula de combinación de 2 funciones separadas:

ÍNDICE: esta fórmula devuelve el valor de una celda en una tabla según el número de columna y fila.

COINCIDIR: esta fórmula devuelve la posición de una celda en una fila o columna.

Un ejemplo de la combinación de fórmulas ÍNDICE y COINCIDIR es: Cuando está buscando y devuelve la altura de una persona según su nombre, puede usar esta fórmula para cambiar ambas variables (en este caso, el nombre y la altura) usando la fórmula ÍNDICE COINCIDIR .

Fuente

Vamos a desglosarlo paso a paso:

Cómo combinar ÍNDICE y PARTIDO

• Tipo ÍNDICE
• Seleccione el área que desea INDEXAR
• Bloquea el área con F4
• Encuentre la fila de la que desea buscar los datos
• Escriba MATCH con la serie de información y bloquee el área con F4
• Escriba 0 para la coincidencia exacta, cierre los corchetes
• Pulsa Enter

Ideas interesantes para proyectos de ciencia de datos

Ahora tiene un conjunto completamente dinámico y funcional de columnas y filas en la hoja donde todo el peinado a través de las celdas y filas para obtener los datos correctos se realiza automáticamente.

2. PROMEDIO

Para realizar la fórmula promedio para encontrar la media de cualquier rango de números, estos son los pasos que debe seguir:

Ingrese los valores, las celdas o la escala de celdas que está calculando en el formato.

La fórmula comenzará con =PROMEDIO(número1, número 2, etc.)

Si desea realizar el promedio de un rango de celdas en la hoja, estos son los pasos que debe seguir:

• Ingrese los valores, las celdas o la escala de las celdas que está calculando en el formato, tal como lo hizo anteriormente
• La fórmula será como =PROMEDIO(Valor inicial: Valor final).

Si se pregunta cómo ingresar los valores para la región de las celdas, puede seleccionar el área en su hoja con el mouse y bloquearla con F4. Esto hará que Excel haga el resto del trabajo por usted sin hacer ninguna suma y división de la figura con el número de elementos en el grupo.

3. SUMAR.SI

Esta fórmula en Excel se denota como SUMAR.SI (rango, criterios, [rango de suma]). Esto daría como resultado la suma de los valores dentro del Rango de celdas deseado que cumpliría con los requisitos establecidos por usted. Por ejemplo, =SUMAR.SI(C3: C12, “>70 000) devolvería la suma de los valores entre las celdas de C3 y C12 solo de las celdas que tienen un valor superior a 70 000.

En el caso de las finanzas, el salario o incluso los cálculos de costos, necesita el valor de los clientes potenciales que están asociados con empleados específicos determinados por la condición establecida por usted. Hacer esto manualmente consume mucho tiempo y ralentiza las cosas.

La fórmula para la condición anterior puede ser =SUMAR.SI(rango, criterios, [rango_suma]), donde Rango se puede definir como el Rango de la hoja de la que debe elegir los valores.

[sum_range] se define como el rango adicional u opcional que agregará además del primer rango ingresado.

Aquí hay un ejemplo:

Fuente

4. COMPENSACIÓN COMBINADA CON SUMA

Por sí sola, la función OFFSET puede no ser útil, pero cuando se combina con otros servicios, puede obtener una fórmula compleja con resultados más rápidos si desea crear un rol dinámico que pueda sumar cualquier número variable de celdas, la fórmula SUM normal, que es la estática debe combinarse con la función OFFSET.

Entonces, para encontrar la suma de valores en celdas variables, la fórmula debería ser:

=SUMA(B4:DESPLAZAMIENTO(B4,0,E2-1))

Fuente

Aquí la referencia final de la función SUM se reemplaza con la función OFFSET. La fórmula SUMA que comienza en B4 termina con una variable y es una fórmula de COMPENSACIÓN que comienza en B4 y continúa con el valor en E2 ("3") menos uno. Esto ayuda al método a moverse sobre dos celdas y sumar tres años de datos. En la referencia anterior, puede ver que la celda F7 contiene la suma de las celdas B4: D4 = 15.

5. SI Y

Esta fórmula es útil en situaciones en las que necesita crear ciertas condiciones para analizar una gran cantidad de datos. La declaración IF ayuda a usar la función avanzada IF de Excel para crear un nuevo campo basado en estas condiciones en una pista ya existente.

Por ejemplo, si desea marcar a los empleados con salarios superiores a 50 000 y una identificación de empleado superior a 3, la fórmula será:

SI(Y(E4>3,F4>50000)1,0)

Fuente

Como no hay casos reales en los datos anteriores, la fórmula devolvería el valor 0.

6. CONCATENAR

Si tiene muchas cadenas de texto en su hoja de datos y desea que los datos aparezcan en una línea, esta fórmula es su opción. Por ejemplo, si desea representar la identificación del empleado y el nombre del empleado en una sola columna, el método debe ser:

=CONCATENAR(B3, C3)

Fuente

7. PAGO

Esta función lo ayudará a calcular los pagos futuros adeudados por un préstamo, dada una tasa de interés específica, monto principal y duración del préstamo. Esto es lo que necesita para comenzar:

• El plazo del préstamo
• El capital inicial (dinero) del préstamo
• El valor futuro
• el tipo de prestamo

Ahora, si desea encontrar el pago mensual por un monto de capital, la fórmula para el mismo será:

= PMT (tasa, por, PV, [fv], [tipo])

Entendamos esto con un ejemplo:

Fuente

=PAGO(C2/12.B2.A2)

¡Y lo mejor es que puede arrastrar la esquina inferior derecha de las celdas de PMT a través de los campos para calcular automáticamente el monto del pago mensual para todos los campos!

Tipos populares de trabajos de ciencia de datos

8. RECORTAR

Esta es una de las fórmulas más utilizadas en Excel que limpia cualquier espacio no deseado en los campos. Por ejemplo: si necesita eliminar los espacios al principio de algún nombre, puede hacerlo usando la función TRIM:

=RECORTAR(C6)

Fuente

Esto le devolvería el valor de Chandan Kale sin espacios adicionales adjuntos al frente o al final.

9. LARGO

Esta es una función que le dice el número de caracteres en una cadena de texto. Una de las formas más emocionantes de usar esto es, por ejemplo, si desea resaltar a los donantes que donaron más de $ 1,000 contando el número de dígitos en la columna de donación, la fórmula será:

=LARGO(B2)

Fuente

Y si desea resaltar todas las celdas en la columna Donación, debe:

• Seleccione la pestaña Inicio en el menú
• Haga clic en Formato condicional (en la barra de herramientas)
• Seleccione Usar una fórmula para determinar qué celdas formatear

Fuente

Aquí, si establece la condición "> 3", entonces todo lo que supere los $ 1,000 recibirá el formato único, que puede elegir haciendo clic en la opción Formato.

10. ELIGE

Esta es una gran función para el análisis de escenarios en el modelado financiero. Le permite elegir entre un número específico de opciones y devolver la "elección" que ha seleccionado. Por ejemplo, si tiene tres valores diferentes para el crecimiento de los ingresos el próximo año en función de suposiciones, mediante la función ELEGIR, puede devolver la cantidad de acuerdo con sus requisitos.

La fórmula es:

=ELEGIR(C7,D3,D4,D5)

Fuente

11. CELDA, IZQUIERDA, CENTRO y DERECHA

Todas las funciones anteriores se pueden combinar de muchas maneras para obtener algunas fórmulas avanzadas.

• La función CELL se utiliza para devolver diferentes tipos de información sobre el contenido de la celda.
• El servicio IZQUIERDA se utiliza para reemplazar el texto desde el principio de la celda.
• La función MID puede devolver texto desde cualquiera de los puntos de inicio de la celda.
• La función DERECHA solo devuelve texto desde el final de la celda.

Fuente

12. XNPV y XIRR

Para todos los analistas que se encuentran con la banca de inversión, la investigación de acciones o cualquier otra área de las finanzas corporativas que requiera descontar los flujos de efectivo, ¡estas fórmulas seguramente les ahorrarán mucho tiempo y quejas!

Preguntas y respuestas de la entrevista de ciencia de datos

Por ejemplo, si desea calcular el valor neto actual (NPV) de cualquier inversión utilizando una tasa específica de descuento y flujos de efectivo que se producen en intervalos irregulares, utilice la siguiente función.

=XNPV(tasa de descuento, flujos de caja, fechas)

Fuente

Por otro lado, la función XIRR te dice la tasa interna de retorno (donde flujo de salida = flujo de entrada) para una serie de flujos de efectivo que ocurren a intervalos irregulares, tales como:

13. CONTARA ( )

Los analistas a menudo se encuentran de puntillas luchando por encontrar la cantidad de celdas con valores (números, errores, texto, valores lógicos) y aquellas que están vacías. La función COUNTA( ) puede ayudarlo a encontrar el nombre de las celdas que no están vacías en un rango seleccionado. Por ejemplo, la fórmula para contar los valores de una hoja de datos que tiene las columnas A1-A10 es:

=CONTARA(A1: A10)

Fuente

14. VF

Esta fórmula es útil si está buscando invertir dinero en algo y saber su valor. Los requisitos de la fórmula FV son:

• La tasa de interés del préstamo
• Numero de pagos
• El pago de cada periodo
• Saldo inicial actual
• Tipo de préstamo

Por ejemplo, si desea comparar varios CD vírgenes y tiene una herencia de $ 20,000 para invertir en un CD. Las tasas de interés se representan en formato decimal; los pagos son cero. La fórmula del escenario será:

=VF(A2/12,B2,C2,D2)

Fuente

Los resultados serán:

Fuente

15. ALEATORIO ENTRE

Esta función ayuda a seleccionar aleatoriamente un número dentro de un rango predefinido de números. Una vez que coloca los números más bajos y más altos en la fórmula, Excel puede elegir los datos correctos de los campos a los que se adjuntan los nombres en el rango y seleccionarlos aleatoriamente. El método para el escenario es:

=ALEATORIOENTRE(punto inicial, punto final)

Fuente

16. PEQUEÑO

La función PEQUEÑA en Excel devuelve valores numéricos según la posición del valor en una lista clasificada por importancia. Esta función ayuda a recuperar los "valores enésimos más pequeños" de una matriz o rango de celdas como el valor más pequeño, el segundo valor más bajo, el tercer valor más bajo, etc.

La sintaxis de la fórmula es

= PEQUEÑO (tiempos, rango)

Por ejemplo,

Fuente

Como la función PEQUEÑA es automática, debe proporcionar un rango y un número entero para 'nth' para especificar el valor clasificado. Los nombres oficiales de estos argumentos son 'matriz' y 'k'.

17. CUARTIL

Esta función devuelve el cuartil (cada uno de los cuatro grupos iguales) en un conjunto de datos dado y puede devolver el valor mínimo, el primer cuartil, el valor máximo del segundo cuartil. Esta función trae la cantidad de cuartiles de los campos en una matriz. La función devuelve un valor numérico según el percentil solicitado.

Sintaxis: =CUARTIL (matriz, cuarto)

Fuente

CONCLUSIÓN:

Microsoft Excel es bastante inevitable cuando se trata del lugar de trabajo del siglo XXI, pero el truco es que no tiene por qué ser tan desalentador. ¡Conviértalo en su amigo y observe cómo se dispara su productividad!