Merwyn dari England menghantar masalah ini.
Adakah terdapat satu formula dalam sel B2 yang dapat disalin di seberang dan ke bawah untuk membuat jadual pendaraban?
Tujuan Merwyn adalah memasukkan satu formula dalam B2 yang dapat disalin dengan mudah ke semua 144 sel untuk membuat jadual rujukan pendaraban.
Mari serang ini sebagai pemula Excel dan lihat perangkap apa yang kita hadapi. Reaksi awal seseorang mungkin mempunyai formula dalam B2 =A2*B1. Formula ini menghasilkan hasil yang betul, tetapi tidak sesuai untuk tugasan Merwyn.
Apabila anda menyalin formula ini dari sel B2 ke sel C2, sel yang dirujuk dalam formula juga bergerak di atas satu sel. Formula yang =A2*B1sekarang menjadi =C1*B2. Ini adalah ciri yang direka ke dalam Microsoft Excel dan disebut sebagai rujukan formula relatif. Semasa anda menyalin formula, rujukan sel dalam formula juga memindahkan bilangan sel yang sesuai ke atas dan ke bawah.
Ada kalanya anda tidak mahu Excel menunjukkan tingkah laku ini dan kes semasa adalah salah satu daripadanya. Untuk mengelakkan Excel mengubah rujukan semasa menyalin sel, masukkan "$" sebelum bahagian rujukan yang ingin anda beku. Contoh:
- $ A1 memberitahu Excel bahawa anda selalu mahu merujuk pada ruangan A.
- B $ 1 memberitahu Excel bahawa anda selalu mahu merujuk pada baris 1.
- $ B $ 1 memberitahu Excel bahawa anda selalu mahu merujuk kepada sel B1.
Mempelajari kod ini akan mengurangkan masa yang diperlukan untuk membina lembaran kerja secara dramatik. Daripada harus memasukkan 144 formula, Merwyn dapat menggunakan singkatan ini untuk memasukkan satu formula tunggal dan menyalinnya ke semua sel.
Melihat rumus Merwyn =B1*A2, kita menyedari bahawa bahagian ungkapan "B1" harus selalu menunjukkan angka dalam Baris 1. Ini harus ditulis semula sebagai "B $ 1". Bahagian "A2" formula harus selalu menunjukkan angka di lajur A. Ini harus ditulis semula sebagai "$ A2". Jadi, formula baru dalam sel B2 adalah =B$1*$A2.
Setelah memasukkan formula di B2, saya dapat menyalin dan menempelkannya ke julat yang sesuai. Excel mengikuti arahan saya dan setelah menyalinnya, saya dapati formula berikut:
- Sel M2 mengandungi
=M$1*$A2 - Sel B13 mengandungi
=B$1*$A13 - Sel M13 mengandungi
=M$1*$A13
Setiap jenis formula ini mempunyai nama. Rumus tanpa tanda dolar disebut formula relatif. Rumus di mana baris dan lajur dikunci dengan tanda dolar disebut formula mutlak. Rumus di mana baris atau lajur dikunci dengan tanda dolar disebut formula campuran.
Terdapat kaedah ringkas untuk memasukkan tanda dolar. Semasa anda mengetik formula dan menyelesaikan rujukan sel, anda boleh menekan butang untuk beralih antara 4 jenis rujukan. Katakan anda mula menaip formula dan anda menaip =100*G87.
- Tekan F4 dan formula anda berubah menjadi
=100*$G$87 - Tekan F4 sekali lagi dan formula anda berubah menjadi
=100*G$87 - Tekan F4 sekali lagi dan formula anda berubah menjadi
=100*$G87 - Tekan F4 sekali lagi dan formula anda kembali ke asal
=100*G87
Anda boleh berhenti sebentar semasa memasukkan formula di setiap rujukan sel untuk menekan F4 sehingga anda mendapat jenis rujukan yang tepat. Tekanan utama untuk memasukkan formula Merwyn di atas adalah =B1*A1.
Salah satu penggunaan rujukan formula campuran kegemaran saya muncul ketika saya mempunyai senarai panjang entri di lajur A. Apabila saya mahukan kaedah cepat dan kotor untuk mencari pendua, saya kadang-kadang memasukkan formula ini di sel B4 dan kemudian menyalinnya:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Perhatikan istilah ke-2 formula VLOOKUP menggunakan rujukan mutlak ($ A $ 2) dan campuran ($ A3) untuk menerangkan julat carian. Dalam bahasa Inggeris, saya menyuruh Excel mencari selalu dari sel $ A $ 2 ke sel di lajur A tepat di atas sel semasa. Sebaik sahaja Excel menemui nilai pendua, hasil formula ini berubah dari # N / A! kepada nilai.
Dengan penggunaan kreatif $ dalam rujukan sel, anda dapat memastikan bahawa satu formula dapat disalin ke banyak sel dengan hasil yang betul.
