MANUAL DE EXCEL AVANZADO 2013.pdf

Texto completo

(1)EXCEL. 2013. NIVEL AVANZADO.

(2) ÍNDICE SESIÓN 1 ....................................................................................................................... 1 1. Base de datos Conceptos y alcances sobre base de datos .................................................................. 2 2. Funciones de Base de Datos ........................................................................................ 2 BDSUMA ..................................................................................................................... 3 BDMAX ....................................................................................................................... 3 BDMIN ........................................................................................................................ 3 BDPROMEDIO ............................................................................................................. 4 BDCONTAR ................................................................................................................. 4 BDCONTARA ............................................................................................................... 4 BDDESVEST ................................................................................................................. 5 BDVAR ........................................................................................................................ 5 3. Formulas Matriciales ................................................................................................... 7 Sintaxis Formulas Matriciales ...................................................................................... 9 4. Prácticas Dirigidas Sesión 1 ......................................................................................... 10 5. Prácticas Propuestas Sesión 1 ..................................................................................... 16. SESIÓN 2 ...................................................................................................................... 22 1. Tablas Dinámicas ....................................................................................................... 23 Origen de datos de la Tabla Dinámica ........................................................................ 23 Filtro Dinámico – Informes de Tabla Dinámica ........................................................... 24 Crear un Informe de Tabal Dinámica .......................................................................... 25 Crear y cambiar el diseño de los campos .................................................................... 26 Agregar campos ......................................................................................................... 28 Organizar los campos ................................................................................................. 29 Crear, editar o eliminar una fórmula de tabla o gráfico dinámico ............................... 30 Agregar un elemento calculado a un campo .............................................................. 31 Mostrar una lista de fórmulas .................................................................................... 32 Editar una Fórmula .................................................................................................... 33 Editar una Fórmula de campo calculado .................................................................... 33 Modificar una única fórmula para un elemento calculado ......................................... 33 Eliminar Fórmula ........................................................................................................ 34 Eliminar un campo calculado ..................................................................................... 34 Eliminar un elemento calculado ................................................................................. 34 2. Informes de gráficos dinámicos ................................................................................. 34 Pasos para crear un gráfico dinámico ......................................................................... 35 Modificar las características del gráfico respecto a su presentación ........................... 39 3. Prácticas Dirigidas Sesión 2 ........................................................................................ 40 4. Prácticas Propuestas Sesión 2 .................................................................................... 45. SESIÓN 3 ...................................................................................................................... 47 1.. Funciones Financieras .............................................................................................. 48 La aplicación de Funciones Financieras ..................................................................... 50 Función Valor Futuro ................................................................................................ 51 Función PAGO .......................................................................................................... 51 Función PAGOINT ..................................................................................................... 52.

(3) Función PAGOPRIM .................................................................................................. 52 Función NPER ........................................................................................................... 53 Función TASA ........................................................................................................... 53 Prácticas Dirigidas Sesión 3 ...................................................................................... 54 Prácticas Propuestas Sesión 3 ................................................................................... 58. SESIÓN 4 ...................................................................................................................... 61 Herramientas de datos ............................................................................................... 62 Auditoria de Fórmulas ................................................................................................ 64 Buscar Objetivo .......................................................................................................... 66 Tabla de datos ............................................................................................................ 67 Escenarios .................................................................................................................. 70 Instalando Solver ....................................................................................................... 73 Defina y solucione un Problema ................................................................................. 74 Prácticas Dirigidas Sesión 4 ........................................................................................ 80 Prácticas Propuestas Sesión 4 .................................................................................... 83. SESIÓN 5 ...................................................................................................................... 86 Formularios ................................................................................................................ 87 Pasos para activar los controles de formulario ........................................................... 88 Insertar Control de Formulario ................................................................................... 89 Prácticas Dirigidas Sesión 5 ......................................................................................... 98 Prácticas Propuestas Sesión 5 .................................................................................. 104. SESIÓN 6 .................................................................................................................... 107 Conceptos y descripción para su aplicación .............................................................. 108 Grabar macros ......................................................................................................... 109 Tipos de macros ....................................................................................................... 109 Aplicación en Excel de macros Funcionales .............................................................. 110 Manipulación de Macros .......................................................................................... 113 Prácticas Dirigidas Sesión 6 ...................................................................................... 115 Prácticas Propuestas Sesión 6 .................................................................................. 118. SESIÓN 7 .................................................................................................................... 120 Exámenes Finales .................................................................................................... 121.

(4) Excel 2013 – Nivel Avanzado. INFO UNI. Sesión 1 1. BASE DE DATOS Conceptos y alcances sobre Base de Datos 2. FUNCIONES DE BASE DE DATOS      . BDSUMA BDMAX BDMIN BDPROMEDIO BDCUENTA BDCONTARA. 3. FORMULAS MATRICIALES . CREACION DE FORMULAS MATRICIALES CON CONDICIONES MULTIPLES HACIENDO USO DE LAS FUNCIONES BASICAS SUMA, MAX, MIN Y PROMEDIO. Universidad Nacional de Ingeniería. Pág. N° 1.

(5) Excel 2013 – Nivel Avanzado. INFO UNI. 1.- BASE DE DATOS: CONCEPTOS Y ALCANCES Una base de datos o banco de datos es un conjunto de datos pertenecientes a un mismo contexto y almacenados sistemáticamente para su posterior uso. En este sentido, una biblioteca puede considerarse una base de datos compuesta en su mayoría por documentos y textos impresos en papel e indexados para su consulta. Actualmente, y debido al desarrollo tecnológico de campos como la informática y la electrónica, la mayoría de las bases de datos están en formato digital (electrónico), y por ende se ha desarrollado y se ofrece un amplio rango de soluciones al problema del almacenamiento de datos. Se define una base de datos como una serie de datos organizados y relacionados entre sí, los cuales son recolectados y explotados por los sistemas de información de una empresa o negocio en particular. Tabla en las bases de datos, se refiere al tipo de modelado de datos, donde se guardan los datos recogidos por un programa. Su estructura general se asemeja a la vista general de un programa de hoja de cálculo. Una tabla es utilizada para organizar y presentar información. Las tablas se componen de filas y columnas de celdas que se pueden rellenar con textos y gráficos Las tablas se componen de dos estructuras: Registro: es cada una de las filas en que se divide la tabla. Cada registro contiene datos de los mismos tipos que los demás registros. Ejemplo: en una tabla de nombres y direcciones, cada fila contendrá un nombre y una dirección. Campo: es cada una de las columnas que forman la tabla. Contienen datos de tipo diferente a los de otros campos. En el ejemplo anterior, un campo contendrá un tipo de datos único, como una dirección, o un número de teléfono, un nombre, etc.. 2.- FUNCIONES DE BASE DE DATOS Las funciones de bases de datos sirven para realizar cálculos en una hoja de cálculos Excel. Es posible realizar operaciones para obtener el total, promedio, mínimo o máximo, además de contar o sumar el contenido del campo (columna o fila) de una base de datos en particular, esto es siempre que y cuando los criterios de verificación se cumplan. Las funciones de Base de Datos (Database Functions, DBase Functions). Estas funciones permiten analizar y realizar cálculos en base a listas. En Excel "lista" es una tabla de datos que en su primera fila tiene encabezamientos. Cada una de estas funciones, denominadas colectivamente funciones BD. Se utiliza tres argumentos: 1.- base_de_datos 2.- nombre_de_campo 3.- criterios Estos tres argumentos se refieren a los rangos de la hoja de cálculo empleados en la función para la gestión de una base de datos. El siguiente resumen muestra la lista de cuáles son las funciones de Base de Datos predefinidas en Excel. BDPROMEDIO BDEXTRAER BDPRODUCTO BDSUMA. Universidad Nacional de Ingeniería. BDCONTAR BDMAX BDDESVEST BDVAR. BDCONTARA BDMIN BDDESVESTP BDVARP. Pág. N° 2.

(6) Excel 2013 – Nivel Avanzado. INFO UNI. 01.- FUNCIÓN BDSUMA Esta función suma los números en el campo (columna) de los registros que coinciden con las condiciones especificadas. Base de Datos es el rango de celdas que compone la lista o base de datos. Una base de datos es una lista de datos relacionados.. 02.- FUNCIÓN BDMAX Esta función devuelve el número máximo del campo (columna) de registros que coinciden con las condiciones especificadas.. 03.- FUNCIÓN BDMIN Esta función devuelve el número menor del campo (columna) de registros que coinciden con las condiciones especificadas.. Universidad Nacional de Ingeniería. Pág. N° 3.

(7) Excel 2013 – Nivel Avanzado. INFO UNI. 04.- FUNCIÓN BDPROMEDIO Esta función permite obtener el promedio de los valores de una columna, lista o base de datos que cumplen las condiciones especificadas.. 05.- FUNCIÓN BDCONTAR La función BDCONTAR, permite el conteo de celdas que contienen números en el campo (columna) de registros de la base de datos que cumplen las condiciones especificadas.. 06.- FUNCIÓN BDCONTARA La función BDCONTARA, permite el conteo de celdas que no están en blanco (columna) de los registros de la base de datos que cumplen las condiciones especificadas.. Universidad Nacional de Ingeniería. Pág. N° 4.

(8) Excel 2013 – Nivel Avanzado. INFO UNI. 07.- FUNCIÓN BDDESVEST La función BDDESVEST, calcula la desviación (extravió, virada) estándar basándose en una muestra de las entradas seleccionadas de una base de datos.. 08.- FUNCIÓN BDVAR La función BDVAR, calcula la varianza basándose en una muestra de las entradas seleccionadas de una base de datos.. Pasos para su aplicación: En la siguiente imagen se muestra una base de datos de un huerto. Cada registro contiene información acerca de un árbol. El rango A5:E11 se denomina Base de Datos y el rango A1:F3 Criterios. Universidad Nacional de Ingeniería. Pág. N° 5.

(9) Excel 2013 – Nivel Avanzado. INFO UNI. Función BDCONTAR Esta función examina los registros de los manzanos cuyo alto varía entre 10 y 16 metros y determina cuántos campos Edad de esos registros contienen números.. =BDCONTAR(A5:E11,"Edad",A1:F2). Función BDCONTARA Esta función examina los registros de los manzanos cuyo alto varía entre 10 y 16 metros y determina el número de campos Ganancia de esos registros que no están en blanco.. =BDCONTARA(A5:E11,"Ganancia",A1:F2). Función BDMAX Esta función permite maximizar un determinado rango de valores. Para el siguiente ejemplo obtener la ganancia máxima de manzanos y perales.. =BDMAX(A5:E11,"Ganancia",A1:A3). Función BDMIN Esta función permite minimizar un determinado rango de valores. Para el siguiente ejemplo obtener la ganancia mínima de manzanos con un alto superior a 10 metros.. =BDMIN(A5:E11,"Ganancia",A1:B2). Función BDSUMA Esta función permite la sumatoria de valores por un determinado criterio de la tabla. Ejemplo 1: La ganancia total de los Manzanos.. =BDSUMA(A5:E11,"Ganancia",A1:A2). Ejemplo 2: La ganancia total del Manzano y Peral.. =BDSUMA(A5:E11,"Ganancia",A1:A3). Ejemplo 3: La ganancia total de los Manzanos con un alto entre 10 y 16.. =BDSUMA(A5:E11,"Ganancia",A1:F2). Universidad Nacional de Ingeniería. Pág. N° 6.

(10) Excel 2013 – Nivel Avanzado. INFO UNI. Función BDPROMEDIO Esta función permite obtener el promedio de valores por un determinado criterio de la tabla. Ejemplo: La edad media de todos los árboles en la base de datos.. =BDPROMEDIO(A5:E11,3,A5:E11). Función BDDESVEST Es la desviación estándar estimada de la base de datos, de una muestra de la población. Ejemplo: Obtener la desviación estándar estimada en el rendimiento de manzanos y perales de la población total del huerto.. =BDDESVEST(A5:E11,"Rendimiento",A1:A3). Función BDVAR Es la varianza estimada de la base de datos, de una muestra de la población. Ejemplo: Obtener la varianza estimada en el rendimiento de manzanos y perales de la población total del huerto.. =BDVAR(A5:E11,"Rendimiento",A1:A3). FORMULAS MATRICIALES FORMULAS MATRICIALES (ARRAY) Las funciones matriciales en Excel permiten efectuar cálculos muy interesantes y proporcionan a la hoja de cálculo una enorme potencia. Las funciones matriciales, en general, abarcan varias celdas, aunque en algunos casos únicamente se centran en una única celda. Las fórmulas matriciales se identifican ya que se encuentran entre corchetes { } El concepto de Matriz viene de los lenguajes de programación y de la necesidad de trabajar con varios elementos de forma rápida y cómoda. Podríamos decir que una matriz es una serie de elementos formando filas (matriz bi-dimensional) o filas y columnas (matriz tri-dimensional). La siguiente tabla representa una matriz bidimensional:. Universidad Nacional de Ingeniería. Pág. N° 7.

(11) Excel 2013 – Nivel Avanzado. INFO UNI. La siguiente tabla representa una matriz tridimensional:. Observar por ejemplo el nombre del elemento 3,4 que significa que está en la posición de fila 3, columna 4. En Excel, podemos tener un grupo de celdas en forma de matriz y aplicar una fórmula determinada en ellas de forma que tendremos un ahorro del tiempo de escritura de fórmulas. En Excel, las fórmulas que hacen referencia a matrices se encierran entre corchetes { }. Hay que tener en cuenta al trabajar con matrices lo siguiente:  No se puede cambiar el contenido de las celdas que componen la matriz  No se puede eliminar o mover celdas que componen la matriz  No se puede insertar nuevas celdas en el rango que compone la matriz. La potencia de las fórmulas matriciales es que nos permiten analizar matrices de datos aplicando filtros complejos por columnas o filas. La fórmula más simple y parecida es el “SUMAR.SI” que actúa como una fórmula matricial con una sola condición. Una típica fórmula matricial es aplicar una serie de condiciones para obtener una suma final, pero se puede hacer con otras funciones como “PROMEDIO”, “MEDIA”, “MAX” o “MIN”.. CONSTANTES MATRICIALES Al igual que en las fórmulas normales podemos incluir referencias a datos fijos o constantes, en las fórmulas matriciales también podemos incluir datos constantes. A estos datos se les llama constantes matriciales y se debe incluir un separador de columnas (símbolo ;) y un separador de filas (símbolo \).. Para introducir una fórmula matricial: 1. Seleccionar el rango en el que la fórmula matricial dejará sus valores. Si la fórmula matricial no abarca más de una celda simplemente no situaremos en esa celda. 2. Escribir la fórmula propiamente dicha 3. Para validar no se ha de pulsar ENTER (Intro), sino que lo que hemos de pulsar es la combinación de tres teclas: CONTROL + SHIFT + ENTER. ¿Qué es una fórmula matricial y para qué sirve? Explicación: A veces es práctico realizar dos operaciones en una sola celda, por ejemplo, multiplicar varias celdas y luego sumar su resultado. Otras veces es necesario aplicar una serie de transformaciones a un rango de celdas y luego poder sumar su resultado. En ambos casos, las fórmulas matriciales pueden resultar de utilidad.. Universidad Nacional de Ingeniería. Pág. N° 8.

(12) Excel 2013 – Nivel Avanzado. INFO UNI. SINTAXIS DE FORMULAS MATRICIALES Las formulas matriciales nos permiten realizar cálculos en base a criterio. I.- PARA APLICAR FORMULAS MATRICIALES CON UN CRITERIO SINTAXIS =SUMA((RANGO=”Criterio”)*1) Ejemplo Nº 1: Realizar un conteo del personal por género de sexo Femenino Ejemplo: Asignando el nombre SEXO al rango desde B4 hasta B202 =SUMA((SEXO="F")*1) Ejemplo Nº 2: Realizar un conteo del personal por género de sexo Masculino Ejemplo: Asignando el nombre SEXO al rango desde B4 hasta B202 =SUMA((SEXO="M")*1). II.- PARA APLICAR FORMULAS MATRICIALES CON DOBLE O MÁS CRITERIOS SINTAXIS =SUMA((RANGO1=”Criterio1”)*(RANGO2=”Criterio2” ”)*(RANGO3=”Criterio3”)) Ejemplo: Cuantas Mujeres con MÁS DE 30 años, son VIUDAS y con más de 2 HIJOS =SUMA((SEXO="F")*(EDAD>30)*(ESTADO="VIUDO")*(HIJOS>2)). III.- FORMULAS MATRICIALES CON DOBLE O MÁS CRITERIOS CON SUMA SINTAXIS =SUMA((RANGO1=”Crit.1”)*(RANGO2=”Crit.2” ”)* … *(RANGO A SUMAR)). IV.- FORMULAS MATRICIALES CON CRITERIO CON OPERACIÓN DE PROMEDIO SINTAXIS =PROMEDIO(SI(RANGO=”Criterio”,RANGO A PROMEDIAR)). V.- FORMULAS MATRICIALES CON CRITERIO CON OPERACIÓN DE MAXIMIZAR =MAX(SI(RANGO=”Criterio”,RANGO A MAXIMIZAR)). VI.- FORMULAS MATRICIALES CON CRITERIO CON OPERACIÓN DE MINIMIZAR =MIN(SI(RANGO=”Criterio”,RANGO A MINIMIZAR)). Universidad Nacional de Ingeniería. Pág. N° 9.

(13) Excel 2013 – Nivel Avanzado. INFO UNI. PRÁCTICAS DIRIGIDAS NIVEL AVANZADO SESIÓN 1 Universidad Nacional de Ingeniería. Pág. N° 10.

(14) Excel 2013 – Nivel Avanzado. INFO UNI. PRÁCTICA 1- FUNCIONES DE BASE DE DATOS 1.- Digitar la siguiente tabla referente a un Reporte de I Semestre 2013 con un total de 14 registros o fila de datos. Seleccionar toda la tabla y asignarle el nombre REPORTE.. 2.- Digitar, diseñar la siguiente tabla de doble entrada para obtener un Informe sobre los Ingresos del I Semestre 2013, según las interrogantes como: Cantidad de Ingresos, Ingresos Máximos, Ingresos Mínimos, Total de Ingresos y Promedio de Ingresos.. 3.- Con los siguientes planteamientos vamos a completar la Tabla resumen (doble entrada), acerca del Informe sobre los Ingresos del I Semestre 2013 Celda H9 =BDCONTARA(REPORTE,$A$6,H6:H$7) hacer arrastres de mouse hasta la celda K9 Celda H10 =BDMAX(REPORTE,$C$6,H6:H7) hacer arrastres de mouse hasta la celda K10 Celda H11 =BDMIN(REPORTE,$C$6,H6:H7) hacer arrastres de mouse hasta la celda K11 Celda H12 =BDSUMA(REPORTE,$C$6,H6:H7) hacer arrastres de mouse hasta la celda K12 Celda H13 =BDPROMEDIO(REPORTE,$C$6,H6:H7) hacer arrastres de mouse hasta la celda K13. Universidad Nacional de Ingeniería. Pág. N° 11.

(15) Excel 2013 – Nivel Avanzado. INFO UNI. PRÁCTICA 2 - FUNCIONES DE BASE DE DATOS 1.- Digitar la siguiente tabla referente a Ventas del II Trimestre 2013 con un total de 50 registros o fila de datos. Seleccionar toda la tabla y asignarle el nombre DATOS.. En la siguiente imagen se podrá comprobar el último registro de la tabla de origen.. 2.- Digitar la siguiente tabla para obtener un Informe según las interrogantes como N° de Ventas y Venta Máxima, etc., para utilizar las Funciones de base de datos – BD.. 3.- Con los siguientes planteamientos vamos a completar la Tabla acerca del Informe de Ventas según los Vendedores: En la celda H7 para el Nº de Ventas =BDCONTARA(DATOS,$A$4,H4:H5) En la celda H8 para calcular la Venta Máxima =BDMAX(DATOS,$C$4,H4:H5) En la celda H9 para calcular la Venta Mínima =BDMIN(DATOS,$C$4,H4:H5) En la celda H10 para calcular la Venta Total =BDSUMA(DATOS,$C$4,H4:H5) En la celda H11 para calcular Venta Promedio =BDPROMEDIO(DATOS,$C$4,H4:H5) En la celda H12 calcular la comisión del 8% =H11*8% (el 8% del Promedio de ventas). Universidad Nacional de Ingeniería. Pág. N° 12.

(16) Excel 2013 – Nivel Avanzado. INFO UNI. PRÁCTICA 1 - FÓRMULAS MATRICIALES I.- PARA APLICAR FORMULAS MATRICIALES CON UN CRITERIO =SUMA((RANGO=”Criterio”)*1). II.- PARA APLICAR FORMULAS MATRICIALES CON DOBLE O MÁS CRITERIOS =SUMA((RANGO1=”Criterio1”)*(RANGO2=”Criterio2” ”)*(RANGO3=”Criterio3”)). 1.- Si se tiene una serie de importes, podemos calcular la suma total con un reintegro del 23% en una sola celda, se podrá comprobar en la siguiente imagen:. 2.- En la celda C8 digitar la siguiente fórmula =SUMA(C2:C7/1.23). Ejecutar la formula matricial pulsando las teclas CONTROL + SHIFT + ENTER. 3.- En la siguiente imagen se podrá visualizar la tabla completa con Total de Importe. Universidad Nacional de Ingeniería. Pág. N° 13.

(17) Excel 2013 – Nivel Avanzado. INFO UNI. PRÁCTICA 2 - FÓRMULAS MATRICIALES 1.- Digitar la siguiente tabla que contiene información referente al Control de Utilidades del I Semestre del año 2013. Comprobar el contenido en la siguiente imagen.. 2.- Digitar y diseñar la siguiente presentación de datos con el objetivo para calcular la Comisión de Utilidad del 6%, con respecto a los Importes del I Semestre 2013.. Seleccionar el relleno C21 hasta H29. Digitar =C8:H16*B9 Después pulsar las teclas CONTROL + SHIFT + ENTER. Universidad Nacional de Ingeniería. Pág. N° 14.

(18) Excel 2013 – Nivel Avanzado. INFO UNI. Obteniendo la siguiente respuesta respecto al rango de C21 hasta H29.. 3.- Digitar y diseñar la siguiente presentación de datos con el objetivo para calcular las Ventas + Comisión del 6%, con respecto a los Importes del I Semestre 2013.. Para calcular las Ventas + Comisión del 6%, debemos de seleccionar un rango C34:H42 despues de digitar la fórmula =C8:H16+C21:H29 después pulsar las teclas CONTROL + SHIFT + ENTER para obtener las respuesta de Ventas + Comisión del 6%.. Universidad Nacional de Ingeniería. Pág. N° 15.

(19) Excel 2013 – Nivel Avanzado. INFO UNI. PRÁCTICAS PROPUESTAS NIVEL AVANZADO SESIÓN 1 Universidad Nacional de Ingeniería. Pág. N° 16.

(20) Excel 2013 – Nivel Avanzado. INFO UNI. PRÁCTICA PROPUESTA N° 1 - FUNCIONES DE BD 1.- Teniendo como referencia la siguiente tabla conformado por Ventas del 1er Semestre del año 2013 conformada por 55 registros (filas de datos). En la siguiente imagen se podrá comprobar los 8 primeros registros.. En la siguiente imagen se podrá comprobar los 4 últimos registros.. PRACTICA PROPUESTA: Preparar una tabla de doble entrada referente al campo PRODUCTOS para calcular N° de Ventas, Venta Máxima, Venta Mínima, Venta Total y Venta Promedio.. Preparar otras tablas de doble entrada en base a los campos MES, VENDEDORES, DISTRITO para calcular N° de Ventas, Venta Máxima, Venta Mínima, Venta Total y Venta Promedio.. Universidad Nacional de Ingeniería. Pág. N° 17.

(21) Excel 2013 – Nivel Avanzado. INFO UNI. PRÁCTICA PROPUESTA N° 2 - FUNCIONES DE BD. Funciones de Base de Datos - BD, para utilizar la Desviación Estándar En las funciones de Base de Datos - BD en el programa Excel se utiliza las funciones BDDESVEST y BDDESVETP para calcular DESVIACIÓN ESTÁNDAR.. PARTIENDO DEL CONCEPTO: La desviación estándar o también denominado desviación típica es una medida de dispersión para variables de razón (ratio o cociente) y de intervalo, de gran utilidad en la estadística descriptiva. Es una medida (cuadrática) de lo que se apartan los datos de su media, y por tanto, se mide en las mismas unidades que la variable.. Esta medida nos permite determinar el promedio aritmético de fluctuación de los datos respecto a su punto central o media. La desviación estándar nos da como resultado un valor numérico que representa el promedio de diferencia que hay entre los datos y la media. Para calcular la desviación estándar basta con hallar la raíz cuadrada de la varianza.. Su aplicación es muy importante para la administración de información para los informes matemáticos, estadísticos.. PRACTICA PROPUESTA: El profesor del curso deberá de explicar a sus alumnos la aplicación práctica de los conceptos acerca de DESVIACIÓN ESTÁNDAR teniendo como referencia la tabla anterior de la Practica Propuesta 1. Utilizar las funciones BD: BDDESVEST y BDDESVETP, crear los respectivos informes elaborando sus tablas de doble entrada o tablas simples. Si cree necesario, también añadir a su explicación y demostración de cálculos referente a Desviación Estándar los temas de Amplitud, Varianza, Frecuencia absolutas (fi), relativas (hi), acumuladas (Fi), acumuladas relativas (Hi) con sus respectivas tablas y acciones de cálculo.. Universidad Nacional de Ingeniería. Pág. N° 18.

(22) Excel 2013 – Nivel Avanzado. INFO UNI. PRÁCTICA PROPUESTA N° 1 - FÓRMULAS MATRICIALES 1.- Digitar la siguiente tabla referente Informe de Ventas desde el año 2000 hasta 2013 con un total de 250 REGISTROS o fila de datos. Seleccionar toda la tabla y asignarle el nombre REGISTROS.. 2.- En la siguiente imagen se podrá visualizar los últimos registros de la tabla. Consideraciones para realizar distintas operaciones de cálculo utilizando formulas matriciales. a) Cuantos Vendedores de Estado Civil Soltero b) Cuantos Vendedores de género Femenino c) Contabilizar las Ventas del vendedor Rubio Ramírez Verónica d) Cuantos Vendedores de Estado Civil Casado y de género Femenino e) Cuantos Vendedores de Estado Civil Casado y de género Masculino f). Cuantos Vendedores de Estado Civil Casado y de género Femenino, el cargo Plataforma, con la finalidad de obtener la sumatoria de Ventas. g) Calcular el acumulado de Ventas de los Vendedores de género Femenino h) Calcular el acumulado de Ventas de los Vendedores de género Masculino i). Calcular el acumulado de Ventas y Bonificación de los Vendedores de género Femenino. j). Calcular el acumulado de Ventas y Bonificación de los Vendedores de género Femenino, que tengan el cargo Online, estado civil Soltero. k) Hallar el promedio de las Ventas de los Vendedores de género Masculino l). Hallar la mínima edad de los vendedores que tengan el cargo de Promotor. Universidad Nacional de Ingeniería. Pág. N° 19.

(23) Excel 2013 – Nivel Avanzado. INFO UNI. PRÁCTICA PROPUESTA N° 2 - FÓRMULAS MATRICIALES De la siguiente tabla que contiene 375 registros. Continuación de la tabla.. Preguntas para la Practica utilizando la herramienta Formulas matriciales. 1.- Calcular los Clientes según tipo de AFP. 2.- Calcular los Clientes para cada Zona. Universidad Nacional de Ingeniería. Pág. N° 20.

(24) Excel 2013 – Nivel Avanzado. INFO UNI. 3.-Calcular los Clientes para cada Semana. 4.- Calcular los Clientes de AFP Integra en la zona A. 5.- Calcular los Clientes de AFP BBVA Horizonte del 19 al 25 de Feb en la zona C. 6.- Calcular los Clientes de la AFP HABITAT del mes de Febrero en la zona B. 7.- Calcular los Clientes de AFPs PRIMA e INTEGRA en el mes de Febrero en la zona A. 8.- Calcular la máxima captación de Afiliados entre las AFPs BBVA Horizonte, Profuturo y Prima en el mes de Enero 2013 9.- Calcular el Promedio de Captaciones en las zonas B o C de la AFP Profuturo. 10) Calcular la menor captación realizadas por las AFP Profuturo y Horizonte en la zona A. 11.- Contar la cantidad de días en que se captaron Clientes por las AFP’s BBVA Horizonte e Integra en el mes de Febrero.. 12.- Calcular la menor captación realizadas por las AFP Profuturo y BBVA Horizonte en la zona A. SUGERENCIA.- Plantear algunas respuestas matriciales aplicadas hasta la pregunta 12 utilizando otras funciones, para comprobar los cálculos anteriores.. Universidad Nacional de Ingeniería. Pág. N° 21.

(25) Excel 2013 – Nivel Avanzado. INFO UNI. Sesión 2 1. INFORMES DE TABLAS DINAMICAS      . Operaciones más comunes(SUMA,MAX,MIN,PROMEDIO,CUENTA) Filtros / porcentajes Ordenar Agrupara /Desagrupar Campo calculado Elemento calculado. 2. INFORMES DE GRÁFICOS DINÁMICOS  Pasos para crear Gráfico  Pasos para optimizar el gráfico: filtrar, asignar cantidades, cambiar de tipo de gráfico, etc.. Universidad Nacional de Ingeniería. Pág. N° 22.

(26) Excel 2013 – Nivel Avanzado. INFO UNI. TABLAS DINÁMICAS Una tabla dinámica consiste en la administración de un conjunto de datos, para generar un resumen, atendiendo a varios criterios de agrupación, representado como una tabla de doble entrada que nos facilita la interpretación de dichos datos. Es dinámica porque nos permite ir obteniendo diferentes totales, filtrando datos, cambiando la presentación de los datos, visualizando o no los datos origen, etc. En conclusión una tabla dinámica combina y compara en forma rápida grandes volúmenes de datos. Permitiendo el análisis multidimensional de los datos al girar las filas y las columnas creando diferentes formas de visualizar reportes con los datos de origen. Escogiendo desde lo general a lo específico. Las tablas dinámicas solo sirven para resumir los datos según la consulta realizada, pero no permiten modelar dentro de la tabla. La opción posible sería tomar los datos de la Tabla Dinámica Excel con la función IMPORTAR DATOS DINAMICOS e incorporarlos a nuestro modelo Excel. Las tablas dinámicas tienen alguna semejanza cuando realizamos consulta de referencias cruzadas, pero con más interactividad. Las tablas dinámicas, también llamadas pivot tables, son herramientas para el análisis de bases de datos (BD). Se encargan de resumir y ordenar la información contenida en la BD. Permiten analizar sólo una porción de la BD, es decir, con una BD con gran cantidad de campos o columnas, ayudan a visualizar únicamente la información relevante, con lo que el análisis se torna más sencillo. Las pivot tables están basadas en dos conceptos: sumarización y rotación. Sumarización: Se refiere a la capacidad de resumir datos del mismo tipo y con los mismos atributos. Rotación: Es la posibilidad de colocar, quitar y/o mover la cantidad de campos seleccionados a cualquier posición, enfoque, situación que se requiera, siempre y cuando estos campos estén contenidos en la base de datos.. Origen de Datos de la Tabla Dinámica Para crear una Tabla Dinámica es necesario contar con información organizada en una hoja de Excel. Esta información debe estar clasificada. Por ejemplo, imagina que tenemos una serie de datos de clientes junto con su dirección. Así, pudiéramos tener los datos ordenados y clasificados en la siguiente manera: Código de Cliente. Nombre de Cliente. Distrito Código Postal Cargo Sueldo …y etc. (mas campos). Teniendo clasificada la información de esta forma, se pudieran hacer consultas para localizar cierto cliente o cierta dirección, ya sea usando la función de Búsqueda o haciendo uso de Filtros.. Universidad Nacional de Ingeniería. Pág. N° 23.

(27) Excel 2013 – Nivel Avanzado. INFO UNI. Sin embargo, para contabilizar los datos de manera rápida y clasificada, se requiere el uso de la Tabla Dinámica. Por ejemplo: Cuántos y cuáles son los clientes que existen por Códigos Postales. Cuántos y cuáles son los clientes que existen por Cargo. Cuántos y cuáles son los clientes que existen por Distrito. … y así por el estilo. O combinaciones de estos (Creación de matriz de datos): No importa la cantidad de información que se desee analizar, Excel tiene la capacidad de hacer un informe de datos inclusive si los datos ocupan toda la capacidad de espacio de una hoja de Excel. Su única limitante, será la capacidad del procesador y memoria de nuestra computadora.. FILTRO DINÁMICO - INFORMES DE TABLA DINÁMICA Un informe de tabla dinámica es una forma interactiva de resumir rápidamente grandes volúmenes de datos. Utilice un informe de tabla dinámica para analizar datos numéricos en profundidad y para realizar diferentes tipos de proyecciones sobre los datos. Un informe de tabla dinámica está especialmente diseñado para:      . Consultar grandes cantidades de datos de muchas maneras diferentes y cómodas para el usuario. Calcular el subtotal y agregar datos numéricos, resumir datos por categorías y subcategorías, y crear cálculos y fórmulas personalizados. Expandir y contraer niveles de datos para destacar los resultados y desplazarse hacia abajo para ver los detalles de los datos de resumen de las áreas de interés. Desplazar filas a columnas y columnas a filas para visualizar los resúmenes diferentes de los datos de origen. Filtrar, ordenar, agrupar y dar formato condicional a los subconjuntos de datos más útiles e interesantes para poder centrarse en la información que le interesa. Presentar informes electrónicos o impresos concisos, atractivos y con comentarios.. A menudo utilizamos un informe de tabla dinámica cuando deseamos comparar totales relacionados, sobre todo si tenemos una lista larga de números para sumar y deseamos realizar comparaciones distintas con cada número. En el informe de tabla dinámica mostrado a continuación, podemos ver fácilmente cómo se comparan la cantidad de vehículos de la semana 2 con las cantidades de cualquier otro vehículo o semana, o con las cantidades totales. 1 2. 3 4. Datos de origen, en este caso de una hoja de calculo Los valores de origen del resumen de Cantidad por vehiculo en el informe de tabla dinámica El informe de tabla dinámica entero Resumen de los valores de origen. Universidad Nacional de Ingeniería. Pág. N° 24.

(28) Excel 2013 – Nivel Avanzado. INFO UNI. En los informes de tabla dinámica, cada columna o campo de los datos de origen se convierte en un campo de tabla dinámica que resume varias filas de información. En el ejemplo anterior, la columna Semana se convierte en el campo Semana y cada registro de Auto se resume en un solo elemento Auto. Un campo de valores, como Suma de Cantidad, proporciona los valores que van a resumirse. De forma predeterminada, los datos del área Valores resumen los datos de origen subyacentes en el informe de gráfico dinámico de la siguiente forma: los valores numéricos utilizan la función SUMA, y los valores de texto la función CONTAR.. CREAR UN INFORME DE TABLA DINÁMICA Para crear un informe de tabla o gráfico dinámico, necesita conectar con un origen de datos y especificar la ubicación del informe. 1. Seleccione una celda de un rango de celdas o coloque el punto de inserción dentro de una tabla de Microsoft Office Excel. Asegúrese de que el rango de celdas tiene encabezados de columna. 2. Siga uno de los procedimientos siguientes:  . Para crear un informe de tabla dinámica, en la ficha Insertar, en el grupo Tablas, haga clic en Tabla dinámica y, a continuación, en Tabla dinámica. Aparecerá el cuadro de diálogo Crear tabla dinámica. Para crear un informe de tabla dinámica o gráfico dinámico, en la ficha Insertar, en el grupo Tablas, haga clic en Tabla dinámica y, a continuación, en Gráfico dinámico.. Aparecerá el cuadro de diálogo Crear tabla dinámica. 3. Seleccione un origen de datos. Siga uno de los procedimientos siguientes: Seleccione la tabla que desea analizar a. Selección de Mouse (clic) en Seleccione una tabla o rango. b. Escriba el rango de celdas o la referencia del nombre de tabla, en el cuadro Tabla o rango.. Universidad Nacional de Ingeniería. Pág. N° 25.

(29) Excel 2013 – Nivel Avanzado. INFO UNI. Si seleccionó una celda de un rango de celdas o si el punto de inserción estaba en una tabla antes de iniciar el asistente, el rango de celdas o la referencia del nombre de tabla se muestra en el cuadro Tabla o rango. Opcionalmente, para seleccionar un rango de celdas o una tabla, haga clic en Contraer cuadro de diálogo. para ocultar temporalmente el cuadro de diálogo, seleccione el rango en la hoja. de cálculo y, a continuación, presione Expandir diálogo. .. NOTA.- Si el rango se encuentra en otra hoja de cálculo (área de trabajo) del mismo libro o de otro libro, escriba el nombre del libro y de la hoja de cálculo utilizando la siguiente sintaxis: ([nombredellibro]nombredelahoja!rango).. CREAR Y CAMBIAR EL DISEÑO DE LOS CAMPOS EN UN INFORME DE TABLA DINÁMICA O GRÁFICO DINÁMICO Después de crear un informe de tabla dinámica o gráfico dinámico, utilice la lista de campos de tabla dinámica para agregar campos. Si desea cambiar un informe de tabla dinámica o gráfico dinámico, utilice la lista de campos para ordenar y quitar campos. De manera predeterminada, la lista de campos de tabla dinámica contiene dos secciones: una sección de campos en la parte superior para agregar o quitar campos, y una sección de diseño en la parte inferior para volver a organizar campos y ajustar su posición. Puede acoplar la lista de campos de tabla dinámica en algún lado de la ventana y cambiarle el tamaño horizontalmente. También puede desacoplarla, en cuyo caso podrá cambiarle el tamaño tanto vertical como horizontalmente. NOTAS:  Si no aparece la lista de campos de tabla dinámica, asegúrese de hacer clic en el informe de tabla dinámica o gráfico dinámico. Si aun así no aparece, en un informe de tabla dinámica, en la ficha Opciones, en el grupo Mostrar u ocultar, haga clic en Lista de campos, y en un informe de gráfico dinámico, en la ficha Analizar, en el grupo Datos, haga clic en Lista de campos. . Si no aparecen los campos que desea utilizar en la lista de campos, actualice el informe de tabla dinámica o gráfico dinámico para que se muestren los nuevos campos, campos calculados, medidas, medidas calculados o dimensiones que haya agregado desde la última operación.. Universidad Nacional de Ingeniería. Pág. N° 26.

(30) Excel 2013 – Nivel Avanzado. INFO UNI. Lista de campos de tabla dinámica Es importante comprender el modo en que funciona la lista de campos de tabla dinámica y el modo en que puede organizar diferentes tipos de campos para obtener los resultados deseados al diseñar un informe de tabla dinámica o gráfico dinámico.. Cómo funciona la lista de campos de tabla dinámica. 1 2 3 4. 5. Un origen de datos externo contiene datos estructurados organizados como uno o varios campos (denominados también columnas) que se muestran en la lista de campos. Mueva un campo al área de filtro de informe en la lista de campos, lo que a su vez moverá el campo al área de filtro de informe en el informe de tabla dinámica. Mueva un campo al área de etiqueta de columna en la lista de campos, lo que a su vez moverá el campo al área de etiqueta de columna en el informe de tabla dinámica. Mueva un campo al área de etiqueta de fila en la lista de campos, lo que a su vez moverá el campo al área de fila de columna en el informe de tabla dinámica. Mueva un campo al área de valores en la lista de campos, lo que a su vez moverá el campo al área de valores en el informe de tabla dinámica.. Universidad Nacional de Ingeniería. Pág. N° 27.

(31) Excel 2013 – Nivel Avanzado. INFO UNI. AGREGAR CAMPOS Para agregar campos al informe, siga uno o varios de estos procedimientos: o. o. Active la casilla de verificación situada junto a cada nombre de campo en la sección de campos. El campo se coloca en el área predeterminada de la sección de diseño, pero puede organizar los campos si lo desea. De forma predeterminada, los campos no numéricos se agregan al área de etiquetas de fila, los campos numéricos se agregan al área de valores y las jerarquías OLAP de fecha y hora se agregan al área de etiquetas de columna. Selección de mouse (clic) con el botón secundario del mouse (ratón) y seleccione el comando correspondiente, Agregar a filtro de informe, Agregar a etiqueta de columna, Agregar a etiqueta de fila y Agregar a valores, para colocar el campo en un área específica de la sección de diseño. SUGERENCIA También puede hacer clic y mantener presionado el botón del mouse en un nombre de campo y después arrastrar el campo entre la sección de campos y un área de la sección de diseño. Para agregar un campo varias veces, repita la operación.. Universidad Nacional de Ingeniería. Pág. N° 28.

(32) Excel 2013 – Nivel Avanzado. INFO UNI. ORGANIZAR LOS CAMPOS Puede cambiar el orden o la posición de los campos existentes utilizando alguna de las cuatro áreas situadas en la parte inferior de la sección de diseño: Informe de tabla dinámica. Descripción. Valores. Se utilizan para mostrar datos numéricos de resumen.. Etiquetas de fila. Se utilizan para mostrar campos como filas en el lado del informe. Una fila en una posición inferior se anida con otra fila que está justo encima de ella.. Etiquetas de columna. Se utilizan para mostrar campos como columnas en la parte superior del informe. Una columna en una posición inferior se anida con otra columna que está justo encima de ella.. Filtro de informe. Se utiliza para filtrar todo el informe en función del elemento seleccionado en el filtro de informe.. Gráfico dinámico. Descripción. Valores. Se utilizan para mostrar datos numéricos de resumen.. Campo de eje (categorías). Se utiliza para mostrar los campos como un eje en el gráfico.. Etiquetas de campos de leyenda (series). Se utilizan para mostrar los campos en la leyenda del gráfico.. Filtro de informe. Se utiliza para filtrar todo el informe en función del elemento seleccionado en el filtro de informe.. Funciones de resumen disponibles en informes de tabla dinámica Las funciones de resumen se utilizan en subtotales automáticos y tablas dinámicas (informe de tabla dinámica: informe de Excel interactivo de tablas cruzadas que resume y analiza datos, como registros de una base de datos, de varios orígenes, incluidos los que son externos a Excel.). En las tablas dinámicas, están disponibles las siguientes funciones de resumen para todos los tipos de datos de origen salvo para OLAP (Procesamiento analítico en línea, Online Analytical Processing) (OLAP: tecnología de base de datos que se ha optimizado para la elaboración de consultas e informes, en lugar de procesar transacciones. Los datos OLAP se organizan jerárquicamente y se almacenan en cubos en lugar de tablas). Función. Resumen. Suma. La suma de los valores. Esta es la función predeterminada para datos numéricos.. Contar. El número de los valores de datos. La función de resumen Contar funciona del mismo modo que la función de la hoja de cálculo CONTARA. Contar es la función predeterminada para datos no numéricos.. Promedio Máx. El promedio de los valores. El valor máximo.. Universidad Nacional de Ingeniería. Pág. N° 29.

(33) Excel 2013 – Nivel Avanzado. Función Mín. INFO UNI. Resumen El valor mínimo.. Producto. El producto de los valores.. Contar números. El número de valores de datos que son números. La función de resumen Contar núm funciona del mismo modo que la función de la hoja de cálculo CONTAR.. DesvEst. Un cálculo de la desviación estándar de una población, donde la muestra es un subconjunto de toda la población.. DesvEstP. Un cálculo de la desviación estándar de una población, donde la población son todos los datos que van a resumirse.. Var. Un cálculo de la varianza de una población, donde la muestra es un subconjunto de toda la población.. Varp. La varianza de una población, donde la población son todos los datos que van a resumirse.. CREAR, EDITAR O ELIMINAR UNA FÓRMULA DE TABLA DINÁMICA O DE GRÁFICO DINÁMICO Si las funciones de resumen y los cálculos personalizados no proporcionan los resultados que desea, puede crear sus propias fórmulas en campos calculados y elementos calculados. NOTAS.- No puede crear fórmulas en un informe de tabla dinámica o de gráfico dinámico conectado a una fuente de datos OLAP. Para obtener resultados óptimos en un informe de gráfico dinámico, trabaje en el informe de tabla dinámica asociado, donde podrá ver los valores de datos individuales que calcula la fórmula.. CREAR UNA FÓRMULA 1. Determine si desea un campo calculado o un elemento calculado dentro de un campo.  Utilice un campo calculado si piensa utilizar los datos de otro campo en la fórmula.  Utilice un elemento calculado si desea que la fórmula utilice datos de uno o más elementos específicos dentro de un campo. 2. Realice uno de los procedimientos siguientes: Agregar un campo calculado a. Haga clic en el informe de tabla dinámica. b. En la ficha ANÁLISIS, en el grupo CÁLCULOS / Campos, elementos y conjuntos / Campo calculado. c. En el cuadro Nombre, escriba un nombre para el campo. d. En el cuadro Fórmula, escriba la fórmula para el campo.. Universidad Nacional de Ingeniería. Pág. N° 30.

(34) Excel 2013 – Nivel Avanzado. INFO UNI. Para utilizar los datos de otro campo en la fórmula, selección de mouse (clic) en el campo en el cuadro Campos y, a continuación, haga clic en Insertar campo. Por ejemplo, para calcular una proyección para el mes de Octubre del 15% para cada valor del campo Cantidad, podría escribir =Cantidad * 1.15. /// Selección de Mouse (clic) en Aceptar.. AGREGAR UN ELEMENTO CALCULADO A UN CAMPO a. Si los elementos del campo están agrupados, en la ficha ANÁLISIS, en el grupo AGRUPAR, haga clic en Desagrupar. b. Haga clic en el campo donde desee agregar el elemento calculado. c. En la ficha ANÁLISIS, en el grupo CÁLCULOS / Campos, Elementos y conjuntos Elemento calculado. d. En el cuadro Nombre, escriba un nombre para el elemento calculado. e. En el cuadro Fórmula, escriba la fórmula para el elemento.. Para utilizar los datos de un elemento de la fórmula, haga clic en el elemento en la lista Elementos y, a continuación, en Insertar elemento (el elemento debe ser del mismo campo que el elemento calculado). f. Selección de mouse (clic) en Aceptar. g. Si ha desagrupado elementos en el paso a, vuelva a agruparlos si lo desea. 3. Para los elementos calculados, puede especificar diferentes fórmulas, celda por celda. Siga este procedimiento: a. Haga clic en la celda donde desee cambiar la fórmula. Para cambiar la fórmula en varias celdas, mantenga presionada la tecla CTRL y haga clic en las celdas. b. En la barra de fórmulas, escriba los cambios en la fórmula.. Universidad Nacional de Ingeniería. Pág. N° 31.

(35) Excel 2013 – Nivel Avanzado. INFO UNI. 4. Si tiene varios elementos o fórmulas calculados, ajuste el orden de cálculo haciendo lo siguiente: a. Haga clic en el informe de tabla dinámica. b. En la ficha ANÁLISIS, en el grupo CÁLCULOS haga clic en Campos, Elementos y conjuntos, a continuación, haga clic en Orden de resolución.. c. Selección de mouse (clic) en una fórmula y, posteriormente, haga clic en Subir o en Bajar. d. Continúe hasta que las fórmulas aparezcan en el orden en que desee que se calculen.. MOSTRAR UNA LISTA DE FÓRMULAS Para mostrar una lista de todas las fórmulas utilizadas en el informe de tabla dinámica actual, haga lo siguiente: 1. Haga clic en el informe de tabla dinámica. 2. En la ficha ANÁLISIS, en el grupo CÁLCULOS, selección de mouse (clic) en la opción CAMPOS, ELEMENTOS Y CONJUNTOS, a continuación, haga clic en CREAR LISTA DE FÓRMULAS.. Universidad Nacional de Ingeniería. Pág. N° 32.

(36) Excel 2013 – Nivel Avanzado. INFO UNI. EDITAR UNA FÓRMULA 1. Determine si la fórmula está en un campo calculado o en un elemento calculado. Si la fórmula está dentro de un elemento calculado, determine si se trata de la única fórmula del elemento calculado haciendo lo siguiente: a. Haga clic en el informe de tabla dinámica. b. En la ficha ANÁLISIS, en el grupo CÁLCULOS, haga clic en CAMPOS, ELEMENTOS Y CONJUNTOS y, a continuación, haga clic en Crear lista de fórmulas. c. En la lista de fórmulas, busque la fórmula que desee cambiar bajo Campo calculado o Elemento calculado. Si hay varias fórmulas para un elemento calculado, la fórmula predeterminada escrita al crear el elemento contiene el nombre de elemento calculado en la columna B. Para las demás fórmulas de un elemento calculado, la columna B contiene tanto el nombre del elemento calculado como los nombres de los elementos con los que se intersecta. 2. Siga uno de los procedimientos siguientes:. EDITAR UNA FÓRMULA DE CAMPO CALCULADO a. Selección de mouse (clic) en el informe de tabla dinámica. b. En la ficha ANÁLISIS, en el grupo CÁLCULOS, selección de mouse (clic) en CAMPOS, ELEMENTOS Y CONJUNTOS y, a continuación, haga clic en Campo calculado. c. En el cuadro Nombre, seleccione el campo calculado cuya fórmula desea cambiar. d. En el cuadro Fórmula, modifique la fórmula / Selección de mouse (clic) en Modificar.. MODIFICAR UNA ÚNICA FÓRMULA PARA UN ELEMENTO CALCULADO a. Selección de mouse (clic) en el campo que contenga el elemento calculado. b. En la ficha ANÁLISIS, en el grupo CÁLCULOS haga clic en CAMPOS, ELEMENTOS Y CONJUNTOS y, a continuación, selección de mouse (clic) en Elemento calculado. c. En el cuadro Nombre, seleccione el elemento calculado. d. En el cuadro Fórmula, modifique la fórmula / Selección de mouse (clic) en Modificar.. Universidad Nacional de Ingeniería. Pág. N° 33.

(37) Excel 2013 – Nivel Avanzado. INFO UNI. Editar fórmulas individuales para celdas específicas de un elemento calculado a. Selección de mouse (clic) en la celda donde desee cambiar la fórmula. Para cambiar la fórmula en varias celdas, mantenga presionada la tecla CTRL y selección de mouse (clic) en las celdas. b. En la barra de fórmulas, escriba los cambios en la fórmula. 3. Si tiene varios elementos o fórmulas calculados, ajuste el orden de cálculo haciendo lo siguiente: a. Selección de mouse (clic) en el informe de tabla dinámica. b. En la ficha ANÁLISIS, en el grupo CÁLCULOS haga clic en CAMPOS, ELEMENTOS Y CONJUNTOS y, a continuación, haga selección de mouse (clic) en Orden de resolución. c. Selección de mouse (clic) en una fórmula y, posteriormente, haga clic en Subir o en Bajar. d. Continúe hasta que las fórmulas aparezcan en el orden en que desee que se calculen.. ELIMINAR UNA FÓRMULA SUGERENCIA Si no desea eliminar una fórmula de forma permanente, puede ocultar el campo o elemento. Para ocultar un campo, arrástrelo fuera del informe. 1. Determine si la fórmula está en un campo calculado o en un elemento calculado. Los campos calculados aparecen en la Lista de campos de tabla dinámica. Los elementos calculados aparecen como elementos dentro de otros campos. 2. Siga uno de los procedimientos siguientes:. ELIMINAR UN CAMPO CALCULADO a. Selección de mouse (clic) en el informe de tabla dinámica. b. En la ficha ANÁLISIS, en el grupo CÁLCULOS, haga selección de mouse (clic) en CAMPOS, ELEMENTOS Y CONJUNTOS y, a continuación, Selección de mouse (clic) en Campo calculado. c. En el cuadro Nombre, seleccione el campo que desea eliminar. d. Selección de mouse (clic) en Eliminar.. ELIMINAR UN ELEMENTO CALCULADO a. Selección de mouse (clic) en el campo que contiene elemento que desee eliminar. b. En la ficha ANÁLISIS, en el grupo CÁLCULOS haga selección de mouse (clic) en CAMPOS, ELEMENTOS Y CONJUNTOS y, a continuación, selección de mouse (clic) en Elemento calculado. c. En el cuadro Nombre, seleccione el elemento que desea eliminar. d. Selección de mouse (clic) en Eliminar.. INFORMES DE GRÁFICOS DINÁMICOS Los informes de tabla dinámica son útiles para resumir, analizar, explorar y presentar datos de resumen. Los informes de gráfico dinámico nos ayudan a visualizar los datos de resumen de un informe de tabla dinámica para que pueda detectar fácilmente comparaciones y tendencias. Tanto los informes de tabla dinámica como los informes de gráfico dinámico permiten tomar decisiones bien fundamentadas sobre datos críticos para su empresa. Los gráficos dinámicos se caracterizan por crear un informe con grandes volúmenes de datos que se administran en varios niveles en el eje X.. Universidad Nacional de Ingeniería. Pág. N° 34.

(38) Excel 2013 – Nivel Avanzado. INFO UNI. Un informe de gráfico dinámico proporciona una representación gráfica de los datos de un informe de tabla dinámica, que en este caso se denomina informe de tabla dinámica asociado. Un informe de gráfico dinámico es interactivo, lo que significa que se puede ordenar y filtrar para mostrar subconjuntos de los datos de la tabla dinámica. Cuando se crea un informe de gráfico dinámico, se muestran filtros de informe de gráfico dinámico en el área del grafico para poder ordenar y filtrar los datos subyacentes del informe de gráfico dinámico. Los cambios que realice en el diseño de campo y los datos en el informe de tabla dinámica asociado se reflejarán inmediatamente en el informe de gráfico dinámico. Un informe de gráfico dinámico muestra series de datos, categorías, marcadores de datos y ejes, al igual que el grafico estándar. Puede cambiar asimismo el tipo de gráfico y otras opciones como los títulos, la ubicación de la leyenda, las etiquetas de datos y la ubicación del gráfico.. Pasos para crear un gráfico dinámico: 1.- Digitar la siguiente tabla:. 2.- Seleccionar una celda (la celda B3) de la tabla conformada por el rango A2:E16. Menú INSERTAR / grupo Tablas – TABLAS DINÁMICAS / GRÁFICO DINÁMICO. Estas acciones se podrán comprobar en la siguiente imagen.. Universidad Nacional de Ingeniería. Pág. N° 35.

(39) Excel 2013 – Nivel Avanzado. INFO UNI. 3.- Se activa la siguiente ventana. En la opción Tabla o rango, nos indica el rango que reconoce la tabla A2:E16 Además se habilita la opción Nueva hoja de cálculo. Escoger la opción Aceptar. Se activa la siguiente ventana: Conformada en Tabla, gráfico y el Panel (para administrar información con el objetivo de obtener la tabla dinámica).. Desde el área del Panel: Escoger los campos Empresa, Región (como campos de contenido literal – criterio), después escoger un campo de contenido numérico. Comprobar en la siguiente imagen.. Universidad Nacional de Ingeniería. Pág. N° 36.

(40) Excel 2013 – Nivel Avanzado. INFO UNI. 4.- Ahora procederemos a filtrar el campo literal criterio EMPRESA, escoger los criterios Data Print, Diseños Arenas y Tools Studio.. Del campo literal criterio REGIÓN, escoger los criterios Arequipa, Chiclayo, Lima. Se podrá comprobar en la siguiente imagen.. Seleccionar cualquier área del grafico pulsar el botón derecho de mouse, escoger la opción Agregar etiqueta de Datos. Estas acciones se podrán comprobar en la siguiente imagen.. Universidad Nacional de Ingeniería. Pág. N° 37.

(41) Excel 2013 – Nivel Avanzado. INFO UNI. Después de filtrar los campos Empresa y Región Se obtiene el siguiente gráfico como en la siguiente imagen.. Pulsar el botón derecho de mouse en un espacio vacío y escoger la opción Cambiar tipo gráfico. Ejemplos de tipos de gráficos:. Universidad Nacional de Ingeniería. Pág. N° 38.

(42) Excel 2013 – Nivel Avanzado. INFO UNI. MODIFICAR LAS CARACTERÍSTICAS DEL GRÁFICO RESPECTO A SU PRESENTACIÓN En la pestaña DISEÑO podrás encontrar todas las opciones relativas al aspecto del gráfico. Por ejemplo, podrás decidir que ejes mostrar o si quieres incluir una cuadrícula de fondo para poder leer mejor los resultados. Todo esto lo encontraras en la sección Ejes:. 1.- DISEÑOS DE GRÁFICO: Está conformado por 2 opciones: 1.1. Agregar elemento de gráfico: Está conformado por Ejes, Títulos de ejes, Titulo del gráfico, Etiquetas de datos, Tabla de datos, Barra de error, Líneas de la cuadricula, Leyenda, Líneas, Línea de Tendencia.. 1.2 Diseño Rápido: Está conformado por 11 diseños. 2.- ESTILOS DE DISEÑO: Cambia el estilo visual general del gráfico. Está conformado por 14 estilos.. 3.- DATOS: Está conformado por 2 opciones 3.1. Cambiar entre filas y columnas: Intercambia los datos del eje. Los datos que se han colocado en el eje X se moverán al eje Y, viceversa. 3.2. Seleccionar datos: Cambia el rango de datos incluidos en el gráfico.. 4.- TIPO: Está conformado por la opción Cambia a un tipo diferente de gráfico.. 5.- UBICACIÓN: Está conformado por la opción Mover Gráfico, mueve este grafico a otra hoja o pestaña del libro.. Universidad Nacional de Ingeniería. Pág. N° 39.

(43) Excel 2013 – Nivel Avanzado. INFO UNI. PRÁCTICAS DIRIGIDAS NIVEL AVANZADO SESIÓN 2 Universidad Nacional de Ingeniería. Pág. N° 40.

(44) Excel 2013 – Nivel Avanzado. INFO UNI. PRÁCTICA 1 - TABLA DINÁMICA 1.- Digitar la siguiente tabla referente al Reporte: I Trimestre 2013 con un total de 381 REGISTROS o fila de datos. Seleccionar toda la tabla y asignarle el nombre REGISTROS.. En la siguiente imagen se podrá comprobar los últimos registros de la tabla.. 2.- Consideraciones para la creación de Informes. 2.1.- Crear un Informe: En base al campo BANCO y al campo IMPORTE, comprobar en la siguiente imagen. Teniendo como referencia los clientes, aunque el informe no será necesario activar el campo.. 2.2.- Crear un Informe: En base al campo TRANSACCIÓN y al campo IMPORTE, comprobar en la siguiente imagen. Teniendo como referencia los clientes, aunque el informe no será necesario activar el campo.. Universidad Nacional de Ingeniería. Pág. N° 41.

(45) Excel 2013 – Nivel Avanzado. INFO UNI. 2.3.- Crear un Informe: En base a los campos CLIENTE, TRANSACCIÓN e IMPORTE, comprobar en la siguiente imagen.. FILTRAR LOS SIGUIENTES CLIENTES: A & B Equipos Médicos, Casa de Cambios JC, Comercial Los Reyes, Compunet S.R.L., Infomarket SRL, Metalcen SRL, Panadería El Trigal. Después crear una Tabla de doble entrada (CLIENTES como FILA y TRANSACCIÓN como COLUMNA), comprobar en la siguiente imagen.. 2.4.- Crear un Informe: En base al campo CLIENTE y al campo IMPORTE, filtrar los mismos clientes (7 criterios) del informe anterior, para proyectar Ventas, para los próximos 2 meses, teniendo como referencia que estos importes son acumulados del 1er trimestre del presente año 2013 En la siguiente imagen tenemos la base de nuestro informe es decir vamos a escoger los campos CLIENTE e IMPORTE.. Universidad Nacional de Ingeniería. Pág. N° 42.

(46) Excel 2013 – Nivel Avanzado. INFO UNI. Utilizando campo calculado: Ficha ANALIZAR / grupos CÁLCULOS, / opción CAMPOS, ELEMENTOS Y CONJUNTOS / escoger la opción CAMPOS CALCULADO.. Proyectar: Primera Proyección 9% del Importe trimestral (mes de Abril) Proyectar: Segunda Proyección 12% del Importe trimestral (mes de Mayo). En la siguiente imagen se podrá comprobar nuestras acciones de cálculo respecto al informe trimestral. 2.5.- Crear un Informe: En base al informe anterior de proyecciones vamos a actualizar la proyección del segundo mes que era 12% ahora será el 15.3%. En la siguiente imagen se podrá visualizar el cálculo anterior es decir la Segunda Proyección es 12% del Importe trimestral. En la siguiente imagen se podrá visualizar el nuevo cálculo respecto a la Segunda Proyección será de 15.3%. En la siguiente imagen se podrá comprobar nuestras acciones de cálculo respecto al informe trimestral actualizado al 15.3% para Segunda Proyección es decir el segundo mes proyectado (Mayo 2013).. Universidad Nacional de Ingeniería. Pág. N° 43.

(47) Excel 2013 – Nivel Avanzado. INFO UNI. PRÁCTICA 2 - TABLA DINÁMICA – GRÁFICO DINÁMICO 1.- Crear un informe grafico en base a la selección de los campos BANCO, IMPORTE Y COMISIÓN. Teniendo en cuenta que los campos Importe y Comisión son datos numéricos y tienen una distancia entre sus cantidades será necesario crear un tipo de grafica denominada EJE SECUNDARIO, con el objetivo de compartir en nuestra grafica ambas cantidades.. En la siguiente imagen se tiene la tabla dinámica en base a los campos BANCO, IMPORTE Y COMISIÓN. En la siguiente imagen podemos comprobar las acciones necesarias para optimizar la lectura de los dos campos numéricos IMPORTE Y COMISIÓN Por ejemplo: El eje secundario del campo COMISIÓN será necesario cambiar el ancho del intervalo a 500% (queda a su criterio escoger un valor %). En la siguiente imagen tenemos nuestro gráfico con los contenidos de los campos BANCO, IMPORTE Y COMISIÓN. Respecto al campo COMISION se está escogiendo la opción DAR FORMATO A SERIE DE DATOS para obtener nuestro informe como gráfico de doble entrada.. Universidad Nacional de Ingeniería. Pág. N° 44.

(48) Excel 2013 – Nivel Avanzado. INFO UNI. PRÁCTICAS PROPUESTAS NIVEL AVANZADO. SESIÓN 2 Universidad Nacional de Ingeniería. Pág. N° 45.

(49) Excel 2013 – Nivel Avanzado. INFO UNI. PRÁCTICA PROPUESTA 1 - TABLA DINÁMICA Con información referente a la siguiente tabla,, crear informes y gráficos.. Consideraciones: Para el desarrollo de la siguiente práctica propuesta utilizando las herramientas de tabla Dinámica. Recuerde que la tabla dinámica se debe mostrar en una nueva hoja. 1) Mostrar las ventas por año y trimestre para cada uno de los productos, en cada región 2) a) Realizar una tabla que obtenga el promedio de ventas por REGIÓN para cada vendedor. Por cada o todos los años. b) Graficarlo. 3) Crear una nueva tabla dinámica en otra hoja que permita visualizar: Cantidades vendidas por producto y por región. 4) Mediante un gráfico dinámico mostrar el promedio de ventas de cada región por año.. Universidad Nacional de Ingeniería. Pág. N° 46.

(50) Excel 2013 – Nivel Avanzado. INFO UNI. PRÁCTICA PROPUESTA 2 - TABLA DINÁMICA 1.- Digitar la siguiente tabla referente al Reporte: I Trimestre 2013 con un total de 381 REGISTROS o fila de datos. Seleccionar toda la tabla y asignarle el nombre REGISTROS.. En la siguiente imagen se podrá comprobar los últimos registros de la tabla.. Consideraciones para crear los Informes de Tabla Dinámica y Gráfico Dinámico. 1.- Crear un Informe: Cual es el importe que se registró en cada Transacción Bancaria como ingreso por cada tipo de cuenta y además especificar en qué Banco fue este dinero depositado. 2.- En base al informe anterior asignar formato de Moneda en Soles de tipo Contabilidad. Además reemplazar los espacios vacíos correspondientes al tipo de Transacción y el Banco, rellenar estos espacios al no haber deposito por el mensaje Sin Ingresos. 3.- Crear un informe en la misma hoja (es decir en el informe actual). Escoger la opción Hoja de cálculo existente. Escoger el campo IMPORTE. Ahora deseamos que se separe los ingresos por cada BANCO, además respecto al tiempo expresado en mes por el campo FECHA MES. Ahora vamos a incluir en nuestro informe dentro de cada mes el tipo de TRANSACCIÓN. 4.- Crear un nuevo informe en base a los campos BANCO y TRANSACCIÓN, para que nos muestre un reporte porcentual de acuerdo al total acumulado por columna.. Universidad Nacional de Ingeniería. Pág. N° 47.

(51) Excel 2013 – Nivel Avanzado. INFO UNI. Sesión 3 FUNCIONES FINANCIERAS Descripción y aplicación de las funciones financieras en el enfoque de la perspectiva, representación empresarial. Funciones:       . VA VF PAGO PAGOINT PAGOPRIN NPER TASA. Universidad Nacional de Ingeniería. Pág. N° 48.

(52) Excel 2013 – Nivel Avanzado. INFO UNI. FUNCIONES FINANCIERAS El administrador financiero juega un papel importante en la empresa, sus funciones y su objetivo pueden evaluarse con respecto a los Estados financieros Básicos. Sus tres funciones primarias son:   . El análisis de datos financieros. La determinación de la estructura de activos de la empresa. La fijación de la estructura de capital.. La nueva perspectiva empresarial ya no se basa en la maximización de las utilidades esta ha cambiado por un enfoque de la maximización de la riqueza.. 1. Análisis de datos financieros Esta función se refiere a la transformación de datos financieros a una forma que puedan utilizarse para controlar la posición financiera de la empresa, a hacer planes para financiamientos futuros, evaluar la necesidad para incrementar la capacidad productiva y a determinar el financiamiento adicional que se requiera. 2. Determinación de la estructura de activos de la empresa El administrador financiero debe determinar tanto la composición, como el tipo de activos que se encuentran en el Balance de la empresa. El término composición se refiere a la cantidad de dinero que comprenden los activos circulantes y fijos.. 3. Determinación de la estructura de capital Esta función se ocupa del pasivo y capital en el Balance. Deben tomarse dos decisiones fundamentales acerca de la estructura de capital de la empresa. Al determinar la estructura de activos de la empresa, se da forma a la parte del activo y al fijar la estructura de capital se están construyendo las partes del pasivo y capital en el Balance. También debe cumplir funciones específicas como:  Evaluar y seleccionar clientes  Evaluación de la posición financiera de la empresa  Adquisición de financiamiento a corto plazo La importancia de la función financiera depende en gran parte del tamaño de la empresa. En empresas pequeñas la función financiera la lleva a cabo normalmente el departamento de contabilidad, a medida que la empresa crece la importancia de la función financiera da por resultado normalmente la creación de un departamento financiero separado; una unidad autónoma vinculada directamente al presidente de la compañía, a través de un administrador financiero. Contabilidad vs Finanzas Para muchos la función financiera y contable de un negocio es virtualmente la misma. Aunque hay una relación estrecha entre estas funciones, la función contable debe considerarse como un insumo necesario de la función financiera.. Universidad Nacional de Ingeniería. Pág. N° 49.