Renat não é mais a primeira vez a favor do GuestRiter em Lifehak. Anteriormente, publicamos um ótimo material dele sobre como fazer um plano de treino: e recursos on-line, além de criar um plano de treinamento.

Este artigo contém técnicas descomplicadas que permitem. Especialmente eles serão úteis para aqueles que estão envolvidos no relatório da administração, prepara uma variedade de relatórios analíticos com base no descarregamento de 1C e em outros relatórios, formulários apresentações e diagramas para o manual. Eu não finjo a absoluta novidade - em uma ou outra, essas técnicas provavelmente discutidas nos fóruns ou.

Alternativas simples para HDP e GRP, se os valores desejados não estiverem na primeira coluna da tabela: Visualizar, index + placa de pesquisa

Resultado:

Nesse caso, além da função, o caractere (char) (para exibir cotações) usa a função se (se), o que permite alterar o texto, dependendo se a dinâmica positiva de vendas é observada e a função de texto (texto ), que permite exibir um número em qualquer formato. Sua sintaxe é descrita abaixo:

Texto ( valor; formato)

O formato é indicado nas citações da mesma forma que se você inseriu um formato personalizado na janela "Formato de célula".

Você pode automatizar textos mais complexos. Na minha prática, havia um longo, mas comentários de rotina para relatórios de gerenciamento no formato "O indicador caiu / aumentado por XX em relação ao plano principalmente devido ao crescimento / declínio no Fator1 em XX, crescimento / declínio em Fator2 em YY .. . "Com uma mudança de fatores. Se você escrever tais comentários, muitas vezes e o processo de escrevê-los pode ser algoritmizado - custa uma vez para criar uma fórmula ou macro que se livrará de você pelo menos da parte do trabalho.

Como salvar dados em cada célula após a União

Ao combinar células, apenas um valor é salvo. Excel adverte sobre isso ao tentar combinar células:

Assim, se você tivesse uma fórmula que depende de cada célula, ele pare de trabalhar após sua fusão (erro # N / D nas linhas 3-4 do exemplo):

Para combinar as células e, ao mesmo tempo, salve os dados em cada um deles (talvez você tenha uma fórmula, como neste exemplo abstrato; você pode querer mesclar células, mas salvar todos os dados para o futuro ou ocultá-los intencionalmente), Combine quaisquer células na folha, destaque-as e, em seguida, usando o comando "Formato de amostra", transfira a formatação para essas células que você precisa combinar:

Como construir um resumo de várias fontes de dados

Se você precisar criar um consolidado imediatamente de várias fontes de dados, você terá que adicionar à faixa de opções ou no painel de acesso rápido "mestre de tabelas e gráficos consolidados", no qual existe essa opção.

Isso pode ser feito da seguinte forma: "Arquivo" → "Parâmetros" → "Painel de Acesso Rápido" → "Todos os comandos" → "Mestre de tabelas e gráficos de resumo" → "Adicionar":

Depois disso, o ícone apropriado aparece na fita, cuja prensagem é o próprio assistente:

Quando você clica nele, uma caixa de diálogo é exibida:

Nele, você precisa escolher o item "em várias faixas de consolidação" e clicar em "Avançar". No próximo item, você pode selecionar "Criar campo de uma página" ou "criar campos de página". Se você quiser aparecer independentemente com um nome para cada uma das fontes de dados - selecione o segundo item:

Na próxima janela, adicione todas as faixas com base nos quais será consolidado e configurá-lo os nomes:

Depois disso, na última caixa de diálogo, especifique onde o relatório da tabela consolidada será publicado - em uma existente ou uma nova folha:

O relatório da tabela consolidada está pronto. No filtro "Page 1", você pode selecionar apenas uma das fontes de dados, se necessário:

Como calcular o número de bordas do texto A para o texto B ("Tesouro MTS Supermts" - duas entradas da abreviação MTS)

Neste exemplo, na coluna A existem várias cadeias de texto, e nossa tarefa é descobrir quantas vezes em cada um deles é encontrada na célula E1:

Para resolver esse problema, você pode usar a fórmula complexa que consiste nas seguintes funções:

1. Dlstr (Len) - Calcula o comprimento do texto, o único argumento é o texto. Exemplo: Dlstr ("Machine") \u003d 6.
2. Substituto - Substitui um texto específico na linha de texto aos outros. Sintaxe: Substituto ( texto; Star_text; New_text.). Exemplo: Substituto ("Car"; "Auto"; "") \u003d "Mobile".
3. Registrado (superior) - substitui todos os caracteres na linha ao capital. O único argumento é o texto. Exemplo: proponir ("máquina") \u003d "máquina". Esse recurso será necessário para fazer a pesquisa sem se registrar. Afinal, registrado ("máquina") \u003d registrado ("máquina")

Para encontrar a entrada de uma cadeia de texto definitiva para outra, você precisa remover toda a sua entrada no original e comparar o comprimento da linha resultante com a fonte:

DLSTR ("Tarifa Super MTS") - Dlstr ("Super Tariff) \u003d 6

E, em seguida, dividir essa diferença para o comprimento da linha que estávamos procurando:

6 / dlstr ("MTS") \u003d 2

Era duas vezes a linha "MTS" está incluída na inicial.

Resta registrar este algoritmo na linguagem das fórmulas (denotamos pelo texto que estamos procurando por inscrições, e o "ISKIM" é o número de entradas dos quais nos interessa):

\u003d (Dlstr ( texto) -Dlster (substituição (registrada (registrada texto); Registrado ( estúpido); ""))) / Dlstr ( estúpido)

Em nosso exemplo, a fórmula é assim:

\u003d (DLSTR (A2) -DLster (substituição (registrado (A2); registrado ($ E $ 1); "" ")) / dlstr ($ E $ 1)

Tabelas de resumo no Excel - uma ferramenta poderosa para criar relatórios. É especialmente útil nos casos em que o usuário funciona bem com as fórmulas e é difícil para ele criar uma análise de dados independentemente. Neste artigo, vamos ver como criar mesas semelhantes e quais existem oportunidades no editor do Excel. Não há arquivos de download para isso. O treinamento está disponível online.

Primeiro de tudo, você precisa criar alguma tabela. É desejável que havia várias colunas. Nesse caso, algumas informações devem ser repetidas, já que somente neste caso, será possível fazer qualquer análise das informações inseridas.

Por exemplo, considere as mesmas despesas financeiras em diferentes meses.

Criando tabelas resumidas

Para construir uma tabela semelhante, você deve desenhar as seguintes ações.

1. Para começar, deve ser totalmente alocado.

1. Em seguida, vá para a guia "Inserir". Clique no ícone "Table". No menu que aparece, selecione "Tabela Resumo".

1. Como resultado, uma janela aparecerá na qual você precisa especificar vários parâmetros básicos para criar uma tabela consolidada. Primeiro de tudo, você deve selecionar a área de dados com base nos quais a análise será realizada. Se você já identificou a tabela, o link para ele será definido automaticamente. Caso contrário, precisará ser alocado.

1. Então você será solicitado a indicar onde a construção acontecerá. É melhor escolher o item "na folha existente", porque será inconveniente para analisar as informações quando tudo estiver espalhado em várias folhas. Então você deve especificar o intervalo. Para fazer isso, clique no ícone perto do campo de entrada.

1. Imediatamente depois disso, o Assistente de Criação chegará a um pequeno tamanho. Além disso, a aparência do cursor mudará. Você precisará fazer o botão esquerdo do mouse em qualquer lugar conveniente para você.

1. Como resultado, a referência à célula especificada será automaticamente substituída. Então você precisa clicar no ícone no lado direito da janela para restaurá-lo para o tamanho da fonte.

1. Para completar as configurações necessárias para clicar no botão "OK".

1. Como resultado, você verá um modelo em branco para trabalhar com tabelas consolidadas.

1. Nesta fase, você deve especificar qual campo será:
   1. coluna;
   2. fila;
   3. valor de análise.

Você pode escolher qualquer coisa. Tudo depende de quais informações você deseja obter.

1. Para adicionar qualquer campo, você precisa fazer o clique do mouse esquerdo e, sem liberar os dedos, arrastar para a área desejada. Nesse caso, o cursor muda sua aparência.

1. Você pode liberar o dedo somente quando o círculo cruzado desaparecer. Da mesma forma, você precisa arrastar todos os campos que estão em sua tabela.

1. Para ver o resultado do conjunto, você pode fechar o painel lateral das configurações. Para fazer isso, basta clicar na cruz.

1. Como resultado, você verá o seguinte. Com esta ferramenta, você pode reduzir a quantidade de despesas em cada mês para cada posição. Além disso, a informação está disponível no resultado geral.

1. Se você não gostou da mesa, você pode tentar construí-lo um pouco diferente. Para fazer isso, você precisa mudar os campos nas áreas de construção.

1. Feche o assistente para construir novamente.

1. Desta vez, vemos que a tabela consolidada se tornou muito mais, porque agora não são os meses das colunas, mas as categorias de despesas.

Se você não pode construir uma mesa, você sempre pode contar com a ajuda do editor. No Excel, existe a possibilidade de criar esses objetos no modo automático.

Para fazer isso, você precisa fazer o seguinte, mas primeiro selecione todas as informações inteiras.

1. Clique na guia Inserir. Em seguida, clique no ícone "Table". No menu que aparece, selecione o segundo item.

1. Imediatamente depois disso, uma janela aparecerá em que haverá exemplos diferentes para a construção. Essas opções são oferecidas com base em várias colunas. O número de modelos depende diretamente de sua quantidade.

1. Quando você passa para cada item estará disponível a visualização do resultado. Então o trabalho é muito mais conveniente.

1. Você pode escolher o que mais gosta.

1. Para inserir a opção selecionada, clique no botão "OK".

1. Como resultado, você receberá o seguinte resultado.

Por favor, note: A tabela foi criada em uma nova folha. Isso ocorrerá toda vez usando o designer.

Depois de adicionar (não importa como) uma tabela de resumo, você verá a nova guia "Análise" na barra de ferramentas. Ele contém um grande número de diferentes ferramentas e funções.

Considere cada um deles com mais detalhes.

Ao clicar no botão marcado na captura de tela, você pode fazer o seguinte:

• mudar o nome;

• chame a janela Configurações.

Na janela Opções, você verá muitas coisas interessantes.

Campo ativo

Com esta ferramenta, você pode fazer o seguinte:

1. Primeiro você precisa destacar algumas células. Em seguida, clique no botão "Campo Active". No menu que aparece, clique no campo "Parâmetros de campo".

1. Imediatamente depois disso, você verá a seguinte janela. Aqui você pode especificar o tipo de operação a ser usado para obter informações no campo selecionado.

1. Além disso, você pode configurar um formato numérico. Para fazer isso, clique no botão apropriado.

1. Como resultado, a janela "Formato de célula" aparecerá.

Aqui você pode especificar, em que tipo de forma você precisa produzir o resultado da análise de informações.

Graças a esta ferramenta, você pode configurar o agrupamento por valores dedicados.

Inserir fatia

O Microsoft Excel Editor permite criar tabelas de resumo interativo. Ao mesmo tempo, nada é difícil de fazer.

1. Realce alguma coluna. Em seguida, clique no botão "Inserir Sleep".
2. Na janela que aparece, como exemplo, selecione um dos campos propostos (no futuro, você pode alocá-los em uma quantidade ilimitada). Depois que algo é selecionado, o botão "OK" é imediatamente ativado. Clique nisso.

1. Como resultado, uma pequena janela aparecerá, que pode ser movida em qualquer lugar. Ele oferecerá todos os possíveis valores únicos que estão neste campo. Graças a esta ferramenta, você pode retirar a quantidade apenas para determinados meses (neste caso). As informações padrão são exibidas para todos os tempos.

1. Você pode clicar em qualquer um dos itens. Imediatamente depois disso, o valor alterará todos os valores no campo.

1. Então acaba por escolher qualquer lapso de tempo.

1. A qualquer momento, tudo pode ser devolvido à aparência original. Para fazer isso, clique no ícone no canto superior direito desta janela.

Nesse caso, fomos capazes de classificar o relatório em meses, já que tínhamos um campo correspondente. Mas para trabalhar com datas, há uma ferramenta mais poderosa.

Se você clicar no botão apropriado na barra de ferramentas, provavelmente verá esse erro. O fato é que não há células em nossa tabela que haverá o formato de dados "Data" explicitamente.

Como exemplo, crie uma pequena mesa com datas diferentes.

Então você precisará construir uma tabela consolidada.

Vá para a guia "Inserir". Clique no ícone "Table". No submenu que aparece, escolha a opção que você precisa.

1. Então seremos solicitados a escolher o intervalo de valores.

1. Para fazer isso, é suficiente destacar toda a mesa.

1. Imediatamente depois disso, o endereço será automaticamente substituído. Tudo é muito simples aqui, já que é projetado para manequins. Para completar a construção, clique no botão "OK".

1. O Excel Editor nos oferecerá apenas uma opção, já que a tabela é muito simples (por exemplo, não é mais necessária).

1. Tente novamente clicar no ícone "Inserir Timeline" (ele está localizado na guia "Análise").

1. Desta vez não haverá erros. Você será oferecido para escolher um campo de classificação. Assinale e clique no botão "OK".

1. Devido a isso, uma janela aparecerá, na qual você pode escolher a data desejada usando o corredor.

1. Escolhemos outro mês e não há dados, uma vez que todos os custos na tabela são indicados apenas para março.

Se você fez alguma alteração nos dados de origem e, por algum motivo, não é exibido na tabela consolidada, você sempre pode atualizá-la manualmente. Para fazer isso, basta clicar no botão correspondente na barra de ferramentas.

Se você decidir alterar os campos, com base nos quais a construção deve ocorrer, é muito mais fácil fazer isso nas configurações e não excluir a tabela e criá-lo novamente com as novas preferências.

Para fazer isso, clique no ícone da fonte de dados. Em seguida, selecione o item de menu visto.

Como resultado, uma janela aparecerá na qual você pode identificar novamente a quantidade desejada de informações.

Ações

Com esta ferramenta você pode:

• tabela clara;
• distribuir;
• mova isso.

Cálculos

Se os cálculos na tabela não forem suficientes ou eles não atenderem às suas necessidades, você sempre pode fazer suas alterações. Ao clicar no ícone desta ferramenta, você verá as seguintes opções.

Esses incluem:

• campo calculado;

• objeto calculado;

• o procedimento para cálculos (fórmulas adicionadas são exibidos na lista);

• retirar a fórmula (não há informações, uma vez que não há fórmulas adicionadas).

Aqui você pode criar um diagrama de resumo ou alterar o tipo de tabela recomendada.

Usando esta ferramenta, você pode configurar a aparência do espaço de desenvolvimento do editor.

Graças a isso você pode:

• configure a exibição da barra lateral com a lista de campos;

• ativar ou desativar os botões Plus / Mini C;

• configure a exibição de cabeçalhos de campo.

Ao trabalhar com tabelas consolidadas, além da guia "Análise", mais um - "Designer" também aparecerá. Aqui você pode alterar a aparência do seu objeto até ser irreconhecível comparado com a opção padrão.

Você pode configurar:

• subtotais:
  • não mostre;
  • mostre todos os resultados na parte inferior;
  • mostrar todos os resultados no título.

• resultados Gerais:
  • desativar por cordas e colunas;
  • ativar para linhas e colunas;
  • ativar apenas para linhas;
  • ativar apenas para colunas.

• layout de relatório:
  • mostra em forma comprimida;
  • mostra sob a forma da estrutura;
  • mostra em forma tabular;
  • repita todas as assinaturas dos elementos;
  • não repita as assinaturas dos elementos.

• linhas vazias:
  • insira uma string vazia após cada elemento;
  • exclua uma string vazia após cada elemento.

• configurações de estilo de mesa postado (Aqui você pode ativar / desativar cada item):
  • cabeçalhos de string;
  • manchetes de coluna;
  • linhas alternadas;
  • colunas alternadas.

• configure o estilo de elementos de design.

Para ver opções mais diferentes, você precisa clicar no triângulo no canto inferior direito desta ferramenta.

Imediatamente depois disso, uma enorme lista aparecerá. Você pode escolher qualquer coisa. Quando você passa para cada um dos modelos, sua tabela será alterada (isso é feito para visualização). As alterações não terão efeito enquanto você não clicar em nenhuma das opções propostas.

Além disso, se você desejar, você pode criar seu próprio estilo de design.

Também aqui você pode alterar a ordem de exibição de linhas. Às vezes é necessário para a conveniência da análise de despesas. Especialmente se a lista for muito grande, já que a posição desejada é mais fácil de encontrar alfabeticamente do que lançar uma lista várias vezes.

Para fazer isso, faça o seguinte.

1. Clique no triângulo perto do campo desejado.
2. Como resultado, você verá o próximo menu. Aqui você pode escolher a opção de classificação desejada ("de A a Z" ou "de I até A").

Se a opção padrão não for suficiente, você poderá clicar no item "Configurações avançadas de classificação" no mesmo menu.

Como resultado, você verá a seguinte janela. Para configurações mais detalhadas, você precisa clicar no botão "Avançado".

Aqui tudo é configurado no modo automático. Se você remover este carrapato, poderá especificar a chave que precisa.

Tabelas de resumo no Excel 2003

As ações descritas acima são adequadas para editores modernos (2007, 2010, 2013 e 2016). Na versão antiga, tudo parece diferente. Oportunidades, é claro, há muito menos.

Para criar uma tabela consolidada no Excel 2003, você precisa fazer o seguinte.

1. Vá para a seção "Data" e selecione o item apropriado.

1. Como resultado, um mestre aparecerá para criar esses objetos.

1. Depois de clicar no botão "Next", uma janela será aberta na qual você deseja especificar o intervalo de células. Em seguida, pressione o "Next" novamente.

1. Para completar as configurações, clique em "Pronto".

1. Como resultado, você verá o seguinte. Aqui você precisa arrastar os campos para as áreas apropriadas.

1. Por exemplo, esse resultado pode acabar.

Torna-se óbvio que é muito melhor criar esses relatórios em editores modernos.

Conclusão

Este artigo cobriu todos os detalhes de trabalhar com tabelas consolidadas no editor do Excel. Se algo não funcionar, você pode alocar não esses campos ou muito poucos deles - para criar tal objeto, existem várias colunas com valores repetitivos.

Se este tutorial não for suficiente para você, informações adicionais podem ser encontradas na Ajuda on-line do Microsoft.

Instrução de vídeo.

Para aqueles que ainda têm alguma dúvida sem responder, um vídeo com comentários para as instruções descritos acima estão anexados.

Assista ao vídeo para o artigo:

Relatórios típicos que estão incluídos em nossos módulos de Business Intelligence (BI) de soluções prontas Permitir responder a 99% das solicitações de usuários mais populares.

Mas se desejar, analistas ou quaisquer outros usuários podem fazer seus relatórios no Excel em cubos OLAP.

Graças a no coração de cada módulo de negócios - analistas (bi) reside Olap CubeConectando-se a quais os usuários podem criar facilmente qualquer relatório no Excel usando tabelas de resumo.

Também construídos relatórios do Excel sobre os cubos OLAP podem ser salvos e, quando reabrir, eles serão atualizados automaticamente com dados atuais de sistema Corporativo de Analytics de Negócios (BI) .

Exemplos de relatórios construídos em cubos OLAP:

Descrição do relatório:

(Processo de criação de relatórios Veja o vídeo para este artigo)

1. Relatório de Vendas em rublos e unidades:

• período: Para o ano atual
• indicadores: Fato Sales.
• detalhando: Colunas / trimestre, mês
• detalhando: Linhas / canais de vendas, clientes, marcas, produtos

2. Relatório sobre estrutura de vendas em rublos e unidades (filtro):

• período: Para o ano atual
• indicadores: Fato de vendas, vendas de fatos em porcentagem
• detalhando: Colunas / trimestre, mês
• detalhando: Linhas / canais de vendas, marcas

3. Relatório sobre a implementação do plano de vendas em rublos e unidades (filtro):

• período: Para o ano atual
• indicadores: Plano de vendas, vendas de fatos, plano de execução
• detalhando: Colunas / trimestre, mês
• detalhando: Linhas / canais de vendas, clientes, categorias, produtos

4. Relatório sobre ganho de vendas em rublos e unidades (filtro):

• período
• indicadores
• detalhando: Linhas / distritos federais, regiões, categorias, produtos

5. Relatório sobre o aumento no tempo de vendas em rublos e unidades (filtro):

• período: Para o ano atual em comparação com o anterior
• indicadores: Vendas do ano anterior, plano de vendas, fato de vendas, execução do plano, aumento de vendas em termos absolutos e relativos para o ano anterior
• detalhando: Linhas / trimestre, mês, categorias, produtos

6. Relatório de recebíveis:

• período: Na data atual
• indicadores: A quantidade do documento, os recebíveis gerais, recebíveis atuais, recebíveis expirados por períodos - até 15 dias, até 30 dias, até 60 dias, até 90 dias, até 180 dias, até 365 dias ou mais , a estrutura de recebíveis
• detalhando: Strings / canais de vendas, contraparte, documento de pagamento de documentos, Documento de documento, documento

7. Relatório de recebíveis:

• período: Para o ano atual
• indicadores: Recebíveis gerais, recebíveis atuais, recebíveis em atraso, estrutura de gestão da dívida
• detalhando: Linhas / ano, semana, contraparte

8. Relatar pagamentos de chegada por data do documento:

• período: Para o ano atual
• indicadores: O valor do documento, a quantidade de pagamento do documento, desvios para pagamento em termos absolutos e relativos
• detalhando: cordas / ano, mês, contraparte, tipo de documento, data de documento, número do documento

9. Relatório de pagamentos A chegada pela data de pagamento:

• período: Para o ano atual
• indicadores: Valor do pagamento, quantidade de distribuição de pagamento, desvios de distribuição em termos absolutos e relativos
• detalhando: Linhas / ano, mês, contraparte, tipo de pagamento, data de pagamento, sala de pagamento

10. Relatório de reserva de commodities:

• período: Na data atual
• indicadores: O saldo das mercadorias, a data de vencimento residual em porcentagem (por DSO), o saldo de mercadorias em termos de adequação - o prazo de 0%, termo 10-30%, termo 40-60%, termo 70-90% , termo 99%
• detalhando: cordas / categoria, produto, status, armazém, data de documento paroquial

Olá pessoal! O material de hoje para aqueles que continuam a desenvolver trabalho com aplicativos, e não sabe como fazer uma tabela consolidada no Excel.

Tendo criado uma tabela comum em qualquer um dos documentos de texto, é possível realizar sua análise fazendo tabelas consolidadas no Excel.

Criar uma tabela de Excel consolidada requer conformidade com certas condições:

1. Os dados se encaixam na tabela onde há colunas e listas com nomes.
2. Sem formas em branco.
3. Falta de objetos escondidos.

Como fazer uma tabela consolidada no Excel: instruções passo a passo

Para criar uma mesa de articulação que você precisa:

Uma folha vazia foi criada, onde as listas de regiões e campos são visíveis. As manchetes tornaram-se campos em nossa nova mesa. A tabela consolidada será formada arrastando os campos.

Eles serão marcados, e pela conveniência de análise você os mudará em lugares em áreas tabulares.


Eu decidi que a análise dos dados seria feita através do filtro de vendedores para ser visto por quem e quanto cada mês foi vendido e qual produto.

Escolha um vendedor específico. Clique no mouse e transfira o campo "Seller" para o "filtro de relatório". O novo campo é marcado e a visão da tabela muda levemente.


A categoria "bens" nós entregaremos na forma de linhas. Nos "títulos de linha", transferimos o campo que você precisa.


Para exibir a lista suspensa, ele tem um valor em que sequência indicamos o nome. Se inicialmente nas linhas fizer uma escolha a favor das mercadorias, e, em seguida, apontar o preço, então as mercadorias são apenas as listas suspensas e vice-versa.

Coluna "Unidades", estando na tabela principal, exibida o número de mercadorias vendidas por um vendedor específico a um preço específico.


Para exibir vendas, por exemplo, para cada mês, você precisa do campo "Data" para colocar os "nomes das colunas". Selecione o comando "Grind" clicando na data.


Indicar períodos de datas e etapa. Confirme a escolha.

Nós vemos uma tabela.


Vamos transferir o campo "Quantidade" para a área "valor".


Tornou-se visível para a exibição de números, e precisamos de um formato numérico


Para corrigir, selecione as células chamando a janela com o mouse, selecione um "formato numérico".

Formato numérico que escolhemos para a próxima janela e celebramos o "separador de grupos de descargas". Confirme o botão "OK".

Registro de uma mesa dinâmica

Se entregarmos um carrapato que confirmam a alocação de vários objetos de uma só vez, podemos processar os dados de uma só vez para vários vendedores.


O uso do filtro é possível para colunas e linhas. Colocando um carrapato em uma das variedades de mercadorias, você pode descobrir quanto é implementado por um ou mais vendedores.


Configure separadamente os parâmetros de campo. No exemplo, vemos que um certo vendedor de Roma em um mês de concreto vendido camisas para uma quantia específica. Ao pressionar o mouse na linha "a soma sobre o campo ..." Chamamos o menu e selecionamos "Parâmetros dos campos de valores".


Ao lado desses dados no campo, selecione "Quantidade". Confirme a escolha.

Olhe para a mesa. Viu claramente que em um dos meses o vendedor vendeu uma camisa na quantidade de 2 peças.


Agora, mudamos a mesa e fazemos o filtro trabalhado por meses. O campo "Data" é transferido para o "filtro de relatório" e onde os "nomes das colunas" serão "vendedor". A tabela exibe todo o período de venda ou para um mês específico.

A seleção de células na tabela de pivô levará à aparência de tal guia como "trabalho com tabelas consolidadas", e haverá mais dois parâmetros "parâmetros" e "designer".


Você pode realmente contar sobre as configurações das tabelas consolidadas por um longo tempo. Conduza mudanças no seu gosto, conseguindo útil para você. Não tenha medo de pressionar e experimentar. Qualquer ação que você sempre pode alterar pressionando a combinação de teclas Ctrl + Z.

Espero que você tenha aprendido todo o material, e agora você sabe como fazer uma tabela consolidada no Excel.

Para a análise de tabelas grandes e complexas são geralmente usadas. Com a ajuda de fórmulas, você também pode implementar um agrupamento e análise dos dados disponíveis. Crie relatórios descomplicados com a ajuda de fórmulas.

Como um começo, usaremos ( Inserir / Mesa / Tabela) Contendo informações sobre vendas de lotes de produtos. Nas linhas de mesa, os dados sobre o fornecimento de um lote do produto e seu marketing são dados. Tabela semelhante foi usada no artigo .

A tabela tem colunas:

• produtos - o nome do lote de mercadorias, por exemplo, " Laranjas.»;
• Grupo - Grupo de bens, por exemplo, " Laranjas."Digite o grupo" Frutas»;
• data de entrega - Data de entrega das mercadorias pelo fornecedor;
• Reparar região - Região em que foi implementado um lote de mercadorias;
• Vendas - o custo de que foi possível implementar um lote de mercadorias;
• Vendas - o prazo da venda real de mercadorias na região (em dias);
• Lucro - A marca de se um lucro foi recebido de um lote realizado de mercadorias.

Através Dispatcher de nome Corrija as tabelas em " OFICIAL_TABLITSA."(Veja o arquivo de exemplo).

Com a ajuda de fórmulas, crie 5 relatórios simples que serão colocados em folhas separadas.

Relatório №1 Vendas totais de mercadorias

Encontramos a venda total de cada produto.
A tarefa é resolvida simplesmente usando a função do silêncio (), no entanto, a construção do relatório requer certas habilidades para trabalhar com algumas ferramentas do Excel.

Então, prossiga. Primeiro, precisamos formar uma lista de nomes de produtos. Porque Na coluna, o nome da tabela de origem é repetido, então precisamos selecionar apenas valores a partir dele. Isso pode ser feito de várias maneiras: fórmulas (ver artigo), através do menu Dados / dados / excluir duplicados ou com. Se você usar o primeiro caminho, ao adicionar novos produtos à tabela de origem, os novos nomes serão incluídos na lista automaticamente. Mas aqui para a simplicidade usará o segundo caminho. Por esta:

• Vá para a folha com a tabela de origem;
• Ligar ( Dados / Classificação e Filtro / Avançado);
• Preencha os campos conforme mostrado na figura abaixo: Switch definido para a posição Copie o resultado para outro lugar; No campo Faixa de origem, insira $ A $ 4: $ A $ 530; Verifique a bandeira Apenas entradas exclusivas.

• Copie a lista resultante na folha em que o relatório será colocado;
• Classifique a lista de mercadorias ( Dados / classificação e filtro / classificação de A para Z).

Deve ser a seguinte lista.

Na célula. B6. Nós introduzimos a seguinte fórmula, então copiá-la até o final da lista:

Sumume (Source_Tablik [produto]; A6; Source_Tablik [Vendendo])

Agendas (Source_Tablik [produto]; A6)

Relatório No. 2 vendas de mercadorias por região

Encontramos a totalificação total de cada produto nas regiões.
Usamos uma lista de mercadorias criadas para relatório No. 1. Da mesma forma, recebemos uma lista de nomes de regiões (no campo Faixa de origem, digite $ D $ 4: $ D $ 530).
Copie o intervalo vertical resultante em Prancheta E é horizontal. A faixa resultante contendo os nomes das regiões é colocada no cabeçalho do relatório.

Na célula. B. 8 Nós introduzimos a seguinte fórmula:

Smerevymn (Source_Tablik [Vendas];
Source_tablik [produto]; $ a8;
Source_Table [Região de venda]; B $ 7)

A fórmula retornará a venda total de mercadorias cujo nome está localizado na célula A8. Na região da célula ÀS 7 . Preste atenção ao uso (Referências $ A8 e B $ 7), será necessário ao copiar a fórmula para as outras células de mesa vazias.

Copiar a fórmula acima na célula à direita não funcionará (isso foi feito para o relatório No. 1), porque Neste caso na célula C8. A fórmula será assim:

Smerevymn (Source_Tablik [vendas, dias];
Source_tablik [grupo]; $ A8;
Source_Table [vendas]; C $ 7)

Relatório No. 3 Produtos de filtragem para rentabilidade

Vamos voltar para a tabela de origem. Cada lote de mercadorias trouxe lucro ou não trouxe (veja a coluna de lucro na tabela de origem). Calcule as vendas por grupos de mercadorias dependendo de rentabilidade. Para fazer isso, filtrará usando a fórmula de gravação de tabela de origem no campo de lucros.

Frequência (Source_Tablik [vendas, dias]; A7: A12)

Para inserir fórmulas, destaque o intervalo C6: C12. , então B. Fórmulas de linha Digite a fórmula acima e clique em Ctrl.+ Mudança.+ ENTRAR.

O mesmo resultado pode ser obtido usando a função usual DimmProt ():
\u003d Sumpacy ((Source_Tablik [vendas, dias]\u003e A6) *
(Source_Tablik [vendas, dias]<=A7))

Relatório №5 estatísticas de fornecimento de mercadorias

Agora prepare um relatório sobre o fornecimento de mercadorias para o mês.
Primeiro crie uma lista de meses por ano. Na tabela de origem, a data de entrega mais antiga 07/11/2009. Pode ser calculado usando a fórmula:
\u003d Min (Source_Tablik [Data de entrega])

Crie uma lista de datas - a partir da data de entrega mais antiga. Para fazer isso, usamos a fórmula:
\u003d Conmine ($ C $ 5; -1) +1

Como resultado, receberemos uma lista de datas - primeiros dias meses:

Aplicando o formato de célula apropriado, altere a exibição da data:

Fórmula para contar o número de mercadorias fornecidas para o mês:

Summice ((Source_Tablik [Detalhes]\u003e \u003d B9) *
(Source_Tablik [Data de entrega]

Agora adicione linhas para contar o número total de partes para cada ano. Para fazer isso, você alterará a mesa ligeiramente, destacando o ano em uma coluna separada, que foi fornecida usando o ano () função.

Agora, para a saída por ano, criaremos uma estrutura através do item de menu:

• Realce qualquer célula da tabela modificada;
• Ligue para a janela através do item de menu Dados / Estrutura / Resultados Provisórios;
• Preencha os campos conforme mostrado na figura:

Depois de pressionar OK, a tabela será alterada da seguinte forma:

Os resultados intermediários serão criados por ano.
Ao pressionar os botões pequenos no canto superior esquerdo da folha, você pode gerenciar a exibição de dados na tabela.

Resumo :

Relatórios semelhantes aos criados, podem ser feitos naturalmente com ou usar Filtro Para a tabela de origem ou com outras funções BDSMM (), Bark (), o BSC (), etc. A escolha da abordagem depende da situação específica.