Bagaimana cara meringkas data dari beberapa lembar di excel?

Anda mungkin memiliki data di beberapa lembar Excel yang perlu dikonsolidasikan ke dalam satu lokasi pusat. Untungnya, Anda dapat membuat Master Sheet dengan ringkasan dan total lainnya dari beberapa lembar sumber yang berbeda.  

Dalam tutorial ini, kami akan memperkenalkan cara menghitung total dan mendapatkan data ringkasan dari beberapa lembar dengan struktur serupa

Dapatkan data dari beberapa sheet menjadi master sheet di Excel

1. Hitung Total Berdasarkan Referensi Sel

Artikel ini menunjukkan cara mendapatkan data dari beberapa sheet ke dalam master sheet. Bergantung pada situasinya, ini dapat dilakukan dengan berbagai metode. Anggaplah kita memiliki 3 lembar penjualan dari 3 toko yang berbeda. 1, 2 dan 3 dengan format serupa di bawah ini

Kami ingin menambahkan Total penjualan Toko 1, 2 dan 3 bersama-sama dan menempatkan hasilnya di lembar "Ringkasan". Dalam contoh ini, kita dapat menggunakan referensi sel seperti pada rumus berikut

Masalah dengan menggunakan operator penjumlahan adalah kami tidak dapat terus menambahkan sel dengan lebih banyak lembar

2. Hitung Total Berdasarkan Formula 3D

Excel memiliki rumus 3D untuk membantu kami menjumlahkan semua data di beberapa sheet yang berdekatan. Pada tab Ringkasan, ketik =sum( di sel C2, pilih sel D9 di tab Store1, tahan tombol Shift dan pilih tab lainnya. Bilah rumus ditampilkan

=sum(‘Store1:Store3’!D9)

Rumus 3D ini menghitung total semua sel D9 di lembar berbeda dari Toko 1 hingga Toko 3

Rumus ini memberikan hasil yang sama dengan menggunakan referensi sel. Lihat di bawah

Perhatikan saat menggunakan rumus ini, pengguna harus memastikan untuk mempertahankan urutan lembaran yang sama. Jika salah satu sheet di tengah rentang sheet (Store to Store 3) dipindahkan dari rentang, sheet tersebut akan dikeluarkan dari perhitungan. Pada screenshot di bawah, kita melihat bahwa saat sheet Store2 dipindahkan ke kanan sheet Store3, nilai Total penjualan turun menjadi 645. 9 yang merupakan penjualan Store1 + Store2 saja

3. Kumpulkan data dari beberapa lembar menggunakan INDIRECT()

Kita dapat menambahkan data dari beberapa sheet ke dalam sheet ringkasan menggunakan fungsi INDIRECT(). Fungsi INDIRECT() membutuhkan kita untuk menyimpan nama sheet secara eksplisit

=SUM(INDIRECT(C1&"!"&"D9"),INDIRECT(D1&"!"&"D9"),INDIRECT(E1&"!"&"D9"))_

Dalam rumus di atas, INDIRECT(C1&”. ”&”D9″) memberi kami total penjualan yang terletak di sel D9 pada lembar Store1, INDIRECT(D1&”. ”&”D9″) memberi kita total penjualan yang terletak di sel D9 di Store2, dan seterusnya

Rumus ini lebih baik daripada rumus 3D karena tidak bergantung pada lokasi lembar. Namun, rumus di atas memerlukan daftar semua nama sheet

Seperti yang telah Anda lihat dari tutorial ini, mengumpulkan data dari beberapa lembar dapat dilakukan dengan beberapa cara berbeda dan tidak ada solusi terbaik. Bergantung pada situasinya, seseorang dapat menggunakan rumus 3D atau fungsi INDIRECT(). Dalam beberapa kasus lain, Anda juga dapat menggunakan Konsolidasi Data untuk menghitung total

Masalah yang berbeda memiliki solusi terbaiknya sendiri. Jika Anda mengalami kebuntuan dan ingin mendapatkan solusi dengan cepat, ajukan pertanyaan Anda agar dijawab oleh pakar Excel dalam 20 menit. Mereka tersedia untuk membantu Anda 24/7 di tautan di sebelah kanan. Pertanyaan pertama gratis

Artikel ini akan menunjukkan cara membuat tabel ringkasan di Excel dari beberapa lembar kerja. Saat Anda memiliki jenis data yang serupa di beberapa lembar kerja, akan lebih mudah bagi Anda untuk membuat ringkasannya untuk menganalisis seluruh data lembar kerja tersebut. PivotTable, dll. adalah alat vital untuk membuat analisis yang efektif dalam meringkas seluruh kumpulan data. Dalam artikel ini, Anda akan melihat cara menggunakan berbagai fitur Excel untuk membuat tabel ringkasan dari beberapa lembar kerja

Daftar isi

Download Buku Latihan

Buat Tabel Ringkasan. xlsx

3 Cara Bermanfaat untuk Membuat Tabel Ringkasan dari Beberapa Lembar Kerja di Excel

Kami memiliki data penjualan untuk beberapa produk dan vendor yang menjualnya di bulan Maret, April, Mei, dan Juni di kumpulan data

Memanfaatkan data sebagai tabel itu praktis. Untuk mengonversi data ini menjadi tabel,

• Setelah memilih dataset, pindah ke Insert >> Table
• Selanjutnya, Anda akan melihat kotak dialog Pastikan 'Tabel saya memiliki tajuk' dicentang, lalu klik OK

Data Anda kemudian diubah menjadi tabel. Bagian berikut akan menggunakan data kami sebagai tabel

1. Menggunakan Editor Power Query dan PivotTable untuk Membuat Tabel Ringkasan dari Beberapa Lembar Kerja

Cara paling efektif untuk membuat tabel ringkasan dari beberapa lembar kerja di Excel adalah dengan menggunakan Editor Power Query dan PivotTable. Mari melalui prosedur di bawah ini untuk penjelasan mendetail

Langkah

• Kami akan menggunakan lembar berikut untuk membuat tabel ringkasan dari beberapa lembar kerja

• Sekarang, buka Data >> Dapatkan Data >> Dari Sumber Lain >> Kueri Kosong

• Setelah itu, Editor Power Query akan terbuka
• Selanjutnya, beri nama Query Anda. Dalam kasus saya, nama permintaan saya adalah Overall_Report dan saya menekan tombol ENTER setelah itu
• Kemudian, di bilah rumus Power Query, masukkan rumus berikut

=Excel.CurrentWorkbook()

Semua tabel buku kerja akan dikembalikan oleh rumus

• Selanjutnya, karena kami tidak memerlukan duplikat, kami menghapus kolom kedua menggunakan perintah Hapus Kolom Lain dari menu konteks yang dapat kami peroleh dengan mengklik kanan tajuk kolom pertama

• Nanti, kita pilih Home >> Keep Rows >> Keep Top Rows. Kami tidak membutuhkan semua tabel di buku kerja ini

• Setelah itu, jendela Keep Top Rows akan muncul. Kami memilih empat karena kami akan bekerja dengan empat tabel pertama dalam buku kerja ini

• Operasi ini hanya akan mengembalikan empat baris pertama. Setelah itu, klik ikon bertanda dari gambar berikut, hapus centang Gunakan nama kolom asli sebagai awalan dan klik OK

• Data dari semua sheet Anda kemudian akan digabungkan menjadi satu, seperti yang akan Anda lihat selanjutnya. Ini akan membantu Anda membuat tabel ringkasan

• Kueri mungkin menyertakan beberapa pemformatan asing, seperti tanggal dan waktu Saat kueri dibuka, Anda akan secara otomatis melihat bahwa ada waktu setelah setiap tanggal. Untuk menghapus ini, kami mengklik kanan mouse sambil memilih kolom Tanggal dan kemudian memilih Ubah Jenis >> Tanggal

• Omong-omong, gunakan perintah berikutnya jika data Anda kosong. Pilih kolom yang berisi kosong lalu klik kanan >> Isi. Pilih Bawah atau Atas sesuai dengan kenyamanan Anda

• Setelah itu, tekan CTRL+A lalu pergi ke Transform >> Detect Data Type. Meskipun tidak selalu penting, menggunakan perintah ini adalah kebiasaan yang baik untuk dilakukan saat menggunakan Editor Power Query

• Setelah itu, pilih Beranda >> Tutup dan Muat Ke…

• Selanjutnya akan muncul kotak dialog Import Data. Pilih Laporan PivotTable, lembar tempat Anda ingin Tabel Pivot dibuka, dan klik OK

• Tabel Pivot Anda akan terlihat di lembar kerja baru saat kami memilih Lembar Kerja Baru untuknya. Kemudian, untuk melihat data kompilasi lembar kerja, seret Rentang tanggal ke Bidang Baris, Harga Jual ke Bidang Nilai, dan Penjual ke Bidang Kolom

• Setelah itu, Anda akan melihat laporan penjualan bulanan dan keseluruhan dari bulan Maret hingga Juni. Anda juga dapat melihat catatan penjualan berdasarkan penjual di Tabel Pivot Anda

• Selain itu, klik tombol Plus di samping nama bulan jika Anda ingin mempelajari detail lebih lanjut tentang penjualan di bulan tersebut. Anda akan menerima laporan penjualan berdasarkan tanggal sebagai hasilnya

Dengan demikian, Anda dapat membuat tabel ringkasan dari beberapa lembar kerja di Excel dengan menggunakan Editor Power Query dan Tabel Pivot

Baca Selengkapnya. Cara Membuat Tabel dari Beberapa Lembar di Excel (4 Cara Mudah)

2. Menerapkan Referensi 3D untuk Membuat Tabel Ringkasan dari Beberapa Lembar Kerja

Jika tidak ingin ribet, cukup data total Sales saja, bisa menggunakan prosedur Referensi 3D. Ini sebenarnya cukup mudah meskipun kedengarannya agak rumit. Mari kita lihat sekilas uraian di bawah ini

Langkah

• Pertama, buat lembar baru
• Setelah itu, pilih sel untuk menyimpan total Penjualan dan gunakan rumus di bawah ini

=SUM(June:March!F5:F11)

Rumus tersebut menggunakan fungsi SUM dan referensi lembar untuk mengembalikan total Penjualan selama periode Maret hingga Juni

Dengan demikian Anda dapat membuat tabel ringkasan dari beberapa lembar kerja dengan cara yang sangat sederhana

Baca Selengkapnya. Cara Membuat Tabel 3D di Excel (2 Cara yang Cocok)

Bacaan Serupa

• Cara Membuat Tabel Excel dengan Header Baris dan Kolom
• Membuat Tabel Keputusan di Excel (dengan Langkah Mudah)
• Cara Membuat Tabel Pencarian di Excel (5 Cara Mudah)
• Membuat Tabel Lebih Besar di Excel (2 Metode Berguna)
• Cara Membuat Tabel Konversi di Excel (8 Contoh Mudah)

3. Menggunakan Perintah Konsolidasi untuk Membuat Tabel Ringkasan dari Beberapa Lembar Kerja

Ada alternatif lain yang berguna untuk membuat tabel ringkasan dari beberapa lembar kerja. Kita bisa menggunakan perintah Consolidate dari tab Data untuk menggabungkan sheet yang ingin kita rangkum. Mari kita lihat dataset yang akan kita gunakan untuk metode ini. Data tersebut menunjukkan berapa banyak penjualan suatu barang tertentu yang telah dilakukan oleh salesman tersebut selama bulan januari sampai dengan april. Kami hadir di sini 3 item. Butir 1, Butir 2, dan Butir 3

Silakan ikuti instruksi di bawah ini dan Anda akan memiliki ide yang jelas

Langkah

• Pertama, buat lembar baru dan pilih sel mana saja di mana Anda ingin data gabungan Anda dimulai. Di sini, saya membuat lembar bernama ringkasan
• Selanjutnya, pilih Data >> Konsolidasi (Dari grup Alat Data). Anda akan melihat tampilan jendela Konsolidasi

• Setelah itu, letakkan kursor di bagian Referensi dan pilih lembar tempat Anda meletakkan data terlebih dahulu (dalam kasus saya Item 1)
• Selanjutnya, pilih rentang (B4. F11) yang akan Anda gunakan untuk membuat ringkasan
• Setelah itu, klik Tambah

• Demikian pula, tambahkan rentang lain dari Item 2 dan Item 3 Juga, periksa opsi di bagian 'Gunakan label di'
• Kemudian, klik OK

• Selanjutnya, di lembar baru, Anda akan melihat ringkasan jumlah total penjualan setiap salesman untuk barang-barang tersebut per bulan. Perintah menghasilkan kolom baru setelah kolom salesman yang tidak kita perlukan. Jadi hapus kolom ini

• Mari lakukan pemformatan untuk membuat tabel ringkasan terlihat lebih baik

• Di sini, Anda dapat melihat beberapa ikon Plus di tepi kiri lembar Excel. Kuantitas penjualan setiap item dan bulan disembunyikan oleh Plus Say ini, kami ingin mengetahui ringkasan penjualan yang dilakukan oleh Falcone. Cukup klik Ikon Plus di samping namanya

• Jika Anda ingin mengetahui informasi detailnya, klik tombol 2 di pojok kiri atas lembar. Mengklik 1 akan membawa laporan keseluruhan lagi

Anda dapat menggabungkan sel yang berisi nama Penjual untuk menghapus garis batas yang tidak perlu. Ini tidak akan mempengaruhi prosedur sama sekali

Dengan demikian Anda dapat membuat tabel ringkasan dari beberapa lembar kerja di Excel dengan menggunakan perintah Konsolidasi. Perintah ini menggunakan fungsi SUM secara otomatis untuk mengembalikan jumlah total data berdasarkan kolom dan lembar

Baca Selengkapnya. Cara Membuat Tabel dengan Data yang Ada di Excel

Bagian Praktek

Di sini, saya memberi Anda kumpulan data dari artikel ini sehingga Anda dapat mempraktikkan metode ini sendiri

Kesimpulan

Pada akhirnya, kita dapat menyimpulkan bahwa Anda dapat mempelajari ide-ide dasar tentang cara membuat tabel ringkasan di Excel dari beberapa lembar kerja. Jika Anda memiliki metode atau pertanyaan atau umpan balik yang lebih baik mengenai artikel ini, silakan bagikan di kotak komentar. Ini akan membantu saya memperkaya artikel saya yang akan datang. Untuk pertanyaan lebih lanjut, silakan kunjungi situs web kami ExcelDemy

Bagaimana cara membuat laporan ringkasan dari beberapa lembar kerja di Excel?

Apa cara terbaik untuk meringkas data di Excel?

Apa langkah-langkah untuk menggabungkan data dari beberapa lembar kerja?

Bagaimana cara menarik data dari beberapa lembar kerja di Excel berdasarkan kriteria?