Menemukan semua kemungkinan kombinasi angka dalam daftar yang jumlahnya mencapai jumlah tertentu merupakan tantangan yang mungkin dihadapi banyak pengguna Excel, baik untuk tujuan penganggaran, perencanaan, atau analisis data.

Dalam contoh ini, kita memiliki daftar angka, dan tujuannya adalah untuk mengidentifikasi kombinasi mana dari daftar ini yang jumlahnya mencapai 480. Tangkapan layar yang diberikan menunjukkan bahwa ada lima kemungkinan kelompok kombinasi yang mencapai jumlah ini, termasuk kombinasi seperti 300+120 +60, 250+120+60+50, antara lain. Pada artikel ini, kita akan mengeksplorasi berbagai metode untuk menentukan dengan tepat kombinasi angka tertentu dalam daftar yang menjumlahkan nilai yang ditentukan di Excel.

Temukan kombinasi angka yang sama dengan jumlah tertentu dengan fungsi Solver

Dapatkan semua kombinasi angka sama dengan jumlah tertentu

• Dengan menggunakan Fungsi Buatan Pengguna
• Dengan fitur pintar – Kutools for Excel

Dapatkan semua kombinasi angka yang jumlahnya dalam suatu rentang dengan kode VBA

Temukan kombinasi sel yang sama dengan jumlah tertentu dengan fungsi Solver

Menyelami Excel untuk menemukan kombinasi sel yang berjumlah angka tertentu mungkin tampak sulit, namun Solver Add-in membuatnya mudah. Kami akan memandu Anda melalui langkah-langkah sederhana untuk menyiapkan Solver dan menemukan kombinasi sel yang tepat, menjadikan tugas yang tampak rumit menjadi mudah dan dapat dilakukan.

Langkah 1: Aktifkan Add-in Solver

1. Silakan pergi ke File > Opsi, Dalam Opsi Excel kotak dialog, klik Add-Ins dari panel kiri, lalu klik Go tombol. Lihat tangkapan layar:
   
2. Lalu, itu Add-Ins dialog muncul, periksa Add-in Solver opsi, dan klik OK untuk memasang add-in ini dengan sukses.
   

Langkah 2: Masukkan rumusnya

Setelah mengaktifkan add-in Solver, Anda perlu memasukkan rumus ini ke dalam sel B11:

=SUMPRODUCT(B2:B10,A2:A10)

Langkah 3: Konfigurasikan dan jalankan Solver untuk mendapatkan hasilnya

1. Klik Data > Solver untuk pergi ke Parameter Solver kotak dialog, di dialog, lakukan operasi berikut:
   • (1.) Klik tombol untuk memilih sel B11 dari mana rumus Anda berada Tetapkan Tujuan bagian;
   • (2.) Kemudian di Untuk bagian, pilih Nilai dari, dan masukkan nilai target Anda 480 seperti yang Anda butuhkan;
   • (3.) Di bawah Dengan Mengubah Sel Variabel bagian, silakan klik tombol untuk memilih rentang sel B2: B10 di mana akan menandai nomor Anda yang sesuai.
   • (4.) Kemudian, klik Add .
   • 
2. Kemudian, sebuah Tambahkan Batasan kotak dialog ditampilkan, klik tombol untuk memilih rentang sel B2: B10, Lalu pilih bin dari daftar drop-down. Terakhir, klik OK tombol. Lihat tangkapan layar:
   
3. Dalam majalah Parameter Solver dialog, klik Memecahkan tombol, beberapa menit kemudian, a Hasil Solver kotak dialog muncul, dan Anda dapat melihat kombinasi sel yang sama dengan jumlah tertentu 480 ditandai sebagai 1 di kolom B. Di kolom Hasil Solver dialog, pilih Pertahankan Solusi Solver opsi, dan klik OK untuk keluar dari dialog. Lihat tangkapan layar:
   

Dapatkan semua kombinasi angka sama dengan jumlah tertentu

Menjelajahi kemampuan Excel yang lebih dalam memungkinkan Anda menemukan setiap kombinasi angka yang cocok dengan jumlah tertentu, dan ini lebih mudah dari yang Anda kira. Bagian ini akan menunjukkan kepada Anda dua metode untuk menemukan semua kombinasi angka yang sama dengan jumlah tertentu.

Dapatkan semua kombinasi angka yang sama dengan jumlah tertentu dengan Fungsi Buatan Pengguna

Untuk mengungkap setiap kemungkinan kombinasi angka dari kumpulan tertentu yang secara kolektif mencapai nilai tertentu, fungsi khusus yang diuraikan di bawah ini berfungsi sebagai alat yang efektif.

Langkah 1: Buka editor modul VBA dan salin kodenya

1. Tahan ALT + F11 kunci di Excel, dan itu membuka file Microsoft Visual Basic untuk Aplikasi jendela.
2. Klik Menyisipkan > Modul, dan tempel kode berikut di Module Window.
   Kode VBA: Dapatkan semua kombinasi angka sama dengan jumlah tertentu

   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
   

Langkah 2: Masukkan rumus khusus untuk mendapatkan hasilnya

Setelah menempelkan kode, tutup jendela kode untuk kembali ke lembar kerja. Masukkan rumus berikut ke dalam sel kosong untuk menampilkan hasilnya, lalu tekan Enter kunci untuk mendapatkan semua kombinasi. Lihat tangkapan layar:

=MakeupANumber(A2:A10,B2)

jenis: Jika ingin mencantumkan hasil kombinasi secara vertikal dalam satu kolom, silakan terapkan rumus berikut:

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

Dapatkan semua kombinasi angka yang sama dengan jumlah tertentu dengan fitur canggih

Mengingat keterbatasan fungsi yang disebutkan di atas, kami merekomendasikan solusi cepat dan komprehensif: Kutools for Excel's Make up a Number fitur, yang kompatibel dengan versi Excel apa pun. Alternatif ini dapat secara efektif menangani bilangan positif, desimal, dan bilangan negatif. Dengan fitur ini, Anda dapat dengan cepat mendapatkan semua kombinasi yang sama dengan jumlah tertentu.

1. Klik Kutools > Konten > Buat Nomor A, lihat tangkapan layar:
   
2. Lalu, di Buatlah angka kotak dialog, silakan klik untuk memilih daftar nomor yang ingin Anda gunakan dari Sumber data, lalu masukkan jumlah total ke dalam Jumlah kotak teks. Terakhir, klik OK tombol, lihat tangkapan layar:
   
3. Dan kemudian, kotak prompt akan muncul untuk mengingatkan Anda untuk memilih sel untuk menemukan hasilnya, lalu klik OK, lihat tangkapan layar:
   
4. Dan sekarang, semua kombinasi yang sama dengan angka yang diberikan telah ditampilkan seperti gambar di bawah ini:
   

Dapatkan semua kombinasi angka yang jumlahnya dalam suatu rentang dengan kode VBA

Terkadang, Anda mungkin berada dalam situasi di mana Anda perlu mengidentifikasi semua kemungkinan kombinasi angka yang secara kolektif menghasilkan jumlah dalam rentang tertentu. Misalnya, Anda mungkin mencari setiap kemungkinan pengelompokan angka yang totalnya berada di antara 470 dan 480.

Menemukan semua kemungkinan kombinasi angka yang merangkum suatu nilai dalam rentang tertentu merupakan tantangan yang menarik dan sangat praktis di Excel. Bagian ini akan memperkenalkan kode VBA untuk menyelesaikan tugas ini.

Langkah 1: Buka editor modul VBA dan salin kodenya

1. Tahan ALT + F11 kunci di Excel, dan itu membuka file Microsoft Visual Basic untuk Aplikasi jendela.
2. Klik Menyisipkan > Modul, dan tempel kode berikut di Module Window.
   Kode VBA: Dapatkan semua kombinasi angka yang berjumlah rentang tertentu

   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
   
   
   

Langkah 2: Jalankan kodenya

1. Setelah menempelkan kode, tekan F5 kunci untuk menjalankan kode ini, pada dialog pertama yang muncul, pilih rentang angka yang ingin Anda gunakan, dan klik OK. Lihat tangkapan layar:
   
2. Di kotak prompt kedua, pilih atau ketik angka batas bawah, dan klik OK. Lihat tangkapan layar:
   
3. Di kotak prompt ketiga, pilih atau ketik angka batas tinggi, dan klik OK. Lihat tangkapan layar:
   
4. Di kotak prompt terakhir, pilih sel keluaran, di situlah hasilnya akan mulai dikeluarkan. Lalu klik OK. Lihat tangkapan layar:
   

Hasil

Sekarang, setiap kombinasi yang memenuhi syarat akan dicantumkan dalam baris berturut-turut di lembar kerja, dimulai dari sel keluaran yang Anda pilih.

Excel memberi Anda beberapa cara untuk menemukan kelompok angka yang berjumlah total tertentu, setiap metode bekerja secara berbeda, sehingga Anda bisa memilih salah satu berdasarkan seberapa familiar Anda dengan Excel dan apa yang Anda perlukan untuk proyek Anda. Jika Anda tertarik untuk menjelajahi tips dan trik Excel lainnya, situs web kami menawarkan ribuan tutorial klik di sini untuk mengaksesnya. Terima kasih telah membaca, dan kami berharap dapat memberi Anda informasi bermanfaat lainnya di masa mendatang!

Artikel Terkait: