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

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

Если вам интересны Python в Excel, Copilot и AI-инструменты, материал поможет понять, как использовать их в реальных рабочих сценариях.

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

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

Сначала определите общее число вариантов. Если на каждой линии 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 блестяще справляется с генерацией комбинаций.

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

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. Преобразуйте эту последовательность в систему счисления с основанием, равным числу вариантов (3), и длиной, равной числу столбцов (4). Формула: Русская версия: =ОСНОВАНИЕ(ПОСЛЕДОВ(A5;1;0); B2; B1)English version: =BASE(SEQUENCE(A5,1,0), B2, B1). Результат будет в цифрах 0, 1, 2.
4. Скорректируйте диапазон. Чтобы получить комбинации от 1111 до 3333, просто прибавьте 1111 к результату: Русская версия: =ОСНОВАНИЕ(ПОСЛЕДОВ(A5;1;0); B2; B1) + 1111English version: =BASE(SEQUENCE(A5,1,0), B2, B1) + 1111.

5. Разбейте комбинацию по столбцам. Чтобы разделить 4-значное число на отдельные столбцы, используйте функцию ПСТР / MID в сочетании с ПОСЛЕДОВ / SEQUENCE. В ячейке B7 введите формулу и растяните ее на нужный диапазон:
   Русская версия:

   =ПСТР($A7; ПОСЛЕДОВ(1; $B$1); 1)

   English version:

   =MID($A7, SEQUENCE(1, $B$1), 1)

   Где $A7 — ячейка с комбинацией, а $B$1 — количество столбцов. Эта формула извлечет каждую цифру в отдельную ячейку.

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

• Используйте абсолютные ссылки ($B$1) для параметров, чтобы формулы можно было легко копировать.
• Для визуализации или дальнейшего анализа сгенерированные данные можно превратить в Таблицу Excel (Ctrl+Т / T) или использовать в сводных таблицах.
• Этот метод масштабируется на любое количество столбцов и вариантов, ограниченное только производительностью Excel.

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