Loncat ke daftar isi utama

Bagaimana menemukan semua kombinasi yang sama dengan jumlah tertentu di Excel?

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

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)
Note: Dalam rumus ini: B2: B10 adalah kolom sel kosong di samping daftar nomor Anda, dan A2: A10 adalah daftar nomor yang Anda gunakan.

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:
Note: Namun, metode ini memiliki keterbatasan: metode ini hanya dapat mengidentifikasi satu kombinasi sel yang berjumlah hingga jumlah yang ditentukan, meskipun terdapat beberapa kombinasi yang valid.

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)
Note: Dalam rumus ini: A2: A10 adalah daftar nomor, dan B2 adalah jumlah total yang ingin Anda dapatkan.

jenis: Jika ingin mencantumkan hasil kombinasi secara vertikal dalam satu kolom, silakan terapkan rumus berikut:
=TRANSPOSE(MakeupANumber(A2:A10,B2))
Keterbatasan metode ini:
  • Fungsi kustom ini hanya berfungsi di Excel 365 dan 2021.
  • Metode ini hanya efektif untuk bilangan positif; nilai desimal secara otomatis dibulatkan ke bilangan bulat terdekat, dan angka negatif akan menghasilkan kesalahan.

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.

Tips: Untuk menerapkan ini Buat Nomor A fitur, pertama, Anda harus mengunduh Kutools untuk Excel, lalu terapkan fitur dengan cepat dan mudah.
  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:
Note: Untuk menerapkan fitur ini, silakan unduh dan instal Kutools untuk Excel pertama.

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:

  • Buat daftar atau hasilkan semua kemungkinan kombinasi
  • Katakanlah, saya memiliki dua kolom data berikut, dan sekarang, saya ingin membuat daftar semua kemungkinan kombinasi berdasarkan dua daftar nilai seperti yang ditunjukkan pada gambar di kiri. Mungkin, Anda dapat membuat daftar semua kombinasi satu per satu jika ada beberapa nilai, tetapi, jika ada beberapa kolom dengan beberapa nilai yang perlu dicantumkan kemungkinan kombinasi, berikut beberapa trik cepat yang dapat membantu Anda untuk mengatasi masalah ini di Excel .
  • Hasilkan semua kombinasi 3 atau beberapa kolom
  • Misalkan, saya memiliki 3 kolom data, sekarang, saya ingin membuat atau mencantumkan semua kombinasi data dalam 3 kolom ini seperti gambar di bawah ini. Apakah Anda memiliki metode yang bagus untuk menyelesaikan tugas ini di Excel?
  • Hasilkan daftar semua kemungkinan kombinasi 4 digit
  • Dalam beberapa kasus, kita mungkin perlu membuat daftar semua kemungkinan kombinasi 4 digit dari angka 0 sampai 9, yang berarti menghasilkan daftar 0000, 0001, 0002… 9999. Untuk menyelesaikan tugas daftar dengan cepat di Excel, saya perkenalkan beberapa trik untuk Anda.
Comments (49)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi everyone,

I'm trying to find a way to to find all possible combinations of workershifts for a specific amount of workhours within a specific amount of working days.

Both, solver and Kutools basically work, but:

Solver only ever displays one combination not all of them (which is what I would need).

Kutools on the other hand only uses each value once, for example I know there are solutions with say 3 7-hour shifts but it won't pick the shift more than once.

Thanks in advance for any responses!

Hope I'm not threadnecroing too hard here.
This comment was minimized by the moderator on the site
Hello, simsok,
Sorry, I can't understand you clearly.
Could you give your problem more detailed, or can you insert a screenshot of your problem here?
Thank you!
This comment was minimized by the moderator on the site
Thank you so much!!! The solver add-in worked for me!
This comment was minimized by the moderator on the site
How to get list of cell names that add to a given no.
This comment was minimized by the moderator on the site
Hello, Ranka,
I'm sorry, at present, there is no good way for getting the cells that add to a given number.
Thank you!
This comment was minimized by the moderator on the site
ExtendOffice - How To Find All Combinations That Equal A Given Sum In Excel

In this example, I would like to run 100 rows instead of the 8 in your example. When I try to create more rows the formula stops working. I started over with a fresh sheet and I still can not get the formula to work.

Running windows 10
Excel 2207
Office 365
This comment was minimized by the moderator on the site
Hello, Shaw

Unfortunately, the formula in this article has a limit of 20 values, if there are more than 20 numbers, the result will not come out. In this case, I will recommend the Make Up A Number feature of Kutools for Excel, with it, you can find all combinations from the list of numbers. If you have a lot of numbers and many combination results, it will take much time, but you can set the number of combinations to make it faster. See screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-make-up-number-1.png

You can download this tool from here: https://www.extendoffice.com/download/kutools-for-excel.html
You can try it for free 30 days, please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Hi I have tried doing this with the first method and it just isn't working. I'm unsure what I'm doing wrong as I seem to have followed the instructions exactly. Are there any common errors to watch out for? There is no error showing in the formula itself and all cells are all in the same position as yours are, but it's a list of 48 numbers rather than 8.
This comment was minimized by the moderator on the site
Hello, Angie
If you can't get the result by using the first method, you can view the video at the bottom of this article:
https://www.extendoffice.com/documents/excel/3557-excel-find-all-combinations-that-equal-given-sum.html#demo
Also, you can try our tool-Kutools for Excel's Make up a number feature, it will get all combinations quickly and easily.
Thank you!
This comment was minimized by the moderator on the site
So will any of these work when I have been paid by a client, but with no remit so don’t know which invoices have been paid.
This comment was minimized by the moderator on the site
For the solver add in on excel, can you make it solve for closest to 480 rather than value of 480 (example used above)? I really need some help on how this might work out, thanks in advance

This comment was minimized by the moderator on the site
Could this be adapted to find combinations that sum up to specific range i.e. sum between 450 and 500? Is there a way to set it so that each cell value can be used only in one combination not more?
This comment was minimized by the moderator on the site
kutools works only integer value. Not support double. Like (395,52) ! Best solution is excel solver extention.
This comment was minimized by the moderator on the site
is there a way to find combination for a target average instead of sum ?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations