3 fórmulas locas de Excel que hacen cosas increíbles

Microsoft Excel es una de las herramientas de hoja de cálculo más poderosas, con una impresionante colección de herramientas y características integradas. En este artículo, aprenderá qué tan poderosas pueden ser las fórmulas de Excel y el formato condicional, con tres ejemplos útiles.

Cavando en Microsoft Excel

Hemos cubierto varias formas diferentes de hacer un mejor uso de Excel, como usarlo para crear su propia plantilla de calendario o usarlo como una herramienta de gestión de proyectos..

Gran parte del poder está detrás de las fórmulas y reglas de Excel que puede escribir para manipular los datos y la información automáticamente, independientemente de los datos que inserte en la hoja de cálculo..

Vamos a profundizar en cómo puedes usar fórmulas y otras herramientas para hacer un mejor uso de Microsoft Excel..

Formato condicional con fórmulas

Una de las herramientas que la gente no usa con frecuencia es el formato condicional. Si está buscando información más avanzada sobre el formato condicional en Microsoft Excel, asegúrese de revisar el artículo de Sandy sobre el formato de datos en Microsoft Excel con formato condicional.

Con el uso de fórmulas de Excel, reglas 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 Casa y haga clic en la Formato condicional icono de la barra de herramientas.

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 trata de resaltar, colorear o sombrear celdas en base a los datos dentro de esa celda..

Este es probablemente el uso más común de los elementos de formato condicional, como hacer que una celda se vuelva de color rojo con menos o mayor que las fórmulas. Obtenga más información sobre cómo usar las declaraciones IF en Excel.

Una de las herramientas de formato condicional menos utilizadas es la Conjuntos de iconos opción, que ofrece un gran conjunto de iconos que puede utilizar para convertir una celda de datos de Excel en un icono de visualización del panel.

Cuando haces clic en Gestionar reglas, te llevará a la Gestor 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 haces clic en Editar regla, Verás el 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 desea.

Este panel de ejemplo mostrará el tiempo dedicado a diferentes tareas en comparación con el tiempo presupuestado. Si excede la mitad del presupuesto, aparecerá una luz amarilla. Si estás 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 por encima de los montos presupuestados.

Tiempo para reenfocar y administrar mejor tu tiempo!

1. Usando la función VLookup

Si desea utilizar funciones más avanzadas de Microsoft Excel, aquí hay otra para usted..

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 ¿qué pasa si se cambian??

En el siguiente ejemplo, ¿qué sucede si deseo 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, opuesto a la forma en que normalmente funciona VLookup..

Si lees los foros de usuarios profesionales de Microsoft Excel, encontrarás a mucha gente diciendo que esto no es posible con VLookup, y que tienes que usar una combinación de funciones de índice y coincidencia para hacer esto. Eso no es del todo cierto..

Puedes hacer que VLookup funcione de esta manera anidando una función ELEGIR en ella. En este caso, la fórmula de Excel se vería así:

"= VLOOKUP (FECHA (2018,6,25), ELEGIR (1,2, E2: E8, A2: A8), 2,0)"

Lo que significa esta función es que desea buscar la fecha 6/25/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 columna es “2”, pero como puede ver, la columna en la tabla de arriba es en realidad 1, a la derecha?

Eso es cierto, pero lo que estás haciendo con el “ESCOGER” La función es manipular los dos campos..

Estás asignando referencia “índice” números a rangos de datos - asignando las fechas al índice número 1 y las tareas al índice número 2.

Entonces, cuando escribes “2” en la función VLookup, en realidad te refieres al índice número 2 en la función ELEGIR. Guay a la derecha?

Entonces, ahora el VLookup usa el Fecha columna y devuelve los datos de la Tarea columna, aunque la 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, asegúrese de consultar el artículo completo de Dann sobre la búsqueda de datos en Excel mediante las funciones de búsqueda.

2. Fórmula anidada para analizar cadenas

Aquí hay una fórmula de Excel más loca para ti.

Puede haber casos en los que importe datos a Microsoft Excel desde una fuente externa que consiste en una cadena de datos delimitados.

Una vez que ingresa los datos, desea analizar esos datos en los componentes individuales. Aquí hay un ejemplo de información de nombre, dirección y número de teléfono delimitada por el “;” personaje.

Aquí le mostramos cómo puede analizar esta información utilizando una fórmula de Excel (vea si puede seguir mentalmente con esta locura):

Para el primer campo, para extraer el elemento situado más a la izquierda (el nombre de la persona), simplemente debe usar una función IZQUIERDA en la fórmula.

"= IZQUIERDA (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
• Agarra el texto más a la izquierda a ese punto

En este caso, el texto más a 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 Nested Excel más loca posible (que realmente funciona), usaremos un enfoque único..

Para extraer las partes de la derecha, debe anidar múltiples funciones DERECHAS para agarrar la sección de texto hasta que aparezca la primera. “;” y ejecute la función IZQUIERDA nuevamente. Esto es lo que parece para extraer la parte del número de calle de la dirección.

"= IZQUIERDA ((DERECHA (A2, LEN (A2) -FIND ("; ", A2))), FIND ("; ", (DERECHA (A2, LEN (A2) -FIND ("; ", A2)) ), 1) -1) "

Parece una locura, pero no es difícil reconstruirla. Todo lo que hice es tomar esta función:

DERECHA (A2, LEN (A2) -FIND (";", A2))

Y lo inserta 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 subsiguiente de la cadena necesita otro nido creado. Así que ahora solo tomas la locura “CORRECTO” Ecuación que creó para la última sección, y luego pásela a una nueva fórmula DERECHA con la fórmula DERECHA anterior pegada en sí misma donde la vea. “A2”. Esto es lo que parece.

(DERECHA ((DERECHA (A2, LEN (A2) -FIND (";", A2))), LEN ((DERECHA (A2, LEN (A2) -FIND (";", A2)))) - FIND ( ";", (DERECHA (A2, LEN (A2) -FIND (";", A2))))))

Luego toma esa fórmula y colóquela en la fórmula IZQUIERDA original donde haya un “A2”.

La fórmula final de la mente se ve así:

"= IZQUIERDA ((DERECHA ((DERECHA (A2, LEN (A2) -FIND ("; "))), LEN ((DERECHA (A2, LEN (A2) -FIND ("; ", A2))) ) -FIND (";", (RIGHT (A2, LEN (A2) -FIND (";" A2)))))) FIND (";", (RIGHT ((RIGHT (A2, LEN (A2) -FIND (";", A2))), LEN ((DERECHA (A2, LEN (A2) -FIND (";", A2)))) - FIND (";", (DERECHA (A2, LEN (A2) ) -FIND (";", A2))))))), 1) -1) "

Esa fórmula extrae correctamente “Portland, ME 04076” fuera de la cadena original.

Para extraer la siguiente sección, repita el proceso anterior de nuevo.

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, haga nidos largos que realmente funcionen.

Sí, esto cumple con el requisito de “loco”. Pero seamos honestos, hay una manera mucho más sencilla de lograr lo mismo con una función.

Solo seleccione la columna con los datos delimitados, y luego debajo de Datos elemento del menú, seleccione Texto a columnas.

Esto abrirá una ventana donde puede dividir la cadena por cualquier delimitador que desee.

En un par de clics, puedes hacer lo mismo que la fórmula loca de arriba ... pero, ¿dónde está la diversión??

Volviéndose loco con Microsoft Excel

Así que ahí lo tienen. Las fórmulas anteriores demuestran cuánta exageración puede obtener una persona al crear fórmulas de Microsoft Excel para realizar ciertas tareas.

A veces esas fórmulas de Excel no son realmente la forma más fácil (o mejor) de lograr cosas. La mayoría de los programadores le dirán que sea sencillo, y eso es tan cierto con las fórmulas de Excel como con cualquier otra cosa..

Si realmente desea ser serio con el uso de Excel, querrá leer nuestra guía para principiantes sobre el uso de Microsoft Excel La Guía para principiantes de Microsoft Excel La Guía para principiantes de Microsoft Excel Use esta guía para principiantes para comenzar su experiencia con Microsoft Excel. Los consejos básicos de la hoja de cálculo aquí te ayudarán a comenzar a aprender Excel por tu cuenta. Lee mas . Tiene todo lo que necesita para comenzar a aumentar su productividad con Excel..

Obtenga más información sobre: ​​Microsoft Excel, Microsoft Office 2016, hoja de cálculo.