Come trovare tutte le combinazioni che sono pari a una somma specifica in Excel?

Scoprire tutte le possibili combinazioni di numeri all'interno di un elenco che si sommano fino 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 ci sono cinque possibili gruppi di combinazioni che raggiungono questa somma, incluse combinazioni come 300+120+60, 250+120+60+50, tra le altre. In questo articolo, esploreremo vari metodi per individuare le specifiche combinazioni di numeri all'interno di un elenco che totalizzano un valore designato in Excel.

Trova una combinazione di numeri uguale a una somma data con la funzione Risolutore

Ottieni tutte le combinazioni di numeri uguali a una somma data

• Utilizzando una 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 uguale a una somma data con la funzione Risolutore

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

Passo 1: Abilita il componente aggiuntivo Risolutore

1. Per favore vai su File > Opzioni, nella finestra di dialogo Opzioni di Excel clicca su Componenti aggiuntivi dal riquadro sinistro, quindi clicca sul pulsante Vai . Vedi screenshot:
   
2. Poi, appare la finestra di dialogo Componenti aggiuntivi seleziona l'opzione Risolutore e clicca su OK per installare correttamente questo componente aggiuntivo.
   

Passo 2: Inserisci la formula

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

=SUMPRODUCT(B2:B10,A2:A10)

Passo 3: Configura ed esegui il Risolutore per ottenere il risultato

1. Clicca su Dati > Risolutore per andare alla finestra di dialogo Parametri del Risolutore nella finestra di dialogo, effettua le seguenti operazioni:
   • (1.) Clicca sul pulsante per selezionare la cella B11 dove si trova la tua formula dalla sezione Imposta Obiettivo ;
   • (2.) Poi, nella sezione A, seleziona Valore Di, e inserisci il tuo valore obiettivo 480 come necessario;
   • (3.) Nella sezione Modificando le Celle Variabili clicca sul pulsante per selezionare l'intervallo di celle B2:B10 che contrassegnerà i tuoi numeri corrispondenti.
   • (4.) Quindi, clicca sul pulsante Aggiungi.
   • 
2. Poi, appare una finestra di dialogo Aggiungi Vincolo clicca sul pulsante per selezionare l'intervallo di celle B2:B10, e seleziona bin dal menu a discesa. Infine, clicca sul pulsante OK Vedi screenshot:
   
3. Nella finestra di dialogo Parametri del Risolutore clicca sul pulsante Risolvi alcuni minuti dopo, appare una finestra di dialogo Risultati del Risolutore e puoi vedere che le combinazioni di celle che sono pari a una somma data di 480 sono contrassegnate con 1 nella colonna B. Nella finestra di dialogo Risultati del Risolutore seleziona l'opzione Mantieni Soluzione del Risolutore e clicca sul pulsante OK per uscire dalla finestra di dialogo. Vedi screenshot:
   

Ottieni tutte le combinazioni di numeri uguali a una somma data

Esplorare le capacità più profonde di Excel ti permette di trovare ogni combinazione di numeri che corrisponde a una somma specifica, ed è più facile di quanto potresti pensare. Questa sezione ti mostrerà due metodi per trovare tutte le combinazioni di numeri uguali a una somma data.

Ottieni tutte le combinazioni di numeri uguali a una somma data con una Funzione Definita dall'Utente

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

Passo 1: Apri l'editor del modulo VBA e copia il codice

1. Tieni premuti i tasti ALT + F11 in Excel, e si aprirà la finestra Microsoft Visual Basic for Applications.
2. Clicca su Inserisci > Modulo, e incolla il seguente codice nella finestra Modulo.
   Codice VBA: Ottieni tutte le combinazioni di numeri uguali a una somma data

   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
   

Passo 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. Inserisci la seguente formula in una cella vuota per visualizzare il risultato, e poi premi il tasto Invio per ottenere tutte le combinazioni. Vedi screenshot:

=MakeupANumber(A2:A10,B2)

Suggerimento: Se desideri elencare i risultati delle combinazioni verticalmente in una colonna, applica la seguente formula:

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

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

Date le limitazioni della funzione sopra menzionata, consigliamo una soluzione rapida e completa: la funzionalità Arrotonda Numeri di Kutools per Excel, compatibile con qualsiasi versione di Excel. Questa alternativa può gestire efficacemente numeri positivi, decimali e negativi. Con questa funzionalità, puoi ottenere rapidamente tutte le combinazioni che sono pari a una somma data.

1. Clicca su Kutools > Contenuto > Arrotonda Un Numero, vedi screenshot:
   
2. Poi, nella finestra di dialogo Arrotonda un numero clicca sul pulsante per selezionare la lista di numeri che desideri utilizzare dalla Fonte Dati, e poi inserisci il numero totale nel campo Somma Infine, clicca sul pulsante OK vedi screenshot:
   
3. Successivamente, apparirà una finestra di dialogo per ricordarti di selezionare una cella dove posizionare il risultato, quindi clicca su OK, vedi screenshot:
   
4. E ora, tutte le combinazioni che sono pari a quel numero dato sono state visualizzate come mostrato nello screenshot sottostante:
   

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 collettivamente si sommano fino a una somma entro un intervallo specifico. Ad esempio, potresti cercare di trovare ogni possibile raggruppamento di numeri dove il totale rientra tra 470 e 480.

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

Passo 1: Apri l'editor del modulo VBA e copia il codice

1. Tieni premuti i tasti ALT + F11 in Excel, e si aprirà la finestra Microsoft Visual Basic for Applications.
2. Clicca su Inserisci > Modulo, e incolla il seguente codice nella finestra Modulo.
   Codice VBA: Ottieni tutte le combinazioni di numeri che si sommano fino 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
   
   
   

Passo 2: Esegui il codice

1. Dopo aver incollato il codice, premi il tasto F5 per eseguire questo codice, nella prima finestra di dialogo che appare, seleziona l'intervallo di numeri che vuoi utilizzare, e clicca su OK. Vedi screenshot:
   
2. Nella seconda finestra di dialogo, seleziona o digita il numero limite inferiore, e clicca su OK. Vedi screenshot:
   
3. Nella terza finestra di dialogo, seleziona o digita il numero limite superiore, e clicca su OK. Vedi screenshot:
   
4. Nell'ultima finestra di dialogo, seleziona una cella di output, che sarà il punto in cui i risultati inizieranno ad essere visualizzati. Poi clicca su OK. Vedi screenshot:
   

Risultato

Ora, ogni combinazione qualificata sarà 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 fino a un certo totale, ogni metodo funziona in modo diverso, quindi puoi sceglierne uno in base alla tua familiarità con Excel e alle necessità del tuo progetto. Se sei interessato a esplorare ulteriori suggerimenti e trucchi di Excel, il nostro sito web offre migliaia di tutorial. Grazie per aver letto, e non vediamo l'ora di fornirti ulteriori informazioni utili in futuro!

Articoli Correlati: