Odkrywanie wszystkich możliwych kombinacji liczb na liście, które dają określoną sumę, jest wyzwaniem, przed którym może stanąć wielu użytkowników Excela, czy to w celach związanych z budżetowaniem, planowaniem, czy analizą danych.

W tym przykładzie mamy listę liczb, a celem jest określenie, które kombinacje z tej listy sumują się do 480. Dostarczony zrzut ekranu pokazuje, że istnieje pięć możliwych grup kombinacji, które osiągają tę sumę, w tym kombinacje takie jak 300+120 +60, 250+120+60+50 m.in. W tym artykule przyjrzymy się różnym metodom określania konkretnych kombinacji liczb na liście, których suma oznacza wyznaczoną wartość w programie Excel.

Znajdź kombinację liczb równą podanej sumie za pomocą funkcji Solver

Znajdź wszystkie kombinacje liczb równe danej sumie

• Korzystając z funkcji zdefiniowanej przez użytkownika
• Dzięki inteligentnej funkcji – Kutools dla Excela

Uzyskaj wszystkie kombinacje liczb, których suma mieści się w zakresie, za pomocą kodu VBA

Znajdź kombinację komórek równą podanej sumie za pomocą funkcji Solver

Zagłębianie się w program Excel w celu znalezienia kombinacji komórek dających sumę określonej liczby może wydawać się trudne, ale dodatek Solver sprawia, że ​​jest to dziecinnie proste. Przeprowadzimy Cię przez proste kroki umożliwiające skonfigurowanie Solvera i znalezienie właściwej kombinacji komórek, dzięki czemu to, co wydawało się złożone, stanie się proste i wykonalne.

Krok 1: Włącz dodatek Solver

1. Proszę przejść do filet > Opcje, W Opcje programu Excel okno dialogowe, kliknij Dodatki w lewym okienku, a następnie kliknij Go przycisk. Zobacz zrzut ekranu:
   
2. A później Dodatki pojawi się okno dialogowe, sprawdź Dodatek Solver opcję i kliknij OK aby pomyślnie zainstalować ten dodatek.
   

Krok 2: Wprowadź formułę

Po aktywowaniu dodatku Solver należy w komórkę B11 wpisać następującą formułę:

=SUMPRODUCT(B2:B10,A2:A10)

Krok 3: Skonfiguruj i uruchom Solver, aby uzyskać wynik

1. Kliknij Dane > Solver , aby przejść do Parametr dodatku Solver w oknie dialogowym, w oknie dialogowym wykonaj następujące czynności:
   • (1.) Kliknij aby wybrać komórkę B11 skąd znajduje się Twoja formuła Ustaw cel Sekcja;
   • (2.) Następnie w Do sekcja, wybierz Wartośći wprowadź wartość docelową 480 tak jak potrzebujesz;
   • (3.) Pod Zmieniając zmienne komórki kliknij aby wybrać zakres komórek B2: B10 gdzie zaznaczy odpowiednie numery.
   • (4.) Następnie kliknij Dodaj przycisk.
   • 
2. Następnie plik Dodaj wiązanie wyświetli się okno dialogowe, kliknij aby wybrać zakres komórek B2: B10i wybierz kosz z rozwijanej listy. Wreszcie kliknij OK przycisk. Zobacz zrzut ekranu:
   
3. W Parametr dodatku Solver dialog, kliknij przycisk Rozwiązać przycisk, kilka minut później, a Wyniki rozwiązania Solver wyskakuje okno dialogowe i możesz zobaczyć kombinację komórek, które równają się danej sumie 480 i są oznaczone jako 1 w kolumnie B. W Wyniki rozwiązania Solver wybierz Zachowaj rozwiązanie Solver opcję i kliknij OK aby zamknąć okno dialogowe. Zobacz zrzut ekranu:
   

Znajdź wszystkie kombinacje liczb równe danej sumie

Poznawanie głębszych możliwości programu Excel pozwala znaleźć każdą kombinację liczb odpowiadającą określonej sumie i jest to łatwiejsze, niż mogłoby się wydawać. W tej sekcji zostaną przedstawione dwie metody znajdowania wszystkich kombinacji liczb równych danej sumie.

Uzyskaj wszystkie kombinacje liczb równe danej sumie za pomocą funkcji zdefiniowanej przez użytkownika

Aby odkryć każdą możliwą kombinację liczb z określonego zestawu, które łącznie osiągają określoną wartość, skutecznym narzędziem jest opisana poniżej funkcja niestandardowa.

Krok 1: Otwórz edytor modułów VBA i skopiuj kod

1. Przytrzymaj przycisk ALT + F11 klucze w programie Excel i otwiera plik Microsoft Visual Basic for Applications okno.
2. Kliknij wstawka > Modułi wklej następujący kod w oknie modułu.
   Kod VBA: Uzyskaj wszystkie kombinacje liczb równe podanej sumie

   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
   

Krok 2: Wprowadź niestandardową formułę, aby uzyskać wynik

Po wklejeniu kodu zamknij okno kodu, aby wrócić do arkusza. Wprowadź następującą formułę w pustej komórce, aby wyświetlić wynik, a następnie naciśnij Wchodzę klawisz, aby uzyskać wszystkie kombinacje. Zobacz zrzut ekranu:

=MakeupANumber(A2:A10,B2)

Wskazówka: Jeśli chcesz wyświetlić wyniki kombinacji pionowo w kolumnie, zastosuj następującą formułę:

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

Uzyskaj wszystkie kombinacje liczb równe danej sumie za pomocą potężnej funkcji

Biorąc pod uwagę ograniczenia wyżej wymienionej funkcji, zalecamy szybkie i kompleksowe rozwiązanie: Kutools dla programu Excel's Make up a Number funkcja, która jest kompatybilna z dowolną wersją programu Excel. Ta alternatywa może skutecznie obsługiwać liczby dodatnie, dziesiętne i liczby ujemne. Dzięki tej funkcji możesz szybko uzyskać wszystkie kombinacje równe danej sumie.

1. Kliknij Kutools > Treść > Uzupełnij liczbęzobacz zrzut ekranu:
   
2. Następnie w Wymyśl liczbę kliknij , aby wybrać listę numerów, której chcesz użyć Źródło danych, a następnie wprowadź całkowitą liczbę do pliku Suma pole tekstowe. Na koniec kliknij OK przycisk, zobacz zrzut ekranu:
   
3. Następnie pojawi się okno z przypomnieniem o wybraniu komórki w celu zlokalizowania wyniku, a następnie kliknij OKzobacz zrzut ekranu:
   
4. A teraz wszystkie kombinacje równe podanej liczbie zostały wyświetlone, jak pokazano na zrzucie ekranu poniżej:
   

Uzyskaj wszystkie kombinacje liczb, których suma mieści się w zakresie, za pomocą kodu VBA

Czasami możesz znaleźć się w sytuacji, w której musisz zidentyfikować wszystkie możliwe kombinacje liczb, które łącznie dają sumę mieszczącą się w określonym zakresie. Na przykład możesz chcieć znaleźć każdą możliwą grupę liczb, których suma mieści się w przedziale od 470 do 480.

Odkrywanie wszystkich możliwych kombinacji liczb, które sumują się do wartości w określonym zakresie, stanowi fascynujące i wysoce praktyczne wyzwanie w programie Excel. W tej sekcji zostanie przedstawiony kod VBA umożliwiający rozwiązanie tego zadania.

Krok 1: Otwórz edytor modułów VBA i skopiuj kod

1. Przytrzymaj przycisk ALT + F11 klucze w programie Excel i otwiera plik Microsoft Visual Basic for Applications okno.
2. Kliknij wstawka > Modułi wklej następujący kod w oknie modułu.
   Kod VBA: Uzyskaj wszystkie kombinacje liczb, które sumują się do określonego zakresu

   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
   
   
   

Krok 2: Wykonaj kod

1. Po wklejeniu kodu naciśnij F5 Aby uruchomić ten kod, w pierwszym wyskakującym oknie dialogowym wybierz zakres liczb, którego chcesz użyć, i kliknij OK. Zobacz zrzut ekranu:
   
2. W drugim polu zachęty wybierz lub wpisz dolny limit i kliknij OK. Zobacz zrzut ekranu:
   
3. W trzecim polu zachęty wybierz lub wpisz górny limit i kliknij OK. Zobacz zrzut ekranu:
   
4. W ostatnim polu zachęty wybierz komórkę wyjściową, w której zaczną się wyświetlać wyniki. Następnie kliknij OK. Zobacz zrzut ekranu:
   

Wynik

Teraz każda kwalifikująca kombinacja zostanie wyświetlona w kolejnych wierszach arkusza, zaczynając od wybranej komórki wyjściowej.

Excel udostępnia kilka sposobów wyszukiwania grup liczb, których suma daje określoną sumę. Każda metoda działa inaczej, więc możesz wybrać jedną na podstawie znajomości programu Excel i potrzeb w swoim projekcie. Jeśli chcesz poznać więcej porad i wskazówek dotyczących Excela, nasza witryna internetowa oferuje tysiące samouczków kliknij tutaj, aby uzyskać do nich dostęp. Dziękujemy za przeczytanie i mamy nadzieję, że w przyszłości przekażemy Ci więcej przydatnych informacji!

Podobne artykuły: