Há provavelmente uma maneira mais elegante de fazer isso, mas aqui está pelo menos uma solução deselegante. A fórmula se torna grande, então vou explicar em pedaços.
Euqueriailustrarcomoissofunciona,entãomantiveointervaloviável.Escale-oparaassuasnecessidades.
MeuarraydedadoséD1:F10.Acabeidepreenchê-locomnúmerosconsecutivosporqueissofoifáciledeixouailustraçãoclara.Afórmulanãodependedenúmerosinteirosoudeumaordemespecífica.Eusubstituíváriosdosvalorescomduplicatasdealgunsdosvalorescorrespondentesparaverificarseafórmulafunciona.
NacolunaA,háalgunsvaloresdesegmentação-umemqueacorrespondênciamaispróximaéarredondadaparabaixo,umacorrespondênciaexata,umaemqueacorrespondênciamaispróximaéarredondadaeoutraemqueodestinoéequidistanteentreduascorrespondênciasmaispróximas.Euescolhivaloresdedestinoparaterumacorrespondênciaemcadacoluna.
AcolunaBcontémacorrespondênciamaispróximanamatrizparacadavaloralvo(asolução).
Afórmulaéconstruídaapartirdecomponentes,portanto,explicareioscomponentesmostradosnessagrade:
OsvaloresdedestinosãoreplicadosnacolunaA.AcolunaBencontraadiferençamínimaentreovalordedestinoeovalormaispróximonatabela.FórmulaemB16:
{=MIN(ABS($D$1:$F$10-A1))}
Estaéumafórmuladematriz.Éumcomponentedafórmulafinal,entãoafórmulafinalprecisaserdigitadacomCtrlDeslocarEnter.
Nãosabemosemquedireçãoadiferençaestá,seprecisamosadicionarousubtrairparacorresponderaovalormaispróximonatabela,entãotentamososdois.Paraencontrarovalorcorrespondente"arredondando para baixo", ele usa a fórmula na coluna C. Para encontrar o valor correspondente "arredondando para cima", ele usa a fórmula na coluna D. Um subtrai e um adiciona a diferença:
=SUMPRODUCT(($D$1:$F$10=A1-B16)*$D$1:$F$10)
=SUMPRODUCT(($D$1:$F$10=A1+B16)*$D$1:$F$10)
Se os primeiros parênteses forem verdadeiros, ele será avaliado como 1 , caso contrário, será 0 . O SUMPRODUCT multiplica isso com o valor da célula associada e adiciona os resultados.
Mas observe alguns resultados estranhos.
- Vários alvos retornam zero ou nenhuma correspondência. Esses são os casos em que a correspondência é encontrada por arredondamento na outra direção.
- O destino que estava a meio caminho entre os dois valores mais próximos retorna o arredondamento mais baixo do valor correspondente e o maior valor correspondente é arredondado para cima. Se você se importa com qual deles é escolhido, sequencie os componentes da fórmula de forma arredondada na direção preferida.
- Vários valores são o dobro do que deveriam ser. Estes são os casos em que eu dupliquei o valor mais próximo; a fórmula adiciona em cada um.
Para lidar com esses casos, contamos quantas correspondências são encontradas. Isso é feito nas colunas E e F:
{=SUM($D$1:$F$10=A1-B16)}
{=SUM($D$1:$F$10=A1+B16)}
Esses componentes também são fórmulas de matriz. A fórmula na coluna E avalia se cada valor de célula é igual ao destino menos a diferença, que é True ou False ( 1 ou 0 ) e soma esses valores. A fórmula na coluna F é a mesma, exceto que testa contra o alvo mais a diferença.
Podemos empregar esses resultados de duas maneiras: para testar a inexistência de correspondência (resultado zero), para selecionar o resultado a ser usado e como uma contagem de correspondências para corrigir o valor inflado de várias correspondências na etapa anterior.
Observe que, como estamos encontrando a correspondência mais próxima, sempre haverá um "mais próximo", então arredondar para cima ou para baixo não pode retornar zero. Nós só precisamos testar um dos resultados. Se for zero, usamos o outro. Se não for zero, usamos esse valor, pois o outro valor será zero, o mesmo ou outro resultado válido que pode ser selecionado pela ordem do teste.
Para o resultado válido selecionado, a contagem é usada como um divisor. Então a fórmula para o resultado é:
=IF(E16=0,D16/F16,C16/E16)
Então é assim que funciona. Transformar isso em uma única fórmula consolidada é uma questão de substituir referências de célula pela fórmula na célula referenciada. Aqui está o que a fórmula consolidada parece (eu vou adicionar espaçamento e linha feeds para torná-lo mais legível; você gostaria de removê-los se você quiser copiar e colar a fórmula):
{=IF(SUM($D$1:$F$10=A1-MIN(ABS($D$1:$F$10-A1)))=0,
SUMPRODUCT(($D$1:$F$10=A1+MIN(ABS($D$1:$F$10-A1)))*$D$1:$F$10)/SUM($D$1:$F$10=A1+MIN(ABS($D$1:$F$10-A1))),
SUMPRODUCT(($D$1:$F$10=A1-MIN(ABS($D$1:$F$10-A1)))*$D$1:$F$10)/SUM($D$1:$F$10=A1-MIN(ABS($D$1:$F$10-A1))))}