Cara menggunakan kueri google sheets col1

Penggunaan rumus Bulan di Excel atau Google Sheet akan menghasilkan Index bulan pada tanggal yang kita cek. Fungsi Bulan ini juga dapat digunakan dalam fungsi Kueri di Google Sheet. Fungsi Bulan pada Formula dan fungsi Bulan pada Query memiliki fungsi yang sama yaitu menghasilkan indeks bulan. Namun terdapat sedikit perbedaan pada hasil indeks Bulan dari kedua formula tersebut

Perbedaan antara hasil yang dihasilkan oleh Fungsi Bulan dan Fungsi Kueri adalah bahwa Indeks dimulai dari 0. Jadi untuk Januari berisi 0, Februari berisi 1 dan seterusnya. sedangkan pada Formula Month Index dimulai dari 1. Namun hal ini tidak akan terlalu sulit karena sangat mudah diatasi dengan sedikit trik

Penggunaan Bulan dalam Fungsi Kueri

Dalam tutorial ini, kita tidak hanya akan mempelajari cara menggunakan fungsi Bulan tetapi juga mempelajari cara membuat laporan berdasarkan bulan dengan Fungsi Kueri yang sangat sederhana. Contoh datanya seperti di bawah ini

Dengan kasus yang lebih sederhana dulu, disini kita akan mencoba membuat rumus yang akan menampilkan Jumlah Buah berdasarkan Bulan. Formula yang digunakan seperti ini

=query(A1:C10,"Select Month(A)+1 , SUM(C) Group By A")

dan hasilnya akan seperti ini

pada rumus di atas ada Bulan(A) + 1, ditambah 1 dimaksudkan untuk mengecoh Index bulan yang dihasilkan Bulan pada Query mulai dari 0. Kita juga bisa mengubah Judul kolom di sum(month(Date)1()) dengan menggunakan Formula Label yang sudah saya bahas pada artikel berikut. Cara mengubah Nama Kolom di Fungsi Kueri

Setelah menerapkan Label Hasilnya akan seperti ini

Untuk rumus yang lebih kompleks, kami juga menampilkan data per bulan dengan jenis buah yang masih, dengan rumus seperti ini

=query(A1:C10,"Select Month(A)+1 , B,SUM(C) Group By A,B Label Month(A)+1 'Bulan'")

Tidak sampai disini, dengan rumus query ini kita juga bisa membuat tabel dari hasil query diatas, kita masukan kembali ke rumus query untuk menghasilkan laporan pada jumlah buah per bulan dengan data yang mudah dibaca. rumusnya adalah

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. Ini 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

1. 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
2. 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

1. Menyoroti sel yang Anda minati, atau
2. Memberi nama kumpulan data dan ketikkan nama kumpulan Anda. Anda dapat melakukan ini dengan (lihat tangkapan layar di bawah)

1. Memilih sel yang berisi data Anda
2. Mengklik "Data" pada menu di atas
3. Mengklik "Rentang Bernama"
4. 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 tanyakan 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

Klausul Penggunaan

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 yang berbeda dalam kolom menjadi kolom baru
ORDER BY Mengurutkan baris berdasarkan 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 Meminta beberapa lembar/rentang/tab secara bersamaan

Jika Anda ingin membuat kueri beberapa lembar/rentang/tab dalam spreadsheet yang sama, Anda dapat melakukannya menggunakan fungsi kueri

⚠️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

1. Nama
2. # transaksi minggu ini
3. # transaksi minggu lalu
4. 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