Если вы еще не знаете про такой прием в 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, но есть одна ложка дегтя: созданные пользователем списки хранятся непосредственно на компьютере. Следовательно они будут доступны из любой книги на том ПК, на котором эти списки были созданы. И если переслать кому-то книгу, в которой ранее эти списки успешно использовались, новый пользователь их не увидит.

 

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

32 комментария

  1. Дмитрий а можно в excel так чтобы когда заполняешь по рабочим дням выходило так:
    13.10.2014
    13.11.2014
    15.12.2014
    13.01.2015
    ........
    то есть одно число 13 е в каждом месяце если в рабочие дни то 13 если в выходные чтобы excel перевел дату на рабочий день то есть 15(понедельник)и так далее

          1. спс Дмитрий буду изобретать формулу))) дай Бог чтобы получилось

  2. =ЕСЛИ(ДЕНЬНЕД(C6)=7;(C6)+0;(C6)+1) ИЛИ (ЕСЛИ(ДЕНЬНЕД(C6=6;(C6)+0;(C6)+1) попробовал эту формулу выдает ошибку слишком много аргументов для данной функции Дмитрий можно ли эту формулу упростить?

  3. Дмитрий один вопрос как сделать так чтобы в таблице заполненной числами в одной ячейке округлить вверх в другой вниз чтобы это автоматом при перетаскивании заполнялось например:
    первая ячейка: 200,50
    вторая ячейка: 200,30
    ....................
    как в одной формуле это написать?

    1. будет гораздо сложней много проблем связанных с длиной месяца и переходом через год. Нужны будут два столбца. А формула примерно такая
      =ЕСЛИ(ДЕНЬНЕД(N5+ДЕНЬ(КОНМЕСЯЦА(N5;0)))=7;N5+ДЕНЬ(КОНМЕСЯЦА(N5;0))+2;ЕСЛИ(ДЕНЬНЕД(N5+ДЕНЬ(КОНМЕСЯЦА(N5;0)))=1;N5+ДЕНЬ(КОНМЕСЯЦА(N5;0))+1;N5+ДЕНЬ(КОНМЕСЯЦА(N5;0))))

    1. Елена, добрый день. А что смущает? Это не опечатка - так я специально написал. Есть проблема - жалуйтесь и просите помощи в форуме. В комментариях нет возможности все проблемы решать. Тем более на форуме более 2000 участников, а в комментарии смотрю практически только я.

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

Добавить комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.