Excel básico (primeiras funções, matemática financeira, e funções avançadas)

Prévia do material em texto

BÁSICO: 
Operações: 
a) adição (+) 
b) subtração (-) 
c) multiplicação (*) 
d) divisão (/) 
e) potenciação (^) 
Ordem de execução: Parênteses → Potenciação e Radiciação → Divisão e Multiplicação → Adição e Subtração. 
A movimentação entre células de uma planilha pode ser feita através das teclas TAB (deslocamento de uma célula 
para direita), e ENTER (deslocamento de uma célula para baixo). 
Regras: 
I. Todas as funções do Excel começam com o símbolo de “=”. 
II. Ao começar a digitar a função desejada (Ex.: =som...), quando aparecer a descrição da função correspondente, 
você pode continuar a digitar, ou pressionar a tecla TAB para preenchimento automático. Lembre-se que 
acentos devem ser utilizados quando necessário. 
III. Quando você copia uma fórmula para uma célula adjacente (arrastando o conteúdo nos cantos da célula), o 
Excel atualiza as referências de linha e coluna para manter o mesmo padrão. Para evitar essa atualização 
automática, deve-se fixar o dado desejado, selecionando na barra de fórmula e pressionando F4. 
IV. Para expressões lógicas, pode-se utilizar os operadores relacionais: =, >, ≥, <, ≤ e <> (símbolo para “...é 
diferente de...”) 
 
PRIMEIRAS FUNÇÕES E SINTAXE: 
a) SOMA (num1; [num2]; ...): soma todos os números no intervalo de células. Caso não seja intervalo 
contíguo, pode realizar a soma célula a célula, clicando sobre uma célula desejada, e então pressionando 
“;” para separar os argumentos. 
Obs1.: Todo argumento passado entre os parênteses de uma função que aparecerem entre colchetes 
[ ] significa que são parâmetros opcionais ao funcionamento da função. 
Obs2.: existe a função AUTOSOMA que escolhe automaticamente o intervalo de soma, preferenciando 
primeiro somar células acima da presente célula, e em seguida, células a esquerda. 
b) MÁXIMO (num1; [num2]; ...): retorna o valor máximo de um conjunto de argumentos. 
c) MÍNIMO (num1; [num2]; ...): retorna o valor mínimo de um conjunto de argumentos. 
Obs.: Além das funções básicas MÁXIMO e MÍNIMO, podemos utilizar as funções MAIOR e MENOR, 
para obter diferentes posições de maiores, como o 1º (obtido através de MÁXIMO(intervalo) ou 
MAIOR(intervalo;1)), o 2º (MAIOR(intervalo;2)), e assim sucessivamente, até o penúltimo MENOR(intervalo;2), 
e o último (MÍNIMO(intervalo) ou MENOR(intervalo;1)). 
d) SE(expressão lógica; [valo_ se_verdadeiro] ; [valor_se_falso] ): verifica se uma condição foi satisfeita e 
retorna um valor se for VERDADEIRO, e outro se FALSO. 
Obs.: Muitas vezes, para aumentar a possibilidade das funções (como três possibilidades de resposta ou mais), 
podemos utilizá-las aninhadas, o que possibilita subdivisões da resposta. A função SE analisa os valores lógicos da 
esquerda para direita, assim ao aninhar mais de uma função SE, caso o primeiro argumento seja verdadeiro, as demais 
condições não serão processadas pelo Excel, assim, a seguinte análise pode ser feita: 
SE( X>60 ; “maior” ; SE( X>30 ; “mediano”; “menor”)) → Como resultado, teremos na célula “mediano” se 30 <
𝑥 ≤ 60, “maior” se 𝑋 > 60, e “menor” se 𝑥 ≤ 30. 
e) E(expressão_lógica_1; [expressão_lógica_2] ; ...): verifica se os argumentos são VERDADEIROS e retorna 
VERDADEIRO se todos argumentos forem VERDADEIRO. 
f) OU(expressão_lógica_1; [expressão_lógica_2]; ...): verifica se um argumento é VERDADEIRO e retorna 
VERDADEIRO ou FALSO. Retorna FALSO somente se todos os argumentos forem FALSOS. 
g) CONT.NÚM( valor1 ; [valor2]; ... ): calcula o número de células de um intervalo que contêm número. 
h) CONT.VALORES( valor1 ; [valor2]; ...): calcula o número de células em um intervalo que não estão vazias. 
i) CONTAR.VAZIO( intervalo ): conta o número de células vaziassem um intervalo de células especificado. 
j) ALEATORIOENTRE( núm_lim_inferior ; núm_lim_superior ): retorna um número aleatório entre os 
números especificados. 
k) A função HOJE() não tem parametros e retorna a data atual. Já a função AGORA() retorna a data e o 
horário. 
Obs.: Pode-se selecionar todas as células da planilha clicando num triângulo localizado no canto superior da 
planilha. 
FINANCEIRO: 
1. Função PGTO: simula os juros real em compras a prazo, com base no valor inicial, duração do financiamento, 
e a taxa de juros mensal constante. PGTO(taxa, tempo, valor) com retorno (resultado) negativo, significando 
dívida (valor das parcelas). Sintaxe: PGTO(taxa ; n_períodos ; valor_pago). 
2. Ao selecionar duas células com valores numéricos e propagar tais valores a mais células, o Excel 
automaticamente entende a relação entre as duas células selecionadas como uma progressão aritmética, 
assim, propaga a PA as demais células. 
REFERÊNCIAS RELATIVAS E ABSOLUTAS: 
Uma fórmula especificada numa célula do Excel, ao ser replicada (arrastada) para outra, preserva sua disposição 
espacial na planilha, assim, ao utilizar uma mesma célula para vários dados, a referência relativa não é indicada, e sim, 
a referência absoluta a posição de uma célula. 
Para referenciar de forma absoluta uma coluna, utilizamos o cifrão ($) antes da variável que queremos fixar (linha 
ou coluna). Ex.: travar coluna G: $G3 ; travar linha 3: G$3 ; travar célula G3: $G$3. Ao selecionar a célula, podemos 
fixá-la pressionando F4. Apertando F4 novamente, o Excel alterna entre as possibilidades de referenciação absoluta 
(célula → linha → coluna). 
Uma alternativa a utilização de referências absolutas é a utilização de nomes de intervalo (células ou matrizes), 
pois ao salvar tal intervalo com um determinado nome, arrastar fórmulas resultará num resultado correto, uma vez 
que todos se referem ao intervalo fixado (através do seu nome). Para extensas contas com um mesmo banco de dados, 
pode ser útil nomeá-lo, e sempre referenciá-lo por esse nome, para evitar ter que selecioná-lo todo o tempo. Para 
atribuir um nome a um intervalo, basta selecionar o campo á esquerda da barra de fórmulas do Excel, e inserir o nome 
desejado (Campo caixa de nome). 
Para realizar funções como soma entre mais de uma planilha com a mesma formatação, como por exemplo 
faturamento de mais de uma filial, produto, etc., podemos utilizar a função soma, e ao invés de selecionar célula por 
célula, podemos selecionar o intervalo de planilhas que serão utilizadas, selecionando-as utilizando o shift, e então 
escolhendo a célula que deve ser somada (por isso, as planilhas devem ter a mesma formatação). 
FUNÇÕES AVANCADAS 
1. SEERRO(valor; valor_se_erro) - Para situações que ocorram erro, pode-se utilizar a função SEERRO. Tal função 
retorna valor_se_erro se a expressão for um erro, caso contrário retorna o valor da expressão. Seu uso evita 
o uso de diferentes fórmulas nesses casos específicos de erro, como divisões por zero. 
2. ORDEM.EQ( número ; intervalo_de_referencia ;[...]): retorna a posição de um número em uma lista de 
números (o seu tamanho em relação a outros valores da lista). O terceiro argumento é optativo, e pode ser 0 
(significando ordem decrescente) ou 1 (ordem crescente), caso não seja especificado, será ordem decrescente. 
Permite uma comparação entre os termos, como um ranking, sem a necessidade de alterar a ordem de 
aparecimento dos dados na planilha, para melhor visualização e entendimento. 
3. SOMASE( intervalo_de_análise; critério_de_análise; intervalo_de_soma): adiciona as células especificadas por 
um determinado critério ou condição. De forma análoga, existe a função MEDIASE, que ao invés de retornar 
a soma, retorna a média, com a mesma sintaxe. Além destas, existe a CONT.SE que retorna uma contagem 
das células que satisfazem a condição analisada. 
Para uma análise mais profunda e pormenorizada de soma, podemos utilizar a função SOMASES, que adiciona as 
células especificadas por um dado conjunto de critérios ou condições. Sintaxe: SOMASES(intervalo_soma; 
intervalo_criterio_1 ; critério1 ; [intervalo critério 2] ; [critério 2] ;...). É importante salientar que a soma resultante é a 
soma dos fatorescujas condições sejam ambas verdadeiras, ou seja, todos os critérios devem ser satisfeitos para que 
o valor seja incluído na soma. Essa análise é válida, por exemplo, para casos em que se deseja o faturamento total das 
filiais de uma empresa, localizada em várias cidades, e por exemplo, dois estados diferentes possuem uma cidade de 
mesmo nome, evitando-se assim, confusão. Ex.: Bonito-BA e Bonito-PE, a verificação por nome seria confirmada em 
ambos os casos, mas seria diferenciado pelo critério “estado”. 
Analogamente a função SOMASES, temos a função MEDIASES, e a CONT.SES. 
Obs.: Para que um teste lógico nessas funções não seja apenas uma igualdade, podemos construir operações 
lógicas como parâmetros das funções, basta para isso, construí-las entre aspas duplas. Ex: “>=15000”. 
4. PROCV( valor_procurado (identificador único); matriz_tabela; num_indice_coluna; [procurar intervalo]): 
procura um valor na primeira coluna à esquerda de uma tabela e retorna um valor na mesma linha de uma 
coluna especificada. Como padrão, a tabela deve estar classificada em ordem crescente. Selecionar o 
cabeçalho da tabela é facultativo. O parâmetro [procurar intervalo] pode valer 0 (significando corresp. exata) 
ou 1 (corresp. aproximada), é opcional, e caso não seja escolhida, como padrão será aproximada. A 
correspondência aproximada é interessante ao fazer uma busca não mais por dados exatos, mas por intervalos 
de valores, assim, a função PROCV retorna o intervalo ao qual tal dado pertence. 
Analogamente a função PROCV, temos a função PROCH. A diferença entre as duas está na direção de busca de 
cada uma: PROCV (vertical), PROCH (horizontal). 
5. SOMARPRODUTO(matriz1; matriz2; [matriz3]) : retorna a soma dos produtos de intervalos ou matrizes 
correspondentes. Por exemplo, uma matriz com preço de produto, e outra com a quantidade disponível, 
podemos ver o valor total do inventário da empresa. 
6. CORRESP(valor_procurado; matriz_procurada; [tipo_correspondencia] ): retorna a posição relativa de um item 
em uma matriz (vetor 𝑛𝑥1) que corresponda a um valor específico em uma ordem específica. 
7. INDICE(matriz; num_linha; [num_coluna] ): retorna o valor ou a referência da célula na intersecção de uma 
linha e coluna específica, em um dado intervalo (vetor 𝑛𝑥1). 
A utilização das funções CORRESP e INDICE aninhadas permite um uso análogo á função PROCV. A diferença 
entre as duas possibilidades de utilização é devido a limitação da função PROCV, que obrigatóriamente faz a busca 
do valor_procurado na primeira coluna da matriz á esquerda, e somente pode retornar valores á direita dessa 
coluna. Já o uso aninhado dessas duas funções, permite mais flexibilidade na disposição dos dados na tabela, não 
sendo limitada por tal fator. 
Obs.: Ferramenta de Validação de Dados: caso seja necessária uma validação de dados, com quantidade 
limitada de opções (ou seja, tal campo não pode aceitar qualquer conjunto de informações, e sim, apenas um 
subconjunto restrito e limitado), pode-se selecionar a guia Dados → Validação de dados, e em seguida aplicar a um 
intervalo selecionado, a opção. Com isso, fornecendo as opções que podem ser inseridas em tais células, obtemos 
uma lista suspensa com tais valores, permitindo rápido e fácil preenchimento. Além disso, pode-se utilizar a validação 
de dados não apenas para preenchimento de respostas, mas como ferramenta na busca de diversas informações em 
uma tabela, em conjunto com funções de busca (PROCX e ÍNDICE). 
Permitem também uma validação de dados profunda e versátil. Nesse sentido, o valor da coluna e da linha 
passada como argumento para a função INDICE, é buscado pela função CORRESP. Assim, utilizando a opção de 
validação de dados, e a função CORRESP referenciada e essa célula, podemos ver diversas informações referente 
a um dado produto (por exemplo), sem mudar a fórmula, ou parâmetros relacionados a ela diretamente, e sim, 
apenas alterando a opção desejada, através da lista suspensa. 
8. PROCX( pesquisa_valor ; matriz_pesquisa ; matriz_retorno): Procura uma correspondência em um intervalo 
ou matriz e retorna o item correspondente de uma segunda matriz ou intervalo. Por padrão, é utilizada uma 
correspondência exata. É a evolução da PROCV/PROCH pois permite a busca por informações localizadas á 
esquerda da coluna utilizada para busca. A sintaxe é semelhante. 
9. DESLOC( referência, n_linhas ; n_colunas; [altura]; [largura]): retorna o conteúdo de uma célula (ou conjunto 
de células) localizada a n_linhas linhas de distancia e a n_colunas colunas de distancia do ponto de referência 
informado. Com tal função, é possível que o sistema atualize de forma automática por exemplo, a soma das 
ultimas 𝑥 vendas, mesmo que incrementemos mais vendas ao final do nosso banco de dados (onde a altura 
do nosso banco de dados é variável e calculado por uma função do tipo CONT). Com base nessa função, com 
auxílio da função DESLOC aliada a uma validação de dados, podemos criar uma soma de valores especificados 
pela validação de dados de forma automatizada. 
Para uma lista de dados dinâmicas, ao invés de pré-selecionarmos o intervalo de opções disponíveis (estático) 
podemos utilizar a função DESLOC para retornar uma matriz de dimensão dinâmica. Assim, utilizamos 
DESLOC(referência (titulo da lista de opções); 1 (para não incluír o titulo); 0 ;CONTVALORES(coluna onde estão as 
possibilidades de seleção -1)) por exemplo. Assim, a lista será atualizada assim que uma nova opção seja acrescida, 
tornando-a dinâmica, sem precisar de correção manual em caso de atualização da base de dados. Com a expressão 
que retorna a lista dinâmica, criamos o critério pedido na validação de dados. 
10. INDIRETO(texto_ref): retorna uma referência indicada por um valor de texto (Ex: “B3”). Muitas vezes 
precisamos concatenar a referência de uma dada célula antes de transformarmos para um texto, através das 
aspas. Para concatenar então, utilizamos o símbolo &. Dessa forma, para buscarmos a informação presente 
na célula dada por Salvador!G3, utilizamos: INDIRETO([célula com o nome das cidades]&”!G3”). Essa análise é 
pertinente quando as planilhas de cada filial são padronizadas. Além de usar o simbolo & para concatenar, 
temos uma função própria do Excel que faz essa função. CONCATENAR( texto_1 ; texto_2 ; [texto_3] ). 
11. ÚNICO (matriz): retorna os valores exclusivos de um intervalo ou matriz. Útil para se obter uma lista, por 
exemplo, dos diferentes cargos ou setores de uma empresa, a partir de uma tabel com todos os funcionários, 
onde o cargo se repete inúmeras vezes. 
12. PARÂMETRO (expressão; valor_1; resultado_1; valor_2 ; resultado_2; [valor_3]; [resultado_3]): avalia uma 
expressão em uma lista de valores, e retorna o resultado correspondente para o primeiro valor coincidente. 
Se não houver correspondência, será retornado um valor padrão opcional. Muito parecido a funções 
tradicionais de busca. 
13. FILTRO( matriz ; incluir ; [se vazia]): filtra um intervalo ou uma matriz. Retorna uma lista de valores associados 
a condição “incluir”.