Classificação de Dados e Tabelas Dinâmicas no Excel, Slides de Microsoft Excel

Baixe Classificação de Dados e Tabelas Dinâmicas no Excel e outras Slides em PDF para Microsoft Excel, somente na Docsity!

Curso de Excel

Autores:

Francisco Portelinha

Santa Rita do Sapucaí – MG

Dezembro de 2020

Sumário

1 Excel Básico

O Excel é um programa capaz de elaborar planilhas de cálculos com fórmulas complexas, bem como representar as informações inseridas através de gráficos, proporcionando uma visão ampla de todos os dados contidos na planilha. Este programa é muito utilizado atualmente, pois se trata de um aplicativo que pode ser utilizado por diferentes tipos de pessoas, nos mais variados cargos dentro de uma organização. Até mesmo dentro de nossas casas podemos tirar proveito dos recursos providos pelo Excel, sendo possível controlar nossas despesas do dia-dia, conseguindo elaborar um orçamento doméstico ou até um simples controle da conta bancária.

1.1 Introdução - Dicas Iniciais

A primeira e mais importante dica sobre o mundo do Excel é sempre pesquisar. A melhor fonte de informações específicas sobre fórmulas, formatação, VBA, ou qualquer outra informação que você venha a necessitar é a internet. Existem muitos sites especializados em Excel, blogs e fóruns. Não há como citar um local que seja o melhor, pois dependerá sempre do assunto que você precisa. Uma pesquisa sobre um assunto por exemplo com as palavras chave “excel soma condicional com mais de uma condição” retornará inúmeras páginas de consulta. Abra algumas delas, pois uma pode ter o detalhe que você precisava ou estar melhor explicada. O office tem uma página de suporte, que provavelmente aparecerá na sua busca, mas existem outras páginas que podem detalhar melhor ou dar exemplos para você usar. Pesquise e teste sempre. Esta é a melhor maneira de se aprofundar no Excel. Uma segunda dica é observar que as fórmulas têm grafias diferentes para cada língua em que o Excel é instalado. Uma fórmula no Excel em versão em inglês usa a fórmula SUM(n1, n2,...) em vez de SOMA(n1; n2;...) em português, ou VLOOKUP em vez de PROCV. Note que além da tradução do texto da fórmula, os argumentos são separados por ponto e vírgula em português e vírgula na versão em inglês. O Excel em português de Portugal também tem fórmulas diferentes do português brasileiro, como por exemplo CONT.SE(BR) e CONTAR.SE(PT), ARRUMAR(BR) e COMPACTAR(PT), SENÃODISP(BR) e SEND(PT), e outros mais. O que fazer se for carregar seu arquivo preparado em português BR em uma versão do Excel em outra língua? O Excel converte tudo para você e mostrará sempre a mesma fórmula traduzida na língua do software que estiver usando. Mas lembre-se que para trabalhar no Excel em outra língua você precisará usar as fórmulas naquela língua.

1.2 Iniciando com o Excel - Área de Trabalho

Na área de trabalho do Excel podemos acessar todas as ferramentas disponíveis, que estão agrupadas em menus de acordo a função de cada uma delas. A Microsoft passou a utilizar as barras de ferramentas no lugar de listar as ferramentas em menus a partir do Excel 2007, com o objetivo de facilitar a visualização, localização e acesso às ferramentas.

Figura 1 – Área de Trabalho do Excel

O menu principal para as funções de abrir arquivo, criar novo arquivo, salvar como, imprimir e outras opções, é acessível pela barra ferramentas em “Arquivo”:

Figura 2 – Menu Principal do Excel

Figura 3 – Opções do Excel

A fórmula pode ser copiada com ctrl+c/ctrl+v, ou usando as opções copiar/colar com o botão direito do mouse na célula que quer copiar e colar, ou ainda arrastando a fórmula para as próximas células. Para arrastar basta levar o cursor do mouse até o canto inferior direito da célula, onde se encontra um pequeno quadrado. Seu cursor se transformará em uma pequena cruz preta. Então basta segurar com o botão esquerdo do mouse e arrastar até onde desejar. Durante o processo de copiar ou arrastar o excel realizará todos os ajustes para que os cálculos sejam realizados corretamente, mudando as referências das células cujos valores serão copiados para as colunas correspondentes. No exemplo acima, veja que a célula C7 não contém mais a fórmula ı  B 4 B 5 B6, mas sim ı  C 4 C 5 C6, somando então os valores que estão na coluna C e não na B, o mesmo ocorre na coluna D.

1.4.1 Mudando as Referências

Por padrão, uma referência de célula é relativa. Por exemplo, quando você se referir a célula A2 na célula C2, você está realmente fazendo referência a uma célula que está duas colunas para a esquerda e na mesma linha. Se você copiar a fórmula “=B2+A2” de célula C2 em D2 (uma coluna à direita), a fórmula em D2 é ajustada para que as referências também sejam deslocadas em uma coluna à direita. A fórmula em D2 ficará então ı  B 2 C2.

Para manter a referência da célula original no exemplo acima, após copiá-lo, você precisará manualmente mudar a referência, ou então copiar o texto que está da fórmula (e não a célula) para então copiar exatamente a mesma fórmula em outra célula.

Para evitar que você precise editar todas as fórmulas coladas é possível travar a referência para que ela seja absoluta. Para isto, antes de copiar ajuste sua fórmula incluindo um cifrão antes das letras da coluna e/ou número da linha.

Exemplo: Se a célula A5 contém: =A1+A3+A4+D1 (Fórmula base) Arrastando a fórmula até A6 temos: =A2+A4+A5+D Porém gostaríamos de manter o valor de A1 e D2, pois são valores que todos os cálculos precisariam usar. Então alteramos em A5, antes de arrastar: =A$1+A3+A4+D$ 1

Ao arrastar a fórmula até A6 temos: =A$1+A4+A5+D$ 1 Ao arrastar A5 até B5 temos: =B$1+B3+B4+E$ 1 Note que as todas as referências de coluna mudaram. Se quiséssemos manter os valores que estão em A1 e D1 fixaríamos com o cifrão antes da referência de coluna. Alterando então o conteúdo de A5: =$A1+A3+A4+$D

E ao arrastar a fórmula até B5 temos: =$A1+B3+B4+$D Se copiarmos a fórmula de A5 para B6 (ou de B5 para B6) teríamos: =$A2+B4+B5+$D Perdemos novamente os valores de A1 e D1 que gostaríamos de ter mantido. Para isto

podemos então colocar cifrão antes da referência de coluna e também da referência de linha. Portanto em A5 seria: =$A$1+A3+A4+$D$ 1 Ao copiar a fórmula na célula B6 teríamos: =$A$1+B4+B5+$D$ 1 Uma dica é posicionar o cursor na célula que deseja colocar o cifrão e apertar F4. O F coloca o cifrão em ambas coluna e linha. Ao apertar novamente remove o da linha. Mais uma vez ele remove o da coluna e coloca cifrão na linha. E se pressionar novamente retira ambos os cifrões. Em resumo, verifiquemos a Figura mostrada abaixo:

Figura 5 – Mudando Referências

Se a referência for É alterada para: $A$1 (coluna absoluta e linha absoluta) $A$1 (a referência e absoluta) A$1 (coluna relativa e linha absoluta) C$1 (a referência e mista) $A1 (coluna absoluta e linha relativa) $A3 (a referência e mista) A1 (coluna relativa e linha relativa) C3 (a referência e relativa) Tabela 1 – Tipos de Referências

1.5 Formatação

Na barra de ferramentas “Página Inicial” há diversas ferramentas de formatação, como mudança de cores, negrito, itálico, fonte, tamanho da fonte, alinhamento vertical, alinhamento horizontal, mesclar células, formatação do tipo de formato (número, texto, moeda, etc), pincel para copiar formatos, entre outros.

Figura 6 – Formatação

Além dos formatos do texto, é possível alterar a altura das linhas e colunas, posicionando o cursor entre duas linhas ou entre duas colunas, clicar e segurar arrastando em direção a aumentar ou diminuir os tamanhos. E em seguida se desejar você pode alinhar horizontalmente ou verticalmente os textos, ou ainda mesclar células.

Figura 9 – Formatação de Tabela

Falaremos sobre formatação condicional mais adiante. O uso do filtro também está disponível nesta mesma barra “Página Inicial”.

Figura 10 – Filtro no Excel

1.6 Funções

Funções são recursos predefinidos usados para efetivação de operações matemáticas e lógicas das mais variadas forma com extrema facilidade. Grande parte das funções encontradas no programa de planilhas do Excel é similar às funções existentes em calculadoras para aplicações cientificas ou financeiras, normalmente usadas por profissionais de diversas áreas de atuação. Uma função tem sempre o nome de identificação que pode ser escrito com ou sem argumentos (parênteses). As Funções sem argumentos são representadas por palavras seguidas dos parênteses sem a menção do conteúdo como argumento dentro deste símbolo. Já as funções com argumentos são acompanhadas de informações que vão dentro dos parênteses. =FUNÇÃO() => Uma função sem argumentos =FUNÇÃO(argumeto1; argumento2;.... ; argumentoN) Os argumentos assim como o nome de identificação de uma função podem ser escritos tanto em caracteres maiúsculos quanto minúsculos. Para usar as funções é necessário observar as regras específicas de cada função.

1.6.1 Função Soma

A função SOMA é uma das funções matemática e de trigonometria. Pode adicionar valores individuais, referências de células, intervalos ou uma combinação dos três.

A sintaxe da função pode ser descrita como, SOMA(núm1;[núm2];...]) A sintaxe da função SOMA tem os seguintes argumento argumentos: núm1: Obrigatório. É o primeiro argumento numérico que você deseja somar. núm2„...: Opcional. Argumentos de número de 2 a 255 que você deseja somar.

A função SOMA soma todos os números que você especifica como argumentos. Cada argumento pode ser um intervalo, uma referência de célula, uma matriz, uma constante, uma fórmula ou o resultado de outra função.

Exemplo: =SOMA(A1:A5) Resultado: soma todos os números contidos nas células de A1 a A5. Exemplo: =SOMA(A1, A3, A5) Resultado: soma os números contidos nas células A1, A3 e A5. Para utilizar a fórmula basta selecionar a célula onde quer o resultado da soma, e escrever a fórmula “=soma(seus argumentos)”. Quando você começar a escrever o Excel irá te mostrar funções que têm também as mesmas letras no início de seus nomes. Você pode selecionar como o mouse ou setas, ou continuar escrevendo até o final. Se o cursor estiver na função que você quer, você pode pressionar a tecla Tab, e o Excel irá completar a palavra e incluir já o primeiro parêntesis. Depois de escrever seus argumentos você pode inserir o parêntesis final, ou pressionar Tab, Enter, ou ainda clicar em outra célula, que o resultado será calculado. Lembrando que se você pressionar a tecla Esc suas alterações na célula serão descartadas, e portanto você precisará digitar novamente sua fórmula.

Figura 13 – Opções de Cálculo no Excel

Dica: Você também pode usar uma referência que esteja em outra planilha. Para isto digite o sinal de igual, e quando for colocar o argumento clique na planilha que contém o valor que você deseja e clique na célula. Após isto você pode continuar digitando até finalizar a fórmula, clicar na planilha de origem para continuar colocando os argumentos a partir dela.

Figura 14 – Opções de Cálculo no Excel

1.6.2 Função Máximo

Retorna o valor máximo de um conjunto de valores, valores lógicos e texto são ignorados, o limite é de 255 valores.

Sintaxe: MÁXIMO(número1, [número2], ...) Exemplo: Suponha que desejasse saber qual a maior idade de crianças em uma tabela de dados. Veja a fórmula no exemplo abaixo:

Figura 15 – Função Máximo no Excel

(A2:A5) – refere-se ao endereço dos valores onde você deseja ver qual é o maior valor.

1.6.3 Função Mínimo

Retorna o valor mínimo de um conjunto de valores, valores lógicos e texto são ignorados. Sintaxe: MÍNIMO(número1, [número2], ...) Exemplo: Suponha que desejasse saber qual o menor peso de crianças em uma tabela de dados. Veja a fórmula no exemplo abaixo:

Figura 16 – Função Mínimo no Excel

1.6.4 Função Média

Retorna a média aritmética dos argumentos. Por exemplo, se intervalo A1: A20 contiver números, a fórmula  mdiapa1 : A 20 q retornará a média desses números.

Sintaxe: MÉDIA(núm1, [núm2], ...) Também existem opções de fórmulas de média que você pode utilizar, como por exemplo a média geométrica, e para usar a sintaxe seria MÉDIA.GEOMÉTRICA(num 1, [num 2], ...). Quando você digitar =MÉDIA o Excel mostra também as outras opções de funções que começam com a Média.

Dica: Ao digitar ı  mdia aparecerão outras opções de funções de média. Se você não quiser ir até o mouse para escolher a que você quer, basta usar a seta para baixo para navegar até a que você deseja e pressionar Tab. Você também pode pressionar Tab se sua função for a primeira da lista mesmo quando não tiver terminado de escrever. O Excel completará para você até o parêntesis.

Lembrando que uma consulta rápida da média pode ser feita apenas selecionando o intervalo de valores que você deseja calcular, e verificar a média na barra de status (barra inferior) do Excel.

1.6.5 Função de Arrendondamento

Função ARRED arredonda um valor para um número especificado de dígitos. Sintaxe: ARRED(número, núm_dígitos)

Figura 17 – Função Data e Hora no Excel

Dica: Caso seu Excel esteja na versão em inglês a ordem dos argumentos muda para mês/dia/ano.

OBSERVAÇÃO: O Excel armazena datas como números de série sequenciais de forma que eles possam ser usados em cálculos. A data 1^ de janeiro de 1900 é o número de série 1 e 1^ de janeiro de 2008 é o número de série 39448, pois corresponde a 39.447 dias após 1^ de janeiro de 1900. Você precisará alterar o formato de número (Formatar Células - Data) para exibir uma data apropriada.

Figura 18 – Função Data e Hora no Excel

=AGORA(): Retorna a data e hora atuais. =HOJE(): Retorna somente a data atual. =DIA(núm_serial) :Retorna o dia de uma data representado por um número de série. O dia é dado como um inteiro que varia de 1 a 31. A sintaxe da função

=MÊS(núm_série): Retorna o mês de uma data representado por um número de série. O mês é fornecido como um inteiro, variando de 1 (janeiro) a 12 (dezembro).

=ANO(núm_série): Retorna o ano correspondente a uma data. O ano é retornado como um inteiro no intervalo de 1900-9999.

=DIAS(data_final,data_inicial):Retorna o número de dias entre duas datas. =DIAS360(data_inicial; data_final): A função DIAS360 retorna o número de dias entre duas datas com base no ano de 360 dias ( doze meses de 30 dias ). Use essa função para ajudar no cálculo de pagamentos, se o seu sistema contábil estiver baseado em doze meses de 30 dias.

=DIATRABALHOTOTAL(data_inicial; data_final;[feriados]): Retorna o número de dias úteis inteiros entre a data incial e a data final. Os dias úteis não incluem os finais de semana

e feriados que você identificar. Use DIATRABALHOTOTAL para calcular os benefícios aos empregados que recebam com base no número de dias trabalhados em um período específico.

=DIATRABALHO(data_inicial; dias ;[feriados]): Retorna um número que represenra uma data que é o número indicado de dias úteis antes ou após uma data (inicial). Os dias úteis excluem fins de semana e datas identificadas como feriados. Use DIATRABALHO para excluir os fins de semana ou feriados ao calcular as datas de vencimento de fatura, horas de entrega esperadas ou o número de dias de trabalho executados.

=DATAM(data_inicial, meses): Retorna um número de série de data que é o número de meses indicado antes ou depois de data_inicial. Use DATAM para calcular datas de liquidação ou datas de vencimento que caem no mesmo dia do mês da data de emissão.

Exemplo:

Figura 19 – Função Data e Hora no Excel

=HORA(núm_série): Retorna a hora de um valor de tempo. A hora é retornada como um inteiro, variando de 0 (12:00 A.M.) a 23 (11:00 P.M.).

=MINUTO(núm_série): Retorna os minutos de um valor de tempo. O minuto é dado como um número inteiro, que vai de 0 a 59.

=SEGUNDO(núm_série): Retorna os segundos de um valor de hora. O segundo é fornecido como um inteiro no intervalo de 0 (zero) a 59.

Figura 20 – Função Data e Hora no Excel

=NÚMSEMANA(núm_série,[retornar_tipo]). Retornar_tipo é um número que determina em que dia a semana começa, o valor padrão é 1.

Figura 23 – Função CONT.SE no Excel

1.6.8 Função CONT.SES

A função COUNT.SES é responsável por aplicar critérios a células em vários intervalos, e realiza a contagem do número de vezes que todos os critérios foram atendidos.

A sintaxe da função CONT.SES é composta pelos seguintes argumentos: =CONT.SES(intervalo_critérios1, critérios1, [intervalo_critérios2, critérios2]... ) onde,

• intervalo_critérios1: Intervalo obrigatório, sendo o primeiro intervalo no qual será avaliado os critérios associados;
• critérios1: Também um campo obrigatório. Os critérios em formato de número, ou uma expressão, uma referência de célula ou até mesmo um texto, que definirão quais células serão contadas;
• intervalo_critérios2, critérios2 : campo opcional.

Como exemplo prático, iremos utilizar novamente a nossa base de dados. Digite em qualquer célula não preenchida na planilha, a seguinte fórmula =CONT.SES(C1:C24;"=Murilo"). Este comando irá contar o número de células que contêm a palavra Murilo, de C1 a C24, o resultado deverá ser (4) quatro, como ilustrado na Figura 24.

Figura 24 – Função CONT.SES no Excel

1.6.9 Função SOMASE

A função SOMASE tem como objetivo somar os valores em um intervalo que atendem aos critérios especificado.

Por exemplo, suponha que em uma coluna que contenha números, e é necessário somar apenas os valores numéricos maiores que o valor 5. É possível através desta função utilizar a seguinte sintaxe de execução :  SOM ASEpB2 : B25; »5”q.

Podemos definir a sintaxe da função SOMASE da seguinte maneira, ilustrado abaixo: =SOMASE(intervalo; critérios; [intervalo_soma]) onde, seus argumentos possuem as seguintes características como demonstrado abaixo:

• intervalo: é um campo obrigatório. O intervalo de células que se deseja calcular por critérios estabelecidos. As células em cada intervalo devem ser números ou nomes, matrizes ou referências que contêm números. Espaços em branco e valores de texto são ignorados. O intervalo selecionado deve conter datas no formato padrão do Excel;
• critérios: é uma campo obrigatório. Os critérios são dados na forma de número, expressão, referência de célula, texto ou função que define quais células serão adicionadas;
• intervalo_soma: é um campo opcional. Sãs as células reais a serem adicionadas, caso seja necessário adicionar células diferentes das especificadas no argumento intervalo. Se