Auditoria de fórmulas

Por favor compra el curso antes de comenzar esta lección.

Mostrar las fórmulas en lugar de los resultados

Cuando tenemos una planilla con multitud de fórmulas puede resultar útil visualizar todas ellas de un solo vistazo en vez de ir una a una supervisando que las instrucciones introducidas en cada una de las celdas son las correctas.

Es por ello que existe la opción de que se nos muestren en pantalla todas las fórmulas en lugar de los resultados de las mismas. Por ejemplo, partamos de la siguiente planilla en el que calculamos la prorrata de IVA:

prorrata-iva
En esta plantilla calculamos el porcentaje de prorrata de IVA. Puede sernos de utilidad revisar que todas las fórmulas utilizadas sean correctas.

Para visualizar las fórmulas en lugar de los resultados, activaremos la pestaña “FÓRMULAS” y haremos clic en la herramienta “Mostrar fórmulas· boton-mostrar-formulas del grupo “Auditoría de fórmulas” o alternativamente pulsaremos el método abreviado[Ctrl] + ` 

mostrando-formulas
Ahora Excel mostrará las fórmulas introducidas en lugar de los resultados de las mismas.

Hay que destacar que cuando activemos esta opción, el ancho de celda se ensancha automáticamente para que podamos visualizar correctamente la totalidad de las fórmulas introducidas en cada celda. Para ocultar las fórmulas y ver de nuevo los resultados, haremos clic en la herramienta boton-mostrar-formulas o usaremos el mismo método abreviado del teclado.

 

Localizar y resolver los errores en una fórmula

En lecciones anteriores ya leímos sobre los distintos resultados de error en Excel y a qué factores se debían. Ahora vamos a profundizar más en este tema.

Excel puede comprobar un número determinado de errores en las fórmulas, como pueden ser valores de error (p. ej. “#¿NOMBRE?”, “#¡VALOR!”…), números almacenados en forma de texto, etc. Cuando se detecta un problema, Excel muestra un triángulo en la esquina superior izquierda de la celda que alberga la fórmula. Los errores más habituales que nos muestra Excel son los siguientes:

  • #¿NOMBRE? – Este error se produce cuando Excel no reconoce el texto de la fórmula introducida en la celda, bien sea porque no está bien escrita la fórmula o porque no existe.
    • si queremos calcular la potencia de 3,4 elevado a 15 deberemos introducir la siguiente fórmula o función en la celda correspondiente “=POTENCIA(3,4;15)” en el caso que escribiésemos “=POTEN(3,4;15)” Excel nos mostraría el error #¿NOMBRE? dado a que no tiene registrado la función POTEN.
#¿NOMBRE?
Hemos escrito mal la fórmula y ésta no es reconocida por Excel
  • #¡VALOR! – Este tipo de error se produce cuando Excel detecta que se está realizando cálculos con tipos de datos distintos entre sí, como datos numéricos, texto o fecha y hora.
    • Ejemplo – La potencia de 4 elevado a una letra o texto “=POTENCIA(4;A)” dará como resultado el error #¡VALOR!, puesto que el carácter “A” no es un número.
#¡VALOR!
El carácter A no es un número y se nos devuelve el error #¡VALOR!
  • #¡NUM! – Este error se produce cuando Excel detecta cuando una fórmula o función requiere un dato numérico y se ha introducido un dato no numérico como una letra o una fecha.
    • También puede ser que el resultado del cálculo resulta tan grande o pequeño que Excel no pueda mostrarlo.
    • Ejemplo – El cálculo de la potencia de 1000 elevado a 103 da como resultado el error #¡NUM! dado a que Excel no puede mostrar el valor tan elevado de dicho cálculo.
#¡NUM!
Elevar un número a una potencia 103 nos daría un número con una extensísima cantidad de dígitos.
  • #¡DIV/0! – Este error se produce cuando Excel detecta que se ha realizado un cálculo de un número dividido por 0 o por una celda que no contiene ningún valor.
    • Ejemplo – El cálculo de una celda que contenga la siguiente fórmula “=2500/0” dará como resultado el error #¡DIV/0!
#¡DIV/0!
Excel no puede dividir un número entre cero
  • #¡NULO! – Este error se genera cuando queremos relacionar mediante algún cálculo o función rangos de datos o celdas que no interseccionan.
#¡NULO!
No hay sección de intersección entre los dos rangos a sumar.
  • #N/A –Este error se genera en las hojas de cálculo de Excel cuando se utilizan funciones de búsqueda o coincidencia de datos los cuales no se existen en el rango de búsqueda especificado.
    • Ejemplo – usar la función Buscarv para encontrar un valor que no existe en el rango de búsqueda especificado.
#N/A
No existe el valor contenido en C4 en el rango buscado.
  • ##### – Este error se muestra cuando :
    • El valor introducido o calculado en la celda es más grande que el ancho de la columna
    • Se ha calculado o introducido un valor negativo de una fecha. Ejemplo – multiplicar una fecha por el valor de -2.
#####
Las fechas en negativo no son soportadas

 

Mostrar los errores

Cuando en una celda el resultado de una fórmula muestra un valor de error como puede ser #¿NOMBRE?, #N/A, #¡DIV0!, etc., es posible localizar todas las celdas que intervienen en la fórmula.

Habiendo seleccionado la celda que contiene el error, activaremos la pestaña “FÓRMULAS” para a continuación abrir la lista asociada a la herramienta “Comprobación de errores” del grupo “Auditoría de fórmulas”. Por último haremos clic en la opción “Rastrear error”.

rastrear-errores

Aparecen en pantalla una serie de flechas de auditoría. Las flechas rojas unen la celda que ha producido el error con aquellas que hacen referencia a ella, mientras que las flechas azules designan los antecedentes de la celda que ha provocado el error.

Por ejemplo, en la imagen anterior el error producido en la celda F8 se produce porque estamos intentando dividir el total de pedidos de un producto (E8) entre cero (E13, la cual es una celda vacía). Así lo señalan las flechas azules que parten de E8 y E13 y señalan a la celda que muestra el error.

Por su parte, en la imagen siguiente se muestra el resultado de haber rastreado el error en la celda F9. Como vemos el valor que debería aparecer en la misma es la suma de las celdas F4 a F8. Esta vez el error se produce porque una de estas celdas que se incluyen en el sumatorio contiene un error en su resultado, y así lo muestra la flecha roja que parte de F5. Esta vez las flechas azules señalan las celdas a las que hace referencia el error producido en esta última celda.

errores-flechas
Las flechas indican la causa del error.

Para borrar las flechas de auditoria, haremos clic en la herramienta “Quitar flechas” del grupo “Auditoría de fórmulas” de la pestaña “FÓRMULAS”.

quitar-flechas
Podemos mostrar y ocultar las flechas del rastreo de los errores.

 

Etiquetas de lecciones: error, Excel
Volver a: Excel para Contables y Controllers > Auditoria de Fórmulas