lunes, 8 de noviembre de 2010

Listado de valores con Macro

Descargar el fichero: Listado.xlsm


Hemos creado siete sistemas para que mediante macro llevemos cierto cálculo a una columna de una hoja. Los primeros cinco sistemas toman el valor de una celda calculada en la Hoja para llevarla a una columna a medida que toma diferentes valores el cálculo. Para simplificar hemos tomado el cálculo de un número aleatorio con la función =ALEATORIO(), pero la celda que tomamos podría ser, en general, cualquier otro valor que se obtenga en una hoja de Excel con cálculos mucho más complicados. Los dos últimos sistemas no toman el valor de una celda de la hoja sino que calculan, en nuestro caso, el número aleatorio desde la propia macro con RND. Será mucho más rápido que los cálculos los haga la macro internamente, y no la hoja de Excel, aunque somos conscientes de que en muchas ocasiones lo interesante es disponer de la Hoja de Excel toda su potencia de cálculo, y su organización intuitiva de los datos.


Hemos tomado tiempos para determinar el sistema más rápido, y gana de forma abrumadora el de la Hoja6. Para 1.000 datos generados los tiempos, en mi portátil, han sido los siguientes:
  1. Hoja1: 10 segundos
  2. Hoja 2: 3 segundos
  3. Hoja 3: 3 segundos
  4. Hoja 4: 2 segundos
  5. Hoja 5: 6 segundos
  6. Hoja 6: 0 segundos
  7. Hoja 7: 3 segundos
La Hoja6 da como resultado 0 segundos, ya que no llega a un segundo.

Para la Hoja6, si en lugar de pedir un listado de 1.000 datos, pedimos 1.000.000 de datos vemos que tarda 5 segundo. Por tanto, la Hoja6 contiene la macro (Listado6) más rápida con mucha diferencia.

Hoja 1

Código:

Sub Listado1()
Dim i As Long
'Activamos la Hoja1
Sheets("Hoja1").Activate
'Tiempo
Range("C12") = Now
'Borramos todo lo que hay en la columna F
Columns("F:F").ClearContents
'Generamos la Lista
'copiando y pegando con pegado especial valores
For i = 1 To Range("D6")
   Range("D4").Copy
   Cells(i, "F").PasteSpecial xlPasteValues
Next i
'Para quitar la linea de hormigas
Application.CutCopyMode = False
Range("C13") = Now
'Situamos el cursor en la celda A1
Range("A1").Select
End Sub

Hoja 2

Código:

Sub Listado2()
Dim i As Long
Sheets("Hoja2").Activate
Range("C12") = Now
Columns("F:F").ClearContents
For i = 1 To Range("D6")
   Cells(i, "F") = Range("D4")
Next i
Application.CutCopyMode = False
Range("C13") = Now
Range("A1").Select
End Sub

Hoja 3

Código:

Sub Listado3()
Dim i As Long
Dim A
Dim n As Long
Sheets("Hoja3").Activate
Range("C12") = Now
n = Range("D6")
ReDim A(n)
Columns("F:F").ClearContents
For i = 1 To n
   A(i) = Range("D4")
   Cells(i, "F") = A(i)
Next i
Application.CutCopyMode = False
Range("C13") = Now
Range("A1").Select
End Sub

Hoja 4

Código:

Sub Listado4()
Dim i As Long
Dim R As Range
Dim A
Dim n As Long
Sheets("Hoja4").Activate
Range("C12") = Now
n = Range("D6")
Set R = Range("F1:F" & n)
A = R
Columns("F:F").ClearContents
'Generamos la matriz A
For i = 1 To Range("D6")
   Calculate
   A(i, 1) = Range("D4")
Next i
'Depositamos los datos de la matriz A en la columna F
Range("F1:F" & n) = A
Range("C13") = Now
End Sub

Hoja 5

Código:

Sub Listado5()
Dim i As Long
'Dim R As Range
Dim A
Dim n As Long
Sheets("Hoja5").Activate
Range("C12") = Now
n = Range("D6")
Set A = Range("F1:F" & n)
Columns("F:F").ClearContents
'Generamos la matriz A
For i = 1 To Range("D6")
   Calculate
   A(i, 1) = Range("D4")
Next i
Range("C13") = Now
End Sub

Hoja 6

Código:

Sub Listado6()
Dim i As Long
Dim R As Range
Dim A
Dim n As Long
Sheets("Hoja6").Activate
Range("C12") = Now
n = Range("D6")
Set R = Range("F1:F" & n)
A = R
Columns("F:F").ClearContents
Randomize
For i = 1 To Range("D6")
   A(i, 1) = Rnd
Next i
Range("F1:F" & n) = A
Range("C13") = Now
End Sub

Hoja 7

Código:

Sub Listado7()
Dim i As Long
Dim A
Dim n As Long
Sheets("Hoja7").Activate
Range("C12") = Now
n = Range("D6")
Set A = Range("F1:F" & n)
Columns("F:F").ClearContents
Randomize
For i = 1 To Range("D6")
   A(i, 1) = Rnd
Next i
Range("C13") = Now
End Sub

sábado, 6 de noviembre de 2010

Sensibilidad de un Forward con Solver

Puede descargar el archivo de Excel siguiente.







No se ve la opción "Convertir variables sin restricciones en no negativas" al grabar la macro


Desde la versión de Excel 2010 en Solver se ha incluido la posibilidad de marcar o dejar sin marcar una casilla de verificación que se denomina "Convertir variables sin restricciones en no negativas".

Para dejar desmarcada esta casilla de verificación desde el código VBA se ha de añadir a la macro la siguiente línea.
  • SolverOptions AssumeNoNeg:=False

Para ver todas las opciones de Solver que se pueden incluir en código VBA ir al siguiente enlace.

La línea de código anterior se puede poner por ejemplo como línea anterior a la que ejecuta Solver al final de la macro.

La línea anterior no aparece al ver el código que ha generado la grabadora de macros, así como no aparecen otras muchas propiedades inherentes al Solver que estamos ejecutando. Por ejemplo, tampoco aparece la precisión que hemos utilizado, entre otras opciones.

Si queremos que aparezcan las opciones utilizadas en Solver, lo que tenemos que hacer mientras grabamos la macro con la grabadora es abrir las Opciones de Solver y luego cerrarlas. Esto nos genera el código necesario que luego si podremos ver. Entre todo este código está el siguiente.
  • AssumeNonNeg:=True

Este código se corresponde con el hecho de no marcar la casilla de verificación de "Convertir variables sin restricciones en no negativas".

Veamos el código que he obtenido al abrir y luego aceptar las Opciones de Solver.

Sub Macro3()
SolverOk SetCell:="$G$6", MaxMinVal:=3, ValueOf:=0, ByChange:="$C$4:$F$4", _
    Engine:=1, EngineDesc:="GRG Nonlinear"
SolverOptions MaxTime:=0, Iterations:=0, Precision:=0.0000005, Convergence:= _
    0.0001, StepThru:=False, Scaling:=True, AssumeNonNeg:=True, Derivatives:=1
SolverOptions PopulationSize:=100, RandomSeed:=0, MutationRate:=0.075, Multistart _
    :=False, RequireBounds:=True, MaxSubproblems:=0, MaxIntegerSols:=0, _
    IntTolerance:=0.1, SolveWithout:=False, MaxTimeNoImp:=30
SolverOk SetCell:="$G$6", MaxMinVal:=3, ValueOf:=0, ByChange:="$C$4:$F$4", _
    Engine:=1, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$G$6", MaxMinVal:=3, ValueOf:=0, ByChange:="$C$4:$F$4", _
    Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve
End Sub

SolverFinish KeepFinal:=1

En el vídeo vemos que se añade una línea de código para no tener que estar aceptando la ventana que nos devuelve Solver al final del proceso. En el vídeo se pone en español el siguiente código.

   SolverResolver resultadoDeseado:=True

Si necesitamos ponerlo en inglés podemos cambiarlo por este otro:

   SolverFinish KeepFinal:=1

En el siguiente enlace se comenta este aspecto.

domingo, 24 de octubre de 2010

Tablas Dinámicas Inversas


Disponemos de una sencilla cuenta de resultados por mes. En cada hoja se encuentran los datos de un mes concreto. Nos proponemos agruparlos con Tablas Dinámicas utilizando Rangos de Consolidación Múltiple, y finalmente convertir los datos en una Base de Datos.



Por ejemplo, los resultados de Enero son los siguientes.


Fase 1

Utilizando Rangos de Consolidación múltiple en Excel 2007 vamos a obtener una tabla resumen con los resultados de todos los meses. Para ello, vamos a necesitar utilizar Tablas Dinámicas con Rangos de Consolidación Múltiple. El problema es que en Excel 2007 ha desaparecido esta opción en el menú habitual para la confección de Tablas Dinámicas.

Es necesario agregarlo a la barra de acceso rápido siguiendo estos pasos:

  1. Botón de Office
  2. Opciones de Excel
  3. Personalizar
  4. "Comandos disponibles en: " Todos los comandos
  5. Localizar, por orden alfabético, "Asistente para tablas y gráficos dinámicos... (PivotTableReport)"
  6. Pulsar el botón Agregar

De esta forma tendremos en la barra de acceso rápido el Asistente para tablas y gráficos dinámicos.


La barra de acceso rápido puede quedar así:



Lanzamos el asistente y elegimos Rangos de consolidación múltiples.


Elegimos "Crear un solo campo de página"


Agregamos Enero, incluida la fila 5.


Agregamos todos los meses.


Pedimos que el informe de tabla dinámica se realice en una hoja de cálculo nueva.


Obtenemos una Tabla Dinámica que agrupa todas las lineas de la cuenta de resultado de los 12 meses.



Fase 2

La lista de campos de la Tabla Dinámica es la siguiente.


De esta lista de campos vamos a eliminar la Columna, la Fila y la página. Únicamente quedará el valor.


Con esto hemos conseguido que la tabla dinámica se reduzca a un único valor numérico que contiene el Total.


Pulsamos doble click con el ratón sobre dicho valor numérico, y así obtendremos la siguiente tabla.



Eliminamos la columna D por no aportar información, y habremos obtenido la información original pero en forma de Base de Datos.



Fase 3

Partiendo de la tabla anterior que contiene los datos en forma de Base de Datos, vamos a obtener la  tabla precedente, cosa que conseguiremos realizando una nueva Tabla Dinámica.

Insertar, Tabla Dinámica y siguiendo lo pasos habituales llegamos a la siguiente tabla.



Actualización

Cuando se elige "Rangos de consolidación múltiples" luego es preferible seleccionar "Campos de página personalizados", en lugar de seleccionar "Crear un solo campo de página". De esta forma nos ahorramos tener que eliminar la columna que sale a la derecha.

Otro sistema para conseguir en Excel 2007 el asistente de Tablas Dinámicas del que disponíamos en Excel 2003 es teclear Alt+T+B. Con ello lanzamos el menú de la versión 2003.





martes, 5 de octubre de 2010

Lluvia

Dispone del siguiente archivo de Excel: lluvia.xlsm

Hoja1

Vamos a generar un poco de lluvia con una macro. De forma aleatoria generamos la posición de una celda dentro del un area, y en ese punto hacemos caer una gota de lluvia representada por un asterisco (*). Esta idea tan simple es la introducción que nos permite entender cómo se utiliza el método de simulación de Monte Carlo para el cálculo de áreas.


Código

Sub puntos_aleatorios()
Dim i As Integer
For i = 1 To 100
    Cells(Int(Rnd * 30) + 1, Int(Rnd * 30) + 1) = "*"
Next i
End Sub
Sub Borra()
Range("A1:AD30").ClearContents
Range("AQ1").Select
End Sub



Rnd genera un número aleatorio entre 0 y 1. Equivale a la función =ALEATORIO().

Hoja2

En la Hoja2 vamos a calcular estimar áreas usando la lluvia.


jueves, 2 de septiembre de 2010

Lanzar macros con iconos y botones

La barra de herramientas de acceso rápido se puede personalizar añadiendo nuevos iconos, y entre ellos podemos incluir las macros que tengamos programadas para lanzarlas de forma rápida. Otra forma de ejecutar macros es creando botones a los que se asigna una macro. Luego al pulsar sobre el botón se lanza la macro. También veremos el uso de ActiveCell.





ActiveCell hace referencia a la celda activa.

ActiveCell.Value = "Estoy aquí"

domingo, 29 de agosto de 2010

Nuevas formas de escribir con macro

Nuevas formas de escribir en las celdas de la hoja de cálculo con Macro. Usaremos Range y Cells combinadas. También veremos cómo se hace un comentario del programador escribiendo una comilla simple ('), o con Rem.

Para elevar a potencias usamos el acento circunflejo francés (^).



Código:

Sub escribe2()
Rem para elevar usamos el acento circunflejo francés ^
Range("C1:D6") = 2 ^ 3 + 10
Range("B8,B10,B12:C12") = "Madrid"
End Sub
Sub escribe3()
Range(Cells(1, 5), Cells(4, 8)).Value = "Hola"
Range("A5").Cells(3, 1).Value = 700
Range("A5:B10").Cells(2, 1).Value = "Bye"
End Sub


Escribir en un rango con un bucle For...Next

Si deseamos escribir en una serie de celdas, por ejemplo en el rango A1: A10 disponemos de varios métodos, que usan una variable, por ejemplo i, que hacemos variar entre 1 y 10.

  • Cells(i,"A") = "Pedido" & i
  • Range("B"&i) = "Envío " & i


viernes, 27 de agosto de 2010

Escribir en una hoja desde una macro

Podemos escribir en una hoja de Excel desde una macro. Para ello utilizaremos entre otros Range y Cells. También veremos cómo se procede cuando se ha producido un error al ejecutar una macro. Con Range podemos usar Value o no, aunque esta es una de las pocas veces que podremos saltarnos la estricta sintaxis de un lenguaje de programación.




Las macros comienzan por Sub seguido del nobre de la macro y un paréntesis abierto y otro cerrado. Dentro de los paréntesis normalmente no se escribe nada, aunque ya veremos más adelante que en ocasiones se escriben variables. La macro finaliza con End Sub. Entre ese comienzo y ese final se encuentran las líneas de comandos o sentencias. Al ejecutar una macro ésta se ejecuta secuencialmente de arriba hacia abajo.

Sub nombre_macro()
   Sentencia 1
   Sentencia 2
   Sentencia 3
   Sentencia 4
End Sub

El nombre de la macro no acepta espacios en blanco. Esto supone que, si son dos palabras, o bien se escriben juntas, o bien se separan con barra baja, como en nombre_macro.

Código:

Sub escribe()
Range("B1").Value = 100
Range("B2").Value = "Felicidades"
Range("B3") = 200
Range("B4") = "Bienvenido"
Cells(5, 2) = "Córdoba"
Cells(6, "B") = "Cádiz"
[B7] = 700
End Sub

lunes, 23 de agosto de 2010

Mi primera macro

Mi primera macro en Excel 2007. Lo primero que necesitamos en la versión 2007 de Excel es disponer de la ficha PORGRAMADOR dentro de la cinta de opciones. La ficha Programador no viene por defecto al instalar Excel, hemos de activarla a posteriori.

Para activar la ficha programador puede visitar un post anterior donde se explica esto:

Ficha Programador

Seguidamente se muestra en vídeo el procedimiento para crear nuestra primera macro.



Supongamos que se nos olvidó cerrar el paréntesis. En este caso el Editor de Visual Basic nos ayuda marcando en rojo la línea y mostrando un mensaje de error.


Nosotros hemos saludado diciendo "Buenos días" pero es muy habitual que el primer programa que se hace de un lenguaje de programación nos salude diciendo "Hola mundo" (en inglés Hello World).

Actualización para Excel 2010

En Excel 2010 seguiríamos los mismos pasos que se muestran en el vídeo para la versión de Excel 2007. En este aspecto no hay diferencias.

Actualización para Excel 2016

En la versiones nuevas de Excel la pestaña ya no se llama 'Programador', ahora la pestaña se denomina 'Desarrollador'.

jueves, 19 de agosto de 2010

Valoración de Rentas a tipo variable

Descargar el fichero: Rentas.xlsm
Calcular el Valor Actual y el Valor Final de una renta de cuantía constante es sencillo con las funciones de Excel VA y VF. Calcular el valor actual o final de una renta en la que el tipo de interés varía ya no es tan obvio. Para calcular el Valor Actual tendremos que capitalizar cada cuantía hasta el final de la renta, aplicando el tipo de interés vigente en cada momento a medida que la cuantía atraviesa los diferentes periodos hasta llegar al final (t=n).

Hoja E11

Nos vamos a la Hoja E11 del fichero Rentas.xls.Vamos a calcular el VA y el VF de una renta de 60 términos mensuales pospagables valorada a diferentes tipos de interés cada uno de los 5 años de duración. La cuantía de los términos de la renta también son variables siendo las mensualidades del primer año de 1.000 €, las del segundo año 1.200 €, y así con incrementos de 200 € cada año, hasta llegar al quinto año donde la cuantía mensual es de 1.800 €.

Utilizaremos dos métodos. El método 1 trabaja en meses, por lo que necesitamos una tabla de 60 meses. El método 2 trabaja en años, debido a que durante cada año la cuantía mensual es constante y el tipo dentro del año no varía. Esto nos permite valorar la renta de cada año al final de ese año, y así con los valores anualizados podemos trabajar como si se tratara de una renta anual.

Método 1 (trabajando en meses)


Columna B: Mes

Rellenamos los meses de 0 a 60. Comenzamos en cero, pero al tratarse de una renta pospagable la primera cuantía vence al final del primer mes, que corresponde al instante 1.

Columna C: Año

Se calcula con fórmula el año. Así la celda C8 contiene la siguiente fórmula:

=SI(RESIDUO(B8;12)=1;C7+1;C7)

Observar que en Excel siempre que deseamos tratar valores periódicos es muy frecuente utilizar la función RESIDUO que calcula el resto. Si estuviéramos programando en VBA usaríamos MOD (módulo) que en español se denomina resto.

Otra forma de conseguir la columna de años sin necesidad de fórmulas muy elaboradas es la siguiente. Durante los primeros 12 meses escribimos un 1. En la celda que se corresponde con el primer mes del segundo año (celda C20) pondríamos esta fórmula: =C8+1. Finalmente copiaríamos esta fórmula hacia abajo.

Columna D: Tipo anual

En nuestro caso el tipo de interés varía cada año comenzando en el 5%, e incrementándose un punto cada año, hasta llegar al 9% en el quinto año. Estos datos se encuentran en una tabla a la derecha. Tabla que posteriormente completaremos para obtener el método 2 de resolución. Creamos un nombre de rango denominado tabla que es: K7:S11.

La fórmula utilizada en la celda D8, que luego copiaremos hacia abajo, es la siguiente.

=BUSCARV(C8;tabla;2;0)

Esto nos proporciona el tipo de interés efectivo anual.

Columna E: Tipo mensual

Tomamos el tipo mensual de la tabla de la derecha como se ve en la fórmula de la celda E8:

=BUSCARV(C8;tabla;3;0)

Columna F: Renta

La celda F8 nos proporciona el importe de los términos de la renta, y para ello consulta la tabla de la derecha  con la siguiente fórmula.

=BUSCARV(C8;tabla;5;0)

Columna G: Factor

El factor en finanzas es igual a uno más el tanto. En este caso lo que hacemos es sumar 1 al tipo mensual de la columna anterior. En finanzas es muy típico trabajar con el famoso (1+i) que es el factor que elevado a exponente negativo y multiplicado por la cuantía, la capitaliza. Por el contrario, elevado a exponente negativo y multiplicado por la cuantía, lo que hace es descontarla, tantos periodos como indique el exponente.

La celda G8 es simplemente: =1+E8.

Columna H: VA


Calculamos el Valor Actual de cada término de la renta. Lo que hacemos es llevar financieramente hasta el origen de la renta (t=0) cada una de las cuantía que la componen. La peculiaridad en este caso es que el tanto es variable por lo que se ha de dividir (o multiplicar con exponente negativo) entre el producto de todos los factores que son necesarios para llegar hasta su valor actual.

En Excel existe la función PRODUCTO que multiplica todos los elementos que se indiquen en su rango. Así la celda H8 tiene la siguiente expresión:

=+F8/PRODUCTO($G$8:G8)

Esta fórmula se copia hacia abajo. Para analizar mejor la fórmula tomemos como ejemplo la de la celda H12:

=+F12/PRODUCTO($G$8:G12)

En esta fórmula se toma la cuantía de la celda F12 que son los 1.000 € que vencen en t=5 y se han de descontar 5 meses. Este es el motivo de que el argumento de la función PRODUCTO sea G8:G12.

Observar que en el argumento de la función PRODUCTO la celda G8 se fija con dólares. Esto es así para que al copiar la fórmula hacia abajo siempre se descuenten las cuantías de la renta hasta el momento inicial (t=0).

El Valor Actual de la renta se calcula en la celda P14 sumando todos los valores actuales individuales de todos los términos de la renta. Así P14 es:

=SUMA(H8:H67)

Columna I: VF

Para calcular el Valor Final de la renta capitalizamos cada cuantía hasta el final de la renta (t=60). Comencemos el razonamiento por el final. La celda I67 es simplemente una fórmula que se vincula con la cuantía que vence en ese momento (=+F67) sin necesidad de capitalizar, ya que la cuantía vence justo al final, en t=60.

La celda I66 tiene la siguiente fórmula.

=+F66*PRODUCTO(G67:$G$67)

En esta expresión lo que hacemos es capitalizar la cuantía hasta su valor final. Para ello utilizamos la función PRODUCTO y en su argumento debemos incluir el rango de los factores necesarios. Para la cuantía que vence en t=59, que está en la celda F66, se ha de multiplicar únicamente por un factor que es el que está en la celda G67. Pero como esta fórmula la copiaremos hacia arriba, hemos de poner el rango de factores como G67:$G$67 dentro de la función PRODUCTO. Esto hace que al fijar con dólares la celda G67 los factores implicados vayan siempre hasta el último.

Observar el desfase de filas en la fórmula. Cuando estoy capitalizando la celda F66 llego hasta el factor G67. Para entender esto, siempre es conveniente recordar que una cosa es un instante, por ejemplo el instante en el que vence una cuantía, y otra cosa es un intervalo de tiempo, por ejemplo el que va asociado a un factor mensual es todo un mes.


El Valor Final de la renta se calcula en la celda Q14 sumando todos los valores finales individuales de todos los términos de la renta. Así Q14 es:

=SUMA(I8:I67)



Método 2 (trabajando en años)

En este método podemos trabajar en años gracias a que la cuantía únicamente varía una ver al año, al igual que sucede con la variación del tipo de interés. Vamos a calcular el valor a final de cada año de los términos de la renta que vencen dentro de ese año. Así obtendremos unas cuantías anualizadas equivalentes financieramente a las 12 mensualidades de cada año. Con las anualidades calculadas podremos trabajar como si de una renta anual se tratara.


Las columnas K y L son datos.


Columna M: Tipo mensual

La celda M7 es:

=+(1+L7)^(1/12)-1

Lo que hacemos es considerar que el tipo anual es un efectivo anual y lo que nosotros pretendemos es calcular el efectivo mensual equivalente. Para ello se aplica la expresión: im=(1+i)^(1/m)-1.


Columna N: Factor Anual

Puesto que trabajaremos con la renta anualizada necesitaremos el factor anual que es (1+i).

Columna O: Renta

Los términos de la renta son datos.

Columna P: Renta Anual

La celda P7 tiene la siguiente expresión:

=+VF(M7;12;-O7)

Lo que hacemos es calcular el valor final con VF, ya que se trata de una renta de 12 términos mensuales constantes. El valor obtenido es el de la renta anualizada para el primer año. Luego lo que hacemos es copiar la fórmula hacia abajo y así obtenemos los 5 términos de la renta anualizada.

Columna Q: Renta Anual

La celda Q7 es:

=+P7/PRODUCTO($N$7:N7)

Aquí calculamos el valor actual del primer término de la renta anualizada. Ese término vence al final del primer año y para descontarlo utilizamos el factor anual, esto es, descontamos con el tanto efectivo anual. Esto fórmula se copia hacia abajo y así obtenemos el valor actual de cada una de las cuantías previamente anualizadas.

Columna R: Factor Capitaliz

En esta columna calculamos el Factor de Capitalización. La celda R7 es:

=+PRODUCTO($N8:N$11)

Esta fórmula se copia hacia abajo, con la excepción de la última celda. En la celda R11 debemos poner un 1 de forma manual, ya que si se copia la fórmula anterior hasta esta celda el proceso no funcionará bien. De hecho el motivo de la existencia de esta columna es precisamente este pequeño e importante detalle. Observar que en el caso del cálculo del VA no fue necesaria una columna calculando previamente el Factor de Descuento.


El Valor Actual de la renta se calcula en la celda P15 sumando todos los valores actuales individuales de todos los términos de la renta. Así P15 es:

=SUMA(Q7:Q11)


Columna S: VF

La celda S7 es:

=+R7*P7

Esta fórmula se copia hacia abajo y nos da el valor final de cada término de la renta anualizada.



El Valor Final de la renta se calcula en la celda Q15 sumando todos los valores finales individuales de todos los términos de la renta. Así Q15 es:

=SUMA(S7:S11)


Comprobación




La celda R14 es:

=+P14*PRODUCTO($N$7:$N$11)-Q14

Esta fórmula se copia a la R15 y si obtenemos cero indica que ambos método coinciden.

En la fórmula R14 lo que hacemos es tomar el valor actual calculado por el método 1 y capitalizarlo con el PRODUCTO de los factores anuales. Esto nos debería dar el valor final de la renta, por lo que al restar éste el resultado debe ser cero.

Puede realizar esta comprobación pero utilizando el producto de los factores mensuales. ¿Cuál es el resultado que debiera obtener con la siguiente expresión?

=+P14*PRODUCTO($G$8:$G$67)-Q14

Ejercicio propuesto

Proponemos al lector que realice este mismo caso pero suponiendo la renta prepagable, esto es, con 60 mensualidades pero venciendo la primera al inicio del primer mes (en t=0). Podrá comprobar que las ideas son las mismas pero hay que tener especial cuidado con los factores que se han de utilizar para descontar o capitalizar cada cuantía.

Texto en Columnas

Convertir texto en columnas. Es frecuente disponer de tablas que provienen de otras aplicaciones (contabilidad, almacén, RRHH, ...) y nos las dan en formato de texto, código ASCII, o también llamdao texto plano. En estos casos es necesario una conversión para poder trabajar en Excel. Lo que debemos hacer es convertir ese texto plano en columnas de la hoja de cálculo con las que podamos luego trabajar.

Existen tres métodos:

  1. Trabajar con funciones de cadena. Son las que se encuentran dentro de la categoría texto: EXTRAE, IZQUIERDA, DERECHA, LARGO, VALOR, ....
  2. Utilizar el asistente para convertir texto en columnas. Esta es la opción que veremos en este artículo.
  3. Crear una macro. Esta opción es adecuada para casos repetitivos en los que la macro ha de adaptarse a las peculiaridades del texto que nos llega de otra aplicación.

En el siguiente video se comenta el uso del Asistente para convertir texto en columnas.

domingo, 15 de agosto de 2010

Un test de Excel

Presentamos un test de 10 preguntas de Excel con un nivel inicial-medio. Se trata de un formulario creado con la hoja de cálculo de Google.



viernes, 13 de agosto de 2010

VAN y TIR, evitando errores

Descargar el fichero: v_vantir1.xlsx

Un error frecuente que se comete al calcular el VAN y la TIR es dejar vacia la celda en la que el flujo de caja que vence en ese momento es cero. Dicho de otro forma, si en un momento determinado el flujo de caja es cero, se ha de poner necesariamente ese cero, ya que si se deja vacia la celda el cálculo del VAN y de la TIR será erróneo.


martes, 10 de agosto de 2010

Tablas Dinámicas con Rangos de Consolidación Múltiples

Descargar el fichero: xPoblamun_TD.xlsx

Habíamos llegado a pensar que la posibilidad de crear Táblas Dinámicas con Rangos de Consolidación Múltiple había desaparecido con Excel 2007, pero no es así. En Excel 2007 y en Excel 2010 podemos crear Tablas Dinámicas con información que proviene de varias tablas que pueden estar en la misma hoja, en distintas hojas, o incluso en distintos libros. Esto es lo que se conoce como Rangos de Consolidación Múltiples.

Primero: obtengamos los datos

Vamos a visitar la página del periódico El Pais. Concretamente su Anuario que contiene información estadística y nos la proporcionan en formato de hoja de cálculo Excel. Su dirección es:



Vamos a tomar como datos de partida los correspondientes al anuario de 2007. Luego seleccionamos Economía dentro del apartado Internacional. Llegamos a una página en la que descargamos la hoja de cálculo correspondiente al apartado:

Países y territorios según población



Si todo va bien habremos descargado el siguiente fichero:

xPoblamun.xlsx

Nos centraremos en la Hoja3 que contiene información sobre:

Los países más poblados del mundo 1995-2030

Disponemos de cuatro tablas con los 30 países más poblados en 1995, 2005, 2015 y 2030. Algunos datos son reales y otros previstos.

Segundo: preparemos los datos

Preparamos un poco las tablas. Lo principal es poner en la fila 6 las cabeceras de las tablas. Indicamos la cabecera 'Pais' y para la población utilizamos como cabecera el año: 1995, 2005, 2015, 2030. La República Democrática del Congo la hemos abreviado por Congo. Al final la idea es obtener la información con el siguiente aspecto.


Tercero: veamos el proceso en Excel 2003

Vamos a lanzar el Asistente de Tablas Dinámicas. En Excel 2003 es sencillo, simplemente se ha de ir al menú Datos y allí veremos la opción que lanza el Asistente para Tablas y Gráficos Dinámicos.

El primer paso del asistente nos pregunta: ¿Dónde están los datos que desea analizar?. Elegimos 'Rangos de consolidación múltiple'.



En el segundo paso indicamos que deseamos un solo campo de página.



En la siguiente ventana nos preguntan ¿Dónde están los rangos de hoja de cálculo que desea consolidar?.




Vamos marcando cada rango y pulsando el botón Agregar, hasta tener incluidos los cuatro rangos correspondientes a las cuatro tablas.



En el tercer paso indicamos que deseamos el informe de tabla dinámica en una hoja de cálculo nueva.



Ya tenemos generada la tabla dinámica, aunque será necesario mejorar un poco. Por ejemplo la columna F de Total general aquí no tienen ningún sentido y tendremos que eliminarla. Pero antes de comenzar a mejorar la tabla veremos cómo se realiza este proceso en Excel 2007 y 2010.



Cuarto: veamos el proceso en Excel 2007 y Excel 2010

En Excel 2007 y en Excel 2010 en la Cinta de Opciones se encuentra la pestaña Insertar y  a su izquierda podemos ver la opción Tabla dinámica




Al pulsar sobre 'Tabla dinámica' obtendremos la siguiente ventana



En esta ventana no encontramos la opción de 'Rangos de Consolidación Múltiple'. Por tanto, abandonaremos esta vía.

Para obtener la opción de 'Rangos de Consolidación Múltiple' debemos llegar hasta el Asistente para Tablas y Gráficos dinámicos. Esto se consigue incluyendo en la Barra de Herramientas de Acceso Rápido el icono correspondiente a este asistente.

Inicialmente la Barra de Herramientas de Acceso Rápido contiene muy pocos icono, pero podemos personalizarla. Su imagen en Excel 2010 es la siguiente.




Pulsamos sobre el desplegable que pone 'Personalizar barra de herramientas de acceso rápido'.




Seleccionamos la opción 'Más comandos ...'



Así llegamos a obtener la siguiente ventana.



En 'Comandos disponibles en:' elegimos del desplegable 'Todos los comandos'. Encontraremos muchísimos iconos ordenados alfabéticamente. De entre todos ellos elegiremos 'Asistente para tablas y gráficos dinámicos' y con el botón Agregar le pasaremos al recuadro de la derecha, y aceptaremos.




De esta forma habremos conseguido en la barra de herramientas de acceso rápido el icono correspondiente al Asistente para tablas y gráficos dinámicos.





Quinto: usemos el Asistente

Ya podemos utilizar el icono con el Asistente para tablas y gráficos dinámicos. Al pulsarlo se obtiene esta ventana en Excel 2010.


Donde podemos ver la tan ansiada opción de 'Rangos de consolidación múltiple' que nos permitirá trabajar con tablas de datos que se encuentren en varias hojas, o incluso en varios libros.


En el segundo paso elegimos un solo campo de página.


Agregamos los cuatro rangos correspondientes a las cuatro tablas.


En el paso 3 pedimos crear la tabla dinámica en hoja de cálculo nueva.



En Excel 2010 obtenemos al siguiente tabla dinámica.


Eliminamos la columna de Total general, y añadimos una columna que nos de el porcentaje de variación de la población entre 1995 y 2030. Así obtenemos la siguiente tabla.


Los colores de las celdas se han añadido de forma manual y nos permiten efectuar un análisis de la evolución de la población para los próximos años. Es sorprendente ver la caída de la población en Rusia, y que India iguala o incluso supera a China.

Nota

También se puede obtener el Asisten para tablas y gráficos dinámicos en versiones posteriores a Excel 2003 pulsando la combinación de teclas ALT+T+B.