Pertanyaan Gabungkan 2 tabel Excel ke dalam satu menambahkan data?


Saya memiliki 2 tabel pada 2 lembar terpisah dari buku kerja MS Excel 2007, seperti di bawah ini:

===========================
no.   f_name     l_name  
===========================
13   Little     Timmy
1   John       Doe
17   Baby       Jessica
---------------------------


===========================
no.   f_name     l_name  
===========================
1   john       Tim
16   kyle       joe
14   Baby       katy
22   qbcd       wsde
---------------------------

Keduanya memiliki kolom yang sama, tetapi mereka dapat memiliki data yang berbeda.

Saya ingin menggabungkan data dari kedua tabel secara vertikal yaitu satu tabel dengan semua data di lembar terpisah ke-3. Jika memungkinkan, saya ingin menambahkan kolom lain dengan nama sheet dari mana baris itu datang.

===================================
SheetName   no.   f_name     l_name  
===================================
Sheet1      13   Little     Timmy
Sheet1      1   John       Doe
Sheet1      17   Baby       Jessica
Sheet2      1   john       Tim
Sheet2      16   kyle       joe
Sheet2      14   Baby       katy
Sheet2      22   qbcd       wsde
-----------------------------------

Bisakah itu dilakukan tanpa menggunakan macro?


9
2018-03-29 10:16


asal


Jawaban:


Jawaban ini berhubungan dengan Tabel Terstruktur sebagaimana ditafsirkan oleh Excel. Sementara metode dapat dengan mudah ditranskripsikan ke matriks data mentah tanpa struktur tabel yang ditetapkan, rumus dan pengkodean VBA untuk solusi ini akan ditargetkan pada tabel terstruktur yang sebenarnya.

Pembukaan

Tabel ketiga dapat mempertahankan data gabungan dari dua tabel dengan beberapa rumus lembar kerja asli tetapi menjaga tabel ketiga berukuran dengan benar saat baris ditambahkan atau dihapus ke / dari tabel dependen akan memerlukan operasi pengubahan ukuran manual atau beberapa VBA yang melacak perubahan ini dan menyesuaikan meja ketiga yang sesuai. Saya telah menyertakan opsi untuk menambahkan kedua nama lembar kerja tabel sumber serta beberapa kode VBA pemeliharaan tabel di akhir jawaban ini.

Jika semua yang Anda inginkan adalah buku kerja contoh operasional tanpa semua penjelasan, lompat ke akhir jawaban ini untuk tautan ke buku kerja yang digunakan untuk membuat prosedur ini.

Tabel data sampel

Table Collection Sample Data

Saya telah menggunakan data sampel OP untuk membangun dua tabel bernama (secara default) Tabel 1 dan Tabel 2 di lembar kerja Sheet1 dan Sheet2 masing-masing. Saya sengaja mengimbangi mereka dengan berbagai derajat dari masing-masing sel A1 worksheet untuk menunjukkan kemampuan tabel terstruktur untuk mengatasi baik itu sendiri atau tabel terstruktur lainnya dalam rumus sebagai entitas terpisah terlepas dari posisinya di lembar kerja orang tua. Tabel ketiga akan dibangun dengan cara yang sama. Offset ini hanya untuk tujuan demonstrasi; mereka tidak diperlukan.

Langkah 1: Buat tabel ketiga

Buat header untuk tabel ketiga dan pilih baris header di masa depan dan setidaknya satu baris di bawahnya untuk mendasari Sisipkan ► Tabel ► Perintah tabel.

Combining Tables New Table

Tabel ketiga kosong Anda yang baru pada lembar kerja Sheet3 harus menyerupai berikut ini.

Collecting Table Data Build New Table

Langkah 2: Isi tabel ketiga

Mulailah dengan mengisi sel pertama di tabel ketiga DataBodyRange. Dalam contoh ini, itu akan menjadi Sheet3! C6. Ketik atau tempelkan rumus berikut di C6 dengan mengingat bahwa ini didasarkan pada nama tabel default. Jika Anda telah mengubah nama tabel Anda, sesuaikan dengan sesuai.

=IFERROR(INDEX(Table1, ROW([@[no.]])-ROW(Table3[#Headers]),COLUMN(A:A)), INDEX(Table2, ROW([@[no.]])-ROW(Table3[#Headers])-ROWS(Table1),COLUMN(A:A)))

Itu Fungsi INDEX pertama mengambil setiap baris yang tersedia dari Tabel 1. Nomor baris aktual berasal dengan Fungsi ROW referensi potongan-potongan tertentu dari tabel terstruktur bersama dengan sedikit matematika. Ketika Table1 kehabisan baris, retrieval diteruskan ke referensi fungsi INDEX kedua Meja 2 oleh Fungsi IFERROR dan baris berurutannya diambil dengan ROW dan Fungsi ROWS menggunakan sedikit lebih banyak matematika. Itu Fungsi COLUMN digunakan sebagai COLUMN(A:A) yang akan mengambil kolom pertama dari tabel yang direferensikan di mana pun berada di lembar kerja. Ini akan berlanjut ke kolom kedua, ketiga, dll. Karena formula diisi dengan benar.

Berbicara tentang pengisian yang benar, isi formula tepat ke E6. Anda harus memiliki sesuatu yang mendekati yang berikut.

Aggregating table data, third table

Langkah 2.5: [opsional] Tambahkan nama lembar kerja induk tabel sumber

Genggam pegangan Grab Table3 (ditunjukkan oleh panah oranye pada gambar contoh di bawah) di sudut kanan bawah dan tarik ke kanan satu kolom untuk menambahkan kolom baru ke tabel. Ganti nama label header menjadi sesuatu yang lebih sesuai daripada default. Saya telah menggunakan Lembar sebagai label kolom.

Collating table data - source worksheet name

Meskipun Anda tidak dapat mengambil nama lembar kerja dari tabel sumber secara langsung, Fungsi SEL dapat mengambil jalur, nama file, dan lembar kerja yang sepenuhnya memenuhi syarat dari sel mana pun dalam buku kerja yang disimpan¹ sebagai salah satu pilihannya info_types.

Masukkan rumus berikut ke dalam sel kosong Table3 di baris pertama dari kolom baru yang baru Anda buat.

=TRIM(RIGHT(SUBSTITUTE(CELL("filename", IF((ROW([@[no.]])-ROW(Table3[#Headers]))>ROWS(Table1), Table2, Table1)), CHAR(93), REPT(CHAR(32), 999)), 255))

Complate menghasilkan Tabel3

Jika Anda tidak berencana menyelesaikan proyek kecil ini dengan beberapa VBA untuk mempertahankan dimensi Table3 ketika baris ditambahkan atau dihapus dari salah satu dari dua tabel sumber, cukup ambil pengalih ukuran Table3 dan seret ke bawah sampai Anda telah mengumpulkan semua data dari kedua tabel . Lihat bagian bawah jawaban ini untuk gambar sampel dari hasil yang diharapkan.

Jika Anda berencana untuk menambahkan beberapa VBA, lewati seluruh populasi Table3 dan lanjutkan ke langkah berikutnya.

Langkah 3: Tambahkan beberapa VBA untuk mempertahankan tabel ketiga

Otomatisasi penuh dari suatu proses yang dipicu oleh perubahan pada data lembar kerja paling baik ditangani oleh lembar kerja Worksheet_Change makro acara. Karena ada tiga tabel yang terlibat, masing-masing pada lembar kerja mereka sendiri, Workbook_SheetChange event macro adalah metode yang lebih baik untuk menangani peristiwa perubahan dari beberapa lembar kerja.

Buka VBE dengan Alt+F11. Setelah Anda membukanya, cari Project Explorer di kiri atas. Jika tidak terlihat, ketuk Ctrl+R untuk membukanya. Menemukan ThisWorkbook dan klik kanan lalu pilih Lihat Kode (atau cukup klik ganda ThisWorkbook).

Collect data from multiple tables

Tempelkan yang berikut ke dalam panel baru dengan judul seperti Book1 - ThisWorkbook (Kode).

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Select Case Sh.Name
        Case Sheet1.Name
            If Not Intersect(Target, Sheet1.ListObjects("Table1").Range.Offset(1, 0)) Is Nothing Then
                On Error GoTo bm_Safe_Exit
                Application.EnableEvents = False
                Call update_Table3
            End If
        Case Sheet2.Name
            If Not Intersect(Target, Sheet2.ListObjects("Table2").Range.Offset(1, 0)) Is Nothing Then
                On Error GoTo bm_Safe_Exit
                Application.EnableEvents = False
                Call update_Table3
            End If
    End Select

bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

Private Sub update_Table3()
    Dim iTBL3rws As Long, rng As Range, rngOLDBDY As Range
    iTBL3rws = Sheet1.ListObjects("Table1").DataBodyRange.Rows.Count
    iTBL3rws = iTBL3rws + Sheet2.ListObjects("Table2").DataBodyRange.Rows.Count
    iTBL3rws = iTBL3rws + Sheet3.ListObjects("Table3").DataBodyRange.Cells(1, 1).Row - _
                          Sheet3.ListObjects("Table3").Range.Cells(1, 1).Row
    With Sheet3.ListObjects("Table3")
        Set rngOLDBDY = .DataBodyRange
        .Resize .Range.Cells(1, 1).Resize(iTBL3rws, .DataBodyRange.Columns.Count)
        If rngOLDBDY.Rows.Count > .DataBodyRange.Rows.Count Then
            For Each rng In rngOLDBDY
                If Intersect(rng, .DataBodyRange) Is Nothing Then
                    rng.Clear
                End If
            Next rng
        End If
    End With
End Sub

Kedua rutin ini memanfaatkan ekstensif Worksheet Properti .CodeName. Lembar kerja CodeName aku s Sheet1, Sheet2, Sheet3, dll dan tidak berubah ketika lembar kerja diganti namanya. Bahkan, mereka jarang diubah oleh bahkan pengguna yang lebih maju. Mereka telah digunakan sehingga Anda dapat mengganti nama lembar kerja Anda tanpa harus memodifikasi kode. Namun, mereka harus menunjuk ke worksheets yang benar sekarang. Ubah kode jika tabel dan lembar kerja Anda tidak sama dengan yang diberikan. Anda dapat melihat nama kolom worksheet individu di dalam tanda kurung di samping lembar kerja mereka .Nama properti dalam gambar di atas menunjukkan Project Explorer VBE.

Keran Alt+Q untuk kembali ke lembar kerja Anda. Semua yang tersisa adalah menyelesaikan populasi Table3 dengan memilih sel mana pun di Tabel 1 atau Meja 2 dan pura-pura memodifikasinya dengan mengetuk F2 kemudian Enter↵. Hasil Anda harus menyerupai berikut ini.

Combine two tables into one automatically

Jika Anda telah mengikuti sepanjang jalan ke sini maka Anda harus memiliki koleksi tabel yang cukup komprehensif yang secara aktif menggabungkan data dari dua tabel sumber 'anak'. Jika Anda menambahkan VBA juga maka pemeliharaan tabel koleksi ketiga hampir tidak ada.

Mengganti nama tabel

Jika Anda memilih untuk mengganti salah satu atau semua dari tiga tabel, rumus lembar kerja akan langsung dan secara otomatis mencerminkan perubahan. Jika Anda memilih untuk menyertakan Workbook_SheetChange dan menemani sub prosedur pembantu, Anda harus kembali ke lembar kode ThisWorkbook dan menggunakan Cari & Ganti untuk membuat perubahan yang sesuai.

Buku Contoh Workbook

Saya telah membuat contoh buku kerja operasional penuh yang tersedia dari DropBox publik saya.

Table_Collection_w_Sheetname.xlsb


¹ Itu Fungsi SEL hanya dapat mengambil nama lembar kerja dari buku kerja yang disimpan. Jika buku kerja belum disimpan maka tidak memiliki nama file dan fungsi SEL akan mengembalikan string kosong ketika diminta untuk nama file.


7
2017-09-20 14:52



Anda dapat mengaktifkan Clipboard Kantor (panah di kanan bawah bagian clipboard di Ribbon Home Tab). Salin kedua rentang lalu gunakan Tempel Semua perintah seperti yang ditunjukkan di bawah ini.

Anda masih perlu mengisi nama sheet di kolom ekstra terlebih dahulu, meskipun yang dapat dilakukan dengan mengklik dua kali pada gagang isian.

enter image description here

Memperbarui

Untuk mendapatkan hasil yang sama dengan rumus, coba isi ulang ini untuk nama sheet:

=IF(ROW()<=COUNTA(Sheet1!A:A),"Sheet1",IF(ROW()<COUNTA(Sheet1:Sheet2!A:A),"Sheet2",""))

dan kemudian mengisi dan melintasi rumus ini untuk nilai-nilai dalam tabel:

=IF(ROW()<=COUNTA(Sheet1!A:A),Sheet1!A2,IF(ROW()<COUNTA(Sheet1:Sheet2!A:A),INDEX(Sheet2!A:A,ROW()-COUNTA(Sheet1!A:A)+1),""))

3
2018-03-29 12:58



lori_m membuat kontribusi yang sangat bagus yang saya bangun dengan menggunakan Microsoft Excel Tables dan referensi terstruktur.

Pertama buat kolom di tabel output Anda yang disebut RowID yang berisi nomor baris dalam tabel dan kemudian gunakan ini untuk mengisi nilai data.

=IF( INDIRECT("Table3[RowId]")<=ROWS(Table1)
    ,INDEX(Table1[column1],INDIRECT("Table3[RowId]"))
    ,INDEX(Table2[Column1],INDIRECT("Table3[RowId]")-ROWS(Table1)))

Ada sebuah penjelasan terperinci tentang bagaimana ini bekerja di blog saya karena terlalu panjang untuk disertakan di sini.


1
2018-03-15 18:28



Sedikit modifikasi pada kode Jeeped.

Jika Anda kebetulan menggunakan pendekatan serupa, tetapi dengan beberapa tabel (misalnya lebih dari 10), maka akan agak rumit untuk mencoba menambahkan setiap nama setiap tabel secara manual. Ini juga masalah jika Anda mengubah nama tabel, karena nama-nama itu terprogram dalam VBA. Untuk menghindari pekerjaan tambahan, pertimbangkan ini:

Jadi, asumsikan hal-hal berikut:

  • Pada setiap lembar kerja ada satu atau beberapa tabel, tetapi mereka memiliki struktur yang serupa.
  • Hanya ada tabel di lembar kerja - tidak ada anggota lain dari koleksi ListObjects yang hadir.
  • Setiap kali kita mengedit tabel pada selembar, ini akan memicu pembaruan di tabel master (tabel 3).

Maka itu Workbook_SheetChange Sub pada contoh di atas dapat terlihat seperti berikut:

     Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
     Dim tbl As ListObject
     For Each tbl In ActiveSheet.ListObjects
        If Not Intersect(Target, tbl.Range.Offset(1, 0)) Is Nothing Then
            On Error GoTo bm_Safe_Exit
            Application.EnableEvents = False
            Call update_Table
        End If
        Next tbl
        bm_Safe_Exit:
        Application.EnableEvents = True
     End Sub

Edit. Rutinitas kedua kemudian akan terlihat seperti:

   Private Sub update_Table()
    Dim iTBL3rws As Long, rng As Range, rngOLDBDY As Range
    Dim tbl As ListObject
    Dim sht As Worksheet
    iTBL3rws = 0

    ' consider all tables, excluding master table
    For Each sht In ThisWorkbook.Worksheets
        For Each tbl In sht.ListObjects
            If tbl.Name <> "Table3" Then
                iTBL3rws = iTBL3rws + tbl.DataBodyRange.Rows.Count
            End If
        Next tbl
    Next sht

    iTBL3rws = iTBL3rws + Sheet3.ListObjects("Table3").DataBodyRange.Cells(1, 1).Row - Sheet3.ListObjects("Table3").Range.Cells(1, 1).Row
        With Sheet3.ListObjects("Table3")

            Set rngOLDBDY = .DataBodyRange

            .Resize .Range.Cells(1, 1).Resize(iTBL3rws, .DataBodyRange.Columns.Count)

            If rngOLDBDY.Rows.Count > .DataBodyRange.Rows.Count Then
                For Each rng In rngOLDBDY
                    If Intersect(rng, .DataBodyRange) Is Nothing Then
                        rng.Clear
                    End If
                Next rng
            End If
        End With

End Sub

Rutinitas ini berbeda dari sebelumnya dengan menghilangkan kasus yang terprogram. Ketika ada perubahan terdaftar di lembar kerja aktif, maka setiap tabel di lembar kerja ini yang akan diubah akan memicu update_Table prosedur.


0
2017-10-29 22:31



Saya menggunakan kode / rumus ini. bekerja dengan baik untuk kebutuhan saya saja yang saya ingin tahu adalah bagaimana saya membuat formula sel yang lebih baik sehingga saya dapat menggunakan 3+ tabel sebagai referensi. Saat ini im hanya bersarang sekelompok pernyataan iferror dalam iferror

=IFERROR(INDEX(Table1, ROW([@Date])-ROW(Table3[#Headers]),COLUMN(A:A)),IFERROR( INDEX(Table2, ROW([@Date])-ROW(Table3[#Headers])-ROWS(Table1),COLUMN(A:A)), IFERROR(INDEX(Table4, ROW([@Date])-ROW(Table3[#Headers])-ROWS(Table2)-ROWS(Table1),COLUMN(A:A)),INDEX(Table5, ROW([@Date])-ROW(Table3[#Headers])-ROWS(Table2)-ROWS(Table1)-ROWS(Table4),COLUMN(A:A)))))

Saya juga menggunakan


0
2018-01-30 15:05