Transcripción del video
Cada celda de nuestra hoja tiene una referencia única y que podemos utilizar para incluir su valor en los cálculos.
En este libro tengo una hoja llamada Datos, la cual tiene un listado de facturas con sus montos y para obtener el total ingresaré el símbolo igual en la celda E1 y en seguida la función SUMA y la referencia al rango B2:B10 y al pulsar Entrar obtendremos la suma de todas las celdas.
Nuestra fórmula funciona muy bien hasta que nos piden que la coloquemos en una hoja diferente.
Voy a copiar las celdas D1 y E1 y en seguida pulsaré el botón “Hoja Nueva” y pegaré el contenido en esta hoja.
Observa que la fórmula de la celda E1 muestra el valor cero y al activar dicha celda vemos la misma fórmula que habíamos creado en la hoja anterior, pero nos devuelve un resultado diferente y eso se debe a que la fórmula hace referencia a las celdas de la hoja actual y no a las celdas de la hoja Datos.
Para comprobarlo, ingresaré el número 1 en la celda B2 y también en la celda B3 y puedes ver que el resultado de la fórmula se actualiza.
Esto comprueba que la fórmula hace referencia a las celdas de esta hoja, pero nosotros necesitamos obtener la suma de los valores en la hoja Datos.
Una manera de modificar las referencias es activando la celda y haciendo clic en el botón Insertar función que abrirá un cuadro de diálogo que nos mostrará los argumentos de la función SUMA.
El primer argumento tiene la referencia al rango B2:B10 y para cambiar esta referencia haré clic en el botón de selección y borraré la referencia actual y en seguida haré clic en la hoja Datos y seleccionaré el rango B2:B10 y pulsaré de nuevo el botón de selección y finalmente el botón Aceptar.
Con este cambio la fórmula de la Hoja1 nos devolverá la suma de los valores de la hoja Datos y quiero que observes la referencia que Excel ha insertado en nuestra fórmula.
La nueva referencia es la palabra Datos seguida del símbolo de exclamación y el rango B2:B10.
La palabra Datos se refiere al nombre de la hoja que queremos acceder y después de dicho nombre siempre debemos colocar el símbolo de exclamación y posteriormente la dirección de la celda o rango.
Hagamos otra prueba para comprobar el funcionamiento de las referencias a otras hojas.
Primero activaré la hoja Datos para que puedas ver que la celda B3 tiene el valor 265.
Ahora activaré la Hoja1 y haré clic en cualquier celda e ingresaré el símbolo igual y enseguida el nombre de la hoja Datos y el símbolo de exclamación y finalmente la referencia a la celda B3.
Al pulsar entrar obtenemos el valor 265 que proviene de la celda B3 de la hoja Datos.
Considera que la fórmula nos trae el valor de la celda sin formato y por eso nuestra celda no muestra el símbolo de moneda.
Con esto podemos concluir que, para hacer referencia a una celda de otra hoja, debemos comenzar escribiendo el nombre de la hoja seguido de un símbolo de exclamación y en seguida la dirección de la celda o rango.
Existe una pequeña variante de este tipo de referencias y es cuando el nombre de la hoja tiene un espacio en blanco o caracteres que no son alfanuméricos.
Voy hacer clic derecho sobre la etiqueta de la hoja Datos y seleccionaré la opción Mover o copiar.
En este cuadro de diálogo seleccionaré la opción Mover al final y activaré la casilla Crear una copia y finalmente pulsaré el botón Aceptar.
Excel coloca un nombre predeterminado a la nueva hoja, pero dicho nombre tiene un espacio en blanco y el número 2 encerrado en paréntesis.
Ahora activaré la Hoja1 y en la celda E2 ingresaré el símbolo igual y la función SUMA seguida del paréntesis y en seguida pulsaré el botón Insertar función.
Haré clic en el botón seleccionar del primer argumento y activaré la hoja Datos2 y seleccionaré el rango B2:B10 y pulsaré de nuevo el botón seleccionar rango y el botón Aceptar.
Observa que la fórmula nos devuelve el resultado correcto, pero en este caso se ha encerrado el nombre de la hoja Datos2 en comillas simples.
Entonces podemos modificar un poco la conclusión a la que habíamos llegado anteriormente para decir que, una referencia a una celda de otra hoja se construye comenzado con el nombre de la hoja, pero si dicha hoja tiene un espacio en blanco, o algún carácter especial, será indispensable encerrarlo en comillas simples.
Posteriormente debemos insertar el símbolo de admiración y terminamos con la dirección de la celda o rango.
Este es el método que debemos utilizar para hacer referencia a celdas que se encuentren en otra hoja.
Pero la pregunta inevitable es ¿Podemos crear referencias a celdas que se encuentran en otro libro?
A continuación, te mostraré cómo construir ese tipo de referencias, pero antes haré clic en el botón Guardar para hacer permanentes los cambios y en seguida haré clic en Archivo > Nuevo > Libro en blanco y tendremos dos libros abiertos.
En el nuevo libro activaré una celda e ingresaré el símbolo igual y la función SUMA seguida de un paréntesis y pulsaré el botón Insertar función.
A continuación, haré clic en el botón de selección de rango y posteriormente activaré la ventana del libro con el que estábamos trabajando originalmente y activaré la hoja Datos y seleccionaré el rango B2:B10.
Para terminar, pulsaré el botón de selección de rango y finalmente el botón Aceptar y puedes ver que la celda del libro muestra el resultado correcto.
Pero analicemos con detenimiento la referencia que ha creado Excel.
Observa que a la izquierda del nombre de la hoja Datos se han insertado un par de corchetes y dentro se encuentra el nombre del libro que para este ejemplo se llama referencias-externas.xlsx.
Tanto el nombre del libro, como la hoja ya han sido encerrados en comillas simples y seguidos por el símbolo de exclamación y la referencia al rango que en este caso es absoluta.
Así es que, para hacer referencia a una celda de otro libro, debemos utilizar el nombre del libro entre corchetes seguido del nombre de la hoja y ambos encerrados en comillas simples.
Luego insertamos el símbolo de admiración y finalmente colocamos la dirección de la celda o rango.
Vamos a repetir la acción, pero ahora ingresando manualmente la fórmula en la celda inferior.
Ingresaré el símbolo igual y la función SUMA y abro paréntesis y en seguida ingresaremos la referencia al rango del otro libro que vamos a sumar.
Comienzo con una comilla simple y abriré los corchetes.
En seguida escribiré el nombre del archivo que quiero acceder y que para este ejemplo es el nombre referencias, guion medio, externas, punto xlsx y finalmente cierro los corchetes.
Ahora escribiré la hoja que quiero acceder y en esta ocasión será la hoja Datos espacio en blanco, abro paréntesis, el número 2, cierro paréntesis y termino con la comilla simple.
Recuerda que la hoja Datos 2 es la que copiamos en nuestro ejemplo anterior.
Continuamos nuestra referencia ingresando el símbolo de exclamación y finalmente el rango B2:B10.
Con esto hemos creado la referencia al rango B2:B10 de la hoja Datos2 del libro Referencias-externas.xlsx.
Cierro el paréntesis de la función SUMA y al pulsar Entrar obtenemos el resultado correcto.
Estas referencias también tienen una pequeña variante y es cuando el libro está cerrado.
En ese caso, nuestra referencia debe cambiar un poco y te lo mostraré a continuación.
Activaré la ventana del libro referencias externas y lo voy a cerrar.
Y en la ventana del libro que ha quedado abierto puedes observar que Excel ha modificado la referencia para incluir la ruta completa al archivo, es decir ha insertado la ubicación exacta de la carpeta en mi disco duro donde se encuentra el archivo de Excel.
Esto no lo puedes olvidar, cuando quieres acceder los datos de un libro de Excel que está cerrado, será necesario incluir la ruta completa del archivo.
Para finalizar esta clase hagamos un resumen de los tipos de referencias externas que tenemos disponibles.
Para acceder a una celda en otra hoja de nuestro libro utilizamos el nombre de la hoja, el símbolo de exclamación y finalmente la dirección de la celda o rango.
Si el nombre de la hoja tiene espacios en blanco o caracteres especiales, debemos rodear el nombre con comillas simples.
Para crear la referencia a la celda de un libro abierto, ingresamos el nombre del archivo Excel encerrado en corchetes y en seguida colocamos el nombre de la hoja.
Ya que es muy probable que en estos dos nombres existan caracteres que no son alfanuméricos, entonces será necesario encerrar ambos en comillas simples.
En seguida colocamos el símbolo de exclamación y la dirección de la celda o rango.
Y finalmente, para hacer referencia a un libro que está cerrado, debemos insertar la ruta completa a la carpeta que lo contiene.
Dicha ruta debe estar a la izquierda de los corchetes con el nombre del archivo.
Es importante aprender a utilizar adecuadamente las referencias en Excel ya que son un elemento indispensable en la construcción de las fórmulas.