JLD de vacaciones

JLD sale de vacaciones! Durante la semana entrante estaré paseando por Barcelona y sus alrededores con mis hijas.

No publicaré entradas y tampoco estaré respondiendo consultas. Prometo tratar de responder en cuanto vuelva de mis vacaciones, aunque seguramente habrá algunas que quedarán sin respuesta.

Suma interna de los dígitos de un número con Excel

Parece ser que la onda mística que barre el mundo en los últimos años ha llegado también a algunos de mis amigos.

A uno de ellos se le ha dado por la numerología, que según Wikipedia viene a ser algo así como

una práctica adivinatoria utilizando los números. Es un conjunto de creencias o tradiciones que pretende establecer una relación mística entre los números, los seres vivos y las fuerzas físicas o espirituales.

Mi amigo quería desarrollar un modelo en Excel que le ayudara a hacer los cálculos necesarios según la doctrina adivinatoria. Básicamente los siguientes:

• Dado que a cada letra del alfabeto le corresponde un número, debemos sumar los valores de las letras del nombre (propio y apellido por separado) y totalizarlo reduciéndolo a un número de una sola cifra
• Dada la fecha de nacimiento, sumar los dígitos que la componen y totalizarlos tal como hicimos con el nombre.

Puesto en términos de Excel si a la letra A le corresponde el número 1 y a la letra N el 14, el nombre Ana equivale a 1 +14 = 15 y a su vez 1 + 5 = 6.

El desafío es hacerlo sin macros, por supuesto. Para realizar la suma interna de los dígitos de un número podemos usar esta fórmula

=SUMAPRODUCTO(EXTRAE(A2,FILA(INDIRECTO("1:" & LARGO(A2))),1)*1)

Vamos a explicar esta fórmula por pasos:

=FILA(INDIRECTO("1:" & LARGO(A2))) crea una vector vertical con una serie de número que empieza en 1 y termina en el número que coincide con el largo del número cuyos dígitos queremos sumar. En nuestro ejemplo el resultado es FILA(1:5) es decir el vector {1,2,3,4,5}

EXTRAE(A2,FILA(INDIRECTO("1:" & LARGO(A2))),1) equivale a EXTRAE(A2, {1,2,3,4,5},1), es decir, extraemos el primer dígito, luego el segundo y así sucesivamente hasta el quinto. Recordemos que SUMAPRODUCTO es una función matricial.

Finalmente SUMAPRODUCTO multiplica cada miembro del vector creado con EXTRAE por 1 y los suma, dando el resultado, 15. Esta multiplicación es necesaria ya que la función EXTRAE da como resultado textos, no valores numéricos.

Para reducir el resultado de dos dígitos a una única cifra tenemos que volver a aplicar la fórmula. Esto puede hacerse en otra celda

o anidando las funciones

Si el resultado del primer cálculo diera un número de tres cifras, la fórmula anidada sería demasiado larga. Por lo tanto dividir el cálculo en varias celdas parece ser la mejor solución.

Actualización: una solución más eficiente puede leerse en esta nota.

Para aplicar esta fórmula a letras, debemos primero convertirlas a sus equivalentes numéricos. Suponiendo que el número equivalente sea el número de orden en el alfabeto, podemos construir esta lista

Incluir el rango en un nombre ("alfabeto") y usar BUSCARV para calcular el valor de cada letra.

Por comodidad y para no complicar las fórmulas vamos a dividir el nombre en varias celdas poniendo cada letra en celda contiguas. Creamos una tabla de dos columnas por 20 filas; en la primer columna ponemos las letras del nombre y en la segunda columna calculamos el valor numérico de la letra con BUSCARV

Para ocultar los resultados #N/A y los bordes donde no hay letras usamos formato condicional

Para sumar los valores de cada letra usamos la fórmula matricial

={SUMA(SI(ESERROR(C2:C21),0,C2:C21))}

Y en las celdas G2 y F2 usamos las fórmulas con la función SUMAPRODUCTO que mostramos más arriba.

Finalmente podemos ocultar las columnas G y H y mostrar el resultado final

Así nos enteramos que Jorge vale 3, sobre cuyo significado no tengo la más pálida idea.

Imprimir páginas pares e impares en Excel

En estos tiempos que corren donde tiene que ser verde, es curioso que Excel no ofrezca la posibilidad de imprimir páginas pares o impares como existe en Word. Esta funcionalidad nos permite ahorrar papel imprimiendo en ambas caras de las hojas.

Podemos superar esta carencia con esta macro que Ron de Bruin publica en su página, quien a su vez cita a Gord Dibben


Sub Print_Odd_Even()
    Dim Totalpages As Long
    Dim StartPage As Long
    Dim Page As Integer
  
    On Error GoTo errHandler

    StartPage = 1  '1 = Odd and 2 = Even

    StartPage = InputBox("Ingrese 1 para impares, 2 para pares")

    Totalpages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
    For Page = StartPage To Totalpages Step 2
        ActiveSheet.PrintOut from:=Page, To:=Page, _
                             Copies:=1, Collate:=True
    Next
  
    Exit Sub
  
errHandler:
Exit Sub

End Sub



El mejor lugar para guardar esta macro es el cuaderno de macros Personal de manera que la macro siempre esté disponible. También podemos crear un icono o un atajo de teclado para lanzar la macro con facilidad.

El corazón de esta macro es la macrofunción XLM GET.DOCUMENT(50), así que agregaremos algunas palabras sobre el tema.

Como ya he publicado en el pasado las funciones macro del lenguaje XLM, que fue usado hasta la versión 5 de Excel, siguen vigentes por motivos de compatibilidad. Estas funciones nos permiten hacer tareas como por ejemplo determinar cuántas páginas a imprimir hay en un cuaderno de Excel.