Excel: encontre o número de registros correspondentes a vários critérios E usando o tempo desde que o primeiro registro foi criado

Question

Estou procurando ajuda com uma fórmula. Na minha planilha de "dados", tenho dados no formato abaixo:

   A         B            C            D            E          F        G           H           I          J
1 UID   RecordType      HCode     AdmittedDate   Forename   Surname    ReviewDate     Sex        STDate      RDate
2 87962 STAsses         STIV1     01/07/2012     Mark       Jones                     Male   09/12/2012 
3 89658 Transfer        GLSI2     01/01/2012     Alison     Aitken                    Female                 08/07/2013 
4 87962 Discharge       STIV1                    Mark       Jones      20/07/2012     Male               08/07/2013
5 89654 STAsses         STGE1     01/01/2012     Andrew     Macbeth                   Male   08/07/2012  
6 89867 Transfer        KIND1     01/01/2012     George     Deas                      Male               08/07/2013 
7 87962 Transfer        STIV1                    Mark       Jones      14/07/2012     Male               04/03/2013 
8 89654 Transfer        STGE1     01/01/2012     Andrew     Macbeth                   Male               12/08/2012

Na minha planilha "relatório", tenho a seguinte tabela configurada:

      B             C             D            E            F
4 HospCode     RecordType     0-2 Weeks    2-4 Weeks    4-6 Weeks
5 STIV1        Transfers
6              Discharges
7              
8

Na minha planilha 'relatório', estou tentando contar o número do número de 'transferências' e 'descargas' (conforme especificado na coluna b da planilha de 'dados') que ocorrem dentro dos intervalos de tempo nas colunas D , E, F, etc.

Em resumo, o que estou procurando na célula D5 é contar o número de 'transferências' (conforme especificado na coluna b da planilha 'data') para o hospcode 'STIV1', ocorrendo dentro de 0-2 semanas do registro 'STAsses' correspondente (que também é especificado na coluna b). D6 seria o mesmo, apenas o número de registros de 'descarga' ocorrendo dentro do prazo especificado.

Eu não sei por onde começar com isso, ou se é possível fazer isso. Cada pessoa terá pelo menos um registro de STAsses, mas nem sempre um registro de 'Transfer' ou 'Discharge'. O 'UID' é o mesmo para cada pessoa, então isso pode ser usado para combinar entradas 'STAsses' com entradas 'Transfer' e 'Discharge'.

Todos os ponteiros seriam muito apreciados.

    

Answer 1

Existem muitas maneiras de fazer isso, mas nenhuma delas será simples. Uma maneira seria fazer uma coluna de data combinada para somar (stdate, rdate). Parece que apenas uma dessas datas é preenchida de uma só vez, então a soma seria apenas combiná-las corretamente em uma única coluna.

Emseguida,vocêpodecolocarumatabeladinâmicanapartesuperiorcolocandoUIDnaslinhas,RecordTypenascolunaseanovacolunaCombinedDatenosvalores(comoSUM).ComoCombinedDateédiferentedacombinaçãoUID/RecordType,nenhumadatarealserásomadaaqui,entãovocêacabarácomumaboatabeladeUIDsDistintosesuasrespectivasdatasSTAsses,TransfereDischargenamesmalinha./p>

Com essa informação, você pode subtrair sua data de STAsses da sua data de Transferência e Desprezo e dividir por 7 para obter as Semanas que se passaram entre os dois tempos. Você também pode executar um vlookup para trazer o HCode.

No final deste novo conjunto de dados, você pode criar novas colunas para capturar 0-2 descarga, 0-2 transferência, 2-4 descarga, 2-4 transferência, etc ... Você pode usar uma declaração if para preencha-os como If(dischargeweeks<2, 1, 0) , If(transferweeks<2, 1, 0) , etc. (note que os dados nas colunas do tipo 0-2, 2-4 são compostos, uma vez que os dados da sua amostra cuspiam números no intervalo de centenas de semanas).

Agoragireestesnovosdados(eusei...pivotspivotspivots)...Destavezstickhcodenaslinhas,eseu0-2Discharge,0-2transferência,etcnosvalores.EleagoralhedaráumacontagemdecadaumdelesparacadaHCodedistinto...queéREALMENTEpróximodoquevocêquer.

Por fim, copiei e colei (e você pode classificá-lo depois) para obtê-lo no formato correto ... Quero dizer, neste ponto, o que é um pequeno copiar e colar, certo?

Tudo o que foi dito, você poderia trabalhar um pouco de VBA para apenas percorrer a lista, calcular tudo e cuspir em uma nova planilha. Eu acho que só depende se isso é uma coisa de uma vez em um grande pedaço de dados, ou se você vai fazer isso todos os dias ...

Por último, como não consigo ver todas as coisas estranhas que podem estar nos seus dados, isso é apenas um conjunto de sugestões. Pode haver bits aqui que simplesmente não funcionarão ou bits que precisem ser alterados ligeiramente, mas o jist é o mesmo ... Use pivots para resumir seus dados, fazer mais alguns cálculos e girar novamente ... não é bonita, mas faz o trabalho.