• Author / Uploaded
• Cristian Urcia Diaz

Citation preview

Exper t

BUSINESS INTELLIGENCE

MASTER

EXCEL METODOLOGÍA ACTIVA APRENDE + HACIENDO

Convierte datos en información haciendo uso de herramientas de MS Excel como: Formato condicional, Filtros, Tablas Dinámicas, Fórmulas y Funciones, Gráficos, Dashboard, entre otras.

Juan QUINDE LI SAY TAN

MASTER EXCEL

Juan QUINDE LI SAY TAN

CUADROS DE MANDO Y TABLAS DINÁMICAS CON EXCEL

JUAN IGNACIO QUINDE LI SAY TAN

1 Juan QUINDE LI SAY TAN

DEDICATORIA Para mi amada esposa Silvia y mis adoradas hijas Luciana y Rafaela. Para mis queridos padres (Juan y Dilcia). Para mis hermanos Silvana y Mario.

AGRADECIMIENTO A mi familia y amigos por sus consejos y apoyo invalorable. A mis compañeros de trabajo del Satp, Sagafalabella, Universidad de Piura y KPI.

Importante: Los ejercicios y datos presentados en el presente libro son ficticios y han sido preparados únicamente para fines didácticos. Los ejercicios están diseñados para apoyar el mejoramiento de competencias técnicas de los interesados.

Master Excel

2

TABLA DE CONTENIDO Introducción

7

Temas importantes antes de empezar

9

PARTE I

15

HERRAMIENTAS EMPRESARIALES Y DE ANÁLISIS DE DATOS Formato condicional

17

Subtotales y Esquemas

23

Filtros

31

PARTE II

45

INTELIGENCIA DE NEGOCIOS Y TABLAS DINÁMICAS Conceptos fundamentales

47

Elaboración de tablas dinámicas

52

Herramientas de inteligencia de negocios

67

PARTE III

97

CUADROS DE MANDO Y KPI’S CON EXCEL (DASHBOARD) Dashboard

98

Soluciones con Dashboard

109

ANEXO

126

Fórmulas y funciones

126

3 Juan QUINDE LI SAY TAN

EVOLUCIÓN DE LAS HOJAS DE CÁLCULO A continuación se explica la cronología de las hojas de cálculo electrónicas y de la aplicación de Microsoft Excel. 

Antigüedad - Las primeras hojas de cálculo nacen con el desarrollo de la escritura y las matemáticas. Se recoge la información de una manera ordenada.



1846- Agustus de Morgan matemático del siglo XIX introdujo un sistema de matrices (filas y columnas) para su uso en la contabilidad financiera, sentando las bases de la estructura principal de las hojas de cálculo, la celda.



1952 – Aparece por primera vez el término Hoja de Cálculo escrito en un diccionario, el diccionario de Kohler.



1960 – Richard Mattesich planteó y sentó las bases para la computerización de las hojas de cálculo.



1969- Pardo y Landau desarrollan los algoritmos básicos para la creación y programación de las hojas de cálculo electrónicas, desarrollan el lenguaje LANPAR que fue utilizada por empresas como General Motors, AT&T, etc.. con objeto de realizar presupuestos informáticamente.



1978 – Daniel Singer Bricklin desarrolla el primer prototipo de VisiCalc programado bajo el sistema operativo de Apple. VisiCalc es considerado como la primera hoja de cálculo electrónica desarrollada e implementada con éxito en los ordenadores personales.



1979 – Bricklin decide impulsar el primer prototipo de VisiCalc , para ello crea la empresa Software Arts Corporation junto con Bob Frankston, ambos lanzaron la primera versión de VisiCalc para el ordenador personal Apple 2 por un precio de 100 dólares, en esta primera versión la hoja de cálculo electrónica está comprendida por un total de 20 filas y 4 columnas.



1982 – La empresa de Bill Gates Microsoft desarrolla la hoja de cálculo electrónica Multiplan como alternativa a VisCalc, Multiplan sería la base para el desarrollo de la hoja de cálculo Excel.



1983 – Se lanza al mercado Lotus 1-2-3, una hoja de cálculo electrónica compatible con los ordenadores IBM y el sistema operativo MS-Dos, su éxito desplazó del mercado a VisiCalc dado a la capacidad de generar gráficas así Master Excel

4

como la posibilidad de usar funciones predefinidas y la facilidad de su uso al disponer de un menú en la parte superior de la pantalla. Posteriormente en la versión 2.0 de Lotus 123 se incluiría la programación y automatización de las hojas de cálculo mediante Macros. 

1985 – Se lanza al mercado la primera versión Excel, la cual fue desarrollada por Microsoft para el Macintosh de Apple , en esta primer versión se dispone de menús desplegables así como la posibilidad de utilizar el ratón, la facilidad de su uso fue la razón por la que muchas personas y empresas decidieron adquirir un Macintosh para utilizar Excel como herramienta de cálculo.



1987 – Nace Excel 2.0 una nueva versión de la famosa hoja de cálculo desarrollada para Windows 2.0, el éxito alcanzado por el programa ofimático de Microsoft desplaza y elimina a otras hojas de cálculo electrónicas como Lotus 1-2-3

A partir de este año, Microsoft lanza periódicamente nuevas versiones de Excel, mejorando las características y manejo de la hoja de cálculo más usada en todos los tiempos: 

En la versión 3.0 se añade la barra de herramientas para la creación de gráficos, también se incluye la herramienta Solver para el cálculo y optimización de ecuaciones.



En la versión Excel 4.0 se incluye el primer set de funciones estadísticas, financieras y de ingeniería, ampliando el uso de las hojas de cálculo a otras ramas científicas.



En la versión Excel 5.0 se introduce el concepto de libro de trabajo, donde cada libro contiene un número limitado de hojas de cálculo.



En el año 1995 se lanza la versión 7.0 también conocida como Excel 95, en esta nueva versión sienta las bases para el entorno gráfico de las actuales versiones de Excel.



La comunicación con Internet y el trabajo en red con Excel vendrá definitivamente con la Versión 9.0 de Excel



En la versión 12.0 y con el lanzamiento de Office 2007 se cambia por completo el entorno gráfico de Excel, añadiendo la denominada "Cinta" o "Rubbon" en la cabecera de la hoja de cálculo.

5 Juan QUINDE LI SAY TAN

INTRODUCCIÓN La Inteligencia de Negocios consiste en convertir los datos en información útil en forma de reportes, gráficos e indicadores para que los responsables tomen decisiones. Una potente herramienta que facilita la generación de información es MS Excel. En otras palabras, podemos utilizar Excel para “hacer hablar a los datos, exprimirlos”. El presente libro tiene un enfoque empresarial y ha sido elaborado con el objetivo de dar a conocer un conjunto de herramientas de Excel y lograr que el lector domine las herramientas. A través de la consecución del objetivo el lector podrá aprovechar los conocimientos para: (i) ahorrar tiempo, (ii) aumentar la productividad y (iii) mejorar la comunicación a través de informes ejecutivos diseñados adecuadamente. El libro está especialmente diseñado para que el lector pueda aprender paso a paso diversas funcionalidades de Excel. La metodología utilizada se denomina Aprende+Haciendo. El método le permitirá al lector aprender a su ritmo. Es decir, cada capítulo contiene un conjunto de casos y cada caso combina un conjunto de herramientas de Excel que se explican de forma específica a través de pasos. En otras palabras, el lector irá conociendo y aplicando diversas herramientas de Microsoft Excel a través de casos prácticos relacionados con aspectos empresariales.

Master Excel

6

De esta manera, el lector aprenderá a elaborar soluciones combinando herramientas profesionales de Excel (como tablas dinámicas, formato condicional, funciones avanzadas, gráficos, filtros, entre otras). El reto es plasmar lo que se quiere comunicar de manera precisa y utilizar un diseño de reporte (físico y/o digital) que facilite la comprensión de la información a los usuarios (gerentes, mandos medios y trabajadores en general). El libro contiene nueve capítulos organizados en tres partes: (i) Herramientas empresariales y de análisis de datos, (ii) Inteligencia de Negocios y Tablas Dinámicas y (iii) Cuadros de Mando y KPI’ con Excel (Dashboard). La primera parte contiene herramientas básicas de Excel como formato condicional, subtotales, filtros, entre otras. En la segunda parte se hará especial énfasis en el diseño y construcción de tablas dinámicas como potente herramienta para la Inteligencia de Negocios. La última parte combina herramientas, tablas dinámicas y funciones avanzadas con el fin de construir soluciones basadas en indicadores de gestión (KPI’s). El libro también incluye un capítulo inicial con temas fundamentales de Excel y al final, en el anexo, una selección de funciones. Deseamos que el libro sea de utilidad para ayudarlo en su carrera profesional.

El autor

7 Juan QUINDE LI SAY TAN

TEMAS IMPORTANTES ANTES DE EMPEZAR

Es importante realizar las siguientes actividades

POR FAVOR, AGREGA UN NUEVO DIRECTORIO EN EL ESCRITORIO DE TU MÁQUINA LLAMADO: KPI_IN_[TUS INICIALES] …. POR EJEMPLO, SI MI NOMBRE ES JUAN SÁNCHEZ, EL NOMBRE DE LA CARPETA DEBE SER: KPI_IN_JS ESTA CARPETA LA UTILIZARÁS PARA GUARDAR LOS ARCHIVOS Y EJERCICIOS RELACIONADOS CON EL CURSO.

TEMA 1. GRUPOS Y BUSCARV (o CONSULTAV para algunas versiones de Excel) 1. Abra un nuevo archivo Excel. 2. A partir de la celda A1 escriba lo siguiente: 10 Sony 20 Hp 30 Toshiba

Master Excel

8

3. Seleccione desde la celda A1 hasta la celda B3. 4. Cree un grupo. Nombrarlo: grupo_marcas y presiona Enter (ver la siguiente imagen). (es muy importante presionar Enter luego de escribir grupo_marcas) 5. A partir de la celda F1 escriba lo siguiente: 10 20 20 30 10 10

6. En la celda G1 escriba: =BUSCARV(F1,grupo_marcas,2,FALSO) 7. Presione Enter. 8. Copie la formula al resto de la columna. 9. Guarde el archivo en tu carpeta.

Ejercicio: Crea un ejemplo simple utilizando Grupos y la función BUSCARV. Por ejemplo, utilizando cinco países o ciudades.

9 Juan QUINDE LI SAY TAN

TEMA 2. FUNCIÓN CONDICIONAL SI 1. Abra un nuevo archivo Excel. 2. Escriba a partir de la celda A1: Valor 1

1000

Valor 2

Piura

Valor 3

A

3. En la celda A10 escriba la siguiente fórmula: =SI(B1>500,"Excelente","Regular") 4. En la celda A12 escriba la siguiente fórmula: =SI(B3=”Piura”,”Si”, “No”) 5. En la celda A14 escriba la siguiente fórmula: =SI(B3=”Chiclayo”,"OK","Volver a intentar") 6. En la celda A16 escriba la siguiente fórmula: =SI(Y(B1=1000,B5="A"),200,10) 7. En la celda A18 escriba la siguiente fórmula: =SI(Y(B3="Piura",B5="B"),200,10) 8. En la celda A20 escriba la siguiente fórmula: =SI(O(B3="Piura",B5="B"),200,10) 9. Analiza lo realizado. 10. Guarda el archivo en tu carpeta.

Master Excel

10

TEMA 3. FUNCIÓN EXTRAE (o MED para algunas versiones de Excel) 1. Abra un nuevo archivo Excel. 2. Escriba a partir de la celda A1: LIMA SANTIAGO BRASILIA QUITO

3. En la celda B1 escriba la siguiente fórmula: =EXTRAE(A1,1,3) 4. Copie la formula al resto de la columna. 5. En la celda C1 escriba la siguiente fórmula: =EXTRAE(A1,2,1) 6. Copie la formula al resto de la columna. 7. Analice lo realizado. 8. En la celda F1 ingrese su nombre 9. En la celda G1 ingrese su primer apellido. 10. En la celda H1 ingrese su segundo apellido. 11. En la celda I2 extraiga la primera letra de tu nombre, la primera letra de tu apellido y la primera letra de tu segundo apellido. =CONCATENAR(EXTRAE(F1,1,1), EXTRAE(G1,1,1), EXTRAE(H1,1,1))

11 Juan QUINDE LI SAY TAN

TEMA 4. AYUDA F1 Excel ofrece un gran contenido de ayuda para el usuario. Al presionar la tecla F1 se abre una pantalla que permite colocar en la parte superior izquierda el tema del cual se requiere ayuda. Por ejemplo: 1. 2. 3. 4. 5.

En una hoja Excel presione F1. Escriba en la parte superior: BUSCARV Presione Enter. Seleccione: BUSCARV (función BUSCARV) Excel presenta la descripción, especificación y ejemplos. Ejercicio: Prueba la ayuda con la función SI. La Ayuda es fundamental para aprender y repasar las funcionalidades de Excel.

Master Excel

12

TEMA 5. TECLAS DE FUNCIÓN Y DE MÉTODO ABREVIADO 1. Teclas de método abreviado combinadas con CTRL TECLA

DESCRIPCIÓN

CTRL+MAYÚS+&

Aplica el contorno a las celdas seleccionadas.

CTRL+MAYÚS+!

Aplica el formato Número con dos decimales, separador de miles y signo menos (-) para los valores negativos.

CTRL+1

Muestra el cuadro de diálogo Formato de celdas.

CTRL+9

Oculta filas seleccionadas.

CTRL+0

Oculta columnas seleccionadas.

CTRL+N

Aplica o quita el formato de negrita.

CTRL+C

Copia las celdas seleccionadas.

CTRL+B

Muestra el cuadro de diálogo Buscar y reemplazar con la ficha Buscar seleccionada.

CTRL+P

Muestra el cuadro de diálogo Imprimir. Guarda el archivo activo con el nombre de archivo, la ubicación y el formato de archivo actuales.

CTRL+G CTRL+S

Aplica o quita el formato de subrayado.

CTRL+V

Inserta el contenido del Portapapeles en el punto de inserción y reemplaza cualquier selección. Disponible solamente después de haber cortado o copiado un objeto, texto o el contenido de una celda. Corta las celdas seleccionadas.

CTRL+X CTRL+Z

Utiliza el comando Deshacer para invertir el último comando o eliminar la última entrada que escribió.

2. Teclas de función TECLA F1

DESCRIPCIÓN Muestra el panel de tareas Ayuda de Microsoft Office Excel. CTRL+F1 muestra u oculta la cinta de opciones, que es un componente de la Interfaz de usuario de Microsoft Office Fluent. ALT+F1 crea un gráfico a partir de los datos del rango actual. ALT+MAYÚS+F1 inserta una hoja de cálculo nueva.

F2

Modifica la celda activa y coloca el punto de inserción al final del contenido de la celda. También mueve el punto de inserción a la barra de fórmulas cuando la edición en una celda está desactivada.

F4

Repite el último comando o acción, si es posible.

13 Juan QUINDE LI SAY TAN

Parte I

Herramientas empresariales y de análisis de datos Explora las herramientas que Excel ofrece para simplificar el manejo de datos empresariales.

Formato condicional Útil para destacar información relevante a través de formatos y/o íconos de diverso diseño.

Subtotales y esquemas Práctica herramienta para el agrupamiento de datos y manejo de información simplificada.

Filtros Eficaz y ágil herramienta para la búsqueda de datos en tablas de diferentes estructuras.

Master Excel

14

↘↘ ANÁLISIS DE DATOS CON EXCEL Datos e Información “Los datos, organizados y empleados debidamente, pueden convertirse en información. La información, absorbida, comprendida y aplicada por las personas, puede convertirse en conocimientos. Los conocimientos, aplicados frecuentemente en un campo pueden convertirse en sabiduría, y la sabiduría es la base de la acción positiva.” Cooley (1987)

La siguiente representación muestra gráficamente la representación de la evolución de los datos hasta la sabiduría.

Excel: Manejo de datos Microsoft Excel es una hoja de cálculo que permite manipular datos numéricos y alfanuméricos dispuestos en forma de tablas. Una tabla es la unión de filas y columnas. Habitualmente es posible realizar cálculos complejos con fórmulas y funciones. Excel permite publicar la información en diferentes modelos de tablas y dibujar distintos tipos de gráficas. Así mismo, es posible interrelacionar hojas a través de referencias y comunicarse con otras aplicaciones, por ejemplo, Word y Access.

15 Juan QUINDE LI SAY TAN

*01

[] [] [] []

FORMATO CONDICIONAL

↙↙

El formato condicional se utiliza para aplicar unas características de formato a determinadas celdas de la hoja de cálculo, dependiendo de que se cumplan determinadas condiciones. Es decir, sirve para destacar los valores más representativos de una lista. Por ejemplo, si usted tiene una lista de productos con su respectiva venta neta y se quiere identificar aquellos productos con mayor venta, puede utilizar el formato condicional para destacar los montos mayores sombreándolos de color verde. Utilice un formato condicional que le ayude a explorar y analizar datos visualmente, a detectar problemas importantes y a identificar modelos y tendencias.

Master Excel

16

INTRODUCCIÓN Cuando se analizan datos, es frecuente que surjan preguntas como:    

¿Quién ha vendido más de US$ 10.000 este mes? ¿Cuáles son los trabajadores más antiguos? ¿De qué productos han aumentado los ingresos más del 10%? ¿Cuáles son los estudiantes con mejores resultados?

El formato condicional ayuda a responder estas preguntas porque facilita el proceso de resaltar celdas o rangos de celdas interesantes y valores inusuales. Un formato condicional cambia el aspecto de un rango de celdas en función de una condición. Sólo si la condición es verdadera, el rango de celdas basa el formato en dicha condición.

↘↘ CASO 1.1 VENTAS 1. Abra el archivo KPI_01_10_FCondicional. 2. Ubíquese en la hoja Ventas. 3. Calcule el Total a Cobrar. Ingrese en la celda D4 la siguiente fórmula: =B4+C4 4. Copie la fórmula al resto de la columna Total a Cobrar. 5. Aplique formato condicional a la columna Ventas con los siguientes pasos: 6. Seleccione los valores de la columna Ventas: desde la celda B4 hasta la B12. 7. Seleccione: Inicio  Formato condicional  Barra de datos  Barra de datos naranja

17 Juan QUINDE LI SAY TAN

8. Note que las barras representan el peso relativo de cada valor; lo cual mejora la visibilidad de la información. 9. Seleccione los valores de la columna Comisión: desde la C4 hasta C12. 10. Seleccione: Inicio  Formato condicional  Barra de datos  Barra de datos verde 11. Seleccione los valores de la columna Total a Cobrar: desde la D4 hasta D12. 12. Seleccione: Inicio  Formato Condicional  Conjunto de Íconos  Tres flechas (de color)

13. Seleccione, nuevamente, los valores de la columna Ventas: desde la celda B4 hasta la celda B12. 14. Seleccione: Inicio  Formato Condicional  Conjunto de Íconos  Tres banderas Nota: Con los pasos 13 y 14, se quiere mostrar que una celda puede recibir más de un formato condicional a la vez. El ejercicio realizado ayuda a comprender cómo se puede Breve utilizar el formato condicional para explorar y analizar explicación datos visualmente. Los colores y formas gráficas que Excel ofrece son utilizados para identificar información importante. Por ejemplo, revise la información del primer vendedor (Castro), se puede observar un elevado nivel de ventas, pero sus comisiones son muy bajas. De la misma forma revise vendedor por vendedor.

Master Excel

18

Muy importante: Una funcionalidad muy importante del formato condicional es que, para determinados íconos, es posible personalizar rangos (definir los rangos de un semáforo por ejemplo). Es decir, indicar valores específicos para los límites de los colores. A continuación se muestran los pasos: 15. Seleccione desde la celda D4 a D12. 16. Seleccione: Formato Condicional  Administrar reglas… 17. Seleccione Editar regla… 18. Para el color verde, en lugar del valor 67 elija 80 (el 67 y el 80 representan un porcentaje a partir del cual el color mostrado será verde). 19. Para el color ámbar, en lugar del valor 33 elija 50 (el 33 y el 50 representan el porcentaje a partir del cual el color será ámbar). 20. Presione Aceptar. 21. Nuevamente presione Aceptar. 22. Revise el resultado. Cuando se administran las reglas, no sólo es posible modificar los porcentajes, también es posible asignarle un número específico. 23. Seleccione desde la celda D4 a D12. 24. Seleccione: Formato Condicional  Administrar reglas… 25. Seleccione Editar regla… 26. Para el color verde, en lugar del tipo porcentual, elija número. Y en valor ingrese 14,000 27. Para el color ámbar, en lugar del tipo porcentual, elija número. Y en valor ingrese 10,000. 28. Presione Aceptar. 29. Nuevamente, presione Aceptar. Revise el resultado. 30. Guarde el archivo en la carpeta creada en el Escritorio.

19 Juan QUINDE LI SAY TAN

↘↘ CASO 1.2 BODEGA 1. En el archivo KPI_01_10_FCondicional, ubíquese en la hoja Bodega. 2. Agregue tres registros más a la lista (Cerveza, Yogurt y Pisco). A cada registro agregado, incluya datos (a su criterio) en las columnas Stock final y Venta de la última semana. 3. Calcule la cobertura en semanas ingresando en la celda D4 la siguiente fórmula: =B4/C4 La cobertura calculará la cantidad de semanas que, en teoría, se tendrá stock para venta. Es decir, si el resultado para la fila Arroz indica 3 semanas de cobertura, significa que la bodega tendrá stock para vender por tres semanas antes de que se acabe el Arroz. 4. 5. 6. 7.

Aplique a la celda D4 formato de número (con un decimal). Copie la fórmula al resto de la columna Cobertura. Marque todos los valores de la columna D. Aplique el Formato Condicional: Escala de colores verde, amarillo y rojo.

8. El formato seleccionado permitirá observar fácilmente los productos con mayor cobertura en verde (posible sobre stock, es decir, posiblemente se tiene demasiado stock) y los productos con menor cobertura en rojo (posible quiebre de stock). Probablemente los que se encuentren en rojo sean los productos que el bodeguero debe comprar a la brevedad. 9. Seleccione la celda E3 y escriba COSTO EN SOLES. Master Excel

20

10. Desde la celda E4 hasta el final, ingrese para cada producto un costo. 11. Seleccione la celda F3 y escriba COSTO DE LA MERCADERÍA. 12. En la celda F4, ingrese la siguiente fórmula: =B4*E4 El inventario a costo es el valor en soles del inventario de la bodega. 13. Copie la fórmula al resto de la columna Costo de la Mercadería. 14. Seleccione:

Inicio  Formato condicional  Reglas superiores e inferiores  10 superiores…

15. Escriba el número 5 en el cuadro y presione Aceptar. Es decir, marcará en rojo los cinco productos con mayor costo. 16. Las celdas resaltadas representan los cinco productos con mayor inventario (inversión) de la bodega. 17. Analice la información.

Ω

21 Juan QUINDE LI SAY TAN

*02 SUBTOTALES

[] [] [] []

↙↙

Y ESQUEMAS La función de subtotales en Excel nos permite tener, de manera casi inmediata, filas con los totales de la información que tenemos en nuestra hoja de cálculo. Es decir, nos ayuda a agrupar data y muestra el total de cada grupo. Por ejemplo, suponiendo que tenemos una hoja con los gastos del personal que hemos realizado, podemos obtener el subtotal de lo gastado por cada categoría de nuestro gasto. El requisito indispensable antes de aplicar subtotales es contar con data debidamente ordenada (en forma ascendente o descendente).

Master Excel

22

↘↘ CASO 2.1 NEGOCIOS SATURNO Usted es el encargado del área de Ventas de la empresa SATURNO. Necesita preparar los datos de los trabajadores de la empresa y generar información para la reunión semanal con sus vendedores. Es muy importante que el informe sea entregado en forma oportuna y que la información sea la correcta. Se le pedirá identificar: El mejor vendedor, el mejor mes y la mejor región en ventas. Prepare los datos 1. Abra el archivo: KPI_01_30_SATURNO 2. Renombre la Hoja1 por el nombre: Ventas 3. Verifique que la data esté bien estructurada, es decir, que la data no tenga filas y columnas vacías.  Genere información 1. Seleccione la celda A1. 2. Seleccione: Datos  Esquema Subtotal

23 Juan QUINDE LI SAY TAN

3. Aparece la pantalla Subtotales. Verifique que se encuentre seleccionados los siguientes campos: Número de pedidos y Ventas.

4. Presione Aceptar. 5. Seleccione con el mouse los números 1 – 2 – 3 ubicados en la parte superior izquierda de la hoja para agrupar o desagrupar la información. Breve El nivel 1, mostrará la suma total. explicación El nivel 2, mostrará los totales por vendedor. El nivel 3, mostrará todos los datos. 6. Seleccione con el mouse el número 2. 7. Selecciones la celda E1 y ordene la columna(Ventas) en orden descendente. Observe cómo los datos se organizan del vendedor con mayor venta al vendedor con menor venta.

Master Excel

24

8. Identifique y anote el vendedor con mayor venta (primero de la tabla ordenada) y el vendedor con menor venta (último de la tabla). Vendedor con mayor venta ____________________________ Vendedor con menor venta ____________________________ 9. Ordene la lista por la columna D (Número de pedidos) e identifique lo siguiente: Vendedor con mayor número de pedido _____________________ Vendedor con menor número de pedidos ____________________ 10. Retire los subtotales: - Seleccione la celda A1. - Seleccione: Datos  Esquema  Subtotal -

Presione el botón Quitar todos.

Ejercicio práctico: 1. Utilizando Subtotales obtenga la siguiente información:  ¿En qué Región vende más y en qué Región vende menos SATURNO? (Recuerde que antes de aplicar subtotales, los datos deben estar ordenados debidamente) ______________________________________________________  ¿En qué mes vendió más y en qué mes vendió menos SATURNO? ______________________________________________________  ¿Quién fue el vendedor que colocó más pedidos en febrero? ______________________________________________________

25 Juan QUINDE LI SAY TAN

↘↘ CASO 2.2 VENTA DE VEHÍCULOS Necesita preparar una presentación para una reunión anual. Cuenta con la información resumida pero requiere organizarla por trimestre y por tipo de vehículo, de tal forma, que sea fácil su lectura y visualización.

Prepare los datos 1. Abra el archivo: KPI_01_40_VEHICULOS. 2. Renombre la Hoja1: Venta_Anual. 3. Seleccione el grupo de celdas que contienen los montos de venta: desde la celda C5 hasta la celda N10. 4. Dé formato de número (sin decimales) al grupo seleccionado.

5. Inserte una columna después del mes de marzo. Para insertar la columna, seleccione la columna F y presione el botón Insertar.

Master Excel

26

6. Luego, escriba en la celda F4: TRIM1. 7. Para obtener la venta del primer trimestre, ingrese la función Suma en la celda F5: =SUMA(C5:E5) 8. Copie la fórmula de la celda F5 en el resto de la columna Trim1. 9. Seleccione desde la celda F5 hasta la celda F10 y sombree las celdas de un color.

10. Igualmente, inserte columnas para TRIM2, TRIM3 y TRIM4 con sus respectivas fórmulas. Coloree el fondo de las celdas tal como se hizo en el paso 9. 11. Ingrese una columna al final de la tabla e ingrese el nombre en la celda S4: Venta Anual. 12. Sume la venta de todos los trimestres. En la celda S5 escriba la fórmula: =F5+J5+N5+R5 13. Copie la fórmula de la celda S5 en el resto de la columna Venta Anual. 14. Coloree el fondo de las celdas que contienen el monto de Venta Anual tal como se hizo en el paso 9. Escoja un color diferente al seleccionado para los trimestres.  Genere información 1. Verifique que la columna A esté ordenada en forma ascendente. Es requisito para trabajar con Subtotales. 2. Seleccione la celda A4.

27 Juan QUINDE LI SAY TAN

3. Seleccione Subtotal, ubicado en el grupo Esquema dentro de la pestaña Datos. 4. Aparece la pantalla Subtotales. 5. Marque desde Enero hasta Venta Anual, sin saltarse ningún mes ni trimestre. 6. Presione Aceptar.

7. Excel ingresó los subtotales para Total Autos, Total Camiones y Total general. 8. Aplique negrita a las tres filas subtotales indicadas en el paso 7. 9. Para ingresar Esquemas: 10. Seleccione las columnas C, D y E. 11. Seleccione Agrupar, ubicado en el grupo Esquema dentro de la pestaña Datos.

12. Aparecen, en la parte superior izquierda, dos niveles de navegación. 13. Agregar Esquemas para TRIM2, TRIM3 y TRIM4. 14. Para agregar un Esquema a Venta Anual: - Seleccione las columnas desde la C hasta la R.

Master Excel

28

-

Seleccione Agrupar, ubicado en el grupo Esquema dentro de la pestaña Datos. 15. En los niveles de navegación ubicados en la parte superior izquierda, seleccione el Número 1 para Subtotales y el Número 1 para el Esquema. Aparece la Venta Total Anual. 16. Navegue por la información utilizando los niveles de navegación (1 – 2 – 3) tanto del Subtotal como del Esquema. También puede utilizar los signos (+) y (-) ubicados en la vertical y horizontal de la hoja. 17. Por ejemplo, seleccione para Subtotal el número 2 y para Esquema también seleccione el número 2. Lo que muestra es la venta trimestral por tipo de vehículo. 18. Realice las combinaciones que crea conveniente. 19. Guarde el archivo.

Ω

29 Juan QUINDE LI SAY TAN

*03 FILTROS

[] [] [] []

↙↙

El filtrado de datos es una manera fácil y rápida de buscar un subconjunto de datos en una tabla y trabajar en él. Por ejemplo, puede filtrar los datos para ver únicamente los valores que especifique, filtrar los datos para ver el valor máximo y el valor mínimo o filtrar los datos para ver rápidamente valores duplicados. Se utiliza usualmente para realizar consultas rápidas. Además, se puede llegar a resultados precisos combinando filtros en más de una columna. Es importante tener en cuenta que la estructura de la tabla debe ser la apropiada.

Master Excel

30

↘↘ CASO 3.1 PRISMA – Información de los trabajadores Usted es el encargado del área de Personal de la empresa PRISMA. Se le ha encargado revisar los datos de los trabajadores de la empresa y preparar información para la reunión del comité de gerencia. Es muy importante tener cuidado con el manejo de los datos (deben ser los correctos). La empresa está en expansión por lo que se debe preparar la lista teniendo presente que se incorporarán más trabajadores a la firma. Preparando los datos 4. Abril el archivo: KPI_02_10_EMPRESA_PRISMA 5. Renombrar la Hoja2 por el nombre: Area 6. En la hoja Area, ingresar a partir de la celda A1 la siguiente tabla: Cod_Area A01 A02 A03 A04 A05

Area Contabilidad Logística Operaciones Personal Gerencia

7. Renombrar la Hoja3 por el nombre: Local 8. En la hoja Local, ingresar a partir de la celda A1 la siguiente tabla: Cod_Local L01 L02 L03 L04 31 Juan QUINDE LI SAY TAN

Local Perú Argentina Brasil España

9. Crear un rango: En la hoja Area, seleccione con el mouse desde la celda A1 hasta la celda B6. 10. En el cuadro de nombre escriba: grupo_area. Luego presione Enter.

11. Crear un rango: En la hoja Local, seleccione con el mouse desde la celda A1 hasta la celda B5. 12. En el cuadro de nombre, escriba: grupo_local. Luego presione Enter. 13. Función BUSCARV: en la hoja Planilla, escriba en la celda E2 lo siguiente: =BUSCARV(D2,grupo_area,2,FALSO) Breve Función BUSCARV Explicación Busca un valor específico en la primera columna de una matriz de tabla y devuelve, en la misma fila, un valor de otra columna de dicha matriz de tabla. La V de BUSCARV significa vertical. La fórmula del paso 11, buscará el contenido de la celda D2 (es decir el código de área A01) en la grupo_area (creado en el paso 7) y mostrará en la celda E2 el nombre del área (Contabilidad).

14. Copie la fórmula hasta la celda E21. 15. Función BUSCARV: en la hoja Planilla, escriba en la celda G2 lo siguiente: =BUSCARV(F2,grupo_local,2,FALSO) 16. Copie la fórmula hasta la celda G21. Master Excel

32

17. Cree una hoja y nómbrela: SueldoBruto. 18. En la hoja SueldoBruto, ingrese a partir de la celda A1 lo siguiente: Cod_Area A01 A02 A03 A04 A05 A01 A02 A03 A04 A05 A01 A02 A03 A04 A05 A01 A02 A03 A04 A05

Cod_Local Sueldo L01 2200 L01 1800 L01 1400 L01 1500 L01 6000 L02 2400 L02 1800 L02 2000 L02 2100 L02 5500 L03 1800 L03 1400 L03 1500 L03 1400 L03 7000 L04 1800 L04 2000 L04 2100 L04 2000 L04 6500

19. Inserte una columna entre las columnas B y C. 20. Escriba en la celda C1: AreaLocal. 21. En la celda C2, ingrese la función: =CONCATENAR(A2,B2) Breve Función CONCATENAR Explicación Concatena dos o más cadenas en una cadena de texto. Es decir, para nuestro caso une dos celdas. Otra alternativa es utilizar la el signo “&” que representa la acción juntar. Para el paso anterior, también se puede ingresar la función: =A2&B2

33 Juan QUINDE LI SAY TAN

22. Copie la función hasta la celda C21. 23. Crear un rango: En la hoja SueldoBruto, seleccione con el mouse desde la celda C1 hasta la celda D21. 24. En el cuadro de nombre, escriba: grupo_sueldobruto. Luego presione Enter. 25. En la hoja Planilla, ingrese en la celda H2 la siguiente fórmula: =BUSCARV(CONCATENAR(D2,F2),grupo_sueldobruto,2,FALSO) 26. Copie la fórmula ingresada hasta la celda H21. 27. Manejo de fechas: Para la calcular la edad, escriba en la celda J2: =ENTERO((HOY()-I2)/365) Tenga en cuenta que I2 es i2, no es 12 (doce) Breve Función ENTERO Explicación Redondea un número hasta el entero inferior más próximo. Para nuestro caso, se calculará la cantidad de días entre la fecha de hoy y la fecha de nacimiento. Luego, se dividirá entre 365 días para calcular la cantidad de años. Función SIFECHA Como alternativa para calcular la edad, también se puede utilizar la función SIFECHA. Probar ingresando: =SIFECHA(I2,HOY(),”y”) SIFECHA es una función oculta de Excel (no aparece en la lista de funciones). También se puede calcular los días y meses reemplazando la “y” por “d” y “m” respectivamente.

28. Copie la fórmula al resto de la columna J. 29. Manejo de fechas: Para calcular la antigüedad en la empresa que tiene cada trabajador, escriba en la celda L2: =ENTERO((HOY()-K2)/365) Master Excel

34

30. Copie la fórmula al resto de la columna L. 31. Función SI: En la celda N, escriba: =SI(M2="S",60,0)*SI(L2>=5,10,1) 32. Para calcular el sueldo neto, escriba en la celda O2:=H2+N2. 33. Copie la fórmula al resto de la columna O. 34. Escriba en la celda P1: Bono. 35. Escriba la siguiente fórmula en la celda P2: =SI(O2>=3000,100,SI(O2>=2000,150,200)) Breve Función SI Explicación Devuelve un valor si la condición especificada es VERDADERO y otro valor, si dicho argumento es FALSO. Utilice SI para realizar pruebas condicionales en valores y fórmulas. Para nuestro caso, si el sueldo es igual o mayor a 3000, el bono es 100. Si no, se cuestiona otra vez: Si el sueldo es igual o mayor a 2000, el bono es 150. Finalmente, si el sueldo es menor a 2000, el bono es 200. 36. Copie la fórmula al resto de la columna P. 37. Finalmente, dele formato a la primera fila (Fila 1): Por ejemplo, Negrita y Color de Relleno (a criterio del alumno).

38. Guarde el archivo (Ctrl + G). 35 Juan QUINDE LI SAY TAN

 Transformando los datos en información 1. Filtros: Obtenga los trabajadores de la empresa PRISMA que trabajen en Perú en las áreas de Personal u Operaciones.

2. Recuerde: para deshabilitar filtros presionar Ctrl + Shift + L. 3. Utilizando filtros, obtener la siguiente información: Filtro

Ingresa la respuesta

Trabajadores con 30 años de edad o más, con antigüedad en la empresa menor a 10 años y que tengan hijos. ¿Cuántos son?  Para deshabilitar filtros, presioner Ctrl + Shift+ L. Trabajadores de Brasil o Argentina, con sueldo mayor a 2000 y que trabajen en Contabilidad. ¿Cuánto suma su sueldo neto?  Para deshabilitar filtros, presione Ctrl + Shift+ L. Trabajadores que su apellido empiece con “S”. ¿Cuántos son?  Para deshabilitar filtros, presione Ctrl + Shift+ L. ¿Cuántos trabajadores tienen hijos y son menores de 30 años?  Para deshabilitar filtros, presionar Ctrl + Shift+ L.

¿Qué porcentaje del Sueldo_Neto total de los trabajadores es el sueldo de los trabajadores de Perú?  Para deshabilitar filtros, presione Ctrl + Shift+ L.

¿Cuántos trabajadores tiene la empresa PRISMA en Perú?  Para deshabilitar filtros, presione Ctrl + Shift+ L.

Master Excel

36

↘↘ CASO 3.5 SANTA LUCÍA Venta de electrodomésticos y productos de deportes Santa Lucía es una organización dedicada a la venta de productos de electro y deporte. Tiene tres tiendas en el país (Norte, Sur y Lima). El archivo contiene la venta del año pasado de cada uno de los 150 productos que comercializa la cadena. Así también, contiene el stock actual, el precio y el costo. Usted es el analista de datos de la empresa y se le ha encargado realizar una evaluación completa de las ventas del año pasado. Además, es necesario preparar para la administración una tabla dinámica donde se compare la venta por familia de productos y marcas que comercializa. Preparando los datos 1. Abra el archivo KPI_02_30_SANTA_LUCIA. 2. La hoja VENTA tiene los datos de stock y venta de 150 productos. La lista tiene 28 columnas con datos generales y específicos. 3. Cada producto pertenece a una Familia de productos. Cada Familia de productos tiene SubFamilias y cada SubFamilia tiene Clases de productos. 4. La hoja GRUPOS contiene tres listas que nos ayudarán a clasificar los productos por Familia, SubFamilia y Clases.

37 Juan QUINDE LI SAY TAN

5. Ir a la celda A2 de la hoja VENTA y escribir la siguiente fórmula: =EXTRAE(E2,1,3) Breve Función EXTRAE Explicación EXTRAE devuelve un número específico de caracteres de una cadena de texto, comenzando en la posición que especifique y en función del número de caracteres que especifique. Para nuestro caso: Se extraerán del Cod_Clase a partir del 1er carácter, 3 caracteres. 6. Copie la fórmula en el resto de la columna Cod_Familia (hasta la celda A151). 7. Ubíquese en la celda C2 y escriba la siguiente fórmula: =EXTRAE(E2,1,5) 8. Copie la fórmula en el resto de la columna Cod_SubFamilia (hasta la celda C151). 9. Crear grupo: Ubíques en la hoja GRUPOS. 10. Seleccione desde la celda A1 hasta la B5. 11. Escriba en el cuadro de nombres: grupo_familia. 12. Seleccione desde la celda E1 hasta la F14. 13. Escriba en el cuadro de nombres: grupo_subfamilia. 14. Seleccione desde la celda I1 hasta la J32. 15. Escriba en el cuadro de nombres: grupo_clase.

Master Excel

38

Nota: para administrar los grupos creados se debe acceder al Administrador de Nombres. Por ejemplo, se podrá eliminar un grupo.

16. Seleccione la celda M2. 17. Escribir en el cuadro de nombres: IGV. 18. Regrese a la hoja VENTA. 19. Aplique la función BUSCARV para relacionar el código de la Familia con su descripción. 20. Posiciónese en la celda B2. Escriba: =BUSCARV(A2,grupo_familia,2,FALSO) 21. Copie la fórmula al resto de la columna Familia. 22. Posiciónese en la celda D2. Escriba: =BUSCARV(C2,grupo_subfamilia,2,FALSO) 23. Copie la fórmula al resto de la columna SubFamilia. 24. Posiciónese en la celda F2. Escribir: =BUSCARV(E2,grupo_clase,2,FALSO) 25. Copie la fórmula al resto de la columna Clase.

39 Juan QUINDE LI SAY TAN

26. Cálculo del margen neto y el margen porcentual. Nota: El margen es la ganancia o utilidad. Ejm: Si usted compra un terreno a 8,000 y lo vende a 10,000, su margen neto es 2,000 y su margen porcentual es 20%.

Fórmula del Margen Neto: Margen Neto = Precio Venta* – Costo de Venta* * Si el precio de venta tiene IGV, el costo también debe tenerlo. * Si el precio de venta no tiene IGV, el costo tampoco debe tenerlo.

Fórmula del Margen Porcentual: Margen Porcentual (%) = Margen Neto / Precio Venta

27. En la celda N2 de la hoja VENTA, escriba la siguiente fórmula: =L2-M2*(1+IGV) Nota: Se está utilizado IGV (nombre de grupo definido anteriormente. Vea paso 17). 28. Copie la fórmula al resto de la columna Margen_Neto. 29. Seleccione las columnas: Precio, Costo sin igvyMargen_Neto. 30. Dele formato (Estilo millares) para dar mejor presentación.

31. En la celda O2, escriba la fórmula: =N2/L2 Master Excel

40

32. Copie la fórmula al resto de la columna Margen%. 33. Dele formato a la columna margen: Estilo porcentual (forma abreviada: Ctrl + Shift + %). 34. Aumente un decimal.

35. En la celda W2, escriba la fórmula. =T2*$L2 Venta_Neta = (Venta en unidades) x (precio de venta) Anclar celdas: $F$C

Para anclar celdas, se utiliza el signo $. $F: Anclar fila. $C: Anclar columnas. $F$C: Anclar una celda.

36. Copie la fórmula al resto de la columna Venta_Neta_Norte. 37. Ingrese la fórmula de Venta_Neta para el SUR y para LIMA (Columnas X e Y). 38. En la celda Z2, escriba la fórmula: =W2*$O2 Margen_Neto = (Venta_Neta) x (Margen porcentual) 39. Copie la fórmula al resto de la columna Margen_Neto_Norte. 40. Ingrese la fórmula de Margen_Neto para el SUR y para LIMA (Columnas AA y AB). 41. Agregue tres columnas a la lista. - En la celda AC1 escriba: Venta_Unidades_Total - En la celda AD1 escriba: Venta_Neta_Total - En la celda AE1 escriba: Margen_Neto_Total 42. Agregue sumatorias a las nuevas columnas agregadas: - En AC2 escriba:=SUMA(T2:V2) … y copie al resto de la columna. - En AD2 escriba: =SUMA(W2:Y2) … y copie al resto de la columna. - En AE2 escriba: =SUMA(Z2:AB2) … y copie al resto de la columna. 41 Juan QUINDE LI SAY TAN

43. Revise la tabla en general. 44. Guarde el archivo.  Transformando los datos en información Filtros: Información clave y de rápido acceso. Para facilitar la visualización y el manejo de base de datos: 1. Ubíquese en la hoja Venta. 2. Reduzca el ancho de las columnas A, B, C, D, E y F. Selección las columnas (todas a la vez). Luego AntiClic y seleccione ancho de columna (colocar Ancho = 12).

3. Inmovilice paneles: Colóquese en la celda K2 e inmovilizar. Vista Inmovilizar paneles Inmovilizar paneles.

Master Excel

42

4. Con el fin de ver la mayor cantidad de datos aplique un zoom de 70% (a criterio del alumno). Zoom se encuentra en la parte inferior de la pantalla. 5. Utilizando filtros, obtenga el número de laptops vendidas en: Norte: __________ Sur: __________ Lima: __________ 6. Utilizando filtros, obtener cuál es la venta neta total (Norte+Sur+Lima) de las siguientes marcas: SONY: __________ MONARK: __________ OSTER: __________ 7. Utilizando filtros, se quiere saber cuántas unidades la laptop Samsung (modelo SPEED1) se han vendido: __________ 8. Quite los filtros (método abreviado: Ctrl + Shift + L 9. ¿Cuál es el producto más vendido en unidades en SANTA LUCÍA? _____________________________________

¿Cuál es el producto más vendido en Venta_Neta? _____________________________________

Ω 43 Juan QUINDE LI SAY TAN

Parte II

Inteligencia de Negocios y Tablas Dinámicas La tabla dinámica es una potente herramienta. Combinándolas con otras herramientas como formato condicional, gráficos, filtros y funciones avanzadas los tomadores de decisiones pueden convertir sus datos en información útil.

Conceptos Fundamentales Revisión general de conceptos de Sistemas de Información MS Excel, Tablas dinámicas y Tipos de Datos.

Elaboración de Tablas Dinámicas Potente herramienta utilizada para convertir datos en información útil y presentarla en forma de resumen.

Inteligencia de Negocios: Combinación de tablas Dinámicas con Otras Herramientas Combinación de Tablas dinámicas con herramientas como Formato condicional, filtros y funciones.

Ejercicios propuestos Conjunto de ejercicios útiles para repasar los conceptos anteriores.

Master Excel

44

45 Juan QUINDE LI SAY TAN

(Mundo real)

OBSERVACIÓN  Análisis  Comprensión

 Proceso

 Imágenes

 Números

INFORMACIÓN

Cuando se relaciona se convierte en

 Palabras

DATO

Cuando se agrupan se convierte en

 Sistema

 Decisión

 Estructura

CONOCIMIENTO

(Mundo real)

ACCIÓN

Esquema básico de la Inteligencia de Negocios:

*04 CONCEPTOS

() () () ()

↙↙

FUNDAMENTALES Sistemas de Información Microsoft Excel Tablas Dinámicas Tipos de Datos

Master Excel

46

↘↘ 4.1 SISTEMAS DE INFORMACIÓN Tomado dePressman (2012)1

La palabra “sistema” es posiblemente el término más usado y abusado del léxico técnico. Hablamos de sistemas políticos, de sistema educativos, de sistemas bancarios, entre otros. Tomando prestada la definición del diccionario Webster, definimos un sistema basado en computadora como: Un conjunto o arreglo de elementos que están organizados para realizar un objetivo predefinido procesando información.

Un sistema de información basado en computadora está formado por seis elementos bien definidos: 1. Software.- Programas de computadora y estructuras de datos que sirven para hacer efectivo el método lógico, procedimiento o control requerido. 2. Hardware.-Dispositivos electrónicos que proporcionan capacidad de cálculo (p. ej.: servidores, cpus) y dispositivos electromecánicos que proporcionan una función externa (p. ej.: sensores, motores, bombas). 3. Personas.- Usuarios y operadores del hardware y software que toman (o dejan de tomar) decisiones. 4. Base de datos.- Una gran colección de información organizada a la que se accede por medio del software. 5. Documentación.- Manuales, formularios y otra información descriptiva que retrata el empleo y/o operación del sistema. 6. Procedimientos.- Los pasos que definen el empleo específico de cada elemento del sistema o el contexto procedimental en que reside el sistema. Los elementos se combinan de varias maneras para transformar información.

1

Pressman (2012): Ingeniería del Software. Un Enfoque Práctico. Quinta Edición. McGraw-Hill

47 Juan QUINDE LI SAY TAN

↘↘ 4.2 MICROSOFT EXCEL Excel, en su versión 2007-2010-2013, ofrece avances en cuanto a su funcionalidad como herramienta de Business Intelligence o Inteligencia de Negocios pata todo tipo de usuarios. Una potente herramienta son las tablas dinámicas. Para aprovechar mejor las tablas dinámicas exige por parte del usuario un conocimiento de la herramienta, pero también, un conocimiento de lo que quiere hacer previamente y lo más importante: qué tipo de información quiere obtener al final del proceso.

↘↘ 4.3 TABLAS DINÁMICAS Las tablas dinámicas son una herramienta de Excel avanzada y potente, con ellas se puede analizar, mostrar y manipular los datos de diferentes formas. Son un tipo de informes generados mediante Excel en diferentes formatos, a partir de una base de datos inicial en forma de tabla simple. Las tablas de datos (bases de datos) pueden poseer muchas filas y columnas que contengan diferentes tipos de datos. El secreto de las tablas dinámicas –curiosamente– no está tanto en la funcionalidad de la herramienta y su potencialidad sino que está en el origen (tabla simple) y las características de los datos que se utilicen. Por tanto, es muy importante conocer, antes de confeccionar las tablas dinámicas, qué información necesitamos. Es decir, para obtener las respuestas correctas, se debe hacer las preguntas adecuadas previamente.

Master Excel

48

↘↘ 4.4 TIPOS DE DATOS Todo sistema de información debe tener unas variables básicas que lo componen y que debemos analizar y entender: los tipos de datos. La identificación de los tipos de datos es fundamental para la elaboración de tablas dinámicas. Tal es así que el diseño de la tabla dinámica está en función de los tipos de datos identificados en la tabla simple. De modo general y práctico, tenemos los siguientes tipos de datos (o tipos de variables): Tipo de Variables Temporales

Descripción

Ejemplos

Nos indican una temporalidad. Las variables temporales se expresan en minutos, horas, días, semanas, meses, trimestres, años, etc.

Año Mes Semana Día Hora

Las variables cualitativas nos expresan una cualidad del dato a analizar, por ejemplo, la zona, el sector, el nombre de un cliente, el código de un producto. Son todas aquellas variables que, en cierta manera, nos expresan el contenido de la información analizada, y a priori no se realizan operaciones con ellas.

País Ciudad Zona Área DNI Producto Código identificador (ID)

Cuantitativas Las variables cuantitativas son expresadas o Numéricas normalmente en variables numéricas que son objeto de operaciones como pueden ser sumas, restas, conteos, entre otras.

Monto de venta Kilos Sueldo Unidades venta Precio

Cualitativas

49 Juan QUINDE LI SAY TAN

Como veremos en los diferentes ejemplos, estas tres variables son las que nos permitirán realizar los diferentes tipos de análisis. Las tablas dinámicas nos van a permitir organizar, segmentar y clasificar los diferentes tipos de datos de una forma fácil y rápida.

REGLA IMPORTANTE: -

Los tipos de datos Cualitativos y/o Temporales se colocarán (generalmente) en los espacios: FILAS, COLUMNAS Y FILTROS Los tipos de datos Cuantitativos o Numéricos se colocarán (generalmente) en el espacio VALORES.

Master Excel

50

*05 ELABORACIÓN () () () ()

DE TABLAS DINÁMICAS

51 Juan QUINDE LI SAY TAN

↙↙

↘↘ CASO 5.1 MOTORES Y VEHÍCULOS 1. Abra el archivo KPI_100_01_Motores y vehículos. 2. El archivo reúne diversos registros de ventas en unidades y venta neta por año.

3. Identifique los tipos de variables (escríbalas en el siguiente cuadro): Temporales Cualitativas Cuantitativas ¿se puede sumar?

4. Seleccione la celda A1 5. Selecciona: Insertar  Tabla dinámica

6. Aparece la pantalla: Crear tabla dinámica Master Excel

52

7. Esta pantalla solicitará dos decisiones: a. La fuente de datos y b. El lugar donde se desea mostrar la tabla dinámica.

8. Presione Aceptar 9. Una tabla dinámica ayuda a obtener información a partir de una tabla simple de datos. Es importante que los datos a transformar en información estén organizados en columnas apropiadas.  La calidad de la tabla dinámica está en función de la calidad de los tipos de datos.

53 Juan QUINDE LI SAY TAN

10. Arrastra los campos según lo siguiente:

11. Como se observa, de forma rápida se ha elaborado un resumen de la venta neta por Zona. 12. Seleccione un valor numérico de la tabla dinámica. 13. Ordene la tabla dinámica de mayor a menor presionando el botón 14. Seleccione todos los valores numéricos de la tabla dinámica y aplique formato número estilo millar. Ejercicio 1: Elabore una nueva tabla dinámica que muestre la venta en unidades por Zona. Ordene la tabla y aplique formato estilo millar a los datos numéricos. Pregunta: ¿Cuál es la zona de mayor venta en unidades? ___________________ Ejercicio 2: Elabore una nueva tabla dinámica que muestre la venta neta por año. Ordene la tabla y aplique formato estilo millar a los datos numéricos. Pregunta: ¿En qué año se vendió más? ___________________ Ejercicio 3: Elabore una nueva tabla dinámica que muestra la venta neta y la venta en unidades por zona y año. Revise la información y ubica datos relevantes (no es necesario escribirlos). ..::Revisar y Guardar ::..

Master Excel

54

↘↘ CASO 5.2 FÁBRICAS DE MUEBLES 15. Abra el archivo KPI_100_02_Fabricas de muebles 16. Identifique los tipos de variables (escríbalas en el siguiente cuadro): Temporales Cualitativas Cuantitativas ¿se puede sumar?

17. Seleccione la celda A1 18. Seleccione: Insertar  Tabla dinámica

19. Aparece la pantalla: Crear tabla dinámica 20. Presione Aceptar 21. Se creará una tabla dinámica para mostrar la producción por país. Arrastre los campos según lo siguiente:

55 Juan QUINDE LI SAY TAN

22. Seleccione la producción de la tabla dinámica (es decir, seleccione los valores numéricos). 23. Aplique formato de número: estilo millares Aplique un mismo formato a los datos de un resumen ayuda a visualizar mejor la información.

24. Elabore una nueva tabla dinámica. Se desea mostrar la producción semanal por fábrica. Además se podrá seleccionar el tipo de producto en el filtro general. Arrastre los campos según lo siguiente:

25. Seleccione un estilo (diseño) de tabla dinámica. Herramientas de tabla dinámica  Diseño  Estilos de tabla dinámica Seleccione: Estilo de tabla dinámica medio 3

Master Excel

56

26. Elabore una nueva tabla dinámica. Arrastre los campos según lo siguiente:

27. Utilizando los comandos de ordenar, responda las siguientes preguntas:

-

¿En qué semana se fabricaron más salas? ¿En qué semana se fabricaron menos salas? ¿En qué semana se fabricaron más comedores? ¿En qué semana se fabricaron menos comedores?

_____________ _____________ _____________ _____________

NAVEGANDO EN LA TABLA DINÁMICA: LLEGAR AL DETALLE (POTENTE FUNCIONALIDAD DE LAS TABLAS DINÁMICAS) 28. Una potente función de tablas dinámicas es llegar al detalle (ver registros) haciendo doble clic en la celda de la tabla dinámica que el usuario elija. 29. Por ejemplo, si se quiere llegar a saber el detalle de la producción de Comedores, se tiene que hacer doble clic en la celda B7. 30. Aparece en una nueva hoja con el detalle. Revísela. 31. Regrese a la hoja que contiene la tabla dinámica. 32. Continúe probando la función. Haga doble clic en la celda D15.

..::Revisar y Guardar ::.. 57 Juan QUINDE LI SAY TAN

↘↘ CASO 5.4 REALFOX: COMPRAS ANUALES 1. Abre el archivo KPI_100_04_RealFox 2. El archivo está formado por dos hojas: o Hoja Data: Contiene las compras de productos realizada a diversos proveedores durante un año. o Hoja Listas: Contiene los códigos y descripción de los proveedores y productos. 3. Seleccione la celda A1 de la hoja Listas 4. Seleccione desde la celda A1 hasta la celda B5 5. Escriba en el cuadro de nombres: grupo_proveedores 6. Seleccione la hoja Data. 7. Escriba en la celda D2 la siguiente fórmula: =BUSCARV(C2,grupo_proveedores,2,FALSO) 8. La fórmula busca el código del proveedor en el grupo creado (grupo_proveedores). Luego, muestra en la celda D2 el contenido correspondiente de la segunda columna del grupo. 9. Copie la fórmula al resto de la columna D. 10. Escriba en la celda F2 la siguiente fórmula: =BUSCARV(E2,grupo_productos,2,FALSO) 11. Copie la fórmula al resto de la columna F. 12. Escriba en la celda H2 la siguiente fórmula: =BUSCARV(E2,grupo_productos,3,FALSO) 13. Copie la fórmula al resto de la columna H. 14. En la celda I2, ingrese la fórmula: =H2*G2. (Corresponde a la fórmula del total) 15. Ingrese el mes en la columna J. Escriba en la celda J1: Mes. Master Excel

58

16. Escriba la fórmula en la celda J2: =Mes(B2). 17. Identifique los tipos de variables (escríbalas en el siguiente cuadro): Temporales Cualitativas Cuantitativas ¿se puede sumar?

18. Elabore una tabla dinámica que muestre la venta por mes. Coloque los campos como se indica a continuación:

19. Dele formato de número: millares con dos decimales. 20. Seleccione desde la celda B4 hasta la celda B15. 21. Vamos a aplicar Formado Condicional. 22. Seleccione: Inicio  Formato Condicional  Conjuntos de iconos  Tres símbolos (sin círculo)

59 Juan QUINDE LI SAY TAN

23. Excel muestra los valores separados en buenos (check verde), regulares (interrogación amarillo) y malos (aspa roja). 24. De esta manera, se ha combinado las tablas dinámicas con el formato condicional para obtener información. 25. Seleccione la celda B6 de la tabla dinámica. 26. Seleccione: Insertar  Barra  Cilindro horizontal agrupado

27. Copie la tabla dinámica y el gráfico al PowerPoint. Ejercicio 1: Elabore una nueva tabla dinámica que muestre la venta por proveedor. Aplique formato condicional para identificar el proveedor con mayor venta. Ejercicio 2: Elabore una nueva tabla dinámica que muestre, a la vez, la venta por proveedor y por producto. Luego cree un gráfico. Ejercicio 3: Elabore una nueva tabla dinámica que muestre, a la vez, la venta por proveedor, por producto y por mes. ..::Revisar y Guardar ::.. Master Excel

60

↘↘ CASO 5.6 INMUEBLES AMERIKA 1. Abra el archivo KPI_100_06_Inmuebles Amerika. 2. El archivo contiene registros de alquileres y ventas de diferentes tipos de inmuebles, en diversas provincias y realizadas por cinco vendedores. 3. Identifique los tipos de variables (escríbelas en el siguiente cuadro): Temporales Cualitativas Cuantitativas

4. Se utilizarán las tablas dinámicas para mostrar, en la misma hoja de la data, los resúmenes. 5. Seleccione desde la columna A hasta la columna H. 6. Inserte una tabla dinámica: Insertar  Tabla dinámica. 7. En el formulario Crear tabla dinámica, seleccione la opción: Hoja de cálculo existente.

8. En el recuadro Ubicación, elija la celda L5. 9. Presione Aceptar.

61 Juan QUINDE LI SAY TAN

10. Prepare el resumen de ventas por vendedor, colocando los campos según lo siguiente:

11. Ordene la tabla dinámica de mayor a menor. 12. Inserte en la celda L18 una tabla dinámica que muestre el monto por tipo de inmueble. 13. Inserte en la celda L30 una tabla dinámica que muestre el monto por Operación. 14. Inserte en la celda L40 una tabla dinámica que muestre, a la vez, el monto por vendedor y por tipo de inmueble. Ejercicio 1: Inserte una tabla dinámica en una nueva hoja que muestre, a la vez, el monto por año y por tipo de operación. (ayuda, utilice la función =AÑO() Ejercicio 2: Inserte un gráfico dinámico que muestre el monto por vendedor y por provincia.

..::Revisar y Guardar ::.. Master Excel

62

↘↘ CASO 5.7 NEGOCIOS NEPTUNO 1. Abra el archivo KPI_100_07_Neptuno. 2. Complete las columnas sombreadas en amarillo. Utilice las hojas Meses, Tipos_Clientes y Trabajadores para crear grupos junto con la función Buscarv. 3. En la celda K2 escriba la función: =SI(J2=5,10,1) 67. Para calcular el sueldo neto, escriba en la celda O2:=H2+N2 68. Copie la fórmula al resto de la columna O. 69. Escriba en la celda P1: Bono. 70. Escriba la siguiente fórmula en la celda P2: =SI(O2>=3000,100,SI(O2>=2000,150,200)) Breve Función SI Explicación Devuelve un valor si la condición especificada es VERDADERO y otro valor si dicho argumento es FALSO. Utilice SI para realizar pruebas condicionales en valores y fórmulas. Para nuestro caso, si el sueldo es igual o mayor a 3000, el bono es 100. Si no, se cuestiona otra vez: Si el sueldo es igual o mayor a 2000, el bono es 150. Finalmente, si el sueldo es menor a 2000, el bono es 200.

73 Juan QUINDE LI SAY TAN

71. Copie la fórmula al resto de la columna P. 72. Finalmente, dele formato a la primera fila (Fila 1): Por ejemplo, Negrita y Color de Relleno (a criterio del alumno).

73. Guarde el archivo (Ctrl + G). 74. Identifique los tipos de variables (escríbalas en el siguiente cuadro): Temporales Cualitativas Cuantitativas

 Transformando los datos en información 75. Inserte una nueva hoja y nómbrela Informe. Ubíquela como primera hoja del libro. 76. En la hoja Planilla, seleccione las COLUMNAS desde la A hasta la P. (no seleccione celdas, seleccione columnas completas). 77. Inserte una primera tabla dinámica. En el campo, Elija dónde desea colocar el informe de tabla dinámica, seleccione Hoja de cálculo existente.

Master Excel

74

78. En Ubicación, seleccione la celda A3 de la hoja Informe. 79. Elabore la tabla dinámica, de tal forma que muestre el número de trabajadores, el sueldo neto y el promedio del sueldo neto por área. Siga la siguiente guía:

80. Seleccione los valores del Sueldo Promedio.

81. Aplique Formato Condicional, seleccionando: Inicio  Formato Condicional  Barras de datos  Barra de datos verde 82. De esta manera, Excel agrega una barra de tamaño proporcional a cada valor.

75 Juan QUINDE LI SAY TAN

Formato El formato condicional se utiliza para aplicar unas Condicional características de formato a determinadas celdas de la hoja de cálculo, dependiendo de que se cumplan determinadas condiciones. Es decir, sirve para destacar los valores más representativos de una lista. Por ejemplo, si tiene una lista de productos con su respectiva venta neta y se quiere identificar aquellos productos con mayor venta, se puede utilizar el formato condicional para destacar los montos mayores sombreándolos de color verde. Utilice un formato condicional que le ayude a explorar y analizar datos visualmente, a detectar problemas importantes y a identificar modelos y tendencias.  Una forma acertada de obtener información es combinado la funcionalidad de las tablas dinámicas con la funcionalidad de los formatos condicionales. 83. En la hoja Planilla, agregue dos registros en las filas 32 y 33. Elija usted los nombres y demás datos. Es muy importante considerar que las fórmulas de las columnas E, G, H, J, N, O y P están listas para copiarse. 84. Para actualizar la tabla dinámica: a. Seleccione un valor de la tabla dinámica. b. Haga clic derecho. c. Seleccione Actualizar.

85. La selección de columnas del paso 37 es la acción que te permite actualizar la tabla dinámica después de agregar/eliminar/modificar valores en la tabla de datos (en este caso en la hoja Planilla).

Master Excel

76

Segunda tabla dinámica: 86. En la hoja Planilla, selecciona nuevamente las COLUMNAS desde la A hasta la P. 87. Inserta una segunda tabla dinámica debajo de la primera tabla dinámica (Separación entre tablas de tres filas). 88. Elabora la tabla dinámica de tal forma que muestre el número de trabajadores, el sueldo neto y el promedio del sueldo neto por local. Sigue la siguiente guía:

Tercera tabla dinámica: 41. En la hoja Planilla, seleccione nuevamente las COLUMNAS desde la A hasta la P. 42. Inserte una tercera tabla dinámica debajo de la segunda tabla dinámica (Separación entre tablas de tres filas). 43. Elabore la tabla dinámica, de tal forma que muestre el sueldo neto por local y por área. Siga la siguiente guía:

77 Juan QUINDE LI SAY TAN

44. Seleccione los valores de los sueldos (sin seleccionar los totales generales).

45. Aplique formato condicional a: d. Los 02 (dos) valores más altos. e. Los 02 (dos) valores más bajos. Ayuda: Utilice: Inicio  Formato Condicional  Reglas superiores e inferiores  10 mejores (o 10 inferiores según sea el caso) Preguntas: a. Utilice la primera tabla dinámica para conocer los nombres de los dos Gerentes. b. Utilice la segunda tabla dinámica para identificar quién es el trabajador de Perú con mayor sueldo neto. c. Modifique la tercera tabla dinámica de tal forma que, en lugar de Sumar los sueldos netos, Cuente los sueldos netos. Interprete el resultado.

46. Inserte una columna entre las columnas J y L. 47. Escriba en la celda K1: Rango_Edad 48. Ingrese la siguiente fórmula en la celda K2: =SI(J2>=45,"Mayor o igual a 45","Menor a 45") La fórmula convierte un dato cuantitativo (Edad) en un dato cualitativo. 49. Copie la fórmula al resto de columna. 50. Inserte una cuarta tabla dinámica que indique el sueldo totaly la cantidad de trabajadorespor Rango_Edad. Analice el resultado.

..::Revisar y Guardar ::.. Master Excel

78

↘↘ CASO 6.4 SAN IGNACIO San Ignacio es una tienda de electrodomésticos que tiene registradas sus más de dos mil facturas en una base de datos. Además posee el registro de veinte productos y cincuenta clientes. Se solicitará preparar un informe para obtener información para la toma de decisiones.

Preparando los datos 1. Abra el archivo: KPI_200_02_San Ignacio. 2. El archivo contiene cuatro hojas: Facturas, Clientes, Productos y Detalles de la factura. 3. Seleccione la hoja Facturas. 4. Seleccione las columnas A, B y C. 5. Nombrar el grupo como: gFacturas. 6. Seleccione la hoja Clientes 7. Seleccione las columnas A, B y C 8. Nombre el grupo como: gClientes 9. Seleccione la hoja Productos 10. Seleccione las columnas A, B, C, D y E 11. Nombre el grupo como: gProductos

79 Juan QUINDE LI SAY TAN

12. Utilice la función BUSCARV para completar las siete columnas en amarillo: Fecha: =BUSCARV(B2,gFacturas,2,FALSO) Id_Cliente: =BUSCARV(B2,gFacturas,3,FALSO) Nombre_Apellido_Cliente: =CONCATENAR(BUSCARV(D2,gClientes,2,FALSO)," ", BUSCARV(D2,gClientes,3,FALSO)) Jerarquía: =BUSCARV(F2,gProductos,2,FALSO) Nombre_Producto: =BUSCARV(F2,gProductos,3,FALSO) Costo_Producto: =BUSCARV(F2,gProductos,4,FALSO) Precio_Producto: =BUSCARV(F2,gProductos,5,FALSO) No olvide copiar los resultados al resto de sus respectivas columnas. 13. Escriba en la celda L2 la función: =K2*J2 14. Copie la fórmula al resto de la columna. 15. Inserte dos columnas entre las columnas C y D 16. En la celda D1, escribe: Mes 17. En la celda E1, escribe: Año 18. En la celda D2, escribe la fórmula: =MES(C2) Si aparece en formato de fecha, cambie el formato de la celda a formato General. 19. Copie la fórmula al resto de la columna D. 20. En la celda E2, escribe la fórmula: =AÑO(C2) 21. Copie la fórmula al resto de la columna E. 22. Identifique los tipos de variables (escríbalas en el siguiente cuadro): Temporales Cualitativas Cuantitativas

Master Excel

80

 Transformando los datos en información Primera tabla dinámica: 23. Inserte una tabla dinámica que muestre la venta neta y la venta en unidades por jerarquía y por año. 24. Inserte una fórmula a la tabla dinámica. Seleccione un valor de la tabla dinámica. Luego seleccione: Opciones  Fórmulas  Campo calculado

25. Escriba en el nombre: Precio Prom 26. Escriba la fórmula: ='Venta Neta' /CANTIDAD 27. Presione Aceptar

28. El resumen que se solicita es el siguiente:

81 Juan QUINDE LI SAY TAN

29. Utilice el formato condicional para destacar la mayor venta neta del 2013 y la menor venta. 30. Utilice el formato condicional para destacar la mayor venta neta del 2014 y la menor venta. 31. Ordene la tabla dinámica de mayor a menor según la venta del año 2014 (Usted puede utilizar el botón Ordenar

)

Segunda tabla dinámica 32. Inserte una tabla dinámica debajo de la primera que muestre la venta neta por año. En el filtro general coloque la Jerarquía 33. Utilice la segunda tabla dinámica para insertar un gráfico a la derecha de la tabla dinámica. El reporte debería verse como sigue:

34. Utilice el formato condicional para destacar el mayor y menor valor del año 2013.

Master Excel

82

↘↘ CASO 6.5 SANTA LUCÍA Venta de electrodomésticos y productos de deportes Santa Lucía es una organización dedicada a la venta de productos de electro y deporte. Tiene tres tiendas en el país (Norte, Sur y Lima). El archivo contiene la venta del año pasado de cada uno de los 150 productos que comercializa la cadena. Así también, contiene el stock actual, el precio y el costo. Usted es el analista de datos de la empresa y se le ha encargado realizar una evaluación completa de las ventas del año pasado. Además, es necesario preparar para la administración una tabla dinámica donde se compare la venta por familia de productos y marcas que comercializa. Preparando los datos 45. Abra el archivo KPI_200_04_Santa Lucia. 46. La hoja VENTA tiene los datos de stock y venta de 150 productos. La lista tiene 28 columnas con datos generales y específicos. 47. Cada producto pertenece a una Familia de productos. Cada Familia de productos tiene SubFamilias y cada SubFamilia tiene Clases de productos. 48. La hoja GRUPOS contiene tres listas que nos ayudarán a clasificar los productos por Familias, SubFamilia y Clases. 49. Ubíquese en la celda A2 de la hoja VENTA y escriba la siguiente fórmula: =EXTRAE(E2,1,3) Breve Función EXTRAE Explicación EXTRAE devuelve un número específico de caracteres de una cadena de texto, comenzando en la posición que especifique y en función del número de caracteres que especifique. Para nuestro caso: Se extraerán del Cod_Clase, a partir del 1er carácter, 3 caracteres. 83 Juan QUINDE LI SAY TAN

50. Copie la fórmula en el resto de la columna Cod_Familia (hasta la celda A151). 51. Ubíquese en la celda C2 y escriba la siguiente fórmula: =EXTRAE(E2,1,5) 52. Copie la fórmula en el resto de la columna Cod_SubFamilia (hasta la celda C151). 53. Cree grupo: Ubíquese en la hoja GRUPOS. 54. Seleccione desde la celda A1 hasta la B5. 55. Escriba en el cuadro de nombres: grupo_familia.

56. Seleccione desde la celda E1 hasta la F14. 57. Escriba en el cuadro de nombres: grupo_subfamilia. 58. Seleccione desde la celda I1 hasta la J32. 59. Escriba en el cuadro de nombres: grupo_clase. Nota: para administrar los grupos creados se debe acceder al Administrador de Nombres. Por ejemplo, se podrá eliminar un grupo.

60. Seleccione la celda M2. 61. Escriba en el cuadro de nombres: IGV. 62. Regrese a la hoja VENTA. 63. Aplique la función BUSCARV para relacionar el código de la Familia con su descripción. 64. Posiciónese en la celda B2. Escriba: =BUSCARV(A2,grupo_familia,2,FALSO) 65. Copie la fórmula al resto de la columna Familia. 66. Posiciónese en la celda D2. Escriba: =BUSCARV(C2,grupo_subfamilia,2,FALSO) Master Excel

84

67. Copie la fórmula al resto de la columna SubFamilia. 68. Posiciónese en la celda F2. Escribir: =BUSCARV(E2,grupo_clase,2,FALSO) 69. Copie la fórmula al resto de la columna Clase. 70. Cálculo del margen neto y el margen porcentual. Nota: El margen es la ganancia o utilidad. Ejm: Si compras un terreno a 8,000 y lo vendes a 10,000, tu margen neto es 2,000 y tu margen porcentual es 20%.

Fórmula del Margen Neto: Margen Neto = Precio Venta* – Costo de Venta* * Si el precio de venta tiene IGV, el costo también debe tenerlo. * Si el precio de venta no tiene IGV, el costo tampoco debe tenerlo.

Fórmula del Margen Porcentual: Margen Porcentual (%) = Margen Neto / Precio Venta

71. En la celda N2 de la hoja VENTA, escriba la siguiente fórmula: =L2-M2*(1+IGV) Nota: Se está utilizado IGV (nombre de grupo definido anteriormente. Ver paso 17). 72. Copie la fórmula al resto de la columna Margen_Neto. 73. Seleccione las columnas: Precio, Costo sin igvyMargen_Neto. 74. Dele formato (Estilo millares) para dar mejor presentación.

85 Juan QUINDE LI SAY TAN

75. En la celda O2, escriba la fórmula: =N2/L2 76. Copie la fórmula al resto de la columna Margen%. 77. Dele formato a la columna margen: Estilo porcentual (forma abreviada: Ctrl + Shift + %). 78. Aumente un decimal.

79. En la celda W2, escriba la fórmula. =T2*$L2 Venta_Neta = (Venta en unidades) x (precio de venta) Anclar celdas: $C$F

Para anclar celdas se utiliza el signo $. $F: Anclar columnas. $C: Anclar fila. $F$C: Ancla una celda.

80. Copie la fórmula al resto de la columna Venta_Neta_Norte. 81. Ingrese la fórmula de Venta_Neta para el SUR y para LIMA (Columnas X e Y). 82. En la celda Z2, escriba la fórmula: =W2*$O2 Margen_Neto = (Venta_Neta) x (Margen porcentual) 83. Copie la fórmula al resto de la columna Margen_Neto_Norte. 84. Ingrese la fórmula de Margen_Neto para el SUR y para LIMA (Columnas AA y AB). 85. Agregue tres columnas a la lista. - En la celda AC1, escribir: Venta_Unidades_Total - En la celda AD1, escribir: Venta_Neta_Total - En la celda AE1, escribir: Margen_Neto_Total

Master Excel

86

86. Agregue sumatorias a las nuevas columnas agregadas: - En AC2 escribir:=SUMA(T2:V2) … y copiar al resto de la columna. - En AD2 escribir: =SUMA(W2:Y2) … y copiar al resto de la columna. - En AE2 escribir: =SUMA(Z2:AB2) … y copiar al resto de la columna. 87. Revise la tabla en general. 88. Guarde el archivo.  Transformando los datos en información Filtros: Información clave y de rápido acceso. Para facilitar la visualización y el manejo de base de datos: 10. Ubíquese en la hoja Venta. 11. Reduzca el ancho de las columnas A, B, C, D, E y F. Seleccione las columnas (todas a la vez). Luego, presione AntiClic y seleccione ancho de columna (colocar Ancho = 12).

12. Inmovilice paneles: Colóquese en la celda K2 e inmovilice. Vista Inmovilizar paneles Inmovilizar paneles.

87 Juan QUINDE LI SAY TAN

13. Con el fin de ver la mayor cantidad de datos, aplique un zoom de 70% (a criterio del alumno). Zoom se encuentra en la parte inferior de la pantalla.

14. Utilizando filtros (método abreviado (Ctrl + Shift + L), obtenga el número de laptops vendidas en las tiendas: Norte: __________ Sur: __________ Lima: __________ 15. Utilizando filtros, obtenga cuál es la venta neta total (Norte+Sur+Lima) de las siguientes marcas: SONY: __________ MONARK: __________ OSTER: __________ 16. Quite los filtros (método abreviado: Ctrl + Shift + L). 17. ¿Cuál es el producto más vendido en unidades en SANTA LUCÍA? _____________________________________ 18. ¿Cuál es el producto que mayor Margen_Neto ha dejado a SANTA LUCÍA? _____________________________________ 19. Quite los filtros (método abreviado: Ctrl + Shift + L).

Master Excel

88

Primera tabla dinámica: 20. Elabore una tabla dinámica que muestra las ventas en unidades por Familia. Es decir, además del campo Familia, seleccione los siguientes campos:

-

Venta_Unidades_Norte Venta_Unidades_Sur Venta_Unidades_Lima Venta_Unidades_Total

21. El resultado es una tabla dinámica que resume la venta de las unidades vendidas por zona y por familia de productos:

Es importante mencionar que el estilo de la tabla (colores, tipo y tamaño de letra, formato de celdas, entre otras) puede realizarse: - A través de la pestaña diseño que contiene estilos predefinidos. - Manualmente, es decir, como si se tratase de una tabla de datos común.

Segunda tabla dinámica (ubíquela debajo de la primera tabla dinámica): 89 Juan QUINDE LI SAY TAN

22. Elabore una tabla dinámica que muestra la Venta_Netapor Familia. ¿Cuál es la Familia con mayor Venta_Neta? ______________________________ Tercera tabla dinámica (ubíquela debajo de la segunda tabla dinámica): 23. Elabore una tabla dinámica que muestra el Margen_Neto por Familia. ¿Cuál es la Familia con mayor Margen_Neto? ______________________________ Explore más detalles: manejo de Familias y Marcas. 33. Cree una tabla dinámica con la siguiente distribución:

Master Excel

90

34. Recuerde que el estilo de la tabla (colores, tipo y tamaño de letra, formato de celdas, entre otras) puede realizarse con estilos sugeridos por Excel o manualmente. 35. Ordenar una tabla dinámica: Seleccione una celda de la columna de la tabla dinámica que desee ordenar. Para nuestro ejemplo, seleccionar la celda D10. 36. Ordene el margen neto de mayor a menor.

37. El resultado es la tabla ordenada en orden descendente por las SubFamilias que mayor Margen_Neto han aportado a SANTA LUCÍA. Video, Cómputo y Bicicletas son las tres Familias que más han aportado al margen. 38. Agregue una fórmula a la tabla dinámica: nuevamente, colocarse en la celda D10. 39. Presione el botón Fórmula, dentro de la pestaña Opciones.

91 Juan QUINDE LI SAY TAN

40. Aparece el formulario Insertar campo calculado. 41. Ingrese el Nombre y la Fórmula: Nombre: Margen Porcentual Fórmula (para escribirla, haga uso de la lista de campos ubicada en el formulario): =Margen_Neto_Total /Venta_Neta_Total

42. Presione Aceptar. 43. Aplique formato de Porcentaje a los valores de la nueva columna. ¿Cuál es la SubFamilia con mayor Margen Porcentual? ____________________________ 44. Llega al detalle: Otra potente función de las tablas dinámicas es llegar al detalle (ver los registros) haciendo doble clic en la celda de la tabla dinámica que el usuario escoja. 45. Por ejemplo, si se quiere llegar a saber cuáles son los productos de la SubFamilia VIDEO más vendidos, se tiene que hacer doble clic en la celda C6. 46. Aparece en una nueva hoja, el listado de productos. Revísela. 47. Ordene en forma descendente por la columna AD. 48. El resultado son los productos de video ordenados en forma descendente por la columna Venta_Neta_Total.

Master Excel

92

49. Utilizando la tabla dinámica y el doble clic, determine: - El producto más vendido en unidades de la SubFamilia BICICLETA: ______________________________ -

El producto con mayor venta neta de la SubFamilia FOTOGRAFIA: ______________________________ El producto con mayor margen porcentual de la SubFamilia PLANCHAS: ______________________________

50. Agregue el campo Marca a la tabla de datos ya existente.

51. Ordene en orden descendente por la columna Venta_Neta_Total. 52. Como se aprecia, la información obtenida puede ser de gran valor para la toma de decisiones en SANTA LUCIA. Por ejemplo, se observa en la SubFamilia COMPUTO que la marca Apple es la que mayor Venta_Neta presenta, sin embargo, presenta un margen negativo (se está perdiendo dinero). 53. Como se aprecia, la tabla dinámica permite presentar la información de muchas maneras. 54. En el área Rótulos de fila, invierta SubFamilia y Marca. Es decir, coloque Marca encima de SubFamilia. 55. Utilizando el filtro de la celda A5, seleccione las marcas Sony y Samsung. 56. Haga un breve análisis de la tabla obtenida.

↘↘ 93 Juan QUINDE LI SAY TAN

6.6 EJERCICIO AGREGAR FÓRMULA (Continúa en el mismo archivo: Santa Lucía) 1. Elabore una nueva tabla dinámica que compare la Venta_Neta del NORTE y del SUR a nivel de marcas. 2. Agregue la fórmula Comparativo =Venta_Neta_Norte/Venta_Neta_Sur - 1 (no te olvides del -1 al final de la fórmula) Esta fórmula mostrará en porcentaje cuánto más (signo positivo) o menos (signo negativo) vende el Norte sobre el Sur.

¿En qué marcas le gana el SUR al NORTE? ______________________________________ ¿Cuánto más (en porcentaje) vende el NORTE sobre el SUR? ______________________________________

Master Excel

94

↘↘ 6.7 EJERCICIO ORDENAR TABLA 1. Utilizando la data de la hoja VENTA, prepare una nueva tabla dinámica: - Diseño a criterio del alumno. - Deberá incluir una fórmula. 2. Utilizando la data de la hoja VENTA, preparar una nueva tabla dinámica con la siguiente distribución: - Dé formato de número (coma decimal) utilizado la configuración de campo de valor.

-

Utilice la pestaña Diseño para dar forma a la tabla.

-

Ordene la tabla por el campo Venta_Neta_Total. En horizontal En vertical.

95 Juan QUINDE LI SAY TAN

Parte III

Cuadros de Mando y KPI’con Excel (Dashboard) En la presente sección se integra herramientas de Excel y funciones para obtener cuadros de mando y soluciones gráficas que ayudan a visualizar indicadores e información para la toma de decisiones.

Introducción a Dashboard Conceptos Generales.

Soluciones con Dashboard Conjunto de soluciones utilizando indicadores y presentándolos de forma gráfica.

Master Excel

96

*08 INTRODUCCIÓN () () () ()

A DASHBOARD

97 Juan QUINDE LI SAY TAN

↙↙

8.1 ¿QUÉ ES UN DASHBOAD? Un dashboard debe mostrar de forma gráfica datos relevantes (críticos o claves) que ayude a conocer de manera práctica la situación de un área, empresa o proyecto. Haciendo una similitud con el cuerpo humano, un dato relevante (pero no completo) del estado del cuerpo humano es su temperatura corporal. Si es en promedio 37°C, se podría decir que todo está dentro de lo normal. Si se acerca a 40°, entonces hay problemas. De igual forma con las empresas, se debe buscar indicadores que brinden una idea general del desempeño. Si uno de los indicadores no está dentro de lo normal, entonces se va al detalle para descubrir la(s) causa(s).

Master Excel

98

-

8.2 FUNCIÓN INDICE Utilidad Acceso Ejemplo 1

Devuelve un valor o la referencia a un valor en una tabla o rango. Fórmulas  Insertar función  Búsqueda  [función] 1. Abra un archivo nuevo. 2. Ingrese el siguiente cuadro: Id

Producto 1 Martillo

Cantidad

Precio

242

35

2 Destornillador

32

19

3 Taladro

23

300

4 Clavos

102

3

3. Seleccione la data ingresada y cree el grupo grupo1. 4. Ingrese la siguiente fórmula en cualquier celda libre: =INDICE(grupo1,2,4) El resultado debe ser 35. Es decir, el valor de la celda ubicada en la fila 2 y la columna 4 del grupo1. 5. Ingrese la fórmula: =INDICE(grupo1,5,2) El resultado debe ser Clavos. 6. En una hoja nueva escribe: 10 20 30 40 7. Seleccione las celdas y cree un grupo: GRUPO2. 8. Ingrese la fórmula: =INDICE(GRUPO2,3) El resultado debe ser 30. Ejercicio 1

99 Juan QUINDE LI SAY TAN

1. Cree dos grupos (GRUPO10 y GRUPO20). Cada grupo debe contener 3 filas y 3 columnas. Todos los valores de los grupos deben ser números. 2. En una celda nueva, sume un valor del GRUPO10 y un valor del GRUPO20 utilizando la función INDICE.

-

8.3 FUNCIÓN INDIRECTO Utilidad Acceso Ejemplo 1

Devuelve un valor o la referencia a un valor en una tabla o rango. Fórmulas  Insertar función  Búsqueda  [función] 1. Abra un archivo nuevo. 2. Ingrese a partir de la celda A1 el siguiente cuadro: Datos Valores B2 1.333 B3 45 Paz 10 5 62 3. Seleccione la celda B4. Crea el grupo PAZ. 4. Seleccione la celda A7 e ingresa la fórmula: =INDIRECTO(A2) Analiza el resultado. 5. Seleccione la celda A8 e ingrese la fórmula: =INDIRECTO(A3) Analice el resultado. 6. Seleccione la celda A9 e ingrese la fórmula: =INDIRECTO(A4) Analice el resultado. 7. Seleccione la celda A19 e ingresa la fórmula: =INDIRECTO("B"&A5) Analice el resultado.

Ejemplo 2

1. Abra un archivo nuevo. 2. A partir de la celda A1, escriba lo siguiente: País

PERU

ARGENTINA

ECUADOR

Ciudad

LIMA

BUENOS AIRES

GUAYAQUIL

PIURA

MENDOZA

QUITO

TRUJILLO

CÓRDOBA

Master Excel

100

3. Sombree las celdas B1 y B2 de las color amarillo. 4. Seleccione los países (celdas D1, E1 y F1) y cree el grupo: PAISES. 5. Seleccione las ciudades de Perú (celdas D2, D3 y D4) y cree el grupo: PERU. 6. Seleccione las ciudades de Argentina (celdas E2, E3 y E4) y cree el grupo: ARGENTINA. 7. Seleccione las ciudades de Ecuador (celdas F2 y F3) y cree el grupo: ECUADOR. 8. Seleccione la celda B1. 9. Seleccione Validación de datos: Datos  Herramientas de Datos  Validación de Datos. 10. En Permitir, seleccione Lista. 11. En Origen, escriba: =PAISES 12. Presione Aceptar. 13. Usted podrá seleccionar un país en la celda B1. Haga la prueba. 14. Seleccione la celda B2. 15. Seleccione Validación de datos: Datos  Herramientas de Datos  Validación de Datos. 16. En Permitir, seleccione Lista. 17. En Origen, escriba: =INDIRECTO(B1) 18. Presione Aceptar. Resultado: Cuando seleccione un país en la celda B1 aparecerá la lista de ciudades en la celda B2. Ejercicio 1

1. Realice las operaciones del ejemplo 2 con la siguiente data. PAIS EQUIPOS

101 Juan QUINDE LI SAY TAN

ESPAÑA REAL MADRID BARCELONA VALENCIA

ITALIA

INGLATERRA

JUVENTUS MILAN LAZIO

MANCHESTER CITY MANCHESTER UNITED CHELSEA

8.4 CASO A INDICE – INDIRECTO – COLUMNA – CUADRO COMBINADO El ejercicio consiste en relacionar la información de cuatro hojas de Excel y presentar el resumen en una hoja. El objetivo es conocer la interacción entre las funciones INDICE, INDIRECTO y un cuadro combinado de la ficha Programador. 1. Abra un archivo nuevo. 2. Cree cinco hojas: PRINCIPAL, PIU, TRU, CHI y AQP. 3. En la hoja PIU, a partir de la celda A1, escriba lo siguiente: PIURA

10 20 30 40

4. 5. 6. 7.

Venta este año 400 312 243 102

Venta año pasado 309 290 287 99

Incr%

En la celda D4, escriba la fórmula del incremento: =(B4-C4)/C4. Copie la fórmula del incremento al resto de la columna. Seleccione desde la celda A4 hasta la celda D7 y cree el grupo PIU. En la hoja TRU, escriba a partir de la celda A1: TRUJILLO

10 20 30 40

Venta este año 304 292 492 592

Venta año pasado 300 200 302 304

Incr%

8. Ingrese la fórmula del incremento. 9. Seleccione desde la celda A4 hasta la celda D7 y cree el grupo TRU.

Master Excel

102

10. En la hoja CHI, escriba a partir de la celda A1: CHICLAYO

10 20 30 40

Venta este año 230 190 321 345

Venta año pasado 200 180 300 300

Incr%

11. Ingrese la fórmula del incremento. 12. Seleccione desde la celda A4 hasta la celda D7 y crea el grupo CHI. 13. En la hoja AQP, escriba a partir de la celda A1: AREQUIPA

10 20 30 40

Venta este año 230 320 430 433

Venta año pasado 210 310 230 380

Incr%

14. Ingrese la fórmula del incremento. 15. Seleccione desde la celda A4 hasta la celda D7 y crea el grupo AQP. 16. En la hoja PRINCIPAL, ingrese, a partir de la celda A1, lo siguiente: RESUMEN Jerarquía

Venta este año PIU TRU CHI AQP

103 Juan QUINDE LI SAY TAN

Venta año pasado

Incr%

17. En la misma hoja PRINCIPAL, ingrese lo siguiente a partir de la celda G6: 10 20 30 40

10-video 20-audio 30-peds 40-fotografia

18. Seleccione las celdas G6 a G9. Cree un grupo llamado INDEX1. 19. Seleccione las celdas H6 a H9. Cree un grupo llamado INDEX2.

CUADRO COMBINADO Ahora vamos a mostrar la ficha del Programador. 20. En el botón de Excel seleccione Opciones de Excel (ver imagen):

21. Seleccione: Mostrar ficha Programador en la cinta de opciones. 22. Verifique que la ficha aparece en el Menú (ver imagen): Master Excel

104

23. Dentro de la ficha del Programador, haga clic en Insertar. 24. Seleccione Cuadro Combinado (segundo botón). 25. Inserte el objeto (dibujando un rectángulo) en la celda B2. 26. Dele clic derecho al Cuadro Combinado. 27. Seleccione Formato de Control. 28. En la pestaña Control, ingrese los siguiente valores:

29. Presione Aceptar. 30. Pruebe la carga de la data haciendo clic en la flecha del Cuadro Combinado. 31. En la celda B6, ingrese la siguiente fórmula: =BUSCARV(INDICE(index1,$G$2),INDIRECTO($A6),COLUMNA(B$1),FALSO) 32. Copie la fórmula desde la celda B6 hasta la celda D9. 33. Marque desde la celda A5 hasta la celda C9 e ingrese un gráfico. 34. Seleccione las categorías utilizando el Cuadro Combinado. Revise lo realizado.

105 Juan QUINDE LI SAY TAN

8.6 CASO C INGRESOS Y SALIDAS DE MERCADERÍA El ejercicio permitirá realizar ingresos y salidas de stock. Además, mostrará en una hoja resumen el stock actual. 1. Cree un nuevo documento. 2. A partir de la celda A1, escriba: Fecha

Producto

descripción

ingreso

salida

3. A partir de la celda J1, escriba: Código 110 120 130 140

Producto 110-Cuadernos 120-Libros 130-Lapiceros 140-Lápices

4. Seleccione las columnas B, C, D y E. Cree el grupo: gRegistros 5. Seleccione la celda D. Cree el grupo: gIngresos 6. Seleccione la celda E. Cree el grupo: gSalidas 7. Seleccione desde la celda J2 hasta la celda J8. Cree el grupo: gProductos1 8. Seleccione desde la celda J2 hasta la celda K8. Cree el grupo: gProductos2 9. Seleccione la celda B2. 10. Seleccione Validación de datos: Datos  Herramientas de Datos  Validación de Datos. 11. En Permitir, seleccione Lista. 12. En Origen, escriba: =gProductos1 13. Presione Aceptar. 14. Copie el contenido de la celda B2 hasta la celda B100. 15. Seleccione la celda C2. Escriba la siguiente fórmula: =SI(ESERROR(BUSCARV(B2,gProductos2,2,FALSO))," ",BUSCARV(B2, gProductos2,2,FALSO)).

Master Excel

106

16. Copie el contenido de la celda C2 hasta la celda C100. 17. Ingrese el siguiente contenido a partir de la celda A2 (para llenar la columna B utilice la lista predefinida): 25/01/2012 25/01/2012 25/01/2012 25/01/2012 02/02/2012 02/02/2012 02/02/2012 02/02/2012 03/02/2012 03/02/2012

110 120 130 140 110 120 130 110 110 120

5000 4800 12000 800 1000 800 2000 400 100 500

18. Nombre a la hoja como: Registro 19. En una hoja nueva, a partir de la celda A1, ingrese lo siguiente: Codigo 110 120 130 140

Producto Cuadernos Libros Lapiceros Lápices

Stock Actual

20. En la celda C2, ingrese la función: =SUMAR.SI(gRegistros,A2,gIngresos)-SUMAR.SI(gRegistros,A2, gSalidas) 21. Copie el contenido de la celda C2 hasta la celda C5. 22. Nombre a la hoja como: Resumen. Tarea: 1. En un documento nuevo elabore un registro de entradas y salidas para los productos: Código

107 Juan QUINDE LI SAY TAN

Juego de mesa 10 10-Monopolio 20 20-Domino 30 30-Ajedrez

*09 SOLUCIONES

() () () ()

↙↙

CON DASHBOARD

Master Excel

108

9.1 CASO INFOMARCA 1. Abra el archivo KPI_300_01_InfoMarca. 2. Ordene la lista (este ordenamiento es muy importante): - Primero, por la columna G en orden descendente. - Luego, por la columna B en orden ascendente. 3. Ingrese en la celda A2 el valor 2. 4. Rellene la columna A en forma correlativa hasta el final. 5. Seleccione desde la celda B2 hasta el final de la columna B. 6. Cree un grupo con las celdas seleccionadas. Nombra al grupo como: grupo_data 7. Inserte una hoja. Nómbrela Input. 8. Copie los títulos de las columnas de la hoja Data. Péguelos a partir de la celda A5 de la hoja Input. 9. En la celda B1 de la hoja Input, escriba J0101. 10. En la fila 4, escriba los números del 2 al 7 (como se muestra a continuación):

11. Escriba en la celda A6, la siguiente fórmula: =COINCIDIR(B1,grupo_data,0)+1

109 Juan QUINDE LI SAY TAN

12. Escribe, en la celda A7, lo siguiente: =A6+1 13. Copie el contenido de la celda A7 hasta la celda A300. 14. Escriba, en la celda B6, la fórmula: =INDIRECTO(DIRECCION($A6,B$4,,,"data")) 15. Copie la fórmula de la celda B6 hasta la celda G6. 16. Copie la fórmula de la celda B6 hasta la celda B300. 17. Escriba en la celda C7 la fórmula: =SI(C6=0,0,SI($B7$B6,0,INDIRECTO(DIRECCION($A7,C$4,,,"data")))) 18. Copie la fórmula de la celda C7 hasta la celda G7. 19. Copie el contenido de las celdas B7 hasta G7 hasta la fila 300. 20. Cambie el contenido de la celda B1 por J0201. REVISE LAS FÓRMULAS INGRESADAS 21. Inserte una nueva hoja llamada InfoMarca. 22. Elabore la siguiente plantilla. El objetivo es que al seleccionar una familia en el cuadro combinado aparezcan los diez primeros productos con mayor Venta Neta.

Master Excel

110

23. Para insertar el cuadro combinado, realice lo siguiente: - Verifique que la ficha Programador o Desarrollador esté habilitada (Archivo  Opciones  Personalizar cinta de opciones). - Presione en Programador/Desarrollador  Insertar  Cuadro combinado. - Dibuje en la hoja un rectángulo pequeño (del tamaño de dos celdas en horizontal).

24. En el cuadro combinado deben aparecer las 138 familias. Para ello, realice lo siguiente: - Ubíquese en la hoja data. - Seleccione desde la celda B2 hasta la celda B5886. - Copiar (es decir, presionar Ctrl + C). - Seleccione la celda L2. - Pegar (es decir, presione Ctrl + V). - Presione Datos  Quitar duplicados - Presione Aceptar. - Presione nuevamente Aceptar. - La función quitar duplicados ha reducido la lista a 138 familias (verifíquelo). - Marque desde la celda L2 hasta la celda L139. - Nombrar el grupo como: Grupo_familias. - Ubíquese en la hoja Infomarca. - Haga clic derecho en el cuadro combinado y elija Formato de control. - En rango de entrada, escriba el nombre del grupo: Grupo_familias 111 Juan QUINDE LI SAY TAN

-

En vincular con la celda, escriba: M2 Pruebe el control eligiendo diferentes familias y observando los valores que toma la celda M2 (esta celda nos ayudará a relacionar los datos). - Seleccione la celda M2. - Nómbrela como grupo: Posicion1. 25. Posiciónese en la hoja Input. 26. Reemplace el contenido de la celda B1 por lo siguiente: =INDICE(grupo_familias,posicion1) 27. Presione Enter. 28. Ubíquese en la hoja Infomarca. 29. Seleccione la celda B6. 30. Escriba: =Input!B6. 31. Copie el contenido de la celda B6 hasta la celda G6. 32. Copie el contenido de las celdas B6 hasta G6 hasta la fila 15. 33. Pruebe el sistema seleccionado en el Cuadro combinado diferentes familias.

34. Guarde el documento.

Master Excel

112

9.3 INSTRUMENTO VELOCÍMETRO El objetivo será elaborar una gráfica parecida a la siguiente imagen, donde en función de unos parámetros nos indicará un valor en una escala determinada. La forma del gráfico es de un velocímetro (similar al velocímetro de un automóvil). El velocímetro se podrá utilizar para mostrar rápidamente múltiples indicadores claves de desempeño (KPI). Por ejemplo: -

Cumplimiento de ventas. Nivel de costos. Logro de costos. Rotación de inventarios. Nivel de producción. Nivel de satisfacción de clientes. Nivel de satisfacción del personal. Evaluaciones de desempeño del personal. Entre otros.

Presentación del ejercicio: A continuación, se medirá el porcentaje de trabajadores capacitados por fábrica y área de trabajo. 1. Cree un nuevo archivo en Excel. 2. Guarde el archivo con el nombre: KPI_600_01_Velocimetro. 3. A partir de la celda A1, escriba: Tipo de Producto Tipo A Tipo B Tipo C TOTAL

113 Juan QUINDE LI SAY TAN

Margen Neto 34,020.00 12,033.00 5,603.00 =SUMA(B2:B4)

% =B2/$B$5 =B3/$B$5 =B4/$B$5 =B5/$B$5

4. A partir de la celda A8, escriba: Parámetros del Velocímetro Títulos Segmento 0% 25% 50% 75% 100%

0.25 0.25 0.25 0.25 1

5. A partir de la celda A16, escribir: Grados

=+(C2-A10)/(A14-A10)*PI()

Importante: La fórmula escrita en la celda B16 es el resultado de una regla de 3 simple (aplicada en Trigonometría. No es necesario comprenderla exhaustivamente): Si el 100% es al número PI (π = 3.14159..); entonces, el porcentaje indicado en la celda C2 es al resultado mostrado en la celda B16. 6. A partir de la celda A18 escriba: Puntos Punto 1 Punto 2

X 0 =-COS(B16)

Y 0 =SENO(B16)

 El instrumento velocímetro mostrará la participación porcentual de los clientes tipo A, es decir, el valor indicado en la celda C2. 7. Seleccione desde la celda B9 hasta la celda B14.

Master Excel

114

8. Inserte un gráfico tipo anillo: Insertar  Gráfico  Gráfico tipo anillo

Segmento

1

2

3

4

5

9. Seleccione el gráfico y gírelo 270°. La idea es que los cuatro segmentos pequeños queden en la parte superior. Seleccionar gráfico  Clic secundario en el anillo  Formato de serie de datos  Opciones de Serie  Ángulo del primer sector  270° (Algunas versiones de Excel tienen otra forma de girar el gráfico). El resultado es el siguiente:

Segmento

1

2

3

4

5

10. Seleccione el área más grande del gráfico (el que ocupa la mitad del círculo) para hacer que no se vea.

115 Juan QUINDE LI SAY TAN

11. En color de relleno, elija “Sin relleno” Inicio  Fuente  Relleno  Sin relleno 12. Seleccione la leyenda del gráfico y bórrela con el botón del teclado “Suprimir” 13. Cambie el título al gráfico: “Producto Tipo A: Margen Neto” 14. El resultado es el siguiente:

 A continuación, utilizaremos otro tipo de gráfico para que aparezca la flecha que indicará el valor. 15. Haga clic derecho en el gráfico. 16. Elija: Seleccione Origen de Datos o Seleccionar datos. 17. En Series elija: Agregar. 18. En nombre de la serie, elija la celda A18. 19. En Valores de la serie, marque desde la celda B20 hasta la C20. 20. Presione Aceptar. 21. Seleccione el anillo exterior. 22. Cambie el gráfico a “Gráfico de dispersión”. Insertar  Gráfico  Elegir gráfico de dispersión 23. Seleccione un punto del gráfico de dispersión. 24. Haga clic derecho. 25. Elija Seleccionar origen de datos o Seleccionar datos. 26. Elija la serie Puntos. 27. Presionar en Modificar. 28. En Valores X de la serie, elija desde la celda B19 hasta la B20. Master Excel

116

29. En Valores Y de la serie, elija desde la celda C19 hasta la C20.

30. Presione Aceptar. 31. Presione nuevamente Aceptar. 32. Haga clic derecho el Eje Y del gráfico. 33. Seleccione Dar formato al eje 34. En Límite mínimo, ingrese el valor: -1 35. En Límite máximo, ingrese el valor: 1 36. En Eje horizontal cruza (valor del eje), ingrese el valor: 0 37. Haga clic derecho el Eje X del gráfico. 38. Seleccione Dar formato al eje 39. En Límite mínimo, ingresar el valor: -1 40. En Límite máximo, ingresar el valor: 1 41. En Eje vertical cruza (valor del eje), ingrese el valor: 0 42. Borre los Ejes X e Y (seleccione cada eje y bórrelo con el botón del teclado “Suprimir”). 43. Borre las líneas horizontales y verticales (seleccione una línea y bórrela con el botón del teclado “Suprimir”). 44. Para dar formato a la aguja de nuestro indicador, que será el vector que nos unirá los puntos, realice lo siguiente: o Haga clic derecho en uno de los puntos. o Seleccione Formato de serie de datos. o En línea, seleccione Línea Sólida. o Dar un ancho de 3 ptos. o En opciones de marcador, elija: ninguno. 117 Juan QUINDE LI SAY TAN

o También se puede cambiar el tipo de terminación de la línea por flecha. 45. También se puede agregar la etiqueta al gráfico de tal manera que aparezca el valor (en este caso 66%) en la parte inferior. Resultado final (imagen referencial):

Master Excel

118

9.5 INSTRUMENTO RADAR 1. Abra el archivo KPI_600_03_Radar. 2. Escriba en la celda K15: =(10-CONTAR.BLANCO(K4:K14))*K4 3. Copie el contenido de la celda K15 desde la celda L15 hasta la celda T15. 4. Verifique que las fórmulas se hayan copiado correctamente en la tabla: ORGANIZACIÓN 5. Escriba en la celda Q16: =SUMA(K15:T15) 6. Escriba en la celda K34: =(10-CONTAR.BLANCO(K23:K33))*K23 7. Copie el contenido de la celda K34 desde la celda L34 hasta la celda T34. 8. Verifique que las fórmulas se hayan copiado correctamente en la tabla: ADMINISTRACIÓN 9. Escriba en la celda Q35: =SUMA(K34:T34) 10. Repita los pasos para el resto de funciones: o PERSONAL o EJECUCIÓN o SUPERVISIÓN DE PRODUCTOS o ABASTECIMIENTO 11. Escriba en la celda C5: 12. Escriba en la celda D5: 13. Escriba en la celda E5: 14. Escriba en la celda F5: 15. Escriba en la celda G5: 16. Escriba en la celda H5:

=Q16 =Q35 =Q54 =Q73 =Q96 =Q115

17. Utilizando el rango de datos desde la C4 hasta la H7, inserte un gráfico tipo radial. 18. Dele formato al gráfico de tal manera que sea fácil de leer. Se recomienda cambiar los colores de las líneas del gráfico con la finalidad que se relacionen con el %Logrado, %Promedio y %Meta. 119 Juan QUINDE LI SAY TAN

Imagen referencial:

19. Guarde el archivo.

Master Excel

120

9.7 INSTRUMENTO GRÁFICO y CONTROLES DE FORMULARIO 1. Abra el archivo KPI_600_04_Grafico y controles. 2. Ubíquese en la hoja data. 3. Seleccione desde la celda A8 hasta la M15. 4. Nombre al grupo como PRODUCCION. 5. En la misma hoja, cree grupos para VENTA, UTILIDAD y PERSONAL. 6. Ubíquese en la hoja grupos. 7. Seleccione desde la celda A2 hasta la celda B5. 8. Nombre al grupo como INDICADOR. 9. Seleccione desde la celda E2 hasta la celda E9. 10. Nombre al grupo como PAISES. 11. Ubíquese en la hoja info. 12. Utilizando la ficha Programador / Desarrollador, ingrese los siguientes controles:

13. Haga clic derecho en el control opción Producción. 14. Seleccione Formato de control

121 Juan QUINDE LI SAY TAN

15. En el cuadro vincular con la celda, elegir la celda N1. 16. Presione Aceptar. 17. Escriba en la celda O1 lo siguiente: =BUSCARV(N1,INDICADOR,2,FALSO) 18. Pruebe los controles de opciones (observe cómo cambia el valor de N1 cada vez que se elige una nueva opción). 19. Haga clic derecho en el primer cuadro combinado. 20. Elija la opción Formato de control. 21. Ingrese lo siguiente:

22. Haga clic derecho en el segundo cuadro combinado. 23. Elija la opción Formato de control. 24. Ingrese lo siguiente:

25. En la hoja info, escriba, a partir de la celda B22, lo siguiente: ene

feb

mar

abr

may

jun

jul

ago

sep

oct

nov

dic

Master Excel

122

26. En la celda A23, escriba: =INDICE(INDIRECTO($O$1),$N$2,COLUMNA()) 27. Copie el contenido de la celda A23 hasta la celda M23. 28. En la celda A24 escriba: =INDICE(INDIRECTO($O$1),$N$3,COLUMNA()) 29. Copie el contenido de la celda A24 hasta la celda M24. 30. Seleccione desde la celda A22 hasta la celda M24. 31. Inserte un gráfico tipo línea. 32. Pruebe el sistema. 33. Guarde el archivo. CREAR UNA MACRO 1. Inserte un control de formulario tipo Boton.

2. Cambie el nombre del botón a Gráfico tipo barra. 3. Seleccione la celda A1. 4. Presione: Ficha Programador/Desarrollador  Grabar macro 5. Presione Aceptar 123 Juan QUINDE LI SAY TAN

6. Seleccione el gráfico. 7. Cambie de tipo de gráfico: Insertar  Gráfico tipo barra 8. Seleccione la celda A1. 9. Presione: Ficha Programador/Desarrollador  Detener macro 10. Dele clic derecho al botón insertado. 11. Seleccione Asignar macro.

12. Elija la macro creada. 13. Presione Aceptar 14. Seleccione la celda A1. 15. Cambie el tipo de gráfico a lineal. 16. Pruebe el botón. 17. EJERCICIO: o Inserte un segundo botón. o Cree una macro que cambie el tipo de gráfico a lineal. o Asigne la nueva macro al segundo botón. o Inserte un tercer botón. o Cree una macro que cambie el tipo de gráfico a un tipo de su preferencia. o Asigne la nueva macro al tercer botón.

18. Guarde el archivo con la extensión: Libro de Excel habilitado para macro

Master Excel

124

ANEXO

FÓRMULAS Y FUNCIONES

125 Juan QUINDE LI SAY TAN

Ficha Fórmulas / Biblioteca de funciones (figura 1)

Datos importantes: - Las funciones son programas (líneas de código pregrabados) que cumplen con una determinada acción. - En general, una función recibe datos y arroja un resultado. Por ejemplo: =SUMA(A1:A2) Recibe dos datos y arroja la suma de ambos. - Excel 2013 cuenta con más 400 funciones. - Cuenta con funciones ocultas, por ejemplo:

=SIFECHA(fecha_anterior; fecha_posterior; unidad_de_tiempo) =SIFECHA(A1,A2,"d") =SIFECHA(A1,A2,"m") =SIFECHA(A1,A2,"a")

 Número de días entre dos fechas  Número de meses entre dos fechas  Número de años entre dos fechas

- Se clasifican según la figura 1. - En el presente manual se revisarán algunas funciones de los siguientes grupos de funciones:

DE FECHA Y HORA

MATEMÁTICAS

DE BASE DE DATOS

ESTADÍSTICAS

DE TEXTO

DE BÚSQUEDA Y REFERENCIA

LÓGICAS

Master Excel

126

↘↘ 1. FUNCIONES DE FECHA Y HORA FUNCIONES HOY – AHORA – DIA – MES - AÑO Utilidad

Acceso Ejemplo 1

Estas funciones se emplean para manipular datos ingresados como números en serie en los formatos permisibles. Dichas funciones permitirán al usuario obtener datos específicos, e inclusive realizar cálculos utilizando los valores de fecha y hora. Excel considera a las fechas como valores numéricos. Estos valores están entre 1, el cual es considerado como 1 de enero de 1900, al 2958465, correspondiente al 31 de diciembre de 9999. Así, el número 5 corresponde al 5 de enero de 1900 y el 32, al 1 de febrero de 1900. Fórmulas  Insertar función  FECHA Y HORA  [función] 1. Abra un archivo nuevo. 2. Escriba en la celda A3: Fecha actual 3. En la celda D3, escriba la fórmula: =HOY() 4. Escriba en la celda A5: Fecha y hora actual 5. En la celda D5, escriba la fórmula: =AHORA() 6. Escriba en la celda A7: Día del mes 7. En la celda D7, escriba la fórmula: =DIA(D3) 8. Escriba en la celda A9: Mes del año 9. En la celda D9, escriba la fórmula: =MES(D3) 10. Escriba en la celda A11: Año 11. En la celda D11, escriba la fórmula: =AÑO(D3) 12. En la celda G3, escriba: Días para año nuevo

127 Juan QUINDE LI SAY TAN

-

13. En la celda J2, escriba: 31/12/2013 14. En la celda J3, ingrese la fórmula: =J2-D3 15. En la celda G7, escriba: Días antes del pago 16. En la celda J6, escriba: 20 17. En la celda J7, ingrese la fórmula:=J6-D7 18. A partir de la celda M3, escriba: 1 Enero 2 Febrero 3 Marzo 4 Abril 5 Mayo 6 Junio 7 Julio 8 Agosto 9 Septiembre 10 Octubre 11 Noviembre 12 Diciembre

19. Marque con el mouse desde la celda M3 hasta la N14. 20. Asigne el nombre el rango: Meses 21. En la celda D9, ingrese la fórmula: =BUSCARV(C9,meses,2,FALSO) 22. En la celda A15, ingrese la fórmula: =CONCATENAR("Es el mes de ",D9," del año ",C11) 23. Guarde el archivo. Ejercicio 1

Investigue (Utilizando F1)

1. Calcule cuántos días faltan oficialmente para la próxima navidad. 2. Calcule cuántos días faltan para su próximo cumpleaños. La función FECHA.MES

Master Excel

128

FUNCIONES DIASEM – NUM.DE.SEMANA Acceso Ejemplo 1

Fórmulas  Insertar función  FECHA Y HORA  [función] 1. Abra un archivo nuevo. 2. En la celda B3, escriba la fecha de hoy. Es decir, escriba: =HOY() 3. En la celda C3, escriba: =DIASEM(B3) 4. El resultado indica el número de día correspondiente a la fecha de hoy. Para Excel, el día domingo es el primer día de la semana. 5. En la celda C4, escriba: =DIASEM(B3,2) 6. El resultado considere el primer día de la semana al lunes. Ello se consigue agregando el parámetro 2 a la función. 7. ¿Qué sucede si en lugar del parámetro 2 se indica 3? Averígüelo con la Ayuda de Excel (Presione F1). 8. En la celda B10, ingrese: 31/12. 9. En la celda C10, escriba la función: =NUM.DE.SEMANA(B10) 10. El resultado indica el número de la semana del año al cual pertenece la fecha indicada.

Ejercicio 2

129 Juan QUINDE LI SAY TAN

1. Abra el archivo KPI_500_01_fechas1. 2. Complete las columnas.

-

-

EJERCICIO COSTO DE ALMACENAJE DÍAS PARA EL PAGO Ejercicio 1

1. Abra el archivo KPI_500_02_Costo de Almacenamiento 2. Inserte en la fecha B3 la fórmula: =HOY() 3. Escriba, en la celda B5, el número 5 (representa 5 metros cúbicos). 4. Inserte, en la celda B7, una fecha anterior a diez días a la fecha de hoy. 5. Escriba en la celda B9 la fórmula: =B3-B7 6. Escriba en la celda B11 la fórmula: =SI(B9