jueves, 19 de enero de 2017

Transponer Vínculos en Excel

Un lector consultaba por la posibilidad de Trasponer vínculos en Excel:
[...]Tengo en una hoja unos datos en horizontal, y quiero (en otra hoja del mismo libro ) pegar los vínculos, pero en vertical. 

- Si utilizo "pegado espacio, vinculo" no me deja trasponer, y lo único que se me ocurre es utilizar la función transponer, y elegir el rango de celdas.  Sin embargo, necesito consultar a que referencia hace cada celda, es decir F9, y con "trasponer" solo indicado todo el rango.

Alguna idea? (en vez copiar cada uno a mano)
[...]


Como bien indicaba el lector no es posible realizar tal acción directamente, por ello veremos un sencillo truco que nos permitirá transponer los vínculos de nuestras fórmulas o funciones; pero respetando las referencias...

Veamos nuestro modelo de partida:

Transponer Vínculos en Excel



Se trata de llegar, por tanto, respetando las referencias a:

Transponer Vínculos en Excel



Primer paso:
Seleccionamos el rango de trabajo (C1:C5 para nuestro ejemplo) con los vínculos originales

Segundo paso:
Accedemos a la herramienta de Reemplazar e indicamos que deseamos reemplazar el igual (=) por algún signo (o signos), por ejemplo la exclamación (!).
Nos aseguramos que las opciones de reemplazo son las que queremos:


Tercer paso:
Una vez completado el reemplazamiento...
Con el rango C1:C5 aún seleccionado, copiamos y pegamos transpuesto donde deseemos, por ejemplo en F1:J1

Cuarto paso:
Revertimos el reemplazo.
Sobre el rango transpuesto pegado en F1:J1 repetimos el proceso de reemplazamiento.. pero a la inversa, reemplazamos el signo de exclamación (!) por el signo igual (=):


Listo. El resultado es el esperado.
Tenemos nuestro rango transpuesto (de vertical a horizontal) respetando los vínculos.

OJO!! con el signo por el que reemplacemos, dependerá su utilidad de cada caso concreto..
Por ejemplo, si trabajamos con referencias entre hojas, la exclamación no es una buena opción...

martes, 17 de enero de 2017

VBA: Ternas Pitagóricas en Excel

Todos más o menos conocemos el Teorema de Pitágoras, si no es así, puedes leer algo m´sa en la Wikipedia o ver este post del blog
En esta entrada en concreto hablaremos de la generación de Ternas Pitagóricas que, según indica nuestra referencia Wikipedia es:
Una terna pitagórica consiste en una tupla de tres enteros positivos a, b, c que cumplen que a² + b² = c². El nombre deriva del teorema de Pitágoras, el cual plantea que en cualquier triángulo rectángulo, se cumple que x² + y² = z² (siendo x e y las longitudes enteras de sus catetos y z la de la hipotenusa).
En sentido contrario también se cumple, o sea, cualquier terna pitagórica se puede asociar con las longitudes de dos catetos y una hipotenusa, formando un triángulo rectángulo.


En definitiva se trata de conseguir números enteros que conformen un triángulo rectángulo perfecto y que verifique el famoso teorema.

Uno de los métodos que se explican en ese artículo es el descrito por el autor Dario Lanni: "Teorema de Generación de terna pitagóricas de números primos", el cual se incluyo en un Tratado sobre Sextales, material que se publicó con motivo de su presentación al XXXII Coloquio de Matemáticos del Perú (ver algo más sobre el autor).

Si nos basamos en ese teorema, y siguiendo las descripciones que aparecen en Wikipedia, podremos generar ternas pitagóricas primas irreductibles (es decir primas entre si y con cateto menor primo). En la que el cateto mayor, la hipotenusa, el área, el perímetro e incluso las funciones trigonométricas de un triangulo rectángulo de lados enteros, solamente dependen del CATETO MENOR: El cateto mayor es la semi diferencia del cuadrado del cateto menor menos uno. La hipotenusa es igual al cateto mayor mas uno. El área de dicho triángulo es la suma de tres consecutivos dividido entre cuatro donde el término del medio es el cateto menor; y mas curiosamente resulta el cálculo del perímetro de dicho triangulo que es igual simplemente al producto del cateto menor por su consecutivo superior. Con esto se consiguen infinitas ternas primas de la forma mas simple y sencilla.
a2 + b2 = h2 Teorema de Pitágoras

Para todo "a" primo se cumple:
a cateto menor
b = (a2-1)/2 cateto mayor
h = (a2+1)/2 hipotenusa


Sobre esta base es la que trabajaremos nuestra macro en Excel.
A partir del desarrollo para la obtención de números primos expuesto en este post ampliaremos, con el teorema de Lanni, y listaremos unas ternas pitagóricas primas irreductibles.


En la ventana de código de nuestra módulo estándar incluimos el siguiente procedimiento:

Sub Ternas_Pitagoricas()
Dim x As Integer, y As Integer
Dim contador As Integer, divisores As Integer
Dim Primos() As Long
'un número primo es un número natural mayor que 1 que tiene únicamente dos divisores distintos:
'él mismo y el 1.

'Contador para insertar el valor en las celdas
contador = 1

ReDim Primos(1 To 25) As Long
'recorremos del 2 al 100
'ya que el 1 no se considera primo
For x = 2 To 100
    'reiniciamos el número de divisores de x
    divisores = 0
    For y = 2 To 100
        'si el resto del cociente es cero es que es divisor
        If (x Mod y = 0) Then
            'incrementamos el contador
            divisores = divisores + 1
        End If
    Next y
    'si hemos encontrado un solo divisor entonces es primo
    'el resto de número tendrá al menos uno (>=1)
    'en todos los casos además de por el uno!!
    If divisores = 1 Then
        'llevamos el número a la Array...
        Primos(contador) = x
        contador = contador + 1
    End If
Next x

'Método LANNI
' a2 + b2 = h2      Teorema de Pitágoras
' a                 cateto menor
' b = a^{2}-1/2     cateto mayor
' h = a^{2}+1/2     hipotenusa

'Generamos los valores para ver la secuencia de ternas pitagóricas (y su comprobación)
zzz = 1
For elto = 1 To UBound(Primos)
    If Primos(elto) > 2 Then
        b = (-1 + Primos(elto) ^ 2) / 2     'cateto mayor
        h = (1 + Primos(elto) ^ 2) / 2      'hipotenusa
        Cells(zzz, "A").Value = "Terna " & zzz
        Cells(zzz, "B").Value = Primos(elto)    'cateto menor (el número primo)
        Cells(zzz, "C").Value = b               'cateto mayor
        Cells(zzz, "D").Value = h               'la hipotenusa
        'la comprobación
        Cells(zzz, "F").Value = Primos(elto) ^ 2 + b ^ 2
        Cells(zzz, "G").Value = h ^ 2

        zzz = zzz + 1
    End If
Next elto

End Sub



Al lanzar la macro obtenemos lo siguiente:

VBA: Ternas Pitagóricas en Excel



Comprobando como se cumplen los requisitos de las ternas...

Entiéndase que con este artículo solo pretendo ver cómo Excel se puede aplicar a diferentes entornos, y que en ningún caso intento entrar en discusiones teóricas de modelos, teoremas, etc. fuera de mi competencia.

jueves, 12 de enero de 2017

VBA: Números de Fibonacci en Excel

Hace algún tiempo publiqué como conseguir un listado de números primos (ver post).
Hoy veremos otro clásico atemporal: La Sucesión de Fibonacci.
Puedes leer algo más en Wikipedia.

Básicamente es una secuencia creciente de números enteros naturales donde cada valor es la suma de los dos anteriores, siendo los dos primeros elementos de la Sucesión el 0 y el 1, así:
0, 1, 1, 2, 3, 5, 8, 13, 21, etc...


Emplearemos una macro, basándonos en el algoritmo de la Sucesión y nuevamente empleando Arrays para componer nuestro procedimiento.

Insertaremos el siguiente procedimiento en un módulo estándar de nuestro proyecto de VB:

Sub SucesionFibonacci()
Dim f0 As Long, f1 As Long, elementos As Integer
f0 = 0: f1 = 1
elementos = 25
'dimensionamos nuestra Array al número de elementos deseados
ReDim Fib(1 To elementos) As Long

'Rellenamos la matriz con los elementos de la Serie de Fibonacci
Fib(1) = f0     'asignamos dato al primer valor
Fib(2) = f1     'asignamos dato al segundo valor
For i = 3 To UBound(Fib)
    Fib(i) = f0 + f1    'Calcula el dato como suma de un elemento más el anterior
    'hacemos la recurrencia para los siguientes elementos
    f0 = f1
    f1 = Fib(i)
Next i

'Generamos un MsgBox para ver la secuencia de Fibonacci
txt = ""
For elto = 1 To UBound(Fib)
    txt = txt & elto & vbTab & Fib(elto) & vbTab & vbCrLf
Next elto
'Mostramos el sucesión generada...
mensaje = MsgBox(txt, , "Sucesión Fibonacci")

End Sub



Si lanzamos la macro veremos:

VBA: Números de Fibonacci en Excel



Obviamente podríamos haber obtenido nuestra Sucesión de Fibonacci con una sencilla fórmula en nuestra hoja de cálculo.
Disponemos en A1 el valor 0
en A2 el valor 1
y en A3 la fórmula =A1+A2
para luego arrastrar hacía abajo..

VBA: Números de Fibonacci en Excel



En ambas formas habrá que tener precaución con el límite de precisión de Excel (15 dígitos), ya que a partir del elemento 75, la precisión de Excel nos devolverá valores incompletos o no válidos.