Uno se da cuenta lo útil que puede ser este procedimiento si ha jugado muchas veces a hacer sumas manuales para encontrar a qué facturas corresponde un cobro conjunto. Gracias por el aporte.
Son muchas las posibles aplicaciones de la "antisuma". Por supuesto, la que se explica en el vídeo es una, pero el método descrito puede resolver otros problemas parecidos. Por ejemplo, si se dispone de varias piezas de algún producto con distinto peso y se desea tomar varias hasta completar un cierto total; o si se desea descontar algunos títulos para alcanzar el importe que se necesite para atender un pago, o si se quiere saber qué tareas de entre varias se pueden terminar en un determinado plazo... Los casos son variadísimos, y espero que este procedimiento sirva para resolverlos.
Te tengo que agradecer que hayas hecho este video. Estamos buscando el total de unos importes en mis facturas y no habia manera. Gracias al video han sido los 7 minutos emplezas en tiempo. Aunque he tenido que utilizar opciones ya que tenias 260 filas para encontrar el resultado y subir el tiempo maximoy las interacciones para resolver el resultado. Aun asi el video es de 10!
Muchas gracias, Carlos. Me alegro de que le haya gustado este vídeo y de que le haya servido para resolver sus cálculos en poco tiempo. El objetivo que tienen todos nuestros tutoriales es resultarles de ayuda, así que su comentario nos anima a seguir ofreciéndoles contenidos tan interesantes como este.
MUCHAS GRACIAS. Recién estoy descubriendo el canal y me preocupa que no veo videos mas recientes. ojalá continúen con el trabajo porque los videos me parecen muy eficientes y eficaces en el tratamiento de los temas que aborda. Muchísimas gracias de nuevo!!!!
Muchas gracias. Me alegro de que le gusten nuestros contenidos. Por favor, ordene los vídeos por antigüedad, porque publicamos vídeos casi todas las semanas. De hecho, el último vídeo lo hemos publicado precisamente hace unos pocos minutos. Aquí tiene el enlace: th-cam.com/video/zOmEOXQ8IPI/w-d-xo.html.
Soy un apasionado por EXCEL aunque tengo un nivel bajo de conocimientos; pero veo tutoriales en cantidad y si de algo estoy seguro, es que sus tutoriales son de los MEJORES que he tenido la oportunidad de disfrutar. Mil y mil gracias por aportarnos tan valiosos conocimientos.
Muchas gracias por su comentario. Le invito a que siga viendo muchos tutoriales, en este canal y en otros. Seguro que encuentra enseñanzas muy útiles. Incluso los tutoriales que menos le convenzan pueden ser una valiosa herramienta de aprendizaje si busca formas alternativas para conseguir los objetivos. Por supuesto, en este canal puede dejarme un comentario con sugerencias o con dudas o pidiendo consejo.
Rafael: Me gustó mucho el uso de la restricción binaria, pues usualmente yo estaba acostumbrado a utilizar SOLVER con restricciones del tipo de programación lineal. Excelente trabajo.
Me alegro de que le haya parecido interesante. Ésta es sólo una de las muchas aplicaciones que pueden tener Solver y la programación lineal. Seguramente mostraré otras en futuros vídeos. Le agradezco el comentario.
Buenos días. Una vez practicado con el modelo me he encontrado unos detalles que me gustaría comentar. 1º En el vídeo no se señala que la formula SUMAPRODUCTO, la de la celda D16, es matricial y debe validarse con CTRL+SHIFT+ENTER. Si no se hace así, generará un error de valor. Descargando en fichero se ve que validó así, ya que lleva los{}. Quizá pueda resultar evidente, ya que los argumentos son rangos, pero si no se tiene práctica con FM, seguro que no lo es tanto. Aunque siempre pienso que se aprende más cuándo surgen errores 😁 2º Seria bueno disponer de una manera para que se limpie el rango de unos y ceros antes de otra prueba. Si no lo limpiamos manualmente, puede aparecer un 1 dónde no corresponde. Aunque Solver señala la nueva solución correcta y la regla condicional colorea las filas correctas. En el minuto 06:34 se puede observar que hay dos filas verdes, las correctas, pero hay otros 3 unos que quedaron del ejemplo anterior. Podría hacerse con una macro pero, siguiendo los usos del canal, seguro que se puede utilizar una función adecuada. De momento se me escapa. No tengo claro si una función, que haga que el rango quede vacío, puede perjudicar el funcionamiento de Solver. 3º Al introducir el formato condicional, que colorea las filas que Solver señala como 1, ojo con cómo hay que poner la referencia de las celdas, para que pueda validar en todas las filas. Excel automatiza con referencia absoluta y no siempre es lo que queremos. No lo es en este caso. En el vídeo se ve, si se observa con detenimiento 😂 Mis disculpas por el comentario, si puede parecer inapropiado. Me ha resultado muy interesante esta práctica y he creído que podría ser útil mi comentario. Edito con Una pequeña macro que limpia al cambiar de cliente Si en la pestaña de la hoja seleccionamos ver código y seleccionamos Worksheet y a la derecha seleccionamos Change podemos hacer una macro que ejecute código para cuando hagamos un cambio en la hoja. Borramos las lineas de código que ha puesto excel que no sean del evento change y en la que se refiere al evento Change ponemos Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$H$2" Then Range("H3") = "" Range("E2:E15") = "" End If End Sub Con esto, Al cambiar el código del cliente se queda en blanco la cantidad cobrada y el rango solver. Para poder guardar la macro hay que guardar el fichero con la extensión de libro habilitado para macros .xlsm Espero que sea útil Saludos
Tiene razón. La fórmula de SUMAPRODUCTO (en D16) es matricial y no lo he advertido expresamente. Las llaves que Excel incorpora a la fórmula delatan ese carácter, pero es verdad que sería mejor haberlo mencionado. También es verdad que no he incorporado un “borrador” o un “limpiador” de resultados para que los únicos unos que se muestren sean los del cliente investigado en cada prueba. Dado que la fórmula del SUMAPRODUCTO ya incluye otro vector (el de coincidencia del código de cliente) para que se multiplique por cero cualquier otro valor de cualquier otro cliente, el complemento Solver encontrará irrelevantes esos unos de pruebas previas, así que ni se molestará en cambiar el valor de la variable para esas facturas. La solución del problema no es tanto la columna de unos como las celdas resaltadas mediante formato condicional, pero es verdad que un “limpiador” haría la presentación visual más coherente, así que debo darle la razón también en este aspecto y agradecerle la macro que ha diseñado para incorporar esa mejora a la hoja de cálculo. Conseguir ese resultado (o algo parecido) con fórmulas es posible. Se me ocurre habilitar una columna adicional que multiplicase la variable binaria de Solver por la condicional que evalúa la coincidencia del cliente, y ocultar o disimular la primera de las mencionadas. Así, Solver podría hacer su trabajo sin limpiar los valores irrelevantes procedentes de una prueba anterior, pero lo que se mostraría en pantalla estaría limpio de aquellos unos correspondientes a otros clientes. Finalmente, también es cierto que cuando se introducen referencias mediante el ratón en las fórmulas de evaluación de un formato condicional, Excel las interpreta por defecto como referencias absolutas, y pone dólares (como si fuesen gratis…). Tampoco lo explicito (aunque no sobraría) pero no deja de ser un detalle apreciable en la propia fórmula. Algo parecido ocurre en las fórmulas ordinarias de las celdas de la hoja: Excel adopta el criterio contrario (interpreta por defecto que las referencias son relativas) y no siempre lo explicamos cuando las convertimos en absolutas. En cualquier caso, este aspecto de su comentario también es acertado. Su comentario, lejos de ser inapropiado, es muy pertinente, así que le felicito por él y le agradezco sinceramente que lo haya compartido con todos y que haya contribuido con sus conocimientos sobre VBA a mejorar este canal.
@@eaae Mis conocimientos De VBA son más bien limitados. Esas lineas de código las aprendí en tutoriales sobre el VBA y las adopte para mis hojas. Es VBA de andar por casa 😂. Me alegra que pueda servir a los suscriptores de canal
Muchiiiiiiiiiiiiiiiiiiiisimaaaaaaaaaaaaaaas gracias, ya me estaba por dar algo haciendo la fórmula y me daba error, ya que no sabía que era matricial, saludos desde RD.
El vídeo es muy bueno. Cuando cambias el cliente conviene que borres previamente la columna s/n de 0s y 1s pues deja grabados 1s de procesos previos que pueden inducir a error.
Efectivamente, conviene borrar antes la columna de ceros y unos. Pero aunque no se hiciese, los cálculos no provocarían ningún error, porque los unos residuales seguirían quedando multiplicando por ceros al no coincidir el cliente. Es verdad que la visualización sería más coherente si desapareciesen, pero la verdadera asociación de importes al total la determina el formato condicional.
Hola buena tarde, recientemente me he encontrado con su canal y estoy muy emocionando incrementando mis conocimientos en Excel. Muchas gracias en verdad por compartir con todos. Tengo un problema al aplicar la solución que plantean Tengo un mensaje "Demasiadas celdas variables". Mi pregunta es: ¿Existe un límite? Justo la formula la quiero usar para lo que el ejemplo lo hace pero mi listado de facturas es de 350 o 400. Ojalá puedan ayudarme. Abrazo a todos
Muchas gracias por su comentario. Efectivamente, existe un límite: 200 celdas cambiantes. Si tiene un listado mayor, será necesario algún algún tipo de "pre-filtrado", que se podría automatizar mediante fórmulas o, en otro caso, con alguna herramienta. Pero este paso previo dependerá de la naturaleza y de la estructura de sus datos, así que para darle alguna orientación más precisa tendría que ver su hoja de cálculo. Aquí tiene algo más de información sobre el límite: support.microsoft.com/es-es/office/definir-y-resolver-un-problema-con-solver-5d1a388f-079d-43ac-a7eb-f63e45925040?ui=es-es&rs=es-es&ad=es.
Muy buena aplicación de la herramienta SOLVER. Me gustaría hacer una petición al canal referente a la función SUMAPRODUCTO, que se me resiste bastante. Y se trataría de que explicase bien el concepto de la fórmula y de las diferentes formas en que se puede usar. Gracias.
A raiz de otro ejercicio de este canal me encaprichado con una idea (que tambien vale para resolver este ejercicio) y he conseguido resolver el ejercicio sin usar solver con una sola formula matrizial que al arrastrar va soltando los valores que componen la suma. Eso si, es una mega-formula de cuatro renglones que mezcla varias tecnicas. (la idea es contener una formula matricial una tabla de la verdad explore todas las posibilidaes, encuentre los valores que componen la suma, y al arrastrar vaya soltando los resultados encontrados, tambien se podria hacer que al arrastrar tanto en filas como columnas suelte todas posibilidades encontradas, si hubiese mas de una solucion) Es probable que se pueda mejorar o depurar la formula ya que soy un aficionado que ha profundizado mucho en algunas cosas y detalles, trucos, etc.. pero en otras no (incluso muy basicas) saludos
@@eaae Ok, Gracias por su interés, le adjunto la formula, yo la tengo en "H19" y arrastro hasta que dejan de salir valores. Formula: =SI.ERROR(INDICE(EXTRAE(INDICE(DEC.A.BIN(FILA(INDIRECTO("1:"&PRODUCTO(SI(--($B$2:$B$15=$H$2);2;""))))-1;CONTAR.SI($B$2:$B$15;$H$2)); COINCIDIR(1;--(MMULT(--EXTRAE(DEC.A.BIN(FILA(INDIRECTO("1:"&PRODUCTO(SI(--($B$2:$B$15=$H$2);2;""))))-1;CONTAR.SI($B$2:$B$15;$H$2));TRANSPONER(FILA(INDIRECTO("1:"&CONTAR.SI($B$2:$B$15;$H$2))));1); INDICE($D$2:$D$15;N(SI(1;K.ESIMO.MENOR(SI(--($B$2:$B$15=$H$2);FILA($D$2:$D$15)-FILA($D$1);"");FILA(INDIRECTO("1:"&CONTAR.SI($B$2:$B$15;$H$2))))))))=$H$3);0));FILA(INDIRECTO("1:"&CONTAR.SI($B$2:$B$15;$H$2)));1)*INDICE($D$2:$D$15;N(SI(1;K.ESIMO.MENOR(SI(--($B$2:$B$15=$H$2);FILA($D$2:$D$15)-FILA($D$1);"");FILA(INDIRECTO("1:"&CONTAR.SI($B$2:$B$15;$H$2)))))));FILAS($G$19:G19));"-")
@@feral7691 Su técnica me parece muy ingeniosa. He comprobado que funciona, así que le felicito por esta aportación tan interesante, que le agradezco haya compartido para provecho de todos los seguidores del canal. La analizaré más adelante para intentar simplificarla (si me es posible) y no descarto dedicarle un futuro tutorial en el que, con su permiso, estaré encantado de citarle.
@@feral7691 No sé cuándo podré dedicarme a ello, porque tengo varios proyectos en mente, pero seguro que es un contenido estupendo para el canal. Le quedo muy agradecido.
Muy buen video, gran canal. Solo un tema, está herramienta aparenetemente solo funciona con valores absolutos, porque tengo que a una suma con distintos importes (positivos y negativos), sin embargo está herramienta solo tomaba solo los valores positivos...
En esta formulación no se toman valores absolutos, así que debería funcionar tanto con los positivos como con los negativos. Si quiere que le eche un vistazo a su hoja de cálculo facilíteme un enlace y trataré de ver por qué no obtiene los resultados esperados.
Me encanto el video. Necesito una fórmula que me tome un rango más amplio, por que con solver me dice que supera e límite de filas. Además necesito identificar los valores de varias diferencias. La base es más amplia
Me alegro de que le haya gustado. Es cierto que Solver tiene un número máximo de celdas cambiantes. Si necesita aplicar esta técnica a un caso más amplio, tendrá que probar con algún prefiltrado, que le aconsejo formular, aunque la forma de hacerlo depende muy estrechamente de la naturaleza y de la estructura de sus datos.
Muy útil y bien explicado el video. Gracias! Aunque me surgió una duda: ¿existe alguna forma de configurar a Solver para que encuentre los valores de una lista que se acerquen (sin llegar a ser exacto) a un valor arbitrario definido por el usuario?
Puede resolverlo incluso sin usar Solver, creando una variable auxiliar que calcule la diferencia entre el número buscado y los de su lista de búsqueda. Para evitar los valores negativos puede usar valores absolutos o cuadrados. Una función MIN sobre ese rango de diferencias le permitirá identificar esa diferencia mínima. Para conocer de qué valor procede bastaría con funciones básicas de búsqueda y referencia (INDICE y COINCIDIR, por ejemplo). Por supuesto, también puede conseguir el mismo resultado con Solver, pero quizás no sea necesario recurrir a esta herramienta. Si necesita alguna ayuda más, no dude en consultarme.
Solver puede trabajar hasta con 200 celdas cambiantes. En este vídeo tiene un tutorial introductorio sobre esta potente herramienta: th-cam.com/video/gi2Llvi5C0Q/w-d-xo.html.
Súper video, una pregunta. Es indispensable (para la suma producto) la condición del "cliente"? yo estoy resolviendo un problema pero solo cuento con la lista de valores que y con el saldo que deseo identificar a partir de la suma de los valores. No cuento con información de "cliente".
Buenas noches caballero, soy nuevo en el canal y lo felicito por el coto enido, es estupendo!. Tengo una consulta... Yo necesito que entre 2 o 3 celdas de un largo inventario me busque la mayor cantidad de combinaciones con un máximo de 30. Es decir si tengo 100 productos con valores desde 5 hasta 20(5,6,7,8,9,10,11,12,13hasta 20) me busque todas las variables posibles de combinación. Cómo en este vídeo pero que me dé varias variables con un máximo de 30 de valor en total. UD podría ayudarme con ello?
Puede hacer su búsqueda sobre una tabla de combinaciones construida con la técnica que se explica en este otro vídeo: th-cam.com/video/6mAB4PNAKwU/w-d-xo.html.
@@eaae excelente hermano! Pero tengo el problema que quiero que me busque un total específico entre 2 productos o 3. De los 100 en total. Ejemplo si tengo Bobina 1 : 20 pesos Bobina 2 : 12 pesos Bobina 3 : 9 pesos Bobina 4 : 17 pesos Bobina 5 : 10 pesos Que me diga que tengo que combinar la bobina 1 con la 5 y la 4 con la 2. El problema lo tengo para poner el total a buscar entre las combinaciones, sino, puede combinar y supera ampliamente el valor lo cual es un dato que no serviría Muchas gracias desde ya por su amabilidad
Buena tardes. Muchas gracias por su clara y completa explicación. Debo aplicar solver sobre 52 variables y veo que no termina nunca de resolverse. He probado con menos para verificar estar haciendolo correctamente y sí funciona. Existe alguna forma de salvar este problema?
El número de iteraciones necesarias crece exponencialmente con el número de variables. Para mejorar la eficiencia del procedimiento habría que conocer un poco mejor la naturaleza de sus variables y qué condiciones debe tener la solución. A veces se puede probar con una una preselección de variables o una acotación de valores, pero son opciones muy casuísticas.
Excelente video. Una pregunta. Existe alguna forma de hacerlo pero trabajar con un rango? Por ejemplo. El monto que yo deseo pero con una varianza de 1 dolar? Ya sea oara arriba o oara abajo?
Puede intentarlo modificando las restricciones en la optimización para que se admitan valores con el margen que desee y cambiando el objetivo a minimización.
Buenas, gracias por el video. Tengo una duda: en el caso de que haya mas de una solución para llegar a esa suma, como puede solver señalarme esa distinta opción que tambien sume mi cifra objetivo. No sé si me explico Gracias!
Pruebe a ejecutar Buscar Objetivo con otro punto de partida. La herramienta utiliza una técnica sucesiva que puede ser sensible a los valores iniciales.
Hola, ya que solver solo permite hasta 200 variables, si requiero que realice eso por varios grupos, con la condicion que los rangos son cedulas, como puedo realizar solver por medio de programación VBA para poder poner dinamico todos los rango?
Estupendo vídeo. ¿Me podría ayudar en un problema? Tengo dos columnas de datos que suman lo mismo. Tengo que conciliarlas pero están agrupadas de forma distinta. Tendría que hacer sumas de la primera columna para encontrar un dato de la segunda columna y viceversa. ¿Es posible con Solver? Muchas gracias
Una posibilidad sería reiterar el cálculo del procedimiento descrito para cada uno de los valores de la segunda columna. Si lo que quiere es automatizar el cálculo de todos los valores a la vez, quizás el problema llegue a tener un número excesivo de variables (dependería de cuántas filas tenga cada columna y cuántas sumas requiera). Una posibilidad sería asignar códigos a cada suma y tratar de resolver las sumas mediante valores binarios en ambas columnas. Le sugiero que pruebe esta opción y que vuelva a consultarme si no consigue ningún avance.
@@eaae ¿Sería posible un enlace o ejemplo de este cálculo? De un listado de facturas necesito saber cuáles suman una base imponible de x y además, una cuota de iva de y. Gracias y enhorabuena por su canal!
@@Msolrs La solución depende muy estrechamente de la naturaleza y de la estructura de los datos. Por favor, comparta un ejemplo de sus datos e intentamos darle alguna orientación más precisa.
Excelente video, solo tengo una duda en el numero de cliente para mi todos son el mismo y al momento de agregar la formula sumaproducto me aparece error, sera por ese motivo?
Muchas gracias, Miguel Ángel. Aunque todos los clientes tuviesen el mismo número de identificación la fórmula debería ofrecer un resultado correcto. ¿Qué error obtiene?
En tu segundo ejemplo, aun habia 1 y 0 de la solucion anterior. Quiza conviene indicar que se debe borrar el resultado anterior para que no haya confusion.
No es necesario borrar el resultado anterior. Precisamente se dispone de una celda amarilla rotulada como diferencia para evidenciar si la solución binaria de la columna E satisface o no la condición del importe total.
Rafael como estas? tengo el mismo caso pero con 3000 celdas de valores. Me dice que es muy extenso y si debo considerarlas todas. Que puedes sugerirme?
Solver tiene un máximo de 200 celdas cambiantes. Si debe abordar un problema de mayores dimensiones tendrá que ingeniar algún sistema de filtrado previo para descartar valores no factibles.
Puede ejecutar SOLVER con otro vértice inicial, aunque no le puedo garantizar que se alcancen todas las demás soluciones distintas. Otra posibilidad es que formule una condicional que le advierta cuando alguno de los valores designados en la solución esté repetido también fuera de ella, lo que le permitirá revisar exógenamente esos casos.
Hola!, muchas gracias por el video, me ha estado ayudando mucho, pero tengo una consulta. Cuando está resolviendo, veo que en la fila "binaria" empiezan a aparecer número con decimales (0,2 o 0,8, etc), por lo que asumo que está buscando con parciales de montos para que de el número que está buscando. Hay una forma de que sólo busque con el número entero? O sea, si tengo un monto de 1.000, sume ese número completo y no vaya parcializándolo?, porque tengo listados más grandes y al hacer eso toma mucho más tiempo buscando el monto que corresponde. De antemano muchas gracias!
Si configura Solver con una restricción para que los valores de la columna en cuestión sean binarios no deben aparecer números con decimales, sino exclusivamente ceros y unos.
Buenas tardes sres excel avanzado para administradores de empresase, les escribo ya que le solicito su ayuda con respecto al tema tratado en este video , ya que basado en el video y en el paso a paso adapte lel arcchivo de excel a la informacion que requiero consultar pero no me da los resultados que se ven en el video ; al utiliar la formula sumar producto , utilizado los rangos de celdas aplicados en el video una vez realizado la formula me dice que son referencias circulares y me da como resultado cero, como pueden ayudar para realizar la consulta que requiero, donde les puedo enviar el archivo con la informacion modificada o contactarme con ustedes para que ayuden a resolver la consulta y me funciones excatamente como sale en el video, quedo atento a su respuesta y me pueda ayudar
Si sigue los pasos del vídeo, obtendrá los resultados deseados. Las fórmulas no deben provocar referencias circulares, pero le advierto de que deben validarse matricialmente. Por favor, revise dónde se produce la autorreferencia y háganoslo saber.
Muy interesante, podria ayudarme con una duda?, como puedo hacer para obtener tres valores aleatorios que me den un resultado dado, le explico. x+y+z/n =35 Donde: x, y, z = valores numéricos aleatorios positivos que no deben pasar de 35 n = promedio de x, y, z 35 = valor que debe darse como resultado.
Fije arbitrariamente los valores de x e y (por ejemplo) y deje libre el de z. En una celda aparte calcule la suma de x, y y el cociente de z entre el promedio. Use BuscarObjetivo o Solver para determinar el valor de z obligando a que la suma sea 35. Puede repetir el procedimiento con otro par de valores de x e y (por ejemplo) cuantas veces quiera.
Tendrá que usar menos variables. Para ello es muy probable que pueda formular algún prefiltrado para reducir el número de soluciones factibles que deba evaluar Solver.
Buen día Me puede indicar exactamente como se haría para encontrar el valor aproximado de cierta cantidad usando solver, osea que no sea exacto el dato
Para encontrar el valor más próximo a uno dado, le sugiero que calcule en una columna auxiliar la diferencia de sus valores con respecto a éste en valor absoluto, y que busque la mínima diferencia con Solver.
Estimado Rafael ante todo espero ud y sus seres queridose encuentre bien, por ello le hago llegar mis bendiciones , tengo un problema el complemento Solver no carga y me sale el siguiente error Error de compilación en módulo oculto: SolverCode De antemano agradezco su ayuda con respecto al problema antes mencionado
Muchas gracias por sus amables palabras. En cuanto a su consulta, el error de compilación puede deberse a las características de la instalación (a 32 o a 64 bits) del sistema operativo, de Excel o del complemento Solver, pero no me es posible conocer el motivo concreto sin tener acceso a su equipo.
@@eaae Muchas gracias corregí el error copiando archivos a la carpeta de excel de2bits a 64 bits, pero ahora tengo un error en la formula, me sale error
@@pusheenlovers9437 Me alegro de que haya conseguido resolver el primer problema. Pero no puedo ayudarle con el segundo si no describe con algo de detalle qué le ocurre...
@@pusheenlovers9437 Pero tiene que ser más explícito al indicar qué fórmula está introduciendo y cómo la valida, entre otros aspectos. Si sólo me dice que "al dar enter sale error", lo único que puedo aconsejarle es que "no dé enter"... ;-)
Hola, utilice tal cual el tutorial en versión de excel 2019 y desde la primer formula donde asignamos las matrices me arroja error : #!valor! , Lo que detecte es que la puntuacion que usas varia de la mia yo no puedo separar matrices con punto y coma, tengo que usar solo coma o no le deja pasar ala siguiente matriz. Me puedes asesorar porfavor. Gracias.
@@eaae ya quedo, eran dos problemas el primero es que tenia celdas en cero y la otra es que el rango que tenia en la columna es de mas de 350 lineas, fraccione en 3 partes la columna, hay forma de configurar para rangos de busqueda tan amplios ? O que me conviene hacer para estos extensos volumenes de informacion ? Saludos .
Hola buenas tardes si no hay solucion exacta se puede poner que tenga un margen de erroro que nos de el numero mas cercano me explico si necesito que me sume 800.000 pero es imposible el me puede dar una suma de 850.000 0 de 780.000 paa uno hacer el juste manual?
Por supuesto, esa opción existe: podría buscar un ajuste que diese la solución más cercana al valor deseado, aunque entonces se trataría de un problema de naturaleza distinta al que se plantea en el tutorial. Le animo a probar a adaptar esa técnica a su necesidad y a consultarme cualquier dificultad que le pueda surgir.
Hola, si tengo mas de 1000 variables no me permite usar esta función de Solver, existe alguna forma de hacer que Solver trabaje con esa cantidad de datos? Gracias :)
Solver no puede hacerlo, así que debe formular previamente algún tipo de filtrado. En este vídeo se explica cómo puede hacerlo: th-cam.com/video/5-MhxFBC_RQ/w-d-xo.html.
@@danystylinson3082 Solver puede operar sobre 200 celdas cambiantes. Para trabajar sobre un número mayor deberá utilizar algún criterio de prefiltrado antes de usar Solver.
@@danystylinson3082 Para buscar una cantidad que no sea exacta puede definir una función objetivo que mida la distancia a la cantidad buscada y utilizar una minimización.
Muy bueno, pero me sucede que cuando hay más de una solución posible solver solo me da una, lo probé con valores elegidos intencionalmente para que haya más de un resultado, pero siempre me arroja el mismo
Si parte de la misma combinación inicial, el algoritmo de Solver normalmente llegará a la misma solución. Pero si el número de elementos no es muy grande, puede probar a desarrollar las combinaciones mediante alguna técnica de 'fuerza bruta'.
Solver tiene un número máximo de celdas cambiantes. Si necesita aplicar esta técnica a un caso más amplio, tendrá que probar con algún prefiltrado, que le aconsejo formular, aunque la forma de hacerlo depende muy estrechamente de la naturaleza y de la estructura de sus datos.
Hola, por favor debo conciliar un extracto bancario con demasiadas cifras las cuales se repiten. y se debe buscar el valor de un egreso pero el solver nunca logra dar una solución. como podría encontrar una solución a mi problema.
Si debe buscar el valor de un egreso no repetido, bastaría con un COINCIDIR y un INDICE (th-cam.com/video/SW0WhlFRtwg/w-d-xo.html ). Si el valor está repetido en la lista, pero es único para el cliente de que se trate, puede localizarlo con un COINCIDIR sobre una anidación que incluya el cliente (th-cam.com/video/eHM3JZNwKaM/w-d-xo.html ) o con una búsqueda apoyada en condicionales (th-cam.com/video/4qS5vljL-Vo/w-d-xo.html ). Pero el modo concreto de hacerlo dependerá de la estructura y de la naturaleza de sus datos, así que tendría que tener acceso a su hoja de cálculo para ofrecerle una ayuda más específica. Si quiere, puede facilitarme el acceso y trato de ayudarle.
Solver tiene una limitación en cuanto al número de celdas cambiantes. Si su problema es demasiado ambicioso, tendrá que hacer un filtrado previo de las soluciones factibles.
Hay alguna manera en que se pueda resolver ejemplo: que la suma de 200 transacciones de 500 transacciones con diferentes montos, tiene un total de $2,000,000, se puede identificar cuales podrían ser esas 200 transacciones de acuerdo al monto que me cuadren los 2millones?
Solver tiene un límite en 200 celdas cambiantes, así que no podría establecer tantas variables binarias. Deberá probar algún otro método para prefiltrar las combinaciones.
Si el número de variables es muy grande, Solver puede no ser eficiente y sería conveniente hacer algún tipo de filtrado previo mediante fórmulas, pero la solución dependerá del tipo de datos y de la estructura en su hoja de cálculo. Si me precisa con detalle en qué consiste su problema, intentaré ayudarle.
Tendrá que reducirlas... Necesitará formular algún prefiltrado, aunque el modo de hacerlo dependerá estrechamente de la naturaleza y de la estructura de sus datos.
Si, pasado un tiempo razonable, no le devuelve una solución, puede ser por algún defecto en la especificación del problema. Por ejemplo, porque la función no pueda llegar a un óptimo. ¿Está seguro de estar especificando el problema igual que se hace en el vídeo?
Como quiere contar una par de etapas de todas las posibles, quizás le sirva usar como condición una función lógica O, porque le sirve la etapa 1 "o" la etapa 2. Otra posibilidad que puede probar es construir una condicional simple (SI) que dé valor 1 a la etapa 1 y cero a las demás, y otra condicional que dé valor 1 a la etapa 2 y cero a las demás, y sumar esas dos binarias. En la función de recuento incluiría como condición que el valor de esa suma fuese mayor que 1 (porque eso significaría que una de las dos condicionales habría sido 1). Pruebe estas orientaciones y vuelva a consultarme si encuentra alguna dificultad.
Bueno lo que pasa es que cuando aplico solver dice buscando con esa franja verde que sale y sigue buscando yo he esperado hasta como 12,000 pero se pasa de esa cantidad y sigue buscando. Por eso preguntaba cuanto es el tiempo que debo esperar para que busque una solución 😪
Y NO HAY MANERA DE QUE SIN NUMERO DE CLIENTES SE PUEDA SACAR LOS IMPORTE QUE SUMAN DETERMINADA CANTIDAD=?? PORQUE EN EL BANCO PONEN UN IMPORTE DETERMINADO, QUE SUMA CIERTAS CANTIDADES, PERO NO PONEN ABSOLUTAMENTE NADA, ESOS IMPORTES SON DE DIFERENTES CLIENTES Y HAY QUE ESTAR SUMANDO PARA DAR CON LAS FACTURAS QUE SUMAN ESOS IMPORTES GLOBALES QUE TE HACE EL BANCO EN LOS PAGOS POR EJEMPLO CON TARJETAS DE DEBITO.
@@eaae Ya lo pude hacer, muchas gracias, me sirve bastante porque BANREGIO tiene la maldita mañita de agrupar un montón de depósitos y hay te la tienes que pelar para ver cuales facturas integran ese deposito, en días de gran facturación batallaba mucho...gracias !!!
Si no obtiene el resultado esperado es porque no sigue los pasos al pie de la letra. Las fórmulas involucradas en esta técnica son muy sencillas (nada más que sumas condicionales) así que el problema podría estar en la especificación del problema al usar Solver. Le aconsejo que descargue el archivo de ejemplo y que lo compare con el suyo. Vuelva a consultarnos si no encuentra dónde está su error.
Solver encuentra la solución del problema propuesto casi instantáneamente. SI a usted le tarda demasiado, quizás no haya especificado el programa de manera correcta. Por favor, comparta su caso para que podamos ofrecerle alguna ayuda.
@@eaaeQuiero conocer cuáles facturas corresponden al monto de un pago realizado. Para ello agregué la columna con valores 1 correspondientes a la matriz, realicé la suma correspondiente al objetivo con la función sumaproducto. En Solver agregué las restricción para valores binarios y establecí en optimizar el valor buscado, con programación lineal. Mi hipótesis es que los verdaderos valores que dan como resultado el valor esperado no se encuentran contenidos en las facturas que estoy usando. Solo son 27 datos.
@@LeanManufacturing7 Solver puede tardar en encontrar la solución porque el problema no tenga solución... Si quiere que indaguemos más sobre su caso, por favor, comparta su hoja de cálculo.
![NISAMANEE - TADA (ท้าดา) [ OFFICIAL MV ]](http://i.ytimg.com/vi/hcQHEPMmJpU/mqdefault.jpg)
Uno se da cuenta lo útil que puede ser este procedimiento si ha jugado muchas veces a hacer sumas manuales para encontrar a qué facturas corresponde un cobro conjunto. Gracias por el aporte.
Son muchas las posibles aplicaciones de la "antisuma". Por supuesto, la que se explica en el vídeo es una, pero el método descrito puede resolver otros problemas parecidos. Por ejemplo, si se dispone de varias piezas de algún producto con distinto peso y se desea tomar varias hasta completar un cierto total; o si se desea descontar algunos títulos para alcanzar el importe que se necesite para atender un pago, o si se quiere saber qué tareas de entre varias se pueden terminar en un determinado plazo... Los casos son variadísimos, y espero que este procedimiento sirva para resolverlos.
Gracias, mu útil realice pruebas y me funciona a la perfección, mil mil gracias
Gracias, Sandra. Me alegro de que le haya resultado útil.
Te tengo que agradecer que hayas hecho este video. Estamos buscando el total de unos importes en mis facturas y no habia manera. Gracias al video han sido los 7 minutos emplezas en tiempo. Aunque he tenido que utilizar opciones ya que tenias 260 filas para encontrar el resultado y subir el tiempo maximoy las interacciones para resolver el resultado. Aun asi el video es de 10!
Muchas gracias, Carlos. Me alegro de que le haya gustado este vídeo y de que le haya servido para resolver sus cálculos en poco tiempo. El objetivo que tienen todos nuestros tutoriales es resultarles de ayuda, así que su comentario nos anima a seguir ofreciéndoles contenidos tan interesantes como este.
MUCHAS GRACIAS. Recién estoy descubriendo el canal y me preocupa que no veo videos mas recientes. ojalá continúen con el trabajo porque los videos me parecen muy eficientes y eficaces en el tratamiento de los temas que aborda. Muchísimas gracias de nuevo!!!!
Muchas gracias. Me alegro de que le gusten nuestros contenidos. Por favor, ordene los vídeos por antigüedad, porque publicamos vídeos casi todas las semanas. De hecho, el último vídeo lo hemos publicado precisamente hace unos pocos minutos. Aquí tiene el enlace: th-cam.com/video/zOmEOXQ8IPI/w-d-xo.html.
Soy un apasionado por EXCEL aunque tengo un nivel bajo de conocimientos; pero veo tutoriales en cantidad y si de algo estoy seguro, es que sus tutoriales son de los MEJORES que he tenido la oportunidad de disfrutar.
Mil y mil gracias por aportarnos tan valiosos conocimientos.
Muchas gracias por su comentario.
Le invito a que siga viendo muchos tutoriales, en este canal y en otros. Seguro que encuentra enseñanzas muy útiles. Incluso los tutoriales que menos le convenzan pueden ser una valiosa herramienta de aprendizaje si busca formas alternativas para conseguir los objetivos.
Por supuesto, en este canal puede dejarme un comentario con sugerencias o con dudas o pidiendo consejo.
@@eaae Altamente agradecido.
Buen día.
como ya viene siendo habitual excelente vídeo, lleno de sabiduría, bravo por usted señor Rafael
Muchas gracias, Antonio.
Gracias excelente explicacion. Me ayudo mucho en un trabajo que necesitaba realizar.
Me alegro de que le haya resultado útil. Gracias por comentar.
Genial! Para un arqueo rápido y eficaz es imprescindible
Efectivamente, esa es una utilidad directa de esta técnica. Me alegro de que le resulte interesante.
Rafael: Me gustó mucho el uso de la restricción binaria, pues usualmente yo estaba acostumbrado a utilizar SOLVER con restricciones del tipo de programación lineal. Excelente trabajo.
Me alegro de que le haya parecido interesante. Ésta es sólo una de las muchas aplicaciones que pueden tener Solver y la programación lineal. Seguramente mostraré otras en futuros vídeos.
Le agradezco el comentario.
Buenos días. Una vez practicado con el modelo me he encontrado unos detalles que me gustaría comentar.
1º En el vídeo no se señala que la formula SUMAPRODUCTO, la de la celda D16, es matricial y debe validarse con CTRL+SHIFT+ENTER. Si no se hace así, generará un error de valor. Descargando en fichero se ve que validó así, ya que lleva los{}. Quizá pueda resultar evidente, ya que los argumentos son rangos, pero si no se tiene práctica con FM, seguro que no lo es tanto. Aunque siempre pienso que se aprende más cuándo surgen errores 😁
2º Seria bueno disponer de una manera para que se limpie el rango de unos y ceros antes de otra prueba. Si no lo limpiamos manualmente, puede aparecer un 1 dónde no corresponde. Aunque Solver señala la nueva solución correcta y la regla condicional colorea las filas correctas. En el minuto 06:34 se puede observar que hay dos filas verdes, las correctas, pero hay otros 3 unos que quedaron del ejemplo anterior. Podría hacerse con una macro pero, siguiendo los usos del canal, seguro que se puede utilizar una función adecuada. De momento se me escapa. No tengo claro si una función, que haga que el rango quede vacío, puede perjudicar el funcionamiento de Solver.
3º Al introducir el formato condicional, que colorea las filas que Solver señala como 1, ojo con cómo hay que poner la referencia de las celdas, para que pueda validar en todas las filas. Excel automatiza con referencia absoluta y no siempre es lo que queremos. No lo es en este caso. En el vídeo se ve, si se observa con detenimiento 😂
Mis disculpas por el comentario, si puede parecer inapropiado. Me ha resultado muy interesante esta práctica y he creído que podría ser útil mi comentario.
Edito con Una pequeña macro que limpia al cambiar de cliente
Si en la pestaña de la hoja seleccionamos ver código y seleccionamos Worksheet y a la derecha seleccionamos Change podemos hacer una macro que ejecute código para cuando hagamos un cambio en la hoja.
Borramos las lineas de código que ha puesto excel que no sean del evento change y en la que se refiere al evento Change ponemos
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$2" Then
Range("H3") = ""
Range("E2:E15") = ""
End If
End Sub
Con esto, Al cambiar el código del cliente se queda en blanco la cantidad cobrada y el rango solver. Para poder guardar la macro hay que guardar el fichero con la extensión de libro habilitado para macros .xlsm
Espero que sea útil
Saludos
Tiene razón. La fórmula de SUMAPRODUCTO (en D16) es matricial y no lo he advertido expresamente. Las llaves que Excel incorpora a la fórmula delatan ese carácter, pero es verdad que sería mejor haberlo mencionado.
También es verdad que no he incorporado un “borrador” o un “limpiador” de resultados para que los únicos unos que se muestren sean los del cliente investigado en cada prueba. Dado que la fórmula del SUMAPRODUCTO ya incluye otro vector (el de coincidencia del código de cliente) para que se multiplique por cero cualquier otro valor de cualquier otro cliente, el complemento Solver encontrará irrelevantes esos unos de pruebas previas, así que ni se molestará en cambiar el valor de la variable para esas facturas. La solución del problema no es tanto la columna de unos como las celdas resaltadas mediante formato condicional, pero es verdad que un “limpiador” haría la presentación visual más coherente, así que debo darle la razón también en este aspecto y agradecerle la macro que ha diseñado para incorporar esa mejora a la hoja de cálculo.
Conseguir ese resultado (o algo parecido) con fórmulas es posible. Se me ocurre habilitar una columna adicional que multiplicase la variable binaria de Solver por la condicional que evalúa la coincidencia del cliente, y ocultar o disimular la primera de las mencionadas. Así, Solver podría hacer su trabajo sin limpiar los valores irrelevantes procedentes de una prueba anterior, pero lo que se mostraría en pantalla estaría limpio de aquellos unos correspondientes a otros clientes.
Finalmente, también es cierto que cuando se introducen referencias mediante el ratón en las fórmulas de evaluación de un formato condicional, Excel las interpreta por defecto como referencias absolutas, y pone dólares (como si fuesen gratis…). Tampoco lo explicito (aunque no sobraría) pero no deja de ser un detalle apreciable en la propia fórmula. Algo parecido ocurre en las fórmulas ordinarias de las celdas de la hoja: Excel adopta el criterio contrario (interpreta por defecto que las referencias son relativas) y no siempre lo explicamos cuando las convertimos en absolutas. En cualquier caso, este aspecto de su comentario también es acertado.
Su comentario, lejos de ser inapropiado, es muy pertinente, así que le felicito por él y le agradezco sinceramente que lo haya compartido con todos y que haya contribuido con sus conocimientos sobre VBA a mejorar este canal.
@@eaae Mis conocimientos De VBA son más bien limitados. Esas lineas de código las aprendí en tutoriales sobre el VBA y las adopte para mis hojas. Es VBA de andar por casa 😂. Me alegra que pueda servir a los suscriptores de canal
Muchiiiiiiiiiiiiiiiiiiiisimaaaaaaaaaaaaaaas gracias, ya me estaba por dar algo haciendo la fórmula y me daba error, ya que no sabía que era matricial, saludos desde RD.
@@anadejesus9474 Consulte sus dudas sin ningún reparo. Entre todos seguro que encontrará la ayuda que necesita.
@@eaae hola me marca un error el cual es demasiadas celdas variables, no me permite hacer el cálculo con 15 mil lineas
El vídeo es muy bueno. Cuando cambias el cliente conviene que borres previamente la columna s/n de 0s y 1s pues deja grabados 1s de procesos previos que pueden inducir a error.
Aunque lo salvas con el formato condicional.
Efectivamente, conviene borrar antes la columna de ceros y unos. Pero aunque no se hiciese, los cálculos no provocarían ningún error, porque los unos residuales seguirían quedando multiplicando por ceros al no coincidir el cliente. Es verdad que la visualización sería más coherente si desapareciesen, pero la verdadera asociación de importes al total la determina el formato condicional.
Excelente, muchas gracias por la explicacion
Gracias a usted. Me alegro de que le haya gustado.
Hola buena tarde, recientemente me he encontrado con su canal y estoy muy emocionando incrementando mis conocimientos en Excel. Muchas gracias en verdad por compartir con todos.
Tengo un problema al aplicar la solución que plantean
Tengo un mensaje "Demasiadas celdas variables". Mi pregunta es: ¿Existe un límite? Justo la formula la quiero usar para lo que el ejemplo lo hace pero mi listado de facturas es de 350 o 400. Ojalá puedan ayudarme.
Abrazo a todos
Muchas gracias por su comentario.
Efectivamente, existe un límite: 200 celdas cambiantes. Si tiene un listado mayor, será necesario algún algún tipo de "pre-filtrado", que se podría automatizar mediante fórmulas o, en otro caso, con alguna herramienta. Pero este paso previo dependerá de la naturaleza y de la estructura de sus datos, así que para darle alguna orientación más precisa tendría que ver su hoja de cálculo.
Aquí tiene algo más de información sobre el límite: support.microsoft.com/es-es/office/definir-y-resolver-un-problema-con-solver-5d1a388f-079d-43ac-a7eb-f63e45925040?ui=es-es&rs=es-es&ad=es.
Excelente y muy bien explicado
Me alegro de que le haya gustado. Muchas gracias por su comentario.
muchas gracias estuvo muy bueno, hay una forma de hacerlo con mas variables 5.000 mas o menos
Solver tiene un límite en 200 variables. Pero para casos con más variables quizás se pueda formular algún filtrado previo.
Sensacional !!
Muchas gracias. Me alegro de que le haya gustado.
Bravo, que excelente vídeo, lo felicito maestro.
Muchas gracias. Me alegro de que le haya gustado.
Excelente vídeo , muy didáctico como siempre :)
Muchas gracias.
Excelente labor.
Muchas gracias.
Excelente información. Gracias.
Me alegro de que le haya gustado. Gracias por comentar.
Muy buena aplicación de la herramienta SOLVER. Me gustaría hacer una petición al canal referente a la función SUMAPRODUCTO, que se me resiste bastante. Y se trataría de que explicase bien el concepto de la fórmula y de las diferentes formas en que se puede usar. Gracias.
La función SUMAPRODUCTO es una de las más interesantes de Excel. Tomo nota de su sugerencia. La trataremos a fondo en un próximo tutorial.
@@eaae Muchas gracias
Muchas gracias!
De nada. Gracias a usted.
Buena solucion y mezcla
Me alegro de que le haya gustado. Gracias por su comentario.
Excelente
Muchas gracias, Antonio.
Fantástico!
Muchas gracias. Me alegro de que le haya gustado.
A raiz de otro ejercicio de este canal me encaprichado con una idea (que tambien vale para resolver este ejercicio) y he conseguido resolver el ejercicio sin usar solver con una sola formula matrizial que al arrastrar va soltando los valores que componen la suma.
Eso si, es una mega-formula de cuatro renglones que mezcla varias tecnicas.
(la idea es contener una formula matricial una tabla de la verdad explore todas las posibilidaes, encuentre los valores que componen la suma, y al arrastrar vaya soltando los resultados encontrados, tambien se podria hacer que al arrastrar tanto en filas como columnas suelte todas posibilidades encontradas, si hubiese mas de una solucion)
Es probable que se pueda mejorar o depurar la formula ya que soy un aficionado que ha profundizado mucho en algunas cosas y detalles, trucos, etc.. pero en otras no (incluso muy basicas)
saludos
La idea que sugiere me parece muy interesante. Si le parece bien, puede compartir su fórmula y la analizamos.
@@eaae Ok, Gracias por su interés, le adjunto la formula, yo la tengo en "H19" y arrastro hasta que dejan de salir valores.
Formula:
=SI.ERROR(INDICE(EXTRAE(INDICE(DEC.A.BIN(FILA(INDIRECTO("1:"&PRODUCTO(SI(--($B$2:$B$15=$H$2);2;""))))-1;CONTAR.SI($B$2:$B$15;$H$2)); COINCIDIR(1;--(MMULT(--EXTRAE(DEC.A.BIN(FILA(INDIRECTO("1:"&PRODUCTO(SI(--($B$2:$B$15=$H$2);2;""))))-1;CONTAR.SI($B$2:$B$15;$H$2));TRANSPONER(FILA(INDIRECTO("1:"&CONTAR.SI($B$2:$B$15;$H$2))));1);
INDICE($D$2:$D$15;N(SI(1;K.ESIMO.MENOR(SI(--($B$2:$B$15=$H$2);FILA($D$2:$D$15)-FILA($D$1);"");FILA(INDIRECTO("1:"&CONTAR.SI($B$2:$B$15;$H$2))))))))=$H$3);0));FILA(INDIRECTO("1:"&CONTAR.SI($B$2:$B$15;$H$2)));1)*INDICE($D$2:$D$15;N(SI(1;K.ESIMO.MENOR(SI(--($B$2:$B$15=$H$2);FILA($D$2:$D$15)-FILA($D$1);"");FILA(INDIRECTO("1:"&CONTAR.SI($B$2:$B$15;$H$2)))))));FILAS($G$19:G19));"-")
@@feral7691 Su técnica me parece muy ingeniosa. He comprobado que funciona, así que le felicito por esta aportación tan interesante, que le agradezco haya compartido para provecho de todos los seguidores del canal. La analizaré más adelante para intentar simplificarla (si me es posible) y no descarto dedicarle un futuro tutorial en el que, con su permiso, estaré encantado de citarle.
@@eaae Encantado, si veo la solución en video, y si es mejorada o pulida (si es posible) mejor, asi aprendo, integro algo mas.
@@feral7691 No sé cuándo podré dedicarme a ello, porque tengo varios proyectos en mente, pero seguro que es un contenido estupendo para el canal. Le quedo muy agradecido.
Muy buen video, gran canal. Solo un tema, está herramienta aparenetemente solo funciona con valores absolutos, porque tengo que a una suma con distintos importes (positivos y negativos), sin embargo está herramienta solo tomaba solo los valores positivos...
En esta formulación no se toman valores absolutos, así que debería funcionar tanto con los positivos como con los negativos. Si quiere que le eche un vistazo a su hoja de cálculo facilíteme un enlace y trataré de ver por qué no obtiene los resultados esperados.
Me encanto el video. Necesito una fórmula que me tome un rango más amplio, por que con solver me dice que supera e límite de filas. Además necesito identificar los valores de varias diferencias. La base es más amplia
Me alegro de que le haya gustado. Es cierto que Solver tiene un número máximo de celdas cambiantes. Si necesita aplicar esta técnica a un caso más amplio, tendrá que probar con algún prefiltrado, que le aconsejo formular, aunque la forma de hacerlo depende muy estrechamente de la naturaleza y de la estructura de sus datos.
Muy útil y bien explicado el video. Gracias! Aunque me surgió una duda: ¿existe alguna forma de configurar a Solver para que encuentre los valores de una lista que se acerquen (sin llegar a ser exacto) a un valor arbitrario definido por el usuario?
Puede resolverlo incluso sin usar Solver, creando una variable auxiliar que calcule la diferencia entre el número buscado y los de su lista de búsqueda. Para evitar los valores negativos puede usar valores absolutos o cuadrados. Una función MIN sobre ese rango de diferencias le permitirá identificar esa diferencia mínima. Para conocer de qué valor procede bastaría con funciones básicas de búsqueda y referencia (INDICE y COINCIDIR, por ejemplo).
Por supuesto, también puede conseguir el mismo resultado con Solver, pero quizás no sea necesario recurrir a esta herramienta.
Si necesita alguna ayuda más, no dude en consultarme.
Excelencia! *****
Muchas gracias.
Muy bien explicado, gracias. Me salto solo una duda, hasta cuantas filas puede resolver Solver?
Solver puede trabajar hasta con 200 celdas cambiantes. En este vídeo tiene un tutorial introductorio sobre esta potente herramienta: th-cam.com/video/gi2Llvi5C0Q/w-d-xo.html.
Súper video, una pregunta. Es indispensable (para la suma producto) la condición del "cliente"? yo estoy resolviendo un problema pero solo cuento con la lista de valores que y con el saldo que deseo identificar a partir de la suma de los valores. No cuento con información de "cliente".
La condición del cliente en la función SUMAPRODUCTO no es imprescindible. Si prescinde de ella la técnica funcionará igualmente.
Buenas noches caballero, soy nuevo en el canal y lo felicito por el coto enido, es estupendo!. Tengo una consulta... Yo necesito que entre 2 o 3 celdas de un largo inventario me busque la mayor cantidad de combinaciones con un máximo de 30. Es decir si tengo 100 productos con valores desde 5 hasta 20(5,6,7,8,9,10,11,12,13hasta 20) me busque todas las variables posibles de combinación. Cómo en este vídeo pero que me dé varias variables con un máximo de 30 de valor en total. UD podría ayudarme con ello?
Puede hacer su búsqueda sobre una tabla de combinaciones construida con la técnica que se explica en este otro vídeo: th-cam.com/video/6mAB4PNAKwU/w-d-xo.html.
@@eaae excelente hermano! Pero tengo el problema que quiero que me busque un total específico entre 2 productos o 3. De los 100 en total.
Ejemplo si tengo
Bobina 1 : 20 pesos
Bobina 2 : 12 pesos
Bobina 3 : 9 pesos
Bobina 4 : 17 pesos
Bobina 5 : 10 pesos
Que me diga que tengo que combinar la bobina 1 con la 5 y la 4 con la 2.
El problema lo tengo para poner el total a buscar entre las combinaciones, sino, puede combinar y supera ampliamente el valor lo cual es un dato que no serviría
Muchas gracias desde ya por su amabilidad
@@santiagocerioni8551 Tiene que especificar con precisión el criterio (o los criterios) que deben cumplir las combinaciones elegibles.
Buena tardes. Muchas gracias por su clara y completa explicación. Debo aplicar solver sobre 52 variables y veo que no termina nunca de resolverse. He probado con menos para verificar estar haciendolo correctamente y sí funciona. Existe alguna forma de salvar este problema?
El número de iteraciones necesarias crece exponencialmente con el número de variables. Para mejorar la eficiencia del procedimiento habría que conocer un poco mejor la naturaleza de sus variables y qué condiciones debe tener la solución. A veces se puede probar con una una preselección de variables o una acotación de valores, pero son opciones muy casuísticas.
@@eaae Muchísimas gracias
@@veronicafinocchiaro9746 De nada. Si quiere que vea su proyecto, intentaré hacerle alguna sugerencia un poco más específica.
Excelente video. Una pregunta. Existe alguna forma de hacerlo pero trabajar con un rango? Por ejemplo. El monto que yo deseo pero con una varianza de 1 dolar? Ya sea oara arriba o oara abajo?
Puede intentarlo modificando las restricciones en la optimización para que se admitan valores con el margen que desee y cambiando el objetivo a minimización.
@@eaae no he podido hacerlo aun ☹️
@@baycr0919 Que tal, lo pudiste resolver? tambien ando intentando
buen video, una consulta ¿hasta cuantos valores puedo utilizarlo? gracias
Solver tiene un límite fijado en doscientas celdas cambiantes.
Buenas, gracias por el video. Tengo una duda: en el caso de que haya mas de una solución para llegar a esa suma, como puede solver señalarme esa distinta opción que tambien sume mi cifra objetivo. No sé si me explico
Gracias!
Pruebe a ejecutar Buscar Objetivo con otro punto de partida. La herramienta utiliza una técnica sucesiva que puede ser sensible a los valores iniciales.
Hola, ya que solver solo permite hasta 200 variables, si requiero que realice eso por varios grupos, con la condicion que los rangos son cedulas, como puedo realizar solver por medio de programación VBA para poder poner dinamico todos los rango?
Estupendo vídeo.
¿Me podría ayudar en un problema?
Tengo dos columnas de datos que suman lo mismo. Tengo que conciliarlas pero están agrupadas de forma distinta. Tendría que hacer sumas de la primera columna para encontrar un dato de la segunda columna y viceversa.
¿Es posible con Solver?
Muchas gracias
Una posibilidad sería reiterar el cálculo del procedimiento descrito para cada uno de los valores de la segunda columna. Si lo que quiere es automatizar el cálculo de todos los valores a la vez, quizás el problema llegue a tener un número excesivo de variables (dependería de cuántas filas tenga cada columna y cuántas sumas requiera). Una posibilidad sería asignar códigos a cada suma y tratar de resolver las sumas mediante valores binarios en ambas columnas. Le sugiero que pruebe esta opción y que vuelva a consultarme si no consigue ningún avance.
@@eaae ¿Sería posible un enlace o ejemplo de este cálculo? De un listado de facturas necesito saber cuáles suman una base imponible de x y además, una cuota de iva de y. Gracias y enhorabuena por su canal!
@@Msolrs La solución depende muy estrechamente de la naturaleza y de la estructura de los datos. Por favor, comparta un ejemplo de sus datos e intentamos darle alguna orientación más precisa.
Excelente video, solo tengo una duda en el numero de cliente para mi todos son el mismo y al momento de agregar la formula sumaproducto me aparece error, sera por ese motivo?
Muchas gracias, Miguel Ángel. Aunque todos los clientes tuviesen el mismo número de identificación la fórmula debería ofrecer un resultado correcto. ¿Qué error obtiene?
En tu segundo ejemplo, aun habia 1 y 0 de la solucion anterior. Quiza conviene indicar que se debe borrar el resultado anterior para que no haya confusion.
No es necesario borrar el resultado anterior. Precisamente se dispone de una celda amarilla rotulada como diferencia para evidenciar si la solución binaria de la columna E satisface o no la condición del importe total.
Rafael como estas? tengo el mismo caso pero con 3000 celdas de valores. Me dice que es muy extenso y si debo considerarlas todas. Que puedes sugerirme?
Solver tiene un máximo de 200 celdas cambiantes. Si debe abordar un problema de mayores dimensiones tendrá que ingeniar algún sistema de filtrado previo para descartar valores no factibles.
Buen vídeo, el problema que no cumple para que den suman cero en cuentas contables o cuentas corrientes.
Por favor, describa mejor el problema al que se refiere e intentaré ofrecerle una solución.
Gracias por el video, tengo una pregunta. ¿ qué pasa si uno de los datos se repite y puede haber más de una solución? Me sería muy útil
Puede ejecutar SOLVER con otro vértice inicial, aunque no le puedo garantizar que se alcancen todas las demás soluciones distintas. Otra posibilidad es que formule una condicional que le advierta cuando alguno de los valores designados en la solución esté repetido también fuera de ella, lo que le permitirá revisar exógenamente esos casos.
Hola!, muchas gracias por el video, me ha estado ayudando mucho, pero tengo una consulta. Cuando está resolviendo, veo que en la fila "binaria" empiezan a aparecer número con decimales (0,2 o 0,8, etc), por lo que asumo que está buscando con parciales de montos para que de el número que está buscando.
Hay una forma de que sólo busque con el número entero? O sea, si tengo un monto de 1.000, sume ese número completo y no vaya parcializándolo?, porque tengo listados más grandes y al hacer eso toma mucho más tiempo buscando el monto que corresponde.
De antemano muchas gracias!
Si configura Solver con una restricción para que los valores de la columna en cuestión sean binarios no deben aparecer números con decimales, sino exclusivamente ceros y unos.
Buenas tardes sres excel avanzado para administradores de empresase, les escribo ya que le solicito su ayuda con respecto al tema tratado en este video , ya que basado en el video y en el paso a paso adapte lel arcchivo de excel a la informacion que requiero consultar pero no me da los resultados que se ven en el video ; al utiliar la formula sumar producto , utilizado los rangos de celdas aplicados en el video una vez realizado la formula me dice que son referencias circulares y me da como resultado cero, como pueden ayudar para realizar la consulta que requiero, donde les puedo enviar el archivo con la informacion modificada o contactarme con ustedes para que ayuden a resolver la consulta y me funciones excatamente como sale en el video, quedo atento a su respuesta y me pueda ayudar
Si sigue los pasos del vídeo, obtendrá los resultados deseados. Las fórmulas no deben provocar referencias circulares, pero le advierto de que deben validarse matricialmente. Por favor, revise dónde se produce la autorreferencia y háganoslo saber.
Muy interesante, podria ayudarme con una duda?, como puedo hacer para obtener tres valores aleatorios que me den un resultado dado, le explico.
x+y+z/n =35
Donde:
x, y, z = valores numéricos aleatorios positivos que no deben pasar de 35
n = promedio de x, y, z
35 = valor que debe darse como resultado.
Fije arbitrariamente los valores de x e y (por ejemplo) y deje libre el de z. En una celda aparte calcule la suma de x, y y el cociente de z entre el promedio. Use BuscarObjetivo o Solver para determinar el valor de z obligando a que la suma sea 35. Puede repetir el procedimiento con otro par de valores de x e y (por ejemplo) cuantas veces quiera.
excelente video, pero solver no me permite realizar mas de 600 variables, algún consejo?
Tendrá que usar menos variables. Para ello es muy probable que pueda formular algún prefiltrado para reducir el número de soluciones factibles que deba evaluar Solver.
Buen día
Me puede indicar exactamente como se haría para encontrar el valor aproximado de cierta cantidad usando solver, osea que no sea exacto el dato
Para encontrar el valor más próximo a uno dado, le sugiero que calcule en una columna auxiliar la diferencia de sus valores con respecto a éste en valor absoluto, y que busque la mínima diferencia con Solver.
Estimado Rafael ante todo espero ud y sus seres queridose encuentre bien, por ello le hago llegar mis bendiciones ,
tengo un problema el complemento Solver no carga y me sale el siguiente error
Error de compilación en módulo oculto: SolverCode
De antemano agradezco su ayuda con respecto al problema antes mencionado
Muchas gracias por sus amables palabras. En cuanto a su consulta, el error de compilación puede deberse a las características de la instalación (a 32 o a 64 bits) del sistema operativo, de Excel o del complemento Solver, pero no me es posible conocer el motivo concreto sin tener acceso a su equipo.
@@eaae Muchas gracias corregí el error copiando archivos a la carpeta de excel de2bits a 64 bits,
pero ahora tengo un error en la formula, me sale error
@@pusheenlovers9437 Me alegro de que haya conseguido resolver el primer problema. Pero no puedo ayudarle con el segundo si no describe con algo de detalle qué le ocurre...
@@eaae Estimado saludos cordiales
bueno el error que tengo es cuando digito la formula le doy enter y me sale formula error
@@pusheenlovers9437 Pero tiene que ser más explícito al indicar qué fórmula está introduciendo y cómo la valida, entre otros aspectos. Si sólo me dice que "al dar enter sale error", lo único que puedo aconsejarle es que "no dé enter"... ;-)
Hola, utilice tal cual el tutorial en versión de excel 2019 y desde la primer formula donde asignamos las matrices me arroja error : #!valor! , Lo que detecte es que la puntuacion que usas varia de la mia yo no puedo separar matrices con punto y coma, tengo que usar solo coma o no le deja pasar ala siguiente matriz. Me puedes asesorar porfavor. Gracias.
Puede que en la configuración de su equipo se requieran comas para separar los argumentos. Por favor, prueba a hacerlo así.
@@eaae ya quedo, eran dos problemas el primero es que tenia celdas en cero y la otra es que el rango que tenia en la columna es de mas de 350 lineas, fraccione en 3 partes la columna, hay forma de configurar para rangos de busqueda tan amplios ? O que me conviene hacer para estos extensos volumenes de informacion ? Saludos .
@@erickc.c.8967 Solver tiene un límite de doscientas celdas cambiantes. Para casos como este es necesario utilizar algún criterio de filtrado previo.
es posible realizarlo en google sheets?
En Google Sheets también hay recursos similares a Solver.
@@eaae tu contenido es excelente, pero no encontré nada similar para hacerlo en google sheets, nos podrías ayudar?
Buenas tardes Gracias; me sirve para 4000 transacciones?
Solver tiene una limitación en 200 celdas cambiantes.
Hola buenas tardes si no hay solucion exacta se puede poner que tenga un margen de erroro que nos de el numero mas cercano me explico si necesito que me sume 800.000 pero es imposible el me puede dar una suma de 850.000 0 de 780.000 paa uno hacer el juste manual?
Por supuesto, esa opción existe: podría buscar un ajuste que diese la solución más cercana al valor deseado, aunque entonces se trataría de un problema de naturaleza distinta al que se plantea en el tutorial. Le animo a probar a adaptar esa técnica a su necesidad y a consultarme cualquier dificultad que le pueda surgir.
Hola, si tengo mas de 1000 variables no me permite usar esta función de Solver, existe alguna forma de hacer que Solver trabaje con esa cantidad de datos? Gracias :)
Solver tiene un límite cuantitativo para el número de celdas cambiantes, así que tendrá que hacer un filtrado precio de las posibilidades.
hola consulta al momento de restar en la diferencia para que de 0 me sale error de valor , me pueden ayudar porfavor
Hola muy buenas, al utilizar la funcion Y no me pintan las celdas. ¿A que puede deberse?
El motivo más probable es una incorrecta configuración del formato condicional.
Se puede hacer esto con OpenOffice?
Sí.
Como lograr que excel analice mas de 200 celdas variables? Gracias
Solver no puede hacerlo, así que debe formular previamente algún tipo de filtrado. En este vídeo se explica cómo puede hacerlo: th-cam.com/video/5-MhxFBC_RQ/w-d-xo.html.
como podría hacerlo para un rango mas amplio de datos?
¿De cuántos datos?
unos 500@@eaae
y también si quisiera que la cantidad a encontrar no fuera exacta, si no mas bien lo mas cercano posible a la cantidad buscada
@@danystylinson3082 Solver puede operar sobre 200 celdas cambiantes. Para trabajar sobre un número mayor deberá utilizar algún criterio de prefiltrado antes de usar Solver.
@@danystylinson3082 Para buscar una cantidad que no sea exacta puede definir una función objetivo que mida la distancia a la cantidad buscada y utilizar una minimización.
Muy bueno, pero me sucede que cuando hay más de una solución posible solver solo me da una, lo probé con valores elegidos intencionalmente para que haya más de un resultado, pero siempre me arroja el mismo
Si parte de la misma combinación inicial, el algoritmo de Solver normalmente llegará a la misma solución. Pero si el número de elementos no es muy grande, puede probar a desarrollar las combinaciones mediante alguna técnica de 'fuerza bruta'.
Pero solo funciona con 200 registros. Alguna alternativa?
Solver tiene un número máximo de celdas cambiantes. Si necesita aplicar esta técnica a un caso más amplio, tendrá que probar con algún prefiltrado, que le aconsejo formular, aunque la forma de hacerlo depende muy estrechamente de la naturaleza y de la estructura de sus datos.
Hola, por favor debo conciliar un extracto bancario con demasiadas cifras las cuales se repiten. y se debe buscar el valor de un egreso pero el solver nunca logra dar una solución. como podría encontrar una solución a mi problema.
Si debe buscar el valor de un egreso no repetido, bastaría con un COINCIDIR y un INDICE (th-cam.com/video/SW0WhlFRtwg/w-d-xo.html ). Si el valor está repetido en la lista, pero es único para el cliente de que se trate, puede localizarlo con un COINCIDIR sobre una anidación que incluya el cliente (th-cam.com/video/eHM3JZNwKaM/w-d-xo.html ) o con una búsqueda apoyada en condicionales (th-cam.com/video/4qS5vljL-Vo/w-d-xo.html ).
Pero el modo concreto de hacerlo dependerá de la estructura y de la naturaleza de sus datos, así que tendría que tener acceso a su hoja de cálculo para ofrecerle una ayuda más específica. Si quiere, puede facilitarme el acceso y trato de ayudarle.
Cómo se hace si la base de datos es grande y solver arroja un error de que son muchas variables?
Solver tiene una limitación en cuanto al número de celdas cambiantes. Si su problema es demasiado ambicioso, tendrá que hacer un filtrado previo de las soluciones factibles.
Hay alguna manera en que se pueda resolver ejemplo: que la suma de 200 transacciones de 500 transacciones con diferentes montos, tiene un total de $2,000,000, se puede identificar cuales podrían ser esas 200 transacciones de acuerdo al monto que me cuadren los 2millones?
Solver tiene un límite en 200 celdas cambiantes, así que no podría establecer tantas variables binarias. Deberá probar algún otro método para prefiltrar las combinaciones.
@@eaae entiendo muchas gracias
Para una base de datos mucho más amplia el solver se queda mucho tiempo y no sé si va terminar o se quedó en el proceso. Cómo puedo solucionar
Si el número de variables es muy grande, Solver puede no ser eficiente y sería conveniente hacer algún tipo de filtrado previo mediante fórmulas, pero la solución dependerá del tipo de datos y de la estructura en su hoja de cálculo. Si me precisa con detalle en qué consiste su problema, intentaré ayudarle.
que puedo hacer en caso de que me diga "hay demasiadas celdas variables" :(
Tendrá que reducirlas... Necesitará formular algún prefiltrado, aunque el modo de hacerlo dependerá estrechamente de la naturaleza y de la estructura de sus datos.
@@eaae Bueno, muchas gracias.
Para 4 intentos u encontrar una sola solución invirtió Excel hora y media
Por favor, comparta su ejemplo para que podamos analizar su caso.
buenas tardes, al momento de ejecutar solve se queda leyendo mucho resolviendo el problema pero sin darme el resultado
Si, pasado un tiempo razonable, no le devuelve una solución, puede ser por algún defecto en la especificación del problema. Por ejemplo, porque la función no pueda llegar a un óptimo. ¿Está seguro de estar especificando el problema igual que se hace en el vídeo?
Siempre quedo así:
😧
Muchas gracias, Oswaldo.
=CONTAR.SI.CONJUNTO($EI:$EI;">="&$M$3;$EI:$EI;"=15";$CV:$CV;"
Como quiere contar una par de etapas de todas las posibles, quizás le sirva usar como condición una función lógica O, porque le sirve la etapa 1 "o" la etapa 2. Otra posibilidad que puede probar es construir una condicional simple (SI) que dé valor 1 a la etapa 1 y cero a las demás, y otra condicional que dé valor 1 a la etapa 2 y cero a las demás, y sumar esas dos binarias. En la función de recuento incluiría como condición que el valor de esa suma fuese mayor que 1 (porque eso significaría que una de las dos condicionales habría sido 1).
Pruebe estas orientaciones y vuelva a consultarme si encuentra alguna dificultad.
Una pregunta, solver hasta cuantas veces busca la solución, es que llevo mucho tiempo esperando 😣
Solver busca la solución hasta que la encuentra o hasta que concluye que no puede hacerlo. ¿Ha conseguido llegar ya al final del proceso de cálculo?
@@eaae no estuve esperando como 5 min y nada 😔
@@marilupolonicacio9803 Describa el problema que intenta optimizar para que podamos analizar si está bien especificado.
Bueno lo que pasa es que cuando aplico solver dice buscando con esa franja verde que sale y sigue buscando yo he esperado hasta como 12,000 pero se pasa de esa cantidad y sigue buscando. Por eso preguntaba cuanto es el tiempo que debo esperar para que busque una solución 😪
@@marilupolonicacio9803 El tiempo dependerá de la concreta especificación del problema. Si nos la describe, quizás podamos darle alguna orientación.
Y NO HAY MANERA DE QUE SIN NUMERO DE CLIENTES SE PUEDA SACAR LOS IMPORTE QUE SUMAN DETERMINADA CANTIDAD=?? PORQUE EN EL BANCO PONEN UN IMPORTE DETERMINADO, QUE SUMA CIERTAS CANTIDADES, PERO NO PONEN ABSOLUTAMENTE NADA, ESOS IMPORTES SON DE DIFERENTES CLIENTES Y HAY QUE ESTAR SUMANDO PARA DAR CON LAS FACTURAS QUE SUMAN ESOS IMPORTES GLOBALES QUE TE HACE EL BANCO EN LOS PAGOS POR EJEMPLO CON TARJETAS DE DEBITO.
Puede hacer esa comprobación simplemente prescindiendo del campo que identifica a los clientes.
@@eaae Ya lo pude hacer, muchas gracias, me sirve bastante porque BANREGIO tiene la maldita mañita de agrupar un montón de depósitos y hay te la tienes que pelar para ver cuales facturas integran ese deposito, en días de gran facturación batallaba mucho...gracias !!!
@@MegaALBERTOSALINAS Muchas gracias por su comentario. Me alegro de que le haya sido útil.
Hola, he puesto la fórmula varias veces pero no puedo resolverla, siempre me da "#¡VALOR!"
Si no obtiene el resultado esperado es porque no sigue los pasos al pie de la letra. Las fórmulas involucradas en esta técnica son muy sencillas (nada más que sumas condicionales) así que el problema podría estar en la especificación del problema al usar Solver. Le aconsejo que descargue el archivo de ejemplo y que lo compare con el suyo. Vuelva a consultarnos si no encuentra dónde está su error.
¿Por qué solver tarda tanto en encontrar la solución?
Solver encuentra la solución del problema propuesto casi instantáneamente. SI a usted le tarda demasiado, quizás no haya especificado el programa de manera correcta. Por favor, comparta su caso para que podamos ofrecerle alguna ayuda.
@@eaaeQuiero conocer cuáles facturas corresponden al monto de un pago realizado. Para ello agregué la columna con valores 1 correspondientes a la matriz, realicé la suma correspondiente al objetivo con la función sumaproducto. En Solver agregué las restricción para valores binarios y establecí en optimizar el valor buscado, con programación lineal. Mi hipótesis es que los verdaderos valores que dan como resultado el valor esperado no se encuentran contenidos en las facturas que estoy usando. Solo son 27 datos.
@@LeanManufacturing7 Solver puede tardar en encontrar la solución porque el problema no tenga solución... Si quiere que indaguemos más sobre su caso, por favor, comparta su hoja de cálculo.
😵😩😩😩😩 Me responde " Demasiadas celdas de variables"💀💀💀💀💀💀
El complemento Solver tiene un límite de 200 celdas cambiantes. Si su problema es más amplio, tendrá que ingeniar alguna forma de prefiltrado.