Как создать список возможных комбинаций N кубиков в Excel

Question

Я хочу перечислить возможные комбинации из 2,3,4,5,6 и т.д. кубиков.

Например:

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

12
13
14
15
16
22
23
24
...
55
56
66

(Всего 21 комбинация)

Чтобы перечислить все значения N кубиков, мы начнем с минимальных значений (для N = 3: 1,1,1). Я просто хотел перетащить эту первую ячейку, чтобы следующие числа отображались как следующая комбинация игральных костей; т.е. для Excel, чтобы увеличить с использованием BASE 6, а не 10, и исключить комбинации, которые уже появились в другой последовательности. Я хочу иметь результирующие значения в виде строки. Итак, если одно из чисел (4 кубика) будет 1126, следующее должно быть 1133, а не 1127.

Это достижимо для 2,3,4,5,6 кубиков?

Answer 1

В А1 введите:

=ROW()

и скопировать вниз. В B1 введите:

=ROW()*6

и скопировать вниз. В С1 введите:

=B1-A1+1

и скопировать вниз.

Столбец C представляет возможные значения для N кубиков.

Стоит отметить, что количество возможных результатов составляет:

(5 * количество кубиков) + 1

Answer 2

Это, вероятно, будет проще с VBA, но это можно сделать с помощью формул. Я работаю в LibreOffice Calc, у которого максимальное количество символов в формуле меньше, поэтому мне нужно было использовать вспомогательные столбцы. Но вы можете объединить это в одну формулу, если хотите. Я построил это максимум из шести кубиков, но если вы будете следовать шаблону в столбцах помощника, вы можете расширить его до необходимого количества.

Ячейка A1 - это то место, куда идет ваш стартовый номер. Обычно это будет 1 за каждый кубик. Я начал с 123456 чтобы проиллюстрировать логику. Столбцы с C по H являются вспомогательными столбцами, по одному на каждую из шести кубиков. Эти клетки вычисляют следующее значение для каждого. Столбец А объединяет значения в одну строку. Введите формулы для строки 2, а затем скопируйте строку вниз, чтобы предварительно заполнить столько, сколько вам нужно (ненужные ячейки будут пустыми, и вы можете скрыть столбцы C:H, если хотите).

Формула в А2:

=IF(A1="","",C2&D2&E2&F2&G2&H2)

Тест на бланк - это то, что скрывает ненужные ячейки. Если вы хотите превратить все в одну формулу, замените формулы в C2:H2 ссылками.

Формулы в C2:H2 следующие:

C2:  =                IF(VALUE(LEFT(A1,1))=6,"", VALUE(LEFT(A1,1)) +          OR(VALUE(MID(A1,2,1))=6))
D2:  =                IF(VALUE(MID(A1,2,1))=6,C2,VALUE(MID(A1,2,1))+IF(LEN(A1)>2,VALUE(MID(A1,3,1))=6,1))
E2:  =IF(LEN(A1)<3,"",IF(VALUE(MID(A1,3,1))=6,D2,VALUE(MID(A1,3,1))+IF(LEN(A1)>3,VALUE(MID(A1,4,1))=6,1)))
F2:  =IF(LEN(A1)<4,"",IF(VALUE(MID(A1,4,1))=6,E2,VALUE(MID(A1,4,1))+IF(LEN(A1)>4,VALUE(MID(A1,5,1))=6,1)))
G2:  =IF(LEN(A1)<5,"",IF(VALUE(MID(A1,5,1))=6,F2,VALUE(MID(A1,5,1))+IF(LEN(A1)>5,VALUE(MID(A1,6,1))=6,1)))
H2:  =IF(LEN(A1)<6,"",IF(VALUE(MID(A1,6,1))=6,G2,VALUE(MID(A1,6,1))+1))

Я добавил пробелы для выравнивания шаблонов формул, чтобы было легче увидеть логику; Вы можете удалить их. У вас есть минимум две кости, поэтому первые две формулы не нужно проверять, присутствуют ли они. Когда первый кубик достигает 6 , всем остальным может быть только 6 , так что это последний ряд. Функция OR в C2 вызвана тем, что LO Calc отказался от вычисления логического выражения; ИЛИ форсирует это (и ничего не ранит). Последний потенциальный кубик не должен переносить значение из следующего, поэтому его формула немного короче.

Обратите внимание, что столбцы с D по H содержат ссылку на предыдущий столбец. Если вы хотите объединить это в одну формулу, замените ссылку C2 в D2 формулой C2. Затем сделайте то же самое для каждого последующего столбца (формула будет расти при этом).

Вот вывод для двух костей:

Answer 3

Вы можете сделать это с помощью формул, но для более чем 2 кубиков будет довольно трудно следовать.

Это общий пример, он будет работать для кубиков или для любого другого примера. Допустим, у нас есть 3 таблицы (Таблица1 = A1:A7), (Таблица2 = B1:B7) и (Таблица3 = C1:C7). Первая строка - заголовок (скажем, dice1, dice2 и dice3).

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

(Вам может понадобиться заменить «;» на «,» в формулах в качестве разделителя параметров)

Для 2 кубиков (36 комбинаций):

=IF(ROW()-ROW($F$2)+1>COUNTA(Table1)*COUNTA(Table2);"";INDEX(Table1;QUOTIENT(ROW()-ROW($F$2);COUNTA(Table2))+1)&INDEX(Table2;MOD(ROW()-ROW($F$2);COUNTA(Table2))+1))

Для 3 кубиков (216 комбинаций):

=IF(ROW()-ROW($F$2)+1>COUNTA(Table1)*COUNTA(Table2)*COUNTA(Table3);"";INDEX(Table1;QUOTIENT(ROW()-ROW($G$2);COUNT(Table2)*COUNT(Table3))+1))&INDEX(Table2;MOD(QUOTIENT(ROW()-ROW($G$2);COUNT(Table3))+1;COUNT(Table3))+1)&INDEX(Table3;MOD(ROW()-ROW($G$2);COUNT(Table3))+1)

Как это работает:

1. Текущий номер комбинации будет задан как ROW()-ROW($F$2) + 1 где F2 - это ячейка, в которую вы помещаете первую формулу.

2. Мы будем вращать значения первых кубиков каждые 6 строк (COUNTA(Table2) (Таблица2) ) с на кубики и каждые 36 строк (COUNTA(Table1)*COUNTA(Table2) ) с 3 кубиками.

3. Мы будем вращать значения второй кости в каждой строке с 2 кубиками и каждые 6 строк с 3 кубиками.

4. С 3 кубиками мы будем вращать значение кубика в каждой строке.

Альтернативный метод будет Microsoft Query (Данные / Из других источников). Вам нужно будет создать как можно больше листов или таблиц (хотя с таблицами я получаю "Этот источник данных не содержит видимых таблиц", и я не могу понять, почему, так что листы есть), содержащие в столбце A значения от 1 до 6, с такие заголовки, как "Dice1", "Dice2" и "Dice3". Чтобы получить декартово произведение трех наборов, запрос будет:

SELECT `Sheet1$`.Dice1 & `Sheet2$`.Dice2 & `Sheet3$`.Dice3 as  Combinations
FROM `Book1.xlsx`.`Sheet1$` `Sheet1$`, `Book1.xlsx`.`Sheet2$` `Sheet2$`, `Book1.xlsx`.`Sheet3$` `Sheet3$`

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