Baixe o app para aproveitar ainda mais
Prévia do material em texto
Inf. Mês Tags 1 Janeiro Como o Excel funciona como funciona o excel;linhas e colunas;barra de formula;endereço 2 Fevereiro Área de Impressão imprimir;área;impressão;impressao;area;imprimir;ferramentas 3 Março Soma soma;somar;sum;formula;funçao;função;fórmula;estatística;estatistica 4 Abril Média média;media;med;formula;funçao;função;fórmula;estatística;estatistica 5 Maio Máximo & Mínimo máximo;mínimo;maximo;minimo;número máximo;número mínimo;numero maximo;numero minimo;maior valor; menor valor;formula;funçao;função;fórmula;estatística;estatistica 6 Junho Maior & Menor maior;menor;rankear;posição;maior valor;menor valor;formula;funçao;função;fórmula;estatística;estatistica 7 Julho Cont.Valores - Cont.núm formula;funçao;cont.valores;cont.núm;cont.num;contvalores;contnum;contador;contadores;função;fórmula;estatística;estatistica 8 Agosto Concatenar concatenar;juntar;&;formula;funçao;função;fórmula 9 Setembro Subtotal subtotal;somar;media;contador;max;min;formula;funçao;função;fórmula 10 Outubro SE e suas variações se;e;ou;formula;condicional;formula;funçao;função;fórmula;condicionais 11 Novembro SE Composto se;ses;se composto;formula condicional;condicional;formula;funçao;função;fórmula;condicionais 12 Dezembro Procv procv;procurar;fórmula de busca;função de busca;proc;vertical;função;fórmula;busca;função;funcao Proch proch;procurar;fórmula de busca;função de busca;proc;vertical;função;fórmula;busca;função;funcao Validação de Dados validação;validacão;validacao;lista;ferramentas Formatação Condicional I formatação;formatacao;formatação condicional;condicional;ferramentas Remover Duplicatas remover duplicatas;remover;tirar valores duplos;duplos;valores iguais;ferramentas Agrupar Dados agrupar dados;juntar;grupo;ferramentas Congelar Painéis congelar;paineis;painéis;travar;linha superior;ferramentas Formatar como Tabela formatação;formatar;tabela;banco de dados;formatar como tabela;ferramentas Localizar & Substituir localizar;substituir;trocar;cade;cadê Hiperlink hiperlink;navegação;navegacao;botão Números formato;formatar;formato de número;numero;unidade;moeda;numero personalizado;porcentagem;fração;geral;hora;personalizado Gráficos gráficos;graficos;colunas;linha;pizza;combinação;eixo Somases somases;somar com condições;condição;somar de acordo;formula;funçao;condicional;função;fórmula;estatistica;estatística;condicionais Médiases médiases;mediases;media com condições;média de acordo;formula;funçao;condicional;função;fórmula;estatistica;estatística;condicionais Cont.ses Cont.ses;contador;contar com condições;contar de acordo;formula;funçao;condicional;função;fórmula;estatistica;estatística;condicionais Índice índice;indice;formulas de busca;buscar;procurar;formula;funçao;buscar;busca;função;fórmula Corresp corresp;corresponder;posição relativa;matriz;formula;funçao;buscar;busca;função;fórmula Escolher escolher;escolha;;formula;funçao;buscar;busca;função;fórmula Desloc desloc;deslocar;largura;altura;formula;funçao;função;fórmula;intervalo;variado;tamanho Seerro seerro;erro;tratamento de erro;função;fórmula;funçao;funcao Fórmulas de Data formulas de data;data;dia;mês;ano;hoje;formula;funçao;função;fórmula Tabela Dinâmica I tabela dinâmica;ferramentas;segmentação Barra de Rolagem barra de rolagem;botão de ação;rolagem;vba Botão de Rotação rotação;rotacao;rotacão;vba Caixa de Seleção caixa de seleção;seleção;caixa;botão de ação;check;vba Botão de Opção botão;opção;botão de opção;botão de ação;vba Caixa de Combinação caixa de combinação;combinação;caixa;botão de ação;lista suspensa;vba Solver solver;resolver;solução;análise;analise;ferramentas Macros macros;vba Tabela Dinâmica II tabela dinâmica;ferramentas;segmentação;modelo de dados;dados;bases Power Query - Consultas power;query;conexão;conexao;banco;dados;banco de dados;consultas Gráficos - Eixo Secundário gráficos;graficos;colunas;linha;2;;combinação;eixo Atalhos Dicas;Atalhos;dicas;atalhos;tecla;tecla de atalho;travar;fixar Responde aí Para iniciar o curso, basta navegar pelas abas aqui embaixo, colocamos o conteúdo em ordem pra te ajudar Como o Excel funciona • Introdução • Propriedades de uma célula (posição e inserção de textos e fórmulas) Colunas Linhas C22 D22 E22 C23 D23 E23 C24 D24 E24 Digite aqui • Referência relativa e absoluta Exemplos 2 Referência A A B C D E F A B C D E F A Referência Absoluta Abs Quadro 2 Quadro 2 - Resolvido =$E$93 Abs Abs Abs Abs Abs Abs Abs Abs Abs Referência de célula R$ 10.59 Quadro 3 R$ 10.59 Como o Excel funciona? Para inserir ou editar o texto você tem duas opções: 1. Selecionando a célula desejada e em seguida inserindo ou editando o texto. 2. Utilizando a barra de fórmulas que se encontra na parte superior da sua planilha. Ainda não conseguiu localizar? Veja a figura abaixo, vai te ajudar! Quer entender na prática? Muito simples... Basta selecionara celula deseja e em seguida digitar o texto pretendido. Veja o exemplo abaixo. Ao lado de 'Digite aqui', escreva o seu nome: Para ficar mais claro, uma referência de célula é, literalmente, referenciar-se a uma outra célula. Ou seja, você pode dizer que A2 é igual ao conteúdo da célula B2. Veja o exemplos abaixo: Na coluna 'Exemplo 2', temos alguns valores e na coluna ao lado 'Referência', é aonde vamos criar uma referência de célula. Na barra de fórmulas ou em uma célula, digite = (sinal de igual) e depois selecione a célula que contém o valor desejado ou digite sua referência de célula, conforme a imagem ao lado. De padrão, toda referência de célula é relativa, ou seja, utilizando o recurso de auto preenchimento você pode completar a coluna 'Referência' igualando aos respectivos valores da coluna 'Exemplo 2'. Para realizar o auto preenchimento, utilize a opção localizada no canto inferior (circulado de vermelho) da célula selecionada. Em seguinda pressione e arraste até onde você deseja criar a referência. Assim que for realizado o preenchimento, podemos perceber que houve alterações nas referências de colunas, ou seja, começas com a referência C81, e em seguida: D81, E81 até H81. Neste caso, a referência de célula se altera totalmente pelo fato de, ao ser realizado o preenchimento na vertical, o número da linha da referência tende a se modificar. No exemplo acima, a primeira célula referenciada foi igual à G61 (onde está a letra A), ao ser realizado o preenchimento na vertical, o número da referência foi se alterando, ou seja, G62, 63, 64, 65 e G66. Desta forma, temos o que chamamos de referência relativa. Obs.: O preenchimento pode ser realizado também na horizontal. Sendo assim, a coluna é a referência modificada. Observe o exemplo abaixo: Referência absoluta Referência absoluta é utilizando quando você deseja igualar todas as células da sua tabela a um único valor. Para tornar uma referência absoluta você pode inserir o $ manualmente ou apertar F4 entre a coluna e a linha da sua referência. Esta célula ao lado ("ABS") será a nossa ref. absoluta. Observe o seguinte: No Quadro 2, fizemos uma referência simples. Nesta referência, igualamos a primeira célula do quadro à celula E93, ou seja, onde está escrito "Abs". Logo em seguida, utilizamos a tecla do teclado F4 para torna-lá uma referência absoluta. Para ficar mais claro, você pode fazer o exemplo abaixo e ver como a sua referência se comportou ao torná-la absoluta. O preenchimento é valido para textos, números, dia da semana e mês que podem se alterar a medida que for realizando o preenchimento. Agora me diz... Conseguiu entender como o Excel funciona? Esperamos que este tópico possa te ajudar com os conceitos mais básicos. Os próximos tópicos serão focados em funções e ferramentas. Te garanto, será tão simples quanto se localizar no Excel. =) Afinal de contas... Como funciona o Excel? Dividido entre linhas e colunas, o Excel é um app incrível para quem deseja ter, em suas mãos, o gerenciamento total do seu negócio. Com a incrível capacidade de criar tabelas, indicadores e gráficos, o Excel é o software de gestão favorito das empresas. Depois desta pequena introdução, vamos ao que interessa. Atalhos Navegação Page Up Mova a tela para cima Page Down Mova a tela para baixo Alt + Page Up Mova a tela para a esquerda Alt + Page Down Mova a tela para a direita Enter Mova a seleção de célula para a próxima abaixo Shift + Enter Mova a seleção de célula para a próxima acima Shift + f10 Abre as opção de formatação de célula (mesmo menu do botão direito do mouse) Tab Mova a seleção de célula para próxima ao lado direito Shift + Tab Mova a seleção de célula para próxima ao lado esquerdo Ctrl + Page Up Troque de planilha dentro do mesmo arquivo, da esquerda para a direita. Ctrl + Page Down Troque de planilha dentro do mesmo arquivo, da direita para a esquerda. Home Mover para a primeira célula da coluna. Ctrl + Home Mover para a primeira célula do Excel (A1) Ctrl + End Mover para o último dado da planilha End + Setas Mover para último dado preenchido para a direção escolhida (As teclas não são usadas simultaneamente) Ctrl + Setas Mover para último dado preenchido para a direção escolhida (As teclas são usadas simultaneamente) Ctrl+L / Ctrl+U Abre a opção de Localizar e Substituir Ctrl+F1 Exibe ou oculta a Faixa de Opções Ctrl+G (ou F5) Abre a caixa de "Ir para" (Podemos escrever o endereço de uma célula) Navegação em Pastas de Trabalho (Arquivos) Ctrl+O Criar uma nova pasta de trabalho em branco Ctrl+Tab / Alt+Tab Move para a próxima pasta de trabalho Ctrl+Shift+Tab Move para a pasta de trabalho anterior Alt+Space Abre menu de controle do Excel (Minimizar, restaurar, fechar) Ctrl+F9 Minimiza a janela do Excel Ctrl+F10 Maximiza ou restaura a janela do Excel Alt+F4 Fecha a janela do Excel Ctrl+B Salvar o arquivo atual Ctrl+A Atalho para abrir um outro arquivo Ctrl+P Abre a página guia de impressão Ctrl+W Fecha a pasta de trabalho atual Edição de Células Ctrl+Z Desfaz a última ação Ctrl+Y Refaz a última ação Ctrl+C Copiar Células ou conteúdo Ctrl+X Recorta Células ou conteúdo Ctrl+V Cola Células ou conteúdo Ctrl+Alt+V Ctrl+Alt+V exibe a caixa de diálogo Colar Especial. Disponível somente depois que você recortar ou copiar um objeto, texto ou conteúdo de célula em uma planilha ou de outro programa. Crtl+D Copia e cola o conteúdo da célula de cima Ctrl + "- "(menos) Abre menu para excluir linhas/colunas/células Ctrl + "+" (mais) Abre menu para excluir linhas/colunas/células Ctrl+k Insere Hiperlink Ctrl+R Usa o comando Preencher à Direita para copiar o conteúdo e o formato da célula mais à esquerda de um intervalo selecionado nas células à direita. F2 Entra no modo de edição da célula (O mesmo que duplo clique no meio da célula). Edição dentro das células Alt+Enter Iniciar uma nova linha dentro da mesma célula Enter Confirmar o cálculo, fórmula ou texto digitado dentro da célula, direcionando para próxima célula abaixo Shift+Enter Confirmar o cálculo, fórmula ou texto digitado dentro da célula, direcionando para próxima célula Acima Tab Confirmar o cálculo, fórmula ou texto digitado dentro da célula, direcionando para próxima célula à direita Shift+Tab Confirmar o cálculo, fórmula ou texto digitado dentro da célula, direcionando para próxima célula à esquerda ESC Cancela a edição de dentro da célula Ctrl + ; (dois pontos) Insere a data atual na célula Crtl + Shift + ; (dois pontos) Insere a hora atual na célula Ocultar e Exibir Ctrl+9 Oculta linhas selecionadas Ctrl+Shift+9 Reexibe linhas ocultas (tem que selecionar o intervalo das linhas ocultas) Ctrl+0(zero) Oculta colunas selecionadas Ctrl+Shift+0(zero) Reexibe colunas ocultas (tem que selecionar o intervalo das colunas ocultas) Alt+Shift+Seta p/ Direta Agrupar linhas e/ou colunas (Agrupar dados) Alt+Shift+Seta p/ Esquerda Desagrupar linhas e/ou colunas Seleção de Células Ctrl+Shift+Setas Expande a seleção das células para as extremidades, onde contém dados. Seleciona linhas e colunas Shift+Setas Expande a seleção de células uma por uma Shift+Home Seleciona todas as células da linha até a primeira coluna da planilha Shift+Barra de espaço Seleciona a linha inteira Ctrl+Barra de espaço Seleciona a coluna inteira Ctrl+Shift+Barra de espaço / Ctrl + * Seleciona todas as células que estão ao redor da célula selecionada Ctrl+Shift+Page Down Seleciona a atual e próxima planilha para edição Ctrl+Shift+Page Up Seleciona a atual e a planilha anteriorpara edição Ctrl+Shift+O Seleciona todas as células que têm comentários Ctrl+Shift+Home Seleciona as células acima de onde está a seleção atual Shift+Backspace Seleciona somente a célula ativa quando várias células estão selecionadas Esc Cancela a seleção de cópia Shift+Setas Seleciona as letras dentro da célula. Letra por letra Ctrl+Shift+Setas Seleciona as palavras dentro da célula. Palavra por palavra Shift+Home / Shift+End Seleciona toda a frase dentro das células F8 Habilita estender a seleção a partir da célula ativa, utilizando as setas do teclado Ctrl + T Seleciona todas as células reconhecidas ao redor (Serve para selecionar tabela) Formatação de Texto Ctrl + 1 Abrir caixa de formatação de células Ctrl + N (ou Ctrl+2) Aplicar negrito Ctrl + i (ou Ctrl+3) Aplicar itálico Ctrl + s (or ctrl+4) Aplicar Sublinhado Ctrl + 5 Aplicar risco no meio do texto Formatação Geral Ctrl + Shift + $ Aplicar formato Moeda na célula R$ 152.52 Ctrl + Shift + % Aplicar formato Porcentagem na célula 28% Ctrl + Shift + # Aplicar formato Data na célula 25-Jan-23 Ctrl + Shift + @ Aplicar formato Hora na célula 7:49 Ctrl + Shift + ! Aplicar formato Número na célula, já formatado com ponto e vírgula com 2 casas decimais 23,568.27 Ctrl + Shift + ^ Aplicar formato Científico na célula 1.58E+03 F4 Repete a última formatação aplicada na célula (Qualquer formatação) - Ctrl + Shift + & Aplicar todas bordas externas nas células selecionadas - Ctrl + Shift + _ (underline) Remove bordas das células selecionadas - Mais atalhos de ferramentas e fórmulas = Sempre usado para iniciar uma fórmula Alt + = Insere Fórmula SOMA para os valores que estão acima F4 Fixa linhas e colunas na fórmula (Referência Absoluta e Relativa) Ctrl + Shift + U Expande a barra de fórmulas Ctrl + F3 Abre gerenciador de nomes Ctrl+Alt+T Exibe a caixa de diálogo Criar Tabela. (Formatar como tabela) F7 Abre a caixa de diálogo para verificar a ortografia F9 Calcula todas as planilhas em todas as pastas de trabalho abertas Shift+ F9 Calcula a planilha ativa F12 Exibe a caixa de diálogo Salvar como. Bora economizar tempo aprendendo algumas dicas e atalhos que o próprio Excel já nos oferece? Separamos algumas dicas que com certeza vão te ajudar Veja no exemplo abaixo: Atalhos Área de Impressão O jeito fácil e prático de imprimir suas planilhas. O primeiro passo é ir até a aba 'Layout de página', e no grupo 'Configuração de Página' localize 'Imprimir Títulos'. Área de Impressão Aba 'Planilha'. 1. O primeiro item a ser selecionado é a área de impressão. Ela pode ser uma tabela, um gráfico ou uma imagem qualquer. Dica: Se o item a ser impresso for uma tabela muito grande, você pode utilizar as teclas Ctrl + T para selecionar toda a tabela 2. No grupo 'Imprimir títulos' você tem a possibilidade de sempre repetir uma linha selecionada na parte superior da sua área de impressão, ou se for necessário, você pode repetir uma coluna à esquerda da sua área de impressão. 3. No terceiro grupo você pode configurar se deseja que as linhas de grade, títulos de linha/coluna e comentários fiquem visíveis na impressão. 4. O quarto grupo é a ordem de impressão, ou seja, se a sua tabela tem muitas colunas, você pode imprimir na ordem Acima e abaixo ou Abaixo e acima. Dica: Antes de qualquer impressão, você pode visualizar impressão para saber se está tudo de acordo com os seus critérios. Na aba 'Cabeçalho/Rodapé'. 1. Você pode personalizar seu cabeçalho ou rodapé com imagem, número da página, data, hora ou qualquer outro tipo de informação na qual você deseja inserir como padrão em suas impressões. Na aba 'Margens'. 1. Nesta aba você pode configurar a distância de todas as margens. 2. Para facilitar, os campos de valores para margem, cabeçalho e rodapé estão localizados em todos os pontos próximos ao gabarito (conforme a imagem ao lado). 3. Nesta aba, você também tem a possibilidade de centralizar tanto horizontalmente como verticalmente, a sua tabela de impressão. Na aba 'Página'. 1. Por fim, nesta aba você pode trabalhar a orientação, dimensionamento, ajustes do tamanho do papel e a qualidade de impressão. Dica: Depois de qualquer ajuste, clique em Visualizar impressão para poder ter certeza que todas as configurações. Soma Mês Produção Defeitos Jan 1257 35 Fev 1235 25 Mar 737 70 Abr 1462 34 Mai 1462 46 Jun 604 43 Jul 1132 64 Ago 1347 21 Set 1344 62 Out 935 38 Nov 919 36 Dez 778 64 Total 13212 Loja Quantidade Receitas Despesas Lucro Salvador 47000 R$ 1,012,850.00 R$ 76,000.00 R$ 936,850.00 Santos 39000 R$ 840,450.00 R$ 95,000.00 R$ 745,450.00 Niterói 58000 R$ 1,249,900.00 R$ 87,000.00 R$ 1,162,900.00 São Paulo 48000 R$ 1,034,400.00 R$ 51,000.00 R$ 983,400.00 Resende 400000 R$ 8,620,000.00 R$ 76,000.00 R$ 8,544,000.00 Lavras 460000 R$ 9,913,000.00 R$ 89,000.00 R$ 9,824,000.00 Barueri 12000 R$ 258,600.00 R$ 92,000.00 R$ 166,600.00 Total Nunca foi tão fácil somar números... Com a fórmula Soma é possível somarvalores selecionando um intervalo de dados. Como bem sabemos, qualquer fórmula no Excel deve ser iniciada com o sinal de "=", veja o exemplo abaixo: =Soma(valor1 ; [valor2] ; ...) - Simplificando tudo: Soma(Qual é o intervalo de dados que você deseja somar?) Onde: Valor 1, Valor 2, ..., Valor N, são os valores que você deseja somar, podendo ser números ou referências de células. Veja o exemplo a seguir: Exemplo 1. Você pode utilizar a fórmula Soma para obter os resultados operacionais das vendas de uma empresa, como no exemplo à seguir. Vejamos: 1 - Obtenha o total de cada item: Quantidade, Receita Bruta, Despesas e Lucro. Soma (SUM) SOMA(C20:C31) Média Mês Produção Defeitos Jan 1257 35 Fev 1235 25 Mar 737 70 Abr 1462 34 Mai 1462 46 Jun 604 43 Jul 1132 64 Ago 1347 21 Set 1344 62 Out 935 38 Nov 919 36 Dez 778 64 Média 1101 Loja Quantidade Receitas Despesas Lucro Salvador 47000 R$ 108,570.00 R$ 76,000.00 R$ 32,570.00 Santos 39000 R$ 90,090.00 R$ 95,000.00 R$ (4,910.00) Niterói 58000 R$ 133,980.00 R$ 87,000.00 R$ 46,980.00 São Paulo 48000 R$ 110,880.00 R$ 51,000.00 R$ 59,880.00 Resende 40000 R$ 92,400.00 R$ 76,000.00 R$ 16,400.00 Lavras 45000 R$ 103,950.00 R$ 89,000.00 R$ 14,950.00 Barueri 22000 R$ 50,820.00 R$ 92,000.00 R$ (41,180.00) Média Vamos direto ao ponto... A fórmula a seguir retorna a média dos valores selecionados. Veja como: =Média(valor1 ; [valor2];...) - Simplificando tudo: Média(Qual intervalo você deseja saber a média?) Onde: Valor 1, Valor 2, ..., Valor N, são os valores que você deseja obter a média, podendo ser números ou referências de células. Veja o exemplo a seguir: Exemplo 1. Você pode utilizar a fórmula média para obter a média de vendas de uma empresa, como no exemplo à seguir. Vejamos: 1 - A média geral de cada informação. Média (AVERAGE) MÉDIA(C18:C29) Máximo & Mínimo Tabela de emissão de CO² (ton) País 2016 2017 2018 Brasil 101.76 90.42 96.772 EUA 104.526 91.736 105.296 China 93.428 93.014 100.408 Rússia 110.368 98.6022 100.62 Máxima emissão 110.368 Mínima emissão 90.42 Quadro de Desempenho Produto Vendas Devoluções Desemp. % Produto 1 879 150 82.94% Valor máximo em vendas Produto 2 758 200 73.61% Produto 3 811 53 93.46% Produto 4 975 92 90.56% Produto 5 704 96 86.36% Valor mínimo em devoluções Produto 6 544 127 76.65% Produto 7 777 85 89.06% Produto 8 758 11 98.55% Produto 9 597 105 82.41% Produto 10 859 147 82.89% MÁXIMO(C19:E22) MÍNIMO(C19:E22) "Qual é o valor máximo de receita da sua empresa? E qual é o seu custo mínimo?" As fórmulas máximo e mínimo retornam o valor máximo e o valor mínimo de um intervalo de dados selecionado. =Máximo(núm1 ; [núm2]... [núm n]) - Simplificando tudo: Máximo(Intervalo de dados) =Mínimo(núm1 ; [núm2]... [núm n]) - Simplificando tudo: Mínimo(Intervalo de dados) Exemplo 1. Como saber qual do seus produtos mais vende? Utilize as fórmulas máximo e mínimo para encontrar a maior quantidade de vendas e o menor número de devoluções entre os produtos. Máximo & Mínimo (MAX & MIN) Maior & Menor Produção de energia Ano 2016 2017 2018 Eólica 1988 9512 6281 Hidroelétrica 2964 6709 7628 Termoeletríca 1073 1345 2560 Solar 6223 3094 4583 Nuclear 5707 983 7659 Ranking 2016 2017 2018 1 6223 9512 7659 2 5707 6709 7628 3 2964 3094 6281 Quadro de Desempenho Produto Vendas Devoluções Desemp. % Maiores vendas Menores vendas Produto 1 879 150 82.94% 1 1 Produto 2 758 200 73.61% 2 2 Produto 3 811 53 93.46% 3 3 Produto 4 975 92 90.56% 4 4 Produto 5 704 96 86.36% 5 5 Produto 6 544 127 76.65% Produto 7 777 85 89.06% Produto 8 758 11 98.55% Produto 9 597 105 82.41% Produto 10 859 147 82.89% As fórmulas Maior e Menor retornam, respectivamente, o maior e menor valor de um intervalo de dados, tendo a possibilidade de "rankear" esses valores. =Maior (Matriz ; k) - Simplificando tudo: Maior(matriz de valores ; posição relativa k) =Menor (Matriz ; k) - Simplificando tudo: Menor(matriz de valores ; posição relativa k) Exemplo 1. Utilize as fórmulas Maior e Menor para "rankear" os valores de vendas. Maior & Menor (LARGE & SMALL) Ao lado podemos ver que, com a fórmula Maior, foi possível rankear os maiores valores de produção de energia de acordo com a posição K. O mesmo se aplica para encontrar os menores valores de produção. Cont.Valores - Cont.núm Lista 1 Brasil Chile Japão EUA Peru Alemanha China Africa do Sul Itália Rússia Índia Espanha Número de paises 12 Balança Receita Federal Placa Excesso KGs KUY 9910 Não - TRT 9087 Não - TNT 1000 Sim 670 GUU 6278 Não - TYW 1782 Sim 430 GDU 5437 Sim 700 Resposta: 3 #N/A Barra Mansa Número de viagens: Volta Redonda Rio de Janeiro São Paulo Jundiaí Santos Lavras Balança Receita Federal Placa Excesso KGs Número de caminhões: KUY 9910 Não - TRT 9087 Não - TNT 1000 Sim 670 GUU 6278 Sim 877 TYW 1782 Sim430 GDU 5437 Sim 700 Para saber quantas células tem preenchidas num determinado intervalo de dados, você pode usar a fórmula Cont.valores. =Cont.valores (valor1;[valor2];...) - Simplificando tudo: Cont.valores(Intervalo de dados que você deseja contar valores) Valor 1, 2 ou N, nada mais é do que a referência ou intervalo de células na qual você deseja contar valores. Não entendeu? Veja o exemplo abaixo: Quantos paises temos na lista abaixo? Podemos utilizar o Cont.valores para saber a resposta. Cont.valores & Cont.núm (COUNTA & COUNT) =CONT.VALORES(B19:D22) Quando queremos saber quantos números tem em um intervalor dados, podemos utilizar a fórmula Cont.núm. Veja o exemplo abaixo: =CONT.NÚM(D33:D38) =Cont.núm(valor1;[valor2];...) Simplificando tudo: Cont.núm(Intervalo de dados que você deseja contar números) Quantos caminhões de carga ultrapassaram o limite de peso estabeleceido pela Receita Federal? Cont.núm pode te dizer a resposta correta. Exemplo 1. Utilize a fórmula Cont.valores para contar quantos viagens o nosso Consultor de Vendas realizou no último mês. Você pode fazer isso contando as cidades que ele visitou. Exemplo 2. Como no exemplo do texto explicativo, utilize a fórmula Cont.núm para saber quantos caminhões de carga ultrapassaram o limite de peso estabeleceido pela Receita Federal? Concatenar Exemplo com texto Barra Mansa/RJ Exemplo com referência Barra MansaRJ Texto 1 Barra Mansa Texto 2 RJ Listagem de cidades Produto Vendas Resultado Barra Mansa RJ Mendes RJ Lavras MG São Roque SP Marília SP Sorocaba SP Salvador BA Santos SP Londrina PR Afinal, o que é concatenar? Utilizamos esta função quando é necessário unir dois ou mais textos. Existem duas possibilidades de utilizar a fórmula Concatenar. Veja: =Concatenar("texto1";"texto2";...) ou... ='texto1'&'texto2' Bem fácil, não é? Só precisamos estar atentos a dois detalhes: 1. Caso você queria unir dois textos (ex.: "Barra Mansa" com "RJ") a fórmula deve ficar desta maneira: ="Barra Mansa"&"RJ" - Como podemos ver, os dois textos devem estar entre aspas. 2. Se os textos já estiverem inseridos em uma célula, você pode usá-las referência. Segue o exemplo abaixo. Temos os textos Barra Mansa na célula C28 e RJ na C29, logo nosso fórmula deve ficar: =A1&A2 Exemplo 1. Deseja saber na prática? Então "Concatene" o nome da cidade com o estado de origem, separando os nomes com espaço. Concatenar (CONCATENATE) ="Barra Mansa"&"/RJ" =C28&C29 Subtotal Produção Defeitos Produção Máx. Produção Mín. Média Faturamento 1163 138 189 64 R$ 17,083.33 SUBTOTAL(9;D39:D47) SUBTOTAL(4;D39:D47) SUBTOTAL(1;G39:G47) Soma Máximo Média Supervisor Máquina Produção Defeitos Peças Boas Faturamento Gustavo Alpha 65 2 63 R$ 9,450.00 Márcia Beta 105 11 94 R$ 14,100.00 Márcia Sigma 155 8 147 R$ 22,050.00 Joana Beta 64 23 41 R$ 6,150.00 Gustavo Beta 156 33 123 R$ 18,450.00 Gustavo Alpha 134 3 131 R$ 19,650.00 Joana Alpha 189 2 187 R$ 28,050.00 Joana Sigma 140 34 106 R$ 15,900.00 Joana Alpha 155 22 133 R$ 19,950.00 Produção Defeitos Produção Máx. Produção Mín. Média Faturamento Supervisor Máquina Produção Defeitos Peças Boas Faturamento Gustavo Alpha 65 2 63 R$ 9,450.00 Márcia Beta 105 11 94 R$ 14,100.00 Márcia Sigma 155 8 147 R$ 22,050.00 Joana Beta 64 23 41 R$ 6,150.00 Gustavo Beta 156 33 123 R$ 18,450.00 Gustavo Alpha 134 3 131 R$ 19,650.00 Joana Alpha 189 2 187 R$ 28,050.00 Joana Sigma 140 34 106 R$ 15,900.00 Joana Alpha 155 22 133 R$ 19,950.00 A fórmula Subtotal realiza algumas operações básicas como: Soma, média, contagem, máximo e mínimo. A sua grande virtude é a possibilidade de não levar em consideração os valores filtrados. A função: =Subtotal(Núm_função;ref 1) - Simplificando tudo Subtotal(Qual função você deseja utilizar - número da função;Intervalo de dados) Como fazer? 1. O primeiro argumento (núm_função), ou seja, é nele que vamos dizer qual o tipo de funções vamos utilizar, conforme na imagem abaixo: 2. O nosso segundo parâmetro (Ref1) é o intervalo de dados na qual você deseja sabe o resultado. Veja o exemplo abaixo: Exemplo 1. Utilize a fórmula Subtotal para obter os seguintes resultados: Subtotal (SUBTOTAL) Você pode visualizar melhor o dinamismo dos seus resultados utilizando os filtros de Máquina ou Supervisor. Por exemplo: Inicialmente, os valores de produção e média de faturamento são: 1163 e R$17.083,33, respectivamente. Utilizando o filtro para o supervisor Gustavo, os valores de produção e média de faturamento vão para: 355 e R$15.850,00. Obs.: No quadro número da função, temos opções de 1 à 11, e uma reptição delas de 101 à 111. Afinal se são igual, qual é a diferença? A diferença entre elas é sobre quais os valore as funções levam em consideração. Nas opções de 1 à 11, somente os valores filtrados (retirada de valores utilizando o filtro automático) são excluídos do resultado. Já na opção de 101 à 111, além dos valores filtrados, a função não leva em consideração os valores ocultados manualmente.SE e suas variações Símbolos Significado Teste Lógico Resultado > Maior =82>10 TRUE >= Maior ou Igual =2>=5 FALSE < Menor =10<23 TRUE <= Menor ou Igual =5<=5 TRUE = Igual ="ITEX"="OI" FALSE <> Diferente ="ITEX"<>"OI" TRUE Estado Valor em Vendas Status Rio de Janeiro R$ 10,192.00 Não Ok São Paulo R$ 19,500.00 Não Ok Minas Gerais R$ 22,398.00 Ok Paraná R$ 25,733.00 Ok Bahia R$ 15,000.00 Não Ok Resultado A Resultado B Meta Atingiu? 215 189 170 Sim Resultado A Resultado B Meta Atingiu? 180 100 135 Não Resultado A Resultado B Meta Atingiu? 180 100 135 Sim Exportação Itex Cód. da Fábrica Destino Produto Partida (E) Partida (OU) 237 Tubo de Ferro 237 Tubo de Aço 354 Tubo de Ferro 237 Tubo de Ferro 237 Tubo de Aço 354 Tubo de Ferro 354 Tubo de Aço 354 Tubo de Aço 354 Tubo de Aço 237 Tubo de Ferro 354 Tubo de Ferro Como saber se um aluno foi aprovado ou reprovado com base em sua nota? Ou então, como saber se suas vendas atingiram ou não a meta estabelecida? A fórmula SE é a resposta! Como ela funciona? Você deve configurar o texto lógico e em seguida determinar quais serão as duas possibilidades de resposta... Se verdadeiro ou se for falso. A fórmula SE e seus argumentos: =SE(Teste_lógico ; [Valor_se_verdadeiro] ; [Valor_se_falso]) Simplificando tudo =Se(Inserir o teste lógico ; [Aqui é o valor se verdadeiro]; [Aqui é o valor se falso]) Estado Valor em Vendas Status Rio de Janeiro R$ 10.192,00 Não Ok São Paulo R$ 19.500,00 Não Ok Minas Teste Lógico: 22.398,00 Ok Paraná R$ 25.733,00 Ok Bahia R$ 15.000,00 Não Ok Antes de aplicarmos a função SE, devemos entender mais a fundo o que é um teste lógico e seus operadores. Nada melhor do que explicar na prática quando o Excel retorna VERDADEIRO ou FALSO, certo? Exemplo 1. Você é o gerente comercial da Itex Exportações, e deseja obter alguns resultados a partir de uma pequena base dados: 1.1 Destino: Sabendo que os códigos: 00237, para países da América do Norte. 00354, para países da Ásia. Elabore a fórmula de maneira que retorne os continentes de acordo com o código correspondente. 1.2 Partida (E) - A sua equipe elaborou um estudo e chegou na seguinte conclusão: Utilizando a função E, se o destino for países da América do Norte e o produto for Tubo de Ferro, é mais conveniente que os produtos da empresa sejam enviados pelo porto de Santos, caso contrário, o porto de Itaguaí é o mais viável financeiramente. 1.3 Partida (OU): Agora utilizando a função OU no lugar de E, veja como as respostas se comportam desta variação. SE e suas variações (IF) =SE(D45>20000;"Ok";"Não Ok") =SE(D48>20000;"Ok";"Não Ok") Como podemos perceber, o primeiro status está como "Não Ok", devido ao valor ser menor do que 20 mil. Já neste caso, podemos perceber que o valor é superior a 20 mil, sendo assim, a resposta selecionada de acordo com suas condições é "Ok". Fórmula "E": [Teste Lógico1; Teste Lógico2...] Verifica se todos os testes lógicos envolvidos estão sendo atendidos, retornando verdadeiro caso sim, ou, caso pelo menos um teste não seja atendido, retorna falso. Veja o exmplos abaixo: Fórmula "Ou": [Teste Lógico1; Teste Lógico2...] Verifica se pelo menos um dos testes lógicos envolvidos na célula é atendido, retornando verdadeiro caso sim, ou falso caso todos os testes não forem atendidos. Temos mais duas variações que podem ser importantes para fórmula SE. São elas: =SE(E(B68>D68;C68>D68);"Sim";"Não") =SE(E(B70>D53;C70>D70);"Sim";"Não") =SE(OU(B77>D77;C77>D77);"Sim";"Não") Agora vamos lá.. Lembre-se: É muito importante configurar todos os argumentos corretamente. Se liga no exemplo abaixo: Imagine que você quer avaliar os status de suas vendas de acordo com os seguintes critérios: 1. Caso o valor em vendas for maior do que 20 mil, configure o valor_se_verdadeiro como Ok, caso contrário, em valor_se_falso, digite a palavra Não Ok (todo texto dentro de uma fórmula deve ser escritro entre aspas). O mesmo poderia ser feito com números ou com referências de células. SE Composto Aluno N1 N2 Média Status Gustavo 6 5 5.5 Exame Final =IF(E20>=7;"Aprovado";IF(E20>=4;"Exame Final";"Reprovado")) Caio 7 7 7 Aprovado =IF(E21>=7;"Aprovado";IF(E21>=4;"Exame Final";"Reprovado")) Maria 10 7 8.5 Aprovado =IF(E22>=7;"Aprovado";IF(E22>=4;"Exame Final";"Reprovado")) Vendas Itex Região Vendendor Valor em Vendas Status Região 1 Maria Clara R$ 384,665.00 Região 1 Fernando R$ 214,000.00 Região 2 Pedro R$ 364,267.00 Região 1 Carla R$ 67,336.00 Região 2 Giovanni R$ 271,494.00 Região 2 Caio R$ 147,758.00 A fórmula SE nos fornece duas possibilidades de resposta, caso o valor seja verdadeiro, e por fim caso o valor seja falso. No entando, podemos encontrar casos na qual podemos precisar de três ou mais possibilidades de resposta. Sendo assim, há a real necessidade de se utilizar o tal do SE composto. Acompanhe o exemplo abaixo: Imagine um boletim escolar, na qual teremos três status para avaliação de aluno. Aprovado para média maior ou igual a 7, em exame final para nota maior ou igual a 4 e menor que 7, e por fim, a temida reprovação para média abaixo de 4. Veja como devemos proceder: Exemplo 1. Você precisa analisar o desempenho de vendas de cada representante de vendas. De acordo com o valor vendido, cada vendedor irá receber um status conforme as informações abaixo: Para vendas acima de 250.000,00 reais - Status: Ótimo Para vendas acima de 100.000,00 reais - Status: Regular Para vendas abaixo de 100.000,00 reais - Status: Ruim SE Composto (IF) Como podemos observar na fórmula, a primeira verificação é se a média do aluno (célula F9) é maior ou igual (>=) a 7, diante deste teste lógico, temos duas possibilidades de resposta, caso ele atenda o critério proposto, o alunoestará aprovado, caso contrário, vamos ter mais duas novas verificações adicionando uma nova função 'SE'. Nesta nova função, deverá ser feita uma outra verificação, neste teste lógico, a função vai verificar se a nota do aluno é maior ou igual a 4 (já estão sendo levando em consideração os valores menores do que 7). Caso a avaliação for positiva, ativa o valor se verdadeiro, caso contrário valor se falo será ativo. Procv Código Respostas C001 Reposta 001 C002 Reposta 002 C003 Reposta 003 C004 Reposta 004 C005 Reposta 005 Código Respostas C001 Reposta 001 Código: C002 Reposta 002 C001 C003 Reposta 003 Resposta: C004 Reposta 004 Reposta 001 C005 Reposta 005 Produto Setor Quantidade Código: Banana A 152 Beterraba Mamão A 75 Resposta: Beterraba B 187 187 Cenoura B 250 Tomate E 51 Market Place Itex Código Produto Valor Código Produto Preço (R$) CD-111 CD-101 Capacitor R$ 4.50 CD-102 Transistor R$ 3.50 CD-103 Placa Mãe R$ 349.00 CD-104 Led R$ 1.00 CD-105 Estabilizador R$ 2.50 CD-106 Diodo R$ 0.50 CD-107 Processador R$ 749.00 CD-108 Resistência R$ 1.50 CD-109 Protetor R$ 169.00 CD-110 Ram R$ 174.00 CD-111 Cooler R$ 179.00 CD-112 Bluetooth R$ 53.00 Dizem que PROCV é Excel Avançado... Discordamos totalmente! Categorizada como fórmula de busca, a fórmula Procv além de simples, pode te ajudar muito. De maneira geral, para utilizá-la, vamos precisar de um código (através dele que a pesquisa é feita) e uma matriz tabela (é aqui que vamos buscar o valor correspondente). A matriz tabela da fórmula deve atender algumas características: 1. A sua matriz tabela, deve ser uma matriz onde tenha as suas possibilidades de códigos e as possibilidades de respostas. 2. O seu valor procurado sempre deve estar na primeira coluna da sua matriz selecionada (o valor procurado deve estar sempre à esquerda das possibilidades de resposta). Exemplo 1. Imagine um banco de dados onde através do código você precisa retornar qual é o produto e seu preço. Procv (VLOOKUP) Na tabela ao lado (esquerda), temos cinco códigos diferentes, e ao lado temos valor correspondente a cada código, ou seja, suas possíveis respostas. Sua sintaxe é bem simples. Vejamos: Procv (Valor_Procurado ; Matriz_Tabela ; Núm_Ind_Col ; [Procurar_Intervalo]) Simplificando tudo: Valor_Procurado: Aqui deve estar o seu valor que será pesquisado, pois através dele que a função Procv irá buscar o valor correspondente (pode ser um valor digitado, ou uma referência de célula. Ex.: A1); Matriz_Tabela: Aqui é o lugar da sua matriz tabela (atendendo todas as suas características); Núm_Ind_Col: Em qual coluna está sua resposta? (lembrando que este argumento é um valor númerico); [Procurar_Intervalo]: Aproximado ou Exato? Aqui é onde você vai definir o tipo de correspondência, se for VERDADEIRO, ele vai buscar valores aproximados caso a função não encontrar o código na matriz tabela. Para correspondência FALSO, irá ocorre um "erro" se ele não encontrar o código desejado, pois o mesmo está buscando valores exatos. =PROCV(Valor que você deseja pesquisar; intervalo no qual você deseja pesquisar o valor; em qual coluna está sua resposta?; Correspondência Exata ou Correspondência Aproximada – indicado como 0/FALSO ou 1/VERDADEIRO). Veja os exemplos abaixo: =PROCV(F53;C52:D56;2;FALSO)) =PROCV(G60;C60:E64;3;FALSO)) Proch Código C001 C002 C003 C004 C005 Respostas Reposta 001 Reposta 002 Reposta 003 Reposta 004 Reposta 005 Código: C001 Resposta: Reposta 001 Nome Mário Roberta Gustavo Caroline Pedro Setor Financeiro TI Comercial TI Comercial Salário R$ 4,320.00 R$ 7,890.00 R$ 7,500.00 R$ 6,271.00 R$ 6,100.00 Nome Setor Salário Análogo ao Procv, a fórmula Proch localiza um valor na linha superior de uma tabela ou matriz de valores e retorna uma resposta na mesma coluna de uma linha especificada na tabela ou matriz. Você pode usar a fórmula quando seus valores de comparação estiverem localizados em uma linha superior de uma tabela de dados e você quiser observar um número específico de linhas mais abaixo. Exemplo 1. Descubra as informações de cada colaborador da Itex SA de acordo com seu nome. Proch (HLOOKUP) Sua sintaxe é bem simples. Vejamos: Proch ( Valor_Procurado ; Matriz_Tabela ; Núm_Ind_Lin ; [Procurar_Intervalo] ) Simplificando tudo: Valor_Procurado: Aqui deve estar o seu valor que será pesquisado, pois através dele que a função Procv irá buscar o valor correspondente (pode ser um valor digitado, ou uma referência de célula. Ex.: A1); Matriz_Tabela: Aqui é o lugar da sua matriz tabela (atendendo todas as suas características); Núm_Ind_Lin: Em qual linha está sua resposta? (lembrando que este argumento é um valor númerico); [Procurar_Intervalo]: Aproximado ou Exato? Aqui é onde você vai definir o tipo de correspondência, se for VERDADEIRO, ele vai buscar valores aproximados, caso o código não seja encontrando na matriz tabela, se for FALSO, irá ocorre um "erro" se ele não encontrar o código desejado; =PROCV(Valor que você deseja pesquisar; intervalo no qual você deseja pesquisar o valor; o número da linha no intervalo contendo o valor de retorno; Correspondência Exata ou Correspondência Aproximada – indicado como 0/FALSO ou 1/VERDADEIRO). Veja o exemplo abaixo: =PROCH(B42;C38:G39;2;FALSO)) Validação de Dados Fonte: HB20 Corolla Civic S10 C180 Tabela de Vendas - Itex Cursos Data Aluno Curso Quantidade Essencial Especialista VBADe modo geral, Validação de Dados restrigir os valores que poderão ser adicionados em uma célula, ou se preferir, selecionar um valor sem precisar digitar. Como configurar uma Validação de Dados? Bem simples, veja a seguir: Quando queremos limitar os valores de entrada em uma planilha. Seja para evitar erros de digitação ou valores indesejados (uma divisão por 0 por exemplo). Para adicionar uma validação de dados basta selecionar o intervalo desejado, localize a aba 'Dados', e por fim selecione a opção 'Validação de Dados'. Validação de Dados Aba 'Configurações' 1. Nesta aba você pode dizer que tipo de validação deseja permitir. Você pode permitir o que quiser, veja o exemplo abaixo: 2. Podemos dizer que, dentre todas as possibilidades de validação, o tipo mais usado é o formato de lista. Acompanhe o exemplo a seguir: 2.1. Selecione o intervalor que você deseja validar dados, em seguida na aba Dados, selecione a opção Validação de Dados. 2.2. Selecione o critério de validação, como por exemplo um lista, em seguida selecionar a sua fonte. Fonte: É a fonte de dados na qual você deseja permitir a sua entrada em uma célula qualquer. Números inteiros No exemplo abaixo, estamos utilizando validação para delimitar a inserção números inteiros, entre 0 e 10. Além do tipo de validação, você pode configurar uma mensagem de entrada, e um alerta de erro. Mensagem de entrada surge quando selecionamos uma célula que contenha qualquer tipo de validação de dados. Mensagem de alerta de erro surge quando inserimos uma mensagem com valor inapropriado. Para configurar uma mensagem de entrada você deve ir até 'Mensagem de entrada' e depois basta escrever um título e uma mensagem de entrada. Veja o exemplo a seguir: Para configurar um alerta de erro, você deve ir até 'Alerta de Erro' e configurar o estilo de alerta, um título e uma mensagem de erro. Veja o exemplo a seguir: Abaixo, um exemplo de alerta de erro, quando digitamos em uma célula algo fora das restrições de uma validação: Obs.: Dentre todas as possibilidades de validação, temos Lista como uma das mais usadas. No entando, para os demais critérios de validação, não há complexidade para configurar. Exemplo 1. Para treinar... Aqui você pode desenvolver validações de dados conforme as informções a serem preenchidas abaixo (Data, curso e quantidade) Formatação Condicional I Custo total Filial RJ SP MG ES A 2475 1616 2012 1862 B 1855 1833 1978 1650 C 2350 2148 1685 1668 Barra de Dados Escala de Cor 10 10 20 20 30 30 40 40 50 50 60 60 70 70 80 80 90 90 100 100 Financeiro Data Receita Despesa Saldo 1-Jan R$ 1,277.00 R$ 845.00 R$ 432.00 1-Jan R$ 956.00 R$ 523.00 R$ 433.00 1-Jan R$ 2,003.00 R$ 1,900.00 R$ 103.00 1-Jan R$ 518.00 R$ 250.00 R$ 268.00 1-Jan R$ 982.00 R$ 214.00 R$ 768.00 1-Jan R$ 2,008.00 R$ 597.00 R$ 1,411.00 2-Jan R$ 900.00 R$ 819.00 R$ 81.00 2-Jan R$ 2,055.00 R$ 491.00 R$ 1,564.00 3-Jan R$ 2,198.00 R$ 695.00 R$ 1,503.00 3-Jan R$ 1,255.00 R$ 1,253.00 R$ 2.00 3-Jan R$ 1,449.00 R$ 1,328.00 R$ 121.00 3-Jan R$ 950.00 R$ 317.00 R$ 633.00 4-Jan R$ 1,812.00 R$ 232.00 R$ 1,580.00 4-Jan R$ 1,333.00 R$ 866.00 R$ 467.00 4-Jan R$ 1,288.00 R$ 487.00 R$ 801.00 5-Jan R$ 2,986.00 R$ 1,138.00 R$ 1,848.00 5-Jan R$ 2,355.00 R$ 1,012.00 R$ 1,343.00 5-Jan R$ 2,730.00 R$ 859.00 R$ 1,871.00 5-Jan R$ 1,381.00 R$ 1,027.00 R$ 354.00 5-Jan R$ 2,369.00 R$ 1,223.00 R$ 1,146.00 5-Jan R$ 2,028.00 R$ 1,271.00 R$ 757.00 5-Jan R$ 1,782.00 R$ 819.00 R$ 963.00 5-Jan R$ 2,847.00 R$ 426.00 R$ 2,421.00 Já pensou em realçar valores de um intervalo de dados conforme uma característica definida? Pois bem, você pode obter esse recurso através de uma Formatação Condicional. Formatações permitem que o usuário configure um intervalo de dados para formatar condicionalmente. Como podemos fazer isso? Basta selecionar o intervalo desejado > 'Página inicial' > 'Formatação Condicional' e escolher a formatação que melhor se aplique a situação. Formatação Condicional I 1. Incialmente vamos trabalhar com dois grupos de formatação; Realçar Regras das Células e Regras de Primeiros/Últimos. 2. De modo geral, os dois primeiros grupos (citados acima) são bem intuitivos, ou seja, para formatar valores maiores do que ou menores do que... O procedimento é o mesmo. Você pode observar isso nas imagens abaixo: 3. Para formatar você só precisa selecionar o tipo de formatação que mais se adapta a sua necessidade. Por exemplo, vamos formatar na tabela ao lado todos os valores maiores do que R$2.000,00 reais. 3.1. Apos selecionar o intervalo (Ex.: tabela: Custo total) e segui o procedimento: Formatação condicional > realçar regras das células > maiores do que... Você também pode escolher o estilo da formatação na caixa de combinação ao lado. Formatação configurada? É só clicar em Ok. Para formatações de Barra de Dados ou Escala de cor: Nesses estilos, a formatação se adequa aos seus valores selecionados, ou seja, o preenchimento será formatado gradativamente na escala de cor, e proporcionalmente no estilo barra de dados. Veja no exemplo abaixo: Exemplo 1. Crie algumas formatações condicionais de acordo com os itens específicados abaixo: 1.1 - Formate condicionalmente (preenchimento verde) para todos os valores acima de R$2.000,00 reais na coluna de Receitas; 1.2 - Na coluna Despesas, crie uma formatação condicional (preenchimento vermelho) para todos os valores maiores que R$1.200,00 reais; Remover Duplicatas Lista 1 Lista 2 Brasil 452 China 541 China 569 Brasil 369 China 300 Chile 104 Chile 428 Brasil 353 Chile 207 Portugal 609 Lista 1 Brasil China Chile Portugal Tabela de Vendas Lista Data Carros Valor Tipo 1-Jan Celta R$ 19,664.00 Financiado 2-Jan Gol R$ 22,764.00 À Vista 3-Jan Vectra R$ 24,558.00 À Vista 4-Jan Gol R$ 15,034.00 Financiado 4-Jan Gol R$ 16,363.00 Financiado 4-Jan Celta R$ 21,565.00 Financiado 4-Jan Vectra R$ 14,383.00 Financiado 4-Jan Gol R$ 17,467.00 À Vista 10-Jan Corolla R$ 13,438.00 À Vista 11-Jan Corolla R$ 13,923.00 À Vista 12-Jan Celta R$ 24,864.00 Financiado 12-Jan Celta R$ 20,225.00 FinanciadoSabe quando você precisa obter uma lista com valores únicos? Você pode Remover Duplicatas para resolver o seu problema. Como fazer? Na aba 'Dados' > Localize o grupo 'Ferramentas de Dados' > 'Remover Duplicatas'. Remover Duplicatas Veja como podemos trabalhar com este recurso: 1. O primeiro passo é selecionar o intervalo na qual você deseja valores exclusivos; 2. Localize a opção Remover Duplicatas; 3. Após os dois primeiros procedimentos, a seguinte caixa de diálogo irá surgir: Em 'Colunas' estará a localização do intervalo estará selecionado (você pode selecionar mais de uma coluna). No exemplo acima, vamos remover valores duplicatos apenas da Lista 1. Embora a seleção seja dupla (lista 1 e 2), os valores na qual vamos remover duplicatas então apenas na coluna Lista 1. De acordo com o exemplo acima, 6 valores duplicados encontrados e removidos. Restaram apenas 4 valores exclusivos. Muito fácil, não é? Exemplo 1. Para treinar... Você pode criar uma lista de carros com valores exclusivos conforme indicado no exemplo abaixo: Agrupar Dados Tabela 1 Cidade Hospitais Escolas Barra Mansa 48 54 Porto Real 20 77 Resende 82 72 Macaé 62 73 Total RJ 212 276 Santos 71 14 Campinas 82 35 Barueri 11 51 Taubaté 21 10 Total SP 185 110 Vitória 59 68 Guarapari 68 70 Total ES 127 138 Lavras 75 78 Uberaba 26 26 Total MG 101 104 Tabela 2 Cidade Hospitais Escolas Barra Mansa 48 54 Porto Real 20 77 Resende 82 72 Macaé 62 73 Total RJ 212 276 Santos 71 14 Campinas 82 35 Barueri 11 51 Taubaté 21 10 Total SP 185 110 Vitória 59 68 Guarapari 68 70 Total ES 127 138 Lavras 75 78 Uberaba 26 26 Total MG 101 104 Cidade Hospitais Escolas Barra Mansa 48 54 Porto Real 20 77 Resende 82 72 Macaé 62 73 Total RJ 212 276 Santos 71 14 Campinas 82 35 Barueri 11 51 Taubaté 21 10 Total SP 185 110 Vitória 59 68 Guarapari 68 70 Total ES 127 138 Lavras 75 78 Uberaba 26 26 Total MG 101 104 Cidade Hospitais Escolas Barra Mansa 48 54 Porto Real 20 77 Resende 82 72 Macaé 62 73 Total RJ 212 276 Santos 71 14 Campinas 82 35 Barueri 11 51 Taubaté 21 10 Total SP 185 110 Vitória 59 68 Guarapari 68 70 Total ES 127 138 Lavras 75 78 Uberaba 26 26 Total MG 101 104 Agrupar dados... Uma maneira visualmente mais bonita de ocultar ou exibir dados. Se você tem uma lista de dados e deseja resumi-la, a ferramenta agrupar pode te ajudar... Como pode ser feito? Na aba 'Dados' e depois localize a opção 'Agrupar', em seguida o Excel vai te sugerir duas opções: Agrupar em linhas ou em colunas. Basta identificar a orientação do seus valores e agrupar. 1. O primeiro passo é verificar se suas informações estão organizadas. É importante que todos os dados estajam categorizados. Veja a tabela abaixo (Tabela 1) todas as cidades estão categorizadas de acordo com seu estado de origem. 2. Verifique se sua tabela tem uma linha de resultado ou subtotal. Caso não tenha, basta inserir uma linha abaixo de cada grupo de dados. Para criar uma estrutura de tópico é bem fácil: 3. Selecione todas as linhas com os valores que você deseja agrupar. 3.1. Na guia 'Dados' > grupo 'Estrutura de Tópicos' > 'Agrupar'. Em seguida uma caixa de diálogo irá sugerirm, selecione uma das duas opções: Agrupar em linhas ou em colunas. (Selecione uma opção e seja feliz!) 3.2. Você pode repetir o procedimento até agrupar todos os dados. 3.3. Se desejar desagrupar linhas ou colunas, selecione o intervalo e vá até a guia 'Dados' > grupo 'Estrutura de Tópicos' > 'Desagrupar'. Agrupar Dados Para poder ocultar ou exibir os valores, basta clicar nos botões que foram criados para o agrupamento - Sinal de mais ( + ) e menos ( - ). Exemplo 1. No exemplo abaixo, nós temos uma tabela referente ao número de escolas e hospitais em cada cidade. Você pode criar uma estrutura de tópicos e agrupar os dados das cidades de acordo com os estados. Congelar Painéis Você pode manter uma área visível enquanto se move para outra área da planilha. Na guia Exibir, você pode Congelar Painéis. Por exemplo, se a primeira linha da planilha inclui cabeçalhos, você pode congelá-la para garantir que os cabeçalhos das colunas fiquem visíveis, enquanto você move a planilha para baixo. Normalmente, congelar painéis, é usado em tabelas ou bases de dados muito extensas. Como fazer? Para congelar painéis, siga o procedimento: 1. Para bloquear somente uma linha, na aba 'Exibir' > Congelar Painéis > Congelar Linha Superior. 2. Para bloquear somente uma coluna > Congelar Primeira Coluna. 3. Para bloquear mais de uma linha ou coluna, ou bloquear tanto linhas quanto colunas ao mesmo tempo, selecione a opção Congelar Painéis > Congelar Painéis. Você deve posicionar sua seleção abaixo das linhas e à direita das colunas que deseja congelar. 4. Para congelar várias linhas/colunas (começando com a linha 1), selecione a linha/coluna abaixo/lado da última linha/coluna que pretende congelar e em seguida Congelar Painéis > Congelar Painéis. Congelar Painéis Exemplo 1. Para treinar... Neste exemplo, você pode utilizar o recurso para congelar o título desta aba, conforme imagem abaixo: Somases Fábrica Setor Gastos Somases 1 451184 F001 Financeiro 218266 F001 Produção 270447 F001 Produção 180737 F002 Produção 297917 Fábrica: F001 F001 Financeiro 218400 F002 Manutenção 187054 Setor Produção F001 Manutenção 188834 F002 Produção 177763 F002 Financeiro 227181 F002 Produção 177763 Somases 2 451184 Destino Produto Qntd Exp Destino Áustria Milho 193 ton Portugal Portugal Soja 202 ton Bahamas Milho 200 ton Produto EUA Soja 189 ton Áustria Soja 181 ton Portugal Soja 139 ton Somases EUA Milho 211 ton Bahamas Soja 129 tonPodemos dizer que é uma das fórmulas mais utilizadas do Excel. Sua finalidade é extrair informações precisas e condicionadas de grandes bases de dados. O grande trunfo da Somases é a possibilidade de tornar a informação flexível com o uso de validação de dados como parâmetro. Aqui está como podemos inserir esta função 1. O primeiro parâmetro deve ser sempre o intervalo que vamos somar - Sempre um intervalo numérico. 2. Já o segundo parâmetro deve ser um intervalo que contém os seus critérios. 3. O terceiro parâmetro deve ser o critério da soma - Geralmente é uma palavra ou uma célula única. 4. Para casos com mais de um critério, o procedimento é o mesmo. Intervalo de critérios e depois o seu critério, e assim sucessivamente. =Somases( 'Intervalo de Soma' ; ' Primeiro Intervalo de Critério' ; 'Critério' ; ... , 'Intervalo de critério N', 'critério N' ) Dica: O terceiro argumento (critério1) deve ser sempre uma expressão lógica, exemplo: "Fábrica 1" ou então ">100", sempre entre aspas. Uma outra maneira de representar um critério seria através de uma referência de célula. Simplificando tudo: =Somases ("O que eu quero somar" ; "Quando esses valores forem..." ; "Iguais a essa expressão") O intervalo de critério é um "compilado" de critérios, ou seja, se seu critério é um modelo de carro, seu intervalo só pode ser uma matriz onde está todos os carros. Lembrando que os dois últimos parâmetros podem ser estendidos até 45 vezes. 'Intervalo de Critérios1' ; 'Critério1' ; 'Intervalo de Critérios2' ; 'Critério2'... 'Intervalo de Critérios N' ; 'Critério N' Veja o exemplo abaixo: =SOMASES(D48:D57;B48:B57;"F001";C48:C57;"Produção") =SOMASES(D48:D57;B48:B57;G51;C48:C57;G53) Exemplo 1. Você está em um novo desafio! Acabou de ser promovido a responsável geral de exportação de produtos agrículas da Agrotex. Sua primeira tarefa é ter o controle da quantidade exportada de acordo com o país e o produto. Somases (SUMIFS) Analise os gastos de acordo com a fábrica e o setor selecionado. Repare que é a mesma função de cima, só que, ao invés de escrevermos "F001" e "Produção", referenciamos às células, G49 e G51. Assim, ao mudar o setor ou a fábrica, automaticamente é recalculado a soma desses novos critérios selecionados. Você pode utilizar estas duas células para fazer sua fórmula. Cont.ses Ano Vendedor Vendas Vendedor: Gustavo 2010 Gustavo 337976 2010 Vanessa 340000 Meta: 300000 2010 Maria 190000 2011 Gustavo 298000 Cont.ses 2 2011 Vanessa 304732 2011 Maria 110067 2012 Gustavo 449857 2012 Vanessa 313560 2012 Maria 306672 2013 Gustavo 245588 2013 Vanessa 286512 2013 Maria 199114 Ano Vendedor Vendas Vendedor: Vanessa 2010 Gustavo 337976 2010 Vanessa 340000 Cont.ses 4 2010 Maria 190000 2011 Gustavo 298000 2011 Vanessa 304732 2011 Maria 110067 2012 Gustavo 449857 2012 Vanessa 313560 2012 Maria 306672 2013 Gustavo 245588 2013 Vanessa 286512 2013 Maria 199114 Cliente Cidade Duração do Projeto (dias) Cliente Empresa 10 Volta Redonda 54 Empresa 7 Barra Mansa 89 Empresa 2 Rio de Janeiro 60 Cidade Empresa 10 Volta Redonda 46 Empresa 7 Barra Mansa 70 Empresa 2 Rio de Janeiro 72 Empresa 7 Volta Redonda 58 Cont.ses Empresa 7 Barra Mansa 56 Empresa 10 Rio de Janeiro 55 Empresa 7 Volta Redonda 64 Empresa 7 Barra Mansa 66 Empresa 10 Rio de Janeiro 88 Cont.ses (COUNTIFS) Quer saber quantas vezes você utilizou a máquina A para produzir? Bom, a fórmula Cont.ses faz exatamente o que você precisa. Selecione o intervalo e os critério na qual serão condições para o contador. Sua sintaxe é a seguinte: Cont.ses( 'Intervalo_critérios1' ; 'Critérios1' ; ['Intervalo_critérios2' ; 'Critérios2']… ) Simplifcando tudo: Cont.ses("Contar quando esses valores forem..." ; "Igual a esta expressão" ; ["Contar quando esses valores forem_2..."] ; ["Igual a esta expressão_2"] ; ...) Vale lembrar: 1. A partir do terceiro parâmetro o procedimento é o mesmo; 2. O critério pode ser uma célula (Ex.: Critério1 = célula A2), ou um texto; 3. O seu critério pode ser um valor exato (conte tudo que for igual à "10"), ou um condição com operadores lógicos, como por exemplo, conte tudo que for maior que 10 (">10"). Veja o exemplo abaixo: Exemplo 1. O setor de Engenharia da Itex precisa de alguns resultado para elaboração de um cronograma ideal de projetos. Precisamos de alguns resultados a partir da tabela abaixo Projetos com mais de 70 dias de duração de acordo com cada cliente e cidade. =CONT.SES(C52:C63;"Vanessa") =CONT.SES(C52:C63;G51) Ou utilizando referência de células: image38.png image37.png image2.png image1.png image39.png image40.png image3.png image7.png image6.png image5.png image4.png image8.png image10.png image9.png image11.png image12.png image13.png image15.png image14.png image16.png image17.png image42.png image18.png image43.png image41.png image24.png image20.png image23.png image21.png image19.png image22.png image27.png image45.png image44.png image26.png image25.png image28.png image29.png image30.png image31.png image32.png image46.png image33.png image35.png image34.png image36.png image47.png image48.png
Compartilhar