Mae darganfod pob cyfuniad posibl o rifau o fewn rhestr sy'n adio i swm penodol yn her y gallai llawer o ddefnyddwyr Excel ddod ar ei thraws, boed at ddibenion cyllidebu, cynllunio neu ddadansoddi data.

Yn yr enghraifft hon, mae gennym restr o rifau, a'r amcan yw nodi pa gyfuniadau o'r rhestr hon sy'n dod i gyfanswm o 480. Mae'r sgrinlun a ddarparwyd yn dangos bod pum grŵp posibl o gyfuniadau sy'n cyflawni'r swm hwn, gan gynnwys cyfuniadau fel 300+120 +60, 250+120+60+50, ymhlith eraill. Yn yr erthygl hon, byddwn yn archwilio gwahanol ddulliau i nodi'r cyfuniadau penodol o rifau o fewn rhestr sy'n rhoi cyfanswm o werth dynodedig yn Excel.

Darganfyddwch gyfuniad o rifau sy'n hafal i swm penodol gyda ffwythiant Datryswr

Sicrhewch fod pob cyfuniad o rifau yn hafal i swm penodol

• Trwy ddefnyddio Swyddogaeth Diffiniedig Defnyddiwr
• Gan nodwedd smart - Kutools ar gyfer Excel

Sicrhewch bob cyfuniad o rifau sydd â swm mewn ystod gyda chod VBA

Darganfod cyfuniad celloedd sy'n hafal i swm penodol â ffwythiant Datryswr

Gallai plymio i Excel i ddod o hyd i gyfuniadau celloedd sy'n adio i rif penodol ymddangos yn frawychus, ond mae'r Ychwanegiad Datryswr yn ei gwneud yn awel. Byddwn yn eich tywys trwy'r camau syml i sefydlu Datryswr a dod o hyd i'r cyfuniad cywir o gelloedd, gan wneud yr hyn a oedd yn ymddangos fel tasg gymhleth yn syml ac yn ymarferol.

Cam 1: Galluogi Ychwanegiad Datryswr

1. Os gwelwch yn dda ewch i Ffeil > Dewisiadau, Yn y Dewisiadau Excel blwch deialog, cliciwch Add-Ins o'r cwarel chwith, ar y pryd, cliciwch Go botwm. Gweler y screenshot:
   
2. Yna, y Add-Ins deialog yn ymddangos, gwiriwch y Ychwanegiad Datrysydd opsiwn, a chlicio OK i osod yr ychwanegiad hwn yn llwyddiannus.
   

Cam 2: Rhowch y fformiwla

Ar ôl actifadu'r ategyn Datryswr, mae angen i chi nodi'r fformiwla hon yn y gell B11:

=SUMPRODUCT(B2:B10,A2:A10)

Cam 3: Ffurfweddu a rhedeg Datryswr i gael y canlyniad

1. Cliciwch Dyddiad > Datryswr i fynd i'r Paramedr Datryswr blwch deialog, yn y dialog, gwnewch y gweithrediadau canlynol:
   • (1.) Cliciwch botwm i ddewis y gell B11 o ble mae'ch fformiwla wedi'i lleoli o'r Gosod Amcan adran;
   • (2.) Yna yn y I adran, dewiswch Gwerth Of, a nodwch eich gwerth targed 480 yn ôl yr angen;
   • (3.) O dan y Trwy Newid Celloedd Amrywiol adran, cliciwch botwm i ddewis yr ystod celloedd B2: B10 ble fydd yn marcio'ch rhifau cyfatebol.
   • (4.) Yna, cliciwch Ychwanegu botwm.
   • 
2. Yna, an Ychwanegu Cyfyngiad blwch deialog yn cael ei arddangos, cliciwch botwm i ddewis yr ystod celloedd B2: B10, a dethol bin o'r gwymplen. O'r diwedd, cliciwch OK botwm. Gweler y screenshot:
   
3. Yn y Paramedr Datryswr deialog, cliciwch y Datrys botwm, rai munudau'n ddiweddarach, a Canlyniadau Datryswr blwch deialog wedi'i popio allan, a gallwch weld y cyfuniad o gelloedd sy'n hafal i swm penodol 480 wedi'u marcio fel 1 yng ngholofn B. Yn y Canlyniadau Datryswr deialog, dewiswch Cadwch Datrysiad Datryswr opsiwn, a chlicio OK i adael y dialog. Gweler y screenshot:
   

Sicrhewch fod pob cyfuniad o rifau yn hafal i swm penodol

Mae archwilio galluoedd dyfnach Excel yn gadael i chi ddod o hyd i bob cyfuniad rhif sy'n cyfateb i swm penodol, ac mae'n haws nag y gallech feddwl. Bydd yr adran hon yn dangos dau ddull i chi o ddarganfod pob cyfuniad o rifau sy'n hafal i swm penodol.

Sicrhewch fod pob cyfuniad o rifau yn hafal i swm penodol gyda Swyddogaeth Ddiffiniedig Defnyddiwr

I ddatgelu pob cyfuniad posibl o rifau o set benodol sydd gyda'i gilydd yn cyrraedd gwerth penodol, mae'r swyddogaeth arfer a amlinellir isod yn arf effeithiol.

Cam 1: Agorwch olygydd modiwl VBA a chopïwch y cod

1. Dal i lawr y ALT + F11 allweddi yn Excel, ac mae'n agor y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.
2. Cliciwch Mewnosod > Modiwlau, a gludwch y cod canlynol yn y Ffenestr Modiwl.
   Cod VBA: Sicrhewch fod pob cyfuniad o rifau yn hafal i swm penodol

   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
   

Cam 2: Rhowch y fformiwla arferiad i gael y canlyniad

Ar ôl gludo'r cod, caewch y ffenestr cod i fynd yn ôl i'r daflen waith. Rhowch y fformiwla ganlynol i mewn i gell wag i allbynnu'r canlyniad, ac yna pwyswch Rhowch allweddol i gael pob cyfuniad. Gweler y sgrinlun:

=MakeupANumber(A2:A10,B2)

Tip: Os ydych chi am restru'r canlyniadau cyfuniad yn fertigol mewn colofn, defnyddiwch y fformiwla ganlynol:

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

Sicrhewch fod pob cyfuniad o rifau yn hafal i swm penodol gyda nodwedd bwerus

O ystyried cyfyngiadau'r swyddogaeth a grybwyllwyd uchod, rydym yn argymell ateb cyflym a chynhwysfawr: Kutools ar gyfer Excel's Make up a Number nodwedd , sy'n gydnaws ag unrhyw fersiwn o Excel. Gall y dewis arall hwn drin rhifau positif, degolion a rhifau negatif yn effeithiol. Gyda'r nodwedd hon, gallwch yn gyflym gael pob cyfuniad sy'n hafal i swm penodol.

1. Cliciwch Kutools > Cynnwys > Colur Rhif, gweler y screenshot:
   
2. Yna, yn y Lluniwch rif blwch deialog, cliciwch botwm i ddewis y rhestr rifau rydych chi am eu defnyddio o'r Ffynhonnell Data, ac yna nodwch y cyfanswm yn y Swm blwch testun. Yn olaf, cliciwch OK botwm, gweler y screenshot:
   
3. Ac yna, bydd blwch prydlon yn ymddangos i'ch atgoffa i ddewis cell i ddod o hyd i'r canlyniad, yna cliciwch OK, gweler y screenshot:
   
4. Ac yn awr, mae'r holl gyfuniadau sy'n hafal i'r rhif penodol hwnnw wedi'u harddangos fel y sgrinlun a ddangosir isod:
   

Sicrhewch bob cyfuniad o rifau sydd â swm mewn ystod gyda chod VBA

Weithiau, efallai y byddwch mewn sefyllfa lle mae angen i chi nodi pob cyfuniad posibl o rifau sydd gyda'i gilydd yn adio i swm o fewn ystod benodol. Er enghraifft, efallai eich bod yn ceisio dod o hyd i bob grŵp posibl o rifau lle mae'r cyfanswm yn disgyn rhwng 470 a 480.

Mae darganfod pob cyfuniad posibl o rifau sy'n crynhoi gwerth o fewn ystod benodol yn her hynod ddiddorol a hynod ymarferol yn Excel. Bydd yr adran hon yn cyflwyno cod VBA ar gyfer datrys y dasg hon.

Cam 1: Agorwch olygydd modiwl VBA a chopïwch y cod

1. Dal i lawr y ALT + F11 allweddi yn Excel, ac mae'n agor y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.
2. Cliciwch Mewnosod > Modiwlau, a gludwch y cod canlynol yn y Ffenestr Modiwl.
   Cod VBA: Sicrhewch bob cyfuniad o rifau sy'n crynhoi i ystod benodol

   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
   
   
   

Cam 2: Gweithredu'r cod

1. Ar ôl pasio'r cod, pwyswch F5 allwedd i redeg y cod hwn, yn y dialog popped cyntaf, dewiswch yr ystod o rifau rydych am eu defnyddio, a chliciwch OK. Gweler y screenshot:
   
2. Yn yr ail flwch prydlon, dewiswch neu deipiwch y rhif terfyn isel, a chliciwch OK. Gweler y screenshot:
   
3. Yn y trydydd blwch prydlon, dewiswch neu deipiwch y rhif terfyn uchel, a chliciwch OK. Gweler y screenshot:
   
4. Yn y blwch prydlon olaf, dewiswch gell allbwn, a dyna lle bydd y canlyniadau'n dechrau cael eu hallbynnu. Yna cliciwch OK. Gweler y screenshot:
   

Canlyniad

Nawr, bydd pob cyfuniad cymwys yn cael ei restru mewn rhesi olynol yn y daflen waith, gan ddechrau o'r gell allbwn a ddewisoch.

Mae Excel yn rhoi sawl ffordd i chi ddod o hyd i grwpiau o rifau sy'n adio i gyfanswm penodol, mae pob dull yn gweithio'n wahanol, felly gallwch ddewis un yn seiliedig ar ba mor gyfarwydd ydych chi ag Excel a'r hyn sydd ei angen arnoch ar gyfer eich prosiect. Os oes gennych ddiddordeb mewn archwilio mwy o awgrymiadau a thriciau Excel, mae ein gwefan yn cynnig miloedd o diwtorialau, os gwelwch yn dda cliciwch yma i gael mynediad iddynt. Diolch am ddarllen, ac edrychwn ymlaen at ddarparu mwy o wybodaeth ddefnyddiol i chi yn y dyfodol!

Erthyglau cysylltiedig: