Matrices y referencias externas...Ten en cuenta que todas las celdas del rango matricial compartirán la misma fórmula de matriz, por lo que no puedes eliminar celdas de un rango

1Copyright © Computer Aided Education, S.A.

Matrices y referencias externas

1. FÓRMULAS DE MATRIZ

Normalmente, las funciones producen un único resultado. Sin embargo, a veces resulta interesante tener funciones que

calculen varios resultados a la vez. Estas funciones se llaman fórmulas de matriz o matriciales, y su resultado puede ocupar varias celdas.

Las fórmulas de matriz también se usan para ahorrar tiempo cuando escribimos fórmulas, ya que puedes sustituir varias fórmulas distintas por una sola fórmula de matriz.

Por ejemplo, en una hoja de cálculo puedes usar una fórmula de matriz para aplicar la misma fórmula sobre distintos datos. En el siguiente caso, si no utilizas fórmulas de matriz, puedes calcular el total de dinero ingresado por la venta de unos coches multiplicando el número de coches vendidos por su precio (fila SUBTOTAL) y sumando estas cantidades (celda B9):

En lugar de realizar todas estas operaciones, podríamos haber combinado todos los cálculos en una solo, usando una fórmula de matriz o matricial:

En este caso, en la celda B8 tenemos la siguiente fórmula de matriz:

{=SUMA(B5:C5*B6:C6)}

2


Las fórmulas de matriz se muestran encerradas entre llaves ( { } ), para diferenciarlas del resto de fórmulas, y para introducirlas debemos aceptar la fórmula escrita con + + .

De esta forma, después de introducir la fórmula de matriz y pulsar esta combinación de teclas, Excel la colocará automáticamente entre llaves ( { } ).

En el ejemplo anterior, si, tras introducir la fórmula de matriz, modificas esta fórmula y aceptas con , ten en cuenta que Excel no introducirá una fórmula de matriz, sino que se tratará de una fórmula “normal”. Recuerda la combinación de teclas que tienes que pulsar para aceptar la fórmula de matriz.

Hemos dicho que una fórmula de matriz o matricial puede devolver un resultado o varios; en este segundo caso, estos resultados se pondrían en varias celdas. El rango de celdas donde se colocarán los resultados de la fórmula de matriz se llama rango matricial.

Ten en cuenta que todas las celdas del rango matricial compartirán la misma fórmula de matriz, por lo que no puedes eliminar celdas de un rango matricial.

En las fórmulas simples, puedes introducir referencias a celdas o valores constantes.

Pues bien, en las fórmulas de matriz o matriciales también tenemos una forma de introducir valores constantes. A estos valores constantes en las fórmulas matriciales se les denomina constantes matriciales.

Para incluir una constante matricial en una fórmula:

Escribe los valores directamente en la fórmula entre llaves ( { } ). Como separador de columnas, tienes que utilizar el punto y

coma ( ; ). Para separar las filas, utiliza el símbolo de barra invertida ( \ ).

Por ejemplo, para incluir la siguiente matriz como constante matricial:

10 2030 40

deberías escribir {10;20\30;40}.

2. CONSTANTES MATRICIALES



Por ejemplo, en la siguiente imagen puedes ver una fórmula de matriz que se ha introducido en las celdas C1:D2:

Según esta fórmula matricial, hemos sumado las celdas A1:B2 y la matriz {10;11\20;21}. ¿Cómo ha hecho Excel esta suma?

Las operaciones de las fórmulas matriciales se hacen celda a celda, dentro del rango seleccionado. Por ejemplo, en este caso se sumaría el valor de la celda A1 más 10, el valor de la celda B1 más 11, el valor de A2 más 20 y el de B2 más 21. Puedes ver el resultado de estas operaciones en la hoja de cálculo.

Recuerda que las fórmulas de matriz no tienen nada que ver con las operaciones matemáticas sobre matrices, son dos cosas distintas. Por ejemplo, aquí podrías multiplicar una matriz de (2x3) por otra de (2x3), produciendo un resultado de (2x3), cosa que no puedes hacer con las operaciones matemáticas de matrices.

Si introduces una constante

matricial en una fórmula "normal" (no

de matriz), sólo se tendrá

en cuenta para los cálculos

el primer elemento de la constante

matricial.

4


En este ejemplo que acabamos de ver, el rango matricial estaría formado por las celdas C1:D2 (es donde están los resultados de la fórmula de matriz). Si intentas eliminar el contenido de una de estas celdas individualmente, Excel te mostrará un mensaje diciendo que no puedes hacer eso.

Además de eliminar celdas, otras operaciones que no se pueden realizar en celdas pertenecientes al rango de una matriz son:

Cambiar el contenido de las celdas. Mover celdas que forman parte de la matriz. Insertar celdas en un rango matricial.

Algunas de las operaciones que sí están permitidas son las siguientes:

Dar formato a cada una de las celdas del rango de forma individual.

Copiar y pegar celdas de un rango matricial.

Por lo tanto, si cambias una celda que pertenece a un rango matricial, debes tener en cuenta que no puedes hacer operaciones que cambien únicamente esta celda: tienes que cambiar todo el rango matricial, como si se tratase de una única celda.

Para seleccionar un rango matricial de una fórmula de matriz, puedes

utilizar el comando Ir a Especial del menú del botón (o, lo que es

lo mismo, seleccionar la opción Ir a de este menú y pulsar el botón Especial). En la ventana que aparecerá, selecciona la opción Matriz actual.



Cuando se trabaja con fórmulas de matriz, cada elemento de la fórmula tiene que tener el mismo número de filas y de columnas. Si esto no es así, Excel expande las fórmulas de matriz. Por ejemplo, la fórmula ={1;2;3}*{2\3} se transformaría en ={1;2;3\1;2;3}*{2;2;2\3;3;3}.

Para introducir una fórmula de matriz, tienes que seleccionar un rango de celdas igual al de la matriz resultante. Si no existe un valor expansible, aparecerá el valor de error #N/A.

Por otra parte, si el resultado matricial es más grande que el rango seleccionado, los valores sobrantes no se mostrarán en la hoja de cálculo.

También hay varias cosas que tienes que tener en cuenta al trabajar con constantes matriciales:

Pueden ser números, texto, valores lógicos o incluso valores de error.

Si introduces texto, debe ponerse entre comillas.

En una misma constante matricial se puede incluir distintos tipos de valores. Por ejemplo: {1;VERDADERO;2\"hola";55;10}.

No pueden estar formadas por fórmulas.

Todas las filas y todas las columnas de una constante matricial tienen que tener la misma longitud. Es decir, no puedes tener una fila con cinco elementos y otra con tres.

No pueden tener símbolos de moneda, paréntesis ni símbolos de porcentaje.

Ya sabemos que, en una celda, podemos hacer referencia a celdas que se encuentran en hojas de cálculo distintas, dentro

de un mismo libro de trabajo.

Excel también permite la creación de vínculos o referencias externas entre dos o más libros, de forma que la información que veas en un libro refleje los cambios que ocurren en los datos de otro libro. Es decir, que un vínculo o referencia externa es una referencia a una celda o a un rango de celdas que están en una hoja de cálculo de otro libro.

Por ejemplo: podemos incluir los resultados de varios libros en un libro resumen, de forma que se puedan comparar los resultados de cada uno de ellos de una forma rápida; además, podemos hacer que cualquier cambio en los libros fuente se refleje en el libro de resultados.

3. REFERENCIAS EXTERNAS (VÍNCULOS)

6


También tienes que tener en cuenta que, al trabajar con libros pequeños, éstos son más fáciles de editar y más rápidos de abrir, guardar y calcular. De esta forma, podemos tener los datos en un libro y analizarlos en otro libro distinto, asegurándonos de que los datos originales no van a ser modificados.

Hay una serie de conceptos que tienes que conocer:

Libro de origen (o fuente): es aquel libro que contiene los datos a los que hace referencia una fórmula que está en otro libro, a través de una referencia externa.

Libro de destino (también llamado libro dependiente o vinculado): es el libro que tiene los vínculos con el otro libro y que, por lo tanto, depende de los datos de este último.

Referencia externa (o vínculo): en un libro de Excel, es cualquier referencia a una celda, a un rango de celdas o a un nombre de otro libro de trabajo.

En resumen, que en el libro de destino o dependiente hay una referencia externa a una o varias celdas que están en el libro origen. O sea, que, a través de esta referencia, en el libro destino podemos ver y trabajar con los datos que hay en el libro origen.



Para que lo tengas más claro, podemos ver las relaciones que se establecen a través de una referencia externa o vínculo entre un libro de origen y un libro de destino en la siguiente imagen:

El libro de destino es donde se ha insertado el vínculo o referencia externa, mientras que el libro de origen es realmente el que tiene los datos, es decir, la información que se muestra a través del vínculo.

Para crear referencias externas, primero tenemos que indicar la ruta o posición del archivo en el disco, seguido del nombre del libro entre corchetes ( [ ] ). A continuación figura el nombre de la hoja; si el nombre de la hoja de cálculo o del libro de la referencia tienen caracteres no alfabéticos, estos elementos figuran entre apóstrofos o comillas sencillas ( ' ). Luego aparece el símbolo de exclamación ( ! ) y, a continuación, la celda o rango de celdas al que hacemos referencia.

Aunque puede parecer complicado, la verdad es que la mayoría de veces no tendrás que escribir toda la referencia externa, ya que se puede introducir directamente pulsando con el ratón.

Para seleccionar mediante el ratón celdas que estén en otros libros, podemos tener abiertos los dos libros (el de origen y el de destino o vinculado) y organizarlos en ventanas dentro de Excel (por ejemplo, desde el botón de la ficha Vista de la Cinta de opciones).

Al introducir las referencias

a las celdas pulsando con el ratón, nos evitamos el

tener que teclear textos

muy largos, evitando así la posibilidad de

que se produz-can errores al escribir estas referencias.

8


Luego iríamos escribiendo la fórmula adecuada en el libro de destino: escribiríamos el signo igual ( = ) e iríamos introduciendo los distintos elementos de la fórmula (valores constantes, operadores, referencias a celdas...).

Para introducir una referencia externa, pulsaríamos en la celda o celdas del libro de origen o fuente. Excel irá construyendo la fórmula con las sintaxis correcta.

Por supuesto, también podemos poner referencias externas con funciones, como si se tratara de referencias a celdas del mismo libro.

Si el libro al cual está vinculado el libro actual está cerrado, la referencia externa se muestra en la barra de fórmulas con la ruta de acceso completa, tal como hemos indicado. En cambio, si el libro de origen o fuente está abierto, la referencia externa se muestra sin la ruta de acceso.

Cuando trabajes con celdas con mucho texto o que tengan fórmulas largas (como en ocasiones ocurre con las referencias externas), puede ser interesante cambiar el tamaño de la barra de fórmulas, para poder ver todo el contenido de la celda. Si pulsas el botón Expandir barra de fórmulas , que está en la parte derecha de la barra de fórmulas, esta barra de hará más grande, mostrando tres o más líneas.

También puedes cambiar el tamaño de la barra de fórmulas arrastrando la separación entre esta barra y las celdas de la hoja de cálculo, cuando el cursor del ratón te lo indique .

Por otro lado, puedes cambiar el ancho de la barra de fórmulas y del cuadro de nombres, arrastrando la zona entre estos dos elementos , cuando el cursor del ratón cambie a una flecha . De esta forma, si lo necesitas, puedes hacer más ancho el cuadro de nombres y más estrecha la barra de fórmulas o al revés, para ajustarlos a tu gusto o forma de trabajar.

En las referencias externas, ten en cuenta la ruta que se mostrará en el caso de que tengas los documentos (es decir, los libros) en la carpeta Documentos del usuario.

Si haces doble clic en la se-

paración entre la barra de

fórmulas y las celdas de la

hoja de cálculo (cuando el cur-

sor cambie a

), el tama-

ño de la barra de fórmulas se ajustará auto-

máticamente al número de líneas que se necesite para

ver todo el contenido de

la celda.



En Windows Vista, la carpeta Documentos es distinta para cada usuario del sistema. Para cada uno de los distintos usuarios que pueda haber definidos, su carpeta Documentos se almacena dentro de la carpeta Users\nombre_de_usuario del disco duro, y su nombre real es Documents (en la carpeta Users\nombre_de_usuario, aparte de guardarse esta carpeta, también se almacenan imágenes, favoritos y otros archivos y configuraciones propias de cada usuario).

Por lo tanto, si seleccionas una referencia externa de un libro que esté en la carpeta Documentos, verás algo parecido a esto:

Si el libro de origen está en otra carpeta, verás la ruta adecuada en la referencia externa:

En Windows XP, la carpeta con los documentos propios del usuario se denomina Mis documentos, y está almacenada dentro de la carpeta Documents and Settings\nombre_de_usuario, mientras que, en versiones anteriores de Windows, la carpeta Mis documentos se guardaba directamente en el directorio raíz del disco duro: C:\Mis documentos.

Finalmente, indicarte también que, al abrir un libro que tiene vínculos o referencias externas (es decir, el libro de destino), aparece una barra de mensajes debajo de la Cinta de opciones, que te indica que, por razones de seguridad, se ha desactivado la actualización automática de los vínculos.

Desde el bo-tón Opciones de esta barra podría-mos habilitar la actualización auto-mática de los vín-culos, de forma que se actualizarían los datos desde el libro de origen, si esta información hu-biera cambiado.

10


Del mismo modo que se pueden hacer referencias externas a celdas, también podemos utilizar nombres en las referencias.

La sintaxis utilizada es la misma que la vista hasta ahora, con la única diferencia de que se utiliza el nombre en lugar de las referencias. Por ejemplo: ='C:\Trabajo\[Ventas.xls]Hoja3'!Total_Este.

Cuando trabajas con libros vinculados y quieres guardarlos, normalmente se debe guardar el libro de origen antes de guardar el de destino. De esta forma, nos aseguraremos de que todos los datos del libro de destino están actualizados.

En la ficha Datos de la Cinta de opciones, el botón del grupo de comandos Conexiones proporciona algunas herramientas y utilidades para el trabajo con vínculos. Por ejemplo, podemos cambiar o actualizar los vínculos de un libro de destino en cualquier momento.

En este cuadro de diálogo podrás ver los distintos vínculos que hay en el libro actual, además de hacer ciertas operaciones relacionadas con ellos.

Para cambiar el libro de origen de los datos, pulsa en el botón Cambiar origen. En el cuadro de diálogo que aparecerá podremos seleccionar el nombre del archivo que será el nuevo fichero de origen o fuente al cual el libro actual está vinculado.

Si quieres acceder al libro de origen y éste no está abierto, con el botón Abrir origen podrías hacerlo. Al editar el libro de origen y tener abierto el libro de destino, los cambios se reflejarán en este último.

Con Actualizar valores podrás realizar una actualización manual de la información vinculada, mientras que Romper vínculo romperá los vínculos, poniendo en las celdas que tengan vínculos el valor que tengan en ese momento.

4. TRABAJO CON REFERENCIAS EXTERNAS

Si desde den-tro de Excel,

con la opción Guardar

como, cambias el nombre del

libro de origen o fuente, de-

bes tener el li-bro de destino

abierto, para que este cam-

bio de nombre se refleje au-

tomáticamente en las referen-

cias externas que tenga.

Ten en cuenta que la acción

de Romper vínculo no se

puede des-hacer, por lo

que es re-comendable que guardes

una copia del libro antes de hacerla, por si te equivocas o quieres volver

a tener el libro como lo tenías

antes.



A través del botón Comprobar estado comprobaremos los vínculos del libro: Excel nos dirá, en la columna Estado del cuadro de diálogo, si el vínculo está bien, si no encuentra el archivo o la hoja de origen, si los valores de los vínculos no están actualizados, etc.

Por último, con el botón Pregunta inicial podremos indicar si, al abrir el libro, se actualizarán automáticamente o no los vínculos sin preguntar nada, o si se preguntará al usuario si quiere actualizarlos.

Al trabajar con libros vinculados, tienes que procurar no perder los vínculos establecidos. Para ello, trata de guardar los libros vinculados en la misma carpeta.

Si, por cualquier motivo, los guardas en carpetas distintas, guarda el libro de origen antes de guardar el libro de destino.

Si, al abrir un libro de destino, Excel no encuentra el libro de origen, te mostrará un mensaje indicándolo.

A veces, también puede suceder que un libro de destino ocupe mucho espacio en disco o tarde mucho tiempo en abrirse. Esto puede ser debido a que, en este libro de destino, se guardan las copias de los valores contenidos en el libro de origen junto con los datos de este libro de destino (esto suele pasar si tienes referencias externas a muchos rangos de celdas del libro de origen y se trata de rangos grandes).

Puedes hacer que esto no sea así accediendo al cuadro de las

Opciones de Excel (desde el menú del Botón de Office )

y, en la sección Avanzadas, desactivar la casilla de verificación Guardar valores de vínculos externos. Así podrás reducir el espacio en disco y el tiempo que se tarda en abrir la hoja de cálculo.

La opción pre-determinada

del cuadro de la Pregunta

inicial (que es Permitir que los usuarios

elijan mostrar o no la alerta)

hace que se muestre o no se muestre el

mensaje de actualización según lo que esté indicado en el cuadro

de las Opcio-nes de Excel,

en la casilla Consultar

al actuali-zar vínculos automáticos de la sección Avanzadas.