Tw-city.info

IT Новости
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Ms excel автозаполнение

ITGuides.ru

Вопросы и ответы в сфере it технологий и настройке ПК

Зачем нужна и как пользоваться функцией автозаполнения в Excel

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

Видео по созданию списков автозаполнения

Понятие и способы использования

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

Автозаполнение может быть знакомой процедурой для постоянных пользователей программы в качестве операции по выделению 2-х ячеек с последовательными числами и протягиванием курсора вниз до появления целой численной вереницы в арифметической прогрессии. Однако не всем известны другие, не менее полезные подобные функции Excel, поскольку программа не ограничивается лишь арифметической прогрессией. Несложно найти и пользоваться нужной прогрессией — после ввода первых значений, курсор мышки направляется к правому нижнему углу, но протягивает не левой, как обычно, кнопкой мышки вниз/вбок, а правой. Такие действия вызовут контекстное меню, где выбирается пункт «прогрессия», а затем подбирает необходимый ее вид.

Таким образом, известно несколько основных способов автозаполнения в Excel:

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

Автозаполнение ячеек можно сделать простым расширением выделенной области

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

  • Двойным щелчком мышки по заполненному квадрату можно также добиться быстрого автозаполнения, только если близлежащие ячейки уже содержат информацию.
  • Автоматическое заполнение в Excel в режиме убывания. Процедура производится как обычная, но с точностью наоборот. К примеру, вводится в первые ячейки 30, 29 и курсором протягивается вниз.
  • Заполнение ячеек одинаковым значением.
  • Заполнение нечетными или четными значениями.
  • Мощность MS Excel в основном заключается в том, что осуществить автозаполнение можно не только простыми числовыми значениями, но и другими типами данных или формулами. К тому же есть возможность создавать индивидуальные списки, которые позволят пользователю не тратить каждый раз время на новый ввод тех же значений.

    Создание списков

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

    • для программ 2003 года необходимо перейти в пункт «Сервис», после чего в «Параметры», «Вкладка» и «Списки»;
    • для пользователей программ 2007 года следует зайти в «Меню» — «Параметры» — «Вкладка» — «Изменить списки»;
    • для программ 2010 года — «Файл»-«Параметры»-вкладка дополнительно-кнопка — «Изменить списки».

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

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

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

    Автозаполнение — это далеко не единственная полезная и нужная функция такой программы Microsoft Office Excel и для познания всех ее «секретов» требуются стремление к знаниям, время и терпение. Удачи вам!

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

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

    Автозаполнение ячеек в Excel позволяет ускорить ввод данных на рабочий лист. Некоторые действия в Microsoft Excel приходится повторять по несколько раз, что отнимает большое количество времени. Именно для автоматизации таких задач и была разработана функция автозаполнения. В данном уроке мы разберем самые распространенные способы автозаполнения: использование маркера и мгновенное заполнение, которое впервые появилось в Excel 2013.

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

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

    1. Выделите ячейку, данные которой необходимо продублировать. В правом нижнем углу выделенной ячейки появится маленький квадрат – это маркер автозаполнения.
    2. Нажмите и, удерживая левую кнопку мыши, перетащите маркер автозаполнения, пока все требуемые ячейки не будут выделены. Разом вы можете заполнять ячейки либо столбца, либо строки.
    3. Отпустите кнопку мыши, чтобы заполнить выбранные ячейки.

    Автозаполнение последовательного ряда данных в Excel

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

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

    Мгновенное заполнение в Excel

    В Excel 2013 появилась новая опция Мгновенное заполнение, которая может автоматически вводить данные на лист, экономя Ваше время и силы. Точно так же, как и Автозаполнение, данная опция определяет, какого рода информацию вы вводите на листе.

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

    Читать еще:  Не суммируются ячейки в excel

    1. Начните вводить данные на листе. Когда функция Мгновенное заполнение определит закономерность, под выделенной ячейкой появится предварительный просмотр вариантов.
    2. Нажмите Enter. Данные будут добавлены на лист.

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

    Умное автозаполнение вниз и вправо

    До сих пор иногда с улыбкой вспоминаю один из своих первых выездных корпоративных тренингов лет 10 назад.

    Представьте: огромный как футбольное поле опенспейс-офис российского представительства международной FMCG-компании. Шикарный дизайн, дорогая оргтехника, дресс-код, экспаты курлыкают по углам — вот это вот все 🙂 В одной из переговорок начинаю двухдневный тренинг продвинутого уровня по текущей тогда версии Excel 2003 для 15 ключевых сотрудников экономического департамента вместе с их руководителем. Знакомимся, расспрашиваю их о бизнес-задачах, проблемах, прошу показать несколько типовых рабочих файлов. Показывают километровой длины выгрузки из SAP, простыни отчетов, которые они по этому всему делают и т.д. Ну, дело знакомое — мысленно прикидываю темы и тайминг, подстраиваюсь под аудиторию. Краем глаза замечаю, как один из участников, демонстрируя кусочек своего отчета, терпеливо тянет ячейку с формулой вниз за черный крестик в правом нижнем углу на несколько тысяч строк, потом проскакивает с лету конец таблицы, тянет обратно и т.д. Не выдержав, прерываю его кёрлинг мышью по экрану и показываю двойной щелчок по черному крестику, объясняя про автозаполнение вниз до упора.

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

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

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

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

    • Копирование не всегда происходит до конца таблицы. Если таблица не монолитная, т.е. в соседних столбцах есть пустые ячейки, то не факт, что автозаполнение сработает до конца таблицы. Скорее всего процесс остановится на ближайшей пустой ячейке, не дойдя до конца. Если ниже по столбцу есть занятые чем-то ячейки, то автозаполнение остановится на них совершенно точно.
    • При копировании портится дизайн ячеек, т.к. по-умолчанию копируется не только формула, но еще и формат. Для исправления надо щелкать по кнопке параметров копирования и выбирать Только значения (Fill without format) .
    • Не существует быстрого способа также удобно протянуть формулу не вниз, а вправо, кроме как тянуть вручную. Двойной щелчок по черному крестику — это только вниз.

    Давайте попробуем исправить эти недостатки с помощью простого макроса.

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

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

    Для пущего удобства можно назначить этим макросам сочетания клавиш, используя кнопку Макросы — Параметры (Macros — Options) там же на вкладке Разработчик (Developer) . Теперь достаточно будет ввести нужную формулу или значение в первую ячейку столбца и нажать заданное сочетание клавиш, чтобы макрос автоматически заполнил весь столбец (или строку):

    MS Excel 2007: Автозаполнение ячеек. Форматирование ячеек

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

    Задания для самостоятельной работы

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

    Автоматическое повторение элементов, уже введенных в столбец

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

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

    Рис. 3.1. Автоматическое завершение

    Заполнение данными с помощью маркера заполнения

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

    Читать еще:  Как проверить объем видеопамяти

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

    Рис. 3.3 Выбор команд с помощью маркера заполнения

    Заполнение активной ячейки содержимым смежной ячейки

    Для заполнения смежных ячеек необходимо выделить пустые ячейки, захватывая в выделении и ячейку с данными, снизу, справа, сверху или слева от ячейки, которая содержит данные для заполнения. На вкладке Главная в группе Редактирование выбрать команду Заполнить, а затем в открывшемся списке команду Вниз, Вправо, Вверх или Влево (рис. 3.4).

    Примечание . Для быстрого заполнения ячейки данными из ячейки, которая находится сверху или слева, можно воспользоваться комбинациями клавиш Ctrl+D или Ctrl+R.

    Рис. 3.4. Команда Заполнить

    Заполнение ячеек последовательностью чисел, дат или элементов встроенных списков

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

    • ввести начальное значение в ячейку
    • ввести значения в следующие ячейки, чтобы задать образец заполнения
    • выделить заполненные ячейки

    Например, если требуется задать последовательность 1, 2, 3, 4, 5. в первые две ячейки нужно ввести значения 1 и 2. Если необходима последовательность 2, 4, 6, 8. – последовательность 2 и 4. Если необходима последовательность 2, 2, 2, 2. то вторую ячейку можно оставить пустой.

    — перетащить маркер заполнения по диапазону (до нужной ячейки), который нужно заполнить.

    Примечание . Для заполнения в порядке возрастания необходимо перетащить маркер вниз или вправо. Для заполнения в порядке убывания – вверх или влево.

    Если начальным значением является дата, например, «янв.13», то для получения ряда, состоящего из названия месяцев в том же формате, нужно выбрать команду Прогрессия из меню Заполнить группы Редактирование вкладки Главная (рис. 3.5.) и в появившемся диалоговом окне поставить в поле Единицы переключатель напротив значения месяц.

    Рис. 3.5.Диалоговое окно Прогрессия

    Пользовательский список автозаполнения

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

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

    Чтобы создать список из существующего необходимо выделить на листе диапазон ячеек, которые требуется включить в список автозаполнения. Нажать кнопку MS Office и выбрать команду Параметры Excel. В появившемся диалоговом окне выбрать группу Основные, а в ней в области Основные параметры работы с Excel нажать кнопку Изменить списки.

    В диалогом окне Списки (рис. 3.6) нажать кнопку Импорт (в данном поле указываются адреса ячеек, которые включены в список). Элементы списка автоматически добавятся в виде нового списка. По завершению нажмите кнопку Ок.

    Рис. 3.6. Диалоговое окно Списки

    Чтобы создать новый список, так же его можно ввести в поле Элементы списка диалогового она Списки. Каждый элемент отделяется друг от друга нажатием на клавишу Enter. Затем нажать на кнопку Добавить и он отразиться в виде списка в поле Списки.

    Для удаления списка нужно его выделить в диалоговом окне Списки и нажать кнопку Удалить. Подтвердить удаление и нажать кнопку Ок.

    Форматирование ячеек

    Для форматирования ячеек: выбор формата данных в ячейке, размещение текста в ячейке, установки типа, размера, цвета шрифта, оформление границ ячейки или таблицы, команда заливки – используются команды вкладки Главная групп Шрифт, Выравнивание, Число или диалоговое окно Формат ячеек.

    Выбор формата отображения значения в ячейке

    В предыдущей лекции рассматривался вопрос о типах данных: текст, число, формула. С помощью Числовых форматов можно указывать какие значения принимают данные в ячейке, например, денежный формат (2,00р. или $ 2,00), дата (15.07.13 или 15 июля 2013 г.), процентный (20,00%) и другие. Также для каждого формата устанавливаются свои параметры Для этого можно вызвать на экран диалоговое окно Формат ячеек (рис. 3.7) или воспользоваться командами группы Число вкладки Главная (рис. 3.8).

    Рис. 3.7. Диалоговое окно Формат ячеек: Число

    Рис. 3.8. Команды группы Число вкладки Главная

    Помимо команд, относящихся к формату данных, в данной группе можно установить формат с разделителями (разделение разрядности, например, 2 000), увеличить / уменьшить разрядность (количество знаков после запятой, например, 2,35346 – при уменьшении разрядности до одного знака получится 2,4).

    Выравнивание данных

    Для выравнивания данных можно использовать команды группы Выравнивание вкладки Главная или команды диалогового окна Формат ячеек: выравнивание (рис. 3.9). В области выравнивание указывается расположение данных относительно ячейки по горизонтали и вертикали; в области отображение Переносить по словам можно заполнить данные ячейки в несколько строк, Автоподбор ширины – изменить размер шрифта, если данные оказались больше ширины ячейки, Объединение ячеек – из нескольких ячеек делает одну; в области Ориентация можно указать на какой угол нужно повернуть текст.

    Рис. 3.9 Диалоговое окно Формат ячеек: Выравнивание

    Установка параметров шрифта

    С помощью команд группы Шрифт вкладки Главная или диалогового окна Формат ячеек: Шрифт (рис. 3.10) можно изменить тип, размер, начертание, цвет шрифта. С помощью команд Надстрочный и Подстрочный в поле Видоизменения устанавливаются верхний (t о ) и нижний (t о ) индексы.

    Читать еще:  Группировка в excel с заголовком

    Рис. 3.10 Диалоговое окно Формат ячеек: Шрифт

    Обрамление выделенного диапазона и заливка

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

    Предварительно выделив таблицу, в диалоговом окне Формат ячеек: Границы (рис.3.11) можно выбрать тип линии, установить цвет, а затем в области Отдельные указать с помощью мыши только те границы, которые следует установить для таблицы.

    Рис. 3.11 Диалоговое окно Формат ячеек: Границы

    Для заливки ячейки или группы ячеек используется команда Заливка из группы Шрифт вкладки Главная или диалогового окна Формат ячейки: Заливка. В последнем можно указать тип узора заливки и его цвет.

    Задания для самостоятельной работы

    1. Запустите программу MS Excel.

    2. Создайте пользовательский автоматический список, состоящих из любых 4 элементов (например, времен года – зима, весна, лето, осень).

    3. Оформите пользовательский список на листе 1.

    4. Переименуйте лист 2 в Факторы.

    5. Оформите таблицу по образцу, учитывая расположение текста в ячейках, начертание, установленные границы к таблице:

    6. Для данной таблице установите тип шрифта Arial; размер шрифта – 12, цвет – синий.

    7. Поверните текст в ячейке В3 на 90 о .

    8. На листе 3 создайте произвольную таблицу, в которую включены такие поля как Дата (установите нужный формат), Числовой (установите нужный формат, например, числовой с

    указанием два знака после запятой, денежный, процентный). Установите границы к данной таблице и скопируйте ее на лист 1.

    9. Сохраните данный файл под именем Форматирование.

    10. Закройте программу.

    СДЕЛАЙТЕ ДОБРОЕ ДЕЛО, ПОДЕЛИТЕСЬ С ДРУЗЬЯМИ

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

    MS Excel — не просто табличный редактор, это настоящая среда разработки, которая обладает внушительным арсеналом возможностей по упрощению жизни пользователя. На самом деле, при должном умении и навыке работы, уровень автоматизации всего и вся в MS Excel просто удивляет — кажется, разработчики предусмотрели буквально всё, что может потребоваться пользователю.

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

    Автозавершение ввода в MS Excel

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

    Стандартное автозавершение ввода в Excel — я ввел в ячейку первую букву, а Excel «додумал» все остальное. В этот раз не ошибся

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

    Надо отметить, что мой пример с именем и фамилией — не самый удачный. Дело в том, что имя и фамилия у разных людей сильно отличается и вряд ли в вашем столбце будет хотя бы три «Ивана Иванова», так что автозаполнение тут не принесет никакой пользы.

    Другое дело, когда речь идет о столбце с повторяющимися данными. К примеру, при заполнении столбца «семейное положение» (подразумевающего не так много комбинаций), простой набор «Хо» (холост) или «Ж» (женат) вместо целого слова, здорово сэкономит вам время.

    Отключение автозаполнения ячеек в MS Excel

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

    Для отключения автозаполнения, нужно зайти в настройки MS Excel (Файл » Параметры).

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

    Чтобы вновь включить эту функцию, просто поставьте галочку обратно.

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

    Выбор значения для автозаполнения из раскрывающегося списка

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

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

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

    Альтернативная (от слова Alt!) возможность автозаполнения в MS Excel из раскрывающегося списка

    Ссылка на основную публикацию
    ВсеИнструменты 220 Вольт
    Adblock
    detector
    ×
    ×