Cari semua kombinasi nombor yang sama dengan jumlah yang diberikan dalam Excel

3 cara untuk mencari semua kombinasi dari satu set nombor yang jumlahnya kepada jumlah tertentu.

Mencari kombinasi nilai yang menambah jumlah yang diberikan adalah masalah biasa dalam analisis data. Sebagai contoh, anda mungkin ingin mengetahui semua kemungkinan kombinasi item yang boleh dibeli dengan anggaran tertentu, atau semua cara yang mungkin untuk memperuntukkan sumber untuk memenuhi keperluan tertentu. Dalam artikel ini, kami akan meneroka cara menggunakan Excel Solver dan VBA untuk menyelesaikan tugas. Kami juga akan membincangkan beberapa perangkap yang berpotensi dan batasan setiap pendekatan yang mungkin timbul ketika mengendalikan dataset besar atau kekangan kompleks.

• Cari gabungan nombor yang sama dengan jumlah yang diberikan - Solver Excel
• Dapatkan semua kombinasi yang Jumlah ke Jumlah tertentu - Fungsi tersuai
• Kembalikan semua kombinasi yang menambah nilai yang diberikan - makro

Cari gabungan nombor yang sama dengan jumlah yang diberikan dengan Solver Excel

Malangnya, tidak ada fungsi Excel yang dibina dapat membantu anda mengenal pasti angka -angka yang menambah jumlah yang diberikan. Nasib baik, Excel menyediakan tambahan khas untuk menyelesaikan masalah pengaturcaraan linear. Tambahan Solver disertakan dengan semua versi Excel tetapi tidak didayakan secara lalai. Jika anda tidak biasa dengan alat ini, inilah artikel yang hebat tentang cara menambah dan menggunakan Solver dalam Excel.

Dengan solver add-in yang diaktifkan dalam Excel anda, teruskan dengan langkah-langkah berikut:

1. Buat model.

   Untuk ini, masukkan set nombor anda dalam satu lajur (A3: A12 dalam kes kami) dan tambahkan lajur kosong di sebelah kanan nombor anda untuk hasilnya (B3: B12). Dalam sel yang berasingan (B13), masukkan formula sumroduct yang serupa dengan ini:

   =SUMPRODUCT(A3:A12, B3:B12)

   

2. Jalankan solver add-in.

   Pada tab Data , dalam kumpulan analisis , klik butang Solver .

   

3. Tentukan masalah untuk pemecah.

   Dalam kotak dialog Parameter Solver , konfigurasikan sel objektif dan berubah -ubah:

   • Dalam kotak objektif yang ditetapkan , masukkan alamat sel formula (B13).
   • Di bahagian ke , pilih nilai dan taipkan nilai jumlah yang dikehendaki (50 dalam contoh ini).
   • Dalam kotak sel Variabel yang berubah -ubah , pilih julat yang akan dihuni dengan hasilnya (B3: B12).

   

4. Tambah kekangan.

   Untuk menentukan kekangan, iaitu sekatan atau syarat yang mesti dipenuhi, klik butang Tambah . Dalam tetingkap dialog Tambah Kekangan , pilih julat yang dihasilkan (B3: B12) dan pilih bin dari senarai juntai bawah. Kekangan akan ditetapkan secara automatik kepada binari . Apabila selesai, klik OK.

   

5. Menyelesaikan masalah.

   Apabila dibawa balik ke tetingkap dialog Parameter Solver , semak tetapan anda dan klik butang Solve .

   

   Beberapa saat (atau minit) kemudian, kotak dialog hasil Solver akan muncul. Jika berjaya, pilih pilihan Penyelesaian Keep Solver , dan klik OK untuk keluar dari dialog.

   

Akibatnya, anda akan mempunyai 1 dimasukkan di sebelah nombor yang menambah jumlah yang ditentukan. Bukan penyelesaian yang mesra pengguna, tetapi ia adalah yang terbaik dari kotak Excel yang boleh dilakukan.

Untuk tujuan visualisasi, saya telah menyerlahkan sel -sel yang memberikan jumlah yang dikehendaki dalam cahaya hijau:

Batasan : Solver Excel dapat mencari, paling banyak, hanya satu gabungan nombor yang sama dengan jumlah tertentu.

Cari semua kombinasi yang sama dengan jumlah yang diberikan dengan fungsi tersuai

Untuk mendapatkan semua kombinasi yang mungkin dari satu set nombor yang diberikan sehingga nilai tertentu, anda boleh menggunakan fungsi tersuai di bawah. Jika anda baru untuk UDFS, anda akan dapati banyak maklumat berguna dalam panduan ini: Bagaimana untuk membuat fungsi pengguna yang ditetapkan dalam Excel.

Fungsi tersuai untuk mencari semua kombinasi yang sama dengan jumlah yang diberikan

Opsyen Fungsi Public Explicit findsumcombinations (rngnumbers sebagai julat, ltargetsum selagi panjang ) redur arnumbers () sebagai panjang , bahagian () sebagai long dimrat arres () sebagai rentetan rentetan indi sel dim yang panjang sebagai redim arres ( 0 ) arnumbers (indi) = clng (cellcurr.value) indi = indi 1 seterusnya cellcurr panggilan sumuprecursivecombinations ( arnumbers, ltargetsum, bahagian () , arres () akhir jika redim preserve arr ( 0 ke ubound ( arres ) , sasaran selagi , bahagian () selagi , byref arres () sebagai rentetan ) redur seketika , saya lama, J lama , num lama , indres selagi baki panjang () selagi panjang , partrec () selagi s = sumArray (bahagian ) jika s = sasaran kemudian indres ( arres ) sasaran kemudian keluar dari sub jika ( tidak bukan nombor) maka untuk i = 0 ke ubound (nombor) memadam baki () num = nombor (i) untuk j = i 1 ke ubound (angka) addToArray baki, nombor (j) next j erase partrec ( ) ArrayToString (x () selagi panjang ) sebagai rentetan rentetan , hasilnya sebagai hasil rentetan = x ( n) untuk n = lbound (x ) 1 ke uBound ( x ) hasil = hasil & " , " & x ( n ) seterusnya n arraytoString = hasil akhir fungsi sumarray ( x () ( x ) sumArray = sumArray x (n) seterusnya n akhir jika fungsi akhir sub swasta swasta (arr ( ) selagi x lama ) Jika ( tidak arr ) > 0 maka redim redim ( 0 ke ubound ( arr ) sumber) 0 kemudian untuk n = 0 ke ubiund (sumber) addToArray destinasi, sumber (n) akhir n seterusnya jika akhir sub

Bagaimana fungsi ini berfungsi

Fungsi utama, FindSumcombinations , memanggil beberapa fungsi subsidiari yang melaksanakan sub-tugas yang lebih kecil. Fungsi yang dinamakan SumuprecursiveCombinations melaksanakan algoritma teras yang mendapati semua jumlah yang mungkin dalam julat yang ditentukan dan menapis mereka yang mencapai sasaran. Fungsi arraytoString mengawal bentuk rentetan output. Tiga lagi fungsi ( SumArray , AddToArray , dan CopyArray ) bertanggungjawab untuk memproses tatasusunan perantaraan: Setiap kali kita membuat array sementara, tambahkan satu elemen dari array sumber kepadanya, dan periksa sama ada jumlah sasaran dicapai. Algoritma teras diambil dari thread StackOverflow ini, terima kasih untuk berkongsi!

Sintaks

Dari perspektif pengguna, sintaks fungsi tersuai kami semudah ini:

FindSumCombinations (julat, jumlah)

Di mana:

• Julat adalah julat nombor untuk diuji.
• Jumlah adalah jumlah sasaran.

Perhatikan! Fungsi tersuai hanya berfungsi dalam array dinamik Excel 365 dan 2021.

Cara menggunakan fungsi FindSumCombinations:

1. Masukkan kod di atas ke dalam modul kod buku kerja anda dan simpannya sebagai buku kerja makro (.xlsm). Arahan langkah demi langkah ada di sini.
2. Dalam mana -mana sel kosong, masukkan formula FindSumCombinations dan tekan kekunci Enter . Pastikan terdapat sel -sel kosong yang cukup di sebelah kanan untuk mengeluarkan semua kombinasi, jika tidak, formula akan mengembalikan ralat #Spill.

Sebagai contoh, untuk mencari semua kemungkinan kombinasi nombor dalam julat A6: A15 yang sama dengan jumlah dalam A3, formula adalah:

=FindSumCombinations(A6:A15, A3)

Seperti mana -mana fungsi array dinamik yang lain, anda memasukkan formula hanya satu sel (C6 dalam imej di bawah) dan ia memaparkan hasilnya ke dalam sel -sel yang diperlukan. Secara lalai, rentetan yang dipisahkan koma adalah output berturut-turut:

Untuk mengembalikan hasil dalam lajur, bungkus fungsi tersuai ke dalam transpose seperti ini:

=TRANSPOSE(FindSumCombinations(A6:A15, A3))

Untuk mengeluarkan rentetan dalam bentuk array yang tertutup dalam pendakap keriting, ubah fungsi arraytoString seperti berikut:

ArraytoString fungsi peribadi ( x ( ) lama ) sebagai rentetan rentet​​​​​​​​​

Hasilnya akan kelihatan serupa dengan ini:

Batasan : Fungsi tersuai ini hanya berfungsi dalam array dinamik Excel 365 dan Excel 2021.

Dapatkan semua kombinasi yang sama dengan jumlah yang diberikan dengan makro VBA

Fungsi tersuai yang diterangkan di atas mengembalikan kombinasi nombor sebagai rentetan. Jika anda lebih suka mempunyai setiap nombor dalam sel yang berasingan, makro ini akan membantu. Kod ini ditulis oleh pakar Excel lain Alexander Trifuntov yang telah secara aktif membantu pengguna menyelesaikan pelbagai masalah Excel di blog ini.

Mulakan dengan menambahkan kod berikut ke buku kerja anda. Untuk langkah -langkah terperinci, sila lihat cara memasukkan kod VBA dalam Excel.

Makro untuk mencari semua kombinasi yang menambah nilai yang diberikan

REVARRAY PUBLIK1 sebagai rentetan awam ds sebagai variasi sasaran awam sebagai sasaran awam yang panjang sebagai integer public sasaran sebagai gabungan sub -integer ( ) userform1.show end function graycode ( item sebagai variasi ) sebagai codevector rentet​​​​ Sebagai boolean redup oddstep sebagai boolean dimalit sss dimautArray () sebagai string kk = targetCol rr = targetRow col1 = targetCol 3 row1 = targetRow oddstep = true lebih rendah = lbound (item) atas = ubound (item) Sel -sel (rr - 1, kk) .font.bold = sel -sel yang benar (rr - 1 , kk 1) .font.bold = codevector redim sejati (lebih rendah ke atas) ' ia bermula semua 0 lakukan sehingga selesai newsub = "" untuk i = lebih rendah ke atas jika codevector (i ) & Item (i) SSS = item SSS (i) Akhir jika akhir jika Seterusnya I jika newsub = "" maka newsub = "}" 'set kosong sublist = sublist & vbcrlf & newsub jika sss = sasaran kemudian sel (rr, kk) .numberFormat = "@" Split (Mid (Newsub, 2), "," ) n1 = Ubound (TargetArray) untuk E = 0 hingga N1 sel (row1, col1) = TargetArray ( e) row1 = row1 1 next e col1 = col1 1 row1 = targetRow rr = rr 1 end if sss = 0 'sekarang kod kod 'Pertama cari pertama 1 i = lebih rendah lakukan semasa codevector (i) 1 i = i 1 loop ' dilakukan jika i = atas: jika i = atas kemudian selesai = benar lain ' jika tidak dilakukan kemudian flip * seterusnya * bit: i = i 1 codevector (i) = 1 - codevector (i ) akhir jika endStep

Seterusnya, buat pengguna ( masukkan > userform ) dengan reka bentuk dan sifat berikut:

Selepas melengkapkan reka bentuk borang, tambahkan kod untuk borang tersebut. Untuk ini, klik kanan pada borang dalam Projek Explorer dan pilih Kod Lihat :

Kod untuk UserForm

Sub CommandButton1_Click () dim b d cisis sebagai integer dim d sebagai integer dim a () sebagai variasi dim i sebagai integer dim e sebagai integer ds = julat (refedit1) targetsum = textbox1.value julat (refedit2). Pilih TargetCol = Selection.Column TargetRow = Selection.Row C = lbound (ds) d = ubound (ds) redim b (d - 1 ) untuk i = 1 hingga d e = i - 1 b ( e ) = ds ( i , 1 )

Petua. Daripada membuat semula borang dari awal, anda boleh memuat turun buku kerja sampel kami di hujung siaran ini dan menyalin modul kod dan userform1 dari sana (lihat cara menyalin makro ke buku kerja lain). Untuk menjadikan makro boleh diakses dalam semua buku kerja anda, salinnya ke buku kerja makro peribadi.

Dengan kod dan bentuk di tempat, tekan Alt F8 dan jalankan makro Findallcombinations :

Mengenai bentuk yang muncul, tentukan perkara berikut:

• Julat dengan nombor sumber (A4: A13)
• Jumlah sasaran (50)
• Sel kiri atas julat destinasi (C4).

Apabila menentukan sel kiri atas julat output, pastikan terdapat sekurang-kurangnya satu baris kosong di atas (untuk tajuk) dan sel kosong yang cukup ke bawah dan ke kanan . Sekiranya terdapat sel kosong yang tidak mencukupi, data anda yang sedia ada akan ditimpa.

Mengklik OK akan menghasilkan hasil seperti yang ditunjukkan dalam tangkapan skrin di bawah:

• Dalam C4: C6, anda mempunyai kombinasi nombor sebagai nilai yang dipisahkan koma.
• Lajur F, G dan H memegang kombinasi nombor yang sama, dengan setiap nombor yang tinggal di dalam sel yang berasingan.
• Dalam D4, anda mempunyai jumlah sasaran.

Bentuk output ini menjadikannya lebih mudah untuk memeriksa hasilnya - hanya masukkan formula jumlah dalam sel F13, seret ke kanan di dua sel lagi dan anda akan melihat bahawa setiap gabungan nombor menambah sehingga nilai yang ditentukan (50).

Batasan: Untuk satu set nombor yang besar, makro mungkin mengambil sedikit masa untuk menghasilkan semua kombinasi yang mungkin.

Kelebihan : Bekerja dalam semua versi Excel 2010 - 365; Menyediakan dua bentuk output - rentetan nilai dan nombor yang dipisahkan koma dalam sel berasingan.

Kesimpulannya, mencari semua kombinasi nilai yang sama dengan nilai tertentu adalah alat yang berkuasa untuk menyelesaikan pelbagai masalah analisis data. Mudah -mudahan, artikel ini telah memberi anda titik permulaan yang berguna untuk meneroka topik ini lebih lanjut dan mengendalikan masalah yang sama dalam kerja anda sendiri. Terima kasih kerana membaca!

Amalan Buku Kerja

Cari kombinasi yang sama dengan jumlah yang diberikan - contoh (fail .xlsm)

Atas ialah kandungan terperinci Cari semua kombinasi nombor yang sama dengan jumlah yang diberikan dalam Excel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!