Para realizar cálculos precisos en Excel, es esencial comprender cómo funcionan los diferentes tipos de referencias de celda.
Referencias A1 vs.R1C1
Las hojas de cálculo de Excel contienen muchas celdas y (de forma predeterminada) cada celda se identifica por su letra de columna seguido de su número de fila. Esto se conoce como referencia al estilo A1. Ejemplos: A1, B4, C6
Estilo de referencia A1
Opcionalmente, puede cambiar a Modo de referencia R1C1 para hacer referencia a la fila de una celda y número de columna. En lugar de referirse a la celda A1, se referirá a R1C1 (fila 1, columna 1). La celda C4 se denominaría R4C3.
Estilo de referencia R1C1
La referencia al estilo R1C1 es extremadamente poco común en Excel. A menos que tenga una buena razón, probablemente debería ceñirse al modo de referencia de estilo A1 predeterminado. Sin embargo, si usa VBA, probablemente encontrará este estilo de referencia.
Cambiar al estilo de referencia R1C1
Para cambiar el estilo de referencia, vaya a Archivo> Opción> Fórmula. Marque la casilla junto a Estilo de referencia R1C1.
Rangos con nombre
Una de las funciones menos utilizadas de Excel es la función Rangos con nombre. En lugar de hacer referencia a una celda (o grupo de celdas) por su ubicación de celda (por ejemplo, B3 o R3C2), puede nombrar ese rango y simplemente hacer referencia al nombre del rango en sus fórmulas.
Para nombrar un rango:
- Seleccione la celda o celdas que desea nombrar
- Haga clic dentro del cuadro Nombre del rango
- Ingrese su nombre deseado
- Pulsa Enter
Ahora puede hacer referencia a la celda A1 escribiendo = range_name1 en su lugar. Esto es muy útil cuando se trabaja con libros de trabajo grandes con varias hojas de trabajo.
Rango de celdas
Al usar las funciones integradas de Excel, es posible que deba hacer referencia a rangos de celdas. Los rangos de celdas aparecen así "A1: B3". Esta referencia se refiere a todas las celdas entre A1 y B3: celdas A1, A2, A3, B1, B2, B3.
Para seleccionar un rango de celdas al ingresar una fórmula:
- Escriba el rango (separe el rango inicial y final con un punto y coma)
- Use su mouse para hacer clic en la primera referencia de celda, mantenga presionado el botón del mouse y arrastre el rango deseado.
- Mantenga presionada la tecla shift y navegue con las teclas de flecha para seleccionar su rango
Referencias absolutas (congeladas) y relativas
Al ingresar referencias de celda dentro de fórmulas, puede usar referencias relativas o absolutas (congeladas). Las referencias relativas se moverán proporcionalmente cuando copie la fórmula en una nueva celda. Las referencias absolutas se mantendrán sin cambios. Veamos algunos ejemplos:
Referencia relativa
Una referencia relativa en Excel se ve así
= A1
Cuando copia y pega una fórmula con referencias relativas, las referencias relativas se moverán proporcionalmente. A menos que especifique lo contrario, sus referencias de celda serán relativas (descongeladas) de forma predeterminada.
Ejemplo: si copia "= A1" una fila hacia abajo, la referencia cambia a "= A2".
Referencias de células absolutas (congeladas)
Si no desea que sus referencias de celda se muevan cuando copia una fórmula, puede "congelar" sus referencias de celda agregando signos de dólar ($ s) delante de la referencia que desea congelar. Ahora, cuando copie y pegue la fórmula, la referencia de celda permanecerá sin cambios. Puede elegir congelar la referencia de fila, la referencia de columna o ambas.
A1: nada está congelado
$ A1: la columna está congelada, pero la fila no está congelada
A $ 1: la fila está congelada, pero la columna no está congelada
$ A $ 1: tanto la fila como la columna están congeladas
Atajo de referencia absoluta
Agregar manualmente signos de dólar ($ s) en sus fórmulas no es muy práctico. En cambio, mientras crea su fórmula, use la tecla F4 para alternar entre referencias de celda absolutas / relativas.
Ejemplo de referencia de celda absoluta
¿Cuándo necesitaría realmente congelar una referencia de celda? Un ejemplo común es cuando tiene celdas de entrada a las que se hace referencia con frecuencia. En el siguiente ejemplo, queremos calcular el impuesto sobre las ventas para cada cantidad de elementos del menú. El impuesto sobre las ventas es constante en todos los artículos, por lo que haremos referencia a la celda del impuesto sobre las ventas repetidamente.
Para encontrar el impuesto total sobre las ventas, ingrese la fórmula "= (B3 * C3) * $ C $ 1" en la columna D y copie la fórmula hacia abajo.
Referencia mixta
Es posible que haya oído hablar de referencias de celdas mixtas. Una referencia mixta es cuando la referencia de fila o columna está bloqueada (pero no ambas).
Referencia mixta
Recuerde, al usar la tecla "F4" puede recorrer sus referencias de celda absolutas y relativas.
Referencias de celda: insertar y eliminar filas / columnas
Es posible que se pregunte qué sucede con las referencias de su celda cuando inserta o elimina filas / columnas.
La referencia de celda se actualizará automáticamente para hacer referencia a la celda original. Este es el caso independientemente de si la referencia de celda está congelada.
Referencias 3D
A veces, es posible que deba trabajar con varias hojas de trabajo con patrones de datos idénticos. Excel le permite consultar varias hojas a la vez sin necesidad de ingresar manualmente cada hoja de trabajo. Puede hacer referencia a un rango de hojas de forma similar a como haría referencia a un rango de celdas. El ejemplo "Sheet1: Sheet5! A1" haría referencia a las celdas A1 en todas las hojas de Sheet1 a Sheet5.
Veamos un ejemplo:
Desea sumar las unidades totales vendidas para cada producto en todas las tiendas. Cada tienda tiene su propia hoja de trabajo y todas las hojas de trabajo tienen un formato idéntico. Podría crear una fórmula similar a esta:
Esto no es demasiado difícil con solo cuatro hojas de trabajo, pero ¿y si tuviera 40 hojas de trabajo? ¿Realmente le gustaría agregar manualmente cada referencia de celda?
En su lugar, puede usar una referencia 3D para hacer referencia a varias hojas a la vez con facilidad (similar a cómo puede hacer referencia a un rango de celdas).
¡Ten cuidado! El orden de sus hojas de trabajo es importante. Si mueve otra hoja entre las hojas de referencia (StoreA y StoreD), esa hoja se incluirá. Por el contrario, si mueve una hoja fuera del rango de hojas (antes de StoreA o después de StoreD), ya no se incluirá.
Referencia de celda circular
Una referencia de celda circular es cuando una celda se refiere a sí misma. Por ejemplo, si el resultado de la celda B1 se utiliza como entrada para la celda B1, se crea una referencia circular. La celda no necesita referirse directamente a sí misma. Puede haber pasos intermedios.
Ejemplo:
En este caso, la fórmula de la celda B2 es "A2 + A3 + B2". Debido a que está en la celda B2, no puede usar B2 en la ecuación. Esto activará una referencia circular y el valor en la celda "B2" se establecerá automáticamente en "0".
Por lo general, las referencias circulares son el resultado de un error del usuario, pero hay circunstancias en las que es posible que desee utilizar una referencia circular. El ejemplo principal de utilizar una referencia circular es calcular valores de forma iterativa. Para hacer esto, debes ir a Archivo> Opciones> Fórmulas y Cálculo iterativo habilitado:
Referencias externas
En ocasiones, al calcular datos, es posible que deba consultar datos fuera de su libro de trabajo. Esto se denomina referencia externa (enlace).
Para seleccionar una referencia externa mientras crea una fórmula, navegue hasta el libro de trabajo externo y seleccione su referencia como lo haría normalmente.
Para navegar al otro libro de trabajo, puede usar el atajo CTRL + TAB o ir a Ver> Cambiar de ventana.
Una vez que haya seleccionado la celda, verá que una referencia externa que se ve así:
Observe que el nombre del libro de trabajo está entre corchetes [].
Una vez que cierre el libro de trabajo al que se hace referencia, la referencia mostrará la ubicación del archivo:
Cuando vuelva a abrir el libro de trabajo que contiene el enlace externo, se le pedirá que habilite la actualización automática de enlaces. Si lo hace, Excel abrirá el valor de referencia con el valor actual del libro. ¡Incluso si está cerrado! ¡Ten cuidado! Puede que quieras esto o no.
Rangos con nombre y referencias externas
¿Qué sucede con su referencia de celda externa cuando se agregan o eliminan filas / columnas del libro de trabajo de referencia? Si ambos libros de trabajo están abiertos, las referencias de celda se actualizan automáticamente. Sin embargo, si ambos libros de trabajo no están abiertos, las referencias de celda no se actualizarán y dejarán de ser válidas. Esta es una gran preocupación cuando se vincula a libros de trabajo externos. Muchos errores son causados por esto.
Si enlaza a libros de trabajo externos, debe nombrar la referencia de celda con un rango con nombre (consulte la sección anterior para obtener más información). Ahora su fórmula se referirá al rango nombrado sin importar qué cambios ocurran en el libro de trabajo externo.
