Como o OFFSET deve funcionar nos dados filtrados do EXCEL?

Question

Considere:

    +---------------+
    | Column A      |
+---+---------------+
| 1 | Milan         |
+---+---------------+
| 2 | Paris         |
+---+---------------+
| 3 | London        |
+---+---------------+
| 4 | Milan         |
+---+---------------+
| 5 | Firenza       |
+---+---------------+
| 6 | Napoli        |
+---+---------------+
| 7 | Amsterdam     |
+---+---------------+
| 8 | Copenhagen    |
+---+---------------+
| 9 | Amsterdam     |
+---+---------------+

Suponha que eu filtre a coluna A para procurar cidades na Itália. Os seguintes resultados são retornados.

    +---------------+
    | Column A      |
+---+---------------+
| 1 | Milan         |
+---+---------------+
| 4 | Milan         |
+---+---------------+
| 5 | Firenza       |
+---+---------------+
| 6 | Napoli        |
+---+---------------+

Agora, considere a seguinte expressão:

=IFERROR(VLOOKUP($AR5,Copy!$I$1:$K$3,2,0),OFFSET($A$1,2,0))

O OFFSET refere-se ao TODO dos dados de origem? nesse caso, meu valor padrão no caso de erro seria London - ou apenas o conjunto de resultados - nesse caso, meu valor padrão em caso de erro seria Firenze ?

Estou vendo resultados inconsistentes em minha planilha, e o que restringir minhas opções de depuração, não encontrando o que deveria acontecer nesta circunscrição.

    

Answer 1

Primeiro, sobre as fórmulas de depuração em geral:

A melhor maneira de fazer isso é usar a funcionalidade Evaluate formula (na guia Formula ). Isso lhe dá uma boa maneira de ver o que acontece em cada etapa / parâmetro. Além disso, se você destacar uma parte em sua fórmula (por exemplo, a fórmula COMPLETA OFFSET), pressione F9 . Isso avaliará a instrução destacada na barra de fórmulas.

O OFFSET retornará London , pois o OFFSET também considerará as células ocultas.

Se você quiser retornar a segunda célula visível, é necessário um pequeno truque envolvendo uma coluna extra: Coloque a fórmula =SUBTOTAL(3,A1)+C1 em C2 (supondo que sua tabela comece na linha 1 e a coluna C esteja disponível. Em seguida, copie a fórmula Ele agora mostrará um contador para todas as células visíveis, ou seja, os números serão alterados de acordo com o seu filtro automático.

Agora, a fórmula =INDEX(A:A,MATCH(2,C:C,0)) retornará o segundo elemento visível.

    

Answer 2

Não acredito que OFFSET() exclua dados ocultos / filtrados. No exemplo abaixo, você pode ver que, mesmo quando parte dos dados é ocultada / filtrada, a fórmula em C1 aponta para a mesma célula.

Paraobteroterceiroitemvisíveldasualista,vocêpodeusaressafórmuladematriz¹:

=INDEX(xmen,SMALL(IF(SUBTOTAL(3,OFFSET(xmen,ROW(xmen)-ROW(A2),0,1)),ROW(xmen)),3)-1,1)

Onde:

xmenrefere-seaointervalodedados,excluindooscabeçalhos(A1:A9noseuexemplo,A2:A9naamostraabaixo)
A2→apontaparaoprimeiroitemdoseualcance(nãotítulo)

Porexemplo:

-
¹ Deve ser confirmado / digitado usando Ctrl + Shift + Digite depois de integrá-lo com sua fórmula VLOOKUP

    

Answer 3

Mantenha a simplicidade com isto:

=VLOOKUP(SUBTOTAL(5,A:A),A:B,2,FALSE)

Ele encontra o menor número visível (subtotal 5 é mínimo) e vlookup o valor (que também recebe o primeiro valor encontrado com esse número de índice).