Ցանկում թվերի բոլոր հնարավոր համակցությունների հայտնաբերումը, որոնք գումարվում են որոշակի գումարի, մարտահրավեր է, որին կարող են հանդիպել Excel-ի շատ օգտվողներ՝ լինի դա բյուջետավորման, պլանավորման կամ տվյալների վերլուծության նպատակներով:

Այս օրինակում մենք ունենք թվերի ցանկ, և նպատակն է բացահայտել, թե այս ցուցակից որ համակցություններն են գումարում մինչև 480: Տրամադրված սքրինշոթը ցույց է տալիս, որ կան համակցությունների հինգ հնարավոր խմբեր, որոնք հասնում են այս գումարին, ներառյալ 300+120 համակցությունները: +60, 250+120+60+50, ի թիվս այլոց: Այս հոդվածում մենք կուսումնասիրենք տարբեր մեթոդներ՝ հստակեցնելու թվերի հատուկ համակցությունները ցուցակում, որոնք կազմում են Excel-ում նշանակված արժեք:

Լուծող ֆունկցիայով գտե՛ք տրված գումարին հավասար թվերի համակցություն

Ստացեք տրված գումարին հավասար թվերի բոլոր համակցությունները

• Օգտվողի կողմից սահմանված գործառույթն օգտագործելով
• Խելացի հատկանիշով – Kutools Excel-ի համար

Ստացեք VBA կոդով թվերի բոլոր համակցությունները, որոնք ունեն տիրույթում գումար

Գտեք բջիջների համակցություն, որը հավասար է Լուծվող ֆունկցիայի տրված գումարին

Excel-ի մեջ սուզվելը՝ որոշակի թվով բջիջների համակցություններ գտնելու համար, կարող է սարսափելի թվալ, բայց «Solver» հավելումը դա հեշտացնում է: Մենք ձեզ կպարզենք Solver-ը կարգավորելու և բջիջների ճիշտ համակցությունը գտնելու պարզ քայլերը, ինչը բարդ առաջադրանք էր թվում պարզ և իրագործելի:

Քայլ 1. Միացնել Solver Add-in-ը

1. Խնդրում ենք գնալ Ֆայլ > ԸնտրանքներԷ, ապա Excel ընտրանքներ երկխոսության վանդակում, սեղմեք Add-Ins- ը ձախ վահանակից, այնուհետև կտտացրեք Go կոճակ Տեսեք,
   
2. Այնուհետեւ Add-Ins- ը երկխոսություն է հայտնվում, ստուգեք Լուծիչի հավելում տարբերակը և սեղմեք OK այս հավելումը հաջողությամբ տեղադրելու համար:
   

Քայլ 2. Մուտքագրեք բանաձևը

Լուծող հավելումը ակտիվացնելուց հետո դուք պետք է մուտքագրեք այս բանաձևը B11 բջիջում.

=SUMPRODUCT(B2:B10,A2:A10)

Քայլ 3. Արդյունք ստանալու համար կազմաձևեք և գործարկեք Solver-ը

1. Սեղմել Ամսաթիվ > solver գնալու համար Լուծիչի պարամետր երկխոսության տուփ, երկխոսության մեջ, խնդրում ենք կատարել հետևյալ գործողությունները.
   • (1.) Կտտացրեք բջիջը ընտրելու կոճակը B11 որտեղից է գտնվում ձեր բանաձևը Նպատակ դնել Բաժին;
   • (2.) Հետո Դեպի բաժին ընտրեք Արժեքըև մուտքագրեք ձեր նպատակային արժեքը 480 ինչպես պետք է;
   • (3.) Համաձայն Փոփոխական բջիջները փոխելով բաժինը, սեղմեք կոճակ՝ բջիջների տիրույթն ընտրելու համար B2: B10 որտեղ նշվեն ձեր համապատասխան համարները:
   • (4.) Դրանից հետո կտտացրեք Ավելացնել կոճակը:
   • 
2. Հետո, ան Ավելացնել սահմանափակում Երկխոսության տուփը ցուցադրվում է, սեղմեք կոճակ՝ բջիջների տիրույթն ընտրելու համար B2: B10եւ ընտրեք ամբար բացվող ցուցակից: Ի վերջո, սեղմեք OK կոճակ Տեսեք,
   
3. Է Լուծիչի պարամետր երկխոսություն, կտտացրեք Լուծել կոճակը, մի քանի րոպե անց, ա Լուծողի արդյունքներ Երկխոսության տուփը դուրս է գալիս, և դուք կարող եք տեսնել բջիջների համակցությունը, որոնք հավասար են տրված գումարի 480-ին, նշված են որպես 1 սյունակ B-ում: Լուծողի արդյունքներ ընտրեք Պահեք Solver Solution տարբերակը և սեղմեք OK երկխոսությունից դուրս գալու համար: Տեսեք,
   

Ստացեք տրված գումարին հավասար թվերի բոլոր համակցությունները

Excel-ի ավելի խորը հնարավորությունների ուսումնասիրությունը թույլ է տալիս գտնել յուրաքանչյուր թվային համակցություն, որը համապատասխանում է որոշակի գումարին, և դա ավելի հեշտ է, քան դուք կարող եք մտածել: Այս բաժինը ձեզ ցույց կտա երկու եղանակ՝ գտնելու տրված գումարին հավասար թվերի բոլոր համակցությունները:

Ստացեք թվերի բոլոր համակցությունները, որոնք հավասար են տրված գումարին Օգտատիրոջ կողմից սահմանված գործառույթով

Հատուկ հավաքածուից թվերի բոլոր հնարավոր համակցությունները բացահայտելու համար, որոնք կոլեկտիվորեն հասնում են տվյալ արժեքին, ստորև նշված հատուկ գործառույթը ծառայում է որպես արդյունավետ գործիք:

Քայլ 1. Բացեք VBA մոդուլի խմբագրիչը և պատճենեք կոդը

1. Անջատեք ALT + F11 Excel- ի ստեղները, և այն բացում է Microsoft Visual Basic հավելվածների համար պատուհան.
2. Սեղմել Տեղադրել > Մոդուլներև տեղադրեք հետևյալ կոդը Մոդուլի պատուհանում:
   VBA կոդ. Ստացեք տրված գումարին հավասար թվերի բոլոր համակցությունները

   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. Մուտքագրեք հատուկ բանաձևը՝ արդյունքը ստանալու համար

Կոդը տեղադրելուց հետո փակեք ծածկագրի պատուհանը, որպեսզի վերադառնաք աշխատաթերթ: Մուտքագրեք հետևյալ բանաձևը դատարկ բջիջում՝ արդյունքը ստանալու համար, այնուհետև սեղմեք Մտնել բանալին՝ բոլոր համակցությունները ստանալու համար: Տես սքրինշոթը.

=MakeupANumber(A2:A10,B2)

ԱկնարկԵթե ​​ցանկանում եք սյունակում ուղղահայաց թվարկել համակցության արդյունքները, խնդրում ենք կիրառել հետևյալ բանաձևը.

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

Ստացեք հզոր հատկանիշով տրված գումարին հավասար թվերի բոլոր համակցությունները

Հաշվի առնելով վերոհիշյալ գործառույթի սահմանափակումները՝ մենք առաջարկում ենք արագ և համապարփակ լուծում՝ Kutools Excel-ի համար Make up a Number ֆունկցիան, որը համատեղելի է Excel-ի ցանկացած տարբերակի հետ: Այս այլընտրանքը կարող է արդյունավետ կերպով կարգավորել դրական թվերը, տասնորդականները և բացասական թվերը: Այս հատկանիշի միջոցով դուք կարող եք արագ ստանալ բոլոր համակցությունները, որոնք հավասար են տվյալ գումարին:

1. Սեղմել Կուտոլս > Պարունակություն > Կազմիր համար, տես նկարի նկարը.
   
2. Այնուհետև, Կազմիր թիվ երկխոսության տուփ, սեղմեք կոճակը ընտրելու համարների ցուցակը, որը ցանկանում եք օգտագործել Data Source, ապա մուտքագրեք ընդհանուր թիվը մեջ Գումար textbox. Վերջապես, սեղմեք OK կոճակը, տես նկարի նկարը.
   
3. Եվ այնուհետև կհայտնվի հուշման տուփ, որը կհիշեցնի ձեզ ընտրել բջիջ, որպեսզի գտնեք արդյունքը, այնուհետև սեղմեք OK, տես նկարի նկարը.
   
4. Եվ այժմ, բոլոր համակցությունները, որոնք հավասար են տվյալ թվին, ցուցադրվել են ստորև ներկայացված սքրինշոթում.
   

Ստացեք VBA կոդով թվերի բոլոր համակցությունները, որոնք ունեն տիրույթում գումար

Երբեմն, դուք կարող եք հայտնվել մի իրավիճակում, երբ ձեզ անհրաժեշտ է բացահայտել թվերի բոլոր հնարավոր համակցությունները, որոնք միասին գումարվում են որոշակի տիրույթում գտնվող գումարի: Օրինակ, դուք կարող եք փնտրել թվերի բոլոր հնարավոր խմբավորումը, որտեղ ընդհանուր թիվը ընկնում է 470-ից 480-ի միջև:

Թվերի բոլոր հնարավոր համակցությունների հայտնաբերումը, որոնք գումարում են որոշակի արժեք, ներկայացնում է հետաքրքրաշարժ և շատ գործնական մարտահրավեր Excel-ում: Այս բաժնում կներկայացվի VBA կոդ այս խնդիրը լուծելու համար:

Քայլ 1. Բացեք VBA մոդուլի խմբագրիչը և պատճենեք կոդը

1. Անջատեք ALT + F11 Excel- ի ստեղները, և այն բացում է Microsoft Visual Basic հավելվածների համար պատուհան.
2. Սեղմել Տեղադրել > Մոդուլներև տեղադրեք հետևյալ կոդը Մոդուլի պատուհանում:
   VBA կոդ. Ստացեք թվերի բոլոր համակցությունները, որոնք ամփոփում են որոշակի տիրույթ

   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. Կատարեք կոդը

1. Կոդը տեղադրելուց հետո սեղմեք F5 բանալին գործարկելու այս կոդը, առաջին բացված երկխոսության մեջ ընտրեք թվերի շրջանակը, որը ցանկանում եք օգտագործել և սեղմեք OK, Տեսեք,
   
2. Երկրորդ հուշման վանդակում ընտրեք կամ մուտքագրեք ցածր սահմանաչափը և սեղմեք OK, Տեսեք,
   
3. Երրորդ հուշման վանդակում ընտրեք կամ մուտքագրեք բարձր սահմանաչափը և սեղմեք OK, Տեսեք,
   
4. Վերջին հուշման վանդակում ընտրեք ելքային բջիջ, որտեղ արդյունքները կսկսեն դուրս գալ: Այնուհետեւ սեղմեք OK, Տեսեք,
   

Արդյունք

Այժմ յուրաքանչյուր որակավորման համակցություն կցուցադրվի աշխատանքային թերթի հաջորդական տողերում՝ սկսած ձեր ընտրած ելքային բջիջից:

Excel-ը ձեզ մի քանի եղանակ է տալիս՝ գտնելու թվերի խմբեր, որոնք գումարում են որոշակի ընդհանուր գումար, յուրաքանչյուր մեթոդ տարբեր կերպ է աշխատում, այնպես որ կարող եք ընտրել մեկը՝ հիմնվելով այն բանի վրա, թե որքանով եք ծանոթ Excel-ին և ինչն է ձեզ անհրաժեշտ ձեր նախագծի համար: Եթե ​​դուք հետաքրքրված եք Excel-ի ավելի շատ խորհուրդներ և հնարքներ ուսումնասիրելով, մեր կայքը առաջարկում է հազարավոր ձեռնարկներ, խնդրում ենք սեղմեք այստեղ՝ դրանք մուտք գործելու համար. Շնորհակալություն կարդալու համար, և մենք անհամբեր սպասում ենք ձեզ ավելի օգտակար տեղեկություններ տրամադրել ապագայում:

Related Articles: