MANUAL DE EXCEL AVANZADO
Introducción
Como su título
lo sugiere estos apuntes son de técnicas avanzadas de Excel, es decir, que no corresponden a un
excel básico ni a un excel intermedio, en general están dirigidas a la gestión.
Estos apuntes se han hecho pensando en usuarios con vasta experiencia en Excel,
que ya han superado el “segundo grado” en manejo de planillas.
Se
supone que quien estudia en estos apunte ya sabe como construir una planilla
simple, como escribir fórmulas y que pasa cuando se copian. Como se imprime una planilla y como se graba.
Como se imprime una planilla y como se graba. Saben como definir, usar e
interpretar tablas dinámicas. Como crear, definir e interpretar escenarios.
En
estos apuntes se seleccionaron las técnicas que se estima necesita un ingeniero
o un ejecutivo para la gestión, es decir, estos apuntes profundizan en todos
aquellos comandos u opciones que son poco usados, no porque no sean útiles sino
porque casi nadie los conoce, pero que se estima son necesarios para el
ejecutivo moderno en la toma de decisiones o en el control.
El
Manual de Excel Básico para Estudiantes de Ingeniería se publicó el año 2004 en
este mismo Portal: www.lawebdelprogramador.com
(lleva hasta ahora más de 60.000 visitas), y durante el año 2006 se publicó el
Manual de Excel Intermedio para Estudiantes de Ingeniería.
Este
proyecto intenta tratar el Excel completo en cinco Manuales:
ü Manual de Excel para Estudiantes de Ingeniería, ya publicado en LWP[1],
ü Manual de Excel Intermedio para Estudiantes de Ingeniería, ya publicado
en la LPW,
ü Manual de Excel Avanzado para Estudiantes de Ingeniería, que es este
texto,
ü Manual de Herramientas Estadísticas de Excel para Estudiantes de
Ingeniería, que se espera su publicación para este año 2007, y
ü Tópicos de Excel para Estudiantes de Ingeniería, se espera su
publicación para el año 2008, que tratará fundamentalmente de funciones de
Excel poco conocidas y por lo tanto poco usadas.
Este
manual trata las siguientes materias:
Gráficos especiales,
Esquemas,
Funciones financieras,
Solver,
Estadísticas aplicadas a través de Excel.
Todos
estos puntos son desarrollados en forma
Teórica y práctica y con ejemplos que les puedan servir a los estudiantes de
Ingeniería, a los ingenieros y a los
ejecutivos en la gestión.
Gráficos Especiales
Gráficos de Línea vs. Gráficos de
Dispersión XY
Una PYME[2]
fabrica solamente tres tipos de muebles: Escritorios, Sillas y Estantes. Mediante
un gran esfuerzo reinvirtiendo las utilidades y capacitando a su personal ha
logrado ir duplicando la producción. La producción en los últimos años se muestra en la siguiente tabla:
|
|
|
|
|
|
PRODUCCION DE UNA PYME
|
|||
|
|
|
|
|
|
AÑOS
|
ESCRITORIOS
|
SILLAS
|
ESTANTES
|
|
1980
|
268
|
323
|
194
|
|
1990
|
536
|
646
|
388
|
|
1996
|
804
|
969
|
582
|
|
2000
|
1072
|
1292
|
776
|
Si esta tabla se grafica mediante un gráfico de
Líneas[3]
el resultado se muestra en la página siguiente:
Como se puede observar este gráfico está con
graves errores, ya que el aumento de la producción es el mismo para todos los
años indicados, sin embargo, la diferencia entre los años no es la misma, por
lo tanto debería salir una curva exponencial. Esto se soluciona usando gráficos
tipo de Dispersión XY. Basta con cambiar el tipo de gráfico para que
aparezcan las curvas correctas, como se muestra en la figura siguiente:
Los gráficos Dispersión XY son los indicados cuando la variable del eje
de las X no representa incrementos
constantes.
Gráficos de Dispersión XY
Usando los gráficos de dispersión se puede
tener gráficos como el siguiente:
Esta roseta se llama figura de Lissajous, en
honor del físico del siglo XIX que las estudio por primera vez. Estas figuras
aparecen al superponer movimientos oscilatorios. Lissajous usaba un aparato muy complejo, con
dos diapasones y espejitos que reflejaban la luz. Ahora se pueden obtener las
mismas figuras en el computador usando gráficos de Dispersión XY.
Para construir este tipo de gráficos se usa una
tabla como la figura siguiente:
Los pasos para hacer esta tabla son los
siguientes:
§ En la columna A se generan los números del 1 al 100,
§ La columna B debe quedar libre,
§ En la celda C1 se escribe la fórmula: =SENO(2*G$1*PI()*A1/10)
§ En la celda D1 se escribe la fórmula: =COS(2*G$1*PI()*A1/10)
§ Se extiende el rango C1:D1 hasta la fila 100
§ En la celda G1 se escribe el valor 2
§ En la celda G2 se escribe el valor 5
§ Se grafica el rango C1:D100
Para hacer este tipo de gráficos hay unas
diferencias con los gráficos normales, por lo tanto lo detallamos paso a paso.
§ Se coloca el cursor en D1 o en cualquier celda del rango anterior,
§ Se toman las opciones Insertar/Gráfico,
entonces aparece el Asistente para
Gráficos.
§ En el primer paso se indica el tipo de gráfico Dispersión XY y el subtipo de la segunda fila, segunda columna.
§ Se da un clic en Siguiente.
§ En el segundo paso del asistente indicamos Series en columnas.
§ Se da un clic en Siguiente
para pasar a la etapa de Opciones de
gráfico.
§ En la ficha Eje se desmarcan
todas las opciones.
§ En la ficha Líneas de división,
también se desmarcan todas las opciones.
§ En la ficha Leyenda se
desmarca la opción Mostrar leyenda.
§ Se da un clic en Siguiente.
§ Se marca la opción Colocar
gráfico en una hoja nueva.
§ Se da un clic en Finalizar.
El resultado será similar al de la figura
siguiente:
Este gráfico se puede optimizar un poco, por
ejemplo, eliminándole el fondo gris, esto se hace de la siguiente forma:
§ Se da un clic sobre el fondo del gráfico, usando el botón derecho del
mouse.
§ Del menú contextual que aparece, se toma la opción Formato de área de trazado, aparece el cuadro que se muestra a
continuación:
§ Dentro de área se da la opción Ninguna.
§ Hacemos un clic en Aceptar.
La figura queda como se muestra a continuación:
Las fórmulas de la tabla fueron escritas de
forma tal que variando el contenido de G1 y/o G2, las curvas pueden variar de
inmediato, por ejemplo si coloco 5 en G1 y en G2, aparece la curva que se
muestra en la página siguiente:
En cambio la Figura de Lissajous, se obtiene
colocando un 5,1 en G1 y un 5 en G2, al efectuar este cambio queda esta figura:
Lo importante de este capítulo es que mediante
el estudiante de Excel comprenda que mediante el Excel se pueden simular los
resultados de efectos físicos de cualquier orden: por ejemplo: Las curvas
resultantes del sonidos de dos diapasones, caídas de cuerpos, cálculo de
trayectorias espaciales, situaciones económicas, etc…
Esquemas.
Descripción de Esquemas
Muchas hojas de cálculos
están diseñadas en jerarquías de celdas. Aplicar un esquema a una hoja consiste
en asociar una relación de subordinación entre las diferentes celdas.
Para explicar los esquemas
podemos apoyarnos en la hoja de la figura siguiente, que muestra el desglose de
la producción de un año en meses y en trimestres. Cada trimestre suma los
valores de los meses que componen el trimestre, y se entregan como totales las
sumas de los trimestres:
Cada trimestre es un
esquema, por lo tanto en una figura como la siguiente debe haber cuatro
esquemas, cada uno con sus totales. La línea horizontal que se observa en la
figura siguiente, indica que hayan esquema que abarca el primer trimestre del
año.
En la figura siguiente se puede observar la esquematización de una hoja
de Excel, en que se muestran solo los tomates de los cuatro trimestres y el
total general del año. Los signos más que se muestran en la parte superior de
los trimestres indican que se ocultó la parte de detalle y sólo se muestran los
totales de cada trimestre.
Los números 1 y 2 que se
muestran en la parte superior, indican que un nivel de esquemas y datos de una
hoja de cálculo.
A su vez esta hoja se puede
volver a esquematizar, dejando como un esquema los totales trimestrales, y al
ocultar éstos, queda como se muestra en la figura siguiente:
Los números 1, 2 y 3 que se
muestran en la parte superior izquierda indican que hay un nivel de datos (3),
un primer nivel de esquemas que resume esos datos (2) y un segundo nivel de
esquemas que resume el nivel anterior (3). Al igual que en el caso anterior el
signo + indica que es un resumen de datos esquematizados.
Creación de un Esquema
Antes de esquematizar es
importante asegurarse de que estén introducidos todos los datos y las fórmulas
en la zona de la hoja que se va a esquematizar. Además los datos deben estar
jerarquizados.
Se pueden crear esquemas de
forma automática o con la posibilidad de incluir modelos con el comando Configurar o con la barra de herramientas.
N este ejemplo se va a
esquematizar unos datos de ventas que necesita la gerencia para la gestión; se
trata de las ventas anuales mes por mes con la siguiente información:
Artículos producidos,
Precio unitario,
Valor total de producción,
Precio de venta,
Comisión al vendedor,
Precio de venta neto,
IVA[4]
Precio de venta al público (PVP).
El primer paso es construir la planilla: colocarle un título: por
ejemplo (Ventas año 2006), y a partir de la FILA 1, crear las siguientes
títulos de filas:
Descripción,
Enero,
Febrero,
Marzo
Trimestre 1,
Abril,
Mayo,
Junio,
Trimestre 2,
Julio,
Agosto,
Septiembre,
Trimestre 3,
Octubre,
Noviembre,
Diciembre;
Trimestre 4,
Total Año.
Si lo ha hecho correctamente, debería quedarle algo como la tabla de la
página siguiente:
en la cual puede resumir y dejar sólo los subtotales, o resumir más y
dejar sólo el total general. Como también puede ampliarlos hasta llegar a los
datos originales.
Funciones financieras.
Introducción
Algunas de las
funciones financieras tales como Pago se vieron en Manuales anteriores[5]
Las funciones financieras NPER, PAGO[6],
PAGOINT, PAGOPRIN, VA, VNA Y VF tienen
en común los argumentos:
tasa : Porcentaje de interés
nper : Plazo de la inversión o préstamo
pago : Dividendo o cuota mensual[7]
va : Valor
actual que se percibe o desembolsa al principio de la operación, también se
denomina Capital o monto del préstamo.
vf : Valor
futuro que se percibe o desembolsa al final de la operación. Si se omite se
supone que el valor futuro es 0.
Tipo : Indica
el tipo de la operación. Si toma el valor:
§ 0 ó se omite. Indica que los pagos se efectuarán al final del período
(mes, trimestre, semestre o año, etc.)
§ 1: Indica que los pagos se realizan al principio del período.
Si en la función
que aparece en Excel cuando se va a ejecutar, si el argumento aparece entre
paréntesis cuadrados indica que es opcional.
Lo argumentos tasa
y nper debe referirse al mismo período de tiempo, es decir, por ejemplo, no
puede colocarse una tasa de interés anual y para período mensual.
A fin de
simplificar los cálculos la tasa mensual se calcula dividiendo por 12 la tasa
anual. Aunque esto está incorrecto la diferencia es mínima con la fórmula de
cálculo real:
§ i: Tipo de interés expresado en tanto por 1
§ k: Número de los nuevos períodos que hay en un año
Por ejemplo para
transformar una tasa anual de 15% en una tasa mensual, la fórmula a aplicar es:
=POTENCIA((1+0.15);(1/12))-1
Lo que nos da por
resultado: 1.01, en cambio, si dividimos 15/12 nos da: 1.25, por lo cual, para
las siguientes fórmulas para reducir de una tasa anual a una tasa mensual, para
simplificar los cálculos se dividirá la tasa anual por 12, ya que la diferencia
es mínima para cantidades pequeñas.
Funciones Financieras
NPER
Calcula el número
de períodos necesarios para amortizar un préstamo, dadas las cantidades a para,
la tasa de interés, el valor actual y el valor futuro (si hay). Su formato es:
El argumento pago debe ser igual o superior al
producto de los argumentos tasa por va, en caso contrario NPER devuelve:
#¡NUM!
Ejemplo: Se desea saber en
cuanto tiempo se amortiza un préstamo de $ 10.000.000 al 11% anual si se desea
pagar una cantidad mensual de $ 120.000:
=NPER(11%/12;-120.000;10000000)
Excel devuelve
158,18 meses.
PAGOINT
Calcula la cantidad
a pagar por intereses sobre un préstamo en un período determinado de tiempo con
unos pagos y un tipo de interés constantes. Su formato es:
§ Período: Período para el que se desea calcular el pago de intereses.
Debe ser un número comprendido entre 1 y nper.
Ejemplo: Se desea saber cual es
la cantidad a pagar por concepto de intereses en el primer mes correspondiente
al pago de un préstamo de $ 10.000.000, a veinte años, si la tasa de interés es
del 11% anual: La fórmula es:
=PAGOINT(11%/12;1;20*12;10000000)
Excel entrega como
resultado: -$ 91.666,67
PAGOPRIN
Calcula la cantidad
amortizada de un préstamo en un período determinado de tiempo, con unos pagos y
un tipo de interés constante. La suma de las funciones PAGOINT y PAGOPRIN
devuelve la cantidad total a pagar determinada por la función pago. Su formato
es:
§ período: Período para el que se desea calcular los pagos de intereses. Debe
ser un número comprendido entre 1 y nper.
Ejemplo: Se desea saber cual es
la cantidad amortizada en el primer mes que corresponde al pago de un préstamo
de $ 10.000.000 a 20 años y a una tasa de interés del 11% anual.
=PAGOPRIN(11%/12;1;20*12;10000000)
Excel entrega como
resultado: $ -11.552,17
VA
Determina el valor
actual de una inversión en base a una serie de pagos periódicos iguales o el de
un pago global. Si el valor devuelto por la función es superior al coste de la
inversión, ésta es buena. Su sintaxis es:
Ejemplo:
Se desea saber si
es rentable invertir US$ 4.000, si se espera recibir US$ 1.000, durante los
próximos 7 años. Como tasa se considera un interés bancario de 10% anual:
=VA(10%;7;1000)
Excel devuelve el
valor –US$ 4.868,42. Esto significa que deberíamos estar dispuestos a invertir
ahora US$ 4.868,42 para recibir US$ 7.000 durante los próximos 7 años. Al ser
la inversión inicial de US$ 4.000, ésta es una buena inversión.
Nota:
Si se omite un
argumento en la mitad de la fórmula para usar e argumento vf, se debe escribir un punto y coma por el argumento omitido.
Ejemplo:
Supongamos que en
lugar de los US$ 1.000 anuales, nos proponen pagarnos los US$ 7.000 al final de
los 7 años ¿Es bueno el negocio? La fórmula a utilizar es:
=VA(10%;7;;7000)
Excel devuelve el
valor –US$ 3.592,11. Esto quiere decir que deberíamos desembolsar ahora US$
3.592,11 para recibir US$ 7.000 al cabo de 7 años. Al ser la inversión inicial
de US$ 4.000, esta no es una buena inversión.
VNA
Calcula el valor neto actual de una serie de
flujos de caja descontados a un tipo de interés. VNA es otra función para
determinar si una inversión es buena. La inversión se considera rentable cuando
VNA da un número positivo. Su sintaxis es:
=VNA(tasa;valor1;valor2;…)
La función VNA se
diferencia de la función VA, en que mientras VA considera siempre la cantidad
constante, VNA permite incluir cantidades variables tanto positivas como
negativas.
Ejemplo:
Supongamos que se
desea saber si es rentable invertir US$ 250.000, si esperan una pérdida de US$
60.000 el primer año, con ganancias en los siguientes años de US$ 100.000, US$
150.000 y US$ 190.000, o invertirlo en letras con un interés del 12% anual. La fórmula es la siguiente:
=VNA(12%;-60000;100000;150000;190000)
Excel devuelve: US$
3.663,43 Al ser un número positivo, indica que la inversión es buena.
VF
Determina el valor
futuro de una inversión consistente en una serie periódica de pagos iguales o
en una única entrega a una tasa de interés fija. Su formato es:
Ejemplo:
Supongamos que se
desea saber cual es el capital final de un plan de pensiones a 30 años, si se
desembolsan todos los meses $ 10.000 a un interés del 8%. La fórmula es la
siguiente:
=VF(8%/12;30*12;-10000;;1)
Excel devuelve la
cantidad de $ 15.002.524,75
Ejemplo:
Supongamos que se
posee un capital acumulado de $100.000, la fórmula tendrá el siguiente aspecto:
=VF(8%/12;30*12;-10000;-100000;1)
Excel devuelve $
16.096.524,75
[2] PYME: Abreviatura de Pequeña y Mediana Empresa
[3] Gráficos se vio con todo detalle en la publicación: Manual de Excel
para Estudiantes de Ingeniería, de este mismo Portal: La web del programador.
[4] IVA: Impuesto al valor agregado, impuesto sobre las ventas y
servicios, que en este momento está en el 19% sobre el precio de venta.
[5] La función Pago se vio en el “Manual de Excel para Estudiantes de
Ingeniería” páginas 16 a 31 del mismo autor y se volvió a insistir sobre ella
el “Manual de Excel Intermedio para Estudiantes de Ingeniería” páginas 47 a 49
del mismo autor.
[6] Ya vista anteriormente.
[7] El pago tiene, al menos en Chile, dos nombres: Si se trata de
préstamos personales o a corto plazo se llama “cuota”, si se trata de préstamos
hipotecarios o a largo plazo se llama “dividendo”. Pero, en ambos casos es el
abono o cuota que se paga mensualmente.
