EXCEL AVANÇADO - CURSO COMPLETO, Manuais, Projetos, Pesquisas de Matemática

Baixe EXCEL AVANÇADO - CURSO COMPLETO e outras Manuais, Projetos, Pesquisas em PDF para Matemática, somente na Docsity!

Carga horária 20 horas Quantidade de aulas 13 aulas Programas Utilizados Microsoft Excel 2019

Sobre o curso

Um curso especialmente desenvolvido para aqueles que desejam aprimorar o seu conhecimento sobre a planilha eletrônica mais utilizada em todo o mundo. Com o Excel 2019 avançado, você estará um passo à frente, dominando as inúmeras atividades que podem ser desenvolvidas com este maravilhoso software, que relaciona e integra cálculos, planilhas, gráficos e uma infinidade de recursos muito importantes para diversas atividades profissionais, e até mesmo para uso pessoal_._

O que aprender com este curso?

Você irá aprimorar de forma substancial seu conhecimento sobre esta maravilhosa ferramenta, a estilizar apresentações de planilhas sofisticadas e confeccionar funções que retornam dados de suma importância para uma análise criteriosa sobre os dados de uma tabela.

Conteúdo programático

Aula 1 – Revisão de Fórmulas e Funções Aula 2 – Funções de Texto Aula 3 – Funções Lógicas Aula 4 – Funções de Matemática e Trigonometria mais utilizadas e Funções de Estatísticas parte 1 Aula 5 – Funções de Estatísticas parte 2 Aula 6 – Funções de Data e Hora Aula 7 – Auditoria de Fórmulas, Teste de Hipóteses e Funções de Informações Aula 8 – Funções de Pesquisa e Referencia Aula 9 – Tabela Dinâmica e Formatação Condicional Aula 10 – Gráfico Dinâmico e Classificação de dados Aula 11 – Utilizando Formulários Aula 12 – Utilizando Macros e Noções de VBA Aula 13 – Solver e Funções Financeiras Excel Avançado 2019

Excel Avançado 2019

Revisão de Fórmulas e Funções

Aula

01

1. Aula 1 – Revisão de Fórmulas e

Funções

Olá, seja bem-vindo a nossa primeira aula do nosso Curso de Excel Avançado 2019. É uma grande satisfação tê-lo (a) como aluno (a). Estamos empenhados em oferecer todas as condições para que você alcance seus objetivos nesse processo de aprendizagem do curso. É um curso para quem quer expandir suas metas e ideias de projetos, ficar mais alinhado com o mercado atual e visar um planejamento de mercado futuro, aprimorar-se é essencial e esse curso irá te ajudar a dar os próximos passos. No curso de Excel Avançado, você aprenderá funções sofisticadas que irão auxiliar no seu trabalho diário, como planilhas e tabelas, análise de cálculos, gráficos com estatísticas.

1.1. Fórmula da Adição

A funções são facilitadoras, imagine que você precise somar uma lista de 150 valores, usando o sinal de adição, seria um processo muito demorado. Já com a função SOMA() o processo é rápido. Sintaxe da função SOMA(). SOMA(número1;[número2];...) Observe o exemplo abaixo : Na célula D5, criaremos a seguinte fórmula.

=SOMA(D2:D4)

D2 é a posição inicial, a primeira célula;

Dois pontos “ : ” é o intervalo entre as células, neste

caso ele vai acrescentar D2, D3 e D4.

D4 é a última posição, a última célula. =SOMA(D:D4).

Outra forma de calcular uma lista em sequência é utilizar o recurso Totalização Automática. Este botão fica localizado no grupo Edição. O Resultado aparecerá após a célula selecionada.

1.2. Função Média

Retorna a média ( média aritmética) dos argumentos. Esta função permite por exemplo, calcular a média de despesas, as suas notas escolares, entre outras possibilidades.

Sintaxe: = MÉDIA(núm1, [núm2], ...)

A fórmula digitada na coluna média de gastos na linha 6 foi:

=MÉDIA(B6:D6)

1.3. Função Mínimo

Retorna o menor número na lista de argumentos.

Sintaxe: = MÍNIMO(número1, [número2], ...)

A fórmula digitada na coluna valor da venda, na célula B11 foi:

=MÍNIMO(B2:B9)

O resultado do menor valor de venda foi de R$ 150,

1.4. Função Máximo

Retorna o maior número de um conjunto de valores. Formato de uso:

= MÁXIMO(número1, [número2], ...)

O resultado do maior valor de venda foi de R$ 56 0, Obs.: Podemos observar que a sintaxe é bastante semelhante a das funções SOMA e MÉDIA, pois também são funções matemáticas e possuem a mesma origem. Basicamente estas funções utilizam o sinal de igualdade para iniciar, depois o nome e em seguida deve-se abrir parênteses. Dentro dos parênteses deve- se digitar/selecionar o(s) intervalo(s) que será(ão) verificado(s) pela função.

= SOMA( célula inicial : célula final )

= MÉDIA( célula inicial : célula final )

= MÍNIMO( célula inicial : célula final )

= MÁXIMO( célula inicial : célula final )

1.5. Exercício de Hoje

Retorna o número de série da data atual. O número de série é o código de data/hora usado pelo Excel para cálculos de data e hora.. A função HOJE é útil quando você precisa ter a data atual exibida em uma planilha, independentemente de quando a pasta de trabalho for aberta. Ela também é útil para o cálculo de intervalos.

1.6. Função Agora

Retorna o número de série da data e da hora atual. Se o formato da célula era Geral antes de a função ter sido inserida, o Excel transformará o formato dessa célula para que ele corresponda ao mesmo formato de data e hora de suas configurações regionais. Você pode alterar o formato de data e hora da célula usando os comandos no grupo Número da guia Página Inicial, na Faixa de Opções. A função AGORA é útil quando você precisa exibir a data e a hora atuais em uma planilha ou calcular um valor com base na data e na hora atuais e ter esse valor atualizado sempre que abrir a planilha. A sintaxe da função é: =AGORA() Formato de apresentação. .

1.7. Função SE()

A função SE é uma das funções mais populares do Excel e permite que você faça comparações lógicas entre um valor e aquilo que você espera. Em sua forma mais simples, a função SE diz: SE(Algo for Verdadeiro, faça tal coisa. Caso contrário, faça outra coisa) Portanto, uma instrução SE pode ter dois resultados. O primeiro resultado é se a comparação for verdadeira , o segundo se a comparação for falsa. A função SE aceita como resposta texto e números. Modo texto. SE(Algo for Verdadeiro, “faça tal coisa”, caso contrário, “faça outra coisa”) A mensagem de resposta faça tal coisa, deve estar entre aspas. Modo número. A mensagem de resposta faça tal coisa, não vai aspas, pois, além de exibir um número, podemos realizar um cálculo. Abaixo temos um exemplo, uma tabela de clientes, onde o status informa os números 0 e 1. A partir do status, será criada uma fórmula para exibir a situação,

• O símbolo = diz ao Excel que em determinada célula irá começar uma nova fórmula;
• PROCV é nome da fórmula, que significa procurar valor;
• Agora, dentro dos parênteses, o valor_procurado , é o argumento que deseja fornecer como base para a procura ser feita;
• Matriz_tabela, é o intervalo onde se realizará a pesquisa;
• Núm_coluna é a coluna que se deseja obter o resultado, considerando que as colunas são contadas a partir do intervalo estipulado em matriz_tabela;
• Ainda dentro dos parênteses, no lugar de procurar_intervalo, você deve selecionar entre as duas seguintes opções: a) VERDADEIRO – seleciona uma resposta que corresponde aproximadamente; b) FALSO – Seleciona uma resposta exata;
• Deve-se usar o símbolo ponto e vírgula (;) para separar cada termo na fórmula.

1.10. Validação de Dados

No Excel, podemos restringir o tipo de dados ou os valores que os usuários inserem em uma célula. Um dos recursos mais comuns é a lista suspensa. Existem diversas possibilidades de utilizar este recurso. A validação de dados impede que dados inválidos sejam aceitos. Exemplo: Uma planilha de controle de compras possui um campo pendências que permite exibir somente SIM ou NÃO e mostrar uma mensagem de erro quando o usuário tenta inserir outro texto. Encontramos este recurso na guia Dados , no grupo Ferramentas de Dados. Na lista, encontramos três opções. Quando a opção Validação de Dados é selecionada, a seguinte caixa de diálogo é exibida. São três as guias dentro desta caixa. Guia Configurações – Permite que sejam definidos critérios de validação. Em Permitir, são exibidas as seguintes opções. De acordo com o que será escolhido, outras opções aparecem. Exemplo com Número inteiro. Guia Mensagem de entrada: Exibe uma mensagem quando o usuário clica na célula definida.

Guia Alerta de erro – Exibe um alerta de erro após o usuário inserir dados inválidos. Ao digitar uma quantidade acima de 5 participantes, a mensagem será exibida. Utilizando uma lista para facilitar o cadastro de seções do supermercado. Em Permitir, foi definida a opção Lista. Em Fonte, foram selecionadas as categorias. Ao clicar na célula C4, automaticamente a lista surge. Podemos testar a data na validação, no exemplo a seguir se um funcionário solicitar uma folga, a data da folga não pode ser abaixo da atual. No exemplo, utilizamos “é maior do que” e a função HOJE() para que automaticamente o sistema faça a busca pela data. Podemos definir a quantidade de caracteres na entrada de texto de uma célula. Veja o exemplo onde iremos definir o número máximo de caracteres para não ocupar muito os comentários.

1.12. Exercício de Fixação

Olá! Seja bem-vindo (a) à nossa área de exercícios de fixação. Desenvolvemos uma série de atividades para que você domine todo conteúdo abordado nesta aula. É muito importante você fazer todos os exercícios e, qualquer dúvida, chame o seu instrutor. Observação: Salve os exercícios. Se você precisar de orientação de como localizar sua pasta, converse com seu instrutor para que ele crie uma ou oriente onde a mesma se encontra. Exercício - 1: Este exercício tem como objetivo digitar a seguinte planilha e solucioná-la, obtendo os resultados desejados.

1. Digite os dados da tabela abaixo.
2. Agora, aplique formatações, como cor de fundo, e cor do texto.
3. Fórmulas: Utilize das funções Soma, Média, Máximo e Mínimo para determinar os valores buscados.
4. Salve a planilha com o nome de Exercício 1 aula 1.
5. Feche o Excel. Exercício - 2: Este exercício tem como objetivo digitar uma planilha de produtos em estoques.
6. Digite os dados da tabela a seguir.
7. Digite os dados referente a tabela auxiliar:
8. Aplique formatações, cor de preenchimento o uso de bordas, estilo de moeda e cor do texto.
9. Fórmulas: Para descobrir o menor valor, utilizar a função =menor(), e para calcular o maior valor, utilize a função =maior().
10. Salve a planilha com o nome produtos em estoque.
11. Feche o Excel.

Exercício - 3: Este exercício tem como objetivo digitar uma planilha de boletim.

1. Digite os dados da tabela abaixo:
2. Insira os demais dados, correspondentes a tabela:
3. Aplique formatações, como cor de preenchimento e bordas. Fórmulas: Na coluna Média, calcular a média com base nas notas dos alunos, usar a função =média(). Na coluna maior situação, usar a função SE(), para descobrir os alunos aprovados e reprovados.
4. Salve a planilha com o nome Boletim
5. Feche o Excel. Exercício - 4 : Este exercício tem como objetivo digitar um controle de notas dos alunos.
6. Digite os dados da tabela abaixo.
7. Insira os demais dados, correspondentes a tabela:
8. Aplique formatações, como cor de preenchimento, cor do texto, mesclar, centralizar e bordas
9. Aplique a fórmula da média e a fórmula do =PROCV(), para retornar o conceito de cada aluno.
10. Salve a planilha com o nome controle de notas de alunos.
11. Feche o Excel.

Excel Avançado 2019

Funções de Texto

AULA

2. Aula 2 – Funções de Texto

Fórmulas de Texto é uma fórmula que retorna texto. As fórmulas de texto usam o operador ( & ) para trabalhar com células de texto e string de texto entre aspas. Servem também para unir os resultados de diferentes fórmulas em uma única célula. Como unir os resultados de diferentes fórmulas em uma única célula. O Microsoft Excel permite o uso inteligente de fórmulas para o trabalho com textos. Seja para extrair parte do texto de uma célula ou montar outra combinação de caracteres, as funções de texto são muito úteis no nosso dia a dia.

2.1.1. Função TEXTO

A função TEXTO permite que você altere a maneira de exibir um número aplicando formatação a ele com códigos de formatação. Isso é útil quando você deseja exibir números em um formato mais legível ou deseja combinar números com texto ou símbolos. Em sua forma mais simples, a função TEXTO diz: =TEXTO(valor que você deseja formatar; “Código de formatação que deseja aplicar”) Exemplo:

2.1.2. Função EXT.TEXTO

A função EXT.TEXTO retorna um número específico de caracteres de uma cadeia de texto, começando na posição especificada, com base no número de caracteres especificado. A função EXT.TEXTO sempre conta cada caractere, tanto de um byte como de dois bytes, como 1, não importa qual seja a configuração padrão do idioma. Sua Sintaxe seria:

=EXT.TEXTO(TEXTO;Nº CARACTERE INICIAL;Nº DE

CARACTERES)

Exemplo:

2.1.3. Função Esquerda

Função ESQUERDA retorna o primeiro caractere ou caracteres em uma cadeia de texto baseado no número de caracteres especificado por você. Digamos que você tenha uma empresa, organize seu estoque de produtos em uma planilha do Excel , e precise de uma lista com os códigos dos produtos, mas quer apenas os seis primeiros dígitos deles. Neste caso, a função ESQUERDA encaixa-se perfeitamente. Sua sintaxe seria: =ESQUERDA(TEXTO;NUMERO CARACTERES) Exemplo:

2.1.4. Função Direita

DIREITA retorna o último caractere ou caracteres em uma cadeia de texto, com base no número de caracteres especificado. Esta função é basicamente igual a função ESQUERDA apenas invertendo a ordem do caractere que é retornado. Sua sintaxe seria: =DIREITA(TEXTO;NUMERO CARACTERES)

F

Exemplo:

2.1.5. Função CONCAT

Esta função substitui a função CONCATENAR. No entanto, a função CONCATENAR permanecerá disponível para compatibilidade com versões anteriores do Excel. A função CONCAT combina o texto de vários intervalos e/ou cadeias de caracteres, mas não oferece o delimitador ou argumentos. Se a cadeia de caracteres resultante exceder 32767 caracteres (limite da célula), CONCAT retornará o erro #VALOR! Sua sintaxe seria: =CONCAT(Texto;Texto) Exemplo:

2.1.6. Função LOCALIZAR

A função LOCALIZAR do Excel informa a posição de determinado texto dentro de uma sequência maior. Por exemplo, para localizar a posição da letra "m" na palavra "primo", você pode usar a seguinte função: =LOCALIZAR("m";"primo") Também é possível procurar palavras dentro de outras palavras. Por exemplo, a função: =LOCALIZAR("linha";"sublinha") Retorna 4, porque a palavra "linha" começa no quinto caractere da palavra "sublinha". Exemplo:

2.1.7. Função LOCALIZAR

A função UNIRTEXTO combina o texto de vários intervalos e/ou cadeias de caracteres e inclui um delimitador especificado por você entre cada valor de texto que será combinado. Se o delimitador for uma cadeia de caracteres de texto vazia, essa função concatenará efetivamente os intervalos. Por exemplo, =UNIRTEXTO("";VERDADEIRO;"O","sol","vai","brilhar", "forte","amanhã.") retornará O sol vai brilhar forte amanhã. Sua sintaxe seria: =UNIRTEXTO(delimitador;ignorar_vazio;texto1;[t exto2], …) Exemplo:

2.1.8. Função NÚM.CARACT

A função NÚM.CARACT retorna o número de caracteres em uma cadeia de texto. Conta os caracteres presentes em uma tabela sem discriminar os espaços em branco. Sua sintaxe seria: =NÚM.CARACT.(texto) Exemplo:

2. Insira os demais dados, correspondentes a tabela:
3. Aplique formatações, cor de preenchimento o uso de bordas e Negrito nos títulos
4. Fórmulas: Para descobrir o Gênero do filme utilize uma junção entre as funções PROCV e a função ESQUERDA.
5. Salve a planilha com o nome Filmes por Gêneros.
6. Feche o Excel. Exercício - 3: Este exercício tem como objetivo digitar uma planilha de retorno de mensagens.
7. Digite os dados da tabela abaixo.
8. Fórmulas: Na coluna Mensagem, concatene a seguinte frase: “O concurso do (a) cairá num(a), com o nome do concurso e o dia da semana na qual o concurso irá ocorrer. Exemplo de como deve ficar a primeira sentença: O concurso do (a) Caixa cairá num(a) quinta- feira.
9. Salve a planilha com o nome Mensagem.
10. Feche o Excel. Exercício - 4: Este exercício tem como objetivo digitar um controle de notas dos alunos.
11. Digite os dados da tabela abaixo:
12. Insira os demais dados, correspondentes a tabela:

3. Aplique formatações, como cor de preenchimento, cor do texto e bordas.
4. Aplique a função PRI.MAIÚSCULA aninhada com a função TEXTO, para retornar o resultado desejado na coluna B.
5. Aplique a função EXT.TEXTO aninhada com a função LOCALIZAR, para retornar o resultado desejado na coluna C.
6. Aplique a função ESQUERDA aninhada com a função LOCALIZAR, para retornar o resultado desejado na coluna D.
7. Aplique a função UNIRTEXTO para retornar o resultado desejado na coluna E.
8. Salve a planilha com o nome Texto.
9. Feche o Excel. Exercício - 5: Este exercício tem como objetivo criar um cadastro de retornarmos os departamentos de cada funcionário e se ele é horista ou mensalista.
10. Digite os dados da tabela abaixo.
11. Insira os demais dados, correspondentes a tabela:
12. Agora, aplique formatações conforme o modelo acima.
13. Na coluna D, defina o departamento de cada funcionário, sendo dentro de quatro possibilidades: ADM, CONTAB, PROD OU VENDAS. Use a função ESCOLHER aninhada com a FUNÇÃO DIREITA para solucionar esta coluna. Considere que funcionários com o código terminando com o número 1 são ADM, com o final de código sendo o número 2 são CONTAB, com o final de número 3 são PROD e com o final de número 4 são VENDAS.
14. Na coluna E determine se o funcionário é Horista ou Mensalista utilizando da função ESCOLHER aninhada com a função ESQUERDA considere que funcionários com o código iniciando com o número 1 são horistas e aqueles que se iniciam com o número 2 são mensalistas.
15. Salve a planilha com o nome Cadastro de Funcionários.
16. Feche o Excel.

3.3. Função SE-OU

Chamamos de Função SE-OU quando aninhamos a função SE com a função OU. No geral, esta função é utilizada para complementar outras funções como, por exemplo, a função SE. Como SE-OU não seria uma função propriamente dita, mas sim uma função aninhada, ela poderia ser descrita de várias formas, mas vamos determinar aqui um exemplo da forma mais simples de sintaxe. Exemplo:

3.4. Função SEERO

A função SEERRO serve para interceptar e manipular erros em uma fórmula. SEERRO retornará um valor especificado se uma fórmula for avaliada como um erro; caso contrário, retorna o resultado da fórmula. Esta função pode ser utilizada juntamente com qualquer outra, pois, caso o valor do cálculo retornar de forma correta, a função SEERRO não será aplicada. Sua sintaxe seria: = SEERRO(valor;valor_se_erro) , onde: Valor - corresponde ao cálculo que será realizado ou a função que será aplicada. Valor_se_erro - expressão, número ou símbolo que deverá ser retornado caso o cálculo resultar em erro. Exemplo:

3.5. Função SES

A função SES verifica se uma ou mais condições são satisfeitas e retorna um valor que corresponde à primeira condição VERDADEIRO. A função SES pode ser usada como substituta de várias instruções SE aninhadas, além de ser muito mais fácil de ser lida quando condições múltiplas são usadas. Sua sintaxe seria: =Ses(teste lógico 1; valor se verdadeiro, teste lógico 2....) Exemplo:

3.6. Função PARÂMETRO

A função PARÂMETRO avalia um valor (chamado de expressão) em relação a uma lista de valores e retorna o resultado correspondente ao primeiro valor coincidente. Se não houver nenhuma correspondência, um valor padrão opcional poderá ser retornado. A sua sintaxe seria: =PARÂMETRO(expressão; valor1; resultado1; [padrão ou valor2;resultado2]…[padrão ou valor3;resultado3]...Valor padrão) Este valor padrão é opcional e caso você não o especifique, será exibido #N/D caso o Excel não encontre nada. Exemplo:

3.7. Exercício de Conteúdo.

1. Abra o Microsoft Excel, digite os títulos da tabela e ajuste o tamanho de cada coluna.
2. Digite os valores correspondentes à coluna “A” e na coluna “B”, digite se a forma de pagamento é à vista ou à prazo.
3. Conforme orientado, formate as colunas.
4. Formate a tabela com a formatação indicada.
5. Determinada loja está concedendo descontos de 5% para os clientes que realizarem compras superiores a R$ 200,00 e efetuarem o pagamento à vista. Solucione a coluna “C”.
6. Solucione a coluna “D”.
7. Abra uma nova planilha no mesmo documento digite os títulos da tabela e ajuste as colunas.
8. Digite os valores das vendas, e a forma de pagamento conforme demonstrado.
9. Formate as colunas da tabela, insira as bordas, coloque a formatação de número nos valores da venda e centralize os textos.
10. Aplique as formatações de cores de fundo, conforme demonstrado.
11. Determinada loja de artigos importados está concedendo descontos de 5% para os clientes que realizarem compras superiores a R$ 400,00 ou efetuarem o pagamento à vista. Solucione a Coluna “C”.
12. Solucione a coluna “D”.
13. Abra uma nova planilha no mesmo documento e digite os títulos da planilha.
14. Informe os nomes de cada Nadador, assim como sua idade.
15. Formate a tabela conforme orientado, inserindo bordas mesclando células e ajustando as colunas.
    16. Existem quatro categorias de natação: Infantil com idade até 10 anos, juvenil com idade entre 11 e 17 anos, adulto com idade entre 18 e 60 anos e Sênior com idade acima de 60 anos. Determine qual a categoria de cada um dos atletas.
    17. Digite os dados correspondentes à coluna “A”.
    18. Digite os dados correspondentes à coluna “B”.
    19. Ajuste as colunas a formate as células conforme orientado, inserindo bordas e negrito.
    20. Insira uma validação de dados na célula B usando a lista de códigos da coluna “A”.
    21. Determine na célula B2 o nome do produto conforme o código exibido na célula B1.
    22. Teste a validação de dados com a função aplicada.

3.8. Exercício de Fixação.

Exercício - 1: Este exercício tem como objetivo digitar a seguinte planilha e o obter os resultados desejados.

1. Abra o Microsoft Excel, digite a tabela e formate a mesma conforme a figura abaixo:
2. Insira os demais dados, correspondentes a tabela: