Baixe o app para aproveitar ainda mais
Prévia do material em texto
Menu Menu - Lista de Exercícios Excel - Do Básico ao Avançado Formatação /xl/drawings/drawing1.xml#Formata%C3%A7%C3%A3o!A1Operações Básicas /xl/drawings/drawing1.xml#'Opera%C3%A7%C3%B5es%20B%C3%A1sicas'!A1Colar Especial /xl/drawings/drawing1.xml#'Colar%20Especial'!A1Autopreenchimento /xl/drawings/drawing1.xml#Autopreenchimento!A1Trancamento /xl/drawings/drawing1.xml#Trancamento!A1Funções E e OU /xl/drawings/drawing1.xml#'Fun%C3%A7%C3%B5es%20E%20e%20OU'!A1Formatação Condicional /xl/drawings/drawing1.xml#'Formata%C3%A7%C3%A3o%20Condicional'!A1Função SE /xl/drawings/drawing1.xml#'Fun%C3%A7%C3%A3o%20SE'!A1Filtragem /xl/drawings/drawing1.xml#Filtragem!A1Classificação /xl/drawings/drawing1.xml#Classifica%C3%A7%C3%A3o!A1Gráficos /xl/drawings/drawing1.xml#Gr%C3%A1ficos!A1Validação de Dados /xl/drawings/drawing1.xml#'Valida%C3%A7%C3%A3o%20de%20Dados'!A1Filtro Avançado /xl/drawings/drawing1.xml#'Filtro%20Avan%C3%A7ado'!A1Classificação Avançada /xl/drawings/drawing1.xml#'Classifica%C3%A7%C3%A3o%20Avan%C3%A7ada'!A1CONT.SE /xl/drawings/drawing1.xml#CONT.SE!A1SOMASE e MÉDIASE /xl/drawings/drawing1.xml#'SOMASE%20E%20M%C3%89DIASE'!A1CONT.SES e MÉDIASES /xl/drawings/drawing1.xml#'CONT.SES%20e%20M%C3%89DIASES'!A1PROCV e PROCH /xl/drawings/drawing1.xml#'PROCV%20e%20PROCH'!A1Tabela Dinâmica /xl/drawings/drawing1.xml#'Tabela%20Din%C3%A2mica'!A1Gráfico Dinâmico /xl/drawings/drawing1.xml#'Gr%C3%A1fico%20Din%C3%A2mico'!A1Menu /xl/drawings/drawing1.xml#Menu!A1 Formatação Formatação O aluno deverá formatar as células de acordo com o modelo para verificar se aprendeu todas as formas de formatação das células Modelo para visualização Atividade para o Aluno Formatação de Célula Formatação de Célula 1 Preenchimento 1 Preenchimento 2 Cor do Texto Vermelho Azul Roxo 2 Cor do Texto Vermelho Azul Roxo 3 Bordas Completa Superior Inferior 3 Bordas Completa Superior Inferior 4 Negrito Teste 4 Negrito Teste 5 Itálico Teste 5 Itálico Teste 6 Sublinhado Teste 6 Sublinhado Teste Menu /xl/drawings/drawing2.xml#Menu!A1 Operações Básicas Operações Básicas As primeiras operações serão feitas escrevendo os números e em seguida o aluno poderá utilizar as referências das células para efetuar novamente as operações Modelo para visualização Atividade para o Aluno Adição + Multiplicação * Adição + Multiplicação * Valor 1 Valor 2 Resultado Valor 1 Valor 2 Resultado Valor 1 Valor 2 Sem Referência Com Referência Valor 1 Valor 2 Sem Referência Com Referência 5 12 17 5 12 60 5 12 5 12 7 25 32 7 25 175 7 25 7 25 15 17 32 15 17 255 15 17 15 17 23 35 58 23 35 805 23 35 23 35 25 8 33 25 8 200 25 8 25 8 Subtração - Divisão / Subtração - Divisão / Valor 1 Valor 2 Resultado Valor 1 Valor 2 Resultado Valor 1 Valor 2 Sem Referência Com Referência Valor 1 Valor 2 Sem Referência Com Referência 5 12 -7 5 12 0.42 5 12 5 12 7 25 -18 7 25 0.28 7 25 7 25 15 17 -2 15 17 0.88 15 17 15 17 23 35 -12 23 35 0.66 23 35 23 35 25 8 17 25 8 3.13 25 8 25 8 As operações sem referências são as operações em que o aluno escreve o número utilizado para cada operação, enquanto as operações com referênciao aluno irá selecionar as células que contém os números para efetuar as operações. Por exemplo: L12 + M12 isso quer dizer que o Excel irá pegar o valor contido na célula L12 e irá somar com o valor contido na célula M12. Existem duas formas de colocar a referência: O aluno pode escrever manualmente a célula ou pode simplesmente clicar na célula desejada enquanto escreve a fórmula. Menu /xl/drawings/drawing3.xml#Menu!A1 Colar Especial Colar Especial Serão demonstradas as opções de Colar Especial e em seguida o aluno poderá utilizá-las para que fiquem igual ao modelo Modelo para visualização Células a serem copiadas R$ 19.99 15% Atividade para o Aluno Colar Colar Colar R$ 19.99 15% Colar Fórmulas 19.99 0.15 Fórmulas Fórmulas e Formatação R$ 19.99 15% Fórmulas e Formatação Manter Formatação Original R$ 19.99 15% Manter Formatação Original Sem Bordas R$ 19.99 15% Sem Bordas Manter Largura da Coluna Original R$ 19.99 15% Manter Largura da Coluna Original Transpor R$ 19.99 Transpor 15% Colar Valores Colar Valores Valores 19.99 0.15 Valores Valores e Formatação de Número R$ 19.99 15% Valores e Formatação de Número Valores e Formatação Original R$ 19.99 15% Valores e Formatação Original Outras Opções para Colar Outras Opções para Colar Formatação Formatação Colar Vínculo R$ 19.99 R$ 0.15 Colar Vínculo Imagem Imagem Imagem Vinculada Imagem Vinculada Menu /xl/drawings/drawing4.xml#Menu!A1 Autopreenchimento Autopreenchimento Serão dados alguns exemplos para que o aluno possa utilizar o método de autopreenchimento, selecionando algumas células e arrastando para completar as sequências. As células destacadas são as células que o aluno deverá selecionar antes de prosseguir com o autopreenchimento, ou seja, deve selecionar as células marcadas antes de arrastar para completar a tarefa. Modelo para visualização Atividade para o Aluno Janeiro Domingo 1 5 Teste 1 João Janeiro Domingo 1 5 Teste 1 João Fevereiro Segunda-feira 2 10 Teste 2 Pedro 2 10 Teste 2 Pedro Março Terça-feira 3 15 Teste 3 Mariana 3 15 Teste 3 Mariana Abril Quarta-feira 4 20 Teste 4 Letícia Letícia Maio Quinta-feira 5 25 Teste 5 João Junho Sexta-feira 6 30 Teste 6 Pedro Julho Sábado 7 35 Teste 7 Mariana Agosto Domingo 8 40 Teste 8 Letícia Setembro Segunda-feira 9 45 Teste 9 João Outubro Terça-feira 10 50 Teste 10 Pedro Novembro Quarta-feira 11 55 Teste 11 Mariana Dezembro Quinta-feira 12 60 Teste 12 Letícia Ao efetuar o autopreenchimento as células abaixo também ficarão da mesma cor, ou seja, o autopreenchimento também traz a formatação das células. Menu /xl/drawings/drawing5.xml#Menu!A1 Trancamento Trancamento Será mostrado um exemplo que contém alguns valores para evidenciar o que acontece quando fazemos o trancamento e arrastamos as células trancadas. Modelo para visualização Atividade para o Aluno Sem trancamento Trancamento da Coluna J10 Exemplo de células Sem trancamento Trancamento da Coluna J11 10 100 1000 10 10 10 10 100 1000 20 20 20 30 30 30 Trancamento da Linha J10 Trancamento de Coluna e Linha J10 Trancamento da Linha J11 Trancamento de Coluna e Linha J11 10 100 1000 10 10 10 10 10 10 10 Selecionando a primeira célula de cada um dos exemplos é possível observar como foi feito o trancamento de cada uma das partes, o aluno também pode utilizar o atalho F4 várias vezes até chegar no trancamento desejado O aluno irá igualar a primeira célula de cada uma das opções a célula em vermelho apenas alterando o trancamento e em seguida arrastando a célula para direita e para baixo para observar o que acontece com as células com cada um dos trancamentos. Menu /xl/drawings/drawing6.xml#Menu!A1 Funções E e OU Funções E e OU Serão mostrados exemplos para que o aluno crie as fórmulas utilizando as funções "E" e "OU" para que tenham o mesmo resultado apresentado. Esse exercício também é importante para que o aluno possa aprender a interpretar a maneira que está sendo pedido para que possa transcrever isso em fórmula. Modelo para visualização Atividade para o Aluno Condições: A > 0 e B < 0 Condições: A > 0 ou B < 0 Condições: A > 0 e B < 0 Condições: A > 0 ou B < 0 A B Resultado A B Resultado A B Resultado A B Resultado10 -3 TRUE 10 -3 TRUE 10 -3 10 -3 5 7 FALSE 5 7 TRUE 5 7 5 7 -4 -9 FALSE -4 -9 TRUE -4 -9 -4 -9 -2 8 FALSE -2 8 FALSE -2 8 -2 8 Condições: (A > 0 e B < 0) ou B < -7 Condições: (A > 0 ou B < 0) E A > 7 Condições: (A > 0 e B < 0) ou B < -7 Condições: (A > 0 ou B < 0) E A > 7 A B Resultado A B Resultado A B Resultado A B Resultado 10 -3 TRUE 10 -3 TRUE 10 -3 10 -3 5 7 FALSE 5 7 FALSE 5 7 5 7 -4 -9 TRUE -4 -9 FALSE -4 -9 -4 -9 -2 8 FALSE -2 8 FALSE -2 8 -2 8 Menu /xl/drawings/drawing7.xml#Menu!A1 Formatação Condicional Formatação Condicional Serão colocados alguns exemplos para que o aluno possa entender o funcionamento da formatação condicional. É importante ressaltar que para que a formatação funcione a análise deverá ser VERDADEIRA, portanto se o aluno precisa de mais de uma formatação será necessária fazer outra análise para que esta também tenha resultado VERDADEIRO. Modelo para visualização Atividade para o Aluno 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4 5 5 5 5 5 5 5 5 5 5 5 5 6 6 6 6 6 6 6 6 6 6 6 6 7 7 7 7 7 7 7 7 7 7 7 7 8 8 8 8 8 8 8 8 8 8 8 8 9 9 9 9 9 9 9 9 9 9 9 9 10 10 10 10 10 10 10 10 10 10 10 10 São 7 exemplos utilizando as opções da Formatação Condicional, para cada uma delas o aluno deverá selecionar toda a coluna para que a formatação se aplique a todo o intervalo. 1 - Realçar Regras das Células - Está entre... - 3 e 7 2 - Regras de Primeiros/Últimos - Acima da Média... 3 - Barra de Dados - Preenchimento Gradual 4 - Escala de Cor - Vermelha - Amarela - Verde 5 - Conjunto de Ícones - Classificação - 3 Estrelas 6 - Nova Regra... - Usar uma fórmula para determinar quais células devem ser formatadas - Fórmula =R9>3 - Preenchimento da célula Azul Menu /xl/drawings/drawing8.xml#Menu!A1 Função SE Função SE Serão colocados exemplos da função SE comum e da função SE composta ,ou seja, uma dentro da outra para que o aluno possa ter mais de uma opção para os critérios. Modelo para visualização Atividade para o Aluno Exemplo 1 Exemplo 1 Vendedor Venda Bônus Venda Bônus O primeiro exemplo é utilizando a função SE comum, com o resultado verdadeiro e falso, enquanto o segundo exemplo utiliza a função SE em cadeia, ou seja, no resultado falso de uma função SE é colocada outra função SE. Os valores para os bônus estão nas tabelas destacadas. Os valores de bônus devem ser trancados se forem utilizadas as células para indicar o valor do bônus. Para facilitar o aluno poderá observar as fórmulas já feitas que também estão destacadas. Vendedor Venda Bônus Venda Bônus João R$50.00 R$50.00 0 a 99 R$50.00 João R$50.00 0 a 99 R$50.00 Pedro R$150.00 R$100.00 Acima de 100 R$100.00 Pedro R$150.00 Acima de 100 R$100.00 Mariana R$75.00 R$50.00 Mariana R$75.00 Helena R$125.00 R$100.00 Helena R$125.00 Lucas R$100.00 R$100.00 Lucas R$100.00 Exemplo 2 Exemplo 2 Vendedor Venda Bônus Venda Bônus Vendedor Venda Bônus Venda Bônus Matheus R$75.00 R$50.00 0 a 99 R$50.00 Matheus R$75.00 0 a 99 R$50.00 Maria R$125.00 R$100.00 100 a 199 R$100.00 Maria R$125.00 100 a 199 R$100.00 Letícia R$250.00 R$150.00 200 a 299 R$150.00 Letícia R$250.00 200 a 299 R$150.00 Marcos R$375.00 R$200.00 Acima de 300 R$200.00 Marcos R$375.00 Acima de 300 R$200.00 Paulo R$150.00 R$100.00 Paulo R$150.00 Ana R$275.00 R$150.00 Ana R$275.00 Menu /xl/drawings/drawing9.xml#Menu!A1 Filtragem Filtragem A filtragem de dados pode ser feita de duas maneiras, colocando o filtro ou formatando os dados como tabela (o que gera um filtro automático). Depois basta filtrar de acordo com a necessidade. Nome Idade Sexo Altura (m) Peso (Kg) João 45 Masculino 1.70 75 Pedro 37 Masculino 1.83 80 Mariana 25 Feminino 1.67 66 Letícia 18 Feminino 1.72 69 Helena 22 Feminino 1.65 65 Marcos 28 Masculino 1.77 72 Felipe 31 Masculino 1.71 77 Ana 27 Feminino 1.66 64 Clicando nas setas de cada coluna é possível fazer a filtragem de acordo com os dados contidos na coluna, se forem texto teremos filtros de texto, se for número teremos filtros de número. Então podemos filtragem em ordem alfabética, em ordem crescente ou decrescente. Podemos filtrar apenas com textos ou números específicos, como no caso do sexo, podemos marcar apenas masculino ou feminino. Tudo isso irá facilitar a análise dos dados. O aluno pode fazer a filtragem de cada uma das colunas para testar cada tipo de filtro disponível e observar como a tabela vai se modificando para adaptar a cada filtragem feita pelo aluno. É importante observar que desta forma os dados de uma mesma linha irão permanecer juntos e não serão bagunçados durante o processo. Menu /xl/drawings/drawing10.xml#Menu!A1 Classificação Classificação A classificação é muito parecida com a filtragem, no entanto pode ser feita de uma forma mais rápida. Modelo para visualização Tabela Exemplo Atividade para o Aluno Tabela com a classificação Nome Idade Sexo Altura (m) Peso (Kg) Nome Idade Sexo Altura (m) Peso (Kg) Nome Idade Sexo Altura (m) Peso (Kg) Letícia 18 Feminino 1.72 69 João 45 Masculino 1.70 75 Letícia 18 Feminino 1.72 69 Mariana 25 Feminino 1.67 66 Pedro 37 Masculino 1.83 80 Mariana 25 Feminino 1.67 66 Ana 27 Feminino 1.66 64 Mariana 25 Feminino 1.67 66 Ana 27 Feminino 1.66 64 Helena 22 Feminino 1.65 65 Letícia 18 Feminino 1.72 69 Helena 22 Feminino 1.65 65 Pedro 37 Masculino 1.83 80 Helena 22 Feminino 1.65 65 Pedro 37 Masculino 1.83 80 Marcos 28 Masculino 1.77 72 Marcos 28 Masculino 1.77 72 Marcos 28 Masculino 1.77 72 Felipe 31 Masculino 1.71 77 Felipe 31 Masculino 1.71 77 Felipe 31 Masculino 1.71 77 João 45 Masculino 1.70 75 Ana 27 Feminino 1.66 64 João 45 Masculino 1.70 75 Enquanto a filtragem temos que ir de um em um para filtrar o que queremos, na classificação podemos fazer todo o processo dentro de uma única janela e escolher a ordem que será feita a classificação. A tabela central mostra os dados originais e o modelo para visualização mostra como ficou a tabela após a classificação. Podemos observar que iremos classificar as alturas dentro da primeira classificação, ou seja, primeiro foi feita a classificação do sexo e essa ordem não irá mais mudar, o que irá mudar é a ordem das alturas das pessoas para se adaptarem a segunda classificação que é de ordem crescente de altura. Obs: O aluno pode moficar os dados colocando mais dados repetidos e alterar o tipo e a ordem das classificações para que possa entender como realmente funciona essa ferramenta e como ela pode facilitar a visualização dos dados. A classificação será feita da seguinte forma: 1 - Sexo em ordem alfabética; 2 - Altura do maior para o menor. Menu /xl/drawings/drawing11.xml#Menu!A1 Gráficos Gráficos Essa parte consiste em um tabela preenchida e três gráficos de exemplo. Meses Valor Janeiro R$ 1,958.00 Fevereiro R$ 2,146.00 Março R$ 2,732.00 Abril R$ 2,797.00 Maio R$ 648.00 Junho R$ 1,591.00 Julho R$ 897.00 Agosto R$ 1,994.00 Setembro R$ 1,449.00 Outubro R$ 1,852.00 Novembro R$ 1,777.00 Dezembro R$ 1,579.00 Obs: O aluno pode alterar os valores no gráfico para ver como se comportam e pode deletar os gráficos para criar seus próprios gráficos. Menu /xl/drawings/drawing12.xml#Menu!A1Gráfico de Barras Valor Janeiro Fevereiro Março Abril Maio Junho Julho Agosto Setembro Outubro Novembro Dezembro 1958 2146 2732 2797 648 1591 897 1994 1449 1852 1777 1579 Gráfico de Linha Valor Janeiro Fevereiro Março Abril Maio Junho Julho Agosto SetembroOutubro Novembro Dezembro 1958 2146 2732 2797 648 1591 897 1994 1449 1852 1777 1579 Valor Janeiro Fevereiro Março Abril Maio Junho Julho Agosto Setembro Outubro Novembro Dezembro 1958 2146 2732 2797 648 1591 897 1994 1449 1852 1777 1579 Validação de Dados Validação de Dados A validação de dados serve para limitar tipo e ou quantidade de dados que serão inseridos em uma célula. Teremos 3 exemplos diferentes, existem mais tipos para serem utilizados. Modelo para visualização Atividade para o Aluno Validação de Número Inteiro Entre 0 e 10 5 O aluno poderá utilizar inicialmente as células destacadas para testar valores fora dos intervalos indicados para ver o resultado. Em seguida poderão fazer sua atividades. Poderão modificar o tipo de validação, a lista e os números dos intervalos. Validação de Número Inteiro Entre 10 e 20 Validação de Comprimento de Texto Mín 1, Máx 3 abc Validação de Comprimento de Texto Mín 1, Máx 3 Validação Utilizando uma Lista (Lista de Nomes Abaixo) Mariana Validação Utilizando uma Lista (Lista de Nomes Abaixo) Lista para Validação Lista para Validação João Marcos Pedro Felipe Lucas Juliana Mariana Leticia Julia Pedro Helena Luciana Menu /xl/drawings/drawing13.xml#Menu!A1 Filtro Avançado Filtro Avançado O filtro avançado permite ao usuário fazer várias filtragens utilizando apenas uma tabela auxiliar, ou seja, não sendo necessário fazer a filtragem uma a uma. Tabela de Referência para o Filtro Avançado Modelo para visualização Atividade para o Aluno ID Produto Quantidade Preço Unitário Preço Total ID Produto Quantidade Preço Unitário Preço Total ID Produto Quantidade Preço Unitário Preço Total 1 Uva 11 R$ 0.43 R$ 4.74 >10 <2 2 Maçã 12 R$ 3.95 R$ 47.44 3 Morango 19 R$ 3.25 R$ 61.72 4 Pêra 7 R$ 4.74 R$ 33.15 ID Produto Quantidade Preço Unitário Preço Total 5 Limão 16 R$ 1.11 R$ 17.70 1 Uva 11 R$ 0.43 R$ 4.74 6 Laranja 10 R$ 5.86 R$ 58.57 5 Limão 16 R$ 1.11 R$ 17.70 7 Mamão 1 R$ 6.04 R$ 6.04 19 Limão 20 R$ 1.11 R$ 22.12 8 Melancia 11 R$ 3.81 R$ 41.86 9 Melão 20 R$ 2.93 R$ 58.59 Abaixo o usuário poderá colocar o mouse em cima de cada uma das informações para ler as informações desta atividade. 10 Pêssego 15 R$ 5.19 R$ 77.84 11 Melancia 17 R$ 3.81 R$ 64.69 12 Melão 14 R$ 2.93 R$ 41.01 13 Limão 5 R$ 1.11 R$ 5.53 Informação 1 tc={AB0D5BDC-4E18-4537-82F7-F91C00C5E887}: [Comentário encadeado] Sua versão do Excel permite que você leia este comentário encadeado, no entanto, as edições serão removidas se o arquivo for aberto em uma versão mais recente do Excel. Saiba mais: https://go.microsoft.com/fwlink/?linkid=870924 Comentário: A filtragem de exemplo é utilizando 2 filtros: o primeiro para a quantidade maior que 10 e o segundo para o preço unitário ser menor que 2. Desta forma temos as duas filtragens de forma mais fácil e rápida. Caso queira fazer filtragem de quantidade entre 2 e 10 é necessário repetir o cabeçalho do item desejado e criar uma opção com >2 e outra <10. 14 Pêssego 1 R$ 5.19 R$ 5.19 15 Limão 9 R$ 1.11 R$ 9.95 Informação 2 tc={F8DC440D-1EDE-46B5-AE6D-F03E353A28D4}: [Comentário encadeado] Sua versão do Excel permite que você leia este comentário encadeado, no entanto, as edições serão removidas se o arquivo for aberto em uma versão mais recente do Excel. Saiba mais: https://go.microsoft.com/fwlink/?linkid=870924 Comentário: A atividade para o aluno é modificar os filtros e testar as diversas opções, tanto como vários filtros em uma única linha que seria apenas uma filtragem completa, quanto filtros em várias linhas que seriam filtragens independentes. 16 Melão 20 R$ 2.93 R$ 58.59 17 Pêra 6 R$ 4.74 R$ 28.42 Informação 3 tc={DF1C9C28-2193-436C-A2AE-E9F1C7794D35}: [Comentário encadeado] Sua versão do Excel permite que você leia este comentário encadeado, no entanto, as edições serão removidas se o arquivo for aberto em uma versão mais recente do Excel. Saiba mais: https://go.microsoft.com/fwlink/?linkid=870924 Comentário: Caso o usuário queira fazer mais de uma filtragem, filtrando diversos itens basta colocar as outras opçãos nas linhas abaixo da primeira filtragem. Cada linha irá representar uma filtragem independente da outra. Então uma não irá interferir na outra, elas serão somadas. tc={AB0D5BDC-4E18-4537-82F7-F91C00C5E887}: [Comentário encadeado] Sua versão do Excel permite que você leia este comentário encadeado, no entanto, as edições serão removidas se o arquivo for aberto em uma versão mais recente do Excel. Saiba mais: https://go.microsoft.com/fwlink/?linkid=870924 Comentário: A filtragem de exemplo é utilizando 2 filtros: o primeiro para a quantidade maior que 10 e o segundo para o preço unitário ser menor que 2. Desta forma temos as duas filtragens de forma mais fácil e rápida. Caso queira fazer filtragem de quantidade entre 2 e 10 é necessário repetir o cabeçalho do item desejado e criar uma opção com >2 e outra <10. tc={F8DC440D-1EDE-46B5-AE6D-F03E353A28D4}: [Comentário encadeado] Sua versão do Excel permite que você leia este comentário encadeado, no entanto, as edições serão removidas se o arquivo for aberto em uma versão mais recente do Excel. Saiba mais: https://go.microsoft.com/fwlink/?linkid=870924 Comentário: A atividade para o aluno é modificar os filtros e testar as diversas opções, tanto como vários filtros em uma única linha que seria apenas uma filtragem completa, quanto filtros em várias linhas que seriam filtragens independentes. 18 Pêssego 2 R$ 5.19 R$ 10.38 19 Limão 20 R$ 1.11 R$ 22.12 20 Mamão 17 R$ 6.04 R$ 102.60 Menu /xl/drawings/drawing14.xml#Menu!A1 Classificação Avançada Classificação Avançada A classificação avançada permite ao usuário classificar mais dados de uma forma mais dinâmica e seguindo uma ordem específica imposta pelo prórprio usuário. Tabela de Referência para Classificação Avançada Atividade para o Aluno ID Produto Quantidade Preço Unitário Preço Total Neste exemplo foram utilizadas duas classificações, a primeira delas é feita pela quantidade do maior para o menor, ou seja, os produtos serão ordenados de forma que as maiores quantidades ficarão mais acima da tabela. A segunda classificação foi pelo preço unitário do menor para o maior, ou seja, dentro da classificação da quantidade teremos a classificação por preço dentro dela. Como pode ser visto temos 3 produtos com quantidades iguais a 20, e feita essa classificação será feita a classificação por preço unitário fazendo com que o menor preço apareça primeiro. ID Produto Quantidade Preço Unitário Preço Total 19 Limão 20 R$ 1.11 R$ 22.12 1 Uva 11 R$ 0.43 R$ 4.74 9 Melão 20 R$ 2.93 R$ 58.59 2 Maçã 12 R$ 3.95 R$ 47.44 16 Melão 20 R$ 2.93 R$ 58.59 3 Morango 19 R$ 3.25 R$ 61.72 3 Morango 19 R$ 3.25 R$ 61.72 4 Pêra 7 R$ 4.74 R$ 33.15 11 Melancia 17 R$ 3.81 R$ 64.69 5 Limão 16 R$ 1.11 R$ 17.70 20 Mamão 17 R$ 6.04 R$ 102.60 6 Laranja 10 R$ 5.86 R$ 58.57 5 Limão 16 R$ 1.11 R$ 17.70 7 Mamão 1 R$ 6.04 R$ 6.04 10 Pêssego 15 R$ 5.19 R$ 77.84 8 Melancia 11 R$ 3.81 R$ 41.86 12 Melão 14 R$ 2.93 R$ 41.01 9 Melão 20 R$ 2.93 R$ 58.59 2 Maçã 12 R$ 3.95 R$ 47.44 10 Pêssego 15 R$ 5.19 R$ 77.84 1 Uva 11 R$ 0.43 R$ 4.74 A classificação obedece a ordem que o usuário coloca, neste caso foi utilizada a quantidade e em seguida o preço unitário. Portanto o aluno poderá escolher as diversas opções de classificação em diversas ordens para que possa obter o resultado desejado. Isso irá depender de como quer os dados organizados para facilitar a análise dos mesmos. 11 Melancia 17 R$ 3.81 R$ 64.69 8 Melancia 11 R$ 3.81R$ 41.86 12 Melão 14 R$ 2.93 R$ 41.01 6 Laranja 10 R$ 5.86 R$ 58.57 13 Limão 5 R$ 1.11 R$ 5.53 15 Limão 9 R$ 1.11 R$ 9.95 14 Pêssego 1 R$ 5.19 R$ 5.19 4 Pêra 7 R$ 4.74 R$ 33.15 15 Limão 9 R$ 1.11 R$ 9.95 17 Pêra 6 R$ 4.74 R$ 28.42 16 Melão 20 R$ 2.93 R$ 58.59 13 Limão 5 R$ 1.11 R$ 5.53 17 Pêra 6 R$ 4.74 R$ 28.42 18 Pêssego 2 R$ 5.19 R$ 10.38 Teste as diferentes possibilidade em diferentes ordens para observar o modo em que os dados são organizados. 18 Pêssego 2 R$ 5.19 R$ 10.38 14 Pêssego 1 R$ 5.19 R$ 5.19 19 Limão 20 R$ 1.11 R$ 22.12 7 Mamão 1 R$ 6.04 R$ 6.04 20 Mamão 17 R$ 6.04 R$ 102.60 Menu /xl/drawings/drawing15.xml#Menu!A1 CONT.SE CONT.SE A função CONT.SE nos permite fazer uma contagem baseado em um critério, neste caso poderemos contar a quantidade de produtos totais que possuímos mesmo quando eles se repetem. Tabela de Referência para CONT.SE Modelo para visualização Atividade para o Aluno ID Produto Quantidade Preço Unitário Preço Total Vamos utilizar a fórmula CONT.SE para contar as quantidades de produtos. No entanto essa contagem será feita na coluna de produto, ou seja, iremos contar quantos compras desses produtos foram feitas. 1 Uva 11 R$ 0.43 R$ 4.74 Produto Ocorrência 2 Maçã 12 R$ 3.95 R$ 47.44 Melão 3 Morango 19 R$ 3.25 R$ 61.72 Melancia 4 Pêra 7 R$ 4.74 R$ 33.15 Mamão 5 Limão 16 R$ 1.11 R$ 17.70 Morango 6 Laranja 10 R$ 5.86 R$ 58.57 7 Mamão 1 R$ 6.04 R$ 6.04 Preço menor que 1,50 8 Melancia 11 R$ 3.81 R$ 41.86 Produto Ocorrência Condição Ocorrência 9 Melão 20 R$ 2.93 R$ 58.59 Uva 1 < 1,50 10 Pêssego 15 R$ 5.19 R$ 77.84 Maçã 1 11 Melancia 17 R$ 3.81 R$ 64.69 Morango 1 Quantidade maior que 10 12 Melão 14 R$ 2.93 R$ 41.01 Pêra 2 Condição Ocorrência 13 Limão 5 R$ 1.11 R$ 5.53 Limão 4 > 10 14 Pêssego 1 R$ 5.19 R$ 5.19 15 Limão 9 R$ 1.11 R$ 9.95 Essa fórmula pode ser utilizada também para contar quantos produtos tem quantidades maiores que 10 por exemplo, ou preços menores que 1,50. As possibilidades são várias, o importante é lembrar que essa fórmula conta a quantidade de vezes que um evento ocorre. 16 Melão 20 R$ 2.93 R$ 58.59 17 Pêra 6 R$ 4.74 R$ 28.42 18 Pêssego 2 R$ 5.19 R$ 10.38 19 Limão 20 R$ 1.11 R$ 22.12 20 Mamão 17 R$ 6.04 R$ 102.60 Menu /xl/drawings/drawing16.xml#Menu!A1 SOMASE E MÉDIASE SOMASE E MÉDIASE As funções SOMASE e MÉDIASE são parecidas com as funções normais delas, no entanto podemos colocar uma condição para somar ou obter a média. Tabela de Referência para SOMASE e MÉDIASE Modelo para visualização Atividade para o Aluno ID Produto Quantidade Preço Unitário Preço Total Vamos utilizar a fórmula SOMASE para sabermos a quantidade total de produtos comprados e em seguida vamos utilziar a fórmula MÉDIASE para saber a média de quantidade por compra efetuada. 1 Uva 11 R$ 0.43 R$ 4.74 Produto Quantidade Média 2 Maçã 12 R$ 3.95 R$ 47.44 Uva 3 Morango 19 R$ 3.25 R$ 61.72 Melancia 4 Pêra 7 R$ 4.74 R$ 33.15 Mamão 5 Limão 16 R$ 1.11 R$ 17.70 Morango 6 Laranja 10 R$ 5.86 R$ 58.57 7 Mamão 1 R$ 6.04 R$ 6.04 8 Melancia 11 R$ 3.81 R$ 41.86 Produto Quantidade Média 9 Melão 20 R$ 2.93 R$ 58.59 Melão 54 18 10 Pêssego 15 R$ 5.19 R$ 77.84 Maçã 12 12 11 Melancia 17 R$ 3.81 R$ 64.69 Morango 19 19 12 Melão 14 R$ 2.93 R$ 41.01 Pêra 13 6.5 13 Limão 5 R$ 1.11 R$ 5.53 Limão 50 12.5 14 Pêssego 1 R$ 5.19 R$ 5.19 15 Limão 9 R$ 1.11 R$ 9.95 Vale lembrar que a coluna de produtos significa uma compra, ou seja, em uma única compra podemos ter várias quantidades. Podemos ter compras repetidas com quantidades diferentes. 16 Melão 20 R$ 2.93 R$ 58.59 17 Pêra 6 R$ 4.74 R$ 28.42 18 Pêssego 2 R$ 5.19 R$ 10.38 19 Limão 20 R$ 1.11 R$ 22.12 20 Mamão 17 R$ 6.04 R$ 102.60 Menu /xl/drawings/drawing17.xml#Menu!A1 CONT.SES e MÉDIASES CONT.SES e MÉDIASES As funções CONT.SES e MÉDIASES são parecidas com as funções normais delas, no entanto podemos colocar mais condições para ter um resultado mais específico. Tabela de Referência para CONT.SES e MÉDIASEs Modelo para visualização Atividade para o Aluno ID Produto Quantidade Preço Unitário Preço Total Vamos utilizar a fórmula CONT.SES para analisar em quantas compras de cada um dos produtos tiveram quantidades maiores que 10.E a fórmula MÉDIASES para analisar a média dos produtos que são maior que 10. 1 Uva 11 R$ 0.43 R$ 4.74 Condição 1 Condição 2 CONT.SES MÉDIASES 2 Maçã 12 R$ 3.95 R$ 47.44 Uva < 5 3 Morango 19 R$ 3.25 R$ 61.72 Melancia < 5 4 Pêra 7 R$ 4.74 R$ 33.15 Mamão < 5 5 Limão 16 R$ 1.11 R$ 17.70 Morango < 5 6 Uva 10 R$ 5.86 R$ 58.57 7 Mamão 1 R$ 6.04 R$ 6.04 8 Melancia 11 R$ 3.81 R$ 41.86 Condição 1 Condição 2 CONT.SES MÉDIASES 9 Melão 20 R$ 2.93 R$ 58.59 Melão > 10 2 17 10 Pêssego 15 R$ 5.19 R$ 77.84 Maçã > 10 2 14.5 11 Melancia 17 R$ 3.81 R$ 64.69 Morango > 10 1 19 12 Melão 14 R$ 2.93 R$ 41.01 Pêra > 10 1 12 13 Limão 5 R$ 1.11 R$ 5.53 Limão > 10 2 18 14 Pêssego 1 R$ 5.19 R$ 5.19 15 Limão 9 R$ 1.11 R$ 9.95 Vale lembrar que a coluna de produtos significa uma compra, ou seja, em uma única compra podemos ter várias quantidades. Podemos ter compras repetidas com quantidades diferentes. 16 Maçã 17 R$ 2.93 R$ 49.80 17 Pêra 12 R$ 4.74 R$ 56.83 18 Pêssego 2 R$ 5.19 R$ 10.38 19 Limão 20 R$ 1.11 R$ 22.12 20 Mamão 17 R$ 6.04 R$ 102.60 Menu /xl/drawings/drawing18.xml#Menu!A1 PROCV e PROCH PROCV e PROCH Essas são funções de procura ma vertical (PROCV) e na horizontal (PROCH), funcionam para pesquisas de dados de uma forma mais rápida, podendo apenas alterar o nome/produto ou o que esteja sendo procurado em cada caso. Feito isso as células contendo essas fórmulas serão atualizadas automaticamente. Tabelas de Referência para PROCV e PROCH PROCV Atividade para o Aluno Nome Idade Cargo Salário Cliente Idade Cargo Salário Cliente Idade Cargo Salário Lucas 42 Engenheiro R$ 6,000.00 Matheus 51 Gerente R$ 4,500.00 Maria Pedro 22 Auxiliar R$ 1,800.00 Matheus 51 Gerente R$ 4,500.00 Basta o usuário modificar o nome do funcionário que todas as suas informações serão atualizadas automaticamente levando em conta a tabela completa a esquerda. Luciana 25 Auxiliar R$ 1,800.00 Maria 60 Auxiliar R$ 1,800.00 Julia 45 Assistente R$ 2,500.00 Leticia 23 Secretária R$ 2,000.00 Joao 34 Engenheiro R$ 6,000.00 PROCH Paulo 49 Assistente R$ 2,500.00 Nome Tempo Nome Tempo Ana 30 Secretária R$ 2,000.00 Joao 10.34 Mariana Nome Joao Mariana Marcos Basta o usuário modificar o nome que o tempo de cada pessoa será atualizado. Tempo 10.34 9.58 11.23 Menu /xl/drawings/drawing19.xml#Menu!A1 Tabela Dinâmica Tabela Dinâmica A tabela dinâmica é uma forma mais rápida de análise dos dados da tabela, podendo modificar como os dados são apresentados, a ordem que são apresentados ou até mesmo a quantidade de dados que serão aprensentados, ou seja, é possível alterar a ordem ou deixar de apresentar alguns dados "desnecessários" no momento. Tabela base para a criação da tabela dinâmica ID Nome Produto Quantidade Preço Unitário Preço Total Rótulos de Linha Soma de Preço Unitário Soma de Preço Total O usuário pode alterar como os dados são apresentados na tabela dinâmica, pode alterar a ordem em que os dados são colocados. Por exemplo ao invés de colocarmos nomes e em seguida produtos, podemos colocar produtos e em seguida podemos colocar os nomes. Desta forma veremos os produtos primeirose quando abrirmos veremos as pessoas que compraram estes produtos. 1 Joao Mochila 2 R$ 200.00 R$ 400.00 Ana R$ 250.00 R$ 250.00 2 Mariana Livro 4 R$ 25.00 R$ 100.00 Helena R$ 150.50 R$ 157.50 3 Lucas Caderno 3 R$ 10.00 R$ 30.00 Joao R$ 475.00 R$ 1,325.00 4 Pedro Caneta 10 R$ 2.00 R$ 20.00 Livro R$ 25.00 R$ 175.00 5 Helena Lápis 15 R$ 0.50 R$ 7.50 Mochila R$ 450.00 R$ 1,150.00 6 Ana Mochila 1 R$ 250.00 R$ 250.00 2 R$ 200.00 R$ 400.00 7 Paula Caderno 2 R$ 15.00 R$ 30.00 3 R$ 250.00 R$ 750.00 8 Lucas Caderno 4 R$ 20.00 R$ 80.00 Lucas R$ 30.00 R$ 110.00 9 Pedro Lápis 5 R$ 0.30 R$ 1.50 Caderno R$ 30.00 R$ 110.00 10 Mariana Caneta 3 R$ 1.70 R$ 5.10 3 R$ 10.00 R$ 30.00 11 Helena Mochila 1 R$ 150.00 R$ 150.00 4 R$ 20.00 R$ 80.00 A forma em que a tabela dinâmica vai depender de como o usuário precisa que os dados apareçam, desta forma ela pode ser modificada rapidamente para se adequar a situação, facilitando a análise dos dados em questão. 12 Pedro Livro 5 R$ 25.00 R$ 125.00 Mariana R$ 29.20 R$ 117.60 13 Joao Livro 7 R$ 25.00 R$ 175.00 Caneta R$ 4.20 R$ 17.60 14 Joao Mochila 3 R$ 250.00 R$ 750.00 3 R$ 1.70 R$ 5.10 15 Mariana Caneta 5 R$ 2.50 R$ 12.50 5 R$ 2.50 R$ 12.50 Livro R$ 25.00 R$ 100.00 Paula R$ 15.00 R$ 30.00 Pedro R$ 27.30 R$ 146.50 Total Geral R$ 977.00 R$ 2,136.60 Menu /xl/drawings/drawing20.xml#Menu!A1 Gráfico Dinâmico Gráfico Dinâmico O gráfico dinâmico é uma representação visual da tabela dinâmica, então o que for representado na tabela dinâmica será automaticamente representado pelo gráfico dinâmico. A medida em que o usuário altera a tabela o gráfico também se altera para acompanhar a visualização da tabela. Tabela base para a criação da tabela dinâmica ID Nome Produto Quantidade Preço Unitário Preço Total Rótulos de Linha Soma de Preço Unitário Soma de Preço Total O usuário pode alterar como os dados são apresentados na tabela dinâmica, pode alterar a ordem em que os dados são colocados. Por exemplo ao invés de colocarmos nomes e em seguida produtos, podemos colocar produtos e em seguida podemos colocar os nomes. Desta forma veremos os produtos primeiros e quando abrirmos veremos as pessoas que compraram estes produtos. 1 Joao Mochila 2 R$ 200.00 R$ 400.00 Ana R$ 250.00 R$ 250.00 2 Mariana Livro 4 R$ 25.00 R$ 100.00 Helena R$ 150.50 R$ 157.50 3 Lucas Caderno 3 R$ 10.00 R$ 30.00 Lápis R$ 0.50 R$ 7.50 4 Pedro Caneta 10 R$ 2.00 R$ 20.00 15 R$ 0.50 R$ 7.50 5 Helena Lápis 15 R$ 0.50 R$ 7.50 Mochila R$ 150.00 R$ 150.00 6 Ana Mochila 1 R$ 250.00 R$ 250.00 1 R$ 150.00 R$ 150.00 7 Paula Caderno 2 R$ 15.00 R$ 30.00 Joao R$ 475.00 R$ 1,325.00 8 Lucas Caderno 4 R$ 20.00 R$ 80.00 Livro R$ 25.00 R$ 175.00 9 Pedro Lápis 5 R$ 0.30 R$ 1.50 7 R$ 25.00 R$ 175.00 10 Mariana Caneta 3 R$ 1.70 R$ 5.10 Mochila R$ 450.00 R$ 1,150.00 11 Helena Mochila 1 R$ 150.00 R$ 150.00 2 R$ 200.00 R$ 400.00 A medida que esses dados são alterado na tabela dinâmica o gráfico dinâmico se altera automaticamente para se adequar ao que está sendo mostrado na tabela dinâmica. Então basta alterar para observar as mudanças no gráfico. 12 Pedro Livro 5 R$ 25.00 R$ 125.00 3 R$ 250.00 R$ 750.00 13 Joao Livro 7 R$ 25.00 R$ 175.00 Lucas R$ 30.00 R$ 110.00 14 Joao Mochila 3 R$ 250.00 R$ 750.00 Mariana R$ 29.20 R$ 117.60 15 Mariana Caneta 5 R$ 2.50 R$ 12.50 Paula R$ 15.00 R$ 30.00 Pedro R$ 27.30 R$ 146.50 Total Geral R$ 977.00 R$ 2,136.60 A tabela base que gerou a tabela e o gráfico dinâmico está atrás do gráfico, caso queira conferir os dados ou fazer alguma alteração basta mover o gráfico. Menu /xl/drawings/drawing21.xml#Menu!A1 Soma de Preço Unitário 15 1 7 2 3 Lápis Mochila Livro Mochila Ana Helena Joao Lucas Mariana Paula Pedro 250 0.5 150 25 200 250 30 29.2 15 27.3 Soma de Preço Total 15 1 7 2 3 Lápis Mochila Livro Mochila Ana Helena Joao Lucas Mariana Paula Pedro 250 7.5 150 175 400 750 110 117.6 30 146.5 19,99R$ 15% 19,99R$ 15% 19,99R$ 15%
Compartilhar