Titi di bayi, nigbami pẹlu ẹrin Mo ranti ọkan ninu awọn ikẹkọ ile-iṣẹ aaye akọkọ mi ni ọdun 10 sẹhin.
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.
Lojiji ni mo rii pe awọn olugbo ti dakẹ ni ifura ati pe gbogbo eniyan n wo mi ni ajeji. Mo wo ara mi ni aibikita nibikibi ti MO le - ohun gbogbo dara, awọn apa ati awọn ẹsẹ mi wa ni aaye, ti fi bọtini fo mi soke. Mo tun yi awọn ọrọ ikẹhin mi pada ni iṣaro ni wiwa diẹ ninu gbolohun ọrọ ẹru - ko si nkan ọdaràn, o dabi. Lẹ́yìn náà, olórí ẹgbẹ́ náà dìde ní ìdákẹ́jẹ́ẹ́, ó gbọn ọwọ́ mi, ó sì sọ pẹ̀lú ojú òkúta pé: “O ṣeun, Nikolai. Ikẹkọ yii le pari.
Daradara, ni kukuru, o wa ni pe ko si ọkan ninu wọn ti o ni imọran nipa titẹ-meji lori agbelebu dudu ati autocomplete. Bakanna o ṣẹlẹ ni itan-akọọlẹ pe ko si ẹnikan lati fihan wọn iru nkan ti o rọrun ṣugbọn pataki. Gbogbo ẹka fa awọn agbekalẹ pẹlu ọwọ fun ẹgbẹẹgbẹrun awọn laini, awọn ẹlẹgbẹ talaka. Ati pe emi wa. Epo si nmu. Olori ẹka naa beere pupọ lati ma ṣe afihan orukọ ile-iṣẹ wọn fun ẹnikẹni 🙂
Ni igba pupọ lẹhinna awọn ipo kanna wa, ṣugbọn pẹlu awọn olutẹtisi kọọkan - julọ ni bayi, dajudaju, mọ iṣẹ yii.
Ibeere naa yatọ. Lẹhin ayọ akọkọ ti iṣakoso iru ẹya iyanu kan, ọpọlọpọ awọn olumulo wa lati loye pe didaakọ adaṣe adaṣe ti awọn agbekalẹ nipasẹ tite lẹẹmeji lori agbelebu dudu (ami ami-ifọwọyi) ni gbogbo awọn aaye rere ati awọn odi:
- Didaakọ ko nigbagbogbo ṣẹlẹ si opin tabili. Ti tabili ko ba jẹ monolithic, ie awọn sẹẹli ofo wa ni awọn ọwọn ti o wa nitosi, lẹhinna kii ṣe otitọ pe autocomplete yoo ṣiṣẹ titi di opin tabili naa. O ṣeese julọ, ilana naa yoo duro ni sẹẹli ti o ṣofo ti o sunmọ ṣaaju ki o to de opin. Ti awọn sẹẹli ba wa ni nkan ti o wa labẹ ọwọn, lẹhinna autocomplete yoo da duro lori wọn ni deede.
- Nigbati didakọ cell design spoils, nitori Nipa aiyipada, kii ṣe agbekalẹ nikan ni a daakọ, ṣugbọn tun ọna kika. Lati ṣe atunṣe, tẹ bọtini awọn aṣayan ẹda ko si yan Awọn iye nikan (Fun laisi ọna kika).
- Ko si ọna ti o yara lati tun ni irọrun na agbekalẹ naa ko si isalẹ sugbon si ọtunayafi lati fa pẹlu ọwọ. Tite lẹẹmeji lori agbelebu dudu jẹ isalẹ.
Jẹ ki a gbiyanju lati ṣatunṣe awọn ailagbara wọnyi pẹlu Makiro ti o rọrun.
Tẹ ọna abuja keyboard si apa osi F11 giga + tabi bọtini visual Ipilẹ taabu developer (Olùgbéejáde). Fi titun sofo module nipasẹ akojọ Fi sii - Module ati daakọ ọrọ ti awọn macros wọnyi nibẹ:
Sub SmartFillDown () Dim rng Bi Ibiti, n Bi Gigun Ṣeto rng = ActiveCell.Offset (0, -1) .CurrentRegion Ti rng.Cells.Count> 1 Lẹhinna n = rng.Cells (1).Row + rng.Rows. Count - ActiveCell.Row ActiveCell.AutoFill Destination:=ActiveCell.Resize(n, 1), Iru:=xlFillValues Ipari Ti Ipari Sub Sub SmartFillRight() Dim rng Bi Range, n Bi Gigun Ṣeto rng = ActiveCell.Offset(-1, 0).CurrentEgbegbe Ti rng.Cells.Count> 1 Nigbana ni n = rng.Cells(1).Column + rng.Columns.Count - ActiveCell.Column ActiveCell.AutoFill Destination:=ActiveCell.Resize(1, n), Iru:: =xlFillAwọn iyeye Ipari Ti Ipin Ipari
Iru macros:
- le kun kii ṣe isalẹ nikan (SmartFillDown), ṣugbọn tun si apa ọtun (SmartFillRight)
- ma ṣe ikogun ọna kika ti awọn sẹẹli ni isalẹ tabi si ọtun - nikan ni agbekalẹ (iye) ti daakọ
- Awọn sẹẹli ti o ṣofo ni a kọju ati didaakọ waye ni deede si opin tabili, kii ṣe si aafo ti o sunmọ julọ ninu data tabi sẹẹli akọkọ ti tẹdo.
Fun irọrun nla, o le fi awọn ọna abuja keyboard si awọn macros wọnyi nipa lilo bọtini naa Macros - Awọn aṣayan (Macros - Awọn aṣayan) ọtun nibẹ lori taabu. developer (Olùgbéejáde). Bayi o yoo to lati tẹ agbekalẹ ti o fẹ tabi iye ninu sẹẹli akọkọ ti iwe naa ki o tẹ apapo bọtini kan pato fun macro lati kun gbogbo iwe laifọwọyi (tabi laini):
Ẹwa.
PS Apakan iṣoro naa pẹlu didaakọ awọn agbekalẹ si opin tabili ni a yanju ni Excel 2007 pẹlu dide ti “awọn tabili ọlọgbọn”. Lootọ, wọn kii ṣe nigbagbogbo ati kii ṣe nibikibi ti o yẹ. Ati si ọtun, Excel ko kọ ẹkọ lati daakọ lori ara rẹ.
- Kini macros, bii o ṣe le lo wọn, nibo ni lati gba koodu Ipilẹ Visual ati ibiti o le lẹẹmọ rẹ.
- Smart tabili ni tayo 2007-2013
- Da awọn agbekalẹ laisi iyipada ọna asopọ