En este video, veremos cómo crear una referencia dinámica a una hoja de trabajo en una fórmula.
Vamos a ver.
cómo combinar fórmulas en Excel
A veces, desea hacer referencia a una hoja de trabajo de forma dinámica en una fórmula, para que pueda cambiarse fácilmente.
En este libro de trabajo, tenemos 5 semanas de calificaciones de exámenes. Supongamos que queremos crear un panel simple que muestre la puntuación máxima, la puntuación mínima y la puntuación promedio para cualquier hoja de trabajo que seleccione un usuario.
Daremos este paso a la vez. Primero, agreguemos las fórmulas que usaremos y codifiquemos una referencia en una sola hoja de trabajo. De esa manera, podemos ver la sintaxis que necesitaremos.
Primero, ingresaré a la función MAX. Cuando entro en el rango, puede ver que Excel agrega automáticamente el nombre de la hoja más un signo de exclamación, luego el rango.
A continuación, copiaré esa fórmula dos veces (usando el atajo Control + apóstrofo para evitar que cambie la referencia) y regresaré y cambiaré los nombres de las funciones.
Una cosa a tener en cuenta: si una hoja de trabajo contiene caracteres de espacio, deberá encerrar el nombre de la hoja entre comillas simples. Si cambio el nombre de Week1 para incluir un espacio, verá que Excel agrega automáticamente estas comillas simples a la referencia.
Debido a que nunca se sabe cuándo el nombre de una hoja contendrá un espacio, tiene sentido crear una referencia que incluya automáticamente las comillas simples. De esa forma, siempre funcionará.
Bien, ahora hagamos que estas fórmulas sean dinámicas, para que podamos cambiar la semana en C5 y obtener resultados de una hoja de trabajo diferente.
Para hacer esto, usaremos la función INDIRECTO. La clave es construir una cadena de texto que represente una referencia completa y usar INDIRECTO para transformar el texto en una referencia real.
La fórmula que necesitaremos para esto está en la celda F9. Necesitamos concatenar el nombre de la hoja con una comilla simple al principio y una comilla simple + un signo de exclamación al final. Finalmente, concatenamos la referencia que necesitamos.
Copiaré esta sintaxis y la usaré para actualizar la primera fórmula. Para la hoja, vamos a tomar el valor de C5, así que haré de C5 una referencia absoluta. Como referencia, tenemos la gama D6: D38. Necesitamos encerrar esto entre comillas dobles ya que estamos construyendo una cadena de texto.
Excel encuentra el último valor en la columna
A continuación, necesito adaptar las otras dos fórmulas para usar INDIRECTO de la misma manera. Para ahorrar tiempo, simplemente copiaré la fórmula MAX y cambiaré los nombres de las funciones.
Ahora, cuando escribo un nombre de hoja diferente en C5, las fórmulas extraen información de esa hoja.
Finalmente, hagamos de C5 un menú desplegable, para que sea más fácil seleccionar una hoja de trabajo.
Obtenemos un error #REF con Week1 porque todavía hay un espacio en el nombre de la hoja. Cuando saco eso, todo funciona.
