Como gerar uma lista das possíveis combinações de dados no Excel

Question

Eu quero listar as possíveis combinações de dados 2,3,4,5,6, etc.

Por exemplo:

Para 2 dados, a célula A1 é 11 (1 & 1). Eu gostaria de arrastar a célula para baixo para adicionar automaticamente os seguintes valores de 2-dados:

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

(Um total de 21 combinações)

Para listar todos os valores de N, começamos com os valores mínimos (para N = 3: 1,1,1). Eu só queria arrastar aquela primeira célula e os próximos números aparecerem como a próxima combinação dos dados; i.e., para o Excel incrementar usando BASE 6, não 10, e excluir combinações que já apareceram em uma seqüência diferente. Eu quero ter os valores resultantes como uma string. Então, se um dos números (4 dados) for 1126, o próximo deve ser 1133, e não 1127.

Isso é possível para 2,3,4,5,6 dados?

    

Answer 1

Isso provavelmente seria mais fácil com o VBA, mas isso pode ser feito com fórmulas. Eu estou trabalhando no LibreOffice Calc, que tem um máximo de caracteres por fórmula, então eu precisava usar colunas auxiliares. Mas você pode consolidar isso em uma única fórmula, se quiser. Eu construí isso em torno de um máximo de seis dados, mas se você seguir o padrão nas colunas auxiliares, você pode expandi-lo para quantos você quiser.

AcélulaA1éondeestáoseunúmeroinicial.Normalmente,seriaum1paracadadado.Comeceicom123456parailustraralógica.AscolunasCaHsãoascolunasauxiliares,umaparacadaumdosseisdados.Essascélulascalculamopróximovalorparacadaumadelas.AcolunaAconcatenaosvaloresemumaúnicastring.Digiteasfórmulasparaalinha2e,emseguida,copiealinhaparapré-preencherquantosforemnecessários(ascélulasdesnecessáriasficarãoembrancoevocêpoderáocultarascolunasC:H,sedesejar).

AfórmulaemA2:

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

O teste para o branco é o que esconde as células desnecessárias. Se você quiser transformar tudo em uma única fórmula, substitua as fórmulas em C2: H2 pelas referências.

As fórmulas em C2: H2 são as seguintes:

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))

Adicionei espaços para alinhar os padrões de fórmula para facilitar a visualização da lógica. você pode remover isso. Você tem no mínimo dois dados, portanto, as duas primeiras fórmulas não precisam testar se estão presentes. Quando o primeiro dado atingir 6 , todos os outros podem ser apenas 6 , por isso é a última linha. A função OR em C2 é porque o LO Calc não confirmou a avaliação da expressão booleana; o OR o força (e não faz mal a nada). O último dado potencial não precisa transmitir um valor de um próximo, então sua fórmula é um pouco mais curta.

Observe que as colunas D a H incluem uma referência à coluna anterior. Se você quiser consolidar isso em uma única fórmula, substitua a referência C2 em D2 pela fórmula C2. Em seguida, faça o mesmo para cada coluna sucessiva (a fórmula aumentará conforme você faz isso).

Aqui está a saída para dois dados:

    

Answer 2

Em A1 digite:

=ROW()

e copie para baixo. Em B1 digite:

=ROW()*6

e copie para baixo. Em C1 , digite:

=B1-A1+1

e copie para baixo.

A coluna C representa os valores possíveis para N dados.

Vale a pena notar que o número de resultados possíveis é:

(5 * o número de dados) + 1

    

Answer 3

Você pode fazer isso com fórmulas, mas por mais de 2 dados, será muito difícil de seguir.

Este é um exemplo genérico, ele funcionará para dados ou para qualquer outro exemplo. Digamos que temos 3 tabelas (Tabela1 = A1: A7), (Tabela2 = B1: B7) e (Tabela3 = C1: C7). Primeira linha é o cabeçalho (digamos dice1, dice2 e dice3).

Você terá que copiar as fórmulas até obter resultados vazios (número máximo de combinações atingidas).

(Pode ser necessário substituir ";" por "," em fórmulas como separador de parâmetros)

Para 2 dados (36 combinações):

=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))

Para 3 dados (216 combinações):

=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)

Como funciona:

1. O número da combinação atual será dado por ROW()-ROW($F$2) + 1 , em que F2 é a célula em que você coloca a primeira fórmula.

2. Giraremos os primeiros valores de dados a cada 6 linhas ( COUNTA(Table2) ) com dados e a cada 36 linhas ( COUNTA(Table1)*COUNTA(Table2) ) com 3 dados.

3. Giraremos o segundo valor de dados a cada linha com 2 dados e a cada 6 linhas com 3 dados.

4. Com 3 dados, giraremos o valor dos dados a cada linha.

Um método alternativo seria o Microsoft Query (Data / From Other Sources). Você precisaria criar quantas folhas ou tabelas (embora com as tabelas eu recebo "Esta fonte de dados não contém tabelas visíveis" e eu não consigo descobrir o porquê, assim as folhas são) contendo valores da coluna A de 1 a 6, com cabeçalhos como "Dice1", "Dice2" e "Dice3". Para obter o produto cartesiano dos 3 conjuntos, a consulta seria:

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

Será mais difícil de preparar, mas mais fácil de entender (se você conhece SQL básico) e expandir.