Как в excel сделать автоматическую нумерацию строк при удалении?

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

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

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

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

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

Обратите внимание на то, что очень важно, чтобы у вас было несколько исходных ячеек. При этом там должны содержаться разные значения.

Попробуйте в качестве примера вставить единицу. Затем продублировать содержимое этой ячейки в остальные строки.

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

Сквозной метод

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

Настроить подобную подстановку очень просто. Для этого нужно сделать следующие операции:

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

Если в диапазоне выделения будет отсутствовать последний пустой элемент, то у вас ничего не получится. Будет просто каша.

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

  ПОИСКПОЗ в Excel

1. Результат автозаполнения будет следующим.

В дальнейшем эти пустые строки можно будет скрыть при помощи фильтрации.

При помощи функции

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

1. Выбираем нужную нам ячейку.
2. Переходим в строку ввода формул.
3. Указываем следующее выражение.

=СТРОКА(A1)

1. Нажимаем на клавишу Enter.

1. Результатом выполнения данной функции будет следующее.

1. Дублируем формулу на остальные строки вашего листа.

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

Более подробно об этой функции можно почитать на официальном сайте компании Microsoft.

При помощи прогрессии

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

1. Вводим какое-нибудь стартовое значение (любое – хоть нуль).
2. Открываем вкладку «Главная».
3. Нажимаем на инструмент «Заполнить».
4. Выбираем пункт «Прогрессия».

1. Затем нужно будет настроить расположение. Выберите вариант «По столбцам».
2. Далее настраиваем тип прогрессии. Поскольку нас интересуют только цифры, нужно будет оставить вариант «Арифметическая».
3. После этого необходимо указать шаг увеличения. Оставим значение «1».
4. Так же нас попросят ввести конечное значение нашего ряда. В качестве примера напишем число «10».
5. Для запуска построения прогрессии нужно нажать на кнопку «OK».

1. В итоге вы увидите, что на странице появились числа от вашего начального значения до десяти.
2. Если в настройках указать горизонтальный формат ряда, то вы получите следующий результат.

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

Автоматический режим

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

1. Укажите стартовое значение. Например, мы введем число «11».
2. Затем выделите нужный диапазон ячеек, в которые вы хотите вставить прогрессию.
3. Откройте на панели инструментов вкладку «Главная».
4. Воспользуйтесь инструментом «Заполнить».
5. Выберите пункт «Прогрессия».

  Функция ЕСЛИ с несколькими условиями в Эксель

1. Ничего не меняйте. Все настройки указаны автоматически. Именно для этого мы вначале выделили блок из нескольких элементов.

1. В итоге вы увидите, что все ячейки заполнились числами, начиная с «11». И ничего за границы выделения не вылезло. Самое главное – мы не указывали конечное значение. Это очень удобно.

Небольшое отличие в версиях Excel

Всё, что было сказано выше, используется в современных редакторах Эксель:

Но если у вас установлена старая программа от 2003 года, то инструмент «Прогрессия» вы сможете найти совсем в другом меню. Но добавление происходит точно по такому же принципу.

1. Кликаем на какое-нибудь число.
2. Переходим в меню «Правка».
3. Нажимаем на пункт «Выполнить», а затем в дополнительном меню выбираем нужный нам инструмент.

1. Окно настройки выглядит практически одинаково.

Заключение

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

При этом не нужно использовать дополнительные VBA-макросы или что-то в этом роде. Но если вдруг у вас возникли какие-нибудь трудности, возможно, вы неправильно выделяете диапазон ячеек.

Или же некорректно настроили прогрессию.

Видеоинструкция

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

Источник: https://os-helper.ru/excel/avtomaticheskaya-numeraciya-strok.html

Способы автоматической нумерация строк в Excel

Используем различные подходы для нумерации строк.

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

Пусть в ячейке A2введено значение 1(номер первой строки). В следующую строку таблицы, в ячейку А3, вводим формулу =А2+1и копируем ее в ячейки ниже.

Так как в формуле мы сослались на ячейку выше с помощью относительной ссылки, то EXCEL при копировании вниз модифицирует вышеуказанную формулу в =А3+1, затем в =А4+1и т.д., тем самым формируя числовую последовательность 2, 3, 4, …

Другим вариантом является использование формулы =СТРОКА()-СТРОКА($A$1). В ячейке А1должен находиться заголовок столбца.

Для чрезстрочной нумерации используйте формулу (см. файл примера) =ЕСЛИ(ЕЧЁТН(СТРОКА());»»;A7+1)

Используем клавишу CTRL

Пусть, как и в предыдущем примере, в ячейку A2введено значение 1.

Выделим ячейку A2. Удерживая клавишу CTRL, скопируем Маркером заполнения(при этом над курсором появится маленький плюсик), значение из A2в ячейки ниже. Получим последовательность чисел 1, 2, 3, 4 …

ВНИМАНИЕ!Если на листе часть строк скрыта с помощью фильтра, то этот подход и остальные, приведенные ниже, работать не будут. Чтобы разрешить нумерацию строк с использованием клавиши CTRL, выделите любую ячейку с заголовком фильтраи дважды нажмите CTRL+SHIFT+L(сбросьте фильтр).

Используем начало последовательности

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

пусть в ячейке А2введено значение 1, а в ячейке А3значение 2;выделяем ячейки A2и A3;беремся за правый нижний угол и Маркером заполненияпротягиваем вниз.

Получаем результат как в предыдущем случае.

Если в ячейке А3введено значение3, т. е. задан шаг последовательности равный 2, то мы получим последовательность нечетных чисел.

Использование инструмента Прогрессия

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

вводим в ячейку А2значение 1;выделяем диапазон A2:А6, в котором будут содержаться номера строк;вызываем инструмент Прогрессия(), в появившемся окне нажимаем ОК.

• СОВЕТ: О специальных случаях нумерации строк можно почитать в статьях Автоматическая перенумерация строк при применении фильтра, Автоматическая нумерация при добавлении строк, Сохранение нумерации при удалении строк, Нумерация строк и столбцов римскими цифрамии Нумерация столбцов буквами.
• Источники:

Источник: https://lean-center.ru/obuchenie/sposoby-avtomaticheskoj-numeracija-strok-v-excel/

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

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

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

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

Рис. 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] Зачем это сделано из дальнейшего текста Джелена не понятно – Прим. Багузина

Источник: http://baguzin.ru/wp/avtonumeratsiya-strok-i-stolbtsov-v-baze/

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

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

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

Если быть точнее, то в статье мы поговорим о том, как в «Экселе» автоматически пронумеровать строки тремя различными способами.

Первый способ: метод растягивания

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

1. В таблице вам необходимо пронумеровать две первые строки, например, вписав в них номера 1 и 2.

2. Затем нужно выделить эти две ячейки, зажав левую кнопку мыши на первой и перетащив курсор на вторую.

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

4. Зажав левую кнопку мыши, остается лишь протащить выделения до той ячейки, в которой нумерация должна оканчиваться.

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

Второй способ: использование функции

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

Итак, говоря о том, как в «Экселе» пронумеровать сроки с помощью функции, вам необходимо выполнить следующее:

1. Установить курсор в ту клеточку, с которой должна начинаться нумерация строк.

2. Дважды кликнуть по ячейке.

3. Вписать следующее выражение: «=СТРОКА(A1)».

4. Нажать ENTER.

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

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

Третий способ: применение прогрессии

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

Итак, для этого вам понадобится:

1. Выделить первую ячейку и ввести туда цифру 1.

2. Нажать на кнопку «Заполнить», что находится на панели инструментов во вкладке «Главная».

3. Из списка, который появился, выбрать пункт «Прогрессия».

4. В появившемся окошке вам необходимо задать все нужные параметры. Расположение ставьте «по столбцам», тип выбирайте «арифметическая», шаг — «1», а предельное значение укажите равное строкам, которые нужно пронумеровать.

5. Нажмите «ОК».

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

Заключение

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

Источник: https://autogear.ru/article/346/686/kak-v-eksele-pronumerovat-stroki-avtomaticheski/

Автоматическая нумерация строк Excel с помощью функции

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

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

Итак, речь идет о функции =СТРОКА(), которую можно применить для отображения номера строки. Как это удобнее сделать?

Вообще, тут есть 2 варианта.

1. Как и любая нормальная функция Excel :-), эта может иметь в качестве аргумента адрес ячейки. При этом она возвращает в ячейку, в которой установлена, результат — номер строки, на которой находится ячейка. Например, функция =СТРОКА(D9) вернет значение 9 в ту ячейку,  которой она находится. Однако, никто не запрещает её ставить в ту же ячейку, адрес которой указывается в ней как аргумент. Поэтому, поместив формулу =СТРОКА(В6) в ячейку В6, мы получем в ней цифру 6.
2. Это вариант, когда аргумент функции вообще можно не указывать (но вы же помните, что скобки при этом обязательны?). В этом случае функция возвращает номер той строки, в которой она стоит. Тут очень просто, поставили функцию =СТРОКА() в ячейку А5, и получили в этой ячейке число 5.

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

1. Записываем в ячейку А3 функцию =СТРОКА();
2. Захватываем маркер заполнения мыщью (это квадратик в правом нижнем углу прямоугольного курсора в таблице Excel);
3. Протаскиваем за маркер прямоугольный курсор вниз по столбцу до ячейки А25 (на которой заканчивается столбец).

А вот тут интересно! Оно то конечно пронумеровалось, но так как А3 — это начало нашей таблицы, т.е. первая её строка, то увидеть там нумерацию начиная с числа «3» как-то не очень хорошо. Что делать? А немножко подумать. Всего-то и делов — внести поправку на сдвиг.

Так как наша таблица располагается ниже основной нумерации строк на две строки, то нам нужно записать в ячейку: =СТРОКА()-2. В этом случае мы получаем в ячейке А3 значение, равное «1».

Теперь можно распространять эту формулу на весь наш столбец и всё будет хорошо пронумеровано.

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

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

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

Источник: http://machula.ru/avtomaticheskaya-numeratsiya-strok-excel-s-pomoshhyu-funktsii/