Fórmulas avanzadas de Excel (tabla de contenido)
- Introducción a las fórmulas avanzadas en Excel
- Ejemplos de fórmula avanzada en Excel
Introducción a las fórmulas avanzadas en Excel
Una vez que alcanza el nivel intermedio en Excel, debe trabajar duro para avanzar al nivel avanzado. Para avanzar al nivel avanzado, debe conocer algunas de las fórmulas avanzadas utilizadas con frecuencia. En este artículo, cubriré las 10 fórmulas avanzadas más importantes que todos los estudiantes de Excel deben saber. Sigue este artículo para aprender y explorar.
Ejemplos de fórmula avanzada en Excel
Comprendamos cómo usar las fórmulas avanzadas en Excel con algunos ejemplos.
Puede descargar esta plantilla de Excel de fórmulas avanzadas aquí - Plantilla de Excel de fórmulas avanzadasEjemplo # 1 - Función VLOOKUP parcial
Debe haber encontrado una situación en la que VLOOKUP arroja un error incluso si hubo un ligero error en el valor de búsqueda. Por ejemplo, si está buscando el salario del nombre Abhishek Sinha y solo tiene Abhishek, VLOOKUP no puede obtener los datos.
Sin embargo, utilizando el carácter de asterisco en cualquier extremo del valor de búsqueda, podemos obtener los datos para un valor de búsqueda parcial.
En la tabla 1 tengo el nombre completo y el salario, pero en la tabla 2 solo tengo nombres parciales y necesito encontrar el salario de cada empleado.
Paso 1: abra la fórmula BUSCARV en la celda E3. Antes de seleccionar el valor de búsqueda, coloque un asterisco (*) a cada lado del valor de búsqueda.
Paso 2: como de costumbre, puede completar la fórmula BUSCARV ahora y tendremos los resultados.
Después de usar la fórmula anterior, la salida se muestra a continuación.
La misma fórmula se usa en otras células.
Ejemplo # 2 - CONTEOS con símbolos de operador
Debe haber utilizado la función COUNTIF & IFS para contar las cosas en la lista. También podemos contar en función de símbolos de operador como mayor que (>) menor que (<) y signo igual (=).
Ahora eche un vistazo a los datos a continuación, por ejemplo. Si desea contar el número total de facturas para la región SUR después de la fecha 10 de enero de 2018, ¿cómo cuenta?
Ejemplo # 3 - Condición IF con condiciones AND & OR
Las funciones lógicas son parte de nuestras actividades diarias. Necesitas dominarlos para avanzar al siguiente nivel. Si está calculando la bonificación en función de múltiples condiciones, entonces necesita anidar la condición AND u OR con la condición IF para realizar el trabajo.
Suponga que necesita calcular el monto de la bonificación para cada departamento en función del departamento y los años de servicio que necesita para estas funciones. Según los criterios a continuación, debemos calcular la bonificación.
Si el servicio es superior a 4 años y el departamento tiene un bono de ventas o soporte, es de 50000 o el bono es de 25000.
Aplique la fórmula a continuación para obtener la cantidad del bono.
Después de usar la fórmula anterior, la salida se muestra a continuación.
Misma fórmula que se aplica en la celda E3 a E9.
Ejemplo # 4 - Función TEXTO para hacer que se dirija dinámico
Supongamos que mantiene una tabla de ventas diarias y debe actualizar la tabla todos los días. Al principio de la tabla, tiene un encabezado que dice "Datos de ventas consolidados de DD-MM-AAAA a DD-MM-AAAA". A medida que la tabla se actualice, debe cambiar la fecha del encabezado. ¿No es una tarea frustrante hacer lo mismo una y otra vez? Podemos hacer que este encabezado sea dinámico utilizando las funciones TETX, MIN y Max junto con el símbolo de operador concatenado ampersand (&).
Ejemplo # 5 - ÍNDICE + PARTIDO + MÁX para encontrar la persona de mayor venta
Tiene una lista de vendedores y las ventas que realizaron con su nombre. ¿Cómo saber quién es el mejor o el mejor vendedor de la lista? Por supuesto, tenemos varias otras técnicas para determinar el resultado, pero esta función puede devolver al vendedor más alto del lote.
Aplique la siguiente función para obtener el nombre más alto de vendedor.
Después de usar la fórmula anterior, la salida se muestra a continuación.
Ejemplo # 6 - Obtenga un recuento de valores únicos de la lista
Si tiene muchos valores duplicados y está obligado a decir cuántos valores únicos hay en ¿cómo saberlo? Puede saberlo eliminando el valor duplicado, pero esta no es la forma dinámica de contar el valor único.
Usando esta función de matriz podemos distinguir los valores únicos del lote.
Aplique la fórmula a continuación para obtener una lista de valores únicos.
Ejemplo # 7 - Use el rango con nombre para hacer que el menú desplegable sea dinámico
Si a menudo trabaja con una lista desplegable y actualiza la lista desplegable, debe volver al rango de la lista de origen para eliminar o agregar valores. Después de eso, debe volver a las celdas desplegables y actualizar nuevamente el rango de la lista desplegable.
Sin embargo, si puede crear un rango con nombre para su lista desplegable, puede hacer que la lista desplegable sea dinámica y esté actualizada.
Cree un rango de nombres como se muestra en la imagen a continuación.
Ahora vaya a la celda desplegable y abra el cuadro de diálogo desplegable.
En la fuente, presione la tecla F3 que mostrará todos los nombres definidos, seleccione el nombre de su lista desplegable.
Ok, una lista desplegable está lista y actualizará los valores automáticamente cuando haya un cambio en el rango de la lista desplegable.
Ejemplo # 8 - Deshacerse de los valores de error usando la función IFERROR
Estoy seguro de que ha experimentado valores de error mientras trabajaba con VLOOKUP, cálculos de división. Manejar estos valores de error es una tarea tediosa. Pero podemos deshacernos de estos valores de error utilizando la función IFERROR en la fórmula.
Después de usar la fórmula anterior, la salida se muestra a continuación.
La misma fórmula utilizada en otras células.
Ejemplo # 9 - Use la función PMT para crear su propio gráfico EMI
Hoy en día, la opción EMI no es algo extraño para todos nosotros. En Excel podemos estimar nuestro propio gráfico EMI utilizando la función PMT. Siga los pasos a continuación para crear su propio gráfico.
Aplique la siguiente fórmula en la celda B4 para obtener la cantidad de EMI.
Ejemplo # 10 - ÍNDICE + COINCIDIR como alternativa a la función BUSCARV
Espero que esté al tanto de la limitación de la función BUSCARV. Una limitación importante es que BUSCARV puede recuperar los datos de izquierda a derecha, no de derecha a izquierda. No todo el tiempo los datos están bien organizados y listos para usar. A menudo, la columna de valor que estamos buscando está en el lado izquierdo del valor de búsqueda, por lo que VLOOKUP no puede ayudar en estos casos.
Una combinación de la función INDEX + MATCH sirve como alternativa a la función BUSCARV.
Después de usar la fórmula anterior, la salida se muestra a continuación.
La misma fórmula se usa en otras células.
Según la identificación del producto, necesitamos extraer los valores de venta. En la tabla principal, el ID del producto se encuentra en el lado derecho de la columna de ventas. Entonces VLOOKUP no puede recuperar los datos. Use la siguiente fórmula para obtener los datos.
Artículos recomendados
Esta es una guía de fórmulas avanzadas en Excel. Aquí discutimos cómo usar fórmulas avanzadas en Excel junto con ejemplos prácticos y plantillas de Excel descargables. También puede consultar nuestros otros artículos sugeridos:
- ¿Cómo utilizar la función de índice de Excel?
- Excel avanzado | Función de base de datos
- Ejemplos sobre la fórmula TRIM en Excel
- Guía para la fórmula de Excel OFFSET