7 consejos para trabajar con fórmulas de Excel

Varios elementos pueden ayudarlo a ser lo más productivo posible al escribir y corregir fórmulas de Excel. Puede ver todas sus fórmulas a la vez y corregir los errores uno por uno. Puede utilizar asistentes de complementos para ayudar a escribir funciones.

Precedencia del operador maestro

Uno de los factores más importantes en la redacción de fórmulas es hacer que los operadores sean correctos, y no me refiero a los operadores de compañías telefónicas. Esto tiene que ver con operadores matemáticos, ya sabes, pequeños detalles como signos más y signos de multiplicación, y dónde van los paréntesis. La precedencia del operador , el orden en el que se realizan las operaciones, puede marcar una gran diferencia en el resultado. Tiene una manera fácil de mantener en orden la prioridad de sus operadores. Todo lo que tienes que recordar es "Por favor, disculpe a mi querida tía Sally".

Esta frase es un mnemónico para lo siguiente:

• Paréntesis

• Exponentes

• Multiplicación

• División

• Adición

• Sustracción

Por lo tanto, los paréntesis tienen la primera (más alta) precedencia y la resta tiene la última precedencia. Bueno, para ser honesto, la multiplicación tiene la misma precedencia que la división y la suma tiene la misma precedencia que la resta, ¡pero entiendes la idea!

Por ejemplo, la fórmula = 1 + 2 × 15 es igual a 31. Si crees que debería ser igual a 45, ¡será mejor que vayas a visitar a tu tía! La respuesta es igual a 45 si incluye paréntesis, como este: = (1 + 2) × 15.

Obtener el orden correcto de los operadores es fundamental para el bienestar de su hoja de trabajo. Excel genera un error cuando los números de paréntesis abiertos y cerrados no coinciden, pero si quiere sumar dos números antes de la multiplicación, Excel no sabe que simplemente dejó los paréntesis fuera.

Unos pocos minutos de refrescar su memoria por orden del operador pueden ahorrarle muchos dolores de cabeza en el futuro.

Mostrar fórmulas

En caso de que no lo haya notado, es un poco difícil ver sus fórmulas sin editarlas accidentalmente. Esto se debe a que cada vez que esté en modo "editar" y la celda activa tenga una fórmula, la fórmula puede incorporar la dirección de cualquier otra celda en la que haga clic. Esto estropea totalmente las cosas.

¿No sería fácil si pudiera mirar todas sus fórmulas? ¡Hay una manera! Es simple. Haga clic en Archivo en la parte superior izquierda del espacio de trabajo de Excel, haga clic en Opciones, haga clic en la pestaña Avanzado y desplácese hacia abajo hasta la sección Opciones de visualización para esta hoja de trabajo.

Opciones de configuración.

Observe la casilla de verificación Mostrar fórmulas en celdas en lugar de sus resultados calculados. Este cuadro le dice a Excel que, para cualquier celda que tenga fórmulas, se muestre la fórmula en sí en lugar del resultado calculado. La siguiente figura muestra una hoja de trabajo que muestra las fórmulas. Para volver a la vista normal, repita estos pasos y anule la selección de la opción. ¡Esta opción facilita ver cuáles son todas las fórmulas!

Ver fórmulas de forma sencilla.

Puede editar funciones accidentalmente incluso cuando haya seleccionado la opción Ver fórmulas. Tenga cuidado al hacer clic alrededor de la hoja de trabajo.

Arreglar fórmulas

Suponga que su hoja de trabajo tiene algunos errores. ¡Que no cunda el pánico! Les sucede incluso a los usuarios más experimentados, y Excel puede ayudarlo a descubrir qué va mal. En la pestaña Fórmulas de la sección Auditoría de fórmulas está el botón Comprobación de errores. Al hacer clic en el botón, se muestra el cuadro de diálogo Comprobación de errores, que se muestra aquí. Es decir, el cuadro de diálogo aparece si su hoja de trabajo tiene algún error. De lo contrario, simplemente aparece un mensaje que indica que la verificación de errores se completó. ¡Es así de inteligente!

Comprobando errores.

Cuando no son errores, aparece el cuadro de diálogo y se pega alrededor mientras trabaja en cada error. Los botones Siguiente y Anterior le permiten recorrer todos los errores antes de que se cierre el cuadro de diálogo. Para cada error que encuentre, usted elige qué acción tomar:

• Ayuda sobre este error: conduce al sistema de ayuda y muestra el tema del tipo de error en particular.

• Mostrar pasos de cálculo: se abre el cuadro de diálogo Evaluar fórmula y puede observar paso a paso cómo se calcula la fórmula. Esto le permite identificar el paso particular que causó el error.

• Ignorar error: tal vez Excel esté equivocado. Ignore el error.

• Editar en la barra de fórmulas: esta es una forma rápida de arreglar la fórmula usted mismo si no necesita ninguna otra ayuda.

El cuadro de diálogo Comprobación de errores también tiene un botón Opciones. Al hacer clic en el botón, se abre la pestaña Fórmulas del cuadro de diálogo Opciones de Excel. En la pestaña Fórmulas, puede seleccionar configuraciones y reglas sobre cómo se reconocen y desencadenan los errores.

Usa referencias absolutas

Si va a usar la misma fórmula para un grupo de celdas, como las que van hacia abajo en una columna, el mejor método es escribir la fórmula una vez y luego arrastrarla hacia las otras celdas usando el controlador de relleno. El problema es que cuando arrastra la fórmula a nuevas ubicaciones, las referencias relativas cambian.

A menudo, esta es la intención. Cuando hay una columna de datos y una columna adyacente de fórmulas, por lo general, cada celda de la columna de fórmula se refiere a su vecino en la columna de datos. Pero si todas las fórmulas hacen referencia a una celda que no es adyacente, la intención generalmente es que todas las celdas de la fórmula hagan referencia a una referencia de celda que no cambia. Haga que esto funcione correctamente utilizando una referencia absoluta a la celda.

Para usar una referencia absoluta a una celda, use el signo de dólar ($) antes del número de fila, antes de la letra de la columna o antes de ambos. Haga esto cuando escriba la primera fórmula, antes de arrastrarla a otras celdas, o tendrá que actualizar todas las fórmulas.

Por ejemplo, no escriba esto:

= A4 x (B4 + A2)

Escríbalo de esta manera en su lugar:

= A4 x (B4 + $ A $ 2)

De esta manera, todas las fórmulas hacen referencia a A2 sin importar dónde las copie, en lugar de que esa referencia se convierta en A3, A4, y así sucesivamente.

Utilice la auditoría de fórmulas

Hay precedentes y dependientes. Hay referencias externas. Hay interacción en todas partes. ¿Cómo se puede rastrear de dónde provienen y hacia dónde van las referencias de fórmulas?

Utilice las herramientas de auditoría de fórmulas, ¡así es como! En la pestaña Fórmulas está la sección Auditoría de fórmulas. En la sección hay varios botones que controlan la visibilidad de las flechas de seguimiento de auditoría.

Fórmulas de auditoría.

La barra de herramientas de auditoría de fórmulas tiene varias características que le permiten recorrer sus fórmulas. Además de mostrar flechas de rastreo, la barra de herramientas también le permite verificar errores, evaluar fórmulas, verificar datos no válidos y agregar comentarios a las hojas de trabajo.

Usar formato condicional

Así como la función SI devuelve un cierto valor cuando la condición del primer argumento es verdadera y otro valor cuando es falsa, el formato condicional le permite aplicar un cierto formato a una celda cuando una condición es verdadera. En la pestaña Inicio en la sección Estilos hay un menú desplegable con muchas opciones de formato condicional.

Esta figura muestra algunos valores que se han tratado con formato condicional. El formato condicional le permite establecer la condición y seleccionar el formato que se aplicará cuando se cumpla la condición. Por ejemplo, puede especificar que la celda se muestre en negrita y cursiva cuando el valor que contiene sea mayor que 100.

Aplicar un formato cuando se cumple una condición.

Las condiciones se establecen como reglas. Los tipos de reglas son

• Formatee todas las celdas según sus valores.

• Aplicar formato solo a las celdas que contienen. . . .

• Aplicar formato solo a los valores de clasificación superior o inferior.

• Aplicar formato solo a los valores que estén por encima o por debajo del promedio.

• Formatee solo valores únicos o duplicados.

• Utilice una fórmula para determinar qué celdas formatear.

Cuando la condición es verdadera, el formateo puede controlar lo siguiente:

• Fronteras

• Configuración de fuente (estilo, color, negrita, cursiva, etc.)

• Relleno (color o patrón de fondo de una celda)

Las celdas también se pueden formatear con esquemas de color o imágenes de iconos colocadas en la celda.

Utilice la validación de datos

En la pestaña Datos, en la sección Herramientas de datos, está Validación de datos. La validación de datos le permite aplicar una regla a una celda (o celdas) de modo que la entrada debe cumplir con la regla. Por ejemplo, se puede configurar una celda para que acepte solo una entrada de número entero entre 50 y 100.

Configuración de la validación de datos.

Cuando la entrada no pasa la regla, se muestra un mensaje.

Atrapado haciendo una mala entrada.

El mensaje de error se puede personalizar. Por ejemplo, si alguien ingresa el número incorrecto, el mensaje de error que se muestra puede decir Noodlehead: ¡aprenda a contar! No dejes que el jefe lo vea.