Заполнение данных в столбце или строке
Если вам нужно продублировать запись в нескольких смежных ячейках или создать таблицу, в первой строке на которую перечислены месяцы года, можно заполнить данные и избежать повторного ввода.
Чтобы заполнить ячейки в Excel Mobile для Windows 10, Excel для планшетов или телефонов с Android или Excel для iPad или iPhone, сначала коснитесь ячейки, строки или столбца, которые нужно заполнить другими ячейками. Затем снова коснитесь его, нажмите «Заливка» и перетащите зеленый кнопку заполнения к ячейкам, которые вы хотите заполнить.
Вот несколько дополнительных сведений о том, как это сделать.
Заполнение смежных ячеек одинаковыми данными
- Касанием выберите ячейку, содержащую данные, которыми нужно заполнить остальные ячейки, а затем снова коснитесь ее, чтобы открыть меню Изменить.
Выберите элемент Заливка, а затем коснитесь стрелок заполнения и перетащите их вниз или вправо.
Заполнение смежных ячеек рядами дат, чисел или другими встроенными рядами данных
- Введите начальное значение ряда данных.
- Введите значение в следующую нижнюю или правую ячейку, чтобы создать шаблон.
- Нажмите, чтобы выбрать первую ячейку, а затем перетащите маркер выделения по второму значению.
В меню Правка выберите пункт Заливка, а затем коснитесь и перетащите стрелку заполнения вниз.
Автоматическое заполнение ячеек листа данными
Функция автозаполнения позволяет заполнять ячейки данными на основе шаблона или данных в других ячейках.
Примечание: В этой статье объясняется, как автоматически заполнить значения в других ячейках. Она не содержит сведения о вводе данных вручную или одновременном заполнении нескольких листов.
Другие видеоуроки от Майка Гирвина в YouTube на канале excelisfun
- Выделите одну или несколько ячеек, которые необходимо использовать в качестве основы для заполнения других ячеек. Например, если требуется задать последовательность 1, 2, 3, 4, 5. введите в первые две ячейки значения 1 и 2. Если необходима последовательность 2, 4, 6, 8. введите 2 и 4. Если необходима последовательность 2, 2, 2, 2. введите значение 2 только в первую ячейку.
- Перетащите маркер заполнения
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Суперсила Мгновенного заполнения (Flash Fill)
Эксперименты делают из нас героев.
(Флэш) Хотя инструмент Мгновенное заполнение (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 будет воспринимать значения начинающиеся со знака «+» как формулы.
Преобразование текста (чисел) в дату
При выгрузках из различных ERP и CRM-систем часто дата представляется 8-значным числом в формате ГГГГММДД. Конвертировать её в нормальный вид можно либо функцией ДАТАЗНАЧ (DATEVALUE) , либо гораздо проще — мгновенным заполнением:
Изменение регистра
Если вам достался текст с нЕпрАвИльНЫм рЕгисТроМ, то можно просто намекнуть в соседнем столбце к какому виду вы хотите его преобразовать — и мгновенное заполнение сделает за вас всю работу: Чуть сложнее будет, если нужно изменить регистр по разному у разных частей текста. Например, сделать заглавными только второе слово, оставив первое в нормальном виде. Здесь двух введённых в качестве образца значений окажется недостаточно и придется внести правки, которые мгновенное заполнение тут же учтёт в результатах:
Ограничения и нюансы
- Оно срабатывает только, если вводить образцы строго рядом — в предыдущем или следующем столбце справа от данных. Если отступить одну пустую колонку от исходного текста, то ничего не получится.
- При выявлении шаблона учитываются все значения в строке — слева и справа от вводимого столбца. Мораль: лишние колонки, способные запутать алгоритм или внести помехи, лучше заранее отделить от рабочих данных пустыми столбцами или удалить.
- Мгновенное заполнение отлично работает в умных таблицах.
- Малейшая ошибка или опечатка при наборе ячеек-образцов может привести к тому, что мгновенное заполнение не сможет выявить паттерн и не сработает. Будьте внимательны.
- Есть ситуации, когда шаблон определяется некорректно, поэтому всегда нужно проверятьрезультаты, которые вы получили (хотя бы выборочно).
Ссылки по теме
- Как извлечь последнее слово из текста в ячейке
- Нечёткий текстовый поиск (Пушкин = Пушкен) с помощью Fuzzy Lookup в Excel
- Три способа склеить текст в Excel
Excel: Автозаполнение (часть 1)
Иногда в Excel приходится вводить однотипные данные в определенной последовательности, например дни недели, даты или порядковые номера строк. Для автоматизации таких процессов разработчики внедрили в программу очень удобную функцию, которая называется Автозаполнение ячеек.
Автозаполнение смежных ячеек
В правом нижнем углу текущей ячейки имеется черный квадратик – маркер заполнения.
При наведении указателя мыши на этот маркер, он приобретает форму тонкого черного крестика.
Для проведения Автозаполнения:
- введите значение в ячейку
- снова сделать ячейку активной
- наведите курсор на маркер заполнения, при появлении черного крестика нажмите на него левой или правой кнопкой мыши и, не отпуская кнопку, «протяните» маркер в нужном направлении по горизонтали или по вертикали
- список заполнится автоматически, после того как кнопка будет отпущена.
Автозаполнение используют в разных целях:
- Копирование данных в другие ячейки (числа, текст, формулы);
- Создание арифметических и геометрических прогрессий;
- Создание различных последовательностей (названия месяцев, дни недели, даты, время);
- Создание пользовательских последовательностей;
- Копирование оформления ячеек.
Примеры автозаполнения :
Ряды, полученные с помощью автозаполнения
Автозаполнение смежных ячеек числами
1. Протягивание левой кнопкой мыши маркера ячейки, содержащей число, скопирует это число в последующие ячейки. Если при протягивании маркера удерживать клавишу , то ячейки будут заполнены последовательными числами
При протягивании вправо или вниз числовое значение увеличивается, при протягивании влево или вверх – уменьшается. По ходу протягивания на экране появляется всплывающая подсказка, которая показывает то значение, которое программа собирается вставить в следующую ячейку.
Кнопка , которая появляется рядом с последней заполненной ячейкой, называется смарт-тегом автозаполнения . Щелкнув по этой кнопке, можно выбрать тип автозаполнения ячеек.
2. При протягивании маркера заполнения правой кнопкой мыши появится контекстное меню, в котором можно выбрать нужную команду:
- Копировать – все ячейки будут содержать одно и то же число;
- Заполнить – ячейки будут содержать последовательные значения (с шагом арифметической прогрессии 1).
Заполнение числами с шагом отличным от 1
- заполнить две соседние ячейки нужными значениями;
- выделить эти ячейки;
- протянуть маркер заполнения.
Таким образом, для сложных последовательностей, перед применением автозаполнения, необходимо самостоятельно заполнить сразу несколько ячеек, чтобы Excel правильно смог определить общий алгоритм вычисления их значений.
- ввести начальное значение в первую ячейку;
- протянуть маркер заполнения правой кнопкой мыши;
- в контекстном меню выбрать команду Прогрессия ;
- в диалоговом окне выбрать тип прогрессии – арифметическая и установить нужную величину шага (в нашем примере это – 3).
Заполнение датами
При автозаполнении дат можно выбрать: заполнение по дням, месяцам, годам, а также по рабочим дням.