Nganti saiki, kadhangkala kanthi eseman aku kelingan salah sawijining pelatihan perusahaan lapangan pisanan 10 taun kepungkur.
Imagine: the open space office of the representative office of an international FMCG company, huge as a football field. Chic design, expensive office equipment, dress code, expats cooing in the corners – that’s all 🙂 In one of the meeting rooms, I start a two-day advanced training on the then current version of Excel 2003 for 15 key employees of the economic department, along with their leader. We get acquainted, I ask them about business tasks, problems, I ask them to show several typical work files. They show the kilometer length of unloading from SAP, the sheets of reports that they make on this, etc. Well, it’s a familiar thing – I mentally figure out topics and timing, adjust to the audience. Out of the corner of my eye, I notice how one of the participants, demonstrating a piece of his report, patiently pulls the cell with the formula down by the black cross in the lower right corner for several thousand lines, then skips the end of the table on the fly, pulls it back, etc. Unable to stand it, I interrupt him curling the mouse around the screen and show a double-click on the black cross, explaining about auto-completion down to the stop.
Dumadakan aku nyadari yen penonton curiga sepi lan kabeh wong ndeleng aku aneh. Aku imperceptibly katon watara aku ngendi aku bisa - kabeh iku OK, tangan lan sikil ing panggonan, fly sandi kancing munggah. Aku mental mundur tembung pungkasan ing panelusuran sawetara klausa elek - ana apa-apa pidana, misale jek. Sawisé iku, kepala grup meneng tangi, salaman lan ngandika karo pasuryan watu: "Matur nuwun, Nikolai. Latihan iki bisa rampung.
Inggih, ing cendhak, ternyata ora ana sing duwe pitunjuk babagan ngeklik kaping pindho ing salib ireng lan autocomplete. Iku piye wae kedaden historis sing ora ana siji kanggo nuduhake wong kuwi prasaja nanging perlu. Kabeh departemen narik rumus kanthi manual kanggo ewu baris, wong miskin. Lan kene aku. Pemandangan lenga. Kepala departemen banjur njaluk banget supaya ora ngandhani jeneng perusahaane marang sapa wae 🙂
Kaping pirang-pirang mengko ana kahanan sing padha, nanging mung karo pamireng individu - paling saiki, mesthi, ngerti fungsi iki.
Pitakonan iku beda. Sawise kabungahan pisanan nguwasani fitur sing apik banget, umume pangguna ngerti manawa nyalin rumus otomatis kanthi ngeklik kaping pindho ing salib ireng (marker lengkap otomatis) duwe kabeh aspek positif lan negatif:
- Nyalin ora tansah kelakon ing mburi meja. Yen tabel ora monolithic, IE ana sel kosong ing kolom jejer, banjur iku ora kasunyatan sing autocomplete bakal bisa nganti mburi meja. Paling kamungkinan, proses bakal mandheg ing sel kosong sing paling cedhak sadurunge tekan pungkasan. Yen ana sel sing dikuwasani ing ngisor kolom kasebut, otomatis rampung bakal mandheg.
- Nalika nyalin desain sel rusak, amarga Kanthi gawan, ora mung rumus disalin, nanging uga format. Kanggo mbenerake, klik tombol pilihan salinan banjur pilih Mung nilai (Isi tanpa format).
- Ora ana cara sing cepet kanggo nggawe rumus kanthi gampang ora mudhun nanging ing sisih tengenkajaba kanggo narik karo tangan. Klik kaping pindho ing salib ireng mung mudhun.
Ayo coba ndandani kekurangan kasebut kanthi makro sing gampang.
Pencet trabasan keyboard kiwa Alt + F11 utawa tombol visual Basic tab pangembang (Pengembang). Lebokake modul kosong anyar liwat menu Insert - Modul lan salin teks makro kasebut ing kana:
Sub SmartFillDown () Dim rng Minangka Range, n Minangka Long Set rng = ActiveCell.Offset (0, -1).CurrentRegion Yen rng.Cells.Count> 1 Banjur n = rng.Cells (1).Row + rng.Rows. Count - ActiveCell.Row ActiveCell.AutoFill Destination:=ActiveCell.Resize(n, 1), Type:=xlFillValues End Yen End Sub Sub SmartFillRight() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(-1, 0).CurrentRegion Yen rng.Cells.Count > 1 Banjur n = rng.Cells(1).Column + rng.Columns.Count - ActiveCell.Column ActiveCell.AutoFill Destination:=ActiveCell.Resize(1, n), Type: =xlFillValues End Yen End Sub
Macro kaya ngono:
- bisa ngisi ora mung mudhun (SmartFillDown), nanging uga ing sisih tengen (SmartFillRight)
- aja ngrusak format sel ing ngisor utawa sisih tengen - mung rumus (nilai) sing disalin
- sel jejer kosong digatèkaké lan Nyalin ana persis kanggo mburi meja, lan ora kanggo longkangan paling cedhak ing data utawa sel pisanan dikuwasani.
Kanggo luwih gampang, sampeyan bisa nemtokake trabasan keyboard menyang makro kasebut nggunakake tombol kasebut Macro - Pilihan (Macros - Pilihan) tengen ana ing tab. pangembang (Pengembang). Saiki cukup kanggo ngetik rumus utawa nilai sing dikarepake ing sel pisanan kolom lan pencet kombinasi tombol sing ditemtokake kanggo makro kanthi otomatis ngisi kabeh kolom (utawa baris):
Beauty.
PS Bagéyan saka masalah karo nyalin rumus menyang mburi meja wis ditanggulangi ing Excel 2007 karo tekane saka "tabel pinter". Bener, ora mesthi lan ora cocog ing endi wae. Lan ing sisih tengen, Excel ora nate sinau nyalin dhewe.
- Apa macro, carane nggunakake, ngendi kanggo njaluk kode Visual Basic lan ngendi kanggo nempel.
- Tabel pinter ing Excel 2007-2013
- Nyalin rumus tanpa shift link