Заполнение ячеек в excel в цикле

Обновлено: 04.07.2024

Хотя инструмент Мгновенное заполнение (Flash Fill) появился в Excel ещё с 2013-й версии, но почему-то для многих пользователей этот факт остался незамеченным. И совершенно напрасно. Во многих случаях он оказывается проще, легче и быстрее, чем аналогичные решения на формулах или макросах. По моему опыту, на тренингах эта тема вызывает постоянное "вау!" аудитории - независимо от продвинутости и/или усталости слушателей.

Механизм работы этого инструмента прост: если у вас есть один или несколько столбцов с исходными данными и вы начинаете набирать рядом в соседнем столбце их же, но в каком-либо нужном вам измененном виде, то Excel рано или поздно намекнёт, что готов продолжить дальше за вас:

Чтобы выявить логику (шаблон, pattern) преоборазования и запустить эту функцию Excel обычно хватает ввода 1-3 первых результирующих значений вручную. Если предложенный вариант вам подходит, то достаточно нажать Enter - и остаток списка будет доделан моментально.

Если вы уже ввели 2-3 первых значения, а продолжение всё не появляется, то можно форсировать процесс сочетанием клавиш Ctrl + E или использовать кнопку Мгновенное заполнение (Flash Fill) на вкладке Данные (Data) :

Давайте рассмотрим несколько примеров использования этого инструмента на практике, чтобы понять его возможности.

Извлечение слов из текста и перестановки

Написать формулу, которая извлекает, например, третье слово из текста в ячейке - маленький подвиг. Разобрать фразу по пробелу в разные колонки с помощью Данные - Текст по столбцам (Data - Text to Columns) тоже дело не быстрое. С помощью мгновенного заполнения это делается легко и красиво. Причем, можно попутно менять извлекаемые слова местами, комбинируя их в любом порядке:

Деление текста по регистру

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

Реализовать подобное формулами очень тяжко. Если без мгновенного заполнения, то поможет только макрос.

Склейка текста

Если можно делить, то можно и клеить! Мгновенное заполнение легко соберёт для вас длинную фразу из нескольких фрагментов, перемежая их нужными пробелами, запятыми, союзами или словами:

Извлечение отдельных символов

Обычно для вытаскивания отдельных символов и подстрок в Excel используются функции ЛЕВСИМВ (LEFT) , ПРАВСИМВ (RIGHT) , ПСТР (MID) и им подобные, но мгновенное заполнение с легкостью решает и эту задачу. Классический пример - формирование ФИО:

Извлечение только чисел, текста или дат

Если вы когда-нибудь пытались вытащить только нужный тип данных из буквенно-цифровой каши, то должны понимать всю сложность этой простой, на первый взгляд, задачи. Мгновенное заполнение и тут справляется "на ура", но нужен лёгкий пендель в виде Ctrl + E :

С извлечением текста тоже самое:

Даты - тоже не проблема (даже если они написаны в разных форматах):

Преобразование форматов чисел или дат

Мгновенное заполнение поможет изменить внешний вид имеющихся данных или привести их к "одному знаменателю". Например, обычную дату преобразовать "шиворот-навыворот" в Unix-формат:

Здесь нюанс в том, что перед вводом нужно заранее поменять формат итоговых ячеек на текстовый, чтобы Excel не пытался распознать введенные вручную в качестве образца "неправильные" даты.

Аналогичным образом можно также правильно представить телефонные номера, добавив код страны и трехзначный префикс оператора (города) в скобках:

Не забудьте сначала поменять формат ячеек в столбце В на текстовый - иначе Excel будет воспринимать значения начинающиеся со знака "+" как формулы.

Преобразование текста (чисел) в дату

Изменение регистра

Если вам достался текст с нЕпрАвИльНЫм рЕгисТроМ, то можно просто намекнуть в соседнем столбце к какому виду вы хотите его преобразовать - и мгновенное заполнение сделает за вас всю работу:

Чуть сложнее будет, если нужно изменить регистр по разному у разных частей текста. Например, сделать заглавными только второе слово, оставив первое в нормальном виде. Здесь двух введённых в качестве образца значений окажется недостаточно и придется внести правки, которые мгновенное заполнение тут же учтёт в результатах:

Ограничения и нюансы

При использовании в работе мгновенного заполнения следует помнить несколько моментов:

Свойство Cells объекта Range в VBA Excel, представляющее коллекцию ячеек заданного диапазона. Обращение к ячейкам диапазона с помощью свойства Cells.

Range.Cells – свойство, возвращающее коллекцию всех ячеек указанного диапазона.

Свойство Cells объекта Worksheet

Обращение к ячейке «A1» активного рабочего листа с помощью свойства Cells:

В данном случае в качестве объекта Range выступает диапазон всего активного рабочего листа (ActiveSheet). Полный путь к ячейке «A1» можно записать так:

MsgBox ThisWorkbook . ActiveSheet . Cells ( 1 ) . Address 'Результат: $A$1 MsgBox ThisWorkbook . ActiveSheet . Cells ( 1 , 1 ) . Address 'Результат: $A$1 MsgBox ThisWorkbook . ActiveSheet . Cells ( 1 , "A" ) . Address 'Результат: $A$1

Обращение в VBA Excel к ячейке «C5» с помощью свойства Cells по имени рабочего листа (Worksheet) в другой книге Excel:

MsgBox Workbooks ( "Книга1.xlsm" ) . Worksheets ( "Лист1" ) . Cells ( 65539 ) . Address 'Результат: $C$5 в Excel 2016 MsgBox Workbooks ( "Книга1.xlsm" ) . Worksheets ( "Лист1" ) . Cells ( 5 , 3 ) . Address 'Результат: $C$5 MsgBox Workbooks ( "Книга1.xlsm" ) . Worksheets ( "Лист1" ) . Cells ( 5 , "C" ) . Address 'Результат: $C$5

Обращение к диапазону «C5:G10» с помощью свойства Cells активного рабочего листа:

MsgBox Range ( Cells ( 5 , 3 ) , Cells ( 10 , 7 ) ) . Address 'Результат: $C$5:$G$10

Свойство Cells объекта Range

Обращение в VBA Excel к ячейкам заданного диапазона с помощью свойства Cells рассмотрим на коллекции ячеек диапазона «C5:G10». Обращаться будем к ячейке «D8» активного листа:

MsgBox Range ( "C5:G10" ) . Cells ( 17 ) . Address 'Результат: $D$8 MsgBox Range ( "C5:G10" ) . Cells ( 4 , 2 ) . Address 'Результат: $D$8 MsgBox Range ( "C5:G10" ) . Cells ( 4 , "B" ) . Address 'Результат: $D$8

Обратите внимание, что отсчет номеров строк, номеров и буквенных обозначений столбцов для указания адреса ячейки внутри диапазона ведется от верхней левой ячейки данного диапазона.

Обход диапазона ячеек циклом

Обход ячеек циклом For Each… Next

Обход ячеек циклом For Each… Next — это самый простой способ обхода всех ячеек заданного диапазона. Он может быть применен, например, для присвоения ячейкам свойств и значений или поиска ячейки с определенным свойством или значением.

Присвоение ячейкам диапазона «B3:F10» числовых значений, соответствующих их порядковым номерам (индексам) в диапазоне:

Поиск в диапазоне «B3:F10», заполненном предыдущим кодом VBA Excel значениями, ячейки со значением «27», окрашивание ее в зеленый цвет и выход из цикла:

Обход диапазона циклом For… Next

Цикл For… Next позволяет указывать переменные в качестве индексов ячеек или номеров строк и столбцов для обхода ячеек заданного диапазона.

Присвоение ячейкам диапазона «B3:F10» числовых значений, соответствующих их порядковым номерам (индексам) в диапазоне, с помощью цикла For… Next:

Если в блоке с оператором With вместо строки .Cells(i) = i указать строку без точки впереди — Cells(i) = i , то свойство Cells будет относиться не к диапазону Range("B3:F10") , а к рабочему листу (объекту ActiveSheet). В этом случае, порядковыми номерами будут заполнены первые 40 ячеек первой строки активного рабочего листа.

Применение в качестве параметров свойства Cells объекта Range переменных, задающих номера строк и номера столбцов указанного диапазона при обходе его ячеек циклом For… Next:

Допустим, у нас есть два открытых файла: «Книга1» и «Книга2», причем, файл «Книга1» активен и в нем находится исполняемый код VBA.

В общем случае при обращении к ячейке неактивной рабочей книги «Книга2» из кода файла «Книга1» прописывается полный путь:

Workbooks ( "Книга2.xlsm" ) . Sheets ( "Лист2" ) . Cells ( 5 , 3 ) Workbooks ( "Книга2.xlsm" ) . Sheets ( "Лист2" ) . Cells ( 5 , "C" )

Удобнее обращаться к ячейке через свойство рабочего листа Cells(номер строки, номер столбца), так как вместо номеров строк и столбцов можно использовать переменные. Обратите внимание, что при обращении к любой рабочей книге, она должна быть открыта, иначе произойдет ошибка. Закрытую книгу перед обращением к ней необходимо открыть.

Теперь предположим, что у нас в активной книге «Книга1» активны «Лист1» и ячейка на нем «A1». Тогда обращение к ячейке «A1» можно записать следующим образом:

Точно также можно обращаться и к другим ячейкам активного рабочего листа, кроме обращения ActiveCell, так как активной может быть только одна ячейка, в нашем примере – это ячейка «A1».

Если мы обращаемся к ячейке на неактивном листе активной рабочей книги, тогда необходимо указать этот лист:

Имя ярлыка может совпадать с основным именем листа. Увидеть эти имена можно в окне редактора VBA в проводнике проекта. Без скобок отображается основное имя листа, в скобках – имя ярлыка.

Обращение к ячейке по индексу

К ячейке на рабочем листе можно обращаться по ее индексу (порядковому номеру), который считается по расположению ячейки на листе слева-направо и сверху-вниз.

Например, индекс ячеек в первой строке равен номеру столбца. Индекс ячеек во второй строке равен количеству ячеек в первой строке (которое равно общему количеству столбцов на листе, зависящему от версии Excel) плюс номер столбца. Индекс ячеек в третьей строке равен количеству ячеек в двух первых строках плюс номер столбца. И так далее.

Для примера, Cells(4) та же ячейка, что и Cells(1, 4). Используется такое обозначение редко, тем более, что у разных версий Excel может быть разным количество столбцов и строк на рабочем листе.

По индексу можно обращаться к ячейке не только на всем рабочем листе, но и в отдельном диапазоне. Нумерация ячеек осуществляется в пределах заданного диапазона по тому же правилу: слева-направо и сверху-вниз. Вот индексы ячеек диапазона Range(«A1:C3»):

Обращение к ячейке Range("A1:C3").Cells(5) соответствует выражению Range("B2") .

Обращение к ячейке по имени

Если ячейке на рабочем листе Excel присвоено имя (Формулы –> Присвоить имя), то обращаться к ней можно по присвоенному имени.

Допустим одной из ячеек присвоено имя – «Итого», тогда обратиться к ней можно – Range("Итого") .

Запись информации в ячейку

Содержание ячейки определяется ее свойством «Value», которое в VBA Excel является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания «=»:

Функция мгновенного заполнения автоматически подставляет данные, когда обнаруживает закономерность. Например, с помощью мгновенного заполнения можно разделять имена и фамилии из одного столбца или объединять их из двух разных столбцов.

Примечание: Функция мгновенного заполнения доступна только в Excel 2013 и более поздних версий.

Предположим, что столбец A содержит имена, столбец B — фамилии, а вы хотите заполнить столбец C сочетаниями имен и фамилий. Если ввести полное имя в столбец C, функция мгновенного заполнения заполнит остальные ячейки соответствующим образом.

Введите полное имя в ячейке C2 и нажмите клавишу ВВОД.

Начните вводить следующее полное имя в ячейке C3. Excel определит закономерность и отобразит предварительное изображение остальной части столбца, заполненной объединенным текстом.

Для подтверждения предварительного просмотра нажмите клавишу ВВОД.

Если вариант заполнения не выводится, вероятно, эта функция не включена. Вы можете выбрать Данные > Мгновенное заполнение, чтобы применить заполнение вручную или нажать клавиши CTRL+E. Чтобы включить мгновенное заполнение, выберите Сервис > Параметры > Дополнительно > Параметры правки и установите флажок Автоматически выполнять мгновенное заполнение.

Введите полное имя в ячейке C2 и нажмите клавишу ВВОД.

Выберите Данные > Мгновенное заполнение или нажмите клавиши CTRL+E.

Excel определит закономерность в ячейке C2 и заполнит ячейки ниже.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Читайте также: