Vai al contenuto principale

Come trovare tutte le combinazioni che equivalgono a una data somma in Excel?

Scoprire tutte le possibili combinazioni di numeri all'interno di un elenco che si sommano a una somma specifica è una sfida che molti utenti di Excel potrebbero incontrare, sia per scopi di budget, pianificazione o analisi dei dati.

In questo esempio, abbiamo un elenco di numeri e l'obiettivo è identificare quali combinazioni da questo elenco danno come somma 480. Lo screenshot fornito dimostra che esistono cinque possibili gruppi di combinazioni che raggiungono questa somma, comprese combinazioni come 300+120 +60, 250+120+60+50, tra gli altri. In questo articolo esploreremo vari metodi per individuare le combinazioni specifiche di numeri all'interno di un elenco che totalizzano un valore designato in Excel.

Trova una combinazione di numeri pari ad una determinata somma con la funzione Risolutore

Ottieni tutte le combinazioni di numeri uguali ad una determinata somma

Ottieni tutte le combinazioni di numeri che hanno una somma in un intervallo con il codice VBA


Trova la combinazione di celle che equivalgono a una determinata somma con la funzione Risolutore

Immergersi in Excel per trovare combinazioni di celle che si sommano a un numero specifico potrebbe sembrare scoraggiante, ma il componente aggiuntivo Risolutore lo rende un gioco da ragazzi. Ti guideremo attraverso i semplici passaggi per impostare il Risolutore e trovare la giusta combinazione di celle, rendendo semplice e fattibile quello che sembrava un compito complesso.

Passaggio 1: abilitare il componente aggiuntivo del risolutore

  1. Si prega di andare a Compila il > Opzioni, Nella Opzioni di Excel finestra di dialogo, fare clic Componenti aggiuntivi dal riquadro di sinistra, quindi fare clic su Go pulsante. Vedi screenshot:
  2. Quindi, il Componenti aggiuntivi viene visualizzata la finestra di dialogo, controllare il Componente aggiuntivo Risolutore opzione e fare clic OK per installare correttamente questo componente aggiuntivo.

Passaggio 2: inserisci la formula

Dopo aver attivato il componente aggiuntivo Risolutore, è necessario inserire questa formula nella cella B11:

=SUMPRODUCT(B2:B10,A2:A10)
Note:: In questa formula: B2: B10 è una colonna di celle vuote accanto all'elenco dei numeri e A2: A10 è l'elenco dei numeri che usi.

Passaggio 3: configurare ed eseguire Risolutore per ottenere il risultato

  1. Clicchi Dati > Risolutore per andare al Parametro del risolutore finestra di dialogo, nella finestra di dialogo, eseguire le seguenti operazioni:
    • (1.) Fare clic su pulsante per selezionare la cella B11 da dove si trova la tua formula da Imposta obiettivo sezione;
    • (2.) Quindi in A sezione, selezionare Valore die inserisci il valore target 480 di cui hai bisogno;
    • (3.) Sotto il Modificando le celle variabili sezione, fare clic pulsante per selezionare l'intervallo di celle B2: B10 dove segnerà i numeri corrispondenti.
    • (4.) Quindi fare clic su Aggiungi pulsante.
  2. Quindi, un file Aggiungi vincolo viene visualizzata la finestra di dialogo, fare clic su pulsante per selezionare l'intervallo di celle B2: B10E selezionare bidone dall'elenco a discesa. Alla fine, fai clic OK pulsante. Vedi screenshot:
  3. Nel Parametro del risolutore finestra di dialogo, fare clic su Risolvere pulsante, alcuni minuti dopo, a Risultati del risolutore viene visualizzata la finestra di dialogo e puoi vedere la combinazione di celle che equivalgono a una determinata somma 480 contrassegnate come 1 nella colonna B. Nella Risultati del risolutore finestra di dialogo, selezionare Mantieni la soluzione del risolutore opzione e fare clic OK per uscire dalla finestra di dialogo. Vedi screenshot:
Note:: Questo metodo, tuttavia, presenta una limitazione: può identificare solo una combinazione di celle che sommate danno la somma specificata, anche se esistono più combinazioni valide.

Ottieni tutte le combinazioni di numeri uguali ad una determinata somma

Esplorare le funzionalità più approfondite di Excel ti consente di trovare ogni combinazione di numeri che corrisponde a una somma specifica ed è più semplice di quanto potresti pensare. Questa sezione ti mostrerà due metodi per trovare tutte le combinazioni di numeri uguali a una determinata somma.

Ottieni tutte le combinazioni di numeri uguali a una determinata somma con la funzione definita dall'utente

Per scoprire ogni possibile combinazione di numeri da un insieme specifico che raggiunge collettivamente un determinato valore, la funzione personalizzata descritta di seguito funge da strumento efficace.

Passaggio 1: apri l'editor del modulo VBA e copia il codice

  1. Tenere premuto il tasto ALT + F11 chiavi in ​​Excel e apre il file Microsoft Visual Basic, Applications Edition finestra.
  2. Clicchi inserire > Modulie incolla il codice seguente nella finestra del modulo.
    Codice VBA: ottieni tutte le combinazioni di numeri uguali a una determinata somma
    Public Function MakeupANumber(xNumbers As Range, xCount As Long)
    'updateby Extendoffice
        Dim arrNumbers() As Long
        Dim arrRes() As String
        Dim ArrTemp() As Long
        Dim xIndex As Long
        Dim rg As Range
    
        MakeupANumber = ""
        
        If xNumbers.CountLarge = 0 Then Exit Function
        ReDim arrNumbers(xNumbers.CountLarge - 1)
        
        xIndex = 0
        For Each rg In xNumbers
            If IsNumeric(rg.Value) Then
                arrNumbers(xIndex) = CLng(rg.Value)
                xIndex = xIndex + 1
            End If
        Next rg
        If xIndex = 0 Then Exit Function
        
        ReDim Preserve arrNumbers(0 To xIndex - 1)
        ReDim arrRes(0)
        
        Call Combinations(arrNumbers, xCount, ArrTemp(), arrRes())
        ReDim Preserve arrRes(0 To UBound(arrRes) - 1)
        MakeupANumber = arrRes
    End Function
    
    Private Sub Combinations(Numbers() As Long, Count As Long, ArrTemp() As Long, ByRef arrRes() As String)
    
        Dim currentSum As Long, i As Long, j As Long, k As Long, num As Long, indRes As Long
        Dim remainingNumbers() As Long, newCombination() As Long
        
        currentSum = 0
        If (Not Not ArrTemp) <> 0 Then
            For i = LBound(ArrTemp) To UBound(ArrTemp)
                currentSum = currentSum + ArrTemp(i)
            Next i
        End If
     
        If currentSum = Count Then
            indRes = UBound(arrRes)
            ReDim Preserve arrRes(0 To indRes + 1)
            
            arrRes(indRes) = ArrTemp(0)
            For i = LBound(ArrTemp) + 1 To UBound(ArrTemp)
                arrRes(indRes) = arrRes(indRes) & "," & ArrTemp(i)
            Next i
        End If
        
        If currentSum > Count Then Exit Sub
        If (Not Not Numbers) = 0 Then Exit Sub
        
        For i = 0 To UBound(Numbers)
            Erase remainingNumbers()
            num = Numbers(i)
            For j = i + 1 To UBound(Numbers)
                If (Not Not remainingNumbers) <> 0 Then
                    ReDim Preserve remainingNumbers(0 To UBound(remainingNumbers) + 1)
                Else
                    ReDim Preserve remainingNumbers(0 To 0)
                End If
                remainingNumbers(UBound(remainingNumbers)) = Numbers(j)
                
            Next j
            Erase newCombination()
    
            If (Not Not ArrTemp) <> 0 Then
                For k = 0 To UBound(ArrTemp)
                    If (Not Not newCombination) <> 0 Then
                        ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
                    Else
                        ReDim Preserve newCombination(0 To 0)
                    End If
                    newCombination(UBound(newCombination)) = ArrTemp(k)
    
                Next k
            End If
            
            If (Not Not newCombination) <> 0 Then
                ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
            Else
                ReDim Preserve newCombination(0 To 0)
            End If
            
            newCombination(UBound(newCombination)) = num
    
            Combinations remainingNumbers, Count, newCombination, arrRes
        Next i
    
    End Sub
    

Passaggio 2: inserisci la formula personalizzata per ottenere il risultato

Dopo aver incollato il codice, chiudi la finestra del codice per tornare al foglio di lavoro. Immettere la formula seguente in una cella vuota per visualizzare il risultato, quindi premere entrare chiave per ottenere tutte le combinazioni. Vedi schermata:

=MakeupANumber(A2:A10,B2)
Note:: In questa formula: A2: A10 è l'elenco dei numeri e B2 è la somma totale che vuoi ottenere.

Consiglio: Se desideri elencare i risultati della combinazione verticalmente in una colonna, applica la seguente formula:
=TRANSPOSE(MakeupANumber(A2:A10,B2))
I limiti di questo metodo:
  • Questa funzione personalizzata funziona solo in Excel 365 e 2021.
  • Questo metodo è efficace esclusivamente per i numeri positivi; i valori decimali vengono automaticamente arrotondati al numero intero più vicino e i numeri negativi genereranno errori.

Ottieni tutte le combinazioni di numeri uguali a una determinata somma con una potente funzionalità

Date le limitazioni della funzione di cui sopra, raccomandiamo una soluzione rapida e completa: Kutools per la funzione Crea un numero di Excel, compatibile con qualsiasi versione di Excel. Questa alternativa può gestire efficacemente numeri positivi, decimali e numeri negativi. Con questa funzione puoi ottenere rapidamente tutte le combinazioni uguali a una determinata somma.

Suggerimenti:: Per applicare questo Componi un numero funzionalità, in primo luogo, dovresti scaricare Kutools for Excele quindi applica la funzione in modo rapido e semplice.
  1. Clicchi Kutools > Contenuti > Componi un numero, vedi screenshot:
  2. Quindi, nel Crea un numero finestra di dialogo, fare clic per selezionare l'elenco dei numeri che si desidera utilizzare dal Fonte di dati, quindi inserisci il numero totale nel file Somma casella di testo. Infine, fare clic OK pulsante, vedi screenshot:
  3. Quindi, verrà visualizzata una finestra di messaggio per ricordarti di selezionare una cella per individuare il risultato, quindi fare clic OK, vedi screenshot:
  4. E ora, tutte le combinazioni uguali a quel dato numero sono state visualizzate come mostrato nell'immagine seguente:
Note:: Per applicare questa funzione, per favore scarica e installa Kutools per Excel prima.

Ottieni tutte le combinazioni di numeri che hanno una somma in un intervallo con il codice VBA

A volte, potresti trovarti in una situazione in cui devi identificare tutte le possibili combinazioni di numeri che insieme danno una somma entro un intervallo specifico. Ad esempio, potresti cercare di trovare ogni possibile raggruppamento di numeri in cui il totale sia compreso tra 470 e 480.

Scoprire tutte le possibili combinazioni di numeri che si sommano a un valore compreso in un intervallo specifico rappresenta una sfida affascinante e altamente pratica in Excel. Questa sezione introdurrà un codice VBA per risolvere questo compito.

Passaggio 1: apri l'editor del modulo VBA e copia il codice

  1. Tenere premuto il tasto ALT + F11 chiavi in ​​Excel e apre il file Microsoft Visual Basic, Applications Edition finestra.
  2. Clicchi inserire > Modulie incolla il codice seguente nella finestra del modulo.
    Codice VBA: ottieni tutte le combinazioni di numeri che si sommano a un intervallo specifico
    Sub Getall_combinations()
    'Updateby Extendoffice
        Dim xNumbers As Variant
        Dim Output As Collection
        Dim rngSelection As Range
        Dim OutputCell As Range
        Dim LowLimit As Long, HiLimit As Long
        Dim i As Long, j As Long
        Dim TotalCombinations As Long
        Dim CombTotal As Double
        Set Output = New Collection
        On Error Resume Next
        Set rngSelection = Application.InputBox("Select the range of numbers:", "Kutools for Excel", Type:=8)
        If rngSelection Is Nothing Then
            MsgBox "No range selected. Exiting macro.", vbInformation, "Kutools for Excel"
            Exit Sub
        End If
        On Error GoTo 0
        xNumbers = rngSelection.Value
        LowLimit = Application.InputBox("Select or enter the low limit number:", "Kutools for Excel", Type:=1)
        HiLimit = Application.InputBox("Select or enter the high limit number:", "Kutools for Excel", Type:=1)
        On Error Resume Next
        Set OutputCell = Application.InputBox("Select the first cell for output:", "Kutools for Excel", Type:=8)
        If OutputCell Is Nothing Then
            MsgBox "No output cell selected. Exiting macro.", vbInformation, "Kutools for Excel"
            Exit Sub
        End If
        On Error GoTo 0
        TotalCombinations = 2 ^ (UBound(xNumbers, 1) * UBound(xNumbers, 2))
        For i = 1 To TotalCombinations - 1
            Dim tempArr() As Double
            ReDim tempArr(1 To UBound(xNumbers, 1) * UBound(xNumbers, 2))
            CombTotal = 0
            Dim k As Long: k = 0
            
            For j = 1 To UBound(xNumbers, 1)
                If i And (2 ^ (j - 1)) Then
                    k = k + 1
                    tempArr(k) = xNumbers(j, 1)
                    CombTotal = CombTotal + xNumbers(j, 1)
                End If
            Next j
            If CombTotal >= LowLimit And CombTotal <= HiLimit Then
                ReDim Preserve tempArr(1 To k)
                Output.Add tempArr
            End If
        Next i
        Dim rowOffset As Long
        rowOffset = 0
        Dim item As Variant
        For Each item In Output
            For j = 1 To UBound(item)
                OutputCell.Offset(rowOffset, j - 1).Value = item(j)
            Next j
            rowOffset = rowOffset + 1
        Next item
    End Sub
    
    
    

Passaggio 2: eseguire il codice

  1. Dopo aver incollato il codice, premere F5 Per eseguire questo codice, nella prima finestra di dialogo visualizzata, seleziona l'intervallo di numeri che desideri utilizzare e fai clic OK. Vedi screenshot:
  2. Nella seconda casella di messaggio, selezionare o digitare il numero del limite inferiore e fare clic OK. Vedi screenshot:
  3. Nella terza casella di messaggio, selezionare o digitare il numero del limite massimo e fare clic OK. Vedi screenshot:
  4. Nell'ultima finestra di messaggio, seleziona una cella di output, dove inizieranno a essere visualizzati i risultati. Quindi fare clic OK. Vedi screenshot:

Risultato

Ora, ciascuna combinazione qualificante verrà elencata in righe consecutive nel foglio di lavoro, a partire dalla cella di output che hai scelto.

Excel ti offre diversi modi per trovare gruppi di numeri che si sommano a un determinato totale, ogni metodo funziona in modo diverso, quindi puoi sceglierne uno in base alla tua familiarità con Excel e a ciò di cui hai bisogno per il tuo progetto. Se sei interessato a esplorare ulteriori suggerimenti e trucchi su Excel, il nostro sito Web offre migliaia di tutorial, per favore clicca qui per accedervi. Grazie per aver letto e non vediamo l'ora di fornirti informazioni più utili in futuro!


Articoli Correlati:

  • Elenca o genera tutte le possibili combinazioni
  • Diciamo che ho le seguenti due colonne di dati e ora voglio generare un elenco di tutte le possibili combinazioni in base ai due elenchi di valori come mostrato nell'immagine a sinistra. Forse, puoi elencare tutte le combinazioni una per una se ci sono pochi valori, ma, se ci sono diverse colonne con più valori necessari per elencare le possibili combinazioni, ecco alcuni trucchi rapidi che possono aiutarti ad affrontare questo problema in Excel .
  • Genera tutte le combinazioni di 3 o più colonne
  • Supponendo, ho 3 colonne di dati, ora, voglio generare o elencare tutte le combinazioni dei dati in queste 3 colonne come mostrato nell'immagine sottostante. Hai dei buoni metodi per risolvere questa attività in Excel?
  • Genera un elenco di tutte le possibili combinazioni di 4 cifre
  • In alcuni casi, potrebbe essere necessario generare un elenco di tutte le possibili combinazioni di 4 cifre del numero da 0 a 9, il che significa generare un elenco di 0000, 0001, 0002… 9999. Per risolvere rapidamente l'attività dell'elenco in Excel, introduco alcuni trucchi per te.