Biaya tersembunyi dari referensi kolom penuh di Excel: Pelajari 3 alternatif untuk mengoptimalkan kinerja workbook Anda

Referensi seluruh kolom di Excel bisa sangat membebani kinerja, sering memaksa program untuk mengelola rentang lebih dari satu juta baris. Akibatnya, ini dapat memperlambat kinerja workbook Anda secara signifikan. Jadi, berhentilah menggunakan referensi A:A dan, sebagai gantinya, buat rentang dinamis.

Biaya tersembunyi dari pajak sejuta baris

Referensi seluruh kolom (A:A) dapat memaksa Excel untuk mengelola 1.048.576 baris, terutama dengan perhitungan gaya array atau fungsi yang mengevaluasi setiap sel. Ambil contoh dataset 50 baris ini. Di sel D1, Anda ingin menggunakan UNIQUE untuk mendaftar semua orang yang disebutkan di kolom B.

Untuk memastikan rumus menangkap data baru yang ditambahkan ke baris 51, 52, dan seterusnya, Anda bisa menggunakan referensi seluruh kolom:

=UNIQUE(B:B)

Rumus ini meninjau semua sel di kolom B—termasuk baris kosong di bawah rentang aktif, yang diwakili oleh nol liar.

Uji nol: Membuktikan beban

Jika Anda ingin melihat dengan tepat apa yang dipaksa Excel untuk ditangani saat Anda memasukkan referensi seluruh kolom, di sel D1, ketik:

=B:B

Saat Anda menggulir ke bawah, Anda akan melihat lebih dari satu juta angka nol di belakang, yang mewakili sel kosong di kolom B dari baris 51 ke bawah.

Sementara beberapa fungsi mengabaikan ruang kosong ini, yang lain menganggapnya sebagai input yang valid, mengembalikan nol yang membuat hasil Anda berantakan dan menunjukkan bahwa mesin terpaksa memproses kekosongan.

Excel modern menggunakan "range culling" untuk melewati baris kosong, tetapi ini bergantung pada area yang digunakan agar bersih. Jika baris 10.000 berisi spasi yang tersisa, Excel akan memperlakukan seluruh ruang kosong sebagai data aktif. Selain itu, rumus masih harus berinteraksi dengan seluruh area untuk memverifikasi batas-batasnya.

Berikut tiga alat bawaan yang dapat Anda gunakan untuk mencegah perilaku ini.

Solusi 1: Ubah data Anda menjadi tabel Excel

Cara paling efektif untuk menghindari pajak sejuta baris adalah dengan menggunakan tabel Excel—objek dinamis yang sepenuhnya mengubah cara Excel menghitung data Anda.

Tabel menggantikan cakupan besar dari referensi seluruh kolom dengan batasan yang jelas. Jika data Anda memiliki 50 baris, objek tabel tepat 50 baris panjangnya, sehingga ketika sebuah rumus merujuk ke tabel, mesin perhitungan Excel membatasi fokusnya pada wadah tersebut.

Pertama, pastikan dataset Anda memiliki header kolom. Selanjutnya, pilih salah satu sel di rentang data, tekan Ctrl+T, dan di kotak dialog Buat Tabel, pastikan 'Tabel saya memiliki header' dicentang. Kemudian, klik 'OK.'

Tabel menggunakan referensi terstruktur, yang selain lebih mudah dibaca, juga berfungsi sebagai pengunci dimensi data Anda. Jadi, untuk mencantumkan semua nama unik yang digunakan di kolom B, Anda perlu mengubah formula Anda dari pemindaian kolom tanpa batas menjadi pencarian tabel yang ditargetkan:

=UNIQUE(Table1[Manager])

Perhatikan bahwa angka nol di bagian bawah hasil hilang karena hanya data yang berada dalam batas tabel yang dirujuk dalam rumus.

Untuk mengubah nama tabel Anda (dari Table1 ke sesuatu seperti T_Managers), pilih salah satu sel dalam rentang tersebut, buka tab "Table Design", dan ketik nama baru di kolom Nama Tabel. Selain banyak manfaat lainnya, ini membuat formula menjadi lebih mudah dibaca.

Metode ini lebih baik daripada referensi seluruh kolom karena dua alasan:

  1. Perhitungan tanpa limbah: Karena tabel memiliki batas yang jelas, Excel tidak perlu memproses ruang kosong di bawah data.
  2. Rentang yang mengelola sendiri: Saat Anda menambahkan data ke baris 51, batas tabel akan meluas satu baris, dan rumus Anda tetap melekat pada rentang tanpa perlu penyesuaian manual.

Solusi 2: Gunakan fungsi TRIMRANGE

Jika Anda tidak ingin mengubah data Excel Anda menjadi tabel resmi, fungsi TRIMRANGE adalah alternatif terbaik berikutnya. Fungsi ini memungkinkan Anda menggunakan referensi seluruh kolom seperti sebelumnya, tetapi memberi tahu Excel untuk memotong rentang agar hanya mencakup sel yang benar-benar berisi data.

Fungsi TRIMRANGE hanya tersedia bagi mereka yang menggunakan Excel untuk Microsoft 365, Excel untuk web, serta aplikasi Excel di ponsel dan tablet.

TRIMRANGE memiliki tiga argumen:

=TRIMRANGE(range,[trim_rows],[trim_cols])

di mana :

  • range (diperlukan) adalah rentang yang akan dipangkas.
  • trim_rows (opsional) memberi tahu Excel apakah akan memangkas baris kosong di awal (1), baris kosong di akhir (2), atau keduanya (3).
  • trim_cols (opsional) memberi tahu Excel apakah akan memangkas kolom kosong di awal (1), kolom kosong di akhir (2), atau keduanya (3).

Untuk menerapkan logika ini pada dataset Anda, ketik:

=UNIQUE(TRIMRANGE(B:B,2))

Di sini, "2" memaksa rumus untuk memangkas sel kosong di akhir. Karena hanya ada satu kolom yang dirujuk dalam rumus, tidak perlu memasukkan argumen trim_cols.

Jika Anda mengklik dua kali sel D1, batas pemilih visual tetap mengelilingi seluruh kolom. Ini adalah fitur tampilan antarmuka pengguna yang memungkinkan Anda dengan mudah melihat rentang sumber yang menjadi dasar fungsi. Namun, fungsi TRIMRANGE adalah instruksi internal untuk mesin perhitungan, sehingga pemilihan visual tidak mencerminkan rentang yang dioptimalkan yang benar-benar diproses oleh rumus.

Inilah mengapa ini merupakan solusi yang baik untuk referensi seluruh kolom:

  1. Referensi yang dinamis namun rapi: Anda mendapatkan fleksibilitas dari referensi seluruh kolom—tetap dapat menemukan data baru yang Anda tambahkan nantinya. Namun, mesin perhitungan hanya dijalankan pada 50 baris data aktif.
  2. Fleksibilitas grid: Anda mendapatkan kinerja seperti tabel tanpa harus mengubah format visual lembar kerja Anda

Solusi 3: Gunakan operator referensi trim

Operator trim ref di Excel menawarkan manfaat yang sama persis seperti fungsi TRIMRANGE, tetapi dengan pengetikan yang lebih sedikit.

Operator referensi trim memiliki ketersediaan yang sama dengan fungsi TRIMRANGE: Excel untuk Microsoft 365, Excel untuk web, serta aplikasi Excel di ponsel dan tablet.

Dengan menambahkan titik (.) di salah satu sisi titik dua dalam rentang Anda, Anda memberi tahu Excel untuk menghapus sel kosong dari arah tersebut:

Di mana menambahkan titik

Kosong mana yang dipangkas

Setelah titik dua

Spasi di akhir

Sebelum titik dua

Spasi di awal

Kedua sisi titik dua

Spasi di awal dan akhir

Jadi, tetap dengan daftar yang sama seperti sebelumnya, ketik:

=UNIQUE(B:.B)

Tanda titik setelah titik dua memberitahu Excel untuk berhenti di baris terakhir yang berisi data—dalam hal ini, baris 50.

Ini adalah metode yang baik karena:

  1. Formula minimalis: Anda mendapatkan manfaat kinerja tinggi tanpa perlu mengubah format visual spreadsheet Anda atau membungkus rentang Anda dengan fungsi tambahan.
  2. Kontrol presisi: Anda dapat dengan mudah memilih dan mengubah dengan tepat void mana yang ingin diabaikan dengan menempatkan titik pada posisi yang tepat relatif terhadap tanda titik dua.
  3. Hasil data bersih: Metode ini mencegah fungsi modern menangkap nol di belakang, memastikan hasil Anda tetap akurat dan bebas dari data hantu.

Selain mengoptimalkan rumus Anda, ingat kebiasaan-kebiasaan ini untuk menjaga agar workbook Excel Anda tetap efisien:

  • BatasiFormatting Berlebihan: Jangan pernah menerapkan batas atau warna ke seluruh kolom.
  • Trik Ctrl+End: Gunakan pintasan ini untuk menemukan di mana Excel mengira data Anda berakhir. Jika melompat ke baris 1.000.000, berarti Anda memiliki sel hantu.
  • Mengatur ulang grid: Jika Anda memiliki sel hantu, pilih baris kosong, klik kanan, dan klik "Hapus" (jangan hanya menekan Backspace). Kemudian, simpan workbook Anda segera untuk mengatur ulang sel terakhir dan memperkecil ukuran file.

Artikel terkait