Excel изменить нумерацию ячеек в Excel

Обновлено: 24.11.2024

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

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

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

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

Даты: если вы вводите дату (даты тоже являются значениями), которая следует за одним из встроенных числовых форматов Excel, например, 10.11.09 или 10-ноября-09, программа присваивает формат даты. который соответствует образцу даты.

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

Выберите ячейки, содержащие числа, которые вы хотите отформатировать.

Выберите нужный числовой формат одним из следующих способов:

Выберите формат из раскрывающегося списка в группе «Число» на вкладке «Главная».

Нажмите кнопку запуска диалогового окна «Число» в правом нижнем углу группы «Число» на вкладке «Главная» и выберите нужный формат на вкладке «Число» диалогового окна «Формат ячеек».

Используйте сочетание клавиш Ctrl+1, чтобы открыть диалоговое окно «Формат ячеек». Просто имейте в виду, что сочетание клавиш — это нажатие клавиши Ctrl и клавиши цифры 1, а не функциональной клавиши F1.

Об этой статье

Эта статья взята из книги:

Об авторе книги:

Грег Харви, доктор философии, президент Mind Over Media, Inc. Он является автором всех выпусков книг Excel для чайников, Excel All-in-One для чайников, Excel Workbook для чайников, и Краткий справочник по Windows для чайников. Он также является опытным педагогом.

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

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

Что вы хотите сделать?

Заполните столбец рядом чисел

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

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

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

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

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

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

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

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

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

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

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

Используйте функцию ROW для нумерации строк

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

<р>1. Выберите пустую ячейку (ячейка C1), введите формулу =ЗАМЕНИТЬ(A1,1,1,"") в строку формул и нажмите клавишу Enter.

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

<р>2. Продолжайте выбирать ячейку C1, перетащите маркер заполнения в диапазон, который нужно покрыть этой формулой. Затем вы увидите, что все первые числа в указанных ячейках немедленно удаляются.

Изменить или найти и заменить первое число ничем с помощью Kutools for Excel

Утилита «Удалить по положению» в Kutools for Excel может легко удалить только первые числа выбранных ячеек. Пожалуйста, сделайте следующее.

Перед применением Kutools for Excel сначала загрузите и установите его.

<р>1. Выберите диапазон с ячейками, в которых вам нужно удалить только первое число, затем нажмите Kutools > Текст > Удалить по положению. Смотрите скриншот:

<р>2. В диалоговом окне «Удалить по положению» введите число 1 в поле «Числа», выберите «Слева» в разделе «Положение» и, наконец, нажмите кнопку «ОК».

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

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

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

Как показано на снимке экрана ниже, вам нужно заменить все первые числа 6 на число 7 в списке, сделайте следующее.

<р>1. Выберите пустую ячейку, введите формулу =ПОДСТАВИТЬ(A1,"6","7",1) в строку формул, а затем нажмите клавишу Enter.

Примечания:

<р>1). В формуле цифра 6 – это первое число ячеек, на которые ссылаются, а цифра 7 означает, что вам нужно заменить первую цифру 6 на 7, а цифра 1 означает, что будет изменена только первая цифра.

<р>2. Продолжайте выбирать ячейку результата, затем перетащите маркер заполнения в нужный диапазон. Теперь все первые ячейки в выбранном диапазоне заменяются одним и тем же значением. Смотрите скриншот:

<р>1. Выберите диапазон и нажмите Kutools > Текст > Удалить по положению.

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

<р>3. Продолжайте выбирать эти ячейки, а затем нажмите Kutools > Текст > Добавить текст. В диалоговом окне «Добавить текст» введите число, которое нужно добавить в начало содержимого ячейки, выберите «Перед первым символом» и, наконец, нажмите кнопку «ОК». Смотрите скриншот:

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

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

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

Теперь, если вы думаете, что один из способов — просто ввести эти серийные номера вручную, что ж, вы правы!

Но это не лучший способ.

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

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

Конечно, их будет больше, и я буду ждать — с кофе — в области комментариев, чтобы услышать от вас об этом.

Это руководство охватывает:

Как пронумеровать строки в Excel

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

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

Вы можете выбрать любой из методов, которые работают на основе вашего набора данных.

1] Использование маркера заполнения

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

Предположим, у вас есть набор данных, как показано ниже:

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

Обратите внимание, что дескриптор заполнения автоматически определяет шаблон и заполняет оставшиеся ячейки этим шаблоном. В этом случае закономерность заключалась в том, что числа увеличивались на 1.

Если в наборе данных есть пустая строка, дескриптор заполнения будет работать только до последней непрерывной непустой строки.

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

2] Использование серий заполнения

Хотя дескриптор заполнения — это быстрый способ нумерации строк в Excel, функция "Заполнить ряд" дает гораздо больше контроля над вводом чисел.

Предположим, у вас есть набор данных, как показано ниже:

Вот как использовать Fill Series для нумерации строк в Excel:

Это мгновенно пронумерует строки от 1 до 26.

Использование «Заполнить серию» может быть полезно, если вы начинаете с ввода номеров строк. В отличие от указателя заполнения, он не требует, чтобы соседние столбцы уже были заполнены.

Даже если на листе ничего нет, функция «Заполнить серию» все равно будет работать.

Примечание. Если в середине набора данных есть пустые строки, функция "Заполнить ряд" все равно заполнит номер этой строки.

3] Использование функции ROW

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

В приведенных выше методах Fill Handle и Fill Series вставляемый серийный номер является статическим значением. Это означает, что если вы переместите строку (или вырежете и вставите ее в другое место в наборе данных), нумерация строк не изменится соответствующим образом.

Этот недостаток можно устранить с помощью формул в Excel.

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

Чтобы получить нумерацию строк с помощью функции СТРОКА, введите следующую формулу в первую ячейку и скопируйте ее для всех остальных ячеек:

Функция ROW() возвращает номер текущей строки. Поэтому я вычел из него 1, поскольку начал со второй строки и далее. Если ваши данные начинаются с 5-й строки, вам нужно использовать формулу =СТРОКА()-4.

Лучшее в использовании функции ROW то, что она не испортит нумерацию, если вы удалите строку в своем наборе данных.

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

Обратите внимание, что как только я удаляю строку, номера строк автоматически обновляются.

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

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

4] Использование функции COUNTA

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

Он использует функцию COUNTA, которая подсчитывает количество непустых ячеек в диапазоне.

Предположим, у вас есть набор данных, как показано ниже:

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

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

Функция ЕСЛИ проверяет, пуста ли соседняя ячейка в столбце B. Если он пуст, он возвращает пустое значение, но если это не так, он возвращает количество всех заполненных ячеек до этой ячейки.

5] Использование ПРОМЕЖУТОЧНЫХ ИТОГОВ для отфильтрованных данных

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

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

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

Позвольте мне показать вам, как это работает, на примере.

Предположим, у вас есть набор данных, как показано ниже:

Если я отфильтрую эти данные на основе продаж Продукта А, вы получите нечто, показанное ниже:

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

Хотя это ожидаемое поведение, если вы хотите получить порядковую нумерацию строк, чтобы можно было просто скопировать и вставить эти данные куда-нибудь еще, вы можете использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

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

3 в функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ указывает на использование функции СЧЁТЗ. Второй аргумент — это диапазон, к которому применяется функция COUNTA.

Преимущество функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ заключается в том, что она динамически обновляется при фильтрации данных (как показано ниже):

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

6] Создание таблицы Excel

Таблица Excel — отличный инструмент, который необходимо использовать при работе с табличными данными. Это значительно упрощает управление данными и их использование.

Это также мой любимый метод среди всех методов, показанных в этом руководстве.

Позвольте сначала показать вам, как правильно нумеровать строки с помощью таблицы Excel:

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

Есть некоторые дополнительные преимущества использования таблицы Excel при нумерации строк в Excel:

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

7] Добавление 1 к номеру предыдущей строки

Это простой метод, который работает.

Идея состоит в том, чтобы добавить 1 к номеру предыдущей строки (число в ячейке выше). Это гарантирует, что последующие строки получат число, увеличенное на 1.

Предположим, у вас есть набор данных, как показано ниже:

Вот шаги для ввода номеров строк с помощью этого метода:

В ячейке в первой строке введите 1 вручную. В данном случае это ячейка A2.
В ячейку A3 введите формулу =A2+1
Скопируйте и вставьте формулу во все ячейки столбца.

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

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

Вот несколько быстрых способов вставки серийных номеров в табличные данные в Excel.

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

Читайте также: