Come trovare tutte le combinazioni di valori che danno una somma specifica in Excel?

Trovare tutte le possibili combinazioni di numeri in un elenco la cui somma corrisponda a un valore specifico è una sfida comune per molti utenti di Excel, sia nel budgeting, nella pianificazione che nell’analisi dei dati.

In questo esempio è presente un elenco di numeri, e l’obiettivo è individuare quali combinazioni di tali numeri diano come somma 480. Lo screenshot mostra cinque possibili gruppi di combinazioni che raggiungono tale totale, tra cui 300+120+60, 250+120+60+50 e altre. In questo articolo esploreremo diversi metodi per trovare in Excel le combinazioni specifiche di numeri all’interno di un elenco la cui somma corrisponda a un valore prestabilito.

Trovare una combinazione di numeri la cui somma corrisponda a un valore specificato con la funzione Risolutore

Ottieni tutte le combinazioni di numeri la cui somma corrisponde a un valore specifico

• Utilizzando una Funzione Definita dall'Utente
• Con una funzione intelligente – Kutools per Excel

Ottenere tutte le combinazioni di numeri la cui somma rientri in un intervallo mediante codice VBA

Trovare la combinazione di celle la cui somma corrisponda a un valore specificato con la funzione Risolutore

Esplorare Excel per individuare combinazioni di celle la cui somma corrisponda a un numero specifico potrebbe sembrare complicato, ma il componente aggiuntivo Risolutore rende l’operazione sorprendentemente semplice. Di seguito sono illustrati i passaggi per configurare il Risolutore e trovare la combinazione esatta di celle, trasformando un compito apparentemente arduo in un’operazione rapida e alla portata di tutti.

Passaggio 1: Abilitare il componente aggiuntivo Risolutore

1. Accedi a File > Opzioni. Nella finestra di Opzioni di Excel, fai clic su Componenti aggiuntivi nel riquadro a sinistra, quindi sul pulsante Vai. Guarda lo screenshot:
   
2. Viene visualizzata la finestra di dialogo Componenti aggiuntivi; seleziona l'opzione Componente aggiuntivo Risolutore e fai clic su OK per installare correttamente il componente aggiuntivo.
   

Passaggio 2: Inserire la formula

Dopo aver attivato il componente aggiuntivo Risolutore, inserire la seguente formula nella cella B11:

=SUMPRODUCT(B2:B10,A2:A10)

Passaggio 3: Configurare ed eseguire il Risolutore per ottenere il risultato

1. Fare clic su Dati>Risolutoreper aprire la finestra di dialogo Parametri Risolutore; in questa finestra eseguire le seguenti operazioni:
   • (1.) Fare clic sul pulsante per selezionare la cella B11in cui si trova la formula dalla sezione Imposta Obiettivo;
   • (2.) Quindi, nella sezione A, selezionare Valoree inserire il valore desiderato 480come necessario;
   • (3.) Nella sezione Modificando le Celle Variabili, fai clic sul pulsante per selezionare l'intervallo di celle B2:B10 in cui verranno contrassegnati i numeri corrispondenti.
   • (4.) Quindi, fai clic sul pulsante Aggiungi.
   • 
2. Viene visualizzata la finestra di dialogo Aggiungi vincolo; fare clic sul pulsante per selezionare l'intervallo di celle B2:B10 e scegliere bin dall'elenco a discesa. Infine, fare clic sul pulsante OK . Vedere lo screenshot:
   
3. Nella finestra di dialogo Parametri Risolutore, fai clic sul pulsante Risolvi. Dopo alcuni minuti, si aprirà la finestra di dialogo Risultati Risolutore, dove potrai vedere che le combinazioni di celle la cui somma corrisponde al valore specificato (480) sono contrassegnate con 1 nella colonna B. Nella finestra di dialogo Risultati Risolutore, seleziona l'opzione Mantieni soluzione del Risolutore e fai clic su OK per chiudere la finestra. Vedere lo screenshot:
   

Ottieni tutte le combinazioni di numeri la cui somma corrisponde a un valore specifico

Esplorare le funzionalità avanzate di Excel ti permette di individuare ogni combinazione di numeri la cui somma corrisponda a un valore specifico, in modo più semplice di quanto immagini. Questa sezione presenta due metodi efficaci per trovare tutte le combinazioni di numeri che danno come risultato una somma prefissata.

Ottenere tutte le combinazioni di numeri la cui somma corrisponda a un valore specificato con una funzione personalizzata

Per individuare tutte le possibili combinazioni di numeri provenienti da un insieme specifico la cui somma raggiunga un determinato valore, la funzione personalizzata descritta di seguito rappresenta uno strumento efficace.

Passaggio 1: Aprire l'editor del modulo VBA e copiare il codice

1. Tieni premuti i tasti ALT + F11 in Excel per aprire la finestra di Microsoft Visual Basic, Applications.
2. Fare clic su Inserisci>Moduloe incollare il codice seguente nella finestra del modulo.
   Codice VBA: Trova tutte le combinazioni di numeri la cui somma è uguale a un valore specificato

   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: Inserire 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, quindi premi il tasto Invio per ottenere tutte le combinazioni. Vedere lo screenshot:

=MakeupANumber(A2:A10,B2)

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

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

Ottenere tutte le combinazioni di numeri la cui somma corrisponda a un valore specificato con una funzionalità avanzata

Date le limitazioni della funzione sopra descritta, ti proponiamo una soluzione rapida ed esaustiva: la funzionalità **Arrotonda numeri** di Kutools per Excel, compatibile con qualsiasi versione di Excel. Questa alternativa gestisce in modo efficace numeri positivi, decimali e negativi, permettendoti di ottenere rapidamente tutte le combinazioni la cui somma corrisponde a un valore specificato.

1. Fai clic su Kutools > Contenuto > Arrotonda numeri. Vedi lo screenshot:
   
2. Nella finestra di dialogo Arrotonda numeri, fare clic sul pulsante per selezionare l'elenco di numeri da utilizzare dalla sezione Intervallo di origine e inserire il valore totale nella casella di testo Somma. Infine, fare clic sul pulsante OK. Vedere lo screenshot:
   
3. Viene visualizzata una finestra di avviso che ti chiede di selezionare una cella in cui inserire il risultato; fai quindi clic su OK. Guarda lo screenshot:
   
4. Ora tutte le combinazioni la cui somma corrisponde al numero specificato sono state visualizzate, come mostrato nello screenshot seguente:
   

Ottenere tutte le combinazioni di numeri la cui somma rientri in un intervallo mediante codice VBA

A volte può capitare di dover identificare tutte le possibili combinazioni di numeri la cui somma complessiva rientri in un intervallo specifico. Ad esempio, si potrebbe voler trovare ogni possibile gruppo di numeri la cui somma totale sia compresa tra 470 e 480.

Individuare tutte le possibili combinazioni di numeri la cui somma rientra in un intervallo specifico è una sfida affascinante e di grande utilità pratica in Excel. Questa sezione presenta un codice VBA pensato appositamente per risolvere questo compito.

Passaggio 1: Aprire l'editor del modulo VBA e copiare il codice

1. Tieni premuti i tasti ALT + F11 in Excel per aprire la finestra di Microsoft Visual Basic, Applications.
2. Fare clic su Inserisci>Moduloe incollare il codice seguente nella finestra del modulo.
   Codice VBA: Trova tutte le combinazioni di numeri la cui somma rientra in 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 il tasto F5 per eseguirlo. Nella prima finestra visualizzata, seleziona l’intervallo di numeri da utilizzare e fai clic su OK. Guarda lo screenshot:
   
2. Nella seconda finestra di dialogo, seleziona o digita il valore minimo e fai clic su OK. Guarda lo screenshot:
   
3. Nella terza finestra di dialogo, seleziona o digita il valore massimo e fai clic su OK. Guarda lo screenshot:
   
4. Nell'ultima finestra di dialogo, seleziona la cella di output, ovvero quella in cui i risultati inizieranno a essere visualizzati, quindi fai clic su OK. Guarda lo screenshot:
   

Risultato

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

Excel ti offre diversi metodi per trovare gruppi di numeri la cui somma corrisponde a un totale specifico; ciascun metodo funziona in modo diverso, così puoi scegliere quello più adatto al tuo livello di familiarità con Excel e alle esigenze del tuo progetto. Se vuoi scoprire altri suggerimenti e trucchi su Excel,il nostro sito web offre migliaia di tutorial. Grazie per aver letto e ci auguriamo di poterti offrire ancora tante informazioni utili in futuro!

Articoli correlati: