Excel’de belirli bir toplama eşit olan tüm kombinasyonları nasıl bulabilirim?

Bir listede belirli bir toplama ulaşan tüm olası sayı kombinasyonlarını bulmak, bütçeleme, planlama veya veri analizi gibi amaçlarla çalışan birçok Excel kullanıcısının karşılaştığı yaygın bir zorluktur.

Bu örnekte bir sayı listemiz bulunmakta ve amacımız bu listeden toplamı 480 eden kombinasyonları belirlemektir. Sağlanan ekran görüntüsü, 300+120+60 ve 250+120+60+50 gibi kombinasyonlar dahil olmak üzere bu toplamı veren beş farklı grubu göstermektedir. Bu makalede, Excel’de bir listedeki sayılardan belirli bir hedef değere ulaşan kombinasyonları nasıl tespit edebileceğimizi inceleyeceğiz.

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

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

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

VBA koduyla belirli bir aralıktaki toplama sahip tüm sayı kombinasyonlarını alın

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

Excel'de belirli bir sayıya ulaşan hücre kombinasyonlarını bulmak zor gibi görünebilir, ancak Çözücü Eklentisi işleri kolaylaştırır. Sizi Çözücü'yü ayarlamak ve hedefinize ulaşacak doğru hücre kombinasyonunu bulmak için basit adımlarla yönlendireceğiz; böylece karmaşık görünen bu görev kolay ve uygulanabilir hale gelir.

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

1. Lütfen Dosya > Seçenekler yolunu izleyin. Excel Seçenekleri iletişim kutusunda, sol bölmeden Eklentiler seçeneğine tıklayın ve ardından Git düğmesine basın. Ekran görüntüsüne bakın:
   
2. Ardından, Eklentiler iletişim kutusu görüntülenir; Çözücü Eklentisi seçeneğini işaretleyin ve Tamam düğmesine tıklayarak bu eklentiyi başarıyla yükleyin.
   

Adım 2: Formülü girin

Çözücü eklentisini etkinleştirdikten sonra, aşağıdaki formülü B11 hücresine girmeniz gerekir:

=SUMPRODUCT(B2:B10,A2:A10)

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

1. Çözücü Parametreleri Veri > Çözücü iletişim kutusuna gitmek için tıklayın. İletişim kutusunda aşağıdaki işlemleri yapın:
   • (1.) Formülünüzün bulunduğu hücreyi düğmesine tıklayarak seçin B11hücresini Hedef Belirlebölümünden;
   • (2.) Ardından Şunabölümünde Belirtilen Değerseçeneğini belirleyin ve gerekli olduğunda hedef değeriniz olarak 480girin;
   • (3.)Değişken Hücreleri Değiştirerek bölümünde, sayılarınızın işaretleneceği hücre aralığını seçmek için lütfen düğmesine tıklayın: B2:B10.
   • (4.) Ardından Ekle düğmesine tıklayın.
   • 
2. Ardından bir Kısıt Ekle iletişim kutusu görüntülenir; hücre aralığını seçmek için B2:B10 düğmesine tıklayın ve açılır listeden bin seçeneğini belirleyin. Son olarak, Tamam düğmesine tıklayın. Ekran görüntüsüne bakın:
   
3. Çözücü Parametreleri iletişim kutusunda Çöz düğmesine tıklayın. Birkaç dakika sonra, verilen toplamı 480 yapan hücre kombinasyonlarının Sütun B’de 1 olarak işaretlendiğini göreceğiniz Çözücü Sonuçları iletişim kutusu açılır. Çözücü Sonuçları iletişim kutusunda lütfen Çözücü Çözümünü Koru seçeneğini seçin ve iletişim kutusundan çıkmak için Tamam düğmesine tıklayın. Ekran görüntüsüne bakın:
   

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

Excel’in daha derin yeteneklerini keşfetmek, belirli bir toplama eşit olan tüm sayı kombinasyonlarını bulmanızı sağlar—ve bu, düşündüğünüzden çok daha kolaydır. Bu bölümde, hedef toplamı veren tüm sayı kombinasyonlarını bulmak için iki etkili yöntemi sizinle paylaşacağız.

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

Belirli bir kümeden verilen bir değere ulaşan tüm olası sayı kombinasyonlarını bulmak için aşağıda açıklanan özel işlev, etkili bir çözümdür.

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

1. Excel'de ALT + F11 tuşlarına basılı tutun; bu işlem, Microsoft Visual Basic for Applications penceresini açar.
2. Tıklayın 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 dönmek için kod penceresini kapatın. Tüm kombinasyonları almak ve sonucu görüntülemek için boş bir hücreye aşağıdaki formülü girin, ardından Enter tuşuna basın. Ekran görüntüsüne bakın:

=MakeupANumber(A2:A10,B2)

İpucu: 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ı alın

Yukarıda belirtilen işlevin sınırlamaları dikkate alındığında, her Excel sürümüyle uyumlu olan Kutools for Excel’in **Sayıları Tamamla** özelliğini öneriyoruz. Bu alternatif çözüm, pozitif sayılar, ondalık sayılar ve negatif sayılarla etkili şekilde çalışır ve sayesinde belirli bir toplama eşit olan tüm kombinasyonları hızla elde edebilirsiniz.

1. Tıklayın Kutools>İçerik>Sayıları Tamamla, ekran görüntüsüne bakın:
   
2. Ardından, Sayıları Tamamla iletişim kutusunda, kullanmak istediğiniz sayı listesini seçmek için düğmesine tıklayın, Kaynak Aralığı’nı belirleyin ve ardından toplam sayıyı Toplam metin kutusuna girin. Son olarak, Tamam düğmesine tıklayın. Ekran görüntüsüne bakın:
   
3. Ardından sonuçların yerleştirileceği bir hücreyi seçmenizi hatırlatan bir uyarı kutusu açılacaktır; ardından Tamamdüğmesine tıklayın, ekran görüntüsüne bakın:
   
4. Şimdi, belirtilen sayıya eşit olan tüm kombinasyonlar aşağıdaki ekran görüntüsünde gösterildiği gibi görüntülenmiştir:
   

VBA koduyla belirli bir aralıktaki toplama sahip tüm sayı kombinasyonlarını alın

Bazen, belirli bir aralıktaki toplama ulaşan tüm olası sayı kombinasyonlarını belirlemeniz gerekebilir. Örneğin, toplamı 470 ile 480 arasında olan her türlü sayı grubunu bulmaya çalışabilirsiniz.

Belirli bir aralıkta hedeflenen değere ulaşan tüm olası sayı kombinasyonlarını bulmak, Excel’de hem ilgi çekici hem de son derece pratik bir zorluktur. Bu bölümde, bu görevi gerçekleştirmek için bir VBA kodu tanıtılacaktır.

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

1. Excel'de ALT + F11tuşlarına basılı tutun; bu işlem Microsoft Visual Basic for Applicationspenceresini açar.
2. Tıklayın Ekle>Modülve aşağıdaki kodu Modül Penceresine yapıştırın.
   VBA kodu: Belirli bir aralığa ulaşan 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 çalıştırın

1. Kodu yapıştırdıktan sonra, bu kodu çalıştırmak için F5 tuşuna basın. İlk açılan iletişim kutusunda kullanmak istediğiniz sayı aralığını seçin ve Tamam düğmesine tıklayın. Ekran görüntüsüne bakın:
   
2. İkinci uyarı kutusunda alt sınır sayısını seçin veya yazın ve Tamam düğmesine tıklayın. Ekran görüntüsüne bakın:
   
3. Üçüncü uyarı kutusunda üst sınır sayısını seçin veya girin, ardından Tamam düğmesine tıklayın. Ekran görüntüsüne bakın:
   
4. Son uyarı kutusunda sonuçların görüntüleneceği çıkış hücresini seçin, ardından Tamam düğmesine tıklayın. 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ırlar halinde listelenecektir.

Excel, belirli bir toplamı veren sayı gruplarını bulmak için size birkaç farklı yöntem sunar; her yöntemin çalışma biçimi farklı olduğundan, Excel ile ne kadar aşina olduğunuz ve projenizde neler gerektiğinize göre en uygun olanı seçebilirsiniz. Daha fazla Excel ipucu ve püf noktası keşfetmek istiyorsanız, web sitemizde binlerce eğitim videosu mevcuttur. Okuduğunuz için teşekkür ederiz ve gelecekte size daha fazla faydalı içerik sunmayı dört gözle bekliyoruz!

İlgili Makaleler: