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

or

Bagaimana hendak melihat dan menggabungkan beberapa nilai yang sepadan dalam Excel?

Seperti yang kita semua ketahui, yang Vlookup fungsi dalam Excel dapat membantu kita mencari sesuatu nilai dan mengembalikan data yang sama dalam lajur yang lain, tetapi secara umum, ia hanya boleh mendapatkan nilai relatif pertama jika terdapat banyak data yang hampir sama. Dalam artikel ini, saya akan bercakap tentang bagaimana untuk melihat dan menggabungkan beberapa nilai yang sama dalam hanya satu sel atau senarai menegak.

Vlookup dan mengembalikan beberapa nilai padanan secara menegak dengan formula

Vlookup dan concatenate multiple matching values ​​in a cell dengan User Defined Function

Vlookup dan concatenate multiple matching values ​​in a cell dengan Kutools for Excel


Vlookup dan concatenate multiple matching values ​​in a cell:

dengan Kutools untuk Excel'S Advanced Combine Rows Oleh itu, anda boleh dengan cepat menggabungkan beberapa baris pendua ke dalam satu rekod berdasarkan pada lajur utama, dan ia juga boleh memohon beberapa pengiraan seperti jumlah, purata, kiraan dan sebagainya untuk lajur yang lain.

  • 1. Tentukan lajur utama yang anda ingin gabungkan lajur yang lain berdasarkan;
  • 2. Pilih pemisah untuk data gabungan anda.

doc menggabungkan teks berdasarkan kriteria 10

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



Supaya, saya mempunyai pelbagai data berikut, untuk mendapatkan semua nilai yang bersamaan berdasarkan nilai khusus secara menegak seperti tangkapan skrin berikut, anda boleh menggunakan formula tatasusunan.

doc vlookup concatenate 1

1. Masukkan formula ini: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") ke dalam sel kosong di mana anda mahu meletakkan hasilnya, sebagai contoh, E2, dan kemudian tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan asas nilai relatif pada kriteria tertentu, lihat tangkapan skrin:

doc vlookup concatenate 2

Nota: Dalam formula di atas:

A1: A16 adalah julat lajur yang mengandungi nilai khusus yang anda mahu cari;

D2 menunjukkan nilai khusus yang ingin anda lihat;

B1: B16 adalah julat lajur yang anda mahu mengembalikan data yang sepadan dari;

$ 1: $ 16 menunjukkan rujukan baris dalam julat.

2. Kemudian pilih sel E2, dan seret pemegang pengisian ke sel sehingga anda mendapat sel kosong, dan semua nilai yang sepadan disenaraikan ke dalam lajur seperti tangkapan skrin berikut:

doc vlookup concatenate 3


Daripada mendapatkan nilai relatif secara menegak, kadang-kadang, anda mahu nilai-nilai yang sepadan ke dalam satu sel dan menggabungkan mereka dengan pemisah tertentu. Dalam kes ini, Fungsi Yang Dihakimi Pengguna berikut mungkin memberi sokongan kepada anda.

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: Vlookup dan concatenate multiple matching values ​​in a cell

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice 20151118
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
    If x = lookupval Then
        result = result & " " & x.Offset(0, indexcol - 1)
    End If
Next x
CusVlookup = result
End Function

3. Kemudian simpan dan tutup kod ini, kembali ke lembaran kerja, dan masukkan formula ini: = cusvlookup (D2, A1: B16,2) ke dalam sel kosong di mana anda mahu meletakkan hasilnya, dan tekan Masukkan kunci, semua nilai yang bersamaan berdasarkan data tertentu telah dikembalikan ke dalam satu sel dengan pemisah ruang, lihat tangkapan skrin:

doc vlookup concatenate 4

Nota: Dalam formula di atas: D2 menunjukkan nilai sel yang anda mahu tengok, A1: B16 adalah julat data yang anda mahu ambil data, nombor itu 2 adalah nombor lajur yang nilai yang sepadan akan dipulangkan dari, anda boleh menukar tesis rujukan kepada keperluan anda.


Jika anda mempunyai Kutools untuk Excel, Dengan itu Advanced Combine Rows ciri, anda dengan cepat dapat menyelesaikan tugas ini dengan mudah. Ciri ini boleh membantu anda menggabungkan semua nilai yang sepadan dengan penentu tertentu berdasarkan data yang sama dalam lajur yang lain.

Kutools untuk Excel : dengan lebih daripada 300 berguna tambahan Excel, bebas untuk mencuba tanpa had pada hari 60.

Selepas memasang Kutools untuk Excel, sila lakukan seperti berikut:

1. Pilih julat data yang anda mahu dapatkan nilai-nilai yang sesuai berdasarkan data tertentu.

2. Kemudian klik Kutools > Kandungan > Advanced Combine Rows, lihat tangkapan skrin:

doc vlookup concatenate 5

3. Di dalam Advanced Combine Rows kotak dialog, klik nama lajur yang anda ingin gabungkan berdasarkan, kemudian klik Kunci utama butang, lihat tangkapan skrin:

doc vlookup concatenate 6

4. Kemudian klik nama lajur lain yang anda ingin mengembalikan nilai yang dipadankan, dan klik Gabungkan untuk memilih satu pemisah untuk memisahkan nilai gabungan, lihat tangkapan skrin:

doc vlookup concatenate 7

5. Kemudian klik Ok butang, semua nilai yang sama berdasarkan nilai yang sama telah digabungkan bersama dengan pemisah tertentu, lihat tangkapan skrin:

doc vlookup concatenate 8  2 doc vlookup concatenate 9

Muat turun dan percubaan percuma Kutools untuk Excel Sekarang!


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


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.
    Russell · 2 months ago
    This is working great for me - is there anyway to change it that it checks if the cell contains rather than a complete match? Basically I have a list of tasks where:
    Column A: Dependencies (eg 10003 10004 10008)
    Column B: Task Reference (eg 10001)
    Column C: Dependent Tasks (the column for the formula result) - where it would lookup the task reference to see which rows contain it in Column A, and then list the Task Reference of those tasks.

    E.g:

    Row | Column A | Column B | Column C
    1 | | 10001 | 10002 10003
    2 | 10001 | 10002 | 10003
    3 | 10001 10002 | 10003 |
  • To post as a guest, your comment is unpublished.
    jeff · 3 months ago
    The cusVlookup worked great for me. Another way to have a different separator is to wrap in two substitute functions. The first (from inside to out) replaces the first space with no space, the second replaces all other spaces with a " / " in mine. Could use "," if you want commas.
    =SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,2)," ","",1)," "," / ")

    Also, if your lookup value isn't the first column, you can use 0 or negative numbers to go to column to the left.
    =SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,-1)," ","",1)," "," / ")
    • To post as a guest, your comment is unpublished.
      skyyang · 2 months ago
      Hi, jeff,
      Thanks for your sharing, you must be a warmhearted man.
  • To post as a guest, your comment is unpublished.
    Dana Rohde · 11 months ago
    I have to say, I have been trying to get a formula for combining multiple values and returning them to a single cell for 2 days now. This "How To" has saved me!! Thank you SO much! I would never have gotten it without your Module!
    I do have 2 questions though. I have the deliminator as a comma instead of a space and because of that it starts out with a comma. Is there a way to prevent the start comma but keep the rest?
    My second question is; When I use the fill handle it changes the range values as well as the cell value I want to look up. I want it to continue to change the cell number I want to look up but keep the same range values. How can I make this happen?

    Thank you so much for your help!!
  • To post as a guest, your comment is unpublished.
    Jacob Nelson · 1 years ago
    Is there a way to delete the duplicate values in the concatenate?
  • To post as a guest, your comment is unpublished.
    Sree Kanth · 1 years ago
    This is amazing but i am looking for something else, i have a table with RollNo StudentName sub1, sub2, sub3 ... Total Result, When I enter Rollnumber it should give a result like "SName Sub1 64, sub2 78,... Total 389, Result pass", is it possible