Excel fórmulas se ajustem de acordo com os horários que a pessoa Dormiu

Igor Moraes Mariano · 25 de setembro de 2018

Ola gostaria de saber se alguém consegue me ajudar.

Tenho uma planilha 3 colunas: Hora da medida, Pressão arterial sistólica (PAS) e Pressão Arterial Diastólica (PAD)
Gostaria de fazer uma fórmula que faça as médias das pressões hora a hora (medidas entre 08:00 e 09:00, entre 09:00 e 10:00 [...])

Tem como eu fazer algo tipo: média de PAS, SE nas células de hora estiver entre 08:00 09:00, sendo que a média só pode envolver as PAS referentes àquele horário.

Segue um exemplo Zipado.

Agradeço desde ja.

ppeterk · 25 de setembro de 2018

Poste um exemplo zipado para facilitar!

Igor Moraes Mariano · 25 de setembro de 2018

Ja adicionei o arquivo de Exemplo Zipado como pedido. Obrigado pela dica.

ppeterk · 25 de setembro de 2018

Pelo que entendi...

Veja se lhe atende a coluna B e as médias!

Exemplo_Ppeterk.rar

Edit: Estou relendo seu post... você deseja realizar as médias de todas as linhas que se encontram no horário desejado?

Igor Moraes Mariano · 25 de setembro de 2018

A formula de quantos minutos passaram deu certo, muito obrigado.

Mas a outra eu acho que não havia me expressado bem, portanto coloquei uma nova versão do exemplo (exemplo V2) no post.

As médias que você fez levam em consideração os valores das linhas, mas eu preciso que leve em consideração os valores das colunas. PAS, PAD, PAM e FC são variaveis diferentes, então eu preciso da media dos valores de cada variável dessa que estejam dentro de intervalos de tempo determinados na coluna A.

Será que ficou compreensível? qualquer duvida estou a disposição e agradeço desde já.

Edson Luiz Branco · 25 de setembro de 2018

Boa noite, @Igor Moraes Mariano

Avalie o anexo se é isso...

Exemplo.xlsx

Igor Moraes Mariano · 25 de setembro de 2018

@Edson Luiz Branco Aparentemente deu certo sim, muito obrigado, Só não entendi o porque o único valor que não bate com a media sendo feita manualmente é o da celula J3

Atualização: eu percebi que essa célula com erro estava apenas com Enter, então dei um CTRL + SSIFT + Enter, e ai deu certo.

Mas quando coloco as formulas para outras séries de dados, as médias quando a hora é 0 ficam bem erradas. Mas o restante esta certo.

adicionado 22 minutos depois

@Edson Luiz Branco Agradeço demais a ajuda, vai facilitar muito as tabelas do meu mestrado!!!

A unica coisa que poderia ajudar mais ainda é que, dependendo do voluntario o aparelho foi colocado as 07, as 08 ou as 09 horas, e portanto a primeira hora da média será diferente. No exemplo V2 a hora inicial foi 9, mas poderia ter sido 7 ou 8. Existe uma maneira de a fórmula analisar a primeira célula da Coluna de hora e a partir disso definir qual será a primeira hora da média?

E o Bug quando a hora é 0 persiste quando colo a formula em outras series de dados.

Agradeço novamente!

Edson Luiz Branco · 25 de setembro de 2018

1 hora atrás, Igor Moraes Mariano disse:

...as médias quando a hora é 0 ficam bem erradas. Mas o restante esta certo.

...E o Bug quando a hora é 0 persiste quando colo a formula em outras series de dados.

Não entendi em que situação isso está ocorrendo para você, pois veja que a fórmula para o valor zero, que também é matricial, é idêntica às demais fórmulas, só muda a hora de comparação. Tomemos como exemplo a fórmula da Pressão Arterial Sistólica (PAS):

=SEERRO(MÉDIA(SE(HORA($A$3:$A$77)=0;$D$3:$D$77;""));"")

Em seu exemplo:

As médias calculadas pela fórmula foram, respectivamente,

Que são os valores corretos para média dos dados para as horas entre zero (inclusive) e uma hora da manhã (exclusive).

Igor Moraes Mariano · 26 de setembro de 2018

@Edson Luiz Branco pois é, eu concordo com você, mas olha neste outro exemplo. Eu marquei em vermelho o local que deu errado.

Exemplo V3.xlsx

Edson Luiz Branco · 26 de setembro de 2018

O problema é que você não ajustou o intervalo para o tamanho de sua tabela de dados e ele está considerando na fórmula ainda até a linha 77 daquela que fiz mas sua nova tabela só vai até a linha 64. Logicamente que, como da linha 64 a 77 não existem dados, são considerados zero e entram na composição da média como tal. Não bastasse isso, em sua fórmula você está considerando até a linha 120!

Aliás, percebi que em seu último arquivo você acabou fazendo uma completa lambança nas fórmulas hehehehe:

A fórmula em J3 não está matricial
As fórmulas no intervalo J3 a M5 consideram só o intervalo até a linha 20 (menor do que o intervalo real)
As fórmulas no intervalo J6 a M25 consideram o intervalo até a linha 120 (maior do que o intervalo real)
As fórmulas no intervalo J26 a M28 consideram um intervalo absurdo, começando no meio da tabela (linha 30) e indo até além dela (120)

Talvez seja o cansaço, isso acontece.

Igor Moraes Mariano · 26 de setembro de 2018

@Edson Luiz Branco

Então na verdade eu adaptei as formulas do inicio da tabela e do fim da tabela com o seguinte pensamento:

A fórmula em J3 não está matricial

R: Agora ja fiz ela ficar matricial

As fórmulas no intervalo J3 a M5 consideram só o intervalo até a linha 20 (menor do que o intervalo real)

R: É porque se o individuo ficar mais de 24 horas com o aparelho, 07 ou 8 da manha pode aparecer de dois dias diferentes. Então no inicio da tabela eu coloquei para ir até a linha 20 como forma de evitar que leve em consideração a manhã do dia seguinte. E estes horários ocupam as 5 a 15 primeiras linhas, por isso coloquei até o 20. E estas equações não deram problema e não alteraram suas médias.

As fórmulas no intervalo J6 a M25 consideram o intervalo até a linha 120 (maior do que o intervalo real)

R: É porque dependendo dos indivíduos esta tabela terá entre 60 e 110 linhas, então coloquei até a 120. Mas estas formulas aqui não tiveram nenhum problema e não alteraram sua média.

As fórmulas no intervalo J26 a M28 consideram um intervalo absurdo, começando no meio da tabela (linha 30) e indo até além dela (120)

R: A resposta é similar a sua segunda pergunta. É porque se o individuo ficar mais de 24 horas com o aparelho, 07 ou 08 da manha pode aparecer de dois dias diferentes. Então no fim da tabela eu coloquei para ir a partir linha 30 como forma de evitar que leve em consideração a manhã do dia anterior. E estes horários ocupam as ultimas linhas, por isso coloquei a partir do 30. E estas equações não deram problema e não alteraram suas médias.

Estes raciocínios estão corretos?

Em relação as contas quando a hora é 0, ai sim foi erro meu. Imaginei que as células vazias não seriam contabilizadas. Existe uma forma de não contabiliza-las? ou acha melhor eu ajustar o tamanho da série de dados nestas células de acordo com o individuo?

Edson Luiz Branco · 26 de setembro de 2018

Ok, agora diante desses fatos novos (possibilidade de passar de 24h, tabela ter entre 60 e 110 linhas, linhas-limite arbitrárias) faz sentido essa segmentação. Só sugiro que, quando possível, procure explicar tudo no início p/ evitar retrabalho, ok?

24 minutos atrás, Igor Moraes Mariano disse:

Existe uma forma de não contabiliza-las? ou acha melhor eu ajustar o tamanho da série de dados nestas células de acordo com o individuo?

2 horas atrás, Igor Moraes Mariano disse:

Existe uma maneira de a fórmula analisar a primeira célula da Coluna de hora e a partir disso definir qual será a primeira hora da média?

Sim, mas deixemos pra amanhã essas duas partes, ok?

Igor Moraes Mariano · 26 de setembro de 2018

Ok muitissimo obrigado @Edson Luiz Branco e @ppeterk .

Sou educador físico e não tenho tanta experiencia com essa parte um pouco mais complexa do Excel. Estão me ajudando muito mesmo a manter a confiabilidade dos meus dados de mestrado além de facilitar muito o trabalho.

Citação

Existe uma maneira de a fórmula analisar a primeira célula da Coluna de hora e a partir disso definir qual será a primeira hora da média?

Na verdade não preciso mais dessa parte. A forma como organizei as equações já cumprem com o que eu preciso.

Citação

Existe uma forma de não contabiliza-las? ou acha melhor eu ajustar o tamanho da série de dados nestas células de acordo com o individuo?

Esta parte sim será muito bem vinda.

Além disso fiz uma nova versão da tabela com todas as variáveis que usarei em uma versão modelo e um exemplo preenchido que seguem em anexo no arquivo "Exemplo V4". Marquei colorido e fiz uma explicação (Coluna Y) da mesma cor nas modificações que preciso fazer. Se alguém souber como ajudar estou aberto a dúvidas e sugestões

Agradeço desde ja.

Exemplo V4.xlsx

Igor Moraes Mariano · 27 de setembro de 2018

Ola gostaria de saber se alguém pode me ajudar na tabela abaixo, eu fiz uma versão modelo e uma preenchida que seguem em anexo no "Modelo V1" (versão Exemplo V1.xlsx e Exemplo V1.zip). Nas colunas Y a AD eu coloquei as coisas que não consegui resolver.

Entre as colunas A e R não precisam se preocupar, mas a situação é a seguinte: esta tabela é referente a exames de Pressão arterial de 24 horas que podem ter quantidades diferentes de medidas e em que os indivíduos podem dormir e acordar em horários diferentes. Nas células T1 e V1 eu preencho com os horários em que a pessoa dormiu e acordou respectivamente, e acho que estas informações podem ajudar a resolver meus problemas que são (OBS. coloquei bem detalhado pra não gerar duvidas):

Em T5 eu preciso fazer a média dos valores da coluna D correspondentes ao período acordado da pessoa. Para tanto, podemos comparar os horários da coluna A (Todas as medidas do aparelho) que são correspondentes a coluna D com os períodos antes de Dormir (T1) e depois de Acordar (V1). O mesmo vale para a Coluna E em relação a U, F em relação a V e G em relação a W.

Em T6 a ideia é similar ao anterior porém ao invés de média seria o desvio padrão

Em T7 a ideia é a mesma que no item 1 porém em relação ao período de sono

Em T8 a ideia é similar ao anterior porém ao invés de média seria o desvio padrão

Em U16 eu preciso do percentual de valores da Coluna D que estão acima de 135 no período acordado.

Em U17 eu preciso do percentual de valores da Coluna D que estão acima de 120 no período acordado.

Em W16 eu preciso do percentual de valores da Coluna E que estão acima de 85 no período sono.

Em W17 eu preciso do percentual de valores da Coluna E que estão acima de 70 no período sono.

Em U20 eu precisava da média das 2 primeira células após acordar (Caso haja apenas 1 hora após acordar, considerar apenas ela) da coluna N, MENOS o menor valor no intervalo de sono da coluna N. Ou seja: Média ao acordar em N - Menor valor do sono em N.

Em W20 eu precisava da média das 2 primeira células após acordar (Caso haja apenas 1 hora após acordar, considerar apenas ela) da coluna O, MENOS o menor valor no intervalo de sono da coluna O. Ou seja: Média ao acordar em O - Menor valor do sono em O.

Em T21 eu precisava fazer a conta "=1-a" sendo "a" a constante da formula "y = ax + b" que representa a curva de tendencia linear mostrada no gráfico de dispersão em S22. Para entender melhor dar uma olhada na Aba Preenchido. Este é um gráfico de dispersão de todos os pontos das colunas D e E.

Minha grande dificuldade é fazer isso de forma que estas fórmulas se ajustem de acordo com os horários que a pessoa Dormiu (T1) e Acordou (V1), já que esses valores variam de acordo com o indivíduo. então na minha cabeça o período Acordado é <T1 e >V1, e o período Dormindo é >T1 e <V1, faz sentido?

A solução de qualquer uma destas dificuldade já será de grande ajuda

Agradeço desde já!

Edson Luiz Branco · 27 de setembro de 2018

Trabalhoso, colega @Igor Moraes Mariano , mas vamos lá:

Defini intervalos nomeados que são dinâmicos, ou seja, se sua tabela crescer ou diminuir ele acompanhará (até a 120ª linha). Na verdade há um que é o principal ("tblDados") e os demais derivam dele, ficando mais fácil a manutenção. Havendo dúvidas quanto ao significado de cada um, questione.
Em alguns locais seus valores irão diferir dos meus. Pelo seu exemplo, como o sujeito dormiu das 23:30 às 07:30, então por esse critério os valores da linha 49 (23:39) já teriam que pertencer ao período de Sono, não ao de Vigilia, penso eu. Portanto esses valores compõe as métricas do período em Repouso, não em Vigília.
Há outros outros locais em que há diferenças de valores, como nos cálculos de Desvio Padrão em Vigília. Ali não entendi o porquê de você ter considerado intervalos como =DESVPAD(D3:D498;D66:D68) em todas as colunas. Acredito que tenha sido um erro de digitação em que o algarismo 9 entrou de gaiato ali no meio do 4 e do 8 hehehe.
Não compreendi o que você quer na área azul (Morning Sourge)
A parte do cálculo do coeficiente de regressão linear para a "Rigidez Arterial" AASI (1 - a) foi feito também através de fórmula e está OK.
As fórmulas são na sua grande maioria matriciais, portanto se for editá-las, lembre-se de finalizar com CTRL+SHIFT+ENTER.
Sucesso pra você em seu trabalho de mestrado! Vai firme!

Exemplo V4.xlsx

Igor Moraes Mariano · 27 de setembro de 2018

@Edson Luiz Branco Muito obrigado novamente, sozinho aqui eu estava ajustando tudo na mão mesmo a cada exame kkkkkk, mas ai abre muita margem pra erro humano.

3 horas atrás, Edson Luiz Branco disse:

Em alguns locais seus valores irão diferir dos meus. Pelo seu exemplo, como o sujeito dormiu das 23:30 às 07:30, então por esse critério os valores da linha 49 (23:39) já teriam que pertencer ao período de Sono, não ao de Vigilia, penso eu. Portanto esses valores compõe as métricas do período em Repouso, não em Vigília.

Isso foi erro meu mesmo, quando fui organizar a tabela pra te mandar o exemplo devo ter puxado errado

3 horas atrás, Edson Luiz Branco disse:

Acredito que tenha sido um erro de digitação em que o algarismo 9 entrou de gaiato ali no meio do 4 e do 8 hehehe.

Exatamente isso!

3 horas atrás, Edson Luiz Branco disse:

Não compreendi o que você quer na área azul (Morning Sourge)

Nesta parte é o seguinte:

Em U20 eu precisava da média das 2 primeira células após acordar (Caso haja apenas 1 hora após acordar, considerar apenas ela) da coluna N, MENOS o menor valor no intervalo de sono da coluna N. Ou seja: Média ao acordar em N - Menor valor do sono em N.
Em W20 eu precisava da média das 2 primeira células após acordar (Caso haja apenas 1 hora após acordar, considerar apenas ela) da coluna O, MENOS o menor valor no intervalo de sono da coluna O. Ou seja: Média ao acordar em O - Menor valor do sono em O.

3 horas atrás, Edson Luiz Branco disse:

As fórmulas são na sua grande maioria matriciais, portanto se for editá-las, lembre-se de finalizar com CTRL+SHIFT+ENTER.

Sucesso pra você em seu trabalho de mestrado! Vai firme!

beleza! e você vai estar nos agradecimentos do trabalho com certeza kkkkkk