Início / coding / Perguntas / 79303894
Accepted
Michi
Asked: 2024-12-24 02:10:12 +0800 CST

Crie uma lista com base em vários critérios de filtro sem limitação de linha na fórmula COUNTIF

  • 772
0 UM B C E E F G E EU
1 Lista de dados Filtro_01 Filtro_02 Filtro_03
2 data marca tipo produção valor. 2023-12-31 Br_B t1
3 2022-05-10 Sutiã t1 P1 15 Br_C t2
4 2023-01-15 Sutiã t1 P1 18 t3
5 2022-08-12 Br_B t2 P2 30
6 2022-09-15 Br_B t3 P3 50 Resultado
7 2023-06-15 Br_B t1 P3 20 produção
8 2023-06-18 Br_B t4 P1 80 P2
9 2024-05-17 Br_B t4 P4 25 P3
10 2023-08-19 Br_C t3 P5 85 P5
11 2022-09-30 Br_C t5 P3 70
12 2024-04-25 Br_C t5 P3 32
13 2025-08-29 Br_D t3 P1 45
14 2022-03-25 Br_E t1 P2 60
15 2022-03-26 Br_E t3 P2 85
16 2024-05-20 Br_E t5 P3 90
17 2024-06-03 Br_E t2 P4 15
18 2024-07-30 Br_E t1 P4 26

Em Cell G8Eu implementei a fórmula abaixo. Ela filtra a lista em Column A:Ecom base nos critérios em Cell G2, em Range H2:H4e Range I2:I4:

=LET(
a,COUNTIF(H2:H4,B1:B20)+AND(H2:H4=""),
b,COUNTIF(I2:I4,C1:C20)+AND(I2:I4=""),
c,FILTER(A1:E20,a*b,""),
d,A2:E2,
e,VSTACK(d,c),
f,MATCH(A2,CHOOSEROWS(e,1),0),
g,FILTER(e,CHOOSECOLS(e,1)<=G2),
h,DROP(UNIQUE(g),1),
i,VSTACK(d,h),
j,XLOOKUP(D2,CHOOSEROWS(i,1),i,NA(),0),
k,DROP(UNIQUE(j),1),
k)

Tudo isso funciona bem.

Agora, estou me perguntando se é possível, de alguma forma, eliminar as limitações de linha na COUNTIFparte da fórmula para que fique assim:

=LET(
a,COUNTIF(H2:H4,B:B)+AND(H2:H4=""),
b,COUNTIF(I2:I4,C:C)+AND(I2:I4=""),
c,FILTER(A:E,a*b,""),
d,A2:E2,
e,VSTACK(d,c),
f,MATCH(A2,CHOOSEROWS(e,1),0),
g,FILTER(e,CHOOSECOLS(e,1)<=G2),
h,DROP(UNIQUE(g),1),
i,VSTACK(d,h),
j,XLOOKUP(D2,CHOOSEROWS(i,1),i,NA(),0),
k,DROP(UNIQUE(j),1),
k)

Entretanto, esta fórmula retorna #VALUE!.

Como preciso modificar a fórmula para que ela funcione?

Nota: Gosto que na fórmula seja muito fácil adicionar filtros adicionais adicionando COUNTIFvariáveis. Portanto, a solução também deve manter essa flexibilidade.

excel

2 respostas

  1. Best Answer

    Gosto de usar ISNUMBER(MATCH(,,0))porque ele aceita matrizes, diferentemente de CONT.SE.

    Usando colunas completas podemos usar a A.:.Dreferência.

    =LET(
    rng,A.:.D,
    f_1,G2,
    f_2,H2:H4,
    f_3,I2:I4,
    _a,CHOOSECOLS(rng,1),
    _b,CHOOSECOLS(rng,2),
    _c,CHOOSECOLS(rng,3),
    _d,CHOOSECOLS(rng,4),
    UNIQUE(FILTER(
        _d,
        (ISNUMBER(MATCH(_a,FILTER(_a,_a<=f_1),0)))*
        (ISNUMBER(MATCH(_b,f_2,0)))*
        (ISNUMBER(MATCH(_c,f_3,0))))))

    insira a descrição da imagem aqui

    Se ainda não tivermos as A.:.Dreferências, podemos simplesmente fazer outro filtro:

    =LET(
    rng,A:D,
    f_1,G2,
    f_2,H2:H4,
    f_3,I2:I4,
    flt,FILTER(rng,CHOOSECOLS(rng,1)<>""),
    _a,CHOOSECOLS(flt,1),
    _b,CHOOSECOLS(flt,2),
    _c,CHOOSECOLS(flt,3),
    _d,CHOOSECOLS(flt,4),
    UNIQUE(FILTER(
        _d,
        (ISNUMBER(MATCH(_a,FILTER(_a,_a<=f_1),0)))*
        (ISNUMBER(MATCH(_b,f_2,0)))*
        (ISNUMBER(MATCH(_c,f_3,0))))))
    • 1 
  2. =LET(c,CHOOSECOLS,
     a,FILTER(A:E,ISNUMBER(A:A)),
     x,LAMBDA(y,z,(1-ISNA(XMATCH(c(a,y),z))+AND(z=""))),
UNIQUE(FILTER(c(a,4),(c(a,1)<=G2)*x(2,H2:H3)*x(3,I2:I4))))

    Esta fórmula usa ETA. Onde eu declarei cser CHOOSECOLS, apara ser o filtro de A:E de linhas contendo datas (números) na coluna A, xsendo (1-ISNA(XMATCH(CHOOSECOLS(a,y ),z ))+AND(z=""))onde yé uma entrada numérica para obter a ycoluna th de ae zsendo o intervalo de valores do filtro. Então x(2,H2:H3)significa(1-ISNA(XMATCH(CHOOSECOLS(FILTER(A:E,IS NUMBER(A:A)),2),H2:H3))+AND(H2:JE=""))

    • 0

relate perguntas