Salin formula tanpa anjakan pautan

Masalah

Katakan kita mempunyai jadual mudah seperti ini, di mana jumlah dikira untuk setiap bulan di dua bandar, dan kemudian jumlahnya ditukar kepada euro pada kadar dari sel kuning J2.

Masalahnya ialah jika anda menyalin julat D2:D8 dengan formula di tempat lain pada helaian, maka Microsoft Excel akan membetulkan pautan dalam formula ini secara automatik, mengalihkannya ke tempat baharu dan berhenti mengira:

Tugas: salin julat dengan formula supaya formula tidak berubah dan kekal sama, mengekalkan hasil pengiraan.

Kaedah 1. Pautan mutlak

Kaedah 2: Lumpuhkan formula buat sementara waktu

Untuk mengelakkan formula daripada berubah semasa menyalin, anda perlu (sementara) memastikan Excel berhenti menganggapnya sebagai formula. Ini boleh dilakukan dengan menggantikan tanda sama (=) dengan mana-mana aksara lain yang biasanya tidak ditemui dalam formula, seperti tanda cincang (#) atau sepasang ampersand (&&) untuk masa salinan. Untuk ini:

1. Pilih julat dengan formula (dalam contoh kami D2:D8)
2. Klik Ctrl + H pada papan kekunci atau pada tab Laman Utama – Cari dan Pilih – Ganti (Laman Utama — Cari&Pilih — Ganti)

   

3. Dalam kotak dialog yang muncul, masukkan perkara yang kita cari dan apa yang kita gantikan, dan masuk Parameter (Pilihan) jangan lupa jelaskan Skop carian – Formula. Kami tekan Ganti semua (Ganti semua).
4. Salin julat yang terhasil dengan formula yang dinyahaktifkan ke tempat yang betul:

   

5. Ganti # on = kembali menggunakan tetingkap yang sama, mengembalikan fungsi kepada formula.

Kaedah 3: Salin melalui Notepad

Kaedah ini lebih cepat dan mudah.

Tekan pintasan papan kekunci Ctrl+Ё atau butang Tunjukkan formula tab formula (Formula — Tunjukkan formula), untuk menghidupkan mod semakan formula – bukannya keputusan, sel akan memaparkan formula yang digunakan untuk mengira:

Salin julat D2:D8 kami dan tampalkannya ke dalam standard Notebook:

Sekarang pilih semua yang ditampal (Ctrl + A), salin ke papan keratan sekali lagi (Ctrl + C) dan tampalkannya pada helaian di tempat yang anda perlukan:

Ia kekal hanya untuk menekan butang Tunjukkan formula (Tunjukkan Formula)untuk mengembalikan Excel kepada mod biasa.

Nota: kaedah ini kadangkala gagal pada jadual kompleks dengan sel yang digabungkan, tetapi dalam kebanyakan kes ia berfungsi dengan baik.

Kaedah 4. Makro

Jika anda sering perlu melakukan penyalinan formula sedemikian tanpa mengalihkan rujukan, maka masuk akal untuk menggunakan makro untuk ini. Tekan pintasan papan kekunci Alt + F11 atau butang Visual Basic tab pemaju (Pembangun), masukkan modul baharu melalui menu Sisipan – Modul  dan salin teks makro ini di sana:

Sub Copy_Formulas() Malapkan copyRange As Range, pasteRange As Range On Error Resume Seterusnya Set copyRange = Application.InputBox("Pilih sel dengan formula untuk disalin.", _ "Salin formula dengan tepat", Lalai:=Selection.Address, Type := 8) Jika copyRange Tiada Apa-apa Kemudian Keluar Sub Set pasteRange = Application.InputBox("Sekarang pilih julat tampal." & vbCrLf & vbCrLf & _ "Julat mesti sama saiznya dengan julat sel " & vbCrLf & _ " asal untuk menyalin." , "Salin formula dengan tepat", _ Lalai:=Selection.Alamat, Jenis:=8) Jika pasteRange.Cells.Count <> copyRange.Cells.Count Kemudian MsgBox "Salin dan tampal julat berbeza mengikut saiz!", vbSeruan, "Salin ralat " Keluar Sub End Jika Jika pasteRange Tiada Apa-apa Kemudian Keluar Sub Lain pasteRange.Formula = copyRange.Formula Tamat Jika End Sub

Anda boleh menggunakan butang untuk menjalankan makro. Makro tab pemaju (Pembangun — Makro) atau pintasan papan kekunci Alt + F8. Selepas menjalankan makro, ia akan meminta anda memilih julat dengan formula asal dan julat sisipan dan akan menyalin formula secara automatik:

• Paparan formula dan keputusan yang mudah pada masa yang sama
• Mengapa gaya rujukan R1C1 diperlukan dalam formula Excel
• Cara cepat mencari semua sel dengan formula
• Alat untuk menyalin formula tepat daripada alat tambah PLEX