Функция автозаполнения в excel. Autofill — автоматическое заполнение форм

Полезнейшим свойством таблиц Excel является автозаполнение ячеек. Т.е. если вам нужно заполнить определенное значение (от 1 до 10), последовательность (к примеру дней) или формулу в большое количество ячеек построчно сделать это можно почти автоматически. Как сделать это быстро и удобно? Читайте далее.

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

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

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

2. Создание собственного списка автозаполнения ячеек

Индивидуально можно создать любую последовательность для заполнения. Нажмите круглую кнопку слева вверху (Файл) – Параметры – Дополнительно – Изменить списки. В появившимся окне можно добавить свой список. Здесь можно создавать как текстовые, числовые, так и смешанные последовательности. После добавления последовательность будет доступна для способа в пункте 1.

3. Как быстро растащить автозаполнение

Если вы записали формулу в ячейку В1 и вам надо продлить ее до конца диапазона (в столбце А у вас уже есть значения, например в ячейках А1:А50). Наведите на нижний правый угол ячейки до появления крестика и теперь кликните два раза левой кнопкой — ваша формула автоматически продлилась до ячейки В50, т.е. до конца диапазона в столбце А. Эта функция действует только если заполнен соседний столбец слева или справа, причем в этих диапазонах нет пустых ячеек (разрывов)! Особенно полезна эта возможность если в вашей таблице тысяча и более строк!

4. Как быстро заполнить формулой ячейки в столбе в 10 тыс строк

Моя любимая хитрость. Предположим, что у нас пример из пункта 3, но необходимо заполнить формулу в столбце В для 50 000 ячеек, при этом ни в левом ни в правом диапазоне нет сплошных данных. Пункт 3 не поможет. Что делать? Хватать крестик и тащить вниз как в пункте 1? Неет, это долго. Сперва копируйте формулу из ячейки В2 (ctrl+c). Затем находите конец диапазона в соседнем столбец (для примера ячейка А50000), либо мышью, либо сочетанием клавиш ctrl + стрелка вниз. Выделяем ячейку в столбце, где нужно заполнить формулу т.е. В50000. Нажимаем ctrl + shift + стрелка вверх — таким образом выделяться все данные в столбце В выше ячейки В50000. Теперь вставляем формулу, которую мы скопировали в самом начале.

5. Сохранить форматы при протягивании формул

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

Автозаполнение ячеек данными в Excel

Для наглядности примера схематически отобразим базу регистрационных данных:

Как описано выше регистр находится на отдельном листе Excel и выглядит следующим образом:

Здесь мы реализуем автозаполнение таблицы Excel. Поэтому обратите внимание, что названия заголовков столбцов в обеих таблицах одинаковые, только перетасованы в разном порядке!

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

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

1. На листе «Регистр» введите в ячейку A2 любой регистрационный номер из столбца E на листе «База данных».
2. Теперь в ячейку B2 на листе «Регистр» введите формулу автозаполнения ячеек в Excel:
3. Скопируйте эту формулу во все остальные ячейки второй строки для столбцов C, D, E на листе «Регистр».

В результате таблица автоматически заполнилась соответствующими значениями ячеек.

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

Главную роль в данной формуле играет функция ИНДЕКС. Ее первый аргумент определяет исходную таблицу, находящуюся в базе данных автомобилей. Второй аргумент – это номер строки, который вычисляется с помощью функции ПОИСПОЗ. Данная функция выполняет поиск в диапазоне E2:E9 (в данном случаи по вертикали) с целью определить позицию (в данном случаи номер строки) в таблице на листе «База данных» для ячейки, которая содержит тоже значение, что введено на листе «Регистр» в A2.

Третий аргумент для функции ИНДЕКС – номер столбца. Он так же вычисляется формулой ПОИСКПОЗ с уже другими ее аргументами. Теперь функция ПОИСКПОЗ должна возвращать номер столбца таблицы с листа «База данных», который содержит название заголовка, соответствующего исходному заголовку столбца листа «Регистр». Он указывается ссылкой в первом аргументе функции ПОИСКПОЗ – B$1. Поэтому на этот раз выполняется поиск значения только по первой строке A$1:E$1 (на этот раз по горизонтали) базы регистрационных данных автомобилей. Определяется номер позиции исходного значения (на этот раз номер столбца исходной таблицы) и возвращается в качестве номера столбца для третьего аргумента функции ИНДЕКС.

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

Модуль поиска не установлен.

Надежда Баловсяк

Те пользователи, кому часто приходится проводить в Интернете много времени, занимаясь, например, регистрацией продуктов, рассылкой материалов или онлайн-покупками, много времени тратят на заполнение различных веб-форм. Логины, пароли, имена, адреса, индекс и многое другое...

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

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

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

IE Scripter

Сайт разработчика: www.iescripter.com
Размер дистрибутива: 1,2 Мб
Статус: Shareware

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

Встретив форму на другом сайте, для того чтобы воспользоваться сохраненными данными, нажмите кнопку Load, и программа автоматически подставит все значения в нужные поля. Правда, надо заметить, что не всегда программа работает корректно, особенно для выпадающих полей. Кроме того, не предусмотрено использование различных данных для одной страницы, открытой в Internet Explorer, например, нельзя применить данные нескольких почтовых ящиков - программа не умеет запоминать несколько наборов значений.

Кроме этого способа заполнения, вы можете сохранить в базе данных IE Scripter стандартный набор значений, который программа будет использовать при заполнении встреченных на веб-страницах форм. Эти параметры следует задать в окне настроек программы. Следует заметить, что набор стандартных параметров недостаточный, и их не всегда хватает для заполнения форм. Эти параметры можно загрузить из набора, сохраненного в настройках Internet Explorer. Кроме того, в программе отсутствует возможность редактирования списка ключевых слов, по которым определяется тип поля в веб-форме.

Программа обладает гибкими настройками. Настройки можно сохранить в специальном файле. Среди дополнительных возможностей программы можно отметить инструмент генерации паролей и просмотр cookies.

iNetFormFiller

Сайт разработчика: www.inetformfiller.com
Размер дистрибутива: 2,8 Мб
Статус: Shareware

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

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

В браузер Internet Explorer после установки программы встраивается дополнительная панель инструментов iNEtFormFiller.

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

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

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

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

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

RoboForm

Сайт разработчика: www.roboform.com
Размер дистрибутива: 1,8 Мб
Статус: Shareware

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

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

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

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

Следует отметить широкие возможности настройки программы. Например, RoboForm позволяет полностью настроить состав контекстного меню Internet Explorer, также доступна настройка комбинаций клавиш, применяемых программой для заполнения форм.

WebM8

Сайт разработчика: www.m8software.com
Размер дистрибутива: 1,59 Мб
Статус: Shareware

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

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

Например, на рис. 35.1 показан ряд последовательных чисел в столбце А. Ячейка А1 содержит значение 1, а ячейка А2 содержит формулу, которая была скопирована вниз по столбцу: =А1+1

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

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

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

Данные, введенные в шагах 1 и 2, обеспечивают Excel необходимой информацией для определения типа серии, которую надо использовать. Если бы вы ввели 3 в ячейку А2, то серия бы состояла из нечетных чисел: 1,3, 5, 7 и т. д.

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

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

Таблица 35.1. Типы данных с возможностью автозаполнения

Вы также можете создавать собственные списки элементов для автоматического заполнения. Для этого откройте диалоговое окно Параметры Excel и перейдите в раздел Дополнительно . Затем прокрутите окно вниз и нажмите кнопку Изменить списки для отображения диалогового окна Списки . Введите ваши элементы в поле Элементы списка (каждый на новой строке). Затем нажмите кнопку Добавить , чтобы создать список. На рис. 35.4 показан пользовательский список названий регионов, которые используют римские цифры.

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

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

Формальный алгоритм выглядит так.

1. Наберите в двух соседних ячейках первые два числа, чтобы Excel мог определить разницу между ними.
2. Выделите обе ячейки. Для этого щелкните на одной из них и, удерживая нажатой кнопку мыши, протащите рамку выделения на соседнюю ячейку, чтобы она захватила ее.
3. Наведите указатель мыши на маркер, который находится в нижнем правом углу рамки выделения (рис. 2.26, а). При этом он примет вид черного плюсика.
4. Щелкните и, удерживая нажатой кнопку мыши, протяните рамку до появления на всплывающей подсказке возле указателя мыши последнего числа, которое будет вставлено в последнюю ячейку. Протаскивать рамку можно в любом направлении (вверх-вниз, влево-вправо) (рис. 2.26, б).
5. Отпустите кнопку мыши, чтобы диапазон охваченных ячеек заполнился (рис. 2.26, в).

Ячейки, которые я выделила, заполнились по образцу. В первом столбце к числу в предыдущей строке прибавляется 1, во втором столбце - 2. Автозаполнение можно применять при вводе времени, дат, дней недели, месяцев, а также комбинаций текста с числом. Посмотрите на рис. 2.27.

В этой таблице мне достаточно было ввести первую строку, дальше выделить ее и за «плюсик» тащить вниз. Excel при этом увеличил каждое значение на единицу. А если бы лабораторные работы шли не каждый день, а через день или через три дня, то нужно было бы ввести две строки, чтобы Excel определил разницу между значениями в ячейках. И дальше программа все заполнит автоматически, только увеличивать значение в ячейках будет не на единицу, а на ту разницу, которую вы ей задали (рис. 2.28).

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

Например, вы ввели в ячейку время (то есть эта ячейка у вас в формате Время) и хотите, чтобы во всем столбце был точно такой же формат. Что делать? Для этого выделяете ячейку и тащите за «плюсик» рамку на весь столбец. После того как вы отпустите кнопку мыши, в правом нижнем углу ряда появится кнопка Параметры автозаполнения. Щелкнув на ней, вы откроете меню, в котором можно выбрать способ заполнения ячеек (рис. 2.29). Выбираем Заполнить только форматы. Весь столбец у вас теперь в формате Время.

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

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

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