Início / coding / Perguntas / 79348889
Accepted
jahnLudvik
Asked: 2025-01-12 05:04:42 +0800 CST

Fórmula para filtrar dados com base em 3 valores booleanos "verdadeiros" anteriores

  • 772

Estou tentando usar o Excel para contar a soma das três entradas anteriores por cada célula da segunda linha, "indexadas" pelo BOOL na primeira linha.

Eu esperava usar offset em vez de um filtro, mas aparentemente é impossível usá-lo.

Agora estou pensando em usar SOMASE e diferenças de somas parciais, mas gostaria de encontrar uma maneira mais fácil.

A saída deve ser a 3ª linha.

Obrigado

0 1 1 1 0 1 0 0 1 0 0 1 1 1 1 0
17 4 0 44 6 0 87 45 36 8 0 1 39 39 41 30
0 0 0 0 48 48 44 44 44 80 80 80 37 76 79 119
excel

5 respostas

  1. =MAP(A1:P1,A2:P2,LAMBDA(x,y,
 LET(c,LAMBDA(e,TAKE(A1:INDEX(A:P,ROW(e),COLUMN(e)-1),-1)),
     d,TAKE(TOCOL(c(y)/c(x),2),-3),
IF(COLUMN(x)<3,0,(ROWS(d)=3)*SUM(d)))))

    Aqui A1:P1está mapeado como xe A2:P2como y.

    cé usado como uma função, que requer entrada ee pegará apenas a última linha de A1:[input range offset -1 column]significando que se a entrada for, yela retornaria o intervalo até a coluna da entrada. Por exemplo, se yfor em E2 e(y)retornariaTAKE(A1:D2,-1)

    Em seguida, dcombinamos TOCOL(c(y)/c(x),2)onde os valores de yaté a coluna anterior são divididos por valores de xaté a coluna anterior. Onde qualquer 0em xforçaria um erro #DIV/0. O TOCOLargumento 2ignora esses valores de erro.

    Existe a possibilidade de que dainda não consista em 3 registros, portanto BOOLEAN de ROWS(d)=3é multiplicado porSUM(d)

    Ou uma solução não-LAMBDA:

    =LET(a,A1:P1,
     s,SEQUENCE(,COLUMNS(a)),
     w,WRAPROWS(TOROW(VSTACK(s+1,A2:P2)/a,2,1),2),
     r,DROP(SEQUENCE(ROWS(w)),2),
IFNA(VLOOKUP(s,
             HSTACK(TAKE(w,-ROWS(r),1),
             MMULT(INDEX(w,r-{0,1,2},2),{1;1;1})),
             2),
     0))
    • 4
  2. Best Answer

    Não é o melhor, mas. Fórmula em B3arrastado para a direita:

    =LET(a,TAKE(FILTER($A$2:A2,$A$1:A1=1),1,-3),IF(COUNTA(a)>=3,SUM(a),0))

    Resultado:

    insira a descrição da imagem aqui

    • 3

  3. Aqui está uma fórmula de vazamento, mas muito mais longa

    =LET(bools,A1:P1,
    data,A2:P2,
    seq,SEQUENCE(1,COLUMNS(data)),
    MAP(seq,LAMBDA(i,IF(i=1,0,
      LET(c,SUM(A1:INDEX(bools,i-1)),
        IF( c<3,0,SUM(TAKE(FILTER(A2:INDEX(data,i-1),A1:INDEX(bools,i-1)),,-3))))))))
    0 1 1 1 0 1 0 0 1 0 0 1 1 1 1 0
    17 4 0 44 6 0 87 45 36 8 0 1 39 39 41 30
    0 0 0 0 48 48 44 44 44 80 80 80 37 76 79 119

    Eu poderia ter usado Take em vez de a1:index...

    =LET(bools,A1:P1,
    data,A2:P2,
    seq,SEQUENCE(1,COLUMNS(data)),
    MAP(seq,LAMBDA(i,IF(i=1,0,
      LET(c,SUM(TAKE(bools,,i-1)),
        IF( c<3,0,SUM(TAKE(FILTER(TAKE(data,,i-1),TAKE(bools,,i-1)),,-3))))))))
    • 2

  4. Você pode usar

    =HSTACK(0,MAP(A1:O1,A2:O2,LAMBDA(α,β,IFERROR(SUM(CHOOSECOLS(FILTER(A2:β,A1:α),-1,-2,-3)),0))))

    insira a descrição da imagem aqui

    • 2

  5. Outra abordagem é usar SCANuma LAMBDAfunção personalizada para armazenar uma contagem booleana em execução, mais os últimos 3 valores "sinalizados" em cada iteração e, em seguida, MAPchamar SUMos resultados:

    =LET(
    rng, DROP(A1:P2,,-1),
    fnλ, LAMBDA([n],[a],[b],[c],LAMBDA(i,CHOOSE(i,n,a,b,c))),
    arr, SCAN(fnλ(),SEQUENCE(,COLUMNS(rng)),LAMBDA(λ,j,IF(INDEX(rng,1,j),fnλ(λ(1)+1,λ(3),λ(4),INDEX(rng,2,j)),λ))),
    HSTACK(0,MAP(arr,LAMBDA(λ,IF(λ(1)>2,SUM(λ({2,3,4})),0))))
)

    Pode parecer muito, mas sem o fardo adicional de ter que fazer isso FILTERem cada iteração, ele permanecerá relativamente eficiente em um conjunto de dados maior (embora não tão rápido quanto MMULTa solução de @Pb).

    • 1

relate perguntas