До сих пор иногда с улыбкой вспоминаю один из своих первых выездных корпоративных тренингов 10 лет назад.
Представьте себе: огромный, как футбольное поле, офис open space представительства международной FMCG-компании. Шикарный дизайн, дорогая оргтехника, дресс-код, воркующие по углам экспаты – вот и все 🙂 В одной из переговорных я начинаю двухдневное повышение квалификации по актуальной на тот момент версии Excel 2003 для 15 ключевых сотрудников экономического отдела. вместе со своим лидером. Мы знакомимся, я расспрашиваю о бизнес-задачах, проблемах, прошу показать несколько типовых рабочих файлов. Показывают километраж выгрузки из SAP, листы отчетов, которые они по этому делают и т. д. Ну, привычное дело — мысленно прикидываю темы и сроки, подстраиваюсь под аудиторию. Краем глаза замечаю, как один из участников, демонстрируя фрагмент своего доклада, терпеливо тянет вниз ячейку с формулой за черный крестик в правом нижнем углу на несколько тысяч строк, затем пропускает конец стол на лету, тянет его назад и т. д. Не выдержав, я прерываю его, водя мышкой по экрану и показываю двойной щелчок по черному крестику, объясняя про автодополнение до упора.
Внезапно я понимаю, что публика подозрительно тиха и все странно на меня смотрят. Незаметно оглядываюсь вокруг себя, где только могу – все в порядке, руки и ноги на месте, ширинка застегнута. Я мысленно перематываю свои последние слова в поисках какой-нибудь страшной оговорки – ничего криминального, кажется, не было. После этого руководитель группы молча встает, пожимает мне руку и с каменным лицом говорит: «Спасибо, Николай. Это обучение можно пройти.
Ну, короче, оказалось, что никто из них понятия не имеет о двойном нажатии на черный крестик и автозаполнении. Как-то так исторически сложилось, что некому было показать им такую простую, но нужную вещь. Весь отдел тянул формулы вручную на тысячи строк, бедняги. И вот я здесь. Масляная сцена. Начальник отдела тогда очень просил никому не раскрывать название их компании 🙂
Несколько раз позже были подобные ситуации, но только с отдельными слушателями — большинство сейчас, конечно, знают эту функцию.
Вопрос в другом. После первой радости освоения такой замечательной функции большинство пользователей приходят к пониманию, что автоматическое копирование формул по двойному клику по черному крестику (маркеру автозаполнения) имеет все свои положительные стороны и отрицательные:
- Копирование не всегда происходит в конец таблицы. Если таблица не монолитная, т.е. в соседних столбцах есть пустые ячейки, то не факт, что автозаполнение сработает до конца таблицы. Скорее всего, процесс остановится в ближайшей пустой ячейке, не дойдя до конца. Если ниже столбца есть ячейки, занятые чем-то, то автозаполнение остановится именно на них.
- При копировании конструкция ячейки портится, т.к. по умолчанию копируется не только формула, но и формат. Чтобы исправить, нажмите кнопку «Параметры копирования» и выберите Только ценности (Заполнять без формата).
- Не существует быстрого способа удобного расширения формулы. не вниз, а вправоразве что тянуть рукой. Двойной щелчок по черному крестику чуть ниже.
Попробуем исправить эти недостатки с помощью простого макроса.
Нажмите сочетание клавиш влево Alt + F11 или кнопка Визуальный Бейсик таб разработчик (Разработчик). Вставьте новый пустой модуль через меню Вставка – Модуль и скопируйте туда текст этих макросов:
Sub SmartFillDown() Уменьшить rng как диапазон, n как длинный Установить rng = ActiveCell.Offset(0, -1).CurrentRegion Если rng.Cells.Count > 1, то 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 Если 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
- Копирование формул без сдвига ссылки