Aprender Excel financiero y para MBA E-Book

MEDIAactive

• Herausgeber: Marcombo
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Spanisch

Seitenzahl: 303

Veröffentlichungsjahr: 2020

Beschreibung

Excel es la aplicación de referencia indiscutible para el trabajo con hojas de cálculo. Con este completo manual, y en 100 ejercicios prácticos, aprenderá a sacarle el máximo provecho para usos financieros, administrativos y de gerencia. A lo largo de las páginas de este libro, el usuario aprenderá a: - Presentar sus datos en informes espectaculares utilizando gráficos, minigráficos, tablas dinámicas y formatos condicionales, entre otras herramientas de análisis de datos muy visuales. - Además, aprenderá a presentar diversos escenarios y perspectivas con los comandos de análisis de datos y planteamiento de hipótesis. - Facilitará sus cálculos con las funciones más utilizadas en el entorno empresarial. - Para terminar, se iniciará en la creación de macros para abreviar la realización de tareas repetitivas y habituales en un solo clic.

Leseprobe

Aprender

Excel financiero y para MBAcon 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 Asignarnombre 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 Barrade 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 Rastrearprecedentes.

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.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 deOpciones, 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 CrearTabla.

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