Descoperirea tuturor combinațiilor posibile de numere dintr-o listă care se adună la o anumită sumă este o provocare pe care o pot întâmpina mulți utilizatori Excel, fie în scopuri de bugetare, planificare sau analiză de date.

În acest exemplu, avem o listă de numere, iar obiectivul este să identificăm ce combinații din această listă însumează până la 480. Captura de ecran oferită demonstrează că există cinci grupuri posibile de combinații care realizează această sumă, inclusiv combinații precum 300+120 +60, 250+120+60+50, printre altele. În acest articol, vom explora diferite metode pentru a identifica combinațiile specifice de numere dintr-o listă care totalizează o valoare desemnată în Excel.

Găsiți o combinație de numere egale cu o sumă dată cu funcția Solver

Obțineți toate combinațiile de numere egale cu o sumă dată

• Prin utilizarea funcției definite de utilizator
• Printr-o funcție inteligentă – Kutools pentru Excel

Obțineți toate combinațiile de numere care au o sumă într-un interval cu cod VBA

Găsiți o combinație de celule care este egală cu o sumă dată cu funcția Solver

Scufundarea în Excel pentru a găsi combinații de celule care se adună la un anumit număr poate părea descurajantă, dar programul de completare Solver îl face ușor. Vă vom ghida prin pașii simpli pentru a configura Solver și a găsi combinația potrivită de celule, făcând ceea ce părea a fi o sarcină complexă simplă și realizabilă.

Pasul 1: Activați programul de completare Solver

1. Vă rugăm să mergeți la Fișier > Opţiuni, În Opțiuni Excel casetă de dialog, faceți clic pe Adăugați ins din panoul din stânga, apoi faceți clic Go buton. Vedeți captura de ecran:
   
2. Apoi, Adăugați ins apare dialogul, verificați Supliment de rezolvare și faceți clic pe OK pentru a instala acest supliment cu succes.
   

Pasul 2: introduceți formula

După activarea programului de completare Solver, trebuie să introduceți această formulă în celula B11:

=SUMPRODUCT(B2:B10,A2:A10)

Pasul 3: Configurați și rulați Solver pentru a obține rezultatul

1. Clic Date > Rezolvator a merge la Parametru Solver casetă de dialog, în dialog, vă rugăm să efectuați următoarele operații:
   • (1.) Faceți clic pe pentru a selecta celula B11 unde se află formula dvs. din Setați obiectivul secțiune;
   • (2.) Apoi în La secțiune, selectați Valoareași introduceți valoarea țintă 480 după cum aveți nevoie;
   • (3.) Sub Prin schimbarea celulelor variabile secțiune, faceți clic pe butonul pentru a selecta intervalul de celule B2: B10 unde va marca numerele corespunzătoare.
   • (4.) Apoi, faceți clic pe Adăuga butonul.
   • 
2. Apoi, un Adăugați Constraint este afișată caseta de dialog, faceți clic butonul pentru a selecta intervalul de celule B2: B10Și selectați bin din lista derulantă. În sfârșit, dați clic OK buton. Vedeți captura de ecran:
   
3. În Parametru Solver , faceți clic pe Rezolva , câteva minute mai târziu, a Rezultate Solver caseta de dialog este afișată și puteți vedea combinația de celule care egalează o sumă dată 480 sunt marcate ca 1 în coloana B. În Rezultate Solver dialog, vă rugăm să selectați Păstrați soluția Solver și faceți clic pe OK pentru a ieși din dialog. Vedeți captura de ecran:
   

Obțineți toate combinațiile de numere egale cu o sumă dată

Explorarea capabilităților mai profunde ale Excel vă permite să găsiți fiecare combinație de numere care se potrivește cu o anumită sumă și este mai ușor decât ați crede. Această secțiune vă va arăta două metode pentru a găsi toate combinațiile de numere egale cu o sumă dată.

Obțineți toate combinațiile de numere egale cu o sumă dată cu funcția definită de utilizator

Pentru a descoperi fiecare combinație posibilă de numere dintr-un anumit set care colectiv atinge o valoare dată, funcția personalizată prezentată mai jos servește ca un instrument eficient.

Pasul 1: Deschideți editorul de module VBA și copiați codul

1. Țineți apăsată tasta ALT + F11 tastele în Excel și deschide fișierul Microsoft Visual Basic pentru aplicații fereastră.
2. Clic Insera > Moduleși lipiți următorul cod în fereastra modulului.
   Cod VBA: obțineți toate combinațiile de numere egale cu o sumă dată

   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
   

Pasul 2: Introduceți formula personalizată pentru a obține rezultatul

După lipirea codului, închideți fereastra de cod pentru a reveni la foaia de lucru. Introduceți următoarea formulă într-o celulă goală pentru a scoate rezultatul, apoi apăsați Intrați cheie pentru a obține toate combinațiile. Vedeți captura de ecran:

=MakeupANumber(A2:A10,B2)

varful: Dacă doriți să enumerați rezultatele combinației pe verticală într-o coloană, vă rugăm să aplicați următoarea formulă:

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

Obțineți toate combinațiile de numere egale cu o sumă dată cu o funcție puternică

Având în vedere limitările funcției menționate mai sus, vă recomandăm o soluție rapidă și cuprinzătoare: Kutools pentru Excel caracteristica Make up a Number, care este compatibilă cu orice versiune de Excel. Această alternativă poate gestiona eficient numerele pozitive, zecimale și numere negative. Cu această caracteristică, puteți obține rapid toate combinațiile egale cu o sumă dată.

1. Clic Kutools > Conţinut > Alcătuiește un număr, vezi captura de ecran:
   
2. Apoi, în Alcătuiește un număr casetă de dialog, faceți clic pe pentru a selecta lista de numere pe care doriți să o utilizați din Sursă de date, apoi introduceți numărul total în Sumă casetă de text. În cele din urmă, faceți clic OK buton, vezi captura de ecran:
   
3. Apoi, va apărea o casetă de solicitare pentru a vă reaminti să selectați o celulă pentru a localiza rezultatul, apoi faceți clic OK, vezi captura de ecran:
   
4. Și acum, toate combinațiile egale cu acel număr dat au fost afișate după cum se arată mai jos:
   

Obțineți toate combinațiile de numere care au o sumă într-un interval cu cod VBA

Uneori, s-ar putea să vă aflați într-o situație în care trebuie să identificați toate combinațiile posibile de numere care însumează colectiv o sumă într-un anumit interval. De exemplu, ați putea căuta să găsiți fiecare grupare posibilă de numere în care totalul se situează între 470 și 480.

Descoperirea tuturor combinațiilor posibile de numere care însumează o valoare dintr-un anumit interval reprezintă o provocare fascinantă și extrem de practică în Excel. Această secțiune va introduce un cod VBA pentru rezolvarea acestei sarcini.

Pasul 1: Deschideți editorul de module VBA și copiați codul

1. Țineți apăsată tasta ALT + F11 tastele în Excel și deschide fișierul Microsoft Visual Basic pentru aplicații fereastră.
2. Clic Insera > Moduleși lipiți următorul cod în fereastra modulului.
   Cod VBA: obțineți toate combinațiile de numere care însumează un anumit interval

   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
   
   
   

Pasul 2: Executați codul

1. După lipirea codului, apăsați F5 pentru a rula acest cod, în primul dialog care apare, selectați intervalul de numere pe care doriți să îl utilizați și faceți clic OK. Vedeți captura de ecran:
   
2. În a doua casetă de solicitare, selectați sau tastați numărul limită inferioară și faceți clic OK. Vedeți captura de ecran:
   
3. În a treia casetă de solicitare, selectați sau tastați numărul limită mare și faceți clic OK. Vedeți captura de ecran:
   
4. În ultima casetă de prompt, selectați o celulă de ieșire, care este locul unde vor începe să fie afișate rezultatele. Apoi apasa OK. Vedeți captura de ecran:
   

Rezultat

Acum, fiecare combinație de calificare va fi listată pe rânduri consecutive în foaia de lucru, pornind de la celula de ieșire pe care ați ales-o.

Excel vă oferă mai multe moduri de a găsi grupuri de numere care însumează un anumit total, fiecare metodă funcționează diferit, astfel încât să puteți alege una în funcție de cât de familiar sunteți cu Excel și de ceea ce aveți nevoie pentru proiectul dvs. Dacă sunteți interesat să explorați mai multe sfaturi și trucuri Excel, site-ul nostru oferă mii de tutoriale, vă rugăm să click aici pentru a le accesa. Vă mulțumim pentru citit și așteptăm cu nerăbdare să vă oferim mai multe informații utile în viitor!

Articole pe aceeaşi temă: