Direcciones mixtas en Excel 2016/2019

Resultados

Desde un punto de vista práctico y productivo, podemos decir que el empleo de referencias relativas y absolutas es imprescindible para obtener buenos resultados.

Matriz

En cambio, las referencias mixtas resultan algo más anecdóticas. Su uso se puede restringir a situaciones bastante concretas: cuando trabajamos con una matriz de celdas.

Desplazamiento

Se ha comprobado que una referencia relativa se mueve según el desplazamiento que realiza la fórmula, y una absoluta queda fija e independiente de dicho desplazamiento.

Variantes

Una referencia mixta se desplaza solamente en una dirección (horizontal o vertical), bloqueando la otra. Así que admite dos variantes. Como ejemplo, la dirección D4:

  • Como referencia relativa, se indica D4. No hay ningún bloqueo.
  • Como referencia absoluta, se indica $D. La celda está bloqueada, tanto en la columna como en la fila.
  • Como referencia mixta, se puede indicar $D4 o bien D. La primera bloquea la columna, la segunda bloquea la fila.

Caso práctico

1

Vamos a preparar una tabla que obtiene diferentes valores de porcentaje para diferentes cantidades. 

En el interior de la tabla debemos calcular los porcentajes correspondientes a los capitales de la fila superior, teniendo en cuenta los porcentajes de la columna izquierda.

Creamos la fórmula para la celda B2. De momento, introducimos las direcciones implicadas, con las referencias por defecto: referencias relativas.

2

Ahora debemos analizar si esta fórmula funcionará, mediante relleno rápido, para el resto de celdas en esta matriz.

Recomendamos copiar, mediante relleno rápido, una celda hacia abajo, y una celda hacia la derecha.

Debemos analizar el resultado. La mejor opción es usar la tecla F2 para editar las fórmulas, y comprobar el origen de los datos para cada una.

A veces se copian fórmulas, y como aportan un resultado (incorrecto, pero resultado), algunos usuarios se dan por satisfechos.

Esto es muy peligroso a nivel profesional: siempre hay que comprobar el buen funcionamiento de la fórmula.

3

Usando las referencias que conocemos hasta ahora, relativas y absolutas, no obtenemos buenos resultados.

  • Las referencias absolutas bloquean por completo la dirección de las celdas.
  • Las referencias relativas funcionan "a medias". Por ejemplo, en la fórmula copiada hacia abajo, la posición relativa de la celda del porcentaje es adecuada, pero no lo es en el caso de la celda que debe aportar el dato.

Lo adecuado es borrar esas fórmulas, y replantear el diseño de la fórmula en B2.

4

  • Las referencias absolutas bloquean tanto la columna como la fila, al incorporar el símbolo $ delante de la letra y delante del número.
  • Las referencias relativas no provocan ningún bloqueo, ya que no incorporamos ningún símbolo a la dirección de la celda.
  • Las referencias mixtas están pensadas para hacer un bloqueo parcial.  O bien bloqueamos la columna, o bien bloqueamos la fila.

Analicemos la fórmula inicial de la celda B2:

 =A2*B1

Pensemos en la implicación de copiar la fórmula (siempre mediante relleno rápido) hacia abajo y hacia la derecha.

Con respecto a la primera dirección, A2, analicemos por separado la columna y la fila. Con respecto a la columna:

  • Al copiar hacia abajo, la columna A se mantiene.
  • Al copiar hacia la derecha, la columna cambia, pero se debiera mantener.

Con respecto a la fila:

  • Al copiar hacia abajo, cambia. Eso es adecuado, pues en cada fila conviene que cambie el porcentaje.
  • Al copiar hacia la derecha, seguimos estando en la misma fila.

Del análisis realizado se deduce que:

  1. Conviene bloquear la columna, para que siempre se cojan datos de la columna A.
  2. No es necesario hacer nada con respecto a la fila.
  3. Por lo tanto, la referencia de la primera dirección, A2, debe ser mixta, bloqueando la columna, sin bloquear la fila: $A2.

5

Con respecto a la segunda dirección, B1, analicemos por separado la columna y la fila. Con respecto a la columna:

  • Al copiar hacia abajo, la columna B se mantiene.
  • Al copiar hacia la derecha, la columna cambia, pero es adecuado, para que vaya cambiando el dato.

Con respecto a la fila:

  • Al copiar hacia abajo, cambia. Eso no es adecuado, pues el dato siempre está en la fila 1.
  • Al copiar hacia la derecha, seguimos estando en la misma fila.

Del análisis realizado se deduce que:

  1. Conviene bloquear la fila, para que siempre se cojan datos de la fila 1.
  2. No es necesario hacer nada con respecto a la columna.
  3. Por lo tanto, la referencia de la segunda dirección, B1, debe ser mixta, bloqueando la fila, sin bloquear la columna: B

6

  1. La fórmula final que ha de introducirse en la celda B2 de manera que sea copiable a toda la matriz será por tanto: $A2*B
  2. Después copiamos hacia abajo desde el cuadro de relleno rápido.
  3. A continuación copiamos hacia la derecha.
  4. Analizamos el resultado y vemos que es correcto, en todas las celdas. En cada fórmula se selecciona correctamente el porcentaje y el dato correspondiente.

Las referencias mixtas son aquellas que marcan una posición fija respecto a una fila y variable respecto a una columna, o viceversa.

Una dirección mixta se indica mediante el literal de la columna y el número de la fila correspondiente, precedido uno de ellos por el símbolo $ ($A1 o A

En una misma fórmula podemos tener referencias relativas, absolutas y mixtas.

Para trabajar adecuadamente con Excel, hay que conocer bien unos pocos (muy pocos) conceptos. Seguramente, los más importantes que se pueden aprender con Excel, son los tipos de referencias de las celdas.

Ahora bien, no todo debe quedarse ahí. Los conceptos serán más difíciles de asimilar si no se ejercitan. Por lo que es altamente recomendable, casi imprescindible, practicar.