Manual de Excel Intermedio
Modulo 3
Definición de Nombres y Auditorias
Objetivo
Crear rangos que tengan nombres que faciliten cálculos avanzados y las auditoria de formulas.
Al finalizar el MODULO, serás capaz de:
Definir y usar nombres en fórmulas y funciones
Auditoría de fórmulas y funciones
Contenidos:
Generar rangos con nombres
Generar funciones o formulas con nombres
Administrar nombres en planillas Excel
Auditar formulas y funciones
Rastrear precedentes
Rastrear dependientes
Rastrear errores
Comprobar errores
Página 1
Manual de Excel Intermedio
Definir y usar nombres en fórmulas o funciones
Si utiliza nombres, sus fórmulas serán mucho más fáciles de entender, manejar y
administrar. Puede definir un nombre para un rango de celdas, una función, una constante
o una tabla.
Una vez que haya adoptado la práctica de utilizar nombres en su libro, podrá actualizar,
auditar y administrar esos nombres con mayor facilidad que si lo hiciera de forma
tradicional.
Para acceder a los nombres, deberemos acceder a la Opción Formulas, Ficha Nombres
Definidos
Crea, Edita y Elimina todos los nombres utilizados en este libro, los
nombres pueden utilizarse como sustitutos para referencias a celdas.
Asigna nombres a celdas, para referirse a ellas por su nombre en
las formulas o funciones
Elige un nombre en este libro y lo inserta en la formula o función
actual.
Genera automáticamente nombre desde las celdas seleccionadas
1. Acerca del uso de nombres
Un nombre es una abreviación con un significado que hace mas fácil 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, debido al uso de referencias de celdas.
Ejemplos comunes de nombres y cómo pueden facilitar su uso y de que se tratan las
referencias.
Tipo de
Ejemplo sin nombre Ejemplo con nombre
ejemplo
Referencia =PROMEDIO(J40:J60) =PROMEDIO(VentasAnuales)
Constante =PRODUCTO(A5,8.3) =PRODUCTO(Valortotal,Impuesto)
Fórmula =SUMA(BUSCARV(A1;B1:F20;5;FALSE); -G5) =SUMA(Stock;-Cant_Pedidos)
Tabla C150:W150 =produccionaño2011
Página 2
Manual de Excel Intermedio
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 Excel en
ocasiones lo hace por defecto, como cuando se 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.
Ámbito de un nombre
Todos los nombres tienen un ámbito, ya sea una hoja de cálculo solamente (nivel de hoja
de cálculo local) o el libro completo (nivel de libro global).
El ámbito de un nombre es la ubicación dentro de la cual el nombre es reconocido sin su
debida identificación, ya sea local o global:
Si ha definido un nombre, como VENTAS2011, 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.
Si lo que desea es 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, Hoja1!
VENTAS2011
Si ha definido un nombre, como CONSTANTES_IMPUESTOS_ANUALES, 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, es decir no debe encontrarse duplicado.
Excel. Sin embargo, es posible utilizar el mismo nombre en diferentes ámbitos.
Puede definir un nombre, como GananciasTotales, 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
GananciasTotales, siempre haga referencia a las mismas celdas en el nivel de hoja de
cálculo local.
Incluso es posible definir el mismo nombre, GananciasTotales, 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 ejemplo:
Página 3
Manual de Excel Intermedio
ArchivoLibro!GananciasTotales
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.
2. Reglas de sintaxis de los nombres
Existen reglas de sintaxis que se 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, a excepción de las 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 [Link].
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 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.
Video Demostrativo
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.
Video Demostrativo
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.
Video Demostrativo
Puede escribir un nombre:
Escribiendo Escribiendo el nombre, por ejemplo, como un argumento para una
fórmula.
Página 4
Manual de Excel Intermedio
Video Demostrativo
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órmula del
grupo Nombres definidos de la ficha Fórmulas.
Video Demostrativo
Auditar nombres
También puede crear una lista de nombres definidos en un libro, para saber con precisión
que rangos y que nombres esta trabajando..
Video Demostrativo
Pasos:
1. Abra Excel y posiciónese en la Hoja2
2. 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).
3. Seleccione una celda que será la esquina superior izquierda de la lista.
4. En la ficha Fórmulas, en el grupo Nombres definidos,
5. haga clic en Utilizar en la fórmula, en Pegar
6. en el cuadro de diálogo Pegar nombres, haga clic en Pegar lista.
3. Definir un nombre para una celda o rango de celdas, usando la Ficha
Nombres Definidos.
Pasos:
1. Mantenga abierto su archivo Excel de práctica, en la Hoja3
2. Seleccione la celda, el rango de celdas o las selecciones no adyacentes a
los que desee poner un nombre.
Página 5
Manual de Excel Intermedio
3. Haga clic en el cuadro Nombre en el extremo izquierdo de la barra de
fórmulas.
4. Escriba el nombre que desea utilizar para referirse a su selección. Los
nombres pueden tener hasta 255 caracteres de longitud.
5. Presione ENTRAR.
4. 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 de seleccion.
1. Mantenga abierto su archivo Excel de práctica, en la Hoja3
2. Seleccione el rango al que desea poner nombre, incluidos los rótulos de las
filas y columnas.
3. 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.
Página 6
Manual de Excel Intermedio
5. 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.
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.
Página 7
Manual de Excel Intermedio
4. De forma opcional se puede escribir un comentario descriptivo de hasta
255 caracteres en el cuadro Comentario.
5. En el cuadro Hace referencia a, puede usar una de estas formas de
agregar las celdas de la selección:
Para especificar una referencia de celda, escriba la referencia de
celda, para lo cual, se ve 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 .
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.
6. Administrar nombres mediante el cuadro de diálogo Administrador de
nombres
El cuadro de diálogo Administrador de nombres, en la Ficha Nombres Definidos se
utiliza para trabajar con todos los nombres definidos, en todos los ámbitos y los nombres
de tabla del libro. Es posible que desee buscar nombres con errores, confirmar el valor y/o
la referencia de un nombre especifico, ver o modificar comentarios descriptivos o
determinar su ámbito.
También se pueden ordenar y filtrar la lista de nombres y agregar nuevos, modificar o
eliminar nombres desde esta herramienta con gran 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:
Página 8
Manual de Excel Intermedio
Esta
Muestra:
columna:
Un nombre definido, indicado por un icono de nombre definido, para celdas y rangos
Icono y
de celdas.
Nombre
De existir, un nombre de tabla, indicado por un icono de nombre de tabla.
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
Valor evaluar la fórmula. A continuación se muestran ejemplos representativos:
La referencia actual del nombre. A continuación se muestran ejemplos representativos:
Se refiere a =Hoja1!$C$1:$A$3
=Hoja1!$F$1
Un nombre de hoja de cálculo si el ámbito es el nivel de hoja de cálculo local.
Ámbito
"Libro" si el ámbito es el nivel de libro global.
Información adicional sobre el nombre de hasta 255 caracteres. A continuación se muestran
ejemplos representativos:
Comentario
Este valor caducará el 2 de mayo de 2007.
No borrar parámetros críticos.
Aclaración importante:
No puede utilizar el cuadro de diálogo Administrador de nombres mientras está
cambiando el contenido de la celda.
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.
Página 9
Manual de Excel Intermedio
7. 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 en
Administrador de nombres.
2. En el cuadro de diálogo Administrador de nombres, haga clic en el
nombre que desea cambiar y, a continuación, en Editar, o 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, o el rango de celdas de la selección.
4. A continuación, haga clic en Aceptar.
Página 10
Manual de Excel Intermedio
5. Si lo que se desea es Eliminar, seleccione el nombre a eliminar y
seleccione el botón Eliminar
6. El botón Cerrar cierra el cuadro de diálogo Administrador de nombres,
una vez que ya se efectuaron los cambios necesarios.
Auditoría de Fórmulas
Excel nos ofrece una serie de herramientas de auditorías de fórmulas que pueden ser de
gran utilidad, para la revisión de errores que se puedan encontrar en formulas que
hubiésemos podido usar en nuestros cálculos.
Para acceder a estas herramientas debemos ir a la pestaña Fórmulas donde aparece una
serie de opciones como podemos ver en la imagen.
Dibuja unas flechas indicando dónde están las celdas
involucradas en la fórmula.
Dibuja flechas indicando a qué fórmula pertenece la celda
seleccionada, si es que pertenece a alguna fórmula.
Elimina las flechas indicativas de celdas creadas con Rastrear
dependientes o Rastrear precedentes.
Página 11
Manual de Excel Intermedio
Muestra la formula en cada celda, en lugar del valor resultante
Analiza la hoja de trabajo actual en busca de errores.
Abre el cuadro de diálogo de Evaluar fórmula para que pueda ver la
fórmula y los resultados de la fórmula de la celda activa.
Muestra la ventana de inspección para hacer un seguimiento del valor de las
celdas.
1. Comprobación de errores
Al introducir una fórmula manualmente podemos cometer un error de sintaxis como por
ejemplo podemos escribir =Produto(A1:A9), lo cual provoca un error de tipo #¿NOMBRE?.
Si pulsamos sobre la pestaña Formulas y hacemos clic en el botón Comprobación de
errores... nos aparece el cuadro de diálogo Comprobaciones de errores como el que
vemos en la imagen donde nos informa de qué tipo de error se ha detectado y en
ocasiones nos puede ofrecer una corrección.
Si pulsamos en Ayuda sobre este error nos aparece la ventana de ayuda de la aplicación,
sobre este tipo de error.
El botón Mostrar pasos de cálculo... nos abre un cuadro de diálogo donde evalúa la
fórmula y nos informa dónde se encuentra el error, si es en el nombre de la función o si
está en los parámetros de la fórmula.
Página 12
Manual de Excel Intermedio
El botón Omitir error salta el error actual y pasa al siguiente que encuentre en la hoja de
trabajo.
El botón Modificar en la barra de fórmulas nos envía a la barra de fórmulas para editar
la fórmula que contiene el error.
También dentro del botón comprobación de errores, esta la opción de “Rastrear error”,
donde se sigue la secuencia de la formula de error a través de una línea azul, que sigue
las celdas que participan en la formula.
2. Rastreo de Precedentes y dependientes.
Se entiende por precedentes a aquellas celdas que afectan el valor de la celda
inspeccionada. Dependientes son las celdas afectadas por la celda seleccionada, y por
dependientes a aquellas que se ven afectadas por los valores de la celda seleccionada
Video Demostrativo
Página 13
Manual de Excel Intermedio
Pasos:
1. Abrimos la aplicación Excel, con un nuevo libro, ubicándonos en la Hoja1
2. Nos ubicamos en la celda A1, y empezamos a ingresar la siguiente tabla de datos.
3. Luego de ingresados los datos, procedemos a efectuar nuestros cálculos, para lo
cual debemos posicionarnos en la celda G2, donde calcularemos le sueldo liquido
que es la suma del sueldo base más el bono, “=suma(E2+F2)”
4. Luego se presiona Enter y se procede a usar el cursor de Auto llenado hasta la
celda G9.
5. Mediante el uso de la función [Link], deberemos calcular en la celda C12, el
sueldo total de los hombres de la empresa, y en la celda C13, el sueldo total de
las mujeres de la empresa.
Página 14
Manual de Excel Intermedio
6. Luego, de obtener los resultados, nos posicionamos en la celda G2, desde donde
presionaremos el botón “Rastrear Precedentes”, apareciendo esto en nuestra
planilla.
7. Si necesitamos saber los resultados de que celdas, esta relacionados a esta,
debemos hacer clic en “Rastrear dependientes”.
Página 15
Manual de Excel Intermedio
8. Si ya analizamos la formula y como afecta a su entorno de la tabla, podemos
quitar las flechas con el botón “quitar flechas”.
9. También es posible visualizar las formulas contenidas en las celdas a través del
clic sobre el botón “Mostrar formulas”.
Agregar inspección
A través de este botón , es posible supervisar los valores de determinadas celdas al
mismo tiempo que se realizan modificaciones en la hoja.
Video Demostrativo
Pasos:
1. Trabajando en la misma hoja del ejemplo anterior.
2. Nos ubicamos en la celda G2, y se presiona el botón “Ventana Inspección”.
3. En esta ventana, se presiona el botón “Agregar inspección”, que se realiza
sobre la celda seleccionada, de inmediato aparece la opción de modificar la
celda seleccionada.
Página 16
Manual de Excel Intermedio
4. Si la celda seleccionada es la correcta solo debemos hacer clic en botón
Agregar, con lo cual se despliega la información respecto a la celda, de su
fórmula, su valor resultante, libro y Hoja.
5. Si se desea agregar otra inspección solo se debe seguir la misma
secuencia.
6. De querer eliminar una inspección, solo se debe seleccionar y presionar la
opción “Eliminar selección”.
Página 17