Opciones de Autorelleno de Excel

En los últimos meses me he encontrado trabajando con varios clientes que ciertos equipos tenían deshabilitadas las opciones de autorrelleno... y como nunca recuerdo en ese momento donde volver a habilitarlo, el post de hoy está dedicado a mi mala memoria.

Veamos primero cuáles son las opciones de autorelleno.
Supongamos una celda cualquiera con una valor, fórmula, función... tras seleccionarla y arrastrar comprobaremos aparece un botón pequeño en la parte inferior. Si desplegamos veremos las siguientes opciones:
-Copiar celdas
-Serie de relleno
-Rellenar formatos solo
-Rellenar sin formato
-Relleno rápido

Los usos más habituales son los dos primeros.
Si presionamos la opción Copiar celdas se duplicará el contenido de la celda arrastrada.
Si presionamos la opción Serie de relleno Excel intentará seguir la secuencia más lógica.
El resto de opciones afectan, básicamente, al formato.

Y ¿cómo habilitamos o deshabilitamos esta opción?.
Accedemos a la ficha Archivo > Opciones de Excel, en la ventana que se abrirá accedemos al menú de la izquierda : Avanzadas > sección Cortar, copiar y pegar

Tras desmarcar la opción, comprobamos que el botón de autorelleno desaparece al arrastrar.

Otra opción de Excel relacionada con la opción de arrastrar es la que habilita/deshabilita precisamente esa acción del arrastrado.
Por tanto, si queremos evitar arrastrar celdas lo que debemos hacer es entrar en las Opciones de Excel > menú Avanzadas > Sección Opciones de edición

haz clic en la imagen

Seguir leyendo...

Suma Individual de los Dígitos de un Número

Un lector consultaba por la manera de reducir un número a su suma mínima de los valores de sus dígitos en nuestra hoja de Excel:

[...]necesito una macro que me permita calcular el valor en su mínima expresión de un nombre al introducirlo. ejemplo: María =41991, 4+1+9+9+1=24; 2+4=6 entonces el valor numérico de María=6 [...]

La pregunta parece sencilla a priori, y quizá tenga un componente 'esotérico' detrás... ya que esta es una de las maneras en que se obtiene el número de la suerte personal de cada uno
;-)

Sin embargo, hay un componente matemático relevante detrás de todo esto, y es que la Prueba del nueve para la suma (o resta) explica en parte algunas de las fórmulas que vamos a desarrollar a continuación.
Puedes leer algo más en la Wikipedia

Partiremos de un número cualquiera en nuestra celda A2 (por ejemplo 1309)

Una primera solución para conseguir 'nuestro número de la suerte' (1309 - 1+3+0+9 = 13 - 1+3 = 4) sería mediante la siguiente fórmula:
=1+RESIDUO(SUMAPRODUCTO(--EXTRAE(A2;FILA(INDIRECTO("1:" & LARGO(A2)));1))-1;9)

Reduciendo mucho el método o prueba, el asunto consiste en:
1) Hallar los restos de la división por 9 de los componentes de la operación aritmética.
2) Realizar con estos restos la misma operación aritmética y calcular para el resultado obtenido su resto cuando se divida por 9.
3) Obtener también el resto de dividir por 9 el resultado de la operación aritmética.
4) Comparar los números obtenidos en los pasos 2 y 3.

Esta fórmula trabaja, en realidad, matricialmente (una virtud de la función SUMAPRODUCTO), donde:
(--EXTRAE(A2;FILA(INDIRECTO("1:" & LARGO(A2)));1))
nos devuelve el listado de los componentes del valor a estudiar, en nuestro ejemplo:
{1;3;0;9}

Al aplicarle posteriormente SUMAPRODUCTO, conseguimos sus suma de elementos.
Finalmente, aplicamos la regla del nueve y al obtener su resto (función RESIDUO) con los ajustes oportunos (+1 y -1) llegamos al dato deseado.

Una alternativa es el empleo de programación en VBA para Excel, creando una función personalizada (UDF).
ASi pues insertamos en un módulo estándar el siguiente procedimiento Function:

Function SumaDigitos(numero As Range) As Long
Dim txt As String
Application.Volatile

'definimos variable
txt = numero.Value
'marcamos un bucle mientras
'que el número de dígitos del dato acumulado sea mayor que 1
While Len(txt) > 1
    'comenzamos contador acumulado
    SumaDigitos = 0
    'recorremos cada caracter/número del valor
    For i = 1 To Len(txt)
        'acumulamos dígito tras dígito
        SumaDigitos = SumaDigitos + Val(Mid(txt, i, 1)) ' * 1
    Next
    'obtenemos un nuevo valor parcial
    'sobre el que operaremos nuevamente...
    txt = SumaDigitos
Wend
End Function

En este caso el procedimiento es sencillo, recorremos los elementos del número, dígito a dígito, sumando sus partes.. y aplicamos recurrentemente mientras el valor obtenido tenga más de un dígito.

Lógicamente en ambos casos obtenemos el mismo resultado; para nuestro ejemplo el 4

Seguir leyendo...

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:

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

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:

haz clic en la imagen

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 (=):

haz clic en la imagen

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...

Seguir leyendo...

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.
a² + b² = h² Teorema de Pitágoras

Para todo "a" primo se cumple:
a cateto menor
b = (a²-1)/2 cateto mayor
h = (a²+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:

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.

Seguir leyendo...

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:

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..

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.

Seguir leyendo...

Entendiendo algo más de las Fechas en Excel

Explicaré hoy algo muy recurrente en mis formaciones... ¿por qué en ocasiones las fechas se comportan de forma errática?.
Todos sabemos que las fechas, en realidad, responden a un ordinal; teniendo como punto de partida el 01/01/1900, el 2 al 02/01/1900, el 3 al 03/01/1900, etc... así, el día 27/12/2016 correspondería al número 42731.
La mayoría de las veces, por tanto, al trabajar sobre fechas, Excel las trata como tales valores numéricos.

Si esto es así, por qué en ocasiones al construir nuestros criterios, Excel nos devuelve valores (a priori) incongruentes?.

Veamos un ejemplo.

Si comprobamos nuestra sencilla fórmula:
=SI(A2>=27/12/2016;1;0)
vemos como estamos comparando los valores del rango A2:A5
0
1
10
100
con la 'fecha' 27/12/2016, que en condiciones normales se trataría como 42731; esto es, comparamos:
0>=42731
1>=42731
10>=42731
100>=42731
siendo (o debería ser) la respuesta en todo casos FALSO, y por tanto, nuestro condicional debería devolvernos un 0...
Sin embargo comprobamos, asustados en un primer momento, como la respuesta es:
0>=42731 ---- 0 (FALSO)
1>=42731 ---- 1 (VERDADERO!!)
10>=42731 --- 1 (VERDADERO!!)
100>=42731 -- 1 (VERDADERO!!)
¿qué ocurre?

La respuesta es que en este supuesto Excel está tratando nuestra fecha como un producto de fracciones!!.
En nuestro ejemplo, con la 'fecha' 27/12/2016, realiza la operación 27/12 (=2,25) y luego la divide por 2016 (=0,00111607142857143).
Tal y como puede verse si editamos nuestra fórmula, seleccionamos la fecha y presionamos la tecla de función F9:

Mucho ojo con las fechas!!...

La solución suele ser referir las condiciones a otras celdas, o bien emplear la función FECHA(año;mes;día)

Seguir leyendo...

Buscar en Columnas Alternas en Excel

Un lector consultaba por la manera de Buscar en Columnas Alternas en Excel:

[...]Mi problema es el siguiente: Tengo una tabla Excel con varias columnas, lo que pasa que una columna se llama de una manera y otra de otra. Ej: Columna A: porcentaje  Columna B: litros Columna C: porcentaje Columna D: litros  Y así sucesivamente hasta la columna R Es decir, que por decirlo de alguna manera las impares se llaman porcentaje, y las pares se llaman litros. Cada una de las columnas, consta de 54 filas. Lo que buscaba es algo que, al poner en A56 un número que aparece en las columnas impares, en otra celda me de cómo resultado lo que hay en las columnas pares donde coincida con lo que pone en la columna anterior.  A1: 10 B1: 110 litros C1: 20 D1: 220 litros E1: 100 F1: 1000 litros G1: 200 H1: 2000 litros Y así sucesivamente[...]

Esta sería nuestra situación:

haz clic en la imagen

En la celda G5 disponemos el valor o porcentaje buscado, insertando en H5 la fórmula matricial necesaria para recuperar los litros correspondientes:
=DESREF(INDICE(A2:H2;1;COINCIDIR(G5;A2:H2*ES.IMPAR(COLUMNA(A2:H2));0));0;1)
(no olvides validarla presionando Ctrl+Mayusc+Enter en lugar de Enter).

Si descomponemos nuestra fórmula matricial, todo comienza dentro de la función COINCIDIR, cuando creamos el rango 'virtual':
ES.IMPAR(COLUMNA(A2:H2))
que nos devolvería un conjunto de valores (V y F) como el siguiente:
{VERDADERO\FALSO\VERDADERO\FALSO\VERDADERO\FALSO\VERDADERO\FALSO}
es decir, identifica con VERDADERO aquellas columnas 'impares', sobre las cuales centramos la búsqueda del porcentaje deseado.
Al realizar el producto de este rango por los valores de A2:H2 conseguimos el rango:
{10\0\20\0\100\0\200\0}
Sobre esos datos, con COINCIDIR, buscamos el valor buscado de la celda G5... el cual nos devolverá una posición que emplearemos como número de columna en la función INDICE.

Nos aprovecharemos de la virtud de la función INDICE con la que podemos utilizarla como referencia (y no solo para recuperar el valor de la celda correspondiente); para nuestro ejemplo, la fórmula:
INDICE(A2:H2;1;COINCIDIR(G5;A2:H2*ES.IMPAR(COLUMNA(A2:H2));0))
devolvería el equivalente a la referencia E2.

Esta referencia la emplearemos dentro de la función DESREF como primer argumento, esto es, como ancla... a partir de la cual recuperamos la celda que se encuentra 0 filas a derecha o izquierda (o sea, la misma fila), y una columna a la derecha:
=DESREF(E2;0;1)
En definitiva, una celda a la derecha de la que localizamos con la búsqueda...

Con esta fórmula conseguimos lo que deseábamos, centrar la búsqueda exclusivamente en las columnas impares (columnas alternas), sin peligro que se detuviera la búsqueda o coincidencia en las columnas pares o de 'litros' a recuperar.

Seguir leyendo...

Gráfico Excel Real versus Presupuesto

Veremos como en pocos y sencillos pasos podemos mostrar una comparativa de nuestros datos Reales frente a los Presupuestados, i.e., un gráfico Real vs Presupuesto.
El objetivo será algo como lo siguiente:

Lo primero nuestros datos, en el rango A1:D10, dos series de datos (y una más para las diferencias):
1 - B2:B10 con los importes de la Facturación Real
2 - C2:C10 con las cantidades Presupuestadas
3 - D2:D10 con las diferencias entre ambas, que mostraremos en las etiquetas de datos.

Comenzamos seleccionando el rango A1:C10 (dejamos fuera de la selección el rango de las diferencias D1:D10), e insertamos un gráfico de columnas agrupadas.

A continuación seleccionamos la serie de 'Presupuesto' y le cambiamos el tipo de gráfico solo a dicha serie (un gráfico tipo línea con marcadores):

En el paso siguiente modificamos algunas de las opciones-propiedades de la serie de datos 'Presupuesto'.
1 - la serie la queremos Sin línea

Seguimos con la Serie de datos 'Presupuesto' seleccionada.
2 - los marcadores lo queremos integrados, del tipo guión, y los colores del borde y relleno al gusto.
3 - damos un tamaño de 12 al marcador (ajustado lo máximo al ancho de la columna).

Finalizamos agregando a nuestra serie de datos 'Presupuesto' unas Etiquetas de datos con los valores personalizados.
Con las Etiquetas de datos incorporados entramos en su Formato de etiqueta de datos> Opciones de etiqueta > Valor de celdas (botón Seleccionar intervalo), desde donde seleccionamos nuestro rango de diferencias D2:D10.

Listo, nuestro gráfico está acabado... solo nos quedaría añadirle algunas propiedades par personalizarlo.

Seguir leyendo...