Questão Como posso "agrupar por" e somar uma coluna no excel?


Estou tentando descobrir como "agrupar por" valores em uma planilha do Excel.

Dado o exemplo a seguir (esta é uma folha de inventário eu uso no meu restaurante :)

Sample Spreadsheet

Na extremidade direita da planilha, há uma coluna chamada Custo das mercadorias vendidas.

Eu gostaria de ser capaz de chegar a um valor total de COGS para cada categoria.

Isso é possível no excel? Além disso, gostaria de fazê-lo em uma planilha separada para que a primeira planilha (o que está na foto) não seja afetada.

Alguma ideia?

Obrigado!


129


origem




Respostas:


O recurso que você deseja usar é Tabelas Dinâmicas. É muito fácil fazer exatamente o que você quer com apenas alguns cliques quando você aprender a usar esse recurso.


80



Omg isso é muito fácil! - Rosdi
Isso não responde a pergunta - Iron Savior
Há algumas instruções básicas nesta resposta stackoverflow.com/a/15838994/74585 - Matthew Lock


Você também pode fazer isso com o recurso Subtotal.

  1. Clique na guia Dados na barra de ferramentas da faixa de opções do Excel
  2. Clique no botão Classificar e classifique pelo seu categoria coluna
  3. Clique no botão Subtotal e preencha a caixa de diálogo conforme apropriado e clique em OK

Por exemplo...

A cada mudança em:    Categoria

Use a função:    Soma

Adicione subtotal a:    Custo de bens vendidos


52



Onde está o botão subtotal? Você poderia adicionar uma captura de tela? - Matthew Lock
@MatthewLock veja addintools.com/documents/excel/where-is-subtotal-button.html (e i.imgur.com/kkSa2AB.png). Note que o botão é desativado ao usar tabelas. - matt wilkie


Eu estou supondo que você está procurando o total de CPV para uma conta como mercearia, por exemplo, para a semana 1.

sumif é a maneira mais fácil de obter esses dados. exemplo tirado da sua foto:

=sumif(d2:d?,"grocery",j2:j?)

Coloquei os pontos de interrogação, pois não consigo ver a folha inteira. Você selecionaria toda a coluna D, onde você tem dados da conta. D2 a D ?, Igual à coluna de uso semanal J2 a J ?.

A fórmula irá aparecer na coluna D para a mercearia de texto, se estiver presente, ela adicionará as células na coluna J que correspondem às colunas D que têm a palavra mercearia nela. (ou qualquer outro texto que você colocar na fórmula para procurar)


12



Esta é uma solução ótima e super fácil. Tabelas dinâmicas são um exagero para muitas necessidades como essa. - T. Brian Jones


A abordagem mais trivial pode ser uma operação de classificação do Excel seguida por uma operação de subtotal do Excel.

Ele produz o que você procura sem alterar todo o layout de seus dados como faria a operação Pivot.

Classificação

  • Clique no Dados aba (ou pressione Alt-A no teclado)
  • Clique Ordenar (ou pressione S no teclado)
  • Clique na seta suspensa ao lado de "Classificar por" na área principal da janela pop-up.
  • Selecione "Categoria", pois é isso que você deseja agrupar.
  • Clique OK!

Subtotal

  • Clique no Dados aba (ou pressione Alt-A no teclado)
  • Clique Subtotal ícone (ou pressione B no teclado)
  • Agora que seus dados estão classificados, leia e preencha o pop-up Subtotal como você estava lendo Inglês natural:
           Em cada mudança
              Categoria (Clique na seta suspensa e selecione isso)
           Use a função
              Soma (Clique na seta suspensa e selecione isso)
           Adicionar subtotal a
              Custo de boa venda (selecione as colunas que você deseja totalizar para categorias individuais)
  •    Click OK!
    


    5





    No Excel 2013, haverá uma opção "agrupamento".

    1. Selecione sua mesa.
    2. Vá para a guia Dados.
    3. Clique em Estrutura> Agrupamento.

    Veja a imagem abaixo, infelizmente, é alemão - mas os ícones ajudarão você a encontrá-lo.

    Gliederung > Gruppieren


    4





    Eu faço isso o tempo todo com vba. Tenho certeza que usei o mesmo método desde o escritório 95 ', com pequenas alterações feitas para o posicionamento da coluna. Isso pode ser feito com menos linhas se você não definir as variáveis. Isso pode ser feito mais rápido se você tiver muitas linhas para passar ou mais coisas com as quais precisa definir seu grupo.

    Eu encontrei situações em que um 'grupo' é baseado em 2-5 células. Este exemplo examina apenas uma coluna, mas ela pode ser expandida facilmente se alguém dispuser de tempo para jogar com ela.

    Isso pressupõe 3 colunas e você precisa classificar pela coluna group_values. Antes de executar a macro, selecione a primeira célula que deseja comparar na coluna group_values.

    'group_values, some_number, empty_columnToHoldSubtotals
    '(as coisas vão aqui)
    'cookie 1 vazio
    'cookie 3 vazio
    'bolo 4 vazio
    'chapéu 0 vazio
    'chapéu 3 vazio
    '...
    'Pare
    
    Sub subtotal ()
      'definir duas cadeias de caracteres e um contador subtotal
      Dim thisOne, thatOne As String
      Dim subcount como Double
    
      'semeie os valores
      thisOne = ActiveCell.Value
      thatOne = ActiveCell.Offset (1, 0)
      subcount = 0
    
      'configurar um loop que vai até atingir um valor de parada
      While (ActiveCell.Value <> "stop")
    
        'compara um valor de célula com a célula abaixo dele.
        Se (thisOne = thatOne) Então
          'se as células forem iguais, a contagem de linhas é adicionada à subconta
          subCount = subCount + ActiveCell.Offset (0, 1) .Value
    
        Outro
          'se as células não forem iguais, a subconta será gravada e o subtotal será redefinido.
          ActiveCell.Offset (0, 2) .Value = ActiveCell.Offset (0, 1) .Valor + subcontagem
          subcount = 0
    
        Fim se
    
        'selecione a próxima célula abaixo
        ActiveCell.Offset (1, 0) .Selecione
    
        'atribuir os valores da célula ativa e o que está abaixo dela às variáveis
        thisOne = ActiveCell.Value
        thatOne = ActiveCell.Offset (1, 0)
      Wend
    
    End Sub
    

    1





    Para fazer isso com um mesa giratória:

    1) Selecione todos os dados (incluindo os rótulos das colunas)
    2) Inserir> Tabela Dinâmica
    3) Arraste a dimensão para agrupar na área de colunas ou linhas
    4) Arraste métrica para somar na área de valores

    enter image description here


    0





    1) Selecione o intervalo de dados.
    2) Copie o intervalo para a área de transferência.
    3) Abrir novo acesso à base de dados.
    4) Design nova tabela ou faixa de pasta.
    5) Criar Vista, Visualização SQL.
    6) Criar consulta "SELECT célula, soma (dados) como total da tabela GROUP BY célula".





    -2





    No Excel 2013:

    -Clique dados

    -Click Subtotal


    -3