Автоматическая нумерация строк в excel с пропуском строк
"Опыт - это то, что получаешь, не получив того, что хотел"
Дата добавления - 15.01.2015 в 10:50 VEKTORVSFREEMAN, О супер. спасибо!
vikttur, и Вам тоже огромное спасибо! VEKTORVSFREEMAN, О супер. спасибо!
vikttur, и Вам тоже огромное спасибо! irinko irinko, не за что. Но в первую очередь Вам благодарить Уважаемого vikttur, его отличная идея, а моя малюсенькая доработка (ответ на Ваш вопрос). irinko, не за что. Но в первую очередь Вам благодарить Уважаемого vikttur, его отличная идея, а моя малюсенькая доработка (ответ на Ваш вопрос). VEKTORVSFREEMAN Я очень извиняюсь, но у меня возникла другая проблема. сама нумерация начинается не с первой строки, а с 10 к примеру. если использовать эту формулу, то номер 2 ставить через 10 строк, а номер 3 и последующие уже правильно через 20.. пыталась изменить формулу всяко разно, но что то не получается ни как.. Я очень извиняюсь, но у меня возникла другая проблема. сама нумерация начинается не с первой строки, а с 10 к примеру. если использовать эту формулу, то номер 2 ставить через 10 строк, а номер 3 и последующие уже правильно через 20.. пыталась изменить формулу всяко разно, но что то не получается ни как.. irinko В первой ячейке с формулой: СТРОКА(A1) (или другой нужный Вам номер строки) В первой ячейке с формулой: СТРОКА(A1) (или другой нужный Вам номер строки) vikttur
в ячейке А10 появляется первая нумерация - 1
затем в Ячейке А11 ставлю вашу формулу
в строке А21 появляется номер 2, а мне нужно в строке А30 (А10+20)
последующие номера появляются верно в строке А41, А61, А81 и т.д.
в ячейке А10 появляется первая нумерация - 1
затем в Ячейке А11 ставлю вашу формулу
в строке А21 появляется номер 2, а мне нужно в строке А30 (А10+20)
последующие номера появляются верно в строке А41, А61, А81 и т.д. irinko
в ячейке А10 появляется первая нумерация - 1
затем в Ячейке А11 ставлю вашу формулу
в строке А21 появляется номер 2, а мне нужно в строке А30 (А10+20)
последующие номера появляются верно в строке А41, А61, А81 и т.д. Автор - irinko
Дата добавления - 15.01.2015 в 12:13
Всем доброго времени суток. Очень частая проблема, которая встречается пользователям таблиц Excel, это пронумеровать строки в создаваемой таблице. Кто как только не ухищряется это делать.
Основных способов несколько. Я перечислю их по частоте использования:
1. Протянуть мышью, с нажатой левой кнопкой и протягивая за правый нижний угол первой ячейки до самого низа таблицы.
Способ протягивания мышью от первой ячейки до самой последней Способ протягивания мышью от первой ячейки до самой последнейСпособ интуитивно понятен даже для начинающих пользователей, поэтому он самый часто встречающийся способ.
Однако в этом простейшем способе есть ряд недостатков:
1. Если в таблице встречается специально предусмотренный разрыв строки, он все равно заполняется следующим числом. Если конечно, пользователь вовремя не заметит этого и не прекратит протягивать нумерацию строк дальше. После пропуска разрыва, приходится переходить на нужную строку, вводить следующий номер строки и продолжить протягивать нумерацию вниз
2. Если в таблице опять встречается разрыв строк, но в отличие от первого недостатка, этот разрыв строки состоит из объединенных ячеек
Перед пользователем появляется окно с ошибкой и требует чтобы в таблице все ячейки имели одинаковый размер Перед пользователем появляется окно с ошибкой и требует чтобы в таблице все ячейки имели одинаковый размер2. Способ довольно редкий, хотя его описание можно встретить на просторах интернета. Назовем его "по двойному щелчку мыши" по нижнему правому углу ячейки, с которой нужно начать нумерацию.
Нужно начать нумерацию хотя бы в первых двух ячейках, выделить их и навести на нижний правый угол выделенных ячеек. В этом месте покажется черный крестик, на который нужно дважды нажать мышью Нужно начать нумерацию хотя бы в первых двух ячейках, выделить их и навести на нижний правый угол выделенных ячеек. В этом месте покажется черный крестик, на который нужно дважды нажать мышьюСпособ кардинально отличается от первого способа и позволяет довольно быстро произвести операцию по нумерации строк, т.к. не нужно мышью протягивать до самого конца таблицы (сомнительное удовольствие, если в таблице несколько тысяч строк!)
У этого способа нет недостатков, которые есть в первом способе. Нумерация просто прекращается когда достигается первая пустая или объединенная ячейка. Нужно просто найти это место, перейти на следующую строку, которую нужно пронумеровать, вставить в следующие строки следующие значения номеров строк и повторить действие предыдущего абзаца.
Вывод: способ достойный внимания. И его, в принципе, должен знать любой уважающий свое время и нервы сотрудник, который в своей работе использует программу Excel.
3. Способ самый продвинутый и предполагает использование кода VBA.
В этом способе напрочь отсутствуют недостатки первого и второго способа. Программа просто нумерует строки от первой до последней ячейки. Единственное, что нужно сделать, это выделить все строки с места, от которого нужно пронумеровать. ОПЯТЬ ВЫДЕЛИТЬ. Чем это отличается от первого способа? Спросите вы.
Но ничего страшного, способов выделить таблицу до конца тоже несколько. Первый - мышью. Иногда долго, но действенно. Второй это нажав комбинацию клавиш ctrl+shift+End.
С помощью кода VBA можно предусмотреть наличие в выделенной таблице пустых ячеек, в ходе нумерации пропустить их и продолжить со следующей строки, где необходимо продолжить нумерацию.
Быстро пронумеровать список фамилий, расставить людей по рейтингу и присвоить участникам викторины порядковый номер — задачи, которые можно автоматизировать. Зачем проставлять номера каждому участнику списка отдельно, если можно воспользоваться силой Excel и сделать всё быстро, и без мучений.
Способ 1 — простая нумерация
Ставим в первую ячейку цифру 1, во вторую — цифру 2. Затем выделяем обе ячейки и тянем вниз, чтобы заполнить всё до конца.
Если строк много, можно воспользоваться быстрым способом. Наводим мышку на правый нижний угол ячейки и делаем двойной клик по крестику — это сразу заполнит весь диапазон до последней строки.
Как это работает. Так как в первую ячейку мы поставили единицу, а во вторую — двойку, Excel счёл это как последовательность. Скорее всего, следующее число будет 3, за ним 4, а потом 5 и так далее.
Ежедневные советы от диджитал-наставника Checkroi прямо в твоем телеграме!Способ 2 — автоматическая последовательная нумерация
Здесь мы уже используем формулу, которая будет увеличивать каждую предыдущую ячейку на единицу. Этот способ удобен тем, что такая формула помогает быстро расширять список. Итак, в первую ячейку ставим 1, а во вторую вписываем формулу:
Далее точно так же тянем всё вниз и заполняем диапазон.
Этот способ прост в использовании, потому что можно не выделять несколько ячеек, а просто протянуть формулу вниз и быстро заполнить все нужные значения.
Как это работает. Наша формула образно звучит так: «Возьми ячейку над текущей и увеличь результат на единицу». А так как формула тянется вниз, то всё значение каждой ячейки увеличивается на один — и происходит автоматическая нумерация.
Способ 3 — через функцию =СТРОКА()
Если ваш список увеличивается строго вниз, то способ через функцию СТРОКА будет оптимальным.
Функция =СТРОКА() возвращает порядковый номер строки для текущей ячейки, начиная со строки №1
Зная это, мы можем найти закономерность и здесь. Введём формулу в ячейку E2:
Как видите, результат работы функции — это число 2. Строка вторая, значит в результате будет 2.
Чтобы нам получить правильный результат, нужно подкорректировать формулу и уменьшить результат на количество строк шапки, то есть на одну строку. Для этого просто вычитаем единицу из самой функции.
Способ удобен тем, что если нам нужно пронумеровать каждый элемент списка отдельно. Мы можем скопировать формулу в любое место таблицы и получить мгновенный результат.
Как это работает. Механика функции простыми словами: «у ячейки с таким адресом вот такой номер строки». И нам, чтобы начать список с первого пункта, нужно этот номер строки уменьшить на количество строк шапки нашей таблицы. Так мы и получаем желаемый результат.
Способ 4 — нумерация через инструмент «Прогрессия»
Нумеровать можно не только формулами и арифметическими действиями, но и встроенными инструментами. Нам нужен инструмент «Прогрессия».
Арифметическая прогрессия — это последовательность чисел, где каждое следующее число больше предыдущего на какой-то шаг
В нашем случае прогрессия самая простая — мы увеличиваем каждое число на единицу, прямо как в способе 2, но только без лишних действий руками.
Вводим в ячейку E1 число 1, выделяем эту единицу и весь диапазон, который нужно пронумеровать, а затем переходим на вкладку «Главная» → область «Редактирование» → кнопка «Заполнить» → «Прогрессия…»
Открывается диалоговое окно, в котором мы выбираем расположение «По столбцам», поскольку нумеруем вниз. Затем выбираем тип «Арифметическая» и ставим шаг — 1.
Если мы знаем, сколько всего людей в списке, то мы можем поставить предельное значение, например, 25, и тогда инструмент остановит свою работу, как только дойдёт до определённого числа. Нажимаем «Ок» и видим как Excel делает автоматическую нумерацию строк.
Как это работает. Прогрессии могут быть разными, но в случае с арифметическими — они ведут себя достаточно предсказуемо, просто увеличивая наше число на какое-то одинаковое число — шаг. В нашем случае шаг равняется единице, что полностью решает нашу задачу.
Способ 5 — функция МАКС()
Иногда бывает, что список людей идёт с разрывами и нам нужно продолжать нумерацию после пропуска. В этом случае нам подойдёт способ через функцию МАКС и работает он следующим образом.
Функция МАКС ищет наибольшее число в указанном диапазоне
В первую ячейку ставим число 1, а во вторую вводим формулу: =МАКС($E$2:E2)+1
Начало диапазона мы указали строго от $E$2, то есть первая граница не меняется ни при каких условиях. Концом диапазона будет последняя ячейка после протягивания. При протягивании такой формуле в строке 26 мы получим формулу: =МАКС($E$2:E25)+1
А там где нам нужно пропустить значения автоматической нумерации, мы просто удаляем формулу из ячейки и всё, что идёт ниже, автоматически пересчитывается.
При этом формулу можно просто скопировать и вставить в любом месте сверху и снизу нашего списка нумерации и все числа автоматически пересчитаются.
Как это работает. Так как диапазон увеличивается на единицу =МАКС() + 1, то у нас каждая ячейка будет больше другой и функция МАКС будет выбирать её.
Способ 6 — адаптивная автоматическая нумерация через =СЧЁТЗ() и =ЕСЛИ()
Если в предыдущем способе нам всё ещё нужно работать с ячейками вручную, то в этом способе мы всё автоматизируем ещё больше. Нам понадобится комбинация функций СЧЁТЗ и ЕСЛИ, чтобы фильтрация происходила по условию.
Функция СЧЁТЗ считает количество заполненных ячеек в диапазоне
Функция ЕСЛИ проверяет ячейку на соответствие какому-то условию. Если условие истинно, выполняется одно действие. Если условие ложно, выполняется другое действие
Итак, формула: =ЕСЛИ(B2=»»;»»;СЧЁТЗ($B$2:B2))
Вставляем формулу в ячейку B2, тянем вниз и формула автоматически пропускает пустые строки. Заметьте, что в части формулы, где СЧЁТЗ, мы закрепили первую часть, потому что мы не хотим, чтобы диапазон состоял из одной строки. Нам нужно, чтобы он постоянно увеличивался.
Ещё важно обратить внимание, что две двойные кавычки «» рядом означают, что ячейка должна быть пустой, то есть не содержать в себе никаких данных.
Как это работает. Функция ЕСЛИ отвечает за такое условие: если ячейка B2, где у нас лежит фамилия, пустая, то так и оставить её пустой, иначе — посчитать общее количество заполненных ячеек.
Мы разобрались, как создать автоматическую нумерацию в Excel. Эти же способы работают и в гугл-таблицах. Желаем приятного применения! Пробуйте и делитесь впечатлениями.
Приходите на курсы по обучению работе с Excel-таблицами — они помогут освойте таблицы, даже если сейчас знаний ноль.
Предположим, у вас есть диапазон данных фильтра, который вы хотите заполнить порядковыми номерами в отфильтрованной пустой ячейке, как показано на следующем снимке экрана. В этом случае перетаскивание маркера заполнения не приведет к правильному заполнению ячеек в целом, в этой статье я расскажу о некоторых хороших приемах для решения этой задачи в Excel.
 |  |  |
Заполните порядковые номера, пропустите скрытые строки с помощью формулы
Чтобы заполнить порядковые номера только видимыми ячейками, вам может помочь простая формула. Сделайте следующее:
1. Введите эту формулу: = ПРОМЕЖУТОЧНЫЙ ИТОГ (103; B $ 3: B3) (B3 - это видимая ячейка, соответствующая ячейке, с которой вы хотите начать заполнение порядкового номера) в первую ячейку, в которую вы хотите заполнить порядковые номера, A3, для этого экземпляра, а затем нажмите Enter key вы получите цифру 1, см. снимок экрана:
2. Затем выберите ячейку A3 и перетащите маркер заполнения вниз к ячейкам, которые вы хотите заполнить порядковыми номерами, см. Снимок экрана:
Заполните порядковые номера, пропустите скрытые строки с кодом VBA
Следующий код VBA также может помочь вам заполнить порядковые номера только видимыми ячейками, пожалуйста, сделайте следующее:
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модульи вставьте следующий код в Модуль Окно.
Код VBA: заполните порядковые номера, пропустите скрытые строки:
3, Затем нажмите F5 нажмите клавишу для запуска этого кода, и появится диалоговое окно, напоминающее вам о выборе диапазона данных, который вы хотите заполнить, см. снимок экрана:
4, Затем нажмите OK кнопки, порядковые номера были заполнены только видимыми ячейками.
Заполните порядковые номера, пропустите скрытые строки с помощью Kutools for Excel
Только два вышеуказанных метода могут помочь вам заполнить порядковые номера в видимых ячейках, если вы хотите заполнить последовательность как AA-0001-BB, AA-0002-BB, AA-0003-BB… , вышеуказанные методы не будут работать. Но, пожалуйста, не беспокойтесь о том, Kutools for ExcelАвтора Вставить порядковый номер Утилита поможет вам закончить это быстро и легко.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Выделите ячейки, которые вы хотите заполнить строками последовательности.
2. Нажмите Kutools > Вставить > Вставить порядковый номер, В Вставить порядковый номер диалоговое окно, нажмите НОВИНКИ кнопку см. скриншоты:
 | |  |
3. В развернутом диалоговом окне укажите необходимые настройки в левом нижнем разделе, в то же время вы можете предварительно просмотреть последовательность в правой нижней панели, см. Снимок экрана:
4. После завершения настройки нажмите Добавить кнопку, чтобы сохранить этот новый элемент в поле списка выше, см. снимок экрана:
6. Затем выберите созданный элемент и нажмите Диапазон заполнения , строки последовательности были заполнены только видимыми ячейками, см. снимок экрана:
Предположим, я пронумеровал строки от 1 до 20 в столбце, теперь, когда я вставляю или удаляю строку из диапазона данных, я хочу, чтобы список номеров можно было перенумеровать автоматически. Можно ли выполнить эту задачу в Excel?
Нумеровать строки после автоматической вставки или удаления строк с помощью формул
Фактически, функция Row в Excel может помочь вам автоматически перенумеровать строки после вставки или удаления строк, пожалуйста, сделайте следующее:
1. Введите эту формулу: = СТРОКА () - 1 в ячейку A2, а затем перетащите маркер заполнения вниз к ячейкам, в которых вы хотите пронумеровать строки, см. снимок экрана:
Внимание: Если ваш список номеров начинается с первой ячейки, вы можете изменить формулу как = СТРОКА () , вы можете прибавить или вычесть из формулы, если ваши числа не совпадают с фактической строкой, например, = СТРОКА () - 3 , = СТРОКА () + 10 .
2. Затем создайте таблицу для этого диапазона данных, выберите диапазон данных и щелкните Вставить > Настольные, а затем в Создать таблицу диалоговое окно, отметьте В моей таблице есть заголовки если в вашем диапазоне есть заголовки, см. снимок экрана:
3, Затем нажмите OK, таблица была создана сразу, и теперь, когда вы вставляете или удаляете строки в диапазоне таблицы, номера строк всегда обновляются автоматически, см. снимок экрана:
Нумеровать строки после автоматической вставки или удаления строк с помощью кода VBA
За исключением вышеуказанного метода формулы, вы также можете завершить его кодом VBA.
1. Перейдите к листу, в который вы хотите автоматически вставить порядковые номера.
2. Щелкните правой кнопкой мыши вкладку листа и выберите Просмотреть код из контекстного меню во всплывающем Microsoft Visual Basic для приложений окна, скопируйте и вставьте следующий код в пустое окно модуля.
Код VBA: автоматическая нумерация строк после вставки или удаления строк:
3. Затем сохраните и закройте этот код, когда вы вводите первую цифру 1 в ячейку A1, порядковые номера от 1 до 20 будут заполнены автоматически. Если вы вставляете или удаляете строки из диапазона, номер серии сразу будет автоматически перенумерован.
Внимание: В приведенном выше коде буква A указывает букву столбца, которую вы хотите заполнить порядковым номером, 1 К 20 это строки, которые вы хотите пронумеровать, и A21 это ссылка на ячейку рядом с вашей последней пронумерованной ячейкой. Вы можете менять их по своему усмотрению.
Вставить маркеры или список нумерации в столбец ячеек
Kutools for Excel's Вставить пулю и яnsert нумерация функции могут помочь вам быстро вставить маркированный и нумерационный список в столбец ячеек. Нажмите, чтобы загрузить Kutools for Excel!
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Читайте также: