Usando Filtro Avançado no Excel para resolver o problema de Mort. Embora os filtros regulares tenham se tornado mais poderosos, ainda há momentos em que o Filtro Avançado pode fazer alguns truques que outros não podem.
Assistir vídeo
- O Filtro avançado é mais "avançado" do que o filtro normal porque:
- 1) Pode copiar para um novo intervalo
- 2) Você pode construir critérios mais complexos, como Campo 1 = A ou Campo 2 = A
- 3) é rápido
- Mort está tentando processar 100 mil linhas em VBA fazendo um loop através dos registros ou usando uma matriz
- Sempre será mais rápido usar os recursos integrados do Excel do que escrever seu próprio código.
- Você precisa de um intervalo de entrada e, em seguida, um intervalo de critérios e / ou um intervalo de saída
- Para o intervalo de entrada: linha única de títulos acima dos dados
- Adicionar uma linha temporária para títulos
- Para o intervalo de saída: uma linha de títulos para as colunas que você deseja extrair
- Para o intervalo de critérios: títulos na linha 1, valores que começam na linha 2
- Complicação: as versões mais antigas do Excel não permitiam que o intervalo de saída ficasse em outra planilha
- Se você estiver escrevendo uma macro que pode ser executada em 2003, use um intervalo nomeado para o intervalo de entrada para contornar
Transcrição de vídeo
Aprenda Excel com o Podcast, Episódio 2060: Filtro Avançado do Excel
Ei, bem-vindo de volta ao netcast, sou Bill Jelen. A pergunta de hoje enviada por Mort. Mort, ele tem 100.000 linhas de dados e está interessado nas Colunas A, B e D, onde a Coluna C corresponde a algum ano específico. Então ele quer que uma pessoa insira um ano e obtenha as colunas A, B e D. E Mort tem algum VBA em que está usando arrays para fazer isso e eu disse: “Espere um segundo, você sabe, o filtro avançado faria isso a muito melhor. ” Tudo bem, e agora só para revisar, voltei, revi meus vídeos. Eu não abordo o filtro avançado há muito tempo, então devemos conversar sobre isso.
O filtro avançado requer um intervalo de entrada e pelo menos um destes: um intervalo de critérios ou um intervalo de saída. Embora hoje vamos usar ambos. Tudo bem, então o intervalo de entrada são seus dados e você deve ter cabeçalhos acima dos dados. Portanto, Mort não tem títulos e, portanto, vou inserir temporariamente uma linha aqui em cima e apenas fazer como o Campo 1. Mort sabe quais são seus dados e, portanto, poderia colocar títulos reais lá. E não estamos usando nada chamado - esses dados nas Colunas E a O, então não tenho que adicionar cabeçalhos lá, certo? Portanto, agora, A1 a D, 100.000 se torna meu intervalo de entrada. E então o intervalo de saída e o intervalo de critérios - Bem, o intervalo de saída é apenas uma lista dos títulos que você deseja. Vou colocar o intervalo de saída aqui e não precisamos do Campo 3, entãoVou apenas tirar isso de lado. Portanto, agora, este intervalo aqui, A1 a C1, torna-se meu intervalo de saída que informa ao Excel quais campos desejo do intervalo de entrada. E, eles poderiam estar em uma ordem diferente se você quisesse reordenar as coisas, como se eu quisesse o Campo 4 primeiro e, em seguida, o Campo 1, depois o Campo 2. E, novamente, esses seriam títulos reais como o número da fatura. Só não sei como são os dados de Mort.
E então, o intervalo de critérios é um título e o valor que você deseja. Então, digamos que eu estava tentando conseguir alguma coisa no ano de 2014. Isso se torna o intervalo de critérios como esse. Tudo bem, apenas uma palavra de cautela aqui. Estou no Excel 2016 e é possível fazer um filtro avançado entre duas planilhas no Excel 2016, mas se você voltar e não me lembro que caminho é, talvez 2003, não tenho certeza. Em algum ponto no passado, costumava ser que você não podia fazer um filtro avançado de uma página para outra, então você teria que vir aqui e nomear seu intervalo de entrada. Você teria que criar um nome aqui. MyName ou algo parecido, certo? E essa seria a maneira que você seria capaz de fazer isso, certo. Não necessariamente no Excel 2016, mas novamente, eu 'Não tenho certeza se Mort vai executar isso em versões mais antigas dos dados.
Tudo bem, então de volta aqui em Dados, vamos para Filtro Avançado, tudo bem. E vamos Copiar para outro local que habilite nosso intervalo de saída lá. Tudo bem, então o intervalo da lista, onde estão os dados? Como estou no Excel 2016, vou apontar para os Dados, em vez de usar o intervalo de nomes - esse é meu intervalo de entrada. O intervalo de critérios são aquelas células bem ali e então, para onde vamos - a saída, serão apenas essas três células ali. E então clicamos em OK. Tudo bem e BAM! É assim que é rápido, rápido. E se quiséssemos um ano diferente? Se quiséssemos um ano diferente, excluiríamos os resultados, inseriríamos 2015 e, em seguida, faríamos um filtro avançado novamente, Copiar para outro local, clicar em OK e teríamos todos os registros de 2015. À velocidade de um relâmpago.
Tudo bem agora, embora eu seja um fã de filtro avançado no Excel regular, eu era um grande fã de filtro avançado em VBA, certo, porque o VBA torna o filtro avançado muito, muito, muito simples. Tudo bem, então vamos escrever algum código aqui para Mort, assumindo que os dados de Mort não têm títulos e vamos ter que adicionar os títulos temporariamente, certo? Então, vou mudar para VBA, Alt + F11 e vamos executar isso a partir da planilha que contém os dados. Portanto: Dim WS como planilha, defina WS = ActiveSheet. E então, insira a Linha 1 e apenas adicione alguns títulos: A, B, Ano e D. Descubra quantas linhas de dados temos hoje e, em seguida, começando da célula A1 indo para 4 colunas até a linha final, nomeie-a ser faixa de entrada. Tudo bem, e este é o código de Mort aqui, onde ele pediu o InputBox,pega o ano que eles querem e depois pergunta em que ano ou como eles querem dar um nome para a nova planilha, certo. Então, ele vai realmente inserir uma planilha no Fly e então dimensionar uma nova planilha, WSN, como a ActiveSheet. Então eu sei que WS é a folha original, WSN é a nova folha que acabou de ser adicionada. Na nova planilha, coloque o intervalo de critérios de forma que na Coluna E haja o título que corresponde a esse título aqui e, em seguida, a resposta que eles nos deram entrará em E2. O intervalo de saída será meus outros três títulos: A, B e D. E, novamente, se você ou Mort alterá-los para títulos reais, o que provavelmente é uma coisa melhor a fazer do que A, B, D, e você também mude para títulos reais, certo? Portanto, tudo isso é apenas um pouco de pré-trabalho aqui. Esta linha de código incrível fará todo o filtro avançado. Assim,do InputRange fazemos um AdvancedFilter, vamos copiar. Essa é a nossa escolha de filtro no local ou cópia. O CriteriaRange é de E1 a E2, o CopyToRange é de A a C. Valores únicos -Não, queremos todos os valores. Tudo bem, aquela linha de código ali faz toda a mágica de fazer um loop em todos os registros ou substitui o loop em todos os registros ou fazer os arrays. E então terminaremos, limparemos o intervalo de critérios e, em seguida, excluiremos a Linha 1 de volta na planilha original.E então terminaremos, limparemos o intervalo de critérios e, em seguida, excluiremos a Linha 1 de volta na planilha original.E então terminaremos, limparemos o intervalo de critérios e excluiremos a Linha 1 de volta na planilha original.
Ok, então vamos voltar aqui para nossos dados. Vamos tornar mais fácil executá-lo, então: Insira, uma Forma e chame este Filtro, Home, Centro, Centro, Maior, Maior, Maior, clique com o botão direito, Atribuir Macro e atribua a MacroForMort. Tudo bem, então vamos lá. Vamos fazer um teste. Veja que estamos na ficha técnica, clique no Filtro, que ano queremos? Queremos 2015. Como quero chamá-lo? Eu quero chamá-lo de 2015, certo. E BAM! Aí está feito. Isso é o quão rápido, é o quão rápido é.
Agora, como os dados originais de Mort não tinham cabeçalhos, talvez esses dados não devessem ter cabeçalhos. Então vamos Alt + F11, bem aqui queremos limpar o intervalo de critérios. Também iremos Rows (1) .Delete. Tudo bem, então agora da próxima vez que estivermos nisso, ele vai se livrar dos títulos. E vamos apenas - em vez de executar tudo rapidamente, vamos dar uma olhada aqui com o 2014. Então, vou selecionar uma célula no Data, Alt + F11, e quero executar apenas até o ponto onde fazemos o filtro avançado. Assim, podemos olhar e ver o que toda a macro está fazendo aqui. Então, clicaremos em Executar e quero 2014. 2014, certo. E então, pressione F8, estamos prestes a fazer o filtro avançado. Podemos reverter para o Excel aqui e ver o que aconteceu.
A primeira coisa que aconteceu- Agora, a primeira coisa que aconteceu é que adicionamos uma nova linha temporária com os títulos. Inseri esta planilha, construímos um intervalo de critérios com um título e em que ano eles inserem, escolhemos os campos que queremos fazer e depois voltamos ao VBA, executarei a próxima linha de códigos, que é F8 que faz o filtro avançado ali . É incrivelmente rápido e você verá que agora nos trouxe todos os discos. A partir daí, é só uma pequena limpeza, exclua isso, exclua isso. Voltarei aos dados e excluirei a Linha 1 e estaremos prontos para prosseguir. Então, vou deixar o resto correr, remover esse ponto de interrupção, certo? Portanto, há o VBA. Para mim, esse é o caminho mais rápido, o caminho mais rápido para ir.
Alright, episode recap: The advanced filter is more advanced than the regular filter because it can copy to a new range. And now, I didn't show it in this video but you can build complex criteria where Field 1 = A or Field 2 = A. The regular auto filter can't do that and it's fast. Mort is trying to process 100,000 rows in VBA by using an array or by looping, but it'll always be faster to use Excel building features than writing your own code. You need to define an input range, criteria range, output range. You always need an input range in at least one of these, although today I'm using both. For the input range, single row of headings above the data. So we're going to add a temporary row of headings. For the output range, the same headings that you want to extract, alright. So, you know, if it was A, B, Year and D, we’ll just put A, B and D as the output range. For the criteria range, headings in Row 1. So this is the field I want to build a criteria on and this is the value I'm looking for. Complications: Older versions of Excel will not allow the output range to be on another sheet, so, potentially your code will run back then. You want to use a named range for the input range because from this sheet, you know, the named range, even though it's on another sheet, the sheet believes the name branches on the current sheet. So that would allow the advanced filter to work.
Tudo bem, bem, aí está. Quero agradecer a Mort por enviar essa pergunta. Quero agradecer a você por passar por aqui. Nos vemos na próxima vez para outro netcast de.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2060.xlsm
