Ada banyak faktor yang membuat Google Sheet semakin lambat. Maka, ada beberapa rekomendasi dan strategi untuk mengoptimasi lembar kerja Google Sheet anda.
Beberapa Optimasi Google Sheet :
Klik untuk langsung menuju topik yang anda inginkan
- Mengenali Google Sheets yang Lambat
- Ketahuilah keterbatasan Google Sheet
- Menganalisis ukuran file Google Sheet
- Mengukur kecepatan perhitungan Google Sheet
- Hapus cell yang tidak digunakan
- Konversikan formula menjadi nilai statis jika memungkinkan
- Gunakan rentang statis sebagai referensi
- Hapus fungsi volatile (dinamis) atau gunakan dengan hati-hati
- Strategi Vlookup
- Strategi Index-Match
- Strategi fungsi Query
- Strategi ArrayFormula
- Strategi Penggunaan Import
- Strategi Fungsi GoogleFinance
- Gunakan pernyataan IF untuk mengelola panggilan formula
- Kelola perhitungan besar dengan kontrol switch
- Gunakan fungsi Filter, Unique, dan Array_Constrain untuk membuat tabel bantuan yang lebih kecil
- Hindari perhitungan berantai panjang
- Referensi data pada halaman tab yang sama
- Gunakan kolom bantuan
- Pisahkan file Google Sheet yang lambat menjadi beberapa file terpisah
- Gunakan Conditional Formating secara terbatas
- Manfaatkan keunggulan Apps Script
- Gunakan formula khusus/custom secara terbatas
- Tips lainnya
- Memahami perubahan di cloud yang membutuhkan waktu dalam penyebarannya
- Tahu kapan saatnya pindah ke basis data
1. Mengenali Google Sheets yang Lambat
Saat Anda membaca informasi ini, kemungkinan besar anda telah mengenali beberapa atau semua masalah berikut.Perhitungan di Google Sheets Anda sangat lambat dan mulai sering muncul loading bar setiap kali ada perubahan pada Sheet Anda.
Google Sheet Anda menjadi lamban saat merespons klik atau penekanan tombol mouse.
Data tidak langsung ditampilkan di cell, meskipun Anda tahu Anda telah memasukkan data ke dalam cell tersebut. Sebagai contoh, lihat gambar di bawah ini yang mana tidak langsung menampilkan nilai setelah formula diinputkan.
Google Sheet Anda menjadi tidak responsif. Skenario terburuknya, menurut pengalaman, jika respon tersebut tidak segera muncul, kemungkinan besar hal tersebut tidak akan terselesaikan. Jadi jika Anda melihat pesan kesalahan seperti di bawah ini, dan Anda telah menunggu beberapa menit tetapi tidak ada yang terjadi, maka Anda mungkin geram, keluar dari halaman, dan mungkin mencoba kembali namun dengan cara yang berbeda.
Hal ini biasanya disertai dengan bertambah cepatnya putaran kipas angin di komputer!
^ Kembali ke Daftar Isi
2. Ketahuilah keterbatasan Google Sheets
Tentunya jika Anda berurusan dengan file Google Sheets skala besar — Google Sheets dengan data dan/atau formula dalam jumlah besar — maka kemungkinan besar Anda akan merasakan kinerja atau performa yang buruk.Oleh karena itu, pembahasan tentang Google Sheets yang lambat tidak akan lengkap tanpa membahas batasan Google Sheets.
Batas Keseluruhan:
Google Sheets memiliki batas 5 juta cell per file (lihat ukuran file Google). Jika Anda melakukan sesuatu yang melewati batas ini (misal. Menambahkan baris baru atau Sheet baru), Anda akan melihat pesan kesalahan ini:
Batas kolom:
Google Sheets memiliki jumlah kolom maksimum 18.278 kolom. Jika Anda melakukan sesuatu yang melewati batas ini, Anda akan melihat pesan kesalahan berikut:
Batas ukuran cell individual:
Dalam satu cell, ada panjang string maksimum yaitu 50.000 karakter (cukup untuk sekitar 500 kalimat rata-rata). Jika Anda melakukan sesuatu yang melewati batas ini, Anda akan melihat pesan kesalahan berikut:
Jumlah baris maksimum yang dapat Anda tambahkan dalam sekali klik:
Anda dapat menambahkan maksimum 40.000 baris baru sekaligus (Anda dapat melakukan hal ini sebanyak yang Anda inginkan, hingga batas 5 juta cell). Jika Anda mencoba melakukan yang lebih dari itu, Anda akan melihat pesan kesalahan berikut:
^ Kembali ke Daftar Isi
3. Menganalisis ukuran file Google Sheet
Dengan menggunakan Google Sheet Script Editor, Anda dapat dengan cepat membuat alat sederhana untuk menghitung ukuran Lembar Anda.Alat untuk audit file Google Sheets ini menunjukkan jumlah total cell dalam 1 file, dan berapa banyak cell yang memiliki data, dan juga ada detail statistik untuk setiap lembar kerja, termasuk berapa banyak fungsi dinamis (volatile) dan array yang Anda miliki:
Dapatkan salinannya dari Google Sheet Audit Tool yang dibuat oleh Ben Collins, untuk menganalisis ukuran Google Sheets Anda. Kode untuk tool ini juga dapat ditemukan di GitHub jika Anda membutuhkannya. hanya tinggal memasukkan URL google sheet anda, dan klik menu Sheet Size Auditor > Audit Sheet, lalu klik continue (jangan lupa untuk memberi ijin akses saat dijalankan).
^ Kembali ke Daftar Isi
4. Mengukur kecepatan perhitungan Google Sheet
Untuk mengukur "kecepatan" berbagai perhitungan, Anda dapat menggunakan Developer Tools dari Google Chrome.Di jendela browser Anda, Anda dapat mengakses Developer Toolkit / toolkit pengembang dengan menekan Cmd + Option + I di Mac atau Ctrl + Shift + I (di PC). atau juga dapat diakses melalui menu View > Developer.
Di bawah tab Kinerja/Performance pada Developer Tools Anda dapat merekam halaman web (atau Google Sheet dalam kasus ini) saat dalam proses memuat, dan centang kotak screenshot untuk menunjukkan apa yang terjadi beserta waktunya.
Untuk proses perhitungan rumus/formula, Anda dapat mengidentifikasi kapan perhitungan dimulai, mis. ditandai dengan angka 1 pada gambar ini, sekitar detik ke 14:
Angka 2 menunjukkan screenshot saat ini, sehingga dapat dilihat bahwa hasil perhitungan Anda belum ditampilkan. Lalu identifikasi kapan Google Sheet selesai menghitung formula/rumus (kotak merah, sekitar detik ke 32):
Periksa screenshot untuk melihat saat pertama kali hasil ditampilkan di cell. Jadi dapat dilihat berapa lama kumpulan formula ini diproses:
14.05 detik - 32.26 detik = 18,2 detik
Ada jauh lebih banyak fungsi pada Developer Tools, namun ini sudah cukup untuk saat ini.
Ini adalah salah satu metodologi yang digunakan untuk menghitung kecepatan pemrosesan rumus apabila membutuhkan sebuah perbandingan. Terkadang perlu melakukan tes di tempat dan waktu yang sama (jadi perbedaan kecepatan wifi dapat diabaikan) atau juga dapat dicoba dijalankan beberapa kali dan dihitung rata-ratanya.
^ Kembali ke Daftar Isi
5. Hapus Cell yang tidak digunakan
Google Sheets akan menjadi semakin lambat saat content semakin besar/banyak. Jadi salah satu hal yang harus dicoba, sebelum masuk ke dalam keuntungan kecil dari optimasi formula, yaitu mengurangi ukuran Google Sheet Anda.Cell-cell kosong dapat memperlambat kinerja, bahkan mungkin secara dramatis. Jadi, hapuslah kapan saja Anda bisa, sehingga dapat mengurangi jumlah cell yang disimpan oleh Google Sheets dalam memori.
Maksud dari menghapus cell kosong disini adalah menghapus sejumlah besar baris kosong di bawah dataset Anda, atau kolom di sebelah kanan dataset Anda (yang mana tidak berisi data apapun). Misal pada kasus gambar di bawah ini, kita perlu menghapus semua kolom di sebelah kanan data (kolom T, U, V, dst) dan semua baris di bawah data (baris 54.761 hingga baris terakhir). Lihat blok baris dan klom berwarna biru.
Bagaimana dengan baris baru yang ditambahkan secara otomatis?
Jangan khawatir jika data Anda diimport secara otomatis oleh Apps Script atau pihak ketiga seperti Supermetrics atau Tiller, karena baris baru secara otomatis ditambahkan ke Lembar Anda sesuai kebutuhan.
(Namun, Anda tetap harus mengujinya dengan pengaturan atau aplikasi spesifik Anda, untuk mengonfirmasi hal tersebut)
^ Kembali ke Daftar Isi
6. Konversikan formula menjadi nilai statis jika memungkinkan
Setelah Anda menggunakan satu set rumus di data Anda, dan Anda yakin tidak akan membutuhkannya lagi, maka Anda dapat mengonversinya menjadi nilai statis.Hal ini harusnya dapat mengurangi ukuran file, yang akan membantu kinerja Google Sheet secara keseluruhan. Hal ini terkadang juga merupakan praktik terbaik (untuk mengurangi kemungkinan kesalahan yang terjadi jika seseorang atau sesuatu terjadi dan menghancurkan hasil nilai dari formula).
Cara mengubah formula ke nilai statis yaitu dengan menyalinnya (copy) lalu:
Edit > Paste special > Paste values
Lebih baik lagi, pelajari shortcut keyboard untuk mempercepatnya. Sorot/blok rumus yang akan diubah, Ctrl + C pada PC atau Cmd + C pada Mac untuk menyalin/copy dan kemudian Ctrl + Shift + V pada PC atau Cmd + Shift + V pada Mac untuk menempelkan/paste sebagai nilai statis.
Catatan, sangat dianjurkan untuk menyimpan salinan formula "langsung" di bagian atas kolom data di tabel Anda, sehingga Anda memiliki catatan tentang bagaimana perhitungan dilakukan dan juga dapat dengan cepat menggunakannya kembali jika diperlukan:
^ Kembali ke Daftar Isi
7. Gunakan rentang statis sebagai referensi
Referensi rentang statis berarti menggunakan sesuatu seperti A1:B1000 daripada A:B dalam rumus Anda (misal. Anda secara eksplisit menentukan batas rentang Anda).Jika Anda bekerja dengan Lembar besar dan menggunakan referensi dinamis di mana Anda tidak memerlukannya, ini akan menambah overhead tambahan dalam perhitungan Google Sheet Anda.
Mungkin tidak terlihat ada banyak data di kolom A, tetapi Google Sheets akan memeriksa setiap cell, termasuk semua baris yang kosong hingga baris terakhir, jika Anda menggunakan rentang dinamis seperti A:B.
Catatan: Meskipun ini umumnya merupakan ide yang baik untuk mempercepat formula Anda, jelas ada situasi ketika referensi dinamis sangat dibutuhkan, misalnya ketika mengantisipasi data baru. Jadi ada saat mutlak dimana masih memerlukan rentang dinamis untuk memungkinkan data baru dimasukkan dalam perhitungan.
^ Kembali ke Daftar Isi
8. Hapus fungsi volatile (dinamis) atau gunakan dengan hati-hati
Dalam Google Sheets ada empat fungsi, NOW(), TODAY(), RAND() dan RANDBETWEEN(), yang dikenal sebagai fungsi volatil atau dinamis, yang berarti mereka selalu menghitung ulang setiap kali ada perubahan pada file Google Sheet. Maka, semua rumus dependen juga akan dihitung ulang juga (yaitu rumus yang mereferensi atau menyertakan fungsi volatile didalamnya).Hal ini menghabiskan daya pemrosesan dan karenanya dapat berdampak negatif terhadap kinerja Google Sheet, meskipun hal ini hanya akan terlihat jika Anda memiliki jumlah data yang besar.
Sebagai ilustrasi, apabila menyalin/mengcopy 100.000 fungsi TODAY() di kolom A, memerlukan sekitar 5,5 detik.
Kemudian jika menghapus 99.999 formula tersebut dan membiarkan satu saja fungsi TODAY() di Cell A1 dan kemudian memasukkan rumus berikut di sel A2 dalam bentuk $A$1.
Menjalankan rumus $A$1 ini dalam 100.000 sel dalam kolom A membutuhkan waktu sekitar 4,1 detik, jadi hampir 1,5 detik lebih cepat.
Hal ini juga merupakan praktik terbaik untuk dilakukan, karena jika suatu saat perlu membuat perubahan tanggal, maka cukup mengubahnya di satu tempat saja, A1, dan tidak harus mengulang semua formula.
^ Kembali ke Daftar Isi
9. Strategi Vlookup
Gunakan referensi statis, daripada dinamis, untuk tabel pencarian apabila memungkinkan (lihat nomor 7 di atas).Kurangi ukuran tabel pencarian Anda (lookup table), jika mungkin, meskipun hanya memiliki pengaruh kecil. Misalnya, daripada mencari di semua cell, Anda bisa menggunakan fungsi Filter (lihat no. 17 di bawah) untuk membuat tabel bantuan yang lebih kecil untuk pencarian pertama, dan kemudian gunakan hasilnya di VLOOKUP.
Namun, usahakan untuk tidak tergoda membuat fungsi FILTER (atau lainnya) di dalam VLOOKUP dan membuat tabel secara bersamaan, hal ini akan jauh lebih lambat.
Misalnya, rumus di bawah ini kurang bagus dan benar-benar akan memperlambat Google Sheet jika Anda memiliki banyak fungsi seperti ini:
= VLOOKUP ($A1, UNIQUE (FILTER ($K$1:$N$10000, $K$1:$K$10000 < 100)), 2, FALSE)
untuk itu lebih disarankan untuk membuat tabel bantuan terlebih dahulu, dalam kasus ini untuk membuat tabel Filter misal pada cell F1:I10. Selanjutnya digunakan untuk referensi VLOOKUP, menjadi:
= VLOOKUP ($A1, $F$1:$I$10, 2, FALSE)
Menggabungkan VLOOKUP dengan ArrayFormulas untuk memunculkan nilai lookup beberapa kolom dengan rumus tunggal secara signifikan lebih lambat daripada menggunakan beberapa VLOOKUP secara individual.
Pada Microsoft Excel, mengurutkan data dan menggunakan perkiraan kecocokan atau matching (TRUE sebagai argumen terakhir di VLOOKUP) sedikit lebih cepat daripada algoritma exact matching (FALSE sebagai argumen terakhir). Namun, juga "sangat" sedikit lebih cepat di Google Sheets, jadi tidak sepadan dengan kerumitannya.
^ Kembali ke Daftar Isi
10. Strategi Index-Match
Index-Match adalah kombinasi pencarian yang kuat dan layak dipelajari. Dalam konstruksi klasiknya, Anda mungkin pernah melihat rumus seperti ini:= INDEX (Sheet2!$A$2:$P$51, MATCH ($A3, Sheet2!$G$2:$G$51,0)), MATCH (C$1, Sheet2!$A$1:$P$1,0))
di mana Anda memiliki dua fungsi MATCH untuk mencari nomor baris dan kolom (itulah sebabnya index-match-match sangat fleksibel).
Ini bagus untuk sejumlah kecil formula index-match-match, tetapi tidak efisien dalam skala besar.
Katakanlah Anda mencari 10.000 baris dan 10 kolom. Untuk setiap satu cell, Anda menggunakan dua "match" untuk mencari nomor baris dan nomor kolom, sehingga jumlah total perhitungan Anda adalah:
Jumlah fungsi Match untuk baris + Jumlah fungsi Match untuk kolom + Fungsi Index Match =
(10.000 baris * 10 Match) + (10.000 baris * 10 Match) + (10.000 baris * 10 indeks) =
300.000 perhitungan
Alangkah lebih baik lagi jika membuat baris dan kolom bantuan yang menggunakan match terlebih dahulu, seperti pada gambar di bawah ini:
Dengan membagi kedua pencarian match ke dalam baris dan kolom tersendiri, Anda dapat menghitung semua match hanya sekali, kemudian menggunakan hasilnya dalam fungsi Index.
Sekarang Anda hanya perlu menghitung fungsi indeks ketika Anda melakukan perhitungan, sehingga total perhitungan Anda sekarang adalah:
(10.000 Match untuk baris) + (10 Match untuk kolom) + (10.000 * 10 Index) =
110.010 perhitungan
Perhitungannya secara signifikan berkurang dari sebelumnya, hanya sekitar 1/3 perhitungan yang harus dilakukan!
^ Kembali ke Daftar Isi
11. Strategi fungsi Query
Seperti yang telah disinggung di atas, menggunakan referensi rentang dinamis dapat mempengaruhi kinerja karena mungkin secara tidak sengaja formula yang ada masih memperhitungkan ribuan baris kosong (lihat no. 7 di atas).Oleh karena itu, gunakan referensi rentang statis atau buat tabel pembantu yang lebih kecil, yang hasilnya dijadikan input untuk fungsi Query agar dapat mempercepat kinerja.
Misalnya, saat menjalankan tes dengan fungsi QUERY, Ben Collins menemukan fakta bahwa setiap penambahan 20.000 baris kosong di dalam perhitungan rentang dinamis, hal tersebut akan menambah 1 detik pada perhitungan.
Jadi daripada rumus seperti ini misalnya:
= QUERY (Sheet2!A:E, "select *", 1)
Lebih baik dengan menetapkan rentang secara eksplisit semisal:
= QUERY (Sheet2!A1:E100000, "select *", 1)Hal ini akan meningkatkan kecepatan fungsi Query karena tidak perlu menghitung semua baris kosong di bawah dataset di Sheet 2.
^ Kembali ke Daftar Isi
12. Strategi ArrayFormula
Apabila Anda pernah menghabiskan waktu membaca forum-forum, Anda akan menemukan banyak orang yang mengatakan jika menggunakan satu ArrayFormula untuk menggantikan ratusan formula individu akan menjadi lebih cepat, dan sebagian yang lain dengan tegas mengatakan sebaliknya. Jadi yang mana yang benar?
Dalam pengalaman, ArrayFormula yang bekerja pada dataset besar (yang memiliki banyak baris) tenyata lambat dan terkadang akan membuat Google Sheet menjadi lamban. Dalam kasus ini, ArrayFormula cenderung lebih lambat daripada formula individual.
Namun, ArrayFormula sangat berguna dalam situasi tertentu, seperti mengumpulkan data dari Google Form, maka ArrayFormula masih direkomendasikan untuk dipelajari dan digunakan, dengan hati-hati.
ArrayFormula banyak digunakan untuk menjalankan perhitungan pada dataset yang selalu berubah dan tumbuh seiring waktu (baris baru yang sering ditambahkan). Dengan begitu perhitungan akan dijalankan secara tomatis tanpa perlu merubah apa pun.
Misalnya, rumus ini:
= ArrayFormula (IF (A2:A <> "", A2: A * 100, ""))
yang merupakan versi array dari formula individual berikut:
= IF(A2 <> "", A2 * 100, "")
akan secara otomatis menghitung nilai untuk setiap baris data baru yang ditambahkan.
Anda perlu menguji kedua versi tersebut pada Google Sheet Anda sendiri, dan memilih mana yang paling tepat untuk kasus anda (lebih mementingkan kecepatan ataukah perhitungan otomatis dalam array). Pendekatan formula individual memiliki manfaat tambahan, yaitu lebih mudah untuk di debug dan di atur.
Jika Anda ingin tetap menggunakan ArrayFormula, perlu dipertimbangankan untuk mengarsipkan data historis secara berkala (lihat no. 21 di bawah), untuk menjaga dataset agar tetap dapat dikelola.
^ Kembali ke Daftar Isi
13. Strategi Penggunaan Import
Fungsi ImportHtml, ImportFeed, ImportData, ImportXml, dan ImportRange mengambil data dari sumber eksternal ke Google Sheet Anda, sehingga fungsi-fungsi tersebut membutuhkan koneksi Internet agar dapat berfungsi.
Mereka jauh lebih lambat dari fungsi-fungsi lain yang beroperasi sepenuhnya dalam satu file, sehingga hal tersebut berpotensi mempengaruhi kinerja Google Sheet Anda.
Misalnya, menggunakan ImportRange untuk mengimpor data dalam jumlah besar dari file Google Sheet lain akan membutuhkan beberapa waktu untuk dimuat dan terkadang ada pesan kesalahan... untuk sementara waktu:
Pendekatan terbaik di sini adalah meminimalkan jumlah panggilan eksternal yang diperlukan.
Misalnya, dengan ImportRange Anda bisa membuat satu panggilan untuk mendapatkan seluruh dataset, meskipun poin di atas mengatakan bahwa ini merupakan operasi yang lambat. Kemudian melakukan filter atau pekerjaan lainnya secara lokal. Hal ini lebih baik daripada melakukan beberapa panggilan ImportRange.
Catatan, Anda dibatasi hingga maksimum 50 fungsi ImportData dalam satu spreadsheet (Lihat Disini).
Tak satu pun dari rumus Impor lainnya yang memiliki batas eksplisit seperti ini di halaman dokumentasinya dan beberapa pengujian terbatas menunjukkan bahwa jikapun ada batasan, mereka pasti lebih dari 50.
Secara umum, fungsi-fungsi import ini dihitung ulang dengan periode sebagai berikut (lihat dokumentasi):
ImportRange: Setiap 30 menit
ImportHtml, ImportFeed, ImportData, ImportXml: Setiap 1 jam
^ Kembali ke Daftar Isi
14. Strategi Fungsi GoogleFinance
Fungsi GoogleFinance adalah fungsi lain yang mengambil data dari sumber eksternal, sehingga memerlukan koneksi internet. Seperti rumus Impor di atas, hal ini akan lebih lambat daripada fungsi biasa yang hanya beroperasi di dalam Google Sheet.
Sekali lagi, strategi umum di sini adalah mengurangi jumlah panggilan eksternal yang menggunakan fungsi GoogleFinance.
Jika memungkinkan, misalnya, cobalah untuk mengambil harga saham dalam satu tahun penuh dengan formula tunggal, daripada menggunakan ratusan fungsi harian.
Dalam dokumentasinya, data GoogleFinance mungkin dapat tertunda hingga 20 menit.
^ Kembali ke Daftar Isi
15. Gunakan pernyataan IF untuk mengelola panggilan formula
Anda dapat menggunakan IF terlebih dahulu untuk memeriksa apakah suatu perhitungan perlu dilakukan atau tidak.
Contoh
Misalnya, Anda memiliki tabel data produk yang mencakup buku, dan Anda ingin menggunakan vlookup untuk memasukkan data penjualan di samping detail buku.
Jadi Anda mengatur VLOOKUP Anda untuk mencari nomor ISBN (nomor seri buku) di tabel data penjualan dan mengambil nilai penjualannya. Inefisiensi terjadi saat Anda mencari semua produk, bukan hanya buku, sehingga semua produk bahkan yang tidak memiliki nilai atau N/A di kolom ISBN masih tetap dicari.
Sebagai gantinya, periksa terlebih dahulu nomor ISBN nya, kemudian lakukan pencarian hanya jika ISBN berisi nilai yang valid.
Rumusnya bisa terlihat seperti ini, di mana Anda pertama kali memeriksa bahwa nilai pencarian ISBN tidak sama dengan N/A:
= IF (A2 <> "N/A", rumus_pencarian_anda, "Bukan buku")
atau lebih umum:
= IF (pengecekan, hitung_jika_pengecekan_bernilai_benar, sampaikan_pesan_tidak_dihitung)
^ Kembali ke Daftar Isi
16. Kelola perhitungan besar dengan kontrol switch
Gunakan sebuah cell sebagai kontrol switch/saklar dengan metode pernyataan IF dari topik sebelumnya, untuk menjaga agar tetap dapat dikelola.
Gunakan data validasi untuk membuat menu drop-down di sebuah cell, yaitu cell kontrol switch, yang berisi pilihan "Proses" atau "Ditunda" (atau isi dengan apa pun yang masuk akal).
Kemudian gunakan rumus IF seperti di bawah ini pada formula yang memperlambat kinerja di Google Sheets, apabila anda membutuhkannya:
= IF ($A$2 = "Ditunda", "Ditunda", jalankan_perhitungan_disini)
Berikut adalah contoh teknik kontrol switch yang diterapkan pada beberapa formula Instagram IMPORTXML yang memperlambat kinerja (untuk 25 akun teratas):
^ Kembali ke Daftar Isi
17. Gunakan fungsi Filter, Unique, dan Array_Constrain untuk membuat tabel bantuan yang lebih kecil
Fungsi Filter, Unique dan Array_Constrain semua menerima rentang (tabel) sebagai input dan mengembalikan output dalam bentuk rentang (tabel) juga, yang lebih kecil dari tabel input.Dengan demikian mereka berguna untuk membuat tabel bantuan untuk perhitungan analisis data selanjutnya.
Katakanlah Anda memiliki tabel dengan 100.000 baris data, tetapi hanya tertarik melakukan perhitungan pada subset/sebagian dari data itu saja, maka Anda bisa menggunakan satu atau lebih fungsi tersebut untuk mengurangi ukuran tabel Anda dan membuat tabel bantuan baru untuk perhitungan Anda.
Misalnya, Anda dapat menggunakan fungsi Filter dan Unique untuk membuat tabel bantuan dari subset dinamis dari tabel besar Anda:
= UNIQUE (FILTER (A1:E100000, B1:B100000 < 100))
atau menggunakan Array_Constrain untuk membuat tabel bantuan dari subset statis dari tabel besar Anda:
= ARRAY_CONSTRAIN (A1:E100000, 10, 4)
^ Kembali ke Daftar Isi
18. Hindari Perhitungan Berantai Panjang
Perhitungan berantai panjang disini, maksudnya yaitu cell yang mereferensikan satu sama lain dalam rantai yang panjang, misalnya cell dalam satu kolom yang sama bernilai sama dengan sel di atasnya, contohnya:
Sebagai contoh, buat 100.000 baris dalam satu kolom, dengan tiap rumus yang merujuk cell di atasnya, dan hal tersebut TIDAK PERNAH mampu menunjukkan semua hasilnya, meskipun rumus yang ada ditampilkan di cell nya.
Meskipun harus menutup file, menunggu sebentar dan membuka kembali untuk melihat hasil yang sudah selesai.
Menariknya, masalah cell kosong ini selalu terjadi pada rantai rumus ke 99.100. Di sini Anda dapat melihat hasil rumus yang mereferensi cell di atasnya (A99102) tetapi tidak menunjukkan nilai 1 seperti yang ditunjukkan cell-cell di atas:
(Batas 99.100 ini terjadi dalam semua tes rangkaian yang pernah dilakukan oleh Ben Collins, yang dapat dikatakan bahwa ini merupakan semacam batas yang ditetapkan oleh Google Sheets untuk penghitungan cell berantai.)
Perhitungan ini lambat dan bahkan mungkin hasilnya tidak keluar dalam kondisi tertentu.
Selain itu, praktik perhitungan berantai ini sangat buruk. Satu kesalahan akan mengalir ke seluruh rantai berikutnya dan juga sulit untuk di-debug.
Praktik yang lebih baik yaitu merujuk sel tunggal dengan referensi absolut dan lalu menyalinnya di seluruh baris di kolom Anda, misal:
= $A$2
^ Kembali ke Daftar Isi
19. Referensi data pada halaman tab yang sama
Merujuk data dalam halaman yang sama membuat segala sesuatunya sederhana — selalu menjadi praktik yang paling baik — dan menghemat waktu karena rumus dapat mengakses data lebih cepat.
Namun, jelas hal tersebut tidak selalu praktis atau nyaman untuk referensi data di halaman tab yang sama dari Google Sheet, tetapi tetap menggunakan referensi dalam satu file akan menjadi lebih cepat daripada menggunakan IMPORTRANGE untuk membawa data dari file yang berbeda.
^ Kembali ke Daftar Isi
20. Gunakan kolom bantuan
Membuat formula pintar yang dapat melakukan semuanya dalams sekali jalan sangatlah menggoda, namun ada banyak keuntungan untuk membuat kolom bantuan. Tidak hanya lebih mudah untuk dibuat, mereka juga lebih mudah untuk dipahami (untuk Anda dan kolega Anda), lebih mudah untuk di-debug, dan kadang-kadang mereka bisa lebih cepat.Misalkan Anda memiliki daftar URL situs web di kolom A dan Anda ingin mengekstrak path dengan cepat, bagian dari URL setelah alamat www.website.com.
Anda dapat menggunakan rumus ini di kolom B:
= RIGHT (A1, LEN (A1) - SEARCH (".com", A1) - 4)
Sekarang, semisal karena nama host sama dalam contoh ini, kita tidak perlu menghitung fungsi SEARCH berulang kali.
Kita dapat menjalankan rumus ini sekali, dan semisal sudah diketahui bahwa nama host panjangnya hanya 24 karakter (dan menyimpannya dalam kolom bantuan), dan kemudian ganti fungsi SEARCH dengan konstanta ini (24 digit + 4 digit untuk ".com" ):
= RIGHT (A1, LEN (A1) - 28)
^ Kembali ke Daftar Isi
21. Pisahkan file Google Sheet yang lambat menjadi beberapa file terpisah
Pertama-tama, mungkin sudah waktunya untuk mempertimbangkan menyimpan data dalam database dan bukan Google Sheets jika data sudah mendekati batas 5 juta cell (lihat no. 27 di bawah).Meskipun dengan asumsi tetap menggunakan Google Sheets, mungkin sudah waktunya untuk membagi dataset besar menjadi beberapa file Google Sheets yang terpisah jika Google Sheet sudah dirasa sangat lambat sehingga sulit digunakan.
Teknik ini harus digunakan dengan HATI-HATI, untuk menghindari KEHILANGAN DATA. Lakukan ini jika Anda yakin sudah tahu apa yang Anda lakukan dan Anda yakin benar-benar memerlukannya.
Sadarilah bahwa pendekatan ini juga ada harga yang harus dibayar. Sebelumnya, Anda mungkin hanya memiliki satu tabel tunggal untuk melacak, memodifikasi, memperbarui, dan menganalisis. Sedangkan, jika Anda membaginya dalam beberapa file terpisah, Anda harus melacak beberapa file dan memastikan bahwa perubahan dan analisis yang dilakuan masih konsisten.
Pendekatan lain yang aman mungkin dengan membongkar data tahun sebelumnya, atau produk lama yang tidak ada lagi ada dalam inventaris Anda. Dengan kata lain, ini adalah data yang tidak digunakan lagi, tetapi tetap perlu untuk disimpan salinannya.
Cara memisahkan Google Sheets dengan aman
Pendekatan terbaik saat membagi file Google Sheets yang sangat besar adalah membuat salinan baru terlebih dahulu (sebagai Backup), beri label setiap halaman dengan jelas lalu hapus semua data yang tidak relevan dengan halaman. Misalnya, jika Anda ingin memindahkan data tahun 2018 ke file tersendiri, maka berikut adalah langkah-langkahnya:
- Buat salinan file masternya, dengan semua datanya (backup)
- Beri nama pada file Google Sheet baru ini dan sertakan "2018" di suatu tempat dalam judulnya
- Sortir data berdasarkan tahun pada file baru ini
- Tambahkan filter dan tampilkan semuanya kecuali data 2018 (misal. Sembunyikan data 2018)
- Hapus semua baris ini (baris yang bukan data 2018)
- Hapus filter agar menyisakan atau menampilkan data 2018
Setelah semua langkah selesai untuk arsip data anda, mungkin Anda ingin menghapusnya dari Lembar master Anda. Namun, sangat dianjurkan untuk menyimpan terlebih dahulu salinan utuh dari file master Google Sheet Anda sebelum Anda mulai menghapus data apa pun.
Anda akan berakhir dengan master Google Sheet baru yang hanya memiliki data saat ini, atau data langsung, dan semoga akan menjadi jauh lebih cepat.
Anda juga dapat melakukan perhitungan atau analisis data secara lokal dan kemudian menyatukan kembali data lama kedalam master Google Sheet Anda menggunakan rumus IMPORTRANGE.
^ Kembali ke Daftar Isi
22. Gunakan Conditional Formating secara terbatas
Conditional Formating adalah fitur yang sangat bagus untuk menambahkan konteks tambahan ke Google Sheets, misalnya menyoroti outlier atau nilai di luar ambang batas. Semisal menggunakan di file penjadwalan untuk menyoroti baris dengan tanggal hari ini.
Namun, hal ini bisa sangat lambat pada data besar karena Conditional Formating diterapkan berdasarkan pada tiap cell (cell-by-cell).
Jadi paling baik hanya digunakan untuk tabel data kecil dan di tabel presentasi dan /atau laporan.
^ Kembali ke Daftar Isi
23. Manfaatkan keunggulan Apps Script
Apps Script adalah bahasa scripting berbasis Javascript yang dapat digunakan untuk memperluas fungsionalitas Google Sheets (dan berinteraksi dengan layanan Google lainnya).Kemungkinan penggunaannya di sini sangat luas, mencakup semuanya, mulai dari mengotomatiskan proses berulang untuk mempercepat alur kerja Anda hingga membuat fungsi custom untuk mempermudah Anda mengetik rumus rumit berulang-ulang (lihat bagian berikutnya).
Aplikasi Script sangat efektif dalam melakukan banyak perhitungan, terutama yang berulang, seringkali lebih cepat daripada ribuan formula biasa.
Contoh
Bayangkan saya memiliki kolom angka yang ingin saya klasifikasikan menjadi Kecil, Sedang dan Besar menggunakan pernyataan IF bersarang seperti ini:
= IF (A1 > 500, "Besar", IF (A1 > 250, "Sedang", "Kecil") )
Dibutuhkan sekitar 6,5 detik untuk menghitung 100.000 baris, dan tambahan sekitar 30 detik untuk menyalin (copy) dan menempelnya (paste) sebagai values/nilai (praktik terbaik lainnya untuk mengoptimalkan Google Sheet — lihat nomor 6 di atas).
Mengganti rumus tersebut dengan Apps Script dan menjalankan rentang data yang sama membutuhkan waktu sekitar 16 detik untuk menempelkan keseluruhan 100.000 nilai, jadi kurang dari separuh dari waktu menggunakan rumus biasa.
Berikut kode untuk fungsi ini:
function columnClassifier() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var input = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues();
var output = [];
for (var i = 0; i < input.length; i++) {
if (input[i][0] > 500) { output.push(["Large"]) }
else if (input[i][0] > 250) { output.push(["Medium"]) }
else { output.push(["Small"]) };
}
sheet.getRange(1,2,output.length,1).setValues(output);
}
Mengoptimalkan Kode Apps Script adalah topik yang berbeda dan merupakan topik yang sangat besar, jadi tidak akan dibahas di dalam artikel ini. Namun perhatikan, kuota dan batasan yang berlaku untuk kode Apps Script.^ Kembali ke Daftar Isi
24. Gunakan formula khusus/custom secara terbatas
Rumus khusus/custom, juga dikenal sebagai fungsi yang ditentukan sendiri oleh pengguna, atau user-defined function (UDF), adalah fungsi khusus yang ditulis di jendela Script editor (Apps Script ).Rumus custom yang rumit akan menjadi sangat lambat jika menggunakan metode atau cara yang sama dengan fungsi tradisional, dan membaca/menulis di tiap cell.
Misalnya, pertimbangkan formula khusus ini untuk menaikkan harga produk sebesar 15%:
/**
* Markup a price by 15%
*
* @param {number} input The value to markup
* @return The input multiplied by 1.15.
* @customfunction
*/
function MARKUP(input) {
return input * 1.15;
}
yang terlihat seperti ini saat digunakan:
Menyalin dan menempel kode ini pada 1.000 baris menjadikannya SANGAT lambat. Butuh waktu sekitar 10 detik, tetapi juga tergantung pada koneksi wifi Anda.
Yang perlu dilakukan dengan formula custom, untuk meningkatkan kinerjanya, adalah berpikir lebih seperti penggunaan ArrayFormula. Bagian lambat dari rumus ini adalah saat membaca dari, dan saat menulis ke, Google Sheet, karena ini terjadi melalui API spreadsheet, jadi tergantung pada jaringan atau koneksi wifi.
Yang terbaik adalah meminimalkan jumlah panggilan baca / tulis ke Google Sheet Anda sebisa mungkin.
Katakanlah Anda ingin melakukan perhitungan pada 10.000 cell, maka akan lebih baik jika membaca sekali semuanya (10k cell) ke dalam fungsi, melakukan perhitungan dan kemudian mengeluarkan hasilnya kembali ke Google Sheet dalam sekali jalan, dalam suatu rentang.
Misalnya, ubah rumus di atas menjadi rumus yang lebih umum, yang menerima input rentang dan menghasilkan output berukuran sama:
/**
* Markup a price by 15%
*
* @param {number} input The value or range to markup
* @return The input multiplied by 1.15.
* @customfunction
*/
function MARKUP_RANGE(input) {
if (input.map) {
return input.map(MARKUP_RANGE);
}
else {
return input * 1.15;
}
}
Fungsi ini akan melakukan perhitungan yang sama pada 1.000 sel dalam waktu kurang dari satu detik, yang secara signifikan lebih cepat. Beginilah cara kerjanya dalam praktik, menerima inputan seluruh rentang:
Catatan: Rumus apps script custom dihitung ulang hanya ketika argumennya berubah. Dan juga, argumen ini harus deterministik, yaitu bukan salah satu fungsi volatile/dinamis seperti NOW() atau RAND(). Jika fungsi khusus tersebut mencoba mengembalikan nilai output berdasarkan fungsi volatile/dinamis, maka akan menampilkan pesan Loading... tanpa batas waktu.
Baca lebih lanjut tentang optimasi fungsi custom pada dokumentasi Google.
^ Kembali ke Daftar Isi
25. Tips lainnya
Terkadang hal itu mungkin bukan masalah yang berasal dari Google Sheets yang menyebabkan waktu respons menjadi lambat. Sebaiknya coba strategi berikut untuk melihat apakah ada yang dapat menyelesaikan masalah Anda:> Tutup dan buka kembali Google Sheet (terkadang ini merupakan perbaikan paling sederhana yang dapat berfungsi).
> Refresh browser.
> Bersihkan cache dan cookie.
> Coba browser atau sistem operasi lain. Pastikan browser dan sistem operasi yang berjalan saat ini adalah versi terbaru.
> Nonaktifkan plugins atau ekstensi browser.
> Jika memungkinkan, cobalah mereplikasi masalah yang sama di komputer lain untuk memeriksa apakah perangkat keras komputerkah yang bermasalah. Kecepatan perhitungan Google Sheet sebagian besar tergantung pada sumber daya lokal.
> Cobalah mereplikasi masalah Anda di jaringan internet lainnya untuk memeriksa apakah itu masalah jaringan. Sebagian besar fungsi dijalankan secara lokal di browser, tetapi beberapa fungsi, seperti IMPORT, memerlukan akses ke Internet (dan juga Apps Script yang dijalankan di server Google).
> Coba nonaktifkan dan aktifkan kembali akses offline.
> atau, cobalah komputer yang lebih cepat
^ Kembali ke Daftar Isi
26. Memahami perubahan di cloud yang membutuhkan waktu dalam penyebarannya
Ingat bahwa jika Anda bekerja secara real-time dengan kolaborator lain, perubahan mungkin membutuhkan waktu beberapa saat untuk disinkronkan.Pengguna lain dan file apps script akan melihat versi cache dari file Google Seeet hingga semua operasi sinkronisasi selesai, dan ini dapat memengaruhi kemunculan beberapa cell tertentu. Misalnya, mereka mungkin telah selesai menghitung dalam Google Seeet Anda tetapi masih menunjukkan kosong atau #N/A dalam tampilan yang dilihat oleh kolaborator.
Namun perlu diingat, hal ini adalah hal yang sangat kecil dan terkadang tidak berpengaruh. Dalam pengalaman, hal itu semua terjadi begitu cepat sehingga terasa hampir instan, meskipun sekali lagi, data atau file Google Sheet yang sangat besar dan lambat akan lebih terpengaruh oleh hal ini.
^ Kembali ke Daftar Isi
27. Tahu kapan saatnya pindah ke basis data
Akan tiba saatnya, dan mungkin Anda sudah disaat dimana, ketika Anda harus mengakui kekalahan dan menerima bahwa Google Sheets tidak dapat memenuhi semua kebutuhan data Anda lagi. Untuk semua kemampuannya, yang banyak, batas 5 juta cell dapat terisi dengan sangat cepat dengan data dunia nyata ataupun data realtime.Jauh sebelum Google Sheet Anda mendekati batas 5 juta cell, Anda harus memikirkan langkah selanjutnya di luar Google Sheet yang lambat.
Pindahkan data ke arsip basis data. Anda masih tetap bisa bekerja di lingkungan Google Sheet tetapi pindahkan penyimpanan data ke basis data khusus.
Opsi Basis Data Google
Dalam ekosistem Google, ada fitur tentang BigQuery dan Cloud SQL, keduanya adalah basis data dalam cloud. Namun, integrasinya agak sedikit rumit, jadi Anda perlu keterampilan pengembangan untuk menghubungkan layanan ini.
Google BigQuery terintegrasi dengan Google Drive, sehingga Anda dapat menggunakan Google Sheets Anda sebagai tabel di BigQuery. Anda juga dapat menggunakan layanan Apps Script BigQuery untuk mengelola proyek BigQuery Anda.
Menghubungkan Google Sheet ke Cloud SQL secara langsung membutuhkan Apps Script, meskipun Anda dapat mengekspor data dari Google Sheets (mis. Sebagai CSV) dan mengimpornya ke Google Cloud SQL.
Carilah lebih banyak sumber daya pada topik-topik ini.
^ Kembali ke Daftar Isi
Kesimpulan
Postingan ini dibuat selengkap mungkin, tetapi mungkin masih lebih banyak ide di luar sana.DIpersilahkan jika ada komentar dan pemikiran lain tentang topik ini. Pikirkan posting ini sebagai artikel yang hidup dan diharapkan akan ditambahkan di lain waktu.
Terima kasih telah membaca dan kami berharap wawasan yang terkandung di sini dapat bermanfaat!
^ Kembali ke Daftar Isi
___________________________
Collins, Ben. 2018. Slow Google Sheets? Here are 27 ideas to try today. www.benlcollins.com