Introdução:

Neste Módulo aprenderemos a trabalhar com uma lista de dados no Excel 2010. Uma lista de dados pode ser uma planilha criada diretamente no Excel 2010 ou dados que foram obtidos a partir de uma fonte externa, tal como uma tabela de um banco de dados do Microsoft Access ou dados de um arquivo do tipo texto, em um formato estruturado, que possa se reconhecido e importado como uma tabela de dados.

Uma lista contém dados relacionados a um determinado assunto. Por exemplo, você poderia ter uma lista com dados sobre os funcionários da sua empresa ou com a lista dos pedidos efetuados em um determinado período. Uma lista é como se fosse uma tabela de um banco de dados, onde cada coluna representa um campo da tabela e cada linha é um registro. 

Existem situações onde os dados que você precisa analisar não estão no formato de uma planilha do Excel 2010. Por exemplo, estes dados podem estar em um arquivo do tipo texto, no formato de texto delimitado ou separado por vírgula, ou podem estar em uma tabela do de um banco de dados do Access ou do SQL Server. Nestas situações você deseja importar estes dados para dentro de uma planilha do Excel 2010, para poder utilizar as funcionalidades de análise de dados do Excel. Este é justamente o assunto desta e das próximas duas lições: Importação de Dados no Excel 2010. 

Nesta lição vamos tratar sobre a importação de dados a partir de arquivos do tipo texto. Existem dois formatos padrão quando se trata de arquivos do tipo texto. Antes de aprendermos a fazer a importação para o Excel 2010, vamos apresentar os dois formatos de texto mais utilizados.

Também é possível acessar dados que estão em outros formatos de arquivos diferentes do formato de arquivo de texto. Nos Módulos 3 e 4 veremos que é possível acessar dados diretamente de um banco de dados do Microsoft Access, do SQL Server ou, até mesmo, dados que estão residentes em um computador de grande porte.

Neste lição você aprenderá acessar dados que estão em arquivos do tipo .dbf. Arquivos .dbf são arquivos no formato do Dbase III, Dbase IV ou Dbase V. O Dbase foi um dos gerenciadores de banco de dados mais utilizado  na época do bom e velho MS-DOS. O Clipper, uma das linguagens mais utilizadas para a criação de aplicações de banco de dados, na época do MS-DOS, também trabalhava com arquivo no formato .dbf. Por isso é bastante provável que, em algum momento, você venha a se deparar com dados no formato .dbf.

Conforme veremos nesta lição, é extremamente simples acessar dados de arquivos .dbf a partir do Excel 2010. Vamos, novamente, utilizar um exemplo prático. Abriremos o Excel 2010 e em seguida vamos abrir o arquivo Pedidos.dbf, que está disponível na pasta de exemplos que acompanha este livro. Em seguida vamos salvar os dados para o formato do Excel, utilizando a opção Arquivo -> Salvar Como. Então, mãos à obra.

Em determinadas situações pode ser necessário acessar, a partir do Excel 2010, dados de uma ou mais tabelas que estão em um banco de dados do Microsoft Access.

Em versões até o Excel 2003 não tínhamos como de dentro do Excel, importar dados de um banco de dados do Access. Tínhamos que entrar no Access e exportar os dados para o formato do Excel. A partir do Excel 2007 esta realidade mudou. Podemos, de dentro do Excel, importar dados de uma ou mais tabelas de um ou mais banco de dados do Access.

Nesta lição vamos fazer dois exemplos. No primeiro vamos abrir um banco de dados do Access e, de dentro do banco de dados, vamos exportar os dados de uma tabela do Access para uma planilha do Excel. Depois faremos um exemplo onde, diretamente de dentro de uma planilha do Excel, vamos importar dados de uma tabela de um banco de dados do Access.

É importante salientar que, usando este procedimento, nenhum vínculo será mantido entre os dados que estão no Microsoft Access e a planilha com os dados no Excel. Em outras palavras, ao alterarmos os dados originais, os dados da planilha não serão alterados e vice-versa, ou seja, são duas cópias completamente independentes dos dados.

Nota: Para os exemplos desta lição vamos utilizar os dados do banco de dados Northwind.accdb, o qual está disponível na pasta de exemplos que acompanha este curso.

Veremos neste primeiro exemplo como exportar dados de uma tabela do Microsoft Access para uma planilha do Excel 2010. Quando os dados são exportados, uma nova planilha será criada. Se uma planilha, com o mesmo nome da que está sendo criada, já existir, o Access perguntará se desejamos excluir a planilha existente e criar uma nova. Não é possível manter a planilha existente e apenas acrescentar os dados que estão sendo exportados. Você pode exportar dados de uma tabela ou os resultados de uma consulta, diretamente para uma planilha do Excel. Ao exportar os dados de uma consulta, o Access primeiro executa a consulta, obtém os dados resultantes da consulta e exporta estes dados para uma planilha do Excel.

Para não fugir da nossa metodológica, vamos utilizar um exemplo prático. Abriremos o banco de dados Northwind.accdb, o qual está na pasta de exemplos que acompanha este curso. Em seguida exportaremos os dados da tabela Pedidos para a planilha Exemplo 03.xlsx, na pasta de exemplos que acompanha o curso. Lembrando, mais uma vez, que estou utilizando a pasta Documentos\Exemplos do Curso de Excel 2010 Avançado. Você deve utilizar a pasta para a qual você baixou e descompactou os arquivos de exemplos que acompanham este livro, conforme orientações que estão na Introdução do livro. Então, mãos à obra.

Nota: Os links para baixar os arquivos utilizados na lição estão indicados na vídeo aula desta lição.

Uma vez que temos uma Lista de dados no Excel 2010, quer esta lista tenha sido importada ou digitada diretamente no Excel, podemos realizar uma série de operações sobre os dados da lista. Podemos classificar a lista de acordo com uma ou mais colunas, podemos aplicar filtros na lista, podemos criar listas ordenadas personalizadas e assim por diante. 

Quando classificamos uma lista, o Excel reordena as linhas da lista de acordo com o conteúdo da coluna (ou das colunas) selecionadas para ordenação. Por exemplo, se você define que a lista seja ordenada em ordem crescente, pelo campo PaísDeDestino, as linhas serão reordenadas para ficar em ordem alfabética crescente do campo PaísDeDestino.

Nesta e na próxima lição aprenderemos a fazer a classificação (ordenação) de uma lista de acordo com uma ou mais colunas de dados.

Vamos utilizar um exemplo prático para aprender a fazer a ordenação de listas usando com o  Excel 2010. Utilizaremos a lista de Pedidos contida na planilha Exemplo 03.xlsx, a qual foi criada em um dos exemplos das lições anteriores. 

Nesta lição aprenderemos a ordenar por uma coluna de cada vez. 

Na próxima lição aprenderemos a classificar por duas ou mais colunas, faremos algumas observações em relação aos cuidados que devem ser tomados, quando classificamos listas cujos valores são obtidos a partir de fórmulas calculadas. Veremos mais algumas observações sobre a classificação de listas.

Nessa lição veremos um exemplo prático de como classificar uma Lista com base em duas ou mais colunas. 

Por exemplo, você pode querer classificar a lista primeiro pelo País e, dentro de um mesmo País, pela Cidade de destino. Neste caso basta fazer a classificação da lista definindo os campos de classificação nesta ordem: primeiro o País e depois a Cidade. 

Você também poderia classificar pelo País, depois pela Cidade e, dentro de cada Cidade pelo Número do pedido ou pela Data do pedido.

Agora vamos fazer um exemplo prático, onde vamos classificar uma listagem de Pedidos com base nas colunas País, Cidade e Número do pedido.

O que Aconteceu com as Listas do Excel?

Desde o Excel 2007 que não é possível encontrar o recurso de lista do Excel nas faixas de comandos do Excel. 

O recurso de lista do Excel ainda existe, mas foi renomeado. 

As listas do Excel, desde o Excel 2007 são chamadas de Tabelas do Excel.

Vamos ver um exemplo prático nesta lição!

Como foi mostrado na lição anterior as Listas no Excel, a partir do Excel 2007, passaram a ser chamadas de Tabelas. 

Quando trabalhamos com muitos dados em uma planilha, com milhares de linhas, pode haver a necessidade de “filtrar” tais dados, para que sejam exibidas somente as linhas onde um ou mais campos atendam determinados critérios de pesquisa/filtragem.

Por exemplo, podemos filtrar uma tabela (lista) de Pedidos para que sejam exibidos apenas os pedidos para o Brasil; ou apenas os pedidos em que o país é Brasil e a Cidade é São Paulo. Podemos avançar mais ainda na definição de critérios de pesquisa. 

Por exemplo, podemos fazer com que sejam exibidos apenas os pedidos para o Brasil, cidade de São Paulo ou Campinas, para um determinado ano. Observe que, neste último exemplo, estamos definindo três critérios de filtragem:

País = Brasil

Cidade= São Paulo OU Cidade=Campinas

Ano do pedido

Filtrar é uma maneira rápida e prática para que possamos trabalhar apenas com um conjunto reduzido de linhas, com base em um ou mais critérios de filtragem. Quando aplicamos um filtro a uma tabela, somente serão exibidas as linhas que atenderem aos critérios especificados. Uma vez removidos os filtros, todas as linhas voltarão a ser exibidas. 

 Com o Excel 2010, dispomos de diversas ferramentas de filtragem, dentre as quais se destacam as seguintes:

• AutoFiltro: Aprenderemos a utilizar esta ferramenta nas próximas três lições deste capítulo.

• Filtro Avançado: Utilizado para a definição de critérios mais complexos. Aprenderemos a usar esta ferramenta nas demais lições deste Módulo, depois das lições sobre AutoFiltro.

Nesta lição começaremos o nosso estudo sobre o uso do recurso de AutoFiltro, através de Exemplos Práticos, Passo a Passo.

A opção AutoFiltro é uma maneira rápida e prática para aplicar critérios básicos de filtragem em uma tabela de dados.

Nesta lição aprenderemos os fundamentos básicos do uso de AutoFiltro. 

Mais uma vez aprenderemos através de um exemplo prático, onde iremos, passo a passo, usando os recursos disponíveis e observando os resultados obtidos. 

Vamos a um exemplo prático sobre a criação e utilização do recurso de AutoFiltro.

Nesta lição aprenderemos a utilizar alguns recursos mais sofisticados de AutoFiltro. 

Aprenderemos a fazer a filtragem em campos numéricos, especificando uma faixa de valores. 

Por exemplo, Frete maior do que R$ 10,00 e menor do que R$ 20,00. Ou especificar intervalos de datas, como por exemplo, todos os pedidos para o primeiro trimestre de 2010, enviados para o Brasil. 

A metodologia adotada será sempre a mesma: aprenderemos através de um exemplo prático, passo a passo. Vamos a um exemplo prático sobre a criação e utilização de recursos avançados do AutoFiltro.

Nesta lição finalizaremos o assunto AutoFiltro. 

Para tal veremos mais alguns detalhes sobre o uso de filtros personalizados, a opção de exibir os “n” primeiros ou n% primeiros registros, bem como opções de filtro personalizado para campos do tipo texto. 

A metodologia adotada será sempre a mesma: aprenderemos através de um exemplo prático, passo a passo.

Pode haver situações em que você necessita localizar, rapidamente, um determinado registro. Por exemplo, pode ser que você queira localizar o pedido 10350 ou um pedido enviado para São Paulo em uma data específica, ou um pedido com um valor específico de frete. Em resumo, você conhece o valor de um ou mais campos e deseja, rapidamente, localizar a linha correspondente. 

Nestas situações podemos utilizar o recurso de Formulário, o qual permite a definição de valores em um ou mais campos e a localização da linha que atende os critérios definidos.

Se você digitar valores em mais de um campo, os valores serão ligados por um operador E, ou seja, todos os critérios devem ser atendidos para que a linha seja localizada.

Por exemplo, se você digitar Brasil no campo País e São Paulo no campo cidade, será localizada a primeira linha que atender aos dois critérios. Se a linha atender apenas a um dos critérios, a pesquisa continua até a primeira linha que atender os dois critérios. 

O mesmo é válido se você definir critérios em três ou mais campos, ou seja, todos terão que ser atendidos para que uma linha seja localizada e exibida. Por padrão, o botão de Acesso ao Formulário de pesquisa não é exibido nas faixas de opção do Excel e nem na guia de Acesso Rápido. 

Vamos primeiro ativar este recurso, adicionando o botão de acesso na Barra de Ferramentas de Acesso Rápido, que é a barra com os comandos principais, bem no canto superior esquerdo da janela do Excel. 

A seguir coloco um exemplo prático, passo a passo, onde mostro como adicionar o Botão Formulário.

Nesta lição trataremos de um assunto teórico e que muitas vezes causa confusão: a diferença e como funciona os operadores lógicos “E” e “OU”.

Veremos que existem diferenças fundamentais entre estes dois operadores e que é muito importante entendermos estas diferenças para que possamos obter os resultados desejados quando estamos definindo critérios de pesquisa em uma lista de valores no Excel. 

Os conceitos vistos nesta lição também são válidos para o uso destes operadores em consultas do Access ou em qualquer outro programa onde possam ser utilizados os operadores “E” e “OU”, como por exemplo em mecanismos de pesquisa na Internet, como por exemplo o Google (www.google.com) o Yahoo (www.yahoo.com) e o Bing (www.bing.com).

Nas lições anteriores aprendemos a usar o comando AutoFiltro em conjunto com a opção Personalizar AutoFiltro, para definir critérios básicos de filtragem. A partir desta lição veremos uma série de exemplos com a opção Filtro Avançado. 

Com esta opção podemos definir critérios de filtragem mais elaborados, onde são envolvidas mais de duas colunas ou onde podemos definir três ou mais valores de filtragem para uma única coluna.

Por exemplo, podemos definir um critério de filtragem onde sejam exibidos apenas os pedidos para o “Brasil”, “Alemanha”, “Argentina”, “EUA” e “França”. Observe que neste caso temos que definir cinco valores diferentes, como critérios de filtragem, no campo País. Este tipo de filtro até é possível de ser feito com a opção AutoFiltro, mas não de uma maneira que possa ser fácil e rapidamente modificada. 

O uso de Faixas de Critérios é bem mais flexível, conforme veremos nesta e nas próximas lições deste capítulo.

Outro exemplo poderíamos definir múltiplos critérios em dois ou mais campos. Por exemplo, podemos filtrar apenas os pedidos para o Brasil, para o ano de 2010; mais todos os pedidos para a Argentina com frete maior do que R$ 20,00 e todos os pedidos para a França onde a cidade é Paris e o ano é 2011. Este tipo de filtragem, com diferentes critérios em diferentes campos, sendo aplicados simultaneamente, não é possível de ser aplicado, usando a opção AutoFiltro.

Com o comando Filtro Avançado é possível fazer com que o Excel 2010 faça uma cópia das linhas filtradas para outro local da planilha ou para outra planilha. Esta opção é muito útil quando você quer, além de filtrar os dados, obter uma cópia dos dados filtrados para efetuar análises, cálculos ou consolidações.

Nesta lição vamos criar um exemplo, passo a passo, onde criaremos uma faixa de critérios e depois iremos utilizá-la em conjunto com o comando Filtro Avançado. 

Em seguida modificaremos os critérios para obter um novo conjunto de linhas.

Nesta lição veremos um exemplo prático do uso de critérios de texto em faixas de critérios, para filtrar os registros de uma lista. Existem algumas regras que devem ser observadas quando fazemos a pesquisa em campos do tipo texto, conforme descrito a seguir:

Regra 1: Se definirmos uma única letra, por exemplo, no campo País, serão selecionados todos os registros em que o país iniciar pela letra especificada. Por exemplo, se definirmos o critério A para o campo País, serão selecionados os registros para Alemanha, Argentina e assim por diante. Não serão selecionados os registros para a Áustria, pois o Excel diferencia com acento e sem acento.

Regra 2: Podemos utilizar os operadores maior do que (>), maior ou igual a (>=), menor do que (<), menor ou igual a (<=) e diferente (< >). Por exemplo, se, no campo País, definirmos o seguinte critério: >=L, serão filtradas todas as linhas onde o nome do País inicie com L ou com todas as demais letras após o L. Assim seriam selecionadas linhas para Índia, Peru, Venezuela, Zaire, etc.

Regra 3: Se você especificar uma palavra como critério, o Excel retornará todas as linhas onde o campo que está sendo filtrado, contenha a palavra digitada. Por exemplo, se em um campo Nome do Cliente, você especificar o critério Silva, serão retornados os seguintes valores: José da Silva, Silvania da Costa, Silvano Pereira, Silvamar Rocha, Carlos Silva, etc.

Regra 4: Se você deseja uma ocorrência exata, deve colocar o texto a ser pesquisado dentro de uma fórmula, no seguinte formato:

=”=texto a ser pesquisado”

Por exemplo, se em um campo Nome do Cliente, você especificar o critério =”=Silva”, serão retornados os seguintes valores: José da Silva, Carlos Silva, etc.; mas não serão retornados os seguintes valores: Silvania da Costa, Silvano Pereira, Silvamar Rocha e assim por diante.

Vamos a um exemplo prático, onde utilizaremos diferentes critérios do tipo texto para filtrar os registros de uma planilha.

Existem situações onde uma simples filtragem com base em um ou mais critérios não é o suficiente para que você possa obter os resultados desejados. 

Por exemplo, em uma lista de pedidos você pode querer filtrar apenas os pedidos onde o valor do pedido é maior do que o valor médio dos pedidos. Em uma situação como esta temos que utilizar critérios calculados.

Existem algumas regras de devem ser observadas quando utilizamos Critérios Calculados em conjunto com o comando Filtro Avançado:

Regra 1: O cabeçalho do critério calculado, acima da célula onde está a fórmula de cálculo não pode ser igual ao nome de uma das colunas da lista, pois senão será gerado um erro. Este cabeçalho pode ficar em branco ou pode conter um texto qualquer, desde que este texto não seja igual ao nome de uma das colunas da lista.

Regra 2: Se a fórmula fizer referência a uma célula fora da lista de valores, esta referência deverá ser absoluta.

Regra 3: Se a fórmula fizer referência a uma célula dentro da lista de valores, esta referência deverá ser relativa.

Para melhor entender o uso de Critérios Calculados vamos utilizar um exemplo prático. Neste exemplo utilizaremos dois conjuntos de critérios diferentes, um para filtrar apenas os pedidos com valores maiores do que o valor médio dos pedidos e outro para filtrar os pedidos onde o número de dias, entre a data do pedido e a data do envio for maior do que 15

Outra funcionalidade bastante útil do Excel 2010 (também disponível nas versões anteriores) é a possibilidade de, rapidamente, criarmos subtotais para grupos de dados em uma planilha. Por exemplo, podemos criar subtotais de vendas por País, dentro de cada País podemos criar subtotais por cidade. Ou poderíamos criar subtotais por vendedor ou cliente. 

A criação de subtotais é uma maneira rápida de agruparmos dados, calcular valores para um determinado grupo (subtotais) e compararmos o desempenho de diferentes grupos. Por exemplo, com o cálculo de subtotais por vendedor, podemos comparar, facilmente, o desempenho de cada vendedor.

Nesta lição veremos um exemplo básico do uso de subtotais. Mais adiante, nos módulos 3 e 4 voltaremos ao assunto de subtotais, quando falaremos das ferramentas de análise de dados e simulação de cenários do Excel 2010. 

Nesta lição veremos um exemplo simples de como criar subtotais por vendedor.

Nesta lição indicarei alguns exercícios para que o amigo leitor possa fixar os conceitos apresentados neste módulo. Em caso de dúvidas na resolução de um destes exercícios entre em contato pelo e-mail: webmaster@juliobattisti.com.br.

Exercício 01: Abrir o arquivo M1Ex1.xlsx, o qual está na pasta de exemplos que acompanha este livro e classificar a listagem primeiro pelo País e, dentro do País pela Cidade e dentro da cidade pela coluna DtDoPedido.

Exercício 02: Abrir o arquivo M1Ex2.xlsx, o qual está na pasta de exemplos que acompanha este livro e definir Subtotais por Cliente. Em seguida exibir apenas os subtotais para cada cliente e o subtotal geral.

Exercício 03: Abrir o arquivo M1Ex3.xlsx, o qual está na pasta de exemplos que acompanha este livro e usar o recurso de AutoFiltro para aplicar critérios de pesquisa pelo País, Cidade e Código do Cliente e Valor de Frete. Pesquise apenas os pedidos para a França para o ano de 2010. Volte a exibir todos os pedidos. Agora pesquise todos os pedidos para os clientes HILAA e WELLI.

Exercício 04: Abrir o arquivo M1Ex4.xlsx, o qual está na pasta de exemplos que acompanha este livro e usar o recurso de AutoFiltro para aplicar critérios de pesquisa para a coluna País (do tipo texto), filtrando apenas os pedidos para Alemanha e Brasil. Voltar a exibir todos os resultados. Aplicar filtros para que sejam exibidos apenas os 50 registros de maior valor do frete.

Conclusão: Neste Capítulo você aprendeu a importar e a exportar dados de/para o Excel 2010. Também aprendeu a classificar planilhas por um ou mais critérios. Na sequencia mostrei como filtrar os registros de uma lista. Finalizamos o Módulo aprendendo sobre Filtros Avançados e subtotais.

Módulo 1 – Trabalhando com Listas de dados    

Lição 01:         Introdução e Conteúdo do Módulo 1.

Lição 02:         Importação de Dados – Parte 1.       

Lição 03:         Importação de Dados – Parte 2.       

Lição 04:         Importação de Dados – Parte 3.       

Lição 05:         Ordenando Listas – Parte 1.

Lição 06:         Ordenando Listas – Parte 2.

Lição 07:         Criando Listas de Ordenação Personalizadas.

Lição 08:         Filtrando Dados em uma Lista – Introdução e Conceitos.

Lição 09:         Filtrando Dados – O Comando AutoFiltro – Parte 1.

Lição 10:         Filtrando Dados – O Comando AutoFiltro – Parte 2.

Lição 11:         Filtrando Dados – O Comando AutoFiltro – Parte 3.

Lição 12:         Localizando Registros Individuais: O Recurso Formulário.

Lição 13:         Operadores de Filtragem: A Diferença Entre “E” e “OU”.

Lição 14:         Definindo e Usando Faixas de Critérios – Introdução.

Lição 15:         O Primeiro Exemplo Usando Faixas de Critérios.

Lição 16:         Usando Critérios de Texto.

Lição 17:         Usando Critérios Calculados.

Lição 18:         Usando Subtotais para Análise de Dados - Introdução.

Lição 19:         Exercícios com Faixas de Critérios.

Lição 20:         Resumo do Módulo 1.

Introdução:

Neste módulo veremos mais funções para trabalhar com listas de dados. 

Iniciaremos o módulo aprendendo mais detalhes sobre a utilização de subtotais para efetuar cálculos e consolidações em uma lista de dados. O uso de subtotais é uma opção bastante interessante quando precisamos, rapidamente, obter consolidações dos dados em uma planilha. Veremos exemplos práticas para entender todas as opções de subtotais disponíveis.

Em seguida aprenderemos a utilizar algumas funções específicas para análise de dados em tabelas do Excel. Aprenderemos a utilizar as seguintes funções:

• CONT.SE( )
• SOMASE( )
• BDMÉDIA( )
• BDCONTAR( )
• BDCONTARA( )
• BDEXTRAIR( )
• BDMÁX( )
• BDMÍN( )
• BDMULTIPL( )
• BDEST( )
• BDDESVPA( )
• BDSOMA( )
• BDVAREST( )
• BDVARP( )

Na sequencia falarei sobre validação de dados e acesso aos dados da Internet a partir do Excel. Conforme veremos neste módulo, é possível acessar dados de uma página da Internet, a partir de uma planilha do Excel. É possível, inclusive, manter a planilha sincronizada com as alterações que forem feitas na página da Internet.

Em seguida você aprenderá a utilizar o Microsoft Query - MSQuery. O MSQuery é uma importante ferramenta de pesquisa, a qual permite que, a partir do Excel, façamos pesquisas em uma grande variedade de fontes e formatos de dados. Com o MSQuery podemos acessar dados via drive ODBC ou quaisquer umas das tecnologias suportadas pela Microsoft, tais como OLE-DB e outras.

Nota: Veremos exemplos práticos de acesso e pesquisa em fontes de dados externas, usando o Microsoft Query, nas lições deste Módulo. Nestes exemplos práticos aprenderemos a acessar os dados via uma fonte ODBC.

Na Lição 18 do Módulo 1 aprendemos sobre a utilização básica de Subtotais em listas de dados. Foi mais mesmo para entender o conceito e o básico sobre o recurso de Subtotais. Nesta lição veremos mais alguns detalhes relacionados com subtotais. Aprenderemos a aplicar estes detalhes utilizando um exemplo prático, passo a passo, como temos feito ao longo deste Livro.

No exemplo prático desta lição aprenderemos os seguintes detalhes sobre Subtotais:

1 - Criar subtotais em dois ou mais campos.

2 - Usar outros cálculos de subtotais além da Soma.

A partir desta lição, até a Lição 6 estudaremos uma série de funções utilizadas para analisar dados em uma lista do Excel. Por exemplo, podemos usar a função CONT.SE para detectar quantas células não vazias, dentro de uma faixa de células, atendem a um determinado critério. 

A função CONT.SE retorna um número o qual indica quantas células contém um valor que corresponde ao critério especificado para a função. 

Podemos usar a função SOMASE para efetuar uma soma condicional, onde os valores de uma coluna são ou não acrescentados a soma, com base nos valores de outra coluna. Por exemplo, poderíamos efetuar uma soma condicional para obter o total de vendas apenas para o mês de Outubro ou somente as vendas feitas para um determinado cliente ou as vendas feitas para um determinado país e assim por diante.

Nesta lição estudaremos as seguintes funções:

• CONT.SE( )
• SOMASE( )
• BDMÉDIA( )

Nesta lição estudaremos as seguintes funções:

• BDCONTAR( )
• BDCONTARA( )
• BDEXTRAIR( )

Nesta lição estudaremos as seguintes funções:

• BDMÁX( )
• BDMÍN( )
• BDMULTIPL( )
• BDEST( )

VALIDAÇÃO DE DADOS:

Com a Validação de Dados podemos limitar os valores que são aceitos em uma célula ou faixa de células. Por exemplo, podemos limitar os valores de uma coluna para que não sejam aceitos valores maiores do que 10000. Outro exemplo: Podemos limitar as entradas de uma coluna Cidade de tal maneira que somente sejam aceitos valores de uma determinada lista de cidades.

Além dos valores aceitos podemos definir o tipo de dados aceitos. Por exemplo, podemos definir que os valores em uma faixa de células devem ser obrigatoriamente numéricos. Neste caso, se o usuário digitar um texto, o Excel não aceita a entrada e emite uma mensagem de erro.

Ao definirmos uma lista de valores que serão aceitos, como no exemplo da lista de cidades do primeiro parágrafo, o Excel cria uma Caixa de combinação, na qual podemos selecionar um valor da lista, sem que seja necessária a digitação do valor desejado. Esta funcionalidade facilita bastante a digitação, além de evitar que sejam aceitos valores que não estão na lista.

Nesta lição aprenderemos a definir critérios para a validação dos dados de entrada. Faremos isso utilizando um exemplo prático, passo a passo, só para “variar” um pouco.

ACESSANDO DADOS DA INTERNET - PARTE 1:

Com o Excel podemos obter dados diretamente de uma página em um site da Internet. Por exemplo, você pode configurar uma planilha para que obtenha dados com cotações de ações, diretamente a partir de um site da Internet que oferece este tipo de serviço. Você também pode configurar uma periodicidade para atualização dos dados, como por exemplo, de 5 em 5 minutos. Com isso o Excel volta na página Web informada e baixa a versão atualizada dos dados, fazendo com que os dados da planilha sejam periodicamente atualizados.

Os dados obtidos a partir da Internet são considerados um Intervalo de dados externos, ou seja, são exibidos na planilha, mas a fonte de dados é externa ao Excel. O uso típico para este tipo de funcionalidade é quando você deseja acompanhar, automaticamente, a variação em índices como cotação de ações, variação das bolsas ou outro índice de interesse.

ACESSANDO DADOS DA INTERNET - PARTE 2:

Nesta lição aprenderemos a criar uma nova consulta, a qual busca dados diretamente de um site da Internet e exibe estes dados em uma planilha do Excel. Também aprenderemos a configurar o intervalo de atualização bem como outras propriedades da consulta.

Vamos acompanhar um exemplo passo a passo da criação de consultas baseadas em dados da Internet. Acessaremos dados do seguinte site:

http://www.portalbrasil.net/2013/economia/indices_poupanca_diaria.htm

Este site traz uma série de indicadores, tais como o Índice de Poupança, Dólar, INPC, IPC, etc. Faremos uma consulta baseada no endereço acima. Esta consulta retornará os índices de Poupança para o ano de  2013. Em seguida iremos configurar o intervalo de atualização destes dados.

O MSQUERY:

O Microsoft Query, que de agora em diante chamaremos apenas de MSQuery, é uma ferramenta que facilita a conexão do Excel com fontes externas de dados, que atendam ao padrão do conexão ODBC - Open Database Connectivity. 

Por exemplo, você pode utilizar o MSQuery para fazer, de dentro do Excel, uma pesquisa em tabelas de um banco de dados do Microsoft Access, especificar critérios de filtragem e ordenação e retornar o resultado da consulta para uma planilha do Excel. Você poderá fazer cálculos, análises, totalizações e gráficos com os dados retornados pelo MSQuery. Nesta lição veremos os conceitos teóricos relacionados com o MSQuery e nas próximos lições veremos exemplos práticos, passo a passo.

Nota: O MSQuery pode ou não ter sido instalado quando o Office foi instalado. Caso ele não tenha sido instalado, você pode instalar o MSQuery a partir do DVD original de instalação do Office 2010. 

O MSQuery é um programa que traz dados de fontes externas para outros programas do Microsoft Office - principalmente para o Microsoft Excel 2010. Se você usar o MSQuery para recuperar dados de arquivos e bancos de dados corporativos, não será preciso digitar novamente os dados a serem analisados no Excel 2010. Também é possível atualizar os relatórios e resumos do Excel 2010 automaticamente a partir do banco de dados da fonte original sempre que o banco de dados for atualizado com novas informações.

Observe as vantagens em se utilizar o Microsoft Query. O que acontece, normalmente, nas empresas é que os relatórios são impressos a partir dos sistemas da empresa. A pessoa responsável por analisar os dados do relatório impresso, digita-os novamente no Excel 2010 para poder usar as ferramentas de análise que veremos no Módulo 3. Porém estes passos são desnecessários, trabalhosos e podem gerar erros na hora da digitação. Já com o MSQuery é possível retornar os dados diretamente do banco de dados da empresa, para uma planilha do Excel 2010 e, facilmente, atualizar estes dados, quando houver mudanças no banco de dados.

DEFININDO A FONTE DE DADOS:

Nesta lição aprenderemos a definir uma fonte de dados. 

Como exemplo, definiremos uma fonte de dados para acessar os dados do arquivo ExQuery.mdb, ou seja, dados de um arquivo no formato do Microsoft Access. 

A exemplo do que foi feito em outras lições, aprenderemos a definir a fonte de dados através da utilização de um exemplo prático.

CRIAÇÃO DE CONSULTAS BÁSICAS:

Na lição anterior definimos uma fonte de dados, fonte esta que nos permite acessar os dados das tabelas do banco de dados ExQuery.mdb, o qual está na pasta de exemplos que acompanha este livro. 

Nesta lição aprenderemos a criar uma consulta básica, baseada nesta fonte de dados. 

Nas próximas lições aprenderemos outras maneiras de criar uma fonte de dados e também a criar consultas mais elaboradas. Nesta Lição, utilizaremos o Assistente de consultas, indicado na Figura 2.30, no capítulo anterior. 

Em outros exemplos deste capítulo também aprenderemos a utilizar diretamente o Microsoft Query, sem o assistente de consultas, para criar consultas mais elaboradas.

DEFINIÇÃO DE CRITÉRIOS EM CONSULTAS:

Nesta lição e nas próximas duas lições aprenderemos a definir critérios em consultas. 

Nesta lição falaremos sobre os operadores que podemos utilizar em critérios de pesquisa, bem como alguns exemplos teóricos. 

A partir da próxima lição, veremos exemplos práticos de aplicação dos conceitos vistos nesta lição.

DEFINIÇÃO DE CRITÉRIOS EM CONSULTAS - PARTE 2:

Nesta lição utilizaremos o Microsoft Query para criar um consulta onde definiremos alguns critérios de pesquisa. 

Faremos a consulta na tabela Pedidos do arquivo ExQuery.mdb, que está na pasta de exemplos que acompanha este livro. 

Após definirmos alguns critérios de pesquisa, executaremos a consulta e os dados serão retornados para uma planilha do Excel. 

Salvaremos a planilha com os resultados obtidos.

A INTERFACE DO MSQUERY:

Neste item vamos editar a consulta criada na Lição anterior. Vamos alterar alguns critérios. 

Também vamos aprender a usar algumas funcionalidades da interface do MSQuery, como por exemplo, adicionar ou excluir colunas, definir a ordenação dos resultados e assim por diante.

TOTALIZAÇÃO DE DADOS EM CONSULTAS:

Nesta lição vamos editar a consulta criada na Lição anterior. Vamos alterar alguns critérios. 

Também aprenderemos a utilizar mais algumas funcionalidades da interface do MSQuery. 

TOTALIZAÇÃO DE DADOS EM CONSULTAS - PARTE 2:

Nesta lição vamos editar a consulta criada na Lição anterior. 

Agora vamos fazer o cálculo do total de vendas por PaísDeDestino e, dentro do PaísDeDestino, por Cidade. Para que possamos fazer estas agregações, é importante que os campos estejam nesta posição, ou seja, primeiro (mais à esquerda) o PaísDeDestino e depois, (a sua direita), o campo CidadeDeDestino. Não vamos alterar os filtros da consulta.

CONSULTAS PARAMETRIZADAS:

Neste item vamos editar a consulta criada na Lição anterior. 

Aprenderemos a criar uma consulta parametrizada. 

Uma consulta parametrizada solicita que seja digitado um ou mais valores (os parâmetros), toda vez que a consulta for executada. 

Por exemplo, podemos definir um parâmetro para o campo PaísDeDestino. Toda vez que a consulta for executada o MSQuery solicita que você digite um valor para o parâmetro no campo PaísDeDestino. 

Vamos supor que você digitou Brasil. O valor digitado será utilizado para filtrar o resultado da consulta e serão exibidos apenas os pedidos, no nosso exemplo, para o Brasil. 

Com isso a consulta fica mais dinâmica, não precisamos alterar diretamente os critérios, basta fornecer um valor diferente para os parâmetros, cada vez que a consulta for executada.

EXERCÍCIOS PROPOSTOS:

Vamos propor alguns exercícios de Revisão para que você possa consolidar, ainda mais, os tópicos apresentados neste Módulo.

CONCLUSÃO DO MÓDULO 2:

Neste Módulo 2 aprendemos a utilizar as funções para pesquisa em tabelas de dados no Excel 2010, bem como a criação de Consultas com Base em dados da Internet. 

Na sequencia aprendemos a utilizar o MSQuery para criar consultas baseadas em dados externos e retornar os resultados para o Microsoft Excel 2010. 

Capítulo 2 – Mais sobre Listas de Dados, Exportação e Importação de Dados 

Lição 01 – Introdução

Lição 02 – Subtotais - Exemplo Prático e Mais Opções

Lição 03  – Funções Para Análise de Dados - Parte I

Lição 04  – Funções Para Análise de Dados - Parte II

Lição 05  – Funções Para Análise de Dados - Parte III

Lição 06  – Funções Para Análise de Dados - Parte IV

Lição 07  – Definindo Critérios Para Validação de Dados

Lição 08  – Acessando Dados da Internet - Conceitos       

Lição 09  – Consultas Baseadas na Internet - Exemplos

Lição 10  – MSQuery - Uma Ferramenta de Pesquisa

Lição 11  – MSQuery - Definindo a Fonte de Dados

Lição 12  – MSQuery - Criação de Consultas Básicas        

Lição 13  – MSQuery - Definição de Critérios em Consultas - Parte I

Lição 14  – MSQuery - Definição de Critérios em Consultas - Parte II

Lição 15  – MSQuery - Alterando Consultas

Lição 16  – MSQuery - Totalização de Dados

Lição 17  – MSQuery - Consultas Avançadas

Lição 18  – MSQuery - Consultas Parametrizadas

Lição 19  – Exercícios do Capítulo 2

Lição 20  – Resumo do Capítulo

Introdução:

Neste módulo aprenderemos a trabalhar com Tabelas Dinâmicas. Essa é uma ferramenta muito poderosa, e de fácil utilização, para análise de dados e tomada de decisão. Com o uso de Tabelas Dinâmicas podemos, facilmente, obter múltiplas visões do mesmo conjunto de dados. A pouca utilização é por falta de conhecimento de como utilizar este recurso. As tabelas dinâmicas do Excel 2010 também são conhecidas como Pivot Tables.

Uma Tabela Dinâmica no Excel 2010 é uma tabela interativa que você pode usar para resumir rapidamente grandes quantidades de dados. Você pode girar suas linhas e colunas para ver resumos diferentes dos dados de origem, filtrar os dados por meio da exibição de páginas diferentes ou exibir os detalhes de áreas de interesse. Nas próximas lições veremos alguns exemplos práticos sobre a criação e uso de tabelas dinâmicas. Além das tabelas dinâmicas aprenderemos também a criar, configurar, formatar e utilizar Gráficos Dinâmicos.

Quando devemos utilizar o recurso de Tabelas Dinâmicas?

Você deve utilizar o recurso de tabelas dinâmicas, por exemplo, quando você deseja comparar totais relacionados, especialmente quando você tiver uma longa lista de valores a serem resumidos/totalizados e desejar comparar vários resultados, com base em diferentes critérios de filtragem, de uma maneira rápida, prática e dinâmica. Com este recurso, podemos, facilmente, fazer com que o Excel 2010 faça a classificação, a subtotalização e a totalização dos resultados com base em diferentes critérios de pesquisa e ordenação. No exemplo da Figura 3.1 você pode, facilmente, ver como as vendas de artigos de golfe no terceiro trimestre, na célula F5, superaram as vendas para outro esporte ou trimestre. Como um relatório de tabela dinâmica é interativo, você ou outros usuários podem alterar a exibição dos dados para ver mais detalhes ou calcular resumos diferentes.

Conforme veremos em uma das lições práticas, mais adiante, a criação de uma tabela dinâmica no Excel 2010 é um processo bastante simples. Após ter criado uma tabela dinâmica, podemos utilizar a barra de ferramentas Tabela dinâmica para organizar os dados dentro da tabela, de acordo com nossas necessidades.

Você também pode exibir uma tabela dinâmica no formato recuado, para visualizar todos os valores de resumo do mesmo tipo em uma coluna. Você pode criar gráficos dinâmicos para visualizar os dados graficamente. Você também pode tornar uma tabela dinâmica disponível na Web usando uma lista de tabela dinâmica em uma página da Web. Quando você publica uma tabela dinâmica do Excel 2010 em uma lista de tabela dinâmica, outras pessoas podem visualizar e interagir com os dados a partir de seus navegadores da Web. 

Como criar uma tabela dinâmica:  Para criar uma tabela dinâmica, na prática, nós usaremos o Assistente de tabela dinâmica e gráfico dinâmico como um guia para localizar e especificar os dados de origem que vamos analisar e criar a estrutura da tabela dinâmica. Você pode, em seguida, usar a barra de ferramentas Tabela dinâmica para organizar os dados dentro dessa estrutura. Aprenderemos a criar tabelas dinâmicas a partir de dados de fontes externas, como por exemplo, dados de um banco de dados do Microsoft Access.

Nota: No Módulo 2 foram apresentadas informações detalhadas sobre como acessar dados externos, inclusive utilizando o MSQuery para definir critérios de pesquisa, fazer cálculos e definir parâmetros.

Objetivo: Nesta lição vou apresentar os elementos que compõem uma tabela dinâmica desde a origem dos dados, passando pelo formato de apresentação, até os detalhes sobre cada elemento que compõe uma Tabela Dinâmica no Excel 2010. 

Um pouco mais de teoria e a partir da próxima Lição vamos por a mão na massa, ou seja, a partir da próxima lição já vamos criar nossa primeira Tabela Dinâmica.

Objetivo: Nessa lição vamos criar a nossa primeira Tabela Dinâmica. Vamos utilizar como fonte de dados, uma lista de informações sobre vendas, contida em uma planilha do Excel 2010. A lista é composta dos seguintes campos:

• Número do Pedido
• Nome da Empresa
• Data do Pedido
• Cidade de Destino
• País de Destino
• Ano
• Trimestre
• Vendas

A planilha é composta de 830 registros.

Vamos descobrir onde está o “Dinâmico” das tabelas dinâmicas??

Nessa lição vamos trabalhar com a Tabela Dinâmica criada na lição anterior. Caso você tenha fechado essa planilha, abra-a novamente, a partir do arquivo Tabelas Dinâmicas - Exemplo 01.xlsx, a qual está na pasta de exemplos que acompanha este livro. 

Vamos aprender a filtrar os dados usando o campo de página e também os campos de coluna e de linha.

Objetivo: Nessa lição vamos trabalhar com a Tabela Dinâmica criada nas lições anteriores e para a qual aplicamos alguns filtros na lição anterior (Ano = 1997 e Trimestres 3 e 4). 

Vamos aprender a aplicar novos filtros, de diferentes tipos e em diferentes campos.

Objetivo: Nos exemplos das Lições anteriores apresentamos uma tabela dinâmica com um ou mais campos de página, porém com um único campo para colunas (Trimestre) e um único campo para as linhas (Nome da Empresa). 

Nessa lição aprenderemos a trabalhar com dois ou mais campos de coluna. 

Por exemplo, iremos alterar a tabela dinâmica da lição anterior, retirando o campo Ano da área de campos de página e arrastando ele para a área de campos de coluna. Vamos fazer isso através de um exemplo prático.

Objetivo: Nos Exemplos da Lição anterior aprendemos a adicionar mais um campo na área Rótulos coluna. Com isso obtivemos o total de vendas por cliente, por trimestre, separadamente para cada ano. 

Nessa lição vamos avançar um pouco mais. Vamos alterar os campos que formam as linhas da tabela dinâmica. 

Até agora utilizamos o campo Nome da Empresa como campo para formar as linhas da tabela dinâmica. Nessa lição vamos alterar a estrutura da tabela dinâmica, iremos adicionar os campos País de Destino e Cidade de Destino e depois remover o campo Nome da Empresa, da área de campos de linha. 

Com isso teremos o total por País, dentro do país por cidade, sendo exibidos os totais por trimestre e por ano. A melhor maneira de entender a tabela dinâmica proposta é vê-la em ação, na prática.

Exercícios Propostos:

Serão propostos dois exercícios, para que você possa praticar o que você aprendeu, até agora, sobre Tabelas Dinâmicas no Excel.

Nessa lição vou apresentar a resolução, passo a passo para o Exercício 01, proposto na Lição anterior.

Nessa lição vou apresentar a resolução, passo a passo para o Exercício 02, proposto anteriormente.

Agora que já conhecemos bem como criar e utilizar tabelas dinâmicas, vamos aprender a alterar a formatação e algumas propriedades da tabela dinâmica. 

Nessa lição aprenderemos a utilizar o recurso de Formatação Estruturada, com o qual é possível selecionar apenas os rótulos ou apenas os dados de uma tabela dinâmica. 

Por exemplo, veremos que existe um comando que permite a seleção somente dos rótulos da tabela dinâmica, existe outro comando que permite a seleção apenas dos valores e assim por diante.

O Excel 2010 oferece diversas combinações prontas para Formatação Como Tabela. Com esse recurso podemos definir uma aparência profissional para um relatório de tabela dinâmica.

O mais interessante é que o recurso de Formatação Como Tabela é extremamente simples de ser utilizado. Com alguns poucos cliques de mouse podemos formatar o relatório de tabela dinâmica, aplicando recursos como cor de fonte, cor de fundo, negrito, destaque em linhas de título, etc.

Nessa lição veremos alguns exemplos simples de utilização do recurso de Formatação Como Tabela.

Podemos facilmente classificar os itens de uma planilha. Também é possível classificar os itens de uma tabela dinâmica. 

Você pode classificar os itens de uma tabela dinâmica usando o seguinte caminho: Clicar em qualquer célula da Tabela Dinâmica, para que seja exibida a guia Ferramentas de Tabela Dinâmica. 

Dentro da guia Ferramenta de tabela Dinâmica clicar na guia Opções. Dentro da guia Opções, dentro do grupo Classificar e Filtrar, clicar no botão Classificar e definir as opções de classificação desejadas.

Nessa lição veremos, através de um exemplo prático, as diferentes opções de classificação que podemos utilizar em uma tabela dinâmica.

Nessa lição veremos as demais opções de configuração da janela “Opções da tabela dinâmica”, indicada na Figura 3.42 da lição anterior.

Principais Opções da caixa de diálogo Opções da tabela dinâmica:

• Campo Nome: Digite um novo para associar um nome descritivo/amigável com a tabela dinâmica. Em um gráfico dinâmico, esta caixa contém o nome da tabela dinâmica que contém os dados para geração do gráfico. A alteração desse nome fará com que o relatório de tabela dinâmica seja renomeado, e não o relatório de gráfico dinâmico. Por padrão é criada uma nomeação sequencial, como por exemplo: Tabela dinâmica 1, Tabela dinâmica 2 e assim por diante.

• Guia Totais e Filtros -> Grupo Totais gerais: Exibe totais gerais para colunas e/ou Linhas em um relatório de Tabela dinâmica. Em um relatório de Gráfico dinâmico, os totais gerais são exibidos no relatório de Tabela dinâmica associado. No exemplo da lição Anterior são os totais gerais por trimestre e por funcionário, ou seja, estão habilitados os totais gerais tanto por linha (Funcionário), quanto por coluna (Trimestres).

• Guia Layout e Formato: Contém diversas opções que afetam a formatação e o layout da tabela dinâmica. Por exemplo, podemos definir o que deve ser exibido em células que não contém valores (já usamos esta opção na lição anterior), o que deve ser exibido em células que apresentem erros, se as células com rótulos devem ser mescladas e centralizadas ou não e assim por diante.

• Guia Totais e Filtros -> Grupo Totais gerais -> Subtotalizar itens de página filtrados: Esta opção vem desmarcada, por padrão. Se você marcar esta opção, o Excel inclui itens de campo de página ocultos nos subtotais do relatório de tabela dinâmica. Em um relatório de gráfico dinâmico, os subtotais são afetados no relatório de tabela dinâmica associado. Esta opção não está disponível em relatórios baseados em dados de origem de bancos de dados OLAP.

• Guia Layout e Formato -> Mesclar e centralizar células com rótulos: Esta opção vem desmarcada, por padrão. Selecione esta opção para mesclar células para todos os rótulos de linha e coluna externos no relatório de tabela dinâmica. Esta opção não está disponível em relatórios de gráfico dinâmico. Por exemplo, podemos selecionar essa opção para mesclar todas as células com os rótulos de ano e trimestre, no exemplo da lição anterior.

• Guia Layout e Formato -> Preservar formatação da célula e atualizar: Esta opção vem marcada, por padrão. Mantém qualquer formatação aplicada aos dados do relatório de tabela dinâmica sempre que você atualiza o relatório ou altera seu layout. Esta opção não está disponível em relatórios de gráfico dinâmico.

• Guia Impressão -> Repetir rótulos de item em cada página impressa: Esta opção vem marcada, por padrão. Selecione esta opção para fazer com que o Excel imprima  rótulos de item de campo de linha externos na parte superior de cada página impressa. Os rótulos de item são repetidos para todos os campos de linha à esquerda do campo para o qual a quebra de página separa um grupo de itens. Desmarque esta caixa de seleção para imprimir os rótulos de item apenas nas primeiras páginas impressas onde eles aparecem.

• Guia Layout e Formato -> Exibir campos na área de filtragem do relatório: Nesta lista você seleciona a ordem na qual você deseja que os campos de página apareçam. O layout de campo de página padrão é Abaixo e acima. Esta opção não está disponível em relatórios de gráfico dinâmico.

• Guia Layout e Formato -> Para valores de erro, mostrar: Marque a caixa de seleção Para valores de erro, mostrar para exibir um valor em vez de um erro; em seguida, digite um valor para exibição. Esta opção não está disponível para relatórios de Gráfico dinâmico.

• Guia Layout e Formato -> Para células vazias, mostrar: Marque a caixa de seleção Para células vazias, mostrar para exibir um valor em vez de células em branco; em seguida, digite um valor para exibição. Esta opção não está disponível em relatórios de gráfico dinâmico. Nós utilizamos esta opção no exemplo da lição anterior.

• Guia Dados -> Salvar dados de origem como arquivo: Esta opção vem marcada por padrão. Salva uma cópia dos dados internos, que geram os consolidados da tabela dinâmica, no arquivo da pasta de trabalho para que você não tenha que atualizar o relatório antes de trabalhar com ele quando você abrir o arquivo da pasta de trabalho. Se você não selecionar esta opção, o arquivo da pasta de trabalho será muito menor. Entretanto, você deve marcar a caixa de seleção Atualizar dados ao abrir o arquivo quando você abrir a pasta de trabalho, antes de trabalhar com o relatório. Se você não salvar esses dados, eles também serão descartados de outros relatórios na pasta de trabalho que são baseados nesse relatório de tabela dinâmica ou no relatório de tabela dinâmica associado ao relatório de tabela dinâmica atual. Esta opção é ignorada em arquivos de modelos de relatórios.

• Guia Dados -> Atualizar dados ao abrir o arquivo: Atualiza os dados do relatório de tabela dinâmica e do relatório de gráfico dinâmico a partir dos dados de origem sempre que você abre a pasta de trabalho.

Bem, estas são as principais opções de configuração para uma tabela dinâmica do Excel 2010. Após ter configurado as opções desejadas, basta clicar em OK para aplica-las.

Gráficos Dinâmicos - O Que São e Elementos:

Nessa lição aprenderemos a criar um Gráfico Dinâmico, a partir dos dados de um relatório de tabela dinâmica. O gráfico é dito dinâmico porque, no próprio gráfico, podemos aplicar filtros e mover campos de posição de tal maneira que o gráfico é dinamicamente alterado para refletir os filtros e/ou alterações efetuadas.

Utilizaremos um exemplo prático a partir do qual aprenderemos a criar um gráfico dinâmico, passo a passo.

Nessa lição aprenderemos a fazer algumas alterações no gráfico dinâmico. 

Faremos as seguintes alterações:

• Alterar o tipo do gráfico dinâmico.
• Formatar títulos, cores e outras opções do gráfico dinâmico.

Outra característica interessante dos gráficos dinâmicos é que é possível alterar os campos que fazem parte do gráfico dinamicamente, simplesmente arrastando campos a partir da Lista de campos da Tabela dinâmica. 

Também podemos retirar campos do gráfico arrastando-os de volta para a Lista de campos da Tabela dinâmica.

Vamos alterar o gráfico dinâmico criado nas lições anteriores, simplesmente arrastando campos. Se você tiver fechado a planilha com o gráfico criado nas lições anteriores, abra-a novamente, pois vamos utilizá-la neste exemplo.

Exercícios Propostos com Gráficos Dinâmicos - Parte 1

Exercícios Propostos com Gráficos Dinâmicos - Parte 2

Conclusão do Módulo 3:

Conclusão: No Módulo 3 aprendemos o importante recurso de Tabelas Dinâmicas e Gráficos Dinâmicos. Aprendemos a criar tabelas dinâmicas, a aplicar filtros e a fazer análise dos dados. Também aprendemos a criar gráficos dinâmicos, aplicando filtros diretamente nos gráficos. Nesse módulo foram abordados os seguintes tópicos.

Módulo 3 – Tabelas Dinâmicas e Gráficos Dinâmicos

Lição 01 – Introdução

Lição 02 – Tabelas Dinâmicas - Como Funcionam e Componentes

Lição 03 – Tabelas Dinâmicas - Primeiro Exemplo Prático

Lição 04 – Tabelas Dinâmicas - Descobrindo o Poder

Lição 05 – Tabelas Dinâmicas - Mais Sobre Filtros Básicos

Lição 06 – Tabelas Dinâmicas - Múltiplos Campos de Coluna

Lição 07 – Tabelas Dinâmicas - Múltiplos Campos de Linha

Lição 08 – Tabelas Dinâmicas - Dois Exercícios Propostos

Lição 09 – Tabelas Dinâmicas - Resolução do Exercício 01

Lição 10 – Tabelas Dinâmicas - Resolução do Exercício 02

Lição 11 – Tabelas Dinâmicas - Formatação Estruturada

Lição 12 – Tabelas Dinâmicas - Formatação Como Tabela

Lição 13 – Tabelas Dinâmicas - Classificação e Formatação de Células Vazias

Lição 14 – Tabelas Dinâmicas - Mais Opções de Configurações

Lição 15 – Gráficos Dinâmicos - Conceito e o Primeiro Exemplo

Lição 16 – Gráficos Dinâmicos - Formatando e Alterando o Tipo

Lição 17 –Alterando os Campos que Compõem o Gráfico

Lição 18 – Tabelas Dinâmicas - Exercícios Propostos

Lição 19 – Tabelas Dinâmicas - Mais Exercícios Propostos

Lição 20 – Resumo do Capítulo

Introdução:

Neste Módulo você aprenderá a utilizar uma série de funções do Excel 2010 para a análise de dados e simulações de hipóteses. Você aprenderá sobre os seguintes tópicos:

• Totalização de dados: Totais/subtotais.
• Consolidação de dados.
• Análise de dados.
• Simulação de cenários.
• Atingindo Metas em cenários.
• Utilizando cenários.

Totalização de Dados:

O recurso de totalização de dados é bastante interessante, pois permite a criação de totais e subtotais de uma maneira rápida e eficiente. Com um simples comando podemos fazer com que o Excel 2010 crie uma série de totais e subtotais úteis para a análise dos dados da planilha.

Além da criação de totais/subtotais o Excel 2010 cria uma estrutura que facilita a navegação pelos resultados. Simplesmente utilizando o mouse é possível ocultar ou exibir mais níveis de detalhes.

Consolidação de Dados:

O recurso de consolidação de dados permite que façamos cálculos com base em dados de diferentes planilhas, inclusive com diferentes fontes de dados. Por exemplo, podemos fazer o cálculo do total anual de vendas com base em 12 planilhas, cada uma contendo o total mensal de vendas.

Também é possível fazer totalizações a partir de recursos de fontes externas, por exemplo, você pode consolidar o total de vendas a partir de uma planilha de vendas da matriz, somando os resultados de vendas de uma ou mais filiais, a partir de dados de um arquivo de texto ou de um banco de dados do Microsoft Access.

Com o recurso de consolidação é possível obter dados que facilitam a análise e a tomada de decisões, sendo que esses dados podem vir de diferentes fontes e estar em diferentes formatos. 

Análise de cenários:

Um cenário é um conjunto de valores que o Excel 2010 salva e pode substituir automaticamente na sua planilha. Você pode usar cenários para prever o resultado de um modelo de dados que está sendo analisado em uma planilha. Você pode criar e salvar diferentes grupos de valores em uma planilha e alternar para qualquer um desses novos cenários para exibir resultados diferentes. 

Criar cenários:  Por exemplo, se você deseja criar um orçamento mas não tem certeza de sua receita, pode definir valores diferentes para a receita e alternar entre os cenários para efetuar análises hipotéticas.

Relatórios de resumo do cenário: Para comparar vários cenários, você pode criar um relatório que os resuma na mesma página. O relatório pode listar os cenários lado a lado ou resumi-los em um relatório de tabela dinâmica. Aqui começamos a combinar diferentes recursos do Excel 2010 usamos o recurso de cenários para fazer análises e, em seguida, o recurso de tabelas dinâmicas, para obter uma visão consolidada dos dados.

Aprenderemos a utilizar os recursos para análise e consolidação de dados, através de uma série de exemplos práticos, de acordo com a metodologia que adotamos nos Módulos 1, 2 e 3.

Uma funcionalidade bastante útil do Excel 2010 (e que já vimos de maneira rápida em lições anteriores) é a possibilidade de, rapidamente, criarmos subtotais para grupos de dados em uma planilha. Por exemplo, podemos criar subtotais de vendas por País, dentro de cada País podemos criar subtotais por cidade. Ou poderíamos criar subtotais por vendedor, produto, categoria, cliente, etc.

A criação de subtotais é uma maneira rápida de compararmos o desempenho de diferentes grupos. Por exemplo, com o cálculo de subtotais por vendedor, podemos comparar, facilmente, o desempenho de cada vendedor. 

Nesta lição veremos um exemplo básico do uso de subtotais. Nas próximas lições abordaremos o assunto de subtotais, quando falaremos das ferramentas de análise e simulação do Excel 2010. 

Nesta lição veremos um exemplo simples de como criar subtotais por vendedor.

Nessa lição vamos apresentar mais um exemplo prático, passo a passo de criação de subtotais em uma lista de dados do Excel. 

O objetivo é fixarmos bem os passos necessários para a criação e a navegação através dos subtotais. Também aprenderemos algumas opções de configurações dos subtotais.

Nessa lição veremos algumas configurações e formatações que podem ser feitas em uma planilha com dados consolidados. 

Para tal vamos utilizar, mais uma vez, um exemplo prático, passo a passo.

Exercícios Propostos com Subtotais.

O Recurso de Consolidação de dados permite que façamos cálculos consolidados a partir de múltiplas planilhas, múltiplas fontes externas de dados ou uma mescla entre planilhas do Excel 2010 e fontes externas de dados, tais como tabelas de dados em uma página da Web ou em um banco de dados do Access ou do SQL Server.

Vamos considerar o seguinte exemplo: Você recebe, mensalmente, uma planilha com os consolidados de vendas por região/produto. Ao final do ano você precisa criar uma planilha com o total anual de vendas. Vamos supor que os dados mensais estejam em uma planilha com o nome do mês. Vamos supor que na célula E3, de cada planilha, tenhamos o total de vendas do produto X para a região Sul. Na célula E3 da planilha dos totais anuais, você teria que utilizar a seguinte fórmula:

=Jan!E3+Fev!E3+Mar!E3+Abr!E3+Mai!E3+Jun!E3+Jul!E3+Ago!E3+
Set!E3+Out!E3+Nov!E3+Dez!E3

Agora imagine o trabalho para criar todas as fórmulas necessárias para o total anual, especialmente se a planilha for complexa, com diferentes cálculos. Com o recurso de consolidação podemos fazer com que o Excel 2010 crie todas as fórmulas necessárias, automaticamente.

Um detalhe importante: “Para utilizarmos o recurso de consolidação, todas as planilhas a serem consolidadas devem ter a mesma estrutura, ou seja, o mesmo número de linhas e colunas, com os dados nas mesmas posições. Ou seja, devem ser planilhas com exatamente a mesma estrutura, apenas variando os dados entre as planilhas.

Nessa lição farei um exemplo prático, passo a passo, onde mostrarei como utilizar o recurso de consolidação de dados com o Excel 2010.

Nessa lição vamos utilizar os mesmos dados do exercício da lição anterior, porém faremos uma consolidação por categoria. A única diferença é que, os rótulos da primeira linha e da primeira coluna também farão parte da faixa a ser selecionada para a consolidação. Com isso os rótulos serão copiados para a planilha Trim1. 

A vantagem desse método é que, mesmo que os dados sejam deslocados de posição, em uma ou mais planilhas, o Excel 2010 consegue manter a referência, com base nos rótulos. Não podem haver rótulos repetidos.

Nesse exemplo utilizaremos o arquivo Modulo 4 - Exercício 05.xlsx. Observe que na planilha Trim1 não tem rótulos, os quais serão copiados diretamente das planilhas Jan, Fev e Mar.

Nessa lição veremos como alterar algumas configurações e cálculos em planilhas onde foram feitas consolidações de dados. 

Veremos como alterar os cálculos que estão sendo realizados e como aplicar algumas formatações básicas ao resultado da consolidação.

Nessa lição aprenderemos a fazer a consolidação de dados no Excel 2010, a partir de dados de diferentes arquivos. Vamos utilizar dois arquivos no formato .txt.

Nota: Para maiores detalhes sobre acessar dados externos no formato de arquivos .txt, consulte as lições do Módulo 1, onde fizemos exemplos práticos de importação de dados a partir de arquivos .txt.

O Excel 2010 também pode ser utilizado para fazer Análise de Hipóteses (também conhecida com análise de cenários ou testes SE). Por exemplo, você está analisando qual a melhor opção de compra para um imóvel, onde você quer achar a melhor alternativa entre o quantitativo a ser pago à vista e o quantitativo a ser financiado. Quanto mais você pagar a vista, menos terá que financiar, em contrapartida mais dinheiro terá que retirar das suas aplicações para cobrir a entrada maior. Nessa situação você pode variar o valor da entrada e fazer com que o Excel 2010 recalcule as demais variáveis que dependem do valor de entrada.

Podemos criar cenários mais complexos, dependentes de duas ou mais variáveis. No exemplo do parágrafo anterior podemos introduzir como segunda variável a taxa de juros mensal do financiamento. Nesse caso teremos um cenário onde variam os valores da entrada e da taxa de juros. O objetivo é definir qual a melhor proposta, ou seja, aquela em que será pago o menor valor já incluídos os juros, ao mesmo tempo em que otimizamos o rendimento do dinheiro aplicado, reduzindo a entrada a ser paga.

A análise é feita a partir da construção de Tabelas de Dados. A seguir vamos aprender sobre o conceito de Tabela de Dados.

O conceito de Tabela de Dados:

Uma Tabela de Dados é um intervalo de células que mostra os resultados da substituição de diferentes valores em uma ou mais fórmulas/variáveis. Existem dois tipos de tabelas de dados: tabelas de entrada única e tabelas de dupla entrada. Para tabelas de entrada única, você insere valores diferentes para uma variável e vê o efeito em uma ou mais fórmulas. Para uma tabela de entrada dupla, você insere diferentes valores para duas variáveis e verifica os efeitos em uma fórmula.

Uma tabela de dados é um intervalo de células que mostra como a alteração de determinados valores em suas fórmulas afeta os resultados das fórmulas. As tabelas de dados fornecem um atalho para calcular várias versões em uma operação e uma maneira de exibir e comparar os resultados de todas as variações diferentes em sua planilha.

Nessa lição criaremos uma tabela de dados baseada em uma única variável e utilizaremos o Excel 2010 para fazer uma análise de hipóteses, variando o valor dessa variável.

Exemplo proposto: Vamos utilizar um exemplo que é clássico para simulações de uma variável. Esse exemplo é utilizado em diversos livros de Excel que tratam sobre simulações. Imagine que você está analisando a hipótese de um empréstimo para aquisição da casa própria. Nesse caso você fará um empréstimo de R$ 150.000,00, com prazo de pagamento de 30 anos e quer calcular o valor da prestação mensal, com base em diferentes taxas de juros oferecidas pelo mercado.

Quando fazemos simulações baseadas em uma variável (no caso do exemplo da lição anterior, a nossa variável era somente a taxa de juros) é possível utilizar mais do que uma fórmula. Por exemplo, poderíamos criar simulações para duas ou mais colunas, cada uma baseada em um montante diferente. 

Nessa lição vamos fazer um exemplo, passo a passo, no qual vamos calcular o valor das prestações baseadas em diferentes taxas de juros e em diferentes valores de capital, ou seja, duas variáveis.

Os exemplos das duas lições anteriores são de simulações baseadas em uma única variável: Taxa de Juros. Na Lição anterior utilizamos várias fórmulas, uma para cada valor de empréstimo, para poder fazer simulações para diferentes empréstimos à diferentes taxas. 

É possível fazer simulações para duas variáveis, sem que tenhamos que utilizar várias fórmulas, uma para cada valor da segunda variável. Nessa lição vamos fazer um exemplo, passo a passo, no qual vamos calcular o valor das prestações baseadas em diferentes prazos, onde não será necessária a utilização de diferentes fórmulas. Nesse caso uma das variáveis será a taxa de juros e a outra será o prazo, em meses.

Atingir Metas:

Nessa lição aprenderemos a utilizar o comando Atingir meta. Quando você sabe o resultado desejado de uma única fórmula mas não o valor de uma variável de entrada que a fórmula precisa para chegar ao resultado desejado, você pode usar o recurso Atingir meta. Para atingir a meta/valor proposto, o Excel 2010 varia o valor em uma célula específica até uma fórmula dependente daquela célula retornar o resultado desejado. Por exemplo, use Atingir meta para alterar a taxa de juros na célula B3, de forma incremental até que o valor do pagamento em B4 seja igual a R$ 900,00, em uma simulação de empréstimo, por exemplo.

O comando Atingir Metas utiliza um método iterativo/recursivo para achar o valor que atenda a situação proposta. Uma das limitações desse comando é que ele somente pode ser utilizado para problemas de uma única variável. Por exemplo, você não pode utilizar o comando Atingir Metas para determinar a taxa de juros e o prazo, para um determinado empréstimo, que produzam um valor específico para a prestação.

Por padrão o comando Atingir Metas interrompe os cálculos após 100 iterações ou depois de encontrar uma resposta que esteja dentro de 0,001 de seu valor de destino especificado. Caso seja necessária uma precisão maior, podemos alterar o número de iterações e a precisão desejada. O comando Atingir Metas está disponível na Guia Dados, dentro do grupo Ferramentas de Dados, clique no botão Teste de Hipóteses e, no menu de opções que é exibido, clique em Atingir Meta. Vamos utilizar um exemplo prático de uso da ferramenta Atingir Metas.

O que é um Cenário?

Apesar de você já ter visto nas lições anteriores o conceito referente a cenário, vamos relembrar. O Cenário é um conjunto de valores que o Excel 2010 salva e pode substituir automaticamente na sua planilha. Você pode usar cenários para prever o resultado de um modelo de planilha. Você pode criar e salvar diferentes grupos de valores em uma planilha e alternar para qualquer um desses novos cenários para exibir resultados diferentes.

As tabelas de dados que utilizamos para simulações nas Lições anteriores são indicadas para problemas relativamente simples, com uma ou duas variáveis. Para a solução de problemas mais complexos, de até 32 variáveis, podemos utilizar o recurso de Gerenciador de Cenários. Este recurso está disponível na guia Dados, grupo Ferramentas de Dados, clique no botão Teste de Hipóteses e, no menu de opções que é exibido clique em Gerenciador de Cenários.

É possível criar vários cenários para um único modelo de simulação, onde cada cenário apresenta um conjunto diferente de valores para as variáveis envolvidas. É possível proteger os cenários com senha para que não sejam modificados.

Vamos usar a planilha da Figura 4.73, da Lição anterior, para criar diferentes cenários. Esta planilha está disponível na pasta de exemplos que acompanha o livro, com no Mem de Modulo 4 - Exercício 10.xlsx.

Nessa lição aprenderemos a utilizar os cenários definidos na Lição anterior. Em primeiro lugar veremos como fazer com que o Excel 2010 recalcule os valores da planilha, com base nas entradas de um cenário específico. 

Na próxima lição veremos como criar um relatório consolidado, onde são comparados os cálculos com base nos valores dos diferentes cenários.

Nessa lição veremos como gerar um relatório consolidado, com cálculos para todos os cenários possíveis. 

O relatório será criado em uma nova planilha, onde são efetuados, automaticamente pelo Excel, uma série de cálculos com base nos valores para cada um dos cenários existentes. Vamos ao exemplo prático.

Conclusão: Neste Módulo 4 aprendemos a utilizar os recursos para análise de dados e simulações de hipóteses e cenários. Iniciamos pelo uso de Subtotais/Resumos. 

Em seguida aprendemos a utilizar a Consolidação de dados, passamos pelo uso de Análises/Simulações, vimos como Atingir objetivos e finalizamos com o uso de Cenários.

Módulo 4 –  Análise, Cenários e Consolidação de Dados.

Lição 01 - Introdução

Lição 02 - Capítulo 04 – Calculando Subtotais/Resumos   

Lição 03 - Capítulo 04 – Subtotais - Mais um Exemplo Prático

Lição 04 - Capítulo 04 – Subtotais - Configurações e Alterações em Dados

Lição 05 - Capítulo 04 – Subtotais - Exercícios Propostos

Lição 06 - Capítulo 04 – Introdução à Consolidação de Dados

Lição 07 - Capítulo 04 – Consolidação de Dados - Um Exemplo Prático

Lição 08 - Capítulo 04 – Consolidação por Categoria - Um Exemplo Prático

Lição 09 - Capítulo 04 – Consolidação de Dados - Configurações

Lição 10 - Capítulo 04 – Consolidação de Dados - Múltiplos Arquivos

Lição 11 - Capítulo 04 – Análise de Dados - Introdução e Conceitos

Lição 12 - Capítulo 04 – Análise/Simulações Baseadas em uma Tabela de Dados

Lição 13 - Capítulo 04 – Análise/Simulações - Mais um Exemplo Prático

Lição 14 - Capítulo 04 – Análise/Simulações com Mais de uma Variável

Lição 15 - Capítulo 04 – Atingindo Objetivos - A Ferramenta Atingir Metas

Lição 16 - Capítulo 04 – Cenários - Introdução e Exemplo

Lição 17 - Capítulo 04 – Cenários - Criação e Utilização

Lição 18 - Capítulo 04 – Cenários - Utilizando e Simulando

Lição 19 - Capítulo 04 – Cenários - Criando um Relatório Consolidado de Cenários

Lição 20 - Capítulo 04 – Resumo do Capítulo 04

Introdução:

Nesse e nos próximos dois módulos veremos assuntos relacionados a automação de tarefas no Excel 2010. Existem situações onde não conseguimos resolver o problema proposto, simplesmente utilizando os comandos e fórmulas do Excel 2010. Nessas situações temos que fazer o uso de recursos mais avançados, tais como Macros e Programação VBA. A linguagem de programação do Excel 2010 é o VBA – Visual Basic for Applications. O VBA é a linguagem de programação para todos os aplicativos do Microsoft Office: Word, Excel, Access e PowerPoint.

Nas lições desse módulo aprenderemos sobre Macros. Veremos o que é uma Macro, para que serve, quando devemos usar Macros, como criar e alterar Macros. Em seguida aprenderemos os fundamentos básicos da linguagem de Programação VBA. Veremos os conceitos teóricos da linguagem VBA e os comandos básicos, para que você possa criar programas simples. Nas lições dos próximos dois capítulos veremos exemplos de aplicação do VBA para a solução de problemas práticos, os quais não poderiam ser solucionados sem o uso de programação.

Neste módulo você aprenderá os fundamentos sobre Macros e VBA. Mostrarei exatamente o que é uma macro, o que é programação VBA, o ambiente de programação, as principais funções do VBA e como criar as primeiras rotinas em programação VBA no Excel 2010. Os conceitos apresentados neste capítulo serão fundamentais para os demais capítulos do livro.

Vou iniciar o módulo mostrando como criar uma macro e como verificar o Código VBA que é criado e associado com a macro, o qual na prática é quem “faz o trabalho” da macro. Também mostrarei como associar teclas de atalho e botões de comando com uma Macro.

O próximo passo será aprender a utilizar o Ambiente de Programação do VBA no Excel 2010, também chamado de Editor do VBA. Você aprenderá a criar código, a “navegar” através dos objetos disponíveis e a utilizar os vários recursos de ajuda fornecidos pelo Editor de código VBA. Mostrarei as diversas partes que compõem o Editor do VBA e como utilizá-las.

Em seguida passarei ao estudo da linguagem VBA propriamente dita. Estes tópicos são a base da linguagem, os quais serão utilizados em todos os exemplos práticos, nos demais capítulos deste livro.

Neste módulo você aprenderá sobre os fundamentos do VBA, tais como:

• Declaração de variáveis.
• Tipos de dados.
• Operadores aritméticos.
• Valores lógicos.
• Operadores lógicos.
• Estrutura de controle.
• Estruturas de decisão.
• Estruturas de repetição.
• Exemplos de utilização.

Seguindo o nosso estudo, apresentarei as principais funções internas do VBA. Farei a apresentação dividindo as funções em categorias, tais como funções de Data/Hora, funções de texto e assim por diante. 

Para encerrar o módulo você aprenderá sobre o conceito de Módulos, procedimentos e funções. Este conceito é muito importante para que você aprenda a criar código que possa ser reaproveitado. Isso aumenta, e muito, a sua produtividade no uso do VBA,

Conforme descrito na introdução do módulo uma macro é uma seqüência de comandos (cliques de mouse ou toques de teclado) que são gravados em um Módulo de programação VBA e podem ser executados, sempre que necessário. A grande vantagem de gravarmos uma seqüência de comandos é que poderemos utiliza-la sempre que necessário. Para isso basta executar a macro na qual foi gravada a seqüência de comandos.

As Macros são uma excelente opção para automatizar tarefas repetitivas. Com o uso de Macros temos um ganho de produtividade considerável, ao evitar que tenhamos que executar manualmente, os diversos passos de uma tarefa, passos estes que podem ser automatizados através do uso de uma macro.

Existem duas maneiras distintas para a criação de uma macro:

• Podemos usar o gravador de Macros: Nesse caso o Excel 2010 grava cada uma das ações que farão parte da Macro e transforma essas ações nos comandos VBA equivalentes. Quando a macro for executada, os comandos VBA é que serão efetivamente executados. Cada comando VBA corresponde a uma ação efetiva da macro.

• Criar a Macro usando programação VBA: A partir do momento em que você dominar a linguagem de programação VBA, você poderá criar a macro digitando os comandos VBA necessários. Isso é feito usando o Editor de VBA, conforme veremos neste e nos próximos dois capítulos.

Melhor do que definições é a prática!!

Melhor do que uma série de definições é ver uma Macro em ação. Vamos a um exemplo prático bem simples, onde criaremos uma Macro. Em seguida vamos executa-la . Na próxima lição analisaremos o código VBA criado pelo gravador de macros.

Na Lição anterior podemos ver uma macro em ação. Criamos uma macro chamada FormataVermCinza. 

Essa macro é composta por três comandos de formatação. Cada vez que a macro é executada, os três comandos de formatação são novamente executados e aplicados à célula onde esta o cursor. Até aqui tudo OK. Nenhuma novidade. 

A pergunta que pode surgir é:

Como é que o Excel 2010 faz esta mágica?

Ou de outra forma:

Do que é feita uma macro?

onforme veremos nessa lição, uma macro é gravada no Excel 2010 como uma seqüência de comandos VBA. Por exemplo, ao clicarmos no botão N, para a aplicação de negrito, o Excel 2010 gera um comando VBA que faz a formatação em negrito. Nessa lição aprenderemos a acessar o código VBA gerado pelo Excel 2010 (embora ainda não sejamos capazes de entender esse código). 

Administração de Macros:

Nessa lição aprenderemos a renomear, excluir, editar e fazer outras alterações em macros.  Também aprenderemos a alterar outras opções associadas com a Macro, tal como o comando de teclado para executar a macro.

Existem algumas operações que podem ser feitas com uma macro, após a sua criação. A mais óbvia (e o motivo pelo qual uma macro é criada) é para executar a macro. 

Além da execução é possível executar outras operações com uma macro, conforme veremos nos exemplos desta lição.

Nessa lição aprenderemos a criar um novo botão, na Barra de Ferramentas e a associar esse botão com a macro. 

Dessa forma toda vez que precisarmos executar a macro, bastará clicar no botão associado com a macro.

Macros - Configurações de Segurança:

Conforme já descrito anteriormente, uma macro nada mais é do que uma seqüência de comandos VBA. Existem, literalmente, milhares de comandos VBA, que podem fazer deste simples formatações, até enviar e-mails, formatar um HD, apagar arquivos, etc. Como toda linguagem de programação, pode ser utilizada, por criminosos virtuais, para a criação de vírus, spywares e outras pragas virtuais. Por isso que existem configurações de segurança, relacionadas com o uso de Macros, para minimizar os riscos de que você venha a executar, sem saber, uma macro que possa instalar um vírus ou um trojan em seu computador.

Importante: Obviamente que nada substitui o uso de um bom programa antivírus, sempre atualizado e também de um bom programa anti spyware. As configurações de segurança das macros são um item a mais, a reforçar a segurança do seu computador, contra ataques de criminosos virtuais.

Nesta lição veremos como acessar as configurações de segurança de Macros e a descrição das opções disponíveis. Com o Excel 2010 o usuário pode alterar as configurações de segurança de macros para controlar que macros são executadas e em que circunstâncias ao abrir uma pasta de trabalho. Por exemplo, podemos configurar o Excel 2010 de tal forma a somente permitir a execução de macros digitalmente assinadas por um desenvolvedor confiável.

Vamos a um exemplo prático, onde mostrarei como alterar as configurações de segurança de macros, ao mesmo tempo que descreverei cada uma das opções disponíveis.

Introdução ao VBA:

Nas lições iniciais desse módulo aprendemos a criar macros simples, as quais reproduzem uma série de comandos de mouse e teclado. Para que possamos criar macros mais sofisticadas e resolver problemas mais complexos com o Excel, precisamos utilizar programação VBA. Conforme descrito anteriormente, a linguagem de programação do Excel (e de todos os aplicativos do Office) é o VBA: Visual Basic for Application.

Uma linguagem de programação, basicamente, é um conjunto de comandos, rotinas, objetos (com seus métodos, propriedades e eventos) e funções que executam tarefas específicas. Considere o exemplo genérico a seguir, onde são utilizados comandos genéricos (não são comandos VBA) para acessar uma tabela do Access a partir de uma planilha do Excel:

Começo da macro

‘ Comentários iniciais do Programa.

‘ Acessa dados da tabela pedidos do banco de dados

‘ C:\Bases de Dados\vendas.accdb

Acessar o banco de dados

Acessar a tabela Pedidos

Aplicar um filtro para País=’Brasil’

Exibir os dados obtidos na planilha atual

Formatar a primeira linha com negrito

Formatar a primeira linha com fonte azul

Encerrar a macro

Por que eu preciso aprender Programação VBA?

A utilização de Macros em conjunto com os recurso do VBA nos oferece um grande número de opções na busca por soluções para problemas mais complexos. Existem situações práticas em que, por mais que saibamos utilizar todos os recursos, comandos e funções do Excel, essa utilização não é capaz de solucionar o problema proposto. Nestas situações temos que utilizar programação VBA. Muitos usuários acreditam que é possível solucionar todo e qualquer problema usando somente os comandos e funções do Excel. Isso não é verdade. Existem situações onde você terá que criar suas próprias rotinas e funções, para solucionar um determinado problema. E isso só pode ser feito com o uso de programação. Vou ser até mais radical: SE VOCÊ NÃO SABE PROGRAMAÇÃO VBA, VOCÊ ESTÁ UTILIZANDO MENOS DE 5% DA REAL CAPACIDADE DO EXCEL. PONTO.

Muitos usuários entram em contato comigo via email, dizendo: “Por favor, me dê uma solução que não envolva programação, não quero saber de programação”.

“Respeito a opinião de todos, mas “fugir” da programação, significa abrir mão dos recursos mais poderosos que o Excel disponibiliza, significa ficar sem poder solucionar problemas mais complexos. A programação é uma ferramenta realmente útil. E você verá, no decorrer deste curso, que programação não é nenhum bicho de sete cabeças”.

A linguagem de programação utilizada pelo Excel é o VBA - Visual Basic for Applications. Conforme veremos a partir de agora esta é uma linguagem, ao mesmo tempo, extremamente simples e poderosa. Com o VBA temos acesso completo a todos os elementos de todos os objetos de uma planilha do  Excel. Também temos acesso a elementos externos, tais como bancos de dados do Access. Com código VBA podemos criar uma rotina para validação do dígito verificador de uma célula que contém um valor de CPF, CNPJ ou de um campo NúmeroDoProcesso; podemos criar código que percorre todas as linhas de uma planilha, alterando os valores de uma ou mais colunas, com base em uma ou mais condições, podemos automatizar rotinas para importação e exportação de dados e assim por diante.

Nesta introdução a linguagem VBA, que será assunto para todas as demais lições deste Capítulo, tratarei dos seguintes assuntos:

1.        Programação com o Microsoft Excel.

2.        Introdução a linguagem VBA - Visual Basic For Applications.

3.        Aprendendo Programação VBA:

·         Uma visão geral.

·         O Ambiente de Programação.

·         Anatomia dos Módulos do VBA.

·         Tipos de dados.

·         Variáveis.

·         Escopo de Variáveis.

·         Estruturas de controle, etc.

4.         Exercícios e Exemplos.

5.         Funções e procedimentos.

O Editor de Códigos - Ambiente de Desenvolvimento do VBA:

O Microsoft Excel fornece um ambiente de programação bastante poderoso, com uma série de recursos que facilitam a criação de código VBA. Neste tópico vamos aprender a utilizar os aspectos básicos do Ambiente de Programação do VBA. O ambiente de programação é um editor que facilita a criação de código VBA. Dentro do ambiente de programação, são oferecidas uma série de facilidades e dicas para que o Programador possa encontrar, facilmente, os objetos disponíveis, bem como os métodos e propriedades de cada objeto. 

Se não tivéssemos disponível um Ambiente de Programação, teríamos que lembrar da sintaxe de todos os comandos, dos métodos e propriedades dos objetos. Convenhamos que isso é praticamente impossível, pois com o VBA temos acesso a milhares de objetos (é isso mesmo: milhares de objetos, comandos e funções. Por enquanto estou utilizando bastante o termo objeto, sem tê-lo explicado ainda. Mais adiante detalharei o conceito de classes, módulos e objetos). Cada objeto pode ter dezenas de propriedades, métodos e coleções (alguns tem centenas de propriedades e métodos). 

O ambiente de Desenvolvimento fornece uma série de facilidades para a criação de código VBA. Por exemplo, ao digitar o nome de um objeto e um ponto será aberta, automaticamente, uma lista com todos os métodos e propriedades deste objeto. Ao invés de lembrar do nome dos métodos/propriedades, basta selecioná-los em uma lista. Se selecionarmos um método, ao digitarmos o parênteses de abertura, será exibida uma lista com os argumentos esperados pelo método, bem como o tipo (texto, número, data, etc.) de cada argumento. Se digitarmos um comando incorretamente, o Ambiente de Desenvolvimento emite uma mensagem e coloca em destaque o comando que foi digitado incorretamente. Estas são apenas algumas das facilidades fornecidas pelo Editor de Código do VBA. 

Para conhecermos melhor o referido ambiente, vamos a um exemplo prático. Vamos abrir uma planilha, que contém uma macro chamada AplicaNegrito e editar essa macro. Lembre que para editar uma macro, temos que acessar o código VBA associado à macro. Isso é feito no Editor do VBA, ou seja, no Ambiente de Desenvolvimento do VBA.

Nesta lição mostrarei mais alguns importantes recursos do Ambiente de Programação do VBA. Mostrarei como o ambiente procura ajudar o programador, à medida que este digita o seu código. 

Também descreverei a estrutura de código contida em uma planilha do Excel. Para mostrar as funcionalidades do ambiente de programação, utilizarei um exemplo prático.

A partir desta lição, você iniciará o estudo dos fundamentos da linguagem VBA. 

O VBA é composto por uma série de comandos básicos, os quais fazem parte de praticamente toda linguagem de programação. São comandos para declaração de variáveis, para realização de operações aritméticas e lógicas e comandos para fazer testes lógicos e para executar a repetição de um conjunto de comandos, com base em uma ou mais condições. 

Esses comandos básicos serão utilizados em praticamente todos os exemplos deste livro e em qualquer rotina de programação que você venha a desenvolver na prática. Iniciaremos o nosso estudo de VBA pela definição do conceito de variáveis a aprendendo a declarar variáveis no VBA.

Nota: A medida que os conceitos forem sendo apresentados, faremos alguns testes. Para os testes criarei uma macro chamada MacroTeste, a qual irei associar uma combinação de teclas de Atalho: Ctrl+T. Para testar os comandos, a medida que estes forem sendo apresentados,  vamos acessar o código VBA da macro MacroTeste (usando o Editor VBA) e inserir os comandos a serem testados. 

Em seguida voltaremos para a planilha e executaremos a Macro para testar os comandos. Para facilitar o processo de execução da Macro usaremos a combinação de teclas Ctrl+Shift+T, a qual será associada à macro. Vou utilizar uma planilha chamada Módulo 5 – Exemplos Básicos VBA.xlsm. A seguir descrevo os passos para criação da MacroTeste e para associar a combinação Ctrl+Shift+T com esta macro. 

Você criará a macro sem nenhum comando. Os comandos serão inseridos e testados, nos exemplos práticos, das lições deste módulo, usando diretamente comandos VBA.

Para realizarmos cálculos e comparações entre variáveis, utilizamos operadores. 

Neste item trataremos sobre operadores aritméticos e operadores de comparação. 

Os operadores são utilizados para fazer operações matemáticas com variáveis, tais como adição, subtração, divisão, multiplicação, potenciação, etc.

Além de declaração de variáveis e cálculos básicos, tais como soma, adição, multiplicação, etc., o VBA fornece uma série de comandos conhecidos como estruturas de controle e repetição. As estruturas de controle são utilizadas para a realização de testes. Por exemplo:

“se o valor de x for maior do que o valor de y, execute estes comandos, caso contrário execute os seguintes comandos”.

Os testes condicionais são intensamente utilizados em programação. Eu até diria que é impossível não utilizá-los, conforme você mesmo constatará nos diversos exemplos deste livro.

Em um primeiro grupo, temos as chamadas estruturas de decisão. São comandos/estruturas que realizam um teste lógico, e executam determinados comandos quando o teste resultar verdadeiro, ou um conjunto diferente de comandos, quando o teste resultar falso. Agora passaremos a analisar as estruturas de decisão, disponíveis no VBA.

IMPORTANTE: Se você quiser pode utilizar a macro MacroTeste, para testar cada um dos exemplos de códigos a seguir. Basta seguir os passos indicados anteriormente, onde você acessa o código da macro MacroTeste, apaga todos os comando que estão dentro da macro, mantendo sempre os comandos Sub MacroTeste() e End Sub, que são os comandos de abertura e fechamento da macro. Depois de apagar os comandos de dentro da macro é só digitar o código dos exemplos, salvar pressionando Ctrl+B e executar pressionando F5.

Estruturas de Repetição:

Em determinadas situações, precisamos repetir um ou mais comandos, um número específico de vezes, ou até que uma determinada condição torne-se verdadeira ou falsa. Por exemplo, pode ser que haja a necessidade de percorrer todas as linhas de dados de uma determinada planilha, até que a última linha com dados seja alcançado. Para isso, utilizamos as chamadas estruturas de repetição, ou Laços. A partir de agora, aprenderemos as estruturas de repetição disponíveis no VBA.

IMPORTANTE: Se você quiser pode utilizar a macro MacroTeste, para testar cada um dos exemplos de códigos a seguir. Basta seguir os passos indicados nas lições anteriores, onde você acessa o código da macro MacroTeste, apaga todos os comandos que estão dentro da macro, mantendo sempre os comandos Sub MacroTeste() e End Sub, que são os comandos de abertura e fechamento da macro. Depois de apagar os comandos de dentro da macro é só digitar o código dos exemplos, salvar pressionando Ctrl+B (para salvar) e executar pressionando F5. Lembrando sempre que o código dos exemplos deve ser digitado entre os comandos Sub MacroTeste () e End Sub.

Além dos comandos básicos do VBA, vistos até agora, tais como declaração de variáveis, operadores aritméticos, estruturas de decisão e laços, estão disponíveis para uso no VBA, as centenas de funções de planilha, disponíveis no Excel. Por exemplo, você pode usar no código VBA, funções tais como SOMA, Média e assim por diante. Um detalhe importante é que no código VBA, devem ser utilizadas as funções com o nome em Inglês: SUM ao invés de SOMA, Avg ao invés de Média e assim por diante.

Na prática, existe um objeto chamado WorksheetFunction (funções de planilha) e as funções são consideradas métodos deste objeto. Por exemplo, para chamar a função Soma, temos que usar o comando:

WorksheetFunction.Soma(parâmetros da soma)

Não se preocupe em entender esta sintaxe neste momento, pois ainda não vimos mais detalhes sobre objetos, métodos, propriedades e parâmetros. Vamos continuar o nosso estudo e aprender a usar as funções, através de exemplos práticos. Mais adiante, aprendermos mais sobre objetos, métodos, eventos e parâmetros.

A partir dessa lição veremos uma série de aspectos que vão além do básico do VBA. Inicialmente vamos estudar uma série de funções disponíveis no Excel. Vamos dividir estas funções em categorias para facilitar o seu entendimento. Iniciaremos falando um pouco mais sobre tipos de dados e funções para conversão de tipos de dados, ou seja, funções que fazem a conversão de um tipo de dado para outro, como por exemplo de texto para número. Este tópico é de grande importância, principalmente quando criamos código que efetua cálculos, tais como uma folha e pagamentos ou cálculos de impostos.

Depois passaremos a analisar uma série de funções internas do VBA. São funções que fazem parte do Microsoft Excel, como por exemplo a função Date(), que retorna a data do sistema. Na seqüência do capítulo, aprenderemos a criar nossas próprias funções e Sub-rotinas. Aprenderemos as diferenças entre uma Sub-rotina e uma função, e quando utilizar uma ao invés da outra.

Nesta lição continuaremos o estudo das funções para determinação e conversão de tipos no VBA.

Nesta lição aprenderemos a utilizar as principais funções para conversão de tipos. Existem situações em que um determinado tipo de dado, deve ser convertido para outro. Por exemplo, se tivermos um número, armazenado na forma de texto, precisamos convertê-lo para inteiro ou Double, para que possamos realizar cálculos. Em um dos exemplos no final deste capítulo, faremos um exemplo de cálculo do DV do CPF, onde o CPF é um valor do tipo texto. Ao extrairmos cada dígito do CPF, estes serão extraídos como caracteres de texto. Precisaremos utilizar uma função de conversão, para convertê-los para números, a fim de que possamos efetuar os cálculos necessários.

Na seqüência, apresento as principais funções de conversão, bem como um pequeno fragmento de código, exemplificando a utilização de cada uma delas. Nunca é demais lembrar que se você quiser, pode utilizar a macro MacroTeste, criada e explicada nas lições anteriores, para testar cada um dos exemplos de códigos que eu apresento. Basta seguir os passos indicados anteriormente, onde você acessa o código da macro MacroTeste, apaga todos os comando que estão dentro da macro, mantendo sempre os comandos Sub MacroTeste() e End Sub, que são os comandos de abertura e fechamento da macro. Depois de apagar os comandos de dentro da macro é só digitar o código dos exemplos, salvar pressionando Ctrl+B e executar pressionando F5. Lembrando sempre que o código dos exemplos deve ser digitado entre os comandos Sub MacroTeste () e End Sub.

Vamos apresentar mais algumas funções do VBA, para a conversão de tipos de dados.

Nessa lição veremos as principais funções para tratamento de Texto, via programação VBA no Excel. 

Nesta lição veremos uma série de funções, relacionadas ao tratamento de valores de data e hora e a realização de cálculos matemáticos.

Módulos, Procedimentos e Funções:

Nas lições anteriores, aprendemos a utilizar as funções prontas, que já fazem parte do Excel. Porém podemos (e precisaremos) criar nossas próprias funções. A partir desta lição aprenderemos a criar nossas próprias funções e Sub-rotinas, usando programação VBA no Excel.

O princípio por trás da criação de  funções e sub-rotinas, é o de reaproveitamento de código e facilidade de manutenção do programa. Imagine que estejamos criando uma planilha para cálculos financeiros, por exemplo, depreciação contábil. Vamos supor que em diversos locais, seja necessária a realização de um determinado cálculo de depreciação. Poderíamos colocar o código/fórmulas que faz o cálculo, em cada um dos locais, onde o cálculo seja necessário. Porém esta não é a melhor maneira de criarmos nossos programas. Imagine, por exemplo, quando fosse necessário alterar a maneira de realizar os cálculos (o que poderia ocorrer devido à mudanças na legislação fiscal ou contábil). Teríamos que repassar todos os locais onde o cálculo é feito,  procurando os pontos onde o código está , e fazer as alterações. E se esquecêssemos de um único local, teríamos sérios problemas, pois haveria cálculos sendo feitos incorretamente, da maneira antiga.

Para resolver estes problemas, poderíamos criar uma função ou sub-rotina que faz os cálculos de depreciação. A função/sub-rotina seria criada dentro de um módulo de código do VBA, na própria planilha. Depois, em cada local onde precisamos fazer os cálculos, é só chamar a função (ou sub-rotina), para fazer os cálculos. Quando fosse necessária a alteração da metodologia de cálculo, era só alterar a função (ou sub-rotina) e pronto,  os novos cálculos passarão a ser feitos com base na nova metodologia. Isto poupa esforço, reduz o número de linhas de código, e facilita a manutenção, além de reduzir a possibilidade de erros.

Agora é chegada a hora de aprendermos a criar funções e sub-rotinas. Estes procedimentos/funções são criados em módulos de código VBA associados a planilha. Quando uma destas funções/procedimentos for necessária, basta chamar a respectiva função/procedimento que o Microsoft Excel se encarrega de localizar a função/procedimento, passar os parâmetros necessários (se for o caso) e receber os resultados retornados, caso seja uma função.

Vamos a outro exemplo. O Excel tem centenas de funções internas, para realizar os mais variados tipos de cálculos e operações. Mas evidentemente que mesmo com um grande número de funções, não estão disponíveis funções para todos os cálculos que você necessitará na prática. Por exemplo, não existe uma função simples para cálculo de Imposto de Renda com base na faixa de salário. Usando programação VBA, você pode criar uma função para cálculo do IRPF e utilizá-la em suas planilhas.

NOTA: Vamos falar um pouco mais sobre o termo Procedimento. Um procedimento é um grupo de instruções que pode ser chamado pelo nome atribuído ao procedimento. Neste contexto, funções e sub-rotinas e macros são tipos diferentes de procedimentos. Nesta e nas próximas lições você aprenderá mais sobre funções e sub-rotinas e verá alguns exemplos práticos. 

O Escopo de Varáveis no VBA:

Nesta lição trataremos de um assunto de grande importância: Escopo de variáveis. Quando você cria sub procedimentos e funções personalizadas é muito importante conhecer bem o conceito de Escopo de variáveis. Conforme será visto nesta lição, o escopo define em que locais do código VBA uma determinada variável é válida e possui um valor associado a ela.

Criação de Funções Personalizadas - Parte 1:

Nesta lição você aprenderá a criar funções personalizadas e a utilizá-las em suas planilhas. Vou mostrar como criar uma função bastante simples. Será uma função para cálculo do imposto de renda Pessoa Física, com base na “Tabela Progressiva Anual Para Cálculo do Imposto”, publicada na página da Receita Federal: www.receita.fazenda.gov.br Esta tabela é constantemente atualizada. 

Por isso, se for criar uma planilha de cálculo de Imposto de Renda, antes, consulte o site da receita, para usar os valores atualizados.

Cálculo do DV do CPF e do CNPJ - Como é o Algoritmo de Cálculo:

Nessa e nas próximas duas lições, veremos o uso do VBA para solucionar mais um exemplo prático. Vamos criar uma função personalizada, chamada ValidaCPF. Em seguida usaremos essa função para fazer o cálculo do DV de um conjunto de CPFs. Observe que para a criação desta função, usaremos apenas os comandos e funções internas básicas do VBA, vistas nas lições anteriores deste módulo.

Importante: O algoritmo de cálculo do DV de CPFs e CNPJs é de domínio público, já tendo sido publicado no diário oficial da união e em diversas revistas de informática, finanças e negócios, de circulação nacional.

Nessa lição explicarei como funciona o cálculo do DV do CPF.

Criação da Função de Validação do DV do CPF:

Nessa lição vamos criar uma função chamada ValidaCPF. Essa função recebe, como parâmetro, um valor de CPF no formato 111.111.111-11 ou o endereço de uma célula que contém um CPF nesse formato. A função retorna a palavra Válido se o CPF for Válido e Inválido se o CPF for inválido. É importante salientar que a função que será criada nesta lição, só funcionará corretamente, se o CPF estiver no formato 111.111.111-11, ou seja, com os pontos e com o traço.

Para criar uma função que possa ser utilizada na planilha, devemos criar a função dentro de um Módulo do VBA, conforme descrito no exemplo de criação da função Calcula_IRPF, em uma das lições anteriores, neste módulo.

Criaremos a função ValidaCPF, dentro do Módulo1 da planilha Números de CPF.xlsm, a qual você recebeu compactada dentro do arquivo Planilhas.zip.

Nessa lição veremos como utilizar a função ValidaCPF, criada na lição Anterior.

Criando mais Funções Personalizadas:

Nesta lição apresentarei mais alguns exemplos de criação de funções personalizadas. É importante lembrar que para que uma função personalizada, possa ser utilizada em todas as planilhas de uma pasta de trabalho, esta função deve ser criada em um módulo de código do VBA, conforme exemplificado e, detalhadamente explicado, nas lições anteriores.

Nesta lição vamos criar duas funções que serão bastante úteis e que, com certeza, você utilizará nas planilhas que criar no Excel. Para criar estas funções vou utilizar o objeto Range, para fazer referência a faixa de células onde estão os valores de dados a serem utilizados pelas funções. Você ainda não aprendeu sobre o Objeto Range, mas não se preocupe, pois esta objeto será detalhadamente estudado e exemplificado, nos demais capítulos deste curso. 

Nesta lição mostrarei mais alguns exemplos de códigos de funções personalizadas que poderão ser úteis em suas planilhas.

Conclusão e Visão Geral do que foi Visto no Módulo 5:

Neste módulo você aprendeu os fundamentos sobre Macros e sobre a programação VBA no Excel 2010. 

Eu mostrei exatamente o que é uma macro, o que é programação VBA, o ambiente de programação, as principais funções do VBA e como criar os primeiros programas e funções personalizadas. 

Os conceitos apresentados neste capítulo serão fundamentais para os demais capítulos deste livro. Em todos os exemplos dos demais capítulos, você irá utilizar um ou mais conceito apresentado neste capítulo.

Módulo 1 – Introdução às Macros e ao VBA no Excel 2010:

Lição 01 – Uma Introdução às Macros        

Lição 02 – O Que São Exatamente Macros??

Lição 03 – Conhecendo do que “é feito” uma Macro

Lição 04 – Administração de Macros

Lição 05 – Associando Botões com Macros

Lição 06 – Macros – Opções de Segurança

Lição 07 – Introdução ao VBA        

Lição 08 – O Editor de Código VBA – Parte 1

Lição 09 – O Editor de Código VBA – Parte 2

Lição 10 – VBA – Declaração de Variáveis

Lição 11 – Cálculos e Operadores Aritméticos

Lição 12 – Estruturas If...Then e os Operadores de Comparação

Lição 13 – Estruturas For...Next, Do...While e Do...Until

Lição 14 – Funções de Tipo de Dados – Parte 1

Lição 15 – Funções de Tipo de Dados – Parte 2

Lição 16 – Funções de Conversão de Tipos – Parte 1

Lição 17 – Funções de Conversão de Tipos – Parte 2         

Lição 18 – Funções para Tratamento de Texto

Lição 19 – Funções de Data e Hora e Matemáticas

Lição 20 - Módulos Procedimentos e Funções – Parte 1

Lição 21 - Módulos Procedimentos e Funções – Parte 2

Lição 22 – Criando Funções Personalizadas – Parte 1

Lição 23 – Cálculo do DV do CPF – Parte 1

Lição 24 – Cálculo do DV do CPF – Parte 2

Lição 25 – Cálculo do DV do CPF – Parte 3

Lição 26 – Mais Exemplos de Funções Personalizadas

Lição 27 – Mais Exemplos de Funções Personalizadas

Lição 28 – Conclusão

No próximo módulo iniciaremos o estudo do Modelo de Objetos do Excel. 

Com o uso dos objetos do Modelo de Objetos do Excel, você será capaz de criar rotinas de programação muito mais poderosas, as quais poderão solucionar uma série de problemas avançados no Excel.

Visão Geral do Módulo 6:

Nas lições deste módulo eu apresentarei o conceito mais importante quando se trata de programação VBA no Excel 2010: “A Hierarquia de Objetos do Excel”. 

Você verá que com o uso dos Objetos do Excel é possível acessar qualquer elemento de uma planilha. Apresentarei o primeiro objeto na Hierarquia de Objetos: O Objeto Application. Este é, sem dúvidas, o conceito mais importante que você precisará dominar, e bem, sobre programação VBA no Excel. 

Entendendo bem o conceito de objetos, hierarquia de objetos, métodos, propriedades e eventos de objetos, você dará um grande passo e tudo parecerá mais fácil. Se você não entender bem este conceito, tudo parecerá mais difícil e confuso. 

Por isso aqui vai a minha recomendação: Estude com calma as lições deste módulo, quantas vezes forem necessárias, até não restarem mais dúvidas sobre o que são objetos, o que é a hierarquia de objetos do Excel e o que são métodos, propriedades e eventos de objetos.

Um objeto representa um elemento da planilha do Excel ou o próprio Excel. Por exemplo, o objeto mais “alto” na hierarquia de objetos é o objeto Application. Este objeto representa o próprio Excel, ou seja, o aplicativo Excel. Descendo um pouco mais na hierarquia temos o objeto Workbook. O objeto Workbook representa uma pasta de trabalho do Excel. Dentro de uma pasta de trabalho (arquivo .xlsx ou .xlsm), podemos ter uma ou mais planilhas. As planilhas são representadas por objetos Worksheet. Dentro de uma planilha posso ter dados em várias faixas de células da planilha. Uma faixa de células é representada pelo objeto Range. Para acessar uma faixa de Células você pode usar a propriedade Cells e assim por diante, sucessivamente e hierquicamente.

O parágrafo anterior serve para dar uma pequena ideia do que é possível de ser feito, usando a Hierarquia de Objetos do Excel na programação VBA. Literalmente é possível ter acesso a qualquer elemento de uma planilha do Excel. Por exemplo, você poderia criar uma rotina que percorre todos os dados de uma ou mais planilhas de uma pasta de trabalho e coloca em negrito e fonte vermelha, todos os dados que atendam a determinadas condições. Ou você pode criar uma rotina que percorre uma faixa de células (Range) e exclui todas as linhas em branco.

Cada objeto da Hierarquia de objetos do Excel tem métodos e propriedades. Os métodos são utilizados para executar determinadas ações. Por exemplo, abrir uma planilha, ativar uma planilha, selecionar uma faixa de células, excluir uma faixa de células e assim por diante. Os métodos estão sempre associados a ações e são representados, por isso, por verbos. As propriedades descrevem características dos objetos. Por exemplo, o objeto Range tem uma propriedade chamada Count, a qual retorna o número de células da faixa. As propriedades representam características dos objetos.

Neste módulo eu farei uma descrição do modelo de Objetos do Excel, apresentarei uma visão geral do modelo, bem como uma descrição da sintaxe para uso de métodos e propriedades de um objeto.  

Também faremos um estudo detalhado do objeto Application. Você aprenderá a utilizar uma série de métodos e propriedades do objeto Applications. Também mostrarei o conceito de coleções e como utilizar as coleções do objeto Application. Além dos exemplos teóricos, para ilustrar o uso dos métodos e propriedades do objeto Application, também apresentarei uma série de exemplos práticos, para ilustrar a utilização do objeto Application. 

Se eu tivesse que resumir a importância deste módulo, resumiria no fato de entender o que é um modelo de Objetos, principalmente o modelo de objetos do Excel. 

Não se preocupe em decorar métodos e propriedades, pois, conforme mostrarei, o próprio Excel fornece ferramentas para que possamos encontrar ajuda, rapidamente, sobre os métodos e propriedades de qualquer objeto.

Hierarquia de Objetos do Excel:

Uma Hierarquia/Modelo de Objetos, como a do Excel, formada por vários objetos, cada um deles com dezenas (alguns com centenas) de métodos e propriedades, também é conhecida como uma Biblioteca. 

Nas lições do Módulo 5 nós tratamos dos aspectos básicos do VBA. Com os aplicativos do Office (Word, Excel, Access, PowerPoint e Outlook), temos acesso a um vasto conjunto de Bibliotecas; cada biblioteca com dezenas/centenas de objetos, cada objeto com dezenas/centenas de métodos, propriedades e coleções. 

Com a utilização dos objetos disponibilizados pelo Office, podemos criar soluções bastante sofisticadas. Nesta lição vamos entender exatamente o que são e como se relacionam, os seguintes itens: Bibliotecas, Objetos, Propriedades, Métodos, Coleções.

Nesta lição farei uma breve descrição dos principais objetos do Modelo de Objetos do Excel. 

Ter uma visão geral dos principais objetos é importante, principalmente para se ter um entendimento preciso de quando utilizar cada um dos objetos para solucionar problemas práticos.

Ao descrever os principais objetos, apresentarei pequenos exemplos de código, apenas para ilustrar o uso destes objetos. .

Não se preocupe em utilizar estes códigos agora. O objetivo, nesta lição, é apenas didático, para mostrar a sintaxe dos comandos. 

Nas demais lições deste curso você aprenderá, em detalhes, a utilizar os métodos e propriedades destes objetos, através de exemplos práticos.

Navegando na Hierarquia de Objetos:

Nesta lição mostrarei como utilizar o recurso Object Browser (Navegador de Objetos). 

Este recurso está disponível no ambiente de programação do VBA. Utilizando o Objetct Browser é possível selecionar uma biblioteca para que sejam exibidos somente os objetos da biblioteca selecionada. 

Em seguida você pode clicar em um dos objetos disponíveis. Serão exibidos todos os métodos e propriedades do referido objeto. Em seguida você pode selecionar um método e/ou propriedade e pressionar a tecla F1 para obter ajuda sobre o método e/ou propriedade selecionado. 

Em resumo, o Object Browser é uma maneira de navegar através das bibliotecas de programação disponíveis, bem como através dos objetos disponíveis em cada biblioteca e dos métodos e propriedades de cada objeto e obter mais informações sobre o item selecionado. 

A seguir mostrarei um exemplo prático de utilização do Object Browser.

O Objeto Application - Parte 1:

Nesta lição vamos iniciar o estudo detalhado, dos principais objetos do Modelo de Objetos do Excel. Iniciaremos este estudo pelo objeto Application, o qual, conforme já descrito anteriormente, representa o próprio Excel.

O objeto Application tem, ao todo, 218 propriedades e métodos.  

Quando trabalhamos dentro de uma planilha do Excel, não é preciso a criação explícita de um objeto Application. Por exemplo, se dentro do código, quisermos fazer referência a uma determinada célula, não precisamos criar um objeto Application, depois um objeto Workbook, depois um objeto Worksheet para, finalmente, poder acessar as células de uma planilha usando um objeto Range ou a propriedade Cells. Ao invés disso, podemos fazer referência direta à célula desejada. 

Com isso o Excel supõe que estamos trabalhando com a instância atual do Excel, dentro da pasta de trabalho atual (arquivo .xlsx ou .xlsm), dentro de uma das suas planilhas, o que é bastante razoável. Apenas teríamos que criar toda essa hierarquia de objetos, se quiséssemos fazer referência a uma célula de uma planilha de uma pasta de trabalho externa.

Nessa lição farei uma introdução ao objeto Application. Nas próximas lições estudaremos, em detalhes, os métodos e propriedades do objeto Application.

O Objeto Application - Parte 2:

Nesta lição mostrarei como declarar uma variável como sendo do tipo Application e como inicializá-la, usando código VBA. 

Vou demonstrar a declaração e utilização do objeto Application, através de exemplos práticos.

O Objeto Application - Parte 3:

Nesta lição vamos iniciar um estudo detalhado, dos principais métodos e propriedades do objeto Application. Faremos este estudo utilizando pequenos trechos de código, os quais ilustram a utilização dos métodos e propriedades do objeto Application.

IMPORTANTE: Para testar estes pequenos trechos de código basta abrir o Excel, acessar o editor de código VBA (Alt+F11), criar um procedimento e inserir o código do exemplo dentro do procedimento. Depois é só colocar o cursor em qualquer linha do procedimento e pressionar a tecla F5 para executá-lo. Todos os detalhes sobre como criar um procedimento e executá-lo estão nas lições de Introdução ao VBA, no Capítulo 5. Em caso de dúvidas, volte e revise o Capítulo 5.

O Objeto Application - Parte 4:

Nesta lição você aprenderá a utilizar alguns métodos e propriedades os quais são utilizados para alterar a visualização das planilhas do Excel, tais como a propriedade DisplayAlerts, que suprime ou ativa as mensagens de aviso e a propriedade DisplayFormulaBar, a qual é utilizada para exibir ou ocultar a barra de fórmulas.

O Objeto Application - Parte 5:

Nesta lição você aprenderá a utilizar alguns métodos e propriedades, utilizados para habilitar e/ou desabilitar recursos do Excel. Por exemplo, existe uma propriedade que habilita/desabilita eventos, outro que habilita/desabilita sons e assim por diante.

O Objeto Application - Parte 6:

Nesta lição mostrarei como é possível associar uma função e/ou procedimento a uma combinação especial de teclas, como por exemplo Ctrl+Ç. Ou seja, você pode configurar o Excel, para que execute um procedimento ou função, toda vez que o usuário pressionar uma combinação especial de teclas. Também é possível fazer com que seja executada uma função ou procedimento em um tempo programado.

A associação de uma função e/ou procedimento, com uma combinação de teclas é feito usando o método Onkey, do objeto Application, o qual estudaremos logo a seguir.

O Objeto Application - Parte 7:

Nesta lição você aprenderá a utilizar alguns métodos e propriedades do objeto Application, para trabalhar com arquivos do Excel. 

Veremos que existe desde uma propriedade que define a pasta padrão para os arquivos do Excel (pasta que é selecionada, automaticamente, quando você usa os comandos Guia Arquivo -> Abrir, Salvar ou Salvar como...), até métodos para exibir uma caixa de diálogo para que o usuário selecione um arquivo e que retorna o nome deste arquivo, juntamente com o caminho completo.

O Objeto Application - Parte 8:

Por padrão, o Excel faz o recálculo automático das planilhas. Sempre que o valor de uma célula é alterado, todas as fórmulas que dependem do valor que foi alterado são recalculadas, para exibir os valores atualizados. 

Você pode controlar a maneira como o Excel faz o recálculo, usando o Guia Arquivo -> Opções. Será aberta a janela Opções do Excel. Nas opções no painel da esquerda clique em Fórmulas. Na janela que é exibida, bem na parte de cima, você tem a opção Cálculo da Pasta de trabalho. 

Por padrão vem marcada a opção Automático, ou seja, o recálculo é feito, automaticamente, sempre que houver mudanças em um ou mais valores da planilha. Para desabilitar o cálculo automático você deve marcar a opção manual e clicar em OK. 

Por exemplo, para planilhas muito grandes, o recálculo automático pode fazer com que você tenha que esperar vários minutos após alterar um determinado valor. Nestas situações, pode ser vantagem desabilitar o recálculo automático. Aí você altera os valores necessários e depois pressiona a tecla F9 para forçar um recálculo de toda a planilha.

Módulos, Funções e Procedimentos - Tópicos Avançados - Parte 1:

Nas lições do Módulo 1, você aprendeu os conceitos básicos para a criação de funções e procedimentos personalizados. 

Apresentei inclusive alguns exemplos simples, de criação de funções personalizadas, como por exemplo uma função para cálculo do Imposto de Renda e uma função para cálculo do DV do CPF. 

Nas demais lições deste módulo, apresentarei uma série de conceitos avançados sobre a criação de Procedimentos e Funções, sobre o uso de Procedimentos e Funções e sobre o tratamento de erro no VBA.

Dica: Não existe limite para o número de linhas de código que pode haver em um procedimento ou função. Porém uma boa prática de programação é não criar procedimentos ou funções muito extensos. Sempre que possível é indicado que você crie procedimentos ou funções para executar tarefas específicas, ou seja, dividir um problema maior em etapas menores e implementar um procedimento ou função para solucionar cada etapa específica. Depois você pode criar um procedimento principal, no qual você chama os diversos procedimentos/funções auxiliares, para solucionar as diversas etapas do problema.

Módulos, Funções e Procedimentos - Tópicos Avançados - Parte 2:

Nesta e nas próximas lições você aprenderá mais detalhes sobre a declaração de argumentos e sobre a passagem de parâmetros para um sub procedimento e/ou função.

Módulos, Funções e Procedimentos - Tópicos Avançados - Parte 3:

Na lição anterior eu falei um pouco mais sobre a declaração de parâmetros, tipo de parâmetros e tipo de retorno de uma função. 

Você também acompanhou, passo a passo, a criação de uma função Personalizada – SomaEsperta, e depois aprendeu a utilizar esta função.

Esta é uma técnica importante, pois apesar de o Excel disponibilizar milhares de funções prontas, que já fazem parte do Excel, evidentemente que não existem funções prontas no Excel, capazes de atender todas as necessidades de cálculo do dia-a-dia. 

Por isso a criação de funções personalizadas, com o uso do VBA, abrem-se novas e amplas perspectivas no uso de Excel. Nesta lição continuaremos o estudo sobre funções e sub procedures no Excel.

Módulos, Funções e Procedimentos - Tópicos Avançados - Parte 4:

Na lição anterior você aprendeu sobre a utilização de parâmetros Opcionais em procedimentos e funções do VBA. 

Você viu que para definir um parâmetro como Opcional, utilizamos a palavra chave Optional. 

Você também aprendeu sobre a lógico do uso da função IsMissing, para determinar se um parâmetro opcional foi ou não definido.

A questão que fica é como utilizar este procedimento. Existem diferentes maneiras para a execução deste procedimento. 

Vou optar por criar um segundo procedimento, chamado ExMudaSelecao, no qual farei diversas chamadas ao procedimento MudaSelecao, usando diferentes valores para os seus argumentos. Em seguida executarei o procedimento ExMudaSelecao, diretamente a partir do editor do VBA.

Módulos, Funções e Procedimentos - Tópicos Avançados - Parte 5:

Nesta lição aprenderemos sobre os chamados argumentos nomeados. 

Você deve ter observado, dos exemplos anteriores, que ao definir uma lista de argumentos para uma função/procedimento, estes argumentos tem que ser informados, quando da chamada da função/procedimento, na mesma ordem em que foram declarados. 

Como alternativa tínhamos os argumentos opcionais, normalmente declarados no final da lista, e que, por serem opcionais, poderão ser omitidos na chamada da função/procedimento, conforme exemplo da Lição anterior

Módulos, Funções e Procedimentos - Tópicos Avançados - Parte 6:

Você já aprendeu que ao declarar uma função ou procedimento, é possível declarar um ou mais argumentos. Ao utilizar uma função ou procedimento, você deve informar os valores para os argumentos, na mesma ordem em que foram definidos durante a declaração da função ou procedimento. 

Este processo é conhecido como passagem de parâmetros para a função ou procedimento, ou seja, ao chamar a função ou procedimento, passamos valores que serão utilizados pelo código da função ou procedimento.

Existem duas maneiras diferentes de fazer a passagem dos parâmetros e é importante para o programador, entender exatamente a diferença entre estas duas maneiras:

• Passagem por Valor                     -           ByVal
• Passagem por Referência             -          ByRef

Este conceito é muito importante e vamos entendê-lo através de alguns exemplos simples. Inicialmente vamos ver o que acontece quando utilizamos o tipo de passagem padrão, que é o tipo ByVal, onde apenas o valor do parâmetro é passado para a função/procedimento.

A Função MsgBox - Estudo Completo:

Em diversos exemplos das lições anteriores, utilizamos a função MsgBox para exibir uma janela com uma mensagem. Sempre utilizamos a função MsgBox de uma maneira básica, para exibição de trechos de texto e valor de variáveis, concatenando as partes da mensagem com o operador &. Nesta lição faremos um estudo mais detalhado da função MsgBox.

O funcionamento básico da função MsgBox é exibir uma mensagem em uma caixa de diálogo e aguardar que o usuário clique em um botão. A função retorna um valor do tipo Integer, valor este que indica qual botão o usuário clicou.

A Função InputBox - Estudo Completo:

A função InputBox exibe uma janela com uma mensagem e um campo para que o usuário digite um valor ou uma entrada de texto. 

Também são exibidos botões tais como OK e Cancelar. O valor digitado pelo usuário será o valor de retorno da função. 

O principal uso da função InputBox é permitir que o usuário digite informações e retornar o valor digitado pelo usuário para ser usado pela rotina de programação VBA que exibiu o InputBox.

Por exemplo, você poderia ter uma planilha que faz uma série de cálculos, os quais dependem da cotação diária do dólar. 

Neste caso você poderia colocar uma função InputBox, associada ao evento ao Abrir da planilha (um dos  assuntos deste módulo será sobre os eventos do Excel), de tal maneira que toda vez que a pasta de trabalho for aberta, será exibida uma caixa de diálogo, solicitando que você digite a cotação atual do dólar em relação ao Real. 

Com base neste informação, todas as planilhas são recalculadas e exibem valores atualizados, com base na última cotação fornecida pelo usuário.

Tratamento de Erros e Debug no VBA - Parte 1:

Existem diferentes tipos de erros que podem ser gerados em uma rotina de programação VBA.

Erros de sintaxe: O primeiro e mais óbvio é o erro de sintaxe, onde um comando é digitado incorretamente. Por exemplo, ao invés de uma vírgula (,) você digita um ponto-e-vírgula (;) e vice-versa. Este tipo de erro é o mais simples de detectar, pois o próprio ambiente de programação do VBA emite uma mensagem, quando um comando é digitado incorretamente (com erro de sintaxe).

Erros de lógica: Um segundo tipo de erro, este o mais difícil de ser detectado, são os chamados erros lógicos. Este tipo de erro é difícil de ser detectado porque o programa executa normalmente, sem ser emitida nenhuma mensagem de erro, porém os resultados obtidos estão incorretos. Este tipo de erro pode ter inúmeras e diferentes causas. Por exemplo, você pode ter inserido uma fórmula incorretamente, pode ter errado na criação de um teste condicional, trocando um sinal de maior (>) por um de menor (<) ou vice-versa. Para localizar este tipo de erro você tem que utilizar os recursos de execução passo a passo, disponíveis no Editor de Código VBA, os quais serão descritos nas próximas lições.

Erros de compilação: Alguns erros não tem como ser detectados pelo editor do VBA como sendo erros de sintaxe, pois são comandos que não estão em uma única linha. Por exemplo, você pode ter iniciado um laço Do, sem ter encerrado o laço com um Loop ou pode ter iniciado um laço For...Each, sem ter encerrado o laço com um comando Next. Estes erros somente são detectados quando o código é executado, ou seja, durante a primeira compilação do código VBA. Antes de executar, o VBA compila o código, ao compilar detecta o erro e exibe uma mensagem.

Erros em tempo de execução: Existem determinados tipos de erros que podem ocorrer dependendo de condições externas. Por exemplo, se você criou uma rotina VBA para importar dados de um arquivo de texto para uma planilha do Excel e este arquivo de texto fica disponível em um drive de rede. Ao executar esta rotina, o computador deve ter acesso ao referido drive de rede. Se o computador tiver acesso ao drive de rede, a rotina será executada normalmente. Se por algum motivo o computador perder o acesso à rede e a rotina for executada enquanto o computador estiver sem acesso à rede, será gerado um erro em tempo de execução. Outro exemplo de erro em tempo de execução é de uma rotina que foi programada para ler dados em um arquivo no pendrive e a rotina é executada sem que um pendrive esteja inserido na porta USB. Estes erros são chamados de erros em tempo de execução, porque somente serão detectados durante a execução do código VBA, não sendo possível detectá-los antes. Os erros em tempo de execução é que são candidatos ao tratamento de erros. Por exemplo, no código VBA, você pode prever o fato de o usuário não ter colocado o pendrive na porta USB. Neste caso, você deve usar um tratamento de erro eficiente, o qual exibe uma mensagem informando que o pendrive deve ser inserido e dando a opção de o usuário clicar em OK para continuar, ao invés de simplesmente encerrar o procedimento com uma mensagem de erro. Ou seja, o tratamento de erros é utilizado para contornar situações onde o erro foi gerado devido a uma condição externa que pode ser contornada (como por exemplo o fato de o usuário não ter inserido um pendrive na porta USB).

Tratamento de Erros e Debug no VBA - Parte 2:

Nesta lição apresentarei alguns exemplos práticos e técnicas de utilização da instrução OnError, para o tratamento de Erros.

Tratamento de Erros e Debug no VBA - Parte 4:

Nesta lição você aprenderá um dos recursos que eu considero mais importantes e mais úteis para a depuração de erros e, principalmente, para a detecção de erros lógicos: “A execução passo a passo e o acompanhamento dos valores de variáveis.”

Nesta e na próxima lição você verá exemplos práticos de utilização dos chamados recursos de depuração do editor do VBA. Com o uso destes recursos você pode fazer com que os comandos de um procedimento/função sejam executados um de cada vez. Após a execução de cada comando você pode verificar o valor das variáveis e parâmetros que estão sendo utilizados, para detectar erros lógicos, os quais estão fazendo com que os resultados obtidos não estejam corretos. A seguir farei um exemplo prático, passo a passo, onde você aprenderá como utilizar a execução passo a passo. Na próxima lição você aprenderá a utilizar o acompanhamento do valor de variáveis. 

Para aprender a utilizar estes recursos, vou utilizar a função Calcula_IRPF, criada na pasta Modulo 5 - Exercício Calcula IRPF.xlsm, a qual você criou em uma das lições do Módulo 5. 

Tratamento de Erros e Debug no VBA - Parte 5:

Na lição anterior você aprendeu a executar um procedimento passo a passo, usando a tecla de função F8. Nesta lição você aprenderá como definir quais variáveis terão seu valor monitorado, a medida que o código é executado passo a passo. Este recurso é útil para detectar variáveis que não foram inicializadas corretamente ou para identificar expressões de cálculos com erros lógicos, as quais acabam por gerar resultados incorretos.

Vamos entender como fazer o acompanhamento das variáveis, através de um exemplo prático, no qual utilizarei  um procedimento que recebe, como parâmetros, o valor dos lados de um retângulo e calcula e exibe o valor da área, do perímetro e da diagonal do retângulo.

Tratamento de Erros e Debug no VBA - Parte 6:

Nesta lição iremos executar o procedimento CalcRetangulo, passo a passo, para acompanharmos como funciona a inspeção de variáveis. 

Você deve estar com o editor VBA ainda aberto, o qual foi utilizado na última lição.

Conclusão e Visão Geral do que Foi Visto no Módulo 6:

Neste módulo tratamos de uma série de assuntos de fundamental importância para a programação VBA no Excel 2010. 

Iniciamos o capítulo falando sobre o Modelo de Objetos do Excel. Este é um conceito de fundamental importância e entendê-lo faz toda a diferença para poder programar com eficiência usando VBA no Excel.  

Em seguida detalhamos uma série de tópicos sobre a criação de funções/procedimentos, declaração de argumentos e passagem de parâmetros.  Para encerrar o capítulo abordamos sobre o tratamento de erros e os recursos de depuração do Editor do VBA.

No próximo capítulo você aprenderá sobre o objeto mais utilizado na programação VBA no Excel: “O Objeto Range”.

Módulo 2 – O Modelo de Objetos do Excel 2010:

Lição 01 – O Que é um Modelo/Hierarquia de Objetos?

Lição 02 – Principais Objetos – Descrição

Lição 03 - Navegando na Hierarquia de Objetos

Lição 03 –Objeto Application – Introdução

Lição 05 – Objeto Application – Utilização

Lição 06 – Objeto Application – Objetos Filho

Lição 07 – Objeto Application – Exibindo/Ocultando

Lição 08 – Objeto Application – Habilitando e Desabilitando Recursos do Excel

Lição 09 – Associando Macros à Teclas Especiais

Lição 10 – Obj. Application – Operações com Arquivos

Lição 11 – Obj. Application – Recálculo de Planilhas

Lição 12 – Criação de Funções e Procedimentos – Tópicos Avançados – Parte 1

Lição 13 – Criação de Funções e Procedimentos – Tópicos Avançados – Parte 2

Lição 14 – Criação de Funções e Procedimentos – Tópicos Avançados – Parte 3

Lição 15 – Criação de Funções e Procedimentos – Tópicos Avançados – Parte 4

Lição 16 – Criação de Funções e Procedimentos – Tópicos Avançados – Parte 5

Lição 17 – Criação de Funções e Procedimentos – Tópicos Avançados – Parte 6

Lição 18 – A Função MsgBox em Detalhes

Lição 19 – A Função InputBox em Detalhes

Lição 20 – Tratamento de Erros no VBA – Parte 1

Lição 21 – Tratamento de Erros no VBA – Parte 2

Lição 22 – Tratamento de Erros no VBA – Parte 3

Lição 23 – Tratamento de Erros no VBA – Parte 4

Lição 24 – Tratamento de Erros no VBA – Parte 5

Lição 25 – Tratamento de Erros no VBA – Parte 6

Lição 26 – Conclusão

No próximo capítulo faremos um estudo detalhado do objeto Range. Este objeto representa uma ou mais faixas de células em uma planilha do Excel. Já dá para ver que, sem nenhuma dúvida, o objeto Range é o objeto mais utilizado na programação VBA no Excel. Também apresentarei diversos exemplos práticos de utilização do objeto Range.

Uma Visão Geral do Módulo 7:

Nas lições deste módulo você aprenderá a trabalhar com o objeto que realmente faz o trabalho no Excel: Objeto Range. 

Falando de uma maneira bem simples, o objeto Range representa uma ou mais faixas de células em uma planilha do Excel. Como a grande maioria do trabalho (para não dizer a quase totalidade) com o Excel está relacionado a valores em uma faixa de células, fica clara a importância e a praticidade do objeto Range.

Este objeto pode ser utilizado para fazer referência ao intervalo atualmente selecionado, a um intervalo específico de células, tal como: A1:C50 ou B2:G50 ou a vários intervalos não contínuos, como por exemplo: A1:C50 mais H4:X30, mais AB45 e assim por diante. Em resumo, um objeto Range faz referência a um conjunto de células da planilha, conjunto este que é definido na declaração do objeto Range.

Além do estudo teórico, da declaração do objeto Range e do estudo de suas propriedades e métodos, apresentarei diversos exemplos de códigos de funções práticas, as quais utilizam o objeto Range. 

Sem nenhuma dúvida, você encontrará um grande número de exemplos que poderão ser facilmente adaptados para o seu próprio uso e, em alguns casos, nem sequer será necessário fazer adaptações.

Apenas para ilustrar o uso do objeto Range, vou citar duas dúvidas que recebo, frequentemente, via email e para as quais a solução passa pelo uso do objeto Range e pelo uso dos eventos do Excel e do VBA, para a criação de funções personalizadas:

• Dúvida 01: Como fazer cálculo que envolvem valores de horas no Excel? Não existe uma ou mais funções prontas, no Excel, que implementam os cálculos necessários com valores de horas. Para tal temos que criar nossas próprias funções personalizadas usando o VBA.
  
• Dúvida 02: Como faço para que o Excel formate, automaticamente, valores que estão dentro de uma determinada faixa. Por exemplo, formatar em negrito e vermelho, todos os valores da faixa de B1 a B100, que estiverem entre 50 e 100? 

Para solucionar esta questão, você poderia usar o recurso de Formatação Condicional, descrito no Curso de Excel Básico. Porém com o uso de programação VBA, você pode automatizar este processo, além de ter um controle bem maior sobre as formatações a serem aplicadas e sobre o número de condições que podem ser levadas em consideração.

Você perceberá que com o uso objeto Range, muitas tarefas que antes pareciam de difícil resolução, ou até mesmo pareciam impossíveis de resolver com o Excel, parecerão mais simples e perfeitamente possíveis de serem solucionadas.

Eu considero os conhecimentos dos Módulos 5 e 6 a base fundamental para a programação VBA. Não tem como programar em VBA, sem os conhecimentos destes capítulos básicos.

Mas é a partir deste módulo que você começará a ver a aplicação prática do VBA, para solução de problemas reais, que tantas vezes parecem insolúveis (e realmente o são), só com o uso das funções e comandos do Excel.

Em resumo, a partir deste módulo você irá constatar, na prática, todas as opções que se abrem com o uso da programação VBA no Excel. Então, sejam bem vindos ao estudo do objeto Range.

Apresentação Formal do Objeto Range:

Vamos fazer uma rápida recapitulação de como é a hierarquia de objetos do Modelo de Objetos do Excel? Vamos lá.

• Objeto Application: O objeto de nível mais alto é o objeto Application, o qual representa a própria aplicação do Excel, ou seja, Application = Excel. Por exemplo, quando você abre o Excel, é criada uma instância do objeto Application, na memória do computador.
  
  
• Objeto Workbook: O objeto Workbook representa uma pasta de trabalho do Excel, ou seja, um arquivo .XLSX ou XLSM. Por exemplo, você pode abrir o Excel (objeto Application) e abrir um ou mais arquivos .XLSX ou XLSM (objetos Workbook). Por isso que o objeto Application, tem uma coleção chamada Workbooks, ou seja, uma coleção que contém uma referência a todas as pastas de trabalho abertas. Em resumo: Pasta de Trabalho = Arquivo .XLSX ou XLSM = objeto Workbook.
  
  
• Objeto Worksheet: Dentro de uma mesma pasta de trabalho (arquivo .XLSX ou .XLSM – estou sendo repetitivo para fixar os conceitos), pode haver várias planilhas (objetos Worksheet). Por padrão, ao criar uma nova pasta de trabalho, são criadas, dentro da nova pasta de trabalho, três planilhas: Plan1, Plan2 e Plan3. O objeto Worksheet é utilizado para fazer referência a uma planilha de uma pasta de trabalho. Por isso que o objeto Workbook (pasta de trabalho), contém uma coleção chamada Worksheets (planilhas), através da qual é possível fazer referência a todas as planilhas da pasta de trabalho atual.
  
  
• Objeto Range: Em cada planilha de uma pasta de trabalho, está disponível um grande número de células. Na verdade, no Excel 2010, estão disponíveis: 17.179.869.184 células (resultado da multiplicação do número de linhas (1048576) pelo número de colunas (16384). Para representar um intervalo da planilha, por exemplo de A1 até C100, é utilizado o objeto Range. Em resumo Range = uma faixa de células de uma planilha.

Após termos situado exatamente a posição do objeto Range, na hierarquia de objetos do Excel, podemos iniciar o estudo deste objeto.

Nesta lição mostrarei que existem inúmeras maneiras diferentes de criar um objeto Range, usando programação VBA.

Nesta lição falarei um pouco mais sobre como funciona a propriedade Cells, do objeto Range e como acessar células específicas, dentro de uma faixa, usando os índices da propriedade Cells

Nesta lição continuarei a mostrar diferentes maneiras para retornar um objeto do tipo Range, no código VBA.

Uma faixa (Range) é formada por uma ou mais linhas e uma ou mais colunas. Com isso o objeto Range oferece propriedades e coleções para trabalhar diretamente com uma linha ou coluna do objeto Range.

Nesta e na próxima lição faremos o estudo dos seguintes itens:

• Propriedade Column
• Coleção Columns
• Propriedade Row
• Coleção Rows

Nesta lição apresentaremos a descrição e exemplos de uso da propriedade Row e da coleção Rows, do objeto Range.

Nesta lição continuaremos a estudar uma série de propriedades, métodos e coleções do objeto Range.

Na parte final do módulo, apresentarei uma série de exemplos práticos, baseados no objeto Range e no uso das propriedades, métodos e coleções que estudaremos nestas e nas próximas lições.

Nesta lição continuaremos o estudo dos principais métodos, propriedades e coleções do objeto Range.

Nesta lição continuaremos o estudo dos principais métodos, propriedades e coleções do objeto Range.

Nesta lição continuaremos o estudo dos principais métodos, propriedades e coleções do objeto Range.

Nesta lição continuaremos o estudo dos principais métodos, propriedades e coleções do objeto Range.

O Método FindNext:

O método FindNext é utilizado para continuar uma pesquisa que tenha começado com o método Find. Localiza a próxima célula que coincida com as mesmas condições que foram definidas para a última execução do método Find e retorna um objeto Range representando essa célula. Não afeta a seleção da célula ativa.

Nesta lição continuaremos o estudo dos principais métodos, propriedades e coleções do objeto Range.

Nesta lição continuaremos o estudo dos principais métodos, propriedades e coleções do objeto Range.

Nesta lição continuaremos o estudo dos principais métodos, propriedades e coleções do objeto Range.

Nesta lição continuaremos o estudo dos principais métodos, propriedades e coleções do objeto Range.

A partir desta lição, até o final deste módulo, apresentarei uma série de exemplos práticos, de utilização do objeto Range.

Nos exemplos mostro como usar o objeto Range no VBA, para a resolução de uma série de problemas práticos, do dia-a-dia.

Com certeza você encontrará exemplos úteis, que irão ajudá-lo a resolver problemas que você está enfrentando com o uso do Excel.

Nesta lição vamos ver um exemplo de uma macro que solicita ao usuário que seja digitado um valor. 

Em seguida, a macro irá procurar em todas as planilhas de todas as pastas de trabalho abertas, por células que contém o valor digitado e irá colocar estas células em destaque, alterando a cor de fundo destas células. Inicialmente vamos apresentar o código. 

Em seguida vamos vê-lo em funcionamento e por último apresentar alguns comentários para esclarecer o funcionamento do código.

Nesta lição vamos continuar com mais alguns exemplos de uso do objeto Range e de técnicas relacionadas a operações em linhas de dados na planilha

Exemplo: Neste exemplo mostrarei como verificar se a seleção efetuada pelo usuário é composta de uma única coluna ou de uma única linha. 

Esta técnica é útil quando, como parte da entrada de dados de um programa no Excel, o usuário deve selecionar uma faixa de dados que seja uma única coluna ou uma única linha. 

Neste caso, se a seleção não for uma única linha ou uma única coluna, você pode emitir uma mensagem, solicitando que o usuário refaça a seleção. Isso evita erros.

Nesta lição apresentarei alguns exemplos de utilização do objeto Range, para a solução de situações práticas envolvendo colunas no Excel.

Nesta lição apresentarei alguns exemplos de utilização do Objeto Range para operações de Editar, Copiar e Colar linhas e colunas. Também apresentarei alguns exemplos do uso do VBA e do objeto Range, para acessar e definir o valor de células em uma planilha do Excel.

Nesta lição apresentarei mais alguns exemplos práticos de utilização do Objeto Range.

Nesta lição apresentarei mais alguns exemplos práticos de utilização do Objeto Range. Você aprenderá a usar o código VBA para definir fórmulas em uma ou mais células.

Nesta lição apresentarei mais alguns exemplos práticos de utilização do Objeto Range. Nos exemplos desta lição você aprenderá a determinar os limites de uma faixa, tal como detectar qual a última entrada em uma linha ou coluna.

Conclusão e uma Resumo do que foi Visto no Módulo 7:

Nas lições deste módulo você aprendeu a trabalhar com o objeto que realmente faz o trabalho no Excel: Objeto Range. 

Falando de uma maneira bem simples, o objeto Range representa uma ou mais faixas de células em uma planilha do Excel. Como a grande maioria do trabalho (para não dizer a totalidade) com o Excel está relacionado a valores em uma faixa de células, fica clara a importância do objeto Range.

Módulo 7 – O Objeto Range – Métodos, Propriedades e Exemplos Práticos

Lição 01 – Apresentação do Objeto Range

Lição 02 – Outras Maneiras de Criar um Objeto Range

Lição 03 – Mais Maneiras de Criar um Objeto Range

Lição 04 – Objeto Range – Principais Métodos e Propriedades - Parte 1  

Lição 05 – Objeto Range – Principais Métodos e Propriedades - Parte 2  

Lição 06 – Objeto Range - Principais Métodos e Propriedades – Parte 3  

Lição 07 – Objeto Range - Principais Métodos e Propriedades – Parte 4

Lição 08 – Objeto Range - Principais Métodos e Propriedades – Parte 5

Lição 09 – Objeto Range - Principais Métodos e Propriedades – Parte 6

Lição 10 – Objeto Range - Principais Métodos e Propriedades – Parte 7  

Lição 11 – Objeto Range - Principais Métodos e Propriedades – Parte 8  

Lição 12 – Objeto Range - Principais Métodos e Propriedades – Parte 9  

Lição 13 – Objeto Range - Principais Métodos e Propriedades – Parte 10

Lição 14 – Objeto Range - Principais Métodos e Propriedades – Parte 11

Lição 15 – Objeto Range - Principais Métodos e Propriedades – Parte 12

Lição 16 - Objeto Range - Exemplos Práticos - Parte 1      

Lição 17 - Objeto Range - Exemplos Práticos - Parte 2      

Lição 18 - Objeto Range - Exemplos Práticos - Parte 3      

Lição 19 - Objeto Range - Exemplos Práticos - Parte 4      

Lição 20 - Objeto Range - Exemplos Práticos - Parte 5      

Lição 21 - Objeto Range - Exemplos Práticos - Parte 6      

Lição 22 - Objeto Range - Exemplos Práticos - Parte 7

Lição 23 - Objeto Range - Exemplos Práticos - Parte 8

Lição 24 - Objeto Range - Exemplos Práticos - Parte 9

Lição 25 – Resumo do Módulo

Existe mais sobre VBA no Excel??

Muito, muito, muito, muito, mas MUIIIIIIIIIIIIIIIIIIIIIIIIITO mesmo. Dentro do escopo previsto para este curso vamos parando por aqui. Em breve vou lançar um curso só sobre Programação VBA no Excel, onde vou abordar bem mais itens e avançar, UM POUCO MAIS, em relação a tudo o que existe sobre VBA no Excel.

Módulo 8 - Uma Visão Geral do que Será Visto Neste Módulo:

Este será um módulo só com exemplos avançados, práticos e úteis, no uso de Fórmulas e Funções no Excel. 

Você verá que mesmo com todo o poderio das Macros e da Programação VBA, ainda é possível fazer pequenos milagres, utilizando somente as fórmulas do Excel.

Vamos iniciar o módulo tratando sobre Fórmulas Matriciais. Este é um assunto “muito pouco” utilizado no Excel, por pura falta de conhecimento dos usuários. É possível fazer “pequenos e grandes milagres” usando fórmulas matriciais. Cálculos que seriam bem mais difíceis de serem feitos com as fórmulas tradicionais podem ser, muito rapidamente, feitos usando fórmulas matriciais. Por isso vou mostrar para você o que são e como utilizar as fórmulas matriciais.

Veremos diversos exemplos práticos, de uso de fórmulas matriciais bem complexas, as quais serão explicadas em detalhes, passo a passo, mostrando como funcionam e, principalmente, qual a lógica envolvida. Eu garanto que você irá se surpreender com os milagres que podem ser feitos com fórmulas e funções matriciais no Excel.

Em seguida vamos para outro ponto que gera muitas dúvidas: Funções de Pesquisa. Vamos começar pelas tradicionais PROCV e PROCH e depois veremos exemplos com várias outras funções de pesquisa. Apresentarei exemplos completos, onde poderemos solucionar problemas aparentemente complexos, apenas utilizando as funções de pesquisa do Excel. Tenho certeza que as lições sobre funções de pesquisa serão bastante úteis para o amigo leitor.

E para encerrar o módulo (e o curso), com chave de ouro, um assunto que praticamente não é abordado em cursos de Excel, assunto este que é uma das novidades mais interessantes introduzidas pelo Excel 2010: Os Mini gráficos, ou Gráficos Sparkline, como são mais comumente chamados.

Como já citado, os Sparkline (ou Minigráficos) são uma das novidades no Excel 2010. Um gráfico sparkline é um pequeno gráfico (minigráfico), inserido em uma única célula de folha de cálculo que fornece uma representação visual de dados. Podemos utilizar gráficos sparkline para mostrar tendências numa série de valores, tais como aumentos ou diminuições sazonais e ciclos econômicos, ou para realçar valores máximos e mínimos. O gráfico sparkline deverá ser colocado junto dos respetivos dados para maior impacto.

Vamos ver um pouco sobre a teoria dos Minigráficos e depois, como não podia deixar de ser, faremos exemplos práticos, passo a passo, mostrando como criar, configurar e formatar os Minigráficos.

Este módulo final traz assuntos bem interessantes e úteis. Tenho certeza que você irá se surpreender com os exemplos deste módulo.

Fica aqui o meu agradecimento por você ter seguido nesta jornada comigo (jornada que foi realmente longa). Fico no aguardo dos teus comentários, sugestões, críticas e elogios (por que não), através do e-mail webmaster@juliobattisti.com.br

Não deixe de conferir novidades diárias em http://www.juliobattisti.com.br

O Que São e Como Funcionam as Fórmulas Matriciais:

Este é um recurso “muito pouco” utilizado no Excel, por pura falta de conhecimento dos usuários. É possível fazer “pequenos e grandes milagres” usando fórmulas matriciais. Cálculos que seriam bem mais difíceis de serem feitos com as fórmulas tradicionais podem ser, muito rapidamente, feitos usando fórmulas matriciais. Por isso vou mostrar para você o que são e como utilizar as fórmulas matriciais.

Fórmulas matriciais são um recurso realmente muito útil, muito fácil de utilizar e capaz de solucionar problemas complexos. Por isso vale realmente a pena conhecer este recurso. Vamos entender este recurso fazendo um estudo comparativo em relação as fórmulas tradicionais no Excel. Uma fórmula tradicional faz um cálculo e retorna um valor como resultado. Por exemplo, a função Soma recebe como parâmetro uma ou mais faixas de células e retorna como resultado, a soma das células. Já uma fórmula matricial pode fazer vários cálculos em um ou mais vetores ou matrizes e retornar um ou múltiplos valores. 

Entenda-se aqui Vetores ou Matrizes como sendo uma faixa de células. Por exemplo A1:A10 é um vetor (que nada mais é do que uma matriz de uma só dimensão). Já A1:C10 é uma matriz de duas dimensões (10 linhas e 3 colunas). É o mesmo conceito de Vetores e Matrizes lá da matemática e da física do Segundo Grau. Em palavras mais simples uma matriz é simplesmente um conjunto de itens (ou de células, usando a linguagem do Excel). No Excel, esses itens podem residir em uma única linha (denominada matriz horizontal unidimensional), em uma coluna (matriz vertical unidimensional) ou em várias linhas e colunas (matriz bidimensional). Você não pode criar matrizes tridimensionais no Excel.

Uma fórmula matricial é capaz de executar vários cálculos em um ou mais itens de uma matriz. As fórmulas matriciais podem retornar um ou vários resultados. Por exemplo, você pode inserir uma fórmula matricial em um intervalo de células e usar a fórmula matricial para calcular uma coluna ou linha de subtotais. 

Você também pode inserir uma fórmula matricial em uma única célula e calcular um valor único. Mas muito melhor do que teoria é realmente ver como funcionam e como utilizar as fórmulas matriciais.

Entendendo as Fórmulas Matriciais:

Vamos a um pouco mais de teoria, onde apresento detalhes importantes sobre o uso das Fórmulas Matriciais. É importante conhecer estes detalhes, pois muitos dos erros que ocorrem no uso das fórmulas matriciais, podem ser evitados com o conhecimento destes pequenos detalhes.

Uma fórmula matricial, tirando alguns detalhes, tem uma sintaxe muito semelhante a das fórmulas tradicionais. Elas começam com um sinal de igual e podemos usar qualquer função interna do Excel nas fórmulas matriciais. 

A diferença fundamental é que, ao usar uma fórmula matricial, você, OBRIGATORIAMENTE, deve pressionar CTRL+SHIFT+ENTER para inserir a fórmula. Ao fazer isso o Excel delimita a fórmula matricial com chaves { }. Se você digitar as chaves manualmente, a fórmula não será reconhecida como sendo uma fórmula matricial e será convertida em uma cadeia de texto e não funcionará.

Regras para a Inserção e Alteração de Fórmulas Matriciais:

A regra básica, obrigatória, para a criação de uma fórmula matricial resume-se no seguinte: pressione CTRL+SHIFT+ENTER sempre que precisar inserir ou editar uma fórmula matricial. Essa regra se aplica a fórmulas com uma ou com várias células. E se for uma fórmula que se aplica a várias células, antes de criar a fórmula e pressionar CTRL+SHIFT+ENTER, você deve selecionar a faixa a qual a fórmula será aplicada.

Ao trabalhar com  fórmulas matriciais que se aplicam a um intervalo de células, tenha em mente as seguintes regras básicas:

• Selecione o intervalo de células que conterá os resultados antes de inserir a fórmula. Você fez isso no primeiro exemplo da lição anterior, quando, antes de inserir a fórmula matricial, selecionou as células de E2 a E11.
  
  
• Não altere o conteúdo de uma célula individual em uma fórmula de matriz. Como experiência, selecione a célula E3 na pasta de trabalho de exemplo da lição anterior e pressione DELETE. Será emitida a tradicional mensagem de erro, informando que você não pode alterar células individuais de uma matriz.
  
  
• Mova ou exclua toda a fórmula de matricial, mas não mova ou exclua parte dela.
  
• DICA: Para excluir uma fórmula de matriz, selecione a fórmula inteira (por exemplo, =C2:C11*D2:D11), pressione DELETE e, em seguida, pressione CTRL+SHIFT+ENTER.
  
  
• Não insira células em branco nem exclua células em uma fórmula de matricial com várias células.

A seguir eu vou mostrar um exemplo, aparentemente simples, mas que mostra toda a mágica, todo o trabalho de que são capazes as fórmulas matriciais, quando combinadas com um pouco de raciocínio lógico por parte do usuário. Vamos ver um exemplo bastante poderoso e que demonstra do que realmente são capazes as fórmulas matriciais

Vamos a alguns exemplos práticos, com o uso de Fórmulas e Funções Matriciais. 

Para os nossos exemplos vamos utilizar a planilha Lista de Pedidos com Valor Data e País de Destino.xlsx, a qual está disponível na pasta de Exemplos que acompanha este curso. 

Vamos a mais alguns exemplos práticos, com o uso de Fórmulas e Funções Matriciais.
Para os nossos exemplos vamos utilizar a planilha Lista de Pedidos com Valor Data e País de Destino.xlsx,

Vamos a mais alguns exemplos práticos, com o uso de Fórmulas e Funções Matriciais. 

Vamos a mais alguns exemplos práticos, com o uso de Fórmulas e Funções Matriciais. 

Vamos a mais alguns exemplos práticos, com o uso de Fórmulas e Funções Matriciais. 

Um Desafio para Você?

Nesta lição vou fazer um pouco diferente. Vou apresentar o problema proposto, a fórmula que soluciona o problema e deixarei a cargo do amigo leitor pensar, “matutar” como se diz em alguns estados, para tentar entender a lógica por trás da fórmula proposta.

Aceitas o desafio?

Objetivo: Nesta lição você aprenderá sobre o uso da Função PROCV e suas limitações.

A função PROCV é utilizada para localizar um valor na primeira coluna de uma tabela de dados no Excel e retorna um valor, na mesma linha, de outra coluna na tabela de dados.

Objetivo: Nesta lição você aprenderá sobre o uso da Função PROCH e suas limitações.

A função PROCH é utilizada para localizar um valor específico na linha superior de uma tabela ou matriz de valores e retorna um valor na mesma coluna de uma linha especificada na tabela ou matriz. O “H “de PROCH significa "Horizontal."

O uso da função PROCH é bem menos comum do que o uso da função PROCV, pois os dados, quase sempre, estão em um formato de tabela tradicional, como é o caso da tabela de produtos, que utilizamos na Lição anterior. Utilizamos PROCH quando os valores de comparação (ou seja, valores que serão pesquisados) estiverem localizados em uma linha ao longo da parte superior de uma tabela de dados e você quiser observar um número específico de linhas mais abaixo. Observem que PROCV era utilizado quando os valores a serem pesquisados estavam na coluna bem a esquerda de uma tabela de dados; já PROCH é utilizada quando os valores a serem pesquisados estiverem na primeira linha da tabela de dados.

Nesta lição vamos aprender a utilizar a seguinte função de pesquisa: Escolher.

Nesta lição vamos aprender a utilizar as seguintes funções de pesquisa: Áreas e Corresp.

Nesta lição vamos aprender a utilizar a seguinte função de pesquisa: Desloc.

Nesta lição vamos aprender a utilizar as seguintes funções de pesquisa: Endereço e Corresp.

A partir desta lição vamos aprender sobre uma das novidades do Excel 2010: Gráficos Sparkline ou Mini gráficos. 

Como já citado, os Sparkline (ou Minigráficos) são uma das novidades no Excel 2010. 

Um gráfico sparkline é um pequeno gráfico (minigráfico), inserido em uma única célula de folha de cálculo que fornece uma representação visual de dados. 

Podemos utilizar gráficos sparkline para mostrar tendências numa série de valores, tais como aumentos ou diminuições sazonais e ciclos econômicos, ou para realçar valores máximos e mínimos. O gráfico sparkline deverá ser colocado junto dos respectivos dados para maior impacto.

A partir desta lição vamos aprender, com exemplos práticos sobre uma das novidades do Excel 2010: Gráficos Sparkline ou Minigráficos.

Para os exemplos deste capítulo utilizaremos a planilha Exemplos Sparkline - 01.xlsx, a qual está disponível na pasta de Exemplos que acompanha este curso

Grupos de Mini Gráficos:

Na lição anterior nós aprendemos a criar Minigráficos dos três tipos disponíveis: Linha, Coluna e Perdas/Ganhos. Você deve ter observado (Figura 8.31), que o minigráfico do tipo Perdas/Ganhos está, digamos, “meio estranho”, pois não faz diferenciação visual entre Ganhos e Perdas em relação ao período anterior. 

Nesta lição aprenderemos a formatar e alterar algumas configurações dos minigráficos, que é justamente onde poderemos ajustar os minigráficos do tipo Perdas/Ganhos, para que seja feita uma diferenciação visual, gráfica, em relação a ter havido uma perda ou um ganho entre os períodos. Vamos continuar trabalhando na planilha do Exemplo anterior. Inicialmente vamos entender o conceito de Grupos de Minigráficos.

A guia Ferramentas de Minigráfico tem um menu só para configurarmos o Eixo dos minigráficos. Vamos a um exemplo prático.

Vamos juntar tudo o que nós aprendemos até agora e, é claro, queimar alguns neurônios para ver como resolver um problema prático, utilizando mini gráficos.

Conclusão e Visão Geral do que foi Visto no Módulo 8:

Nas lições deste módulo você aprendeu a trabalhar funções avançadas. Iniciamos pelas funções Matriciais, com as quais fizemos “pequenos milagres”. 

Depois fomos ao estudo das funções de pesquisa, com diversos exemplos práticos, passo a passo. 

E finalizamos o módulo (e o Curso), com o recurso de Sparklines, ou em bom Português: Mini Gráficos.

Módulo 8 – Fórmulas Matriciais, Funções de Pesquisa e Mini Gráficos:

Lição 01 - Fórmulas Avançadas e Minigráficos

Lição 02 - Fórmulas Matriciais - Introdução e um Exemplo

Lição 03 - Fórmulas Matriciais - Um Exemplo Intrigante

Lição 04 - Fórmulas Matriciais - Exemplos Práticos - Parte 1

Lição 05 - Fórmulas Matriciais - Exemplos Práticos - Parte 2

Lição 06 - Fórmulas Matriciais - Exemplos Práticos - Parte 3

Lição 07 - Fórmulas Matriciais - Exemplos Práticos - Parte 4

Lição 08 - Fórmulas Matriciais - Exemplos Práticos - Parte 5

Lição 09 - Fórmulas Matriciais - Exemplos Práticos - Parte 6

Lição 10 - Funções de Pesquisa - PROCV

Lição 11 - Funções de Pesquisa - PROCH

Lição 12 - Funções de Pesquisa - Escolher

Lição 13 - Funções de Pesquisa - Áreas e Corresp

Lição 14 - Funções de Pesquisa - Desloc

Lição 15 - Funções de Pesquisa - Endereço e Corresp

Lição 16 - Gráficos Sparkline - Minigráficos - Introdução

Lição 17 - Gráficos Sparkline - Minigráficos - Como Criar 

Lição 18 - Gráficos Sparkline - Minigráficos - Formatações

Lição 19 - Gráficos Sparkline - Minigráficos - Outras Formatações

Lição 20 - Gráficos Sparkline - Minigráficos - Exemplo Prático

Lição 21 - Resumo

Com isso encerramos este curso, onde procurei mostrar, através de exemplos práticos, como utilizar os principais e mais úteis recursos avançados do Excel 2010. É claro que existe muito mais sobre Excel Avançado. Mas dentro da proposta deste curso já está de bom tamanho.

Se você quiser sugerir novos tópicos a serem inclusos em futuras versões deste curso, para enviar suas críticas, elogios e sugestões, é só entrar em contato através do e-mail webmaster@juliobattisti.com.br ou no Facebook: https://www.facebook.com/autorjuliobattisti 

            Diariamente confira o lançamento de novos livros, e-books, vídeo aulas, cursos online, artigos, cursos e livros gratuitos online e um mar de conhecimento no meu site, em http://www.juliobattisti.com.br

Cordialmente,

Júlio Battisti