Definindo e Criando uma Fórmula

2024 Autor: Geoffrey Carr | [email protected]. Última modificação: 2024-01-31 12:50

Nesta lição, apresentamos as regras básicas para criar fórmulas e usar funções. Nós sentimos que uma das melhores maneiras de aprender é através da prática, então nós fornecemos vários exemplos e os explicamos detalhadamente. Os tópicos que abordaremos incluem:

NAVEGAÇÃO ESCOLAR

Por que você precisa de fórmulas e funções?
Definindo e Criando uma Fórmula
Referência de célula relativa e absoluta e formatação
Funções úteis que você deve conhecer
Pesquisas, gráficos, estatísticas e tabelas dinâmicas

Linhas e colunas
exemplo de função matemática: SUM ()
operadores
operador precedente
exemplo função financeira: PMT (), pagamento de empréstimo
usando uma função "string" ("string" é a abreviação de "string de texto") dentro de uma fórmula e funções de aninhamento

As fórmulas são uma mistura de "funções", "operadores" e "operandos". Antes de escrevermos algumas fórmulas, precisamos criar uma função, mas antes de podermos criar uma função, primeiro precisamos entender a notação de linha e coluna.

Linhas e colunas

Para entender como escrever fórmulas e funções, você precisa saber sobre linhas e colunas.

As linhas são executadas horizontalmente e as colunas são executadas verticalmente. Para lembrar qual é qual, pense em uma coluna segurando um telhado - as colunas sobem e, assim, as linhas vão para a esquerda e para a direita.

Colunas são rotuladas por letras; linhas por números. A primeira célula na planilha é A1, ou seja, coluna A, linha 1. As colunas são rotuladas como A-Z. Quando o alfabeto acaba, o Excel coloca outra letra na frente: AA, AB, AC … AZ, BA, BC, BC, etc.

Exemplo: Função Sum ()

Agora vamos demonstrar como usar uma função.

Você usa funções digitando-as diretamente ou usando o assistente de função. O assistente de função é aberto quando você escolhe uma função do menu “Fórmulas” da “Biblioteca de funções”. Caso contrário, você pode digitar = em uma célula e um prático menu suspenso permitirá que você escolha uma função.

O assistente informa quais argumentos você precisa fornecer para cada função. Ele também fornece um link para instruções on-line se você precisar de ajuda para entender o que a função faz e como usá-la. Por exemplo, se você digitar = sum em uma célula, o assistente in-line mostrará quais argumentos são necessários para a função SUM.

Quando você digita uma função, o assistente está alinhado ou direto nos seus dedos. Quando você seleciona uma função no menu "Fórmulas", o assistente é uma caixa pop-up. Aqui está o assistente pop-up da função SUM ().

Para nossa primeira função, vamos usar SUM (), que adiciona uma lista de números.

Suponha que essa planilha contenha planos para orçamentar as férias de sua família:

Para calcular os custos totais, você pode escrever = b2 + b3 + b4 + b5, mas é mais fácil usar a função SUM ().

No Excel, procure o símbolo Σ no canto superior esquerdo da tela do Excel para encontrar o botão AutoSoma (matemáticos usam a letra grega Σ para adicionar uma série de números).

Se o cursor estiver abaixo dos números do orçamento familiar, o Excel é inteligente o suficiente para saber que você deseja somar a lista de números acima de onde você colocou o cursor, para que ele realce os números.

Pressione “enter” para aceitar o intervalo selecionado pelo Excel ou use o cursor para alterar quais células estão selecionadas.

Se você olhar o que o Excel colocou na planilha, poderá ver que ele escreveu essa função:

Nesta fórmula, o Excel soma os números de B2 a B9. Note, nós deixamos algum quarto abaixo da linha 5 para que você possa adicionar ao orçamento de férias da família - o custo certamente subirá à medida que a lista de crianças do que eles querem fazer e onde eles querem ir cresce mais!

As funções matemáticas não funcionam com letras, portanto, se você colocar letras na coluna, o resultado será exibido como “#NAME?”, Conforme mostrado abaixo.

#NOME? indica que há algum tipo de erro. Pode ser qualquer número de coisas, incluindo:

referência de célula ruim
usando letras em funções matemáticas
omitindo argumentos obrigatórios
nome da função ortográfica errado
operações matemáticas ilegais como a divisão por 0

A maneira mais fácil de selecionar os argumentos em um cálculo é usar o mouse. Você pode adicionar ou remover da lista de argumentos para a função, ampliando ou diminuindo a caixa que o Excel desenha quando você move o mouse ou clica em outra célula.

Clicamos no topo do quadrado desenhado pelo Excel para tirar “passagens aéreas” do orçamento. Você pode ver o símbolo de mira que pode desenhar para aumentar ou diminuir o intervalo selecionado.

Pressione “enter” para confirmar os resultados.

Operadores de Cálculo

Existem dois tipos de operadores: matemática e comparação.

Operador de Matemática	Definição
+	Adição
–	subtração ou negação, por exemplo, 6 * -1 = -6
*	multiplicação
/	divisão
%	por cento
^	expoente, e. 2⁴= 2 ^ 4 = 2 * 2 * 2 * 2 =16

Existem outros operadores não relacionados à matemática como “&”, o que significa concatenar (unir fim-a-fim) duas strings. Por exemplo, = "Excel" e "é divertido" é igual a "Excel is Fun".

Agora olhamos para os operadores de comparação.

Operador de Comparação	Definição
=	é igual, por exemplo, 2 = 4 ou “b” = “b”
>	maior que, por exemplo, 4> 2 ou “b”> “a”
<	menos que, por exemplo, 2 <4 ou “a” <“b”
>=	maior ou igual a - outra maneira de pensar nisso é> = significa ou > ou =.
<=	menos que ou igual a.
	não é igual a, por exemplo, 4 <> 6

Como você pode ver acima, os operadores de comparação trabalham com números e texto.

Note, se você digitar = "a"> "b" em uma célula, ele dirá "FALSE", pois "a" não é maior que "b". "B" vem depois de "a" no alfabeto, então "a" > "B" ou "B"> "a"

Precedência de ordem do operador

A precedência de ordem é uma ideia da matemática. O Excel tem que seguir as mesmas regras da matemática. Este tópico é mais complicado, então respire fundo e vamos mergulhar.

Precedência de ordem significa a ordem em que o computador calcula a resposta. Como explicamos na lição 1, a área de um círculo é πr², que é o mesmo que π * r * r. Isto é não (πr)².

Então você tem que entender a precedência de ordem quando você escreve uma fórmula.

Geralmente, você pode dizer isto:

O Excel avalia primeiro os itens entre parênteses que funcionam de dentro para fora.
Em seguida, ele usa as regras de precedência de ordem da matemática.
Quando dois itens têm a mesma precedência, o Excel funciona da esquerda para a direita.

A precedência dos operadores matemáticos é mostrada abaixo, em ordem decrescente.

(e)	Quando parênteses são usados, eles substituem as regras normais de precedência. Isso significa que o Excel fará esse cálculo primeiro. Nós explicamos isso mais abaixo.
–	Negação, por exemplo, -1. Isso é o mesmo que multiplicar um número por -1. -4 = 4 * (-1)
%	Porcentagem, significa multiplicar por 100. Por exemplo, 0,003 = 0,3%.
^	Expoente, por exemplo, 10 ^ 2 = 100
* e /	Multiplique e divida. Como dois operadores podem ter a mesma precedência? Significa apenas que, se uma fórmula tiver mais dois operadores com a mesma precedência, o cálculo será feito da esquerda para a direita.
+ e -	Adição e subtração.

Existem outras regras de precedência relacionadas a cadeias de caracteres e operadores de referência. No momento, vamos apenas ficar com o que acabamos de cobrir. Agora, vamos ver alguns exemplos.

Exemplo: Calcular a área de um círculo

A área de um círculo é = PI () * raio ^ 2.

Olhando para a tabela acima, vemos que os expoentes vêm antes da multiplicação. Então o computador calcula primeiro o raio ^ 2 e depois multiplica o resultado por Pi.

Exemplo: Calcular um aumento no salário

Digamos que seu chefe decida que você está fazendo um ótimo trabalho e ele ou ela vai lhe dar um aumento de 10%! Como você calcularia seu novo salário?

Primeiro, lembre-se de que a multiplicação vem antes da adição.

É = salário + salário * 10% ou é = salário + (salário * 10%)?

Suponha que seu salário seja de $ 100. Com um aumento de 10%, seu novo salário será:

= 100 + 100 * 10% = 100 + 10 = 110

Você também pode escrever assim:

=100 + (100 * 10%) = 100 + 10 = 110

No segundo caso, tornamos a ordem de precedência explícita usando parênteses. Lembre-se de que os parênteses são avaliados antes de qualquer outra operação.

By the way, a maneira mais fácil de escrever isso é = salário * 110%

Parênteses podem ser aninhados dentro de um outro. Então, quando escrevemos (3 + (4 * 2)), trabalhando de dentro para fora, primeiro calculamos 4 * 2 = 8, então adicionamos 3 + 8 para obter 11.

Mais alguns exemplos

Aqui está outro exemplo: = 4 * 3 / 2. Qual é a resposta?

Vemos a partir das regras da tabela acima que * e / tem precedência igual. Então o Excel trabalha da esquerda para a direita, 4 * 3 = 12 primeiro, depois divide isso por 2 para obter 6.

Novamente, você pode tornar isso explícito escrevendo = (4 * 3) / 2

E quanto a 4 + 3 * 2?

O computador vê os operadores * e +. Então seguindo as regras de precedência (multiplicação vem antes da adição) ele calcula 3 * 2 = 6 primeiro, depois adiciona 4 para obter 10.

Se você quisesse mudar a ordem de precedência, escreveria = (4 + 3) * 2 = 14.

E quanto a este = -1 ^ 3?

Então resposta é -3 porque o computador calculou = (-1) ^ 3 = -1 * -1 * -1 = -1.

Lembre-se que tempos negativos negativos são positivos e negativos vezes positivos são negativos. Você pode ver isto assim (-1 * -1) * -1 = 1 * -1 = -1.

Portanto, há alguns exemplos de ordem e precedência matemática. Esperamos que isso ajude a esclarecer algumas coisas sobre como o Excel realiza os cálculos (e isso provavelmente é matemática suficiente para durar uma vida inteira para alguns de vocês).

Exemplo: pagamento do empréstimo da função (PMT)

Vejamos um exemplo para calcular um pagamento de empréstimo.

Comece criando uma nova planilha.

Formate os números com cifrões e use zero casas decimais, uma vez que não estamos interessados em centavos agora, porque eles não importam muito quando você está falando sobre dólares (no próximo capítulo, vamos explorar como formatar números em detalhes). Por exemplo, para formatar a taxa de juros, clique com o botão direito do mouse na célula e clique em "formatar células". Escolha a porcentagem e use duas casas decimais.

Da mesma forma, formate as outras células para “moeda” em vez de porcentagem e escolha “número” para o prazo do empréstimo.

Adicione a função SUM () ao total de despesas mensais.

Note o hipoteca célula não está incluída no total. O Excel não sabe que você deseja incluir esse número, pois não há nenhum valor nele. Portanto, tenha cuidado para estender a função SUM () para o topo, usando o cursor ou digitando E2, onde diz E3 para incluir a hipoteca na soma.

Coloque o cursor na célula de pagamento (B4).

No menu Fórmulas, selecione o menu suspenso “Financeiro” e, em seguida, selecione a função PMT. O assistente aparece:

Use o cursor para selecionar a “taxa”, “nper” (termo de empréstimo), “Pv” (“valor presente” ou valor do empréstimo). Observe que você deve dividir a taxa de juros por 12, uma vez que os juros são calculados mensalmente. Além disso, você precisa multiplicar o prazo do empréstimo em 12 anos para obter o prazo do empréstimo em meses. Pressione "OK" para salvar o resultado na planilha.

Observe que o pagamento é mostrado como um número negativo: -1013.37062. Para torná-lo positivo e adicioná-lo às despesas mensais, aponte para a célula hipotecária (E2). Digite “= -” e use o cursor para apontar para o campo de pagamento. A fórmula resultante é = -B4.

Suas despesas mensais são de US $ 1.863 - Ai!

Exemplo: Função de Texto

Aqui demonstramos como usar funções dentro de uma fórmula e funções de texto.

Suponha que você tenha uma lista de alunos, conforme mostrado abaixo. O primeiro e último nome está em um campo separado por uma vírgula. Precisamos colocar os nomes da última e da empresa em células separadas. Como vamos fazer isso?

Para resolver esse problema, você precisa usar um algoritmo - ou seja, um procedimento passo a passo para fazer isso.

Por exemplo, olhe para “Washington, George”. O procedimento para dividir isso em duas palavras seria:

Calcule o comprimento da string.
Encontre a posição da vírgula (isso mostra onde uma palavra termina e a outra começa).
Copie o lado esquerdo da corda até a vírgula.
Copie o lado direito da string da vírgula até o final.

Vamos discutir como fazer isso com o "George Washington" passo a passo no Excel.

Calcule o comprimento da corda com a função = LEN (A3) - o resultado é 18.
Agora encontre a posição da vírgula inserindo esta função = FIND (“,”, A3”) - o resultado é 11.
Agora pegue o lado esquerdo da corda até a vírgula e crie essa fórmula aninhada usando o resultado da Etapa 1: = ESQUERDA (A3, ENCONTRAR (“,”, A3) -1). Note, temos que subtrair 1 do comprimento porque FIND dá a posição da vírgula.

Aqui está o que tudo isso parece quando todas as funções são colocadas juntas em uma fórmula. Na célula B3, você pode ver que essa fórmula pega todas as informações da célula A3 e insere “Washington” nela.

Então, temos "Washington", agora precisamos pegar "George". Como fazemos isso?

Note que poderíamos ter salvo o resultado da Etapa 1 em uma célula por si só, digamos, B6, e depois escrever uma fórmula mais simples = ESQUERDA (A3, B6-1). Mas isso usa uma célula para o passo intermitente.

Lembre-se da posição da vírgula ou calcule-a novamente.
Calcule o comprimento da string.
Conte os caracteres do final da string para a vírgula.

Pegue o número de caracteres da Etapa 3 e subtraia um para omitir a vírgula e o espaço.

Vamos fazer isso passo a passo.

De cima, isso é = FIND (“,”, A3”)
O comprimento da string é = LEN (A3)
Você precisará usar um pouco de matemática para encontrar o número de caracteres a serem tomados: = LEN (A3) - FIND (",", A3) - 1
O lado direito da corda que queremos é = DIREITA (A3, LEN (A3) - ENCONTRAR (“,”, A3) - 1)

Agora, sua planilha deve ser semelhante à captura de tela abaixo. Copiamos as fórmulas como texto na parte inferior da planilha para facilitar a leitura e a visualização.

Aquele foi um pouco difícil, mas você só precisa escrever essas fórmulas uma vez.

A seguir …

Isto conclui nossa lição para hoje. Você deve ter um entendimento bastante firme agora de fórmulas e funções, linhas e colunas, e a maneira como tudo isso pode ser empregado através de vários exemplos definidos.

A seguir, na Lição 3, discutiremos a referência e a formatação da célula, além de mover e copiar fórmulas, para que você não precise reescrever cada fórmula repetidamente.