Auditoria de formulas

Luego de un tiempo, esa planilla de Excel , cargada de datos y fórmulas complejas, debe ser modificada.

El problema surge de inmediato: ¿cuáles son las celdas que le aportan a cada fórmula los valores de cálculo? La respuesta es la herramienta Auditoría de fórmulas .

Para comenzar, abra una planilla que contenga fórmulas. Luego, proceda a activar una celda con una ecuación y corra el comando Herramientas>Auditoría de fórmulas . Esto abre un menú con varias opciones.

Aquí queremos conocer las celdas que aportan datos a la fórmula; por lo tanto, seleccione el ítem Rastrear precedentes . De inmediato, desde cada una de estas celdas Excel traza flechas azules que convergen en la que contiene la fórmula. Así, de forma gráfica, sabemos cuáles son los datos de origen de ésta, cuya sintaxis, además, puede observarse en la Barra de fórmulas .

Lo inverso, es decir, a partir de una celda con datos ubicar las fórmulas que nutre, también es factible. Para eso, seleccione una celda de datos y corra la instrucción Herramientas>Auditoría de fórmulas >Rastrear dependientes . Una vez más, Excel expondrá en pantalla flechas azules desde la celda activa hacia aquéllas con fórmulas. Es más, si estas celdas son, a su vez, fuente de otra ecuación, si vuelve a ejecutar el mencionado comando, Excel dibujará nuevas flechas para indicar este segundo nivel de dependencia.

Antes que haya demasiadas líneas, tenga en cuenta que para eliminarlas (lo malo es que no deja ninguna) debe ejecutar la instrucción Herramientas>Auditoría de fórmulas>Quitar todas las flechas .

Sin equivocaciones

Al trabajar con una planilla suelen producirse errores. Un clásico: eliminar los valores de una celda que forma parte de una fórmula y que ésta muestre algún mensaje de error. Por ejemplo, #°DIV/0!

¿Cómo saber rápidamente qué pasa? Una vez más, active la celda que contiene la ecuación y corraHerramientas>Auditoría de fórmulas> Rastrear error . En este caso, además de señalar con las flechas azules las celdas precedentes, la aplicación despliega un cartel emergente explicando la causa del problema. En este caso, una división por cero.

Gabriel Tomich 

Glosario

Activar: acción de seleccionar una celda en Excel con objeto de editar su contenido, disparar un proceso, escribir una fórmula o aplicar un formato, por ejemplo.

Barra de fórmulas: herramienta de la interfaz de Excel que permite editar y corregir ecuaciones y modificar datos.

NOMBRE DEFINIDO

Obtener información acerca del uso de nombres

Un nombre es una abreviación con significado que facilita la comprensión del propósito de una referencia de celda, una constante, una fórmula o una tabla que pueda ser difícil de comprender a primera vista. En la siguiente información se muestran ejemplos comunes de nombres y cómo pueden mejorar la claridad y la comprensión.

Tipo de ejemplo	Ejemplo sin nombre	Ejemplo con nombre
Referencia	=SUMA(C20:C30)	=SUMA(VentasPrimerTrimestre)
Constante	=PRODUCTO(A5,8.3)	=PRODUCTO(Precio,ImpuestoVentasEstatal)
Fórmula	=SUMA(BUSCARV(A1;B1:F20;5;FALSE); -G5)	=SUMA(Nivel_Inventario;-Cant_Pedidos)
Tabla	C4:G36	=PrincipalesVentas06

Tipos de nombres

Existen varios tipos de nombres que se pueden crear y utilizar.

Nombre definido  Nombre que representa el valor de una celda, un rango de celdas, una fórmula o una constante. Puede crear su propio nombre definido, aunque Microsoft Office Excel en ocasiones lo hace por usted, por ejemplo cuando establece un área de impresión.

Nombre de tabla  Nombre de una tabla de Excel, que es una recopilación de datos sobre un tema concreto que están almacenados en registros (filas) y campos (columnas). Excel crea un nombre de tabla de Excel predeterminado Tabla1, Tabla2, etc., cada vez que se inserta una tabla de Excel, pero puede cambiarle el nombre para que tenga más significado. Para obtener más información acerca de las tablas de Excel, vea el tema sobre cómo utilizar referencias estructuradas con tablas de Excel.

Ámbito de un nombre

Todos los nombres tienen un ámbito, ya sea una hoja de cálculo concreta (lo que también se denomina nivel de hoja de cálculo local) o el libro completo (también denominado nivel de libro global). El ámbito de un nombre es la ubicación dentro de la cual el nombre es reconocido sin cualificación. Por ejemplo:

• Si ha definido un nombre, como Presupuesto_AÑO08, y su ámbito es Hoja1, ese nombre, si no está cualificado, sólo se reconoce en Hoja1, pero no en otras hojas mientras no cuente con cualificación.
  Para utilizar un nombre de hoja de cálculo local en otra hoja de cálculo, puede cualificarlo si lo precede del nombre de la hoja de cálculo, como en el siguiente ejemplo:
  Hoja1!Presupuesto_AÑO08
• Si ha definido un nombre, como Objetivos_Dept_Ventas, y su ámbito es el libro, ese nombre se reconocerá en todas las hojas de cálculo del libro, pero no en otros libros.

Un nombre siempre debe ser único en su ámbito. Excel no permite que se defina un nombre que no sea único en su ámbito. Sin embargo, es posible utilizar el mismo nombre en diferentes ámbitos. Por ejemplo, puede definir un nombre, como BeneficioBruto, para los ámbitos Hoja1, Hoja2 y Hoja3 en el mismo libro. Aunque todos los nombres son iguales, cada uno es único en su ámbito. Podría hacer esto para asegurarse de que una fórmula que emplee el nombre BeneficioBruto siempre haga referencia a las mismas celdas en el nivel de hoja de cálculo local.

Incluso es posible definir el mismo nombre, BeneficioBruto, para el nivel de libro global, aunque una vez más el ámbito es único. Sin embargo, en este caso puede producirse un conflicto de nombre. Para solucionarlo, Excel usa de forma predeterminada el nombre definido para la hoja de cálculo, ya que el nivel de hoja de cálculo local tiene prioridad sobre el nivel de libro global. Si desea anular la prioridad y utilizar el nombre del libro, puede eliminar la ambigüedad del nombre si le agrega el nombre del libro al principio como en el siguiente ejemplo:

ArchivoLibro!BeneficioBruto

Puede anular el nivel de hoja de cálculo local para todas las hojas de cálculo del libro, con excepción de la primera, que siempre utiliza el nombre local si hay un conflicto de nombre y que no se puede anular.

Definir y especificar nombres

Puede definir un nombre mediante:

• El cuadro Nombre de la barra de fórmulas   Es la mejor forma de crear un nombre de nivel de libro para un rango seleccionado.
• La creación de un nombre a partir de una selección   Puede crear nombres cómodamente a partir de rótulos existentes de filas y columnas mediante una selección de celdas de la hoja de cálculo.
• El cuadro de diálogo Nombre nuevo  Es lo mejor que se puede utilizar cuando se desea más flexibilidad a la hora de crear nombres, por ejemplo para especificar un ámbito de nivel de hoja de cálculo local o para crear un comentario del nombre.

 NOTA   De forma predeterminada, los nombres utilizan referencias de celda absolutas.

Puede escribir un nombre:

• Escribiendo   Escribiendo el nombre, por ejemplo, como un argumento para una fórmula.
• Mediante Fórmula Autocompletar  Use la lista desplegable Fórmula Autocompletar, que muestra automáticamente los nombres válidos.
• Seleccionando a partir del comando Utilizar en la fórmula  Seleccione un nombre definido en una lista disponible en el comando Utilizar en la fórmuladel grupo Nombres definidos de la ficha Fórmulas.

Auditar nombres

También puede crear una lista de nombres definidos en un libro. Busque un área con dos columna en blanco en la hoja de cálculo (la lista incluirá dos columnas, una para el nombre y otra para una descripción de éste). Seleccione una celda que será la esquina superior izquierda de la lista. En la ficha Fórmulas, en el grupo Nombres definidos, haga clic en Utilizar en la fórmula, en Pegar y, a continuación, en el cuadro de diálogo Pegar nombres, haga clic en Pegar lista.

Obtener información acerca de las reglas de sintaxis de los nombres

A continuación se enumeran las reglas de sintaxis que necesita conocer para crear y modificar nombres.

• Caracteres válidos  El primer carácter de un nombre debe ser una letra, un carácter de subrayado (_) o una barra invertida (\). El resto de los caracteres del nombre pueden ser letras, números, puntos y caracteres de subrayado.
   NOTA   No se pueden utilizar los caracteres en mayúsculas y minúsculas "C", "c", "R" o "r" como nombre definido, ya que todos ellos se utilizan como una abreviatura para seleccionar una fila o columna para la celda seleccionada al escribirlas en un cuadro de texto Nombre o Ir a.
• Referencias de celda no permitidas  Los nombres no pueden ser idénticos a una referencia de celda, como Z$100 o R1C1.
• Los espacios no son válidos   Los espacios no están permitidos como parte de un nombre. Use el carácter de subrayado (_) y el punto (.) como separadores de palabra, por ejemplo Impuesto_Ventas o Primer.Trimestre.
• Longitud del nombre  Un nombre puede contener hasta 255 caracteres.
• Distinción entre mayúsculas y minúsculas  Los nombres pueden incluir letras en mayúscula y minúscula. Excel no distingue entre caracteres en mayúscula y minúscula en los nombres. Por ejemplo, si ha creado el nombre Ventas y luego crea otro nombre VENTAS en el mismo libro, Excel le pedirá que seleccione un nombre único.

Definir un nombre para una celda o un rango de celdas de una hoja de cálculo

1. Seleccione la celda, el rango de celdas o las selecciones no adyacentes a los que desee poner un nombre.
2. Haga clic en el cuadro Nombre en el extremo izquierdo de la barra de fórmulas.
   
   Cuadro Nombre
3. Escriba el nombre que desea utilizar para referirse a su selección. Los nombres pueden tener hasta 255 caracteres de longitud.
4. Presione ENTRAR.

 NOTA   No podrá ponerle nombre a una celda mientras esté cambiando su contenido

Definir un nombre mediante una selección de celdas de la hoja de cálculo

Puede convertir rótulos existentes de filas y columnas en nombres.

1. Seleccione el rango al que desea poner nombre, incluidos los rótulos de las filas y columnas.
2. En el grupo Nombres definidos de la ficha Fórmulas, haga clic en Crear desde la selección.
   
3. En el cuadro de diálogo Crear nombres a partir de la selección, designe la ubicación que contiene los rótulos al activar la casilla Fila superior,Columna izquierda, Fila inferior o Columna derecha.

 NOTA   Un nombre creado mediante este procedimiento sólo hace referencia a las celdas que contienen los valores y no incluye los rótulos existentes de filas y columnas.

Definir un nombre mediante el cuadro de diálogo Nombre nuevo

1. En el grupo Nombres definidos de la ficha Fórmulas, haga clic en Definir nombre.
   
2. En el cuadro de diálogo Nombre nuevo, en el cuadro Nombre, escriba el nombre que desea utilizar para su referencia.
    NOTA   Los nombres pueden tener una longitud máxima de 255 caracteres.
3. Para especificar el ámbito del nombre, en la lista desplegable Ámbito, seleccione Libro o el nombre de una hoja de cálculo del libro.
4. De forma opcional escriba un comentario descriptivo de hasta 255 caracteres en el cuadro Comentario.
    NOTA   Si guarda el libro en Microsoft Office SharePoint Server 2007 Excel Services y especifica uno o más parámetros, el comentario se utiliza como información sobre pantallas en el panel de tareas Parámetros.
5. En el cuadro Se refiere a, siga uno de estos procedimientos:
   • Para especificar una referencia de celda, escriba la referencia de celda.
      SUGERENCIA   Se especifica la selección actual de forma predeterminada. Para especificar otras referencias de celda como argumento, haga clic en Contraer diálogo  (que reduce el cuadro de diálogo de forma temporal), seleccione las celdas en la hoja de cálculo y, a continuación, haga clic en Expandir diálogo .
   • Para especificar una constante, escriba = (signo igual) y, a continuación, escriba el valor de la constante.
   • Para especificar una fórmula, escriba = y, a continuación, escriba la fórmula.
6. Para finalizar y volver a la hoja de cálculo, haga clic en Aceptar.

 SUGERENCIA   Para hacer el cuadro de diálogo Nombre nuevo más ancho o largo, haga clic y arrastre el controlador situado en la parte inferior.

Administrar nombres mediante el cuadro de diálogo Administrador de nombres

Use el cuadro de diálogo Administrador de nombres para trabajar con todos los nombres definidos y los nombres de tabla del libro. Por ejemplo, es posible que desee buscar nombres con errores, confirmar el valor y la referencia de un nombre, ver o modificar comentarios descriptivos o determinar el ámbito. También puede ordenar y filtrar la lista de nombres y agregar, cambiar o eliminar nombres de una ubicación con facilidad.

Para abrir el cuadro de diálogo Administrador de nombres, en la ficha Fórmulas, en el grupo Nombres definidos, haga clic en Administrador de nombres.

Ver nombres

El cuadro de diálogo Administrador de nombres muestra la siguiente información acerca de cada uno de los nombres en un cuadro de lista:

Esta columna:	Muestra:
Icono y Nombre	Uno de los siguientes: Un nombre definido, indicado por un icono de nombre definido.  Un nombre de tabla, indicado por un icono de nombre de tabla.
Valor	El valor actual del nombre, como los resultados de una fórmula, una constante de cadena, un rango de celdas, un error, una matriz de valores o un marcador de posición si no es posible evaluar la fórmula. A continuación se muestran ejemplos representativos: "ésta es mi constante de cadena" 3,1459 {2003;12,2002;23,;2001,18} #REF! {...}
Se refiere a	La referencia actual del nombre. A continuación se muestran ejemplos representativos: =Hoja1!$A$3 =8,3 =HR!$A$1:$Z$345 =SUMA(Hoja1!A1,Hoja2!B2)
Ámbito	Un nombre de hoja de cálculo si el ámbito es el nivel de hoja de cálculo local. "Libro" si el ámbito es el nivel de libro global.
Comentario	Información adicional sobre el nombre de hasta 255 caracteres. A continuación se muestran ejemplos representativos: Este valor caducará el 2 de mayo de 2007. ¡No eliminar! ¡Nombre crítico! Basado en los números del examen de certificación ISO.  NOTA   Si guarda el libro en Microsoft Office SharePoint Server 2007 Excel Services y especifica uno o más parámetros, el comentario se utiliza como información sobre pantallas en el panel de tareas Parámetros.

  NOTAS  

• No puede utilizar el cuadro de diálogo Administrador de nombres mientras está cambiando el contenido de la celda.
• El cuadro de diálogo Administrador de nombres no muestra nombres definidos en Visual Basic para Aplicaciones (VBA) ni nombres ocultos (la propiedad Visible del nombre está establecida en "False").

Cambiar el tamaño de las columnas

• Para ajustar automáticamente el tamaño de la columna al valor más largo de la misma, haga doble clic en el lateral derecho del encabezado de la columna.

Ordenar nombres

• Para ordenar la lista de nombres en orden ascendente y descendente, haga clic de forma alternativa en el encabezado de la columna.

Filtrar nombres

Use los comandos de la lista desplegable Filtro para visualizar rápidamente un subconjunto de nombres. Al seleccionar cada comando se activa o desactiva la operación, lo que facilita la combinación o eliminación de las distintas operaciones de filtrado para obtener los resultados deseados.

Para filtrar la lista de nombres, siga uno o varios de estos procedimientos:

Seleccione:	Para:
Nombres en el ámbito de la hoja de cálculo	Mostrar únicamente aquellos nombres que sean locales en una hoja de cálculo.
Nombres en el ámbito del libro	Mostrar únicamente aquellos nombres que sean globales en un libro.
Nombres con errores	Mostrar únicamente aquellos nombres con valores que contengan errores (como #REF, #VALOR o #NOMBRE).
Nombres sin errores	Mostrar únicamente aquellos nombres con valores que no contengan errores.
Nombres definidos	Mostrar únicamente aquellos nombres definidos por el usuario o por Excel, como un área de impresión.
Nombres de tabla	Mostrar únicamente nombres de tabla.

Cambiar un nombre

Si cambia un nombre definido o un nombre de tabla, también se cambian todos los usos de ese nombre en el libro.

1. En el grupo Nombres definidos de la ficha Fórmulas, haga clic enAdministrador de nombres.
   
2. En el cuadro de diálogoAdministrador de nombres, haga clic en el nombre que desea cambiar y, a continuación, en Modificar.
    SUGERENCIA   También puede hacer doble clic en el nombre.
3. En el cuadro de diálogo Editar nombre, en el cuadro Nombre, escriba el nuevo nombre de la referencia.
4. En el cuadro Se refiere a, cambie la referencia y, a continuación, haga clic en Aceptar.
5. En el cuadro de diálogo Administrador de nombres, en el cuadro Se refiere a, cambie la celda, la fórmula o la constante representada por el nombre.
   • Para cancelar los cambios no deseados o accidentales, haga clic enCancelar  o presione ESC.
   • Para guardar los cambios, haga clic en Ejecutar  o presione ENTRAR.

 NOTA   El botón Cerrar sólo cierra el cuadro de diálogo Administrador de nombres. No es necesario ejecutar cambios que ya se hayan realizado.

Eliminar uno o más nombres

1. En el grupo Nombres definidos de la ficha Fórmulas, haga clic enAdministrador de nombres.
   
2. En el cuadro de diálogo Administrador de nombres, haga clic en el nombre que desee cambiar.
3. Seleccione uno o más nombres mediante uno de los siguientes procedimientos:
   • Para seleccionar un nombre, haga clic en él.
   • Para seleccionar más de un nombre en un grupo contiguo, haga clic y arrastre los nombres, o bien presione Mayús y haga clic con el botón del mouse en cada nombre del grupo.
   • Para seleccionar más de un nombre en un grupo no contiguo, presione CTRL y haga clic con el botón del mouse en cada nombre del grupo.
4. Haga clic en Eliminar. También puede presionar SUPR.
5. Haga clic en Aceptar para confirmar la eliminación.

 NOTA   El botón Cerrar sólo cierra el cuadro de diálogo Administrador de nombres. No es necesario ejecutar cambios que ya se hayan realizado.