Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Формулы и функции

Генерация всех комбинаций с помощью функции BASE в Excel

forest_paw 03.04.2026 823

Генерация всех комбинаций с помощью функции ОСНОВАНИЕ (BASE) в Excel

Функция ОСНОВАНИЕ (BASE) в Excel — это мощный инструмент не только для работы с системами счисления, но и для решения практических задач, таких как генерация всех возможных комбинаций сценариев. Этот метод незаменим при анализе рисков, тестировании моделей или планировании, когда нужно перебрать все варианты.

Пример ниже использует динамические массивы и функцию ПОСЛЕДОВ (SEQUENCE), доступную в Microsoft 365, Excel 2021 и 2024, включая поддерживающие её версии для Mac и веба. В Excel 2016 и 2019 ОСНОВАНИЕ есть, а ПОСЛЕДОВ нет: последовательность индексов нужно заполнить и формулы скопировать обычным способом.

Представьте, что у вас есть четыре производственные линии, и на каждой возможны три различных сценария развития событий. Вам необходимо проанализировать все комбинации от 1-1-1-1 до 3-3-3-3. Всего получится 81 упорядоченный набор; формулы позволяют перечислить их автоматически. Повторения вариантов разрешены, а порядок производственных линий имеет значение.

Цель — расположить числа от 1 до 3 в четырех столбцах. Формула =3^4 показывает, что получится 81 комбинация.
Цель — расположить числа от 1 до 3 в четырех столбцах. Формула =3^4 показывает, что получится 81 комбинация.

Как рассчитать количество комбинаций

Сначала определите общее число вариантов. Если на каждой линии 3 сценария, а линий 4, то общее количество комбинаций равно 3 в степени 4, то есть 81. Это легко рассчитать формулой Русская версия: =3^4English version: =3^4 или Русская версия: =СТЕПЕНЬ(3;4)English version: =POWER(3,4).

Суть функции ОСНОВАНИЕ (BASE)

Функция ОСНОВАНИЕ (BASE) преобразует число из десятичной системы в указанное основание (от 2 до 36). Например, Русская версия: =ОСНОВАНИЕ(49;2)English version: =BASE(49,2) переведет число 49 в двоичный вид — 110001. Третий необязательный аргумент задает минимальную длину результата.

Число 49 в ячейке B2. Разные формулы преобразуют его в двоичный код. =BASE(B2,2,8) возвращает 8-значный результат 00110001.
Число 49 в ячейке B2. Разные формулы преобразуют его в двоичный код. =ОСНОВАНИЕ(B2;2;8) возвращает 8-значный результат 00110001.

Хотя в бухгалтерии мы редко выходим за пределы десятичной системы, ОСНОВАНИЕ (BASE) блестяще справляется с генерацией комбинаций.

Пошаговая инструкция по генерации комбинаций

  1. Задайте параметры. В ячейках укажите количество столбцов (например, 4) и вариантов в каждом (например, 3). В ячейке A5 рассчитайте общее число комбинаций: Русская версия: =B2^B1English version: =B2^B1 (где B2 — варианты, B1 — столбцы).
  2. Создайте последовательность чисел. В ячейке A7 сгенерируйте массив чисел от 0 до (N−1), где N — общее количество комбинаций. Используйте ПОСЛЕДОВ (SEQUENCE): Русская версия: =ПОСЛЕДОВ(A5;1;0)English version: =SEQUENCE(A5,1,0).
  3. Примените функцию ОСНОВАНИЕ (BASE). Замените формулу в A7 следующей формулой, которая преобразует последовательность в систему счисления с основанием, равным числу вариантов (3), и длиной, равной числу столбцов (4). Формула: Русская версия: =ОСНОВАНИЕ(ПОСЛЕДОВ(A5;1;0); B2; B1)English version: =BASE(SEQUENCE(A5,1,0), B2, B1). Результат будет в цифрах 0, 1, 2.
  4. Скорректируйте диапазон. Для показанного случая — ровно четыре позиции и три варианта в каждой — замените формулу в A7 вариантом с прибавлением 1111. Текстовые троичные записи 0000–2222 при этом преобразуются в десятичные числа 1111–3333 с цифрами 1–3: Русская версия: =ОСНОВАНИЕ(ПОСЛЕДОВ(A5;1;0); B2; B1) + 1111English version: =BASE(SEQUENCE(A5,1,0), B2, B1) + 1111.
Формула генерирует все 81 возможную комбинацию в одном столбце.
Формула генерирует все 81 возможную комбинацию в одном столбце.
  1. Разбейте комбинацию по столбцам. Чтобы разделить 4-значное число на отдельные столбцы, используйте функцию ПСТР / MID в сочетании с ПОСЛЕДОВ (SEQUENCE). В ячейке B7 введите формулу и скопируйте только вниз до B87; каждая строка сама заполнит четыре столбца B:E. Эти ячейки должны быть свободны:
    Русская версия:
    =ПСТР($A7; ПОСЛЕДОВ(1; $B$1); 1)
    English version:
    =MID($A7, SEQUENCE(1, $B$1), 1)
    Где $A7 — ячейка с комбинацией, а $B$1 — количество столбцов. Формула извлечёт цифры как текст. Для числового результата добавьте +0 в конец формулы.
Формула =MID(A7,SEQUENCE(1,4),1) разбивает четырехзначное число на четыре столбца.
Формула =ПСТР(A7;ПОСЛЕДОВ(1;4);1) разбивает четырехзначное число на четыре столбца.

Практические советы и заключение

  • Используйте абсолютные ссылки ($B$1) для параметров, чтобы формулы можно было легко копировать.
  • Для визуализации или дальнейшего анализа сгенерированные данные можно превратить в таблицу Excel (Ctrl+T), предварительно скопировав результат и вставив его как значения в свободное место. Полученную таблицу можно использовать как источник сводной таблицы.
  • Полный перебор растёт экспоненциально и ограничен числом строк листа, памятью и точностью чисел. ОСНОВАНИЕ принимает целое число от 0 до 2^53−1, основание 2–36 и минимальную длину до 255. Прибавление 1111 относится к показанному случаю: при изменении числа позиций добавку нужно менять; для оснований 10 и выше такой способ перенумерации цифр не подходит. Разливающиеся формулы должны находиться вне таблицы Excel.

Комбинация функций ОСНОВАНИЕ (BASE) и ПОСЛЕДОВ (SEQUENCE) открывает элегантный способ решения задачи полного перебора. Вместо сложных вложенных циклов или макросов вы получаете динамическую, формульную модель, которую можно адаптировать к другим задачам с учётом ограничений основания, длины и объёма результата. Добавьте этот прием в свой арсенал для сложного аналитического моделирования в Excel.

Поделиться:

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