Google sheets meminta beberapa sheet menjadi satu

Banyak orang mengira tidak ada alternatif Konsolidasi Data Excel di Google Sheets. Tentu saja, tidak ada perintah seperti itu. Namun di Google Spreadsheet, Anda dapat menggabungkan data dari beberapa Spreadsheet menggunakan formula

Jangan hapus Google Sheets. Ada fungsi yang kuat di Google Sheets untuk melakukan konsolidasi data dengan cara yang jauh lebih baik. Apakah Anda tahu apa fungsi / rumus itu?

Saya berbicara tentang fungsi Kueri. Di Google Sheets, Anda dapat menggabungkan data dari beberapa sheet menggunakan formula berdasarkan Query. Sangat mudah untuk dipelajari

Kita dapat menggunakan fungsi Query di Google Sheets untuk menggabungkan beberapa sheet dan menggabungkan data. Apa lagi. Konsolidasi data dengan cara ini tidak terbatas pada Lembar dalam satu file. Anda bahkan dapat menggunakan Lembar dari file yang berbeda dalam metode ini

Dalam tutorial ini, tidak ada Skrip atau Plugin Aplikasi Google Sheets yang digunakan untuk menggabungkan data dari beberapa Spreadsheet

Jika Anda baru mengenal konsolidasi data, lihat di bawah apa yang saya maksud dengan mengatakan konsolidasikan data dari beberapa lembar menggunakan rumus

Penjelasan. Gabungkan Data dari Beberapa Lembar Menggunakan Rumus

Saya menggabungkan data dari beberapa lembar menjadi satu lembar dan kemudian meringkasnya. Di sini banyak lembar berarti banyak tab dalam satu file. Bagaimana dengan dua file Google Sheets independen?

Itu sudah saya jelaskan setelah beberapa paragraf di bawah ini

Seperti yang telah disebutkan di atas, tidak diperlukan skrip atau plugin untuk melakukan konsolidasi data di Google Docs Sheets

Jika Anda bertanya kepada saya fungsi mana yang dapat kami gunakan untuk menggabungkan data di Google Spreadsheets, saya akan menyarankan fungsi Kueri. Kami juga dapat menggunakan fungsi Filter sampai batas tertentu. Tapi untuk meringkas Query adalah satu-satunya pilihan

Bisakah Kita Mengkonsolidasikan Data Hanya Dari Beberapa Lembar Dari Satu File?

Jawabannya adalah tidak'. Anda juga dapat menggabungkan beberapa lembar dari file Spreadsheet yang berbeda

Untuk melakukannya, Anda harus menggunakan fungsi Google Sheets IMPORTRANGE bersama dengan Query. Bagian yang saya lewati sekarang. Saya akan mencoba menulis tutorial Google Sheets lainnya untuk itu. Jika tidak, Anda akan bingung

Keuntungan Menggunakan Fungsi Query untuk Mengkonsolidasikan Data di Google Sheets

Berikut manfaat konsolidasi data berbasis rumus di Spreadsheet

1. Konsolidasi data tanpa Script apa pun berarti kinerja Spreadsheet yang lebih baik
2. Jika kami menggunakan plugin apa pun (saya tidak tahu plugin semacam itu tersedia untuk mengkonsolidasikan data), kami tidak dapat selalu memastikan ketersediaan plugin itu. Pengembang mungkin atau mungkin tidak menghapus plugin di masa mendatang. Selain itu, mungkin ada risiko keamanan yang terkait dengannya
3. Saat menggunakan rumus untuk menggabungkan data, Anda mendapatkan lebih banyak fleksibilitas. Data gabungan akan diperbarui secara otomatis saat kami mengubah data sumber
4. Terakhir, ini cepat dan Anda juga dapat mengatur formula sekaligus dan melupakannya

Cara Mengkonsolidasikan Data dari Beberapa Lembar Menggunakan Formula di Google Sheets

Silakan ikuti petunjuk langkah demi langkah di bawah ini

Di bawah ini Anda dapat melihat empat lembar. Dua lembar pertama, maksud saya tab lembar, berisi data sampel yang akan kita gabungkan terlebih dahulu di lembar ketiga dan terakhir merangkumnya di lembar keempat

Sebenarnya, kita bisa menggabungkan dan meringkas data menggunakan satu rumus tunggal. Tetapi untuk membuat langkah-langkahnya lebih sederhana, saya melakukannya secara terpisah

Saya pasti akan merinci Anda, bagaimana menggabungkan dan meringkas data bersama-sama menggunakan satu Query di bagian terakhir tutorial ini

Jadi ada dua langkah yang terlibat – konsolidasi dan penggabungan data. Lembar ketiga dan keempat bisa kita gunakan untuk ini. Saya akan membagi tutorial di bawah ini

Dua lembar pertama berisi beberapa contoh data. Lihat itu di bawah. Nama lembar pertama saya adalah "junesheet"

Sekarang lihat data di lembar kedua yang diberi nama “julysheet”

Cara Menggabungkan Data dari Beberapa Lembar Menggunakan Rumus Kueri di Google Doc Sheets

Sekarang mari kita lihat bagaimana menggabungkan dua sheet di atas menjadi satu sheet di file Google Sheets yang sama. Maksud saya di lembar ketiga bernama "Gabungan"

Di bawah ini adalah rumus Kueri untuk menggabungkan data dua Lembar di atas menjadi satu lembar

=query({junesheet!A2:H5;julysheet!A2:H5},"Select * where Col1 is not null ")

Rumus disesuaikan untuk data sampel kami di atas. Anda dapat menggunakannya apa adanya, tetapi dengan beberapa perubahan yang sesuai untuk data spreadsheet Anda. Perubahan yang Anda butuhkan mungkin sebagai berikut

1. Anda harus mengubah rentang data A2. A5 ke rentang data aktual Anda
2. Ubah nama sheet menjadi nama sheet Anda. Saya telah menggunakan dua Lembar untuk digabungkan

Misalkan ada satu Lembar lagi untuk disertakan yang diberi nama "augsheet" di file Anda, Anda dapat memasukkan Lembar itu seperti di bawah ini. Letakkan saja semi-kolom dan masukkan nama sheet dan rentang data

{junesheet!A2:H5;julysheet!A2:H5;augsheet!A2:H5}

Penting. Gabungkan hanya tipe data yang serupa. Jangan pernah menggabungkan kolom teks dengan kolom tanggal atau kolom numerik

Maksud saya jika kolom A Anda berisi string teks di lembar pertama dan angka di lembar kedua, jangan gabungkan. Itu bertentangan dengan konsolidasi data menggunakan Query. Kueri akan mengembalikan keluaran yang salah saat menggabungkan tipe data dalam kolom

Perubahan di atas hanya diperlukan saat Anda menggabungkan beberapa lembar Anda sendiri. Data sampel saya sudah membenarkan aturan di atas

Sekali lagi periksa gambar di bawah ini untuk memahami rumus kueri yang telah saya gunakan untuk menggabungkan beberapa lembar di Google Sheets

Rentang Tak Terbatas dalam Konsolidasi

Jika mau, Anda dapat menggunakan rentang tak terbatas seperti A2. H dalam rumus. Maka rumusnya akan menjadi seperti ini

=query({junesheet!A2:H;julysheet!A2:H},"Select * where Col1 is not null ")

Sekarang kami ingin meringkas atau menggabungkan data yang baru saja kami gabungkan. Di bawah ini adalah langkah-langkahnya. Sebelum itu lihat dulu data gabungan di bawah ini

Cara Meringkas atau Mengkonsolidasikan Data dari Beberapa Lembar Menggunakan Formula di Google Spreadsheets

Bagian di bawah ini menggunakan fungsi Query. Anda juga dapat melakukan ini dengan menggunakan Tabel Pivot

Hanya dengan menggunakan satu lagi rumus Kueri di Lembar terakhir bernama "Konsolidasi", kita dapat mencapai hasil yang diinginkan

=query(Combined!A1:H10,"select D, sum(H) where A is not null group by D")

Lihat rumus ini dijelaskan dengan bantuan gambar di bawah ini

Saya telah menggunakan pengelompokan Kueri untuk melakukan agregasi Sum dalam rumus konsolidasi data ini

Dalam data sampel kami, kolom D berisi deskripsi item. Saya telah meringkas data berdasarkan bidang item ini

Saat item yang sama muncul beberapa kali, rumus ini akan mengelompokkannya ke dalam satu baris tunggal dan akan menjumlahkan nilainya di kolom H

Misalnya, semua item '3″ Air Hose Black' akan dikelompokkan dan jumlahnya di kolom H akan dijumlahkan

Data gabungan akan terlihat seperti di bawah ini

Itu saja. Semoga Anda dapat mempelajari tutorial di atas tentang menggabungkan data dari beberapa lembar menggunakan rumus

Seperti yang sudah saya janjikan di awal, inilah rumus tunggal yang menggabungkan dan meringkas data. Ini adalah kombinasi dari dua rumus Kueri di atas tetapi dengan beberapa perubahan

=query({junesheet!A2:H;julysheet!A2:H},"select Col4, sum(Col8) where Col1 is not null group by Col4")

Jika Anda menemukan masalah untuk mempelajari penggunaannya, Anda dapat dengan bebas menyalin file data tiruan dengan mengikuti tautan di bawah ini

Contoh Konsolidasi Data

Apa yang Harus Dilakukan jika Jumlah Kolom Berbeda di Setiap Tab?

Biasanya itu tidak akan terjadi. Kebetulan, jumlah kolom dalam rentang data di setiap tab berbeda, metode di atas tidak berfungsi. Inilah solusi alternatifnya – Gabungkan Dua Tabel dengan Jumlah Kolom yang Berbeda dalam Kueri

Anda dapat menggunakan rumus kueri di atas sebagai alternatif Tabel Pivot untuk meringkas data. Selain itu, Kueri dapat digunakan sebagai alternatif dari fungsi Filter. Berikut tutorialnya – Menggunakan Fungsi Kueri sebagai Alternatif untuk Fungsi Filter di Google Spreadsheet

Fungsi kueri sangat berguna jika Anda menggunakannya dengan benar. Jadi luangkan waktu Anda sendiri dan pelajari tutorial fungsi Query di atas

Saya pikir saya harus menautkan beberapa tutorial Query lagi di sini. Tapi ada 100+ tutorial Kueri di Info Inspired. Saya bingung mau share yang mana. Lebih baik gunakan ikon Pencarian di bilah atas untuk menemukannya

Bisakah saya menanyakan beberapa lembar di Google Sheets?

Bagaimana cara menggabungkan beberapa Google Sheets menjadi satu?

Apakah ada fungsi konsolidasi di Google Sheets?

Bagaimana Anda mengisi data secara otomatis dari beberapa lembar ke master?