Как сделать последовательность чисел в excel

Обновлено: 05.07.2024

Эта статья является логическим продолжением предыдущего материала про новые динамические массивы (ДМ), появившиеся в Excel в Office 365. Если вы ещё с не ознакомились (кому лень читать - там есть видео), то очень советую сделать это сейчас, чтобы понимать о чём, собственно, идёт речь и как заполучить все эти радости в вашем Excel.

Обновление Office 365, которое подарило Microsoft Excel новый вычислительный движок с поддержкой динамических массивов, также добавило к нашему арсеналу 7 новых функций, заточенных специально для работы с массивами. Про три самых важных функции: СОРТ (SORT) , ФИЛЬТР (FILTER) и УНИК (UNIQUE) я уже писал, а в этой статье хотел разобрать ещё одну весьма приятную обнову - функцию ПОСЛЕД (SEQUENCE) .

Обманчивая простота

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

=ПОСЛЕД( строки ; [столбцы] ; [начало] ; [шаг] )

строки - число строк в диапазоне, который надо заполнить числовой последовательностью - это единственный обязательный аргумент;
с толбцы - число столбцов в диапазоне (если нужно сгенерить двумерный массив)
н ачало - стартовое значение (если не задано, то 1)
шаг - шаг изменения ряда (если не задан, то 1)

Последние два аргумента могут быть и дробными, и отрицательными числами при необходимости.

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

Пример 1. Посимвольный разбор текста

Уверен, вы не раз встречались при заполнении бланков и всевозможных анкет с необходимостью "распатронить" текст на отдельные символы по отдельным ячейкам. С нашей функцией ПОСЛЕД эта задача решается в два счёта:

В английской версии это будет

Логика тут простая:

Сначала функция ПОСЛЕД формирует простой числовой ряд 1, 2, 3 . N, где N - длина исходного текста, определяемая функцией ДЛСТР (LEN) .
Затем функция ПСТР (MID) выдёргивает из исходного текста символы по очереди, используя сгенерированную последовательность как порядковый номер извлекаемого символа.

Для аналогичного разбора даты по ячейкам, придется добавить ещё функцию ТЕКСТ (TEXT) для преобразования даты в текст - иначе на выходе мы получим код даты, а не её саму в привычном виде. Ну, а длину в этом случае мы знаем заранее - 10 символов:

Пример 2. Сумма каждой N-ой ячейки

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

Функция ПОСЛЕД (SEQUENCE) здесь генерит числовую последовательность номеров строк, откуда нам нужно взять данные: 5, 9, 13, 17, 21.

Затем функция ИНДЕКС (INDEX) вытаскивает значения по этим номерам из столбца А, и затем мы их суммируем с помощью СУММ (SUM) .

Пример 3. Трансформация столбца в таблицу

Самый красивый пример - преобразование столбца с данными в двумерную таблицу одной короткой формулой:

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

Чтобы не привязываться к жёстко прописанному количеству блоков (людей) в таблице - мы используем функцию СЧЁТЗ (COUNTA) , чтобы вычислить их количество.

А затем мы извлекаем данные из левой таблицы по номеру строки с помощью функции ИНДЕКС (INDEX) . (Если вы с ней ещё, не дай бог, не знакомы, то обязательно посмотрите эту статью).

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

Пример 4. Многоуровневая нумерация

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

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

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

В английской версии это, соответственно :

Функция ПОСЛЕД сначала генерирует числовую последовательность 1, 2, 3 .. 15 (общее число строк, равное произведению количества смен на количество людей в каждой смене)
Мы делим эти числа на число людей в каждой смене и получаем последовательность дробных чисел: 0.2, 0.4, 0.6, 0.8, 1, 1.2 и т.д.
Затем мы округляем эти дробные числа до ближайшего целого функцией ОКРУГЛВВЕРХ (ROUNDUP) .

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

Здесь мы сначала вычисляем остаток от деления нашей последовательности 1,2,3. 15 на 5 с помощью функции ОСТАТ (MOD) :

А затем заменяем нули на 5 - значение из ячейки B4 с помощью функции ЕСЛИ (IF) .

Дополнительно, можно подтянуть к нашему графику еще и имена сотрудников из списка с помощью уже знакомой нам функции ИНДЕКС (INDEX) :

Заключение и выводы

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

Если же вы пока ещё не получили обновление, которое добавляет в ваш Excel динамические массивы и эти новые функции, то когда придет время - вы будете готовы :)

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

Перед построением последовательностей и различных прогрессий, как обычно, вспомним их детальные определения.
Числовая последовательность — это упорядоченный набор произвольных чисел a₁, a₂, a₃, …, a_n, … .
Арифметической прогрессией называется такая числовая последовательность, в которой каждый член, начиная со второго, получается из предыдущего добавлением постоянной величины d (также называют шагом или разностью):

Геометрическая прогрессия — это последовательность чисел, в котором каждый член, начиная со второго, получается умножением предыдущего члена на ненулевое число q (также называют знаменателем):

С определениями закончили, теперь самое время перейти от теории к практике.

Арифметическая прогрессия в Excel

Рассмотрим 2 способа задания прогрессии в Excel — с помощью стандартного инструмента Прогрессия и через формулы.
В первом случае на панели вкладок выбираем Главная -> Редактирование -> Заполнить -> Прогрессия:

Далее мы увидим диалоговое окно с настройками параметров:

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

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

Разберем как сделать арифметическую прогрессию в Excel на конкретном примере.

Создадим набор чисел 3, 7, 11, … , то есть первый элемент равняется 3, а шаг равен 4.
Выделяем диапазон (к примеру, A1:J1) в котором мы хотим разместить набор чисел (диапазон можно и не выделять, однако в этом случае в настройках будет необходимо указать предельное значение), где в первой ячейке будет указан первый элемент (в нашем примере это 3 в ячейке A1), и указываем параметры (расположение, тип, шаг и т.д.):

В результате мы получим заполненный диапазон с заданным набором чисел:

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

Геометрическая прогрессия в Excel

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

Например, создадим набор чисел 4, 8, 16, … , то есть первое число равно 4, а каждое последующее в 2 раза больше предыдущего.
Также задаем начальный элемент (4 в ячейке A1), выделяем диапазон данных (например, A1:J1) и указываем параметры:

В итоге получаем:

Идентичного результата также можно добиться и через использование формул:

Числовая последовательность в Excel

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

Непосредственное (прямое) перечисление элементов;
Через общую формулу n-го члена;
С помощью рекуррентного соотношения, которое выражает произвольный член через предыдущие.

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

Рассмотрим создание числовой последовательности на примере построения обратных чисел к натуральным, то есть набора чисел 1, 1/2, 1/3, … , в котором общая формула n-го члена принимает вид F_n=1/n.
Создадим дополнительный ряд в отдельной строчке, куда для удобства расчета поместим порядковые номера (1, 2, 3 и т.д.), на которые будут ссылаться формулы:

В варианте с рекуррентной формулой рассмотрим пример с набором чисел Фибоначчи, в котором первые два числа равны 1 и 1, а каждый последующее число равно сумме двух предыдущих.
В итоге произвольный член можно представить в виде рекуррентного соотношения F_n = F_n-1 + F_n-2 при n > 2.
Определяем начальные элементы (две единицы) в двух ячейках, а остальные задаем с помощью формулы:

Автоматическая нумерация строк

В этом курсе:

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

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

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

В этой статье

Заполнение столбца последовательностью чисел

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

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

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

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

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

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

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

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

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

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

Нумерация строк с помощью функции СТРОКА

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

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

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

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

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

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

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

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

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

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

3 способа автоматической нумерации строк в программе Microsoft Excel

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

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

Способ 1: заполнение первых двух строк

Первый способ предполагает ручное заполнение первых двух строк числами.

Как видим, нумерация строчек автоматически заполнилась по порядку.

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

Способ 2: использование функции

Как видим, нумерация строк и в этом случае расположилась по порядку.

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

Способ 3: использование прогрессии

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

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

Как альтернативный вариант можно использовать следующую схему этого же способа:

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

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

Функция Excel ПОСЛЕДОВАТЕЛЬНОСТЬ генерирует список последовательных чисел в массиве. Массив может быть одномерным или двумерным. Значения start и step являются аргументами.

Возвращаемое значение

Синтаксис

Аргументы

rows - Количество возвращаемых строк.
columns - (необязательно) Количество возвращаемых столбцов.
start - (необязательно) Начальное значение (по умолчанию 1).
step - (необязательно) приращение между каждым значением (по умолчанию 1).

Версия

Примечания по использованию

Функция ПОСЛЕДОВАТЕЛЬНОСТЬ Excel создает список последовательных чисел в массиве. Массив может быть одномерным или двумерным, управляемым аргументами строк и столбцов . Значения start и step (приращение) также передаются в качестве аргументов. В показанном примере формула B4 имеет следующий вид:

В этой конфигурации ПОСЛЕДОВАТЕЛЬНОСТЬ возвращает массив последовательных чисел, 10 строк на 5 столбцов, начиная с нуля и увеличиваясь на 3. В результате получается 50 чисел, начиная с 0 и заканчивая 147, как показано на экране.

Чтобы вернуть 10 чисел в одном столбце, начиная с -5 и заканчивая 5, увеличенные на 1:

Способ 1 — простая нумерация

Ставим в первую ячейку цифру 1, во вторую — цифру 2. Затем выделяем обе ячейки и тянем вниз, чтобы заполнить всё до конца.

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

Как это работает. Так как в первую ячейку мы поставили единицу, а во вторую — двойку, Excel счёл это как последовательность. Скорее всего, следующее число будет 3, за ним 4, а потом 5 и так далее.

Способ 2 — автоматическая последовательная нумерация

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

Далее точно так же тянем всё вниз и заполняем диапазон.

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

Способ 3 — через функцию =СТРОКА()

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

Функция =СТРОКА() возвращает порядковый номер строки для текущей ячейки, начиная со строки №1

Зная это, мы можем найти закономерность и здесь. Введём формулу в ячейку E2:

Как видите, результат работы функции — это число 2. Строка вторая, значит в результате будет 2.

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

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

Арифметическая прогрессия — это последовательность чисел, где каждое следующее число больше предыдущего на какой-то шаг

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

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

Способ 5 — функция МАКС()

Иногда бывает, что список людей идёт с разрывами и нам нужно продолжать нумерацию после пропуска. В этом случае нам подойдёт способ через функцию МАКС и работает он следующим образом.

Функция МАКС ищет наибольшее число в указанном диапазоне

В первую ячейку ставим число 1, а во вторую вводим формулу: =МАКС($E$2:E2)+1

Начало диапазона мы указали строго от $E$2, то есть первая граница не меняется ни при каких условиях. Концом диапазона будет последняя ячейка после протягивания. При протягивании такой формуле в строке 26 мы получим формулу: =МАКС($E$2:E25)+1

А там где нам нужно пропустить значения автоматической нумерации, мы просто удаляем формулу из ячейки и всё, что идёт ниже, автоматически пересчитывается.

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

Как это работает. Так как диапазон увеличивается на единицу =МАКС() + 1, то у нас каждая ячейка будет больше другой и функция МАКС будет выбирать её.

Способ 6 — адаптивная автоматическая нумерация через =СЧЁТЗ() и =ЕСЛИ()

Если в предыдущем способе нам всё ещё нужно работать с ячейками вручную, то в этом способе мы всё автоматизируем ещё больше. Нам понадобится комбинация функций СЧЁТЗ и ЕСЛИ, чтобы фильтрация происходила по условию.

Функция СЧЁТЗ считает количество заполненных ячеек в диапазоне
Функция ЕСЛИ проверяет ячейку на соответствие какому-то условию. Если условие истинно, выполняется одно действие. Если условие ложно, выполняется другое действие

Вставляем формулу в ячейку B2, тянем вниз и формула автоматически пропускает пустые строки. Заметьте, что в части формулы, где СЧЁТЗ, мы закрепили первую часть, потому что мы не хотим, чтобы диапазон состоял из одной строки. Нам нужно, чтобы он постоянно увеличивался.

Как это работает. Функция ЕСЛИ отвечает за такое условие: если ячейка B2, где у нас лежит фамилия, пустая, то так и оставить её пустой, иначе — посчитать общее количество заполненных ячеек.

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

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

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