☰

Como obter o valor mais à direita da linha no Excel?

tag-icon • tag-icon microsoft-excel
Como obter o valor mais à direita da linha no Excel?

Preciso mostrar o valor da célula não vazia mais à direita de cada linha em uma matriz. Como isso pode ser feito no Excel?

Nesta tabela de exemplo a coluna [Atual] tem o resultado desejado:

+---------+----------+---------+----------+
|  2016   |   2017   |  2018   | Current  |
+---------+----------+---------+----------+
|     700 |          |   200   |     200  |
|         |          |         |          |
|         |     450  |         |     450  |
|         |          |  2,700  |   2,700  |
|         |          |         |          |
|  42,350 |  71,500  |         |  71,500  |
|  2,660  |          |         |   2,660  |
|         |   1,100  |         |   1,100  |
|         |          |         |          |
|    470  |          |         |     470  |
+---------+----------+---------+----------+

Variações sobre o tema seriam os valores mais à esquerda, mais alto e mais baixo; ou valor maior quen, etc. Desktop Excel do Office 2016 se a versão for relevante.

Responder1

insira a descrição da imagem aqui

  • Insira esta fórmula E2e preencha.

=LOOKUP(2,1/(A2:C2<>""),A2:C2)

Como funciona:

  • A fórmula reconhece que o valor de pesquisa de 2 é deliberadamente maior do que quaisquer valores que aparecerão no vetor de pesquisa.
  • A expressão A2:C2<>""retorna uma matriz de valores Truee False.
  • 1é então dividido por este Array e cria um novo Array composto por erros de 1 ou divisão por zero (#DIV/0!): {1,0,1,...}.
  • Essa matriz é o vetor de pesquisa.
  • Quando a fórmula não consegue encontrar o valor de pesquisa,
    Lookupcorresponde ao próximo menor valor.
  • Nesse caso, o valor de pesquisa é 2, mas o maior valor na matriz de pesquisa é 1, portanto, a pesquisa corresponderá ao último 1na matriz.
  • LOOKUP retorna o valor correspondente no Vetor de Resultado, que é o valor na mesma posição.

:Editado:

  • Para o Planilhas Google, esta é a fórmula a ser usada:

    =(IFERROR(LOOKUP( 2, 1 / ( A2:C2 <> "" ), A2:C2 ),""))

  • Termine comCtrl+Shift+Enter, a fórmula será semelhante a,

    =ArrayFormula(IFERROR(LOOKUP( 2, 1 / ( A2:C2 <> "" ), A2:C2 ),""))

Responder2

Embora já existam múltiplas soluções para este problema, aqui está a minha preferida, para mim é a que mais se aproxima do pensamento natural:

=INDEX(A2:C2,MAX(IF(A2:C2="","",COLUMN(A2:C2))))- esta é uma fórmula de matriz, então pressione CTRL+ SHIFT+ ENTERapós digitá-la.

insira a descrição da imagem aqui

Como funciona:

  • IF(A2:C2="","",COLUMN(A2:C2))- para cada célula da linha retorna uma string vazia se a célula estiver vazia e o número da coluna caso contrário
  • MAX( ... )- seleciona o maior número de coluna retornado
  • =INDEX(A2:C2, ... )- seleciona a célula da linha com base no maior número da coluna

Aviso: funciona corretamente apenas se o seu intervalo começar na primeira coluna; caso contrário, será necessário compensar a mudança, por exemplo, para um intervalo começando na coluna C:
=INDEX(C2:X2,MAX(IF(C2:X2="","",COLUMN(C2:X2)))-2)

Responder3

Vamos supor que sua tabela esteja disposta em C2:F12 com a linha do cabeçalho sendo a linha 2 e a coluna de resumo sendo F. Coloque a seguinte fórmula em F3 e copie.

=IFERROR(INDEX(3:3,AGGREGATE(14,6,column($C3:$E3)/($C3:$E3<>""),1)),"")

POC

OBSERVAÇÃO:

  • AGGREGATE executa operações de array com as opções de fórmula 14 e 15. Como resultado, não use referências completas de coluna/linha dentro da função AGGREGATE, pois você pode acabar paralisando seu sistema ou travando-o com o número de cálculos que serão executados. Usar referências de coluna completas fora das funções do tipo array é adequado. observe o 3:3 sendo usado para INDEX.

  • Ao inserir uma nova coluna, se a coluna F for selecionada e a inserção for realizada, será necessário atualizar a fórmula em F para que C3:F3 seja o novo intervalo. Se você tiver a coluna E selecionada e inserir uma nova coluna, o intervalo será atualizado automaticamente, mas agora seus dados estão na coluna errada. Se você deixou a coluna F em branco, colocou as fórmulas na coluna G e usou C3:F3 como intervalo dentro de AGGREGATE, no futuro você poderá selecionar a coluna F para inserção e suas fórmulas serão atualizadas e você poderá inserir novos dados em F Você teria uma coluna em branco à direita ainda para seleção no próximo ano para repetir o processo.

Responder4

Outra abordagem, deselegante, mais brutal, mas facilmente compreensível é usar TEXTJOIN(), agora que o temos.

Usando A2:C2 para a primeira linha, coloque o seguinte em D2, copie e cole. Ou Preencher, ou... essa é a ideia:

ParaString de junção de textoabaixo, use a função TEXTJOIN() para concatenar todo o intervalo de células que você deseja examinar. Use "TRUE" para deixar espaços em branco para encurtar a string e, para um delimitador, use um caractere que NUNCA aparecerá realisticamente em seus dados. Eu uso "Ŧ" abaixo (e para o caractere substituir o último por "Ų"). Usar vírgulas como costumamos fazer com TEXTJOIN() e seus parentes pode causar problemas.

=RIGHT( Textjoin string,
LEN( Textjoin string ) -
FIND( "Ų", SUBSTITUTE( Textjoin string, "Ŧ", "Ų",
LEN( Textjoin string **with** delimiter ) - LEN( Textjoin string **without** delimiter )
)))

e é mais fácil de entender. SUBSTITUTE() pode fazer seu trabalho começando em umInstância #o que permitiria encontrar o último uso do seu delimitador emString Textjoin com delimitador. Na última linha, você encontra o LEN() da string Textjoin com e sem o delimitador e encontra a diferença por subtração. Esse é o número de delimitadores e, portanto, oInstância #você precisa.

Na penúltima linha, você substitui essa instância por um caractere diferente e, em seguida, usa FIND() para obter sua posição na string.

A segunda linha subtrai essa posição do LEN() geral da string para descobrir quantos caracteres a seguem. Isso informa quantos caracteres devem ser retirados à direita da string que você criou.

A primeira linha faz exatamente isso, deixando você com o conteúdo da última célula do intervalo.

O comprimento da string que o Excel usará varia de acordo com a função, alguns na faixa de 6 a 7.000, por exemplo, outros mais como 32.000. Com isso em mente (é por isso que você especifica "TRUE"), pode-se fazer um intervalo ENORME em vez de A2:C2.

Observe que você está trabalhando com a string unida, não com células:

  1. Você nunca precisa encontrar endereços de células, etc.
  2. Na verdade, você pode usá-lo em um intervalo composto de "sub" intervalos unidos e intervalos compostos de células REALMENTE separadas. Faixas descontínuas são seus amigos e aliados.

Devido à forma como os dados existem dentro das partes avaliadas pelo Excel dentro de uma fórmula, dividir os pedaços em intervalos nomeados pode causar problemas, ou não, pois os resultados provisórios criados e usados ​​pelo Excel para avaliar a fórmula PODEM estar em um formato diferente do resultado final que um intervalo nomeado apresenta adiante, às vezes não é possível usar intervalos nomeados em peças para definir a lógica de uma fórmula para facilidade futura. Mas nada acima apresenta esse problema, então você pode criar intervalos nomeados para, digamos, os TEXTJOIN e inserir o restante nativamente para que qualquer pessoa que clicar na célula possa ver a lógica. Ou divida as peças em algo lógico como "InstanceNumber" (Named Range) para que a leitura seja ainda mais fácil. Crie-o e despeje tudo em um intervalo nomeado. Ou não se preocupe com intervalos nomeados.

Como eu disse, deselegante. Mais longo do que algumas soluções, mas não realmente "brutal" como algumas coisas são. Não há colunas auxiliares ou outras coisas que as pessoas muitas vezes não possam usar. Ou não. Não há fórmulas {array}.

(E você pode usar os intervalos descontínuos quando necessário.) A abordagem também pode pegar uma pilha de texto e dados que um mecanismo de relatório contém em PDF e que é então extraído para o Excel, mas dividido de forma diferente em células para cada conjunto relacionado (portanto, informações sobre 10 clientes , cada um definido em um bloco de 10 colunas por 13 linhas, mas o endereço de um está na célula 4,6 e na célula 3,8 de outro, mas segue o mesmo fluxo, apenas preenche células diferentes quando importado) e tornando-o um corda única, permite que você procure as peças de maneira formulada. Muitas vezes, de qualquer maneira. Ou pegue um bloco de células e veja se algum dado aparece em algum lugar dentro delas usando funções, não macros ou arrays ou uma célula auxiliar para cada uma no bloco.

informação relacionada