И до сега понякога с усмивка си спомням едно от първите си полеви корпоративни обучения преди 10 години.
Представете си: отвореният офис на представителството на международна компания за бързооборотни стоки, огромен като футболно игрище. Шикозен дизайн, скъпо офис оборудване, дрескод, експати, гукащи по ъглите – това е всичко 🙂 В една от заседателните зали започвам двудневно разширено обучение по актуалната тогава версия на Excel 2003 за 15 ключови служители от икономическия отдел , заедно с техния лидер. Запознаваме се, разпитвам ги за бизнес задачи, проблеми, моля ги да покажат няколко типични работни файла. Показват километрите на разтоварване от SAP, листовете с отчети, които правят за това и т.н. Е, това е познато нещо – мислено измислям темите и времето, настройвам се към публиката. С крайчеца на окото си забелязвам как един от участниците, демонстрирайки част от доклада си, търпеливо издърпва клетката с формулата до черния кръст в долния десен ъгъл на няколко хиляди реда, след което пропуска края на масата в движение, дърпа я назад и т.н. Не мога да издържа, прекъсвам го, като върти мишката около екрана и показва двойно щракване върху черния кръст, обяснявайки за автоматичното довършване до спирка.
Изведнъж осъзнавам, че публиката е подозрително притихнала и всички ме гледат странно. Неусетно се оглеждам накъдето мога – всичко е наред, ръцете и краката са на мястото си, мухата ми е закопчана. Мислено превъртам последните си думи в търсене на някаква ужасна клауза – май нямаше нищо престъпно. След това ръководителят на групата мълчаливо става, подава ми ръка и казва с каменно лице: „Благодаря ти, Николай. Това обучение може да бъде завършено.
Е, накратко, оказа се, че никой от тях няма представа за двойно щракване върху черен кръст и автодовършване. Някак исторически се е случило, че няма кой да им покаже такова просто, но необходимо нещо. Целият отдел тегли ръчно формули за хиляди редове, нещастници. И ето ме тук. Маслена сцена. Тогава ръководителят на отдела много помоли да не разкриват името на компанията им на никого 🙂
Няколко пъти по-късно имаше подобни ситуации, но само с отделни слушатели - повечето сега, разбира се, знаят тази функция.
Въпросът е друг. След първата радост от овладяването на такава прекрасна функция, повечето потребители разбират, че автоматичното копиране на формули чрез двойно щракване върху черния кръст (маркер за автоматично попълване) има всички положителни и отрицателни аспекти:
- Копирането не винаги се случва до края на масата. Ако таблицата не е монолитна, т.е. има празни клетки в съседни колони, тогава не е факт, че автоматичното попълване ще работи до края на таблицата. Най-вероятно процесът ще спре в най-близката празна клетка, преди да стигне до края. Ако има клетки, заети от нещо под колоната, автоматичното довършване ще спре точно върху тях.
- При копиране клетъчен дизайн разваля, защото по подразбиране се копира не само формулата, но и форматът. За да коригирате, щракнете върху бутона за опции за копиране и изберете Само ценности (Попълнете без формат).
- Няма бърз начин за удобно разтягане на формулата не надолу, а надясноосвен да тегли на ръка. Двойното щракване върху черния кръст е точно надолу.
Нека се опитаме да поправим тези недостатъци с прост макрос.
Натиснете клавишна комбинация наляво Alt + F11 или бутон Visual Basic етикет предприемач (Разработчик). Вмъкнете нов празен модул чрез менюто Вмъкване – Модул и копирайте текста на тези макроси там:
Sub SmartFillDown() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(0, -1).CurrentRegion If rng.Cells.Count > 1 Then n = rng.Cells(1).Row + rng.Rows. Брой - 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 Ако rng.Cells.Count > 1 Тогава n = rng.Cells(1).Column + rng.Columns.Count - ActiveCell.Column ActiveCell.AutoFill Destination:=ActiveCell.Resize(1, n), Тип: =xlFillValues End If End Sub
Такива макроси:
- може да запълва не само надолу (SmartFillDown), но и надясно (SmartFillRight)
- не разваляйте формата на клетките отдолу или отдясно – копира се само формулата (стойността).
- празните съседни клетки се игнорират и копирането се извършва точно до края на таблицата, а не до най-близката празнина в данните или първата заета клетка.
За по-голямо удобство можете да зададете клавишни комбинации на тези макроси с помощта на бутона Макроси – Опции (Макроси — Опции) точно там в раздела. предприемач (Разработчик). Сега ще бъде достатъчно да въведете желаната формула или стойност в първата клетка на колоната и да натиснете указаната клавишна комбинация, за да може макросът автоматично да запълни цялата колона (или ред):
Красота.
PS Част от проблема с копирането на формули в края на таблицата беше решен в Excel 2007 с появата на „интелигентни таблици“. Вярно, те не винаги и не навсякъде са подходящи. А отдясно Excel никога не се е научил да копира сам.
- Какво представляват макросите, как да ги използвате, къде да получите кода на Visual Basic и къде да го поставите.
- Интелигентни таблици в Excel 2007-2013
- Копирайте формули без изместване на връзката