Функции автозаполнение в таблице программы excel

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

Смотрите также видеоверсию статьи «Тонкости функции Автозаполнения в MS Excel».

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

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

По умолчанию Excel продолжит заполнение данными ячейки в зависимости от имеющихся в выделенных ячейках/ячейке данных, например, если просто растянуть число «1», то остальные ячейки заполнятся единицами, если выделить две ячейки с числами «1» и «2», то Excel продолжит ряд данных: «3», «4», «5» и т.д.

АВТОЗАПОЛНЕНИЕ В MICROSOFT EXCEL

Можно ли как-то повлиять на виды автозаполнения?

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

Аналогичного результата (имеется ввиду изменения режима автозаполнения) можно добиться, если воспользоваться командой «Заполнить» из вкладки «Главная», группа «Редактирование», но, в это случае, сначала надо выделить область на листе, которая будет изменяться, а потом воспользоваться соответствующей командой.

А что, если необходимо продолжить текстовый перечень или даты?

Автозаполнение отлично работает с датами, при использовании простого растягивания с помощью маркера заполнения, изменение идет аналогично числам.

А если воспользоваться правой клавишей мышки, или командой «Заполнить», то появляются дополнительные возможности для заполнения.

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

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

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

Однако, пользователю доступна возможность задавать такие списки, а также просматривать существующие. Настройка списков находится по адресу: Файл / Параметры Excel / Дополнительно / Группа Общие / Изменить списки…

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

Хитрые приемы, о которых мало кто знает.

Вот так работает автозаполнение в Excel, в завершение, пару хитростей работы с автозаполнением.

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

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

Источник: msoffice-prowork.com

Суперсила Мгновенного заполнения (Flash Fill)

Чтобы выявить логику (шаблон, 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

Источник: www.planetaexcel.ru

Маркер автозаполнения в Excel: что это такое и как им пользоваться?

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

Поехали!

Как использовать функцию

Чтобы сделать автозаполнение ячеек в Excel – достаточно использовать специальный маркер заполнения. Что это такое? Маркер автозаполнения – это функциональный инструмент, который позволяет использовать автоматическое заполнение ячеек определенными данными, используя текст, цифры, формулы, дату и т.д.

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

1. Выделить основной блок левой кнопкой мыши.
2. Теперь наведите курсор (или стрелочку) в правый нижний угол ячейки. Как только стрелка превратится в крестик – зажимаем левую кнопку мыши (ЛКМ) и тянем вниз до определенной строки.

1. Вы увидите, как рабочие блоки будут подсвечены полужирной рамкой.

1. Отпустите левую кнопку.
2. Теперь текстовая информация скопировалась во все выделенные ячейки.

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

Но вот куда более интересно обстоят дела с цифрами и формулами.

Работа с числовыми значениями

В этой главе мы прикоснемся уже к цифирным значениям, и начнем с самого простого. Как вы думаете – сколько минимум чисел необходимо для автозаполнения, и что будет, если мы установим только одну цифру? Давайте проверим это. Смотрим на скриншот ниже. Если кто помнит уроки информатики в школе, то нас учили, что для быстрого заполнения цифрами нужно как минимум 2 цифры – например, 1 и 2. Попробуйте написать единицу и потянуть маркер автозаполнения.

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

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

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

Прогрессия заполнится автоматически. Но есть небольшая проблема – почему-то не во всех версиях программы работает клавиша Ctrl . Если у вас приложение просто выполнило копирование, вызовите заполнение через параметры, как мы это делали ранее выше.

Инструмент

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

1. Поставьте две цифры прогрессии и выделите все ячейки, которые вы хотите заполнить.

1. Перейдите на вкладку «Главная».

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

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

Ctrl + Z

1. Выберите команду «Прогрессия».

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

1. Расположение – можно изменить расположение по строкам или столбцам, но лучше работать с выделением и не менять этот параметр.
2. Тип – здесь мы можем задать конфигурацию прогрессии. По умолчанию в прошлом способе программа выбираем арифметическую прогрессию. Давайте для примера попробуем геометрическую.
3. Единицы. Прогрессия может работать с датой – например, вы можете задать прибавление по дням, месяцам, годам или по рабочим дням.
4. Шаг – параметр, задающий количество значений в одном шаге. Можно установить автоматическое значение.
5. Предельное значение – если вам нужно задать ограничение, то его стоит вводить в эту строку.

1. В конце жмем «ОК».

Формулы

Функция также может работать с выделенными формулами. Давайте разберем на конкретном примере. В нашем виртуальном магазине на складе у нас хранится определенный товар (шоколад, вода, молоко, чай, кафе и т.д.). У нас есть информация о количестве товара в штуках (пачках) и о цене за одну единицу.

1. Чтобы не считать все вручную, мы будем использовать формулы. В столбце «Общей стоимости» пытаемся умножить:

Количество * Цена за штуку

1. Выделяем ячейку и вводим формулу, начиная со знака равно (=). Напомню, что адреса ячеек не обязательно вводить вручную, достаточно их выделить ЛКМ. Можете посмотреть на пример как у меня.

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

1. На всякий случай проверьте, чтобы формулы во всех ячейках были правильные.

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

Давайте представим себе, что нам нужно каждое значение «Общей стоимости» умножить на коэффициент нашей прибыли. Пусть это будет 20%.

1. Я добавил в нашу табличку еще один столбец – там будет отображена стоимость товаров в нашем магазине. Теоретически – это прибыль, которую мы получим за продажу всех этих товаров. И коэффициент, который равен 1,2.

1. Теперь умножаем общую стоимость и коэффициент, который находится в отдельной ячейке. Но так как мы будем использовать не изменяющуюся цифру в одной ячейке, то нам нужно её зафиксировать. Для этого мы используем в формуле знаки доллара ($). Причем мы можем отдельно зафиксировать изменение значения по строке или по столбцу. Например, если вы хотите, чтобы менялась только строка, тогда ставим доллар перед буквой. Если вы хотите, чтобы менялась только столбец, то ставим доллар перед цифрой.

$B$8 – статическая ячейка.
$B8 – не меняется только столбец.
B$8 – не меняется только строка.

1. А теперь еще раз проверьте, чтобы в других строках были правильные формулы.

Дата

Программа отлично работает и с датами. Попробуйте установить одну дату, а потом выполнить автозаполнение. Как видите, приложение автоматом заполнило все ячейки по дням. В параметрах можно изменить шаг заполнения (день, месяц, год и т.д.). Если же вам нужен неровный шаг, состоящий из нескольких дней, тогда используем инструмент «Заполнения», который мы рассматривали чуть раньше.

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

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

Свой список

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

1. Откройте первую вкладку «Файл».

1. Перейдите в «Параметры».

1. В разделе «Дополнительно» листаем до подраздела «Общие» и жмем по кнопке «Изменить списки».

1. В правый блок через перенос на новую строчку ( Enter ) вводим значения элементов списка. В конце жмем «Добавить».

1. Проверьте, чтобы список добавился. Два раза жмем «ОК».

1. Теперь пробуем его использовать.

В этом уроке мы узнали много нового. Если у вас еще остались какие-то вопросы по теме – пишите их в комментариях. На этом я с вами прощаюсь и жду вас в новом уроке. Пока-пока.

Видео

Источник: wifigid.ru