Kuasai Fungsi QUERY Google Sheets
Buka kekuatan manipulasi data seperti SQL di Google Sheets. Pelajari sintaks QUERY untuk filter, urutkan, pivot, dan agregasi data dengan formula yang powerful.
Fungsi QUERY adalah alat manipulasi data paling powerful di Google Sheets-tapi sebagian besar pengguna tidak pernah mempelajarinya. Jika Anda sudah tahu SQL, QUERY memungkinkan Anda menulis statement SELECT, WHERE, GROUP BY, dan ORDER BY yang familiar langsung dalam formula spreadsheet. Jika Anda belum tahu SQL, panduan ini akan mengajarkan semua yang Anda butuhkan.
QUERY menghilangkan kebutuhan untuk formula nested yang kompleks, pivot table, dan filtering manual. Satu formula QUERY dapat menggantikan 10+ formula tradisional, membuat sheet Anda lebih cepat, lebih bersih, dan lebih mudah dipelihara. Ini adalah skill yang membedakan pengguna spreadsheet pemula dari analis profesional.
Dalam panduan komprehensif ini, Anda akan mempelajari sintaks QUERY dari dasar hingga teknik lanjutan. Di akhirnya, Anda akan mampu memfilter jutaan baris, membuat laporan dinamis, dan membangun dashboard otomatis-semuanya dengan satu fungsi yang powerful.
Yang Anda Perlukan
- Pengetahuan menengah tentang Google Sheets
- Pemahaman tentang formula dasar dan referensi sel
- Dataset dengan setidaknya 3 kolom untuk latihan (kami akan menyediakan contoh)
Panduan Langkah demi Langkah
Pahami Dasar-Dasar Sintaks QUERY
Pelajari struktur fundamental dari formula QUERY.
- Sintaks dasar: =QUERY(data, query, [headers])
- data = rentang untuk dikueri (A1:D100)
- query = kueri mirip SQL dalam tanda kutip ("SELECT A, B WHERE C > 100")
- headers = jumlah baris header opsional (biasanya 1)
- Referensi kolom: Gunakan huruf (A, B, C) atau Col1, Col2, Col3
- Contoh: =QUERY(A1:D100, "SELECT A, B, C", 1)
Pro Tip
Selalu sertakan baris header dalam rentang data Anda. QUERY bekerja paling baik ketika nama kolom berada di baris pertama.SELECT Kolom Tertentu
Pilih kolom mana yang akan ditampilkan dalam hasil Anda.
- Pilih semua kolom: =QUERY(A1:D100, "SELECT *")
- Pilih kolom tertentu: =QUERY(A1:D100, "SELECT A, C, D")
- Urutkan ulang kolom: =QUERY(A1:D100, "SELECT D, A, B") menempatkan kolom D pertama
- Gunakan notasi Col: =QUERY(A1:D100, "SELECT Col1, Col3, Col4")
- Ubah nama kolom dengan LABEL: =QUERY(A1:D100, "SELECT A, B LABEL A 'Name', B 'Revenue'")
Pro Tip
Notasi Col (Col1, Col2) lebih baik daripada huruf ketika rentang data Anda mungkin berubah-ini menyesuaikan secara otomatis.Filter Data dengan WHERE Clause
Gunakan WHERE untuk memfilter baris berdasarkan kondisi.
- Filter dasar: =QUERY(A1:D100, "SELECT * WHERE B > 1000")
- Filter teks: =QUERY(A1:D100, "SELECT * WHERE A = 'Product A'")
- Kondisi ganda (AND): =QUERY(A1:D100, "SELECT * WHERE B > 1000 AND C < 5000")
- Kondisi ganda (OR): =QUERY(A1:D100, "SELECT * WHERE A = 'Product A' OR A = 'Product B'")
- Teks berisi: =QUERY(A1:D100, "SELECT * WHERE A CONTAINS 'Manager'")
- Filter tanggal: =QUERY(A1:D100, "SELECT * WHERE B > DATE '2024-01-01'")
Pro Tip
Nilai teks dalam WHERE clause harus menggunakan tanda kutip tunggal di dalam tanda kutip ganda: "WHERE A = 'Text'"Urutkan Hasil dengan ORDER BY
Urutkan hasil kueri Anda secara ascending atau descending.
- Urutkan ascending: =QUERY(A1:D100, "SELECT * ORDER BY B")
- Urutkan descending: =QUERY(A1:D100, "SELECT * ORDER BY B DESC")
- Urutkan berdasarkan beberapa kolom: =QUERY(A1:D100, "SELECT * ORDER BY A ASC, B DESC")
- Kombinasikan WHERE dan ORDER BY: =QUERY(A1:D100, "SELECT * WHERE C > 100 ORDER BY B DESC")
- Urutkan berdasarkan kolom yang tidak ada di SELECT: =QUERY(A1:D100, "SELECT A, B ORDER BY D DESC")
Pro Tip
Urutan clause penting: SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LIMIT. Ikuti urutan ini atau kueri akan gagal.Agregasi Data dengan GROUP BY
Ringkas data berdasarkan kategori seperti pivot table.
- Jumlahkan berdasarkan kategori: =QUERY(A1:D100, "SELECT A, SUM(B) GROUP BY A")
- Hitung berdasarkan kategori: =QUERY(A1:D100, "SELECT A, COUNT(B) GROUP BY A")
- Rata-rata berdasarkan kategori: =QUERY(A1:D100, "SELECT A, AVG(B) GROUP BY A")
- Agregasi ganda: =QUERY(A1:D100, "SELECT A, SUM(B), AVG(C), COUNT(D) GROUP BY A")
- Kelompokkan berdasarkan beberapa kolom: =QUERY(A1:D100, "SELECT A, B, SUM(C) GROUP BY A, B")
- Tambahkan LABEL untuk kejelasan: =QUERY(A1:D100, "SELECT A, SUM(B) GROUP BY A LABEL SUM(B) 'Total Revenue'")
Pro Tip
Saat menggunakan GROUP BY, setiap kolom dalam SELECT harus berada di GROUP BY atau menjadi agregasi (SUM, AVG, COUNT, dll).Gunakan Fungsi Agregasi
Kuasai SUM, COUNT, AVG, MIN, MAX untuk analisis data.
- SUM: =QUERY(A1:D100, "SELECT A, SUM(B) GROUP BY A") menjumlahkan nilai numerik
- COUNT: =QUERY(A1:D100, "SELECT A, COUNT(B) GROUP BY A") menghitung sel yang tidak kosong
- AVG: =QUERY(A1:D100, "SELECT A, AVG(B) GROUP BY A") menghitung rata-rata
- MIN/MAX: =QUERY(A1:D100, "SELECT MIN(B), MAX(B)")
- Kombinasikan fungsi: =QUERY(A1:D100, "SELECT A, SUM(B), AVG(C), COUNT(*) GROUP BY A")
Pro Tip
Gunakan COUNT(*) untuk menghitung semua baris termasuk yang kosong, COUNT(B) hanya menghitung nilai non-blank di kolom B.Batasi dan Format Hasil
Kontrol berapa banyak baris yang dikembalikan dan format output.
- Batasi hasil: =QUERY(A1:D100, "SELECT * ORDER BY B DESC LIMIT 10") menampilkan top 10
- Lewati baris dengan OFFSET: =QUERY(A1:D100, "SELECT * LIMIT 10 OFFSET 5") melewati 5 baris pertama
- Format angka: =QUERY(A1:D100, "SELECT A, B FORMAT B '$#,##0.00'")
- Format tanggal: =QUERY(A1:D100, "SELECT A, B FORMAT B 'yyyy-mm-dd'")
- Format persentase: =QUERY(A1:D100, "SELECT A, B FORMAT B '0.00%'")
Pro Tip
LIMIT sempurna untuk laporan "Top 10". Kombinasikan dengan ORDER BY DESC untuk mendapatkan nilai tertinggi terlebih dahulu.Teknik Lanjutan dan Best Practices
Gunakan kueri dinamis, PIVOT, dan kueri antar sheet.
- Dynamic WHERE dengan referensi sel: =QUERY(A1:D100, "SELECT * WHERE B > "&E2) menggunakan nilai di E2
- Kueri sheet lain: =QUERY(Sheet2!A1:D100, "SELECT * WHERE B > 1000")
- PIVOT data: =QUERY(A1:D100, "SELECT A, SUM(B) GROUP BY A PIVOT C")
- Kombinasikan beberapa kueri dengan kurung kurawal: ={QUERY(A1:D10, "SELECT *"); QUERY(A20:D30, "SELECT *")}
- Tangani kesalahan: =IFERROR(QUERY(A1:D100, "SELECT * WHERE B > 1000"), "Tidak ada hasil")
Pro Tip
Saat membangun kueri dinamis dengan referensi sel, hati-hati dengan tanda kutip. Teks membutuhkan: "WHERE A = '"&E2&"'"Kesimpulan
Anda sekarang menguasai fungsi paling powerful di Google Sheets. Dari statement SELECT sederhana hingga agregasi GROUP BY yang kompleks dan PIVOT table, Anda dapat memanipulasi data seperti pro SQL-semuanya dalam formula spreadsheet.
Namun, jika Anda menemukan diri Anda menulis formula QUERY kompleks berulang kali, ada cara yang lebih pintar. ModelMonkey dapat menghasilkan formula QUERY secara otomatis dengan prompt AI sederhana. Daripada mengingat sintaks dan men-debug kesalahan tanda kutip, cukup beri tahu ModelMonkey apa yang Anda butuhkan:
- "Tunjukkan total pendapatan berdasarkan produk, diurutkan dari tertinggi ke terendah"
- "Filter untuk penjualan lebih dari $1000 dalam 30 hari terakhir dan kelompokkan berdasarkan rep"
- "Buat pivot yang menunjukkan pendapatan bulanan berdasarkan wilayah"
ModelMonkey menulis formula QUERY, menjelaskan sintaks, dan bahkan menyarankan optimisasi. Ini mengubah jam trial-and-error menjadi formula yang bekerja secara instan.
Siap berhenti melawan sintaks QUERY? Lihat paket ModelMonkey dan biarkan AI menulis formula Anda.
Pertanyaan Umum
Apa itu fungsi QUERY Google Sheets?
QUERY adalah fungsi powerful yang menggunakan sintaks mirip SQL untuk memfilter, mengurutkan, dan mengagregasi data di Google Sheets. Sintaks: =QUERY(data, query, headers). Ini mendukung klausa SELECT, WHERE, GROUP BY, ORDER BY, PIVOT, dan LIMIT, menjadikannya lebih fleksibel daripada pivot table atau formula tradisional untuk manipulasi data yang kompleks.
Bagaimana cara menggunakan WHERE di QUERY Google Sheets?
WHERE memfilter baris berdasarkan kondisi. Sintaks: =QUERY(A1:D100, "SELECT * WHERE B > 1000"). Untuk teks, gunakan tanda kutip tunggal: "WHERE A = 'Product'". Kombinasikan kondisi dengan AND/OR: "WHERE B > 1000 AND C < 5000". Gunakan CONTAINS untuk kecocokan parsial: "WHERE A CONTAINS 'Manager'".
Bagaimana cara mengelompokkan data di QUERY seperti pivot table?
Gunakan GROUP BY dengan fungsi agregasi. Sintaks: =QUERY(A1:D100, "SELECT A, SUM(B) GROUP BY A"). Ini menjumlahkan kolom B untuk setiap nilai unik di kolom A. Setiap kolom dalam SELECT harus berada di GROUP BY atau menjadi agregasi (SUM, AVG, COUNT, MIN, MAX). Tambahkan LABEL untuk mengubah nama kolom hasil.
Apa perbedaan antara QUERY dan FILTER di Google Sheets?
FILTER mengembalikan baris yang cocok dengan kondisi tetapi tidak dapat mengagregasi, mengurutkan dalam formula, atau memilih kolom tertentu. QUERY melakukan semua ini dengan sintaks SQL. Gunakan FILTER untuk filtering baris sederhana, QUERY untuk analisis kompleks dengan WHERE, GROUP BY, ORDER BY, dan agregasi. QUERY lebih powerful tetapi memiliki kurva pembelajaran yang lebih curam.
Bagaimana cara membuat formula QUERY dinamis dengan referensi sel?
Gunakan konkatenasi untuk menyisipkan nilai sel ke dalam string kueri: =QUERY(A1:D100, "SELECT * WHERE B > "&E2). Untuk nilai teks, tambahkan tanda kutip: =QUERY(A1:D100, "SELECT * WHERE A = '"&E2&"'"). Untuk tanggal: "WHERE B > DATE '"&TEXT(E2, "yyyy-mm-dd")&"'". Kueri dinamis diperbarui secara otomatis saat sel referensi berubah.