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
- 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:
- 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
- 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.
- 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:
- 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
- Tenere premuto il tasto ALT + F11 chiavi in Excel e apre il file Microsoft Visual Basic, Applications Edition finestra.
- Clicchi inserire > Modulie incolla il codice seguente nella finestra del modulo.
Codice VBA: ottieni tutte le combinazioni di numeri uguali a una determinata sommaPublic 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)
=TRANSPOSE(MakeupANumber(A2:A10,B2))
- 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.
- Clicchi Kutools > Contenuti > Componi un numero, vedi screenshot:
- 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:
- Quindi, verrà visualizzata una finestra di messaggio per ricordarti di selezionare una cella per individuare il risultato, quindi fare clic OK, vedi screenshot:
- 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
- Tenere premuto il tasto ALT + F11 chiavi in Excel e apre il file Microsoft Visual Basic, Applications Edition finestra.
- 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 specificoSub 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
- 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:
- Nella seconda casella di messaggio, selezionare o digitare il numero del limite inferiore e fare clic OK. Vedi screenshot:
- Nella terza casella di messaggio, selezionare o digitare il numero del limite massimo e fare clic OK. Vedi screenshot:
- 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 .
- Elenca tutte le possibili combinazioni da una singola colonna
- Se desideri restituire tutte le possibili combinazioni dai dati di una singola colonna per ottenere il risultato come mostrato nell'immagine sottostante, hai dei modi rapidi per gestire questa attività 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.
I migliori strumenti per la produttività in ufficio
Potenzia le tue competenze di Excel con Kutools per Excel e sperimenta l'efficienza come mai prima d'ora. Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e risparmiare tempo. Fai clic qui per ottenere la funzionalità di cui hai più bisogno...
Office Tab porta l'interfaccia a schede in Office e semplifica notevolmente il tuo lavoro
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
- Aumenta la produttività del 50% e riduce ogni giorno centinaia di clic del mouse!
Sommario
- Trova una combinazione di numeri pari ad una data somma
- Ottieni tutte le combinazioni di numeri uguali ad una determinata somma
- Con funzione definita dall'utente
- Con Kutools per Excel
- Ottieni tutte le combinazioni di numeri che hanno una somma in un intervallo
- Articoli Correlati
- I migliori strumenti per la produttività in ufficio
- Commenti