Fórmulas complejas en Excel (Tabla de contenido)
- Introducción a las fórmulas complejas en Excel
- Elija la función Servir como función de búsqueda
- BUSCAR CON ELEGIR
- SI con la función AND & OR
Introducción a las fórmulas complejas en Excel
Excel viene con muchas fórmulas útiles, tenemos una categoría de Finanzas, Lógica, Texto, Fecha y hora, Búsqueda y referencias, y Matemáticas y Trig ”. Cada fórmula individual es lo suficientemente buena para usuarios intermedios donde enfrentan situaciones directas, pero a medida que avanza al siguiente nivel, su habilidad necesita tomar la elevación, por lo que, en ese sentido, la aplicación de fórmulas más complejas en Excel juega un papel vital en la resolución de situaciones complejas. Teniendo esto en cuenta este artículo, lo guiaremos a través de algunas de las fórmulas complejas importantes y muy necesarias. Sigue leyendo.
Elija la función Servir como función de búsqueda
No muchos de ustedes han usado la función ELEGIR, pero esto es muy útil para servir también como función de búsqueda.
Puede descargar esta plantilla de Excel de fórmulas complejas aquí - Plantilla de Excel de fórmulas complejas Por ejemplo, mire los datos a continuación.
Tenemos datos de clientes mensualmente bajo esta tabla, tenemos cada tabla de clientes. En esta tabla, en la celda B13, he creado la lista desplegable del mes, de modo que cuando este menú desplegable cambie, obtengamos el total del mes seleccionado.
Crea una tabla como la siguiente.
Aplicar BUSCARV a la celda D13.
Ahora abra la función ELEGIR en la celda B14.
Seleccione el número de ÍNDICE como celda VLOOKUP y conviértalo en una referencia absoluta.
Ahora seleccione para cada argumento los números de ventas de cada mes.
Cierre el soporte y presione la tecla Intro.
Ahora copie y pegue la fórmula en las celdas inferiores también.
Ahora la fórmula ELEGIR mostrará los números del mes seleccionado.
BUSCAR CON Función ELEGIR
Hemos visto VLOOKUP tradicional, pero también podemos combinarlo con la función ELEGIR. Por ejemplo, mire los datos a continuación.
En las celdas A1 a B11, tenemos una tabla de ventas y en la celda D2 he creado la lista desplegable de meses y en la celda E2 necesitamos aplicar VLOOKUP y obtener el valor de ventas del mes seleccionado. Pero el problema para aplicar VLOOKUP es una estructura de datos, donde el valor de búsqueda está a la derecha de la columna de resultados, por lo que VLOOKUP no puede recuperar los datos de derecha a izquierda, en este caso, podemos usar la función ELEGIR.
Para seleccionar la matriz de la tabla, necesitamos abrir la función ELEGIR aquí.
En el argumento ÍNDICE de números en flor, los corchetes ingresan 1 y 2. Ahora seleccione el primer rango como columna Mes.
Seleccione la columna Ventas como el segundo rango.
Mencione el número de índice de columna como 2 y la búsqueda de rango como 0.
Entonces obtuvimos el resultado del mes de abril de la tabla. Por lo tanto, siempre que no tenga una estructura de datos precisa, podemos seleccionar la función ELEGIR.
SI con la función AND & OR
Las funciones lógicas están llenas de lógica para calcular las cosas. Eche un vistazo a los datos de ejemplo a continuación.
De la tabla anterior, necesitamos calcular la bonificación en función de las siguientes condiciones.
- Si el Servicio dura más de 1.5 años y si el departamento es Ventas o Soporte, entonces el Bono es 1000.
- En todo caso, entonces la Bonificación es de 3000.
Para calcular la bonificación, debemos aplicar IF, AND y OR juntos. Condición IF abierta en la celda D2.
Necesitamos probar múltiples condiciones, abrir la función AND.
Ahora la siguiente lógica es Ventas o Soporte, por lo tanto, para esta condición OR abierta.
Pruebe si el título es Ventas o Soporte.
Por lo tanto, si todas las condiciones proporcionadas son VERDADERAS, la bonificación debería ser de 10000.
Si las pruebas lógicas proporcionadas son FALSAS, la bonificación es de 3000.
Cierre el soporte y presione la tecla Intro para obtener el resultado.
Copie y pegue la fórmula en las celdas a continuación.
Entonces, de acuerdo con la condición, obtuvimos el resultado. Si el servicio es> 1.5 años y el título es Ventas o Soporte, obtendremos 10000 o de lo contrario obtendremos 3000.
Fórmula compleja con caracteres comodín
BUSCARV requiere que se haga coincidir un valor de búsqueda exacto para obtener los datos. Este es el eslogan tradicional, pero aún podemos obtener los datos utilizando el valor de búsqueda de partículas. Por ejemplo, si el valor de búsqueda es "VIVO" y en la tabla principal si es "VIVO Mobile" aún podemos coincidir utilizando caracteres comodín. Veremos uno de los ejemplos ahora, a continuación se muestran los datos de ejemplo.
Tenemos la tabla de búsqueda en la columna A. En la columna C tenemos valores de búsqueda, estos valores de búsqueda no son exactamente los mismos que los valores de la tabla de búsqueda. Entonces veremos cómo aplicar VLOOKUP usando comodines.
Primero, abra la función BUSCARV en la celda D1.
El primer argumento es el valor de búsqueda . Uno de los problemas con el valor de búsqueda aquí es que no tenemos una coincidencia exacta, por lo que debemos encerrar este valor de búsqueda con un asterisco antes y después del valor de búsqueda.
Aquí hemos aplicado dos caracteres de asterisco "*" y C2 y "*". Aquí el asterisco indica que cualquier cosa entre el comodín debe coincidir y devolver el resultado relacionado.
Aunque teníamos el carácter de asterisco "Infosys" coincidía con el valor en la tabla de búsqueda y devolvía el resultado exacto como "Infosys Ltd".
Del mismo modo, en la celda D6, obtuvimos un valor de error como # ¡VALOR! porque no hay una palabra "Mintra" en la tabla de búsqueda.
Artículos recomendados
Esta es una guía de fórmulas complejas en Excel. Aquí discutimos algunas de las fórmulas complejas importantes y muy necesarias con ejemplos y plantilla de Excel. También puede consultar nuestros otros artículos sugeridos para obtener más información:
- BUSCAR Fórmula en Excel (Consejos y trucos)
- Tutoriales sobre la función NORMSINV de Excel
- Cuadro de nombre y sus usos en Excel
- Concatenar cadenas en Excel