Генерация всех комбинаций с помощью функции ОСНОВАНИЕ (BASE) в Excel
Функция ОСНОВАНИЕ (BASE) в Excel — это мощный инструмент не только для работы с системами счисления, но и для решения практических задач, таких как генерация всех возможных комбинаций сценариев. Этот метод незаменим при анализе рисков, тестировании моделей или планировании, когда нужно перебрать все варианты.
Пример ниже использует динамические массивы и функцию ПОСЛЕДОВ (SEQUENCE), доступную в Microsoft 365, Excel 2021 и 2024, включая поддерживающие её версии для Mac и веба. В Excel 2016 и 2019 ОСНОВАНИЕ есть, а ПОСЛЕДОВ нет: последовательность индексов нужно заполнить и формулы скопировать обычным способом.
Представьте, что у вас есть четыре производственные линии, и на каждой возможны три различных сценария развития событий. Вам необходимо проанализировать все комбинации от 1-1-1-1 до 3-3-3-3. Всего получится 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. Третий необязательный аргумент задает минимальную длину результата.
Хотя в бухгалтерии мы редко выходим за пределы десятичной системы, ОСНОВАНИЕ (BASE) блестяще справляется с генерацией комбинаций.
Пошаговая инструкция по генерации комбинаций
- Задайте параметры. В ячейках укажите количество столбцов (например, 4) и вариантов в каждом (например, 3). В ячейке A5 рассчитайте общее число комбинаций: Русская версия:
=B2^B1English version:=B2^B1(где B2 — варианты, B1 — столбцы). - Создайте последовательность чисел. В ячейке A7 сгенерируйте массив чисел от 0 до (N−1), где N — общее количество комбинаций. Используйте
ПОСЛЕДОВ (SEQUENCE): Русская версия:=ПОСЛЕДОВ(A5;1;0)English version:=SEQUENCE(A5,1,0). - Примените функцию ОСНОВАНИЕ (BASE). Замените формулу в A7 следующей формулой, которая преобразует последовательность в систему счисления с основанием, равным числу вариантов (3), и длиной, равной числу столбцов (4). Формула: Русская версия:
=ОСНОВАНИЕ(ПОСЛЕДОВ(A5;1;0); B2; B1)English version:=BASE(SEQUENCE(A5,1,0), B2, B1). Результат будет в цифрах 0, 1, 2. - Скорректируйте диапазон. Для показанного случая — ровно четыре позиции и три варианта в каждой — замените формулу в A7 вариантом с прибавлением 1111. Текстовые троичные записи 0000–2222 при этом преобразуются в десятичные числа 1111–3333 с цифрами 1–3: Русская версия:
=ОСНОВАНИЕ(ПОСЛЕДОВ(A5;1;0); B2; B1) + 1111English version:=BASE(SEQUENCE(A5,1,0), B2, B1) + 1111.
- Разбейте комбинацию по столбцам. Чтобы разделить 4-значное число на отдельные столбцы, используйте функцию
ПСТР / MIDв сочетании сПОСЛЕДОВ (SEQUENCE). В ячейке B7 введите формулу и скопируйте только вниз до B87; каждая строка сама заполнит четыре столбца B:E. Эти ячейки должны быть свободны:Где $A7 — ячейка с комбинацией, а $B$1 — количество столбцов. Формула извлечёт цифры как текст. Для числового результата добавьтеРусская версия:=ПСТР($A7; ПОСЛЕДОВ(1; $B$1); 1)English version:=MID($A7, SEQUENCE(1, $B$1), 1)+0в конец формулы.
Практические советы и заключение
- Используйте абсолютные ссылки (
$B$1) для параметров, чтобы формулы можно было легко копировать. - Для визуализации или дальнейшего анализа сгенерированные данные можно превратить в таблицу Excel (Ctrl+T), предварительно скопировав результат и вставив его как значения в свободное место. Полученную таблицу можно использовать как источник сводной таблицы.
- Полный перебор растёт экспоненциально и ограничен числом строк листа, памятью и точностью чисел. ОСНОВАНИЕ принимает целое число от 0 до 2^53−1, основание 2–36 и минимальную длину до 255. Прибавление 1111 относится к показанному случаю: при изменении числа позиций добавку нужно менять; для оснований 10 и выше такой способ перенумерации цифр не подходит. Разливающиеся формулы должны находиться вне таблицы Excel.
Комбинация функций ОСНОВАНИЕ (BASE) и ПОСЛЕДОВ (SEQUENCE) открывает элегантный способ решения задачи полного перебора. Вместо сложных вложенных циклов или макросов вы получаете динамическую, формульную модель, которую можно адаптировать к другим задачам с учётом ограничений основания, длины и объёма результата. Добавьте этот прием в свой арсенал для сложного аналитического моделирования в Excel.