Darren Bartrup-Cook's questions

Tenho estes dados de exemplo:

• 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.

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.

Tenho essa tabela de dados contendo a quantidade de devoluções por dia (Ret Volume) e a quantidade de devoluções que foram processadas por dia (Volume Cleared) .
O que procuro é a data exibida à direita, exibindo em que dias as devoluções foram processadas.

Por exemplo, no dia 1 de agosto recebemos 472 devoluções, das quais 462 foram processadas, deixando 10 ainda em espera.
No dia seguinte recebemos 380 devoluções e processamos 370 delas. As 10 primeiras processadas teriam sido do dia anterior, portanto todas as devoluções do 1º foram processadas até o 2º.

Minha primeira tentativa de fórmula está abaixo (a transposição só está lá para que eu possa verificar os valores retornados para cada dia sem um erro #SPILL): =TRANSPOSE(LAMBDA(ReturnedVolume,ClearedVolume,BYROW(ClearedVolume,LAMBDA(ClearedToday,SUM(ReturnedVolume)-SUM(ClearedVolume)>ClearedToday)))($F$3:$F3,$H$3:$H3))
Isso sempre retorna FALSE, acho que porque para 2 de agosto está calculando 852-832= 20>370 = FALSE .

Começo a pensar que não consigo ver a floresta por causa das árvores agora.

Estou tentando mesclar dois intervalos de derramamento de tamanhos iguais para que a primeira coluna do intervalo de derramamento A fique ao lado da primeira coluna do intervalo de derramamento B.

Estou perto de uma resposta, mas ao mesmo tempo tenho a sensação de que estou fazendo isso de maneira completamente errada.

Como exemplo:
Em A1digite a fórmula =SEQUENCE(3,2,1).
Em A5digite a fórmula=CHAR(SEQUENCE(3,2,65))

Isso fornecerá os dois intervalos abaixo. O resultado que procuro está nas linhas 9:11.

Meus pensamentos foram fazer isso com FILTERXML.

Isso une os dois intervalos e começa a construir a string xml (inserida em cell F1):

=LET(ColumnNum,COLUMN(A1#),
     FirstTable, A1#,
     SecondTable, A5#,
     PrefixA, "<a" & ColumnNum & ">",
     SuffixA, "</a" & ColumnNum & ">",
     PrefixB, "<b" & ColumnNum & ">",
     SuffixB, "</b" & ColumnNum & ">",
     xml, PrefixA & FirstTable & SuffixA &
          PrefixB & SecondTable & SuffixB,
     xml)  

Isso finaliza o xml e retorna o intervalo final derramado.

=LET(StartCol,COLUMN(F1),
     xml,F1#,
     LastCol,StartCol+COLUMNS(xml)-1,
     finalxml,"<y>" & TEXTJOIN("",FALSE,IF(COLUMN(xml)=StartCol,"<x>","") & xml & IF(COLUMN(xml)=LastCol,"</x>","")) & "</y>",
     CHOOSE(SEQUENCE(,COLUMNS(xml)*2,1),
            FILTERXML(finalxml,"//x/a1"),
            FILTERXML(finalxml,"//x/b1"),
            FILTERXML(finalxml,"//x/a2"),
            FILTERXML(finalxml,"//x/b2")))  

Isso funciona bem para dois intervalos de duas colunas, mas o número de colunas será alterado para que a CHOOSEinstrução precise ser atualizada manualmente a cada vez.

Existe uma maneira de fazer isso funcionar? Feliz em tomar LAMBDAsoluções, mas com medo de não poder usar as TEXTSPLITfunções do novo tipo.

Eu reformulei esta pergunta como se não tivesse resposta desde o primeiro ... talvez tenha sido um pouco prolixo.

Meus dados originais estão abaixo.
Nota - estes são apenas dados de teste, no meu arquivo é um intervalo derramado criado a partir de uma fórmula (consulte Fórmula para criar um produto cartesiano de dois conjuntos de dados em um intervalo derramado para essa fórmula)

Eu adiciono tags XML aos dados usando esta fórmula (nota: a !null!parte é onde está meu problema):
Data : ="<a>" & IF($B$4:$B$9=OFFSET($B$4:$B$9,-1,0),"!null!",$B$4:$B$9) & "</a>"
Texto : ="<b>" & $D$4:$D$9 & "</b>"

E, FILTERXMLem seguida, transformar os resultados em um intervalo de derramamento de duas colunas.

=LET(xml,"<y><x>" & TEXTJOIN("",FALSE,D3#,E3#) & "</x></y>",
     x,"//x/a",
     y,"//x/b",
     CHOOSE({1,2},FILTERXML(xml,x),FILTERXML(xml,y)))  

Como altero os !null!marcadores de lugar para células vazias reais?
"", "�"e "<a></a>"qualquer outra coisa que eu tentei retorna #VALUE!erros.

Responda:

A resposta dada por @HasanNahiyanNobel me deu a ideia de usar SUBSTITUTE. Por si só, faz com que as datas apareçam como seu valor numérico e a formatação das células não afeta isso. Usando TEXTa formatação em torno do FILTERXMLque fornece as datas, esse problema foi resolvido.

Minha fórmula final é:

=LET(xml,"<y><x>" & TEXTJOIN("",FALSE,Z4#,AA4#) & "</x></y>",
           x,"//x/a",
           y,"//x/b",
           CHOOSE({1,2},TEXT(SUBSTITUTE(FILTERXML(xml,x),"!null!",""),"dd-mmm"),FILTERXML(xml,y)))

Estou atrás de uma fórmula que criará um produto cartesiano a partir de dois conjuntos de dados em que qualquer um dos conjuntos de dados possa crescer ou diminuir.

A resposta dada por @ScottCraner neste post quase me deu a resposta, exceto que não está derramando como esperado.
Excel - Cada combinação da coluna a e coluna b na coluna d e e

Não estou procurando uma solução PowerQuery ou VBA - PQ envolve confiar na equipe para pressionar Refresh, VBA envolve confiar na equipe para notar a barra amarela que diz "Ativar macros" e entender o que isso significa.

Dados de teste:

Na imagem acima estou tentando obter os valores nas colunas M:Nusando os dados nas colunas A:F.
No momento estou recebendo os valores em J:K.

• Minha versão reduzida dos dados brutos está na coluna Dem uma tabela imaginativamente chamada Table1 .
• F1contém a fórmula =SEQUENCE(EndDate-StartDate+1,,StartDate)para me dar uma lista de todas as datas entre o início e o fim.
• G1contém a fórmula=UNIQUE(Table1[Process])
• I1contém a fórmula =SEQUENCE(COUNTA($F$1#)*COUNTA(G1#))que será derramada para me dar o número de linhas na minha tabela final (ou seja, quanto eu preciso derramar).
• J1contém a fórmula =IF(I1#<>"",INDEX($F$1#,INT(ROW()-1)/COUNTA($G$1#)+1)).
  A Indexparte por conta própria será arrastada para baixo e me fornecerá os valores em column M, ou eu poderia selecionar o intervalo e usar uma fórmula de matriz para também me fornecer os valores.
  Se eu avaliar a fórmula na célula J4, ela deve fazer referência à célula F2.... e faz, mas retorna o valor da célula F1.
  

Tenho a sensação de que estou perdendo algo fundamental sobre ranges derramados aqui. Alguma ideia?

Eu tenho uma tabela de dados que se parece com abaixo:

Report Date |   ID      |   Percent
11/06/2021  |   12345   |   0.9
18/06/2021  |   12345   |   1
25/06/2021  |   12345   |   0.85
02/07/2021  |   12345   |   0.5
11/06/2021  |   54321   |   0.77
18/06/2021  |   54321   |   1
25/06/2021  |   54321   |   1
02/07/2021  |   54321   |   0.25

Cada ID aparece para cada data junto com um valor de porcentagem.
Às vezes, há quatro semanas na tabela, às vezes cinco, dependendo do período de pagamento (um mês pode ter quatro ou cinco semanas).

Eu tenho uma consulta que agrupa os dados por ID e retorna um valor percentual médio nas quatro semanas.

Agora, espero que sem ter que entrar em detalhes completos, quero mudar essas datas para Semana 1, Semana 2, Semana 3, Semana 4 e ocasionalmente Semana 5 (meu chefe quer ver as semanas individuais ao lado do valor médio).
Se eu dinamizar a tabela para que cada semana se torne uma coluna, preciso saber o nome da coluna antes de expandi-la:

let
    Source = Table.NestedJoin(#"My Query", {"ID"}, #"Pivotted Table", {"ID"}, "Pivotted Table", JoinKind.LeftOuter),
    #"Reordered Columns" = Table.ReorderColumns(Source,{"ID", "Pivotted Table", "Percent"}),
    #"Expanded Pivotted Table" = Table.ExpandTableColumn(#"Reordered Columns", "Pivotted Table", {"11/06/2021", "18/06/2021", "25/06/2021", "02/07/2021"}, {"11/06/2021", "18/06/2021", "25/06/2021", "02/07/2021"})
in
    #"Expanded Pivotted Table"  

Encontrei uma função que me permite renomear colunas com base em sua posição na tabela para que eu possa padronizar os nomes das colunas para Semana 1, Semana 2, Semana 3 etc. Isso ainda gera um erro, pois há uma Semana 5 ocasional.

//Function Name - Rename Columns
let
    RenameColumns = (InputTable as table, ColumnNumbers as list, NewColumnNames as list) =>
let
    OldColumnNames = Table.ColumnNames(InputTable),
    Indexed = List.Zip({OldColumnNames, {0..-1+List.Count(OldColumnNames)}}),
    Filtered = List.Select(Indexed, each List.Contains(ColumnNumbers,_{1})),
    IndexRemoved = List.Transform(Filtered, each _{0}),
    RenameList = List.Zip({IndexRemoved,NewColumnNames}),
    RenamedColumns = Table.RenameColumns(InputTable, RenameList)
in
    RenamedColumns
in
    RenameColumns  

Portanto, isso gera um erro se não houver sexta coluna (a primeira coluna é a coluna 0).

//Query Name - Pivotted Table
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Report Date", type date}, {"ID", type text}, {"Percent", type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Report Date", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Report Date", type text}}, "en-GB")[#"Report Date"]), "Report Date", "Percent"),
    RenameColumns = #"Rename Columns"(#"Pivoted Column",{1,2,3,4,5}, {"Week 1", "Week 2", "Week 3", "Week 4", "Week 5"})
in
     RenameColumns  

Pergunta:

Como renomear ou expandir um número variável de colunas?

Eu tenho um conjunto de dados como abaixo. Observe que nem todas as datas são representadas.

Estou procurando uma fórmula do Excel 365 que, quando passada uma data de amostra, retornará uma data de dentro da tabela anterior ou igual à data de amostra e o valor correspondente é o primeiro valor maior que 0.

• Portanto, de 1 a 8 de fevereiro, a fórmula retornará 01-fev-21, pois é a primeira data na tabela.
• Para o dia 10 ao dia 16, ele retornará o dia 10, pois é a primeira data em que o valor aumentou acima de 0.
• Do 17º ao final da tabela retornará o 17º.

Uma das minhas tentativas:
=SUMPRODUCT(--(Table1[Date]<=D2),--(Table1[Value]>0),Table1[Date])- para 2 de fevereiro as duas primeiras datas são verdadeiras, assim como os dois primeiros números, então soma as datas e retorna 08-mar-42.
D2na fórmula acima contém uma data que está dentro dos limites de 01 a 25 de fevereiro.

Eu tentei trabalhar com o =MIN(IF((Table1[Date]<=D2)*(Table1[Value]>0),Table1[Date],"-"))que retorna 1º de fevereiro .

Qualquer ajuda seria muito apreciada.