Excel заполнить ячейки инкремент
Обновлено: 07.07.2024
Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек - по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками - например после копирования результатов сводных таблиц или выгрузок в Excel из внешних программ. Таким образом, возникает необходимость заполнить пустые ячейки таблицы значениями из верхних ячеек, то бишь.
| из | сделать |
В общем случае, может возникнуть необходимость делать такое заполнение не только вниз, но и вверх, вправо и т.д. Давайте рассмотрим несколько способов реализовать такое.
Способ 1. Без макросов
Выделяем диапазон ячеек в первом столбце, который надо заполнить (в нашем примере, это A1:A12).
Нажимаем клавишу F5 и затем кнопку Выделить (Special) и в появившемся окне выбираем Выделить пустые ячейки (Blanks) :
Не снимая выделения, вводим в первую ячейку знак "равно" и щелкаем по предыдущей ячейке или жмём стрелку вверх (т.е. создаем ссылку на предыдущую ячейку, другими словами):
И, наконец, чтобы ввести эту формулу сразу во все выделенные (пустые) ячейки нажимаем Ctrl + Enter вместо обычного Enter . И все! Просто и красиво.
В качестве завершающего мазка я советовал бы заменить все созданные формулы на значения, ибо при сортировке или добавлении/удалении строк корректность формул может быть нарушена. Выделите все ячейки в первом столбце, скопируйте и тут же вставьте обратно с помощью Специальной вставки (Paste Special) в контекстом меню, выбрав параметр Значения (Values) . Так будет совсем хорошо.
Способ 2. Заполнение пустых ячеек макросом
Если подобную операцию вам приходится делать часто, то имеем смысл сделать для неё отдельный макрос, чтобы не повторять всю вышеперечисленную цепочку действий вручную. Для этого жмём Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , чтобы открыть редактор VBA, затем вставляем туда новый пустой модуль через меню Insert - Module и копируем или вводим туда вот такой короткий код:
Как легко можно сообразить, этот макрос проходит в цикле по всем выделенным ячейкам и, если они не пустые, заполняет их значениями из предыдущей ячейки.
Для удобства, можно назначить этому макросу сочетание клавиш или даже поместить его в Личную Книгу Макросов (Personal Macro Workbook), чтобы этот макрос был доступен при работе в любом вашем файле Excel.
Способ 3. Power Query
Power Query - это очень мощная бесплатная надстройка для Excel от Microsoft, которая может делать с данными почти всё, что угодно - в том числе, легко может решить и нашу задачу по заполнению пустых ячеек в таблице. У этого способа два основных преимущества:
- Если данных много, то ручной способ с формулами или макросы могут заметно тормозить. Power Query сделает всё гораздо шустрее.
- При изменении исходных данных достаточно будет просто обновить запрос Power Query. В случае использования первых двух способов - всё делать заново.
Для загрузки нашего диапазона с данными в Power Query ему нужно либо дать имя (через вкладку Формулы - Диспетчер имен), либо превратить в "умную" таблицу командой Главная - Форматировать как таблицу (Home - Format as Table ) или сочетанием клавиш Ctrl + T :
После этого на вкладке Данные (Data) нажмем на кнопку Из таблицы / диапазона (From Table/Range) . Если у вас Excel 2010-2013 и Power Query установлена как отдельная надстройка, то вкладка будет называться, соответственно, Power Query.
В открывшемся редакторе запросов выделим столбец (или несколько столбцов, удерживая Ctrl ) и на вкладке Преобразование выберем команду Заполнить - Заполнить вниз (Transform - Fill - Fill Down) :
Вот и всё :) Осталось готовую таблицу выгрузить обратно на лист Excel командой Главная - Закрыть и загрузить - Закрыть и загрузить в. (Home - Close&Load - Close&Load to. )
В дальнейшем, при изменении исходной таблицы, можно просто обновлять запрос правой кнопкой мыши или на вкладке Данные - Обновить всё (Data - Refresh All) .
В Excel функция Fill Handle может помочь вам заполнить непрерывные числа с определенным приращением, но, если вы хотите создать номер приращения с текстом в Excel, как показано на скриншоте ниже, ручка Fill не будет работать. В этом руководстве мы покажем вам два метода создания числа приращения с текстом в Excel.
Приращение числа с текстом по формуле в Excel
Вы можете создать номер приращения с текстом по формуле в Excel, пожалуйста, сделайте следующее.
1. Создайте рабочий лист с содержимым, как показано на скриншоте ниже:
A. В ячейке A2 введите текстовую строку перед числами. B: Столбец B - это столбец числа приращения, в ячейке B2 введите первое число приращения, а затем заполните ячейки ниже нужным числом приращения.
2. В ячейке D2 введите в нее приведенную ниже формулу и нажмите Enter ключ. Выберите ячейку результата, а затем перетащите маркер заполнения вниз к ячейкам, в которых вы хотите получить номер приращения с текстом. Смотрите скриншот:
Заметки:
1. В формуле « 000 ”- это количество цифр, которое вы хотите вставить между текстами. 2. В формуле текст, на который ссылаются ячейки, должен быть абсолютным.Теперь вы получаете числа приращения с текстом или внутри текста.
Увеличить число с текстом с помощью Kutools for Excel
После установки Kutools for Excel, вы можете легко создать число приращения с текстом в Excel. Пожалуйста, сделайте следующее.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Нажмите Kutools > Вставить > Вставить порядковый номер…. Смотрите скриншот:
2. в Вставить порядковый номер диалогового окна, нажмите НОВИНКИ кнопку, чтобы развернуть диалоговое окно, то вам необходимо:
A: Введите имя для этой последовательности в поле Имя пунктом. B: Введите начальный номер в Стартовый номер пунктом. C: В инкремент в поле введите число приращения. В этом случае номер приращения равен 1. D: введите количество цифр в Кол-во цифр коробка. Здесь вводим цифру 3. E: введите текст префикса и текст суффикса в поле Префикс и Суффикс коробках. F. Щелкните значок Добавить кнопка. Смотрите скриншот:
4. Теперь последовательность создана, выберите ячейки, которые вы хотите заполнить текстом приращения числа, а затем щелкните значок Диапазон заполнения кнопка. Вы можете видеть, что числа приращения с текстом сразу же заполняются в выбранных ячейках. Смотрите скриншот:
Внимание: В следующий раз, когда вы примените функцию «Вставить порядковый номер» с этой созданной последовательностью, она сгенерирует порядковые номера, за которыми следует последний номер, который вы вставили в прошлый раз.
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Хотя инструмент Мгновенное заполнение (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 будет воспринимать значения начинающиеся со знака "+" как формулы.
Преобразование текста (чисел) в дату
Изменение регистра
Если вам достался текст с нЕпрАвИльНЫм рЕгисТроМ, то можно просто намекнуть в соседнем столбце к какому виду вы хотите его преобразовать - и мгновенное заполнение сделает за вас всю работу:
Чуть сложнее будет, если нужно изменить регистр по разному у разных частей текста. Например, сделать заглавными только второе слово, оставив первое в нормальном виде. Здесь двух введённых в качестве образца значений окажется недостаточно и придется внести правки, которые мгновенное заполнение тут же учтёт в результатах:
Ограничения и нюансы
При использовании в работе мгновенного заполнения следует помнить несколько моментов:
В нашей повседневной работе с Excel довольно часто автоматическое заполнение увеличивающихся ячеек, например, автоматическое заполнение ячеек на 1, 2, 3…. Но в некоторых случаях вам нужно заполнить ячейки приращения с фиксированным интервалом, например, 10001, 10012, 10023…, как вы можете быстро автоматически заполнить эти ячейки приращением? Теперь это руководство может рассказать вам о решениях.
Автоматически заполнять ячейки приращения с помощью Kutools for Excel
Автоматически заполнять увеличивающиеся ячейки с помощью функции автозаполнения
Собственно, в Excel вы можете использовать функцию автозаполнения для быстрого заполнения ячеек приращения.
1. Выберите ячейку, в которую вы хотите поместить свой начальный номер, например, в A1, я набираю 10001, см. Снимок экрана:
2. Затем в ячейке под начальным числом, вот A2, введите второе число, которое хотите.
3. Затем выберите A1 и A2 и перетащите маркер автозаполнения вниз, пока нижние ячейки не заполнятся нужными вам числами приращения. Смотрите скриншоты:
Автоматически заполнять увеличивающие ячейки формулой
Кроме того, вы можете использовать формулу для решения этого автозаполнения.
1. Выберите пустую ячейку и введите в нее начальный номер, а затем в ячейке под начальным номером введите эту формулу. = A1 + 11 (A1 - начальное число, а 11 - интервал, вы можете изменить их по своему усмотрению). Смотрите скриншот:
2. Затем нажмите Enter и перетащите эту формулу вниз к ячейкам, которые вы хотите заполнить числами с фиксированным интервалом.
Автоматически заполнять ячейки приращения с помощью Kutools for Excel
Работы С Нами Kutools for ExcelАвтора Вставить порядковый номер Утилита, вы также можете быстро автоматически заполнять инкрементные числа, и, кроме того, вы можете добавлять префикс или суффикс к каждому номеру.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите список или диапазон, в котором вы хотите заполнить ячейки приращения, затем щелкните Kutools > Вставить > Вставить порядковый номер. Смотрите скриншот:
2. Во всплывающем Вставить порядковый номер диалоговое окно, нажмите НОВИНКИ чтобы добавить новую последовательность в диалог. Смотрите скриншот:
3. В раскрывающемся диалоговом окне выполните следующие операции:
1) Назовите последовательность;
2) Введите начальный номер ваших чисел приращения в Стартовый номер текстовое окно;
3) Введите номер интервала в инкремент текстовое окно;
4) Укажите количество цифр ваших заполненных чисел в Кол-во цифр текстовое окно;
5) Введите префикс или суффикс в поле Префикс (необязательно) or Суффикс (необязательно) текстовое поле, как вам нужно.
4. Нажмите Добавить чтобы добавить этот новый инкрементный порядковый номер в диалоговое окно, а затем щелкните Диапазон заполнения. Смотрите скриншот:
Теперь вы можете видеть, что выбранные ячейки заполняются номером приращения.
Примечание:
1. Если вы хотите сбросить эту инкрементную последовательность и снова вставить ее с начального номера, вы можете перейти к Вставить порядковый номер диалоговое окно, выберите эту последовательность и нажмите Сбросить > Ok.
2. Если вы хотите закончить порядковый номер конкретным номером, затем повторите вставку этой последовательности с начального номера, отметьте Конечный номер в диалоговом окне и введите конечный номер, который вы хотите, в текстовое поле ниже, и вы можете предварительно просмотреть, что числа заканчиваются этим номером, а затем перезапустить последовательность.
Читайте также: