Щелчок и перетаскивание маркера заполнения в Microsoft Excel для создания нумерованного списка ненадежны, нединамичны и требуют много времени, особенно если список длинный. Вместо этого вы можете объединить две самые простые функции Excel, чтобы программа сделала всю работу за вас.
На момент написания статьи (май 2025 г.) функция СЧЁТЗ доступна в большинстве версий Microsoft Excel, однако доступ к функции ПОСЛЕДОВАТЕЛЬНОСТЬ могут получить только те, кто использует версию Microsoft Excel, выпущенную в 2021 году или более позднюю, Microsoft 365, Excel для Интернета или приложения Microsoft Excel для телефонов и планшетов.
Получите постоянный доступ к Word, Excel, PowerPoint и OneNote с пакетом Office Home 2024. Это не полная замена подписке Microsoft 365, так как в нем отсутствуют некоторые приложения и не включены обновления функций, но это разовая покупка, поэтому она идеально подходит тем, кому нужны только основные функции.
Посмотреть на Amazon Посмотреть на Microsoft
Представьте, что вы игровой администратор, и у вас есть электронная таблица, содержащая список игроков, их результаты в турнире и их игровой статус. Каждый раз, когда вы добавляете нового игрока в столбец B, вы хотите, чтобы число в столбце A автоматически увеличивалось, чтобы вы могли отслеживать количество активных игроков в любой момент времени.
Чтобы увидеть формулу, которую я использовал в столбце F, и следовать ей по мере чтения этого руководства, загрузите бесплатную копию книги Excel, используемой в примерах. После того, как вы нажмете на ссылку, вы найдете кнопку загрузки в правом верхнем углу экрана.
Автоматическая генерация этих идентификационных номеров избавит вас от необходимости тратить время на их ввод вручную, исключит возможность ошибки и сохранит порядок в вашей таблице. Кроме того, автоматическая нумерация означает, что когда вы удаляете строку, чтобы удалить исключенного игрока из списка, счетчик будет обновляться соответствующим образом.
Прежде чем использовать функции SEQUENCE и COUNTA вместе для создания этого сценария, убедитесь, что данные не отформатированы как таблица Excel. Это связано с тем, что SEQUENCE — это динамическая функция массива, которая создает пролитый массив, а таблицы Excel несовместимы с результатами, полученными таким образом.
Чтобы преобразовать форматированную таблицу Excel в неформатированный диапазон, выберите одну из ячеек в таблице и нажмите «Преобразовать в диапазон» в группе «Инструменты» на вкладке «Конструктор таблиц».
Сначала удалите все числа, которые вы уже ввели автоматически в столбце A. Для этого нажмите Ctrl+Shift+Home, чтобы перейти в ячейку A1, прежде чем использовать клавиши со стрелками для перехода к нужной ячейке. Затем нажмите Ctrl+Shift+Down, чтобы выбрать оставшиеся числа. Теперь нажмите Delete.
Далее, пришло время сгенерировать волшебную формулу в ячейке A2. Сначала позвольте мне объяснить, как это работает.
Функция SEQUENCE делает то, о чем говорит ее название: создает последовательность чисел:
=ПОСЛЕДОВАТЕЛЬНОСТЬ(a,b,c,d)
в котором
- a количество возвращаемых строк,
- b количество возвращаемых столбцов,
- c это начальный номер, и
- d — это шаг между каждым числом.
Однако количество строк, которые вам необходимо вернуть (аргумент a) зависит от количества имен в столбце B. В результате для этого аргумента вам нужно указать Excel подсчитать количество непустых ячеек от B2 вниз. Вот где в игру вступает функция СЧЁТЗ:
=СЧЁТA(e)
в котором
- e — это диапазон значений, которые вы хотите подсчитать.
Имея это в виду, в ячейке A2 введите:
=ПОСЛЕДОВАТЕЛЬНОСТЬ(СЧЁТA(B2:B901)
в котором
- ПОСЛЕДОВАТЕЛЬНОСТЬ это функция, которая сгенерирует список чисел, и
- COUNTA, первый аргумент формулы ПОСЛЕДОВАТЕЛЬНОСТЬ, подсчитывает количество непустых ячеек в диапазоне от B2 до B901.
На этом этапе обратите внимание, что столбцы (аргумент b), начало (аргумент c), и шаг (аргумент d) находятся в квадратных скобках в подсказке функции ПОСЛЕДОВАТЕЛЬНОСТЬ, указывая на то, что эти аргументы необязательны. Если их не указать, по умолчанию они равны 1, поскольку Excel предполагает, что возврат одного столбца чисел, начиная с 1 и увеличиваясь на 1 каждый раз, — это то, что большинство людей, использующих функцию ПОСЛЕДОВАТЕЛЬНОСТЬ, вероятно, захотят вернуть.
И Excel был прав в своем предположении в вашем случае — вы стремитесь создать один столбец чисел, начинающихся с 1 и увеличивающихся на 1 каждый раз.
Итак, закройте круглые скобки, чтобы завершить формулу ПОСЛЕДОВАТЕЛЬНОСТИ:
=ПОСЛЕДОВАТЕЛЬНОСТЬ(СЧЁТA(B2:B901))
и нажмите Ctrl+Enter, чтобы выйти из режима редактирования и остаться в ячейке A2.
Синяя линия вокруг результата напоминает вам, что это выплеснутый массив, то есть результат буквально выплескивается из ячейки, в которую вы ввели формулу.
Однако, поскольку вы ссылаетесь только на ячейки B2-B901 в части формулы COUNTA, количество в столбце A не увеличится, если вы выйдете за пределы этого диапазона. Чтобы исправить это, в формуле COUNTA сошлитесь на весь столбец B и вычтите единицу из общей суммы, чтобы учесть строку заголовка:
=ПОСЛЕДОВАТЕЛЬНОСТЬ(СЧЁТA(B:B)-1)
Теперь, даже если вы введете тысячи новых имен игроков, количество в столбце A продолжит динамически обновляться.
Допустим, GameMaestro и Ghost проиграли матч на выбывание, и вы удалили строки 4 и 6 из таблицы, щелкнув правой кнопкой мыши по заголовкам строк и выбрав «Удалить». Поскольку в столбце B на две ячейки меньше, содержащих данные, функция СЧЁТЗ уменьшает результат ПОСЛЕДОВАТЕЛЬНОСТИ на две строки.
SEQUENCE — не единственная функция динамического массива в Excel, которая автоматически обновляется в соответствии с любыми изменениями в ваших данных. Например, функция FILTER постоянно переупорядочивает диапазон данных на основе заданных вами критериев, функция SORTBY многократно сортирует список на основе независимых данных, а функция PIVOTBY позволяет вам группировать, сортировать, фильтровать и агрегировать данные динамически в одной формуле.
