Início / computer / Perguntas / 1880924
Accepted
Darren Bartrup-Cook
Asked: 2025-02-18 23:11:50 +0800 CST

Semanas numeradas sequencialmente entre as datas de início e término

  • 772

Tenho estes dados de exemplo: insira a descrição da imagem aqui

  • Os dados no intervalo B1:I1são desta fórmula: =SEQUENCE(,8,DATE(2025,1,25),7).

  • A fórmula completa provavelmente será de 28/12/2024 para 53 colunas - intervalos de 7 dias, dando-me a data de início de cada sábado em 2025.

  • Os valores na coluna A são digitados manualmente (variações do formato d/m/a ):

     2/2/25-16/2/25, 
 02/03/2025-16 Mar 25

 25/1/25-25/1/25,
 15/03/25-15/03/25

Pergunta:
O que estou tentando fazer é numerar sequencialmente cada semana coberta pelos intervalos de datas na coluna A, a menos que haja um intervalo de seis semanas, caso em que a numeração deve começar em 1 novamente.

Esta fórmula transformaria os valores na célula A2em datas reais: =DATEVALUE(TEXTSPLIT(CLEAN(A2),"-",","))

ou isso retornaria o início da semana no sábado e o fim da semana na sexta-feira:

=LET(AllDates,DATEVALUE(TEXTSPLIT(CLEAN(A2),"-",",")),
     StartDates,CHOOSECOLS(AllDates,1)+1-WEEKDAY(CHOOSECOLS(AllDates,1)+1,1),
     EndDates,CHOOSECOLS(AllDates,2)+1-WEEKDAY(CHOOSECOLS(AllDates,2)+1,1)+6,
     HSTACK(StartDates,EndDates))

A fórmula =BYCOL($B$1#,LAMBDA(ThisDate,IF(AND(ThisDate>=$A5,ThisDate<=$B5),1,"")))adicionaria 1 sob a semana correta para um conjunto de datas. Aqui A5estão B5as duas primeiras datas retornadas pela minha fórmula de data.

insira a descrição da imagem aqui

Eu simplesmente não consigo colocar tudo em uma fórmula organizada para cada linha, muito menos a numeração sequencial. Eu adoraria se eu pudesse apenas adicionar uma fórmula B2e arrastar para baixo.

Qualquer sugestão é muito bem-vinda.

microsoft-excel

2 respostas

  1. Best Answer

    Acredito que isso deve funcionar. Salve esta função LAMBDA no módulo Workbook no Advanced Formula Environment.

    NumberWeeks = LAMBDA(raw_text, header, 
    LET(
        max_gap, 6, 
        dates, DATEVALUE(TEXTSPLIT(CLEAN(raw_text), "-", ",")),
        week_starts, MAP(dates, LAMBDA(d, XLOOKUP(d, header, header, , -1))),
        flag_weeks, DROP(
            REDUCE(
                0,
                header,
                LAMBDA(a, b,
                    LET(
                        position_of_latest_value, XMATCH(TRUE,a<>0,0,-1),
                        latest_value, IFERROR(INDEX(a,1,position_of_latest_value),TAKE(a,,-1)),
                        restart, IFERROR(AND(COLUMNS(a)>=max_gap,position_of_latest_value<(COLUMNS(a)+1-max_gap)),FALSE),
                        increment, MAX(
                            BYROW(week_starts, LAMBDA(r, IF(AND(b >= INDEX(r, 1, 1), b <= INDEX(r, 1, 2)), 1, 0)))
                        ),
                        HSTACK(a,IF(increment=1,IF(restart,1,latest_value + increment),0))
                    )
                )
            ),
            ,
            1
        ),
        IF(flag_weeks=0,"",flag_weeks)
    )
)

    Então use assim:

    insira a descrição da imagem aqui

    Note que no meu local, as datas são analisadas de acordo com mm/dd/aaaa. Então, alterei o texto bruto na coluna A para acomodar isso, a fim de evitar brincadeiras desnecessárias com sua fórmula DATEVALUE.

    A função definida acima deve funcionar independentemente do local.

    • 1

  2. Outra maneira possível:

    =LET(d,DATEVALUE(TEXTSPLIT(CLEAN(A2),"-",",")),
     s,TAKE(d,,1),
     e,DROP(d,,1),
     x,BYCOL((s-MOD(s,7)<=B$1#)*(e-MOD(e,7)>=B$1#),SUM),
IF(x,SCAN(,x,SUM),""))

    Isso primeiro pega sua versão de dividir suas datas de A2e divide as datas de início se término eem matrizes. Em seguida, BYROW verifica se o sábado de é sigual ou menor que a data de início da sequência da semana B1#e o mesmo, mas maior que na ematriz e soma o resultado (sendo 1(TRUE) ou 0(FALSE)).

    Em seguida, usamos SCAN para somar cumulativamente esses resultados byrow. E mostramos apenas um valor do resultado byrow que é maior que 0.

    Se houver valores sobrepostos dentro de uma mesma semana, useSCAN(,SIGN(x),SUM)

    Curiosidade: Eu usei =DATE(2025,1,SEQUENCE(,8,25,7))paraB1#

    Edição: Incluindo lacuna de 6 lógicas:

    =LET(d,DATEVALUE(TEXTSPLIT(CLEAN(A2),"-",",")),
     s,TAKE(d,,1),e,DROP(d,,1),x,BYCOL((s-MOD(s,7)<=B$1#)*(e-MOD(e,7)>=B$1#),SUM),
     y,SCAN(,x,LAMBDA(a,b,IF(b,0,a+1))),
IF(x,
   SCAN(0,IF(y<6,x+1,0),LAMBDA(a,b,IF(b,a+SIGN(b-1),0))),
   ""))
    • 1

relate perguntas