Agregando Colunas no Excel

Question

Eu tenho dados que consistem em uma data e uma lista de compras, algo assim (vírgulas representam colunas separadas):

**date,product1,product2,product3** 
08/11/13,oranges,apples,chips
08/11/13,grapes,oranges
08/12/13,grapes,pineapples
08/12/13,grapes,oranges

Eu quero saber quantos itens de cada produto foram comprados em cada data (e plotar).

Se eu tivesse uma única coluna de produto, seria bem simples; Eu poderia apenas usar uma tabela dinâmica. No entanto, no meu caso, para 11 de agosto, preciso de uma maneira de contar as "laranjas" do product1 junto com as "laranjas" do product2 ...

Uma maneira seria duplicar linhas e traduzir os dados para uma única coluna, mas gostaríamos de evitá-las.

Existe uma maneira de fazer isso, de preferência sem o uso de VBA?

    

Answer 1

Se os seus dados estiverem no intervalo A2: D25, você poderá usar a seguinte fórmula de matriz:

=SUM(($A$2:$A$25=DATEVALUE("8/11/13"))*
    (($B$2:$B$25="oranges")+($C$2:$C$25="oranges")+($D$2:$D$25="oranges")))

Você precisará inseri-lo com a combinação de teclas Control - Shift - Enter .

Se você tiver várias datas para as quais deseja calcular os totais para cada um de um grande número de produtos, faça o seguinte.

Vamos supor que os dados do produto estejam no intervalo A2: D20000 na Folha 1, com o intervalo de datas na coluna A, como no seu exemplo. Digamos que você queira colocar as contagens de produtos na Planilha2.

Na Planilha2, você entraria nas células B1, C1, etc., nas datas em que está interessado. Abaixo disso, no intervalo A2: A2000, você tem a lista de seus produtos. Na célula B2, insira essa fórmula de matriz:

=SUM((Sheet1!$A$2:$A$20000=B$1)*((Sheet1!$B$2:$B$20000=$A2)+
     (Sheet1!$C$2:$C$20000=$A2)+(Sheet1!$D$2:$D$20000=$A2)))

e copie-o para o final da lista de produtos. Isso lhe dá as contas para a primeira data.

Em seguida, copie toda a coluna de contagens de produtos para cada coluna que tenha uma data na linha 1. Isso fornece as contagens de todas as datas.

Como alternativa, se você tiver um grande número de produtos e um grande número de datas, uma fórmula não matricial um pouco mais complicada dará a você um melhor desempenho (ou seja, levará menos tempo para calcular).

Na célula B2, você deve digitar:

     =COUNTIFS(Sheet1!$A$2:$A$20000,B$1,Sheet1!$B$2:$B$20000,$A2)+
      COUNTIFS(Sheet1!$A$2:$A$20000,B$1,Sheet1!$C$2:$C$20000,$A2)+
      COUNTIFS(Sheet1!$A$2:$A$20000,B$1,Sheet1!$D$2:$D$20000,$A2)

Em seguida, copie a fórmula para a parte inferior da lista de produtos e copie toda a coluna de fórmulas (no intervalo B2: B2000) em todas as colunas para as quais você colocou as datas na linha 1 .