APLICACIONES PRACTICAS CON EXCEL

Por Johnny Pacheco Contreras

Excel es una herramienta muy eficaz para manipular datos y convertirlos en información. Por ello, este libro le brinda las técnicas para el uso de las funciones y herramientas de manera que pueda obtener información y le permita tomar decisiones.

Conocerá y explorará la interface de Excel 2016, aprenderá a desplazarse por los datos en una hoja de

• Idioma: Español
• Editorial: Editorial Macro
• Fecha de lanzamiento: 24 jul 2019

Johnny Pacheco Contreras

Ingeniero industrial egresado de la Universidad Nacional de Ingeniería (UNI), con especialización en Finanzas Corporativas en la Universidad de Ciencias Aplicadas (UPC). Actualmente, cursa estudios de maestría en Ingeniería Industrial. Ha desempeñado funciones en empresas privadas en el área financiera. Es consultor en soluciones empresariales en el uso de Microsoft Excel y cuenta con la certificación Microsoft Office Specialist Master. Tiene más de cinco años de experiencia docente a nivel de pregrado e imparte cursos de extensión en temas financieros, ingeniería e inteligencia de negocios con Excel.

Vista previa del libro

BIBLIOGRAFÍA

Introducción

Aplicaciones prácticas con Excel 2016 le brinda el conocimiento necesario para el uso de las diversas funciones y herramientas de Microsoft Excel, un programa eficaz para manipular datos y convertirlos en información con el objeto de tomar decisiones.

En el capítulo 1 el lector aprenderá cómo iniciar este programa, cómo es su interfaz, en qué consiste la cinta de opciones y cuáles son las partes que las componen. También sabrá acerca del manejo de las vistas de Excel, el uso del zoom, la división de paneles y lo referido a las acciones de inmovilizar, mostrar y ocultar las líneas de la cuadrícula.

En el capítulo 2, por su parte, se explicará la manera de abrir, guardar y cerrar libros de Excel. Asimismo, se describirá el área de trabajo y la diferencia entre celda y rango. Por otro lado, se detallará cómo realizar algunas acciones tales como el desplazamiento de los datos en una hoja de cálculo, la selección de datos (con el mouse, teclado o comando), la introducción o modificación de datos, la búsqueda y el reemplazo de datos, la revisión ortográfica y la inserción de comentarios.

En el capítulo 3 se desarrollará, mediante ejemplos con cálculos sencillos, la creación de fórmulas y el uso del símbolo dólar ($) en las fórmulas para obtener los resultados esperados.

En el capítulo 4 el lector aprenderá sobre el uso de las herramientas para cambiar la apariencia de los datos mostrados a través del uso del formato fuente, alineación y número. Además, se plantearán casos prácticos del uso de formato personalizado y del formato condicional, así como de los estilos y los temas.

En los capítulos del 5 al 11 se desarrollará, también mediante ejercicios y casos prácticos, el uso de funciones tales como las matemáticas y trigonométricas, las estadísticas, las de texto, las de hora y fecha, las lógicas y las de búsqueda e información.

En el capítulo 12 el lector conocerá cómo crear una tabla y en cómo se usan las herramientas ordenar y filtrar para mostrar los datos que cumplan con un criterio.

En el capítulo 13 se explicará cómo insertar imagen, formas, SmartArt, WordArt y texto con el objeto de mejorar la presentación de los datos en una hoja de cálculo.

En el capítulo 14 se proporcionarán los pasos para crear informes de tablas dinámicas que permitan resumir datos y convertirlos en información vital para la toma de decisiones en una gestión de actividades.

En el capítulo 15 el lector conocerá el uso de la herramienta de gráficos, cómo crear desde los gráficos más simples hasta los más visuales, cuáles son sus diferentes tipos y cómo usar el eje secundario para graficar valores pequeños versus valores muy grandes.

Finalmente, en el capítulo 16 el lector aprenderá sobre cuáles son las herramientas necesarias para imprimir una hoja de cálculo, ajustar el ancho de la misma, repetir la fila superior en todas las hojas e insertar en ellas el encabezado y el pie de página.

A través de cada uno de estos aportes y mediante la variada información ofrecida, este libro se presenta como una obra de gran valor, útil para todos aquellos estudiantes y profesionales de distintas carreras de nivel superior, que estén interesados en el manejo de Excel y necesiten tomar importantes decisiones una vez convertidos los datos en información.

Introducción a la interface Excel 2016

1.1 Iniciar Excel

1. Inicie Excel 2016 siguiendo el método necesario para abrir el programa. Por ejemplo, si trabaja con Windows 8 o 10, simplemente haga clic o toque en el mosaico Excel 2016. También puede iniciar pulsando las teclas Windows + R, se muestra el cuadro de diálogo Ejecutar en el cual debe digitar la palabra Excel.

2. Luego, haga clic en el botón Aceptar.

Una vez iniciado Excel 2016, lo primero que se ve es la pantalla inicio. Esta nueva característica le ayuda a elegir rápidamente un archivo reciente o comenzar a crear uno en blanco o desde una plantilla.

1.1.1 Abrir un libro de trabajo

Para abrir un libro de Excel, puede abrir un libro en blanco. También puede abrir un libro existente o una plantilla de Excel.

a. Abrir un libro en blanco

1. Haga clic en la pestaña Archivo.

2. Haga clic en el botón Nuevo.

3. Luego, haga clic en Libro en blanco.

b. Abrir un libro existente

1. Haga clic en la pestaña Archivo.

2. Haga clic en el botón Abrir.

3. Luego, haga clic en el botón Examinar.

4. Excel muestra el cuadro de diálogo Abrir.

5. Seleccione la unidad de disco o carpeta donde se encuentra el archivo existente.

6. Haga clic en el archivo existente y luego, haga clic en el botón Abrir.

c. Abrir una plantilla existente

1. Haga clic en la pestaña Archivo.

2. Haga clic en el botón Nuevo.

3. Luego, haga clic en una Planilla o busque más plantillas.

1.2 Entorno de Excel

1.2.1 Cinta de opciones

Todos los comandos necesarios para trabajar con Excel 2016 se encuentran en la cinta de opciones. En Excel 2016 la cinta de opciones aparece minimizada para que el usuario se pueda dedicar a trabajar con sus datos y no a ver las herramientas. A la izquierda de los botones de operación se encuentra el botón Opciones de presentación de la cinta de opciones.

Lo principal en una cinta de opciones son sus pestañas. Excel 2016 tiene siete pestañas que aparecen por defecto (Inicio, Insertar, Diseño de página, Fórmulas, Datos, Revisar y Vista), además de contar con las pestañas Desarrollador y PowerPivot (oculta por defecto), la pestaña Complementos (solo cuando instala un programa de terceros).

Cada pestaña organiza sus comandos en grupos; por ejemplo, en la pestaña Inicio, los comandos para aplicar Negrita, Cursiva y Subrayado se encuentran organizados en el grupo Fuente. Algunos grupos poseen un pequeño botón (al extremo derecho del nombre de grupo) que permite activar o desactivar algunos cuadros de diálogo o paneles.

1.2.2 Barra de título

Muestra el nombre de la aplicación, además del nombre del archivo actual. Por defecto, la barra de título muestra el nombre Libro1 cuando abre Excel 2016.

1.2.3 Barra de fórmulas y cuadro de nombres

En la barra de Fórmulas podrá ingresar datos directamente a las celdas y, también, modificar fórmulas o funciones. Esta barra es ideal cuando necesita ver un dato sin ningún formato, ya que los formatos alteran la vista de los datos, mas no el dato en sí. La siguiente imagen muestra la barra de fórmulas con una función en modo edición.

El cuadro de nombres permite verificar la celda activa, y constituye una manera rápida de dirigirse hacia una celda o rango. Por otra parte, también alberga los nombres de rangos aplicados a una celda o rango. Una lista de nombres de rango desde el cuadro de nombres se muestra en la siguiente imagen.

1.2.4 Zona de trabajo

a. Libro

Un libro de trabajo es el archivo que se crea con Excel, es decir, todo lo que haga en este programa se almacena formando el libro de trabajo.

Los libros de trabajo de Excel tienen la extensión .XLSX para que la computadora los reconozca como tales.

Cuando se inicia una sesión de Excel automáticamente se abre un nuevo libro de trabajo con el nombre provisional de Libro1.

Piense en un libro como un cuaderno de anillado. Cada libro contiene al menos una hoja, y un nuevo libro posee una o más hojas nombradas hoja1, hoja2 y hoja3. Las personas utilizan los libros para organizar, gestionar y consolidar datos.

b. Hoja de cálculo

Es uno de los distintos tipos de hojas que puede contener un libro de trabajo. Es una herramienta muy útil para todas aquellas personas que trabajan con gran cantidad de números y necesitan realizar cálculos u operaciones con ellos.

Es como una gran hoja cuadriculada formada por 16 384 columnas y 1 048 576 filas. Las hojas de cálculo están formadas por columnas y filas.

c. Encabezado de columna

Los encabezados de columna están representados por letras, que van desde A hasta XFD. Esto equivale a 16 384 columnas.

Una columna es el conjunto de celdas seleccionadas verticalmente. Cada columna se nombra por letras, por ejemplo, A, B, C,.......AA, AB,........AAA, AAB……XFD.

d. Encabezados de fila

Los encabezados de fila están representados por números. Puede encontrar desde la fila 1 hasta la fila 1 048 576.

e. Celda y celda activa

La celda activa es aquella celda a la que se da un clic. Se puede reconocer una celda activa cuando el borde de la celda es grueso y de color verde bien pronunciado. Solo se puede elegir una celda activa. Las celdas son la intersección de una fila por una columna. Cada celda tiene un nombre, por ejemplo, A1, C5 o H345.

f. Rango

Un rango es un conjunto de celdas. Su representación es Celda inicio: Celda fin. Por ejemplo, en la siguiente imagen el rango es: B2:E10.

1.2.5 Barra de estado

La barra de estado se sitúa en la parte inferior de la ventana del programa de Microsoft Excel. Muestra automáticamente cálculos básicos de un rango, el modo de celda, las vistas del libro, el zoom, entre otros detalles.

a. Modo de celda

• Listo: para indicar un estado general.

• Introducir: para indicar el modo de introducción de contenido.

• Modificar: para indicar el modo de modificar una celda.

• Señalar: para indicar el modo selección de la celda en una fórmula.

1.2.6 Vistas del libro

Para trabajar con las hojas de Excel y ver mejor los datos, es posible que se necesiten diversas vistas. Las vistas de libro (Normal, Diseño de página y Vista previa de salto de página) se encuentran en la esquina inferior derecha de la ventana de Excel (debajo de la barra de desplazamiento horizontal) y permiten un fácil acceso a las mismas.

También puede elegir un tipo de vista desde la pestaña Vista en el grupo Vistas de libro.

1.2.7 Vista personalizada

Permite guardar una configuración de presentación. Por ejemplo, el ancho de una columna, el alto de una fila, filas y columnas ocultas, selección de celda, etc., y en la configuración de impresión, por ejemplo, la configuración de la página, los márgenes, encabezados y pie de página y configuración de página, etcétera.

1.2.8 Herramienta Zoom

A la derecha de las vistas del libro se encuentran las herramientas Zoom. A diferencia de las vistas, permiten acercar o alejar la vista del libro actual.

1.2.9 Etiquetas de hojas

Muestran por defecto una o más hojas, los nombres para ellas van desde Hoja1 hasta HojaN. Están limitadas a la memoria disponible (el valor predeterminado es 1 hoja). Se puede cambiar el nombre de las hojas para identificar mejor los diversos datos que se agregan a su archivo de Excel.

1.2.10 Dividir paneles

Excel puede dividir la hoja de cálculo en paneles de manera que pueda revisar información y comparar cuando se encuentra muy distante en fila o columna.

1. Abra el archivo Cartera Cobranza.xlsx.

2. Seleccione la celda I14.

3. Haga clic en la pestaña Vista, en el grupo Ventana haga clic en el comando Dividir.

a. Deshacer la división

1. Haga clic en la pestaña Vista, en el grupo Ventana haga clic en el comando Dividir.

2. Luego, haga clic en el comando Dividir. O haga doble clic en la intersección de las barras horizontal y vertical.

1.2.11 Inmovilizar y movilizar paneles

El comando inmovilizar permite mantener visibles las filas y columnas o mantener solo la fila superior o mantener visible únicamente la columna a medida que se desplaza por él, mientras se trabaja en la hoja de cálculo.

a. Inmovilizar un panel cualquiera

1. Seleccione la celda I14.

2. Haga clic en la pestaña Vista, en el grupo Ventana, haga clic en la flecha del comando Inmovilizar.

3. Luego, haga clic en Inmovilizar paneles.

b. Movilizar panel

1. Haga clic en la pestaña Vista, en el grupo Ventana haga clic en la flecha del comando Inmovilizar.

2. Luego, haga clic en Movilizar paneles.

1.2.12 Mostrar u ocultar títulos y líneas de cuadrícula

a. Líneas de cuadrícula

Las líneas de cuadrícula le permiten distinguir a las celdas o rangos. Tienen un color por defecto y no se puede modificar su grosor, pero sí el color.

Para mostrar las líneas de cuadrícula active la casilla de verificación Líneas de cuadrícula de la siguiente manera:

1. Haga clic en la pestaña Vista.

2. En el grupo Mostrar haga clic en la casilla de verificación Líneas de cuadrícula.

Para ocultar las líneas de cuadrícula, desactive la casilla de verificación Líneas de cuadrícula de la siguiente manera:

1. Haga clic en la pestaña Vista.

2. En el grupo Mostrar haga clic en la casilla de verificación Líneas de cuadrícula.

1.3 Métodos abreviados

La cinta de opciones incluye nuevos métodos abreviados llamados KeyTips. Para que aparezcan, se presiona Alt.

La mayoría de los antiguos métodos abreviados de menú Alt + también siguen funcionando. Sin embargo, se debe conocer de memoria los métodos abreviados completos, pues no hay recordatorios en la pantalla de qué letras presionar. Por ejemplo, si se intenta presionar Alt, y después se presiona una de las teclas de menú antiguas V (Vista), etc., aparecerá un cuadro que indica que se está usando una tecla de acceso de una versión anterior de Microsoft Office, como muestra la siguiente figura. Si se conoce la secuencia de teclas completa, se continúa e inicia el comando.

Si se desconoce la secuencia, se presiona Esc y se usa los distintivos de KeyTips en su lugar.

a. Teclas de método abreviado combinadas con Ctrl

¹

b. Teclas de función