Google Sheet ialah perangkat lunak yang memungkinkan beberapa user untuk mampu mengedit lembar kerja sekaligus secara serempak. Di kembangkan oleh Google yang memiliki beberapa fitur yang ibarat Microsoft Excel, tetapi mampu diakses dimana saja melalui internet. Namun, ada beberapa kekurangan bila kita menggunakan Google Sheet. Semakin banyak data yang dimasukkan dalam suatu file Google Sheet, maka akan terasa makin kurang responsif. Selain itu, penggunaan puluhan bahkan ratusan fungsi dinamis turut memperburuk suasana. Anda akan mulai merasakannya saat anda mulai melihat loading bar yang sering timbul di bagian kanan atas lembar kerja Google Sheet
Ada banyak aspek yang membuat Google Sheet semakin lambat. Maka, ada beberapa saran dan taktik untuk mengoptimasi lembar kerja Google Sheet anda.
Beberapa Optimasi Google Sheet :
Klik untuk eksklusif menuju topik yang anda kehendaki
- Mengenali Google Sheets yang Lambat
- Ketahuilah kekurangan Google Sheet
- Menganalisis ukuran file Google Sheet
- Mengukur kecepatan perkiraan Google Sheet
- Hapus cell yang tidak digunakan
- Konversikan formula menjadi nilai statis jikalau memungkinkan
- Gunakan rentang statis selaku 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 mengorganisir panggilan formula
- Kelola perkiraan besar dengan kendali switch
- Gunakan fungsi Filter, Unique, dan Array_Constrain untuk membuat tabel pinjaman yang lebih kecil
- Hindari perhitungan berantai panjang
- Referensi data pada halaman tab yang sama
- Gunakan kolom pinjaman
- 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 pergantian di cloud yang memerlukan waktu dalam penyebarannya
- Tahu kapan saatnya pindah ke basis data
Bekerja dengan data memungkinkan Anda mencicipi spreadsheet menjadi lamban pada tahap tertentu. Banyak kiat biasa untuk menciptakan spreadsheet anda lebih baik, jadi meskipun dikala ini anda hanya bekerja dengan data kecil, masih tetap pantas untuk dipraktekkan mulai kini.
1. Mengenali Google Sheets yang Lambat
Saat Anda membaca isu ini, kemungkinan besar anda telah mengenali beberapa atau semua duduk perkara berikut.
Perhitungan di Google Sheets Anda sungguh lambat dan mulai sering timbul loading kafe setiap kali ada pergeseran pada Sheet Anda.
Google Sheet Anda menjadi lamban dikala merespons klik atau pementingan tombol mouse.
Data tidak pribadi ditampilkan di cell, walaupun Anda tahu Anda telah memasukkan data ke dalam cell tersebut. Sebagai contoh, lihat gambar di bawah ini yang mana tidak langsung memperlihatkan nilai sesudah formula diinputkan.
Google Sheet Anda menjadi tidak responsif. Skenario terburuknya, berdasarkan pengalaman, jikalau respon tersebut tidak secepatnya timbul, kemungkinan besar hal tersebut tidak akan tertuntaskan. Kaprikornus bila Anda melihat pesan kesalahan seperti di bawah ini, dan Anda telah menanti beberapa menit tetapi tidak ada yang terjadi, maka Anda mungkin geram, keluar dari halaman, dan mungkin mencoba kembali namun dengan cara yang berlawanan.
Hal ini lazimnya dibarengi dengan bertambah cepatnya putaran kipas angin di komputer!
2. Ketahuilah kekurangan Google Sheets
Tentunya kalau 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 jelek.
Oleh alasannya itu, pembahasan tentang Google Sheets yang lambat tidak akan lengkap tanpa membahas batasan Google Sheets.
Batas Keseluruhan:
Google Sheets mempunyai batas 5 juta cell per file (lihat ukuran file Google). Jika Anda melakukan sesuatu yang melalui batas ini (misal. Menambahkan baris gres atau Sheet gres), Anda akan melihat pesan kesalahan ini:
Batas kolom:
Google Sheets mempunyai jumlah kolom maksimum 18.278 kolom. Jika Anda melaksanakan sesuatu yang melewati batas ini, Anda akan menyaksikan pesan kesalahan berikut:
Batas ukuran cell individual:
Dalam satu cell, ada panjang string maksimum yakni 50.000 karakter (cukup untuk sekitar 500 kalimat rata-rata). Jika Anda melaksanakan sesuatu yang melewati batas ini, Anda akan melihat pesan kesalahan berikut:
Jumlah baris maksimum yang mampu Anda tambahkan dalam sekali klik:
Anda dapat menambahkan maksimum 40.000 baris baru sekaligus (Anda dapat melakukan hal ini sebanyak yang Anda kehendaki, hingga batas 5 juta cell). Jika Anda menjajal melakukan yang lebih dari itu, Anda akan melihat pesan kesalahan berikut:
3. Menganalisis ukuran file Google Sheet
Dengan menggunakan Google Sheet Script Editor, Anda dapat dengan segera membuat alat sederhana untuk menghitung ukuran Lembar Anda.
Alat untuk audit file Google Sheets ini menawarkan jumlah total cell dalam 1 file, dan berapa banyak cell yang mempunyai 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 mampu didapatkan di GitHub jika Anda membutuhkannya. hanya tinggal memasukkan URL google sheet anda, dan klik menu Sheet Size Auditor > Audit Sheet, kemudian klik continue (jangan lupa untuk memberi ijin susukan saat dijalankan).
4. Mengukur kecepatan perkiraan Google Sheet
Untuk mengukur “kecepatan” aneka macam 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 mampu merekam halaman web (atau Google Sheet dalam masalah ini) dikala dalam proses memuat, dan centang kotak screenshot untuk menunjukkan apa yang terjadi beserta waktunya.
Untuk proses perkiraan rumus/formula, Anda mampu mengidentifikasi kapan perhitungan dimulai, mis. ditandai dengan angka 1 pada gambar ini, sekitar detik ke 14:
Angka 2 memperlihatkan screenshot ketika ini, sehingga dapat dilihat bahwa hasil perhitungan Anda belum ditampilkan. Lalu identifikasi kapan Google Sheet simpulan menjumlah formula/rumus (kotak merah, sekitar detik ke 32):
Periksa screenshot untuk melihat dikala pertama kali hasil ditampilkan di cell. Jadi dapat dilihat berapa usang kumpulan formula ini diproses:
14.05 detik – 32.26 detik = 18,2 detik
Ada jauh lebih banyak fungsi pada Developer Tools, namun ini telah cukup untuk dikala ini.
Ini ialah salah satu metodologi yang dipakai untuk menjumlah kecepatan pemrosesan rumus jika membutuhkan sebuah perbandingan. Terkadang perlu melakukan tes di kawasan dan waktu yang sama (jadi perbedaan kecepatan wifi dapat diabaikan) atau juga dapat dicoba dijalankan beberapa kali dan dijumlah rata-ratanya.
5. Hapus Cell yang tidak dipakai
Google Sheets akan menjadi kian lambat dikala content makin besar/banyak. Jadi salah satu hal yang mesti dicoba, sebelum masuk ke dalam laba kecil dari optimasi formula, yaitu meminimalkan ukuran Google Sheet Anda.
Cell-cell kosong mampu memperlambat kinerja, bahkan mungkin secara dramatis. Kaprikornus, hapuslah kapan saja Anda mampu, sehingga dapat meminimalkan jumlah cell yang disimpan oleh Google Sheets dalam memori.
Maksud dari menghapus cell kosong disini yaitu 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 meniadakan semua kolom di sebelah kanan data (kolom T, U, V, dst) dan semua baris di bawah data (baris 54.761 sampai baris terakhir). Lihat blok baris dan klom berwarna biru.
Bagaimana dengan baris gres yang ditambahkan secara otomatis?
Jangan cemas jika data Anda diimport secara otomatis oleh Apps Script atau pihak ketiga seperti Supermetrics atau Tiller, alasannya adalah baris baru secara otomatis ditambahkan ke Lembar Anda sesuai keperluan.
(Namun, Anda tetap harus mengujinya dengan pengaturan atau aplikasi spesifik Anda, untuk mengonfirmasi hal tersebut)
6. Konversikan formula menjadi nilai statis bila memungkinkan
Setelah Anda menggunakan satu set rumus di data Anda, dan Anda yakin tidak akan membutuhkannya lagi, maka Anda mampu mengonversinya menjadi nilai statis.
Hal ini harusnya dapat menghemat ukuran file, yang akan menolong kinerja Google Sheet secara keseluruhan. Hal ini kerap kali juga merupakan praktik terbaik (untuk menghemat kemungkinan kesalahan yang terjadi jikalau seseorang atau sesuatu terjadi dan menghancurkan hasil nilai dari formula).
Cara mengubah formula ke nilai statis ialah dengan menyalinnya (copy) kemudian:
Edit > Paste special > Paste values
Lebih baik lagi, pelajari shortcut keyboard untuk mempercepatnya. Sorot/blok rumus yang mau diubah, Ctrl + C pada PC atau Cmd + C pada Mac untuk menyalin/copy dan lalu Ctrl + Shift + V pada PC atau Cmd + Shift + V pada Mac untuk menempelkan/paste sebagai nilai statis.
Catatan, sungguh diusulkan untuk menyimpan salinan formula “langsung” di bab atas kolom data di tabel Anda, sehingga Anda mempunyai catatan perihal bagaimana perkiraan dikerjakan dan juga mampu dengan cepat menggunakannya kembali kalau diperlukan:
7. Gunakan rentang statis sebagai tumpuan
Referensi rentang statis berarti menggunakan sesuatu seperti A1:B1000 dibandingkan dengan A:B dalam rumus Anda (misal. Anda secara eksplisit menentukan batas rentang Anda).
Jika Anda melakukan pekerjaan dengan Lembar besar dan menggunakan acuan dinamis di mana Anda tidak memerlukannya, ini akan menambah overhead perhiasan dalam perkiraan Google Sheet Anda.
Mungkin tidak tampakada banyak data di kolom A, namun Google Sheets akan mengusut setiap cell, termasuk semua baris yang kosong hingga baris terakhir, bila Anda menggunakan rentang dinamis mirip A:B.
Catatan: Meskipun ini lazimnya ialah ilham yang baik untuk mempercepat formula Anda, jelas ada suasana saat rujukan dinamis sungguh dibutuhkan, misalnya saat mengantisipasi data baru. Makara ada dikala mutlak dimana masih membutuhkan rentang dinamis untuk memungkinkan data gres dimasukkan dalam perhitungan.
8. Hapus fungsi volatile (dinamis) atau gunakan dengan hati-hati
Dalam Google Sheets ada empat fungsi, NOW(), TODAY(), RAND() dan RANDBETWEEN(), yang diketahui sebagai fungsi volatil atau dinamis, yang berarti mereka selalu mengkalkulasikan ulang setiap kali ada perubahan pada file Google Sheet. Maka, semua rumus dependen juga akan dijumlah ulang juga (ialah rumus yang mereferensi atau menambahkan fungsi volatile didalamnya).
Hal ini menghabiskan daya pemrosesan dan alhasil dapat mempunyai efek negatif kepada kinerja Google Sheet, meskipun hal ini cuma akan terlihat kalau Anda mempunyai jumlah data yang besar.
Sebagai ilustrasi, bila menyalin/mengcopy 100.000 fungsi TODAY() di kolom A, membutuhkan sekitar 5,5 detik.
Kemudian jika menghapus 99.999 formula tersebut dan membiarkan satu saja fungsi TODAY() di Cell A1 dan lalu 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 nyaris 1,5 detik lebih singkat.
Hal ini juga merupakan praktik terbaik untuk dijalankan, karena kalau sebuah ketika perlu membuat pergantian tanggal, maka cukup menggantinya di satu kawasan saja, A1, dan tidak harus mengulang semua formula.
9. Strategi Vlookup
Gunakan referensi statis, ketimbang dinamis, untuk tabel penelusuran kalau memungkinkan (lihat nomor 7 di atas).
Kurangi ukuran tabel pencarian Anda (lookup table), jikalau mungkin, walaupun hanya memiliki imbas kecil. Misalnya, daripada mencari di semua cell, Anda mampu memakai fungsi Filter (lihat no. 17 di bawah) untuk menciptakan tabel bantuan yang lebih kecil untuk pencarian pertama, dan lalu gunakan kesudahannya di VLOOKUP.
Namun, usahakan untuk tidak tergoda membuat fungsi FILTER (atau yang lain) di dalam VLOOKUP dan membuat tabel secara berbarengan, hal ini akan jauh lebih lambat.
Misalnya, rumus di bawah ini kurang elok dan benar-benar akan memperlambat Google Sheet bila Anda mempunyai banyak fungsi mirip ini:
= VLOOKUP ($A1, UNIQUE (FILTER ($K$1:$N$10000, $K$1:$K$10000 < 100)), 2, FALSE)
untuk itu lebih direkomendasikan untuk menciptakan tabel pertolongan terlebih dulu, dalam masalah ini untuk menciptakan tabel Filter misal pada cell F1:I10. Selanjutnya dipakai untuk tumpuan 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 ketimbang menggunakan beberapa VLOOKUP secara individual.
Pada Microsoft Excel, mengurutkan data dan menggunakan asumsi kecocokan atau matching (TRUE selaku argumen terakhir di VLOOKUP) sedikit lebih cepat daripada algoritma exact matching (FALSE sebagai argumen terakhir). Namun, juga "sangat" sedikit lebih singkat di Google Sheets, jadi tidak seimbang dengan kerumitannya.
10. Strategi Index-Match
Index-Match ialah kombinasi pencarian yang besar lengan berkuasa dan layak dipelajari. Dalam konstruksi klasiknya, Anda mungkin pernah menyaksikan 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 sungguh fleksibel).
Ini manis 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 memakai dua "match" untuk mencari nomor baris dan nomor kolom, sehingga jumlah total perkiraan Anda yakni:
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 perkiraan
Alangkah lebih baik lagi kalau menciptakan baris dan kolom pinjaman yang menggunakan match terlebih dahulu, mirip pada gambar di bawah ini:
Dengan membagi kedua penelusuran match ke dalam baris dan kolom tersendiri, Anda dapat menjumlah semua match cuma sekali, kemudian menggunakan akhirnya dalam fungsi Index.
Sekarang Anda hanya perlu menjumlah fungsi indeks dikala Anda melaksanakan perhitungan, sehingga total perkiraan Anda kini adalah:
(10.000 Match untuk baris) + (10 Match untuk kolom) + (10.000 * 10 Index) =
110.010 perhitungan
Perhitungannya secara signifikan menyusut dari sebelumnya, cuma sekitar 1/3 perhitungan yang mesti dikerjakan!
11. Strategi fungsi Query
Seperti yang sudah disinggung di atas, menggunakan rujukan rentang dinamis dapat mempengaruhi kinerja alasannya mungkin secara tidak sengaja formula yang ada masih memperhitungkan ribuan baris kosong (lihat no. 7 di atas).
Oleh karena itu, gunakan acuan rentang statis atau buat tabel pembantu yang lebih kecil, yang karenanya dijadikan input untuk fungsi Query biar mampu mempercepat kinerja.
Misalnya, dikala menjalankan tes dengan fungsi QUERY, Ben Collins mendapatkan fakta bahwa setiap penambahan 20.000 baris kosong di dalam perkiraan rentang dinamis, hal tersebut akan memperbesar 1 detik pada perkiraan.
Makara ketimbang rumus seperti ini contohnya:
= 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 menjumlah semua baris kosong di bawah dataset di Sheet 2.
12. Strategi ArrayFormula
Apabila Anda pernah menghabiskan waktu membaca forum-forum, Anda akan menemukan banyak orang yang menyampaikan bila memakai satu ArrayFormula untuk menggantikan ratusan formula individu akan menjadi lebih singkat, dan sebagian yang lain dengan tegas menyampaikan sebaliknya. Kaprikornus yang mana yang benar?
Dalam pengalaman, ArrayFormula yang melakukan pekerjaan pada dataset besar (yang mempunyai banyak baris) tenyata lambat dan kadang kala akan membuat Google Sheet menjadi lamban. Dalam perkara ini, ArrayFormula condong lebih lambat ketimbang formula perorangan.
Namun, ArrayFormula sangat berkhasiat dalam situasi tertentu, seperti mengumpulkan data dari Google Form, maka ArrayFormula masih dianjurkan untuk dipelajari dan digunakan, dengan hati-hati.
ArrayFormula banyak digunakan untuk melakukan perhitungan pada dataset yang senantiasa berubah dan tumbuh seiring waktu (baris gres yang sering disertakan). Dengan begitu perkiraan akan dilakukan secara tomatis tanpa perlu mengganti apa pun.
Misalnya, rumus ini:
= ArrayFormula (IF (A2:A <> "", A2: A * 100, ""))
yang merupakan versi array dari formula perorangan berikut:
= IF(A2 <> "", A2 * 100, "")
akan secara otomatis menjumlah nilai untuk setiap baris data baru yang disertakan.
Anda perlu menguji kedua model tersebut pada Google Sheet Anda sendiri, dan memilih mana yang paling sempurna untuk perkara anda (lebih mementingkan kecepatan ataukah perkiraan otomatis dalam array). Pendekatan formula individual mempunyai manfaat pelengkap, ialah lebih mudah untuk di debug dan di atur.
Jika Anda ingin tetap memakai ArrayFormula, perlu dipertimbangankan untuk mengarsipkan data historis secara terencana (lihat no. 21 di bawah), untuk menjaga dataset semoga tetap mampu dikontrol.
13. Strategi Penggunaan Import
Fungsi ImportHtml, ImportFeed, ImportData, ImportXml, dan ImportRange mengambil data dari sumber eksternal ke Google Sheet Anda, sehingga fungsi-fungsi tersebut memerlukan koneksi Internet agar dapat berfungsi.
Mereka jauh lebih lambat dari fungsi-fungsi lain yang beroperasi sepenuhnya dalam satu file, sehingga hal tersebut berpotensi menghipnotis kinerja Google Sheet Anda.
Misalnya, menggunakan ImportRange untuk mengimpor data dalam jumlah besar dari file Google Sheet lain akan membutuhkan beberapa waktu untuk diangkut dan kadang kala ada pesan kesalahan... untuk sementara waktu:
Pendekatan terbaik di sini adalah menghemat jumlah panggilan eksternal yang dibutuhkan.
Misalnya, dengan ImportRange Anda mampu membuat satu panggilan untuk mendapatkan seluruh dataset, meskipun poin di atas mengatakan bahwa ini merupakan operasi yang lambat. Kemudian melaksanakan filter atau pekerjaan yang lain secara setempat. Hal ini lebih baik daripada melaksanakan beberapa panggilan ImportRange.
Catatan, Anda dibatasi hingga maksimum 50 fungsi ImportData dalam satu spreadsheet (Lihat Disini).
Tak satu pun dari rumus Impor yang lain yang mempunyai batas eksplisit seperti ini di halaman dokumentasinya dan beberapa pengujian terbatas menunjukkan bahwa jikapun ada batasan, mereka niscaya lebih dari 50.
Secara lazim, fungsi-fungsi import ini dihitung ulang dengan kala selaku berikut (lihat dokumentasi):
ImportRange: Setiap 30 menit
ImportHtml, ImportFeed, ImportData, ImportXml: Setiap 1 jam
14. Strategi Fungsi GoogleFinance
Fungsi GoogleFinance yaitu fungsi lain yang mengambil data dari sumber eksternal, sehingga memerlukan koneksi internet. Seperti rumus Impor di atas, hal ini akan lebih lambat daripada fungsi umumyang cuma beroperasi di dalam Google Sheet.
Sekali lagi, strategi umum di sini yakni menghemat jumlah panggilan eksternal yang memakai fungsi GoogleFinance.
Jika memungkinkan, misalnya, cobalah untuk mengambil harga saham dalam satu tahun penuh dengan formula tunggal, dibandingkan dengan memakai ratusan fungsi harian.
Dalam dokumentasinya, data GoogleFinance mungkin dapat tertunda sampai 20 menit.
15. Gunakan pernyataan IF untuk mengurus panggilan formula
Anda mampu menggunakan IF apalagi dahulu untuk menilik apakah sebuah perkiraan perlu dilakukan atau tidak.
Contoh
Misalnya, Anda mempunyai tabel data produk yang meliputi buku, dan Anda ingin menggunakan vlookup untuk memasukkan data pemasaran di samping rincian buku.
Jadi Anda menertibkan VLOOKUP Anda untuk mencari nomor ISBN (nomor seri buku) di tabel data pemasaran dan mengambil nilai penjualannya. Inefisiensi terjadi saat Anda mencari semua produk, bukan hanya buku, sehingga semua produk bahkan yang tidak mempunyai nilai atau N/A di kolom ISBN masih tetap dicari.
Sebagai gantinya, periksa terlebih dulu nomor ISBN nya, lalu kerjakan penelusuran cuma jika ISBN berisi nilai yang valid.
Rumusnya mampu terlihat seperti ini, di mana Anda pertama kali menyelidiki bahwa nilai pencarian ISBN tidak sama dengan N/A:
= IF (A2 <> "N/A", rumus_pencarian_anda, "Bukan buku")
atau lebih biasa :
= IF (pengecekan, hitung_jika_pengecekan_bernilai_benar, sampaikan_pesan_tidak_dihitung)
16. Kelola perhitungan besar dengan kendali switch
Gunakan suatu cell selaku kontrol switch/saklar dengan sistem pernyataan IF dari topik sebelumnya, untuk mempertahankan semoga tetap dapat dikontrol.
Gunakan data validasi untuk membuat hidangan drop-down di sebuah cell, ialah cell kontrol switch, yang berisi pilihan "Proses" atau "Ditunda" (atau isi dengan apa pun yang masuk akal).
Kemudian gunakan rumus IF mirip di bawah ini pada formula yang memperlambat kinerja di Google Sheets, kalau anda membutuhkannya:
= IF ($A$2 = "Ditunda", "Ditunda", jalankan_perhitungan_disini)
Berikut yaitu acuan teknik kontrol switch yang diterapkan pada beberapa formula Instagram IMPORTXML yang memperlambat kinerja (untuk 25 akun teratas):
17. Gunakan fungsi Filter, Unique, dan Array_Constrain untuk menciptakan tabel santunan yang lebih kecil
Fungsi Filter, Unique dan Array_Constrain semua menerima rentang (tabel) selaku input dan mengembalikan output dalam bentuk rentang (tabel) juga, yang lebih kecil dari tabel input.
Dengan demikian mereka berguna untuk membuat tabel tunjangan untuk perkiraan analisis data selanjutnya.
Katakanlah Anda memiliki tabel dengan 100.000 baris data, tetapi hanya kesengsem melaksanakan perkiraan pada subset/sebagian dari data itu saja, maka Anda mampu memakai satu atau lebih fungsi tersebut untuk meminimalkan ukuran tabel Anda dan menciptakan tabel pinjaman gres untuk perhitungan Anda.
Misalnya, Anda dapat memakai fungsi Filter dan Unique untuk menciptakan tabel pinjaman dari subset dinamis dari tabel besar Anda:
= UNIQUE (FILTER (A1:E100000, B1:B100000 < 100))
atau menggunakan Array_Constrain untuk membuat tabel santunan dari subset statis dari tabel besar Anda:
= ARRAY_CONSTRAIN (A1:E100000, 10, 4)
18. Hindari Perhitungan Berantai Panjang
Perhitungan berantai panjang disini, tujuannya yakni cell yang mereferensikan satu sama lain dalam rantai yang panjang, contohnya cell dalam satu kolom yang sama bernilai sama dengan sel di atasnya, misalnya:
Sebagai pola, buat 100.000 baris dalam satu kolom, dengan tiap rumus yang merujuk cell di atasnya, dan hal tersebut TIDAK PERNAH mampu memberikan semua alhasil, walaupun rumus yang ada ditampilkan di cell nya.
Meskipun mesti menutup file, menunggu sebentar dan membuka kembali untuk menyaksikan hasil yang telah selesai.
Menariknya, duduk perkara cell kosong ini senantiasa terjadi pada rantai rumus ke 99.100. Di sini Anda mampu menyaksikan hasil rumus yang mereferensi cell di atasnya (A99102) tetapi tidak memberikan nilai 1 seperti yang ditunjukkan cell-cell di atas:
(Batas 99.100 ini terjadi dalam semua tes rangkaian yang pernah dijalankan oleh Ben Collins, yang mampu dikatakan bahwa ini ialah semacam batas yang ditetapkan oleh Google Sheets untuk penghitungan cell berantai.)
Perhitungan ini lambat dan bahkan mungkin risikonya tidak keluar dalam kondisi tertentu.
Selain itu, praktik perkiraan berantai ini sungguh buruk. Satu kesalahan akan mengalir ke seluruh rantai berikutnya dan juga susah untuk di-debug.
Praktik yang lebih baik yakni merujuk sel tunggal dengan referensi adikara dan kemudian menyalinnya di seluruh baris di kolom Anda, misal:
= $A$2
19. Referensi data pada halaman tab yang sama
Merujuk data dalam halaman yang sama membuat segala sesuatunya sederhana — senantiasa menjadi praktik yang paling baik — dan meminimalkan waktu sebab rumus dapat mengakses data lebih singkat.
Namun, jelas hal tersebut tidak senantiasa praktis atau nyaman untuk rujukan data di halaman tab yang sama dari Google Sheet, tetapi tetap menggunakan rujukan dalam satu file akan menjadi lebih singkat dibandingkan dengan memakai IMPORTRANGE untuk membawa data dari file yang berlainan.
20. Gunakan kolom dukungan
Membuat formula cerdik yang mampu melakukan seluruhnya dalams sekali jalan sangatlah menarik hati, tetapi ada banyak laba untuk membuat kolom santunan. Tidak cuma lebih mudah untuk dibuat, mereka juga lebih mudah untuk dimengerti (untuk Anda dan kolega Anda), lebih gampang untuk di-debug, dan kadang kala mereka mampu lebih singkat.
Misalkan Anda memiliki daftar URL website di kolom A dan Anda ingin mengekstrak path dengan cepat, bagian dari URL sesudah alamat www.website.com.
Anda mampu memakai rumus ini di kolom B:
= RIGHT (A1, LEN (A1) - SEARCH (".com", A1) - 4) Sekarang, semisal karena nama host sama dalam pola ini, kita tidak perlu menjumlah fungsi SEARCH berulang kali.
Kita dapat menjalankan rumus ini sekali, dan semisal sudah dimengerti bahwa nama host panjangnya hanya 24 aksara (dan menyimpannya dalam kolom pinjaman), dan kemudian ganti fungsi SEARCH dengan konstanta ini (24 digit + 4 digit untuk ".com" ):
= RIGHT (A1, LEN (A1) - 28)
21. Pisahkan file Google Sheet yang lambat menjadi beberapa file terpisah
Pertama-tama, mungkin telah waktunya untuk memikirkan menyimpan data dalam database dan bukan Google Sheets jika data sudah mendekati batas 5 juta cell (lihat no. 27 di bawah).
Meskipun dengan perkiraan tetap menggunakan Google Sheets, mungkin telah waktunya untuk membagi dataset besar menjadi beberapa file Google Sheets yang terpisah kalau Google Sheet telah dirasa sungguh lambat sehingga sulit digunakan.
Teknik ini mesti digunakan dengan HATI-HATI, untuk menyingkir dari KEHILANGAN DATA. Lakukan ini jika Anda percaya sudah tahu apa yang Anda lakukan dan Anda yakin sungguh-sungguh memerlukannya.
Sadarilah bahwa pendekatan ini juga ada harga yang mesti dibayar. Sebelumnya, Anda mungkin hanya mempunyai satu tabel tunggal untuk melacak, memodifikasi, memperbarui, dan menganalisis. Sedangkan, kalau Anda membaginya dalam beberapa file terpisah, Anda harus melacak beberapa file dan menentukan bahwa perubahan dan analisis yang dilakuan masih konsisten.
Pendekatan lain yang kondusif mungkin dengan membongkar data tahun sebelumnya, atau produk lama yang tidak ada lagi ada dalam inventaris Anda. Dengan kata lain, ini yaitu data yang tidak dipakai lagi, tetapi tetap perlu untuk disimpan salinannya.
Cara memisahkan Google Sheets dengan kondusif
Pendekatan terbaik saat membagi file Google Sheets yang sungguh besar adalah membuat salinan gres apalagi dulu (selaku Backup), beri label setiap halaman dengan terperinci kemudian hapus semua data yang tidak relevan dengan halaman. Misalnya, bila Anda ingin memindahkan data tahun 2018 ke file tersendiri, maka berikut yaitu langkah-langkahnya:
- Buat salinan file masternya, dengan semua datanya (backup)
- Beri nama pada file Google Sheet baru ini dan sertakan "2018" di suatu daerah 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 biar menyisihkan atau memperlihatkan data 2018
Setelah semua langkah akhir untuk arsip data anda, mungkin Anda ingin menghapusnya dari Lembar master Anda. Namun, sungguh diusulkan untuk menyimpan apalagi dahulu salinan utuh dari file master Google Sheet Anda sebelum Anda mulai menghapus data apa pun.
Anda akan selsai dengan master Google Sheet baru yang hanya memiliki data dikala ini, atau data langsung, dan semoga akan menjadi jauh lebih singkat.
Anda juga mampu melakukan perhitungan atau analisis data secara lokal dan lalu menyatukan kembali data usang kedalam master Google Sheet Anda memakai rumus IMPORTRANGE.
22. Gunakan Conditional Formating secara terbatas
Conditional Formating yakni fitur yang sungguh anggun untuk menyertakan konteks tambahan ke Google Sheets, misalnya menyoroti outlier atau nilai di luar ambang batas. Semisal menggunakan di file penjadwalan untuk menyinari baris dengan tanggal hari ini.
Namun, hal ini mampu sangat lambat pada data besar karena Conditional Formating diterapkan berdasarkan pada tiap cell (cell-by-cell).
Makara paling baik hanya dipakai untuk tabel data kecil dan di tabel penyajian dan /atau laporan.
23. Manfaatkan keunggulan Apps Script
Apps Script adalah bahasa scripting berbasis Javascript yang dapat dipakai untuk memperluas fungsionalitas Google Sheets (dan berinteraksi dengan layanan Google yang lain).
Kemungkinan penggunaannya di sini sungguh luas, mencakup semuanya, mulai dari mengotomatiskan proses berulang untuk mempercepat alur kerja Anda hingga membuat fungsi custom untuk membuat lebih mudah Anda mengetik rumus rumit berulang-ulang (lihat bab selanjutnya).
Aplikasi Script sangat efektif dalam melakukan banyak perhitungan, terutama yang berulang, kadang kala lebih cepat ketimbang 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 mengkalkulasikan 100.000 baris, dan pemanis sekitar 30 detik untuk menyalin (copy) dan menempelnya (paste) sebagai values/nilai (praktik terbaik yang lain untuk mengoptimalkan Google Sheet — lihat nomor 6 di atas).
Mengganti rumus tersebut dengan Apps Script dan melakukan rentang data yang sama memerlukan waktu sekitar 16 detik untuk menempelkan keseluruhan 100.000 nilai, jadi kurang dari separuh dari waktu menggunakan rumus biasa.
Berikut aba-aba 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 yakni topik yang berlawanan dan merupakan topik yang sungguh besar, jadi tidak akan dibahas di dalam postingan ini. Namun perhatikan, kuota dan batasan yang berlaku untuk instruksi Apps Script.
24. Gunakan formula khusus/custom secara terbatas
Rumus khusus/custom, juga diketahui sebagai fungsi yang diputuskan sendiri oleh pengguna, atau user-defined function (UDF), yakni fungsi khusus yang ditulis di jendela Script editor (Apps Script ).
Rumus custom yang rumit akan menjadi sungguh lambat kalau memakai metode atau cara yang serupa 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 dikala dipakai:
Menyalin dan menempel aba-aba ini pada 1.000 baris menjadikannya SANGAT lambat. Butuh waktu sekitar 10 detik, namun 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 yaitu ketika membaca dari, dan saat menulis ke, Google Sheet, karena ini terjadi melalui API spreadsheet, jadi tergantung pada jaringan atau koneksi wifi.
Yang terbaik yaitu meminimalisir jumlah panggilan baca / tulis ke Google Sheet Anda sebisa mungkin.
Katakanlah Anda ingin melakukan perhitungan pada 10.000 cell, maka akan lebih baik kalau membaca sekali seluruhnya (10k cell) ke dalam fungsi, melaksanakan perkiraan dan kemudian mengeluarkan balasannya kembali ke Google Sheet dalam sekali jalan, dalam sebuah rentang.
Misalnya, ubah rumus di atas menjadi rumus yang lebih lazim, yang mendapatkan input rentang dan menciptakan output berskala 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 melaksanakan perkiraan yang sama pada 1.000 sel dalam waktu kurang dari satu detik, yang secara signifikan lebih singkat. 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, yakni 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 deadline.
Baca lebih lanjut wacana optimasi fungsi custom pada dokumentasi Google.
25. Tips lainnya
Terkadang hal itu mungkin bukan duduk perkara yang berasal dari Google Sheets yang menjadikan waktu respons menjadi pelan. Sebaiknya coba seni manajemen berikut untuk melihat apakah ada yang mampu menuntaskan problem Anda:
> Tutup dan buka kembali Google Sheet (sering kali ini merupakan perbaikan paling sederhana yang mampu berfungsi).
> Refresh browser.
> Bersihkan cache dan cookie.
> Coba browser atau tata cara operasi lain. Pastikan browser dan metode operasi yang berlangsung dikala ini yaitu model modern.
> Nonaktifkan plugins atau ekstensi browser.
> Jika memungkinkan, cobalah mereplikasi problem yang sama di komputer lain untuk menilik apakah perangkat keras komputerkah yang berurusan. Kecepatan perhitungan Google Sheet sebagian besar tergantung pada sumber daya lokal.
> Cobalah mereplikasi persoalan Anda di jaringan internet yang lain untuk memeriksa apakah itu masalah jaringan. Sebagian besar fungsi dilaksanakan secara lokal di browser, tetapi beberapa fungsi, seperti IMPORT, membutuhkan saluran ke Internet (dan juga Apps Script yang dilakukan di server Google).
> Coba nonaktifkan dan aktifkan kembali saluran offline.
> atau, cobalah komputer yang lebih cepat
26. Memahami perubahan di cloud yang memerlukan waktu dalam penyebarannya
Ingat bahwa jikalau Anda bekerja secara real-time dengan kolaborator lain, perubahan mungkin memerlukan waktu beberapa ketika untuk disinkronkan.
Pengguna lain dan file apps script akan melihat versi cache dari file Google Seeet sampai semua operasi sinkronisasi final, dan ini dapat memengaruhi kehadiran beberapa cell tertentu. Misalnya, mereka mungkin telah final menghitung dalam Google Seeet Anda namun masih memberikan kosong atau #N/A dalam penampilan yang dilihat oleh kolaborator.
Namun perlu diingat, hal ini yaitu hal yang sungguh kecil dan seringkali tidak berpengaruh. Dalam pengalaman, hal itu semua terjadi begitu cepat sehingga terasa nyaris instan, walaupun sekali lagi, data atau file Google Sheet yang sangat besar dan lambat akan lebih terpengaruh oleh hal ini.
27. Tahu kapan saatnya pindah ke basis data
Akan tiba saatnya, dan mungkin Anda telah disaat dimana, ketika Anda harus mengakui kekalahan dan mendapatkan bahwa Google Sheets tidak dapat menyanggupi semua kebutuhan data Anda lagi. Untuk semua kemampuannya, yang banyak, batas 5 juta cell dapat terisi dengan sangat cepat dengan data dunia aktual ataupun data realtime.
Jauh sebelum Google Sheet Anda mendekati batas 5 juta cell, Anda harus menimbang-nimbang langkah selanjutnya di luar Google Sheet yang lambat.
Pindahkan data ke arsip basis data. Anda masih tetap bisa melakukan pekerjaan di lingkungan Google Sheet namun pindahkan penyimpanan data ke basis data khusus.
Opsi Basis Data Google
Dalam ekosistem Google, ada fitur ihwal BigQuery dan Cloud SQL, keduanya adalah basis data dalam cloud. Namun, integrasinya agak sedikit rumit, jadi Anda perlu keahlian pengembangan untuk menghubungkan layanan ini.
Google BigQuery terintegrasi dengan Google Drive, sehingga Anda mampu memakai Google Sheets Anda sebagai tabel di BigQuery. Anda juga mampu memakai layanan Apps Script BigQuery untuk mengurus proyek BigQuery Anda.
Menghubungkan Google Sheet ke Cloud SQL secara eksklusif membutuhkan Apps Script, walaupun Anda mampu mengekspor data dari Google Sheets (mis. Sebagai CSV) dan mengimpornya ke Google Cloud SQL.
Carilah lebih banyak sumber daya pada topik-topik ini.
Kesimpulan
Postingan ini dibuat selengkap mungkin, namun mungkin masih lebih banyak pandangan baru di luar sana.
DIpersilahkan bila ada komentar dan pedoman lain tentang topik ini. Pikirkan posting ini selaku artikel yang hidup dan diharapkan akan disertakan di lain waktu.
Terima kasih telah membaca dan kami berharap wawasan yang terkandung di sini mampu berfaedah!
___________________________
Collins, Ben. 2018. Slow Google Sheets? Here are 27 ideas to try today. www.benlcollins.com