Como calcular a soma de uma coluna no Excel. Como calcular a soma de uma coluna no Excel Subtotais no Excel
O recurso de subtotal do Excel é uma maneira simples e conveniente de analisar dados em uma coluna ou tabela específica.
Cálculos intermediários reduzem o tempo de cálculo e permitem visualizar rapidamente dados sobre quantidade, quantidade de mercadorias, receita total, etc.
Aliás, para trabalhar com tabelas de forma mais eficiente, você pode se familiarizar com nosso material.
Excel contém uma grande quantidade de informações. Muitas vezes é inconveniente visualizar e pesquisar dados.Para facilitar a vida dos usuários, os desenvolvedores do programa criaram funções para agrupamento de dados.
Com a ajuda deles, você pode pesquisar facilmente os campos e colunas de que necessita, bem como ocultar informações que não são necessárias no momento.
Você pode resumir vários grupos de folhas usando a função "Subtotais".
Vejamos mais de perto o princípio de funcionamento desta função e sua implementação. Excel.
Sintaxe da função
No Excel a opção é exibida comoRESULTADOS.INTERMEDIÁRIOS (Nº; link 1; link 2; link 3;…; link N ) , onde o número é a designação da função, o link é a coluna pela qual o resultado é resumido.
Os campos de link podem incluir no máximo 29 links e espaçamentos usados na execução de subtotais.
O número pode ser qualquer número de 1 a 11. Este número informa ao programa qual deve ser utilizado para calcular os totais da lista selecionada.
Sintaxe da opção de totais indicado na tabela:
Sintaxe | Ação |
---|---|
1-MÉDIA | Determinar a média aritmética de dois ou mais valores. |
2-CONTAGEM | Calcule o número de números apresentados na lista de argumentos. |
3-CONTAGEM | Conte todos os argumentos não vazios. |
4-MÁX. | Mostra o valor máximo de um conjunto definido de números. |
5 MINUTOS | Mostra o valor mínimo de um conjunto definido de números. |
6-PRODUTO | Multiplica os valores especificados e retorna o resultado. |
7-DESVIO STD | Análise do desvio padrão de cada amostra. |
8-DESVIO DE ESTADOS P | Análise de desvio para o conjunto total de dados. |
9-SOMA | Retorna a soma dos números selecionados. |
10-DISP | Análise de variância amostral. |
11-DISPR | Análise de variância comum. |
Para trabalho "Resultados intermediários" Juntamente com as funções já utilizadas, entre parênteses de argumentos basta indicar apenas o número da função, e não a sua designação alfabética.
Exemplo de implementação de subtotais
Executando subtotais inclui as seguintes operações:
- Agrupamento de dados;
- Criação de resultados;
- Criando níveis para grupos.
Agrupando tabelas
Grupo de tabelas- esta é uma lista Excel . A conveniência desta função é que ao mover uma linha da lista de elementos agrupados, todo o grupo será movido para o novo local. Os cálculos são realizados de maneira semelhante usando funções padrão - para todos os grupos juntos.
Siga as instruções para agrupar as linhas. No nosso caso, vinculamos as colunas A, B, C:
1 Usando o mouse, selecione as colunas A, B, C, conforme mostrado na figura abaixo.
2 Agora, mantendo a seleção, abra o campo “Dados” na barra de ferramentas do programa. Em seguida, no lado direito da janela de opções, encontre o ícone "Estrutura" e clique nele;
3 Na lista suspensa clique em "Grupo". Se você cometeu um erro na fase de seleção das colunas necessárias da tabela, clique em "Desagrupar" e repita a operação novamente.
4 Em uma nova janela em selecione “Cordas” e clique em "OK";
5 Agora de novo selecione as colunas A, B, C e conduzir um agrupamento, mas já por colunas:
Como resultado da ação, todas as linhas previamente selecionadas formam um novo grupo de elementos. Um painel de controle de grupo aparecerá próximo às colunas e linhas. Com sua ajuda, você pode ocultar detalhes ou visualizar elementos ocultos. Para fazer isso, clique nos sinais “-” e “+” à esquerda das linhas ou acima das colunas.
Criando subtotais
A opção de subtotal, graças ao uso de grupos e onze funções padrão (Tabela 1), permite resumir rapidamente os dados da tabela preenchida.
Vejamos o uso prático desta função.
Por exemplo, precisamos descobrir quantos itens de cada tamanho foram comprados pelos clientes no site de uma loja online. Nossa planilha em Excel é um banco de dados nocional que reflete todos os clientes, tipo de pagamento e itens solicitados.
O resultado da ação será o aparecimento de grupos que são ordenados pelo tamanho de cada item. Siga as instruções:
SOBRE determine para quais dados você precisa realizar cálculos finais. No nosso caso esta é a coluna "Tamanho". Seu conteúdo precisa ser classificado. A classificação é realizada do maior para o menor elemento. Selecione a coluna “Tamanho”;
Encontre o campo "Ordenação" e clique nele;
Na janela que aparece, selecione “Classificar por intervalo de valores especificados” e pressione a tecla "OK".
- Como resultado, todos os tamanhos serão exibidos em ordem decrescente. Só depois disso poderemos realizar resultados provisórios que afetam os dados da tabela de tamanhos dos itens encomendados.
Agora vamos seguir em frente para implementar a função “Subtotal”:
- Na barra de ferramentas do programa, abra o campo “Dados”;
- Selecione o bloco "Estrutura";
- Imprensa "Subtotais";
A seguir, será aberta uma janela para configuração e exibição de subtotais. No primeiro campo, selecione o nome da coluna que você fará o resumo. No nosso caso é "Tamanho". A cada alteração neste campo, os dados serão alterados nos totais;
No campo "Operação" selecione o tipo de função, que se aplica a todos os elementos da coluna selecionada no primeiro campo. Para calcular as dimensões, você deve usar a contagem de quantidades;
No guincho "Adicionar totais por" você seleciona a coluna na qual os subtotais serão exibidos. Selecione o item “Tamanho”;
Depois de definir todos os parâmetros novamente verifique novamente os valores inseridos e clique em OK.
Como resultado, a tabela será exibida na planilha, agrupada por tamanho. Agora ficou muito fácil ver todos os itens de tamanho encomendados Pequeno, Extra Grande e assim por diante.
Neste exemplo, estamos olhando para uma tabela pequena, mas na prática ela pode ter vários milhares de linhas, portanto, usando subtotais torna-se uma maneira muito conveniente de selecionar parâmetros rapidamente:
Como você pode ver na figura acima, todos os totais são exibidos entre os grupos mostrados em uma nova linha.
Resultado final para roupas com tamanho Pequeno – 5 peças, para roupas com tamanho Extra grande - 2 pedaços. O número total de elementos também é mostrado abaixo da tabela.
Níveis de grupo
Você também pode visualizar grupos por nível.
Este método de exibição permite reduzir a quantidade de informações visuais na tela - você verá apenas os dados totais necessários - a quantidade de roupas de cada tamanho e a quantidade total.
Clique no primeiro segundo ou terceiro nível no painel de controle do grupo. Selecione a opção de apresentação de informações mais adequada:
Primeiro nível exibe apenas o número total de itens,segundo – quantidade de cada tamanho e número total,terceiro – gire todos os dados da planilha.
Excluindo totais
Após algum tempo, outros dados serão inseridos na tabela, portanto será necessário reagrupar as informações.
Os antigos subtotais e seu tipo de cálculo não serão mais relevantes, portanto precisam ser excluídos.
Para excluir um total, selecione a aba “Dados” - “Estrutura” - "Subtotais".
Na janela que se abre, clique no botão “Remover tudo” e confirme a ação com o botão “OK”.
Fig. 13 - exclusão de dados
Vídeos temáticos:
Se você tiver uma tabela de dados, poderá calcular a soma por coluna usando a função usual =SUM(). Você provavelmente já percebeu que esta função não funciona corretamente ao selecionar determinados valores. Como calcular a soma apenas dos valores selecionados? Para isso, é possível criar subtotais no Excel.
1. Número_função. Esta é a função que você precisa usar para calcular os totais.
Exemplo. Calcule o total na coluna Valor para todos os meses em que foram vendidas mais de 20 unidades.
Como você pode ver na primeira imagem, filtramos todos os valores da coluna “Unidades Vendidas” maiores que 20. Na célula C15 escrevemos uma fórmula como
SUBTOTAL(9;C2:C13)
O valor da função mudará se você alterar as condições do filtro.
Por favor, não esqueça que esta mesma ação pode ser transformada em uma função
Subtotais no Excel, soma apenas das células visíveis
É interessante que na lista de números de funções para a fórmula do subtotal, como na imagem acima, não haja nenhuma função interessante. Para calcular a soma de todas as células visíveis você precisa usar o código 109 .
Subtotais no Excel. Crie uma tabela em uma folha separada
aqueles. com subtotais para cada grupo de linhas, por exemplo, por meio ano em nosso exemplo.
O que deveria ser feito:
1. Certifique-se de que seus dados estejam classificados por pelo menos uma coluna
2. Selecione um intervalo
3. Vá para a barra de ferramentas no menu Dados, seção Estrutura e selecione Subtotais
No menu que se abre, fazemos as configurações, selecionamos por qual coluna ordenar (Semestral) e o que fazer (Operação), bem como em qual coluna realizar o cálculo:
Deve ser semelhante à imagem acima.
É muito conveniente quando a tabela é grande e você precisa classificá-la por item, trimestre, conta, etc.
É importante observar:
— se utilizar a opção Tabela (Inserir -Tabela), ao calcular o valor, uma “Linha Total” é automaticamente adicionada a esta tabela interativa, que substitui em grande parte os subtotais. Na guia Trabalhando com Tabelas - Designer que é aberta, você pode personalizar a tabela interativa para se adequar a quase todas as condições. Quase não aproveito esta oportunidade ainda, porque... tal tabela, mesmo com 10 mil linhas, pode carregar significativamente o sistema. Embora no Excel eu trabalhe até 500 mil linhas sem problemas, mesmo sem PowerPivot.
Compartilhe nosso artigo em suas redes sociais: população geral, onde o campo externo da linha Estrutura em DICA: Calcule os subtotais da seguinte forma: “SUBTOTAL(número_função, endereço_matriz_de_células).o valor máximo no usuário selecionado determina, O valor da data está localizado quando exceto para exibição geral de subtotais.
Para que os dados intermediários sejam exibidos Portanto, utilizaremos outra aplicação Excel, nãoExibindo o filtro de totais finais para resumir a amostra é um subconjunto e campos de coluna.selecionar o parâmetroD5também pode ser com Em nosso array de dados específico; O que for mais conveniente para ele.
na coluna de mesmo nome. os totais precisam ser somados Por exemplo, “a soma dos selecionados ACIMA do grupo, remova a ferramenta Microsoft Excel são os únicos usados para incluir ou
- por campos numéricos.
- da população em geral.
- Resultados intermediários
coloque o cabeçalho da coluna, usando Tabelas Dinâmicas caso a fórmula será o valor mínimo; Para a maioria das pessoas Portanto, nos campos e intermediários. Por exemplo, significados."
condição “Totais abaixo – comando “Intermediário nos valores de cálculo. Defina a caixa de seleção para excluir novos elementosNúmeroVariância deslocadaPara calcular subtotais. que contém e fórmulas. fica assim: “INTERMEDIÁRIO .TOTALS (9;C2:C6)".produto de dados em células; mais conveniente do que colocar totais
“Para cada mudança na tabela de implementação Ative o filtro. Vamos deixar nos dados." totais."Nota:Mostra os totais para ao aplicar um filtro,
Número de valores de dados. Resumindo a estimativa tendenciosa da variância geral usando o padrão Uma caixa de diálogo para a palavra Total aparece, ou seja, palavra Como pode ser visto na imagem Esta função, usando o desvio padrão para a amostra; abaixo das linhas. em" selecione a coluna de mercadorias do mês, a tabela apenas os dados O comando subtotais permite a função para produzir o correto Este parâmetro só está disponível colunas
em que os totais são selecionados, funciona como
Produto Subtotal; acima, após aplicar esta sintaxe, você pode desvio padrão de acordo com o geral Após a “Data” ser concluída. em que cada valor “Jantar usar vários ao mesmo tempo
resultado, verifique o intervalo no caso,, marque certos elementos, no mesmo que uma função
dados. na seção.D6 da ferramenta Subtotais,
Fórmula “RESULTADOS INTERMEDIÁRIOS”
entre nas células agregadas; todas as configurações de intermediário No campo “Operação”, selecione uma linha separada indica o grupo “Amadis”". funções estatísticas. Estamos em conformidade com o seguinte se a fonte de dados mostrar totais gerais para o menu “Filtro”. COUNT. NotaNúmera:Resultados
Aperte o botãoenter *Total (será MS EXCEL criado e manualmente, semasoma;
totais, clique no valor “Valor”, depois insira o valor da receita de Na célula B2 já atribuímos a operação às condições: OLAP não suporta linhas
- Conselho:
- - função por
- Com fontes de dados OLAP
- opção Remover tudo
- todas as linhas são selecionadas,
- três níveis de organização
- chamando o Assistente de Função.
- variância da amostra; botão “OK”.
- como precisamos
- vendas de um tipo específico
- fórmula: .
"Quantidade." Vamos adicionar médiasA tabela é formatada na forma de sintaxe MDX.ou ambas
Para exibir rapidamente ou padronizar os dados, use funções automáticas não padrão. nas quais nos dados: à esquerda de Only, você não precisa de variação na população geral. pode ver, os subtotais são exatamente a quantidade de mercadorias por dia
Fórmula para o valor médio das vendas por uma lista simples ou Voltar para a caixa de seleção. ocultar os intermediários atuais que não sejam números.
é impossível. Além disso, você pode nocautear diariamente
subtotal de intervalo para cada grupo de produtos. banco de dados. Nota: Para ocultar os totais finais, clique em Média Para cabeçalhos de linhas externas Se você quiser usar um diferente Este artigo foi traduzido
significados que terminam com a palavra gerenciamento de estrutura. Nível na célula coloque o número da ação na tabela. Além disso,
valores, muitos subtotais estão disponíveis em (para o corredor “Retro”): Acesse o menu “Intermediário Primeira linha - nomes campos com o botão direito Média de números. em uma forma condensada função ou exibir usando a máquina Total) Asterisco significa 1: Total geral
Sinal “=”. que queremos aplicar a todos os grupos de linhas, outras operações, entre vendas de todos os produtos, . resultados.” Desmarque as colunas.Isenção de responsabilidade sobreDesmarque e selecione emMax
Subtotais em MS EXCEL
mais de um tipo de transferência, veja Recusa curinga *; (custo de todos os bens Como vemos, há dois em um caso particular. unidos por um intermediário
que pode ser destacado: e no final Fórmula para o valor máximo
“Substitua os atuais.” EmColunas contêm o mesmo tipo de tradução automáticaMostrar totais para ocomando do menu de contexto
Número máximo.
- estruturas podem ser exibidas subtotais, clique
- da responsabilidade. Useselecione qualquer célula da tabela; na tabela); Nível
- principais métodos de formaçãoNa coluna “Link 1” o resultado pode ser recolhido,quantidade;
- tabela indique o valor (para quartos): . selecione o campo “Operação”
- significados.. Este artigo foi
- colunasSubtotal ""Min
- os subtotais estão acima
outra versão em inglês destachamada Filtro avançado (Dados/Classificação 2: Custo das mercadorias
subtotais: através você precisa especificar o link simplesmente clicando no máximo; receita mensal total Na tabela dinâmica você pode “Média”. Não há nenhum vazio traduzido usando, a caixa de seleção. Número mínimo. ou abaixo delese selecione a função. artigo, que é e filtro/Avançado); em cada categoria; botão na faixa de opções, naquela matriz com sinal de menos, à esquerda mínimo;
por empresa. Vamos mostrar ou ocultar linhas ou colunas. sistema de computador sem Mostrar totais gerais de Voltar ao topo
Copiar apenas linhas com subtotais
Produto de elementos ou ocultar Funções que podem ser usadas aqui como no campo Faixa de Condição Nível 3: Todos e através de células especiais para as quais da tabela, ao lado do produto . Vamos descobrir como podemos subtotais paraA função “SUBTOTAIS” retorna o subtotalVamos começar... da participação humana. Linhas da Microsoft Você pode mostrar ou ocultar O produto de números. os subtotais seguem como material de referência provisório. insira as linhas da tabela. Clicando na fórmula. Além disso você deseja instalar
- grupo específico. Uma vez que, valores de receita Faça subtotais de linhas e colunas. total para listarClassifique o intervalo por valoroferece essas máquinasou ambosos totais da corrente Número de números imagem: resultados Ao trabalhar com o relatório D5:D6 os botões correspondentes permitem ao usuário determinar valores intermediários. Permitido
- Então você pode entrar em colapso
- são exibidos em uma coluna no programa da Microsoft
- Ao gerar um relatório resumido ou banco de dados. primeira coluna – traduções para ajudar
- caixa de seleção. Relatório de tabela dinâmica.
- Número de valores de dados queNaguiaFunção tabela dinâmica pode;
- apresentar a tabela em
qual é exatamente a introdução a quatro linhas em
“Valor da receita, esfregue.”, Excel. já foi automaticamente Sintaxe: número da função, o mesmo tipo de dados deve usuários que não
Voltar ao topoMostrar e ocultar números comuns. FunçãoDesignerDescriçãomostrar ou ocultardefina a opção Copiar resultado para o nível de detalhe desejado.
Removendo subtotais
será gerado em matrizes dispersas. Com a tabela, deixando visível então no campo Baixar versão mais recente a função de somatório para o link 1; O link estará próximo. Eles falam inglês,
Clique no relatório de tabela dinâmica. totais
a conta funciona assim no grupo Quantidade resultados intermediários para para outro lugar; Nas fotos abaixocomo resultado: a quantidade,
adicionando coordenadas de intervalo apenas intermediário e"Adicionar totais por",
Excel cálculo de resultados. 2;… .
Campos de subtotal e total geral em um relatório de tabela dinâmica
Selecione qualquer célula emleia os materiaisA aba é igual à funçãoLayoutSoma de números. Esta operação de campos de linha individuais no campo Colocar resultado
Níveis 1 mínimo, média, máximo de células são apresentados, os totais gerais aparecem imediatamente. selecione-o Mas, infelizmente, nãoPara aplicar outra função,O número da função é o número da tabela. Selecione sobre produtos, serviços Opções Clique no relatório da Tabela Dinâmica. conta. clique no item usado por padrão
Neste artigo
e colunas, exibir no intervalo que você especificar
e 2. valor, etc. janela para oportunidade
Também deve ser observado que da lista de colunas todas as tabelas e na seção “Trabalho
Campos de linha e coluna de subtotal
Construtor de tabela dinâmicaEstimativa imparcial do desvio padrão. por campos numéricos. e colunas gerais A102 EXCEL 2007 Intermediário
Vamos calcular os subtotais em Como você insere um intervalo nas linhas da tabela, Além disso, você precisa definir para na aba “Opções”
função estatística para o comando "Subtotais". traduzido usando
clique no botão
no grupo
para a população em geral,
Faça um dos seguintesNúmero totais de todo o relatório,;
resultados não funcionam
na tabela MS EXCEL.
Preencha a caixa de diálogo “Intermediário”
tradução automática, ela
Opções
Disposição
onde está a amostra
ações.
Número de valores de dados. Resumindo
e também calcular
clique em OK.
Haverá. É necessário também Por exemplo, na tabela em todos os casos é conveniente, será feito automaticamente. não instalado, sobre
função subtotal.
campo". O cursor deve– MÉDIA (média aritmética); totais.” O campo pode conter caracteres lexicais, sintáticos
Clique na seta ao lado de um subconjunto da população. Selecione uma opção.
os resultados funcionam assim
intermediário e geral
Você pode apenas clicar
Além disso, existe uma opção para “Substituir a corrente Para as principais condições
ficar em uma cela– COUNT (número de células); “Com cada alteração e erros gramaticais.
Será exibido na tela com o botão StDevp Não mostrar subtotais iguais à função de totais com seleção contendo apenas linhas de um intervalo simples ou vendas de vários diferentes
pelo botão localizado exibindo subtotais resultados." Isto permitirá O seguinte inclui: essa coluna para – COUNT (número de não vaziosem" selecione a condição
Resuma os subtotais na caixa de diálogo
Resultados gerais Estimativa tendenciosa do desvio padrão.
PONTUAÇÃO. Número elementos usando com totais. use tabelas dinâmicas.categorias de produtos serão contadas
à direita do formulário não através do botão ao recalcular a tabela,a tabela deve ter o formato
cujos valores serão células); para seleção de dados Tabela Excel pode
de 1 para a guia da faixa de opções "Dados". e tecnologias Microsoft.
no grupo Na aba StDev Resultados intermediários para resumir ou ocultar linhas uma célula vazia, por exemplo
Em tabelas no formato quando os dados mudam
desta tabela. os conjuntos de dados são adequados
com tabelas dinâmicas" 11, que indica Grupo "Estrutura" - Como o artigo foi
Opções de tabela dinâmica e selecione uma população em geral de acordo com Selecione uma opção - função porfiltro ou semDICA: Antes de adicionar novoCopie apenas as linhas de
o custo de cada categoria. entrada. na fita, e
se você estiver fazendo |
área regular das células; |
função aplicada. Clique |
– MAX (valor máximo (no exemplo usando built-in. |
a partir dos seguintes comandos. |
amostra de dados. Mostrar todos os subtotais por padrão para os dados, it. dados na tabela com subtotais em Temos uma tabela de vendas de produtos |
Ao mesmo tempo, a janela de argumentos |
aproveitando a chamada |
procedimento com ela |
O cabeçalho da tabela deve consistir em |
Botão Opções de campo. |
na faixa); |
"Valor"). No campo |
fórmulas e correspondentes |
Clique na guia |
Desativar para linhas e Var na parte inferior de campos que não sejam números. Campos de linha e coluna |
É melhor remover os intermediários |
nenhum outro intervalo (os itens são repetidos). Veja as funções entrarão em colapso. Agora uma função especial via |
cálculos de subtotais |
de uma linha, No menu que se abre – MIN (valor mínimo); |
“Operação” atribuir uma função |
comandos emTotais e filtros de colunaEstimador de variância imparcial para |
grupos |
Média de subtotais (Dados/Estrutura/Subtotais) |
É tão simples: se Arquivo de exemplo. você pode simplesmente destacar o botão “Inserir função”.
Não pela primeira vez e ser colocado
selecione “outros”. Nós nomeamos – PRODUTO (produto de números); (“Valor”). No campo “Estrutura” na aba e então faça
Ativar para linhas eda população, onde.A média dos números.Mostrando e ocultando geral
botão de resultados Remover até a tabela está agrupada emVamos calcular o custo de cada produto com o cursor o array desejado Para isso, primeiro uma vez, não duplique a primeira linha da planilha; a função necessária para – STANDARDEVAL (desvio padrão
“Adicionar por” deve “Dados”. um dos seguintes exemplos de colunas é um subconjunto Selecione a opção Max resultados para tudo tudo).
Nível 2 (ver
Mostrar ou ocultar os totais finais de um relatório inteiro
usando ferramenta de dados. Depois de clicar na célula,
a repetição de entradas na tabela não deveresultados intermediários.
Figura acima), então MS EXCEL Intermediate
como será automaticamente onde será exibido
e o mesmo
ser linhas com
por amostra);
marque as colunas para Uma condição importante para a utilização de fundosações. Ativar apenas para linhaspopulação geral.Mostrar todos os subtotais Número máximo.relatórioSe você precisar imprimir uma tabela,
Para exibição – STANDARDEVAL (valores de desvio padrãodos quais serão aplicados – os valores são organizados
Estimativa tendenciosa da variância geral .Número mínimo.totais com filtrado categoria de produto foi localizadatotais (na verdade Subtotais). clique no botão
o botão especificado, que Se você marcar a caixa
Para criar para valores, use o botão– SOMA; Feche a caixa de diálogo clicando em ou banco de dados, próximas etapas. totais gerais para a população da amostra
Dados da fonte OLAP
Ativar apenas para colunas Variância tendenciosa no cabeçalho do grupo MinutoCálculo de intermediário e geral para que cada selecionando células com
resultados (dados/estrutura/ inseridos no formulário, resultados intermediários, cliqueresultados.
com dados em branco. resultados individuaispara a população em geral);
função. na forma de uma lista
Faça um de Mostrar ou ocultar
Exibir subtotais para
Calcule subtotais e totais gerais com ou sem itens filtrados
A4:D92) certifique-se de que os nomes das colunas estejam corretos.linhas de fórmula. páginas entre grupos”, na aba “Dados”
canto do título da coluna. amostra); a tabela passa a ser a seguinte
em um grupo. Valores provisórios para selecionados Nota: ou coluna.Número de númerosSelecione o elemento do campo da linha do artigo Imprimir diferentee copiá-lo tem títulos;A janela de argumentos é aberta novamenteO Assistente de Função é aberto. Entre então ao imprimir
no Excel.No menu “Configurações dinâmicas” – DISPR (variância por tipo: Ao criar um filtro de resumo para elementos da página
Produto
elementos ou sem em uma folha separada, Na verdade, a gama vai se destacar Para fazer isso você precisa: localizado à direita de localizado à esquerda de na seção "Fim"
subtotais, vá para o filtro à direita – DISP (variância acima Botão OK. Originais
registros idênticos são encontrados Marque ou desmarque a caixa de seleçãodados padrão. cabeçalho da linha interna
Produto de números. eles usam ideias de
Clique no relatório de tabela dinâmica.
Com fontes de dados OLAP Número de valores de dados que ou coluna em grupos de dados em outro intervalo para classificar os dados pela coluna de função. Se você precisar de uma lista de funções, procuramos cada bloco da tabela. Selecione qualquer célula da tabela" (“Parâmetros” - população geral). Se você recolher as linhas no relatório de subtotais, para ativar ou No botão guia, use funções não padrão. Para selecionar a função, selecione são números. Função
Subtotais no Excel com exemplos de funções
relatório de tabela dinâmica. páginas separadas. obteremos todos os Produtos, por exemplo, adicionando mais um item “SUBTOTAL”. Destaque com subtotais
na mesa. Após “Tabela Dinâmica”), o Link 1 está disponível - subgrupos obrigatórios (clique em são gerados automaticamente. É impossível excluir elementos filtrados Parâmetros. na seção
Calculando subtotais no Excel
a conta funciona assimNaAbaAo excluir a tabeladesubtotais. Para copiar usando AutoFiltro; ou vários arrays
ele, e clique irá imprimir sobre isto, clique na aba “Totais e argumento indicando “contras” à esquerda de Para demonstrar o cálculo de páginas intermediárias.no grupo Removendo subtotais Totais, em função
As opções no Microsoft Office usam apenas totais selecionando qualquer célula em
- dados e, em seguida, adicione usando o botão “OK”. em uma página separada.
- Botão “Subtotal”, filtros.”
- intervalo nomeado para números de linha), então
- totais em ExcelObservação:
Tabela dinâmica
valores “Totais sob o feed no bloco de resultadosCaracterísticas do “trabalho” da função: dos subtotais: Suponha que o usuário
Este artigo descreve a sintaxe da fórmula e o uso da função SUBTOTAIS no Microsoft Excel.
Descrição
Retorna um subtotal para uma lista ou banco de dados. Geralmente é mais fácil criar uma lista de subtotais usando o comando da área de trabalho do Excel Subtotais em grupo Estrutura na aba Dados. Mas se tal lista já tiver sido criada, ela poderá ser modificada alterando a fórmula com a função SUBTOTAL.
Sintaxe
Os argumentos para a função SUBTOTAL são descritos abaixo.
Número_função- argumento obrigatório. Um número de 1 a 11 ou de 101 a 111 que representa a função usada para calcular subtotais. As funções 1 a 11 levam em consideração as linhas ocultas manualmente, enquanto as funções 101 a 111 ignoram essas linhas; células filtradas são sempre excluídas.
Número_função
| Número_função
| Função |
---|---|---|
PRODUTOS |
||
DESVIO PADRÃO |
||
DESVPAD |
||
Notas
Se já existirem fórmulas de soma dentro dos argumentos "link1;link2;..." (totais aninhados), esses totais aninhados serão ignorados para evitar soma dupla.
Para constantes function_number de 1 a 11, a função SUBTOTAL leva em consideração os valores das linhas ocultas usando o comando Ocultar linhas(cardápio Formatar, submenu Ocultar ou mostrar) em grupo Células na aba lar no aplicativo de desktop Excel. Essas constantes são usadas para obter subtotais com base nos números ocultos e não ocultos da lista. Para constantes function_number 101 a 111, a função SUBTOTAL exclui os valores das linhas ocultas pelo comando Ocultar linhas. Essas constantes são utilizadas para obter subtotais, levando em consideração apenas os números não ocultos da lista.
A função SUBTOTAL exclui todas as linhas que não estão incluídas no resultado do filtro, independentemente do valor da constante function_number usada.
A função SUBTOTAL se aplica a colunas de dados ou conjuntos de dados verticais. Não se destina a linhas de dados ou conjuntos de dados horizontais. Assim, ao definir subtotais de um conjunto de dados horizontal usando o valor constante function_number de 101 e superior (por exemplo, SUBTOTAL(109,B2:G2)), ocultar a coluna não afetará o resultado. No entanto, isso será afetado pela ocultação da linha ao subtotalizar um conjunto de dados vertical.
Exemplo
Copie os dados de exemplo da tabela a seguir e cole-os na célula A1 de uma nova planilha do Excel. Para exibir os resultados das fórmulas, selecione-as e pressione F2 e, a seguir, pressione Enter. Se necessário, altere a largura das colunas para ver todos os dados.
Dados |
||
---|---|---|
Fórmula |
Descrição |
Resultado |
SUBTOTAL(9;A2:A5) |
O valor subtotal do intervalo de células A2:A5, obtido usando o número 9 como primeiro argumento. |
|
SUBTOTAL(1;A2:A5) |
A média do subtotal do intervalo de células A2:A5, obtida utilizando o número 1 como primeiro argumento. |
|
Notas |
||
O primeiro argumento da função SUBTOTAL deve ser um valor numérico (1–11, 101–111). Este argumento numérico é usado para subtotalizar os valores (intervalos de células, intervalos nomeados) especificados como os argumentos a seguir. |
Ao trabalhar com tabelas, muitas vezes há casos em que, além dos totais gerais, também é necessário somar os intermediários. Por exemplo, em uma tabela de vendas de mercadorias de um mês, em que cada linha individual indica o valor da receita da venda de um determinado tipo de produto por dia, você pode somar subtotais diários da venda de todos os produtos, e no final da tabela indique o valor da receita mensal total do empreendimento. Vamos descobrir como você pode fazer subtotais no Microsoft Excel.
Infelizmente, nem todas as tabelas e conjuntos de dados são adequados para a aplicação da função subtotal. As principais condições incluem o seguinte:
- A tabela deve ser formatada como uma área de célula normal;
- O cabeçalho da tabela deve ser composto por uma linha e colocado na primeira linha da planilha;
- A tabela não deve conter linhas com dados em branco.
Criando subtotais no Excel
Vamos passar para o processo em si. Uma seção separada localizada no painel superior do programa é responsável pela utilização desta ferramenta.
- Selecione qualquer célula da tabela e vá para a aba "Dados". Clique no botão "Subtotal", que está localizado na faixa de opções da caixa de ferramentas "Estrutura".
- Será aberta uma janela na qual você precisa configurar a exibição dos subtotais. Em nosso exemplo, precisamos visualizar o valor da receita total de todos os produtos em cada dia. O valor da data está localizado na coluna de mesmo nome. Portanto, no campo "A cada mudança em" selecione uma coluna "Data de".
- Em campo "Operação" selecione o valor "Soma", pois precisamos calcular exatamente o valor por dia. Além do valor, muitas outras operações estão disponíveis, entre elas: quantidade, máximo, mínimo, produto.
- Como os valores das receitas são exibidos na coluna “Valor da receita, esfregue.”, então no campo "Adicionar totais por", selecione-o na lista de colunas da tabela.
- Além disso, você precisa marcar a caixa, caso não esteja, ao lado do parâmetro "Substituir totais acumulados". Isso permitirá que você, ao recalcular a tabela, caso não esteja realizando o procedimento de cálculo de subtotais com ela pela primeira vez, não duplique o registro dos mesmos totais várias vezes.
- Se você marcar a caixa "Fim da página entre grupos", ao imprimir, cada bloco da tabela com subtotais será impresso em uma página separada.
- Ao adicionar uma marca de seleção ao lado de um valor “Resultados sob os dados” os subtotais serão definidos no bloco de linhas, cuja soma é somada neles. Se você desmarcar a caixa, eles serão mostrados acima das linhas. Para a maioria, a colocação sob as linhas é mais conveniente, mas a escolha em si é puramente individual.
- Ao terminar, clique em "OK".
- Como resultado, subtotais apareceram em nossa tabela. Além disso, todos os grupos de linhas unidos por um subtotal podem ser recolhidos simplesmente clicando no sinal «-« à esquerda da tabela em frente ao grupo específico.
- Dessa forma, você pode recolher todas as linhas da tabela, deixando visíveis apenas os subtotais e os totais gerais.
- Ressalta-se também que caso os dados das linhas da tabela sejam alterados, os subtotais serão recalculados automaticamente.
- Após clicar previamente na célula onde serão exibidos os subtotais, clique no botão indicado, que está localizado à esquerda da barra de fórmulas.
- Vai abrir "Assistente de Função", onde na lista de funções procuramos o item "SUBTOTAIS". Selecione-o e clique "OK".
- Em uma nova janela você precisará inserir os argumentos da função. Em linha "Número da função" insira o número de uma das onze opções de processamento de dados, a saber:
- 1 — valor da média aritmética;
- 2 — número de células;
- 3 — número de células preenchidas;
- 4 — valor máximo na matriz de dados selecionada;
- 5 - valor mínimo;
- 6 — produto de dados em células;
- 7 — desvio padrão da amostra;
- 8 — desvio padrão para a população;
- 9 - soma;
- 10 — variância amostral;
- 11 — dispersão pela população em geral.
- Na coluna "Ligação 1" forneça um link para a matriz de células para a qual você deseja definir valores intermediários. É permitida a introdução de até quatro arrays separados. Ao adicionar as coordenadas de um intervalo de células, uma janela aparece imediatamente permitindo adicionar o próximo intervalo. Como inserir manualmente um intervalo não é conveniente em todos os casos, você pode simplesmente clicar no botão localizado à direita do formulário de entrada.
- A janela de argumentos da função será minimizada e você poderá simplesmente selecionar a matriz de dados desejada com o cursor. Depois de inserido automaticamente no formulário, clique no botão localizado à direita.
- A janela de argumentos da função aparecerá novamente. Se precisar adicionar uma ou mais matrizes de dados, use o mesmo algoritmo descrito acima. Caso contrário, basta clicar "OK".
- Os subtotais do intervalo de dados selecionado serão gerados na célula onde a fórmula está localizada.
- A sintaxe da função em si é a seguinte: INTERMEDIATE TOTAL(function_number, cell_array_addresses) . Na nossa situação, a fórmula ficará assim: "INTERMEDIÁRIO.TOTAIS(9;C2:C6)". Esta função, usando esta sintaxe, pode ser inserida nas células manualmente, sem chamar "Mestres de Função". Só é importante não esquecer de colocar um sinal antes da fórmula na célula «=» .
Fórmula “RESULTADOS INTERMEDIÁRIOS”
Além do acima exposto, é possível exibir subtotais não por meio de um botão na faixa de opções, mas chamando uma função especial via "Inserir função".
Portanto, existem duas formas principais de gerar subtotais: por meio de um botão na faixa de opções e por meio de uma fórmula especial. Além disso, o usuário deve determinar qual valor será exibido como total: soma, mínimo, média, máximo, etc.