como filtrar um intervalo para um intervalo usando a fórmula do CSE?

Question

Eu tenho um intervalo A1: C100, as células nas colunas A e B podem estar em branco. Eu gostaria de usar a fórmula CSE, para filtrar A1: C100, somente linhas permanecem que as colunas A e B não estão vazias e preencha isso no intervalo D1: F100.

no SQL, isso seria direto, por exemplo

select A, B, C from [A1:C100] where A!="" and B!=""

mas como eu poderia usar a fórmula do CSE para escrever isso no Excel?

    

Answer 1

Confira este exemplo, com uma coluna sobressalente e três fórmulas de matriz de tipos.

OintervaloA1:B100épreenchidocomstringsaleatórias,comcélulasembranco,intervaloC1:C100éapenasalgunsnúmeros(tambémpodemserstrings).

OintervaloH1:H100éusadocomocolunadeajuda,que,naverdade,filtraeacumulaosnúmerosdaslinhascomcélulasnãovaziasemAeB.

H1:fórmuladematrizparaencontraroprimeironúmerodelinhacomcélulasnãovaziasnascolunasAeB:

=MATCH(1,SIGN(LEN(TRIM($A$1:$A$100))*LEN(TRIM($B$1:$B$100))),0)

H2:outrafórmuladematrizparaencontrartodososnúmerosrestantesdelinhacomcélulasnãovaziasemAeBcolunas:

=IF(ROW()>SUM(SIGN(LEN(TRIM($A$1:$A$100))*LEN(TRIM($B$1:$B$100)))),"",MATCH(1,SIGN(LEN(TRIM(INDIRECT(ADDRESS((1+H1),1)&":$A$100")))*LEN(TRIM(INDIRECT(ADDRESS((1+H1),2)&":$B$100")))),0)+H1)

A fórmula é bastante longa, portanto, apenas no caso, aqui está, formatada para mostrar a estrutura:

IF( 
  ROW()>
          SUM(
            SIGN(
              LEN(TRIM($A$1:$A$100))*LEN(TRIM($B$1:$B$100))
            )
          )
  ,""
  ,MATCH(1, 
           SIGN(
              LEN(TRIM(INDIRECT(ADDRESS((1+H1),1)&":$A$100")))
             *LEN(TRIM(INDIRECT(ADDRESS((1+H1),2)&":$B$100")))
           )
         ,0
   )+H1
)

Digite o último tipo de fórmula de matriz em D1 :

=IF(ROW()>SUM(SIGN(LEN(TRIM($A$1:$A$100))*LEN(TRIM($B$1:$B$100)))),"",INDEX($A$1:$C$100,$H1,COLUMN()-COLUMN($D$1)+1))

copie a célula para E1 , F1 ; selecione e copie o intervalo D1:F1 ; selecione o intervalo D2:F100 e cole as células copiadas.

As fórmulas podem ser muito mais curtas em relação a mais colunas auxiliares.

Nota obrigatória: para entrar a fórmula do array , pressione Ctrl + Deslocamento + Enter em vez de apenas Enter para a fórmula escalar).

    

Answer 2

• Escolha a guia Dados
• Selecione da tabela, na seção "Obter e transformar".
• Escolha filtros, "Remover espaços vazios", quando solicitado.
• ou
• Filtro avançado: em que ColA não é igual a null E ColB não é igual a null, por exemplo.

VocêtambémpodeadicionarumacláusulaANDemsuaconsultalógica(porexemplo,ondeColAnãoéigualanulleColBnãoéigualanull)

SelecioneCarregar&Perto.Feito.

"Get & Transform > From Table" function is not available in Excel 2010

• Ir para: a guia "Dados" > Ordenar & Filtrar > Avançado.

ObserveosCritérios,comosãodigitados.