No Excel, como multiplico valores separados por valores separados em outra coluna?

Question

Eu tenho duas colunas de dados. A coluna A contém os itens, a coluna B contém a quantidade dos itens. No exemplo abaixo, gostaria de contar a quantidade de "garfos":

 - A1: spoon|fork|knife
 - A2: spoon|bottle|fork|piston
 - B1: 2|1|1
 - B2: 6|2|4|3

Ou veja a imagem abaixo:

No total, haveria cinco garfos; um garfo na linha 1 e cinco garfos na linha 2.

Como eu contaria esse total de garfos?

    

Answer 1

Use COUNTIF . Por exemplo, na linha 9, digite: =COUNTIF(A1:A7, "star") e, em seguida, você verá o número de ocorrências.

Alémdisso,vocêpodeusarumaexcelenteferramentaonline: link (no primeiro campo, digite a palavra desejada, ou seja, fork e, em seguida, insira o texto abaixo)

( fonte )

    

Answer 2

Esta é uma maneira de fazer isso. Eu estou supondo que você está copiando e colando de outro lugar.

1. Em vez de trazer seus dados para o Excel, salve seus dados originais como um arquivo .txt.
2. Abra o Excel e abra uma nova pasta de trabalho em branco.
3. Abaixo da faixa de dados, use a opção "Obter dados externos" "Do texto" (lado esquerdo)
4. Selecione seu arquivo .txt que você salvou, você virá para o Assistente de importação de texto.
5. No passo 1, selecione "Delimitado"
6. Na etapa 2, selecione "Outro" e digite o caractere delimitador que você está usando (no seu caso, seu "|").
7. Ignore a etapa 3 clicando em Concluir.

8. Escolha "Nova planilha" como seu destino de saída

   Agora seus dados estarão em um formato um pouco melhor. Observe que você pode executar as etapas acima usando a função "Text to Columns" se os dados já estiverem no Excel.

9. Na Folha1 (ou em qualquer folha em branco), faça os títulos das colunas:

   • A1: ITEM
   • A2: QUANTIDADE

10. Veja como você transforma os dados misturados para os dados utilizáveis:

    Em A2, coloque esta fórmula:

    =OFFSET(Sheet4!$A$1,INT((ROW()-2)/4),MOD(ROW()-2,4))  
    

    • Certifique-se de substituir "Sheet4!$A$1" pela célula onde seus dados do ITEM são iniciados. No seu exemplo, esta seria a célula com a primeira "colher"
    • Verifique se você tem os cifrões (ou seja, não A1 , mas $A$1 )
    • Se necessário, substitua o número 4 da fórmula por quantas colunas existem dos dados do item. No seu caso, o máximo foi 4)

11. Em B2, repita o que você fez em A2, exceto substituir o "Sheet4!$A$1" pela célula onde seus dados QUANTITY começam. Para mim, parece assim:

    =OFFSET(Sheet4!$E$1,INT((ROW()-2)/4),MOD(ROW()-2,4))  
    

12. Agora, tudo o que você precisa fazer é preencher essa fórmula até obter uma linha para cada combinação Item / Quantidade. Com o seu exemplo, é assim:

    ITEM    QUANTITY
    spoon   2
    fork    1
    knife   1
    0       0
    spoon   6
    bottle  2
    fork    4
    piston  3
    

13. Em seguida, copie a coluna A do resultado acima e passe-a para uma nova folha em branco na mesma pasta de trabalho. (Estou usando o Sheet2)

14. Enquanto os dados colados ainda estiverem selecionados, na faixa de opções Dados, escolha "Remover duplicados"

15. Na caixa de diálogo que aparece, marque a caixa "Meus dados tem cabeçalhos" e clique em OK

    Você deve ter isso agora:

    ITEM  
    spoon  
    fork  
    knife  
    0  
    spoon  
    bottle  
    

16. Em B1, crie um cabeçalho de coluna "QUANTIDADE"

17. Em B2, coloque a seguinte fórmula:

    =SUMIF(Sheet1!$A:$A,$A2,Sheet1!$B:$B)
    

    Certifique-se de substituir "Sheet1" por qualquer folha usada nas primeiras fórmulas.

Aqui está o resultado!

ITEM    QUANTITY  
spoon   8  
fork    5  
knife   1  
0       0  <-- Go ahead and delete this row if you want!  
spoon   8  
bottle  2

Você pode salvar essa pasta de trabalho do Excel e reutilizá-la para futuras importações de dados, desde que você continue colocando os dados de texto importados no mesmo local e o número de colunas não seja alterado. Se eles apenas atualizarem as fórmulas de acordo.

Como outros apontaram, este é um formato de dados bizarro para começar, mas eu assumo que essas são as restrições com as quais você está preso.