(Parte 1 de 6)

Dicas do Microsoft EXCEL

Este documento faz parte do material didático oferecido no Curso Introdução a Informática Básica da Faetec, composto pelo resultado de pesquisas sobre o assunto na Internet, contendo material interessante para aprofundar no tema Excel. Pode ser acessado através do link http://info.abril.com.br/dicas/cat_12_0.shl.

A macro do Excel sumiu? Então ache onde ela está!3
Ache o valor máximo de uma planilha com o Excel 97/20003
Aprenda a posicionar um gráfico tridimensional numa planilha Excel4
Arredondamento e formatação de células4
Cálculo da moda no Excel5
Como buscar planilhas na web e levar para o Excel 2000/XP5
Como calcular juros no Excel6
Como calcular número de dias decorridos entre duas datas com o Excel 97/20009
Como configurar impressão para várias planilhas de uma só vez no Excel 97/20009
Como consolidar várias planilhas numa só com o Excel 97/2000/XP9
Como dar nome às células no Excel 97/200010
Como determinar o número que mais aparece numa planilha Excel 9710
Como fazer séries personalizadas para AutoPreenchimento com o Excel 97/200010
Como lidar com a formatação condicional no Excel 97/20001
Como lidar com linhas múltiplas em células com o Excel 97/20001
Como lidar com objetos de desenho em planilhas do Excel 97/20001
Como lidar com seqüências numéricas no AutoPreenchimento do Excel 97/20001
Como montar no Excel um controle das finanças pessoais12
Como mudar a visualização das tabelas no Excel 9713
Como preservar o conteúdo de uma célula no modo de edição do Excel 97/200013
Como recuperar informações de planilhas corrompidas do Excel14
Como recuperar uma planilha danificada no Excel14
Como salvar uma planilha como documento web no Excel 97/200014
Como somar a jato no Excel 97/200014
Como usar no Excel XP para publicar automaticamente números na internet15
Como usar o Excel 97/2000/XP como um banco de dados15
Como usar planilha com HTML e ActiveX no Excel 200015
Como usar planilhas 3D16
Como usar planilhas dinâmicas no Excel 9717
Concatenando valores no Excel 97/200018
Dá para chamar células pelo nome, amigavelmente, no Excel 97/200018
Diretório-padrão do Excel18
Duas planilhas na tela18
Duplicando fórmulas no Excel 97/200019
Explorando o AutoPreenchimento do Excel 97/200019
Faça cálculos com números de outras planilhas com o Excel 97/2000/XP19

SUMÁRIO Formatando regiões de uma planilha no Excel 97/2000......................................................20

Linhas e colunas ocultas no Excel20
O Excel 2000 parou de calcular? É fácil resolver20
O que fazer se o Excel se recusa a salvar uma planilha21
Ocultar células agiliza o trabalho no Excel21
Para achar orelhas de acesso em meio a grande número de planilhas no Excel 97/200021
Para evitar erros de datas no Excel2
Para incluir um comentário na célula2
Para listar apenas os dias úteis no Excel 97/20002
Para personalizar avisos de erros no Excel 97/200023
Para proteger planilhas com o Excel 97/200023
Para usar hyperlinks no Excel 97/200023
Planeje os investimentos no Excel 97/2000/XP24
Planilhas como banco de dados no Excel 97/200025
Quantas planilhas você quer?25
Recorra ao poder da Subtotal25
Seleção de colunas no Excel26
Selecionar toda a planilha26
Separando botões de imprimir e salvar no Excel 97/200026
Separando resultados parciais numa planilha com o Excel 97/200026
Some no Excel só quando for necessário26
Tabelas Excel dentro do Word27
Toda a planilha numa única página27
Torne vermelhos números que indicam negativo no Excel 97/2000/XP27
Saiba como criar formatos para exibir números, textos e datas no Excel28

Incluindo numa célula o nome e o caminho da pasta de trabalho no Excel 97/2000...........20 Para copiar dados de uma seqüência vertical para uma horizontal com o Excel 97/2000...2 Use o Excel para montar uma planilha que automatiza a emissão de pedidos e orçamento na empresa................................................................................................................................. 30

Você acaba de criar uma macro no Excel, fecha a planilha, abre outra ecadê a macro? Para

A macro do Excel sumiu? Então ache onde ela está! começo de conversa, ela não sumiu, foi salva no próprio arquivo da planilha. Você nem percebeu porque o Excel, diferentemente do Word, que guarda todas as macros num lugar só, o Normal.dot, oferece três opções de arquivamento. Refaça o caminho e confira. Abra uma planilha, clique em Ferramentas, Macro e em Gravar nova macro. Na caixa de diálogo, observe que no campo Armazenar Macro Em tem uma lista dropdown com as seguintes opções: Esta pasta de trabalho, Nova pasta de trabalho e Pasta de trabalho pessoal de macros. Defina de novo a macro, determine a tecla de atalho, escolha a pasta de trabalho pessoal e clique OK. Agora, se você usa Windows 98, sua macro estará no caminho C:WindowsApplication DataMicrosoftExcel XLInicio. No Windows 2000 e no XP, as macros ficam guardadas nos diretórios relativos ao usuário.

Ache o valor máximo de uma planilha com o Excel 97/2000

Você tem uma planilha que faz o acompanhamento diário da variação de um número qualquer: valor do dólar, faturamento do caixa de sua empresa, medições num laboratório. Enfim, uma planilha que inclui uma coluna para datas e outra para valores. Num documento desse tipo, como localizar, na coluna de datas, a célula correspondente ao valor máximo (ou mínimo) registrado?

A dificuldade, aqui, reside no fato de você não saber de antemão até onde se estendem as colunas, já que os pares data-valor vão sendo adicionados com o tempo. Em outras palavras, você quer descobrir qual o maior valor de uma região em aberto. Uma solução pode ser a seguinte. Admita que as datas estão na coluna A e os valores na B. A primeira linha é ocupada com títulos: Data, Valor. Admita, também, que esses dados sempre formam um par: não há data sem valor nem vice-versa. O primeiro passo consiste em nomear a coluna B. Acione Inserir/Nome/Definir e digite, em cima, o nome Dados (Valor não é adequado: existe uma função com esse nome). Embaixo, escreva:

=DESLOC(Plan1!$B$2;0;0;CONT.VALORES(Plan1!$B:$B)-1)

Você criou uma fórmula chamada Dados. A função CONT.VALORES verifica quantas células preenchidas existem na coluna B. A subtração de 1, no final, desconta a célula B1, que contém o título. A fórmula acima tem apenas um papel auxiliar: retorna uma referência à coluna B e será usada na fórmula seguinte, que você pode incluir em qualquer célula:

=DESLOC(A1;CORRESP(MÁXIMO(Dados);Dados;0);0)

Esta nova fórmula fornece a data na coluna A que faz par com o valor máximo registrado na coluna B. Se você quiser a data para o valor mínimo, basta trocar aí a função MÁXIMO por MÍNIMO. Como as colunas de dados tendem a crescer, crie, no início da planilha, uma célula para exibir a data de máximo e outra para a data de mínimo. Lembrete: naturalmente, o valor máximo ou mínimo pode ocorrer mais de uma vez. Essa solução aponta apenas o primeiro. Você pode ampliá-la para encontrar os demais.

Há outra solução interessante derivada dessa primeira: é possível criar um gráfico que incorpore automaticamente os novos valores diários incluídos na planilha. Mais uma vez, acione Inserir/Nome/Definir e digite a fórmula Eixo_X:

=DESLOC(Plan1!$A$2;0;0;CONT.VALORES(Plan1!$A:$A)-1)

Agora, crie um gráfico - melhor em outra planilha, para ocupar todo o espaço disponível. Inicialmente, conclua o gráfico vazio. Em seguida, clique nele com o botão direito e escolha Dados de Origem. Abre-se uma janela com esse nome. Escolha a orelha Seqüência e clique no botão Adicionar. Agora, na caixa Nome, dê um título para o gráfico. No campo Valores, digite:

=Plan1!Dados

Isso diz ao Excel quais dados devem ser usados para gerar o gráfico. Por fim, na caixa Rótulo dos Eixos das Categorias (X), informe:

=Plan1!Eixo_X

O gráfico está pronto. Para testá-lo, vá à planilha e inclua novos pares data-valor. Funciona! De agora para a frente, alimente os dados e o gráfico vai se fazer sozinho.

Aprenda a posicionar um gráfico tridimensional numa planilha Excel

Vale a pena explorar o poder do Excel para apresentar as informações de forma gráfica. No caso de figuras 3D, alguns truques ajudam a deixar o resultado melhor. Quando o Excel produz um gráfico tridimensional, é possível que a disposição de barras, setores de pizza ou outros componentes do gráfico não sejam os mais adequados para a visualização dos dados. Uma saída pode ser a rotação do gráfico. Para fazer isso, o modo tradicional é clicar com o botão direito em qualquer ponto das extremidades do gráfico e, no menu, escolher Exibição 3D. Abre-se a janela com esse mesmo nome. Nela, você pode clicar nos botões com setas para rotacionar o gráfico. Outra forma, mais rápida e menos conhecida, é levar o ponteiro do mouse até uma das extremidades da área de plotagem do gráfico.

Quando o cursor passa sobre uma delas, aparece uma etiqueta com a legenda “Cantos”. Ao clicar num desses cantos, surgem pequenos quadrados pretos que servem de alça para a manipulação do gráfico. Clique numa dessas alças e gire a figura à vontade. Outra dica: faça a movimentação mantendo pressionada a tecla Ctrl. Isso permite girar e visualizar ao mesmo tempo a estrutura do gráfico, o que facilita decidir a hora de parar.

Arredondamento e formatação de células

No Excel, quando você formata as células para exibir números - por exemplo - com duas casas decimais, passa a enfrentar um problema.Os números, especialmente os que resultam de cálculos, são mostrados na tela com dois algarismos depois da vírgula. Mas, na memória, eles são guardados sem arredondamento, com muito mais casas decimais. E esses números é que são usados nas operações. Resultado: depois de cálculos em cascata, você obtém valores que não são os esperados. Para garantir que o Excel exiba na tela o mesmo número guardado na memória, use a função ARRED, de arredondamento. Ela pede o número e a quantidade desejada de casas decimais.

Cálculo da moda no Excel

Numa planilha Excel, é fácil calcular a moda de um conjunto de valores. Moda, você sabe, é a figura estatística que indica o valor que mais se repete numa amostra. Nesse caso, a amostra é uma linha, uma coluna ou uma região qualquer da planilha.

Só que, no Excel, em vez de usar o nome moda, que seria o correto, usa-se a função Modo. Basta construir uma fórmula assim: =Modo(A1:C4)

No caso, A1:C4 é a região que contém as células com os valores desejados. Se não houver valores repetidos, o resultado será um erro do tipo #N/D (não disponível). Se houver mais de um valor modal, a moda será o menor. Na seqüência de quatro números 3,3,10,10, a moda é 3, que é o valor menor.

Como buscar planilhas na web e levar para o Excel 2000/XP

O Excel oferece um recurso que permite a captura de tabelas existentes em páginas web para uma planilha em seu desktop. Trata-se de um recurso muito útil para quem deseja realizar cálculos com base em informações trazidas de uma fonte externa. Também é possível manter um vínculo ativo com a página: se os dados se modificarem na web, serão igualmente modificados na planilha. Veja, por exemplo, como importar uma tabela da web para o Excel. Dê o comando Dados/Obter Dados Externos/Nova Consulta à Web. Na tela que se abre, digite o endereço da página desejada. Você pode escolher a opção Somente as Tabelas e trazer todas as tabelas da página. Ou então selecionar a alternativa Uma ou Mais Tabelas Específicas na Página. Isso implica que você deve indicar o número da tabela desejada. O problema, nesse caso, é que você precisa descobrir, por tentativa e erro, qual número corresponde àquela tabela. Agora, clique em OK. Surge nova tela. Clique em Propriedades para ajustar algumas características da tabela importada.

Marque a caixa Atualizar a Cada X Minutos (defina o valor X), se quiser que os dados sejam recapturados na web a intervalos regulares. Naturalmente, isso só faz sentido se a tabela original for dinâmica, com variações freqüentes, como informações da bolsa de valores e taxas de câmbio. Marque ainda a caixa Atualizar Dados em Abrir Arquivo para que uma reimportação da tabela seja feita toda vez que você abrir a planilha.

No Excel XP, essa tarefa é mais fácil, porque o programa exibe um minibrowser, no qual você pode navegar até a página que contém a tabela que deseja capturar. Cada tabela da página está marcada, no topo, por uma pequena seta. Clique na seta para selecionar a tabela desejada. Isso evita que você tenha de adivinhar qual o número da tabela.

Como calcular juros no Excel

Vamos mostrar como criar uma aplicação para calcular e montar tabelas detalhadas para a amortização de empréstimos. Você, que vai construir o sisteminha, terá de encarar todas as fórmulas e truques necessários para fazê-lo funcionar. Mas o objetivo é esconder todas as complexidades, criando um resultado final que qualquer usuário possa utilizar. Na verdade, se você não está nem aí para fórmulas, a planilha desenvolvida por INFO está pronta para usar, e disponível online no site da revista no endereço ftp:// ftp.info.abril.com.br/financiamento.zip.

Mãos à obra. Para começar, vamos definir o formato geral de nossa planilha. Ela deve conter três áreas. Primeiro, as células nas quais o usuário deve digitar os dados básicos do financiamento (valor, taxa de juros, prazo e data inicial). Depois, um painel-resumo, com o valor do pagamento mensal, número de pagamentos, total de juros e custo total do financiamento. O terceiro bloco apresenta uma tabela com a discriminação dos dados de cada pagamento previsto. Cada linha dessa tabela mostra a previsão de pagamento de um mês, evoluindo desde o valor financiado total até o saldo final zero. Na elaboração dessa planilha, tomamos como ponto de partida um modelo publicado no site da Microsoft, que foi redefinido para este tutorial. Para acompanhar adequadamente as tarefas, é aconselhável que você faça o download de nossa planilha no site de INFO: ftp:// ftp.info.abril.com.br/financiamento.zip.

A principal técnica utilizada na elaboração do projeto é a definição de nomes para células, grupos de células e fórmulas. Assim, em vez de operar com endereços, trabalha-se com variáveis, o que facilita bastante a compreensão. É muito mais confortável, por exemplo, lidar com itens como Valor_Financiado e Taxa_Juros do que com E4, C7. Vamos, então, montar a área 1 da planilha, dedicada à entrada de dados. Em nosso exemplo, ela ocupa as células de E4 a E7. Vamos dar um nome a cada uma dessas células.

Coloque o cursor em E4 e acione o comando Inserir/Nome/Definir. Na caixa Definir Nome, digite, em cima, Valor_Financiado (o nome não pode ter espaços). Embaixo, o programa já inclui, automaticamente, o endereço de E4. Acione OK. Repita a operação para as células E5 a E7, nomeando-as, respectivamente, como Taxa_Juros, Prazo_Meses e Data_Inicio. Faça as adequadas formatações nas quatro células, ajustando cada uma conforme o conteúdo esperado: moeda, decimal, inteiro e data. Para orientar o usuário da calculadora de financiamentos, escreva, à esquerda de cada célula, as indicações dos dados a serem digitados.

Passemos à segunda área. Na mesma coluna, no intervalo E11:E14, vamos nomear quatro células: Pagamento_Mensal, Num_Pagamentos, Total_Juros e Custo_Total. A cada um desses nomes deve corresponder uma fórmula:

=-PGTO(Taxa_Juros;Num_Pagamentos;Valor_Financiado)

Acima, a fórmula para Pagamento_Mensal. Ela usa a função PGTO e baseia-se em outras variáveis nomeadas. Observe o sinal de menos: ele indica que cada pagamento será subtraído do valor financiado. A fórmula para Num_Pagamentos é a seguinte:

=SE(Tudo_Preenchido;Prazo_Meses;"")

A rigor, o número de pagamentos é igual ao valor digitado na célula Prazo_Meses. No entanto, ele só deve aparecer quando o usuário tiver preenchido as células da área 1 para o cálculo de novo financiamento. Por extensão, as células da área 2 e da área 3 só devem exibir alguma informação se as quatro células da área 1 estiverem preenchidas. Para garantir isso, vamos criar uma fórmula nomeada (sempre em Inserir/Nome/Definir), Tudo_Preenchido, que funciona como um teste lógico, do tipo sim/não:

=SE(Valor_Financiado*Taxa_Juros *Prazo_Meses*Data_Inicio>0;1;0)

Aqui, o truque é o seguinte: multiplicam-se os quatro valores das células na área 1. Se todos estiverem preenchidos, o resultado será um número positivo; se pelo menos um estiver em branco, será zero. Assim, se a fórmula Tudo_Preenchido responde sim (valor 1), os cálculos devem ser executados para preencher as áreas 2 e 3. Se for não (valor 0), aquelas regiões são mostradas em branco.

As fórmulas para as células Total_Juros (E13) e Custo_Total (E14) são, respectivamente: =-IPGTO(Taxa_Juros;Pagamento_Num;Num_Pagamentos;Valor_Financiado)

=SE(Tudo_Preenchido;ARRED(Pagamento_Mensal;2)*Num_Pagamentos;"")

Em Total_Juros, usa-se a função IPGTO, do Excel, que fornece os juros acumulados de um investimento. Também nesse caso, como se trata de débitos, o sinal é negativo. Na fórmula do Custo_Total, preste atenção para a função ARRED (arredondamento), aplicada apenas ao valor do pagamento mensal. Ela garante que a multiplicação dos valores mostrados para Pagamento_Mensal e Num_Pagamentos produz, exatamente, o número mostrado em Custo_Total. Sem essa função, apareceria um número aproximado.

A área 3 da planilha, que apresenta a discriminação de cada pagamento, é encimada por um cabeçalho com sete títulos de colunas: Número; Data do Pagamento; Balanço Inicial; Pagamento; Principal; Juros; e Balanço Final. Selecione essas sete células e nomeie-as como Linha_Cabeçalho, definida pela seguinte fórmula:

=LIN(Tabela de Amortização´! $17:$17)

Ainda não havíamos dito que nossa folha de cálculo foi batizada como Tabela de Amortização. Nessa tabela, o cabeçalho corresponde à linha 17. Como a área 3 pode se estender por mais de uma página impressa, acione Arquivo/Configurar Página/orelha Planilha e, na caixa Linhas a Repetir na Parte Superior, digite Linha_Cabeçalho. Avancemos, agora, para o miolo da área 3. Vamos construir a primeira linha, logo abaixo do cabeçalho. Naturalmente, as sete células dessa linha têm fórmulas específicas. A célula Número apresenta apenas o valor Pagamento_Num, ou seja, a seqüência 1, 2, 3 etc. Sua fórmula é:

(Parte 1 de 6)

Comentários