Fungsi QUERY membolehkan anda menarik maklumat daripada julat atau keseluruhan helaian data menggunakan arahan pertanyaan fleksibel. Mempelajari cara menggunakan fungsi QUERY Helaian Google memberi anda akses kepada alat carian yang berkuasa.
Jika anda pernah menulis pertanyaan SQL untuk mendapatkan data daripada pangkalan data, maka anda akan mengenali fungsi QUERY. Jika anda tidak mempunyai pengalaman pangkalan data, fungsi QUERY masih sangat mudah dipelajari.
Apakah Fungsi QUERY?
Fungsi ini mempunyai tiga parameter utama:
=QUERY(data, pertanyaan, pengepala)
Parameter ini agak mudah.
- Data: Julat sel yang mengandungi data sumber
- Query: Pernyataan carian yang menerangkan cara mengekstrak perkara yang anda inginkan daripada data sumber
- Headers: Argumen pilihan yang membolehkan anda menggabungkan berbilang pengepala dalam julat sumber menjadi satu pengepala dalam helaian destinasi
Fleksibiliti dan kuasa fungsi QUERY berasal daripada hujah Pertanyaan, seperti yang anda akan lihat di bawah.
Cara Membuat Formula QUERY Mudah
Formula QUERY amat berguna apabila anda mempunyai set data yang sangat besar yang anda perlukan untuk mengekstrak dan menapis data.
Contoh berikut menggunakan statistik prestasi sekolah tinggi SAT A. S.. Dalam contoh pertama ini, anda akan belajar cara menulis formula QUERY mudah yang mengembalikan semua sekolah menengah dan data mereka dengan nama "New York" dalam nama sekolah.
-
Buat helaian baharu untuk meletakkan hasil pertanyaan. Di bahagian atas sebelah kiri jenis sel =Query(. Apabila anda melakukan ini, anda akan melihat tetingkap pop timbul dengan hujah memerlukan, contoh dan maklumat berguna tentang fungsi tersebut.
Image -
Seterusnya, andaikan anda mempunyai data sumber dalam Helaian1, isikan fungsi seperti berikut:
=Pertanyaan(Helaian1!A1:F460, "PILIH B, C, D, E, F DI MANA B SUKA '%New York%'")
Formula ini termasuk hujah berikut:
- Julat Sel: Julat data dalam A1 hingga F460 dalam Helaian1
- PILIH Pernyataan: Pernyataan SELECT yang memanggil sebarang data dalam lajur B, C, D, E dan F dengan lajur B mengandungi teks yang mengandungi perkataan "New York " di dalamnya.
Image Watak "%" ialah kad bebas yang boleh anda gunakan untuk mencari bahagian rentetan atau nombor dalam mana-mana set data. Meninggalkan "%" di hadapan rentetan akan mengembalikan mana-mana nama sekolah yang bermula dengan teks "New York".
-
Jika anda ingin mencari nama sekolah yang tepat daripada senarai, anda boleh menaip pertanyaan:
=Pertanyaan(Helaian1!A1:F460, "PILIH B, C, D, E, F DI MANA B='Sekolah Menengah New York Harbour'")
Menggunakan operator =mencari padanan tepat dan boleh digunakan untuk mencari teks atau nombor yang sepadan dalam mana-mana lajur.
Image
Oleh kerana fungsi QUERY Helaian Google sangat mudah difahami dan digunakan, anda boleh mengeluarkan sebarang data daripada mana-mana set data besar menggunakan pernyataan pertanyaan mudah seperti di atas.
Gunakan Fungsi QUERY Dengan Operator Perbandingan
Pengendali perbandingan membenarkan anda menggunakan fungsi QUERY untuk menapis data yang tidak memenuhi syarat.
Anda mempunyai akses kepada semua operator berikut dalam fungsi QUERY:
- =: Nilai sepadan dengan nilai carian
- <: Nilai adalah kurang daripada nilai carian
- >: Nilai lebih besar daripada nilai carian
- <=: Nilai adalah kurang daripada atau sama dengan nilai carian
- >=: Nilai lebih besar daripada atau sama dengan nilai carian
- dan !=: Nilai carian dan nilai sumber tidak sama
Menggunakan set data contoh SAT yang sama di atas, mari kita lihat cara melihat sekolah mana yang mempunyai purata min matematik melebihi 500 mata.
-
Dalam sel kiri sebelah atas helaian kosong, isikan fungsi QUERY seperti berikut:
=Pertanyaan(Helaian1!A1:F460, "PILIH B, C, D, E, F WHERE E > 500")
Formula ini memerlukan sebarang data yang lajur E mengandungi nilai yang lebih besar daripada 500.
Image -
Anda juga boleh memasukkan operator logik seperti DAN dan ATAU untuk mencari berbilang syarat. Sebagai contoh, untuk menarik markah hanya untuk sekolah yang mempunyai lebih 600 peserta ujian dan bacaan kritikal bermakna antara 400 dan 600, anda akan menaip fungsi QUERY berikut:
=Pertanyaan(Helaian1!A1:F460, "PILIH B, C, D, E, F DI MANA C > 600 DAN D > 400 DAN D < 600")
Image -
Pengendali perbandingan dan logik memberi anda pelbagai cara untuk menarik data daripada hamparan sumber. Mereka membenarkan anda menapis maklumat penting daripada set data yang sangat besar.
Penggunaan Lanjutan Fungsi QUERY
Terdapat beberapa ciri lain yang boleh anda tambahkan pada fungsi QUERY dengan beberapa arahan tambahan. Perintah ini membolehkan anda mengagregat nilai, mengira nilai, data pesanan dan mencari nilai maksimum.
-
Menggunakan GROUP dalam fungsi QUERY membolehkan anda mengagregatkan nilai dalam berbilang baris. Sebagai contoh, anda boleh purata gred ujian untuk setiap pelajar menggunakan fungsi GROUP. Untuk melakukan ini, taip:
=Pertanyaan(Helaian1!A1:B24, "PILIH A, AVG(B) KUMPULAN OLEH A")
Image -
Menggunakan COUNT dalam fungsi QUERY, anda boleh mengira bilangan sekolah dengan skor min penulisan melebihi 500 menggunakan fungsi QUERY berikut:
=QUERY(Helaian1!A2:F460, "PILIH B, KIRA (F) KUMPULAN OLEH B")
Image -
Menggunakan ORDER BY dalam fungsi QUERY, anda boleh mencari sekolah dengan skor min matematik maksimum dan menyusun senarai mengikut markah tersebut.
=QUERY(Helaian1!A2:F460, "PILIH B, MAX (E) KUMPULAN OLEH B PESANAN OLEH MAX(E)")
Image
