Tomás Vásquez – Blog

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.

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!

   Excel, Microsoft Office    Add a comment

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, Microsoft Office    Add a comment

VBA – Filtrar dados no ListBox (Tela de Pesquisa) – Parte 2

Há algum tempo, publiquei um exemplo de código VBA que permitia efetuar um filtro em uma lista de itens em um ListBox (UserForm), mas especificamente neste link:

http://www.tomasvasquez.com.br/blog/microsoft-office/vba-como-filtrar-dados-no-listbox

Bom, nas discussões no nosso fórum, acabamos por aperfeiçoar o código para algo mais abrangente. A boa notícia é que o código continua bem genérico, pode ser facilmente acoplado em qualquer planilha. A notícia ótima é que os critérios de filtro ficaram bem mais completos.

O código agora permite selecionar o campo no qual será aplicado o filtro, o valor do filtro e mostra todos as colunas da tabela. Abaixo segue como ficou a aparência do formulário:

O código VBA

Entrando um pouco em detalhes a respeito do funcionamento do código e como adaptá-lo a sua planilha, seguem algumas considerações.

Variáveis

Private Const NomePlanilha As String = “Fornecedores”
Private Const LinhaCabecalho As Integer = 1

NomePlanilha contempla o nome da planilha ao qual será aplicado o filtro. Altere para o nome da sua planilha.

LinhaCabecalho representa qual a linha da sua planilha contém o cabeçalho da tabela. É nele que se baseiam todos as outras funções.

Funções

PreencheCampos – Preenche o ComboBox com o nome dos cabeçalhos

PreencheCabecalho – Função auxiliar para colocar no ListBox de resultado o cabeçalho dos valores

PreencheLista – Preenche o ListBox de resultado considerando os valores de filtro

Download da planilha

Filtra_ListBox_VBA_Custom.zip

Bom proveito!

   Desenvolvimento, Excel, Microsoft Office, VBA    Add a comment

Excel – Abrindos arquivos em uma nova instância do Excel

Por padrão, as versões mais atuais do Excel abrem novos arquivos na mesma instância do aplicativo, caso já exista um aberto. É o comportamento padrão quando você dá um duplo-clique em arquivo pelo Windows Explorer.

Porém, dependendo do comportamento do determinado arquivo, é desejável que ele seja aberto e uma nova instância. Isso é relativamente simples em VBA. Porém, seria interessante ter o mesmo comportamento de forma mais tradicional. Issoé possível, seguindo os seguintes passos:

Passo 1: Com o Windows Explorer aberto, vá em Ferramentas->Opções de pasta. Nessa janela, vá para a aba Tipos de Arquivo e procure pela extensão XLS. Selecione e clique no botão Avançado.

Passo 2: Com a janela Editar tipo de arquivo aberta, clique no botão novo.

Passo 3: Na janela Nova ação, selecione um nome para esta. Como sugestão inicial, coloque “Abrir em uma nova instância do Excel” já que é isto mesmo que a ação fará e no campo Aplicativo usado para executar a ação, coloque o seguinte comando:

“C:\Arquivos de programas\Microsoft Office\OFFICE11\EXCEL.EXE” “%1″

É importante que a primeira parte do comando aponte para a pasta onde está instalado o office no seu computador. O comando acima faz referência a instalação padrão do Excel 2003.

Passo 4: Agora é fazer o teste. Abra o Windows Explorer e aponte para qualquer pasta que contenha um arquivo em Excel (.xls) e clique com o botão direito para que a ação que acabamos de configurar está presente no  menu:

Porém, para efetuar o teste com clareza, deixe aberto antes de executar a ação acima algum outro arquivo do Excel. Agora basta conferir pelo gerenciador de tarefas (Ctrl+Shift+Esc) que quando este comando é executado, uma nova instância do Excel é aberta.

Para remover o comportamento, basta ir até o passo 2 deste tutorial, mas ao invés de clicar em novo, selecione a ação que acabamos de criar em clique em remover.

Bom proveito!

Referências:

http://www.vadriano.com/excel-vb/

   Excel, Microsoft Office    Add a comment