Konten
Masalah
Misalkan kita memiliki tabel sederhana seperti ini, di mana jumlahnya dihitung untuk setiap bulan di dua kota, dan kemudian totalnya dikonversi ke euro dengan kurs dari sel kuning J2.
Masalahnya adalah jika Anda menyalin rentang D2:D8 dengan rumus di tempat lain pada lembar, maka Microsoft Excel akan secara otomatis memperbaiki tautan dalam rumus ini, memindahkannya ke tempat baru dan berhenti menghitung:
Tugas: menyalin rentang dengan rumus agar rumus tidak berubah dan tetap sama, menjaga hasil perhitungan.
Metode 1. Tautan absolut
Metode 2: Nonaktifkan sementara rumus
Untuk mencegah rumus berubah saat menyalin, Anda perlu (sementara) memastikan bahwa Excel berhenti memperlakukannya sebagai rumus. Ini dapat dilakukan dengan mengganti tanda sama dengan (=) dengan karakter lain yang biasanya tidak ditemukan dalam rumus, seperti tanda hash (#) atau sepasang ampersand (&&) untuk waktu penyalinan. Untuk ini:
- Pilih rentang dengan rumus (dalam contoh kami D2:D8)
- Klik Ctrl + H di keyboard atau di tab Beranda – Temukan dan Pilih – Ganti (Beranda — Temukan&Pilih — Ganti)
- Pada kotak dialog yang muncul, masukkan apa yang kita cari dan apa yang kita ganti, dan di parameter (Pilihan) jangan lupa klarifikasi Lingkup pencarian – Rumus. Kami menekan Menggantikan semua (Menggantikan semua).
- Salin rentang yang dihasilkan dengan rumus yang dinonaktifkan ke tempat yang tepat:
- menggantikan # on = kembali menggunakan jendela yang sama, mengembalikan fungsionalitas ke rumus.
Metode 3: Salin melalui Notepad
Cara ini jauh lebih cepat dan mudah.
Tekan pintasan keyboard Ctrl+ atau tombol Tampilkan rumus tab rumus (Rumus — Menampilkan rumus), untuk mengaktifkan mode pemeriksaan rumus – alih-alih hasil, sel akan menampilkan rumus yang digunakan untuk menghitungnya:
Salin rentang kami D2: D8 dan tempel ke standar buku catatan:
Sekarang pilih semua yang ditempel (Ctrl + A), salin ke clipboard lagi (Ctrl + C) dan tempel di lembar di tempat yang Anda butuhkan:
Tetap hanya untuk menekan tombol Tampilkan rumus (Tampilkan Rumus)untuk mengembalikan Excel ke mode normal.
Catatan: metode ini terkadang gagal pada tabel kompleks dengan sel gabungan, tetapi dalam sebagian besar kasus, metode ini berfungsi dengan baik.
Metode 4. Makro
Jika Anda sering harus menyalin rumus seperti itu tanpa menggeser referensi, maka masuk akal untuk menggunakan makro untuk ini. Tekan pintasan keyboard Alt + F11 atau tombol Visual Basic tab pembangun (Pengembang), masukkan modul baru melalui menu Sisipkan – Modul dan salin teks makro ini di sana:
Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Resume Set Berikutnya copyRange = Application.InputBox("Pilih sel dengan formula untuk disalin.", _ "Salin formula persis", Default:=Selection.Address, Type := 8) Jika copyRange Bukan Apa-apa Kemudian Keluar dari Sub Set pasteRange = Application.InputBox("Sekarang pilih rentang tempel." & vbCrLf & vbCrLf & _ "Kisaran harus sama ukurannya dengan rentang sel " & vbCrLf & _ " yang asli untuk menyalin." , "Salin formula dengan tepat", _ Default:=Selection.Address, Type:=8) If pasteRange.Cells.Count <> copyRange.Cells.Count Kemudian MsgBox "Copy dan paste ranges bervariasi dalam ukuran!", vbExclamation, "Kesalahan penyalinan" Keluar dari Sub Akhir Jika Jika pasteRange Tidak Ada Lalu Keluar dari Sub Lain pasteRange.Formula = copyRange.Formula Akhiri Jika Akhir SubAnda dapat menggunakan tombol untuk menjalankan makro. Macro tab pembangun (Pengembang — Makro) atau pintasan keyboard Alt + F8. Setelah menjalankan makro, itu akan meminta Anda untuk memilih rentang dengan rumus asli dan rentang penyisipan dan akan menyalin rumus secara otomatis:
- Tampilan formula dan hasil yang nyaman secara bersamaan
- Mengapa gaya referensi R1C1 diperlukan dalam rumus Excel
- Cara cepat menemukan semua sel dengan rumus
- Alat untuk menyalin rumus yang tepat dari add-on PLEX