Как создать числовую последовательность в таблице Excel

Обновлено: 21.11.2024

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

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

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

  • Использование функции маркера заполнения
  • Использование функции ROW
  • Использование функции ПОСЛЕДОВАТЕЛЬНОСТЬ
  • Преобразование набора данных в таблицу

Давайте рассмотрим каждый из этих методов один за другим, чтобы ввести серийные номера в Excel.

Оглавление

Использование маркера заполнения для ввода порядковых номеров в Excel

Функция Excel «Обработчик заполнения» достаточно умна, чтобы выявлять шаблоны в ваших данных и проецировать шаблон на остальные ячейки в столбце.

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

Вот как можно быстро заполнить столбец A последовательностью чисел с помощью маркера заполнения:

  1. Выберите обе ячейки (A2 и A3). Вы должны увидеть ручку заполнения (маленький зеленый квадрат) в правом нижнем углу выделенного фрагмента.

  1. Перетащите дескриптор заполнения вниз до последней строки набора данных (или просто дважды щелкните маркер заполнения).

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

Использование функции ROW для ввода последовательных чисел в Excel

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

Однако ваш порядок последовательности изменится, как только вы отсортируете данные или даже если вы вставите или удалите строку.

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

Единственная задача функции ROW — вернуть номер ROW текущей строки.

Поэтому, если вы введете =СТРОКА() в ячейку A3 или B3 или любую ячейку в строке 3, вы получите значение 3.

Поскольку мы хотим начать последовательность со второй строки и далее (в нашем примере). Мы можем ввести следующую формулу в ячейку A2:

Функция ROW() в ячейке A2 возвращает значение 2, поскольку оно находится во второй строке.

Однако вычитание 1 из возвращаемого значения гарантирует, что вы получите на одно значение меньше для каждого номера строки. Это означает, что значение в формате A4 будет равно 3 и т. д.

Если бы вместо второй строки вам нужно было начать последовательность с 5-й строки (скажем), тогда ваша формула была бы такой:

Скопируйте формулу в остальные ячейки столбца A, используя маркер заполнения.

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

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

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

Преобразование набора данных в таблицу Excel

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

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

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

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

Более того, даже если вы добавите новые строки в конец таблицы, она автоматически расширится, чтобы включить новые строки как часть таблицы.

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

Чтобы преобразовать набор данных в таблицу Excel, выполните следующие действия:

  1. Выберите все ячейки набора данных.
  2. Перейдите к Вставка->Таблица. Кроме того, вы можете просто нажать сочетание клавиш CTRL+T на клавиатуре.

  1. Откроется диалоговое окно Создать таблицу. Убедитесь, что диапазон, указанный в разделе «Где находятся данные для вашей таблицы», верен.
  2. Если ваш выбор включал заголовки, установите флажок рядом с пунктом "В моей таблице есть заголовки".

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

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

После того как таблица будет готова, вы можете ввести следующую формулу в первую строку таблицы (ячейка A2):

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

Здесь мы использовали Table1 в качестве имени таблицы Excel. Вы можете заменить это имя на имя вашей таблицы.

Примечание. Вы найдете имя таблицы на вкладке Дизайн (или Дизайн таблицы) в поле ввода под меткой "Имя таблицы:" (когда вы нажмите на любую ячейку таблицы) :

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

Этот метод работает независимо от того, где находится ваш стол.

Использование функции ПОСЛЕДОВАТЕЛЬНОСТЬ для ввода порядковых номеров в Excel (доступно только в Office 365 и Excel 2021)

Возможно, самый простой способ создать массив последовательных чисел — использовать функцию ПОСЛЕДОВАТЕЛЬНОСТЬ.

Функция ПОСЛЕДОВАТЕЛЬНОСТЬ — это недавно добавленная функция Excel. На самом деле он доступен только в более новых версиях, таких как Office 365 и Excel 2021.

Эта функция в основном генерирует последовательные числа в виде динамического массива. Синтаксис этой функции следующий:

  • rows – это количество строк, которое вы хотите вернуть в массив.
  • columns – это количество столбцов, которое вы хотите вернуть в массиве.
  • начало – это номер, с которого должна начинаться последовательность. Этот параметр является необязательным, и по умолчанию его значение равно 1.
  • шаг – это значение шага, на которое должно увеличиваться/уменьшаться каждое число в последовательности. Этот параметр является необязательным, и по умолчанию его значение равно 1.

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

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

Вы получаете массив из 8 последовательных чисел, отображаемых в столбце A, как показано ниже:

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

Означает ли это, что мы должны обновлять параметр в функции ПОСЛЕДОВАТЕЛЬНОСТЬ каждый раз при добавлении или удалении строки?

Мы можем использовать функцию COUNTA, чтобы отслеживать последнюю строку, содержащую данные. Следующая формула должна работать лучше:

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

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

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

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

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

Объяснение формулы

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

В нашем примере формула =СЧЁТЕЗ(B:B) вернёт значение 9, так как в столбце B 9 ячеек (включая заголовок).

Но мы хотим начать последовательность со второй строки и далее (исключая заголовок), поэтому нам нужно вычесть 1 из значения, возвращаемого функцией СЧЕТЧ.

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

При использовании этой функции вы получаете последовательность чисел от 1 до 8 (в нашем случае), начиная с ячейки A2.

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

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

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

Третий метод намного надежнее, но он предполагает полное переформатирование набора данных в таблицу Excel.

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

Однако функция, используемая в этом методе (функция ПОСЛЕДОВАТЕЛЬНОСТЬ), доступна только в более новых версиях Excel, таких как Office 365 и Excel 2021.

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

В отличие от других программ 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 утомительна. Чтобы избавиться от этой монотонной проблемы, я собираюсь показать вам способы автоматической генерации числовой последовательности. Для подготовки урока я использую Excel 2019. Не беспокойтесь, если у вас есть другая версия.

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

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

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

Практическая рабочая тетрадь

Я предоставил вам доступ к книге. Вы можете скачать его по ссылке ниже.

Автоматическое создание последовательности номеров

1. Основной метод добавления

В этом методе сериализации мы увеличим номер предыдущей строки на 1. Таким образом, мы добавим 1 к номеру предыдущей строки в нашей текущей ячейке.

Сначала вставьте число в первую строку. Как правило, серийные номера начинаются с 1. Для примера не усложняйте и вставьте 1 в строку, с которой вы хотите начать.

Теперь добавьте 1 к этому номеру строки. Используйте ссылку на ячейку при добавлении.Например, я вставил 1 в ячейку C4. Итак, формула была C4+1

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

Хотя создание серийных номеров с разницей более 1 является необычным, вам может потребоваться сгенерировать число, увеличивающееся на 2 (или любое другое приращение). Используйте 2 вместо 1 в предыдущей формуле.

2. Метод автозаполнения для автоматического создания числа

Для метода автозаполнения у вас есть два варианта. Маркер заполнения и серия заполнения.

2.1. Маркер заполнения

Вставьте значение в строку и выберите его в качестве активной ячейки. В правом нижнем углу вы увидите небольшое круглое поле (отмечено на изображении ниже).

Я написал в ячейке 1 и установил ее как активную (выбранную).

Здесь у вас есть два варианта:

Вы можете выбрать любой из вариантов.

О! Это дало кучу 1s. Ничего подобного мы точно не хотели. Почему это произошло?

Когда я использовал дескриптор заполнения, был сохранен только 1. Excel не понял, что я хотел (конечно, я не сделал никакой команды, чтобы он понял). Давайте исправим это.

Давайте вернемся назад и вставим еще одно число в следующую строку. Пусть другое число будет 2.

Теперь выберите обе ячейки одновременно. Вы увидите этот значок Заполнителя в правом нижнем углу.

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

На этот раз это сработало. Когда мы устанавливаем два значения, Excel понимает шаблон и выдает результат в соответствии с ним. Здесь, выбирая 1 и 2 одновременно, выберите понимать последовательность чисел, увеличенную на 1.

2. 2. Метод заполнения серий

Для метода Fill Series вам не нужно вставлять два значения, как в предыдущем случае.

Вставьте значение в одну строку. Выбрав эту строку, изучите раздел «Редактирование» на вкладке «Главная». Там в опции «Заливка» вы найдете опцию «Серии».

Нажмите "Серия", откроется новое диалоговое окно. В диалоговом окне задайте значения и направление.

Здесь я установил "Столбцы" в разделе "Серия" (поскольку моим направлением генерации чисел был столбец).

Шаг: 1 (увеличить на 1)

Стоп-значение: 5 (генерируется до 5)

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

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

Функция СТРОКА возвращает номер строки ячейки, в которой вы сейчас находитесь (активная ячейка).

Я написал функцию ROW в 4-й строке (F4), и поэтому она вернула значение 4.

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

Здесь мой номер строки был 4 (возвращенный функцией ROW), поэтому я вычел 3 из результата, и это дало мне нужное число.

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

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

Просто вставьте имя ячейки в функцию ROW. Функция вернет номер строки.

Здесь я использовал A1 в качестве параметра в ROW, и он дал 1. Вы можете выбрать любую ячейку, она предоставит вам число.

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

4. Метод функции COUNTA для автоматического создания последовательности

Еще один метод, о котором стоит упомянуть, — это функция COUNTA. Функция COUNTA подсчитывает количество непустых ячеек в диапазоне. Этот метод поможет вам сериализовать непустые строки.

Чтобы использовать функцию COUNTA, сначала нам нужно проверить, пуста ли ячейка (или диапазон ячеек) или нет. Мы сделаем это с помощью функции ISBLANK.

ЕПУСТО – это логическая функция, возвращающая значение ИСТИНА или ЛОЖЬ.

Используйте оператор IF, чтобы использовать результат ISBLANK.

Здесь наша логика будет проверять, пусто/пусто или нет.

Пусть пока не присваивает никакого значения ИСТИННОМУ результату.

Мы напишем функцию COUNTA, которая срабатывает, когда логика становится ЛОЖЬЮ.

Формула будет похожа

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

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

5. Метод функции OFFSET

Функция СМЕЩ возвращает ссылку на ячейку или диапазон ячеек. Вы должны вставить 3 параметра в функцию OFFSET

Ссылка: ячейка, которую вы хотите запустить

Строки: какое количество строк

Столбцы: какой номер столбца

Вот в качестве примера я написал A4, 0, 0 в ссылке, строках, столбцах-заполнителях соответственно. В результате это предоставил Ашраф Али.

Теперь воспользуемся функцией OFFSET для создания числовой последовательности.

Формула будет похожа

В рамках функции OFFSET() в этой формуле, во-первых, мы ссылаемся на конкретную ячейку. Затем установите на одну строку раньше, используя -1 в заполнителе строк. 0 означает, что внутри столбца нет обхода. Теперь добавьте 1 к значению, возвращенному OFFSET.

Запишите это в Excel. Вы лучше поймете на примере.

Я установил H4 в значение ячейки внутри. Теперь OFFSET возвращает значение H3 (на одну строку меньше из-за -1). Затем добавил к этому значению 1 и дал число.

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

Он вернул строку. Можем ли мы добавить 1 к строке? Давайте попробуем

Ошибка. Чтобы исправить ошибки такого рода, оставьте пустую ячейку над начальной точкой при использовании функции СМЕЩ для создания последовательности чисел.

6. Функция SEQUENCE для автоматического создания числа

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

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

N — это количество строк, которые вы хотите заполнить.

В функции SEQUENCE также есть три необязательных поля.

Столбцы, начало, шаг.

Строки: сколько строк вы хотите заполнить

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

Начало: начальный номер вашей последовательности. По умолчанию это 1

Шаг: приращение каждого значения в последовательности. По умолчанию это 1

Однако с другой стороны, если у вас нет Microsoft Office 365, вы не сможете воспользоваться преимуществами этой функции ПОСЛЕДОВАТЕЛЬНОСТЬ. Поскольку я использую Excel 2019, я не могу показать вам какие-либо примеры. Но я делюсь ссылкой на видео для лучшего понимания.

Заключение

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

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

Автозаполнение также может создавать ряды дат и значения, содержащие как текст, так и числа, показанные в столбцах F, H и J выше.

Что на этой странице

1. Где находится кнопка автозаполнения на ленте?

Кнопка "Автозаполнение" находится на вкладке "Главная" на ленте. Нажмите левой кнопкой мыши на кнопку "Заполнить" и появится всплывающее меню.

Всплывающее меню показывает:

  • Вниз
  • Правильно
  • Вверх
  • Слева
  • На листах.
  • Серии.
  • Обосновать
  • Быстрая заливка

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

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

1.1 Как создать числовой ряд от 1 до n

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

Пример 1

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

  1. Введите 1 в ячейку B2.
  2. Нажмите Enter.
  3. Нажмите правой кнопкой мыши на черную точку и перетащите ее вниз, насколько это необходимо.
    Появится всплывающее меню.
  4. Нажмите левой кнопкой мыши на "Заполнить серию".
Пример 2
  1. Введите 1 в ячейку A3 и 2 в ячейку A4.
  2. Выберите формат A3 и A4.
  3. Нажмите и удерживайте левую кнопку мыши на черной точке и перетащите вниз, насколько это необходимо.
Пример 3

Если вы предпочитаете формулу, попробуйте эту:

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

Формула работает должным образом, даже если вы вставляете строки над ячейкой B2.

1.2 Как создать числовую серию со всеми остальными числами

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

  1. Выберите ячейку D2.
  2. Введите 1 и нажмите Enter.
  3. Выберите ячейку D3, если она еще не выбрана.
  4. Введите 3 и нажмите Enter на клавиатуре.
  5. Выберите диапазон ячеек D2:D3.
  6. Нажмите и удерживайте левой кнопкой мыши точку в правом нижнем углу выбранного диапазона ячеек.
  7. Перетащите мышью вниз, насколько это необходимо.
  8. Отпустите левую кнопку мыши.

1.3 Как создать серию дат

В столбце F на изображении выше показан ряд дат с 01.01.2020 по 05.01.2020. Вот как его быстро создать:

  1. Выберите ячейку F2.
  2. Введите 01.01.2020 и нажмите клавишу ВВОД на клавиатуре.
  3. Снова выберите ячейку F2.
  4. Нажмите и удерживайте правую кнопку мыши на точке, расположенной в правом нижнем углу выбранной ячейки, в данном случае на ячейке F2.
  5. Перетащите мышью вниз, насколько это необходимо.
  6. Отпустите правую кнопку мыши.
  7. Появится всплывающее меню.
  8. Нажмите левой кнопкой мыши на "Заполнить серию".

    9. 1.4 Как создать серию дат на основе заданного периода

    В столбце H показан ряд дат за каждую вторую неделю или 14 дней.

    1. Выберите ячейку H2.
    2. Введите 01.01.2020 или дату начала, которую вы хотите использовать, а затем нажмите клавишу ВВОД.
    3. Выберите ячейку H3, если она еще не выбрана.
    4. Введите 15.01.2020 или дату, которую вы хотите использовать, и нажмите Enter на клавиатуре.
      Инструмент автозаполнения будет использовать разницу в днях между первой датой (H2) и второй датой (H3) для создания оставшихся дат.
    5. Выберите диапазон ячеек H2:H3.
    6. Нажмите и удерживайте левой кнопкой мыши точку, расположенную в правом нижнем углу выделенного диапазона ячеек.
    7. Перетащите мышью в ячейки ниже, насколько это необходимо.
    8. Отпустите левую кнопку мыши.

    1.5 Как создать серию значений на основе текста и чисел

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

    1. Выберите ячейку J2.
    2. Введите элемент 1
    3. Снова выберите ячейку J2.
    4. Нажмите и удерживайте левой кнопкой мыши точку, расположенную в правом нижнем углу выбранной ячейки.
    5. Перетащите мышью в ячейки ниже, насколько это необходимо.

    2. Создание повторяющейся числовой последовательности с помощью формулы

    В этом примере я собираюсь создать повторяющуюся числовую последовательность 1, 2, 3, 4.

    Эта формула проверяет, равен ли предыдущий порядковый номер 4. Если он истинен, он перезапускается со значением 1. Если нет, он добавляет к предыдущему порядковому номеру 1.

    Выберите ячейку B3 и введите 1. Затем нажмите клавишу ВВОД.

    Формула в B4:

    Введите приведенную выше формулу в ячейку B4. Нажмите Enter.

    Обратите внимание, что вам не нужно вводить все формулы в диапазоне ячеек B2:B10. Только число в ячейке B3 и формула в ячейке B4.

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

    Пояснение формулы в ячейке B4

    Функция ЕСЛИ позволяет вам контролировать результат на основе условия или нескольких условий, другими словами, она возвращает одно значение, если логическая проверка ИСТИНА, и другое значение, если логическая проверка ЛОЖЬ.

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

    ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])

    Шаг 1. Логическое выражение

    B3 — это относительная ссылка на ячейку. Относительная означает, что она изменяется, когда ячейка копируется, а затем вставляется в другую ячейку, в данном случае в соседнюю ячейку ниже.

    Например, ссылка на ячейку B3 изменяется на ячейку B4, когда ячейка B4 копируется в ячейку B5.

    Помните, что эта формула находится в ячейке B4, поэтому ссылкой на ячейку B3 является соседняя ячейка выше. B3 равно 1.

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

    Проверяет, встречается ли логическое выражение. Возвращает конкретное значение, если TRUE, и другое конкретное значение, если FALSE.

    Шаг 2. Следующий аргумент

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

    и возвращает 2 в ячейке B4.

    Следующие ячейки

    Только в ячейке B7 происходит что-то неожиданное. Теперь логическое выражение оценивается как ИСТИНА.

    Это заставит функцию ЕСЛИ вычислить второй аргумент вместо третьего, как раньше.

    ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])

    возвращает 1. Серия начинается заново, начиная с 1.

    3. Создайте ряд чисел и перезапустите, когда значение ячейки будет равно заданному условию

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

    Выберите ячейку C3 и введите 1. Затем нажмите клавишу ВВОД.

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

    Пояснение формулы в ячейке C4

    Функция ЕСЛИ возвращает одно значение, если логическая проверка ИСТИНА, и другое значение, если логическая проверка ложна.

    ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])

    Шаг 1. Вычисление логического выражения

    Логическое выражение является первым аргументом, в данном случае: B4="A". Ссылка на ячейку B4 — это относительная ссылка на ячейку, она изменяется, когда ячейка копируется в ячейки ниже. Обратите внимание: чтобы это работало, вам нужно скопировать ячейку, а не формулу.

    Текстовая строка "A" является условием, если ячейка B4 равна условию, она возвращает TRUE, в противном случае FALSE. ИСТИНА и ЛОЖЬ — логические значения в Excel.

    и возвращает ЛОЖЬ.

    Шаг 2. Возврат второго аргумента, если ИСТИНА, и третьего аргумента, если ЛОЖЬ.

    Второй аргумент равен 1, а третий аргумент добавляет 1 к числу в ячейке C3.

    и возвращает 2 в ячейке C4.

    Следующие ячейки

    Только в ячейке C7 что-то изменится в формуле.

    и возвращает 1. Теперь серия начинается с номера 1.

    4. Создайте числовую последовательность для подсчета записей по годам и месяцам (отсортированный список)

    Эта формула проверяет, совпадают ли год и месяц предыдущей даты с датой текущей ячейки. Если true, к предыдущему порядковому номеру добавляется 1. Если false, последовательность начинается снова с 1.

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

    Формула в C4:

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

    Пояснение формулы в ячейке C4

    Шаг 1. Преобразуйте даты в месяц и год
    Шаг 2. Сравните значения

    ТЕКСТ(B4, "М-ГГГГ")=ТЕКСТ(B3, "М-ГГГГ")

    ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])

    Шаг 3. Если функция возвращает одно значение, если True, и другое значение, если False

    ЕСЛИ(ТЕКСТ(B4, "М-ГГГГ")=ТЕКСТ(B3, "М-ГГГГ"), C3+1, 1)

    5. Создайте числовую последовательность для подсчета записей по году и месяцу (несортированный список)

    Формула массива в B32:

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

    Функция СУММПРОИЗВ вычисляет произведение соответствующих значений, а затем возвращает сумму каждого умножения.

    6. Создайте числовую последовательность для подсчета дат по годам

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

    Формула в B39:

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

    7. Создайте числовую серию для подсчета отдельных продуктов

    Функция СЧЁТЕСЛИ просто подсчитывает, сколько раз отображалось значение элемента.

    Формула в C3:

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

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

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

    8. Создайте числовую последовательность для подсчета цен в определенных диапазонах

    Формула в ячейке C9 создает последовательность в зависимости от того, в каком диапазоне находится цена.

    Формула в C9:

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

    9. Создайте числовую серию для подсчета записей по отдельным продуктам и годам

    Формула в D3:

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

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

    Формула в столбце B возвращает текущий счет на основе значений в столбце C.

    Формула в ячейке B3:

    Формула состоит из двух функций Excel. Функция ЕСЛИ проверяет, не пусто ли соответствующее значение в столбце C.

    Если ячейка C3 не пуста, функция COUNTA подсчитывает количество непустых ячеек в диапазоне ячеек $C$3:C3.

    Диапазон ячеек $C$3:C3 содержит одну непустую ячейку, поэтому формула возвращает 1 в ячейке B3.

    Обратите внимание, что ссылка на ячейку $C$3:C3 расширяется, когда вы копируете ячейку B3 и вставляете ее в ячейки ниже.

    Например, в ячейке B4 ссылка на ячейку изменится на $C$3:C4.

    Если ячейка C3 пуста, формула ничего не возвращает.

    Посмотрите видео, в котором я демонстрирую приемы, описанные ниже

    Еженедельный блог EMAIL

    [newsletter_signup_form Я Оскар и здесь, чтобы помочь вам.

    Не стесняйтесь оставлять комментарии и задавать вопросы по Excel.

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

    Статьи по теме

    В этой статье объясняется, как повторять определенные значения на основе таблицы, таблица содержит повторяющиеся элементы […]

    В этом сообщении Поиск самой длинной/наименьшей последовательной последовательности значений содержит несколько действительно больших формул массива. Сегодня я хотел бы […]

    Формула массива в ячейке D3 возвращает длину самой длинной последовательной последовательности значений в столбце A. Ячейка […]

    В этой статье демонстрируются формулы массива, которые определяют два значения поиска в строке или в последовательности. Изображение выше […]

    В этой статье демонстрируются формулы массива, которые выполняют поиск с использованием подстановочных знаков на основе последовательности значений. Формулы возвращают […]

    Вопрос: как определить две последовательные даты в списке? Ответ: Формула массива в ячейке B1: =ЕСЛИОШИБКА(НАИБОЛЬШИЙ(ЕСЛИ((A1+1)=$A$1:$A$30,A1+1,""),1),"") Как ввести […]< /p>

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