Consejo: Otros idiomas son traducidos por Google. Puedes visitar el English versión de este enlace.
Iniciar sesión
x
or
x
x
Suscríbete
x

or

¿Cómo encontrar todas las combinaciones que equivalen a una suma dada en Excel?

Por ejemplo, tengo la siguiente lista de números, y ahora, quiero saber qué combinación de números en la lista resume a 480, en la siguiente captura de pantalla que se muestra, puede ver que hay cinco grupos de combinaciones posibles que suman igual a 480, como 300 + 60 + 120, 300 + 60 + 40 + 80, etc. En este artículo, hablaré sobre algunos métodos para encontrar qué celdas resumen un valor específico en Excel.


Encuentre y enumere todas las combinaciones que equivalen a una suma dada de forma rápida y sencilla en Excel

Kutools for Excel's Hacer un número La utilidad puede ayudarlo a buscar y enumerar todas las combinaciones y combinaciones específicas que sean iguales y rápidas a un número de suma dado. Haga clic para descargar Kutools para Excel!

Kutools for Excel: con más de 300 útiles complementos de Excel, de prueba sin límite en días 60. Descargar y prueba gratis ahora!


Encuentre una combinación de celdas que iguale una suma dada con fórmulas

Primero, debe crear algunos nombres de rango y luego aplicar una fórmula de matriz para encontrar las celdas que suman el valor objetivo, haga lo siguiente paso a paso:

1. Seleccione la lista de números y defina esta lista como un rango-- Range1 en el Nombre de buzóny presione entrar tecla para finalizar el nombre del rango definido, ver captura de pantalla:

2. Después de definir un nombre de rango para la lista de números, entonces necesita crear dos nombres de rango adicionales en el Administrador de nombre caja, por favor haga clic Fórmulas > Administrador de nombre, En el Administrador de nombre cuadro de diálogo, haga clic en Nueva botón, ver capturas de pantalla:

3. En el estallado Nuevo nombre diálogo, ingrese un nombre List1 en el Nombre campo, y escriba esta fórmula = FILA (INDIRECTA ("1:" & ROWS (Range1))) (Range1 es el nombre de rango que ha creado en step1) en el Se refiere a campo, ver captura de pantalla:

4. Hacer clic OK para regresar al Administrador de nombre diálogo, luego continúe haciendo clic Nueva para crear otro nombre de rango, en Nuevo nombre diálogo, ingrese un nombre List2 en el Nombre campo, y escriba esta fórmula = FILA (INDIRECTA ("1:" & 2 ^ FILAS (Range1))) (Range1 es el nombre de rango que ha creado en step1) en el Se refiere a campo, ver captura de pantalla:

5. Después de crear los nombres del rango, aplique la siguiente fórmula de matriz en la celda B1:

=IF(ISNUMBER(MATCH(ROWS($1:1),IF(INDEX(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),MATCH(TRUE,MMULT(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),Range1)=$C$2,0),),TRANSPOSE(List1)),0)),"X","")y presione Shift + Ctrl + Enter teclas juntas, luego arrastre el controlador de relleno a la celda B8, el último número de la lista, y puede ver los números cuya cantidad total es 480 marcados como X en la columna B, ver captura de pantalla:

  • Notas:
  • En la fórmula larga anterior: List1, List2 y Range1 son los nombres de rango que ha creado en pasos anteriores, C2 es el valor específico que desea agregar a los números.
  • Si más de una combinación de valores tiene una suma igual al valor específico, solo se enumera una combinación.

Encuentre una combinación de celdas que iguale una suma dada con el complemento Solver

Si está confundido con el método anterior, Excel contiene un Solver Add-in característica, al usar este complemento, también puede identificar los números cuya cantidad total es igual a un valor dado.

1. Primero, necesitas activar esto Solver complemento, ve a Envíe el > Opciones, En el Opciones de Excel cuadro de diálogo, haga clic en Complementos desde el panel izquierdo, y luego haz clic Solver Add-in del Complementos de aplicaciones inactivas sección, ver captura de pantalla:

2. Luego haga clic Go botón para ingresar al Complementos diálogo, verificar Solver Add-in opción, y haga clic OK para instalar este complemento con éxito.

3. Después de activar el complemento Solver, debe ingresar esta fórmula en la celda B9: = SUMPRODUCT (B2: B9, A2: A9), (B2: B9 es una celda de columna en blanco al lado de su lista de números, y A2: A9 es la lista de números que usas ), y presione entrar clave, ver captura de pantalla:

4. A continuación, haga clic en Información > Solver para ir a la Parámetro de Solver cuadro de diálogo, en el cuadro de diálogo, realice las siguientes operaciones:

(1.) Clic para seleccionar la celda B10 donde su fórmula desde el Establecer objetivo sección;

(2.) Luego en el A sección, seleccionar Valor dee ingrese su valor objetivo 480 como necesites;

(3.) Debajo de Al cambiar las celdas variables sección, por favor haga clic para seleccionar el rango de celdas B2: B9 donde marcará sus números correspondientes.

5. Y luego haz clic Añadir botón para ir a la Añadir restricción cuadro de diálogo, haga clic en para seleccionar el rango de celdas B2: B9Y seleccione papelera de la lista desplegable, ver captura de pantalla:

6. Hacer clic OK volver atrás Parámetro de Solver diálogo, luego haga clic Resolver botón, algunos minutos más tarde, un Resultados de Solver cuadro de diálogo aparece, y puede ver la combinación de celdas que equivalen a una suma determinada 480 están marcadas como 1. En el Resultados de Solver diálogo, seleccione Mantenga la solución Solver opción, y haga clic OK para salir del diálogo Ver captura de pantalla:

Nota: Este método también solo puede obtener una combinación de celdas si hay más de una combinación de valores que tenga una suma igual al valor específico.


Encuentre una combinación de celdas que iguale una suma dada con la Función definida por el usuario

Los primeros dos métodos son complejos para la mayoría de nuestros usuarios de Excel, aquí puedo crear un código VBA para resolver este trabajo de manera rápida y fácil.

Para obtener el resultado correcto, primero debe ordenar la lista de números en orden descendente. Y luego haz los siguientes pasos:

1. Mantenga presionado el ALT + F11 teclas para abrir el Microsoft Visual Basic para aplicaciones ventana.

2. Hacer clic recuadro > Móduloy pegue el siguiente código en Módulo Ventana.

Código de VBA: Encuentra la combinación de celdas que equivale a una suma dada:

Function GetCombination(CoinsRange As Range, SumCellId As Double) As String
'updateby Extendoffice
    Dim xStr As String
    Dim xSum As Double
    Dim xCell As Range
    xSum = SumCellId
    For Each xCell In CoinsRange
        If Not (xSum / xCell < 1) Then
            xStr = xStr & Int(xSum / xCell) & " of " & xCell & "  "
            xSum = xSum - (Int(xSum / xCell)) * xCell
        End If
    Next
    GetCombination = xStr
End Function

3. A continuación, guarde y cierre esta ventana de código, y luego regrese a la hoja de trabajo e ingrese esta fórmula = combinación de obtención (A2: A9, C2) en una celda en blanco, y presione entrar clave, obtendrá el siguiente resultado que muestra los números de combinación que equivalen a una suma dada, vea la captura de pantalla:

  • Notas:
  • En la fórmula anterior, A2: A9 es el rango numérico, y C2 contiene el valor objetivo que desea igualar.
  • Si más de una combinación de valores tiene una suma igual al valor específico, solo se enumera una combinación.

Encuentra todas las combinaciones que equivalen a una suma dada con una característica sorprendente

Tal vez todos los métodos anteriores son algo difíciles para usted, aquí, presentaré una herramienta poderosa, Kutools for Excel, Con su Hacer un número característica, puede obtener rápidamente todas las combinaciones que equivalen a una suma dada.

Consejos:Para aplicar esto Hacer un número característica, en primer lugar, debe descargar el Kutools for Excel, y luego aplicar la característica de forma rápida y sencilla.

Después de instalar Kutools for Excel, haz lo siguiente:

1. Hacer clic Kutools > Contenido > Hacer un número, mira la captura de pantalla:

2. Entonces, en el Inventa un número cuadro de diálogo, por favor haga clic para seleccionar la lista de números que desea usar de Fuente de datosy luego ingrese el número total en el Suma cuadro de texto, ver captura de pantalla:

3. Y luego, haz clic OK botón, aparecerá un cuadro emergente para recordarle que seleccione una celda para ubicar el resultado, vea la captura de pantalla:

4. Luego, haz clic OK, y ahora, todas las combinaciones que equivalen a ese número dado se han mostrado como se muestra a continuación:

Haga clic para descargar Kutools para Excel y prueba gratuita ¡Ahora!


Demostración: Encuentra la combinación de celdas que equivale a una suma dada en Excel


Kutools for Excel: la mejor herramienta de productividad de Office aumenta su productividad en un 80%

  • Super Formula Bar (edite fácilmente varias líneas de texto y fórmula); Diseño de lectura (lee y edita fácilmente un gran número de celdas); Pegar en rango filtrado...
  • Combinar celdas / filas / columnas y mantener datos; Contenido de celdas divididas; Combinar filas duplicadas y suma / promedio... Prevenir células duplicadas; Comparar rangos...
  • Seleccione Duplicado o Único Filas; Seleccionar filas en blanco (todas las celdas están vacías); Super Find y Fuzzy Find en muchos libros de trabajo; Selección aleatoria ...
  • Copia exacta Celdas múltiples sin cambiar la referencia de fórmula; Crear referencias automáticamente a múltiples hojas; Insertar viñetas, Casillas de verificación y más ...
  • Fórmulas favoritas e insertadas rápidamente, Gamas, cuadros y cuadros; Cifrar celdas con contraseña Crear una lista de correo y enviar correos electrónicos ...
  • Extracto del texto, Agregar texto, Eliminar por posición, Eliminar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
  • Súper filtro (guardar y aplicar esquemas de filtro a otras hojas); Clasificación avanzada por mes / semana / día, frecuencia y más; Filtro especial por negrita, cursiva ...
  • Combinar libros de trabajo y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes xls, xlsx y PDF...
  • Funciona con Office 2007-2019 y 365, y es compatible con todos los idiomas. Es fácil de implementar en su empresa. Funciones completas de prueba gratuita de 60-day.
pestaña kte 201905

Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil

  • Habilitar la edición y lectura con pestañas en Word, Excel, PowerPoint, Editor, Acceso, Visio y Proyecto.
  • Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta tu productividad en un 50% y reduce cientos de clics de ratón por ti todos los días!
fondo officetab
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Feroz · 2 months ago
    How do you do for the list of numbers like 480
  • To post as a guest, your comment is unpublished.
    Guilherme Dorn · 4 months ago
    Thank you very much! Resolved my problem correctly.
  • To post as a guest, your comment is unpublished.
    Miss Jones · 5 months ago
    Thank you so much for the VBA coding, it has solved a major headache trying to find combinations to equal an exact amount.
  • To post as a guest, your comment is unpublished.
    Stephanie · 6 months ago
    I have 1162 cells to find number x. Excel tells me that is too many variable cells. Very small data set! Any suggestions? Thanks!
  • To post as a guest, your comment is unpublished.
    a · 9 months ago
    Will the solver add-in not work if there are negative numbers in the list or if the value of number is 0? I'm trying to find a sum of numbers in a list that equate to zero with some numbers being negative and positive, but the solver does not work. I changed a couple numbers on my list to test to make sure I followed the steps correctly and it did work for the test. Please advise if there is a way to solve with negative and positive numbers to find a 0 value.
  • To post as a guest, your comment is unpublished.
    Fattir · 1 years ago
    Hello,
    Thanks this is very good,
    How can find the most approximate combinations if there is no exact value.
    Many thanks
  • To post as a guest, your comment is unpublished.
    Fattir · 1 years ago
    Hello,
    Many thanks for information;
    How can find the most approximate combinations if there is no exact value.
    Many thanks,
  • To post as a guest, your comment is unpublished.
    Igor Wilk · 1 years ago
    Would somebody know how to adjust the VBA Getcombination function so that no repetition should be allowed?

    For example, for numbers 1,2,3,4,5,13 if 14 is to be achieved than 1,13 is a solution, and not 14 of 1.
    • To post as a guest, your comment is unpublished.
      Ram · 1 years ago
      Function GetCombination(CoinsRange As Range, SumCellId As Double) As String
      'updateby Extendoffice 20160506
      Dim xStr As String
      Dim xSum As Double
      Dim xCell As Range
      xSum = SumCellId
      For Each xCell In CoinsRange
      If Not (xSum / xCell < 1) Then
      xStr = xStr & "1 of " & xCell & " "
      xSum = xSum - xCell
      End If
      Next
      GetCombination = xStr
      End Function
      • To post as a guest, your comment is unpublished.
        Shashanth · 6 months ago
        Hi Ram, this works fine but doesnot give the actual sum.
        EX: if i have 23,34,25,28,10,17&12 and i have a sum of 80(which is the sum of 23,28,17&12), I need a vba code which can find this combination (sum of 23,28,17&12) Can you please help me with this ?
      • To post as a guest, your comment is unpublished.
        ddddddd7 · 10 months ago
        hi it is giving me ambigious name error for the vba code
        any help cause i know nothing in VBA
  • To post as a guest, your comment is unpublished.
    alex · 1 years ago
    does anyone know if this works on google sheets
  • To post as a guest, your comment is unpublished.
    epp · 1 years ago
    Hi,

    My drouble with this formula is that it gives me one value for enough times to get the target value..
    In the list of different values there are some values which are equal to each other.

    E.g. I have 0,16 for 3 times(the first values in the list) and the formula gives me the answer that my target value is 593 of 0,16.

    Why does it not combine different values to get my target value? It only chooses one value and gives how many times it is to be the target value.

    Any help or idea?


    Thanks!
  • To post as a guest, your comment is unpublished.
    Dana · 1 years ago
    I am trying to determine the best blend of product and am unsure if this is the best way to do it. At most I use three products in a blend with 5 specifications each. All of the specifications are linear and can be averaged when blended. One blend is usually 45,000lbs and each batch is 30,000lbs. Most of the time our blends are 15k+30k but I would like to be able to calculate for the unusual blends using the increments all the way down to 2000lbs.
  • To post as a guest, your comment is unpublished.
    Lorena · 1 years ago
    The macro didn't work if there are more than one solution.
    Also, I didn't work if I find "0"
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello,Lorena,
      Before applying the above VBA code, you must sort the number list in descending order first.
      Second, the code is not work correctly to get the total number 0.
      Hope it can help you, thank you!
  • To post as a guest, your comment is unpublished.
    laura · 1 years ago
    Could you upload the excel?
  • To post as a guest, your comment is unpublished.
    Ruchir · 1 years ago
    Brilliant!!!
  • To post as a guest, your comment is unpublished.
    LL · 1 years ago
    I was able to get the example with Range1 to work with my range in 12 rows, but when I changed the range to 42 rows it did not work. I even restarted the entire process with the 42 row version and that didn't work either. Any ideas?
  • To post as a guest, your comment is unpublished.
    WL · 2 years ago
    HI, I downloaded Kutools but cannot get it to find all the combos less than a specified total.
  • To post as a guest, your comment is unpublished.
    Dori · 2 years ago
    Hi. The formula version didn't work for me either. It feels like it is missing a step. I do not see where the number specified in cell C2 comes into the formula.

    Thanks
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello, Dori,


      There is no formula in C2, it is just the specific value that you want numbers added up to.
  • To post as a guest, your comment is unpublished.
    tarra · 2 years ago
    how if i need more than one combination? thank you
  • To post as a guest, your comment is unpublished.
    DJ · 2 years ago
    I'm at best a advanced beginner at Excel. I tried everything and it didn't work. What could I be doing wrong?
  • To post as a guest, your comment is unpublished.
    Alan · 2 years ago
    Awesome. Couldn't get the large formula to work but the solver add-in worked perfectly. Saved me so much work.
  • To post as a guest, your comment is unpublished.
    Rick · 2 years ago
    Is there a way to expand the range as Thom says, to say up to 50 numbers, but to also only total six of the numbers out of the range that sum to the specified total? Currently it will provide all combinations that total to the specified total.

    thanks
  • To post as a guest, your comment is unpublished.
    nitin · 2 years ago
    Superb Man!!! Superb Man!!!
  • To post as a guest, your comment is unpublished.
    Thom · 2 years ago
    Is there a way to expand the range so that it includes more than 8 numbers? Also, I'm not sure how this function is working: "=ROW(INDIRECT("1:"&2^ROWS(Range1)))". If I try to expand "Range1" beyond 15 rows, I get an #Ref error. It works great with just the 8 numbers, but what if you wanted to include, say, 50 numbers or even 100.