Mostrar imágenes

Voy a describir una técnica para mostrar la foto de un tenista elegido de una lista desplegable. La lista la pondremos en la celda H19 y encima, la fotografía. Añadiremos su posición en el ranking de la ATP, los puntos que lleva acumulados y los torneos jugados (datos del 24 de junio de 2011).

En la columna B de la hoja Fotos, ponemos los nombres y nacionalidades de los jugadores; en la columna C, sus fotografías.

Hacemos clic en la celda H19 de la hoja ATP y accedemos a Datos + Validación de datos. En la ficha Configuración, ponemos una lista con los valores del rango C3:C32.

En la lista de H19, elegimos un nombre; por ejemplo: Nadal, Rafael (ESP)

A continuación, creamos un "nombre" accediendo a Fórmulas + Asignar nombre. El nombre será Foto_tenista y la fórmula: =DESREF(Fotos!$C$3;COINCIDIR(ATP!$H$19;ATP!$C$3:$C$32;0)-1;0;1;1)

En la hoja Fotos, hacemos clic en la fotografía de Roger Federer y pulsamos Ctrl + C.
En la hoja ATP, hacemos clic en la celda G10 (vale cualquier otra) y pulsamos Ctrl + V.
Hacemos clic en la fotografías de Federer y, en la barra de fórmulas, escribimos: =Foto_tenista

Como en H19 habíamos elegido Nadal, Rafael (ESP), la fotografía de Federer habrá sido sustituida por la de Nadal. Cada vez que cambiemos de jugador, se mostrará su fotografía.

Arrastramos y agrandamos la fotografía hasta colocarla en la posición deseada.

Para terminar, vamos a poner la posición del jugador en el ranking de la ATP, los puntos y los torneos.

Hacemos clic en H15, pulsamos Ctrl + 1 para acceder a la ventana Formato de celdas, y ponemos el siguiente formato personalizado: "Ranking: " #.##0"º"

Del mismo modo, en H16 ponemos el formato: "Puntos: " #.##0
Finalmente, en H17 el formato será: "Torneos: " #.##0

En H15:

=INDICE($B$3:$B$32;COINCIDIR($H$19;$C$3:$C$32;);1)

En H16:

=INDICE($D$3:$D$32;COINCIDIR($H$19;$C$3:$C$32;);1)

En H17:

=INDICE($E$3:$E$32;COINCIDIR($H$19;$C$3:$C$32;);1)

Descargar archivo (S78-Ranking de la ATP.xls)

Descargar archivo (S78-Ranking de la ATP.xlsx)

Insertar un vídeo de YouTube en una hoja

Para incrustar un vídeo de YouTube en una hoja de Excel necesitamos dos cosas: tener acceso a Internet y conocer la URL del vídeo.

El vídeo elegido para este ejercicio está en la dirección: http://www.youtube.com/watch?v=bnZFaxShSjA
Debemos fijarnos en dos partes: la expresión watch? y el signo =.

Comenzamos accediendo a Programador + Insertar + Más controles.

En la ventana Más controles, elegimos Shockwave Flash Object y pulsamos Aceptar.

Manteniendo pulsada la tecla Alt, marcamos un área rectangular que abarque, por ejemplo, el rango B2:G19.

Hacemos clic con el botón secundario en el control insertado y, en el menú contextual, seleccionamos Propiedades.

En la propiedad Movie, ponemos la URL del vídeo. Eliminamos la expresión watch? y sustituimos el signo = por /.

Cerramos la ventana Propiedades y salimos del modo de diseño seleccionando Programador + Modo Diseño. El vídeo ha quedado preparado para ser visionado.

Descargar archivo (S53-YouTube.xlsx)

Calendario con fiestas

En la última entrada vimos cómo crear un calendario y, en otra anterior, cómo hallar la fecha del domingo de Pascua. Juntando los dos ejercicios, podemos crear un calendario con fiestas que incluya las fechas más significativas del santoral.

Algunas fiestas cristianas tienen fecha fija y otras dependen de la Pascua de Resurrección. Buscando en Internet se puede obtener información para calcular las fechas de las principales fiestas religiosos y civiles.

Festividades cristianas variables	Fecha
Lunes de Carnaval	Dos días antes del comienzo de la Cuaresma.
Miércoles de Ceniza. Empieza la Cuaresma	Comprende los cuarenta días anteriores a la Semana Santa. Es el tiempo que dedican los cristianos a prepararse para celebrar los acontecimientos de la pasión, muerte y resurrección de Jesús. Da comienzo el Miércoles de Ceniza. El color litúrgico es el morado.
Domingo de Carnaval	Cuatro días después del comienzo de la Cuaresma.
Domingo de Ramos. Semana Santa.	Es la semana previa al día de Pascua, dedicada a conmemorar la pasión y muerte de Jesús (Mc. 14-15). Comienza el Domingo de Ramos (Mc. 11, 1-11) y concluye con la celebración de la vigilia Pascual, en la noche del sábado santo.
Jueves Santo	Tres días antes de la Pascua de Resurrección.
Viernes Santo	Dos días antes de la Pascua de Resurrección.
Pascua de Resurrección	Es la fiesta cristiana más importante. Se celebra la Resurrección de Jesús, ocurrida el domingo después del 14 de nisán (Jn.20, 1). El color litúrgico es el blanco.
Lunes de Pascua	Siguiente día  de la Pascua de Resurrección
Ascensión del Señor	Originariamente, 40 días después de Pascua. Esta solemnidad ha sido trasferida al 7º domingo después de Pascua.
Pentecostés	Descenso del Espíritu Santo y el inicio de la actividad de la Iglesia. Se celebra 50 días después de Pascua
Santísima Trinidad	Domingo siguiente a Pentecostés
Corpus Christi	Jueves siguiente a la Santísima Trinidad

El libro tendrá tres hojas. En la hoja Calendario crearemos el calendario. La hoja Pascua contendrá el ejercicio del cálculo de la fecha de la Pascua de Resurrección estudiado en una entrada anterior. Finalmente, la hoja Festivos contendrá la lista de los días festivos, civiles y religiosos, de todo el año y el santo del día.

Con la celda F2 de la hoja Festivos seleccionada, en el Cuadro de nombres escribimos: año_elegido

Análogamente, a la celda G2 de la misma hoja le ponemos de nombre: mes_elegido

Para seleccionar el año y el mes, colocaremos sendas barras de desplazamiento en las celdas I2 y C2 de la hoja Calendario. Para ello, en el grupo Controles de la pestaña Programador, abrimos la lista de opciones del icono Insertar y elegimos Barra de desplazamiento (control ActiveX).

Manteniendo pulsada la tecla Alt, trazamos un rectángulo que ocupe la celda I2. Una vez que el control esté incrustado, en el menú emergente, seleccionamos Propiedades. Se abrirá la ventana de propiedades. Aquí hay que cambiar algunos parámetros.

• LinkedCell: año_elegido
• Max: 2200
• Min: 1910
• SmallChange: 1
• LargeChange: 10

Cerramos la ventana de propiedades y salimos del modo de diseño haciendo clic en Modo Diseño.

Volvemos a poner otra barra de desplazamiento en C2 con estas propiedades:

• LinkedCell: mes_elegido
• Max: 12
• Min: 1
• SmallChange: 1
• LargeChange: 1

Vamos a la hoja Calendario para poner el mes y el año en la cabecera del calendario, pero, antes, pulsando Ctrl +1 para abrir el cuadro de diálogo Formato de celdas, le ponemos a la celda C3 el siguiente formato personalizado: mmmm

En C3:
=FECHA(año_elegido;mes_elegido;1)

En I3:
=año_elegido

Con las barras de desplazamiento, elegimos un mes y un año. Si seleccionamos el mes de abril de 2012 y ponemos letra Arial + blanca + 16 ppp, con justificación central, el encabezado quedará así:

Ahora, sólo tenemos que copiar las fórmulas del calendario que hicimos en una entrada anterior, adaptándolas al nuevo formato.

En C5:
=(DIASEM($C$3;2)=1)*$C$3

En D5:
=(DIASEM($C$3;2)=COLUMNA()-2)*$C$3+(C5>0)+C5

Copiamos la fórmula de D5 hacia la derecha hasta I5.

En C7:
=I5+1

En D7:
=C7+1

Copiamos la fórmula de D7 hacia la derecha hasta I7.

En C9:
=I7+1

En D9:
=C9+1

Copiamos la fórmula de D9 hacia la derecha hasta I9.

En C11:
=I9+1

En D11:
=C11+1

Copiamos la fórmula de D11 hacia la derecha hasta I11.

En C13:
=(MES(C$11+7)=MES($C$3))*(C$11+7)

Copiamos la fórmula de C13 hacia la derecha hasta I13.

En C15:
=(MES(C$11+14)=MES($C$3))*(C$11+14)

Copiamos la fórmula de C15 hacia la derecha hasta I15.

En la hoja Pascua escribimos:

En B4:
=año_elegido

En C4:
=BUSCARV(B4;$M$4:$P$9;3;VERDADERO)

En D4:
=BUSCARV(B4;$M$4:$P$9;4;VERDADERO)

En E4:
=RESIDUO(B4;19)

En F4:
=RESIDUO(B4;4)

En G4:
=RESIDUO(B4;7)

En H4:
=RESIDUO(19*E4+C4;30)

En I4:
=RESIDUO(2*F4+4*G4+6*H4+D4;7)

En J4:
=H4+I4

En K4:
=SI(J4<10;FECHA(B4;3;J4+22);SI(J4-9=26;FECHA(B4;4;19);SI(Y(J4-9=25;H4=28;I4=6;E4>10);FECHA(B4;4;18);FECHA(B4;4;J4-9))))

En la hoja Festivos hemos agrupado las festividades en tres bloques: el primero, contiene las festividades oficiales; el segundo, las festividades que dependen de la Pascua de Resurrección; el tercero, contiene las festividades religiosas fijas. Algunos días pueden coincidir en dos bloques, pero al colocarlos en el orden mencionado, la función BUSCARV (que emplearemos más adelante) elegirá siempre la festividad que esté en el primero de los bloques. Por otra parte, en la columna D indicaremos si la fecha es laborable o es día festivo. Este dato nos servirá para resaltar en el calendario los días no laborables.

Las fórmulas de la columna D mostrarán la fecha del día festivo correspondiente al año que se ha seleccionado en F2. Todas las fórmulas de los rangos B2:B4 y B29:B395 tendrán el mismo formato. Por ejemplo, la fórmula de B2 es la siguiente: =FECHA(año_elegido;5;1)

Las fechas del bloque Festividades religiosas relacionadas dependen de la fecha de la Pascua, que está en B20 (se han incluido la Epifanía y el Bautismo de Jesús, que no dependen de la Pascua pero que están relacionadas entre sí).

En B8: =FECHA(año_elegido;1;6)
En B9: =B8+ELEGIR(DIASEM(B8;2);6;5;4;3;2;1;7)
En B10: =B12-2
En B11: =B10+1
En B12: =B20-46
En B13: =B12+1
En B14: =B13+1
En B15: =B14+1
En B16: =B15+1
En B17: =B20-7
En B18: =B20-3
En B19: =B20-2
En B20: =Pascua!K4
En B21: =B20+1
En B22: =B20+42
En B23: =B20+49
En B24: =B23+7
En B25: =B24+4

Volvemos a la hoja Calendario para poner las festividades debajo de los días. En algunos casos, el texto no cabe en una sola línea, por lo que hay que ajustar el formato de las celdas.

Seleccionamos las celdas C6:I6 y pulsamos Ctrl +1. En al ficha Alineación, marcamos Ajustar texto y nos aseguramos que esté seleccionada la posición Centrar, tanto en la alineación horizontal como en la vertical.

Copiamos este formato en las celdas de las filas 8, 10, 12, 14 y 16.

Ahora, ponemos las festividades de la siguiente manera:

En C6:
=SI.ERROR(BUSCARV(C5;Festivos!$B$1:$C$500;2;FALSO);"")

Copiamos la fórmula de C6 hacia la derecha hasta I6. Volvemos a copiar C6:I6 en las celdas inferiores de las filas 8, 10, 12, 14 y 16.

Para poner en rojo las fiestas oficiales usaremos un formato condicional.

Estando en la celda C5, accedemos a Inicio + Formato condicional + Nueva regla. Ponemos la fórmula:
=("Sí"=BUSCARV(C5;Festivos!$B$1:$D$500;3;FALSO)) y, en Formato, elegimos la fuente de color rojo.

Copiamos este formato en todas las celdas donde hay números. Para ello, estando en C5, hacemos doble clic en Copiar formato (pestaña Inicio) y pasamos la brocha sobre las celdas a las que queremos dar formato.

Nos ponemos en C6 y volvemos a poner el mismo formato condicional a esta celda. Es decir, la fórmula debe ser: =("Sí"=BUSCARV(C5;Festivos!$B$1:$D$500;3;FALSO))

De nuevo, con Copiar formato  aplicamos el formato al resto de las filas pares. De este modo, el ejercicio está terminado.

Nota: En Excel 2003 ni existe la función SI.ERROR ni se puede hacer referencia a otro libro o a otra hoja cuando se pone un formato condicional (las referencias hay que sustituirlas por "nombres" de Excel). Por ese motivo, en el archivo que se puede descargar más abajo, se han hecho las oportunas modificaciones para que el calendario funcione correctamente.

Descargar archivo (S26-Calendario con fiestas.xls)

Descargar archivo (S26-Calendario con fiestas.xlsx)

Calendario anual

Comenzamos diseñando el calendario.

Seleccionamos la celdas B2:X2 y, en el grupo Alineación de la pestaña Inicio, hacemos clic en Combinar y centrar. Además, ponemos un tamaño de letra grande: 26

Combinamos de la misma manera los encabezados de los meses: B3:H3, J3:P3, etc.

En B2 escribimos el año; por ejemplo, 2011

En B3:
=FECHA($B$2;1;1)    [Resultado: 01/01/2011]

Estando en B3, pulsamos Ctrl + 1 para entrar en el cuadro de diálogo Formato de celdas, y ponemos el formato personalizado: mmmm    [Resultado: enero]

En las celdas del rango B5:H10 sólo se debe mostrar el día; por tanto, el formato personalizado deberá ser: d

En B5:
=(DIASEM($B$3;2)=1)*$B$3    [Resultado: 0]

En C5:
=(DIASEM($B$3;2)=2)*$B$3+(B5>0)+B5    [Resultado: 0]

En D5:
=(DIASEM($B$3;2)=3)*$B$3+(C5>0)+C5    [Resultado: 0]

En E5:
=(DIASEM($B$3;2)=4)*$B$3+(D5>0)+D5    [Resultado: 0]

En F5:
=(DIASEM($B$3;2)=5)*$B$3+(E5>0)+E5    [Resultado: 0]

En G5:
=(DIASEM($B$3;2)=6)*$B$3+(F5>0)+F5    [Resultado: 1]

En H5:
=(DIASEM($B$3;2)=7)*$B$3+(G5>0)+G5    [Resultado: 2]

Puesto que el 1 de enero de 2011 es viernes, las celdas correspondientes al lunes, martes, miércoles y jueves deben estar en blanco. Las fórmulas nos devuelven un cero, pero no importa; más adelante ocultaremos los ceros con un formato personalizado. Los números de las tres filas siguientes se obtienen sumando uno a la celda anterior.

En B6:
=H5+1    [Resultado: 3]

En C6:
=B6+1    [Resultado: 4]

Extendemos la fórmula de C6 hasta H6.    [Resultados: 5|6|7|8|9]

Seleccionamos B6:H6 y copiamos las fórmulas hasta la fila 8.    [Resultados: 10|11|12...23]

Hasta aquí todo va bien, pero, ahora, hay que andar con cuidado ya que en la siguiente fila puede terminarse el mes. Debemos comprobar, celda a celda, si el siguiente número corresponde a enero o ya nos hemos pasado a febrero.

En B9:
=(MES(B$8+7)=MES($B$3))*(B$8+7)    [Resultado: 24]

Extendemos la fórmula de B9 hasta H9.    [Resultados: 25|26|27|28|29|30]

En B10:
=(MES(B$8+14)=MES($B$3))*(B$8+14)    [Resultado: 31]

Extendemos la fórmula de B10 hasta H10.    [Resultados: 0|0|0|0|0|0]

Para ocultar los ceros, volvemos a cambiar el formato de B5:H10, sustituyendo el que hemos puesto anteriormente por: d;;;

Podemos concluir el ejercicio poniendo un formato condicional para que las celdas blancas se muestren en otro color. Para ello, seleccionamos B5:H10 y accedemos a Inicio + Formato condicional + Nueva regla.

Repitiendo estos pasos en los meses restantes obtendremos un calendario que nos valdrá para cualquier año.

Descargar archivo (S95-Calendario anual.xls)

Descargar archivo (S95-Calendario anual.xlsx)

Cálculo de la fecha de la Pascua de Resurección

La Pascua de Resurrección es la celebración cristiana que conmemora la resurrección de Jesucristo. La fecha de celebración varía entre el 22 de marzo y el 25 de abril, ya que tiene lugar el domingo siguiente a la primera luna llena de primavera del hemisferio norte.

Esta fiesta determina el calendario móvil de otras fiestas: así la Ascensión (el ascenso de Jesús al cielo) se celebra 40 días después de Pascua, y Pentecostés 10 días después de la Ascensión. La semana anterior a la Pascua de Resurrección es la Semana Santa, que comienza con el Domingo de Ramos (que conmemora la entrada triunfal de Jesús en Jerusalén). La Octava de Pascua (popularmente conocida como "semana de Pascua") es la semana que sucede a este Domingo de Pascua (o Domingo de Resurrección). El tiempo pascual o tiempo de Pascua designa, en la liturgia católica, las semanas que van desde el Domingo de Resurrección hasta el de Pentecostés.

La forma más sencilla de calcular la fecha del domingo de Pascua es mediante la fórmula desarrollada por el matemático alemán Johann Carl Friedrich Gauss (30 de abril de 1777, Brunswick – 23 de febrero de 1855, Göttingen).

Llamaremos A al año en el que queremos calcular la fecha de la Pascua de Resurrección. De la tabla siguiente, obtendremos dos parámetros, M y N . Por ejemplo, al año 2010 le corresponde M = 24 y N = 5

Ya tenemos los tres datos básicos. Ahora, mediante fórmulas sencillas obtendremos otros cinco datos. Les llamaremos: a, b, c, d y e.

A continuación, se muestran las fórmulas que hay que utilizar y un ejemplo. El año elegido es el 2010.
























Se pueden dar dos casos:

1)      Si (d + e < 10) la Pascua será el día (d + e + 22) de marzo.

2)      Si (d + e > 9) la Pascua será el día (d + e − 9) de abril.

Hay dos excepciones:

• Si la fecha obtenida es el 26 de abril, entonces, la Pascua será el 19 de abril.
• Si la fecha obtenida es el 25 de abril, con d = 28, e = 6 y a > 10, entonces, la Pascua será el 18 de abril.

Sigamos con nuestro cálculo:

d + e = 13. Es mayor que nueve. Se aplica el segundo caso.

d + e − 9 = 4. La Pascua es el 4 de abril, que no coincide con ninguna de las dos excepciones.

Ya tenemos toda la información necesaria para crear la aplicación que nos permita obtener la fecha de la Pascua de Resurrección.

Diseñamos una hoja que contenga la tabla para obtener M y N, así como celdas donde almacenar los valores de a, b, c, d y e.

A la celda K4 le aplicamos un formato de fecha adecuado. Para ello, estando en K4, pulsamos Ctrl + 1 y, en la pestaña Número del cuadro de diálogo Formato de celdas, seleccionamos el siguiente formato de fecha: *miércoles, 14 de marzo de 2001.





























En B4 escribimos el año; por ejemplo, 2010

En C4:

=BUSCARV(B4;$M$4:$P$9;3;VERDADERO)    [Resultado: 24]

En D4:

=BUSCARV(B4;$M$4:$P$9;4;VERDADERO)     [Resultado: 5]

En E4:

=RESIDUO(B4;19)     [Resultado: 15]

En F4:

=RESIDUO(B4;4)    [Resultado: 2]

En G4:

=RESIDUO(B4;7)    [Resultado: 1]

En H4:

=RESIDUO(19*E4+C4;30)    [Resultado: 9]

En I4:

=RESIDUO(2*F4+4*G4+6*H4+D4;7)    [Resultado: 4]

En J4:

=H4+I4    [Resultado: 13]

En K4:

=SI(J4<10;FECHA(B4;3;J4+22);SI(J4-9=26;FECHA(B4;4;19);SI(Y(J4-9=25;H4=28;I4=6;E4>10);FECHA(B4;4;18);FECHA(B4;4;J4-9))))    [Resultado: domingo, 04 de abril de 2010]

Copiando las fórmulas hacia abajo obtendremos la fecha de la Pascua de años sucesivos.

Estudiando la hoja, podemos comprobar que, por ejemplo, 1954 y 2076 son años en los que se dan las excepciones señaladas más arriba.

Descargar archivo (S25-Pascua Resurrección.xls)

Descargar archivo (S25-Pascua Resurrección.xlsx)

Agrupar el texto de una columna intercalando separadores

Continuando con el ejemplo de la entrada anterior, vamos a agrupar el texto de la columna B intercalando separadores entre los datos.

Pondremos una lista de separadores en la columna D y elegiremos uno en la celda G2. El texto lo agruparemos en G3 y usaremos el rango G5:G9 para almacenar los resultados intermedios.

En D3: Dejamos un espacio y escribimos (espacio)
En D4: / (barra de división)
En D5: \ (barra invertida)
En D6: | (barra vertical)
En D7: ǁ (vertical doble)
En D8: ─ (guión largo)
En D9: • (círculo negro)
En D10: ▪ (cuadrado negro)
En D11: ▫ (cuadrado blanco)

A continuación, definiremos un "nombre" dinámico para referirnos a esta lista de separadores. Para ello, en la pestaña Fórmulas, hacemos clic en Asignar nombre y creamos el nombre "Separador" con la fórmula: =DESREF($D$2;1;0;CONTARA($D:$D)-1;1)

Hacemos clic en G2 y, en la pestaña Datos, accedemos a Validación de datos. En Permitir, elegimos Lista. En Origen escribimos: =Separador

En la lista de separadores de la celda G2, elegimos uno; por ejemplo, | (barra vertical)

Contamos los caracteres de la columna B que debemos agrupar y ponemos el resultado en G5.

En G5:

=SUMAPRODUCTO(LARGO(DESREF(B2;1;0;CONTARA(B:B)-1;1)))      [Resultado: 78]

Contamos las filas y ponemos el dato en G6.

En G6:
=CONTARA(B:B)-1     [Resultado: 9]

Abrimos el menú Archivo y accedemos a Opciones. En la ficha Fórmulas, habilitamos el cálculo iterativo, ponemos 100 iteraciones y un cambio máximo de cero.

En G7:

=SI(G8>G6;0;1)

En G8:
=(G8+1)*G7

En G9:
=SI(G7=0;"";IZQUIERDA(CONCATENAR($G$9;IZQUIERDA(G2;1);DESREF($B$2;$G$8;0;1;1));G5+CONTARA(B:B)))

Finalmente,

En G3:
=SI(LARGO(G9)=G5+CONTARA(B:B);G9;G3)

Podemos elegir cualquier otro separador y añadir nuevos separadores en la columna D.

Descargar archivo (S104-Unir celdas con separadores.xls)

Descargar archivo (S104-Unir celdas con separadores.xlsx)

Agrupar en una celda el texto de una columna

Las celdas del rango B3:B17 contienen texto que debemos agrupar en E2. Vamos a resolver el ejercicio sin utilizar macros.

El problema debe resolverse para cualquier texto y cualquier número de filas que pongamos en la columna B. Usando la función CONCATENAR podríamos pensar que una fórmula matricial del estilo, {=CONCATENAR("";DESREF($B$2;1;0;CONTARA(B:B)-1;1))}, nos acercaría a la solución, pero comprobamos que CONCATENAR no funciona bien si uno de los argumentos es una matriz.

En http://www.officefull.es/seccion/excel/107-hmo.html hay una solución al problema planteado utilizando macros de Excel 4, pero el objetivo es prescindir de las macros de cualquier versión y resolver el ejercicio, únicamente, con funciones de Excel.

Necesitamos un procedimiento repetitivo, algo parecido a las funciones FOR o WHILE de Visual Basic, para poder aplicar repetidamente la función CONCATENAR. Lo conseguiremos usando referencias circulares.

Vamos a comenzar contando los caracteres que hay en B3:B17 y el número de filas que tenemos que agrupar. Pondremos las siguientes fórmulas:

En E4:

=SUMAPRODUCTO(LARGO(DESREF(B2;1;0; CONTARA(B:B)-1;1)))   [Resultado: 48]

En E5:
=CONTARA(B:B)-1   [Resultado: 15]

Es el momento de permitir las referencias circulares. Nos bastarán 150 iteraciones.

Abrimos el menú Archivo y accedemos a Opciones. En la ficha Fórmulas, habilitamos el cálculo interactivo, ponemos 150 iteraciones y un cambio máximo de cero.

En E6:

=SI(E7>E5;0;1)       [Resultado: 1]

En E7:

=(E7+1)*E6      [Resultado: 14]

Aquí hay una referencia circular; E6 utiliza el valor de E7 y E7 utiliza el de E6. Con esto conseguimos que en E6 haya siempre un 1 o un 0. Puesto que E7 incrementa su valor en una unidad en cada iteración, su valor va creciendo hasta llegar a 15 (valor de E5). En el momento que E7 supere 15, E6 valdrá 0 y E7 empezará de nuevo el ciclo desde 0. Pulsando reiteradamente F9 forzaremos las sucesivas iteraciones.

Ahora, vamos a ir, poco a poco, componiendo la fórmula hasta llegar al resultado deseado.

En E8:

=CONCATENAR($E$8;DESREF($B$2;$E$7;0;1;1))

Estudiemos esta fórmula. DESREF($B$2;$E$7;0;1;1) devuelve el valor de una celda de la columna B. Empieza en B2 (primer argumento) y salta a la fila señalada en E7 (segundo argumento). Este último valor, como hemos visto, está cambiando en cada iteración (de 0 a 15), por lo que la función devolverá los valores de las celdas B2 a B17. Se incluye el encabezado porque E6 empieza desde 1 pero en las siguientes iteraciones empieza desde 0. Por tanto, la fórmula devuelve todos los valores de la columna B.

Luego, con la función CONCATENAR, ponemos estos valores uno a continuación del otro. El primer valor es un 0, pero no importa, lo eliminaremos enseguida. A continuación, vienen todos los caracteres de B3:B17. En ese momento, E6 se pone a cero apuntando al encabezado de la columna B y, como se realizan 150 iteraciones, el siguiente bucle empieza en B2 (lo que no nos interesa) y se añade la columna B entera, incluido el encabezado. El proceso continúa varios ciclos hasta consumir el número de iteraciones fijado. Este es un problema que se debe resolver. Para ello, sustituiremos la fórmula por la siguiente:

En E8:

=SI(E6=0;"";IZQUIERDA(CONCATENAR($E$8;DESREF($B$2;$E$7;0;1;1));E4))

Para eliminar el texto extra de la celda E8, utilizamos la función IZQUIERDA, con la que extraemos los 48 primeros caracteres (celda E4). Además, con la función SI, eliminamos el 0 que nos aparecía al comienzo, empezando con un carácter nulo cuando E6 es cero.

El ejercicio aún no está terminado. Si pulsamos repetidamente F9 hasta que en E7 aparezca un número menor que 15 (número de filas de la lista), la fórmula de E8 concatenará el número de celdas que indica E7 y el texto no estará completo. La solución es bien sencilla y la pondremos en E2.

En E2:

=SI(LARGO(E8)=E4;E8;E2)

Ahora, funciona correctamente. Lo comprobamos añadiendo o eliminando datos en la columna B. Los valores de E6, E7 y E8 cambiarán cada vez que pulsemos F9 o modifiquemos el valor de una celda cualquiera de la hoja.

Descargar archivo (S103-Unir celdas.xls)

Descargar archivo (S103-Unir celdas.xlsx)

Referencias circulares en Excel

Si en una celda ponemos una fórmula que haga referencia a la propia celda, Excel mostrará un mensaje indicando que se ha producido una referencia circular. Por ejemplo, si en B1 ponemos =2*B1, nos mostrará el mensaje:

Esto nos indica que hemos podido cometer un error. Quizás quisimos poner =2*A1

Sin embargo, las referencias circulares no son necesariamente errores; podemos usarlas para resolver problemas. Veamos un ejemplo.

Queremos calcular los beneficios obtenidos en una operación en la que hemos ingresado 1.000 € y hemos gastado 700. El 10% de los beneficios netos lo vamos a destinar a obras benéficas. Pongamos estos datos en una hoja de cálculo.

En C4:

=10%*C5

En C5:
=C2-C3-C4

Excel nos responde con la advertencia de referencia circular y señala con flechas azules las celdas implicadas.

Las fórmulas son correctas, pero el cálculo es imposible. Para obtener el valor en C4 es necesario tener previamente el valor de C5; pero para obtener el valor de C5 es preciso conocer el de C4. Como se observa, hay una referencia circular.

Comencemos eliminando las flechas. Para ello, en el grupo Auditoría de fórmulas de la pestaña Fórmulas, seleccionamos Quitar flechas.

Borramos las fórmulas de las celdas C4 y C5.

El siguiente paso consiste en habilitar el cálculo iterativo. Abrimos el menú Archivo y elegimos Opciones. En el apartado Fórmulas, marcamos Habilitar cálculo iterativo, ponemos 1 en Iteraciones máximas y dejamos 0,001 en Cambio máximo. Terminamos pulsando Aceptar.

Volvemos a poner las fórmulas:

En C4:

=10%*C5

En C5:
=C2-C3-C4

Al poner la fórmula en C4 todavía no hay nada en C5, por lo que el 10% de nada es nada.

En C5 se resta a C2 (1.000 €) el valor de C3 (700 €) y de C4 (nada). Por tanto, se obtienen 300,00 €.

Pulsamos la tecla F9 para recalcular la hoja. Se obtienen los valores siguientes:

En C4 se obtienen 30,00 € (el 10% de los 300,00 € de C5).

En C5 se restan 1.000 € - 700 € - 30,00 € = 270,00 €

Volvemos a pulsar repetidamente la tecla F9 hasta que observemos que los valores de la hoja no cambian. Es el resultado final. Han sido necesarias menos de 10 pulsaciones.

Pero no vamos a pulsar reiteradamente F9; dejaremos que Excel haga el trabajo. Para ello, repetimos el ejercicio poniendo un número suficiente de iteraciones en el cuadro de diálogo Opciones de Excel. El número máximo de iteraciones es 32.767; a nosotros nos bastan 100.

El cálculo se detendrá cuando el siguiente recálculo provoque una diferencia menor de 0,001.

Ahora, surge la pregunta, ¿podría resolverse el ejercicio sin usar referencias circulares?

Se puede, y vamos a hacerlo.

Tenemos que encontrar la forma de relacionar las aportaciones a Obras Benéficas con los Ingresos y los Gastos, pero no con los Beneficios netos. De este modo, evitaremos las referencias circulares. Un poco de matemática elemental nos ayudará a conseguirlo.

Bastará borrar las fórmulas de las celdas C4 y C5, deshabilitar las referencias circulares y escribir las nuevas fórmulas:

En C4:

=(C2-C3)/11

En C5:
=C2-C3-C4

En la próxima entrada mostraré otro ejemplo del uso de las referencias circulares.