Tutorial

Adquira o tutorial especial sobre Dashboard Desempenho de Vendas.

Adquira o pacote opção 3 em promoção e receba 10 tutoriais semelhantes a este.

Acompanha dashboard completo e pronto para ser utilizado.

Como criar TD a partir de várias planilhas.

Gerar tabelas dinâmicas (TDs) exige alto conhecimento, principalmente quando estamos trabalhando com várias planilhas ao mesmo tempo.

A dica essencial deste tutorial é o uso de uma ferramenta oculta no Excel 2007 e pouco utilizada pela maioria dos usuários de planilhas.

Aumente seu poder de fogo e aprenda a desenvolver tabelas dinâmicas a partir de várias planilhas.

Adquira o tutorial na promoção e faça parte de um mundo seleto de profissionais.

nbmagalhaes@gmail.com

Planilha expirada

Um tutorial especial que ensina passo a passo a criar planilha com data prevista para expirar e de quebra ensina também a proteger macros com senhas para não sofrer alterações indevidas.

Entre em contato, nbmagalhaes@gmail.com para adquirir este tutorial.

Dossiê Combobox

Dossiê especial sobre como funcionam os controles de formulários (Combobox).

Com este material você vai conhecer as formas ideais para aplicar os recursos. São 10 pastas conforme abaixo:

- Como usar os controles com funções especiais;

- Como usar os controles para cálculos;

- Como usar os controles combinados com formatações condicionais;

- Como usar os controles em formulários;

- Como usar os controles para tarefas específicas (comissão sobre vendas);

- Como usar os controles para gerenciar gráficos;

- Como usar os controles para gerenciar volumes de dados em interfaces reduzidas;

- Como usar os controles para desenvolver questionário de satisfação;

- Como usar os controles em cheklists.

Aproveite o preço de lançamento e adquira ainda hoje.

Para saber mais, contate nbmagalhaes@gmail.com

Tutorial 3D no Excel 2007

Tutorial 3D no Excel 2007 que ensina tela a tela a criar textos 3D, textos transparentes e como desenvolver Cubo 3D com imagens.

Solicite mais informações em:

nbmagalhaes@gmail.com

Tutorial especial em vídeo

Uma vídeo-aula sobre como montar gráficos especiais no Excel 2007.

Tutorial passo-a-passo.

Acompanha planilha exemplo.

Link:

http://www.easy-share.com/1909735083/Gráfico Especial - Vendas 01.rar

http://www.easy-share.com/1909735229/Gráfico Especial - Vendas 02.rar

http://www.easy-share.com/1909735266/Gráfico Especial - Vendas 03.rar

Excel Advance + (Tutorial)

Mais um suplemento especial do curso Excel Advance + com 10 planilhas e tutoriais.

Desta vez, enfocamos as técnicas do colar especial e suas aplicações.

Link:

http://www.easy-share.com/1909725725/F - Colar Especial.rar

Gráfico com classificação dinâmica

Tabela sem classificaçãoGráfico baseado em tabela não classificada
Tabela classificada
Gráfico classificado automaticamente

Se você tem uma tabela de dados, não classificada, com vínculo a gráficos e quer criar uma classificação dinâmica, siga as etapas:

1. Monte a tabela da imagem 1 a partir da célula B1;
2. Selecione os rótulos e os valores de jan;
3. Insira um gráfico de barras agrupadas conforme imagem 2;
4. Após a coluna de Jan, equivalente a linha de Fórmulas e Funções digite a fórmula =MENOR($C$2:$C$8;LIN()-1);
5. Arraste a fórmula até a linha equivalente ao tópico Word e Power Point;
6. Insira outro gráfico e use o novo intervalo de dados classificado.

Se desejar alterar a ordem de classificação, mude nas fórmulas:

• De = Menor
• Para = Maior

Link:

http://www.easy-share.com/1909704486/Informações classificadas.rar

Tutorial de Sistema em tabela

Um breve tutorial que ensina a criar um micro sistema com tabela, simulando um banco de dados. Também um formulário para incluir, alterar e excluir registros do banco de dados.

http://www.easy-share.com/1909698050/sistema.rar

Configuração Personalizada no Excel 2007

Um tutorial passo a passo, completo, sobre como criar a configuração personalizada no Excel 2007 em 10 planilhas.

Material inédito do curso especial Excel Advance +.

Link:

http://www.easy-share.com/1909695135/C - Configuração pesonalizada avançada.rar

Tutorial gráfico bullet

Um tutorial excelente que ensina passo a passo a desenvolver um gráfico para medir desempenho de vendedores, vendas, produção ou qualquer processo que precisa ser medido.

Acompanha planilha com material pronto.

Link:

http://www.easy-share.com/1909667669/Tutorial - Gráfico de Bala.rar

Validação de dados - Além dos limites

Na lista acima você vai inserir mais três clubes, o Botafogo, Guarani e Ponte Preta. Porém, limitar as células para evitar erros.

Selecione A13:A15 e acesse Dados\Ferramentas de Dados\Validação de dados.

Na aba Configurações

Permitir = Comprimento do texto

Dados = É menor ou igual a

Máximo = 15

Na aba Mensagem de entrada

Digite o nome de um clube de futebol

Na aba Alerta de erro

Célula limitada até 15 caracteres

Para testar a validação, digite em A13, paralelepípedo e veja que uma mensagem é apresentada alertando sobre a limitação de caracteres.

Nota:

Digite paralelepípedo na célula D13. Copie o conteúdo da célula D13 e cole em A13.

Veja que há brechas em que o Excel 2007 não consegue impedir. O exercício somente limita com a digitação direta na célula. Se for colado na célula, rompe a limitação.

Selecione B13:B15 e crie uma validação que permita até 2 caracteres.

De I1 até I15, digite a sequencia dos valores de 1 a 15.

Em D1, digite o texto: Quantas opções serão mostradas

Em E1, uma validação tipo Lista. Na fonte digite: =$I$1:$I$15

Escolha logo um valor entre 1 e 12.

Na célula D3 com o botão direito do mouse escolha a opção Nomear intervalo.

Nome: clubesdinâmicos

Refere-se a: =DESLOC(Plan1!$A$2;0;0;Plan1!$E$1;1)

Ainda na célula D3, crie uma validação tipo Lista e na fonte:

=clubesdinâmicos

Use a validação em D3 e observe a quantidade de opções de acordo com a validação em E1.

Altere a quantidade na validação em E1 para 15.

Visualize as opções em D3 que termina com o Vasco.

Acrescente os três times após o Vasco: Botafogo, Guarani e Ponte Preta.

Visualize novamente as opções em D3 e verá os novos clubes incluídos.

Link do arquivo:

http://www.easy-share.com/1909662768/VALID.rar

Determinando os três maiores corretores - Proc, Maior e Procv

O exercício tem a seguinte proposta:

- Determinar os 3 corretores que mais venderam no semestre.

A coluna que define quem vendeu mais é justamente o total. Porém, precisamos apresentar o nome do corretor e seu respectivo número de vendas.

Clique na célula A2 e digite:

=PROC(MAIOR(H8:H16;1);H8:H16;A8:A16)

Onde:

A função Maior deverá capturar dentro do intervalo H*:H16, o maior valor, determinado por 1.

A função Proc procura o maior valor dentro do intervalo H8:H16 e retorna o equivalente no intervalo especificado em A8:A16.

Para determinar o segundo e terceiro, transforme os endereços em absolutos e arraste-os para baixo. Substitua a posição de 1 para 2 e posteriormente para 3.

Confira as fórmulas do segundo e terceiro.

=PROC(MAIOR($H$8:$H$16;2);$H$8:$H$16;$A$8:$A$16)

=PROC(MAIOR($H$8:$H$16;3);$H$8:$H$16;$A$8:$A$16)

Bem, o resultado ainda não ficou como esperado, devido a função Maior exigir que a coluna com os valores que a função vai determinar quem seja o maior, esteja classificada em ordem crescente, ou seja, do menor valor para o maior valor do total.

Selecione a tabela e acesse Dados\Classificar e Filtrar\Classificar.

Verifique se a opção Meus dados contem cabeçalhos esteja marcada.

Em Classificada por escolha Total. Ok!

Pronto, o resultado deverá apresentar os 3 primeiros corretores do semestre.

Para apresentar os valores equivalentes de cada corretor, use o procv conforme abaixo:

=PROCV(A2;$A$8:$H$16;8;0)

Arraste a fórmula para as duas linhas abaixo.

Se desejar o oposto, ou seja, os 3 últimos corretores, substitua a função Maior por Menor.

Link:

http://www.easy-share.com/1909665632/maiores corretores.rar

Autoformas

Dê aspectos profissionais as suas planilhas, use os recursos especiais das autoformas e smartarts.

Se você compartilha suas planilhas, desenvolva ambiente que agilize processos e/ou facilite os "marinheiros de primeira viagem".

Vamos a simulação.

Se você tem uma pasta com duas planilhas, Relatório e Contas à Pagar, acrescente uma terceira e posicione-a como sendo a primeira. Renomear a guia para Início.

Acesse Exibição\Mostrar/Ocultar e desmarque Linhas de Grade.

Acesse Inserir\Ilustrações\SmartArt. Em Tudo, escolha a opção Lista vertical em setas, Ok.

Dê um clique duplo sobre a borda do objeto para acessar a guia Design.

Na faixa de opção Estilos de SmartArt, clique em Alterar cores. Entre em colorido e escolha a quarta opção (Faixa colorida, cores de ênfase 4 a 5).

Ao lado de Alterar cores, escolha um estilo visual clicando em Mais na seta para baixo.

Opte por Cena com vista aérea.

Clique em Texto sobre o primeiro retângulo de cantos arredondados e digite Relatório.

No segundo retângulo, digite Contas à Pagar.

Na primeira área de texto da seta, digite Dez/2009. Apague o segundo tópico.

Na segunda área, digite Jan/2010. Apague o segundo tópico.

Crie hiperlinks.

Acesse Inserir\ilustrações\Formas. Escolha elipse. Desenhe um tamanho razoável para colocar na ponta da seta. Com a autoforma selecionada, escolha o estilo de acordo com a cor do retângulo. Arraste-o para a ponta da seta. Com a elipse selecionada aplique Ctrl K para acessar a caixa de diálogo Inserir hiperlinks.

Em vincular, escolha Colocar neste documento. Clique sobre Relatório, Ok.

Repita o processo para contas á pagar.

Faça um teste e clique na elipse com hiperlink do relatório e veja que será deslocado para a planilha Relatório.

Insira uma autoforma qualquer e aplique outro hiperlink direcionado para a guia Início. Assim, é possível retornar a tela inicial.

Limite a navegação somente para pequenas áreas.

Selecione as colunas após o objeto SmartArt e oculte.

Selecione as linhas abaixo do SmartArt e oculte.

Dica: Se for arrastar os objetos, faça assim. Clique no primeiro e pressione Ctrl. Clique no segundo e terceiro para selecioná-los. Agora pode arrastá-los.

Acesse Exibição\Modos de Exibição de Pasta de Trabalho\Tela inteira.

Link do arquivo:

http://cid-e62e8ab2ef27e054.skydrive.live.com/browse.aspx/.Public?uc=1

Formatação Condicional na Escala de trabalho

Uma tarefa que parece simples, às vezes, pode ser complicado, por exemplo, montar uma escala de trabalho que não pode ultrapassar 40hs semanais por empregado e todo dia deve ter uma jornada de trabalho com 40hs.

Devemos usar critérios para determinar que as jornadas diárias não ultrapassem 8 hs diárias e jornadas diárias que sejam menores que 8hs e maiores que zero devem ser ressaltadas.

Para apresentar as condições acima mencionadas, precisamos usar os recursos da formatação condicional.

Selecione o intervalo B2:H8. Acesse a guia Início\Estilo\Formatação Condicional.

Em nova regra, escolha Formatar apenas células que contenham, monte a regra com:

Valor da célula é maior do que 8. Clique no botão Formatar com cor da fonte (branca) e preenchimento azul.

Acrescente uma nova regra e em Usar uma fórmula para determinar quais células devem ser formatadas.

Digite a fórmula:

Formatar com com da fonte (branca) e preenchimento vermelho.

Devemos usar a formatação condicional para informar quando a jornada semanal ultrapassar 40hs. Selecione I2:I8 e aplique a regra Formatar apenas células que contenham (Valor da célula é maior do que 40). Formate com fonte branca e preenchimento verde.

Use a mesma formatação condicional anterior no intervalo B9:H9.

Você pode melhorar as regras, utilizando todo o potencial da formatação condicional do Excel 2007.

Aproveite e adquira o curso completo sobre formatação condicional no Excel 2007 que acompanha um dashboard especial. São quase 12Mb de tutoriais.

Envie um email para: nbmagalhaes@gmail.com

Gráfico especial

Abrir uma pasta no Excel e renomear a guia Plan1 para GD.

Digite nas células:

A8 = Dias

A9 = 1

A10 = 2

A11 = 3

A12 = 4

A13 = 5

B8 = Vendas

B9 = 8

B10 = 14

B11 = 21

B12 = 27

B13 = 33

Na célula D8, digite Dias para mostrar.

Na célula E8, criar uma validação tipo Lista com a fonte em $A$9:$A$25

Escolha o valor máximo, 5, na caixa de listagem.

Selecione o intervalo B19:B13 e clique em Inserir\Gráficos\Colunas\Colunas Agrupadas 2d.

Bom, até aqui tudo tradicional, então vamos dinamizar o gráfico. Salvar como Gráfico Dinâmico.

Clique em Fómulas\Nomes Definidos\Definir Nomes.

Nome: Dias

Refere-se a: =DESLOC(GD!$A$9;0;0;GD!$E$8;1)

Clique em Fómulas\Nomes Definidos\Definir Nomes.

Nome: Vendas

Refere-se a: =DESLOC(GD!$B$9;0;0;GD!$E$8;1)

Clique sobre o gráfico e com o botão direito do mouse escolha a opção Selecionar dados.

Na caixa de diálogo, Selecionar Fonte de dados. Escolha a única série existente e clique no botão Editar. Em valores da série, substitua por: ='Gráfico Dinâmico.xlsx'!Vendas

Saindo da edição do gráfico, digite em A14, 6. Em B14, 50.

Verifique na caixa de listagem que agora existe a opção 6. Escolha e veja o gráfico.

Orçamento especial

A situação é a seguinte, um cliente recebeu uma planilha com orçamento por email sobre a composição de um computador. Porém, a informação estava formatada com o nome de cada peça seguido do símbolo de igualdade e do valor, tudo na mesma coluna. E ele queria capturar o valor para gerar uma proposta com cálculos.

Analisando a situação e sabendo que periodicamente ele receberá esta informação atualizada, foi montado uma estrutura padrão para gerar sua proposta.

Capturando o valor em B7, passo a passo:

1 - Calcular o número de caracteres na célula usando a função: =NÚM.CARACT(B7)

Armazenar esta informação na célula E7.

2 - Localizar o símbolo de igualdade no texto: =PROCURAR("=";B7)

Armazenar esta informação na célula F7.

3 - Extrair somente os valores do texto, usando o recurso da função Direita:

=DIREITA(B7;(E7-F7)-1)

Armazenar esta informação na célula G7.

Obs: O resultado é apresentado em texto.

4 - Converter o texto em valor:

=VALOR(G7)

Armazenar esta informação na célula G7 e formatar como contábil.

Pronto! Você já sabe quais funções que deve usar, então faça um aninhamento de funções e transforme em um fórmula.

=VALOR(DIREITA(B7;(NÚM.CARACT(B7)-PROCURAR("=";B7)-1)))

Na célula C2 preencha com a quantidade necessária.

Na célula C3 preencha com: =SOMA(C7:C15)

Na célula C4 preencha com: =C2*SOMA(H7:H15)

Baixe o exemplo no link:

http://cid-e62e8ab2ef27e054.skydrive.live.com/browse.aspx/.Public?uc=1

Verificando a existência de duplicidade

Esta é uma pequena variação do modelo já apresentado. Acrescentamos a precisão da informação.

Para verificar a existência de duplicidade dentro de um intervalo de dados, faça:

1. Siga o caminho: Início\Estilo\Formatação Condicional.
2. Nova Regra\Usar uma fórmula para determinar quais células devem ser formatadas.
3. Digite a fórmula: =CONT.SE($A$6:$A$65536;A6)>$A$6
4. Clique no botão Formatar. Na aba Fonte. Cor Vermelho. OK e feche.

Se você deseja ser mais preciso, então digite os valores nas células correspondentes:

D1 = 2;

D2 = 3;

D3 = 4.

Na célula A1 digite, Nº de repetições.

Clique na célula A2. Siga o caminho:

Dados\Ferramentas de Dados\Validação.

Acesse a aba configurações. Permitir, escolha lista. Na fonte, digite: =$D$1:$D$3.

Acesse novamente: Início\Estilo\Formatação Condicional. Gerenciar Regras. Mostrar regras de formatação para: Esta Planilha.

Clique no botão Editar Regra e altere .

De:

=CONT.SE($A$6:$A$65536;A6)>$A$6

Para:

=CONT.SE($A$6:$A$65536;A6)=$A$2.

Escolha o número de repetições com maior precisão, 2, 3 ou 4.

Baixe o exemplo no link:

http://cid-e62e8ab2ef27e054.skydrive.live.com/browse.aspx/.Public?uc=1

Curso completo sobre formatação condicional

A formatação condicional permite mostrar duplicidade de informações na tabela.

Selecione A5:A10 e acesse a guia Início\Estilo\Formatação Condicional.

Nova Regra\Usar uma fórmula para determinar quais células devem ser formatadas.

Digite:

=CONT.SE($B$5:$B$100;B5)>1

Clique no botão formatar. Na aba Fonte, Estilo da Fonte = Negrito, Cor = Vermelho. OK.

Observe que o intervalo é estendido até a célula B100, então, se você digitar um código repetido no intervalo B5:B100, instantaneamente a célula será formatada com negrito e cor da fonte vermelha.

Adquira o curso completo sobre formatação condicional no Excel 2007. São quase 12MB de tutoriais, explicando passo a passo como utilizar estes novos recursos. São inúmeras situações possíveis.

Entre em contato:

nbmagalhaes@gmail.com

Formulário

Um dos recursos, oriundos de versões anteriores do Excel que ainda tem muito valor é o Formulário. Para conhecê-lo, precisamos antes de uma tabela simples. Vamos criar uma pequena agenda.

Digite:

A1 = Nome

B1 = Endereço

C1 = Telefone

Como temos os campos pré-definidos, precisamos agora, cadastrar registros, mas, onde está o formulário?

Clique em personalizar Barra de ferramentas de acesso rápido (a seta para baixo mostrada na imagem acima). Escolha a opção Mais Comandos.

Na caixa de diálogo Opções do Excel\Personalizar.

Escolher comandos em: Todos os comandos

Use a barra de rolamento para localizar Formulário no Separador. Para ir mais rápido, dentro do separador, tecle a letra F. Assim que encontrar Formulário, deixe selecionada a opção. Clique no botão Adicionar e pressione Ok.

Voltando para a tabela, selecione A2 e clique no botão Formulário, localizado na barra de Acesso Rápido.

Uma mensagem é apresentada porque não há nenhum registro. Clique em Ok para prosseguir.

Fique a vontade para cadastrar em sua agenda.

Para pular do campo Nome para endereço, use o tabulador (tecla acima de CAPS LOCK). Não use ENTER. Quando preencher todos os campos, clique no botão Novo para novo cadastro.

Se desejar encerrar, tecle ESC ou clique no botão Fechar.

Em breve estarei postando um tutorial completo sobre como criar um cadastro de colaboradores.