Как сделать автозаполнение в Excel: подробная инструкция

Как мгновенно заполнить ячейки в Microsoft Excel

Для начала Excel нужно знать закономерность, исходя из которой он будет сразу заполнять ячейки. Например, у вас есть список месяцев с полными названиями, но вы хотите сократить их до трех букв. Затем в ячейке напротив следует написать «Ян» и активировать мгновенное заполнение всей таблицы. Программа сразу поймет, как именно вы хотите сократить написание, поэтому также оставит только первые три буквы для всех остальных строк.

Более сложный вариант — просто использовать аббревиатуры фамилии и отчества, о чем уже говорилось во вступительном абзаце. Позвольте мне рассмотреть его более подробно, чтобы вы поняли, как работает инструмент.

1. откройте таблицу и начните с первой строки, введите аббревиатуру, которая должна отображаться в остальной части списка. Обратите внимание на следующий скриншот, чтобы понять, какой путь я выбрал в своем примере
2. .
3. Перейдите на следующую строку и введите первую букву. Автозаполнение, включенное по умолчанию в Excel, должно предлагать варианты сразу для всех ячеек в таблице. Если это не так, оставайтесь на первой строке аббревиатуры и нажмите Ctrl + E, чтобы использовать опцию быстрой заливки.
4. Посмотрите на результат и убедитесь, что вы смогли справиться с поставленной задачей. Не стесняйтесь экспериментировать с различными сокращениями, чтобы протестировать инструмент более подробно
5. .
6. В блоке с мгновенным заполнением появится кнопка, отвечающая за открытие панели дополнительных действий. Это позволяет отменить заполнение, принять запросы на ввод данных или выделить все измененные ячейки
7. .

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

1. Создайте первую строку с сокращенным вариантом и перейдите на вкладку «Данные».
2. Нажмите на кнопку с надписью «Мгновенное заполнение»
3. .
4. Обратите внимание, что инструмент вступил в силу, таблица заполнена, и вы сэкономили значительное количество времени.

Читайте также: Как посчитать количество заполненных ячеек в Экселе

Принцип работы автозаполнения

Для выполнения автозаполнения в Excel необходимо использовать так называемый маркер заполнения. Делается это достаточно просто:

1. Наведите курсор на правый нижний угол ячейки, из которой мы хотим продублировать информацию.
2. Должен появиться черный знак плюса, который является маркером заполнения. Удерживая левую кнопку мыши, тянем вниз/вверх/вправо/влево — в зависимости от нужного нам направления (в нашем случае — вниз).
3. Остановившись на ячейке, в которую мы хотим выполнить автозаполнение, отпустите кнопку.

Примечание: заполнение ячеек маркером зависит от того, какие данные есть в источнике. В нашем случае это текстовое значение, которое было просто скопировано.

Как включить / выключить функцию автозаполнения Excel

Параметр «Автозаполнение» в Microsoft Excel автоматически заполняет данные по мере их ввода. Вы можете отключить или включить автозаполнение в любое время.

Когда следует и не следует использовать автозаполнение?

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

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

Например, если вы набрали «Иван Петров» в первой ячейке, а затем многие другие в следующих, например, «Света» и «Степан», вы можете снова набрать «Иван Петров» намного быстрее, просто набрав «И» и затем нажмите Enter, чтобы Excel автоматически ввел полное имя.

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

Включить/отключить автозаполнение в Excel

Действия по включению или отключению автозаполнения в Microsoft Excel различаются в зависимости от используемой версии:

В EXCEL 2019, 2016, 2013 и 2010 гг

1. Перейдите в «Файл»> «Параметры».
2. В окне «Параметры Excel» откройте «Дополнительно» слева.
3. Включить или отключить Включите параметр автозаполнения для значений ячеек в разделе «Параметры редактирования», в зависимости от того, хотите ли вы включить или отключить эту функцию.
4. Нажмите OK, чтобы сохранить изменения и продолжить работу с Excel.

В EXCEL 2007

1. Нажмите кнопку «Офис .
2. Выберите Параметры Excel, чтобы открыть диалоговое окно Параметры Excel».
3. Выберите «Дополнительно» на панели слева.
4. Установите флажок рядом с Включить автозаполнение для значений ячеек, чтобы включить или отключить эту функцию.
5. Нажмите OK, чтобы закрыть диалоговое окно и вернуться к электронной таблице.

В EXCEL 2003

1. Перейдите в Инструменты > Параметры в строке меню, чтобы открыть диалоговое окно Параметры .
2. Выберите вкладку «Правка ».
3. Включите/отключите автозаполнение с помощью флажка рядом с параметром Включить автозаполнение для значений ячеек.
4. Нажмите OK, чтобы сохранить изменения и вернуться к электронной таблице.

Работа автозаполнения в Эксель

Автозаполнение в Microsoft Excel выполняется с помощью специального дескриптора заполнения. Чтобы вызвать этот инструмент, наведите указатель мыши на правый нижний край ячейки. Появится маленький черный крестик. Это маркер заполнения. Вам просто нужно зажать левую кнопку мыши и перетащить в ту сторону листа, где вы хотите заполнить ячейки.

Способ заполнения ячеек уже зависит от типа данных, находящихся в исходной ячейке. Например, если это обычный текст в виде слов, он будет скопирован в другие ячейки листа при перетаскивании маркера заполнения.

Автозаполнение ячеек числами

Чаще всего автозаполнение используется для ввода большого количества чисел, следующих по порядку. Например, некая ячейка содержит число 1, и нам нужно пронумеровать ячейки от 1 до 100.

1. Активируйте маркер заполнения и перетащите его вниз на нужное количество ячеек.
2. Но, как видите, во все ячейки скопировался только один. Нажимаем на иконку, которая находится внизу слева от заполненной области и называется «Параметры автозаполнения».
3. В открывшемся списке установите переключатель в положение «Заполнить».

Как видите, вся необходимая область после этого была заполнена цифрами по порядку.

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

Существует также способ автоматического завершения серии прогрессии.

1. Вводим первые два числа прогрессии в соседние клетки.
2. Мы разделяем их. Используя маркер заполнения, мы вводим данные в другие ячейки.
3. Как видите, последовательный ряд чисел создается с заданным шагом.

Инструмент «Заполнить»

В Excel также есть отдельный инструмент под названием «Заливка». Он находится на ленте во вкладке «Главная» панели инструментов «Редактирование».

1. Вводим данные в любую ячейку, затем выбираем ее и диапазон ячеек, которые хотим заполнить.
2. Нажмите кнопку «Заполнить». В появившемся списке выберите направление заполнения ячеек.
3. Как видите, после этих действий данные из одной ячейки скопировались во все остальные.

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

1. Вводим число в ячейку и выбираем диапазон ячеек для заполнения данными. Нажмите на кнопку «Заполнить», и в появившемся списке выберите пункт «Прогресс».
2. Откроется окно настроек прогрессии. Здесь нужно выполнить ряд манипуляций:
   • выбрать расположение прогрессии (по столбцам или строкам);
   • тип (геометрический, арифметический, даты, автозаполнение);
   • установить шаг (по умолчанию 1);
   • введите предельное значение (необязательный параметр).

   Кроме того, в некоторых случаях устанавливаются единицы измерения.

   Когда все настройки сделаны, нажмите кнопку «ОК».

   

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

Автозаполнение формулами

Формулы — один из основных инструментов Excel. Если в таблице большое количество одинаковых формул, вы также можете воспользоваться функцией автозаполнения. Суть не меняется. Вы должны скопировать формулу в другие ячейки так же, как и маркер заполнения. При этом, если в формуле есть ссылки на другие ячейки, их координаты по умолчанию изменятся по принципу относительности при копировании таким способом. Поэтому такие ссылки называются относительными.

Если вы хотите, чтобы адреса фиксировались при автодополнении, необходимо поставить знак доллара перед координатами строк и столбцов исходной ячейки. Такие ссылки называются абсолютными. Затем выполняется обычная процедура автодополнения с помощью курсора заполнения. Во всех ячейках, заполненных таким образом, формула будет совершенно неизменной.

Автозаполнение другими значениями

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

Точно так же вы можете автозаполнять по дням недели (понедельник, вторник, среда…) или по месяцам (январь, февраль, март…).

Кроме того, если в тексте есть какое-либо число, Excel распознает его. При использовании маркера заполнения текст будет скопирован с изменением номера в порядке возрастания. Например, если написать в одной ячейке выражение «4 дела», а в других ячейках заполнить маркером заливки, это имя будет преобразовано в «5 дел», «6 дел», «7 дел» и т.д.

Добавление собственных списков

Функциональность автозаполнения Excel не ограничивается определенными алгоритмами или предопределенными списками, такими как дни недели. При желании пользователь может добавить в программу свой личный список. Тогда при вводе в ячейку любого слова из пунктов, которые есть в списке, после использования курсора заполнения весь выделенный диапазон ячеек будет заполнен этим списком. Для добавления в свой список необходимо выполнить следующую последовательность действий.

1. Перейдем на вкладку «Файл».
2. Перейдите в раздел «Настройки».
3. Затем перейдите в подраздел «Дополнительно».
4. В блоке настроек «Общие» в центральной части окна нажмите кнопку «Изменить списки…».
5. Откроется окно списка. Слева от него уже существующие списки. Чтобы добавить новый список, запишите необходимые слова в поле «Элементы списка». Каждый элемент должен начинаться с новой строки. После того, как все слова будут написаны, нажмите кнопку «Добавить».
6. После этого окно списка закроется, а когда оно снова откроется, пользователь сможет увидеть уже добавленные им элементы в активное окно списка.
7. Теперь, после того как вы наберете слово в любую ячейку листа, которая была одним из пунктов добавленного списка, и воспользуетесь курсором заполнения, выделенные ячейки будут заполнены символами из соответствующего списка.

Как видите, Автозаполнение в Excel — очень полезный и удобный инструмент, который позволяет значительно сэкономить время, добавляя одни и те же данные, повторяющиеся списки и т д. Преимущество этого инструмента в том, что он настраиваемый. Вы можете добавлять новые списки или изменять старые. Кроме того, с помощью автозаполнения вы можете быстро заполнять ячейки различными типами математических прогрессий.

Автозаполнение в Excel из списка данных

Понятно, что, кроме дней недели и месяцев, может возникнуть потребность в других списках. Предположим, вам часто требуется ввести список городов, в которых расположены сервисные центры компании: Минск, Гомель, Брест, Гродно, Витебск, Могилев, Москва, Санкт-Петербург, Воронеж, Ростов-на-Дону, Смоленск, Белгород. Во-первых, вам нужно создать и сохранить (в правильном порядке) полный список заголовков. Перейдите в «Файл» — «Параметры» — «Дополнительно» — «Общие» — «Редактировать списки.

В следующем открывшемся окне видны списки, существующие по умолчанию.

Как видите, их не так много. Но легко добавить свой собственный. Вы можете использовать окно справа, где либо через запятую, либо через столбцы показан желаемый порядок. Однако импортировать будет быстрее, особенно если данных много. Для этого сначала создайте список имен где-нибудь на листе Excel, затем создайте ссылку на него и нажмите «Импорт.

Нажмите «ОК». Список создан, вы можете использовать его для автозаполнения.

Помимо текстовых списков, чаще создаются последовательности чисел и дат. Один из вариантов рассматривался в начале статьи, но он примитивен. Есть несколько интересных трюков. Сначала нужно выбрать одно или несколько первых значений в ряду, а также диапазон (вправо или вниз), где будет разворачиваться последовательность значений. Затем вызываем диалоговое окно прогрессии: Main — Fill — Progression.

Смотрим настройки.

В левой части окна с помощью переключателя задается направление построения последовательности: вниз (по строкам) или вправо (по столбцам).

Посередине выберите нужный тип:

• арифметическая прогрессия — каждое последующее значение изменяется на число, введенное в поле шага
• геометрическая прогрессия — каждое последующее значение умножается на число, введенное в поле шага
• даты — создает последовательность дат. При выборе этого типа активируются переключатели справа, где можно выбрать тип единицы измерения. Есть 4 варианта:
• день — список дат календаря (со следующими шагами)
• рабочий день — последовательность рабочих дней (выходные пропускаются)
• месяц — меняются только месяцы (число фиксированное, как и в первой ячейке)
• год — меняется только год

автозаполнение — эта команда эквивалентна перетаскиванию левой кнопкой мыши. То есть Excel сам решает: продолжать ли ему последовательность чисел, или расширять список. Если две ячейки предварительно заполнены значениями 2 и 4, в других выбранных ячейках появятся 6, 8, и так далее прогноз по прямой линии тренда (интересная особенность — подробнее см ниже).

Нижняя часть окна Progression используется для создания последовательности любой длины на основе конечного значения и шага. Например, вам нужно заполнить столбец последовательностью четных чисел от 2 до 1000. Перетаскивание мышью нецелесообразно. Таким образом, вы должны сначала выбрать только ячейку с первым значением. Затем установите положение, шаг и предельное значение в окне Progression.

В результате получится заполненный столбец от 2 до 1000. Таким же образом можно создать последовательность рабочих дней на год вперед (в качестве предельного значения необходимо ввести последнюю дату, например 31.12.2016). Возможность заполнять столбец (или строку) последним значением — очень полезная вещь, т.к избавляет от кучи лишних действий при растягивании. На этом настройка автозаполнения завершена. Мы идем дальше.Автозаполнение чисел с помощью мышиАвтодополнение в Excel удобнее делать мышкой, у которой есть правая и левая кнопка. Оба будут необходимы.

Допустим, вам нужно составить порядковые номера цифр, начиная с 1. Обычно две ячейки заполняются цифрами 1 и 2, а затем левой кнопкой мыши рисуется арифметическая прогрессия. Вы можете сделать это по-другому. Просто заполняем ячейку 1. Растягиваем ее и получаем столбец единиц. Далее откройте появившееся сразу после перетаскивания поле в правом нижнем углу и выберите «Заливка.
Если вы выберете Только форматы заливки, будут расширены только форматы ячеек.
Вы можете создать числовую последовательность еще быстрее. Удерживая нажатой клавишу Ctrl, перетаскивайте ячейку.

Этот трюк работает только с последовательностями чисел. В других ситуациях удержание Ctrl вызовет копирование данных вместо автозаполнения.

Если вы используете правую кнопку мыши при перетаскивании, контекстное меню открывается сразу после того, как вы отпустите кнопку.

Это добавляет несколько команд. Прогресс позволяет использовать дополнительные операции автозаполнения (см настройки выше). Правда, выбирается диапазон и длина последовательности будет ограничена последней ячейкой.

Для автодополнения до нужного предельного значения (числа или даты) можно проделать следующий трюк. Берем курсор правой кнопкой мыши, тащим немного вниз, тут же возвращаем обратно и отпускаем кнопку — открывается контекстное меню автодополнения. Выберите прогрессию. На этот раз выбрана только одна ячейка, поэтому мы указываем направление, шаг, предельное значение и создаем нужную последовательность.

Пункты меню Linear and Exponential Approximation очень интересны. Это экстраполяция, т.е прогнозные данные по заданной модели (линейной или экспоненциальной). Обычно для прогноза используются специальные функции Excel, либо предварительно рассчитывается уравнение тренда (регрессии), в которое подставляются значения независимой переменной для будущих периодов и таким образом рассчитывается прогнозное значение. Это делается так. Предположим, что в индикаторе есть динамика с устойчивым ростом.

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

Чтобы получить прогноз в числовом выражении, нужно провести расчет на основе полученного уравнения регрессии (или напрямую обратиться к формулам Excel). Это оказывается довольно много действий, которые требуют хорошего понимания.

Так что прогноз по методу линейной регрессии можно сделать без формул и вообще без графиков, только с помощью автозаполнения ячеек в Excel. Для этого выделите данные, на которых основан прогноз, перетащите правую кнопку мыши на необходимое количество ячеек, соответствующее длине прогноза, и выберите пункт Линейная аппроксимация. Получаем прогноз. Без шума, пыли, формул и схем.

Если данные имеют ускоренный рост (например, депозитный счет), можно использовать экспоненциальную модель. Опять же, чтобы не мучиться с вычислениями, можно воспользоваться автодополнением, выбрав Экспоненциальное приближение.

Вы не можете придумать более быстрый способ предсказания.

Автозаполнение дат с помощью мыши

Довольно часто хочется расширить список дат. Берем дату и перетаскиваем ее левой кнопкой мыши. Откройте коробку и выберите способ наполнения.

В будние дни — отличный вариант для бухгалтеров, HR и других специалистов, занимающихся составлением различных планов. А вот еще один пример. Допустим, запланированные платежи приходят 15 числа и в последний день каждого месяца. Введите первые две даты, перетащите вниз и заполните по месяцам (любая кнопка мыши).

Обратите внимание, что 15-е число фиксировано, а последний день месяца всегда будет последним.

Используя правую кнопку мыши, вы можете использовать настройки прогрессии. Например, составить список рабочих дней до конца года. В списке команд через правую кнопку есть и Instant Fill. Эта функция появилась в Excel 2013. Она используется для заполнения ячеек по образцу. Но об этом уже была статья, рекомендую прочитать. Это также сэкономит вам часы работы.

На этом, пожалуй, все. В этом видеоуроке показано, как автоматически заполнять ячейки в Excel.

Создаем собственные списки

Если требуемый вариант автозаполнения недоступен в Excel, вы можете создать его вручную. В этом случае мы руководствуемся следующим планом действий:

1. откройте меню «Файл”.
2. Выберите «Настройки» из списка слева”.
3. В открывшихся опциях перейдите в подраздел «Дополнительно». Листаем содержимое в правой части окна, находим группу «Общие», где нажимаем на кнопку «Изменить списки…”.
4. На экране появится окно со стандартными списками. По умолчанию должен быть выбран пункт «НОВЫЙ СПИСОК» (если его нет, проверьте вручную). Для создания своего кликаем в поле «Элементы списка», после чего добавляем нужные значения по порядку, каждое из которых должно начинаться с новой строки. Когда все будет готово, нажмите «Добавить”.
5. Созданный список появится в списке слева. Теперь окно можно закрыть. Мы также заканчиваем параметры программы.
6. Пишем в свободную ячейку любое слово из созданного списка, после чего можно использовать маркер заполнения.
   Получаем массив ячеек, заполненных расположением значений в нашем списке. Причем делается это циклически, т.е после последнего слова снова начинается первое — и так до последнего элемента в выделенной области.

Включение или отключение параметров автозаполнения

Если вы не хотите, чтобы кнопка «Параметры автозаполнения» появлялась каждый раз, когда вы перетаскиваете маркер заполнения, просто отключите ее. Точно так же, если кнопка не видна при использовании маркера заполнения, вы можете включить ее.

1. Перейдите в меню «Файл/Офис» -> «Параметры» -> «Дополнительно» и найдите раздел «Вырезать, копировать и вставить.
2. Снимите флажок «Показывать кнопки параметров вставки» при вставке содержимого.

Решение некоторых проблем

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

1. Нажмите на вкладку «Файл»
2. .
3. откройте список опций программы, нажав на строку с соответствующим названием.
4. Выберите раздел «Дополнительно» и убедитесь, что во вкладке «Параметры редактирования» установлены галочки рядом с «Автозаполнение значений ячеек» и «Автоматическое завершение Flash»
5. .

Если вы не нашли элемент с мгновенной заливкой, значит, вы используете старую версию Microsoft Excel и этот инструмент просто недоступен. Установите обновление, если оно доступно.

Другая проблема заключается в том, что программа не может понять, как именно вы хотите использовать Flash Fill. В этом случае на экране будет отображаться ошибка с соответствующим содержимым. Не закрывайте его сразу, а читайте описание и делайте все, что пишут разработчики.

Используйте мгновенное заполнение в Microsoft Excel в своих интересах, оптимизируйте и значительно увеличьте рабочий процесс. Если вам не нужны исходные данные после сокращения, удалите их, чтобы сэкономить место в электронной таблице.

Что делать, если нет маркера автозаполнения?

Если маркер заполнения (курсор) отсутствует, необходимо настроить Excel так, чтобы маркер появился.

Для этого, если вы используете версию 2003, выберите Сервис — Параметры — на вкладке Параметры установите флажок Перетаскивание ячеек.

Если вы используете версию 2007 или 2010, выберите «Файл» (кнопка «Офис») — «Параметры» — «Дополнительно» — «Разрешить заполнение ячеек и перетаскивание маркеров» — «ОК.

Ввод данных экспресс-методом

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

Используйте клавишу Ctrl, чтобы выбрать ячейки, в которые вы хотите ввести одинаковые значения.

В строке формул введите нужное значение и нажмите комбинацию клавиш Ctrl+Enter на клавиатуре. Все выбранные ячейки автоматически заполняются необходимыми данными.

Полуавтоматическое заполнение ячеек в Excel из выпадающего списка

Теперь в полуавтоматическом режиме вы можете заполнять пустые ячейки. Имеет всего несколько значений, которые повторяются в последовательном или случайном порядке.

В новой исходной таблице вы автоматически заполняете столбцы C и D соответствующими данными.

1. Заполните заголовки столбцов С1 – «Дата» и D1 – «Вид платежа».
2. В ячейку C2 введите дату 18.07.2015
3. В ячейках C2:C4 даты повторяются. Поэтому выделите диапазон C2:C4 и нажмите комбинацию клавиш CTRL+D, чтобы автоматически заполнить ячейки предыдущими значениями.
4. Введите текущую дату в ячейку C5. Для этого нажмите комбинацию клавиш CTRL+; (точка с запятой на английской раскладке клавиатуры). Заполните столбец C текущими датами до конца таблицы.
5. Заполните диапазон ячеек D2:D4, как показано на рисунке ниже.
6. В ячейку D5 введите первую букву «р», после чего слово заполнять не нужно. Просто нажмите клавишу Enter.
7. В ячейке D6 после ввода первой буквы «н» не отображается автозаполнение части слова. Поэтому нажмите комбинацию ALT + (стрелка вниз), чтобы вызвать выпадающий список. С помощью стрелок клавиатуры или указателя мыши выберите значение «наличные в кассе» и нажмите Enter.

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

Обратите внимание на следующее! Если значение состоит из нескольких строк, оно не появится в выпадающем списке значений при нажатии комбинации ALT+(стрелка вниз.

Вы можете разбить значение на строки, используя комбинацию клавиш ALT + Enter. Таким образом, текст разбивается на строки в пределах одной ячейки.

Примечание. Обратите внимание, как мы ввели текущую дату в разделе 4, используя сочетания клавиш (CTRL+;). Это очень удобно! И когда вы нажимаете CTRL+SHIFT+; получаем текущее время.