Cara Menggunakan Rumus dengan Tanggapan Formulir Google di Spreadsheet

Saat orang mengirimkan Formulir Google Anda, baris baru dimasukkan ke dalam Google Sheet yang menyimpan tanggapan formulir. Baris spreadsheet ini berisi kolom Timestamp, tanggal aktual saat formulir dikirimkan, dan kolom lain di lembar berisi semua jawaban pengguna, satu per kolom.

Anda dapat memperluas lembar Google Formulir untuk juga menyertakan bidang rumus dan nilai sel dihitung secara otomatis setiap kali baris baru ditambahkan ke lembar oleh Formulir Google. Contohnya:

• Anda dapat memiliki rumus penomoran otomatis yang menetapkan peningkatan otomatis tetapi ID berurutan untuk setiap respons formulir. Ini dapat berguna saat Anda menggunakan Google Formulir untuk pembuatan faktur.
• Untuk formulir pesanan pelanggan, formula dapat ditulis di Google Spreadsheet untuk menghitung jumlah total berdasarkan pemilihan item, negara (tarif pajak berbeda) dan jumlah yang dipilih dalam formulir.
• Untuk formulir reservasi hotel, formula dapat secara otomatis menghitung sewa kamar berdasarkan tanggal check-in dan check-out yang diisi oleh pelanggan di Google Form.
• Untuk kuis, seorang guru dapat secara otomatis menghitung skor akhir siswa dengan mencocokkan nilai yang dimasukkan dalam formulir dengan jawaban yang sebenarnya dan menetapkan skor.
• Jika pengguna telah membuat beberapa pengiriman formulir, rumus dapat membantu Anda menentukan jumlah total entri yang dibuat oleh pengguna segera setelah mereka mengirimkan formulir.

Rumus Google Spreadsheet untuk Google Formulir

Dalam panduan langkah demi langkah ini, Anda akan mempelajari cara menambahkan rumus ke Google Spreadsheet yang terkait dengan Google Formulir. Nilai sel yang sesuai di baris respons akan dihitung secara otomatis saat respons baru dikirimkan.

Untuk mendapatkan pemahaman yang lebih baik tentang apa yang ingin kami capai, buka Formulir Google ini dan kirimkan tanggapan. Selanjutnya, buka Google Sheet ini dan Anda akan menemukan respons Anda di baris baru. Kolom FK diisi otomatis menggunakan rumus.

Semua contoh di bawah ini akan menggunakan fungsi ArrayFormula dari Google Sheets meskipun beberapa contoh ini juga dapat ditulis menggunakan fungsi FILTER .

Tanggapan Formulir Nomor Otomatis dengan ID Unik

Buka Google Sheet yang menyimpan respons formulir, buka kolom kosong pertama dan salin-tempel rumus berikut di baris #1 kolom kosong.

 =ArrayFormula( IFS( ROW(A:A)=1, "Invoice ID", LEN(A:A)=0, IFERROR(1/0), LEN(A:A)>0, LEFT(CONCAT(REPT("0",5), ROW(A:A) -1),6) ) )

Fungsi ROW() mengembalikan nomor baris dari baris respons saat ini. Ini mengembalikan 1 untuk baris pertama di Kolom Faktur dan dengan demikian kami menetapkan judul kolom di baris pertama. Untuk baris berikutnya, jika kolom pertama dari baris (biasanya Timestamp) tidak kosong, ID invoice dibuat secara otomatis.

ID akan seperti 00001 , 00002 dan seterusnya. Anda hanya perlu menempatkan rumus di baris pertama kolom dan formula akan otomatis mengisi semua baris lainnya di kolom.

Fungsi IFERROR mengembalikan argumen pertama jika itu bukan nilai kesalahan, sebaliknya mengembalikan argumen kedua jika ada, atau kosong jika argumen kedua tidak ada. Jadi dalam hal ini 1/0 adalah kesalahan dan karenanya selalu mengembalikan nilai kosong.

Rumus Perhitungan Tanggal untuk Google Forms

Formulir Google Anda memiliki dua bidang tanggal - tanggal check-in dan tanggal check-out. Tarif hotel dapat bervariasi setiap musim sehingga Anda memiliki tabel terpisah di Google Sheet yang mengatur sewa kamar per bulan.

Kolom C di Google Sheet menyimpan tanggapan untuk tanggal check-in sementara kolom D menyimpan tanggal check-out.

 =ArrayFormula( IF(ROW(A:A) = 1, "Room Rent", IF(NOT(ISBLANK(A:A)), (D:D - C:C) * VLOOKUP(MONTH(D:D), 'Room Rates'!$B$2:$C$13,2, TRUE), "" ) ) )

Rumus menggunakan VLOOKUP untuk mendapatkan tarif kamar untuk tanggal perjalanan yang ditentukan dalam respons formulir dan kemudian menghitung sewa kamar dengan mengalikan sewa kamar dengan durasi menginap.

Rumus yang sama juga dapat ditulis dengan IFS alih-alih VLOOKUP

 =ArrayFormula( IF(ROW(A:A) = 1, "Room Rent", IFS(ISBLANK(C:C), "", MONTH(C:C) < 2, 299, MONTH(C:C) < 5, 499, MONTH(C:C) < 9, 699, TRUE, 199 ) ) )

Hitung Jumlah Pajak Berdasarkan Nilai Faktur

Dalam pendekatan ini, kita akan menggunakan fungsi FILTER dan itu bisa menghasilkan rumus yang tidak terlalu rumit daripada menggunakan fungsi IF . Kelemahannya adalah Anda harus menulis judul kolom di baris #1 dan menempelkan rumus di baris #2 (jadi satu respons formulir harus ada agar rumus berfungsi).

 =ArrayFormula(FILTER(E2:E, E2:E<>"")*1.35)

Di sini kami menerapkan pajak 35% ke nilai faktur dan rumus ini harus ditambahkan di baris #2 kolom berjudul "Jumlah Pajak" seperti yang ditunjukkan pada tangkapan layar.

Tetapkan Skor Kuis di Google Formulir

Kota manakah yang disebut sebagai apel besar? Ini adalah pertanyaan jawaban singkat di Google Formulir sehingga siswa dapat memberikan tanggapan seperti New York, New York City, NYC dan mereka akan tetap benar. Guru harus memberikan 10 poin untuk jawaban yang benar.

 =ArrayFormula( IF(ROW(A:A) = 1, "Quiz Score", IFS( ISBLANK(A:A), "", REGEXMATCH(LOWER({B:B}), "new\s?york"), 10, {B:B} = "NYC", 10, TRUE, 0 ) ) )

Dalam rumus ini, kami menggunakan fungsi IFS yang seperti pernyataan IF THEN dalam pemrograman. Kami menggunakan REGEXMATCH untuk mencocokkan nilai seperti New York, New York, newyork sekaligus menggunakan ekspresi reguler.

Fungsi IFS mengembalikan NA jika tidak ada kondisi yang benar, jadi kami menambahkan pemeriksaan TRUE di akhir yang akan selalu dievaluasi ke true jika tidak ada kondisi sebelumnya yang cocok dan mengembalikan 0 .

Ekstrak Nama Depan Form Responden

Jika Anda memiliki bidang formulir yang meminta pengguna untuk menyebutkan seluruh nama lengkap mereka, Anda dapat menggunakan fungsi Google Spreadsheet untuk mengekstrak nama depan dari nama lengkap dan menggunakan bidang itu untuk mengirim email yang dipersonalisasi.

 =ArrayFormula( IFS( ROW(A:A)=1, "First Name", LEN(A:A)=0, IFERROR(1/0), LEN(A:A)>0, PROPER(REGEXEXTRACT(B:B, "^[^\s+]+")) ) )

Kami telah menggunakan metode RegexExtract di sini untuk mengambil string sebelum spasi pertama di bidang nama. Fungsi PROPER akan mengkapitalisasi huruf pertama dari nama jika pengguna memasukkan nama mereka dalam huruf kecil.

Temukan Duplikat Pengiriman Formulir Google

Jika Formulir Google Anda adalah kumpulan alamat email, Anda dapat menggunakan bidang itu untuk dengan cepat mendeteksi tanggapan yang telah dikirimkan oleh pengguna yang sama beberapa kali.

 =ArrayFormula( IFS( ROW(A:A)=1, "Is Duplicate Entry?", LEN(A:A)=0, IFERROR(1/0), LEN(A:A)>0, IF(COUNTIF(B:B, B:B) > 1, "YES", "") ) )

Dengan asumsi bahwa Kolom B menyimpan alamat email dari responden formulir, kita dapat menggunakan fungsi COUNTIF untuk menandai entri duplikat dengan cepat dalam spreadsheet tanggapan kita. Anda juga dapat menggunakan pemformatan bersyarat di Spreadsheet untuk menyorot baris yang mungkin merupakan entri duplikat.

Respons Formulir Email dengan Nilai IsiOtomatis

Anda dapat menggunakan Document Studio untuk secara otomatis mengirim email ke responden formulir. Email dikirim setelah nilai formular terisi otomatis oleh Google Sheet. Respons formulir asli dan nilai yang dihitung juga dapat disertakan dalam dokumen PDF yang dihasilkan.