El concepto de Matriz viene de los lenguajes de programación y de la necesidad de trabajar con varios elementos de forma rápida y cómoda. Podríamos decir que una matriz es una serie de elementos formando filas (matriz bi-dimensional) o filas y columnas (matriz tri-dimensional).

         La siguiente tabla representa una matriz bidimensional:

1

2

3

4

5

         ...ahora una matriz tridimensional:

1,1	1,2	1,3	1,4	1,5
2,1	2,2	2,3	2,4	2,5
3,1	3,2	3,3	3,4	3,4

         Observa por ejemplo el nombre del elemento 3,4 que significa que está en la posición de fila 3, columna 4. En Excel, podemos tener un grupo de celdas en forma de matriz y aplicar una fórmula determinada en ellas de forma que tendremos un ahorro del tiempo de escritura de fórmulas.

         En Excel, las fórmulas que hacen referencia a matrices se encierran entre corchetes {}. Hay que tener en cuenta al trabajar con matrices lo siguiente:

·         No se puede cambiar el contenido de las celdas que componen la matriz

·         No se puede eliminar o mover celdas que componen la matriz

·         No se puede insertar nuevas celdas en el rango que compone la matriz

1. Crea la siguiente hoja:

Si te sitúas en la celda B4, observarás que hemos hecho una simple multiplicación para calcular el precio total de las unidades. Lo mismo pasa con las demás fórmulas.

En vez de esto, podríamos haber combinado todos los cálculos posibles en uno solo utilizando una fórmula matricial.

Una fórmula matricial se tiene que aceptar utilizando la combinación de teclas CTRL+MYSC+Intro y Excel colocará los corchetes automáticamente.

2. Borra las celdas adecuadas para que quede la hoja de la siguiente forma:

3. Sitúa el cursor en la celda B7 e introduce la fórmula:

            =SUMA(B3:E3*B4:E4)

4. Acepta la fórmula usando la combinación de teclas adecuada.

Observa cómo hemos obtenido el mismo resultado tan sólo con introducir una fórmula.

Observa la misma en la barra de fórmulas.  Ahora hay que tener cuidado en editar celdas que pertenezcan a una matriz, ya que no se pueden efectuar operaciones que afecten sólo a un rango de datos. Cuando editamos una matriz, editamos todo el rango como si de una sola celda se tratase.

         Al igual que en las fórmulas normales podemos incluir referencias a datos fijos o constantes, en las fórmulas matriciales también podemos incluir datos constantes. A estos datos se les llama constantes matriciales y se debe incluir un separador de columnas (símbolo ;) y un separador de filas (símbolo \).

         Por ejemplo, para incluir una matriz como constante matricial:

30               25

31               18

Debemos escribir: {30;25\31;18}

1.     
Escribe estas celdas en la hoja2

2.      Selecciona el rango C1:D2

3.      Escribe la fórmula: =A1:B2*{10;20\30;40}

4.      Acepta la fórmula con la combinación de teclas adecuada.

Observa que Excel ha ido multiplicando los valores de la matriz por los números introducidos en la fórmula:

         Cuando trabajamos por fórmulas matriciales, cada uno de los elementos de la misma, debe tener idéntico número de filas y columnas, porque de lo contrario, Excel expandiría las fórmulas matriciales. Por ejemplo:

={1;2;3}*{2\3} se convertiría en ={1;2;3\1;2;3}*{2;2;2\3;3;3}

5.      Selecciona el rango C4:E5

6.     
Introduce la fórmula: =A4:B4+{2;5;0\3;9;5} y acéptala.

Observemos que Excel devuelve un mensaje de error diciendo que el rango seleccionado es diferente al de la matriz original.

7.      Graba si lo deseas la hoja.

         Excel permite utilizar en sus fórmulas referencias a otras celdas, hojas o incluso libros de trabajo. A veces es más práctico dividir el trabajo en pequeños libros y posteriormente unirlos en uno. Imagínate una empresa con tres sucursales, las cuales llevan por separado una serie de hojas. En un momento dado, interesaría unirlas todas en una sola hoja a modo de resumen.

         Excel permite varios tipos de referencias en sus fórmulas:

Por ejemplo, la referencia:

‘C:\Mis documentos\[Ventas.xls]Enero’!A12

haría referencia a la celda A12 de la hoja Enero del libro Ventas.xls que está guardado en la carpeta Mis documentos de la unidad C:

1.     
Crea en un libro nuevo la siguiente hoja:

2.      Guarda el libro con el nombre: Empresa1

3.      Cierra el libro de trabajo.

4.      En un nuevo libro de trabajo, crea la siguiente hoja:

 

5.      Sitúate en la celda B4.

6.      Escribe la fórmula: (suponiendo que la tengas guardada en la carpeta Mis documentos:

='C:\Mis documentos\[empresa1.xls]Hoja1'!B4:D4

7.      Cópiala dos celdas hacia abajo.

8.      Graba el libro con el nombre: empresa2.xls

9.      Abre el libro empresa1.xls

10.  Accede a Ventana – Organizar y acepta la opción Mosaico.

Ahora tenemos dos ventanas correspondientes a los dos libros de trabajo abiertos. Para pasar de una a otra, debemos activarla con un clik en su título o en cualquier parte de la misma. Por ejemplo, si deseamos situar el cursor en la ventana inactiva, primero debemos pulsar un click para activarla y después otro click para situar ya el cursor.

11.  Sitúa el cursor en la celda B4 del libro empresa2.

Observa la barra de fórmulas. Ahora no vemos el camino marcado que hace referencia a un archivo grabado en disco.  Cuando tenemos abiertos los archivos, no se observa el camino de unidades y carpetas.

Si ahora modificamos cualquier dato del libro empresa1, se actualizarían las fórmulas del libro empresa2.

12.  Cierra los dos libros.

         Esta sencilla opción sirve para saber a qué celdas hace referencia una fórmula determinada, posibles errores en fórmulas, etc.

1.      Crea un libro nuevo.

2.     
Crea una sencilla hoja con sus fórmulas:

3. Sitúa el cursor en la celda D2

3.      Accede a  Herramientas  - Auditoría – Rastrear precedentes

4.      Accede a Herramientas – Auditoría – Rastrear dependientes

Excel nos muestra que la fórmula hace referencia al rango B2:C2 (precedentes) y que a su vez, otra celda, la E2, depende del resultado de la celda actual (dependientes).

     A través de esta opción podemos localizar qué celdas dependen de otras en sus fórmulas, a qué celdas hace referencia la fórmula, etc. Incluso podemos, en caso de error, localizar el mismo (opción Rastrear error)

5.      Accede a Herramientas – Auditoría – Quitar todas las flechas

         La protección de hojas nos permite proteger contra borrados accidentales algunas celdas que consideremos importantes. Podemos proteger toda la hoja, el libro entero, o bien sólo algunas celdas.

1.      Accede a Herramientas – Proteger – Proteger hoja y acepta el cuadro de diálogo que aparece.

2.      Intenta borrar con la tecla Supr cualquier celda que contenga un dato.

La hoja está protegida por completo. Imaginemos ahora que sólo deseamos proteger las celdas que contienen las fórmulas, dejando libres de protección el resto de celdas.

3.      Desprotege la hoja siguiendo el mismo método que antes.

4.      Selecciona el rango B2:C4 y accede a Formato – Celdas – (Pestaña proteger).

5.      Desactiva la opción Bloqueada y acepta el cuadro.

6.      Vuelve a proteger la hoja desde Herramientas – Proteger – Proteger hoja.

7.      Cambia algún valor del rango B2:C4

8.      Intenta cambiar algo o borrar alguna celda del resto de la hoja.

Con la opción anterior (Bloqueada), hemos preparado un rango de celdas para que esté libre de protección cuando decidamos proteger toda la hoja. De esta forma no habrá fallos de borrados accidentales en celdas importantes.

Si escribimos una contraseña al proteger la hoja, nos la pedirá en caso de querer desprotegerla posteriormente.

Si elegimos la opción Proteger libro, podemos proteger la estructura entera del libro (formatos, anchura de columnas, colores, etc...)

         Es posible la inserción de comentarios en una celda a modo de anotación personal. Desde la opción Insertar – Comentario podemos crear una pequeña anotación.

1.      Sitúa el cursor en E1 y accede a Insertar – Comentario.

2.      Escribe el siguiente texto:

Descuento aplicado según la última reunión del consejo de administración

3.      Pulsa click fuera de la casilla amarilla.

Dependiendo de qué opción esté activada en el menú Herramientas – Opciones – Ver, podemos desactivar la visualización de una marca roja, la nota amarilla, activar sólo la marca, o todo.

4.      Accede a Herramientas – Opciones y observa en la pestaña Ver (sección Comentarios) las distintas casillas de opción. Prueba a activar las tres saliendo del cuadro de diálogo y observa el resultado.

5.      Finalmente, deja la opción Sólo indicador de comentario activada.

6.      Sitúa el cursor sobre la celda que contiene el comentario.

7.      Pulsa el botón derecho del ratón sobre esa misma celda.

Desde aquí o bien desde Edición, podemos modificar o eliminar el comentario.

En listas de datos agrupados por un campo, es útil mostrar a veces no solo el total general de una columna, sino también los sub-totales parciales de cada elemento común.

1.      Crea una sencilla hoja:

2. Ordénala por Marca.

3.      Selecciona todo el rango de datos (A1:C6)

4.      Accede a Datos – Subtotales.

Excel nos muestra por defecto una configuración para crear sub-totales agrupados por Marca (casilla Para cada cambio en), utilizando la función SUMA y añadiendo el resultado bajo la columna Ventas

5.      Acepta el cuadro.

Observa la agrupación que ha hecho Excel, calculando las ventas por marcas y obteniendo las sumas parciales de cada una de ellas

En el margen izquierdo de la ventana se muestran unos controles para obtener mayor o menor nivel de resumen en los subtotales.

6.      Pulsa los botones                y observa el resultado.

7.      Vuelve a Datos – Subtotales.

8.      Abre la lista de Usar función y elige la función PROMEDIO.

9.      Desactiva la casilla Reemplazar subtotales actuales porque borraría los que ya hay escritos.

10.  Acepta.

11.  Pulsa un click uno a uno en los 4 botones                 y observa el resultado.

12.  Accede a Datos – Subtotales y pulsa en Quitar todos.

Si se quisiera crear subtotales por otro campo (por ejemplo el campo País), deberíamos primero ordenar la lista por ese campo para que Excel pueda agrupar posteriormente la tabla.