Bagaimana cara menarik data dari banyak tab di google sheets

Selanjutnya, sebelum Anda beralih ke spreadsheet baru, pastikan untuk mencatat rentang sel tempat Anda ingin menarik data dari spreadsheet asli. Misalnya, A. 1 sampai C. 10

Langkah 3. Gunakan fungsi Google Sheets untuk mentransfer data Anda

Sekarang kita menggunakan fungsi IMPORTRANGE. Pertama, klik spreadsheet baru tempat Anda ingin menambahkan data. Dalam contoh ini, namanya “Inventaris Produk. ” Masukkan kolom atau baris ke dalam spreadsheet tempat Anda ingin meletakkan data

Selanjutnya, ketikkan =IMPORTRANGE di dalam sel (Anda dapat memilih menggunakan huruf besar semua atau tidak, tidak masalah. ). Fungsi tersebut kemudian akan menanyakan tiga hal kepada Anda

1. URL spreadsheet asli (atau kunci spreadsheet, kedua opsi berfungsi. )
   
2. Nama tab tertentu di spreadsheet tempat Anda menarik informasi
   
3. Rentang sel untuk data yang Anda butuhkan

Ini akan terlihat mirip dengan ini

=IMPORTRANGE(“https. //dokumen. google. com/spreadsheets/d/1RNez4bhTMt_evAdHrFOBHeBgk1l5HAWVTb43EKpYHR8/edit#gid=0","Pendapatan Penjualan per Kuartal. A1. C10”)

Penting untuk diperhatikan bahwa Anda harus menggunakan nama khusus tab di lembar dalam rumus. Jadi untuk contoh ini, nama spreadsheet asli yang berisi beberapa kumpulan data disebut "Pendapatan Penjualan", tetapi nama tab khusus dengan data kami di dalamnya disebut "Pendapatan Penjualan per Kuartal. ” Kami ingin menggunakan nama tab tertentu untuk menghindari kerusakan fungsi kami di masa mendatang saat sheet atau tab baru dibuat

Oh dan trik lainnya. jangan lupa tambahkan tanda seru (. ) sebelum rentang data. Itu juga penting

Langkah 4. Impor data Anda

Setelah menambahkan formula IMPORTRANGE, Anda dapat mengklik enter

Jika ini pertama kalinya Anda mengimpor data dari spreadsheet tertentu, pop-up mungkin muncul. Jangan khawatir. Pemeriksaan keamanan ini memastikan Anda setuju untuk memberikan akses kepada kolaborator mana pun di spreadsheet ini ke data yang ada di spreadsheet lain. Ini akan meminta Anda untuk "Izinkan akses" saat Anda melihat #REF di sel Anda. Lanjutkan dan klik ya

Bukankah menyenangkan memanipulasi data Anda hanya dengan satu fungsi yang mudah dipelajari?

Masalah apa yang dipecahkan oleh tutorial ini?

Jika Anda memiliki banyak kumpulan data yang ingin dianalisis, digabungkan, difilter, atau diubah dengan perhitungan, Anda mungkin menemukan bahwa tabel pivot, fungsi sederhana seperti SUM, dan fitur pemfilteran mungkin tidak dapat menyelesaikan semua yang Anda inginkan dengan rapi. Tabel Anda bisa berantakan dengan begitu banyak lapisan filter dan fungsi di semua tempat

Apa yang akan Anda pelajari

Untungnya, fungsi kueri Google Spreadsheet adalah alat yang sederhana dan andal — bahkan jika Anda tidak mengetahui SQL. Intuitif untuk dipelajari karena menggunakan kata-kata bahasa Inggris seperti "SELECT", "WHERE", "ORDER BY", "LIMIT" dan lainnya

Saya akan memandu Anda melalui tata letak/penyiapan fungsi kueri dan kata kunci SQL untuk diketahui. Kemudian saya akan menunjukkan kepada Anda beberapa contoh pemula dan kemudian beberapa fungsi yang lebih canggih

Anda berada di tempat yang tepat jika Anda mencari fungsi kueri Google Sheets bersarang, kueri Google Sheets col1, kueri Google Sheets, pilih beberapa kolom, dll.

Sumber daya yang saya buat untuk membantu Anda menggunakan kueri Google Spreadsheet

1. Anda dapat melihat semua rumus yang saya gunakan dalam tindakan di Lembar Google ini. Fungsinya tertulis di atas tabel sehingga Anda dapat melihatnya dengan jelas tetapi aktif di setiap kotak ungu
2. Anda juga dapat menonton video tutorial 8 menit di sini. Itu juga disematkan di bagian bawah artikel ini

‍

‍

Sintaks QUERY Google Sheets

QUERY(data, kueri, [tajuk])

data - Rentang sel untuk menjalankan kueri

• Setiap kolom data hanya dapat menampung nilai boolean, numerik (termasuk tipe tanggal/waktu) atau string
• Dalam kasus tipe data campuran dalam satu kolom, tipe data mayoritas menentukan tipe data kolom untuk tujuan kueri. Tipe data minoritas dianggap sebagai nilai nol

kueri - Kueri yang akan dijalankan, ditulis dalam Bahasa Kueri Google Visualization API

• Nilai kueri harus diapit dengan tanda kutip atau menjadi referensi ke sel yang berisi teks yang sesuai
• Lihat https. // pengembang. google. com/chart/interactive/docs/querylanguage untuk detail lebih lanjut tentang bahasa kueri

header - [ OPSIONAL ] - Jumlah baris header di bagian atas data. Jika dihilangkan atau disetel ke -1, nilainya ditebak berdasarkan isi data

‍

Cara menggunakan Fungsi Kueri Google Sheets argumen pertama. DATA

Fungsi Kueri diatur dalam dua bagian utama

Makhluk pertama, DATA, artinya kumpulan data mana yang Anda tanyakan. Anda dapat memilih antara

1. Menyoroti sel yang Anda minati, atau
2. Memberi nama kumpulan data dan ketikkan nama kumpulan Anda. Anda dapat melakukan ini dengan (lihat tangkapan layar di bawah)

1. Memilih sel yang berisi data Anda
2. Mengklik "Data" pada menu di atas
3. Mengklik "Rentang Bernama"
4. Memberi nama rentang Anda

Cara menggunakan argumen kedua Fungsi Kueri Google Sheets. PERTANYAAN

Bagian kedua dari fungsi kueri disebut QUERY. Ini pada dasarnya diterjemahkan menjadi "apa yang Anda coba kueri dalam rentang sel yang baru saja Anda pilih?" . Omong-omong, bagian ini selalu dibungkus dengan tanda petik

Klausa Fungsi Kueri

Sintaks bahasa kueri terdiri dari klausa berikut. Setiap klausa dimulai dengan satu atau dua kata kunci. Semua klausa bersifat opsional. Klausa dipisahkan oleh spasi. Urutan klausa harus sebagai berikut

Penggunaan Klausa

SELECT Memilih kolom mana yang akan dikembalikan, dan dalam urutan apa. Jika dihilangkan, semua kolom tabel dikembalikan, dalam urutan defaultnya
WHERE Mengembalikan hanya baris yang cocok dengan kondisi. Jika dihilangkan, semua baris dikembalikan
GROUP BY Menggabungkan nilai di seluruh baris
PIVOT Mengubah nilai berbeda di kolom menjadi kolom baru
ORDER BY Mengurutkan baris menurut nilai dalam kolom
LIMIT Membatasi jumlah baris yang dikembalikan
OFFSET Melewati sejumlah baris pertama tertentu
LABEL Mengatur label kolom
FORMAT Memformat nilai dalam kolom tertentu menggunakan pola pemformatan yang diberikan
OPSI Mengatur opsi tambahan

‍

Pada artikel ini, kami akan membahas 4 kata kunci pertama yang harus Anda ketahui

• PILIH
• DI MANA
• DIPESAN OLEH
• MEMBATASI

Cara menggunakan Google Sheets Query SELECT

Setiap kueri dasar dimulai dengan SELECT. Ini mengembalikan baris yang cocok dengan kondisi yang ditentukan menggunakan klausa SELECT

‍

Cara Menggunakan Kueri Google Sheets Pilih Semua Kolom

Jika Anda ingin memilih semua data dalam kumpulan data (artinya tabel yang diambil akan memiliki semua kolom) maka beri tanda * setelah PILIH

=QUERY(A1. G15, “PILIH *”)

Cara menggunakan Kueri Google Sheets Pilih Beberapa Kolom

Tetapi jika Anda hanya ingin mengambil data dari kolom tertentu di dalam kumpulan data, letakkan huruf kolom setelah SELECT (lihat tangkapan layar di bawah)

=QUERY(A1. G15, “PILIH B, C, G”)

Setelah Anda memilih kolom mana yang Anda minati, menggunakan SELECT, sekarang saatnya memfilter jenis data yang Anda inginkan dari kolom tersebut

Kumpulan contoh data di atas menunjukkan ID Perusahaan, Nama, Tahun Pendirian, dan Pendapatan Stripe. Mungkin Anda hanya ingin mengambil data dari perusahaan yang berbasis di Amerika Serikat. Di tabel baru yang akan Anda buat dalam kueri Anda, Anda hanya tertarik untuk memiliki 3 kolom. Nama, Tahun Pendirian, dan Pendapatan Stripe

Anda bisa menulis

=QUERY(Range1,"SELECT B,D,G WHERE C='Amerika Serikat'")

☝️Perhatikan bahwa Anda tidak perlu menyertakan kolom yang Anda filter di kolom yang Anda pilih

☝️Ingatlah untuk memberi tanda kutip tunggal di sekitar teks. 'Amerika Serikat'

Akibatnya, Kolom B (Nama), D (Tahun Pendirian), dan G (Pendapatan Stripe) akan dikembalikan, tetapi hanya perusahaan yang berbasis di Amerika Serikat yang akan disertakan (lihat tangkapan layar di bawah)

Cara menggunakan Kueri Google Sheets DI MANA

Ini mengembalikan baris yang cocok dengan kondisi yang ditentukan menggunakan klausa WHERE. Anda mungkin memerlukan beberapa klausa WHERE

‍

Katakanlah lembar data Anda memiliki beberapa informasi yang hilang dan terlihat seperti ini

Anda hanya ingin mengambil data di mana ada sel yang terisi tetapi juga ingin mengambil data hanya dari perusahaan AS. Anda akan memisahkan dua filter WHERE hanya dengan menulis DAN di antara keduanya (lihat tangkapan layar di bawah).  

=QUERY(Range1, "SELECT B, D, G WHERE A bukan null dan C = 'Amerika Serikat'")

Cara menggunakan Google Sheets Query ORDER BY (mengurutkan data dalam urutan naik atau turun)

Sekarang langkah selanjutnya adalah jika Anda ingin menyortir perusahaan-perusahaan ini. Mungkin Anda tertarik dengan perusahaan dengan pendapatan bulanan terbanyak. Anda akan menambahkan frasa “ORDER BY G DESC” di akhir kueri

DESC adalah kependekan dari 'descending' dan berarti angka terendah ke angka tertinggi

ASC adalah kependekan dari 'naik' dan berarti tertinggi ke terendah (lihat tangkapan layar di bawah)

=QUERY(Range1, "SELECT B, D, G WHERE A is not null and C = 'United States' ORDER BY G DESC")

Cara menggunakan BATAS Kueri Google Sheets

Anda selanjutnya mungkin hanya ingin membatasi diri untuk melihat 5 perusahaan AS teratas dengan pendapatan bulanan tertinggi. Anda cukup menambahkan LIMIT 5 di akhir kueri (lihat tangkapan layar di bawah)

=QUERY(Range1, “SELECT B, D, G WHERE A is not null and C = ‘United States’ ORDER BY G DESC LIMIT 5”)

Penting untuk diingat untuk menyusun kriteria kueri Anda dalam urutan ini. PILIH > DI MANA > PESAN BERDASARKAN > BATAS

Cara Membuat Kueri kueri beberapa sheet/rentang/tab secara bersamaan

Jika Anda ingin membuat kueri beberapa lembar/rentang/tab dalam spreadsheet yang sama, Anda dapat melakukannya menggunakan fungsi kueri

⚠️Namun, Anda harus memastikan bahwa datanya konsisten; . Misalnya, Col1 di semua sheet harus semuanya nama perusahaan, Col2 sebagai ID Perusahaan, Col3 sebagai Pendapatan, dll. Segera setelah Anda menggunakan lebih dari satu tabel dalam kueri, kop surat kolom (A, B, C, D, dll.). ) menjadi tidak relevan dalam kriteria kueri (Tapi tetap relevan saat Anda menunjukkan rentang). Anda sekarang perlu merujuk ke kolom sebagai Col1 untuk kolom pertama dalam rentang yang dipilih dan Col2 untuk yang kedua dan seterusnya

Untuk memilih beberapa tab/sheet, lihat tab by

• Nama mereka
• Kemudian ikuti nama sheet dengan
• Dan kemudian rentang sel pada lembar itu
• Kemudian tambahkan titik koma untuk menumpuk data di atas satu sama lain
• Dan kemudian Nama lembar diikuti oleh
• Dan jangkauannya

Ini sebuah contoh

=QUERY({Sheet1. A. D;Lembar2. B. E} “PILIH *”)

☝️Hal yang paling penting untuk diingat adalah meletakkan tanda kurung kurawal di sekitar totalitas rentang data yang Anda pilih setiap kali Anda meminta data dari lembar yang berbeda atau spreadsheet yang berbeda

Sekarang lanjutkan seperti biasa tetapi rujuk ke kolom sebagai Col1, Col2, dll

‍

Contoh  cara membuat kueri beberapa sheet dalam spreadsheet yang sama

Katakanlah saya ingin menggabungkan data dari dua lembar (Pertumbuhan1 dan Pertumbuhan2) tetapi mengecualikan tingkat pertumbuhan yang kurang dari 0

Inilah Pertumbuhan 1

Dan inilah Pertumbuhan 2

Anda dapat melihat bahwa masing-masing memiliki empat kolom dalam urutan

1. Nama
2. # transaksi minggu ini
3. # transaksi minggu lalu
4. Tingkat pertumbuhan

Seperti yang Anda ingat, Anda perlu memastikan datanya konsisten, dengan tipe data harus sesuai di setiap lembar. Jadi disini

• Nama. Kolom A dari Pertumbuhan1 sejajar dengan Kolom B dari Pertumbuhan2
• # transaksi minggu ini. Kolom B dari Pertumbuhan1 sejajar dengan Kolom C dari Pertumbuhan2
• # transaksi minggu lalu. Kolom C dari Pertumbuhan1 sejajar dengan Kolom D dari Pertumbuhan2
• Tingkat pertumbuhan. Kolom D dari Pertumbuhan1 sejajar dengan Kolom E dari Pertumbuhan2

Sekarang saya akan menggunakan fungsi (lihat tangkapan layar di bawah)

=QUERY({Pertumbuhan1. A. D; . B. E} “Pilih * WHERE Col 4 >0”)

Inilah hasilnya

Contoh cara membuat kueri beberapa sheet di spreadsheet yang berbeda

Anda juga dapat membuat kueri dari spreadsheet yang sama sekali berbeda.  

Katakanlah Pertumbuhan2 tidak ada di spreadsheet ini dan itu ada di spreadsheet lain dan kami masih ingin menggabungkannya. Kami akan mengambil fungsi yang sama.  

=QUERY({Pertumbuhan1. A. D; . B. E} “Pilih * WHERE Col 4 >0”)

Tapi kami akan menggunakan metode "jangkauan impor" untuk menambahkan.  

=QUERY({Pertumbuhan1. A. D; . B. E} “Pilih * WHERE Col 4 >0”)

Pelajari tentang alat yang lebih canggih daripada Google Spreadsheet

‍

Jika perusahaan Anda menyimpan data bisnis utama dalam database atau gudang data, maka Anda harus mencoba Actiondesk

‍

Actiondesk adalah cara termudah bagi tim startup untuk membuat keputusan yang lebih baik dengan data mereka. Anda dapat mengakses data kunci perusahaan Anda tanpa kode atau menulis SQL, Anda hanya memerlukan keterampilan spreadsheet dasar

Bagaimana cara menarik data dari banyak tab ke satu lembar di Google Sheets?

Bagaimana cara menarik data dari banyak tab ke satu tab?

Bagaimana cara meringkas data dari beberapa lembar di Google Sheets?

Bagaimana cara menautkan banyak tab di Google Sheets?