Хитрости »

Основные понятия (27) Сводные таблицы и анализ данных (10) Графики и диаграммы (5) Работа с VB проектом (12) Power BI и Power Query (23)

Условное форматирование (5) Списки и диапазоны (5) Макросы(VBA процедуры) (68) Разное (43) Баги и глюки Excel (5)

---

Списки автозаполнения

---

Если вы еще не знаете про такой прием в Excel, как автозаполнение ячеек путем протягивания мышью крестика - то самое время с ним познакомиться, т.к. инструмент весьма полезный. Что делает автозаполнение: допустим, нам надо заполнить строку или столбец днями недели (Понедельник, Вторник и т.д.). Без автозаполнения нам пришлось бы последовательно вводит в каждую ячейку вручную все эти дни. Но в Excel для выполнения подобной операции нам потребуется заполнить лишь первую ячейку. Запишем только в ячейку A1 Понедельник. Выделяем эту ячейку -наводим курсор мыши на правый нижний угол ячейки до появления черного крестика:

Как только курсор стал крестиком, зажимаем левую кнопку мыши и удерживая её тянем вниз(если надо заполнить строки) или вправо(если надо заполнить столбцы) на необходимое количество ячеек. Теперь все захваченные нами ячейки заполнены днями недели. И не одним Понедельником, а по порядку следования:

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

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

Выбираете необходимый пункт и вуаля!
Серым шрифтом выделены неактивные пункты меню - те, которые нельзя применить к данным в выделенных ячейках

Подобное автозаполнение доступно для числовых данных, для дат и некоторых распространенных данных - дней недели и месяцев. Но откуда Excel знает дни недели и имена месяцев? На основании "зашитых" в него списков. И эти списки можно дополнять своими.
Например, в шаблоне таблицы нам постоянно приходится записывать шапку руками: Дата, Артикул, Цена, Сумма. Можно их вписывать каждый раз руками или копировать из другой таблицы, но можно сделать и при помощи списков автозаполнения:

• Excel 2003, то переходите Сервис(Tools) -Параметры(Options) -вкладка Списки(Lists)
• Excel 2007 -Кнопка Офис -Параметры Excel(Excel Options) -вкладка Основные(General) -кнопка Изменить списки(Edit Custom Lists)
• Excel 2010 и выше -Файл(File)-Параметры(Options) -вкладка Дополнительно(Advanced) -кнопка Изменить списки...(Edit Custom Lists)

Здесь мы увидим все "вшитые" в Excel списки автозаполнения:

Самый первый пункт в левой части - НОВЫЙ СПИСОК(NEW LIST). Выделяем его и ставим курсор в поле правее - Элементы списка(List Entries). Заносим туда наименования столбцов через запятую(как на рисунке выше: Дата, Артикул, Цена, Кол-во) или занося каждый элемент с новой строки(перенос на новую строку производится клавишей Enter). Нажимаем Добавить(Add).
Так же можно воспользоваться полем Импорт списка из ячеек(Import list from cells). Активируем поле выбора, щелкнув в нем мышкой. Выбираем диапазон ячеек со значениями, из которых хотим создать список. Жмем Импорт(Import). В поле Списки(Custom lists) появиться новый список из значений указанных ячеек.
После добавления списков закрываем окно, нажав кнопку Ок.
Теперь проверим в действии. Пишем в любую ячейку слово Дата и протягиваем, как описано выше. Excel заполнит нам остальные столбцы значениями из того списка, который мы сами только что создали. Созданные нами списки можно редактировать и удалять. Встроенные изначально(например, дни недели) - удалять и изменять нельзя.

---

Дополнительное использование списков автозаполнения
Но списки автозаполнения помогут не только при записи значений на листе. Так же эти списки можно использовать и для сортировки значений. Для этого выделяем нужные для сортировки ячейки -переходим на вкладку Данные(Data) -Сортировка(Sort). Раскрываем поле Порядок(Order) -Настраиваемый список(Custom list...). Выбираем нужный список из списков автозаполнения.
Рассмотрим жизненную ситуацию, применимую к созданному нами только что списку: Дата, Артикул, Цена, Кол-во.
Например, есть уже заполненная таблица, столбцы которой расположены не в том порядке, который нам нужен: Артикул, Дата, Кол-во, Цена. Как видно, порядок столбцов отличается от нашего эталонного. И нам надо упорядочить столбцы в том же порядке, в котором идет наш список(не путать с алфавитным). Выделяем полностью столбцы нашей таблицы -вкладка Данные(Data) -Сортировка(Sort). В окне сортировки жмем кнопку Параметры(Options) -столбцы диапазона(sort left to right). Жмем Ок. Теперь выбираем строку заголовка в поле сортировать по(sort by) - если заголовки для сортировки в первой строке, то выбираем Строка 1(Row 1), если во второй – Строка 2(Row 2) и т.д. Раскрываем поле порядок(Order) -выбираем настраиваемый список(Custom list) и выбираем нужный нам список. Нажимаем ОК, и второй раз Ок уже в окне сортировки. Столбцы таблицы упорядочены в том порядке, который нам нужен.

Где это будет работать?
Эти списки работают в любой версии Excel, но есть одна ложка дегтя: созданные пользователем списки хранятся непосредственно на компьютере. Следовательно они будут доступны из любой книги на том ПК, на котором эти списки были созданы. И если переслать кому-то книгу, в которой ранее эти списки успешно использовались, новый пользователь их не увидит.

 

Так же см.:
Выпадающие списки
Связанные выпадающие списки

---

Статья помогла? Поделись ссылкой с друзьями!     Видеоуроки

---

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика