jueves, 26 de abril de 2018

VBA: Método de Newton-Raphson en Excel

Un usuario planteaba dudas por cómo emplear en Excel el método de Newton-Raphson (ver Wikipedia) para resolver ecuaciones y encontrar su raíz.

Si trasladamos esos algoritmos de cálculo a nuestras macros, en modo de función, tendríamos las UDF que veremos a continuación...
El algoritmo se basa, de acuerdo a lo que se indica en Wikipedia, a un método recurrente geométrico... desarrollado a partir de otro método de cálculo (método de la secante).
Seguro que algún experto matemático podrá aportar mucho más al respecto.


En mi ejemplo, operaremos para obtener la raíz de la siguiente ecuación:
f(x)=LOG(x-1)-COS(x-1)

Nota: la función de VBA LOG equivale al logaritmo neperiano (o natural) en base e (2,718281828459)

y sabiendo que su función derivada es:
f'(x)=1/(x-1)-SIN(x-1)




En un módulo estándar del libro incluimos las siguientes UDF:

Function func(x) As Double
'Nuestra función a analizar
func = Math.Log(x - 1) + Math.Cos(x - 1)

End Function
Function func_derivada(x) As Double
'la derivada de nuestra función
func_derivada = 1 / (x - 1) - Math.Sin(x - 1)

End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function NewtonRaphson(x_0 As Double, tolerancia As Double, iteracionMax As Long)
'1er argumeto x_0: el valor inicial desde donde parte el recorrido buscando la raíz/solución.
'2do argumento tolerancia: el margen de error buscado.
'3er argumento iteraciónMax: el límite de iteraciones máximo permitido buscando la raíz/solución
Dim x_n1 As Double, x_n As Double
Dim error_dif As Double

Dim contador As Integer
x_n = x_0
contador = 0
'iniciamos el bucle...
Do
    'método Newton-Raphson
    x_n1 = x_n - func(x_n) / func_derivada(x_n)
    'paremetrizamos el error
    error_dif = x_n1 - x_n
    'y asignamos nuevo valor para el siguiente paso iterativo
    x_n = x_n1
    'incrementamos el contador para controlar una salida del bucle
    contador = contador + 1
Loop Until Math.Abs(error_dif) <= tolerancia Or contador = iteracionMax
'hemos salido del bucle si la diferencia entre los elementos es menor de la tolerancia determinada
' o la iteración llega al máximo indicado(iteracionMax).

'finalmente damos valor a nuestra función
NewtonRaphson = x_n1
End Function

Podemos verificar nuestra solución si insertamos en la celda C22 la fórmula: =NewtonRaphson(C3;0,000001;100)

VBA: Método de Newton-Raphson en Excel

Una alternativa, mediante funciones sin programación, es hacerlo directamente sobre la hoja de cálculo, como se ve en la imagen anterior.

En la celda C3 disponemos el primer valor a tomar 1,01 para nuestro ejemplo, y en la celdas sucesivas C4:C18 añadimos:

=C3-D3/E3

que respondería al algoritmo del método de Newton...

En las columnas C y D añadiríamos la formulación de la función principal y la función derivada, respectivamente para los valores obtenidos en la columna B.

En otro post, describiré el proceso manual.

Por otra parte, si hacemos la comprobación gráfica de la raíz, vemos que efectivamente, cuando x=1,397748475 la función f(x)=LOG(x-1)-COS(x-1) se hace cero.

VBA: Método de Newton-Raphson en Excel

martes, 24 de abril de 2018

Alto de fila y Ancho de columna

Vamos a configurar nuestro alto de fila y ancho de columna para nuestras hojas... dos aspectos frecuentes, que tocamos rutinariamente, pero del que desconocemos bastante.

Comencemos por el típico Ancho de columna.
Podemos configurar una o varias columnas, o todas las columnas de la hoja.
Basta seleccionar aquellas columnas que queremos afectar y con el clic derecho del ratón buscar la opción: Ancho de columna...

Alto de fila y Ancho de columna


En la ventana siguiente indicaremos el ancho deseado (ver post)

Otra posibilidad es acceder, en la cinta de opciones, a la Ficha Inicio > grupo Celdas > desplegable Formato

Alto de fila y Ancho de columna


Aquí disponemos, respecto del ancho de columna, de tres alternativas:
1-Ancho de columna...: equivalente a hacer clic derecho del ratón. Nos permite sobre las columnas seleccionados configurar el ancho.
2-Autoajustar ancho de columna: Se ajustará cada columna seleccionada al ancho necesario según los caracteres máximos existentes columna por columna.
3-Ancho predeterminado...: Sin necesidad de seleccionar nada en particular, aquí definiremos el ancho de todas las columnas de la hoja.


Todos estos cambios afectarán únicamente a la hoja activa, sobre la que realizamos las modificaciones!!.

Vamos ahora a por el Alto de fila.
Respecto del alto de fila podemos actuar de forma similar, i.e., seleccionamos una o varias filas, y al hacer clic derecho buscaremos la opción de Alto de fila..., y en la ventana diálogo siguiente indicaremos el alto deseado (ver post).

Alto de fila y Ancho de columna



Igualmente al ancho de columna también podemos acceder desde la Ficha Inicio > grupo Celdas > desplegable Formato

Alto de fila y Ancho de columna


Disponemos, respecto del alto de fila, de dos opciones:
1-Alto de fila: equivalente a hacer clic derecho del ratón. Nos permite sobre las filas seleccionados configurar su alto.
2-Autoajustar alto de fila: que retorna al alto de fila por defecto!!.


En este punto es donde aparece una singularidad del alto de fila autoajustado, y es que al autoajustar, el alto de fila se configura con el alto predefinido!!.

Pero, ¿cuál es ese alto de fila por defecto?, ¿dónde lo puedo configurar?.


Pues no existe una opción como tal que me lo permita.. pero si algún truco que dejará nuestro alto a la medida deseada...
Y todo pasa por modificar la fuente y el tamaño de esta.


Posibilidades.
1- Cambiar el alto por defecto de una hoja:
Para ello basta cambiar el tamaño de la fuente (aumentar o disminuir) para esa hoja, previa selección de toda la hoja.

2- Cambiar el alto por defecto del libro completo:
Iremos a los Estilos de celda en Ficha Inicio > grupo Celdas > desplegable Estilos de celdas > buscar el estilo Normal.

Alto de fila y Ancho de columna


A continuación lo modificaremos desde 'Formato' y en la ventana de Formato de celdas, desde la pestaña Fuente , cambiamos Tamaño y Fuente

Alto de fila y Ancho de columna


3- Cambiar el alto por defecto de cualquier libro:
Accederemos a las Opciones de Excel, desde la ficha Archivo > Menú General > sección Al crear nuevos libros > opción Tamaño y Fuente.

Alto de fila y Ancho de columna



Lo interesante del caso, cambiar alto de fila por defecto, es que dependerá del tipo de fuente y del tamaño que marquemos.. ya que en función a estas características el alto predeterminado será uno u otro.

jueves, 19 de abril de 2018

Filtro Avanzado Registros Únicos

Días atrás un usuario planteaba una cuestión interesante.
Preguntaba por la forma de aplicar un filtro avanzado para obtener registros únicos.
[...]Realice un filtro avanzado en Excel, en donde utilicé criterios de varias columnas, y con valores únicos.
Para comprobar si estaba correcto, concatené las columnas utilizadas para el filtro avanzado, copie y pegué como texto, y le apliqué formato condicional, duplicar valores y me aparecen 5 registros repetidos.[...]


Partiremos de la siguiente 'base de datos':

Filtro Avanzado Registros Únicos



La idea propuesta es obtener un listado de los registros únicos de acuerdo a un 'id' múltiple, que para nuestro ejemplo estará compuesto de los campos Zona+Comercial+Producto.
Es indiferente que apliquemos o no criterios sobre nuestra herramienta: Filtro Avanzado, ya que opera indistintamente como veremos.


Para el primer caso no aplicaremos criterio alguno, solo obtendremos de la totalidad de la base de datos aquellas combinaciones únicas de esos tres campos deseados: Zona+Comercial+Producto.

Así pues preparamos el destino de nuestro listado, copiando y pegando esos tres encabezados:

Filtro Avanzado Registros Únicos



Con la hoja preparada seleccionamos la base de datos (basta marcar una celda cualquiera de ella) y accedemos a la ficha Datos > grupo Ordenar y Filtrar > botón Avanzadas...
Es nos abrirá la ventana del Filtro avanzado que configuraremos como sigue:
1-Copiar a otro lugar
2-Rango de la lista: $B$2:$F$22 (esto es, la base de datos con el encabezado incluido!!)
3-Rango de criterios: vacío para este primer ejemplo (es decir, trabajamos sobre la totalidad de la base de datos)
4-Copiar a: $I$2:$K$2 (son los tres campos que componen nuestro id múltiple).
5-Solo registros únicos (marcado).

Filtro Avanzado Registros Únicos



Tras aceptar vemos en nuestro rango destino como se han volcado, para esos tres campos, las combinaciones únicas obtenidas desde la base de datos...

Filtro Avanzado Registros Únicos


Se puede comprobar fácilmente como esas doce combinaciones de tres elementos son las únicas existentes en nuestro origen...

De manera similar podríamos, además, haber aplicado unos criterios de filtro, para obtener las combinaciones únicas de solo aquellos registros que cumplan aquellos criterios...
Veamos la imagen siguiente, donde se especifica un criterio por Zona, y donde queremos listar solo las combinaciones/registros únicos de éstos.
1-Copiar a otro lugar
2-Rango de la lista: $B$2:$F$22 (esto es, la base de datos con el encabezado incluido!!)
3-Rango de criterios: $I$2:$M$3 (donde incorporamos los criterios deseados)
4-Copiar a: $I$7:$K$7 (son los tres campos que componen nuestro id múltiple).
5-Solo registros únicos (marcado).

Filtro Avanzado Registros Únicos



El resultado tras aceptar:

Filtro Avanzado Registros Únicos


Obteniendo el listado de combinaciones únicas que verifican nuestros criterios para esos tres campos Zona+Comercial+Producto.