3 fórmulas locas de Microsoft Excel que son extremadamente útiles
Las fórmulas de Microsoft Excel pueden hacer casi cualquier cosa. En este artículo, aprenderá cuán poderosas pueden ser las fórmulas de Microsoft Excel y el formato condicional, con tres ejemplos útiles.
Introducción a Microsoft Excel
Hemos cubierto varias formas diferentes de hacer un mejor uso de Excel, como dónde encontrar excelentes plantillas de Excel descargables y cómo usar Excel como una herramienta de gestión de proyectos .
Gran parte del poder de Excel se encuentra detrás de las fórmulas y reglas de Excel que lo ayudan a manipular datos e información automáticamente, independientemente de los datos que inserte en la hoja de cálculo.
Analicemos cómo puede utilizar fórmulas y otras herramientas para utilizar mejor Microsoft Excel.
Formato condicional con fórmulas de Excel
Una de las herramientas que la gente no usa con suficiente frecuencia es el formato condicional. Con el uso de fórmulas, reglas de Excel o solo algunas configuraciones realmente simples, puede transformar una hoja de cálculo en un tablero automatizado.
Para acceder al formato condicional, simplemente haga clic en la pestaña Inicio y haga clic en el icono de la barra de herramientas de formato condicional .
En Formato condicional, hay muchas opciones. La mayoría de estos están fuera del alcance de este artículo en particular, pero la mayoría de ellos tratan sobre resaltar, colorear o sombrear celdas según los datos dentro de esa celda.
Este es probablemente el uso más común del formato condicional, cosas como poner una celda en rojo usando fórmulas de menor o mayor que. Obtenga más información sobre cómo usar declaraciones IF en Excel .
Una de las herramientas de formato condicional menos utilizadas es la opción Conjuntos de iconos , que ofrece un gran conjunto de iconos que puede utilizar para convertir una celda de datos de Excel en un icono de visualización de tablero.
Cuando haga clic en Administrar reglas , lo llevará al Administrador de reglas de formato condicional .
Dependiendo de los datos que seleccionó antes de elegir el conjunto de iconos, verá la celda indicada en la ventana del Administrador con el conjunto de iconos que acaba de elegir.
Cuando haga clic en Editar regla , verá el cuadro de diálogo donde ocurre la magia.
Aquí es donde puede crear la fórmula lógica y las ecuaciones que mostrarán el icono del panel de control que desee.
Este panel de ejemplo mostrará el tiempo dedicado a diferentes tareas en comparación con el tiempo presupuestado. Si excede la mitad del presupuesto, se mostrará una luz amarilla. Si está completamente por encima del presupuesto, se pondrá rojo.
Como puede ver, este panel muestra que el presupuesto de tiempo no es exitoso.
Casi la mitad del tiempo se gasta muy por encima de los montos presupuestados.
¡Es hora de volver a concentrarse y administrar mejor su tiempo!
1. Uso de la función VLookup
Si desea utilizar funciones más avanzadas de Microsoft Excel, aquí tiene algunas para que las pruebe.
Probablemente esté familiarizado con la función VLookup, que le permite buscar en una lista un elemento en particular en una columna y devolver los datos de una columna diferente en la misma fila que ese elemento.
Desafortunadamente, la función requiere que el elemento que está buscando en la lista esté en la columna de la izquierda y los datos que está buscando estén a la derecha, pero ¿y si se cambian?
En el siguiente ejemplo, ¿qué sucede si quiero encontrar la tarea que realicé el 25/6/2018 a partir de los siguientes datos?
En este caso, está buscando valores a la derecha y desea devolver el valor correspondiente a la izquierda.
Si lee los foros de usuarios profesionales de Microsoft Excel, encontrará muchas personas que dicen que esto no es posible con VLookup. Debe utilizar una combinación de funciones de índice y coincidencia para hacer esto. Eso no es del todo cierto.
Puede hacer que VLookup funcione de esta manera anidando una función ELEGIR en él. En este caso, la fórmula de Excel se vería así:
"=VLOOKUP(DATE(2018,6,25),CHOOSE({1,2},E2:E8,A2:A8),2,0)"Esta función significa que desea encontrar la fecha 25/6/2013 en la lista de búsqueda y luego devolver el valor correspondiente del índice de la columna.
En este caso, notará que el índice de la columna es "2", pero como puede ver, la columna en la tabla anterior es en realidad 1, ¿verdad?
Eso es cierto, pero lo que está haciendo con la función " ELEGIR " es manipular los dos campos.
Está asignando números de "índice" de referencia a rangos de datos, asignando las fechas al índice número 1 y las tareas al índice número 2.
Entonces, cuando escribe "2" en la función VLookup, en realidad se está refiriendo al índice número 2 en la función ELEGIR. ¿Guay, verdad?
VLookup ahora usa la columna Fecha y devuelve datos de la columna Tarea , aunque Tarea está a la izquierda.
Ahora que conoces este pequeño detalle, ¡imagina qué más puedes hacer!
Si está intentando realizar otras tareas avanzadas de búsqueda de datos, consulte este artículo sobre cómo buscar datos en Excel utilizando funciones de búsqueda .
2. Fórmula anidada para analizar cadenas
¡Aquí hay una fórmula más loca de Excel para ti! Puede haber casos en los que importe datos a Microsoft Excel desde una fuente externa que consta de una cadena de datos delimitados.
Una vez que ingrese los datos, desea analizar esos datos en los componentes individuales. A continuación, se muestra un ejemplo de información sobre el nombre, la dirección y el número de teléfono delimitados por ";" personaje.
Así es como puede analizar esta información usando una fórmula de Excel (vea si puede seguir mentalmente esta locura):
Para el primer campo, para extraer el elemento más a la izquierda (el nombre de la persona), simplemente usaría una función IZQUIERDA en la fórmula.
"=LEFT(A2,FIND(";",A2,1)-1)"Así es como funciona esta lógica:
- Busca la cadena de texto de A2
- Encuentra el ";" símbolo delimitador
- Resta uno para la ubicación correcta del final de esa sección de cuerda
- Toma el texto más a la izquierda hasta ese punto
En este caso, el texto de la izquierda es "Ryan". Misión cumplida.
3. Fórmula anidada en Excel
Pero, ¿qué pasa con las otras secciones?
Puede haber formas más fáciles de hacer esto, pero como queremos intentar crear la fórmula de Excel anidada más loca posible (que realmente funcione), usaremos un enfoque único.
Para extraer las partes de la derecha, necesita anidar varias funciones DERECHA para tomar la sección de texto hasta el primer ";" símbolo y vuelva a ejecutar la función IZQUIERDA. Así es como se ve para extraer la parte del número de la calle de la dirección.
"=LEFT((RIGHT(A2,LEN(A2)-FIND(";",A2))),FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2))),1)-1)"Parece una locura, pero no es difícil de reconstruir. Todo lo que hice fue tomar esta función:
RIGHT(A2,LEN(A2)-FIND(";",A2))Y lo insertó en cada lugar en la función IZQUIERDA arriba donde hay un "A2".
Esto extrae correctamente la segunda sección de la cadena.
Cada sección posterior de la cuerda necesita que se cree otro nido. Ahora todo lo que necesita hacer es tomar la ecuación " DERECHA " que creó en la última sección y pegarla en una nueva fórmula DERECHA con la fórmula DERECHA anterior pegada en ella donde ve "A2". Así es como se ve.
(RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2))))))Luego, debe tomar ESA fórmula y colocarla en la fórmula IZQUIERDA original donde haya un "A2".
La fórmula alucinante final se ve así:
"=LEFT((RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2)))))),FIND(";",(RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2)))))),1)-1)"Esa fórmula extrae correctamente "Portland, ME 04076" de la cadena original.
Para extraer la siguiente sección, repita el proceso anterior nuevamente.
Sus fórmulas de Excel pueden volverse realmente locas, pero todo lo que está haciendo es cortar y pegar fórmulas largas en sí mismas, creando nidos largos que aún funcionan.
Sí, esto cumple con el requisito de "loco". Pero seamos honestos, hay una forma mucho más sencilla de lograr lo mismo con una función.
Simplemente seleccione la columna con los datos delimitados y luego, en el elemento del menú Datos , seleccione Texto a columnas .
Esto abrirá una ventana donde puede dividir la cadena por cualquier delimitador que desee. Simplemente ingrese ' ; 'y verá que la vista previa de los datos seleccionados cambia en consecuencia.
Con un par de clics, puede hacer lo mismo que la fórmula loca de arriba … pero ¿dónde está la diversión en eso?
Volviéndose loco con las fórmulas de Microsoft Excel
Así que ahí lo tienes. Las fórmulas anteriores demuestran cuán exagerada puede llegar a ser una persona al crear fórmulas de Microsoft Excel para realizar ciertas tareas.
A veces, esas fórmulas de Excel no son en realidad la forma más fácil (o mejor) de lograr cosas. La mayoría de los programadores le dirán que lo mantenga simple, y eso es tan cierto con las fórmulas de Excel como con cualquier otra cosa.
Si realmente quiere tomarse en serio el uso de Excel, querrá leer nuestra guía para principiantes sobre el uso de Microsoft Excel . Tiene todo lo que necesita para comenzar a aumentar su productividad con Excel. Después de eso, asegúrese de consultar nuestra hoja de referencia de funciones esenciales de Excel para obtener más orientación.
Crédito de la imagen: kues / Depositphotos