Cronograma Especial

Caro seguidor (a);

Depois de algum tempo sem apresentar novos materiais no blog, um tempo surge para apresentar novas técnicas combinadas de funções que permitem simular painéis inteligentes.

Infelizmente não é possível apresentar todo o projeto porque é parte integrante de um grande projeto sendo desenvolvido para o governo do estado. Mesmo assim, apresentamos uma parcela que poderá fornecer subsídios para novos projetos.

Esta parte do projeto visa gerenciar localidades que foram programadas para visitas e aplicações de ações contínuas. Vamos aos detalhes do projeto.

No topo do painel, utilizamos uma barra de evolução que permite mostrar o quanto já foi realizado.

Para fazer a barra, usamos a função CONT.VALORES associada a uma FORMATAÇÃO CONDICIONAL.

Na área de intervalo de datas, temos um "cursor" que se movimenta de acordo com o dia atual.

A combinação de funções condicionais SE e E com funções de datas, DIA, MÊS e HOJE.

Logo abaixo, temos a localidade sendo visitada com sua respectiva ação.

O uso de uma linha de tempo é essencial para se adequar ao processo. Há um link para uma tabela auxiliar que permite este deslocamento importante. Usamos a fórmula SE(E(DIA(HOJE()>=E3);DIA(HOJE()<=F3);MÊS(F3)>=MÊS(HOJE()));1;"") para criar esta variável.

Nas datas de início e fim são aplicadas formatações condicionais para mostrar o cumprimento das datas.

As duas tabelas são imagens vinculadas da tabela auxiliar oculta.

O gráfico se baseia na tabela de evolução existente na tabela auxiliar.

Link do arquivo:

http://www.easy-share.com/1916146951/Cronograma.rar

Índice com funções

Feliz 2011 para todos!

Nos últimos dias andei pesquisando na internet sobre índices no Excel e não encontrei nada em nenhum lugar, nem no Brasil e nem no exterior. Então, resolvi enfrentar mais um desafio e inovar. Assim, desenvolvi um índice automático que deverá ajudar aos inúmeros profissionais que utilizam o Excel.

Sabemos que criar um índice no Word é fácil, no Excel é extremamente complexo, principalmente se estamos utilizando apenas funções e nenhum VBA.

Com o auxílio das funções Texto, &, Seerro, Indireto, Se, Lin, Hiperlink e duas noites sem dormir direito conseguimos desenvolver um índice funcionando perfeitamente.

Como funciona?

1. Você insere uma nova planilha (Shift F11) após a planilha 3.
2. Renomeia como 3 para dar seqüência ao índice.
3. Insere o título na célula A1 e automaticamente é mostrado no índice.
4. Selecione a célula da coluna E que contém o hiperlink e use CTRL K. Configure para o número seqüencial criado.

Se desejar remover a linha que se refere a planilha 3 é só deletar a planilha 3.

As fórmulas estão ocultas!

Se ainda tem dúvida, baixe o arquivo em:

http://www.easy-share.com/1913448803/%C3%8Dndice.rar

Contagem de palavras em textos

Para solucionar o problema de contagem de palavras no texto use o tutorial e planilha que ensina a usar função e ferramenta adequada para solucionar este problema. Baixe tudo no link abaixo:

http://www.easy-share.com/1912016427/Contador%20de%20palavras%20no%20texto.rar

Contagem de números únicos

Uma das grandes solicitações é determinar quantos números únicos dentro de um intervalo.

Disponibilizamos um planilha que demonstra a eficácia da pesquisa utilizando funções do Excel.

Cole o Link no navegador para baixar:

http://www.easy-share.com/1911968803/Contagem%20exclusivas.rar

Calcular somente valores com cores

Material inédito na internet e em primeira mão.

Use uma técnica especial para calcular valores com cores em uma tabela.

O uso do formato da célula + a função cél com opção formato é aplicada com êxito.

Baixe a planilha em:

http://www.easy-share.com/1911272895/Cálculo de valores por cores.rar

Link:

Gerador de senhas

Com a utilização das funções INT, ALEATÓRIO, SE, CARACT e a concatenação de textos com &, é possível desenvolver um gerador de senhas para 13 caracteres aleatórios.

Link:

http://www.easy-share.com/1910195729/Gerador de senhas.rar

Cálculo entre datas

A função DATADIF é essencial para calcular a diferença entre duas datas em uma variedade de diferentes intervalos de tempo, tais como o número de anos, meses ou dias entre as datas.

Na célula B5 contém a fórmula:

=DATADIF(B3;B4;"y")

Onde:

B3 refere-se a primeira data

B4 refere-se a segunda data

"y"apresenta o resultado em número de anos.

Se você substituir o "y" para "m" obtém o resultado em meses.

Se você substituir o "y" para "d" obtém o resultado em dias.

Gerenciando a produção no semestre

Qual o mês com a melhor produção no semestre?

Se você pretende gerenciar o mês com melhor índice de produção, então use a combinação de fórmulas conforme abaixo:

=SE(ÉCÉL.VAZIA(lin);"";ORDEM(B4;B:B)-1)+1

Link:

http://www.easy-share.com/1910100972/Produção no semestre.rar

Modelo de controle para gincana em colégio

Acompanhe a evolução dos estudantes durante a gincana com este controle. Um gráfico permite a melhor visualização.

Contém fórmulas especiais para determinar quem vence a gincana.

Link>

http://www.easy-share.com/1910022360/gincana.rar

A importância da tabela ASCII para o Excel

ASCII (acrônimo para American Standard Code for Information Interchange, que em português significa "Código Padrão Americano para o Intercâmbio de Informação") é uma codificação de caracteres de sete bits baseada no alfabeto inglês. Os códigos ASCII representam texto em computadores, equipamentos de comunicação, entre outros dispositivos que trabalham com texto. Desenvolvida a partir de 1960, grande parte das codificações de caracteres modernas a herdaram como base.

Fonte: Wikipédia

Para compreendermos a sua aplicação no Excel, precisamos conhecer a seguinte solicitação:

Qual fórmula devo usar para detectar o primeiro caracter digitado na célula e convertido para o número correspondente a ordem no alfabeto, ou seja, a = 1, z = 26. O resultado deverá ser colocado na coluna seguinte.

Ex: ATO-34567

Resultado= 1

Na primeira tentativa, usamos a fórmula:

=SE(B2="";"";PROCURAR(ESQUERDA(B2;1);"ABCDEFGHIJKLMNOPQRSTUVWXYZ";1))

B2 se refere a célula de entrada do código.

A primeira fórmula falha se por acaso alguém digitar ATO em minúsculas, ou qualquer caracter minúsculo.

Na segunda tentativa, usamos a fórmula:

=SE(B3="";"";CÓDIGO(ESQUERDA(MAIÚSCULA(B3);1))-64)

Independente se digitar em maiúscula ou minúscula, o resultado estará correto.

Link:

http://www.easy-share.com/1910016092/FÓRMULA PARA CÓDIGO.rar

Relatório Estatístico

Dicas para usar as funções:

• Cont.valores;
• Cont.se;
• Somase aninhado.

A validação de dados tipo lista aliada as funções acima proporciona um excelente desempenho.

Link:

http://www.easy-share.com/1909970611/Relatório Estatístico.rar

Modelo de Fatura

Um modelo especial de fatura que utiliza controles de formulário combinado com fórmulas para determinar o desconto na fatura.

Para sua coleção especial.

Link:

http://www.easy-share.com/1909968614/Fatura.rar

Determinando o mais produtivo

Um modelo especial que determina o colaborador mais produtivo.

Link:

http://www.easy-share.com/1909821288/Determinando o colaborador mais produtivo.rar

Fórmula gera automaticamente 8 dígitos

Acompanhe esta solicitação de um seguidor.

Tenho uma codificação fixa em itens, que vai de 01 até 43 o preço da mercadoria tambem é fixo, e o codigo que pretendo usar dentro do sistema tem que ter 8 digitos, pois cada código desses itens de 01 a 99 tem muitas mercadorias diferentes, onde o que as diferencia, são os preços...

O que eu quero? calcular o Código de 8 digitos automaticamente no Excel, pois
facilita muito e ainda adianta o serviço, pois tenho mais de 200.000 itens na casa!

Exemplo

Item Preço Cod Final Esperado
01 12,75 01001275

02 09,50 02000950

O cod final esperado teria que ser preenchido automaticamente em uma celula!

Fórmula aplicada:

=SE(TEXTO(A2;"00")&"00"&TEXTO(B2*100;"0000")="00000000";"";TEXTO(A2;"00")&"00"&TEXTO(B2*100;"0000"))

Planilha especial de funções

Planilha que demonstra a utilização das funções:

• Cont.se;
• Somase;
• Cont.ses;
• Soma com array

Link:

http://www.easy-share.com/1909748337/RELATÓRIO.rar

Fonte especial para gerar gráfico

Para criar o gráfico de barras semelhante ao da imagem acima usando apenas fontes, siga as etapas:

1. Digite na célula C4, =REPT("g";25*B4)
2. Arraste a fórmula para as células abaixo.
3. Selecione o intervalo C4:C8 e aplique a fonte Webdings.
4. Uma alternativa é substituir na fórmula a letra g por I (i em maiúsculo).

Link:

http://www.easy-share.com/1909730005/Gráfico de barras em fontes.rar

Folha de Horas-Extras - 1

Uma planilha especial preenchida que apresenta como aplicar as funções principais do Excel.

Funções utilizadas:

• Hoje
• Cont.valores
• Se (aninhado)
• Arrumar combinado com maiúscula
• Núm.caract
• Se
• Validação tipo Lista
• Soma
• Somase
• Média
• Proc combinado com Máximo

Uma técnica especial é demonstrada quando o usuário digita um nome completo com espaços a mais e o sistema arruma na célula, removendo os espaços e mostrando o nome corretamente.

Link:

http://www.easy-share.com/1909719997/Folha de Horas Extras 1.rar

Painel de reflexão e motivação

Quando estou em sala de aula com meus alunos, sempre digo a eles:

• Quando vocês enxergam o Excel somente como uma planilha, criam barreiras intransponíveis para a criatividade. Foi-se o tempo em que o Excel só servia para planilhas, hoje é um canivete suíço, com muitas utilidades.

Já demonstrei as inúmeras possibilidades que se pode fazer com o Excel. Você encontra neste blog alguns exemplos. Um deles é a Linha de tempo com imagens (Excelente material).

Abaixo, apresento outra.

Para quem trabalha com motivação nas empresas, deixo aqui uma dica interessante, um painel de mensagens aleatórias criadas no Excel 2007.

Abra o Excel e use uma pasta com duas planilhas. Na primeira, digite na célula A1 a fórmula:

=ALEATÓRIOENTRE(1;10)

Na segunda, digite:

A1 = Lista

Insira um autoforma que ocupe toda a área da janela. Clique na autoforma para selecioná-la e digite na barra de fórmula, =$B$1.

A2 até A11 = números sequenciais de 1 até 10

B2 até B11 = frases motivacionais ou de reflexão

Coloque uma pequena autoforma informando as pessoas para pressionar a tecla F9 (Recalcular).

Link:

http://www.easy-share.com/1909706192/REFLEXÕES E MOTIVAÇÕES.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

Controlando datas de vencimento das contas

Para aqueles que tem contas a vencer e tem dificuldade em administrar os pagamentos, elabore uma planilha de controle.

Baixe a planilha do link:

http://www.easy-share.com/1909680649/Controlando datas de vencimento.rar

Como foi elaborado:

• Em C2, a função =HOJE(). Baseia-se na data do seu computador. Caso o relógio do seu computador esteja com problema o sistema não vai funcionar;
• No intervalo C5:C21, formatação condicional (Conjunto de ícones) que utilizam as fórmulas para controlar as datas em relação ao dia atual.
• =HOJE()+5 para o ícone verde, vencimento em 5 dias;
• =HOJE()+2 para o ícone amarelo, vencimento em 2 dias;
• =HOJE() para o ícone vermelho, vencimento no dia atual;
• para o ícone preto, vencimento atrasado.

Formatação condicional para o intervalo E5:E21 com a regra específica que apresenta a cor da fonte vermelha para quando houver a palavra Não na célula.

Formatação condicional para o intervalo E5:E21 com a regra específica que apresenta a cor da fonte lilás para quando houver a palavra Sim na célula.

No intervalo E5:E21 contém uma validação tipo lista.

Observe dois detalhes nesta tabela;

1. Foi gerada para ser uma tabela. Isto quer dizer que no fim da tabela a direita, existe uma marcação em azul que permite ser arrastada para expandir a tabela. Antes disso, clique na linha 22 e insira uma nova linha;
2. A tabela está com filtro para facilitar a pesquisa e visualização de dados específicos.