Excel Avançado

Excel Avançado

(Parte 1 de 5)

Excel Avançado em 120 Lições

Autor: Júlio Cesar Fabris Battisti webmaster@juliobattisti.com.br

Autor: Júlio Cesar Fabris Battisti
Site:w.juliobattisti.com.br
É proibido usar este material em treinamentos ou em sala de aulaPágina 2 de 417

Curso de Excel Avançado em 120 Lições

Nota sobre direitos autorais:

Este ebook é de autoria de Júlio Battisti, sendo comercializado diretamente através do site w.juliobattisti.com.br ou através do site de leilões Mercado Livre: w.mercadolivre.com.br, pelo usuário GROZA. Nenhum outro usuário, pessoa ou site está autorizado a vender este ebook.

Ao adquirir este ebook você tem o direito de lê-lo na tela do seu computador e de imprimir quantas cópias desejar. É vetada a distribuição deste arquivo, mediante cópia ou qualquer outro meio de reprodução, para outras pessoas. Se você recebeu este ebook através do e-mail ou via ftp de algum site da Internet, ou através de um CD de Revista, saiba que você está com uma cópia pirata, não autorizada. A utilização de uma cópia pirata, não autorizada, é crime de Violação de Direitos Autorais, sujeita a pena de Cadeia. Denuncie o site ou revista que está disponibilizando a cópia, através do e-mail webmaster@juliobattisti.com.br

O valor cobrado por este arquivo é praticamente simbólico, pelas horas e horas de trabalho que ele representa. Novos cursos somente podem ser desenvolvidos graças a honestidade de pessoas que adquirem o arquivo do curso e não o distribuem livremente para outras pessoas. Se você recebeu uma cópia deste arquivo sem tê-la adquirido diretamente com o autor, seja honesto, entre em contato com o autor, através do e-mail webmaster@juliobattisti.com.br para regularizar esta cópia. Ao regularizar a sua cópia você irá remunerar, mediante uma pequena quantia, o trabalho do autor e incentivar que novos trabalhos sejam disponibilizados.

Se você tiver sugestões sobre novos cursos que gostaria de ver disponibilizados, entre em contato pelo e-mail: webmaster@juliobattisti.com.br

Visite periodicamente o site w.juliobattisti.com.br para ficar por dentro das novidades:

• Cursos de informática. • Artigos e dicas sobre Certificações da Microsoft.

• Artigos sobre Carreira e Trabalho.

• Dicas de livros e sites sobre diversos assuntos.

• Simulados gratuitos, em português, para os exames da Microsoft.

Autor: Júlio Cesar Fabris Battisti
Site:w.juliobattisti.com.br
É proibido usar este material em treinamentos ou em sala de aulaPágina 3 de 417

Curso de Excel Avançado em 120 Lições

Conheça outros livros do autor Júlio Battisti:

Manual de Estudos Para o Exame 70-217 - 752 páginas

Chega ao mercado editorial mais um aguardado lançamento da Axcel Books Editora – Certificação Microsoft – Guia de Estudos Para o MCSE – Exame 70-217, onde o autor Júlio Battisti descreve, de forma detalhada e com exemplos passo-apasso, todos os tópicos que fazem parte do programa oficial da Microsoft para o exame de certificação 70-217. A obra apresenta e explica desde os princípios básicos, incluindo os fundamentos do Active Directory; passando por serviços tais como DNS, gerenciamento de compartilhamentos, Master Operations, permissões NTFS, Grupos de Usuários, Unidades Organizacionais e Group Policy Objects, os GPOs; além de ainda tratar de questões como a configuração de Auditoria de Objetos, o gerenciamento do Schema, entre outros.

Um cursocompleto de Active Directory para o Windows 2000 Server.

Windows Server 2003 – Curso Completo – 1568 páginas

O livro ensina desde os fundamentos básicos do Active Directory, passando pela instalação do Windows Server 2003 e por dicas sobre o projeto, implementação e migração do Windows 2000 Server para o Windows Server 2003. Você aprenderá, em detalhes, sobre os serviços de compartilhamento de arquivos e impressoras, segurança, como tornar o Windows Server 2003 um servidor Web, aprenderá sobre os serviços de rede: DNS, DHPC, WINS, RRAS, IPSec, Análise de Segurança, Group Policy Objects e muito mais. Confira, vale a pena.

Manual de Estudos Para o Exame 70-216 - 712 páginas

Neste aguardado lançamento da Axcel Books Editora – Certificação Microsoft – Guia de Estudos Para o MCSE –Exame 70-216, o autor Júlio Battisti descreve, de forma detalhada e com exemplos passo-a-passo, todos os tópicos que fazem parte do programa oficial da Microsoft para o exame de certificação. A obra apresenta e explica desde os princípios básicos, incluindo os fundamentos do protocolo TCP/IP; passando por instalação, configuração e administração do DNS, DHCP, WINS e RRAS; além de ainda tratar de questões quanto ao roteamento, NAT, Certificados Digitais, IPSec, entre outros.

Autor: Júlio Cesar Fabris Battisti
Site:w.juliobattisti.com.br
É proibido usar este material em treinamentos ou em sala de aulaPágina 4 de 417

Curso de Excel Avançado em 120 Lições

Windows XP Home & Professional – 840 páginas

O novo mundo do Windows XP, que representa a nova era do sistema operacional para usuários e administradores está reunido nesta obra. Júlio Battisti apresenta a nova interface do sistema, completamente redesenhada e com a experiência de um profissional certificado da Microsoft. Na obra, os leitores irão aprender a implementar, configurar e utilizar o Windows XP, desvendando as funcionalidades, além das configurações de segurança, de desempenho e de estabilidade do sistema. O livro aborda ainda toda a parte de Internet do Windows XP – conectando e usando a Internet; configurando o firewall de conexão; além dos novos recursos do correio eletrônico. Veja também os detalhes sobre o Active Directory, as configurações de rede e protocolo TCP/IP, criptografia, registry do Windows, entre tantos outros assuntos. O leitor ainda vai poder contar com um capítulo exclusivo e um simulado com 100 questões/respostas destinados aos interessados no exame de Certificação 70-270 da Microsoft.

ASP.NET: Uma Nova Revolução Na Criação de Sites e Aplicações Web – 730 páginas

Conheça o ASP.NET, a mais nova versão do ASP, que representa uma mudança no modelo de desenvolvimento de aplicações Web. O livro traz todas as informações necessárias sobre o assunto, inclusive os detalhes da iniciativa .NET, o CLR, o MSIL e o C#, a nova linguagem da Microsoft. Aprenda os novos controles do ASP.NET e como utilizar o Visual Studio.NET para criar páginas ASP.NET. Veja ainda como criar formulários avançados para edição de dados, configurar as opções de segurança do Windows 2000, do IIS e do ASP.NET, além de aprender como criar páginas ASP.NET para as mais diversas funções.

Curso Completo – 816 páginas

SQL Server 2000: Administração & Desenvolvimento

O lançamento é destinado aos usuários/leitores da versão anterior do SQL Server, o SQL 7, além de redes de computadores em geral, Windows 2000 Server, TCP/IP, Bancos de Dados em geral, do Microsoft Access e do Visual Basic. O leitor aprenderá na obra destinada do iniciante ao avançado detalhes sobre o modelo de dados relacional, como instalar o SQL Server 2000 em diferentes plataformas, além da criação e administração de bancos de dados, tabelas e outros objetos. Aprenda ainda Como criar páginas ASP que acessam os dados do SQL Server 2000.

Autor: Júlio Cesar Fabris Battisti
Site:w.juliobattisti.com.br
É proibido usar este material em treinamentos ou em sala de aulaPágina 5 de 417

Curso de Excel Avançado em 120 Lições

Para que você possa acompanhar as lições deste curso é necessário que você já tenha preenchido os seguintes pré-requisitos:

• Curso básico de Excel em 120 Lições, disponível no seguinte endereço: http://www.juliobattisti.com.br/excel120/excel120.asp ou conhecim ento equivalente.

• Conhecimento dos aspectos básicos do Modelo Relacional de banco de dados.

Para saber mais sobre o Modelo Relacional de dados, consulte as seguintes lições do curso básico de Access, disponível no meu site, no seguinte endereço: o http://www.juliobattisti.com.br/accbasico/modulo1/licao2.htm o http://www.juliobattisti.com.br/accbasico/modulo1/licao3.htm o http://www.juliobattisti.com.br/accbasico/modulo1/licao4.htm o http://www.juliobattisti.com.br/accbasico/modulo1/licao5.htm o http://www.juliobattisti.com.br/accbasico/modulo1/licao6.htm

Estes conhecimentos serão fundamentais nas lições sobre Listas, Tabelas Dinâmicas e Análise de Hipóteses.

Algumas palavras do autor:

Este curso foi criado com o objetivo de ajudá-lo a entender e a utilizar no seu dia-a-dia, as funções avançadas do Microsoft Excel. O curso é composto de 6 Módulos, com 20 lições por módulo.

Em cada lição são apresentados conceitos teóricos, seguidos por exemplos práticos, passo-a-passo, para que você possa consolidar os conceitos teóricos apresentados.

Um bom estudo a todos e espero, sinceramente, que este curso possa ajudá-los a utilizar melhor o Microsoft Excel.

Autor: Júlio Cesar Fabris Battisti
Site:w.juliobattisti.com.br
É proibido usar este material em treinamentos ou em sala de aulaPágina 6 de 417

Curso de Excel Avançado em 120 Lições

Módulo 1 – Trabalhando com Listas de dados9
Lição 01: Introdução e conteúdo do Módulo 19
Lição 02: Importando de dados – Parte 112
Lição 03: Importando de dados – Parte 218
Lição 04: Importação de dados – Parte 320
Lição 05: Ordenando Listas – Parte 123
Lição 06: Ordenando Listas – Parte 226
Lição 07: Criando Listas de ordenação personalizadas29
Lição 08: Filtrando dados em uma Lista – Introdução e conceitos3
Lição 09: Filtrando dados – o comando AutoFiltro – Parte 135
Lição 10: Filtrando dados – o comando AutoFiltro – Parte 238
Lição 1: Filtrando dados – o comando AutoFiltro – Parte 341
Lição 12: Localizando registros individuais: Dados -> Formulário4
Lição 13: Operadores de filtragem: A diferença entre “E” e “OU”47
Lição 14: Definindo e usando faixas de critérios – Introdução50
Lição 15: O Primeiro exemplo usando faixas de critérios54
Lição 16: Usando Critérios de Texto59
Lição 17: Usando Critérios Calculados62
Lição 18: Usando Subtotais para análise de dados6
Lição 19: Exercícios com faixas de critérios69
Lição 20: Resumo do Módulo 170
Módulo 2 – Mais sobre Listas. Exportação e Importação de dados71
Lição 01: Introdução e conteúdo do Módulo 271
Lição 02: Mais opções e um exemplo com Subtotais72
Lição 03: Funções para análise dos dados de uma lista – Parte I7
Lição 04: Funções para análise dos dados de uma lista – Parte I82
Lição 05: Funções para análise dos dados de uma lista – Parte I85
Lição 06: Funções para análise dos dados de uma lista – Parte IV8
Lição 07: Definindo critérios para validação de dados91
Lição 08: Consultas baseadas em dados da Internet – Conceitos96
Lição 09: Consultas baseadas em dados da Internet – Exemplos9
Lição 10: Uma ferramenta de pesquisa – Microsoft Query104
Lição 1: Definindo a fonte de dados a ser utilizada10
Lição 12: Criação de consultas básicas com o Microsoft Query112
Lição 13: Definição de critérios em consultas – Parte I118
Lição 14: Definição de critérios em consultas – Parte I127
Lição 15: Alterando consultas com o Microsoft Query133
Lição 16: Totalização de dados com o Microsoft Query137
Lição 17: Ex. 02 - Criação de consultas e análise de dados com o Query142
Lição 18:Ex. 03 - Criação de consultas e análise de dados com o Query.......146
Lição 19: Exercícios do Módulo 2149
Lição 20: Resumo do Módulo 2150
Módulo 3 – Tabelas Dinâmicas151
Lição 01: Tabelas Dinâmicas – Introdução151

ÍNDICE DO CURSO – EXCEL AVANÇADO Lição 02: Tabelas Dinâmicas – Conceito e Apresentação....................................153

Autor: Júlio Cesar Fabris Battisti
Site:w.juliobattisti.com.br
É proibido usar este material em treinamentos ou em sala de aulaPágina 7 de 417
Lição 03: Tabelas Dinâmicas – um exemplo para começar158
Lição 04: Tabelas Dinâmicas – Descobrindo o poder163
Lição 05: Tabelas Dinâmicas – Mais sobre filtros básicos165
Lição 06: Tabelas Dinâmicas – múltiplos campos de coluna168
Lição 08: Tabelas Dinâmicas – Dois Exercícios Propostos172
Lição 09: Tabelas Dinâmicas – Resolução do Exercício 01173
Lição 10: Tabelas Dinâmicas – Resolução do Exercício 02176
Lição 1: Tabelas Dinâmicas – Seleção Estruturada180
Lição 12: Tabelas Dinâmicas – O Recurso de AutoFormatação183
Lição 14: Tabelas Dinâmicas – Mais opções de configuração191
Lição 15: Gráficos Dinâmicos – Conceitos e um exemplo simples194
Lição 16: Gráficos Dinâmicos – Formatações e Alteração de Tipo197
Lição 17: Alterando os campos que compõem o gráfico201
Lição 18: Exercícios propostos – Tabelas Dinâmicas204
Lição 19: Mais exercícios com Tabelas Dinâmicas e Gráficos Dinâmicos206
Lição 20: Resumo do Módulo 3208
Módulo 4 – Análise, Cenários e Consolidação de Dados209
Lição 01: Introdução e conteúdo do Módulo 4209
Lição 02: Calculando SubTotais/Resumos212
Lição 03: Criando Subtotais – Mais um exemplo simples215
Lição 04: Configurações e alterações em dados consolidados220
Lição 05: Exercícios com totalizações223
Lição 06: Introdução à consolidação de dados224
Lição 07: Consolidação de dados: e um exemplo simples228
Lição 08: Consolidação por categoria – um exemplo prático231
Lição 09: Configurações relacionadas a consolidação de dados235
Lição 10: Consolidação com dados de múltiplos arquivos237
Lição 1: Análise de Dados com o Excel – Introdução e Conceitos242
Lição 12: Análise/Simulações baseadas em uma Tabela de Dados245
Lição 13: Análise/Simulações: Simulação com múltiplos valores250
Lição 14: Análise/Simulações: Simulação com duas variáveis253
Lição 15: Atingindo Objetivos – A Ferramenta Atingir Metas256
Lição 16: Gerenciando Cenários – Introdução e Exemplo259
Lição 17: Criando Cenários – Criação dos cenários261
Lição 18: Utilizando os Cenários Criados na Lição Anterior267
Lição 19: Gerando um Relatório Consolidado de Cenários269
Lição 20: Resumo do Módulo 4271
Módulo 5 – Introdução as Macros e a programação VBA no Excel272
Lição 01: Introdução e conteúdo do Módulo 5272
Lição 02: O que são Macros?275
Lição 03: Conhecendo do que é feita uma Macro278
Lição 04: Operações com Macros281
Lição 05: Associando botões com macros283
Lição 06: Introdução ao VBA287
Lição 07: O Ambiente de programação – o Editor VBA290
Lição 08: VBA – Declaração de Variáveis293

Curso de Excel Avançado em 120 Lições Lição 13: Tabelas Dinâmicas – Classificação e Formatação de Células Vazias..187 Lição 09: VBA - Cálculos, Operadores Aritméticos e Exemplos....................296

Autor: Júlio Cesar Fabris Battisti
Site:w.juliobattisti.com.br
É proibido usar este material em treinamentos ou em sala de aulaPágina 8 de 417
Lição 10: Estrutura If...Then e os Operadores de Comparação300
Lição 1: Escopo de Variáveis e Variáveis do tipo Array304
Lição 12: Estruturas If...Then...Else If e Select Case307
Lição 13: Estruturas For...Next, Do...While e Do...Until312
Lição 14: Funções do VBA – Funções de Tipo – Parte I319
Lição 15: Funções do VBA – Funções de Tipo – Parte I324
Lição 16: Funções do VBA – Funções para conversão de Tipos– Parte I328
Lição 17: Funções do VBA – Funções para conversão de Tipos–Parte I332
Lição 18: Funções do VBA – Funções para tratamento de Texto335
Lição 19: Funções do VBA – Funções de Data/Hora e Matemáticas339
Lição 20: Resumo do Módulo 5348
Módulo 6 – VBA – O Modelo de Objetos do Excel349
Lição 01: Introdução e conteúdo do Módulo 6349
Lição 02: Bibliotecas, Objetos, Propriedades, Métodos e Referências352
Lição 03: Criando suas próprias funções e procedimentos356
Lição 04: VBA - Trabalhando com o objeto Application363
Lição 05: Trabalhando com o Objetos Workbook366
Lição 06: Trabalhando com o Objeto Worksheet370
Lição 07: Trabalhando com o Objeto Range374
Lição 08: Um exemplo prático – calculando o DV do CPF - Algoritmo:378
Lição 09: Calculando o DV do CPF – Criando a Função:380
Lição 10: Calculando o DV do CPF – Usando a Função ValidaCPF:384
Lição 1: O Objeto Application – Propriedades e Métodos – Parte I385
Lição 12: O Objeto Application – Propriedades e Métodos – Parte I388
Lição 13: O Objeto Application – Propriedades e Métodos – Parte I392
Lição 14: O Objeto Workbook – – Propriedades e Métodos – Parte I396
Lição 15: O Objeto Workbook – – Propriedades e Métodos – Parte I400
Lição 16: O Objeto Workbook – – Propriedades e Métodos – Parte I402
Lição 17: Worksheet e Range – Propriedades e Métodos – Parte I406
Lição 18: Worksheet e Range – Propriedades e Métodos – Parte I409
Lição 19: Worksheet e Range – Propriedades e Métodos – Parte I411

Curso de Excel Avançado em 120 Lições Lição 20: Resumo do Módulo 6.......................................................................415

Autor: Júlio Cesar Fabris Battisti
Site:w.juliobattisti.com.br
É proibido usar este material em treinamentos ou em sala de aulaPágina 9 de 417

Curso de Excel Avançado em 120 Lições

Módulo 1 – Trabalhando com Listas de dados

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

Neste Módulo aprenderemos a trabalhar com uma lista de dados no Excel. Uma lista de dados pode ser uma planilha criada diretamente no Excel 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.

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.

Nota: Para detalhes sobre tabelas, campos e registros, consulte os endereços indicados na seção “pré-requisitos para o curso”.

Na Figura 1.1, obtida a partir da Ajuda do Excel, está indicada a estrutura de uma lista:

Figura 1.1 – Estrutura de uma lista no Excel. Considere os seguintes detalhes sobre listas:

• Cada coluna na lista representa um campo. • Os rótulos de cada coluna, representam os nomes dos campos.

• A lista não deve conter linhas ou colunas em branco.

• Use somente uma lista em cada planilha. O ideal é que não exista nenhuma outra informação na planilha, a não ser os dados da lista. Algumas funções de planilha, tais como a filtragem, podem não funcionar corretamente se houver outros dados além dos dados da própria lista.

• Cada coluna deve conter dados do mesmo tipo: texto, número, data, etc. Não “misture” dados de diferentes tipos, na mesma coluna.

• Caso você tenha outros dados, além da lista, mantenha a lista “separada” dos demais dados. Para isso mantenha, pelo menos, uma linha e uma coluna em branco, entre a lista e os demais dados.

Autor: Júlio Cesar Fabris Battisti
Site:w.juliobattisti.com.br
É proibido usar este material em treinamentos ou em sala de aulaPágina 10 de 417

Curso de Excel Avançado em 120 Lições

Nota: Os dados utilizados nos exemplos deste módulo, foram importados a partir da tabela Pedidos, do banco de dados northwind.mdb, o qual é instalado durante a instalação do Microsoft Office. Veremos mais detalhes sobre a importação de dados nas próximas lições deste módulo.

No exemplo da Figura 1.2 uma lista, na qual são listados os diversos pedidos para uma determinada empresa. Observe que a primeira linha contém os nomes dos campos. Cada coluna representa um campo. A primeira coluna é o Número do Pedido, a segundo o Código do Cliente, a terceira a data do Pedido e assim por diante. Cada linha representa um registro completo. Por exemplo, a segunda linha contém todas as informações (com exceção dos itens do pedido) sobre o cabeçalho do pedido 10248.

Figura 1.2 – Uma lista de Pedidos.

Autor: Júlio Cesar Fabris Battisti
Site:w.juliobattisti.com.br
É proibido usar este material em treinamentos ou em sala de aulaPágina 1 de 417

Curso de Excel Avançado em 120 Lições

Neste Módulo aprenderemos a fazer as seguintes operações com Listas de dados no Excel:

Importando de dados – Parte 1 Importando de dados – Parte 2 Importação de dados – Parte 3 Ordenando Listas – Parte 1 Ordenando Listas – Parte 2 Criando Listas de ordenação personalizadas Filtrando dados em uma Lista – Introdução e conceitos Filtrando dados – o comando AutoFiltro – Parte 1 Filtrando dados – o comando AutoFiltro – Parte 2 Filtrando dados – o comando AutoFiltro – Parte 3 Localizando registros individuais: Dados -> Formulário Operadores de filtragem: A diferença entre “E” e “OU” Definindo e usando faixas de critérios – Exemplo 1 Mais exemplos usando faixas de critérios. Exercícios com faixas de critérios. Usando Critérios de Texto. Usando Critérios Calculados. Usando Subtotais para análise de dados. Resumo do Módulo 1.

Estas operações são especialmente úteis quando queremos ter diferentes visões dos dados de uma lista. Por exemplo, você pode querer pesquisar apenas os pedidos para o Brasil, no ano de 1996; ou todos os pedidos para o Brasil e também para os EUA que ocorreram em um determinado período e assim por diante.

Também aprenderemos a utilizar, em detalhes, o comando AutoFiltro, o qual é muito útil quando queremos efetuar consultas rápidas nos dados, de tal forma que podemos alterar os critérios de pesquisa de uma maneira fácil e rápida. No Curso Básico de Excel, aprendemos as funções básicas do comando AutoFiltro, agora vamos detalhar um pouco mais este comando.

Na seqüência aprenderemos a usar os operadores “E” e “OU” para trabalhar com múltiplos critérios. Também veremos como usar faixas de células para tornar a definição de critérios mais dinâmica, o que facilita a alteração do valor de um ou mais critérios.

Para encerrar o módulo, trataremos sobre o uso de Critérios calculados e sobre a criação de Subtotais para a análise de dados. Veremos apenas uma introdução sobre o uso de Subtotais. Este assunto será detalhado no Módulo 4 – Análise, Cenários e Consolidação de Dados.

Autor: Júlio Cesar Fabris Battisti
Site:w.juliobattisti.com.br
É proibido usar este material em treinamentos ou em sala de aulaPágina 12 de 417

Curso de Excel Avançado em 120 Lições

Lição 02: Importando de dados – Parte 1

Existem situações onde os dados que você precisa analisar não estão no formato de uma planilha do Excel. 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 Microsoft Access. Nestas situações você deseja importar estes dados para dentro de uma planilha do Excel, 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.

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, vamos apresentar os dois formatos de texto mais comumente utilizados.

Arquivos de texto no formato tamanho fixo:

Na Figura 1.3 temos um exemplo de arquivo de texto, onde os dados estão no formato tamanho fixo. Este exemplo é baseado no arquivo PedidosTamanhoFixo.txt, o qual está disponível na pasta C:\ExcelAvançado. Para detalhes sobre como copiar os arquivos deste curso, consulte a página principal do curso na Internet, no seguinte endereco: w.juliobattisti.com.br/excel120av/excel120av.asp.

(Parte 1 de 5)

Comentários