Как сделать автоматический список в excel?

Автоматическая нумерация строк

В этом курсе:

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

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

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

В этой статье

Заполнение столбца последовательностью чисел

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

Введите начальное значение последовательности.

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

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

Выделите ячейки, содержащие начальные значения.

Примечание: В Excel 2013 и более поздних версиях кнопка » Экспресс-анализ » отображается по умолчанию, если выбрано несколько ячеек с данными. Для завершения этой процедуры кнопку можно проигнорировать.

Перетащите маркер заполнения , охватив диапазон, который нужно заполнить.

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

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

Совет: Если маркер заполнения не виден, возможно, его сначала необходимо отобразить. Дополнительные сведения см. в статье Отображение или скрытие маркера заполнения.

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

Нумерация строк с помощью функции СТРОКА

Введите в первую ячейку диапазона, который необходимо пронумеровать, формулу =СТРОКА(A1).

Функция СТРОКА возвращает номер строки, на которую указана ссылка. Например, функция =СТРОКА(A1) возвращает число 1.

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

Совет: Если маркер заполнения не виден, возможно, его сначала необходимо отобразить. Дополнительные сведения см. в статье Отображение или скрытие маркера заполнения.

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

Если вы используете функцию СТРОКА и хотите, чтобы числа вставлялись автоматически при добавлении новых строк данных, преобразуйте диапазон данных в таблицу Excel. Все строки, добавленные в конец таблицы, последовательно нумеруются. Дополнительные сведения см. в статье Создание и удаление таблицы Excel на листе.

Для ввода определенных последовательных числовых кодов, например кодов заказа на покупку, можно использовать функцию СТРОКА вместе с функцией ТЕКСТ. Например, чтобы начать нумерованный список с кода 000-001, введите формулу =ТЕКСТ(СТРОКА(A1),»000-000″) в первую ячейку диапазона, который необходимо пронумеровать, и перетащите маркер заполнения в конец диапазона.

Отображение или скрытие маркера заполнения

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

В Excel 2010 и более поздних версий откройте вкладку файл и выберите пункт Параметры.

Читать еще:  Как сделать несколько группировок в excel?

В Excel 2007 нажмите кнопку Microsoft Office , а затем — Параметры Excel.

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

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

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

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

Для создания своего списка автозаполнения выполните следующие действия.

Если используется Excel версии 2003, то нужно выбрать меню СервисПараметрыСпискиНовый список — вводим элементы списка через клавишу Enter — выбираем ДобавитьОК.

Если используется Excel версии 2007 (2010), то нужно выбрать ФайлПараметрыДополнительно — в Общие Изменить спискиНовый список — вводим элементы списка через клавишу Enter — выбираем ДобавитьОК.

После создания своего списка автозаполнения достаточно в нужную ячейку таблицы ввести первое значение из списка (в примере Иванов Антон) и протянуть маркер заполнения ячейки в нужном направлении. Смотрите подробнее Как пользоваться списками автозаполнения и вводить стандартные последовательности.

Что делать, если нет маркера автозаполнения?

Если маркер (курсор) заполнения отсутствует, то нужно настроить Excel так, чтобы маркер отображался.

Для этого, если Вы используете версию 2003, выбираем СервисПараметры — на вкладке Параметры устанавливаем галочку Перетаскивание ячеек.

Если Вы используете версию 2007 или 2010, Файл (кнопка Офис) — ПараметрыДополнительноРазрешить маркеры заполнения и перетаскивания ячеекОК.

Ввод данных экспресс-методом

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

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

В строку формул введем нужное значение и нажмем на клавиатуре сочетание клавиш Ctrl+Enter. Все выделенные ячейки автоматически заполнятся нужными данными.

Кратко об авторе:

Шамарина Татьяна Николаевна — учитель физики, информатики и ИКТ, МКОУ «СОШ», с. Саволенка Юхновского района Калужской области. Автор и преподаватель дистанционных курсов по основам компьютерной грамотности, офисным программам. Автор статей, видеоуроков и разработок.

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

Есть мнение?
Оставьте комментарий

Понравился материал?
Хотите прочитать позже?
Сохраните на своей стене и
поделитесь с друзьями

Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст

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

Содержание

Описание занятия

Видеоверсия

Текстовая версия

Результат автоматического распределения записей по спискам

Записи в перечне слева «сырые», т.е. они не сгруппированы, содержат «маркера (т.е. указатель номера списка)» и саму запись, в списках справа — распределены. Количество в 3 списка взято в качестве примера, это количество может быть произвольным, равно как и название «списки». Особым плюсом можно считать то, что, изменяя маркер в «сыром перечне», можно изменять место расположение записи в сгруппированных списках.

Реализуем такие, автоматически заполняемые списки, подробно объясняя этапы решения задачи.

Суть решения

Видеоверсия

Текстовая версия

Принцип действия

Пользователи, которые уже поработали в Excel, очевидно заметят, что принцип действия таких динамических списков очень схож с таковым у функции ВПР, либо более продвинутого аналога данной функции – связки ИНДЕКС и ПОИСКПОЗ.

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

Читать еще:  Как сделать пивот таблицу в excel?

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

Основная идея реализации

Основу в реализации такого множественного выбора будет составлять чрезвычайно полезная функция ИНДЕКС, которая возвращает значение ячейки, которая находится на пересечении указанных строки и столбца. Столбце у нас известен из условия – это столбец со всеми значениями (правый в «сырой» таблице), а вот номер строки мы будем подставлять динамически.

Данную функции еще часто используют в связке с другой полезной функцией ПОИСКПОЗ, однако, сейчас нам нужна не связка, поскольку номер строки (а именно за поиск номера строки в функции ИНДЕКС отвечает функция ПОИСКПОЗ) мы найдем отдельно.

Поиск номера строки

Видеоверсия

Текстовая версия

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

Поиск номера строки

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

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

Упорядочивание промежуточных вычислений

Видеоверсия

Текстовая версия

Функция НАИМЕНЬШИЙ похожа на вычисление минимального, т.е. функцию МИН, за тем исключением, что позволяет найти не только минимальное, но и 2-е, 3-е и т.д. наименьше значение после минимального.

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

Первым аргументом функции указана ссылка полностью на строку, если будете указывать на диапазон, не забудьте позаботиться о том, чтобы его зафиксировать (сделав абсолютную или смешанную ссылку). Второй аргумент – это смешанная ссылка на вспомогательный ряд, т.е., в первом случае, когда ссылка идет на цифру «1», формула вернет минимальное значение, потом — 2-е после минимального и т.д.

Общая картина вычислений выглядит так:

Упорядочивание чисел с помощью функции НАИМЕНЬШИЙ

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

Построение финальной формулы

Видеоверсия

Текстовая версия

Таким образом, пишем:

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

В конечном итоге получаем вот такой результат:

Результат применения функции ИНДЕКС

Все хорошо, за исключением ошибки, которая находится в не заполненных ячейках. Ошибку легко скрыть, если «обернуть» конечную формулу в функцию ЕСЛИОШИБКА, указав, в качестве второго аргумента, пустую ячейку (просто двойные кавычки).

И вот такой результат:

Результат использования функции ЕСЛИОШИБКА для перехвата ошибок

Последние штрихи

Видеоверсия

Текстовая версия

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

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

Файл с примером

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

Автоматическая нумерация строк в Excel: 3 способа

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

Читать еще:  Как сделать прайс с картинками в excel?

Метод 1: нумерация после заполнения первых строк

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

  1. Для начала, создаем новый столбец под нумерацию строк. В первой ячейке (не считая шапки) пишем цифру 1, затем переходим во вторую, в которой вводим цифру 2.
  2. Теперь нужно выделить эти две ячейки, после чего наводим курсор мыши на нижний правый угол выделенной области. Как только указатель изменит свой вид на крестик, зажав левую кнопку мыши тянем его до последней строки столбца.
  3. Отпускаем левую кнопку мыши, и порядковые номера строк сразу же появятся в строках, которые мы охватили при растягивании.

Метод 2: оператор СТРОКА

Данный метод для автоматической нумерации строк предполагает использование фукнции “СТРОКА”.

  1. Встаем в первую ячейку столбца, которой хотим присвоить порядковый номер 1. Затем пишем в ней следующую формулу: =СТРОКА(A1) .
  2. Как только мы щелкнем Enter, в выбранной ячейке появится порядковый номер. Осталось, аналогично первому методу, растянуть формулу на нижние строки. Но теперь нужно навести курсор мыши на нижний правый угол ячейки с формулой.
  3. Все готово, мы автоматически пронумеровали все строки таблицы, что и требовалось.

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

  1. Также выделяем первую ячейку столбца, куда хотим вставить номер. Затем щелкаем кнопку “Вставить функцию” (слева от строки формул).
  2. Откроется окно Мастера функций. Кликаем по текущей категории функций и выбираем в открывшемся перечне “Ссылки и массивы”.
  3. Теперь из списка предложенных операторов выбираем функцию “СТРОКА”, после чего жмем OK.
  4. На экране появится окно с аргументами функции для заполнения. Кликаем по области ввода информации для параметра “Строка” и указываем адрес первой ячейки столбца, которой хотим присвоить номер. Адрес можно прописать вручную или просто кликнуть мышью по нужной ячейке. Далее кликаем OK.
  5. Нумер строки вставлен в выбранную ячейку. Как растянуть нумерацию на остальные строки мы рассмотрели выше.

Метод 3: применение прогрессии

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

  1. Указываем в первой ячейки столбца ее порядковый номер, равный цифре 1.
  2. Переключаемся во вкладку “Главная”, нажимаем кнопку “Заполнить” (раздел “Редактирование”) и в раскрывшемся перечне щелкаем по опции “Прогрессия…”.
  3. Перед нами появится окно с параметрами прогрессии, которые нужно настроить, после чего нажимаем OK.
    • выбираем расположение “по столбцам”;
    • тип указываем “арифметический”;
    • в значении шага пишем цифру “1”;
    • в поле “Предельное значение” указываем количество строк таблицы, которые нужно пронумеровать.
  4. Автоматическая нумерация строк выполнена, и мы получили требуемый результат.

Данный метод можно реализовать по-другому.

  1. Повторяем первый шаг, т.е. в первой ячейке столбца пишем цифру 1.
  2. Выделяем диапазон, включающий все ячейки, в которые мы хотим вставить номера.
  3. Снова открываем окно “Прогрессии”. Параметры автоматически выставлены согласно выделенному нами диапазону, поэтому нам остается только щелкнуть OK.
  4. И снова, благодаря этим несложным действиям мы получаем нумерацию строк в выбранном диапазоне.

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

Заключение

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

Ссылка на основную публикацию
Adblock
detector