EXCEL Y OFIMATICA

TIR o TIE

2008-01-29T11:31:00.000+01:00

TIPO DE INTERES EFECTIVO: o Tasa Interna de Rendimiento. Es el tipo de actualización que iguala el valor en libro de un instrumento financiero con los flujos de efectivo destinados a lo largo de la vida del instrumento, en su cálculo se incluirá las comisiones financieras que se carguen por adelantado en la concesión de financiación.
.

Voy a intentar explicar como realizar y como calcular el tipo de interés efectivo partiendo del siguiente ejemplo:

Préstamo de 10.000.- euros con unos gastos de formalización de 500.- euros y con un tipo de interés inicial de 4,75% con una duración de 2 años.

Para ello elaboramos los siguientes cuadros con los datos arriba indicados, subrayando en rojo las funciones que considero importantes:

Para calcular la anualidad utilizaremos la siguiente formula, en la celda

U5 =PAGO(U4;U3;U2).

Realizaremos el siguiente cuadro de amortización:

Donde utilizaremos las siguientes formulas:

B4 = E3*U4

B5 = E4*U4

C4 = D4-B4

C5 = D5-B5

D4 = U5*-1

D5 = U5*-1

E3 = U2

E4 = E3-C4
E5 = E4-C5

A continuación realizaremos el cuadro del cálculo de tipo de interés efectivo, con las siguientes fórmulas.

H3 = 500-U2 ==> (500 corresponde a los gastos de formailización)

H4 = D4

H5 = D5

J3 = H3

J4 = H4/(1+$H$7)^I4

J5 = H5/(1+$H$7)^I5

Aquí viene la función importante para calcular el tipo de interés efectivo.

H7 = TIR(H3:H5)

Y por último elaboramos el siguiente cuadro de amortización, con las siguientes fórmulas.

N4 = P3*$H$7
N5 = P4*$H$7

O4 = H4

O5 = H5

P3 = J3*-1

P4 = J4

P5 = J5

Tabla Dinámica

2008-01-24T09:56:00.001+01:00

Voy a poner un ejemplo sencillo para aprender a utilizar las tablas dinámicas, que son muy útiles. Para empezar a crear una tabla dinámica tendremos que partir de unos datos que se encontrarán en un formato tabla, es decir, que los datos se encontrarán encolumnados y la primera fila corresponderán al nombre de los campos. Por ejemplo:

Iremos al asistente de tablas dinámicas que se encuentra en excel en: Menu/datos/informe de tablas y gráficos dinámicos. Aparecerá la siguiente pantalla:

Le damos siguiente y aparecerá una nueva ventana que nos pide que identifiquemos el rango de los datos a utilizar para la tabla dinámica, por lo que seleccionaremos toda la tabla como se muestra en la imagen de abajo, una vez realizado le damos siguiente.

Aparecerá la siguiente pantalla que nos da la opción de que la tabla dinamica aparezca en una hoja nueva dentro del mismo libro donde tenemos los datos a tratar o en la misma hoja abierta, en este caso le damos en la misma hoja de cálculo existente y a continuación le indicamos en el lugar donde aparecerá la tabla dinámica que en este caso será A1

y a continuación le damos en Diseño y aparecerá la siguiente pantalla.
Colocaremos arrastrando los 3 campos de la derecha en pagina, en fila y en datos como se muestra en la imagen de abajo.
Una vez colocados los campos le damos a aceptar.

y volvera a salir esta ventana, por lo que le damos finalizar. y ya esta. Aparecerá la siguiente tabla como se muestra en la imagen de abajo.
Podremos filtrar la información si apretamos los desplegables que aparecen en los campos de fecha o clientes, en este caso si abirmos el desplegables de clientes aparecerá la siguiente ventana como se muestra en la imagen de abajo.

También podremos manipular la información que se muestra, lo que haremos es arrastrar el campo cliente a donde estaba el campo fecha y el campo fecha donde estaba el campo cliente, a continuación filtraremos la información del campo fecha seleccionando el día 2/1/08, quedará como se muestra en la siguiente imagen.
Podremos poner también el campo fecha al lado izquierdo del campo cliente. Si nos agobia los totales podremos quitarlos pulsando dos veces sobre el campo fecha y aparecerá una ventana " campo de la tabla dinamica", como se muestra en la imagen de abajo

si le damos a ninguno lo que conseguiremos es que no aparezcan subtotales por el campo fecha.

¿cuando se actualizan los datos de la tabla dinámica?, cada vez que habrimos la hoja de calculo donde se encuentra la tabla dinámica o cada vez que actualizamos la tabla dinamica. Si cambiamos el dato de pepe del día 2/1/08 por 1000 en la tabla dinámica no se actualiza, para ello pulsaremos con el boton derecho sobre los datos de la tabla dinamica y aparecera la siguiente ventana como se muestra en la imagen de abajo, le damos a Actualizar datos y ya está

La tabla dinámica se ha actualizado como vemos ahora en la imagen de abajo.

XLSX a XLS, Convertir OFFICE 2007 a versiones anteriores

2007-10-22T12:04:00.000+02:00

Si intenta habrir archivos realizados en office 2007 con versiones anteriores al 2007 y aún no ha instalado las actualizaciones y los convertidores, cuando intente abrir un libro de Excel 2007, es posible que le diga que no o que instale alguna acutalización o algún convertidor.
.
Después de instalar las actualizaciones y los convertidores, podrá abrir los libros de Excel 2007 y guardarlos o copiarlos a una versión anterior..
.
Pero OJO. Si tienes alguna version no oficial (pirata). nunca debes de realizar la actualización, deberas bajarte solo el convertidor.
.
Para descargar y usar convertidores de archivos pinche aquí y descargue el paquete de compatibilidad .Yo recomiendo que se guarde (asi siempre podrá tener el archivo guaradado para cualquier necesidad) y luego ejecutarlo desde la ubicación guardada.

HTM a EXCEL

2007-10-22T08:49:00.000+02:00

Para convertir datos de un archivo en htm a excel es muy sencillo. Deberemos seleccionar todo los datos del archivo en HTM y habrir una hoja en excel y luego tan simple como pegarlos en la hoja abierta de excel.

Posteriormente deberemos tratar un poco los datos, para ello debemos ir al menu de Formato > de la barra de herramientas, luego celda> y en la ventana que se nos abre elegimos la pestaña de Alineación> y desmarcamos todas las casillas del control de texto.

Luego debemos de organizar los datos ya que aparecerán datos que no nos interesa, filas en blanco o incluso columnas en blanco. Este punto es muy personal pero yo suelo incluir una columna a la izquierda donde la enumero todas las filas de los datos que voy a tratar y le doy nombre a todas las columnas tanto vacías como no vacías. A partir de aquí empiezo a ordenar columnas, eliminando las filas que no interesan.

mostrar filas ocultas

2007-10-17T10:00:00.000+02:00

Muestra todas las filas ocultas de una hoja especifica y de un rango de filas entre 1 y 1000.

Application.ScreenUpdating = False
Sheets("nombre hoja").Select
Rows("1:1000").Select
Selection.EntireRow.Hidden = False
Range("a1").Select
Application.ScreenUpdating = True

Eliminar y ocultar filas por condición

2007-10-17T09:29:00.000+02:00

Para eliminar filas o ocultar filas de una columna especifica, por una condición, utilizaremos el siguiente código. Teniendo en cuenta que la columna especifica que utilizaremos para evaluar la condición será la COLUMNA "A" y la condición a utilizar será el encontrar en las filas de la columna A la condicion escrita "v"

Dim rngString As Range
Do
Set rngString = Columns("a").Find("v", MatchCase:=False, _ LookAt:=xlPart, LookIn:=xlValues)
If Not rngString Is Nothing Then

A continuación y en la misma macro podremos utilizar una de estas dos opciones dependiendo si queremos eliminar las filas (.delete) o si queremos solamente ocultar las filas (.hidden =true).
1. Rem para eliminar filas>
rngString.EntireRow.Delete
o
2. Rem para ocultar las filas>
rngString.EntireRow.Hidden = True

Y continuación de la macro ponemos lo siguiente...
End If
Loop Until rngString Is Nothing

Cambiar la fuente y el tamaño

2007-10-17T09:19:00.000+02:00

Cambiar la fuente y el tamaño de un rango especifico (en este caso es desde B4 a U1000) y la fuente será "times new roman" y el tamaño a 8.

Range("B4:U1000").Select
With Selection.Font
.Name = "Times New Roman"
.Size = 8 .Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Range("B4").Select

Borrar fuentes, bordes y sombreados

2007-10-17T09:05:00.000+02:00

Con este codigo barraremos un area seleccionada previamente por nosotros (se puede incluir en una macro) para borrar todo: fuentes, colores de fuentes, bordes y sombreados de celdas.

Selection.Font.ColorIndex = 0
Selection.Interior.ColorIndex = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Clear

escribir en un lugar especifico

2007-10-17T08:36:00.000+02:00

Escribir en un lugar especifico, en esta caso en la celda C2, un texto predeterminado a traves de una formula de texto, luego bajamos a la celda de abajo C3 la celda activa.

Range("C2").Select
ActiveCell.FormulaR1C1 = "escribir texto"
Range("C3").Select

Copiar una Hoja en una Hoja nueva

2007-10-16T19:33:00.000+02:00

Crearemos una hoja nueva dandole nombre y copiaremos todo los datos de otra hoja con el nombre especifico y lo pegaremos en la hoja nueva creada.

Sheets.Add.Name = "nombre hoja nueva"
Sheets("nombre de la hoja a copiar").Select
Cells.Select
Range("C1").Activate
Selection.Copy
Sheets("nombre de la hoja nueva").Select
Cells.Select
ActiveSheet.Paste
Range("a1").Activate

Añadir Hoja nueva

2007-10-16T18:44:00.000+02:00

Para añadir una hoja nueva en nuestro libro abierto dandole un nombre especifico que nosotros querramos es:

Sheets.Add.Name = "poner nombre"