С помощью чего можно заполнить ячейки рядом значений: Автозаполнение ячеек в Excel — statanaliz.info

Автозаполнение ячеек в Excel — statanaliz.info

Автозаполнение ячеек Excel – это автоматический ввод серии данных в некоторый диапазон. Введем в ячейку «Понедельник», затем удерживая левой кнопкой мышки маркер автозаполнения (квадратик в правом нижнем углу), тянем вниз (или в другую сторону). Результатом будет список из дней недели. Можно использовать краткую форму типа Пн, Вт, Ср и т.д. Эксель поймет. Аналогичным образом создается список из названий месяцев.

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

Эксель также умеет распознать числа среди текста. Так, легко создать перечень кварталов. Введем в ячейку «1 квартал» и протянем вниз.

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

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

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

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

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

Жмем ОК. Список создан, можно изпользовать для автозаполнения.

Помимо текстовых списков чаще приходится создавать последовательности чисел и дат. Один из вариантов был рассмотрен в начале статьи, но это примитивно. Есть более интересные приемы. Вначале нужно выделить одно или несколько первых значений серии, а также диапазон (вправо или вниз), куда будет продлена последовательность значений. Далее вызываем диалоговое окно прогрессии: Главная – Заполнить – Прогрессия.

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

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

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

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

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

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

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

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

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

Автозаполнение в Excel удобнее делать мышкой, у которой есть правая и левая кнопка. Понадобятся обе.

Допустим, нужно сделать порядковые номера чисел, начиная с 1. Обычно заполняют две ячейки числами 1 и 2, а далее левой кнопкой мыши протягивают арифметическую прогрессию. Можно сделать по-другому. Заполняем только одну ячейку с 1. Протягиваем ее и получим столбец с единицами. Далее открываем квадратик, который появляется сразу после протягивания в правом нижнем углу и выбираем Заполнить.

Если выбрать Заполнить только форматы, будут продлены только форматы ячеек.
Сделать последовательность чисел можно еще быстрее. Во время протягивания ячейки, удерживаем кнопку Ctrl.

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

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

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

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

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

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

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

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

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

Более быстрого способа прогнозирования, пожалуй, не придумаешь.

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

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

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

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

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

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

Поделиться в социальных сетях:

Заполнение пустых ячеек значениями из соседних ячеек

66535 30.10.2012 Скачать пример

Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек — по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками — например после копирования результатов сводных таблиц или выгрузок в Excel из внешних программ. Таким образом, возникает необходимость заполнить пустые ячейки таблицы значениями из верхних ячеек, то бишь…

из

сделать

В общем случае, может возникнуть необходимость делать такое заполнение не только вниз, но и вверх, вправо и т.д. Давайте рассмотрим несколько способов реализовать такое.

Способ 1. Без макросов

Выделяем диапазон ячеек в первом столбце, который надо заполнить (в нашем примере, это A1:A12).

Нажимаем клавишу F5 и затем кнопку Выделить (Special) и в появившемся окне выбираем Выделить пустые ячейки (Blanks):

Не снимая выделения, вводим в первую ячейку знак «равно» и щелкаем по предыдущей ячейке или жмём стрелку вверх (т. е. создаем ссылку на предыдущую ячейку, другими словами):

И, наконец, чтобы ввести эту формулу сразу во все выделенные (пустые) ячейки нажимаем Ctrl + Enter вместо обычного Enter. И все! Просто и красиво.

В качестве завершающего мазка я советовал бы заменить все созданные формулы на значения, ибо при сортировке или добавлении/удалении строк корректность формул может быть нарушена. Выделите все ячейки в первом столбце, скопируйте и тут же вставьте обратно с помощью Специальной вставки (Paste Special) в контекстом меню, выбрав параметр Значения (Values). Так будет совсем хорошо.

Способ 2. Заполнение пустых ячеек макросом

Если подобную операцию вам приходится делать часто, то имеем смысл сделать для неё отдельный макрос, чтобы не повторять всю вышеперечисленную цепочку действий вручную. Для этого жмём Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer), чтобы открыть редактор VBA, затем вставляем туда новый пустой модуль через меню Insert — Module и копируем или вводим туда вот такой короткий код:

Sub Fill_Blanks()
    For Each cell In Selection
        If IsEmpty(cell) Then cell. Value = cell.Offset(-1, 0).Value
    Next cell
End Sub

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

Для удобства, можно назначить этому макросу сочетание клавиш или даже поместить его в Личную Книгу Макросов (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).

Ссылки по теме

• Объединение двух столбцов с данными
• Быстрое выделение всех ячеек с формулами или константами
• Быстрое заполнение пустых ячеек макросом из надстройки PLEX
  

 

Как использовать дескриптор заполнения в Excel (Полное руководство)

Главная / Основы Excel / Как использовать дескриптор заполнения в Excel

Используйте маркер заполнения для автоматического заполнения шаблона числового ряда с помощью перетаскивания.

Используйте дескриптор заполнения для автозаполнения шаблона серии чисел с помощью двойного щелчка

Используйте дескриптор заполнения для автозаполнения дат

Параметры маркера заполнения

Дополнительные руководства

В Excel дескриптор заполнения — это инструмент, который автоматически заполняет строки/столбцы следуя шаблону значений выбранных ячеек и создает список рядов.

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

Ручка заполнения может использоваться для автоматического заполнения ячеек на основе значения, которое у вас есть в ячейке (ячейках) в начальных ячейках, из которых вы хотите создать список.

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

Используйте дескриптор заполнения для автозаполнения шаблона числового ряда с помощью перетаскивания.

1. Сначала введите значение в ячейку, с которой вы хотите начать серию, нажмите и удерживайте маркер заполнения и перетащите вверх к ячейке, в которую вы хотите скопировать серию.
2. После этого нажмите и откройте значок «Ручка заполнения» и выберите параметр «Заполнить серию», и ваш список будет преобразован в серию с шагом 1.
   
3.  Или введите значение как минимум в две ячейки, чтобы создать шаблон за которым следует дескриптор заполнения.
4. После этого выберите обе ячейки и поместите курсор мыши в правый нижний угол ячейки, после чего появится значок плюса (+) (Ручка заполнения).
   
5. Теперь нажмите и удерживайте кнопку мыши и перетащите к ячейке, до которой вы хотите автоматически заполнить шаблон.
   

Используйте дескриптор заполнения для автоматического заполнения шаблона числового ряда с помощью двойного щелчка

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

1. Сначала введите значения как минимум в две ячейки, чтобы создать шаблон, за которым следует дескриптор заполнения.
2. После этого выберите обе ячейки и поместите курсор мыши в правый нижний угол ячейки, при этом появится значок плюса (+).
3. Теперь просто дважды щелкните, и ваш шаблон будет скопирован до ячеек, в которых есть данные в соседних ячейках.

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

Используйте дескриптор заполнения для автоматического заполнения дат

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

И когда мы использовали дескриптор заполнения для автозаполнения ячеек и щелкнули значок дескриптора заполнения, он показал нам дополнительные параметры, связанные с датами, такие как «Заполнить дни, Заполнить дни недели, Заполнить месяцы, Заполнить годы».

И вы можете выбрать любой из вариантов в зависимости от ваших требований к списку.

Параметры маркера заполнения

Некоторые параметры значка маркера заполнения, такие как «Копировать ячейки», «Заполнить ряды», «Заполнить только форматирование», «Заполнить без форматирования» и «Быстрое заполнение», остаются неизменными каждый раз, но некоторые параметры могут различаться в зависимости от значений ячеек.

Например, если значением вашей ячейки является название дня, то оно покажет вам 2-3 дополнительных параметра, связанных только с форматированием названия дня. Мы описали некоторые широко используемые параметры дескриптора заполнения ниже:

1. Копировать ячейки: Эта опция просто копирует и вставляет значения выбранных ячеек.
2. Заполнить ряд: Этот параметр заполняет ячейки последовательно с шагом 1.
3. Заполнять только форматирование: Этот параметр заполняет только форматирование ячеек, а не значения.
4. Заполнить без форматирования: Этот параметр заполняет ячейки на основе шаблона значений без копирования форматирования.
5. Заполнить дни: Этот параметр заполняет ячейки серией названий дней.
6. Заполнить дни недели: Эта опция заполняет ячейки названиями дней, кроме выходных (сб и вс).
7. Заполнить месяцы: Этот параметр создает список, увеличивая только месяцы, а дата и год остаются постоянными.
8. Заполнить годы: Этот параметр создает список, увеличивая только годы, а дата и месяц остаются постоянными.

Используйте маркер заполнения в Excel, чтобы сэкономить время и повысить производительность

Обработчик заполнения — это функция Excel, которая позволяет автоматически заполнять список в строке/столбце, просто перетаскивая его с помощью мыши.

Базовое понимание дескриптора заполнения в Excel может сэкономить вам время и повысить производительность.

В этом руководстве рассматриваются:

Что это за дескриптор заполнения в Excel?

«Fill Handle» — это инструмент, который можно использовать для автозаполнения списков в Excel.

Например, если вам нужно ввести числа от 1 до 20 в ячейку A1:A20, вместо того, чтобы вручную вводить каждое число, вы можете просто ввести первые два числа и использовать маркер заполнения, чтобы сделать все остальное.

Как использовать дескриптор заполнения в Excel?

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

Предположим, у вас есть данные, как показано ниже:

Вот шаги по использованию маркера заполнения для быстрой вставки чисел до 20.

• Выберите набор данных.
• Наведите указатель мыши на нижний правый край выделения, появится значок плюса –
• Щелкните левой кнопкой мыши и перетащите ее вниз. Excel идентифицирует шаблон первых двух чисел (приращение 1) и использует его для заполнения всего ряда.

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

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

Примеры использования маркера заполнения в Excel

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

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

Вот встроенные шаблоны маркеров заполнения, которые вы можете использовать:

Автозаполнение чисел, которые увеличиваются/уменьшаются на 1

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

Обратите внимание, что Ручка заполнения работает в обоих направлениях. Вы можете либо заполнить его, перетащив его вниз, либо заполнить, перетащив его вверх.

Автозаполнение названий дней недели

Вы можете использовать дескриптор заполнения в Excel для автозаполнения названий дней недели. Это может быть либо трехбуквенная номенклатура (Пн, Вт…), либо полное название (понедельник, вторник…).

Обратите внимание, что он заполняет ячейки 7 названиями дней недели, а затем начинает с того же имени, с которого вы начали. Например, в приведенном выше примере он начинается с понедельника, а после воскресенья снова автоматически вставляется понедельник.

Даты автозаполнения

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

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

Хотя маркер заполнения распознает шаблоны и заполняет список, он также предоставляет некоторые дополнительные параметры, которые вы можете использовать.

После перетаскивания мышью (или двойного щелчка) и автозаполнения списка вы увидите значок параметра автозаполнения в правом нижнем углу списка.

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

Параметры автозаполнения для списка с номерами

Когда вы используете дескриптор заполнения для автозаполнения списка номеров, вы увидите значок «Параметры автозаполнения» в правом нижнем углу. Нажмите на этот значок, чтобы увидеть дополнительные параметры.

Давайте рассмотрим эти параметры:

• Копировать ячейку : Если вы выберете этот параметр, он просто скопирует и вставит ячейки.
• Заполнение серии : это параметр по умолчанию, при котором серия заполняется на основе распознаваемого шаблона. В приведенном ниже примере он заполняет ячейки числами, увеличивающимися на 1.
• Заполнить только форматирование : Этот параметр заполняет только форматирование, но не значения.
• Заполнить без форматирования : Этот параметр заполняет ячейки на основе распознанного шаблона, но не копирует форматирование. В приведенном ниже примере при выборе этого параметра граница и цвет не применяются к заполненным ячейкам.
• Быстрое заполнение : это новая функция, доступная только в Excel 2013. Он выводит шаблон для завершения списка на основе значений в соседних ячейках.

Параметры автозаполнения для списка с датами

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

Вот дополнительные параметры, которые становятся доступными при работе с датами:

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

Параметры автозаполнения для списка с названиями дней

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

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

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

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

Вот как включить дескриптор заполнения в Excel:

1. Перейти к Файл -> Варианты.
2. Выберите Advanced на левой панели диалогового окна.