Referencia de celda relativa y absoluta, y formateo
En esta lección, analizaremos las referencias de las celdas, cómo copiar o mover una fórmula y el formato de las celdas. Para comenzar, aclaremos qué entendemos por referencias de celda, que sustentan gran parte de la potencia y la versatilidad de las fórmulas y funciones. Una comprensión concreta de cómo funcionan las referencias de celda le permitirá sacar el máximo provecho de sus hojas de cálculo de Excel!
NAVEGACION ESCOLAR- ¿Por qué necesitas fórmulas y funciones??
- Definiendo y creando una fórmula
- Referencia de celda relativa y absoluta, y formateo
- Funciones útiles que deberías conocer
- Búsquedas, gráficos, estadísticas y tablas dinámicas
Nota: asumiremos que ya sabes que una celda es uno de los cuadrados de la hoja de cálculo, organizada en columnas y filas a las que se hace referencia mediante letras y números que se ejecutan horizontal y verticalmente.
¿Qué es una referencia de celda??
Una "referencia de celda" significa la celda a la que se refiere otra celda. Por ejemplo, si en la celda A1 tienes = A2. Entonces A1 se refiere a A2.
Revisemos lo que dijimos en la Lección 2 sobre filas y columnas para poder explorar más las referencias de celdas.
Las celdas en la hoja de cálculo son referidas por filas y columnas. Las columnas son verticales y etiquetadas con letras. Las filas son horizontales y etiquetadas con números..
La primera celda de la hoja de cálculo es A1, lo que significa la columna A, fila 1, B3 se refiere a la celda ubicada en la segunda columna, la tercera fila, etc..
Para propósitos de aprendizaje sobre referencias de celdas, a veces las escribimos como filas, columnas, esto no es una notación válida en la hoja de cálculo y simplemente pretende aclarar las cosas..
Tipos de referencias celulares
Hay tres tipos de referencias celulares..
Absoluto: esto significa que la referencia de celda permanece igual si copia o mueve la celda a cualquier otra celda. Esto se hace anclando la fila y la columna, por lo que no cambia cuando se copia o mueve.
Relativo: la referencia relativa significa que la dirección de la celda cambia a medida que la copia o mueve; es decir, la referencia de celda es relativa a su ubicación.
Mezclado: esto significa que puede elegir anclar la fila o la columna al copiar o mover la celda, de modo que una cambie y la otra no. Por ejemplo, podría anclar la referencia de la fila y luego mover una celda hacia abajo dos filas y cuatro columnas y la referencia de la fila seguirá siendo la misma. Explicaremos esto más adelante..
Referencias Relativas
Vamos a referirnos a ese ejemplo anterior. Supongamos que en la celda A1 tenemos una fórmula que simplemente dice = A2. Eso significa que la salida de Excel en la celda A1 es lo que se ingresa en la celda A2. En la celda A2, hemos escrito "A2", por lo que Excel muestra el valor "A2" en la celda A1.
Ahora, supongamos que necesitamos hacer espacio en nuestra hoja de cálculo para obtener más datos. Necesitamos agregar columnas arriba y filas a la izquierda, así que tenemos que mover la celda hacia abajo y hacia la derecha para hacer espacio.
A medida que mueve la celda a la derecha, aumenta el número de columna. A medida que lo mueves hacia abajo, el número de fila aumenta. La celda a la que apunta, la referencia de la celda, también cambia. Esto se ilustra a continuación:
Continuando con nuestro ejemplo, y mirando el gráfico a continuación, si copia el contenido de la celda A1 dos a la derecha y cuatro abajo, lo ha movido a la celda C5.
Copiamos la celda dos columnas a la derecha y cuatro abajo. Esto significa que hemos cambiado la celda a la que se refiere dos a través y cuatro hacia abajo. A1 = A2 ahora es C5 = C6. En lugar de referirse a A2, ahora la celda C5 se refiere a la celda C6.
El valor mostrado es 0 porque la celda C6 está vacía. En la celda C6 escribimos "I am C6" y ahora C5 muestra "I am C6".
Ejemplo: Fórmula de texto
Probemos con otro ejemplo. ¿Recuerda de la Lección 2, donde tuvimos que dividir un nombre completo en nombre y apellido? ¿Qué pasa cuando copiamos esta fórmula??
Escriba la fórmula = DERECHA (A3, LEN (A3) - BUSCAR (“,”, A3) - 1) o copie el texto en la celda C3. No copie la celda real, solo el texto, copie el texto, de lo contrario actualizará la referencia.
Puede editar el contenido de una celda en la parte superior de una hoja de cálculo en el cuadro al lado de donde dice "fx". Ese cuadro es más largo que el ancho de una celda, por lo que es más fácil de editar..
Ahora tenemos:
Nada complicado, acabamos de escribir una nueva fórmula en la celda C3. Ahora copia C3 a las celdas C2 y C4. Observe los resultados a continuación:
Ahora tenemos los nombres de Alexander Hamilton y Thomas Jefferson.
Utilice el cursor para resaltar las celdas C2, C3 y C4. Apunta el cursor a la celda B2 y pega el contenido. Mira lo que sucedió, recibimos un error: "#REF". ¿Por qué es esto??
Cuando copiamos las celdas de la columna C a la columna B, se actualizó la columna de referencia una a la izquierda = DERECHA (A2, LEN (A2) - FIND (“,”, A2) - 1).
Cambió todas las referencias de A2 a la columna a la izquierda de A, pero no hay ninguna columna a la izquierda de la columna A. Por lo tanto, la computadora no sabe lo que quiere decir.
La nueva fórmula en B2, por ejemplo, es = DERECHA (#REF!, LEN (#REF!) - FIND (“,”, # REF!) - 1) y el resultado es #REF:
Copiar una fórmula a un rango de celdas
Copiar celdas es muy útil porque puede escribir una fórmula y copiarla en un área grande y la referencia se actualiza. Esto evita tener que editar cada celda para asegurarse de que apunta al lugar correcto.
Por "rango" nos referimos a más de una celda. Por ejemplo, (C1: C10) significa todas las celdas desde la celda C1 a la celda C10. Así que es una columna de células. Otro ejemplo (A1: AZ1) es la fila superior de la columna A a la columna AZ.
Si un rango cruza cinco columnas y diez filas, indica el rango escribiendo la celda superior izquierda y la inferior derecha, por ejemplo, A1: E10. Esta es un área cuadrada que cruza filas y columnas y no solo parte de una columna o parte de una fila.
Aquí hay un ejemplo que ilustra cómo copiar una celda a múltiples ubicaciones. Supongamos que queremos mostrar nuestros gastos proyectados del mes en una hoja de cálculo para poder hacer un presupuesto. Hacemos una hoja de cálculo como esta:
Ahora copie la fórmula en la celda C3 (= B3 + C2) al resto de la columna para obtener un saldo corriente para nuestro presupuesto. Excel actualiza la referencia de la celda a medida que la copia. El resultado se muestra a continuación:
Como puedes ver, cada nueva celda se actualiza. relativo a la nueva ubicación, por lo que la celda C4 actualiza su fórmula a = B4 + C3:
La celda C5 se actualiza a = B5 + C4, y así sucesivamente:
Referencias Absolutas
Una referencia absoluta no cambia cuando mueve o copia una celda. Usamos el signo $ para hacer una referencia absoluta: para recordar eso, piense en un signo de dólar como un ancla.
Por ejemplo, ingrese la fórmula = $ A $ 1 en cualquier celda. El $ delante de la columna A significa que no cambia la columna, el $ delante de la fila 1 significa que no cambia la columna cuando copia o mueve la celda a cualquier otra celda.
Como puede ver en el siguiente ejemplo, en la celda B1 tenemos una referencia relativa = A1.Cuando copiamos B1 a las cuatro celdas debajo de ella, la referencia relativa = A1 cambia a la celda de la izquierda, entonces B2 se convierte en A2, B3 convertirse en A3, etc. Esas celdas obviamente no tienen ningún valor ingresado, por lo que la salida es cero.
Sin embargo, si usamos = $ A1 $ 1, como en C1 y lo copiamos a las cuatro celdas debajo de él, la referencia es absoluta, por lo que nunca cambia y la salida es siempre igual al valor en la celda A1.
Supongamos que está haciendo un seguimiento de su interés, como en el ejemplo a continuación. La fórmula en C4 = B4 * B1 es la “tasa de interés” * “saldo” = “interés por año”.
Ahora, ha cambiado su presupuesto y ha ahorrado $ 2,000 adicionales para comprar un fondo mutuo. Supongamos que es un fondo de tasa fija y paga la misma tasa de interés. Ingrese la nueva cuenta y el saldo en la hoja de cálculo y luego copie la fórmula = B4 * B1 de la celda C4 a la celda C5.
El nuevo presupuesto se ve así:
El nuevo fondo mutuo gana $ 0 en intereses por año, lo que no puede ser correcto ya que la tasa de interés es claramente del 5 por ciento..
Excel resalta las celdas a las que hace referencia una fórmula. Puede ver arriba que la referencia a la tasa de interés (B1) se mueve a la celda vacía B2. Deberíamos haber hecho la referencia a B1 absoluta escribiendo $ B $ 1 usando el signo de dólares para anclar la referencia de fila y columna.
Reescriba el primer cálculo en C4 para leer = B4 * $ B $ 1 como se muestra a continuación:
Luego copia esa fórmula de C4 a C5. La hoja de cálculo ahora se ve así:
Dado que copiamos la fórmula una celda hacia abajo, es decir, aumentamos la fila en una, la nueva fórmula es = B5 * $ B $ 1. La tasa de interés de los fondos mutuos ahora se calcula correctamente, porque la tasa de interés está anclada a la celda B1.
Este es un buen ejemplo de cuándo podría usar un "nombre" para referirse a una celda. Un nombre es una referencia absoluta. Por ejemplo, para asignar el nombre "tasa de interés" a la celda B1, haga clic con el botón derecho en la celda y luego seleccione "definir nombre".
Los nombres pueden referirse a una celda o un rango, y puede usar un nombre en una fórmula, por ejemplo, = tasa de interés * 8 es lo mismo que escribir = $ B $ 1 * 8.
Referencias mixtas
Referencias mixtas son cuando ya sea La fila o columna está anclada.
Por ejemplo, supongamos que usted es un agricultor que está haciendo un presupuesto. También eres dueño de una tienda de piensos y vendes semillas. Vas a plantar maíz, soja y alfalfa. La hoja de cálculo a continuación muestra el costo por acre. El "costo por acre" = "precio por libra" * "libras de semillas por acre": eso es lo que le costará plantar un acre.
Ingrese el costo por acre como = $ B2 * C2 en la celda D2. Usted está diciendo que quiere anclar el precio por columna de la libra. Luego copia esa fórmula a las otras filas en la misma columna:
Ahora quieres saber el valor de tu inventario de semillas. Usted necesita el precio por libra y la cantidad de libras en el inventario para saber el valor del inventario..
Añadimos dos columnas: "libra de semillas en el inventario" y luego "valor del inventario". Ahora, copie la celda D2 a F4 y observe que la referencia de la fila en la primera parte de la fórmula original ($ B2) se actualiza a la fila 4 pero la columna permanece fija porque el $ lo ancla a "B".
Esta es una referencia mixta porque la columna es absoluta y la fila es relativa.
Referencias circulares
Una referencia circular es cuando una fórmula se refiere a sí misma..
Por ejemplo, no puede escribir c3 = c3 + 1. Este tipo de cálculo se llama "iteración", lo que significa que se repite. Excel no admite la iteración porque calcula todo solo una vez.
Si intenta hacer esto escribiendo SUM (B1: B5) en la celda B5:
Aparece una pantalla de advertencia:
Excel solo le dice que tiene una referencia circular en la parte inferior de la pantalla, por lo que es posible que no lo note. Si tiene una referencia circular y cierra una hoja de cálculo y la abre de nuevo, Excel le dirá en una ventana emergente que tiene una referencia circular.
Si tiene una referencia circular, cada vez que abra la hoja de cálculo, Excel le dirá con esa ventana emergente que tiene una referencia circular..
Referencias a otras hojas de trabajo
Un "libro de trabajo" es una colección de "hojas de trabajo". En pocas palabras, esto significa que puede tener varias hojas de cálculo (hojas de cálculo) en el mismo archivo de Excel (libro de trabajo). Como puede ver en el siguiente ejemplo, nuestro libro de ejemplo tiene muchas hojas de trabajo (en rojo).
Las hojas de trabajo de forma predeterminada se denominan Hoja1, Hoja2, etc. Para crear una nueva, haga clic en "+" en la parte inferior de la pantalla de Excel..
Puede cambiar el nombre de la hoja de trabajo a algo útil como "préstamo" o "presupuesto" haciendo clic derecho en la pestaña de la hoja de trabajo que se muestra en la parte inferior de la pantalla del programa Excel, seleccionando cambiar nombre y escribiendo un nombre nuevo.
O simplemente puede hacer doble clic en la pestaña y cambiarle el nombre.
La sintaxis para una referencia de la hoja de trabajo es = worksheet! Cell. Puede usar este tipo de referencia cuando se usa el mismo valor en dos hojas de trabajo, y algunos ejemplos podrían ser:
- El día de hoy
- Tasa de conversión de moneda de dólares a euros
- Todo lo que sea relevante para todas las hojas de trabajo del libro.
A continuación se muestra un ejemplo de la hoja de trabajo "interés" que hace referencia a la hoja de trabajo "préstamo", celda B1.
Si nos fijamos en la hoja de trabajo del “préstamo”, podemos ver la referencia al monto del préstamo:
A continuación…
Esperamos que ahora tenga un firme conocimiento de las referencias de las celdas, incluyendo relativa, absoluta y mixta. Ciertamente hay mucho.
Eso es todo para la lección de hoy, en la Lección 4, discutiremos algunas funciones útiles que quizás desee conocer para el uso diario de Excel..
