Grup kueri google sheets berdasarkan beberapa kolom

Pernahkah Anda memikirkan bagaimana hidup Anda jika google sheets dapat menanyakan data Anda dengan cara seperti SQL?

Bayangkan sebuah fungsi yang melakukan semua tugas manipulasi data dan memiliki perintahnya sendiri untuk berkomunikasi dengan database

Fungsi QUERY lembar google adalah fantasi yang saya bicarakan

Ini adalah fungsi yang kuat yang mengambil perintah seperti SQL dan memungkinkan Anda untuk memanipulasi data dengan cara yang sederhana dan cepat

Pertama, pastikan Anda mendapatkan contoh buku kerja yang digunakan dalam posting ini untuk diikuti

Apa yang dilakukan fungsi QUERY?

Apa yang tidak dilakukannya

Izinkan saya menyampaikan kepada Anda potensi dan kekuatan fungsi ini

Dengan penggunaannya, kita dapat menghilangkan kebutuhan fungsi google sheet konvensional seperti SUM, AVERAGE, FILTER, dll.

Untuk lebih mengesankan Anda, menurut saya menyortir, memfilter, membatasi, dan memberi label pada data juga dimungkinkan setelah Anda mengetahui fungsi ini

Akhirnya, saya akan mengatakan Anda dapat menggabungkan data dari lembar yang berbeda dan menggabungkan tabel dengan bantuan fungsi QUERY lembar google

Lihat postingan berikut yang berisi contoh fungsi QUERY

Ini sangat serbaguna, bahkan dapat digunakan untuk perhitungan sederhana seperti mengkuadratkan angka atau menggabungkan nama depan dan belakang

Namun, agak sulit untuk menguasai fungsi ini, dan Anda memerlukan beberapa latihan ditambah panduan mendalam yang memandu Anda menjelajahi dunianya.

Namun, bagian kedua ada bersama kita. Yang perlu Anda lakukan hanyalah sedikit latihan

Sintaks untuk fungsi QUERY

Sintaks fungsi QUERY google sheets adalah seperti yang ditunjukkan di bawah ini

= QUERY ( data, query, [headers] )

Sepintas, tampilannya tidak berbeda dengan fungsi Google Sheet biasa. Ini memiliki tiga argumen, di mana dua di antaranya wajib dan satu argumen opsional

• = QUERY ( Data!A1:G1001, "SELECT D, F" )

  _9 menentukan rentang sel tempat Anda ingin menerapkan fungsi

• = QUERY ( Data!A1:G1001, "SELECT * WHERE F > 35" )

  0 menentukan metode atau cara untuk memanipulasi data. Bahasa yang digunakan untuk mengembangkan klausa dalam argumen ini adalah Google Visualization API Query Language. Perintah atau klausa mirip dengan kueri SQL, terkadang lebih lugas daripada SQL

• = QUERY ( Data!A1:G1001, "SELECT * WHERE F > 35" )

  1 adalah argumen opsional dan muncul dalam gambar yang menunjukkan berapa banyak baris judul yang dimiliki data Anda

Sekarang cukup berbicara. Mari kita mulai dengan beberapa contoh fungsi QUERY

Himpunan data

Kumpulan data persediaan adalah sekitar 1000 baris dan riwayat penjualan selama seminggu. Ini adalah database imajiner yang dibuat hanya untuk artikel ini

Dataset ini terdiri dari tujuh kolom.  

• TANGGAL – kolom ini menentukan tanggal terjadinya penjualan. Ini memiliki nilai tanggal dari 23 Mei hingga 28 Mei 2020
• Mitra Kurir – kolom ini memiliki nama mitra kurir. Kami memiliki empat nilai unik untuk kolom ini. Layanan Kurir ABC, DHL, Kurir Emirat, dan FedEx
• Negara Pengiriman – menentukan nama negara tujuan pengiriman produk
• Produk – ini adalah kolom dengan nama produk
• Nama Pelanggan – pelanggan tujuan pengiriman produk
• Kuantitas – menentukan jumlah barang yang dikirim
• Nilai Bersih – menunjukkan jumlah penjualan aktual

Sebelum saya memulai contoh, saya telah membuat tab baru bernama Hasil Kueri di dalam lembar google saya. Untuk semua kueri sampel, Anda akan melihat hasilnya di tab ini

Tab Data, di sisi lain, akan memiliki data aktual

Cara Memilih Seluruh Data dengan Fungsi QUERY

Untuk memilih seluruh data dari lembar google, Anda memiliki klausa pengantar pertama bernama SELECT

Klausa SELECT dengan kombinasi tanda bintang (*) memberi Anda seluruh data dari tabel Anda

Ini akan mirip dengan pernyataan SQL SELECT. Satu-satunya perbedaan adalah tidak adanya klausa DARI. Anda tidak memerlukannya saat bekerja dengan fungsi QUERY

= QUERY ( Data!A1:G1000, "SELECT *" )

Di dalam lembar Hasil Kueri, arahkan ke sel A1 dan masukkan rumus di atas untuk mendapatkan seluruh tabel dari tab Data

Ini adalah salah satu fitur luar biasa dari fungsi QUERY yang dapat mengembalikan array dan bukan hanya hasil nilai tunggal. Anda dapat menggunakan ini untuk mengisi perhitungan Anda secara otomatis alih-alih menyalin dan menempelkan rumus ke bawah satu baris

📝 Catatan. Pastikan Anda memasukkan klausa

= QUERY ( Data!A1:G1001, "SELECT * WHERE F > 35" )

Penjelasan

• Fungsi QUERY mengambil rentang A1. G1000 dari tab Data sebagai argumen pertama (data adalah argumen pertama, ingat?).  
• Kemudian, untuk bagian kueri, kami menggunakan pernyataan SELECT pseudo-SQL
• Tanda bintang (*) berarti mengembalikan semua kolom. Seperti disebutkan di atas, setiap argumen kueri harus berada di dalam tanda kutip ganda

💡 Kiat. Bahkan jika Anda menghilangkan argumen

= QUERY ( Data!A1:G1001, "SELECT * WHERE F > 35" )

Pilih Kolom Tertentu Dengan Fungsi QUERY

Anda telah melihat cara memilih semua kolom di dalam fungsi QUERY. Bagaimana jika Anda hanya ingin mengambil kolom tertentu dari tabel?

= QUERY ( Data!A1:G1001, "SELECT D, F" )

Pergi ke sel H2 dan letakkan rumus di atas untuk mengembalikan kolom tertentu dari tabel sebenarnya

Penjelasan

• Di sini, di sel H2, fungsi QUERY mengambil A1 terlebih dahulu. G1000 sebagai argumen data dari tab Data
• Kemudian, di dalam klausa SELECT, Anda menyebutkan abjad kolom (Kolom D dan F) yang perlu diekstraksi dan dipisahkan dengan koma (,)

Kode ini akan mengiris data Anda dan hanya mengembalikan kolom Product & Quantity

Cara Memfilter Data dengan Fungsi QUERY

Pertimbangkan situasi di mana Anda ingin memfilter data berdasarkan beberapa kondisi

Misalnya, Anda mungkin ingin mengekstrak informasi tentang negara tertentu seperti Amerika Serikat. Atau Anda mungkin ingin menampilkan data untuk tanggal tertentu. Anda mungkin ingin menunjukkan nilai penjualan yang lebih besar dari $1000, dll

Untuk situasi seperti itu, kami mungkin memiliki fungsi FILTER bawaan di bawah google sheets. Tapi percayalah, menggunakan klausa WHERE di bawah fungsi QUERY lebih fleksibel daripada menggunakan fungsi FILTER

• Anda dapat membatasi jumlah kolom dalam tampilan
• Anda juga dapat menggunakan fungsi agregasi seperti SUM, MAX, MIN, AVG, dll. , dikombinasikan dengan klausa WHERE

Beberapa operator sangat membantu saat menentukan kondisi di dalam fungsi

• We can use the numeric comparison operators such as equals to (=), not equals to (<>), less than (<), greater than (>), less than or equals (<=), greater than or equals (>=).
• Untuk membandingkan string, kita mulai dengan, diakhiri dengan, berisi, dll. , operator
• Kita juga bisa menggunakan is null, is not null untuk memeriksa apakah sel kosong atau tidak
• Operator logis seperti AND, OR juga membantu di dalam klausa WHERE

Memfilter Data Numerik

= QUERY ( Data!A1:G1001, "SELECT * WHERE F > 35" )

Untuk memfilter data berdasarkan kolom numerik, Anda dapat menggunakan rumus di atas

Penjelasan

Di dalam pernyataan SELECT, klausa WHERE mengembalikan semua nilai untuk kolom F (Kuantitas) yang lebih besar dari 35. Kami hanya menjual sembilan produk dengan jumlah lebih dari 35

💡 Kiat. Coba gunakan operator perbandingan numerik lainnya untuk bermain dengan data Anda

Memfilter Teks atau Data String

Mari kita periksa berapa banyak derek yang terjual selama seminggu

= QUERY ( Data!A1:G1001, "SELECT * WHERE D CONTAINS 'Cranes'" )

Untuk memfilter nilai teks di bawah fungsi QUERY, kita dapat menggunakan salah satu operator string. Mari kita gunakan operator CONTAINS untuk melihat berapa banyak derek yang ada di data kita. Formulanya seperti yang diberikan di atas

Penjelasan

• Klausa WHERE menggunakan metode CONTAINS untuk melihat apakah ada kata Cranes di dalam kolom D (Produk)
• Karena kami telah menggunakan tanda bintang, semua kolom yang nilai Produk dibaca sebagai Derek akan ditampilkan di output

Latihan. Gunakan semua operator string lain untuk melihat cara kerjanya pada fungsi QUERY dengan klausa  WHERE

Catatan

1. Saat mengerjakan string, jangan lupa untuk mengapitnya di dalam tanda kutip tunggal. Misalnya, Derek diletakkan di bawah tanda kutip tunggal sebagai string dalam kueri di atas
2. Alih-alih BERISI, Anda dapat menggunakan operator sama dengan (=) untuk mendapatkan semua data Derek. Lihat tangkapan layar di atas

Menyaring Tanggal

Anda juga dapat memfilter data berdasarkan tanggal

= QUERY ( Data!A1:G1001, "SELECT * WHERE A = DATE '2020-05-25'" )

Pertimbangkan situasi di mana Anda ingin memeriksa semua data penjualan untuk tanggal 25/05/2020. Anda dapat melakukannya dengan menggunakan klausa WHERE di dalam fungsi QUERY. Formulanya seperti gambar di atas

Masalahnya dengan tanggal adalah, mereka disimpan sebagai nomor seri dari tanggal dasar. Oleh karena itu, saat mengerjakannya di dalam google sheets, Anda akan selalu mencari bantuan fungsi seperti DATE, YEAR, MONTH, DATEVALUE, dll.

Saat Anda bekerja dengan fungsi, semuanya cukup sederhana. Cukup gunakan kata kunci DATE dan setelah itu, sebutkan tanggal Anda di dalam tanda kutip tunggal dengan format yyyy-mm-dd

Penjelasan

Klausa WHERE memeriksa apakah nilai tanggal adalah 25-05-2020 baris demi baris dan memfilter nilai tersebut

Klausa SELECT * mengembalikan semua nilai yang difilter tersebut dalam bentuk tabel

Catatan

1. Klausul DATE bersifat wajib. Anggap saja sebagai jembatan antara format tanggal standar (yyyy-mm-dd) untuk kueri dan format tanggal yang Anda gunakan di lembar Anda. Tanpa kata kunci itu, rumus tidak akan pernah bisa mengambil tanggal yang sesuai
2. Harap perhatikan baik-baik tanggalnya; . Anda tidak dapat menulis tanggal sama dengan angka. Tanggal dianggap sebagai string

Data Agregat Menggunakan Fungsi QUERY

Pengalaman penulisan kueri apa pun tidak lengkap tanpa fungsi agregasi

Manipulasi data tidak akan terjadi jika Anda tidak mengetahui fungsi agregasi. Di dalam fungsi QUERY juga Anda dapat menggunakan fungsi agregasi standar seperti SUM, AVG, MIN, MAX, dan COUNT

Sekarang saya akan menunjukkan cara menggunakan fungsi agregasi ini di dalam fungsi QUERY di bawah lembar google

Menggunakan SUM Di dalam Fungsi QUERY

Jumlah adalah jenis agregasi paling umum yang akan Anda gunakan, dan fungsi QUERY adalah salah satu dari banyak cara untuk MENJUMLAHKAN di Google Sheets

Mari kita gunakan fungsi SUM terlebih dahulu pada satu kolom di dalam fungsi QUERY

Jumlah kolom Kuantitas

= QUERY ( Data!A1:G1000, "SELECT SUM(F)" )

Gambar di atas menunjukkan kolom Sum of the Quantity. Formulanya seperti gambar di atas

Penjelasan

Ini cukup mudah. Fungsi SUM menggunakan abjad kolom sebagai argumen dan mengembalikan satu nilai sebagai penjumlahan

Menggunakan SUM beberapa kali di dalam fungsi QUERY

= QUERY ( Data!A1:G1001, "SELECT SUM(F), SUM(G)" )

Fungsi SUM mengambil beberapa abjad kolom sebagai argumen dan menghasilkan jumlah untuk setiap kolom numerik. Formulanya seperti gambar di atas

Penjelasan

Di dalam fungsi QUERY, SUM diterapkan pada kolom F dan kolom G. Ini mengembalikan jumlah kolom Kuantitas dan Nilai Bersih

Menggunakan SUM dengan klausa WHERE

= QUERY ( Data!A1:G1001, "SELECT SUM(G) WHERE A <= DATE '2020-05-25'" )

Rumus untuk penjumlahan bersyarat adalah seperti yang ditunjukkan di atas

Penjelasan

Klausa WHERE bekerja lebih dulu. Tanggal 23, 24, dan 25 disaring terlebih dahulu, lalu SUM diterapkan ke kolom G untuk mendapatkan Total Nilai Bersih hanya untuk tanggal tersebut

Menggunakan MIN dan MAX

Seperti yang disarankan oleh kata kunci itu sendiri, MIN dan MAX adalah agregator yang digunakan untuk menghasilkan nilai minimum dan maksimum dari data. Anda dapat menggunakan kedua agregator ini di database Persediaan untuk menghasilkan nilai minimum dan maksimum

Menggunakan MIN dan MAX pada satu kolom

= QUERY ( Data!A1:G1001, "SELECT MIN(F), MAX(F)" )

Fungsi MIN dan MAX menggunakan abjad kolom tunggal sebagai argumen dan mengembalikan nilai minimum dan maksimum masing-masing untuk kolom tersebut. Formulanya seperti di atas

Penjelasan

Di dalam fungsi QUERY, MIN, dan MAX mengambil kolom F sebagai argumen. Berdasarkan itu, sistem mengembalikan nilai kuantitas minimum dan maksimum, masing-masing

Menggunakan MIN dan MAX pada Banyak Kolom

= QUERY ( Data!A1:G1000, "SELECT *" )

Dimungkinkan untuk menggunakan MIN dan MAX pada lebih dari satu kolom sekaligus. Anda harus memanggil fungsi ini lebih dari sekali dengan alfabet kolom sebagai argumen. Tuliskan rumus di atas untuk menghasilkan nilai minimum dan maksimum masing-masing untuk kolom Quantity dan Net Value

Penjelasan

= QUERY ( Data!A1:G1000, "SELECT *" )

MIN dan MAX dipanggil masing-masing pada kolom F dan G. Anda juga dapat mengubah urutan eksekusi karena tidak wajib memanggil MIN terlebih dahulu dan MAX setelahnya. Formula di atas baik-baik saja

Menggunakan MIN dan MAX dengan Klausa WHERE

Pertimbangkan situasi di mana kami ingin mengetahui nilai MIN dan MAX untuk Kuantitas dan Nilai Bersih. Tapi kali ini, kami menginginkan nilai-nilai ini hanya untuk negara India

= QUERY ( Data!A1:G1000, "SELECT *" )

Rumus untuk mencapai hasil ini adalah seperti yang ditunjukkan di atas

Penjelasan

Seperti yang telah kita lihat pada contoh sebelumnya, MIN dan MAX bekerja pada banyak kolom secara bersamaan. Kali ini, kami menambahkan klausa WHERE untuk memfilter semua nilai dengan Country = 'India'

Klausa WHERE akan dieksekusi terlebih dahulu. Kemudian sistem akan mengembalikan nilai minimum dan maksimum untuk semua lini di mana Negaranya adalah India

Menggunakan AVG pada Satu Kolom

Fungsi AVG di dalam kueri mengembalikan nilai rata-rata atau rata-rata untuk kolom numerik

= QUERY ( Data!A1:G1000, "SELECT *" )

Fungsi AVG menggunakan abjad kolom sebagai argumen dan mengembalikan nilai rata-rata untuk kolom tersebut. Gunakan kode di atas untuk mencapai hasil ini

Penjelasan

Di sini, kolom F disediakan sebagai argumen di dalam fungsi AVG. Fungsi mengembalikan Kuantitas rata-rata yang terjual

Menggunakan AVG di Lebih dari Satu Kolom

Anda juga dapat menggunakan fungsi AVG di lebih dari satu kolom numerik. Ini mirip dengan menggunakan fungsi SUM atau MIN, MAX pada banyak kolom

=QUERY(Data. A1. G1001, “PILIH AVG(F), AVG(G)”)

= QUERY ( Data!A1:G1000, "SELECT *" )

Untuk mengetahui Kuantitas rata-rata dan Nilai Bersih untuk basis data Perbekalan, Anda dapat menggunakan kode di atas

Penjelasan

Fungsi AVG di sini menggunakan abjad kolom sebagai argumen. Anda memiliki Kuantitas dan Nilai Bersih masing-masing disimpan di kolom F & G;

Menggunakan AVG Dengan Klausa WHERE

Misalkan Anda ingin menghasilkan nilai rata-rata berdasarkan kriteria. Dalam hal ini, Anda dapat menggunakan fungsi AVG bersama dengan klausa WHERE

= QUERY ( Data!A1:G1000, "SELECT *" )

Pertimbangkan situasi di mana Anda ingin menampilkan Kuantitas rata-rata dan Nilai Bersih untuk produk tertentu. Anda dapat menggunakan klausa WHERE untuk membatasi produk dan kemudian menggunakan AVG untuk menghasilkan nilai rata-rata

Penjelasan

Di sini, klausa WHERE mengevaluasi terlebih dahulu untuk memfilter semua produk 'Ban'. Kemudian fungsi AVG menghasilkan rata-rata Kuantitas dan Nilai Bersih. Contoh ini menunjukkan bahwa hampir empat ban terjual rata-rata selama minggu itu, dengan perkiraan nilai bersih rata-rata sebesar $51.071

Menggunakan COUNT di dalam Fungsi QUERY

Fungsi COUNT di dalam QUERY mengembalikan jumlah baris di dalam kolom yang disebutnya

Mirip dengan fungsi agregasi lainnya, fungsi ini juga bekerja pada satu kolom, lebih dari satu kolom, dan bahkan dengan klausa WHERE

Agregator COUNT hanya akan menghitung nilai yang tidak kosong saat digunakan di dalam fungsi QUERY

Pertimbangkan situasi di mana kami ingin mengetahui jumlah kuantitas untuk tanggal kurang dari atau sama dengan tanggal tertentu. Mencapai ini mudah dengan menggunakan COUNT yang dikombinasikan dengan klausa WHERE, seperti yang ditunjukkan pada gambar di atas

Penjelasan

Fungsi QUERY terlebih dahulu mengevaluasi kondisi WHERE dan memfilter tanggal 23, 24, dan 25 Mei 2020. Kemudian, fungsi COUNT diterapkan ke kolom F untuk menghitung jumlah baris di dalam kolom Kuantitas

Kita juga bisa menggunakan fungsi COUNT dengan sendirinya

Di sini, dalam contoh ini, fungsi COUNT diterapkan ke kolom F. Ini mengembalikan jumlah sel yang tidak kosong di dalam kolom itu

💡 Kiat. Fungsi COUNT selalu mengembalikan sel yang tidak kosong dari data yang diberikan

Klausa LABEL dalam Fungsi QUERY

Sekarang pertimbangkan situasi di mana Anda ingin mengubah label kolom atau menetapkan label ke kolom data Anda

Bisakah Anda melakukannya di dalam fungsi QUERY?

Klausa LABEL secara khusus dikembangkan untuk menghadapi situasi seperti ini. Ini memungkinkan Anda untuk menetapkan label ke satu atau lebih kolom

Pertimbangkan Anda ingin mengubah label TANGGAL, Produk, dan Nilai Bersih di dalam kumpulan data. Yang baru harus Tanggal Pengiriman, Nama Produk, dan Nilai Penjualan

= QUERY ( Data!A1:G1000, "SELECT *" )

Rumus yang melakukan tugas ini adalah seperti yang ditunjukkan di atas

Penjelasan

Di dalam fungsi QUERY, klausa LABEL mengambil abjad kolom terlebih dahulu. Setelah itu, terlampir adalah label baru di dalam tanda kutip tunggal

Setiap kombinasi header-label kolom harus dipisahkan menggunakan tanda koma (,)

📝 Catatan. Klausul LABEL bebas dan independen. Tidak perlu adanya klausa lain untuk eksekusi

Klausul LIMIT dalam Fungsi QUERY

Bagaimana jika saya memberi tahu Anda bahwa ada cara untuk membatasi tampilan jumlah baris?

Jika saya memberi tahu Anda bahwa fungsi QUERY hanya dapat menarik jumlah baris tertentu yang pertama, apakah Anda akan terkejut?

Klausa LIMIT adalah jawaban untuk semua permintaan Anda. Ini membatasi jumlah baris di dalam tampilan akhir

Muncul setelah SELECT, WHERE, dan ORDER BY (kita akan mempelajari klausa ini nanti) dalam hal eksekusi. Namun, Anda dapat menggunakan klausa ini sebagai mandiri di dalam fungsi QUERY

Pertimbangkan situasi di mana Anda ingin menampilkan Produk, Nama Pelanggan, Kuantitas, dan Nilai Bersih. Batasi output dengan ketentuan bahwa hanya pelanggan dengan huruf "a" di namanya yang boleh dipilih. Terakhir, batasi tampilan agar hanya menampilkan sepuluh baris pertama

= QUERY ( Data!A1:G1000, "SELECT *" )

Rumus yang setara adalah seperti yang ditunjukkan di atas

Penjelasan

Pernyataan SELECT mengembalikan kolom tertentu D, E, F, dan G. Klausa WHERE menambahkan syarat bahwa kolom E harus berisi huruf “a“

Terakhir, klausa LIMIT membatasi keluaran, dan Anda hanya dapat melihat sepuluh baris pertama di dalam tampilan akhir

SORT Data dengan Fungsi QUERY

Penyortiran sangat penting saat memanipulasi tabel besar, bukan?

Dengan mengurutkan data, Anda dapat melihat sekilas nilai tertinggi atau terendah

Di dalam fungsi QUERY, Anda dapat menggunakan klausa ORDER BY untuk mengurutkan data. Ini mengurutkan seluruh data berdasarkan nilai dari kolom tertentu

Itu muncul setelah klausa SELECT dan WHERE (Jika ada dalam kode). Karena itu, itu independen dan tidak membutuhkan pengguna untuk mengeksekusi klausa WHERE

Anda dapat menentukan arah pengurutan. Standar ASC untuk urutan menaik, dan DESC adalah singkatan dari urutan menurun. Secara default, sistem akan mengurutkan data dalam urutan menaik jika urutan pengurutan tidak ditentukan

Anda dapat membuat situasi di mana Anda memilih kolom Shipping Country, Quantity, dan Net Value. Jumlahnya harus lebih dari 1 unit

= QUERY ( Data!A1:G1000, "SELECT *" )

Urutkan data dalam urutan menaik menurut kolom Nilai Bersih dan batasi untuk kembali ke sepuluh baris saja. Formulanya seperti gambar di atas

Penjelasan

Klausa SELECT membatasi tampilan kolom. Klausa WHERE hanya mengizinkan kuantitas lebih dari 1 unit. Klausa ORDER BY mengurutkan data berdasarkan kolom Nilai Bersih dalam urutan menaik. Terakhir, klausa LIMIT membatasi keluaran menjadi sepuluh baris

Jika Anda ingin mengurutkan data dalam urutan menurun, gunakan kata kunci DESC setelah nama kolom di dalam klausa ORDER BY

Pertimbangkan contoh yang sama yang digunakan di atas;

= QUERY ( Data!A1:G1000, "SELECT *" )

Gunakan kata kunci DESC setelah klausa ORDER BY. Outputnya seperti yang ditunjukkan pada gambar di atas berdasarkan rumus di atas

Menggunakan GROUP BY dengan Fungsi QUERY

Pengelompokan data adalah cara yang sangat berguna untuk meringkas data Anda. Ada banyak cara untuk mengelompokkan data di Google Sheets, termasuk dengan fungsi QUERY

Klausa GROUP BY sangat berguna. Ini memungkinkan Anda meringkas data Anda dalam grup berdasarkan nilai dari kolom tertentu. Anda dapat menganggapnya sebagai sesuatu yang bekerja pada garis yang mirip dengan tabel pivot

= QUERY ( Data!A1:G1001, "SELECT D, F" )

Asumsikan bahwa Anda ingin meringkas total penjualan untuk setiap negara selama seminggu dalam data Persediaan kami. Anda dapat melakukannya dengan menggunakan klausa GROUP BY di dalam QUERY. Outputnya akan seperti yang ditunjukkan pada gambar di atas. Formulanya ada di atas

Penjelasan

Di dalam fungsi QUERY, klausa GROUP BY mengelompokkan SUM of Net Value menurut negara. Kemudian klausa SELECT memungkinkan kita mengembalikan output yang dikelompokkan dari Negara Pengiriman dan jumlah Nilai Bersih (kita menyebutnya Total Penjualan)

Menggunakan Operator logika AND & OR di dalam Fungsi QUERY

Operator logis seperti AND, OR bermanfaat saat Anda ingin menggunakan dua atau lebih kondisi secara bersamaan di dalam fungsi QUERY

• Anda dapat menggunakan klausa AND untuk menggabungkan dua kondisi atau lebih. Jika rumus memenuhi semua persyaratan, sistem akan menghasilkan keluaran;
• Anda dapat menggunakan klausa OR saat Anda ingin fungsi mengevaluasi apakah salah satu kondisinya benar. Jika Anda memiliki tiga kondisi dan setidaknya satu kondisi benar, sistem akan menghasilkan output

Menggunakan AND untuk Dua Kondisi

Pertimbangkan situasi di mana Anda menginginkan keluaran sedemikian rupa sehingga dua kondisi terpenuhi

• Tanggal Pengiriman harus 05/25/2020 atau lebih
• Kuantitas harus lebih dari 30 unit

= QUERY ( Data!A1:G1001, "SELECT D, F" )

Karena kita memerlukan baris yang mengikuti kedua kondisi ini, kita dapat mengelompokkannya menggunakan operator AND, seperti yang ditunjukkan pada tangkapan layar di atas. Rumus untuk hasil ini di atas

Penjelasan

Klausul WHERE memungkinkan kondisi untuk memfilter nilai dari kolom A setelah 25/05/2020. Klausa AND akan memungkinkan kita menambahkan satu ketentuan lagi; . Karena kedua kondisi ini dievaluasi sebagai BENAR, sistem mengembalikan baris yang difilter sebagai output

Menggunakan ATAU untuk Dua Kondisi

= QUERY ( Data!A1:G1001, "SELECT D, F" )

Di sini Anda memiliki situasi di mana Anda harus memiliki keluaran sedemikian rupa sehingga setidaknya salah satu dari kondisi berikut harus dipenuhi

• Negara Pelayaran diakhiri dengan huruf “e“
• Nilai Bersih kurang dari $5000

Penjelasan

Penjelasannya cukup sederhana. Kami memiliki dua kondisi yang ditambahkan di bawah klausa WHERE. Jika salah satu kondisi benar, sistem akan menerapkan filter ke data dan mengembalikan hasilnya. Klausa OR disini membantu sistem dalam pengambilan keputusan

Memilih Top N dengan Fungsi QUERY

Pemilihan baris N teratas menggunakan fungsi QUERY cukup sederhana

Tidak ada klausul yang dirancang secara eksplisit untuk ini. Namun, Anda dapat menggunakan kombinasi ORDER BY dan LIMIT satu sama lain untuk mengembalikan N baris teratas dari kumpulan data Anda

Anda ingin memilih 10 baris teratas berdasarkan riwayat penjualan dari data Persediaan

= QUERY ( Data!A1:G1001, "SELECT D, F" )

Rumus yang mengembalikan 10 baris teratas berdasarkan riwayat penjualan adalah seperti yang ditunjukkan di atas

Penjelasan

Klausa ORDER BY mengurutkan kolom G (Nilai Bersih) dalam urutan menurun. Kata kunci DESC membantu sistem mengurutkan dalam urutan menurun. Kemudian, klausa LIMIT membatasi jumlah baris menjadi sepuluh. Terakhir, klausa SELECT mengembalikan kolom C, D, E, dan G dengan sepuluh baris dalam tata letak

Format Data dengan fungsi QUERY

Formatnya sama pentingnya saat bekerja dengan data Anda

Misalnya, saat bekerja dengan data keuangan, Anda mungkin ingin hasilnya ditampilkan dalam format akuntansi

Beberapa data mungkin tidak dalam bentuk yang sesuai. Beberapa mungkin tidak dalam kondisi yang Anda harapkan

Ada beberapa cara memformat data di dalam google sheets. Karena artikel ini khusus tentang fungsi QUERY, kami hanya akan membahas metode yang kompatibel

Cakupan pemformatan data di dalam fungsi QUERY terbatas. Anda dapat memformat nilai tanggal, angka, dan waktu saja

Klausa FORMAT di dalam fungsi QUERY memungkinkan kita mengubah format data

Memformat Nilai Tanggal

Untuk memformat tanggal di dalam lembar google, kami memiliki cara berbeda. Saya mencantumkan beberapa kode di sini yang dapat membantu

Mari kita lihat contoh pemformatan nilai tanggal di dalam fungsi QUERY google sheet

Di sini, dalam contoh ini, Anda mengonversi dan mengembalikan nilai tanggal ke dalam format mmm yy. Klausa LIMIT membatasi keluaran hingga sepuluh baris

= QUERY ( Data!A1:G1001, "SELECT D, F" )

Formulanya seperti gambar di atas

Anda dapat menggunakan kombinasi kode tanggal yang berbeda untuk menghasilkan format kustom Anda sendiri

Di sini, dalam contoh ini, saya telah membuat format tanggal khusus dengan bantuan kode tanggal yang tersedia. Anda dapat membuat format tanggal Anda sendiri dengan bantuan itu

= QUERY ( Data!A1:G1001, "SELECT D, F" )

Rumusnya adalah seperti yang ditunjukkan di atas

Memformat Angka

Untuk memformat angka di dalam lembar google, Anda harus mengikuti tabel yang diberikan di bawah ini

Sekarang Anda dapat memformat nilai angka di dalam database Persediaan

Di sini, format #,#### diterapkan ke kolom Nilai Bersih. Anda dapat melihat output seperti yang ditunjukkan pada tangkapan layar di atas

= QUERY ( Data!A1:G1001, "SELECT D, F" )

Formulanya seperti gambar di atas

Jika Anda ingin menerapkan format pada lebih dari satu kolom, Anda bisa menggunakan koma untuk memisahkan kolom di dalam klausa FORMAT

📝 Catatan. Anda dapat melihat klausa LIMIT digunakan untuk membatasi baris keluaran. Urutan eksekusi untuk klausa FORMAT adalah setelah klausa LIMIT

Tambahkan Dua Rentang dengan Fungsi QUERY

Sesekali, Anda akan menghadapi situasi di mana Anda perlu menambahkan dua rentang dari dua lembar berbeda

Menyalin dan menempel bisa membosankan. Apalagi jika data dari sheet lain berubah, Anda tetap perlu melakukan copy-paste

Apakah ada cara lain untuk menyelesaikan tugas ini?

Baiklah. Fungsi QUERY lembar google dapat membantu Anda menambahkan dua rentang dari dua lembar berbeda

Misalkan kita memiliki rentang 20 item untuk tanggal 29/05/2020 di lembar lain, seperti yang ditunjukkan di atas. Anda ingin menambahkan rentang ini ke data asli

Berikut cara mencapainya menggunakan fungsi QUERY

Contoh ini akan menggabungkan rentang dari sheet “Data” dan rentang dari tab “Data 3” ke dalam tab “Appended Ranges”

Tangkapan layar di atas menunjukkan bagaimana kedua rentang ditambahkan bersama

= QUERY ( Data!A1:G1001, "SELECT D, F" )

Rumus yang melakukan ini adalah seperti yang ditunjukkan di atas, dan tampilannya menarik

Penjelasan

• Di dalam fungsi kueri, rentang dari tab Data dan tab Data 3 disebutkan di dalam kurung kurawal. Gunakan titik koma sebagai pemisah
• Kondisi WHERE memungkinkan Anda menambahkan baris yang tidak kosong hanya dari kedua sheet
• Terakhir, klausa SELECT mengembalikan semua baris yang ditambahkan bersama-sama sebagai tampilan. Anda telah melihat tangkapan layar sebagian di atas

Anda dapat menambahkan beberapa rentang. Pisahkan masing-masing dengan titik koma di dalam kurung kurawal

Menggabungkan Dua Tabel dengan Fungsi QUERY

Bergabung dengan dua tabel adalah aktivitas penting dalam kehidupan sehari-hari seorang analis sehingga Anda tidak dapat hidup tanpanya

Ini memungkinkan Anda untuk menggabungkan tabel yang berisi data terkait berdasarkan kolom yang merupakan bagian dari kedua tabel

Sayangnya, belum ada operator GABUNG untuk fungsi QUERY, seperti yang ada di SQL. Jika Anda ingin melihat semua cara untuk mencari data, lihat postingan kami tentang 7 cara mencari data di Google Sheets

Inilah cara menggabungkan dua tabel di dalam fungsi QUERY

Anda memiliki situasi di sini. Tabel Persediaan yang telah kita gunakan tampak tidak lengkap karena Biaya Satuan bukan merupakan bagian darinya. Kami telah menyimpan Biaya Unit berdasarkan Produk di dalam tab baru bernama Data 2

Anda ingin menggabungkan dua tabel bersama-sama berdasarkan kolom Produk untuk menggunakan Biaya Satuan untuk perhitungan lebih lanjut

Menggabungkan dua tabel bersama menggunakan fungsi QUERY agak rumit karena, sayangnya, tidak ada klausa JOIN yang tersedia seperti di SQL

Anda perlu mengambil bantuan ARRAYFORMULA dan fungsi VLOOKUP untuk menyelesaikannya di dalam lembar google

= QUERY ( Data!A1:G1001, "SELECT D, F" )

Rumus untuk menyelesaikan ini bersama dengan tangkapan layar adalah seperti di atas

Penjelasan

• Kami memulai kurung kurawal dan menggunakan rentang tabel pertama. Kemudian kami memulai fungsi ARRAYFORMULA. Fungsi ini memungkinkan Anda untuk menggunakan larik di dalam fungsi VLOOKUP yang merupakan fungsi non-array
• Kemudian kami menggunakan fungsi VLOOKUP untuk mencari nilai D1. D1001 dari tabel yang ada di dalam tab Data. Kami mencari nilai-nilai itu ke dalam tabel dari tab Data 2 dan mengambil kolom kedua (mis. e. , Biaya Satuan)
• Terakhir, kami menggunakan klausa SELECT untuk mengembalikan seluruh output

Catatan

1. Biasanya, VLOOKUP menggunakan nilai sel tunggal sebagai nilai pencarian. Tapi dalam kasus ini, kita menggunakan seluruh array (D1. D1001) sebagai nilai pencarian. Agar hal ini terjadi, kami telah menyertakan VLOOKUP di dalam ARRAYFORMULA
2. Harus selalu ada kolom yang umum di antara dua tabel

Anda dapat menggunakan rumus ini untuk menggabungkan tabel yang berbeda bersama-sama dan meningkatkan keterampilan manipulasi data Anda

Kesimpulan

Fungsi QUERY lembar google adalah cara yang disempurnakan untuk memanipulasi data dengan cara seperti SQL

Bahasa yang digunakan untuk menulis argumen di dalam fungsi adalah pseudo-SQL, bernama Google Visualization API Query Language

Fungsi ini dapat melakukan tugas yang mirip dengan SQL dalam hal pengelompokan, pengurutan, agregasi, pemilihan data, dll

Selalu ada lebih banyak untuk dipelajari tentangnya, dan ini bukanlah akhir. Di masa mendatang, kami akan membuat artikel yang menekankan cara yang lebih canggih dalam menggunakan fungsi QUERY di dalam google sheets

Bagaimana cara menanyakan beberapa kolom di Google Sheets?

Bagaimana cara mengelompokkan berdasarkan kueri di Google Sheets?

Bagaimana cara memilih beberapa kolom dalam kueri pemilihan?

Bagaimana cara mengelompokkan banyak kolom dalam lembaran?