Sampai sekarang, terkadang sambil tersenyum saya ingat salah satu pelatihan perusahaan lapangan pertama saya 10 tahun yang lalu.
Bayangkan: ruang terbuka kantor perwakilan perusahaan FMCG internasional, sebesar lapangan sepak bola. Desain yang apik, peralatan kantor yang mahal, aturan berpakaian, ekspatriat yang berkumpul di sudut – itu saja 🙂 Di salah satu ruang pertemuan, saya memulai pelatihan lanjutan selama dua hari tentang versi Excel 2003 yang saat itu berlaku untuk 15 karyawan kunci departemen ekonomi , bersama dengan pemimpin mereka. Kami berkenalan, saya bertanya kepada mereka tentang tugas bisnis, masalah, saya meminta mereka menunjukkan beberapa file pekerjaan yang khas. Mereka menunjukkan berapa kilometer lamanya bongkar muat dari SAP, lembar-lembar laporan yang mereka buat mengenai hal ini, dll. Ya, itu hal yang familiar – saya secara mental memikirkan topik dan waktunya, menyesuaikan dengan audiensnya. Dari sudut mata saya, saya memperhatikan bagaimana salah satu peserta, yang mendemonstrasikan sebagian laporannya, dengan sabar menarik sel berisi rumus ke bawah dengan tanda silang hitam di pojok kanan bawah sebanyak beberapa ribu baris, lalu melewatkan bagian akhir. meja dengan cepat, menariknya kembali, dll. Karena tidak tahan, saya menyela dia dengan menggerakkan mouse di sekitar layar dan menunjukkan klik dua kali pada tanda silang hitam, menjelaskan tentang pelengkapan otomatis hingga berhenti.
Tiba-tiba saya menyadari bahwa penonton diam dengan curiga dan semua orang memandang saya dengan aneh. Saya tanpa terlihat melihat sekeliling saya di mana pun saya bisa – semuanya baik-baik saja, lengan dan kaki saya berada di tempatnya, lalat saya dikancingkan. Saya secara mental memundurkan kata-kata terakhir saya untuk mencari beberapa klausa yang mengerikan – sepertinya tidak ada kriminal. Setelah itu, kepala kelompok diam-diam bangkit, menjabat tangan saya dan berkata dengan wajah batu: “Terima kasih, Nikolai. Pelatihan ini dapat diselesaikan.
Singkatnya, ternyata tidak satu pun dari mereka yang tahu tentang mengklik dua kali pada tanda silang hitam dan pelengkapan otomatis. Entah bagaimana terjadi secara historis bahwa tidak ada yang menunjukkan kepada mereka hal yang begitu sederhana namun perlu. Seluruh departemen menarik formula secara manual untuk ribuan baris, teman-teman yang malang. Dan inilah aku. Adegan minyak. Kepala departemen kemudian sangat meminta untuk tidak mengungkapkan nama perusahaan mereka kepada siapa pun
Beberapa kali kemudian ada situasi serupa, tetapi hanya dengan pendengar individu – kebanyakan sekarang, tentu saja, mengetahui fungsi ini.
Pertanyaannya berbeda. Setelah kegembiraan pertama menguasai fitur yang luar biasa seperti itu, sebagian besar pengguna memahami bahwa penyalinan rumus secara otomatis dengan mengklik ganda pada tanda silang hitam (penanda pelengkapan otomatis) memiliki semua aspek positif dan negatif:
- Menyalin tidak selalu terjadi di akhir tabel. Jika tabel tidak monolitik, yaitu ada sel kosong di kolom yang berdekatan, maka bukan fakta bahwa pelengkapan otomatis akan berfungsi hingga akhir tabel. Kemungkinan besar, proses akan berhenti di sel kosong terdekat sebelum mencapai akhir. Jika ada sel yang ditempati oleh sesuatu di bawah kolom, maka pelengkapan otomatis akan berhenti tepat pada mereka.
- Saat menyalin kerusakan desain sel, karena Secara default, tidak hanya rumus yang disalin, tetapi juga formatnya. Untuk mengoreksi, klik tombol opsi salin dan pilih Hanya nilai (Isi tanpa format).
- Tidak ada cara cepat untuk meregangkan formula dengan mudah bukan ke bawah tapi ke kanankecuali ditarik dengan tangan. Mengklik dua kali pada salib hitam baru saja turun.
Mari kita coba perbaiki kekurangan tersebut dengan makro sederhana.
Tekan pintasan keyboard ke kiri Alt + F11 atau tombol Visual Basic tab pembangun (Pengembang). Masukkan modul kosong baru melalui menu Sisipkan – Modul dan salin teks makro ini di sana:
Sub SmartFillDown() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(0, -1).CurrentRegion If rng.Cells.Count > 1 Kemudian n = rng.Cells(1).Row + rng.Rows. Hitung - ActiveCell.Row ActiveCell.AutoFill Destination:=ActiveCell.Resize(n, 1), Type:=xlFillValues End If End Sub Sub SmartFillRight() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(-1, 0.CurrentRegion Jika rng.Cells.Count > 1 Kemudian n = rng.Cells(1).Column + rng.Columns.Count - ActiveCell.Column ActiveCell.AutoFill Destination:=ActiveCell.Resize(1, n), Ketik: =xlFillValues End If End Sub
Makro seperti itu:
- dapat mengisi tidak hanya ke bawah (SmartFillDown), tetapi juga ke kanan (SmartFillRight)
- jangan merusak format sel di bawah atau di sebelah kanan – hanya rumus (nilai) yang disalin
- sel-sel kosong yang berdekatan diabaikan dan penyalinan terjadi tepat ke ujung tabel, dan bukan ke celah terdekat dalam data atau sel pertama yang ditempati.
Untuk kenyamanan yang lebih besar, Anda dapat menetapkan pintasan keyboard ke makro ini menggunakan tombol Makro – Opsi (Makro — Opsi) di sana pada tab. pembangun (Pengembang). Sekarang cukup memasukkan formula atau nilai yang diinginkan di sel pertama kolom dan tekan kombinasi tombol yang ditentukan untuk makro untuk secara otomatis mengisi seluruh kolom (atau baris):
Kecantikan
PS Bagian dari masalah dengan menyalin rumus ke akhir tabel diselesaikan di Excel 2007 dengan munculnya "tabel pintar". Benar, mereka tidak selalu dan tidak di mana-mana sesuai. Dan di sebelah kanan, Excel tidak pernah belajar menyalin sendiri.
- Apa itu makro, bagaimana menggunakannya, di mana mendapatkan kode Visual Basic dan di mana menempelkannya.
- Tabel pintar di Excel 2007-2013
- Salin rumus tanpa pergeseran tautan