Tenho duas tabelas do Excel chamadas FacilityLoss e MappingTable. A tabela FacilityLoss tem duas colunas, FiberNum e Loss. FiberNum tem linhas com valores de 1 a 30, e Loss contém os valores de perda de fibra registrados para cada fibra numerada.
Tenho uma célula chamada "Tolerância" que especifica um valor máximo de perda permitido.
A MappingTable possui duas colunas, Fibra UUT e Fibra da Instalação. A coluna UUT possui linhas com valores de 1 a 20. A coluna Fibra da Instalação precisa ser preenchida com fibras da instalação que apresentem valores de perda inferiores ao valor de tolerância. Queremos priorizar conexões 1:1 entre fibras UUT e fibras da instalação para melhorar a clareza da configuração.
Atualmente, tenho a seguinte fórmula nas células da coluna Fibra da Instalação:
=IF(XLOOKUP([@[UUT Fiber]],FacilityLoss[Fiber Num],FacilityLoss[Loss])<=Tolerance,[@[UUT Fiber]],"")
Como você pode ver, funciona corretamente para fibras boas, mas preciso que ele retome a contagem começando pela próxima fibra mais alta em uma segunda passagem.
Não preciso que seja infinitamente recursivo; algumas passagens já seriam suficientes. Tentei criar uma coluna auxiliar que preencheria valores em branco com números que aumentam em um, apesar de pular várias linhas, mas não consigo fazer funcionar direito.
Também implementei uma fórmula para a célula de Tolerância que calcula a melhor tolerância com base no número de fibras solicitadas:
=SMALL(FacilityLoss[Loss],MAX(MappingTable[UUT Fiber]))
Como posso modificar a fórmula XLOOKUP() na coluna FacilityFiber da MappingTable para que ela faça o que eu quero? Aqui está uma captura de tela dos valores corretos preenchidos manualmente:
Estou usando o Microsoft Excel para Microsoft 365 MSO (versão 2502 build 16.0.18526.20286) de 64 bits.
De acordo com a ideia de solução apresentada, a seguinte fórmula pode ser utilizada:
Isso é quase um desafio de quadro e não usa
Xlookup. Se entendi as restrições corretamente, aqui está uma solução alternativa:=IF(E3<=$G$3,D3,INDEX(FILTER($D$23:$D$32,$E$23:$E$32<=$G$3),COUNTIF($E$3:E3,">"&$G$3)))Isso seria colado na célula B3 e depois copiado/colado na célula B22
decompondo isso
thenpeça atribui o número da fibra (1-20) à instalação correspondenteifdentro da tolerânciaelseparte atribui o número de fibras (21-30) à instalaçãoiffora da tolerância. O truque nesta parte é criar uma matriz de números de fibras que atendam à tolerância usando o método "filterentãoindex" e, em seguida, percorrer essa matriz com base na posição do subconjunto de itens em 1-20 que excedem a tolerância usando o método "countife ancorar parcialmente a matriz para contar".A suposição é que a tolerância será ajustada (talvez com o solucionador) para obter os 20 "melhores" fios.