terça-feira, 30 de dezembro de 2008

50

quinta-feira, 25 de dezembro de 2008

Feliz Natal

Para os meus 20 leitores e os meus 6 bilhões de não leitores:

sexta-feira, 19 de dezembro de 2008

Youtube é um bom companheiro

Básico sobre referências em Excel.

Banco de Dados

Excelente aula sobre a função banco de dados do Excel.
Quem nunca usou esta função, vale a pena conferir.

quarta-feira, 10 de dezembro de 2008

Fim de ano furacão

Vamos ver se eu consigo chegar em 50 posts em 2008!

Com posts assim, fica fácil! :)

terça-feira, 2 de dezembro de 2008

Wishlist


Sempre tive diversas sugestões para turbinar o Excel.
Mas nunca tive tempo para compilar e enviar para a Microsoft.

Agora chegou o momento.
Vou fazer uma lista de desejos para a próxima versão do Excel.

Quem quiser colaborar é só incluir a sugestão no comentário deste post.
Quando a lista estiver gorda, solto uma compilação.

sábado, 29 de novembro de 2008

Dependentes e diversão

Algumas vezes é muito difícil auditar a planilha de outra pessoa, especialmente quando há muitas contas e planilhas correlacionadas.

Para ajudar nessa tarefa (e em outras tantas situações), o Excel tem um modo de rastrear dependentes e precedentes visualmente.



É um recurso muito útil, mas tem gente que fica brincando com isso... Veja o resultado das brincadeiras:



Só que o mais incrível mesmo é fazer isso no Paint...

domingo, 23 de novembro de 2008

Criar macro

Vídeo muito básico, mas quem nunca criou uma macro verá que não é um bicho de sete cabeças. Peguei no Youtube. Vocês podem encontrar uns mais profundos lá.

terça-feira, 18 de novembro de 2008

Para divulgar

Quem entrou, gostou e quer divulgar pode usar este banner:

Macro sem botão

"Tem como ao digitar ou escolher na lista de opçõe de uma célula um número, uma macro ser executada?" [sic].

A pergunta é boa.

Para resolver isso, crie na sua pasta em VBA Project:

Por exemplo, ao digitar "5" na célula "E5" ele roda a macro "teste" (que está no módulo 1).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Cells(5, 5) = 5 Then teste
End Sub

terça-feira, 11 de novembro de 2008

MsgBox & InputBox

Para quem quer comunicar algo por caixa de texto: msgbox.

Para quem quer pegar informação por caixa de texto: inputbox.

Exemplo:

Sub meu_time()
nome = InputBox("digite o nome do seu time")
If nome = "Flamengo" Then
MsgBox ("Parabéns!!!")
Else
MsgBox ("Coitado...")
End If
End Sub

Leitura complementar em bugsa

quinta-feira, 6 de novembro de 2008

Estrutura de Tópicos

Para fazer uma estrutura de tópicos (facilita na apresentação dos dados em relatórios), selecione "Dados".
No canto direito você encontrará o menu para a Estrutura de tópicos.

Basta selecionar as linhas que serão agrupadas e clicar em "Agrupar".
Veja o exemplo abaixo:


Também serve para colunas.
Repare que você pode criar grupos dentro de grupos.

Para recolher os tópicos (agrupar), basta clicar no número que está fora da área de células da planilha (canto superior esquerdo).

Dica:
atalho para agrupar = shift + alt + seta para a direita
atalho para desagrupar = shift + alt + seta para a esquerda

terça-feira, 4 de novembro de 2008

Já vai...

Calma, pessoal!

A vida está um pouco corrida, mas em breve volto a escrever por aqui.

Também falta um pouco de estímulo da votação...
Muitos passaram por aqui, mas poucos votaram.

quinta-feira, 30 de outubro de 2008

Formatação condicional por fórmula

Para a primeira pergunta:

"excel formatação condicional colorir outra coluna"
Uberaba - Minas Gerais

Vamos lá...




Repare que está travada apenas a coluna. Assim, quando eu puxar a formatação para as outras células, o Excel entende que a formatação condicional varia de acordo com a linha.


quarta-feira, 29 de outubro de 2008

formação ou formatação?

Não tinha percebido anteriormente, mas "você pode utilizar a formação condicional" também é para o bugsa...

Obrigado, Google!

terça-feira, 28 de outubro de 2008

Dúvidas

Tenho visto algumas dúvidas através das pesquisas no Google (que apontam para este blog).

Exemplos:

"como descobrir a altura de uma coluna com macro no excel"
Uberaba - Minas Gerais

"como ocultas as fórmulas no excel?" (o Google corrigiu: Você quis dizer: como ocultar as fórmulas no excel? )
Rio de Janeiro - Rio de Janeiro

"excel formatação condicional colorir outra coluna"
Uberaba - Minas Gerais

Já tenho material novas dicas...

Quem preferir pode enviar as dúvidas para "excelsecrets@gmail.com".
Na medida do possível (e do conhecimento) vou respondendo neste blog.

Podem enviar também sugestões, correções, críticas...

Decepção

Hoje veio a dúvida: será que há algum problema em ter um blog com este conteúdo?

Parece que não. Existem inúmeros blogs sobre Excel. Estou procurando divulgar o Excel, sem custo para a Microsoft.

Bom, mesmo assim procurei conversar com a Microsoft para eliminar a dúvida.

Depois de vasculhar um pouco o site da MS descubro um link "Ajude-nos a melhorar o Excel 2007". Deve ser aí!

Veja a página que encontro:

Give Us Feedback for Microsoft Office 2007

Thank you for taking the time to give us your feedback. We appreciate your feedback and use it to help create better services and products. Unfortunately, we cannot provide a personal response to your comments. For assistance with a specific problem you are experiencing with this product, contact support.

"Unfortunately, we cannot provide a personal response to your comments" - minha leitura: 'Se estiver em português, o seu comentário vai direto para a lixeira'.

Além de estar em inglês (ou seja, 'tô nem aí pros tupiniquins') ainda me limita a "Please type your feedback here: (1000 character limit)"

Ok. Poderia ter ido por outro caminho para manifestar a minha dúvida, mas fiquei decepcionado com o tratamento - pela segunda vez (a primeira foi um atendente da MS me dizendo que um bug do word era um problema da minha máquina...) - que dou a minha dúvida como resolvida: não há problema.

Desculpem-me pelo desabafo...

Quebra de linha


Para pular de linha dentro de uma célula: alt+enter

sábado, 25 de outubro de 2008

Obter valor de texto

Tenho uma lista que mistura números com texto e quero fazer uma soma dos números.

Todos sabemos que o Excel oferece diversos modos para resolver um problema.

Veja uma solução:



Na coluna "B" tenho o texto com números.

Na coluna "C" usei a fórmula "=ESQUERDA(B3;2)" para aparecer só os números. Porém repare que a fórmula está fixa para mostrar apenas os dois primeiros algarismos da esquerda. Além disso, o que aparece na coluna "C" ainda é interpretado como texto.

Na coluna "D" resolvi o problema de interpretação como valor, com a fórmula: "=VALOR(ESQUERDA(B3;2))"

Na coluna "E" resolvi todos os problemas com: "=VALOR(ESQUERDA(B3;NÚM.CARACT(B3)-6))"

E se não for tudo com "Reais"? Fica para um outro dia...

sexta-feira, 24 de outubro de 2008

Generosidade

No help do Excel podemos encontrar:

"Formatação condicional rica

No Versão do 2007 Office, você pode utilizar a formação condicional para anotar visualmente seus dados para fins de análise e apresentação. Para localizar com facilidade as exceções e destacar tendências importantes nos seus dados, você pode implementar e gerenciar várias regras de formatação condicional que aplicam a formatação visual rica em forma de cores de gradiente, barras de dados, conjuntos de ícones a dados que atendem a essas regras. Os formatos condicionais também são fáceis de serem aplicados — com apenas alguns cliques, você pode ver relações nos seus dados que podem ser usados para fins de análise."

É claro que "No Versão" é algo digno para outro blog.
Fora isso, o Excel finalmente saiu da sovinice de apenas 3 restrições para formatação condicional (sem ter que apelar para macro).

Parabéns Bill Gates! E sucesso com a bgC3!

quarta-feira, 22 de outubro de 2008

Votação até o momento

Muito bem, pessoal! Vamos continuar votando...

Pelo menos este blog está salvando a vida de 7 pessoas!

Zoom na Seleção

Pare de gastar tempo formatando o relatório para caber em uma tela.

Se o seu monitor for maior que o do seu chefe, já era...

Use a seguinte macro para sempre acertar:


Sub Z()

Range("A1:P40").Select 'Intervalo de células do seu relatório

ActiveWindow.Zoom = True

End Sub

domingo, 19 de outubro de 2008

Mais atalhos

No "F1" do Excel, com o texto "atalho" você encontra muito mais...

CTRL+9 >> Oculta as linhas selecionadas.
CTRL+0 >> Oculta as colunas selecionadas.

CTRL+SHIFT+( >> Exibe novamente as linhas ocultas dentro da seleção.
CTRL+SHIFT+) >> Exibe novamente as colunas ocultas dentro da seleção.

Atalho para dependente

Outro dia uma pessoa me perguntou: "Tem um atalho para ir ao dependente?"

Para dependentes na mesma aba: F5 + (Alt+E) + N (para Excel 2007 em português)

Ou, se preferir, com uma macro básica (e associar uma tecla de atalho):

Sub D1()
Selection.Dependents.Select
End Sub


ou (para dependente direto)...

Sub D2()
Selection.DirectDependents.Select
End Sub


Para dependentes em outras planilhas também é possível mas acho que o crime não compensa...

sexta-feira, 17 de outubro de 2008

O tempo ruge

Como dizia um amigo meu: "O tempo ruge"!

Pessoal, está acabando o tempo para a votação na Pesquisa Rápida...

quinta-feira, 16 de outubro de 2008

Resposta

Interessante o brinquedinho de ver como as pessoas chegam ao seu site.

Outro dia encontrei uma pessoa que escreveu no google:
"o'que é excel ? qual sua função? e pra que serve? nao e como usar usar eu sei."

Se você sabe usar, talvez não seja tão difícil definir o que é Excel e qual é a sua função...

segunda-feira, 13 de outubro de 2008

Aleatório

"A semente do aleatório do Excel é fraca". Frase já espalhada por aí. Eu mesmo tinha essa visão e resolvi pesquisar.

A página de suporte da MS explica (em português de Portugal) que: "Em versões anteriores do Excel, a função ALEATÓRIO utilizava um algoritmo de geração de números pseudo-aleatórios, cujo desempenho em testes padrão de aleatoriedade não foi suficiente."

Só para os simples mortais isso não era um grande problema: "Apesar desta situação só afectar provavelmente os utilizadores que efectuam um grande número de chamadas a ALEATÓRIO (tal como um milhão ou mais) e não ser motivo de preocupação para a maior parte dos utilizadores"

Mesmo assim eles melhoraram a função Aleatório(): "Resultados no Excel 2003: Foi implementado um algoritmo simples e eficaz. O novo gerador passa todos os testes padrão de aleatoriedade."

sexta-feira, 10 de outubro de 2008

Contas com "texto"

Ainda não é para pagar a dívida, mas serve por enquanto...

Veja o exemplo abaixo:

Estou calculando um aumento usando como base as linhas 3 e 5. No primeiro caso não é possível calcular, mas no segundo, sim.

Repare que, na linha 3, "Crescimento de 5% a.a." é um texto, enquanto que na linha 5 ele é um valor disfarçado de texto.


Ele é 5%, com a formatação personalizada:

Entre em "formatar células" (ctrl+1)

Escolha tipo "personalizado"

E faça o formato de número que você desejar:

quinta-feira, 9 de outubro de 2008

Seja bem-vindo

Vejam quem veio me visitar:

quarta-feira, 8 de outubro de 2008

Fogo amigo


Um blog amigo (ou que deveria ser) insiste em ataques gratuitos...

Como não estou mais na idade de ter que provar que sei algo (ok, menos para o meu chefe), faço votos para que ele seja feliz com o python.

Interessante

Alguém de Contagem (MG) escreveu no google: "algo interessante sobre excel".

Para minha surpresa, o primeiro da lista de resultados foi o excelgredo... E ele resolveu nos visitar.

Espero que tenha encontrado algo interessante por aqui.

Dar nome aos bois

O que é mais fácil de ler:

=2*usd ou = 2*F2 ?

Na primeira fórmula eu sei que é 2 vezes o dólar. Na segunda tenho que procurar quem é esse tal de F2...

No exemplo abaixo veremos como dar nome aos bois:

1) Faça a sua planilha com os valores que precisam de nome


2) Selecione a célula com o valor e no campo (superior esquerdo) em que estava escrito "F2", digite "USD".

3) Pronto para usar. O Excel entende que a célula chamada "USD" é a F2.

4) Para apagar esse nome ou mesmo incluir outros, basta selecionar o "Gerenciador de Nomes":


Você pode ainda incluir um comentário para o nome que você definiu. Fica como dever de casa!

terça-feira, 7 de outubro de 2008

Soma 3D


Tenho várias abas com o mesmo modelo e desejo ter na primeira aba os valores consolidados.

Posso fazer:
=F9+Plan2!F9+Plan3!F8+Plan4!F8+Plan5!F8+Plan6!F8+Plan7!F8+Plan8!F8+Plan9!F8

Ou simplesmente:
=SOMA(Plan1:Plan9!F8)

domingo, 5 de outubro de 2008

Excel leitor

Quando você tem uma tabela muito grande e precisa conferir se foi tudo digitado de acordo com uma tabela em papel, o sistema de leitura do Excel pode facilitar a vida.

O comando de leitura fica fora da faixa de opções, por isso é preciso que seja habilitado em "opções do Excel".


Agora é só usar.


Cuidado para não perder tempo com brincadeiras, pois é um comando que facilmente pode escorregar para esse lado...

sábado, 4 de outubro de 2008

colar apenas formatos e fórmulas excel

Algum amigo de Leiria, Portugal, estava procurando no google "colar apenas formatos e fórmulas excel" e por isso visitou este blog (apareceu em segundo na lista do google!!!).

Caso nosso amigo volte por aqui...

Excel 2007:
Colar formatos: alt + c + v + s + s
Colar fórmulas: alt + c + v + f

Versões anteriores:
Colar formatos: alt + e + a + s
Colar fórmulas: alt + e + a + f

Voto

Amanhã é dia de votar...

Não esqueça de votar também na pesquisa rápida aqui do site!

Validação plus

Leia até o fim...

Aposto que muita gente já sabe criar uma lista suspensa a partir de um intervalo de células.

Exemplo

Temos uma relação de países e capitais:

Basta escolher validação de dados:


Depois selecionar "Permitir Lista" e escolher o intervalo (no exemplo os dados estão entre A2 e A201):


Pronto! Com um "procv" podemos fazer algo interessante. Você escolhe o país e a célula ao lado apresenta a capital.



Porém o que muitos não sabem é como fazer uma lista que se ajuste automaticamente ao tamanho da tabela de dados...

As funções "desloc" e "corresp" podem ajudar. Veja abaixo e tente fazer.


OBS: Para entender as funções citadas, basta ver o help do Excel (F1).

sexta-feira, 3 de outubro de 2008

Crise passageira II

Uma semana fora por conta do orçamento dos próximos três anos da empresa. Tudo em Excel! Espero voltar a escrever em breve aqui.

sábado, 27 de setembro de 2008

Crise passageira

"do velho para o novo"

Verifiquei hoje e voltou a funcionar no Mozilla Firefox!

quarta-feira, 24 de setembro de 2008

Largue o mouse

Para quem quer ser mais rápido no Excel, precisa aprender a largar o mouse e usar mais os atalhos.
OK... Desta vez a Microsoft vacilou e trocou diversas combinações de atalho na versão 2007.

Mas vale a pena aprender novamente. Aos poucos vou publicando alguns atalhos aqui.

ctrl + barra de espaço >> seleciona coluna inteira
shift + barra de espaço >> seleciona linha inteira

alt + c + v + v >> colar especial valores (2007)
alt + e + a + v >> colar especial valores (versões anteriores)

Placeholder

Esta semana vai ser tsunamiana para mim!
Portanto fica aqui um placeholder para o assunto: "Somar valores com texto".
Pode ser muito útil para quem quer economizar colunas.

Bem que o José Antonio poderia me ajudar e publicar algo por aqui...

segunda-feira, 22 de setembro de 2008

Delete não deleta...

Uma vez José Antonio me disse:

"Eu tenho um arquivo xls com várias planilhas que eu vou atualizando mensalmente.
Algumas vezes tenho que apagar várias linhas da tabela, mas reparei que o tamanho do arquivo só aumenta.
Pesquisei na Internet e vi que se eu excluir a linha inteira o Excel libera aquele espaço do arquivo. Se só apagar os valores, ele não libera o espaço."

Boa dica!

domingo, 21 de setembro de 2008

Remover Duplicatas

Para quem quer fazer um resumo de dados sem usar tabela dinâmica, a ferramenta remover duplicatas pode ser muito útil.

Por exemplo, tenho uma lista de pessoas com seus gastos e desejo saber quanto cada pessoa gastou no total.


Separe os nomes em uma outra coluna. No menu Dados você encontrará "Remover Duplicatas".


A coluna H, após aplicar a ferramenta, terá apenas os nomes das pessoas sem repetição:


Agora ficou fácil. Basta usar a fórmula somase que o seu trabalho terminou.

sábado, 20 de setembro de 2008

Você sabia?

Você sabia que o Excel 2007 tem dezessete bilhões, cento e setenta e nove milhões, oitocentos e sessenta e nove mil e cento e oitenta e quatro células em cada aba?

Isso porque agora são 2^20 linhas com 2^14 colunas!

Exagero? Veremos... Na versão antiga já era possível uma expansão do tamanho das abas (mais linhas e colunas). E tinha gente que usava...

Macro ReEnter

Outro dia estava caçando uma macro do Excel para fazer o seguinte procedimento para cada célula de um Range:

1) Entrar na célula
2) Abri-la para edição
3) Fechá-la para edição

Esse processo faz o Excel recalcular fórmulas e aplicar os formatos nas células. Isto é muito útil quando se cola muito texto no excel e ele não aplica os formatos pré-definidos nas células e acha que tudo é texto.

Segue abaixo a macro. Você pode encontrar essa e outras macros nessa página.
Sub ReEnter()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'pre XL97 xlManual
Dim cell As Range
Dim ix as Long
tCells = Selection.Count

For ix = 1 To tCells
Selection.Item(ix).Formula = Trim(Selection.Item(ix).Formula)
Next ix

Application.Calculation = xlCalculationAutomatic 'pre XL97 xlAutomatic
Application.ScreenUpdating = True
End Sub

Copiando uma fórmula com o mouse

Talvez essa seja a operação mais usada no Excel. Você tem uma célula com uma fórmula e precisa repeti-la mais vezes.

Essa operação pode ser feita puxando o quadrado preto que fica embaixo de cada célula quando ela está selecionada. Isto serve não só para fórmulas, mas para seqüências de números também. Você coloca dois ou três números da sua seqüência, que o Excel preenche o resto para você. Se ele não descobrir a seqüência, ele repete os números N vezes para preencher a seleção.

Na imagem abaixo, fiz o exemplo mais simples de seqüência.



Tendo uma seqüência de dados, por exemplo é possível definir uma fórmula e replicá-la para toda seqüência usando o mesmo quadrado preto no canto inferior direito da célula:




Note que a fórmula foi mudando de acordo com a célula. Caso se queira fixar uma célula na fórmula, coloque "$" antes da coluna e/ou da linha:




Dessa forma, onde houver um "$" a coluna ou linha não será alterada ao se arrastar a fórmula.

Repetir operação

Caso típico:
Estou percorrendo uma lista e quero colorir de amarelo as células com um determinado valor (quando não posso usar formatação condicional).
Selecionei a primeira célula e, com o mouse, cliquei na ferramenta de preenchimento amarelo.
Vou com a seta para baixo percorrendo a minha lista e encontro uma nova célula a ser preenchida de amarelo. F4 resolve!
Essa tecla repete a última operação realizada (que foi colorir a célula).
Só para lembrar: F4 = ctrl + Y.

sexta-feira, 19 de setembro de 2008

Apresentação de Relatórios

Você não precisa mais gastar tanto tempo, ajustando a largura das colunas e a altura das linhas, para fazer caber em seu relatório todos os dados de forma ordenada.
Com a ferramenta "colar vínculo da imagem" (alt, c, v, a, i), você tem uma imagem que flutua no Excel, com a vantagem de manter o vínculo com as células copiadas.

Do velho para o novo

Para quem não está encontrando os comandos no Excel 2007, a Microsoft criou um "tradutor". Clique aqui para ver.

Dica: se parar no 93% no Mozilla Firefox, tente no Internet Explorer...

Sempre uma dica nova

Agora este blog é para valer.
Sempre com uma dica nova sobre Excel.