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

• Productividad

Anuncio

Las fórmulas de 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.

¡Desbloquee la hoja de trucos "Fórmulas esenciales de Excel" ahora!

Esto lo suscribirá a nuestro boletín

Excavando 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 herramienta de gestión de proyectos.

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

Analicemos cómo puede usar fórmulas y otras herramientas para hacer un mejor uso de 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. Si está buscando información más avanzada sobre formato condicional en Microsoft Excel, asegúrese de consultar el artículo de Sandy sobre datos de formato en Microsoft Excel con 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 más allá del alcance de este artículo en particular, pero la mayoría de ellos se trata de resaltar, colorear o sombrear celdas en función de los datos dentro de esa celda.

Este es probablemente el uso más común del formato condicional: cosas como convertir una celda en rojo usando fórmulas menores o mayores que. 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 opción Conjuntos de iconos, que ofrece un gran conjunto de iconos que puede usar para convertir una celda de datos de Excel en un icono de visualización del 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 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 tablero que desee.

Este panel de ejemplo mostrará el tiempo dedicado a diferentes tareas versus el tiempo presupuestado. Si supera la mitad del presupuesto, se mostrará una luz amarilla. Si está completamente por encima del presupuesto, se volverá rojo.

Como puede ver, este panel muestra que el presupuesto de tiempo no es exitoso.

Casi la mitad del tiempo se gasta mucho más de las cantidades presupuestadas.

¡Es hora de reenfocarse y administrar mejor su 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 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/06/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 de Microsoft Excel, encontrarás a muchas personas que dicen que esto no es posible con VLookup, y que tienes que usar una combinación de funciones de índice y coincidencia para hacerlo. 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)" 

Lo que significa esta función es que desea encontrar la fecha 25/06/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?

Entonces, ahora VLookup usa la columna Fecha y devuelve los datos de la columna Tarea, aunque la Tarea está a la izquierda.

Ahora que conoces este pequeño dato, ¡imagina qué más puedes hacer!

Si está intentando realizar otras tareas de búsqueda de datos avanzadas, asegúrese de consultar el artículo completo de Dann sobre cómo encontrar datos en Excel utilizando 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 importa 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 delimitado por el carácter ";".

Así es como puedes analizar esta información usando una fórmula de Excel (mira si puedes seguir mentalmente junto con 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 desde A2
• Encuentra el símbolo delimitador ";"
• Resta uno para la ubicación correcta del final de esa sección de cadena
• Toma el texto de la izquierda hasta 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é hay de las otras secciones?

Puede haber formas más fáciles de hacer esto, pero dado que queremos intentar crear la fórmula de Nested Excel más loca posible (que realmente funcione), vamos a utilizar un enfoque único.

Para extraer las partes a la derecha, debe anidar múltiples funciones DERECHA para tomar la sección de texto hasta el primer símbolo ";" y volver a ejecutar la función IZQUIERDA. Esto es lo que parece 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 de 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 cadena necesita otro nido creado. Así que ahora simplemente toma la loca ecuación "DERECHA" que había creado para la última sección, y luego la pasa a una nueva fórmula DERECHA con la fórmula DERECHA anterior pegada en sí misma donde vea "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, tomas ESA fórmula y la colocas en la fórmula original IZQUIERDA donde haya un "A2".

La fórmula final alucinante 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 irregulares, pero todo lo que está haciendo es cortar y pegar fórmulas largas en sí mismo, hacer nidos largos que realmente funcionen.

Sí, esto cumple con el requisito de "loco". Pero seamos honestos, hay una manera mucho más simple 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 en columnas .

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

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

Volverse loco con las fórmulas de Microsoft Excel

Entonces ahí lo tienes. Las fórmulas anteriores demuestran cuán exagerado puede 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 la 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 cualquier otra cosa. Incluso puede usar funciones integradas como Power Query.

Si realmente quiere tomarse en serio 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í lo ayudarán a comenzar a aprender Excel por su cuenta. Lee mas . Tiene todo lo que necesita para comenzar a aumentar su productividad con Excel. Después de eso, asegúrese de consultar nuestra hoja de trucos de funciones esenciales de Excel. La hoja de trucos de las fórmulas y funciones esenciales de Microsoft Excel. La hoja de trucos de las fórmulas y funciones esenciales de Microsoft Excel. Lee mas .

Explore más sobre: ​​Microsoft Excel, Microsoft Office 2016, Hoja de cálculo.