Belajar QUERY

 Querry

Fungsi Query Google Sheets: Fungsi Paling Kuat di Google Sheets

Fungsi Query Google Sheets merupakan fungsi terkuat dan serbaguna di Google Sheets.

Alat ini memungkinkan Anda menggunakan perintah data untuk memanipulasi data Anda di Google Sheets, dan alat ini sangat serbaguna dan canggih .

Fungsi tunggal ini menjalankan tugas banyak fungsi lain dan dapat mereplikasi sebagian besar fungsionalitas tabel pivot.

Video ini adalah pelajaran ke-14 dari 30 dari kursus Google Sheets gratis saya: Tantangan Rumus Lanjutan 30 Hari

Sintaks Fungsi QUERY Google Sheets

=QUERY(data, query, [headers])

Dibutuhkan 3 argumen:

1. rentang data yang ingin Anda analisis
2. kueri yang ingin Anda jalankan, diapit tanda kutip
3. angka opsional untuk memberi tahu berapa banyak baris header yang ada di data Anda

Berikut ini contoh fungsi QUERY:

=QUERY(A1:D234,"SELECT B, D",1)

Rentang data dalam contoh ini adalah A1:D234

Pernyataan kueri adalah string di dalam tanda kutip, berwarna hijau. Dalam hal ini, ia memberi tahu fungsi untuk memilih kolom B dan D dari data.

Argumen ketiga adalah angka 1, yang memberi tahu fungsi bahwa data asli memiliki satu baris tajuk. Argumen ini bersifat opsional dan, jika dihilangkan, akan ditentukan secara otomatis oleh Sheets.

Ini adalah salah satu fungsi khusus Google yang tidak tersedia di alat spreadsheet lainnya.

Catatan Fungsi QUERY

Kata kunci tidak peka huruf besar/kecil, jadi Anda dapat menulis “SELECT” atau “select” dan keduanya berfungsi.

Namun, huruf kolom harus huruf besar: A, B, C, dst. jika tidak, Anda akan mendapatkan kesalahan.

Kata kunci harus muncul dalam urutan ini (tentu saja, Anda tidak harus menggunakan semuanya):

• memilih
• Di mana
• kelompok berdasarkan
• urutkan berdasarkan
• membatasi
• label

Anda akan melihat contoh semua kata kunci tersebut di bawah.

Ada beberapa kata kunci lain tetapi kurang umum. Lihat daftar lengkapnya di sini .

💡 Pelajari lebih lanjut
Pelajari lebih lanjut tentang Fungsi QUERY di kursus terbaru saya: Fungsi QUERY di Google Sheets

Templat Fungsi QUERY Google Sheets

Klik di sini untuk membuka salinan hanya-lihat >>

Jangan ragu untuk membuat salinan: File > Buat salinan…

Jika Anda tidak dapat mengakses templat, mungkin hal itu disebabkan oleh setelan Google Workspace organisasi Anda. Jika Anda mengklik kanan tautan dan membukanya di jendela Penyamaran, Anda akan dapat melihatnya.

Contoh Fungsi QUERY pada Google Sheets

Jika Anda ingin mengikuti solusinya, silakan salin templat Google Sheet di atas.

Seperti inilah tampilan data awal kita:

Dalam tutorial ini, saya menggunakan rentang bernama untuk mengidentifikasi data, yang membuatnya lebih mudah dan lebih bersih untuk digunakan dalam fungsi QUERY. Jangan ragu untuk menggunakan rentang bernama “negara” juga, yang sudah ada dalam templat.

Jika Anda baru mengenal rentang bernama, berikut cara membuatnya:

Pilih rentang data Anda dan buka menu:

Data > Rentang bernama…

Panel baru akan ditampilkan di sisi kanan lembar kerja Anda. Di kotak input pertama, masukkan nama untuk tabel data Anda sehingga Anda dapat merujuknya dengan mudah.

PILIH Semua

Pernyataan tersebut SELECT *mengambil semua kolom dari tabel data kami.

Di sisi kanan tabel (saya menggunakan sel G1) ketik fungsi Google Sheets QUERY berikut menggunakan notasi rentang bernama:

=QUERY(countries,"SELECT *",1)

Catatan: jika Anda tidak ingin menggunakan rentang bernama, tidak masalah. Rumus QUERY Anda akan terlihat seperti ini:

=QUERY(A1:D234,"SELECT *",1)

Untuk sisa artikel ini, saya telah menggunakan rentang bernama "negara", tetapi Anda bebas untuk terus menggunakan referensi rentang biasa A1:D234 sebagai gantinya.

Output dari query ini adalah tabel lengkap kita lagi, karena SELECT *mengambil semua kolom dari tabel negara:

Wah, begitulah! Anda telah menulis QUERY pertama Anda! Tepuk punggung Anda.

PILIH Kolom Tertentu

Bagaimana jika kita tidak ingin memilih setiap kolom, tetapi hanya beberapa kolom tertentu?

Ubah fungsi QUERY Google Sheets Anda untuk membaca:

=QUERY(countries,"SELECT B, D",1)

Kali ini kami hanya memilih kolom B dan D dari dataset asli , jadi output kami akan terlihat seperti ini:

Catatan Penting

Ingat, fungsi QUERY kita ada di sel G1, jadi output akan ada di kolom G, H, I, dan seterusnya.

B dan D di dalam pernyataan pilih QUERY merujuk ke referensi kolom dalam data asli.

Kata Kunci WHERE

Kata kunci WHERE menentukan kondisi yang harus dipenuhi. Kata kunci ini memfilter data kita. Kata kunci ini muncul setelah kata kunci SELECT.

Ubah fungsi QUERY Google Sheets Anda untuk memilih hanya negara yang memiliki populasi lebih dari 100 juta:

=QUERY(countries,"SELECT B, D WHERE D > 100000000",1)

Tabel keluaran kami adalah:

Mari kita lihat contoh kata kunci WHERE lainnya, kali ini hanya memilih negara-negara Eropa. Ubah rumus Anda menjadi:

=QUERY(countries,"SELECT B, C, D WHERE C = 'Europe' ",1)

Perhatikan bagaimana terdapat tanda kutip tunggal di sekitar kata 'Eropa'. Bandingkan ini dengan contoh numerik sebelumnya yang tidak memerlukan tanda kutip tunggal di sekitar angka.

Sekarang tabel outputnya adalah:

ORDER BERDASARKAN Kata Kunci

Kata kunci ORDER BY mengurutkan data kita. Kita dapat menentukan kolom dan arahnya (naik atau turun). Kata kunci ini muncul setelah kata kunci SELECT dan WHERE.

Mari kita urutkan data kita berdasarkan populasi dari terkecil hingga terbesar. Ubah rumus Anda untuk menambahkan kata kunci ORDER BY berikut, dengan menentukan arah menaik dengan ASC:

=QUERY(countries,"SELECT B, C, D ORDER BY D ASC",1)

Tabel keluaran:

Ubah rumus QUERY Anda untuk mengurutkan data berdasarkan negara dalam urutan menurun, Z – A:

=QUERY(countries,"SELECT B, C, D ORDER BY B DESC",1)

Tabel keluaran:

Batasi Kata Kunci

Kata kunci LIMIT membatasi jumlah hasil yang dikembalikan. Kata kunci ini muncul setelah kata kunci SELECT, WHERE, dan ORDER BY.

Mari tambahkan kata kunci LIMIT ke rumus kita dan kembalikan hanya 10 hasil:

=QUERY(countries,"SELECT B, C, D ORDER BY D ASC LIMIT 10",1)

Sekarang ini hanya mengembalikan 10 hasil dari data kami:

Fungsi Aritmatika

Kita dapat melakukan operasi matematika standar pada kolom numerik.

Jadi, mari kita hitung berapa persen dari total populasi dunia (7,16 miliar) yang dimiliki masing-masing negara.

Kita akan membagi kolom populasi dengan total (7.162.119.434) dan mengalikannya dengan 100 untuk menghitung persentase. Jadi, ubah rumus kita menjadi:

=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100",1)

Saya membagi nilai di kolom D dengan total populasi (di dalam tanda kurung), lalu dikalikan dengan 100 untuk mendapatkan persentase.

Tabel keluaran kali ini adalah:

Catatan – Saya telah menerapkan pemformatan pada kolom keluaran di Google Sheets untuk hanya menampilkan 2 tempat desimal.

LABEL Kata Kunci

Judul kolom aritmatika itu agak jelek, bukan? Nah, kita dapat mengganti namanya menggunakan kata kunci LABEL, yang ada di akhir pernyataan QUERY. Coba ini:

=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100 LABEL (D / 7162119434) * 100 'Percentage'",1)

=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100 LABEL (D / 7162119434) * 100 'Percentage' ",1)

Fungsi Agregasi

Kita dapat menggunakan fungsi lain dalam perhitungan kita, misalnya min, maks, dan rata-rata.

Untuk menghitung jumlah populasi minimum, maksimum, dan rata-rata pada kumpulan data negara Anda, gunakan fungsi agregat dalam kueri Anda sebagai berikut:

=QUERY(countries,"SELECT max(D), min(D), avg(D)",1)

Output mengembalikan tiga nilai – populasi maksimum, minimum, dan rata-rata dari kumpulan data, sebagai berikut:

KELOMPOKKAN BERDASARKAN Kata Kunci

Oke, tarik napas dalam-dalam. Ini adalah konsep yang paling sulit dipahami. Namun, jika Anda pernah menggunakan tabel pivot di Google Sheets (atau Excel), Anda pasti sudah paham.

Kata kunci GROUP BY digunakan dengan fungsi agregat untuk meringkas data ke dalam kelompok seperti yang dilakukan tabel pivot.

Mari kita rangkum berdasarkan benua dan hitung berapa banyak negara per benua. Ubah rumus kueri Anda untuk menyertakan kata kunci GROUP BY dan gunakan fungsi agregat COUNT untuk menghitung berapa banyak negara, sebagai berikut:

=QUERY(countries,"SELECT C, count(B) GROUP BY C",1)

Perhatikan, setiap kolom dalam pernyataan SELECT (yaitu sebelum GROUP BY) harus digabungkan (misalnya dihitung, min, maks) atau muncul setelah kata kunci GROUP BY (misalnya kolom C dalam kasus ini).

Output untuk kueri ini adalah:

Mari kita lihat contoh yang lebih rumit, yang menggabungkan berbagai jenis kata kunci. Ubah rumusnya menjadi:

=QUERY(countries,"SELECT C, count(B), min(D), max(D), avg(D) GROUP BY C ORDER BY avg(D) DESC LIMIT 3",1)

Ini mungkin lebih mudah dibaca jika dipecah menjadi beberapa baris:

=QUERY(countries,
"SELECT C, count(B), min(D), max(D), avg(D)
GROUP BY C
ORDER BY avg(D) DESC
LIMIT 3",1)

Ini merangkum data kami untuk setiap benua, mengurutkan berdasarkan rata-rata populasi tertinggi ke terendah, dan akhirnya membatasi hasil hanya ke 3 teratas.

Output dari query ini adalah:

Teknik Fungsi QUERY Google Sheets Tingkat Lanjut

Cara menambahkan baris total ke rumus Kueri Anda

Cara menggunakan tanggal sebagai filter dalam rumus Kueri Anda

Ada 4 kata kunci lagi yang belum dibahas dalam artikel ini: PIVOT , OFFSET , FORMAT dan OPTIONS .

Selain itu, ada lebih banyak fungsi manipulasi data yang tersedia daripada yang telah kita bahas di atas. Misalnya, ada berbagai fungsi skalar untuk bekerja dengan tanggal.

Misalkan Anda memiliki kolom tanggal di kolom A kumpulan data Anda, dan Anda ingin meringkas data Anda menurut tahun. Anda dapat mengurutkannya dengan menggunakan fungsi skalar YEAR:

=QUERY(data,"select YEAR(A), COUNT(A) group by YEAR(A)",1)

Untuk teknik yang lebih maju dengan fungsi QUERY, tonton pelajaran ini dari kursus Advanced 30 :

Video ini adalah pelajaran ke-15 dari 30 dari kursus Google Sheets gratis saya: Tantangan Rumus Lanjutan 30 Hari .

Sumber Daya Lainnya

Fungsi QUERY dibahas di hari ke-14 dan ke-15 kursus Rumus Lanjutan gratis saya untuk Google Sheets: Pelajari 30 Rumus Lanjutan dalam 30 Hari

Dokumentasi resmi Google untuk fungsi QUERY()

Dokumentasi resmi untuk Bahasa Kueri API Visualisasi Google .