Note: The other languages of the website are Google-translated. Back to English

Bagaimana cara membuat daftar semua kemungkinan kombinasi dari satu kolom di Excel?

Jika Anda ingin mengembalikan semua kemungkinan kombinasi dari data kolom tunggal untuk mendapatkan hasil seperti gambar di bawah ini, apakah Anda memiliki cara cepat untuk menangani tugas ini di Excel?

Daftar semua kemungkinan kombinasi dari satu kolom dengan rumus

Daftar semua kemungkinan kombinasi dari satu kolom dengan kode VBA


Daftar semua kemungkinan kombinasi dari satu kolom dengan rumus

Rumus array berikut dapat membantu Anda mencapai pekerjaan ini, lakukan langkah demi langkah:

1. Pertama, Anda harus membuat dua sel formula pembantu. Di sel C1, masukkan rumus di bawah ini, dan tekan Ctrl + Shift + Enter kunci untuk mendapatkan hasil:

=MAX(LEN(A2:A6))
Note: Dalam rumus ini, A2: A6 adalah daftar sel yang ingin Anda daftarkan kombinasinya.

2. Di sel C2, masukkan rumus berikut, dan tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan hasil kedua, lihat tangkapan layar:

=CONCAT(A2:A6&REPT(" ",C2-LEN(A2:A6)))
Note: Dalam rumus ini, A2: A6 adalah daftar sel yang ingin Anda daftarkan kombinasinya, C2 adalah sel berisi rumus yang Anda buat di langkah 1.

3. Kemudian, salin dan tempel rumus berikut di sel D2, dan tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan hasil pertama, lihat tangkapan layar:

=IF(ROW()>2^(COUNTA(A$2:A$6)),"",TEXTJOIN(" + ",TRUE,IF(MID(TEXT(DEC2BIN(ROW()-1),REPT("0",COUNTA($A$2:$A$6))),ROW(INDIRECT("1:"&COUNTA($A$2:$A$6))),1)+0,TRIM(MID($C$3,(ROW(INDIRECT("1:"&COUNTA($A$2:$A$6)))-1)*$C$2+1,$C$2)),"")))
Note: Dalam rumus ini, A2: A6 adalah daftar sel yang ingin Anda daftarkan kombinasinya, C2 adalah sel berisi rumus yang Anda buat di langkah 1, C3 adalah sel dengan rumus yang Anda buat di langkah 2, the + karakter adalah pemisah untuk memisahkan kombinasi, Anda dapat mengubahnya sesuai kebutuhan.

4. Dan kemudian, pilih sel rumus ini, dan seret gagang isian ke bawah hingga sel kosong muncul. Sekarang, Anda dapat melihat semua kombinasi data kolom yang ditentukan ditampilkan seperti demo di bawah ini:

Note: Rumus ini hanya tersedia di Office 2019, 365 dan versi yang lebih baru.

Daftar semua kemungkinan kombinasi dari satu kolom dengan kode VBA

Rumus di atas hanya tersedia untuk versi Excel yang lebih baru, jika Anda memiliki versi Excel yang lebih lama, kode VBA berikut dapat membantu Anda.

1. tekan Alt + F11 tombol secara bersamaan untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Lalu klik Menyisipkan > Modul, salin dan tempel kode VBA di bawah ini ke jendela.

Kode VBA: Daftar semua kemungkinan kombinasi dari satu kolom

Sub ConnectArr()
'Updateby ExtendOffice
Dim xDValue As Variant
Dim xOutRg As Range
Dim xDictionary As Object
Dim xF As Long
Dim xChar As String
xDValue = Range("A2:A6").Value 'the data range
Set xOutRg = Range("C1") 'output range
xChar = "," 'separator
For xF = 1 To UBound(xDValue)
    Set xDictionary = CreateObject("Scripting.Dictionary")
    xDictionary(0) = "Sets of " & xF
    Call ConnectValue(xDValue, xDictionary, 0, xF, 0, "", xChar)
    xOutRg.Offset(0, xF - 1).Resize(xDictionary.Count).Value = WorksheetFunction.Transpose(xDictionary.Items)
    Set xDictionary = Nothing
Next
End Sub
Sub ConnectValue(ByRef pDValue, ByRef pDictionary, ByRef pLevel, ByVal pMaxLevel, ByVal pIndex, ByVal pValue, ByVal pChar)
Dim xF As Long
If pLevel = pMaxLevel Then
    pDictionary(pDictionary.Count + 1) = pValue
    Exit Sub
End If
For xF = pIndex + 1 To UBound(pDValue)
    If pValue = "" Then
        Call ConnectValue(pDValue, pDictionary, pLevel + 1, pMaxLevel, xF, pDValue(xF, 1), pChar)
    Else
        Call ConnectValue(pDValue, pDictionary, pLevel + 1, pMaxLevel, xF, pValue & pChar & pDValue(xF, 1), pChar)
    End If
Next
End Sub
Note: Pada kode diatas:
  • A2: A6: adalah daftar data yang ingin Anda gunakan;
  • C1: adalah sel keluaran;
  • ,: pembatas untuk memisahkan kombinasi.

3. Dan kemudian, tekan F5 kunci untuk mengeksekusi kode ini. Semua kombinasi dari satu kolom terdaftar seperti gambar di bawah ini:


Alat Produktivitas Kantor Terbaik

Kutools for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda dengan
80%

  • Reuse: Masukkan dengan cepat rumus, bagan yang kompleks dan apa pun yang pernah Anda gunakan sebelumnya; Enkripsi Sel dengan kata sandi; Buat Milis dan mengirim email ...
  • Bilah Formula Super (dengan mudah mengedit beberapa baris teks dan rumus); Membaca Tata Letak (membaca dan mengedit sel dalam jumlah besar dengan mudah); Tempel ke Rentang yang Difilter...
  • Gabungkan Sel / Baris / Kolom tanpa kehilangan Data; Pisahkan Konten Sel; Gabungkan Baris / Kolom Duplikat... Mencegah Sel Duplikat; Bandingkan Rentang...
  • Pilih Duplikat atau Unik Baris; Pilih Baris Kosong (semua sel kosong); Temukan Super dan Temukan Fuzzy di Banyak Buku Kerja; Pilih Acak ...
  • Salinan Tepat Beberapa Sel tanpa mengubah referensi rumus; Buat Referensi Otomatis ke Beberapa Lembar; Sisipkan Poin, Kotak Centang, dan lainnya ...
  • Ekstrak Teks, Tambahkan Teks, Hapus berdasarkan Posisi, Hapus Space; Membuat dan Mencetak Subtotal Paging; Konversi Konten Antar Sel dan Komentar...
  • Filter Super (simpan dan terapkan skema filter ke sheet lain); Penyortiran Lanjutan menurut bulan / minggu / hari, frekuensi dan lainnya; Filter Khusus dengan huruf tebal, miring ...
  • Gabungkan Workbooks dan WorkSheets; Gabungkan Tabel berdasarkan kolom kunci; Pisahkan Data menjadi Beberapa Lembar; Konversi Batch xls, xlsx dan PDF...
  • Lebih dari
    300
    fitur yang kuat
    . Mendukung Office/Excel
    2007-2019 dan 365
    . Mendukung semua bahasa. Penerapan yang mudah di perusahaan atau organisasi Anda. Fitur lengkap
    30
    percobaan gratis -hari. Jaminan uang kembali 60 hari.
tab kte 201905

Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Meningkatkan produktivitas Anda dengan
    50%
    , dan mengurangi ratusan klik mouse untuk Anda setiap hari!
officetab bawah
Urutkan komentar berdasarkan
komentar (6)
Belum ada peringkat. Jadilah yang pertama memberi peringkat!
Komentar ini diminimalkan oleh moderator di situs
Saya sangat menyukai metode ini tetapi nilainya paling bawah pada baris ke-511 dan Anda mendapatkan #NUM! jika Anda memiliki lebih dari 6 entri di kolom A. Saya ingin tahu apakah seseorang dapat mempertimbangkan untuk membantu saya menyesuaikan rumus sehingga nilai yang dihasilkan dihitung di luar baris ke-511? Terima kasih banyak! =)
Komentar ini diminimalkan oleh moderator di situs
Halo,
Ya, seperti yang Anda katakan, rumus akan berhenti bekerja di baris 511. Jadi, di sini, Anda dapat menerapkan kode VBA di artikel ini.
Atau jika Anda ingin membuat daftar semua kemungkinan kombinasi menjadi satu kolom, silakan terapkan kode di bawah ini:
Note: Di dalam kode, A2 adalah sel pertama berisi data yang ingin Anda gunakan, Anda harus mengubah referensi sel A2 serta A untuk Anda sendiri. Setelah menjalankan kode, semua kombinasi akan terdaftar di kolom berikutnya dari daftar data.
Sub allcombination()
Dim Ray As Variant, n As Long, nn As Long, Allnum As String, c As Long
Dim Res As Long, obit, oSt, ipc, Tot As Long, oPst As Long, sNum As String
Ray = Application.Transpose(Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)))
sNum = Join(Evaluate("TRANSPOSE(ROW(" & 1 & ":" & UBound(Ray) & "))"), ",")
For n = 1 To UBound(Ray)
    Tot = Tot + Application.Combin(UBound(Ray), n)
Next n
ReDim Oval(1 To Tot)
ReDim nRay(1 To Tot - UBound(Ray))
Do Until Allnum = sNum
   If c < UBound(Ray) Then
       For n = 1 To UBound(Ray)
             c = c + 1: Oval(c) = n
       Next n
   Else
       For n = 1 To UBound(Ray)
             Res = Res + 1
             obit = Oval(Res)
             oSt = Split(obit, ",")(UBound(Split(obit, ",")))
                For nn = oSt + 1 To UBound(Ray)
                    c = c + 1
                    Allnum = obit & "," & nn
                    Oval(c) = Allnum
                Next nn
         Next n
   End If
Loop
Dim s As Variant, nStr As String
    For oPst = UBound(Ray) + 1 To UBound(Oval)
        For Each s In Split(Oval(oPst), ",")
            nStr = nStr & IIf(nStr = "", Ray(s), "," & Ray(s))
        Next s
            nRay(oPst - UBound(Ray)) = nStr: nStr = ""
  Next oPst
Range("B1").Resize(UBound(nRay)).Value = Application.Transpose(nRay)
End Sub

Silahkan dicoba, semoga bisa membantu!
Komentar ini diminimalkan oleh moderator di situs
sayang skyyang:

Ini luar biasa. Terima kasih, ini sangat membantu saya. Saya sangat berterimakasih.

Hanya beberapa pengamatan yang saya perhatikan setelah membuat kode VB yang Anda berikan adalah bahwa lajang (karena tidak ada istilah yang lebih baik), seperti hanya "Ruby", akan dihilangkan, dan pemesanan (kolom) yang dihasilkan tidak lagi sesuai dengan pemesanan asli dihasilkan dalam grafik animasi Langkah 4.

Apakah Anda memiliki saran cepat tentang bagaimana saya dapat mengubah kode Anda untuk juga menyertakan "lajang" dan untuk mencocokkan urutan yang sama seperti pada Langkah 4? Saya akan mencoba untuk memperdebatkan solusinya tetapi sayangnya saya cukup baru dalam skrip VB.

Terima kasih lagi! Saya sangat menghargainya.

terbaik saya. =)
Komentar ini diminimalkan oleh moderator di situs
Hello, ffuuzz
In this case, you can try the vba code in our article, all possible combinations will be listed into separated columns, please try:
Sub ConnectArr()
'Updateby ExtendOffice
Dim xDValue As Variant
Dim xOutRg As Range
Dim xDictionary As Object
Dim xF As Long
Dim xChar As String
xDValue = Range("A2:A6").Value 'the data range
Set xOutRg = Range("C1") 'output range
xChar = "," 'separator
For xF = 1 To UBound(xDValue)
    Set xDictionary = CreateObject("Scripting.Dictionary")
    xDictionary(0) = "Sets of " & xF
    Call ConnectValue(xDValue, xDictionary, 0, xF, 0, "", xChar)
    xOutRg.Offset(0, xF - 1).Resize(xDictionary.Count).Value = WorksheetFunction.Transpose(xDictionary.Items)
    Set xDictionary = Nothing
Next
End Sub
Sub ConnectValue(ByRef pDValue, ByRef pDictionary, ByRef pLevel, ByVal pMaxLevel, ByVal pIndex, ByVal pValue, ByVal pChar)
Dim xF As Long
If pLevel = pMaxLevel Then
    pDictionary(pDictionary.Count + 1) = pValue
    Exit Sub
End If
For xF = pIndex + 1 To UBound(pDValue)
    If pValue = "" Then
        Call ConnectValue(pDValue, pDictionary, pLevel + 1, pMaxLevel, xF, pDValue(xF, 1), pChar)
    Else
        Call ConnectValue(pDValue, pDictionary, pLevel + 1, pMaxLevel, xF, pValue & pChar & pDValue(xF, 1), pChar)
    End If
Next
End Sub
Komentar ini diminimalkan oleh moderator di situs
sayang skyyang:

Pertama, terima kasih banyak atas solusi kode Anda. Saya bersyukur! =)

Saya menulis balasan kemarin tetapi sistem tampaknya tidak mempostingnya karena alasan yang tidak diketahui. Saya harap yang satu ini bisa lolos.

Kode Anda menghasilkan keluaran yang saya minati. Saya baru saja melakukan beberapa pengamatan dan kemudian sebuah pertanyaan:

1) Kode tidak menghasilkan entri individual saja.
2) Urutan asli yang terlihat pada grafik animasi pada Langkah 4 hilang.

Dari kode Anda apakah ada cara untuk juga memasukkan entri tunggal dan untuk mencerminkan format pemesanan asli dari Langkah 4. Saya agak baru dalam skrip VB.

Sekali lagi, terima kasih banyak atas bantuan Anda yang tak ternilai. Saya sangat menghargainya.

Yang terbaik.
Komentar ini diminimalkan oleh moderator di situs
sayang skyyang:

Terima kasih banyak atas bantuan dan kodenya. Itu sangat berharga dan saya berterima kasih.

Saya relatif baru dalam skrip VB, akibatnya tidak terlalu mahir dalam mengkode bahasa.

Hanya satu atau dua poin:

- Kode yang Anda sarankan tidak menghasilkan entri tunggal (mis. Ruby, atau...)
- Urutan asli seperti yang disorot dalam grafik animasi di Langkah 4 menghilang.

Saya akan memeriksa kode Anda untuk mencoba mengkalibrasinya sehingga poin-poin di atas dikeluarkan, tetapi saya ingin tahu apakah Anda memiliki saran atau saran cepat yang dapat mengatasinya.

Terima kasih sekali lagi atas bantuan Anda yang baik. Saya sangat menghargainya. =)

Yang terbaik.
Belum ada komentar yang diposting di sini
Tinggalkan komentar anda
Posting sebagai Tamu
×
Beri peringkat pos ini:
0   Karakter
Lokasi yang Disarankan

Ikuti kami

Hak Cipta © 2009 - www.extendoffice.com. | Seluruh hak cipta. Dipersembahkan oleh ExtendOffice. | Peta Situs
Microsoft dan logo Office adalah merek dagang atau merek dagang terdaftar dari Microsoft Corporation di Amerika Serikat dan / atau negara lain.
Dilindungi oleh Sectigo SSL