Kusvika zvino, dzimwe nguva nekunyemwerera ndinorangarira imwe yekutanga kudzidziswa kwekambani yekambani makore gumi apfuura.
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.
Pakarepo ndinoona kuti vateereri vanyarara zvinonyumwira uye munhu wese ari kunditarisa zvisinganzwisisike. Ndinozvitarisa ndisingaonekwe pese pandinokwanisa - zvese zvakanaka, maoko angu nemakumbo zviri panzvimbo, nhunzi yangu yakasungirirwa. Ini ndinodzosera kumashure mazwi angu ekupedzisira mukutsvaga imwe chirevo chinotyisa - pakanga pasina mhosva, zvinoita. Zvadaro, mukuru weboka racho anosimuka chinyararire, ondibata ruoko obva ati nechiso chedombo: “Maita basa, Nikolai. Kudzidziswa uku kunogona kupedzwa.
Zvakanaka, muchidimbu, zvakazoitika kuti hapana kana mumwe wavo aive neruzivo rwekudzvanya kaviri pamuchinjiko mutema uye autocomplete. Zvakaitika sezvinei munhoroondo kuti pakanga pasina munhu aivaratidza chinhu chakapfava zvakadaro asi chakakosha. Dhipatimendi rose rakadhonza mafomula nemaoko ezviuru zvemitsara, varombo vamwe. Uye ndiri pano. Nzvimbo yemafuta. Mukuru wedhipatimendi akabva akumbira zvakanyanya kuti asaburitse zita rekambani yavo kune chero munhu 🙂
Kanoverengeka gare gare pakanga paine mamiriro akafanana, asi chete nevateereri vega - vazhinji ikozvino, chokwadi, vanoziva basa iri.
Mubvunzo wakasiyana. Mushure memufaro wekutanga wekuziva chinhu chinoshamisa chakadaro, vashandisi vazhinji vanosvika pakunzwisisa kuti kukopa otomatiki kwemafomula nekudzvanya kaviri pamuchinjiko mutema (autocomplete marker) ine zvese zvakanaka uye zvakaipa:
- Kukopa hakuitike nguva dzose kusvika kumagumo etafura. Kana tafura isiri monolithic, kureva kuti pane masero asina chinhu mumakoroni ari pedyo, saka haisi chokwadi chokuti autocomplete ichashanda kusvika pakuguma kwetafura. Zvingangodaro, maitiro acho achamira pane imwe sero isina chinhu iri pedyo isati yasvika kumagumo. Kana paine maseru akagarwa nechimwe chinhu pazasi pekoramu, ipapo autocomplete inomira paari chaizvo.
- Pakukopa kugadzirwa kwesero kunoparadza, nekuti Nekumisikidza, haisi iyo fomula chete inokopwa, asiwo fomati. Kuti ugadzirise, tinya bhatani rekusarudza kopi uye sarudza Tsika chete (Zadza pasina fomati).
- Iko hakuna nzira yekukurumidza zvakare nyore kutambanudza fomula kwete pasi asi kurudyikunze kwekudhonza neruoko. Kudzvanya kaviri pamuchinjiko mutema kunongova pasi.
Ngatiedzei kugadzirisa zvikanganiso izvi neiyo macro yakapusa.
Dzvanya kiibhodhi kudimburira kuruboshwe Alt + F11 kana bhatani Visual Basic Tab yokuvaka (Mugadziri). Isa nyowani isina chinhu module kuburikidza nemenyu Pinza – Module uye kopira zvinyorwa zveaya macros ipapo:
Sub SmartFillDown() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(0, -1).CurrentRegion Kana rng.Cells.Count > 1 Then n = rng.Cells(1).Row + rng.Rows. Count - 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 Kana rng.Cells.Count > 1 Then n = rng.Masero(1).Column + rng.Columns.Count - ActiveCell.Column ActiveCell.AutoFill Destination:=ActiveCell.Resize(1, n), Type: =xlFillValues Inopera Kana Yapera Sub
Macros akadai:
- inogona kuzadza kwete pasi chete (SmartFillDown), asiwo kurudyi (SmartFillRight)
- usashatise mafomati emasero pazasi kana kurudyi - chete fomula (kukosha) inokopwa
- masero asina chinhu ari padyo anofuratirwa uye kukopa kunoitika chaizvo kumagumo etafura, uye kwete kune gaka riri padyo mu data kana sero rekutanga rakagarwa.
Kuti zvive nyore, unogona kugovera mapfupi ekhibhodi kune aya macros uchishandisa bhatani Macros - Sarudzo (Macros - Sarudzo) ipapo pane tab. yokuvaka (Mugadziri). Ikozvino zvichave zvakakwana kuti uise fomura yaunoda kana kukosha musero rekutanga rekoramu uye wodzvanya kiyi yakatsanangurwa musanganiswa kuti macro kuzadza otomatiki koramu yese (kana mutsara):
Runako.
PS Chikamu chedambudziko nekukopa mafomula kusvika kumagumo etafura chakagadziriswa muExcel 2007 nekuuya kwe "smart tables". Ichokwadi, haisi nguva dzose uye kwete kwose kwose kwakakodzera. Uye kurudyi, Excel haina kumbodzidza kukopa yega.
- Chii macros, maitiro ekuashandisa, kupi kwekuwana Visual Basic kodhi uye kupi nekuiisa.
- Smart matafura muExcel 2007-2013
- Kopa mafomula pasina chinongedzo chekuchinja