Fórmulas 3D no Excel – Como elas funcionam e como utilizá-las?

Na publicação de hoje vamos ver como as fórmulas 3D no Excel funcionam e como elas podem te ajudar nas tarefas rotineiras.

Fórmulas 3D no Excel são fórmulas que buscam a mesma célula em várias abas de uma vez, como =SOMA(‘Mês 1:Mês 6’!B2). Você seleciona a célula na primeira aba, segura SHIFT e clica na guia da última: o Excel soma todas as abas do intervalo. Com asterisco no nome, entram só as abas de um padrão.

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

O que você aprende neste vídeo

Resposta rápida: A aula compara três jeitos de somar a B2 de seis abas: clicando aba por aba com ;, pelo intervalo 'Mês 1:Mês 6'!B2 criado com Shift e pelo curinga '*Mês*'!B2. Você vê por que o intervalo soma qualquer aba posicionada no meio e como o asterisco a deixa de fora; o Excel resolve o curinga ao confirmar a fórmula.

Neste vídeo (12 min):

• 1:03 - A planilha de exemplo: seis abas, de Mês 1 a Mês 6, com o faturamento semanal e uma aba Resumo para consolidar
• 1:47 - Método manual: =SOMA clicando na B2 de cada aba, uma por uma, separadas por ;
• 2:52 - Total de R$ 38.265 na semana 1 e duplo clique na alça de preenchimento para levar a fórmula às outras semanas
• 3:19 - Por que selecionar aba por aba não escala: com 50 meses seriam horas só de cliques
• 4:06 - Fórmula 3D: clicar na B2 do Mês 1, segurar Shift e clicar na aba Mês 6
• 4:27 - Os dois-pontos em 'Mês 1:Mês 6'!B2 significam até: a B2 de todas as abas do intervalo
• 4:49 - Conferir a fórmula pronta com F2, ou Fn + F2 dependendo do teclado
• 5:39 - O risco do intervalo: uma Planilha1 arrastada para entre Mês 3 e Mês 4, com 10 mil na B2, entra na soma do Resumo
• 7:04 - Método 3: somar só as abas cujo nome contém a palavra Mês
• 8:31 - Apagar o número da aba e pôr asterisco antes e depois do nome: =SOMA('*Mês*'!B2)
• 9:09 - O total volta ao valor certo, ignorando a Planilha1, e o Excel reescreve a referência com os intervalos reais de abas
• 10:00 - O mesmo curinga com =MÉDIA('*Mês*'!B2); a referência 3D vale também para CONT.NÚM, MÁXIMO e MÍNIMO

Trechos do vídeo:

• Numa referência 3D, os dois-pontos entre os nomes das abas significam até: 'Mês 1:Mês 6'!B2 pega a célula B2 de todas as abas posicionadas entre Mês 1 e Mês 6
• O intervalo de abas é definido pela posição delas no arquivo, não pelo nome: qualquer aba colocada no meio dele entra no cálculo, mesmo sem relação com o assunto
• Com asterisco antes e depois do texto, como em '*Mês*'!B2, o Excel troca o curinga, no momento em que a fórmula é confirmada, pelos intervalos das abas cujo nome contém aquela palavra; aba criada ou movida depois não passa pelo filtro de novo
• A referência a várias abas não é exclusiva da soma: funciona também com MÉDIA, MÁXIMO, MÍNIMO, CONT.NÚM e CONT.VALORES, mas não com CONT.SE nem SOMASE
• Pronta na primeira linha, a fórmula 3D se estende às demais com duplo clique na alça de preenchimento, como qualquer outra fórmula

Para baixar a planilha utilizada nesta publicação, clique aqui!

O que são as Fórmulas 3D no Excel?

São fórmulas que permitem ao usuário obter dados de várias abas de uma única vez, por isso são chamadas de 3D. São fórmulas simples, mas que são muito úteis para a obtenção de dados em diferentes abas de um único arquivo.

Quando utilizar essas funções?

Essas fórmulas serão utilizadas sempre que o usuário precisa obter várias informações de diferentes abas dentro do seu documento. Muitas das vezes são arquivos que pegam informações mensais, e para que o usuário não tenha que ir de aba por aba para obter esses dados que essas fórmulas serão utilizadas.

Excel Impressionador

Tudo que você precisa saber, do Básico ao Avançado, pra dominar a ferramenta mais importante do Mercado de Trabalho. Aprenda as principais ferramentas, funções e recursos do Excel para deixar qualquer planilha mais chamativa e eficiente.

Começar agora

Como utilizar as Fórmulas 3D no Excel?

Nesta aula vamos mostrar 3 formas de utilizar as fórmulas 3D no Excel para obter dados de várias abas diferentes. Na planilha que vamos utilizar temos 6 abas de meses, que possuem os faturamentos das suas respectivas semanas.

Modelo de faturamento mensal

E temos também a aba Resumo que é exatamente onde vamos utilizar as fórmulas 3D para obter a soma dos faturamentos de todos os meses.

Tabela de resumo dos faturamentos mensais

O primeiro método é o mais comum, que é simplesmente colocando a fórmula SOMA e indo em cada uma das abas e selecionando o dado desejado.

Método 1 pegando os dados individualmente pelas abas

Neste caso a soma ficará correta, no entanto é um processo trabalhoso, pois temos que acessar aba por aba para selecionar cada um dos dados. Aqui temos somente 6 abas, mas caso tenha 20 ou mais abas o processo já se torna inviável.

Feito isso podemos simplesmente arrastar a fórmula para as outras células que o Excel irá reconhecer que tem que pegar as células corretas de cada uma das abas. Como já fizemos a fórmula uma vez, para expandir fica mais fácil.

Resultado do primeiro método para todas as semanas

Para o método 2 vamos novamente começar com a fórmula SOMA e vamos selecionar a célula B2 da aba Mês 1, no entanto não vamos ficar trocando de aba até chegar na aba Mês 6. Neste caso, após selecionar a célula B2 da primeira aba, vamos segurar a tecla SHIFT e selecionar a aba Mês 6.

Método 2 utilizando o intervalo entre a primeira aba e a última

Como queremos somente a célula B2 de cada uma das abas, ao fazer essa seleção com SHIFT o Excel entende que vamos pegar da aba Mês 1 até a aba Mês 6 e selecionar a célula B2 de cada uma delas.

É possível observar que o Excel escreve a fórmula automaticamente para ir da primeira aba até a última e selecionar a célula B2 de cada aba para que possamos somar utilizando a fórmula SOMA.

Feito isso basta pressionar Enter e em seguida arrastar a fórmula para as outras células.

Resultado do segundo método para todas as semanas

Desta forma podemos verificar que os resultados dos dois métodos são iguais, portanto, temos o mesmo resultado com dois métodos diferentes. O interessante desse método é que a seleção é muito parecida quando fazemos a seleção de um intervalo, pois esse intervalo é separado por dois pontos, neste caso acontece o mesmo, só que temos abas ao invés de células.

Para o próximo método vamos criar uma aba e colocar ela entre os meses 3 e 4.

Criação de uma nova aba entre as já existentes

Isso é para mostrar que quando colocarmos um valor dentro da célula B2 dessa nova planilha o Excel vai considerar esse valor na soma que fizemos anteriormente.

Inserindo o valor 10.000 na célula B2 da nova aba

Ao inserir esse valor dentro da célula B2 podemos observar que a soma da aba Resumo vai ser modificada automaticamente.

Modificação do resultado do segundo método ao adicionar a nova aba com o valor 10.000

Isso acontece porque essa nova aba está dentro do intervalo que selecionamos, ou seja, tudo que estiver entre a aba Mês 1 e Mês 6 será considerado na soma, mas acontece que nem sempre queremos somar todas as abas.

Vamos querer somar somente abas específicas, como é o caso do método 1. É possível observar que o seu valor não foi modificado, pois escolhemos manualmente as 6 abas que queríamos somar os valores.

Para o método 3 nós vamos utilizar uma fórmula que evita esse tipo de acontecimento, que é de somar dados indesejados. Neste caso vamos começar com a fórmula normal, mas somando apenas a célula B2 da primeira aba. Em seguida vamos substituir o número 1 e o espaço por um asterisco (*).

Método 3 para obter a soma somente das abas com o nome “Mês”

A fórmula dessa maneira vai pegar tudo que começa com Mês e possui qualquer outra informação após esse texto, essa é a função do asterisco, ou seja, se tivermos uma aba que tenha o nome Mês seguido de qualquer outra informação, seja espaço, número ou texto o Excel vai entender que vamos querer utilizar essa aba para a soma.

Caso o usuário queira todas as abas que possuem o texto Mês escrito seja no início, meio ou fim, basta colocar o * antes da letra M também, desta forma tudo que possui esse texto será considerado para a soma.

Resultado da fórmula do terceiro método

Ao pressionar Enter o Excel irá fazer a soma normalmente. Veja que é possível observar a fórmula que foi atribuída após a busca pelas abas que possuem o nome Mês. O Excel faz uma seleção somente dessas abas, neste caso vai do mês 1 até o mês 3 e depois do mês 4 até o mês 6.

Isso acontece porque colocamos a nova aba criada entre os meses 3 e 4, por esse motivo o Excel pula essa aba e não leva em consideração para efetuar a soma das abas. Depois basta arrastar a fórmula para as outras células que teremos os resultados desejados.

Resultado final de todos os métodos – Fórmulas 3D no Excel

Nesta aula foi possível aprender 3 maneiras de utilizar fórmulas para obter dados de diferentes abas. Essas formas vão retornar o mesmo resultado, portanto o usuário poderá escolher qual delas deseja utilizar e qual será melhor para sua aplicação, lembrando o que cada uma delas faz.

Você sabe o que é o Nível Impressionador do nosso Curso de Excel? É um nível que está acima do avançado! Quer saber mais? Só clicar no link!

Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?

Perguntas frequentes