Preskoči na glavno vsebino

Kako najti vse kombinacije, ki ustrezajo dani vsoti v Excelu?

Odkrivanje vseh možnih kombinacij števil na seznamu, ki dajejo določeno vsoto, je izziv, s katerim se lahko srečajo številni uporabniki Excela, bodisi za namene proračuna, načrtovanja ali analize podatkov.

V tem primeru imamo seznam števil, cilj pa je ugotoviti, katere kombinacije s tega seznama dajejo vsoto 480. Predloženi posnetek zaslona prikazuje, da obstaja pet možnih skupin kombinacij, ki dosežejo to vsoto, vključno s kombinacijami, kot je 300+120 +60, 250+120+60+50, med drugim. V tem članku bomo raziskali različne metode za natančno določitev specifičnih kombinacij števil na seznamu, ki seštevajo določeno vrednost v Excelu.

S funkcijo Reševalec poiščite kombinacijo števil, ki je enaka dani vsoti

Dobite vse kombinacije števil, ki so enake dani vsoti

Pridobite vse kombinacije števil, ki imajo vsoto v obsegu s kodo VBA


Poišči kombinacijo celic, ki je enaka dani vsoti s funkcijo Reševalec

Potapljanje v Excel za iskanje kombinacij celic, ki dajejo določeno število, se morda zdi zastrašujoče, vendar je z dodatkom Solver to preprosto. Vodili vas bomo skozi preproste korake za nastavitev Reševalca in iskanje prave kombinacije celic, tako da bo tisto, kar se je zdelo zapleteno opravilo, preprosto in izvedljivo.

1. korak: Omogočite dodatek Solver

  1. Prosimo, pojdite na file > možnostiV Možnosti programa Excel pogovorno okno, kliknite Dodatki v levem podoknu, nato kliknite Go . Oglejte si posnetek zaslona:
  2. Potem, Dodatki se prikaže pogovorno okno, preverite Dodatek Solver in kliknite OK za uspešno namestitev tega dodatka.

2. korak: Vnesite formulo

Ko aktivirate dodatek Reševalec, morate v celico B11 vnesti to formulo:

=SUMPRODUCT(B2:B10,A2:A10)
Opombe: V tej formuli: B2: B10 je stolpec s praznimi celicami poleg vašega seznama številk in A2: A10 je seznam številk, ki ga uporabljate.

3. korak: Konfigurirajte in zaženite Solver, da dobite rezultat

  1. klik datum > Reševalec Pojdite na Parameter reševalca v pogovornem oknu v pogovornem oknu izvedite naslednje postopke:
    • (1.) Kliknite , da izberete celico B11 kje se nahaja vaša formula od Postavite Cilj odsek;
    • (2.) Nato v Da izberite, izberite Vrednostin vnesite ciljno vrednost 480 kot potrebujete;
    • (3.) Pod S spreminjanjem spremenljivih celic , kliknite gumb za izbiro obsega celic B2: B10 kjer bodo označene vaše ustrezne številke.
    • (4.) Nato kliknite Dodaj gumb.
  2. Nato an Dodaj omejitev se prikaže pogovorno okno, kliknite gumb za izbiro obsega celic B2: B10in izberite bin s spustnega seznama. Končno kliknite OK . Oglejte si posnetek zaslona:
  3. v Parameter reševalca kliknite pogovorno okno Rešite , nekaj minut kasneje, a Rezultati reševalcev pogovorno okno se odpre in lahko vidite kombinacijo celic, ki so enake dani vsoti 480, označene kot 1 v stolpcu B. Rezultati reševalcev pogovorno okno, izberite Naj bo rešitev Solver in kliknite OK za izhod iz pogovornega okna. Oglejte si posnetek zaslona:
Opombe: Vendar pa ima ta metoda omejitev: identificira lahko samo eno kombinacijo celic, ki seštejejo v navedeno vsoto, tudi če obstaja več veljavnih kombinacij.

Dobite vse kombinacije števil, ki so enake dani vsoti

Raziskovanje Excelovih globljih zmogljivosti vam omogoča, da poiščete vsako številsko kombinacijo, ki se ujema z določeno vsoto, in to je lažje, kot si morda mislite. Ta razdelek vam bo pokazal dve metodi za iskanje vseh kombinacij števil, ki so enake dani vsoti.

Z uporabniško določeno funkcijo pridobite vse kombinacije števil, ki so enake dani vsoti

Za odkrivanje vseh možnih kombinacij števil iz določenega niza, ki skupaj dosežejo dano vrednost, je funkcija po meri, opisana spodaj, učinkovito orodje.

1. korak: Odprite urejevalnik modulov VBA in kopirajte kodo

  1. Drži dol ALT + F11 tipke v Excelu in odpre Microsoft Visual Basic za aplikacije okno.
  2. klik Vstavi > Moduliin prilepite naslednjo kodo v okno modula.
    Koda VBA: Pridobite vse kombinacije števil, ki so enake dani vsoti
    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
    

2. korak: Vnesite formulo po meri, da dobite rezultat

Ko prilepite kodo, zaprite okno kode, da se vrnete na delovni list. Vnesite naslednjo formulo v prazno celico, da izpišete rezultat, in nato pritisnite Vnesite ključ za pridobitev vseh kombinacij. Oglejte si posnetek zaslona:

=MakeupANumber(A2:A10,B2)
Opombe: V tej formuli: A2: A10 je seznam številk in B2 je skupni znesek, ki ga želite dobiti.

Nasvet: Če želite rezultate kombinacij prikazati navpično v stolpcu, uporabite naslednjo formulo:
=TRANSPOSE(MakeupANumber(A2:A10,B2))
Omejitve te metode:
  • Ta funkcija po meri deluje samo v Excelu 365 in 2021.
  • Ta metoda je učinkovita izključno za pozitivna števila; decimalne vrednosti se samodejno zaokrožijo na najbližje celo število, negativna števila pa povzročijo napake.

Z zmogljivo funkcijo pridobite vse kombinacije števil, ki so enake dani vsoti

Glede na omejitve zgoraj omenjene funkcije priporočamo hitro in celovito rešitev: Kutools for Excel's Make up a Number funkcija, ki je združljiva s katero koli različico Excela. Ta alternativa lahko učinkovito obravnava pozitivna števila, decimalke in negativna števila. S to funkcijo lahko hitro dobite vse kombinacije, ki so enake dani vsoti.

nasveti: Če želite uporabiti to Sestavite številko funkcijo, najprej morate prenesti Kutools za Excelin nato funkcijo hitro in enostavno uporabite.
  1. klik Kutools > vsebina > Sestavite številko, glej posnetek zaslona:
  2. Nato v Sestavite številko pogovorno okno, kliknite s seznama številk, ki ga želite uporabiti, izberite Vir podatkovin nato vnesite skupno število v Seštevek besedilno polje. Na koncu kliknite OK gumb, glej posnetek zaslona:
  3. Nato se prikaže okno s pozivom, ki vas opomni, da izberete celico za iskanje rezultata, nato pa kliknete OK, glej posnetek zaslona:
  4. In zdaj so bile prikazane vse kombinacije, ki so enake danemu številu, kot je prikazano na spodnjem posnetku zaslona:
Opombe: Če želite uporabiti to funkcijo, prosim prenesite in namestite Kutools za Excel najprej.

Pridobite vse kombinacije števil, ki imajo vsoto v obsegu s kodo VBA

Včasih se lahko znajdete v situaciji, ko morate identificirati vse možne kombinacije števil, ki skupaj tvorijo vsoto znotraj določenega obsega. Na primer, morda želite najti vsako možno skupino števil, kjer je skupni znesek med 470 in 480.

Odkrivanje vseh možnih kombinacij števil, ki dajejo vrednost v določenem obsegu, predstavlja fascinanten in zelo praktičen izziv v Excelu. Ta razdelek bo predstavil kodo VBA za rešitev te naloge.

1. korak: Odprite urejevalnik modulov VBA in kopirajte kodo

  1. Drži dol ALT + F11 tipke v Excelu in odpre Microsoft Visual Basic za aplikacije okno.
  2. klik Vstavi > Moduliin prilepite naslednjo kodo v okno modula.
    Koda VBA: pridobite vse kombinacije števil, ki ustrezajo določenemu obsegu
    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
    
    
    

2. korak: Izvedite kodo

  1. Po lepljenju kode pritisnite F5 za zagon te kode, v prvem pojavnem pogovornem oknu izberite obseg števil, ki jih želite uporabiti, in kliknite OK. Oglejte si posnetek zaslona:
  2. V drugem pozivnem polju izberite ali vnesite nizko mejno številko in kliknite OK. Oglejte si posnetek zaslona:
  3. V tretjem pozivnem polju izberite ali vnesite visoko mejno številko in kliknite OK. Oglejte si posnetek zaslona:
  4. V zadnjem pozivnem polju izberite izhodno celico, kjer se bodo začeli izpisovati rezultati. Nato kliknite OK. Oglejte si posnetek zaslona:

Rezultat

Zdaj bo vsaka kvalificirana kombinacija navedena v zaporednih vrsticah na delovnem listu, začenši z izhodno celico, ki ste jo izbrali.

Excel vam ponuja več načinov za iskanje skupin števil, ki dajejo določeno vsoto, vsaka metoda deluje drugače, tako da lahko izberete eno glede na to, kako dobro poznate Excel in kaj potrebujete za svoj projekt. Če vas zanima več nasvetov in trikov za Excel, naše spletno mesto ponuja na tisoče vadnic kliknite tukaj za dostop do njih. Hvala za branje in veselimo se, da vam bomo v prihodnje zagotovili več koristnih informacij!


Sorodni članki:

  • Navedite ali ustvarite vse možne kombinacije
  • Recimo, da imam naslednja dva stolpca podatkov, zdaj pa želim ustvariti seznam vseh možnih kombinacij na podlagi obeh seznamov vrednosti, kot je prikazano na levi sliki zaslona. Mogoče lahko naštejete vse kombinacije eno za drugo, če je vrednosti malo, če pa je za naštevanje možnih kombinacij več stolpcev z več vrednostmi, je tukaj nekaj hitrih trikov, ki vam lahko pomagajo pri reševanju te težave v Excelu. .
  • Navedite vse možne kombinacije iz enega stolpca
  • Če želite vrniti vse možne kombinacije iz podatkov enega stolpca, da dobite rezultat, kot je prikazan na spodnjem posnetku zaslona, ​​ali imate kakšne hitre načine za reševanje te naloge v Excelu?
  • Ustvarite vse kombinacije 3 ali več stolpcev
  • Recimo, da imam 3 stolpce podatkov, zdaj želim ustvariti ali navesti vse kombinacije podatkov v teh 3 stolpcih, kot je prikazano spodaj. Ali imate kakšne dobre metode za reševanje te naloge v Excelu?
  • Ustvarite seznam vseh možnih 4-mestnih kombinacij
  • V nekaterih primerih bomo morda morali ustvariti seznam vseh možnih 4-mestnih kombinacij števil od 0 do 9, kar pomeni, da bomo ustvarili seznam 0000, 0001, 0002… 9999. Za hitro reševanje naloge s seznamom v Excelu vam predstavljam nekaj trikov.