Neste artigo você verá como usar as funções de Cubo Excel MembroCubo, ValorCubo, ConjuntoCubo.
As funções de cubo do Excel tem por finalidade retornar dados de conexões do Modelo de dados do Excel, como veremos a seguir.
As conexões do modelo de dados podem vir da própria planilha ou ainda de conexões externas.
E as funções de cubo do Excel permitem consultar partes destes dados de forma agrupada, como em uma tabela dinâmica.
Resumindo o seu funcionamento, ela permite consultar dados agrupados, como uma tabela dinâmica, mas sem a necessidade de cria-la.
Veremos a seguir exemplos claros e práticos de como usar as funções MembroCubo, ValorCubo e ConjuntoCubo.
Como usar as funções MembroCubo, ValorCubo e ConjuntoCubo?
As três funções mais utilizadas de cubo são MembroCubo, ValorCubo e ConjuntoCubo, então vamos focar nelas.
Mas as outras funções de cubo como a MEMBROKPICUBO e a CONTAGEMCONJUNTOCUBO seguem o mesmo padrão no Excel, então se precisar basta seguir este processo.
No nosso exemplo temos uma tabela Fato Faturamento e tabelas de dimensões Cliente, Vendedor e Cidade.
O objetivo é interligar estas tabelas e montar um relatório resumido com a soma do valor de vendas por cidade e por mês.
Abaixo temos a tabela de Faturamento.
As tabelas dimensões são estas:
Como Relacionar Tabelas no Excel
O próximo passo é relacionarmos as tabelas de Fato e Dimensões para que possamos realizar agrupamentos conforme solicitado pelo nosso exemplo.
Então na guia Dados e no grupo Ferramentas de dados clique no botão Relações.
Clique em Novo e adicione as relações ligando a chave de cada coluna.
Lembre que os dados na tabela dimensão deve ser única. Como no exemplo abaixo, tClientes tem que ter uma coluna chave única.
Relacione selecionando então a tabela principal Faturamento com a tabela de Clientes pelo campo de chave do cliente na tabela faturamento com a coluna de ID da tabela Cliente.
Faça o mesmo procedimento para as demais tabelas de Dimensões relacionarem com a tabela de Fato.
Como Criar a Tabela Dinâmica no Excel para as Funções de Cubo
Após as conexões realizadas nós já podemos então criar a tabela dinâmica que será utilizada para criarmos as funções de Cubo do Excel.
Clique então na guia Dados->Tabela Dinâmica->Da Tabela/Intervalo e clique a Tabela Faturamento.
Marque a opção Adicinoar estes dados ao Modelo de Dados.
E clique em OK para que seja criada a Tabela Dinâmica.
Selecione se deseja colocar em Nova Planilha ou Planilha Existente.
Clique em OK, veja que é criada a estrutura da tabela dinâmica e adicione os campos que desejar.
Agora apague esta tabela dinâmica.
Faça novamente, mas clique no botão Inserir->Tabela dinâmica->Do Modelo de Dados.
Clique no botão Inserir->Tabela Dinâmica e perceba que você tem então as tabelas.
Adicione os campos na tabela dinâmica arrastando os campos conforme abaixo.
Veja que temos então uma tabela dinâmica montada conforme abaixo.
Temos então um resumo das vendas por Cidade e por Mês.
No processo seguinte iremos então converter esta tabela dinâmica em fórmulas de Cubo.
Convertendo Tabela Dinâmica em Fórmulas de Cubo do Excel
Agora iremos converter a tabela dinâmica em fórmulas de cubo do Excel automaticamente.
Para que funcione a tabela dinâmica tem que ter sido criada pelo Modelo de Dados.
Clique na tabela dinâmica e clique na guia Tabela Dinâmica Analyze e clique em Ferramentas OLAP->Converter em Fórmulas.
Veja então que as fórmulas foram convertidas conforme temos na planilha abaixo.
Veja que agora não temos mais uma tabela dinâmica, mas fórmulas ligadas ao modelo de dados.
Temos duas fórmulas MEMBROCUBO e VALORCUBO.
Função MembroCubo Excel
A função MembroCubo retorna dados, tuplas de dados, dimensões.
Por padrão ela será criada assim: =MEMBROCUBO(“ThisWorkbookDataModel”;”[tCidades].[Cidade].&[Amparo]”)
Como vemos abaixo, aonde a função MembroCubo está retornando o nome da Cidade.
Um exemplo da função MEMBROCUBO:
=MEMBROCUBO(conexão;expressão_membro)
Conexão: Este parâmetro tem as informações do Modelo de Dados, basta digitar ” e veja que já é retornado o modelo de dados.
Expressão_membro: Este é o nome do membro que será retornado. Preencha então ” e veja que já são exibidas as tabelas e também a Measures, medidas. E basta clicar em .(ponto) após a tabela e ir selecionando o campo e o dado que quer retornar.
Exemplo: =MEMBROCUBO(“ThisWorkbookDataModel”;“[tCidades].[Cidade].&[Americana]”)
Função ValorCubo Excel
A função ValorCubo tem finalidade de retornar o total dos valores que temos na tabela dinâmica.
O valor é retornado conforme abaixo pela função ValorCubo.
Perceba que os dados do ValorCubo também seguem o mesmo padrão do MembroCubo.
Nele são passados os parâmetros de qual o campo que será somado, qual a tupla de cidade e a tupla de mês.
Por padrão ela será criada assim: =VALORCUBO(“ThisWorkbookDataModel”;$B$7;$B12;C$8;SegmentaçãodeDados_Vendedor).
Veja que temos ali passagem das células, porém, não são textos, elas são entendidas como funções de MembroCubo, não funciona se passar somente o Texto.
Se quiser passar somente o Texto, veja abaixo como fazer.
Como vemos abaixo, aonde a função ValorCubo está retornando o nome da Cidade.
Um exemplo da função VALORCUBO:
=VALORCUBO(conexão;[expressão_membro1]; [expressão_membro2]; [expressão_membro3]; [expressão_membro4];…)
Conexão: Este parâmetro tem as informações do Modelo de Dados, basta digitar ” e veja que já é retornado o modelo de dados.
Expressão_membro: Este é o membro que será retornado, o primeiro digamos que seja o campo de retorno que teremos [Soma de valor] por exemplo, e os demais as tuplas dos dados que queremos retornar, veja no exemplo.
Exemplo:
=VALORCUBO(“ThisWorkbookDataModel”;“[Measures].[Soma de Valor]”;“[tCidades].[Cidade].[All]”;“[tFaturamento].Vencimento (Mês)].[All].[jan]”;SegmentaçãodeDados_Vendedor)
Por fim temos inclusive a segmentação de dados inclusive, que podemos adicionar na função de ValorCubo, para usar a segmentação de dados coloque ela ao criar a tabela dinâmica, antes de converter em fórmulas a tabela dinâmica.
Função ConjuntoCubo Excel
A função ConjuntoCubo permite agrupar vários campos para realizar uma consulta por exemplo de ValorCubo.
O conjunto Cubo permite então agrupar conforme abaixo.
Um exemplo da função CONJUNTOCUBO:
=CONJUNTOCUBO(conexão;expressão_conjunto; [legenda])
conexão: Este parâmetro tem as informações do Modelo de Dados, basta digitar ” e veja que já é retornado o modelo de dados.
expressão_conjunto: Este parâmetro tem a finalidade de agrupar MembroCubo de várias células de uma vez para a função ValorCubo.
[legenda]: Texto que será usado nome do ConjuntoCubo.
Exemplo: =CONJUNTOCUBO(“ThisWorkbookDataModel”;B11:B21;“Região de Americana”)
O intervalo de B10 à B18 são os campos que estamos agrupando.
Por fim, pode usar a função VALORCUBO para usar como parâmetro de Tupla esta soma.
Por exemplo: =VALORCUBO(“ThisWorkbookDataModel”;“[Measures].[Soma de valor]”;E3)
São colocadas então as dimensões da soma de valor e as cidades.
Vantagens das Funções de Cubo sobre as de InfodadosTabelaDinâmica
As funções de cubo tem vantagens sobre as funções InfodadosTabelaDinâmica.
A principal delas é permitir consultar dados resumidos sem a necessidade de ter a tabela dinâmica.
E isto é muito interessante pois permite que você possa criar gráficos sem a necessidade de usar o gráfico de tabela dinâmica ou ainda usar funções auxiliares.
Além disso temos também a situação de que as funções de Cubo não redimensionam como uma tabela dinâmica.
Desta forma os campos ficam sempre estanques, não alteram ou redimensionam, o que facilita por exemplo o uso em dashboards.
Além disso é possível montar fórmulas usando funções de texto concatenadas.
=VALORCUBO(“ThisWorkbookDataModel”;”[Measures].[Soma de Valor]”;”[tCidades].[Cidade].[All].[“&P7&”]”;”tFaturamento].[Vencimento (Mês)].[All].[“&P6&”]”).
Veja que no exemplo acima, colocamos o mês e a cidade á partir de células e os dados são consultados dinamicamente, ao alterar o mês e a cidade os dados são alterados automaticamente.
Download da Planilha de Funções de Cubo Excel
Realize o download da planilha com o exemplo deste artigo no botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.
