Solución al Reto

Hola jóvenes

¿Qué tal les fue con el reto de referencias de celdas?
No recibí respuesta de ninguno de ustedes. Supongo que se les olvido y se concentraron en el ejercicio enviado por correo. Así que cierro la recepción de respuestas de el reto de la entrada anterior y en ésta entrada comento la solución al ejercicio sobre referencias de celdas relativas y absolutas,

Observación general

El trabajo consiste en obligarse a pensar en escribir una sola formula (en cada tabulación) y esa formula es la que va a poder copiar a las demás celdas (de la tabulación).

Solución al ejercicio

La formula a transformar a referencias de celdas en la hoja de cálculo es: 

r = (a + b) /c * M

En el caso de los valores para la constante "M" y la variable "c", se presenta la siguiente tabulación de datos:

Recordemos que las celdas coloreadas en  naranja indican las etiquetas y en las celdas en amarillo se escriben los valores respectivos:

• La constante "M" se encuentra en la celda C10
• En el caso de la variable "c", como las tabulaciones presentan 4 posiciones para escribir la formula (r1, r2, r3, r4), por ello "c" tiene 4 posiciones de su valor (las cuales se corresponderán en cada caso con la versión de cada tabulación): la celda en la que se encuentra el valor de "c1"  es F12,   el valor de "c2" se encuentra en F12, el valor de "c3" se encuentra en H12, y por último el valor para "c4" se encuentra en I12.

Resolvamos el ejemplo de la primera tabulación de datos:

Transformemos la fórmula de modo que en lugar de las variables se empleen las referencias de celdas de la hoja de cálculo:

r = (a + b) /c * M

Vamos a escribir la formula en la primera celda, es decir en la primera posición para "r1": celda D22, por lo tanto:

• "a" se encuentra en la celda B22 
• "b" se encuentra en C22
• en el caso de la variable "c" se tomara la referencia de celda de "c1" esto es F12
• no olvidemos que la constante M se encuentra en C10

de esta forma, sin detenernos aun a observar cual será referencia absoluta, la formula quedaría en D22 como se expresa a continuación:

=(B22 + C22) / F12 * C10

Pero la fórmula no queda allí ya que debemos recordar que se pidió analizar cómo ajustar las referencias de celda para que se escriba la formula una sola vez y se copie en las cuatro columnas de celdas para resultados (etiquetadas como r1, r2, r3, r4), así debemos tener presente lo siguiente: La formula se va a copiar tanto hacia abajo como a la derecha; por ello:

• En el caso de la celda de las variables a y b:

1. Al copiar la fórmula para abajo el renglón se afectará, sin embargo, es algo conveniente ya que se necesita que al copiar la formula de la celda D22 a la celda D23, los valores a los que se haga referencia, deberán ser los del siguiente renglón. Por ello el renglón debe quedar como relativo.
2. Sin embargo, si al copiar la fórmula a la derecha, la columna queda como relativa se tendrá un problema ya que no se hará referencia a los valores de a y b (es decir ya no se hará referencia a las celdas B22 y C22), sino que respectivamente las celdas a las que se hará referencia: para el valor de "a" se estaría señalando erróneamente a la celda C22, y para el valor de "b" a la celda D22. Ello permite deducir que la columna si debe quedar como absoluta, siendo la referencia a introducir: $B22  para la variable "a", y $C22 para la variable "b".

• En el caso de la celda de la variable c:

1. Para "r1" (cálculo de la fórmula original) se tiene que se hace referencia al valor de "c1" (celda F12), entonces, para "r2" deberá referir a "c2" (celda G12), para "r3" deberá ser "c3" (celda  H12) y para el cálculo de "r4" se deberá referenciar a "c4" (celda I12).
2. Por lo tanto, cuando la formula se copie hacia la derecha, se requiere que la referencia a la columna quede relativa, para permitir la movilidad de la celda como corresponde (indicado en punto anterior).
3. Sin embargo, el problema radica que si la parte de la referencia correspondiente al renglón se deja como relativa, entonces el renglón se movería apuntando ahora al renglón 13 (en el cual no se encuentran los valores de la variable c; por ello es preciso indicar el renglón como absoluto, quedando entonces la referencia de la celda para el cálculo en r1 (D22) como: F$12

• En el caso de la celda que hace referencia a la constante M (la explicación aplica para ambos ejemplos de tabulación):

1.  si la formula se copia para abajo implica que no se mueve de columna, pero si de renglón, por ello si la parte de la referencia que indica el renglón no se pone como absoluta, la referencia ya no apuntaría al valor de la constante, debido a que se movería de forma relativa la parte del renglón, pasando de C10 a C11, esto indica entonces que la parte de la referencia al renglón debe ser absoluta, quedando por el momento como: C$10
2. ahora bien, si la formula se va a copiar a la derecha, hablamos que la referencia de la columna se a a mover si no se indica como absoluta: así se tendrá que si se deja como relativa, la referencia al valor de la constante apuntaría ahora a C$10, por lo tanto la referencia del renglón deberá ser indicada como absoluta. De esta forma se tiene que la referencia a la celda de la constante deberá ser:  $C$10

El análisis entonces indica que la formula quedaría en D22 como se expresa a continuación:

=($B22 + $C22) / F$12 * $C$10

Como puede verse, con esta fórmula, se permite el cálculo en todas las demás celdas en donde se requiere hacer el mismo cálculo, pero con distintos valores (de acuerdo a lo tabulado). 

• Así si la formula se copia una celda a la derecha (es decir a la celda C22) haría referencia a: =($B22 + $C22) / G$12 * $C$10, como puede verse solo se movería la referencia de la columna de la variable "c" que es lo único que realmente se necesita que cambie de referencia de celda (de la celda para la variable c1 ahora apuntaría a la celda de la variable c2).
• Y si se copia la formula una celda hacia abajo (es decir a D23) lo que se tendrá en la fórmula es lo siguiente:  =($B23 + $C23) / F$12 * $C$10, y tal como se necesita, solo cambia la referencia al renglón de las celdas que hacen referencia a las variables "a" y "b" (B22 ahora es B23 y C22 ahora es C23) apuntando a los nuevos valores de las variables en cuestión, de acuerdo a la tabulación.

Resolvamos la segunda tabulación de datos:

Transformemos la fórmula de modo que en lugar de las variables se empleen las referencias de celdas de la hoja de cálculo:

r = (a + b) /c * M

Vamos a escribir la formula en la primera celda, es decir en la primera posición para "r1": celda C34, por lo tanto:

• "a" se encuentra en la celda C32 
• "b" se encuentra en C33
• en el caso de la variable "c" se tomara la referencia de celda de "c1" esto es F12, 
• Y la constante M sigue estando en la celda C10

de esta forma, sin detenernos aun a observar cual será referencia absoluta, la formula quedaría en C34 como se expresa a continuación:

=(C32 + C33) / F12 * C10

Sin olvidar que se debe escribir la fórmula una sola vez para copiarla a las otras celdas de las cuatro columnas (etiquetadas como r1, r2, r3, r4), en esta tabulación igualmente debemos tener presente lo siguiente: La formula se va a copiar tanto hacia a la derecha como hacia abajo; por ello:

• En el caso de la celda de las variables a y b:

1. Al copiar la fórmula para la derecha la columna no se afectará, lo cual resulta conveniente ya que se necesita que al copiar la fórmula de la celda C32 a la celda D32, los valores a los que se haga referencia, sea a los nuevos valores de cada una de las variables. Por ello, la columna debe quedar como relativa.
2. Sin embargo, en el caso de copiar la fórmula para abajo, si se deja al renglón como relativo se tendrá el problema que ya no se hará referencia a los valores de a y b (es decir ya no se hará referencia a las celdas C32 y C33), sino que respectivamente: para el valor de "a" estaría señalando erróneamente a la celda C33, y para el valor de "b" a la celda C34. Ello permite deducir que el renglón debe quedar como absoluto. Por lo tanto, las referencias a introducir sn: C$32  para la variable "a", y C$33 para la variable "b".

• En el caso de constante M (la explicación de la tabulación anterior aplica igualmente para este caso de tabulación):

1.  Al copiar la formula para abajo se necesita que el renglón quede como absoluto
2. Y al copiar la formula a la derecha se necesita que la columna se indique como absoluta. 
3. De esta forma se tiene que la referencia a la celda de la constante deberá ser:  $C$10

• En el caso de la celda de la variable c:

1. En el caso de la referencia, en la parte del renglón, al copiar hacia abajo se afectaría, por lo que para mantener el mismo renglón, debe hacerse absoluta la referencia al renglón, por lo que por el momento debería quedar como: F$12.
2. Para "r1" (cálculo de la fórmula original) se tiene que se hace referencia al valor de "c1" (celda F12), entonces, para "r2" deberá referir a "c2" (celda G12), para "r3" debera ser "c3" (celda  H12) y para el cálculo de "r4" se deberá referenciar a "c4" (celda I12).
3. Por lo tanto, si la referencia de la columna queda como relativa, cuando la fórmula se copie hacia la derecha, se tendría el problema que para el cálculo de "r1" con los siguientes valores de las variables "a" y" b", el valor de la variable "c1" cambiaría al valor de la "c2", por ello sería un problema que la referencia a la columna quede relativa, así que para impedir la movilidad de la columna se tendría que indicar la columna como absoluta: teniendo que escribir la referencia como $F12, permitiendo que para r1, el valor de la variable c1 no se mueva de celda.
4. Sin embargo, con esta segunda tabulación para el cálculo de r1, r2, r3, r4,  se tiene como problema añadido la forma en la que se tabularon de los datos de la variable "c" (los valores respectivos c1, c2, c3, c4  no es conveniente), ya que ésta tabulación de valores de la variable "c" solo resulta conveniente para lo trabajado al calcular los valores de "r" de la primera tabulación (ya explicada anteriormente), sin embargo, para este segundo ejemplo de tabulación será imposible tener una sola fórmula para copiar hacia abajo y hacia la derecha de cada uno de los cálculos de r2, r3, r4. Ya que: al copiar hacia abajo se tiene el problema de que debido a la tabulación de los valores de "c" no puede aplicarse la misma fórmula en todos los renglones y por lo tanto, en cada caso (r2, r3, r4) debe debe hacerse el cambio de la referencia para la variable c (para c2 se tiene que modificar la columna por $G12; para c3 se tiene que modificar la columna como $H12; y para c4 se tiene que modificar la columna como $I12).

El análisis entonces indica que la primera formula para la primera celda de r1 será:
  =(C$32 + C$33) / $F$12 * $C$10

Al copiar esta fórmula, primero copie hacia abajo para cada caso de la variable r,  y antes de pretender copiar la fórmula hacia la derecha, se debe cambian en cada caso de r2, r3, y r4 la columna respectiva para que haga referencia al valor correspondiente de la variable "c"; es decir donde se encuentra $F$12, se cambia respectivamente como se expresa a continuación:

para la primera celda de r2 (es decir en la celda C35) quedará:  =(C$32 + C$33) / $G$12 * $C$10

para la primera celda de r3: =(C$32 + C$33) / $H$12 * $C$10

para la primera celda de r4: =(C$32 + C$33) / $I$12 * $C$10

Para evitar hacer este cambio manual en cada renglón, y tener una sola formula, lo que se debió tener para la segunda tabulación de valores, es decir ésta:

es una tabulación diferente para los valores de la variable "c", colocando las variables por ejemplo así:

De forma que al escribir la formula, se pudiera copiar una sola a las demás celdas:  

=(C$32 + C$33) / $F12 * $C$10

Como se evidencia en este ejemplo, ambas formas de tabulación de valores tienen sus particularidades al pretender que con la escritura de una sola formula se permita el cálculo en todas las demás celdas, en donde se requiere hacer el mismo cálculo pero con distintos valores. 

No olvide que de acuerdo a la forma en la que se tabulen los datos, se dificulta o se facilita el trabajo (siempre y cuando las tabulaciones se correspondan adecuadamente con la distribución de donde se tomen los valores para hacer los cálculos, el trabajo se facilitará). Como ven, nosotros mismos podemos complicar la replicación de las formulas, al hacer una tabulación de datos de forma  inadecuada.

No se trata solo de poner como absoluto todo, porque no funcionarían las formulas, ni solo como absoluto el renglón en todas las celdas, o como absoluta solo todas las partes de las columnas porque ello tampoco soluciona el problema; lo que se necesita es ser preciso en cada caso, analizando para cada referencia de celda ocupada en la formula, cuál es la necesidad, es decir, qué debe quedar como relativo y qué como absoluto. Y este es un trabajo constante a analizar cada vez que se requiera escribir una formula que va a ser copiada a otras celdas. No es algo que solo aplique a este ejercicio.

Animo, sé que ustedes pueden aplicar lo aquí expuesto y escribir correctamente las fórmulas del ejercicio enviado por correo electrónico. Sigan analizando los conceptos de referencias de celdas y rangos, los aspectos puntuales de que implica que una parte o toda la referencia sea absoluta. Confió en que son capaces de comprender los conceptos y avanzar en su aprendizaje. Animo.

Para descargar la explicación de la solución y el ejercicio de la entrada anterior haga clic AQUI.
Saludos

M.C. Bertha López Azamar


P.D. Alumno UNPA, si ya leíste la entrada, pon la letra de tu grupo y las iniciales de tus apellidos primero y luego de tu nombre en un comentario de esta entrada.