Início / computer / Perguntas / 1713772
Accepted
Preston Maness
Asked: 2022-03-31 21:54:39 +0800 CST

Nome de tabela dinâmica na função Lambda do Excel com referências estruturadas sem INDIRETO

  • 772

É possível definir uma função lambda do Excel que recebe um parâmetro que atua como um nome "dinâmico" para uma tabela, que é então usada em uma referência estruturada, sem usar a função INDIRETA ?
Minha esperança é ser capaz de definir um método auxiliar que permita aos usuários, de fato, usar uma tabela para definir uma estrutura de dicionário com uma string como chave e uma tupla como valor e, em seguida, extrair partes específicas desse tupla.

Uma tabela de exemplo chamada "Variáveis" está abaixo:

Variável Valor inicial Unidade inicial
Pressão 1 caixa eletrônico
Volume 4 eu
massa 10 g
Temperatura 30 C
Rgas 0,7 atm*L/g*C

E meu comportamento desejado é poder definir um lambda que os usuários possam chamar assim:

=l.Variable("Variables","mass","Initial Value")

(com o resultado sendo 10)

Até agora, porém, só posso alcançar essa interface específica empregando a INDIRECTfunção:

=LAMBDA(Table,Var,Prop,
    INDEX(
        INDIRECT(Table & "[#All]"),
        MATCH(Var,INDIRECT(Table & "[[#All],[Variable]]"),0),
        MATCH(Prop,INDIRECT(Table & "[#Headers]"),0)
    )
)

Se, em vez disso, eu quiser evitar o uso de INDIRECT, tenho que codificar o nome da tabela específica:

=LAMBDA(Var,Prop,
    INDEX(
        Variables[#All],
        MATCH(Var,Variables[[#All],[Variable]],0),
        MATCH(Prop,Variables[#Headers],0)
    )
)

Estou sem sorte? Existe uma maneira melhor de fazer o que estou tentando fazer?

Eu vinculei a uma pasta de trabalho que usa a abordagem codificada e a INDIRECTabordagem abaixo:

https://www.dropbox.com/s/344rfrrzhjjr6z2/superuser-lambda-question.xlsx?dl=0

microsoft-excel worksheet-function

3 respostas

  1. Você pode procurar dados arbitrários de uma tabela sem INDIRETO com bastante facilidade. Você só precisa passar a referência completa da tabela com o qualificador [#All] em vez de apenas o nome da tabela como uma string e extrair as seções [#Header] e [#Data] usando INDEX.

    Eu dividi isso em vários lambdas, que é como eu implementaria para mim. Eu também incluí alguma verificação de erros.

    Você pode ajustar conforme necessário para trabalhar exatamente como deseja.

    getTableColumn = lambda(tableall, colname, let(
   header, index(tableall, 1, 0),
   data, let(rcount, rows(tableall), index(tableall, 2, 0):index(tableall, rcount, 0)),
   colIndex, xmatch(colname, header),
   if(isnumber(colIndex), index(data, 0, colIndex), "column not found")));

TableLookup = lambda(tableAll, keyColName, dataColName, keyVal, let(
   keyCol, getTableColumn(tableAll, keyColName),
   dataCol, getTableColumn(tableAll, dataColName),
   keyFound, index(keyCol, 1)<>"column not found",
   dataFound, index(dataCol, 1)<>"column not found",
   ifs(
       not(keyFound), "key column not found",
       not(dataFound), "data column not found",
       and(keyFound, dataFound), xlookup(keyVal, keyCol, dataCol, "key not found"))
    ));

    Então, usando este lambda para seu exemplo original:

    =TableLookup(Variable[#All], "Variable", "Initial Value", "mass")
    • 2
  2. Best Answer

    recebe um parâmetro que atua como um nome "dinâmico" para uma tabela

    A melhor maneira no Excel para converter um parâmetro de texto em um intervalo é usar a função INDIRETO.
    (outras maneiras são OFFSET e função VBA personalizada, no entanto, no seu caso, ambos seriam mais complicados)

    No entanto, você ainda pode melhorar sua fórmula usando FILTER, em vez de INDEX e MATCH.

    O exemplo abaixo mostra apenas parte da fórmula desejada, pois meu Excel ainda não possui LAMBDA.

    =FILTER(INDIRECT(E1&"["&G1&"]"),INDIRECT(E1&"[Variable]")=F1)

    insira a descrição da imagem aqui

    • 1

  3. Método não volátil para INDEX em diferentes Tabelas ou Planilhas

    A única maneira de INDEX em tabelas diferentes usando uma variável de texto é usar INDIRETO conforme a pergunta. A razão para evitar o INDIRETO, é claro, é porque ele é volátil, o que significa que ele será recalculado em todas as oportunidades possíveis (o INDIRETO pode se referir a qualquer célula e, portanto, depende de cada célula). Funções voláteis podem afetar o desempenho, especialmente para pastas de trabalho grandes.

    Para um número finito predefinido de tabelas ou planilhas, você pode usar CHOOSE e LET (LET requer Excel 365). Para mesas:

    =LET(tableall,CHOOSE(MATCH(table,{"Table1","Table2","Table3"},0),Table1[#All],Table2[#All],Table3[#All]),INDEX(tableall,MATCH(var,INDEX(tableall,,1),0),MATCH(prop,INDEX(tableall,1,),0)))

    Onde var é o valor de pesquisa na primeira coluna da tabela e prop é a coluna de cabeçalho correspondente.

    Se você precisar de colunas de tabela específicas, você pode estender LET com várias variáveis, por exemplo:

    =LET(tableindex,MATCH(table,{"Table1","Table2","Table3"},0),tableall,CHOOSE(tableindex,Table1[#All],Table2[#All],Table3[#All]),tablehdr,CHOOSE(tableindex,Table1[#Headers],Table2[#Headers],Table3[#Headers]),INDEX(tableall,MATCH(var,INDEX(tableall,,1),0),MATCH(prop,tablehdr,0)))

    Ou se você precisar apenas de um VLOOKUP e não de um INDEX completo:

    =LET(tableall,CHOOSE(MATCH(table,{"Table1","Table2","Table3"},0),Table1[#All],Table2[#All],Table3[#All]),INDEX(tableall,VLOOKUP(var,tableall,MATCH(prop,INDEX(tableall,1,),0),FALSE))

    E, por último, uma vez que o LAMDA chega à versão de produção do Excel 365, você pode basicamente definir o acima como uma função de intervalo nomeado e chamá-lo com parâmetros, por exemplo=TABLEINDEX("Table1","Mass","Initial Value")

    E como um FYI, você pode fazer o mesmo para endereçar dinamicamente diferentes planilhas de forma não volátil:

    =LET(sheet,CHOOSE(MATCH(sheetname,{"Sheet1","Sheet2","Sheet3"},0),Sheet1!$1:$10000,Sheet2!$1:$10000,Sheet3!$1:$10000),INDEX(sheet, i, j))

    onde sheetname é o sheetname como um valor de texto, i e j são índices de linha e coluna.

    PS. Se você não tiver o Excel 365, tudo acima pode ser feito sem LET, você só precisa substituir/duplicar as fórmulas para cada variável LET nomeada na fórmula principal (mais computacionalmente intensiva).

    • 1

relate perguntas