Como calcular as diferenças com base nos valores de agrupamento em outra coluna no Excel

Question

Eu tenho vários conjuntos de dados com uma coluna de agrupamento contendo nomes de raízes de plantas (Rname) e uma coluna indicando o dia do ano em que a raiz foi detectada (com cada um desses dias indicando uma sessão de medição durante um experimento - houve para seis sessões no total). Aqui está uma amostra dos dados:

Eu quero encontrar o tempo de vida total da raiz nessas sessões, o que espero conseguir simplesmente subtraindo o dia do ano em que a raiz foi detectada pela primeira vez a partir do dia do último ano de detecção (como mostrado na a imagem).

Eu não sou proficiente com o Excel, e eu realmente não sei como codificar uma macro ou fazer qualquer coisa sofisticada como essa. Eu preciso fazer isso por cerca de 70 planilhas separadas, e seria quase impossível calcular isso manualmente para cada raiz. Existe alguma maneira de fazer isso automaticamente com uma coluna auxiliar ou algo assim?

    

Answer 1

Minha solução

Primeiro, mantenha suas colunas A e B classificadas em ordem crescente.

Em seguida, assumindo que a linha 1 é uma linha de cabeçalho, defina

C2 a =IF(NOT(A2=A1),ROW(),0) ;

D2 a =IF(AND(NOT(C2=C1),NOT(C2=0)),CONCATENATE("B",TEXT(C2,"0")),D1) ;

E2 a =IF(NOT(A3=A2),ROW(),0) ;

F2 a =IF(AND(NOT(E2=E1),NOT(E2=0)),CONCATENATE("B",TEXT(E2,"0")),F3) ;

e, finalmente, G2 to =INDIRECT(F2)-INDIRECT(D2) .

Preenchimento automático de colunas C a G até a última linha e a vida útil do raiz Axe na célula Gx.

Aqui está a saída com seus dados de amostra:

Explicações

Classifiqueasduasprimeirascolunasparagarantirqueosdadossejamagrupadosporraízes,eosúltimosdadosmenososprimeirosdadosemcadagruposãootempodevidadessaraiz.

Useduascolunassuplementares(C&D)paraencontraroíndicedecélulasdosprimeirosdadoseoutrasduasparaencontraroíndicedosúltimosdados.

OtruqueéquecadavezquevocêobservaovalordacolunaAmuda,vocêsabequeumgrupodedadosterminoueumnovocomeçou.

DepoisdepreencherascolunasDeFcomíndicesdecélulas,useafunçãoINDIRECTparabuscarorespectivovaloresubtraí-las.

Referência

TEXTO função

Função INDIRECTA

    

Answer 2

Em geral, provavelmente seria esperado que os dias e o número de medições não fossem os mesmos para todos os grupos-raiz. A fórmula abaixo fornece os resultados corretos quando os dias e o número de medições variam.

=IF(A2<>A3,MAX(IF(A:A=A2,B:B,FALSE))-MIN(IF(A:A=A2,B:B,FALSE)),"")

Esta é uma fórmula de matriz e deve ser inserida em C2 com CTRL + Deslocamento + Enter , em vez de apenas Enter . Depois que ele for inserido, clique em C2 e preencha a última linha de seus dados. A tabela abaixo mostra os resultados:

Como funciona: As expressões internas IF(A:A=A2,B:B,FALSE) retornam uma matriz dos valores da Coluna B, onde Coluna A = Um (o Nome R particular na linha atual) e FALSE em todos os lugares. Assim, as Jornadas do Ano são isoladas para cada Nome R, sejam elas quais forem e não importando quantas sejam. Então, a Vida é calculada subtraindo-se o Min do Max daquele array.

O IF () externo coloca o cálculo da Vida no final de um grupo de R Names e em branco em qualquer outro lugar.

Espero que isso ajude e boa sorte.

    

Answer 3

@Dunius, gostaria de sugerir-lhe a solução imediata, como o grupo Subtraction . E está funcionando corretamente usando seus dados.

Comoparacadaconjuntodecódigos,vocêtemumvalordediadoanomaisbaixoemaisalto.

EntãominhaSoluçãoé

=SE($A201<<$A200,(MÁXIMO(B$200:B200)-MIN(B$200:B200)),"")

Arraste a fórmula para baixo.

E me dê algum tempo para criar código VBA para o mesmo trabalho.

NB: Fórmula na célula C200. Altere o intervalo de dados conforme necessário.

    

Answer 4

@Dunois, Gostaria de sugerir duas soluções. O primeiro está relacionado ao post anterior e o segundo é improvisado.

Solução 1:

Em relação ao post anterior, sua pergunta era se a fórmula funcionaria para dados de tamanho variável ou não?

Sim, vai funcionar. Verifique a captura de tela Root3 tem 7 linhas e a fórmula é simplesmente arrastada para o comprimento, ou seja,

= SE ($ A201 < < $ A200, (MÁXIMO (B $ 200: B200) -MIN (B $ 200: B200)), "")

NB:OintervalodedadosédeA200aC218(excluindolinhaCabeçalho).CéluladefórmulaéC200.Ajustearaivadosdadosconformesuanecessidade.

Solução2:

Éimprovisado.Paraisso,ointervalodedadosprecisaserclassificadoeaplicarocomandoGroupe,finalmente,usaraseguintefórmula.

=SE(F200="", MÁXIMO (G200: ÍNDICE (G $ 199: G199, CORRESPONDÊNCIA (2,1 / (G $ 199: $ G199=""), 1))) - MIN (G200: INDEX ( G $ 199: G199, PARTIDA (2,1 / (G $ 199: $ G199=""), 1))), "")

NB: O intervalo de dados é de F200 a H218 (excluindo linha de Cabeçalho). A fórmula está na célula H200 .

Além disso, propositalmente eliminei para Heading from G199 .

Se você quiser evitar, escreva os títulos na linha 199 e inicie a primeira entrada da linha 201 e não se esqueça de alterar o endereço da célula na fórmula.

Ajuste a raiva dos dados conforme sua necessidade.