excel avanzado
TRANSCRIPT
Calle Coromoto, Diagonal al C.C La Fuente Tlf: 0264-2411155-2417282
Guia de Excel Avanzado
Excel Avanzado.
CENIBIT Página 2
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.
Excel Avanzado.
CENIBIT Página 3
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:
-10
-5
0
5
10
15
20
25
30
1 2 3 4 5 6
Tiempo
Velocidad
Aceleración
Pero esta plagado de imperfecciones e inexactitudes, las cuales van a ser
corregidas a continuación.
Como norma para datos
científicos, asumiendo
que estos son 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.
Excel Avanzado.
CENIBIT Página 4
Ahora compare el gráfico de
barras precedente con el gráfico
de correlación que sigue a
continuación, observe los ejes
de referencia en ambos casos:
Es posible en este punto
ubicarse sobre cualquiera de las
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
cada eje de referencia tiene su
propio eje auxiliar de
referencia, cada uno con su
escala adecuada a los valores
que representa, inclusive con
signos propios para distinguir
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.
-10
-5
0
5
10
15
20
25
30
0 2 4 6 8 10 12 14 16 18 20
Velocidad
Aceleración
0
5
10
15
20
25
30
0 5 10 15 20
-6
-4
-2
0
2
4
6
8
Velocidad
Aceleración
Excel Avanzado.
CENIBIT Página 5
Mes Semana Día/Semana Día Hora Ventas
Enero 1 Lunes 1 9:00 147
… … … … … …
Diciembre… 52 Viernes 31 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:
Excel Avanzado.
CENIBIT Página 6
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.
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:
0
1000
2000
3000
4000
5000
6000
7000
9 10 11 12 13 14 15 16 17 18 19 20
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
Día/Semana (Todas) Mes (Todas)
Suma de Ventas
Hora
Día
Semana
Excel Avanzado.
CENIBIT Página 8
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…
Excel Avanzado.
CENIBIT Página 9
Para cambio de Relleno: Categoría.
Usar función: Suma.
Agregar Subtotal a: Precio y Unidades vendidas
Observe el resultado.
Excel Avanzado.
CENIBIT Página 10
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
Excel Avanzado.
CENIBIT Página 11
Agregue las condiciones, para el total.
Columna Es Este valor
Categoría = Herramientas
Precio >= 12,95
Excel Avanzado.
CENIBIT Página 12
Observe las opciones y seleccione copiar la formula y los valores condicionales,
en la celda A20.
Seleccione la celda destino, A20.
Excel Avanzado.
CENIBIT Página 13
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))
Excel Avanzado.
CENIBIT Página 14
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.
Excel Avanzado.
CENIBIT Página 15
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.
Excel Avanzado.
CENIBIT Página 16
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%.
Excel Avanzado.
CENIBIT Página 17
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%
Excel Avanzado.
CENIBIT Página 18
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
Excel Avanzado.
CENIBIT Página 19
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…
Excel Avanzado.
CENIBIT Página 20
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.
Excel Avanzado.
CENIBIT Página 21
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
Excel Avanzado.
CENIBIT Página 22
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:
Excel Avanzado.
CENIBIT Página 23
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:
Excel Avanzado.
CENIBIT Página 24
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:
Excel Avanzado.
CENIBIT Página 25
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.
Excel Avanzado.
CENIBIT Página 26
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.
Excel Avanzado.
CENIBIT Página 27
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,
Excel Avanzado.
CENIBIT Página 28
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 el rango de datos
(A1:E7) y aplique el
autoformato multicolor2
Sombree el rango de datos (A9:B15) y aplique el autoformato multicolor2
Excel Avanzado.
CENIBIT Página 29
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
Excel Avanzado.
CENIBIT Página 30
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.
Excel Avanzado.
CENIBIT Página 31
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.
Excel Avanzado.
CENIBIT Página 32
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
Excel Avanzado.
CENIBIT Página 33
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.