Loncat ke daftar isi utama

Di Excel, merupakan skenario umum untuk menemukan kumpulan data dengan entri duplikat. Seringkali, Anda mungkin menemukan serangkaian data yang tantangan utamanya adalah menggabungkan baris duplikat ini secara efisien sekaligus menjumlahkan nilai dalam kolom terkait seperti gambar layar berikut yang ditampilkan. Dalam konteks ini, kami akan mempelajari beberapa metode praktis yang dapat membantu Anda mengkonsolidasikan data duplikat dan mengagregasi nilai terkaitnya, sehingga meningkatkan kejelasan dan kegunaan buku kerja Excel Anda.


Gabungkan baris duplikat dan jumlahkan nilainya dengan fungsi Konsolidasi

Konsolidasi adalah alat yang berguna bagi kita untuk menggabungkan beberapa lembar kerja atau baris di Excel, dengan fitur ini, kita dapat menggabungkan baris duplikat dan menjumlahkan nilainya dengan cepat dan mudah. Silakan lakukan dengan langkah-langkah berikut:

Langkah 1: Pilih Sel Tujuan

Pilih di mana Anda ingin data konsolidasi muncul.

Langkah 2: Akses Fungsi Konsolidasi dan atur konsolidasi

  1. Klik Data > Mengkonsolidasikan, lihat tangkapan layar:
  2. Dalam majalah Mengkonsolidasikan kotak dialog:
    • (1.) Pilih Jumlah dari fungsi daftar drop-down;
    • (2.) Klik untuk memilih rentang yang ingin Anda gabungkan Referensi kotak;
    • (3.) Periksa Baris teratas dan Kolom kiri dari Gunakan label dalam pilihan;
    • (4.) Terakhir, klik OK .

Hasil:

Excel akan menggabungkan setiap duplikat yang ditemukan di kolom pertama dan menjumlahkan nilainya yang sesuai di kolom yang berdekatan seperti gambar berikut yang ditampilkan:

Catatan:
  • Jika rentang tidak menyertakan baris header, pastikan untuk menyertakannya hapus centang Baris atas dari Gunakan label dalam .
  • Dengan fitur ini, penghitungan hanya dapat digabungkan berdasarkan kolom pertama (paling kiri) data.

Gabungkan baris duplikat dan jumlahkan nilainya dengan fitur canggih – Kutools

Jika sudah terpasang Kutools untuk Excel, nya Lanjutan Gabungkan Baris Fitur ini memungkinkan Anda menggabungkan baris duplikat dengan mudah, memberikan opsi untuk menjumlahkan, menghitung, rata-rata, atau menjalankan penghitungan lain pada data Anda. Selain itu, fitur ini tidak terbatas pada satu kolom kunci saja, fitur ini dapat menangani beberapa kolom kunci, sehingga membuat tugas konsolidasi data yang kompleks menjadi lebih mudah.

Note: Jika Anda ingin menggunakan ini Lanjutan Gabungkan Baris fitur, silakan unduh dan instal Kutools untuk Excel pertama.

Setelah menginstal Kutools untuk Excel, pilih rentang data, lalu klik Kutools > Gabungkan & Pisahkan > Lanjutan Gabungkan Baris.

Dalam majalah Lanjutan Gabungkan Baris kotak dialog, setel operasi berikut:

  1. Klik nama kolom yang ingin Anda gabungkan duplikatnya, di sini saya akan klik Produk, lalu pilih Kunci utama dari daftar tarik-turun di Operasi kolom;
  2. Lalu, pilih nama kolom yang ingin Anda jumlahkan nilainya, lalu pilih Jumlah dari daftar tarik-turun di Operasi kolom;
  3. Sedangkan untuk kolom lainnya, Anda dapat memilih operasi yang Anda perlukan, seperti menggabungkan nilai dengan pemisah tertentu atau melakukan penghitungan tertentu; (langkah ini dapat diabaikan jika Anda hanya memiliki dua kolom)
  4. Terakhir, Anda dapat melihat pratinjau hasil gabungan lalu klik OK .

Hasil:

Sekarang, nilai duplikat di kolom kunci digabungkan, dan nilai terkait lainnya diringkas seperti gambar berikut yang ditampilkan:

Tip:
  • Dengan fitur berguna ini, Anda juga dapat menggabungkan baris berdasarkan nilai sel duplikat seperti yang ditunjukkan demo berikut:
  • Fitur ini mendukung Batalkan, jika Anda ingin memulihkan data asli Anda, cukup tekan Ctrl + Z.
  • Untuk menerapkan fitur ini, silakan unduh dan instal Kutools untuk Excel pertama.

Gabungkan baris duplikat dan jumlahkan nilainya dengan Tabel Pivot

Tabel Pivot di Excel menyediakan cara dinamis untuk mengatur ulang, mengelompokkan, dan meringkas data. Fungsionalitas ini menjadi sangat berguna ketika Anda dihadapkan dengan kumpulan data yang berisi entri duplikat dan perlu menjumlahkan nilai yang sesuai.

Langkah 1: Membuat Tabel Pivot

  1. Pilih rentang data. Lalu, pergi ke Menyisipkan tab, dan klik Tabel pivot, lihat tangkapan layar:
  2. Di kotak dialog yang muncul, pilih di mana Anda ingin menempatkan laporan Tabel Pivot, Anda dapat meletakkannya di lembar baru atau lembar yang sudah ada sesuai kebutuhan. Lalu klik OK. Lihat tangkapan layar:
  3. Sekarang, Tabel Pivot disisipkan di sel tujuan yang dipilih. Lihat tangkapan layar:

Langkah 2: Mengonfigurasi Tabel Pivot:

  1. Dalam majalah Bidang PivotTable panel, seret bidang yang berisi duplikat ke Baris daerah. Ini akan mengelompokkan duplikat Anda.
  2. Selanjutnya, seret bidang dengan nilai yang ingin Anda jumlahkan Nilai - Nilai daerah. Secara default, Excel menjumlahkan nilainya. Lihat demonya di bawah ini:

Hasil:

Tabel Pivot sekarang menampilkan data Anda dengan gabungan duplikat dan nilainya diringkas, menawarkan tampilan analisis yang jelas dan ringkas. Lihat tangkapan layar:


Gabungkan baris duplikat dan jumlahkan nilainya dengan kode VBA

Jika Anda tertarik dengan kode VBA, di bagian ini, kami akan memberikan kode VBA untuk menggabungkan baris duplikat dan menjumlahkan nilai terkait di kolom lain. Silakan lakukan dengan langkah-langkah berikut:

Langkah 1: Buka editor modul lembar VBA dan salin kodenya

  1. Tahan ALT + F11 kunci di Excel untuk membuka file Microsoft Visual Basic untuk Aplikasi jendela.
  2. Klik Menyisipkan > Modul, dan tempel kode berikut di Modul Jendela.
    Kode VBA: Gabungkan baris duplikat dan jumlahkan nilainya
    Sub CombineDuplicateRowsAndSumForMultipleColumns()
    'Update by Extendoffice
        Dim SourceRange As Range, OutputRange As Range
        Dim Dict As Object
        Dim DataArray As Variant
        Dim i As Long, j As Long
        Dim Key As Variant
        Dim ColCount As Long
        Dim SumArray() As Variant
        Dim xArr As Variant
        Set SourceRange = Application.InputBox("Select the original range:", "Kutools for Excel", Type:=8)
        If SourceRange Is Nothing Then Exit Sub
        ColCount = SourceRange.Columns.Count
        Set OutputRange = Application.InputBox("Select a cell for output:", "Kutools for Excel", Type:=8)
        If OutputRange Is Nothing Then Exit Sub
        Set Dict = CreateObject("Scripting.Dictionary")
        DataArray = SourceRange.Value
        For i = 1 To UBound(DataArray, 1)
            Key = DataArray(i, 1)
            If Not Dict.Exists(Key) Then
                ReDim SumArray(1 To ColCount - 1)
                For j = 2 To ColCount
                    SumArray(j - 1) = DataArray(i, j)
                Next j
                Dict.Add Key, SumArray
            Else
                xArr = Dict(Key)
                For j = 2 To ColCount
                    xArr(j - 1) = xArr(j - 1) + DataArray(i, j)
                Next j
                Dict(Key) = xArr
            End If
        Next i
        OutputRange.Resize(Dict.Count, ColCount).ClearContents
        i = 1
        For Each Key In Dict.Keys
            OutputRange.Cells(i, 1).Value = Key
            For j = 1 To ColCount - 1
                OutputRange.Cells(i, j + 1).Value = Dict(Key)(j)
            Next j
            i = i + 1
        Next Key
        Set Dict = Nothing
        Set SourceRange = Nothing
        Set OutputRange = Nothing
    End Sub
    

Langkah 2: Jalankan kodenya

  1. Setelah menempelkan kode ini, harap tekan F5 kunci untuk menjalankan kode ini. Di kotak prompt, pilih rentang data yang ingin Anda gabungkan dan jumlahkan. Lalu, klik OK.
  2. Dan di kotak prompt berikutnya, pilih sel tempat Anda akan menampilkan hasilnya, dan klik OK.

Hasil:

Sekarang, baris duplikat digabungkan, dan nilai terkaitnya telah dijumlahkan. Lihat tangkapan layar:


Menggabungkan dan menjumlahkan baris duplikat di Excel bisa menjadi sederhana dan efisien. Pilih dari fungsi Konsolidasi yang mudah, Kutools tingkat lanjut, Tabel Pivot analitis, atau pengkodean VBA yang fleksibel untuk menemukan solusi yang sesuai dengan keahlian dan kebutuhan Anda. Jika Anda tertarik untuk menjelajahi tips dan trik Excel lainnya, situs web kami menawarkan ribuan tutorial klik di sini untuk mengaksesnya. Terima kasih telah membaca, dan kami berharap dapat memberi Anda informasi bermanfaat lainnya di masa mendatang!


Artikel Terkait:

  • Gabungkan beberapa baris menjadi satu berdasarkan duplikat
  • Mungkin, Anda memiliki rentang data, di kolom Nama Produk A, ada beberapa item duplikat, dan sekarang Anda perlu menghapus entri duplikat di kolom A tetapi menggabungkan nilai yang sesuai di kolom B. Bagaimana cara menyelesaikan tugas ini di Excel ?
  • Vlookup dan mengembalikan beberapa nilai tanpa duplikat
  • Terkadang, Anda mungkin ingin vlookup dan mengembalikan beberapa nilai yang cocok ke dalam satu sel sekaligus. Tetapi, jika ada beberapa nilai berulang yang diisi ke dalam sel yang dikembalikan, bagaimana Anda bisa mengabaikan duplikat dan hanya menyimpan nilai unik saat mengembalikan semua nilai yang cocok seperti gambar layar berikut yang ditampilkan di Excel?
  • Gabungkan baris dengan ID/nama yang sama
  • Misalnya, Anda memiliki tabel seperti gambar di bawah ini yang ditampilkan, dan Anda perlu menggabungkan baris dengan ID pesanan, ada ide? Di sini, artikel ini akan memperkenalkan dua solusi untuk Anda.
Comments (30)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Em planilha sem cálculo número, onde eu tenho uma lista de prestadores de serviço para determinadas empresas, como faço para deixar classificado por empresas, por ordem de empresas? Se é possível.
Na planilha eu tenho, o nome da pessoa, razão social e empresa. Neste caso algumas empresas repetem, gostaria de classificar automático, sem precisar refazer um por um.
This comment was minimized by the moderator on the site
is there a way to save the specific merging and combining settings so that i can reuse them for future workbooks?
This comment was minimized by the moderator on the site
Thanks for your help.

This comment was minimized by the moderator on the site
SN SAD No Unit Item No Description Qty CIF_Value ID_EXD AID CSF ARF ECS RCF RDF IFT IDP AIT VAT
1 M200 UNT 1 Pen 194 500 50 0 0 0 0 0 0 0 0 0 65
2 M200 UNT 2 Pencil 241 250 25 0 0 0 0 0 0 0 0 0 32.5
3 M200 UNT 3 Cutter 204 400 40 0 0 0 0 0 0 0 0 0 52
4 M200 UNT 4 Copy 171 600 60 0 0 0 0 0 0 0 0 0 78
5 M300 KGM 1 Cup 220 250 25 0 500 0 0 0 0 0 0 0 32.5
6 M300 KGM 2 Plate 40 350 35 155 0 0 0 0 0 0 0 0 45.5
7 M300 UNT 3 Bottle 2 150 15 131 0 0 0 0 0 0 0 0 19.5
8 M300 UNT 4 Glass 2 90 9 34 0 0 0 0 0 0 0 0 11.7
9 M400 null 1 Shirt 20 800 80 0 0 0 0 0 0 0 0 0 104
10 M400 KGM 2 Pant 5 5000 500 0 0 0 0 0 0 0 0 0 650
11 M400 null 3 Shoe 12 7200 720 0 0 0 0 0 0 0 0 0 936
12 M400 MTR 4 Sandle 40 1600 160 0 0 0 0 0 0 0 0 0 208
13 M400 UNT 5 Belt 100 2000 200 0 0 0 0 0 0 0 0 0 260
how to sum cif value and remove duplicate No. (SAD No.) Please help me.
This comment was minimized by the moderator on the site
I am sooooo happy & glad with your tips. Allah bless you.
This comment was minimized by the moderator on the site
Sub MergeSameCells()
Application.DisplayAlerts = False

Dim rng As Range

MergeCells:

For Each rng In Selection
If rng.Value = rng.Offset(1, 0).Value And rng.Value <> "" Then
Range(rng, rng.Offset(1, 0)).Merge
GoTo MergeCells
End If
Next

End Sub
This comment was minimized by the moderator on the site
An absolute mess of an explantation. Thanks for the effort but it did nothing to help.
This comment was minimized by the moderator on the site
LOVE IT!!! YOUR SAVE MY LIFE!!
This comment was minimized by the moderator on the site
Ifsum=(columns include,start point row,sum column)
Example ifsum=(A:D,B:2,D:D)
WAY EASIER!
This comment was minimized by the moderator on the site
Hi Am chinnaraju

can u please assist for this. Any one?

=VLOOKUP(M5,E:F,2,)


Thanks in advance.
This comment was minimized by the moderator on the site
Needs to be:
=VLOOKUP(M5,E:F,2,FALSE)
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations