Arquivo da tag: AutoFiltro

VBA – Obtendo os valores dos filtros aplicados numa planilha

AutoFiltro

Mais uma pra lista dos canivetes suíços. O autofiltro é um mecanismo tão conhecido e utilizado que poucos notam um defeito (pelo menos em minha opinião é um defeito).

Supondo que você tenha uma tabela com 20 colunas. Você filtra uma coluna, duas, três…. quando chega no resultado que quer, já nem lembra mais quais os filtros selecionou. O que é normalmente feito é, limpa-se os filtros e começa tudo de novo.

Foi um cliente (sempre eles, mas eu gosto!) que gerou a necessidade. Portanto, foi preciso uma forma de extrair quais os filtros estavam sendo aplicados na planilha naquele momento. Uma garimpada na internet e lá veio uma resposta.

Adaptei o código abaixo para atender às necessidades da maioria e corrigir alguns erros. Mantive o nome da função original para garantir a autoria do código

Public Function GetAutoFilterCriteria() As String
On Error GoTo trataerro
    Dim oAF As AutoFilter
    Dim oFlt As Filter
    Dim sField As String
    Dim sCrit1 As String
    Dim sCrit2 As String
    Dim sMsg As String
    Dim i As Integer
 
    ' Verifica se há filtros ativados na planilha
    If Not ActiveSheet.AutoFilterMode And Not ActiveSheet.ListObjects(1).ShowAutoFilter Then
        sMsg = "O auto filtro não está ativado"
        GoTo trataerro
    Else
        If ActiveSheet.AutoFilterMode Then
            ' obtém o objeto de filtro da planilha
                Set oAF = ActiveSheet.AutoFilter
        Else
            ' obtém o objeto de filtro da planilha
            Set oAF = ActiveSheet.ListObjects(1).AutoFilter
        End If
    End If
 
    ' itera em todos os filtros aplicados na planilha
    For i = 1 To oAF.Filters.Count
        ' obtém no nome da coluna
        sField = oAF.Range.Cells(1, i).Value
 
        ' obtém o objeto filtro da coluna
        Set oFlt = oAF.Filters(i)
 
        ' Está ativo?
        If oFlt.On Then
 
            ' Obtém o primeiro critério de filtro (tem que haver ao menos um)
            If IsArray(oFlt.Criteria1) Then
                Dim x As Integer
                sMsg = sMsg & vbCrLf & sField
                For x = 1 To UBound(oFlt.Criteria1)
                    sMsg = sMsg & "'" & oFlt.Criteria1(x) & "'"
                Next x
            Else
                sMsg = sMsg & vbCrLf & sField & "'" & oFlt.Criteria1 & "'"
            End If
 
            ' Verifica se há operador aplicado. Caso positivo, analisa o critério seguinte
            Select Case oFlt.Operator
                Case xlAnd
                    sMsg = sMsg & " E " & sField & "'" & oFlt.Criteria2 & "'"
                Case xlOr
                    sMsg = sMsg & " Ou " & sField & "'" & oFlt.Criteria2 & "'"
                Case xlBottom10Items
                    sMsg = sMsg & " (últimos 10 itens)"
                Case xlBottom10Percent
                    sMsg = sMsg & " (últimos 10%)"
                Case xlTop10Items
                    sMsg = sMsg & " (primeiros 10 itens)"
                Case xlTop10Percent
                    sMsg = sMsg & " (primeiros 10%)"
            End Select
        End If
 
    Next i
 
    If sMsg = "" Then
        ' Mensagem vazia, signifca que não há filtros aplicados
        sMsg = "Não há filtros ativados"
        Else
        ' Do contrário, monta a mensagem
        sMsg = "Filtros aplicados: " & Left(sMsg, Len(sMsg) - 1)
    End If
 
trataerro:
    If Err.Description <> "" Then
        Debug.Print Err.Description
        GetAutoFilterCriteria = "Não foi possível analisar os filtros"
    Else
        ' Display the message
        GetAutoFilterCriteria = sMsg
    End If
End Function

A função não é infalível, mas funcionou muito bem nos casos em que testei. A planilha abaixo mostra a função em trabalhando:

AutoFiltro Trabalhando

AutoFilterMode.zip
VBA – Obtendo os valores dos filtros aplicados numa planilha(20.19 KiB

Link da macro original: http://www.vbaexpress.com/forum/archive/index.php/t-7564.html

Excel – Somando apenas as células visíveis no AutoFiltro

Como complemento do último post, que dava a possibilidade de efetuar operações matemáticas em células filtradas através do VBA, adiciono uma alternativa disponibilizada por um colega do fórum INFO.

Existe uma forma mais simples de fazer operações matemáticas em dados filtrados fazendo uso da função SUBTOTAL.

A descrição da Ajuda do Excel fala por si:

A função SUBTOTAL retorna um subtotal em uma lista ou em um banco de dados. É geralmente mais fácil criar uma lista com subtotais usando o comando Subtotais, grupo Contorno, na guia Dados. Assim que a lista de subtotais for criada, você poderá modificá-la editando a função SUBTOTAL.

Sintaxe

SUBTOTAL(núm_função, ref1, ref2, …)

Núm_função é o número de 1 a 11 (incluindo valores ocultos) ou 101 a 111 (ignorando valores ocultos) que especifica qual função usar no cálculo de subtotais dentro de uma lista.

Núm_função
(incluindo valores ocultos)
Núm_função
(ignorando valores ocultos)
Função
1 101 MÉDIA
2 102 CONTA
3 103 CONT.VALORES
4 104 MÁX
5 105 MÍN
6 106 MULT
7 107 DESVPAD
8 108 DESVPADP
9 109 SOMA
10 110 VAR
11 111 VARP

Ref1, ref2 são de 1 a 254 intervalos ou referências cujo subtotal você deseja.

Comentários

  • Se existirem outros subtotais dentro de ref1; ref2,… (ou subtotais aninhados), esses subtotais aninhados serão ignorados para evitar dupla contagem.
  • Para as constantes núm_função de 1 a 11, a função SUBTOTAL inclui os valores de linhas ocultas pelo comando Ocultar Linhas no submenu Ocultar e Exibir do comando Formato no grupo Células, na guia Início). Use essas constantes para subtotalizar números ocultos e não-ocultos em uma lista. Para as constantes núm_função de 101 a 111, a função SUBTOTAL ignora valores de linhas ocultos pelo comando Ocultar Linhas. Use essas constantes para subtotalizar somente números não-ocultos em uma lista.
  • A função SUBTOTAL ignora as linhas não incluídas no resultado de um filtro, independentemente de qual valor de núm_função seja utilizado.
  • A função SUBTOTAL foi projetada para colunas de dados ou intervalos verticais. Ela não foi projetada para linhas de dados nem intervalos horizontais. Por exemplo, quando você subtotaliza um intervalo horizontal usando uma núm_função de 101 ou maior, como SUBTOTAL(109,B2:G2), ocultar uma coluna não afeta o subtotal. Porém, ocultar uma linha em um subtotal de um intervalo vertical afeta o subtotal.
  • Se qualquer uma das referências for uma referência 3D, a função SUBTOTAL retornará o valor de erro #VALOR!.

Exemplo

Portanto, considerando a mesma planilha do post anterior, cuja fórmula mencionada foi:

=Soma_Celulas_Visiveis(C2:C50)

pode ser substituída por:

=SUBTOTAL(9;C2:C50)

A vantagem óbvia é a simplicidade de uso e a dispensa do uso de macros. O post anterior mantém sua utilidade para execução de funções mais complexas em dados não filtrados.

Agradecimentos

Fica meu agradecimento ao usuário Avatar_2010 do fórum INFO de Excel sobre a dica colocada. Valeu garoto!

Bom proveito!

VBA – Somando apenas as células visíveis no AutoFiltro

Dois dos recursos mais poderosos do Excel infelizmente não conseguem trabalhar bem juntos.

O problema reside no fato de que a função SOMA compreende um range de células e inclui em sua soma todo o intervalo. Por exemplo, você possui uma planilha que abrange da linha 2 a linha 50. Aplica uma fórmula SOMA, por exemplo, na coluna C, o que geraria o seguinte:

=SOMA(C2:C50)

Se aplicar um AutoFiltro nesta planilha, perceberá que sua fórmula continua considerando todas as linhas, mesmo as que não estão visíveis. Dependendo da situação, isso pode ser um problema. Não há funções tradicionais para resolvê-lo, porém, uma macro pode ajudar.

Copie e cole esta função personalizada em um módulo VBA.

Function Soma_Celulas_Visiveis(Cells_To_Sum As Object)
    Application.Volatile
    For Each cell In Cells_To_Sum
        If cell.Rows.Hidden = False Then
            If cell.Columns.Hidden = False Then
                Total = Total + cell.Value
            End If
        End If
    Next
    Soma_Celulas_Visiveis = Total
End Function

Agora, substitua a função citada acima pela seguinte:

=Soma_Celulas_Visiveis(C2:C50)

Ou adapte ao range da sua planilha. Agora, aplique o autofiltro de forma que algumas linhas fiquem escondidas. Note que a função considera apenas as células que estão visíveis agora.

Explicando

O segredo da função acima está na seguinte linha:

If cell.Rows.Hidden = False Then

Essa linha verifica se a linha da célula corrente está oculta ou não (propriedade Hidden). Quando o filtro é aplicado, as linhas que não obedecem aos critérios do filtro ficam no estado de Hidden = True.

Espero que a dica seja de grande ajuda.

Bom proveito!

Download da Planilha de exemplo

http://www.tomasvasquez.com.br/downloads/Soma_Celulas_Visiveis.zip

Referências

http://support.microsoft.com/?scid=kb%3Ben-us%3B150363&x=10&y=14

Excel 2000 – Habilitando o AutoFiltro em uma planilha protegida

Ativar o AutoFiltro em uma planilha protegida desde o Excel XP/2002 é uma tarefa simples, já que ao protegê-la, a caixa de diálogo oferecida já oferece esta possibilidade.

O problema é que os usuários do Excel 2000, que ainda são muitos não gozam desta funcionalidade. Felizmente, a Microsoft publicou um código em VBA que implementa esta funcionalidade. O código abaixo efetua a proeza:

1
2
3
4
5
6
Private Sub Workbook_Open()
    Sheet1.Protect password:="test", DrawingObjects:=True, _
        contents:=True, Scenarios:=True, _
        userinterfaceonly:=True
    Sheet1.EnableAutoFilter = True
End Sub

Resumidamente, o código efetua a proteção tradicionalmente e habilita o autofiltro logo em seguida.

A referência e código completo podem ser vistos neste link:

http://office.microsoft.com/en-us/excel/HA010982701033.aspx

Abraços

Tomás Vásquez
www.tomasvasquez.com.br