Alex de Almeida Ramos
O mercado de trabalho em permanente mudança exige que o profissional se atualize continuamente ou, em muitos casos, busque qualificações. É para esse profissional, sintonizado com a evolução tecnológica e com as inovações nos processos produtivos, que o SENAI-SP oferece muitas opções em cursos, em diferentes níveis, nas diversas áreas tecnológicas.
Microsoft Office Excel 2013 — Avançado
Esta publicação integra uma série da SENAI-SP Editora especialmente criada para apoiar os cursos do SENAI-SP.
TECNOLOGIA DA INFORMAÇÃO
Microsoft Office Excel 2013 Avançado
ISBN 978-85-8393-450-9
9
788583
934509
Excel avançado 2013_capa ok.indd Todas as páginas
29/05/18 11:47
Microsoft Office Excel 2013 Avanรงado
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 1
26/11/15 16:17
Dados Internacionais de Catalogação na Publicação (CIP) Ramos, Alex de Almeida. Microsoft Office Excel 2013: avançado / Alex de Almeida Ramos. – São Paulo : SENAI-SP Editora, 2016 164 p. : il
Inclui referências ISBN 978-85-8393-450-9
1. Excel (Programa de computador) 2. Planilhas eletrônicas I. Serviço Nacional de Aprendizagem Industrial II. Título.
CDD 005.3
Índice para o catálogo sistemático: 1. Excel (Programa de computador) 005.3
SENAI-SP Editora Avenida Paulista, 1313, 4o andar, 01311 923, São Paulo – SP F. 11 3146.7308 | editora@sesisenaisp.org.br | www.senaispeditora.com.br
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 2
26/11/15 16:17
TECNOLOGIA DA INFORMAÇÃO
Microsoft Office Excel 2013 Avançado Alex de Almeida Ramos
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 3
26/11/15 16:17
Departamento Regional de São Paulo Presidente Paulo Skaf Diretor Regional Walter Vicioni Gonçalves Diretor Técnico Ricardo Figueiredo Terra Gerente de Educação João Ricardo Santa Rosa
Material didático utilizado nos cursos do SENAI-SP
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 4
26/11/15 16:17
Apresentação
Com a permanente transformação dos processos produtivos e das formas de organização do trabalho, as demandas por educação profissional se multiplicam e, sobretudo, se diversificam. Em sintonia com essa realidade, o SENAI-SP valoriza a educação profissional para o primeiro emprego dirigida a jovens. Privilegia também a qualificação de adultos que buscam um diferencial de qualidade para progredir no mercado de trabalho. E incorpora firmemente o conceito de “educação ao longo de toda a vida”, oferecendo modalidades de formação continuada para profissionais já atuantes. Dessa forma, atende às prioridades estratégicas da Indústria e às prioridades sociais do mercado de trabalho. A instituição trabalha com cursos de longa duração, como os cursos de Aprendizagem Industrial, os cursos Técnicos e os cursos Superiores de Tecnologia. Oferece também cursos de Formação Inicial e Continuada, com duração variada nas modalidades de Iniciação Profissional, Qualificação Profissional, Especialização Profissional, Aperfeiçoamento Profissional e Pós-Graduação. Com satisfação, apresentamos ao leitor esta publicação, que integra uma série da SENAI-SP Editora especialmente criada para apoiar os alunos das diversas modalidades. Walter Vicioni Gonçalves Diretor Regional do SENAI-SP
Excel avançado 2013_miolo.indd 5
14/12/15 14:44
REQUISITOS MÍNIMOS DE HARDWARE Processador de 1 GHz ou superior 1 GB de memória RAM (32 bits) ou 2 GB de RAM (64 bits) 16 GB de espaço disponível em HD (32 bits) ou 20 GB (64 bits) Dispositivo gráfico DirectX 9 com driver WDDM 1.0 ou superior REQUISITOS DE SOFTWARE Microsoft Windows 7 ou superior e Microsoft Office Excel 2013.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 6
26/11/15 16:17
Sumário 1. Introdução ao Excel Avançado 2013
9
Argumentos nas funções do Excel
10
Funções da categoria Lógica
10
Funções da categoria Estatística
14
Funções da categoria Pesquisa e Referência
16
Funções da categoria Data e Hora
23
Funções da categoria Financeira
24
Funções aninhadas
28
2. Banco de dados
32
Características de um banco de dados
32
Classificação de registros
33
Relatórios de um banco de dados
34
Extratos de um banco de dados
40
Funções de banco de dados
51
3. Tabelas e gráficos dinâmicos
61
Tabela Dinâmica
61
Gráfico Dinâmico
73
4. Análise de dados
80
Cenários 80 Auditoria de células Importação de dados
86 93
Suplemento Solver
105
5. Introdução a macros
115
O que são macros?
115
Tipos de macros
116
Criação de macros no Excel
116
O ambiente VBA
124
Criação de uma agenda telefônica
139
Técnicas básicas de programação
150
6. Funções definidas pelo usuário
157
Característica funcional
157
Funções diretas no VBA
158
Referências 162
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 7
26/11/15 16:17
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 8
26/11/15 16:17
1. Introdução ao Excel Avançado 2013 Argumentos nas funções do Excel Funções da categoria Lógica Funções da categoria Estatística Funções da categoria Pesquisa e Referência Funções da categoria Data e Hora Funções da categoria Financeira Funções aninhadas
O Microsoft Office Excel 2013 é um programa que permite executar diversas tarefas: controlar estoque, controlar vendas, fazer planejamento financeiro, controlar eventos, fazer tabelas de campeonatos, elaborar gráficos e até criar planilhas dinâmicas e interativas com funções programadas. Ele possui uma enorme quantidade de recursos que podem ser vistos em diversos cursos e em todas as áreas de atuação. A capacidade do programa é tão ampla que é difícil imaginar alguma atividade que não possa ser realizada nele. Um dos recursos mais usados do Excel são as funções. Elas permitem a manipulação de dados de todos os tipos e valores, atendendo assim a qualquer necessidade. Função é um conjunto de fórmulas capazes de desenvolver tarefas complexas de forma simples e rápida. O Excel apresenta dez categorias de funções: Financeira, Data e Hora, Matemática e Trigonométrica, Estatística, Pesquisa e Referência, Banco de Dados, Texto, Lógica, Informações, Engenharia.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 9
26/11/15 16:17
10
INTRODUÇÃO AO EXCEL AVANÇADO 2013
Toda função do Excel tem um nome que a identifica, por exemplo: SOMA, MÉDIA, SE, HOJE etc. Para um melhor aproveitamento do conteúdo deste livro, é aconselhável copiar os exemplos em uma planilha Excel e executar as funções para acompanhar o funcionamento delas.
Argumentos nas funções do Excel As funções podem ter ou não argumentos. • Funções com argumentos – ocorrem quando há parâmetros ou variáveis definidos dentro dos parênteses. Apresentam a seguinte sintaxe: =NOME_ FUNÇÃO(argumento1;argumento2;argumento3;...). E cada argumento é separado por ponto e vírgula (;). As funções SOMA, MÉDIA, SE etc. necessitam do argumento, caso contrário não terão o que calcular ou avaliar. • Funções sem argumentos – ocorrem quando não há parâmetros definidos entre os parênteses. Apresentam a seguinte sintaxe: =NOME_FUNÇÃO( ). A função HOJE, que apenas retorna a data do sistema, dispensa o uso de argumentos porque não é uma função de análise ou cálculo.
Funções da categoria Lógica
Função E A função lógica E necessita de argumentos. Ela avalia cada argumento individualmente, atribuindo a cada um a qualidade de VERDADEIRO ou FALSO. No fim da avaliação de todos os argumentos, a função E retorna VERDADEIRO se todos forem avaliados como VERDADEIROS. Se somente um argumento for considerado FALSO em sua análise individual, a função retornará o resultado FALSO. A sintaxe dessa função é: =E(lógico1;lógico2;...). O argumento lógico1 é obrigatório e refere-se ao primeiro argumento que a função vai avaliar. É opcional a
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 10
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
11
inserção de argumentos a partir do argumento lógico2, que se refere ao próximo argumento que será avaliado na função. Exemplo
Figura 1 – Função E.
Nela, cada argumento contém uma expressão de comparação que verifica se o valor da variável é maior do que o valor fixo. Para que isso aconteça, a sintaxe da função E deve ficar assim: =E(B11>B13;C11>B13;D11>B13;E11>B13;F11>B13). Dessa forma, a função E realiza as avaliações conforme o Quadro 1. Quadro 1 – Avaliações detalhadas da função E
B11>B13
C11>B13
D11>B13
E11>B13
F11>B13
32>25
25>25
36>25
48>25
52>25
VERDADEIRO
FALSO
VERDADEIRO
VERDADEIRO
VERDADEIRO
Ao analisar os argumentos, a função E retorna o resultado FALSO porque o segundo argumento é considerado FALSO: afinal, 25 não é maior do que 25. Porém, se o valor do segundo argumento for alterado para 28, mantendo-se o sistema de comparação, a função terá um resultado diferente.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 11
26/11/15 16:17
12
INTRODUÇÃO AO EXCEL AVANÇADO 2013
Figura 2 – Alteração do valor de um dos argumentos na função E.
Com a alteração, a função E agora realiza as avaliações conforme o Quadro 2. Quadro 2 – Avaliações detalhadas da função E com novo valor de argumento
B11>B13
C11>B13
D11>B13
E11>B13
F11>B13
32>25
28>25
36>25
48>25
52>25
VERDADEIRO
VERDADEIRO
VERDADEIRO
VERDADEIRO
VERDADEIRO
Agora a função E retorna o resultado VERDADEIRO porque todos os argumentos foram considerados VERDADEIROS.
Função OU A função lógica OU também precisa de argumentos. Ela avalia cada argumento individualmente, atribuindo a cada um a qualidade de VERDADEIRO ou FALSO. No fim da avaliação de todos os argumentos, a função OU retorna VERDADEIRO se apenas um dos argumentos for avaliado como VERDADEIRO. Se todos forem considerados FALSOS, a função retornará o resultado FALSO. A sintaxe dessa função é: =OU(lógico1;lógico2;...). O argumento lógico1 é obrigatório e refere-se ao primeiro argumento que a função vai avaliar. É opcional a inserção de argumentos a partir do argumento lógico2, que se refere ao próximo argumento que será avaliado na função.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 12
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
13
Exemplo
Figura 3 – Função OU.
Nela, cada argumento contém uma expressão de comparação que verifica se o valor da variável é maior do que o valor fixo. Para que isso aconteça, a sintaxe da função OU deve ficar assim: =OU(B11>B13;C11>B13;D11>B13;E11>B13;F11>B13). Dessa forma, a função OU realiza as avaliações conforme o Quadro 3. Quadro 3 – Avaliações detalhadas da função OU
B11>B13
C11>B13
D11>B13
E11>B13
F11>B13
16>25
25>25
20>25
52>25
8>25
FALSO
FALSO
FALSO
VERDADEIRO
FALSO
Ao analisar os argumentos, a função OU retorna o resultado VERDADEIRO porque o quarto argumento é considerado VERDADEIRO.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 13
26/11/15 16:17
14
INTRODUÇÃO AO EXCEL AVANÇADO 2013
Funções da categoria Estatística
Função MAIOR A função estatística MAIOR retorna o maior valor em uma lista de valores conforme o critério definido. São obrigatórios dois argumentos em sua sintaxe: =MAIOR(matriz;k). A matriz indica o intervalo de células em que serão verificados os dados para encontrar o maior valor conforme a determinação. O k refere-se à posição do maior valor a ser retornado, considerando-se a ordem decrescente, isto é, do maior para o menor. Exemplo
Figura 4 – Função MAIOR.
Na Figura 4, a sintaxe da função MAIOR fica: =MAIOR(A3:E12;8). Nela, identificam-se:
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 14
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
15
• MAIOR – nome da função • A3:E12 – matriz, intervalo de células que contém os dados a serem verificados pela função • 8 – ordem do valor que deverá ser retornado, considerando os valores do maior para o menor (neste caso, refere-se ao oitavo maior valor, 41) Portanto, no intervalo que se estende da célula A3 até a célula E12, o oitavo maior valor é 41, a idade de Beatriz.
Função MENOR A função estatística MENOR retorna o menor valor em uma lista de valores conforme o critério definido. São obrigatórios dois argumentos em sua sintaxe: =MENOR(matriz;k). A matriz indica o intervalo de células em que serão verificados os dados para encontrar o menor valor conforme a determinação. O k refere-se à posição do menor valor a ser retornado, considerando-se a ordem crescente, isto é, do menor para o maior. Exemplo
Figura 5 – Função MENOR.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 15
26/11/15 16:17
16
INTRODUÇÃO AO EXCEL AVANÇADO 2013
Na Figura 5, a sintaxe da função MENOR fica: =MENOR(A3:E12;12). Nela, identificam-se: • MENOR – nome da função. • A3:E12 – matriz, intervalo de células que contém os dados a serem verificados pela função. • 12 – ordem do valor que deverá ser retornado, considerando os valores do menor para o maior; nesse caso, refere-se ao 12o maior valor, 38. Portanto, no intervalo que se estende da célula A3 até a célula E12, o 12o maior valor é 38, a idade de Tatiane.
Funções da categoria Pesquisa e Referência
Função CORRESP A função CORRESP procura um valor especificado e retorna sua posição relativa ao intervalo de células procurado. Sua sintaxe é: =CORRESP(valor_ procurado;matriz_procurada;tipo_correspondência). O valor_procurado é o valor que se deseja encontrar. A matriz_procurada refere-se ao intervalo de células em que a função tentará localizar o valor_procurado. O tipo_correspondência especifica como a função deverá proceder na procura do valor_procurado, podendo ser −1, 0 ou 1. • −1 – localiza o maior valor que é menor ou igual ao valor_procurado. A matriz_procurada deve estar classificada em ordem decrescente. • 0 – localiza o primeiro valor que é exatamente igual ao valor_procurado. A matriz_procurada não precisa estar classificada. • 1 – localiza o menor valor que é maior ou igual ao valor_procurado. A matriz_procurada deve estar classificada em ordem crescente. Esse é o valor-padrão da função, ou seja, quando não for especificado o tipo_correspondência, a função vai considerá-lo automaticamente como 1.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 16
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
17
Exemplo
Figura 6 – Função CORRESP.
Na primeira função da Figura 6, o nome “Mauro” é procurado dentro da matriz indicada (B2:B21). Como a função não encontra o nome “Mauro”, ela retorna a posição do menor valor maior do que o valor_ procurado, que é o nome “Matheus”, que está na posição 12. Na segunda função da Figura 6, o nome “Karen” é procurado dentro da matriz B2:B21. O retorno é a posição do nome “Karen”, que se encontra na posição 7. Caso o nome não fosse encontrado na matriz, o retorno seria #N/D, pois o tipo_correspondência usado foi 0, que trata a procura como o valor exatamente igual ao valor_procurado.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 17
26/11/15 16:17
18
INTRODUÇÃO AO EXCEL AVANÇADO 2013
Na terceira função da Figura 6, o nome “Luciana” é procurado na matriz B3:B22. O resultado foi um erro #N/D, pois o tipo_correspondência utilizado foi −1. Entretanto, para que ela funcione, a matriz precisa estar classificada em ordem decrescente.
Função DESLOC A função DESLOC retorna uma referência de um intervalo de células especificado por meio de linhas e colunas na função. Sua sintaxe é: =DESLOC(ref;lins; cols;altura;largura). A seguir, são explicados os argumentos dessa sintaxe. • ref – é um argumento obrigatório que indica a posição em que a função vai iniciar o deslocamento. • lins – também obrigatório, refere-se ao número de linhas acima ou abaixo da posição inicial definida no argumento ref. Assim, os números podem ser negativos (quando estão em linhas acima da posição inicial) ou positivos (quando estão em linhas abaixo da posição inicial). • cols – refere-se ao número de colunas à esquerda ou à direita da posição inicial definida no argumento ref. E os números também podem ser negativos (quando estão em colunas à esquerda da posição inicial) ou positivos (quando estão em colunas à direita da posição inicial). • altura – argumento opcional, é definida em número de linhas e deve sempre ser um número positivo. Refere-se ao número de linhas que será considerado como referência para o novo intervalo de células. • largura – também opcional, é definida em número de colunas e deve sempre ser um número positivo. Refere-se ao número de colunas que será considerado como referência para o novo intervalo de células.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 18
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
19
Exemplo
Figura 7 – Função DESLOC.
Na Figura 7, a sintaxe é: =DESLOC(C3;4;3). Nela, identificam-se: • DESLOC – nome da função. • C3 – posição em que a função vai iniciar o deslocamento (ref). • 4 – número de linhas que a função vai percorrer a partir da posição inicial (lins). Como o número informado é positivo, a função vai se deslocar para as linhas abaixo da posição inicial. • 3 – refere-se ao número de colunas que a função vai percorrer a partir da posição inicial (cols). Como o número informado é positivo, a função vai se deslocar para as colunas à direita.
Função ÍNDICE A função ÍNDICE retorna um valor ou a referência a um valor de um intervalo de células definido na função. São três os argumentos obrigatórios que fazem parte de sua sintaxe: =ÍNDICE(matriz;núm_linha;núm_coluna). A seguir, são explicados cada um dos argumentos. • matriz – intervalo de células que será considerado. • núm_linha – linha da matriz para a qual o valor deverá retornar.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 19
26/11/15 16:17
20
INTRODUÇÃO AO EXCEL AVANÇADO 2013
• núm_coluna – coluna da matriz para a qual o valor deverá retornar. Exemplo
Figura 8 – Função ÍNDICE.
Na primeira função da Figura 8, =ÍNDICE(A3:D12;4;3), o resultado é o valor da quarta linha da terceira coluna do intervalo de células de A3 até D12, ou seja, “Renata”. Já na segunda função da Figura 8, =ÍNDICE(A3:D12;10;1), o resultado é o valor da décima linha da primeira coluna do intervalo de células de A3 até D12, ou seja, “Matheus”. Ao indicar o intervalo de células na função ÍNDICE, o Excel divide esse intervalo em colunas e linhas, identificando-os por meio de números que sempre começam pelo número 1. A Figura 9 mostra como isso acontece.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 20
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
21
Figura 9 – Divisão de intervalo no Excel entre colunas e linhas.
Função ÉERROS É uma função do tipo booliana, ou seja, retorna um valor VERDADEIRO se ao menos um valor de erro for encontrado na função, caso contrário retornará o valor FALSO. Sua sintaxe é: =ÉERROS(valor). E o valor indicado na sintaxe pode ser uma fórmula, uma função ou uma referência a uma célula. Exemplo A fórmula =10/0 tem como resultado o erro #DIV/0!. Isso ocorre porque nenhum número pode ser dividido por zero. Entretanto, se colocada dentro da função ÉERROS, ou seja, =ÉERROS(10/0), o resultado seria VERDADEIRO, pois a própria função contém um erro. Mas em que situação essa função deve de fato ser aplicada? A função ÉERROS pode ser aplicada em diversos casos, mas o mais comum é que seja utilizada em conjunto com a função SE.
Excel avançado 2013_miolo.indd 21
14/12/15 14:44
22
INTRODUÇÃO AO EXCEL AVANÇADO 2013
A
B
1
10
0
2
10
2
=SE(ÉERROS(A1/B1);“Impossível realizar essa operação”;A1/B1) Resultado: Impossível realizar essa operação. Na figura anterior, a função ÉERROS testa a fórmula A1/B1, considerando que, na tabela, os valores de A1 e B1 são, respectivamente, 10 e 0. Portanto, a função ÉERROS testa a fórmula =10/0. Como já visto anteriormente, nenhum número é divisível por zero; dessa forma, o resultado da função ÉERROS será VERDADEIRO. Esse resultado será avaliado agora pela função SE, que retornará a resposta “Impossível realizar essa operação” se o resultado de ÉERROS for VERDADEIRO; caso o resultado seja FALSO, a fórmula A1/B1 será executada, como pode ser observado a seguir.
A
B
1
10
0
2
10
2
=SE(ÉERROS(A2/B2);”Impossível realizar essa operação”;A2/B2) Resultado: 5 Na figura anterior, a função ÉERROS retorna FALSO, pois nenhum erro foi encontrado na fórmula =A2/B2. Portanto, a função SE pode executar a fórmula. Essa junção de funções é chamada de aninhamento de funções, conceito que será estudado ainda neste capítulo.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 22
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
23
Funções da categoria Data e Hora
Função DIATRABALHOTOTAL A função DIATRABALHOTOTAL retorna o número de dias úteis entre uma data de início e outra de fim informadas na função. Ela também pode excluir os feriados se eles forem informados. A sintaxe da função DIATRABALHOTOTAL é: =DIATRABALHOTOTAL(data_inicial;data_final;feriados). A seguir, são explicados os argumentos que fazem parte dessa sintaxe. • data_inicial – argumento obrigatório, indica a data de início do período. • data_final – também obrigatório, refere-se à data de término do período. • feriados – argumento opcional, é o intervalo de datas a serem excluídas do período informado. Exemplo
Figura 10 – Função DIATRABALHOTOTAL.
A Figura 10 apresenta duas tabelas: uma em que constam os feriados dos meses de janeiro e fevereiro do ano de 2015; outra em que aparece o período trabalhado por dois funcionários. Em “Dias trabalhados”, foi aplicada a função DIATRABALHOTOTAL para calcular o número
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 23
26/11/15 16:17
24
INTRODUÇÃO AO EXCEL AVANÇADO 2013
de dias úteis trabalhados por cada funcionário. Na coluna “Função”, mostra-se a função usada na coluna “Dias trabalhados”. A funcionária Joana trabalhou no período de 01/01/2015 até 28/02/2015, porém nesse período ocorreram cinco feriados. A função então retorna o número de dias úteis trabalhados, excluindo os feriados informados. A sintaxe da função fica assim: =DIATRABALHOTOTAL(B12;C12;D2:D6). Nela, identificam-se: • • • •
DIATRABALHOTOTAL – nome da função. B12 – célula que contém a data de início do período. C12 – célula que contém a data de fim do período. D2:D6 – intervalo de células que contém as datas de feriados a serem excluídas da contagem. Portanto, a função DIATRABALHOTOTAL retorna o número de dias úteis trabalhados por Joana, ou seja, 37 dias.
Funções da categoria Financeira
Função NPER A função NPER retorna como resultado o tempo de investimento baseado em pagamentos periódicos e taxas constantes de juros. A função NPER pode ser usada quando são conhecidos os valores de taxas e pagamentos mensais que serão realizados e se deseja saber quanto tempo levará para que o objetivo determinado seja atingido. A sintaxe da função NPER é: =NPER(taxa;pgto;vp;vf;tipo). A seguir, são explicados os argumentos que fazem parte dessa sintaxe.
• taxa – a taxa de juros constante.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 24
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
25
• pgto – pagamento constante feito em cada período. O valor deve sempre ser informado em número negativo. • vp – valor atual, que deve sempre ser informado em número negativo. • vf – saldo futuro que se deseja atingir, que, se for omitido, será considerado 0. • tipo – número definido como 0 ou 1 que indica as datas de vencimento; o número 0 (ou quando for omitido) indica vencimento no fim do período, enquanto o número 1 indica vencimento no início do período. Exemplo
Figura 11 – Função NPER.
O resultado da função NPER apresentado na Figura 11 foi 14,33. Observa-se na tabela que a taxa de juros e o pagamento são mensais; portanto, o resultado refere-se ao número de meses que serão necessários para que o valor desejado seja alcançado. Para atingir o valor de R$ 5.000,00, o tempo será de aproximadamente 14 meses, ou seja, 1 ano e 2 meses.
Função PGTO A função PGTO retorna como resultado o valor do pagamento periódico de acordo com as informações recebidas. Ela pode ser usada quando são conhecidos os valores de taxas e o número de prestações, e se deseja saber o valor do
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 25
26/11/15 16:17
26
INTRODUÇÃO AO EXCEL AVANÇADO 2013
pagamento periódico. A sintaxe da função PGTO é: =PGTO(taxa;nper;vp;vf;tipo). A seguir, são explicados os argumentos que fazem parte dessa sintaxe. • • • • •
taxa: taxa de juros constante. nper: número de período (número de parcelas, prestações etc.). vp: valor atual, que deve sempre ser informado em número negativo. vf: saldo futuro que se deseja atingir, que, se for omitido, será considerado 0. tipo: número definido como 0 ou 1 que indica as datas de vencimentos; o número 0 (ou quando for omitido) indica vencimento no fim do período, enquanto o número 1 indica vencimento no início do período. Exemplos A Figura 12 mostra como saber o valor de uma das 24 parcelas de um empréstimo realizado no valor de R$ 5.000,00, com juros de 2,99% ao mês e com pagamento no início do período. 1 2 3 4 5 6
A
B
C
Descrição Taxa de juros mensal Nº Período Empréstimo Valor desejado Vencimento
Valor 2,99% 24 5000 0 1
Argumento da Função taxa nper vp vf tipo
-286,36
Função =PGTO(B2;B3;B4;B5;B6)
7 8 9
Qtde Meses:
Figura 12 – Função PGTO aplicada a parcelamento de empréstimo.
A Figura 13 mostra como saber o valor de depósitos mensais para que seja alcançado o valor de R$ 12.000,00, considerando uma taxa de rendimento de 1,5% ao mês e com pagamento no fim do período.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 26
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
1 2 3 4 5 6
A
B
C
Descrição Taxa de juros mensal Nº Período Empréstimo Valor desejado Vencimento
Valor 1,50% 30 0 12000 0
Argumento da Função taxa nper vp vf tipo
-319,67
Função =PGTO(B2;B3;B4;B5;B6)
27
7 8 9
Qtde Meses:
Figura 13 – Função PGTO aplicada a rendimento mensal.
Função TAXA A função TAXA retorna a taxa de juros anual cobrada com base nas informações fornecidas. Sua sintaxe é: =TAXA(nper;pgto;vp;vf;tipo;estimativa). A seguir, são explicados os argumentos que fazem parte dessa sintaxe. • nper – número de período (número de parcelas, prestações etc.). • pgto – pagamento constante feito em cada período, cujo valor deve sempre ser informado em número negativo. • vp – valor atual. • vf – saldo futuro que se deseja atingir, o qual, se omitido, será considerado 0. • tipo – número definido como 0 ou 1 que indica as datas de vencimento; o número 0 (ou quando for omitido) indica vencimento no fim do período, enquanto o número 1 indica vencimento no início do período. • estimativa – é a estimativa para a taxa; argumento opcional, se for omitido, será considerada a estimativa de 10%. Exemplo A Figura 14 mostra como saber os juros pagos anualmente aplicados a um empréstimo no valor de R$ 120.000,00 pelo período de 30 anos.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 27
26/11/15 16:17
28
INTRODUÇÃO AO EXCEL AVANÇADO 2013
Figura 14 – Função TAXA.
No fim da função, é inserida uma multiplicação por 12. Isso ocorre porque as parcelas foram informadas em meses; assim, o valor obtido refere-se à taxa de juros mensal. Para saber o valor percentual anual desejado, é necessário multiplicar esse resultado por 12, que equivale ao número de meses que completam o período de um ano. A sintaxe da função aplicada ao exemplo fica assim: =TAXA(B2;B3;B4;B5;B6)*12 =TAXA(360;−487,86;120000;0;0)*12 =TAXA(0,23%)*12 =2,72% Portanto, a função TAXA retorna a taxa de juros anual de 2,72%.
Funções aninhadas Às vezes é necessário usar mais de uma função para obter o resultado esperado. Dessa forma, são utilizadas funções como argumentos de uma função. Elas são denominadas funções aninhadas.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 28
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
29
Exemplos
Figura 15 – Função SE aninhada em outra função SE principal.
Na Figura 15, são consideradas as seguintes possibilidades: • Para nota igual ou maior do que 7,0, a situação do aluno é Aprovado. • Para nota maior do que 5,0 e menor do que 7,0, o aluno ficará de Recuperação. • Para nota menor do que 5,0, a situação do aluno é Reprovado. A função SE possui apenas duas condições lógicas, portanto será necessário o uso de função aninhada para atender às três condições lógicas. =SE(B2>=7;“Aprovado”;SE(B2<5;“Reprovado”;“Recuperação”))
Figura 16 – Função E aninhada em uma função SE principal.
Na Figura 16, é exibida uma porcentagem de acertos em uma prova dividida entre Conhecimentos Gerais e Conhecimentos Específicos. O candidato precisa acertar 50% ou mais das questões nas duas partes da prova para ser aprovado; caso contrário, será reprovado. =SE(E(G2>=50%;H2>=50%);“Aprovado”;“Reprovado”)
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 29
26/11/15 16:17
30
INTRODUÇÃO AO EXCEL AVANÇADO 2013
Exercícios 1. (Fundação Carlos Chagas, 2013). O Microsoft Excel 2013 (em portu-
guês) possui diversas funções que permitem executar cálculos financeiros. A maioria dessas funções aceita argumentos similares como prazo de investimento, pagamentos periódicos, período, taxa de juros, valor presente etc. Uma dessas funções é a NPER, que calcula o número de períodos de um investimento de acordo com pagamentos constantes e periódicos e uma taxa de juros constante. Baseando-se nas informações acima, considere um financiamento no valor de R$ 3.377,00, a uma taxa de 12% ao ano. Considere a disponibilidade de apenas R$ 300,00 por mês para pagar este financiamento. A
B
1
Dados
Descrição
2
R$ 3.377,00
Valor do financiando (Valor Presente)
3
R$ 300,00
Pagamento efetuado a cada mês (período)
4
12%
Taxa de juros anual
5
12,00
Número de meses (períodos) necessários para pagar o financiamento
O resultado presente na célula A5 (configurado para ser mostrado com duas casas decimais) é o número de meses necessários para pagar o financiamento, resultante da aplicação da função NPER. Baseando-se nos dados apresentados, a função utilizada na célula A5 é: • =NPER(A4/12;A2;-A3) • =NPER(A4/12;A2;A3) • =NPER(A4/12;-A3;A2) • =NPER(A4/12;A3;A2) • =NPER(A4;-A3;A2) 2. Qual é a diferença entre função com argumento e função sem argu-
mento? Cite dois exemplos de cada uma delas. 3. O que são funções aninhadas?
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 30
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
31
4. Substitua os números pela função correspondente.
• A função (1) retorna um resultado VERDADEIRO se apenas um de seus argumentos for avaliado como VERDADEIRO; se todos os argumentos forem considerados FALSOS, o retorno será FALSO. • A função (2) retorna a referência de um intervalo de células especificado por meio de linhas e colunas na função. • A função (3) retorna o número de dias úteis entre uma data de início e outra data de fim informadas na função. Ela também pode excluir os feriados se eles forem informados. • A função (4) retorna o maior valor em uma lista de valores conforme o critério definido. • A função (5) retorna um valor ou a referência a um valor de um intervalo de células definido na função.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 31
26/11/15 16:17
2. Banco de dados Características de um banco de dados Classificação de registros Relatórios de um banco de dados Extratos de um banco de dados Funções de banco de dados
Características de um banco de dados De acordo com Jorge Menezes (2010, p. 91), um banco de dados “pode ser considerado como um arquivo organizado logicamente e usado para cadastrar grande quantidade de informações”. • No Excel, um banco de dados é formado por campos (colunas) e registros (linhas). O encontro de um campo com um registro é chamado de atributo (célula).
Figura 1 – Banco de dados em planilha do Excel.
Cada atributo contém um dado (valor). Assim, um banco de dados, como o próprio nome já diz, é formado por um conjunto de dados que juntos resultam em uma ou mais informações.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 32
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
33
Por padrão, ao montar um banco de dados utilizando uma planilha Excel, é importante adotar a inserção de um apóstrofo (᾿) antes do nome do campo. Com isso, mesmo que os campos contenham números, estes serão convertidos para texto automaticamente. Isso deve ser realizado porque o nome de um campo deve sempre ser um texto, e nunca um número. Os nomes de cada campo devem ser únicos, ou seja, não podem existir outros campos com o mesmo nome. Caso seja necessário incluir dois ou mais campos com a mesma finalidade, eles devem ser diferenciados, por exemplo: ENDEREÇO1, ENDEREÇO2 ou ENDEREÇO_RESIDENCIAL, ENDEREÇO_EMPRESA etc.
Classificação de registros Muitas vezes é necessário classificar os registros de um banco de dados, tanto por questões estéticas como por questões de funcionalidade. Para classificar os registros de um banco de dados criado no Excel, ao clicar em qualquer célula do banco de dados, devem ser realizados os seguintes procedimentos: 1. Na guia Dados, no grupo Classificar e Filtrar, clique na ferramenta Classificar. 2. A caixa de seleção Meus dados contêm cabeçalhos deve estar selecionada. 3. Em Classificar por, escolha o campo que será usado como base para a classificação dos registros. 4. Em Ordem, defina a ordem da classificação. 5. Clique no botão OK para concluir a classificação.
Figura 2 – Opções de classificação em Classificar por na caixa de diálogo da ferramenta Classificar.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 33
26/11/15 16:17
34
BANCO DE DADOS
É possível ainda adicionar vários níveis de classificação. Por exemplo, pode ser desejado classificar o banco de dados por ordem de Categoria e depois por ordem de Produto. Dessa forma, os dados serão classificados primeiramente por Categoria e posteriormente por Produto. Para isso, na caixa de diálogo Classificar, após definir o primeiro critério de classificação, deve-se clicar no botão Adicionar Nível, e uma nova linha será adicionada logo abaixo do primeiro critério definido. Depois de selecionar o segundo critério, pode-se querer definir um terceiro, bastando repetir os mesmos procedimentos de inclusão de nível, ou concluir a configuração de classificação clicando no botão OK.
Figura 3 – Botão Adicionar Nível na caixa de diálogo da ferramenta Classificar.
Relatórios de um banco de dados Segundo o verbete “Relatório” da Wikipédia: “Um relatório é um conjunto de informações utilizado para reportar resultados parciais ou totais de uma determinada atividade, experimento, projeto, ação, pesquisa, ou outro evento que esteja finalizado ou em andamento”. Por meio de um banco de dados, é possível emitir relatórios com informações diversificadas e com critérios diferentes. Para isso, utilizam-se os filtros de pesquisa do Excel.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 34
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
35
Aplicação de AutoFiltro Para a aplicação de AutoFiltro devem ser seguidos os seguintes procedimentos: 1. Clique em qualquer célula dentro da faixa de intervalo que constitui o banco de dados. 2. Na guia Página Inicial, no grupo Edição, clique na ferramenta Classificar e Filtrar. 3. Clique na opção Filtro.
Figura 4 – Opção Filtro da ferramenta Classificar e Filtrar.
Ao lado direito de cada campo (cabeçalhos de colunas), surgirá um botão drop-down. É por meio desse botão que se torna possível definir os critérios de filtros do banco de dados.
Figura 5 – Botões drop-down ao lado direito de cada um dos campos do cabeçalho.
Excel avançado 2013_miolo.indd 35
14/12/15 14:44
36
BANCO DE DADOS
Por exemplo, para exibir um relatório apenas dos vendedores que recebem salários fixos no valor de R$ 800,00, devem ser realizados os seguintes procedimentos: 1. Clique no botão drop-down do campo “Salário Fixo”, pois o filtro será realizado pelo salário fixo. 2. Será exibida uma lista em ordem crescente de todos os valores existentes no campo (coluna) “Salário Fixo”. 3. Deixe marcada apenas a caixa de seleção relacionada ao valor de R$ 800,00. 4. Clicar no botão OK. 5. Serão exibidos apenas os vendedores que recebem salário fixo de R$ 800,00, os demais serão ocultados.
Figura 6 – Filtro do campo “Salário Fixo”.
Figura 7 – Relatório filtrado por salário fixo de R$ 800,00.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 36
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
37
É possível filtrar vários campos ao mesmo tempo, atentando-se à ordem em que são inseridos: o campo que receber o primeiro filtro será o primeiro a ser filtrado; o segundo campo filtrará os dados que resultarem do primeiro filtro, e assim por diante. Ao aplicar o filtro a um campo, o botão drop-down adotará uma nova simbologia, como ocorre no campo “Salário Fixo” na tabela da Figura 7. Essa nova simbologia indica que o banco de dados está com aplicação de filtro definida pelo campo indicado. Na Figura 8, o banco de dados está sendo filtrado por dois campos: “Total Vendas” e “Salário Fixo”. Dessa forma, ao serem observados filtros nos campos, entende-se que pode haver mais informações ocultas no banco de dados.
Figura 8 – Aplicação simultânea de dois campos: “Total Vendas” e “Salário Fixo”.
Para reexibir todos os dados do campo, clique no botão drop-down que contém a aplicação do filtro e marque na caixa de seleção a opção (Selecionar Tudo). Se houver mais filtros aplicados, repita a operação em todos os campos que apresentarem o filtro ativo. O banco de dados voltará a exibir todas as informações cadastradas. Da mesma forma que é possível filtrar quantos campos forem desejados, também é possível filtrar quantos critérios forem desejados em um único campo. Por exemplo, para exibir um relatório com as informações somente dos vendedores Luiz e Sabrina, devem ser realizados os seguintes procedimentos: 1. Clique no botão drop-down do campo “Vendedor”. 2. Deixe marcadas apenas as caixas de seleção dos nomes Luiz e Sabrina. 3. Clique no botão OK.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 37
26/11/15 16:17
38
BANCO DE DADOS
Figura 9 – Caixas de seleção “Luiz” e “Sabrina”.
Para finalizar, ainda é possível filtrar os campos sob determinada condição, por exemplo, a de exibir um relatório somente com aqueles que venderam mais do que R$ 22.500,00. Para isso, devem ser realizados os seguintes procedimentos: 1. Clique no botão drop-down do campo “Total Vendas”. 2. Clique na opção Filtros de Números. 3. Clique na opção É Maior do que.
Figura 10 – Opções Filtros de Número e É Maior do que.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 38
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
39
4. Na caixa de diálogo, informe o valor de R$ 22.500,00. 5. Clique no botão OK.
Figura 11 – Caixa de diálogo Personalizar AutoFiltro.
6. Será exibido um relatório apenas com profissionais com vendas superiores a R$ 22.500,00.
Figura 12 – Relatório com exibição de profissionais com vendas superiores a R$ 22.500,00.
Considerações Por meio do recurso do AutoFiltro, é possível definir inúmeras condições de filtragem. Na caixa de listagem, é possível escolher dentre as diversas opções é maior do que, é diferente de, é menor ou igual a etc.
Figura 13 – Opções da caixa de listagem da caixa de diálogo Personalizar AutoFiltro.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 39
26/11/15 16:17
40
BANCO DE DADOS
As opções E/OU referem-se às operações lógicas boolianas de conjunção (E) e disjunção (OU). Por exemplo, é possível definir um valor maior do que R$ 20.000,00 E menor do que R$ 22.000,00. Nesse caso, serão exibidos somente os valores que atenderem às duas condições. Se a condição lógica E for substituída por OU, serão exibidos todos os valores que atendam no mínimo a uma delas. O conceito dos conectores E e OU serão vistos ainda neste capítulo.
Extratos de um banco de dados Para extrair parte de uma base de dados, utiliza-se o recurso de Filtro avançado do Excel. Diferentemente do AutoFiltro, o Filtro avançado permite extrair as informações de um banco de dados, permitindo formatá-la conforme desejado, sem alterar o banco de dados origem. Antes de apresentar o Filtro avançado, é necessário conhecer os operadores de comparação, pois serão muito utilizados na aplicação do Filtro avançado. Quadro 1 – Operadores de comparação
Operador de Comparação = (sinal de igual) > (sinal de maior) < (sinal de menor) >= (sinal de maior ou igual) <= (sinal de menor ou igual) <> (sinal de diferente)
Significado Igual a Maior que Menor que Maior ou Igual a Menor ou Igual a Diferente de
Exemplo A5=B5 A5>B5 A5<B5 A5>=B5 A5<=B5 A5<>B5
Quando se utilizam os operadores de comparação, o Excel retorna um resultado lógico, VERDADEIRO ou FALSO. Por exemplo: ao digitar na célula: =5>6, o resultado será FALSO porque 5 não é maior do que 6. Porém, se for digitado =5<6, o resultado será VERDADEIRO porque 5 é menor do que 6.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 40
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
41
Exercício 1. Digite os dados a seguir na célula de uma planilha Excel e informe o
resultado obtido. • =10<>10 • =“Excel”=“Word”c. =(5+2)>(3*2) • =“AutoFiltro”=“Filtro avançado”
Filtro local com Filtro avançado O Filtro local aplica-se na própria planilha que contém o banco de dados, bem como o AutoFiltro, porém utilizam-se os recursos do Filtro avançado. Para aplicação do Filtro local, é necessário um intervalo de critérios com os mesmos campos do banco de dados. Esse intervalo de critérios deve se localizar acima do banco de dados e deve haver no mínimo uma linha de distância entre eles. Intervalo de critérios
Base de dados
Figura 14 – Intervalo de critérios e base de dados para a aplicação de Filtro local com Filtro avançado.
Para saber as informações somente da categoria “Bebidas”, digita-se a palavra “Bebida” no campo “Categoria” do intervalo de critérios.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 41
26/11/15 16:17
42
BANCO DE DADOS
Figura 15 – Seleção da categoria “Bebidas”.
Depois, devem ser realizados os seguintes procedimentos: 1. Na guia Dados, no grupo Classificar e Filtrar, clique em Avançado. 2. Em Intervalo de lista, indique o intervalo da Base de Dados. 3. Em Intervalo de critérios, coloque o intervalo que contêm os critérios, indicando apenas a linha que contém os cabeçalhos e as linhas que contêm critérios, e não as linhas que não contêm critérios definidos. 4. Clique no botão OK.
Figura 16 – Caixa de diálogo Filtro avançado.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 42
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
43
5. Observe o resultado.
Figura 17 – Resultado da seleção da categoria “Bebidas” após aplicação do Filtro avançado.
Limpar Filtro avançado aplicado Para retirar o Filtro avançado aplicado, basta realizar os seguintes procedimentos: 1. Clique no banco de dados com o filtro aplicado. 2. Na guia Dados, no grupo Classificar e Filtrar, clique em Limpar.
Extrato do banco de dados Com o Filtro avançado é possível extrair as informações do banco de dados para outro intervalo de células. Com isso é possível formatá-las para outros fins sem afetar o banco de dados original. O processo inicial é o mesmo da aplicação do Filtro local explicado conforme a Figura 14. É necessário um intervalo de critérios com os mesmos campos do banco de dados de origem. Esse intervalo de critérios deve se localizar acima do banco e deve haver no mínimo uma linha de distância entre eles, conforme a Figura 19. Para extrair somente as informações relacionadas à categoria “Doces”, devem ser realizados os seguintes procedimentos: 1. Digite a palavra “Doces” na primeira linha do campo “Categoria”, no intervalo de critérios. 2. Na guia Dados, no grupo Classificar e Filtrar, clicar em Avançado.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 43
26/11/15 16:17
44
BANCO DE DADOS
3. Em Ação, clique na opção Copiar para outro local. 4. Em Intervalo de lista, indique o intervalo da base de dados. 5. Em Intervalo de critérios, coloque o intervalo que contém os critérios, indicando apenas a linha que contêm os cabeçalhos e as linhas que contêm critérios, e não as linhas que não contêm critérios definidos. 6. Em Copiar para, indique o local para onde os dados serão extraídos. 7. Clique no botão OK.
Figura 18 – Caixa de diálogo Filtro avançado.
8. Observe o resultado.
Figura 19 – Resultado da seleção da categoria “Doces” após aplicação do Filtro avançado.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 44
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
45
Na Figura 19, é possível observar que foram extraídas somente as informações relacionadas à categoria “Doces”. Porém, diferentemente do Filtro Local, a base de dados não sofreu alterações. Com isso é possível copiar e formatar o conteúdo extraído da forma que se desejar, preservando, assim, a integridade dos dados originais.
Filtro avançado com mais de um critério Para a utilização de filtros avançados com mais de um critério, faz-se necessário o entendimento dos conectores E e OU, além da conhecida álgebra de Boole (ou álgebra booliana). A álgebra booliana retorna apenas dois tipos de respostas diante de uma análise: VERDADEIRO ou FALSO. Ela analisa uma expressão com duas situações separadamente e depois transmite um dos dois possíveis resultados com base nas respostas dessa afirmação. Ao utilizar os conectores E (conjunção) ou OU (disjunção), é estabelecido um critério de análise para um determinado resultado.
Conector de conjunção (E) O conector de conjunção (E) permite analisar duas ou mais expressões lógicas. Para tanto, o resultado somente será VERDADEIRO se todas as expressões também forem VERDADEIRAS, como pode ser observado no Quadro 2. Quadro 2 – Análise de resultados de duas expressões com o conector de conjunção (E)
1a expressão
2a expressão
Resultado
Falso
Falso
FALSO
Falso
Verdadeiro
FALSO
Verdadeiro
Falso
FALSO
Verdadeiro
Verdadeiro
VERDADEIRO
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 45
26/11/15 16:17
46
BANCO DE DADOS
Exemplos Tomando por base o Quadro 2 e considerando 2>3 E 3=3, pode ser elaborado o Quadro 3 a seguir. Quadro 3 – Análise da conjunção 2>3 E 3=3
1a expressão
2 a expressão
2>3
3=3
Falso
Verdadeiro
Resultado FALSO
No Quadro 3, é possível observar que o resultado da primeira expressão (2>3), é Falso porque o número 2 não é maior do que 3. A segunda expressão (3=3) informa que o número 3 é igual a 3, portanto o resultado da segunda expressão é Verdadeiro. Com base no Quadro 2, analisando sua segunda linha (Falso, Verdadeiro, FALSO), é possível observar que o resultado da conjunção será FALSO, visto que para ser VERDADEIRO todas as expressões precisariam ser Verdadeiras, o que não ocorre, pois o resultado da primeira expressão é Falso. Baseando-se ainda no Quadro 2 e considerando a conjunção 10<>25 E 5+3>2*3, pode ser elaborado o Quadro 4 a seguir. Quadro 4 – Análise da conjunção 10<>25 E 5+3>2*3
1a expressão
2a expressão
10<>25
5+3>2*3
Verdadeiro
8>6
Verdadeiro
Verdadeiro
Resultado
VERDADEIRO
No Quadro 4, é VERDADEIRO o resultado da primeira expressão 10<>25, pois o número 10 é diferente de 25. Na segunda expressão, primeiramente devem ser resolvidas as equações para que os resultados sejam, então, comparados. Se 5+3=8 e 2*3=6, logo 8>6; portanto, o resultado também é Verdadeiro. Se todas as expressões são Verdadeiras,
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 46
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
47
então o resultado da conjunção é VERDADEIRO, como mostra a última linha do Quadro 2 (Verdadeiro, Verdadeiro, VERDADEIRO). Observação Podemos afirmar que, com o uso do conector E, o resultado do teste lógico somente será VERDADEIRO se todas as expressões forem Verdadeiras. Se apenas uma expressão for Falsa, o resultado da conjunção será FALSO.
Conector de disjunção (OU) O conector de disjunção (OU) permite analisar duas ou mais expressões lógicas. Para tanto, o resultado será VERDADEIRO se ao menos uma das expressões for VERDADEIRA, como pode ser observado no Quadro 5. Quadro 5 – Análise de resultados de duas expressões com o conector de disjunção (OU)
1a expressão
2 a expressão
Resultado
Falso
Falso
FALSO
Falso
Verdadeiro
VERDADEIRO
Verdadeiro
Falso
VERDADEIRO
Verdadeiro
Verdadeiro
VERDADEIRO
Exemplos Tomando por base o Quadro 5 e considerando os mesmos exemplos anteriores, mas com conectores diferentes, ou seja, 2>3 OU 3=3, pode ser elaborado o Quadro 6 a seguir. Quadro 6 – Análise da disjunção 10<>25 OU 5+3>2*3
1a expressão
2 a expressão
2>3
3=3
Falso
Verdadeiro
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 47
Resultado VERDADEIRO
26/11/15 16:17
48
BANCO DE DADOS
No Quadro 6, é possível observar que a primeira expressão é Falsa, porém a segunda é Verdadeira. Logo o resultado da disjunção é VERDADEIRO, como pode ser observado na segunda linha (Falso, Verdadeiro, VERDADEIRO) do Quadro 5. Baseando-se ainda no Quadro 5 e considerando agora a disjunção 10<>25 OU 5+3>2*3, pode ser elaborado o Quadro 7 a seguir. Quadro 7 – Análise da disjunção 10<>25 OU 5+3>2*3
1a expressão
2 a expressão
10<>25
5+3>2*3
Verdadeiro
8>6
Verdadeiro
Verdadeiro
Resultado
VERDADEIRO
No Quadro 7, como as duas expressões são Verdadeiras, o resultado da disjunção também é VERDADEIRO, como é observado na última linha do Quadro 5. Observação Podemos afirmar que, com o uso do conector OU, o resultado do teste lógico será FALSO se todas as expressões forem Falsas. Se apenas uma expressão for Verdadeira, o resultado da disjunção será VERDADEIRO.
Exercício 2. Informe o resultado das expressões a seguir:
• 325<294 E 1.450<>1.350= • Gato=Gato OU Rato>Cachorro= c. 25*12<250/5 OU 25*3>62*2= • 252*3<>2.550/2 E 935>3*200=
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 48
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
49
Filtro avançado Entendido o uso dos conectores E e OU, a mesma lógica deve ser aplicada ao Filtro avançado. Para filtrar dados utilizando o conceito do conector E, devem ser inseridos os argumentos na mesma linha do intervalo de critérios, como demonstra, por exemplo, a Figura 20, em que: Lógica booliana: Categoria=“Balas” E “Vendas”>23.000.
Figura 20 – Filtragem com uso do conector de conjunção E com a categoria “Balas” e valor de vendas maior do que 23.000.
Para filtrar dados utilizando o conceito do conector OU, devem ser inseridos os argumentos em linhas diferentes do intervalo de critérios, como pode ser visto na Figura 21, em que se tem: Lógica booliana: Vendedores=“André” OU Vendedores=“Marcelo”.
Figura 21 – Filtragem com uso do conector de disjunção OU com os vendedores “André” e “Marcelo”.
As figuras 22 e 23 também exemplificam o uso desses conectores. Na Figura 22, tem-se: Lógica booliana: Categoria=“Bebidas” OU Vendas<23.500.
Figura 22 – Filtragem com uso do conector de disjunção OU com a categoria “Bebidas” e o valor de venda menor do que 23.500.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 49
26/11/15 16:17
50
BANCO DE DADOS
Já na Figura 23, tem-se: Lógica booliana: Categoria=“Bebidas” OU (Categoria=“Cigarros” E Vendas>22.000).
Figura 23 – Filtragem com uso dos conectores de conjunção E e de disjunção OU com as categorias “Bebidas” e “Cigarros” e valor de venda maior do que 22.000.
É possível ainda utilizar o ponto de interrogação (?) ou o asterisco (*) para filtrar os dados da base de dados. O asterisco (*) busca qualquer número de caracteres. Na Figura 24, por exemplo, ele filtrará todos os dados do campo “Categoria” que terminem com as letras “as”.
Figura 24 – Filtragem da base de dados com uso do asterisco (*).
O ponto de interrogação (?) busca qualquer caractere único. Na Figura 25, ele filtrará todos os dados do campo “Vendedores” que contenham as letras informadas, independentemente da letra existente onde o ponto de interrogação estiver, por exemplo, Paulo, Paula, Paola, Pablo etc.
Figura 25 – Filtragem da base de dados com uso do ponto de interrogação (?).
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 50
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
51
Funções de banco de dados As funções de banco de dados se assemelham às funções comuns do Excel, porém elas foram criadas para facilitar o uso do banco de dados, em que o volume de informação pode ser muito grande. As funções de banco de dados apresentadas a seguir têm a mesma sintaxe: =FUNÇÃO_BD(banco_de_dados;campo;critérios). E os argumentos dessa função são explicados a seguir. • • • •
FUNÇÃO_BD – nome da função a ser chamada. banco_de_dados – intervalo de células do banco de dados. campo – nome do campo (coluna) que será considerado na função. critérios – critérios ou intervalo de células que contém os critérios de pesquisa.
Função BDCONTAR A função BD CONTAR conta as células que contêm números de determinado campo em um banco de dados.
Figura 26 – Função BDCONTAR.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 51
26/11/15 16:17
52
BANCO DE DADOS
Na Figura 26, a sintaxe da função fica: =BDCONTAR(A6:C16;”Vendas”;A1:C3). Nela, identificam-se: • BDCONTAR – nome da função de banco de dados. • A6:C16 – intervalo de células do banco de dados. • “Vendas” – nome do campo (coluna) do banco de dados que será utilizado para a contagem. • A1:C3 – intervalo de células que contém os critérios definidos para a função.
Execução dos procedimentos da função BDCONTAR A função BDCONTAR localiza a base de dados informada no intervalo de A6 até C16 e posiciona-se no campo “Vendas”. Em seguida, realiza a contagem do número de entradas que atendam aos critérios estabelecidos na Figura 26 no intervalo de células de A1 até C3: “Bebidas” com “Vendas” entre 23.000 e 25.000; e “Cigarros” de qualquer valor. Por fim, retorna o resultado da contagem: três entradas.
Função BDMÉDIA A função BDMÉDIA calcula a média dos valores em um campo de registros conforme as condições especificadas.
Figura 27 – Função BDMÉDIA.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 52
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
53
Na Figura 27, a sintaxe da função fica: =BDMÉDIA(A6:C16;“Vendas”;A1:C2). Nela, identificam-se: • BDMÉDIA – nome da função de banco de dados. • A6:C16 – intervalo de células do banco de dados. • “Vendas” – nome do campo (coluna) do banco de dados que será utilizado para calcular a média. • A1:C2 – intervalo de células que contém os critérios definidos para a função calcular a média.
Execução dos procedimentos da função BDMÉDIA Na Figura 27, a função BDMÉDIA localiza o banco de dados informado no intervalo de A6 até C16 e posiciona-se no campo “Vendas”. Em seguida, realiza o cálculo da média somente das entradas que atendam aos critérios estabelecidos no intervalo de células de A1 até C2: “Doces”. Ou seja, será calculada a média dos valores de vendas somente da categoria “Doces”, ignorando-se as demais.
Função BDSOMA A função BDSOMA calcula a soma dos valores em um campo de registros de um banco de dados conforme as condições especificadas.
Figura 28 – Função BDSOMA.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 53
26/11/15 16:17
54
BANCO DE DADOS
Na Figura 28, a sintaxe da função fica: =BDSOMA(A6:C16;“Vendas”;A1:C4). Nela, identificam-se: • BDSOMA – nome da função de banco de dados. • A6:C16 – intervalo de células do banco de dados. • “Vendas” – nome do campo (coluna) do banco de dados que será utilizado para calcular a soma. • A1:C4 – intervalo de células que contém os critérios definidos para a função calcular a soma.
Execução dos procedimentos da função BDSOMA A função BDSOMA localiza a base de dados informada no intervalo de A6 até C16 e posiciona-se no campo “Vendas”. Em seguida, realiza a soma somente das entradas que atendam aos critérios estabelecidos no intervalo de células de A1 até C4, ou seja, a soma apenas dos valores de vendas de Aline, Maria e Sabrina.
Função BDMÁX A função BDMÁX retorna o valor máximo em um campo de registro de um banco de dados conforme as condições especificadas.
Figura 29 – Função BDMÁX.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 54
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
55
Na Figura 29, a sintaxe da função fica: =BDMÁX(A6:C16;“Vendas”;A1:C4). Nela, identificam-se: • BDMÁX – nome da função de banco de dados. • A6:C16 – intervalo de células do banco de dados. • “Vendas” – nome do campo (coluna) do banco de dados que será utilizado para encontrar o valor máximo. • A1:C4 – intervalo de células que contém os critérios definidos para a função localizar o valor máximo.
Execução dos procedimentos da função BDMÁX A função BDMÁX localiza a base de dados informada no intervalo de A6 até C16 e posiciona-se no campo “Vendas”. Em seguida, retorna o valor máximo encontrado de acordo com os critérios estabelecidos no intervalo de células de A1 até C4, em que são considerados apenas os valores das categorias “Balas”, “Doces” e “Bebidas” com vendas com valores menores do que R$ 27.000,00.
Função BDMÍN A função BDMÍN retorna o valor mínimo em um campo de registro de um banco de dados conforme as condições especificadas.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 55
26/11/15 16:17
56
BANCO DE DADOS
Figura 30 – Função BDMÍN.
Na Figura 30, a sintaxe da função fica: =BDMÍN(A6:C16;“Vendas”;A1:C2). Nela, identificam-se: • BDMÍN – nome da função de banco de dados. • A6:C16 – intervalo de células do banco de dados. • “Vendas” – nome do campo (coluna) do banco de dados que será utilizado para encontrar o valor mínimo. • A1:C2 – intervalo de células que contém os critérios definidos para a função localizar o valor mínimo.
Execução dos procedimentos da função BDMÍN A função BDMÍN localiza a base de dados informada no intervalo de A6 até C16 e posiciona-se no campo “Vendas”. Em seguida, retorna o valor mínimo encon-
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 56
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
57
trado de acordo com os critérios estabelecidos no intervalo de células de A1 até C2, em que são considerados apenas os valores de vendas entre R$ 22.000,00 e R$ 26.000,00.
Função BDMULTIPL A função BDMULTIPL multiplica os valores em um campo de registro de um banco de dados conforme as condições especificadas. A 1 2 3
Produtos Mouse Teclado
B
C
Quantidades
Preço
<30
4 5 6 7 8 9 10 11 12 13 14 15 16 17
Produtos Pendrive Mouse Teclado Webcam CD-R DVD-R
Quantidades 12 26 14 32 47 34
Preço R$ 19,90 R$ 19,90 R$ 39,90 R$ 54,90 R$ 0,49 R$ 0,79
=BDMULTIPL(A6:C12;"Preço";A1:C4) Resultado: 15800,799 Figura 31 – Função BDMULTIPL.
Na Figura 31, a sintaxe da função fica: =BDMULTIPL(A6:C16;“Preço”;A1:C4). Nela, identificam-se: • BDMULTIPL – nome da função de banco de dados. • A6:C16 – intervalo de células do banco de dados. • “Preço” – nome do campo (coluna) do banco de dados que será utilizado para encontrar os valores a serem multiplicados. • A1:C4 – intervalo de células que contém os critérios definidos para a função.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 57
26/11/15 16:17
58
BANCO DE DADOS
Execução dos procedimentos da função BDMULTIPL A função BDMULTIPL localiza o banco de dados informado no intervalo de A6 até C16 e posiciona-se no campo “Preço”. Em seguida, multiplica os valores encontrados conforme os critérios definidos no intervalo de células de A1 até C4, ou seja, a função multiplica todos os valores encontrados dos produtos “Mouse” e “Teclado” e todos os demais valores com quantidade menor do que 30. Exercícios 3. Utilizando o Microsoft Office Excel, crie os campos conforme solicita-
• • •
•
do para início da construção do banco de dados. Na plan1, renomeie a planilha para “Mesas” e crie um campo na célula A1 com o nome “Mesas”. Na plan2, renomeie a planilha para “Categorias” e crie um campo na célula A1 com o nome “Categoria”. Na plan3, renomeie a planilha para “Produtos” e crie os campos da seguinte forma: i) A1 = “Categoria” ii) B1 = “Cod_Produto” iii) C1 = “Produto” iv) D1 = “Preço” Na plan4, renomeie a planilha para “Vendas” e os seguintes campos: • A1 = “Data” • B1 = “Mesa” • C1 = “Categoria” • D1 = “Cod_Produto” • E1 = “Produto” • F1 = “Quantidade” • G1 = “Preço” • H1 = ‘Total
• Salve a pasta de trabalho com o nome “BD_Bares_Restaurantes”. DICA: Para renomear o nome da planilha, clique com o botão direito do mouse sobre a guia da planilha e escolha a opção Renomear. Digite
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 58
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
59
o novo nome da planilha e pressione a tecla Enter. Para inserir uma nova planilha, pressione as teclas Shift + F11. 4. Em “BD_Bares_Restaurantes”, realize dez registros em cada banco de
dados. 5. Ainda na pasta de trabalho “BD_Bares_Restaurantes”, classifique o
banco de dados “Produtos” (planilha “Produtos”) por ordem de categoria. Salve a alteração. 6. Copie o banco de dados a seguir e, utilizando o Filtro avançado, infor-
me os resultados obtidos conforme o que se pede.
Figura 32 – Banco de dados.
• Extraia os dados correspondentes à categoria “Bebidas”. • Extraia os dados correspondentes às categorias “Balas” ou “Cigarros”. • Extraia os dados que atendam à seguinte condição: (Vendedores=“Sabrina” OU “Luiz”) E (Categoria=“Doces”). • Extraia os dados somente de vendedores que iniciem com as letras “P” OU “M”.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 59
26/11/15 16:17
60
BANCO DE DADOS
7. Associe as descrições às respectivas funções de banco de dados. 1) 2) 3) 4) 5)
BDSOMA BDMÁX BDMÍN BDMULTIPL BDCONTAR a)
Multiplica os valores em um campo de registro de um banco de dados.
b)
Retorna o valor máximo em um campo de registro de um banco de dados.
c)
Conta as células que contêm números de determinado campo em um banco de dados.
d)
Calcula a soma dos valores em um campo de registros de um banco de dados.
e)
Retorna o valor mínimo em um campo de registro de um banco de dados.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 60
26/11/15 16:17
3. Tabelas e gráficos dinâmicos Tabela Dinâmica Gráfico Dinâmico
Tabela Dinâmica Uma Tabela Dinâmica é um relatório gerado com base em um banco de dados, em que é possível manipular os dados de forma resumida a fim de tornar mais fácil uma análise, assim como possíveis tomadas de decisão. Com um relatório de Tabela Dinâmica, é possível consultar enormes quantidades de dados de forma resumida, manipulando os campos conforme se desejar. Além disso, ainda há a opção de expandir (exibir) ou recolher (ocultar) os dados no momento da análise, tornando as informações mais objetivas ou abrangentes. Os campos do banco de dados são os pontos determinantes da estrutura do relatório de Tabela Dinâmica, como pode ser observado na Figura 1.
Figura 1 – Banco de dados “Cursos – Tecnologia da Informação”.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 61
26/11/15 16:17
62
TABELAS E GRÁFICOS DINÂMICOS
Para aplicar um relatório de Tabela de Dinâmica do banco de dados, devem ser realizados os seguintes procedimentos: 1. Clique em qualquer célula dentro do intervalo de células do banco de dados. 2. Na guia Inserir, no grupo Tabelas, clique na ferramenta Tabela Dinâmica. 3. Clique na opção Tabela Dinâmica.
Figura 2 – Aplicação da Tabela Dinâmica.
Uma caixa de diálogo surgirá com a indicação do intervalo de célula correspondente ao banco de dados que será considerado para a construção do relatório de Tabela Dinâmica. O banco de dados vai apresentar uma linha tracejada na borda, que indica a área que será considerada para o relatório de Tabela Dinâmica. Caso a área não esteja de acordo com o desejado, basta inserir manualmente a referência do intervalo de células que deverá ser considerado. Também é possível definir o local onde o relatório de Tabela Dinâmica será criado, uma Nova Planilha ou uma Planilha Existente, que pode ser identificada caso essa opção seja selecionada. Após definir todas as configurações necessárias para a criação do relatório de Tabela Dinâmica, deve-se clicar no botão OK.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 62
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
63
Figura 3 – Caixa de diálogo Criar Tabela Dinâmica.
Após clicar no botão OK, uma nova planilha surgirá contendo a lista de Campos da Tabela Dinâmica (1) e a área onde os dados serão exibidos para análise (2).
Figura 4 – Nova planilha com a lista de Campos da Tabela Dinâmica.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 63
26/11/15 16:17
64
TABELAS E GRÁFICOS DINÂMICOS
Para montar a estrutura do relatório de Tabela Dinâmica, basta clicar nos campos desejados (1) para adicioná-los ao relatório. Por exemplo, para exibir a soma das idades mínimas requeridas para os cursos, deve-se clicar na caixa de seleção “Idade Mín.”. Como esse campo contém valores numéricos, ele será automaticamente adicionado à área Valores e será exibido em seu relatório com o valor total das idades, como mostra a Figura 5.
Figura 5 – Seleção do campo “Idade Mín.”.
Pode-se também alterar a forma de cálculo do campo selecionado, alterando, por exemplo, a média pela soma da “Idade Mín.”. Para isso, devem ser realizados os seguintes procedimentos: 1. Clique com o botão esquerdo do mouse sobre o campo adicionado na área Valores. 2. Clique na opção Configurações do Campo de Valor, no menu suspenso que será exibido.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 64
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
65
Figura 6 – Opção Configurações do Campo de Valor.
3. Na caixa de diálogo, em Resumir Valor por, escolha a opção Média e clique no botão OK. 4. Observe que o relatório será atualizado automaticamente.
Figura 7 – Opção Média da caixa de diálogo Configurações do Campo de Valor.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 65
26/11/15 16:17
66
TABELAS E GRÁFICOS DINÂMICOS
Figura 8 – Atualização automática do relatório.
Na lista de Campos da Tabela Dinâmica, além da área Valores vista acima, que mostra os valores numéricos que serão exibidos na Tabela Dinâmica e que permite configurar a forma como estes são calculados, há também outras três áreas: • Rótulo de Linha – refere-se aos cabeçalhos das linhas que serão exibidos no relatório de Tabela Dinâmica. • Rótulo de Coluna – refere-se aos cabeçalhos de colunas que serão exibidos no relatório de Tabela Dinâmica. • Filtro de Relatório – refere-se ao filtro que ficará localizado acima do relatório de Tabela Dinâmica, permitindo manipular as informações conforme se desejar.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 66
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
67
Figura 9 – Áreas da lista de Campos da Tabela Dinâmica.
Exemplos A seguir, são apresentados exemplos e explicações de como alguns relatórios foram construídos, tendo como base o banco de dados da Figura 1. A Figura 10 mostra um relatório de contagem de cursos.
Figura 10 – Relatório de contagem de cursos.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 67
26/11/15 16:17
68
TABELAS E GRÁFICOS DINÂMICOS
Para criar o relatório da Figura 10, devem ser realizados os seguintes procedimentos: 1. Selecione o campo Tipo na lista de campos a escolher. 2. Selecione o campo Cursos na lista de campos a escolher. Por padrão, os campos Tipo e Cursos serão adicionados na área Rótulo de Linha. 3. Utilizando o recurso Pressionar e Arrastar, mova o campo Cursos da área de Rótulo de Linha para a área Valores. 4. Como o campo Cursos contém valores do tipo texto, o Excel vai atribuir o cálculo de contagem automaticamente. Caso isso não ocorra, realize o procedimento de Configuração do Campo de Valor, visto anteriormente.
Figura 11 – Relatório de carga horária total versus tipo.
Para criar o relatório da Figura 11, devem ser realizados os seguintes procedimentos: 1. Selecione o campo Tipo na lista de campos a escolher.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 68
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
69
2. Selecione o campo C.Horária na lista de campos a escolher. 3. O campo C.Horária, por se tratar de um campo com valores numéricos, será atribuído à área Valores automaticamente. Configure a forma de cálculo se necessário. A seguir, serão apresentados exemplos e explicações de como alguns relatórios foram construídos, tendo como base o banco de dados da Figura 12.
.
Figura 12 – Banco de dados de produtos de supermercado.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 69
26/11/15 16:17
70
TABELAS E GRÁFICOS DINÂMICOS
Figura 13 – Relatório de preço versus produto com filtro por categoria.
Para criar o relatório da Figura 13, devem ser realizados os seguintes procedimentos: 1. Selecione o campo Produtos na lista de campos a escolher. 2. Selecione o campo Preço na lista de campos a escolher. 3. Selecione o campo Categoria na lista de campos a escolher. 4. Mova o campo Categoria da área de Rótulos de Linha para a área de Filtro de Relatórios a fim de que a configuração seja a mesma da Figura 13. 5. Observe o filtro aplicado acima do relatório de Tabela Dinâmica gerado. Ao clicar na seta, é exibida uma lista de seleção por categoria que possibilita filtrar os itens do relatório por categoria escolhida.
Excel avançado 2013_miolo.indd 70
03/10/17 08:51
MICROSOFT OFFICE EXCEL 2013
71
Figura 14 – Lista de seleção por categoria.
Ao escolher uma opção, observe que serão exibidos no relatório somente os produtos relacionados à categoria escolhida. Na Figura 15, é escolhida a categoria “Bebidas” como filtro.
Figura 15 – Escolha da categoria “Bebidas”.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 71
26/11/15 16:17
72
TABELAS E GRÁFICOS DINÂMICOS
A Figura 16 mostra o relatório com quantidade de produtos versus categoria.
Figura 16 – Relatório com quantidade de produtos versus categoria.
Para criar o relatório, devem ser realizados os seguintes procedimentos: 1. Selecione os campos Categoria, Produtos e Quantidade na lista de campos a escolher. 2. Mova o campo Produtos da área Rótulos de Linha para a área Rótulos de Colunas. 3. Configure o campo Quantidade na área Valores se necessário. Observação Conforme foi visto nos exemplos anteriores, há diversas formas de exibir os valores em um relatório de Tabela Dinâmica, além das apresentadas até agora. Tudo depende da necessidade do usuário e de seu conhecimento quanto às configurações do relatório. O ideal é tentar outras formas de exibição, montar outros bancos de dados com mais informações e explorar ao máximo todos os recursos disponíveis. Quanto mais conhecimento for adquirido sobre as configurações do relatório
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 72
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
73
de Tabela Dinâmica, maiores serão as possibilidades de manipulação dos dados a serem exibidos para análise.
Gráfico Dinâmico Um Gráfico Dinâmico, ou relatório de Gráfico Dinâmico, ou ainda relatório de Tabela Dinâmica Associada, oferece uma representação gráfica dos dados de um relatório de Tabela Dinâmica. Para melhor entender, deve-se observar a comparação do relatório de Tabela Dinâmica com o Gráfico Dinâmico feita a seguir.
Figura 17 – Rótulos de Linha e Rótulos de Coluna na Tabela Dinâmica.
Rótulos de Linha da Tabela Dinâmica correspondem à categoria do eixo X do Gráfico Dinâmico. Rótulos de Coluna na Tabela Dinâmica correspondem aos dados lidos com parâmetros representados no eixo Y. Para aplicar um relatório de Gráfico Dinâmico, devem ser realizados os seguintes procedimentos:
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 73
26/11/15 16:17
74
TABELAS E GRÁFICOS DINÂMICOS
4. Clique em qualquer célula dentro do intervalo de células do banco de dados. 5. Na guia Inserir, no grupo Gráficos, clique na ferramenta Gráfico Dinâmico.
Figura 18 – Ferramenta Gráfico Dinâmico.
6. Uma nova planilha será criada conforme mostra a Figura 19.
Figura 19 – Nova planilha criada.
Excel avançado 2013_miolo.indd 74
03/10/17 08:53
MICROSOFT OFFICE EXCEL 2013
75
A estrutura é a mesma do relatório de Tabela Dinâmica, e a única diferença é o acréscimo da área que receberá o Gráfico Dinâmico (Gráfico 1). A forma de configuração dos campos para exibição também permanece igual, basta selecionar os campos na Lista de campos da Tabela Dinâmica, alocá-los nas devidas áreas e configurar o campo da área Valores quando necessário, como mostra a Figura 20.
Figura 20 – Gráfico “Soma de Quantidade”.
Na Figura 20, foram selecionados os campos Categoria e Quantidade, em que o campo Categoria ficou na área Rótulos de Linha e o campo Quantidade na área Valores, configurado para efetuar a soma das quantidades. Dessa forma, o Gráfico Dinâmico apresenta as quantidades totais separadas por categoria. É possível ainda mover o Gráfico Dinâmico para uma planilha única, ou seja, uma planilha que exibe apenas o Gráfico Dinâmico e nada mais. Há duas formas de realizar essa operação.
Uso do menu interativo do Excel Para mover o gráfico usando o menu interativo do Excel, devem ser realizados os seguintes procedimentos: 1. Selecione o gráfico, clicando uma vez com o botão esquerdo do mouse sobre ele.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 75
26/11/15 16:17
76
TABELAS E GRÁFICOS DINÂMICOS
2. Surgirá uma guia adicional referente às Ferramentas de Gráfico Dinâmico. Então clique na guia secundária Design. 3. No grupo Local, clique em Mover Gráfico.
Uso do mouse Para mover o gráfico usando o mouse, devem ser realizados os seguintes procedimentos: 1. Clique com o botão direito do mouse sobre o gráfico para exibir o menu suspenso de opções. 2. Clique em Mover Gráfico. 3. Em ambos os casos será aberta a caixa de diálogo Mover Gráfico, conforme a Figura 21.
Figura 21 – Caixa de diálogo Mover Gráfico.
A opção Nova planilha cria uma nova planilha somente para exibição do gráfico. Na caixa de texto, onde está escrito “Gráf1”, é possível atribuir um nome para a planilha nova que será criada. A opção Objeto em permite escolher em que local, entre as planilhas existentes, o gráfico será fixado. Na Figura 21, o gráfico se encontra na planilha Plan7. Para mover o gráfico para outra planilha, deve-se escolher a planilha de destino em Objeto em. Para criar uma planilha e mover o gráfico para ela, deve-se clicar em Nova planilha e atribuir um nome a ela se for desejado.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 76
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
77
Na Figura 22, é criada uma planilha com o nome “Grafico_Dinamico” para onde o gráfico será movido.
Figura 22 – Criação da planilha “Grafico_Dinamico”.
Figura 23 – Gráfico “Rótulos de Dados”.
Rótulos de dados são os dados que aparecem nos pontos de dados do gráfico. Esses dados podem ficar ocultos ou serem exibidos. Para configurar essas e outras opções do gráfico de forma rápida, devem ser realizados os seguintes procedimentos:
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 77
26/11/15 16:17
78
TABELAS E GRÁFICOS DINÂMICOS
1. Clique em qualquer local do gráfico. 2. Use os botões localizados na parte superior direita do gráfico para obter a aparência e configuração desejada.
Figura 24 – Botões na parte superior direita do gráfico.
a. O botão com sinal de mais (+) permite configurar os elementos do gráfico, como exibir/ocultar Eixos, Títulos, Rótulos, Tabela de Dados, Barras, Linhas, Legendas e Linha de Tendência. As opções podem variar conforme o modelo do gráfico utilizado. b. O botão com o desenho de um pincel permite alterar o estilo (Design) e as cores usadas no gráfico. Observação As opções são dinâmicas, ou seja, ao posicionar o mouse sobre as opções o gráfico tomará a nova aparência. Mas o recurso só será empregado no gráfico se houver o clique do mouse sobre a ação desejada. Na Figura 25, foram aplicados no gráfico os elementos Eixos, Título do Gráfico, Rótulos de Dados, Tabela de Dados e Legenda. Foram também aplicados o Estilo 3 e a Cor 4.
Figura 25 – Gráfico de total de alimentos, bebidas e frutas.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 78
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
79
Exercícios 1. Monte a tabela a seguir e siga conforme o que for solicitado.
Figura 26 – Tabela de livros segundo autor, título, editora e preço.
• Elabore um Gráfico Dinâmico que mostre o total de títulos por autor. • Elabore um relatório de Tabela Dinâmica que demonstre o valor total gasto com cada editora. 2. Em um Gráfico Dinâmico, diferencie Rótulo de Dados de Pontos de
Dados.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 79
26/11/15 16:17
4. Análise de dados Cenários Auditoria de células Importação de dados Suplemento Solver
Cenários Em certos momentos é necessário realizar testes de hipóteses para analisar uma situação em contextos diferentes, por exemplo, avaliar a venda de produtos importados com algumas possibilidades de oscilação do dólar. O Microsoft Office Excel 2013 oferece várias ferramentas diferentes para realizar esse teste. Cada ferramenta contém tipos de análises diferentes conforme as necessidades. Uma das ferramentas mais usadas no teste de hipóteses é a ferramenta Cenários. A ferramenta Cenários usa um conjunto de dados de entrada e determina possíveis resultados para análise na planilha. Um cenário pode ter diversas variáveis, porém aceita até 32 dados de entrada. A Figura 1 mostra uma tabela criada para calcular a receita de cursos vendidos com base no número de alunos matriculados. Os dados da coluna “Valor” contêm funções que vão multiplicar o número de matriculados pelo valor por aluno de cada curso. No fim da coluna, será calculado o valor total da receita.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 80
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
A
B
C
Cursos AutoCad 2D Corel Draw Excel Avançado Informática Básica LibreOffice Manutenção de Microcomputadores Web Designer Word Avançado
Alunos
Valor =B3*300 =B4*320 =B5*234 =B6*450 =B7*250 =B8*380 =B9*290 =B10*250 =SOMA(C3:C10)
81
1 2 3 4 5 6 7 8 9 10 11
Total
12
Figura 1 – Tabela de cursos.
Agora serão criados três cenários para análise. Cada um contém um número de matrículas conforme a tabela na Figura 2.
Número de Alunos Mín. Méd. Máx. 10 15 20 10 15 20 15 20 30 20 30 40 20 30 40 6 8 10 8 14 18 15 20 30 Figura 2 – Tabela “Número de alunos”.
Primeiramente, é necessário selecionar as células variáveis do cenário, que no exemplo é o intervalo de células de B3 até B10 e a célula A1.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 81
26/11/15 16:17
82
ANÁLISE DE DADOS
A
B
C
Cursos AutoCad 2D Corel Draw Excel Avançado Informática Básica LibreOffice Manutenção de Microcomputadores Web Designer Word Avançado
Alunos
Valor =B3*300 =B4*320 =B5*234 =B6*450 =B7*250 =B8*380 =B9*290 =B10*250 =SOMA(C3:C10)
1 2 3 4 5 6 7 8 9 10
Total
11 12
Figura 3 – Intervalo selecionado na tabela de cursos.
Para criar os cenários, devem ser realizados os seguintes procedimentos: 1. Clique na guia Dados. 2. No grupo Ferramentas de Dados, clique em Teste de Hipóteses. 3. Clique em Gerenciador de Cenários.
Figura 4 – Opção Gerenciador de Cenários.
4. Na caixa de diálogo Gerenciador de Cenários, clique no botão Adicionar.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 82
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
83
Figura 5 – Caixa de diálogo Gerenciador de cenários.
5. Indique um nome para identificação do primeiro cenário que será criado. Observe que o campo Células variáveis contém o intervalo de células selecionado na planilha para criação do cenário. 6. Clique no botão OK.
Figura 6 – Caixa de diálogo Adicionar cenário.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 83
26/11/15 16:17
84
ANÁLISE DE DADOS
7. Na caixa de diálogo Valores de cenário, informe os valores desejados em cada célula que será gravada dentro do cenário criado. No exemplo, serão considerados os valores mínimos da tabela “Número de alunos” (Figura 2).
Figura 7 – Caixa de diálogo Valores de cenário.
8. Clique no botão OK para a criação do primeiro cenário. 9. Na caixa de diálogo Gerenciador de cenários, clique em Adicionar para criar o segundo cenário. (Lembre-se de que serão criados três cenários.)
Figura 8 – Botão Adicionar da caixa de diálogo Gerenciador de cenários.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 84
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
85
10. Repita a partir do procedimento 5 para criar o segundo cenário. Dê o nome de “Ponto de Equilíbrio” para o segundo cenário e considere a coluna “Méd.” da tabela “Número de alunos” (Figura 2) para a inserção dos valores no procedimento 7 feito na caixa de diálogo Valores de cenário. 11. Repita a partir do procedimento 5 para criar o terceiro cenário. Dê o nome de “Máximo de Matrículas” para criar o terceiro cenário e considere a coluna “Máx.” da tabela “Número de alunos” (Figura 2) para a inserção dos valores no procedimento 7 feito na caixa de diálogo Valores de cenário. 12. Depois de criar os três cenários, a caixa Gerenciador de cenários mostrará todos os cenários criados.
Figura 9 – Caixa de diálogo Grenciador de cenários com os três cenários criados.
13. Para exibir os cenários, selecione um dos cenários criados e clique no botão Mostrar. A tabela vai apresentar os valores referentes a cada cenário para análise.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 85
26/11/15 16:17
86
ANÁLISE DE DADOS
Sempre que for necessário adicionar, excluir ou editar um cenário, deve-se realizar os procedimentos a seguir para a abertura da caixa de diálogo Gerenciador de cenários (Figura 9) e clicar em um dos botões desejados: Adicionar, Excluir ou Editar. Para excluir ou editar, é necessário antes selecionar o cenário que será modificado ou excluído na caixa de diálogo Gerenciador de cenários. 1. Clique na guia Dados. 2. No grupo Ferramentas de Dados, clique na ferramenta Teste de Hipóteses. 3. Escolha a opção Gerenciador de cenários.
Auditoria de células Quando são usadas uma fórmula ou função em uma célula, apenas o resultado dela fica visível, e a função usada não. Para saber se essa função retornou o resultado correto, pode-se clicar sobre a célula e analisá-la na barra de fórmulas. Para auxiliar esse trabalho em uma planilha com várias funções, o Excel 2013 apresenta uma área específica chamada Auditoria de Fórmulas, ou Auditoria de Células como é conhecida. As ferramentas de Auditoria de Fórmulas se encontram no seguinte caminho: 1. Clique na guia Fórmulas. 2. No grupo Auditoria de Fórmulas, escolha uma das ferramentas que são explicadas a seguir.
Figura 10 – Grupo Auditoria de Fórmulas.
• Rastrear Precedentes – exibe setas que indicam que células afetam o valor da célula selecionada.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 86
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
87
Tabuada 5 Constante 0 1 2 3 4 5 6 7 8 9
Resultado 0 5 10 15 20 25 30 35 40 45
Figura 11 – Ferramenta Rastrear Precedentes.
• Rastrear Dependentes – exibe setas que indicam que células são afetadas pelo valor da célula selecionada. Tabuada 5 Constante 0 1 2 3 4 5 6 7 8 9
Resultado 0 5 10 15 20 25 30 35 40 45
Figura 12 – Ferramenta Rastrear Dependentes.
• Remover Setas – remove as setas de Rastrear Precedentes e Rastrear Dependentes. • Mostrar Fórmulas – exibe a fórmula/função de cada célula, em vez do valor resultante.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 87
26/11/15 16:17
88
ANÁLISE DE DADOS
Figura 13 – Ferramenta Mostrar Células.
• Verificação de Erros – procura os erros comuns que existem nas fórmulas/funções. Verifica todas as funções que existem na planilha e retorna uma caixa de mensagem de ajuda na correção de erros encontrados.
Figura 14 – Caixa de diálogo Verificação de Erros.
Na Figura 14, a caixa de diálogo da ferramenta Verificação de erros informa que foi encontrado um erro na célula E11 e exibe a função/fórmula que consta na célula: =$C$3*Cc. Diante do erro encontrado é possível escolher entre as opções: • Ajuda sobre este erro – abre o autoajuda do Excel para encontrar tópicos que possam auxiliar na correção da fórmula/função. • Ignorar erro – ignora o erro encontrado, finalizando a procura de erros. Nessa opção, a fórmula ou função não será corrigida e permanecerá como se encontra. • Editar na barra de fórmulas – posiciona o cursor em modo de edição na barra de fórmulas do Excel para correção manual da função.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 88
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
89
• Mostrar etapas de cálculo – abre uma nova caixa de mensagem possibilitando o acompanhamento de cada etapa da função/fórmula na tentativa de se encontrar o erro. A primeira parte mostra a função que será avaliada: =5*Cc.
Figura 15 – Função 5*Cc avaliada na caixa de diálogo Avaliar fórmula.
Ao clicar no botão Avaliar, é demonstrado que a referência Cc resultou em um erro: #NOME?. Isso quer dizer que o erro na função se encontra nessa referência: Cc.
Figura 16 – Erro 5*#NOME? indicado na caixa de diálogo Avaliar fórmula.
Ao clicar no botão Avaliar mais uma vez, o resultado da função será exibido, e será também um erro.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 89
26/11/15 16:17
90
ANÁLISE DE DADOS
Figura 17 – Erro #NOME? indicado na caixa de diálogo Avaliar fórmula.
Nesse caso, cabe ao usuário analisar a função e identificar a lógica correta para que ela funcione corretamente. A alteração deve ser feita diretamente na célula em que consta a função errada. Para isso, deve-se clicar no botão Fechar e realizar a correção da função, ou clicar no botão Reiniciar para rever as etapas da função. • Avaliar Fórmula – exibe a caixa de diálogo Avaliar fórmula para analisar, individualmente, cada parte de uma fórmula/função. A cada clique no botão Avaliar é demonstrada a próxima etapa na função. Se esta contiver uma referência, essa referência será convertida em seu valor real. No fim, é demonstrado o resultado da função.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 90
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
91
Figura 18 – Etapas de uma função demonstradas na caixa de diálogo Avaliar fórmula.
• Janela de inspeção: monitora os valores de determinadas células à medida que são feitas alterações na planilha. Por exemplo, o caso de uma pasta de trabalho em que na Planilha 1 existam dados de uma tabela e na Planilha 2 haja uma célula que realize a média desses dados. Se fosse preciso alterar um valor na Planilha 1, seria necessário clicar na Planilha 2 para visualizar o resultado. Com a Janela de inspeção, é possível posicionar uma janela na Planilha 1 e observar o resultado que aparece na Planilha 2 sem a necessidade de mudar de planilhas.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 91
26/11/15 16:17
92
ANÁLISE DE DADOS
Para acessar a Janela de inspeção, deve-se estar com a planilha que será trabalhada ativa e seguir os procedimentos a seguir. 1. Clique na guia Fórmulas. 2. No grupo Auditoria de Fórmulas, clique em Janela de inspeção. 3. Na caixa de diálogo Janela de inspeção, clique em Adicionar inspeção de variáveis para indicar a célula que será monitorada.
Figura 19 – Caixa de diálogo Janela de inspeção.
4. Na caixa de diálogo Adicionar inspeção de variáveis, indique a célula que será monitorada e clique no botão Adicionar.
Figura 20 – Caixa de diálogo Adicionar inspeção de variáveis.
5. A Janela de Inspeção exibirá as informações relativas à célula referenciada para inspeção, incluindo seu resultado no campo “Valor”. Sempre que um dado for alterado na Planilha 1, o resultado da função da Planilha 2 será atualizado automaticamente, e essa informação será exibida, em tempo real, na Janela de inspeção. É possível referenciar várias células para inspeção simultaneamente.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 92
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
93
Figura 21 – Informações da célula referenciada na caixa de diálogo Janela de inspeção.
Importação de dados Muitas vezes é necessário trabalhar com informações de uma base externa, porém essa base pode ser atualizada com certa frequência. Seria trabalhoso que sempre que se abrisse o Excel houvesse a necessidade de abrir também a base de dados para verificar a ocorrência ou não de atualizações, ou ainda a necessidade de ter de copiar as informações da base de dados e colar na planilha para mantê-la sempre atualizada. Para evitar esse trabalho, no Excel há a possibilidade de importar dados com atualização automática. Dessa forma, as informações importadas sempre estarão atualizadas, sem a necessidade de ter de abrir a base de dados para atualizá-las. O Excel 2013 permite importar diversos tipos de dados. Entre os mais conhecidos e usados podemos citar os dados de um arquivo de texto ou de um banco de dados do Microsoft Office Access.
Importação de dados do Microsoft Office Access Com a planilha do Excel aberta, devem ser realizados os seguintes procedimentos: 1. Clique na guia Dados.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 93
26/11/15 16:17
94
ANÁLISE DE DADOS
2. No grupo Obter Dados Externos, clique em Do Access. 3. Ao abrir a caixa de diálogo Selecionar fonte de dados, indique o banco que deseja importar e clique no botão Abrir.
Figura 22 – Caixa de diálogo Selecionar fonte de dados.
4. Caso queira selecionar mais de uma tabela, marque a caixa de seleção Habilitar a seleção de várias tabelas e selecione as tabelas que deseja importar. 5. Clique no botão OK.
Figura 23 – Caixa de diálogo Selecionar tabela.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 94
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
95
6. Depois escolha o modo como os dados serão exibidos na pasta de trabalho do Excel e onde serão colocados. A Figura 24 mostra a criação de um relatório de Tabela Dinâmica com os dados do banco de dados do Access. O relatório será colocado a partir da célula A1 da planilha atual ativa. Depois de se clicar no botão OK, o tempo de importação dependerá do tamanho do banco de dados original e da capacidade de processamento do computador, podendo ser rápido ou um pouco demorado.
Figura 24 – Caixa de diálogo Importar Dados.
Uma Tabela Dinâmica será criada contendo todas as informações do banco de dados. O funcionamento da Tabela Dinâmica é igual ao que foi visto no Capítulo 3.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 95
26/11/15 16:17
96
ANÁLISE DE DADOS
Figura 25 – Planilha com lista de Campos da Tabela Dinâmica.
Importação de dados de texto Com a planilha do Excel aberta, realize os seguintes procedimentos: 1. Clique na guia Dados. 2. No grupo Obter Dados Externos, clique em De Texto. 3. Ao abrir a caixa de diálogo Importar arquivo de texto para seleção da fonte de dados, indique o arquivo de texto que deseja importar e clique no botão Importar.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 96
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
97
Figura 26 – Caixa de diálogo Importar arquivo de texto.
4. Será aberta a caixa de mensagem Assistente de importação de texto, em cuja primeira etapa é necessário marcar a opção que melhor defina como os dados se encontram no arquivo. • Delimitado – serve para um arquivo cujos campos são separados por vírgulas (,), ponto e vírgula (;), espaços etc. • Largura fixa – serve para arquivos cujos campos estão alinhados como colunas, separados com um ou mais espaços entre um campo e outro. O arquivo selecionado contém os campos separados por vírgulas (,); dessa forma, a melhor opção será Delimitado.
Figura 27 – Texto a ser importado.
5. Clique no botão Avançar.
Excel avançado 2013_miolo.indd 97
03/10/17 08:54
98
ANÁLISE DE DADOS
Figura 28 – Primeira etapa de importação mostrada na caixa Assistente de importação de texto.
6. Na segunda etapa, é preciso definir os delimitadores existentes no arquivo de dados. O arquivo de texto contém o sinal de vírgula (,) como delimitador; dessa forma, a opção Vírgula deve ser selecionada. Na parte inferior da janela é possível visualizar como o texto será afetado. As linhas verticais indicam que o delimitador foi substituído pelas linhas de coluna, formando os campos da tabela. 7. Clique no botão Avançar.
Figura 29 – Segunda etapa de importação mostrada na caixa Assistente de importação de texto.
Excel avançado 2013_miolo.indd 98
03/10/17 08:57
MICROSOFT OFFICE EXCEL 2013
99
8. Na terceira e última etapa, ainda é possível definir o formato dos dados de cada campo. Esse processo também pode ser feito posteriormente, utilizando as ferramentas de formatação da própria planilha Excel. 9. Clique no botão Concluir.
Figura 30 – Terceira etapa de importação mostrada na caixa Assistente de importação de texto.
Após concluir as três etapas de importação, é preciso escolher o local onde o arquivo será importado. Na Figura 31, o arquivo será importado a partir da célula A1 da planilha atual ativa.
Figura 31 – Importação de arquivo a partir da célula A1 da planilha atual ativa.
Excel avançado 2013_miolo.indd 99
03/10/17 08:57
100
ANÁLISE DE DADOS
O arquivo de texto é importado em forma de tabela na planilha Excel, como mostra a Figura 32.
1 2 3 4 5 6 7
A Nome Alex Luciana Karen Gustavo Lívia Matheus
B
C Sexo Idade Masculino 32 Feminino 44 Feminino 23 Masculino 25 Feminino 4 Masculino 3
Figura 32 – Tabela importada.
Utilizando as ferramentas de formatação do próprio Excel, é possível modificá-la.
1 2 3 4 5 6 7
A B C Nome Sexo Idade Alex Masculino 32 Luciana Feminino 44 Karen Feminino 23 Gustavo Masculino 25 Lívia Feminino 4 Matheus Masculino 3
Figura 33 – Aplicação de ferramentas de formatação na tabela importada.
Observação Para formatar uma tabela com vínculos externos, não utilize as ferramentas do grupo Estilo, caso contrário todos os vínculos externos serão perdidos. Os Vínculos externos serão mais bem descritos ainda neste capítulo.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 100
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
101
Importação de dados da internet Para importar dados da internet, basta visitar a página que contém as informações que serão importadas, clicar com o botão direito sobre a tabela que será importada para o Excel e escolher a opção Exportar para o Microsoft Excel.
Figura 34 – Exportação de página na internet para o Excel.
Uma nova planilha será criada e a tabela será exportada para o Excel automaticamente. 1 2 3 4 5 6 7 8 9 10 11 12
A Cidade Melbourne/Estocolmo Sydney Innsbruck Innsbruck Antuérpia Antuérpia Montreal Lake Placid Calgary St. Moritz St. Moritz
B NOC_PaísRegião AUS AUS AUT AUT BEL BEL CAN CAN CAN SUI SUI
C Código Alfa-2 AS AS AT AT BE BE CA CA CA SZ SZ
D Edição 1956 2000 1964 1976 1920 1920 1976 1980 1988 1928 1948
E Estação Verão Verão Inverno Inverno Verão Inverno Verão Inverno Inverno Inverno Inverno
Figura 35 – Tabela da internet exportada para o Excel.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 101
26/11/15 16:17
102
ANÁLISE DE DADOS
Observação Quando se diz Importar dados para o Excel, isso significa que a janela ativa é a do Excel, que receberá os dados externos. Quando se fala Exportar dados para o Excel, isso quer dizer que a janela ativa é a dos dados que deverão ser copiados e levados para o Excel. Dessa forma, importar dados significa trazer de fora para dentro e exportar dados significa levar de dentro para fora.
Atualização de dados importados Há três formas de atualizar os dados importados para o Excel. Primeiramente, deve-se clicar na guia Dados e, no grupo Conexões, escolher uma das seguintes opções:
Figura 36 – Opções do grupo Conexões.
• Atualizar Tudo – atualiza todas as fontes da pasta de trabalho. • Atualizar – atualiza a fonte somente da célula ativa conectada à fonte de dados. As duas opções acima vão abrir novamente a janela de importação do arquivo. É preciso, então, selecionar novamente a fonte de dados e clicar no botão Abrir ou Importar.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 102
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
103
Figura 37 – Janelas Selecionar fonte de dados e Importar arquivo de texto.
• Propriedades da Conexão – permite configurar atualizações automáticas da tabela importada de forma periódica.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 103
26/11/15 16:17
104
ANÁLISE DE DADOS
Figura 38 – Janela Propriedades da Conexão.
Em Nome da conexão, encontra-se o nome do arquivo de origem dos dados. Em Controle de atualização, é possível configurar a forma com que a tabela deverá ser atualizada, por exemplo: • Solicitar nome de arquivo ao atualizar – se selecionada, abrirá automaticamente a janela de importação do arquivo para selecionar o arquivo que atualizará os dados da tabela. Se não selecionada, a tabela será atualizada automaticamente. (Importante: o arquivo não pode sofrer alterações como nome do arquivo, local de armazenamento etc.) • Atualizar a cada ... minutos – permite indicar, em minutos, a periodicidade com que a tabela será atualizada automaticamente. • Atualizar dados ao abrir o arquivo – permite atualizar a tabela assim que o arquivo é aberto.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 104
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
105
• Atualizar esta conexão em Atualizar Tudo – permite manter essas configurações quando a opção Atualizar Tudo for acionada. Por exemplo, se forem definidas as configurações mostradas na Figura 39 na janela Propriedades da Conexão, a tabela será atualizada automaticamente a cada três minutos, sem solicitar a seleção do arquivo de origem.
Figura 39 – Definição da periodicidade de atualização de uma tabela.
Suplemento Solver O Excel disponibiliza inúmeros suplementos e funções para a obtenção de resultados dos mais variados tipos. Entre eles existe o suplemento Solver, que permite a realização de simulações no Excel conforme limites e restrições predefinidos. O suplemento Solver é amplamente utilizado na área de engenharia, porém sua aplicação pode ser realizada em quaisquer áreas. Ele não é um suplemento ativo no Excel, porém sua ativação é simples e não requer investimentos.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 105
26/11/15 16:17
106
ANÁLISE DE DADOS
Ativação do suplemento Solver 1. Clique na guia Arquivo. 2. Clique em Opções. 3. Clique em Suplementos. 4. Em suplementos inativos, clique na opção Solver. 5. Clique no botão Ir. 6. Ao abrir a janela de Suplementos, marque apenas a opção Solver. 7. Clique no botão OK. 8. Uma caixa de mensagem surgirá perguntando se deseja instalá-lo agora. Clique no botão Sim. 9. Aguarde até o fim do processo de instalação. Pode ser que o Excel seja reiniciado para a ativação do suplemento Solver, porém, se isso ocorrer, será automaticamente. 10. Para confirmar a instalação, clique na guia Dados e, no grupo Análise, observe a inclusão do suplemento Solver. Exemplo O suplemento Solver pode ser aplicado no caso a seguir. João estuda informática básica em uma escola profissionalizante, onde tem as seguintes disciplinas: Terminologia, Windows, Word, Excel, PowerPoint e Internet. A escola aplica uma atividade e duas provas para cada disciplina. A média final de cada disciplina é calculada considerando a nota da atividade e a média aritmética das notas das provas. Por exemplo: Atividade: 7,0 Prova 1: 6,0 Prova 2: 6,0
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 106
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
107
Média das provas: (6,0+6,0)/2 = 6,0 Média final: (7,0+6,0)/2 = 6,5 A média para ser aprovado em cada disciplina é no mínimo 7,0; porém, caso não atinja a média 7,0, João poderá realizar um exame para tentar aprovação, que resultará em uma média definitiva. O cálculo da média definitiva tem a seguinte fórmula: MD = (MF+EXAME)/2 MD é a média definitiva e MF é a média final calculada antes do exame. Pode-se aplicar essa fórmula no exemplo a seguir: Média final: 6,5 Exame: 7,5 Média definitiva: (6,5+7,5)/2 = 7,0 Ao pegar o resultado parcial de seu boletim, João constatou que precisará realizar três exames, como mostra a Figura 40.
Figura 40 – Boletim do João.
Para saber quanto João precisará tirar nos exames para conseguir a média 7,0 necessária para aprovação, é possível utilizar o suplemento Solver. Para isso, será preciso criar a tabela na planilha Excel, aplicando fórmulas para cálculos das médias. Para um melhor entendimento, é aconselhável copiar a tabela da Figura 41 inserindo as fórmulas e funções conforme é demonstrado.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 107
26/11/15 16:17
108
ANÁLISE DE DADOS
Figura 41 – Aplicação da função MÉDIA no cálculo da média das provas e da média final.
Figura 42 – Aplicação da função SE para o cálculo da nota necessária.
Depois de criar a tabela no Excel, já é possível aplicar o suplemento Solver para análise. Deve-se clicar na guia Dados e, no grupo Análise, clicar no suplemento Solver. Será aberta a janela Parâmetros do Solver, cujas opções são descritas a seguir.
Figura 43 – Janela Parâmetros do Solver.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 108
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
109
1. Definir Objetivo – informa-se uma referência ou um nome de uma célula que deve ser única e deve conter uma fórmula já inserida na célula. A célula de objetivo no exemplo será a que contém o cálculo da média total, ou seja, a célula $B$9. 2. Para: Máx.; Mín.; Valor de – define-se como se deseja que seja o valor da célula de objetivo: se ele será o maior valor possível (Máx.), o menor valor possível (Mín.) ou um valor predeterminado (Valor de). Caso seja marcada a última opção, deve-se informar o valor desejado. No exemplo, como se quer saber a nota mínima que João precisa tirar para ser aprovado, deve-se marcar a opção Mín.. 3. Alterando Células Variáveis – insere-se um intervalo de referência de células que vão receber as variáveis de decisão. Esse intervalo deve estar relacionado direta ou indiretamente à célula de objetivo. É possível especificar até 200 células variáveis. No exemplo, as células variáveis serão as do campo “Exame”, que se refere à nota que João deverá tirar para atingir a média mínima (Célula de Objetivo). No exemplo, deve-se informar o intervalo $G$2:$G$7. 4. Sujeito às Restrições – inserem-se as restrições, caso haja, na análise. O botão Adicionar inclui uma nova restrição. O botão Alterar permite modificar uma restrição existente; para isso, é necessário selecionar a restrição que deverá ser modificada antes de clicar no botão. O botão Excluir permite deletar uma restrição existente; para isso, é necessário selecionar a restrição que se deseja excluir antes de clicar no botão. No caso do problema de João existem restrições: a média e as notas não podem ser menores do que 7,0 para aprovação nem maiores do que 10,0, já que a nota 10,0 é a nota máxima. Assim, existem as seguintes restrições: a. A média definitiva deve ser menor ou igual a 10. b. A média definitiva para aprovação deve ser maior ou igual a 7,0. c. A nota do exame deve ser menor ou igual a 10.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 109
26/11/15 16:17
110
ANÁLISE DE DADOS
Para incluir a primeira restrição, deve-se clicar no botão Adicionar, selecionar o intervalo de células que contém a média definitiva e informar que os valores deverão ser menores ou iguais a 10. Por fim, deve-se clicar no botão Adicionar. A Figura 44 exemplifica esses procedimentos.
Figura 44 – Inserção da primeira restrição na janela Adicionar Restrição.
Deve-se inserir a segunda restrição e clicar no botão Adicionar.
Figura 45 – Inserção da segunda restrição na janela Adicionar Restrição.
Deve-se inserir a terceira restrição e clicar no botão OK para finalizar.
Excel avançado 2013_miolo.indd 110
03/10/17 09:03
MICROSOFT OFFICE EXCEL 2013
111
Figura 46 – Inserção da terceira restrição na janela Adicionar Restrição.
• GRG Não Linear – o Gradiente Reduzido Generalizado (GRG) é usado em problemas simples e não lineares. Quando essa opção é usada, o suplemento Solver procura por soluções locais. • LP Simplex – usado para problemas lineares. Quando essa opção é usada, o suplemento Solver procura primeiramente por soluções globais que atendam a todas as células. • Evolutionary – “evolucionário”‚ em inglês, é usado em problemas mais complexos. Quando essa opção é usada, o suplemento Solver calcula todas as varáveis possíveis, locais e globais, que possam atender às definições da análise. Normalmente essa opção é mais demorada. No exemplo, será usado o método GRG Não Linear, pois se pretende encontrar a nota mínima de cada exame (solução local). A configuração final da janela Parâmetros do Solver aplicada ao exemplo é mostrada na Figura 47.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 111
26/11/15 16:17
112
ANÁLISE DE DADOS
Figura 47 – Configuração final aplicada ao exemplo na janela Parâmetros do Solver.
Agora, basta clicar no botão Resolver, e os valores serão calculados e colocados automaticamente na planilha.
Figura 48 – Planilha atualizada com as notas necessárias de João.
As notas do campo “Exame” foram preenchidas pelo suplemento Solver, que já calculou qual é a nota mínima em cada disciplina que João precisará tirar para obter no mínimo 7,0 para aprovação.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 112
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
113
Ao clicar no botão Resolver, a janela Resultados do Solver será aberta. É possível escolher entre as opções Manter Solução do Solver, que mantém os valores calculados pelo Solver, ou Restaurar Valores Originais, que descarta os valores calculados pelo Solver. Após escolher o desejado, deve-se clicar no botão OK.
Figura 49 – Janela Resultados do Solver.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 113
26/11/15 16:17
114
ANÁLISE DE DADOS
Exercícios 1. O que são cenários e qual é o caminho para acessar a ferramenta
Cenário? 2. Em Auditoria de Fórmulas, qual é a diferença entre Rastrear Preceden-
tes e Rastrear Dependentes? 3. Qual é o procedimento para importar um arquivo de texto para o Ex-
cel? Explique detalhadamente. 4. Copie a tabela a seguir para o Excel e, utilizando a ferramenta Solver,
produza a carga horária por dia da semana para cada professor com base nas restrições informadas.
Figura 50 – Carga horária de trabalho de professores.
Alice só pode trabalhar 26 horas por semana. Joarez só pode trabalhar 22 horas por semana. Alessandro pode trabalhar 40 horas por semana. Marília só pode trabalhar 30 horas por semana. Alice não pode trabalhar na segunda-feira. Joarez e Marília só podem trabalhar 2 horas na quinta-feira.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 114
26/11/15 16:17
5. Introdução a macros O que são macros? Tipos de macros Criação de macros no Excel O ambiente VBA Criação de uma agenda telefônica Técnicas básicas de programação
O que são macros? O nome macro é uma abreviação de macroinstrução, que consiste em um padrão ou regra que define uma sequência de comandos ou um conjunto de instruções que devem ser executados quando forem solicitados. Uma macro pode ser armazenada em discos ou em memória e só é executada quando for solicitada pelo usuário ou por uma ação definida na macro. Por exemplo, o atalho Ctrl+C utilizado no teclado para copiar dados é uma macro de teclado. Ao pressionar o atalho, a macro é solicitada e executada. Nessa macro, uma cópia do item selecionado é realizada e a macro é finalizada. O mesmo ocorre com os outros atalhos de teclado: Ctrl+X, Ctrl+V, Ctrl+A, Crtl+B etc. Em resumo, uma macro é um conjunto de instruções ou sequência de comandos que serve para automatizar tarefas repetitivas, ficando gravada em disco ou na memória. Ela só é executada após solicitação do usuário ou depois de uma ação de um programa.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 115
26/11/15 16:17
116
INTRODUÇÃO A MACROS
Tipos de macros As macros podem ser de três tipos: • Macros de Comandos – os procedimentos ocorrem somente no local selecionado em uma pasta de trabalho. Não requerem muito conhecimento em programação, tanto que são o modelo mais utilizado pelos usuários de informática. • Macros de Funções Definidas pelo Usuário – o próprio usuário é quem cria suas funções. As funções têm a finalidade de retornar um valor e, ao contrário das Macros de Comandos, as Macros de Funções Definidas pelo Usuário ocorrem em qualquer local da pasta de trabalho. Requerem uma noção básica de programação. • Macros de Subrotinas – são macros criadas que podem ser chamadas de dentro de outras macros e deixam os códigos de programação mais leves. Esse tipo de procedimento pode ocorrer em locais pré-selecionados e também retornar valores em locais diversos. Requerem um conhecimento mais profundo de programação.
Criação de macros no Excel Antes de criar uma macro, é necessário ativar a guia Desenvolvedor no Excel. 1. Clique na guia Arquivo. 2. Clique em Opções. 3. Será aberta a janela Opções do Excel e, no menu lateral esquerdo, clique em Personalizar Faixa de Opções. 4. Marque a caixa de seleção Desenvolvedor. 5. Clique no botão OK. A guia Desenvolvedor será adicionada ao lado da guia Exibição.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 116
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
117
Figura 1 – Guia Desenvolvedor.
Uma das formas mais simples de se criar uma macro é inserir um botão de comando e atribuir a ele uma macro; assim toda vez que o botão for pressionado a macro será executada.
Criação da primeira macro Para criar uma macro no Excel, devem ser realizados os seguintes procedimentos: 1. Clique na guia Desenvolvedor. 2. No grupo Controles, clique em Inserir. 3. No menu suspenso, em Controles de Formulários, clique na primeira opção Botão. 4. Com o auxílio do mouse, pressione o botão esquerdo e arraste o mouse para criar o botão, definindo o tamanho desejado. Ao terminar, solte o botão do mouse. O processo é o mesmo do desenho de formas (retângulos, quadrados, triângulos etc.). 5. Será aberta a janela Atribuir Macro, clique no botão Gravar.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 117
26/11/15 16:17
118
INTRODUÇÃO A MACROS
Figura 2 – Janela Atribuir macro.
6. Na janela Gravar Macro, clique no botão OK novamente.
Figura 3 – Janela Gravar macro.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 118
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
119
7. A partir desse momento, toda ação que for realizada será gravada na macro. Clique na célula A1 e digite o seguinte texto: “Minha Primeira Macro”. Pressione a tecla Enter. 8. Na guia Desenvolvedor, grupo Código, clique em Parar gravação. 9. Para conferir sua execução, apague o conteúdo digitado na célula A1 e clique no botão ao qual teve sua macro atribuída. Observe que o texto digitado vai aparecer na célula A1.
Figura 4 – Aplicação de macro na célula A1.
Até o momento foi criada uma macro de comando sem a necessidade de se conhecer programação. Será criado agora um botão que contenha uma macro que apague o conteúdo da célula A1. Assim não será preciso selecionar a célula A1 e clicar na tecla Delete para limpar seu conteúdo, pois essa rotina já estará gravada na macro. 1. Repita os procedimentos de 1 a 6 para a criação da macro. 2. No procedimento 7, faça a seguinte operação: selecione a célula A1 e pressione a tecla Delete ou Del do teclado. 3. Repita o procedimento 8 para parar a gravação da macro. 4. Para ver o resultado, clique no primeiro botão criado, e o texto aparecerá na célula A1. Depois, clique no segundo botão criado, e o texto da célula A1 será excluído.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 119
26/11/15 16:17
120
INTRODUÇÃO A MACROS
Figura 5 – Aplicação da segunda macro criada.
Exemplo A seguir é apresentado um exemplo de aplicação prática de macros. Em uma empresa, um funcionário lança suas despesas para prestação de contas em um relatório de despesas. Toda vez que precisa lançar dados para novas prestações, é necessário selecionar as células e apagar os dados antigos para inserir os novos dados. Entretanto, se for criada uma macro, ao pressionar um botão todas as células vão ficar vazias instantaneamente para receber novas informações. A Figura 6 mostra um dos modelos de relatório de despesas disponíveis na galeria de modelos do Excel 2013. As informações são fictícias.
Figura 6 – Modelo de relatório de despesas do Microsoft Office Excel.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 120
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
121
As colunas “Data”, “Conta”, “Descrição”, “Hotel”, “Transporte”, “Refeições”, “Telefone”, “Diversos”, “Início” e “Término” precisam estar vazias para receberem os novos dados. Será criado, então, um botão limpar que, ao ser pressionado, excluirá as informações contidas em todas as células das colunas mencionadas. Criação do botão Limpar
Para criar o botão Limpar, devem ser realizados os seguintes procedimentos: 1. Na guia Desenvolvedor, no grupo Controles, clique em Inserir. 2. Clique no Botão (Controle de Formulário). 3. Desenhe o botão no local desejado do relatório. 4. Na caixa de diálogo Gravar macro, clique no botão Cancelar.
Figura 7 – Criação do botão Limpar.
5. Clique com o botão direito do mouse sobre o botão criado. 6. Escolha a opção Editar Texto.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 121
26/11/15 16:17
122
INTRODUÇÃO A MACROS
Figura 8 – Opção Editar Texto do botão criado.
7. Altere o nome do botão para Limpar. 8. Pressione a tecla Esc. Criação da macro do botão Limpar
Para criar a macro do botão Limpar, devem ser realizados os seguintes procedimentos: 1. Clique com o botão direito do mouse sobre o botão Limpar. 2. Escolha a opção Atribuir macro. 3. Em Nome da macro, digite “Bt_Limpar”. 4. Clique no botão Gravar.
Figura 9 – Criação da macro “Bt_Limpar” na janela Atribuir macro.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 122
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
123
5. Na caixa de diálogo Gravar macro, clique no botão OK.
Figura 10 – Botão OK da janela Gravar macro.
6. Selecione as células que devem ser apagadas. 7. Pressione a tecla Delete ou Del.
Figura 11 – Seleção das células que devem ser apagadas.
8. Na guia Desenvolvedor, grupo Código, clique em Parar gravação. Agora toda vez que for preciso apagar as informações inseridas no
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 123
26/11/15 16:17
124
INTRODUÇÃO A MACROS
relatório, basta clicar no botão Limpar para que todas as informações do relatório sejam excluídas. 9. Para testar, copie o relatório no Excel, execute os procedimentos para criação do botão e da macro, insira novas informações no relatório e execute o botão Limpar. Se algo der errado, reveja todos os procedimentos para ver se algum deles não foi esquecido.
O ambiente VBA A macro registra as ações como uma rotina, uma sequência de comandos, e as armazena em um local para que sejam usadas posteriormente em ações repetitivas, poupando a repetição manual feita pelo usuário do computador das mesmas ações inúmeras vezes. Em alguns casos, são necessários pequenos ajustes para que o resultado da operação seja satisfatório. Nesse caso, é preciso trabalhar diretamente com o VBA do Excel. Sigla de Visual Basic for Applications, o VBA é uma linguagem voltada à programação visual de aplicativos. Apesar de ser mais conhecido por causa da programação dos objetos do Excel, o VBA também é encontrado em programas como o Access e o Word, também pertencentes à Microsoft. Há duas maneiras de acessar o ambiente VBA: por meio da guia Desenvolvedor, grupo Código, Visual Basic; ou pelo atalho do teclado Alt+F11.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 124
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
125
Figura 12 – Microsoft Visual Basic for Applications.
Comandos básicos VBA A seguir são apresentados alguns dos comandos mais utilizados no VBA. • Range – usado para referenciar uma célula do Excel. Exemplo: Range(“B2”). Value=5. • Cells – também usado para referenciar uma célula do Excel, utiliza, porém, como parâmetros a coluna e a linha que identificam o posicionamento da célula na planilha Excel. Exemplo: Cells(2,2).Value=8. • Value – usado para ações, indica que um dado será atribuído à referência mencionada. Exemplo: Range(“D10”).Value=23.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 125
26/11/15 16:17
126
INTRODUÇÃO A MACROS
• Worksheets – usado para referenciar uma planilha do Excel. Exemplo: Worksheets(“Plan2”).Select. • Select – também usado para ações, seleciona a referência indicada. Exemplo: Range(“F3”).Select. • MsgBox – usado para exibir uma mensagem ao usuário do Excel. Exemplo: MsgBox “Seja bem-vindo!”. Exemplo MsgBox
Para utilizar o comando MsgBox, devem ser realizados os seguintes procedimentos: 1. Abra uma pasta de trabalho em branco do Excel. 2. Na guia Desenvolvedor, grupo Código, clique em Visual Basic (ou utilize o atalho do teclado Alt+F11). 3. No menu lateral esquerdo, dê um duplo clique na opção Plan 1(Plan1) e, nos ícones de seleção das caixas de texto, altere para Worksheet e Change, conforme demonstrado na Figura 13.
Figura 13 – Alteração para Worksheet e Change.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 126
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
127
4. Inclua o comando: MsgBox “Você alterou a células” & Target.Address & “.”. O código deverá ficar conforme a Figura 14.
Figura 14 – Código do comando MsgBox.
No código apresentado na Figura 14, são identificados: • • • •
• •
Private Sub – indica o início de um procedimento que é acessível somente a partir de seu contexto de declaração. Worksheet_Change – corresponde à denominação do comando. (ByVal Target As Range) – corresponde aos parâmetros de entrada do evento. MsgBox – corresponde ao comando responsável por ativar a caixa de mensagem, seguido do comando, entre aspas duplas (“) deve conter o texto que se deseja exibir. O sinal do e comercial (&) é utilizado para concatenar informações distintas, no exemplo o texto “Você alterou a célula” será concatenado (unido) com o endereço da célula modificada. Target.Address – retorna o endereço da célula modificada. End Sub – indica o fim de um procedimento.
5. Clique no botão fechar da janela do VBA.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 127
26/11/15 16:17
128
INTRODUÇÃO A MACROS
6. Agora, toda vez que for realizada qualquer alteração em qualquer célula da planilha, a mensagem da Figura 15 será exibida.
Figura 15 – Janela com exibição da mensagem criada pelo comando MsgBox.
Comandos Range e Worksheets
Para utilizar os comandos Range e Worksheets, devem ser realizados os seguintes procedimentos: 1. Abra uma nova pasta de trabalho em branco do Excel. 2. Inclua uma nova planilha (Plan2) na pasta – caso a pasta de trabalho seja criada apenas com uma planilha (Plan1).
Figura 16 – Inclusão de nova planilha.
3. Abra o ambiente VBA. 4. Clique na guia Inserir e escolha a opção Módulo para inserir um módulo de comando. 5. Digite:
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 128
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
129
Sub Macro1() Worksheets(“Plan2”).Select Range(“D7”).Value = 5 Range(“C4”).Select End Sub O cógido deverá ficar como aparece na Figura 17.
Figura 17 – Código dos comandos Range e Worksheets.
No código apresentado na Figura 17, são identificados: • • • • •
Sub Macro1() – Sub indica o início do procedimento. É seguido do nome criado para a macro: Macro1(). Worksheets(“Plan2”).Select – seleciona a planilha 2 (Plan2). Range(“D7”).Value = 5 – insere o valor 5 na célula D7 da planilha 2. Range(“C4”).Select – deixa a célula C4 da planilha 2 ativa. End Sub – indica o fim do procedimento.
6. Feche o ambiente VBA. Agora teste a macro criada.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 129
26/11/15 16:17
130
INTRODUÇÃO A MACROS
1. Deixe a planilha 1 do Excel ativa. 2. Na guia Desenvolvedor, grupo Código, clique em Macros. 3. Ao abrir a janela Macro, selecione a macro desejada (Macro1) e clique em Executar.
Figura 18 – Seleção da macro desejada na janela Macro.
4. A macro seguirá exatamente os passos informados no procedimento. Ela vai ativar a Plan2, inserir o valor 5 na célula D7 e manter selecionada a célula C4.
Figura 19 – Aplicação da macro na planilha 2.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 130
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
131
Cells e Worksheets
Considerando ainda o exemplo anterior, substitui-se agora o comando Range pelo comando Cells. Mas, primeiramente, será inserido um novo módulo para facilitar o entendimento. 1. Clique na guia Inserir e escolha a opção Módulo para inserir um módulo de comando. 2. Digite: Sub Macro2() Worksheets(“Plan2”).Select Cells(7, 4).Value = 5 Cells(4, 3).Select End Sub O código deverá ficar como mostra a Figura 20.
Figura 20 – Código dos comandos Worksheets e Cells.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 131
26/11/15 16:17
132
INTRODUÇÃO A MACROS
No código apresentado na Figura 20, são identificados: • • • • •
Sub Macro2() – Sub indica o início do procedimento. É seguido do nome criado para a macro: Macro2(). Worksheets(“Plan2”).Select – seleciona a planilha 2 (Plan2). Cells(7, 4).Value = 5 – insere o valor 5 na célula D7 da planilha 2. Cells(4, 3).Select – deixa a célula C4 da planilha 2 ativa. End Sub – indica o fim do procedimento.
Ao utilizar o comando Cells, é necessário informar as coordenadas da localização da célula, e não seu endereço como no comando Range. A sintaxe do comando Cells é =Cells(Linha;Coluna). Ao utilizar o comando Cells para selecionar a células C4, foram usados os seguintes parâmetros (4,3). O 4 refere-se à quarta linha da planilha e 3 à terceira coluna da planilha, o que resulta na célula C4. Para testar a nova macro criada: 1. Deixe a planilha 1 do Excel ativa. 2. Na guia Desenvolvedor, grupo Código, clique em Macros. 3. Ao abrir a caixa de diálogo Macro, selecione a macro desejada (Macro1) e clique em Executar.
Figura 21 – Seleção da macrodesejada na janela Macro.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 132
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
133
4. A macro seguirá exatamente os passos informados no procedimento. Ela vai ativar a Plan2, inserir o valor 5 na célula D7 e manter selecionada a célula C4, exatamente como no exemplo anterior, porém com o comando Cells.
Procedimentos para criação de um jogo da velha no Excel com macro 1. Abra uma nova planilha no Excel. 2. Monte uma tabela igual à da Figura 22.
Figura 22 – Tabela de jogo da velha.
3. Crie três botões conforme a Figura 23. a. Ao surgir a janela Atribuir Macro, clique no botão OK (a macro será gravada posteriormente). b. Clique com o botão direito do mouse sobre um dos botões. c. Escolha a opção Editar Texto. d. Renomeie o botão conforme a Figura 23 (X ou O). e. Repita o procedimento para renomear os outros botões.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 133
26/11/15 16:17
134
INTRODUÇÃO A MACROS
Figura 23 – Botões criados para jogo da velha.
Macro Reiniciar Para criar a macro Reiniciar, devem ser realizados os seguintes procedimentos: 1. Clique com o botão direito do mouse sobre o botão Reiniciar. 2. Escolha a opção Atribuir Macro. 3. Altere o Nome da Macro para “Reiniciar”. 4. Clique no botão Gravar.
Figura 24 – Criação da macro Reiniciar na janela Atribuir macro.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 134
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
135
5. Na janela Gravar Macro, clique no botão OK. 6. Selecione o intervalo de células destinado ao jogo. 7. Pressione a tecla Delete ou Del no teclado.
Figura 25 – Seleção do intervalo de células destinado ao jogo da velha.
8. Selecione a célula central do jogo (F6). 9. Na guia Desenvolvedor, grupo Código, clique em Parar gravação.
Macro X Para criar a macro X, devem ser realizados os seguintes procedimentos: 1. Clique com o botão direito do mouse no botão X. 2. Escolha a opção Atribuir Macro. 3. Altere o Nome da Macro para “Botao_X”. 4. Clique no botão Gravar. 5. Selecione a célula E5. 6. Digite a letra X maiúscula. 7. Tecle Enter. 8. Na guia Desenvolvedor, grupo Código, clique em Parar gravação.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 135
26/11/15 16:17
136
INTRODUÇÃO A MACROS
Macro O Para criar a macro O, devem ser realizados os seguintes procedimentos: 1. Clique com o botão direito do mouse no botão O. 2. Escolha a opção Atribuir Macro. 3. Altere o Nome da Macro para “Botao_O”. 4. Clique no botão Gravar. 5. Selecione a célula E5. 6. Digite a letra O maiúscula. 7. Tecle Enter. O botão Reiniciar funciona corretamente. Sua função é selecionar o intervalo de células do jogo, excluir seu conteúdo e deixar a célula central do jogo selecionada. Os botões X e O precisam de ajustes, pois a macro criada insere X ou O apenas na célula E5, que foi a célula selecionada no momento da gravação da macro. É necessário lembrar que, quando se grava uma macro, todos os procedimentos efetuados são gravados como sequência de comandos. Os procedimentos efetuados do Botão_X são: 1. Selecionar a célula E5. 2. Inserir a letra X. 3. Pressionar a tecla Enter (após pressionar Enter a célula E6 será selecionada). Observando a sequência de comandos anterior será necessário excluir a sequência 1 e 3, já que não se quer que a macro selecione uma célula para inserir o valor, pois será o usuário quem fará essa ação, e também não se quer que a macro selecione outra célula após inserir o valor, pois ela deve permanecer na célula selecionada pelo usuário. Para efetuar essa alteração, devem ser realizados os seguintes procedimentos: 1. Abra o ambiente VBA.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 136
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
137
2. Dê um duplo clique na opção Módulo 1, que contém todos os procedimentos dos botões.
Figura 26 – Código dos botões do jogo da velha.
3. No procedimento Sub Botao_X(), exclua as linhas Range(“E5”).Select e Range(“E6”).Select. 4. No procedimento Sub Botao_O(), exclua as linhas Range(“E5”).Select e Range(“E6”).Select. 5. Observe os procedimentos após as alterações.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 137
26/11/15 16:17
138
INTRODUÇÃO A MACROS
Figura 27 – Exclusão de linhas do código.
Com essas alterações as letras X e O serão inseridas apenas nas células selecionadas pelo usuário. O procedimento apresenta um novo comando, o ActiveCell.FormulaR1C1. Esse comando se refere à célula ativa no momento de sua execução, ou seja, a célula que estiver selecionada será a célula que receberá os valores X ou O quando os botões forem acionados para executar as macros criadas. 6. Feche o ambiente VBA e realize o teste no jogo criado.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 138
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
139
Figura 28 – Jogo da velha criado.
Criação de uma agenda telefônica Para criar uma agenda telefônica, devem ser realizados os seguintes procedimentos: 1. Abra o Excel. 2. Renomeie a Plan1 para “Consulta”. 3. Renomeie a Plan2 para “BaseDados”. 4. Monte a tabela da Figura 29 na planilha “BaseDados” e deixe a célula A2 selecionada após a criação da tabela.
Figura 29 – Célula A2 selecionada após a criação da tabela.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 139
26/11/15 16:17
140
INTRODUÇÃO A MACROS
5. Na planilha “Consulta”, monte a tabela de consulta conforme demonstrado na Figura 30. Para formatação, utilize as ferramentas básicas de formatação do Excel. Para inserir os botões, utilize o Botão de Controle de Formulários, localizado na guia Desenvolvedor, no grupo Controles, na opção Inserir.
Figura 30 – Tabela de consulta da agenda de contatos.
Macro do botão Cadastrar Para criar a macro do botão Cadastrar, devem ser realizados os seguintes procedimentos: 1. Clique com o botão direito do mouse sobre o botão Cadastrar. 2. Escolha a opção Atribuir macro.
Figura 31 – Opção Atribuir macro do botão Cadastrar.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 140
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
141
3. Altere o Nome da Macro para “Cadastrar” e clique no botão Gravar.
Figura 32 – Alteração do nome da macro para “Cadastrar” na janela Atribuir macro.
4. Na janela Gravar macro, clique no botão OK.
Figura 33 – Botão OK da janela Gravar macro.
5. Lembre-se que nesse momento toda ação que for realizada nas planilhas será gravada e armazenada quando a macro for acionada. Repita os procedimentos conforme demonstrado a seguir. a. Selecione todas as células que receberão os dados para cadastro e utilize a tecla Ctrl para a seleção simultânea das células.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 141
26/11/15 16:17
142
INTRODUÇÃO A MACROS
Figura 34 – Seleção das células que receberão dados do cadastro.
b. Clique na planilha “BaseDados” (observe que a célula A2 já se encontra selecionada, conforme foi orientado no procedimento 4 de construção da tabela na planilha BaseDados). c. Na guia Página Inicial, grupo Área de Transferência, clique na seta da ferramenta Colar e escolha a opção Colar Especial. d. Na janela Colar Especial, deixe marcadas as opções Valores (serão copiados apenas os valores contidos nas células, e não as fórmulas/funções caso existam), Nenhuma (nenhuma operação será adicionada na cópia) e Transpor (altera o sentido da área selecionada, por exemplo, se foi copiada de uma área no sentido vertical, será colada no sentido horizontal, e vice-versa). Clique no botão OK.
Figura 35 – Seleção das opções Valores, Nenhuma e Transpor na janela Colar especial.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 142
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
143
e. Selecione a célula A3.
Figura 36 – Célula A3 selecionada.
f. Clique na planilha “Consulta”. g. Observe que as células que foram copiadas estarão selecionadas, pressione a tecla Delete ou Del para apagar as informações. h. Selecione a célula D5 (célula que recebe os dados do campo “Nome”). 6. Na guia Desenvolvedor, grupo Código, clique em Parar gravação. Agora será necessário ajustar o código da macro Cadastrar, pois da forma com que foi feita ela cadastrará as informações sempre na linha 2 da planilha “BaseDados”. O ideal é que, sempre que a macro Cadastrar for executada, os dados sejam colados na linha seguinte à última informação preenchida. Para isso, é preciso realizar uma pequena alteração no código da macro Cadastrar. 1. Na planilha “BaseDados”, clique na última célula da coluna A (deverá ser a célula A1048576) e a renomeie para “FIM”. 2. Na guia Desenvolvedor, grupo Código, clique em Macros. 3. Na janela Macro, escolha a macro Cadastrar e clique em Editar.
Figura 37 – Escolha da macro Cadastrar na janela Macro.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 143
26/11/15 16:17
144
INTRODUÇÃO A MACROS
4. Se todos os passos para a criação da macro Cadastrar foram seguidos corretamente, o código da macro deverá estar da seguinte forma: Sub Cadastrar() ‘ ‘ Cadastrar Macro ‘ ‘ Range(“D5,D7,D9,D11,D13”).Select Range(“D13”).Activate Selection.Copy Sheets(“BaseDados”).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True Range(“A3”).Select Sheets(“Consulta”).Select Application.CutCopyMode = False Selection.ClearContents Range(“D5”).Select End Sub 5. Realize a seguinte alteração no código: Sub Cadastrar() ‘ ‘ Cadastrar Macro ‘ ‘ Range(“D5,D7,D9,D11,D13”).Select Range(“D13”).Activate Selection.Copy Sheets(“BaseDados”).Select Range(“FIM”).Select Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Select
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 144
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
145
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True Range(“A1”).Select Sheets(“Consulta”).Select Application.CutCopyMode = False Selection.ClearContents Range(“D5”).Select End Sub 6. Feche o ambiente VBA. Agora, sempre que se cadastrar um novo registro dos dados, eles serão armazenados na próxima linha em branco, independentemente da célula ativa na planilha “BaseDados”. Observação No exemplo anterior, mais um novo comando surgiu, o Selection.End(xlUp).Select. Esse comando tem a função de selecionar a primeira célula que contém valores acima da célula ativa. Também é possível selecionar a primeira célula que contém valores abaixo da célula ativa; nesse caso, o comando é Selection.End(xlDown).Select.
Macro do botão Consultar Para criar a macro do botão Consultar, devem ser realizados os seguintes procedimentos: 1. Clique com o botão direito do mouse sobre o botão Consultar. 2. Escolha a opção Atribuir Macro.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 145
26/11/15 16:17
146
INTRODUÇÃO A MACROS
Figura 38 – Opção Atribuir macro do botão Consultar.
3. Altere o Nome da Macro para “Consultar” e clique no botão Gravar.
Figura 39 – Alteração do nome da macro para “Consultar” na janela Atribuir macro.
4. Na janela Gravar macro, clique no botão OK.
Figura 40 – Botão OK da janela Gravar macro.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 146
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
147
5. Insira as funções conforme demonstrado na planilha “Consulta” da Figura 41.
Figura 41 – Planilha “Consulta”.
Observação A função SE estabelece uma condição que retorna um valor vazio (“”) se a função PROCV resultar em um valor de erro; caso contrário, o retorno será o valor encontrado pela função PROCV. A função ÉERROS testa a função PROCV dentro da função SE na intenção de encontrar um valor de erro. A função PROCV verifica o valor digitado na célula D5 e realiza a busca na guia “BaseDados”, no intervalo de células de A2 até E1000, e retorna o valor referente ao campo (coluna) informado (2,3,4,5) caso encontre o valor procurado, senão retornará um valor de erro. Para mais informações sobre essas funções, ver o Capítulo 1. 6. Após digitar as funções anteriores, selecione o intervalo de células de D5 até D13 e copie (Ctrl+C) seus valores.
Figura 42 – Seleção de intervalo de células na planilha “Consulta”.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 147
26/11/15 16:17
148
INTRODUÇÃO A MACROS
7. Na guia Página Inicial, grupo Área de Transferência, clique na seta da ferramenta Colar e escolha a opção Colar Especial. 8. Na janela Colar Especial, deixe marcadas somente as opções Valores, Nenhuma e Ignorar em branco. Clique no botão OK.
Figura 43 – Opções Valores, Nenhuma e Ignorar em branco marcadas na janela Colar especial.
9. Selecione a célula D5 e pressione a tecla Esc. 10. Na guia Desenvolvedor, grupo Código, clique em Parar gravação.
Macro do botão Limpar Para criar a macro do botão Limpar, devem ser realizados os seguintes procedimentos: 1. Clique com o botão direito do mouse sobre o botão Limpar. 2. Escolha a opção Atribuir macro.
Figura 44 - Opção Atribuir macro no botão Limpar.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 148
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
149
3. Altere o Nome da macro para “Limpar” e clique no botão Gravar.
Figura 45 – Alteração do nome da macro para “Limpar” na janela Atribuir macro.
4. Na janela Gravar macro, clique no botão OK. 5. Na planilha “Consulta”, selecione o intervalo de células de D5 até D13.
Figura 46 – Seleção do intervalo de células na planilha “Consulta”.
6. Pressione a tecla Delete ou Del. 7. Selecione a célula D5. 8. Na guia Desenvolvedor, grupo Código, clique em Parar gravação.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 149
26/11/15 16:17
150
INTRODUÇÃO A MACROS
Técnicas básicas de programação Para uma boa técnica de programação, é necessária uma boa lógica de programação. Segundo o verbete do Dicionário eletrônico Houaiss da língua portuguesa (2009), a lógica “trata das formas do pensamento em geral (dedução, indução, hipótese, inferência etc.) e das operações intelectuais que visam à determinação do que é verdadeiro ou não”. Para entender o conceito, pode-se ler o seguinte: João é pai de Luís. Luís é filho de Maria. Com base no texto acima, pode-se dizer o que João e Maria são? Se a primeira resposta a surgir for “casados”, ela estaria dentro do pensamento lógico natural da maioria das pessoas, pois é um resultado comum. Entretanto, desenvolver o raciocínio lógico consiste em ir além, em enxergar outros caminhos nem tão visíveis nas situações. Por exemplo, João e Maria podem não ser casados, podem ter se separado ou podem nunca ter se casado. Se as informações forem analisadas com cuidado, pode-se ainda supor que João e Maria nem se conheçam. Após a frase “João é pai de Luís”, existe um ponto-final que indica o fim da sentença. E uma nova sentença se inicia em outro parágrafo. Pode-se também concluir que não há qualquer relação de João com Maria, e o nome dos filhos (Luís) é apenas uma coincidência. Na programação, o raciocínio lógico é fundamental para evitar problemas futuros no código de programação. O raciocínio lógico de programação consiste em prever todas as hipóteses que podem ocorrer e apresentar soluções para todas elas. Para ajudar nesse processo, antes de iniciar a construção de uma rotina, pode-se usar o algoritmo. Um algoritmo consiste em realizar, de forma genérica, os procedimentos necessários para o alcance do objetivo. É como uma receita de bolo em que se tem os ingredientes e depois o passo a passo de como preparar. Esse “como preparar” pode ser considerado o algoritmo da receita.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 150
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
151
Exercício 1. Dados os ingredientes a seguir, descreva a sequência de como preparar
um café no Excel. Ingredientes 4 colheres de pó de café 1 litro de água 1 recipiente para ferver água 1 coador de papel 1 suporte para coador de papel 1 garrafa de café de 750 mL 1 fogão 1 acendedor automático
Exemplos Um algoritmo é a descrição sequencial para alcançar um objetivo. Sua estrutura deve estar clara e bem identificada. Criar, por exemplo, o algoritmo de um programa que leia três valores, realize a soma desses valores e apresente o resultado da soma não é nada fácil, embora pareça simples. Essa é uma das principais dificuldades da maioria das pessoas. Saber organizar o pensamento logicamente é um grande desafio. Ainda mais quando existem diferentes maneiras e pontos a serem considerados: 1. Ler os valores das variáveis A, B e C. 2. Efetuar a soma das variáveis A, B e C e atribuir o resultado na variável X. 3. Apresentar o valor da variável X.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 151
26/11/15 16:17
152
INTRODUÇÃO A MACROS
Da mesma maneira, existem várias formas de preparar um café, porém todas seguem uma lógica comum. Uma dessas formas poderia ser a seguinte: 1. Levar 1 litro de água ao fogo, em um recipiente próprio. 2. Pegar o acendedor automático, abrir o botão de gás do fogão e apertar o acendedor próximo à boca do fogão para acender o fogo. 3. Abrir a garrafa térmica. 4. Colocar o coador de papel no suporte para coador de papel. 5. Encaixar o suporte de coador de papel na boca da garrafa térmica. 6. Adicionar 4 colheres de pó de café no coador de papel. 7. Quando a água estiver quase levantando fervura, apagar o fogo e despejar lentamente a água dentro do coador de papel. 8. Assim que atingir o limite da garrafa térmica, retirar com cuidado o suporte de coador de papel. 9. Descartar o coador de papel. 10. Fechar a garrafa térmica. É importante ressaltar que não existe uma forma correta de se montar um algoritmo, mas a forma que deixará um programa mais leve e funcional. Após elaborar o algoritmo podemos montar o diagrama de algoritmo. Ele permite a demonstração, de maneira visual, de como o programa se comportará. O diagrama de algoritmo do programa que deverá somar três valores ficaria assim:
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 152
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
153
Início
Ler A, B e C
X←A+ B + C
Escrever X
Fim Figura 47 – Diagrama de algoritmo.
Para a representação gráfica, é necessário seguir corretamente sua simbologia. Os símbolos básicos mais utilizados são:
• Terminal – usado para indicar o início e o fim do fluxo do programa.
• Seta de fluxo de dados – usada para indicar o sentido do fluxo de dados.
• Processamento – usado para indicar cálculos, atribuições ou qualquer tipo de manipulação de dados.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 153
26/11/15 16:17
154
INTRODUÇÃO A MACROS
• Entrada/Saída de dados – usado para indicar a entrada e/ou saída de dados.
• Decisão – usado para indicar uma decisão que deve ser tomada, possibilitando o desvio para outros pontos do fluxo de dados. Outra forma de representação lógica do programa é o Português Estruturado. Ela permite construir como deverão ficar as linhas de código do programa, como pode ser visto a seguir: início
inteiro: A, B, C, X;
leia A; leia B; leia C; X ß A+B+C; escreva X; fim Na representação lógica anterior, identificam-se: • início – indica o início da rotina. • inteiro: A, B, C, X – indica a criação das variáveis A, B, C e X do tipo inteiro.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 154
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
155
• leia A – o programa vai ler o valor de A informado e o mesmo vai ocorrer em B e C. • X ß A+B+C – a variável X vai receber o resultado da soma de A+B+C. • escreva X – o programa vai exibir o valor atribuído à variável X, que no exemplo é o resultado da soma das variáveis A+B+C. • fim – indica o fim da rotina. O ideal é que sejam elaboradas todas as três formas de algoritmo. Assim, as chances de ocorrerem erros na codificação serão menores.
Operadores aritméticos Os operadores aritméticos podem ser unários ou binários. • Operadores aritméticos unários – aplicam-se à inversão de algum valor atribuído, alterando esse valor de positivo para negativo, e vice-versa. Quadro 1 – Operadores aritméticos unários
Operador unário
Descrição
+
Define números positivos
−
Define números negativos
Operadores aritméticos binários – aplicam-se a várias operações matemáticas. Quadro 2 – Operadores aritméticos binários
Operador binário
Descrição
^
Exponenciação
*
Multiplicação
/
Divisão de valores reais
\
Divisão de valores inteiros
+
Adição
−
Subtração (continua)
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 155
26/11/15 16:17
156
INTRODUÇÃO A MACROS
Operador binário
Descrição
Mod
Resto da divisão (valores inteiros)
=
Atribuição
& ou +
Concatenação de caracteres
Exemplo São exemplos do uso de operadores aritméticos: • X = 5+3 – a variável X recebe o resultado da soma de 5 e 3. • nome = Alex+Ramos – a variável nome recebe a concatenação de Alex e Ramos. • nome = Alex&Ramos – ocorre o mesmo efeito do exemplo anterior, porém usou-se o operador & (e comercial).
Exercícios 2. Qual é a diferença entre os comandos Range e Cells? Cite dois exemplos
de cada um. 3. O que são macros? Quais são os tipos de macro? 4. Um médico recém-formado precisa de uma planilha em Excel que
cadastre: nome de seus pacientes, telefone de contato, data e horário de consulta. Essa planilha também deverá retornar, pelo nome do paciente, as informações cadastradas. Com base nessas informações, construa o diagrama de algoritmo que represente graficamente o fluxo de dados. Deverá ser criado um diagrama para cada operação (cadastro, consulta). 5. Considerando o diagrama de algoritmo criado no exercício 3, monte
no Excel uma planilha para cadastro e consulta dos dados do paciente. Utilize as macros para deixar a planilha funcional.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 156
26/11/15 16:17
6. Funções definidas pelo usuário Característica funcional Funções diretas no VBA
Característica funcional Dentro do Microsoft Office Excel uma função tem a finalidade de processar os argumentos recebidos e retornar um resultado. Esses resultados podem ser de diversos tipos, dependendo do tipo de função utilizada. Por exemplo: ao inserir a função =SOMA(B1:B10), espera-se o resultado da soma dos valores constantes no intervalo de células de B1 até B10. O Excel possui diversas funções que são distribuídas em categorias. Apesar de haver inúmeras funções com finalidades variadas, ainda é possível deparar-se com uma situação em que não se encontra uma função específica para realizar determinada operação. Nesse ponto, pode ser utilizado o VBA para suprir essa necessidade. Por meio do VBA é possível definir funções específicas, ou seja, funções definidas pelo usuário. Ao definir uma nova função, esta ficará disponível na categoria Definido pelo Usuário. A sintaxe para criar uma função é: Function NomeFunção(var1;var2;...;varN) NomeFunção = Operação End Function
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 157
26/11/15 16:17
158
FUNÇÕES DEFINIDAS PELO USUÁRIO
Nela, identificam-se: • Function – indica o início da rotina para criação da função. • NomeFunção – corresponde ao nome que será dado à nova função. Esse nome não pode ser o mesmo já existente no menu de funções do Excel. • var – refere-se às variáveis que receberão os argumentos para a função. • Operação – corresponde à ação desejada, ou seja, de que forma a função vai processar os argumentos. • End Function – indica o encerramento da rotina de criação da função.
Funções diretas no VBA Uma função só pode ser definida manualmente com a utilização do ambiente VBA.
Função IMC O Índice de Massa Corporal (IMC) foi desenvolvido no fim do século XIX para avaliar o nível de gordura de cada pessoa. Ele é utilizado pela Organização Mundial da Saúde (OMS) e por profissionais da área, como médicos e nutricionistas. O IMC segue uma classificação que determina se uma pessoa está abaixo ou acima do peso conforme o quadro 1. Seu cálculo considera o peso e a altura do indivíduo e é realizado por meio da fórmula: Peso/Altura2. Quadro 1 – Classificação do índice de massa corporal (IMC)
Resultado Até 17
Situação Muito abaixo do peso
Entre 17 e 18,49
Abaixo do peso
Entre 18,50 e 24,99
Peso normal
Entre 25 e 29,99
Acima do peso
Entre 30 e 34,99
Obesidade I
Entre 35 e 39,99
Obesidade II (severa)
Acima de 40
Obesidade III (mórbida)
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 158
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
159
Para criar o Quadro 1, devem ser realizados os procedimentos: 1. No Excel, pressione Alt+F11 para abrir o ambiente VBA. 2. No menu Inserir, clique em Módulo para inserir um novo módulo. 3. Digite o código a seguir: Function IMC(peso, altura) IMC = peso / (altura ^ 2) End Function 4. Feche o ambiente VBA. No código anterior, foi criada a função IMC em que peso e altura são os parâmetros. Nela, a altura é elevada à segunda potência. Para isso, deve-se utilizar o acento circunflexo (^), que indica exponenciação. A sintaxe da função será: =IMC(peso;altura). Nela, identificam-se: • IMC – nome da função. • peso – valor correspondente ao peso informado pelo usuário. • altura – valor correspondente à altura informada pelo usuário. Para calcular no Excel o IMC de uma pessoa que pesa 80 kg e tenha 1,70 m de altura, a função ficará da seguinte forma: =IMC(80;1,70). Na Figura 1, essas informações são colocadas diretamente na planilha do Excel.
1 2 3 4 5
A Peso(kg) Altura(m) Função Resultado
B
C
80 1,70 =IMC(80;1,7) 27,6816609
Figura 1 – Planilha de cálculo de IMC.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 159
26/11/15 16:17
160
FUNÇÕES DEFINIDAS PELO USUÁRIO
Função RaizT A função RaizT calcula a raiz conforme os parâmetros informados pelo usuário. Para criá-la, devem ser realizados os seguintes procedimentos: 1. No Excel, pressione Alt+F11 para abrir o ambiente VBA. 2. No menu Inserir, clique em Módulo para inserir um novo módulo. 3. Digite o código a seguir: Function RaizT(valor, indice) RaizT = valor ^ (1/indice) End Function 4. Feche o ambiente VBA. A sintaxe da função será: =RaizT(valor;índice). Nela, identificam-se: • RaizT – nome da função. • valor – valor que se deseja obter a raiz. • índice – valor que identifica a raiz; por exemplo, se informado o valor 3, será uma raiz cúbica. Na Figura 2, essas informações são colocadas diretamente na planilha no Excel.
1 2 3 4 5
A Valor Índice Função Resultado
B
C
1728 3 =RAIZ(1728;3) 12
Figura 2 – Planilha de cálculo de raiz.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 160
26/11/15 16:17
MICROSOFT OFFICE EXCEL 2013
161
Exercícios 1. O que são funções definidas pelo usuário? 2. Que comandos indicam o início e o encerramento da criação de uma
função no ambiente VBA? 3. Dadas as fórmulas de cálculo da área das imagens a seguir, crie uma
função para cada item que receba os valores informados e retorne como resultado a área do objeto. L • Área do quadrado = L2
h b • Área do retângulo = b*h
h b • Área do triângulo retângulo = (b1*h)/2
h b1
b2
• Área do triângulo escaleno = [(b1*h)/2] + [(b2*h)/2] 4. Após criar uma função, em que categoria ela fica armazenada?
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 161
26/11/15 16:17
Referências ALVES, William Pereira. Lógica de programação de computadores: ensino didático. 1. ed. São Paulo: Érica, 2014. APRENDER EXCEL. Como usar VBA no Excel, Disponível em: <http://www. aprenderexcel.com.br/2013/tutoriais/como-usar-uma-vba-no-excel>. Acesso em: 23 ago. 2015. HADDAD, Renato; HADDAD, Paulo. Crie planilhas inteligentes com o Microsoft Office Excel 2003: avançado. 5. ed. São Paulo: Érica, 2014. LÓGICA [verbete]. In: Dicionário eletrônico Houaiss da língua portuguesa. Rio de Janeiro: Objetiva, 2009. MANZANO, José Augusto N. G.; MANZANO, André Luiz N. G. Estudo dirigido de Microsoft Office Excel 2010 avançado. 1. ed. São Paulo: Érica, 2013. MENEZES, Jorge. Excel em 101 passos. São Paulo: Clube de Autores, 2010. MICROSOFT OFFICE. Aninhar uma função dentro de uma função. Disponível em: < https://support.office.com/pt-br/article/Aninhar-umafun%C3%A7%C3%A3o-dentro-de-uma-fun%C3%A7%C3%A3o-1b2402e298b8-4ecd-a22e-06af95222bda?ui=pt-BR&rs=pt-BR&ad=BR >. Acesso em: 23 ago. 2015. MICROSOFT OFFICE. Utilizar critérios avançados em filtros. Disponível em: <https://support.office.com/pt-pt/article/Utilizar-crit%c3%a9riosavan%c3%a7ados-em-filtros-0184ad30-6043-4e33-9f25-6b2a79992a20?ui=ptPT&rs=pt-PT&ad=PT>. Acesso em: 23 ago. 2015.
AF_SENAI_MIOLO_Excel Avancado 2013 .indd 162
26/11/15 16:17
Conselho Editorial Paulo Skaf Walter Vicioni Gonçalves Débora Cypriano Botelho Ricardo Figueiredo Terra Roberto Monteiro Spada Neusa Mariani
Editor-chefe Rodrigo de Faria e Silva Produção editorial Letícia Mendes de Souza Edição Juliana Farias Monique Gonçalves Tania Mano Colaboração editorial Juliana Muscovick Revisão Bárbara Borges Muiraquitã Editoração Gráfica Produção gráfica Camila Catto Valquíria Palma Diagramação Estúdio Kiwi Capa Inventum Design Administrativo e financeiro Valéria Vanessa Eduardo Flávia Regina Souza de Oliveira Comercial Ariovaldo Camarozano Bruna Mataran Volpe © SENAI-SP Editora, 2016
A SENAI-SP Editora empenhou-se em identificar e contatar todos os responsáveis pelos direitos autorais deste livro. Se porventura for constatada omissão na identificação de algum material, dispomo-nos a efetuar, futuramente, os possíveis acertos.
Excel avançado 2013_miolo.indd 163
20/10/16 11:21
Alex de Almeida Ramos
O mercado de trabalho em permanente mudança exige que o profissional se atualize continuamente ou, em muitos casos, busque qualificações. É para esse profissional, sintonizado com a evolução tecnológica e com as inovações nos processos produtivos, que o SENAI-SP oferece muitas opções em cursos, em diferentes níveis, nas diversas áreas tecnológicas.
Microsoft Office Excel 2013 — Avançado
Esta publicação integra uma série da SENAI-SP Editora especialmente criada para apoiar os cursos do SENAI-SP.
TECNOLOGIA DA INFORMAÇÃO
Microsoft Office Excel 2013 Avançado
ISBN 978-85-8393-450-9
9
788583
934509
Excel avançado 2013_capa ok.indd Todas as páginas
29/05/18 11:47