Apostila Excel 2013 Avançado Revisão 02 - 01-01-2016.pdf 5v535s

[email protected]

>= (maior ou igual a)

=A1>=B1

<= (menor ou igual a)

=A1<=B1

<> (diferente de)

=A1<>B1

& (E comercial)

="mal"&"sucedido" ou =soma(A1:B4)&“ horas” Concatena dois valores para produzir um valor de texto contínuo.

: (dois-pontos)

=SOMA(A2:A100) Operador de intervalo, que produz uma referência para todas as células entre duas referências, incluindo as duas referências

; (ponto e virgula)

Separador de argumentos. =SOMA(B1;B5;B9)

# (Sustenido/Cerquilha)

###### ou #VALOR! Exibido em sequência significa que o a célula é menor que o valor ou a formatação utilizada não é válida. Também pode vir junto a uma mensagem de erro de fórmula.

$ (Cifrão)

=$A$2*B2 ou =A$2*$B2 Utilizado para indicar que aquele endereço não se alterará quando for copiado para um novo endereço. Representa uma referência Absoluta, ou seja, uma referência fixa.

( ) (Parenteses)

=(100+50)*4 ou =Maior(A2:A10;3) ou (R$ 300.000,00) Representa "prioridade" de "resolução" em uma expressão algébrica. Separa os argumentos de uma função ou representa valores negativos no formato contábil.

[ ] (Colchetes)

[h]:mm - Utilizado para aplicar um formato personalizado a uma célula.

{ } (Chaves)

={A1:A4*B1:B4} Utilizados em fórmulas matriciais. Não pode ser digitado ele é inserido através das teclas CTRL+SHIFT+ENTER após a digitação da fórmula.

\ (Contra Barra)

=ÍNDICE({5\6; 7\8};0;2) - O operador \ é usado em fórmulas matriciais. neste caso, o ; (ponto e vírgula) é um separador de linha e, o operador \ (contra barra) é um separador de colunas.

Separa

os

argumentos

de

uma

função.

Sempre que você tiver alguma dúvida sobre uma função ou ferramenta do Excel utilize o botão de ajuda do Microsoft Excel para consultar maiores informações sobre a função e visualizar exemplos de aplicação das mesmas. Você Sabia?

 Ajuda do Microsoft Excel ou pressione a tecla F1.

Fone: 112084-0070

Reprodução Proibida

Pag.: 18

Microsoft Excel 2013 - Avançado

Cálculos Básicos Nesta etapa, será feita uma breve revisão e refinamento de como trabalhar em uma planilha com fórmulas básicas, além de apresentar as maneiras de se inserir fórmulas em uma célula. Também será revisado os conceitos básicos de células, intervalos, formatação e operadores.

Colunas, linhas, células e intervalos A área de trabalho de uma folha de cálculo (do Excel ou qualquer outra) é uma área formada pela interseção de linhas com designações numéricas (do lado esquerdo) e de colunas designadas por letras (no topo da folha).

A interseção de uma coluna com uma linha chama-se célula. As células são designadas pela conjunção do nome da coluna com o nome da linha. Numa planilha com milhares de células poderá ser difícil encontrar aquela que pretende. Para isso o Excel possui uma caixa de nome, onde pode digitar diretamente a célula para onde pretende ir (seguido de Enter). Por exemplo, a célula fruto da interseção da coluna B com a linha 4 será chamada “B4”.

Intervalo é um conjunto células. Ex: B1:B5 (Lê-se: De B1 até B5)

Inserindo fórmulas em uma célula Existem algumas maneiras diferentes de se inserir fórmulas no Excel: 

Usando o Auto Soma () para fazer somatórios;



Usando o sinal de igual (=) e inserindo a fórmula manualmente;



Usando o assistente de função ;

Fone: 112084-0070


Pag.: 19

Microsoft Excel 2013 - Avançado AutoSoma A AutoSoma é usado quando se é necessário fazer uma somatória de valores contidos em várias células. Por padrão, ao clicar no botão de  na barra de ferramentas, a fórmula de soma busca realizar a conta com as células adjacentes a ela, que contêm valores numéricos. Se o intervalo de células sugerido para o somatório estiver incorreto pode-se alterá-lo, bastando para tanto selecionar as células a serem somadas usando o procedimento normal de seleção de células.

Através do botão  podemos incluir outras funções além da AutoSoma. Para isto, em vez de clicar no botão  na barra de ferramentas, clique na seta al ao lado dele e escolha a função desejada.

Para fazer uma AutoSoma pode utilizar o botão de  ou a tecla de atalho ALT = Você Sabia?

Inserindo fórmulas manualmente Uma outra maneira de inserir fórmulas é usando o sinal de igual (=), depois digitando-se os endereços das células intercalados pelos sinais das operações a serem feitas. É uma maneira mais flexível de se montar qualquer fórmula, pois é aberta à inserção de valores, variáveis (endereços de células), e funções para a execução de qualquer cálculo.

Fone: 112084-0070


Pag.: 20


Referências de Células relativas e absolutas Um conceito que precisa ser bem entendido e fixado é o de referência de células relativas e absolutas. É bastante usado este conceito em fórmulas avançadas, bem como no uso da alça de preenchimento para se copiar fórmulas de uma célula para outras. Muitas vezes é necessário fixar o endereço de uma célula em certas fórmulas/funções. Para isto, usa-se endereços de células absolutas, isto é, quando se copia o conteúdo de uma célula para outra, certos endereços na fórmula não se devem alterar, precisam ser fixos, independente do endereço da célula de destino. Por outro lado, quando é necessário que os endereços contidos na fórmula sejam alterados de acordo com a nova posição, usam-se os endereços relativos.

Referência Relativa Uma referência relativa de uma célula em uma fórmula/função é simplesmente a combinação do endereço da coluna seguido do endereço da linha. Por exemplo: tem-se na célula D2 a seguinte fórmula: =B2*C2, nesta fórmula todos os endereços das células são relativos, pois quando se copia esta fórmula para a célula D3, esta tornar-se-á =B3*C3, observe que todos os endereços foram atualizados de acordo com a nova posição da fórmula e assim sucessivamente para as demais.

Fórmula e ou funções que possuem célula com endereços relativos, quando são copiadas para outras posições têm seus endereços alterados de acordo com a nova posição.

Referência Absoluta Uma referência absoluta de uma célula em uma fórmula/função possui na frente da letra da coluna e/ou na frente do número da linha, o símbolo de $ indicando que aquele endereço não se alterará quando for copiado para um novo endereço. Por exemplo: tem-se na célula E2 a seguinte fórmula: =D2/$D$8, nesta fórmula $D$8 indica que a coluna e a linha estão fixadas. Quando esta fórmula é copiada para a célula E3, ela é alterada para =D3/$D$8, observe que o endereço que possui o $ não foi atualizado, por outro lado, o que não possui, foi, de acordo com a nova posição da fórmula.

As referências em uma célula também podem ser Mistas quando utilizamos na mesma fórmulas referências Relativas e Absolutas como no exemplo anterior.

Fone: 112084-0070


Pag.: 21


As opções para fixação de uma célula podem ser as seguintes:

$C1

Colocar $ antes da letra da coluna para fixar apenas a coluna quando tiver arrastando uma fórmula no sentido horizontal. Neste exemplo somente a coluna C esta fixa.

C$1

Colocar $ antes do número da linha para fixar apenas a linha quando tiver arrastando uma fórmula no sentido vertical. Neste exemplo somente a linha 1 esta fixa.

$C$1

Colocar $ antes da letra da coluna e antes do número da linha para fixar tanto linha quanto coluna quando tiver arrastando uma fórmula no sentido horizontal e também no vertical. Neste exemplo toda a célula C1 esta fixa.

Você Sabia?

Fórmula/ funções que possuem células com endereços absolutos, quando são copiadas para outras posições, têm os endereços das células com $ inalterados. A tecla de Atalho F4 permite alternar entre as 3 situações previstas, linha, coluna ou ambas.

Opções de Cálculos Em algumas situações como por exemplo, planilhas muito grande com diversos cálculos, as mesma podem demorar para processar os dados, pois por padrão todas as vezes que alteramos o conteúdo de alguma célula o Excel recalcula toda a planilha automaticamente o que pode levar um certo tempo dependendo do tamanho da planilha.

Para resolver este problema podemos alterar o modo de cálculo de Automático para Manual. Para isso clique na Fórmulas e no grupo Cálculo clique no botão Opções de Cálculo e selecione a opção desejada.

Se a opção selecionada for a Manual lembre-se que quando for feita a alteração no valor de alguma célula da planilha a mesma não atualizará os resultados nas fórmulas automaticamente, você precisara clicar no comando Calcular Agora que se encontra na guia Fórmulas e no grupo Cálculo.

A quantidade do mouse é 15 e o total R$ 180,00

Você Sabia?

Se a quantidade do mouse for alterada para 30 o total continuará sendo R$ 180,00 pois a Opção de Cálculo foi definida como Manual e nesse caso precisávamos ter clicado no comando Calcular Agora.

Se preferir pode utilizar as seguintes teclas de atalho para atualizar os dados: F9  Calcula a Pasta de Trabalho Inteira Shift + F9  Calcula apenas a Planilha Atual (Ativa)

Fone: 112084-0070


Pag.: 22


Conceito de Funções Uma função é um procedimento de cálculo previamente definidos, que determina um resultado. Assim como todo cálculo deve ser iniciada com “=” e de ser seguida de um ou mais parâmetros. = Nome da Função ( Argumentos ) Os argumentos podem ser números, texto, valores lógicos, como VERDADEIRO ou FALSO, matrizes, valores de erro como #N/D ou referências de célula Como dito anteriormente as funções são procedimentos de cálculos pré-definidos que determinam um resultado. As funções podem realizar um cálculo especifico ou simplificar uma fórmula como no exemplo abaixo onde foi simplificada a fórmula da soma das células pela função:

Assistente de Função  Através do assistente de função, insere-se funções com auxílio o a o. Pode-se escolher uma função específica que exista dentro do Excel, inserindo, quando requisitado pelo assistente, apenas os endereços das células que contêm os valores a serem usados na função, ou então os valores numéricos propriamente ditos. Para utilizar o assistente de funções clique na guia Fórmulas e em seguida clique no botão Inserir Função ou através do botão Inserir Função da Barra de Fórmulas.

As funções também podem ser digitadas diretamente na célula. Durante a digitação da função, um pequeno quadro de auxilio para a sintaxe do nome da função poderá ser exibido. Para completar a digitação do nome da função, poderá clicar sobre o nome correspondente, inserindo-o corretamente na célula.

Pode-se também utilizar as setas de direção para selecionar o nome da função e pressionar a tecla TAB para completar a digitação. Você Sabia?

Fone: 112084-0070


Pag.: 23

Microsoft Excel 2013 - Avançado Funções Básicas Função SOMA

Descrição Soma todos os números das células mencionadas.

MÁXIMO

Retorna o maior valor dentre as células mencionadas.

=MÁXIMO(célula1; célula2; ...) =MÁXIMO(célulainicial:célulafinal)

MÍN

Retorna o menor valor dentre as células mencionadas.

=MÍN(célula1; célula2; ...) =MÍN(célulainicial:célulafinal)

MÉDIA

Retorna a média aritmética dos valores.

=MÉDIA(célula1; célula2; ...) =MÉDIA(célulainicial:célulafinal)

MAIOR

Retorna dentro de um intervalo de dados o maior valor através de um critério especificado na função.

MENOR

Retorna dentro de um intervalo de dados o menor valor através de um critério especificado na função.

CONT.NÚM

Conta quantas células contêm números e também os números na lista de argumentos.

Exemplo =SOMA(célula1; célula2; ...) =SOMA(célulainicial:célulafinal)

=MAIOR(Intervalo;Critério) Critério: Valor a ser retornado 1, 2 , 3... =MENOR(Intervalo;Critério) Critério: Valor a ser retornado 1, 2 , 3... =CONT.NÚM(intervalo)

Calcula o número de células não vazias e CONT.VALORES os valores na lista de argumentos.

=CONT.VALORES(Intervalo)

CONTAR.VAZIO

Conta o número de células vazias no intervalo especificado.

=CONTAR.VAZIO(intervalo)

HOJE

Retorna a data atual. Esta função fará com que cada vez que você abrir o arquivo a data venha atualizada, você sempre terá o dia correto.

=HOJE( )

AGORA

Retorna a data e hora atual. Esta função fará com que cada vez que você abrir o arquivo a data venha atualizada.

=AGORA( )

INT

Arredonda o valor para baixo até o número inteiro mais próximo.

=INT(Número) =IINT(Fórmula)

ARRED

Arredonda um número até uma quantidade especificada de dígitos.

=ARRED(Número;Nº de dígitos) =ARRED(Fórmula;Nº de dígitos)

TRUNC

Omite a parte fracionária de um número de acordo com quantidade especificada de dígitos.

=TRUNC(Número;Nº de dígitos) =TRUNC(Fórmula;Nº de dígitos)

MOD

Retorna o resto depois de uma divisão de um número por um divisor. O resultado possui o mesmo sinal do divisor.

=MOD(Número;Divisor)

ALEATÓRIO

Retorna um número aleatório entre 0 e 1. Um novo número é retornado toda vez que a planilha é calculada.

=ALEATÓRIO( )

Algumas funções sofrerão alterações na versão 2013 com relação as versões anteriores. As mesmas foram substituídas ou extintas. Exemplos: A função TRUNCAR das versões anteriores foi substituída por TRUNC na versão atual o mesmo aconteceu com função MÍNIMO que foi abreviada para MÍN na versão atual.

Fone: 112084-0070


Pag.: 24


Repetir linhas e/ou colunas em várias páginas Muitas vezes é necessário repetirmos ou uma linha ou uma coluna em todas as páginas para isso clique na guia Layout de Página, grupo Configurar Página, clique no botão Imprimir Títulos e a janela a seguir será exibida.

Na janela Configurar Página , na guia Planilha, vá até a seção Linhas a repetir na parte superior e clicando no botão de seleção (botão recolher), selecione a linha 3. Em seguida, feche a janela de seleção e clique no botão OK.

Selecione Colunas a repetir à esquerda se desejar usar títulos verticais em cada página. O botão Recolher caixa de diálogo na extremidade direita dessa caixa move temporariamente a caixa de diálogo para que você possa inserir o intervalo selecionando células na planilha. Quando você terminar, clique no botão novamente para exibir a caixa de diálogo inteira. As linhas e/ou colunas serão repetidas em todas as páginas da planilha. Abaixo as demais opções desta janela: Área de Impressão - Clique na caixa Imprimir área para selecionar um intervalo de planilha para imprimir e, em seguida, arraste pelas áreas da planilha que você deseja imprimir. Imprimir: Linhas de Grade - Marque a caixa de seleção Linhas de grade para imprimir linhas de grade de célula verticais e horizontais nas planilhas.

Fone: 112084-0070


Pag.: 25

Microsoft Excel 2013 - Avançado Preto e branco - Marque a caixa de seleção Preto e branco se tiver formatado dados com cores mas estiver utilizando uma impressora monocromática. Se você estiver usando uma impressora colorida, a seleção dessa opção pode reduzir o tempo de impressão. Qualidade Rascunho – Marque a caixa de seleção Qualidade rascunho para reduzir o tempo de impressão. Quando essa opção é selecionada, o Microsoft Excel não imprime nem as linhas de grade nem a maior parte dos elementos gráficos. Títulos de linha e coluna – Marque a caixa de seleção Cabeçalhos de linha e coluna para imprimir números de linha e letras de coluna em um estilo de referência A1 ou linhas e colunas numeradas em um estilo de referência R1C1. Ordem da página – Clique em Abaixo e à direita ou Acima e abaixo para controlar a ordem na qual os dados são numerados e impressos quando não couberem em uma página. A figura de exemplo mostra a direção na qual seu documento será impresso quando você selecionar uma dessas opções. Opções – Clique em Opções para definir opções específicas para a impressora selecionada. Para obter maiores informações, consulte a documentação da sua impressora.

Congelar Painéis Com o recurso de Congelar Painéis é possível manter os rótulos de linhas e colunas visíveis enquanto rola. 

Para congelar o horizontal superior, selecione a linha abaixo do lugar onde você deseja que apareça a divisão.



Para congelar o vertical esquerdo, selecione a coluna à direita do lugar onde você deseja que apareça a divisão.



Para congelar os painéis superior e esquerdo, clique na célula abaixo e à direita do lugar onde você deseja que apareça a divisão.

Na guia Exibição, no grupo Janela, clique no botão Congelar Painéis.

Veja que você tem as opções Congelar Painéis para manter as linhas e as colunas visíveis enquanto faz a rolagem, Congelar Linha Superior para manter somente a linha Superior visível e Congelar Primeira Coluna para manter a primeira coluna visível enquanto rola. No exemplo abaixo serão congeladas a coluna A e a linha 3. Para isso devemos posicionar o cursor na célula B4. Para descongelar clique na guia Exibição, no grupo Janela e clique Congelar Painéis e em seguida, no comando Descongelar painéis.

Fone: 112084-0070


Pag.: 26


Exibir duas partes de uma planilha ao mesmo tempo Posicione o cursor no local onde deseja dividir a janela e em seguida, na Guia Exibição, no grupo Janela, Clique no botão Dividir. ou Na parte superior da barra de rolagem vertical ou na extremidade direita da barra de rolagem horizontal, aponte para o botão de divisão.

Quando o ponteiro se tornar um ponteiro de divisão, arraste a caixa de divisão abaixo ou à esquerda para a posição desejada. O exemplo abaixo mostra uma janela dividida:

Para voltar ao normal clique na Guia Exibição, no grupo Janela, Clique no botão Dividir. Se a janela estiver dividida, este o irá remover a divisão.

Para fixar o conteúdo da REVISÃO e tirar possíveis dúvidas realize os exercícios complementares que se encontram no final do material e em casos de dúvidas você pode tirar com seu instrutor durante o treinamento ou através do e-mail: [email protected]

Fone: 112084-0070


Pag.: 27


Fone: 112084-0070


Pag.: 28


AULA 01 Trabalhando com Nomes de Células Você pode dar o nome que quiser a uma ou mais células. Selecione a área desejada, clique na Caixa de Nome, digite um nome para a célula ou intervalo e termine pressionando a tecla Enter.

Cada nome registrado a a fazer parte de uma lista na Caixa de Nome, localizada à esquerda da barra de fórmulas. Para selecionar a área correspondente, basta escolher o nome nessa lista. Para editar, excluir ou visualizar nomes de uma Pastra de Trabalho clique na guia Fórmulas e no grupo Nomes Definidos clique na opção Gerenciador de Nomes.

Na janela que se abre surgirão todos os nomes utilizados na Pasta de trabalho. Basta selecionar o nome que deseja alterar ou excluir e acionar o comando correspondente.

Para abrir o Gerenciador de Nomes pode ser utilizada a tecla de atalho CTRL + F3. Você Sabia?

Fone: 112084-0070


Pag.: 29


Listas Criando Listas a partir de Dados da Planilha Listas personalizadas, são listas que irão preencher automaticamente as células quando utilizarmos o recurso de cópia através da alça de preenchimento da célula. O Excel já possui listas automáticas como as exemplificadas abaixo:

+ Para acioná-las um dos itens da lista (no exemplo acima as células que estão na linha 1) selecione o 1º item da lista e arraste com a alça de preenchimento da esquerda para direita ou de cima para baixo.

Listas Personalizadas Para criar uma lista personalizada, siga os os abaixo : 1. Digite na planilha as informações que farão parte da lista, conforme exemplo ao lado e selecione a lista. 2. Clique no Menu Arquivo e clique em Opções para abrir a janela “Opções do Excel” . 3. Na coluna da esquerda, clique em Avançado. 4. Na seção Geral, clique no botão Editar Listas Personalizadas...

5. Verifique se o intervalo do seu novo conteúdo está selecionado. Se estiver, clique no botão Importar e depois clique em OK.

Fone: 112084-0070


Pag.: 30


6. Clique em OK novamente para fechar a janela Opções do Excel. A lista irá aparecer na caixa Entradas da Lista. Toda vez que você digitar uma palavra da lista em uma célula e copiar a mesma através da alça de preenchimento, as células seguintes serão automaticamente preenchidas com a sequência cadastrada.

Preenchimentos Automáticos Para gerar preenchimentos automáticos de data ou valores siga uma das dicas abaixo: Em uma célula digite a data inicial ou a hora e pela alça de preenchimento arraste a célula até a área que deseja preencher assim a lista será preenchida com a sequencia para as data será com o intervalo de 1 em 1 dia e para as horas será de 01:00 em 01:00.

Para as datas se preferir podemos arrastar a alça de preenchimento com o botão direito onde teremos as opções Preencher dias, Preencher dias da semana, Preencher Meses ou Preencher anos.

Pode-se também arrastar a alça de preenchimento com o botão esquerdo do mouse e ao soltar aparecerá no final da lista um botão de preenchimento contendo as opções acima de preenchimento.

Fone: 112084-0070


Pag.: 31


Para sequencias de horas, datas ou números com um intervalo especifico devemos digitar o primeiro e segundo valor , selecionar os 2 valores e arrastar a alça de preenchimento para que o Excel entenda o intervalo desejado.

O preenchimento automático também funciona com palavras seguidas de números.

Você Sabia?

Você Sabia?

Você também pode mudar o intervalo de tempo por exemplo digitar a primeira data e outra com 7 dias a diante e arrastar o intervalo para ter uma sequência de datas com intervalos de 7 dias.

É possível utilizar o autopreenchimento do Excel para digitar uma palavra já existente em um intervalo consecutivo de células. Basta para isso pressionar a tecla ALT +  (Seta para baixo) na célula seguinte a lista e selecionar um item da mesma:

Fone: 112084-0070


Pag.: 32


Funções de Data e Hora O Excel armazena datas como números de série sequenciais para que eles possam ser usados em cálculos. Por padrão, 1° de janeiro de 1900 é o número de série 1 e 1° de Maio de 2011 é o número de série 40.664 porque está 40.664 dias após 1° de janeiro de 1900. Já quando trabalhamos com horas no Excel o mesmo entende que 01:00 é o mesmo que 1/24 do dia, ou seja, internamente, no Excel, 1 hora corresponde a 0,041666... Dessa forma o Excel expressa a hora como uma fração decimal de um dia de 24 horas, obtendo um número entre 0 e 1. Por exemplo para meia-noite é atribuído o valor 0, de forma que meio-dia será 0,5.

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 Microsoft Excel para cálculos de data e hora. Se o formato da célula era Geral antes de a função ser inserida, o resultado será formatado como uma data. =HOJE( )

AGORA Retorna o número de série sequencial da data e hora atuais. Se o formato da célula era Geral antes de a função ser inserida, o resultado será formatado como uma data. =AGORA( )

DATA Retorna o número de série sequencial que representa uma determinada data. Se o formato da célula era Geral antes de a função ser inserida, o resultado será formatado como uma data. =DATA(ano;mês;dia)

DIA 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. =DIA(núm_série) Núm_série é a data do dia que você está tentando encontrar. As datas devem ser inseridas com a função DATA ou como resultado de outras fórmulas ou funções.

Fone: 112084-0070


Pag.: 33

Microsoft Excel 2013 - Avançado MÊS 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). =MÊS(núm_série) Núm_série é a data do mês que você está tentando encontrar. As datas devem ser inseridas com a função DATA ou como resultado de outras fórmulas ou funções.

ANO Retorna o ano correspondente a uma data. O ano é retornado como um inteiro no intervalo de 19009999. =ANO(núm_série) Núm_série é a data do ano que você deseja localizar. As datas devem ser inseridas com a função DATA ou como resultado de outras fórmulas ou funções.

DIA.DA.SEMANA Retorna o dia da semana correspondente a uma data. O dia é dado como um inteiro, variando de 1 (domingo) a 7 (sábado), por padrão. =DIA.DA.SEMANA(núm_série,[retornar_tipo]) Núm_série - Obrigatório. Um número sequencial que representa a data do dia que você está tentando encontrar. As datas devem ser inseridas com a função DATA ou como resultado de outras fórmulas ou funções. Retornar_tipo - Opcional. Um número que determina o tipo do valor retornado. Retornar_tipo

Número retornado

1 ou omitido

Números 1 (domingo) a 7 (sábado). Como nas versões anteriores do Microsoft Excel.

2

Números 1 (segunda-feira) a 7 (domingo).

3


11


12

Números 1 (terça-feira) a 7 (segunda-feira).

13

Números 1 (quarta-feira) a 7 (terça-feira).

14

Números 1 (quinta-feira) a 7 (quarta-feira).

15

Números 1 (sexta-feira) a 7 (quinta-feira).

16

Números 1 (sábado) a 7 (sexta-feira).

17

Números 1 (domingo) a 7 (sábado).

No Exemplo acima como utilizamos o padrão da função o 1 (dom), 4 (qua) e 7 (sáb).

Fone: 112084-0070


Pag.: 34

Microsoft Excel 2013 - Avançado DIAS360 Retorna o número de dias entre duas datas com base em um 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. = DIAS360(data_inicial;data_final;[método]) Data_inicial, data_final - Obrigatório. As duas datas entre as quais você deseja saber o número de dias. Se data_inicial ocorrer depois de data_final, a função DIAS360 retornará um número negativo. As datas devem ser inseridas com o uso da função DATA, ou devem ser derivadas dos resultados de outras fórmulas ou funções. Método - Opcional. Um valor lógico que especifica o método a ser usado cálculo, o americano ou o europeu. Método

Definido

FALSO ou omitido

Método americano (NASD). Se a data inicial for o último dia do um mês, ela se tornará igual ao dia 30 do mesmo mês. Se a data final for o último dia do mês e a data inicial for anterior ao trigésimo dia de um mês, a data final se tornará igual ao dia primeiro do próximo mês. Caso contrário, a data final se tornará igual ao trigésimo dia do mesmo mês.

VERDADEIRO

Método europeu. A data inicial e final que ocorrer no dia 31 de um mês se tornará igual ao trigésimo dia do mesmo mês.

NÚMSEMANA Retorna o número da semana de uma data específica. Por exemplo, a semana que contém 1 de janeiro é a primeira semana do ano e é numerada semana 1. Há dois sistemas usados para essa função: Sistema 1 - A semana que contém 1 de janeiro é a primeira semana do ano e é numerada semana 1. Sistema 2 - A semana que contém a primeira quinta-feira do ano é a primeira semana do ano e é numerada como semana 1. Este sistema é a metodologia especificada na norma ISO 8601, normalmente conhecida como sistema europeu de numeração de semanas. =NÚMSEMANA(núm_série,[tipo_retorno]) A sintaxe da função NÚMSEMANA tem os seguintes argumentos: Núm_série - Obrigatório. Uma data na semana. As datas devem ser inseridas com a função DATA ou como resultado de outras fórmulas ou funções. Tipo_retorno Opcional. É um número que determina em que dia a semana começa. O valor padrão é 1.

Fone: 112084-0070

Retornar_tipo

A semana começa em

Sistema

1 ou omitido

Domingo

1

2

Segunda-feira

1

11

Segunda-feira

1

12

Terça-feira

1

13

Quarta-feira

1


Pag.: 35

Microsoft Excel 2013 - Avançado Retornar_tipo

A semana começa em

Sistema

14

Quinta-feira

1

15

Sexta-feira

1

16

Sábado

1

17

Domingo

1

21

Segunda-feira

2

DATAM 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. =DATAM(data_inicial;meses) A sintaxe da função DATAM tem os seguintes argumentos: Data_inicial - Obrigatório. Uma data que representa a data inicial. As datas devem ser inseridas com a função DATA ou como resultado de outras fórmulas ou funções. Meses - Obrigatório. O número de meses antes ou depois de data_inicial. Um valor positivo para meses gera uma data futura; um valor negativo gera uma data ada.

FIMMÊS Retorna o último dia do mês antes ou depois de um dado número de meses. A sintaxe da função FIMMÊS tem os seguintes argumentos: Data_inicial - Obrigatório. Uma data que representa a data inicial. As datas devem ser inseridas com a função DATA ou como resultado de outras fórmulas ou funções. Meses - Obrigatório. O número de meses antes ou depois de data_inicial. Um valor positivo para meses gera uma data futura; um valor negativo gera uma data ada.

Fone: 112084-0070


Pag.: 36

Microsoft Excel 2013 - Avançado DATA.VALOR A função DATA.VALOR converte uma data armazenada como texto em um número de série que o Excel reconhece como data. Por exemplo, a fórmula =DATA.VALOR("1/1/2008") retorna 39448, o número de série da data 1/1/2008. OBS: O número de série retornado pela função DATA.VALOR pode ser diferente do exemplo anterior, dependendo das configurações de data do seu computador. A função DATA.VALOR é útil nos casos em que uma planilha contém datas em um formato de texto que você deseja filtrar, classificar ou formatar como datas ou usar em cálculos de data. =DATA.VALOR(texto_data) Texto_data - Obrigatório. Texto que representa uma data em um formato de data do Excel ou uma referência a uma célula que contém texto representando uma data em um formato de data do Excel. Por exemplo "1/30/2008" ou "30-Jan-2008" são cadeias de texto entre aspas que representam datas.

DATADIF A função DATADIF é uma função não documentada do Excel, ou seja, ela existe e funciona muito bem. No entanto ela não faz parte da documentação do Excel. Esta função é muito útil para o calculo de idades e tempo de serviço. =DATADIF(Data_Inicial; Data_Final; “Tipo”) Onde Tipo pode ser: Tipo "Y"

Retorna O número de anos completos no período.

"M"

O número de meses completos no período.

"D"

O número de dias no período.

"MD" (exemplo DtNasc 15/12/2000) Retorna a quantidade de dias no último mês (após o último dia 15). "YM" Retorna a quantidade de meses decorridos após o último aniversário. "YD"

Retorna a quantidade de dias decorridos após o último aniversário.

Usando a fórmula:

Você ira obter: 25 anos, 5 meses e 16 dias.

Fone: 112084-0070


Pag.: 37

Microsoft Excel 2013 - Avançado DIATRABALHOTOTAL Retorna o número de dias úteis inteiros entre data_inicial e data_final. Os dias úteis excluem os fins de semana e quaisquer datas identificadas em feriados. Use DIATRABALHOTOTAL para calcular os benefícios aos empregados que recebem com base no número de dias trabalhados durante um período específico. =DIATRABALHOTOTAL(data_inicial;data_final;feriados) A sintaxe da função DIATRABALHOTOTAL tem os seguintes argumentos (argumento: um valor que fornece informações a uma ação, um evento, um método, uma função ou um procedimento.): Data_inicial Obrigatório. Uma data que representa a data inicial. Data_final Obrigatório. A data que representa a data final. Feriados - Opcional. Um intervalo opcional de uma ou mais datas a serem excluídas do calendário de dias de trabalho, como feriados estaduais e federais, e feriados móveis. A lista pode ser um intervalo de células que contém as datas. Em nosso exemplo o intervalo de F2:F16 que contem as datas dos feriados nacionais e regionais foi renomeado para “Feriados” para facilitar a utilização do mesmo na função.

DIATRABALHO Os dias úteis excluem fins de semana e quaisquer 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 executado. =DIATRABALHO(data_inicial;dias;feriados) A sintaxe da função DIATRABALHO tem os seguintes argumentos: Data_inicial - Obrigatório. Uma data que representa a data inicial. Dias - Obrigatório. O número de dias úteis antes ou depois de data_inicial. Um valor positivo para gera uma data futura; um valor negativo gera uma data ada. Feriados - Opcional. Uma lista opcional com uma ou mais datas a serem excluídas do calendário de trabalho, como feriados estaduais, federais e flutuantes. A lista pode ser um intervalo de células que contém as datas. Em nosso exemplo o intervalo de F2:F16 que contem as datas dos feriados nacionais e regionais foi renomeado para “Feriados” para facilitar a utilização do mesmo na função.

Fone: 112084-0070


Pag.: 38

Microsoft Excel 2013 - Avançado HORA 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.). =HORA(núm_série) Núm_série - Obrigatório. O horário que contém a hora que você deseja encontrar. Os horários podem ser inseridos como sequências de caracteres de texto entre aspas (por exemplo, "6:45 PM"), como números decimais (por exemplo, 0,78125, que representa 6:45 PM) ou como resultados de outras fórmulas ou funções (por exemplo, VALOR.TEMPO("6:45 PM")).

MINUTO Retorna os minutos de um valor de tempo. O minuto é dado como um número inteiro, que vai de 0 a 59. =MINUTO(núm_série) Núm_série - Obrigatório. O horário que contém a hora que você deseja encontrar. Os horários podem ser inseridos como sequências de caracteres de texto entre aspas (por exemplo, "6:45 PM"), como números decimais (por exemplo, 0,78125, que representa 6:45 PM) ou como resultados de outras fórmulas ou funções (por exemplo, VALOR.TEMPO("6:45 PM")).

SEGUNDO Retorna os segundos de um valor de hora. O segundo é fornecido como um inteiro no intervalo de 0 a 59. =SEGUNDO(núm_série) Núm_série - Obrigatório. O horário que contém a hora que você deseja encontrar. Os horários podem ser inseridos como sequências de caracteres de texto entre aspas (por exemplo, "6:45 PM"), como números decimais (por exemplo, 0,78125, que representa 6:45 PM) ou como resultados de outras fórmulas ou funções (por exemplo, VALOR.TEMPO("6:45 PM")).

Para inserir a data ou a hora atual em uma célula podemos utilizar as teclas de atalho:

Você Sabia?

Ctrl + ;  Inseri a data atual do sistema. Ctrl +Shift + :  Inseri a hora atual do sistema.

Fone: 112084-0070


Pag.: 39


Se algumas funções como DIATRABALHOTOTAL e DIATRABALHO não estiverem disponíveis e retornarem o erro #NOME?, instale e carregue o suplemento Ferramentas de análise. Para isto, faça o seguinte: 1. Clique no Menu Arquivo e em seguida, em Opções. 2. Na janela Opções do Excel, clique em Suplementos. 3. Na opção Gerenciar e clique no botão Ir. 4. Na janela Suplementos marque a opção Ferramentas de Análise e clique em OK.

Práticas com Funções de Data e Hora 

Na célula A2 utilizar a função HOJE para obter a data atual.



Na célula C2 utilizar a função AGORA para obter a data e a hora atual.



A Data Inicial e a Data Final são digitadas.



Na coluna feriados digitar todos os feriados, recessos e datas não trabalhadas (exceto sábados e domingos que tem a necessidade de digitar).



Selecione o intervalo das datas (Feriados) no exemplo abaixo E2:E16 e nomeie para FERIADOS através da Caixa de nomes.



As demais funções utilizar as respectivas funções para obter os resultados correspondentes.

OBS: Os resultados obtidos podem variar uma vez que estamos utilizando em muitas das funções acima a data atual.

Fone: 112084-0070


Pag.: 40


Fazendo cálculos com Horas Relembrando: quando trabalhamos com horas no Excel o mesmo entende que 01:00 é o mesmo que 1/24 do dia, ou seja, internamente, no Excel, 1 hora corresponde a 0,041666... Dessa forma o Excel expressa a hora como uma fração decimal de um dia de 24 horas, obtendo um número entre 0 e 1. Por exemplo para meia-noite é atribuído o valor 0, de forma que meio-dia será 0,5.

Na Célula D2 foi realizado o cálculo das horas trabalhadas com a fórmula =C2-B2 onde C2 é a hora da saída e B2 a hora de entrada. Após foi copiada a fórmula para as demais células. A célula E12 irá armazenar o total de horas da coluna. Foi utilizado o botão Autosoma para obter o resultado igual a 15:25. Está evidente que este valor está “errado”. No entanto, o que está acontecendo é apenas uma questão de formatação pois para o Excel, o dia só tem 24:00 horas portanto, em sua formatação padrão, a cada 24:00 hs, ele começa a contar novamente. Este resultado então será corrigido simplesmente trocando a formatação. Para isto, clique na Página Inicial, no grupo Número, clique no botão expansão para abrir a janela Formatar Células. Clique na categoria Personalizado e digite no campo Tipo o formato [h]:mm para exibir o total das horas e clique em OK.

OBS: Na janela de Formatar células na categoria Hora possui um formato que pode ser utilizado para esta situação basta selecionar o formato 37:30:55.

Fone: 112084-0070


Pag.: 41

Microsoft Excel 2013 - Avançado Para calcularmos o Valor a Pagar podemos utilizar duas fórmulas como a seguir: = (HORA(D2)+MINUTO(D2)/60)*E2

=D2*E2*24

Função MOD MOD Retorna o resto depois da divisão de um número por um divisor. O resultado possui o mesmo sinal que divisor. =MOD(núm;divisor) Núm é o número ou a célula para o qual você deseja encontrar o resto. Divisor é o número pelo qual você deseja dividir o número. Se divisor for 0, Resto retornará o valor de erro #DIV/0!. Exemplos: =Mod(3; 2) é igual a 1

=Mod(-3; 2) é igual a 1

=Mod(3; -2) é igual a -1

Situação 1 Temos uma quantidade total de peças para a montagem de alguns Kits. Sabemos a quantidade de peças por Kit. Usando a fórmula =INT(A2/B2) temos a quantidade de Kits completos que conseguiremos montar. OBS: A função INT serve para arredontar um valor para o Inteiro mais próximo para menos. Agora utilizando a função MOD conseguiremos saber se haverá alguma sobre de peças ou se a quantidade é exata. Para isso utilizamos a fórmula =MOD(A2;B2).

Situação 2 Outro exemplo, é o caso de um funcionário que entra as 22:00 da noite e sai as 06:00 da manhã do dia seguinte. O que fazer para calcular as horas trabalhadas? Se você fizer =Saída – Entrada, o resultado será negativo em alguns casos e, no sistema de datas 1900 o Excel não exibe horas negativas. Vide figura abaixo:

Uma forma para efetuar este cálculo é usando a função MOD da seguinte forma: =MOD(C2-B2;1)

Fone: 112084-0070


Pag.: 42

Microsoft Excel 2013 - Avançado Onde o número 1 representa um intervalo de 24:00 e quando o MOD faz a subtração e divide por 1 ele mantêm o resultado com o sinal do divisor, nesse caso o valor virá positivo.

Para o Excel, 1 hora é 1/24 avos do dia, ou seja, internamente, no Excel, 1 hora corresponde a 0,041666. Por isso é que conseguimos colocar data e hora em uma única célula. Isto quer dizer que a data corresponde à parte inteira e a hora à parte decimal do número.

Comformatação – Tipo Data/Hora

Sem formatação – Tipo Geral

Exemplo: Um equipamento entrou em operação em 15/05/2011 22/05/2011 17:35. Quantas horas o equipamento trabalhou?

08:15

e,

saiu

de

operação

em

Para resolver o problema acima iremos fazer um calculo simples que será o Término-Início. O resultado será 7,388888889 onde a parte inteira são os dias de funcionamento e a parte decimal as horas de funcionamento.

Este valor pode ser apresentado de diversas formas, bastando para isso aplicar uma Formatação Personalizado na célula conforme exemplos abaixo. Para aplicar a formatação, clique na Página Inicial, no grupo Número, clique no botão expansão para abrir a janela Formatar Células. Clique na categoria Personalizado e digite no campo Tipo o formato desejado para a célula e clique em OK.

Fone: 112084-0070


Pag.: 43


Funções Lógicas SE Esta é uma função lógica que retorna um valor baseado no teste lógico. A função SE retornará um valor se uma condição que você especificou for considerada VERDADEIRO e um outro valor se essa condição for considerada FALSO. Por exemplo, a fórmula =SE(A1>10;"Mais que 10";"10 ou menos") retornará "Mais que 10" se A1 for maior que 10 e "10 ou menos" se A1 for menor que ou igual a 10. =SE(teste_lógico; valor_se_verdadeiro; valor_se falso) teste_lógico - Obrigatório. Qualquer valor ou expressão que possa ser avaliado como VERDADEIRO ou FALSO. Por exemplo, A10=100 é uma expressão lógica; se o valor da célula A10 for igual a 100, a expressão será considerada VERDADEIRO. Caso contrário, a expressão será considerada FALSO. Esse argumento pode usar qualquer operador de cálculo de comparação. valor_se_verdadeiro - Opcional. O valor que você deseja que seja retornado se o argumento teste_lógico for considerado VERDADEIRO. Por exemplo, se o valor desse argumento for um texto como "Dentro do orçamento" e o argumento teste_lógico for considerado VERDADEIRO, a função SE retornará o texto "Dentro do orçamento". Se teste_lógico for considerado VERDADEIRO e o argumento valor_se_verdadeiro for omitido (ou seja, há apenas um ponto e vírgula depois do argumento teste_lógico), a função SE retornará 0 (zero). Para exibir a palavra VERDADEIRO, use o valor lógico VERDADEIRO para o argumento valor_se_verdadeiro. valor_se_falso - Opcional. O valor que você deseja que seja retornado se o argumento teste_lógico for considerado FALSO. Por exemplo, se o valor desse argumento for a um texto como "Acima do orçamento" e o argumento teste_lógico for considerado FALSO, a função SE retornará o texto "Acima do orçamento". Se teste_lógico for considerado FALSO e o argumento valor_se_falso for omitido (ou seja, não há vírgula depois do argumento valor_se_verdadeiro), a função SE retornará o valor lógico FALSO. Se teste_lógico for considerado FALSO e o valor do argumento valor_se_falso for omitido (ou seja, na função SE, não há vírgula depois do argumento valor_se_verdadeiro), a função SE retornará o valor 0 (zero).

Executando a função SE via assistente: Utilizaremos o exemplo a seguir para calcular o resultado que o aluno teve, caso sua Média Final atenda a um determinado critério, ou seja, se a Média Final for inferior a 7 será exibida a mensagem “Reprovado” caso contrário será exibida a mensagem “Aprovado”. F2<7

"Reprovado"

"Aprovado"

Lembre-se de calcular a Média Final na célula F2 e copiar para as demais células. =MÉDIA(B2:E2)

Fone: 112084-0070


Pag.: 44


Você Sabia?

O Assistente de funções pode ser utilizado para quaisquer funções do Excel. Nele podemos ver uma lista de todas as funções do Excel, uma breve descrição sobre as mesmas e a sintaxe da função. Caso necessite pode solicitar ajuda mais detalhada clicando na opção “Ajuda sobre esta função”.

Coloque o cursor na célula que deve receber o resultado da fórmula. Clique no botão  (Inserir Função) da barra de fórmulas para abrir o Assistente de função ou clique na guia Fórmulas, no grupo Biblioteca de Funções e clique no botão Inserir Função. Será exibida a janela a seguir:

Na lista “selecione uma categoria” escolha Lógico; Na lista “Selecione uma Função” escolha a função SE; Clique no botão OK.A janela a seguir será exibida:

No campo Teste_lógico insira o teste/comparação que deve ser feito, como por exemplo da média ser inferior a 7. No nosso caso a planilha exemplo o teste fica =F2<7; No campo Valor_se_verdadeiro coloque o valor a ser retornado se o teste lógico retornar verdadeiro por exemplo “Reprovado”;

Fone: 112084-0070


Pag.: 45

Microsoft Excel 2013 - Avançado No campo Valor_se_falso coloque o valor a ser retornado se o teste lógico retornar falso por exemplo “Aprovado”; Clique em OK e use a alça de preenchimento para copiar a fórmula para as outras células necessárias.

Ao clicar na célula G2 podemos observar a fórmula utilizada através da Barra de Fórmulas: =SE(F2<7;"Reprovado";"Aprovado")

Em uma função quando um texto for utilizado para o teste lógico, valor_se_verdadeiro, valor_se_falso ou critério o mesmo deverá vir entre aspas para que o Excel entenda como texto. Caso contrario o mesmo poderá interpretar como uma função ou um nome de uma referência.

Aninhamento da Função SE A função lógica SE pode ser aninhada de forma que uma das respostas seja uma nova função SE. Utilizaremos o exemplo anterior para calcular o resultado que o aluno teve neste caso teremos uma terceira condição, ou seja, além do “Aprovado” e “Reprovado” o aluno desta hipótese poderá ter o resultado “Recuperação”. Os critérios serão: Se a Média Final for superior ou igual a 7 será exibida a mensagem “Aprovado”; se a Média Final for inferior a 4 será exibida a mensagem “Reprovado” caso contrario será exibida a mensagem “Recuperação”. F2>=7

"Aprovado"

F2<4 "Recuperação"

"Reprovado"

Para realizarmos nosso cálculo utilizaremos a seguinte fórmula: =SE(F2>=7;"Aprovado";SE(F2<4;"Reprovado";"Recuperação"))

Fone: 112084-0070


Pag.: 46

Microsoft Excel 2013 - Avançado E Retornará VERDADEIRO se todos os seus argumentos forem avaliados como VERDADEIRO e retornará FALSO se um ou mais argumentos forem avaliados como FALSO. Um uso comum para a função E é expandir a utilidade de outras funções que realizam testes lógicos. Por exemplo, a função SE realiza um teste lógico e, em seguida, retornará um valor se o teste for avaliado como VERDADEIRO e outro valor se o teste for avaliado como FALSO. Usando a função E como argumento teste_lógico da função SE, você pode testar várias condições diferentes em vez de apenas uma. =E(lógico1; [lógico2], ...) A sintaxe da função E tem os seguintes argumentos: lógico1 - Obrigatório. A primeira condição que você deseja testar que pode ser avaliada como VERDADEIRO ou FALSO. lógico2, ... - Opcional. Condições adicionais que você deseja testar que pode ser avaliada como VERDADEIRO ou FALSO, até um máximo de 255 condições. No exemplo a seguir para calcular o resultado que o aluno teve utilizaremos como critérios a Média Final e a Frequência, ou seja, se a Média Final for superior ou igual a 7 e a Frequência superior ou igual a 70% será exibida a mensagem “Aprovado” caso contrário será exibida a mensagem “Reprovado”. E(F2>=7;G2>=70%)

"Aprovado"

"Reprovado"

A fórmula utilizada na célula H2 para o resultado foi: =SE(E(F2>=7;G2>=70%);"Aprovado";"Reprovado")

Observe que o aluno Pedro mesmo tendo a Média final maior que 7,0 foi Reprovado pois sua frequência não foi superior a 70%.

Fone: 112084-0070


Pag.: 47


OU Retorna VERDADEIRO se qualquer argumento for VERDADEIRO; retorna FALSO se todos os argumentos forem FALSOS. Um uso comum para a função OU é expandir a utilidade de outras funções que realizam testes lógicos. Semelhante a função E só que precisa apenas de 1 teste_lógico VERDADEIRO para ser VERDADEIRO. =OU(lógico1; [lógico2], ...) Lógico1; lógico2; ... Lógico1 é necessário, valores lógicos subsequentes são opcionais. Condições de 1 a 255 que você deseja testar e que podem resultar em VERDADEIRO ou FALSO. No exemplo a seguir para calcular o resultado que o aluno teve utilizaremos como critérios a Média Final e a Média Geral da Turma, ou seja, se a Média Final for superior ou igual a 7 e a Média Final superior a Média Geral da Turma será exibida a mensagem “Aprovado” caso contrário será exibida a mensagem “Reprovado”. OU(F2>=7;F2>=$C$11)

"Aprovado"

"Reprovado"

A fórmula utilizada na célula G2 para o resultado foi: =SE(OU(F2>=7;F2>=$C$11);"Aprovado";"Reprovado")

A célula $C$11 é a Média Geral da Turma e foi fixada pois é o mesmo critério para todos os alunos. Neste exemplo observe que a aluna Cecilia não teve uma média final superior a 7 porém sua média ficou superior a média geral da turma e por esse motivo foi Aprovada.

Sempre que você criar esta ou qualquer outra fórmula, procure escrevê-la de forma que ao serem alterados os dados da tabela, você não tenha que corrigir a fórmula, isto significa que você deve sempre digitar os endereços das células e nunca o conteúdo da mesma. Para tanto você também deve observar a forma correta para fixar os endereços antes de copiá-la para as demais células.

Fone: 112084-0070


Pag.: 48

Microsoft Excel 2013 - Avançado SEERRO Esta função pertence a categoria de Funções de Informação. Retorna um valor especificado se uma fórmula gerar um erro; caso contrário, retorna o resultado da fórmula. Use a função SEERRO para capturar e controlar os erros em uma fórmula. =SEERRO(valor;valor_se_erro) A sintaxe da função SEERRO tem os seguintes argumentos (argumento: um valor que fornece informações a uma ação, um evento, um método, uma função ou um procedimento.): Valor - Obrigatório. O argumento verificado quanto ao erro. Valor_se_erro - Obrigatório. O valor a ser retornado se a fórmula gerar um erro. Os seguintes tipos de erro são considerados: #N/D, #VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? ou #NULO!.

Mensagem

O que ocorreu?

#DIV/0!

Existe uma fórmula que exibiu este resultado que está utilizando alguma referência para dividir por zero. É preciso corrigir a célula, ou alterar o valor para um número diferente de 0 na célula usada como divisor.

#N/D

Esta mensagem significa que nenhum valor está disponível neste momento na célula usada como referência ou fonte de dados. Você pode inserir #N/D quando desejar que esta célula não faça cálculos tendo como valor 0, e sim espere até ser digitado algum valor. Enquanto o valor não for digitado na célula referente, o resultado da fórmula será #N/D. Se for algum erro na fórmula, possivelmente está na referência errada.

#NOME?

Aparece quando se usa um nome estranho ao Excel. Pode ser divido ao fato de você não ter definido o nome antes, ou digitou o nome errado (Exemplo SONA, ao invés de SOMA). Se você está inserindo texto junto com fórmula deve coloca-lo entre aspas.

#NULO!

Existe sempre quando você se refere a uma interseção de áreas que não se interceptam. Por exemplo: (A10:A20) (A21:D21)

#NUM!

Está existindo algum problema com algum número digitado. Você digitou algum número, somatória, matriz, que não existe. Por exemplo: =RAIZ(-1).

#REF!

Ocorre quando você utilizou uma referência errada.

#VALOR!

Ocorre quando você usa algum tipo estranho de argumento ou operando. Você pode, por exemplo, digitar algo repetido ou algum caractere desconhecido.

Observe que na planilha abaixo que para o aluno Carlos não foi lançada nenhuma nota e vários erros ocorreram na planilha.

Para corrigir estes erros utilizaremos a função SEERRO na coluna da Média Final para que se o aluno não tiver nenhuma nota e aparecer o erro #DIV/0! o mesmo seja substituido por 0. A fórmula da Média Final foi adaptada para corrigir o erro. Na célula F2 foi inserida a seguinte fórmula: =SEERRO(MÉDIA(B2:E2);0) Se a Média der algum erro será exibido 0 caso contrário será exibido o resultado da média.

Fone: 112084-0070


Pag.: 49


Observe que fazendo apenas a alteração na coluna da Média Final automaticamente todos os outros cálculos são atualizados. A função SEERRO só esta disponível a partir da versão 2007 do Excel. Nas versões anteriores a 2007 precisamos aninha a função SE com a função ÉERROS como no exemplo abaixo: =SE(ÉERROS(MÉDIA(B2:E2);0;MÉDIA(B2:E2))

NÃO Inverte o valor do argumento FALSO para VERDADEIRO, ou VERDADEIRO para FALSO. Use NÃO quando quiser ter certeza de que um valor não é igual a outro valor determinado. =NÃO(lógico) Lógico - Obrigatório. Um valor ou expressão que pode ser avaliado como VERDADEIRO ou FALSO. Exemplo de aplicação da função NÃO: =SE(NÃO(EstáVencido); FaçaIsso; FaçaAquilo) ao invés de =SE(EstáVencido = FALSO; FaçaIsso; FaçaAquilo)

VERDADEIRO Retorna o valor lógico VERDADEIRO.

=VERDADEIRO( )

Você pode inserir o valor VERDADEIRO diretamente nas células e fórmulas sem usar esta função. A função VERDADEIRO é fornecida primeiramente para compatibilidade com outros programas de planilha.

FALSO Retorna o valor lógico FALSO.

=FALSO( )

Você também pode digitar a palavra FALSO diretamente na planilha ou fórmula, e o Microsoft Excel a interpretará como o valor lógico FALSO. A função FALSO é fornecida basicamente para compatibilidade com outros programas da planilha.

Para fixar o conteúdo da AULA 01 e tirar possíveis dúvidas realize os exercícios complementares que se encontram no final do material e em casos de dúvidas você poderá tirar com seu instrutor durante o treinamento ou através do e-mail: [email protected]

Fone: 112084-0070


Pag.: 50


AULA 2 Formatação Condicional O Excel permite que você aplique formatos à células de acordo com uma condição estabelecida na função SE. Proceda da seguinte forma: 

Selecione as células que você deseja formatar.



Na guia Página Inicial, no grupo Estilos, clique na seta ao lado de Formatação Condicional.

Varias Opções serão exibidas:  Realçar Regras das Células – Formata as células de acordo com seus valores (Maior, Menor, Entre, Igual, Valores Duplicados, e outros)  Regras de Primeiros/Últimos – Formata os 10 primeiros ou 10 últimos itens, formata os primeiros ou últimos 10%, os valores acima ou abaixo da média.  Barras de Dados – Exibi uma barra de dados colorida nas células de acordo com seus respectivos valores.  Escalas de Cor – Exibi um gradiente de duas ou três cores em um intervalo de células  Conjuntos de Ícones – Exibi um ícone do conjunto de ícones em cada célula. Cada ícone representa um valor da célula. Desde a versão 2007 a Formatação Condicional sofreu algumas melhorias, a quantidade de condições ou a ser ilimitada, enquanto nas versões anteriores era limitada a 3 condições apenas. As opções do menu acima só estão disponíveis a partir da versão 2007 e muitas das regras e condições aplicadas podem não ser compatíveis com as versões anteriores ao 2007.

Aplicando a Formatação condicional 

Selecione as células que você deseja alterar. No nosso exemplo selecionaremos a coluna de Resultado para formatarmos as palavras “Aprovado” de Azul, “Reprovado” de Vermelho e a “Recuperação” de Verde.



Na guia Página Inicial, no grupo Estilo, clique em Formatação Condicional.



Clique em Nova Regra.



Clique em Formatar apenas células que contenham.



Clique na seta que aponta para baixo de frase de comparação e, em seguida, clique na frase de comparação que você deseja. Ex: Valor da Célula... é igual a...



Digite os valores constantes ou fórmulas que você deseja que sejam avaliados. Ex: Aprovado

Fone: 112084-0070


Pag.: 51

Microsoft Excel 2013 - Avançado 

Clique em Formatar. Especifique a formatação desejada e clique em OK duas vezes.



Clique em OK para aplicar a regra.



Se quiser aplicar uma nova regra para as demais condições repita o processo novamente alterando os valores e formatação.

Além de permitir a formatação por comparação, é possível usar regras de classificação e recursos gráficos como barras de dados, ícones e escalas de cores. Esta formatação é ideal para a visualização do valor de uma célula em relação a outras. Quanto mais longa a barra, maior é o valor. É uma interessante alternativa para os gráficos. Cuidado ao utilizar a opção Nova Regra pois o Excel irá aplicar as regras ao intervalo selecionado. Caso a formatação não funcione você precisa editar a regra, pois se utilizar o comando Nova Regra o mesmo irá adicionar outra regra além da que não funcionou o que pode ocasionar um acumulo de regras.

Editando uma Regra de Formatação Condicional 

Selecione as células que contém a regra que você deseja editar.






Clique em Gerenciar Regras.



Clique na regra que você deseja alterar.



Clique em Editar Regra.



Use os controles para fazer suas alterações.



Clique em OK duas vezes para salvar as alterações.

Fone: 112084-0070


Pag.: 52

Microsoft Excel 2013 - Avançado Barras de Dados em uma ou mais células 

Selecione as células que contêm os dados.






Aponte para Barras de Dados.



Clique na opção de barra de dados que você deseja aplicar.

Escala de cor em uma ou mais células As escalas de cores são guias visuais para a interpretação da distribuição e da variação dos dados. 

Selecione as células que contêm seus dados.






Aponte para Escalas de Cor.



Clique no padrão de escala de cor que você deseja aplicar.

Fone: 112084-0070


Pag.: 53

Microsoft Excel 2013 - Avançado Conjuntos do ícones em uma ou mais células Quanto aos ícones servem para marcar e classificar dados em três ou cinco categorias diferentes separadas por um valor limite. 

Selecione as células que contém os dados.






Aponte para Conjuntos de ícones.



Clique no conjunto de ícones que você deseja aplicar.

Destacando Valores Duplicados ou Exclusivos Podemos destacar valores duplicados ou exclusivos em uma lista de dados. Para isso siga os procedimentos abaixo: 

Selecione as células que contém os dados.






Aponte para Realçar Regras das Células em seguida clique em Valores Duplicados.



Na janela que se abre selecione a formatação desejada e clique em OK.

Observe que podemos escolher entre destacar os valores Duplicados ou Exclusivos. No exemplo acima foi destacado os valores duplicados observe que todos os Pedidos com números repetidos foram destacados.

Fone: 112084-0070


Pag.: 54

Microsoft Excel 2013 - Avançado Excluindo uma Regra de Formatação Condicional 

Selecione as células que contém a regra que você deseja editar.






Clique em Gerenciar Regras.



Clique na regra que você deseja excluir.



Clique em Excluir Regra.



Clique em OK.

Formatação Condicional Utilizando Fórmula Para deixar a planilha com efeito de “Papel Listrado” utilizamos a Formatação Condicional. 

Selecione a área da planilha na qual você deseja aplicar o efeito,



Clique na guia Página Inicial , no grupo Estilo e clique em Formatação Condicional



Em seguida, clique em Nova Regra.



Escolha a opção Usar uma fórmula para determinar quais células devem ser formatadas



Digite a fórmula abaixo para formatar as linhas com números pares: =MOD(LIN();2)=0



Clique no botão Formatar e escolha uma formatação de cor na guia Preenchimento e clique em OK;



Clique no botão OK e pronto a planilha terá o efeito de papel listrado.



Se desejar formatar as linhas ímpares repita o procedimento e digite a função abaixo: =MOD(LIN();2)<>0

Fone: 112084-0070


Pag.: 55

Microsoft Excel 2013 - Avançado Para fazer a formatação de colunas substitua na formatação condicional nas formulas a função LIN() por COLUNA(). =MOD(COLUNA();2)=0 e =MOD(COLUNA();2)<>0

LIN Retorna o número da linha de uma referência. =LIN([ref]) A sintaxe da função LIN tem os seguintes argumentos: Ref - Opcional. A célula ou intervalo de células cujo número da linha você deseja obter. Se ref for omitido, será equivalente à referência da célula na qual a função LIN aparecer. Se ref for um intervalo de células e LIN for inserida como uma matriz vertical, LIN retornará os números de linha de referência como uma matriz vertical. Ref não pode se referir a áreas múltiplas.

COLUNA Retorna o número da coluna da referência de célula especificada. =COLUNA([ref]) A sintaxe da função COLUNA tem os seguintes argumentos: Ref - Opcional. A célula ou intervalo de células cujo número da coluna você deseja obter. Se ref for omitido, será equivalente à referência da célula na qual a função COLUNA aparecer.

Nas versões anteriores a versão 2013 do Excel a função COLUNA se chama COL. Neste caso se for utilizar esta função em outras versões do Excel que não seja a 2013 faça como no exemplo: =COL() ou =COL(C10)

Fone: 112084-0070


Pag.: 56


Importação de Dados Existem situações onde os dados que você precisa analisar não estão no formato de uma planilha do Excel. Por exemplo, estes dados podem estar em um arquivo do tipo texto, no formato de texto delimitado ou separado por vírgula, ou podem estar em uma tabela do Microsoft Access. Nestas situações você deseja importar estes dados para dentro de uma planilha do Excel, para poder utilizar as funcionalidades de análise de dados do Excel. Ao importar dados, você não precisa digitar novamente os dados que deseja analisar no Microsoft Excel. Você também pode atualizar seus relatórios e resumos do Excel automaticamente a partir do banco de dados original, sempre que o banco de dados for atualizado com novas informações.

Importando Arquivos de Texto Você pode usar o Microsoft Excel para importar dados de um arquivo de texto para uma planilha. O Assistente de Importação de Texto examina o arquivo de texto que você está importando e o ajuda a verificar se os dados estão sendo importados da maneira desejada. OBS: Para iniciar o Assistente de Importação de Texto, na guia Dados, no grupo Obter Dados Externos, clique em De Texto.

Em seguida, na caixa de diálogo Importar Arquivo de Texto, clique duas vezes no arquivo de texto que você deseja importar.

Neste momento será apresentado o Assistente de Importação de texto que possui 3 etapas.

Etapa 1 de 3 A Primeira possui as seguintes opções: 

Tipo de dados originais – Se os itens do arquivo de texto estiverem separados por tabulações, dois-pontos, ponto-e-vírgulas, espaços ou outros caracteres, selecione Delimitado. Se todos os itens em cada coluna tiverem o mesmo tamanho, selecione Largura fixa.



Iniciar importação na linha – Digite ou selecione um número de linha para especificar a primeira linha dos dados que deseja importar.



Origem do arquivo - Selecione o conjunto de caracteres usado no arquivo de texto. Na maioria dos casos, você pode deixar essa configuração como o padrão. Se souber que o arquivo de texto foi criado com o uso de um conjunto de caracteres diferente do que está sendo usado no seu computador, você deverá alterar a configuração para corresponder a esse conjunto de caracteres. Por exemplo, se o seu computador estiver definido para usar o conjunto de caracteres 1251 (Cirílico, Windows) e você souber que o arquivo foi produzido com o uso do conjunto de caracteres 1252 (Europeu Ocidental, Windows), será necessário definir a Origem do Arquivo como 1252.

Fone: 112084-0070


Pag.: 57


Visualização do arquivo – Esta caixa de diálogo mostra como o texto será exibido quando separado em colunas na planilha.



Selecione as opções desejadas e clique em Avançar para ir a janela da segunda etapa do Assistente.

Etapa 2 de 3 – Arquivos de Texto com Delimitadores Se na primeira etapa você tiver selecionada a opção Delimitado a segunda etapa do Assistente terá a janela a seguir:



Delimitadores – Selecione o caractere que separa valores no arquivo de texto. Se o caractere não estiver listado, marque a caixa de seleção Outros e digite o caractere na caixa que contém o cursor. Essas opções não se encontrarão disponíveis se o tipo de dados for Largura fixa.



Considerar delimitadores consecutivos como um só – Marque essa caixa de seleção se os dados contiverem um delimitador de mais de um caractere entre os campos de dados ou se contiverem vários delimitadores personalizados.



Qualificador de texto – Selecione o caractere que delimita valores no seu arquivo de texto. Quando o Excel encontrar o caractere qualificador de texto, todo o texto após o caractere e antes da próxima ocorrência dele será importado como um valor, mesmo que o texto contenha um caractere delimitador. Por exemplo, se o delimitador for uma vírgula (,) e o qualificador do texto for uma aspa ("), "Dallas, Texas" será importado em uma célula como Dallas, Texas. Se nenhum caractere, ou o apóstrofo ('), for especificado como qualificador de texto, "Dallas, Texas" será importado em duas células adjacentes como "Dallas e Texas".

Fone: 112084-0070


Pag.: 58


Se o caractere delimitador ocorrer entre qualificadores de texto, o Excel omitirá os qualificadores no valor importado. Se nenhum caractere delimitador ocorrer entre qualificadores de texto, o Excel incluirá o caractere qualificador no valor importado. Assim, "Dallas Texas" (usando o qualificador de texto aspas) será importado em uma célula como "Dallas Texas".



Visualização dos dados – Releia o texto nesta caixa para verificar se o texto será separado em colunas na planilha conforme desejado.

Neste momento avance para a terceira etapa para definir os formatos de dados de cada coluna (campo).

Etapa 2 de 3 – Arquivos de Texto com Largura Fixa Se na primeira etapa você optar por Largura Fixa será exibida a janela a seguir para a segunda etapa do Assistente:



Visualização dos dados – Defina as larguras de campo nessa seção. Clique na janela de visualização para definir uma quebra de coluna, representada por uma linha vertical. Clique duas vezes em uma quebra de coluna para removê-la ou arraste uma quebra de coluna para movê-la.

Etapa 3 de 3 – Formato dos dados da coluna Na terceira etapa iremos definir o formato dos dados das colunas. 

Formato dos dados da coluna – Clique no formato dos dados da coluna selecionado na seção Visualização dos dados. Se você não desejar importar a coluna selecionada, clique em Não importar coluna (ignorar).



Depois de selecionar uma opção de formato de dados para a coluna selecionada, o título da coluna em Visualização dos dados exibirá o formato. Se você selecionar Data, selecione um formato de data na caixa Data.



Escolha o formato de dados que corresponda aos dados da visualização para que o Excel possa converter os dados importados corretamente. Por exemplo:  Para converter uma coluna de todos os caracteres numéricos de moeda no formato de Moeda do Excel, selecione Geral.  Para converter uma coluna de todos os caracteres numéricos no formato de Texto do Excel, selecione Texto.  Para converter uma coluna de todos os caracteres de data, cada data na ordem de ano, mês e dia, no formato de Data do Excel, selecione Data e depois selecione o tipo de data de AMD na caixa Data.



O Excel importará a coluna como Geral se a conversão puder produzir resultados indesejáveis. Por exemplo:

Fone: 112084-0070


Pag.: 59

Microsoft Excel 2013 - Avançado  Se a coluna contiver vários formatos, como caracteres alfabéticos e numéricos, o Excel converterá essa coluna em Geral.  Se, em uma coluna de datas, cada data estiver na ordem de ano, mês e dia e você selecionar Data junto com um tipo de data de MDA, o Excel converterá a coluna no formato Geral. Uma coluna que contenha caracteres de data deve corresponder a formatos de data personalizados ou internos do Excel.  Se o Excel não converter uma coluna no formato desejado, você poderá converter os dados depois de importá-los.

Clique no botão Avançado para seguir um ou mais destes procedimentos: 

Especifique o tipo de separador de decimal ou milhar usado no arquivo de texto. Quando os dados forem importados para o Excel, os separadores corresponderão aos especificados para o seu local nas Configurações Regionais e de Idiomas ( de Controle do Windows).



Especifique que um ou mais valores numéricos podem conter um sinal de menos à direita.

Clique no botão Concluir do Assistente para concluir a configuração da importação. Neste momento será exibida a janela a seguir perguntando onde deve ser colocado os dados. Se Na Planilha Existente ou Na nova planilha. Selecione a opção desejada e OK. Os dados serão importados do arquivo de texto para o Excel.

Fone: 112084-0070


Pag.: 60

Microsoft Excel 2013 - Avançado Importando Dados do Access Para transferir ao Excel dados do Access que podem ser atualizados, você pode criar uma conexão, muitas vezes armazenada em um arquivo de conexão de dados do Office (extensão .odc), com o banco de dados do Access e recuperar todos os dados a partir de uma tabela ou consulta. A principal vantagem de se conectar aos dados do Access em vez de importá-los é que você pode analisar periodicamente esses dados no Excel sem copiar ou exportar repetidamente os dados do Access. Após estabelecer a conexão com os dados, você também pode atualizar automaticamente as pastas de trabalho do Excel a partir do banco de dados original do Access sempre que o banco de dados for atualizado com novas informações. Por exemplo, é possível atualizar um relatório de orçamento de resumo do Excel que é distribuído mensalmente de forma que ele contenha os dados do mês atual. 

Clique na célula onde você deseja colocar os dados do banco de dados do Access.



Na guia Dados, no grupo Obter Dados Externos, clique em Do Access.



Localize o banco de dados do Access que você deseja importar e clique duas vezes nele.



Na caixa de diálogo Selecionar Tabela, clique na tabela ou consulta que deseja importar e clique em OK.



Na caixa de diálogo Importar Dados, selecione a opção desejada.

Fone: 112084-0070


Pag.: 61

Microsoft Excel 2013 - Avançado Em Selecione o modo como deseja exibir estes dados na pasta de trabalho, siga um destes procedimentos: 

Para exibir os dados como uma tabela, selecione Tabela.



Para exibir os dados como um relatório de tabela dinâmica, selecione Relatório de Tabela Dinâmica.



Para exibir os dados como um relatório de gráfico dinâmico e tabela dinâmica, selecione Relatório de Tabela Dinâmica e Gráfico Dinâmico.



Outra opção é clicar em Propriedades para definir as opções de atualização, formatação e layout para os dados importados e, em seguida, clicar em OK.

Em Onde você deseja colocar os dados?, siga um destes procedimentos: 

Para retornar os dados para o local que você selecionou, clique em Planilha existente.



Para retornar os dados para o canto superior esquerdo da nova planilha, clique em Nova planilha.



Clique em OK.



O Excel colocará o intervalo de dados externos no local especificado por você.

Fone: 112084-0070


Pag.: 62

Microsoft Excel 2013 - Avançado Importando Dados da WEB Um recurso muito interessante do Excel, mas ainda pouco explorado diz respeito a obtenção de dados de páginas da internet diretamente na sua planilha Excel. Esta tarefa é especialmente importante quando temos dados que tem que ser obtidos periodicamente da internet e colados na sua planilha, como a cotação de moedas estrangeiras e bolsa de valores por exemplo. O Excel pode trazer vários dados da internet, mas é especialmente útil para dados que estejam em tabelas HTML. Para obtermos os dados da internet siga os procedimentos abaixo: 

Na guia Dados, no grupo Obter Dados Externos, clique em Da Web.



Um navegador Web do Excel será exibido, digite o endereço do site do qual deseja importar os dados. Para exemplo digite o endereço abaixo para obtermos a tabela de IRPF atualizada para o ano de 2011. http://www1.folha.uol.com.br/indicadores/



Nesta tela em todos os pontos aonde você tem marcadores amarelos você poderá importar para o Excel, marque conforme a figura abaixo:



Depois de fazer a seleção dos dados, clique no botão Importar, aparecerá a tela para determinar aonde será depositada a consulta WEB, e clicando no botão Propriedades você poderá alterar para que ele mantenha a formatação que você fizer nas células depois por exemplo, e ainda você poderá alterar para Atualizar os dados periodicamente por minutos ou ao abrir o arquivo conforme a figura a seguir.

Fone: 112084-0070


Pag.: 63




O Excel irá importar os dados para o Excel e você poderá formatar ele como quiser, como abaixo:

Atualizando dados Para atualizar uma conexão de dados específica na pasta de trabalho, clique em uma célula no intervalo de dados externos. 

Na guia Dados, no grupo Conexões, clique na seta ao lado de Atualizar Tudo e clique em Atualizar.



Para atualizar todas as conexões de dados na pasta de trabalho, clique em Atualizar Tudo na guia Dados, no grupo Conexões.

Se os dados a serem atualizados forem de um arquivo de texto siga os procedimentos abaixo: 

Selecione a planilha que contém o arquivo de texto importado.

Fone: 112084-0070


Pag.: 64


Na guia Dados, no grupo Conexões, clique na seta ao lado de Atualizar e, em seguida, clique em Atualizar.



Na caixa de diálogo Importar arquivo de texto, selecione seu arquivo de texto e clique em Importar.

Para atualizar as fontes de dados pode utilizar as teclas de atalho:

Você Sabia?

CTRL + ALT + F5 ALT + F5

 Atualiza as informações da fonte de dados selecionada.  Atualiza as informações de todas as fontes de dado da Pasta de trabalho ativa.

Funções Matemáticas CONT.SE Calcula o número de células não vazias em um intervalo que corresponde a determinados critérios. =CONT.SE(intervalo;critérios) Intervalo - é o intervalo de células no qual se deseja contar células não vazias. Critérios - é o critério na forma de um número, expressão ou texto que define quais células serão contadas. Por exemplo, os critérios podem ser expressos como 32, "32", ">32", "André". Exemplo: Tendo uma base de dados como a do exemplo (Pedidos) abaixo utilizando a função CONT.SE podemos analisar a quantidade de pedidos emitidos por um determinado vendedor ou o número de pedidos enviados para um determinado país.

Para podermos analisar os dados devemos primeiro montar uma tabela de resumo com os itens que desejamos contar. A partir daí podemos fazer montar a fórmula: =CONT.SE($E$2:$E$181;K2) Onde: $E$2:$E$181 é o intervalo de células no qual se deseja contar células não vazias e K2 é o critério que define quais células devem ser contadas.

Fone: 112084-0070


Pag.: 65


Quando utilizamos o intervalo de células (E$2:$E$181) em uma função como a CONT.SE e depois precisamos copiar a mesma fórmula para as outras células não podemos esquecer de fixar as células do intervalo para que elas não se alterem. O critério no caso acima é Variável e não deve ser fixado.

SOMASE Use a função SOMASE para somar os valores em um intervalo que atendem aos critérios que você especificar. =SOMASE(intervalo;critérios;[intervalo_soma]) Intervalo - é o intervalo de células que se deseja calcular. Critérios - são os critérios na forma de um número, expressão ou texto, que define quais células serão adicionadas. Por exemplo, os critérios podem ser expressos digitando-se a célula que contem o critério ou também, em outros casos como 1000, "1000", ">1000", "André". Intervalo_soma - são as células que serão realmente somadas. As células em intervalo_soma são somadas somente se suas células correspondentes em intervalo coincidirem com os critérios estipulados. Se intervalo_soma for omitido, as células em intervalo serão somadas. Por exemplo, tomando como base a planilha utilizada anteriormente, se quisermos somar o valor total de vendas de cada vendedor devemos utilizar a seguinte fórmula: =SOMASE($E$2:$E$181;K2;$I$2:$I$181) Onde: Serão somados apenas os valores no intervalo $I$2:$I$181, em que as células correspondentes no intervalo $E$2:$E$181 equivalem a "André".

Lembre que os intervalos foram fixados para que a fórmula pudesse ser copiada para os demais vendedores. As funções CONT.SES, SOMASES, MÉDIASE e MÉDIASES só estão disponíveis no Excel a partir da versão 2007. Sendo assim quando utilizada em uma pasta de trabalho nas versões mais atuais e salva no modo de compatibilidade as mesma se abertas em versões anteriores a 2007 apresentação o erro #NOME?.

Fone: 112084-0070


Pag.: 66


Você Sabia?

Você pode nomear os intervalos de dados a serem utilizados em uma função para facilitar a elaboração da fórmula. Por exemplo: O intervalo $E$2:$E$181 pode ser nomeado para Vendedores através da caixa de nome. Assim como o intervalo $I$2:$I$181 pode ser nomeado para Total. Desta forma na hora de montar a fórmula basta utilizar os nomes criados dispensando desta forma a necessidade de fixar as células e evitando erros: =SOMASE(VENDEDORES;K2;TOTAL)

CONT.SES Aplica critérios a células em vários intervalos e conta o número de vezes que todos os critérios são atendidos. =CONT.SES(intervalo_critérios1; critérios1; [intervalo_critérios2; critérios2]…) A sintaxe da função CONT.SES tem os seguintes argumentos: intervalo_critérios1 - Obrigatório. O primeiro intervalo no qual avaliar os critérios associados. critérios1 - Obrigatório. Os critérios no formato de um número, uma expressão, uma referência de célula ou um texto que define quais células serão contadas. Por exemplo, os critérios podem ser expressos como 32, ">32", B4, "André" ou "32". intervalo_critérios2, critérios2, ... – Opcional. Intervalos adicionais e seus critérios associados. Até 127 intervalo/critérios pares são permitidos. Por exemplo, tomando como base a planilha de Pedidos, se quisermos saber a quantidade de pedidos emitidos por cada vendedor em um determinado país devemos utilizar a seguinte fórmula: =CONT.SES($E$2:$E$181;K2;$D$2:$D$181;L2) Onde: $E$2:$E$181 é o intervalo de células (Vendedor) no qual se deseja contar células não vazias e K2 é o critério para o primeiro intervalo. $D$2:$D$181 (Total) é o segundo intervalo de células e L2 o segundo critério. Só serão contadas as células que atendam aos 2 critérios.

SOMASES Soma as células em um intervalo que atendem a vários critérios. =SOMASES(intervalo_soma; intervalo_critérios1; critérios1; [intervalo_critérios2; critérios2]; …) intervalo_soma - Necessário. Uma ou mais células para somar, incluindo números ou nomes, intervalos ou referências de célula (referência de célula: o conjunto de coordenadas que a célula abrange em uma planilha. Por exemplo, a referência da célula que aparece na interseção da coluna B e linha 3 é B3.) que contêm números. Valores em branco e de texto são ignorados. intervalo_critérios1 - Necessário. O primeiro intervalo no qual avaliar os critérios associados. critérios1 - Necessário. Os critérios no formato de um número, uma expressão, uma referência de célula ou um texto que define quais células no argumento intervalo_critérios1 serão adicionadas. Por exemplo, os critérios podem ser expressos como 32, ">32", B4, "André" ou "32."

Fone: 112084-0070


Pag.: 67

Microsoft Excel 2013 - Avançado intervalo_critérios2, critérios2, … - Opcional. Intervalos adicionais e seus critérios associados. Até 127 intervalo/critérios pares são permitidos. Por exemplo, utilizando a planilha de Pedidos, iremos somar o Total dos Pedidos do Vendedor de acordo com o País. Para isso utilizamos a fórmula: =SOMASES($I$2:$I$181;$E$2:$E$181;K2;$D$2:$D$181;L2) Onde: $I$2:$I$181 é o intervalo das células (Total) a serem somadas cujo os critérios sejam correspondentes . $E$2:$E$181 é o primeiro intervalo de critérios (Vendedor) e K2 é o critério para o primeiro intervalo. $D$2:$D$181 é o segundo intervalo de células (País) e L2 o segundo critério. Só serão somadas as células que atendam aos 2 critérios.

A ordem dos argumentos é diferente entre as funções SOMASES e SOMASE. Em particular, o argumento intervalo_soma é o primeiro em SOMASES, mas é o terceiro em SOMASE. Se você estiver copiando e editando essas funções semelhantes, coloque os argumentos na ordem correta.

MÉDIASE Retorna a média (média aritmética) de todas as células em um intervalo que satisfazem um determinado critério. Semelhante a função SOMASE só que neste caso ela calcula a média. =MÉDIASE(intervalo; critérios; [intervalo_média]) Intervalo - Obrigatório. Uma ou mais células a serem usadas para o cálculo da média, incluindo números ou nomes, matrizes ou referências que contêm números. Critérios - Obrigatório. Os critérios na forma de um número, uma expressão, uma referência de célula ou um texto que define quais células serão usadas para o cálculo da média. Por exemplo, os critérios podem ser expressos como 32, "32", ">32", "André" ou B4. Intervalo_média - Opcional. O conjunto real de células que será usado para calcular a média. Se omitido, será usado o intervalo. Por exemplo, utilizando a planilha de Pedidos, iremos calcular a média do Total dos Pedidos de cada Vendedor. Para isso utilizamos a fórmula: =MÉDIASE($E$2:$E$181;K4;$I$2:$I$181)

Fone: 112084-0070


Pag.: 68

Microsoft Excel 2013 - Avançado Onde: Será calculada a média apenas dos valores no intervalo $I$2:$I$181, em que as células correspondentes no intervalo $E$2:$E$181 equivalem a "André".

MÉDIASES Retorna a média (média aritmética) de todas as células que satisfazem vários critérios. =MÉDIASES(intervalo_média; intervalo_critérios1; critérios1; [intervalo_critérios2; critérios2]; …) Intervalo_média - Obrigatório. Uma ou mais células a serem usadas para o cálculo da média, incluindo números ou nomes, matrizes ou referências que contêm números. Intervalo1_critérios, intervalo2_critérios, … - Intervalo1_critérios é obrigatório, intervalos_critérios subsequentes são opcionais. Os intervalos de 1 a 127 para avaliar os critérios associados. Critérios1, critérios2, … - Critérios1 é obrigatório, critérios subsequentes são opcionais. Os critérios de 1 a 127 na forma de um número, uma expressão, uma referência de célula ou um texto que define quais células serão usadas para calcular a média. Por exemplo, os critérios podem ser expressos como 32, "32", ">32", "André" ou K4. Por exemplo, iremos tirar a média do Total de pedidos do Brasil por vendedor. Para isso utilizamos a fórmula: =MÉDIASES($I$2:$I$181;$D$2:$D$181;$L$1;$E$2:$E$181;K4) Onde: $I$2:$I$181 é o intervalo das células (Total) a serem calculadas as médias cujo os critérios sejam correspondentes. $D$2:$D$181 é o primeiro intervalo de critérios (País) e $L$1 é o critério para o primeiro intervalo. $E$2:$E$181 é o segundo intervalo de células (Vendedor) e L2 o segundo critério. Só será calculada a média das células que atendam aos 2 critérios.

Observe que ao copiar a fórmula para as demais células ocorrem erros (#DIV/0!) em algumas células. Isso ocorre porque nem todos os vendedores emitiram pedidos para o Brasil e neste caso a média não pode ser calculada pois não há possibilidade de se dividir valores por 0.

Fone: 112084-0070


Pag.: 69

Microsoft Excel 2013 - Avançado Para corrigir o erro devemos utilizar a função SEERRO como mostrada na fórmula abaixo: =SEERRO(MÉDIASES($I$2:$I$181;$D$2:$D$181;$L$1;$E$2:$E$181;K4);0)

Funções de Arredondamento As funções de arredondamento se encontra na categoria Matemática e Trigonometria. Para utilizar alguma dessas funções siga os procedimentos abaixo: 

Clique no botão  da barra de ferramentas padrão para abrir o Assistente de funções.



Na lista Categoria da Função escolha Matemática e Trigonometria;



Na lista Nome da Função escolha a função desejada;

Você pode também ar as funções da seguinte forma: 

Clique na guia Fórmulas, no grupo Bibliotecas de Funções clique na categoria Matemática e Trigonometria;



Na lista Nome da Função escolha a função desejada;

INT Arredonda um número para baixo até o número inteiro mais próximo. =INT(núm) Núm - é o número real que se deseja arredondar para baixo até um inteiro. Exemplos: =INT(8,9) é igual a 8

=INT(-8,9) é igual a -9

A seguinte fórmula retorna a parte decimal de um número real positivo: =A2-INT(A2)

Fone: 112084-0070


Pag.: 70

Microsoft Excel 2013 - Avançado TRUNC Trunca um número para um inteiro removendo a parte fracionária do número. =TRUNC(núm;núm_dígitos) Núm - é o número que se deseja truncar. Núm_dígitos - é um número que especifica a precisão da operação. O valor padrão para núm_dígitos é 0 (zero). Exemplos: =TRUNC(8,9) é igual a 8

=TRUNC(-8,9) é igual a -8

TRUNC e INT são semelhantes pois os dois retornam inteiros. TRUNC remove a parte fracionária do número. INT arredonda para menos até o número inteiro mais próximo de acordo com o valor da parte fracionária do número. INT e TRUNC são diferentes apenas quando usam números negativos: TRUNC(-4,3) retorna -4, mas INT(-4,3) retorna -5, porque -5 é o número menor.

A função TRUNC nas versões anteriores ao Excel 2013 se chama TRUNCAR.

ARRED Arredonda um número até uma quantidade especificada de dígitos. =ARRED(núm;núm_dígitos) Núm - é o número que você deseja arredondar. Núm_dígitos - especifica o número de dígitos para o qual você deseja arredondar núm. 

Se núm_dígitos for maior que 0, então núm será arredondado para o número especificado de casas decimais.



Se núm_dígitos for 0, então núm será arredondado para o inteiro mais próximo.



Se núm_dígitos for menor que 0, então núm será arredondado para a esquerda da vírgula decimal.

Exemplos: =ARRED(2,15; 1) é igual a 2,2 =ARRED(-1,475; 2) é igual a -1,48

Fone: 112084-0070

=ARRED(2,149; 1) é igual a 2,1 =ARRED(21,5; -1) é igual a 20


Pag.: 71


ARREDONDAR.PARA.CIMA Arredonda um número para cima afastando-o de zero. =ARREDONDAR.PARA.CIMA(núm;núm_dígitos) Núm - é qualquer número real que se deseja arredondar. Núm_dígitos - é o número de dígitos para o qual se deseja arredondar núm. O ARREDONDAR.PARA.CIMA funciona como ARRED, com a diferença de sempre arredondar um número para cima. 

Se núm_dígitos for maior do que 0, então o número será arredondado para cima pelo número de casas decimais especificado.



Se núm_dígitos for 0, núm será arredondado para cima até o inteiro mais próximo.



Se núm_dígitos for menor do que 0, então o número será arredondado para cima à esquerda da vírgula decimal.

Exemplos:

ARREDONDAR.PARA.BAIXO Arredonda um número para baixo até zero. =ARREDONDAR.PARA.BAIXO(núm;núm_dígitos) Núm - é qualquer número real que se deseja arredondar. Núm_dígitos - é o número de dígitos para o qual se deseja arredondar núm. O ARREDONDAR.PARA.BAIXO funciona como ARRED, com a diferença de sempre arredondar um número para baixo. 

Se núm_dígitos for maior do que 0, então o número será arredondado para baixo pelo número de casas decimais especificado.



Se núm_dígitos for 0, núm será arredondado para baixo até o inteiro mais próximo.



Se núm_dígitos for menor do que 0, então o número será arredondado para baixo à esquerda da vírgula decimal.

Exemplos:

Fone: 112084-0070


Pag.: 72

Microsoft Excel 2013 - Avançado TETO Retorna um número arredondado para cima, afastando-o de zero, até o múltiplo mais próximo de significância. =TETO(núm;significância) Núm - Obrigatório. O valor que você deseja arredondar. Significância - Obrigatório. O múltiplo para o qual você deseja arredondar. Exemplos:

PAR Retorna o número arredondado para o inteiro par mais próximo. Esta função pode ser usada para processar itens que aparecem em pares. =PAR(núm) Núm - Obrigatório. O valor a ser arredondado. Exemplos:

ÍMPAR Retorna o número arredondado para cima até o inteiro ímpar mais próximo. =ÍMPAR(núm) Núm - Obrigatório. O valor a ser arredondado. Exemplos:

Fone: 112084-0070


Pag.: 73

Microsoft Excel 2013 - Avançado Utilizando as funções de arredondamento No exemplo a seguir utilizaremos a tabela de Pedidos onde os vendedores receberão uma comissão de 1,50% sobre o total de vendas que ele realizou no mês. Para isso montamos a tabela abaixo onde na comissão utilizamos a fórmula =M2*$M$12 e copiamos a mesma para as demais células.

Observe que os valores da comissão quando calculados tiveram valores decimais. Se quisermos arredondar este valores para cima ou para baixo podemos utilizar uma das funções de arredondamento já vista. Neste exemplo iremos utilizar a função ARRED para arredondar os valores da comissão para números inteiros. Basta para isso incluir na fórmula da comissão a função ARRED como abaixo: =ARRED(M2*$M$12;0) Onde: M2*$M$10 é o valor a ser arredondado obtido pelo calculo da multiplicação e 0 é o número de dígitos decimais que desejamos para o resultado.

Observe que algumas células tiveram seus valores aumentados e outras os valores diminuidos.

Muito cuidado ao utilizar valores decimais no Excel pois muitas pessoas tem o costume de arredondar os valores através dos Botões de Aumentar e Diminuir casas decimais o que apenas faz um arredondamento visual o que pode implicar em divergências de valores caso um usuário tente conferir os valores em uma calculadora por exemplo. Por isso sempre que quiser ter uma precisam utilize uma das funções de arredondamento.

Abaixo uma sugestão de sintaxe de como utilizar uma função de arredondamento aninhada a outros cálculos ou funções. Você Sabia?

Fone: 112084-0070

=Função_de_Arredondamento(Fórmula;Nº_Digitos)


Pag.: 74


Validação de Dados O recurso de validação é muito interessante e útil pois através dele é possível fazer com que apareça uma mensagem informando o valor(es) que deverão ser inseridos em uma célula, controlar os valores que serão inseridos nesta célula e mostrar uma mensagem caso o valor não esteja correto. A validação de dados é muito útil para compartilhar uma pasta de trabalho com outros usuários na sua organização e quando você deseja que os dados inseridos nessa pasta de trabalho sejam precisos e consistentes. Para utilizar o comando de validação de dados siga os procedimentos abaixo: 

Selecione a célula ou intervalo de células a ser validado;



Clique na guia Dados, grupo Ferramentas de Dados e escolha a opção Validação de Dados;



Janela a seguir será exibida:

Na janela que se abre escolher as opções abaixo: 

Permitir - Clique em uma opção de validação de dados na caixa Permitir para aplicar restrições na entrada de dados das células selecionadas na planilha. 

Qualquer Valor – Permite a entrada de qualquer tipo de dados na célula, números, fórmulas, textos, símbolos entre outros;



Número Inteiro – Só permite a entrada de valores inteiros na célula;



Decimal – Permite a entrada de números inteiros e decimais na célula;



Lista – Permite a entrada de valores previamente especificado em uma lista que pode ser digitada ou de um intervalo de células;



Data – Restringe a entrada de valores para um período pré-fixado;



Hora – Permite apenas horas entre um intervalo ou após um valor determinado;



Comprimento do Texto – Determina a quantidade de caracteres que pode ser inserido em uma célula;



Personalizar – utilizado para validar a célula através da digitação de uma fórmula, usar uma expressão ou referir-se a um cálculo em outra célula para determinar entradas válidas.

Fone: 112084-0070


Pag.: 75


Dados - Clique no operador de comparação que deseja usar. Os operadores disponíveis dependem do tipo de dados escolhidos na caixa Permitir: 

Mínimo - Insira o valor mínimo, data inicial ou hora inicial como um valor, referência de célula ou fórmula para a opção de validação de dados escolhidos na caixa Permitir;



Máximo - Aplique o valor máximo, data final ou hora final como um valor, referência de célula ou fórmula para a opção de validação de dados escolhida na caixa Permitir;



Comprimento – Especifique o número de caracteres que deseja para a célula;



Fonte – Digite ou insira o intervalo referente a fonte de dados a ser utilizada na opção de lista;



Fórmula – Opção disponível quando se utiliza o tipo Personalizado. Permite a digitação da fórmula que será utilizada como regra de validação.



Aplicar alterações - Aplica todas as restrições, mensagens de entrada e mensagens de erro exibidas nesta caixa de diálogo às outras células na planilha que possuem as mesmas definições de validação de dados que as células selecionadas.



Ignorar em Branco - Interrompe a exibição de uma mensagem de erro quando uma célula restrita ou uma célula dependente das referências ou fórmulas estiver em branco.



Limpar Tudo - Cancela todas as seleções e exclui informações de todas as três guias da caixa de diálogo Validação de dados. Para desfazer os efeitos da opção Limpar tudo, clique em Cancelar.

Mensagem de Entrada Para exibir mensagens que solicitam entradas e para explicar ou evitar entradas incorretas, especifique os tipos de mensagens que você deseja nas guias Mensagem de entrada e Alerta de erro. Estes dois itens são opcionais.

Certifique-se de que a caixa de seleção Mostrar mensagem de entrada ao selecionar célula esteja marcada. Para exibir um título em negrito na mensagem, digite o texto na caixa Título. Digite o texto da mensagem com até 255 caracteres, na caixa Mensagem de entrada. Pressione ENTER para iniciar uma nova linha na mensagem. O texto deve ser digitado nesta área para que a mensagem seja exibida. No exemplo da janela preenchida o resultado na planilha ficará como exibido abaixo:

Fone: 112084-0070


Pag.: 76

Microsoft Excel 2013 - Avançado Alerta de Erro Para exibir um Alerta de Erro siga o procedimento abaixo: 

Clique na guia Alerta de erro da janela de Validação de Dados.



Certifique-se de que a caixa de seleção Mostrar alerta de erro após a inserção de dados inválidos esteja marcada.



Na caixa Estilo, escolha Parar/Aviso/ Informações.



Se você desejar exibir um título na barra de título da mensagem ou no balão do Assistente do Office, se o Assistente do Office estiver exibido, digite o texto na caixa Título. Se você deixar a caixa Título em branco, o título assume como padrão Microsoft Excel.



Se você desejar exibir um texto criado por você na mensagem, digite o texto na caixa Mensagem de erro, com até 225 caracteres. Pressione ENTER para iniciar uma nova linha na mensagem. Se você não inserir um texto na caixa Mensagem de erro, a mensagem exibirá o seguinte: "O valor inserido não é válido. Outro usuário restringiu os valores que podem ser inseridos nesta célula."

entre

Estilos de Alerta de Erro Ícone

Tipo

Use para

Parar

Impede que os usuários insiram dados inválidos em uma célula. Uma mensagem de alerta Parar tem duas opções: Repetir ou Cancelar.

Aviso

Avisa os usuários de que os dados inseridos são inválidos, mas não os impede de inserir esses dados. Quando uma mensagem de alerta de Aviso é exibida, os usuários podem clicar em Sim para aceitar a entrada inválida, em Não para editá-la ou em Cancelar para removê-la.

Informações

Informa os usuários de que os dados inseridos são inválidos, sem impedir a inserção desses dados. Esse tipo de alerta de erro é o mais flexível. Quando uma mensagem de alerta de Informações é exibida, os usuários podem clicar em OK para aceitar o valor inválido ou em Cancelar para rejeitá-lo.

Fone: 112084-0070


Caixa de diálogo

Pag.: 77

Microsoft Excel 2013 - Avançado Exemplos de Validação de dados Para podermos praticar iremos criar uma planilha de Lançamento de Despesas conforme modelo abaixo:

Na célula B2 iremos inserir a função =HOJE() para exibir a data atual. Na coluna J iremos criar uma lista com os Tipos de Despesas. Esta lista será utilizada mais adiante.

Na planilha anterior iremos selecionar a lista com os nomes dos vendedores e nomear para Lista_Vendedores através da Caixa de nome. Depois de nomeada a lista retorne a planilha de Lançamento de Despesas para iniciarmos as validações de dados. 1ª Validação – Campo Data 

Selecione o intervalo que conterá as datas de lançamento;






Na janela que se abre na guia Configurações iremos configurar as restrições para este campo conforme abaixo: Permitir: Data Dados: é menor do que (para lançar apenas despesas já geradas) Data de Término: =$B$2 – só serão aceitas datas anteriores a data atual da célula. A célula precisa ser fixada para que o critério seja o mesmo para todas as células do campo Data.



Se quiser clique na guia Mensagem de entrada e personalize uma mensagem para quando o usuário clicar no campo Data.

Fone: 112084-0070


Pag.: 78




Crie um alerta de erro com o Estilo de Parar para não aceitar datas fora da restrição;



Clique em OK e teste a validação para ver se esta funcionando corretamente.



Se a data não for anterior a atual será exibida a janela de Erro.

2ª Validação – Campo Valor 

Selecione o campo Valor que conterá o Valor da despesa que deverá estar entre 0 e R$ 1.000,00;






Na janela que se abre na guia Configurações iremos configurar as restrições para este campo conforme abaixo: Permitir: Decimal Dados: está entre

Fone: 112084-0070

Mínimo: 0

Máximo: 1000


Pag.: 79




Clique em OK e teste a validação. Se quiser pode personalizar a Mensagem de Entrada e Alerta de Erro.

3ª Validação – Campo OK 

Selecione o campo OK que conterá duas opções SIM ou NÃO para identificar as despesas que já foram lançadas no sistema;






Na janela que se abre na guia Configurações iremos configurar as restrições para este campo conforme abaixo: Permitir: Lista Fonte: Digite os valores para a lista (SIM;NÃO)

Se os textos digitados na fonte da lista não são 100% seguros pois o usuário poderá digitar um dos valores da lista com espaço e o Excel aceitará o que poderá causar transtornos na hora de utilizar alguma função ou ferramenta de analise uma vez que o Excel diferencia valores com espaço e sem. 

Verifique se a opção Menu suspenso na célula esta marca para que seja criada uma lista suspensa com as opções digitas em cada célula do campo.




Fone: 112084-0070


Pag.: 80

Microsoft Excel 2013 - Avançado 4ª Validação – Campo Despesas Outra forma de validar as células utilizando lista é selecionar a lista previamente digitada na planilha. É o que vamos fazer agora para o campo Despesas: 

Selecione o campo Despesas;






Na janela que se abre na guia Configurações iremos configurar as restrições para este campo conforme abaixo: Permitir: Lista Fonte: Selecione a lista de despesas que se encontra no intervalo de =$J$2:$J$8







Você Sabia?

Para a validação por lista utilizando um intervalo de células você pode digitar todas as listas na planilha e utiliza-las na validação depois se quiser pode ocultar as listas para que o usuário não tenha o e possa modificar.

5ª Validação – Campo Vendedor Outra forma de validar as células utilizando lista é utilizar um intervalo previamente nomeado. Ex: Lista_Vendedores. 

Selecione o campo Vendedor;






Na janela que se abre na guia Configurações iremos configurar as restrições para este campo conforme abaixo: Permitir: Lista Fonte: Digite = Lista_Vendedores desta forma as células serão validadas de acordo com a lista da outra planilha.

Fone: 112084-0070


Pag.: 81








OBS: Se os dados da planilha que contem a lista de nomes forem alterados a lista de validação será atualizada automaticamente. 6ª Validação – Campo Nº da Nota As vezes nos deparamos com um campo onde não podemos ter valores repetidos, como por exemplo, o campo Nº da Nota que não pode ser repedido pois a mesma nota não pode ser lançada 2 vezes na mesma planilha. Para evitar este problema iremos validar o campo utilizando o critério Personalizado e inserindo uma fórmula para verificar se o valor digitado já existe no intervalo. 

Selecione o campo Nº da Nota;






Na janela que se abre na guia Configurações iremos configurar as restrições para este campo conforme abaixo: Permitir: Personalizado Fórmula: Digite =SE(CONT.SE($C$5:$C$10;C5)>1;FALSO;VERDADEIRO) Ou utilize a fórmula: =NÃO(CONT.SE($C$5:$C$10;C5)>1) Onde o valor digitado C5 será contado no intervalo do campo $C$5:$C$10 e se a quantidade for superior a 1 a validação será FALSO e o valor não será aceito caso contrario o mesmo será VERDADEIRO e assim permitido.

Fone: 112084-0070


Pag.: 82






Circular Dados Inválidos É possível aplicar a validação de dados a células que já possuem dados inseridos. Entretanto, o Excel não o notificará automaticamente de que as células existentes contêm dados inválidos. Nesse cenário, você pode realçar os dados inválidos fornecendo instruções para que o Excel os circule na planilha. Após a identificação dos dados inválidos, é possível ocultar novamente os círculos. Se você corrigir uma entrada inválida. o círculo desaparecerá automaticamente. Para Circular os dados inválidos siga os procedimentos abaixo: 

Clique na guia Dados, grupo Ferramentas de Dados, clique na seta ao lado da Validação de Dados e em seguida clique na opção Circular Dados Inválidos;

Neste momento todos os dados inválidos serão circulados de vermelho como no exemplo abaixo.

A opção de Circular Dados Inválidos nas versões anteriores ao Excel 2007 se encontra junto com as Ferramentas de Auditoria de Fórmulas.

Fone: 112084-0070


Pag.: 83


Planilhas Tridimensionais e Vínculos O Excel é uma planilha tridimensional, isto é, além das duas dimensões já conhecidas (linhas e colunas) ganhou mais algumas planilhas, que são outras planilhas dentro de cada pasta. A grande vantagem da utilização de planilhas em 3D é a facilidade das consolidações e a istração das mesmas. Você pode duplicar o conteúdo de uma planilha para outra usando o seguinte procedimento: Selecione todas as planilhas mantendo a tecla SHIFT pressionada e clicando sobre as planilhas desejadas criando com isso um grupo. Quando as planilhas estão agrupadas na barra de título ao lado do nome do arquivo aparece a palavra [Grupo]. Quando as planilhas estão agrupadas devemos digitar os dados comuns para todas as planilhas do grupo, pois a alteração feita em uma será aplicada nas demais.

Grupo de Planilhas Planilhas agrupadas com a tecla SHIFT

Para desagrupar as planilhas clique sobre uma das planilhas selecionadas e clique na opção Desagrupar Planilhas a partir de agora as planilhas voltam a ser individuais.

Para copiar as planilhas mantenha a tecla CTRL pressionada enquanto arrasta a guia da planilha. Quando soltar o mouse terá uma copia idêntica da planilha selecionada. Você Sabia?

Fone: 112084-0070


Pag.: 84

Microsoft Excel 2013 - Avançado O exemplo utilizaremos 3 planilhas com o Total das vendas de cada Vendedor nos anos de 2009, 2013 e 2011 e uma ultima planilha para a consolidação dos dados.

A consolidação entre as planilha tem a sintaxe a seguir: =nome-da-planilha!célula [operador aritmético] nome-da-planilha!célula

Na planilha Consolidação, no campo Qtde de Pedidos a fórmula ficaria assim : ='2009'!B3 + '2013'!B3 + '2011'!B3

No campo Total a fórmula ficaria assim: ='2009'!C3 + '2013'!C3 + '2011'!C3

Se você tiver varias planilhas e todas tiverem a mesma estrutura podemos utilizar funções para agilizar e simplificar as fórmulas: =SOMA('2009:2011'!C3)

Fone: 112084-0070


Pag.: 85


Vincular Células entre Arquivos Para vincular células entre arquivos o procedimento mais simples é o seguinte: 

Abra os dois arquivos que serão vinculados;



Crie em um dos arquivos a fórmula ou referência que será utilizada iniciando com o sinal de =;



Alterne para o outro arquivo, clique na célula que será vinculada e pressione ENTER para voltar ao primeiro arquivo.

A sintaxe para vincular células entre arquivos é a seguinte: =’Caminho(Drive:\Pasta)\[Nome-do-Arquivo] Planilha’!Célula Ou pode-se copiar os valores da planilha de origem e na planilha de destino clique na célula onde deseja colar e clique na guia Página Inicial, no grupo Área de Transferência, clique em Colar Especial e em seguida, Colar Vínculo.

Desta forma os valores serão colados com os vínculos e toda vez que os valores forem alterados na planilha de origem serão alteradas na planilha de destino. Se preferir pode clicar direto na opção Colar Vínculo na guia Página Inicial, no grupo Área de Transferência, no botão colar escolher a opção.

Quando trabalhamos com fórmulas vinculadas a outras planilhas ou arquivos temos sempre que lembrar que se fizermos qualquer alteração nas base de dados a planilha que possui o vínculo será atualizada. Por isso cuidado para não renomear, mover ou excluir alguma célula ou arquivo pois isto causará erros nas fórmulas vinculadas. O erro que aparecerá é #REF!  Ocorre quando você utilizou uma referência errada.

Desafio!

Vá até o capítulo de DESAFIOS e tente resolver o Desafio 01 utilizando os conhecimentos adquiridos até o momento. Se necessário solicite orientações e dicas para seu instrutor para ajudar na solução do Desafio. Quando tiver a solução solicite ao instrutor que corrija ou envie por e-mail para: [email protected]


Fone: 112084-0070


Pag.: 86


AULA 3 Classificação Desde a versão 2007 do Excel a possibilidade de Classificação de dados foi ampliada de 3 para 64 níveis. Você pode classificar uma lista simplesmente clicando em uma célula da lista e depois nos botões de classificação da barra de ferramentas. Através dos botões: você pode classificar listas em ordem crescente (1 a 9, A a Z) ou decrescente (9 a 1, Z a A), e classificar com base no conteúdo de uma ou mais colunas.

Caso queira personalizar sua classificação ou utilizar mais de uma coluna, você deve selecionar a opção Classificar da guia Dados no grupo Classificar e Filtrar. Você encontra a opção de Personalizar Classificação também na guia Página Inicial no grupo Edição.

Basta escolher a Coluna, Tipo de valores e a Ordem de classificação. Caso deseje classificar por mais de uma coluna clique no botão Adicionar Nível para incluir a linha E depois por para que se possa personalizar o próximo nível de classificação.

Você Sabia?

Desde a versão 2007 do Excel a classificação de dados identifica o tipo de valor da lista e na opção Ordem indica a forma de classificação. Se forem valores numéricos aparecerá Do Maior para o Menor ou Do Menor para o Maior, se forem tipo Texto aparecerá De A a Z ou De Z a A e para lista de datas aparecerá Do Mais Antigo para o Mais Novo ou Do Mais Novo para o Mais Antigo.

Fone: 112084-0070


Pag.: 87

Microsoft Excel 2013 - Avançado Na maioria dos casos, usa-se a classificação vertical (de cima para baixo) que até esquecemos da possibilidade da classificação horizontal (da esquerda para a direita) como veremos mais abaixo. Vamos utilizar as listas personalizadas e as opções para a classificação dos dados das tabelas abaixo:

Observe, na figura acima que temos tabelas horizontais e verticais e todas devem ser classificadas pela primeira coluna ou primeira linha. Veremos dois exemplos. Os demais você conseguirá fazer facilmente. Você pode reordenar as linhas ou colunas de uma lista com base nos valores de uma lista através da classificação. Selecione o intervalo I2:J14 e, em seguida, clique na guia Dados / Classificar. Escolha a coluna Meses (caso não tenha aparecido o nome do campo verifique se a opção Meus dados contêm cabeçalhos esta marcada). Na caixa Ordem clique na seta à direita e escolha Lista personalizada.

Selecione a lista dos nomes dos meses por extenso e clique em OK. A lista utilizada tem que ser da mesma forma que os dados digitados na planilha.

Fone: 112084-0070


Pag.: 88

Microsoft Excel 2013 - Avançado Selecione o intervalo D2:G5 e, na guia Dados clique em Classificar. Clique no botão Opções e ative a orientação Da esquerda para a direita e clique no botão OK.

Selecione a Linha para o primeiro nível de classificação no nosso caso a Linha 2,. Na Ordem de classificação utilizaremos a lista personalizada de pontos cardeais importadas na aula anterior.

Pronto a tabela será classificada de acordo com a lista utilizadas.

Você Sabia?

Quando quiser selecionar uma base de dados (tabela) inteira basta utilizar a tecla de atalho CTRL+* . Utilizando esta tecla a tabela toda será selecionada e a célula ativa será a primeira célula do intervalo.

Fone: 112084-0070


Pag.: 89


AutoFiltro O uso do AutoFiltro para filtrar dados é um modo rápido e fácil de localizar e trabalhar com um subconjunto de dados em um intervalo de células ou coluna de tabela. Os dados filtrados exibem somente as linhas que atendem aos critérios especificados e oculta as linhas que não deseja exibir. Depois de filtrar os dados, você pode copiar, localizar, editar, formatar, fazer gráfico e imprimir o subconjunto de dados filtrados sem reorganizá-los nem movê-los. Você também pode filtrar por mais de uma coluna. Os filtros são aditivos, o que significa que cada filtro adicional baseia-se no filtro atual e ainda reduz o subconjunto de dados. Utilizando AutoFiltro, você pode criar três tipos de filtro: por valores de uma lista, por um formato ou por critérios. Cada um desses tipos de filtros é exclusivo para cada intervalo de célula ou tabela de coluna. Por exemplo, você pode filtrar por cor de célula ou por uma lista de números, mas não pelos dois; é possível filtrar por ícone ou por um filtro personalizado, mas não pelos dois.

Você Sabia?

Desde a versão 2007 do Excel o AutoFiltro foi aprimorado possibilitando filtra pela cor da Célula ou do Texto, por Ícones quando estes foi inseridos através da formatação condicional e também permite a seleção de Múltiplos Valores como critérios.

Fone: 112084-0070


Pag.: 90


Filtro Avançado O Filtro Avançado é de extrema importância na hora de trabalhar com grande quantidade de dados. Pois podemos analisar os dados aplicando vários critérios e a copiar os dados correspondentes para um outro local. Podemos ainda extrair registros exclusivos de uma base de dados caso a mesma tenha informações duplicadas. Como exemplo utilizaremos a tabela de Pedidos que deve ser moneada para Pedidos através da Caixa de Nome. Não se esqueça de selecionar toda a tabela antes de nomear.

Selecione a coluna do campo País e nomeie para Países e depois nomeie o campo Vendedor para Vendedores. Os nomes dados a tabela e aos campos ajudaram na hora de referenciar a base de dados no Filtro Avançado. A lista ou base de dados a ser utilizada no Filtro Avançado deverá conter rótulo de colunas (Ex: Pedido, Data, Cliente, País, etc).

Em outra planilha digitaremos os critérios a serem utilizados no Filtro Avançado e será nesta planilha também que os dados serão colados. Os cabeçalhos dos critérios devem ser exatamente iguais ao da base de dados para que o Excel posso fazer a correspondência.

A primeira consulta que iremos fazer são dos Pedidos do Brasil. Para isso utilizaremos o critério País=Brasil. 

Para utilizar o Filtro Avançado siga os procedimentos abaixo:



Clique em uma área qualquer da planilha que não contenha dados;



Clique na guia Dados, no grupo Classificar e Filtrar clique na opção Avançado;

Fone: 112084-0070


Pag.: 91


Na janela que se abre iremos realizar as configurações do Filtro;



Onde:





Ação: Se Filtra a lista no local na própria planilha ou Copia para um outro Local (selecione conforme desejado);



Intervalo da lista: É a referencia do banco de dados. Selecione todos os dados digitados ou informe o nome da base de dados. Ex: Pedidos.



Intervalo de critérios: São as regras de filtragem. É necessário fazer uma planilha de apoio onde conste os critérios. No nosso caso será o intervalo $A$1:$A$2.



Copiar para: Se habilitado a opção copiar para outro local, identifique o novo local em sua planilha. No nosso caso $A$8. O Filtro não sobrepõem dados por isso escolha uma área vazia.



Somente registros exclusivos: Se habilitado irá ignorar os registros duplicados colando apenas um só.

Após configurado clique em OK e os dados que correspondam ao critério informado serão colados no local indicado.

Quando o Filtro Avançado cola as informações em outro local os mesmos são colados como valores, ou seja, não serão coladas fórmulas e não haverá vinculo com a base de dados. O botão de Desfazer também não funcionará caso os dados sejam colados errados.

Agora realize os filtros para os demais critérios. Se preferir pode excluir os registros anteriores para colocar os novos ou criar cada critério em uma planilha separada.

Fone: 112084-0070


Pag.: 92

Microsoft Excel 2013 - Avançado Abaixo iremos deixar os critérios e as janelas com suas respectivas configurações. 1. Todos os pedidos dos clientes que tenham o nome iniciados com a letra A:

2. Todos os pedidos do Brasil efetuados pela Janete:

3. Todos os pedidos efetuados no período de 01/01/2011 à 10/01/2011:

Fone: 112084-0070


Pag.: 93

Microsoft Excel 2013 - Avançado 4. Todos os pedidos dos cliente com os nomes iniciados com a letra A ou B:

5. Todos os pedidos da categoria Condimentos com os Preços inferiores a R$ 30,00:

Outro exemplo de aplicação do Filtro Avançado. Na tabela de pedidos temos o campo Vendedor que renomeamos para Vendedores. Utilizando este campo podemos extrair a lista de vendedores da tabela Pedidos. Para isso siga os procedimentos: 

Crie um campo de critério em uma planilha como no exemplo abaixo. O critério será em branco pois queremos extrair todos os nomes dos vendedores;



A opção Somente registros exclusivos deverá estar marcada para que seja extraido apenas um nome de cada.

Fone: 112084-0070


Pag.: 94


Subtotais O Microsoft Excel pode calcular automaticamente valores de subtotais e totais gerais em uma lista. Quando você insere subtotais automáticos, o Excel organiza a lista em tópicos para que você possa exibir e ocultar as linhas de detalhes de cada subtotal. Para inserir subtotais, você primeiro classifica a lista para agrupar as linhas das quais deseja calcular o subtotal. Em seguida, você pode calcular subtotais de qualquer coluna que contenha números. Se os dados não estiverem organizados como uma lista ou se você precisar somente de um único total, você poderá usar AutoSoma .

Aplicando o recurso Subtotal Para o nosso exemplo utilizaremos a planilha de Pedidos para gerarmos alguns Subtotais. 

Selecione a planilha correspondente;



Classifique os dados pelas colunas Vendedor e Categoria. Para isso, clique na guia Dados, Classificar. Na Caixa de diálogo Classificar, classifique por Vendedor e, em seguida por Categoria. Use classificação Ascendente (De A a Z) para os dois casos.



A figura a seguir mostra como ficarão os dados.



Após a classificação, item fundamental, selecione a tabela novamente caso não esteja selecionada e clique na guia Dados e no grupo Estrutura de Tópicos clique na opção Subtotal.

Fone: 112084-0070


Pag.: 95


Na caixa de diálogo Subtotal, iremos configurar as opções para o primeiro subtotal.

 Na caixa A cada alteração em, clique na coluna para calcular o subtotal. Como no exemplo, que selecionaremos Vendedor.  Na caixa Usar função, clique na função de resumo que deseja usar para calcular os subtotais. Como no exemplo, que selecionaremos Soma. São ao todo 11 funções resumo para o Subtotal.  Na caixa Adicionar subtotal a, marque a caixa de seleção para cada coluna que contém valores que você deseja subtotalizar. Como no exemplo, que selecionaremos Frete e Total.  Se quiser uma quebra de página automática depois de cada subtotal, marque a caixa de seleção Quebra de página entre grupos.  Para especificar uma linha de resumo acima da linha de detalhes, desmarque a caixa de seleção Resumir abaixo dos dados. Para especificar uma linha de resumo abaixo da linha de detalhes, marque a caixa de seleção Resumir abaixo dos dados. Como no exemplo, deixaremos marcada a opção.  Opcionalmente, você pode usar o comando Subtotais novamente repetindo as etapas de anteriores para adicionar mais subtotais com diferentes funções de resumo. Para evitar sobrescrever os subtotais existentes, desmarque a caixa de seleção Substituir subtotais atuais. 

Após configurar as opções para o Subtotal clique em OK e verifique o resultado.

No exemplo mostrado os dados foram resumidos através dos Botões de Níveis da Estrutura de Tópicos a esquerda das linhas. Como a base de dados é muito extensa os níveis de grupo ajudam a resumir os dados. No exemplo foi clicado no nível 2 para exibir os subtotais por vendedor. 

O Nível 1 exibe somente o Total Geral;



O Nível 2 exibe os resumos dos subtotais. No exemplo o total de cada Vendedor;



O Nível 3 exibe todos os dados.

Fone: 112084-0070


Pag.: 96

Microsoft Excel 2013 - Avançado A quantidade de níveis depende de quantos subtotais e agrupamentos foram criados na base de dados. Agora iremos criar mais um subtotal para a Tabela de Pedidos. Agora para o campo Categoria para que possamos ver quanto cada Vendedor vendeu por Categoria. Para isso devemos: 

Selecionar a base de dados ou deixar o cursos em um dos campo da base de dados;



Clique na guia Dados e no grupo Estrutura de Tópicos clique na opção Subtotal;



Configure as opções de acordo com a tela abaixo:



Note que o item Substituir subtotais atuais foi desativado e que A cada alteração em foi alterado para Categoria. Isto é importante para que você possa criar subtotais por Vendedor e por Categoria.



Após você aplicar os recursos descritos acima, sua planilha terá a seguinte aparência:

Onde, se você clicar num dos botões ou nos botões ou você verá um layout diferente. Ou seja, com o recurso Subtotal, você tem uma única tabela mas com diversas visualizações possíveis. Para remover os Subtotais devemos:

Removendo o Subtotal 

Selecionar a base de dados ou deixar o cursos em um dos campo da base de dados;



Clique na guia Dados e no grupo Estrutura de Tópicos clique na opção Subtotal;



Na janela de Subtotal clicar no botão Remover todos.

Fone: 112084-0070


Pag.: 97


Estrutura de Tópicos Quando criamos um Subtotal a ferramenta automaticamente cria um Estrutura de Tópicos a esquerda da tabela com os Botões de níveis e de Expandir ou Recolher tópicos. Mas podemos também criar as Estruturas de Tópicos manualmente Agrupando as colunas ou linhas. Em nosso exemplo utilizaremos a Estrutura de Tópicos para Agrupar as colunas vazias que aparecem quando os dados estão resumidos como na figura acima. Para isso devemos: 

Selecionar as colunas que devem ser agrupadas no nosso caso as colunas de Pedido, Data, Cliente e País;



Clique na guia Dados, grupo Estrutura de Tópicos, clique no botão Agrupar;



Agora, é só clicar no botão de Recolher Tópico para ver os resultados.



Repita o procedimento para as colunas CodProd e Produto e depois para as colunas Preço e Quantidade;



Ao Recolher os Tópicos sua tabela terá a seguinte aparência:



Quando a planilha for impressa a mesma sairá como exibida na tela.

que aparece sobre as colunas Agrupadas

Se desejar excluir a Estrutura de tópicos, selecione o Intervalo que deseja Desagrupar e clique na guia Dados, grupo Estrutura de Tópicos clique no botão Desagrupar.

Caso deseje Ocultar ou Reexibir os botões de Estrutura de Tópicos utilize a tecla de atalho CTRL+8. Você Sabia?

Fone: 112084-0070


Pag.: 98


Função SUBTOTAL Se você observar a fórmula criada para cada subtotal verá a função SUBTOTAL. Na função há um número que identifica o tipo do cálculo a ser aplicado.

SUBTOTAL Retorna um subtotal em uma lista ou em um banco de dados. É geralmente mais fácil criar uma lista com subtotais usando o comando Subtotais, grupo Contorno, na guia Dados no aplicativo de desktop do Excel. Assim que a lista de subtotais for criada, você poderá modificá-la editando a função SUBTOTAL. =SUBTOTAL(núm_função;ref1;[ref2];...]) NÚM_FUNÇÃO: Obrigatório. É o número de 1 a 11 (incluindo valores ocultos) ou 101 a 111 (ignorando valores ocultos) que especifica qual função usar no cálculo de subtotais dentro de uma lista. Ref1: Obrigatório. O primeiro intervalo nomeado ou referência cujo subtotal você deseja. Ref2,...: Opcional. Intervalos nomeados ou referências de 2 a 254 cujo subtotal você deseja. A seguir tem a lista com as 11 funções resumo do SUBTOTAL. Núm_função) 1 2 3 4 5 6 7 8 9 10 11

Núm_função 101 102 103 104 105 106 107 108 109 110 111

Função MÉDIA CONTAR NÚMEROS* CONTAGEM* MÁX MÍN PRODUTO DESVPAD DESVPADP SOMA VAR VARP

* CONTAR NÚMEROS Igual a CONT.NÚM e CONTAGEM igual a CONT.VALORES

Função SUBTOTAL e AutoFiltro Outra maneira de se utilizar a função de SUBTOTAL é junto com a ferramenta AutoFiltro. Utilizando a tabela de Pedidos iremos criar alguns campos de resumo dos dados, como mostrado abaixo. Na tabela de dados coloque o Filtro. Selecione o 1º campo da tabela clique na guia Dados, Filtro.

Agora iremos criar as fórmulas correspondentes para cada campo da tabela de Resumo. A fórmulas estão descritas abaixo:

Fone: 112084-0070

Total Geral:

=SUBTOTAL(9;L8:L2162)

Nº de Pedidos:


Média:



Pag.: 99

Microsoft Excel 2013 - Avançado Qual a vantagem em se criar uma tabela com Funções de SUBTOTAL e adicionar Filtro a base de dados? A vantagem é que agora toda vez que a base de dados for filtrada por algum critério os resumos se atualizarão automaticamente. No exemplo a tabela foi filtrada pelo País e Vendedor:

Copiando apenas células visíveis Caso seus dados tenham a aparência a seguir e você deseje copiá-los para outro local, não basta copiar e colar. Para isto, após selecionar, siga os os:



Pressione a tecla F5;



Na janela Ir para clique no botão Especial;



Na janela seguinte Ir para especial marque a opção Somente células visíveis;



Serão selecionadas somente as células visíveis;



Copie e cole no local de destino desejado;



Os dados serão colados como valores não possuindo fórmulas.

Para selecionar somente as células visíveis pode ser utilizada a tecla de atalho: ALT + ; Você Sabia?

Fone: 112084-0070


Pag.: 100


Gerar Arquivos PDF ou XPS Agora você pode exportar dados para um formato PDF (Portable Document Format) ou XPS (XML Paper Specification) para impressão, lançamento e distribuição por e-mail, desde que você primeiramente instale o Publish como suplemento de PDF ou XPS. A exportação de uma planilha para um arquivo PDF ou XPS permite que você capture informações facilmente distribuído que mantém todas as suas características de formatação, mas não exige que outras pessoas tenham o Excel instalado em seus computadores para imprimir ou revisar sua saída. Será possível exportar para um arquivo de formato PDF ou XPS a partir do Office 2007 somente após a instalação do suplemento. Seguem algumas informações sobre os formatos de arquivo: PDF (Portable Document Format) o PDF é um formato de arquivo eletrônico de layout fixo que mantém a formatação do documento e habilita o compartilhamento de arquivos. O formato PDF assegura que na exibição ou impressão do arquivo online, seja mantido exatamente o formato planejado e que os dados do arquivo não possam ser facilmente copiados ou alterados. O formato PDF também é útil para documentos que serão reproduzidos por métodos de impressão comercial. XPS (XML Paper Specification) O XPS é um formato de arquivo eletrônico que mantém a formatação do documento e habilita o compartilhamento de arquivos. O formato XPS assegura que na exibição ou impressão do arquivo online, ele mantenha exatamente o formato planejado e que os dados do arquivo não possam ser facilmente copiados ou alterados.

Para Gerar um arquivo em PDF siga as instruções abaixo: 

Clique na planilha a ser Exportada;



Clique no Menu Arquivo, em seguida clique em clique na opção Salvar e Enviar, clique em Criar Documento PDF/XPS e clique no botão Criar PDF/XPS;



Na janela que se abre selecione o local onde será salvo o arquivo. Renome o arquivo;

Fone: 112084-0070


Pag.: 101




Clique no botão Opções caso queira modificar alguma propriedade;



Clique em OK e em seguida no botão Publicar. Pronto o arquivo será criado.

Caso a ferramenta de PDF or XPS não esteja instalada basta fazer o do suplemento no site da Microsoft e instalar o plug-in. Após a instalação precisa fechar o aplicativo e abrir novamente para ter a ferramenta disponível. A instalação adiciona a ferramentas em todos os aplicativos do Microsoft Office.

Fone: 112084-0070


Pag.: 102


Funções de Banco de Dados O Microsoft Excel inclui 12 funções de planilha que analisam os dados armazenados em listas ou bancos de dados. Cada uma dessas funções, citadas genericamente como BDfunções, usa três argumentos: banco de dados, campo e critérios. Esses argumentos se referem aos intervalos de planilha usados pela função. =BDFUNÇÃO(BANCO_DADOS;CAMPO;CRITÉRIOS) Banco_dados: é o intervalo de células que constitui a lista ou o banco de dados. Campo: indica a coluna que será usada na função. As colunas de dados na lista devem ter um rótulo de identificação na primeira linha. O campo pode ser dado como texto com o rótulo de coluna entre aspas, ou como um número que represente a posição da coluna na lista: 1 para a primeira coluna, 2 para a segunda e assim por diante. Critérios: é uma referência a um intervalo de células que especifica as condições para a função. A função retorna informações da lista que atendem às condições especificadas no intervalo de critérios. O intervalo de critérios inclui uma cópia do rótulo da coluna na lista para a coluna que você deseja que a função resuma. A referência de critérios pode ser inserida como um intervalo de células, como por exemplo A1:F2, ou como um nome que represente o intervalo, como "Critérios".

BDCONTAR Conta as células contendo números em uma coluna de uma lista ou banco de dados que coincidirem com as condições especificadas. O argumento de campo é opcional. Se o campo for omitido, BDCONTAR contará todos os registros no banco de dados que coincidirem com os critérios.

BDCONTARA Conta todas as células não vazias em uma coluna de uma lista ou banco de dados que coincidirem com as condições especificadas.

BDMAX Retorna o maior número em uma coluna de uma lista ou banco de dados que coincidir com as condições especificadas por você.

BDMIN Retorna o menor número em uma coluna de uma lista ou banco de dados que coincidir com as condições especificadas.

BDSOMA Soma os números em uma coluna de uma lista ou banco de dados que coincidirem com as condições especificadas.

BDMÉDIA Calcula a média dos valores em uma coluna de uma lista ou banco de dados que coincidirem com as condições especificadas.

BDMULTIPL Multiplica os valores em uma coluna de uma lista ou banco de dados que coincidirem com as condições especificadas.

Fone: 112084-0070


Pag.: 103

Microsoft Excel 2013 - Avançado BDEST Estima o desvio padrão de uma população com base em uma amostra, usando os números em um campo (coluna) de registros em uma lista ou banco de dados que coincidirem com as condições especificadas.

BDDESVPA Calcula o desvio padrão de uma população com base na população total, usando os números em um campo (coluna) de registros em uma lista ou banco de dados que coincidirem com as condições especificadas.

BDVAREST Estima a variância de uma população com base em uma amostra, usando os números em um campo (coluna) de registros em uma lista ou banco de dados que coincidem com as condições especificadas.

BDVARP Calcula a variância de uma população com base na população total, usando os números em um campo (coluna) de registros em uma lista ou banco de dados que coincidem com as condições especificadas.

BDEXTRAIR Extrai um único valor em uma coluna de uma lista ou banco de dados que coincide com as condições especificadas.

Exemplos de Aplicação das Funções de Banco de Dados Como exemplo para as funções de Banco de Dados utilizaremos a base de dados Pedidos e criaremos os critérios a serem utilizados na analise. Abaixo as funções e enunciados. Na página seguinte você encontra a planilha com os resultados e modelo para a planilha de analises.  Na Célula F4  =BDCONTAR(Pedidos;"Pedido";A3:B4) ou =BDCONTAR(Pedidos;;A3:B4)

 Na Célula F9  =BDCONTARA(Pedidos;“Cliente”;A8:B9) ou =BDCONTARA(Pedidos;;A8:B9)

 Na Célula F14  =BDCONTARA(Pedidos;;A13:B15) ou =BDCONTARA(Pedidos;“Cliente”;A13:B15)

Fone: 112084-0070


Pag.: 104

Microsoft Excel 2013 - Avançado  Na Célula F20  =BDMÁX(Pedidos;"Frete";A19:B20)

 Na Célula F25  =BDMÍN(Pedidos;"Preço";A24:C25)

 Na Célula F30  =BDMÉDIA(Pedidos;"Total";A29:D30)

 Na Célula F35  =BDSOMA(Pedidos;"Total";A34:C35)


Fone: 112084-0070


Pag.: 105


Fone: 112084-0070


Pag.: 106


AULA 4 Tabela Dinâmica Tabela dinâmica é uma tabela interativa que resume uma grande quantidade de dados rapidamente, ou os combina de tabelas diferentes. É possível girar suas linhas e colunas para ver resumos diferentes dos dados de origem, filtrar os dados exibindo páginas diferentes ou exibir os detalhes das áreas de interesse. Você pode criar uma Tabela dinâmica a partir de uma Lista ou Banco de dados do Microsoft Excel, de um Banco de dados externo, de Várias planilhas do Microsoft Excel ou de outra Tabela dinâmica. Para criar uma Tabela dinâmica. Para começar utilizaremos a base de dados Pedido.

Para criar a Tabela Dinâmica, primeiro clique sobre uma célula da lista ou selecione toda base de dados e siga os procedimentos: 

Clique na guia Inserir e grupo Tabelas clique em Tabela Dinâmica;



Na janela que se abre podemos utilizar devemos selecionar a fonte de dados a ser utilizada no nosso caso a tabela Pedidos.



Escolha o local onde deseja colocar a tabela dinâmica: 

Nova Planilha: Cria uma nova planilha na mesma pasta de trabalho para o relatório de tabela dinâmica.

Fone: 112084-0070


Pag.: 107

Microsoft Excel 2013 - Avançado 



Planilha Existente: Selecione a célula na planilha ou digite uma referência de célula na caixa Planilha existente para especificar a célula superior esquerda do intervalo na planilha onde você deseja que o relatório de tabela dinâmica seja posicionado. O botão Recolher caixa de diálogo, posicionado na extrema direita desta caixa, moverá temporariamente a caixa de diálogo, para que você insira o intervalo, selecionando células na planilha. Quando você terminar, poderá clicar novamente no botão para exibir a caixa de diálogo inteira.

Clique no botão OK.

O Layout acima é o Novo layout padrão do Excel desde a versão 2007 mas o mesmo só estará com este layout se a pasta de trabalho estiver com a extensão .xlsx. Se a pasta de trabalho estiver com a extensão .xls será exibido o Layout Clássico de Tabela dinâmica idêntico ao das versões anteriores ao 2007.

Para mudar o Layout Padrão da Tabela Dinâmica para o Layout Clássico siga os procedimentos abaixo: 

Clique no meio da área da Tabela Dinâmica para exibir as Ferramentas de Tabela Dinâmica;



Clique na guia Opções das Ferramentas de Tabela Dinâmica e no grupo Tabela Dinâmica clique em Opções;

Fone: 112084-0070


Pag.: 108


Na janela que se abre clique na guia Exibição e marque a opção Layout clássico de tabela dinâmica (permite arrastar campos na grade) em seguida clique em OK.

Neste momento o Layout da Tabela Dinâmica estará igual ao das versões anteriores ao 2007.

Você Sabia?

Se você já utilizou a Ferramenta de Tabela Dinâmica nas versões anterior a 2007 de se lembrar que o Excel possuía um Assistente de Tabela Dinâmica e Gráfico Dinâmico que auxiliava na criação da Tabela ou Gráfico. Na Versão 2007 ou 2013 do Excel se você quiser pode abrir este Assistente através de da tecla de atalho ALT+D+A e utilizar o Assistente com nas versões anteriores a 2007.

Fone: 112084-0070


Pag.: 109

Microsoft Excel 2013 - Avançado Agora para montar a Tabela Dinâmica basta arrastar os campos de acordo com os dados que desejamos analisar e montar a melhor estrutura para expô-los.. Para auxiliar do lado direito da janela foi aberto o Lista de campos de tabela dinâmica que contem todos os campos disponíveis na base de dados e que podem ser utilizados na analise. Este só aparece se o cursor estiver em algum local da área da tabela dinâmica. Caso o cursor esteja na área da tabela dinâmica e mesmo assim o não seja exibido clique na guia Opções das Ferramentas de Tabela Dinâmica e no grupo Mostrar clique no item Lista de Campos.

Vamos montar nossa tabela dinâmica. Como no exemplo abaixo:

Os campos podem ser arrastados diretamente para as áreas de campo disponíveis na Tabela dinâmica ou para um os quadros do Lista de campos da tabela dinâmica como podemos ver abaixo na figura:

Fone: 112084-0070


Pag.: 110

Microsoft Excel 2013 - Avançado Para mudar a forma de visualização de sua tabela, basta clicar sobre um dos campos de título de coluna ou linha e arrastá-lo para uma nova posição.

Se o campo utilizado na área de valores for do tipo número o Excel por padrão fará a AutoSoma dos dados. Mas podemos modificar o tipo de cálculo utilizando uma das 11 funções resumo disponíveis as mesmas encontradas na ferramenta e função Subtotal. Para alterar o cálculo de um duplo clique sobre o campo Soma de Total da Tabela Dinãmica ou dê um clique sobre o campo que está no diagrama Valores, no exemplo acima “Soma de Total” e clique na opção Configurações do Campo de Valor a próxima janela será exibida:

No item Resumir campo de valor por escolha o tipo de cálculo que deseja usar para resumir os dados do campo selecionado e clique em OK. 

Formato do Número: Formata os itens numéricos no campo selecionado.

Depois de selecionadas as opções clicar em OK.

Fone: 112084-0070


Pag.: 111


Observe que no exemplo acima o cálculo da soma foi alterado pelo da contagem exibindo assim o número de pedidos de cada vendedor. Outra vantagem da Tabela dinâmica é podermos filtrar os dados através dos Rótulos de Coluna, Rótulo de Linha e Filtro de Relatório. Exibindo assim somente as informações desejadas.

OBS: No Filtro de Relatório se marcarmos a opção Selecionar Vários Itens poderemos selecionar mais de um item para ser utilizado no relatório. Nos demais campos estas opção já automática.

No exemplo acima filtramos a tabela para exibir apenas os totais do Brasil referentes ao cliente Antonio Moreno. Caso você deseja formatar a Tabela Dinâmica basta clicar na guia Design das Ferramentas de Tabela Dinâmica e no grupo Estilos de Tabela Dinâmica escolher um estilo.

Fone: 112084-0070


Pag.: 112


Outra possibilidade da Tabela Dinâmica e a de extrair dados a partir dos valores exibidos. Por exemplo na tabela acima a vendedora Ana vendeu R$ 1.418,86 de Carnes/Aves. Vamos supor que precisássemos fazer um levantamento de todos os pedidos referentes a esse total. Na Tabela Dinâmica isso é uma tarefa fácil basta dar um duplo clique sobre o valor correspondente.

Observe que o Excel cria uma nova planilha com os dados referentes ao total selecionado. Esta nova planilha não esta vinculada a base de dados por isso se algum dado for modificado você precisará dar um duplo clique novamente para ser gerado um novo resumo.

Atualizar dados da Tabela Dinâmica Quando a base de dados sofre alguma alteração a Tabela Dinâmica não se atualiza automaticamente, neste caso devemos utilizar o procedimento a seguir: 

Clique na guia Opções das Ferramentas de Tabela Dinâmica e no grupo Dados clique no comando Atualizar.

Para atualizar as fontes de dados pode utilizar as teclas de atalho:

Você Sabia?

CTRL + ALT + F5 ALT + F5

Fone: 112084-0070

 Atualiza as informações da fonte de dados selecionada.  Atualiza as informações de todas as fontes de dado da Pasta de trabalho ativa.


Pag.: 113

Microsoft Excel 2013 - Avançado Formatando a base de dados Vale lembrar que os dados só serão atualizados se inseridos entre as linhas da base de dados se os mesmo forem inseridos no final da base de dados não serão atualizadas pois os mesmos não pertencem ao intervalo inicialmente selecionado para se criar a Tabela Dinâmica. Para resolver este problema devemos antes de criar a Tabela Dinâmica formatar a base de dados como Tabela. Para fazer este procedimento siga as instruções abaixo: 

Selecione a base de dados;



Clique na guia Página Inicial, no grupo Estilo clique na opção Formatar como Tabela e escolha um dos modelos disponíveis;



Na janela seguinte irá aparecer o intervalo que será formatado e a opção Minha tabela tem cabeçalhos para que o Excel interprete a primeira linha como o Rótulo das colunas. Clique em OK.

A tabela será formatada como uma Tabela de dados e agora toda vez que forem inseridos dados no final da tabela os mesmo serão inclusos automaticamente a base de dados.

Além disso serão incluídos os botões de filtro na tabela.

Gráfico Dinâmico Para utilizarmos o recurso iremos precisar de uma tabela de dados ou uma Tabela Dinâmica já criada. Em geral, quando os dados não incluem muitos números, a tabela dinâmica funciona melhor que o gráfico para a visualização das informações. Já quando o objetivo é fazer uma análise numérica das informações, tanto a tabela como o gráfico dinâmico são úteis.

Criando Gráfico Dinâmico a partir de uma base de dados Para se construir um Gráfico Dinâmico a partir de uma base de dados devemos realizar alguns procedimento: 

Selecione a base de dados no nosso caso Pedidos e clique na guia Inserir em seguida, no grupo Tabelas, clique no comando Tabela Dinâmica e escolha a opção Gráfico Dinâmico;

Fone: 112084-0070


Pag.: 114


Na janela que se abre poderá ser indicado o local da fonte de dados. No nosso caso já estará o intervalo de dados selecionado. Nesta janela também poderemos escolher o local onde se deseja criar a tabela e o gráfico dinâmico se em uma nova planilha ou na planilha existente. Selecionaremos uma Nova planilha para que o gráfico fique separado das tabelas. Clique em OK;



Neste momento será criada uma planilha com a Tabela Dinâmica e o Gráfico Dinâmico em brancos, com a Lista de campos da tabela dinâmica;



Para facilitar nosso trabalho vamos mover o gráfico para uma nova planilha para isso clique na Área do Gráfico e clique na guia Design das Ferramentas de Gráfico Dinâmico;



Clique na opção Mover Gráfico e escolha a opção Nova Planilha e de um nome para a planilha e clique em OK;

Fone: 112084-0070


Pag.: 115


Neste momento o gráfico será movido para uma nova planilha onde iremos começar a construir o nosso gráfico;

Para criarmos o gráfico precisamos mover os campos da tabela dinâmica para as áreas desejadas utilizando o de campos para isso arraste os campos para as Áreas desejadas e vá observando o layout do gráfico se necessário altere as posições dos campos;

Para podermos analisar e filtrar os dados do Gráfico Dinâmico podemos utilizar os botões disponíveis no gráfico e que podem ser utilizados como na Tabela dinâmica. Experimente fazer outras modificações no gráfico arrastando os campos para outras posições e realize alguns filtros para testar. Assim você poderá visualizar diferentes informações.

Para remover algum campo do gráfico basta desmarcar o campo da Lista de Campos ou arrastá-lo para fora da áreas.

Fone: 112084-0070


Pag.: 116

Microsoft Excel 2013 - Avançado Criando Gráfico Dinâmico a partir de uma Tabela Dinâmica Se a você possuir uma Tabela Dinâmica e quiser gerar um gráfico o mesmo pode ser feito de maneira simples. Siga os procedimentos abaixo quando quiser criar um Gráfico Dinâmico para um Tabela Dinâmica: 

Clique em qualquer local da Tabela Dinâmica;



Clique na guia Inserir, no grupo Gráficos escolha o tipo e subtipo de gráfico que deseja;



O gráfico será inserido como objeto na planilha;



Se desejar pode mover o gráfico para uma nova planilha e modificar sua formatação.

O Gráfico Dinâmico fica vinculado a Tabela Dinâmica se a estrutura da tabela for alterada, ou seja, algum campo modificado ou alterada o cálculo o Gráfico Dinâmico também será alterado automaticamente e vice-versa. Se a base de dados for modificada deve-se atualizar o Gráfico Dinâmico ou a Tabela Dinâmica.

Fone: 112084-0070


Pag.: 117


Segmentação de Dados Em versões anteriores do Microsoft Excel, você pode usar filtros de relatório para filtrar dados em um relatório de Tabela Dinâmica, mas não é fácil ver o estado de filtragem atual ao filtrar vários itens. No Microsoft Excel 2013, você tem a opção de usar segmentações para filtrar os dados. As segmentações de dados oferecem botões nos quais você pode clicar para filtrar dados de Tabela Dinâmica. Além da filtragem rápida, as segmentações de dados também indicam o estado de filtragem atual, tornando mais fácil entender o que exatamente é mostrado em um relatório de Tabela Dinâmica filtrado. Um item, ao ser selecionado, é incluído no filtro e os dados desse item são exibidos no relatório.

O que são segmentações de dados? Segmentações de dados são componentes de filtragem fáceis de usar, com um conjunto de botões que permite filtrar rapidamente os dados de um relatório de Tabela Dinâmica, sem a necessidade de abrir listas suspensas para localizar os itens que você deseja filtrar. Ao usar um filtro de relatório de Tabela Dinâmica comum para filtrar vários itens, o filtro indica somente que vários itens foram filtrados, e você precisa abrir uma lista suspensa para localizar os detalhes da filtragem. Entretanto, uma segmentação de dados identifica de forma clara o filtro aplicado e fornece os detalhes para que você possa entender facilmente os dados exibidos no relatório de Tabela Dinâmica filtrado. Uma segmentação de dados normalmente exibe os seguintes elementos:

1. Um cabeçalho de segmentação de dados indica a categoria dos itens da segmentação. 2. Um botão de filtragem não selecionado indica que o item não está incluído no filtro. 3. Um botão de filtragem selecionado indica que o item está incluído no filtro. 4. Um botão Limpar Filtro remove o filtro selecionando todos os itens da segmentação de dados. 5. Uma barra permite a rolagem quando há mais itens do que são vistos atualmente na segmentação de dados. 6. Controles para mover e redimensionar borda permitem alterar o tamanho e a localização da segmentação de dados.

Fone: 112084-0070


Pag.: 118

Microsoft Excel 2013 - Avançado Criar uma segmentação de dados em uma Tabela Dinâmica existente 

Clique em qualquer lugar do relatório de Tabela Dinâmica no qual você deseja criar uma segmentação de dados.



Isso exibe as Ferramentas da Tabela Dinâmica, adicionando as guias Opções e Design.



Clique na guia Opções, no grupo Classificar e Filtrar, clique em Inserir Segmentação de Dados.



Na caixa de diálogo Inserir Segmentações de Dados, marque a caixa de seleção dos campos da Tabela Dinâmica nos quais você deseja criar uma segmentação de dados.



Clique em OK.



Uma segmentação de dados é exibida para cada campo selecionado.



Em cada segmentação de dados, clique nos itens que você deseja filtrar.



Para selecionar mais de um item, segure a tecla CTRL enquanto clica nos itens que deverão ser filtrados.

Fone: 112084-0070


Pag.: 119

Microsoft Excel 2013 - Avançado Formatar uma segmentação de dados 

Clique na segmentação de dados que você deseja formatar.



Isso exibe as Ferramentas de Segmentação de Dados, adicionando uma guia Opções.



Na guia Opções, no grupo Estilos de Segmentação de Dados, clique no estilo desejado.



Para ver todos os estilos disponíveis, clique no botão Mais

.



Exclua uma segmentação de dados Siga um destes procedimentos: 

Clique na segmentação de dados e pressione DELETE.



Clique com o botão direito do mouse na segmentação de dados e, em seguida, clique em Remover .

O comando Segmentação de Dados só está disponível nas versões do Excel 2013.

Fone: 112084-0070


Pag.: 120


Campo Calculado em Tabela Dinâmica O recurso de tabela dinâmica é uma das melhores funcionalidades do Excel. Ele cria tabelas com o resumo dos dados, com fácil atualização e construção. Um dos recursos pouco explorado pelos usuários é o de criar campos calculados. Os campos calculados são muito úteis servindo para realizar cálculos sobre os dados resumidos, como por exemplo identificar se uma meta foi atingida ou calcular percentuais. Vamos ao nosso exemplo, primeiro devemos ter uma base de dados no nosso caso a tabela de Pedidos. Com base nesta tabela iremos cria nossa Tabela Dinâmica conforme o modelo abaixo:

Observe que na tabela acima temos a soma do Total e a soma do Frete. Na base de dados o Total representa o Preço*Qtde+Frete. O que precisaremos acima e saber o percentual do Frete no Total e para utilizaremos um Campo Calculado.

Criando campo calculado 

Clicando sobre a tabela dinâmica, surgirão as Ferramentas de Tabela Dinâmica, clique na guia Opções, no grupo Cálculos clique em Campos, Itens e Conjuntos e escolha a opção Campo Calculado.

Fone: 112084-0070


Pag.: 121




Na janela que abre no campo Nome digite % Frete.



Clique em Fórmula.



Selecione o campo Frete, digite / e selecione o campo Total. Deve ficar =Frete/Total e clique em Adicionar para que o campo seja criado e em seguida clique em OK.

Agora na lista de campos de tabela dinâmica aparece o campo % Frete que pode ser inserido na Tabela Dinâmica para analise dos percentuais como mostra o exemplo abaixo. Se necessário formate os valores para Percentual.

Como se podem notar campos calculados em tabelas dinâmicas podem ser de grande utilidade, evitando a utilização de recursos como realizar cálculos em outras células com base na tabela dinâmica.

Fone: 112084-0070


Pag.: 122


Funções de Texto As funções de texto, quando usadas de forma isolada, não fazem sentido. Ou seja, possuem aplicações restritas. Porém, quando são aninhadas, am a fazer sentido e a terem larga aplicação. Inicialmente, para que você possa entender a sintaxe, a melhor forma e trabalhar com as funções isoladamente e depois aninhar elas.

CONCATENAR Agrupa vários itens de texto em um único item de texto =CONCATENAR(texto1; texto2;...) Texto1; texto2 são de 2 a 255 itens de texto a serem agrupados em um único item de texto. Os itens de texto podem ser cadeia de caracteres, números ou referências a células únicas. Observe na figura abaixo onde temos um número de chassi que esta escrito em células separadas. Para junta os dígitos em uma única célula, digite a seguinte fórmula:

Outra forma de concatenar valores é utilizando o operador de Concatenação &: Exemplo:

=A4&B4&C4&D4&E4&F4&G4&H4

A linha acima produz o mesmo resultado de a função concatenar vista anteriormente.

Note que foi concatenado o conteúdo da célula A1 com um espaço e com o conteúdo da célula B1.

NÚM.CARACT Retorna o número de caracteres em uma sequência de texto =NÚM.CARACT(Texto) Exemplo: Conferir se uma determinada célula possui a quantidade de dígitos necessárias no caso do Chassi tem que ter 17 dígitos.

Retorna o número 17. Ou seja, o número de caracteres (incluindo o espaço entre as palavras)

Fone: 112084-0070


Pag.: 123

Microsoft Excel 2013 - Avançado ESQUERDA Retorna os caracteres mais à esquerda de um valor de texto =ESQUERDA(Texto;Núm_caract) Texto é o texto ou a célula onde está o texto que você deseja extrair. Núm_caract é a quantidade de caracteres a ser extraída. Exemplo: Separar os 2 primeiros dígitos do chassi para identificar o País e Região:

Retorna os 2 caracteres à esquerda, ou seja, 9B.

DIREITA Retorna os caracteres mais à direita de um valor de texto =DIREITA(Texto;Núm_caract) Texto é o texto ou a célula onde está o texto que você deseja extrair. Núm_caract é a quantidade de caracteres a ser extraída. Exemplo: Extrair os dígitos que representa a Sequência de Produção. No caso os 6 últimos dígitos.

Retorna os seis caracteres à direita. Ou seja, 000011.

EXT.TEXTO Retorna um número específico de caracteres de uma seqüência de texto começando na posição especificada =EXT.TEXTO(texto;núm_inicial;núm_caract) Texto é o texto onde está escrito o que você deseja extrair.

Fone: 112084-0070


Pag.: 124

Microsoft Excel 2013 - Avançado núm_inicial é a posição onde começa o texto a ser extraído. núm_caract é a quantidade de caracteres que você deseja extrair. Exemplo: Extrair os dígitos referentes ao modelo.

Retorna o texto que se inicia na posição 7 e possui 2 caracteres de comprimento. Ou seja, 377.

VALOR Converte um argumento de texto em um número =VALOR(Texto) ou =VALOR(FunçãodeTexto) Texto é o texto entre aspas ou uma referência a uma célula que contém o texto a ser convertido. Exemplo: Quando foi extraído o Modelo e a Sequência de Produção os valores vieram como texto e se precisarmos deles como números temos que converte-los utilizando a função VALOR.

Retorna o valor 377 como número. Se você não usar a função valor o valor 377 será texto. Ou seja, as funções de Texto sempre retornam texto. Até mesmo quando retornam dígitos numéricos.

ARRUMAR Remove excesso de espaços do texto. =ARRUMAR(Texto) Texto é o texto entre aspas ou uma referência a uma célula que contém o texto a ser arrumado. Exemplo:

Fone: 112084-0070


Pag.: 125

Microsoft Excel 2013 - Avançado MAIÚSCULA Converte o texto em maiúsculas. = MAIÚSCULA(Texto) Texto é o texto entre aspas ou uma referência a uma célula que contém o texto a ser convertido. Exemplo: Quando os valores foram concatenados algumas células estavam com os caracteres em minúsculo. Aninhando a função MAIÚSCULA com a função CONCATENAR resolveremos este problema.

Retorna o texto: 9BWZZZ377ST000011 com as letras em maiúsculas.

MINÚSCULA Converte texto para minúsculas. =MINÚSCULA(Texto) Texto é o texto entre aspas ou uma referência a uma célula que contém o texto a ser convertido. Exemplo:

Retorna o texto: [email protected] com as letras em minúsculas.

PRI.MAIÚSCULA Coloca a primeira letra de cada palavra em maiúscula em um valor de texto =PRI.MAIÚSCULA(Texto) Texto é o texto entre aspas, uma fórmula que retorna o texto ou uma referência a uma célula que contenha o texto que você deseja colocar parcialmente em maiúscula. Exemplos:

PESQUISAR Localiza um valor de texto dentro de outro (não diferencia maiúsculas de minúsculas). =PESQUISAR(texto_procurado;no_texto;núm_inicial)

Fone: 112084-0070


Pag.: 126

Microsoft Excel 2013 - Avançado Texto_Procurado Necessário, é o texto ou caractere que você deseja encontrar. no_texto Necessário, é o texto onde se encontra o texto que você deseja localizar. núm_inicial Opcional. O número do caractere no argumento no_texto em que você deseja iniciar a busca. Exemplos: Encontrar a posição de um determinado caractere dentro de uma célula. Saber a posição do dígito referente ao Ano no Chassi.

No exemplo, retornou 10 pois a letra s indicada pela célula B4 está na décima posição.

PROCURAR Procura um valor de texto dentro de outro (diferencia maiúsculas de minúsculas). =PROCURAR(texto_procurado;no_texto;núm_inicial) Texto_Procurado Necessário, é o texto ou caractere que você deseja encontrar. no_texto Necessário, é o texto onde se encontra o texto que você deseja localizar. núm_inicial Opcional. O número do caractere no argumento no_texto em que você deseja iniciar a busca. Exemplos: Encontrar a posição de um determinado caractere dentro de uma célula. Saber a posição do dígito referente ao Local da Montagem no Chassi.

No exemplo, retornou 11 pois a letra t indicada pela célula G4 está na décima primeira posição.

TEXTO Formata um número e o converte em texto =TEXTO(valor;formato_texto)

Fone: 112084-0070


Pag.: 127

Microsoft Excel 2013 - Avançado Valor Necessário. Um valor número, uma fórmula que é avaliada como um valor numérico ou uma referência a uma célula contendo um valor numérico. formato_texto Necessário. Um formato numérico como uma cadeia de texto entre aspas. Por exemplo, "m/d/aaaa" ou "#,##0.00".. Exemplo: Quando concatenamos os dígitos para gerar ocorreu um problema a Sequência de produção esta formatada como número o que não permite os zero a esquerda ficando o chassi incompleto. Como sabemos que a Sequencia de produção possui 6 dígitos vamos utilizar a função TEXTO aninhada com o Concatenar.

REPT Repete o texto um determinado número de vezes. =REPT(texto;núm_vezes) Texto é o texto a ser repetido. núm_vezes é um número positivo que especifica o número de vezes que você deseja repetir texto. O máximo permitido é 32.767 repetições. Exemplo: Gerar um gráfico na célula para demonstrar o consumo de energia elétrica.

No exemplo acima o caractere a ser repetido foi “|” (barra vertical) e a quantidade de vezes foi determinada pelos Kw gastos. Como o valor é muito grande e só precisamos de uma simples representação visual o mesmo foi dividido por 10 numa forma de gerar uma escala menor.

Extrair a primeira palavra de um nome ou frase qualquer

Fone: 112084-0070


Pag.: 128


Para obter o resultado acima, na célula B2, foi escrita a seguinte fórmula: =ESQUERDA(ARRUMAR(A2); PESQUISAR(" "; ARRUMAR(A2); 1) - 1) Onde: a função ARRUMAR, remove os espaços em branco desnecessários. A função PESQUISAR localiza a posição do primeiro espaço, que é o caractere que separa o primeiro nome dos demais. No caso, PESQUISAR retorna o número 6, mas, pela fórmula temos que subtrair um então o resultado é 5, que é o comprimento do primeiro nome. Se você alterar o nome por outro qualquer, a fórmula sempre trará o primeiro nome.

Extrair o sobrenome de um nome qualquer

Para obter o resultado acima, na célula B5, foi escrita a seguinte fórmula: =DIREITA(ARRUMAR(A5); NÚM.CARACT(ARRUMAR(A5)) - PESQUISAR(" "; ARRUMAR(A5); 1)) Onde: NÚM.CARACT retorna a quantidade de caracteres PESQUISAR retorna a posição do primeiro espaço (6)

contidos

na

célula

A5

(27).

Resolvendo por etapas temos: =DIREITA(ARRUMAR(A5); 27 - 6) que resulta em: =DIREITA(A5; 21)

Convertendo texto para minúsculas ou maiúsculas

Observe que, na figura acima, coluna B, foram usadas as fórmulas da coluna C. Os dados foram convertidos para maiúsculo. No entanto, agora surgiu outro problema. Ou seja, agora você tem duas colunas com a mesma informação. Se você remover a coluna A, surgirá erro na coluna conversão. A atitude correta a ser tomada aqui é a seguinte: siga os os: 

Selecione o intervalo B2:B9 e clique em Copiar.



Clique com botão direito do mouse na célula A2 e selecione a opção Colar Especial.



No diálogo Colar especial, marque a opção Valores e clique OK.



Apague as demais informações

Os os acima podem ser usados com as funções MINÚSCULA, PRI.MAIÚSCULA ou outra função que você julgar necessário.

Transformar Texto em Data Em alguns sistemas, você poderá obter datas no tipo texto com a seguinte forma: 05091969 Com este formato, não dá para o Excel executar cálculos, pois a informação não é data. Sendo assim, você terá que fazer a conversão deste texto em data

Fone: 112084-0070


Pag.: 129

Microsoft Excel 2013 - Avançado Formula de conversão supondo-se que a data esteja na célula D9: =DATA.VALOR(ESQUERDA(D9; 2) & "/" & EXT.TEXTO(D9; 3; 2) & "/" & DIREITA(D9; 2)) O resultado será o número de série 25451 que, se formatado como data você obterá: 05/09/1969.

Funções de Informação É (Funções É) Cada uma dessas funções, chamada coletivamente de funções É, verifica o valor especificado e retorna VERDADEIRO ou FALSO, dependendo do resultado. Por exemplo, a função ÉCÉL.VAZIA retornará o valor lógico VERDADEIRO se o argumento de valor for uma referência a uma célula vazia; caso contrário, ele retornará FALSO. Você pode usar uma função É para obter informações sobre um valor antes de realizar um cálculo ou outra ação com ele. Por exemplo, é possível usar a função ÉERROS junto com a função SE para realizar uma ação diferente se um erro ocorrer: =SE(ÉERROS(A1); "Um erro ocorreu."; A1 * 2) Essa fórmula verifica se existe uma condição de erro em A1. Em caso positivo, a função SE retornará a mensagem "Um erro ocorreu". Se não houver erro, a função SE realizará o cálculo A1*2. FUNÇÃO

RETORNA VERDADEIRO SE

=ÉCÉL.VAZIA(valor)

Valor se referir a uma célula vazia.

=ÉERRO(valor)

Valor se referir a um valor de erro exceto #N/D.

=ÉERROS(valor)

Valor se referir a qualquer valor de erro (#N/D, #VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? ou #NULO!).

=ÉLÓGICO(valor)

Valor se referir a um valor lógico.

=É.NÃO.DISP(valor)

Valor se referir ao valor de erro #N/D (valor não disponível).

=É.NÃO.TEXTO(valor)

O valor faz referência a qualquer item que não seja texto. Observe que essa função retornará VERDADEIRO se o valor fizer referência a uma célula em branco.

=ÉNÚM(valor)

Valor se referir a um número.

=ÉREF(valor)

Valor se referir a uma referência.

=ÉTEXTO(valor)

Valor se referir a texto.

valor Obrigatório. O valor que você deseja testar. O argumento de valor pode ser um espaço em branco (célula vazia), um erro, um valor lógico, um texto, um número ou um valor de referência ou ainda um nome que faz referência a qualquer um desses elementos.

ÉERROS A função ÉERROS, verifica o tipo de valor e retorna VERDADEIRO ou FALSO, dependendo do resultado. Esta função retorna o valor verdadeiro quando o valor se referir a qualquer valor de erro (#N/D, #VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? ou #NULO!) =ÉERROS(valor)

Fone: 112084-0070


Pag.: 130

Microsoft Excel 2013 - Avançado Valor é o valor que se deseja testar. O valor pode ser um espaço em branco (célula vazia), um valor de erro, lógico, texto, número ou referência, ou um nome correspondente a qualquer um destes itens que se deseja testar. A função pode ser utilizada em diversas situações e é mais eficiente se utilizada junto com a função SE. =SE(ÉERROS(fórmula);resposta_se_verdadeiro;fórmula) Resposta_se_verdadeiro: É a resposta a ser dada caso a fórmula retorne algum erro. Fórmula: É a fórmula a ser testada pela função ÉERROS e se não retornar o erro deverá ser executada ao final da função SE para retornar o resultado da mesma. Mensagens de erro de fórmulas:

Mensagem

O que ocorreu?

#DIV/0!

Existe uma fórmula que exibiu este resultado que está utilizando alguma referência para dividir por zero. É preciso corrigir a célula, ou alterar o valor para um número diferente de 0 na célula usada como divisor.

#N/D

Esta mensagem significa que nenhum valor está disponível neste momento na célula usada como referência ou fonte de dados. Você pode inserir #N/D quando desejar que esta célula não faça cálculos tendo como valor 0, e sim espere até ser digitado algum valor. Enquanto o valor não for digitado na célula referente, o resultado da fórmula será #N/D. Se for algum erro na fórmula, possivelmente está na referência errada.

#NOME?

Aparece quando se usa um nome estranho ao Excel. Pode ser divido ao fato de você não ter definido o nome antes, ou digitou o nome errado (Exemplo SONA, ao invés de SOMA). Se você está inserindo texto junto com fórmula deve coloca-lo entre aspas.

#NULO!

Existe sempre quando você se refere a uma interseção de áreas que não se interceptam. Por exemplo: (A10:A20) (A21:D21)

#NUM!

Está existindo algum problema com algum número digitado. Você digitou algum número, somatória, matriz, que não existe. Por exemplo: =RAIZ(-1).

#REF!

Ocorre quando você utilizou uma referência errada.

#VALOR!

Ocorre quando você usa algum tipo estranho de argumento ou operando. Você pode, por exemplo, digitar algo repetido ou algum caractere desconhecido.

NÃO.DISP Retorna o valor de erro #N/D. #N/D é o valor de erro que significa "não há nenhum valor disponível." Use NÃO.DISP para marcar células vazias. Ao inserir #N/D nas células onde estão faltando informações, você pode evitar o problema de incluir, não intencionalmente, células vazias nos seus cálculos. Quando uma fórmula se refere a uma célula que contém #N/D, a fórmula retornará o valor de erro #N/D. =NÃO.DISP( )

Fone: 112084-0070


Pag.: 131


Funções de Pesquisa e Referência PROCV A função PROCV compara o valor de pesquisa com a primeira coluna da lista e em seguida retorna um valor associado da mesma linha. Para usar a função PROCV, digite uma fórmula que usa a seguinte sintaxe em uma célula vazia da sua planilha. =PROCV(valor_procurado;matriz_tabela;núm_índice_coluna;procurar_intervalo) valor_procurado especifica o valor de pesquisa que se deseja comparar com a primeira coluna na lista; matriz_tabela especifica o intervalo que contém toda a lista; núm_índice_coluna especifica a coluna que contém o valor que deve ser retornado; procurar_intervalo informa ao Excel como comparar o valor de pesquisa, podendo ser: FALSO ou 0 para valor exato ou VERDADEIRO, 1 ou omitir para valor aproximando para menos. No exemplo, criaremos uma planilha para consultar os dados da tabela Pedidos. Precisaremos de uma planilha semelhante a exibida abaixo:

Agora temos que inserir a função na célula B4 a célula B3 servirá de referência para a pesquisa: =PROCV($B$3;PEDIDOS;2;FALSO)

Agora é só copiar a fórmula para as demais células substituindo apenas o número índice da coluna.

Quando o Número do Pedido for alterado os dados serão atualizados automaticamente e caso o Pedido não exista aparecerá o erro #N/D. Para corrigir este erro devemos utilizar a função SEERRO para que se o pedido não existir a célula deverá ficar vazia. =SEERRO(PROCV($B$3;Pedidos;2;FALSO);"")

Fone: 112084-0070


Pag.: 132


Abaixo a planilha após a correção da fórmula:

Outro exemplo de utilização do PROCV: Calcular a comissão do Vendedor de acordo do o total das vendas realizadas por ele. Utilizaremos a Tabela de comissões para saber o percentual ao qual ele terá direito:

Para facilitar o nosso trabalho iremos renomear a Tabela de Comissões. Selecione o intervalo I5:K10 e nomeie para Comissões através da Caixa de nomes. Agora iremos montar a fórmula para encontrar a Alíquota correspondente para cada Total: =PROCV(B4;Comissões;3;VERDADEIRO)

O Valor da comissão será: =B4*C4 OBS: Para o cálculo acima poderíamos usar a função SE mas a PROCV é mais prática e simples. abaixo a função SE correspondente a fórmula acima: =SE(B4<=$J$5;$K$5;SE(B4<=$J$6;$K$6;SE(B4<=$J$7;$K$7;SE(B4<=$J$8;$K$8;SE(B4<=$J$9;$K$9;$K$10)))))

Fone: 112084-0070


Pag.: 133

Microsoft Excel 2013 - Avançado PROCH A função PROCH compara o valor de pesquisa com a primeira linha da tabela e em seguida retorna um valor associado da mesma coluna. Para usar a função PROCH, digite uma fórmula que usa a seguinte sintaxe em uma célula vazia da sua planilha. =PROCH(valor_procurado;matriz_tabela;núm_índice_lin;procurar_intervalo) Valor_procurado é o valor a ser localizado na primeira linha da tabela. Matriz_tabela é uma tabela de informações onde os dados devem ser procurados. Núm_índice_lin é o número da linha em matriz_tabela de onde o valor correspondente deve ser retirado. Um núm_índice_lin equivalente a 1 retorna o valor da primeira linha na matriz_tabela, um núm_índice_lin equivalente a 2 retorna o valor da segunda linha na matriz_tabela, e assim por diante. Se núm_índice_lin for menor do que 1, PROCH retornará o valor de erro #VALOR!; se núm_índice_lin for maior do que o número de linhas na matriz_tabela, PROCH retornará o valor de erro #REF!. procurar_intervalo informa ao Excel como comparar o valor de pesquisa, podendo ser: FALSO ou 0 para valor exato ou VERDADEIRO, 1 ou omitir para valor aproximando para menos. Como exemplo iremos adaptar a Tabela anterior e criar uma coluna para o pagamento de um Prêmio extra para o vendedor. Criaremos também a tabela com as faixas de valores.

Renomeie o intervalo de A16:E17 para Premiação. Agora montaremos a fórmula:

=PROCH(B4;Premiação;2;VERDADEIRO)

Fone: 112084-0070


Pag.: 134

Microsoft Excel 2013 - Avançado Caso você já queira que apareça o valor do prêmio a ser pago ao invés dos percentuais aplique a fórmula abaixo: =PROCH(B4;Premiação;2;VERDADEIRO)*B4

Desafio!

Vá até o capítulo de DESAFIOS e tente resolver o Desafio 02 utilizando os conhecimentos adquiridos até o momento. Se necessário solicite orientações e dicas para seu instrutor para ajudar na solução do Desafio. Quando tiver a solução solicite ao instrutor que corrija ou envie por e-mail para: [email protected]


Fone: 112084-0070


Pag.: 135


Fone: 112084-0070


Pag.: 136


AULA 5 Funções de Pesquisa e Referências (cont.) ÍNDICE Retornar o valor de um elemento em uma tabela selecionado pelos índices de número de linha e coluna. Use a forma de matriz se o primeiro argumento de ÍNDICE for uma constante de matriz. =ÍNDICE(matriz;núm_lin;núm_col) Matriz é um intervalo de células ou uma constante de matriz. Se a matriz contiver somente uma linha ou coluna, o argumento núm_lin ou núm_col correspondente será opcional. Se a matriz tiver mais de uma linha e mais de uma coluna e apenas núm_linha ou núm_coluna for usado, ÍNDICE retornará uma matriz referente à linha ou coluna inteira da matriz. Núm_lin seleciona a linha na matriz a partir da qual um valor deverá ser retornado. Se núm_linha for omitido, núm_coluna será obrigatório. Núm_col seleciona a coluna na matriz a partir da qual um valor deverá ser retornado. Se núm_col for omitido, núm_linha será obrigatório. Núm_área seleciona um intervalo em ref do qual deve ser retornada a interseção de núm_linha com núm_coluna. A primeira área selecionada ou inserida recebe o número 1, a segunda recebe o número 2, e assim por diante. Se núm_área for omitido, ÍNDICE usará a área 1.

Retorna a referência da célula na interseção de linha e coluna específicas. Se a referência for formada por seleções não-adjacentes, você pode escolher a seleção que deseja observar. =ÍNDICE(ref;núm_linha;núm_coluna;núm_área) Ref é uma referência a um ou mais intervalos de célula.

Como exemplo utilizaremos a Tabela de Rendimentos da Caderneta de Poupança:

Vamos supor que eu precisa-se saber o índice de Rendimento no ano de 2013 no mês de Março. Iriamos utilizar a seguinte fórmula para buscar este valor: =ÍNDICE(A5:M14;3;4)

Fone: 112084-0070


Pag.: 137


4 3

Onde A5:M14 é o intervalo de células, 3 é o número da linha e 4 o número da coluna. O resultado que teremos é o valor da intersecção da linha com a coluna no intervalo selecionado. Ex: 0,5000

CORRESP Retorna a posição relativa de um item em uma matriz que coincide com um valor especificado em uma ordem específica. Use CORRESP em vez de uma das funções PROC quando você precisar da posição de um item em um intervalo ao invés do item propriamente dito. =CORRESP(valor_procurado;matriz_procurada;tipo_correspondência)

Valor_procurado é o valor utilizado para localizar o valor desejado em uma tabela. 

Valor_procurado é o valor que você deseja coincidir com matriz_procurada..



Valor_procurado pode ser um valor (número, texto ou valor lógico) ou uma referência de célula de um número, texto ou valor lógico.

Matriz_procurada é um intervalo contíguo de células que contêm valores possíveis de procura. Matriz_procurada precisa ser uma matriz ou uma referência de matriz. Tipo_correspondência é o número -1, 0 ou 1. Tipo_correspondência especifica como o Microsoft Excel corresponde a valor_procurado com os valores contidos em matriz_procurada. 

Se tipo_correspondência for 1, CORRESP localizará o maior valor que for menor do que ou igual a valor_procurado. Matriz_procurada deve ser posicionada em ordem ascendente: ...-2, 1, 0, 1, 2,...A-Z, FALSO, VERDADEIRO.



Se tipo_correspondência for 0, CORRESP localizará o primeiro valor que for exatamente igual a valor_procurado. Matriz_procurada pode ser colocada em qualquer ordem.



Se tipo_correspondência for -1, CORRESP localizará o menor valor que seja maior ou igual a valor_procurado. Matriz_procurada deve ser posicionada em ordem decrescente: VERDADEIRO, FALSO, Z-A,...2, 1, 0, -1, -2,... e assim por diante.



Se tipo_correspondência for omitido, será equivalente a 1.

Comentários 

CORRESP não faz distinção entre letras maiúsculas e minúsculas quando estiver fazendo a correspondência entre valores de texto.



Se CORRESP não conseguir localizar um valor coincidente, ele fornecerá o valor de erro #N/D.



Se tipo_correspondência for 0 e valor_procurado for um texto, valor_procurado poderá conter os caracteres curinga asterisco (*) e ponto de interrogação (?). Um asterisco corresponde a qualquer sequência de caracteres; um ponto de interrogação corresponde a qualquer caractere individual.

Fone: 112084-0070


Pag.: 138


No exemplo utilizamos como matriz o intervalo de A6:A14. Acima o ano de 2009 foi localizado na posição 3 pois utilizamos o tipo de correspondência 0 (exata). O ano de 2000 deu erro #N/D pois solicitamos o tipo de correspondência 1 (aproximado para menos) deu erro pois a lista utilizada deveria estar em ordem crescente. O ano de 2001 não existe porém a fórmula retornou a posição 9 pois utilizamos o tipo de correspondência -1 (aproximado para mais) ele retornou o aproximado para mais.

Aninhando as Funções ÍNDICE e CORRESP No exemplo criaremos uma consulta para os rendimentos de acordo com o Ano e o Mês. Para isso iremos aninhar a função ÍNDICE com a CORRESP. Para facilitar nosso trabalho iremos renomear os intervalos conforme abaixo: Intervalos A5:M5 A5:A14 A5:M14

Nomes MESES ANOS JUROS

Na célula K3 utilizamos a fórmula abaixo: =ÍNDICE(JUROS;CORRESP(K1;ANOS;0);CORRESP(K2;MESES;0))

Onde: 

JUROS – corresponde a tabela de onde será extraído o valor;



CORRESP(K1;ANOS;0) – corresponde ao número da linha onde se encontra o ano procurado;



CORRESP(K2;MESES;0) – corresponde ao número da linha onde se encontra o mês procurado;

Fone: 112084-0070


Pag.: 139


Funções Financeiras As funções financeiras efetuam cálculos comerciais comuns, como determinar o pagamento de um empréstimo, o valor futuro ou o valor atual líquido de um investimento, e os valores de obrigações ou cupons. Os argumentos comuns para as funções financeiras incluem: 

VF - Valor futuro – o valor do investimento ou empréstimo depois da efetuação de todos os pagamentos.



NPER - Número de períodos – o número total de pagamentos ou períodos de um investimento.



PGTO - Pagamento – a quantia paga periodicamente para um investimento ou empréstimo.



VP - Valor presente – o valor de um investimento ou empréstimo no início do período de investimento. Por exemplo, o valor presente de um empréstimo é a quantia principal tomada emprestada.



Taxa – a taxa de juros ou taxa de desconto de um empréstimo ou investimento.



Tipo – o intervalo em que os pagamentos são feitos durante o período, como no início (1) ou no final (0) de um mês.

Equivalência de Taxas Certifique-se de que está sendo consistente quanto às unidades usadas para especificar taxa e nper. Para fazer a equivalência de taxas utiliza-se a formula a seguir: iq = (1+it)^(Pq/Pt)-1 Onde: iq -- Taxa que quero encontrar

Pq -- Período para qual quero encontrar a taxa

it -- Taxa que tenho

Pt -- Período que possuo

Exemplo: Se fizer pagamentos mensais por um empréstimo de quatro anos com juros de 12% ao ano, utilize =(1+12%)^(1/12)-1 para Taxa e 4*12 para nper. A taxa de juros será de 0,95% ao mês e o período (nper) será de 48 meses.

PGTO Retorna o pagamento periódico de uma anuidade de acordo com pagamentos constantes e com uma taxa de juros constante. =PGTO(taxa;nper;vp;vf;tipo)

 (1  Taxa) NPER xTaxa  PGTO  VPx   NPER  Taxa   (1  Taxa) Exemplo:

Fone: 112084-0070


Pag.: 140

Microsoft Excel 2013 - Avançado VP Retorna o valor presente de um investimento. O valor presente é o valor total correspondente ao valor atual de uma série de pagamentos futuros. Por exemplo, quando você toma uma quantia de dinheiro emprestada, a quantia do empréstimo é o valor presente para o concessor do empréstimo. =VP(taxa;nper;pgto;vf;tipo)

VP  Exemplo:

VF (1  Taxa) NPER

NPER Retorna o número de períodos para investimento de acordo com pagamentos constantes e periódicos e uma taxa de juros constante. =NPER(Taxa;pgto;vp;vf;tipo)

NPER 

log(VP / VF ) log(1  Taxa)

Exemplo:

OBS: Neste exemplo nós precisamos converter a taxa anual para mensal porque o Pagamento esta mensal.

TAXA Retorna a taxa de juros por período de uma anuidade. TAXA é calculado por iteração e pode ter zero ou mais soluções. Se os resultados sucessivos de TAXA não convergirem para 0,0000001 depois de 20 iterações, TAXA retornará o valor de erro #NÚM!. =TAXA(nper;pgto;vp;vf;tipo;estimativa)

  VF  1 NPER  Taxa     1 x100    VP    

Fone: 112084-0070


Pag.: 141


Estimativa – Opcional. A sua estimativa para a taxa. 

Se você omitir estimativa, este argumento será considerado 10%.



Se TAXA não convergir, atribua valores diferentes para estimativa. Em geral, TAXA converge se estimativa estiver entre 0 e 1.

Exemplo:

OBS: Tendo a Taxa mensal de Juros Efetiva podemos calcular a Taxa Anual Efetiva bastando para isso fazer uma Equivalência de Taxa.

VF Retorna o valor futuro de um investimento de acordo com os pagamentos periódicos e constantes e com uma taxa de juros constante. =VF(taxa;nper;pgto;vp;tipo)

VF  PV (1  Taxa) NPER Exemplo:

Você Sabia?

A SIM possui um treinamento especifico voltado a Finanças. O treinamento é o de Cálculos Financeiros no Excel e HP-12C. De continuidade ao seu treinamento para ter um diferencial a mais para o mercado de trabalho. Entre em contato e obtenha mais informações: (11) 2084-0070 ou [email protected]

Fone: 112084-0070


Pag.: 142


Teste de Hipóteses O Teste de Hipóteses e um grupo de ferramentas que ajuda a testar diversos valores para as fórmulas na Planilha. Para ar as ferramentas do Teste de Hipóteses clique na guia Dados, no grupo Ferramentas de Dados clique no botão Teste de Hipóteses e selecione o teste a ser feito.

O Teste de Hipóteses disponibiliza 3 opções: 

Gerenciador de Cenários: Permite criar e salvar diferentes grupos de valores, ou cenários, e alternar entre eles.



Atingir Meta: O recurso localizará a entrada correta quando você souber o resultado desejado.



Tabela de Dados: Permitem ver os resultados de várias entradas possíveis diferentes ao mesmo tempo

Tabela de Dados O Excel permite a criação de tabelas baseadas em uma ou várias fórmulas. Existem dois tipos de tabelas de dados: 

Tabela de Entrada Única: Você digita valores diferentes para uma única variável e observa o efeito em uma ou mais fórmulas.



Tabela de Duas Entradas: Neste caso precisamos de uma estrutura de tabela de dados com duas variáveis independentes, as quais deverão estar dispostas de maneira perpendicular com a fórmula inserida na interseção.

Exemplo 1: Tabela de Entrada Única – 1 Variável 1. Na planilha a seguir, em primeiro lugar na célula C6 deverá ser inserida uma fórmula que calcule o valor dos Pagamentos e que exiba a mensagem “Falta Informação” caso de algum erro na célula quando não for informado o Prazo ou digitado algum argumento inválido.

2. Após calculado o pagamento vamos então montar a planilha para considerar estas possibilidades.

Fone: 112084-0070


Pag.: 143

Microsoft Excel 2013 - Avançado 3. Tendo inserido as taxas de juros para cada banco, na célula C10 você deverá calcular o pagamento utilizando a mesma fórmula anterior pois esta servirá de base para a tabela só devendo substituir a frase “Falta Informação” por 0 para que a tabela não seja toda preenchida com a frase caso tenha algum erro na fórmula. 4. Em seguida 

Selecione as células de B10:C15 (somente as taxas de juros e a fórmula);



Clique na guia Dados, grupo Ferramentas de Dados, Teste de Hipóteses e clique em Tabela de Dados.



Clique no campo Célula de Entrada da Coluna e digite a referência de célula que contém a Taxa de Juros na planilha base (C4).




Note que a tabela será preenchida automaticamente como os valores de acordo com as suas respectivas taxas. Se o Prazo ou o Valor financiado for alterado na tabela base os valores da tabela se atualizarão automaticamente. Exemplo 2: Tabela de Entrada Única – 1 Variável Este exemplo é igual ao anterior o que muda é apenas a Célula de Entrada que agora será pela Linha. 

Selecione as células de B20:G21 (somente as taxas de juros e a fórmula);






Clique no campo Célula de Entrada da Linha e digite a referência de célula que contém a Taxa de Juros na planilha base (C4).




Fone: 112084-0070


Pag.: 144

Microsoft Excel 2013 - Avançado Exemplo 2: Tabela de Duas Entrada – 2 Variáveis Neste exemplo iremos variar o Prazo (C5) e a Taxa de Juros (C4) para isso montados a tabela abaixo e seguiremos os procedimentos para realização do teste: 

Selecione as células de B26:G31 (somente a fórmula, taxas de juros e os prazos);






Clique no campo Célula de Entrada da Linha e digite a referência de célula que contém o Prazo na planilha base (C5).



Clique no campo Célula de Entrada da Coluna e digite a referência de célula que contém a Taxa de Juros na planilha base (C4).




Faça a alteração no Valor Financiado e observe que a tabela se atualizará automaticamente. Se quiser pode substituir os Prazos e Taxas da tabela devendo manter a fórmula base.

Quando utilizamos o comando Tabela de Dados se selecionarmos os intervalos errados e clicarmos no botão de OK este comando não poderá ser desfeito pelo botão de Desfazer. E os resultados obtidos não podem ser alterados individualmente por se tratarem de uma fórmula matricial.

Fone: 112084-0070


Pag.: 145


Atingir Meta Quando você souber o resultado desejado de uma única fórmula mas não o valor de entrada de que a fórmula necessita para determinar o resultado, você poderá usar o recurso Atingir meta. Ao utilizar este recurso, o Microsoft Excel varia o valor em uma célula específica até que uma fórmula dependente daquela célula retorne o resultado desejado. No exemplo, iremos simular os testes para um Financiamento usamos Atingir meta para alterar o valor do Financiamento da célula C3 de forma incremental até que o valor de pagamento em C6 seja igual a R$ 400,00.

Para utilizarmos o Atingir Meta selecione a célula C6 e siga os os abaixo: 

Clique na guia Dados, grupo Ferramentas de Dados, Teste de Hipóteses e clique em Atingir Meta;



Na janela que se abre configure as opções:



Na caixa Definir célula, insira a referência para a célula que contém a fórmula (fórmula: uma sequência de valores, referências de células, nomes, funções ou operadores em uma célula que juntos produzem um novo valor. Uma fórmula sempre começa com um sinal de igual (=).) que você deseja resolver. No exemplo, essa referência é a célula C6.



Na caixa Para valor, digite o resultado da fórmula desejado. No exemplo, esse valor é 400.



Na caixa Alternando célula, insira a referência para a célula que contém o valor que você deseja ajustar. No exemplo, essa referência é a célula C3. OBS: A célula alterada pelo Atingir Meta deve ser referenciada pela fórmula na célula que você especificou na caixa Definir célula.



Clique em OK. Será exibida a janela do Status do comando atingir meta clique em OK para manter ou Cancelar para retornar os valores anteriores.

Tabela antes:

Tabela Depois

Observe que se os Pagamentos forem de R$ 400,00 o Valor Financiado cai para R$ 47.160,91.

Fone: 112084-0070


Pag.: 146


Gerenciador de Cenários Gerenciador de Cenários é um conjunto de valores utilizados para prever o resultado de um modelo de planilha. Você pode criar e salvar diferentes grupos de valores em uma planilha e, em seguida, alternar para qualquer um destes novos cenários para visualizar resultados diferentes. Por exemplo, se quiser criar simular várias hipóteses de Financiamento, você poderá definir valores diferentes para o Valor Financiado, Taxa de Juros ou Período, em seguida, alternar entre os cenários para executar a análise hipotética. Vamos gerar algumas simulações conforme abaixo:

Simulação 1

Simulação 2

Simulação 3

Para criar um cenário faça os seguintes procedimentos: 1. Na guia Dados no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, clique em Gerenciador de Cenários.

2. Clique em Adicionar. 3. No campo Nome do Cenário, digite um nome para o cenário. Exemplo: Simulação 1 4. Na extremidade direita do campo Células Variáveis, selecione as células a serem alteradas;

5. Clique em OK.

Fone: 112084-0070


Pag.: 147

Microsoft Excel 2013 - Avançado 6. Altere os valores das células se necessários e, em seguida, clique em OK.

7. Clique em Fechar. Para criar cenários adicionais, clique em Adicionar e, em seguida, repita as etapas 3-7. Quando acabar de criar cenários, clique em OK e, em seguida, clique em Fechar na caixa de diálogo Gerenciador de cenários.

Para exibir os valores de um Cenário basta abrir a janela do Gerenciador de cenários selecionar o cenário desejado e clicar no botão Mostrar ou dar um duplo clique sobre o cenário desejado. Não devemos armazenar células com fórmulas no cenário pois o mesmo só armazena valores e não fórmulas.

Depois de criar ou coletar todos os cenários necessários, você poderá criar um relatório de resumo de cenário que incorpore informações desses cenários. Um relatório de cenário exibe todas as informações em uma tabela em uma nova planilha. Para gerar o Relatório de resumo de cenário devemos: 

Na janela do Gerenciador de cenários clique no botão Resumir e na janela que se abre selecione a opção Resumo do cenário e na Célula de resultado informe a célula C6;



Clique em OK.

Fone: 112084-0070


Pag.: 148

Microsoft Excel 2013 - Avançado O relatório abaixo será criado em uma nova planilha.

OBS: Relatórios de cenário não são recalculados automaticamente. Se você alterar os valores de um cenário, essas alterações não aparecerão em um relatório de resumo existente. Em vez disso, será necessário criar um novo relatório de resumo.

Auditoria Através da guia Fórmulas, no grupo Auditoria de Fórmulas, você pode examinar as relações entre células e fórmulas em sua planilha e identificar erros. Quando você usa as ferramentas de auditoria, as setas rastreadoras apontam as células que fornecem dados para as fórmulas e as células que contêm fórmulas que se referem às células. Uma caixa é desenhada em volta dos intervalos de células que fornecem dados a fórmulas.

1. Rastrear precedentes. As setas rastreadoras azuis mostram células que fornecem dados à fórmula.

2. Rastrear dependentes. As setas azuis apontam para todas as células que dependem da célula ativa.

Fone: 112084-0070


Pag.: 149

Microsoft Excel 2013 - Avançado 3. Remover Setas: 

Remover setas precedentes. Remove as setas azuis da célula selecionada.



Remover setas dependentes. Remove as setas azuis da célula selecionada.



Remover todas as setas. Remove todas as setas adicionadas na planilha.

4. Mostrar Fórmulas. Exibe as fórmulas da planilha e os valores sem formatação

Para reexibir os resultados basta clicar no botão Mostrar Fórmulas novamente. 5. Verificação de Erros:



Verificação de erros. Ativa a janela de verificação de erros que analisa e informa as células que possuem erros.



Rastrear erro. As setas vermelhas apontam para fórmulas que causam valores de erro; as setas azuis apontam para as células que contêm valores que criam o erro.

Fone: 112084-0070


Pag.: 150

Microsoft Excel 2013 - Avançado 6. Avaliar fórmulas. Abre a janela de avaliação de fórmula onde podemos avaliar e verificar a execução do calculo o-a-o.

7. Mostra janela de Inspeção. Abre a janela de inspeção para que se possa guardar as referencias das células analisadas.

Desafio!

Vá até o capítulo de DESAFIOS e tente resolver os Desafios 03 e 04 utilizando os conhecimentos adquiridos até o momento. Se necessário solicite orientações e dicas para seu instrutor para ajudar na solução dos Desafios. Quando tiver a solução solicite ao instrutor que corrija ou envie por e-mail para: [email protected]


Fone: 112084-0070


Pag.: 151


Fone: 112084-0070


Pag.: 152


AULA 6 Guia Desenvolvedor A guia Desenvolvedor não é exibida por padrão, mas você pode adicioná-la à faixa de opções quando desejar fazer ou usar o seguinte: 

Escrever macros.



Executar macros que você gravou anteriormente.



Usar controles de formulário no Microsoft Excel.



Usar controles ActiveX.



Usar comandos XML.



Criar aplicativos para usar com os programas do Microsoft Office.

Habilitando a Guia Desenvolvedor 

Clique na guia Arquivo.



Em Ajuda, clique em Opções.



Clique em Personalizar a Faixa de Opções.



Em Personalizar a Faixa de Opções, marque a caixa de seleção Desenvolvedor.



Clique em OK.

Fone: 112084-0070


Pag.: 153


Formulários Para criar formulários utilizando controles, devemos clicar na guia Desenvolvedor e no grupo Controles, clicar no botão Inserir. São dois grupos de controles: Controles de Formulários e Controles ActiveX. Qualquer objeto de formulário pode ser personalizado selecionando-o e clicando com o botão direito do mouse e escolhendo a opção Formatar objeto... ou atribuir uma macro escolhendo a opção Atribuir macro... ou ainda atribuindo propriedades. Para este último caso, só será possível ao escolher controles do segundo grupo, isto é, escolhendo entre os Controles Activex.

Tutorial para criação de formulário personalizado Nesta aula iremos criar o formulário abaixo utilizando Ferramentas de Controle, Proteção de Células e Macros para automatizar algumas ações.

No arquivo de trabalho, selecione a planilha correspondente a este exercício. O arquivo possui 3 planilhas: FORM, REFERÊNCIAS e SOLICITAÇÕES. Na planilha FORM criaremos um formulário no intervalo A1:F15. O intervalo A20:H20 na figura abaixo, será usado na configuração das propriedades dos controles do formulário que iremos criar servirá para armazenar os vínculos das células.

Fone: 112084-0070


Pag.: 154

Microsoft Excel 2013 - Avançado Na planilha REFERÊNCIAS teremos a base de dados a ser utilizada pelo formulário. A mesma deve ser nomeada através da caixa de nomes conforme abaixo: Intervalos Nomes A1:D8 DADOS A2:A8 PRODUTOS F1:F3 FORNECEDORES A planilha SOLICITAÇÕES receberá os dados do formulário via macros.

Vamos começar a construção do formulário na planilha FORM. Iremos inicialmente desenhar os objetos do formulário e configura-los e depois iremos fazer as fórmulas correspondentes.

Forma de Pagamento (Caixa de Grupo e Botões de Opção): Clique no botão Caixa de grupo no grupo Controles de Formulários. Ao clicar no botão, o cursor do mouse será alterado para a figura posicione o cursor no local desejado (célula A10), pressione o botão esquerdo do mouse e, mantendo o botão pressionado, arraste até desenhar um retângulo conforme figura abaixo:

Para alterar o título da caixa de grupo, arraste o mouse até o texto. O ponteiro do mouse terá a aparência de um i maiúsculo neste momento clique e arraste para selecionar e substituir o texto. Outra forma: posicione o mouse próximo a borda da caixa de grupo o ponteiro do mouse mudará para clique com o botão direito e selecione a opção Editar texto. Dentro desta caixa de grupo, vamos colocar 3 botões de opção. Para isto, clique no Botão de opção em Controles do Formulário e, depois dê um clique dentro da caixa de grupo. Após colocar os 3 controles, use o procedimento descrito acima para alterar os respectivos textos. Certifique-se de que nenhum dos controles tenha pedaços fora da Caixa de grupo. Se, por acaso, 2 ou mais controles ficarem selecionados ao mesmo tempo, é porque existem controles que não estão totalmente inseridos na caixa de grupo.

Você Sabia?

Para facilitar as seleções clique na caixa de grupo Forma de Pagamento serão habilitadas as Ferramentas de Desenho, no grupo Organizar clique na opção de Seleção para abrir o Seleção e Visibilidade. Neste encontram-se todos os objetos da planilha basta clicar sobre o nome do objeto para selecioná-lo. Se quiser selecionar mais de um segure a tecla CTRL enquanto clica sobre os objetos no . Através das Ferramentas de Desenho, no grupo Organizar, clicando na opção Alinhar pode-se alinhar os objetos selecionados e redistribuir os espaçamentos entre eles.

Fone: 112084-0070


Pag.: 155

Microsoft Excel 2013 - Avançado Atenção: antes de alterar o texto, clique com o botão direito do mouse em um dos três controles e selecione a opção Formatar controle. No diálogo formatar controle, clique na guia Controle e, no item vinculo da célula, digite H20.

Agora vamos fazer um teste. Clique em uma célula qualquer, clique no 1º botão e observe a célula H20. Ela deverá mostrar o valor 1, 2º Botão o valor 2 e 3º Botão o valor 3. Caso não estejam em ordem, troque-os de posição para que fiquem em ordem. Note que não é a ordem da descrição e sim na ordem do que aparece na célula H20. Após certificar-se de que os controles estejam em ordem crescente, altere os respectivos textos conforme figura abaixo.

Os demais controles serão adicionados a partir do grupo Controles ActiveX.

Quantidade (Botão de rotação) Vamos iniciar pelo controle mais simples o Botão de rotação (SpinButton). Clique no respectivo controle e desenhe um retângulo vertical (primeira figura) na célula F3. Ao soltar o mouse o controle será adicionado. Se você desenhar um retângulo horizontal, o Botão de Rotação terá a seguinte aparência:

Atenção: observe o botão Modo de design. Ele está com a aparência pressionada. Mantenha-o assim até terminar o desenho e a configuração de todos os controles do formulário. Ao terminar a edição, clique neste botão para sair do modo de design. Se você não fizer isso, os controles não irão funcionar.

Fone: 112084-0070


Pag.: 156

Microsoft Excel 2013 - Avançado Vamos agora configurar algumas propriedades do SpinButton. Para isto, clique botão Propriedades. Na propriedade LinkedCell digite o endereço E3 (A configuração desta propriedade é fundamental). Na propriedade Min digite o número 1. Outras Propriedades: 

ForeColor (cor das setas). Clique na guia Paleta e selecione uma cor (vide figura abaixo).



BackColor (cor de fundo).



Orientation (orientação vertical ou horizontal)

Para testar o controle SpinButton, clique no botão Sair do modo de design e depois clique nas setas para cima e para baixo e note as alterações na célula E3.

Produto e Fornecedor (Caixa de Combinação) Clique na Caixa de combinação e desenhe um retângulo envolvendo o intervalo B3:C3. Desenhe o controle com altura maior que a altura da linha conforme figura abaixo:

Se você desenhar o controle com a altura exata da linha, o texto a ser exibido nele poderá ser cortado. Desenhe ou copie o controle para o fornecedor. Sem dúvida, o melhor é copiar, pois assim, os controles terão exatamente o mesmo tamanho.

Antes de efetuar a configuração das Caixas de Combinação adicionadas acima, você deve nomear alguns intervalos que serão usados em conjunto com os controles adicionados acima. Os dados são fundamentais, pois são os dados que irão aparecer quando você clicar em uma das setas dos controles acima. Ou seja, a lista de fornecedores, a lista de produtos e seus respectivos preços que variam de acordo com o fornecedor.

Fone: 112084-0070


Pag.: 157

Microsoft Excel 2013 - Avançado Você deve dar preferência ao uso de nomes em vez de usar intervalos. Por quê? Simples os intervalos dentro das propriedades dos controles são fixos. E, caso você precise aumentar a quantidade de itens, você terá que mexer na configuração dos controles também senão eles não refletirão os novos valores. Quando você usa intervalos nomeados, este problema não ocorre, pois basta você inserir os novos dados dentro do intervalo nomeado e eles, naturalmente constarão nos controles. Os intervalos que utilizaremos são os da planilha REFERÊNCIAS nomeados anteriormente: Intervalos A1:D8 A2:A8 F1:F3

Nomes DADOS PRODUTOS FORNECEDORES

Com os intervalos devidamente nomeados, vamos configurar os controles do formulário. Para isto, selecione a Caixa de Combinação referente ao Produto e clique no botão Propriedades do grupo Controles. Você verá a janela de propriedades. As propriedades fundamentais para o funcionamento adequado do controle Caixa de Combinação são LinkeCell (célula vinculada) e ListFillRange (intervalo para preenchimento da lista). Mas existem outras propriedades que também são importantes tais como: BackColor, Font, ForeColor, Style e TextAlign dentre várias outras. 1. De um clique duplo na propriedade LinkedCell e digite A20. Poderia ser qualquer outra célula, mas a escolha de A20 é devido ao fato de ela estar tabela de vínculos. 2. De um clique duplo na propriedade ListFillRange e digite PRODUTOS. 3. Selecione a Caixa de Combinação referente ao Fornecedor e altere as propriedades LinkedCell para B20 e ListFillRange para FORNECEDORES.

Caixa de Combinação - Produto

Caixa de Combinação - Fornecedor

As propriedades fundamentais estão configuradas. Agora vamos fazer outras configurações para melhorar a aparência e evitar a digitação de palavras que não existam na lista.

Fone: 112084-0070


Pag.: 158

Microsoft Excel 2013 - Avançado 4. Se não estiver selecionada, selecione uma das Caixas de Combinação. 5. De um clique duplo na propriedade BackColor (cor de fundo) e selecione a guia Paleta e selecione uma cor clara qualquer. 6. De um clique duplo na propriedade Font, ative Negrito e tamanho 14. 7. De um clique duplo na propriedade ForeColor (cor do texto) e proceda conforme o item 5. 8. Altere a propriedade Style para 2-fmStyleDropDownList. Esta propriedade impede que o usuário digite palavras que não existam na lista de itens (produtos, por exemplo). 9. Altere a propriedade TextAlign para 2-TextAlignCenter (texto alinhado ao meio). 10. Selecione a outra Caixa de Combinação e repita os os do item 4 ao 9. Clique no botão Sair do modo de design. Teste os controles clicando nas respectivas setas. Selecione um produto qualquer e um fornecedor. A propriedade responsável pelo aparecimento dos itens na Caixa de Combinação foi a propriedade ListFillRange. Observe nas células A20 e B20 que os valores mudam de acordo com o item seleciona na Caixa de Combinação.

Preço, Total, Data e A Pagar (Fórmulas) Siga os os abaixo para escrever as fórmulas que completam o formulário. 1. A célula B7 deverá ter uma formula que, através do nome do produto que está na célula A20, e do nome do fornecedor que está na célula B20, pegue o respectivo preço. Para isso, clique na célula B7 e digite a seguinte fórmula: =PROCV(A20; DADOS; CORRESP(B20; FORNECEDORES; 0) + 1; 0)

2. Coloque na célula B9 o valor total. Ou seja, o preço vezes a quantidade do produto selecionado. Para isto, selecione a célula B9 e digite a fórmula: =B7 * E3

3. A data da compra será exibida na célula E7 logo, selecione a célula E7 e digite a fórmula: =HOJE()

4. O valor a pagar ira variar de acordo com a forma de pagamento. Ou seja, se o pagamento for em cheque, não haverá desconto nem acréscimo. Se o pagamento for em dinheiro o cliente terá um desconto de 10% e se o pagamento for em cartão será cobrado uma taxa de 10%. Para isso, ative a célula E9 e digite a fórmula: =SE( H20 = 2 ; B9 * 90% ; SE( H20 = 3 ; B9 * 110% ; B9 ) )

Fone: 112084-0070


Pag.: 159

Microsoft Excel 2013 - Avançado Tabela de Vínculos (Fórmulas) Agora precisamos preencher as demais células da tabela de vínculos. As células A20, B20 e H20 já são preenchidas pelos controles do formulário nas demais utilizaremos a igualdade para puxar os valores da células correspondentes no formulário como visualizamos abaixo na figura:

Após fazermos as configurações de proteção e gravar as macros necessárias poderemos ocultar as células de vínculo e a planilha de REFERÊNCIA. Aplique as formatações que desejar ao Layout do formulário. Até este ponto seu formulário deverá estar como o modelo abaixo:

Para retirar as Linhas de Grade da planilha clique na guia Exibição, no grupo Mostrar desmarque a opção Linhas de Grade. Você Sabia?

Fone: 112084-0070


Pag.: 160


Proteger a Pasta de Trabalho com Senha Em se tratando de salvar os arquivos no Microsoft Excel, pode-se, além de armazenar o arquivo de maneira padrão, colocar senhas de proteção e gravação, criar backup, recomendável somente para leitura e alterar o tipo do arquivo.

Para inserir senha para Abrir a Pasta de Trabalho 1. Clique no Menu Arquivo e em seguida clique em Salvar Como.

2. Clique no botão Ferramentas e, em seguida, clique em Opções Gerais.

3. Digite uma senha na caixa Senha de Proteção. 4. Digite uma senha diferente na caixa Senha de Gravação. 5. Clique em OK. 6. Na caixa Reinsira a senha para prosseguir, digite a primeira senha e clique em OK. 7. Na caixa Reinsira a senha para modificar digite a segunda senha e clique em OK. Se desejar que seja sempre criado um arquivo de backup, marcar a caixa de verificação Sempre criar backup. Se desejar que o arquivo ao ser aberto, seja recomendável somente leitura, deve-se clicar na caixa de verificação correspondente. Após configurar todas as opções de salvamento desejadas, clicar no botão OK Clicar no botão Salvar para efetuar a gravação do arquivo nos padrões estabelecidos anteriormente.

Fone: 112084-0070


Pag.: 161


Bloquear Células de uma Planilha A proteção de células é bastante utilizada para se restringir o o a fórmulas. Por padrão, todas as células vêm formatadas para serem travadas quando é dado o comando para travar a planilha, mas esta opção pode ser facilmente alterada, de forma que disponibiliza selecionar quais células devem ser travadas. Bloqueada: Evita que as células selecionadas sejam alteradas, movidas, redimensionadas ou excluídas. Para alterar a forma de proteção das células, proceda da seguinte forma: Selecionar as células que você deseja que sejam travadas. Ocultas: Oculta uma fórmula em uma célula de maneira que ela não apareça na barra de fórmulas quando a célula for selecionada. Se você selecionar essa opção, ela não terá efeito a menos que a planilha esteja protegida. Caso se queira ocultar a fórmula que existe dentro das células selecionadas, basta marcar a caixa de verificação Ocultas. Observe que esta opção só faz sentido quando a célula deve ser travada, de forma que o usuário não possa alterar nem ver o conteúdo das células que possuem fórmulas. Como exemplo iremos desbloquear algumas células do fórmula criando anteriormente para que possamos proteger a planilha e ficarmos apenas com algumas células livres para alteração. Para isso siga os procedimentos: 

Selecione a célula E3 e segurado a tecla CTRL selecione o intervalo de A20:H20;



Clique na guia Página Inicial, no grupo Número e clique no botão de Expansão para abrir a janela Formatar Células.



Clique na guia Proteção e desmarque a opção Bloqueadas.

Bloquear células ou ocultar fórmulas só terão efeitos quando você proteger a planilha. Clique na guia Revisão, grupo Alterações, botão Proteger Planilha.

Fone: 112084-0070


Pag.: 162


Proteger Planilhas Para se travar uma planilha é necessário protegêla. Ao executar tal tarefa, todas as células que tiverem sua formatação configurada para serem travadas, serão protegidas. Para proteger uma planilha, clique na guia Revisão, aponte para o Grupo Alterações e clique em Proteger Planilha. Será exibida a janela ao lado: Marcar as caixas de verificação correspondentes aos itens que se deseja proteger escolhendo o tipo de ação que será permitida. No nosso exemplo deixaremos marcada apenas a opção Selecionar células desbloqueadas. Assim o usuário só poderá clicar nas células que destravamos anteriormente. Se desejar proteger com senha, esta deve ser digitada na caixa Senha (opcional). Clicar no botão OK se foi inserida uma senha, será requisitado que se confirme o que foi digitado.

Se tiver células desbloqueadas para alguma ação e que o usuário não possa ter o as mesmas podem ser ocultadas. Por exemplo as células da tabela de vínculos que serão ocultas. Neste caso as células precisam ser ocultadas antes da proteção.

Proteger Pasta de trabalho Às vezes é necessário proteger apenas a estrutura e o modo de exibição da pasta de trabalho, isto é, não permitir que sejam incluídas ou excluídas planilhas, mover ou copiar planilha, alterar as propriedades, Dividir e congelar painéis. Para proteger a pasta de trabalho, vá até a guia Revisão, no grupo Alterações clique no botão Proteger Pasta de Trabalho e clique em Proteger Estrutura e Janelas.

Estrutura: Protege a estrutura de uma pasta de trabalho de modo que as planilhas não possam ser excluídas, movidas, ocultas, reexibidas ou renomeadas, e novas planilhas não possam ser incluídas. Janelas: Impede que as janelas de uma pasta de trabalho sejam movidas, redimensionadas, ocultas, reexibidas ou fechadas.

Fone: 112084-0070


Pag.: 163


Proteger e compartilhar pasta de trabalho Quando é necessário compartilhar a pasta de trabalho, é preciso proteger, de forma que, quando o arquivo estiver sendo usado por um ou mais usuários ao mesmo tempo, não possam ocorrer operações que alterem a integridade dos dados, por exemplo, se dois ou mais usuários tentarem, ao mesmo tempo, alterar o conteúdo de uma mesma célula, a pasta de trabalho estará protegida contra isto. Para se proteger e compartilhar a pasta de trabalho, faça: Clicar na guia Revisão, no Grupo Alterações clique no comando Proteger e compartilhar pasta de trabalho.

Marcar a caixa de verificação Compartilhar com alterações de controle. Pode-se definir uma senha, se desejado, deve-se digitá-la no campo Senha (opcional). Clicar no botão OK caso tenha sido digitada a senha opcional, está deve ser confirmada neste momento.

Protege o compartilhamento e o controle do histórico da alteração em uma pasta de trabalho compartilhada, para que não possam ser desativados. Se a pasta de trabalho não estiver compartilhada quando você marcar esta caixa de seleção e clicar em OK, será exibida uma mensagem perguntando se você deseja salvar a pasta de trabalho como uma pasta de trabalho compartilhada. Em uma pasta de trabalho já compartilhada, é possível ativar a proteção para o compartilhamento e histórico da alteração, mas não designar senha para essa proteção. Para designar uma senha, é preciso, em primeiro lugar, remover o compartilhamento da pasta de trabalho.

A proteção de elementos de planilha e pasta de trabalho não deve ser confundida com a segurança por senha em nível de pasta de trabalho. A proteção de elementos não pode proteger uma pasta de trabalho contra usuários mal-intencionados. Para uma camada de segurança adicional, convém proteger o arquivo de pasta de trabalho inteiro usando uma senha. Isso permite que somente usuários autorizados exibam ou modifiquem os dados da pasta de trabalho.

Fone: 112084-0070


Pag.: 164


Gravação de Macros Macro é uma sequência de comandos ou ações que são gravadas a fim de automatizar as tarefas mais utilizadas no dia a dia. Tem o objetivo de facilitar a vida do usuário tornando o trabalho mais rápido e produtivo. Existem dois tipos de macro no Excel: 

Macro Gravador: O usuário grava esta macro interativamente , isto é, aciona-se o gravador e todos os comandos são armazenados para uso posterior.



Macro Comandos: Este tipo de macro envolve programação, exigindo certo raciocínio lógico do usuário. É construído a partir da digitação de macro-comandos em uma folha de macro.

Gravador Para gravar uma nova macro, na guia Desenvolvedor, no grupo Código, clique em Gravar Macro. Será aberta a seguinte caixa de diálogo:

Nesta caixa de diálogo, podemos: 

Alterar o nome da macro.



Atribuir uma tecla de atalho.



Alterar o local de gravação da macro



Alterar a descrição da macro.

A caixa de combinação “Armazenar macro em:” possui as seguintes opções:

Onde: 

Pasta de trabalho pessoal de macros - Grava a macro em uma pasta de trabalho denominada PESSOAL.XLS, que pode ser usada para gravar todas as suas macros pessoais.



Nova pasta de trabalho - Grava a macro em uma nova pasta



Esta pasta de trabalho - Grava a macro na pasta de trabalho atual.

Após clicar OK na caixa de diálogo Gravar macro, no grupo Código irá aparecer o comando Parar Gravação.

Execute os os que deseja gravar em sua macro e clique no botão Parar gravação.

Fone: 112084-0070


Pag.: 165

Microsoft Excel 2013 - Avançado Formas de execução de Macro Para executar uma macro qualquer, podemos usar o seguinte procedimento: 

Clique na guia Desenvolvedor, no grupo Código, clique em Macros;



Será exibido a seguinte caixa de diálogo:

Onde: 

Executar: executa a macro selecionada.



Cancelar: cancela a operação fechando a caixa de diálogo Macro



Entrar: Executa a macro selecionada uma linha de código de cada vez.



Editar: abre a janela do VBA



Criar: permite a criação de uma nova macro



Excluir: exclui a macro selecionada



Opções: permite a atribuição de uma tecla de atalho e/ou a alteração da descrição da macro

Selecione a macro desejada e clique no botão Executar. Você também pode dar um duplo clique sobre o nome da macro que deseja executar.

A Janela de Macros pode ser aberta através da tecla de atalho ALT + F8. Você Sabia?

Ao gerar uma Pasta de Trabalho com Macros a mesma deve ser salva com a extensão .XLSM (Pasta de Trabalho Habilitada para Macro do Excel). Caso contrario a macro será excluída. Outra forma de armazenar a macro na Pasta de Trabalho e salvar no modo de Compatibilidade para o Excel 97-2003.

Fone: 112084-0070


Pag.: 166

Microsoft Excel 2013 - Avançado Exemplos de Gravação de Macros Exemplo 1 – Macro para Redefinir o formulário Neste exemplo iremos criar uma macro para redefinir o formulário a mesma deverá apagar as células de vínculo das Caixa de Combinação Produto (A20), Fornecedor (B20), a célula referente a Forma de Pagamento (H20) e da Quantidade (E3). Para isso siga os procedimentos abaixo: 1. Clique na Guia Desenvolvedor, selecione o grupo Código e clique em Gravar Macro. 2. Na caixa de diálogo Gravar Macro, digite: LimparForm. Escolha para armazenar em EstaPasta_de_Trabalho. 3. Digite a letra L (maiúscula) para criar o atalho CTRL+SHIFT+L. 4. No campo Descrição identifique sua macro e depois clique em OK.

5. Selecione as células A20, B20, E3 e H20 utilizando a tecla CTRL pressionada; 6. Pressione a tecla DELETE e clique na célula E3; 7. Clique na guia Desenvolvedor e clique no botão Para gravação; 8. Preencha o formulário e execute a macro pressionando CTRL + SHIFT + L para testarmos a macro.

Devido a macro para Limpar o formulário, os campos Preço (B7), Total (B9) e A pagar (E9) apresentarão erros quando os vínculos estiverem vazios. Para corrigir este problema precisaremos aprimorar as fórmulas deste campos utilizando a função SEERRO como segue abaixo: 

B7 

=SEERRO(PROCV(A20;DADOS;CORRESP(B20;FORNECEDORES;0)+1;0);“”)



B9 

=SEERRO(B7*E3;“”)



E9 

=SEERRO(SE(H20=2;B9*90%;SE(H20=3;B9*110%;B9));“”)

Fone: 112084-0070


Pag.: 167

Microsoft Excel 2013 - Avançado Exemplo 2 – Macro para Gravar dados em outra Planilha Neste exemplo iremos criar uma macro para gravar os dados do formulário na planilha SOLICITAÇÕES que será nossa base de dados. Para isso siga os procedimentos abaixo: 1. Clique na guia Desenvolvedor, selecione o grupo Código e clique em Gravar Macro. 2. Na caixa de diálogo Gravar Macro, digite: GravarDados. Escolha para armazenar em EstaPasta_de_Trabalho. 3. No campo Descrição identifique sua macro e depois clique em OK.

4. Clique na Planilha SOLICITAÇÕES; 5. Habilite a opção Usar Referências Relativas, no grupo Código da guia Desenvolvedor;

6. Na Caixa de nome digite a referência A65536 e pressione ENTER;

7. Pressione a tecla CTRL + SETA PARA CIMA e em seguida pressione a SETA PARA BAIXO; 8. Desabilite a opção Usar Referências Relativas, no grupo Código da guia Desenvolvedor; 9. Clique na planilha FORM; 10. Selecione os vínculos das células (intervalo de A20:H20);

11. Pressione a tecla CTRL + C para copiarmos as células; 12. Clique na planilha SOLICITAÇÕES; 13. Clique no botão COLAR da guia Página Inicial e escolha a opção COLAR VALORES; 14. Retorne para a planilha FORM, clique na célula E3 e pressione a tecla ESC; 15. Clique na guia Desenvolvedor e clique no botão Para gravação;

Fone: 112084-0070


Pag.: 168

Microsoft Excel 2013 - Avançado Finalizando o Formulário Após criadas as macros podemos agora criar os botões para acioná-las. Iremos desenhar os 2 botões para a macro de Limpar e Gravar como no exemplo abaixo:

Para criarmos os botões siga os procedimentos a baixo: 

Clique na guia Desenvolvedor, no grupo Controles, clique no botão Inserir e no grupo Controles de Formulários escolha a opção Botão;



Ao desenhar o botão será exibida a janela Atribuir macro. Selecione a macro a ser atribuída ao botão e clique em OK;



Substitua o texto do botão;



Repita os procedimentos para o outro botão;



Após concluir teste os botões para verificar se os mesmos estão funcionando corretamente.



Se precisar atribuir a macro ou alterar a macro atribuída clique com o botão direito do mouse sobre o botão desenha e clique na opção Atribuir Macro.

Fone: 112084-0070


Pag.: 169

Microsoft Excel 2013 - Avançado Agora vamos melhor a aparência do formulário: 

Selecione a coluna G, pressione CTRL + SHIFT + SETA PARA DIREITA até selecionar a última coluna da planilha;



Clique com o botão direito do mouse em uma das colunas selecionadas e selecione a opção Ocultar;



Selecione a linha 16 e pressione CTRL + SHIFT + SETA PARA BAIXO até selecionar a última linha;



Clique com o botão direito do mouse em uma das linhas selecionadas e selecione a opção Ocultar;

Agora iremos tirar os cabeçalhos das linhas e colunas: 

Clique na guia Exibição, no grupo Mostrar, desmarque a opção Títulos. Também pode retirar as Linhas de Grade;

Para finalizar iremos Proteger a planilha: 

Clique na guia Revisão, no grupo Alterações, clique na opção Proteger Planilha;



Na janela de proteção deixe marcada apenas a opção Selecionar células desbloqueadas;



Se quiser pode definir uma senha para proteger a planilha.

Pronto nosso formulário esta concluído.


Fone: 112084-0070


Pag.: 170


AULA 7 Macro para Importação de Dados Exemplo 3 – Macro para Importação de Arquivo TXT 1. Clique na Guia Desenvolvedor, selecione o grupo Código e clique em Gravar Macro. 2. Na caixa de diálogo Gravar Macro, digite: ImportarDados. Escolha para armazenar em EstaPasta_de_Trabalho. 3. Digite a letra I (maiúscula) para criar o atalho CTRL+SHIFT+I. 4. No campo Descrição identifique sua macro e depois clique em OK.

5. Neste momento inicia a gravação da macro. Fique atento à sequência de comandos pois os mesmos serão executados da mesma forma que você os fizer a partir deste momento. Clique na guia Dados, no grupo Obter Dados Externos, clique na opção De Texto; 

Selecione o arquivo de texto que deseja importar. No nosso exemplo utilizaremos o arquivo população.txt. Clique no arquivo e em seguida clique no botão Importar;

Fone: 112084-0070


Pag.: 171


Será aberta a janela do Assistente de importação de texto. Na Etapa 1 de 3 marque a opção Delimitado e clique no botão Avançar;



Na Etapa 2 de 3 marque a opção Tabulação que é o delimitador de colunas do arquivo de texto. O delimitador depende da base de dados utilizada.



Clique no botão Avançar;



Na Etapa 3 de 3 podemos definir os formatos dos dados das colunas. Clique em Concluir.

Fone: 112084-0070


Pag.: 172




Na janela que se abre selecione a opção Na nova planilha para que seja criada uma planilha para os dados importados e clique em OK.



Os dados serão inseridos da nova planilha como no exemplo abaixo:



Pressione a tecla CTRL + * (asterisco) do teclado numérico para selecionar toda a área de dados;



Aplique todas as bordas;



Vá até a guia Exibição e no grupo Mostar/Ocultar, desative as Linhas de grade;

Fone: 112084-0070


Pag.: 173


Selecione o intervalo de A1:F1 e aplique a formatação Negrito, Centralizado e Preenchimento Cinza 25%;



Clique na célula A1;



Clique na guia Desenvolvedor e clique no botão Para gravação.



Execute a macro pressionando CTRL + SHIFT + I para testarmos a macro.

Associar macro a um botão na barra de ferramentas Para associar uma macro a um botão, primeiro você deverá inserir um novo botão na barra de ferramentas, e depois fazer a associação da macro. 

Inserindo um novo botão na barra de ferramentas de o rápido:



Clique com botão direito na Barra de ferramentas de o rápido e em seguida, clique em Personalizar Barra de Ferramentas de o Rápido. Será exibida a seguinte caixa de diálogo:



Em, Escolher comandos em selecione Macros, selecione o nome da macro que deseja adicionar e clique no botão Adicionar.



Em seguida, clique no botão Modificar para escolher a imagem que será estampada no botão.



Clique em OK.



Na Barra de Ferramentas de o Rápido será inserido o novo botão com a Macro associada clique no botão para testar.

Fone: 112084-0070


Pag.: 174


Personalizar a Barra de o Rápido A Barra de Ferramentas de o Rápido é personalizável e contém um conjunto de comandos independentes da guia exibida no momento na Faixa de Opções. Você pode mover a Barra de Ferramentas de o Rápido de um de dois locais possíveis e pode adicionar, a essa barra, botões que representem comandos.

Observações: 

Não é possível aumentar o tamanho dos botões que representam os comandos por meio de uma opção do Microsoft Office. A única maneira de aumentar o tamanho dos botões é diminuir a resolução de tela utilizada.



Não é possível exibir a Barra de Ferramentas de o Rápido em várias linhas.



Somente comandos podem ser adicionados à Barra de Ferramentas de o Rápido. O conteúdo da maioria das listas, como valores de recuo e de espaçamento e estilos individuais, os quais também são exibidos na Faixa de Opções, não pode ser adicionado à Barra de Ferramentas de o Rápido. Entretanto, você pode personalizar a Faixa de Opções de acordo com suas preferências. Por exemplo, você pode criar guias e grupos personalizados para conter os comandos de uso mais frequente.

Adicionar um comando à Barra de Ferramentas de o Rápido 

Na Faixa de Opções, clique na guia ou no grupo apropriado para exibir o comando a ser adicionado à Barra de Ferramentas de o Rápido.



Clique com o botão direito do mouse no comando e, em seguida, clique em Adicionar à Barra de Ferramentas de o Rápido no menu de atalho.

Podemos também Personalizar a Barra de Ferramentas de o Rápido. Quando esta opção é aciona a janela de Opções do Excel será aberta com a possibilidade de adicionarmos Mais comandos a barra. Basta selecionar o comando e clicar no botão Adicionar.

Fone: 112084-0070


Pag.: 175

Microsoft Excel 2013 - Avançado Remover um comando da Barra de Ferramentas de o Rápido 

Clique com o botão direito do mouse no comando que você deseja remover da Barra de Ferramentas de o Rápido e, no menu de atalho, clique em Remover da Barra de Ferramentas de o Rápido.

Personalizar a Faixa de Opções Use as personalizações para ajustar a Faixa de Opções, que faz parte da interface de usuário do Microsoft Office Fluent, da maneira que desejar. Por exemplo, você pode criar guias e grupos personalizados para conter os comandos usados com frequência.

Embora você possa adicionar comandos a grupos personalizados, não é possível alterar as guias e os grupos padrão internos do Microsoft Office 2013. Os comandos nos grupos padrão não estão disponíveis ou são exibidos na cor cinza e não podem ser editados. Na lista Personalizar a Faixa de Opções, as guias e os grupos personalizados têm (Personalizado) após o nome, mas a palavra (Personalizado) não aparece na Faixa de Opções. A personalização da Faixa de Opções é específica para o programa do Microsoft Office em que você estiver trabalhando no momento. A personalização da Faixa de Opções não se aplica a todos os programas do Office. Você adiciona uma guia e um grupo personalizados quando clica em Nova Guia. Você só pode adicionar comandos a grupos personalizados. Para criarmos uma Nova Guia siga os procedimentos abaixo: 

Clique na guia Arquivo e em seguida, clique em Opções, clique em Personalizar a Faixa de Opções.

Fone: 112084-0070


Pag.: 176


Clique em Nova Guia.



Após clique na Nova Guia e clique no botão Renomear e substitua o nome da Guia;



Clique em OK;



Renomeie o Novo Grupo e clique em OK; Selecione o grupo renomeado;



Escolha os comandos que deseja adicionar no grupo e clique no botão Adicionar;



Se quiser crie um novo grupo, renomeie e adicione os comando. Após terminar clique em OK e a nova Guia estará disponível na Faixa de Opções.

Importa e Exportar Personalizações Se preferir você pode também Importar/Exportar as personalizações para serem inseridas em outros computadores com Excel. Para isso siga os o abaixo: 

Após ter personalizado a Faixa de Opções e Barra de ferramentas de o Rápido clique no botão Importar/Exportar;



Salve o arquivo e depois importe o arquivo em outro computador através do botão Importar/Exportar da opção Personalizar Faixa de Opções.



Desta forma o Excel irá ter as mesmas guias e ferramentas.

Fone: 112084-0070


Pag.: 177


Revisão Geral – Projeto Folha de Pagamento Você foi informado que será responsável pela elaboração da Folha de Pagamento de sua empresa. Para lhe auxiliar você recebeu as instruções abaixo para realização de todos os cálculos e itens que devem constar no relatório. Se necessário utilize suas anotações e materiais disponíveis para auxilio. Com estas informações você já pode iniciar o seu trabalho. Abra o Excel e abra o arquivo com o nome de Revisão Geral.xls e Não se esqueça de ir salvado o arquivo. BOM TRABALHO!!! 1. Para calcular o desconto do INSS e do IR, você deverá utilizar as tabelas abaixo que são publicadas diariamente nos jornais.

2. Renomeie a Plan1 para Tributos. 3. Para facilitar os cálculos nomeie os intervalos das tabelas: a. Selecione o intervalo A3:C5 e nomeie como INSS (tabela INSS). b. Dê o nome de SalTeto (salário teto) à célula B5. c. Nomeie a célula C5 como AlTeto (alíquota teto). d. O intervalo A9:D13 deve ser nomeado como IR (tabela IR). 4. Na planilha Plan2 você terá a tabela que contem todos os dados necessários para os cálculos.

Fone: 112084-0070


Pag.: 178

Microsoft Excel 2013 - Avançado 5. Renomeie a Plan2 para Folha. 6. Os Nomes dos Funcionários, Empresas, Depto, Horas Trabalhadas e Valor Hora são digitados. Os demais campos serão calculados. 7. Agora deverá ser realizados os cálculos: a. Na célula F2, calcule o salário bruto. b. Calcule o desconto do INSS na célula G2. Se o Salário Bruto for maior ou igual ao SALTETO o desconto será o SALTETO multiplicado pelo ALTETO ; caso contrário será o Salário Bruto multiplicado pela Alíquota correspondente da tabela INSS. c. O Imposto de Renda deve ser calculado na célula H2. O IR deve ser calculado sobre o Salário Bruto menos o INSS. (Salário Bruto – INSS) * Alíquota do IR – Parcela a Deduzir d. Calcule na célula I2 o Salário Liquido. 8. Monte as tabela de Gastos por Depto e Nº de Funcionários conforme o modelo acima e realize os cálculos: a. Calcule os Gastos por Depto com Salários Líquidos. b. Conte o número de funcionários por empresa. 9. Na coluna de Horas Trabalhadas aplique uma Formatação condicional para que quando as horas trabalhadas forem menores que 200:00 apareça na cor Vermelha se as horas trabalhadas forem maiores que 220:00 apareça na cor Azul. 10. Dê o nome de Pagamentos o intervalo de A1: I27 da planilha Folha para ser utilizada posteriormente. 11. Classifique a planilha em Ordem Crescente de nome. 12. Renomeie a Plan3 para Gastos.

13. Utilizando o recurso de Filtro Avançado extraia todos os gastos com pagamentos de salários da Matriz e Filial da tabela Pagamentos.

14. Após extraídos os dados Classifique a tabela por ordem de Empresa. 15. Crie Subtotais por Empresas para obter um resumo de gastos com Salários Líquidos da Matriz e Filial.

Fone: 112084-0070


Pag.: 179

Microsoft Excel 2013 - Avançado 16. Utilizando a planilha Folha crie um Relatório de Tabela Dinâmica que mostre os gastos com Salários Líquidos das Empresas por Departamentos conforme modelo abaixo:

17. Renomeie a planilha que contem a Tabela dinâmica para Relatório Dinâmico. 18. Para sua segurança proteja as planilhas Tributos, Folha e Gastos para que não possa haver alterações.

Fórmulas das Questões 7 e 8: Salário Bruto

=D2*E2*24

INSS

=SE(F2>=SALTETO;SALTETO*ALTETO;F2*PROCV(F2;INSS;3))

IR

=(F2-G2)*PROCV(F2-G2;IR;3)-PROCV(F2-G2;IR;4)

Salário Líquido

=F2-G2-H2

Gastos por Depto

=SOMASE($C$2:$C$27;A31;$I$2:$I$27)

Nº de Funcionários

=CONT.SE($B$2:$B$27;D31)


Fone: 112084-0070


Pag.: 180


Exercícios Complementares Exercícios Complementares - Revisão Revisão 01 Monte a planilha abaixo e realize os cálculos:

1.

Elabore uma função para o Total (ano), onde deverá somar todos os valores de cada refrigerante durante o ano.

2.

Crie uma fórmula para o Total Bruto, onde se deve multiplicar o Total (ano) pelo Preço de Venda de cada produto.

3.

Elabore uma fórmula para o Total Líquido, onde se deve multiplicar o Total (ano) pelo Preço de Compra de cada produto.

4.

Utilize uma fórmula para o faturamento onde deverá subtrair o Total Bruto pelo Total Líquido de cada refrigerante.

5.

Elabore uma função para calcular o Máximo, o Mínimo e a Média de cada mês.

Revisão 02 Monte a planilha a seguir e realize os cálculos: 1.

Fórmulas:

2.

Estoque

= Quantidade Restante + Quantidade Vendida.

3.

Total Vendido

= Quantidade Vendida * Valor Unitário.

4.

Valor da Comissão = Total Vendido * Valor da Comissão.

Fone: 112084-0070


Pag.: 181


Revisão 03 Monte a planilha de Folha de Pagamento e realize os cálculos abaixo: 1. Na célula D5, calcule o Salário Bruto. Insira a fórmula somente uma vez e arraste para as demais células (até D13). 2. As colunas E, F,G e H serão sempre uma porcentagem aplicada sobre o Salário Bruto. Portanto, a célula E5, é a única na qual você deverá digitar a fórmula que calcula o percentual de desconto sobre o Salário Bruto. Em seguida você deverá arrastá-la para os demais funcionários bem como para os demais descontos até a coluna H. 3. Na célula I5, calcule o Total de Descontos para cada funcionário. Também digite a fórmula uma única vez. Pode se usar o comando de AutoSoma se você selecionar as células de E5:I13. 4. O cálculo de 5% de Bônus na célula J5 deverá ser calculado sobre o Salário Bruto; 5. Na coluna K5, calcule o Salário Líquido lembrando que bônus é um acréscimo ao salário. 6. Os cálculos nas células de K15:K18 referem-se à coluna do Salário Líquido. 7. Na célula K19 você deverá calcular a quantidade de funcionários na empresa utilizando a coluna A onde estão os respectivos nomes. 8. Os cálculos das células K20 e K21 referem-se à coluna B de Horas trabalhadas.

Fone: 112084-0070


Pag.: 182


Exercícios Complementares - Aula 01 Exercício 01 Cálculo de Idade Em uma planilha em branco, digite os seguintes dados:   

Em B2 digite a data de Nascimento. Em B3 digite a fórmula: =HOJE() Em B4 digite a fórmula abaixo:

=SE(MÊS(B2) > MÊS(B3); ANO(B3) - ANO(B2) – 1; SE(MÊS(B3) = MÊS(B2); SE(DIA(B2) > DIA(B3); ANO(B3) - ANO(B2) - 1; ANO(B3) - ANO(B2)); ANO(B3) - ANO(B2))) Ou

=DATADIF(B2;B3;“y”)

Exercício 02 Preencha a coluna B da seguinte forma:  Se os Pontos <= 100, desconto de 0%  Se os Pontos estiverem entre 100 e 500, desconto de 5%  Se os Pontos > 500, desconto de 20%

Exercício 03 Caso a data de vencimento seja inferior a data de hoje, Mandar cobrança deverá ser preenchido com SIM. Caso contrário, preencher com NÃO.

Exercício 04 Preencha a tabela de acordo com as seguintes condições. Comissão: Se Vendas for maior ou igual a R$ 10.000,00 o Vendedor terá uma comissão de 10%, senão a comissão será de 5%. Prêmio: Se o total de Vendas (B7) for maior que R$ 50.000,00 e Vendas for maior que R$ 9.000,00 o prêmio será de R$ 1.000,00 senão 0.

Fone: 112084-0070


Pag.: 183

Microsoft Excel 2013 - Avançado Exercício 05

Encontre o valor correspondente ao Imposto de acordo com a tabela de impostos. Após ter encontrado o imposto correspondente calculo o Novo Preço.

Exercícios Complementares - Aula 02 Exercício 01 Utilizando as funções SOMASE, CONT.SE, SOMASES e CONT.SES preencha a tabela abaixo:

Copie a fórmula para as outras células. Não esqueça de Travar as células necessárias antes de usar a alça de preenchimento

Exercício 02

Fone: 112084-0070


Pag.: 184

Microsoft Excel 2013 - Avançado Exercício 03 Aplique uma formatação condicional de acordo com os critérios: 

Se o número digitado for menor do que 10, a célula deverá ficar Verde;



Se o número digitado for entre 10 e 20, a célula deverá ficar Vermelha;



Se o número digitado for maior do que 20 a célula deverá ficar Azul;



Aplique uma Validação de dados na célula B7 para só aceitar números inteiros entre 1 e 50.

Exercícios Complementares - Aula 03 Exercício 01 Considere a seguinte tabela:

1. Classifique-a por Região e por Esporte. 2. Usando a guia Dados, aplique Subtotal à coluna Região. 3. Usando a guia Dados, aplique Subtotal à coluna Esporte. Porem, antes de pressionar OK, desabilite a opção: “Substituir Subtotais atuais”.

Fone: 112084-0070


Pag.: 185

Microsoft Excel 2013 - Avançado Exercício 02 Digite a lista abaixo e calcule os subtotais por vendedor. Não se esqueça de classificar a tabela por vendedor antes de aplicar o subtotal.

Exercício 03 Construa a tabela abaixo:

1. Selecione o intervalo B3:E3 e arraste até a coluna Q. Observe o resultado. 2. Na célula R3, digite a palavra Total. 3. Selecione a célula B9 e clique no botão AutoSoma. 4. Selecione a célula B14 e clique no botão AutoSoma. 5. Selecione a célula B16 e clique no botão AutoSoma. 6. Selecione a célula E5 e clique no botão AutoSoma. 7. Repita o item 6 para as células: I5, M5, Q5 e R5. 8. Observou os recursos da função AutoSoma. 9. Arraste as fórmulas para as demais células. 10. Aplique uma Estrutura de Tópicos à tabela. (Guia Dados, grupo Estrutura de Tópicos, Agrupar, AutoTópicos) 11. Explore a estrutura clicando nos botões 1, 2 e 3 ou nos botões + e -.

Fone: 112084-0070


Pag.: 186

Microsoft Excel 2013 - Avançado Exercício 04 Calcule o total depois aplique as funções de Banco de dados correspondente:

Obs: Selecionar a Tabela e renomear para DADOS.

Função BDSoma BDSoma

O que pesquisa?? Total de unidades por região. Total de unidades pelo Nordeste ano de 1993

Fórmula BDSOMA(dados;"Unidades";E27:E28) BDSOMA(dados;"Unidades";E30:G31)

BDContara

Qtde de registros da Região Centro-Oeste com Sofá e unid acima de 60

BDCONTARA(dados;;E34:G35)

BDMáx

Maior preço de cadeira

BDMÁX(dados;"Preço";E37:E38)

BDMin

Menor preço da Região Sudeste entre 01/08/93 e 01/01/96

BDMÍN(dados;"Preço";E41:G42)

BDMedia

Média de Total do Sul com Mesa e Centro-Oeste com Sofá

BDMÉDIA(dados;"Total";F44:G46)

Fone: 112084-0070


Pag.: 187


Exercícios Complementares - Aula 04 Exercício 01 Monte a planilha abaixo:



Total Venda: Valor Ação * Quantidade Vendida



Comissão: 15% do Total Venda



Gratificação: Se o Total Venda for maior que 300 ; Total Venda * 10% ; Total Venda * 5%



Crie um Relatório de Tabela dinâmica com os dados da planilha.

Exercício 02 Calcule a média aritmética de potência dos veículos, extraindo os 3 primeiros caracteres à esquerda e convertendo estes caracteres em valor.

Fone: 112084-0070


Pag.: 188

Microsoft Excel 2013 - Avançado Exercício 03 Construa a tabela acima e, com base nas tabelas abaixo, use a função PROCV pra determinar os valores das colunas B, C e D. OBS: Nomeie as tabelas INSS e IR para facilitar o cálculo.

Exercício 04 1. Use a tabela de cálculo para determinar as Alíquotas. (função PROCV)

Exercício 05 1. Use a tabela de níveis para determinar o Percentual. (função PROCH) 2. Calcule o Bônus e o Novo salário.

Fone: 112084-0070


Pag.: 189


Exercícios Complementares - Aula 05 Exercício 01 Monte o a planilha abaixo e utilize as funções ÍNDICE e CORRESP.

Nome os intervalos conforme tabela baixo: Intervalos

Nomes

$A$8:$E$15

Vendas

$A$8:$A$15

Col_Código

$C$8:$C$15

Col_Data

Monte as fórmulas para fazer as pesquisas: 

B3

=ÍNDICE(Vendas; CORRESP(B1; Col_Código; 0); 2)



E3

=ÍNDICE(Vendas; CORRESP(E1; Col_Data; 0); 4)

Exercício 02 1. Monte a planilha abaixo:

2. Calcule o valor da Prestação utilizando a função PGTO. 3. Calcule o valor a Prazo utilizando a função VF.

Fone: 112084-0070


Pag.: 190

Microsoft Excel 2013 - Avançado Exercício 03 Utilizando o comando Tabela de dados do Teste de Hipóteses realize o exercício abaixo. 1. Na planilha a seguir, em primeiro lugar na célula B7 você deverá inserir uma fórmula que calcule a velocidade média somente quando as duas velocidades (Máxima e Mínima) estiverem preenchidas, caso contrário a célula (B7) deverá ficar em branco.

Falso

Verdadeiro E(B5>0;B6>0)

“ ” (Ficar em Branco)

Média(B5:B6)

2. Para calcular a duração (média) da viagem na célula B9 bastaria inserir a fórmula =B3/B7. No entanto, se a célula B7 estiver vazia, esta fórmula vai gerar uma mensagem de erro. Para que esta mensagem de erro não aconteça devemos informar que a divisão em B9 só deverá ser realizada quando a célula B7 contiver um número. Neste caso você deverá utilizar a função ÉNUM( ) que identifica se o conteúdo de uma célula é numero ou não. Aplique então o seguinte raciocínio: Falso

Verdadeiro ENÚM(B7)

“ ” (Ficar em Branco)

B3/B7

Após ter calculado a velocidade média para um determinado tipo de veículo, agora deveremos avaliar estes dados para veículos diferentes como carro, ônibus ou avião. Vamos então montar a planilha para considerar estas possibilidades.

Tendo inserido as velocidades médias para cada tipo de veículo, na célula C15 você deverá calcular a Duração (Horas) conforme os dados da planilha base.

Fone: 112084-0070


Pag.: 191

Microsoft Excel 2013 - Avançado Em seguida:  Selecione as células de C14:F15  Abra a guia Dados, grupo Ferramentas de Dados, Teste de Hipóteses e clique em Tabela de Dados.  Clique no campo Célula de Entrada de Linha e digite a referência de célula que contém a velocidade média já calculada na planilha base (B7).  Clique no botão OK. Tabela de Duas Entradas. Na etapa anterior nós calculamos a duração da viagem para um tipo de veículo e um destino X predefinido. Agora teremos veículos diferentes para destinos diferentes. Neste caso precisamos de uma estrutura de tabela de dados com duas variáveis independentes, as quais deverão estar dispostas de maneira perpendicular com a fórmula inserida na interseção.

     

Na célula C20, insira a fórmula que calcule a velocidade média conforme dados sugeridos na planilha base. Selecione as células C20:F24. Abra a guia Dados, grupo Ferramentas de Dados, Teste de Hipóteses e clique em Tabela de Dados. Clique no campo Célula de Entrada de Linha e digite a referência de célula que contém a velocidade média já calculada na planilha base (B7). Clique no campo Célula de Entrada de Coluna e digite a referência de célula que contém a distância já calculada na planilha base (B3). Clique no botão OK.

Agora você pode verificar que o Excel calculou a duração das viagens para cada um dos meios de transportes informados.

Fone: 112084-0070


Pag.: 192


Exercícios Complementares - Aula 06 Exercício 01 Crie o Formulário a seguir Simulação de Vendas:

Nesta planilha estaremos aplicando uma série de objetos para tornar mais flexível a consulta de produtos que são vendidos por uma loja de eletrodomésticos. Deste modo o cliente desta loja escolhe o produto desejado em uma lista (Produto) e a planilha deverá apresentar automaticamente o valor unitário do produto selecionado, sem a necessidade de programação. Define a quantidade de produtos que pretende levar (em Quant.), indica condições de pagamento (% Entrada e Número Parcelas) e a planilha já apresenta o valor total das mercadorias (Total da Venda) e o valor da prestação (Valor da Parcela). Na segunda planilha da pasta de trabalho, encontramos a planilha Apoio.

Esta planilha contém 3 tabelas que deverão ser nomeadas conforme abaixo para facilitar o trabalho com os objetos de formulários do Excel. Intervalos Nomes A3:B5

TabelaFrete

D4:E15

TaxaJuros

G4:H10

TabelaProdutos

G4:G10

ListaProd

A primeira tabela indica o fator referente as despesas de Frete e Seguro (intervalo de A3:B5). A segunda tabela indica o número de prestações e a taxa de juros para cada parcela (intervalo de D4:E15). A terceira tabela indica a lista de produtos e o seu respectivo preço (intervalo de G4:H10). Iremos renomear também a lista dos Produtos (intervalo de G4:G10) Observe os nomes definidos para cada uma das áreas indicadas.

Fone: 112084-0070


Pag.: 193

Microsoft Excel 2013 - Avançado Para desenha os controle deste formulário utilizaremos a opção de Controles de Formulário. 1. Na célula B3 desenhe um controle Caixa de combinação (Drop-down), que irá listar todos os produtos. Após desenhá-lo, pressione o botão direito do mouse sobre este controle, selecione Formatar Controle.

2. Na caixa de diálogo Formatar Controle, selecione a guia Controle, e você terá a seguinte caixa de diálogo:

3. Atribua os seguintes valores. Onde: 

Intervalo de entrada:

ListaProd



Vínculo da célula:

$B$3



Linhas suspensas:

4

4. Na célula B4, vamos inserir uma função que irá pesquisar o valor do produto na planilha Apoio, em uma área chamada TabelaProdutos. Para o nosso exemplo vamos usar a função ÍNDICE da seguinte forma: =ÍNDICE(TabelaProdutos;B3;2) 5. Na célula C5, vamos inserir um controle que irá informar a quantidade de produtos a serem adquiridos. Dessa forma insira um Botão de Rotação:

6. Usando o botão direito sobre este, selecione Formatar Controle, na guia Controle insira os seguintes dados.

Onde: 

Valor mínimo: 1



Mudança incremental: 1



Valor máximo: 30



Vínculo da célula: $B$5

Fone: 112084-0070


Pag.: 194

Microsoft Excel 2013 - Avançado 7. Na célula D3 insira uma Caixa de grupo e digite o seguinte título: Local da Entrega. 8. Na Caixa de grupo, adicione 3 botões e aplique os nomes conforme relação abaixo. Para executar este o utilize o Botão de opção dos Controles de Formulários: Grande São Paulo, Estado de São Paulo e Outros Estados. 9. Com o botão direito do mouse sobre um dos botões de opção, selecione Formatar Controle e na guia Controle em Vínculo da célula digite E2 e pressione o botão OK. 10. Dessa forma à cada seleção dos botões será atribuído automaticamente um número de 1 à 3. Clique sobre os botões e observe em E2. 11. Na célula B6, vamos inserir uma fórmula que irá exibir o valor total da compra, com base no local de entrega. Dessa forma devemos inserir a fórmula: =(B4*B5)*ÍNDICE(TabelaFrete;E2;2) 12. Clique na guia da planilha Apoio e observe a tabela: Despesas de Frete e Seguro - Fator. A fórmula acima, esta baseada nesta tabela, onde ao selecionar um local de entrega será adicionado o valor do frete correspondente. 13. Clique nos botões de opção e veja que o Valor Total é alterado dependendo do Local da Entrega. 14. Na célula B7 insira um Botão de Rotação e, usando o botão direito sobre este, selecione Formatar Controle. Na guia Controle insira os seguintes valores:

Onde: 

Valor mínimo: 10






Valor máximo: 100



Vínculo da célula: $C$7

15. Como o Botão de Rotação não permite trabalharmos com valores de mudança incremental inferior a 1, devemos utilizar uma célula de apoio para indicarmos o valor percentual desejado. 16. Na célula B7, devemos inserir uma fórmula que irá exibir o valor da entrada em Percentual. Dessa forma insira a seguinte fórmula: =C7/100. 17. Em C8, desenhe uma Barra de rolagem, usando o botão direito sobre este, selecione Formatar Controle, na guia Controle:

Fone: 112084-0070


Pag.: 195

Microsoft Excel 2013 - Avançado Insira os seguintes dados onde: 

Valor mínimo: 1






Valor máximo: 12



Mudança de página: 0



Vínculo da célula: $B$8

18. Na célula B9 vamos inserir uma fórmula que faz o cálculo da taxa de juros, com base no número de parcelas selecionada. Observe que na fórmula existe um nome de um intervalo. Este nome refere-se à tabela usada na planilha chamada Apoio. =ÍNDICE(TaxaJuros;B8;2) 19. Na célula B10 vamos inserir uma fórmula para exibir o valor da entrada, dessa forma digite: =B6*B7. Lembrando que na célula B6 contém uma fórmula que traz o valor total da compra, dependendo do local da entrega e na célula B7, contém uma fórmula que traz o valor Percentual da Entrada. 20. Na célula B11, devemos inserir uma fórmula que, com base no valor da entrada, Taxa de Juros, Número de parcela, Valor total e subtraindo a entrada informada irá exibir o valor da parcela. Dessa forma digite a seguinte fórmula: =SE(B7=1;0;PGTO(B9;B8;-B6+B10)) 21. Para finalizar devemos indicar o valor total da venda, de forma que na célula B12, insira a seguinte fórmula: =B11*B8+B10. 22. Para que os números apresentados sejam exibidos de forma correta devemos formatá-los da seguinte forma, selecione a célula B4 pressione a tecla [CTRL] e clique nas células B6, B10, B11 e B12. 23. Mantendo as células acima selecionadas pressione o botão direito do mouse, selecione Formatar Células e na guia Número selecione a categoria Contábil com 2 casas decimais e símbolo R$. 24. Utilizando o mesmo procedimento acima descrito, selecione as células B7 e B9, aplicando o formato Porcentagem com 2 casas decimais. 25. Para finalizar vamos personalizar a planilha colocando uma cor em toda a sua extensão. 26. Para que os números exibidos nas células C7 e E2 sejam ocultos, selecione estas células e aplique o formato personalizado ;;; (três pontos e virgulas) para ocultar a apresentação das informações das células. 27. Dessa forma a sua planilha deverá estar como abaixo:

Fone: 112084-0070


Pag.: 196


Exercícios Complementares - Aula 07 Exercício 01 Para este exemplo, abra uma nova Pasta de trabalho com duas planilhas conforme figura ao lado: 1. Preencha as planilhas Cadastrar e Clientes, conforme as figuras abaixo.

Planilha Cadastrar

Planilha Clientes 2. Salve o arquivo como Cadastro De Clientes. 3. Vamos gravar uma macro que irá recortar os dados digitados na planilha Cadastrar e colar na primeira linha vazia da planilha Clientes.

Faça exatamente o que está descrito abaixo. Caso contrário, seu código não funcionará corretamente. Ou seja, um clique extra poderá corromper seu código.

4. Clique na guia da planilha Cadastrar. 5. Preencha a linha 3 com os dados a seguir: Tospericagerja da Silva rua da Seleção, 70

Mauá

1234-5678

6. Na guia Desenvolvedor e clique no botão Gravar Macro e preencha o diálogo conforme abaixo. 7. Nome da macro: AtualizarDados Tecla de atalho: A Descrição: Digite seu nome e Data etc. Pressione OK. 8. Selecione o intervalo A3:D3, clique no botão Recortar (Ctrl+X). 9. Clique na guia da planilha Clientes. 10. Na Caixa de nome digite: A1048576 e pressione Enter. 11. Pressione Ctrl+SetaAcima. 12. Clique no botão Usar Referências relativas. 13. Pressione a tecla Seta para baixo. 14. Clique no botão Colar.

Fone: 112084-0070


Pag.: 197

Microsoft Excel 2013 - Avançado 15. Pressione a tecla Home. 16. Clique na guia da planilha Cadastrar. 17. Pressione a tecla Home. 18. Clique no botão Parar gravação. 19. Clique no botão Inserir e, em seguida, em Botão (Controle de formulário). 20. De um clique na planilha (no local onde você deseja colocar o botão. Selecione a macro AtualizarDados conforme figura abaixo e clique OK.

Ao soltar o mouse surgirá o botão 21. Para alterar o texto do botão, clique inversamente sobre ele e selecione a opção Editar Texto, altere o texto do botão para Cadastrar.

22. Clique em uma célula qualquer para desselecionar o botão. Pronto agora é só digitar novos dados e clicar no botão ou usar o atalho Ctrl+Shift+A.

Fone: 112084-0070


Pag.: 198


Desafios DESAFIO 01 Utilizando os recursos aprendidos solucionar o desafio abaixo: 1. Digitar a planilha e aplicar as formatações;

2. Na célula C5 não haverá fórmula somente a digitação de algum valor que deverá ser sempre inteiro, ou seja, o Excel não poderá aceitar valores que não sejam inteiros na célula; 3. Quando você posicionar o cursor na célula C5 aparecerá a mensagem “Digite um número Inteiro”; 4. Caso você digite um número que não seja inteiro será exibida a mensagem “Erro - Você deve digitar um nº inteiro”; 5. Na célula E5 deverá montar uma fórmula onde aparecer a mensagem “PAR” (na cor azul) ou “IMPAR” (na cor vermelha) de acordo com o valor digitado na célula C5.

DESAFIO 02 Utilizando a planilha abaixo precisamos gerar o Nome Completo utilizando como base a Lista de Nomes. Você deve digitar a fórmula na primeira linha e depois arrastá-la para as demais obtendo o resultado sugerido. A linha 1 é o gabarito para o resultado que você deve obter ao aplicar as funções de Texto.



Na coluna B você deve obter um número que representa a posição da vírgula da coluna Lista de Nomes (Coluna A).



Na coluna C obtenha apenas o sobrenome (Silva).



Na coluna D obtenha apenas o prenome (S. Tatiane)



Na coluna E escreva o nome nesta sequência (S. Tatiane Silva)



Na coluna F obtenha apenas a letra abreviada com o ponto. (S.)



Na coluna G obtenha apenas o primeiro nome (Tatiane)



Na coluna H escreva o nome completo (Tatiane S. Silva)



Na coluna I crie um código para cada registro com o seguinte padrão: SIL001, CAR002, DIA003. Este padrão de código terá as 3 letras iniciais do nome da coluna A em maiúscula + uma sequência numérica de 3 dígitos (001).

Fone: 112084-0070


Pag.: 199

Microsoft Excel 2013 - Avançado DESAFIO 03 Utilizando os Cálculos e Funções Financeiras resolva o desafio abaixo.

Situação: Antônio tem 3 financiamentos conforme descritos abaixo: 1. 06 x 425,06 taxa de 19,05% a.a. (ao ano) 2. 09 x 587,08 taxa de 1,55% a.m. (ao mês) 3. 18 x 654,10 taxa de 11,89% a.s. (ao semestre) Antônio deseja trocar todos por um único financiamento que deseja liquidar em 12 meses a menor taxa dos financiamentos acima. Calcule: 1. Qual o saldo devedor total de Antônio? (VP) 2. Qual a menor taxa de juros encontrada? (Taxa) 3. Qual o valor das parcelas do novo financiamento? (PGTO)

Monte as tabelas abaixo para auxiliar nos cálculos:

DESAFIO 04 Monte o formulário abaixo e realiza as operações:

1. Utilize as Planilhas CLIENTES e PRODUTOS para alimentar o formulário.

Fone: 112084-0070


Pag.: 200

Microsoft Excel 2013 - Avançado 2. O nome do Cliente só deve aparecer quando o Código do Cliente tiver sido digitado, caso contrário deverá aparecer Em Branco 3. Este mesmo princípio deve ser aplicado para os campos Endereço, Cidade e Telefone. 4. Se o código do Cliente (digitado) não existir, no campo NOME deverá aparecer a mensagem "Cliente não Cadastrado" enquanto que os campos Endereço, Cidade e Telefone deverão permanecer em Branco. 5. Os campos NOME DO PRODUTO e PREÇO UNITÁRIO deverão ser preenchidos somente quando for digitado um código de produto válido. 6. No campo NOME DO PRODUTO, deverá aparecer a mensagem PRODUTO NÃO CADASTRADO, quando for digitado um CÓDIGO DE PRODUTO não existente no cadastro. Neste caso, o PREÇO UNITÁRIO deverá ficar em Branco. 7. Se o campo PREÇO UNITÁRIO ou QTD estiver em Branco, o campo TOTAL também ficará em Branco. 8. No campo TOTAL DO PEDIDO, o valor só deverá aparecer se pelo menos um valor existir, caso contrário, ficará em branco. 9. O campo VENCIMENTO será sempre 30 dias após a DATA CORRENTE. 10. O VALOR DO DOCUMENTO apresenta o TOTAL DO PEDIDO. 11. A DATA DO PAGAMENTO será digitada na data efetiva do pagamento. 12. Haverá um DESCONTO de 5% para pagamentos anteriores à data do VENCIMENTO. 13. O valor de MORA corresponde a 0,06 por dia de atraso. 14. Será aplicado uma MULTA de 10% após 5 dias da data de VENCIMENTO. 15. O VALOR COBRADO será o VALOR DO DOCUMENTO - DESCONTO + MORA + MULTA 16. Todos os campos com FÓRMULAS deverão ser protegidos para evitar os desagradáveis acidentes de ALGUÉM apagá-las.

Fone: 112084-0070


Pag.: 201


Fone: 112084-0070


Pag.: 202


Resumo das Funções Função ABS AGORA

Definição

Categoria

Retorna o valor absoluto de um número, um número sem sinal. Retorna o número de série da data e da hora atuais

Matemática e Trigonometria

ALEATÓRIO

Retorna um número aleatório entre 0 e 1

ANO

Converte um número de série em um ano Arredonda um número até uma quantidade especificada de dígitos

ARRED ARREDONDAR.PARA.BAIXO ARREDONDAR.PARA.CIMA ARRUMAR BDCONTAR BDCONTARA BDDESVPA

BDEST BDEXTRAIR BDMÁX BDMÉDIA

BDMÍN

BDMULTIPL

BDSOMA BDVAREST BDVAREST BDVARP

BDVARP

Arredonda um número para baixo até zero. Arredonda um número para cima afastando-o de zero. Remove espaços do texto Conta as células que contêm números em um banco de dados Conta células não vazias em um banco de dados Calcula o desvio padrão com base na população inteira de entradas selecionadas de um banco de dados Estima o desvio padrão com base em uma amostra de entradas selecionadas de um banco de dados Extrai de um banco de dados um único registro que corresponde a um critério específico Retorna o valor máximo de entradas selecionadas de um banco de dados Calcula a média dos valores em uma coluna de uma lista ou banco de dados que corresponde às condições especificadas por você. Retorna o menor número de uma coluna em uma lista ou banco de dados que coincide com as condições especificadas por você. Multiplica os valores em um campo específico de registros que correspondem ao critério em um banco de dados Adiciona os números à coluna de campos de registros do banco de dados que correspondem ao critério Estima a variância com base em uma amostra de entradas selecionadas de um banco de dados Estima a variância com base em uma amostra de entradas selecionadas de um banco de dados Calcula a variância com base na população inteira de entradas selecionadas de um banco de dados Calcula a variância com base na população inteira de entradas selecionadas de um banco de dados

COLUNA

Retorna o número da coluna de uma referência

CONCATENAR

Agrupa vários itens de texto em um item de texto

Fone: 112084-0070


Data e Hora Matemática e Trigonometria Data e Hora Matemática e Trigonometria Matemática e Trigonometria Matemática e Trigonometria Texto Banco de dados Banco de dados Banco de dados

Banco de dados Banco de dados Banco de dados Banco de dados

Banco de dados

Banco de dados

Banco de dados Banco de dados Banco de dados Banco de dados

Banco de dados Pesquisa e referência Texto

Pag.: 203

Microsoft Excel 2013 - Avançado Função CONT.NÚM CONT.SE

CONT.SES CONT.VALORES CONTAR.VAZIO CORRESP DATA DATA.VALOR

DATADIF

DATAM DIA DIA.DA.SEMANA DIAS360 DIATRABALHO DIATRABALHOTOTAL DIREITA E É.NÃO.DISP

É.NÃO.TEXTO ÉCÉL.VAZIA ÉERRO ÉERROS ÉLÓGICO ÉNÚM ESQUERDA

Fone: 112084-0070

Definição

Categoria

Calcula quantos números há na lista de argumenEstatística tos Calcula o número de células não vazias em um intervalo que correspondem ao critério especifiEstatística cado Aplica critérios a células em vários intervalos e conta o número de vezes que todos os critérios Estatística são atendidos. Calcula quantos valores há na lista de argumenEstatística tos Conta o número de células vazias no intervalo Estatística especificado Retorna a posição relativa de um item em uma Pesquisa e matriz que coincide com um valor especificado referência em uma ordem específica. Retorna o número de série de uma data específiData e Hora ca Converte uma data armazenada como texto em um número de série que o Excel reconhece como Data e Hora data. É uma função não documentada do Excel, ou seja, ela existe e funciona muito bem. No entanto ela não faz parte da documentação do Excel. Não Documentada Esta função é muito útil para o calculo de idades e tempo de serviço. Retorna um número de série de data que é o número de meses indicado antes ou depois de Data e Hora data_inicial. Converte um número de série em um dia do mês Data e Hora Converte um número de série em um dia da seData e Hora mana Calcula o número de dias entre duas datas com Data e Hora base em um ano de 360 dias Retorna um número que representa uma data que é o número indicado de dias úteis antes ou após Data e Hora uma data (a data inicial). Retorna o número de dias úteis inteiros entre Data e Hora duas datas Retorna os caracteres mais à direita de um valor Texto de texto Retorna VERDADEIRO se todos os argumentos Lógica forem VERDADEIRO Valor se referir ao valor de erro #N/D (valor não Informações disponível). O valor faz referência a qualquer item que não seja texto. Observe que essa função retornará Informações VERDADEIRO se o valor fizer referência a uma célula em branco. Valor se referir a uma célula vazia. Informações Valor se referir a um valor de erro exceto #N/D. Informações Retorna VERDADEIRO se o valor for um valor de Informações erro Valor se referir a um valor lógico. Informações Valor se referir a um número. Informações Retorna os caracteres mais à esquerda de um Texto valor de texto


Pag.: 204

Microsoft Excel 2013 - Avançado Função EXT.TEXTO FALSO FIMMÊS HOJE HORA ÍMPAR ÍNDICE INT LIN MAIOR MAIÚSCULA MÁXIMO MÉDIA MÉDIASE

MÉDIASES MENOR MÊS MÍN MINÚSCULA MINUTO MOD NÃO NÃO.DISP NPER NÚM.CARACT NÚMSEMANA OU PAR PESQUISAR PGTO PRI.MAIÚSCULA

Fone: 112084-0070

Definição Retorna um número específico de caracteres de uma sequência de texto começando na posição especificada Retorna o valor lógico FALSO. Retorna o último dia do mês antes ou depois de um dado número de meses. Retorna o número de série da data de hoje Converte um número de série em uma hora Retorna o número arredondado para cima até o inteiro ímpar mais próximo. Retornar o valor de um elemento em uma tabela selecionado pelos índices de número de linha e coluna. Arredonda um número para baixo até o número inteiro mais próximo. Retorna o número da linha de uma referência Retorna o maior valor k-ésimo de um conjunto de dados Converte o texto em maiúsculas Retorna o valor máximo em uma lista de argumentos Retorna a média dos argumentos Retorna a média (média aritmética) de todas as células em um intervalo que satisfazem um determinado critério. Semelhante a função SOMASE só que neste caso ela calcula a média. Retorna a média (média aritmética) de todas as células que satisfazem vários critérios. Retorna o menor valor k-ésimo em um conjunto de dados Converte um número de série em um mês Retorna o valor mínimo na lista de argumentos Converte texto para minúsculas Converte um número de série em um minuto Retorna o resto depois da divisão de núm por divisor. Inverte o valor lógico do argumento Retorna o valor de erro #N/D. #N/D é o valor de erro que significa "não há nenhum valor disponível." Use NÃO.DISP para marcar células vazias. Retorna o número total de pagamentos ou períodos de um investimento. Retorna o número de caracteres em uma sequência de texto Retorna o número que indica onde se encontra a semana, numericamente, em um ano. Retorna VERDADEIRO se um dos argumentos for VERDADEIRO Retorna o número arredondado para o inteiro par mais próximo. Localiza um valor de texto dentro de outro (não diferencia maiúsculas de minúsculas). Retorna o pagamento periódico de uma anuidade Coloca a primeira letra de cada palavra em maiúscula em um valor de texto


Categoria Texto Lógica Data e Hora Data e Hora Data e Hora Matemática e Trigonometria Pesquisa e referência Matemática e Trigonometria Pesquisa e referência Estatística Texto Estatística Estatística Matemática e Trigonometria Matemática e Trigonometria Estatística Data e Hora Estatística Texto e Dados Data e Hora Não Lógica Informações Financeira Texto e Dados Data e Hora Lógica Matemática e Trigonometria Texto Financeira Texto e Dados

Pag.: 205

Microsoft Excel 2013 - Avançado Função PROCH PROCURAR PROCV RAIZ

Definição

Categoria

Procura na linha superior de uma matriz e retorna o valor da célula especificada Procura um valor de texto dentro de outro (diferencia maiúsculas de minúsculas). Procura na primeira coluna de uma matriz e move ao longo da linha para retornar o valor de uma célula

Pesquisa e referência

Retorna uma raiz quadrada positiva

SEGUNDO

Repete um texto um determinado número de vezes Especifica um teste lógico a ser executado Retorna um valor especificado se uma fórmula gerar um erro; caso contrário, retorna o resultado da fórmula. Converte um número de série em um segundo

SOMA

Adiciona os argumentos

REPT SE SEERRO

TEXTO

Adiciona as células especificadas por um determinado critério Soma as células em um intervalo (intervalo: duas ou mais células em uma planilha. As células de um intervalo podem ser adjacentes ou não adjacentes.) que atendem a vários critérios. Substitui um novo texto por um texto antigo em uma sequência de texto Retorna um subtotal em uma lista ou em um banco de dados. Retorna a taxa de juros ou taxa de desconto de um empréstimo ou investimento. Retorna um número arredondado para cima, afastando-o de zero, até o múltiplo mais próximo de significância. Formata um número e o converte em texto

TRUNC

Trunca um número para um inteiro

VALOR VERDADEIRO

Converte um argumento de texto em um número Retorna o valor lógico VERDADEIRO. Retorna o valor do investimento ou empréstimo depois da efetuação de todos os pagamentos. Retorna o valor de um investimento ou empréstimo no início do período de investimento.

SOMASE

SOMASES

SUBSTITUIR SUBTOTAL TAXA TETO

VF VP

Fone: 112084-0070


Texto Pesquisa e referência Matemática e Trigonometria Texto e Dados Lógica Informação Data e Hora Matemática e Trigonometria Matemática e Trigonometria Matemática e Trigonometria Texto e Dados Matemática e Trigonometria Financeira Matemática e Trigonometria Texto e Dados Matemática e Trigonometria Texto e Dados Lógica Financeira Financeira

Pag.: 206


Anotações

Fone: 112084-0070


Pag.: 207


Fone: 112084-0070


Pag.: 208


Fone: 112084-0070


Pag.: 209


Fone: 112084-0070


Pag.: 210


Fone: 112084-0070


Pag.: 211


Fone: 112084-0070


Pag.: 212

Apostila Excel 2013 Avançado Revisão 02 - 01-01-2016.pdf 5v535s

Overview 5o1f4z

More details 6z3438

Related Documents c2h70

Excel 2013 e5214

Excel.2013.pl.biblia.2013-walkenbach.john.pdf 4j5t5r

02-contenido - Microsoft Excel k2n32

Apostila - Ms Excel z5f35

Apostila Aim Excel 561pa

Apostila De Excel Pleno 25w4f

More Documents from "Flavia Firmino" 5u5f6i

6g3p1u

6g3p1u

But So Because 1b3n4x

6g3p1u

6g3p1u

6g3p1u