matrices y referencias externas

Copyright © Computer Aided Elearning, S.A. 1

Matrices y referencias externas

1. FÓRMULAS DE MATRIZ

ormalmente, 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 coches multiplicando el número de

coches vendidos por su precio (fila SUBTOTAL) y sumando esas

cantidades (celda B9):

En lugar de realizar todas estas operaciones, podríamos haber

combinado todos los cálculos en uno solo, usando una fórmula de

matriz o matricial.

N


2

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

matriz:

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

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 ha colocado automáticamente entre

llaves ( { } ).

En el ejemplo anterior, si tras introducir la fórmula de matriz

modificas la 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.

2. CONSTANTES MATRICIALES

n 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 símbolo de

barra invertida ( \ ).

Para separar las filas, utiliza el punto y coma ( ; ).

E



Por ejemplo, para incluir la siguiente matriz como constante

matricial:

10 20

30 40

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

Por ejemplo, en la siguiente imagen puedes ver una fórmula de

matriz que se ha introducido en las celdas C1:D2:

Y luego tenemos la matriz {10\20;30\40}, que hemos introducido

como constante matricial en la fórmula.

Según la fórmula matricial escrita, hemos multiplicado las celdas

A1:B2 por 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 por 10, el valor de la celda B1 por 11, el valor de

A2 por 20 y el de B2 por 21. Puedes ver el resultado de estas

operaciones en la hoja de cálculo.


4

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.

En el 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.

Si introduces

una constante

matricial en

una fórmula

"normal" (no

de matriz),

solo se tendrá

en cuenta

para los

cálculos el

primer

elemento de

la constante

matricial.



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.

Una de las ventajas de las fórmulas matriciales es que pueden

realizar operaciones complejas, sin necesidad de usar otras fórmulas

intermedias. Además, pueden añadir coherencia y seguridad a las hojas,

al no poder modificar o eliminar únicamente una celda que pertenece a

un rango matricial. Muchas veces también pueden reducir el tamaño del

archivo, en comparación al uso de varias fórmulas distintas para

calcular los mismos resultados.

Por contra, como desventajas podemos decir que en ocasiones

pueden ser más difíciles de entender por parte de otros usuarios que

puedan trabajar con el libro y que, dependiendo del tipo de fórmula

matricial y de la memoria del ordenador, en ocasiones pueden hacer más

lentos los cálculos.

3. REFERENCIAS EXTERNAS (VÍNCULOS)

a 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.

Y


6

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.

También tienes que tener en cuenta que, al trabajar con libros

pequeños, estos 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.

Cuando trabajes con celdas con mucho texto o que tengan fórmulas

largas (como es este caso), puede ser interesante cambiar el tamaño de

la barra de fórmulas, para poder ver todo el contenido de la celda.

Su pulsas el botón Expandir barra de fórmulas , que está en la

parte derecha de la barra de fórmulas, esta barra se hará más alta.

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 .

Si haces doble

clic en la

separación

entre la barra

de fórmulas y

las celdas de

la hoja de

cálculo

(cuando el

cursor cambie

a ), el

tamaño de la

barra de

fórmulas se

ajustará

automáticame

nte al número

de líneas que

se necesite

para ver todo

el contenido

de la celda.


8

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 biblioteca o carpeta Documentos del usuario.

En Windows 7 y Windows 8 la biblioteca Documentos es

distinta para cada usuario del sistema (en Windows Vista se

habla de la carpeta Documentos, ya que el concepto de

biblioteca se introdujo en Windows 7, aunque el

funcionamiento para el caso que estamos viendo es similar).

Para cada uno de los distintos usuarios que pueda haber

definidos, su biblioteca o carpeta Documentos se almacena

dentro de la carpeta Users\nombre_de_usuario del disco

duro, y normalmente suele hacer referencia a una carpeta real

de nombre 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 biblioteca o 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.

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. Para ello,

muestra la pestaña Vista de la Cinta de opciones y pulsa el botón

.

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 la 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, como ya hemos visto. En cambio, si el libro de origen

o fuente está abierto, la referencia externa se muestra sin la ruta de

acceso.

Al abrir un libro que tiene vínculos o referencias externas (es decir,

el libro de destino), aparece un mensaje que indica que, por razones de

seguridad, se ha desactivado la actualización automática de los vínculos.

Pulsando Actualizar se actualizarían los datos desde el libro de

origen, si esta información hubiera cambiado.

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

produzcan

errores al

escribir estas

referencias.


10

4. TRABAJO CON REFERENCIAS EXTERNAS

el 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 pestaña 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 que aparece 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á en este caso

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 este 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.

D

Si desde

dentro de

Excel, con la

opción

Guardar

como,

cambias el

nombre del

libro de

origen o

fuente, debes

tener el libro

de destino

abierto, para

que este

cambio de

nombre se

refleje

automática-

mente en las

referencias

externas que

tenga.

Ten en cuenta

que la acción

de Romper

vínculo no se

puede

deshacer, por

lo que es

recomendable

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.



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.

Mediante el botón Comprobar estado verificaremos 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 este 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 (accesible desde la vista Backstage) 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

predetermina

da de este

cuadro

(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

Opciones de

Excel, en la

casilla

Consultar al

actualizar

vínculos

automáticos

de la sección

Avanzadas.