Как сделать нумерацию в 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 2007 нажмите кнопку Microsoft Office , а затем — Параметры Excel.

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

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

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

Заключение

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

Автонумерация строк и столбцов в базе данных Excel

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

В базе данных (рис. 1) записи и столбцы пронумерованы. После применения автофильтра в столбце G (> 335) и скрытии двух столбцов (D и F), пропуски в автонумерации не появились (рис. 2).

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

Читать еще:  Как сделать на английском excel?

Рис. 1. Полная база данных

Скачать заметку в формате Word или pdf, примеры в формате Excel

Рис. 2. Ваша цель – иметь метки строк/столбцов, которые нумеруют только видимые строки/столбцы

Решение: для начала задайте имя базе данных, расположенной в диапазоне А2:G13 (рис. 3). [1]

Рис. 3. Создание имени

Это исключит строку 1 из выбора по умолчанию, когда вы примените автофильтр. Также требуется добавить пробел в ячейку сразу после последней записи (в нашем примере – в ячейку А14). Это нужно, чтобы победить досадную ошибку в Excel, вследствие которой показывается последняя запись (строка), независимо от того, соответствует ли она критериям фильтра. На рис. 4а в ячейке А14 ничего нет. После применения фильтра по столбцу G (больше 345) показывается последняя запись со значением 342. На рис. 4б в ячейке А14 имеется пробел. После применения того же фильтра последняя строка не отражается.

Рис. 4. Добавьте пробел, чтобы фильтр работал корректно в отношении последней записи диапазона

В ячейку А3 введите формулу: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B$3:B3) и скопируйте её в диапазон А4:А13. Обратите внимание, что первая строка в ссылке является абсолютной, а вторая – относительной. Поэтому при копировании вдоль столбца формула будет меняться. Например, в ячейке А13 формула примет вид: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B$3:B13).

В ячейку А2 введите формулу: =ЕСЛИ(ЯЧЕЙКА( » ширина » ;A1)=0;0;1). В ячейку B2 введите формулу: =ЕСЛИ(ЯЧЕЙКА( » ширина » ;B1)=0;0;МАКС($A2:A2)+1) и скопируйте ее вдоль рада в ячейки С2:G2. Обратите внимание, что первая ссылка на столбец абсолютная, а вторая относительная. Формула в G2 примет вид: =ЕСЛИ(ЯЧЕЙКА( » ширина » ;G1)=0;0;МАКС($A2:F2)+1).

Рассмотрим подробнее работу формул. Начнем с автоматической нумерации записей. Синтаксис функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции;ссылка). 3-й тип соответствует функции СЧЁТЗ. Формула в ячейке А3 использует тот факт, что функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает скрытые ячейки из расчета.

Рассмотрим формулу в А10: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B$3:B12). Общее количество текстовых значений в диапазоне B$3:B12 составляет восемь, но только пять значений являются видимыми. Поэтому формула возвращает значение 5, которое является порядковым номером видимых строк!

Примечание. К сожалению, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ работает только для подсчета итогов по столбцам. Попытка применить ее в ряду 2 приведет к неудаче. Вот что сказано в справке Excel по этой функции: Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных, скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.

Для автоматической нумерации столбцов используйте функцию ЯЧЕЙКА. Синтаксис этой функции ЯЧЕЙКА(тип_сведений;[ссылка]). При указании типа_сведений » ширина » , функция возвращает ширину столбца для верхней левой ячейки аргумента ссылка.

Формула в ячейке A2 =ЕСЛИ(ЯЧЕЙКА( » ширина » ;A1)=0;0;1) возвращает 0, если столбец является скрытым и 1 – в противном случае. Формула в D2 =ЕСЛИ(ЯЧЕЙКА( » ширина » ;D1)=0;0;МАКС($A2:C2)+1). Поскольку ЯЧЕЙКА( » ширина » ;D1)=0 (столбец D скрыт; см. рис. 2), формула возвращает 0. Аналогично формула возвращает 0 и в ячейке Е2 (столбец также скрыт, см. рис. 2). Формула в следующей (теперь уже видимой) ячейке – F2: =ЕСЛИ(ЯЧЕЙКА( » ширина » ;F1)=0;0;МАКС($A2:E2)+1). Поскольку ЯЧЕЙКА( » ширина » ;F1)>0, формула =ЕСЛИ() вернет МАКС($A2:E2)+1. Фрагмент МАКС() вернет наибольшее значение, которое будет соответствовать последней видимой строк. В нашем примере на рис. 2 это значение 3 для столбца С. Следовательно, значение в F2 = 4.

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

[1] Зачем это сделано из дальнейшего текста Джелена не понятно – Прим. Багузина

Как пронумеровать строки в таблице Эксель

Автор: rf-webmaestro · Опубликовано 23.02.2018 · Обновлено 08.02.2019

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

Самый простой способ

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

  1. В первую ячейку впишите единицу. В следующую клетку – цифру «2».

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

  1. Зажмите ЛКМ и протяните курсор вниз на необходимое количество клеток, после чего отпустите мышку.

  1. Теперь вы увидите порядковую нумерацию столбика или строки.

Второй вариант использования данного метода:

  1. Снова впишите «1» в первой ячейке.

  1. Затем поставьте курсор в положение черного крестика.
Читать еще:  Как microsoft excel сделать график?

  1. Зажмите левый Ctrl на клавиатуре вместе с ЛКМ и протяните курсор вниз.

Внимание! Сначала необходимо отпустить клавишу Ctrl , а затем левую кнопку мыши, а не наоборот (1). В противном случае вы получите столбик из одинаковых цифр (2):

Если вам нужна последовательность с определенным шагом, например, 2 4 6 8, то введите первые две цифры ряда и выполните все действия из предыдущей инструкции:

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

  1. Записать начальные значения с необходимым промежутком.

  1. Выбрать мышкой все ячейки после первого значения.

  1. Поставить курсор в нижний правый угол, чтобы он принял вид крестика.

  1. Зажать клавишу Ctrl и протянуть курсор вниз. Теперь таблица будет пронумерована так, как вами и задумывалось.

Обратная нумерация

Для создания обратного порядка вы можете воспользоваться вышеописанным способом:

  1. Впишите первые числа последовательности, например, 10 9 8.

  1. Выделите их и протяните маркер вниз.

  1. На экране появятся номера в обратном направлении. Использовать можно даже отрицательные числа.

«Эксель» подразумевает не только ручной метод, но и автоматический. Ручное перетаскивание маркера курсором весьма затруднительно при работе с объемными таблицами. Рассмотрим все варианты подробнее.

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

  1. Выделите начальную клетку.

  1. В строке функций введите текст «=СТРОКА(А1)» и жмите Enter .

  1. Теперь перетаскиваем отредактированную клетку с маркером вниз.

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

«ПРОГРЕССИЯ»

В этом случае вам не придется вручную перетаскивать маркер. Список создастся в автоматическом режиме по заданным вами условиям. Рассмотрим два варианта использования прогрессии – быстрое и полное.

В быстром режиме вам необходимо:

  1. Ввести первую цифру будущего списка.

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

  1. Во вкладке «Главная» нажать на кнопку «Заполнить» и в меню выбрать пункт «Прогрессия».

  1. В открывшемся окне просто нажать «ОК».

  1. Готово! Выделенные поля превратятся в упорядоченный список.

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

  1. Впишите начальные значения с необходимым шагом. Например, «3 6 9 12».

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

  1. Снова откройте окно «Прогрессия», как это описано в предыдущей инструкции, и нажмите «ОК».

  1. Теперь вы увидите в документе пронумерованную последовательность.

Теперь разберем подробнее работу с функцией «Прогрессия»:

  1. Сначала впишите первое число будущего списка.

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

  1. В разделе «Расположение» выберите направление нумерации (1), тип прогрессии (2), установите шаг для заполнения (3) и предельное значение (4). Затем кликните на «ОК».

  1. Вы увидите пронумерованную таблицу по заданным параметрам. С помощью этого способа вам не придется вручную протаскивать маркер и вписывать стартовые значения.

Рассмотрим подробнее типы прогрессии, по которым можно создать пронумерованную таблицу:

  • арифметическая последовательность. Этот вариант подразумевает порядковые числа, например, «10 11 12 13» и т. д.;
  • геометрическая. С ее помощью создается последовательность посредством умножения каждого предыдущего значения на определенное число. Например, шаг, равный цифре «3», будет создавать ряд «1 3 9 27» и т. д.;
  • даты. Удобная функция для нумерации строк и столбцов по дням, месяцам и годам.
  • автозаполнение. В этом случае вы вручную задаете определенную последовательность, которую программа продолжает по аналогии.

Используем формулы

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

  1. Активируйте то поле, где будет продолжаться список с определенным шагом.

  1. Здесь необходимо задать формулу. Поставьте знак «=», затем кликните мышкой на первую клетку, чтобы сделать ссылку. Теперь укажите шаг, например, «+5» или «-2» и т. д. Нажмите Enter для завершения.

  1. Теперь выделите все ячейки от первой пустой до введенной формулы. С помощью маркера в правом нижнем углу (без зажатой Ctrl ) протяните список вниз.

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

  1. В разделе «Вставить» установите пункт «Значения» и нажмите «ОК».

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

Заключение

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

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

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