Mark S.'s questions

Tenho uma tabela com formatação solta que tem Pessoas no topo, tarefas nas linhas e uma coluna para data de início e uma coluna para Data de término. O usuário inserirá as horas esperadas nas subtarefas fornecidas e elas serão acumuladas na própria tarefa. Consegui obter uma soma por linha dividida pelo número de meses sendo pesquisados, mas não consegui que o valor fosse distribuído corretamente pelos meses relevantes.

É assim que os dados são estruturados.

A tarefa é produzir uma tabela que descreva as tarefas e subtarefas e faça uma média linear das horas gastas em uma determinada tarefa nos meses relevantes. (Total / # meses -> os meses entre as datas de início e término).

Por exemplo, aqui você vê que estou filtrando as subtarefas do total de tarefas e distribuindo suas respectivas horas totais pelos meses em que a tarefa deve ocorrer.

Gostaria de me concentrar na tabela inferior por enquanto, já que a tabela superior é composta por isso, acho que posso ajustar o resto adequadamente.

Agora mesmo, eu pego um sumifs do total por tarefa/mês # para aquela tarefa/dia do cabeçalho apenas para que ele se espalhe pelas colunas. No entanto, isso está espalhando o mesmo valor por todos os meses para as tarefas, não apenas os meses relevantes. Então, eu tentei um If para verificar se a data está entre a data de início e término, mas então ele não se espalha. E se eu fizer isso sozinho, ele não valida corretamente a lógica.

Também tentei usar uma instrução LET para definir a data de início e a data de término, depois verificar o intervalo de vazamento do cabeçalho do mês para ver se esse valor está entre essas datas e, se estiver, multiplicar o valor por 1 ou 0 para tentar zerar os valores irrelevantes.

Isso é o que está acontecendo atualmente quando tento verificar as colunas em relação aos resultados do xlookup das linhas e aplicá-los às interseções.

Aqui está uma tabela de markdown para você entender o básico.

Minha fórmula de filtro para as tarefas:=FILTER(A5:A16,MOD(A5:A16,1)<>0)

Minha fórmula para os cabeçalhos de data:=DATE(YEAR(MIN(B5:B16)),SEQUENCE(1,DATEDIF(MIN(B5:B16),MAX(C5:C16),"M")+1,MONTH(MIN(B5:B16)),1),1)

Eu tenho uma matriz com várias colunas que são intervalos de derramamento. Preciso usar cada coluna para fazer um xlookup multicondicional (ou fórmula/resultado semelhante) que forneça uma faixa de resultados dos resultados. Usei sumifs para obter resultados, mas quando uma célula está vazia, gostaria que ela permanecesse vazia, onde os resultados dos sumifs dão 0% quando uma célula está vazia.

A aplicação real disso será colocada em uma fórmula LET e cada um dos derramamentos no xlookup será uma saída de escolha.

=XLOOKUP(1,($C$3#=$C$12:$C$15)*($D$3#=D12:D15),$E$12:$E$15,"",0)

=SUMIFS($E$12:$E$15,$C$12:$C$15,C3#,$D$12:$D$15,D3#)

Na imagem, as bordas indicam uma matriz de derramamento. Os resultados esperados são inseridos manualmente.

Tudo, meu objetivo é ter uma saída resumida de fórmulas de derramamento que gere o x superior das linhas, mas a última linha agrupando todas as outras linhas para que os totais sejam vinculados à fonte de dados. (A primeira imagem mostra geralmente como está funcionando, a segunda mostra como eu gostaria que fosse. Tabela Markdown incluída para teste.) Se houver uma maneira melhor de formular o título, me avise e eu ajustarei.

A tabela de origem é gerada por fórmulas auxiliares em uma tabela dinâmica. Eu faço isso para poder obter o total que devo vincular antes de começar a combinar campos, para os quais as tabelas dinâmicas não têm uma solução limpa (para agrupar os x campos principais, você precisa selecioná-los manualmente, mas preciso que isso seja dinâmico).

Na imagem, a primeira tabela (à esquerda) contém todas as fórmulas de derramamento. Os números de departamento na parte superior e os IDs na lateral são gerados, assim como os valores.

A segunda tabela tenta pegar os 5 primeiros e depois somar tudo depois dos 5 primeiros em "Outros"; no entanto, como você pode ver, isso está obtendo dados para o ID 6, em vez de tudo maior que 6. Tentei a fórmula de algumas maneiras, mas não consegui somar todas as colunas de uma vez enquanto fazia isso. Uma solução que exige que eu copie a fórmula para colunas adicionais ou remova-as quando houver menos, pois não será estático.

Restrições: A primeira tabela é gerada a partir de fórmulas de derramamento, o número de linhas e colunas não é estático, portanto a segunda tabela precisa levar isso em consideração. Por exemplo, usei Sequence()para os cabeçalhos e linhas, e uma fórmula aleatória para os valores conterem alguns dados.

A fórmula funciona para tudo, menos para a linha "Outros":

=MAKEARRAY( ROWS(I2#), COLUMNS(K1#), LAMBDA(r,c,SUM(C2#*--(A2#=INDEX(I2#,r))*--(C1#=INDEX(K1#,1,c)) )))

Tentei modificar a fórmula em K2 abaixo, mas resultou em #N/A:

=MAKEARRAY( ROWS(I2#), COLUMNS(K1#), LAMBDA(r,c,SUM(C2#*--IF(I2#>5,(A2#>=INDEX(I2#,r)),(A2#=INDEX(I2#,r)))*--(C1#=INDEX(K1#,1,c)) )))

Esta é a saída desejada:

(Marcação para teste)

Aqui está uma pergunta que me ajudou a criar a primeira fórmula

Eu tenho uma planilha onde o departamento está na parte superior (H43) e estou usando uma fórmula aninhada Classificar, Exclusivo e Filtrar para extrair 3 colunas de uma tabela nomeada. O filtro analisa esse departamento e fornece todos os IDs, nomes e cargos dos funcionários. A coluna adicional necessária também está nessa tabela, mas não em uma coluna adjacente, Status. Os números das colunas que preciso são 7,8,9,5 nessa ordem.

Gostaria de minimizar a manutenção de fórmulas de arrastamento, por isso espero uma de duas coisas: poder usar a fórmula extra para extrair a coluna adicional não adjacente ou ter uma pesquisa multicondicional (porque aqui estão os IDs duplicados) para trazer o status de emprego e preenchê-lo automaticamente com o derramamento.

Os cabeçalhos _Pop são, atualmente estou puxando os 3 últimos, mas quero status de funcionário. As últimas três colunas estão extraindo dados do primeiro conjunto de colunas, mas o status está bom como está, então eu gostaria de não movê-lo ou duplicá-lo desnecessariamente.

Fórmulas que tentei:

Derramamentos:

Esta fórmula extrai as últimas 3 colunas corretamente, mas não a coluna de status adicional

=SORT( UNIQUE( FILTER( _Pop[[EmployeeID]:[Title]], _Pop[department]='Annualized Hours (2)'!$H$43)),2,1)

Esta fórmula deu um comportamento estranho

=SORT( UNIQUE( FILTER( _Pop, _Pop[department]='Annualized Hours (2)'!$H$43),{7,8,9,5}),2,1)

Essa tentativa deu um #Valor! erro.

=SORT(UNIQUE(FILTER(INDEX(_Pop, , {7,8,9,5}), _Pop[department]='Annualized Hours (2)'!$H$43)),2,1) =SORT(UNIQUE(FILTER(INDEX(_Pop[#Data], , {7,8,9,5}), _Pop[department]='Annualized Hours (2)'!$H$43)),2,1)

Isso deu as colunas, mas não na ordem que preciso:

=SORT(UNIQUE(FILTER(FILTER(_Pop, _Pop[department]='Annualized Hours (2)'!$H$43), (COLUMN(_Pop)=COLUMN(INDEX(_Pop,,7)))+(COLUMN(_Pop)=COLUMN(INDEX(_Pop,,8)))+(COLUMN(_Pop)=COLUMN(INDEX(_Pop,,9)))+(COLUMN(_Pop)=COLUMN(INDEX(_Pop,,5)))),FALSE,FALSE),3,1,FALSE)

Criados helpers em A1:D1 caso não gostasse das constantes. sem sorte

=INDEX(_Pop,MATCH('Annualized Hours (2)'!$H$43,_Pop[department],0),MATCH(COLUMN(A1:D1),COLUMN(INDEX(_Pop,,{7,8,9,5})),0))

Pesquisas:

=XLOOKUP(INDEX(I44#,,1), MATCH(TRUE,($H$43=_Pop[department])*(INDEX(I44#,,1)=_Pop[EmployeeID]),0),_Pop[Employee Status],0,0)