Início / computer / Perguntas / 1877234
Accepted
burnersk
Asked: 2025-02-07 17:53:17 +0800 CST

Com base em um determinado intervalo de datas de início/término, como encontrar datas únicas correspondentes apenas com a fórmula do Excel?

  • 772

Tenho um arquivo Excel com duas planilhas (lista de datas únicas de ausência e lista de semanas do calendário com data de início/término da semana do calendário e status de disponibilidade).

(estilo de escrita da data: ano-mês-dia)

planilha lista de datas únicas de ausência ( Absence):

planilha lista de datas únicas de ausência

planilha com lista de semanas do calendário com data de início/término da semana do calendário e status de disponibilidade Availability:

planilha lista de semanas do calendário com data de início/término da semana do calendário e status de disponibilidade

Meu objetivo é fazer com que a Statuscoluna na Statusplanilha mostre o número de dias distintos (0-7) dentro do intervalo de datas inclusivo ( coluna Mondaypara Sunday).

Por exemplo, dada uma única semana do calendário:

  • quando não houver nenhuma data na Absencelista da planilha que esteja entre (inclusive) a semana atual do calendário, a Statuscoluna deverá mostrar 0,
  • quando houver uma única data distinta na Absencelista da planilha que esteja entre (inclusive) a semana atual do calendário, a Statuscoluna deverá mostrar 1,
  • quando há uma única data distinta, mas a mesma data é listada duas vezes, na Absencelista da planilha que fica entre (inclusive) a semana atual do calendário, então a Statuscoluna deve mostrar 1,
  • quando houver duas datas distintas na Absencelista da planilha que estejam entre (inclusive) a semana atual do calendário, a Statuscoluna deverá mostrar 2,
  • quando houver sete datas distintas (a semana inteira) na Absencelista da planilha que estejam entre (inclusive) a semana atual do calendário, a Statuscoluna deverá mostrar 7.

Importante: O limite superior ( Sunday) pode ser alterado para sexta-feira , portanto, uma função que seja dinâmica em vez de adicionar um dia seis vezes e verificar novamente seria favorecida.

Distinção : O usuário pode inserir a mesma data várias vezes devido a erro do usuário. O formulário deve tomar medidas para que duas ou três vezes a menção de 2025-12-31 não seja contada como 2 , mas sim como 1 .

No exemplo dado, CW1 mostraria Status"1" (uma única data distinta dentro do CW), e todos os outros mostrariam "0" (no exemplo, não há CW para fevereiro/março/abril).

Mais tarde, os números de 0 a 7 serão um indicador para destacar toda a linha da semana do calendário, mas este não é o tópico desta pergunta.

Eu conheço a função LOOKUP/ VLOOKUP, mas meu caso é a "lógica LOOKUP reversa" e eu nem sei como começar a escrever a fórmula/função para ter meu problema resolvido. Talvez eu esteja pensando demais e haja uma solução fácil, mas não a vejo agora.

microsoft-excel

2 respostas

  1. Best Answer

    Aqui está uma maneira de fazer isso:

    COUNT(FILTER(UNIQUE(Absence!$D$2:$D$365),(UNIQUE(Absence!$D$2:$D$365)>=C2)*(UNIQUE(Absence!$D$2:$D$365)<=D2),""))

    Amostra:

    Resultado

    Ou um pouco mais fácil de manter:

    =LET(_abs,UNIQUE(Absence!$D$2:$D$365),
_FILTER,FILTER(_abs,(_abs>=C2)*(_abs<=D2),""),
COUNT(_FILTER))

    Ambos precisavam ser arrastados para baixo.

    Editar: alterou ligeiramente a fórmula para incluir a data de início e término: <para<=

    Edição 2: Datas duplicadas agora são contadas apenas uma vez;

    • 3

  2. Se você usar a numeração padrão ISO de semanas, também poderá simplificar um pouco:
    Em S2: =SUM(--(ISOWEEKNUM(UNIQUE(Absence))=P2)) E copie para baixo.
    "Ausência" significa um intervalo de datas de ausência. Ausência

    Se você quiser incluir apenas dias selecionados durante a semana, você pode, por exemplo, fazer assim (aqui dias úteis de segunda a sexta, fim de semana no sábado e domingo), mas não será mais curto neste caso (ou apenas um pouco):
    =LET(ua,UNIQUE(Absence),SUM((ISOWEEKNUM(ua)=P2)*NETWORKDAYS.INTL(ua,ua,"0000011")))

    • 2

relate perguntas