Para expandir uma matriz de quantidades de recursos gráficos em uma linha da planilha por item, primeiro liste cada combinação de recurso e formato com uma quantidade acumulada. Em seguida, use SEQUENCE e XLOOKUP para repetir os rótulos o número de vezes necessário. Este passo a passo usa rótulos de obras de arte fictícias.

A tarefa é simples: transformar uma grade compacta de planejamento em uma lista de itens. Uma quantidade igual a dois gera duas linhas com o mesmo recurso e formato; zero não gera nenhuma. O exemplo abaixo mantém os cálculos intermediários visíveis para que você possa conferir o resultado antes de adaptá-lo.

Quais versões do Excel para Mac são compatíveis com este método?

Use o Excel para Microsoft 365 para Mac, o Excel 2024 para Mac ou o Excel 2021 para Mac. A documentação da Microsoft lista essas edições para SEQUENCE e XLOOKUP, e o guia de recursos do Excel 2021 para Mac documenta explicitamente matrizes dinâmicas. Função SEQUENCE, Função XLOOKUP, Novidades do Excel 2021 para Mac.

A disponibilidade das funções é importante ao copiar fórmulas de outros tutoriais. Por exemplo, a Microsoft lista VSTACK para o Microsoft 365 e o Excel 2024 no Mac, mas não para o Excel 2021. O método aqui evita essa dependência. A Microsoft também declara explicitamente que XLOOKUP não está disponível no Excel 2016 nem no Excel 2019, portanto essas edições não conseguem executar as fórmulas abaixo sem alterações. Função VSTACK, Função XLOOKUP.

Configure a matriz de quantidades de recursos gráficos

Em uma planilha em branco, insira os seguintes dados fictícios em A1:C4. A primeira coluna contém os rótulos dos recursos, a linha de cabeçalho contém os formatos e as células restantes contêm as quantidades.

Recurso Pequeno Grande
Mosaico Arco 2 0
Mosaico Ponto 1 0
Mosaico Onda 2 1

Neste exemplo, cada quantidade deve ser um número inteiro igual ou superior a zero. Insira explicitamente zero quando nenhum item for necessário. Resolva células vazias, quantidades em texto, valores negativos, frações e erros de célula antes de prosseguir. Estas fórmulas pressupõem contagens válidas; elas não as validam nem arredondam.

O resultado esperado contém seis linhas de itens: dois Mosaicos Arco Pequenos, um Mosaico Ponto Pequeno, dois Mosaicos Onda Pequenos e um Mosaico Onda Grande. A repetição dos rótulos é intencional, pois cada linha representa um item. As fórmulas criam registros na planilha; elas não criam nem duplicam arquivos de arte.

Transforme a matriz em uma lista auxiliar que possa ser conferida

Deixe a coluna D vazia. Em E1:I1, insira os cabeçalhos Posição, Recurso, Formato, Quantidade e Item final. Insira os números de um a seis em E2:E7, em ordem. São necessárias seis linhas auxiliares porque três recursos multiplicados por dois formatos resultam em seis combinações.

A lista auxiliar lê cada linha de origem da esquerda para a direita antes de avançar para baixo: Mosaico Arco Pequeno, Mosaico Arco Grande, depois as duas combinações do Mosaico Ponto, seguidas pelas duas combinações do Mosaico Onda. É essencial manter essa ordem consistente.

Recupere os rótulos e a quantidade de cada combinação

Insira esta fórmula em F2:

=INDEX($A$2:$A$4,INT((E2-1)/2)+1)

Insira esta fórmula em G2:

=INDEX($B$1:$C$1,1,MOD(E2-1,2)+1)

Insira esta fórmula em H2:

=INDEX($B$2:$C$4,INT((E2-1)/2)+1,MOD(E2-1,2)+1)

Copie F2:H2 até a linha sete. São fórmulas comuns em células, com um resultado por célula. INDEX recupera um valor em uma linha e coluna especificadas. Aqui, INT faz a linha de origem avançar a cada duas posições, enquanto MOD alterna a coluna de origem entre os dois formatos. Função INDEX, Função INT, Função MOD.

Calcule o número do item final de cada combinação

Em I2, insira a seguinte fórmula e copie-a até I7:

=SUM($H$2:H2)

A referência inicial fixa e a referência final móvel criam um total acumulado. SUM soma as quantidades da primeira linha auxiliar até a linha atual. Função SUM.

A lista auxiliar concluída deve ficar assim:

Posição Recurso Formato Quantidade Item final
1 Mosaico Arco Pequeno 2 2
2 Mosaico Arco Grande 0 2
3 Mosaico Ponto Pequeno 1 3
4 Mosaico Ponto Grande 0 3
5 Mosaico Onda Pequeno 2 5
6 Mosaico Onda Grande 1 6

Interprete Item final como um limite. A primeira combinação ocupa os números de item um e dois. A terceira combinação ocupa o número de item três. A quinta ocupa os números quatro e cinco, e a sexta ocupa o número seis. Uma quantidade igual a zero não faz o limite avançar, portanto não recebe números de item.

Gere uma linha de saída por item

Em K1, insira Recurso; em L1, insira Formato. Deixe vazias as células abaixo dos dois cabeçalhos. Coloque a saída em células comuns da planilha, fora de uma tabela do Excel: a Microsoft documenta que fórmulas de matriz com derramamento não são compatíveis com tabelas. Fórmulas de matriz dinâmica e comportamento de matrizes derramadas.

Insira esta fórmula completa em K2:

=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$F$2:$F$7,,1,1))

Insira esta fórmula completa em L2:

=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$G$2:$G$7,,1,1))

Confirme cada fórmula com Return. Não copie essas duas fórmulas para baixo: os resultados de matriz delas preenchem automaticamente as linhas adjacentes. A Microsoft descreve essa expansão automática como derramamento. Fórmulas de matriz dinâmica e comportamento de matrizes derramadas.

SEQUENCE gera os números de item de um até a quantidade total. Para cada número, XLOOKUP pesquisa os limites acumulados. Os dois últimos argumentos especificam uma correspondência exata ou o próximo valor maior, com pesquisa do primeiro ao último. Essa direção de pesquisa preserva o primeiro limite correspondente quando zeros criam totais repetidos. Função SEQUENCE, Função XLOOKUP.

A função IF externa retorna texto vazio quando o total é zero. Nesse caso, as duas células âncora parecem vazias e não há registros de itens para usar. Essa é uma condição deliberada de total zero, usando IF para escolher entre dois resultados. Função IF.

Para a matriz fornecida, K1:L7 deve conter:

Recurso Formato
Mosaico Arco Pequeno
Mosaico Arco Pequeno
Mosaico Ponto Pequeno
Mosaico Onda Pequeno
Mosaico Onda Pequeno
Mosaico Onda Grande

As duas colunas de saída usam os mesmos números de item gerados e o mesmo intervalo de limites. Apenas o intervalo de retorno muda: a coluna F fornece os rótulos dos recursos, e a coluna G fornece os formatos. Mantenha sincronizadas essas entradas de pesquisa ao editar as fórmulas.

Confira o resultado antes de ampliar os dados de origem

Faça três verificações com os dados de exemplo:

  1. Compare os totais: =SUM(B2:C4) e =SUM(H2:H7) devem retornar seis.
  2. Compare as combinações: a saída deve conter as quatro combinações diferentes de zero com as respectivas quantidades de repetição indicadas.
  3. Altere B2 de dois para três: o resultado esperado passa a ter sete linhas, com três entradas de Mosaico Arco Pequeno. Depois, restaure o valor para dois.

A primeira verificação detecta quantidades omitidas ou duplicadas durante a transformação em lista. A segunda detecta rótulos desalinhados, o que uma simples comparação de totais não revela. A terceira verifica se as alterações percorrem a lista auxiliar até chegar à saída.

Adicione mais recursos ou formatos de forma deliberada

Alterar as quantidades em B2:C4 usa a configuração existente. Para aumentar as dimensões da matriz, é preciso alterar as referências e o número de linhas da lista auxiliar.

Por exemplo, amplie os dados de origem para quatro recursos e três formatos em A1:D5. Mova ou mantenha a área auxiliar em E:I, insira as posições de um a doze em E2:E13 e substitua as fórmulas em F2:H2 por:

=INDEX($A$2:$A$5,INT((E2-1)/3)+1)

=INDEX($B$1:$D$1,1,MOD(E2-1,3)+1)

=INDEX($B$2:$D$5,INT((E2-1)/3)+1,MOD(E2-1,3)+1)

Copie essas fórmulas até a linha treze e estenda a fórmula do total acumulado na coluna I até a mesma linha. Nas duas fórmulas de saída, substitua toda referência de intervalo auxiliar que termine na linha sete por uma que termine na linha treze. O divisor e a base do resto passam a ser três, pois cada recurso tem três formatos. Confira novamente o total de origem e cada combinação depois de fazer essas alterações.

Resolva uma saída incompleta ou bloqueada

Se o Excel exibir #SPILL!, verifique se há entradas existentes ou células mescladas na área de saída prevista e confirme que a fórmula está fora de uma tabela do Excel. Um resultado que ultrapasse a borda da planilha também não pode ser derramado. A Microsoft documenta cada uma dessas condições. Como corrigir um erro #SPILL!.

Se a saída tiver poucas linhas, confira as quantidades antes de alterar a lógica de pesquisa. A Microsoft informa que SUM ignora texto nos intervalos referenciados. Portanto, uma quantidade armazenada como texto pode ser omitida do total. Corrija o valor de origem e repita a verificação do total. Função SUM.

Se os rótulos estiverem incorretos apesar de o total estar certo, compare a lista auxiliar com a matriz célula por célula. Mantenha juntos, em cada linha auxiliar, o recurso, o formato, a quantidade e o limite acumulado. Não reorganize uma coluna auxiliar de forma independente.

Quando os totais e as combinações estiverem de acordo, use as duas colunas de saída como lista de itens. Mantenha disponíveis a matriz e os cálculos auxiliares para que as alterações posteriores nas quantidades continuem rastreáveis até as linhas repetidas.

Fontes