Kitabı oku: «Aprender Excel financiero y para MBA», sayfa 3

Yazı tipi:

011Crear fórmulas de matriz

UNA FÓRMULA MATRIZ REALIZA UN CONJUNTO de cálculos a partir de dos o más argumentos que suelen estar referidos a rangos de celdas, y puede devolver más de un resultado. Después de introducir el texto de la fórmula de matriz, es necesario pulsar Ctrl.+Mayúsculas+Retorno para aplicarla.

IMPORTANTE

Para crear una fórmula de matriz, es imprescindible pulsar la combinación Ctrl.+Mayúsculas+Retorno a la hora de aplicarla y dejar que la aplicación inserte automáticamente las llaves. No intente hacer esto último de forma manual, ya que en ese caso las fórmulas se introducirán como texto.

1. En este ejercicio volveremos a trabaja con la hoja Ventas mensuales del libro Ejemplo1. Ubíquese en la celda E3, introduzca la palabra Total y pulse Retorno.

2. Seleccione el rango de celdas B25:B26 y arrastre su controlador de relleno hasta la celda D26.

3. Sumaremos ahora en la columna E los valores de las columnas B, C y D para cada fila, pero lo haremos sin necesidad de copiar la fórmula celda a celda. Seleccione las celdas E4:E26 e introduzca la siguiente fórmula: =enero+febrero+marzo y NO pulse la tecla Retorno, sino la combinación Ctrl.+Mayúsculas+Retorno.

Sustituiremos la fórmula de la imagen por un fórmula de matriz que equivale a {=E2:E14+F2:F14}.

4. Se aplica una instancia de la fórmula a cada celda del rango seleccionado, y se muestra entre llaves para indicar que se trata de una fórmula de matriz. En ocasiones es posible utilizar fórmulas de matriz para completar hojas de cálculo mucho más complejas. Seleccione el rango de celdas A3:D23 y cópielo.

5. Cree una nueva hoja y pegue en ella, a partir de la celda A1, los datos copiados. Luego seleccione y borre el contenido de las celdas B2:D21.

6. Cambie el contenido de la celda A1 por la palabra Descuentos, puesto que calcularemos en esta hoja los descuentos aplicados a cada comunidad durante los tres meses analizados.

7. Seleccione de nuevo el rango B2-:D21 e inserte la siguiente fórmula: =’Ventas mensuales’!B4:D23*Descuento (para mayor comodidad, puede introducir la referencia del rango B4:D23 de la hoja Ventas mensuales con el ratón después de saltar a la hoja en cuestión.

8. Para aplicar la fórmula como una fórmula de matriz, pulse la combinación Ctrl.+Mayúsculas+Retorno.

9. El resultado es realmente emocionante. Lo que ha hecho la aplicación de manera automática, ha sido crear una instancia de la fórmula en cada una de las celdas del rango seleccionado y se ha realizado todos los cálculos en sólo un instante y sin necesidad de copiar y pegar contenidos. De este modo se ha multiplicado el valor de cada celda del rango por el valor de la celda Descuentos que, como recordará, tiene un nombre de ámbito global, así que no necesita referencia a la página en la que se encuentra y actúa como una referencia absoluta.

Las fórmulas de matriz pueden ahorrarle algunos pasos en el cálculo de datos en tablas de mediana complejidad.

Debe tener en cuenta, sin embargo, que aunque las fórmulas de matriz pueden minimizar las posibilidades de error y facilitar el proceso de creación de numerosas fórmulas, necesitan más memoria para el cálculo, lo que en algunos casos puede ralentizar su equipo. Para terminar el ejercicio, guarde los cambios realizados en el archivo.

012La edición de fórmulas de matriz

LAS CELDAS QUE CONTIENEN FÓRMULAS DE matriz no pueden ser modificadas de forma individual. Cualquier cambio que realice afectará a toda la matriz creada y requerirá que pulse la combinación Ctrl.+Mayúsculas+Retorno para hacerlo efectivo.

1. Cambie el nombre de la Hoja1 creada en el ejercicio anterior en el libro Ejemplo1 por Descuentos mensuales.

2. Seleccione cualquiera de las celdas con fórmula de matriz de la hoja Descuentos que creamos en el ejercicio anterior y pulse la tecla Suprimir.

3. Se abre un cuadro de advertencia de la aplicación que le indica que no puede cambiar parte de una matriz. Pulse el botón Aceptar del cuadro de advertencia.

4. No puede tampoco cambiar la ubicación de celdas que forman parte de la matriz, pero sí que puede mover todo el rango. También puede mover el rango entero de las celdas que contienen los argumentos, y la fórmula continuará trabajando correctamente, siempre que lo haga en bloque. Por otra parte, aunque no es posible cambiar la fórmula de una de las celdas, sí es posible cambiar el texto de toda la fórmula de matriz. Pulse en la barra de direcciones para poder editar su contenido y observe cómo desaparecen sus llaves.

5. Vamos a cambiar la fórmula para que calcule las comisiones pagadas, que calcularemos sobre el precio después de descuentos. Para ello cambie la función actual por la que presentamos a continuación: =(‘Ventas mensuales’!B4:D23-(‘Ventas mensuales’!B4:D23*Descuento))*Comisiones y pulse la tecla Retorno.

6. ¡Error! Ya le hemos dicho cuál es la combinación adecuada. Acepte la advertencia y pulse Ctrl.+Mayúsculas+Retorno.

7. Ahora sí se efectúa el cálculo indicado. También para introducir un cambio en una fórmula de matriz debe utilizar la combinación de teclas Ctrl.+Mayúsculas+Retorno y, como puede ver, al cambiar la fórmula matriz, ésta es modificada en todas y cada una de las celdas del rango que la contienen. Seleccione a continuación el rango de celdas B2:D21 de la hoja de descuentos y arrastre su controlador de relleno hasta la ceda E21.

8. Se rellena el rango añadido y los resultados parecen correctos pero, si observa la fórmula en alguna de las celdas de la columna E, podrá comprobar que el rango de origen es E4:G23, cuando debería ser B4:E23. Para arreglarlo, seleccione el rango de celdas B7:E21, cambie las dos referencias a las celdas B23 por referencias a la celda E23, de modo que la fórmula quede así: =(‘Ventas mensuales’!B4:E23-(‘Ventas mensuales’!B4:E23*Descuento))*Comisiones y, para aplicarla de nuevo como una fórmula de matriz, pulse la combinación de teclas Ctrl.+Mayúsculas+Retorno.

9. Los resultados no verían pero ahora la fórmula es perfecta, como podrá comprobar en cualquiera de las celdas de la matriz. Guarde los cambios para terminar.

Para ampliar el rango de celdas que comprende la matriz, deberá seleccionar primero el conjunto de celdas ampliado y luego modificar la fórmula matriz. No olvide usar la combinación de teclas requerida: Ctrl.+Mayúsculas+Retorno.

IMPORTANTE

Si modifica el valor de una de las celdas utilizadas para el cálculo de la fórmula de matriz (y no de las celdas en las que ha creado la matriz), los valores de la matriz se actualizan al momento.

013 Crear constantes de matriz

LAS FÓRMULAS DE MATRIZ PUEDEN USAR constantes de matriz. Éstas están constituidas por listas de elementos separados por barras inclinadas hacia la izquierda (\) o por puntos y comas (;). Las primeras crean una matrices horizontales o de fila y los segundos crean matrices verticales o de una columna. Las constantes de matriz se escriben entre llaves y también requieren el uso de la combinación Ctrl.+Mayúsculas+Retorno para ser aplicadas.

IMPORTANTE

Aunque en este ejercicio puede resultar difícil visualizar la utilidad de las constantes de matriz, cuando trabaje con fórmulas se dará cuenta de que en ocasiones pueden ser de enorme ayuda.

1. Para este ejercicio inserte una nueva hoja en el libro Ejemplo1 y cambie su nombre por la palabra Matriz.

2. Conozcamos las constantes de matriz. Seleccione el rango A1:F1 e introduzca la siguiente fórmula: ={1\2\3\4\5\6}. Al terminar pulse la combinación que ya conoce, Ctrl.+Mayúsculas+Retorno.

3. Cada uno de los valores introducidos se ubica en de las celdas seleccionadas. Seleccione el rango A3:A8 e introduzca la siguiente fórmula: ={2;3;4;5;6;7}. Al terminar pulse Ctrl.+-Mayúsculas+Retorno.

Las llaves de una constante de matriz sí que deben ser introducidas de forma manual.

Al separar los valores con una barra inclinada hacia la izquierda, se organizan en una matriz horizontal.

4. Ha creado de esta forma una matriz de constantes vertical. Elimine los datos introducidos en los pasos anteriores y seleccione las celdas A1 a 3C.

5. Introduzca la fórmula ={1\2\3\4\5\6\7\8\9} y pulse Ctrl.+Mayúsculas+Retorno.

6. Se crean tres filas que repiten cada uno de los tres primeros valores introducidos y lo que queremos es que se inserten todos los valores, sin repetirse. Pulse F2 para hacer editable la fórmula.

7. Seleccione la barra inclinada que está entre los números 3 y 4 y sustitúyala por un punto y coma.

8. Repita la operación con la barra situada entre los números 6 y 7, y pulse Ctrl.+Mayúsculas+Retorno.

9. Ahora sí ha conseguido la matriz que buscábamos. Evidentemente puede utilizar constantes de matriz para realizar cálculos. Seleccione la matriz creada.

Las barras inclinadas indican que los datos se organizan en filas y los signos de punto y coma, que lo hacen en columnas.

10. Pulse la tecla F2 para hacer editable la fórmula que la creó, añada al final de la fórmula, después de la llave de cierre, el texto *10 y pulse la combinación de teclas Ctrl.+Mayúsculas+Retorno.

11. El número mostrado en cada celda ahora es el producto de multiplicar el número de la matriz por 10. Seleccione el rango A5:C7 e introduzca la siguiente fórmula: =A1:C3-{1\2\3; 4\5\6; 7\8\9} para indicar a la aplicación que debe restar a cada uno de los valores del rango A1:C3, los valores que se encuentran dentro de la matriz

12. Pulse Ctrl.+Mayúsculas+Retorno y vea el resultado, que es exactamente el que hemos anticipado.

13. Para acabar el ejercicio, guarde los cambios realizados en el libro activo.

014 Calcular con constantes de matriz

LAS CONSTANTES DE MATRIZ NORMALMENTE SON de verdadera utilidad cuando se usan dentro de fórmulas, combinadas con referencias a los contenidos de una hoja de cálculo. En este ejercicio la utilizaremos para estimar las ventas totales por comunidad que obtendríamos si se registrara un aumento de un 2%, un 5% y un 10% en las ventas

1. En este ejercicio vamos a calcular las ventas totales del primer trimestre del libro Ejemplo1. Abra su hoja Ventas_1T e introduzca en el rango D3:F3 los siguientes valores porcentuales: 2%, 5% y 10%, uno en cada celda. Puede aplicarle el formato de las celdas A3:B3 para mantener la uniformidad de la hoja.

2. Calcularemos las ventas obtenidas con un aumento en los porcentajes indicados. Seleccione ahora el rango D4:F26, donde se ubicarán los resultados.

3. Introduzca en la selección la siguiente fórmula: =(B4:B26)* {1,02\1,05\1,1}.

Al usar constantes de matriz dentro de una fórmula, el resultado tiene dos pares de llaves. El par introducido manualmente por el usuario para las constantes y el par creado de forma automática por la aplicación al aplicar la fórmula, que la contiene entera.

4. Esta fórmula cogerá cada celda del rango indicado, B4:B26, multiplicará su valor por cada uno de los tres valores de la matriz, es decir, por 1,02, por 1,05 y por 1,1. De este modo calculará el resultado de sumar al valor de origen el 2%, el 5% y el 10% y los organizará en una matriz. Como separamos las constantes de la matriz mediante el uso de puntos y comas, la matriz se creará en columnas. Así, alineará los primeros resultados en la primera fila seleccionada (D), los segundos en la segunda fila (E) y los terceros en la tercera fila (F).

5. Pulse Ctrl.+Mayúsculas+Retorno para aplicar la fórmula como una fórmula de matriz.

6. Efectivamente, se crea una instancia de la fórmula para cada una de las celdas seleccionadas y el resultado es el correcto.

7. Ubíquese ahora en la celda H4 o cualquier otra que esté en blanco, introduzca un signo Igual (=) y seleccione las celdas D2 a D14 crear una referencia al rango.

8. Pulse la tecla F9 y compruebe cómo las referencias son sustituidas en la fórmula por la serie de valores de origen. Observe además que los valores se han insertado precisamente dentro de llaves, lo que quiere decir que se ha creado una constante de matriz con los valores en cuestión. Seleccione todo el texto de la fórmula y pulse Ctrl.+X para cortarlo.

9. Seleccione a continuación el rango de celdas H6 a H26 y pulse la combinación de teclas Ctrl.+V para pegar en este rango el texto de la constante de matriz que obtuvimos al pulsar la tecla F9.

10. Una vez pegada la fórmula, pulse Ctrl.+Mayúsculas+Retorno para terminar.

11. Evidentemente hay otros métodos de lograr el mismo resultado, pero ahora nos ha servido para demostrar lo sencillo que es convertir un rango de celdas en constantes de matriz.

Para que la matriz se cree correctamente, el signo de separación utilizado deberá ser el apropiado para la orientación que busca y el rango seleccionado deberá coincidir con los valores introducidos en número de entradas y orientación.

IMPORTANTE

Si su equipo está configurado en inglés, el signo utilizado para establecer la matriz horizontal será la coma (,). Esto se modifica en los equipos configurados en castellano, pues la coma queda reservada para indicar el uso de decimales.

015 Aplicar formato condicional

LA HERRAMIENTA FORMATO CONDICIONAL permite marcar fácilmente excepciones o tendencias en los datos con degradados de color, barras de datos y conjuntos de iconos que cumplan una regla concreta, que puede ser modificada o definida desde el administrador de reglas.

IMPORTANTE

Los esquemas de visualización enriquecidos, entre los que se incluyen gradientes, umbrales e iconos indicadores de rendimiento, permiten destacar tendencias importantes en series de datos. Es posible aplicar reglas de formato condicional a un rango de celdas, a una tabla de Microsoft Excel o a un informe de tabla dinámica.

1. En este ejercicio trabajaremos con el documento Ejemplo2. Imaginemos, en primer lugar, que queremos resaltar los gastos de más de 100 euros. Seleccione los rangos de celdas que queremos analizar: B10:D18, F10:H18, J10:L18 y N10:P18. Pulse el botón Formato condicional del grupo de herramientas Estilos de la ficha Inicio.

Utilice la tecla Ctrl. como modificador para poder realizar esta selección con arrastres de ratón.

2. Como puede comprobar, el formato condicional nos permite como utilizar barras de datos, escalas de color o conjuntos de iconos. Haga clic sobre la opción Resaltar reglas de celdas.

3. Es posible resaltar las celdas que contienen valores superiores o inferiores a un número, las que contienen un texto o una fecha concretos, etc. Haga clic sobre la opción Es mayor que.

4. Se abre el cuadro Es mayor que. En el campo Aplicar formato a las celdas que son mayores que escriba el valor 100.

5. A medida que se van introduciendo los valores, se van marcando las celdas que cumplen la regla con el formato seleccionado por defecto, Relleno rojo con texto rojo oscuro. Haga clic en el botón de punta de flecha del campo Con, elija la opción Relleno rojo claro y pulse el botón Aceptar.

6. Observe el cambio. Haga clic nuevamente en el botón Formato condicional, pulse sobre la opción Escalas de color y, seleccione el segundo estilo de la primera fila.

Pruebe con los diferentes estilos de formato condicional que ofrece Excel 2013 hasta dar con el que más se ajusta a sus necesidades.

7. Contemple el resultado, haga clic una vez más en el botón Formato condicional del grupo Estilos, pulse sobre la opción Borrar reglas y, del submenú que se despliega, elija Borrar reglas de las celdas seleccionadas.

8. Pulse una vez más sobre la herramienta Formato condicional y elija la opción Nueva regla en esta ocasión.

9. Se abre así el cuadro de diálogo Nueva regla de formato. Mantenga la opción Aplicar formato a todas las celdas según sus valores y haga clic en el botón de punta de flecha del campo Estilo de formato.

10. Los estilos de formato pueden ser escalas de 2 o 3 colores, barras de datos o conjuntos de iconos. Haga clic sobre la opción Conjuntos de iconos.

11. Haga clic en el botón de punta de flecha del campo Estilo de icono y seleccione la primera opción, con tres flechas.

12. Seguidamente cambiaremos los valores que tomará como base la regla. En el campo Tipo, elija la opción Número.

13. Cambie el valor de este primer parámetro por 100.

14. Cambie también el tipo del siguiente por número y el valor, por 50 y pulse el botón Aceptar para aplicar la regla.

15. Deseleccione el rango de celdas pulsando en una libre para ver el efecto conseguido y guarde los cambios pulsando el icono Guardar de la Barra de herramientas de acceso rápido.

Según la regla creada, las celdas con un valor igual o superior a 10 muestran la flecha verde, las de un valor entre 100 y 50 muestran la flecha amarilla y las que de un valor inferior a 50 muestran la flecha roja.

IMPORTANTE

Si su hoja de cálculo tiene celdas con un formato condicional, puede localizarlas rápidamente usando el comando Ir a Especial. En el cuadro del mismo nombre, deberá activar la opción Celdas con formatos condicionales antes de proceder con la búsqueda.

016 Aplicar análisis instantáneo

EXCEL 2013 OFRECE UNA NUEVA HERRAMIENTA de análisis instantáneo de datos que permite convertir una lista de datos en un gráfico o tabla en muy pocos pasos, así como obtener una vista previa con formato condicional, minigráficos o gráficos.

IMPORTANTE

La galería de análisis rápido se divide en varias pestañas según el resultado que queramos obtener al realizar el análisis (cambio de formato, obtención de gráficos, cálculo de totales y creación de tablas o minigráficos.) Si en cualquier momento desea abrir la galería de análisis rápido y no logra ver la etiqueta flotante, sólo tiene que pulsar la combinación de teclas Ctrl.+Q para que se abra al instante.

1. Para comenzar este ejercicio, abra el libro Ejemplo1 y seleccione el rango de celdas B4:D22, donde se encuentran los valores de las ventas mensuales.

2. El icono de la herramienta Análisis rápido aparece en la esquina inferior derecha del rango seleccionado. Púlselo.

3. Se muestra la galería de análisis rápido. La primera ficha, Formato, permite aplicar rápidamente el formato condicional. Sitúe el puntero del ratón sobre la opción Escala de colores de esta pestaña y vea el efecto sobre el rango de celdas seleccionado.

4. Funciona con vista previa activa. Seleccione la opción Mayores que para abrir el cuadro Es mayor que.

5. Acepte la regla sin cambiarla pulsando el botón Aceptar.

6. Como ve, la etiqueta flotante de análisis rápido continúa disponible. Despliegue su galería nuevamente y seleccione la opción Barras de datos para aplicar este formato condicional.

7. Se añaden las barras a las celdas seleccionadas, pero no se borra el formato Es mayor que aplicado. Si desea eliminar el formato condicional, sólo tiene que seleccionar el rango puede hacerlo desde el último botón de la galería. Active ahora la categoría Gráficos pulsando en su pestaña.

La función Análisis rápido aplica fácil y rápidamente formatos condicionales, y proporciona en pocos pasos complejos gráficos de datos.

8. Desde esta galería puede crear un gráfico estándar. Pasee el ratón sobre las diferentes opciones y observe la previsualización de cada tipo de gráfico. Si escoge alguna, se creará el gráfico indicado en medio de la hoja activa. La opción Más gráficos le lleva directamente al cuadro de diálogo Insertar gráfico. Active la galería Totales pulsando sobre su pestaña.

9. Las opciones de esta galería realizan cálculos automáticos a partir de los datos proporcionados. Pulse el botón de punta de flecha que señala hacia la derecha en la misma galería para ver las opciones que vienen a continuación.

Si desea desactivar la función, debe acceder a la categoría General del cuadro de opciones de Excel y desmarcar la opción Mostrar opciones de análisis rápido durante la selección.

10. Como verá, es posible aplicar los cálculos de totales por columnas o por filas. Si aplicara alguno ahora, se borrarían los datos actuales de las fila 23 o de la columna E según la opción que utilizara. Active la pestaña Tablas.

11. Desde esta galería puede convertir una lista de datos en una tabla, pero no puede hacer lo contrario. Además, con un sólo clic, puede crear una tabla dinámica que se insertará en una nueva hoja. Active para terminar la pestaña Minigráficos y, una vez más, previsualice el efecto de sus opciones colocando el puntero del ratón sobre cada una de ellas.

12. El minigráfico expresa en la celda del total de cada comunidad las variaciones registradas en los tres meses analizados. Pulse en una celda de la hoja para cerrar la galería.