Czytaj książkę: «Aprender Excel financiero y para MBA»
Aprender
Excel financiero y para MBA con 100 ejercicios prácticos
Aprender Excel financiero y para MBA con 100 ejercicios prácticos
© 2014 MEDIAactive
Primera edición, 2014
© 2014 MARCOMBO, S.A.
Gran Via de les Corts Catalanes, 594
08007 Barcelona
www.marcombo.com
«Cualquier forma de reproducción, distribución, comunicación pública o transformación de esta obra sólo puede ser realizada con la autorización de sus titulares, salvo excepción prevista por la ley. Diríjase a CEDRO (Centro Español de Derechos Reprográficos, www.cedro.org) si necesita fotocopiar o escanear algún fragmento de esta obra».
ISBN: 978-84-267-2334-5
Presentación
APRENDER EXCEL FINANCIERO Y PARA MBA CON 100 EJERCICIOS PRÁCTICOS
100 ejercicios prácticos resueltos que conforman un recorrido por las herramientas de Microsoft Excel más relevantes para administración y finanzas. Si bien es imposible recoger en las páginas de este libro todas las prestaciones de Excel 2013, hemos escogido las más utilizadas en el ámbito empresarial. Una vez realizados a conciencia los 100 ejercicios que componen este manual, el lector será capaz de gestionar complejas hojas de cálculo y trabajar con herramientas avanzadas de tratamiento de datos.
LA FORMA DE APRENDER
Nuestra experiencia en el ámbito de la enseñanza nos ha llevado a diseñar este tipo de manual, en el que cada una de las funciones se ejercita mediante la realización de un ejercicio práctico. Dicho ejercicio se halla explicado paso a paso y pulsación a pulsación, a fin de no dejar ninguna duda en su proceso de ejecución. Además, lo hemos ilustrado con imágenes descriptivas de los pasos más importantes o de los resultados que deberían obtenerse y con recuadros IMPORTANTE que ofrecen información complementaria sobre cada uno de los temas tratados en los ejercicios.
Gracias a este sistema se garantiza que una vez realizados los 100 ejercicios que componen el manual, el usuario será capaz de desenvolverse cómodamente con el programa y sacar el máximo partido de las múltiples prestaciones administrativas que ofrece.
LOS ARCHIVOS NECESARIOS
En el caso de que desee utilizar los archivos de ejemplo de este libro puede descargarlos desde la zona de descargas de la página de Marcombo (www.marcombo.com) y desde la página específica de este libro.
A QUIÉN VA DIRIGIDO EL MANUAL
Este libro ha sido concebido para usuarios iniciados en el trabajo con Excel, aunque no es necesario que sea un usuario avanzado para aprovechar el manual. Por otra parte, si es usted un experto en el programa, le resultará también muy útil para consultar determinados aspectos complejos o repasar funciones específicas que podrá localizar en el sumario.
Cada ejercicio está tratado de forma independiente, por lo que no es necesario que los realice por orden (aunque así se lo recomendamos, puesto que hemos intentado agrupar aquellos ejercicios con temática común). De este modo, si necesita realizar una consulta puntual, podrá dirigirse al ejercicio en el que se trata el tema y llevarlo a cabo sobre su propio libro de Excel.
EXCEL
Excel es el programa líder en gestión de hojas de cálculo y es una herramienta indispensable en cualquier empresa, pues ofrece múltiples y avanzadas herramientas de tratamiento de datos.
Este manual le ayudará a sacar máximo provecho a la aplicación a través del uso de herramientas de análisis que darán un aspecto impresionante a sus informes. Hablamos, por ejemplo, de tablas, gráficos, minigráficos, formatos condicionales, esquemas y subtotales.
Además, conocerá las funciones más relevantes para el ámbito empresarial, tanto para el trabajo con datos textuales como para valores numéricos y conocerá las herramientas del grupo análisis, que le permiten plantear de forma clara, organizada y sencilla diversos escenarios.
Finalmente, aprenderá a trabajar con macros, lo que optimizará sin lugar a dudas la realización de operaciones habituales y repetitivas en sus hojas de cálculo.
Cómo funcionan los libros “Aprender...”
Índice
001 Nombrar celdas y rangos
002 Las celdas precedentes y las dependientes
003 Insertar referencias a otras hojas y libros
004 Editar varias hojas simultáneamente
005 Referencias circulares y cálculo manual
006 El relleno automático y el relleno flash
007 Convertir datos en tabla
008 Usar la función subtotales
009 Crear esquema por subtotales de la tabla
010 Crear un esquema de forma automática
011 Crear fórmulas de matriz
012 La edición de fórmulas de matriz
013 Crear constantes de matriz
014 Calcular con constantes de matriz
015 Aplicar formato condicional
016 Aplicar análisis instantáneo
017 Insertar en celda controles de formulario
018 Utilizar el pegado especial
019 Crear y editar un gráfico en Excel (I)
020 Crear y editar un gráfico en Excel (II)
021 Crear gráficos de anillas
022 Crear gráficos de cotización
023 Crear gráficos combinados
024 Gráficos de burbujas y radiales
025 Trabajar con los minigráficos
026 Crear tablas dinámicas
027 Utilizar segmentación de datos
028 Crear un gráfico dinámico
029 Dividir una celda en varias columnas
030 Eliminar duplicados de una serie de datos
031 Establecer validaciones de datos
032 Crear una lista de validación de datos
033 Consolidar datos de varias tablas
034 Crear un histograma o diagrama de Pareto
035 Hacer análisis con Tablas de datos (I)
036 Hacer análisis con Tablas de datos (II)
037 Trabajar hipótesis en diversos escenarios
038 Realizar un resumen de escenario
039 Formular una hipótesis para alcanzar un objetivo
040 Ajustar para lograr un objetivo con Solver (I)
041 Ajustar para lograr un objetivo con Solver (II)
042 Ajustar para lograr un objetivo con Solver (III)
043 Buscar un valor en una fila
044 Buscar con referencias relativas a una celda
045 Usar referencias relativas a una celda
046 Funciones DIRECCIÓN E INDIRECTO
047 Trasponer datos en una matriz
048 Concatenar, largo izquierda, hallar
049 Extrae, reemplazar y sustituir
050 Convertir valores en texto y texto en valores
051 Calcular precio variable con función SI
052 Otras funciones lógicas: Y, O y NO
053 Redondeos controlados (I)
054 Redondeos controlados (II)
055 Sumar bajo condiciones
056 Sumar acumulados por fila
057 Calcular cuotas para un préstamo
058 Calcular inversión necesaria
059 Calcular amortización e intereses
060 Calcular el valor actual de una inversión
061 Calcular el valor futuro de una inversión
062 Calcular plazo de inversión o préstamo
063 Calcular la tasa de interés asumible
064 Calcular el valor presente neto
065 Calcular tasa interna de retorno
066 El valor presente neto no periódico
067 Calcular TIR para flujos no periódicos
068 Calcular tasa interna de retorno múltiple
069 Analizar la tasa interna de retorno múltiple
070 Calcular el valor futuro con tasas variables
071 Retorno sobre inversión con interés variable
072 Calcular interés efectivo e interés nominal
073 Calcular el pago a capital acumulado
074 Calcular el pago de intereses acumulado
075 Decidir si es mejor comprar o contratar
076 Estimar depreciación lineal
077 Calcular depreciación acelerada
078 Depreciación acelerada y por meses
079 Depreciación por disminución variable (I)
080 Depreciación por disminución variable (II)
081 Calcular depreciación acumulada
082 Trabajar con fechas en Excel
083 Realizar cálculos con fechas
084 Trabajar con horas en Excel
085 Extraer horas, minutos y segundos
086 Realizar cálculos con horas
087 Sumar horas trabajadas
088 Combinar horas con otros datos numéricos
089 Calcular promedio, mediana y moda
090 Otras medidas de tendencia central
091 Definir jerarquías
092 Percentiles, frecuencias y K.ESIMO.MAYOR
093 Usar las principales funciones matemáticas
094 Obtener información sobre datos
095 Grabar una macro
096 Ejecutar y modificar una macro
097 Aplicar una macro de otro libro
098 Crear botón de acceso rápido para macro
099 Insertar botón de macro en una hoja
100 Establecer la seguridad para macros
001 Nombrar celdas y rangos
HABITUALMENTE NOS REFERIMOS a celdas y rangos por la identificación de su columna y su fila, pero también podemos darles nombres de manera que luego podamos referirnos a ellos en fórmulas y funciones a partir de los nombres establecidos.
IMPORTANTE
El primer caracter del nombre de una celda o un rango debe ser una letra, un guión bajo o una barra invertida. Después del primer caracter, el nombre puede usar letras, números, puntos y guiones bajos. Un nombre no distingue entre mayúsculas y minúsculas, y no puede ser idéntico a una referencia de celda, ni tener espacios en blanco. Puede tener hasta 255 caracteres (¡que son muchos!).
1. En este primer ejercicio, descargue de nuestra web el archivo Ejemplo1 y abra su hoja Ventas mensuales.
2. Otorgaremos un nombre para las celdas donde se establecen las comisiones y el descuento. Seleccione la celda B26.
3. Active la ficha Fórmulas y haga clic sobre el botón Asignar nombre del grupo Nombres definidos. 
También puede acceder al cuadro Nombre nuevo desde la opción Definir nombre del menú contextual de una celda o un rango de celdas seleccionado.
4. El cuadro Nombre nuevo asigna como nombre el texto de la celda más próxima, que es Descuento y en este caso es perfecto. El siguiente campo, Ámbito, se refiere a la ubicación donde el nombre será recocido. Mantenga la opción Libro seleccionada, para que el nombre afecte a todo el archivo.
5. Puede además añadir algún comentario. Escriba por ejemplo el texto Distribuidores y pulse el botón Aceptar. 
Los comentarios añadidos a un nombre pueden ayudar más adelante a identificar mejor el contenido de la celda o rango.
6. Pulse en la celda B22 e introduzca la siguiente fórmula: =B21* y pulse sobre la celda B26 para insertarla en la función.
7. En la fórmula no se inserta la referencia a la celda sino el nombre que le hemos asignado, que es Descuento. 
Aplique la fórmula y copie esta celda en las dos contiguas: C22 y D22.
Los nombres actúan como referencias absolutas.
8. Ubíquese en la celda D22 y compruebe que el nombre actúa como referencia absoluta sin necesidad de usar el signo $. 
9. Seleccione las celdas B2, a B21 y pulse de nuevo el botón Asignar nombre.
10. En este caso el campo Nombre muestra el texto de la cabecera del rango de celdas seleccionado. Seleccione el ámbito Ventas mensuales para que tenga efecto sólo en la hoja activa, confirme que el campo Hace referencia a indique el rango correcto y pulse el botón Aceptar. 
11. Haga un doble clic en la celda E21, seleccione en la barra de direcciones el rango utilizado como argumento de la función (B2:B20) y escriba la palabra Enero para seleccionar el rango en cuestión. 
12. Tal como lo hizo en pasos anteriores para crear el rango enero, cree los rangos febrero y marzo.
13. Seleccione la celda B27 y pulse en el campo Nombre, a la izquierda de la Barra de fórmulas.
14. Éste se hace editable. Introduzca el texto Comisiones y pulse la tecla Retorno. 
También puede asignar un nombre a una celda o un rango introduciéndolo en el campo Nombre.
15. También este sencillo método le permite dar un nombre a una celda. En este caso se aplica automáticamente a todo el libro.
IMPORTANTE
El Administrador de nombres le permite crear un nuevo nombre desde este cuadro, editar el nombre seleccionado, borrarlo o cambiar el rango de celdas al que se refiere. Además, si pulsa en el botón que está a la derecha del campo Se refiere a, el cuadro se minimiza para permitirle seleccionar un nuevo rango de celdas.
002 Las celdas precedentes y las dependientes
LAS CELDAS PRECEDENTES SON AQUELLAS a las que hace referencia una fórmula y las dependientes aquellas que son utilizadas en las fórmulas de otras celdas. Con los comandos del grupo Auditoría de fórmulas de la ficha Fórmulas de la Cinta de opciones, resulta muy fácil ubicar a unas y a otras de manera gráfica, no importa lo compleja que pueda ser la hoja de cálculo con la que está trabajando.
1. En este ejercicio continuaremos trabajando con la hoja Ventas mensuales del archivo Ejemplo1, para conocer los comandos del grupo Auditoría de fórmulas que nos permiten mostrar en la hoja la vinculación entre celdas con fórmulas. Seleccione la celda B21.
2. Pulse sobre el comando Rastrear precedentes del grupo Auditoría de fórmulas en la ficha Fórmulas.
El comando Rastrear precedentes muestra en qué fórmulas ha sido usada la referencia a la celda seleccionada.
3. Automáticamente aparece una flecha azul que parte de la celda B2 y acaba en la celda seleccionada. En este caso indica que el rango que comienza con la celda B2 es precedente a la celda B21.
4. Muestre ahora las celdas precedentes de B22.
5. Efectivamente, son precedentes de esta celda el rango enero, la celda B22 y la celda B26.
6. También es posible mostrar gráficamente las fórmulas a las que nutre una celda con datos o, lo que es lo mismo, mostrar las celdas dependientes. Haga clic en la celda B26.
7. Pulse ahora el comando Rastrear dependientes del grupo Auditoría de fórmulas.
El comando Rastrear dependientes muestra en qué fórmulas ha sido usada la referencia a la celda seleccionada.
8. En este caso la flecha azul parte de la celda seleccionada y señala las celdas B22 y C22 y D22, pues B26 es uno de los valores implicados en las tres fórmulas. Podríamos comprobarlo pulsando directamente sobre cada una de estas celdas y leyendo la fórmula en la Barra de fórmulas pero en esta ocasión, utilizaremos otro de los comandos de auditoría de fórmulas. Haga clic sobre el comando Mostrar fórmulas, situado a la derecha del comando Rastrear precedentes.
9. Se muestra en cada celda la fórmula que contiene en lugar de su resultado. Para desactivar el comando Mostrar fórmulas, pulse de nuevo en su botón.
10. Seleccione de nuevo la celda B22 y despliegue el comando Quitar flechas del grupo Auditoría de fórmulas pulsando en su botón de punta de flecha.
11. Seleccione la opción Quitar un nivel de precedentes.
12. La flecha que venía del rango enero desaparece, pues este es precedente de forma indirecta, en un segundo nivel. Ahora pulse directamente en el botón Quitar flechas para borrar todas las flechas de la hoja. 
13. Para terminar, guarde los cambios pulsando el comando Guardar de la Barra de herramientas de acceso rápido.
IMPORTANTE
El resto de comandos incluidos en el grupo Auditoría de fórmulas nos permiten localizar errores comunes en fórmulas y depurarlas evaluando cada una de sus partes.
003 Insertar referencias a otras hojas y libros
EN HOJAS DE CÁLCULO MAS COMPLEJAS es posible que necesite crear referencias a celdas de otra hoja e incluso, de otro libro. Al realizar un cambio en los datos de origen, se actualizan también los datos de destino, es decir, los que contienen la referencia externa.
1. En la ficha Vista, grupo Ventana de la Cinta de opciones pulse el botón Nueva ventana.
2. Ahora en la barra de títulos de la aplicación se lee Ejemplo1:2 para indicar que es una segunda ventana del mismo libro. Pulse el botón Organizar todo.
3. En el cuadro Organizar Ventanas, seleccione las opciones Vertical y Ventanas del libro abierto.
4. Ahora puede ver las dos ventanas del libro simultáneamente. Active la hoja Ventas_1T en la segunda ventana.
5. Así será más cómodo, aunque podría realizar el ejercicio saltando de un archivo al otro. Active la hoja Ventas_1t en la ventana 2, introduzca en la celda B2 de esta hoja la función SUMA(), y como argumentos seleccione con un arrastre las celdas B2:D2 de la hoja Ventas mensuales. En la función se indicará entre comillas simples más un signo de admiración al cierre el nombre de la hoja antes del rango (‘Ventas mensuales’!B3:B24).
6. Copie y pegue ésta fórmula en el rango B3:B21 para que se muestren todos los totales.
7. Ahora cree un nuevo libro en blanco y colóquelo sobre la segunda ventana. En la celda A1 de este libro escriba la palabra Descuentos y, el la celda B2 escriba Comisiones. Luego pulse en B1, introduzca un signo Igual (=) y haga clic en la celda B26 de la hoja activa en el libro Ejemplo1.
8. Aparece entre corchetes el nombre del libro, seguido del nombre de la hoja. Ambos datos están entre comillas simples e, inmediatamente después de éstas, un signo de admiración de cierre y la referencia de la celda pegada. Ésta es la sintaxis que deberá usar para teclear una referencia externa. Pulse la tecla Retorno para introducir la referencia y compruebe cómo se muestra el resultado.
9. En la Ventas mensuales del archivo Ejemplo1, ubíquese en la celda B27 y pulse el comando Copiar (ficha Inicio, grupo Portapapeles).
10. Seleccione la celda B2 de la hoja1 del libro nuevo, despliegue el comando Pegar del grupo Portapapeles en la ficha Inicio y, de la lista de opciones de pegado que aparece, seleccione la opción Pegar vínculo, el segundo icono de la sección Otras opciones de pegado.
Evidentemente, si cambia el contenido de las celdas B26 y B27 de la hoja Ventas mensuales en el libro Ejemplo1, cambiarán también los valores de las celdas B1 y B2 en la Hoja1 del libro Nuevo1.
11. Se pega el valor de la celda y observe la Barra de fórmulas que se ha creado una referencia externa usando la misma sintaxis que ya conoce. Guarde el nuevo libro con el nombre Nuevo1 y ciérrelo, y cierre también la ventana Ejemplo1:2.
004 Editar varias hojas simultáneamente
AL TRABAJAR CON LIBROS COMPLEJOS DE varias hojas, puede ser de utilidad realizar una edición conjunta de todas de forma simultánea. Para lograrlo, sólo tendrá que seleccionar todas las hojas que desee modificar y trabajar sobre algunas de ellas. Los cambios se reflejarán en todas.
1. En este ejercicio cambiaremos y unificaremos el estilo de las tres hojas del libro Ejemplo1 y lo primero que deberemos hacer es seleccionarlas. Haga clic en la pestaña Ventas mensuales, pulse la tecla Mayúsculas y, sin soltarla, haga clic en la hoja Ventas_1T.
2. La palabra Grupo en la cabecera nos indica que hay un grupo de hojas activas. Para comenzar, ajustaremos el formato de los datos. Con un arrastre de su borde, en la cabecera, ajuste el ancho de la columna A de modo que todos los contenidos quepan cómodamente en ella.
La palabra Grupo en la cabecera de la aplicación nos confirma que hemos agrupado las pestañas y que las hojas correspondientes se editarán en conjunto.
3. Seleccione las celdas A1 y B1, despliegue el comando Color de relleno del grupo Fuente en la ficha Inicio y escoja algún color que le agrade. Luego cambie también el color de fuente.
4. Cambie el tamaño de la fuente a 20 y modifique la fuente desde el primer comando del mismo grupo de herramientas.
5. Ahora seleccione las filas 1 y 2, muestre su menú contextual con el botón izquierdo del ratón y escoja la opción Insertar.
6. Ha creado dos filas. Escriba en la fila A1 su nombre o el de su empresa, por ejemplo, y ajuste el estilo de la celda a su gusto. Sepa que no podrá agrupar un conjunto de celdas, puesto que es imposible agrupar celdas de otras hojas y es lo que interpreta la aplicación.
7. Cuando haya terminado, pulse sobre las otras dos pestañas del documento y compruebe el resultado obtenido. Ya arreglamos lo que falta más adelante.
8. Regrese a la hoja Ventas mensuales y, con ayuda da la tecla Control, seleccione también la hoja Ventas_1T con un clic en su pestaña.
9. A continuación seleccione la celda B25 e introduzca la siguiente fórmula: =(B23-B24)*comisiones y pulse la tecla Retorno para que se realice el cálculo.
Al agrupar hojas, los cambios que modifiquemos en la hoja visible se aplican en todas las seleccionadas.
10. Una vez calculado el total mensual para comisiones y descuento, calcularemos en la celda B26 el ingreso real. Introduzca la fórmula que viene a continuación: =B23-B24-B25 y pulse Retorno.
11. Pulse en la pestaña Clientes VIP y compruebe que no se ha efectuado ningún cambio adicional en la misma.
12. Haga clic en la celda Ventas_1T y seleccione la celda B25 para comprobar que la fórmula se ha aplicado correctamente en esta celda.
13. Luego repita el procedimiento en la celda B26 y guarde los cambios para terminar.
005 Referencias circulares y cálculo manual
LAS REFERENCIAS CIRCULARES SE DAN cuando una fórmula utiliza la celda que la contiene como uno de sus parámetros, ya sea de forma directa o indirecta. Normalmente, una referencia circular produce un error en Excel; sin embargo, es posible establecer que las referencias circulares sean permitidas.
IMPORTANTE
Si inserta, antes de permitir las iteraciones, una referencia circular indirecta, unas flechas azules indicarán cuáles son las celdas en conflicto.
1. En la hoja Ventas mensuales del libro Ejemplo1, seleccione la celda B28, introduzca la fórmula =Descuento+40% y confirme la entrada pulsando sobre el botón Introducir.
2. Un mensaje de advertencia le indica que está utilizando una referencia circular, es decir, una fórmula que toma su contenido como parte del cálculo. Pulse el botón Aceptar.
En la ficha Fórmulas del cuadro de Opciones de Excel se encuentran las opciones de cálculo de la aplicación.
3. Aparece el término Referencias circulares seguido del nombre de la celda que la contiene en la Barra de estado. El resultado de la celda es 0 porque Excel realmente no es capaz de utilizar referencias circulares, si no activa la opción apropiada. Haga clic en la pestaña Archivo, pulse sobre el comando Opciones y, en el cuadro de diálogo, seleccione la categoría Fórmulas.
4. Marque la casilla Habilitar cálculo iterativo del apartado Opciones de cálculo, cambie las Iteraciones máximas a 1 y pulse el botón Aceptar.
5. Al permitir las iteraciones, el resultado de B28 cambia a 40% de nuevo, pues a su último valor, que era 0, se le ha sumado 5. Seleccione la celda C4, cambie su valor por 8.000 y pulse Retorno.
6. Al efectuarse el cambio en la celda C4, cambia el resultado de B28 a 80% y, por lo tanto, cambia el valor de B24 ya que éste es calculado a partir de aquél. Aunque el valor de B28 no está relacionado con valor de C4, sucede que al realizar una operación en una celda Excel siempre recalcula todas las celdas del libro al que pertenece. Por lo tanto, al valor anterior de la celda Descuento (40% al momento del cálculo) se le ha sumado, según indica la fórmula, un 40% más. Como verá, si trabaja con referencias, circulares puede obtener resultados imprevistos al modificar el contenido de cualquier celda.
Cada vez que se recalcula la hoja, vuelve a calcularse la fórmula de la celda con referencia circular a partir del valor actual de la misma.
7. En el grupo Calculo de la ficha Fórmulas de la Cinta de Opciones, puse el botón Opciones para el cálculo y seleccione la opción Manual.
8. Ahora Excel no realizará ningún cálculo hasta que usted le indique que debe hacerlo. Para ello deberá pulsar la tecla F9. Hágalo y compruebe cómo el contenido de la celda Descuento cambia a 120%. 
Al activar el cálculo manual, la aplicación sólo recalcula las fórmulas del libro cuando el usuario pulsa la tecla F9 o pulsa los botones Calcular ahora o Calcular hoja ahora, ubicados a la derecha del botón Opciones para el cálculo de la Cinta de opciones.
9. Cambie el valor de B28 por 40% nuevamente.
10. Abra el cuadro Opciones de Excel, reactive el cálculo Auto-mático, deshabilite el cálculo iterativo y acepte el cuadro.
11. Para terminar el ejercicio, guarde los cambios realizados.
IMPORTANTE
El grupo Cálculo de la ficha Fórmulas de la Cinta de opciones contiene, además del botón Opciones para el cálculo, que le permite establecer si este debe ser Manual, Automático o Automático excepto en las tablas de datos, dos botones que le permiten calcular toda la hoja o calcular todo el libro cuando la opción seleccionada es Manual.
006 El relleno automático y el relleno flash
EXCEL 2013 RELLENA AUTOMÁTICAMENTE celdas con series de datos (días, meses, números, etc.). La nueva función de relleno rápido, por otra parte, reconoce, si lo hubiera, un patrón en la hoja y lo utiliza para rellenar series.
1. En la esquina inferior derecha de la celda seleccionada aparece un pequeño cuadro negro, el controlador de relleno. Al situar el puntero del ratón sobre ese cuadro, éste cambia a una cruz negra. Seleccione la celda B3, haga clic en su controlador de relleno y, sin soltar el botón, arrastre hasta la celda E3.
2. Ahora todas las celdas tienen el mismo formato y se añade el mes de abril. Además, aparece la etiqueta inteligente Opciones de autorrelleno. Pulse sobre ella.
3. Puede copiar las celdas, rellenarlas con una serie (aplicada por defecto), rellenarlas con formato o sin formato, o rellenarlas con los meses. Escoja Copiar celdas y vea el resultado.
Las opciones de esta etiqueta varían en función del tipo de serie creada.
4. Ahora la nueva celda muestra el contenido de la primera, enero. Si continuara arrastrando el mismo controlador de auto-relleno, se repetirían los mismos tres meses con los que partimos. Seleccione la celda E3, pulse el botón Borrar del grupo Modificar de la ficha Inicio y escoja la opción Borrar todo.
5. Active la hoja Clientes VIP, seleccione la columna A y añada una nueva columna desde la ficha Inicio, grupo Celdas, comando Insertar, opción Insertar celdas.
6. Introduzca un número 1 en la celda A4 y arrastre su controlador de relleno hasta la celda A14.
7. Al partir de sólo un número, Excel lo repite en todas las celdas. Pulse en la etiqueta inteligente Opciones de autorrelleno.
Si arrastra mientras mantiene la tecla Control pulsada, la serie numérica se rellena automáticamente con números consecutivos.
8. En este caso, el programa nos permite copiar las celdas, rellenar con una serie de relleno, rellenar sólo con los formatos de celda o rellenar sin formato. Pulse en la opción Serie de relleno y vea cómo se autorrellenan las celdas con una serie.
9. Seleccione el rango E4:E14, introduzca la palabra Barcelona y pulse Ctrl.+Retorno.
10. Las celdas seleccionadas se rellenan automáticamente con esta palabra. Sitúese en la celda B2, escriba el nombre del primer elemento de la lista, María, y pulse la tecla Retorno.
11. Una vez en la celda inferior, empiece a escribir el nombre del segundo elemento y vea cómo, tan pronto introduce la inicial, Excel muestra una lista de las opciones que considera adecuadas usando como patrón los datos de la primera columna.
12. Es gracias a la herramienta de relleno flash. Pulse la tecla Retorno para confirmar la entrada de ese segundo elemento.
13. La columna B queda rellenada con los nombres de pila correspondientes y aparece la etiqueta Opciones de relleno de Flash. Pulse sobre ella.
14. Puede deshacer el relleno rápido, aceptar las sugerencias y seleccionar las celdas en blanco, si las hubiera, y las celdas modificadas. Elija la opción Aceptar sugerencias y repita el proceso para rellenar la columna de apellidos. 
IMPORTANTE
La nueva herramienta de relleno rápido funciona únicamente cuando el programa reconoce un patrón de datos con alguna coherencia, pero estos datos no tienen que ser siempre nombres. Además, el relleno rápido distingue entre mayúsculas y minúsculas, por lo que puede emplearse, por ejemplo, para convertir fácilmente una lista de términos en minúsculas en una lista en mayúsculas. También es capaz de crear una lista de iniciales a partir de nombres propios.
007 Convertir datos en tabla
LAS TABLAS DAN UN ASPECTO PROFESIONAL a sus datos y permiten el uso de poderosas herramientas de administración de información como los filtros automáticos de las cabeceras de columnas.
IMPORTANTE
También puede convertir un rango en Tabla usando el botón Dar formato como tabla, del grupo Estilos de la ficha Inicio que, además, le permite escoger al momento el diseño que desea aplicar. Por otra parte, puede convertir la tabla de nuevo en rango usando la opción Convertir en rango del grupo Herramientas de la subficha contextual Diseño, de Herramientas de tabla.
1. Descargue de nuestra web el archivo InventarioMarcombo. xlsx, ábralo y compruebe, desplazándose por él, la gran cantidad de datos que contiene.
2. Se trata de casi 800 referencias de libros de nuestra editorial. Vamos a crear una tabla con estos datos. Seleccione A1 y pulse Ctrl.+Mayúsculas+desplazamiento hacia la derecha para seleccionar todas las columnas con datos y luego Ctrl.+Mayúsculas+desplazamiento hacia la abajo para seleccionar todas las filas
3. Pulse el botón Tabla del grupo Tablas, en la ficha Insertar.
El comando Tabla se encuentra en el grupo Tablas de la ficha Insertar y le dirige al cuadro de diálogo Crear Tabla.
4. Como hizo la selección de los datos previamente, el rango que muestra la aplicación en el campo ¿Dónde están los datos de la tabla? es correcto. Mantenga activa la opción Tiene encabezados y pulse en botón Aceptar.
Si selecciona previamente los datos con los cuales desea crear una tabla, al abrirse el cuadro Crear tablas encontrará su referencia en el lugar adecuado.
5. El rango se convierte en una tabla con un estilo predeterminado y se activa la subficha contextual Diseño, de Herramientas de tabla. Pulse en su botón Estilos rápidos, el último.
6. Se despliega una galería de muestras. Seleccione la que prefiera para cambiar el aspecto de la tabla.
7. Haga clic en cualquier celda para ver el efecto obtenido. Las puntas de flecha que están en la esquina derecha de cada cabecera de columna despliegan los filtros de la tabla. Pulse el que corresponde a la columna Título y seleccione la opción Ordenar de A a Z.
8. La columna se organiza alfabéticamente y el resto de la tabla se reorganiza en función de este orden. Despliegue el cuadro de filtros de la columna Id. marca.
9. Pulse en la casilla de selección de la opción Seleccionar todo para desactivarla, seleccione la opción Alfaomega y pulse Aceptar.
10. Compruebe cómo ahora sólo se ven las filas de títulos publicados por esta editorial. Muestre ahora las opciones de filtro de la columna Páginas.
11. Seleccione ahora la opción Filtros de número.
12. Puede establecer una gran cantidad de filtros a partir de un valor establecido. Seleccione la opción Entre.
13. Como verá, con esta opción debe establecer un límite superior y uno inferior para el rango mostrado. Mantenga seleccionada la opción es mayor o igual a e introduzca el valor 100 y, en el campo siguiente, es menor o igual a, introduzca el valor 200 y pulse Aceptar.
14. Sólo se muestran los libros que tienen entre 100 y 200 páginas, ambas inclusive, que han sido publicadas por Editorial Alfaomega. Para eliminar el filtro de páginas, despliegue de nuevo sus opciones y pulse sobre Borrar filtro de “Páginas”.
IMPORTANTE
Puede convertir un conjunto de datos en una tabla de excelente presentación visual con filtros incorporados con sólo pulsar en uno de los estilos del menú Dar formato como tabla.
008 Usar la función subtotales
LA FUNCIÓN SUBTOTALES SE CREA al utilizar la función Auto-suma sobre una tabla. Lo interesante es que esta función permite al usuario aplicar en una misma celda cualquier función de auto-suma a partir de un cómodo menú.
1. En este ejercicio continuaremos trabajando con el libro InventarioMarcombo. Haga clic en la cabecera PC sin IVA y luego pulse la combinación de teclas Ctrl.+Desplazamiento hacia abajo.
2. Una vez se encuentre al final de la columna, seleccione la celda H795, que es la inmediatamente inferior. En la ficha Fórmulas de la Cinta, pulse en el botón Autosuma del grupo Biblioteca de fórmulas.
El argumento 109 en la función SUBTOTALES indica que para el cálculo serán consideradas sólo las celdas visibles (10) y que se efectuará una suma (9).
3. Se inserta en la celda indicada la sumatoria de todos los PVP. de la columna, pero observe la fórmula: se trata de la función SUBTOTALES. En ella, el primer argumento establece el código de la función aplicada, que en este caso es SUMA y, como viene precedido por el número 10, indica que se ignoran las celdas ocultas. El siguiente dato simplemente indica el nombre de la columna analizada. Pulse el botón Introducir.
4. Se muestra el resultado de sumar todos los datos visibles. Despliegue el menú de filtros de la columna Editorial, seleccione la editorial 2001 Editors Choice sin desactivar Alfao- mega y pulse en botón Aceptar.
Tenga en cuenta que también puede combinar filtros de distintas columnas.
5. Se añaden las referencias con la identidad indicada y la suma se actualiza automáticamente. Sin embargo, el dato que nos proporciona la celda H795 es bastante inútil. Vamos a cambiarlo por otro que nos arroje una información más valiosa. ¿Se ha fijado en el botón de punta de flecha de esta celda? Pulse sobre él.
6. Puede en estos momentos cambiar la fórmula aplicada por cualquiera de las que se indican en este menú, o por cualquier otra si pulsa en la opción Más funciones, que le llevaría al cuadro Insertar funciones. Escoja en este caso la función Promedio.
7. La celda nos muestra ahora, efectivamente, el promedio de todos los PV. Con la celda aún seleccionada, coloque el cursor en su esquina inferior derecha y arrastre el ratón hacia la izquierda, para copiar su contenido en la celda G153.
8. La función se pega en la celda indicada y se adapta correctamente para mostrarnos el promedio de la columna I, PVP. Aplique el formato de moneda de dos decimales a todos los datos de las columnas H e I.
La etiqueta inteligente que aparece a la derecha de la celda al añadir una nueva columna a una tabla, se llama Opciones de autocorrección y le permite eliminar el autoformato establecido.
9. Cambie la función de ambos subtotales por Máx.
10. Ahora ya sabe cuál es el coste máximo de las referencias visibles. Guarde los cambios para terminar y pasar al próximo ejercicio.
009 Crear esquema por subtotales de la tabla
EL COMANDO SUBTOTAL, INCLUIDO EN EL grupo de herramientas Esquema de la ficha Datos de Excel 2013, puede calcular subtotales de forma automática en celdas concretas de las tabla, siempre que haya algún dato común y que éste se encuentre en la primera fila de datos.
1. Seleccione la columna D, Editorial, active la ficha Datos y pulse sobre el botón Borrar del grupo Ordenar y filtrar para eliminar el filtro aplicado.
2. Con la columna aún seleccionada, coloque el puntero en borde del ratón en su cabecera. Cuando se transforme en una cruz con puntas en flecha, pulse sobre el mismo y, manteniendo el botón pulsado, presione también la tecla Mayúsculas sin soltarla y arrastre la columna hacia la izquierda, hasta situarla sobre la columna A.
3. De este modo la columna movida se inserta a la derecha de la columna en la que ha sido soltada. Seleccione la cabecera y pulse sobre el botón Ordenar de la A a la Z, también en el grupo Ordenar y filtrar.
4. Seleccione la celda A1 y pulse a continuación sobre el botón Subtotal del grupo Esquema, también en la ficha Datos.
5. En el cuadro Subtotales se indica que se calcularán los subtotales para cada cambio en la columna Editorial (es decir, para cada nuevo nombre de la empresa). Despliegue el campo Usar función y escoja la opción Promedio.
6. Calcularemos los promedios de las columnas PV sin IVA y PVP. Seleccione estas opciones en el siguiente campo, active las opciones Reemplazar subtotales actuales y Resumen debajo de los datos y pulse el botón Aceptar.
Las vistas esquemas le permiten presentar grandes cantidades de datos de forma más cómoda y manejable.
7. Se muestran del lazo izquierdo de la tabla los símbolos del esquema. Para visualizar mejor lo que ha sucedido, pulse sobre el número 2 que está en la cabecera, de modo que se muestren sólo los datos que están en este nivel.
8. Bajo los datos de cada editorial se ha insertado una fila con la función Subtotal, que nos muestra el promedio de cada editorial. Además, al final de la tabla se ha insertado otra fila que calcula el promedio general de los precios de todos los títulos. Pulse sobre el símbolo Mas (+) que se encuentra a la derecha de la fila 8, que contiene el promedio de 2001 Editors Choice.
9. De este modo puede expandir cualquiera de los grupos creados para el esquema y mostrar los detalles del mismo. Seleccione ahora las columnas E, F y G desde sus cabeceras y despliegue el comando Agrupar.
10. Pulse sobre la opción Agrupar de este comando.
11. También podría haber pulsado directamente sobre el icono del comando Agrupar. Como podrá ver, se ha creado un nuevo grupo que une las tres columnas seleccionadas. Contráigalo haciendo clic en el símbolo Menos (-) del grupo.
12. Guarde los cambios para terminar.
IMPORTANTE
La opción Autoesquema crea grupos automáticamente en conjunto de datos siempre que éstos sean analizados por funciones que proporcionen totales parciales o subtotales. Además, deberá haber una primera fila o columna que proporcione los rótulos (por ejemplo, la columna Editorial en este caso), y no puede haber filas en blanco.
010 Crear un esquema de forma automática
EL GRUPO DE HERRAMIENTAS ESQUEMA DE la ficha Datos de Excel 2013 incluye la opción Autoesquema, dentro del comando Agrupar, que permite crear complejos esquemas de forma auto-mática, siempre que los datos estén presentados de la forma correcta. Los datos deben estar agrupados por fórmulas o funciones que calculen totales parciales, no puede haber filas o columnas en blanco en medio de los datos a agrupar, y las celdas que actúan como rótulos de grupo deberán estar en alguno de los extremos de los datos, nunca en medio de éstos.
1. Antes de comenzar este ejercicio descargue de nuestra web el archivo Ejemplo2 y guárdelo en su equipo.
2. Vamos a crear un esquema que organizará toda la información de la hoja de cálculo con un solo clic, pero antes vamos a analizar sus contenidos. En primer lugar, puede ver que los datos están divididos en Ingresos y Gastos. Al pie de cada categoría se presenta el total de cada una. Seleccione la celda B7.
3. En ella se suman los valores de la misma columna para las filas 5 y 6, y así en todas las columnas. Del mismo modo, las celdas de la fila 19 suman los valores de cada columna para las filas 10 a 18.
4. Luego son sumados los totales trimestrales para cada concepto en las filas E, I, M y Q, de nuevo con una sencilla función SUMAR. Compruébelo, por ejemplo, en la celda E5.
5. Una vez analizados los datos, y sin importar cuál es la celda seleccionada, despliegue el comando Agrupar del grupo Esquema, que se encuentra en la ficha Datos, y seleccione la opción Autoesquema.
En este ejemplo, la presencia de la función SUMA en diversas celdas de la hoja es fundamental para el correcto funcionamiento de la función Autoesquema.
6. Utilizando como referencia las celdas con la función SUMA, se ha creado de forma automática un conjunto de grupos, tanto para las filas como para las columnas de los datos. Pulse sobre el símbolo 1 que se encuentra en la parte superior del conjunto de datos.
7. De este modo puede ver los totales por trimestre para cada concepto. Tal como vimos en el ejercicio anterior, puede expandir el campo que quiera con sólo pulsar sobre el signo Más (+) del trimestre que prefiera. Pruébelo.
Si los datos no son apropiados para la función Autoesquema, al intentar usar este comando se abre una advertencia de la aplicación que informa que no se puede crear un esquema. En ese caso deberá crearlo de forma manual.
8. Haga clic ahora en el símbolo 1 de los controles laterales del esquema.
9. Se ocultan los detallas de Ingresos y Gastos. Seleccione las celdas A5:A7, despliegue a continuación el comando Desagrupar y seleccione la opción Desagrupar.
10. Se abre el cuadro del mismo nombre. Mantenga seleccionada la opción Filas y pulse el botón Aceptar.
11. Se desagrupa el conjunto de filas que correspondía a los datos seleccionados. Pulse sobre cualquier celda de datos, despliegue una vez más el comando Desagrupar y seleccione esta vez el comando Borrar esquema.
12. Así de fácil es borrar un esquema de una hoja de cálculo. Guarde los cambios para terminar.
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.
13. Para terminar, puede guardar los cambios.
017 Insertar en celda controles de formulario
LA FICHA DESARROLLADOR DE LA CINTA de opciones permite insertar varios tipos de controles de formularios que sirven para controlar cambios de valor en determinados campos de la hoja de cálculo.
1. Para comenzar este ejercicio, despliegue la vista Archivo, acceda al cuadro Opciones de Excel y seleccione la categoría Cinta de opciones.
2. En el apartado Pestañas principales, active la pestaña Desarrollador y pulse el botón Aceptar. Observe la Cinta.
3. Antes de configurar los botones, haremos un par de cambios en la hoja de cálculo, puesto que los botones no admiten números decimales. Seleccione la celda B24 y, al final de su fórmula actual, añada los caracteres /100 para que el resultado de la fórmula actual se divida entre 100. Repita el proceso con todas las otras celdas del rango B24:D25.
4. Cambie el formato de número de las celdas B28 y B29, y los valores de las mismas por 40 y 3.
5. Ahora aumente el alto de las filas 28 y 29 a 27px.
6. Pulse sobre el comando Insertar del grupo Controles de la ficha Desarrollador que ha añadido a la Cinta de opciones, active el Control de número, que es el cuarto de la primera fila, y trace una diagonal en la celda C28 para insertar en este punto el control.
7. Puede modificar su tamaño arrastrando los tiradores de sus esquinas. Si se deselecciona accidentalmente el control y tiene que volver a modificarlo, pulse sobre él mientras presiona la tecla Ctrl.
8. Haga clic con el botón derecho del ratón sobre el control insertado y seleccione la opción Copiar.
9. Seleccione ahora la celda inmediatamente inferior, C29, y pulse Ctrl.+V para pegar en este punto un duplicado del control.
10. A continuación, vamos a configurar el primer control para que modifique el valor de la celda, B28. Haga un clic con el botón derecho del ratón sobre el mismo y seleccione la opción Formato de control.
11. En el cuadro de diálogo que se abre, Formato de control, debemos establecer cómo funcionará el botón. En el campo Valor actual introduzca el valor 40, que es el valor que queremos mostrar en la celda B28. Establezca el valor mínimo en 30, el máximo en 60 y el incremento en 5.
12. Pulse en el campo Vincular con la celda para colocar el cursor de edición en su interior, haga clic en la celda B28 y pulse el botón Aceptar.
13. Muestre ahora el cuadro Formato de control del segundo control y cambie el valor actual a 3, el Valor mínimo a 2, el Valor máximo a 10, el Incremento a 1 y, en el campo Vincular con la celda inserte la referencia $B$29. Luego pulse el botón Aceptar.
14. Compruebe el funcionamiento de los controles pulsando en sus botones. Estos no funcionarán si el control está seleccionado.
018 Utilizar el pegado especial
LA FUNCIÓN PEGADO ESPECIAL, INCLUIDA en el comando Pegar del grupo de herramientas Portapapeles, da acceso a diferentes opciones de pegado. Por ejemplo, si se activa la opción Fórmulas, éstas serán los únicos elementos de la celda origen que se copiarán en la celda destino y si se activa la opción Valores, se pegarán los resultados de las fórmulas en su lugar de origen.
1. Para este ejercicio, active Descuentos mensuales del libro Ejemplo1. Haga clic con el botón derecho del ratón sobre la celda D21 para que aparezcan su menú contextual y pulse sobre la opción Copiar.
2. Seleccione la celda G22 y haga un clic sobre el botón de flecha del comando Pegar del grupo Portapapeles, que se encuentra en la ficha Inicio.
3. En Excel 2013 la vista previa de pegado nos permite ver cómo se pegarán los datos desde el Portapapeles con sólo situar el puntero del ratón sobre cada uno de los iconos de pegado. Evitamos así tener que deshacer acciones o volver a aplicar formato después de pegarlos. Compruebe los diferentes resultados que podría obtener con las opciones disponibles.
4. Ahora lo dejaremos, puesto que con estas opciones del programa ya hemos practicado en ejercicios anterior; lo que nos interesa el pegado especial, así que pulse sobre esa opción.
5. Se abre así el cuadro Pegado especial, donde se muestra seleccionada por defecto la opción Todo, que hace que se peguen por defecto tanto el contenido como el formato de la celda copiada. Active la opción Fórmulas y pulse Aceptar.
El cuadro Pegado especial le permite escoger exactamente qué atributos desea pegar en la celda seleccionada.
6. Compruebe que con esta opción especial, el atributo Negrita no se ha pegado, sino que sólo lo ha hecho la fórmula. Seleccione ahora la celda H21, pulse nuevamente el botón de flecha del comando Pegar y elija la opción Pegado especial.
7. Sepa que también puede acceder al cuadro Pegado especial pulsando la combinación de teclas Ctrl.+Alt.+V. Esta vez seleccione la opción Formatos y pulse el botón Aceptar.
8. Como ve, el contenido de la celda copiada no se ha pegado y la celda seleccionada se encuentra en blanco. Para comprobar que el atributo Negrita sí se ha pegado, escriba cualquier valor(8) y pulse el botón Introducir de la Barra de fórmulas para confirmar la entrada.
9. Efectivamente, el texto introducido está en negrita. Lógicamente, en función del tipo de datos que esté copiando y pegando (estáticos o vinculados), será más adecuado un pegado especial u otro. Habrá podido comprobar también que algunas de las opciones especiales de pegado se incluyen ya de manera predeterminada en el comando Pegar, lo que evita el tener que acceder al cuadro de pegado especial. Para acabar este sencillo ejercicio, guarde los cambios realizados en la hoja pulsando el icono Guardar de la Barra de herramientas de acceso rápido.
IMPORTANTE
Seleccione la opción Saltar blancos del cuadro Pegado especial para que no se reemplacen los valores de pegado cuando haya celdas en blanco en el área de copia, y marque la opción Transponer en ese cuadro para cambiar las columnas de datos copiados a filas, o viceversa.
019 Crear y editar un gráfico en Excel (I)
EXCEL 2013 HACE QUE CREAR UN gráfico sea un proceso real-mente sencillo gracias a la barra de análisis rápido que vimos en ejercicios anteriores.
IMPORTANTE
Como novedad en Excel 2013, disponemos del botón Gráficos recomendados en el grupo Gráficos, que nos recomienda los estilos de gráfico más adecuados para el tipo de datos que queremos representar y facilita el trabajo a la hora de escoger el gráfico que mejor se adapte a nuestros datos.
1. En este ejercicio crearemos un gráfico de columnas a partir de los precios unitarios sin descuentos de la hoja Descuentos. En primer lugar debe seleccionar el rango de celdas que incluye los datos que va a representar en el gráfico. Haga clic sobre la celda B4, pulse la tecla Mayúsculas y, sin soltarla, haga clic sobre la celda D22.
2. Haga clic en la etiqueta de Análisis rápido, pulse sobre la pestaña Gráficos y escoja la opción Gráfico de barras.
Gracias a la nueva Vista previa en directo podrá ver el efecto que se conseguirá con cada uno de los elementos de las galerías de estilos antes de aplicarlos. Sólo debe situar el puntero del ratón sobre ellos.
3. Se crea un gráfico de forma automática y, al trabajar con Excel 2013, se muestran los botones de ajuste a su derecha. Para cambiar la ubicación de la leyenda, pulse el botón de ajuste que muestra un signo +, haga clic en la opción Leyenda de la lista de elementos que aparece y elija la opción Izquierda.
Ubique fácilmente títulos, leyendas y rótulos donde prefiera.
4. Seguidamente, muestre las mismas opciones, pulse de nuevo en el botón de punta de flecha de la opción Leyenda y elija esta vez Más opciones.
5. La leyenda se selecciona en el gráfico y se abre el panel Formato de leyenda a la derecha del área de trabajo. Pulse sobre el primer icono de este panel, correspondiente a la herramienta Relleno y línea, y despliegue la categoría Relleno.
6. Puede aplicar un color de fondo sólido o bien una imagen, un degradado o una textura. Active la opción Relleno sólido, pulse en la muestra de color y elija el que prefiera.
7. El icono siguiente le permite aplicar efectos al elemento seleccionado y el tercero, las barras, establece la posición de la leyenda. También puede seguir modificando el relleno, el contorno y la ubicación del texto si activa la sección Opciones de texto del panel Formato de leyenda. Así mismo, puede editar el gráfico desde la ficha contextual Herramientas de gráficos. Active la subpestaña Formato.
8. Despliegue el menú Elementos de gráfico y pulse sobre la opción Título del gráfico para seleccionar este elemento.
9. Despliegue la galería de estilos de WordArt y seleccione uno de los estilos predeterminados.
10. Compruebe el efecto conseguido y active luego, en el panel lateral, ahora llamado Formato del título del gráfico, el apartado Opciones de texto. pulse sobre el segundo icono, correspondiente a la función Efectos de texto y muestre los efectos de reflejo disponibles.
11. Muestre la galería de reflejos preestablecidos y elija la variación que prefiera.
12. Si no está contento con el resultado, puede escoger uno de los Estilos de diseños preestablecidos. Pulse sobre el segundo de los botones de ajuste a la derecha del gráfico, que muestra un pincel como icono, y seleccione la opción que mejor se ajuste a sus necesidades.
020 Crear y editar un gráfico en Excel (II)
LOS GRÁFICOS CIRCULARES SON DE GRAN utilidad para demostrar la forma en la que cada una de los valores analizados contribuye al total presentado. Sólo pueden ser utilizados si todos los valores con los que trabaja son positivos.
1. En este ejercicio crearemos un gráfico circular a partir de los datos de la hoja Ventas mensuales del libro Ejemplo1. Ábrala y seleccione desde la celda A4 hasta la E22.
2. Nos centraremos en los datos de la columna Total pero seleccionamos este rango más amplio para disponer de rótulos apropiados. Active la ficha Insertar, pulse sobre el botón Insertar gráfico circular o de anillas y escoja la primera opción para insertar un gráfico circular en 2D.
Puede modificar la posición por defecto de los elementos de su gráfico.
3. Aparece el gráfico en el centro de la hoja y los datos de los que se nutre están seleccionados en la hoja de cálculo. Pulse en el botón de ajuste Elementos del gráfico, que es el primero, despliegue la opción Etiquetas de datos y pulse sobre la opción seleccione la opción Extremo externo.
4. Por ahora el gráfico resulta confuso. Active la ficha Diseño en la Cinta y pulse en el comando Seleccionar datos del grupo Datos.
5. En el cuadro Seleccionar datos de origen puede modificar los datos analizados modificando el primer campo: Rango de datos del gráfico. Sin embargo en nuestro caso nos conviene más eliminar algunas series. Seleccione en el panel de la derecha la serie 1 y pulse el botón Quitar. Repita el proceso con las series 2 y 3, y pulse el botón Aceptar.
6. Alargue el gráfico, tirando desde sus tiradores superior e inferior, para dar más espacio a sus datos.
7. Pulse ahora en la pestaña Diseño y en el botón Mover gráfico, que es el único del grupo Ubicación.
8. En el cuadro Mover gráfico, seleccione la opción Hoja nueva, mantenga el nombre propuesto, que es Gráfico1, y pulse el botón Aceptar.
9. Nuestro gráfico circular se ubica en una hoja nueva con el nombre designado y, al ampliarse, sus contenidos son un poco más fáciles de leer. Observe, con ayuda de la Vista previa activa, el efecto de las distintas muestras del grupo Estilo y escoja el estilo que prefiera.
10. Despliegue ahora las opciones del botón Diseño rápido, en el grupo de herramientas Diseño de gráfico aún en la subficha Diseño, seleccione la segunda opción disponible y observe el resultado.
11. Pulse sobre el tercer botón de ajuste del gráfico, que muestra como icono un embudo (que representa un filtro de datos).
12. También desde aquí habría podido deshabilitar las otras series de datos. Compruebe que al colocar el puntero del ratón sobre el nombre de una comunidad, ésta se muestra destacada en el gráfico. Pulse sobre el fundo del gráfico y guarde los cambios para dar por terminado el ejercicio.
Puede cambiar completamente el aspecto de los elementos de un gráfico usando las múltiples herramientas que Excel 2013 pone a su disposición.
021 Crear gráficos de anillas
LOS GRÁFICOS DE ANILLOS SON MUY similares a los circulares. Iguales que éstos, permiten ver la forma en la que diversos agentes contribuyen a un total pero, en este caso, además es posible trabajar con varias series a la vez.
1. En este ejercicio volveremos a trabajar con la hoja Ventas mensuales del libro Ejemplo1. Ábrala y seleccione el rango A3:D22.
2. Los gráficos de anillos se encuentran también con los circulares. Pulse en el botón correspondiente en el grupo Gráficos de la ficha Insertar y seleccione la última opción.
3. Este tipo de gráficos necesitan más espacio para ser mejor visualizados. Pulse el botón Mover gráfico, seleccione la opción Hoja nueva en el cuadro de diálogo y pulse el botón Aceptar.
4. Cada uno de los anillos representa uno de los tres meses analizados. Haga doble clic en el anillo interior.
5. De este modo se abre el panel Formato de serie de datos. Pulse sobre el botón de la tercera categoría, Opciones de serie, que muestra un gráfico de barras.
6. Reduzca el valor de la propiedad Tamaño del agujero del anillo al 35% y compruebe el resultado en el gráfico.
7. La propiedad Ángulo del primer sector le permite hacer girar todo el gráfico. Deslice su control hasta los 180º mientras ve cómo se modifica.
8. Ahora haga clic en el tercer botón de ajuste de la gráfica, des-active aproximadamente la mitad de las categorías (es decir, de las comunidades autónomas en nuestro ejemplo) y pulse el botón Aplicar para que el cambio tenga efecto.
9. De mismo modo podría seleccionar y eliminar cualquier serie. Haga clic ahora en el primer botón de ajuste, que es Elementos del gráfico, y seleccione el comando Más opciones.
10. Haga clic de nuevo en el icono de barras de la última categoría del panel y ahora despliegue el apartado Opciones de etiqueta.
11. Active la casilla de Porcentaje y desactive la que corresponde a Valor.
12. Una vez más, despliegue el botón de ajuste de Elementos del gráfico y, a continuación, despliegue las opciones de Leyenda y escoja la primera, es decir, Derecha.
13. Una lista tan larga es casi con seguridad más apropiada si está dispuesta verticalmente. Haga clic sobre la leyenda para seleccionar todo el bloque en el que está insertada y, en el panel, pulse en la categoría Opciones de texto.
14. Haga clic en la segunda categoría del nivel siguiente, Efectos de texto (la segunda letra A), despliegue el apartado Sombras y luego las muestras preestablecidas y seleccione alguna.
15. Muestre de nuevo las opciones de leyendo y, en esta categoría, las propiedades de Relleno y línea (pulsando el primer icono).
16. Active la opción Relleno sólido, escoja un color claro y guarde los cambios para terminar.
022Crear gráficos de cotización
EXCEL DISPONE DE UN TIPO DE gráfico pensado para expresar la fluctuación del precio de los valores, aunque también puede ser utilizado para expresar otro tipo de datos que también tengan valores mínimos, máximos y medias.
1. En este ejercicio trabajaremos con un nuevo archivo, el libro Ejemplo3, así que descárguelo de nuestra red, ábralo y muestre su hoja Cotizaciones.
2. En los gráficos de cotizaciones es vital el orden en el que se presentan los datos, que deben incluir al menos fecha, valor máximo, valor mínimo y valor de cierre. Los datos de esta hoja están divididos en cuatro bloques, que representan las cuatro presentaciones posibles de cotizaciones. Vamos con la primera, que incluye los datos indispensables. Seleccione el rango A1:D36 y pulse el botón Gráficos recomendados.
3. El gráfico de cotizaciones no está entre los recomendados. Active la pestaña Todos los gráficos, escoja el tipo Cotizaciones, mantenga activo el primer diseño y pulse Aceptar.
4. Mueva el gráfico a una nueva hoja para poder contemplarlo mejor: la longitud de la línea representa el máximo y el mínimo, mientras la pequeña línea transversal representa el cierre. Pulse sobre este diminuto punto, compruebe que se seleccionen los de todos los datos, active la pestaña Formato y pulse sobre el comando Aplicar formato a la selección.
5. Active el primer icono del panel lateral para mostrar las opciones de Relleno y línea, pulse en la etiqueta Marcador y aumente el ancho a 10 puntos.
6. Deseleccione para ver el efecto y regrese a la hoja Cotizaciones, seleccione el rango E1:I36 (que incluye precio de apertura) y haga clic de nuevo en el botón Gráficos recomendados.
7. En la pestaña Todos los gráficos, seleccione el segundo diseño, compruebe que es el apropiado para los datos que ha seleccionado y pulse Aceptar.
8. Mueva también este gráfico a una hoja separada para observar su diferencia con el anterior.
9. Los rectángulos blancos indican que la cotización subió entre apertura y cierre, mientras las negras indican que bajó. Nuevamente, regrese a los datos de partida, seleccione el siguiente bloque, J1:N36 y cree un nuevo gráfico de cotizaciones, pero esta vez con el tercer diseño, que es el apropiado para expresar volumen, máximo, mínimo y cierre.
Tenga en cuenta que en el caso de los gráficos de cotizaciones, no puede escoger el tipo de gráfico, sino que debe usar el que se ajuste a los datos.
10. Desplácelo a una hoja en blanco y obsérvelo: Es similar al primer tipo estudiado, pero en este caso se añaden unas barras azules que expresan el volumen. Para terminar el ejercicio, vuelva a la hoja Cotizaciones y cree un gráfico con el cuarto tipo de Cotizaciones, utilizando los datos del rango O1:T36.
11. Este tipo de gráfico, como verá, combina el segundo y el tercer tipo para poder expresar todos los datos presentados, es decir, volumen, apertura, máximo, mínimo y cierre. Tenga en cuenta que el orden de las columnas (no así las cabeceras) es fundamental para obtener el resultado esperado.
023 Crear gráficos combinados
LAS GRÁFICAS COMBINADAS SON IDEALES PARA destacar datos contrastantes o de distinta naturaleza. Excel, como siempre, le propone una versión recomendada pero, si considera que no es adecuada, puede crear su propia combinación personalizada.
1. Para el presente ejercicio, abra la HOJA1 del libro Ejemplo3.
2. Tenemos por un lado los ingresos mensuales y, por el otro, los gastos. En el caso de los gastos, como puede ver, están desglosados por conceptos. Seleccione con ayuda de la tecla Ctrl. estos dos rangos de celdas: A3:M4 y A7:M15.
3. En la ficha Insertar de la cinta, pulse sobre el botón de flecha del comando Gráfico combinado, en el grupo de herramientas Gráficos (es el tercero de la segunda fila de iconos.
4. Previsualice los gráficos recomendados en esta categoría y pulse el botón Crear gráfico combinado personalizado.
Aunque la aplicación presenta modelos recomendados de gráficos combinados, el usuario puede crear su propia combinación de los tipos de gráficos que prefiera.
5. En el cuadro Insertar gráfico dispone de un área que le permite designar un tipo de gráfico distinto para cada serie. Como queremos distinguir claramente los ingresos, seleccione de su menú de opciones la penúltima opción de la categoría Línea, la opción Línea acumulada con marcadores.
6. Marque la opción Eje secundario para esta serie, de modo que se añada otro eje de valores a la derecha del gráfico.
7. Conforme va realizando cambios, la vista previa se va actualizando. Seleccione la segunda opción de gráfica de columnas para cada una de las series siguientes (todas ellas son tipos de gastos), visualice el efecto y pulse el botón Aceptar.
8. La gráfica se inserta en la hoja activa. Utilice el botón Mover gráfico de la ficha contextual Diseño para moverlo a una nueva hoja y poder apreciarlo mejor.
9. Este tipo de gráficos le permiten comparar las tendencias de dos tipos de datos distintos. Aquí, por ejemplo, podemos destacar que mientras los ingresos han subido de forma significativa durante el mes de diciembre, el ascenso de los gastos es mucho más sutil. Despliegue el comando Agregar elemento del gráfico, luego la categoría Eje y seleccione el comando Más opciones de ejes.
10. Despliegue las opciones del eje en el panel Dar formato al eje y escoja la opción Eje vertical (Valor).
11. Despliegue la categoría Opciones del eje y cambie el límite mínimo a 600.
12. Escoja ahora la categoría Opciones del eje vertical (Valor) y cambie el límite mínimo a 25000.
13. Finalmente, cierre el panel lateral y guarde los cambios para terminar el ejercicio.
Todos los gráficos con ejes permiten personalizar los límites y las unidades mostradas.
Darmowy fragment się skończył.
