Умное автозаполнение вниз и вправо

До сих пор иногда с улыбкой вспоминаю один из своих первых выездных корпоративных тренингов лет 10 назад.

Представьте: огромный как футбольное поле опенспейс-офис российского представительства международной FMCG-компании. Шикарный дизайн, дорогая оргтехника, дресс-код, экспаты курлыкают по углам - вот это вот все :) В одной из переговорок начинаю двухдневный тренинг продвинутого уровня по текущей тогда версии Excel 2003 для 15 ключевых сотрудников экономического департамента вместе с их руководителем. Знакомимся, расспрашиваю их о бизнес-задачах, проблемах, прошу показать несколько типовых рабочих файлов. Показывают километровой длины выгрузки из SAP, простыни отчетов, которые они по этому всему делают и т.д. Ну, дело знакомое - мысленно прикидываю темы и тайминг, подстраиваюсь под аудиторию. Краем глаза замечаю, как один из участников, демонстрируя кусочек своего отчета, терпеливо тянет ячейку с формулой вниз за черный крестик в правом нижнем углу на несколько тысяч строк, потом проскакивает с лету конец таблицы, тянет обратно и т.д. Не выдержав, прерываю его кёрлинг мышью по экрану и показываю двойной щелчок по черному крестику, объясняя про автозаполнение вниз до упора. 

Вдруг понимаю, что в аудитории подозрительно тихо и все как-то странно на меня смотрят. Незаметно окидываю себя взглядом где могу - все ОК, руки-ноги на месте, ширинка застегнута. Мысленно отматываю назад свои последние слова в поисках какой-нибудь жуткой оговорки - не было ничего криминального, вроде бы. После этого главный в группе молча встает, жмет мне руку и с каменным лицом говорит: "Спасибо, Николай. На этом тренинг можно закончить."

Ну, короче говоря, выяснилось, что никто из них не имел понятия про двойной щелчок по черному крестику и автозаполнение. Как-то исторически так сложилось, что некому им было показать такую простую но нужную штуку. Тянули всем отделом формулы вручную на тысячи строк, бедолаги. И тут я. Сцена маслом. Руководитель отдела потом очень просил название их компании никому не озвучивать :)

Несколько раз потом были похожие ситуации, но только с отдельными слушателями - большинство сейчас эту функцию, конечно, знает. 

smart-autofill2.pngВопрос в другом. После первой радости от освоения столь прекрасной функции большинство пользователей доходит до понимания того, что у автоматического копирования формул двойным щелчком по черному кресту (маркеру автозаполнения) есть при всех положительных моментах и отрицательные:

  • Копирование не всегда происходит до конца таблицы. Если таблица не монолитная, т.е. в соседних столбцах есть пустые ячейки, то не факт, что автозаполнение сработает до конца таблицы. Скорее всего процесс остановится на ближайшей пустой ячейке, не дойдя до конца. Если ниже по столбцу есть занятые чем-то ячейки, то автозаполнение остановится на них совершенно точно.
  • При копировании портится дизайн ячеек, т.к. по-умолчанию копируется не только формула, но еще и формат. Для исправления надо щелкать по кнопке параметров копирования и выбирать Только значения (Fill without format).
  • Не существует быстрого способа также удобно протянуть формулу не вниз, а вправо, кроме как тянуть вручную. Двойной щелчок по черному крестику - это только вниз.

Давайте попробуем исправить эти недостатки с помощью простого макроса.

Нажмите сочетание клавиш левый Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer). Вставьте новый пустой модуль через меню Insert - Module и скопируйте туда текст этих макросов:

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.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
    If rng.Cells.Count > 1 Then
        n = rng.Cells(1).Column + rng.Columns.Count - ActiveCell.Column
        ActiveCell.AutoFill Destination:=ActiveCell.Resize(1, n), Type:=xlFillValues
    End If
End Sub

Такие макросы:

  • умеют заполнять не только вниз (SmartFillDown), но и вправо (SmartFillRight)
  • не портят формат ниже- или справа стоящих ячеек - копируется только формула (значение)
  • игнорируют пустые соседние ячейки и копирование происходит именно до конца таблицы, а не до ближайшего разрыва в данных или первой занятой ячейки.

Для пущего удобства можно назначить этим макросам сочетания клавиш, используя кнопку Макросы - Параметры (Macros - Options) там же на вкладке Разработчик (Developer). Теперь достаточно будет ввести нужную формулу или значение в первую ячейку столбца и нажать заданное сочетание клавиш, чтобы макрос автоматически заполнил весь столбец (или строку):

smart-autofill.gif

Красота.

P.S. Частично проблема с копированием формул до конца таблицы была решена в Excel 2007 с появлением "умных таблиц". Правда, не всегда и не везде они уместны. И вправо Excel самостоятельно копировать так и не научился.

Ссылки по теме




18.05.2015 19:27:55
С настолько классными макросами как на этом сайте столкнулся с жуткой проблемой 8)
Не знаю на какие клавиши можно назначать макросы. Все, которые знал - уже использовал.
Реально ли выложить где-нить Excel файл с доступными для макросов кнопками на русском и на английском.
А то бесит немного разные CTRL+B, которые в английской раскладке делают текст жирным, а в русской у меня исполняют макрос.
19.05.2015 13:13:00
А, неплохо бы, добавить эти функции в Вашу надстройку Plex.
20.05.2015 15:21:43
Уже :) В следующей версии будут.
21.05.2015 14:52:16
Николай, спасибо за статью и полезный макрос

Возможно, что к положительным / отрицательным моментам двойного щелчка по чёрному крестику в нижнем левом углу также следует отнести и возможное изменение значений. Иногда это полезно, а иногда нет

Я имею в виду, что, к примеру, есть колонка с данными. Рядом нужно вставить слово "Sun" для всех записей. Пишем "Sun" в правую ячейку, затем щёлкаем и получаем ниже: "Mon", "Tue", "Wed" и так далее. Это потому, что в Custom Lists есть последовательность дней недели (у меня установлена англоязычная версия Excel). То есть, при наличии Custom List возможно его распознавание и вставка. Если этого не нужно, то приходится вставлять значение "Sun" в две ячейки друг под другом, затем выделять их обе и потом уже двойной щелчок. Тогда происходит простое копирование

Но с другой стороны, это может иметь и положительную сторону. Когда в ячейке есть, к примеру, цифра 1, ниже – цифра 2. Выделяем обе и два раза щёлкаем – получаем вставку с нужной последовательностью (удобно при нумерации)

Однако эти нюансы можно переиначить, если после двойного щелчка раскрыть смарт-тэг внизу и убрать Fill Series (сменить на Copy Cells)

Уверен, что Вы это прекрасно знаете. Просто на моей машине есть программа Sun и Custom Lists с английский днями недели. Частенько Excel и я "не пониманием друг друга" из-за двойного щелчка и автозаполнения…
:)
20.06.2015 15:21:11
Николай, добрый день.
Мне кажется, что Offset(0, -1) и Offset(-1, 0) можно убрать без потери какого-либо функционала. Нет?
02.07.2015 13:08:50
Николай, добрый день.

Хочу поделиться своей хитростью автозаполнения формулами либо данными без потери форматирования ячеек.
1. Выделю ячейку с формулой или данными, которую нужно "размножить".
2. Перехожу к последней ячейке диапазона и выделяю ее с зажатой клавишей Shift (при этом происходит выделение всего диапазона). Если данные в диапазоне не содержат пустых ячеек, то выручает сочетание Ctrl+Shift+СтрелкаВниз
3. Нажимаю клавишу F2 (Excel переходит в режим редактирования формулы прямо в ячейке. Данную операцию можно заменить кликом ЛКМ в строке формул).
4. Нажимаю комбинацию Ctrl+Enter
Excel заполняет формулой/данными выделенный диапазон как при автозаполнении, но при этом целиком и полностью сохраняя форматирование каждой ячейки.
В работе данный прием, лично мне позволяет экономить уйму времени и значительно увеличивает скорость ввода формул в уже готовые таблицы не портя их внешний вид.

И отдельно хочу сказать большое спасибо за вашу работу и ваш канал на YouTube.  
29.12.2015 15:17:38
Дмитрий, СУПЕР!
Огромное спасибо.
Макрос Николая не всегда отрабатывает без ошибок, а Ваш прием сработал на отлично!
CTRL+D
CTRL+R
:)
28.07.2015 15:25:27
Нет, это не совсем то, что делает приведенный макрос.
18.08.2015 18:34:28
Здравствуйте! Огромное спасибо за макрос!!! Если не сложно поясните пожалуйста за что отвечает каждая строка.
06.09.2015 21:02:03
Я восхищен!..
Николай, у меня нет слов!... Насколько легко, доступно и ясно Вы разъясняете механизмы решения ежедневно возникающих задач! Я даже сначала не поверил, что у меня так складно выйдет. Но в самом деле, когда знаешь - все гораздо проще кажется. Огромное спасибо, Ваша магия Excel просто завораживает!
24.10.2015 14:42:13
Николай, а вот у меня что-то не получается. Все делаю по алгоритму выше...но заполнение вправо не работает, до тех пор пока не заполнена ячейка последней строки перед формулой в третьем столбце таблицы. То есть, упираясь в пустую ячейку перед формулой третьего столбца, макрос останавливается. При заполнении последней ячейки перед формулой 3-го столбца, макрос работает как следует. Использовал в Excel 2013.





В случае если пустой является последняя строчка перед формулой во втором столбце, то выдает ошибку на скрине ниже.





29.12.2015 11:18:14
Тоже получил подобную ошибку.
Пока не пойму причины.
22.11.2017 19:18:38
Это потому, что ячейка B9 со всех сторон окружена пустыми ячейками, т.е. макрос не может понять, где границы таблицы.
Наверх