referenciando a parte visível da seleção enquanto escreve uma fórmula em excel

Question

Eu sei que existem muitas maneiras de criar subtotabls que a mudança baseia a visibilidade das células - embora minha pergunta possa parecer similar - esta não é a minha pergunta.

suponha que eu tenha uma pasta de trabalho com 2 colunas e 5 linhas, por exemplo

.    A      B
1    Name   Val
2    A      1
3    A      3
4    B      5
5    B      2
6    A      6
7    A      
8    B      5
9    A      2

Estou tentando preencher a célula B7 com uma média de algumas das linhas "A" nomeadas acima dela ... Então eu filtrar a tabela com base no nome col usando "A" o que eu quero obter é a soma da fórmula (B2, B3, B6) ou (ainda melhor) soma (B2: B3, B6) ou seja, a soma das células < strongs que são visíveis durante a criação desta fórmula .

Eu não posso usar o subtotal porque ele mudaria seu valor quando eu alterasse a filtragem ... teoricamente eu poderia usar subtotal e copiar-colar como valor, mas depois perderia a semântica do valor.

Observe que esse valor deve permanecer constante em relação a mudanças futuras na visibilidade da linha (nem preciso que mude se os valores em A2, A3, A6 mudarem de A para B - mas espero que mude se B2 , B3 ou B6 são alterados ....)

Eu também sei que se eu escolher o intervalo B2: B6 (quando ainda filtrado para mostrar apenas "A" linhas nomeadas) e pressione ALT +; (windows) ele irá mudar a seleção para ser exatamente B2: B3, B6, mas isso não parece funcionar durante a criação de uma fórmula: (

Só para esclarecer, os dados reais são muito maiores, então escolher as células uma por uma não é uma opção.

Alguma ideia de como obter a referência para a parte atualmente visível do intervalo atualmente selecionado?

    

Answer 1

Eu esperava ter uma solução melhor, então esperei, mas como parece que não há um aqui, acabei fazendo:

Eu filtrava e selecionava as células que queria calcular em média.

i.e. No exemplo fornecido após a filtragem com base no nome "A" da coluna A, obtive:

.    A      B
1    Name   Val
2    A      1
3    A      3
6    A      6
7    A      
9    A      2

Para ser sincero, não sei ao certo o que eu fiz originalmente para resolver essa questão, envolvia selecionar as células que eu queria e, em seguida, copiá-las (forçando um letreiro a aparecer em torno de cada uma das células visíveis) e usar alguma expressão no painel VBA Watches para obter a lista de endereços de todas as células selecionadas.

no entanto, se eu tentar isso no exemplo e eu usei o mouse para selecionar de B2 a B6 e copiar. efetivamente selecionando B2: B3, B6 (porque B4 e B5 são invisíveis devido ao filtro). Não consigo recriar meu one-liner: (

Dito isso, descobri agora que posso usar debug.print selection.SpecialCells(xlCellTypeVisible).address , o que nem requer a ação de cópia, então, de fato, é provavelmente melhor do que a minha solução original.

Se você pretende usar esse método, saiba que existem alguns limites para o método SpecialCells nos casos em que o intervalo subjacente tem mais de 8192 áreas - veja mais detalhes sobre isso e maneiras de superar isso aqui: < href="https://stackoverflow.com/a/1375508/25412"> link

    

Answer 2

Filtrar e entrar em coisas como essa parece ser muito trabalhoso. Por que não pegar o caminho mais fácil?

=IF(B2<>"","",AVERAGEIF($A$2:A2,"A",$B$2:B2))

Na coluna C, e copie-o. Ao lado de células em branco, ele colocará a média de todos os "A" acima dele. Você pode expandi-lo para mais condições usando o AVERAGEIFS, e você pode mudar a condição de "A" para a2 se você quiser calcular a média de todas as observações acima para esse valor (assim ele pegaria B's, C's e assim por diante).

Sei que isso não responde à sua pergunta exata, mas sua pergunta real pode ser demorada para ser implementada, e essa é uma solução realmente rápida

Depois, você pode criar outra coluna que consolida os valores - assume o valor em B se estiver lá, caso contrário, o valor em C. Você também pode alterar a fórmula para:

=IF(B2<>"",B2,AVERAGEIF($A$2:A2,"A",$B$2:B2))

E faça isso tudo em uma coluna.