Досі іноді з усмішкою згадую один зі своїх перших виїзних корпоративних тренінгів 10 років тому.
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.
Раптом я розумію, що публіка підозріло тиха і всі дивляться на мене якось дивно. Непомітно озираюся, куди тільки можна – все гаразд, руки-ноги на місці, мушка застібнута. Подумки перемотую свої останні слова в пошуках якогось страшного пункту – нічого кримінального, здається, не було. Після цього староста групи мовчки встає, тисне мені руку і з кам'яним обличчям каже: «Дякую, Миколо. Це навчання можна завершити.
Ну, коротше, виявилося, що ніхто з них гадки не мав про подвійне клацання по чорному хрестику і автозаповнення. Так склалося історично, що не було кому показати їм таку просту, але потрібну річ. Весь відділ вручну витягував формули на тисячі рядків, бідолахи. І ось я тут. Нафта сцена. Керівник відділу тоді дуже попросив нікому не розкривати назву їхньої компанії 🙂
Пізніше кілька разів були подібні ситуації, але тільки з окремими слухачами – зараз більшість, звичайно, знають цю функцію.
Питання в іншому. Після першої радості від освоєння такої чудової функції більшість користувачів приходять до розуміння того, що автоматичне копіювання формул подвійним клацанням по чорному хрестику (маркеру автозаповнення) має всі позитивні сторони і негативні:
- Копіювання не завжди відбувається до кінця таблиці. Якщо таблиця не монолітна, тобто в сусідніх стовпцях є порожні комірки, то не факт, що автозаповнення буде працювати до кінця таблиці. Швидше за все, процес зупиниться на найближчій порожній клітинці, не досягнувши кінця. Якщо під стовпцем є клітинки, зайняті чимось, то автозаповнення зупиниться саме на них.
- При копіюванні дизайн клітини псує, тому що за замовчуванням копіюється не тільки формула, але і формат. Щоб виправити, натисніть кнопку параметрів копіювання та виберіть Тільки цінності (Заповнити без форматування).
- Немає швидкого способу також зручно розтягнути формулу не вниз, а вправохіба що тягнути вручну. Подвійне клацання по чорному хрестику просто вниз.
Спробуємо виправити ці недоліки простим макросом.
Натисніть комбінацію клавіш ліворуч Alt + F11 або кнопку Візуальний Бейсік таб розробник (розробник). Вставте новий порожній модуль через меню Вставка – модуль і скопіюйте туди текст цих макросів:
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
- Копіювати формули без зсуву посилання