jueves, 24 de mayo de 2012

Otro problema con celdas combinadas

En cierta ocasión, un compañero de trabajo me consultó un problema que le surgió al aplicar el formato condicional a una hoja en la que había celdas combinadas. Éste era el aspecto de la hoja:

En la columna B las celdas se combinan de 4 en 4, y en la C, de 2 en 2. Las fórmulas de la columna B son irrelevantes para el caso (además, no las recuerdo) por lo que pondremos cualquier fórmula que genere errores a partir de una fila determinada.

Se trata de ocultar las celdas a partir de la fila en la que comienzan a presentarse los errores, aplicando formato condicional.

Si no hubiera celdas combinadas el procedimiento sería:
  1. Seleccionar B2:B29
  2. Acceder a Inicio + Formato condicional + Nueva regla.
  3. Elegir la opción Utilice una fórmula que determine las celdas para aplicar formato.
  4. En el cuadro de texto Dar formato a los valores donde esta fórmula sea verdadera, poner: =ESERROR($B2)
  5. Pulsar el botón Formato y elegir: En Bordes: Ninguno; En Fuente: Color blanco.
Si damos estos pasos el resultado será:

Las filas 22 y 26 quedan ocultas, pero el resto de las filas, de la 22 hacia abajo, el formato no se aplica correctamente.

Como en otras ocasiones, nos ayudaremos de celdas auxiliares para crear una fórmula. Debemos conseguir que, en cada grupo de 4 filas, el valor que evalúe la función ESERROR sea el de la celda combinada de ese grupo. El procedimiento utilizado será similar al empleado en la entrada Problemas con celdas combinadas.

Eliminamos el formato condicional incorrecto accediendo a Inicio + Formato condicional + Borrar reglas + Borrar reglas de toda la hoja.

En F2:
=RESIDUO(FILA()+2;4)

Extendemos la fórmula hasta la fila 29. Con esta operación hemos conseguido que las filas de cada bloque estén numeradas del 0 al 3.

En G2:
=FILA()-F2

Extendemos la fórmula hasta la fila 29. Ahora, cada grupo contiene un número que coincide con el de la primera fila del grupo.

En H2:
=INDIRECTO(DIRECCION(G2;2))

Extendemos la fórmula hasta la fila 29. Hemos conseguido asociar cada fila con la fecha correcta de su grupo.

En I2:
=INDIRECTO(DIRECCION(FILA()-RESIDUO(FILA()+2;4);2))

Extendemos la fórmula hasta la fila 29. Esta fórmula compuesta es la que debemos poner en el formato condicional.
  1. Seleccionamos B2:B29
  2. Accedemos a Inicio + Formato condicional + Nueva regla.
  3. Elegimos la opción Utilice una fórmula que determine las celdas para aplicar formato.
  4. En el cuadro de texto Dar formato a los valores donde esta fórmula sea verdadera, ponemos: =ESERROR(INDIRECTO(DIRECCION(FILA()-RESIDUO(FILA()+2;4);2)))
  5. Pulsamos el botón Formato y elegimos: En Bordes: Ninguno; En Fuente: Color blanco.
El resultado no es exactamente el deseado: falta la línea inferior de la última fila visible.

Lo solucionaremos añadiendo una nueva regla.
  1. Seleccionamos B2:B29
  2. Accedemos a Inicio + Formato condicional + Nueva regla.
  3. Elegimos la opción Utilice una fórmula que determine las celdas para aplicar formato.
  4. En el cuadro de texto Dar formato a los valores donde esta fórmula sea verdadera, ponemos:    =NO(ESERROR(INDIRECTO(DIRECCION(FILA()-RESIDUO(FILA()+2;4);2))))
  5. Pulsamos el botón Formato y, en Bordes, elegimos Contorno.
Ahora, está perfecto.

Podemos probar diferentes valores en la celda E1 (hemos usado esta celda para crear las fechas de la columna B) para comprobar que todo funciona bien.

Nota: En Excel 2003 el formato condicional funciona de manera algo diferente que en 2010. Al aplicar la primera regla el resultado ya es correcto. Por eso, en el fichero para la versión 2003, que se puede descargar más abajo, se ha prescindido de la segunda regla.

 
 
 

martes, 22 de mayo de 2012

Problemas con las celdas combinadas

Las celdas combinadas son útiles para poner títulos o rótulos. Pero hay que tener cuidado porque pueden darnos muchos problemas si intervienen en fórmulas que deben ser copiadas arrastrando el controlador de relleno. Consideremos el siguiente ejemplo:

Se ha creado una celda combinada con el rango C3:C5 (Inicio + Combinar y centrar) para no repetir tres veces el precio de la tonelada de alumnio. Se ha hecho lo mismo con las celdas C6:C8 y C9:C11.

El cálculo de los valores de la columna F parece sencillísimo; basta multiplicar el valor en euros de la tonelada de cada metal por las toneladas vendidas. Veamos si es tan sencillo.

En F3:
=E3*C3   [Resultado correcto: 490.284,90 €]

Si extendemos la fórmula hasta la fila 11 obtendremos el siguiente resultado:

Evidentemente, el resultado no es correcto. En F4 la fórmula, que ha quedado así: =E4*C4, parece correcta, pero no lo es, ya que en C4 no hay nada. Cuando se combinan celdas, Excel les asigna el nombre y el valor de la que ocupa el extremo superior izquierdo; en nuestro caso, la celda combinada C3:C5 es, ahora, la celda C3. No hay nada en C4 ni en C5. Tampoco lo hay en las celdas C7, C8, C10 y C11.

Resolveremos correctamente el ejercico en las columnas H:K y luego montaremos una fórmula compuesta en la columna F.

En H3:
=RESIDUO(FILA(F3);3)

Extendemos la fórmula hasta la fila 11. Hemos conseguido numerar las celdas correspondientes a cada metal del 0 al 2.

En I3:
=FILA(F3)-H3

Extendemos la fórmula hasta la fila 11. Con esta operación hemos logrado que las celdas I3, I4 e I5 contengan el número 3, con el que compondremos la dirección C3, en la que se encuentra el precio de la tonelada de aluminio. Del mismo modo, las celdas I6, I7 e I8 contienen el 6, etc.

En J3:
=INDIRECTO(DIRECCION(I3;3))

Primero creamos la dirección C3DIRECCION(I3;3)—; luego, obtenemos su valor—INDIRECTO(DIRECCION(I3;3))—. Extendemos la fórmula hasta la fila 11.

En K3:
=E3*INDIRECTO(DIRECCION(I3;3))

Al multiplicar el valor de E3 por el de C3, obtenemos el valor correcto. Copiamos la fórmula hasta la fila 11.

Sólo nos falta crear la fórmula compuesta para prescindir de las columnas H a K.

En F3:
=E3*INDIRECTO(DIRECCION(FILA(F3)-RESIDUO(FILA(F3);3);3))

Extendemos la fórmula hasta la fila 11.

Si no hubiéramos creado celdas combinadas el ejercicio se habría resuelto con una fórmula elemental. La conclusión es clara: Para evitar problemas, prescinda de las celdas combinadas si éstas van a intervenir en fórmulas que deban extenderse a otros rangos.

 
 
 

jueves, 17 de mayo de 2012

Curiosidades y rarezas

Para escribir un número negativo hay que poner un signo menos delante del número o encerrarlo entre paréntesis.

En A1:
-50,25    [Resultado: -50,25]

En A3:
(50,25)    [Resultado: -50,25]

Un número seguido del símbolo del euro (€) adopta el formato Moneda.

En B1:
150€    [Resultado: 150 €]

En B3:
(150€)    [Resultado: -150 €]

Si nos ponemos en B1 (o en B3) y pulsamos Ctrl + 1 para ver el formato de la celda, observaremos que Excel ha aplicado el formato Moneda con los valores negativos en rojo y precedidos del signo menos:

Se puede sustituir el signo igual (=), con el que deben comenzar todas las fórmulas, por el signo más (+). Si después del igual (=) hay un signo menos (-) se puede prescindir del signo igual (=) o sustituirlo por el signo más (+).

En C1:
=HOY()      [Devuelve la fecha actual]

En C3:
+HOY()      [Devuelve la fecha actual]

En C5:
=-(3*COS(A1))      [Resultado: -2,99964045]

En C7:
-(3*COS(A1))      [Resultado: -2,99964045]

En C9:
+-(3*COS(A1))      [Resultado: -2,99964045]

Los valores lógicos son VERDADERO y FALSO. Ambos valores equivalen numéricamente a 1 y 0 respectivamente. Para transformar los valores lógicos en sus correspondientes valores numéricos basta multiplicarlos por uno.

En D1:
=5=6      [Resultado: FALSO]

En D3:
=6=6      [Resultado: VERDADERO]

En D5:
=1*(5=6)      [Resultado: 0]

En D7:
=1*(6=6)      [Resultado: 1]

Multiplicar dos veces por menos uno es equivalente a multiplicar por uno. Esto permite usar una forma muy comprimida en los ejemplos que hemos puesto en D5 y D6 ya que 1* se puede sustituir por (-1)*(-1)* y esta expresión, a su vez, por dos signos menos (--). De esta forma, las fórmulas de las dos celdas citadas quedarán así:

En E5:
=--(5=6)      [Resultado: 0]

En E7:
=--(6=6)      [Resultado: 1]

El exceso de signos más (+) no afecta al resultado de las fórmulas, aunque su comportamiento resulte extraño.

En A1:
+++++100    [Resultado: 100]

Hemos puesto cinco signos más (+) seguidos de un número. Si nos situamos en A1 y miramos lo que pone en la barra de fórmulas veremos: =++++100. Excel ha eliminado uno de los signos más y ha antepuesto el signo igual.

Si pulsamos la tecla F2 para editar la fórmula y, sin hacer ningún cambio, pulsamos Intro, se elimina otro signo más (+). Cada vez que editemos la fórmula, se eliminará un más hasta que desaparezcan todos.

En A2:
=+++++A1    [Resultado: 100]

En este caso, no se cumple la regla anterior. La fórmula funciona correctamente pero los signos más no desaparecen al editarla con F2.

En A3:
=+++++4*A1     [Resultado: 400]

Ahora, sí. Los signos más van desapareciendo con F2. Parece que si encuentra un número después del último (+), al editar la fórmula los va eliminando; si encuentra la referencia a una celda, no los elimina.

En A4:
=+++++COS(A1)    [Resultado: 0,86231887]

En este caso tampoco se quitan los signos (+). Quizás sea porque después del último (+) hay una función y no un número. Ignoro la razón del extraño y, en apariencia, irrelevante comportamiento de Excel y los signos más.

Cada vez que se abre un fichero de Excel 2003, el nombre del usuario queda incrustado en el libro. Basta abrirlo y cerrarlo inmediatamente sin hacer nigún cambio.

Debido a esto, se puede saber quién fue la ultima persona que utilizó un libro de Excel usando el Bloc de notas de Windows o cualquier otro editor o procesador de textos (Word, por ejemplo).

El siguiente ejemplo ha sido realizado usando el archivo "home-mortgage-calculator.xls" obtenido de la página de Vertex42.

Una vez descargado el fichero (pero sin abrirlo con Excel) lo he abierto con el Bloc de notas de Windows y he obtenido algo prácticamente ilegible pero donde se puede leer claramente el nombre Vertex42.

Luego, lo he abierto con Excel y lo he vuelto a cerrar sin guardar los cambios. Parece que el fichero no debería haberse alterado... pero no es así. Utilizando de nuevo el Bloc de notas se observa que Vertex42 ha sido sustituido por mi nombre de usuario.

Delante de los números, los espacios son ignorados. Delante de las horas, los espacios provocan resultados inesperados.

En A1:
(espacio)(espacio)(espacio)47    [Resultado: 47]

Podemos operar con el dato de A1 sin ningún problema. Los espacios no son tenidos en cuenta.

Pero, ¿qué pasa si el dato representa una hora? La respuesta es distinta según sea el dato introducido y la versión de Excel que utilicemos.

Comencemos con lo que es común en todas las versiones:

En A2:
8:30    [Resultado justificado a la derecha: 8:30]

En A3:
(espacio)8:30    [Resultado justificado a la izquierda: 8:30]

En A2 la hora se ha introducido correctamente. Sin embargo, en A3 el dato se ha insertado como un texto y, en consecuencia, no se podrán realizar cálculos propios de los valores de fecha y hora. Lo comprobaremos con los siguientes ejemplos:

En B2:
=A2+"2:15"    [Resultado correcto: 10:45]

En B3:
=A2+"2:15"    [Resultado incorrecto: #¡VALOR!]

Lo que viene a continuación funciona igual en Excel 2003 y 2007.

En A4: (espacio):0    [Resultado: La celda ha quedado cubierta de almohadillas (#). Aunque ensanchemos al máximo la columna A, las almohadillas no desaparecen. Sin embargo, si nos ponemos en A4, observaremos que en la barra de fórmulas aparece el valor: -0,000694444444444444]
En A5: (espacio):1    [Resultado en la barra de fórmulas: 0:59:00]
En A6: (espacio):2    [Resultado: 1:59:00]
En A7: (espacio):3    [Resultado: 2:59:00]
En A8: (espacio):25    [Resultado: 01/01/1900  0:59:00]
En A9: (espacio):26    [Resultado: 01/01/1900  1:59:00]
En A10: (espacio):49    [Resultado: 02/01/1900  0:59:00]
En A11: (espacio):0 AM    [Resultado rarísimo: 2982616,16248843]
En A12: (espacio):0 PM    [Resultado rarísimo: 2982616,16318287]
En A13: (espacio):7 PM    [Resultado rarísimo: 2982616,45484954]

En Excel 2010 los resultados son distintos:

En A4: (espacio):0    [Resultado en la barra de fórmulas: 0:00:00]
En A5: (espacio):1    [Resultado: 1:00:00]
En A6: (espacio):2    [Resultado: 2:00:00]
En A7: (espacio):3    [Resultado: 3:00:00]
En A8: (espacio):25    [Resultado tipo texto: :25]
En A9: (espacio):26    [Resultado tipo texto: :26]
En A10: (espacio):49    [Resultado tipo texto: :49]
En A11: (espacio):0 AM    [Resultado tipo texto: :0 AM]
En A12: (espacio):0 PM    [Resultado tipo texto: :0 PM]
En A13: (espacio):7 PM    [Resultado tipo texto: :7 PM]

La suma de valores lógicos puede devolver resultados inesperados.

Comencemos poniendo unos cuantos valores lógicos en una hoja:

En A1: VERDADERO
En A2: FALSO
En A3: VERDADERO

Ahora, vamos a sumar estos valores de dos formas distintas.

En A5:
=A1+A2+A3     [Resultado: 2]

En este caso, Excel ha asignado valores numéricos a VERDADERO y FALSO. Sin embargo, al utilizar la función SUMA, se obtiene un resultado inesperado.

En A6:
=SUMA(A1:A3)    [Resultado: 0]

La función SUMA no considera los valores lógicos como números y, por tanto, el resultado es cero. La operación se ejecuta correctamente si, previamente, transformamos los valores lógicos en números nultiplicándolos por uno. 

En A7:
=SUMA(--A1:A3)     [Terminar con Ctrl + Mayús + Intro. Resultado: 2]

Hemos obtenido el resultado correcto, pero ha sido necesario crear una fórmula matricial.

Si, en vez de usar referencias, ponemos dentro de la función SUMA los valores lógicos, Excel utilizará sus equivalencias numéricas:

En A8:
=SUMA(VERDADERO;FALSO;VERDADERO;VERDADERO)     [Resultado: 3]

Las funciones Y y O actúan de forma confusa cuando se aplican a valores no lógicos.

Pongamos unos cuantos valores en la columna C para ilustrar este caso:

En C1: 5
En C2: (dejamos la celda en blanco)
En C3: Mariposa
En C4: 4:20
En C5: 16/5/2012

Ahora, apliquemos las funciones Y y O a estas celdas:

En C6:
=Y(C1;C2;C3;C4;C5)    [Resultado: VERDADERO]

En C7::
=O(C1;C2;C3;C4;C5)    [Resultado: VERDADERO]

¿Por qué estos resultados? Para comprender lo que hace Excel, nos ponemos en C6 (o en C7) y accedemos a Fórmulas + Insertar función. Obtendremos la siguiente ventana:

A las celdas en las que hay un número, una fecha o una hora, las funciones Y y O les asignan el valor VERDADERO; a las celdas vacías les asignan FALSO; a las que contienen un texto, el error #¡VALOR! En buena lógica, la función Y debería devolver FALSO, ya que se supone que para devolver VERDADERO todos los argumentos deben ser VERDADERO (lo indica claramente el propio cuadro de diálogo). Sin embargo, y por alguna razón incomprensible para mí, devuelve VERDADERO. ¡Misterio!

En algunas ordenaciones ocurre un caso curioso. Si los datos que hay que ordenar se han obtenidos de otras celdas mediante referencias que incluye el nombre de la hoja, pueden obtenerse resultados erróneos.

Rellenemos unas cuantas celdas de la "Hoja1" con los valores y fórmulas siguientes:

El resultado será:

Es evidente que la referencia Hoja1! en la columna C es innecesaria. Sin embargo, la singularidad a la que hemos hecho referencia, y que ahora vamos a descubrir, se produce por esta circunstancia.

Seleccionamos A1:C5 y accedemos a Datos + Ordenar de A a Z. Obtendremos la ordenación siguiente:

Las columnas A y B están perfectamente ordenadas, pero la C, no. Para ver qué ha ocurrido debemos comprobar cómo han quedado las fórmulas de las columnas B y C. Pulsamos Ctrl + ` para visualizar las fórmulas; o bien, accedemos a Fórmulas + Mostrar fórmulas (Alt + º).

Nota: Aunque Excel indica que la combinación de teclas rápidas para mostrar las fórmulas es Alt + º, esta combinación sólo funciona en Excel 2003 y 2007. En Excel 2010 hay que pulsar  Ctrl + ` (acento grave).

Las fórmulas han quedado así:

Albacete, que antes de la ordenación estaba en A3, ahora está en A1. En la celda C1 la fórmula hace referencia al valor de A3, donde ya no está Albacete sino Murcia; de ese modo, en C1 aparece Murcia. Jaén, que estaba en A1 antes de la ordenación, pasa a A2, pero la fórmula de C2 indica que se debe mostrar el valor de A1, que como hemos indicado anteriormente ocupa Albacete. Lo mismo ocurre en las otras celdas. ¿No es un comportamiento rarísimo? Es como si hubiéramos puesto referencias absolutas en las fórmulas de la columna C...pero no lo hemos hecho.

La conclusión es evidente: si tenemos que ordenar una lista con Datos + Ordenar de A a Z, no conviene incluir el nombre de la hoja en la referencia a las celdas.


jueves, 10 de mayo de 2012

Mostrar y ocultar un gráfico

Modificando un poco la técnica utilizada en otro artículo para mostrar imágenes, podemos mostrar u ocultar un gráfico hecho en Excel. Lo haremos poniendo dos botones de opción: uno para ocultar el gráfico y otro para mostrarlo.

Comencemos con los botones accediendo a Programador + Insertar + Botón de opción (control de formulario). Colocamos el botón en el sitio que queramos y en el menú contextual elegimos Modificar texto para poner: Mostrar gráfico. De nuevo, en el menú contextual seleccionamos Formato de control y, en el apartado Vincular con la celda de la ficha Control, ponemos: $E$2

Repetimos el proceso con el otro botón cambiando el texto por: Ocultar gráfico.

Si elegimos el primer botón, en E2 se mostrará un 1; si elegimos el segundo, se mostrará un 2.

Con el rango B2:D6 hacemos un gráfico del tipo que queramos adornándolo como nos parezca mejor.

Seleccionamos el gráfico, pulsamos Ctrl + X para cortarlo, hacemos clic en cualquier lugar de la hoja Auxiliar y pulsamos Ctrl + V para pegarlo. De este modo, habremos movido el gráfico de la hoja Negocio a la hoja Auxiliar.

En la hoja Auxiliar, seleccionamos B2:E9 y en el Cuadro de nombres escribimos: imagen (no debemos olvidarnos de pulsar Intro para terminar).

Manteniendo pulsada la tecla Alt, arrastramos el gráfico hasta que ocupe el rango B2:E9. Lógicamente, si quisiéramos un gráfico más grande tendríamos que seleccionar un rango con más celdas; por ejemplo, B2:F14.

En la hoja auxiliar hacemos clic en una celda vacía; por ejemplo en G1. Ahora, en el Cuadro de nombres escribimos: nada (terminar con Intro).

Estando en la celda G1 de la hoja Auxiliar, pulsamos Ctrl + C y hacemos clic con el botón derecho en la celda F2 de la hoja Negocio. En el menú contextual, elegimos Pegado especial + Imagen vinculada (M).

En F2 aparece la imagen de la celda G1 y en la barra de fórmulas: =Auxiliar!$G$1

Ahora, crearemos un nombre. Para ello, accedemos a Fórmulas + Asignar nombre y creamos el nombre Ver con la definición siguiente: =ELEGIR(Negocio!$E$2;imagen;nada)

Volvemos a seleccionar F2 y sustituimos la fórmula de la barra de fórmulas por: =Ver

Ocultamos los números de E2 poniendo el formato personalizado siguiente: ;;;


 
 
 

martes, 8 de mayo de 2012

Etiquetas de hoja permitidas

¿Se puede poner cualquier nombre a una hoja?
No. El nombre de una hoja debe cumplir las reglas siguientes:
  • No superar los 31 caracteres.
  • No contener ninguno de los siguientes caracteres: \ / ? * [ ]
  • No se admiten nombres en blanco.
  • No puede haber dos hojas con el mismo nombre.
Además, hay un nombre reservado que tampoco se puede utilizar. Si intentamos ponerle a una hoja el nombre Historial, Excel devuelve el siguiente mensaje:

Al utilizar el Control de cambios para registrar los cambios que hagan diferentes usuarios en una hoja compartida se le puede pedir a Excel que muestre las modificaciones realizadas. Excel crea una hoja, llamada Historial, con todas las variaciones que experimenta la hoja. El nombre Historial está reservado para esta hoja.

¿Podemos conseguir que en la pestaña de la hoja no se muestre ningún nombre?
Sí, se puede.
¿Cómo?
Sustituyendo el nombre por espacios en blanco (debe haber, al menos, uno).

Si usamos este truco en dos o más hojas, todas deben tener distinto número de espacios en blanco. En caso contrario, contravendría una de las reglas.

¿Cómo se reflejaría este nombre en una fórmula? Lo veremos mejor con un ejemplo.

En la celda A1 de la Hoja1:
25

En B1:
=2*Hoja1!A1     [Resultado: 50]

Hacemos doble clic en la pestaña de la Hoja1, pulsamos dos veces la barra espaciadora y terminamos con Intro.

Al ponernos en B1, en la barra de fórmulas ya no aparece la fórmula que hemos escrito; en su lugar se muestra: =2*'  '!A1. El nombre de la hoja ha sido sustituido por dos espacios delimitados por comillas simples.

lunes, 7 de mayo de 2012

Listas circulares

Llamamos lista circular a la lista móvil que funciona como las orugas de un tanque; el elemento que sale por debajo entra por encima, y viceversa. Para hacer que la lista se mueva podemos emplear una Barra de desplazamiento, un Control de número o cualquier otro método que genere valores numéricos consecutivos.

Podemos hacer que la lista muestre todos los elementos posibles (por ejemplo, los doce meses del año) o parte de ellos (por ejemplo, 12 elementos de los 117 que tiene la Tabla períodica).

Si sólo vamos a mostrar unos pocos elementos de la lista, y ésta es muy extensa, será necesario crear una tabla. Esto ocurre con la Tabla periódica, que en el ejemplo desarrollado está en la hoja Elementos.

Si la lista tiene pocos elementos (vayamos a mostrarlos todos o parte de ellos), no será necesario crear una tabla (aunque si la hubiere, facilitaría cualquier modificación).

Comencemos con la lista corta. En la ficha Programador, seleccionamos Insertar + Barra de desplazamiento (control ActiveX). Trazamos un pequeño rectángulo en cualquier sitio libre de la hoja y, en el menú contextual, elegimos Propiedades.

Salimos del modo de edición haciendo clic en Programador + Modo Diseño. Con estas propiedades, hemos creado una barra que generará valores comprendidos entre 1 y 100. Estos valores se asocian a la celda B16. Inicialmente, hemos puesto 50 en la propiedad Value, lo que significa que el desplazable de la barra se encontrará en la mitad. Podemos pulsar en los botones de la barra para comprobar cómo cambia el valor de la celda B16.

El siguiente paso consiste en ocultar el valor de la celda B16 con la propia barra de desplazamiento. Para ello, volvemos a entrar en el modo de edición pulsando Programador + Modo Diseño y, manteniendo pulsada la tecla Alt, desplazamos y modificamos el tamaño de la barra hasta que ocupe toda la celda B16. Finalmente, salimos con Programador + Modo Diseño.

Ahora, ponemos la siguiente fórmula en B3:
=INDICE({"ENE"\"FEB"\"MAR"\"ABR"\"MAY"\"JUN"\"JUL"\"AGO"\"SEP"\"OCT"\"NOV"\"DIC"};RESIDUO(FILAS($B$3:B3)+$B$16-2;12)+1)

Extendemos la fórmula hasta la fila 14 y probamos.

En el caso de una lista larga no podemos poner los elementos de la matriz en la fórmula (sería inmensa); hay que crear una tabla. La columna B de la hoja Elementos contiene los nombres de todos los elementos de la Tabla Periódica ordenados por orden alfabético.

Seleccionamos B4:B120 y, en el Cuadro de nombres, escribimos: Elementos (terminar pulsando Intro)

En la ficha Programador, seleccionamos Insertar + Control de número (control ActiveX). Trazamos un pequeño rectángulo en cualquier sitio libre de la hoja Lista_circular y, en el menú contextual, elegimos Propiedades

Manteniendo pulsada la tecla Alt, desplazamos y modificamos el tamaño del control hasta que ocupe toda la celda D16. Finalmente, salimos con Programador + Modo Diseño.

En D3:
=INDICE(Elementos;RESIDUO(FILAS($B$3:B3)+$D$16-2;117)+1)

Extendemos la fórmula hasta la fila 14.

 


viernes, 4 de mayo de 2012

Referencia a rangos de otros libros

En la hoja Población del libro S338-Poblacion.xlsx, tenemos una tabla con las poblaciones de todas las provincias españolas.

En la hoja Extensión del libro S338-Extension.xlsx, tenemos la tabla de provincias en la que faltan los datos de la columna Población. Estos datos los obtendremos del libro anterior.

Pueden darse varias posibilidades:
  1. Que ambos libros estén abiertos en nuestro ordenador.
  2. Que el libro del que queremos extraer los datos no esté abierto pero que se encuentre en una carpeta de nuestro propio ordenador.
  3. Que el libro del que queremos extraer los datos no esté abierto y se encuentre en una carpeta de otro ordenador de nuestra Intranet.
  4. Que conozcamos la dirección de Internet donde está guardado el libro.
Veamos la forma de obtener la información en cada caso.

Caso 1º: Los dos libros abiertos

Abrimos el primer libro y accedemos a Archivo para abrir el segundo libro. Es importante que ambos libros se abran desde la misma ventana de Excel.

En la celda E6 de la hoja Extensión escribimos:
=BUSCARV(C6;'[S338-Poblacion.xlsx]Población'!$C$6:$D$57;2;FALSO)

Extendemos la fórmula hasta la fila 57.

En E58:
=SUMA(E6:E57)     [Resultado: 46.744.116]

Guardamos ambos libros y salimos de Excel.

Cuando abramos otra vez el libro S338-Extension.xlsx, Excel nos mostrará un cuadro de diálogo indicándonos que los datos se han obtenido de una fuente externa. Puesto que esta fuente ha podido ser modificada, nos ofrece la posibilidad de actualizar los datos con los cambios habidos.

Elegimos la opción que nos interese y comprobamos que la fórmula de la celda E6 se ha modificado; el nombre del libro [S338-Poblacion.xlsx], ahora, irá precedido de la ruta donde se ha guardado. Por ejemplo, yo lo he guardado en el escritorio y la fórmula que muestra mi ordenador es la siguiente:
=BUSCARV(C6;'C:\Users\Javi\Desktop\[S338-Poblacion.xlsx]Población'!$C$6:$D$57;2;FALSO)

Caso 2º: Un libro abierto y el otro en una carpeta de nuestro ordenador

Visto el caso anterior, éste es evidente; bastará poner la fórmula modificada en E6:
=BUSCARV(C6;'C:\Users\Javi\Desktop\[S338-Poblacion.xlsx]Población'!$C$6:$D$57;2;FALSO)

Caso 3º: Un libro abierto y el otro en una carpeta de la Intranet

Este caso es similar al anterior. Sólo necesitamos conocer la ruta en el servidor:
=BUSCARV(C6;'\\Servidor\Carpeta\[S338-Poblacion.xlsx]Población'!$C$6:$D$57;2;FALSO)

Caso 4º: Un libro abierto y el otro alojado en Internet

Una vez conocida la URL del alojamiento, pondremos una fórmula siguiendo este esquema:
=BUSCARV(C6;'http://www.servidor.com/Carpeta/[S338-Poblacion.xlsx]Población'!$C$6:$D$57;2;FALSO)

Si el fichero S338-Poblacion.xlsx estuviera alojado en:

la fórmula de E6 debería ser:
=BUSCARV(C6;'http://www.fileden.com/files/2011/10/14/3209175/[S338-Poblacion.xlsx]Población'!$C$6:$D$57;2;FALSO)


 
 
 
 

miércoles, 2 de mayo de 2012

Punto de vista

En ocasiones, nuestro sentido de la vista nos hace percibir distintas realidades según cómo miremos un objeto; por ejemplo, ¿qué vemos al contemplar esta figura?


Fijemos la mirada un rato hasta que percibamos que el vértice a está más alejado que el b; estaremos viendo un pequeño cubo apoyado en el suelo y en dos paredes laterales. Por el contrario, si conseguimos que nuestra vista perciba que el vértice a se encuentra más cerca que el b, veremos un cubo flotando en el aire al que se le ha quitado un pequeño trozo en forma de dado en el vértice más cercano a nosotros.

No creo que sea muy práctico hacer un dibujo como éste utilizando los gráficos de Excel, pero es un pasatiempo como otro cualquiera y, como tal pasatiempo, vamos a construirlo empleando un gráfico de áreas.

El primer paso consistirá en crear una tabla:

Los números de esta tabla tienen que ver con las dimensiones de la figura. Así, el punto más alto del dibujo corresponderá al punto (2, 24). Por el contrario, el punto más bajo será el (2, 1). En el siguiente dibujo se muestran algunos puntos significativos. Como es lógico, las medidas del dibujo, y, por tanto, las coordenadas de esos puntos, las decidimos nosotros.



Vamos a construir el gráfico poco a poco, como si fuera un rompecabezas.

Seleccionamos B2:C7, accedemos al grupo Gráficos de la pestaña Insertar y elegimos Área + Área. Quitando el título del gráfico nos queda así:

Ya tenemos hecha la parte posterior del gráfico. Aparentemente, no tiene la forma correcta pero ya la tendrá cuando cambiemos la escala vertical y eliminemos la leyenda.

Seleccionamos D2:D5, pulsamos Ctrl + C para copiar los datos en el portapapeles, hacemos clic en el gráfico y pulsamos Ctrl + V para pegar los datos en el gráfico.

Hemos creado la pared izquierda del gráfico.

Ahora, seleccionamos E2:E6 y volvemos a incorporar los datos en el gráfico con el truco copiar (Ctrl + C) y pegar (Ctrl + V). Repitiendo estos pasos con el resto de las columnas, obtendremos la secuencia siguiente:

Eliminamos las líneas de división principales haciendo clic en una de ellas y pulsando Supr. Del mismo modo, eliminamos las leyendas.

En el eje vertical, Excel ha puesto una escala comprendida entre 0 y 30. Vamos a fijarla entre 0 y 25.

Doble clic en el eje vertical:
  • Opciones de eje → Mínima: Fija + 0 / Máxima: Fija + 25 / Marca de graduación principal: Ninguno / Marca de graduación secundaria: Ninguno / Etiquetas del eje: Ninguno
  • Color de líneaSin línea
Doble clic en el eje horizontal:
  • Opciones de eje → Marca de graduación principal: Ninguno / Marca de graduación secundaria: Ninguno / Etiquetas del eje: Ninguno
  • Color de líneaSin línea
Doble clic en el área del gráfico:
  • Relleno → Sin relleno
  • Color del bordeSin línea
Nos ayudamos con la tecla Alt para agrandar y colocar el gráfico ocupando el rango C8:J29.


Faltan dos cosas: colorear adecuadamente cada región y eliminar el borde del gráfico.

Las regiones 1, 2 y 3 las pondremos en un color claro; para ello, hacemos doble clic en la región 1 (corresponde a la serie Banda):
  • Relleno → Relleno sólido / Color: Anaranjado, Énfasis 6
Repetimos el mismo color en las zonas 2 (Pared_izqda) y 3 (Cubo_dcha). Las regiones 4 (Fondo) y 5 (Cubo_izqda) irán en color Anaranjado, Énfasis 6, Oscuro 25%. El color de las regiones 6 (Superior) y 7 (Suelo) será Anaranjado, Énfasis 6, Oscuro 50%. Finalmente, la región 8 (Recorte) tendrá color Blanco y borde, también, Blanco.

Para quitar el borde del gráfico, hacemos doble clic en el Área del gráfico:
  • Color del bordeSin línea