15 fórmulas do Excel, atalhos de teclado e truques que economizarão muito tempo

3. Porcentagem

Para executar a fórmula de porcentagem no Excel, insira as células para as quais você está encontrando uma porcentagem no formato =A1/B1 . Para converter o valor decimal resultante em uma porcentagem, destaque a célula, clique na guia Início e selecione “Porcentagem” na lista suspensa de números.

Não existe uma “fórmula” do Excel para porcentagens em si, mas o Excel facilita a conversão do valor de qualquer célula em uma porcentagem para que você não fique preso ao cálculo e reinserção dos números por conta própria.

A configuração básica para converter o valor de uma célula em porcentagem está na guia Início do Excel. Selecione esta guia, destaque a(s) célula(s) que você gostaria de converter em porcentagem e clique no menu suspenso ao lado de Formatação Condicional (este botão de menu pode dizer "Geral" primeiro). Em seguida, selecione “Porcentagem” na lista de opções que aparece. Isso converterá o valor de cada célula que você destacou em uma porcentagem. Veja este recurso abaixo.

Lembre-se de que se você estiver usando outras fórmulas, como a fórmula de divisão (indicada como =A1/B1 ), para retornar novos valores, seus valores poderão aparecer como decimais por padrão. Simplesmente destaque suas células antes ou depois de executar esta fórmula e defina o formato dessas células para “Porcentagem” na guia Início – como mostrado acima.

4. Subtração

Para executar a fórmula de subtração no Excel, insira as células que você está subtraindo no formato =SUM(A1, -B1) . Isso subtrairá uma célula usando a fórmula SUM adicionando um sinal negativo antes da célula que você está subtraindo. Por exemplo, se A1 fosse 10 e B1 fosse 6, =SUM(A1, -B1) realizaria 10 + -6, retornando um valor de 4.

Assim como as porcentagens, a subtração também não tem fórmula própria no Excel, mas isso não significa que não possa ser feita. Você pode subtrair quaisquer valores (ou aqueles valores dentro das células) de duas maneiras diferentes.

• Usando a fórmula =SOMA. Para subtrair vários valores uns dos outros, insira as células que deseja subtrair no formato =SOMA(A1, -B1), com um sinal negativo (indicado por um hífen) antes da célula cujo valor você está subtraindo. Pressione enter para retornar a diferença entre as duas células incluídas nos parênteses. Veja como isso fica na captura de tela acima.
• Usando o formato, =A1-B1 . Para subtrair vários valores uns dos outros, basta digitar um sinal de igual seguido pelo seu primeiro valor ou célula, um hífen e o valor ou célula que você está subtraindo. Pressione Enter para retornar a diferença entre os dois valores.

5. Multiplicação

Para executar a fórmula de multiplicação no Excel, insira as células que você está multiplicando no formato =A1*B1 . Esta fórmula usa um asterisco para multiplicar a célula A1 pela célula B1. Por exemplo, se A1 for 10 e B1 for 6, =A1*B1 retornará um valor de 60.

Você pode pensar que a multiplicação de valores no Excel tem sua própria fórmula ou usa o caractere “x” para denotar a multiplicação entre vários valores. Na verdade, é tão fácil quanto um asterisco — *.

Para multiplicar dois ou mais valores em uma planilha do Excel, realce uma célula vazia. Em seguida, insira os valores ou células que deseja multiplicar no formato =A1*B1*C1 … etc. O asterisco multiplicará efetivamente cada valor incluído na fórmula.

Pressione Enter para retornar o produto desejado. Veja como isso fica na captura de tela acima.

6. Divisão

Para executar a fórmula de divisão no Excel, insira as células que você está dividindo no formato =A1/B1 . Esta fórmula usa uma barra, “/,” para dividir a célula A1 pela célula B1. Por exemplo, se A1 for 5 e B1 for 10, =A1/B1 retornará um valor decimal de 0,5.

A divisão no Excel é uma das funções mais simples que você pode executar. Para fazer isso, realce uma célula vazia, insira um sinal de igual, “=,” e continue com os dois (ou mais) valores que você gostaria de dividir com uma barra, “/,” entre eles. O resultado deve estar no seguinte formato: =B2/A2 , conforme mostrado na captura de tela abaixo.

Pressione Enter e o quociente desejado deve aparecer na célula que você destacou inicialmente.

7. DATA

A fórmula Excel DATE é denotada =DATE(year, month, day) . Essa fórmula retornará uma data que corresponde aos valores inseridos entre parênteses — mesmo valores referidos de outras células. Por exemplo, se A1 for 2018, B1 for 7 e C1 for 11, =DATE(A1,B1,C1) retornará 11/07/2018.

Criar datas nas células de uma planilha do Excel pode ser uma tarefa inconstante de vez em quando. Felizmente, existe uma fórmula útil para facilitar a formatação de suas datas. Existem duas maneiras de usar essa fórmula:

• Crie datas de uma série de valores de célula. Para fazer isso, destaque uma célula vazia, digite “=DATE” e entre parênteses, insira as células cujos valores criam a data desejada – começando com o ano, depois o número do mês e depois o dia. O formato final deve ficar assim: =DATA(ano, mês, dia). Veja como isso fica na captura de tela abaixo.
• Defina automaticamente a data de hoje. Para fazer isso, realce uma célula vazia e insira a seguinte sequência de texto: =DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY())) . Pressionar enter retornará a data atual em que você está trabalhando em sua planilha do Excel.

Em qualquer uso da fórmula de data do Excel, sua data de retorno deve estar no formato “mm/dd/aa” — a menos que seu programa Excel seja formatado de forma diferente.

8. Matriz

Uma fórmula de matriz no Excel envolve uma fórmula simples em caracteres de chave usando o formato =(Start Value 1:End Value 1)*(Start Value 2:End Value 2) . Ao pressionar ctrl+shift+center, isso calculará e retornará o valor de vários intervalos, em vez de apenas células individuais adicionadas ou multiplicadas umas pelas outras.

Calcular a soma, o produto ou o quociente de células individuais é fácil — basta usar a fórmula =SOMA e inserir as células, valores ou intervalo de células em que deseja realizar essa aritmética. Mas e quanto a vários intervalos? Como você encontra o valor combinado de um grande grupo de células?

Matrizes numéricas são uma maneira útil de executar mais de uma fórmula ao mesmo tempo em uma única célula para que você possa ver uma soma, diferença, produto ou quociente final. Se você deseja encontrar a receita total de vendas de várias unidades vendidas, por exemplo, a fórmula de matriz no Excel é perfeita para você. Veja como você faria isso:

1. Para começar a usar a fórmula de matriz, digite “=SOMA” e, entre parênteses, insira o primeiro de dois (ou três ou quatro) intervalos de células que deseja multiplicar. Veja como pode ser o seu progresso: =SOMA(C2:C5
2. Em seguida, adicione um asterisco após a última célula do primeiro intervalo que você incluiu em sua fórmula. Isso significa multiplicação. Após este asterisco, insira seu segundo intervalo de células. Você estará multiplicando este segundo intervalo de células pelo primeiro. Seu progresso nesta fórmula agora deve ficar assim: =SOMA(C2:C5*D2:D5)
3. Pronto para pressionar Enter? Não tão rápido... Por essa fórmula ser tão complicada, o Excel reserva um comando de teclado diferente para matrizes. Depois de fechar os parênteses em sua fórmula de matriz, pressione C trl+Shift+Enter . Isso reconhecerá sua fórmula como uma matriz, envolvendo sua fórmula em caracteres de chave e retornando com êxito seu produto de ambos os intervalos combinados.

Nos cálculos de receita, isso pode reduzir significativamente seu tempo e esforço. Veja a fórmula final na captura de tela acima.

9. CONTAGEM

A fórmula COUNT no Excel é denotada =COUNT(Start Cell:End Cell) . Essa fórmula retornará um valor igual ao número de entradas encontradas no intervalo de células desejado. Por exemplo, se houver oito células com valores inseridos entre A1 e A10, =COUNT(A1:A10) retornará um valor de 8.

A fórmula COUNT no Excel é particularmente útil para planilhas grandes , nas quais você deseja ver quantas células contêm entradas reais. Não se engane: esta fórmula não fará nenhuma matemática nos valores das próprias células. Esta fórmula é simplesmente para descobrir quantas células em um intervalo selecionado estão ocupadas com algo.

Usando a fórmula em negrito acima, você pode facilmente executar uma contagem de células ativas em sua planilha. O resultado será um pouco mais ou menos assim:

10. MÉDIA

Para executar a fórmula média no Excel, insira os valores, células ou intervalo de células das quais você está calculando a média no formato =MÉDIA(número1, número2 etc.) ou =MÉDIA(Valor inicial:Valor final) . Isso calculará a média de todos os valores ou intervalo de células incluídas nos parênteses.

Encontrar a média de um intervalo de células no Excel evita que você precise encontrar somas individuais e, em seguida, executar uma equação de divisão separada em seu total. Usando =AVERAGE como sua entrada de texto inicial, você pode deixar o Excel fazer todo o trabalho para você.

Para referência, a média de um grupo de números é igual à soma desses números, dividida pelo número de itens desse grupo.

11. SOMA

A fórmula SUMIF no Excel é denotada =SUMIF(intervalo, critérios, [intervalo de soma]) . Isso retornará a soma dos valores dentro de um intervalo desejado de células que atendem a um critério. Por exemplo, =SOMA(C3:C12,”>70.000″) retornaria a soma dos valores entre as células C3 e C12 apenas das células maiores que 70.000.

Digamos que você queira determinar o lucro gerado a partir de uma lista de leads associados a códigos de área específicos ou calcular a soma dos salários de determinados funcionários — mas somente se eles ficarem acima de um determinado valor. Fazer isso manualmente parece um pouco demorado, para dizer o mínimo.

Com a função SUMIF, não precisa ser — você pode facilmente somar a soma das células que atendem a determinados critérios, como no exemplo de salário acima.

• A fórmula : = SUMIF(intervalo, critérios, [sum_range])
  • Intervalo: O intervalo que está sendo testado usando seus critérios.
  • Critérios: os critérios que determinam quais células em Criteria_range1 serão adicionadas
  • [Sum_range]: Um intervalo opcional de células que você adicionará além do primeiro intervalo inserido. Este campo pode ser omitido.

No exemplo abaixo, queríamos calcular a soma dos salários superiores a US$ 70.000. A função SOMASE somou os valores em dólares que excederam esse número nas células C3 a C12, com a fórmula =SOMA(C3:C12,”>70.000″) .

12. TRIM

A fórmula TRIM no Excel é denotada =TRIM(text) . Essa fórmula removerá todos os espaços inseridos antes e depois do texto inserido na célula. Por exemplo, se A2 inclui o nome "Steve Peterson" com espaços indesejados antes do primeiro nome, =TRIM(A2) retornaria "Steve Peterson" sem espaços em uma nova célula.

E-mail e compartilhamento de arquivos são ferramentas maravilhosas no local de trabalho de hoje. Isto é, até que um de seus colegas lhe envie uma planilha com um espaçamento bem esquisito. Esses espaços não autorizados podem dificultar a pesquisa de dados, mas também afetam os resultados quando você tenta somar colunas de números.

Em vez de remover e adicionar espaços meticulosamente conforme necessário, você pode limpar qualquer espaçamento irregular usando a função TRIM, que é usada para remover espaços extras dos dados (exceto espaços simples entre palavras).

• A fórmula : =TRIM(texto).
  • Texto: O texto ou célula da qual você deseja remover espaços.

Aqui está um exemplo de como usamos a função TRIM para remover espaços extras antes de uma lista de nomes. Para fazer isso, inserimos =TRIM(“A2”) na barra de fórmulas e replicamos isso para cada nome abaixo dele em uma nova coluna ao lado da coluna com espaços indesejados.

Abaixo estão algumas outras fórmulas do Excel que podem ser úteis à medida que suas necessidades de gerenciamento de dados crescem.

13. ESQUERDA, MÉDIO e DIREITO

Digamos que você tenha uma linha de texto dentro de uma célula que deseja dividir em alguns segmentos diferentes. Em vez de redigitar manualmente cada parte do código em sua respectiva coluna, os usuários podem aproveitar uma série de funções de string para desconstruir a sequência conforme necessário: LEFT, MID ou RIGHT.

DEIXEI

• Finalidade : Usado para extrair os primeiros X números ou caracteres em uma célula.
• A fórmula : = LEFT(texto, número_de_caracteres)
  • Text : A string da qual você deseja extrair.
  • Number_of_characters : O número de caracteres que você deseja extrair a partir do caractere mais à esquerda.

No exemplo abaixo, digitamos =LEFT(A2,4) na célula B2 e copiamos para B3:B6. Isso nos permitiu extrair os primeiros 4 caracteres do código.

MEIO

• Finalidade : Usado para extrair caracteres ou números no meio com base na posição.
• A fórmula : = MID(texto, posição_inicial, número_de_caracteres)
  • Text : A string da qual você deseja extrair.
  • Start_position : A posição na string da qual você deseja começar a extrair. Por exemplo, a primeira posição na string é 1.
  • Number_of_characters : O número de caracteres que você deseja extrair.

Neste exemplo, inserimos =MID(A2,5,2) na célula B2 e a copiamos para B3:B6. Isso nos permitiu extrair os dois números começando na quinta posição do código.

CERTO

• Finalidade: Usado para extrair os últimos X números ou caracteres em uma célula.
• A fórmula: = DIREITA(texto, número_de_caracteres)
  • Text : A string da qual você deseja extrair.
  • Number_of_characters : O número de caracteres que você deseja extrair a partir do caractere mais à direita.

Para este exemplo, inserimos =RIGHT(A2,2) na célula B2 e a copiamos para B3:B6. Isso nos permitiu extrair os dois últimos números do código.

14. PROCV

Este é antigo, mas bom - e é um pouco mais profundo do que algumas das outras fórmulas que listamos aqui. Mas é especialmente útil para aqueles momentos em que você tem dois conjuntos de dados em duas planilhas diferentes e deseja combiná-los em uma única planilha.

Minha colega Rachel Sprung – cujo tutorial “Como usar o Excel” é leitura obrigatória para quem quer aprender – usa uma lista de nomes, endereços de e-mail e empresas como exemplo. Se você tiver uma lista de nomes de pessoas ao lado de seus endereços de e-mail em uma planilha e uma lista dos endereços de e-mail dessas mesmas pessoas ao lado de seus nomes de empresas na outra, mas quiser os nomes, endereços de e-mail e nomes de empresas dessas pessoas para aparecer em um só lugar - é aí que entra o VLOOKUP.

Nota: Ao usar esta fórmula, você deve ter certeza de que pelo menos uma coluna aparece de forma idêntica em ambas as planilhas. Vasculhe seus conjuntos de dados para garantir que a coluna de dados que você está usando para combinar suas informações seja exatamente a mesma, incluindo sem espaços extras.

• A fórmula: PROCV(valor de pesquisa, matriz de tabela, número da coluna, [pesquisa de intervalo])
  • Valor de pesquisa: o valor idêntico que você tem nas duas planilhas. Escolha o primeiro valor em sua primeira planilha. No exemplo de Sprung a seguir, isso significa o primeiro endereço de e-mail na lista ou célula 2 (C2).
  • Matriz de tabela: o intervalo de colunas na planilha 2 de onde você extrairá seus dados, incluindo a coluna de dados idêntica ao seu valor de pesquisa (no nosso exemplo, endereços de e-mail) na planilha 1, bem como a coluna de dados que você está tentando copiar para a Planilha 1. Em nosso exemplo, esta é “Planilha2!A:B”. “A” significa Coluna A na Planilha 2, que é a coluna na Planilha 2 onde estão listados os dados idênticos ao nosso valor de pesquisa (e-mail) na Planilha 1. O “B” significa Coluna B, que contém as informações disponíveis apenas na Planilha 2 que você deseja traduzir para a Planilha 1.
  • Número da Coluna: A matriz da tabela informa ao Excel onde (qual coluna) estão localizados os novos dados que você deseja copiar para a Planilha 1. Em nosso exemplo, esta seria a coluna “House”, a segunda em nosso array de tabelas, tornando-se a coluna número 2.
  • Pesquisa de intervalo: use FALSE para garantir que você obtenha apenas correspondências de valor exato.
• A fórmula com as variáveis ​​do exemplo do Sprung abaixo: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)

Neste exemplo, a Planilha 1 e a Planilha 2 contêm listas que descrevem informações diferentes sobre as mesmas pessoas, e o fio condutor entre as duas são seus endereços de e-mail. Digamos que queremos combinar os dois conjuntos de dados para que todas as informações da casa da Planilha 2 sejam traduzidas para a Planilha 1. Veja como isso funcionaria:

15. ALEATÓRIO

Há um ótimo artigo que compara a fórmula RANDOMIZE do Excel para embaralhar um baralho de cartas. O baralho inteiro é uma coluna e cada carta - 52 em um baralho - é uma linha. “Para embaralhar o baralho”, escreve Steve McDonnell, “você pode calcular uma nova coluna de dados, preencher cada célula da coluna com um número aleatório e classificar a pasta de trabalho com base no campo de número aleatório”.

Em marketing, você pode usar esse recurso quando quiser atribuir um número aleatório a uma lista de contatos — como se quisesse experimentar uma nova campanha de e-mail e tivesse que usar critérios cegos para selecionar quem a receberia. Ao atribuir números a esses contatos, você pode aplicar a regra: “Qualquer contato com um número igual ou superior a 6 será adicionado à nova campanha”.

• A fórmula: RAND ()
  • Comece com uma única coluna de contatos. Em seguida, na coluna adjacente a ele, digite “RAND()” — sem as aspas — começando com a linha do contato superior.
• • RANDBETWEEN permite que você dite o intervalo de números que deseja atribuir. No caso deste exemplo, eu queria usar de um a 10.
  • bottom: O número mais baixo no intervalo.
  • top: O número mais alto no intervalo, Para o exemplo abaixo: RANDBETWEEN (bottom,top)
  • Fórmula no exemplo abaixo: =RANDBETWEEN(1,10)

Coisas úteis, certo? Agora, a cereja do bolo: depois de dominar a fórmula do Excel necessária, você desejará replicá-la para outras células sem reescrever a fórmula. E, felizmente, há uma função do Excel para isso também. Confira abaixo.

Às vezes, você pode querer executar a mesma fórmula em uma linha ou coluna inteira de sua planilha. Digamos, por exemplo, que você tenha uma lista de números nas colunas A e B de uma planilha e queira inserir os totais individuais de cada linha na coluna C.

Obviamente, seria muito tedioso ajustar os valores da fórmula para cada célula para que você encontre o total dos respectivos números de cada linha. Felizmente, o Excel permite que você complete a coluna automaticamente; tudo o que você precisa fazer é inserir a fórmula na primeira linha. Confira os seguintes passos:

1. Digite sua fórmula em uma célula vazia e pressione “Enter” para executar a fórmula.
2. Passe o cursor sobre o canto inferior direito da célula que contém a fórmula. Você verá um pequeno símbolo “+” em negrito aparecer.
3. Enquanto você pode clicar duas vezes neste símbolo para preencher automaticamente a coluna inteira com sua fórmula, você também pode clicar e arrastar o cursor para baixo manualmente para preencher apenas um comprimento específico da coluna. Depois de chegar à última célula da coluna na qual deseja inserir sua fórmula, solte o mouse para copiar a fórmula. Em seguida, basta verificar cada novo valor para garantir que corresponda às células corretas.

Atalhos de teclado do Excel

1. Selecione rapidamente linhas, colunas ou toda a planilha.

Talvez você esteja sem tempo. Quero dizer, quem não é? Sem tempo, sem problemas. Você pode selecionar sua planilha inteira em apenas um clique. Tudo o que você precisa fazer é clicar na guia no canto superior esquerdo da sua planilha para destacar tudo de uma vez.

Quer apenas selecionar tudo em uma coluna ou linha específica? Isso é tão fácil com estes atalhos:

Para Mac:

• Selecione Coluna = Comando + Shift + Baixo/Cima
• Selecione Linha = Comando + Shift + Direita/Esquerda

Para computador:

• Selecione Coluna = Controle + Shift + Baixo/Cima
• Selecione Linha = Control + Shift + Direita/Esquerda

Esse atalho é especialmente útil quando você está trabalhando com conjuntos de dados maiores, mas só precisa selecionar uma parte específica dele.

2. Abra, feche ou crie rapidamente uma pasta de trabalho.

Precisa abrir, fechar ou criar uma pasta de trabalho em tempo real? Os atalhos de teclado a seguir permitirão que você conclua qualquer uma das ações acima em menos de um minuto.

Para Mac:

• Abrir = Comando + O
• Fechar = Comando + W
• Criar Novo = Comando + N

Para computador:

• Aberto = Controle + O
• Fechar = Controle + F4
• Criar Novo = Controle + N

3. Formate os números em moeda.

Tem dados brutos que deseja transformar em moeda? Quer se trate de valores salariais, orçamentos de marketing ou vendas de ingressos para um evento, a solução é simples. Basta destacar as células que você deseja reformatar e selecionar Control + Shift + $ .

Os números serão convertidos automaticamente em valores em dólares – completos com cifrões, vírgulas e pontos decimais.

Nota: Este atalho também funciona com porcentagens. Se você quiser rotular uma coluna de valores numéricos como números de “porcentagem”, substitua “$” por “%”.

4. Insira a data e hora atuais em uma célula.

Esteja você registrando postagens de mídia social ou acompanhando as tarefas que está verificando em sua lista de tarefas, convém adicionar um carimbo de data e hora à sua planilha. Comece selecionando a célula à qual deseja adicionar essas informações.

Em seguida, dependendo do que você deseja inserir, siga um destes procedimentos:

• Inserir data atual = Control + ; (ponto e vírgula)
• Inserir hora atual = Control + Shift + ; (ponto e vírgula)
• Inserir data e hora atuais = Control + ; (ponto e vírgula) , SPACE e depois Control + Shift + ; (ponto e vírgula) .

Outros truques do Excel

1. Personalize a cor de suas guias.

Se você tiver várias planilhas diferentes em uma pasta de trabalho - o que acontece com a maioria de nós - facilite a identificação de onde você precisa ir codificando as guias por cores. Por exemplo, você pode rotular os relatórios de marketing do mês passado com vermelho e os deste mês com laranja.

Basta clicar com o botão direito do mouse em uma guia e selecionar "Cor da guia". Aparecerá um pop-up que permite que você escolha uma cor de um tema existente ou personalize uma para atender às suas necessidades.

2. Adicione um comentário a uma célula.

Quando quiser fazer uma anotação ou adicionar um comentário a uma célula específica em uma planilha, basta clicar com o botão direito do mouse na célula que deseja comentar e clicar em Inserir comentário . Digite seu comentário na caixa de texto e clique fora da caixa de comentários para salvá-lo.

As células que contêm comentários exibem um pequeno triângulo vermelho no canto. Para ver o comentário, passe o mouse sobre ele.

3. Copie e duplique a formatação.

Se você já passou algum tempo formatando uma planilha ao seu gosto, provavelmente concorda que não é exatamente a atividade mais agradável. Na verdade, é bem tedioso.

Por esse motivo, é provável que você não queira repetir o processo da próxima vez – nem precisa. Graças ao Format Painter do Excel, você pode copiar facilmente a formatação de uma área de uma planilha para outra.

Selecione o que você gostaria de replicar e, em seguida, selecione a opção Format Painter - o ícone de pincel - no painel. O ponteiro exibirá um pincel, solicitando que você selecione a célula, o texto ou a planilha inteira à qual deseja aplicar essa formatação, conforme mostrado abaixo:

4. Identifique valores duplicados.

Em muitos casos, valores duplicados – como conteúdo duplicado ao gerenciar SEO – podem ser problemáticos se não forem corrigidos. Em alguns casos, porém, você simplesmente precisa estar ciente disso.

Qualquer que seja a situação, é fácil exibir quaisquer valores duplicados existentes em sua planilha em apenas algumas etapas rápidas. Para fazer isso, clique na opção Formatação Condicional e selecione Realçar Regras de Célula > Valores Duplicados

Usando o pop-up, crie a regra de formatação desejada para especificar qual tipo de conteúdo duplicado você deseja apresentar.

No exemplo acima, procuramos identificar quaisquer salários duplicados dentro do intervalo selecionado e formatamos as células duplicadas em amarelo.

Atalhos do Excel economizam seu tempo

No marketing, o uso do Excel é bastante inevitável – mas com esses truques, não precisa ser tão assustador. Como dizem, a prática leva à perfeição. Quanto mais você usar essas fórmulas, atalhos e truques, mais eles se tornarão uma segunda natureza.

Nota do editor: Esta postagem foi publicada originalmente em janeiro de 2019 e foi atualizada para maior abrangência.