Как сделать чередование цифр в экселе

Обновлено: 02.07.2024

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

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

Чередуем цвет строк в Excel

Когда в Excel требуется выделить цветом каждую вторую строку, большинство специалистов тут же вспоминают об условном форматировании и, поколдовав некоторое время над созданием замысловатого сочетания функций ОСТАТ (MOD) и СТРОКА (ROW), достигают нужного результата.

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

Выделяем цветом каждую вторую строку при помощи стилей таблиц (Чередующиеся строки в Excel)

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

Выделите диапазон ячеек, в котором нужно настроить чередование цветов строк.
На вкладке Вставка (Insert) кликните Таблица (Table) или нажмите Ctrl+T.
Готово! Чётные и нечётные строки созданной таблицы раскрашены в разные цвета. И, что замечательно, автоматическое чередование цветов будет сохраняться при сортировке, удалении или добавлении новых строк в таблицу.

Если все преимущества таблицы не нужны, и достаточно оставить только чередующуюся окраску строк, то таблица легко преобразуется обратно в обычный диапазон. Для этого кликните правой кнопкой по любой ячейке таблицы и в контекстном меню нажмите Таблица > Преобразовать в диапазон (Table > Convert to Range).

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

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

Как выбрать собственные цвета для полос

Если стандартная сине-белая палитра таблицы Excel не вызывает у Вас восторга, то на выбор предлагается множество шаблонов и цветов. Просто выделите таблицу, или любую ячейку этой таблицы, а затем на вкладке Конструктор (Design) в разделе Стили таблиц (Table Styles) выберите подходящий цвет.

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

Как выделять различное количество строк в полосах таблицы

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

Откроем вкладку Конструктор (Design), кликнем правой кнопкой по понравившемуся стилю таблицы и в появившемся меню нажмём Дублировать (Duplicate).
В поле Имя (Name) введём подходящее имя для нового стиля таблицы.
Выберем элемент Первая полоса строк (First Row Stripe) и установим Размер полосы (Stripe Size) равный 2 или другому значению (по желанию).
Далее выберем элемент Вторая полоса строк (Second Row Stripe) и повторим процесс.
Жмём ОК, чтобы сохранить пользовательский стиль.
Выбираем только что созданный стиль в галерее Стили таблиц (Table Styles). Созданные стили находятся вверху галереи в разделе Пользовательские (Custom).

Замечание: Пользовательские стили таблиц хранятся только в текущей рабочей книге, т.е. в других книгах они доступны не будут. Чтобы использовать пользовательский стиль по умолчанию для всех создаваемых таблиц в текущей рабочей книге, при создании или изменении стиля в диалоговом окне Изменение стиля таблицы (Modify Table Style) отметьте галочкой параметр Назначить стилем таблицы по умолчанию для данного документа (Set as default table style for this document).

Если созданный стиль получился не совсем таким, как хотелось, его легко можно изменить. Для этого открываем галерею стилей, находим наш пользовательский стиль, кликаем по нему правой кнопкой мыши и в контекстном меню выбираем Изменить (Modify). Вот где нужно дать волю своему творческому мышлению! Нажимаем кнопку Формат (Format), как показано на рисунке ниже, и на вкладках Шрифт (Font), Граница (Border) и Заливка (Fill) открывшегося диалогового окна нам доступны любые настройки соответствующих параметров. Можно настроить даже градиентную заливку для чередующихся строк.

Удаляем чередование раскраски строк в Excel в один клик

Если чередование цветов в таблице Excel больше не требуется, удалить его можно буквально одним щелчком мыши. Выделите любую ячейку таблицы, откройте вкладку Конструктор (Design) и уберите галочку в строке параметра Чередующиеся строки (Banded rows).

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

Чередование окраски строк при помощи условного форматирования

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

Выделяем в Excel каждую вторую строку при помощи условного форматирования

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

Создадим правило условного форматирования вот таким образом:

Выделите ячейки, для которых нужно изменить цвет. Если раскрасить строки необходимо на всём листе, то нажмите на серый треугольник в левом верхнем углу листа – так лист будет выделен полностью.
На вкладке Главная (Home) в разделе Стили (Styles) нажмите кнопку Условное форматирование (Conditional Formatting) и в открывшемся меню выберите Создать правило (New Rule).
В диалоговом окне Создание правила форматирования (New Formatting Rule) выберите вариант Использовать формулу для определения форматируемых ячеек (Use formula to determine which cells to format) и введите такую формулу:

Вот что получилось у меня в Excel 2013:

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

Теперь чётные и нечётные строки выделены разными цветами:

Просто, правда? Сейчас я хочу кратко объяснить синтаксис функции ОСТАТ (MOD), поскольку далее мы будем использовать её в чуть более сложных примерах.

Функция ОСТАТ (MOD) – возвращает остаток от деления и имеет следующий синтаксис:

Например, результатом вычисления формулы

будет 0, т.к. 4 делится на 2 без остатка.

Теперь посмотрим подробнее, что именно делает созданная нами в предыдущем примере функция ОСТАТ (MOD). Мы использовали вот такую комбинацию функций ОСТАТ (MOD) и СТРОКА (ROW):

Синтаксис простой и бесхитростный: функция СТРОКА (ROW) возвращает номер строки, затем функция ОСТАТ (MOD) делит его на 2 и возвращает остаток от деления. В применении к нашей таблице формула возвращает вот такие результаты:

№ строки	Формула	Результат
Строка 2	=ОСТАТ(2;2) =MOD(2,2)	0
Строка 3	=ОСТАТ(3;2) =MOD(3,2)	1
Строка 4	=ОСТАТ(4;2) =MOD(4,2)	0
Строка 5	=ОСТАТ(5;2) =MOD(5,2)	1

Увидели закономерность? Для чётных строк результат всегда равен 0, а для нечётных – 1. Далее мы создаём правило условного форматирования, которое указывает Excel окрасить нечётные строки (результат равен 1) в один цвет, а чётные строки (результат равен 0) – в другой цвет.

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

Как настроить чередование групп строк различного цвета

Следующие формулы можно использовать для раскраски заданного числа строк, независимо от их содержимого:

Здесь НомерСтроки – это номер строки первой ячейки с данными, а N – количество строк в каждой раскрашенной группе.

Совет: Если нужно выделить и чётные, и нечётные группы строк, тогда придётся создать два правила условного форматирования – для каждой из показанных выше формул.

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

Раскрасить каждые 2 строки, начиная с 1 группы. Данные начинаются в строке 2.	=ОСТАТ(СТРОКА()-2;4)+1<=2 =MOD(ROW()-2,4)+1<=2
Раскрасить каждые 2 строки, начиная со 2 группы. Данные начинаются в строке 2.	=ОСТАТ(СТРОКА()-2;4)>=2 =MOD(ROW()-2,4)>=2
Раскрасить каждые 3 строки, начиная со 2 группы. Данные начинаются в строке 3.	=ОСТАТ(СТРОКА()-3;6)>=3 =MOD(ROW()-3,6)>=3

Как раскрасить строки тремя различными цветами

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

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

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

Как настроить чередование цветов строк, зависящее от содержащегося в них значения

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

Предположим, у нас есть таблица, в которой собраны данные из различных источников, например, отчёты о продажах из различных регионов. Мы хотим раскрасить первую группу строк, с данными о первом продукте (Product 1) в один цвет, группу строк с данными о втором продукте (Product 2) – во второй цвет, и так далее. Столбец A, в котором содержится список продуктов, мы можем использовать как ключевой столбец или столбец с уникальными идентификаторами.

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

С правой стороны таблицы добавим вспомогательный столбец, в нашем примере это будет столбец F. Позже мы сможем скрыть его.
В ячейку F2 введём следующую формулу (предполагается, что строка 2 – это первая строка с данными) и затем скопируем её во все ячейки столбца:

Формула заполнит столбец F последовательностью групп из 0 и 1, причём каждая новая группа начинается в той строке, в которой появляется новое наименование продукта.

Если хотите, чтобы в таблице вместо цветной и белой полосы чередовалось два цвета, как показано на рисунке ниже, то можете создать ещё одно правило:

Чередование цвета столбцов в Excel (Чередующиеся столбцы)

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

Два основных способа раскрасить столбцы в Excel, это:

Чередование расцветки столбцов в Excel при помощи стилей таблиц

Если желаете цвета посимпатичнее, к Вашим услугам любой шаблон из галереи стилей таблиц.

Если хотите каждым цветом окрасить различное количество столбцов, тогда скопируйте и настройте выбранный существующий стиль таблицы, как было описано ранее. В этом случае в диалоговом окне Изменение стиля таблицы (Modify Table Style) вместо Первая полоса строк (First Row Stripe) и Вторая полоса строк (Second Row Stripe) нужно выбрать соответственно Первая полоса столбцов (First Colum Stripe) и Вторая полоса столбцов (Second Colum Stripe).

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

Чередование расцветки столбцов при помощи условного форматирования

Формулы для настройки чередующейся окраски столбцов в Excel очень похожи на аналогичные формулы, которые мы ранее использовали для чередования окраски строк. Отличие в том, что в сочетании с функцией ОСТАТ (MOD) вместо функции СТРОКА (COLUMN) нужно использовать функцию СТОЛБЕЦ (COLUMN). Я покажу несколько примеров формул в таблице ниже. Не сомневаюсь, что Вы сами легко сможете преобразовать формулы для строк в формулы для столбцов по аналогии:

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

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

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

Если последовательность нужно сформировать в строке, то формулу нужно вводить в ячейку B2 и копировать ее нужно не вниз, а вправо.

Чтобы сформировать последовательность нечетных чисел вида 1, 3, 7, . необходимо изменить формулу в ячейке А3 на =А2+2 . Чтобы сформировать последовательность 100, 200, 300, . необходимо изменить формулу на =А2+100 , а в ячейку А2 ввести 100.

Другим вариантом создания последовательности 1, 2, 3, . является использование формулы =СТРОКА()-СТРОКА($A$1) (если первый элемент последовательности располагается в строке 2 ). Формула =СТРОКА(A2)-СТРОКА($A$1) позволяет создать вертикальную последовательность, в случае если ее первый элемент последовательности располагается в любой строке. Тот же результат дают формулы =ЧСТРОК($A$1:A1) , =СТРОКА(A1) и =СТРОКА(H1) . Формула =СТОЛБЕЦ(B1)-СТОЛБЕЦ($A$1) создает последовательность, размещенную горизонтально. Тот же результат дают формулы =ЧИСЛСТОЛБ($A$1:A1) , =СТОЛБЕЦ(A1) .

Чтобы сформировать последовательность I, II, III, IV , . начиная с ячейки А2 , введем в А2 формулу =РИМСКОЕ(СТРОКА()-СТРОКА($A$1))

Сформированная последовательность, строго говоря, не является числовой, т.к. функция РИМСКОЕ() возвращает текст. Таким образом, сложить, например, числа I+IV в прямую не получится.

Другим видом числовой последовательности в текстовом формате является, например, последовательность вида 00-01 , 00-02, . Чтобы начать нумерованный список с кода 00-01 , введите формулу =ТЕКСТ(СТРОКА(A1);"00-00") в первую ячейку диапазона и перетащите маркер заполнения в конец диапазона.

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

Создадим последовательность с повторами вида 1, 1, 1, 2, 2, 2. Это можно сделать формулой =ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/3+1) . С помощью формулы =ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/4+1)*2 получим последовательность 2, 2, 2, 2, 4, 4, 4, 4. , т.е. последовательность из четных чисел. Формула =ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/4+1)*2-1 даст последовательность 1, 1, 1, 1, 3, 3, 3, 3, .

Примечание . Для выделения повторов использовано Условное форматирование .

Формула =ОСТАТ(ЧСТРОК(A$2:A2)-1;4)+1 даст последовательность 1, 2, 3, 4, 1, 2, 3, 4, . Это пример последовательности с периодически повторяющимися элементами.

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

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

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

Используем правую клавишу мыши

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

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

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

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

Создадим последовательность вида 1, 2, 3, 1, 2, 3. для этого введем в первые три ячейки значения 1, 2, 3, затем маркером заполнения , удерживая клавишу CTRL , скопируем значения вниз.

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

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

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

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

СОВЕТ: О текстовых последовательностях вида первый, второй, . 1), 2), 3), . можно прочитать в статье Текстовые последовательности . О последовательностях значений в формате дат (и времени) вида 01.01.09, 01.02.09, 01.03.09, . янв, апр, июл, . пн, вт, ср, . можно прочитать в статье Последовательности дат и времен . О массивах значений, содержащих последовательности конечной длины, используемых в формулах массива , читайте в статье Массив значений (или константа массива или массив констант) .

Быстро пронумеровать список фамилий, расставить людей по рейтингу и присвоить участникам викторины порядковый номер — задачи, которые можно автоматизировать. Зачем проставлять номера каждому участнику списка отдельно, если можно воспользоваться силой 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-таблицами — они помогут освойте таблицы, даже если сейчас знаний ноль.

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

Рассматривать все доступные способы, будем с помощью следующей таблицы:

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

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

В итоге таблица снова автоматически заполнится.

Третий способ. Поставьте в ячейку «А2» цифру один, после выделите диапазон ячеек с «А2» по «А8». На верхней панели настроек, в закладке «Главная», найдите раздел «Редактирование», где есть иконка в виде синего квадратика со стрелочкой вниз, нажмите на неё, в появившемся списке выберете «Прогрессия».

В появившемся меню выберете в разделе «Расположение»: «по столбцам», «Тип»: «арифметическая». И укажите шаг единичка, после нажмите на кнопку «ОК» и программа снова все вам пронумерует от одного до восьми.

В Excel вы можете ввести начальный номер, а затем перетащить дескриптор автозаполнения вниз, чтобы автоматически заполнить ряд чисел. Но в некоторых случаях вам нужно вставить последовательность чисел, которая заканчивается определенным числом, а затем повторять эту последовательность снова и снова, как показано на скриншоте ниже, как вы можете быстро вставить ее? Это руководство может дать вам удовлетворительный ответ.
Повторите ряд чисел в столбце с формулой
Повторите серию чисел в столбце или нескольких столбцах с помощью Kutools for Excel

Найти отсутствующий порядковый номер

Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Повторите ряд чисел в столбце с формулой

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

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

2. В следующей ниже ячейке введите эту формулу = ЕСЛИ (A1 = 4,1; A1 + 1) , и нажмите Enter нажмите клавишу, затем перетащите дескриптор автозаполнения в нужные ячейки вниз. Смотрите скриншоты:

Наконечник:

1. В формуле выше = ЕСЛИ (A1 = 4,1; A1 + 1) , 4 указывает конечный номер последовательности, 1 указывает начальный номер последовательности, а A1 - это первая ячейка, в которую вы вставите повторяющуюся последовательность.

2. Если вы хотите вставить повторяющуюся последовательность чисел, начинающуюся с цифры 1, вы можете использовать эту формулу = МОД (A1,4) +1 , Тоже.

Повторите серию чисел в столбце или нескольких столбцах с помощью Kutools for Excel

Если вы хотите повторить серию чисел в диапазоне, вы можете использовать Kutools for ExcelАвтора Вставить порядковый номер утилита.

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Выберите диапазон столбцов из нескольких диапазонов столбцов, щелкните Kutools > Вставить > Вставить порядковый номер. Смотрите скриншот:

2. В появившемся диалоговом окне щелкните НОВИНКИ чтобы развернуть диалоговое окно, см. снимок экрана:

3. Затем в Вставить порядковый номер диалог, сделайте как показано ниже:

(1) Назовите последовательность;

(2) Укажите Стартовый номер последовательности;

(3) Укажите инкремент последовательности;

(4) Укажите цифры последовательности, если вам нужно, 1, 2…, просто введите 1 в Кол-во цифр текстовое поле, если вам нужно 01, 02…, введите 2 внутрь;

(5) Проверить Конечный номери введите конечный номер серии в текстовое поле ниже. Смотрите скриншот:

4. Нажмите Добавить, а затем нажмите кнопку «Заполнить диапазон», будет вставлен повторяющийся ряд чисел.

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