Baixe o app para aproveitar ainda mais
Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original
INTRO Funções avançadas do Excel (demonstração) Gabriel Reina e Marcelo Venske — Cavalcante Consultores www.cavalcanteassociados.com.br Esta planilha contém uma fração do conteúdo apresentado no treinamento Funções avançadas no Excel, realizado mensalmente pela Cavalcante. No treinamento, você aprenderá a utilizar cada uma das funções a seguir e resolverá diversos casos práticos, além de contar com uma apostila exclusiva. Todo o material é distribuído em CD. Esta planilha contém os seguintes materiais: § Apresentações e exemplos de aplicação das principais fórmulas avançadas do Excel; § Casos práticos baseados em situações reais para aplicação das fórmulas apresentadas. A planilha TESTE contém um banco de dados para que você possa conferir a aplicação de funções e testar suas idéias. Ela também possui um gerador de amostras aleatórias de quatro grandezas: datas, nomes, quantidades inteiras e valores em reais. Use esta ferramenta durante e após o treinamento para obter rapidamente amostras para a realização de testes. Para conhecer várias outras funções e recursos do Excel, visite o Express Training, a biblioteca online da Cavalcante: www.expresstraining.com.br www.cavalcanteassociados.com.br www.expresstraining.com.br TESTE Planilha para testes Se desejar, use esta planilha para testar funções ou recursos apresentados no treinamento. Data da venda Cliente Cidade Produto Prazo (dias) Preço (R$/kg) Volume (kg) Valor (R$) Vendedor Comissão (R$) 10/07/2000 Extra Barueri Rolamento $60 $3 $1,000 $2,660 Luciana $319 10/07/2000 Big São Paulo Roda $45 $7 $800 $5,632 Eduardo $676 10/07/2000 Carrefour Diadema Roda $45 $7 $300 $2,112 Rodrigo $253 11/07/2000 Sonda Diadema Repimboca $60 $7 $200 $1,408 Eduardo $169 12/07/2000 Carrefour São Paulo Repimboca $45 $7 $1,000 $7,040 Rodrigo $845 15/07/2000 Big Barueri Repimboca $30 $7 $150 $1,056 Rodrigo $127 15/07/2000 Carrefour S. Caetano Roda $45 $7 $500 $3,520 Rodrigo $422 15/07/2000 Sonda Diadema Dínamo $45 $10 $550 $5,280 Amanda $634 27/08/2000 Carrefour São Paulo Rolamento $30 $3 $600 $1,560 Luciana $187 15/07/2000 Big S. Caetano Repimboca $45 $4 $100 $402 Luciana $48 15/08/2000 Carrefour São Paulo Rolamento $30 $3 $300 $780 Eduardo $94 15/07/2000 Sonda Diadema Repimboca $30 $4 $300 $1,206 Eduardo $145 15/05/2000 Carrefour Barueri Roda $45 $7 $500 $3,520 Luciana $422 15/05/2000 Big São Paulo Painel $45 $4 $200 $804 Luciana $96 15/07/2000 Carrefour Diadema Condensador $30 $6 $100 $556 Eduardo $67 25/06/2000 Sonda São Paulo Painel $45 $4 $50 $201 Luciana $24 25/06/2000 Carrefour Barueri Repimboca $45 $6 $200 $1,112 Luciana $133 27/08/2000 Big São Paulo Eixo $45 $3 $40 $111 Luciana $13 15/07/2000 Carrefour Barueri Dínamo $60 $10 $2,000 $19,200 Amanda $2,304 15/07/2000 Sonda S. Caetano Painel $45 $4 $220 $884 Luciana $106 25/06/2000 Carrefour São Paulo Eixo $60 $3 $1,100 $3,047 Luciana $366 15/08/2000 Big São Paulo Parafuseta $45 $3 $880 $2,438 Luciana $293 25/06/2000 Carrefour Barueri Parafuseta $60 $3 $500 $1,300 Luciana $156 25/06/2000 Sonda São Paulo Rolamento $30 $3 $600 $1,560 Luciana $187 25/05/2000 Carrefour Barueri Roda $45 $7 $400 $2,816 Rodrigo $338 15/08/2000 Big São Paulo Parafuseta $45 $6 $450 $2,502 Eduardo $300 25/06/2000 Carrefour Diadema Rolamento $45 $3 $600 $1,560 Rodrigo $187 25/06/2000 Sonda São Paulo Rolamento $60 $3 $750 $1,950 Luciana $234 25/06/2000 Carrefour São Paulo Eixo $45 $3 $180 $499 Eduardo $60 25/06/2000 Big São Paulo Rolamento $45 $3 $300 $780 Luciana $94 15/07/2000 Carrefour São Paulo Roda $60 $7 $600 $4,224 Luciana $507 10/07/2000 Sonda São Paulo Rolamento $45 $3 $800 $2,080 Luciana $250 25/06/2000 Carrefour Barueri Rolamento $60 $3 $900 $2,340 Luciana $281 15/08/2000 Big São Paulo Eixo $60 $3 $500 $1,385 Luciana $166 15/07/2000 Carrefour S. Caetano Rolamento $60 $3 $530 $1,378 Luciana $165 25/06/2000 Sonda São Paulo Repimboca $60 $4 $800 $3,216 Carlos $386 25/06/2000 Carrefour São Paulo Roda $45 $7 $680 $4,787 Luciana $574 25/06/2000 Big São Paulo Painel $45 $4 $660 $2,653 Luciana $318 10/07/2000 Carrefour São Paulo Condensador $45 $6 $600 $3,336 Luciana $400 15/08/2000 Sonda Barueri Painel $30 $4 $530 $2,131 Luciana $256 12/08/2000 Carrefour São Paulo Condensador $45 $6 $450 $2,502 Luciana $300 15/08/2000 Big São Paulo Eixo $30 $3 $400 $1,108 Luciana $133 15/08/2000 Carrefour Diadema Parafuseta $30 $10 $300 $2,880 Luciana $346 15/07/2000 Sonda Diadema Rolamento $30 $3 $200 $520 Luciana $62 15/05/2000 Carrefour São Paulo Painel $30 $4 $100 $402 Luciana $48 15/08/2000 Big Barueri Roda $30 $7 $30 $211 Luciana $25 15/08/2000 Carrefour São Paulo Painel $45 $4 $220 $884 Rodrigo $106 15/08/2000 Sonda São Paulo Condensador $45 $6 $460 $2,558 Eduardo $307 12/08/2000 Carrefour São Paulo Painel $45 $4 $1,000 $4,020 Luciana $482 12/08/2000 Big São Paulo Repimboca $45 $6 $1,200 $6,672 Luciana $801 15/08/2000 Carrefour São Paulo Eixo $45 $3 $200 $554 Eduardo $66 10/05/2000 Sonda S. Caetano Parafuseta $45 $10 $500 $4,800 Rodrigo $576 27/08/2000 Carrefour São Paulo Painel $60 $4 $600 $2,412 Luciana $289 27/05/2000 Big Barueri Repimboca $45 $3 $1,000 $2,770 Luciana $332 10/07/2000 Carrefour Barueri Eixo $60 $3 $1,000 $2,770 Luciana $332 25/06/2000 Sonda São Paulo Repimboca $45 $3 $1,200 $3,324 Rodrigo $399 27/08/2000 Carrefour São Paulo Dínamo $60 $10 $500 $4,800 Luciana $576 15/07/2000 Big São Paulo Condensador $45 $6 $300 $1,668 Luciana $200 27/08/2000 Carrefour Diadema Repimboca $45 $4 $450 $1,809 Luciana $217 27/08/2000 Sonda Diadema Condensador $45 $6 $400 $2,224 Luciana $267 27/08/2000 Carrefour São Paulo Eixo $30 $3 $200 $554 Luciana $66 27/05/2000 Big São Paulo Condensador $45 $6 $1,500 $8,340 Luciana $1,001 15/08/2000 Carrefour Diadema Painel $45 $4 $380 $1,528 Luciana $183 27/08/2000 Sonda Diadema Repimboca $30 $6 $1,300 $7,228 Luciana $867 27/08/2000 Carrefour São Paulo Eixo $45 $3 $460 $1,274 Luciana $153 27/08/2000 Big Barueri Dínamo $45 $10 $450 $4,320 Luciana $518 15/08/2000 Carrefour Diadema Painel $30 $4 $560 $2,251 Amanda $270 27/08/2000 Sonda São Paulo Eixo $45 $3 $1,000 $2,770 Luciana $332 27/08/2000 Carrefour São Paulo Dínamo $45 $10 $500 $4,800 Luciana $576 27/08/2000 Big Barueri Repimboca $30 $3 $450 $1,247 Eduardo $150 27/08/2000 Carrefour São Paulo Eixo $30 $3 $650 $1,801 Luciana $216 27/08/2000 Sonda Barueri Condensador $45 $6 $750 $4,170 Luciana $500 15/08/2000 Carrefour Barueri Eixo $30 $3 $800 $2,216 Eduardo $266 15/08/2000 Big São Paulo Dínamo $45 $10 $500 $4,800 Luciana $576 27/08/2000 Carrefour São Paulo Painel $45 $4 $600 $2,412 Luciana $289 27/08/2000 Sonda Barueri Eixo $60 $3 $300 $831 Luciana $100 27/05/2000 Carrefour São Paulo Painel $45 $4 $600 $2,412 Luciana $289 27/08/2000 Big Barueri Eixo $45 $3 $1,000 $2,770 Luciana $332 10/07/2000 Carrefour Barueri Repimboca $60 $3 $1,000 $2,770 Luciana $332 25/05/2000 Carrefour Barueri Roda $45 $7 $400 $2,816 Rodrigo $338 15/08/2000 Big São Paulo Parafuseta $60 $6 $450 $2,502 Eduardo $300 25/06/2000 Carrefour Diadema Rolamento $60 $3 $600 $1,560 Rodrigo $187 25/06/2000 Sonda São Paulo Rolamento $45 $3 $750 $1,950 Luciana $234 25/06/2000 Carrefour São Paulo Repimboca $45 $3 $180 $499 Eduardo $60 25/06/2000 Big São Paulo Parafuseta $60 $3 $300 $780 Luciana $94 15/07/2000 Carrefour São Paulo Roda $45 $7 $600 $4,224 Luciana $507 15/05/2000 Carrefour São Paulo Rolamento $45 $3 $300 $780 Eduardo $94 15/07/2000 Sonda Diadema Repimboca $60 $4 $300 $1,206 Eduardo $145 15/07/2000 Carrefour Barueri Roda $45 $7 $500 $3,520 Luciana $422 15/07/2000 Big São Paulo Painel $45 $4 $200 $804 Luciana $96 15/07/2000 Carrefour Diadema Condensador $60 $6 $100 $556 Eduardo $67 25/05/2000 Sonda São Paulo Repimboca $45 $4 $50 $201 Luciana $24 25/06/2000 Carrefour Barueri Condensador $30 $6 $200 $1,112 Luciana $133 12/07/2000 Carrefour São Paulo Roda $45 $7 $1,000 $7,040 Rodrigo $845 15/07/2000 Big Barueri Roda $45 $7 $150 $1,056 Rodrigo $127 15/07/2000 Carrefour S. Caetano Roda $30 $7 $500 $3,520 Rodrigo $422 15/07/2000 Sonda Diadema Dínamo $45 $10 $550 $5,280 Amanda $634 27/08/2000 Carrefour São Paulo Rolamento $30 $3 $600 $1,560 Luciana $187 15/05/2000 Big S. Caetano Painel $45 $4 $100 $402 Luciana $48 15/08/2000 Carrefour São Paulo Rolamento $45 $3 $300 $780 Eduardo $94 15/07/2000 Sonda Diadema Parafuseta $60 $4 $300 $1,206 Eduardo $145 15/07/2000 Carrefour Barueri Roda $45 $7 $500 $3,520 Luciana $422 15/07/2000 Big São Paulo Painel $45 $4 $200 $804 Luciana $96 15/07/2000 Carrefour Diadema Condensador $60 $6 $100 $556 Eduardo $67 25/06/2000 Sonda São Paulo Repimboca $45 $4 $50 $201 Luciana $24 25/06/2000 Carrefour Barueri Repimboca $45 $6 $200 $1,112 Luciana $133 27/05/2000 Big São Paulo Eixo $45 $3 $40 $111 Luciana $13 15/07/2000 Big S. Caetano Repimboca $30 $4 $100 $402 Luciana $48 15/08/2000 Carrefour São Paulo Rolamento $45 $3 $300 $780 Eduardo $94 15/07/2000 Sonda Diadema Repimboca $45 $4 $300 $1,206 Eduardo $145 15/05/2000 Carrefour Barueri Roda $60 $7 $500 $3,520 Luciana $422 15/05/2000 Big São Paulo Painel $45 $4 $200 $804 Luciana $96 27/08/2000 Carrefour São Paulo Rolamento $60 $3 $600 $1,560 Luciana $187 15/05/2000 Big S. Caetano Painel $45 $4 $100 $402 Luciana $48 15/08/2000 Carrefour São Paulo Rolamento $45 $3 $300 $780 Eduardo $94 Banco de nomes Alice Ana Carlos Carolina David Herman José Oscar Paulo Ricardo Gerador de números aleatórios: cole qualquer uma das amostras como valor. Para gerar uma nova amostra, recalcule a planilha pressionando a tecla F9. Data Nome Quantidade Valor 27/07/2007 Alice $227 * 41,753.58 28/07/2007 Carlos $244 * 47,000.78 29/07/2007 Carlos $244 * 45,079.28 30/07/2007 Ana $237 * 40,865.16 31/07/2007 Ana $262 * 47,570.43 01/08/2007 Carlos $210 * 41,939.34 02/08/2007 Carlos $201 * 47,785.14 03/08/2007 Carlos $200 * 41,813.45 04/08/2007 Alice $216 * 47,425.71 05/08/2007 Carolina $254 * 49,067.49 06/08/2007 Carlos $234 * 44,328.51 07/08/2007 David $232 * 41,025.09 08/08/2007 Carolina $211 * 43,675.65 09/08/2007 Alice $243 * 40,683.49 10/08/2007 Ana $201 * 49,222.31 CP3 Caso prático: ÍNDICE (INDEX) com CORRESP (MATCH) Unidades / Dias 0 0 0 0 0 0 Calculadora de descontos 01 ?0.00% ?0.00% ?0.00% ?0.00% ?0.00% ?0.00% Valor total da compra * 5,000.00 ç Altere 02 ?2.00% ?0.00% ?0.00% ?0.00% ?0.00% ?0.00% Quantidade de itens $50 ç Altere 03 ?2.00% ?0.00% ?0.00% ?0.00% ?0.00% ?0.00% Condição de pagamento $30 ç Altere 04 ?2.00% ?1.00% ?0.00% ?0.00% ?0.00% ?0.00% Valor do desconto * 250.00 05 ?3.00% ?1.00% ?0.90% ?0.00% ?0.00% ?0.00% Total a pagar * 4,750.00 06 ?3.50% ?1.50% ?0.90% ?0.50% ?0.00% ?0.00% 07 ?3.50% ?1.50% ?1.00% ?0.80% ?0.60% ?0.00% A tabela à esquerda contém diferentes taxas de desconto que podem ser concedidas a um cliente com base nas condições de pagamento desejadas e no número de itens comprados. Complete a calculadora acima. 08 ?3.50% ?2.50% ?1.00% ?0.80% ?0.60% ?0.20% 09 ?3.50% ?2.50% ?1.00% ?0.80% ?0.60% ?0.20% 10 ?3.50% ?2.50% ?1.00% ?0.80% ?0.60% ?0.20% 11 ?4.00% ?3.00% ?2.00% ?1.20% ?0.90% ?0.50% 12 ?4.00% ?3.00% ?2.00% ?1.20% ?0.90% ?0.50% 13 ?4.00% ?3.00% ?2.00% ?1.20% ?0.90% ?0.50% 14 ?4.00% ?3.00% ?2.00% ?1.20% ?0.90% ?0.50% 15 ?4.00% ?3.00% ?2.00% ?1.20% ?0.90% ?0.50% 16 ?4.50% ?3.00% ?2.00% ?1.20% ?0.90% ?0.50% 17 ?4.50% ?3.00% ?2.00% ?1.20% ?0.90% ?0.50% 18 ?4.50% ?3.00% ?2.00% ?1.20% ?0.90% ?0.50% 19 ?4.50% ?3.00% ?2.00% ?1.20% ?0.90% ?0.50% 20 ?4.50% ?3.00% ?2.00% ?1.20% ?0.90% ?0.50% 21 ?4.50% ?3.00% ?2.00% ?1.20% ?0.90% ?0.50% 22 ?4.50% ?3.00% ?2.00% ?1.20% ?0.90% ?0.50% 23 ?5.00% ?4.00% ?3.00% ?2.30% ?1.50% ?1.00% 24 ?5.00% ?4.00% ?3.00% ?2.30% ?1.50% ?1.00% 25 ?5.00% ?4.00% ?3.00% ?2.30% ?1.50% ?1.00% 26 ?5.00% ?4.00% ?3.00% ?2.30% ?1.50% ?1.00% 27 ?5.00% ?4.00% ?3.00% ?2.30% ?1.50% ?1.00% 28 ?5.00% ?4.00% ?3.00% ?2.30% ?1.50% ?1.00% 29 ?5.00% ?4.00% ?3.00% ?2.30% ?1.50% ?1.00% 30 ?7.00% ?5.50% ?5.00% ?4.00% ?2.20% ?1.70% 31 ?7.00% ?5.50% ?5.00% ?4.00% ?2.20% ?1.70% 32 ?7.00% ?5.50% ?5.00% ?4.00% ?2.20% ?1.70% 33 ?7.00% ?5.50% ?5.00% ?4.00% ?2.20% ?1.70% 34 ?7.00% ?5.50% ?5.00% ?4.00% ?2.20% ?1.70% 35 ?7.00% ?5.50% ?5.00% ?4.00% ?2.20% ?1.70% 36 ?7.00% ?5.50% ?5.00% ?4.00% ?2.20% ?1.70% 37 ?7.00% ?5.50% ?5.00% ?4.00% ?2.20% ?1.70% 38 ?7.00% ?5.50% ?5.00% ?4.00% ?2.20% ?1.70% 39 ?7.00% ?5.50% ?5.00% ?4.00% ?2.20% ?1.70% 40 ?10.00% ?7.00% ?6.50% ?5.00% ?3.50% ?3.00% 41 ?10.00% ?7.00% ?6.50% ?5.00% ?3.50% ?3.00% 42 ?10.00% ?7.00% ?6.50% ?5.00% ?3.50% ?3.00% 43 ?10.00% ?7.00% ?6.50% ?5.00% ?3.50% ?3.00% 44 ?10.00% ?7.00% ?6.50% ?5.00% ?3.50% ?3.00% 45 ?10.00% ?7.00% ?6.50% ?5.00% ?3.50% ?3.00% 46 ?10.00% ?7.00% ?6.50% ?5.00% ?3.50% ?3.00% 47 ?10.00% ?7.00% ?6.50% ?5.00% ?3.50% ?3.00% 48 ?10.00% ?7.00% ?6.50% ?5.00% ?3.50% ?3.00% 49 ?10.00% ?7.00% ?6.50% ?5.00% ?3.50% ?3.00% 50 ?10.00% ?7.00% ?6.50% ?5.00% ?3.50% ?3.00% CP4 Caso prático: INDIRETO (INDIRECT) com CONT.NÚM (COUNT) Saldo atual * (797.61) Desejamos que o valor de C4 seja o saldo na tabela abaixo, ou seja, o último valor numérico disponível na coluna G. Data Descrição [+] Entrada [-] Saída [-] CPMF [=] Saldo 18/01/2007 Depósito $900.00 0.0 $900.00 18/01/2007 Energia elétrica $4,545.00 $1,000.00 $3.80 $4,441.20 18/01/2007 Restaurante 0.0 $154.00 $0.59 $4,286.61 18/01/2007 $50.00 $0.19 $4,236.42 19/01/2007 $45.35 $0.17 $4,190.90 20/01/2007 $50.00 $900.05 $3.42 $3,337.43 20/01/2007 $128.00 $1,895.45 $7.20 $1,562.78 21/01/2007 $2,450.70 $9.31 $(897.23) 22/01/2007 $200.00 0.0 $(697.23) 23/01/2007 $100.00 $0.38 $(797.61) 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 DESLOC DESLOC (OFFSET) DESLOC recebe uma área da planilha e devolve outra área, de tamanho arbitrário, definida como um deslocamento em relação à original. Data Temperatura média Data 14/09/2006 ç Altere a data! 01/08/2006 $18.47 Número da medição 45 02/08/2006 $19.46 Média na última semana 22.21 03/08/2006 $21.31 04/08/2006 $20.19 Use CORRESP (MATCH) para obter o valor de F7. 05/08/2006 $19.18 06/08/2006 $22.56 07/08/2006 $20.93 Desafio: você é capaz de realizar as melhorias abaixo neste sistema? 08/08/2006 $21.43 09/08/2006 $24.19 § Permitir apenas valores pertencentes à lista de datas em F6; 10/08/2006 $23.29 11/08/2006 $24.14 § Calcular a média utilizando apenas os dias disponíveis caso a data inicial esteja dentro da primeira semana, ou seja, evitar que o intervalo devolvido por DESLOC caia fora da área da tabela. 12/08/2006 $28.84 13/08/2006 $29.98 14/08/2006 $29.51 15/08/2006 $31.01 16/08/2006 $33.32 17/08/2006 $29.81 18/08/2006 $28.98 19/08/2006 $29.91 20/08/2006 $28.77 21/08/2006 $28.08 22/08/2006 $27.92 23/08/2006 $29.11 24/08/2006 $25.84 25/08/2006 $26.99 26/08/2006 $27.39 27/08/2006 $24.41 28/08/2006 $26.95 29/08/2006 $25.71 30/08/2006 $22.96 31/08/2006 $21.30 01/09/2006 $20.44 02/09/2006 $16.27 03/09/2006 $14.94 04/09/2006 $15.13 05/09/2006 $17.65 06/09/2006 $16.74 07/09/2006 $16.28 08/09/2006 $16.19 09/09/2006 $18.56 10/09/2006 $23.11 11/09/2006 $24.84 12/09/2006 $24.22 13/09/2006 $22.95 14/09/2006 $25.57 15/09/2006 $27.04 16/09/2006 $26.26 17/09/2006 $27.99 18/09/2006 $26.98 19/09/2006 $26.83 20/09/2006 $25.91 21/09/2006 $24.92 22/09/2006 $23.28 23/09/2006 $23.92 24/09/2006 $24.57 25/09/2006 $22.32 26/09/2006 $26.49 27/09/2006 $28.75 28/09/2006 $29.19 29/09/2006 $28.76 30/09/2006 $28.88 CP7 Caso prático resolvido: DESLOC (OFFSET) e CONT.NÚM (COUNT) Este caso prático resolvido está disponível no Express Training, a biblioteca online sobre Excel da Cavalcante. Visite expresstraining.com.br para encontrar este e outros exemplos! Mês Receita 01/2006 * 5,568.37 02/2006 * 6,357.08 03/2006 * 7,255.73 04/2006 * 8,029.54 05/2006 * 8,714.58 06/2006 * 9,255.14 07/2006 * 9,955.21 08/2006 * 10,804.46 09/2006 * 11,303.40 10/2006 * 12,381.71 11/2006 * 13,193.38 12/2006 * 14,381.60 Neste exemplo, desejamos que as informações preenchidas na tabela à esquerda sejam representadas em um gráfico. Contudo, gostaríamos que novas linhas preenchidas na tabela fossem adicionadas ao gráfico dinamicamente: para que isso ocorra, é necessário que as séries de dados no gráfico sejam definidas dinamicamente. 01/2007 * 14,769.38 02/2007 * 14,961.25 As áreas são definidas através dos nomes grafMes e grafReceita. Você pode visualizar a aplicação de DESLOC nestes nomes através do menu Inserir > Nome > Definir. CP7 5568.3668935325 6357.080684288 7255.729671975 8029.5383155148 8714.5763825248 9255.1376370657 9955.2143864991 10804.4638897418 11303.400572214 12381.7078861501 13193.3786901444 14381.5977199569 14769.3841730915 14961.2508018808 Dados
Compartilhar