Então, já aprendemos o que é referência 3D no Excel. O fato engraçado é que a referência normal do Excel 3D não funciona com funções condicionais, como a função SOMASE. Neste artigo, aprenderemos como fazer a referência 3D funcionar com a função SUMIF.
A fórmula genérica para SUMIF com referência 3D no Excel
Parece complicado, mas não é (tanto).
| = SUMPRODUCT (SUMIF (INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Intervalo_de_critérios"), critérios, INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Sum_range"))) |
"'" name_range_of_sheet_names "'":É um intervalo nomeado que contém os nomes das planilhas. Isto é muito importante.
"intervalo_de_critérios":É a referência de texto de critérios contendo intervalo. (Deve ser o mesmo em todas as folhas de trabalho de referência 3D.)
critério:É simplesmente a condição que você deseja definir para a soma. Pode ser uma referência de texto ou célula.
"intervalo_soma":É a referência de texto do intervalo de soma. (Deve ser o mesmo em todas as folhas de trabalho de referência 3D.)
Chega de teoria, vamos fazer referência 3D com a função SUMIF funcionando.
Exemplo: Soma por região de várias planilhas usando referência 3D do Excel:
Estamos pegando os mesmos dados que pegamos no exemplo de referência 3D simples. Neste exemplo, tenho cinco planilhas diferentes que contêm dados semelhantes. Cada folha contém os dados de um mês. Na planilha mestre, desejo a soma das unidades e a coleção por região de todas as planilhas. Vamos fazer isso pelas unidades primeiro. As unidades estão no intervalo D2: D14 em todas as folhas.
Agora, se você usar a referência 3D normal com a função SUMIF,
= SUMIF (Jan: Abr! A2: A14, Mestre! B4, Jan: Abr! D2: D14)
Ele retornará #VALUE! erro. Portanto, não podemos usá-lo. Usaremos a fórmula genérica mencionada acima.
Usando a fórmula SUMIF de referência 3D genérica acima do Excel, escreva esta fórmula na célula C3:
| = SUMPRODUTO (SUMIF (INDIRETO ("'" & Meses & "'!" & "A2: A14"), Mestre! B3, INDIRETO ("'" & Meses & "'!" & "D2: D14"))) |
Aqui meses é um intervalo nomeado que contém os nomes das planilhas. Isso é crucial.
Quando você pressiona Enter, você obtém sua saída exata.
Como funciona?
O núcleo da fórmula é a função INDIRETA e a intervalo nomeado. Aqui a corda"'"&Meses&"'!" & "A2: A14"se traduz em uma matriz de referências de intervalo de cada folha no intervalo nomeado.
| {"'Jan'! D2: D14"; "'Fev'! D2: D14"; "'Mar'! D2: D14"; "'Abr'! D2: D14"} |
Esta matriz contém o referência de textode intervalos, não os intervalos reais. Agora, como é uma referência de texto, pode ser usado pela função INDIRETO para convertê-los em intervalos reais. Isso acontece para ambas as funções INDIRETAS. Depois de resolver os textos dentro das funções INDIRETAS (segure firme), a fórmula fica assim:
| = SUMPRODUCT (SUMIF (INDIRETO (({"'Jan'! A2: A14"; "'Fev'! A2: A14"; "'Mar'! A2: A14"; "'Abr'! A2: A14"}) , Mestre! B3, INDIRETO ({"'Jan'! D2: D14"; "'Fev'! D2: D14"; "'Mar'! D2: D14"; "'Abr'! D2: D14"}))) |
Agora, a função SUMIF entra em ação (não a INDIRETA, como você deve ter adivinhado). A condição corresponde ao primeiro intervalo"'Jan'! A2: A14". Aqui, a função INDIRETO funciona dinamicamente e converte este texto no intervalo real (é por isso que se você tentar resolver INDIRETO primeiro usando a tecla F9, você não obterá o resultado) Em seguida, soma os valores correspondentes no intervalo"'Jan'! D2: D14".Isso acontece para cada intervalo da matriz. Finalmente, teremos um array retornado pela função SUMIF.
| = SUMPRODUCT ({97; 82; 63; 73}) |
Agora o SUMPRODUCT faz o que faz de melhor. Ele resume esses valores e colocamos nossa função 3D SUMIF funcionando.
Então sim pessoal, é assim que você pode conseguir uma função 3D SUMIF. Isso é um pouco complexo, concordo com isso. Há muito espaço para erros nesta fórmula 3D. Eu sugiro que você use a função SUMIF em cada folha em uma célula definida e, em seguida, use a referência 3D normal para somar esses valores.
Espero ter sido suficientemente explicativo. Se você tiver alguma dúvida em relação ao excel referenciando qualquer outra consulta relacionada ao Excel / VBA, pergunte na seção de comentários abaixo.
Referência relativa e absoluta no Excel | Referenciar no Excel é um tópico importante para todo iniciante. Até mesmo usuários experientes do Excel cometem erros ao fazer referência.
Referência de planilha dinâmica | Forneça planilhas de referência dinamicamente usando a função INDIRETA do Excel. Isso é simples …
Expandindo referências no Excel | A referência de expansão se expande quando copiada para baixo ou para a direita. Usamos o sinal $ antes do número da coluna e da linha para fazer isso. Aqui está um exemplo …
Tudo sobre referência absoluta | O tipo de referência padrão no Excel é relativo, mas se você quiser que a referência de células e intervalos seja absoluta, use o sinal $. Aqui estão todos os aspectos da referência absoluta no Excel.
Artigos populares:
50 atalhos do Excel para aumentar sua produtividade | Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.
A função VLOOKUP no Excel | Esta é uma das funções mais usadas e populares do Excel, que é usada para pesquisar valores em diferentes intervalos e planilhas.
CONT.SE no Excel 2016 | Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar um valor específico. A função Countif é essencial para preparar seu painel.
Como usar a função SUMIF no Excel | Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.