Η ανακάλυψη όλων των πιθανών συνδυασμών αριθμών σε μια λίστα που αθροίζονται σε ένα συγκεκριμένο άθροισμα είναι μια πρόκληση που μπορεί να αντιμετωπίσουν πολλοί χρήστες του Excel, είτε για σκοπούς προϋπολογισμού, προγραμματισμού ή ανάλυσης δεδομένων.

Σε αυτό το παράδειγμα, έχουμε μια λίστα αριθμών και ο στόχος είναι να προσδιορίσουμε ποιοι συνδυασμοί από αυτήν τη λίστα αθροίζονται σε 480. Το στιγμιότυπο οθόνης που παρέχεται δείχνει ότι υπάρχουν πέντε πιθανές ομάδες συνδυασμών που επιτυγχάνουν αυτό το άθροισμα, συμπεριλαμβανομένων συνδυασμών όπως 300+120 +60, 250+120+60+50, μεταξύ άλλων. Σε αυτό το άρθρο, θα εξερευνήσουμε διάφορες μεθόδους για να εντοπίσουμε τους συγκεκριμένους συνδυασμούς αριθμών σε μια λίστα που αθροίζουν μια καθορισμένη τιμή στο Excel.

Βρείτε έναν συνδυασμό αριθμών ίσου με ένα δεδομένο άθροισμα με τη συνάρτηση Επίλυσης

Πάρτε όλους τους συνδυασμούς αριθμών ίσοι με ένα δεδομένο άθροισμα

• Χρησιμοποιώντας τη λειτουργία καθορισμένη από το χρήστη
• Με ένα έξυπνο χαρακτηριστικό – Kutools για Excel

Λάβετε όλους τους συνδυασμούς αριθμών που έχουν άθροισμα σε ένα εύρος με κωδικό VBA

Βρείτε συνδυασμό κελιών που ισούται με δεδομένο άθροισμα με συνάρτηση Επίλυσης

Η κατάδυση στο Excel για να βρείτε συνδυασμούς κελιών που αθροίζονται σε έναν συγκεκριμένο αριθμό μπορεί να φαίνεται τρομακτική, αλλά το πρόσθετο επίλυσης το κάνει παιχνιδάκι. Θα σας καθοδηγήσουμε στα απλά βήματα για να ρυθμίσετε το Solver και να βρείτε τον σωστό συνδυασμό κελιών, κάνοντας αυτό που φαινόταν σαν περίπλοκο έργο απλό και εφικτό.

Βήμα 1: Ενεργοποιήστε το πρόσθετο επίλυσης

1. Παρακαλώ πηγαίνετε στο Αρχεία > Επιλογές, Στην Επιλογές του Excel , κάντε κλικ στην επιλογή Add-Ins,en από το αριστερό παράθυρο, στη συνέχεια, κάντε κλικ Go κουμπί. Δείτε το στιγμιότυπο οθόνης:
   
2. Μετά το Add-Ins,en εμφανίζεται το παράθυρο διαλόγου, ελέγξτε το Πρόσθετο επίλυσης επιλογή και πατήστε OK για να εγκαταστήσετε αυτό το πρόσθετο με επιτυχία.
   

Βήμα 2: Εισαγάγετε τον τύπο

Αφού ενεργοποιήσετε το πρόσθετο Solver, πρέπει να εισαγάγετε αυτόν τον τύπο στο κελί B11:

=SUMPRODUCT(B2:B10,A2:A10)

Βήμα 3: Διαμορφώστε και εκτελέστε το Solver για να λάβετε το αποτέλεσμα

1. Πατήστε ημερομηνία > Διαλύτης για να μεταβείτε στο Παράμετρος επίλυσης πλαίσιο διαλόγου, στο παράθυρο διαλόγου, κάντε τις ακόλουθες λειτουργίες:
   • (1.) Κάντε κλικ κουμπί για να επιλέξετε το κελί B11 όπου βρίσκεται ο τύπος σας από το Ορισμός στόχου Ενότητα;
   • (2.) Στη συνέχεια, στο Προς την , επιλέξτε Αξία τουκαι εισαγάγετε την τιμή-στόχο σας 480 όπως χρειάζεστε?
   • (3.) Σύμφωνα με το Αλλάζοντας μεταβλητά κελιά ενότητα, κάντε κλικ κουμπί για να επιλέξετε την περιοχή κελιών Β2: Β10 πού θα σημειώσει τους αντίστοιχους αριθμούς σας.
   • (4.) Στη συνέχεια, κάντε κλικ στο Πρόσθεση κουμπί.
   • 
2. Τότε, ένα Προσθήκη περιορισμού εμφανίζεται το παράθυρο διαλόγου, κάντε κλικ κουμπί για να επιλέξετε την περιοχή κελιών Β2: Β10, και επιλέξτε bin από την αναπτυσσόμενη λίστα. Επιτέλους, κάντε κλικ OK κουμπί. Δείτε το στιγμιότυπο οθόνης:
   
3. Στο Παράμετρος επίλυσης , κάντε κλικ στο Λύστε κουμπί, μερικά λεπτά αργότερα, α Αποτελέσματα επίλυσης εμφανίζεται το παράθυρο διαλόγου και μπορείτε να δείτε τον συνδυασμό κελιών που ισούται με ένα δεδομένο άθροισμα 480 σημειώνονται ως 1 στη στήλη Β. Αποτελέσματα επίλυσης διαλόγου, επιλέξτε Διατηρήστε το 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 Δημιουργήστε έναν αριθμό, η οποία είναι συμβατή με οποιαδήποτε έκδοση του Excel. Αυτή η εναλλακτική μπορεί να χειριστεί αποτελεσματικά θετικούς αριθμούς, δεκαδικούς και αρνητικούς αριθμούς. Με αυτήν τη δυνατότητα, μπορείτε να λάβετε γρήγορα όλους τους συνδυασμούς που ισούνται με ένα δεδομένο άθροισμα.

1. Πατήστε Kutools > Περιεχόμενο > Αποτελέστε έναν αριθμό, δείτε το στιγμιότυπο οθόνης:
   
2. Στη συνέχεια, στο Συνθέστε έναν αριθμό πλαίσιο διαλόγου, κάντε κλικ για να επιλέξετε τη λίστα αριθμών που θέλετε να χρησιμοποιήσετε από το Πηγή δεδομένωνκαι, στη συνέχεια, εισαγάγετε τον συνολικό αριθμό στο Άθροισμα πλαίσιο κειμένου. Τέλος, κάντε κλικ 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, ο ιστότοπός μας προσφέρει χιλιάδες εκπαιδευτικά προγράμματα, παρακαλούμε κάντε κλικ εδώ για πρόσβαση σε αυτά. Σας ευχαριστούμε που διαβάσατε και ανυπομονούμε να σας παρέχουμε περισσότερες χρήσιμες πληροφορίες στο μέλλον!

Σχετικά Άρθρα: