Mpaka pano, nthawi zina ndikumwetulira ndimakumbukira imodzi mwazophunzitsidwa zanga zoyamba zamakampani zaka 10 zapitazo.
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.
Mwadzidzidzi ndimazindikira kuti omvera ali chete mokayikira ndipo aliyense akundiyang'ana modabwitsa. Ndimayang'ana mosadziwika bwino kulikonse komwe ndingathe - zonse zili bwino, manja ndi miyendo yanga ili pamalo, ntchentche yanga ili ndi mabatani. Ndimabwezera m'mbuyo mawu anga omaliza kufunafuna ndime yoyipa - panalibe chigawenga, zikuwoneka. Kenako, mkulu wa gululo anaimirira mwakachetechete, n’kundigwira dzanja n’kunena ndi nkhope yamwala kuti: “Zikomo, Nikolai. Maphunzirowa atha kutha.
Chabwino, mwachidule, zidapezeka kuti palibe aliyense wa iwo amene anali ndi chidziwitso chokhudza kudina kawiri pamtanda wakuda ndikumaliza. Zinachitika mwanjira inayake m'mbiri kuti panalibe wina wowawonetsa chinthu chophweka koma chofunikira. Dipatimenti yonse idapanga ma formula pamanja amizere masauzande, anthu osauka. Ndipo ine ndiri pano. Chiwonetsero cha mafuta. Mkulu wa dipatimentiyo adafunsa kwambiri kuti asaulule dzina la kampani yawo kwa aliyense 🙂
Kangapo pambuyo pake panali zochitika zofanana, koma ndi omvera okha - ambiri tsopano, ndithudi, amadziwa ntchitoyi.
Funso ndi losiyana. Pambuyo pa chisangalalo choyamba chodziwa chinthu chodabwitsa chotere, ogwiritsa ntchito ambiri amamvetsetsa kuti kukopera ma fomula mwa kudina kawiri pamtanda wakuda (cholembera autocomplete) kumakhala ndi zabwino zonse ndi zoyipa:
- Kukopera sikuchitika nthawi zonse mpaka kumapeto kwa tebulo. Ngati tebulo si monolithic, mwachitsanzo, pali maselo opanda kanthu mzati moyandikana, ndiye si zoona kuti autocomplete ntchito mpaka mapeto a tebulo. Nthawi zambiri, njirayi imayima pa cell yapafupi yopanda kanthu isanafike kumapeto. Ngati pali ma cell omwe ali ndi china chake pansi pa mzati, ndiye kuti autocomplete imayima pa iwo ndendende.
- Pokopera ma cell mapangidwe amawononga, chifukwa Mwachisawawa, sikuti fomula yokha imakopera, komanso mawonekedwe ake. Kuti mukonze, dinani batani la zosankha ndikusankha Zofunika zokha (Lembani popanda mtundu).
- Palibe njira yachangu yotambasuliranso chilinganizo mosavuta osati pansi koma kumanjakupatula kukoka ndi dzanja. Kudina kawiri pamtanda wakuda kuli pansi.
Tiyeni tiyese kukonza zolakwika izi ndi ma macro osavuta.
Dinani njira yachidule ya kiyibodi kumanzere Alt + F11 kapena batani Zooneka Basic tsamba Woyambitsa (Wolemba Mapulogalamu). Lowetsani gawo latsopano lopanda kanthu kudzera pa menyu Ikani - Module ndi kukopera zolemba za macros awa:
Sub SmartFillDown() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(0, -1).CurrentRegion Ngati rng.Cells.Count > 1 Ndiye 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 Ngati rng.Cells.Count > 1 Ndiye n = rng.Maselo(1).Column + rng.Columns.Count - ActiveCell.Column ActiveCell.AutoFill Destination:=ActiveCell.Resize(1, n), Type: =xlFillValues Kutha Ngati Kutha Sub
Ma macros awa:
- sangangodzaza pansi (SmartFillDown), komanso kumanja (SmartFillRight)
- osawononga mawonekedwe a maselo omwe ali pansipa kapena kumanja - njira yokhayo (mtengo) ndiyomwe imakopera
- Maselo opanda kanthu oyandikana amanyalanyazidwa ndipo kukopera kumachitika ndendende mpaka kumapeto kwa tebulo, osati kusiyana kwapafupi kwambiri ndi deta kapena selo loyamba lotanganidwa.
Kuti zikhale zosavuta, mutha kugawa njira zazifupi za kiyibodi ku ma macros pogwiritsa ntchito batani Macros - Zosankha (Macros - Zosankha) pomwepo pa tabu. Woyambitsa (Wolemba Mapulogalamu). Tsopano zidzakhala zokwanira kuyika fomula kapena mtengo womwe mukufuna mu cell yoyamba yagawo ndikusindikiza makiyi omwe atchulidwa kuti macro angodzaza mzere wonse (kapena mzere):
Kukongola.
PS Gawo lina lavuto pakukopera mafomu mpaka kumapeto kwa tebulo linathetsedwa mu Excel 2007 ndi kubwera kwa "matebulo anzeru". Zowona, sizili zoyenera nthawi zonse ndipo sizili paliponse. Ndipo kumanja, Excel sanaphunzire kukopera palokha.
- Kodi macros ndi chiyani, momwe mungagwiritsire ntchito, komwe mungapeze Visual Basic code ndi komwe mungayiike.
- Matebulo anzeru mu Excel 2007-2013
- Koperani mafomu opanda ulalo kusintha