Belirli bir toplamı oluşturan bir listedeki tüm olası sayı kombinasyonlarını keşfetmek, bütçeleme, planlama veya veri analizi amacıyla birçok Excel kullanıcısının karşılaşabileceği bir zorluktur.

Bu örnekte, bir sayılar listemiz var ve amaç, bu listedeki hangi kombinasyonların toplamının 480'e ulaştığını belirlemektir. Sağlanan ekran görüntüsü, 300+120 gibi kombinasyonlar da dahil olmak üzere, bu toplamı elde eden beş olası kombinasyon grubunun olduğunu göstermektedir. +60, 250+120+60+50 ve diğerleri. Bu makalede, Excel'de belirlenmiş bir değeri toplayan bir listedeki belirli sayı kombinasyonlarını belirlemek için çeşitli yöntemleri inceleyeceğiz.

Çözücü işleviyle belirli bir toplama eşit sayıların birleşimini bulun

Belirli bir toplama eşit olan tüm sayı kombinasyonlarını alın

• Kullanıcı Tanımlı İşlevi kullanarak
• Akıllı bir özellik ile – Kutools for Excel

VBA koduyla bir aralıkta toplamı olan tüm sayı kombinasyonlarını alın

Çözücü işleviyle belirli bir toplama eşit olan hücre kombinasyonunu bulun

Belirli bir sayıya karşılık gelen hücre birleşimlerini bulmak için Excel'e dalmak göz korkutucu görünebilir, ancak Çözücü Eklentisi bunu kolaylaştırır. Solver'ı kurmanız ve doğru hücre kombinasyonunu bulmanız için size basit adımlarda yol göstereceğiz, böylece karmaşık gibi görünen bir görevi basit ve yapılabilir hale getireceğiz.

1. Adım: Çözücü Eklentisini Etkinleştirin

1. Lütfen şu adrese git fileto > Opsiyonlar, In Excel Seçenekleri iletişim kutusunu tıklayın Eklentiler sol bölmeden, ardından Go buton. Ekran görüntüsüne bakın:
   
2. Ardından, Eklentiler iletişim kutusu görüntülenir, kontrol edin Çözücü Eklentisi seçeneğini işaretleyin ve tıklayın. OK bu eklentiyi başarıyla yüklemek için.
   

2. Adım: Formülü girin

Çözücü eklentisini etkinleştirdikten sonra bu formülü B11 hücresine girmeniz gerekir:

=SUMPRODUCT(B2:B10,A2:A10)

3. Adım: Sonucu almak için Çözücüyü yapılandırın ve çalıştırın

1. Tıkla Veri > Çözücü gitmek için Çözücü Parametresi iletişim kutusunda, iletişim kutusunda lütfen aşağıdaki işlemleri yapın:
   • (1.) Tıklayın hücreyi seçmek için düğme B11 formülünüzün bulunduğu yer Hedef Belirleyin Bölüm;
   • (2.) Sonra için bölümünde, seçin Değerive hedef değerinizi girin 480 gerek duyduğunuz kadar;
   • (3.) Altında Değişken Hücreleri Değiştirerek bölümü, lütfen tıklayın hücre aralığını seçmek için düğme B2: B10 Karşılık gelen numaralarınızı nerede işaretleyeceksiniz.
   • (4.) Ardından, Ekle düğmesine basın.
   • 
2. Sonra bir Kısıtlama Ekle iletişim kutusu görüntülenir, tıklayın hücre aralığını seçmek için düğme B2: B10Seçin ve çöp kutusu açılır listeden. Sonunda tıklayın OK buton. Ekran görüntüsüne bakın:
   
3. içinde Çözücü Parametresi iletişim kutusunda Çözmek düğmesi, birkaç dakika sonra bir Çözücü Sonuçları iletişim kutusu açılır ve belirli bir toplam 480'e eşit olan hücre kombinasyonunun B sütununda 1 olarak işaretlendiğini görebilirsiniz. Çözücü Sonuçları iletişim kutusu, lütfen seçin Çözücü Çözümü Tutun seçeneğini işaretleyin ve tıklayın. OK iletişim kutusundan çıkmak için. Ekran görüntüsüne bakın:
   

Belirli bir toplama eşit olan tüm sayı kombinasyonlarını alın

Excel'in daha derin yeteneklerini keşfetmek, belirli bir toplamla eşleşen her sayı kombinasyonunu bulmanızı sağlar ve bu, düşündüğünüzden daha kolaydır. Bu bölüm size belirli bir toplama eşit olan tüm sayı kombinasyonlarını bulmanın iki yöntemini gösterecektir.

Kullanıcı Tanımlı İşlev ile belirli bir toplama eşit olan tüm sayı kombinasyonlarını alın

Belirli bir kümedeki toplu olarak belirli bir değere ulaşan tüm olası sayı kombinasyonlarını ortaya çıkarmak için aşağıda özetlenen özel işlev etkili bir araç görevi görür.

Adım 1: VBA modül düzenleyicisini açın ve kodu kopyalayın

1. basılı tutun ALT + F11 Excel'de anahtarlar ve açılır Uygulamalar için Microsoft Visual Basic pencere.
2. Tıkla Ekle > modülve aşağıdaki kodu Modül Penceresine yapıştırın.
   VBA kodu: Belirli bir toplama eşit olan tüm sayı kombinasyonlarını alın

   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
   

Adım 2: Sonucu almak için özel formülü girin

Kodu yapıştırdıktan sonra çalışma sayfasına geri dönmek için kod penceresini kapatın. Sonucun çıktısını almak için aşağıdaki formülü boş bir hücreye girin ve ardından tuşuna basın. Keşfet Tüm kombinasyonları elde etmek için tuşuna basın. Ekran görüntüsüne bakın:

=MakeupANumber(A2:A10,B2)

Bahşiş: Kombinasyon sonuçlarını dikey olarak bir sütunda listelemek istiyorsanız lütfen aşağıdaki formülü uygulayın:

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

Güçlü bir özellik ile belirli bir toplama eşit olan tüm sayı kombinasyonlarını elde edin

Yukarıda belirtilen işlevin sınırlamaları göz önüne alındığında, hızlı ve kapsamlı bir çözüm öneriyoruz: Kutools for Excel'in Make a Number özelliği, Excel'in herhangi bir sürümüyle uyumludur. Bu alternatif, pozitif sayıları, ondalık sayıları ve negatif sayıları etkili bir şekilde işleyebilir. Bu özellik sayesinde, belirli bir toplama eşit olan tüm kombinasyonları hızlı bir şekilde elde edebilirsiniz.

1. Tıkla Kutools > içerik > Bir Numara Oluşturun, ekran görüntüsüne bakın:
   
2. Sonra, içinde Bir numara uydur iletişim kutusu, lütfen tıklayın kullanmak istediğiniz numara listesini seçmek için Veri kaynağıve ardından toplam sayıyı Toplam Metin kutusu. Son olarak, tıklayın OK düğmesi, ekran görüntüsüne bakın:
   
3. Ardından, sonucu bulmak için bir hücre seçmenizi hatırlatan bir bilgi istemi kutusu açılır ve ardından OK, ekran görüntüsüne bakın:
   
4. Ve şimdi, verilen sayıya eşit olan tüm kombinasyonlar aşağıdaki ekran görüntüsünde gösterildiği gibi görüntülendi:
   

VBA koduyla bir aralıkta toplamı olan tüm sayı kombinasyonlarını alın

Bazen kendinizi, toplu olarak belirli bir aralıktaki toplamı oluşturan tüm olası sayı kombinasyonlarını tanımlamanız gereken bir durumda bulabilirsiniz. Örneğin, toplamın 470 ile 480 arasında olduğu tüm olası sayı gruplarını bulmaya çalışıyor olabilirsiniz.

Belirli bir aralıktaki bir değeri toplayan tüm olası sayı kombinasyonlarını keşfetmek, Excel'de büyüleyici ve son derece pratik bir mücadeleyi temsil eder. Bu bölümde bu görevi çözmek için bir VBA kodu tanıtılacaktır.

Adım 1: VBA modül düzenleyicisini açın ve kodu kopyalayın

1. basılı tutun ALT + F11 Excel'de anahtarlar ve açılır Uygulamalar için Microsoft Visual Basic pencere.
2. Tıkla Ekle > modülve aşağıdaki kodu Modül Penceresine yapıştırın.
   VBA kodu: Belirli bir aralığa karşılık gelen tüm sayı kombinasyonlarını alın

   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
   
   
   

Adım 2: Kodu yürütün

1. Kodu yapıştırdıktan sonra F5 Bu kodu çalıştırmak için tuşuna basın, açılan ilk iletişim kutusunda kullanmak istediğiniz sayı aralığını seçin ve OK. Ekran görüntüsüne bakın:
   
2. İkinci bilgi istemi kutusunda alt limit numarasını seçin veya yazın ve tıklayın. OK. Ekran görüntüsüne bakın:
   
3. Üçüncü bilgi istemi kutusunda üst limit numarasını seçin veya yazın ve tıklayın. OK. Ekran görüntüsüne bakın:
   
4. Son bilgi kutusunda, sonuçların çıktısının alınacağı yer olan bir çıktı hücresi seçin. Sonra tıklayın OK. Ekran görüntüsüne bakın:
   

Sonuç

Artık her uygun kombinasyon, seçtiğiniz çıktı hücresinden başlayarak çalışma sayfasında ardışık satırlarda listelenecektir.

Excel, toplamı belirli bir toplamı oluşturan sayı gruplarını bulmanız için size birkaç yol sunar; her yöntem farklı çalışır; böylece Excel'e ne kadar aşina olduğunuza ve projeniz için neye ihtiyacınız olduğuna bağlı olarak birini seçebilirsiniz. Daha fazla Excel ipucu ve püf noktası keşfetmek istiyorsanız web sitemiz binlerce eğitim sunmaktadır, lütfen bunlara erişmek için burayı tıklayın. Okuduğunuz için teşekkür ederiz ve gelecekte size daha yararlı bilgiler sunmayı sabırsızlıkla bekliyoruz!

İlgili Makaleler: