Actualizado por Ryan Dube en octubre de 2017.
Siempre he creído que Microsoft Excel es una de las herramientas de software más poderosas que existen. No es solo el hecho de que es una poderosa aplicación de hoja de cálculo. Microsoft Excel tiene una impresionante colección de herramientas y funciones integradas. En este artículo, planeo mostrarte cómo pueden ser las fórmulas poderosas y el formato condicional, con tres ejemplos útiles.
Hemos cubierto varias maneras diferentes de hacer un mejor uso de Excel, como usarlo para crear su propia plantilla de calendario Cómo hacer una plantilla de calendario en Excel Cómo hacer una plantilla de calendario en Excel Siempre necesitará un calendario. Le mostramos cómo crear su propia plantilla personalizada de forma gratuita con Microsoft Excel. Lea más, utilícelo como una herramienta de gestión de objetivos 10+ Plantillas de Excel útiles para la Gestión y seguimiento de proyectos 10+ Plantillas de Excel útiles para gestión de proyectos y plantillas de seguimiento son para los directores de proyecto lo que los cuchillos son para los chefs profesionales: indispensable. Le mostramos cómo puede replicar proyectos exitosos mediante el uso de plantillas ya preparadas en Microsoft Excel y más. Lea más y otras formas únicas en que puede usarlo para administrar su vida. Simplemente lea esos artículos y verá lo poderoso que puede ser Microsoft Excel.
Gran parte del poder está realmente detrás de las fórmulas y reglas que puede escribir para manipular datos e información de forma automática, independientemente de los datos que inserte en la hoja de cálculo.
Excavando en Microsoft Excel
Con la información correcta, puede crear un sistema que automáticamente calcula y recalcula resultados e informes a partir de esos datos brutos. Hoy, me gustaría profundizar un poco más en la superficie y mostrarte cómo puedes utilizar algunas de las fórmulas subyacentes y otras herramientas para hacer un mejor uso de Microsoft Excel.
Formato condicional fresco con fórmulas
Una de las herramientas que creo que la gente simplemente no usa con la suficiente frecuencia es el formato condicional. Si está buscando información más avanzada sobre el formato condicional en Microsoft Excel, asegúrese de consultar el artículo de Sandy sobre el formato de datos en Microsoft Excel con formato condicional Automatically Format Data in Excel Hojas de cálculo con formato condicional Automáticamente Format Data en hojas de cálculo de Excel con formato condicional La función de formato condicional de Excel le permite formatear celdas individuales en una hoja de cálculo de Excel en función de su valor. Le mostramos cómo usar esto para diversas tareas cotidianas. Lee mas .
Con el uso de fórmulas, reglas o solo algunas configuraciones realmente simples, puede transformar una hoja de cálculo en un tablero automatizado que le muestra mucho sobre la información en la hoja de cálculo de un solo vistazo.
Para acceder al formato condicional, simplemente haga clic en la pestaña Inicio y haga clic en el icono de la barra de herramientas "Formato condicional".
En formato condicional, hay un montón de 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 las celdas en función de los datos dentro de esa celda. Este es probablemente el uso más común del formato condicional: hacer cosas como convertir una celda en rojo con fórmulas lógicas menores o mayores que. Brad Jones describe cómo usar muchos de estos en su artículo sobre cómo crear un Tablero de Excel. Visualizar sus datos y hacer que sus hojas de cálculo sean fáciles de usar con un Tablero de Excel. Visualice sus datos y haga que sus hojas de cálculo sean fáciles de usar con un Tablero de Excel. A veces, un formato simple de hoja de cálculo. no es lo suficientemente atractivo como para hacer que sus datos sean accesibles. Un tablero de instrumentos le permite presentar sus datos más importantes en un formato fácil de digerir. Lea más: vale la pena consultar si ese es su objetivo.
Una de las herramientas de formato condicional menos utilizadas es la opción de 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 del tablero.
Descubrí esto una vez que actualicé Microsoft Office 13+ Razones por las que debería actualizar a Microsoft Office 2016 13+ Razones por las que debería actualizar a Microsoft Office 2016 Microsoft Office 2016 ya llegó y es hora de que tome una decisión. La pregunta de la productividad es: ¿debería actualizar? Le proporcionamos las nuevas funciones y los motivos más nuevos para ayudarlo a ... Leer más y usamos el formato condicional en Microsoft Excel por primera vez.
Revisé los conjuntos de iconos y vi estas luces indicadoras LED que realmente había visto antes en algunas de las pantallas de automatización de fábrica que he programado en el pasado. Al hacer clic en "Gestionar 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 ícono que acaba de elegir.
Cuando haces clic en "Editar regla ...", ves el diálogo donde sucede la magia. Aquí es donde puede crear la fórmula lógica y las ecuaciones que mostrarán el ícono del tablero que desea. En mi ejemplo, controlo el tiempo invertido en diferentes tareas en comparación con el tiempo presupuestado. Si he gastado más de la mitad de mi presupuesto, quiero que se muestre una luz amarilla, y si estoy por encima del presupuesto, quiero que se ponga rojo.
Como puede ver, realmente no estoy presupuestando mi tiempo demasiado bien. Casi la mitad de mi tiempo se gasta mucho más de lo que he presupuestado.
Es hora de volver a enfocarme y administrar mejor mi tiempo. Usar la regla 80/20 de gestión del tiempo para priorizar tus tareas. Usar la regla 80/20 de gestión del tiempo para priorizar tus tareas. ¿Cómo maximizas tu tiempo? Si ya priorizas, delega y aún luchas por hacer todo, debes probar la regla 80/20, también conocida como Principio de Pareto. Lee mas !
Buscar elementos con la función VLookup
De acuerdo, tal vez eso no es lo suficientemente loco para ti. Tal vez no estés tan entusiasmado con las simples fórmulas lógicas que encienden y apagan las luces. Si te gusta usar funciones más avanzadas de Microsoft Excel, entonces he ido otro para ti.
Probablemente esté familiarizado con la función VLookup, que le permite buscar a través de una lista un artículo en particular en una columna, y devolver los datos de una columna diferente en la misma fila que ese artículo. 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é ocurre si se cambian?
En el siguiente ejemplo, ¿qué sucede si quiero encontrar la Tarea que realicé el 25/6/2013 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 al modo en que VLookup funciona normalmente. Buscar hojas de cálculo de Excel más rápido: Reemplazar BUSCARV con ÍNDICE y COINCIDIR Buscar hojas de cálculo Excel más rápido: Reemplazar BUSCARV Con INDEX y MATCH ¿Sigue usando VLOOKUP para buscar información en su hoja de cálculo? Así es como INDEX y MATCH pueden brindar una mejor solución. Lee mas . Si lee foros de usuarios profesionales de Microsoft Excel, encontrará muchas personas que dicen que esto no es posible con VLookup y que tiene 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 de ELEGIR. En este caso, la fórmula sería así:
"=VLOOKUP(DATE(2013, 6, 25), CHOOSE({1, 2}, E2:E8, A2:A8), 2, 0)" Lo que significa esta función es 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 columna es "2", pero como puede ver, la columna en la tabla de arriba es en realidad 1, ¿verdad?
Eso es cierto, pero lo que estás haciendo con la función "ELEGIR" es manipular los dos campos. Está asignando números de referencia de "índice" a rangos de datos, asignando las fechas al índice número 1 y las tareas al índice número dos. Por lo tanto, cuando escribe "2" en la función VLookup, en realidad se está refiriendo al índice número 2 en la función ELEGIR. Loco, eh?
Así que ahora VLookup usa la columna Fecha y devuelve los datos de la columna Tarea, aunque Tarea está a la izquierda. Ahora que conoces este pequeño bocado, imagina lo que puedes hacer.
Si está tratando de realizar otras tareas avanzadas de búsqueda de datos, asegúrese de revisar el artículo completo de Dann sobre cómo encontrar datos en Excel usando funciones de búsqueda. Encuentre todo en su hoja de cálculo de Excel con funciones de búsqueda. Encuentre todo en su hoja de cálculo de Excel con funciones de búsqueda. En un gigante Hoja de cálculo de Excel, CTRL + F solo te llevará hasta ahora. Sé inteligente y deja que las fórmulas hagan el trabajo duro. Las fórmulas de búsqueda ahorran tiempo y son fáciles de aplicar. Lee mas .
Fórmulas anidadas insanas para analizar cadenas
Como pueden ver, estoy intentando volverme un poco loco a medida que avanzamos, porque sé que hay algunos de ustedes que piensan: "... ¡bueno, eso no es una locura en absoluto!" Lo sé, sus estándares son altos. Estoy tratando de vivir a la altura de ellos. Aquí hay una fórmula más loca para ti.
Puede haber casos en los que importe datos en Microsoft Excel desde una fuente externa que consista en una cadena de datos delimitados. Una vez que ingrese los datos, desea analizar esos datos en los componentes individuales. Aquí hay un ejemplo del nombre, la dirección y la información del número de teléfono delimitados por el carácter ";"
A continuación, le mostramos cómo 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)" Esto 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 cadena, y luego toma el texto más a la izquierda de ese punto. En este caso, ese es "Ryan". Misión cumplida.
Anidación de fórmulas de Excel
Pero, ¿y las otras secciones? Bueno, para extraer las partes de la derecha, debe anidar varias funciones CORRECTAS para tomar la sección del texto hasta ese primer símbolo ";" y volver a ejecutar la función IZQUIERDA. Esto es lo que parece para extraer la parte del número de calle de la dirección.
"=LEFT((RIGHT(A2, LEN(A2)-FIND(";", A2))), FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2))), 1)-1)" Se ve loco, pero no es difícil reconstruirlo. 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 cadena necesita crear otro nido. Así que ahora solo tomas la loca ecuación "DERECHA" que habías creado para la última sección, y luego la pasas a una nueva fórmula DERECHA con la fórmula DERECHA anterior pegada a sí misma donde sea que veas "A2". Esto es lo que parece.
"(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 IZQUIERDA original donde haya un "A2". La fórmula final de flexión de la mente 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 una vez más. Sus fórmulas pueden ser realmente extravagantes, pero lo único que hace es cortar y pegar fórmulas largas en sí mismo, hacer nidos largos que funcionan realmente bien.
Sí, esto cumple con el requisito de "locura", 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 . Aparecerá una ventana donde puede dividir la cadena por cualquier delimitador que desee.
En un par de clics puede hacer lo mismo que la fórmula anterior ... pero ¿qué hay de divertido en eso?
Poniéndote loco con Microsoft Excel
Entonces ahí lo tienes. Las fórmulas anteriores demuestran cuán exagerada puede llegar una persona al crear fórmulas de Microsoft Excel para realizar ciertas tareas. A veces esas fórmulas no son realmente 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 así es con las fórmulas de Excel 16 Fórmulas de Excel que lo ayudarán a resolver problemas de la vida real 16 Fórmulas de Excel que lo ayudarán a resolver problemas de la vida real La herramienta correcta es la mitad del trabajo. Excel puede resolver cálculos y procesar datos más rápido de lo que puede encontrar su calculadora. Le mostramos las fórmulas clave de Excel y demostramos cómo usarlas. Lea más como cualquier otra cosa.
¿Estas fórmulas y técnicas eran lo suficientemente salvajes? ¿Tiene algún truco de fórmula sorprendente que forma su caja de herramientas para superusuarios de Microsoft Excel? ¡Comparta su opinión y comentarios en la sección de comentarios a continuación!
Crédito de la imagen: kues / Depositphotos