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

• Utilizzando la funzione definita dall'utente
• Con una funzionalità intelligente: Kutools per Excel

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)

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:
   

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)

Consiglio: Se desideri elencare i risultati della combinazione verticalmente in una colonna, applica la seguente formula:

=TRANSPOSE(MakeupANumber(A2:A10,B2))

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.

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:
   

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: