Merwyn dari Inggris mengirimkan masalah ini.
Apakah ada rumus tunggal di sel B2 yang bisa disalin ke seberang dan ke bawah untuk membuat tabel perkalian?
Tujuan Merwyn adalah memasukkan satu rumus di B2 yang dapat dengan mudah disalin ke 144 sel untuk membuat tabel referensi perkalian.
Mari serang ini sebagai pemula Excel dan lihat kesulitan apa yang kita hadapi. Reaksi awal seseorang mungkin memiliki rumus di B2 =A2*B1. Rumus ini memberikan hasil yang benar, tetapi tidak sesuai untuk tugas Merwyn.
Saat Anda menyalin rumus ini dari sel B2 ke sel C2, sel yang direferensikan dalam rumus juga berpindah ke satu sel. Rumusnya yang =A2*B1sekarang menjadi =C1*B2. Ini adalah fitur yang dirancang ke dalam Microsoft Excel dan disebut referensi rumus relatif. Saat Anda menyalin rumus, referensi sel dalam rumus juga memindahkan sejumlah sel yang sesuai ke depan dan ke bawah.
Ada kalanya Anda tidak ingin Excel menunjukkan perilaku ini dan kasus saat ini adalah salah satunya. Untuk mencegah Excel mengubah referensi saat menyalin sel, masukkan "$" sebelum bagian referensi yang ingin Anda bekukan. Contoh:
- $ A1 memberi tahu Excel bahwa Anda selalu ingin merujuk ke kolom A.
- B $ 1 memberi tahu Excel bahwa Anda selalu ingin merujuk ke baris 1.
- $ B $ 1 memberi tahu Excel bahwa Anda selalu ingin merujuk ke sel B1.
Mempelajari kode ini secara dramatis akan mengurangi waktu yang dibutuhkan untuk membuat lembar kerja. Daripada harus memasukkan 144 rumus, Merwyn dapat menggunakan singkatan ini untuk memasukkan satu rumus dan menyalinnya ke semua sel.
Melihat rumus Merwyn =B1*A2, kami menyadari bahwa bagian "B1" dari ekspresi harus selalu menunjuk ke angka di Baris 1. Ini harus ditulis ulang sebagai "B $ 1". Bagian "A2" dari rumus harus selalu menunjuk ke angka di kolom A. Ini harus ditulis ulang sebagai "$ A2". Jadi, rumus baru di sel B2 adalah =B$1*$A2.
Setelah memasukkan rumus di B2, saya dapat menyalin dan menempelkannya ke kisaran yang sesuai. Excel mengikuti instruksi saya dan setelah melakukan penyalinan, saya menemukan rumus berikut:
- Sel M2 berisi
=M$1*$A2 - Sel B13 berisi
=B$1*$A13 - Sel M13 berisi
=M$1*$A13
Masing-masing tipe rumus ini memiliki nama. Rumus tanpa tanda dolar disebut rumus relatif. Rumus yang baris dan kolomnya dikunci dengan tanda dolar disebut rumus absolut. Rumus yang baris atau kolomnya dikunci dengan tanda dolar disebut rumus campuran.
Ada metode singkat untuk memasukkan tanda dolar. Saat Anda mengetik rumus dan menyelesaikan referensi sel, Anda dapat menekan tombol untuk beralih di antara 4 tipe referensi. Misalkan Anda mulai mengetik rumus dan Anda mengetik =100*G87.
- Tekan F4 dan rumus Anda berubah menjadi
=100*$G$87 - Tekan F4 lagi dan rumus Anda berubah menjadi
=100*G$87 - Tekan F4 lagi dan rumus Anda berubah menjadi
=100*$G87 - Tekan F4 lagi dan rumus Anda kembali ke aslinya
=100*G87
Anda dapat berhenti selama entri rumus di setiap referensi sel untuk menekan F4 sampai Anda mendapatkan tipe referensi yang tepat. Penekanan tombol untuk memasukkan rumus Merwyn di atas adalah =B1*A1.
Salah satu penggunaan favorit saya dari referensi formula campuran muncul ketika saya memiliki daftar entri yang panjang di kolom A. Ketika saya ingin metode cepat dan kotor untuk menemukan duplikat, terkadang saya memasukkan rumus ini di sel B4 dan kemudian menyalinnya:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Perhatikan istilah kedua dari rumus VLOOKUP menggunakan referensi absolut ($ A $ 2) dan campuran ($ A3) untuk mendeskripsikan rentang pencarian. Dalam bahasa Inggris, saya memberi tahu Excel untuk selalu mencari dari sel $ A $ 2 ke sel di kolom A tepat di atas sel saat ini. Segera setelah Excel menemukan nilai duplikat, hasil rumus ini berubah dari # N / A! ke suatu nilai.
Dengan penggunaan kreatif $ dalam referensi sel, Anda dapat memastikan bahwa satu rumus tunggal dapat disalin ke banyak sel dengan hasil yang benar.
