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
- URL spreadsheet asli (atau kunci spreadsheet, kedua opsi berfungsi. )
- Nama tab tertentu di spreadsheet tempat Anda menarik informasi
- 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
- 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
- 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
- Menyoroti sel yang Anda minati, atau
- Memberi nama kumpulan data dan ketikkan nama kumpulan Anda. Anda dapat melakukan ini dengan (lihat tangkapan layar di bawah)
- Memilih sel yang berisi data Anda
- Mengklik "Data" pada menu di atas
- Mengklik "Rentang Bernama"
- 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
- Nama
- # transaksi minggu ini
- # transaksi minggu lalu
- 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