Tanda $ di Excel kecil, tetapi itulah alasan formula Anda rusak saat Anda memindahkannya

Bagi sebagian orang, formula yang rusak terasa seperti bagian normal dari penggunaan Excel. Begitu sebuah formula di spreadsheet Excel mereka berhenti bekerja saat diseret di seluruh spreadsheet, mereka hanya beralih untuk memasukkan formula baris demi baris. Namun, itu sangat merepotkan, terutama karena formula Anda tidak harus rusak begitu saja saat Anda memindahkannya.

Dalam banyak kasus, perbedaan antara sebuah formula yang bekerja dengan sempurna di satu baris dan rusak di baris lain tergantung pada apakah Anda menambahkan tanda $. Simbol ini memberitahu Excel secara persis nilai mana yang ingin Anda hitung. Lebih sering daripada tidak, masalahnya adalah bahwa Anda secara tidak sengaja memberitahunya untuk menghitung nilai yang salah.

Rumus Anda sebenarnya tidak rusak

Excel baru saja memindahkan referensimu karena itu yang menurutnya kamu inginkan

Ketika Anda menulis formula seperti =A1+B1 di sel C1, Excel tidak selalu menafsirkannya sebagai "menambahkan A1 dan B1." Sebaliknya, Excel membaca formula tersebut secara relatif: tambahkan sel dua kolom ke kiri dan sel satu kolom ke kiri. Itulah sebabnya, jika Anda menarik formula tersebut ke bawah dari baris 1 ke baris 2 (dari C1 ke C2), Excel secara otomatis mengubahnya menjadi =A2+B2. Jika Anda menarik formula ke kanan ke D1, formula tersebut menjadi =B1+C1.

Dalam banyak kasus, perilaku ini berguna karena Excel menganggap Anda ingin pola perhitungan yang sama diterapkan di bagian lain spreadsheet Anda. Namun, hal itu tidak akan terjadi ketika formula Anda bergantung pada satu nilai tetap, seperti tarif pajak, kurs mata uang, persentase komisi, atau angka acuan yang disimpan di sel Z1. Setelah Anda menarik formula ke bawah, Excel akan menggeser referensi ke Z2, Z3, dan seterusnya, yang sering menyebabkan kesalahan atau hasil kosong karena sel-sel tersebut kosong.

Jadi, rumus itu sendiri sebenarnya bukan masalahnya. Masalah sebenarnya adalah referensi bergerak ketika Anda tidak menginginkannya, dan tanda $adalah yang memberitahu Excel untuk berhenti memindahkannya.

Logika tanda $

Simbol kecil yang melakukan pekerjaan yang sangat banyak di spreadsheet Anda

Tanda $ di bilah rumus membuat referensi absolut, sebuah jenis referensi sel di Excel yang mengunci sel, baris, atau kolom tertentu dalam suatu rumus. Tanpa tanda ini, Excel secara default menggunakan referensi relatif, yang berarti rumus akan menyesuaikan berdasarkan posisinya yang baru setiap kali Anda menyalin atau menyeretnya. Anda dapat menganggap tanda $ sebagai memberi tahu Excel: tidak peduli ke mana rumus ini bergerak, biarkan referensi ini tetap sama.

Cara kerja kunci itu sederhana. Tanda dolar menahan bagian manapun dari alamat sel yang didahuluinya:

Jika Anda ingin cara paling cepat untuk beralih antara jenis referensi ini, tempatkan kursor Anda pada referensi sel di bilah rumus dan tekan F4. Tekan pertama membuat referensi menjadi absolut ($A$1); tekan kedua hanya mengunci baris (A$1); tekan ketiga hanya mengunci kolom ($A1); dan tekan keempat mengembalikannya ke referensi relatif (A1).

Perilaku penguncian ini terutama berlaku saat Anda menyalin atau menyeret rumus. Jika Anda memotong dan menempelkan rumus sebagai gantinya, atau memindahkan sel secara manual dengan mouse Anda, Excel mempertahankan referensi asli seolah-olah mereka bersifat absolut. Tabel Excel juga berperilaku berbeda karena mereka bergantung pada referensi terstruktur, sehingga logika tanda $ tidak bekerja dengan cara yang sama.

Seperti apa ini terlihat dalam kehidupan nyata

Dari perhitungan pajak hingga pencarian

Bayangkan Anda perlu menghitung biaya kepatuhan pajak lokal untuk setiap pesanan dalam dataset Anda. Total Laba Anda ada di Kolom N, dan di tempat lain dalam spreadsheet Anda, Anda memiliki tabel Registri Pajak yang mencantumkan tarif pajak berdasarkan wilayah. Misalkan Sub-Sahara Afrika memiliki tarif pajak tetap sebesar 12%, yang disimpan di sel Q3. Untuk menghitung jumlah pajak untuk pesanan pertama Anda di baris 2, naluri pertama Anda mungkin menulis rumus ini:

=N2 * Q3

Untuk baris pertama, formula bekerja dengan sempurna. Excel mengambil Total Pendapatan dari N2 dan mengalikannya dengan tarif 12% di Q3. Masalah mulai muncul ketika Anda menyeret formula ke beberapa baris berikutnya.

Row 3 becomes: =N3 * Q4
Row 4 becomes: =N4 * Q5

Alih-alih terus menggunakan tarif pajak di Q3, Excel menggeser referensi ke bawah karena menganggap Anda ingin pergerakan relatif yang sama. Tidak lama kemudian, rumus mulai mengalikan pendapatan dengan sel kosong di Kolom Q, sehingga menghasilkan nol atau kesalahan #VALUE!, salah satu kesalahan paling menyebalkan di Excel. Untuk menghentikan Excel dari menggeser referensi dari tarif pajak itu, Anda perlu mengubah referensi menjadi referensi absolut:

=N2 * $Q$3

Tanda dolar itu memberitahu Excel: “Saat saya menyalin rumus ini ke 100 baris, perbarui referensi pendapatan sesuai kebutuhan, tetapi jangan pernah menjauh dari sel Q3.” Itu berarti N2 bisa menjadi N3, N4, dan N5 saat rumus bergerak, sementara $Q$3 tetap tetap sepanjang waktu.

Tentu saja, jika Anda bekerja dengan dataset global, kemungkinan menggunakan tarif pajak tunggal sangat kecil. Anda lebih mungkin menangani beberapa wilayah, mungkin tercantum di Kolom A, masing-masing dengan persentase pajaknya sendiri. Misalkan Anda memiliki tabel pencarian di sel P3:Q9 yang memetakan wilayah ke tarif pajaknya. Dalam hal ini, Anda dapat menghitung pajak secara dinamis dengan menyematkan VLOOKUP di dalam formula Anda.

Anda akan memasukkan rumus ini di kolom kosong pada baris 2 dan menyeretnya ke bawah:

=N2 * VLOOKUP(A2, $P$3:$Q$9, 2, FALSE)

Di sini, N2 dan A2 tidak menggunakan tanda dolar karena seharusnya diperbarui saat formula bergerak ke bawah lembar kerja. Untuk setiap baris, Excel akan secara otomatis merujuk pada pendapatan dan wilayah untuk baris tertentu itu. Namun, rentang pencarian sepenuhnya dikunci dengan tanda dolar: $P$3:$Q$9. Tanpa referensi yang terkunci tersebut, rentang pencarian akan bergeser ke bawah setiap kali Anda menyeret formula, berubah dari P4:Q10 menjadi P5:Q11, lalu P6:Q12, dan seterusnya. Akhirnya, Excel akan mulai kehilangan tabel pajak Anda sepenuhnya, yang mengakibatkan hasil yang salah atau kesalahan #N/A.

Namun, berkat tanda $, Anda dapat menjaga bagian-bagian dari spreadsheet Anda yang seharusnya tetap tetap di tempatnya, sementara bagian lain dari rumus Anda diperbarui secara dinamis.

Ya, tanda $ kecil itu lebih penting dari yang Anda kira

Karena hampir setiap rumus yang Anda tulis di beberapa sel tergantung pada referensi, ada baiknya memahami kapan harus menggunakan tanda $ dan tepatnya di mana menempatkannya. Setelah Anda tahu apa yang harus tetap tetap dan apa yang harus bergerak, Excel menjadi jauh lebih mudah untuk digunakan.

Setelah itu, Anda dapat menyeret, menyalin, dan memindahkan rumus di seluruh spreadsheet Anda tanpa khawatir tentang segala sesuatu atau apa pun yang rusak.