Fractales: El Helecho de Bransley - Excel

Aplicaremos hoy un cálculo recursivo para mostrar uno de los fractales más famosos y sencillos: el Helecho de Barnsley.

Es interesante leer algo más de este gran matemático y de su fractal más famoso en Wikipedia.

Si has leído algo al respecto verás que se trata de aplicar una serie de fórmulas dependiendo de una distribución probabilística que se basa en el siguiente cuadro:

Nuestras cuatro fórmulas serán entonces:
0. El punto original será
x₀ = 0
y₀ = 0

1. Para probabilidades acumuladas entre 0,99 a 1 (probabilidad del 1%)
x_n+1=0
y_n+1=0,16 * y_n

2. Para probabilidades acumuladas entre 0,14 y 0,99 (probabilidad del 85%)
x_n+1=0,85 * x_n + 0,04 * y_n
y_n+1= -0,04 * x_n + 0,85 * y_n+ 1,6

3. Para probabilidades acumuladas entre 0,07 y 0,14 (probabilidad del 7%)
x_n+1=0,2 * x_n – 0,26 * y_n
y_n+1= 0,23 * x_n + 0.22 * y_n+ 1,6

4. Para probabilidades acumuladas inferiores a 0,07 (probabilidad del 7%)
x_n+1=-0,15 * x_n + 0,28 * y_n
y_n+1= 0,26 * x_n + 0,24 * y_n+ 0,44

Así pues construimos nuestro modelo a partir de la función ALEATORIO(), la culal nos permitirá obtener una de la cuatro funciones descritas...

En el rango A3:A6001 insertamos la función:
=ALEATORIO()

En las celdas B2 y C2 insertamos el punto (0,0) inicial con valores no formulados.

Y en B3:B6001 añadimos el componente de la equis de nuestras funciones, así en B3 añadimos para luego arrastrar:
=ELEGIR((A3>0,99)+(A3>0,14)+(A3>0,07)+(A3>0);-0,15*B2+0,28*C2;0,2*B2-0,26*C2;0,85*B2+0,04*C2;0)

Esta primera fórmula nos devuelve el valor recursivo de 'x' sobre las fórmulas descritar por Michael Barnsley.
Con la función ELEGIR a partir de una comparativa con las probabilidades acumuladas comentadas:
(A3>0,99)+(A3>0,14)+(A3>0,07)+(A3>0)
sabremos qué función tenemos que recuperar de entre las cuatro.

De forma similar para el valor de 'y', y por tanto en C3 insertamos para luego arrastrar al rango completo C3:C6001:
=ELEGIR((A3>0,99)+(A3>0,14)+(A3>0,07)+(A3>0);0,26*B2+0,24*C2+0,44;0,23*B2+0,22*C2+1,6;-0,04*B2+0,85*C2+1,6;0,16*C2)

Ambas variables formuladas operan de igual forma con la función ELEGIR, la suma del primer argumento dará un 1,2,3 o 4 según el resultado de ALEATORIO, y así poder recuperar una de las cuatro fórmulas de Barnsley.

El paso final es simple, construir un gráfico de dispersión normal a partir del rango de datos B1:C6001...
Solo tendrás que ajustar algunos parámetros simples, como por ejemplo bajar el grosor de los marcadores... y si quieres cambiar el color de esos puntos a 'verde helecho' ;-)

Con lo que llegamos al gráfico del inicio.

Interesante aplicación de Excel al mundo de las matemáticas.

Seguir leyendo...

Espiral de Arquimedes y las Coordenadas polares en Excel

Un tema muy interesante es poder representar gráficamente en Excel todo tipo de curvas, circunferencias, etc...
El problema es que en Excel no puede hacer gráficas en coordenadas polares!!!.
por lo que tendremos que convertir la función a coordenadas cartesianas (x , y) y representar éstas.

Para convertir coordenadas polares: (r,θ) (sistema de coordenadas bidimensional en el que cada punto del plano se determina por una distancia y un ángulo) a coordenadas cartesianas: (x,y) tendremos que emplear el siguiente cálculo.

Definido un punto en coordenadas polares por su ángulo θ sobre el eje x, y su distancia r al centro de coordenadas, se tiene:
x=r cos(θ)
y=r sen(θ)

Es fundamental esta conversión de polar a cartesiana para mostrar en Excel la Espiral de Arquímedes!!!.

Esta espiral de Arquímedes se define como el lugar geométrico de un punto moviéndose a velocidad constante sobre una recta que gira sobre un punto de origen fijo a velocidad angular constante.
De manera equivalente, en coordenadas polares (r,θ), la espiral de Arquímedes que responde a la ecuación:
r(θ)=a+bθ

siendo a y b números reales.
Cuando el parámetro 'a' cambia, la espiral gira, mientras que 'b' controla la distancia en giros sucesivos.

Nuestro siguiente ejemplo representa gráficamente esta espiral de Arquímedes.
Tomaremos como parámetro 'a' el radio de partida, y nuestra 'b' será nuestro coeficiente de giro.

Crearemos una tabla con cuatro campos...
El primer campo 'ángulo θ' representará diferentes medidas de ángulos, con valores de 0 a 60 con intervalos de 0,2.

El segundo campo 'radio - r' representa la medida polar del radio, esto es, la función polar:
r(θ)=a+bθ
así pues lo fórmulamos como:
=r_inicial+coeficiente*[@[ángulo θ]]

Sabiendo que hemos creado dos nombres definidos:
coeficiente =Arquimedes!$C$2
r_inicial =Arquimedes!$C$1

El tercer campo 'x' es la conversión a cartesiano de la coordenada anterior calculada, sobre (r(θ),'ángulo θ'), es decir, el campo 'x' es igual a:
=[@[radio - r]]*COS([@[ángulo θ]])

Mientras que con el cuarto y último campo 'y' obtenemos el valor cartesiano de la y desde los valores de (r(θ),'ángulo θ'):
=[@[radio - r]]*SENO([@[ángulo θ]])

Vemos en la imagen siguiente los datos...

Una vez calculados todas las coordenadas cartesianas, seleccionamos los campos para 'x' y para 'y' e insertaremos un gráfico de dispersión con líneas suavizadas y obtendremos:

Otro ejemplo de espiral es la Espiral logarítmica con ecuación polar:
r(θ)=a b^θ
aplicándolo sobre la misma tabla, pero cambiando el segundo campo 'radio - r' por la fórmula:
=coeficiente*r_inicial^[@[ángulo θ]]
y resultado:

Seguir leyendo...

VBA: Mostrar segunda columna en Combobox

Hoy toca responder a la cuestión de un usuario que preguntaba por la manera de que su ComboBox reflejara el elemento correspondiente a la segunda columna de las dos que mostraba (Nombre + Apellido)... pero además el dato que quería almacenar era el de la primera !!

Para conseguir esta , aparentemente fácil tarea:
1-mostrar el valor de la segunda columna en el ComboBox (el apellido)
2-registrar el valor de la primera columna (Nombre)

debemos configurar tres propiedades del ComboBox:
.BoundColumn = 1
.ColumnCount = 2
.TextColumn = 2

En particular es relevante la tercera .TextColumn que de acuerdo a la biblioteca MSDN:
Los valores para la propiedad .TextColumn van de -1 al número de columnas de la lista (de -1 a 2 en nuestro ejemplo).
Por tanto, el valor de .TextColumn para la primera columna es 1, el valor de la segunda columna es 2 y así sucesivamente.
Al establecer TextColumn a 0 muestra los valores de ListIndex . Al establecer TextColumn a -1 muestra la primera columna que tiene un valor de ColumnWidths mayor que 0.

Cuando el usuario selecciona una fila de un ComboBox, la columna que se hace referencia en TextColumn se almacena en la propiedad Text .
Por ejemplo, estableceremos para nuestro ejercicio un control ComboBox que contiene los nombres en una columna y los apellidos en una segunda columna. Para presentar los Apellidos, especificaríamos la segunda columna como .TextColumn... y para almacenar los Nombres la primera columna como .BoundColumn.

Cuando la propiedad .Text de un ComboBox cambia (cuando el usuario escribe una entrada en el control), el nuevo texto se compara con la columna de datos especificadas por .TextColumn.

Conseguiremos con la programación que sigue mostrar ambos campos: Nombre y Apellido con la propiedad .ColumnCount = 2:

Y finalmente se mostrará solo el Apellido (segunda columna) pero registraremos el Nombre (primera columna)

Este último paso es posible a la combinación de .TextColumn=2 y .BoundColumn=1 con otras partes del código (por ejemplo, el uso de .Text)

Vamos con el código.
Insertaremos los siguientes eventos asociados al UserForm que tiene únicamente un ComboBox como control:

Private Sub UserForm_Initialize()
'MUY IMPORTANTE esta configuración!!!
With Me.ComboBox1
    .BoundColumn = 1
    .ColumnCount = 2
    .TextColumn = 2     'esta propiedad en particular...
End With

'También se puede emplear la propiedad .RowSource para rellenar el Comobobox
'Me.ComboBox1.RowSource = "TblNombres"
'o con un bucle...
For Each cNombre In Range("TblNombres[Nombre]")
  With Me.ComboBox1
    .AddItem cNombre.Value
    .List(.ListCount - 1, 1) = cNombre.Offset(0, 1).Value
  End With
Next cNombre
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Sub ComboBox1_Click()
'completamos  el valor que se verá al clicar...
With ComboBox1
    .Text = .List(.ListIndex, 1)       'se podría omitir...
    
    'trasladamos a la hoja de cálculo el dato de la primera columna (Nombre)
    Range("E1").Value = .List(.ListIndex, 0)        'también vale   .Value
End With

End Sub

Listo... puedes probar y verificar el correcto funcionamiento...

Seguir leyendo...

Números binarios en Excel

En algunas entradas anteriores del blog hemos hecho uso de propiedades binarias para conseguir nuestras metas, por eso analizaremos hoy el 'mundo' binario en Excel.

Es interesante abordar una breve lectura en Wikipedia.

Comenzaremos revisando la función de Excel específica para convertir un número decimal a binario:
=DEC.A.BIN((número, [caracteres])

dos argumentos, el número decimal a convertir y opcionalmente con cuantos caracteres queremos mostrarlos.

Esta es una función bien simple de emplear pero con una gran limitación.. y es que solo permite números decimales entre -512 y 511 !!!

Sin duda muy corto en la mayoría de los casos...

Por este motivo desarrollaremos algunos de los métodos rutinarios para obtener los necesitados números binarios, salvando esa limitación.

Como primera alternativa veremos la clásica división por dos y con el residuo o resto de esas divisiones (que serán 0 y 1) compondremos nuestro binario.
El método descrito se detalla dividiendo el número del sistema decimal entre 2, cuyo resultado entero se vuelve a dividir entre 2, y así sucesivamente hasta que el dividendo sea menor que el divisor, 2, esstoes, cuando el número a dividir sea 1 finaliza la división.

A continuación se ordena desde el último cociente hasta el primer resto, simplemente se colocan en orden inverso a como aparecen en la división. Este será el número binario que buscamos.

Podríamos replicarlos manualmente con fórmulas, pero parece más interesante montar una función personalizada con programación que realice el cálculo.
Así pues insertamos el código VBA en un módulo estándar del editor de VB:

Function NumBIN(NumDecimal As Range)
'OJO!!! valor max 1.999.999.999 y positivos

'Método ... dividiendo por dos
Dim digitos() As Long

'cuantos dígitos necesitaremos para convertirloa binario
n = 0
Do
    n = n + 1
    rdo = NumDecimal ^ (1 / n)
Loop Until rdo < 2

'redefinimos la matriz para cargar cada dígito binario
ReDim digitos(1 To n) As Long

'hacemos las divisiones necesarias y
'cargamos nuestra matriz de dígitos....

div = NumDecimal
For dig = 1 To n
    digitos(dig) = div Mod 2    'residuo o resto de la división entre 2
    
    div = div \ 2   'división entera !!
Next dig

'componemos nuestro numero binario
For i = 1 To n
    txt = digitos(i) & txt
Next i
'y lo devolvemos a la función
NumBIN = txt

End Function

La limitación de esta función ahora la encontramos en el número decimal 1.999.999.999 y en que siempre deben ser números positivos!!.

Una segunda alternativa es empleando funciones en nuestra hoja de cálculo. Si revisamos la imagen del inicio del post, en la celda D6 para tratar el número decimal de la celda B6 escribíamos:

=DEC.A.BIN(RESIDUO(COCIENTE($B$6;256^5);256);8)& DEC.A.BIN(RESIDUO(COCIENTE($B$6;256^4);256);8)& DEC.A.BIN(RESIDUO(COCIENTE($B$6;256^3);256);8)& DEC.A.BIN(RESIDUO(COCIENTE($B$6;256^2);256);8)& DEC.A.BIN(RESIDUO(COCIENTE($B$6;256^1);256);8)& DEC.A.BIN(RESIDUO(COCIENTE($B$6;256^0);256);8)

Como se observa partimos en 6 partes o tramos el número a convertir, componiendo una secuencia de concatenados como múltiplos de 256, retornando 'paquetes' de ocho caracteres secuenciados... para una vez divididos y con el resto del cociente recuperado, pasar éste último a su forma binaria con la función DEC.A.BIN

Es una forma curiosa que encontré en un foro (no puedo recordar dónde) aunque desarrollada de una manera algo mas simple... Tiene como límite el decimal cercano a: 287.000.000.000

Y una tercera alternativa será empleando el método de distribución.

Este método consiste en distribuir los 1 necesarios entre las potencias sucesivas de 2 de modo que su suma resulte ser el número decimal a convertir.

Por ejemplo el número 131, para el que se necesitarán las 8 primeras potencias de 2, ya que la siguiente, 28=256, es superior al número a convertir.

Se comienza poniendo un 1 en 128, por lo que aún faltarán 3, 131-128 = 3, para llegar al 131.

Este valor se conseguirá distribuyendo unos entre las potencias cuya suma dé el resultado buscado y poniendo ceros en el resto. En nuestro caso resultan ser las potencias 1 y 0, esto es,2 y 1, respectivamente.

Para replicar este modelo emplearemos Solver.

Al montar nuestro modelo, llevaremos números naturales de 0 a 9 (en este caso) en las celdas A6:A15 (serán las potencias a aplciar a nuestra base 2...

Lo que generamos en el rango de celdas B6:B15 con la fórmula:

=C6*2^A6

observa que a la potencia

2^A6

la multiplicamos por las celdas C6:C15 que Solver se encargará de completar con 0 y 1 hasta conseguir que la suma de B5:B15 sea igual al decimal buscado...

Es decir, nuestras celda objetivo en Solver es B16 que contiene la función

=SUMA(B6:B15)

Estamos listos para configurar Solver, así en la ventana de configuración:

Las celdas cambiantes serán las del rango C5:C15.. las mismas a las que hemos agregado la condición que sena binarias, esto es, que sean 0 y 1...

Al lanzar Solver comprobaremos como logra calcular la combinación de 0 y 1 para sumar el 131 buscado.

Seguir leyendo...

Listado de combinaciones sin repetición en Excel

Hoy veremos una forma curiosa de obtener un listado con las combinaciones sin repetición de n elementos tomados de k en k.
Una breve introducción del tema.
Las combinaciones sin repetición de  n elementos tomados de k en k son los diferentes grupos de k elementos que se pueden formar a partir de estos n elementos, de modo que dos grupos se diferencian solamente si tienen elementos distintos, esto es, no nos importa el orden en que aparezcan.
Se representan por C_n,k y su fórmula es:
C_n,k = n! /(n-k)! * k!

Esto en Excel se refleja empleando la función COMBINAT de la que ya hemos hablado aquí

Planteemos el ejemplo a desarrollar.
Queremos listar las combinaciones sin repeticiones de los números 1,2,3 y 4 (4 elementos) tomados de 1 en 1, de 2 en 2, de 3 en 3 y de 4 en 4...

haz clic en la imagen

Así pues el posible número de combinaciones serán:
En las celdas I2:I5 insertamos:
=COMBINAT(4;H2)

o también empleando la función de factorial FACT para replicar la fórmula combinatoria.
En las celdas K2:K5
=FACT(4)/(FACT(H2)*FACT(4-H2))

que devuelve
a) de 1 en 1: 4 combinaciones:= 1, 2, 3, 4
b) de 2 en 2: 6 combinaciones:= 12, 13, 14, 23, 24, 34
c) de 3 en 3: 4 combinaciones:= 123, 124, 134, 234
d) de 4 en 4: 1 combinación:= 1234

La suma de esas combinaciones es de 15, valor que coincide con el número 2ⁿ-1, siendo n el número de elementos:
2⁴-1 = 15

Esto es interesante ya que para obtener nuestro listado de combinaciones sin repetición nos vamos a apoyar en las características binarias de esos 15 números...
Ya vimos un uso de esta propiedad aquí

La cuestión es que convirtiendo en números binarios los valores de 1 a 15, aplicando la función de Excel DEC.A.BIN tendremos unas combinaciones de 0 y 1 que responden a nuestro propósito...

Observemos el uso de la función en las celdas B2:B16:
=TEXTO(DEC.A.BIN(A2);"0000")
o sencillamente (más simple)
=DEC.A.BIN(A2;4)

hemos anidado DEC.A.BIN dentro de la función TEXTO para forzar se complete con ceros hasta que aparezcan siempre 4 caracteres o dígitos... o recomendado emplear el segundo argumento de la función DEC.A.BIN para forzar el número de caracteres, con ceros por la izquierda, a usar.

Esta secuencia de 0 y 1 es la que necesitamos para obtener la equivalencia respecto de nuestros 4 elementos a combinar (1,2,3 y 4) de las celdas C1:F1.

Así pues en el rango C2:F16 añadimos la fórmula:
=SI(EXTRAE($B2;COLUMNAS($C$1:C1);1)="1";C$1;"")

Esta última fórmula simplemente descompone o extrae elemento a elemento de esos números binarios (eso 0 y 1) para finalmente con un sencillo condicional recuperar el elemento correspondiente...

Hemos acabado.. tenemos nuestro listado de 15 combinaciones sin repetición.

Seguir leyendo...

Tres Formas de búsqueda condicionada

Hoy toca hablar de algo cotidiano, y repasaremos tres maneras de aplicar condiciones.
Hablaremos de:
1- función BUSCARV
2- función SI
3- función ELEGIR

Veamos un ejemplo aplicado de esto.
Supongamos tenemos las puntuaciones de varias personas (rango A1:B11), y queremos valorar esas puntuaciones de acuerdo a una tabla auxiliar (rango A15:C20):

Aplicaremos sobre nuestra hoja de cálculo de Excel las tres funciones.
Empezaremos por el clásico BUSCARV, pero atención, en este caso emplearemos su argumento [ordenado] como VERDADERO, i.e., como una coincidencia aproximada.

Así pues en las celdas C2:C11 insertaremos la función:
=BUSCARV($B2;$A$16:$C$20;3;1)
que buscará de manera aproximada, por defecto, el número de unidades en la primera columna de nuestra tabla auxiliar de valoraciones, devolviendo el texto de 'Valoración' correspondiente.

Primera forma fácil y elegante.

La siguiente forma emplea la función SI condicional.
En las celdas D2:D11 insertamos:
=SI($B2<10;$C$16;SI($B2<35;$C$17;SI($B2<56;$C$18;SI($B2<83;$C$19;$C$20))))
fijémosnos que anidamos un SIi dentro de otro para controlar los intervalos de la Valoración...

Y por fin llegamos al motivo de este post, emplearemos la función ELEGIR para recuperar la Valoración que toque.
En las celdas E2:E11 añadimos:
=ELEGIR(SUMA(B2>0;B2>10;B2>35;B2>56;B2>83);$C$16;$C$17;$C$18;$C$19;$C$20)
donde lo interesante es cómo obtenemos el 'num_indice' que recupera el valor o celda de valoración que aplique por cada puntuación numérico.
La fórmula:
SUMA(B2>0;B2>10;B2>35;B2>56;B2>83)
devolverá 1,2,3,4 o 5 según la puntuación, como suma de VERDADEROS (= 1) y FALSOS (= 0)

Por supuesto hay otra muchas maneras... sin olvidar la función (de Excel 3656) SI.CONJUNTO.

Seguir leyendo...

Excelforo: IX aniversario y V Premio Microsoft MVP Excel 2018-2019

Nueve años juntos...
y por quinto año consecutivo (desde el 2014) he sido premiado por Microsoft con el título MVP (Most Vauable Professional) en Excel... (ver perfil)

Comenzar por agradeceos a todos vosotros por el apoyo mostrado durante todo este tiempo, sé que sin vosotros nada sería posible.
Agradecer, también, a Microsoft por este nuevo premio... tomo el reconocimiento como acicate para continuar difundiendo todo el conocimiento posible de nuestra hoja de cálculo favorita: Excel.

Por otra parte, son nueve años!! ya prestando la mejor formación presencial y elearning(online).
Sin olvidar todos aquellos clientes a los que las horas de consultoría han ahorrado tiempo y dinero.
Visita mi web:
www.excelforo.com

Como en años pasados, en este nuevo aniversario, mostraré algunos datos estadísticos acumulados hasta la fecha; respecto al blog diré que son ya más de 8.700.000 páginas vistas, y un 4.300.000 usuarios únicos de todo el mundo (España, México, Colombia, Perú, Chile, Argentina, Ecuador, Estados Unidos.. y un largo etcétera)...
Son ya más de 960 entradas publicadas, de casos prácticos propuestos por vosotros, solucionados y explicados; y más de 10.000 comentarios, y desde luego muchísimas horas dedicadas.

No quiero olvidar aventuras ya consolidadas, como el grupo de Facebook de Excel: Microsoft Excel en Español...
https://www.facebook.com/groups/ExcelEspanol/
donde lo especial es que trato de controlar al máximo los comentarios que no aporten nada (como publicidad de cursos, web, grupos, etc...), reduciendo el grupo a contenido de valor añadido.
Contento de alcanzar ya casi 6.000 seguidores!!
ÚNETE.. no lo dudes!

Y una nueva comunidad de Google Microsof Excel visto por un MVP, con el mismo objetivo con el que nació el grupo de FB... alcanzar al máximo de personas interesadas.

Pero sin duda la mayor satisfacción es y ha sido poder contestar personalmente todas las consultas presentadas, bien a través del correo bien a través de los comentarios del blog...

Mi eterno agradecimiento a todos vosotros.

Por todo ello no puedo dejar de seguir ofreciendo estos cursos en modalidad elearning, para permitir el acceso a ellos a cualquier persona desde cualquier parte del mundo...
No lo dudes haz de Excel tu mejor aliado!
Aprende con los mejores y adquiere una buena base: Edición de Cursos de Excel y Macros online con tutor personal de Julio de 2017.
Nunca estudiar fue tan fácil.

Los cursos de Excel y Macros abiertos para este mes de Julio son:

Curso Excel Avanzado

(ver más)

Curso Macros Medio

(ver más)

Curso Macros Iniciación

(ver más)

Curso Excel Nivel Medio

(ver más)

Curso Tablas dinámicas en Excel

(ver más)

Curso Excel Financiero

(ver más)


Esta nueva edición de Cursos de Excel y macros en modalidad elearning (online) comienzan el día 1 de Julio de 2018; y la matrícula estará abierta hasta el día 10.

Excelforo: con la confianza de siempre....estás a tiempo!!

También formación Excel a empresas.
Explota los recursos a tu alcance (ver más).

Informarte sin compromiso en cursos@excelforo.com o directamente en www.excelforo.com.

Seguir leyendo...