Rumus Google Sheets: QUERY, ARRAYFORMULA, IMPORTRANGE

Rumus dasar

Dengan lokal Indonesia (File → Setelan → Lokal), argumen dipisah titik koma dan desimal ditulis dengan koma; contoh di sini memakai koma seperti lembar latihan.

• Tanggal − tanggalLatih: Angka, teks, tanggal, dan pengaturan lokal Indonesia

  Tanggal di Sheets adalah angka hari, jadi pengurangan dua tanggal memberi jumlah hari dan tanggal + angka memberi tanggal lain.

  =C2-B2

• &Latih: Angka, teks, tanggal, dan pengaturan lokal Indonesia

  Menggabungkan teks. Tanda + untuk teks memunculkan #VALUE!.

  ="RT "&A2

• $F$1Latih: Referensi relatif dan absolut

  Referensi absolut: alamat yang dikunci tidak bergeser saat rumus disalin ke baris lain.

  =C2*$F$1

• SUM / AVERAGE / MIN / MAXLatih: SUM, AVERAGE, MIN, MAX, dan COUNT

  Jumlah, rata-rata, nilai terkecil, dan nilai terbesar dari satu rentang. Sel kosong dan teks dilewati.

  =AVERAGE(C2:C7)

• COUNT / COUNTALatih: SUM, AVERAGE, MIN, MAX, dan COUNT

  COUNT menghitung sel berisi angka; COUNTA menghitung semua sel yang terisi, termasuk teks.

  =COUNT(C2:C7)

• IFLatih: COUNTIF, SUMIF, dan IF

  Memberi keterangan berdasarkan satu syarat: hasil pertama bila benar, hasil kedua bila salah.

  =IF(C2>=2,"Penuh","Sebagian")

• COUNTIFLatih: COUNTIF, SUMIF, dan IF

  Menghitung sel yang memenuhi satu syarat.

  =COUNTIF(B2:B9,"Hadir")

• SUMIFLatih: COUNTIF, SUMIF, dan IF

  Menjumlahkan nilai pada baris yang memenuhi satu syarat; rentang yang dijumlahkan ditulis terakhir.

  =SUMIF(B2:B9,"Hadir",C2:C9)

Mencari dan menyaring

• VLOOKUP(…, FALSE)Latih: Mengambil data dari tabel lain dengan VLOOKUP

  Mengambil nilai dari tabel rujukan berdasarkan kode di kolom pertamanya. FALSE berarti kodenya harus sama persis.

  =VLOOKUP(A2,$F$2:$G$6,2,FALSE)

• IFERRORLatih: Mengambil data dari tabel lain dengan VLOOKUP

  Mengganti galat, misalnya #N/A dari kode yang tidak ada, dengan nilai pilihan.

  =IFERROR(VLOOKUP(A2,$F$2:$G$6,2,FALSE),"Tidak ada")

• FILTERLatih: Mengurutkan dan memfilter data

  Mengambil baris yang memenuhi syarat dan menumpahkannya ke bawah, tanpa mengubah tabel asal.

  =FILTER(A2:A9,C2:C9=0)

• SORTLatih: Mengurutkan dan memfilter data

  Salinan tabel yang terurut menurut satu nomor kolom: TRUE naik, FALSE turun. Urutan data aslinya tidak berubah.

  =SORT(A2:C9,3,FALSE)

QUERY

QUERY tidak ada di Excel. Kuerinya teks di dalam tanda kutip, dan kolom disebut dengan huruf kolomnya, bukan judulnya.

• QUERY(data, kueri, header)Latih: QUERY: menyaring dan meringkas dengan bahasa kueri

  Menjalankan kueri bergaya SQL atas satu rentang dan menumpahkan hasilnya sebagai tabel baru. Argumen header adalah jumlah baris judul, bukan TRUE atau FALSE.

  =QUERY(A1:D20,"select B, D where C='Sarana'",1)

• select … group by … order byLatih: QUERY: menyaring dan meringkas dengan bahasa kueri

  Mengelompokkan dan meringkas dalam satu rumus: tiap kategori muncul sekali beserta totalnya, terurut.

  =QUERY(A1:D20,"select C, sum(D) where D>0 group by C order by sum(D) desc",1)

Fungsi larik khas Google

FILTER, SORT, UNIQUE, dan QUERY sudah tumpah sendiri; ARRAYFORMULA hanya untuk fungsi satu sel yang diberi rentang.

• ARRAYFORMULALatih: ARRAYFORMULA dan fungsi larik khas Google

  Membuat satu rumus berlaku untuk seluruh rentang, misalnya IF atas satu kolom penuh. Ctrl+Shift+Enter saat menyunting rumus menambahkannya.

  =ARRAYFORMULA(IF(C2:C50>=50000,"Lunas","Belum"))

• UNIQUELatih: ARRAYFORMULA dan fungsi larik khas Google

  Daftar nilai tanpa kembar, tumpah ke bawah.

  =UNIQUE(B2:B50)

• COUNTUNIQUELatih: ARRAYFORMULA dan fungsi larik khas Google

  Banyaknya nilai berbeda dalam satu angka, tanpa membuat daftar UNIQUE lebih dulu.

  =COUNTUNIQUE(A2:A201)

• SPLITLatih: ARRAYFORMULA dan fungsi larik khas Google

  Memecah satu teks menjadi beberapa sel di baris yang sama. Tiap huruf pemisah dihitung sendiri kecuali argumen ketiga FALSE.

  =SPLIT(A2,";")

• JOINLatih: ARRAYFORMULA dan fungsi larik khas Google

  Kebalikan SPLIT: menyambung isi larik satu dimensi menjadi satu teks dengan pemisah.

  =JOIN(", ",A2:A6)

• FLATTENLatih: ARRAYFORMULA dan fungsi larik khas Google

  Menumpuk nilai dari satu atau beberapa rentang menjadi satu kolom; sel kosong ikut terbawa.

• SORTNLatih: ARRAYFORMULA dan fungsi larik khas Google

  n baris teratas setelah diurutkan; tanpa argumen n, yang keluar hanya satu baris.

• SPARKLINELatih: ARRAYFORMULA dan fungsi larik khas Google

  Grafik mini di dalam satu sel; opsi charttype menerima line, bar, column, dan winloss.

Antar-spreadsheet

• IMPORTRANGELatih: IMPORTRANGE: menyambung dua spreadsheet dan izinnya

  Menarik rentang dari spreadsheet lain. URL dan rentang ditulis sebagai teks; tanpa nama sheet, yang ditarik sheet pertama.

  =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123","Kas!A1:F30")

• #REF! di IMPORTRANGELatih: IMPORTRANGE: menyambung dua spreadsheet dan izinnya

  Sambungan pertama ke sumber baru menunggu izin: tekan Izinkan akses di pesan selnya. Membungkusnya dengan IFERROR ikut menyembunyikan pesan itu.