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

miércoles, marzo 17, 2010

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.

44 comments:

Marcos,  17 marzo, 2010 23:52  

jajaja...

sus_80 18 marzo, 2010 01:03  

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

Jorge L. Dunkelman 18 marzo, 2010 06:43  

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

Elbert Villarreal 18 marzo, 2010 06:44  

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.

Jorge L. Dunkelman 18 marzo, 2010 06:51  

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.

sus_80 18 marzo, 2010 16:26  

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

Jorge L. Dunkelman 18 marzo, 2010 18:27  

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.

Rubencillo 19 marzo, 2010 21:11  

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.

Jorge L. Dunkelman 20 marzo, 2010 09:29  

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.

Marco Antonio 21 marzo, 2010 07:18  

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.

Marco Antonio 22 marzo, 2010 14:03  

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.

Anónimo,  18 agosto, 2010 20:37  

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

Jorge L. Dunkelman 18 agosto, 2010 21:30  

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

Anónimo,  21 noviembre, 2010 02:07  

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

Jorge L. Dunkelman 21 noviembre, 2010 06:17  

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

William,  01 diciembre, 2010 18:58  

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

Jorge L. Dunkelman 01 diciembre, 2010 20:18  

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

William,  01 diciembre, 2010 21:10  

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

Jorge L. Dunkelman 02 diciembre, 2010 18:24  

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.

William,  03 diciembre, 2010 22:31  

Gracias Sr, Jorge por el aporte muchas gracias

Anónimo,  25 diciembre, 2010 22:37  

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

Jorge L. Dunkelman 26 diciembre, 2010 07:08  

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

Anónimo,  09 diciembre, 2011 02:08  

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

vitorique,  24 abril, 2012 17:44  

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

Jorge L. Dunkelman 24 abril, 2012 20:44  

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

Anónimo,  18 agosto, 2012 20:11  

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

Jorge L. Dunkelman 19 agosto, 2012 00:00  

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

Jose Ramos 23 abril, 2013 17:11  

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.

Anónimo,  29 agosto, 2013 22:07  

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

Gracias

Jorge Dunkelman 29 agosto, 2013 22:25  

Fijate en los comentarios del 24 de abril de 2012

Anónimo,  05 septiembre, 2013 16:40  

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

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

Saludos,

Jorge Dunkelman 05 septiembre, 2013 18:44  

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

Anónimo,  02 octubre, 2014 00:53  

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.

Jorge Dunkelman 02 octubre, 2014 07:50  

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.

andres 05 febrero, 2015 21:12  

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?

Jorge Dunkelman 06 febrero, 2015 07:54  

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.

andres 07 febrero, 2015 00:05  

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

Renard Kapon 27 octubre, 2015 23:15  

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

Jorge Dunkelman 28 octubre, 2015 07:03  

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.

Renard Kapon 28 octubre, 2015 19:24  

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

muchas gracias

Anónimo,  13 enero, 2017 21:57  

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

Jorge Dunkelman 14 enero, 2017 07:44  

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.

Astroario 14 octubre, 2017 22:34  

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)

Jorge Dunkelman 15 octubre, 2017 00:01  

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.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP