Archive for category excel
Excel – CS-Solver de VBA a VSTO
Hace ya algún tiempo que escribí CS-Solver, estos días me he propuesto convertirlo de VBA a VSTO.
CS-Solver es un add-in para Excel que escribí hace algún tiempo, ya que la calculadora que usaba tenía un sistema de resolver ecuaciones muy parecido (PowerOne finance) y sin embargo resolver ecuaciones con Excel era bastante más complicado.
CS-Solver utiliza el método secante para encontrar las raíces de f(x)=0 en un intervalo, este no es tan rápido como el de Newton, pero si más rápido que la bisección ya que acota el intervalo y usa la aproximación más reciente.
A diferencia del método de Newton, no necesitamos calcular la derivada de la función en cada iteración, y que es un proceso más tedioso y requiere cálculo adicional.
Veamos ahora como funciona CS-Solver con un pequeño ejemplo, resolviendo la siguiente ecuación
2^x + 5x = 2
Primero crearemos una celda C2 a la que daremos un nombre en este caso X, esta es la incógnita, en otra celda teclearemos la función, igualando el resultado a 0, quedando la ecuación 2^x+5x-2=0, esta celda la introduciremos como texto.
Ahora desde el menú de CSSolver, seleccionamos resolver ecuación
En donde se nos pide que introduzcamos la celda que contiene la ecuación a resolver, una vez seleccionada la celda que contiene la ecuación C4 pulsamos sobre Aceptar
El resultado aparece en la celda de nombre X que es la incógnita a resolver.
Veamos ahora otras cosas que se puede hacer con CS-Solver. Para ello usaremos la formula del Interés compuesto y calcularemos el Valor Futuro de un Capital invertido al 5% de interés anual durante 3 meses.
Valor Final = Valor Actual * ( 1 + (Interés/12))^Meses
Teclearemos la formula igualada a 0, 0=(vactual*(1+interes))^meses)-vfinal para ello, nombraremos la celda C2 como vfinal, C3 como vactual, C4 como interes y C5 como meses
Si ejecutamos CS-Solver y seleccionamos B7 como la formula a resolver obtendremos el Valor Actual, solucionando la ecuación.
Bien, una vez obtenido el resultado deseamos vemos que 1012,55 no es lo esperado que necesitamos obtener al final de nuestra inversión un total de 1125, así que queremos ver cuanto necesitamos ingresar ahora para poder retirar dentro de 3 meses 1125
Para ello, nos basta con eliminar el contenido de la celda C3, (vactual) e indicar en la celda C2 el importe que deseamos obtener. Nuestra hoja de excel quedaría así.
Bien, no tenemos que tocar nada más tan solo seleccionar CS-Solver e indicar la formula que ya habíamos escrito, B7
CS-Solver comprueba cual es la incógnita de la ecuación, vactual en este caso y comprueba que no tiene valor, así que su objetivo es resolver dicha incógnita.
CS-Solver incorpora también una nueva formula llamada CSSolver con la cual podemos tener en una celda el valor de la incógnita de la ecuación, de este modo se puede tener en una celda =CSSolver(“2*x*sin(x)-5”) para la ecuación 2*x*sin(x)=5 e incluso podemos vincular la formula con una celda =CSSolver(A5)
Estoy pasando el código de VBA a VSTO así que en unos días espero tenerlo terminado. (Excel)
Excel – Distribuciones, planificaciones, formulas matriciales
No se si lo he comentado alguna vez, pero en el trabajo suelo usar mucho Excel para hacer una gran variedad de cosas, en general mucho de lo que tiene que ver con el área económico financiera (lo terminamos haciendo en Excel).
Muchas de nuestras hojas de cálculo, son planes financieros, estudios de viabilidad, análisis etc. Básicamente estas hojas planifican cobros y pagos ó gastos e ingresos, la cuestión es la planificación, en Excel podemos planificar todo aquello que queramos, es sencillo podemos establecer una fila para cada previsión a realizar y en cada columna podemos poner un periodo (ene, feb, mar…), por último en cada celda (previsión / periodo) el importe de dicha previsión.
El problema se complica un poco cuando el importe de cada previsión no tiene un periodo fijo, sino que este debe establecerse en función de otra variable. Supongamos que tenemos que realizar una previsión entre dos periodos dados (Inicio y Final) y el importe debe ser proporcional al número de periodos.
Como es lógico las formulas se nos van complicando, más y más en función de las condiciones que necesitamos. Con lo que terminamos creando nuestras funciones en VBA para simplificar el proceso.
Function csPDistB(importe As Integer, periodo As Integer, inicio As Integer, fin As Integer)
If (periodo >= inicio) And (periodo <= fin) Then
csPDistB = importe / ((fin – inicio) + 1)
End If
End FunctionRealizaría el mismo trabajo que las formulas vistas. Dándole una vuelta más podemos crear una función matricial para hacer la misma tarea y que automáticamente tome el periodo actual en función del rango en donde se encuentre.
Public Function csPDistB(importe As Variant, primero As Integer, ultimo As Integer) As Variant
Dim i As Integer
Dim nPeriodos As Integer
Dim valor As Double
ReDim a(0 To Application.Caller.Rows.Count, 0 To Application.Caller.Columns.Count) As Variant
On Error GoTo Handler
nPeriodos = ultimo – primero
valor = CDbl(importe / (nPeriodos + 1))For i = 0 To Application.Caller.Columns.Count
If i + 1 >= primero And i + 1 <= ultimo Then
a(0, i) = valor
End If
NextcsPDistB = a
Exit Function
Handler:
csPDistB = CVErr(2015) ‘xlErrNum = 2036End Function
Por último con una pequeñas modificaciones sobre este código podemos crear funciones más complejas para nuestras planificaciones, por ejemplo distribuciones en función de una curva de porcentajes, 25%, 50% y 25% sería el 25% en el primer tercio, el 50% en el segundo tercio y el 25% en el tercer tercio del tiempo.
Public Function csPDistCP(importe As Variant, ParamArray porcentajes()) As Variant
Dim i As Integer
Dim nParte As Integer
Dim p As Integer
ReDim a(Application.Caller.Columns.Count) As Variant
On Error GoTo Handler
nParte = (UBound(a) + 1) / (UBound(porcentajes) + 1)
p = -1
For i = 0 To UBound(a)
If i Mod Int(nParte) = 0 Then
If p < UBound(porcentajes) Then
p = p + 1
End If
End If
a(i) = CDbl((importe / nParte) * porcentajes(p)) NextcsPDistCP = a
Exit Function
Handler:
csPDistCP = CVErr(2015) ‘xlErrNum = 2036End Function
Un último ejemplo en donde realizamos previsiones los periodos indicados, el importe proporcional al número de periodos.
Public Function csPDistP(importe As Variant, ParamArray periodos()) As Variant
Dim i As Integer
Dim nPeriodos As Integer
Dim valor As Double
ReDim a(Application.Caller.Columns.Count) As Variant
On Error GoTo Handler
nPeriodos = UBound(periodos)
valor = CDbl(importe / (nPeriodos + 1))For i = 0 To nPeriodos
a(periodos(i)) = valor
NextcsPDistP = a
Exit Function
Handler:
csPDistP = CVErr(2015) ‘xlErrNum = 2036End FunctionExcel – Generando MDX
Posted by cseg in excel, sharepoint on February 20, 2006
Continuando con el trabajo que estaba realizando para mostrar información del Analisys Services en SharePoint, he realizado un pequeño programa en Excel para exportar las sentencias MDX (Nota: todavía no he visto el editor del SQL2005 y puede que este sea una maravilla, el del SQL2000 sin comentarios), con lo cual puedo usar Excel con una tabla dinámica para diseñar el informe y exportar este para usarlo con el webpart de OLAP que estaba realizando. Usando el contenido del un .dwp podríamos generar un webpart completo para importar en SharePoint.
Este ejemplo generará un archivo XML con la definición del informe con esta sintaxis.
<?xml version=”1.0″ encoding=”utf-8″?>
<informe>
<nombre>Informe de Prueba</nombre>
<descripcion>Informe de ventas de la base de datos FoodMart 2000</descripcion>
<conexion>
OLEDB;Provider=MSOLAP.2;Persist Security Info=True;Data Source=srvdatos;Initial Catalog=FoodMart 2000;
Client Cache Size=25;Auto Synch Period=10000
</conexion>
<mdx>
<![CDATA[
SELECT NON EMPTY HIERARCHIZE({DrillDownLevel({[Product].[All Products]})})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,
NON EMPTY HIERARCHIZE({DrillDownMember(
{{DrillDownMember({DrillDownLevel({[Customers].[All Customers]})},
{[Customers].[All Customers].[USA]})}}, {[Customers].[All Customers].[USA].[CA]})})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS
FROM [Warehouse and Sales] WHERE ([Measures].[Sales Count])
]]>
</mdx>
</informe>
Pasos para hacer nuestro diseñador de informes.
1.- Abrir excel
2.- Crear un informe de tabla dinámica usando el asistente de excel
3.- Configurar el informe a nuestro gusto
3.- Llamar a la macro de exportar informe (yo le he hecho un bonito formulario …)
Sub ExportaXML(sNombre As String, sDesc As String)
Dim xmlDoc As DOMDocument
Dim xmlNode As IXMLDOMNode
Dim xmlAttribute As IXMLDOMAttribute
Dim xmlInforme As IXMLDOMNode
Dim xmlPi As IXMLDOMProcessingInstruction
Set xmlDoc = New DOMDocument
Dim xmlText As IXMLDOMText
Dim sArchivosArchivo = Application.GetSaveAsFilename(“”, “Archivo MDX,(*.xml)”, , “Archivo MDX en XML”)
If sArchivo <> False ThenSet xmlPi = xmlDoc.createProcessingInstruction(“xml”, “version=”“1.0″“”)
Set xmlNode = xmlDoc.appendChild(xmlPi)Set xmlInforme = xmlDoc.createElement(“informe”)
Set xmlNode = xmlDoc.appendChild(xmlInforme)
Set xmlNode = xmlDoc.createElement(“nombre”)
Set xmlText = xmlNode.appendChild(xmlDoc.createTextNode(sNombre))
Set xmlNode = xmlInforme.appendChild(xmlNode)Set xmlNode = xmlDoc.createElement(“descripcion”)
Set xmlText = xmlNode.appendChild(xmlDoc.createTextNode(sDesc))
Set xmlNode = xmlInforme.appendChild(xmlNode)Set xmlNode = xmlDoc.createElement(“conexion”)
Set xmlText = xmlNode.appendChild(xmlDoc.createTextNode(ActiveSheet.PivotTables(1).PivotCache.Connection))
Set xmlNode = xmlInforme.appendChild(xmlNode)Set xmlNode = xmlDoc.createElement(“mdx”)
Set xmlText = xmlNode.appendChild(xmlDoc.createTextNode(ActiveSheet.PivotTables(1).MDX))
Set xmlNode = xmlInforme.appendChild(xmlNode)xmlDoc.Save (sArchivo)
End IfEnd Sub
