Bagaimana cara menambahkan rumus secara otomatis di google sheets?

Anda bekerja di dalam Google Spreadsheet di mana rumus harus disalin ke baris terakhir lembar. Anda juga memerlukan rumus untuk ditambahkan secara otomatis saat baris baru ditambahkan ke Google Sheet

Ada beberapa cara untuk mengatasi masalah ini

Salin Formula Turun di Google Sheets

Pendekatan termudah untuk menyalin rumus adalah dengan menggunakan gagang isian di Google Sheets. Tulis formula Anda di baris pertama spreadsheet Anda, lalu arahkan mouse Anda ke pojok kanan bawah sel formula

Penunjuk berubah menjadi gagang isian (simbol tambah hitam) yang dapat Anda seret ke baris terakhir lembar. Pegangan isian tidak hanya akan menyalin rumus ke semua sel yang berdekatan tetapi juga menyalin pemformatan visual

Jika Anda perlu menyalin rumus di seluruh sel tetapi tanpa pemformatan apa pun, pilih sel yang berisi pemformatan dan tekan Ctrl+C untuk menyalinnya ke papan klip. Selanjutnya, pilih range dimana formula tersebut perlu diterapkan, klik kanan, pilih Paste Special dan Paste Formula only

Terapkan Formula ke Seluruh Kolom di Google Sheets

Jika Anda memiliki ratusan baris dalam Google Spreadsheet dan Anda ingin menerapkan rumus yang sama ke semua baris kolom tertentu, ada solusi yang lebih efisien daripada salin-tempel - Rumus Array

Sorot sel pertama di kolom dan ketik rumus seperti sebelumnya. Namun, alih-alih menentukan satu sel sebagai parameter, kami akan menentukan seluruh kolom menggunakan notasi B2:B (mulai dari sel B2 dan turun ke baris terakhir kolom B)

Kemudian tekan Ctrl+Shift+Enter, atau Cmd+Shift+Enter di Mac, dan Google Sheets akan secara otomatis mengelilingi rumus Anda dengan fungsi ARRAYFORMULA

Dengan demikian, kami dapat menerapkan rumus ke seluruh kolom spreadsheet hanya dengan satu sel. Formula Array lebih efisien karena mereka memproses sekumpulan baris sekaligus. Mereka juga lebih mudah dipertahankan karena Anda hanya perlu memodifikasi satu sel untuk mengedit rumus

Satu masalah yang mungkin Anda perhatikan dengan rumus di atas adalah rumus ini berlaku untuk setiap baris dalam kolom di mana Anda hanya ingin menambahkan rumus ke baris yang berisi data dan melewati baris kosong

Hal ini dapat dilakukan dengan menambahkan isi IF ke ARRAYFORMULA kita sehingga tidak menerapkan rumus pada salah satu baris kosong

Google Spreadsheet menawarkan dua fungsi untuk membantu menguji apakah sebuah sel kosong atau tidak

• ISBLANK(A1)_ - Mengembalikan BENAR jika sel yang direferensikan kosong
• LEN(A1) <> 0 - Mengembalikan TRUE jika sel yang direferensikan tidak kosong, FALSE jika tidak

Oleh karena itu, Rumus Array kami yang dimodifikasi akan terbaca

Menggunakan ISBLANK(Referensi Sel)

Ada beberapa cara lain untuk menguji apakah sebuah sel kosong atau tidak

=ArrayFormula(IF(ISBLANK(B2:B), "", ROUND(B2:B*18%, 2)))
=ArrayFormula(IF(LEN(B2:B)<>0, ROUND(B2:B*18%, 2), ""))
=ArrayFormula(IF(B2:B="", "", ROUND(B2:B*18%, 2)))

Gunakan Rumus Array di dalam Tajuk Kolom

Dalam contoh kami sebelumnya, teks judul kolom (seperti Pajak, Jumlah Total) telah diisi sebelumnya dan rumus hanya ditambahkan ke baris pertama kumpulan data.

Kami dapat lebih meningkatkan formula kami sehingga dapat diterapkan ke tajuk kolom itu sendiri. Jika indeks baris saat ini adalah 1, dihitung menggunakan fungsi ROW(), rumus akan menampilkan judul kolom selain itu perhitungan akan dilakukan menggunakan rumus

=ArrayFormula(IF(ROW(B:B)=1,"Tax",IF(ISBLANK(B:B),"",ROUND(B:B*18%, 2))))

Formula Isi Otomatis ke dalam Pengajuan Formulir Google

Fungsi ARRAYFORMULA sangat berguna untuk Google Forms saat respons formulir disimpan di dalam Google Sheet. Anda tidak dapat melakukan penghitungan langsung di dalam Formulir Google tetapi dapat dilakukan di dalam spreadsheet yang mengumpulkan respons

Anda dapat membuat kolom baru di dalam Google Spreadsheet dan menerapkan ARRAYFORMULA ke baris pertama kolom yang ditambahkan

Ketika pengiriman formulir baru diterima, baris baru akan ditambahkan ke Google Sheet dan formula akan digandakan dan secara otomatis diterapkan ke baris baru tanpa Anda harus menyalin-menempelkan barang.

Lihat juga. Konversi Respons Formulir Google ke Dokumen PDF

Cara Menggunakan VLOOKUP di dalam ARRAYFORMULA

Anda dapat menggabungkan ARRAYFORMULA dengan VLOOKUP untuk melakukan pencarian dengan cepat di seluruh kolom

Katakanlah Anda memiliki lembar "Buah" yang mencantumkan nama buah di kolom A dan harga yang sesuai di kolom B. Lembar kedua "Pesanan" memiliki nama buah di kolom A, jumlah di kolom B dan Anda harus menghitung jumlah pesanan di kolom C

=ArrayFormula(
  IF(ROW(A:A)=1,
  "Total",
  IF(NOT(ISBLANK(A:A)), VLOOKUP(A:A, Fruits!A2:B6, 2, FALSE) * B:B, "")))

Dalam bahasa Inggris sederhana, jika baris sel saat ini adalah 1, tampilkan judul kolom dalam teks biasa. Jika baris lebih besar dari 1 dan kolom A dari baris saat ini tidak kosong, lakukan VLOOKUP untuk mengambil harga item dari lembar Buah. Kemudian gandakan harga itu dengan kuantitas di sel B dan hasilkan nilainya di sel C

Jika rentang VLOOKUP Anda ada di Google Spreadsheet lain, gunakan fungsi IMPORTRANGE() dengan ID Google Spreadsheet lain

Harap perhatikan bahwa Anda mungkin harus menggunakan titik koma dalam rumus spreadsheet, bukan koma untuk beberapa bahasa lokal

Google Membentuk Rumus dan Fungsi Google Sheets

BagikanWhatsAppTwitterFacebook

Amit Agarwal

Pakar Pengembang Google, Juara Google Cloud

Amit Agarwal adalah Pakar Pengembang Google di Google Workspace dan Google Apps Script. Dia memegang gelar insinyur di bidang Ilmu Komputer (I. I. T. ) dan merupakan blogger profesional pertama di India

Amit telah mengembangkan beberapa add-on Google yang populer termasuk Mail Merge untuk Gmail dan Document Studio. Baca selengkapnya di Lifehacker dan YourStory

Bagaimana cara menerapkan formula secara otomatis di Google Sheets?

Bagaimana cara mengisi otomatis formula saat memasukkan baris di Google Sheets?

Bagaimana cara menambahkan formula ke setiap sel di Google Sheets?

Bagaimana cara membuat formula diperbarui secara otomatis di Google Sheets?