Tarik sel yang sama dari beberapa sheet ke dalam kolom master google sheets

Apakah menautkan data antara beberapa Google Sheets menyita terlalu banyak waktu dan energi Anda?

Kabar baik. Kami di sini untuk berbagi dengan Anda beberapa solusi

Kami akan membahas beberapa metode yang memungkinkan Anda menggabungkan data di antara beberapa Google Sheets sehingga Anda tidak perlu menghabiskan banyak waktu dan sumber daya untuk melakukan proses ini secara manual

Pada saat Anda selesai membaca panduan ini, Anda akan dilengkapi dengan beberapa teknik yang telah dicoba dan diuji untuk menggabungkan data dan Google Sheets Anda dengan mulus dan efisien.

Mengapa menautkan data antara beberapa Google Sheets?

Jika Anda tidak menautkan spreadsheet, Anda mengumpulkan dan menggabungkan data dari file dan sheet terpisah secara manual

Menangani data di beberapa Google Spreadsheet dapat menyita banyak waktu dan sumber daya Anda, mencegah Anda dan tim bekerja dengan efisiensi optimal

Tanpa menautkan beberapa spreadsheet Anda, Anda pada akhirnya akan menyalin dan menempelkan ribuan baris dan kolom data, yang tidak akan memotongnya

Selain itu, jika data sumber Anda berubah, Anda harus memperbarui kumpulan data dan laporan secara manual dengan menyisir banyak tab dan lembar, yang semudah menemukan jarum di tumpukan jerami.

File seperti ini bisa jadi sulit untuk dinavigasi, dan butuh waktu lama sebelum siapa pun dapat menemukan data yang mereka cari. Memproses volume data secara manual juga dapat menyebabkan kesalahan, menyebabkan Anda bekerja dengan informasi yang tidak akurat

Itulah mengapa sangat penting untuk menautkan beberapa spreadsheet Anda untuk membantu Anda mengembangkan pandangan utama dari informasi gabungan Anda. Ini memungkinkan Anda untuk melihat dan mengelola data Anda dengan mudah dan menjaga Anda dari proses bolak-balik yang padat karya dan memakan waktu untuk menyalin dan menempelkan data secara manual dari berbagai Google Sheets

TL;DR. Koefisien menyederhanakan dan mengotomatiskan penautan data antara beberapa spreadsheet

Koefisien membuat menautkan beberapa data Google Sheets jauh lebih mudah, lebih mudah, apalagi, otomatis

Dengan Koefisien, Anda dapat memilih file sumber data Google Sheets, menambahkan filter untuk menyaring informasi yang ingin Anda tarik, dan mengimpor data dengan beberapa klik

Memperbarui spreadsheet tertaut Anda juga sangat mudah karena Koefisien memungkinkan Anda mengonfigurasi jadwal penyegaran otomatis

Anda tidak perlu memperbarui secara manual setiap kali lembar sumber tertaut Anda berubah atau mendapatkan informasi baru

Menyiapkan datanya

Sebagai contoh kami, kami akan menggunakan dua spreadsheet Google Sheets. Kami akan memiliki satu spreadsheet bernama Data Penjualan yang berisi dua lembar

• Langganan Perusahaan. Ini termasuk daftar perusahaan dan nomor langganan mereka
• Detail Langganan. Ini berisi daftar nomor langganan dan syarat dan jumlah yang tersisa yang masih jatuh tempo

Yang kedua, Langganan Pengguna, berada dalam spreadsheet yang sepenuhnya terpisah. Ini berisi daftar pengguna dan nomor langganan perusahaan yang sesuai

Mengimpor data dari sheet lain di spreadsheet Google Sheets yang sama

Langkah-langkah di bawah ini menguraikan cara Anda dapat memindahkan data dari satu lembar ke lembar lainnya dengan lancar. Anda juga dapat menonton video kami di atas untuk tutorial langkah demi langkah

Referensi Sel-By-Sel

Menggunakan referensi sel demi sel untuk menarik data dari lembar lain dalam spreadsheet yang sama cukup mudah

Di sel pertama tempat Anda ingin menampilkan data, ketikkan =

Kemudian, Anda dapat mengetikkan nama lembar yang diapit dengan tanda kutip tunggal, diikuti dengan. dan nomor sel, seperti ini

='Langganan Perusahaan'. A2

Atau, Anda dapat mengklik lembar, lalu sel. Anda akan melihat kotak rumus mengambang yang memberi tahu Anda rumus saat ini

Seret sel itu ke bawah dan ke kanan untuk menyertakan sel kumpulan data yang Anda inginkan di baris

Meskipun kumpulan data mungkin terlihat mirip dengan data aslinya, perubahan pada data dalam rentang tersebut akan muncul di lembar yang sesuai dengannya

Catatan. Nomor sel tidak harus cocok di antara dua lembar. Anda dapat mengimpor Sel A1 dari Langganan Korporat ke Sel D7 lembar kerja dengan menempatkan rumus yang sama di D7 alih-alih A1 seperti yang kami lakukan pada contoh

Referensi Rentang

Anda dapat menarik seluruh kolom atau rentang tertentu tanpa melakukan semua pekerjaan di langkah sebelumnya menggunakan rumus ini

={'Detail Berlangganan'. B2. B52}

Yang menarik dari pendekatan ini adalah koneksi diatur oleh sel tempat rumus dimasukkan, tetapi nilai lainnya tetap diperbarui

Fungsi PENCARIAN

Fungsi LOOKUP adalah alat yang sangat berharga untuk mencocokkan data antara dua lembar di mana data mungkin tidak berada dalam urutan yang sama atau berada dalam hubungan satu-ke-banyak

Agar ini berfungsi, Anda harus memiliki bidang yang dapat digunakan sebagai kunci (unik untuk setiap catatan yang Anda cari) untuk mendapatkan hasil yang sempurna menggunakan LOOKUP

Untuk melakukannya, kita akan membuka lembar Langganan Korporat dan menambahkan kolom untuk Jangka Waktu Tersisa dan Nilai Tersisa, serupa dengan yang ada di lembar Detail Langganan

Pertama, klik C2, sel pertama di bawah kolom Remaining Term baru kami, dan masukkan rumus yang terlihat seperti ini

=LOOKUP($A2,'Detail Berlangganan'. $A$2. $A$52,'Rincian Berlangganan'. B$2. B$52)

Fungsi LOOKUP memerlukan tiga bagian

• Sel yang ingin kita cari (oranye)
• Rentang sel yang kami perkirakan akan menemukan nilainya (ungu)
• Rentang sel yang berisi nilai yang ingin kita kembalikan (biru)

Kami telah menggunakan sintaks yang sama sebelumnya untuk memilih sel di lembar lain, kecuali sekarang kami telah melakukannya dengan rentang. Tentukan $ penggunaan untuk mengunci sel tertentu

Selain itu, karena kita ingin menemukan data Nilai Tersisa dan Nomor Langganan yang sama, tambahkan $ untuk mengunci kolom tersebut

Seret nilai ini ke sel lainnya dan semua baris berikutnya, jadi tidak perlu menggunakannya pada nomor baris

Gunakan $ untuk kolom dan baris rentang pencarian (ungu) karena tidak akan berubah berdasarkan kolom atau baris

Terapkan $ untuk nomor baris pada rentang hasil (biru) jika Anda tidak ingin bagian atas dan bawah rentang berubah. Ini memungkinkan Anda mendapatkan Nilai Tersisa tanpa menulis ulang rumus (atau kolom lain yang mungkin Anda miliki setelah itu)

Inilah yang kami punya

Anda dapat menyeret rumus itu ke bawah dan ke kanan

Voila. Anda telah menggabungkan data dari kedua sheet tersebut menjadi satu. Hanya kali ini, Anda dapat mengurutkan ulang hal-hal di lembar pencarian (Detail Langganan) tanpa memengaruhi data di lembar Langganan Perusahaan. Pembaruan data akan tetap tercermin, termasuk data yang tidak cocok

Namun, fungsi LOOKUP tidak sempurna. Jika Anda mengubah baris 52 menjadi langganan 1060 alih-alih 1050, kami tetap mendapatkan hasil untuk 1050, karena LOOKUP tidak memberikan hasil yang sama persis

Ini memberikan kecocokan terdekat, yang dalam hal ini, akan menjadi nilai 1050

Manfaat menggunakan LOOKUP adalah pencarian dan rentang hasil tidak perlu berada di baris yang sama atau berukuran sama. Tangkapannya adalah Anda kemungkinan besar hanya akan mendapatkan perkiraan kecocokan

Di sinilah HLOOKUP dan VLOOKUP berguna

Fungsi VLOOKUP/HLOOKUP

VLOOKUP dan HLOOKUP memiliki fungsi yang serupa, tetapi menemukan nilai dengan cara yang berbeda

Sederhananya, VLOOKUP mencari nilai dalam kolom (Vertikal), dan HLOOKUP melakukan hal yang sama dengan nilai dalam baris (Horisontal)

Dengan menggunakan kumpulan data asli kami, Anda dapat mengganti fungsi LOOKUP menggunakan rumus ini

Rumusnya terlihat sedikit berbeda dari fungsi LOOKUP

=VLOOKUP($A2,'Detail Berlangganan'. $A$2. $C$52,2, SALAH)

Fungsi VLOOKUP memerlukan tiga bagian dan menyertakan satu komponen opsional

• Sel yang ingin kita cari (oranye)
• Rentang penuh (termasuk kolom yang ingin kita cari), nilai yang ingin kita kembalikan, dan segala sesuatu di antaranya (ungu)
• Indeks kolom tempat kita ingin mengembalikan nilai, dimulai dengan 1 (kolom pencarian, 2 (kolom berikutnya di sebelah kanan), dll.
• (Opsional) Google mendeskripsikan variabel keempat ini sebagai apakah rentang pencarian diurutkan. Namun, deskripsi Microsoft tentang hal ini mengacu pada apakah Anda menginginkan kecocokan perkiraan. Jika Anda tidak memasukkan nilai ini, VLOOKUP dan HLOOKUP akan menganggap nilainya BENAR

• Jika BENAR, Google Spreadsheet akan berasumsi bahwa kumpulan data Anda telah diurutkan. Jika menemukan nilai yang lebih besar dari sel pencarian Anda, itu akan mengembalikan nilai sebelum itu. Ini dapat berarti bahwa jika nilai Anda rusak, Google Spreadsheet dapat memberikan jawaban yang salah

• Saat FALSE, program akan berasumsi bahwa nilai bisa saja salah dan mungkin menemukan nilai yang lebih besar dari nilai pencarian sebelum menemukan nilai pencarian. Itu perlu terus mencari sampai menemukan kecocokan yang tepat, dan sel akan mengembalikan kesalahan jika tidak menemukan kecocokan yang tepat

Tentukan $ Anda untuk mengunci rentang Anda. Kunci di kolom huruf (oranye), tapi biarkan nomor barisnya saja sehingga kita bisa menyeretnya ke bawah dan ke kanan dan mengambil hasil kedua

Kunci huruf kolom dan nomor baris rentang pencarian, sehingga Anda tahu bahwa Anda mengambil semua data

Anda akan melihat bahwa jika Anda telah menyalin nilai-nilai ini, Anda tidak akan melihat perubahan apa pun

Ini adalah kekhasan VLOOKUP, dan karena fungsinya bergantung pada rentang tunggal dan indeks kolom, Anda tidak bisa begitu saja menggeser data Anda. Anda harus memperbarui indeks kolom Anda

Kami memasukkan nilai 1060 sebelumnya, dan karena kami memerlukan kecocokan persis, VLOOKUP dan HLOOKUP menunjukkan kesalahan (tidak seperti LOOKUP, di mana kami mendapatkan nilai perkiraan) karena tidak ada dalam tabel yang dicari

Panduan Video. Cara Menghubungkan Google Sheets ke Google Sheets

Menghubungkan ke data di Google Sheet lain

Sekarang setelah Anda mengetahui cara menggabungkan beberapa spreadsheet menjadi satu dengan menautkan data dalam sheet yang sama, mari belajar menautkan data antara dua Google Sheets

Gunakan fungsi IMPORTRANGE, yang terlihat seperti ini

=IMPORTRANGE(“https. //dokumen. google. com/spreadsheets/d/1K2pyGYhTkMrvYqF3JKL4KKjqUzgWYOCcd1-fedLEMiA/edit”,“Langganan Pengguna. A1. C10001”)

Rumus membutuhkan dua variabel

• URL spreadsheet tempat Anda ingin mengambil data. Salin ini dari bilah alamat lembar tempat Anda menarik data dan lampirkan dalam tanda kutip

• Nama sheet dan range (juga dilampirkan dalam tanda kutip) menggunakan sintaks serupa yang kita lihat di bagian referensi Cell-By-Cell. Anda mungkin akan melihat kesalahan referensi, jadi Anda harus memberikan akses ke sheet saat ini

Setelah Anda mengklik Izinkan akses (dengan asumsi Anda memiliki kedua sheet atau memiliki akses edit ke keduanya), data akan ditarik ke dalam sheet

Perubahan pada lembar tertaut akhirnya diperbarui (tidak secara instan, tetapi cukup dekat)

Mengimpor data dari Google Sheet lain dengan Koefisien

Mari kita bahas bagaimana Koefisien dapat membantu Anda menggabungkan beberapa spreadsheet menjadi satu menggunakan proses yang lebih mudah dan efisien

Buka menu Add-on Google Spreadsheet, klik Koefisien. Salesforce, Konektor Data HubSpot, lalu Luncurkan

Klik Impor Data di panel Koefisien

Kemudian, pilih Google Sheet

Pilih Google Sheet apa pun yang Anda inginkan dari Google Drive atau dokumen lain yang aksesnya Anda bagikan

Untuk contoh ini, mari kita lihat daftar Langganan Pengguna yang sama dengan yang kita gunakan di langkah sebelumnya dan klik Pilih

Koefisien membutuhkan waktu beberapa detik untuk mengakses data dan membaca informasi kolom. Anda kemudian akan melihat panel impor dan opsi untuk memfilter data saat masuk

Untuk contoh ini, tambahkan filter untuk nomor langganan yang lebih besar dari 1025. Anda dapat menggunakan ini untuk memfilter langganan yang tidak aktif atau yang memiliki lebih dari jumlah pengguna tertentu. Melakukan ini dengan Koefisien lebih cepat dan sederhana daripada proses manual di Google Sheets

Setelah selesai, klik Impor

Itu dia. Koefisien mengimpor data Anda hampir secara instan, dan Anda bahkan tidak perlu berurusan dengan fungsi dan rumus selama keseluruhan proses

Jadi mungkin sekarang Anda bertanya-tanya, "Bagaimana saya bisa membuat Google Sheets memperbarui secara otomatis referensi ke sheet lain?"

Koefisien telah Anda liput karena memungkinkan Anda menyegarkan data secara otomatis sesuai jadwal, jadi Anda tidak perlu melakukannya dengan susah payah dan manual

Solusi IMPORTRANGE memungkinkan Anda untuk mengimpor seluruh kolom sekaligus, tetapi Anda harus menentukan batasannya atau ini akan mengganggu kumpulan data Anda jika data Anda terlalu banyak bergerak

Selain itu, IMPORTRANGE tidak menawarkan cara bawaan untuk memfilter data, jadi Anda harus melakukannya secara terpisah

Koefisien menangani kedua masalah tersebut untuk Anda dengan membuat filter ke dalam impor, termasuk mengimpor baris atau kolom apa pun yang terisi dengan lancar

Manfaat lain menggunakan Koefisien adalah saat Anda ingin membuat laporan untuk presentasi Anda dan tidak ingin data Anda diperbarui secara real-time

Data Anda diperbarui secara otomatis saat Anda menggunakan fungsi IMPORTRANGE, dan ini terjadi segera setelah data spreadsheet sumber diubah

Ini berarti bahwa jika Anda menyiapkan presentasi dari dokumen langsung, Anda perlu membuat salinan atau pemilik harus berhenti memperbarui file saat Anda menyelesaikan tugas Anda

Anda sudah dapat menebak kebingungan dan gangguan yang dapat terjadi ketika banyak orang mengerjakan dokumen yang sama pada waktu yang bersamaan

Namun, Koefisien dapat menyinkronkan permintaan, menyelamatkan Anda dari tantangan ini. Anda dapat melakukan sinkronisasi akhir dan kemudian menyiapkan materi presentasi Anda tanpa mengubah data sumber atau tabel yang ditautkan ditulis ulang dan meledakkan spreadsheet Anda dalam prosesnya

Kesimpulan

Metode yang diperkenalkan dalam panduan ini membantu Anda merampingkan proses penautan data spreadsheet antara beberapa Google Sheets

Saat Anda menautkan beberapa Google Sheets bersama-sama, memungkinkan file untuk berinteraksi satu sama lain secara otomatis, Anda dapat meningkatkan produktivitas dan keakuratan data Anda

Dengan jumlah stres, frustrasi, dan biaya tenaga kerja yang dapat Anda kurangi dengan mengotomatiskan proses penautan spreadsheet Google, Anda dapat fokus menjalankan bisnis dengan lebih baik

Bagaimana cara mengumpulkan data dari beberapa sheet ke master sheet di Google Sheets?

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

Bagaimana Anda mengonsolidasikan banyak tab pada sheet menjadi satu daftar utama?

Bagaimana cara menautkan data dari beberapa sheet di Google Sheets?