Michi's questions

Quero Range H3:I8transpor os valores de Range A1:F2.
Para a transposição, uso esta fórmula TRANSPOSE(A1:F2).

Entretanto, não sei como incluir o cálculo da diferença entre os números na Range B2:F2seguinte fórmula de transposição:

I4 = 100 - 0 = 100
I5 = 400-100 = 300
I6 = 600-400 = 200
I7 = 200-600 = -400
I8 = 900-200 = 700

Você tem alguma ideia?

Nota: Sempre haverá exatamente uma linha para transpor.

DB-Violino

CREATE TABLE vouchers (
    id SERIAL PRIMARY KEY,
    customer VARCHAR,
    collected_date DATE,
    balance INT
);

INSERT INTO vouchers
(customer, collected_date, balance
)
VALUES 
('customer_a', '2024-03-09', '1600'),
('customer_a', '2024-04-20', '2700'),
('customer_a', '2024-05-24', '3100'),
('customer_a', '2025-03-09', '1500'),
('customer_a', '2025-04-20', '400'),
('customer_a', '2025-05-24', '0'),
('customer_b', '2024-10-17', '200'),
('customer_b', '2025-02-15', '1000'),
('customer_b', '2025-10-17', '800'),
('customer_b', '2025-11-18', '950'),
('customer_b', '2026-02-15', '1150'),  
('customer_b', '2026-11-18', '0');

Resultado esperado:

No resultado esperado, quero exibir apenas o mais recente disponível balancepor customercom base em uma determinada data.
Neste exemplo 2025-03-31.

Até agora consegui desenvolver esta consulta:

select
    customer as customer,
    collected_date as collected_date,
    sum(balance) as balance,
    max(collected_date) over (partition by customer) AS max_date
from vouchers
where collected_date < '2025-03-31' 
group by 1, 2
order by 1, 2 desc;

Ele quase me leva ao resultado correto. No entanto, não consegui fazê-lo funcionar completamente.
O que preciso mudar para obter o resultado esperado?

Fiddle de DB

CREATE TABLE vouchers (
    id SERIAL PRIMARY KEY,
    collected_date DATE,
    collected_volume INT
);

INSERT INTO vouchers
(collected_date, collected_volume
)
VALUES 
('2024-03-09', '900'),
('2024-04-20', '300'),
('2024-04-20', '800'),
('2024-05-24', '400'),
('2025-01-17', '200'),
('2025-02-15', '800'),
('2025-02-15', '150');

Resultado Esperado

Para ir dos dados brutos aos resultados esperados, são necessárias estas etapas:

Etapa 1: Calcule um expire_dateque é sempre 12 meses após a collected_date
Etapa 2: Coloque o expire_datena linha abaixo do collected_date. Não em uma coluna separada!
Etapa 3: Coloque o total collected_volumepor collected_datecomo um número negativo na linha com o expire_date.

Até agora consegui desenvolver esta consulta:

select
collected_date as collected_date,
(collected_date + interval '12 months')::date as expire_date,
sum(collected_volume) as collected_volume
from vouchers
group by 1,2
order by 1,2;

No entanto, não tenho ideia do que preciso mudar para exibir expire_datecada um abaixo collected_datee não em uma coluna separada.

Você tem alguma ideia de como resolver isso?

Em Range E5:G13Eu quero filtrar os dados de Column A:Ccom base no filtro em Cell F1e - como um primeiro passo - classificá-los por Column B.
Portanto, implementei esta fórmula em Cell E5:

=LET(
a;FILTER(A:C;(C:C=F1));
b;SORT(a;2;1);
b)

Tudo isso funciona bem.

Agora - como um segundo passo - eu quero classificar a lista filtrada em ordem crescente com base no date.
No entanto, essa classificação deve ser aplicada somente dentro dos valores de Column B.
No exemplo, isso significa que a classificação do airlinedeve permanecer como está e dentro do airlineo datedeve ser listado em ordem crescente. Os resultados esperados em Range E5:G13exibem como ele deve ficar.
Em SQL, esse tópico é resolvido com a PARTITION BYfunção.

=LET(
a;FILTER(A:C;(C:C=F1));
b;SORT(a;2;1);
c;SORT(b;1;1);
c)

Como preciso alterar a cparte na fórmula do Excel para que ela funcione?

Implementei Cell E8a fórmula abaixo para filtrar Columns A:Ccom base nos critérios em Cell E2.

=LET(
a,FILTER(A2:C18,A2:A18<=E2),
b,CHOOSECOLS(a,2),
c,CHOOSECOLS(a,3),
d,HSTACK(b,c),
d)

Tudo isso funciona bem.

No entanto, a questão é que

1. não prod.são listados como únicos e
   
2. o val.não se resume a cada um únicoprod.
   

O primeiro problema é fácil de resolver com UNIQUEa função, mas não tenho a mínima ideia de como resolver o segundo problema com a soma.
Sei que na versão mais recente do Excel há uma GROUPBYfunção que resolve esse problema.
No entanto, ainda não tenho essa função disponível no meu Excel. Estou usando o Office365 (versão 2410).

Você conhece alguma maneira alternativa de fazer isso funcionar?

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.

Na tabela acima tenho duas listas diferentes:

Lista A = Range A1:C12
Lista B = Range E1:G4 (Info: Os produtos desta lista serão sempre únicos)

No é Range E8:E10filtrado List Acom esta fórmula:

=UNIQUE(FILTER(B:B;A:A=E7))

Tudo isso funciona.

Agora, Range F8:G10quero adicionar informações adicionais aos produtos filtrados da Lista B.
Até agora, consegui fazer isso para a primeira linha com esta fórmula:

=DROP(FILTER(E1:G4;E1:E4=E8);;1)

Como preciso alterar a(s) fórmula(s) para que funcione(m) em todas as linhas?

Na tabela acima tenho duas listas diferentes:

Lista A Column A:C
Lista BColumn E:G

E Range I3:J6eu Range K3:L4filtro essas duas listas com base nos critérios Cell I1usando as seguintes fórmulas:

Cell I3 =CHOOSECOLS(FILTER(A1:C12,A1:A12=I1),2,3) 
Cell K3 =CHOOSECOLS(FILTER(E1:G12,E1:E12=I1),2,3)

Tudo isso funciona bem.

Agora meu alvo é a lista em Range I9:J11.
A lista exibe exclusivamente todos os produtos da lista filtrada A em Range I3:J6e subtrai os valores correspondentes por produto da lista filtrada B em Range K3:L4.

Eu sei que poderia fazer isso aplicando a UNIQUEfunção e a SUMIFfunção nos resultados existentes em Range I3:J6e Range K3:L4.

No entanto, eu preferiria ter uma solução na qual eu não precisasse de Range I3:J6e Range K3:L4.
Em vez disso, a lista em Range I9:J11deveria ser exibida sem os "helper ranges".

Você tem alguma ideia de como conseguir isso?

Observação:

1. List Bnunca conterá valores que não existam em List A. Nem para marca nem para produto.
   

2. List Btambém pode conter várias vezes o mesmo produto por marca. Nem sempre é único. Por exemplo, pode acontecer que Product 02apareça x vezes por Brand C.

Quero Range G3:G6transpor os valores com base nos critérios de linha inseridos em Cell G1.
(Observação: os produtos listados em Column Asão sempre exclusivos.)

Neste exemplo, o Row B2:E2for Product Aé transposto e exibido em Range G3:G6.

Até agora consegui desenvolver esta fórmula:

=LET(
a,TRANSPOSE(B2:E2),
IFERROR(INDEX(FILTER(a,a<>0),SEQUENCE(4,,1)),""))

Esta fórmula funciona.
No entanto, não tenho a mínima ideia de como tornar o Range B2:E2nesta fórmula flexível para que ele mude com base na entrada em Cell G1.

Você tem alguma ideia de como modificá-lo para que funcione?

Criei Range A7:B11uma matriz usando esta fórmula:

=HSTACK(A1:A5;C1:C5)

Agora, quero aplicar a FILTERfórmula a essa matriz e filtrar apenas os valores que estão =Project Blá, para que no final a lista fique assim:

0 |    A      |  B  |   C  |
--|-----------|-----|------|--
  |           |     |      |
7 | Project B | 200 |      |
8 | Project B | 600 |      | 
9 |           |     |      |

Eu tentei algo assim:

=FILTER(A7#;A7#="Project B")

Ele retorna#VALUE!

Presumo que o problema seja que, de alguma forma, preciso informar à Filterfórmula que o filtro deve ser aplicado à primeira coluna da matriz.

Você sabe como mudar a fórmula para que funcione?

Em Range A7:B11Eu quero classificar os valores Range A1:A5decrescente com base nos valores em Range C1:C5. Portanto, tentei usar esta fórmula:

=SORT(A1:C5;;-1)

Entretanto, quando faço isso o resultado fica assim:

Como preciso modificar a SORTfórmula para

1. obter a lista na ordem decrescente correta com base nos valores em Range C1:C5?
   
2. ignorar o vazio Column Bdentro do Range A1:C5e não exibir 0 valores para ele?

Em Cell B6:B9eu quero

1. classifique Column Aem ordem decrescente e
   
2. exibir sempre o número de linhas inseridas Cell B4a partir da linha inserida em Cell B2.

Até agora consegui desenvolver esta fórmula:

=CHOOSEROWS(SORT(A1:A10,,-1),SEQUENCE(C4,,C2))

Essa fórmula quase me dá o resultado.

No entanto, quando você chegar ao ponto em que não há mais de 4 linhas abaixo do ponto inicial - que é o caso do exemplo acima - a fórmula retornará um erro #VALUE!.

A ideia é que a fórmula neste caso exiba as 3 linhas restantes e apenas recorte a quarta.

Como preciso modificá-lo para que funcione?

Quero Column Bextrair o primeiro número que ocorre nas strings em Column A.
Quero Column Cextrair o último número que ocorre nas strings em Column A.

Tentei aplicar soluções para esta questão , mas elas não funcionaram no meu caso.

Qual fórmula eu preciso para obter os resultados esperados Column Be Column C?

Em Column Beu quero

1. classifique os valores em Column Aordem decrescente e
2. então exiba apenas os valores na classificação 5,6,7 da ordem decrescente.
   

Até agora consegui resolver a primeira etapa usando esta fórmula:

=TAKE(SORT(A1:A10;;-1);5)

No entanto, agora tenho ideia de como implementar a segunda etapa. Deveria ser algo assim

=TAKE(SORT(A1:A10;;-1);5-7)

Você sabe qual fórmula preciso mostrar apenas os valores na classificação 5,6,7 ?

NOTA: O 5,6,7 é apenas um exemplo para esta questão.
Seria ótimo ter uma fórmula na qual esse intervalo pudesse ser definido de forma flexível.

Quero Column Lexibir para cada produto a primeira margem por produto, que é a segunda célula não vazia em cada linha.

Até agora consegui desenvolver esta fórmula:

=XLOOKUP(TRUE,ISNUMBER(C2:J2),C2:J2)

Isso me dá corretamente as primeiras vendas, que são a primeira célula não vazia em cada linha.
Como preciso modificá-lo para exibir a segunda célula não vazia em cada linha?

Quero Range M6:M14obter os dados correspondentes com base nos valores em Range K6:14.
No entanto, quero ter a flexibilidade que

1. the lookup_arrayé usado com base na entrada Cell M3e
   
2. the return_arrayé usado com base na entrada em Cell M5. Por exemplo, se eu mudar de 2024-Mpara Product, os produtos correspondentes Column Adeverão ser listados.
   

Com referência à resposta nesta pergunta , estou procurando algo assim:

=LET(
     _A, K6:K14,
     _B, >> flexible based on input in Cell M3 <<,
     _RollingA, MAP(_A,LAMBDA(α,COUNTIF(α:K6,α))),
     _RollingB, MAP(_B,LAMBDA(α,COUNTIF(α:H3,α))),
     XLOOKUP(_A&"|"&_RollingA,_B&"|"&_RollingB,XLOOKUP(M5,A1:I1,A3:I17)))

Nota:
Os valores em lookup_arraynão são exclusivos. Neste exemplo 130, 400e 70. Portanto, caso a frequência dos valores em Range K6:14não corresponda à frequência dos valores em return_arrayentão, Range M6:M14eles deverão ser comparados aos valores em Range K6:14em ordem decrescente. (A fórmula de exemplo acima já trata dessa forma).

Qual fórmula eu preciso para obter o resultado no Range M6:M14trabalho com base em um processo flexível lookup_arraye return_array?

Como você pode ver na captura de tela, defini um parametertipo de dados with datee selecionei a data 2024-05-27.
Porém, quando consulto esse parâmetro no SQL estou obtendo um valor completamente diferente ( 1992-01-01).

Você sabe por que isso acontece e como resolver?

      |   A   |B |C|   D  |E|    F   |   G  |   H  |   I  |J|    K    |     L    |    M     |   
------|-------|--|-|------|-|--------|------|------|------|-|---------|----------|----------|--
    1 |       |  | |ratio | | tested | 2023 | 2024 | 2025 | | RowCrit | ColCrit1 | ColCrit2 |
    2 |Brand A|P1| |  7%  | |   yes  |  500 |   70 |   60 | |   2024  |  Brand A |    P1    |         
    3 |Brand A|P2| |  8%  | |   yes  |  100 |   47 |  300 | |         |  Brand D |    P4    |            
    4 |Brand A|P2| | 10%  | |   yes  |  800 |   21 |  200 | |         |          |          |          
    5 |Brand B|P1| |  5%  | |   yes  |   90 |   56 |  150 | |         |          |          |                     
    6 |Brand C|P4| |  3%  | |   no   |   45 |  700 |  790 | |         |          |          |                  
    7 |Brand C|P2| |  8%  | |   no   |  600 |  150 |   40 | |  Result |    191.5 |          |          
    8 |Brand D|P1| | 12%  | |   yes  |  900 |   90 |  980 | |         |          |          |                    
    9 |Brand D|P1| | 20%  | |   yes  |  125 |  854 |  726 | |         |          |          |          
    10|Brand D|P3| | 19%  | |   yes  |   70 |  860 |  614 | |         |          |          |          
    11|Brand D|P4| |  2%  | |   yes  |  842 |  250 |   85 | |         |          |          |          
    12|Brand E|P4| |  6%  | |   no   |  300 |  324 |  450 | |         |          |          |   
       

Quero calcular SUMPRODUCTmultiplicando os valores de Column Dpelos valores correspondentes em Column G:I.
A SUMPRODUCTfórmula também deve considerar os filtros para Rowin Cell K2e Columnin Range L2:L4e Range M2:M4.

No exemplo acima o resultado é:

L7 = 7%x70 + 12%x90 + 20%x854 + 2%x250 = 191.5

Com referência à resposta nesta pergunta , tentei aplicar esta solução:

=LET(
    a, IF(
        COUNTA($L$2:$L$3) = 0,
        N(ISNUMBER(ROW($A$2:$A$12))),
        COUNTIF($L$2:$L$3, $A$2:$A$12)
    ),
    b, IF(
        COUNTA($M$2:$M$3) = 0,
        N(ISNUMBER(ROW($B$2:$B$12))),
        COUNTIF($M$2:$M$3, $B$2:$B$12)
    ),
    c, CHOOSECOLS($A$2:$I$12, 3, XMATCH($K$2, $A$1:$I$1)),
    SUM(a * b * CHOOSECOLS(c, 1) * CHOOSECOLS(c, 2))
)

No entanto, ele retorna 0como resultado. Pelo que posso dizer, isso é causado pelo vazio Column C.
Presumo que de alguma forma nesta fórmula ela Column Ddeve ser declarada como coluna fixa a ser usada para o arquivo SUMPRODUCT.

Você tem alguma ideia de como alterar a fórmula para que funcione?

   |   A   | B|    C    |D|     E    |     F    |      
---|-------|--|---------|-|----------|----------|-
   |       |  | Revenue | | ColCrit1 | ColCrit2 |
2  |Brand A|P1|    500  | |  Brand A |    P1    |         
3  |Brand A|P2|    100  | |  Brand B |    P3    |            
4  |Brand A|P2|    800  | |  Brand D |          |
5  |Brand B|P1|     90  | |          |          |
6  |Brand C|P4|     45  | |          |          |
7  |Brand C|P2|    600  | |   Result |    500   |
8  |Brand D|P1|    900  | |          |     90   |
9  |Brand D|P1|    125  | |          |    900   |
10 |Brand D|P3|     70  | |          |    125   |
11 |Brand D|P3|    842  | |          |     70   |
12 |Brand E|P4|    300  | |          |    842   |

   

Quero filtrar a lista com base em critérios de várias colunas.
Os critérios da coluna podem ser inseridos pelo usuário em Range E2:E4e Range F2:F4.

Até agora, só consegui criar uma fórmula de filtro em entradas de célula única:

F7 = FILTER(C2:C12,IF(E2="",1,(A2:A12=E2))*IF(F2="",1,(B2:B12=F2)))

Você sabe se existe uma fórmula para aplicar os critérios como um intervalo?
Algo assim:

F7 = FILTER(C2:C12,IF(E2:E4="",1,(A2:A12=E2:E4))*IF(F2:F4="",1,(B2:B12=F2:F4)))

   |   A   | B|    C  |   D    |   E  |   F  |   G  |H|    I    |     J    |    K     |  L
---|-------|--|-------|--------|------|------|------|-|---------|----------|----------|-----
   |       |  | ratio | tested | 2023 | 2024 | 2025 | | RowCrit | ColCrit1 | ColCrit2 |
2  |Brand A|P1|   7%  |   yes  |  500 |   70 |   60 | |   2023  |  Brand A |    P1    |         
3  |Brand A|P2|   8%  |   yes  |  100 |   47 |  300 | |         |  Brand B |          |            
4  |Brand A|P2|  10%  |   yes  |  800 |   21 |  200 | |         |          |          |          
5  |Brand B|P1|   5%  |   yes  |   90 |   56 |  150 | |         |          |          |                     
6  |Brand C|P4|   3%  |   no   |   45 |  700 |  790 | |         |          |          |                  
7  |Brand C|P2|   8%  |   no   |  600 |  150 |   40 | |  Result |    39.5  |          |          
8  |Brand D|P1|  12%  |   yes  |  900 |   90 |  980 | |         |          |          |                    
9  |Brand D|P1|  20%  |   yes  |  125 |  854 |  726 | |         |          |          |          
10 |Brand D|P3|  19%  |   yes  |   70 |  860 |  614 | |         |          |          |          
11 |Brand D|P3|   2%  |   yes  |  842 |  250 |   85 | |         |          |          |          
12 |Brand E|P4|   6%  |   no   |  300 |  324 |  450 | |         |          |          |   

   

Quero Cell J7calcular SUMPRODUCTcom base em vários critérios de coluna e critérios de uma linha:

1. Os critérios para a linha que você pode encontrar emCell I2
   
2. Os critérios para as colunas você pode encontrar em Range J2:J3e Range K2:K3.
   

No exemplo acima o resultado é:

J7 = 500x7% + 90x5% = 39.5

Até agora consegui extrair SUMcom base nos critérios usando esta fórmula:

=SUM(IF(COUNTIF(I2;C1:G1)*COUNTIF(J2:J3;A2:A12)*COUNTIF(K2:K3;B2:B12);C2:G12))

Porém, não tenho ideia de como alterar essa fórmula para que ela calcule SUMPRODUCTe inclua todos os critérios tanto para coluna quanto para linha.