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

Belirli bir listedeki sayıların, bütçeleme, planlama veya veri analizi amacıyla toplamı belirli bir değere ulaşan tüm olası kombinasyonlarını bulmak, birçok Excel kullanıcısının karşılaştığı bir zorluktur.

Bu örnekte, elimizde bir sayı listesi var ve amacımız bu listeden hangi kombinasyonların toplamının 480'e ulaştığını belirlemek. Sağlanan ekran görüntüsünde, 300+120+60, 250+120+60+50 gibi kombinasyonlar da dahil olmak üzere beş olası kombinasyon grubunun bu toplamı sağladığı gösterilmektedir. Bu makalede, Excel'de belirli bir değere ulaşan liste içindeki sayıların spesifik kombinasyonlarını tespit etmek için çeşitli yöntemleri ele alacağız.

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

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

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

VBA kodu ile belirli bir aralıktaki toplamlara sahip tüm sayı kombinasyyonlarını alın

Çözücü fonksiyonu ile belirli bir toplama eşit olan hücre kombinasyonlarını bulun

Excel'de belirli bir sayıya ulaşan hücre kombinasyonlarını bulmak zor görünebilir, ancak Çözücü Eklentisi bu işlemi kolaylaştırır. Size Çözücü'yi ayarlamak ve doğru hücre kombinasyonlarını bulmak için basit adımları göstereceğiz; böylece karmaşık görünen bir görevi basit ve yapılabilir hale getireceğiz.

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

1. Lütfen şu yola gidin: Dosya > Seçenekler, ardından Excel Seçenekleri diyalog kutusunda, tıklayın Eklentiler soldaki bölmeden, ardından Git düğmesine tıklayın. Ekran görüntüsüne bakın:
   
2. Ardından, Eklentiler diyalog kutusu açılır, işaretleyin Çö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 gerekmektedir:

=SUMPRODUCT(B2:B10,A2:A10)

Adım 3: Çözücü'yu yapılandırın ve sonucu alın

1. Tıklayın Veri > Çözücü gidin Çözücü Parametresi diyalog kutusunda, lütfen aşağıdaki işlemleri yapın:
   • (1.) Tıklayın düğmesine tıklayarak seçin B11 formülünüzün bulunduğu hücreyi seçin Hedefi Ayarla bölümünden;
   • (2.) Ardından, To bölümünde Değerini seçin ve hedef değer olarak 480'i girin.
   • (3.) Değişken Hücreleri Değiştirerek bölümünde, lütfen tıklayın düğmesine tıklayarak hücre aralığını seçin B2:B10 bu aralık, karşılık gelen numaralarınızı işaretleyecektir.
   • (4.) Ardından, Ekle düğmesine tıklayın.
   • 
2. Ardından, bir Kısıtlama Ekle diyalog kutusu görüntülenir, tıklayın düğmesine tıklayarak hücre aralığını seçin B2:B10, ve seçin bin açılan listeden. Son olarak, tıklayın Tamam düğmesine basın. Ekran görüntüsüne bakın:
   
3. Şimdi Çözücü Parametresi diyalog kutusunda, tıklayın Çöz düğmesine basın, birkaç dakika sonra bir Çözücü Sonuçları diyalog kutusu açılır ve belirli bir toplam olan 480'e eşit hücre kombinasyonlarının B sütununda 1 olarak işaretlendiğini görebilirsiniz. Ayrıca Çözücü Sonuçları diyalog kutusunda, lütfen seçin Çözücü Çözümünü Koru seçeneğini işaretleyin ve Tamam düğmesine tıklayarak diyalog kutusundan çıkın. 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şfederek, belirli bir toplama uygun her sayı kombinasyonunu bulabilirsiniz ve bunu düşündüğünüzden daha kolaydır. Bu bölümde, belirli bir toplama eşit olan tüm sayı kombinasyonlarını bulmak için iki yöntem sunacağız.

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

Belirli bir kümeden toplamı belirli bir değere ulaşan tüm olası sayı kombinasyonlarını ortaya çıkarmak için, aşağıda açıklanan özel fonksiyon etkili bir araç olarak hizmet eder.

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

1. Excel'de ALT + F11 tuşlarına basılı tutun, bu Microsoft Visual Basic for Applications penceresini açar.
2. Tıklayın Ekle > Modül, ve 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: Özel formülü girin ve sonucu alın

Kodu yapıştırdıktan sonra, kod penceresini kapatıp çalışma sayfasına dönün. Aşağıdaki formülü boş bir hücreye yazarak sonucu çıktılayın ve ardından tüm kombinasyonları almak için Enter tuşuna basın. Ekran görüntüsüne bakın:

=MakeupANumber(A2:A10,B2)

İpucu: Eğer kombinasyon sonuçlarını dikey olarak bir sütunda listelemek isterseniz, 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 bahsedilen fonksiyonun sınırlamaları göz önüne alındığında, hızlı ve kapsamlı bir çözüm öneriyoruz: Kutools for Excel'in Sayıyı Tamamla özelliği, bu alternatif tüm Excel sürümleriyle uyumludur ve pozitif sayılar, ondalık sayılar ve negatif sayılarla etkili bir şekilde başa çıkabilir. Bu özellik ile belirli bir toplama eşit olan tüm kombinasyonları hızlıca alabilirsiniz.

1. Tıklayın Kutools > Metin > Sayıyı Tamamla, ekran görüntüsüne bakın:
   
2. Ardından, Sayıyı Tamamla diyalog kutusunda, lütfen tıklayın düğmesine tıklayarak kullanmak istediğiniz numara listesini seçin Kaynak Veri, ve ardından toplam sayıyı girin Toplam metin kutusuna. Son olarak, tıklayın Tamam düğmesine basın, ekran görüntüsüne bakın:
   
3. Ardından, bir uyarı kutusu açılacak ve size sonucu yerleştirecek bir hücre seçmenizi hatırlatacaktır, ardından tıklayın Tamam, ekran görüntüsüne bakın:
   
4. Ve şimdi, belirli bir sayıya eşit olan tüm kombinasyonlar aşağıdaki ekran görüntüsünde gösterildiği gibi görüntülenecektir:
   

VBA kodu ile belirli bir aralıktaki toplamlara sahip tüm sayı kombinasyonlarını alın

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

Belirli bir aralıktaki değerlere toplamı ulaşan tüm olası sayı kombinasyonlarını bulmak, Excel'de ilgi çekici ve oldukça pratik bir zorluktur. Bu bölümde, bu görevi çözmek için bir VBA kodu tanıtılacaktır.

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

1. Excel'de ALT + F11 tuşlarına basılı tutun, bu Microsoft Visual Basic for Applications penceresini açar.
2. Tıklayın Ekle > Modül, ve aşağıdaki kodu Modül Penceresine yapıştırın.
   VBA Kodu: Belirli bir aralıktaki toplamlara sahip 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, basın F5 tuşuna basarak bu kodu çalıştırın, ilk açılan diyalog kutusunda, kullanmak istediğiniz numara aralığını seçin ve tıklayın Tamam. Ekran görüntüsüne bakın:
   
2. İkinci uyarı kutusunda, alt limit sayısını seçin veya yazın ve tıklayın Tamam. Ekran görüntüsüne bakın:
   
3. Üçüncü uyarı kutusunda, üst limit sayısını seçin veya yazın ve tıklayın Tamam. Ekran görüntüsüne bakın:
   
4. Son uyarı kutusunda, sonuçların çıktısını almak istediğiniz hücreyi seçin. Ardından tıklayın Tamam. Ekran görüntüsüne bakın:
   

Sonuç

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

Excel, belirli bir toplamı oluşturan sayı gruplarını bulmak için size birkaç yol sunar, her yöntem farklı şekilde çalışır, bu nedenle Excel'deki bilginize ve projeniz için neye ihtiyacınız olduğuna bağlı olarak birini seçebilirsiniz. Eğer daha fazla Excel ipucu ve püf noktası keşfetmeye ilgileniyorsanız, web sitemiz binlerce öğretici sunmaktadır. Okuduğunuz için teşekkür ederiz ve gelecekte size daha faydalı bilgiler sunmayı dört gözle bekliyoruz!

İlgili Makaleler: