miércoles, marzo 17, 2010

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.

50 comentarios:

  1. Interesante; entendí bien la última parte (desde la tabla de la correspondencia letras-números), sin embargo no entiendo la primera parte (la macrofunción esa, me refiero que no entiendo para que la haces); por que tampoco entiendo al final en tu nombre no sé por que le pones 3

    Saludos

    ResponderBorrar
  2. Como pongo en la nota, la función calcula la suma de las cifras de un número. Después de calcular que la suma de los valores correspondientes a las letras del nombre Jorge equivle a 57, la fórmula calcula 5 + 7 = 12 y luego 1 + 2 = 3

    ResponderBorrar
  3. Don Jorge, primero un gran saludo y agradecer su valiosa ayuda en el uso del Excel! Quisiera preguntarle si ud postea los archivos con las formulas, ya que yo utilizo el excel en ingles, y a veces se me complica un poco para poder traducir las formulas. En caso de ser así, por favor indiqueme donde puedo hacer dichas descargas. De antemano agradezco su amable respuesta.

    ResponderBorrar
  4. Elbert,
    para traducir las funciones de las fórmulas que muestro en las notas puedes usar la técnica que muestro en esta nota o, mejor aun, descargar el complemento que comento en esta otra nota.

    ResponderBorrar
  5. Hola de nuevo Jorge,
    He estado mirando el modelo, pero la verdad es que hay dos cosas que no entiendo:
    1. Para que utilizas la función indirecto(INDIRECTO ("1:" &..), ya que no consigo sacar esa función y luego ese 1 también "me extraña".
    He intentado mirarlo desarbolnado la fórmula, pero nada....

    ResponderBorrar
  6. Sus_80, está todo explicado en la nota pero a,pliaré un poco el tema.
    FILA(INDIRECTO("1:" & LARGO(A2))) crea, en nuestro ejemplo el vector 1,2,3,4,5
    Estos valores son usados para extraer los dígitos usando la función EXTRAE. En definitiva creamos para la palabra Jorge el vector 10,16,19,7,5. Pero estos no son números sino texto ya que son el resultado de usar EXTRAE (fijate en la ayuda en linea). Para convertirlos en números los multiplicamos por 1 y los sumamos con SUMAPRODUCTO.
    Espero que ahora esté todo claro.

    ResponderBorrar
  7. Ahora que estamos empleando el Excel como miscelanea, quisiera consultarte...

    El producto que coloco en el mercado, además de tener un control por unidades en inventario y antigüedad de producto, exige un control por color y por talla de producto, lo que complica el tener un stock ideal.

    A lo largo de 4 años he desarrollado una y otra soluciones que sirven, pero no doy con una filosofia o un paradigma que sea el mejor.

    Actualmente lo mejor que tengo es una imagen ideal de cada producto con estas 4 variables y cuando una de ellas se aleja del tamaño ideal me lanza una alerta, el problema es que ahora estoy repleto de alertas por que la realidad no alcanza a ser como mis modelos ideales, en fin, ojala haya podido hacerme entender, gracias por tu tiempo.

    ResponderBorrar
  8. Rubencillo,
    pareciera ser que tu modelo trabaja con tolerancias muy bajas y ese podría ser el motivo que recibes tantas alertas.
    Otra posibilidad, por supuesto, es que haya un error lógico en las fórmulas.

    ResponderBorrar
  9. Hola Jorge, más allá del tema de fondo me parce interesante el uso de las fórmulas creando un valioso diccionario para sus posibles aplicaciones. Además admiro la paciencia que tienes con algunos. felicitaciones.

    ResponderBorrar
  10. Jorge, permiteme que te sugiera una idea: la de crear una especie de formulario con las funciones que has utilizado en cada unos de tus post, o tal vez, un índice que vincule a la entrada correspondiente, por ejemplo, a este post se podría llegar por SUMAPRODUCTO o FUNCIONES MATRICIALES.
    Para mi tu Blog es de consulta obligada y a veces, resulta dificil encontrar la entrada buscada.

    ResponderBorrar
  11. uff querido!! nos has salvado!! un 100 a este artículo!!!

    ResponderBorrar
  12. ¡Qué interesante! No pensé que este tema tuviera alguna aplicación práctica. ¿Podrías contarnos que uso le has dado? De ser posible, por supuesto.

    ResponderBorrar
  13. hola, quisiera saber si con un numero de cuatro cifras ej.4345 puedo separar 43 por un lado y 45 por otro osea cargar los datos con numeros de cuatro cifras y que en otra parte aparescan por separados

    ResponderBorrar
  14. Podrías usar =VALOR(IZQUIERDA(A1,2)) para extraer 43 y =VALOR(DERECHA(A1,2)) para 45

    ResponderBorrar
  15. Hola sr Jorge, es posible colocar el fichero Para aplicar esta fórmula a letras, es decir los numero a letras, gracias

    ResponderBorrar
  16. William,
    ¿podrías explicar un poco más la consulta?

    ResponderBorrar
  17. Bien señor Jorge, es lo siguiente: en una celda cualquiera existe un numero de 2 cifras por decir en A2 = 10 para separar cada la cifra se hace con extraer =extraer(A2;1;1) = 1 y =extraer(A2;2;1) = 0, ahora, como quedaría cuando en esa celda quede un numero de 1 dígito, por ejemplo A2= 6 , extraer la parte derecha que seria siendo el mismo 6 y la parte izquierda que me quede el dígito 0, esa es mi pregunta , gracias por contestarme señor Jorge

    ResponderBorrar
  18. Tendrías que usar un condicional. POr ejemplo, en la celda de la izquierda pondrías

    =SI(LARGO(A2)=1;"0";IZQUIERDA(A2;1))

    en la celda de la derecha sencillamente

    =DERECHA(A2;1)

    Las comillas en "0" se deben a que DERECHA e IZQUIERDA (y todo funcón de texto, como EXTRAE) dan como resultado texto, no valores numéricos.

    ResponderBorrar
  19. Gracias Sr, Jorge por el aporte muchas gracias

    ResponderBorrar
  20. quiero saber si en una columna con varias cifras se puede saber las terminaciones que hay de cada cifra

    ResponderBorrar
  21. Puedes extraer las últimas cifras de cada número usando al función DERECHA. Por ejemplo
    =DERECHA(A1;3)
    para extraer las últimas tres cifras del número en la celda A1. Para que el resultado sea su vez número hay que combinar DERECHA con la función VALOR
    =VALOR(DERECHA(A1;3))

    ResponderBorrar
  22. muchas gracias por esta formula, me sirvio muchisimo para crearme un generador propio de los digitos verificadores del IMSS, gracias una super ayuda!!!!

    ResponderBorrar
  23. Muy interesante la fórmula de la nota que colocaste, y puede ser utilizada para resolver en un solo paso el asunto de las letras, modificándola de la siguiente manera:

    =1+RESIDUO(SUMAPRODUCTO(CODIGO(EXTRAE(A1,FILA(INDIRECTO("1:"&LARGO(A1))),1)))-1,9)

    En A1 van las letras o el nombre que se desee y en la celda donde se coloca la fórmula aparece el resultado, sin mas complicaciones.

    Saludos y buena suerte con tu blog

    ResponderBorrar
  24. Si, también Rick Rothstein propuso esa fórmula que cité en esta nota.

    ResponderBorrar
  25. How can I do that, I mean, my Excell is in english

    ResponderBorrar
  26. Just hit the "Traducir esta pagina" button in the top left corner and choose English or use this site to translate the functions.

    ResponderBorrar
  27. Para la suma de dos números como por ejemplo 48, Se puede utilizar la siguiente formula,valida para números de 2 dígitos:

    =SUMA(IZQUIERDA(SUMA(DERECHA(Y8;1);(IZQUIERDA(Y8;1))));(DERECHA(SUMA(DERECHA(Y8;1);(IZQUIERDA(Y8;1))))))

    Suma 4+8 y el resultado 12, lo vuelve a sumar 1+2 = 3. Dando 3 como el valor que buscamos.

    ResponderBorrar
  28. Y si no quiero separar mi palabra en varias celdas se pueden sumar los digitos con una sola formula??

    Gracias

    ResponderBorrar
  29. Fijate en los comentarios del 24 de abril de 2012

    ResponderBorrar
  30. La formula tiene algún pequeño error. Os la paso modificada:

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

    Saludos,

    ResponderBorrar
  31. Estimado, antes que nada, gracias por las buenas intenciones.
    La fórmula no tiene ningún error. El problema que se te presentaba tiene que ver con las definiciones regionales del Windows. En España (y otros países) se usa el ";" como separador de argumentos en las funciones. En otros países se usa la coma ",". Lo mismo sucede con los separadores de miles (en algunos lugares puntos, en otros comas) y decimales (lo mismo, al revés).

    ResponderBorrar
  32. Estimado Jorge, la formula =1+RESIDUO(SUMAPRODUCTO(CODIGO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)))-1;9) para numerologìa no sirve.
    En numerologìa la letra "a" tiene valor 1 hasta la letra "i" con valor 9 y los valores vuelven a 1 con la letra "j".
    Con la fórmula propuesta la letra "A" resulta en un 2 y en minúscula "a" tiene valor 7 lo que hace imposible calcular el número que se necesita.

    ResponderBorrar
  33. La fórmula que mencionas no aparece en esta nota sino en la siguiente y calcula la suma interna de los dígitos de un número. Así que no termino de entender el comentario.
    La asociación del número a la letra lo hace la función BUSCARV, y no tiene nada que ver con la fórmula anterior. Así que si en la tabla pongo que a la letra "A" le corresponde el 1, eso es lo que dará como resultado.

    ResponderBorrar
  34. Hola, tengo una duda, mi idea es que necesito sumar los digitos de una fecha por ejemplo esta: 02/12/2012 seria así 0+2+1+2+2+0+1+2=10= 1+0= 1
    ¿Cómo se haría para trabajar con fechas?

    ResponderBorrar
  35. Excel maneja las fechas como números de una serie de números enteros, donde 1 es al 01/01/1900. A la fecha 02/12/2012 le corresponde el 41245, por eso primero hay que crear una celda que contenga el número 2122012. Eso l hacemos usando las funciones DIA, MES y AÑO de esta manera

    =VALOR(DIA(A1)&MES(A1)&AÑO(A1))

    También podemos utilizar esta fórmula como argumento.

    Te recomiendo también ver esta nota.

    ResponderBorrar
  36. Gracias Jorge Dunkelman por la solución, me resolvió mi problema. Saludos!

    ResponderBorrar
  37. Hola
    Estoy trabajando una reducción numérica por "sustracción" de las tablas o series de multiplicar del 1 al 12 hasta el múltiplo 5000. Usé la opción de "texto en columnas" para acomodar los decimales en celdas por fila, y aplicar la fórmula =SUMA(A1:A5000-B1:B5000-(C1:C5000-D1:D5000)) para realizar la reducción sustractiva. El problema al que me enfrento es que no he encontrado la forma de acomodar de forma automática los resultados de la multiplicación en orden de mayor a menor, sin tener que hacerlo de forma manual uno a uno.

    De antemano agradezco cualquier guía. Saludos

    ResponderBorrar
  38. Renard,

    sin entender el tema ni conocer el modelo con el que estás trabjando, te puedo decir que la única forma automática de ordenar las celdas de una columna o una fila es usando macros.

    ResponderBorrar
  39. Entiendo, enviaré un correo con el modelo y una explicación mas concisa

    muchas gracias

    ResponderBorrar
  40. Hola Sr. Jorge: Podría ayudarme por favor? Necesito descomponer una serie de números de varios dígitos y que obtenga un dígito para cada celda. Por ejemplo si tengo el número 123456789 en una misma celda yo pueda colocar en la primera celda el 1, en la segunda celda el 2 y así sucesivamente hasta el último. Necesito crear un generador para el dígito verificador del IMSS en México, veo que aquí alguien ya creó uno pero no he podido encontrar algo que me sirva. Gracias por su ayuda. Saludos Jorge Pérez

    ResponderBorrar
  41. Usando funciones puedes hacerlo con EXTRAE. Suponiendo que el número 123456789 está en la celda A1, en la celda B1 ponés la fórmula

    =VALOR(EXTRAE($A$1,COLUMNA()-1,1))

    y la copiás a las celdas C1, D1, etc. hasta la celda J1.

    Usamos la función VALOR porque EXTRAE convierte los número en texto; valor los reconvierte a valores num´´éricos.

    ResponderBorrar
  42. Excelente Jorge, gracias por facilitarme esta tan importante información.
    Solo quisiera corregir un pequeño detalle, que a lo mejor no pudieron solucionar con tu Función, quienes buscaban esa solución.
    Y es que hay que cambiar las comas(,) por punto y coma(;) y Voalá.
    Entonces quedaría así:

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

    ResponderBorrar
  43. Gracias por la acotación. Ya he mencionado en varias oportunidades el problema de las definiciones regionales de Windows. Así como hay países en los cuales se usa el punto y la coma de diferente manera, algo similar ocurre con las fechas. En los Estados Unidos se usa la notación mes/día/año mientras que en la mayoría de los países hispanoparlantes se usa día/mes/año.

    ResponderBorrar
  44. Te agradezco mucho el aporte Jorge Dunkelman, me ha servido enormemente. Felicidades por compartir tu conocimiento.

    ResponderBorrar
  45. Hola muchas gracias. Mi pregunta es la siguiente la verdad no necesito convertir las letras la formula funciona perfectamente PERO, cuando ingreso un número decimal me genera error (12,345) el resultado debería ser 15. ¿Que debo modificar? Muchas gracias de antemano y a ver si hay respuesta ya que veo que este post es viejo :)

    ResponderBorrar
  46. Tendrías que escribir el número como número entero. El resultado será el mismo. El resultado final es 6 (15..> 1+5=6)

    ResponderBorrar
  47. Guau muchas gracias Jorge por la respuesta. Si, de esa manera lo estoy haciendo sin embargo como son bastantes números lo que hago es copiarlos y pegarlos de un archivo externo y le quito las comas uno a uno. :( eso toma mucho tiempo. Hay alguna forma de darle formato a la celda u otra manera para que me quite las comas al pegarlos?. No sabes lo agradecido que estoy por tu formula y tu respuesta Feliz día.

    ResponderBorrar
  48. ¿Los valores en la celda, son números o texto? En caso de ser números, ¿la coma separa los decimales o los miles? En tu caso supongo que la coma separa los decimales. En ese caso todo lo que tenés que hacer es usar "Buscar y reemplazar" (Ctrl - L). En la casilla "Buscar" ponés la coma y en "Reemplazar por" no ponés nada.

    ResponderBorrar
  49. Me sirve de mucho tu solución, bastante práctica por cierto :) muchas gracias, sin embargo cuando el número a descomponer (12,345 (C1)) está en una celda como resultado de una operación (A1 + B1 = en C1) no sirve buscar y reemplazar. Muchas gracias de todas formas y como digo tu formula me ha servido de mucho. Seguiré en mi búsqueda de lo que quiero a partir de tu formula y cuando tenga el resultado lo compartiré. Mil gracias.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.