Excel Avanzado

Guia de Excel Avanzado

Calle Coromoto, Diagonal al C.C La Fuente Tlf: 0264-2411155-2417282

Excel Avanzado. Tabla de Contenido

o Graficación y Correlación de datos científicos o Tablas y Gráficos Dinámicos. o Análisis de información. 

Ordenación y Resumen de Datos (Subtotales).



Ordenación y Resumen de Datos (Sumar Si).



Alternativas (Escenarios).



Análisis de Sensibilidad de una y dos variables (Tablas).



Búsqueda de Objetivo con una variable (Solver).



Problemas de Optimación (Programación lineal).

o Análisis de datos o Macros. o Formularios.

CENIBIT

Página 2

Excel Avanzado. Graficación y correlación de datos científicos Si bien es cierto que una de las herramientas más poderosas del Excel es la creación de gráficos, sin embargo, estos suelen complicarse cuando los datos no siguen un patrón lineal, por ejemplo la siguiente tabla produce el grafico que le acompaña: 30 25 20 15 Tiempo 10

Velocidad Aceleración

5 0 1

2

3

4

5

6

-5 -10

Pero esta plagado de imperfecciones e inexactitudes, las cuales van a ser corregidas a continuación. Como norma para datos científicos,

asumiendo

que

son

estos

todos

aquellos cuyos ejes de referencia

no

son

lineales

y

pueden

disponer

de

escalas

individuales

e

independientes, seleccione el gráfico de Dispersión XY que se muestra en el grafico.

CENIBIT

Página 3

Excel Avanzado. Ahora compare el gráfico de 30

barras precedente con el gráfico

25

de

20

correlación

que

sigue

a

continuación, observe los ejes

15 Velocidad

10

de referencia en ambos casos:

Aceleración

5

Es

0 0

2

4

6

8

10

12

14

16

18

20

posible

en

este

punto

ubicarse sobre cualquiera de las

-5 -10

2

líneas,

presionar

el

botón

secundario y seleccionar la opción formato de la serie de datos, en esta selecciona la opción Eje y Activar eje secundario. Observe el resultado, ahora

30

cada eje de referencia tiene su

25

propio

de

20

referencia, cada uno con su

15

eje

auxiliar

8 6 4 2 0

escala adecuada a los valores

10

que representa, inclusive con

5

signos propios para distinguir

0

Velocidad Aceleración

-2 -4 -6 0

5

10

15

20

cada representación.

Tablas y Gráficos Dinámicos Esta opción aparece dada la necesidad de crear hojas de cálculo, donde se pueda ordenar, filtrar y reordenar de forma dinámica la data con la finalidad de poder destacar algún dato o arreglo de estos en específico.

Tipee una tabla

análoga a la siguiente en una hoja de cálculo, debe tener al menos unas 40 líneas de datos.

CENIBIT

Página 4

Excel Avanzado. Mes Enero … Diciembre…

Semana 1 … 52

Día/Semana Lunes … Viernes

Día 1 … 31

Hora Ventas 9:00 147 … … 17:50 200

Ahora sombree la región que abarca los datos (A1:FXXX), y selecciones del cuadro de dialogo la opción lista o base de datos de Excel y luego Seleccione Tabla dinámica y presione Finalizar, observe la resultante:

El área en cuestión se caracteriza por tener cuatro (4) regiones: 

Campos de página.



Campos de Columna.



Campos de fila.



Datos.

Ahora es simplemente cuestión de arrastrar los campos necesarios a los encabezados y área de datos, coloque en campos de fila (Semana y día), en los campos de columna coloque (Hora), en el campo de datos coloque (Ventas) y en campo de página coloque (Día/Semana y Mes) y observe la tabla resultante:

CENIBIT

Página 5

Excel Avanzado.

Marque los botones desplegables y haga diversas selecciones y observe los resultados. La presente tabla resume las operaciones de venta realizadas por la tienda en el lapso de un año, mes a mes y día a día, incluso hora por hora, los botones desplegables sirven para que el usuario pueda configurar la tabla según sus necesidades. La edición es igual de sencilla, observa la barra de herramientas de tablas y gráficos dinámicos: Botón Opciones de Tabla Dar formato al informe. Asistente para gráficos. Ocultar Detalles. Mostrar Detalles. Actualizar Campos. Incluir los elementos ocultos en los totales. Mostar siempre los elementos. Configurar Campo. Ocultar lista de campos.

CENIBIT

Página 6

Ahora intente a partir de los mismos datos crear un gráfico dinámico, al presionar el botón Finalizar obtendrá: Observe la analogía estructural con

la

tabla

dinámica,

simplemente arrastre los campos que necesite a las regiones: 

Campos de página.



Campos de Serie.



Campos de Categoría.



Datos.

Coloque

en

campos

de

serie

(Semana y día), en los campos de categoría coloque (Hora), en el campo de datos coloque (Ventas) y en campo de página coloque (Día/Semana y Mes) y observe la gráfica resultante: Día/Semana (Todas) Mes (Todas)

7000

Suma de Ventas

6000 Día Semana 14 - 2 13 - 2 12 - 2 11 - 2 10 - 2 9-2 8-2 7-1 6-1 5-1 4-1 3-1 2-1 1-1

5000

4000

3000

2000

1000

0 9

10

11

12

13

14

15 Hora

16

17

18

19

20

Excel Avanzado. Marque los botones desplegables y haga diversas selecciones y observe los resultados.

Análisis de Datos Excel es una herramienta para análisis de información, existe un sin numero de posibles aplicaciones, a saber análisis estadísticos de datos, análisis financiero, Aplicaciones para ingeniería, consulta de bases de datos, entre otras. Acá

se

resumen las capacidades más importantes que sirven de base a estas opciones.

Ordenación y Resumen de Datos (Subtotales) Esta es una de las herramientas más poderosas, ya que permite sin utilizar filtros categorizar electos y decidir como se van totalizar, por ejemplo la siguiente tabla:

Datos > Subtotales…

CENIBIT

Página 8

Excel Avanzado.

Para cambio de Relleno: Categoría. Usar función: Suma. Agregar Subtotal a: Precio y Unidades vendidas Observe el resultado.

CENIBIT

Página 9

Excel Avanzado. Ordenación y Resumen de Datos (Sumar Si) Esta es una de las herramientas aun más poderosas que la anterior, ya que permite discriminar dentro de los rangos, al punto que sobre la misma tabla es posible totalizar si es una herramienta y precio > 12,95 por ejemplo la siguiente tabla:

Herramientas > Asistente > Suma Condicional Seleccione el rango incluyendo los rótulos

CENIBIT

Página 10

Excel Avanzado.

Agregue las condiciones, para el total. Columna

Es

Este valor

Categoría

=

Herramientas

Precio

>=

12,95

CENIBIT

Página 11

Excel Avanzado.

Observe las opciones y seleccione copiar la formula y los valores condicionales, en la celda A20.

Seleccione la celda destino, A20.

CENIBIT

Página 12

Excel Avanzado.

Seleccione la celda para la primera condición, A21

Copie

la

formula

en

A22

y

observa

la

formula

resultante

=SUMA(SI($B$2:$B$18=A20;SI($D$2:$D$18>A21;$F$2:$F$18;0);0))

CENIBIT

Página 13

Excel Avanzado.

La tabla muestra el resultado de los totales de las herramientas, con precio superior a 12,95, el aspecto mas importante es que la forma original de la tabla no se pierde.

CENIBIT

Página 14

Excel Avanzado.

Análisis de Alternativas (Escenarios) Dado que la compra de un vehiculo en la actualidad es un inversión en el orden de Bs. 70.000.000, es necesario decidir si para el uso de la empresa es mejor venderle o alquilarle el vehiculo en cuestión, la oferta de venta implica 5 años de financiamiento y se puede rescatar sobre el costo del activo al final de su vida útil Bs. 3.000.000. Por otro lado el contrato de alquiler es también de 5 años y se contempla el pago de Bs. 16.000.000 por año, al final del periodo, en ambos casos la tasa es de 5,8%, cabe destacar que el problema se resuelve con el uso de la función financiera VA en la celda E3. =VA(B8/B4;B6*B4;B3;B7;B5)

Para tomar la decisión se crea la siguiente matriz: 

Alquiler Semestral: importe Bs. 8.500.000; periodicidad 2



Alquiler Trimestral: importe Bs. 4.200.000; periodicidad 4



Alquiler Mensual: importe Bs. 1.500.000; periodicidad 12

La herramienta escenarios permite evaluar estas tres (3) opciones y sobre esa comparación decidir cual es la mas rentable.

Se invoca la herramienta,

se asigna los nombres Semestral, Trimestral y mensual, las celdas cambiantes son el rango (B3:B4), ver gráfico.

CENIBIT

Página 15

Excel Avanzado.

Se presiona el botón resumen se acepta celda para el resumen (E3) y se obtiene:

A partir de esto se observa que es más rentable cobrar el alquiler mensual, en su defecto el semestral, en vez de la opción trimestral o la anual.

Análisis de Sensibilidad de una y dos variables (Tablas) Ahora vamos a los análisis de sensibilidad, se basa en la herramienta para generación de tablas, las cuales pueden trabajarse a partir de la variación de una o dos variables, la base para el análisis es el problema precedente, solo que ahora, se parte de pagos mensuales de Bs. 1.350.000 y se evalúan los intereses a partir de 5%.

CENIBIT

Página 16

Excel Avanzado. NOTA: Las tablas que a continuación se incluyen muestran los resultados de los ejercicios de una y dos variables.

Complete la tabla como se muestra a continuación:

Análisis de una variable: Consiste en verificar que pasa si se varia una de las variables, por ejemplo que pasa si el interés varia y como afecta el valor actual.

Para esto en la celda

A12 coloque la ecuación (=E3), los intereses inícielos en B12 con 5% y complételos con relleno de series (Edición > Rellenar > Series). 

Series en: Filas



Tipo: Lineal



Incremento: 0,25%



Límite: 7,25%

CENIBIT

Página 17

Excel Avanzado.

Sombree el rango (A11:K12) y complete la tabla (Datos > Tablas) con el dato celda de entrada (filas) $B$8 y presione ACEPTAR, observe los resultados.

Análisis de dos variables: Consiste en verificar que pasa si se varían dos de las variables, por ejemplo que pasa si el interés varia y la mensualidad y como afecta el valor actual. Para esto en la celda A15 coloque la ecuación (=E3), los intereses inícielos en B15 con 5% y complételos, los intereses complételos con relleno de series (Edición > Rellenar > Series). 

Series en: Filas



Tipo: Lineal



Incremento: 0,25%



Límite: 7,25%

Ahora actualice los montos de la mensualidad, inicie los valores en A16 con Bs. 1.300.000 y complételos los interese complételos con relleno de series (Edición > Rellenar > Series). 

Series en: Columnas



Tipo: Lineal



Incremento: Bs. 50.000

CENIBIT

Página 18

Excel Avanzado. 

Límite: Bs. 1.800.000

Sombree el rango (A11:K26) y complete la tabla (Datos > Tablas) con el dato celda de entrada (filas) $B$8 y celdas de entrada (columnas) $B$3, presione ACEPTAR, observe los resultados.

Para los efectos de cambio de color use

el formato condicional y utilice como referencia el valor de E3.

Búsqueda de Objetivo con una Variable (Solver). Esta herramienta sirve para resolver ecuaciones de una variable, a través de la variación de uno de sus elementos para obtener un resultado especifico predeterminado, en otras palabras sirve para ajustar según un resultado que desea obtenerse. En el siguiente problema el dueño de la ferretería debe facturar Bs. 400.000.000 y desea saber cual con el incremento en la venta de cual de mercancía debe puede obtener el resultado, se tomara como caso de estudio la venta de rollos de alambre. Herramientas > Búsqueda de Objetivos…

CENIBIT

Página 19

Excel Avanzado. Definir la Celda: F19 Con el valor: Bs. 400.000.000 Para cambiar en las celdas: E17 El primer resultado indica que con 84,54 rollos se alcanza el objetivo, pero las estadísticas le indican que no vende más de 82 rollos, así que se busca otro objetivo. Ahora sobre la base de 81 rollos, se toma la venta de la cinta aislante eléctrica, con los siguientes parámetros: Definir la Celda: F19 Con el valor: Bs. 400.000.000 Para cambiar en las celdas: E14 El resultado de 1253,06, se ajusta en 1153, pero aún existe una diferencia de 300 Bs. El problema puede seguirse hasta ajustar el resultado buscado.

CENIBIT

Página 20

Excel Avanzado. Problemas de Optimación (Programación lineal). Una de las limitaciones del método anterior se refiere a la cantidad de intentos que se requieren para ajustar un resultado, existe un mecanismo mas expedito para este tipo de cálculos, es conocido como el método de programación lineal y específicamente refiere como maximizar o minimizar (optimar funciones). El problema planteado se refiere a la mejor combinación de avisos en periódicos, con un presupuesto de hasta Bs. 90.000 y que sean leídos por al menos 100.000 personas. El análisis preliminar del problema indica: 

Maximizar la cantidad de personas que vean los anuncios.



Colocar un mínimo de 3 en los primeros 3 periódicos y 4 en el último.



No colocar más de 4 avisos en ningún periódico.



No exceder los Bs. 90.000 de presupuesto

La optimación de un problema, se basa en cumplir una serie de restricciones que a continuación se detallan: 

Presupuesto <= Bs. 90.000



Audiencia >= 100.000



Anuncios casos Nº 1 a 3 >= 3



Anuncios casos Nº 4 >= 4



Anuncios Nº 1-4 <= 8

CENIBIT

Página 21

Excel Avanzado. 

Anuncios Nº 1-4 deben ser enteros

Se establece como objetivo la celda ($F$6) con el valor (el resultado de la audiencia) a maximizar (Máximo).

Las celdas cambiantes son el rango de datos en el cual se ubican las cantidades de avisos ($D$2:$D$5) y las restricciones se agregan una a una en el recuadro siguiente: Restricciones: Sintaxis

Descripción

$E$6 <= $F$8

Presupuesto

$F$6 >= $F$9

Audiencia

$D$2:$D$4 >= $F$10

Mínimos Anuncios Periódico Nº 1 – 3

$D$5 >= $F$11

Mínimos Anuncios Periódico Nº 4

$D$2:$D$5 <= $F$12

Máximos Anuncios Periódicos Nº 1 -4

$D$2:$D$5 = int (Entero)

Criterio de validación

Observe el cuadro correspondiente:

CENIBIT

Página 22

Excel Avanzado.

Al presionar el botón resolver:

Se marcan las opciones, Respuestas, Sensibilidad y Límites, obteniéndose como resultados:

Excel presenta un informe de resultados:

CENIBIT

Página 23

Excel Avanzado.

Estadística Descriptiva. Al momento Excel ha ofrecido sus herramientas de análisis, sin embargo, el proceso de toma de decisiones obedece al análisis de las características del grupo de datos, para esto se ofrece un paquete de análisis de datos. Para esto en la barra de menú, tome la opción Herramientas > Análisis de Datos… y observe las opciones disponibles:

CENIBIT

Página 24

Excel Avanzado.

A los efectos de este análisis se utilizara la opción de estadística descriptiva, opción que permite obtener el perfil de los datos, para esto se analizara el mismo paquete de datos utilizado en la búsqueda de objetivos. La razón principal es que en la búsqueda de objetivos, solo puede variarse una celda por vez y el diseño para obtener respuestas especificas puede obtenerse de un problema de programación lineal, pero, la toma de decisiones en la mayoría de los caso depende de conocer el patrón de comportamiento de los datos.

CENIBIT

Página 25

Excel Avanzado.

El análisis es una función de las opciones de entrada: La primera regla sociada es que solo

pueden

analizarse

datos

numericos, en este caso el rango $D$1:$F$18. Los

datos

se

muestran

en

columnas y tiene rotulas en la primera fila. Las opciones de salida, define que datos y como se mostraran. Para los efectos de este ejercicio se toman todas las opciones y con los resultados en una hoja nueva.

CENIBIT

Página 26

Excel Avanzado.

Este punto es apenas el inicio en Excel, su uso pueden ser tan complejo como se desee, el resto de las aplicaciones que aun quedan por explicar son específicas para un área o rama profesional especifica por ejemplo: Aplicaciones Económicas Aplicaciones Financieras Aplicaciones de Ingeniería. Aplicaciones Estadísticas.

Macros en Excel Las Macros son programas que sirven para abreviar acciones repetitivas, que salen del contexto normal de operación de un software, por ejemplo el formato de una tabla, es un accesorio que solo sirve de adorno a la tabla, ya que su valor de esta en la información, sin embargo, la forma en que se presenta puede hacer que esos datos tengan o no el impacto deseado. Otra ventaja es que la interfase gráfica de la aplicaciones compatibles con office, le ahorran al usuario la necesidad de conocer formalmente lenguajes de programación visuales de 4to nivel, esto es una ventaja y una limitación,

CENIBIT

Página 27

Excel Avanzado. Es importante destacar que una macro solo funcionara en la versión para la cual fue diseñada, estas no son heredables, por lo cual debe mantener registro de los pasos o acciones que siguió para la creación de cada macro, esto le será de ayuda para cambiar una macro de una versión a otra, ya que método mas directo y sencillo es volver a hacer, el otro involucra entrar al código fuente y editar los pasos hasta hacer que funciones (de allí la importancia del registro). Ahora un ejemplo para las macros de Excel, la función va a ser la automatización de un filtro avanzado, la función para calcular los promedios es

de

la

forma

(celda

E2)

=B2*20%+C2*20%+D2*60% y en la celda E7 =promedio(E2:E6), como se muestra

a

continuación.

Renombre la hoja de trabajo con el

nombre

MFA

(Macro

Filtro

Avanzado). Copie los datos de los alumnos con los cuales va a trabajar, ahora vamos a crear el primer macro (Herramientas > Macro > Grabar nueva macro…) Nombre del Macro: Formato_1 Sombree (A1:E7)

el y

rango

de

datos

aplique

autoformato multicolor2 Sombree el rango de datos (A9:B15) y aplique el autoformato multicolor2

CENIBIT

Página 28

el

Excel Avanzado. Sombree el rango de datos (D1:D15) y aplique el autoformato multicolor2 Ajuste los anchos de columnas y en el zoom seleccione la opción selección Cierre el macro Seleccione la región (A1:E15) y aplique el autoformato ninguno Repita el macro y observe los resultados.

Ahora el filtro avanzado, este va a servir para traer datos específicos de los alumnos en cuestión, el filtro en cuestión va a traer los nombre de los alumnos que cumplan un cierto criterio, en este caso >12 (Celda B10). Nombre del Macro: Filtro_1 Haga clic en la celda A2 Ejecute Datos > Filtro > Filtro Avanzado Rango de datos MFA!$A$9:B$10 Copiar en MFA$D$9 Cierre el macro y observe los resultados. Borre los resultados obtenidos. Ejecute el macro de nuevo

CENIBIT

Página 29

Excel Avanzado.

Observe de nuevo los resultados, borre las celda de resultado y pruebe con varios opciones por ejemplo <14, <10, >17, etc.

FORMULARIOS Esta herramienta se basa en agregar opciones formularios para simplificar que estos sean llenado entre otras opciones se dispone de: 1. Casillas de Verificación, se caracterizan por ser opciones que se marcar con un signo de verificación, puede haber mas de una selección, es decir se utiliza para opciones de selección múltiple.

CENIBIT

Página 30

Excel Avanzado. 2. Botones de Selección, se caracterizan por ser opciones que se marcar con un circulo, no puede haber mas de una selección, es decir cuando se marca una opción las demás se inhabilitan. 3. Cuadros de lista, presenta como su nombre lo indica una lista para seleccionar una opción, es posible habilitar selección múltiple. 4. Cuadros combinado, ofrece un botón desplegable que contiene una lista para seleccionar la opción disponible. 5. Barras de desplazamiento, las hay horizontales o verticales, sirven como control deslizante, para colocar valores en una celda vinculada, sus valores oscilan entre 0 a 30.000 con incrementos fraccionarios. 6. Control numérico, similar al anterior, pero en vez de presentar una barra deslizante presenta solo las fechas arriba abajo, tiene los mismos rangos. 7. Cuadros de texto, es un espacio para que el usuario escriba un comentario. Ejemplos: Casilla de Verificación Botón de Selección Cuadro de Lista Cuadro Combinado

Barra deslizante y Control Numérico Cuadro de Texto Ejercicio 1.

CENIBIT

Página 31

Excel Avanzado.

Inserte dos (2) botones de selección, sobre cada uno presione el botón secundario del ratón y vincule con la celda $G$1, en G2 incluya la ecuación =SI(G1=1;”Indocumentado”;”Valido”). Cambie el botón y observe los resultados. Ejercicio 2. FALSO FALSO FALSO FALSO FALSO Revisar Vehiculo

Inserte cinco (5) casillas de verificación, vincule cada una con las celdas $G$8, $G$9, $G$10, $G$11 y $G$12, en la celda G13 escriba la ecuación =SI(Y(G8=Verdadero;G9=Verdadero;G10=Verdadero;G11=Verdadero;G12=Ve rdadero);”Viajar”;”Revisar Vehiculo”), comience a activar uno a uno los botones y observe los resultados. Recuerde la función O e Y, que son de utilidad con los formularios.

Ejercicio 3

CENIBIT

Página 32

Excel Avanzado.

Ingrese esta lista en el rango (G20:G23), dibuje en B20 un cuadro combinado, ahora vincule origen es el rango de la lista, destino E20, despliegue y observe.

Las demás formas ofrecen similitudes a las ya observadas, solo resta algo de imaginación para aplicar estas funciones y automatizar formularios, para avanzar a este nivel se requieren conocimientos de Visual Basic.

CENIBIT

Página 33