Petua: Bahasa lain diterjemahkan Google. Anda boleh melawat English versi pautan ini.
Log masuk
x
or
x
x
Daftar Sekarang
x

or

Bagaimana untuk mencari semua kombinasi yang sama dengan jumlah yang diberikan dalam Excel?

Sebagai contoh, saya mempunyai senarai nombor berikut, dan sekarang, saya ingin mengetahui gabungan nombor-nombor dalam senarai merangkumi 480, dalam tangkapan skrin berikut yang ditunjukkan, anda dapat melihat terdapat lima kumpulan kemungkinan gabungan yang menambah sama untuk 480, seperti 300 + 60 + 120, 300 + 60 + 40 + 80, dan lain-lain. Artikel ini, saya akan bercakap tentang beberapa kaedah untuk mencari sel-sel mana merangkumi nilai khusus dalam Excel.

doc cari kombinasi gabungan sama 1

Cari gabungan sel yang sama dengan jumlah yang diberikan dengan formula

Cari gabungan sel yang sama dengan jumlah yang diberikan dengan Add-in Solver

Cari gabungan sel yang sama dengan jumlah yang diberikan dengan Fungsi Ditetapkan Pengguna


Senaraikan Semua Kombinasi Kemungkinan dari pelbagai lajur:

Kutools untuk Excel'S Senaraikan Semua Kombinasi Utiliti boleh membantu anda dengan cepat menjana / menyenaraikan semua kombinasi yang mungkin berdasarkan pelbagai senarai nilai.

doc cari kombinasi gabungan sama 2

Kutools untuk Excel: dengan lebih daripada 200 berguna Excel tambahan, bebas untuk mencuba tanpa had pada hari 60. Muat turun dan percubaan percuma Sekarang!


Cari gabungan sel yang sama dengan jumlah yang diberikan dengan formula


Pertama, anda perlu membuat beberapa nama julat, dan kemudian gunakan formula array untuk mencari sel-sel yang jumlah nilai sasaran, sila lakukan langkah demi langkah berikut:

1. Pilih senarai nombor dan tentukan senarai ini nama pelbagai - Range1 ke Kotak Nama, dan tekan Masukkan kunci untuk menyelesaikan nama julat yang ditetapkan, lihat tangkapan skrin:

doc cari kombinasi gabungan sama 2

2. Selepas mendefinisikan nama pelbagai untuk senarai nombor, maka anda perlu membuat dua nama jarak jauh di dalam Nama Pengurus kotak, sila klik Formula > Nama Pengurus, Dalam Nama Pengurus kotak dialog, klik Baru butang, lihat tangkapan skrin:

doc cari kombinasi gabungan sama 3

3. Dalam keluar Nama Baru dialog, masukkan nama List1 ke Nama bidang, dan taipkan formula ini = ROW (TIDAK LANGSUNG ("1:" & ROWS (Range1))) (Range1 adalah nama jarak yang telah anda buat dalam langkah1) ke dalam Merujuk kepada medan, lihat tangkapan skrin:

doc cari kombinasi gabungan sama 4

4. Klik OK untuk kembali ke Nama Pengurus dialog, kemudian terus klik Baru butang untuk mencipta nama julat lain, dalam Nama Baru dialog, masukkan nama List2 ke Nama bidang, dan taipkan formula ini = ROW (TIDAK LANGSUNG ("1:" & 2 ^ ROWS (Range1))) (Range1 adalah nama jarak yang telah anda buat dalam langkah1) ke dalam Merujuk kepada medan, lihat tangkapan skrin:

doc cari kombinasi gabungan sama 5

5. Setelah mencipta nama pelbagai, sila gunakan rumusan formula berikut ke dalam sel B1:

=IF(ISNUMBER(MATCH(ROWS($1:1),IF(INDEX(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),MATCH(TRUE,MMULT(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),Range1)=$C$2,0),),TRANSPOSE(List1)),0)),"X",""), dan tekan Shift + Ctrl + Enter kekunci bersama-sama, kemudian tarik pemegang lengkokan ke sel B8, nombor terakhir senarai, dan anda boleh melihat nombor yang jumlahnya 480 ditandakan sebagai X dalam lajur B, lihat tangkapan skrin:

doc cari kombinasi gabungan sama 6

Nota:

1. Dalam formula panjang di atas: List1, List2 dan Range1 adalah nama julat yang telah anda buat dalam langkah pertama, C2 adalah nilai khusus yang anda mahu nombor ditambah sehingga.

2. Jika lebih daripada satu gabungan nilai mempunyai jumlah yang sama dengan nilai tertentu, hanya satu gabungan disenaraikan.


Cari gabungan sel yang sama dengan jumlah yang diberikan dengan Add-in Solver

Jika anda keliru dengan kaedah di atas, Excel mengandungi a Add-in Solver ciri, dengan menggunakan add-in ini, anda juga boleh mengenal pasti nombor yang jumlahnya sama dengan nilai yang diberikan.

1. Pertama, anda perlu mengaktifkannya Solver tambahkan, sila pergi ke File > Pilihan, Dalam Pilihan Excel kotak dialog, klik Add-Ins dari anak tetingkap kiri, dan kemudian klik Add-in Solver daripada Aplikasi Tambahan Tidak Aktif seksyen, lihat tangkapan skrin:

doc cari kombinasi gabungan sama 7

2. Kemudian klik Go butang untuk memasuki Add-Ins dialog, periksa Add-in Solver pilihan, dan klik OK untuk memasang add-in ini dengan jayanya.

doc cari kombinasi gabungan sama 8

3. Selepas mengaktifkan add-in Solver, maka anda perlu memasukkan formula ini ke dalam sel B9: = SUMPRODUCT (B1: B8, A1: A8), (B1: B8 adalah sel lajur kosong di sebelah senarai nombor anda, dan A1: A8 adalah senarai nombor yang anda gunakan. ), dan tekan Masukkan kunci, lihat tangkapan skrin:

doc cari kombinasi gabungan sama 9

4. Kemudian klik Tarikh > Solver untuk pergi ke Parameter Penyelesai kotak dialog, dalam dialog, sila lakukan operasi berikut:

(1.) Klik butang 1 doc butang untuk memilih sel B9 di mana formula anda dari Tetapkan Objektif seksyen;

(2.) Kemudian di Untuk seksyen, pilih Nilai, dan masukkan nilai sasaran anda 480 seperti yang anda perlukan;

(3.) Di bawah Dengan Mengubah Sel Variabel seksyen, sila klik butang 1 doc butang untuk memilih julat sel B1: B8 di mana akan menandakan nombor yang sepadan anda.

doc cari kombinasi gabungan sama 10

5. Kemudian klik Tambah butang untuk pergi ke Tambah Kekangan kotak dialog, klik butang 1 doc butang untuk memilih julat sel B1: B8, And select bin dari senarai jatuh bawah, lihat tangkapan skrin:

doc cari kombinasi gabungan sama 11

6. Klik OK untuk kembali Parameter Penyelesai dialog, kemudian klik Menyelesaikan butang, beberapa minit kemudian, a Keputusan Solver kotak dialog muncul, dan anda dapat melihat kombinasi sel yang sama dengan jumlah tertentu 480 ditandakan sebagai 1. Di dalam Keputusan Solver dialog, sila pilih Simpan Solver Solution pilihan, dan klik OK untuk keluar dari dialog. Lihat tangkapan skrin:

doc cari kombinasi gabungan sama 12

Catatan: Kaedah ini juga hanya boleh mendapatkan satu sel gabungan jika terdapat lebih dari satu kombinasi nilai mempunyai jumlah yang sama dengan nilai khusus.


Demo: Cari gabungan sel yang sama dengan jumlah yang diberikan dalam Excel


Cari gabungan sel yang sama dengan jumlah yang diberikan dengan Fungsi Ditetapkan Pengguna

Dua kaedah pertama adalah semua kompleks untuk kebanyakan pengguna Excel kami, di sini, saya boleh membuat kod VBA untuk menyelesaikan tugas ini dengan cepat dan mudah.

Untuk mendapatkan hasil yang betul, anda mesti mengisih senarai nombor dalam urutan menurun terlebih dahulu. Dan kemudian lakukan langkah-langkah berikut:

1. Tahan ALT + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi tingkap.

2. Klik Memasukkan > Modul, dan tampal kod berikut dalam Modul Tetingkap.

Kod VBA: Cari gabungan sel yang sama dengan jumlah yang diberikan:

Function GetCombination(CoinsRange As Range, SumCellId As Double) As String
'updateby Extendoffice 20160506
    Dim xStr As String
    Dim xSum As Double
    Dim xCell As Range
    xSum = SumCellId
    For Each xCell In CoinsRange
        If Not (xSum / xCell < 1) Then
            xStr = xStr & Int(xSum / xCell) & " of " & xCell & "  "
            xSum = xSum - (Int(xSum / xCell)) * xCell
        End If
    Next
    GetCombination = xStr
End Function

3. Kemudian simpan dan tutup tetingkap kod ini, kemudian kembali ke lembaran kerja, dan masukkan formula ini = getcombination (A1: A8, C2) ke dalam sel kosong, dan tekan Masukkan utama, anda akan mendapat hasil berikut yang memaparkan nombor gabungan yang sama dengan jumlah yang diberikan, lihat tangkapan skrin:

doc cari kombinasi gabungan sama 13

Nota:

1. Dalam formula di atas, A1: A8 adalah julat nombor, dan C2 mengandungi nilai sasaran yang anda mahu sama.

2. Jika lebih daripada satu gabungan nilai mempunyai jumlah yang sama dengan nilai tertentu, hanya satu gabungan disenaraikan.


Cadangan Alat Produktiviti untuk Excel

kte tab 201905

Kutools untuk Excel Membantu Anda Selalu Menyelesaikan Kerja di Hadapan Masa, dan Berdiri Daripada Orang

  • Lebih daripada ciri-ciri canggih 300 yang canggih, yang direka untuk senario kerja 1500, meningkatkan produktiviti oleh 70%, memberi anda lebih banyak masa untuk menjaga keluarga dan menikmati kehidupan.
  • Tidak perlu lagi menghafal formula dan kod VBA, berikan rehat dari otak anda sekarang.
  • Menjadi pakar Excel dalam minit 3, Operasi yang rumit dan berulang boleh dilakukan dalam beberapa saat,
  • Mengurangkan beribu-ribu operasi papan kekunci & tetikus setiap hari, mengucapkan selamat tinggal kepada penyakit pekerjaan sekarang.
  • 110,000 orang yang sangat berkesan dan pilihan syarikat 300 + yang terkenal di dunia.
  • Ciri-ciri penuh 60-hari adalah percubaan percuma. Jaminan wang balik 60 hari. Tahun 2 peningkatan dan sokongan percuma.

Membawa Browsing Tab dan Editing ke Microsoft Office, Jauh Lebih Berkuasa Daripada Tab Penyemak Imbas

  • Tab Pejabat direka untuk Aplikasi Pejabat Word, Excel, PowerPoint dan Lain-lain: Penerbit, Akses, Visio dan Projek.
  • Buka dan buat beberapa dokumen dalam tab baharu pada tetingkap yang sama, dan bukannya dalam tetingkap baru.
  • Meningkatkan produktiviti anda oleh 50%, dan mengurangkan beratus-ratus klik tetikus untuk anda setiap hari!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Feroz · 23 days ago
    How do you do for the list of numbers like 480
  • To post as a guest, your comment is unpublished.
    Guilherme Dorn · 2 months ago
    Thank you very much! Resolved my problem correctly.
  • To post as a guest, your comment is unpublished.
    Miss Jones · 3 months ago
    Thank you so much for the VBA coding, it has solved a major headache trying to find combinations to equal an exact amount.
  • To post as a guest, your comment is unpublished.
    Stephanie · 4 months ago
    I have 1162 cells to find number x. Excel tells me that is too many variable cells. Very small data set! Any suggestions? Thanks!
  • To post as a guest, your comment is unpublished.
    a · 7 months ago
    Will the solver add-in not work if there are negative numbers in the list or if the value of number is 0? I'm trying to find a sum of numbers in a list that equate to zero with some numbers being negative and positive, but the solver does not work. I changed a couple numbers on my list to test to make sure I followed the steps correctly and it did work for the test. Please advise if there is a way to solve with negative and positive numbers to find a 0 value.