Encontrar que valores suman un importe en excel (Macro muy buena)
Te anticipo que esta publicación es muy buena y útil. Tengo la certeza de que en algún momento te has preguntado ¿cómo hacer para encontrar que valores (sumandos) conforman un resultado o dicho en términos mas simples que valores de una lista se sumaron para llegar a un resultado el cual obviamente estamos interesados en saber su composición. Sin ir tan lejos en Excel no existe una herramienta por defecto que haga esta tarea sin embargo una vez más el poder de las macros nos permiten ir más allá de lo que Excel nos entrega. Este archivo es de basta utilidad para aquellos que deben conciliar cuentas (podrían ser contables), por ejemplo si tenemos una lista con los pagos de diferentes facturas, dichos pagos se ha hecho en varias partes (cuotas) y tenemos un sinnúmero de ellos correspondientes a facturas diferentes; una forma de identificarlos podría ser teniendo en cuenta el valor total de dicha facturación, con esta macro podrías encontrar que pagos conforman el pago total de una factura, en contabilidad le podemos dar mucha aplicación ya que siempre conciliamos cuentas contables.
Si no logras captar la idea de esta macros te dejo unas imagen para que veas su utilidad.
La columna «Valores» contiene una lista de importes los cuales al sumarse (no sabemos cuales ni cuantos) han generado uno o varios resultados (depende de lo que busquemos).
La columna «Lote de objetivos a componer» contiene los importes que deseamos averiguar como están compuestos tomando como referencia los datos de la columna «valores».
Por ejemplo para encontrar como esta compuesto el importe de 5.907.00 la macro determino lo siguiente:
Dichos datos que conforman su composición son aquellos datos tomados y retirados de la columna «valores».
Luego de ejecutar la macro para averiguar la composición de todos los datos de la columna «Lote de objetivos a componer» tenemos los siguientes resultados y en segundos.
La descarga de este archivo la puedes hacer desde aquí:
DESCARGA EL ARCHIVO
Descargar Encontrar que valores componen una suma – Validar saldos contables.xls
Fuente: AyudaExcel
SUSCRÍBETE GRATIS
Te convertiré en un experto en Excel, tengo la experiencia y todo el material que necesitas, mi ayuda es totalmente gratuita.
no puedo descaragr el archivo, podria enviarlo a mi correo?
Hola, le envíe el archivo a su correo, saludos.
Solo soporta una cantidad de datos igual que solver, quisiera buscar una cantidad sumando dentro de un rango de datos de 2 mil
Hola, esto es para casos pequeños, no tengo ahora con casos más complejos. Saludos.
Hola!! Gracias la descargue y es buenísima!!, me gustaría saber si hay forma de que trabaje también con números negativos y que el objetivo pudiera ser cero y/o si hay forma de que sea un rango +-10?
gracias!!
Hola, no lo tengo establecido para esos casos, este archivo solamente es un ejemplo, una base para que pueda hacer modificaciones. Saludos.
buenas tardes intente descargar el archivo y esta dañadoag graciasradeceria si me lo pueden enviar por mail
Hola, lo acabo de descargar y esta normal, le envié el archivo a su correo. Saludos.
Que puedo hacer si me sale un mensaje de «Se ha producido un error en tiempo de ejecución», porque pasa esto, por favor tu ayuda.
Mil gracias de antemano.
Hola, estas usando números muy grandes, utiliza números menores. Saludos.
deseo suscribirme a su pagina, gracias
Hola, te ubicas es la sección BLOG y al lado derecho hay un recuadro donde te indica suscribirte. Saludos.
Gracias por publicar y por tu tiempo.
Hola, gracias por su comentario, saludos.
hola
estoy buscando la forma de encontrar los sumandos de un numero,agrupandolos (los sumandos) en cuatro,y cinco sumandos
Hola, no tengo esa casuística, saludos.
Hola como esta? gran aporte necesito hacer algo parecido a esto, me podria ayudar? muchas gracias,
Si desea que le ayude, envíenos un correo por favor bien detallado de lo que necesita para plantearle una solución.
Muchas gracias por la aportación, me ha servido bastante y me parece un excelente detalle que lo compartan con el público.
Es simplemente util esta macro, muchas gracias mi estimado hace tiempo estou buscando algo asi
Tengo el siguiente error, ya que cuando pongo mas de 1000 registros en la columna C, solo me entrega un resultado parcial en la hoja «Resultado», como puedo solucionar esto?????????????????
Felipe ingresa a la macro y cambia los valores de los rangos.
Me lanza un error en algunas operaciones
y en la depuracion me lleva aqui
Fil(k) = 1000 * objParcial Mod 1000
me puedes ayudar
Hola
¿Encontraste la solución?
necesito esta macro para buscar valores
en cuanto me la vendes digamos que este programada para encontrar 5 valores en un columna de 100 renglones
Muchas gracias amigo a mi me ha funcionado perfectamente
En la empresa me dan una lista muy grande de artículos que hay que revisar si físicamente están en la lista que me dan, y para ello me dan el total en libras de cada material, por lo que tu macro me sirve, pero como el total se compone de muchos artículos y además en la lista aparecen más artículos de los que realmente hay en el inventario, a la hora de usar tu macro sí me sale qué cantidades deben sumarse, pero no las cantidades que realmente deben sumarse
Mi pregunta es: existe una forma para saber qué combinaciones de números sumados dan la cantidad que estoy buscando? Porque en mi caso son muchos los números y muchos de ellos combinados pueden darme la cantidad total que estoy buscando
Salvador lo tuyo ya es una macro mas amplica y especializada, por el momento solo tengo esta que ya de por si es una ayuda.
me sale un mensaje de error numero 6 y solo dice desbordamiento, a que se refiere ese erro?
Buenas noches, sería posible editar la macro para que te diera los valores que más se aproximan al objetivo, ya que si no es exacto me da error.
Muchas gracias
Alfonso, para el caso que planteas se necesitaría de muchas combinaciones, por ahora no es posible brindarte una solución de esa forma.
Genial la macro, gracias ha sido de mucha utilidad
Hola Buenas noches!
He tratado de ejecutar la macro y me sale un error
se ha producido el error ‘6’ en tiempo de ejecución : Desbordamiento.
Estas usando numeros muy grandes, utiliza números menores.
PERO POR ESO SE NECESITA POR QUE SON BASTANTES LOS DATOS A ANALIZAR POR UN HUMANO Y SE REQUIERE EL USO DE UN ORDENADOR
la solucion es facil, las cantidades dividelas entre 100 o mil , con esto si funciona
No tiene sentido si no funciona con números grandes. Si se quiere trabajar con cantidades de millones no sirve para nada.
Podrías indicar dónde se debe modificar el código paraa que no haya un desbordamiento? VB te señala donde se produce pero realmente no es de mucha ayuda la información que nos da.
No es posible utilizar Mod con números grandes, deberías crear tu propia formula que reemplazara al Mod, es un error de Excel, basicamente tendrías que poner tu valor que quieres utilizar
n – d * Int(n / d)
Alucinante… gracias.
eres un genio . gracias
me sirvio de mucho.
Hola
Me queda claro que la macro al ingresar números por sobre cinco cifras emite un error 6 en tiempo de ejecución desbordamiento, ¿Que orden en la programación se puede hacer para que admita números de mas de cinco cifras?
Tecnicamente deberias cambiar la variable declarada tipo Double por Long
WOWWWWW me colgaran por hereje si me ven usando esta magia budu jajaja andare con precaucion.
Cuidado con la hoguera…
Y como puedo crear esa macro, ya hice una pero como puedo crear la funcion para que me de esa info.
gracias
La función ya esta creada, no comprendo.
espero funcione biem
Saludos.
Buenas noches.
La macro no me realiza la operación, emite un error 6 en tiempo de ejecución desbordamiento. y solo ingrese 13 valores en la columna derecha y 2 valores en la columna izquierda
Ese error es por que sobrepasas la capacidad del archivo. Esta hecho para numeros mas cortos.
hola oye tu macro es mu7y buena, pero tengo un problema que espero resolver.
son 148 celdas que contienen las cantidades que necesito y tu mavro me dice que hay un error en tiempo
Es muy complicado hasta para el propio excel buscar los 148 sumandos.
Estimado
Tu macro funciona a la perfección, pero tengo una duda hasta cuantos datos puedo ingresar en los «valores» y cuanto es el valor máximo que puedo colocar, ya que pobre con valores como 300000 y no funciona, ademas tengo un problema,
Tengo 2000 valores como ejemplo, y solo se que 210 valores deben sumar EJM $1232.
Hay alguna de ingresar la variable de números que tengo ademas de el total de la sumatoria.
Sería modificar casi toda la macro, puedes contactar al autor de esta macro al final del artículo.
Una pregunta, los importes negativos también los tiene en cuenta?
Saludos
No he realizado las pruebas, puedes intentar.
Muchas gracias por el aporte, me ahorro mucho tiempo.
buen dia
he descargado el archivo hace tiempo y dejó de funcionar quiero descargarlo de nuevo y no funciona, existe alguna manera de obtenerlo de nuevo.
Saludos
quizás tienes algún conflicto al habilitar las macros.
buenas tengo una duda que me facilitaria mucha el trabajo en excel existe alguna forma que excel te lleve automaticamente a los valores que componen una formula o a la hoja de donde viene enlazado un valor determinado . ej tengo en la hoja 5 en la casilla b2 el dato 500 que lo traje del hoja 2 casilla A6 es osible que seleccionando la casilla b2 me lleve directamente a la hoja 2 casilla A6 o simpre tengo que hacer con el mouse esa operacion ???? 🙁 lo cual es bastante tedioso … GRACIAS
tendrías que hacer una macro con hipervínculos.
Muuuuchiiisimas gracias!!
Me he colocado una medalla en el trabajo gracias a esto!
De verdad?,excelente!
MUCHAS GRACIAS, ES DE LO MEJOR ESTE MACROS –
Amigos, para cuando sus calculos sean muy mayores, los montos a calcular y el resultado esperado lo deben dividir por 100 , asi los montos a consultar son inferioreses y es posible utilizar la macros.
ejemplo :
RESULTADO REAL
952363
RESULTADO – PEGAR EN Lote de objetivos a componer
9523,63
VALOR REAL VALOR /100(PEGAR EN VALORES
4731 47,31
2848 28,48
671 6,71
2855 28,55
2826 28,26
y Listo,
LUEGO LO * POR 100 Y TIENEN EL VALOR REAL. [email protected]
Tu comentario me sirvio mucho, que ingenioso, gracias.
Excelente! muchas gracias!
Realmente muy bueno el macros pero, en mi caso tengo un gran numero de monto, aproximadamente 500 y al tratar de poder dentro de la planilla me aparece que debo depurar el macros. espero un ayuda lo antes posible
Tu macro me parece de las cosas más útiles que he encontrado para el excel, realmente ayuda a reducir muchisimo el tiempo. Mi único problema, al igual que le paso a los demás, es el error que tira con los números grandes. Si lo podrías resolver, sería de gran ayuda, igualmente intentaré arreglar la macro.
Mil gracias!
en el artículo esta la fuente, puedes hacer las consultas en ese hilo, puede que el autor tenga una actualización.
Que tal Gustavo,
Primero que nada, muchas gracias por elaborar esta herramienta, que sin duda, ha sido de gran ayuda para muchos nosotros.
Una pregunta, como la que varios ya hicieron, como se puede corregir el error «Fil(k) = 1000 * objParcial Mod 1000», mencionas que hay que revisar variables, sin embargo, no tengo experiencia con las macros y no se de que hablas al momento de decir «revisar las variables».
Esta herramienta pudo haberte tomado mucho tiempo, pero sería posible que nos ayudaras a corregir este error?
Gracias de nuevo por tu gran aportación
revisa la fuente para hacer las consultas al autor directo del archivo.
cuando te sale error?.
migos, para cuando sus calculos sean muy mayores, los montos a calcular y el resultado esperado lo deben dividir por 100 , asi los montos a consultar son inferioreses y es posible utilizar la macros.
ejemplo :
RESULTADO REAL
952363
RESULTADO – PEGAR EN Lote de objetivos a componer
9523,63
VALOR REAL VALOR /100(PEGAR EN VALORES
4731 47,31
2848 28,48
671 6,71
2855 28,55
2826 28,26
y Listo,
Buena idea!.
Tu formula esta super henial! me ahorraste mucho tiempo pero, oye tengo un problema! 🙁
Cuando intento poner cantidades en mas de 17 celdas me dice que hay un error!
me podrias ayudar? 🙂
probablemente supera el limite.
Fue de mucha ayuda tu artículo, de casualidad no tienes una página o un lugar donde podamos encontrar más ayuda como esta?
Gracias por tu ayuda desinteresada. No sabes cuánto tiempo de trabajo me has ahorrado
Hola sebastiani,
Genial el haber creado que valores suman un importe en excel (Macro Buenisima) pero al igual que los demas cuando utilizo valores superiores a los 500000 me sale error de Fil(k) = 1000 * objParcial Mod 1000 dentro de la tabla de datos, yo intento que me encuentre los valores que conforman esta cantidad $207,270.51 quisiera tu ayuda por favor
Muchas gracias!
no he tenido tiempo de verlo pero quizás la variable supere el limite, revisa las variables.
Buen día Sebastiani,
Primero que Excelente macro y que Solidaridad por ayudar a los demás
y la Razón por el comentario es el siguiente:
en el error «Fil(k) = 1000 * objParcial Mod 1000» he visto que surge cuando en la columna C se ponen valores mayores a los 21,000.00 ¿como puedo solucionar esto?
Gracias y sigue con ese espíritu de solidaridad.
tendrías que revisar el tipo de variable.
Todavía estoy con la boca abierta, me estaba volviendo loca intentando encontrarlo!
Muy bueno!!! Lo incorporo a mis herramientas de trabajo!
hola sebastian gracias por el Encontrar que valores suman un importe en excel (Macro muy buena) pero cuando utilizo valores superiores a los 500000 me sale un erro de Fil(k) = 1000 * objParcial Mod 1000 dentro de la tabla de dato quisiera tu ayuda gracias ….
He observado varios ejemplos que son de mucha utilidad para tus seguidores, lamentablemente no se nos indica como podemos realizarlos, un ejemplo es el truco de sumar, restar, etc. un valor a un rango de celdas. Si me puedes ayudar con ello te agradecería mucho, Jaime
Hola Sebastiani.
Interesante este articulo de como localizar varios valores que conforman un total a traves de una macro.
Lamentablemente el link para descargar el ejemplo no está activado solo aparece unas letra extraña no aparece ni el codigo de la macro.
Podrias corregirlo
Saludos
Gracias.
Hola.
Interesante y util pagina, me llamo la atencion este articulo de como localizar varios valores que conforman un total a traves de una macro.
Lamentablemente el link para descargar el ejemplo no aparece y en el articulo tampoco se ve el codigo de la macro.
Podrian corregir esto, Saludos y gracias.
esta al final de la publicación
hola sebastian gracias por el Encontrar que valores suman un importe en excel (Macro muy buena) pero cuando utilizo valores superiores a los 500000 me sale un erro de Fil(k) = 1000 * objParcial Mod 1000 dentro de la tabla de dato quisiera tu ayuda gracias ….