Умное автозаполнение вниз и вправо
До сих пор иногда с улыбкой вспоминаю один из своих первых выездных корпоративных тренингов лет 10 назад.
Представьте: огромный как футбольное поле опенспейс-офис российского представительства международной FMCG-компании. Шикарный дизайн, дорогая оргтехника, дресс-код, экспаты курлыкают по углам - вот это вот все :) В одной из переговорок начинаю двухдневный тренинг продвинутого уровня по текущей тогда версии Excel 2003 для 15 ключевых сотрудников экономического департамента вместе с их руководителем. Знакомимся, расспрашиваю их о бизнес-задачах, проблемах, прошу показать несколько типовых рабочих файлов. Показывают километровой длины выгрузки из SAP, простыни отчетов, которые они по этому всему делают и т.д. Ну, дело знакомое - мысленно прикидываю темы и тайминг, подстраиваюсь под аудиторию. Краем глаза замечаю, как один из участников, демонстрируя кусочек своего отчета, терпеливо тянет ячейку с формулой вниз за черный крестик в правом нижнем углу на несколько тысяч строк, потом проскакивает с лету конец таблицы, тянет обратно и т.д. Не выдержав, прерываю его кёрлинг мышью по экрану и показываю двойной щелчок по черному крестику, объясняя про автозаполнение вниз до упора.
Вдруг понимаю, что в аудитории подозрительно тихо и все как-то странно на меня смотрят. Незаметно окидываю себя взглядом где могу - все ОК, руки-ноги на месте, ширинка застегнута. Мысленно отматываю назад свои последние слова в поисках какой-нибудь жуткой оговорки - не было ничего криминального, вроде бы. После этого главный в группе молча встает, жмет мне руку и с каменным лицом говорит: "Спасибо, Николай. На этом тренинг можно закончить."
Ну, короче говоря, выяснилось, что никто из них не имел понятия про двойной щелчок по черному крестику и автозаполнение. Как-то исторически так сложилось, что некому им было показать такую простую но нужную штуку. Тянули всем отделом формулы вручную на тысячи строк, бедолаги. И тут я. Сцена маслом. Руководитель отдела потом очень просил название их компании никому не озвучивать :)
Несколько раз потом были похожие ситуации, но только с отдельными слушателями - большинство сейчас эту функцию, конечно, знает.
Вопрос в другом. После первой радости от освоения столь прекрасной функции большинство пользователей доходит до понимания того, что у автоматического копирования формул двойным щелчком по черному кресту (маркеру автозаполнения) есть при всех положительных моментах и отрицательные:
- Копирование не всегда происходит до конца таблицы. Если таблица не монолитная, т.е. в соседних столбцах есть пустые ячейки, то не факт, что автозаполнение сработает до конца таблицы. Скорее всего процесс остановится на ближайшей пустой ячейке, не дойдя до конца. Если ниже по столбцу есть занятые чем-то ячейки, то автозаполнение остановится на них совершенно точно.
- При копировании портится дизайн ячеек, т.к. по-умолчанию копируется не только формула, но еще и формат. Для исправления надо щелкать по кнопке параметров копирования и выбирать Только значения (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). Теперь достаточно будет ввести нужную формулу или значение в первую ячейку столбца и нажать заданное сочетание клавиш, чтобы макрос автоматически заполнил весь столбец (или строку):
Красота.
P.S. Частично проблема с копированием формул до конца таблицы была решена в Excel 2007 с появлением "умных таблиц". Правда, не всегда и не везде они уместны. И вправо Excel самостоятельно копировать так и не научился.
Ссылки по теме
- Что такое макросы, как их использовать, где брать код на Visual Basic и куда его вставлять.
- Умные таблицы в Excel 2007-2013
- Копирование формул без сдвига ссылок
Не знаю на какие клавиши можно назначать макросы. Все, которые знал - уже использовал.
Реально ли выложить где-нить Excel файл с доступными для макросов кнопками на русском и на английском.
А то бесит немного разные CTRL+B, которые в английской раскладке делают текст жирным, а в русской у меня исполняют макрос.
Возможно, что к положительным / отрицательным моментам двойного щелчка по чёрному крестику в нижнем левом углу также следует отнести и возможное изменение значений. Иногда это полезно, а иногда нет
Я имею в виду, что, к примеру, есть колонка с данными. Рядом нужно вставить слово "Sun" для всех записей. Пишем "Sun" в правую ячейку, затем щёлкаем и получаем ниже: "Mon", "Tue", "Wed" и так далее. Это потому, что в Custom Lists есть последовательность дней недели (у меня установлена англоязычная версия Excel). То есть, при наличии Custom List возможно его распознавание и вставка. Если этого не нужно, то приходится вставлять значение "Sun" в две ячейки друг под другом, затем выделять их обе и потом уже двойной щелчок. Тогда происходит простое копирование
Но с другой стороны, это может иметь и положительную сторону. Когда в ячейке есть, к примеру, цифра 1, ниже – цифра 2. Выделяем обе и два раза щёлкаем – получаем вставку с нужной последовательностью (удобно при нумерации)
Однако эти нюансы можно переиначить, если после двойного щелчка раскрыть смарт-тэг внизу и убрать Fill Series (сменить на Copy Cells)
Уверен, что Вы это прекрасно знаете. Просто на моей машине есть программа Sun и Custom Lists с английский днями недели. Частенько Excel и я "не пониманием друг друга" из-за двойного щелчка и автозаполнения…
Нажатие клавиши CTRL – это хороший совет (спасибо, Виктор)
Однако он работает именно при протягивании. Я же в своём старинном комментарии имел ввиду двойной щелчок по чёрной точке в нижнем правом углу. Это удобный функционал копирования вдоль непустой соседней колонки. Но при этом всегда значения копируются с учётом последовательностей. Бывает, что столбец очень длинный и двойной щелчок гораздо удобнее ручного протягивания и хотелось бы (к сожалению, ничего не поменялось с 2015 года) иметь возможность отключения/включения последовательности данных при двойном щелчке
Мне кажется, что Offset(0, -1) и Offset(-1, 0) можно убрать без потери какого-либо функционала. Нет?
Хочу поделиться своей хитростью автозаполнения формулами либо данными без потери форматирования ячеек.
1. Выделю ячейку с формулой или данными, которую нужно "размножить".
2. Перехожу к последней ячейке диапазона и выделяю ее с зажатой клавишей Shift (при этом происходит выделение всего диапазона). Если данные в диапазоне не содержат пустых ячеек, то выручает сочетание Ctrl+Shift+СтрелкаВниз
3. Нажимаю клавишу F2 (Excel переходит в режим редактирования формулы прямо в ячейке. Данную операцию можно заменить кликом ЛКМ в строке формул).
4. Нажимаю комбинацию Ctrl+Enter
Excel заполняет формулой/данными выделенный диапазон как при автозаполнении, но при этом целиком и полностью сохраняя форматирование каждой ячейки.
В работе данный прием, лично мне позволяет экономить уйму времени и значительно увеличивает скорость ввода формул в уже готовые таблицы не портя их внешний вид.
И отдельно хочу сказать большое спасибо за вашу работу и ваш канал на YouTube.
Огромное спасибо.
Макрос Николая не всегда отрабатывает без ошибок, а Ваш прием сработал на отлично!
CTRL+R
Николай, у меня нет слов!... Насколько легко, доступно и ясно Вы разъясняете механизмы решения ежедневно возникающих задач! Я даже сначала не поверил, что у меня так складно выйдет. Но в самом деле, когда знаешь - все гораздо проще кажется. Огромное спасибо, Ваша магия Excel просто завораживает!
В случае если пустой является последняя строчка перед формулой во втором столбце, то выдает ошибку на скрине ниже.
Пока не пойму причины.
"протягивают" только одну ячейку.
как сделать автозаполнение сразу по нескольким колонкам и строкам?
спасибо!
А как по данному принципу сделать, чтобы значения протягивались только на одну ячейку вправо.
Например:
У меня есть ячейка со значением 01.11.2022. Нужно перетащить её вправо, чтобы получилось значение 02.11.2022 и т.д.
[img]data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAIIAAAArCAYAAAC0EFDbAAAEeElEQVR4nO2bX0hbVxzHP6k+6MNARh+SuQddhx2IkAVmfCsBbaawER3o28qM01iEYTskIii1iOJKQqEwrVXom9tDEyjTiQHZmzrIAi6jHfPPQ2PuUynswb11D0lsMnOu1+ruid7zeZGc+zvn9/3d+82559xcbb29va9RWJ5LsgUoSgNlBAWgjKDIooygAKC8paWFkZER2TqkMTExwerqqmwZ0mhsbGRzc5NLNptNthapWL3+srIyAGUEq9d/aATJOhSSUTNCFqvXnzNCufknop2Z51N49ubp8E6TPHIswE6Hl+mkoPsZI88Ibvpnu3EeftZYHh8jmjJXxaERzE2bR42f0NBPeM264qVEtY87o61oC330beS3zeJY6OP7Dd3eZ4rkW8Me8x1Bdv1PmGmXkD4PGfW7P2+F5fHCC56KMraQwNnmo9pELSWwRogQCK7hmZpBphfMr9+Ny6nx269F7gEbcRJ2B++bqKY0dg2RAB3ztUytDFEvVYiJVDuwo5EWrgXsOEycEkpgRsiQnL7FPH5CQ3KsYHr9qTSa7sXWM8nZUzJGgCTT3iC7/hAyvGB+/S9Ia3Y+/qSIE9wunFqaFyaqKSEjQGa9sIv/yRQekzObX3+K6MNlaB2l353X7O5nttvO8sMoZu4g5W8f/0skQND7nCmznSCDVJSxcbgzOstsd64xwULfGCbuHAGpD5QiBK5Gih8JXKX4kf8PaTNiKspYX1RO7jxK7NYgD6vXXxrbR4V01IyQxer1H64RbsbC8FmNZDnyuBkLwzvQ8rc1z0HOCLba29fV/zUAwbovZEuQSjnAzr0V2Tqk8cG3XgC+rHomWYk8KjpDb54j/PPjLZlapFDRGZItoWRQuwYFUMQI20uLVHaFqexaZCDxqvCgFsfTFWYgoTOiKOaYvitzubxhPHN7x+oRxYvajXLSfEc4p/UXGkGL0/MYvrs/yMHwZeYnf+aBlhWUiOP55hfWdQYTxRzbNxHDF0vjHx7k4P41iEUyJ0ykRxQvajfKSfNdoPoLfmvYjv/JOpcJ2gHepYktnmkAcXom9U2AJogRtefjbObgh+ZsfOZPcv8V2/sCPYJ42gTtziq97IcI6xflyx/3nNdfMCP8lUofCUjuZ6aj+uZ2fh9u0B1MFGOkb46Vp5mTVv9ela6eYvFG2vU4TT443/UbWyzaXTz4uoYrbxNjpG+WlblFfDGg7hqDzmPDhfEnHccouuOe8/oLbg0fVjuOBJzkG3UatpcW8cXSUNdA9K4rc+J09BSN12k3gl79pxnXCLLrL5gRrrjqaGKLpwlAe8k6Dj6y6/Q2sosw0leL0/M4DTQQvduM9zg9gnhhu0HeJt9Fqb/wxRS7i0c3XtIzGaYSB/7hTxnQM8IZkVmkAWzh69oCoOnGV6y1FdezvVQ8/hGicQzOaoL6RfnWXBenflvt7euvd+6tWPbJYu4R8x+NlnmP+ggVnSH1ZFGRQRlBASgjKLIoIyiAUnqd3UTyf37OfxfDigvmHJY0Arx5ISUfK+8c/gWynulL0qcJIgAAAABJRU5ErkJggg==[/img]
Я так понимаю, что лучше отталкиваться от ActiveCell.
P.S.: пробовал макрорекордером, но он отталкивается от строгого адреса указанной ячейки(диапазона)